コード例 #1
0
ファイル: AcUserDb.java プロジェクト: pabplanalp/pvmail
  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;
  }
コード例 #2
0
ファイル: AcUserDb.java プロジェクト: pabplanalp/pvmail
  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;
  }