<?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,
                   RTRIM(TO_CHAR( CASE WHEN SUM(bs.prod_mt) = 0 THEN 0 ELSE ROUND((SUM(bs.emission) / SUM(bs.prod_mt)),2) END , 'FM990.99'), '.') base_ghg,
                   RTRIM(TO_CHAR( CASE WHEN SUM(bs.prod_mt) = 0 THEN 0 ELSE ROUND((SUM(bs.energy) / SUM(bs.prod_mt)),2) END , 'FM990.99'), '.') base_energy,
                   RTRIM(TO_CHAR( CASE WHEN SUM(bs.prod_mt) = 0 THEN 0 ELSE ROUND((SUM(bs.water) / SUM(bs.prod_mt)),2) END , 'FM990.99'), '.') base_water,
                   RTRIM(TO_CHAR( CASE WHEN SUM(bs.prod_mt) = 0 THEN 0 ELSE ROUND((SUM(bs.waste) / SUM(bs.prod_mt)),2) END , 'FM990.99'), '.') base_waste,
                   0 base_ltifr,
                   0 base_trifr
              FROM (SELECT yr, entry_plan, SUM(ghg) 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, '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 trunc(entry_mon, 'YEAR') BETWEEN trunc(to_date('".date('Y-m-d', strtotime($data->YEAR_FROM. ' +1 day'))."', 'YYYY-MM-DD'), 'YEAR') AND trunc(to_date('".date('Y-m-d', strtotime($data->YEAR_TO. ' +1 day'))."', 'YYYY-MM-DD'), 'YEAR')
                              AND entry_plan = NVL(:entry_plan, entry_plan)
                            GROUP BY to_char(entry_mon, 'YYYY'), entry_plan
                           UNION
                           SELECT to_char(YEAR) yr, location, emission, energy, water, waste, ltifr, trifr,
                                  0
                             FROM ytycustom.esg_additional
                            WHERE trunc(to_date(YEAR,'YYYY'),'YEAR') BETWEEN trunc(to_date('".date('Y-m-d', strtotime($data->YEAR_FROM. ' +1 day'))."', 'YYYY-MM-DD'), 'YEAR') AND trunc(to_date('".date('Y-m-d', strtotime($data->YEAR_TO. ' +1 day'))."', 'YYYY-MM-DD'), 'YEAR')
                              AND location = NVL(:entry_plan, location))
                         GROUP BY yr, entry_plan    
                        ) esg,
                   (SELECT y.n yr, y.location,
                            CASE
                              WHEN y.n BETWEEN 2017 AND 2020 THEN
                               b.prod_mt
                              ELSE
                               a.mt
                            END prod_mt
                       FROM (SELECT n, location
                                FROM (SELECT rownum n FROM dual CONNECT BY LEVEL <= TO_CHAR(SYSDATE,'YYYY')),
                                     (SELECT 'GPA' location FROM dual
                                        UNION
                                        SELECT 'GPL' FROM dual
                                        UNION
                                        SELECT 'YTYL' FROM dual
                                        UNION
                                        SELECT 'YTYA' FROM dual)
                               WHERE n >= 2017) y,
                            (SELECT to_char(PROD_DATE, '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 1=1
                               GROUP BY to_char(PROD_DATE, 'YYYY'), ORG_ID) a,
                            (SELECT YEAR, location, SUM(prod_mt) prod_mt
                                FROM ytycustom.esg_additional
                               GROUP BY YEAR, location) b
                      WHERE y.n = a.yr(+)
                        AND y.location = a.org_id(+)
                        AND y.n = b.year(+)
                        AND y.location = b.location(+)) prod,
                   ytycustom.esg_target tar,
                   (SELECT YEAR, location, energy, emission, water, waste, ltifr, trifr, prod_mt
                      FROM ytycustom.esg_additional
                     WHERE YEAR = '2017') bs
             WHERE esg.yr = prod.yr
               AND esg.entry_plan = prod.location
               AND esg.yr = tar.year(+)
               AND esg.entry_plan = bs.location(+)
             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);
    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);
?>