IntersectionListMgrDAO.java

package com.github.wicketoracle.app.data.list.intersection;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.github.wicketoracle.app.data.RequiredRoles;
import com.github.wicketoracle.html.form.choice.IntegerSelectChoice;
import com.github.wicketoracle.html.form.choice.SelectChoiceList;
import com.github.wicketoracle.oracle.dao.AbstractOracleDAO;
import com.github.wicketoracle.oracle.util.CloseResource;

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;


final class IntersectionListMgrDAO extends AbstractOracleDAO
{
    /** Log */
    private static final Logger LOGGER = LoggerFactory.getLogger( IntersectionListMgrDAO.class );

    /**
     * Constructor
     */
    public IntersectionListMgrDAO( final String pUsername , final String pPassword ) throws SQLException
    {
        super( pUsername , pPassword );
    }

    /**
     * return the list of parent list items
     */
    public SelectChoiceList<IntegerSelectChoice> getParentData( final int pDataStructureId , final String pDbRoleName ) throws SQLException
    {
        final String dbStatement =   " BEGIN  "
                                   + "   SYS.dbms_application_info.set_module ( module_name => ? , action_name => ? ); "
                                   + "   ? := app_refdata.pk_intersection_list_mgr.fn_get_parent_data( p_rds_id => ? );"
                                   + " END;   ";

        OracleCallableStatement dbCstmt = null;
        OracleResultSet         dbRs    = null;

        SelectChoiceList<IntegerSelectChoice> parentData = new SelectChoiceList<IntegerSelectChoice>();

        try
        {
            LOGGER.debug( "Retrieve intersection parent list data" );

            String[] dbRoles = { RequiredRoles.ROLE_REF_DATA_MGR, pDbRoleName};

            setRoles( dbRoles );

            LOGGER.debug( "Roles set" );

            dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement );

            LOGGER.debug( "DB statement prepared -> {}", dbStatement );

            dbCstmt.setString( 1, "PK_INTERSECTION_LIST_MGR" );
            dbCstmt.setString( 2, "FN_GET_PARENT_DATA" );
            dbCstmt.registerOutParameter( 3, OracleTypes.CURSOR );
            dbCstmt.setInt( 4, pDataStructureId );

            LOGGER.debug( "DB params registered" );

            dbCstmt.execute();

            LOGGER.debug( "DB statement executed" );

            dbRs = ( OracleResultSet ) dbCstmt.getCursor( 3 );

            while ( dbRs.next() )
            {
                parentData.add( new IntegerSelectChoice( dbRs.getInt( "ID" ), dbRs.getString( "NAME" ) ) );
            }

            return parentData;
        }
        catch ( SQLException sqle )
        {
            LOGGER.error
            (
                "SQL Exception whilst retrieving intersection parent data -> {}; error code -> {}; sql state -> {}; pRdsId -> {}; pDbrlName -> {}"
            ,   new Object []
                {
                    sqle.getMessage()
                ,   sqle.getErrorCode()
                ,   sqle.getSQLState()
                ,   pDataStructureId
                ,   pDbRoleName
                }
            );

            throw sqle;
        }
        finally
        {
            CloseResource.close( dbRs );
            CloseResource.close( dbCstmt );
        }
    }

    /**
     * Retrieve the child data, split into selected and available lists
     * @param pDataStructureId
     * @param pDbRoleName
     * @param pParentItemId
     * @return
     * @throws SQLException
     */
    public Map<String , SelectChoiceList<IntegerSelectChoice>> getChildData( final int pDataStructureId , final String pDbRoleName , final int pParentItemId ) throws SQLException
    {
        final String dbStatement =   " BEGIN  "
                                   + "   SYS.dbms_application_info.set_module ( module_name => ? , action_name => ? ); "
                                   + "   ? := app_refdata.pk_intersection_list_mgr.fn_get_child_data ( p_rds_id => ? , p_parent_id => ? );"
                                   + " END;   ";

        OracleCallableStatement dbCstmt = null;
        OracleResultSet         dbRs    = null;

        try
        {
            LOGGER.debug( "Retrieve intersection parent list data" );

            String[] dbRoles = { RequiredRoles.ROLE_REF_DATA_MGR, pDbRoleName};

            setRoles( dbRoles );

            LOGGER.debug( "Roles set" );

            dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement );

            LOGGER.debug( "DB statement prepared -> {}", dbStatement );

            dbCstmt.setString( 1 , "PK_INTERSECTION_LIST_MGR" );
            dbCstmt.setString( 2 , "FN_GET_CHILD_DATA" );
            dbCstmt.registerOutParameter( 3, OracleTypes.CURSOR );
            dbCstmt.setInt( 4 , pDataStructureId );
            dbCstmt.setInt( 5 , pParentItemId );

            LOGGER.debug( "DB params registered" );

            dbCstmt.execute();

            LOGGER.debug( "DB statement executed" );

            dbRs = ( OracleResultSet ) dbCstmt.getCursor( 3 );

            return getKeyValueRefData( dbRs );
        }
        catch ( SQLException sqle )
        {
            LOGGER.error
            (
                "SQL Exception whilst retrieving intersection child data -> {}; error code -> {}; sql state -> {}; pRdsId -> {}; pDbrlName -> {}; pItemId -> {}"
            ,   new Object []
                {
                    sqle.getMessage()
                ,   sqle.getErrorCode()
                ,   sqle.getSQLState()
                ,   pDataStructureId
                ,   pDbRoleName
                ,   pParentItemId
                }
            );

            throw sqle;
        }
        finally
        {
            CloseResource.close( dbRs );
            CloseResource.close( dbCstmt );
        }
    }

    /**
     * Apply updates
     */
    public void updateData( final int pRdsId , final String pDbrlName , final int pParentItemId , final SelectChoiceList<IntegerSelectChoice> pListData ) throws SQLException
    {
        final String dbStatement =   " BEGIN  "
                                   + "   SYS.dbms_application_info.set_module ( module_name => ? , action_name => ? ); "
                                   + "   app_refdata.pk_intersection_list_mgr.pr_update( p_rds_id => ? , p_parent_id => ? , p_dataset => ?);"
                                   + " END;   ";

        OracleCallableStatement dbCstmt = null;

        try
        {
            /* retrieve data */

            LOGGER.debug( "Update list of subdivision list data" );

            String[] dbRoles = { RequiredRoles.ROLE_REF_DATA_MGR, pDbrlName};

            setRoles( dbRoles );

            LOGGER.debug( "Roles set" );

            dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement );

            /* check that some modifications have taken place */
            List<Object[]> moddedListData = new ArrayList<Object[]>();

            for ( IntegerSelectChoice listItem : pListData )
            {
                moddedListData.add
                (
                    new Object[]
                    {
                        listItem.getKey()
                    }
                );
            }

            STRUCT[] moddedDbListData = new STRUCT[moddedListData.size()];

            LOGGER.debug( "User STRUCT[] built : size -> {}", moddedListData.size() );

            StructDescriptor tyIntersectionListRefData = StructDescriptor.createDescriptor( "APP_REFDATA.TY_INTERSECTION_LIST_REF_DATA", getConnection() );

            LOGGER.debug( "ty_intersection_list_ref_data structure retrieved" );

            ArrayDescriptor ttyIntersectionListRefData = ArrayDescriptor.createDescriptor( "APP_REFDATA.TTY_INTERSECTION_LIST_REF_DATA", getConnection() );

            LOGGER.debug( "tty_intersection_list_ref_data structure retrieved" );

            for ( int i = 0 ; i < moddedDbListData.length ; i++ )
            {
                moddedDbListData[i] = new STRUCT( tyIntersectionListRefData , getConnection(), moddedListData.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_INTERSECTION_LIST_MGR" );
            dbCstmt.setString( 2 , "PR_UPDATE" );
            dbCstmt.setInt( 3 , pRdsId );
            dbCstmt.setInt( 4 , pParentItemId );
            dbCstmt.setARRAY( 5 , new ARRAY( ttyIntersectionListRefData , getConnection() , moddedDbListData ) );

            LOGGER.debug( "DB params registered" );

            dbCstmt.execute();

            LOGGER.debug( "DB statement executed" );
        }
        catch ( SQLException sqle )
        {
            LOGGER.error
            (
                "SQL Exception whilst updating list intersection ref data -> {}; error code -> {}; sql state -> {}; pRdsId -> {}; pDbrlName -> {}; pParentItemId -> {}"
            ,   new Object []
                {
                    sqle.getMessage()
                ,   sqle.getErrorCode()
                ,   sqle.getSQLState()
                ,   pRdsId
                ,   pDbrlName
                ,   pParentItemId
                }
            );

            throw sqle;
        }
        finally
        {
            CloseResource.close( dbCstmt );
        }
    }
}