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

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

    if (isset($data->STAGE)) {
        if ($data->STAGE == '0') {
        
          $sql = " SELECT id, customer, customer_address, sku, glove_colour, glove_type, target_weight, glove_length,
                          glove_size, glove_surface, glove_donning, special_features, sku_requirement, cert_required, 
                          aql_requirement, volume_exp1, volume_remarks, price_exp, segment_territory, segment_oth,
                          geographical, country, stage1_remarks, close_on,
                          cost_of_cert, timing_cert, stage2_remarks,
                          expect_changes_form, formulation_remarks, expect_changes_proc, feasible_wo_dev, prod_dev_remarks,
                          cost_change, trial_req, qualification_req, OQ, PQ, 
                          timing_prototype, timing_trial, timing_oq, timing_pq, resources_required, technical_recommend,
                          discuss_with_ops, stage3_remarks, 
                          manufacture_review, trial_result, stage4_remarks,
                          final_signoff, stage5_remarks,
                          c_510k, astm_d6319, en_455, astm_en_455, iso_11193, as_nzs_4011, en_16523_1, ppe_eu,
                          astm_d6978, iso_10993_5, elution_test, heavy_metal, japan, china, eu, us, others_req, 
                          astm_f1671,endotoxin_lal, pyrogen, bioburden, silicone_oil, draize_95, as_nzs_4011_1,
                          coeficient_friction, bio_compatibility, sem, voc, grip_test, nfpa_1999, 
                          iso_10993_11, others_cc,
                          created_date, modified_date, stage_pos, 
                          MAX(case when status is null and stage_pos = 5 and approval_pos = 5 then 'SUBMITTED' else status end) status
                    FROM ( SELECT a.id, a.customer, a.customer_address, a.sku, a.glove_colour, a.glove_type, a.target_weight, a.glove_length,
                                  a.glove_size, a.glove_surface, a.glove_donning, a.special_features, a.sku_requirement, a.cert_required, 
                                  a.aql_requirement, a.volume_exp1, a.volume_remarks, a.price_exp, a.segment_territory, a.segment_oth,
                                  a.geographical, a.country, a.stage1_remarks, a.close_on,
                                  a.cost_of_cert, a.timing_cert, a.stage2_remarks,
                                  a.expect_changes_form, a.formulation_remarks, a.expect_changes_proc, a.feasible_wo_dev, a.prod_dev_remarks,
                                  a.cost_change, a.trial_req, a.qualification_req, case a.OQ when '1' then 'Yes' else 'No' end OQ, 
                                  case a.PQ when '1' then 'Yes' else 'No' end PQ, 
                                  a.timing_prototype, a.timing_trial, a.timing_oq, a.timing_pq, a.resources_required, a.technical_recommend,
                                  a.discuss_with_ops, a.stage3_remarks, 
                                  a.manufacture_review, a.trial_result, a.stage4_remarks,
                                  a.final_signoff, a.stage5_remarks,
                                  c.c_510k, c.astm_d6319, c.en_455, c.astm_en_455, c.iso_11193, c.as_nzs_4011, c.en_16523_1, c.ppe_eu,
                                  c.astm_d6978, c.iso_10993_5, c.elution_test, c.heavy_metal, c.japan, c.china, c.eu, c.us, c.others_req, 
                                  c.astm_f1671,c.endotoxin_lal, c.pyrogen, c.bioburden, c.silicone_oil, c.draize_95, c.as_nzs_4011_1,
                                  c.coeficient_friction, c.bio_compatibility, c.sem, c.voc, c.grip_test, c.nfpa_1999, 
                                  c.iso_10993_11, c.others_cc,
                                  a.created_date, case when a.stage_pos < 0 then a.approval_pos else a.stage_pos end stage_pos, 
                                  nvl(a.modified_date, a.created_date) modified_date, 
                                  b.action AS status, a.approval_pos
                             FROM ytycustom.ple_header a,
                                  ytycustom.ple_regulatory c,
                                  (SELECT *
                                     FROM ytycustom.ple_approval_stages t
                                    WHERE NOT EXISTS
                                           (SELECT 1
                                              FROM ytycustom.ple_approval_stages s
                                             WHERE t.header_id = s.header_id
                                               AND s.action_date > t.action_date)
                                      --AND t.action IS NOT NULL
                                      ) b
                            WHERE a.id = b.header_id(+)
                              AND a.id = c.header_id(+)
                              AND NVL(cancel_flag,'N') = 'N'
                           UNION
                           SELECT a.id, a.customer, a.customer_address, a.sku, a.glove_colour, a.glove_type, a.target_weight, a.glove_length,
                                  a.glove_size, a.glove_surface, a.glove_donning, a.special_features, a.sku_requirement, a.cert_required, 
                                  a.aql_requirement, a.volume_exp1, a.volume_remarks, a.price_exp, a.segment_territory, a.segment_oth,
                                  a.geographical, a.country, a.stage1_remarks, a.close_on,
                                  a.cost_of_cert, a.timing_cert, a.stage2_remarks,
                                  a.expect_changes_form, a.formulation_remarks, a.expect_changes_proc, a.feasible_wo_dev, a.prod_dev_remarks,
                                  a.cost_change, a.trial_req, a.qualification_req, case a.OQ when '1' then 'Yes' else 'No' end OQ, 
                                  case a.PQ when '1' then 'Yes' else 'No' end PQ, 
                                  a.timing_prototype, a.timing_trial, a.timing_oq, a.timing_pq, a.resources_required, a.technical_recommend,
                                  a.discuss_with_ops, a.stage3_remarks, 
                                  a.manufacture_review, a.trial_result, a.stage4_remarks,
                                  a.final_signoff, a.stage5_remarks,
                                  c.c_510k, c.astm_d6319, c.en_455, c.astm_en_455, c.iso_11193, c.as_nzs_4011, c.en_16523_1, c.ppe_eu,
                                  c.astm_d6978, c.iso_10993_5, c.elution_test, c.heavy_metal, c.japan, c.china, c.eu, c.us, c.others_req, 
                                  c.astm_f1671,c.endotoxin_lal, c.pyrogen, c.bioburden, c.silicone_oil, c.draize_95, c.as_nzs_4011_1,
                                  c.coeficient_friction, c.bio_compatibility, c.sem, c.voc, c.grip_test, c.nfpa_1999, 
                                  c.iso_10993_11, c.others_cc,
                                  a.created_date, a.stage_pos,
                                  b.request_date modified_date, 
                                  'REQ. INFO' AS status, a.approval_pos
                             FROM ytycustom.ple_header a,
                                  ytycustom.ple_regulatory c,
                                  ytycustom.ple_information b
                            WHERE a.id = b.header_id
                              AND a.id = c.header_id
                              AND b.info_desc IS NULL
                              AND NVL(cancel_flag,'N') = 'N') x
                   WHERE NOT EXISTS
                         (SELECT 1
                            FROM ( SELECT a.id, a.customer, a.customer_address, a.sku, a.glove_colour, a.glove_type, a.target_weight, a.glove_length,
                                          a.glove_size, a.glove_surface, a.glove_donning, a.special_features, a.sku_requirement, a.cert_required, 
                                          a.aql_requirement, a.volume_exp1, a.volume_remarks, a.price_exp, a.segment_territory, a.segment_oth,
                                          a.geographical, a.country, a.stage1_remarks, a.close_on,
                                          a.cost_of_cert, a.timing_cert, a.stage2_remarks,
                                          a.expect_changes_form, a.formulation_remarks, a.expect_changes_proc, a.feasible_wo_dev, a.prod_dev_remarks,
                                          a.cost_change, a.trial_req, a.qualification_req, case a.OQ when '1' then 'Yes' else 'No' end OQ, 
                                          case a.PQ when '1' then 'Yes' else 'No' end PQ, 
                                          a.timing_prototype, a.timing_trial, a.timing_oq, a.timing_pq, a.resources_required, a.technical_recommend,
                                          a.discuss_with_ops, a.stage3_remarks, 
                                          a.manufacture_review, a.trial_result, a.stage4_remarks,
                                          a.final_signoff, a.stage5_remarks,
                                          c.c_510k, c.astm_d6319, c.en_455, c.astm_en_455, c.iso_11193, c.as_nzs_4011, c.en_16523_1, c.ppe_eu,
                                          c.astm_d6978, c.iso_10993_5, c.elution_test, c.heavy_metal, c.japan, c.china, c.eu, c.us, c.others_req, 
                                          c.astm_f1671,c.endotoxin_lal, c.pyrogen, c.bioburden, c.silicone_oil, c.draize_95, c.as_nzs_4011_1,
                                          c.coeficient_friction, c.bio_compatibility, c.sem, c.voc, c.grip_test, c.nfpa_1999, 
                                          c.iso_10993_11, c.others_cc,
                                          a.created_date, case when a.stage_pos < 0 then a.approval_pos else a.stage_pos end stage_pos, 
                                          nvl(a.modified_date, a.created_date) modified_date,
                                          b.action AS status, a.approval_pos
                                     FROM ytycustom.ple_header a,
                                          ytycustom.ple_regulatory c,
                                          ( SELECT *
                                              FROM ytycustom.ple_approval_stages t
                                             WHERE NOT EXISTS
                                                   (SELECT 1
                                                      FROM ytycustom.ple_approval_stages s
                                                     WHERE t.header_id = s.header_id
                                                       AND s.action_date > t.action_date)
                                               --AND t.action IS NOT NULL
                                               ) b
                                    WHERE a.id = b.header_id(+)
                                      AND NVL(cancel_flag,'N') = 'N'
                                    UNION
                                   SELECT a.id, a.customer, a.customer_address, a.sku, a.glove_colour, a.glove_type, a.target_weight, a.glove_length,
                                          a.glove_size, a.glove_surface, a.glove_donning, a.special_features, a.sku_requirement, a.cert_required, 
                                          a.aql_requirement, a.volume_exp1, a.volume_remarks, a.price_exp, a.segment_territory, a.segment_oth,
                                          a.geographical, a.country, a.stage1_remarks, a.close_on,
                                          a.cost_of_cert, a.timing_cert, a.stage2_remarks,
                                          a.expect_changes_form, a.formulation_remarks, a.expect_changes_proc, a.feasible_wo_dev, a.prod_dev_remarks,
                                          a.cost_change, a.trial_req, a.qualification_req, case a.OQ when '1' then 'Yes' else 'No' end OQ, 
                                          case a.PQ when '1' then 'Yes' else 'No' end PQ, 
                                          a.timing_prototype, a.timing_trial, a.timing_oq, a.timing_pq, a.resources_required, a.technical_recommend,
                                          a.discuss_with_ops, a.stage3_remarks, 
                                          a.manufacture_review, a.trial_result, a.stage4_remarks,
                                          a.final_signoff, a.stage5_remarks,
                                          c.c_510k, c.astm_d6319, c.en_455, c.astm_en_455, c.iso_11193, c.as_nzs_4011, c.en_16523_1, c.ppe_eu,
                                          c.astm_d6978, c.iso_10993_5, c.elution_test, c.heavy_metal, c.japan, c.china, c.eu, c.us, c.others_req, 
                                          c.astm_f1671,c.endotoxin_lal, c.pyrogen, c.bioburden, c.silicone_oil, c.draize_95, c.as_nzs_4011_1,
                                          c.coeficient_friction, c.bio_compatibility, c.sem, c.voc, c.grip_test, c.nfpa_1999, 
                                          c.iso_10993_11, c.others_cc,
                                          a.created_date, a.stage_pos,
                                          b.request_date modified_date,
                                          'REQ. INFO' AS status, a.approval_pos
                                     FROM ytycustom.ple_header a,
                                          ytycustom.ple_regulatory c,
                                          ytycustom.ple_information b
                                    WHERE a.id = b.header_id
                                      AND a.id = c.header_id
                                      AND b.info_desc IS NULL
                                      AND NVL(cancel_flag,'N') = 'N') z
                           WHERE x.id = z.id
                             AND z.modified_date > x.modified_date)
                        GROUP BY id, customer, customer_address, sku, glove_colour, glove_type, target_weight, glove_length,
                                 glove_size, glove_surface, glove_donning, special_features, sku_requirement, cert_required, 
                                 aql_requirement, volume_exp1, volume_remarks, price_exp, segment_territory, segment_oth,
                                 geographical, country, stage1_remarks, close_on,
                                 cost_of_cert, timing_cert, stage2_remarks,
                                 expect_changes_form, formulation_remarks, expect_changes_proc, feasible_wo_dev, prod_dev_remarks,
                                 cost_change, trial_req, qualification_req, OQ, PQ, 
                                 timing_prototype, timing_trial, timing_oq, timing_pq, resources_required, technical_recommend,
                                 discuss_with_ops, stage3_remarks, 
                                 manufacture_review, trial_result, stage4_remarks,
                                 final_signoff, stage5_remarks,
                                 c_510k, astm_d6319, en_455, astm_en_455, iso_11193, as_nzs_4011, en_16523_1, ppe_eu,
                                 astm_d6978, iso_10993_5, elution_test, heavy_metal, japan, china, eu, us, others_req, 
                                 astm_f1671,endotoxin_lal, pyrogen, bioburden, silicone_oil, draize_95, as_nzs_4011_1,
                                 coeficient_friction, bio_compatibility, sem, voc, grip_test, nfpa_1999, 
                                 iso_10993_11, others_cc,
                                 created_date, modified_date, stage_pos";

          $sqlarr = oci_parse($dbcon, $sql);
          $r = oci_execute($sqlarr);
          oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);
          
          $resMsg = "OK";
          if (!$r) {
              $e = oci_error($sqlarr);
              $resMsg = json_encode($e['message']);
          }

        } else {
      
          $sql = "SELECT id, customer, created_date, modified_date, stage_pos, 
                         MAX(case when status is null and stage_pos = 5 and approval_pos = 5 then 'SUBMITTED' else status end) status
                    FROM (SELECT a.id, a.customer, a.created_date,
                                  nvl(a.modified_date, a.created_date) modified_date,
                                  a.stage_pos, a.approval_pos, b.action AS status
                             FROM ytycustom.ple_header a,
                                  (SELECT *
                                     FROM ytycustom.ple_approval_stages t
                                    WHERE NOT EXISTS
                                           (SELECT 1
                                              FROM ytycustom.ple_approval_stages s
                                             WHERE t.header_id = s.header_id
                                               AND s.action_date > t.action_date)
                                      AND t.stage_pos = :stage
                                      --AND t.action IS NOT NULL
                                      ) b
                            WHERE a.id = b.header_id(+)
                              AND a.stage_pos = :stage
                              AND NVL(cancel_flag,'N') = 'N'
                           UNION
                           SELECT a.id, a.customer, a.created_date,
                                  b.request_date modified_date, a.stage_pos,
                                  a.approval_pos, 'REQ. INFO' AS status
                             FROM ytycustom.ple_header a,
                                  ytycustom.ple_information b
                            WHERE a.id = b.header_id
                              AND b.info_desc IS NULL
                              AND b.to_stage_pos = :stage
                              AND NVL(cancel_flag,'N') = 'N') x
                   WHERE NOT EXISTS
                         (SELECT 1
                            FROM ( SELECT a.id, a.customer, a.created_date,
                                          nvl(a.modified_date, a.created_date) modified_date,
                                          a.stage_pos, a.approval_pos, b.action AS status
                                     FROM ytycustom.ple_header a,
                                          ( SELECT *
                                              FROM ytycustom.ple_approval_stages t
                                             WHERE NOT EXISTS
                                                   (SELECT 1
                                                      FROM ytycustom.ple_approval_stages s
                                                     WHERE t.header_id = s.header_id
                                                       AND s.action_date > t.action_date)
                                               AND t.stage_pos = :stage
                                               --AND t.action IS NOT NULL
                                               ) b
                                    WHERE a.id = b.header_id(+)
                                      AND a.stage_pos = :stage
                                      AND NVL(cancel_flag,'N') = 'N'
                                    UNION
                                   SELECT a.id, a.customer, a.created_date,
                                          b.request_date modified_date, a.stage_pos,
                                          a.approval_pos, 'REQ. INFO' AS status
                                     FROM ytycustom.ple_header a,
                                          ytycustom.ple_information b
                                    WHERE a.id = b.header_id
                                      AND b.info_desc IS NULL
                                      AND b.to_stage_pos = :stage
                                      AND NVL(cancel_flag,'N') = 'N') z
                           WHERE x.id = z.id
                             AND z.modified_date > x.modified_date)
                        GROUP BY id, customer, created_date, modified_date, stage_pos";
          
          $sqlarr = oci_parse($dbcon, $sql);
          oci_bind_by_name($sqlarr, ':stage', $data->STAGE); 
          $r = oci_execute($sqlarr);
          oci_fetch_all($sqlarr, $rows_main, null, null, OCI_FETCHSTATEMENT_BY_ROW);
          
          $resMsg = "OK";
          if (!$r) {
              $e = oci_error($sqlarr);
              $resMsg = json_encode($e['message']);
          } 
        
        }
    
    } else {
        $resMsg = "Wrong parameters.";
    }

    $result[] = [
        "message" => $resMsg,
        "data" => $rows_main
    ];

    print json_encode($result);
?>