LogonHistoryReportDAO.java

package com.github.wicketoracle.app.report.logonhistory;

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

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleTypes;

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

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;


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

    /**
     * Constructor
     *
     * @param pUsername
     *                  The username
     * @param pPassword
     *                  The password
     * @throws SQLException
     */
    public LogonHistoryReportDAO( final String pUsername , final String pPassword ) throws SQLException
    {
        super( pUsername , pPassword );
    }

    /**
     * @return The reference data lists required by the standard user management functionality
     * @throws SQLException
     */
    public Map <String , SelectChoiceList<IntegerSelectChoice>> getKeyValueRefData() throws SQLException
    {
        setRole( RequiredRoles.ROLE_AUTHENTICATION_REPORT );

        return getKeyValueRefData( "app_report" , "pk_authentication_report" , "fn_get_list_ref_data" );
    }

    /**
     *
     * @return The report data
     */
    public List<ReportRecord> getReport( final int pAurId , final java.util.Date pStartDateTime , final java.util.Date pEndDateTime , final int pLowerRecordLimit , final int pUpperRecordLimit ) throws SQLException
    {
        final String dbStatement = " begin "
                                 + "   sys.dbms_application_info.set_module ( module_name => ? , action_name => ? ); "
                                 + "   ? := app_report.pk_authentication_report.fn_get_report"
                                 + "        ( "
                                 + "          p_aur_id             => ? "
                                 + "        , p_start_lgn_time     => ? "
                                 + "        , p_end_lgn_time       => ? "
                                 + "        , p_lower_record_limit => ? "
                                 + "        , p_upper_record_limit => ? "
                                 + "        );"
                                 + " end;  ";

        OracleCallableStatement dbCstmt  = null;
        OracleResultSet         dbRs     = null;

        List < ReportRecord > reportData = new ArrayList <ReportRecord>();

        try
        {
            LOGGER.debug( "Run authentication report" );

            setRole( RequiredRoles.ROLE_AUTHENTICATION_REPORT );

            LOGGER.debug( "Role set" );

            /* retrieve data */

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

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

            dbCstmt.setString( 1 , "PK_AUTHENTICATION_REPORT" );
            dbCstmt.setString( 2 , "FN_GET_REPORT" );
            dbCstmt.registerOutParameter( 3 , OracleTypes.CURSOR );

            if ( pAurId == 0 )
            {
                dbCstmt.setNull( 4 , OracleTypes.NULL );
            }
            else
            {
                dbCstmt.setInt( 4, pAurId );
            }

            if ( pStartDateTime == null )
            {
                dbCstmt.setNull( 5 , OracleTypes.NULL );
            }
            else
            {
                dbCstmt.setDate( 5, new java.sql.Date( pStartDateTime.getTime() ) );
            }

            if ( pEndDateTime == null )
            {
                dbCstmt.setNull( 6 , OracleTypes.NULL );
            }
            else
            {
                dbCstmt.setDate( 6, new java.sql.Date( pEndDateTime.getTime() ) );
            }

            if ( pLowerRecordLimit == 0 )
            {
                dbCstmt.setNull( 7 , OracleTypes.NULL );
            }
            else
            {
                dbCstmt.setInt( 7, pLowerRecordLimit );
            }

            if ( pUpperRecordLimit == 0 )
            {
                dbCstmt.setNull( 8 , OracleTypes.NULL );
            }
            else
            {
                dbCstmt.setInt( 8, pUpperRecordLimit );
            }

            LOGGER.debug( "DB params registered" );

            dbCstmt.execute();

            LOGGER.debug( "DB statement executed" );

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

            /* build report data */
            while ( dbRs.next() )
            {
                reportData.add
                (
                    new ReportRecord
                   (
                            dbRs.getString( "AUR_USERNAME" )
                        ,   dbRs.getTIMESTAMP( "LGN_TIME" ).timestampValue()
                        ,   dbRs.getString( "LGN_IP_ADDRESS" )
                        ,   dbRs.getString( "LGN_HTTP_SESSION" )
                        )
                );
            }

            return reportData;
        }
        catch ( SQLException sqle )
        {
            LOGGER.error
            (
                "SQL Exception whilst running authentication report -> {}; error code -> {}; sql state -> {}; aur id -> {}; start date time -> {}; end date time -> {}; lower record limit -> {}; upper record limit -> {}"
            ,   new Object [ ]
                {
                    sqle.getMessage()
                ,   sqle.getErrorCode()
                ,   sqle.getSQLState()
                ,   pAurId
                ,   pStartDateTime
                ,   pEndDateTime
                ,   pLowerRecordLimit
                ,   pUpperRecordLimit
                }
            );

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