package com.github.wicketoracle.app.user.standard.mgr;
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 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.html.form.choice.IntegerSelectChoice;
import com.github.wicketoracle.html.form.choice.SelectChoiceList;
import com.github.wicketoracle.html.form.choice.StringSelectChoice;
import com.github.wicketoracle.oracle.dao.AbstractOracleDAO;
import com.github.wicketoracle.oracle.util.CloseResource;
final class StandardUserMgrDAO extends AbstractOracleDAO
{
/** Log */
private static final Logger LOGGER = LoggerFactory.getLogger( StandardUserMgrDAO.class );
/**
* Constructor
*
* @param pUsername
* The username
* @param pPassword
* The password
* @throws SQLException
*/
public StandardUserMgrDAO( 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_STANDARD_APP_USER_MGR );
return getKeyValueRefData( "app_user", "pk_standard_app_user_mgr", "fn_get_list_ref_data" );
}
/**
*
* @param pUsername
* @param pIsAccountEnabled
* @param pIsTracingEnabled
* @param pDbrlId
* @param pLowerRecordLimit
* @param pUpperRecordLimit
* @return
* A list of standard users which can be managed through the application
*/
public List<StandardUser> getUsers( final String pUsername, final String pIsAccountEnabled, final String pIsTracingEnabled, final int pDbrlId, final int pLngId, final int pLowerRecordLimit, final int pUpperRecordLimit ) throws SQLException
{
final String dbStatement = " begin "
+ " sys.dbms_application_info.set_module ( module_name => ? , action_name => ? ); "
+ " ? := app_user.pk_standard_app_user_mgr.fn_get_standard_users"
+ " ( "
+ " p_aur_username => ? "
+ " , p_is_account_enabled => ? "
+ " , p_is_tracing_enabled => ? "
+ " , p_dbrl_id => ? "
+ " , p_lng_id => ? "
+ " , p_lower_record_limit => ? "
+ " , p_upper_record_limit => ? "
+ " );"
+ " end; ";
OracleCallableStatement dbCstmt = null;
OracleResultSet dbRs = null;
List<StandardUser> userMgrBeanList = new ArrayList<StandardUser>();
try
{
LOGGER.debug( "Search standard users" );
setRole( RequiredRoles.ROLE_STANDARD_APP_USER_MGR );
LOGGER.debug( "Role set" );
/* retrieve data */
dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement );
LOGGER.debug( "DB statement prepared -> {}", dbStatement );
dbCstmt.setString( 1, "PK_STANDARD_APP_USER_MGR" );
dbCstmt.setString( 2, "FN_GET_STANDARD_USERS" );
dbCstmt.registerOutParameter( 3, OracleTypes.CURSOR );
if ( pUsername == null )
{
dbCstmt.setNull( 4, OracleTypes.VARCHAR );
}
else
{
dbCstmt.setString( 4 , pUsername );
}
if ( pIsAccountEnabled == null )
{
dbCstmt.setNull( 5 , OracleTypes.VARCHAR );
}
else
{
dbCstmt.setString( 5 , pIsAccountEnabled );
}
if ( pIsAccountEnabled == null )
{
dbCstmt.setNull( 6 , OracleTypes.VARCHAR );
}
else
{
dbCstmt.setString( 6 , pIsTracingEnabled );
}
if ( pDbrlId == 0 )
{
dbCstmt.setNull( 7 , OracleTypes.INTEGER );
}
else
{
dbCstmt.setInt( 7 , pDbrlId );
}
if ( pLngId == 0 )
{
dbCstmt.setNull( 8 , OracleTypes.INTEGER );
}
else
{
dbCstmt.setInt( 8 , pLngId );
}
if ( pLowerRecordLimit == 0 )
{
dbCstmt.setNull( 9 , OracleTypes.INTEGER );
}
else
{
dbCstmt.setInt( 9 , pLowerRecordLimit );
}
if ( pUpperRecordLimit == 0 )
{
dbCstmt.setNull( 10 , OracleTypes.INTEGER );
}
else
{
dbCstmt.setInt( 10 , pUpperRecordLimit );
}
LOGGER.debug( "DB params registered" );
dbCstmt.execute();
LOGGER.debug( "DB statement executed" );
dbRs = ( OracleResultSet ) dbCstmt.getCursor( 3 );
/* build user list */
while ( dbRs.next() )
{
userMgrBeanList.add
(
new StandardUser
(
dbRs.getInt( "ID" )
, dbRs.getString( "AUR_USERNAME" )
, dbRs.getString( "AUR_PROFILE" )
, new StringSelectChoice( dbRs.getString( "AUR_IS_ACCOUNT_ENABLED" ) )
, new StringSelectChoice( dbRs.getString( "AUR_IS_TRACING_ENABLED" ) )
, new IntegerSelectChoice( dbRs.getInt( "LNG_ID" ) )
, new java.util.Date( dbRs.getTIMESTAMP( "CREATED_DATE" ).timestampValue().getTime() )
, dbRs.getTIMESTAMP( "UPDATED_DATE" )
, false
)
);
}
return userMgrBeanList;
}
catch ( SQLException sqle )
{
LOGGER.error
(
"SQL Exception whilst searching standard users -> {}; error code -> {}; sql state -> {}; username -> {}; "
+ " is account enabled -> {}; is tracing enabled -> {}; pDbrlId -> {}; pLngId -> {}; pLowerRecordLimit -> {}; pUpperRecordLimit -> {}"
, new Object[]
{
sqle.getMessage()
, sqle.getErrorCode()
, sqle.getSQLState()
, pUsername
, pIsAccountEnabled
, pIsTracingEnabled
, pDbrlId
, pLngId
, pLowerRecordLimit
, pUpperRecordLimit
}
);
throw sqle;
}
finally
{
CloseResource.close( dbRs );
CloseResource.close( dbCstmt );
}
}
/**
* @param pUsers
* a list of users to whom changes may be applied
*/
public void updateUsers( final List<StandardUser> pUsers ) throws SQLException, NothingToDoException
{
final String dbStatement = " begin "
+ " sys.dbms_application_info.set_module ( module_name => ? , action_name => ? ); "
+ " app_user.pk_standard_app_user_mgr.pr_update_standard_users( p_dataset => ? );"
+ " end; ";
OracleCallableStatement dbCstmt = null;
try
{
LOGGER.debug( "Update standard users" );
setRole( RequiredRoles.ROLE_STANDARD_APP_USER_MGR );
LOGGER.debug( "Role set" );
dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement );
/* check that some modifications have taken place */
List<Object[]> moddedUsers = new ArrayList<Object[]>();
for ( StandardUser user : pUsers )
{
if ( user.isModified() )
{
moddedUsers.add
(
new Object[]
{
user.getUserId()
, user.getUsername()
, user.getIsEnabled().getKey()
, user.getIsTracingEnabled().getKey()
, user.getLanguage().getKey() // language
, null // created date
, user.getAurUpdatedDate()
}
);
}
}
/* if moddedUsers is empty then nothing needs to be done */
if ( moddedUsers.size() == 0 )
{
throw new NothingToDoException();
}
LOGGER.debug( "{} user mod(s) specified", moddedUsers.size() );
STRUCT[] moddedDbUsers = new STRUCT[moddedUsers.size()];
LOGGER.debug( "User STRUCT[] built : size -> {}", moddedUsers.size() );
StructDescriptor tyAppUser = StructDescriptor.createDescriptor( "APP_USER.TY_STANDARD_APP_USER", getConnection() );
LOGGER.debug( "ty_app_user structure retrieved" );
ArrayDescriptor ttyAppUser = ArrayDescriptor.createDescriptor( "APP_USER.TTY_STANDARD_APP_USER", getConnection() );
LOGGER.debug( "tty_app_user structure retrieved" );
for ( int i = 0 ; i < moddedDbUsers.length ; i++ )
{
moddedDbUsers[i] = new STRUCT( tyAppUser , getConnection(), moddedUsers.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_STANDARD_APP_USER_MGR" );
dbCstmt.setString( 2, "PR_UPDATE_STANDARD_USERS" );
dbCstmt.setARRAY( 3, new ARRAY( ttyAppUser , getConnection() , moddedDbUsers ) );
LOGGER.debug( "DB params registered" );
dbCstmt.execute();
LOGGER.debug( "DB statement executed" );
}
catch ( SQLException sqle )
{
LOGGER.error
(
"SQL Exception whilst updating standard users -> {}; error code -> {}; sql state -> {}"
, new Object []
{
sqle.getMessage()
, sqle.getErrorCode()
, sqle.getSQLState()
}
);
throw sqle;
}
finally
{
CloseResource.close( dbCstmt );
}
}
}