<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    //Production
    switch($data->LOCATION) {
        case "GPA": 
            $location = " AND ORG_ID = '86' ";
            break;

        case "GPL":
            $location = " AND ORG_ID = '102' ";
            break;

        case "YTYL":
            $location = " AND ORG_ID = '84' ";
            break;

        case "YTYA":
            $location = " AND ORG_ID = '122' ";
            break;

        default:
            $location = " ";
            break;
    }

    // if (!empty($data->MONTH)) {
    //     $dt = " AND trunc(tmb_julian_date, 'MONTH') = 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(tmb_julian_date,'YEAR') = trunc(to_date('".date('Y-m-d', strtotime($data->YEAR. ' +1 day'))."', 'YYYY-MM-DD'), 'YEAR')  
    //                 AND to_char(tmb_julian_date,'Q') = '".$data->QUARTER."' ";

    //     } else {
    //         if (!empty($data->YEAR)) {
    //             $dt = " AND trunc(tmb_julian_date,'YEAR') = trunc(to_date('".date('Y-m-d', strtotime($data->YEAR. ' +1 day'))."', 'YYYY-MM-DD'), 'YEAR') ";
    //         }

    //     }
    // }

    if (!empty($data->YEAR)) {
        $dt = " AND to_char(PROD_DATE,'YYYY') = '".$data->YEAR."' ";
    }

    $sql = "SELECT NVL((ROUND(SUM(wt)) / 1000), 0) mt, trunc(PROD_DATE, 'MONTH') mon,
                   to_char(trunc(PROD_DATE, 'MONTH'), 'Mon') period,
                   to_char(trunc(PROD_DATE, 'MONTH'), 'MM') pointer
              FROM production.yty_vw_pr_dashboard_28
             WHERE 1 = 1 
                   ".$dt." 
                   ".$location."
             GROUP BY trunc(PROD_DATE, 'MONTH')
             ORDER BY 2";

    $sqlarr = oci_parse($dbcon , $sql);
    $r = oci_execute($sqlarr);

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_prod, null, null, OCI_FETCHSTATEMENT_BY_ROW);
    
    //Widget
    if (!empty($data->LOCATION)) {
        if ($data->LOCATION != "ALL") {
            $Wlocation = " AND entry_plan = '".$data->LOCATION."'";
        }
    }

    // if (!empty($data->MONTH)) {
    //     $Wdt = " 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)) {
    //         $Wdt = " 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)) {
    //             $Wdt = " AND trunc(entry_mon,'YEAR') = trunc(to_date('".date('Y-m-d', strtotime($data->YEAR. ' +1 day'))."', 'YYYY-MM-DD'), 'YEAR') ";
    //         }

    //     }
    // }

    if (!empty($data->YEAR)) {
        $Wdt = " AND to_char(entry_mon,'YYYY') = '".$data->YEAR."' ";
    }

    //Raw Input
    $sql = "SELECT *
              FROM (SELECT to_char(a.entry_mon, 'Mon') entry_period, a.esg_input_id id,
                            a.param_name, a.uom_main, a.pvalue, a.sort_seq, a.gwp_name
                       FROM ytycustom.esg_input_data a, ytycustom.esg_category_master b
                      WHERE a.esg_category_id = b.id
                        AND NVL(b.inactive_flag, 'N') = 'N'
                        AND b.group_name = 'Emission_Detail'
                        ".$Wdt." 
                        ".$Wlocation.")
            PIVOT(SUM(pvalue)
               FOR entry_period IN('Jan' AS \"Jan\",
                                   'Feb' AS \"Feb\",
                                   'Mar' AS \"Mar\",
                                   'Apr' AS \"Apr\",
                                   'May' AS \"May\",
                                   'Jun' AS \"Jun\",
                                   'Jul' AS \"Jul\",
                                   'Aug' AS \"Aug\",
                                   'Sep' AS \"Sep\",
                                   'Oct' AS \"Oct\",
                                   'Nov' AS \"Nov\",
                                   'Dec' AS \"Dec\"))
             ORDER BY sort_seq";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':entryplan', $data->LOCATION);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_raw, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //GHG
    $sql = "SELECT *
              FROM (SELECT to_char(a.entry_mon, 'Mon') entry_period, a.esg_input_id id,
                            a.param_name, a.uom_main, a.pvalue, a.sort_seq
                       FROM ytycustom.esg_input_data a, ytycustom.esg_category_master b
                      WHERE a.esg_category_id = b.id
                        AND NVL(b.inactive_flag, 'N') = 'N'
                        AND b.group_name = 'Emission'
                        ".$Wdt." 
                        ".$Wlocation.")
            PIVOT(SUM(pvalue)
               FOR entry_period IN('Jan' AS \"Jan\",
                                   'Feb' AS \"Feb\",
                                   'Mar' AS \"Mar\",
                                   'Apr' AS \"Apr\",
                                   'May' AS \"May\",
                                   'Jun' AS \"Jun\",
                                   'Jul' AS \"Jul\",
                                   'Aug' AS \"Aug\",
                                   'Sep' AS \"Sep\",
                                   'Oct' AS \"Oct\",
                                   'Nov' AS \"Nov\",
                                   'Dec' AS \"Dec\"))
             ORDER BY sort_seq";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':entryplan', $data->LOCATION);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_ghg, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Energy
    $sql = "SELECT *
              FROM (SELECT to_char(a.entry_mon, 'Mon') entry_period, a.esg_input_id id,
                            a.param_name, a.uom_main, a.pvalue, a.sort_seq
                       FROM ytycustom.esg_input_data a, ytycustom.esg_category_master b
                      WHERE a.esg_category_id = b.id
                        AND NVL(b.inactive_flag, 'N') = 'N'
                        AND b.group_name = 'Energy'
                        ".$Wdt." 
                        ".$Wlocation.")
            PIVOT(SUM(pvalue)
               FOR entry_period IN('Jan' AS \"Jan\",
                                   'Feb' AS \"Feb\",
                                   'Mar' AS \"Mar\",
                                   'Apr' AS \"Apr\",
                                   'May' AS \"May\",
                                   'Jun' AS \"Jun\",
                                   'Jul' AS \"Jul\",
                                   'Aug' AS \"Aug\",
                                   'Sep' AS \"Sep\",
                                   'Oct' AS \"Oct\",
                                   'Nov' AS \"Nov\",
                                   'Dec' AS \"Dec\"))
             ORDER BY sort_seq";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':entryplan', $data->LOCATION);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_energy, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Water
    $sql = "SELECT *
              FROM (SELECT to_char(a.entry_mon, 'Mon') entry_period, a.esg_input_id id,
                            a.param_name, a.uom_main, a.pvalue, a.sort_seq
                       FROM ytycustom.esg_input_data a, ytycustom.esg_category_master b
                      WHERE a.esg_category_id = b.id
                        AND NVL(b.inactive_flag, 'N') = 'N'
                        AND b.group_name = 'Water'
                        ".$Wdt." 
                        ".$Wlocation.")
            PIVOT(SUM(pvalue)
               FOR entry_period IN('Jan' AS \"Jan\",
                                   'Feb' AS \"Feb\",
                                   'Mar' AS \"Mar\",
                                   'Apr' AS \"Apr\",
                                   'May' AS \"May\",
                                   'Jun' AS \"Jun\",
                                   'Jul' AS \"Jul\",
                                   'Aug' AS \"Aug\",
                                   'Sep' AS \"Sep\",
                                   'Oct' AS \"Oct\",
                                   'Nov' AS \"Nov\",
                                   'Dec' AS \"Dec\"))
             ORDER BY sort_seq";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':entryplan', $data->LOCATION);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_water, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Waste
    $sql = "SELECT *
              FROM (SELECT to_char(a.entry_mon, 'Mon') entry_period, a.esg_input_id id,
                            a.param_name, a.uom_main, a.pvalue, a.sort_seq
                       FROM ytycustom.esg_input_data a, ytycustom.esg_category_master b
                      WHERE a.esg_category_id = b.id
                        AND NVL(b.inactive_flag, 'N') = 'N'
                        AND b.group_name = 'Waste'
                        ".$Wdt." 
                        ".$Wlocation.")
            PIVOT(SUM(pvalue)
               FOR entry_period IN('Jan' AS \"Jan\",
                                   'Feb' AS \"Feb\",
                                   'Mar' AS \"Mar\",
                                   'Apr' AS \"Apr\",
                                   'May' AS \"May\",
                                   'Jun' AS \"Jun\",
                                   'Jul' AS \"Jul\",
                                   'Aug' AS \"Aug\",
                                   'Sep' AS \"Sep\",
                                   'Oct' AS \"Oct\",
                                   'Nov' AS \"Nov\",
                                   'Dec' AS \"Dec\"))
             ORDER BY sort_seq";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':entryplan', $data->LOCATION);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_waste, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    //Safety
    $sql = "SELECT *
              FROM (SELECT to_char(a.entry_mon, 'Mon') entry_period, a.esg_input_id id,
                            a.param_name, a.uom_main, a.pvalue, a.sort_seq
                       FROM ytycustom.esg_input_data a, ytycustom.esg_category_master b
                      WHERE a.esg_category_id = b.id
                        AND NVL(b.inactive_flag, 'N') = 'N'
                        AND b.group_name = 'EHS'
                        ".$Wdt." 
                        ".$Wlocation.")
            PIVOT(SUM(pvalue)
               FOR entry_period IN('Jan' AS \"Jan\",
                                   'Feb' AS \"Feb\",
                                   'Mar' AS \"Mar\",
                                   'Apr' AS \"Apr\",
                                   'May' AS \"May\",
                                   'Jun' AS \"Jun\",
                                   'Jul' AS \"Jul\",
                                   'Aug' AS \"Aug\",
                                   'Sep' AS \"Sep\",
                                   'Oct' AS \"Oct\",
                                   'Nov' AS \"Nov\",
                                   'Dec' AS \"Dec\"))
             ORDER BY sort_seq";

    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':entryplan', $data->LOCATION);
    $r = oci_execute($sqlarr);
    $resMsg = "OK";

    if (!$r) {
         $e = oci_error($sqlarr);
         $resMsg = json_encode($e['message']);
    }
    oci_fetch_all($sqlarr, $rows_safety, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    $result[] = [
        "message" => $resMsg,
        "prod" => $rows_prod,
        "raw" => $rows_raw,
        "ghg" => $rows_ghg,
        "energy" => $rows_energy,
        "water" => $rows_water,
        "waste" => $rows_waste,
        "safety" => $rows_safety
    ];

    print json_encode($result);
?>