<?php
// get these values from your DB.
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');
//to debug the json values;
//file_put_contents("data.json", $json)
// Converts it into a PHP object
$data = json_decode($json, true);

$site_cd = $data['site_cd'];
$grp_ast_grp_cd=$data['grp_ast_grp_cd'];


$Active =0;
$Deactivate=0;
$Disposed=0;




if (!empty($grp_ast_grp_cd)){	
	$where_grp_ast_grp_cd = " AND grp_ast.grp_ast_grp_cd = '".$grp_ast_grp_cd."' ";	
}else{	
	$where_grp_ast_grp_cd = "";	
}


$sql="Select  	ast_mst.site_cd,
				ast_mst.RowID, 
				ast_mst_wrk_grp			= COALESCE(ast_mst_wrk_grp + ' : ' + wrk_grp.wrk_grp_desc,''),	
				ast_mst_asset_no		= COALESCE(ast_mst_asset_no,''),
				ast_mst_asset_group 	= COALESCE(ast_mst_asset_grpcode + ' : ' + ast_grp_desc,''),				
				ast_mst_asset_shortdesc	= COALESCE(ast_mst_asset_shortdesc,''),
				ast_mst_asset_longdesc	= COALESCE(ast_mst_asset_longdesc,''),
				ast_mst_asset_grpcode	= COALESCE(ast_mst_asset_grpcode+ ' : ' + ast_grp_desc,''),
				ast_mst_perm_id 		= COALESCE(ast_mst_perm_id,''),
				ast_det_cus_code 		= COALESCE(ast_det_cus_code,''),				
				ast_mst_asset_type 		= COALESCE(ast_mst_asset_type + ' : ' + ast_type_descs,''),				
				ast_mst_asset_code		= COALESCE(ast_mst_asset_code+ ' : ' + ast_cod_ast_cd,''),				
				mst_war_work_area 		= COALESCE(mst_war_work_area + ' : ' + mst_war_desc,''),				
				ast_mst_asset_locn 		= COALESCE(ast_loc_ast_loc + ' : ' + ast_loc_desc,''),				
				ast_mst_asset_lvl 		= COALESCE(ast_lvl_ast_lvl + ' : ' + ast_lvl_desc,''),				
				ast_mst_asset_status 	= COALESCE(ast_sts_status + ' : ' + ast_sts_desc,''),				
				ast_mst_cri_factor 		= COALESCE(ast_cri_cri_factor + ' : ' + ast_cri_desc,''),				
				ast_mst_cost_center 	= COALESCE(costcenter + ' : ' + descs,''),
				ast_det_warranty_date	= COALESCE(ast_det_warranty_date,''),
				grp_ast_grp_cd	= COALESCE(grp_ast.grp_ast_grp_cd,'')
				
				
				
								
				FROM 	ast_mst (NOLOCK) 

							INNER JOIN		ast_det (NOLOCK)
							ON				ast_mst.site_cd = ast_det.site_cd
							AND				ast_mst.rowid = ast_det.mst_rowid
					
							INNER JOIN		ast_sts (NOLOCK)
							ON				ast_mst.site_cd = ast_sts.site_cd
							AND				ast_mst.ast_mst_asset_status = ast_sts.ast_sts_status

							INNER JOIN		grp_ast (NOLOCK)
							ON				ast_mst.site_cd = grp_ast.site_cd
							AND				ast_mst.ast_mst_asset_no = grp_ast.grp_ast_asset_no

							LEFT 
							OUTER 
							JOIN			ast_grp (NOLOCK)
							ON				ast_mst.site_cd = ast_grp.site_cd
							AND				ast_mst.ast_mst_asset_grpcode = ast_grp.ast_grp_grp_cd


							LEFT 
							OUTER 
							JOIN			ast_type (NOLOCK)
							ON				ast_mst.site_cd = ast_type.site_cd
							AND				ast_mst.ast_mst_asset_type = ast_type.ast_type_cd

							LEFT 
							OUTER 
							JOIN			ast_cod (NOLOCK)
							ON				ast_mst.site_cd = ast_cod.site_cd
							AND				ast_mst.ast_mst_asset_code = ast_cod.ast_cod_ast_cd


							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


							LEFT 
							OUTER 
							JOIN			ast_cri (NOLOCK)
							ON				ast_mst.site_cd = ast_cri.site_cd
							AND				ast_mst.ast_mst_cri_factor = ast_cri.ast_cri_cri_factor


							LEFT 
							OUTER 
							JOIN			cf_cost_center (NOLOCK)
							ON				ast_mst.site_cd = cf_cost_center.site_cd
							AND				ast_mst.ast_mst_cost_center = cf_cost_center.costcenter
							
							
							
							LEFT 
							OUTER 
							JOIN		wrk_grp (NOLOCK)
							ON			ast_mst.site_cd = wrk_grp.site_cd
							AND			ast_mst.ast_mst_wrk_grp = wrk_grp.wrk_grp_grp_cd

				

			WHERE  			(ast_mst.site_cd = '".$site_cd."' )".
							$where_grp_ast_grp_cd."  ORDER BY ast_mst_asset_no , ast_mst_asset_shortdesc ";


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

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

				$json = array();

				do {
					 while ($row = sqlsrv_fetch_array($stmt_asset_list, SQLSRV_FETCH_ASSOC)) {		
						$json[] = $row;	
					
					 }
				} while ( sqlsrv_next_result($stmt_asset_list) );


				
$sql="SELECT	Active = COALESCE(SUM(Active),'' ), Deactivate = COALESCE(SUM(Deactivate),'' ), Disposed = COALESCE(SUM(Disposed),'' )
	  From (	SELECT 		Active 		= SUM(CASE ast_sts.ast_sts_typ_cd WHEN 'IN-SERVICE' THEN 1 ELSE 0 END),
							Deactivate	= SUM(CASE ast_sts.ast_sts_typ_cd WHEN 'OUT-OF-SERVICE' THEN 1 ELSE 0 END),
							Disposed 	= SUM(CASE ast_sts.ast_sts_typ_cd WHEN 'AWA-DISPOSED' THEN 1 WHEN 'DISPOSED' THEN 1 ELSE 0 END)
		FROM 		ast_mst (NOLOCK)

						INNER JOIN		ast_sts (NOLOCK)
						ON				ast_mst.site_cd = ast_sts.site_cd
						AND				ast_mst.ast_mst_asset_status = ast_sts.ast_sts_status

						INNER JOIN		ast_det (NOLOCK)
						ON				ast_mst.site_cd = ast_det.site_cd
						AND				ast_mst.rowid = ast_det.mst_rowid

						INNER JOIN		grp_ast (NOLOCK)
						ON				ast_mst.site_cd = grp_ast.site_cd
						AND				ast_mst.ast_mst_asset_no = grp_ast.grp_ast_asset_no

						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
			
			WHERE  			(ast_mst.site_cd = '".$site_cd."')".
							$where_grp_ast_grp_cd. ") as x" ;




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

if( !$stmt_count ) {
     $error_message = "Error selecting table (ast_mst)";
	 returnError($error_message);
     die( print_r( sqlsrv_errors(), true));
	 
}
do {
     while ($row = sqlsrv_fetch_array($stmt_count, SQLSRV_FETCH_ASSOC)) {		
		$Active = $row['Active'];
		$Deactivate = $row['Deactivate'];
		$Disposed = $row['Disposed'];
	
     }
} while ( sqlsrv_next_result($stmt_count) );


if( $stmt_asset_list && $stmt_count) {
		 sqlsrv_commit( $conn );
		 sqlsrv_close( $conn);	
		 returnData($json,$Active,$Deactivate,$Disposed);	
	} else {
		 sqlsrv_rollback( $conn );
		 $error_message = "Transaction rolled back.<br />";
		 returnError($error_message);
	} 



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

 
?>