<?php
require_once ('config.php');
require_once ('f_insert_email_log.php');
require_once ('send_notification_v1.php');
require_once ('f_insert_notification_log.php');
require_once ('find_online_tablet.php');
require_once ('f_logging.php');

$error_message;
$valid = true;
$insert_email = false;
$insert_ntf = false;
$now = date("Ymdhisa");
$file_name = __DIR__ . "\mobile_log\_update_close_workorder.log";

/* Begin the transaction. */
if (sqlsrv_begin_transaction($conn) === false)
{
    die(print_r(sqlsrv_errors() , true));
}

$json = file_get_contents('php://input');

// Converts it into a PHP object
$data = json_decode($json, true);

$site_cd = $data['site_cd'];
$sync_step = $data['sync_step'];
$sync_time = $data['sync_time'];
$sync_status = $data['sync_status'];
$sync_url = $data['sync_url'];
$LOGINID = $data['LOGINID'];
$dvc_id = $data['dvc_id'];

if ($sync_status == "online")
{

}
else
{
    f_logging($json, "update_action_workorder.php", $sync_url, $LOGINID, $sync_step, $sync_time, $site_cd);
}

$EmpID = $data['EmpID'];
$ROW_ID = $data['RowID'];

$wko_mst_status = $data['wko_mst_status'];
$wko_mst_status_after = $data['wko_mst_status_after'];

$wko_det_planner = $data['wko_det_planner'];
$wko_det_cause_code = $data['wko_det_cause_code'];
$wko_det_act_code = $data['wko_det_act_code'];
$wko_det_work_class = $data['wko_det_work_class'];
$wko_det_work_grp = $data['wko_det_work_grp'];
$wko_det_corr_action = $data['wko_det_corr_action'];
$wko_det_note1 = $data['wko_det_note1'];
$wko_mst_wo_no = $data['wko_mst_wo_no'];
$Is_checked = $data['Is_checked'];

$Assestno = $data['Assestno'];
$Assest_description = $data['Assest_description'];
$Work_Area = $data['Work_Area'];
$Assest_location = $data['Assest_location'];
$Assest_level = $data['Assest_level'];
$Originationdate = $data['Originationdate'];
$Duedate = $data['Duedate'];
$Work_Description = $data['Work_Description'];
$Requested_by = $data['Requested_by'];
$Contact_no = $data['Contact_no'];

$current_date = $data['current_date'];

$wko_det_varchar1 = $data['wko_det_varchar1'];
$wko_det_varchar2 = $data['wko_det_varchar2'];
$wko_det_varchar3 = $data['wko_det_varchar3'];

$wko_det_sched_date = $data['wko_det_sched_date'];
$wko_det_cmpl_date = $data['wko_det_cmpl_date'];
$wko_det_clo_date = $data['wko_det_clo_date'];

$wko_mst_org_date = $data['wko_mst_org_date'];
$wko_mst_due_date = $data['wko_mst_due_date'];

$dft_mst_cm_req_cmp = $data['dft_mst_cm_req_cmp'];
$dft_mst_pm_req_cmp = $data['dft_mst_pm_req_cmp'];

if (empty($wko_det_sched_date))
{
	$wko_det_sched_date = Null;
}
else
{
	$wko_det_sched_date = "".$wko_det_sched_date."";
}

if (empty($wko_det_cmpl_date))
{
	$wko_det_cmpl_date = Null;
}
else
{
	$wko_det_cmpl_date = "" . $wko_det_cmpl_date . "";
}


if (empty($wko_det_clo_date))
{
	$wko_det_clo_date = Null;
}
else
{
	$wko_det_clo_date = "" . $wko_det_clo_date . "";
}

if (empty($wko_mst_org_date))
{
	$wko_mst_org_date = Null;
}
else
{
	$wko_mst_org_date = "" . $wko_mst_org_date . "";
}


if (empty($wko_mst_due_date))
{
	$wko_mst_due_date = Null;
}
else
{
	$wko_mst_due_date = "" . $wko_mst_due_date . "";
}


if (empty($wko_det_planner))
{
	$wko_det_planner = Null;
}
else
{
	$wko_det_planner = "" . $wko_det_planner . "";
}

if (!empty($data['wko_det_work_grp'])) {

    $wko_det_work_grp = trim($data['wko_det_work_grp']);

} else {

    $wko_det_work_grp = NULL;

}


$sql = "select 	wrk_sts_typ_cd,wrk_sts_status,wrk_sts_desc,wko_mst_type,wko_mst_org_date
		from 	wko_mst(NOLOCK) 
		INNER 
		JOIN	wrk_sts (NOLOCK)
		ON		wko_mst.site_cd = wrk_sts.site_cd
		AND		wko_mst.wko_mst_status = wrk_sts.wrk_sts_status		
		where  wko_mst.site_cd = '".$site_cd."' 
		and wko_mst_wo_no = '".$wko_mst_wo_no."' ";			
	
$stmt = sqlsrv_query( $conn, $sql);			
if( !$stmt ) {
	$error_message = "Error select table (wrk_sts)";
	returnError($error_message);
	die( print_r( sqlsrv_errors(), true));
}
do {
	while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
				
		$wko_mst_type = $row['wko_mst_type'];						
		$wrk_sts_typ_cd = $row['wrk_sts_typ_cd'];						
		$wrk_sts_status = $row['wrk_sts_status'];						
		$wrk_sts_desc 	= $row['wrk_sts_desc'];						
		$wko_mst_org_date 	= $row['wko_mst_org_date'];						
		 
	}
} while ( sqlsrv_next_result($stmt));
sqlsrv_free_stmt( $stmt);	

if($dft_mst_cm_req_cmp === '0' ||$dft_mst_pm_req_cmp === '0'){
	
	$wrk_status = $wrk_sts_typ_cd ==='OPEN' ||  $wrk_sts_typ_cd ==='COMPLETE'  ||  $wrk_sts_typ_cd ==='ACKNOWLEDGEMENT' || ($wrk_sts_typ_cd === "CLOSE" && $wrk_sts_status <> $wko_mst_status_after);
	
}else{
	$wrk_status = $wrk_sts_typ_cd ==='COMPLETE' || $wrk_status = $wrk_sts_typ_cd ==='ACKNOWLEDGEMENT' || ($wrk_sts_typ_cd === "CLOSE" && $wrk_sts_status <> $wko_mst_status_after);
}



if($wrk_status){
	
	
$sql = "UPDATE	wko_det 
		SET		wko_det_cause_code=?,
				wko_det_act_code=?,		
				wko_det_work_class=?,
				wko_det_work_grp=?,
				wko_det_corr_action =?,
				wko_det_note1 =?,
				wko_det_sched_date =?,
				wko_det_cmpl_date =?,
				wko_det_planner =?,
				wko_det.audit_user =?,						
				wko_det.audit_date=GETDATE(),
				wko_det.mbl_audit_user =?,						
				wko_det.mbl_audit_date=GETDATE()
		WHERE	wko_det.site_cd =?
		AND		wko_det.mst_RowID =?";

$params_update_wko_det = array(
	trim($wko_det_cause_code),
	trim($wko_det_act_code),
	$wko_det_work_class,
	$wko_det_work_grp,
	$wko_det_corr_action,
	$wko_det_note1,
	$wko_det_sched_date,
	$wko_det_cmpl_date,
	$wko_det_planner,
	$LOGINID,
	$LOGINID,
	$site_cd,
	$ROW_ID
);
$stmt_wko_det = sqlsrv_query($conn, $sql, $params_update_wko_det);

if ($stmt_wko_det === false)
{
	if (($errors = sqlsrv_errors()) != null)
	{
		foreach ($errors as $error)
		{
			file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "json: " . $params_update_wko_det . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
		}
	}

	sqlsrv_rollback($conn);
	sqlsrv_close($conn);

	$error_message = "Error updating table (wko_det)";
	returnError($error_message);
}
sqlsrv_free_stmt($stmt_wko_det);



if ($Is_checked == 'true'){
	
	
	$sql_wrk_sts = "Select 	wrk_sts_tim_crd,wrk_sts_typ_cd
		from 	wrk_sts (NOLOCK)
		Where 	site_cd = '" . $site_cd . "'
		And 	wrk_sts_status = '" . $wko_mst_status. "'";

	$stmt_wrk_sts = sqlsrv_query($conn, $sql_wrk_sts);

	if ($stmt_wrk_sts === false)
	{
		if (($errors = sqlsrv_errors()) != null)
		{
			foreach ($errors as $error)
			{
				file_put_contents($file_name, $now . "\r\n" . $sql_wrk_sts . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
			}
		}

		sqlsrv_rollback($conn);
		sqlsrv_close($conn);

		$error_message = "Error selecting table (wrk_sts) - ".$now;
		returnError($error_message);
	}
	do
	{
		while ($row = sqlsrv_fetch_array($stmt_wrk_sts, SQLSRV_FETCH_ASSOC))
		{

			$wrk_sts_tim_crd = $row['wrk_sts_tim_crd'];
			$wrk_sts_typ_cd_before  = $row['wrk_sts_typ_cd'];
			

		}
	}
	while (sqlsrv_next_result($stmt_wrk_sts));
	
	
	$sql_wrk_sts = "Select 	wrk_sts_tim_crd,wrk_sts_typ_cd
		from 	wrk_sts (NOLOCK)
		Where 	site_cd = '" . $site_cd . "'
		And 	wrk_sts_status = '" . $wko_mst_status_after . "'";

	$stmt_wrk_sts = sqlsrv_query($conn, $sql_wrk_sts);

	if ($stmt_wrk_sts === false)
	{
		if (($errors = sqlsrv_errors()) != null)
		{
			foreach ($errors as $error)
			{
				file_put_contents($file_name, $now . "\r\n" . $sql_wrk_sts . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
			}
		}

		sqlsrv_rollback($conn);
		sqlsrv_close($conn);

		$error_message = "Error selecting table (wrk_sts) - ".$now;
		returnError($error_message);
	}
	do
	{
		while ($row = sqlsrv_fetch_array($stmt_wrk_sts, SQLSRV_FETCH_ASSOC))
		{

			$wrk_sts_tim_crd = $row['wrk_sts_tim_crd'];
			$wrk_sts_typ_cd = $row['wrk_sts_typ_cd'];
			

		}
	}
	while (sqlsrv_next_result($stmt_wrk_sts));
	
	if ($wrk_sts_tim_crd == "1"){
	
		// Time Card 
		$sql_wrk_sts = "Select 	counts = count(*) from 	wko_ls8 (NOLOCK) Where 	site_cd = '".$site_cd ."' And 	mst_RowID = '".$ROW_ID ."'";
		$stmt_wrk_sts = sqlsrv_query($conn, $sql_wrk_sts);
		if ($stmt_wrk_sts === false){
			if (($errors = sqlsrv_errors()) != null){
				foreach ($errors as $error)
				{
					file_put_contents($file_name, $now . "\r\n" . $sql_wrk_sts . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				}
			}

			sqlsrv_rollback($conn);
			sqlsrv_close($conn);
			$error_message = "Error selecting table (wko_ls8) - ".$now;
			returnError($error_message);
		}
		do{
			while ($row = sqlsrv_fetch_array($stmt_wrk_sts, SQLSRV_FETCH_ASSOC)){
				$counts = $row['counts'];
			}
		}
		while (sqlsrv_next_result($stmt_wrk_sts));

		if ($counts == "0"){
			sqlsrv_rollback($conn);
			$error_message = "Please enter time card before close";
			returnError($error_message);
		}else{
			if($wrk_sts_typ_cd === 'FORCE-CLOSE'){
				$save_clo = true;
			}else{
				$save_clo = check_MR($site_cd,$ROW_ID);
			}
		}
	
	}else{
	
		if($wrk_sts_typ_cd === 'FORCE-CLOSE'){
			$save_clo = true;
		}else{
			$save_clo = check_MR($site_cd,$ROW_ID);
		}
	}
	
	if($save_clo){
		 if ($wko_mst_status != $wko_mst_status_after){
			 
			 $sql = "UPDATE 	wko_mst  SET 
								wko_mst_status = ?, 
								audit_user = ?, 
								audit_date = GETDATE(),
								mbl_audit_user = ?, 
								mbl_audit_date = GETDATE()
								WHERE 	site_cd = ?
								AND 	wko_mst_wo_no = ?";

			$params_update_wko_mst = array(
				$wko_mst_status_after,
				$LOGINID,
				$LOGINID,
				$site_cd,
				$wko_mst_wo_no
			);
			$stmt_wko_mst = sqlsrv_query($conn, $sql, $params_update_wko_mst);

			if ($stmt_wko_mst === false)
			{
				if (($errors = sqlsrv_errors()) != null)
				{
					foreach ($errors as $error)
					{
						file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
					}
				}

				sqlsrv_rollback($conn);
				sqlsrv_close($conn);

				$error_message = "Error updating table (wko_mst) - ".$now;
				returnError($error_message);
			}

			sqlsrv_free_stmt($stmt_wko_mst);
			
			//REWORK
			if ($wrk_sts_typ_cd_before == 'OPEN' || $wrk_sts_typ_cd_before == 'REWORK' && $wrk_sts_typ_cd == 'CLOSE' || $wrk_sts_typ_cd == 'FORCE-CLOSE') {
				
				$start = ($wko_mst_org_date instanceof DateTime) ? $wko_mst_org_date : new DateTime($wko_mst_org_date);
				$end   = ($wko_det_clo_date instanceof DateTime) ? $wko_det_clo_date : new DateTime($wko_det_clo_date);
				$minutes = ($end->getTimestamp() - $start->getTimestamp()) / 60;
				
				
				$sql = "update wko_det 
					SET  
					wko_det_sc_date= ?,
					wko_det_clo_date= ?,
					wko_det_opn_clo=?,
					mbl_audit_user = ?, 
					mbl_audit_date = GETDATE(),
					wko_det_wo_open = ?						
					WHERE	wko_det.site_cd =?
					AND		wko_det.mst_RowID = ?";

					$params_update_wko_det = array(
						$current_date,
						$wko_det_clo_date,
						$minutes,
						$LOGINID,
						'N',
						$site_cd,
						$ROW_ID
					);
					$stmt_wko_det1 = sqlsrv_query($conn, $sql, $params_update_wko_det);

					if ($stmt_wko_det1 === false)
					{
						if (($errors = sqlsrv_errors()) != null)
						{
							foreach ($errors as $error)
							{
								file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
							}
						}

						sqlsrv_rollback($conn);
						sqlsrv_close($conn);

						$error_message = "Error updating table (wko_det) - ".$now;
						returnError($error_message);
					}

					sqlsrv_free_stmt($stmt_wko_det1);
				
			} else if ($wrk_sts_typ_cd_before == 'COMPLETE' || $wrk_sts_typ_cd_before == 'ACKNOWLEDGEMENT' && $wrk_sts_typ_cd == 'CLOSE' || $wrk_sts_typ_cd == 'FORCE-CLOSE') {
				
				
				$start = ($wko_det_cmpl_date instanceof DateTime) ? $wko_det_cmpl_date : new DateTime($wko_det_cmpl_date);
				$end   = ($wko_det_clo_date instanceof DateTime) ? $wko_det_clo_date : new DateTime($wko_det_clo_date);
				$minutes = ($end->getTimestamp() - $start->getTimestamp()) / 60;
				
				$start2 = ($wko_mst_org_date instanceof DateTime) ? $wko_mst_org_date : new DateTime($wko_mst_org_date);
				$end2   = ($wko_det_clo_date instanceof DateTime) ? $wko_det_clo_date : new DateTime($wko_det_clo_date);
				$minutes2 = ($end2->getTimestamp() - $start2->getTimestamp()) / 60;
				
				
				$sql = "update wko_det 
					SET  
					wko_det_sc_date= ?,
					wko_det_clo_date= ?,
					wko_det_opn_clo = ?,
					wko_det_cmp_clo=?,
					mbl_audit_user = ?, 
					mbl_audit_date = GETDATE(),
					wko_det_wo_open = ?					
					WHERE	wko_det.site_cd =?
					AND		wko_det.mst_RowID = ?";

				$params_update_wko_det = array(
					$current_date,
					$wko_det_clo_date,
					$minutes2,
					$minutes,
					$LOGINID,
					'N',
					$site_cd,
					$ROW_ID
				);
				$stmt_wko_det1 = sqlsrv_query($conn, $sql, $params_update_wko_det);

				if ($stmt_wko_det1 === false)
				{
					if (($errors = sqlsrv_errors()) != null)
					{
						foreach ($errors as $error)
						{
							file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
						}
					}

					sqlsrv_rollback($conn);
					sqlsrv_close($conn);

					$error_message = "Error updating table (wko_det) - ".$now;
					returnError($error_message);
				}

				sqlsrv_free_stmt($stmt_wko_det1);
			
				
			}
			
					
			
					
			$sql = "update wko_sts  
					SET wko_sts_end_date =GETDATE() , 
					wko_sts_duration =0 , 
					audit_user =?, 						
					audit_date =GETDATE(),
					mbl_audit_user =?, 						
					mbl_audit_date =GETDATE()						
					WHERE 
					wko_sts_end_date IS NULL 
					AND site_cd =? 
					AND wko_sts_wo_no =? 
					AND wko_sts_status =?";

			$params_update_wko_sts = array(
				$LOGINID,
				$LOGINID,
				$site_cd,
				$wko_mst_wo_no,
				$wko_mst_status
			);
			$stmt_wko_sts = sqlsrv_query($conn, $sql, $params_update_wko_sts);

			if ($stmt_wko_sts === false)
			{
				if (($errors = sqlsrv_errors()) != null)
				{
					foreach ($errors as $error)
					{
						file_put_contents($file_name, $now . "\r\n" . $sql . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
					}
				}

				sqlsrv_rollback($conn);
				sqlsrv_close($conn);

				$error_message = "Error updating table (wko_sts) - ".$now;
				returnError($error_message);
			}
			sqlsrv_free_stmt($stmt_wko_sts);
					
					
			$sql_insert_ast_mst = "INSERT wko_sts 
						(		site_cd ,					mst_RowID,						wko_sts_wo_no ,			wko_sts_status , 
								wko_sts_originator ,		wko_sts_start_date ,			wko_sts_end_date ,		wko_sts_duration , 
								audit_user , 				audit_date,						mbl_audit_user,			mbl_audit_date ) 
				VALUES	(		? ,							? ,								? ,						?,			
								? ,							getdate(),						NULL ,						NULL ,				 
								? ,							getdate(),						? ,							getdate())";

			$params_update_wko_sts = array(
				$site_cd,
				$ROW_ID,
				$wko_mst_wo_no,
				$wko_mst_status_after,
				$LOGINID,
				$LOGINID,
				$LOGINID
			);
			$stmt_wko_sts1 = sqlsrv_query($conn, $sql_insert_ast_mst, $params_update_wko_sts);

			if ($stmt_wko_sts1 === false)
			{
				if (($errors = sqlsrv_errors()) != null)
				{
					foreach ($errors as $error)
					{
						file_put_contents($file_name, $now . "\r\n" . $sql_insert_ast_mst . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
					}
				}

				sqlsrv_rollback($conn);
				sqlsrv_close($conn);

				$error_message = "Error inserting table (wko_sts) - ".$now;
				returnError($error_message);
			}

			sqlsrv_free_stmt($stmt_wko_sts1);
					
					
			$sql_email = " Select emp_mst_empl_id, emp_det_email_id 
					from emp_mst (NOLOCK), emp_det (NOLOCK)
					Where emp_mst.site_cd = emp_det.site_cd
					And emp_mst.RowID = emp_det.mst_RowID
					And emp_mst.emp_mst_empl_id = '" . $EmpID . "'";

			$stmt_email = sqlsrv_query($conn, $sql_email);

			if ($stmt_email === false)
			{
				if (($errors = sqlsrv_errors()) != null)
				{
					foreach ($errors as $error)
					{
						file_put_contents($file_name, $now . "\r\n" . $sql_email . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
					}
				}

				sqlsrv_rollback($conn);
				sqlsrv_close($conn);

				$error_message = "Error selecting table (emp_mst,emp_det) - ".$now;
				returnError($error_message);
			}

			do
			{
				while ($row = sqlsrv_fetch_array($stmt_email, SQLSRV_FETCH_ASSOC))
				{

					$emp_mst_empl_id = $row['emp_mst_empl_id'];
					$emp_det_email_id = $row['emp_det_email_id'];

				}
			}
			while (sqlsrv_next_result($stmt_email));
					
			 if (!Empty($emp_det_email_id))
			{

				//Insert Email
				$title = "Work Order Close.";
				$body = "The work order  (" . $wko_mst_wo_no . ") is Close.";
				$module = "Work Order";
				$message_completed = "Asset No: " . $Assestno . " / " . $Assest_description . "\r\n" .
				 "Work Area: " . $Work_Area . "\r\n" .
				 "Location: " . $Assest_location . "\r\n" .
				 "Level: " . $Assest_level . "\r\n\r\n" .
				 "Work Order No: " . $wko_mst_wo_no . " / " . $Work_Description . "\r\n" .
				 "WO Date: " . $Originationdate . "\r\n" .
				 "WO Due Date: " . $Duedate . "\r\n\r\n" .
				 "Close By: " . $EmpID . "\r\n" .
				 "Requested By: " . $Requested_by . "\r\n" .
				 "Contact No: " . $Contact_no;

				$insert_email = insert_email_log($site_cd, $EmpID, $emp_det_email_id, $title, $body, $message_completed, $LOGINID);

			}
					
			//Insert notification_log for Work Order Completed
			$title = "Work Order Close";
			$body = "The work order  ('" . $wko_mst_wo_no . "') is Close.";
			$module = "Work Order Close";
			$message_completed = "Asset No: " . $Assestno . " / " . $Assest_description . "\r\n" .
			 "Work Area: " . $Work_Area . "\r\n" .
			 "Location: " . $Assest_location . "\r\n" .
			 "Level: " . $Assest_level . "\r\n\r\n" .
			 "Work Order No: " . $wko_mst_wo_no . " / " . $Work_Description . "\r\n" .
			 "WO Date: " . $Originationdate . "\r\n" .
			 "WO Due Date: " . $Duedate . "\r\n\r\n" .
			 "Close By: " . $EmpID . "\r\n" .
			 "Requested By: " . $Requested_by . "\r\n" .
			 "Contact No: " . $Contact_no;

			$insert_ntf1 = insert_notification_log($site_cd, $EmpID, $Requested_by, $wko_mst_wo_no, $title, $body, $message_completed, $module, $dvc_id, $LOGINID);

			foreach ($insert_ntf1 as $x => $val)
			{

				$row = $x = $val["row"];
				$insert_ntf = $x = $val["insert_ntf"];

				//echo '<pre>'; print_r($row); echo '</pre>';
				//echo '<pre>'; print_r($insert_ntf); echo '</pre>';
				

				
			}
					
			//Get New Value Assignto Online Token
			$tkn_token_1 = get_online($Requested_by, $site_cd);

			//echo '<pre>'; print_r($tkn_token_1); echo '</pre>';
			//Notify token user
			if (sizeof($tkn_token_1) > 0)
			{

				foreach ($tkn_token_1 as $x => $val)
				{

					$tokenlist = $x = $val["token"];
					$RowID = $x = $val["RowID"];
					$tkn_site = $x = $val["tkn_site"];

					//echo '<pre>'; print_r($tkn_site); echo '</pre>';
					//echo '<pre>'; print_r($RowID); echo '</pre>';
					push_notification_android($tokenlist, 'The work order (' . $wko_mst_wo_no . ') is Close', "Work Order Close (" . $site_cd . ")", $site_cd, $tokenlist, $RowID, $row, $tkn_site, $LOGINID);

				}

			}
		
		}
		
	}
	
}
			
			
}else{
	
	$error_message = 'Work Order No: ' .$wko_mst_wo_no. ' is already  ' .$wrk_sts_desc. '';
	returnalert($error_message);
	
}


if ($stmt_wko_det)
{
	if ($Is_checked == 'true'){


		if ($wko_mst_status != $wko_mst_status_after)
			{

				if ($stmt_wko_mst && $stmt_wko_det1 && $stmt_wko_sts && $stmt_wko_sts1)
				{

					sqlsrv_commit($conn);
					sqlsrv_close($conn);
					returnData();
				}
				else
				{
					sqlsrv_rollback($conn);
					$error_message = "Transaction rolled back check Box true .<br />";
					returnError($error_message);
				}

			}
	}else{
        sqlsrv_commit($conn);
        sqlsrv_close($conn);
        check_returnData();
    }
}
else
{
    sqlsrv_rollback($conn);
    $error_message = "Transaction rolled back check Box false.<br />";
    returnError($error_message);
}


//Material Request checking 
function check_MR($site_cd,$ROW_ID){
	global $conn;
	
		// MR count 
		$sql_wko_ls2 = "Select 	counts = count(*) from 	wko_ls2 (NOLOCK) Where 	wko_ls2.site_cd = '".$site_cd ."' And 	wko_ls2.mst_RowID = '".$ROW_ID."'";
		$stmt_wko_ls2 = sqlsrv_query($conn, $sql_wko_ls2);

		if ($stmt_wko_ls2 === false){
			if (($errors = sqlsrv_errors()) != null){
				foreach ($errors as $error)
				{
					file_put_contents($file_name, $now . "\r\n" . $sql_wrk_sts . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				}
			}

			sqlsrv_rollback($conn);
			sqlsrv_close($conn);

			$error_message = "Error selecting table (wko_ls2) - ".$now;
			returnError($error_message);
		}

		do{
			while ($row = sqlsrv_fetch_array($stmt_wko_ls2, SQLSRV_FETCH_ASSOC)){

				$counts = $row['counts'];

			}
		}
		while (sqlsrv_next_result($stmt_wko_ls2));
		
		if ($counts > 0){
			
			$sql_mr = "Select 	mtr_mst_status, 
								wko_ls2_mr_no,
								wko_ls2_mr_lineno,
								wko_ls2_qty_needed	= COALESCE(wko_ls2.wko_ls2_qty_needed,0),
								mtr_ls1_rcv_qty 	= COALESCE(mtr_ls1.mtr_ls1_rcv_qty,0),
								mtr_ls1_clear_qty	= COALESCE(mtr_ls1.mtr_ls1_clear_qty,0),
								mtr_ls1_bo_qty,
								TotalAvailable		= (SELECT 	itm_mst_ttl_oh FROM itm_mst (NOLOCK) 
								WHERE 	itm_mst.site_cd = wko_ls2.site_cd 
								AND 	itm_mst.itm_mst_stockno = wko_ls2.wko_ls2_stockno ) 

								
						from 	wko_ls2 (NOLOCK) 
						LEFT OUTER JOIN mtr_mst
						ON				mtr_mst.site_cd = wko_ls2.site_cd
						AND			mtr_mst.mtr_mst_mtr_no =  wko_ls2.wko_ls2_mr_no


						LEFT OUTER JOIN mtr_ls1
						ON				mtr_mst.site_cd = mtr_ls1.site_cd
						AND			mtr_mst.RowID =  mtr_ls1.mst_RowID
						AND			mtr_ls1.mtr_ls1_mtr_lineno =  wko_ls2.wko_ls2_mr_lineno	
							
						LEFT OUTER JOIN emp_mst
						ON				mtr_mst.site_cd = emp_mst.site_cd
						AND			mtr_mst.mtr_mst_requester = emp_mst.emp_mst_empl_id
						Where 	wko_ls2.site_cd = '".$site_cd ."' 
						And 	wko_ls2.mst_RowID = '".$ROW_ID."'";
						
		$stmt_mr = sqlsrv_query($conn, $sql_mr);

		if ($stmt_mr === false){
			if (($errors = sqlsrv_errors()) != null){
				foreach ($errors as $error)
				{
					file_put_contents($file_name, $now . "\r\n" . $sql_mr . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				}
			}

			sqlsrv_rollback($conn);
			sqlsrv_close($conn);

			$error_message = "Error selecting table (wko_ls8) - ".$now;
			returnError($error_message);
		}

		do{
			while ($row = sqlsrv_fetch_array($stmt_mr, SQLSRV_FETCH_ASSOC)){

				$mtr_mst_status  = $row['mtr_mst_status'];
				$wko_ls2_mr_no  = $row['wko_ls2_mr_no'];
				$wko_ls2_mr_lineno  = $row['wko_ls2_mr_lineno'];
				$wko_ls2_qty_needed  = $row['wko_ls2_qty_needed'];
				$mtr_ls1_rcv_qty  = $row['mtr_ls1_rcv_qty'];
				$mtr_ls1_clear_qty  = $row['mtr_ls1_clear_qty'];
				$mtr_ls1_bo_qty  = $row['mtr_ls1_bo_qty'];
				$TotalAvailable  = $row['TotalAvailable'];
				
				if( $mtr_mst_status === 'W'){
					
					if($mtr_ls1_bo_qty > 0){
						
						sqlsrv_rollback($conn);
						sqlsrv_close($conn);
						$error_message = "The work order cannot be close because there is an outstanding unissued materal request: "."(".$wko_ls2_mr_no.")"."";
						returnError($error_message);
						
					}else{
						return check_SP($site_cd,$ROW_ID);
					}
					
					
					
				}else if ($mtr_mst_status === 'A'){
					//Ready to issued else Shortage
					if($wko_ls2_qty_needed != ($mtr_ls1_rcv_qty + $mtr_ls1_clear_qty) && ($TotalAvailable >= ($wko_ls2_qty_needed - $mtr_ls1_rcv_qty + $mtr_ls1_clear_qty ))){
						sqlsrv_rollback($conn);
						sqlsrv_close($conn);
						$error_message = "The work order cannot be close because there is an outstanding unissued materal request: "."(".$wko_ls2_mr_no.")"."";
						returnError($error_message);
					}else if($wko_ls2_qty_needed != ($mtr_ls1_rcv_qty + $mtr_ls1_clear_qty) && ($TotalAvailable < ($wko_ls2_qty_needed - $mtr_ls1_rcv_qty + $mtr_ls1_clear_qty ))){
						sqlsrv_rollback($conn);
						sqlsrv_close($conn);
						$error_message = "The work order cannot be close because there is an outstanding unissued materal request: "."(".$wko_ls2_mr_no.")"."";
						returnError($error_message);
					}else{
						return check_SP($site_cd,$ROW_ID);
					}
					
				}else{
					return check_SP($site_cd,$ROW_ID);
				}

			}
		}
		while (sqlsrv_next_result($stmt_mr));
	}else{
		
		return check_SP($site_cd,$ROW_ID);
		
	}
    
}


//Special Oredr Checking 
function check_SP($site_cd,$ROW_ID){
	global $conn;
	// SP Card 
	$sql_wko_ls3 = "Select 	counts = count(*) from 	wko_ls3 (NOLOCK) Where 	wko_ls3.site_cd = '".$site_cd ."' And 	wko_ls3.mst_RowID = '".$ROW_ID."'";
	$stmt_wko_ls3 = sqlsrv_query($conn, $sql_wko_ls3);

	if ($stmt_wko_ls3 === false){
		if (($errors = sqlsrv_errors()) != null){
			foreach ($errors as $error)
			{
				file_put_contents($file_name, $now . "\r\n" . $stmt_wko_ls3 . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
			}
		}

		sqlsrv_rollback($conn);
		sqlsrv_close($conn);

		$error_message = "Error selecting table (wko_ls8) - ".$now;
		returnError($error_message);
	}

	do{
		while ($row = sqlsrv_fetch_array($stmt_wko_ls3, SQLSRV_FETCH_ASSOC)){

			$counts = $row['counts'];

		}
	}
	while (sqlsrv_next_result($stmt_wko_ls3));
	
	if ($counts > 0){
		$sql_sp = "select pur_mst_purq_approve,wko_ls3_pr_no,wko_ls3_qty_needed,puo_ls1_rcv_qty from 	wko_ls3 (NOLOCK) 
					LEFT OUTER JOIN pur_mst
					ON		pur_mst.site_cd = wko_ls3.site_cd
					AND		pur_mst.pur_mst_porqnnum =  wko_ls3.wko_ls3_pr_no

					LEFT OUTER JOIN pur_ls1
					ON		pur_mst.site_cd = pur_ls1.site_cd
					AND		pur_mst.RowID =  pur_ls1.mst_RowID
					AND		pur_ls1.pur_ls1_pr_lineno =  wko_ls3.wko_ls3_pr_lineno

					LEFT OUTER JOIN puo_ls1
					ON		wko_ls3.site_cd = puo_ls1.site_cd		
					AND		wko_ls3.wko_ls3_pr_no =  puo_ls1.puo_ls1_pr_no
					AND		wko_ls3.wko_ls3_pr_lineno =  puo_ls1.puo_ls1_po_lineno
					Where 	wko_ls3.site_cd = '".$site_cd ."' 
					And 	wko_ls3.mst_RowID = '".$ROW_ID."'";
					
		$stmt_sp = sqlsrv_query($conn, $sql_sp);

		if ($stmt_sp === false){
			if (($errors = sqlsrv_errors()) != null){
				foreach ($errors as $error)
				{
					file_put_contents($file_name, $now . "\r\n" . $sql_sp . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				}
			}

			sqlsrv_rollback($conn);
			sqlsrv_close($conn);

			$error_message = "Error selecting table (wko_ls8) - ".$now;
			returnError($error_message);
		}

		do{
			while ($row = sqlsrv_fetch_array($stmt_sp, SQLSRV_FETCH_ASSOC)){

				$pur_mst_purq_approve  = $row['pur_mst_purq_approve'];
				$wko_ls3_pr_no  = $row['wko_ls3_pr_no'];
				$wko_ls3_qty_needed  = $row['wko_ls3_qty_needed'];
				$puo_ls1_rcv_qty  = $row['puo_ls1_rcv_qty'];
				
				
				if( $pur_mst_purq_approve === 'W'){
					
					sqlsrv_rollback($conn);
					sqlsrv_close($conn);
					$error_message = "The work order cannot be close because there is an outstanding unreceipted special order "."(".$wko_ls3_pr_no.")"."";
					returnError($error_message);
					
				}else if ($pur_mst_purq_approve === 'N'){
					sqlsrv_rollback($conn);
					sqlsrv_close($conn);
					$error_message = "The work order cannot be close because there is an outstanding unreceipted special order "."(".$wko_ls3_pr_no.")"."";
					returnError($error_message);
					
				}else if ($pur_mst_purq_approve === 'A'){
					//Ready to issued else Shortage
					if($puo_ls1_rcv_qty === 0){
						sqlsrv_rollback($conn);
						sqlsrv_close($conn);
						$error_message = "The work order cannot be close because there is an outstanding unreceipted special order "."(".$wko_ls3_pr_no.")"."";
						returnError($error_message);
					}else if($puo_ls1_rcv_qty < $wko_ls3_qty_needed){
						sqlsrv_rollback($conn);
						sqlsrv_close($conn);
						$error_message = "The work order cannot be close because there is an outstanding unreceipted special order "."(".$wko_ls3_pr_no.")"."";
						returnError($error_message);
					}else{
						return check_PR($site_cd,$ROW_ID);
					}
					
				}else{
					return check_PR($site_cd,$ROW_ID);
				}

			}
		}
		while (sqlsrv_next_result($stmt_sp));
	}else{
		
		return check_PR($site_cd,$ROW_ID);
	}
	
}


//Contract Service Checking
function check_PR($site_cd,$ROW_ID){
	global $conn;
	// PR Card 
	$sql_wko_ls4 = "Select 	counts = count(*) from 	wko_ls4 (NOLOCK) Where 	wko_ls4.site_cd = '".$site_cd ."' And 	wko_ls4.mst_RowID = '".$ROW_ID."'";
	$stmt_wko_ls4 = sqlsrv_query($conn, $sql_wko_ls4);

	if ($stmt_wko_ls4 === false){
		if (($errors = sqlsrv_errors()) != null){
			foreach ($errors as $error)
			{
				file_put_contents($file_name, $now . "\r\n" . $stmt_wko_ls4 . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
			}
		}

		sqlsrv_rollback($conn);
		sqlsrv_close($conn);

		$error_message = "Error selecting table (wko_ls4) - ".$now;
		returnError($error_message);
	}

	do{
		while ($row = sqlsrv_fetch_array($stmt_wko_ls4, SQLSRV_FETCH_ASSOC)){

			$counts = $row['counts'];

		}
	}
	while (sqlsrv_next_result($stmt_wko_ls4));
	
	if ($counts > 0){
		$sql_pr = "select pur_mst_purq_approve,wko_ls4_pr_no, wko_ls4_qty_needed,puo_ls1_rcv_qty from 	wko_ls4 (NOLOCK) 
					LEFT OUTER JOIN pur_mst
					ON			pur_mst.site_cd = wko_ls4.site_cd
					AND			pur_mst.pur_mst_porqnnum =  wko_ls4.wko_ls4_pr_no


					LEFT OUTER JOIN pur_ls1
					ON			pur_mst.site_cd = pur_ls1.site_cd		
					AND			pur_mst.RowID =  pur_ls1.mst_RowID
					AND			pur_ls1.pur_ls1_pr_lineno =  wko_ls4.wko_ls4_pr_lineno
					
					LEFT OUTER JOIN puo_ls1
					ON			wko_ls4.site_cd = puo_ls1.site_cd		
					AND			wko_ls4.wko_ls4_pr_no =  puo_ls1.puo_ls1_pr_no
					AND			wko_ls4.wko_ls4_pr_lineno =  puo_ls1.puo_ls1_po_lineno
					Where 	wko_ls4.site_cd = '".$site_cd ."' 
					And 	wko_ls4.mst_RowID = '".$ROW_ID."'";
					
		$stmt_pr = sqlsrv_query($conn, $sql_pr);

		if ($stmt_pr === false){
			if (($errors = sqlsrv_errors()) != null){
				foreach ($errors as $error)
				{
					file_put_contents($file_name, $now . "\r\n" . $sql_pr . "\r\n" . "SQLSTATE: " . $error['SQLSTATE'] . "\r\n" . "code: " . $error['code'] . "\r\n" . "message: " . $error['message'] . "\r\n\r\n", FILE_APPEND);
				}
			}

			sqlsrv_rollback($conn);
			sqlsrv_close($conn);

			$error_message = "Error selecting table (wko_ls4) - ".$now;
			returnError($error_message);
		}

		do{
			while ($row = sqlsrv_fetch_array($stmt_pr, SQLSRV_FETCH_ASSOC)){

				$pur_mst_purq_approve  = $row['pur_mst_purq_approve'];
				$wko_ls4_pr_no  = $row['wko_ls4_pr_no'];
				$wko_ls4_qty_needed  = $row['wko_ls4_qty_needed'];
				$puo_ls1_rcv_qty  = $row['puo_ls1_rcv_qty'];
				
				
				if( $pur_mst_purq_approve === 'W'){
					
					sqlsrv_rollback($conn);
					sqlsrv_close($conn);
					$error_message = "The work order cannot be close because there is an outstanding unreceipted contract order "."(".$wko_ls4_pr_no.")"."";
					returnError($error_message);
					
				}else if ($pur_mst_purq_approve === 'N'){
					sqlsrv_rollback($conn);
					sqlsrv_close($conn);
					$error_message = "The work order cannot be close because there is an outstanding unreceipted contract order "."(".$wko_ls4_pr_no.")"."";
					returnError($error_message);
					
				}else if ($pur_mst_purq_approve === 'A'){
					//Ready to issued else Shortage
					if($puo_ls1_rcv_qty === 0){
						sqlsrv_rollback($conn);
						sqlsrv_close($conn);
						$error_message = "The work order cannot be close because there is an outstanding unreceipted contract order "."(".$wko_ls4_pr_no.")"."";
						returnError($error_message);
					}else if($puo_ls1_rcv_qty < $wko_ls4_qty_needed){
						sqlsrv_rollback($conn);
						sqlsrv_close($conn);
						$error_message = "The work order cannot be close because there is an outstanding unreceipted contract order "."(".$wko_ls4_pr_no.")"."";
						returnError($error_message);
					}else{
						return true;
					}
					
				}else{
					return true;
				}

			}
		}
		while (sqlsrv_next_result($stmt_pr));
	}else{
		
		return true;
	}
}


function check_returnData()
{
    $returnData = array('status' => 'SUCCESS', 
	'message' => 'Work order has been updated successfully');
    echo json_encode($returnData);
}

function returnData()
{
    $returnData = array(
        'status' => 'SUCCESS',
        'message' => 'Work order is close successfully'
    );

    echo json_encode($returnData);
}

function returnError($error_message)
{

    $returnData = array(
        'status' => 'ERROR',
        'message' => $error_message
    );
    echo json_encode($returnData);
    exit();
}

function returnalert($error_message){	
	
	$returnData = array(
	'status' => 'alert',
	'message' => $error_message);	
	echo json_encode($returnData);
	exit();
}


?>
