<?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 = null;
    } else {
        $location = $data->location;
    }

    $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,
                   RTRIM(TO_CHAR( CASE WHEN SUM(prod.prod_mt) = 0 THEN 0 ELSE ROUND((SUM(esg.ghg) / SUM(prod.prod_mt)),2) END , 'FM990.99'), '.') ghg_intensity,
                   RTRIM(TO_CHAR( CASE WHEN SUM(prod.prod_mt) = 0 THEN 0 ELSE ROUND((SUM(esg.energy) / SUM(prod.prod_mt)),2) END , 'FM990.99'), '.') energy_intensity,
                   RTRIM(TO_CHAR( CASE WHEN SUM(prod.prod_mt) = 0 THEN 0 ELSE ROUND((SUM(esg.water) / SUM(prod.prod_mt)),2) END , 'FM990.99'), '.') water_intensity,
                   RTRIM(TO_CHAR( CASE WHEN SUM(prod.prod_mt) = 0 THEN 0 ELSE ROUND((SUM(esg.waste) / SUM(prod.prod_mt)),2) END , 'FM990.99'), '.') waste_intensity,
                   RTRIM(TO_CHAR( CASE WHEN SUM(esg.ehs_manhour) = 0 THEN 0 ELSE ROUND(((SUM(esg.ehs_ltc) * 1000000) / SUM(esg.ehs_manhour)),2) END , 'FM990.99'), '.') ltifr,
                   RTRIM(TO_CHAR( CASE WHEN SUM(esg.ehs_manhour) = 0 THEN 0 ELSE ROUND(((SUM(esg.ehs_acc) * 1000000) / SUM(esg.ehs_manhour)),2) END , 'FM990.99'), '.') trifr,
                   RTRIM(TO_CHAR( tar.emission , 'FM990.99'), '.') ghg_target, 
                   RTRIM(TO_CHAR( tar.energy , 'FM990.99'), '.') energy_target, 
                   RTRIM(TO_CHAR( tar.water , 'FM990.99'), '.') water_target,
                   RTRIM(TO_CHAR( tar.waste , 'FM990.99'), '.') waste_target, 
                   RTRIM(TO_CHAR( tar.ltifr , 'FM990.99'), '.') ltifr_target, 
                   RTRIM(TO_CHAR( tar.trifr , 'FM990.99'), '.') trifr_target
              FROM (SELECT to_char(entry_mon, 'MON-YYYY') yr, entry_plan,
                            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') = :yr
                        AND entry_plan = NVL(:entry_plan, entry_plan)
                      GROUP BY to_char(entry_mon, 'MON-YYYY'), entry_plan) esg,
                   (SELECT y.n yr, y.location, a.mt prod_mt
                       FROM (SELECT n, location
                                FROM (SELECT to_char(to_date(:yr, 'YYYY') + numtoyminterval(LEVEL, 'month'),'MON') || '-' || :yr AS n
                                        FROM dual
                                      CONNECT BY LEVEL <= 12),
                                     (SELECT 'GPA' location FROM dual
                                        UNION
                                        SELECT 'GPL' FROM dual
                                        UNION
                                        SELECT 'YTYL' FROM dual
                                        UNION
                                        SELECT 'YTYA' FROM dual)) y,
                            (SELECT to_char(PROD_DATE, 'MON-YYYY') yr,
                                    case ORG_ID when 84 then 'YTYL' 
                                          when 122 then 'YTYA'
                                          when 86 then 'GPA'
                                          when 102 then 'GPL' end org_id,
                                     NVL((ROUND(SUM(wt)) / 1000), 0) mt
                                FROM production.yty_vw_pr_dashboard_28
                               WHERE to_char(PROD_DATE,'YYYY') = :yr
                               GROUP BY to_char(PROD_DATE, 'MON-YYYY'), ORG_ID) a
                      WHERE y.n = a.yr(+)
                        AND y.location = a.org_id(+)) prod,
                   ytycustom.esg_target tar
             WHERE esg.yr = prod.yr
               AND esg.entry_plan = prod.location
               AND SUBSTR(esg.yr,-4) = tar.year(+)
             GROUP BY esg.yr, tar.emission, tar.energy, tar.water, tar.waste, tar.ltifr, tar.trifr
             ORDER BY to_date(esg.yr,'MON-YYYY')";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':yr', $data->mon);
    oci_bind_by_name($sqlarr, ':entry_plan', $location);
    $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);
?>