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

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

// Converts it into a PHP object
$data = json_decode($json, true);


$site_cd 		= $data['site_cd'];
$rowid 			= $data['rowid'];
$ast_mst_rowid 	= $data['ast_mst_rowid'];
$meter_id 		= $data['meter_id'];
$asset_no 		= $data['asset_no'];
$asset_desc 	= $data['asset_desc'];
$usage_date 	= $data['usage_date'];
$meter_reading 	= $data['meter_reading'];
$asset_status 	= $data['asset_status'];
$asset_location = $data['asset_location'];
$LogingID 		= $data['LOGINID'];
$EmpID 			= $data['EmpID'];

	$sql_select_meter_id="Select 	ast_ls2_meter_id, 
									ast_ls2_incr_usage_flag, 
									ast_ls2_usage_uom, 
									ast_ls2_usage_reading, 
									ast_ls2_rowid = ast_ls2.rowid,
									ast_ls2_meter_install_date, 
									ast_ls2_meter_maximum, 
									ast_ls2_max_avg_usage,
									ast_ls2_ltd_usage,
									ast_ls2_usage_date,
									ast_mst.ast_mst_asset_status,
									ast_mst.ast_mst_asset_locn,
									compute_usg = CASE When ast_ls2_incr_usage_flag = 'N' Then ".$meter_reading."
										 When ".$meter_reading." - ast_ls2_ltd_usage <= 0 Then 0
										 Else ".$meter_reading." -  ast_ls2_ltd_usage End,
									compute_avg = (CASE When ast_ls2_incr_usage_flag = 'N' Then ".$meter_reading."
										 When ".$meter_reading." - ast_ls2_ltd_usage <= 0 Then 0
										 Else ".$meter_reading." -  ast_ls2_ltd_usage End ) / CASE WHEN (Datediff(day, (SELECT	MAX(ast_usg_usage_date) 
																FROM	ast_usg WHERE site_cd = '".$site_cd."' 
																AND		mst_RowID = ".$ast_mst_rowid." 
																AND		ast_usg_meter_id = '".$meter_id."'), '".$usage_date."')) = 0 Then 1 Else (Datediff(day, (SELECT	MAX(ast_usg_usage_date) 
																FROM	ast_usg WHERE site_cd = '".$site_cd."' 
																AND		mst_RowID = ".$ast_mst_rowid." 
																AND		ast_usg_meter_id = '".$meter_id."'), '".$usage_date."')) END
								From 	ast_mst (NOLOCK), ast_ls2 (NOLOCK)
								Where 	ast_mst.site_cd = ast_ls2.site_cd
								And 	ast_mst.rowid = ast_ls2.mst_rowid
								And 	ast_mst.ast_mst_asset_no = '".$asset_no."'
								And		ast_mst.site_cd = '".$site_cd."'
								And		ast_ls2.ast_ls2_meter_id = '".$meter_id."'
								Order by ast_mst.rowid desc";


	$stmt_select_meter_id = sqlsrv_query( $conn, $sql_select_meter_id);

	if( !$stmt_select_meter_id ) {
		 $error_message = "Error selecting table (ast_mst , ast_ls2 )";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
		 
	}

	do {
		while ($row = sqlsrv_fetch_array($stmt_select_meter_id, SQLSRV_FETCH_ASSOC)) {		
			
			//extract($row_building);
			$ls_meter 				= $row['ast_ls2_meter_id'];
			$ls_inc_flag 			= $row['ast_ls2_incr_usage_flag'];
			$ls_uom 				= $row['ast_ls2_usage_uom'];
			//$ldc_usage 			= $row['ast_ls2_usage_reading'];
			$ll_rowid 				= $row['ast_ls2_rowid'];
			$ldt_install_dt 		= $row['ast_ls2_meter_install_date'];
			$ldc_max_reading 		= $row['ast_ls2_meter_maximum'];
			$ldc_max_avg_reading	= $row['ast_ls2_max_avg_usage'];
			$ldc_cur_reading		= $row['compute_usg'];
			$ldc_cur_avg_reading	= $row['compute_avg'];
			$ldc_ltd_usage 			= $row['ast_ls2_ltd_usage'];
			$ldt_usage_dt 			= $row['ast_ls2_usage_date'];
			$ll_asset_status 		= $row['ast_mst_asset_status'];
			$ll_asset_location		= $row['ast_mst_asset_locn'];
		
		 }
	} while ( sqlsrv_next_result($stmt_select_meter_id) );
	sqlsrv_free_stmt( $stmt_select_meter_id);
	
	$ldc_usage = $meter_reading;
	$ldc_new_ltd_usage 		= $ldc_ltd_usage + $ldc_usage;
	
	//Select Max usage and Reading
	$sql_select_max="Select max_usage_date = Max(ast_usg_usage_date),
								 max_reading = Max(ast_usg_usage_reading)
							From 	ast_usg (NOLOCK)
							Where 	ast_usg.site_cd = '".$site_cd."'
							And		ast_usg.mst_rowid = '".$ll_rowid."'
							And		ast_usg.ast_usg_meter_id = '".$ls_meter."'";


	$stmt_select_max = sqlsrv_query( $conn, $sql_select_max);

	if( !$stmt_select_max ) {
		 $error_message = "Error selecting table (max_usage_date)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
		 
	}

	do {
		while ($row = sqlsrv_fetch_array($stmt_select_max, SQLSRV_FETCH_ASSOC)) {

			
			$ldt_prev_usage_dt 			= $row['max_usage_date'];
			$ldc_meter_ltd_usage 		= $row['max_reading'];			
		
		 }
	} while ( sqlsrv_next_result($stmt_select_max) );
	sqlsrv_free_stmt($stmt_select_max);
	
	If ($ldc_meter_ltd_usage > $ldc_ltd_usage) {
		
		$ldc_meter_ltd_usage = $ldc_ltd_usage;
	}
	
	If ($ls_inc_flag == "Y" ){
		
		$sql_update_meter_ltd_usage= "UPDATE 	ast_ls2
				SET		ast_ls2_usage_date = '".$usage_date."',
						ast_ls2_usage_reading = '".$ldc_usage."',
						ast_ls2_ltd_usage = '".$ldc_usage."',
						audit_user = '".$LogingID."'
				Where	ast_ls2.site_cd = '".$site_cd."'	
				and 	ast_ls2.rowid = '".$ll_rowid."'";

		$stmt_update_meter_ltd_usage = sqlsrv_query( $conn, $sql_update_meter_ltd_usage);

		if( !$stmt_update_meter_ltd_usage ) {
			$error_message = "Error insert table (UPDATE Table update_meter_ltd_usage)";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		}
		sqlsrv_free_stmt( $stmt_update_meter_ltd_usage);
		
		
		$ldc_new_ltd_usage = $ldc_usage;
		
	}elseIf ($ls_inc_flag == "N"){
		
		
		$sql_update_meter_ltd_usage= "UPDATE 	ast_ls2
										SET		ast_ls2_usage_date = '".$usage_date."',
												ast_ls2_usage_reading = '".$ldc_usage."',
												ast_ls2_ltd_usage = '".$ldc_usage."',
												audit_user = '".$LogingID."'
										Where	ast_ls2.site_cd = '".$site_cd."'	
										and 	ast_ls2.rowid = '".$ll_rowid."'";

		$stmt_update_meter_ltd_usage = sqlsrv_query( $conn, $sql_update_meter_ltd_usage);

		if( !$stmt_update_meter_ltd_usage ) {
			$error_message = "Error insert table (UPDATE Table update_meter_ltd_usage)";
			returnError($error_message);
			die( print_r( sqlsrv_errors(), true));
		}
		sqlsrv_free_stmt( $stmt_update_meter_ltd_usage);
		
		$ldc_ltd_usage = 0;
		$ldc_new_ltd_usage = $ldc_usage;
		
	}
	
	If (empty($ldc_ltd_usage) || !isset($ldc_ltd_usage)){
		$ldc_ltd_usage = 0;
	}
	
	If (!isset($ldc_ltd_usage) || empty($ldc_ltd_usage)){
		$ldc_ltd_usage = 0;
	}
	
	//Insert ast_usg
	$sql_insert_ast_usg = "INSERT ast_usg ( site_cd, 			mst_RowID, 			ast_usg_meter_id, 		ast_usg_usage_reading, 
											ast_usg_usage_uom, 	ast_usg_usage_date, ast_usg_org_ltd_usage, 	ast_usg_new_ltd_usage, 
											audit_user, 		audit_date ) 
									VALUES (?,					?,					?,						?,
											?,					?,					?,						?,
											?,					getdate() )";
											
	$params_insert_ast_usgt = array($site_cd,$ll_rowid,$ls_meter,$ldc_usage,$ls_uom,$usage_date,$ldc_ltd_usage,$ldc_new_ltd_usage,$LogingID);						
	$stmt_insert_ast_usgt = sqlsrv_query( $conn, $sql_insert_ast_usg,$params_insert_ast_usgt);	
	if( !$stmt_insert_ast_usgt ) {
		$error_message = "Error insert table (INSERT Table ast_usg)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}	
	sqlsrv_free_stmt($stmt_insert_ast_usgt);
	
	
	$sql_select_avg="SELECT avg_reading = (Max(ast_ls2.ast_ls2_ltd_usage) - MAX(ast_ls2.ast_ls2_old_ltd_usage)) / 
											   (DateDiff(Day,MAX(ast_ls2.ast_ls2_meter_install_date), Max(ast_usg.ast_usg_usage_date) + 1 ))
						From 	ast_ls2 (NOLOCK), ast_usg (NOLOCK)
						Where 	ast_ls2.site_cd = '".$site_cd."'
						And		ast_ls2.rowid = '".$ll_rowid."'
						And 	ast_ls2.rowid = ast_usg.mst_rowid";


	$stmt_select_avg = sqlsrv_query( $conn, $sql_select_avg);

	if( !$stmt_select_avg ) {
		 $error_message = "Error selecting table (select_avg )";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
		 
	}

	do {
		while ($row = sqlsrv_fetch_array($stmt_select_avg, SQLSRV_FETCH_ASSOC)) {		
			
			$ldc_avg_reading = $row['avg_reading'];
					
		 }
	} while ( sqlsrv_next_result($stmt_select_avg) );
	sqlsrv_free_stmt( $stmt_select_avg);
	
	//Update average int ast_ls2
	$sql_update_ast_ls2_avg = "UPDATE ast_ls2 SET ast_ls2_avg_usage = '".$ldc_avg_reading."' Where RowID = '".$ll_rowid."'";
	$stmt_update_ast_ls2_avg = sqlsrv_query( $conn, $sql_update_ast_ls2_avg);

	if( !$stmt_update_ast_ls2_avg ) {
		$error_message = "Error insert table (UPDATE Table update_ast_ls2_avg)";
		returnError($error_message);
		die( print_r( sqlsrv_errors(), true));
	}
	sqlsrv_free_stmt( $stmt_update_ast_ls2_avg);
	
	
	if($ll_asset_status !=  $asset_status){
		
		$sql = "UPDATE	ast_aud 
		SET			ast_aud_end_date = GetDate(),
					ast_aud_duration = 0,
					audit_user = ? ,
					audit_date = getdate(),
					mbl_audit_user = ?,
					mbl_audit_date = getdate()
					
		WHERE		ast_aud_end_date IS NULL
		AND			site_cd = ?
		AND			ast_aud_asset_no = ?
		AND			ast_aud_status = ?
		AND			mst_RowID = ?";

		$params_update_ast_aud = array($LogingID,$LogingID,$site_cd,$asset_no,$ll_asset_status,$ast_mst_rowid);
		
		$stmt_update_ast_aud = sqlsrv_query($conn, $sql, $params_update_ast_aud);

		if ($stmt_update_ast_aud === 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 updating table (ast_aud)";
			returnError($error_message);		
		}
		sqlsrv_free_stmt($stmt_update_ast_aud);

		$sql_insert_ast_aud = "INSERT INTO ast_aud
							(	site_cd ,					mst_RowID ,				ast_aud_asset_no ,			ast_aud_status ,	ast_aud_originator , 
								ast_aud_start_date ,		ast_aud_end_date ,		ast_aud_duration ,			audit_user ,		audit_date, 
								mbl_audit_user,				mbl_audit_date) 												
					VALUES	(	?,							?,						?,							?,					?,
								GetDate(),					NULL,					NULL,						?,					GetDate(),
								?,							GetDate())";

		$params_insert_ast_aud = array( $site_cd,$ast_mst_rowid,$asset_no,$asset_status,$EmpID,$LogingID,$LogingID);
		$stmt_insert_ast_aud = sqlsrv_query($conn, $sql_insert_ast_aud, $params_insert_ast_aud);

		if ($stmt_insert_ast_aud === false)
		{
			if (($errors = sqlsrv_errors()) != null)
			{
				foreach ($errors as $error)
				{
					file_put_contents($file_name, $now . "\r\n" . $sql_insert_ast_aud . "\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_aud)";
			returnError($error_message);
		}
		sqlsrv_free_stmt($stmt_insert_ast_aud);
		
		$sql = "UPDATE ast_mst 
				SET ast_mst_asset_locn			= ?,
					ast_mst_asset_status		= ?,
					ast_mst.audit_user 			= ?,
					ast_mst.audit_date 			= GETDATE(),
					ast_mst.mbl_audit_user		=?,
					ast_mst.mbl_audit_date		= GETDATE()
				WHERE site_cd =?
				AND ast_mst_asset_no =? 
				AND RowID = ?";

		$params_ast_mst = array($asset_location,$asset_status,$LogingID,$LogingID,$site_cd,$asset_no,$ast_mst_rowid);
		$stmt_ast_mst = sqlsrv_query($conn, $sql, $params_ast_mst);

		if ($stmt_ast_mst === 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);
				}
			}
			
			$error_message = "(".$now.") Error updating table (ast_mst)";
			returnError($error_message);
			
			sqlsrv_rollback($conn);
			sqlsrv_close($conn);
		}
		sqlsrv_free_stmt($stmt_ast_mst);
		
	}
	
	if ($ll_asset_location != $asset_location)
	{

		$sql_insert_ast_aud = "INSERT INTO ast_loc_s
							( 	site_cd,				ast_loc_s_asset_no,		ast_loc_s_asset_olocn,		ast_loc_s_asset_nlocn,				ast_loc_s_asset_reason, 
								audit_user,				audit_date,				column1,					column2,							column3, 
								column4, 				column5,				mbl_audit_user,				mbl_audit_date  )											
					VALUES	(	?,						?,						?,							?,									?,
								?,						GETDATE(),				NULL,						NULL,								NULL,
								NULL,					NULL,					?,							GETDATE())";

		$params_ast_loc_s = array(  $site_cd,$asset_no,$ll_asset_location,$asset_location,$Loc_remake,$LogingID,$LogingID);
		$stmt_ast_loc_s = sqlsrv_query($conn, $sql_insert_ast_aud, $params_ast_loc_s);

		if ($stmt_ast_loc_s === false)
		{
			if (($errors = sqlsrv_errors()) != null)
			{
				foreach ($errors as $error)
				{
					file_put_contents($file_name, $now . "\r\n" . $sql_insert_ast_aud . "\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_loc_s)";
			returnError($error_message);
		}
		sqlsrv_free_stmt($stmt_ast_loc_s);
		
		
		$sql = "UPDATE ast_mst 
				SET ast_mst_asset_locn			= ?,
					ast_mst_asset_status		= ?,
					ast_mst.audit_user 			= ?,
					ast_mst.audit_date 			= GETDATE(),
					ast_mst.mbl_audit_user		=?,
					ast_mst.mbl_audit_date		= GETDATE()
				WHERE site_cd =?
				AND ast_mst_asset_no =? 
				AND RowID = ?";

		$params_ast_mst = array($asset_location,$asset_status,$LogingID,$LogingID,$site_cd,$asset_no,$ast_mst_rowid);
		$stmt_ast_mst = sqlsrv_query($conn, $sql, $params_ast_mst);

		if ($stmt_ast_mst === 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);
				}
			}
			
			$error_message = "(".$now.") Error updating table (ast_mst)";
			returnError($error_message);
			
			sqlsrv_rollback($conn);
			sqlsrv_close($conn);
		}
		sqlsrv_free_stmt($stmt_ast_mst);

	}
	
	
 
 if($stmt_select_meter_id &&  $stmt_select_max && $stmt_update_meter_ltd_usage && $stmt_insert_ast_usgt && $stmt_select_avg && $stmt_update_ast_ls2_avg){
	 
	 sqlsrv_commit( $conn );
	 sqlsrv_close( $conn);	
	 returnData();
	 
 }else{
	sqlsrv_rollback( $conn );
	$error_message = "Transaction rolled back.<br />";
	returnError($error_message);
 }
 

 
function returnData(){
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'The Meter Reading successfully saved.');
	echo json_encode($returnData);
}

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



 
 
?>