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 );
}
}
}