<?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') ";
    //             $unionQ = " UNION
    //                         SELECT emission, energy, water, waste, ltifr, trifr, 0
    //                           FROM ytycustom.esg_additional
    //                          WHERE location = '".$data->LOCATION."'
    //                            AND trunc(to_date(year,'YYYY'), 'YEAR') = trunc(to_date('".date('Y-m-d', strtotime($data->YEAR. ' +1 day'))."', 'YYYY-MM-DD'), 'YEAR') ";
    //         }

    //     }
    // }

    $sql = "SELECT esg.yr, SUM(prod.prod_mt) prod_mt, SUM(esg.ghg) ghg, SUM(esg.energy) energy,
                   SUM(esg.water) water, SUM(esg.waste) waste, SUM(esg.ehs_ltc) ehs_ltc,
                   SUM(esg.ehs_acc) ehs_acc, SUM(esg.ehs_manhour) ehs_manhour,
                   ROUND((SUM(esg.ghg) / SUM(prod.prod_mt)),2) ghg_intensity,
                   ROUND((SUM(esg.energy) / SUM(prod.prod_mt)),2) energy_intensity,
                   ROUND((SUM(esg.water) / SUM(prod.prod_mt)),2) water_intensity,
                   ROUND((SUM(esg.waste) / SUM(prod.prod_mt)),2) waste_intensity,
                   ROUND(((SUM(esg.ehs_ltc) * 1000000) / SUM(esg.ehs_manhour)),2) ltifr,
                   ROUND(((SUM(esg.ehs_acc) * 1000000) / SUM(esg.ehs_manhour)),2) trifr,
                   tar.emission ghg_target, tar.energy energy_target, tar.water water_target,
                   tar.waste waste_target, tar.ltifr ltifr_target, tar.trifr trifr_target
              FROM (SELECT to_char(entry_mon, 'YYYY') yr,
                            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)
                        AND TO_CHAR(entry_mon, 'YYYY') BETWEEN TO_CHAR(SYSDATE, 'YYYY') -3 AND TO_CHAR(SYSDATE, 'YYYY') -1
                      GROUP BY to_char(entry_mon, 'YYYY')
                     UNION
                     SELECT to_char(YEAR) yr, emission, energy, water, waste, ltifr, trifr,
                            0
                       FROM ytycustom.esg_additional
                      WHERE YEAR BETWEEN TO_CHAR(SYSDATE, 'YYYY') -3 AND TO_CHAR(SYSDATE, 'YYYY') -1) esg,
                   (SELECT y.n yr,
                            CASE
                              WHEN y.n BETWEEN 2017 AND 2020 THEN
                               b.prod_mt
                              ELSE
                               a.mt
                            END prod_mt
                       FROM (SELECT n
                                FROM (SELECT rownum n FROM dual CONNECT BY LEVEL <= 2025)
                               WHERE n >= 2017) y,
                            (SELECT to_char(PROD_DATE, 'YYYY') yr,
                                     NVL((ROUND(SUM(wt)) / 1000), 0) mt
                                FROM production.yty_vw_pr_dashboard_28
                               WHERE 1=1
                               GROUP BY to_char(PROD_DATE, 'YYYY')) a,
                            (SELECT YEAR, SUM(prod_mt) prod_mt
                                FROM ytycustom.esg_additional
                               GROUP BY YEAR) b
                      WHERE y.n = a.yr(+)
                        AND y.n = b.year(+)) prod,
                   ytycustom.esg_target tar
             WHERE esg.yr = prod.yr
               AND esg.yr = tar.year(+)
             GROUP BY esg.yr, tar.emission, tar.energy, tar.water, tar.waste, tar.ltifr, tar.trifr
             ORDER BY esg.yr";

    $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_trend, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_trend
    ];

    print json_encode($result);
?>