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


$error_message;
$valid = true;

$json_return = array();
$json_img_link = array();

/* Begin the transaction. */
if ( sqlsrv_begin_transaction( $conn ) === false ) {
     die( print_r( sqlsrv_errors(), true ));
}


$site_cd 			= $_REQUEST['site_cd'];
$searchTerm 		= $_REQUEST['searchTerm'];
$Folder				= $_REQUEST['Folder'];
$dvc_id				= $_REQUEST['dvc_id'];
$MobileURL			= $_REQUEST['MobileURL'];
$LOGINID			= $_REQUEST['LOGINID'];
$ast_sts_typ_cd		= $_REQUEST['ast_sts_typ_cd'];

$page 				= $_REQUEST['page'];
$pageSize 			= 50;

$offset 			= ($page - 1) * $pageSize;


$uploaddir = "C:\\wamp64\\www\\".$Folder."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\";

	if (!is_dir($uploaddir)) {
		mkdir($uploaddir, 0777, true);
		mkdir($uploaddir."\\thumbnail\\", 0777, true);
	}

$MobileURLFullSize = $MobileURL . "/mobile_image_temp/temp_".$LOGINID."_".$dvc_id."/";
$MobileURLTnSize = $MobileURL . "/mobile_image_temp/temp_".$LOGINID."_".$dvc_id."/thumbnail/";

$exportdir = "C:\\wamp64\\www\\". $Folder ."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\";
$exportdirtn = "C:\\wamp64\\www\\". $Folder ."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\thumbnail\\";	


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 ="";
}



$sql="Select  	ast_mst.site_cd,
				ast_mst.RowID, 
				ast_mst_wrk_grp			= COALESCE(wrk_grp.wrk_grp_desc,''),	
				ast_mst_asset_no		= COALESCE(ast_mst_asset_no,''),
				ast_mst_asset_group 	= COALESCE(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_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_type_descs,''),				
				ast_mst_asset_code		= COALESCE(ast_cod_ast_cd,''),				
				mst_war_work_area 		= COALESCE(mst_war_desc + ' / ' + ast_loc_desc+ ' / ' + ast_lvl_desc,''),				
				ast_mst_asset_locn 		= COALESCE(ast_loc_desc,''),				
				ast_mst_asset_lvl 		= COALESCE(ast_lvl_desc,''),				
				ast_mst_asset_status 	= COALESCE(ast_sts_desc,''),				
				ast_mst_cri_factor 		= COALESCE(ast_cri_desc,''),				
				ast_mst_cost_center 	= COALESCE(descs,''),
				ast_det_warranty_date	= COALESCE(ast_det_warranty_date,''),
				ast_det_serial			= COALESCE(ast_det_serial,''),
				attachment				= COALESCE(ast_ref.attachment,Null),
				file_name				= COALESCE(ast_ref.file_name,Null),		
				
				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	( Select *, ROW_NUMBER() OVER (PARTITION BY mst_rowid ORDER BY ast_ref.type DESC) AS rn
										From ast_ref (NOLOCK)
										WHERE		ast_ref.type  IN ('P','A')
										and			COALESCE(ast_ref.column2,'') <> 'SIGN' 
										and			COALESCE(ast_ref.column2,'') <> 'RESPONSE_SIGN' 
										and			COALESCE(ast_ref.column2,'') <> 'Checklist'
										AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
										AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
										AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
										And 		DataLength(ast_ref.attachment) > 0 
										And			DataLength(ast_ref.attachment) is not null
										and			ast_ref.file_name not like '%xlsx%' 
										and			ast_ref.file_name not like '%xls%'
									) ast_ref 
							ON ast_mst.site_cd = ast_ref.site_cd AND ast_mst.rowid = ast_ref.mst_rowid AND rn = 1

							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."' )
			AND 			( 	ast_mst.ast_mst_asset_no LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								ast_mst.ast_mst_cost_center LIKE '%".$searchTerm. "%' OR 
								ast_mst.ast_mst_work_area LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								ast_mst.ast_mst_asset_locn LIKE '%".$searchTerm. "%' OR 
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR
								ast_mst.ast_mst_ast_lvl LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' 
							)".$where_ast_sts_typ_cd."ORDER BY ast_mst_asset_no OFFSET ".$offset." ROWS FETCH NEXT ".$pageSize." ROWS ONLY";

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

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

				$json = array();

				do {
					 while ($row = sqlsrv_fetch_array($stmt_asset_list, SQLSRV_FETCH_ASSOC)) {		
						
						$site_cd = $row['site_cd'];
						$RowID = $row['RowID'];
						$ast_mst_wrk_grp = $row['ast_mst_wrk_grp'];
						$ast_mst_asset_no = $row['ast_mst_asset_no'];
						$ast_mst_asset_group = $row['ast_mst_asset_group'];
						$ast_mst_asset_shortdesc = $row['ast_mst_asset_shortdesc'];
						$ast_mst_asset_longdesc = $row['ast_mst_asset_longdesc'];
						$ast_mst_asset_grpcode = $row['ast_mst_asset_grpcode'];
						$ast_mst_perm_id = $row['ast_mst_perm_id'];
						$ast_det_cus_code = $row['ast_det_cus_code'];
						$ast_mst_asset_type = $row['ast_mst_asset_type'];
						$ast_mst_asset_code = $row['ast_mst_asset_code'];
						$mst_war_work_area = $row['mst_war_work_area'];
						$ast_mst_asset_locn = $row['ast_mst_asset_locn'];
						$ast_mst_asset_lvl = $row['ast_mst_asset_lvl'];
						$ast_mst_asset_status = $row['ast_mst_asset_status'];
						$ast_mst_cri_factor = $row['ast_mst_cri_factor'];
						$ast_mst_cost_center = $row['ast_mst_cost_center'];
						$ast_det_warranty_date = $row['ast_det_warranty_date'];
						$ast_det_serial = $row['ast_det_serial'];
						$Asset_Status_Category = $row['Asset_Status_Category'];
						
						$file_name = $row['file_name'];
				
						$attachment;
						$fileName;
						
						if(empty($file_name)){
						
						$attachment = Null;
						$fileName =Null;
						
						}else{
							
							if (strpos($file_name, '.pdf') !== false) {
						
								$attachment = Null;
								$fileName =Null;
							}else if (strpos($file_name, '.mp4') !== false) {
								
								$attachment = Null;
								$fileName =Null;
							}else if (strpos($file_name, 'docx') == true){	
						
								$attachment = Null;
								$fileName =Null;
			
							}else{
								
								$Photo = $row['attachment'];
								$file_Name = $row['file_name'];

								$exportFullPathFileName = $exportdir . $file_Name;
								$exportFullPathTNFileName = $exportdirtn . $file_Name;

								$publicFullPathFileName = $MobileURLFullSize . $file_Name;
								$publicFullPathTNFileName = $MobileURLTnSize . $file_Name;
								
								if (!file_exists($exportFullPathTNFileName)) {
									// If the thumbnail does not exist, resize and write the thumbnail
									resizeImageImagick($Photo, $exportFullPathTNFileName);
								} 

								$attachment = $publicFullPathTNFileName;
								$fileName =$file_Name;
								
							}
					
							
						}
						
						
						$json_img_link['site_cd'] = $site_cd;
						$json_img_link['RowID'] = $RowID;
						$json_img_link['ast_mst_wrk_grp'] = $ast_mst_wrk_grp;
						$json_img_link['ast_mst_asset_no'] = $ast_mst_asset_no;
						$json_img_link['ast_mst_asset_group'] = $ast_mst_asset_group;
						$json_img_link['ast_mst_asset_shortdesc'] = $ast_mst_asset_shortdesc;
						$json_img_link['ast_mst_asset_longdesc'] = $ast_mst_asset_longdesc;
						$json_img_link['ast_mst_asset_grpcode'] = $ast_mst_asset_grpcode;
						$json_img_link['ast_mst_perm_id'] = $ast_mst_perm_id;
						$json_img_link['ast_det_cus_code'] = $ast_det_cus_code;
						$json_img_link['ast_mst_asset_type'] = $ast_mst_asset_type;
						$json_img_link['ast_mst_asset_code'] = $ast_mst_asset_code;
						$json_img_link['mst_war_work_area'] = $mst_war_work_area;
						$json_img_link['ast_mst_asset_locn'] = $ast_mst_asset_locn;
						$json_img_link['ast_mst_asset_lvl'] = $ast_mst_asset_lvl;
						$json_img_link['ast_mst_asset_status'] = $ast_mst_asset_status;
						$json_img_link['ast_mst_cri_factor'] = $ast_mst_cri_factor;
						$json_img_link['ast_mst_cost_center'] = $ast_mst_cost_center;
						$json_img_link['ast_det_warranty_date'] = $ast_det_warranty_date;
						$json_img_link['ast_det_serial'] = $ast_det_serial;
						$json_img_link['Asset_Status_Category'] = $Asset_Status_Category;
						$json_img_link['attachment'] = $attachment;
						$json_img_link['fileName'] = $fileName;
						array_push($json_return, $json_img_link);
							
					}
				} while ( sqlsrv_next_result($stmt_asset_list) );


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



function returnData($json){
	 $json1 = json_encode($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();
		
}
	

 
?>