<?php

namespace App\Http\Controllers;
use DB;
use Illuminate\Http\Request;

class HomeController extends Controller
{

	public function index(){
		//return view("sss.display2");
		header( "refresh: 1; url=/asi/display/screen2" );
	}

	public function loadData(){	
		$date = date("Y-m-d");
		//$date = '2023-08-19';
		$data = $this->loadScreen1($date);
		echo json_encode($data);
	}

	public function loadScreen1($date){	

		// code date current 
		// -----------------
		// $date = date("Y-m-d");
		// $dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date))); 
		// $datenow = date("Y-m-d h:i:sa");
		// $dateAll = date('Y-m-d')." 07:59:00.000";
		// $datemid = $dateOne." 08:00:00.000";

		// $dateshow = date('d-m-Y');
		// $timeshow = date('h:i:sa');
		// $data["dateshow"] = $dateshow;
		// $data["timeshow"] = $timeshow;

		// $data['datestring'] = date("m")."'M ".date("d")."'D (".date("D").")";

		// =======================================================================================


		// code fix date for testing 
		// -------------------------
		//$date = "2023-09-07"; // change date here.
		//$date = date("Y-m-d");
		$dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date)));  
		$datenow = $date." ".date("h:i:sa");
		$dateAll = "$date 07:59:00.000";
		$datemid = $dateOne." 08:00:00.000";
		
		$data["dateshow"] = date('d-m-Y', strtotime($date));
		$data["timeshow"] = date('h:i:sa');

		$data['datestring'] = date("m",strtotime($date))."'M ".date("d",strtotime($date))."'D (".date("D",strtotime($date)).")";

		// ========================================================================================

		$data["db_capacity"] =DB::SELECT("select SUM(Qty) as db_capacity 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datemid."'");

		// today plan pcs
		$data["pdieFull"] =DB::SELECT("select SUM(qty_in_lot) as pdieFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datemid."'");
		
		$data["pdieNow"] =DB::SELECT("select SUM(qty_in_lot) as pdieNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datenow."'");

		$data["povenFull"] =DB::SELECT("select SUM(qty_in_lot) as povenFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_diebond_plan_start_dt >= '".$dateAll."' AND oven_diebond_plan_start_dt <= '".$datemid."'");

		$data["povenNow"] =DB::SELECT("select SUM(qty_in_lot) as povenNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_diebond_plan_start_dt >= '".$dateAll."' AND oven_diebond_plan_start_dt <= '".$datenow."'");

		$data["pwireFull"] =DB::SELECT("select SUM(qty_in_lot) as pwireFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE wirebond_plan_start_dt >= '".$dateAll."' AND wirebond_plan_start_dt <= '".$datemid."'");

		$data["pwireNow"] =DB::SELECT("select SUM(qty_in_lot) as pwireNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE wirebond_plan_start_dt >=  '".$dateAll."' AND wirebond_plan_start_dt <= '".$datenow."'");

		$data["pbondinFull"] =DB::SELECT("select SUM(qty_in_lot) as pbondinFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE bonding_ins_plan_start_dt >= '".$dateAll."' AND bonding_ins_plan_start_dt <= '".$datemid."'");
		
		$data["pbondinNow"] =DB::SELECT("select SUM(qty_in_lot) as pbondinNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE bonding_ins_plan_start_dt >= '".$dateAll."' AND bonding_ins_plan_start_dt <= '".$datenow."'");

		$data["pmoldFull"] =DB::SELECT("select SUM(qty_in_lot) as pmoldFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE mold_plan_start_dt >= '".$dateAll."' AND mold_plan_start_dt <= '".$datemid."'");

		$data["pmoldNow"] =DB::SELECT("select SUM(qty_in_lot) as pmoldNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE mold_plan_start_dt >= '".$dateAll."' AND mold_plan_start_dt <= '".$datenow."'");

		$data["povenmoldFull"] =DB::SELECT("select SUM(qty_in_lot) as povenmoldFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_mold_plan_start_dt >= '".$dateAll."' AND oven_mold_plan_start_dt <= '".$datemid."'");

		$data["povenmoldNow"] =DB::SELECT("select SUM(qty_in_lot) as povenmoldNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_mold_plan_start_dt >= '".$dateAll."' AND oven_mold_plan_start_dt <= '".$datenow."'");

		$data["pdicerFull"] =DB::SELECT("select SUM(qty_in_lot) as pdicerFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE dicer_plan_start_dt >= '".$dateAll."' AND dicer_plan_start_dt <= '".$datemid."'");

		$data["pdicerNow"] =DB::SELECT("select SUM(qty_in_lot) as pdicerNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE dicer_plan_start_dt >= '".$dateAll."' AND dicer_plan_start_dt <= '".$datenow."'");

		$data["pappeaFull"] =DB::SELECT("select SUM(qty_in_lot) as pappeaFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE appearance_plan_start_dt >= '".$dateAll."' AND appearance_plan_start_dt <= '".$datemid."'");

		$data["pappeaNow"] =DB::SELECT("select SUM(qty_in_lot) as pappeaNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE appearance_plan_start_dt >= '".$dateAll."' AND appearance_plan_start_dt <= '".$datenow."'");

		$data["pcalssFull"] =DB::SELECT("select SUM(qty_in_lot) as pcalssFull 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE classify_plan_start_dt >= '".$dateAll."' AND classify_plan_start_dt <= '".$datemid."'");

		$data["pcalssNow"] =DB::SELECT("select SUM(qty_in_lot) as pcalssNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE classify_plan_start_dt >= '".$dateAll."' AND classify_plan_start_dt <= '".$datenow."'");


		// today plan lot
		$data["pdieFulllot"] =DB::SELECT("select count(lot_no) as pdieFulllot 		
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datemid."'");
		
		$data["pdieNowlot"] =DB::SELECT("select count(lot_no) as pdieNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datenow."'");

		$data["povenFulllot"] =DB::SELECT("select count(lot_no) as povenFulllot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_diebond_plan_start_dt >= '".$dateAll."' AND oven_diebond_plan_start_dt <= '".$datemid."'");

		$data["povenNowlot"] =DB::SELECT("select count(lot_no) as povenNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_diebond_plan_start_dt >= '".$dateAll."' AND oven_diebond_plan_start_dt <= '".$datenow."'");

		$data["pwireFulllot"] =DB::SELECT("select count(lot_no) as pwireFulllot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE wirebond_plan_start_dt >= '".$dateAll."' AND wirebond_plan_start_dt <= '".$datemid."'");

		$data["pwireNowlot"] =DB::SELECT("select count(lot_no) as pwireNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE wirebond_plan_start_dt >=  '".$dateAll."' AND wirebond_plan_start_dt <= '".$datenow."'");

		$data["pbondinFulllot"] =DB::SELECT("select count(lot_no) as pbondinFulllot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE bonding_ins_plan_start_dt >= '".$dateAll."' AND bonding_ins_plan_start_dt <= '".$datemid."'");
		
		$data["pbondinNowlot"] =DB::SELECT("select count(lot_no) as pbondinNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE bonding_ins_plan_start_dt >= '".$dateAll."' AND bonding_ins_plan_start_dt <= '".$datenow."'");

		$data["pmoldFulllot"] =DB::SELECT("select count(lot_no) as pmoldFulllot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE mold_plan_start_dt >= '".$dateAll."' AND mold_plan_start_dt <= '".$datemid."'");

		$data["pmoldNowlot"] =DB::SELECT("select count(lot_no) as pmoldNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE mold_plan_start_dt >= '".$dateAll."' AND mold_plan_start_dt <= '".$datenow."'");

		$data["povenmoldFulllot"] =DB::SELECT("select count(lot_no) as povenmoldFulllot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_mold_plan_start_dt >= '".$dateAll."' AND oven_mold_plan_start_dt <= '".$datemid."'");

		$data["povenmoldNowlot"] =DB::SELECT("select count(lot_no) as povenmoldNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_mold_plan_start_dt >= '".$dateAll."' AND oven_mold_plan_start_dt <= '".$datenow."'");

		$data["pdicerFulllot"] =DB::SELECT("select count(lot_no) as pdicerFulllot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE dicer_plan_start_dt >= '".$dateAll."' AND dicer_plan_start_dt <= '".$datemid."'");

		$data["pdicerNowlot"] =DB::SELECT("select count(lot_no) as pdicerNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE dicer_plan_start_dt >= '".$dateAll."' AND dicer_plan_start_dt <= '".$datenow."'");

		$data["pappeaFulllot"] =DB::SELECT("select count(lot_no) as pappeaFulllot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE appearance_plan_start_dt >= '".$dateAll."' AND appearance_plan_start_dt <= '".$datemid."'");

		$data["pappeaNowlot"] =DB::SELECT("select count(lot_no) as pappeaNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE appearance_plan_start_dt >= '".$dateAll."' AND appearance_plan_start_dt <= '".$datenow."'");

		$data["pcalssFulllot"] =DB::SELECT("select count(lot_no) as pcalssFulllot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE classify_plan_start_dt >= '".$dateAll."' AND classify_plan_start_dt <= '".$datemid."'");

		$data["pcalssNowlot"] =DB::SELECT("select count(lot_no) as pcalssNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE classify_plan_start_dt >= '".$dateAll."' AND classify_plan_start_dt <= '".$datenow."'");			
		

		//input pcs

		$data["indieNow"] =DB::SELECT("select SUM(qty_in_lot) as indieNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_act_start_dt >= '".$dateAll."' AND diebond_act_start_dt <= '".$datenow."'");

		$data["inovenNow"] =DB::SELECT("select SUM(diebond_output) as inovenNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_diebond_act_start_dt >= '".$dateAll."' AND oven_diebond_act_start_dt <= '".$datenow."'");

		$data["inwirebondNow"] =DB::SELECT("select SUM(diebond_output) as inwirebondNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE wirebond_act_start_dt >= '".$dateAll."' AND wirebond_act_start_dt <= '".$datenow."'");

		$data["inbondingNow"] =DB::SELECT("select SUM(wirebond_output ) as inbondingNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE bonding_ins_act_start_dt >= '".$dateAll."' AND bonding_ins_act_start_dt <= '".$datenow."'");

		$data["inmoldNow"] =DB::SELECT("select SUM(bonding_ins_output) as inmoldNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE mold_act_start_dt >= '".$dateAll."' AND mold_act_start_dt <= '".$datenow."'");

		$data["inovenmoldNow"] =DB::SELECT("select SUM(mold_output) as inovenmoldNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_mold_act_start_dt >= '".$dateAll."' AND oven_mold_act_start_dt <= '".$datenow."'");

		$data["indicerNow"] =DB::SELECT("select SUM(mold_output) as indicerNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE dicer_act_start_dt >= '".$dateAll."' AND dicer_act_start_dt <= '".$datenow."'");

		$data["inappearaNow"] =DB::SELECT("select SUM(dicer_output) as inappearaNow 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE appearance_act_start_dt >= '".$dateAll."' AND appearance_act_start_dt <= '".$datenow."'");
	
		$data["incalssNow"] =DB::SELECT("select SUM(input) as incalssNow
		from [WMSASIDB].[dbo].[ClassifyLot] WHERE classify_lot IN
		(select classify_lot
		from [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE classify_act_start_dt >= '".$dateAll."' AND classify_act_start_dt <= '".$datenow."')");

		// input Lot

		$data["indieNowlot"] =DB::SELECT("select count(lot_no) as indieNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_act_start_dt >= '".$dateAll."' AND diebond_act_start_dt <= '".$datenow."'");
		
		$data["inovenNowlot"] =DB::SELECT("select COUNT(lot_no) as inovenNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_diebond_act_start_dt >= '".$dateAll."' AND oven_diebond_act_start_dt <= '".$datenow."'");

		$data["inwirebondNowlot"] =DB::SELECT("select COUNT(lot_no) as inwirebondNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE wirebond_act_start_dt >= '".$dateAll."' AND wirebond_act_start_dt <= '".$datenow."'");

		$data["inbondingNowlot"] =DB::SELECT("select COUNT(lot_no) as inbondingNowlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE bonding_ins_act_start_dt >= '".$dateAll."' AND bonding_ins_act_start_dt <= '".$datenow."'");

		$data["inmoldNowlot"] =DB::SELECT("select COUNT(lot_no)  as inmoldNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE mold_act_start_dt >= '".$dateAll."' AND mold_act_start_dt <= '".$datenow."'");

		$data["inovenmoldNowlot"] =DB::SELECT("select COUNT(lot_no) as inovenmoldNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_mold_act_start_dt >= '".$dateAll."' AND oven_mold_act_start_dt <= '".$datenow."'");

		$data["indicerNowlot"] =DB::SELECT("select COUNT(lot_no) as indicerNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE dicer_act_start_dt >= '".$dateAll."' AND dicer_act_start_dt <= '".$datenow."'");

		$data["inappearaNowlot"] =DB::SELECT("select COUNT(lot_no) as inappearaNowlot 
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE appearance_act_start_dt >= '".$dateAll."' AND appearance_act_start_dt <= '".$datenow."'");
	
		$data["incalssNowlot"] =DB::SELECT("select COUNT(classify_lot) as incalssNowlot
		from [WMSASIDB].[dbo].[ClassifyLot] WHERE classify_lot IN
		(select classify_lot
		from [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE classify_act_start_dt >= '".$dateAll."' AND classify_act_start_dt <= '".$datenow."')");


		//output
		$data["outdieend"] =DB::SELECT("select SUM(diebond_output) as outdieend
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_act_end_dt >= '".$dateAll."'  AND diebond_act_end_dt <= '".$datenow."'");

		$data["outovenend"] =DB::SELECT("select SUM(diebond_output) as outovenend
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_diebond_act_end_dt >= '".$dateAll."' AND oven_diebond_act_end_dt <= '".$datenow."'");

		$data["outwirebondend"] =DB::SELECT("select SUM(diebond_output) as outwirebondend
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE wirebond_act_end_dt >= '".$dateAll."' AND wirebond_act_end_dt <= '".$datenow."'");

		$data["outbondingend"] =DB::SELECT("select SUM(wirebond_output) as outbondingend
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE bonding_ins_act_end_dt >= '".$dateAll."' AND bonding_ins_act_end_dt <= '".$datenow."'");

		$data["outmoldend"] =DB::SELECT("select SUM(wirebond_output) as outmoldend
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE mold_act_end_dt >= '".$dateAll."' AND mold_act_end_dt <= '".$datenow."'");

		$data["outoven_moldend"] =DB::SELECT("select SUM(mold_output) as outoven_moldend
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_mold_act_end_dt >= '".$dateAll."' AND oven_mold_act_end_dt <= '".$datenow."'");

		$data["outdicer_actend"] =DB::SELECT("select SUM(mold_output) as outdicer_actend
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE dicer_act_end_dt >= '".$dateAll."' AND dicer_act_end_dt <= '".$datenow."'");

		$data["outappearanceend"] =DB::SELECT("select SUM(dicer_output) as outappearanceend
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE appearance_act_end_dt >= '".$dateAll."' AND appearance_act_end_dt <= '".$datenow."'");

		$data["outclassifyend"] =DB::SELECT("select SUM(output) as outclassifyend
		from [WMSASIDB].[dbo].[ClassifyLot] WHERE classify_lot IN
		(select classify_lot
		from [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE classify_act_end_dt >= '".$dateAll."' AND classify_act_end_dt <= '".$datenow."')");


		//output lot	
		$data["outdieendlot"] =DB::SELECT("select COUNT(lot_no) as outdieendlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE diebond_act_end_dt >= '".$dateAll."'  AND diebond_act_end_dt <= '".$datenow."'");

		$data["outovenendlot"] =DB::SELECT("select COUNT(lot_no) as outovenendlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_diebond_act_end_dt >= '".$dateAll."' AND oven_diebond_act_end_dt <= '".$datenow."'");

		$data["outwirebondendlot"] =DB::SELECT("select COUNT(lot_no) as outwirebondendlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE wirebond_act_end_dt >= '".$dateAll."' AND wirebond_act_end_dt <= '".$datenow."'");

		$data["outbondingendlot"] =DB::SELECT("select COUNT(lot_no) as outbondingendlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE bonding_ins_act_end_dt >= '".$dateAll."' AND bonding_ins_act_end_dt <= '".$datenow."'");

		$data["outmoldendlot"] =DB::SELECT("select COUNT(lot_no) as outmoldendlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE mold_act_end_dt >= '".$dateAll."' AND mold_act_end_dt <= '".$datenow."'");

		$data["outoven_moldendlot"] =DB::SELECT("select COUNT(lot_no) as outoven_moldendlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE oven_mold_act_end_dt >= '".$dateAll."' AND oven_mold_act_end_dt <= '".$datenow."'");

		$data["outdicer_actendlot"] =DB::SELECT("select COUNT(lot_no) as outdicer_actendlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE dicer_act_end_dt >= '".$dateAll."' AND dicer_act_end_dt <= '".$datenow."'");

		$data["outappearanceendlot"] =DB::SELECT("select COUNT(lot_no) as outappearanceendlot
		from [WMSASIDB].[dbo].[ProductionPlan]
		WHERE appearance_act_end_dt >= '".$dateAll."' AND appearance_act_end_dt <= '".$datenow."'");

		$data["outclassifyendlot"] =DB::SELECT("select COUNT(classify_lot) as outclassifyendlot
		from [WMSASIDB].[dbo].[ClassifyLot] WHERE classify_lot IN
		(select classify_lot
		from [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE classify_act_end_dt >= '".$dateAll."' AND classify_act_end_dt <= '".$datenow."')");		


		//NG

		$data["ngbonding"] =DB::SELECT("select SUM(qty) as ngbonding
		from [WMSASIDB].[dbo].[NGFactorList] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'BONDING_INS' ");

		$data["ngmold"] =DB::SELECT("select SUM(qty) as ngmold
		from [WMSASIDB].[dbo].[NGFactorList] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'MOLD' ");

		$data["ngdicer"] =DB::SELECT("select SUM(qty) as ngdicer
		from [WMSASIDB].[dbo].[NGFactorList] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'DICER' ");

		$data["ngappear"] =DB::SELECT("select SUM(qty) as ngappear
		from [WMSASIDB].[dbo].[NGFactorList] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'APPEARANCE' ");

		$data["ngclassify"] =DB::SELECT("select SUM(qty) as ngclassify from [WMSASIDB].[dbo].[ClassifyOutput]
		where output_type = 'NG' AND create_dt >='".$dateAll."'  AND create_dt <= '".$datenow."'");
		 
		// **** top3 *****
		 		/// BONDING_INS
		$data["t3bonding"] =DB::SELECT("select top 2 ng_code,ng_name, SUM(qty) as sumqty FROM [WMSASIDB].[dbo].[NGFactorList] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."'
		AND process = 'BONDING_INS'
		GROUP BY ng_code, ng_name ORDER BY sumqty desc");	

		$data["t3mold"] =DB::SELECT("select top 2 ng_code,ng_name, SUM(qty) as sumqty FROM [WMSASIDB].[dbo].[NGFactorList] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."'
		AND process = 'MOLD'
		GROUP BY ng_code, ng_name ORDER BY sumqty desc");
		
		$data["t3dicer"] =DB::SELECT("select top 2 ng_code,ng_name, SUM(qty) as sumqty FROM [WMSASIDB].[dbo].[NGFactorList] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."'
		AND process = 'DICER'
		GROUP BY ng_code, ng_name ORDER BY sumqty desc");
		
		$data["t3appea"] =DB::SELECT("select top 2 ng_code,ng_name, SUM(qty) as sumqty FROM [WMSASIDB].[dbo].[NGFactorList] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."'
		AND process = 'APPEARANCE'
		GROUP BY ng_code, ng_name ORDER BY sumqty desc");	

		$data["t3classi"] =DB::SELECT(" select top 2 output_name, SUM(qty) as sumng 
		from [WMSASIDB].[dbo].[ClassifyOutput] 
		where create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND output_type = 'NG'
		GROUP BY output_name ORDER BY sumng desc");

		// dd($data);

		// remark 

		$data["remarkdie"] =DB::SELECT("select top 2 lot_no, dienond_remark AS remark , count(dienond_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE create_dt >= '".$dateAll."'  AND create_dt <= '".$datenow."' AND process = 'DIE_BOND' AND dienond_remark is not null
		GROUP BY lot_no, dienond_remark ORDER BY total_remark desc");
		
		$data["remarkoven"] =DB::SELECT("select top 2 lot_no, oven_diebond_remark AS remark , count(oven_diebond_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE  create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'OVEN_DIE_BOND' AND oven_diebond_remark is not null
		GROUP BY lot_no, oven_diebond_remark ORDER BY total_remark desc");

		$data["remarkwirebond"] =DB::SELECT("select top 2 lot_no, wirebond_remark AS remark , count(wirebond_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'WIRE_BOND' AND wirebond_remark is not null
		GROUP BY lot_no, wirebond_remark ORDER BY total_remark desc");

		// $data["remarkbonding"] = array();

		$data["remarkmold"] =DB::SELECT("select top 2 lot_no, mold_remark AS remark , count(mold_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'MOLD' AND mold_remark is not null
		GROUP BY lot_no, mold_remark ORDER BY total_remark desc");

		$data["remarkovenmold"] =DB::SELECT("select top 2 lot_no, oven_mold_remark AS remark , count(oven_mold_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'OVEN_MOLD' AND oven_mold_remark is not null
		GROUP BY lot_no, oven_mold_remark ORDER BY total_remark desc");

		$data["remarkdicer"] =DB::SELECT("select top 2 lot_no, dicer_remark AS remark , count(dicer_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'DICER' AND dicer_remark is not null
		GROUP BY lot_no, dicer_remark ORDER BY total_remark desc");

		$data["remarkappearance"] = array();

		$data["remarkclassif"] =DB::SELECT("select top 2 lot_no, classify_remark AS remark , count(classify_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan] 
		WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'CLASSIFY' AND classify_remark is not null
		GROUP BY lot_no, classify_remark ORDER BY total_remark desc");


		return $data;

		//echo json_encode($data);
	}

	public function screen1(){
		return view("display.screen1");
	}

	public function loadDataScreen2(){
		$data = $this->loadScreen2();
		echo json_encode($data);
	}	

	public function loadScreen2(){
		// code date current 
		// -----------------
		// $date = date("Y-m-d");
		// $dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date))); 
		// $datenow = date("Y-m-d h:i:sa");
		// $dateAll = date('Y-m-d')." 07:59:00.000";
		// $datemid = $dateOne." 08:00:00.000";

		// $dateshow = date('d-m-Y');
		// $timeshow = date('h:i:sa');
		// $data["dateshow"] = $dateshow;
		// $data["timeshow"] = $timeshow;

		// $data['datestring'] = date("m")."'M ".date("d")."'D (".date("D").")";

		// =======================================================================================


		// code fix date for testing 
		// -------------------------
		$date = date("Y-m-d");
		//$date = '2023-09-21';
		$timeshow = date("h:i:sa");
		$H_check = date("H");
		//$H_check = 20;
		if( $H_check>=8 && $H_check<20 ){
			$dateOne = $date;
			$dateAll = "$date 08:00:00.000";
			$datemid = $dateOne." 19:59:59.000";
			// $dateOne = date("Y-m-d", strtotime("-1 day", strtotime($date))); 
			// $dateAll = "$dateOne 20:00:00.000";
			// $datemid = $date." 19:59:59.000";
			//$list_hours = array("08:00","09:00","10:00","11:00","12:00","13:00","14:00","15:00","16:00","17:00","18:00","19:00");
		}else{
			$dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date)));  
			$dateAll = "$date 20:00:00.000";
			$datemid = $dateOne." 07:59:59.000";
			// $dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date)));  
			// $dateAll = "$date 08:00:00.000";
			// $datemid = $dateOne." 07:59:59.000";
			//$list_hours = array("20:00","21:00","22:00","23:00","00:00","01:00","02:00","03:00","04:00","05:00","06:00","07:00");
		}
		
		// $dateOne = $date; 
		// $datemid = date("Y-m-d", strtotime("+1 day", strtotime($date)))." 07:59:59.000";

		$datenow = $date." ".$timeshow;
		$data["day_cal"] = $date;
		$data["timeshow"] = $timeshow;
		$data["dateshow"] = date('d M Y', strtotime($date));

		$data["dateAll"] = $dateAll;
		$data["datemid"] = $datemid;
		$data['H_check'] = $H_check;
		//$data['datestring'] = date("m",strtotime($date))."'M ".date("d",strtotime($date))."'D (".date("D",strtotime($date)).")";

		// ========================================================================================

		$data['lot_not_start'] = DB::SELECT("SELECT ISNULL(SUM(IIF(ISNULL(diebond_act_start_dt,1)=1,1,0)),0) AS notstart
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE diebond_act_start_dt >= '$dateAll' AND diebond_act_start_dt <= '$datemid'");

		$data['lot_finish'] = DB::SELECT("SELECT  ISNULL(SUM(IIF(ISNULL(classify_act_end_dt,0)=0,0,1)),0) AS finish
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE (diebond_act_start_dt >= '$dateAll' AND diebond_act_start_dt <= '$datemid') AND ( classify_act_end_dt >= '$dateAll' AND classify_act_end_dt <= '$datemid')");


		$SQL = "    SELECT lot_no 
					FROM (
							select *, 'diebond' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE diebond_act_start_dt >= '$dateAll' AND diebond_act_start_dt <= '$datemid'
					union
							SELECT  *, 'oven_diebond' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE oven_diebond_act_start_dt >= '$dateAll' AND oven_diebond_act_start_dt <= '$datemid'
					union	
							SELECT  *, 'wirebond' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE wirebond_act_start_dt >= '$dateAll' AND wirebond_act_start_dt <= '$datemid'
					union
							SELECT  *, 'bonding_ins' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE bonding_ins_act_start_dt >= '$dateAll' AND bonding_ins_act_start_dt <= '$datemid'
					union	
							SELECT  *, 'mold' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE mold_act_start_dt >= '$dateAll' AND mold_act_start_dt <= '$datemid'
					union
							SELECT  *, 'over_mold' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE oven_mold_act_start_dt >= '$dateAll' AND oven_mold_act_start_dt <= '$datemid'
					union	
							SELECT  *, 'dicer' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE dicer_act_start_dt >= '$dateAll' AND dicer_act_start_dt <= '$datemid'
					union
							SELECT  *, 'appearance' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE appearance_act_start_dt >= '$dateAll' AND appearance_act_start_dt <= '$datemid'
					union	
							SELECT  *, 'classify' as 'job_type'
							FROM [WMSASIDB].[dbo].[ProductionPlan]
							WHERE classify_act_start_dt >= '$dateAll' AND classify_act_start_dt <= '$datemid'
							
					) tmp  GROUP BY lot_no 
				";	

		$data['lot_no'] =	 DB::SELECT($SQL); 

		$data['lot_data'] = DB::SELECT("SELECT  a.lot_no, 
												diebond_plan_start_dt AS diebond_plan,  
												IIF(ISNULL(diebond_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), diebond_plan_start_dt, 121),':00' ) ) AS block_diebond_plan,
												IIF(ISNULL(diebond_plan_start_dt,'')='','', CONVERT(VARCHAR(5), diebond_plan_start_dt, 114 )) AS diebond_plan_time,

												diebond_act_start_dt AS diebond_act, 
												ISNULL(DATEDIFF(hour, diebond_plan_start_dt, diebond_act_start_dt),99) AS diebond_plan_diff,
												IIF(ISNULL(diebond_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), diebond_act_start_dt, 121),':00' ) ) AS block_diebond_act,
												IIF(ISNULL(diebond_act_start_dt,'')='','', CONVERT(VARCHAR(5), diebond_act_start_dt, 114)) AS diebond_act_time,

												oven_diebond_plan_start_dt AS oven_diebond_plan, 
												IIF(ISNULL(oven_diebond_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), oven_diebond_plan_start_dt, 121),':00' ) ) AS block_ovediebond_plan,
												IIF(ISNULL(oven_diebond_plan_start_dt,'')='','', CONVERT(VARCHAR(5), oven_diebond_plan_start_dt, 114)) AS ovediebond_plan_time,

												oven_diebond_act_start_dt AS oven_diebond_act,
												ISNULL(DATEDIFF(hour, oven_diebond_plan_start_dt, oven_diebond_act_start_dt),99) AS oven_diebond_plan_diff,
												IIF(ISNULL(oven_diebond_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), oven_diebond_act_start_dt, 121),':00' ) ) AS block_ovediebond_act,
												IIF(ISNULL(oven_diebond_act_start_dt,'')='','', CONVERT(VARCHAR(5), oven_diebond_act_start_dt, 114)) AS ovediebond_act_time,

												wirebond_plan_start_dt AS wirebond_plan,
												IIF(ISNULL(wirebond_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), wirebond_plan_start_dt, 121),':00' ) ) AS block_wirebond_plan,
												IIF(ISNULL(wirebond_plan_start_dt,'')='','', CONVERT(VARCHAR(5), wirebond_plan_start_dt, 114)) AS wirebond_plan_time,

												wirebond_act_start_dt AS wirebond_act,
												ISNULL(DATEDIFF(hour, wirebond_plan_start_dt, wirebond_act_start_dt),99) AS wirebond_plan_diff,
												IIF(ISNULL(wirebond_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), wirebond_act_start_dt, 121),':00' ) ) AS block_wirebond_act,
												IIF(ISNULL(wirebond_act_start_dt,'')='','', CONVERT(VARCHAR(5), wirebond_act_start_dt, 114)) AS wirebond_act_time,

												bonding_ins_plan_start_dt AS bonding_ins_plan,
												IIF(ISNULL(bonding_ins_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), bonding_ins_plan_start_dt, 121),':00' ) ) AS block_bondingins_plan,
												IIF(ISNULL(bonding_ins_plan_start_dt,'')='','', CONVERT(VARCHAR(5), bonding_ins_plan_start_dt, 114)) AS bondingins_plan_time,

												bonding_ins_act_start_dt AS bonding_ins_act,
												ISNULL(DATEDIFF(hour, bonding_ins_plan_start_dt, bonding_ins_act_start_dt),99) AS bonding_ins_plan_diff,
												IIF(ISNULL(bonding_ins_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), bonding_ins_act_start_dt, 121),':00' ) ) AS block_bondingins_act,
												IIF(ISNULL(bonding_ins_act_start_dt,'')='','', CONVERT(VARCHAR(5), bonding_ins_act_start_dt, 114)) AS bondingins_act_time,

												mold_plan_start_dt AS mold_plan,
												IIF(ISNULL(mold_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), mold_plan_start_dt, 121),':00' ) ) AS block_mold_plan,
												IIF(ISNULL(mold_plan_start_dt,'')='','', CONVERT(VARCHAR(5), mold_plan_start_dt, 114)) AS mold_plan_time,

												mold_act_start_dt AS mold_act,
												ISNULL(DATEDIFF(hour, mold_plan_start_dt, mold_act_start_dt),99) AS mold_plan_diff,
												IIF(ISNULL(mold_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), mold_act_start_dt, 121),':00' ) ) AS block_mold_act,
												IIF(ISNULL(mold_act_start_dt,'')='','', CONVERT(VARCHAR(5), mold_act_start_dt, 114)) AS mold_act_time,

												oven_mold_plan_start_dt AS oven_mold_plan,
												IIF(ISNULL(oven_mold_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), oven_mold_plan_start_dt, 121),':00' ) ) AS block_ovemold_plan,
												IIF(ISNULL(oven_mold_plan_start_dt,'')='','', CONVERT(VARCHAR(5), oven_mold_plan_start_dt, 114)) AS ovemold_plan_time,

												oven_mold_act_start_dt AS oven_mold_act,
												ISNULL(DATEDIFF(hour, oven_mold_plan_start_dt, oven_mold_act_start_dt),99) AS oven_mold_plan_diff,
												IIF(ISNULL(oven_mold_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), oven_mold_act_start_dt, 121),':00' ) ) AS block_ovemold_act,
												IIF(ISNULL(oven_mold_act_start_dt,'')='','', CONVERT(VARCHAR(5), oven_mold_act_start_dt, 114)) AS ovemold_act_time,

												dicer_plan_start_dt AS dicer_plan,
												IIF(ISNULL(dicer_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), dicer_plan_start_dt, 121),':00' ) ) AS block_dicer_plan,
												IIF(ISNULL(dicer_plan_start_dt,'')='','', CONVERT(VARCHAR(5), dicer_plan_start_dt, 114)) AS dicer_plan_time,

												dicer_act_start_dt AS dicer_act,
												ISNULL(DATEDIFF(hour, dicer_plan_start_dt, dicer_act_start_dt),99) AS dicer_plan_diff,
												IIF(ISNULL(dicer_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), dicer_act_start_dt, 121),':00' ) ) AS block_dicer_act,
												IIF(ISNULL(dicer_act_start_dt,'')='','', CONVERT(VARCHAR(5), dicer_act_start_dt, 114)) AS dicer_act_time,

												appearance_plan_start_dt AS appea_plan,
												IIF(ISNULL(appearance_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), appearance_plan_start_dt, 121),':00' ) ) AS block_appea_plan,
												IIF(ISNULL(appearance_plan_start_dt,'')='','', CONVERT(VARCHAR(5), appearance_plan_start_dt, 114)) AS appea_plan_time,

												appearance_act_start_dt AS appea_act,
												ISNULL(DATEDIFF(hour, appearance_plan_start_dt, appearance_act_start_dt),99) AS appea_plan_diff,
												IIF(ISNULL(appearance_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), appearance_act_start_dt, 121),':00' ) ) AS block_appea_act,
												IIF(ISNULL(appearance_act_start_dt,'')='','', CONVERT(VARCHAR(5), appearance_act_start_dt, 114)) AS appea_act_time,

												classify_plan_start_dt AS classi_plan,
												IIF(ISNULL(classify_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), classify_plan_start_dt, 121),':00' ) ) AS block_classi_plan,
												IIF(ISNULL(classify_plan_start_dt,'')='','', CONVERT(VARCHAR(5), classify_plan_start_dt, 114)) AS classi_plan_time,

												classify_act_start_dt AS classi_act,
												ISNULL(DATEDIFF(hour, classify_plan_start_dt, classify_act_start_dt),99) AS classi_plan_diff,
												IIF(ISNULL(classify_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), classify_act_start_dt, 121),':00' ) ) AS block_classi_act,
												IIF(ISNULL(classify_act_start_dt,'')='','', CONVERT(VARCHAR(5), classify_act_start_dt, 114)) AS classi_act_time

										FROM [WMSASIDB].[dbo].[ProductionPlan] a
										inner join (
											$SQL
										) lot on a.lot_no=lot.lot_no");
	
		return $data;
		//echo json_encode($data);
	}

	public function screen2(){
		$data = $this->loadScreen2();
		return view("display.screen2", ['lot_no'=> $data['lot_no'],]);
	}

	public function loadDataScreen3(){
		$date = date("Y-m-d");
		//$date = '2023-08-19';
		$data['data_today'] = $this->loadScreen1($date);
		$datePrev = date("Y-m-d", strtotime("-1 day", strtotime($date)));  
		$data['data_prev'] = $this->loadScreen1($datePrev);
		echo json_encode($data);
	}

	public function screen3(){
		return view("display.screen3");
	}

	public function loadDataScreen4(){
		$month_mm = date('m');
		//$month_mm = '08';
		$year_20yy = date('Y');
		$last_day_in_month = cal_days_in_month(CAL_GREGORIAN, $month_mm,  $year_20yy);

		$data['DB'] = DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.diebond_output) as act,  sum(ISNULL(ngbonding,0)) ng,  t.dm 
										from (
											SELECT sch_id, qty_in_lot, diebond_output, IIF(ISNULL(diebond_act_end_dt,'')='','',  CONVERT(VARCHAR(5), diebond_act_end_dt, 105) ) AS dm
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE diebond_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND diebond_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
										) t
										LEFT join (
											select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
											from [WMSASIDB].[dbo].[NGFactorList] 
											WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'DIE_BOND'
										) s ON t.sch_id = s.sch_id
										GROUP BY t.dm");
		$data['OvenDB']	= DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.diebond_output) as act,  sum(ISNULL(ngbonding,0)) ng,  t.dm 
										from (
											SELECT sch_id, qty_in_lot, diebond_output, IIF(ISNULL(oven_diebond_act_end_dt,'')='','',  CONVERT(VARCHAR(5), oven_diebond_act_end_dt, 105) ) AS dm
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE oven_diebond_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND oven_diebond_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
										) t
										LEFT join (
											select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
											from [WMSASIDB].[dbo].[NGFactorList] 
											WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'OVEN_DIE_BOND'
										) s ON t.sch_id = s.sch_id
										GROUP BY t.dm");		
		$data['WIRE']	= DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.wirebond_output) as act,  sum(ISNULL(ngbonding,0)) ng,  t.dm 
										from (
											SELECT sch_id, qty_in_lot, wirebond_output, IIF(ISNULL(wirebond_act_end_dt,'')='','',  CONVERT(VARCHAR(5), wirebond_act_end_dt, 105) ) AS dm
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE wirebond_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND wirebond_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
										) t
										LEFT join (
											select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
											from [WMSASIDB].[dbo].[NGFactorList] 
											WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'WIRE_BOND'
										) s ON t.sch_id = s.sch_id
										GROUP BY t.dm");			
		$data['BONDINS']	= DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.bonding_ins_output) as act, sum(ISNULL(ngbonding,0)) ng, t.dm 
											from (
												SELECT sch_id, qty_in_lot, bonding_ins_output, IIF(ISNULL(bonding_ins_act_end_dt,'')='','',  CONVERT(VARCHAR(5), bonding_ins_act_end_dt, 105) ) AS dm
												FROM [WMSASIDB].[dbo].[ProductionPlan]
												WHERE bonding_ins_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND bonding_ins_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
											) t
											LEFT join (
												select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
												from [WMSASIDB].[dbo].[NGFactorList] 
												WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'BONDING_INS'
											) s ON t.sch_id = s.sch_id 
											GROUP BY t.dm");	
		$data['MOLD']	= DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.mold_output) as act, sum(ISNULL(ngbonding,0)) ng, t.dm 
										from (
											SELECT sch_id, qty_in_lot, mold_output, IIF(ISNULL(mold_act_end_dt,'')='','',  CONVERT(VARCHAR(5), mold_act_end_dt, 105) ) AS dm
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE mold_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND mold_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
										) t
										LEFT join (
											select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
											from [WMSASIDB].[dbo].[NGFactorList] 
											WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'MOLD'
										) s ON t.sch_id = s.sch_id 
										GROUP BY t.dm");																														
		$data['OvenMOLD']	= DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.mold_output) as act, sum(ISNULL(ngbonding,0)) ng, t.dm 
										from (
											SELECT sch_id, qty_in_lot, mold_output, IIF(ISNULL(oven_mold_act_end_dt,'')='','',  CONVERT(VARCHAR(5), oven_mold_act_end_dt, 105) ) AS dm
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE oven_mold_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND oven_mold_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
										) t
										LEFT join (
											select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
											from [WMSASIDB].[dbo].[NGFactorList] 
											WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'OVEN_MOLD'
										) s ON t.sch_id = s.sch_id 
										GROUP BY t.dm");		
		$data['DICER']	= DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.dicer_output) as act, sum(ISNULL(ngbonding,0)) ng, t.dm 
										from (
											SELECT sch_id, qty_in_lot, dicer_output, IIF(ISNULL(dicer_act_end_dt,'')='','',  CONVERT(VARCHAR(5), dicer_act_end_dt, 105) ) AS dm
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE dicer_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND dicer_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
										) t
										LEFT join (
											select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
											from [WMSASIDB].[dbo].[NGFactorList] 
											WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'DICER'
										) s ON t.sch_id = s.sch_id 
										GROUP BY t.dm");
		$data['APP']	= DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.appearance_output) as act, sum(ISNULL(ngbonding,0)) ng, t.dm 
										from (
											SELECT sch_id, qty_in_lot, appearance_output, IIF(ISNULL(appearance_act_end_dt,'')='','',  CONVERT(VARCHAR(5), appearance_act_end_dt, 105) ) AS dm
											FROM [WMSASIDB].[dbo].[ProductionPlan]
											WHERE appearance_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND appearance_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
										) t
										LEFT join (
											select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
											from [WMSASIDB].[dbo].[NGFactorList] 
											WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'APPEARANCE'
										) s ON t.sch_id = s.sch_id 
										GROUP BY t.dm");
										
		$data['Classify']	= DB::SELECT("select sum(t.qty_in_lot) as pla, sum(t.last_output_qty) as act, sum(ISNULL(ngbonding,0)) ng, t.dm 
											from (
												SELECT sch_id, qty_in_lot, last_output_qty, IIF(ISNULL(classify_act_end_dt,'')='','',  CONVERT(VARCHAR(5), classify_act_end_dt, 105) ) AS dm
												FROM [WMSASIDB].[dbo].[ProductionPlan]
												WHERE classify_act_end_dt>='$year_20yy-$month_mm-01 00:00:00:000' AND classify_act_end_dt<='$year_20yy-$month_mm-$last_day_in_month 23:59:59:000'
											) t
											LEFT join (
												select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105))  AS dm 
												from [WMSASIDB].[dbo].[NGFactorList] 
												WHERE create_dt >= '$year_20yy-$month_mm-01 00:00:00:000' AND create_dt <= '$year_20yy-$month_mm-$last_day_in_month 23:59:59:000' AND process = 'CLASSIFY'
											) s ON t.sch_id = s.sch_id 
											GROUP BY t.dm");
										
		echo json_encode($data);								
	}

	public function screen4(){
		$month_3txt = date('M');
		$month_mm = date('m');
		//$month_mm = '08';
		//$month_3txt = 'Aug';
		$year_20yy = date('Y');
		return view("display.screen4", ['month_3txt'=> $month_3txt, 'year_20yy'=> $year_20yy, 'month_mm'=> $month_mm,]);
	}

}