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

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

    if ($data->LOCATION != "ALL") {
        $location = " AND entry_plan = '".$data->LOCATION."'";
    }

    if (!empty($data->MONTH)) {
        $dt = " AND trunc(entry_mon,'MONTH') = trunc(to_date('".date('Y-m-d', strtotime($data->MONTH. ' +1 day'))."', 'YYYY-MM-DD'), 'MONTH') ";
    }

    $sql = "SELECT mon_period, NVL(SUM(ghg),0) ghg, SUM(energy) energy, SUM(water) water, SUM(waste) waste, SUM(ehs_ltc) ehs_ltc, SUM(ehs_acc) ehs_acc, SUM(ehs_manhour) ehs_manhour
              FROM (   
                        SELECT TO_CHAR(entry_mon,'Mon YYYY') mon_period,
                                SUM(CASE WHEN esg_input_id = 77 THEN pvalue END) ghg,
                                SUM(CASE WHEN esg_input_id = 27 THEN pvalue END) energy,
                                SUM(CASE WHEN esg_input_id = 99 THEN pvalue END) water,
                                SUM(CASE WHEN esg_input_id = 124 THEN pvalue END) waste,
                                SUM(CASE WHEN esg_input_id IN (101) THEN pvalue END) ehs_ltc,
                                SUM(CASE WHEN esg_input_id IN (100, 101, 102, 125) THEN pvalue END) ehs_acc,
                                SUM(CASE WHEN esg_input_id = 103 THEN pvalue END) ehs_manhour
                            FROM ytycustom.esg_input_data
                            WHERE esg_input_id IN (27, 77, 99, 124, 100, 101, 102, 103, 125) 
                            ".$dt."
                        GROUP BY TO_CHAR(entry_mon,'Mon YYYY') 
                    ) 
           GROUP BY mon_period";

    $sqlarr = oci_parse($dbcon , $sql);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
        $e = oci_error($sqlarr);
        $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_stats, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_stats
    ];

    print json_encode($result);
?>