<?php


header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header(
    "Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With"
);

// get these values from your DB.
require_once('config.php');
require_once('f_get_emp_ls5.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'];

$ast_mst_asset_no=$data['ast_mst_asset_no'];
$asset_shortdesc= $data['asset_shortdesc'];
$cost_center= $data['cost_center'];
$asset_status= $data['asset_status'];
$asset_type= $data['asset_type'];
$asset_grpcode= $data['asset_grpcode'];
$work_area= $data['work_area'];
$asset_locn= $data['asset_locn'];
$asset_code=$data['asset_code'];
$ast_lvl= $data['ast_lvl'];
$ast_sts_typ_cd =$data['ast_sts_typ_cd'];
$createby=$data['createby'];
$from_date=$data['from_date'];
$to_date=$data['to_date'];
$emp_det_work_grp=$data['emp_det_work_grp'];
$emp_id=$data['emp_id'];
$type=$data['type'];

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


if($type =='ScanAsset'){
	if (!empty($ast_mst_asset_no)){	
	$where_ast_mst_asset_no = " AND ast_mst.ast_mst_asset_no = '".$ast_mst_asset_no."'";	
	}else{	
		$where_ast_mst_asset_no = "";	
	}
	
}else{
	if (!empty($ast_mst_asset_no)){	
	$where_ast_mst_asset_no = " AND ast_mst.ast_mst_asset_no LIKE '%".$ast_mst_asset_no. "%' ";	
	}else{	
		$where_ast_mst_asset_no = "";	
	}
	
}

if (!empty($asset_shortdesc)){	
	$where_asset_shortdesc = " AND ast_mst.ast_mst_asset_shortdesc like '%".$asset_shortdesc."%' ";	
}else{	
	$where_asset_shortdesc = "";	
}

if (!empty($cost_center)){	
	$where_cost_center = " AND ast_mst.ast_mst_cost_center = '".$cost_center."' ";	
}else{	
	$where_cost_center = "";	
}

if (!empty($asset_status)){	
	$where_asset_status = " AND ast_mst.ast_mst_asset_status IN ('".$asset_status. "')";	
}else{	
	$where_asset_status = "";	
}

if (!empty($asset_type)){	
	$where_asset_type = " AND ast_mst.ast_mst_asset_type = '".$asset_type."'";	
}else{	
	$where_asset_type = "";	
}

if (!empty($asset_grpcode)){	
	$where_asset_grpcode = " AND ast_mst.ast_mst_asset_grpcode = '".$asset_grpcode."'";	
}else{	
	$where_asset_grpcode = "";	
}

if (!empty($work_area)){	
	$where_work_area = " AND mst_war_work_area = '".$work_area."'";	
}else{	
	$where_work_area = "";	
}

if (!empty($asset_locn)){	
	$where_asset_locn = " AND ast_mst_asset_locn = '".$asset_locn."' ";	
}else{	
	$where_asset_locn = "";	
}

if (!empty($asset_code)){	
	$where_asset_code = " AND ast_mst.ast_mst_asset_code = '".$asset_code."' ";	
}else{	
	$where_asset_code = "";	
}

if (!empty($ast_lvl)){	
	$where_ast_lvl = " AND ast_mst.ast_mst_ast_lvl = '".$ast_lvl."' ";	
}else{	
	$where_ast_lvl = "";	
}


if($ast_sts_typ_cd == "Active"){
	
	$where_ast_sts_typ_cd ="AND ast_sts.ast_sts_typ_cd IN ('IN-SERVICE')";
	
}else if($ast_sts_typ_cd == "Deactivate"){
	
	$where_ast_sts_typ_cd ="AND ast_sts.ast_sts_typ_cd IN ('OUT-OF-SERVICE')";
	
}else if($ast_sts_typ_cd == "Disposed"){
	
	$where_ast_sts_typ_cd ="AND ast_sts.ast_sts_typ_cd IN ('AWA-DISPOSED' ,  'DISPOSED')";
	
}else{
	$where_ast_sts_typ_cd ="";
}


if (!empty($createby)){	
	$where_createby = " AND (ast_mst.ast_mst_create_by = '".$createby."') ";	
}else{	
	$where_createby = "";	
}

if (!empty($from_date)){
	$where_createdate = " AND CONVERT(DATETIME, CONVERT(VARCHAR, ast_mst.ast_mst_create_date , 101)) >= CONVERT(DATETIME, CONVERT(VARCHAR, '".$from_date."', 101)) 
						AND CONVERT(DATETIME, CONVERT(VARCHAR, ast_mst.ast_mst_create_date , 101)) < CONVERT(DATETIME, CONVERT(VARCHAR, '".$to_date."', 101))  + 1 ";
}else{
	$where_createdate = " AND CONVERT(DATETIME, CONVERT(VARCHAR, '1900-01-01', 101)) < Getdate() ";
}


$where_work_group = get_emp_ls5($site_cd, $emp_id, 'ast_mst_wrk_grp');


$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,''),
				
				cf_wko_mst_originator 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_originator'),
				cf_wko_mst_phone 			= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_phone'),
				
				cf_wko_mst_assetno 			= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_assetno'),
				cf_wko_mst_asset_group_code = (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_asset_group_code'),
				cf_wko_mst_asset_location 	= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_asset_location'),
				cf_wko_mst_asset_level 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_asset_level'),
				cf_wko_mst_chg_costcenter 	= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_chg_costcenter'),
				
				cf_wko_mst_wo_no 			= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_wo_no'),
				cf_wko_mst_orig_priority 	= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_orig_priority'),
				cf_wko_mst_plan_priority 	= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_plan_priority'),
				cf_wko_mst_org_date 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_org_date'),
				cf_wko_mst_due_date 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_due_date'),
				cf_wko_mst_org_time 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_org_time'),
				cf_wko_mst_due_time 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_due_time'),
				cf_wko_det_work_type 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_work_type'),
				cf_wko_mst_flt_code 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_flt_code'),
				cf_wko_mst_status 			= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_status'),
				cf_wko_mst_descs 			= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_descs'),
				cf_wko_det_assign_to 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_assign_to'),
				cf_wko_det_work_grp 		= (select customize_label from cf_label (NOLOCK)  where language_cd = 'DEFAULT' and column_name ='wko_det_work_grp'),
				
				cf_wko_mst_project_id 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_project_id'),
				cf_wko_det_numeric1 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_numeric1'),
				cf_wko_det_numeric2 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_numeric2'),
				cf_wko_det_numeric3 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_numeric3'),
				
				cf_wko_det_lab_cost 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_lab_cost'),
				cf_wko_det_con_cost 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_con_cost'),
				cf_wko_det_mtl_cost 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_mtl_cost'),
				
				cf_ast_mst_asset_no 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_no'),
				cf_ast_mst_asset_shortdesc 	= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_shortdesc'),
				cf_ast_mst_asset_type 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_type'),
				cf_ast_mst_asset_grpcode 	= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_grpcode'),
				cf_ast_mst_asset_code 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_code'),
				cf_ast_mst_work_area 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_work_area'),
				cf_ast_mst_asset_locn 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_locn'),
				cf_ast_mst_ast_lvl 			= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_ast_lvl'),
				cf_ast_det_from_date 		= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_det_from_date'),
				cf_ast_det_to_date 			= (select customize_label from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_det_to_date'),
				
				Asset_Status_Category = CASE ast_sts.ast_sts_typ_cd 
									WHEN 'IN-SERVICE' THEN 'Active'
									WHEN 'OUT-OF-SERVICE' THEN 'Deactivate'
									WHEN 'AWA-DISPOSED' THEN 'Disposed'
									WHEN 'DISPOSED' THEN 'Disposed'
									ELSE 'Active' END
				
								
				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

							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_ast_mst_asset_no.
							$where_asset_shortdesc.
							$where_cost_center.
							$where_asset_status.
							$where_asset_type.
							$where_asset_grpcode.
							$where_work_area.
							$where_asset_locn.
							$where_asset_code.
							$where_ast_lvl.
							$where_ast_sts_typ_cd.
							$where_work_group.
							$where_createdate.
							$where_createby."  ORDER BY ast_mst_asset_no , ast_mst_asset_shortdesc ";


//echo $sql;
//die();
				$stmt_asset_list = sqlsrv_query( $conn, $sql);

				if( !$stmt_asset_list ) {
					 $error_message = "Error selecting table (ast_mst list)";
					 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) );



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



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

 
?>