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; }
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; }