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

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

   if (!empty($data)) {
   
      //Loop by Created By
      $uniqueUser = array_unique(array_map(function ($i) { return $i->CREATED_BY_ID; }, $data->data)); 
      
      foreach ($uniqueUser as $user) {
         $records = array_filter($data->data, function ($main) use ($user) {
            return $main->CREATED_BY_ID == $user;
         });

         usort($records, function($a, $b) {
            $retval = $a->CUSTOMER_NAME <=> $b->CUSTOMER_NAME;
            if ($retval == 0) {
               $retval = $a->PRODUCT_ATTR_VALUE <=> $b->PRODUCT_ATTR_VALUE;
               if ($retval == 0) {
                  $retval = $a->SKU_SIZE <=> $b->SKU_SIZE;
                  if ($retval == 0) {
                     $retval = $a->BRAND <=> $b->BRAND;
                  }
               }
            }
            return $retval;
         });
      
         $approveddata = '<table border="1" cellspacing="0" cellpadding="5" style="width:100%;">
                          <thead>
                          <tr>
                              <th style="width:200px;">Customer</th>
                              <th style="width:120px;">SKU</th>
                              <th style="width:80px;">Size</th>
                              <th style="width:80px;">Brand</th>
                          </tr>
                          </thead>
                          </tbody>';

         $flag_for_next_approval = "N";
         $apprv_mail = [];

         foreach ($records as $row) {
            //Price Data
            $sql = "SELECT a.period, a.customer_name, b.employee_name created_by_name, b.email_id, 
                           a.product_attr_value, a.incoterm siz_nm, a.brand
                      FROM apps.yty_g_prc_modifier_list_tl a,
                           production.employee_master b
                     WHERE a.created_by = b.emp_id
                       AND a.ID = :id";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':id', $row->ID);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            if (!$r) {
                $e = oci_error($sqlarr);
                $resMsg = json_encode($e['message']);
            }
            oci_fetch_all($sqlarr, $rows_price, null, null, OCI_FETCHSTATEMENT_BY_ROW);

            //Approval Level
            $sql = "SELECT MAX(appr_level) max_level, MAX(NVL(status,0)) status
                      FROM YTYCUSTOM.USER_REG_APP_TRANS
                     WHERE doc_no = :doc_no
                       AND doc_type_code = '2003'";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':doc_no', $row->ID);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            if (!$r) {
                $e = oci_error($sqlarr);
                $resMsg = json_encode($e['message']);
            }

            $max_level = 99;
            while ($rw = oci_fetch_array($sqlarr)) {
               $max_level = $rw['MAX_LEVEL'];
            }

            //App Trans
            $sql = "UPDATE ytycustom.user_reg_app_trans
                       SET action = 'Approve',
                           enter_date = sysdate,
                           enter_by = :enter_by
                     WHERE doc_no = :doc_no
                       AND appr_level = :appr_level
                       AND approver_emp_id = :approver_emp_id
                       AND doc_type_code = '2003'";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':doc_no', $row->ID);
            oci_bind_by_name($sqlarr, ':enter_by', $data->userid);
            oci_bind_by_name($sqlarr, ':approver_emp_id', $data->userid);
            oci_bind_by_name($sqlarr, ':appr_level', $row->APPROVAL_LEVEL);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            if (!$r) {
                  $e = oci_error($sqlarr);
                  $errorMsg = json_encode($e['message']);
                  continue;
            }

            $next_level = ($row->APPROVAL_LEVEL + 1) >= $max_level ? $max_level : ($row->APPROVAL_LEVEL + 1); 
            if ($row->APPROVAL_LEVEL == $max_level) {
               $sql = "UPDATE apps.yty_g_prc_modifier_list_tl
                          SET approval_flag = 'A',
                              approved_by = :approved_by,
                              approved_date = sysdate,
                              approver_comments = :approver_comments,
                              approval_level = '".$next_level."'
                        WHERE ID = :id
                          AND approval_flag = 'P'";
            
            } else {

               $sql = "UPDATE apps.yty_g_prc_modifier_list_tl
                          SET approved_by = :approved_by,
                              approver_comments = :approver_comments,
                              approval_level = '".$next_level."'
                        WHERE ID = :id
                          AND approval_flag = 'P'";
            }     
            
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':id', $row->ID);
            oci_bind_by_name($sqlarr, ':approved_by', $data->userid);
            oci_bind_by_name($sqlarr, ':approver_comments', $row->APPROVER_COMMENTS);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            
            if (!$r) {
                  $e = oci_error($sqlarr);
                  $errorMsg = json_encode($e['message']);
                  continue;
            }

            if ($row->APPROVAL_LEVEL < $max_level) {
               $flag_for_next_approval = "Y";

               //Next Approval
               $sql = "SELECT email_id
                         FROM ytycustom.user_reg_app_trans a, employee_master b
                        WHERE a.approver_emp_id = b.emp_id
                          AND a.doc_no = :doc_no
                          AND a.appr_level = '".$next_level."'
                          AND doc_type_code = '2003'";

               $sqlarr = oci_parse($dbcon , $sql);
               oci_bind_by_name($sqlarr, ':doc_no', $row->ID);
               $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

               if (!$r) {
                   $e = oci_error($sqlarr);
                   $resMsg = json_encode($e['message']);
               }

               $apprv_mail = [];
               while ($rw = oci_fetch_array($sqlarr)) {
                  array_push($apprv_mail, $rw['EMAIL_ID']);
               }
            }

            $approveddata .= '<tr>
                                 <td>'.$rows_price[0]["CUSTOMER_NAME"].'</td>
                                 <td>'.$rows_price[0]["PRODUCT_ATTR_VALUE"].'</td>
                                 <td>'.$rows_price[0]["SIZ_NM"].'</td>
                                 <td>'.$rows_price[0]["BRAND"].'</td>
                              </tr>';
         }
         $approveddata .= '</tbody></table>';

         //Email
         if (count($apprv_mail) > 0) {
            
            $html='<!doctype html>
                   <html>
                   <head>
                         <title></title>
                         <style type="text/css">
                            table {border-collapse:separate;}
                            .ExternalClass p, .ExternalClass span, .ExternalClass font, .ExternalClass td {line-height: 100%}
                            .ExternalClass {width: 100%;}
                   
                            .wrapper {
                               width:100%;
                               background-color: #fff;
                            }
                   
                            .container {
                               width:800px !important;
                            }
                   
                            .content {
                               background-color: #fff;
                               border: 1px solid #D6D6D6;
                               -webkit-box-shadow: 0px 0px 7px 1px rgba(0,0,0,0.75);
                               -moz-box-shadow: 0px 0px 7px 1px rgba(0,0,0,0.75);
                               box-shadow: 0px 0px 7px 1px rgba(0,0,0,0.55);
                            }
                   
                            .mbody {
                               color: #000;
                               font-family: Verdana;
                               font-style: normal;
                               font-size: 10.5pt;
                            }
                   
                            .note {
                               font-size: 7.5pt;
                               font-family: Verdana;
                               color: #000;
                               text-align: justify;
                            }
                         </style>
                   </head>
                   <body>
                   <table cellpadding="0" cellspacing="0" align="center" border="0" width="100%" style="height:100%!important; margin:0; padding:0; width:100%!important; text-align:center">
                   <tr>
                         <td style="height:20px; display:block;"></td>
                   </tr>
                   <tr>
                         <td style="width:20px; display:block;"></td>
                         <td align="center">
                            <table class="content" border="0" cellspacing="0" cellpadding="0" width="800" style="border-collapse: collapse; border:1px solid #7f7f7f;">
                            <tr>
                               <td>
                                     <img src="http://dl.ytygroup.com.my/CommonLogo/MailHeader.jpg" width="952" height="106" style="display:block;"/>
                               </td>
                            </tr>
                            <tr>
                               <td style="height:10px; display:block;"></td>
                            </tr>
                            <tr>
                               <td>
                                     <table class="mbody" style="width:100%;">
                                     <tr>
                                        <td style="width:20px; display:block;"></td>
                                        <td style="line-height: 1.3em;">
                                           <p>Dear Approver,</p>
                                           <p>Your kind approval is sought on the quotation price list for '.$rows_price[0]["PERIOD"].'. Please <a href="https://ytyportal.ytygroup.my/advance_pricing/dashboard.php" target="_blank" style="color:#1355a7; font-weight:600; text-decoration:none;">login</a> to Pricing system to respond.</p>
                                           
                                           <br/>
                                           <p>Sincerely,<br /><b>YTY Workflow</b></p> 
                                           <p style="font-size:11px;">Note: This message is auto-generated so please do not respond to this email.</p></td>
                                        <td style="width:20px; display:block;"></td>
                                     </tr>
                                     </table>
                               </td>
                            </tr>
                            <tr>
                               <td style="height:10px; display:block;"></td>
                            </tr>
                            <tr>
                               <td>
                                     <img src="http://dl.ytygroup.com.my/CommonLogo/MailFooter.jpg" style="display:block;"/>
                               </td>
                            </tr>
                            </table>
                         </td>
                         <td style="width:20px; display:block;"></td>
                   </tr>
                   <tr>
                         <td style="height:20px; display:block;"></td>
                   </tr>
                   </table>
                   </body>
                   </html>';
                   
            $mailSubject="Request for quotation price list for ".$rows_price[0]["PERIOD"];

            $unique_appr = array_unique($apprv_mail);
            $strInsertEmailQuery = "BEGIN
                                       ytycustom.set_notification.generate(ytycustom.set_notification.get_id,    --no change <br>
                                       'EMAIL',                              --no change<br>
                                       '".implode(',', $unique_appr)."',      --to email id<br>
                                       NULL,    --cc email id (Null for none)<br>
                                       NULL,                                 --attachment file path, let it be null for the time being<br>
                                       '".$mailSubject."',                               --Email Subject<br>
                                       '".$html."',               --Email body (in html format)<br>
                                       'HTML',                               --no change<br>
                                       SYSDATE,                              --no change<br>
                                       'ORACLE PRICING',                      --Claim system (will change later on)<br>
                                       Null     --bcc email id (Null for none)<br>
                                       );
                                    END;";
            $insertEmailQuery = oci_parse($dbcon, $strInsertEmailQuery);
            oci_execute($insertEmailQuery, OCI_NO_AUTO_COMMIT);

         } else {

            //Finally Approved
            $html='<!doctype html>
                   <html>
                   <head>
                        <title></title>
                        <style type="text/css">
                           table {border-collapse:separate;}
                           .ExternalClass p, .ExternalClass span, .ExternalClass font, .ExternalClass td {line-height: 100%}
                           .ExternalClass {width: 100%;}

                           .wrapper {
                              width:100%;
                              background-color: #fff;
                           }

                           .container {
                              width:800px !important;
                           }

                           .content {
                              background-color: #fff;
                              border: 1px solid #D6D6D6;
                              -webkit-box-shadow: 0px 0px 7px 1px rgba(0,0,0,0.75);
                              -moz-box-shadow: 0px 0px 7px 1px rgba(0,0,0,0.75);
                              box-shadow: 0px 0px 7px 1px rgba(0,0,0,0.55);
                           }

                           .mbody {
                              color: #000;
                              font-family: Verdana;
                              font-style: normal;
                              font-size: 10.5pt;
                           }

                           .note {
                              font-size: 7.5pt;
                              font-family: Verdana;
                              color: #000;
                              text-align: justify;
                           }
                        </style>
                  </head>
                  <body>
                  <table cellpadding="0" cellspacing="0" align="center" border="0" width="100%" style="height:100%!important; margin:0; padding:0; width:100%!important; text-align:center">
                  <tr>
                        <td style="height:20px; display:block;"></td>
                  </tr>
                  <tr>
                        <td style="width:20px; display:block;"></td>
                        <td align="center">
                           <table class="content" border="0" cellspacing="0" cellpadding="0" width="800" style="border-collapse: collapse; border:1px solid #7f7f7f;">
                           <tr>
                              <td>
                                    <img src="http://dl.ytygroup.com.my/CommonLogo/MailHeader.jpg" width="952" height="106" style="display:block;"/>
                              </td>
                           </tr>
                           <tr>
                              <td style="height:10px; display:block;"></td>
                           </tr>
                           <tr>
                              <td>
                                 <table class="mbody" style="width:100%;">
                                 <tr>
                                    <td style="width:20px; display:block;"></td>
                                    <td style="line-height: 1.3em;">
                                       <p>Dear <b>'.$rows_price[0]["CREATED_BY_NAME"].'</b>,</p>
                                       <p>Below customer quotation price List for '.$rows_price[0]["PERIOD"].' has been Approved.</p> 
                                       <p>
                                          '.$approveddata.'
                                       </p>
                                       <br/>
                                       <p>Sincerely,<br /><b>YTY Workflow</b></p> 
                                       <p style="font-size:11px;">Note: This message is auto-generated so please do not respond to this email.</p></td>
                                    <td style="width:20px; display:block;"></td>
                                 </tr>
                                 </table>
                              </td>
                           </tr>
                           <tr>
                              <td style="height:10px; display:block;"></td>
                           </tr>
                           <tr>
                              <td>
                                    <img src="http://dl.ytygroup.com.my/CommonLogo/MailFooter.jpg" style="display:block;"/>
                              </td>
                           </tr>
                           </table>
                        </td>
                        <td style="width:20px; display:block;"></td>
                  </tr>
                  <tr>
                        <td style="height:20px; display:block;"></td>
                  </tr>
                  </table>
                  </body>
                  </html>';

            $senderEmail = $rows_price[0]["EMAIL_ID"];
            $ccEmail = 'kavinmalar.kathirchelvam@ytygroup.com.my,pikling.ho@ytygroup.com.my,shawn.kuok@ytygroup.com.my,farhanah.zamzuri@ytygroup.com.my';
            //$ccEmail = 'reyndo.frenliedo@ytygroup.com.my,satish.madala@ytygroup.com.my';
            $mailSubject = "Request for quotation price list for ".$rows_price[0]["PERIOD"]." has been Approved";
            $strInsertEmailQuery = "BEGIN
                                       ytycustom.set_notification.generate(ytycustom.set_notification.get_id,    --no change <br>
                                       'EMAIL',                              --no change<br>
                                       '".$senderEmail."',      --to email id<br>
                                       '".$ccEmail."',    --cc email id (Null for none)<br>
                                       NULL,                                 --attachment file path, let it be null for the time being<br>
                                       '".$mailSubject."',                               --Email Subject<br>
                                       '".$html."',               --Email body (in html format)<br>
                                       'HTML',                               --no change<br>
                                       SYSDATE,                              --no change<br>
                                       'COUNTER DAILY',                      --Claim system (will change later on)<br>
                                       Null     --bcc email id (Null for none)<br>
                                       );
                                    END;";
            
            $insertEmailQuery = oci_parse($dbcon, $strInsertEmailQuery);
            oci_execute($insertEmailQuery, OCI_NO_AUTO_COMMIT);

         }
         
      }
   }

finalStep:
   $result = [];
   if ($errorMsg == "") {
      oci_commit($dbcon);
      $result[] = [
         "message" => "Success",
         "id" => $newid
      ];
   } else {
      oci_rollback($dbcon);
      $result[] = [
         "message" => $errorMsg,
         "id" => ""
      ];
   }
   print json_encode($result);
?>