package com.github.wicketoracle.app.user.standard.mgr;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
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 StandardUserRoleMgrDAO extends AbstractOracleDAO
{
/** Log */
private static final Logger LOGGER = LoggerFactory.getLogger( StandardUserRoleMgrDAO.class );
/**
* Constructor
*
* @param pUsername
* The username
* @param pPassword
* The password
* @throws SQLException
*/
public StandardUserRoleMgrDAO( final String pUsername, final String pPassword ) throws SQLException
{
super( pUsername, pPassword );
}
/**
*
* @param pUserId
* User id of the application user whose roles we are trying to discover
* @return
*/
public List<StandardUserRole> getUserRoles( final int pUserId ) throws SQLException
{
final String dbStatement = " begin "
+ " sys.dbms_application_info.set_module ( module_name => ? , action_name => ? ); "
+ " ? := app_user.pk_standard_user_role_mgr.fn_get_standard_user_roles( p_aur_id => ? );"
+ " end; ";
OracleCallableStatement dbCstmt = null;
OracleResultSet dbRs = null;
List<StandardUserRole> userRoleList = new ArrayList<StandardUserRole>();
try
{
LOGGER.debug( "Get user roles : pAurId -> {}", pUserId );
setRole( RequiredRoles.ROLE_STD_APP_USER_ROLE_MGR );
LOGGER.debug( "Role set" );
/* retrieve data */
dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement );
LOGGER.debug( "DB statement prepared -> {}", dbStatement );
dbCstmt.setString( 1, "PK_STANDARD_USER_ROLE_MGR" );
dbCstmt.setString( 2, "FN_GET_STANDARD_USER_ROLES" );
dbCstmt.registerOutParameter( 3, OracleTypes.CURSOR );
dbCstmt.setInt( 4, pUserId );
LOGGER.debug( "DB params registered" );
dbCstmt.execute();
LOGGER.debug( "DB statement executed" );
dbRs = ( OracleResultSet ) dbCstmt.getCursor( 3 );
/* wrap the result up in our list */
while ( dbRs.next() )
{
boolean tempUserHasRole = false;
if ( dbRs.getString( "DBRL_IS_ASSIGNED_TO_USER" ).equals( "Y" ) )
{
tempUserHasRole = true;
}
userRoleList.add
(
new StandardUserRole
(
dbRs.getInt( "ID" )
, dbRs.getString( "DESCR" )
, tempUserHasRole
, dbRs.getString( "NAME" )
, false
)
);
}
return userRoleList;
}
catch ( SQLException sqle )
{
LOGGER.error
(
"SQL Exception whilst retrieving standard user roles -> {}; error code -> {}; sql state -> {}; pAurId -> {}"
, new Object []
{
sqle.getMessage()
, sqle.getErrorCode()
, sqle.getSQLState()
, pUserId
}
);
throw sqle;
}
finally
{
CloseResource.close( dbRs );
CloseResource.close( dbCstmt );
}
}
/**
* Apply changes to roles specified via the application
*/
public void setUserRoles( final int pAurId, final List<StandardUserRole> pRoles ) throws SQLException , NothingToDoException
{
final String dbStatement = " begin "
+ " sys.dbms_application_info.set_module ( module_name => ? , action_name => ? ); "
+ " app_user.pk_standard_user_role_mgr.pr_update_standard_user_roles( p_aur_id => ?, p_dataset => ? );"
+ " end; ";
OracleCallableStatement dbCstmt = null;
try
{
LOGGER.info( "Set user roles : pAurId-> {}", pAurId );
setRole( RequiredRoles.ROLE_STD_APP_USER_ROLE_MGR );
LOGGER.info( "Role set" );
dbCstmt = ( OracleCallableStatement ) getConnection().prepareCall( dbStatement );
/* check that some modifications have taken place */
List<Object[]> moddedRoles = new ArrayList<Object[]>();
for ( StandardUserRole role : pRoles )
{
if ( role.isModified() )
{
String tempGetAssigned = "N";
if ( role.getAssigned() )
{
tempGetAssigned = "Y";
}
moddedRoles.add
(
new Object[]
{
role.getDbRole()
, tempGetAssigned
}
);
}
}
/* if moddedUsers is empty then nothing needs to be done */
if ( moddedRoles.size() == 0 )
{
throw new NothingToDoException();
}
LOGGER.debug( "{} user mod(s) specified", moddedRoles.size() );
STRUCT[] moddedDbRoles = new STRUCT[moddedRoles.size()];
LOGGER.debug( "Role STRUCT[] built : size -> {}", moddedRoles.size() );
StructDescriptor tyDbRole = StructDescriptor.createDescriptor( "APP_USER.TY_DB_ROLE", getConnection() );
LOGGER.debug( "ty_db_role structure retrieved" );
ArrayDescriptor ttyDbRole = ArrayDescriptor.createDescriptor( "APP_USER.TTY_DB_ROLE", getConnection() );
LOGGER.debug( "tty_db_role structure retrieved" );
for ( int i = 0 ; i < moddedDbRoles.length ; i++ )
{
moddedDbRoles[i] = new STRUCT( tyDbRole , getConnection(), moddedRoles.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_USER_ROLE_MGR" );
dbCstmt.setString( 2, "PR_UPDATE_STANDARD_USER_ROLES" );
dbCstmt.setInt( 3, pAurId );
dbCstmt.setARRAY( 4, new ARRAY( ttyDbRole , getConnection() , moddedDbRoles ) );
LOGGER.debug( "DB params registered" );
dbCstmt.execute();
LOGGER.debug( "DB statement executed" );
}
catch ( SQLException sqle )
{
LOGGER.error
(
"SQL Exception whilst setting user roles -> {}; error code -> {}; sql state -> {}; pAurId -> {}"
, new Object []
{
sqle.getMessage()
, sqle.getErrorCode()
, sqle.getSQLState()
, pAurId
}
);
throw sqle;
}
finally
{
CloseResource.close( dbCstmt );
}
}
}