<?php
require_once('config.php');
$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'];
$EmpID = $data['EmpID'];
$EmpName = $data['EmpName'];

$wkr_mst_originator 	= !empty($data['wkr_mst_originator']) ? $data['wkr_mst_originator'] : null;
$wkr_mst_phone 			= !empty($data['wkr_mst_phone']) ? $data['wkr_mst_phone'] : null;
 
$wkr_mst_assetno = $data['wkr_mst_assetno'];

$mst_war_work_area 		= !empty($data['mst_war_work_area']) ? $data['mst_war_work_area'] : null;
$wkr_mst_assetlocn 		= !empty($data['wkr_mst_assetlocn']) ? $data['wkr_mst_assetlocn'] : null;
$wkr_mst_location 		= !empty($data['ast_mst_asset_lvl']) ? $data['ast_mst_asset_lvl'] : null;


$wkr_mst_wr_no = $data['wkr_mst_wr_no'];

$wkr_mst_chg_costcenter = !empty($data['wkr_mst_chg_costcenter']) ? $data['wkr_mst_chg_costcenter'] : null;
$wkr_mst_orig_priority 	= !empty($data['wkr_mst_orig_priority']) ? $data['wkr_mst_orig_priority'] : null;

$wkr_mst_org_date = $data['wkr_mst_org_date'];
$wkr_mst_due_date = $data['wkr_mst_due_date'];

$wkr_mst_fault_code 	= !empty($data['wkr_mst_fault_code']) ? $data['wkr_mst_fault_code'] : null;
$wkr_mst_work_group 	= !empty($data['wkr_mst_work_group']) ? $data['wkr_mst_work_group'] : null;
$wkr_mst_wr_descs 		= !empty($data['wkr_mst_wr_descs']) ? $data['wkr_mst_wr_descs'] : null;
$wkr_mst_work_type 		= !empty($data['wkr_mst_work_type']) ? $data['wkr_mst_work_type'] : null;
$wkr_mst_work_class 	= !empty($data['wkr_mst_work_class']) ? $data['wkr_mst_work_class'] : null;


$LOGINID = $data['LOGINID'];

$cnt_mst_numbering = $data['cnt_mst_numbering'];


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 =?
					And 	cnt_mst_module_cd =  'WKR'";		

			$params = array($site_cd);
			$stmt = sqlsrv_query( $conn, $sql, $params);			
			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'];				
				$wkr_mst_wr_no  = $ls_doc_no;
						 
			}
		} while ( sqlsrv_next_result($stmt));
	
		$sql_wkr_mst = "Select Count(*) ll_dup_cnt	From wkr_mst (NOLOCK) Where site_cd = ? And wkr_mst_wr_no = ?";			
		
		$params_wkr_mst = array($site_cd,$wkr_mst_wr_no);
		$stmt_wkr_mst = sqlsrv_query( $conn, $sql_wkr_mst,$params_wkr_mst);	
		if( !$stmt_wkr_mst ) {
			$error_message = "Error select table (wkr_mst)";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		}
		
		do {
			while ($row = sqlsrv_fetch_array($stmt_wkr_mst, SQLSRV_FETCH_ASSOC)) {	 
				$ll_dup_cnt = $row['ll_dup_cnt'];				 
			}
		} while ( sqlsrv_next_result($stmt_wkr_mst));
	
		if($ll_dup_cnt == 1){	
			$error_message = "Work Request number is already exists auto";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		} 
	 
	break;

	case 'M':
	
			$sql_wkr_mst = "Select Count(*) ll_dup_cnt	From wkr_mst (NOLOCK) Where site_cd = ? And wkr_mst_wr_no = ?";			
				
			$params_wkr_mst = array($site_cd,$wkr_mst_wr_no);
			$stmt_wkr_mst = sqlsrv_query( $conn, $sql_wkr_mst,$params_wkr_mst);	
			if( !$stmt_wkr_mst ) {
				$error_message = "Error select table (wkr_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			
			do {
				while ($row = sqlsrv_fetch_array($stmt_wkr_mst, SQLSRV_FETCH_ASSOC)) {	 
					$ll_dup_cnt = $row['ll_dup_cnt'];		 
				}
			} while ( sqlsrv_next_result($stmt_wkr_mst));
		
			if($ll_dup_cnt == 1){	
				$error_message = "Asset number is already exists";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			} 
	 
	break;
}


$sql_insert_wkr_mst = "INSERT INTO wkr_mst (	site_cd,					wkr_mst_wr_no,				wkr_mst_org_date,			wkr_mst_org_time,			wkr_mst_taken_by,			wkr_mst_originator, 
												wkr_mst_phone,				wkr_mst_orig_priority,		wkr_mst_due_date,			wkr_mst_fault_code,			wkr_mst_wr_descs,			wkr_mst_assetno, 
												wkr_mst_wr_status,			wkr_mst_wo_status,			wkr_mst_work_type,			wkr_mst_work_class,			wkr_mst_chg_costcenter,		wkr_mst_work_group, 
												wkr_mst_approved,			wkr_mst_location,			wkr_mst_assetdesc,			wkr_mst_projectid,			wkr_mst_work_area,			wkr_mst_planner, 
												wkr_mst_assetlocn,			wkr_mst_requestor,			wkr_mst_requestor_phone,	wkr_mst_org_locn,			wkr_mst_dept,				wkr_mst_capital_project,
												wkr_mst_temp_asset, 		wkr_mst_email_notification, audit_user,					audit_date,					wkr_mst_create_by,			wkr_mst_create_date,		
												column1, 					column2,					column3,					column4,					column5,					mbl_audit_user,
												mbl_audit_date ) 
												
									VALUES (	?,							?,							?,							NULL,						NULL,						?,
												?,							?,							?,							?,							?,							?,
												?,							NULL,						?,							?,							?,							?,
												NULL,						?,							NULL,						NULL,						?,							NULL,
												?,							NULL,						NULL,						NULL,						NULL,						NULL,
												?,							?,							?,							getdate(),					?,							getDate(),
												NULL,						NULL,						NULL,						NULL,						NULL,						?,
												getDate())";
								


$params_wkr_mst = array($site_cd,$wkr_mst_wr_no,$wkr_mst_org_date,$wkr_mst_originator,$wkr_mst_phone,$wkr_mst_orig_priority,$wkr_mst_due_date,$wkr_mst_fault_code,$wkr_mst_wr_descs,$wkr_mst_assetno,'W',$wkr_mst_work_type,$wkr_mst_work_class,$wkr_mst_chg_costcenter,$wkr_mst_work_group,$wkr_mst_location,$mst_war_work_area,$wkr_mst_assetlocn,'0','0',$LOGINID,$LOGINID,$LOGINID,);
				
$stmt_insert_wkr_mst = sqlsrv_query( $conn, $sql_insert_wkr_mst,$params_wkr_mst);

if( !$stmt_insert_wkr_mst ) {
	$error_message = "Error insert table (INSERT Table wkr_mst)";
	returnError($error_message);
	die( print_r( sqlsrv_errors(), true));
}	
sqlsrv_free_stmt( $stmt_insert_wkr_mst);	



$sql_wkr_mst = "SELECT Rowid From wkr_mst (NOLOCK) WHERE site_cd = ? AND wkr_mst_wr_no = ?";

$params_select_wkr_mst= array($site_cd,$wkr_mst_wr_no);
$stmt_select_wkr_mst = sqlsrv_query( $conn, $sql_wkr_mst,$params_select_wkr_mst);	
		
if( !$stmt_select_wkr_mst ) {
	$error_message = "Error  table (itm_mst)";
	returnError($error_message);
	die( print_r( sqlsrv_errors(), true));
}
do {
     while ($row = sqlsrv_fetch_array($stmt_select_wkr_mst, SQLSRV_FETCH_ASSOC)) {
	 
	 $ROW_ID = $row['Rowid'];		
	 	 
   }
} while ( sqlsrv_next_result($stmt_select_wkr_mst));




$sql_insert_wkr_det = "INSERT INTO wkr_det
										(	site_cd,			mst_RowID,			wkr_det_reject_by,	wkr_det_reject_desc,	wkr_det_reject_date, 
											wkr_det_approver,	wkr_det_appr_date,	wkr_det_wo,			wkr_det_varchar1,		wkr_det_varchar2, 
											wkr_det_varchar3,	wkr_det_varchar4,	wkr_det_varchar5,	wkr_det_varchar6,		wkr_det_varchar7, 
											wkr_det_varchar8,	wkr_det_varchar9,	wkr_det_varchar10,	wkr_det_varchar11,		wkr_det_varchar12, 
											wkr_det_varchar13,	wkr_det_varchar14,	wkr_det_varchar15,	wkr_det_varchar16,		wkr_det_varchar17, 
											wkr_det_varchar18,	wkr_det_varchar19,	wkr_det_varchar20,	wkr_det_varchar21,		wkr_det_varchar22, 
											wkr_det_varchar23,	wkr_det_varchar24,	wkr_det_varchar25,	wkr_det_varchar26,		wkr_det_varchar27, 
											wkr_det_varchar28,	wkr_det_varchar29,	wkr_det_varchar30,	wkr_det_numeric1,		wkr_det_numeric2, 
											wkr_det_numeric3,	wkr_det_numeric4,	wkr_det_numeric5,	wkr_det_numeric6,		wkr_det_numeric7, 
											wkr_det_numeric8,	wkr_det_numeric9,	wkr_det_numeric10,	wkr_det_numeric11,		wkr_det_numeric12, 
											wkr_det_numeric13,	wkr_det_numeric14,	wkr_det_numeric15,	wkr_det_numeric16,		wkr_det_numeric17, 
											wkr_det_numeric18,	wkr_det_numeric19,	wkr_det_numeric20,	wkr_det_datetime1,		wkr_det_datetime2, 
											wkr_det_datetime3,	wkr_det_datetime4,	wkr_det_datetime5,	wkr_det_datetime6,		wkr_det_datetime7, 
											wkr_det_datetime8,	wkr_det_datetime9,	wkr_det_datetime10, wkr_det_datetime11,		wkr_det_datetime12, .
											wkr_det_datetime13, wkr_det_datetime14, wkr_det_datetime15, wkr_det_datetime16,		wkr_det_datetime17, 
											wkr_det_datetime18, wkr_det_datetime19, wkr_det_datetime20, wkr_det_note1,			wkr_det_note2, 
											wkr_det_note3,		audit_user,			audit_date,			column1,				column2, 
											column3,			column4,			column5,			mbl_audit_user,			mbl_audit_date ) 
								VALUES (	?,					?,					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,
											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,				NULL,					NULL,
											NULL,				NULL,				NULL,				NULL,					NULL,
											NULL,				NULL,				NULL,				NULL,					NULL,
											NULL,				NULL,				NULL,				NULL,					NULL,
											NULL,				NULL,				NULL,				NULL,					NULL,
											NULL,				?,					getDate(),			NULL,					NULL,
											NULL,				NULL,				NULL,				?,						getDate())";
						

$params_insert_wkr_det = array($site_cd,$ROW_ID,$LOGINID,$LOGINID);						
								
				
$stmt_insert_wkr_det = sqlsrv_query( $conn, $sql_insert_wkr_det,$params_insert_wkr_det);

if( !$stmt_insert_wkr_det ) {
	$error_message = "Error insert table (INSERT Table wkr_det)";
	returnError($error_message);
	die( print_r( sqlsrv_errors(), true));
}	
sqlsrv_free_stmt( $stmt_insert_wkr_det); 



$sql_cnt_mst = "UPDATE 	cnt_mst WITH (UPDLOCK)
			SET 		cnt_mst_counter 	= cnt_mst_counter + 1,
						mbl_audit_user		=	?,							
						mbl_audit_date		=	getDate()
			WHERE 	site_cd =? 
			AND 	cnt_mst_module_cd = 'WKR'";		

			
			$params_cnt_mst = array($LOGINID,$site_cd);
			$stmt_cnt_mst = sqlsrv_query( $conn, $sql_cnt_mst, $params_cnt_mst);			
			if( !$stmt_cnt_mst ) {
				$error_message = "Error Update table (cnt_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			
		 while ( sqlsrv_next_result($stmt_cnt_mst));
		
		
		
		
switch ($cnt_mst_numbering) {
	case 'A':
	if( $stmt && $stmt_wkr_mst) {
					sqlsrv_commit( $conn );
					//returnData($ROW_ID,$wkr_mst_wr_no);	
		} else {
				 sqlsrv_rollback( $conn );
				 $error_message = "Transaction rolled back.<br />";
				 returnError($error_message);
		} 
	
	break;
	
	case 'M':
	if($stmt_wkr_mst) {
					sqlsrv_commit( $conn );
					//returnData($ROW_ID,$wkr_mst_wr_no);	
		} else {
				 sqlsrv_rollback( $conn );
				 $error_message = "Transaction rolled back.<br />";
				 returnError($error_message);
		} 
	
	break;
	
}

if( $stmt_insert_wkr_mst && $stmt_select_wkr_mst && $stmt_insert_wkr_det && $stmt_cnt_mst ) {
		 sqlsrv_commit( $conn );
		 sqlsrv_close( $conn);	
		 returnData($ROW_ID,$wkr_mst_wr_no);	
} else {
		 sqlsrv_rollback( $conn );
		 $error_message = "Transaction rolled back.<br />";
		 returnError($error_message);
} 
 
 

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

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

//sqlsrv_close( $conn);




 
 
?>