<?php
require_once('config.php');

$error_message;
$valid = true;


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

	$sql = "Select 
		wko_isp.site_cd,
			wko_isp.rowid,
			wko_isp.mst_rowid,
			
			wko_isp_asset_no,
			wko_isp_job_cd,
			wko_isp_job_desc,
			wko_isp_sec_no,
			wko_isp_sec_desc,
			wko_isp_stp_no,
			wko_isp_stp_desc,
			wko_isp_stp_datatype,
			wko_isp_stp_rowid,
			
			wko_isp_varchar1,
			wko_isp_varchar2,
			wko_isp_varchar3,
			wko_isp_numeric1,
			wko_isp_numeric2,
			wko_isp_numeric3,
			wko_isp_datetime1,
			wko_isp_datetime2,
			wko_isp_datetime3,
			wko_isp_checkbox1,
			wko_isp_checkbox2,
			wko_isp_checkbox3,
			wko_isp_dropdown1,
			wko_isp_dropdown2,
			wko_isp_dropdown3,
			
			wko_isp_uom,
			wko_isp_min_thr,
			wko_isp_max_thr,
			wko_isp_ovr_thr,
			
			wko_isp.column1,
			
			file_name,
			file_link,
			
			wko_isp.audit_user,
			wko_isp.audit_date,
			
			wko_isp.mbl_audit_user,
			wko_isp.mbl_audit_date,
			
			
			'total' ='0',							
			'done' = '0',							
			'na' = '0'			
														
		FROM 	wko_mst (NOLOCK)     			
	
				INNER JOIN wko_det (NOLOCK)
				ON		wko_mst.site_cd = wko_det.site_cd
				AND		wko_mst.rowid = wko_det.mst_rowid
				AND 	wko_mst_type  IN ('C', 'P')
				
				
				INNER JOIN wko_isp (NOLOCK)
				ON		wko_mst.site_cd = wko_isp.site_cd
				AND		wko_mst.rowid = wko_isp.mst_rowid
				
				
				LEFT 
				OUTER 
				JOIN		ast_mst ast_mst_check(NOLOCK)
				ON			wko_isp.site_cd = ast_mst_check.site_cd
				AND			wko_isp.wko_isp_asset_no = ast_mst_check.ast_mst_asset_no
				
				
				INNER JOIN	wrk_sts (NOLOCK)
				ON			wko_mst.site_cd = wrk_sts.site_cd
				AND			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status
						
				LEFT 
				OUTER 
				JOIN		ast_mst (NOLOCK)
				ON			wko_mst.site_cd = ast_mst.site_cd
				AND			wko_mst.wko_mst_assetno = ast_mst.ast_mst_asset_no
					
				
					

			WHERE	( wko_mst.site_cd = '".$_REQUEST['site_cd']."' ) 
			AND 	wko_det.wko_det_assign_to = '".$_REQUEST['wko_det_assign_to']."'  
			AND (wrk_sts_typ_cd NOT IN ('CLOSE', 'COMPLETE', 'FORCE-CLOSE', 'CANCEL')) 
			
					
			
			
			UNION ALL
			
			Select 
			wko_isp.site_cd,
			wko_isp.rowid,
			wko_isp.mst_rowid,
			
			wko_isp_asset_no,
			wko_isp_job_cd,
			wko_isp_job_desc,
			wko_isp_sec_no,
			wko_isp_sec_desc,
			wko_isp_stp_no,
			wko_isp_stp_desc,
			wko_isp_stp_datatype,
			wko_isp_stp_rowid,
			
			wko_isp_varchar1,
			wko_isp_varchar2,
			wko_isp_varchar3,
			wko_isp_numeric1,
			wko_isp_numeric2,
			wko_isp_numeric3,
			wko_isp_datetime1,
			wko_isp_datetime2,
			wko_isp_datetime3,
			wko_isp_checkbox1,
			wko_isp_checkbox2,
			wko_isp_checkbox3,
			wko_isp_dropdown1,
			wko_isp_dropdown2,
			wko_isp_dropdown3,
			
			wko_isp_uom,
			wko_isp_min_thr,
			wko_isp_max_thr,
			wko_isp_ovr_thr,
			
			wko_isp.column1,
			
			file_name,
			file_link,
			
			wko_isp.audit_user,
			wko_isp.audit_date,
			
			wko_isp.mbl_audit_user,
			wko_isp.mbl_audit_date,
			
			
			'total' ='0',							
			'done' = '0',							
			'na' = '0'			
												
			FROM 	wko_mst (NOLOCK)     			
	
					INNER JOIN wko_det (NOLOCK)
					ON		wko_mst.site_cd = wko_det.site_cd
					AND		wko_mst.rowid = wko_det.mst_rowid
					AND 	wko_mst_type  IN ('G')
					
					LEFT JOIN wko_isp (NOLOCK)
					ON		wko_mst.site_cd = wko_isp.site_cd
					AND		wko_mst.rowid = wko_isp.mst_rowid
					
					
					LEFT 
					OUTER 
					JOIN		ast_mst ast_mst_check(NOLOCK)
					ON			wko_isp.site_cd = ast_mst_check.site_cd
					AND			wko_isp.wko_isp_asset_no = ast_mst_check.ast_mst_asset_no
					
					
					INNER JOIN	wrk_sts (NOLOCK)
					ON			wko_mst.site_cd = wrk_sts.site_cd
					AND			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status
							
					LEFT 
					OUTER 
					JOIN		ast_mst (NOLOCK)
					ON			wko_isp.site_cd = ast_mst.site_cd
					AND			wko_isp.wko_isp_asset_no = ast_mst.ast_mst_asset_no

			WHERE	( wko_mst.site_cd = '".$_REQUEST['site_cd']."' ) 
			AND 	wko_det.wko_det_assign_to = '".$_REQUEST['wko_det_assign_to']."' 
			AND (wrk_sts_typ_cd NOT IN ('CLOSE', 'COMPLETE', 'FORCE-CLOSE', 'CANCEL'))  
			order by wko_isp_job_cd, wko_isp_sec_no,wko_isp_stp_no" ;
			
$stmt = sqlsrv_query( $conn, $sql);

if( !$stmt ) {
     $error_message = "Error selecting table (Work Order Check List)";
	 returnError($error_message);
     die( print_r( sqlsrv_errors(), true));
}

$json = array();

do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
			$site_cd = $row['site_cd'];
			$rowid = $row['rowid'];
			$mst_rowid = $row['mst_rowid'];
			
			$wko_isp_asset_no = $row['wko_isp_asset_no'];
			$wko_isp_job_cd = $row['wko_isp_job_cd'];
			$wko_isp_job_desc = $row['wko_isp_job_desc'];
			$wko_isp_sec_no = $row['wko_isp_sec_no'];
			$wko_isp_sec_desc = $row['wko_isp_sec_desc'];
			$wko_isp_stp_no = $row['wko_isp_stp_no'];
			$wko_isp_stp_desc = $row['wko_isp_stp_desc'];
			$wko_isp_stp_datatype = $row['wko_isp_stp_datatype'];
			$wko_isp_stp_rowid = $row['wko_isp_stp_rowid'];
			
			$wko_isp_varchar1 = $row['wko_isp_varchar1'];
			$wko_isp_varchar2 = $row['wko_isp_varchar2'];
			$wko_isp_varchar3 = $row['wko_isp_varchar3'];
			$wko_isp_numeric1 = $row['wko_isp_numeric1'];
			$wko_isp_numeric2 = $row['wko_isp_numeric2'];
			$wko_isp_numeric3 = $row['wko_isp_numeric3'];
			$wko_isp_datetime1 = $row['wko_isp_datetime1'];
			$wko_isp_datetime2 = $row['wko_isp_datetime2'];
			$wko_isp_datetime3 = $row['wko_isp_datetime3'];
			$wko_isp_checkbox1 = $row['wko_isp_checkbox1'];
			$wko_isp_checkbox2 = $row['wko_isp_checkbox2'];
			$wko_isp_checkbox3 = $row['wko_isp_checkbox3'];
			$wko_isp_dropdown1 = $row['wko_isp_dropdown1'];
			$wko_isp_dropdown2 = $row['wko_isp_dropdown2'];
			$wko_isp_dropdown3 = $row['wko_isp_dropdown3'];
			
			
			
			$wko_isp_uom = $row['wko_isp_uom'];
			$wko_isp_min_thr = $row['wko_isp_min_thr'];
			$wko_isp_max_thr = $row['wko_isp_max_thr'];
			$wko_isp_ovr_thr = $row['wko_isp_ovr_thr'];
			
			$column1 = $row['column1'];
			
			$file_name = $row['file_name'];
			$file_link = $row['file_link'];
			
			
			$audit_user = $row['audit_user'];
			$audit_date = $row['audit_date'];
			$mbl_audit_user = $row['mbl_audit_user'];
			$mbl_audit_date = $row['mbl_audit_date'];
			
			$sql = "SELECT stp_zom_data FROM stp_zom(NOLOCK) WHERE site_cd = '".$site_cd."' AND mst_rowid = '".$wko_isp_stp_rowid." ' order by stp_zom.column1, stp_zom.stp_zom_data asc";
			
			$stp_zom = sqlsrv_query( $conn, $sql);

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

			$json_list = array();

			do {
				 while ($row = sqlsrv_fetch_array($stp_zom, SQLSRV_FETCH_ASSOC)) {
					//$json_list[] = $row;
					
					$stp_zom_data = $row['stp_zom_data'];
					
					$json_zoom['label'] = $stp_zom_data;
					$json_zoom['value'] = $stp_zom_data;
					array_push($json_list, $json_zoom);
					
					
					
					
				 }
			} while ( sqlsrv_next_result($stp_zom) );
			
			$sql = "SELECT Img = count(*) FROM wko_isp_ref (NOLOCK) 
			WHERE site_cd = '".$site_cd."' 
			AND wko_isp_ref_stp_RowID = '".$wko_isp_stp_rowid." '
			And DataLength(attachment) > 0 
			And	DataLength(attachment) is not null
			And mst_RowID is not null
			And COALESCE(column2,'') <> 'SIGN' 
			And COALESCE(column2,'') <> 'RESPONSE_SIGN' 
			And COALESCE(column2,'') <> 'Checklist'
			And file_name not like '%xlsx%' 
			And file_name not like '%xls%'";
			
			$stp_ref = sqlsrv_query( $conn, $sql);

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

			$Img_count;

			do {
				 while ($row = sqlsrv_fetch_array($stp_ref, SQLSRV_FETCH_ASSOC)) {
					//$json_list[] = $row;
					
					$Img_count = $row['Img'];
					
				 }
			} while ( sqlsrv_next_result($stp_ref) );
			
			
			
				$json_img_link['site_cd'] = $site_cd;
				$json_img_link['rowid'] = $rowid;
				$json_img_link['mst_rowid'] = $mst_rowid;
				$json_img_link['wko_isp_asset_no'] = $wko_isp_asset_no;
				$json_img_link['wko_isp_job_cd'] = $wko_isp_job_cd;
				$json_img_link['wko_isp_job_desc'] = $wko_isp_job_desc;
				$json_img_link['wko_isp_sec_no'] = $wko_isp_sec_no;
				$json_img_link['wko_isp_sec_desc'] = $wko_isp_sec_desc;
				$json_img_link['wko_isp_stp_no'] = $wko_isp_stp_no;
				$json_img_link['wko_isp_stp_desc'] = $wko_isp_stp_desc;
				$json_img_link['wko_isp_stp_datatype'] = $wko_isp_stp_datatype;
				$json_img_link['wko_isp_stp_rowid'] = $wko_isp_stp_rowid;
				
				$json_img_link['wko_isp_varchar1'] = $wko_isp_varchar1;
				$json_img_link['wko_isp_varchar2'] = $wko_isp_varchar2;
				$json_img_link['wko_isp_varchar3'] = $wko_isp_varchar3;
				$json_img_link['wko_isp_numeric1'] = $wko_isp_numeric1;
				$json_img_link['wko_isp_numeric2'] = $wko_isp_numeric2;
				$json_img_link['wko_isp_numeric3'] = $wko_isp_numeric3;
				$json_img_link['wko_isp_datetime1'] = $wko_isp_datetime1;
				$json_img_link['wko_isp_datetime2'] = $wko_isp_datetime2;
				$json_img_link['wko_isp_datetime3'] = $wko_isp_datetime3;
				$json_img_link['wko_isp_checkbox1'] = $wko_isp_checkbox1;
				$json_img_link['wko_isp_checkbox2'] = $wko_isp_checkbox2;
				$json_img_link['wko_isp_checkbox3'] = $wko_isp_checkbox3;
				$json_img_link['wko_isp_dropdown1'] = $wko_isp_dropdown1;
				$json_img_link['wko_isp_dropdown2'] = $wko_isp_dropdown2;
				$json_img_link['wko_isp_dropdown3'] = $wko_isp_dropdown3;
				
				$json_img_link['wko_isp_uom'] = $wko_isp_uom;
				$json_img_link['wko_isp_min_thr'] = $wko_isp_min_thr;
				$json_img_link['wko_isp_max_thr'] = $wko_isp_max_thr;
				$json_img_link['wko_isp_ovr_thr'] = $wko_isp_ovr_thr;
				$json_img_link['column1'] = $column1;
				$json_img_link['file_name'] = $file_name;
				$json_img_link['file_link'] = $file_link;
				$json_img_link['audit_user'] = $audit_user;
				$json_img_link['audit_date'] = $audit_date;
				$json_img_link['mbl_audit_user'] = $mbl_audit_user;
				$json_img_link['mbl_audit_date'] = $mbl_audit_date;
				$json_img_link['Zoom_list'] = $json_list;
				$json_img_link['Img_count'] = $Img_count;
				
				array_push($json_return, $json_img_link);
     }
} while ( sqlsrv_next_result($stmt) );

if ($valid) {
	sqlsrv_free_stmt( $stmt);
	sqlsrv_close( $conn);	
	
	returnData($json_return);	
}

function returnData($json){
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'Select 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);
	
	sqlsrv_free_stmt( $stmt);
	sqlsrv_close( $conn);	
		
	exit();
}


?>