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

$error_message;
$valid = true;

$sql = "Select 
				wko_isp.mst_rowid,
				wko_isp.site_cd,
				wko_isp.wko_isp_asset_no,
				ast_mst.ast_mst_asset_shortdesc,
				wko_isp.wko_isp_job_cd,
				wko_isp.wko_isp_job_desc,
				ast_mst.ast_mst_work_area,
				ast_mst.ast_mst_asset_locn,
				ast_mst.ast_mst_ast_lvl,
				wko_isp.wko_isp_datetime3,
				
				'total' = COALESCE(SUM(CAST(wko_isp_varchar3 as int)),0 ),
				'done' = SUM(CASE WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'T' and wko_isp_varchar1 IS NOT NULL Then 1
								WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'N' and wko_isp_numeric1 IS NOT NULL Then 1
								WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'D' and wko_isp_datetime1 IS NOT NULL Then 1
								WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'C' and wko_isp_checkbox1 = '1' Then 1
								WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'Z' and wko_isp_dropdown1 IS NOT NULL Then 1 ELSE 0 END),
				'na' = SUM(CASE WHEN wko_isp_varchar3 = '0' Then 1 ELSE 0 END )	

					
						
		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 LIKE '".$_REQUEST['wko_det_assign_to']."'  
		AND 	(wrk_sts_typ_cd NOT IN ('CLOSE', 'COMPLETE', 'FORCE-CLOSE', 'CANCEL')) 
		group by  
		wko_isp.mst_rowid,
		wko_isp.site_cd,
		wko_isp.wko_isp_asset_no,
		ast_mst.ast_mst_asset_shortdesc,
		wko_isp.wko_isp_job_cd,
		wko_isp.wko_isp_job_desc,
		ast_mst.ast_mst_work_area,
		ast_mst.ast_mst_asset_locn,
		ast_mst.ast_mst_ast_lvl,
		wko_isp.wko_isp_datetime3
		

			
		
		
		UNION ALL
			
		Select 	

				wko_isp.mst_rowid,
				wko_isp.site_cd,
				wko_isp.wko_isp_asset_no,
				ast_mst.ast_mst_asset_shortdesc,
				wko_isp.wko_isp_job_cd,
				wko_isp.wko_isp_job_desc,
				ast_mst.ast_mst_work_area,
				ast_mst.ast_mst_asset_locn,
				ast_mst.ast_mst_ast_lvl,
				wko_isp.wko_isp_datetime3,
				
				
				'total' = COALESCE(SUM(CAST(wko_isp_varchar3 as int)),0 ),
				'done' = SUM(CASE WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'T' and wko_isp_varchar1 IS NOT NULL Then 1
								WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'N' and wko_isp_numeric1 IS NOT NULL Then 1
								WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'D' and wko_isp_datetime1 IS NOT NULL Then 1
								WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'C' and wko_isp_checkbox1 = '1' Then 1
								WHEN wko_isp_varchar3 = '1' and wko_isp_stp_datatype = 'Z' and wko_isp_dropdown1 IS NOT NULL Then 1 ELSE 0 END),
				'na' = SUM(CASE WHEN wko_isp_varchar3 = '0' Then 1 ELSE 0 END )	 									
						
		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 LIKE '".$_REQUEST['wko_det_assign_to']."'   
		AND		(wrk_sts_typ_cd NOT IN ('CLOSE', 'COMPLETE', 'FORCE-CLOSE', 'CANCEL'))  
		
		group by  
		wko_isp.mst_rowid,
		wko_isp.site_cd,
		wko_isp.wko_isp_asset_no,
		ast_mst.ast_mst_asset_shortdesc,
		wko_isp.wko_isp_job_cd,
		wko_isp.wko_isp_job_desc,
		ast_mst.ast_mst_work_area,
		ast_mst.ast_mst_asset_locn,
		ast_mst.ast_mst_ast_lvl,
		wko_isp.wko_isp_datetime3";
		
$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)) {
     $json[] = $row;
     }
} while ( sqlsrv_next_result($stmt) );

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

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();
}


?>