@Override
public String generateNativeQueryForChart() {
String sql = null;
if (chartReportOption.equals("marking")) {
sql =
"SELECT CASE WHEN (mc.ENABLED_CHR = '1' AND md.value_chr IS NOT NULL) THEN md.VALUE_CHR ELSE '"
+ i18n.iValue("web.client.label.NoDescription")
+ "' END, "
+ "Q.* "
+ "FROM ( "
+ " SELECT SV.COLUMN1_CHR, COUNT(*) AS COUNT "
+ " FROM SERVICE_VALUE_DETAIL svd "
+ " INNER JOIN SERVICE_VALUE sv ON (svd.COD_SERVICEVALUE = sv.SERVICEVALUE_COD) "
+ " INNER JOIN USERPHONE u ON (sv.COD_USERPHONE = u.USERPHONE_COD) "
+ " INNER JOIN MESSAGE m ON (m.MESSAGE_COD = sv.COD_MESSAGE) "
+ " WHERE 1 = 1 "
+ " AND SV.COD_SERVICE = 15 AND SVD.COLUMN2_CHR IS NUll "
+ " %s "
+ " GROUP BY SV.COLUMN1_CHR ) Q "
+ " LEFT JOIN META_DATA md ON (md.COD_CLIENT = %s AND md.COD_META = 4 AND md.COD_MEMBER = 1 AND TRIM(md.CODE_CHR) = TRIM(Q.COLUMN1_CHR)) "
+ " LEFT JOIN META_CLIENT mc ON (mc.COD_CLIENT = %s AND mc.COD_META = 4) ";
Long codClient =
SecurityBean.getInstance().getLoggedInUserClient().getClient().getClientCod();
sql = String.format(sql, getWhereReport(), codClient, codClient);
} else if (chartReportOption.equals("nomarking")) {
sql =
"SELECT CASE WHEN (mc.ENABLED_CHR = '1' AND md.value_chr IS NOT NULL) THEN md.VALUE_CHR ELSE '"
+ i18n.iValue("web.client.label.NoDescription")
+ "' END, "
+ "Q.* "
+ "FROM ( "
+ " SELECT SV.COLUMN1_CHR, COUNT(*) AS COUNT "
+ " FROM SERVICE_VALUE_DETAIL svd "
+ " INNER JOIN SERVICE_VALUE sv ON (svd.COD_SERVICEVALUE = sv.SERVICEVALUE_COD) "
+ " INNER JOIN USERPHONE u ON (sv.COD_USERPHONE = u.USERPHONE_COD) "
+ " INNER JOIN MESSAGE m ON (m.MESSAGE_COD = sv.COD_MESSAGE) "
+ " WHERE 1 = 1 "
+ " AND SV.COD_SERVICE = 15 AND SVD.COLUMN2_CHR IS NOT NUll "
+ " %s "
+ " GROUP BY SV.COLUMN1_CHR ) Q "
+ " LEFT JOIN META_DATA md ON (md.COD_CLIENT = %s AND md.COD_META = 4 AND md.COD_MEMBER = 1 AND TRIM(md.CODE_CHR) = TRIM(Q.COLUMN1_CHR)) "
+ " LEFT JOIN META_CLIENT mc ON (mc.COD_CLIENT = %s AND mc.COD_META = 4) ";
Long codClient =
SecurityBean.getInstance().getLoggedInUserClient().getClient().getClientCod();
sql = String.format(sql, getWhereReport(), codClient, codClient);
}
return sql;
}
public List<RoleClient> getRoleclientList() {
if (roleclientList == null) {
roleclientList =
roleclientFacade.getAllEnabledRoleclientByClient(
SecurityBean.getInstance().getLoggedInUserClient().getClient().getClientCod());
}
return roleclientList;
}
public Client getClient() {
if (client == null) {
client = SecurityBean.getInstance().getLoggedInUserClient().getClient();
}
return client;
}