<?php
require_once('config.php');
$error_message;
$valid = true;

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

$ll_row = 0;
$mtr_mst_mtr_no;
$ldc_tot_cost = 0;
$first_row = true;
foreach($result->Header as $Heade){
	
	
	$site_cd= $Heade->site_cd;
	$wko_RowID= $Heade->wko_RowID;
	$wko_mst_wo_no= $Heade->wko_mst_wo_no;
	$wko_mst_assetno= $Heade->wko_mst_assetno;
	$wko_mst_chg_costcenter= $Heade->chargecostcenter;
	
	$wko_det_laccount= $Heade->wko_det_laccount;
	$wko_det_caccount= $Heade->wko_det_caccount;
	$wko_det_maccount= $Heade->wko_det_maccount;
	
	$audit_user= $Heade->audit_user;
	$audit_date= $Heade->audit_date;
	$LogingID= $Heade->LOGINID;
	
	
	$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 =  'MTR'
			UPDATE 	cnt_mst WITH (UPDLOCK)
			SET 		cnt_mst_counter = cnt_mst_counter + 1
			WHERE 	site_cd ='".$site_cd."' 
			AND 	cnt_mst_module_cd = 'MTR'";			
			
			
			$stmt = sqlsrv_query( $conn, $sql);			
			if( !$stmt ) {
				$error_message = "Error update table (itm_mst)";
				returnError($error_message);
				die( print_r( sqlsrv_errors(), true));
			}
			do {
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
				
				$mtr_mst_mtr_no = $row['ls_doc_no'];

				
				
					 	 
			}
		} while ( sqlsrv_next_result($stmt));
	
	
	$sql = "SELECT 	dft_mst_mtr_sts,dft_mst_mr_release_for_app,dft_mst_mtr_email_requestor,dft_mst_mtr_email
			FROM	dft_mst(NOLOCK)
			WHERE	( dft_mst.site_cd = '".$site_cd."' )";


		$stmt = sqlsrv_query( $conn, $sql);				

		if( !$stmt ) {
			 $error_message = "Error select table (dft_mst)";
			 returnError($error_message);
			 die( print_r( sqlsrv_errors(), true));
		}

		do {
			 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				$ls_mr_status = $row['dft_mst_mtr_sts'];	
				$dft_mst_mr_release_for_app = $row['dft_mst_mr_release_for_app'];	
				$dft_mst_mtr_email_requestor = $row['dft_mst_mtr_email_requestor'];	
				$dft_mst_mtr_email = $row['dft_mst_mtr_email'];	
			 }
		} while ( sqlsrv_next_result($stmt) );		
		
		
		
	if(empty($mtr_sts_status)){
		
		$sql = "SELECT TOP 1 mtr_sts.mtr_sts_status 
				FROM mtr_sts (NOLOCK)
				WHERE mtr_sts.site_cd ='".$site_cd."' 
				AND mtr_sts.mtr_sts_st_cat ='MR' 
				AND mtr_sts.mtr_sts_status_type ='OPEN' 
				AND mtr_sts.mtr_sts_disable_flag ='0'";


		$stmt = sqlsrv_query( $conn, $sql);				

		if( !$stmt ) {
			 $error_message = "Error select table (mtr_sts)";
			 returnError($error_message);
			 die( print_r( sqlsrv_errors(), true));
		}

		do {
			 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				$ls_mr_status = $row['mtr_sts_status'];					
			 }
		} while ( sqlsrv_next_result($stmt) );
	}
			
	$sql_insert_mtr_mst = "INSERT mtr_mst 
			(	site_cd ,					mtr_mst_mtr_no ,			mtr_mst_assetno ,				mtr_mst_costcenter ,				
				mtr_mst_account ,			mtr_mst_org_date ,			mtr_mst_req_date ,				mtr_mst_cmpl_date ,
				mtr_mst_wo_no ,				mtr_mst_requester ,			mtr_mst_entered_by ,			mtr_mst_status ,
				mtr_mst_mr_status ,			mtr_mst_email_notification ,mtr_mst_note ,					mtr_mst_tot_cost ,
				mtr_mst_release_for_app ,	mtr_mst_email_requestor ,	mtr_mst_create_by ,				mtr_mst_create_date ,			
				audit_user ,				audit_date ) 
	VALUES 	(	?,							?,							?,								?,		
				?,							getDate(),					getDate(),						Null,							
				?,							?,							?,								'W',						
				'MTR',						?,							Null,							'0',
				?,							?,							?,								getDate(),
				?,							getDate())";
								
								
	$params_mtr_mst = array($site_cd,$mtr_mst_mtr_no,$wko_mst_assetno,$wko_mst_chg_costcenter,$wko_det_maccount,$wko_mst_wo_no,$audit_user,$audit_user,$dft_mst_mtr_email,$dft_mst_mr_release_for_app,$dft_mst_mtr_email_requestor,$LogingID,$LogingID);				
	$stmt = sqlsrv_query( $conn, $sql_insert_mtr_mst,$params_mtr_mst);

	if( !$stmt ) {
		$error_message = "Error insert table (INSERT Table mtr_mst)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}	
	sqlsrv_free_stmt( $stmt);	
		
	
	
	$sql = "SELECT 	RowID FROM 	mtr_mst (NOLOCK)
			WHERE	site_cd = '".$site_cd."'
			AND		mtr_mst_mtr_no = '".$mtr_mst_mtr_no."'";


		$stmt = sqlsrv_query( $conn, $sql);				

		if( !$stmt ) {
			 $error_message = "Error select table (dft_mst)";
			 returnError($error_message);
			 die( print_r( sqlsrv_errors(), true));
		}

		do {
			 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				 
				$ll_RowID = $row['RowID'];	
				
			 }
		} while ( sqlsrv_next_result($stmt) );	
		
		
		
	$sql_insert_mtr_det = "INSERT mtr_det 
									(	 site_cd,		mst_RowID, 		audit_user, 		audit_date )
							VALUES	(	 ?,				?,				?,					GETDATE());";
								
								
	$params_mtr_det = array($site_cd,$ll_RowID,$LogingID);				
	$stmt = sqlsrv_query( $conn, $sql_insert_mtr_det,$params_mtr_det);

	if( !$stmt ) {
		$error_message = "Error insert table (INSERT Table mtr_det)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}	
	sqlsrv_free_stmt( $stmt);	
		
	
	
	$sql_insert_mtr_ls2 = "INSERT mtr_ls2 
					(		site_cd,				mst_RowID ,				mtr_ls2_varchar1,		mtr_ls2_varchar2,		
							mtr_ls2_varchar3,		mtr_ls2_datetime1, 		mtr_ls2_datetime2,		mtr_ls2_numeric1,		
							audit_user,				audit_date ) 
			VALUES  (		?,						?,						?,						'MTR',					
							?,						GETDATE ( ), 			 NULL,					 NULL,					
							?,						GETDATE ( ))";
								
								
	$params_mtr_ls2 = array($site_cd,$ll_RowID,$mtr_mst_mtr_no,$audit_user,$LogingID);							
	$stmt = sqlsrv_query( $conn, $sql_insert_mtr_ls2,$params_mtr_ls2);

	if( !$stmt ) {
		$error_message = "Error insert table (INSERT Table mtr_ls2)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}	
	sqlsrv_free_stmt( $stmt);
	
	
	
	foreach($result->Details as $Detail){
		
		$stockno = $Detail->stockno;
		$stockloc = $Detail->stockloc;
		$description = $Detail->description;
		$uom = $Detail->uom;
		$issueqty = $Detail->issueqty;
		$quantity = $Detail->quantity;
		$chargecostcenter = $Detail->chargecostcenter;
		$chargeaccount = $Detail->chargeaccount;
		$itemcost = $Detail->itemcost;
		
		$ll_row++;
		$ldc_tot_cost = $ldc_tot_cost + ($quantity * $itemcost);
		
		
		
		$sql_insert_mtr_ls1 = "INSERT mtr_ls1 
			(		site_cd ,					mst_RowID ,					mtr_ls1_mtr_lineno ,			mtr_ls1_stockno , 
					mtr_ls1_stk_locn ,			mtr_ls1_mtl_uom ,			mtr_ls1_desc ,					mtr_ls1_status , 
					mtr_ls1_chg_costcenter ,	mtr_ls1_chg_account ,		mtr_ls1_req_qty ,				mtr_ls1_rcv_qty , 
					mtr_ls1_bo_qty ,			mtr_ls1_clear_qty ,			mtr_ls1_item_cost ,				mtr_ls1_po_no , 
					mtr_ls1_po_lineno ,			mtr_ls1_rec_supplier ,		mtr_ls1_supplier_partno ,		audit_user , 
					audit_date )
	VALUES (		? ,							?,							?,								?, 
					? ,							?,							?,								'W' , 
					?,							?,							?,								0, 
					?,							0,							?,								NULL , 
					NULL ,						 NULL ,						NULL ,							?, 
					GetDate( ) ) ";
								
								
				//echo $sql_insert_mtr_ls1;
	$params_mtr_ls1 = array($site_cd,$ll_RowID,$ll_row,$stockno,$stockloc,$uom,$description,$chargecostcenter,$chargeaccount,$quantity,$quantity,$itemcost,$LogingID);			
	$stmt = sqlsrv_query( $conn, $sql_insert_mtr_ls1,$params_mtr_ls1);

	if( !$stmt ) {
		$error_message = "Error insert table (INSERT Table mtr_ls1)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}	
	sqlsrv_free_stmt( $stmt);
	
	
	
	$sql_insert_wko_ls2 = "INSERT INTO wko_ls2 
			(	site_cd,				wko_ls2_assetno,			wko_ls2_activity_code,			wko_ls2_stockno,			wko_ls2_desc, 
				wko_ls2_qty_needed,		wko_ls2_item_cost,			wko_ls2_mr_no,					wko_ls2_mr_lineno,			wko_ls2_rec_supplier, 
				wko_ls2_act_qty,		wko_ls2_act_cost,			wko_ls2_mtl_uom,				wko_ls2_pr_no,				wko_ls2_pr_lineno, 
				wko_ls2_po_no,			wko_ls2_po_lineno,			wko_ls2_craft,					wko_ls2_nonrefpono,			wko_ls2_com_code, 
				wko_ls2_taxable,		wko_ls2_chg_costcenter,		wko_ls2_chg_account,			wko_ls2_stk_locn,			wko_ls2_info_src, 
				wko_ls2_inv_no,			wko_ls2_inv_lineno,			wko_ls2_varchar1,				wko_ls2_varchar2,			wko_ls2_varchar3, 
				wko_ls2_varchar4,		wko_ls2_varchar5,			wko_ls2_numeric1,				wko_ls2_numeric2,			wko_ls2_numeric3, 
				wko_ls2_numeric4,		wko_ls2_numeric5,			wko_ls2_datetime1,				wko_ls2_datetime2,			wko_ls2_datetime3, 
				wko_ls2_datetime4,		wko_ls2_datetime5,			audit_user,						audit_date,					column1, 
				column2,				column3,					column4,						column5,					mst_RowID )
	VALUES (	?,						?,							NULL,							?,							?,
				?,						?,							?,								?,							NULL,
				?,						?,							?,								NULL,						NULL,
				NULL,					NULL,						NULL,							NULL,						NULL,
				'0',					?,							?,								?,							NULL,
				NULL,					NULL,						NULL,							NULL,						NULL,
				NULL,					NULL,						NULL,							NULL,						NULL,
				NULL,					NULL,						NULL,							NULL,						NULL,
				NULL,					NULL,						?,								GetDate( ),					NULL,
				NULL,					NULL,						NULL,							NULL,						?)";
								
								
	$params_wko_ls2 = array($site_cd,$wko_mst_assetno,$stockno,$description,$quantity,$itemcost,$mtr_mst_mtr_no,$ll_row,$issueqty,$issueqty,$uom,$wko_mst_chg_costcenter,$chargeaccount,$stockloc,$LogingID,$wko_RowID);			
	$stmt = sqlsrv_query( $conn, $sql_insert_wko_ls2,$params_wko_ls2);

	if( !$stmt ) {
		$error_message = "Error insert table (INSERT Table wko_ls2)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}	
	sqlsrv_free_stmt( $stmt);
	
		
	}/* 2nd lopp */	
	
	
	$sql = "SELECt 'count' = COUNT (*) 
			FROM	cf_cost_center (NOLOCK),emp_ls3 (NOLOCK)
			WHERE	cf_cost_center.site_cd = emp_ls3.site_cd
			AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter
			AND		emp_ls3.emp_ls3_approver_flag = '1'
			AND		cf_cost_center.site_cd = '".$site_cd."'
			AND		cf_cost_center.costcenter = '".$wko_mst_chg_costcenter."'";


		$stmt = sqlsrv_query( $conn, $sql);				

		if( !$stmt ) {
			 $error_message = "Error select table (cf_cost_center)";
			 returnError($error_message);
			 die( print_r( sqlsrv_errors(), true));
		}

		do {
			 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
				 
				$count = $row['count'];	
				
			 }
		} while ( sqlsrv_next_result($stmt) );
				
		
		if($count >0){
			
			
			$sql = "SELECt cf_cost_center.costcenter,
							cf_cost_center.descs,
							emp_ls3.emp_ls3_empl_id,   
							emp_ls3.emp_ls3_approval_limit,
							emp_ls3.emp_ls3_approver_flag,   
							emp_ls3.emp_ls3_level,
							emp_ls3.RowID 
			FROM		cf_cost_center (NOLOCK),emp_ls3 (NOLOCK)
			WHERE	cf_cost_center.site_cd = emp_ls3.site_cd
			AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter
			AND		emp_ls3.emp_ls3_approver_flag = '1'
			AND		cf_cost_center.site_cd = '".$site_cd."'
			AND		cf_cost_center.costcenter = '".$wko_mst_chg_costcenter."'
			ORDER BY emp_ls3.emp_ls3_level "	;


			$stmt = sqlsrv_query( $conn, $sql);				

			if( !$stmt ) {
				 $error_message = "Error select table (cf_cost_center)";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}

			$json = array();
			
			
			do {
				 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
					 
					$costcenter = $row['costcenter'];	
					$descs = $row['descs'];	
					$emp_ls3_empl_id = $row['emp_ls3_empl_id'];	
					$emp_ls3_approval_limit = $row['emp_ls3_approval_limit'];	
					$emp_ls3_approver_flag = $row['emp_ls3_approver_flag'];	
					$emp_ls3_level = $row['emp_ls3_level'];	
					$RowID = $row['RowID'];				
					
					
					if ($first_row) {
						//echo $first_row;
					 $next_approver = $emp_ls3_empl_id;
					 $first_row = false;
					}
					
					//$next_approver = $emp_ls3_empl_id
					
					$sql_insert_mtr_app = "Insert mtr_app 
									(	site_cd ,				mst_RowID ,							mtr_app_level,				mtr_app_cost_center, 
										mtr_app_empl_id,		mtr_app_mr_limit,					mtr_app_date,				mtr_app_status, 
										mtr_app_approval,		audit_user ,						audit_date )
						Values 		(	?,						?,									?,							?, 
										?,						? ,									NULL ,						'P', 
										NULL,					?,									GetDate () ) ";								
											
					$params_mtr_app = array($site_cd,$ll_RowID,$emp_ls3_level,$wko_mst_chg_costcenter,$emp_ls3_empl_id,$emp_ls3_approval_limit,$LogingID);		
					$stmt2 = sqlsrv_query( $conn, $sql_insert_mtr_app,$params_mtr_app);

					if( !$stmt2 ) {
						$error_message = "Error insert table (INSERT Table mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}	
					sqlsrv_free_stmt( $stmt2);
					
					//$json[]= $row;	
					
					
				 }			 
				 
			} while ( sqlsrv_next_result($stmt) );
			
			
			
				
			
			/* for ($x = 0; $x <= $count; $x++) {
				
					$sql_insert_wko_ls2 = "Insert mtr_app 
									(	site_cd ,				mst_RowID ,							mtr_app_level,				mtr_app_cost_center, 
										mtr_app_empl_id,		mtr_app_mr_limit,					mtr_app_date,				mtr_app_status, 
										mtr_app_approval,		audit_user ,						audit_date )
						Values 		(	'".$site_cd."',			'".$ll_RowID."',					'".$emp_ls3_level."',		'".$wko_mst_chg_costcenter."' , 
										'".$audit_user."',		'".$emp_ls3_approval_limit."' ,		NULL ,			'P', 
										NULL,					'".$audit_user."' ,					GetDate () ) ";								
											
							
					$stmt = sqlsrv_query( $conn, $sql_insert_wko_ls2);

					if( !$stmt ) {
						$error_message = "Error insert table (INSERT Table mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}	
					sqlsrv_free_stmt( $stmt);
				
			  
			} */
			
			$sql = "SELECt 'Max' =MAX(emp_ls3_level) 
			FROM		cf_cost_center (NOLOCK),emp_ls3 (NOLOCK)
			WHERE	cf_cost_center.site_cd = emp_ls3.site_cd
			AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter
			AND		emp_ls3.emp_ls3_approver_flag = '1'
			AND		cf_cost_center.site_cd = '".$site_cd."'
			AND		cf_cost_center.costcenter = '".$wko_mst_chg_costcenter."'";


				$stmt = sqlsrv_query( $conn, $sql);				

				if( !$stmt ) {
					 $error_message = "Error select table (cf_cost_center)";
					 returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
				}

				do {
					 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
						 
						$Max = $row['Max'];	
						
					 }
				} while ( sqlsrv_next_result($stmt) );
					
					$sql = "UPDATE mtr_mst 
							SET mtr_mst_app_level ='".$Max."' , 
								mtr_mst_cur_app_level =0 , 
								mtr_mst_approver ='".$next_approver."' , 
								mtr_mst_tot_cost ='".$ldc_tot_cost."' 
						WHERE 	site_cd = '".$site_cd."' 
						AND 	RowID = '".$ll_RowID."'";			
					$stmt = sqlsrv_query( $conn, $sql);				
									
					if( !$stmt ) {
						 $error_message = "Error update table (mtr_mst)";
						 returnError($error_message);
						 die( print_r( sqlsrv_errors(), true));
					}


		}else{
			
			
			$sql = "UPDATE mtr_mst 
					SET mtr_mst_app_level =0, 
						mtr_mst_cur_app_level =0, 
						mtr_mst_approver =Null, 
						mtr_mst_tot_cost ='".$ldc_tot_cost."' 
				WHERE 	site_cd = '".$site_cd."' 
				AND 	RowID = '".$ll_RowID."'";			
			$stmt = sqlsrv_query( $conn, $sql);				
							
			if( !$stmt ) {
				 $error_message = "Error update table (mtr_mst)";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}

		}
		
			
		$sql = "UPDATE wko_det 
				SET wko_det_est_mtl_cost = wko_det_est_mtl_cost + '".$ldc_tot_cost."', 
				audit_user = '".$LogingID."', 
				audit_date = GetDate () 
				WHERE mst_RowID = '".$wko_RowID."'";	
				
			$stmt = sqlsrv_query( $conn, $sql);				
							
			if( !$stmt ) {
				 $error_message = "Error update table (wko_det)";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}
	
	
	
}/* 1st lopp */


if ($valid) {	
	returnData($mtr_mst_mtr_no,$json);	
}

//sqlsrv_free_stmt( $stmt);

sqlsrv_close($conn);


function returnData($mtr_mst_mtr_no,$json){
	
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'The Material Request No ('.$mtr_mst_mtr_no .') Created Successfully',
	'data' => $json);
	
	echo json_encode($returnData);
}

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