<?php
// get these values from your DB.
require_once('config.php');
$error_message;
$valid = true;
$now = date("Ymdhisa");
$file_name = __DIR__ . "\mobile_log\_insert_time_card.log";

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

foreach($result->Header as $Heade){
	
	
	$site_cd= $Heade->site_cd;
	$ast_rcb_mst_war= $Heade->ast_rcb_mst_war;
	$ast_rcb_ast_lvl= $Heade->ast_rcb_ast_lvl;
	$ast_rcb_ast_loc= $Heade->ast_rcb_ast_loc;
	
	$ast_rcb_ast_total= $Heade->ast_rcb_ast_total;
	$ast_rcb_ast_scan= $Heade->ast_rcb_ast_scan;
	$ast_rcb_ast_in_loc= $Heade->ast_rcb_ast_in_loc;
	$ast_rcb_ast_not_loc= $Heade->ast_rcb_ast_not_loc;
	$ast_rcb_ast_not_system= $Heade->ast_rcb_ast_not_system;
	
	$LogingID= $Heade->LoginID;
	
	
	$sql = "SELECT 	RowID 
			FROM 	ast_rcb 
			where 	site_cd = '".$site_cd."' 
			and 	ast_rcb_mst_war ='".$ast_rcb_mst_war."' 
			and 	ast_rcb_ast_lvl='".$ast_rcb_ast_lvl."' 
			and 	ast_rcb_ast_loc ='".$ast_rcb_ast_loc."'
			and 	ast_rcb_active_flag ='1'";

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

        if ($stmt === 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 = "(".$now.") Error selecting and updating table (cnt_mst)";
            returnError($error_message);
        }
        do
        {
            while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
            {

                $RowID = $row['RowID'];
                
            }
        }
        while (sqlsrv_next_result($stmt));
		
		if(empty($RowID)){
			
			
			$sql_insert_ast_rcb = "INSERT ast_rcb 
	
				(	site_cd ,					ast_rcb_desc ,				ast_rcb_mst_war ,			ast_rcb_ast_lvl ,			ast_rcb_ast_loc ,
					ast_rcb_active_flag , 		ast_rcb_date ,				ast_rcb_ast_total ,			ast_rcb_ast_scan ,			ast_rcb_ast_in_loc ,				
					ast_rcb_ast_not_loc ,		ast_rcb_ast_not_system, 	ast_rcb_completed ,			ast_rcb_completed_by ,		audit_user ,		
					audit_date ,				ast_rcb_create_by,			ast_rcb_create_date,		mbl_audit_user,				mbl_audit_date
				)
					
			VALUES (?,							Null,						?,							?,							?,				
					?,							GetDate(),					?,							?,							?,				
					?,							?,							Null,						Null,						?,		
					GetDate(),					?,							GetDate(),					?,							GetDate()					
					)";

				$params = array($site_cd,$ast_rcb_mst_war,$ast_rcb_ast_lvl,$ast_rcb_ast_loc,'1',$ast_rcb_ast_total,$ast_rcb_ast_scan,$ast_rcb_ast_in_loc,$ast_rcb_ast_not_loc,$ast_rcb_ast_not_system,$LogingID,$LogingID,$LogingID);
				$stmt = sqlsrv_query($conn, $sql_insert_ast_rcb,$params);

				if ($stmt === false)
				{
					if (($errors = sqlsrv_errors()) != null)
					{
						foreach ($errors as $error)
						{

							file_put_contents($file_name, $now . "\r\n" . $sql_insert_ast_rcb . "\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 = "(".$now.") Error inserting table (ast_rcb)";
					returnError($error_message);
				}
				sqlsrv_free_stmt($stmt);
				
				$sql = "SELECT 	RowID 
						FROM 	ast_rcb 
						where 	site_cd = '".$site_cd."' 
						and 	ast_rcb_mst_war ='".$ast_rcb_mst_war."' 
						and 	ast_rcb_ast_lvl='".$ast_rcb_ast_lvl."' 
						and 	ast_rcb_ast_loc ='".$ast_rcb_ast_loc."'
						and 	ast_rcb_active_flag ='1'";

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

				if ($stmt === 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 = "(".$now.") Error selecting and updating table (cnt_mst)";
					returnError($error_message);
				}
				do
				{
					while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
					{

						$RowID = $row['RowID'];
						
					}
				}
				while (sqlsrv_next_result($stmt));
		
		}else{
			
			
			$sql = "SELECT 	RowID 
					FROM 	ast_rcb 
					where 	site_cd = '".$site_cd."' 
					and 	ast_rcb_mst_war ='".$ast_rcb_mst_war."' 
					and 	ast_rcb_ast_lvl='".$ast_rcb_ast_lvl."' 
					and 	ast_rcb_ast_loc ='".$ast_rcb_ast_loc."'
					and 	ast_rcb_active_flag ='1'";

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

			if ($stmt === 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 = "(".$now.") Error selecting and updating table (cnt_mst)";
				returnError($error_message);
			}
			do
			{
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
				{

					$RowID = $row['RowID'];
					
				}
			}
			while (sqlsrv_next_result($stmt));
			
		}
	
	
	foreach($result->Details as $Detail){
		
		$ast_rcf_asset_no = $Detail->ast_rcf_asset_no;
		$ast_rcf_scan_date = $Detail->ast_rcf_scan_date;
		
		$matched = false;
		$out_loc_matched = false;
		
		$sql = "Select  * FROM 	ast_mst (NOLOCK) 
				LEFT 
				OUTER 
				JOIN	mst_war (NOLOCK)
				ON		ast_mst.site_cd = mst_war.site_cd
				AND		ast_mst.ast_mst_work_area = mst_war.mst_war_work_area

				LEFT 
				OUTER 
				JOIN	ast_loc (NOLOCK)
				ON		ast_mst.site_cd = ast_loc.site_cd
				AND		ast_mst.ast_mst_asset_locn = ast_loc.ast_loc_ast_loc

				LEFT 
				OUTER 
				JOIN	ast_lvl (NOLOCK)
				ON		ast_mst.site_cd = ast_lvl.site_cd
				AND		ast_mst.ast_mst_ast_lvl = ast_lvl.ast_lvl_ast_lvl 
				WHERE  ast_mst.site_cd ='".$site_cd."' 
				AND ast_mst_work_area ='".$ast_rcb_mst_war."' 
				and ast_mst_ast_lvl='".$ast_rcb_ast_lvl."' 
				and ast_mst_asset_locn ='".$ast_rcb_ast_loc."'
				and ast_mst_asset_no ='".$ast_rcf_asset_no."'";

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

		if ($stmt === 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 = "(".$now.") Error selecting and updating table (ast_mst)";
			returnError($error_message);
		}
		do
		{
			while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
			{
				$matched = true;
				
			}
		}
		while (sqlsrv_next_result($stmt));
		
		$ast_rcf_asset_status;
		
		if ($matched) {
			$ast_rcf_asset_status = 'IN LOC';
		}else{
			
			
			$sql = "Select  * FROM 	ast_mst (NOLOCK) 
				LEFT 
				OUTER 
				JOIN	mst_war (NOLOCK)
				ON		ast_mst.site_cd = mst_war.site_cd
				AND		ast_mst.ast_mst_work_area = mst_war.mst_war_work_area

				LEFT 
				OUTER 
				JOIN	ast_loc (NOLOCK)
				ON		ast_mst.site_cd = ast_loc.site_cd
				AND		ast_mst.ast_mst_asset_locn = ast_loc.ast_loc_ast_loc

				LEFT 
				OUTER 
				JOIN	ast_lvl (NOLOCK)
				ON		ast_mst.site_cd = ast_lvl.site_cd
				AND		ast_mst.ast_mst_ast_lvl = ast_lvl.ast_lvl_ast_lvl 
				WHERE  ast_mst.site_cd ='".$site_cd."' 
				and ast_mst_asset_no ='".$ast_rcf_asset_no."'";

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

			if ($stmt === 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 = "(".$now.") Error selecting and updating table (ast_mst)";
				returnError($error_message);
			}
			do
			{
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
				{
					$out_loc_matched = true;
					
				}
			}
			while (sqlsrv_next_result($stmt));
			
			
			if ($out_loc_matched) {
				$ast_rcf_asset_status = 'OUT LOC';
			}else{
				$ast_rcf_asset_status = 'NOT FOUND';
			}
			
			
		}
		
		$ast_rcf_RowID = '';
		
		$sql = "SELECT RowID FROM ast_rcf where site_cd = '".$site_cd."' and ast_rcf_asset_no ='".$ast_rcf_asset_no."' and mst_RowID='".$RowID."'";

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

			if ($stmt === 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 = "(".$now.") Error selecting and updating table (cnt_mst)";
				returnError($error_message);
			}
			do
			{
				while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
				{

					$ast_rcf_RowID = $row['RowID'];
					
				}
			}
			while (sqlsrv_next_result($stmt));
			
			
			if(empty($ast_rcf_RowID)){
				
				$sql_insert_ast_rcf = "INSERT ast_rcf 
	
						( 	site_cd,	mst_RowID,	ast_rcf_asset_no,	ast_rcf_scan_date,	ast_rcf_asset_status, audit_user, audit_date )
					
				VALUES 	(	?,			?,			?,					?,					?,						?,			GetDate() )";

				$params = array($site_cd,$RowID,$ast_rcf_asset_no,$ast_rcf_scan_date,$ast_rcf_asset_status,$LogingID);
				$stmt = sqlsrv_query($conn, $sql_insert_ast_rcf,$params);

				if ($stmt === false)
				{
					if (($errors = sqlsrv_errors()) != null)
					{
						foreach ($errors as $error)
						{

							file_put_contents($file_name, $now . "\r\n" . $sql_insert_ast_rcf . "\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 = "(".$now.") Error inserting table (ast_rcf)";
					returnError($error_message);
				}
				sqlsrv_free_stmt($stmt);
				
			}else{
				
				
				$sql = "UPDATE ast_rcf 
					SET 	ast_rcf_asset_no = ?, 
							ast_rcf_scan_date = ?, 
							ast_rcf_asset_status = ?, 
							audit_user = ?,
							audit_date =  GetDate () 
							
					WHERE 	site_cd = ? 
					AND 	RowID = ?";

				$params_ast_rcf = array($ast_rcf_asset_no,$ast_rcf_scan_date,$ast_rcf_asset_status,$LogingID,$site_cd,$ast_rcf_RowID);								
				$stmt = sqlsrv_query( $conn, $sql,$params_ast_rcf);				
								
				if( !$stmt ) {
					 $error_message = "Error update table (ast_rcf)";
					 returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
				}
				
				
				
			}
		
	}
	
	$sql_1_cnt="select 	site_cd,mst_RowID,ast_rcf_asset_no,ast_rcf_scan_date,ast_rcf_asset_status FROM 	ast_rcf	WHERE	site_cd = '".$site_cd."' AND 	mst_RowID = '".$RowID."'";
	
	$stmt_1_cnt = sqlsrv_query( $conn, $sql_1_cnt);

	if( !$stmt_1_cnt ) {
		 $error_message = "Error selecting table count Preventive";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
		 
	}
	$json= array();
	do {
		 while ($row = sqlsrv_fetch_array($stmt_1_cnt, SQLSRV_FETCH_ASSOC)) {		
			$json[] = $row;		
		
		 }
	} while ( sqlsrv_next_result($stmt_1_cnt) );
	
	$sql_1_cnt="select 	ast_rcb_ast_in_loc = COALESCE(SUM(ast_rcb_ast_in_loc),'' ),
				ast_rcb_ast_not_loc = COALESCE(SUM(ast_rcb_ast_not_loc),'' ),
				ast_rcb_ast_scan = COALESCE(SUM(ast_rcb_ast_scan),'' ),
				ast_rcb_ast_not_system =COALESCE(SUM(ast_rcb_ast_not_system),'' )
		FROM (
				SELECT ast_rcb_ast_in_loc = SUM(CASE ast_rcf.ast_rcf_asset_status WHEN 'IN LOC' THEN 1 ELSE 0 END),
						ast_rcb_ast_not_loc = SUM(CASE ast_rcf.ast_rcf_asset_status WHEN 'OUT LOC' THEN 1 ELSE 0 END),
						ast_rcb_ast_not_system = SUM(CASE ast_rcf.ast_rcf_asset_status WHEN 'NOT FOUND' THEN 1 ELSE 0 END),
						ast_rcb_ast_scan = COUNT(*)
				FROM ast_rcf (NOLOCK) 
					
							
							
		WHERE	site_cd = '".$site_cd."'
		AND 	mst_RowID = '".$RowID." ')	as x";

	$stmt_1_cnt = sqlsrv_query( $conn, $sql_1_cnt);

	if( !$stmt_1_cnt ) {
		 $error_message = "Error selecting table count Preventive";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
		 
	}
	do {
		 while ($row = sqlsrv_fetch_array($stmt_1_cnt, SQLSRV_FETCH_ASSOC)) {		
			$ast_rcb_ast_in_loc = $row['ast_rcb_ast_in_loc'];
			$ast_rcb_ast_not_loc = $row['ast_rcb_ast_not_loc'];
			$ast_rcb_ast_not_system = $row['ast_rcb_ast_not_system'];
			$ast_rcb_ast_scan = $row['ast_rcb_ast_scan'];
		
		 }
	} while ( sqlsrv_next_result($stmt_1_cnt) );
	
	$sql = "UPDATE ast_rcb 
			SET 	ast_rcb_ast_in_loc = ?, 
					ast_rcb_ast_not_loc = ?, 
					ast_rcb_ast_not_system = ?, 
					ast_rcb_ast_scan = ?, 
					audit_user = ?,
					audit_date =  GetDate () 
					
			WHERE 	site_cd = ? 
			AND 	RowID = ?";

	$params_ast_rcf = array($ast_rcb_ast_in_loc,$ast_rcb_ast_not_loc,$ast_rcb_ast_not_system,$ast_rcb_ast_scan,$LogingID,$site_cd,$RowID);								
	$stmt = sqlsrv_query( $conn, $sql,$params_ast_rcf);				
					
	if( !$stmt ) {
		 $error_message = "Error update table (ast_rcf)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
	}
}



if ($valid) {

	returnData($json,$ast_rcb_ast_in_loc,$ast_rcb_ast_not_loc,$ast_rcb_ast_not_system,$ast_rcb_ast_scan);	
	
}



function returnData($json,$ast_rcb_ast_in_loc,$ast_rcb_ast_not_loc,$ast_rcb_ast_not_system,$ast_rcb_ast_scan){
	$json1 = json_encode($json);
	//echo$json;
	 
	if(empty(json_decode($json1,1))) {
		 $returnData = array(
		'status' => 'SUCCESS',
		'message' => 'No records found',
		'ast_rcb_ast_in_loc'=>$ast_rcb_ast_in_loc,
		'ast_rcb_ast_not_loc'=>$ast_rcb_ast_not_loc,
		'ast_rcb_ast_not_system'=>$ast_rcb_ast_not_system,
		'ast_rcb_ast_scan'=>$ast_rcb_ast_scan,
		'data' => $json);
		
	}else{
		
		$returnData = array(
		'status' => 'SUCCESS',
		'message' => 'Vadlidation Successfully',
		'ast_rcb_ast_in_loc'=>$ast_rcb_ast_in_loc,
		'ast_rcb_ast_not_loc'=>$ast_rcb_ast_not_loc,
		'ast_rcb_ast_not_system'=>$ast_rcb_ast_not_system,
		'ast_rcb_ast_scan'=>$ast_rcb_ast_scan,
		'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);
		
}
	
 
?>