package com.github.wicketoracle.app.data; import java.sql.SQLException; import java.util.ArrayList; import java.util.Enumeration; import java.util.List; import javax.swing.tree.DefaultMutableTreeNode; import javax.swing.tree.DefaultTreeModel; import javax.swing.tree.TreeModel; import javax.swing.tree.TreeNode; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleResultSet; import oracle.jdbc.OracleTypes; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.sql.STRUCT; import oracle.sql.StructDescriptor; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.github.wicketoracle.app.exception.NothingToDoException; import com.github.wicketoracle.oracle.dao.AbstractOracleDAO; import com.github.wicketoracle.oracle.util.CloseResource; final class DataMgrDAO extends AbstractOracleDAO { /** Log */ private static final Logger LOGGER = LoggerFactory.getLogger( DataMgrDAO.class ); /** * Constructor * * @param pUsername * The username * @param pPassword * The password * @throws SQLException */ public DataMgrDAO( final String pUsername , final String pPassword ) throws SQLException { super( pUsername , pPassword ); } /** * * @return a tree representation of the available reports. * root nodes represent report categories; leaves represent actual reports * */ public TreeModel getRefdataTree( final TreeNode pRootNode ) throws SQLException { final String dbStatement = " begin " + " SYS.dbms_application_info.set_module( module_name => ? , action_name => ? ); " + " ? := app_refdata.pk_ref_data_mgr.fn_get_data_structure_list;" + " end; "; OracleCallableStatement dbCstmt = null; OracleResultSet dbRs = null; try { /* retrieve data */ LOGGER.debug( "Get refdata tree" ); setRole( RequiredRoles.ROLE_REF_DATA_MGR ); LOGGER.debug( "Role set" ); dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement ); LOGGER.debug( "DB statement prepared -> {}" , dbStatement ); dbCstmt.setString( 1 , "PK_REF_DATA_MGR" ); dbCstmt.setString( 2 , "FN_GET_DATA_STRUCTURE_LIST" ); dbCstmt.registerOutParameter( 3 , OracleTypes.CURSOR ); LOGGER.debug( "DB params registered" ); dbCstmt.execute(); LOGGER.debug( "DB statement executed" ); dbRs = ( OracleResultSet ) dbCstmt.getCursor( 3 ); /* build tree - each row in the result set represents a category */ DefaultMutableTreeNode rootNode = ( DefaultMutableTreeNode ) pRootNode; DefaultMutableTreeNode prevRefdataNode = null; DefaultMutableTreeNode currRefdataNode = null; int prevRefdataLevel = -1; int currRefdataLevel = -1; while ( dbRs.next() ) { boolean tempIsEditable = false; if ( dbRs.getString( "IS_EDITABLE" ).equals( "Y" ) ) { tempIsEditable = true; } currRefdataLevel = dbRs.getInt( "HLEVEL" ); currRefdataNode = new DefaultMutableTreeNode ( new DataStructure ( dbRs.getInt( "ID" ) , dbRs.getString( "CODE" ) , dbRs.getString( "DESCR" ) , tempIsEditable , dbRs.getString( "RDT_CODE" ) , false ) ); /* add new report category to the tree */ if ( currRefdataLevel == 1 ) { rootNode.add( currRefdataNode ); } else { if ( currRefdataLevel == prevRefdataLevel ) { ( ( DefaultMutableTreeNode ) prevRefdataNode.getParent() ).add( currRefdataNode ); } else if ( currRefdataLevel > prevRefdataLevel ) { prevRefdataNode.add( currRefdataNode ); } else if ( currRefdataLevel < prevRefdataLevel ) { // find tree node with level < currReportCategoryLevel and make the current node a child of it DefaultMutableTreeNode searchNode = ( DefaultMutableTreeNode ) prevRefdataNode.getParent(); do { if ( searchNode.getLevel() < currRefdataLevel ) { searchNode.add( currRefdataNode ); break; } else { searchNode = ( DefaultMutableTreeNode ) searchNode.getParent(); } } while ( true ); } } /* store references to the just processed node */ prevRefdataNode = currRefdataNode; prevRefdataLevel = currRefdataLevel; } return new DefaultTreeModel( pRootNode ); } catch ( SQLException sqle ) { LOGGER.error ( "SQL Exception whilst creating getting report tree -> {}; error code -> {}; sql state -> {}" , new Object [ ] { sqle.getMessage() , sqle.getErrorCode() , sqle.getSQLState() } ); throw sqle; } finally { CloseResource.close( dbRs ); CloseResource.close( dbCstmt ); } } /** * * @throws SQLException */ public void configRefdata( final TreeModel pRefdataTree ) throws SQLException, NothingToDoException { final String dbStatement = " begin " + " SYS.dbms_application_info.set_module( module_name => ? , action_name => ? ); " + " app_refdata.pk_configure_ref_data.pr_configure_ref_data( p_data_structure => ? );" + " end; "; OracleCallableStatement dbCstmt = null; try { setRole( RequiredRoles.ROLE_CONFIGURE_REF_DATA ); LOGGER.debug( "Role set" ); dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement ); LOGGER.debug( "DB statement prepared -> {}" , dbStatement ); /* retrieve list of reference data structures */ Enumeration<DefaultMutableTreeNode> treeNodes = ( ( DefaultMutableTreeNode ) pRefdataTree.getRoot() ).preorderEnumeration(); /* check that some modifications have taken place */ List <Object[]> moddedNodes = new ArrayList<Object[]>(); while ( treeNodes.hasMoreElements() ) { Object node = treeNodes.nextElement().getUserObject(); if ( node instanceof DataStructure ) { DataStructure refDataNode = ( DataStructure ) node; if ( refDataNode.isModified() ) { Object tempId = null; if ( refDataNode.getRdsId() != 0 ) { tempId = Integer.valueOf( refDataNode.getRdsId() ); } String tempIsEditable = "N"; if ( refDataNode.getEditable() ) { tempIsEditable = "Y"; } moddedNodes.add ( new Object [ ] { tempId , tempIsEditable } ); } } } /* if moddedNodes is empty then nothing needs to be done */ if ( moddedNodes.size() == 0 ) { throw new NothingToDoException(); } LOGGER.debug( "{} ref data structures mod(s) specified" , moddedNodes.size() ); STRUCT [ ] moddedDbNodes = new STRUCT [ moddedNodes.size() ]; LOGGER.debug( "User STRUCT[] built : size -> {}" , moddedNodes.size() ); StructDescriptor tyRefDataStructure = StructDescriptor.createDescriptor( "APP_REFDATA.TY_REF_DATA_STRUCTURE" , getConnection() ); LOGGER.debug( "ty_ref_data_structure structure retrieved" ); ArrayDescriptor ttyRefDataStructure = ArrayDescriptor.createDescriptor( "APP_REFDATA.TTY_REF_DATA_STRUCTURE" , getConnection() ); LOGGER.debug( "tty_ref_data_structure structure retrieved" ); for ( int i = 0 ; i < moddedDbNodes.length ; i++ ) { moddedDbNodes [ i ] = new STRUCT( tyRefDataStructure , getConnection() , moddedNodes.get( i ) ); } /* if we have reached this stage then changes have been specified, and now an ARRAY can be built, and our sp. executed */ dbCstmt.setString( 1 , "PK_CONFIGURE_REF_DATA" ); dbCstmt.setString( 2 , "PR_CONFIGURE_REF_DATA" ); dbCstmt.setARRAY( 3 , new ARRAY( ttyRefDataStructure , getConnection() , moddedDbNodes ) ); LOGGER.debug( "DB params registered" ); dbCstmt.execute(); LOGGER.debug( "DB statement executed" ); } catch ( SQLException sqle ) { LOGGER.error ( "SQL Exception whilst configuring reference data -> {}; error code -> {}; sql state -> {}" , new Object [ ] { sqle.getMessage() , sqle.getErrorCode() , sqle.getSQLState() } ); throw sqle; } finally { CloseResource.close( dbCstmt ); } } }