public static void find(Long id) {
Department department = Department.findById(id);
List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
StringBuffer query = new StringBuffer();
query.append("SELECT DATE_FORMAT(c.createdAt, '%Y-%m') as Month, ");
query.append("SUM(c.status = 1) AS Received, ");
query.append("SUM(c.status = 2) AS InProgress, ");
query.append("SUM(c.status = 4) AS Resolved, ");
query.append(
"SUM(c.status = 2 && c.slaProceedDays - DATEDIFF(CURDATE(),c.slaProceedDate) < 0) / SUM(c.status = 2) * 100 AS DelayedPrecentage ");
query.append("FROM Complaint c RIGHT JOIN Department d ON c.department_id = d.id ");
query.append("WHERE d.id = " + id + " ");
query.append("GROUP BY c.department_id, Month ");
query.append("ORDER BY Month DESC LIMIT 6 ");
List<Object[]> data = Model.em().createNativeQuery(query.toString()).getResultList();
for (Object[] o : data) {
String month = o[0].toString();
Object Received = o[1];
if (Received == null) Received = 0;
Object InProgress = o[2];
if (InProgress == null) InProgress = 0;
Object Resolved = o[3];
if (Resolved == null) Resolved = 0;
Object DelayedPercentage = o[4];
if (DelayedPercentage == null) DelayedPercentage = 0.0;
Map<String, Object> result = new HashMap<String, Object>();
result.put("Month", month);
result.put("Received", Received);
result.put("InProgress", InProgress);
result.put("Resolved", Resolved);
result.put("Delayed", DelayedPercentage);
results.add(result);
}
Map<String, Object> o = new HashMap<String, Object>();
o.put("DepartmentID", department.id);
o.put("DepartmentName", department.name);
if (department.head != null) o.put("Head", department.head.name);
if (department.head.avatar != null) o.put("Photo", department.head.avatar.id);
o.put("results", results);
renderJSON(o);
}
public static void result() {
List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
StringBuffer query = new StringBuffer();
query.append("SELECT d.id, ");
query.append("SUM(c.status = 1) AS Received, ");
query.append("SUM(c.status = 2) AS InProgress, ");
query.append("SUM(c.status = 4) AS Resolved, ");
query.append(
"SUM(c.status = 2 && c.slaProceedDays - DATEDIFF(CURDATE(),c.slaProceedDate) < 0) / SUM(c.status = 2) * 100 AS DelayedPrecentage ");
query.append(
"FROM Complaint c RIGHT JOIN Department d ON c.department_id = d.id GROUP BY c.department_id");
List<Object[]> data = Model.em().createNativeQuery(query.toString()).getResultList();
for (Object[] o : data) {
Department department = Department.findById(Long.parseLong(o[0].toString()));
if (department.status.equalsIgnoreCase(Constant.ACTIVE)) {
Object Received = o[1];
if (Received == null) Received = 0;
Object InProgress = o[2];
if (InProgress == null) InProgress = 0;
Object Resolved = o[3];
if (Resolved == null) Resolved = 0;
Object DelayedPercentage = o[4];
if (DelayedPercentage == null) DelayedPercentage = 0.0;
Map<String, Object> result = new HashMap<String, Object>();
result.put("DepartmentID", department.id);
result.put("DepartmentName", department.name);
if (department.head != null) {
result.put("Head", department.head.name);
if (department.head.avatar != null) result.put("Photo", department.head.avatar.id);
}
result.put("Received", Received);
result.put("InProgress", InProgress);
result.put("Resolved", Resolved);
result.put("Delayed", DelayedPercentage);
results.add(result);
}
}
Map<String, Object> o = new HashMap<String, Object>();
o.put("results", results);
renderJSON(o);
}