<?php
date_default_timezone_set('Asia/Manila');
header('Content-Type: application/json');
 
$sqlUser = 'autoph_arnel';
$sqlDatabase = 'autoph_connect';
$sqlPass = 'Hke@2001';
$toDate = date_create() ->format("Y/m/d");
$myDate = date_create() ->format("Y-m-d"); 
$toTime = date('H:i:s');
$dateTime = $myDate .  " " .$toTime;

$conn = mysql_connect($sqlHost, $sqlUser, $sqlPass) or die ("Couldn't connct to MySQL server on $sqlHost: ". mysql_error(). '.');
$db =  mysql_select_db($sqlDatabase, $conn) or die ("Couldn't select database $sqlDatabase: ". mysql_error(). '.');
 
//##########################################################################
// ITEXMO SEND SMS API - PHP - CURL-LESS METHOD
// Visit www.itexmo.com/developers.php for more info about this API
//##########################################################################
//$MyAPIcode = "TR-AUTOH331398_2V9R5";
//$MyPasswd = "36k)y3(915";
function itexmoXXXX($number,$message,$MyAPIcode,$MyPasswd){  
		$url = 'https://www.itexmo.com/php_api/api.php';
		$itexmo = array('1' => $number, '2' => $message, '3' => $MyAPIcode, 'passwd' => $MyPasswd	);
		$param = array(
			'http' => array(
				'header'  => "Content-type: application/x-www-form-urlencoded\r\n",
				'method'  => 'POST',
				'content' => http_build_query($itexmo),
			),
		);
		$context  = stream_context_create($param);
		return file_get_contents($url, false, $context);
}
//##########################################################################
function itexmo($number,$message,$apicode,$passwd){
			$ch = curl_init();
			$itexmo = array('1' => $number, '2' => $message, '3' => $apicode, 'passwd' => $passwd);
			curl_setopt($ch, CURLOPT_URL,"https://www.itexmo.com/php_api/api.php");
			curl_setopt($ch, CURLOPT_POST, 1);
			 curl_setopt($ch, CURLOPT_POSTFIELDS, 
			          http_build_query($itexmo));
			curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
			return curl_exec ($ch);
			curl_close ($ch);
}
//##########################################################################
//##########################################################################
$ApiKey= $_REQUEST['keyID']; // Expect LoginName, LoginPassword   
$transID= $_REQUEST['transID']; // Expect LoginName, LoginPassword   
$approverID= $_REQUEST['approverID']; // Expect LoginName, LoginPassword   
$MyAPIcode = mysql_result(mysql_query("SELECT `SMSAPICode` FROM `asa_versioning_db` WHERE `type`='Live'"),0);
$MyPasswd =  mysql_result(mysql_query("SELECT `SMSAPIPass` FROM `asa_versioning_db` WHERE `type`='Live'"),0); 
//==================UPDATE FUNCTIONS===========================
//==================UPDATE FUNCTIONS=========================== 3818206-40
//==================UPDATE FUNCTIONS===========================
if($ApiKey == 10101088991100){  //////SIGNED CLEARANCE FORM
$myKeyCode = mysql_result(mysql_query("SELECT `key_code` FROM `cl_clearance_data` WHERE `cl_id`='$transID'"),0);
$ClearanceHdrID = mysql_result(mysql_query("SELECT `h_id` FROM `cl_clearance_header` WHERE `key_code`='$myKeyCode'"),0);
$CLempID = mysql_result(mysql_query("SELECT `cl_h_emp_id` FROM `cl_clearance_header` WHERE `key_code`='$myKeyCode'"),0);

	//mysql_query("UPDATE `cl_clearance_data` SET `cl_approved_by_id` = '$approverID', `cl_approved_date`='$myDate',`cl_approved_time`='$toTime' , WHERE `cl_id` = '$transID'");
	    //===========================
		$olddate = mysql_result(mysql_query("SELECT `cl_sla_start` FROM `cl_clearance_data` WHERE `cl_id`='$transID'"),0);
			$endDate = mysql_result(mysql_query("SELECT `cl_sla_end` FROM `cl_clearance_data` WHERE `cl_id`='$transID'"),0);
			 
			$myEndDate = strtotime($endDate);
			 
			$your_date = strtotime($olddate);
			$datediff = $myEndDate - $your_date;
			 
				$totalCount = round($datediff / (60 * 60 * 24));
		 
		//CHECK ACTUAL SLA
    	mysql_query("UPDATE `cl_clearance_data` SET `cl_approved_by_id` = '$approverID', `cl_approved_date`='$myDate', `cl_approved_time`='$toTime',`cl_sla_end`='$myDate',`cl_status`='Cleared',`cl_actual_sla`='$totalCount' WHERE `key_code`='$myKeyCode' AND `cl_id`='$transID' AND `cl_approver_id_a`='$approverID' || `key_code`='$myKeyCode' AND `cl_id`='$transID' AND `cl_approver_id_b`='$approverID'");
		//===========================
	
        $countTotalApprover = mysql_result(mysql_query("SELECT COUNT(cl_id) FROM `cl_clearance_data` WHERE  `key_code`='$myKeyCode' AND `cl_approver_id_a` != '0' || `key_code`='$myKeyCode' AND `cl_approver_id_b` != 0"),0);
		$countApproved = mysql_result(mysql_query("SELECT COUNT(cl_id) FROM `cl_clearance_data` WHERE  `key_code`='$myKeyCode' AND `cl_approved_by_id` != '0' AND `cl_approved_date` !=''"),0);
			//$compRatings = number_format((($countApproved / $countTotalApprover) * 100),2);
			if($countApproved == $countTotalApprover){
			    mysql_query("UPDATE `cl_clearance_header` SET `cl_h_hr_approval` = 'LASTPAY COMPUTATION', `hdr_status`='2',`signatory_completion_date`='$myDate' WHERE `key_code`='$myKeyCode'");	
				mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='2' WHERE `key_code`='$myKeyCode'");	
				
				//##############################################################
				//##############################################################
				//##############################################################
				// SEND SMS TO ENDORSING OFFICERS 1 & 2 
				// SEND SMS TO ENDORSING OFFICERS 1 & 2
				//##############################################################
				$endorsingIDA= mysql_result(mysql_query("SELECT `cl_recommender_a_id` FROM `cl_clearance_header` WHERE `h_id`='$ClearanceHdrID'"),0);
				$endorsingIDB= mysql_result(mysql_query("SELECT `cl_recommender_b_id` FROM `cl_clearance_header` WHERE `h_id`='$ClearanceHdrID'"),0);
				
					$empMobileA = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$endorsingIDA'"),0);
					$empMobileB = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$endorsingIDB'"),0);
					$SMSMessage = "Clearance form has been routed to you for your recommendation.";
					$result = itexmo($empMobileA, $SMSMessage,$MyAPIcode,$MyPasswd);
					
					$result = itexmo($empMobileB, $SMSMessage,$MyAPIcode,$MyPasswd);		 
					if ($result == ""){
						echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
					}else if ($result == 0){
						echo "SMS Sent!";
					}
					else{	
						echo "Error Num ". $result . " was encountered!";
					}	
				//##############################################################
			    //############################################################## 
			}
			
		// SEND MAIL FUNCTION HR APPROVING OFFICER=========================================================
		// SEND MAIL FUNCTION HR APPROVING OFFICER=========================================================
		// SEND MAIL FUNCTION HR APPROVING OFFICER=========================================================
		  $hFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
		  $hLname = mysql_result(mysql_query("SELECT `u_lname` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
		  $hEmail = mysql_result(mysql_query("SELECT `email` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
		  $approvalFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$approverID'"),0);
		  $approvalLname = mysql_result(mysql_query("SELECT `u_lname` FROM `vts_users` WHERE `u_id`='$approverID'"),0);
		  $getApproverFullName = $approvalFname ." ". $approvalLname;
		  $getApproverFullNameSMS = $approvalFname ." ". $approvalLname;
		  
		        $getRegisteredHR = $hFname ." ". $hLname ."!";
				$fromH = "support@autohubgroup.com";
				$toH = $hEmail;
				$subjectH = "CLEARANCE UPDATE";
				
				$countTotalApprover = mysql_result(mysql_query("SELECT COUNT(cl_id) FROM `cl_clearance_data` WHERE `key_code`='$myKeyCode' AND `cl_approver_id_a` != '0' || `key_code`='$myKeyCode' AND `cl_approver_id_b` != 0"),0);
					
				$countApproved = mysql_result(mysql_query("SELECT COUNT(cl_id) FROM `cl_clearance_data` WHERE `key_code`='$myKeyCode' AND `cl_approved_by_id` != '0' AND `cl_approved_date` !=''"),0);
				$compRate = number_format((($countApproved / $countTotalApprover) * 100),2) . "%";
					
$msgBoxH = "Hi ".$getRegisteredHR."
				
Your clearance form has been signed by ".$getApproverFullName.".  Your clearance status is now at ".$compRate." completion rate as of ".$myDate.".  If you have any questions or clarificaiton please call us @ 8860-8888 and we are happy to assist you.  
				
Autohub Group";
				
				$headersH = "From:" . $fromH;
				mail($toH, $subjectH, $msgBoxH, $headersH);
		//######################################################################
		//######################################################################
            //##################################################################
    		//##################################################################
    		//##################################################################
    		// SEND SMS TO CLEARANCE OWNER=========================================================
    		// SEND SMS TO CLEARANCE OWNER=========================================================
    		//##################################################################
    		 	$empMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
    			$SMSMessage = "Your clearance has been signed by ".$getApproverFullNameSMS.".";
    			$result = itexmo($empMobile, $SMSMessage,$MyAPIcode,$MyPasswd);		 
    			if ($result == ""){
    			echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
    			}else if ($result == 0){
    			//	echo "SMS Sent!";
    			}
    			else{	
    				echo "<script>alert('Error Num'. $result . ' was encountered!')</script>";
    			}	
    		//##################################################################
    		//##################################################################
    $message = "Clearance Form has been signed";
    $json = json_encode(["UpdateMessage" => "$message","Visible" => "false","VisibleTwo" => "true"]);
    
}
//******************************************************************************
//******************************************************************************
//******************************************************************************
if($ApiKey == 20101088991122){//FOR APPROVAL REST API FUNCTION
    $transID= $_REQUEST['transID']; // Expect LoginName, LoginPassword   
    $approverID= $_REQUEST['approverID']; // Expect LoginName, LoginPassword
    $myKeyCode = mysql_result(mysql_query("SELECT `key_code` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);   
    $CLempID = mysql_result(mysql_query("SELECT `cl_h_emp_id` FROM `cl_clearance_header` WHERE `key_code`='$myKeyCode'"),0);
    $templateID = mysql_result(mysql_query("SELECT `cl_h_template_id` FROM `cl_clearance_header` WHERE `key_code`='$myKeyCode'"),0);
    $PAYempID = mysql_result(mysql_query("SELECT `payroll_officer` FROM `cl_template_name` WHERE `id`='$templateID'"),0);
	
	//==================================================================
	$clApproverA = mysql_result(mysql_query("SELECT `cl_approver_id` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
	$clReason = mysql_result(mysql_query("SELECT `cl_h_reason` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
	
	$clApproverATS = mysql_result(mysql_query("SELECT `cl_approver_id_timestamp` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
		
	$clApproverB = mysql_result(mysql_query("SELECT `cl_approver_id_b` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
	$clApproverBTS = mysql_result(mysql_query("SELECT `cl_approver_id_b_timestamp` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
	$smsCode ='';		
		
		if($clApproverA == $approverID){
			if($clApproverBTS!=''){
			   if($clReason == 'LEAVE' || $clReason == 'REASSIGNMENT' || $clReason == 'TRANSFER' || $clReason == 'DETAIL' || $clReason == 'SUSPENSION' || $clReason == 'REEMPLOYMENT' || $clReason == 'MATERNITY' || $clReason == 'LEAVE OF ABSENCE'){
			       $smsCode='NO';
			        mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_timestamp` = '$dateTime', `cl_h_hr_approval`='CLOSED', `hdr_status`='4',`cl_sla_end` = '$dateTime',`cl_date_of_release` = '$dateTime',`cl_lastpay_released_date` = '$dateTime',`cl_second_wave`='CLOSED' WHERE `h_id`='$transID'");
			      
			    	mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$keyCode'");  
			    }else{
			     $smsCode='YES';	   
				mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_timestamp` = '$dateTime', `cl_h_hr_approval`='CHECK PREPARATION', `hdr_status`='4', `cl_second_wave`='CHECK PREPARATION' WHERE `h_id`='$transID'");
				mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$keyCode'");	
			    }
			    
			}else{
				if($clApproverB == 0){
				    if($clReason == 'LEAVE' || $clReason == 'REASSIGNMENT' || $clReason == 'TRANSFER' || $clReason == 'DETAIL' || $clReason =='SUSPENSION' || $clReason == 'REEMPLOYMENT' || $clReason == 'MATERNITY'){
				    $smsCode='NO';	    
			        mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_timestamp` = '$dateTime', `cl_h_hr_approval`='CLOSED', `hdr_status`='4',`cl_sla_end` = '$dateTime',`cl_date_of_release` = '$dateTime',`cl_lastpay_released_date` = '$dateTime',`cl_second_wave`='CLOSED' WHERE `h_id`='$transID'");
			        mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$keyCode'");  
				    }else{
				    $smsCode='YES';	
					mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_timestamp` = '$dateTime', `cl_h_hr_approval`='CHECK PREPARATION', `hdr_status`='4', `cl_second_wave`='CHECK PREPARATION' WHERE `h_id`='$transID'");
				
				    mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$keyCode'");	
				    }
				}else{
					mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_timestamp` = '$dateTime' WHERE `h_id`='$transID'");
				}
			}
		}
	//==================================================================
	if($clApproverB == $approverID){
			if($clApproverATS!=''){
			   if($clReason == 'LEAVE' || $clReason == 'REASSIGNMENT' || $clReason == 'TRANSFER' || $clReason == 'DETAIL' || $clReason == 'SUSPENSION' || $clReason == 'REEMPLOYMENT' || $clReason == 'MATERNITY'){
			      $smsCode='NO';	 
			      mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_b_timestamp` = '$dateTime', `cl_h_hr_approval`='CLOSED', `hdr_status`='4',`cl_sla_end` = '$dateTime',`cl_date_of_release` = '$dateTime',`cl_lastpay_released_date` = '$dateTime',`cl_second_wave`='CLOSED' WHERE `h_id`='$transID'");
			      
			    	mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$keyCode'");  
			    }else{
			    $smsCode='YES';	
				mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_b_timestamp` = '$dateTime', `cl_h_hr_approval`='CHECK PREPARATION', `hdr_status`='4', `cl_second_wave`='CHECK PREPARATION' WHERE `h_id`='$transID'");
				mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$keyCode'");	
			    }
			    
			}else{
				if($clApproverB == 0){
				    if($clReason == 'LEAVE' || $clReason == 'REASSIGNMENT' || $clReason == 'TRANSFER' || $clReason == 'DETAIL' || $clReason =='SUSPENSION' || $clReason == 'REEMPLOYMENT' || $clReason == 'MATERNITY'){
				    $smsCode='NO';	    
			        mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_timestamp` = '$dateTime', `cl_h_hr_approval`='CLOSED', `hdr_status`='4',`cl_sla_end` = '$dateTime',`cl_date_of_release` = '$dateTime',`cl_lastpay_released_date` = '$dateTime',`cl_second_wave`='CLOSED' WHERE `h_id`='$transID'");
			        mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$keyCode'");  
				    }else{
				    $smsCode='YES';	    
					mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_b_timestamp` = '$dateTime', `cl_h_hr_approval`='CHECK PREPARATION', `hdr_status`='4', `cl_second_wave`='CHECK PREPARATION' WHERE `h_id`='$transID'");
				
				    mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$keyCode'");	
				    }
				}else{
					mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_b_timestamp` = '$dateTime' WHERE `h_id`='$transID'");
				}
			}
		}
	//==================================================================
	
//mysql_query("UPDATE `cl_clearance_header` SET `cl_approver_id_timestamp` = '$dateTime', `cl_h_hr_approval`='LASTPAY COMPUTATION', `hdr_status`='4', `cl_second_wave`='LASTPAY COMPUTATION' WHERE `h_id`='$transID'");
	//mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$myKeyCode'");
		 
		 
		 // SEND MAIL FUNCTION TO EMPLOYEE=========================================================
		 // SEND MAIL FUNCTION TO EMPLOYEE=========================================================		 
		  $hFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
		  $hLname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
		  
		  $sFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$PAYempID'"),0);
		  $sLname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$PAYempID'"),0);
		   $POfficerMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$PAYempID'"),0);
		  
		  $hEmail = mysql_result(mysql_query("SELECT `email` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
		   $sEmail = mysql_result(mysql_query("SELECT `email` FROM `vts_users` WHERE `u_id`='$PAYempID'"),0);
		 		$getRegisteredHR = $hFname ." ". $hLname;
		 		$getRegisteredPayroll = $sFname ." ". $sLname;
				$fromH = "support@autohubgroup.com";
				$toH = $hEmail;
				$toS = $sEmail;
				$subjectH = "CLEARANCE UPDATE";
$msgBoxH = "Hi ".$getRegisteredHR."! 

Your clearance form has been approved.  We are now processing the computation of your final pay.  Please check your e-mail for more updates about the status of your clerance form and the release of your check. If you have any questions or clarificaiton please call us @ 8860-8888 and we are happy to assist you.  

Support Group";

$msgBoxS = "Hi ".$getRegisteredPayroll."! 

New Clearance Form has been approved. You can now process the final pay computation of ".$getRegisteredHR.". If you have any questions or clarificaiton please call us @ 8860-8888 and we are happy to assist you.  

Support Group";

				$headersH = "From:" . $fromH;
				$headersS = "From:" . $fromH;
			    if($smsCode==='YES'){
					mail($toH,$subjectH,$msgBoxH, $headersH);					
					mail($toS,$subjectH,$msgBoxS, $headersS);				
				}					
		//======================================================================
		//======================================================================
		//######################################################################
		//######################################################################
		//######################################################################
		// SEND SMS TO AUTHORIZING HR OFFICER=========================================================
		// SEND SMS TO AUTHORIZING HR OFFICER=========================================================
		//######################################################################
			//$getTemplateID = mysql_result(mysql_query("SELECT `cl_h_template_id` FROM `cl_clearance_header` WHERE `key_code`='$keyCode'"),0);
			$empMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
			$SMSMessage = "Your clearance form has been approved.  We are now processing your Last pay.";
			$SMSMessagePay = "Hello ". $sFname ."! ". "Clearance of ". $hFname . " ". $hLname. " has been approved.  You can now prepare the lastpay computation.";
			if($smsCode==='YES'){
    			$result = itexmo($empMobile, $SMSMessage,$MyAPIcode,$MyPasswd);
    		    $result = itexmo($POfficerMobile, $SMSMessagePay, $MyAPIcode,$MyPasswd);
    			if ($result == ""){
    				echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
    			}else if ($result == 0){
    				//echo "SMS Sent!";
    			}
    			else{	
    			    echo "<script>alert('Error Num '. $result . ' was encountered!')</script>";	
    					
    					 
    			}	
			}
		//######################################################################
		//######################################################################
 
 $message = "Clearance Form has been approved";
    $json = json_encode(["UpdateMessage" => "$message","CLStatus" => "$clReason","Visible" => "false","VisibleTwo" => "true"]);
}
//******************************************************************************
//******************************************************************************
//******************************************************************************
if($ApiKey == 30101088991133){ //FOR ENDORSEMENT REST API
    $myKeyCode = mysql_result(mysql_query("SELECT `key_code` FROM `cl_clearance_data` WHERE `cl_id`='$transID'"),0);   
    $CLempID = mysql_result(mysql_query("SELECT `cl_h_emp_id` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
	$endorserA = mysql_result(mysql_query("SELECT `cl_recommender_a_id` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
	$endorserAT = mysql_result(mysql_query("SELECT `cl_recommender_a_timestamp` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
	$endorserB = mysql_result(mysql_query("SELECT `cl_recommender_b_id` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
	$endorserBT = mysql_result(mysql_query("SELECT `cl_recommender_b_timestamp` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);   
	//******check endorser #1 if exist******************************************
	//******check endorser #1 if exist******************************************
	if($endorserA == $approverID){
			if($endorserBT != ''){
				mysql_query("UPDATE `cl_clearance_header` SET `cl_recommender_a_timestamp` = '$dateTime', `cl_h_hr_approval`='FINAL APPROVAL', `hdr_status`='4' WHERE `h_id`='$transID'");
				mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$myKeyCode'");
				//##########################################################################
				// SEND SMS TO APPROVING OFFICER AND TO EMPLOYEE=========================================================
				// SEND SMS TO APPROVING OFFICER AND TO EMPLOYEE=========================================================
				//#######################################################################$approverID###
				 
					$approverMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$approverID'"),0);
					$empMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
					$SMSMessageA = "New Employee Clearance Form has been routed to you for your approval";
					$SMSMessageE = "Your clearance form is now for final approval.";
					$result = itexmo($approverMobile, $SMSMessageA,$MyAPIcode,$MyPasswd);
					$result = itexmo($empMobile, $SMSMessageE,$MyAPIcode,$MyPasswd);					
					if ($result == ""){
						echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
					}else if ($result == 0){
						    //echo "SMS Sent!";
					}
					else{	
						echo '<script>alert("Error Num '. $result . ' was encountered!")</script>';	
					}
				 
			}else{
				mysql_query("UPDATE `cl_clearance_header` SET `cl_recommender_a_timestamp` = '$dateTime', `hdr_status`='2' WHERE `h_id`='$transID'");
				mysql_query("UPDATE `cl_clearance_data` SET `hdr_status` = '2' WHERE `key_code`='$myKeyCode'");
				//##########################################################################
				// SEND SMS TO EMPLOYEE ONLY =========================================================
				// SEND SMS TO EMPLOYEE ONLY =========================================================
				//############################################################## 
					$empMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
					$SMSMessage = "Your clearance form has been endorsed to next officer";
				 
					$result = itexmo($empMobile, $SMSMessage,$MyAPIcode,$MyPasswd);					
					if ($result == ""){
						echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
					}else if ($result == 0){
						//echo "SMS Sent!";
					}
					else{	
						echo '<script>alert("Error Num '. $result . ' was encountered!")</script>';	
					}	
				//##############################################################
				//##############################################################
			}
		}
if($endorserB == $approverID){
			if($endorserAT != ''){
				mysql_query("UPDATE `cl_clearance_header` SET `cl_recommender_b_timestamp` = '$dateTime', `cl_h_hr_approval`='FINAL APPROVAL', `hdr_status`='4' WHERE `h_id`='$transID'");
				mysql_query("UPDATE `cl_clearance_data` SET `hdr_status`='4' WHERE `key_code`='$myKeyCode'");
				//##############################################################
				// SEND SMS TO APPROVING OFFICER AND TO EMPLOYEE=========================================================
				// SEND SMS TO APPROVING OFFICER AND TO EMPLOYEE=========================================================
				//##############################################################
				 
				 
					$approverMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$approverID'"),0);
					$empMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
					$SMSMessageA = "New Employee Clearance Form has been routed to you for your approval";
					$SMSMessageE = "Your clearance form is now for Final Approval.";
					$resultA = itexmo($approverMobile, $SMSMessageA,$MyAPIcode,$MyPasswd);
					if ($resultA == ""){
						echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
					}else if ($result == 0){
						    //echo "SMS Sent!";
					}
					else{	
						echo '<script>alert("Error Num '. $result . ' was encountered!")</script>';	
					}
					$resultE = itexmo($empMobile, $SMSMessageE,$MyAPIcode,$MyPasswd);					
					if ($resultE == ""){
						echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
					}else if ($result == 0){
						    //echo "SMS Sent!";
					}
					else{	
						echo '<script>alert("Error Num '. $result . ' was encountered!")</script>';	
					}
				 
			}else{
				mysql_query("UPDATE `cl_clearance_header` SET `cl_recommender_b_timestamp` = '$dateTime', `hdr_status`='2' WHERE `h_id`='$transID'");
				mysql_query("UPDATE `cl_clearance_data` SET `hdr_status` = '2' WHERE `key_code`='$myKeyCode'");
				//##########################################################################
				// SEND SMS TO EMPLOYEE ONLY =========================================================
				// SEND SMS TO EMPLOYEE ONLY =========================================================
				//############################################################## 
					$empMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
					$SMSMessage = "Your clearance form has been endorsed to next officer";
				 
					$result = itexmo($empMobile, $SMSMessage,$MyAPIcode,$MyPasswd);					
					if ($result == ""){
						echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
					}else if ($result == 0){
						//echo "SMS Sent!";
					}
					else{	
						echo '<script>alert("Error Num '. $result . ' was encountered!")</script>';	
					}	
				//##############################################################
				//##############################################################
			}
		}
//##############################################################################
//##############################################################################
//==============================================================================
//==============================================================================
// SEND MAIL FUNCTION TO EMPLOYE
// SEND MAIL FUNCTION TO EMPLOYEE
$hFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
$hLname = mysql_result(mysql_query("SELECT `u_lname` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
$hEmail = mysql_result(mysql_query("SELECT `email` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
$hMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$CLempID'"),0);
$getRegisteredHR = $hFname ." ". $hLname;
$fromH = "support@autohubgroup.com";
$toH = $hEmail;
$subjectH = "CLEARANCE UPDATE";
$msgBoxH = "Hi ".$getRegisteredHR."!

Your clearance form has been endorsed to next higher officer.

Please <a href='https://autohub.ph/connect'>Login</a> to your account via Portal or Mobile App to check the status of your clearance.   If you have any questions or clarificaiton please call us @ 8860-8888 and we are happy to assist you.  

Support Group";
				$headersH = "From:" . $fromH;
				mail($toH,$subjectH,$msgBoxH, $headersH);					
//==============================================================================
//==============================================================================
//##############################################################################
//##############################################################################
 $message = "Clearance Form has been endorsed to next higher officer";
$json = json_encode(["UpdateMessage" => "$message","Visible" => "false","VisibleTwo" => "true"]);
}
//******************************************************************************
//******************************************************************************
//******************************************************************************
if($ApiKey == 40101088991144){  //////AUTHORIZED CLEARANCE FORM
$transID= $_REQUEST['transID']; // Expect LoginName, LoginPassword   
$myKeyCode = mysql_result(mysql_query("SELECT `key_code` FROM `cl_clearance_header` WHERE `h_id`='$transID'"),0);
//$clHeaderID = mysql_result(mysql_query("SELECT `h_id` FROM `cl_clearance_header` WHERE `key_code`='$myKeyCode'"),0); 
mysql_query("UPDATE `cl_clearance_header` SET `cl_h_hr_approval` = 'POSTED',`cl_sla_start`='$myDate', `hdr_status`='1',`cl_hr_approval_timestamp`='$dateTime' WHERE `h_id`='$transID'");
mysql_query("UPDATE `cl_clearance_data` SET `cl_hdr_id` = '$transID', `hdr_status`='1',`cl_sla_start`='$myDate' WHERE `key_code`='$myKeyCode'");
 $message = "New Clearance Form has been routed to all approving officers"; 
 
 
 $getApprover = mysql_query("SELECT * FROM `cl_clearance_data` WHERE  `key_code`='$myKeyCode' AND `cl_approver_id_a` != 0 AND `cl_approver_id_b` != 0");
 while($snRow = mysql_fetch_array($getApprover)){
				$approverIDa = $snRow['cl_approver_id_a'];
				$approverIDb = $snRow['cl_approver_id_b'];
				if($approverIDa != 0 ){
					$aFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$approverIDa'"),0);
					$aLname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$approverIDa'"),0);
					$aEmail = mysql_result(mysql_query("SELECT `email` FROM `vts_users` WHERE `u_id`='$approverIDa'"),0);
					$aMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$approverIDa'"),0);
					$getRegisteredApproverA = $aFname ." ". $aLname;
					$fromA = "support@autohubgroup.com";
					$toA = $aEmail;
					$subjectA = "NEW EMPLOYEE CLEARANCE";
$msgBoxA = "Hi ".$getRegisteredApproverA."! 

New Employee Clearance Form has been routed to you for your signature.  Please <a href='https://autohub.ph/connect'>Login</a> to your account via Portal or Mobile App to process the clearance.   If you have any questions or clarificaiton please call us @ 8860-8888 and we are happy to assist you.  

Support Group";
					$headersA = "From:" . $fromA;
					mail($toA,$subjectA,$msgBoxA, $headersA);
					//##########################################################
					//SEND SMS TO ALL CLEARING OFFICERS/////////////
					//##########################################################
					$clearingOfficerMsg = "New Employee Clearance Form has been routed to you for your signature";
					$result = itexmo($aMobile, $clearingOfficerMsg, $MyAPIcode, $MyPasswd);		 
					if ($result == ""){
						 	
					}else if ($result == 0){
						 
					}
					else{	
						$erroCount =  "Error Num ". $result . " was encountered!";
					}
					//##########################################################
					//##########################################################
				}
				//()()()()()()()()()()()()()()()()()()()()()()()()()()()()()()()
				//()()()()()()()()()()()()()()()()()()()()()()()()()()()()()()()
				if($approverIDb != 0 ){
					$bFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$approverIDb'"),0);
					$bLname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$approverIDb'"),0);
					$bEmail = mysql_result(mysql_query("SELECT `email` FROM `vts_users` WHERE `u_id`='$approverIDb'"),0);
					$bMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$approverIDb'"),0);
					$getRegisteredApproverB = "Hi ".$bFname ." ". $bLname ."!";
					$fromB = "support@autohubgroup.com";
					$toB = $bEmail;
					$subjectB = "NEW EMPLOYEE CLEARANCE";
$msgBoxB = "Hi ".$getRegisteredApproverB."! 

New Employee Clearance Form has been routed to you for your signature.  Please <a href='https://autohub.ph/connect'>Login</a> to your account via Portal or Mobile App to process the clearance.   If you have any questions or clarificaiton please call us @ 8860-8888 and we are happy to assist you.  

Support Group";
					$headersB = "From:" . $fromB;
					mail($toB,$subjectB,$msgBoxB, $headersB);
					//##########################################################
					//SEND SMS TO ALL CLEARING OFFICERS/////////////
					//##########################################################
					$clearingOfficerMsgB = "New Employee Clearance Form has been routed to you for your signature";
					$result = itexmo($bMobile, $clearingOfficerMsgB, $MyAPIcode, $MyPasswd);		 
					if ($result == ""){
						 	
					}else if ($result == 0){
						 
					}
					else{	
						$erroCount =  "Error Num ". $result . " was encountered!";
					}
					//##########################################################
					//##########################################################
				}
				//()()()()()()()()()()()()()()()()()()()()()()()()()()()()()()()
				//()()()()()()()()()()()()()()()()()()()()()()()()()()()()()()()
    }
  $json = json_encode(["UpdateMessage" => "$message","Visible" => "false","VisibleTwo" => "true"]);   
 }
//******************************************************************************
//******************************************************************************
//******************************************************************************
if($ApiKey == 50101088991155){  //////SEND ACTION SLIP
    $ApiKey= $_REQUEST['keyID']; // Expect LoginName, LoginPassword   
    $transID= $_REQUEST['transID']; // Expect LoginName, LoginPassword
    $myKeyCode = mysql_result(mysql_query("SELECT `key_code` FROM `cl_clearance_data` WHERE `cl_id`='$transID'"),0);
    
    //$myKeyCode = mysql_result(mysql_query("SELECT `key_code` FROM `cl_clearance_data` WHERE `cl_id`='$transID'"),0);
    $ClearanceHdrID = mysql_result(mysql_query("SELECT `h_id` FROM `cl_clearance_header` WHERE `key_code`='$myKeyCode'"),0);
    $empID = mysql_result(mysql_query("SELECT `cl_h_emp_id` FROM `cl_clearance_header` WHERE `h_id`='$ClearanceHdrID'"),0);
    
    $approverID= $_REQUEST['approverID']; // Expect LoginName, LoginPassword   
    $ActionSlip= mysql_real_escape_string($_REQUEST['ActionSlip']); // Expect LoginName, LoginPassword   
    $Amount= mysql_real_escape_string($_REQUEST['Amount']); // Expect LoginName, LoginPassword   
    	$sql = "INSERT INTO cl_action_slip_data(cl_hdr_id,	cl_key_code,cl_emp_id, cl_request_by_id, cl_slip_info, cl_slip_date, cl_slip_time, amount) VALUES('$ClearanceHdrID','$myKeyCode','$empID', '$approverID' ,'$ActionSlip', '$myDate','$toTime','$Amount')";
    			$sqlQuery = mysql_query($sql, $conn) or die("Couldn't perform query $sql (".__LINE__."): " . mysql_error() . '.');
     $message = "New action slip has been sent to emplyee.";
     //##################################################################
	// SEND SMS TO CLEARANCE OWNER=========================================================
	// SEND SMS TO CLEARANCE OWNER=========================================================
	//##################################################################
	 	$empMobile = mysql_result(mysql_query("SELECT `usr_mobile` FROM `vts_users` WHERE `u_id`='$empID'"),0);
	 	$empFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$empID'"),0);
	 	$empLname = mysql_result(mysql_query("SELECT `u_lname` FROM `vts_users` WHERE `u_id`='$empID'"),0);
	    $empEmail = mysql_result(mysql_query("SELECT `email` FROM `vts_users` WHERE `u_id`='$empID'"),0);
	 	//====================================================
	 	$aprFname = mysql_result(mysql_query("SELECT `u_fname` FROM `vts_users` WHERE `u_id`='$approverID'"),0);
	 	$aprLname = mysql_result(mysql_query("SELECT `u_lname` FROM `vts_users` WHERE `u_id`='$approverID'"),0);
	 	$getApproverFullNameSMS =$aprFname . " ". $aprLname;
	 	//====================================================
		$SMSMessage = "New Action Slip has been sent by ".$getApproverFullNameSMS." for your compliance.";
		$result = itexmo($empMobile, $SMSMessage,$MyAPIcode,$MyPasswd);		 
		if ($result == ""){
		echo '<script>alert("No response from SMS server.  SMS failed to send")</script>';	
		}else if ($result == 0){
		//	echo "SMS Sent!";
		}
		else{	
			echo "<script>alert('Error Num'. $result . ' was encountered!')</script>";
		}	
	//##################################################################
	//##################################################################
    $getRegisteredAC = $empFname ." ". $empLname ."!";
	$fromH = "support@autohubgroup.com";
	$toH = $empEmail;
	$subjectH = "CLEARANCE UPDATE";
			 
$msgBoxH = "Hi ".$getRegisteredAC."
				
New Action Slip has been requested by " .$getApproverFullNameSMS." prior to signing of your clearance form.  Please attend to this request to prevent any delays in processing your clearance.  If you have any questions or clarificaiton please call us @ 8860-8888 and we are happy to assist you.  
				
Autohub Group";
				
				$headersH = "From:" . $fromH;
				mail($toH, $subjectH, $msgBoxH, $headersH);
		//######################################################################
		//######################################################################
    $json = json_encode(["UpdateMessage" => "$message","Visible" => "false","VisibleTwo" => "true"]);
    
}
//==================UPDATE FUNCTIONS===========================
//==================UPDATE FUNCTIONS===========================
//==================UPDATE FUNCTIONS===========================
echo $json; 
 
?>