<?php
// get these values from your DB.
require_once('config.php');

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: GET");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");



if ($_SERVER["REQUEST_METHOD"] == "GET")
{
	
	$site_cd 	= $_REQUEST['site_cd'];
	$page 		= $_REQUEST['page'];
	$pageSize 	= 10;


$sql_3="Select
			wko_mst.site_cd,
			wko_mst.RowID ,
			wko_mst_wo_no				= COALESCE(wko_mst_wo_no,''),
			wko_mst_status 				= COALESCE(wrk_sts_desc,''),
			wko_mst_org_date			= COALESCE(wko_mst_org_date,''),
			wko_mst_descs				= COALESCE(wko_mst_descs,''),
			wko_mst_originator 			= COALESCE(CASE WHEN emp_mst1.emp_mst_name IS NULL THEN wko_mst_originator WHEN emp_mst1.emp_mst_name IS NOT NULL THEN emp_mst1.emp_mst_name END,'') ,
			wko_mst_assetno 			= COALESCE(wko_mst_assetno,''),
			ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),
			wko_mst_work_area 			= COALESCE(mst_war_desc+' / '+ast_loc_desc+' / '+ast_lvl_desc,''),
			wko_mst_asset_location 		= COALESCE(ast_loc_desc,''),
			wko_mst_asset_level			= COALESCE(ast_lvl_desc,'') ,
			wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
			wko_mst_type				= COALESCE(wko_mst_type,''),
			prm_grp_desc 				= '',
			wko_det_work_grp			= COALESCE(wrk_grp_desc,''),
			wko_det_assign_to 			= COALESCE(emp_mst2.emp_mst_name,''),
			assigndate					= COALESCE(wko_ls7.audit_date,''),
			wko_mst_create_date 		= COALESCE(wko_mst.wko_mst_create_date,''),
			wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
			wko_mst_due_date			= COALESCE(wko_mst_due_date,''),
			wko_mst_chg_costcenter		= COALESCE(wko_mst_chg_costcenter + ' : ' + cf_cost_center.descs,''),  
			wko_det_laccount			= COALESCE(wko_det_laccount + ' : ' + cf_account_1.descs,''),
			wko_det_caccount 			= COALESCE(wko_det_caccount + ' : ' + cf_account_2.descs ,''),
			wko_det_maccount			= COALESCE(wko_det_maccount + ' : ' + cf_account_3.descs,'')

			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	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 emp_mst1(NOLOCK)
			ON			wko_mst.site_cd = emp_mst1.site_cd
			AND			wko_mst.wko_mst_originator = emp_mst1.emp_mst_empl_id


			LEFT
			OUTER
			JOIN		emp_mst emp_mst2(NOLOCK)
			ON			wko_det.site_cd = emp_mst2.site_cd
			AND			wko_det.wko_det_assign_to = emp_mst2.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		ast_sts (NOLOCK)
			ON			wko_mst.site_cd = ast_sts.site_cd
			AND			wko_mst.wko_mst_asset_status = ast_sts.ast_sts_status

			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		wrk_typ (NOLOCK)
			ON			wko_mst.site_cd = wrk_typ.site_cd
			AND			wko_det.wko_det_work_type = wrk_typ.wrk_typ_typ_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

			LEFT
			OUTER
			JOIN		wrk_grp (NOLOCK)
			ON			wko_det.site_cd = wrk_grp.site_cd
			AND			wko_det.wko_det_work_grp = wrk_grp.wrk_grp_grp_cd


			LEFT
			OUTER
			JOIN		wko_ls7(NOLOCK)
			ON			wko_mst.site_cd = wko_ls7.site_cd
			AND			wko_mst.rowid = wko_ls7.mst_rowid
			AND			wko_det.site_cd = wko_ls7.site_cd
			AND			wko_det_assign_to = wko_ls7.wko_ls7_emp_id
			AND			wko_ls7.RowID = (SELECT MAX(rowid) FROM wko_ls7 (NOLOCK)
								 WHERE wko_ls7.site_cd = wko_mst.site_cd
								 AND   wko_ls7.mst_rowid = wko_mst.rowid
								 AND   wko_ls7.site_cd = wko_det.site_cd
								 AND   wko_ls7.wko_ls7_emp_id = wko_det.wko_det_assign_to)

			WHERE	wko_mst.site_cd = '".$site_cd."' 
			AND 	wrk_sts_typ_cd NOT IN ('CLOSE', 'COMPLETE', 'FORCE-CLOSE', 'CANCEL')";
			
			If (!empty($asset_shortdesc)){
				$sql_3 = $sql_3."ORDER BY wko_mst_create_date desc OFFSET ".($page  -1)*$pageSize." ROWS FETCH NEXT ".$pageSize." ROWS ONLY";
			}else{
				$sql_3 = $sql_3."UNION ALL
				
				Select

					wko_mst.site_cd,
			wko_mst.RowID ,
			wko_mst_wo_no				= COALESCE(wko_mst_wo_no,''),
			wko_mst_status 				= COALESCE(wrk_sts_desc,''),
			wko_mst_org_date			= COALESCE(wko_mst_org_date,''),
			wko_mst_descs				= COALESCE(wko_mst_descs,''),
			wko_mst_originator 			= COALESCE(CASE WHEN emp_mst1.emp_mst_name IS NULL THEN wko_mst_originator WHEN emp_mst1.emp_mst_name IS NOT NULL THEN emp_mst1.emp_mst_name END,'') ,
			wko_mst_assetno 			= COALESCE(wko_mst_assetno,''),
			ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),
			wko_mst_work_area 			= COALESCE(mst_war_desc+' / '+ast_loc_desc+' / '+ast_lvl_desc,''),
			wko_mst_asset_location 		= COALESCE(ast_loc_desc,''),
			wko_mst_asset_level			= COALESCE(ast_lvl_desc,'') ,
			wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
			wko_mst_type				= COALESCE(wko_mst_type,''),
			prm_grp_desc 				= COALESCE(prm_grp_desc,''),
			wko_det_work_grp			= COALESCE(wrk_grp_desc,''),
			wko_det_assign_to 			= COALESCE(emp_mst2.emp_mst_name,''),
			assigndate					= COALESCE(wko_ls7.audit_date,''),
			wko_mst_create_date 		= COALESCE(wko_mst.wko_mst_create_date,''),
			wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
			wko_mst_due_date			= COALESCE(wko_mst_due_date,''),
			wko_mst_chg_costcenter		= COALESCE(wko_mst_chg_costcenter + ' : ' + cf_cost_center.descs,''),  
			wko_det_laccount			= COALESCE(wko_det_laccount + ' : ' + cf_account_1.descs,''),
			wko_det_caccount 			= COALESCE(wko_det_caccount + ' : ' + cf_account_2.descs ,''),
			wko_det_maccount			= COALESCE(wko_det_maccount + ' : ' + cf_account_3.descs,'')
			

					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')

							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		prm_grp (NOLOCK)
							ON			wko_mst.site_cd = prm_grp.site_cd
							AND			wko_mst.wko_mst_pm_grp = prm_grp.prm_grp_grp_cd

							LEFT
							OUTER
							JOIN		emp_mst emp_mst1(NOLOCK)
							ON			wko_mst.site_cd = emp_mst1.site_cd
							AND			wko_mst.wko_mst_originator = emp_mst1.emp_mst_empl_id


							LEFT
							OUTER
							JOIN		emp_mst emp_mst2(NOLOCK)
							ON			wko_det.site_cd = emp_mst2.site_cd
							AND			wko_det.wko_det_assign_to = emp_mst2.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		ast_sts (NOLOCK)
							ON			wko_mst.site_cd = ast_sts.site_cd
							AND			wko_mst.wko_mst_asset_status = ast_sts.ast_sts_status

							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		wrk_typ (NOLOCK)
							ON			wko_mst.site_cd = wrk_typ.site_cd
							AND			wko_det.wko_det_work_type = wrk_typ.wrk_typ_typ_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

							LEFT
							OUTER
							JOIN		wrk_grp (NOLOCK)
							ON			wko_det.site_cd = wrk_grp.site_cd
							AND			wko_det.wko_det_work_grp = wrk_grp.wrk_grp_grp_cd


							LEFT
							OUTER
							JOIN		wko_ls7(NOLOCK)
							ON			wko_mst.site_cd = wko_ls7.site_cd
							AND			wko_mst.rowid = wko_ls7.mst_rowid
							AND			wko_det.site_cd = wko_ls7.site_cd
							AND			wko_det_assign_to = wko_ls7.wko_ls7_emp_id
							AND			wko_ls7.RowID = (SELECT MAX(rowid) FROM wko_ls7 (NOLOCK)
												 WHERE wko_ls7.site_cd = wko_mst.site_cd
												 AND   wko_ls7.mst_rowid = wko_mst.rowid
												 AND   wko_ls7.site_cd = wko_det.site_cd
												 AND   wko_ls7.wko_ls7_emp_id = wko_det.wko_det_assign_to)

					WHERE	wko_mst.site_cd = '".$site_cd."' 
					AND 	wrk_sts_typ_cd NOT IN ('CLOSE', 'COMPLETE', 'FORCE-CLOSE', 'CANCEL') 
					ORDER BY wko_mst_create_date desc OFFSET ".($page  -1)*$pageSize." ROWS FETCH NEXT ".$pageSize." ROWS ONLY";
			}

		//echo  $sql;
		$stmt_3 = sqlsrv_query( $conn, $sql_3);

		if( !$stmt_3 ) {

			 $error_message = "Error selecting table All Type";
			 returnError($error_message);
			 die( print_r( sqlsrv_errors(), true));

		}

		$json= array();

		do {
			while ($row = sqlsrv_fetch_array($stmt_3, SQLSRV_FETCH_ASSOC)) {

				$json[] = $row;


			}
		} while ( sqlsrv_next_result($stmt_3) );
			 
}

if ($stmt_3) {

	sqlsrv_free_stmt( $stmt_3);
	sqlsrv_close( $conn);


	returnData($json);	
	
}


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


 
?>