<?php
// get these values from your DB.
require_once('config.php');
$error_message;
$valid = true;

$site_cd = $_REQUEST['site_cd'];

$wrk_sts_typ_cd =$_REQUEST['wrk_sts_typ_cd']; 

$wko_mst_assetno= $_REQUEST['wko_mst_assetno'];
$wko_mst_descs= $_REQUEST['wko_mst_descs'];
$wko_mst_originator= $_REQUEST['wko_mst_originator'];
$wko_mst_status= $_REQUEST['wko_mst_status'];
$wko_mst_work_area= $_REQUEST['wko_mst_work_area'];
$wko_mst_type= $_REQUEST['wko_mst_type'];
$wko_mst_asset_location= $_REQUEST['wko_mst_asset_location'];
$wko_mst_asset_level= $_REQUEST['wko_mst_asset_level'];
$wko_mst_org_date=$_REQUEST['wko_mst_org_date'];
$wko_mst_due_date= $_REQUEST['wko_mst_due_date'];
$asset_shortdesc=$_REQUEST['asset_shortdesc'];


if (isset($_REQUEST['wko_mst_wo_no'])){
	$where = " AND wko_mst.wko_mst_wo_no LIKE '%".$_REQUEST['wko_mst_wo_no']. "%' ";
}else{
	$where = "AND wko_mst.wko_mst_wo_no LIKE '%%'";
}


if($wrk_sts_typ_cd  == "Open"){
	$sts_typ_cd ="AND (wrk_sts_typ_cd NOT IN ('CLOSE', 'COMPLETE', 'FORCE-CLOSE', 'CANCEL'))";
}else if($wrk_sts_typ_cd  == "Complete"){
	$sts_typ_cd ="AND (wrk_sts_typ_cd IN ('COMPLETE'))";
}else if($wrk_sts_typ_cd  == "Close"){
	$sts_typ_cd ="AND (wrk_sts_typ_cd IN ('CLOSE', 'FORCE-CLOSE'))";
}else{
	$sts_typ_cd ="AND (wrk_sts_typ_cd LIKE '%%')";
}

if($wko_mst_type  == ""){
	$wko_mst_type ="AND (wko_mst_type LIKE '%%')";
}else if($wko_mst_type  == "C"){
	$wko_mst_type ="AND (wko_mst_type LIKE '%C%')";
}else if($wko_mst_type  == "P"){
	$wko_mst_type ="AND wko_mst_type  IN ('P','G')";
}else{
	$wko_mst_type ="AND (wko_mst_type LIKE '%%')";
}


$sql="Select 
				wko_mst.site_cd,   
				wko_mst.wko_mst_wo_no,     			
				wko_mst_originator= wko_mst_originator+':'+emp_mst_name,      			
				wko_mst_phone= COALESCE(wko_mst_phone,'') ,     
				wko_mst_asset_level= wko_mst_asset_level+':'+ast_lvl_desc,     	
				wko_mst.wko_mst_assetno, 
				wko_mst_flt_code= wko_mst_flt_code+':'+wrk_flt_desc,      		
				wko_mst_status =wko_mst_status +':'+ wrk_sts_desc,      		
				wko_mst.wko_mst_due_date,     			
				wko_mst.wko_mst_descs, 
				wko_mst_ast_cod,				
				wko_mst.wko_mst_org_date,     		
				wko_mst_chg_costcenter= wko_mst_chg_costcenter+':'+cf_cost_center.descs, 
				wko_mst_work_area = wko_mst_work_area +':'+mst_war_desc,          	
				wko_mst_asset_location =wko_mst_asset_location+':'+ast_loc_desc,		
				wko_mst_asset_group_code = wko_mst_asset_group_code+':'+ ast_grp_desc,
				wko_mst_orig_priority =wko_mst_orig_priority+':'+ wrk_pri_2.wrk_pri_desc,		
				wko_mst_plan_priority =wko_mst_plan_priority+':'+ wrk_pri.wrk_pri_desc ,
				wko_mst.audit_user,   wko_mst.audit_date,     			
				wko_mst.wko_mst_create_by,  
				wko_mst.wko_mst_create_date,     		   		
				wko_mst.RowID, 
				wrk_sts_typ_cd,
				wko_mst_type,
				wko_det_laccount= wko_det_laccount+':'+ cf_account_1.descs ,
				wko_det_caccount =wko_det_caccount+':'+ cf_account_2.descs ,
				wko_det_maccount= wko_det_maccount+':'+ cf_account_3.descs ,
				ast_mst_asset_shortdesc				
								
				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
						
						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
						
						LEFT 
						OUTER 
						JOIN		emp_mst(NOLOCK)						
						ON			wko_mst.site_cd = emp_mst.site_cd
						AND			wko_mst.wko_mst_originator = emp_mst.emp_mst_empl_id
						
						
						LEFT 
						OUTER 
						JOIN		ast_grp (NOLOCK)
						ON			wko_mst.site_cd = ast_grp.site_cd
						AND			wko_mst.wko_mst_asset_group_code = ast_grp.ast_grp_grp_cd
						
						LEFT 
						OUTER 
						JOIN		ast_loc (NOLOCK)
						ON			wko_mst.site_cd = ast_loc.site_cd
						AND			wko_mst.wko_mst_asset_location = ast_loc.ast_loc_ast_loc


						LEFT 
						OUTER 
						JOIN		ast_lvl (NOLOCK)
						ON			wko_mst.site_cd = ast_lvl.site_cd
						AND			wko_mst.wko_mst_asset_level = ast_lvl.ast_lvl_ast_lvl
						
						LEFT 
						OUTER 
						JOIN		mst_war (NOLOCK)
						ON			wko_mst.site_cd = mst_war.site_cd
						AND			wko_mst.wko_mst_work_area = mst_war.mst_war_work_area

						
						LEFT 
						OUTER 
						JOIN		wrk_pri (NOLOCK)
						ON			wko_mst.site_cd = wrk_pri.site_cd
						AND			wko_mst.wko_mst_plan_priority = wrk_pri.wrk_pri_pri_cd

						LEFT 
						OUTER 
						JOIN		wrk_pri wrk_pri_2(NOLOCK)
						ON			wko_mst.site_cd = wrk_pri_2.site_cd
						AND			wko_mst.wko_mst_orig_priority = wrk_pri_2.wrk_pri_pri_cd

						LEFT 
						OUTER 
						JOIN		cf_cost_center (NOLOCK)
						ON			wko_mst.site_cd = cf_cost_center.site_cd
						AND			wko_mst.wko_mst_chg_costcenter = cf_cost_center.costcenter


						LEFT 
						OUTER 
						JOIN		wrk_flt (NOLOCK)
						ON			wko_mst.site_cd = wrk_flt.site_cd
						AND			wko_mst.wko_mst_flt_code = wrk_flt.wrk_flt_fault_cd
					
						LEFT 
						OUTER 
						JOIN		cf_account cf_account_1(NOLOCK)
						ON			wko_mst.site_cd = cf_account_1.site_cd
						AND			wko_det.wko_det_laccount = cf_account_1.account
						
						LEFT 
						OUTER 
						JOIN		cf_account cf_account_2(NOLOCK)
						ON			wko_mst.site_cd = cf_account_2.site_cd
						AND			wko_det.wko_det_caccount = cf_account_2.account
						
						LEFT 
						OUTER 
						JOIN		cf_account cf_account_3(NOLOCK)
						ON			wko_mst.site_cd = cf_account_3.site_cd
						AND			wko_det.wko_det_maccount = cf_account_3.account

				WHERE		( wko_mst.site_cd = '".$site_cd."' )  
						
						AND	( wko_mst.wko_mst_assetno like '%".$wko_mst_assetno."%' )
						AND	( ast_mst_asset_shortdesc like '%".$asset_shortdesc."%' )
						AND	( wko_mst.wko_mst_descs like '%".$wko_mst_descs."%' )
						AND	( wko_mst.wko_mst_originator like '%".$wko_mst_originator."%' )	
						AND	( wko_mst.wko_mst_status like '%".$wko_mst_status."%' )	
						AND	( wko_mst.wko_mst_work_area like '%".$wko_mst_work_area."%' )	
						AND	( wko_mst.wko_mst_asset_location like '%".$wko_mst_asset_location."%' )	
						AND	( wko_mst.wko_mst_asset_level like '%".$wko_mst_asset_level."%' )
						AND CONVERT(DATETIME, CONVERT(VARCHAR, wko_mst.wko_mst_org_date , 101)) >= CONVERT(DATETIME, CONVERT(VARCHAR, '".$wko_mst_org_date."', 101)) -- yyyy-mm-dd from
						AND CONVERT(DATETIME, CONVERT(VARCHAR, wko_mst.wko_mst_org_date , 101)) < CONVERT(DATETIME, CONVERT(VARCHAR, '".$wko_mst_due_date."', 101))  + 1 --today
						".$sts_typ_cd."
						".$wko_mst_type."
						".$where."
						ORDER BY wko_mst_create_date DESC";
 

		//echo  $sql;

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

		if( !$stmt ) {
			 $error_message = "Error selecting table";
			 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) );
 
 
 $sql="SELECT 	Opens = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'OPEN' THEN 1 
												WHEN 'READY-TO-BE-DONE' THEN 1 ELSE 0 END),
				Complete = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'COMPLETE' THEN 1 ELSE 0 END),
				Closes = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'CLOSE' THEN 1 WHEN 'FORCE-CLOSE' THEN 1 ELSE 0 END)
						
		FROM 	wko_mst (NOLOCK)  
				
				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 = '".$site_cd."' )		
						AND	( wko_mst.wko_mst_assetno like '%".$wko_mst_assetno."%' )
						AND	( ast_mst_asset_shortdesc like '%".$asset_shortdesc."%' )
						AND	( wko_mst.wko_mst_descs like '%".$wko_mst_descs."%' )
						AND	( wko_mst.wko_mst_originator like '%".$wko_mst_originator."%' )	
						AND	( wko_mst.wko_mst_status like '%".$wko_mst_status."%' )	
						AND	( wko_mst.wko_mst_work_area like '%".$wko_mst_work_area."%' )	
						AND	( wko_mst.wko_mst_asset_location like '%".$wko_mst_asset_location."%' )	
						AND	( wko_mst.wko_mst_asset_level like '%".$wko_mst_asset_level."%' )	
						AND CONVERT(DATETIME, CONVERT(VARCHAR, wko_mst.wko_mst_org_date , 101)) >= CONVERT(DATETIME, CONVERT(VARCHAR, '".$wko_mst_org_date."', 101)) -- yyyy-mm-dd from
						AND CONVERT(DATETIME, CONVERT(VARCHAR, wko_mst.wko_mst_org_date , 101)) < CONVERT(DATETIME, CONVERT(VARCHAR, '".$wko_mst_due_date."', 101))  + 1 --today						
						".$wko_mst_type."
						".$where."";

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

			if( !$stmt ) {
				 $error_message = "Error selecting table (wko_mst)";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
				 
			}
			do {
				 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {		
					$Opens = $row['Opens'];
					$Complete = $row['Complete'];
					$Closes = $row['Closes'];
				
				 }
			} while ( sqlsrv_next_result($stmt) );
 




if ($valid) {

	//echo $sql;
	returnData($json,$Opens,$Complete,$Closes);	
	
}


function returnData($json,$Opens,$Complete,$Closes){
	 $json1 = json_encode($json);
	 //echo$json;
	 
if(empty(json_decode($json1,1))) {
     $returnData = array(
	'status' => 'Error',
	'message' => 'No Records found',
	'data' => $json);
}else{
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'Successfully',
	'Opens'=>$Opens,
	'Complete'=>$Complete,
	'Closes'=>$Closes,
	'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);	

 
?>