<?php
require_once('config.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');

$insert_ntf = false;
$error_message;
$valid = true;

/* 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'];

if($sync_status =="online"){
	
}else{
	f_logging($json,"insert_workorder.php",$sync_url,$LOGINID,$sync_step,$sync_time,$site_cd);
}




$EmpID = $data['EmpID'];
$EmpName = $data['EmpName'];

$wko_mst_originator = $data['wko_mst_originator'];
$wko_mst_phone = $data['wko_mst_phone'];
 
$ast_mst_asset_no = $data['ast_mst_asset_no'];
$ast_mst_asset_shortdesc = $data['ast_mst_asset_shortdesc'];
$ast_mst_asset_group = $data['ast_mst_asset_group'];
$mst_war_work_area = $data['mst_war_work_area'];
$ast_mst_asset_locn = $data['ast_mst_asset_locn'];
$ast_mst_asset_lvl = $data['ast_mst_asset_lvl'];
$ast_mst_asset_code = $data['ast_mst_asset_code'];
$ast_mst_perm_id = $data['ast_mst_perm_id'];
$ast_det_cus_code = $data['ast_det_cus_code'];
$ast_mst_asset_status = $data['ast_mst_asset_status'];

$wko_mst_wo_no = $data['wko_mst_wo_no'];
$wko_mst_chg_costcenter = $data['wko_mst_chg_costcenter'];

$wko_det_laccount = $data['wko_det_laccount'];
$wko_det_caccount = $data['wko_det_caccount'];
$wko_det_maccount = $data['wko_det_maccount'];

$wko_mst_orig_priority = $data['wko_mst_orig_priority'];
$wko_mst_plan_priority = $data['wko_mst_plan_priority'];
$wko_mst_org_date = $data['wko_mst_org_date'];
$wko_mst_due_date = $data['wko_mst_due_date'];
$wko_mst_status = $data['wko_mst_status'];
$wko_det_work_type = $data['wko_det_work_type'];
$wko_mst_flt_code = $data['wko_mst_flt_code'];
$wko_mst_descs = $data['wko_mst_descs'];

$wko_det_assign_to =$data['wko_det_assign_to'];
$cnt_mst_numbering = $data['cnt_mst_numbering'];
$wko_det_parent_wo = $data['wko_det_parent_wo'];
$wko_det_work_grp = $data['wko_det_work_grp'];

$dvc_id = $data['dvc_id'];

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_typ_option, wrk_typ_prefix + SUBSTRING(CONVERT(VARCHAR(7), wrk_typ_counter + 1000000), 2, 6)  ls_doc_no 
			from wrk_typ where wrk_typ_typ_cd ='".$wko_det_work_type."'";			
			
			$stmt = sqlsrv_query( $conn, $sql);	
			if( !$stmt ) {
				$error_message = "Error select table (wrk_typ)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			
			do {
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	 
				$wrk_typ_option = $row['wrk_typ_option'];					
				$ls_doc_no = $row['ls_doc_no'];				
				$wko_mst_wo_no  = $ls_doc_no;					
					 
			}
		} while ( sqlsrv_next_result($stmt));
			sqlsrv_free_stmt( $stmt);


	if ($wrk_typ_option =="0"){		
	
			switch ($cnt_mst_numbering) {
	
			case 'A':
					
			
			$sql = "Select cnt_mst_prefix + SUBSTRING(CONVERT(VARCHAR(7), cnt_mst_counter + 1000000), 2, 6)  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;
								 
					}
				} 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 = "Work Order number is already exists";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
			
			
			 
			break;
			
			case 'M':
			
			$sql = "Select Count(*) ll_dup_cnt	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 = "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 = "Work Order number is already exists";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				} 
			 
			break;
		}
		
		
	}else{
		
		
		$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 = "Work Order number is already exists";
					returnError($error_message);
					die( print_r( sqlsrv_errors(), true));
				}
		
		
		
	}

$sql_insert_ast_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_orig_priority,			wko_mst_plan_priority,			wko_mst_type,				wko_mst_pm_grp,				
			wko_mst_ast_cod,				audit_user,						audit_date,					wko_mst_create_by,		
			wko_mst_create_date,			column1,						column2,					column3,					
			column4,						column5,						wko_mst_asset_status ) 
			
VALUES 	(	?,								?,								?,							?,
			?,								?,								?,							?,
			?,								?,								NULL,						?,
			?,								?,								?,							?,
			?,								?,								?,							NULL,						
			?,								?,								GetDate(),					?,
			GetDate(),						NULL,							NULL,						NULL,						
			NULL,							NULL,							?) ";
								
								
$params_wko_mst = array($site_cd,$wko_mst_wo_no,$wko_mst_originator,$wko_mst_phone,$ast_mst_asset_lvl,$ast_mst_asset_no,$wko_mst_flt_code,$wko_mst_status,$wko_mst_due_date,$wko_mst_descs,$wko_mst_org_date,$wko_mst_chg_costcenter,$mst_war_work_area,$ast_mst_asset_locn,$ast_mst_asset_group,$wko_mst_orig_priority,$wko_mst_plan_priority,'C',$ast_mst_asset_code,$LOGINID,$LOGINID,$ast_mst_asset_status);

$stmt_wko_mst = sqlsrv_query( $conn, $sql_insert_ast_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);	



$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 = "Error  table (itm_mst)";
	returnError($error_message);
	die( print_r( sqlsrv_errors(), true));
}
do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
	 
	 $ROW_ID = $row['Rowid'];		
	 	 
   }
} while ( sqlsrv_next_result($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 = "Error update table (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));
 */

$sql = " select 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_empl_id ='".$EmpID."'";
		
$stmt = sqlsrv_query( $conn, $sql);	
		
if( !$stmt ) {
	$error_message = "Error update table (emp_mst)";
	returnError($error_message);
	die( print_r( sqlsrv_errors(), true));
}
do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
	 
	 $emp_det_wo_approval_limit = $row['emp_det_wo_approval_limit'];
	
	 	 
   }
} while ( sqlsrv_next_result($stmt));


$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_ent_date,				wko_det_clo_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_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_assign_to,				wko_det_parent_wo,				
				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_saccount,				wko_det_est_misc_cost,			wko_det_misc_cost, 
				wko_det_customer_cd,					wko_det_temp_asset,				wko_det_contract_no,			wko_det_delay_cd, 
				audit_user,								audit_date,						column1,						column2, 
				column3,								column4,						column5 ) 
			
VALUES 	(		?,											?,								NULL,							NULL,
				NULL,										NULL,							?,								'0',
				'0',										NULL,							NULL,							NULL,
				NULL,										NULL,							?,								NULL,
				?,											NULL,							NULL,							NULL,
				NULL,										NULL,							NULL,							NULL,
				NULL,										'0.0000',						'0.0000',						'0.0000',
				'0.0000',									'0.0000',						'0.0000',						NULL,
				NULL,										NULL,							NULL,							?,
				?,											?,								NULL,							NULL,
				?,											'0.0000',						NULL,							'Y',
				NULL,										NULL,							NULL,							NULL,
				NULL,										NULL,							?,								?,
				NULL,										NULL,							NULL,							NULL,
				NULL,										NULL,							NULL,							NULL,
				NULL,										NULL,							NULL,							NULL,
				NULL,										NULL,							NULL,							NULL,
				NULL,										NULL,							NULL,							NULL,	
				NULL,										NULL,							'0.0000',						'0.0000',
				?,											'0',							NULL,							NULL,
				?,											GetDate(),						NULL,							NULL,
				NULL,										NULL,							NULL) ";
								
								
$params_wko_det = array($site_cd,$ROW_ID,$ast_mst_perm_id,$wko_det_work_type,$wko_det_work_grp,$wko_det_laccount,$wko_det_maccount,$wko_det_caccount,$emp_det_wo_approval_limit,$wko_det_assign_to,$wko_det_parent_wo,$ast_det_cus_code,$LOGINID);				
$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_mst)";
	returnError($error_message);
	die( print_r( sqlsrv_errors(), true));
}	
sqlsrv_free_stmt( $stmt_wko_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 ) 
				VALUES 
						(	'".$site_cd."',				'".$ROW_ID."',				'".$wko_mst_wo_no."',			'".$wko_mst_status."',
							'".$wko_mst_originator."',	GetDate(),					NULL,							NULL,
							'".$LOGINID."',				GetDate())";

$params_wko_sts = array($site_cd,$ROW_ID,$wko_mst_wo_no,$wko_mst_status,$wko_mst_originator,$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);

//null to Values 
if(!empty($wko_det_assign_to)){

	
	$title = "Work Order Assigned";
	$body = "The work order  ( '".$wko_mst_wo_no."' ) been assigned to you.";
	$module = "Work Order";
	$message_assign ="Asset No: ".$ast_mst_asset_no." / ".$ast_mst_asset_shortdesc." 
	\r\nLocation: ".$ast_mst_asset_locn."";
	
	
	
	$sql_wko_ls7_max = "select max_level = coalesce( MAX (wko_ls7_level ),0) + 1 from  wko_ls7 (NOLOCK) where site_cd =? and mst_RowID = ?";
				
			
			$params_wko_ls7_max = array($site_cd,$ROW_ID);
			$stmt_wko_ls7_max = sqlsrv_query( $conn, $sql_wko_ls7_max,$params_wko_ls7_max);	
				
		if( !$stmt_wko_ls7_max ) {
			$error_message = "Error select table (wko_ls7)";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		}
		do {
			
			while ($row = sqlsrv_fetch_array($stmt_wko_ls7_max, SQLSRV_FETCH_ASSOC)) {		
			
			$max_level = $row['max_level'];		
			
			}
			
		} while ( sqlsrv_next_result($stmt_wko_ls7_max) );
		//sqlsrv_free_stmt( $stmt_wko_ls7_max);
	
	
	$sql_insert_wko_ls7 = "	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 ) 
						VALUES 
								(	?,							?,							?,								?,
									?,							GetDate(),					NULL,							'1',
									?,							GetDate())";

		$params_insert_wko_ls7 = array($site_cd,$ROW_ID,$max_level,$wko_det_assign_to,'1',$LOGINID);
		$stmt_insert_wko_ls7 = sqlsrv_query( $conn, $sql_insert_wko_ls7,$params_insert_wko_ls7);	
		if( !$stmt_insert_wko_ls7 ) {
		$error_message = "Error (INSERT Table wko_ls7)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
			
		}
		sqlsrv_free_stmt( $stmt_insert_wko_ls7);
	
	$insert_ntf1 = insert_notification_log($site_cd, $EmpID, $wko_det_assign_to, $wko_mst_wo_no, $title, $body, $message_assign, $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($wko_det_assign_to, $site_cd);
	
	//echo '<pre>'; print_r($tkn_token_2); 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.') had been assigned to you',"Work Order Assigned(".$site_cd.")",$site_cd,$tokenlist,$RowID,$row,$tkn_site,$LOGINID);			
			
		
		}		
		
	}	
	
	
	
}



if($stmt_wko_mst && $stmt_wko_det && $stmt_wko_sts){
	
	if ($wrk_typ_option =="0"){	
	
		$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 select table (cnt_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}			
			sqlsrv_free_stmt( $stmt);
	
			
	}else{
		
		$sql = "UPDATE 	wrk_typ WITH (UPDLOCK)
			SET 	wrk_typ_counter = wrk_typ_counter + 1
			WHERE 	site_cd ='".$site_cd."' 
			AND 	wrk_typ_typ_cd = '".$wko_det_work_type."'";			
					
			$stmt = sqlsrv_query( $conn, $sql);	
			if( !$stmt ) {
				$error_message = "Error select table (wrk_typ)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}			
			sqlsrv_free_stmt( $stmt);
		
	}	
	
	if(!empty($wko_det_assign_to)){
		if($insert_ntf) {
			 sqlsrv_commit( $conn );
			 sqlsrv_close( $conn);	
			 returnData($ROW_ID,$wko_mst_wo_no);
		}else{
			 sqlsrv_rollback( $conn );
			 $error_message = "Transaction rolled back.<br />";
			 returnError($error_message);
		}
	}else{
		 sqlsrv_commit( $conn );
		 sqlsrv_close( $conn);	
		 returnData($ROW_ID,$wko_mst_wo_no);
	}	
	
}else{
	sqlsrv_rollback( $conn );
	$error_message = "Transaction rolled back.<br />";
	returnError($error_message);
}

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

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






 
 
?>