<?php
// get these values from your DB.

require_once('config.php');

$error_message;
$valid = true;
$site_cd = $_REQUEST['site_cd'];
$wko_mst_wo_no = $_REQUEST['wko_mst_wo_no'];
	
$sql= "select	wko_det_varchar9 = CASE WHEN wko_det_varchar9 IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
		wko_det_safety = CASE WHEN wko_det_safety IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
		wko_det_ack_name = CASE WHEN wko_det_ack_name IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
		wrk_sts_typ_cd  = CASE WHEN wrk_sts_typ_cd = 'COMPLETE' Then '#5EFF33' Else '#05375a' End,
		ctc_cnt = (Select Count(*) from wko_ls4 where wko_ls4.site_cd = wko_mst.site_cd and wko_ls4.mst_RowID = wko_mst.rowid),
		mr_cnt = (Select Count(*) from wko_ls2 where wko_ls2.site_cd = wko_mst.site_cd and wko_ls2.mst_RowID = wko_mst.rowid),
		cl_cnt = (Select CAST(COALESCE(SUM(CASE WHEN wko_isp_stp_datatype = 'Z' and wko_isp_dropdown1 is not null Then 1
										WHEN wko_isp_stp_datatype = 'T' and wko_isp_varchar1 is not null Then 1
										WHEN wko_isp_stp_datatype = 'C' and wko_isp_checkbox1 = '1' Then 1
										WHEN wko_isp_stp_datatype = 'D' and wko_isp_datetime1 is not null Then 1
										WHEN wko_isp_stp_datatype = 'N' and wko_isp_numeric1 is not null Then 1 End), '0') as varchar) + '/' +
									CAST(COALESCE(SUM(CAST(wko_isp_varchar3 as numeric)), '0') as varchar)
					From wko_isp where mst_rowid = wko_mst.rowid and wko_isp_varchar3  = '1'),
		tc_cnt  = (Select Count(*) from wko_ls8 where wko_ls8.site_cd = wko_mst.site_cd and wko_ls8.mst_RowID = wko_mst.rowid),
		swo_cnt = (Select Count(*) from wko_det B where B.site_cd = wko_mst.site_cd and B.wko_det_parent_wo = wko_mst.wko_mst_wo_no),
		dwn_cnt = (select count(*) from ast_dwntime where ast_dwntime_down_wo = wko_mst.wko_mst_wo_no and ast_dwntime.site_cd = wko_mst.site_cd),
		pur_cnt	= (select count(*)	from		pur_mst (NOLOCK)   
					INNER JOIN	pur_det (NOLOCK)
					ON			pur_mst.site_cd = pur_det.site_cd
					AND			pur_mst.rowid = pur_det.mst_RowID
							
			
					INNER JOIN	pur_ls1 (NOLOCK)
					ON			pur_mst.site_cd = pur_ls1.site_cd
					AND			pur_mst.rowid = pur_ls1.mst_RowID
	
					where 		pur_mst.site_cd  =	wko_mst.site_cd
					AND 	( 	pur_det_varchar1 =  wko_mst.wko_mst_wo_no
					OR			pur_det_varchar5 =  wko_mst.wko_mst_wo_no
					OR 			pur_det_varchar6 =  wko_mst.wko_mst_wo_no
					OR 			pur_det_varchar7 =  wko_mst.wko_mst_wo_no
					OR 			pur_det_varchar8 =  wko_mst.wko_mst_wo_no))
		from	wko_mst (NOLOCK), wko_det(NOLOCK), wrk_sts (NOLOCK)
		Where	wko_mst.site_cd = wko_det.site_cd
		and		wko_mst.rowid = wko_det.mst_rowid
		and		wko_mst.site_cd = wrk_sts.site_cd
		and		wko_mst.wko_mst_status = wrk_sts.wrk_sts_status
		and		wko_mst.site_cd = '".$site_cd."'
		and		wko_mst.wko_mst_wo_no = '".$wko_mst_wo_no."'";

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

if( !$stmt ) {
     $error_message = "Error selecting table (dft_mst)";
	 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) {
			
	returnData($json);	
	sqlsrv_free_stmt( $stmt);
	sqlsrv_close( $conn);
}

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

 
?>