|
1
|
<?php
|
|
2
|
|
|
3
|
namespace App\Http\Controllers;
|
|
4
|
use DB;
|
|
5
|
use Illuminate\Http\Request;
|
|
6
|
|
|
7
|
class HomeController extends Controller
|
|
8
|
{
|
|
9
|
|
|
10
|
public function index(){
|
|
11
|
//return view("sss.display2");
|
|
12
|
header( "refresh: 1; url=/asi/display/screen2" );
|
|
13
|
}
|
|
14
|
|
|
15
|
public function loadData(){
|
|
16
|
$date = date("Y-m-d");
|
|
17
|
//$date = '2023-08-19';
|
|
18
|
$data = $this->loadScreen1($date);
|
|
19
|
echo json_encode($data);
|
|
20
|
}
|
|
21
|
|
|
22
|
public function loadScreen1($date){
|
|
23
|
|
|
24
|
// code date current
|
|
25
|
// -----------------
|
|
26
|
// $date = date("Y-m-d");
|
|
27
|
// $dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date)));
|
|
28
|
// $datenow = date("Y-m-d h:i:sa");
|
|
29
|
// $dateAll = date('Y-m-d')." 07:59:00.000";
|
|
30
|
// $datemid = $dateOne." 08:00:00.000";
|
|
31
|
|
|
32
|
// $dateshow = date('d-m-Y');
|
|
33
|
// $timeshow = date('h:i:sa');
|
|
34
|
// $data["dateshow"] = $dateshow;
|
|
35
|
// $data["timeshow"] = $timeshow;
|
|
36
|
|
|
37
|
// $data['datestring'] = date("m")."'M ".date("d")."'D (".date("D").")";
|
|
38
|
|
|
39
|
// =======================================================================================
|
|
40
|
|
|
41
|
|
|
42
|
// code fix date for testing
|
|
43
|
// -------------------------
|
|
44
|
//$date = "2023-09-07"; // change date here.
|
|
45
|
//$date = date("Y-m-d");
|
|
46
|
$dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date)));
|
|
47
|
$datenow = $date." ".date("h:i:sa");
|
|
48
|
$dateAll = "$date 07:59:00.000";
|
|
49
|
$datemid = $dateOne." 08:00:00.000";
|
|
50
|
|
|
51
|
$data["dateshow"] = date('d-m-Y', strtotime($date));
|
|
52
|
$data["timeshow"] = date('h:i:sa');
|
|
53
|
|
|
54
|
$data['datestring'] = date("m",strtotime($date))."'M ".date("d",strtotime($date))."'D (".date("D",strtotime($date)).")";
|
|
55
|
|
|
56
|
// ========================================================================================
|
|
57
|
|
|
58
|
$data["db_capacity"] =DB::SELECT("select SUM(Qty) as db_capacity
|
|
59
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
60
|
WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datemid."'");
|
|
61
|
|
|
62
|
// today plan pcs
|
|
63
|
$data["pdieFull"] =DB::SELECT("select SUM(qty_in_lot) as pdieFull
|
|
64
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
65
|
WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datemid."'");
|
|
66
|
|
|
67
|
$data["pdieNow"] =DB::SELECT("select SUM(qty_in_lot) as pdieNow
|
|
68
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
69
|
WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datenow."'");
|
|
70
|
|
|
71
|
$data["povenFull"] =DB::SELECT("select SUM(qty_in_lot) as povenFull
|
|
72
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
73
|
WHERE oven_diebond_plan_start_dt >= '".$dateAll."' AND oven_diebond_plan_start_dt <= '".$datemid."'");
|
|
74
|
|
|
75
|
$data["povenNow"] =DB::SELECT("select SUM(qty_in_lot) as povenNow
|
|
76
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
77
|
WHERE oven_diebond_plan_start_dt >= '".$dateAll."' AND oven_diebond_plan_start_dt <= '".$datenow."'");
|
|
78
|
|
|
79
|
$data["pwireFull"] =DB::SELECT("select SUM(qty_in_lot) as pwireFull
|
|
80
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
81
|
WHERE wirebond_plan_start_dt >= '".$dateAll."' AND wirebond_plan_start_dt <= '".$datemid."'");
|
|
82
|
|
|
83
|
$data["pwireNow"] =DB::SELECT("select SUM(qty_in_lot) as pwireNow
|
|
84
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
85
|
WHERE wirebond_plan_start_dt >= '".$dateAll."' AND wirebond_plan_start_dt <= '".$datenow."'");
|
|
86
|
|
|
87
|
$data["pbondinFull"] =DB::SELECT("select SUM(qty_in_lot) as pbondinFull
|
|
88
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
89
|
WHERE bonding_ins_plan_start_dt >= '".$dateAll."' AND bonding_ins_plan_start_dt <= '".$datemid."'");
|
|
90
|
|
|
91
|
$data["pbondinNow"] =DB::SELECT("select SUM(qty_in_lot) as pbondinNow
|
|
92
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
93
|
WHERE bonding_ins_plan_start_dt >= '".$dateAll."' AND bonding_ins_plan_start_dt <= '".$datenow."'");
|
|
94
|
|
|
95
|
$data["pmoldFull"] =DB::SELECT("select SUM(qty_in_lot) as pmoldFull
|
|
96
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
97
|
WHERE mold_plan_start_dt >= '".$dateAll."' AND mold_plan_start_dt <= '".$datemid."'");
|
|
98
|
|
|
99
|
$data["pmoldNow"] =DB::SELECT("select SUM(qty_in_lot) as pmoldNow
|
|
100
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
101
|
WHERE mold_plan_start_dt >= '".$dateAll."' AND mold_plan_start_dt <= '".$datenow."'");
|
|
102
|
|
|
103
|
$data["povenmoldFull"] =DB::SELECT("select SUM(qty_in_lot) as povenmoldFull
|
|
104
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
105
|
WHERE oven_mold_plan_start_dt >= '".$dateAll."' AND oven_mold_plan_start_dt <= '".$datemid."'");
|
|
106
|
|
|
107
|
$data["povenmoldNow"] =DB::SELECT("select SUM(qty_in_lot) as povenmoldNow
|
|
108
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
109
|
WHERE oven_mold_plan_start_dt >= '".$dateAll."' AND oven_mold_plan_start_dt <= '".$datenow."'");
|
|
110
|
|
|
111
|
$data["pdicerFull"] =DB::SELECT("select SUM(qty_in_lot) as pdicerFull
|
|
112
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
113
|
WHERE dicer_plan_start_dt >= '".$dateAll."' AND dicer_plan_start_dt <= '".$datemid."'");
|
|
114
|
|
|
115
|
$data["pdicerNow"] =DB::SELECT("select SUM(qty_in_lot) as pdicerNow
|
|
116
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
117
|
WHERE dicer_plan_start_dt >= '".$dateAll."' AND dicer_plan_start_dt <= '".$datenow."'");
|
|
118
|
|
|
119
|
$data["pappeaFull"] =DB::SELECT("select SUM(qty_in_lot) as pappeaFull
|
|
120
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
121
|
WHERE appearance_plan_start_dt >= '".$dateAll."' AND appearance_plan_start_dt <= '".$datemid."'");
|
|
122
|
|
|
123
|
$data["pappeaNow"] =DB::SELECT("select SUM(qty_in_lot) as pappeaNow
|
|
124
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
125
|
WHERE appearance_plan_start_dt >= '".$dateAll."' AND appearance_plan_start_dt <= '".$datenow."'");
|
|
126
|
|
|
127
|
$data["pcalssFull"] =DB::SELECT("select SUM(qty_in_lot) as pcalssFull
|
|
128
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
129
|
WHERE classify_plan_start_dt >= '".$dateAll."' AND classify_plan_start_dt <= '".$datemid."'");
|
|
130
|
|
|
131
|
$data["pcalssNow"] =DB::SELECT("select SUM(qty_in_lot) as pcalssNow
|
|
132
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
133
|
WHERE classify_plan_start_dt >= '".$dateAll."' AND classify_plan_start_dt <= '".$datenow."'");
|
|
134
|
|
|
135
|
|
|
136
|
// today plan lot
|
|
137
|
$data["pdieFulllot"] =DB::SELECT("select count(lot_no) as pdieFulllot
|
|
138
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
139
|
WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datemid."'");
|
|
140
|
|
|
141
|
$data["pdieNowlot"] =DB::SELECT("select count(lot_no) as pdieNowlot
|
|
142
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
143
|
WHERE diebond_plan_start_dt >= '".$dateAll."' AND diebond_plan_start_dt <= '".$datenow."'");
|
|
144
|
|
|
145
|
$data["povenFulllot"] =DB::SELECT("select count(lot_no) as povenFulllot
|
|
146
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
147
|
WHERE oven_diebond_plan_start_dt >= '".$dateAll."' AND oven_diebond_plan_start_dt <= '".$datemid."'");
|
|
148
|
|
|
149
|
$data["povenNowlot"] =DB::SELECT("select count(lot_no) as povenNowlot
|
|
150
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
151
|
WHERE oven_diebond_plan_start_dt >= '".$dateAll."' AND oven_diebond_plan_start_dt <= '".$datenow."'");
|
|
152
|
|
|
153
|
$data["pwireFulllot"] =DB::SELECT("select count(lot_no) as pwireFulllot
|
|
154
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
155
|
WHERE wirebond_plan_start_dt >= '".$dateAll."' AND wirebond_plan_start_dt <= '".$datemid."'");
|
|
156
|
|
|
157
|
$data["pwireNowlot"] =DB::SELECT("select count(lot_no) as pwireNowlot
|
|
158
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
159
|
WHERE wirebond_plan_start_dt >= '".$dateAll."' AND wirebond_plan_start_dt <= '".$datenow."'");
|
|
160
|
|
|
161
|
$data["pbondinFulllot"] =DB::SELECT("select count(lot_no) as pbondinFulllot
|
|
162
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
163
|
WHERE bonding_ins_plan_start_dt >= '".$dateAll."' AND bonding_ins_plan_start_dt <= '".$datemid."'");
|
|
164
|
|
|
165
|
$data["pbondinNowlot"] =DB::SELECT("select count(lot_no) as pbondinNowlot
|
|
166
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
167
|
WHERE bonding_ins_plan_start_dt >= '".$dateAll."' AND bonding_ins_plan_start_dt <= '".$datenow."'");
|
|
168
|
|
|
169
|
$data["pmoldFulllot"] =DB::SELECT("select count(lot_no) as pmoldFulllot
|
|
170
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
171
|
WHERE mold_plan_start_dt >= '".$dateAll."' AND mold_plan_start_dt <= '".$datemid."'");
|
|
172
|
|
|
173
|
$data["pmoldNowlot"] =DB::SELECT("select count(lot_no) as pmoldNowlot
|
|
174
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
175
|
WHERE mold_plan_start_dt >= '".$dateAll."' AND mold_plan_start_dt <= '".$datenow."'");
|
|
176
|
|
|
177
|
$data["povenmoldFulllot"] =DB::SELECT("select count(lot_no) as povenmoldFulllot
|
|
178
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
179
|
WHERE oven_mold_plan_start_dt >= '".$dateAll."' AND oven_mold_plan_start_dt <= '".$datemid."'");
|
|
180
|
|
|
181
|
$data["povenmoldNowlot"] =DB::SELECT("select count(lot_no) as povenmoldNowlot
|
|
182
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
183
|
WHERE oven_mold_plan_start_dt >= '".$dateAll."' AND oven_mold_plan_start_dt <= '".$datenow."'");
|
|
184
|
|
|
185
|
$data["pdicerFulllot"] =DB::SELECT("select count(lot_no) as pdicerFulllot
|
|
186
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
187
|
WHERE dicer_plan_start_dt >= '".$dateAll."' AND dicer_plan_start_dt <= '".$datemid."'");
|
|
188
|
|
|
189
|
$data["pdicerNowlot"] =DB::SELECT("select count(lot_no) as pdicerNowlot
|
|
190
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
191
|
WHERE dicer_plan_start_dt >= '".$dateAll."' AND dicer_plan_start_dt <= '".$datenow."'");
|
|
192
|
|
|
193
|
$data["pappeaFulllot"] =DB::SELECT("select count(lot_no) as pappeaFulllot
|
|
194
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
195
|
WHERE appearance_plan_start_dt >= '".$dateAll."' AND appearance_plan_start_dt <= '".$datemid."'");
|
|
196
|
|
|
197
|
$data["pappeaNowlot"] =DB::SELECT("select count(lot_no) as pappeaNowlot
|
|
198
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
199
|
WHERE appearance_plan_start_dt >= '".$dateAll."' AND appearance_plan_start_dt <= '".$datenow."'");
|
|
200
|
|
|
201
|
$data["pcalssFulllot"] =DB::SELECT("select count(lot_no) as pcalssFulllot
|
|
202
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
203
|
WHERE classify_plan_start_dt >= '".$dateAll."' AND classify_plan_start_dt <= '".$datemid."'");
|
|
204
|
|
|
205
|
$data["pcalssNowlot"] =DB::SELECT("select count(lot_no) as pcalssNowlot
|
|
206
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
207
|
WHERE classify_plan_start_dt >= '".$dateAll."' AND classify_plan_start_dt <= '".$datenow."'");
|
|
208
|
|
|
209
|
|
|
210
|
//input pcs
|
|
211
|
|
|
212
|
$data["indieNow"] =DB::SELECT("select SUM(qty_in_lot) as indieNow
|
|
213
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
214
|
WHERE diebond_act_start_dt >= '".$dateAll."' AND diebond_act_start_dt <= '".$datenow."'");
|
|
215
|
|
|
216
|
$data["inovenNow"] =DB::SELECT("select SUM(diebond_output) as inovenNow
|
|
217
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
218
|
WHERE oven_diebond_act_start_dt >= '".$dateAll."' AND oven_diebond_act_start_dt <= '".$datenow."'");
|
|
219
|
|
|
220
|
$data["inwirebondNow"] =DB::SELECT("select SUM(diebond_output) as inwirebondNow
|
|
221
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
222
|
WHERE wirebond_act_start_dt >= '".$dateAll."' AND wirebond_act_start_dt <= '".$datenow."'");
|
|
223
|
|
|
224
|
$data["inbondingNow"] =DB::SELECT("select SUM(wirebond_output ) as inbondingNow
|
|
225
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
226
|
WHERE bonding_ins_act_start_dt >= '".$dateAll."' AND bonding_ins_act_start_dt <= '".$datenow."'");
|
|
227
|
|
|
228
|
$data["inmoldNow"] =DB::SELECT("select SUM(bonding_ins_output) as inmoldNow
|
|
229
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
230
|
WHERE mold_act_start_dt >= '".$dateAll."' AND mold_act_start_dt <= '".$datenow."'");
|
|
231
|
|
|
232
|
$data["inovenmoldNow"] =DB::SELECT("select SUM(mold_output) as inovenmoldNow
|
|
233
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
234
|
WHERE oven_mold_act_start_dt >= '".$dateAll."' AND oven_mold_act_start_dt <= '".$datenow."'");
|
|
235
|
|
|
236
|
$data["indicerNow"] =DB::SELECT("select SUM(mold_output) as indicerNow
|
|
237
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
238
|
WHERE dicer_act_start_dt >= '".$dateAll."' AND dicer_act_start_dt <= '".$datenow."'");
|
|
239
|
|
|
240
|
$data["inappearaNow"] =DB::SELECT("select SUM(dicer_output) as inappearaNow
|
|
241
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
242
|
WHERE appearance_act_start_dt >= '".$dateAll."' AND appearance_act_start_dt <= '".$datenow."'");
|
|
243
|
|
|
244
|
$data["incalssNow"] =DB::SELECT("select SUM(input) as incalssNow
|
|
245
|
from [WMSASIDB].[dbo].[ClassifyLot] WHERE classify_lot IN
|
|
246
|
(select classify_lot
|
|
247
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
248
|
WHERE classify_act_start_dt >= '".$dateAll."' AND classify_act_start_dt <= '".$datenow."')");
|
|
249
|
|
|
250
|
// input Lot
|
|
251
|
|
|
252
|
$data["indieNowlot"] =DB::SELECT("select count(lot_no) as indieNowlot
|
|
253
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
254
|
WHERE diebond_act_start_dt >= '".$dateAll."' AND diebond_act_start_dt <= '".$datenow."'");
|
|
255
|
|
|
256
|
$data["inovenNowlot"] =DB::SELECT("select COUNT(lot_no) as inovenNowlot
|
|
257
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
258
|
WHERE oven_diebond_act_start_dt >= '".$dateAll."' AND oven_diebond_act_start_dt <= '".$datenow."'");
|
|
259
|
|
|
260
|
$data["inwirebondNowlot"] =DB::SELECT("select COUNT(lot_no) as inwirebondNowlot
|
|
261
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
262
|
WHERE wirebond_act_start_dt >= '".$dateAll."' AND wirebond_act_start_dt <= '".$datenow."'");
|
|
263
|
|
|
264
|
$data["inbondingNowlot"] =DB::SELECT("select COUNT(lot_no) as inbondingNowlot
|
|
265
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
266
|
WHERE bonding_ins_act_start_dt >= '".$dateAll."' AND bonding_ins_act_start_dt <= '".$datenow."'");
|
|
267
|
|
|
268
|
$data["inmoldNowlot"] =DB::SELECT("select COUNT(lot_no) as inmoldNowlot
|
|
269
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
270
|
WHERE mold_act_start_dt >= '".$dateAll."' AND mold_act_start_dt <= '".$datenow."'");
|
|
271
|
|
|
272
|
$data["inovenmoldNowlot"] =DB::SELECT("select COUNT(lot_no) as inovenmoldNowlot
|
|
273
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
274
|
WHERE oven_mold_act_start_dt >= '".$dateAll."' AND oven_mold_act_start_dt <= '".$datenow."'");
|
|
275
|
|
|
276
|
$data["indicerNowlot"] =DB::SELECT("select COUNT(lot_no) as indicerNowlot
|
|
277
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
278
|
WHERE dicer_act_start_dt >= '".$dateAll."' AND dicer_act_start_dt <= '".$datenow."'");
|
|
279
|
|
|
280
|
$data["inappearaNowlot"] =DB::SELECT("select COUNT(lot_no) as inappearaNowlot
|
|
281
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
282
|
WHERE appearance_act_start_dt >= '".$dateAll."' AND appearance_act_start_dt <= '".$datenow."'");
|
|
283
|
|
|
284
|
$data["incalssNowlot"] =DB::SELECT("select COUNT(classify_lot) as incalssNowlot
|
|
285
|
from [WMSASIDB].[dbo].[ClassifyLot] WHERE classify_lot IN
|
|
286
|
(select classify_lot
|
|
287
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
288
|
WHERE classify_act_start_dt >= '".$dateAll."' AND classify_act_start_dt <= '".$datenow."')");
|
|
289
|
|
|
290
|
|
|
291
|
//output
|
|
292
|
$data["outdieend"] =DB::SELECT("select SUM(diebond_output) as outdieend
|
|
293
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
294
|
WHERE diebond_act_end_dt >= '".$dateAll."' AND diebond_act_end_dt <= '".$datenow."'");
|
|
295
|
|
|
296
|
$data["outovenend"] =DB::SELECT("select SUM(diebond_output) as outovenend
|
|
297
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
298
|
WHERE oven_diebond_act_end_dt >= '".$dateAll."' AND oven_diebond_act_end_dt <= '".$datenow."'");
|
|
299
|
|
|
300
|
$data["outwirebondend"] =DB::SELECT("select SUM(diebond_output) as outwirebondend
|
|
301
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
302
|
WHERE wirebond_act_end_dt >= '".$dateAll."' AND wirebond_act_end_dt <= '".$datenow."'");
|
|
303
|
|
|
304
|
$data["outbondingend"] =DB::SELECT("select SUM(wirebond_output) as outbondingend
|
|
305
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
306
|
WHERE bonding_ins_act_end_dt >= '".$dateAll."' AND bonding_ins_act_end_dt <= '".$datenow."'");
|
|
307
|
|
|
308
|
$data["outmoldend"] =DB::SELECT("select SUM(wirebond_output) as outmoldend
|
|
309
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
310
|
WHERE mold_act_end_dt >= '".$dateAll."' AND mold_act_end_dt <= '".$datenow."'");
|
|
311
|
|
|
312
|
$data["outoven_moldend"] =DB::SELECT("select SUM(mold_output) as outoven_moldend
|
|
313
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
314
|
WHERE oven_mold_act_end_dt >= '".$dateAll."' AND oven_mold_act_end_dt <= '".$datenow."'");
|
|
315
|
|
|
316
|
$data["outdicer_actend"] =DB::SELECT("select SUM(mold_output) as outdicer_actend
|
|
317
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
318
|
WHERE dicer_act_end_dt >= '".$dateAll."' AND dicer_act_end_dt <= '".$datenow."'");
|
|
319
|
|
|
320
|
$data["outappearanceend"] =DB::SELECT("select SUM(dicer_output) as outappearanceend
|
|
321
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
322
|
WHERE appearance_act_end_dt >= '".$dateAll."' AND appearance_act_end_dt <= '".$datenow."'");
|
|
323
|
|
|
324
|
$data["outclassifyend"] =DB::SELECT("select SUM(output) as outclassifyend
|
|
325
|
from [WMSASIDB].[dbo].[ClassifyLot] WHERE classify_lot IN
|
|
326
|
(select classify_lot
|
|
327
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
328
|
WHERE classify_act_end_dt >= '".$dateAll."' AND classify_act_end_dt <= '".$datenow."')");
|
|
329
|
|
|
330
|
|
|
331
|
//output lot
|
|
332
|
$data["outdieendlot"] =DB::SELECT("select COUNT(lot_no) as outdieendlot
|
|
333
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
334
|
WHERE diebond_act_end_dt >= '".$dateAll."' AND diebond_act_end_dt <= '".$datenow."'");
|
|
335
|
|
|
336
|
$data["outovenendlot"] =DB::SELECT("select COUNT(lot_no) as outovenendlot
|
|
337
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
338
|
WHERE oven_diebond_act_end_dt >= '".$dateAll."' AND oven_diebond_act_end_dt <= '".$datenow."'");
|
|
339
|
|
|
340
|
$data["outwirebondendlot"] =DB::SELECT("select COUNT(lot_no) as outwirebondendlot
|
|
341
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
342
|
WHERE wirebond_act_end_dt >= '".$dateAll."' AND wirebond_act_end_dt <= '".$datenow."'");
|
|
343
|
|
|
344
|
$data["outbondingendlot"] =DB::SELECT("select COUNT(lot_no) as outbondingendlot
|
|
345
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
346
|
WHERE bonding_ins_act_end_dt >= '".$dateAll."' AND bonding_ins_act_end_dt <= '".$datenow."'");
|
|
347
|
|
|
348
|
$data["outmoldendlot"] =DB::SELECT("select COUNT(lot_no) as outmoldendlot
|
|
349
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
350
|
WHERE mold_act_end_dt >= '".$dateAll."' AND mold_act_end_dt <= '".$datenow."'");
|
|
351
|
|
|
352
|
$data["outoven_moldendlot"] =DB::SELECT("select COUNT(lot_no) as outoven_moldendlot
|
|
353
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
354
|
WHERE oven_mold_act_end_dt >= '".$dateAll."' AND oven_mold_act_end_dt <= '".$datenow."'");
|
|
355
|
|
|
356
|
$data["outdicer_actendlot"] =DB::SELECT("select COUNT(lot_no) as outdicer_actendlot
|
|
357
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
358
|
WHERE dicer_act_end_dt >= '".$dateAll."' AND dicer_act_end_dt <= '".$datenow."'");
|
|
359
|
|
|
360
|
$data["outappearanceendlot"] =DB::SELECT("select COUNT(lot_no) as outappearanceendlot
|
|
361
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
362
|
WHERE appearance_act_end_dt >= '".$dateAll."' AND appearance_act_end_dt <= '".$datenow."'");
|
|
363
|
|
|
364
|
$data["outclassifyendlot"] =DB::SELECT("select COUNT(classify_lot) as outclassifyendlot
|
|
365
|
from [WMSASIDB].[dbo].[ClassifyLot] WHERE classify_lot IN
|
|
366
|
(select classify_lot
|
|
367
|
from [WMSASIDB].[dbo].[ProductionPlan]
|
|
368
|
WHERE classify_act_end_dt >= '".$dateAll."' AND classify_act_end_dt <= '".$datenow."')");
|
|
369
|
|
|
370
|
|
|
371
|
//NG
|
|
372
|
|
|
373
|
$data["ngbonding"] =DB::SELECT("select SUM(qty) as ngbonding
|
|
374
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
375
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'BONDING_INS' ");
|
|
376
|
|
|
377
|
$data["ngmold"] =DB::SELECT("select SUM(qty) as ngmold
|
|
378
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
379
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'MOLD' ");
|
|
380
|
|
|
381
|
$data["ngdicer"] =DB::SELECT("select SUM(qty) as ngdicer
|
|
382
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
383
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'DICER' ");
|
|
384
|
|
|
385
|
$data["ngappear"] =DB::SELECT("select SUM(qty) as ngappear
|
|
386
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
387
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'APPEARANCE' ");
|
|
388
|
|
|
389
|
$data["ngclassify"] =DB::SELECT("select SUM(qty) as ngclassify from [WMSASIDB].[dbo].[ClassifyOutput]
|
|
390
|
where output_type = 'NG' AND create_dt >='".$dateAll."' AND create_dt <= '".$datenow."'");
|
|
391
|
|
|
392
|
// **** top3 *****
|
|
393
|
/// BONDING_INS
|
|
394
|
$data["t3bonding"] =DB::SELECT("select top 2 ng_code,ng_name, SUM(qty) as sumqty FROM [WMSASIDB].[dbo].[NGFactorList]
|
|
395
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."'
|
|
396
|
AND process = 'BONDING_INS'
|
|
397
|
GROUP BY ng_code, ng_name ORDER BY sumqty desc");
|
|
398
|
|
|
399
|
$data["t3mold"] =DB::SELECT("select top 2 ng_code,ng_name, SUM(qty) as sumqty FROM [WMSASIDB].[dbo].[NGFactorList]
|
|
400
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."'
|
|
401
|
AND process = 'MOLD'
|
|
402
|
GROUP BY ng_code, ng_name ORDER BY sumqty desc");
|
|
403
|
|
|
404
|
$data["t3dicer"] =DB::SELECT("select top 2 ng_code,ng_name, SUM(qty) as sumqty FROM [WMSASIDB].[dbo].[NGFactorList]
|
|
405
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."'
|
|
406
|
AND process = 'DICER'
|
|
407
|
GROUP BY ng_code, ng_name ORDER BY sumqty desc");
|
|
408
|
|
|
409
|
$data["t3appea"] =DB::SELECT("select top 2 ng_code,ng_name, SUM(qty) as sumqty FROM [WMSASIDB].[dbo].[NGFactorList]
|
|
410
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."'
|
|
411
|
AND process = 'APPEARANCE'
|
|
412
|
GROUP BY ng_code, ng_name ORDER BY sumqty desc");
|
|
413
|
|
|
414
|
$data["t3classi"] =DB::SELECT(" select top 2 output_name, SUM(qty) as sumng
|
|
415
|
from [WMSASIDB].[dbo].[ClassifyOutput]
|
|
416
|
where create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND output_type = 'NG'
|
|
417
|
GROUP BY output_name ORDER BY sumng desc");
|
|
418
|
|
|
419
|
// dd($data);
|
|
420
|
|
|
421
|
// remark
|
|
422
|
|
|
423
|
$data["remarkdie"] =DB::SELECT("select top 2 lot_no, dienond_remark AS remark , count(dienond_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
424
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'DIE_BOND' AND dienond_remark is not null
|
|
425
|
GROUP BY lot_no, dienond_remark ORDER BY total_remark desc");
|
|
426
|
|
|
427
|
$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]
|
|
428
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'OVEN_DIE_BOND' AND oven_diebond_remark is not null
|
|
429
|
GROUP BY lot_no, oven_diebond_remark ORDER BY total_remark desc");
|
|
430
|
|
|
431
|
$data["remarkwirebond"] =DB::SELECT("select top 2 lot_no, wirebond_remark AS remark , count(wirebond_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
432
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'WIRE_BOND' AND wirebond_remark is not null
|
|
433
|
GROUP BY lot_no, wirebond_remark ORDER BY total_remark desc");
|
|
434
|
|
|
435
|
// $data["remarkbonding"] = array();
|
|
436
|
|
|
437
|
$data["remarkmold"] =DB::SELECT("select top 2 lot_no, mold_remark AS remark , count(mold_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
438
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'MOLD' AND mold_remark is not null
|
|
439
|
GROUP BY lot_no, mold_remark ORDER BY total_remark desc");
|
|
440
|
|
|
441
|
$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]
|
|
442
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'OVEN_MOLD' AND oven_mold_remark is not null
|
|
443
|
GROUP BY lot_no, oven_mold_remark ORDER BY total_remark desc");
|
|
444
|
|
|
445
|
$data["remarkdicer"] =DB::SELECT("select top 2 lot_no, dicer_remark AS remark , count(dicer_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
446
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'DICER' AND dicer_remark is not null
|
|
447
|
GROUP BY lot_no, dicer_remark ORDER BY total_remark desc");
|
|
448
|
|
|
449
|
$data["remarkappearance"] = array();
|
|
450
|
|
|
451
|
$data["remarkclassif"] =DB::SELECT("select top 2 lot_no, classify_remark AS remark , count(classify_remark) AS total_remark FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
452
|
WHERE create_dt >= '".$dateAll."' AND create_dt <= '".$datenow."' AND process = 'CLASSIFY' AND classify_remark is not null
|
|
453
|
GROUP BY lot_no, classify_remark ORDER BY total_remark desc");
|
|
454
|
|
|
455
|
|
|
456
|
return $data;
|
|
457
|
|
|
458
|
//echo json_encode($data);
|
|
459
|
}
|
|
460
|
|
|
461
|
public function screen1(){
|
|
462
|
return view("display.screen1");
|
|
463
|
}
|
|
464
|
|
|
465
|
public function loadDataScreen2(){
|
|
466
|
$data = $this->loadScreen2();
|
|
467
|
echo json_encode($data);
|
|
468
|
}
|
|
469
|
|
|
470
|
public function loadScreen2(){
|
|
471
|
// code date current
|
|
472
|
// -----------------
|
|
473
|
// $date = date("Y-m-d");
|
|
474
|
// $dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date)));
|
|
475
|
// $datenow = date("Y-m-d h:i:sa");
|
|
476
|
// $dateAll = date('Y-m-d')." 07:59:00.000";
|
|
477
|
// $datemid = $dateOne." 08:00:00.000";
|
|
478
|
|
|
479
|
// $dateshow = date('d-m-Y');
|
|
480
|
// $timeshow = date('h:i:sa');
|
|
481
|
// $data["dateshow"] = $dateshow;
|
|
482
|
// $data["timeshow"] = $timeshow;
|
|
483
|
|
|
484
|
// $data['datestring'] = date("m")."'M ".date("d")."'D (".date("D").")";
|
|
485
|
|
|
486
|
// =======================================================================================
|
|
487
|
|
|
488
|
|
|
489
|
// code fix date for testing
|
|
490
|
// -------------------------
|
|
491
|
$date = date("Y-m-d");
|
|
492
|
//$date = '2023-09-21';
|
|
493
|
$timeshow = date("h:i:sa");
|
|
494
|
$H_check = date("H");
|
|
495
|
//$H_check = 20;
|
|
496
|
if( $H_check>=8 && $H_check<20 ){
|
|
497
|
$dateOne = $date;
|
|
498
|
$dateAll = "$date 08:00:00.000";
|
|
499
|
$datemid = $dateOne." 19:59:59.000";
|
|
500
|
// $dateOne = date("Y-m-d", strtotime("-1 day", strtotime($date)));
|
|
501
|
// $dateAll = "$dateOne 20:00:00.000";
|
|
502
|
// $datemid = $date." 19:59:59.000";
|
|
503
|
//$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");
|
|
504
|
}else{
|
|
505
|
$dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date)));
|
|
506
|
$dateAll = "$date 20:00:00.000";
|
|
507
|
$datemid = $dateOne." 07:59:59.000";
|
|
508
|
// $dateOne = date("Y-m-d", strtotime("+1 day", strtotime($date)));
|
|
509
|
// $dateAll = "$date 08:00:00.000";
|
|
510
|
// $datemid = $dateOne." 07:59:59.000";
|
|
511
|
//$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");
|
|
512
|
}
|
|
513
|
|
|
514
|
// $dateOne = $date;
|
|
515
|
// $datemid = date("Y-m-d", strtotime("+1 day", strtotime($date)))." 07:59:59.000";
|
|
516
|
|
|
517
|
$datenow = $date." ".$timeshow;
|
|
518
|
$data["day_cal"] = $date;
|
|
519
|
$data["timeshow"] = $timeshow;
|
|
520
|
$data["dateshow"] = date('d M Y', strtotime($date));
|
|
521
|
|
|
522
|
$data["dateAll"] = $dateAll;
|
|
523
|
$data["datemid"] = $datemid;
|
|
524
|
$data['H_check'] = $H_check;
|
|
525
|
//$data['datestring'] = date("m",strtotime($date))."'M ".date("d",strtotime($date))."'D (".date("D",strtotime($date)).")";
|
|
526
|
|
|
527
|
// ========================================================================================
|
|
528
|
|
|
529
|
$data['lot_not_start'] = DB::SELECT("SELECT ISNULL(SUM(IIF(ISNULL(diebond_act_start_dt,1)=1,1,0)),0) AS notstart
|
|
530
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
531
|
WHERE diebond_act_start_dt >= '$dateAll' AND diebond_act_start_dt <= '$datemid'");
|
|
532
|
|
|
533
|
$data['lot_finish'] = DB::SELECT("SELECT ISNULL(SUM(IIF(ISNULL(classify_act_end_dt,0)=0,0,1)),0) AS finish
|
|
534
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
535
|
WHERE (diebond_act_start_dt >= '$dateAll' AND diebond_act_start_dt <= '$datemid') AND ( classify_act_end_dt >= '$dateAll' AND classify_act_end_dt <= '$datemid')");
|
|
536
|
|
|
537
|
|
|
538
|
$SQL = " SELECT lot_no
|
|
539
|
FROM (
|
|
540
|
select *, 'diebond' as 'job_type'
|
|
541
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
542
|
WHERE diebond_act_start_dt >= '$dateAll' AND diebond_act_start_dt <= '$datemid'
|
|
543
|
union
|
|
544
|
SELECT *, 'oven_diebond' as 'job_type'
|
|
545
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
546
|
WHERE oven_diebond_act_start_dt >= '$dateAll' AND oven_diebond_act_start_dt <= '$datemid'
|
|
547
|
union
|
|
548
|
SELECT *, 'wirebond' as 'job_type'
|
|
549
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
550
|
WHERE wirebond_act_start_dt >= '$dateAll' AND wirebond_act_start_dt <= '$datemid'
|
|
551
|
union
|
|
552
|
SELECT *, 'bonding_ins' as 'job_type'
|
|
553
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
554
|
WHERE bonding_ins_act_start_dt >= '$dateAll' AND bonding_ins_act_start_dt <= '$datemid'
|
|
555
|
union
|
|
556
|
SELECT *, 'mold' as 'job_type'
|
|
557
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
558
|
WHERE mold_act_start_dt >= '$dateAll' AND mold_act_start_dt <= '$datemid'
|
|
559
|
union
|
|
560
|
SELECT *, 'over_mold' as 'job_type'
|
|
561
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
562
|
WHERE oven_mold_act_start_dt >= '$dateAll' AND oven_mold_act_start_dt <= '$datemid'
|
|
563
|
union
|
|
564
|
SELECT *, 'dicer' as 'job_type'
|
|
565
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
566
|
WHERE dicer_act_start_dt >= '$dateAll' AND dicer_act_start_dt <= '$datemid'
|
|
567
|
union
|
|
568
|
SELECT *, 'appearance' as 'job_type'
|
|
569
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
570
|
WHERE appearance_act_start_dt >= '$dateAll' AND appearance_act_start_dt <= '$datemid'
|
|
571
|
union
|
|
572
|
SELECT *, 'classify' as 'job_type'
|
|
573
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
574
|
WHERE classify_act_start_dt >= '$dateAll' AND classify_act_start_dt <= '$datemid'
|
|
575
|
|
|
576
|
) tmp GROUP BY lot_no
|
|
577
|
";
|
|
578
|
|
|
579
|
$data['lot_no'] = DB::SELECT($SQL);
|
|
580
|
|
|
581
|
$data['lot_data'] = DB::SELECT("SELECT a.lot_no,
|
|
582
|
diebond_plan_start_dt AS diebond_plan,
|
|
583
|
IIF(ISNULL(diebond_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), diebond_plan_start_dt, 121),':00' ) ) AS block_diebond_plan,
|
|
584
|
IIF(ISNULL(diebond_plan_start_dt,'')='','', CONVERT(VARCHAR(5), diebond_plan_start_dt, 114 )) AS diebond_plan_time,
|
|
585
|
|
|
586
|
diebond_act_start_dt AS diebond_act,
|
|
587
|
ISNULL(DATEDIFF(hour, diebond_plan_start_dt, diebond_act_start_dt),99) AS diebond_plan_diff,
|
|
588
|
IIF(ISNULL(diebond_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), diebond_act_start_dt, 121),':00' ) ) AS block_diebond_act,
|
|
589
|
IIF(ISNULL(diebond_act_start_dt,'')='','', CONVERT(VARCHAR(5), diebond_act_start_dt, 114)) AS diebond_act_time,
|
|
590
|
|
|
591
|
oven_diebond_plan_start_dt AS oven_diebond_plan,
|
|
592
|
IIF(ISNULL(oven_diebond_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), oven_diebond_plan_start_dt, 121),':00' ) ) AS block_ovediebond_plan,
|
|
593
|
IIF(ISNULL(oven_diebond_plan_start_dt,'')='','', CONVERT(VARCHAR(5), oven_diebond_plan_start_dt, 114)) AS ovediebond_plan_time,
|
|
594
|
|
|
595
|
oven_diebond_act_start_dt AS oven_diebond_act,
|
|
596
|
ISNULL(DATEDIFF(hour, oven_diebond_plan_start_dt, oven_diebond_act_start_dt),99) AS oven_diebond_plan_diff,
|
|
597
|
IIF(ISNULL(oven_diebond_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), oven_diebond_act_start_dt, 121),':00' ) ) AS block_ovediebond_act,
|
|
598
|
IIF(ISNULL(oven_diebond_act_start_dt,'')='','', CONVERT(VARCHAR(5), oven_diebond_act_start_dt, 114)) AS ovediebond_act_time,
|
|
599
|
|
|
600
|
wirebond_plan_start_dt AS wirebond_plan,
|
|
601
|
IIF(ISNULL(wirebond_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), wirebond_plan_start_dt, 121),':00' ) ) AS block_wirebond_plan,
|
|
602
|
IIF(ISNULL(wirebond_plan_start_dt,'')='','', CONVERT(VARCHAR(5), wirebond_plan_start_dt, 114)) AS wirebond_plan_time,
|
|
603
|
|
|
604
|
wirebond_act_start_dt AS wirebond_act,
|
|
605
|
ISNULL(DATEDIFF(hour, wirebond_plan_start_dt, wirebond_act_start_dt),99) AS wirebond_plan_diff,
|
|
606
|
IIF(ISNULL(wirebond_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), wirebond_act_start_dt, 121),':00' ) ) AS block_wirebond_act,
|
|
607
|
IIF(ISNULL(wirebond_act_start_dt,'')='','', CONVERT(VARCHAR(5), wirebond_act_start_dt, 114)) AS wirebond_act_time,
|
|
608
|
|
|
609
|
bonding_ins_plan_start_dt AS bonding_ins_plan,
|
|
610
|
IIF(ISNULL(bonding_ins_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), bonding_ins_plan_start_dt, 121),':00' ) ) AS block_bondingins_plan,
|
|
611
|
IIF(ISNULL(bonding_ins_plan_start_dt,'')='','', CONVERT(VARCHAR(5), bonding_ins_plan_start_dt, 114)) AS bondingins_plan_time,
|
|
612
|
|
|
613
|
bonding_ins_act_start_dt AS bonding_ins_act,
|
|
614
|
ISNULL(DATEDIFF(hour, bonding_ins_plan_start_dt, bonding_ins_act_start_dt),99) AS bonding_ins_plan_diff,
|
|
615
|
IIF(ISNULL(bonding_ins_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), bonding_ins_act_start_dt, 121),':00' ) ) AS block_bondingins_act,
|
|
616
|
IIF(ISNULL(bonding_ins_act_start_dt,'')='','', CONVERT(VARCHAR(5), bonding_ins_act_start_dt, 114)) AS bondingins_act_time,
|
|
617
|
|
|
618
|
mold_plan_start_dt AS mold_plan,
|
|
619
|
IIF(ISNULL(mold_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), mold_plan_start_dt, 121),':00' ) ) AS block_mold_plan,
|
|
620
|
IIF(ISNULL(mold_plan_start_dt,'')='','', CONVERT(VARCHAR(5), mold_plan_start_dt, 114)) AS mold_plan_time,
|
|
621
|
|
|
622
|
mold_act_start_dt AS mold_act,
|
|
623
|
ISNULL(DATEDIFF(hour, mold_plan_start_dt, mold_act_start_dt),99) AS mold_plan_diff,
|
|
624
|
IIF(ISNULL(mold_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), mold_act_start_dt, 121),':00' ) ) AS block_mold_act,
|
|
625
|
IIF(ISNULL(mold_act_start_dt,'')='','', CONVERT(VARCHAR(5), mold_act_start_dt, 114)) AS mold_act_time,
|
|
626
|
|
|
627
|
oven_mold_plan_start_dt AS oven_mold_plan,
|
|
628
|
IIF(ISNULL(oven_mold_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), oven_mold_plan_start_dt, 121),':00' ) ) AS block_ovemold_plan,
|
|
629
|
IIF(ISNULL(oven_mold_plan_start_dt,'')='','', CONVERT(VARCHAR(5), oven_mold_plan_start_dt, 114)) AS ovemold_plan_time,
|
|
630
|
|
|
631
|
oven_mold_act_start_dt AS oven_mold_act,
|
|
632
|
ISNULL(DATEDIFF(hour, oven_mold_plan_start_dt, oven_mold_act_start_dt),99) AS oven_mold_plan_diff,
|
|
633
|
IIF(ISNULL(oven_mold_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), oven_mold_act_start_dt, 121),':00' ) ) AS block_ovemold_act,
|
|
634
|
IIF(ISNULL(oven_mold_act_start_dt,'')='','', CONVERT(VARCHAR(5), oven_mold_act_start_dt, 114)) AS ovemold_act_time,
|
|
635
|
|
|
636
|
dicer_plan_start_dt AS dicer_plan,
|
|
637
|
IIF(ISNULL(dicer_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), dicer_plan_start_dt, 121),':00' ) ) AS block_dicer_plan,
|
|
638
|
IIF(ISNULL(dicer_plan_start_dt,'')='','', CONVERT(VARCHAR(5), dicer_plan_start_dt, 114)) AS dicer_plan_time,
|
|
639
|
|
|
640
|
dicer_act_start_dt AS dicer_act,
|
|
641
|
ISNULL(DATEDIFF(hour, dicer_plan_start_dt, dicer_act_start_dt),99) AS dicer_plan_diff,
|
|
642
|
IIF(ISNULL(dicer_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), dicer_act_start_dt, 121),':00' ) ) AS block_dicer_act,
|
|
643
|
IIF(ISNULL(dicer_act_start_dt,'')='','', CONVERT(VARCHAR(5), dicer_act_start_dt, 114)) AS dicer_act_time,
|
|
644
|
|
|
645
|
appearance_plan_start_dt AS appea_plan,
|
|
646
|
IIF(ISNULL(appearance_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), appearance_plan_start_dt, 121),':00' ) ) AS block_appea_plan,
|
|
647
|
IIF(ISNULL(appearance_plan_start_dt,'')='','', CONVERT(VARCHAR(5), appearance_plan_start_dt, 114)) AS appea_plan_time,
|
|
648
|
|
|
649
|
appearance_act_start_dt AS appea_act,
|
|
650
|
ISNULL(DATEDIFF(hour, appearance_plan_start_dt, appearance_act_start_dt),99) AS appea_plan_diff,
|
|
651
|
IIF(ISNULL(appearance_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), appearance_act_start_dt, 121),':00' ) ) AS block_appea_act,
|
|
652
|
IIF(ISNULL(appearance_act_start_dt,'')='','', CONVERT(VARCHAR(5), appearance_act_start_dt, 114)) AS appea_act_time,
|
|
653
|
|
|
654
|
classify_plan_start_dt AS classi_plan,
|
|
655
|
IIF(ISNULL(classify_plan_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), classify_plan_start_dt, 121),':00' ) ) AS block_classi_plan,
|
|
656
|
IIF(ISNULL(classify_plan_start_dt,'')='','', CONVERT(VARCHAR(5), classify_plan_start_dt, 114)) AS classi_plan_time,
|
|
657
|
|
|
658
|
classify_act_start_dt AS classi_act,
|
|
659
|
ISNULL(DATEDIFF(hour, classify_plan_start_dt, classify_act_start_dt),99) AS classi_plan_diff,
|
|
660
|
IIF(ISNULL(classify_act_start_dt,'')='','', CONCAT( CONVERT(VARCHAR(13), classify_act_start_dt, 121),':00' ) ) AS block_classi_act,
|
|
661
|
IIF(ISNULL(classify_act_start_dt,'')='','', CONVERT(VARCHAR(5), classify_act_start_dt, 114)) AS classi_act_time
|
|
662
|
|
|
663
|
FROM [WMSASIDB].[dbo].[ProductionPlan] a
|
|
664
|
inner join (
|
|
665
|
$SQL
|
|
666
|
) lot on a.lot_no=lot.lot_no");
|
|
667
|
|
|
668
|
return $data;
|
|
669
|
//echo json_encode($data);
|
|
670
|
}
|
|
671
|
|
|
672
|
public function screen2(){
|
|
673
|
$data = $this->loadScreen2();
|
|
674
|
return view("display.screen2", ['lot_no'=> $data['lot_no'],]);
|
|
675
|
}
|
|
676
|
|
|
677
|
public function loadDataScreen3(){
|
|
678
|
$date = date("Y-m-d");
|
|
679
|
//$date = '2023-08-19';
|
|
680
|
$data['data_today'] = $this->loadScreen1($date);
|
|
681
|
$datePrev = date("Y-m-d", strtotime("-1 day", strtotime($date)));
|
|
682
|
$data['data_prev'] = $this->loadScreen1($datePrev);
|
|
683
|
echo json_encode($data);
|
|
684
|
}
|
|
685
|
|
|
686
|
public function screen3(){
|
|
687
|
return view("display.screen3");
|
|
688
|
}
|
|
689
|
|
|
690
|
public function loadDataScreen4(){
|
|
691
|
$month_mm = date('m');
|
|
692
|
//$month_mm = '08';
|
|
693
|
$year_20yy = date('Y');
|
|
694
|
$last_day_in_month = cal_days_in_month(CAL_GREGORIAN, $month_mm, $year_20yy);
|
|
695
|
|
|
696
|
$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
|
|
697
|
from (
|
|
698
|
SELECT sch_id, qty_in_lot, diebond_output, IIF(ISNULL(diebond_act_end_dt,'')='','', CONVERT(VARCHAR(5), diebond_act_end_dt, 105) ) AS dm
|
|
699
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
700
|
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'
|
|
701
|
) t
|
|
702
|
LEFT join (
|
|
703
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
704
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
705
|
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'
|
|
706
|
) s ON t.sch_id = s.sch_id
|
|
707
|
GROUP BY t.dm");
|
|
708
|
$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
|
|
709
|
from (
|
|
710
|
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
|
|
711
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
712
|
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'
|
|
713
|
) t
|
|
714
|
LEFT join (
|
|
715
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
716
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
717
|
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'
|
|
718
|
) s ON t.sch_id = s.sch_id
|
|
719
|
GROUP BY t.dm");
|
|
720
|
$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
|
|
721
|
from (
|
|
722
|
SELECT sch_id, qty_in_lot, wirebond_output, IIF(ISNULL(wirebond_act_end_dt,'')='','', CONVERT(VARCHAR(5), wirebond_act_end_dt, 105) ) AS dm
|
|
723
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
724
|
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'
|
|
725
|
) t
|
|
726
|
LEFT join (
|
|
727
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
728
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
729
|
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'
|
|
730
|
) s ON t.sch_id = s.sch_id
|
|
731
|
GROUP BY t.dm");
|
|
732
|
$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
|
|
733
|
from (
|
|
734
|
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
|
|
735
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
736
|
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'
|
|
737
|
) t
|
|
738
|
LEFT join (
|
|
739
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
740
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
741
|
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'
|
|
742
|
) s ON t.sch_id = s.sch_id
|
|
743
|
GROUP BY t.dm");
|
|
744
|
$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
|
|
745
|
from (
|
|
746
|
SELECT sch_id, qty_in_lot, mold_output, IIF(ISNULL(mold_act_end_dt,'')='','', CONVERT(VARCHAR(5), mold_act_end_dt, 105) ) AS dm
|
|
747
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
748
|
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'
|
|
749
|
) t
|
|
750
|
LEFT join (
|
|
751
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
752
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
753
|
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'
|
|
754
|
) s ON t.sch_id = s.sch_id
|
|
755
|
GROUP BY t.dm");
|
|
756
|
$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
|
|
757
|
from (
|
|
758
|
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
|
|
759
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
760
|
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'
|
|
761
|
) t
|
|
762
|
LEFT join (
|
|
763
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
764
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
765
|
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'
|
|
766
|
) s ON t.sch_id = s.sch_id
|
|
767
|
GROUP BY t.dm");
|
|
768
|
$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
|
|
769
|
from (
|
|
770
|
SELECT sch_id, qty_in_lot, dicer_output, IIF(ISNULL(dicer_act_end_dt,'')='','', CONVERT(VARCHAR(5), dicer_act_end_dt, 105) ) AS dm
|
|
771
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
772
|
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'
|
|
773
|
) t
|
|
774
|
LEFT join (
|
|
775
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
776
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
777
|
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'
|
|
778
|
) s ON t.sch_id = s.sch_id
|
|
779
|
GROUP BY t.dm");
|
|
780
|
$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
|
|
781
|
from (
|
|
782
|
SELECT sch_id, qty_in_lot, appearance_output, IIF(ISNULL(appearance_act_end_dt,'')='','', CONVERT(VARCHAR(5), appearance_act_end_dt, 105) ) AS dm
|
|
783
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
784
|
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'
|
|
785
|
) t
|
|
786
|
LEFT join (
|
|
787
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
788
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
789
|
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'
|
|
790
|
) s ON t.sch_id = s.sch_id
|
|
791
|
GROUP BY t.dm");
|
|
792
|
|
|
793
|
$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
|
|
794
|
from (
|
|
795
|
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
|
|
796
|
FROM [WMSASIDB].[dbo].[ProductionPlan]
|
|
797
|
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'
|
|
798
|
) t
|
|
799
|
LEFT join (
|
|
800
|
select sch_id, ISNULL(qty,0) as ngbonding , IIF(ISNULL(create_dt,'')='','', CONVERT(VARCHAR(5), create_dt, 105)) AS dm
|
|
801
|
from [WMSASIDB].[dbo].[NGFactorList]
|
|
802
|
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'
|
|
803
|
) s ON t.sch_id = s.sch_id
|
|
804
|
GROUP BY t.dm");
|
|
805
|
|
|
806
|
echo json_encode($data);
|
|
807
|
}
|
|
808
|
|
|
809
|
public function screen4(){
|
|
810
|
$month_3txt = date('M');
|
|
811
|
$month_mm = date('m');
|
|
812
|
//$month_mm = '08';
|
|
813
|
//$month_3txt = 'Aug';
|
|
814
|
$year_20yy = date('Y');
|
|
815
|
return view("display.screen4", ['month_3txt'=> $month_3txt, 'year_20yy'=> $year_20yy, 'month_mm'=> $month_mm,]);
|
|
816
|
}
|
|
817
|
|
|
818
|
}
|