"dateFrom and dateTo are required"]); exit; } // Only count entries that have actually been submitted, not drafts. $plantFilter = ""; if ($plant === 'YTYA' || $plant === 'YTYL') { $plantFilter = " AND KZ_PLANT = :KZ_PLANT "; } //--------------------------------------------------------------------- // YTYA / YTYL totals for the selected range //--------------------------------------------------------------------- $sqlCounts = "SELECT KZ_PLANT, COUNT(*) AS CNT FROM KAIZEN_ENTRY WHERE KZ_STATUS = 'SUBMITTED' AND CREATED_DATE BETWEEN TO_DATE(:DATE_FROM, 'YYYY-MM-DD') AND TO_DATE(:DATE_TO, 'YYYY-MM-DD') $plantFilter GROUP BY KZ_PLANT"; $stmtCounts = oci_parse($dbcon, $sqlCounts); oci_bind_by_name($stmtCounts, ':DATE_FROM', $dateFrom); oci_bind_by_name($stmtCounts, ':DATE_TO', $dateTo); if ($plantFilter) { oci_bind_by_name($stmtCounts, ':KZ_PLANT', $plant); } $rCounts = oci_execute($stmtCounts, OCI_NO_AUTO_COMMIT); if (!$rCounts) { $e = oci_error($stmtCounts); http_response_code(500); print json_encode(["error" => $e['message']]); exit; } $ytya = 0; $ytyl = 0; while ($row = oci_fetch_assoc($stmtCounts)) { if ($row['KZ_PLANT'] === 'YTYA') $ytya = (int) $row['CNT']; if ($row['KZ_PLANT'] === 'YTYL') $ytyl = (int) $row['CNT']; } //--------------------------------------------------------------------- // Monthly submission counts for the selected range (Jan..Dec of range) //--------------------------------------------------------------------- $sqlMonthly = "SELECT TO_CHAR(CREATED_DATE, 'Mon') AS MONTH_LABEL, TO_CHAR(CREATED_DATE, 'MM') AS MONTH_NUM, COUNT(*) AS CNT FROM KAIZEN_ENTRY WHERE KZ_STATUS = 'SUBMITTED' AND CREATED_DATE BETWEEN TO_DATE(:DATE_FROM, 'YYYY-MM-DD') AND TO_DATE(:DATE_TO, 'YYYY-MM-DD') $plantFilter GROUP BY TO_CHAR(CREATED_DATE, 'Mon'), TO_CHAR(CREATED_DATE, 'MM') ORDER BY MONTH_NUM"; $stmtMonthly = oci_parse($dbcon, $sqlMonthly); oci_bind_by_name($stmtMonthly, ':DATE_FROM', $dateFrom); oci_bind_by_name($stmtMonthly, ':DATE_TO', $dateTo); if ($plantFilter) { oci_bind_by_name($stmtMonthly, ':KZ_PLANT', $plant); } $rMonthly = oci_execute($stmtMonthly, OCI_NO_AUTO_COMMIT); if (!$rMonthly) { $e = oci_error($stmtMonthly); http_response_code(500); print json_encode(["error" => $e['message']]); exit; } // Build a full 12-month skeleton for the range's year(s) so months with // zero submissions still render as a bar of height 0 instead of being // skipped from the x-axis. $monthNames = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']; $monthly = []; foreach ($monthNames as $i => $name) { $monthly[$i + 1] = ["month" => $name, "count" => 0]; } while ($row = oci_fetch_assoc($stmtMonthly)) { $monthNum = (int) $row['MONTH_NUM']; if (isset($monthly[$monthNum])) { $monthly[$monthNum]['count'] = (int) $row['CNT']; } } //--------------------------------------------------------------------- // Submission by Department — derived from the entry's Team Leader, // joined against EMPLOYEE_MASTER to get their department. // NOTE: adjust DEPARTMENT_NAME below to whatever the actual department // column is called on EMPLOYEE_MASTER in your schema. //--------------------------------------------------------------------- $sqlDept = "SELECT NVL(em.DEPARTMENT, 'Unassigned') AS DEPARTMENT, COUNT(*) AS CNT FROM KAIZEN_ENTRY e LEFT JOIN EMPLOYEE_MASTER em ON em.EMP_ID = e.KZ_TEAM_LEADER_EMP_ID WHERE e.KZ_STATUS = 'SUBMITTED' AND e.CREATED_DATE BETWEEN TO_DATE(:DATE_FROM, 'YYYY-MM-DD') AND TO_DATE(:DATE_TO, 'YYYY-MM-DD') " . ($plant === 'YTYA' || $plant === 'YTYL' ? " AND e.KZ_PLANT = :KZ_PLANT " : "") . " GROUP BY em.DEPARTMENT ORDER BY CNT DESC"; $stmtDept = oci_parse($dbcon, $sqlDept); oci_bind_by_name($stmtDept, ':DATE_FROM', $dateFrom); oci_bind_by_name($stmtDept, ':DATE_TO', $dateTo); if ($plantFilter) { oci_bind_by_name($stmtDept, ':KZ_PLANT', $plant); } $rDept = oci_execute($stmtDept, OCI_NO_AUTO_COMMIT); if (!$rDept) { $e = oci_error($stmtDept); http_response_code(500); print json_encode(["error" => $e['message']]); exit; } $byDepartment = []; while ($row = oci_fetch_assoc($stmtDept)) { $byDepartment[] = [ "department" => strtoupper($row['DEPARTMENT']), "count" => (int) $row['CNT'], ]; } $result = [ "ytya" => $ytya, "ytyl" => $ytyl, "monthly" => array_values($monthly), "byDepartment" => $byDepartment, "asOfLabel" => "Data as of " . date('M Y', strtotime($dateTo)), ]; print json_encode($result);