<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data->CUSTOMER->ID)) {
        $additional = " AND cc_party_id = :customer_id ";
    }

    $sql = "SELECT to_char(cc_received_on, 'MON-RR') MONTH,
                   COUNT(CC_ID) total_complaints,
                   SUM(CASE WHEN cc_closed_by IS NOT NULL THEN 1 ELSE 0 END) closed_complaint,
                   ROUND(((SUM(CASE WHEN cc_closed_by IS NOT NULL THEN 1 ELSE 0 END) / COUNT(CC_ID)) * 100),1) closed_percentage,
                   SUM(CASE WHEN cc_closed_by IS NULL THEN 1 ELSE 0 END) open_complaint,
                   ROUND(((SUM(CASE WHEN cc_closed_by IS NULL THEN 1 ELSE 0 END) / COUNT(CC_ID)) * 100),1) open_percentage,
                   ROUND(AVG(CASE WHEN cc_closed_by IS NOT NULL THEN (cc_closed_by - cc_received_on) ELSE 0 END),0) res_day
              FROM YTYCUSTOM.customer_complaint_hd
             WHERE TRUNC(cc_received_on,'MONTH') BETWEEN TO_DATE(:from_dt, 'YYYY-MM-DD') AND TO_DATE(:to_dt, 'YYYY-MM-DD')
                   ".$additional."
             GROUP BY to_char(cc_received_on, 'MON-RR')
             ORDER BY TO_DATE(TO_CHAR(cc_received_on, 'MON-RR'), 'MON-RR')";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':from_dt', date('Y-m-d', strtotime($data->FROM_DATE. ' +1 day')));
    oci_bind_by_name($sqlarr, ':to_dt', date('Y-m-d', strtotime($data->TO_DATE. ' +1 day')));
    if (!empty($data->CUSTOMER->ID)) {
     oci_bind_by_name($sqlarr, ':customer_id', $data->CUSTOMER->ID);
    }
    $r = oci_execute($sqlarr);

    if (!$r) {
         $e = oci_error($sqlarr);
         print json_encode($e['message']);
    }
    
    oci_fetch_all($sqlarr, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    
    $result[] = [
         "message" => $resMsg,
         "data" => $rows
    ];

    print json_encode($result);
?>