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