<?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,
				ast_mst.ast_mst_ast_lvl,
				ast_mst.ast_mst_work_area,
				ast_mst.ast_mst_asset_locn,
				wko_isp.wko_isp_job_cd,
				wko_isp.wko_isp_job_desc,
				wko_isp.wko_isp_datetime3,
				
			
			'total' = SUM(CAST(wko_isp_varchar3 as int)),
			
			'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_isp 
		
			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_isp.site_cd = '".$_REQUEST['site_cd']."' 
			AND mst_rowid = ".$_REQUEST['rowid']." 
			GROUP BY 
			wko_isp_job_cd,
			wko_isp.wko_isp_job_desc,
			wko_isp.mst_rowid,
			wko_isp.site_cd,
			wko_isp.wko_isp_asset_no,
			ast_mst.ast_mst_asset_shortdesc,
			ast_mst.ast_mst_ast_lvl,
			ast_mst.ast_mst_work_area,
			ast_mst.ast_mst_asset_locn,
			wko_isp.wko_isp_datetime3
		HAVING 
			SUM(CAST(wko_isp_varchar3 as int)) > 0  -- Make sure we have valid data to avoid dividing by zero
		ORDER BY  
			CASE 
				WHEN 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) = SUM(CAST(wko_isp_varchar3 as int)) THEN 1
				ELSE 0
			END ASC";
$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();
}


?>