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

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

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

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

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

        }
    }

    $sql = "SELECT 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."
               ".$location;

    $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);
?>