	<?php
require_once('config.php');
require_once('utility_image.php');

//$uploaddir = "C:\\wamp64\\www\\CMMS_Standard\\temp\\";
$uploaddir;
$error_message;
$valid = true;
$json_return = array();
$json_return2 = array();
$json_return = [];

$mst_RowID;


if ($_SERVER["REQUEST_METHOD"] == "POST") { 

	$count    	   	= $_REQUEST["count"];
	$json 			= $_REQUEST["json"];	
	$json 			= json_decode($json);
	$rowid			= $json->data->rowid;
	$site_cd		= $json->data->site_cd;
	$EMPID			= $json->data->EMPID;
	$LOGINID		= $json->data->LOGINID;
	$folder			= $json->data->folder;
	$dvc_id			= $json->data->dvc_id;
	$base64string	= $_REQUEST["base64string"];
	
	$ack_name		= $json->data->ack_name;
	$ack_id			= $json->data->ack_id;
	$ack_contact	= $json->data->ack_contact;
	$status 		= 'CLO';
	
	$uploaddir = "C:\\wamp64\\www\\".$folder."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\";
	if (!is_dir($uploaddir)) {
			mkdir($uploaddir, 0777, true);
			mkdir($uploaddir."\\thumbnail\\", 0777, true);
	}
	
	// -----------------------------
    // UPDATE ler_mst status
    // -----------------------------
    $sql_upd_mst = "
        UPDATE ler_mst
        SET
            
            ler_mst_status     = ?,
            audit_user         = ?,
            audit_date         = GETDATE(),
            mbl_audit_user     = ?,
            mbl_audit_date     = GETDATE()
        WHERE site_cd = ?
          AND RowID = ?
    ";

    $params_mst = [
       
        $status,
        $LOGINID,
        $LOGINID,
        $site_cd,
        $rowid
    ];

    $stmt = sqlsrv_query($conn, $sql_upd_mst, $params_mst);
    if (!$stmt) throw new Exception("Update ler_mst error: " . print_r(sqlsrv_errors(), true));
    sqlsrv_free_stmt($stmt);
	
	
	$sql_upd_det = "
        UPDATE ler_det
        SET
			ler_det_sign_by 	= ?,
			ler_det_sign_date 	= GETDATE(),
			ler_det_sign_by_id 	= ?,
			ler_det_sign_by_contact = ?,
            audit_user     		= ?,
            audit_date     		= GETDATE(),
            mbl_audit_user 		= ?,
            mbl_audit_date 		= GETDATE()
        WHERE site_cd = ?
          AND mst_RowID = ?
    ";

    $stmt = sqlsrv_query($conn, $sql_upd_det, [$ack_name,$ack_id,$ack_contact,$LOGINID, $LOGINID, $site_cd, $rowid]);
    if (!$stmt) throw new Exception("Update ler_det error: " . print_r(sqlsrv_errors(), true));
    sqlsrv_free_stmt($stmt);
	
	
	$sql = "SELECT mst_RowID From ler_ref WHERE site_cd = '".$site_cd."' AND mst_RowID = ".$rowid." AND type = 'A' AND column2 = 'SIGN'";	

	$stmt = sqlsrv_query( $conn, $sql);	
			
	if( !$stmt ) {
		$error_message = "Error update table (ler_ref)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}
	do {
		 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		 
		 $mst_RowID = $row['mst_RowID'];	 
			 
	   }
	} while ( sqlsrv_next_result($stmt));
	
	if(empty($mst_RowID)){
		
		$parts   	  	= explode(";base64,", $base64string);
		$imageparts   	= explode("image/", @$parts[0]);
		$imagetype    	= $imageparts[1];
		$imagebase64  	= base64_decode($parts[1]);
		$random_id 		= rand(100000, 999999);
		$filename 		= 'linen_request_sign_'.$random_id.'.jpg';
		$file         	= $uploaddir . uniqid() . '.png';
		
		file_put_contents($file, $imagebase64);
		$fullPathwithfileName = $file;
		
		
		
		$datastring= file_get_contents($fullPathwithfileName);
		$data=unpack("H*hex", $datastring);
		//echo $data;
		
		$sql = "INSERT INTO ler_ref	(	site_cd, 			audit_user, 		audit_date, 		status,					column1, 			column2, 
										FILE_NAME, 			type, 				mst_rowid, 			attachment,				mbl_audit_user,		mbl_audit_date, category) 
							VALUES 	(	'".$site_cd."',		'".$LOGINID."', 	getdate(),			'Saved', 				'Native', 			'SIGN', 
										'".$filename."','A', 				".$rowid.", 		0x".$data['hex'].",		'".$LOGINID."',		getdate(), 'Linen Request Signature') SELECT SCOPE_IDENTITY() as rowid";
			
		$stmt = sqlsrv_query( $conn, $sql);				
						
		if( !$stmt ) {
			$error_message = "Error insert table (ler_ref)";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		}else{
			unlink($fullPathwithfileName);
		}
		$json_wko_ref = array();
		do {
			while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				$json_wko_ref['rowid'] = $row['rowid'];
				$json_wko_ref['action'] = "INSERT";
				array_push($json_return2, $json_wko_ref);
				
			}
		} while ( sqlsrv_next_result($stmt) );
		$json_return['ler_ref'] = $json_return2;
		

		
	}else{
		
		$parts   	  	= explode(";base64,", $base64string);
		$imageparts   	= explode("image/", @$parts[0]);
		$imagetype    	= $imageparts[1];
		$imagebase64  	= base64_decode($parts[1]);
		$random_id 		= rand(100000, 999999);
		$filename 		= 'Daily_linen_request_sign_'.$random_id.'.jpg';
		$file         	= $uploaddir . uniqid() . '.png';
		
		file_put_contents($file, $imagebase64);
		$fullPathwithfileName = $file;
	
		$datastring= file_get_contents($fullPathwithfileName);
		$data=unpack("H*hex", $datastring);
		
		$sql ="UPDATE 	ler_ref 
				SET  		
						attachment=0x".$data['hex'].",
						audit_user=?,
						audit_date=getdate(),	
						mbl_audit_user=?,	
						mbl_audit_date=getdate() 														
				where 	site_cd=? 
				and  	mst_rowid =? 
				and 	column2 =?  
				and 	type =? "; 
		
		$params = array($LOGINID,$LOGINID,$site_cd,$rowid,'SIGN','A');					
		
	
		$stmt_wko_ref = sqlsrv_query( $conn, $sql,$params);			
	
		if( !$stmt_wko_ref ) {
			print_r( sqlsrv_errors(), true);
			
			$error_message = "Error update table (UPDATE ler_ref)";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		}
		
		$json_wko_ref = array();
		do {
			while ($row = sqlsrv_fetch_array($stmt_wko_ref, SQLSRV_FETCH_ASSOC)) {
				$json_wko_ref['rowid'] = $row['rowid'];
				$json_wko_ref['action'] = "INSERT";
				array_push($json_return2, $json_wko_ref);
				
			}
	   } while ( sqlsrv_next_result($stmt_wko_ref) );	
			
		$json_return['ler_ref'] = $json_return2;
		
	}
	
	// ---------------------------------------------------------------------
	// 5) Insert STATUS AUDIT into ler_ls2 (avoid inserting same status multiple times)
	//    Rule: if latest status == new status -> SKIP
	// ---------------------------------------------------------------------

	
	// Get RR No
	$sql_chk_ls2 = "
		SELECT TOP 1 ler_mst_ler_no
		FROM ler_mst WITH (NOLOCK)
		WHERE site_cd = ? AND RowID = ?
	";
	$stmt = sqlsrv_query($conn, $sql_chk_ls2, [$site_cd, $rowid]);
	if ($stmt === false) {
		throw new Exception("Select ler_ls2 latest status error: " . print_r(sqlsrv_errors(), true));
	}

	$ler_mst_ler_no = '';
	if ($r = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		$ler_mst_ler_no = trim($r['ler_mst_ler_no'] ?? '');
	}
	sqlsrv_free_stmt($stmt);
	
	// Get latest status for this mst_RowID
	$sql_chk_ls2 = "
		SELECT TOP 1 ler_ls2_varchar2
		FROM ler_ls2 WITH (NOLOCK)
		WHERE site_cd = ? AND mst_RowID = ?
		ORDER BY ler_ls2_datetime1 DESC, RowID DESC
	";
	$stmt = sqlsrv_query($conn, $sql_chk_ls2, [$site_cd, $rowid]);
	if ($stmt === false) {
		throw new Exception("Select ler_ls2 latest status error: " . print_r(sqlsrv_errors(), true));
	}

	$lastStatus = '';
	if ($r = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		$lastStatus = trim($r['ler_ls2_varchar2'] ?? '');
	}
	sqlsrv_free_stmt($stmt);
	
	
	// B) Check if same status already exists anywhere for this mst_RowID
	$sql_chk_exists = "
		SELECT 1
		FROM ler_ls2 WITH (NOLOCK)
		WHERE site_cd = ? AND mst_RowID = ? AND ler_ls2_varchar2 = ?
	";
	$stmt = sqlsrv_query($conn, $sql_chk_exists, [$site_cd, $rowid, $status]);
	if ($stmt === false) {
		throw new Exception("Select ler_ls2 exists status error: " . print_r(sqlsrv_errors(), true));
	}
	$existsSameStatus = (sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC) !== null);
	sqlsrv_free_stmt($stmt);

	// Insert only if status changed
	if ($lastStatus !== $status && !$existsSameStatus) {

		
		$originator = $LOGINID; // you can change if you have separate originator
		$name       = $LOGINID; // replace with actual name if you have it
		
		$sql_upd_det = "
			UPDATE ler_ls2
			SET
				ler_ls2_datetime2 	= GETDATE(),
				audit_user     		= ?,
				audit_date     		= GETDATE(),
				mbl_audit_user 		= ?,
				mbl_audit_date 		= GETDATE()
			WHERE site_cd = ?
			  AND mst_RowID = ?
			  AND ler_ls2_datetime2 IS NULL
		";

		$stmt = sqlsrv_query($conn, $sql_upd_det, [$LOGINID, $LOGINID, $site_cd, $rowid]);
		if (!$stmt) throw new Exception("Update ler_ls2 error: " . print_r(sqlsrv_errors(), true));
		sqlsrv_free_stmt($stmt);

		$sql_ler_ls2 = "
			INSERT INTO ler_ls2 (
				site_cd, mst_RowID,
				ler_ls2_varchar1,   -- status cd
				ler_ls2_varchar2,   -- status desc
				ler_ls2_varchar3,   -- originator
				ler_ls2_varchar4,   -- name
				ler_ls2_datetime1,  -- start date
				audit_user, audit_date,
				mbl_audit_user, mbl_audit_date,
				column1, column2, column3, column4, column5
			)
			VALUES (
				?, ?,
				?, ?,
				?, ?,
				GETDATE(),
				?, GETDATE(),
				?, GETDATE(),
				NULL, NULL, NULL, NULL, NULL
			)
		";

		$params_ls2 = [
			$site_cd,
			$rowid,
			$ler_mst_ler_no,
			$status,
			$originator,
			$name,
			$LOGINID,
			$LOGINID
		];

		$stmt = sqlsrv_query($conn, $sql_ler_ls2, $params_ls2);
		if ($stmt === false) {
			throw new Exception("Insert ler_ls2 (status audit) error: " . print_r(sqlsrv_errors(), true));
		}
		sqlsrv_free_stmt($stmt);
	}

}
		
if ($valid) {
	//sqlsrv_free_stmt( $stmt);
	sqlsrv_close( $conn);	
	
	//Declare NULL Array since no records inserted, in future will insert as wko_ref
	//$json = array();
	
	returnData($json_return);	
}

function insertImage($image){

}

function returnData($json_return){
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'Acknowledgement signature has been successfully captured',
	'data' => $json_return);
	
	echo json_encode($returnData);
}

function returnError($error_message){
	$json = array();
	
	$returnData = array(
	'status' => 'ERROR',
	'message' => $error_message,
	'data' => $json);
	
	echo json_encode($returnData);
	
	
	//sqlsrv_close( $conn);	
		
	exit();
}


?>