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

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

$json = file_get_contents('php://input');
$data = json_decode($json, true);

$site_cd 				= $data['site_cd'];
$EmpID 					= $data['EmpID'];
$EmpName 				= $data['EmpName'];
$RowID 					= $data['RowID'];
$dvc_id 				= $data['dvc_id'];
$wrk_status 			= $data['wrk_status'];
$wko_det_assign_to 		= $data['wko_det_assign_to'];
$wko_mst_moc_required 	= $data['wko_mst_moc_required'];
$LOGINID 				= $data['LOGINID'];

$li_assign_cnt = 0;
$ls_assign_emp = null;
$ls_udf7 = null;
$ls_udf8 = null;
$ls_udf9 = null;
$ls_udf10 = null;

if (!empty($wko_det_assign_to)){
	
	 // Convert wko_det_assign_to string into an array
    $assignedEmployees = explode(",", $wko_det_assign_to);
    
    foreach ($assignedEmployees as $li_assign_cnt => $emp_id) {
        $emp_id = trim($emp_id);
        switch ($li_assign_cnt + 1) { // Adjust index to match case numbers
            case 1:
                $ls_assign_emp = $emp_id;
                break;
            case 2:
                $ls_udf7 = $emp_id;
                break;
            case 3:
                $ls_udf8 = $emp_id;
                break;
            case 4:
                $ls_udf9 = $emp_id;
                break;
            case 5:
                $ls_udf10 = $emp_id;
                break;
        }
    }
	
}


$sql = "SELECT 	wkr_mst.wkr_mst_wr_no,   
				wkr_mst.wkr_mst_assetno,  
				wkr_mst.wkr_mst_chg_costcenter, 			
				wkr_mst.wkr_mst_assetlocn,   
				work_area  						= COALESCE(wkr_mst_work_area+' : '+mst_war_desc,''),	
				assetlocn						= COALESCE(wkr_mst_assetlocn+' : '+ast_loc_desc,''),
				wkr_mst.wkr_mst_work_area,  
				wkr_mst.wkr_mst_work_type,   
				wkr_mst.wkr_mst_work_class,   
				wkr_mst.wkr_mst_work_group, 
				wkr_mst.wkr_mst_location, 
				wkr_mst.wkr_mst_taken_by,   
				wkr_mst.wkr_mst_originator,   
				wkr_mst.wkr_mst_phone,  
				wkr_mst.wkr_mst_orig_priority,    
				wkr_mst.wkr_mst_org_date, 
				wkr_mst.wkr_mst_due_date,   
				wkr_mst.wkr_mst_fault_code,   
				wkr_mst.wkr_mst_wr_descs,
				wkr_mst.wkr_mst_wr_status,
				wkr_mst.wkr_mst_temp_asset,
				wkr_mst.wkr_mst_projectid,
				wkr_det.wkr_det_varchar1,
				wkr_det.wkr_det_varchar2,
				wkr_det.wkr_det_varchar3,
				wkr_det.wkr_det_varchar4,
				wkr_det.wkr_det_varchar5,
				wkr_det.wkr_det_note1,
				wkr_det.wkr_det_numeric1,
				wkr_det.wkr_det_numeric2,
				wkr_det.wkr_det_numeric3,
				wkr_det.wkr_det_numeric4,
				wkr_det.wkr_det_numeric5,
				wkr_det.wkr_det_datetime1,
				wkr_det.wkr_det_datetime2,
				wkr_det.wkr_det_datetime3,
				wkr_det.wkr_det_datetime4,
				wkr_det.wkr_det_datetime5					
		
		FROM 	wkr_mst (NOLOCK)
		INNER 
		JOIN 	wkr_det with (NOLOCK)
		ON 		wkr_mst.site_cd = wkr_det.site_cd 
		AND		wkr_mst.RowID = wkr_det.mst_RowID
		
		LEFT 
		OUTER 
		JOIN		mst_war (NOLOCK)
		ON			wkr_mst.site_cd = mst_war.site_cd
		AND			wkr_mst.wkr_mst_work_area = mst_war.mst_war_work_area
		
		LEFT 
		OUTER 
		JOIN		ast_loc (NOLOCK)
		ON			wkr_mst.site_cd = ast_loc.site_cd
		AND			wkr_mst.wkr_mst_assetlocn = ast_loc.ast_loc_ast_loc
		
		LEFT 
		OUTER 
		JOIN		ast_lvl (NOLOCK)
		ON			wkr_mst.site_cd = ast_lvl.site_cd
		AND			wkr_mst.wkr_mst_location = ast_lvl.ast_lvl_ast_lvl
		
		WHERE	wkr_mst.site_cd = '".$site_cd."' 
		AND 	wkr_mst.RowID = '".$RowID."'";			
			
		$stmt = sqlsrv_query( $conn, $sql);			
		if( !$stmt ) {
			$error_message = "Error select table (cnt_mst)";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		}
		do {
			while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
			
			$wkr_mst_wr_no 			= $row['wkr_mst_wr_no'];				
			$wkr_mst_assetno 		= $row['wkr_mst_assetno'];
			$wkr_mst_chg_costcenter = $row['wkr_mst_chg_costcenter'];
			$wkr_mst_assetlocn 		= $row['wkr_mst_assetlocn'];
			$wkr_mst_work_area 		= $row['wkr_mst_work_area'];
			$work_area 				= $row['work_area'];
			$assetlocn 				= $row['assetlocn'];
			$wkr_mst_work_type 		= $row['wkr_mst_work_type'];
			$wkr_mst_work_class 	= $row['wkr_mst_work_class'];
			$wkr_mst_work_group 	= $row['wkr_mst_work_group'];
			$wkr_mst_location 		= $row['wkr_mst_location'];
			$wkr_mst_taken_by 		= $row['wkr_mst_taken_by'];
			$wkr_mst_originator 	= $row['wkr_mst_originator'];
			$wkr_mst_phone 			= $row['wkr_mst_phone'];
			$wkr_mst_orig_priority 	= $row['wkr_mst_orig_priority'];
			$wkr_mst_org_date 		= $row['wkr_mst_org_date'];
			$wkr_mst_due_date 		= $row['wkr_mst_due_date'];
			$wkr_mst_fault_code 	= $row['wkr_mst_fault_code'];
			$wkr_mst_wr_descs 		= $row['wkr_mst_wr_descs'];				
			$wkr_mst_wr_status 		= $row['wkr_mst_wr_status'];				
			$wkr_mst_temp_asset 	= $row['wkr_mst_temp_asset'];
			$wkr_mst_projectid 		= $row['wkr_mst_projectid'];
			$wkr_det_varchar1 		= $row['wkr_det_varchar1'];
			$wkr_det_varchar2 		= $row['wkr_det_varchar2'];
			$wkr_det_varchar3 		= $row['wkr_det_varchar3'];
			$wkr_det_varchar4 		= $row['wkr_det_varchar4'];
			$wkr_det_varchar5 		= $row['wkr_det_varchar5'];
			$wkr_det_note1 			= $row['wkr_det_note1'];
			$wkr_det_numeric1 		= $row['wkr_det_numeric1'];
			$wkr_det_numeric2 		= $row['wkr_det_numeric2'];
			$wkr_det_numeric3 		= $row['wkr_det_numeric3'];
			$wkr_det_numeric4 		= $row['wkr_det_numeric4'];
			$wkr_det_numeric5 		= $row['wkr_det_numeric5'];				
			$wkr_det_datetime1 		= $row['wkr_det_datetime1'];
			$wkr_det_datetime2 		= $row['wkr_det_datetime2'];
			$wkr_det_datetime3 		= $row['wkr_det_datetime3'];
			$wkr_det_datetime4 		= $row['wkr_det_datetime4'];
			$wkr_det_datetime5 		= $row['wkr_det_datetime5'];					
					 
		}
	} while ( sqlsrv_next_result($stmt));
	sqlsrv_free_stmt( $stmt);


if($wkr_mst_wr_status == 'W'){
	
	if (preg_match('/^([A-Za-z]+)/', $wkr_mst_wr_no, $matches)) {
		$prefix = $matches[1];
		
		$wko_mst_wo_no;
		
		if ($prefix == 'MJR' || $prefix == 'MPR' || $prefix == 'PJR') {
			
			
			$sql = "SELECT	ll_loc_cnt = COUNT(*) 
					FROM	ast_loc (NOLOCK) 
					WHERE	ast_loc.site_cd = '".$site_cd."' 
					AND 	ast_loc.ast_loc_ast_loc = '".$wkr_mst_assetlocn."' 
					AND		ast_loc.ast_loc_wo_prefix IS NOT NULL  
					AND		ast_loc.ast_loc_wo_option = '1'";			
			
			$stmt_ll_loc_cnt = sqlsrv_query( $conn, $sql);			
			if( !$stmt_ll_loc_cnt ) {
				$error_message = "Error select table (ll_loc_cnt)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				while ($row = sqlsrv_fetch_array($stmt_ll_loc_cnt, SQLSRV_FETCH_ASSOC)) {	
				
					$ll_loc_cnt = $row['ll_loc_cnt'];	
				}
			} while ( sqlsrv_next_result($stmt_ll_loc_cnt));
			sqlsrv_free_stmt($stmt_ll_loc_cnt);


			$sql = "SELECT	ll_cnt = COUNT(*)
					FROM	wrk_typ (NOLOCK)
					WHERE	site_cd = '".$site_cd."'
					AND		wrk_typ_typ_cd = '".$wkr_mst_work_type."'
					AND		wrk_typ_wr_prefix IS NOT NULL		
					AND		wrk_typ_wr_option = '1'";			
				
			$stmt_ll_cnt = sqlsrv_query( $conn, $sql);			
			if( !$stmt_ll_cnt ) {
				$error_message = "Error select table (ll_cnt)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				while ($row = sqlsrv_fetch_array($stmt_ll_cnt, SQLSRV_FETCH_ASSOC)) {	
				
					$ll_cnt = $row['ll_cnt'];	
				}
			} while ( sqlsrv_next_result($stmt_ll_cnt));	
			sqlsrv_free_stmt($stmt_ll_cnt);		
			
			if($ll_loc_cnt>0){
				
				$sql = "Select 	ast_loc_wo_prefix + SUBSTRING(CONVERT(VARCHAR(7), ast_loc_wo_counter + 1000000), 4, 4)  ls_doc_no
						From 	ast_loc WITH (UPDLOCK)
						Where 	site_cd ='".$site_cd."'
						And 	ast_loc_ast_loc =  '".$wkr_mst_assetlocn."'";			
				
				$stmt_ast_loc = sqlsrv_query( $conn, $sql);			
				if( !$stmt_ast_loc ) {
					$error_message = "Error select table (ast_loc)";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
				do {
					while ($row = sqlsrv_fetch_array($stmt_ast_loc, SQLSRV_FETCH_ASSOC)) {	
					
						$ls_doc_no = $row['ls_doc_no'];				
						$wko_mst_wo_no  = $ls_doc_no;
							 
					}
				} while ( sqlsrv_next_result($stmt_ast_loc));
				sqlsrv_free_stmt( $stmt_ast_loc);


				$sql = "Select Count(*) ll_dup_cnt	
						From 	wko_mst (NOLOCK)
						Where 	site_cd = '".$site_cd."'
						And 	wko_mst_wo_no = '".$ls_doc_no."'";			
						
				$stmt_wko_mst = sqlsrv_query( $conn, $sql);	
				if( !$stmt_wko_mst ) {
					$error_message = "Error select table (wko_mst)";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
				
				do {
					while ($row = sqlsrv_fetch_array($stmt_wko_mst, SQLSRV_FETCH_ASSOC)) {	 
						$ll_dup_cnt = $row['ll_dup_cnt'];	 
					}
				} while ( sqlsrv_next_result($stmt_wko_mst));
				sqlsrv_free_stmt( $stmt_wko_mst);

				if($ll_dup_cnt == 1){	
					$error_message = "work order number is already exists";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				} 
						
				
				
			}else if ($ll_cnt >0){
				
				$sql = "Select 	wrk_typ_prefix + SUBSTRING(CONVERT(VARCHAR(7), wrk_typ_counter + 1000000), 4, 4)  ls_doc_no
						From 	wrk_typ WITH (UPDLOCK)
						Where 	site_cd ='".$site_cd."'
						And 	wrk_typ_typ_cd =  '".$wkr_mst_work_type."'";			
			
				$stmt_wrk_typ = sqlsrv_query( $conn, $sql);			
				if( !$stmt_wrk_typ ) {
					$error_message = "Error select table (wrk_typ)";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
				do {
					while ($row = sqlsrv_fetch_array($stmt_wrk_typ, SQLSRV_FETCH_ASSOC)) {	
					
					$ls_doc_no = $row['ls_doc_no'];				
					$wko_mst_wo_no  = $ls_doc_no;
							 
					}
				} while ( sqlsrv_next_result($stmt_wrk_typ));
				sqlsrv_free_stmt($stmt_wrk_typ);
				
				
				$sql = "Select Count(*) ll_dup_cnt	
						From 	wko_mst (NOLOCK)
						Where 	site_cd = '".$site_cd."'
						And 	wko_mst_wo_no = '".$ls_doc_no."'";			
					
				$stmt_wko_mst = sqlsrv_query( $conn, $sql);	
				if( !$stmt_wko_mst ) {
					$error_message = "Error select table (wko_mst)";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
				
				do {
					while ($row = sqlsrv_fetch_array($stmt_wko_mst, SQLSRV_FETCH_ASSOC)) {	 
					$ll_dup_cnt = $row['ll_dup_cnt'];		
					
						 
					}
				} while ( sqlsrv_next_result($stmt_wko_mst));
				sqlsrv_free_stmt( $stmt_wko_mst);
			
				if($ll_dup_cnt == 1){	
					$error_message = "Asset number is already exists";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				} 
				
				
				
			}else{
				
				
				$sql = "Select cnt_mst_prefix + SUBSTRING(CONVERT(VARCHAR(7), cnt_mst_counter + 1000000), 2, 6)  as ls_doc_no
				From 	cnt_mst WITH (UPDLOCK)
				Where 	site_cd ='".$site_cd."'
				And 	cnt_mst_module_cd =  'WKO'";			
			
				$stmt = sqlsrv_query( $conn, $sql);			
				if( !$stmt ) {
					$error_message = "Error select table (cnt_mst)";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
				do {
					while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
					
					$ls_doc_no = $row['ls_doc_no'];				
					$wko_mst_wo_no  = $ls_doc_no;
					//echo $ls_doc_no ;
							 
					}
				} while ( sqlsrv_next_result($stmt));
				sqlsrv_free_stmt( $stmt);


				$sql = "Select Count(*) ll_dup_cnt	From wko_mst (NOLOCK)
						Where site_cd = '".$site_cd."'
						And wko_mst_wo_no = '".$ls_doc_no."'";			
					
				$stmt = sqlsrv_query( $conn, $sql);	
				if( !$stmt ) {
					$error_message = "Error select table (wko_mst)";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
				
				do {
					while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
					
						$ll_dup_cnt = $row['ll_dup_cnt'];		

					}
				} while ( sqlsrv_next_result($stmt));
				sqlsrv_free_stmt( $stmt);
				
				if($ll_dup_cnt == 1){	
					$error_message = "Asset number is already exists";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				} 
				
				
			}

		}else {
			
			$wko_mst_wo_no = $wkr_mst_wr_no;
			
		}
		
		if (empty($wko_mst_wo_no)) {
			$error_message = "Work Order No Failed to generate the Work Order No.Please contact the system administrator.";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		}else{
		
			$sql = "SELECT	ast_mst_asset_status,
							ast_mst_asset_grpcode,
							ast_mst_asset_code,
							ast_mst_perm_id,	
							ast_mst_asset_shortdesc,
							ast_det_cus_code,
							ast_mst.RowID
					FROM 	ast_mst (NOLOCK),ast_det (NOLOCK)
					WHERE	ast_mst.site_cd = '".$site_cd."'
					AND		ast_mst.site_cd = ast_det.site_cd
					AND		ast_mst.rowid = ast_det.mst_rowid
					AND 	ast_mst.ast_mst_asset_no = '".$wkr_mst_assetno."'";			
				
			$stmt = sqlsrv_query( $conn, $sql);			
			if( !$stmt ) {
				$error_message = "Error select table (cnt_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
				
					$ast_mst_asset_status = $row['ast_mst_asset_status'];				
					$ast_mst_asset_grpcode = $row['ast_mst_asset_grpcode'];
					$ast_mst_asset_code = $row['ast_mst_asset_code'];
					$ast_mst_perm_id = $row['ast_mst_perm_id'];
					$ast_mst_asset_shortdesc = $row['ast_mst_asset_shortdesc'];
					$ast_det_cus_code=$row['ast_det_cus_code'];
					$ast_mst_RowID = $row['RowID'];							
						 
				}
			} while ( sqlsrv_next_result($stmt));
			sqlsrv_free_stmt( $stmt);
			
			
			$sql = "SELECT 	wrk_pri_due_date_count = wrk_pri_due_date_count
					FROM	wrk_pri (NOLOCK)
					WHERE	site_cd = '".$site_cd."'
					AND 	wrk_pri_pri_cd = '".$wkr_mst_orig_priority."'";			
					
			$stmt = sqlsrv_query( $conn, $sql);			
			if( !$stmt ) {
				$error_message = "Error select table (wrk_pri)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
				
					$wrk_pri_due_date_count = $row['wrk_pri_due_date_count'];			 
				}
			} while ( sqlsrv_next_result($stmt));
			sqlsrv_free_stmt( $stmt);
			
			if(empty($wrk_pri_due_date_count)){
				$wrk_pri_due_date_count =0;
			}
				
			$sql = "SELECT TOP 1 
			wkr_mst_org_date = CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()), 0) AS SMALLDATETIME),
			wkr_mst_due_date = CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DATEADD(MI, ".$wrk_pri_due_date_count.", GETDATE())), 0) AS SMALLDATETIME)
			FROM cf_user (NOLOCK)";			
				
			$stmt = sqlsrv_query( $conn, $sql);			
			if( !$stmt ) {
				$error_message = "Error select table (cf_user)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
				
					$wko_mst_org_date = $row['wkr_mst_org_date']->format('Y-m-d H:i:s');
					$wko_mst_due_date = $row['wkr_mst_due_date']->format('Y-m-d H:i:s');		
				}
			} while ( sqlsrv_next_result($stmt));
			sqlsrv_free_stmt( $stmt);
			
			if ($prefix == 'PR'){
				$wko_mst_type = 'P';
				
			}else{
				$wko_mst_type = 'C';
			}
			
			//Insert Work order
			$sql_insert_wko_mst = "INSERT INTO wko_mst  (	site_cd,						wko_mst_wo_no,					wko_mst_originator,			wko_mst_phone,				
															wko_mst_asset_level,			wko_mst_assetno,				wko_mst_flt_code,			wko_mst_status,				
															wko_mst_due_date,				wko_mst_descs,					wko_mst_project_id,			wko_mst_org_date,			
															wko_mst_chg_costcenter,			wko_mst_work_area,				wko_mst_asset_location,		wko_mst_asset_group_code,	
															wko_mst_asset_status,			wko_mst_orig_priority,			wko_mst_plan_priority,		wko_mst_type,
															wko_mst_create_by,				wko_mst_create_date,			wko_mst_ast_cod,			audit_user,
															audit_date,						mbl_audit_user,					mbl_audit_date,				wko_mst_moc_required ) 
															
												VALUES 	(	?,								?,								?,							?,
															?,								?,								?,							?,
															?,								?,								?,							?,	
															?,								?,								?,							?,	
															?,								?,								?,							?,	
															?,								[dbo].[utc_getedate]('".$site_cd."'),						?,							?,	
															[dbo].[utc_getedate]('".$site_cd."'),						?,								[dbo].[utc_getedate]('".$site_cd."'), ?)";
																
						
			$params_wko_mst = array($site_cd,$wko_mst_wo_no,$wkr_mst_originator,$wkr_mst_phone,$wkr_mst_location,$wkr_mst_assetno,$wkr_mst_fault_code,$wrk_status,$wko_mst_due_date,$wkr_mst_wr_descs,$wkr_mst_projectid,$wko_mst_org_date,$wkr_mst_chg_costcenter,$wkr_mst_work_area,$wkr_mst_assetlocn,$ast_mst_asset_grpcode,$ast_mst_asset_status,$wkr_mst_orig_priority,$wkr_mst_orig_priority,$wko_mst_type,$LOGINID,$ast_mst_asset_code,$LOGINID,$LOGINID,$wko_mst_moc_required);	

			$stmt_wko_mst = sqlsrv_query( $conn, $sql_insert_wko_mst,$params_wko_mst);

			if( !$stmt_wko_mst ) {
				$error_message = "Error insert table (INSERT Table wko_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}	
			sqlsrv_free_stmt( $stmt_wko_mst);
			
			//select work order RowID
			$sql = "SELECT Rowid From wko_mst (NOLOCK) WHERE site_cd = '".$site_cd."' AND wko_mst_wo_no = '".$wko_mst_wo_no."'";
			$stmt = sqlsrv_query( $conn, $sql);	
			if( !$stmt ) {
				$error_message = "SELECT   table Error (wko_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				 
				 $Mst_ROW_ID = $row['Rowid'];		
					 
			   }
			} while ( sqlsrv_next_result($stmt));
			sqlsrv_free_stmt( $stmt);
			
			$sql = " SELECT 	dft_mst_lab_act,
								dft_mst_mat_act,
								dft_mst_con_act
					FROM		dft_mst (NOLOCK)
					WHERE	site_cd = '".$site_cd."'";
					
			$stmt = sqlsrv_query( $conn, $sql);	
					
			if( !$stmt ) {
				$error_message = "SELECT  table Error (dft_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				 
					$dft_mst_lab_act = $row['dft_mst_lab_act'];
					$dft_mst_mat_act = $row['dft_mst_mat_act'];
					$dft_mst_con_act = $row['dft_mst_con_act']; 
			    }
			} while ( sqlsrv_next_result($stmt));
			sqlsrv_free_stmt( $stmt);
			
			$sql = "select emp_det_wo_budget_approver, emp_det_wo_approval_limit from emp_det (NOLOCK) ,emp_mst (NOLOCK) 
					where emp_det.site_cd = emp_mst.site_cd 
					and emp_det.mst_RowID = emp_mst.RowID 
					AND		emp_mst.site_cd = '".$site_cd."'
					and emp_mst_empl_id ='".$EmpID."'";
					
			$stmt = sqlsrv_query( $conn, $sql);	
					
			if( !$stmt ) {
				$error_message = "SELECT  table Error (emp_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				 
				  $emp_det_wo_budget_approver = $row['emp_det_wo_budget_approver'];
				  $emp_det_wo_approval_limit = $row['emp_det_wo_approval_limit'];
				}
			} while ( sqlsrv_next_result($stmt));
			sqlsrv_free_stmt( $stmt);
			
			$sql = "SELECT	emp_mst_name		
					FROM 	emp_mst (NOLOCK)
					WHERE  	emp_mst.site_cd = '".$site_cd."'
					AND		emp_mst_empl_id = '".$wkr_mst_originator."' ";
					
			$stmt = sqlsrv_query( $conn, $sql);	
					
			if( !$stmt ) {
				$error_message = "SELECT  table Error (emp_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				 
				  $emp_mst_name = $row['emp_mst_name'];
					
					 
			   }
			} while ( sqlsrv_next_result($stmt));
			sqlsrv_free_stmt( $stmt);
			
			if($emp_det_wo_budget_approver = '0'){
				$ld_wo_limit = 0;
			}
			
			$sql_insert_ast_mst = "INSERT INTO wko_det 
										(		site_cd, 						mst_RowID, 					wko_det_wr_no, 				wko_det_supv_id, 
												wko_det_planner,				wko_det_approver, 			wko_det_perm_id, 			wko_det_approved, 
												wko_det_safety, 				wko_det_pm_grp, 			wko_det_grp_code, 			wko_det_task_id, 
												wko_det_pm_idno, 				wko_det_route_id, 			wko_det_work_type, 			wko_det_work_locn, 
												wko_det_work_grp, 				wko_det_exc_date, 			wko_det_sched_date, 		wko_det_cmpl_date, 
												wko_det_sc_date, 				wko_det_act_code, 			wko_det_critical_ratio, 	wko_det_est_lab_cost,
												wko_det_est_mtl_cost,    		wko_det_est_con_cost, 		wko_det_lab_cost, 			wko_det_mtl_cost, 			
												wko_det_con_cost,				wko_det_support_rqmt, 		wko_det_corr_action, 		wko_det_chg_costcenter, 	
												wko_det_crd_costcenter, 		wko_det_laccount, 			wko_det_maccount,			wko_det_caccount, 			
												wko_det_work_class, 			wko_det_finance_clo, 		wko_det_budget, 			wko_det_wo_limit, 
												wko_det_fund_src, 				wko_det_ent_date,			wko_det_wo_open, 			wko_det_dispatch, 			
												wko_det_current_location, 		wko_det_cause_code, 		wko_det_wo_print, 			wko_det_on_dispatch_q, 	
												wko_det_check_out, 				wko_det_varchar1, 			wko_det_varchar2, 			wko_det_varchar3, 			
												wko_det_varchar4, 				wko_det_varchar5, 			wko_det_varchar6, 			wko_det_varchar7, 			
												wko_det_varchar8, 				wko_det_varchar9, 			wko_det_varchar10, 			wko_det_numeric1, 			
												wko_det_numeric2, 				wko_det_numeric3, 			wko_det_numeric4, 			wko_det_numeric5, 			
												wko_det_datetime1, 				wko_det_datetime2, 			wko_det_datetime3, 			wko_det_datetime4, 			
												wko_det_datetime5, 				wko_det_note1, 				wko_det_customer_cd,		audit_user, 						
												audit_date,						wko_det_temp_asset,			wko_det_est_misc_cost,		wko_det_misc_cost,
												wko_det_misc_amt,				wko_det_assign_to,			mbl_audit_user,				mbl_audit_date,
												wko_det_wr_org_date,			wko_det_wr_due_date) 
			
									VALUES 	(		?,								?,							?,							NULL,
													NULL,							?,							?,							'0',
													'0',							NULL,						NULL,						NULL,
													NULL,							NULL,						?,							NULL,
													?,								NULL,						NULL,						NULL,
													?,								NULL,						NULL,						0,
													0,								0,							0,							0,
													0,								Null,						Null,						?,
													NULL,							?,							?,							?,
													?,								Null,						?,							?,					
													Null,							Null,						'Y',						Null,
													NULL,							NULL,						NULL,						NULL,
													NULL,							?,							?,							?,
													?,								?,							NULL,						?,
													?,								?,							?,							0,
													0,								0,							0,							0,
													?,								?,							?,							?,
													?,								?,							?,							?,
													[dbo].[utc_getedate]('".$site_cd."'),						?,							0,							0,
													0,								?,							?,							[dbo].[utc_getedate]('".$site_cd."'),
													?,								?)";

			$params_wko_det = array($site_cd,$Mst_ROW_ID,$wkr_mst_wr_no,$EmpID,$ast_mst_perm_id,$wkr_mst_work_type,$wkr_mst_work_group,$wko_mst_org_date,$wkr_mst_chg_costcenter,$dft_mst_lab_act,$dft_mst_mat_act,$dft_mst_con_act,$wkr_mst_work_class,$emp_det_wo_approval_limit,$emp_det_wo_approval_limit,$wkr_det_varchar1,$wkr_det_varchar2,$wkr_det_varchar3,$wkr_det_varchar4,$wkr_det_varchar5,$ls_udf7,$ls_udf8,$ls_udf9,$ls_udf10,$wkr_det_datetime1,$wkr_det_datetime2,$wkr_det_datetime3,$wkr_det_datetime4,$wkr_det_datetime5,$wkr_det_note1,$ast_det_cus_code,$LOGINID,$wkr_mst_temp_asset,$ls_assign_emp,$LOGINID,$wkr_mst_org_date,$wkr_mst_due_date);					
			$stmt_wko_det = sqlsrv_query( $conn, $sql_insert_ast_mst,$params_wko_det);

			if( !$stmt_wko_det ) {
				$error_message = "Error insert table (INSERT Table wko_det)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}	
			sqlsrv_free_stmt( $stmt_wko_det);
			
			
			
			
			
			//insert img 
			$GetWkrRefImg = array();
			// insert img 
			$sqlSelectImg = "SELECT file_name, type, attachment,column1 FROM wkr_ref (NOLOCK) WHERE mst_RowID = ?";

			$params = array($RowID);
			$stmt_update_wkr_ref = sqlsrv_query($conn, $sqlSelectImg, $params);

			if (!$stmt_update_wkr_ref) {
				$errors = sqlsrv_errors();
				$error_message = "Error selecting from table (wkr_ref)";
				returnError($error_message, $errors);
				die();
			}

			while ($row = sqlsrv_fetch_array($stmt_update_wkr_ref, SQLSRV_FETCH_ASSOC)) {
				// Directly use attachment in hexadecimal format
				$attachment = $row['attachment'];

				// Store file name, type, and attachment
				$GetWkrRefImg[] = array(
					'file_name' => $row['file_name'],
					'type' => $row['type'],
					'column1' => $row['column1'],
					'attachment' => $attachment
				);
			}

			sqlsrv_free_stmt($stmt_update_wkr_ref);

			if (!empty($GetWkrRefImg)) {
				   foreach ($GetWkrRefImg as $img) {
					$file_name = $img['file_name'];
					$type = $img['type'];
					$column1 = $img['column1'];
					$attachment = bin2hex($img['attachment']);
					
				
					$sqlInsert = "INSERT INTO wko_ref (site_cd, mst_RowID, file_name, type, status, attachment, audit_user, audit_date ,column1)
								  VALUES (?, ?, ?, ?, 'Saved', 0x".$attachment.", ?, [dbo].[utc_getedate]('".$site_cd."'),?)";

					$paramsInsert = array($site_cd, $Mst_ROW_ID, $file_name, $type, $LOGINID, $column1);

					$result = sqlsrv_query($conn, $sqlInsert, $paramsInsert);

					if (!$result) {
						$errors = sqlsrv_errors();
						$error_message = "Error inserting into table (wko_ref)";
						returnError($error_message, $errors);
						die();
					}

					sqlsrv_free_stmt($result);
				}

			}
			
			
			if ($prefix == 'PR'){
				
				$Getchecklist = array();
				$ls_job;
				$ls_job_desc;
				
				if ($wkr_mst_work_class === "GENERAL - PAREA" || $wkr_mst_work_class === "GENERAL - STANK") {
					
					if ($wkr_mst_work_class === "GENERAL - PAREA") {
						$ls_job = 'PROCESSING AREA';
						$ls_job_desc = 'Processing Area Checklist';
					} elseif ($wkr_mst_work_class === "GENERAL - STANK") {
						$ls_job = 'STORAGE TANK';
						$ls_job_desc = 'Storage Tank';
					}
				}
				
				$sql_wkr_ls2 = "SELECT * FROM wkr_ls2 (NOLOCK) WHERE mst_RowID = ?";
				$params = array($RowID);
				$stmt_wkr_ls2 = sqlsrv_query($conn, $sql_wkr_ls2, $params);
				if (!$stmt_wkr_ls2) {
					$errors = sqlsrv_errors();
					$error_message = "Error selecting from table (wkr_ref)";
					returnError($error_message, $errors);
					die();
				}
				while ($row = sqlsrv_fetch_array($stmt_wkr_ls2, SQLSRV_FETCH_ASSOC)) {
					
					$Getchecklist[] = $row;	

				}
				sqlsrv_free_stmt($stmt_wkr_ls2);
				
				
				if (!empty($Getchecklist)) {
					
					$count = 1;
					foreach ($Getchecklist as $img) {
					$ll_cnt = $count++;	
					$site_cd = $img['site_cd'];
					$wkr_ls2_varchar2 = $img['wkr_ls2_varchar2'];
					$wkr_ls2_varchar3 = $img['wkr_ls2_varchar3'];
					$wkr_ls2_varchar4 = $img['wkr_ls2_varchar4'];
					$wkr_ls2_varchar5 = $img['wkr_ls2_varchar5'];
					$wkr_ls2_varchar6 = $img['wkr_ls2_varchar6'];
						
						$sql_wko_isp_Insert = "INSERT INTO wko_isp ( site_cd, 				mst_rowid, 					wko_isp_asset_no, 			wko_isp_job_cd, 			wko_isp_job_desc,
																	 wko_isp_sec_no, 		wko_isp_sec_desc,			wko_isp_stp_no,				wko_isp_stp_desc, 			wko_isp_stp_datatype,	
																	 wko_isp_stp_rowid,		wko_isp_checkbox1, 			wko_isp_numeric1,   		wko_isp_varchar1,			wko_isp_varchar2,	   
																	 wko_isp_varchar3,		audit_user,		    		audit_date)
														  VALUES   ( '".$site_cd."',		'".$Mst_ROW_ID."',			'".$wkr_mst_assetno."',		'".$ls_job."', 				'".$ls_job_desc."',  
																	 '1',					'".$ls_job_desc."',			'".$ll_cnt."',				'".$wkr_ls2_varchar2."',	'C',
																	 '0',					'".$wkr_ls2_varchar3."',	'".$wkr_ls2_varchar3."',	'".$wkr_ls2_varchar5."', 	'".$wkr_ls2_varchar6."', 
																	 '1',					'".$LOGINID."',				[dbo].[utc_getedate]('".$site_cd."'))";
															 
						
						$wko_isp_result = sqlsrv_query($conn, $sql_wko_isp_Insert);

						if (!$wko_isp_result) {
							$errors = sqlsrv_errors();
							$error_message = "Error inserting into table (wko_isp)";
							returnError($error_message, $errors);
							die();
						}

						sqlsrv_free_stmt($wko_isp_result);
							
						}
					
				}
				
			}
			
			
			$sql = " update wkr_mst SET wkr_mst_wr_status ='A' WHERE RowID = ? ";
			$params_wkr_mst = array($RowID);			
			$stmt_update_wkr_mst = sqlsrv_query( $conn, $sql,$params_wkr_mst);	
					
			if( !$stmt_update_wkr_mst ) {
				$error_message = "Error update table (wkr_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				 while ($row = sqlsrv_fetch_array($stmt_update_wkr_mst, SQLSRV_FETCH_ASSOC)) {
				 
				 $emp_det_wo_approval_limit = $row['emp_det_wo_approval_limit'];
				
					 
			   }
			} while ( sqlsrv_next_result($stmt_update_wkr_mst));
			sqlsrv_free_stmt( $stmt_update_wkr_mst);
			
			
			$sql = " update wkr_det SET wkr_det_approver = ? ,wkr_det_appr_date =[dbo].[utc_getedate]('".$site_cd."') , wkr_det_wo = ? ,wkr_det_datetime1 =Null  WHERE mst_RowID = ? ";
			$params_wkr_det = array($EmpID,$wko_mst_wo_no,$RowID);
			$stmt_update_wkr_det = sqlsrv_query( $conn, $sql, $params_wkr_det);	
					
			if( !$stmt_update_wkr_det ) {
				$error_message = "Error update table (wkr_det)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				 while ($row = sqlsrv_fetch_array($stmt_update_wkr_det, SQLSRV_FETCH_ASSOC)) {	
				
					 
			   }
			} while ( sqlsrv_next_result($stmt_update_wkr_det));
			sqlsrv_free_stmt( $stmt_update_wkr_det);
			
			
			$sql_insert_wko_sts = "	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 
						(	?,							?,							?,								?,
							?,							?,							NULL,							'1',
							?,							[dbo].[utc_getedate]('".$site_cd."'),					?,							[dbo].[utc_getedate]('".$site_cd."'))";

			$params_wko_sts = array($site_cd,$Mst_ROW_ID,$wko_mst_wo_no,$wrk_status,$LOGINID,$wkr_mst_org_date,$LOGINID,$LOGINID);	
			$stmt_wko_sts = sqlsrv_query( $conn, $sql_insert_wko_sts,$params_wko_sts);	
			if( !$stmt_wko_sts ) {
			$error_message = "Error insert table (INSERT Table wko_sts)";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
			}	
			sqlsrv_free_stmt($stmt_wko_sts);
			
			if ($prefix == 'MJR' || $prefix == 'MPR' || $prefix == 'PJR') {
				
				$sql = "SELECT	ll_loc_cnt = COUNT(*) 
					FROM	ast_loc (NOLOCK) 
					WHERE	ast_loc.site_cd = '".$site_cd."' 
					AND 	ast_loc.ast_loc_ast_loc = '".$wkr_mst_assetlocn."' 
					AND		ast_loc.ast_loc_wo_prefix IS NOT NULL  
					AND		ast_loc.ast_loc_wo_option = '1'";			
			
				$stmt_ll_loc_cnt = sqlsrv_query( $conn, $sql);			
				if( !$stmt_ll_loc_cnt ) {
					$error_message = "Error select table (ll_loc_cnt)";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
				do {
					while ($row = sqlsrv_fetch_array($stmt_ll_loc_cnt, SQLSRV_FETCH_ASSOC)) {	
					
						$ll_loc_cnt = $row['ll_loc_cnt'];	
					}
				} while ( sqlsrv_next_result($stmt_ll_loc_cnt));
				sqlsrv_free_stmt($stmt_ll_loc_cnt);


				$sql = "SELECT	ll_cnt = COUNT(*)
						FROM	wrk_typ (NOLOCK)
						WHERE	site_cd = '".$site_cd."'
						AND		wrk_typ_typ_cd = '".$wkr_mst_work_type."'
						AND		wrk_typ_wr_prefix IS NOT NULL		
						AND		wrk_typ_wr_option = '1'";			
					
				$stmt_ll_cnt = sqlsrv_query( $conn, $sql);			
				if( !$stmt_ll_cnt ) {
					$error_message = "Error select table (ll_cnt)";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
				do {
					while ($row = sqlsrv_fetch_array($stmt_ll_cnt, SQLSRV_FETCH_ASSOC)) {	
					
						$ll_cnt = $row['ll_cnt'];	
					}
				} while ( sqlsrv_next_result($stmt_ll_cnt));	
				sqlsrv_free_stmt($stmt_ll_cnt);
				
				if($ll_loc_cnt>0){
	
					$sql = "UPDATE 	ast_loc WITH (UPDLOCK)
							SET 	ast_loc_wo_counter = ast_loc_wo_counter + 1
							WHERE 	site_cd ='".$site_cd."' 
							AND 	ast_loc_ast_loc = '".$wkr_mst_assetlocn."'";			
						
					$stmt = sqlsrv_query( $conn, $sql);			
					if( !$stmt ) {
						$error_message = "Error UPDATE table (ast_loc)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}
					sqlsrv_free_stmt( $stmt);
	
				}else if ($ll_cnt >0){
	
					$sql = "UPDATE 	wrk_typ WITH (UPDLOCK)
							SET 	wrk_typ_counter = wrk_typ_counter + 1
							WHERE 	site_cd ='".$site_cd."' 
							AND 	wrk_typ_typ_cd = '".$wkr_mst_work_type."'";			
							
					$stmt = sqlsrv_query( $conn, $sql);			
					if( !$stmt ) {
						$error_message = "Error UPDATE table (wrk_typ)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}
					sqlsrv_free_stmt( $stmt);
	
				}else{
	
					$sql = "UPDATE 	cnt_mst WITH (UPDLOCK)
							SET 	cnt_mst_counter = cnt_mst_counter + 1
							WHERE 	site_cd ='".$site_cd."' 
							AND 	cnt_mst_module_cd = 'WKO'";			
							
					$stmt = sqlsrv_query( $conn, $sql);			
					if( !$stmt ) {
						$error_message = "Error UPDATE table (cnt_mst)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}
					sqlsrv_free_stmt( $stmt);
				}
				
				
			}
			
			
			if (!empty($ls_assign_emp) || !empty($ls_udf7) || !empty($ls_udf8) || !empty($ls_udf9) || !empty($ls_udf10)) {
				
				
				$sql_insert_wko_sts = "	INSERT wko_ls7 
						(	site_cd,				mst_RowID,					wko_ls7_level,			wko_ls7_emp_id, 
							wko_ls7_due_days,		wko_ls7_wo_org_date,		wko_ls7_due_date,		wko_ls7_total_days, 
							audit_user ,			audit_date,					mbl_audit_user,			mbl_audit_date ) 
				VALUES 
						(	?,						?,							?,						?,
							?,						GetDate(),					NULL,					NULL,
							?,						GetDate(),					?,			GetDate())";

				$params_wko_ls7 = array($site_cd,$Mst_ROW_ID,'1',$ls_assign_emp,'1',$LOGINID,$LOGINID);	
				$stmt = sqlsrv_query( $conn, $sql_insert_wko_sts,$params_wko_ls7);	
				if( !$stmt ) {
				$error_message = "Error insert table (INSERT Table wko_sts)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
				}	
				sqlsrv_free_stmt( $stmt);
				
				
				//Send Notification
				$title = "Work Order Assigned";
				$body = "The work order ( " . $wko_mst_wo_no . " ) has been assigned to you.";
				$module = "Work Order";
				$message_assign = "Asset No: " . $wkr_mst_assetno . " / " . $ast_mst_asset_shortdesc . "\r\n" .
					"Work Area: " . $work_area . "\r\n" .
					"Location: " . $assetlocn;
				
				
				
				if (!empty($ls_assign_emp) || !empty($ls_udf7) || !empty($ls_udf8) || !empty($ls_udf9) || !empty($ls_udf10)) {
					
					
					// Convert assigned employees into an array (ignoring empty values)
					$assignedEmployees = array_filter(explode(",", $wko_det_assign_to), 'strlen');
					// Loop through each assigned employee and send notifications
					foreach ($assignedEmployees as $emp_id) {
						
						$emp_id = trim($emp_id);
						 
						// Insert notification log for each assigned employee
						$insert_ntf4 = insert_notification_log($site_cd, $EmpID, $emp_id, $wko_mst_wo_no, $title, $body, $message_assign, $module, $dvc_id, $LOGINID);

						foreach ($insert_ntf4 as $val) {
							$row = $val["row"];
							$insert_ntf = $val["insert_ntf"];
						}
						
						// Get assigned employee’s token for push notification
						$tkn_token_2 = get_online($emp_id, $site_cd);
						
						if (!empty($tkn_token_2)) {
							
							foreach ($tkn_token_2 as $val) {
								
								$tokenlist = $val["token"];
								$RowID = $val["RowID"];
								$tkn_site = $val["tkn_site"];
								
								// Send push notification
								push_notification_android($tokenlist,'The work order (' . $wko_mst_wo_no . ') has been assigned to you',"Work Order Assigned (" . $site_cd . ")",$site_cd,$tokenlist,$RowID,$row,$tkn_site,$LOGINID);
							}
							
						}
						
						
					}
				} 
				
			}
			
			
			//Finaly checking 
			if($stmt_wko_mst && $stmt_wko_det && $stmt_update_wkr_mst && $stmt_update_wkr_det && $stmt_wko_sts){
				
				sqlsrv_commit( $conn );
				sqlsrv_close( $conn);	
				returnData($Mst_ROW_ID,$wko_mst_wo_no);
				
				
			}else{
				
			 sqlsrv_rollback( $conn );
			 $error_message = "Transaction rolled back.<br />";
			 returnError($error_message);
				
			}	
		}
		
	}
	

}else{
	
	$sql = "SELECT wkr_det_wo FROM wkr_det (NOLOCK) 
			WHERE	wkr_det.site_cd = '".$site_cd."' 
			AND wkr_det.mst_RowID = '".$RowID."'";			
			
	$stmt = sqlsrv_query( $conn, $sql);	
	
	if( !$stmt ) {
		$error_message = "Error select table (cnt_mst)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}
	do {
		while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
		
			$wkr_det_wo = $row['wkr_det_wo'];				
		
		}
	} while ( sqlsrv_next_result($stmt));
	sqlsrv_free_stmt( $stmt);
	sqlsrv_rollback( $conn );
	$error_message = 'Work Request is already Approved Work Order No: ' .$wkr_det_wo. '';
	returnalert($error_message);
	
}


function returnData($Mst_ROW_ID,$wko_mst_wo_no){
	$returnData = array(
	'status' => 'SUCCESS',
	'ROW_ID'=>	$Mst_ROW_ID,
	'WorkOrderno'=>	$wko_mst_wo_no,
	'message' => 'Work order no ' .$wko_mst_wo_no.  ' had created successfully');
	echo json_encode($returnData);
}


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



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