<?php  
ob_start();
ini_set('display_errors',0); 
session_start();
include("../db/connection.php");
include("functions.php");

 $action=($_REQUEST['action']) ? $_REQUEST['action'] : "";
 list($claimId,$approvalLvl)=explode("_",$_REQUEST['rd']);
 //echo $claimId.'-----'.$approvalLvl; die;
 
 if($claimId!="" && $approvalLvl!="")// code to update status  by id by Deep Rana
 {
  //*******************Check duplicate entry**************************************
  $claimExists="SELECT ACTION,APPROVER_EMP_ID
            FROM ytycustom.user_reg_app_trans WHERE doc_no='".$claimId."' AND appr_level='".$approvalLvl."'"; 				 
  $cliamByclaimExistsId= oci_parse($dbcon, $claimExists);
  oci_execute($cliamByclaimExistsId);
  $rowNum="";
  $rowNum=oci_fetch_array($cliamByclaimExistsId);
  $toEmailId='reply.yty@ytygroup.com.my';
  $employeeId=$rowNum['APPROVER_EMP_ID'];
  //echo"<pre>";print_r($rowNum);die;
  if($rowNum['ACTION']=="")
  {
    $strUpdateTransQuery = "UPDATE  ytycustom.user_reg_app_trans SET action='Approve', reason='',enter_date=sysdate,enter_by='' where doc_no='".$claimId."' AND approver_emp_id='".$employeeId."' AND appr_level='".$approvalLvl."'"; 
    $updateTransQuery = oci_parse($dbcon, $strUpdateTransQuery);
    oci_execute($updateTransQuery);

    $manage_query = oci_parse($dbcon, 'select ytycustom.xxyty_fn_auto_appr(\''.$claimId.'\', '.$approvalLvl.') AS RES_COUNT from dual');
    oci_execute($manage_query);
    $auto_appr_result = oci_fetch_array($manage_query);

    if (isset($auto_appr_result['RES_COUNT']) && $auto_appr_result['RES_COUNT'] == 1) {
        $query = 'select approver_emp_id, FN_GET_SHORT_NAME(approver_emp_id) AS APPR_NAME from ytycustom.user_reg_app_trans where 
        doc_no=\''.$claimId.'\' and doc_type_code=\'1000\' and appr_level=(select max(appr_level) from ytycustom.user_reg_app_trans 
        where doc_no=\''.$claimId.'\' and doc_type_code=\'1000\')';

        $manage_query = oci_parse($dbcon, $query);
        oci_execute($manage_query);
        $appr_result = oci_fetch_array($manage_query);

        $strUpdateTransQuery = "UPDATE  ytycustom.user_reg_app_trans SET action='Approve', reason='',enter_date=sysdate,enter_by='".(!empty($appr_result['APPR_NAME']) ? $appr_result['APPR_NAME'] : '')."' where doc_no='".$claimId."' AND appr_level='".($approvalLvl + 1)."'"; 
        $updateTransQuery = oci_parse($dbcon, $strUpdateTransQuery);
        oci_execute($updateTransQuery);
        oci_commit($dbcon);
        
        $proc_query = 'BEGIN apps.xxyty_claim_portal_inv_pkg.insert_interface(:x); END;';
        $proc_manage_query = oci_parse($dbcon, $proc_query);
        oci_bind_by_name($proc_manage_query, ':x', $claimId);
        $result = oci_execute($proc_manage_query);
        oci_commit($dbcon);
    }
    
    //*******************Check max level hierachy**************************************
    $queryMaxStatus="SELECT ACTION,R.APPR_LEVEL
            FROM YTYCUSTOM.USER_REG_APP_TRANS R
            WHERE APPR_LEVEL = (SELECT MAX(APPR_LEVEL) M                    
            FROM YTYCUSTOM.USER_REG_APP_TRANS R
            WHERE R.doc_no='".$claimId."'                
            GROUP BY EMP_ID,                              
            DOC_TYPE_CODE,
            DOC_NO)  AND R.doc_no='".$claimId."'"; 
     $StatusEmp= oci_parse($dbcon, $queryMaxStatus);
     oci_execute($StatusEmp);
     $row="";
     $row=oci_fetch_array($StatusEmp);
     if($claimId)
      $upgradeLevel=" ,current_approve_level='".($approvalLvl+1)."'";

     if($row['ACTION']=='Approve'){
      $claimStatus=" ,claim_status='Approve'";
      $upgradeLevel=" ,current_approve_level='".$row['APPR_LEVEL']."'";
      
      $claimDetails="SELECT ctd.CLAIM_TYPE_NAME ,ctd.CLAIM_TYPE_DESCRIPTION,CH.CLAIM_TYPE,CH.CREATED_BY,CH.CLAIM_TOT_AMT_MYR,CH.CLAIM_TOT_TAX_AMT,CH.CLAIM_TOTAL_AMT,emp.email
                FROM CLAIM_HEADER CH 
                LEFT JOIN CLAIM_INFO ci on CH.claim_id=ci.claim_id
                LEFT JOIN user_registry emp on emp.emp_id=ci.employee_id
                LEFT JOIN  EMPLOYEE_MASTER em on ci.EMPLOYEE_ID = em.EMP_ID
                LEFT JOIN  CLAIM_TYPE_DETAIL ctd on (ch.CLAIM_TYPE = ctd.CLAIM_TYPE_CODE AND ctd.CLAIM_HEAD_CODE = em.CLAIM_GROUP)
      WHERE CH.CLAIM_ID='".$claimId."'"; 		 
      $cliamByIdDetails= oci_parse($dbcon, $claimDetails);
      oci_execute($cliamByIdDetails);
      $row="";
      $row=oci_fetch_array($cliamByIdDetails);
      $emailContent='<style type="text/css">
                      table {border-collapse:separate;}
                      
                      .mbody {
                          color: #000;
                          font-family: Verdana;
                          font-style: normal;
                          font-size: 10.5pt;
                      }
                body{margin:0;padding:0}

                      .note {
                          font-size: 7.5pt;
                          font-family: Verdana;
                          color: #000;
                          text-align: justify;
                      }
                  </style>
              <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:5px; display:block;"></td>
              </tr>
              <tr>
                <td style="width:10px; display:block;"></td>
                <td align="center">
                  <table class="content" border="0" cellspacing="0" cellpadding="0" width="100%" style="border-collapse: collapse; border:1px solid #7f7f7f;">
                  <tr>
                    <td>
                      <img src="http://dl.ytygroup.com.my/CommonLogo/MailHeader.jpg" 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:10px; display:block;"></td>
                        <td style="line-height: 1.3em;">
                          <p>Dear '.ucwords(strtolower($row['CREATED_BY'])).',</p>
                          <p>Your claim approval request vide id no.'.$claimId.' as per below details has been processed by Finance dept within 3-5 business days.</p>
                            <p> <table width="100%" border="1" cellspacing="0">
                              <tbody>
                              <tr>
                                <td style="width:30px;font-size:13px"><strong>S&nbsp;No</strong></td>
                                <td style="width:200px;font-size:13px;"><b>Claim Type</b></td>
                                <td style="width:200px;font-size:13px;"><b>Amount</b></td>
                                <td style="width:200px;font-size:13px;"><b>Tax</b></td>
                                <td style="width:120px;font-size:13px;"><b>Total</b></td>
                              </tr>';
                              $total=0;
                              $sno=1;
                              $cliamByIdDetails= oci_parse($dbcon, $claimDetails);
                              oci_execute($cliamByIdDetails);
                                while($mailClaimDetail=oci_fetch_array($cliamByIdDetails))
                                {
                                  $emailContent.='<tr height="30">
                                          <td style="font-size: 12px;" align="left">'.$sno++.'.</td>
                                          <td style="font-size: 12px;">'.$mailClaimDetail['CLAIM_TYPE_NAME'].'</td>
                                          <td style="font-size: 12px;">RM &nbsp;&nbsp; '.number_format($mailClaimDetail['CLAIM_TOT_AMT_MYR'],2).'</td>
                                          <td style="font-size: 12px;">RM &nbsp;&nbsp; '.number_format($mailClaimDetail['CLAIM_TOT_TAX_AMT'],2).'</td>
                                          <td style="font-size: 12px;" align="left">RM &nbsp;&nbsp; '.number_format($mailClaimDetail['CLAIM_TOTAL_AMT'],2).'</td>
                                        </tr>';
                                  $total+=$mailClaimDetail['CLAIM_TOTAL_AMT'];
                                }
                            $emailContent.='<tr><td colspan="4" style="font-size:13px; text-align:right">Total Claim Amount :</td>
                                                                    <td style="font-size:13px;">RM '.number_format($total,2).'</td></tr></tbody>
                            </table>
                            </p>
                          <br/><p>Sincerely,<br /><b>YTY Workflow</b></p> 
                          <p><a href="'.get_login_url().'" target="_blank" style="color:#54a0ff; font-weight:600; text-decoration:none;">Login  here to see details</a></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:10px; 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:10px; display:block;"></td>
              </tr>
              <tr>
               <td style="height:5px; display:block;"></td>
              </tr>
              </table>
              </body>
              </html>';
         $mailSubject="Expense claim request no. ".$claimId."- Approved by Finance";
         //echo"<pre>";print_r($emailContent); die; //".$row1['EMAIL']." 
          $strInsertEmailQuery = "BEGIN
            ytycustom.set_notification.generate(ytycustom.set_notification.get_id,    --no change <br>
            'EMAIL',                              --no change<br>
            '".$row['EMAIL']."',      --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>
            '".$emailContent."',               --Email body (in html format)<br>
            'HTML',                               --no change<br>
            SYSDATE,                              --no change<br>
            'CLAIM_F',                      --Claim system (will change later on)<br>
            Null     --bcc email id (Null for none)<br>
            );
            END;";
        $insertEmailQuery = oci_parse($dbcon, $strInsertEmailQuery);
        oci_execute($insertEmailQuery);
     }
     else
     {
       $status='Approve';
       $Record=$claimId."_".($approvalLvl+1);
       if($status=='Reject'){
        $claimStatus=" ,claim_status='Reject'";
       }
       else
       { //*****************code to send mail for next level by Deep Rana
         $claimNo=$claimId;
         $queryNextEmailId="select emp_name,email FROM user_registry where emp_id =(SELECT R.approver_emp_id FROM YTYCUSTOM.USER_REG_APP_TRANS R 
                  WHERE APPR_LEVEL = (SELECT MAX(APPR_LEVEL)+1 M FROM YTYCUSTOM.USER_REG_APP_TRANS R 
                  WHERE R.doc_no='".$claimId."' AND R.approver_emp_id='".$employeeId."' GROUP BY EMP_ID, DOC_TYPE_CODE, DOC_NO) AND R.doc_no='".$claimId."')";
                 $NextLevelEmal= oci_parse($dbcon, $queryNextEmailId);
                 oci_execute($NextLevelEmal);
                 $row1="";
                 $row1=oci_fetch_array($NextLevelEmal);
                 //echo"<pre>";print_r($row1);die;
        $claimDetails="SELECT ctd.CLAIM_TYPE_NAME ,ctd.CLAIM_TYPE_DESCRIPTION,CH.CLAIM_TYPE,CH.CREATED_BY,CH.CLAIM_TOT_AMT_MYR,CH.CLAIM_TOT_TAX_AMT,CH.CLAIM_TOTAL_AMT,emp.email
                    FROM CLAIM_HEADER CH 
                    LEFT JOIN CLAIM_INFO ci on CH.claim_id=ci.claim_id
                    LEFT JOIN user_registry emp on emp.emp_id=ci.employee_id
                    LEFT JOIN  EMPLOYEE_MASTER em on ci.EMPLOYEE_ID = em.EMP_ID
                    LEFT JOIN  CLAIM_TYPE_DETAIL ctd on (ch.CLAIM_TYPE = ctd.CLAIM_TYPE_CODE AND ctd.CLAIM_HEAD_CODE = em.CLAIM_GROUP)
        WHERE CH.CLAIM_ID='".$claimId."'"; 				 
        $cliamByIdDetails= oci_parse($dbcon, $claimDetails);
        oci_execute($cliamByIdDetails);
        $row="";
        $row=oci_fetch_array($cliamByIdDetails);
         $emailContent='<style type="text/css">
                            table {border-collapse:separate;}
                            
                            .mbody {
                                color: #000;
                                font-family: Verdana;
                                font-style: normal;
                                font-size: 10.5pt;
                            }
                      body{margin:0;padding:0}
                    
                            .note {
                                font-size: 7.5pt;
                                font-family: Verdana;
                                color: #000;
                                text-align: justify;
                            }
                        </style>
                  <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:5px; display:block;"></td>
                  </tr>
                  <tr>
                    <td style="width:10px; display:block;"></td>
                    <td align="center">
                      <table class="content" border="0" cellspacing="0" cellpadding="0" width="100%" style="border-collapse: collapse; border:1px solid #7f7f7f;">
                      <tr>
                        <td>
                          <img src="http://dl.ytygroup.com.my/CommonLogo/MailHeader.jpg" 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:10px; display:block;"></td>
                            <td style="line-height: 1.3em;">
                              <p>Dear <b>Approver</b>,</p>
                              <p>'.$row['CREATED_BY'].' has submitted Expense Reimbursement Request for your approval with following summary:</p>
                                <p> <table width="100%" border="1" cellspacing="0" cellpadding="5">
                                  <tbody>
                                  <tr>
                                    <td style="width:30px;font-size:13px"><strong>S&nbsp;No</strong></td>
                                    <td style="width:200px;font-size:13px;"><b>Claim Type</b></td>
                                    <td style="width:200px;font-size:13px;"><b>Amount</b></td>
                                    <td style="width:200px;font-size:13px;"><b>Tax</b></td>
                                    <td style="width:120px;font-size:13px;"><b>Total</b></td>
                                  </tr>';
                                  $total=0;
                                  $sno=1;
                                  $cliamByIdDetails= oci_parse($dbcon, $claimDetails);
                                  oci_execute($cliamByIdDetails);
                                    while($mailClaimDetail=oci_fetch_array($cliamByIdDetails))
                                    {
                                      $emailContent.='<tr height="30">
                                              <td style="font-size: 12px;" align="left">'.$sno++.'.</td>
                                              <td style="font-size: 12px;">'.$mailClaimDetail['CLAIM_TYPE_NAME'].'</td>
                                              <td style="font-size: 12px;">RM &nbsp;&nbsp; '.number_format($mailClaimDetail['CLAIM_TOT_AMT_MYR'],2).'</td>
                                              <td style="font-size: 12px;">RM &nbsp;&nbsp; '.number_format($mailClaimDetail['CLAIM_TOT_TAX_AMT'],2).'</td>
                                              <td style="font-size: 12px;" align="left">RM &nbsp;&nbsp; '.number_format($mailClaimDetail['CLAIM_TOTAL_AMT'],2).'</td>
                                            </tr>';
                                      $total+=$mailClaimDetail['CLAIM_TOTAL_AMT'];
                                    }
                                $emailContent.='<tr><td colspan="4" style="font-size:13px; text-align:right"><strong>Total Claim Amount :</strong></td><td style="font-size:13px;">RM '.number_format($total,2).'</td></tr></tbody>
                                </table>
                                <center>
                                <table width="30%" border="0" cellspacing="0" cellpadding="0">
                                <tbody>
                                <tr>
                                <td align="center" style="background:#3d607e; height:50px; width:20%; border:3px solid #ffffff;">
                                <a href="https://ytygroup.app/ClaimPortal/apprbyemp.php?rd='.$Record.'" style="text-decoration:none; color:#ffffff; font-weight:600; text-align:center;" target="_blank">Approve</a></td>
                                <td align="center" style="background:#3d607e; height:50px; width:20%; border:3px solid #ffffff;">
                                <a href="https://ytygroup.app/ClaimPortal/rejctbyemp.php?rd='.$Record.'" style="text-decoration:none; color:#ffffff; font-weight:600; text-align:center;" target="_blank">Reject</a></td>
                                </tr>
                              </tbody>
                              </table>
                              </center>
                                </p>
                              <br/><p>Sincerely,<br /><b>YTY Workflow</b></p> 
                              <p><a href="'.get_login_url().'" target="_blank" style="color:#54a0ff; font-weight:600; text-decoration:none;">Login  here to see details</a></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:10px; 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:10px; display:block;"></td>
                  </tr>
                  <tr>
                   <td style="height:5px; display:block;"></td>
                  </tr>
                  </table>
                  </body>
                  </html>';
           $mailSubject="Expense Reimbursement request vide id no $claimNo .";
           //echo"<pre>";print_r($emailContent); die;  //".$sentTo." ".$sentTo." ".$row1['EMAIL']."
           $strInsertEmailQuery = "BEGIN
              ytycustom.set_notification.generate(ytycustom.set_notification.get_id,    --no change <br>
              'EMAIL',                              --no change<br>
              '".$row1['EMAIL']."',      --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>
              '".$emailContent."',               --Email body (in html format)<br>
              'HTML',                               --no change<br>
              SYSDATE,                              --no change<br>
              'CLAIM_F',                      --Claim system (will change later on)<br>
              Null     --bcc email id (Null for none)<br>
              );
              END;";
          $insertEmailQuery = oci_parse($dbcon, $strInsertEmailQuery);
          oci_execute($insertEmailQuery);
          //echo"<pre>";print_r($emailContent); die; 
       }
     }
    $strUpdateQuery = "UPDATE  claim_info SET  reason='',status_date=sysdate $upgradeLevel $claimStatus where claim_id='".$claimId."'";
    $updateQuery = oci_parse($dbcon, $strUpdateQuery);
    oci_execute($updateQuery);
    $msgFlag="Response submitted successfully. Thank you.";
   }
   else
   {
    $msgFlag="This notification is already closed.";
   }
 }
 ?>
<?php include("templates/top.php");?>
<body style="background:url(image/background.jpg);">
<script type="text/javascript" src="js/wz_tooltip.js"></script> 
<link rel="stylesheet" href="css/calendar.css" type="text/css" media="screen">
<script language="JavaScript" src="js/popcalendar.js"></script>
<div class="d-flex'" id="wrapper">
    <!-- /#sidebar-wrapper -->
    <!-- Page Content -->
    <div id="page-content-wrapper">
     <?php include("templates/navbarNew.php") ?>
      <div class="container">
       <div class="card mx-auto" style="padding: 20px;box-shadow: 0px 0px 20px rgb(27 27 27 / 13%);border-radius: 10px;
">
			<style>
            .form-group{
                margin-bottom:6px;
            }
      </style

                  <table width="100%" cellpadding="2" cellspacing="2" border="2">
                  <tr>
                      <td width="30%">
                      <div class="form-group" style="border-bottom: 1px dashed #015aa9;color: #015aa9;font-size: 20px;position: relative;
">
                          <label for="code" style="margin-left: -7px;"><b>&nbsp;WORKFLOW SYSTEM</b></label>
                        </div>
                      </td>
                  </tr>                
                   <tr>
                      <td width="30%">
                      <div class="form-group">
                          <label for="department" style="color: #18c31d;"><b>&nbsp;<?php echo $msgFlag;?></b></label>
                        </div>
                      </td>
                  </tr>
                </table>
	</div>		
<?php require_once("templates/footer.php"); ?>
