public JwSqlSelect getUserAirportAndRolesAndRoleGroupsSelect(
String airportCode, String roleOrRoleGroup) {
boolean isRole = AcRoleEnum.getFromCode(roleOrRoleGroup) != null;
String roleCode = isRole ? roleOrRoleGroup : null;
String roleGroupCode = isRole ? null : roleOrRoleGroup;
AcUserAirportDb ua = null;
String x = "x";
String roles = "roles";
String roleGroups = "roleGroups";
String airports = "airports";
JwSqlSelect st = createSelect();
st.selectString(x, LOGIN);
st.selectString(x, FIRST_NAME);
st.selectString(x, LAST_NAME);
st.selectString(x, roles);
st.selectString(x, roleGroups);
st.selectGroupConcat(ua.ALIAS, ua.AIRPORT_CODE, null, "1", null, airports);
st.from(getUserRolesAndRoleGroupsSelect(roles, roleGroups), x);
st.fromLeftOuterJoin(null, x, ua.TABLE, ua.ALIAS, LOGIN, ua.LOGIN);
st.groupBy(1, 2, 3, 4, 5);
if (JwUtility.hasValue(airportCode)) st.having().contains(null, airports, airportCode);
if (JwUtility.hasValue(roleCode)) st.having().contains(null, roles, roleCode);
if (JwUtility.hasValue(roleGroupCode)) st.having().contains(null, roleGroups, roleGroupCode);
return st;
}
public void populateSelectColumnsToCompose(JwSqlSelect st, String alias) {
st.selectInteger(alias, ID);
st.selectString(alias, PATH);
st.selectString(alias, FILE_NAME);
st.selectString(alias, DESCRIPTION);
st.selectTimestamp(alias, CREATED_UTC_TS);
}
public void populateSelectColumnsToCompose(JwSqlSelect st, String alias) {
st.selectInteger(alias, ID);
st.selectTimestamp(alias, MODIFIED_UTC_TS);
st.selectString(alias, USER);
st.selectInteger(alias, TYPE);
st.selectString(alias, TYPE_NAME);
st.selectString(alias, SUBJECT_1);
st.selectString(alias, SUBJECT_2);
st.selectString(alias, SUBJECT_3);
st.selectString(alias, OLD_VALUE);
st.selectString(alias, NEW_VALUE);
}
private JwSqlSelect getUserRolesAndRoleGroupsSelect(String roles, String roleGroups) {
AcUserRoleDb ur = null;
String x = "x";
String isRoleGroup = "isRoleGroup";
String groupRoleRoles = "groupRoleRoles";
JwSqlAdaptor adaptor = getAccess().getAdaptor();
JwSqlSimpleExprValue nullExpr = null;
JwSqlSimpleExprLiteral openParenExpr = new JwSqlSimpleExprLiteral(adaptor, "'('");
JwSqlSimpleExprLiteral closeParenExpr = new JwSqlSimpleExprLiteral(adaptor, "')'");
JwSqlSimpleExprColumn boolExpr = new JwSqlSimpleExprColumn(adaptor, isRoleGroup);
JwSqlSimpleExprColumn roleExpr = new JwSqlSimpleExprColumn(adaptor, null, ur.ROLE);
JwSqlSimpleExprColumn groupRoleRolesExpr =
new JwSqlSimpleExprColumn(adaptor, null, groupRoleRoles);
JwSqlSimpleExprFunctionConcat concat = new JwSqlSimpleExprFunctionConcat(adaptor);
concat.addExpression(roleExpr);
concat.addExpression(openParenExpr);
concat.addExpression(groupRoleRolesExpr);
concat.addExpression(closeParenExpr);
JwSqlSimpleExprFunctionIf roleCond;
roleCond = new JwSqlSimpleExprFunctionIf(boolExpr, nullExpr, roleExpr);
JwSqlSimpleExprFunctionGroupConcat roleConcat;
roleConcat = new JwSqlSimpleExprFunctionGroupConcat(roleCond, false);
roleConcat.setOrderBy(1, true);
JwSqlSimpleExprFunctionIf groupCond;
groupCond = new JwSqlSimpleExprFunctionIf(boolExpr, concat, nullExpr);
JwSqlSimpleExprFunctionGroupConcat groupRoleConcat;
groupRoleConcat = new JwSqlSimpleExprFunctionGroupConcat(groupCond, false);
groupRoleConcat.setOrderBy(1, true);
JwSqlSelect st = createSelect();
st.selectString(x, LOGIN);
st.selectString(x, FIRST_NAME);
st.selectString(x, LAST_NAME);
st.addExpression(
roleConcat, roles); // group_concat(if(isRoleGroup,null,role) order by 1) as roles
st.addExpression(
groupRoleConcat,
roleGroups); // group_concat(if(isRoleGroup,concat(role,'(',groupRoleRoles,')'),null) order
// by 1) as roleGroups
st.from(getUserRoleGroupsSelect(isRoleGroup, groupRoleRoles), x);
st.groupBy(1, 2, 3);
return st;
}
public void populateSelectColumnsToCompose(JwSqlSelect st, String alias) {
st.selectString(alias, LOGIN);
st.selectTimestamp(alias, CREATED_UTC_TS);
st.selectDate(alias, BLOCK_BEGIN_UTC_DT);
st.selectDate(alias, BLOCK_END_UTC_DT);
st.selectDate(alias, ROUTE_BEGIN_UTC_DT);
st.selectDate(alias, ROUTE_END_UTC_DT);
st.selectInteger(alias, ERROR);
}
private JwSqlSelect getUserRoleGroupsSelect(String isRoleGroup, String groupRoleRoles) {
AcUserRoleDb ur = null;
AcRoleGroupRoleDb rgr = null;
JwSqlSimpleExprColumn c =
new JwSqlSimpleExprColumn(_access.getAdaptor(), rgr.ALIAS, rgr.ROLE_GROUP_CODE);
JwSqlBooleanPrimaryIs isRoleGroupExpr = new JwSqlBooleanPrimaryIs(c, true);
JwSqlSelect st = createSelect();
st.selectString(ALIAS, LOGIN);
st.selectString(ALIAS, FIRST_NAME);
st.selectString(ALIAS, LAST_NAME);
st.selectString(ur.ALIAS, ur.ROLE);
st.addExpression(isRoleGroupExpr, isRoleGroup);
st.selectGroupConcat(rgr.ALIAS, rgr.ROLE, null, "1", null, groupRoleRoles);
st.from(TABLE, ALIAS);
st.from(ur.TABLE, ur.ALIAS);
st.fromLeftOuterJoin(null, ur.ALIAS, rgr.TABLE, rgr.ALIAS, ur.ROLE, rgr.ROLE_GROUP_CODE);
st.where().isTrue(ALIAS, ACTIVE);
st.where().isEqualColumn(ALIAS, LOGIN, ur.ALIAS, ur.LOGIN);
st.groupBy(1, 2, 3, 4, 5);
return st;
}
public void populateSelectColumnsToCompose(JwSqlSelect st, String alias) {
st.selectInteger(alias, ID);
st.selectString(alias, OPERATOR_CARRIER_CODE);
st.selectString(alias, OPERATOR_NUMBER);
st.selectString(alias, RELATIONSHIP);
st.selectString(alias, ORIGIN_AIRPORT_CODE);
st.selectString(alias, DESTINATION_AIRPORT_CODE);
st.selectTime(alias, DEPARTURE_LOCAL_TM);
st.selectTime(alias, ARRIVAL_LOCAL_TM);
st.selectInteger(alias, DAY_OFFSET);
st.selectDate(alias, BOUNDING_START_LOCAL_DT);
st.selectDate(alias, BOUNDING_END_LOCAL_DT);
st.selectDayFrequency(alias, FREQUENCY);
st.selectString(alias, EQUIPMENT_CODE);
st.selectString(alias, SOURCE);
st.selectString(alias, ABSOLUTE_SOURCE);
st.selectBoolean(alias, CANCEL);
st.selectTimestamp(alias, CREATED_UTC_TS);
st.selectInteger(alias, SEQUENCE);
}
public void populateSelectColumnsToCompose(JwSqlSelect st, String alias) {
st.selectString(alias, NAME);
}
