<?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>';

         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);

            $sql = "UPDATE apps.yty_g_prc_modifier_list_tl
                       SET approval_flag = 'A',
                           approved_by = :approved_by,
                           approved_date = sysdate,
                           approver_comments = :approver_comments
                     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;
            }

            $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
         $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);
      }
   }

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