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

         $rejecteddata = '<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 = 'R',
                           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;
            }

            $rejecteddata .= '<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>';
         }
         $rejecteddata .= '</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 Rejected.</p> 
                                          <p>
                                             '.$rejecteddata.'
                                          </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';
         $mailSubject = "Request for quotation price list for ".$rows_price[0]["PERIOD"]." has been Rejected";

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