<?php  
ob_start();
ini_set('display_errors',0); 
session_start();
include("../db/connection.php");   
include("functions.php");
   
 $action=($_REQUEST['action']) ? $_REQUEST['action'] : "";
 list($emp_id,$leaveId,$approvalLvl)=explode("_",base64_decode($_REQUEST['rd']));
 //echo $employeeId.'-----'.$leaveId.'-----'.$approvalLvl;
 
 if($leaveId!="")// code to update status  by id by Deep Rana
 {
   
   //*******************Check duplicate entry**************************************
   $claimExists="SELECT ACTION,APPROVER_EMP_ID,ur.EMP_NAME
			FROM ytycustom.user_reg_app_trans  urat
			LEFT JOIN production.user_registry ur ON urat.approver_emp_id=ur.emp_id WHERE doc_no='".$leaveId."'  AND appr_level='".$approvalLvl."'";				 
   $cliamByclaimExistsId= oci_parse($dbcon, $claimExists);
   oci_execute($cliamByclaimExistsId);
   $rowNum="";
   $rowNum=oci_fetch_array($cliamByclaimExistsId);
   $employeeId=$rowNum['APPROVER_EMP_ID'];
   //echo"<pre>";print_r($employeeId);die;
   if($rowNum['ACTION']=="")
   {
     $strUpdateTransQuery = "UPDATE  ytycustom.user_reg_app_trans SET action='Approve', reason='',enter_date=sysdate,enter_by='".str_replace("'","''",$rowNum['EMP_NAME'])."' where doc_no='".$leaveId."' AND approver_emp_id='".$employeeId."' AND appr_level='".$approvalLvl."'"; 
     $updateTransQuery = oci_parse($dbcon, $strUpdateTransQuery);
     oci_execute($updateTransQuery);
   //*******************Check max level hierachy Approve or not**************************************
     $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='".$leaveId."'                
       GROUP BY EMP_ID,                              
       DOC_TYPE_CODE,
       DOC_NO)  AND R.doc_no='".$leaveId."'";
     $StatusEmp= oci_parse($dbcon, $queryMaxStatus);
     oci_execute($StatusEmp);
     $row="";
     $row=oci_fetch_array($StatusEmp);
     $upgradeLevel="";
     $leaveManageFlag=0;
     if($leaveId)
       $upgradeLevel=" ,next_level='".($approvalLvl+1)."'";
     if($row['ACTION']=='Approve'){
       $claimStatus=" ,status='APPROVED'";
       $upgradeLevel=" ,next_level='".$row['APPR_LEVEL']."'";
       $leaveManageFlag=1;
       
     }
     $status='Approve';
     $nextLvl=($approvalLvl+1);
     if($status=='Reject'){
       $claimStatus=" ,status='REJECTED'";
     }
     else
     { //*****************code to send mail for next level by Deep Rana
       $toEmailId='reply.yty@ytygroup.com.my';
       $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='".$leaveId."' AND R.approver_emp_id='".$employeeId."' GROUP BY EMP_ID, DOC_TYPE_CODE, DOC_NO) AND R.doc_no='".$leaveId."')";
            $NextLevelEmal= oci_parse($dbcon, $queryNextEmailId);
            oci_execute($NextLevelEmal);
            $row1="";
            $row1=oci_fetch_array($NextLevelEmal);
           $leaveDetails=" select LE.EMP_NO,
          UR.EMPLOYEE_NAME,
          UR.EMAIL_ID,
          LE.LDP_FROM_DATE,
          LE.LDP_TO_DATE,
          LE.LDP_REMARKS1,
          LE.LDP_CODE,
          LE.NO_OF_DAYS,
          LE.EFFECTIVE_DAYS,
          OTHER_ENTERED_BY,
          LTM.LEAVE_TYPE_NAME
     FROM ytycustom.emp_leave_entry_hd LE
     LEFT JOIN employee_master UR
       ON LE.EMP_NO = UR.EMP_ID
     LEFT OUTER JOIN ytycustom.leave_type_master ltm
       ON LE.LDP_CODE = ltm.LEAVE_TYPE_CODE                   
	WHERE REQ_ID='".$leaveId."'";				 
       $leaveByIdDetails= oci_parse($dbcon, $leaveDetails);
       oci_execute($leaveByIdDetails);
       $row="";
       $row=oci_fetch_array($leaveByIdDetails);
       //*************HALF DAY AND EMERGENCY LEAVE WILL BE DEDUCT FROM ANNUAL LEAVE AS DISCUSS 14-07-2020 BY DEEP RANA********//
       if($row['LDP_CODE']=="10HF" || $row['LDP_CODE']=="11EL")
         $leaveType="'".$row['LDP_CODE']."','01AL'";
       else
         $leaveType="'".$row['LDP_CODE']."'";
       
     /*$availLeaves="SELECT TOTAL_LEAVE, TAKEN, TOTAL_LEAVE - TAKEN leave_balance
                    FROM (SELECT L.LEAVE_CF,
                    L.LEAVE_AVAILED,
                    L.LEAVE_CF + L.LEAVE_AVAILED TOTAL_LEAVE,
                    L.AVAILED TAKEN
                    FROM ytycustom.expat_leave_balance_master L
                    WHERE EMP_NO ='".$row['EMP_NO']."'
                    AND yr = TO_CHAR(SYSDATE, 'YYYY')
                    AND LEAVE_TYPE IN ($leaveType))";				
               

       $AvailableDetails= oci_parse($dbcon, $availLeaves);
       oci_execute($AvailableDetails);
       $row2="";
       $row2=oci_fetch_array($AvailableDetails);
       $avlLeave=0;
       if($row2['LEAVE_BALANCE']>0)
         $avlLeave=$row2['LEAVE_BALANCE'];*/

       $avlLeave = leave_type_balance($row['LDP_CODE'], 'BALANCE', $row['EMP_NO']); 
       
       if($leaveManageFlag==1){
         $updateLeave="UPDATE ytycustom.expat_leave_balance_master SET LEAVE_BALANCE=LEAVE_BALANCE-".$row['EFFECTIVE_DAYS'].",AVAILED=AVAILED+".$row['EFFECTIVE_DAYS']." 
   WHERE EMP_NO = '".$row['EMP_NO']."' AND yr = TO_CHAR(SYSDATE, 'YYYY') AND LEAVE_TYPE IN($leaveType)";
         $updateQueryLeave = oci_parse($dbcon, $updateLeave);
         oci_execute($updateQueryLeave);
         $avlLeave=($avlLeave-$row['EFFECTIVE_DAYS']);

         update_replacement_work_count($emp_id, $row['LDP_CODE']);
         
         if($row['EMAIL_ID']!=""){
           $emailContent='<!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>'.ucwords(strtolower($row['EMPLOYEE_NAME'])).'</b>,</p>
                         <p>Your leave approval request vide id no.'.$leaveId.' as per below details has been finally approved.</p>
                           <p> <table width="100%" border="1" cellspacing="0" cellpadding="5">
                             <tr>
                               <td style="width:200px;font-size:13px;"><b>Vide Request ID</b></td>
			       <td style="width:200px;font-size:13px;"><b>Leave Type</b></td>
                               <td style="width:200px;font-size:13px;"><b>From Date</b></td>
                               <td style="width:200px;font-size:13px;"><b>To Date</b></td>
                               <td style="width:120px;font-size:13px;"><b>'.((is_on_duty($row['LDP_CODE'])) ? 'Number Of Days' : 'Leave Balance').'</b></td>
                             </tr>';
                             $emailContent.='<tr height="30">
                                         <td style="font-size: 12px;">'.$leaveId.'</td>
					 <td style="font-size: 12px;">'.$row['LEAVE_TYPE_NAME'].'</td>
                                         <td style="font-size: 12px;">'.$row['LDP_FROM_DATE'].'</td>
                                         <td style="font-size: 12px;">'.$row['LDP_TO_DATE'].'</td>
                                         <td style="font-size: 12px;" align="left">'.((is_on_duty($row['LDP_CODE'])) ? $row['EFFECTIVE_DAYS'] : $avlLeave).'</td>
                                       </tr>';
                           $emailContent.=' <tr height="30">
                                         <td style="font-size: 12px;"><b>Remark</b></td>
                                         <td style="font-size: 12px;" colspan="3">'.$row['LDP_REMARKS1'].'</td>
                                       </tr>
                           </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: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="Leave Application- ".$row['EMPLOYEE_NAME']." (".$row['LDP_FROM_DATE']." to ".$row['LDP_TO_DATE'].")";
         //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_ID']."',      --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>
           '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);
       }
       }
       else
       {
        $Record=base64_encode ($emp_id."_".$leaveId."_".$nextLvl);
         $emailContent='<!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>Approver</b>,</p>
                           <p><span style="font-size:14px; color:#444444;">'.$row['EMPLOYEE_NAME'].'</span> has submitted Leave Request for your approval with following summary:</p>
                             <p> <table width="100%" border="1" cellspacing="0" cellpadding="5">
                               <tr>
                                 <td style="width:200px;font-size:13px;"><b>Vide Request ID</b></td>
				 <td style="width:200px;font-size:13px;"><b>Leave Type</b></td>
                                 <td style="width:200px;font-size:13px;"><b>From Date</b></td>
                                 <td style="width:200px;font-size:13px;"><b>To Date</b></td>
                                 <td style="width:120px;font-size:13px;"><b>'.((is_on_duty($row['LDP_CODE'])) ? 'Number Of Days' : 'Leave Balance').'</b></td>
                               </tr>';
                               $emailContent.='<tr height="30">
                                           <td style="font-size: 12px;">'.$leaveId.'</td>
					   <td style="font-size: 12px;">'.$row['LEAVE_TYPE_NAME'].'</td>
                                           <td style="font-size: 12px;">'.$row['LDP_FROM_DATE'].'</td>
                                           <td style="font-size: 12px;">'.$row['LDP_TO_DATE'].'</td>
                                           <td style="font-size: 12px;" align="left">'.((is_on_duty($row['LDP_CODE'])) ? $row['EFFECTIVE_DAYS'] : $avlLeave).'</td>
                                         </tr>';
                             $emailContent.=' <tr height="30">
                                           <td style="font-size: 12px;"><b>Remark</b></td>
                                           <td style="font-size: 12px;" colspan="3">'.$row['LDP_REMARKS1'].'</td>
                                         </tr>
                             </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/LeavePortal/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/LeavePortal/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: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="Leave Application- ".$row['EMPLOYEE_NAME']." (".$row['LDP_FROM_DATE']." to ".$row['LDP_TO_DATE'].")";
         //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>
           '".$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>
           'COUNTER DAILY',                      --Claim system (will change later on)<br>
           Null     --bcc email id (Null for none)<br>
           );
           END;";
         if($row1['EMAIL']!="")
         {
           $insertEmailQuery = oci_parse($dbcon, $strInsertEmailQuery);
           oci_execute($insertEmailQuery);
         }
       }
       //echo"<pre>";print_r($emailContent); die; 
     }
     $strUpdateQuery = "UPDATE  ytycustom.emp_leave_entry_hd SET  approval_reason='".$_POST['reason'.$leaveId]."',status_date=sysdate $upgradeLevel $claimStatus where req_id='".$leaveId."'";
     $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(images/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"); ?>
