n>
+ c1.title.font = Font(underline=Font.UNDERLINE_SINGLE)
|
|
|
111
|
+ c1.y_axis.title = "Std. Weight(g)"
|
|
|
112
|
+ c1.y_axis.number_format = "#,##0.00"
|
|
|
113
|
+ c1.x_axis.title = "Date"
|
|
|
114
|
+ c1.width = 30
|
|
|
115
|
+ c1.height = 10
|
|
|
116
|
+
|
|
|
117
|
+
|
|
|
118
|
+ #print(len(values))
|
|
|
119
|
+ MINDATACOL = 2
|
|
|
120
|
+ MAXDATACOL = 2 + 31
|
|
|
121
|
+ lt = Reference(wsd, min_col=3, max_col=33, min_row=5, max_row=5)
|
|
|
122
|
+
|
|
|
123
|
+ std = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=6, max_row=6)
|
|
|
124
|
+ data = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=10, max_row=10)
|
|
|
125
|
+
|
|
|
126
|
+ maxUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=15, max_row=15)
|
|
|
127
|
+ minUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=21, max_row=21)
|
|
|
128
|
+ avgUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=26, max_row=26)
|
|
|
129
|
+ vsStd = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=32, max_row=32)
|
|
|
130
|
+ vsStd2 = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=37, max_row=37)
|
|
|
131
|
+
|
|
|
132
|
+
|
|
|
133
|
+ c1.add_data(data, from_rows=True, titles_from_data=True)
|
|
|
134
|
+ c1.add_data(maxUB, from_rows=True, titles_from_data=True)
|
|
|
135
|
+ c1.add_data(minUB, from_rows=True, titles_from_data=True)
|
|
|
136
|
+ c1.add_data(avgUB, from_rows=True, titles_from_data=True)
|
|
|
137
|
+ c1.add_data(vsStd, from_rows=True, titles_from_data=True)
|
|
|
138
|
+ c1.add_data(vsStd2, from_rows=True, titles_from_data=True)
|
|
|
139
|
+ c1.add_data(std, from_rows=True, titles_from_data=True)
|
|
|
140
|
+
|
|
|
141
|
+ s1 = c1.series[0]
|
|
|
142
|
+ s1.marker.symbol = "triangle"
|
|
|
143
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
144
|
+
|
|
|
145
|
+ s1 = c1.series[1]
|
|
|
146
|
+ s1.marker.symbol = "circle"
|
|
|
147
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
148
|
+
|
|
|
149
|
+ s1 = c1.series[2]
|
|
|
150
|
+ s1.marker.symbol = "square"
|
|
|
151
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
152
|
+
|
|
|
153
|
+ s1 = c1.series[3]
|
|
|
154
|
+ s1.marker.symbol = "auto"
|
|
|
155
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
156
|
+
|
|
|
157
|
+ s1 = c1.series[4]
|
|
|
158
|
+ s1.marker.symbol = "diamond"
|
|
|
159
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
160
|
+
|
|
|
161
|
+ s1 = c1.series[5]
|
|
|
162
|
+ s1.marker.symbol = "auto"
|
|
|
163
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
164
|
+
|
|
|
165
|
+ s1 = c1.series[6]
|
|
|
166
|
+ #s1.marker.symbol = "diamond"
|
|
|
167
|
+ #s1.graphicalProperties.line.noFill = True
|
|
|
168
|
+
|
|
|
169
|
+ c1.set_categories(lt)
|
|
|
170
|
+
|
|
|
171
|
+ '''c1.x_axis.txPr = RichText(bodyPr=RichTextProperties(anchor="ctr",anchorCtr="1",rot="-2700000",
|
|
|
172
|
+ spcFirstLastPara="1",vertOverflow="ellipsis",wrap="square"),
|
|
|
173
|
+ p=[Paragraph(pPr=ParagraphProperties(defRPr=CharacterProperties()), endParaRPr=CharacterProperties())])'''
|
|
|
174
|
+
|
|
|
175
|
+ ws.add_chart(c1, "b3")
|
|
|
176
|
+ #pass
|
|
|
177
|
+def createNightChart(chartTitle):
|
|
|
178
|
+ global wb
|
|
|
179
|
+ ws = wb['Chart']
|
|
|
180
|
+ wsd = wb['Daily Check']
|
|
|
181
|
+ c1 = LineChart()
|
|
|
182
|
+ c1.legend.position = "t";
|
|
|
183
|
+ c1.title = chartTitle
|
|
|
184
|
+ c1.title.font = Font(underline=Font.UNDERLINE_SINGLE)
|
|
|
185
|
+ c1.y_axis.title = "Std. Weight(g)"
|
|
|
186
|
+ c1.y_axis.number_format = "#,##0.00"
|
|
|
187
|
+ c1.x_axis.title = "Date"
|
|
|
188
|
+ c1.width = 30
|
|
|
189
|
+ c1.height = 10
|
|
|
190
|
+
|
|
|
191
|
+
|
|
|
192
|
+ #print(len(values))
|
|
|
193
|
+ MINDATACOL = 2
|
|
|
194
|
+ MAXDATACOL = 2 + 31
|
|
|
195
|
+ lt = Reference(wsd, min_col=3, max_col=33, min_row=5, max_row=5)
|
|
|
196
|
+
|
|
|
197
|
+ std = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=6, max_row=6)
|
|
|
198
|
+ #begin data
|
|
|
199
|
+ data = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=51, max_row=51)
|
|
|
200
|
+
|
|
|
201
|
+ maxUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=56, max_row=56)
|
|
|
202
|
+ minUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=62, max_row=62)
|
|
|
203
|
+ avgUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=67, max_row=67)
|
|
|
204
|
+ vsStd = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=73, max_row=73)
|
|
|
205
|
+ vsStd2 = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=78, max_row=78)
|
|
|
206
|
+
|
|
|
207
|
+
|
|
|
208
|
+ c1.add_data(data, from_rows=True, titles_from_data=True)
|
|
|
209
|
+ c1.add_data(maxUB, from_rows=True, titles_from_data=True)
|
|
|
210
|
+ c1.add_data(minUB, from_rows=True, titles_from_data=True)
|
|
|
211
|
+ c1.add_data(avgUB, from_rows=True, titles_from_data=True)
|
|
|
212
|
+ c1.add_data(vsStd, from_rows=True, titles_from_data=True)
|
|
|
213
|
+ c1.add_data(vsStd2, from_rows=True, titles_from_data=True)
|
|
|
214
|
+ c1.add_data(std, from_rows=True, titles_from_data=True)
|
|
|
215
|
+
|
|
|
216
|
+ s1 = c1.series[0]
|
|
|
217
|
+ s1.marker.symbol = "triangle"
|
|
|
218
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
219
|
+
|
|
|
220
|
+ s1 = c1.series[1]
|
|
|
221
|
+ s1.marker.symbol = "circle"
|
|
|
222
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
223
|
+
|
|
|
224
|
+ s1 = c1.series[2]
|
|
|
225
|
+ s1.marker.symbol = "square"
|
|
|
226
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
227
|
+
|
|
|
228
|
+ s1 = c1.series[3]
|
|
|
229
|
+ s1.marker.symbol = "auto"
|
|
|
230
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
231
|
+
|
|
|
232
|
+ s1 = c1.series[4]
|
|
|
233
|
+ s1.marker.symbol = "diamond"
|
|
|
234
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
235
|
+
|
|
|
236
|
+ s1 = c1.series[5]
|
|
|
237
|
+ s1.marker.symbol = "auto"
|
|
|
238
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
239
|
+
|
|
|
240
|
+ s1 = c1.series[6]
|
|
|
241
|
+ #s1.marker.symbol = "diamond"
|
|
|
242
|
+ #s1.graphicalProperties.line.noFill = True
|
|
|
243
|
+
|
|
|
244
|
+ c1.set_categories(lt)
|
|
|
245
|
+
|
|
|
246
|
+ '''c1.x_axis.txPr = RichText(bodyPr=RichTextProperties(anchor="ctr",anchorCtr="1",rot="-2700000",
|
|
|
247
|
+ spcFirstLastPara="1",vertOverflow="ellipsis",wrap="square"),
|
|
|
248
|
+ p=[Paragraph(pPr=ParagraphProperties(defRPr=CharacterProperties()), endParaRPr=CharacterProperties())])'''
|
|
|
249
|
+
|
|
|
250
|
+ ws.add_chart(c1, "b22")
|
|
|
251
|
+
|
|
|
252
|
+def createExcel(codeno, filters):
|
|
|
253
|
+ global cursor
|
|
|
254
|
+ global wb
|
|
|
255
|
+ ws = wb["Daily Check"]
|
|
|
256
|
+
|
|
|
257
|
+
|
|
|
258
|
+ machineName = getMachineName(codeno)
|
|
|
259
|
+ machineDetail = getMachineDetail(codeno)
|
|
|
260
|
+
|
|
|
261
|
+ ws["C2"].value = machineName
|
|
|
262
|
+ ws["C3"].value = filters.replace("created_at between", "").replace("and", "to").replace("'", "")
|
|
|
263
|
+
|
|
|
264
|
+ ws["C43"].value = machineName
|
|
|
265
|
+ ws["C44"].value = filters.replace("created_at between", "").replace("and", "to").replace("'", "")
|
|
|
266
|
+
|
|
|
267
|
+ ws["C4"].value = machineDetail.machineStd;
|
|
|
268
|
+
|
|
|
269
|
+ if filters is not None:
|
|
|
270
|
+ q = "select * from daily_checks_wb where machine_id = '{0}' and {1}".format(codeno, filters)
|
|
|
271
|
+ else:
|
|
|
272
|
+ q = "select * from daily_checks_wb where machine_id = '{0}'".format(codeno)
|
|
|
273
|
+
|
|
|
274
|
+
|
|
|
275
|
+ #print(q)
|
|
|
276
|
+ cursor.execute(q)
|
|
|
277
|
+ rows = cursor.fetchall()
|
|
|
278
|
+ #print(rows)
|
|
|
279
|
+ a0740 = [];
|
|
|
280
|
+ a1230 = []
|
|
|
281
|
+ a1700 = []
|
|
|
282
|
+ a0030 = []
|
|
|
283
|
+ a1940 = []
|
|
|
284
|
+ a0500 = []
|
|
|
285
|
+
|
|
|
286
|
+ for i in range(31):
|
|
|
287
|
+ a0740.append([])
|
|
|
288
|
+ a1230.append([])
|
|
|
289
|
+ a1700.append([])
|
|
|
290
|
+ a0030.append([])
|
|
|
291
|
+ a1940.append([])
|
|
|
292
|
+ a0500.append([])
|
|
|
293
|
+
|
|
|
294
|
+
|
|
|
295
|
+ for r in rows:
|
|
|
296
|
+ i = r.created_at.day
|
|
|
297
|
+ if r.shift == "07.40":
|
|
|
298
|
+ a0740[i-1].append(r)
|
|
|
299
|
+ if r.shift == "12.30":
|
|
|
300
|
+ a1230[i-1].append(r)
|
|
|
301
|
+ if r.shift == "17.00":
|
|
|
302
|
+ #a1700.append(r)
|
|
|
303
|
+ a1700[i-1].append(r)
|
|
|
304
|
+ if r.shift == "00.30":
|
|
|
305
|
+ a0030[i-1].append(r)
|
|
|
306
|
+ if r.shift == "19.40":
|
|
|
307
|
+ a1940[i-1].append(r)
|
|
|
308
|
+ if r.shift == "05.00":
|
|
|
309
|
+ a0500[i-1].append(r)
|
|
|
310
|
+
|
|
|
311
|
+
|
|
|
312
|
+ #print(a0740)
|
|
|
313
|
+ for idx, val in enumerate(a0740):
|
|
|
314
|
+ j = 7
|
|
|
315
|
+ k = 12
|
|
|
316
|
+ users = []
|
|
|
317
|
+ #print(val)
|
|
|
318
|
+ ok = 0
|
|
|
319
|
+ ng = 0
|
|
|
320
|
+ for v in val:
|
|
|
321
|
+ #print(j , idx + )
|
|
|
322
|
+ #print(v.judgment)
|
|
|
323
|
+ if v.judgement == "OK":
|
|
|
324
|
+ ok += 1
|
|
|
325
|
+ if ok > 3:
|
|
|
326
|
+ continue
|
|
|
327
|
+ ws.cell(row = j , column = idx + 3 , value = v.weight)
|
|
|
328
|
+ j += 1
|
|
|
329
|
+ if v.judgement == "NG":
|
|
|
330
|
+ ng += 1
|
|
|
331
|
+ if ng > 3:
|
|
|
332
|
+ continue
|
|
|
333
|
+ ws.cell(row = k , column = idx + 3 , value = v.weight)
|
|
|
334
|
+ k += 1
|
|
|
335
|
+
|
|
|
336
|
+ #print(v.empid)
|
|
|
337
|
+ if v.empid not in users:
|
|
|
338
|
+ #print("insert in users")
|
|
|
339
|
+ users.append(v.empid)
|
|
|
340
|
+
|
|
|
341
|
+ #print(val)
|
|
|
342
|
+ if len(val) > 0:
|
|
|
343
|
+ q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users))
|
|
|
344
|
+ #print(q)
|
|
|
345
|
+ cursor.execute(q)
|
|
|
346
|
+ us = cursor.fetchall()
|
|
|
347
|
+ names = [x.fname + " " + x.lname for x in us]
|
|
|
348
|
+ ws.cell(row = 17, column = idx + 3, value = ",".join(names))
|
|
|
349
|
+
|
|
|
350
|
+ for idx, val in enumerate(a1230):
|
|
|
351
|
+ j = 18
|
|
|
352
|
+ k = 23
|
|
|
353
|
+ users = []
|
|
|
354
|
+ ok = ng = 0
|
|
|
355
|
+ for v in val:
|
|
|
356
|
+ #print(j , idx)
|
|
|
357
|
+ if v.judgement == "OK":
|
|
|
358
|
+ ok += 1
|
|
|
359
|
+ if ok > 3:
|
|
|
360
|
+ continue
|
|
|
361
|
+ ws.cell(row = j , column = idx + 3 , value = v.weight)
|
|
|
362
|
+ j += 1
|
|
|
363
|
+ if v.judgement == "NG":
|
|
|
364
|
+ ng += 1
|
|
|
365
|
+ if ng > 3:
|
|
|
366
|
+ continue
|
|
|
367
|
+
|
|
|
368
|
+ ws.cell(row = k , column = idx + 3 , value = v.weight)
|
|
|
369
|
+ k += 1
|
|
|
370
|
+
|
|
|
371
|
+ if v.empid not in users:
|
|
|
372
|
+ users.append(v.empid)
|
|
|
373
|
+
|
|
|
374
|
+ if len(val) > 0:
|
|
|
375
|
+ q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users))
|
|
|
376
|
+ #print(q)
|
|
|
377
|
+ cursor.execute(q)
|
|
|
378
|
+ us = cursor.fetchall()
|
|
|
379
|
+ names = [x.fname + " " + x.lname for x in us]
|
|
|
380
|
+ ws.cell(row = 28, column = idx + 3, value = ",".join(names))
|
|
|
381
|
+
|
|
|
382
|
+ for idx, val in enumerate(a1700):
|
|
|
383
|
+ j = 29
|
|
|
384
|
+ k = 34
|
|
|
385
|
+ users = []
|
|
|
386
|
+ ok = ng = 0
|
|
|
387
|
+
|
|
|
388
|
+ for v in val:
|
|
|
389
|
+ #print(j , idx)
|
|
|
390
|
+ if v.judgement == "OK":
|
|
|
391
|
+ ok += 1
|
|
|
392
|
+ if ok > 3:
|
|
|
393
|
+ continue
|
|
|
394
|
+
|
|
|
395
|
+ ws.cell(row = j , column = idx + 3 , value = v.weight)
|
|
|
396
|
+ j += 1
|
|
|
397
|
+ if v.judgement == "NG":
|
|
|
398
|
+ ng += 1
|
|
|
399
|
+ if ng > 3:
|
|
|
400
|
+ continue
|
|
|
401
|
+
|
|
|
402
|
+ ws.cell(row = k , column = idx + 3 , value = v.weight)
|
|
|
403
|
+ k += 1
|
|
|
404
|
+ if v.empid not in users:
|
|
|
405
|
+ users.append(v.empid)
|
|
|
406
|
+
|
|
|
407
|
+ if len(val) > 0:
|
|
|
408
|
+ q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users))
|
|
|
409
|
+ #print(q)
|
|
|
410
|
+ cursor.execute(q)
|
|
|
411
|
+ us = cursor.fetchall()
|
|
|
412
|
+ names = [x.fname + " " + x.lname for x in us]
|
|
|
413
|
+ ws.cell(row = 39, column = idx + 3, value = ",".join(names))
|
|
|
414
|
+
|
|
|
415
|
+ for idx, val in enumerate(a1940):
|
|
|
416
|
+ j = 47
|
|
|
417
|
+ k = 53
|
|
|
418
|
+ users = []
|
|
|
419
|
+ ok = ng = 0
|
|
|
420
|
+ for v in val:
|
|
|
421
|
+ #print(j , idx)
|
|
|
422
|
+ if v.judgement == "OK":
|
|
|
423
|
+ ok += 1
|
|
|
424
|
+ if ok > 3:
|
|
|
425
|
+ continue
|
|
|
426
|
+
|
|
|
427
|
+ ws.cell(row = j , column = idx + 3 , value = v.weight)
|
|
|
428
|
+ j += 1
|
|
|
429
|
+ if v.judgement == "NG":
|
|
|
430
|
+ ng += 1
|
|
|
431
|
+ if ng > 3:
|
|
|
432
|
+ continue
|
|
|
433
|
+
|
|
|
434
|
+ ws.cell(row = k , column = idx + 3 , value = v.weight)
|
|
|
435
|
+ k += 1
|
|
|
436
|
+ if v.empid not in users:
|
|
|
437
|
+ users.append(v.empid)
|
|
|
438
|
+
|
|
|
439
|
+ if len(val) > 0:
|
|
|
440
|
+ q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users))
|
|
|
441
|
+
|
|
|
442
|
+ #print(q)
|
|
|
443
|
+ cursor.execute(q)
|
|
|
444
|
+ us = cursor.fetchall()
|
|
|
445
|
+ names = [x.fname + " " + x.lname for x in us]
|
|
|
446
|
+ ws.cell(row = 58, column = idx + 3, value = ",".join(names))
|
|
|
447
|
+
|
|
|
448
|
+ for idx, val in enumerate(a0030):
|
|
|
449
|
+ j = 59
|
|
|
450
|
+ k = 64
|
|
|
451
|
+ users = []
|
|
|
452
|
+ ok = ng = 0
|
|
|
453
|
+ for v in val:
|
|
|
454
|
+ #print(j , idx)
|
|
|
455
|
+ if v.judgement == "OK":
|
|
|
456
|
+ ok += 1
|
|
|
457
|
+ if ok > 3:
|
|
|
458
|
+ continue
|
|
|
459
|
+
|
|
|
460
|
+ ws.cell(row = j , column = idx + 3 , value = v.weight)
|
|
|
461
|
+ j += 1
|
|
|
462
|
+ if v.judgement == "NG":
|
|
|
463
|
+ ng += 1
|
|
|
464
|
+ if ng > 3:
|
|
|
465
|
+ continue
|
|
|
466
|
+
|
|
|
467
|
+ ws.cell(row = k , column = idx + 3 , value = v.weight)
|
|
|
468
|
+ k += 1
|
|
|
469
|
+ if v.empid not in users:
|
|
|
470
|
+ users.append(v.empid)
|
|
|
471
|
+
|
|
|
472
|
+ if len(val) > 0:
|
|
|
473
|
+ q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users))
|
|
|
474
|
+
|
|
|
475
|
+ #print(q)
|
|
|
476
|
+ cursor.execute(q)
|
|
|
477
|
+ us = cursor.fetchall()
|
|
|
478
|
+ names = [x.fname + " " + x.lname for x in us]
|
|
|
479
|
+ ws.cell(row = 69, column = idx + 3, value = ",".join(names))
|
|
|
480
|
+
|
|
|
481
|
+ for idx, val in enumerate(a0500):
|
|
|
482
|
+ j = 70
|
|
|
483
|
+ k = 75
|
|
|
484
|
+ users = []
|
|
|
485
|
+ ok = ng = 0
|
|
|
486
|
+ for v in val:
|
|
|
487
|
+ #print(j , idx)
|
|
|
488
|
+ if v.judgement == "OK":
|
|
|
489
|
+ ok += 1
|
|
|
490
|
+ if ok > 3:
|
|
|
491
|
+ continue
|
|
|
492
|
+
|
|
|
493
|
+ ws.cell(row = j , column = idx + 3 , value = v.weight)
|
|
|
494
|
+ j += 1
|
|
|
495
|
+ if v.judgement == "NG":
|
|
|
496
|
+ ng += 1
|
|
|
497
|
+ if ng > 3:
|
|
|
498
|
+ continue
|
|
|
499
|
+ ws.cell(row = k , column = idx + 3 , value = v.weight)
|
|
|
500
|
+ k += 1
|
|
|
501
|
+ if v.empid not in users:
|
|
|
502
|
+ users.append(v.empid)
|
|
|
503
|
+
|
|
|
504
|
+ if len(val) > 0:
|
|
|
505
|
+ q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users))
|
|
|
506
|
+ #print(q)
|
|
|
507
|
+ cursor.execute(q)
|
|
|
508
|
+ us = cursor.fetchall()
|
|
|
509
|
+ names = [x.fname + " " + x.lname for x in us]
|
|
|
510
|
+ ws.cell(row = 80, column = idx + 3, value = ",".join(names))
|
|
|
511
|
+
|
|
|
512
|
+ createDayChart("Daily Check of Day Shift ({0})".format(machineName))
|
|
|
513
|
+
|
|
|
514
|
+ createNightChart("Daily Check of Night Shift ({0})".format(machineName))
|
|
|
515
|
+
|
|
|
516
|
+ ut = int(time.time())
|
|
|
517
|
+ filename = "dc{0}.xlsx".format(str(ut))
|
|
|
518
|
+ outfile = './public/excel/'+filename
|
|
|
519
|
+ wb.save(filename = outfile)
|
|
|
520
|
+ print(filename)
|
|
|
521
|
+
|
|
|
522
|
+if __name__ == "__main__":
|
|
|
523
|
+ env = sys.argv[1]
|
|
|
524
|
+ machine = sys.argv[2]
|
|
|
525
|
+ #print(len(sys.argv))
|
|
|
526
|
+
|
|
|
527
|
+ df = None
|
|
|
528
|
+ if len(sys.argv) == 4:
|
|
|
529
|
+ filter = sys.argv[3]
|
|
|
530
|
+ #import ast
|
|
|
531
|
+ #df = ast.literal_eval(filter)
|
|
|
532
|
+ #print(df['date'])
|
|
|
533
|
+ df = filter
|
|
|
534
|
+
|
|
|
535
|
+ global cnxn
|
|
|
536
|
+ global cursor
|
|
|
537
|
+
|
|
|
538
|
+ if env == "prod":
|
|
|
539
|
+ cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.0.253;DATABASE=OB2011DB;UID=user1;PWD=1234')
|
|
|
540
|
+ cursor = cnxn.cursor()
|
|
|
541
|
+ else:
|
|
|
542
|
+ cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=OB2011DB;UID=admin;PWD=1234')
|
|
|
543
|
+ cursor = cnxn.cursor()
|
|
|
544
|
+ #print("code is "+code)
|
|
|
545
|
+
|
|
|
546
|
+ createExcel(machine, df)
|
|
|
@@ -0,0 +1,352 @@
|
|
|
1
|
+from openpyxl import load_workbook
|
|
|
2
|
+import pyodbc
|
|
|
3
|
+import time
|
|
|
4
|
+import sys
|
|
|
5
|
+from pprint import pprint
|
|
|
6
|
+import datetime
|
|
|
7
|
+from openpyxl.styles import NamedStyle, Font
|
|
|
8
|
+from copy import deepcopy
|
|
|
9
|
+from openpyxl.chart import LineChart, Reference, Series
|
|
|
10
|
+from openpyxl.chart.text import RichText
|
|
|
11
|
+from openpyxl.drawing.text import RichTextProperties,Paragraph,ParagraphProperties, CharacterProperties
|
|
|
12
|
+
|
|
|
13
|
+dateStyle = NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM')
|
|
|
14
|
+
|
|
|
15
|
+# Specifying the ODBC driver, server name, database, etc. directly
|
|
|
16
|
+#cnxn = None
|
|
|
17
|
+#cursor = None
|
|
|
18
|
+'''cursor.execute('select * from data_wb')
|
|
|
19
|
+rows = cursor.fetchall()
|
|
|
20
|
+for row in rows:
|
|
|
21
|
+ print(row)'''
|
|
|
22
|
+
|
|
|
23
|
+wb = load_workbook(filename = './datacpmaster2.xlsx')
|
|
|
24
|
+
|
|
|
25
|
+#wb.save(filename = './createfrompy.xlsx');
|
|
|
26
|
+def findCustomer(code):
|
|
|
27
|
+ global cursor
|
|
|
28
|
+ global env
|
|
|
29
|
+
|
|
|
30
|
+ tempResult = []
|
|
|
31
|
+ views = []
|
|
|
32
|
+ if env == "prod":
|
|
|
33
|
+ views = ["bel_master_view", "e_master_view", "mg_master_view", "v_master_view"]
|
|
|
34
|
+ else:
|
|
|
35
|
+ views = ["bel_master_view_dev", "e_master_view_dev", "mg_master_view_dev", "v_master_view_dev"]
|
|
|
36
|
+
|
|
|
37
|
+ ts = []
|
|
|
38
|
+
|
|
|
39
|
+ select = "distinct MI24 as unbStd, PRO1C as customer, cast(PRO8 as varchar) + ' ' + cast(PRO9 as varchar) + cast(PRO10 as varchar) + 'x' + cast(PRO11 as varchar ) + 'x' + cast(PRO12 as varchar) as sizestring, cast(PRO13 as varchar) + ' ' + cast(PRO14 as varchar) + ' ' + cast(PRO15 as varchar) + ' ' + cast(PRO16 as varchar) + ' ' + cast(PRO17 as varchar) + ' ' + cast(PRO18 as varchar) as spec"
|
|
|
40
|
+ where = "PRO1 = '{0}'".format(code)
|
|
|
41
|
+ for v in views:
|
|
|
42
|
+ sqlQuery = "select {0} from {1} where {2}".format(select, v, where)
|
|
|
43
|
+ ts.append(sqlQuery)
|
|
|
44
|
+ merged = []
|
|
|
45
|
+ for t in ts:
|
|
|
46
|
+ merged += cursor.execute(t)
|
|
|
47
|
+ return merged
|
|
|
48
|
+def createWS(name, values, codeno, filters):
|
|
|
49
|
+ global wb
|
|
|
50
|
+ #wb.create_sheet(name)
|
|
|
51
|
+ wo = wb['Data Cp']
|
|
|
52
|
+ ws = wb.copy_worksheet(wo)
|
|
|
53
|
+ ws.title = name
|
|
|
54
|
+ cust = findCustomer(codeno.replace("-", ""))
|
|
|
55
|
+ ws["B2"] = cust[0].customer
|
|
|
56
|
+ ws["B3"] = codeno.replace("-", "")
|
|
|
57
|
+ ws["B4"] = cust[0].sizestring
|
|
|
58
|
+ ws["B5"] = cust[0].spec
|
|
|
59
|
+
|
|
|
60
|
+ bcode = ord('B')
|
|
|
61
|
+ ccode = 2
|
|
|
62
|
+ for idx, val in enumerate(values):
|
|
|
63
|
+ #print(idx)
|
|
|
64
|
+ c = chr(bcode + idx)
|
|
|
65
|
+ #print(val.lot_no)
|
|
|
66
|
+ if ( bcode + idx) > ord('Z'):
|
|
|
67
|
+ r = bcode + idx - ord('Z')
|
|
|
68
|
+ c = 'A' + chr(ord('A') + (r - 1))
|
|
|
69
|
+ #print("c = "+c)
|
|
|
70
|
+
|
|
|
71
|
+ ws.cell(column= ccode, row=45, value = val.lot_no)
|
|
|
72
|
+ ms = findInMasterView('*', "PRO2 = '{0}'".format(val.lot_no))
|
|
|
73
|
+ #pressInfo = findPressInfo(codeno.replace("-", ""),val.lot_no, filters)
|
|
|
74
|
+ #print("master view")
|
|
|
75
|
+ #print(ms)
|
|
|
76
|
+ if ms:
|
|
|
77
|
+ ws.cell(column = ccode, row=35, value = ms[0].MI24)
|
|
|
78
|
+ #ws[c+'38'] = ms[0].STD_Balance
|
|
|
79
|
+ ws.cell(column = ccode , row=37, value = ms[0].PRO6)
|
|
|
80
|
+ #ws[c+'44'] = ms[0].PRO5
|
|
|
81
|
+ ws.cell(column = ccode, row = 43, value = ms[0].PRO10)
|
|
|
82
|
+ ws.cell(column = ccode, row = 42, value = ms[0].PRO12)
|
|
|
83
|
+
|
|
|
84
|
+ #if pressInfo:
|
|
|
85
|
+ ws.cell(column = ccode , row=39, value = val.Press_UserName)
|
|
|
86
|
+ ws.cell(column = ccode, row = 40, value = val.Press_McName)
|
|
|
87
|
+ #print(type(pressInfo[0].Insp_Date))
|
|
|
88
|
+ #ws[c+'46'].number_format = "Short Date"
|
|
|
89
|
+ ws.cell(column = ccode, row = 46 , value = val.Insp_Date.date())
|
|
|
90
|
+ #print(type(ws[c+'46'].value))
|
|
|
91
|
+ #ws[c+'46'].style = dateStyle
|
|
|
92
|
+ #ws[c+'46'].value = datetime.datetime(2010, 7, 21)
|
|
|
93
|
+
|
|
|
94
|
+ #print(ws[c+'46'].number_format)
|
|
|
95
|
+
|
|
|
96
|
+ #ws[c+'46']. = datetime.datetime
|
|
|
97
|
+ ws.cell(column = ccode , row = 44, value = val.FG_Qty)
|
|
|
98
|
+
|
|
|
99
|
+ dws = updateLength(val.lot_no)
|
|
|
100
|
+ for j,v in enumerate(dws):
|
|
|
101
|
+ ws.cell(column = ccode , row = 48+v.row_no , value = v.weight)
|
|
|
102
|
+
|
|
|
103
|
+ dws = updateUb(val.lot_no)
|
|
|
104
|
+ for j,v in enumerate(dws):
|
|
|
105
|
+ ws.cell(column = ccode , row = 171+v.row_no , value = v.weight)
|
|
|
106
|
+
|
|
|
107
|
+ ccode += 1
|
|
|
108
|
+
|
|
|
109
|
+ c1 = LineChart()
|
|
|
110
|
+ c1.legend.position = "t";
|
|
|
111
|
+ c1.title = "Graph of Result Cp Unbalance"
|
|
|
112
|
+ c1.title.font = Font(underline=Font.UNDERLINE_SINGLE)
|
|
|
113
|
+ c1.y_axis.title = "Unb Weight"
|
|
|
114
|
+ c1.y_axis.number_format = "#,##0.00"
|
|
|
115
|
+ c1.x_axis.title = "Lot No"
|
|
|
116
|
+ c1.width = 30
|
|
|
117
|
+ c1.height = 10
|
|
|
118
|
+
|
|
|
119
|
+
|
|
|
120
|
+ #print(len(values))
|
|
|
121
|
+ lt = Reference(ws, min_col=2, max_col=len(values)+1, min_row=45, max_row=45)
|
|
|
122
|
+ data = Reference(ws, min_col=1, max_col=len(values)+1, min_row=34, max_row=34)
|
|
|
123
|
+
|
|
|
124
|
+ maxUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=29, max_row=29)
|
|
|
125
|
+ minUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=30, max_row=30)
|
|
|
126
|
+ avgUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=31, max_row=31)
|
|
|
127
|
+ vsStd = Reference(ws, min_col=1, max_col=len(values)+1, min_row=36, max_row=36)
|
|
|
128
|
+
|
|
|
129
|
+
|
|
|
130
|
+ c1.add_data(data, from_rows=True, titles_from_data=True)
|
|
|
131
|
+ c1.add_data(minUB, from_rows=True, titles_from_data=True)
|
|
|
132
|
+ c1.add_data(avgUB, from_rows=True, titles_from_data=True)
|
|
|
133
|
+ c1.add_data(vsStd, from_rows=True, titles_from_data=True)
|
|
|
134
|
+ c1.add_data(maxUB, from_rows=True, titles_from_data=True)
|
|
|
135
|
+
|
|
|
136
|
+
|
|
|
137
|
+ s1 = c1.series[0]
|
|
|
138
|
+ s1.marker.symbol = "triangle"
|
|
|
139
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
140
|
+
|
|
|
141
|
+ s1 = c1.series[1]
|
|
|
142
|
+ s1.marker.symbol = "circle"
|
|
|
143
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
144
|
+
|
|
|
145
|
+ s1 = c1.series[2]
|
|
|
146
|
+ s1.marker.symbol = "square"
|
|
|
147
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
148
|
+
|
|
|
149
|
+ s1 = c1.series[4]
|
|
|
150
|
+ s1.marker.symbol = "diamond"
|
|
|
151
|
+ s1.graphicalProperties.line.noFill = True
|
|
|
152
|
+
|
|
|
153
|
+ c1.set_categories(lt)
|
|
|
154
|
+
|
|
|
155
|
+ c1.x_axis.txPr = RichText(bodyPr=RichTextProperties(anchor="ctr",anchorCtr="1",rot="-2700000",
|
|
|
156
|
+ spcFirstLastPara="1",vertOverflow="ellipsis",wrap="square"),
|
|
|
157
|
+ p=[Paragraph(pPr=ParagraphProperties(defRPr=CharacterProperties()), endParaRPr=CharacterProperties())])
|
|
|
158
|
+
|
|
|
159
|
+ ws.add_chart(c1, "A7")
|
|
|
160
|
+ #newws.title = name
|
|
|
161
|
+ #copy data cp ws
|
|
|
162
|
+ #insert values in the sheet
|
|
|
163
|
+def findInMasterView(select, where):
|
|
|
164
|
+ global cursor
|
|
|
165
|
+ global env
|
|
|
166
|
+
|
|
|
167
|
+ tempResult = []
|
|
|
168
|
+ views = []
|
|
|
169
|
+ if env == "prod":
|
|
|
170
|
+ views = ["bel_master_view", "e_master_view", "mg_master_view", "v_master_view"]
|
|
|
171
|
+ else:
|
|
|
172
|
+ views = ["bel_master_view_dev", "e_master_view_dev", "mg_master_view_dev", "v_master_view_dev"]
|
|
|
173
|
+
|
|
|
174
|
+
|
|
|
175
|
+
|
|
|
176
|
+ ts = []
|
|
|
177
|
+ for v in views:
|
|
|
178
|
+ sqlQuery = "select {0} from {1} where {2}".format(select, v, where)
|
|
|
179
|
+ ts.append(sqlQuery);
|
|
|
180
|
+
|
|
|
181
|
+ qj = ';'.join(ts)
|
|
|
182
|
+ #print(qj)
|
|
|
183
|
+ merged = []
|
|
|
184
|
+ for t in ts:
|
|
|
185
|
+ merged += cursor.execute(t)
|
|
|
186
|
+ if len(merged) > 0:
|
|
|
187
|
+ return merged
|
|
|
188
|
+ return merged
|
|
|
189
|
+
|
|
|
190
|
+def findPressInfo(code, lot, year):
|
|
|
191
|
+ global cursor
|
|
|
192
|
+ global env
|
|
|
193
|
+
|
|
|
194
|
+ fyear = year.replace("created_at", "Insp_Date")
|
|
|
195
|
+ q = "select * from tb_fg_pressinfo_lotlist where ProductCode = '{0}' and LotNo = '{1}' and {2}" \
|
|
|
196
|
+ .format(code, lot, fyear)
|
|
|
197
|
+
|
|
|
198
|
+ cursor.execute(q)
|
|
|
199
|
+ return cursor.fetchall()
|
|
|
200
|
+
|
|
|
201
|
+def updateLength(lotno):
|
|
|
202
|
+ global cursor
|
|
|
203
|
+ global wb
|
|
|
204
|
+
|
|
|
205
|
+ q = "select * from data_wb where lot_no = '{0}' and judgement='OK' order by row_no asc".format(lotno)
|
|
|
206
|
+ #print(q);
|
|
|
207
|
+ return cursor.execute(q)
|
|
|
208
|
+
|
|
|
209
|
+def updateUb(lotno):
|
|
|
210
|
+ global cursor
|
|
|
211
|
+ global wb
|
|
|
212
|
+
|
|
|
213
|
+ q = "select * from data_wb where lot_no = '{0}' and judgement='NG' order by row_no asc".format(lotno)
|
|
|
214
|
+ #print(q);
|
|
|
215
|
+ return cursor.execute(q)
|
|
|
216
|
+
|
|
|
217
|
+def createExcel(codeno, filters):
|
|
|
218
|
+ global cursor
|
|
|
219
|
+ global wb
|
|
|
220
|
+ global dateStyle
|
|
|
221
|
+
|
|
|
222
|
+ ws = wb["Data Cp"]
|
|
|
223
|
+ codeString = ""
|
|
|
224
|
+
|
|
|
225
|
+ if codeno.find('-') != -1:
|
|
|
226
|
+ codeString = codeno
|
|
|
227
|
+ else:
|
|
|
228
|
+ codeString = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10]
|
|
|
229
|
+
|
|
|
230
|
+ if filters is not None:
|
|
|
231
|
+ q = """select * from lot_summary_wb as lt
|
|
|
232
|
+ inner join tb_fg_pressinfo_lotlist as press
|
|
|
233
|
+ on lt.lot_no = press.LotNo
|
|
|
234
|
+ and replace(lt.code, '-', '') = press.ProductCode
|
|
|
235
|
+ where code = '{0}' and {1}
|
|
|
236
|
+ order by press.Insp_Date asc
|
|
|
237
|
+ """.format(codeString, filters)
|
|
|
238
|
+ else:
|
|
|
239
|
+ q = """select * from lot_summary_wb as lt
|
|
|
240
|
+inner join tb_fg_pressinfo_lotlist as press
|
|
|
241
|
+on lt.lot_no = press.LotNo
|
|
|
242
|
+and replace(lt.code, '-', '') = press.ProductCodes
|
|
|
243
|
+where code = '{0}'
|
|
|
244
|
+order by press.Insp_Date asc
|
|
|
245
|
+ """.format(codeString)
|
|
|
246
|
+
|
|
|
247
|
+ #print(q)
|
|
|
248
|
+ cursor.execute(q)
|
|
|
249
|
+ rows = cursor.fetchall()
|
|
|
250
|
+ #print(rows)
|
|
|
251
|
+ y = {}
|
|
|
252
|
+ for r in rows:
|
|
|
253
|
+ t = "{0}-{1}".format(r.created_at.year, r.created_at.month)
|
|
|
254
|
+
|
|
|
255
|
+ if t in y:
|
|
|
256
|
+ y[t].append(r)
|
|
|
257
|
+ else:
|
|
|
258
|
+ y[t] = []
|
|
|
259
|
+ y[t].append(r)
|
|
|
260
|
+
|
|
|
261
|
+ #print(y)
|
|
|
262
|
+
|
|
|
263
|
+ for k, v in y.items():
|
|
|
264
|
+ createWS(k,v, codeno, filters)
|
|
|
265
|
+ #print(ws._charts)
|
|
|
266
|
+ cust = findCustomer(codeno.replace("-", ""))
|
|
|
267
|
+ ws["B2"] = cust[0].customer
|
|
|
268
|
+ ws["B3"] = codeno.replace("-", "")
|
|
|
269
|
+ ws["B4"] = cust[0].sizestring
|
|
|
270
|
+ ws["B5"] = cust[0].spec
|
|
|
271
|
+
|
|
|
272
|
+ bcode = ord('B')
|
|
|
273
|
+ ccode = 2
|
|
|
274
|
+ for idx, val in enumerate(rows):
|
|
|
275
|
+ #print(idx)
|
|
|
276
|
+ c = chr(bcode + idx)
|
|
|
277
|
+ #print(val.lot_no)
|
|
|
278
|
+ if ( bcode + idx) > ord('Z'):
|
|
|
279
|
+ r = bcode + idx - ord('Z')
|
|
|
280
|
+ c = 'A' + chr(ord('A') + (r - 1))
|
|
|
281
|
+ #print("c = "+c)
|
|
|
282
|
+
|
|
|
283
|
+ ws.cell(column= ccode, row=45, value = val.lot_no)
|
|
|
284
|
+ ms = findInMasterView('*', "PRO2 = '{0}'".format(val.lot_no))
|
|
|
285
|
+ #pressInfo = findPressInfo(codeno.replace("-", ""),val.lot_no, filters)
|
|
|
286
|
+ #print("master view")
|
|
|
287
|
+ #print("ms")
|
|
|
288
|
+ #print(ms)
|
|
|
289
|
+ if ms:
|
|
|
290
|
+ ws.cell(column = ccode, row=35, value = ms[0].MI24)
|
|
|
291
|
+ #ws[c+'38'] = ms[0].STD_Balance
|
|
|
292
|
+ ws.cell(column = ccode , row=37, value = ms[0].PRO6)
|
|
|
293
|
+ #ws[c+'44'] = ms[0].PRO5
|
|
|
294
|
+ ws.cell(column = ccode, row = 43, value = ms[0].PRO10)
|
|
|
295
|
+ ws.cell(column = ccode, row = 42, value = ms[0].PRO12)
|
|
|
296
|
+
|
|
|
297
|
+ #if pressInfo:
|
|
|
298
|
+ ws.cell(column = ccode , row=39, value = val.Press_UserName)
|
|
|
299
|
+ ws.cell(column = ccode, row = 40, value = val.Press_McName)
|
|
|
300
|
+ #print(type(pressInfo[0].Insp_Date))
|
|
|
301
|
+ #ws[c+'46'].number_format = "Short Date"
|
|
|
302
|
+ ws.cell(column = ccode, row = 46 , value = val.Insp_Date.date())
|
|
|
303
|
+ #print(type(ws[c+'46'].value))
|
|
|
304
|
+ #ws[c+'46'].style = dateStyle
|
|
|
305
|
+ #ws[c+'46'].value = datetime.datetime(2010, 7, 21)
|
|
|
306
|
+
|
|
|
307
|
+ #print(ws[c+'46'].number_format)
|
|
|
308
|
+
|
|
|
309
|
+ #ws[c+'46']. = datetime.datetime
|
|
|
310
|
+ ws.cell(column = ccode , row = 44, value = val.FG_Qty)
|
|
|
311
|
+
|
|
|
312
|
+ dws = updateLength(val.lot_no)
|
|
|
313
|
+ for j,v in enumerate(dws):
|
|
|
314
|
+ ws.cell(column = ccode , row = 48+v.row_no , value = v.weight)
|
|
|
315
|
+
|
|
|
316
|
+ dws = updateUb(val.lot_no)
|
|
|
317
|
+ for j,v in enumerate(dws):
|
|
|
318
|
+ ws.cell(column = ccode , row = 171+v.row_no , value = v.weight)
|
|
|
319
|
+
|
|
|
320
|
+ ccode += 1
|
|
|
321
|
+
|
|
|
322
|
+ ut = int(time.time())
|
|
|
323
|
+ filename = "cp{0}.xlsx".format(codeno.replace("-", ""))
|
|
|
324
|
+ outfile = './public/excel/'+filename
|
|
|
325
|
+ wb.save(filename = outfile)
|
|
|
326
|
+ print(filename)
|
|
|
327
|
+
|
|
|
328
|
+if __name__ == "__main__":
|
|
|
329
|
+ env = sys.argv[1]
|
|
|
330
|
+ code = sys.argv[2]
|
|
|
331
|
+ #print(len(sys.argv))
|
|
|
332
|
+
|
|
|
333
|
+ df = None
|
|
|
334
|
+ if len(sys.argv) == 4:
|
|
|
335
|
+ filter = sys.argv[3]
|
|
|
336
|
+ #import ast
|
|
|
337
|
+ #df = ast.literal_eval(filter)
|
|
|
338
|
+ #print(df['date'])
|
|
|
339
|
+ df = filter
|
|
|
340
|
+
|
|
|
341
|
+ global cnxn
|
|
|
342
|
+ global cursor
|
|
|
343
|
+
|
|
|
344
|
+ if env == "prod":
|
|
|
345
|
+ cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.0.253;DATABASE=OB2011DB;UID=user1;PWD=1234')
|
|
|
346
|
+ cursor = cnxn.cursor()
|
|
|
347
|
+ else:
|
|
|
348
|
+ cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=db;DATABASE=OB2011DB;UID=admin;PWD=1234')
|
|
|
349
|
+ cursor = cnxn.cursor()
|
|
|
350
|
+ #print("code is "+code)
|
|
|
351
|
+
|
|
|
352
|
+ createExcel(code, df)
|
|
|
@@ -0,0 +1,3 @@
|
|
|
1
|
+from django.db import models
|
|
|
2
|
+
|
|
|
3
|
+# Create your models here.
|
|
|
@@ -0,0 +1,38 @@
|
|
|
1
|
+{% extends 'base.html' %}
|
|
|
2
|
+
|
|
|
3
|
+{% block title %}Report Dashboard{% endblock %}
|
|
|
4
|
+
|
|
|
5
|
+{% block content %}
|
|
|
6
|
+<div class="container mx-auto px-4 py-8">
|
|
|
7
|
+ <h1 class="text-3xl font-bold text-center mb-4">Welcome to the Report Dashboard</h1>
|
|
|
8
|
+ <p class="text-center text-gray-600">Below is the list of reports currently available:</p>
|
|
|
9
|
+
|
|
|
10
|
+<form class="max-w-sm mx-auto">
|
|
|
11
|
+ <label for="countries" class="block mb-2 text-sm font-medium text-gray-900 dark:text-white">Select an option</label>
|
|
|
12
|
+ <select id="countries" class="bg-gray-50 border border-gray-300 text-gray-900 text-sm rounded-lg focus:ring-blue-500 focus:border-blue-500 block w-full p-2.5 dark:bg-gray-700 dark:border-gray-600 dark:placeholder-gray-400 dark:text-white dark:focus:ring-blue-500 dark:focus:border-blue-500">
|
|
|
13
|
+ <option selected>Choose a country</option>
|
|
|
14
|
+ <option value="US">United States</option>
|
|
|
15
|
+ <option value="CA">Canada</option>
|
|
|
16
|
+ <option value="FR">France</option>
|
|
|
17
|
+ <option value="DE">Germany</option>
|
|
|
18
|
+ </select>
|
|
|
19
|
+</form>
|
|
|
20
|
+
|
|
|
21
|
+
|
|
|
22
|
+ <!-- Reports List -->
|
|
|
23
|
+ <div class="mt-6">
|
|
|
24
|
+ {% if reports %}
|
|
|
25
|
+ <ul class="bg-white rounded shadow p-4">
|
|
|
26
|
+ {% for report in reports %}
|
|
|
27
|
+ <li class="border-b last:border-none py-2">
|
|
|
28
|
+ <strong>{{ report.name }}</strong>
|
|
|
29
|
+ <span class="text-sm text-gray-500">- Created on {{ report.date_created }}</span>
|
|
|
30
|
+ </li>
|
|
|
31
|
+ {% endfor %}
|
|
|
32
|
+ </ul>
|
|
|
33
|
+ {% else %}
|
|
|
34
|
+ <p class="text-gray-500 text-center">No reports available at the moment. Please check back later.</p>
|
|
|
35
|
+ {% endif %}
|
|
|
36
|
+ </div>
|
|
|
37
|
+</div>
|
|
|
38
|
+{% endblock %}
|
|
|
@@ -0,0 +1,3 @@
|
|
|
1
|
+from django.test import TestCase
|
|
|
2
|
+
|
|
|
3
|
+# Create your tests here.
|
|
|
@@ -0,0 +1,13 @@
|
|
|
1
|
+from django.urls import path
|
|
|
2
|
+from . import views
|
|
|
3
|
+
|
|
|
4
|
+app_name = "report" # Use this namespace for reverse URL lookups
|
|
|
5
|
+
|
|
|
6
|
+urlpatterns = [
|
|
|
7
|
+ path('', views.index, name='index'), # Homepage for the report app
|
|
|
8
|
+ # path('create/', views.create_report, name='create'), # Create a new report
|
|
|
9
|
+ # path('<int:pk>/', views.detail_report, name='detail'), # View details of a specific report
|
|
|
10
|
+ # path('<int:pk>/update/', views.update_report, name='update'), # Update a specific report
|
|
|
11
|
+ # path('<int:pk>/delete/', views.delete_report, name='delete'), # Delete a specific report
|
|
|
12
|
+ # path('generate/', views.generate_excel_report, name='generate_excel'), # Custom route for Excel generation
|
|
|
13
|
+]
|
|
|
@@ -0,0 +1,20 @@
|
|
|
1
|
+from django.conf import settings
|
|
|
2
|
+import pyodbc
|
|
|
3
|
+
|
|
|
4
|
+def create_db_connection(env="dev"):
|
|
|
5
|
+ try:
|
|
|
6
|
+ connection_details = settings.DATABASE_CONNECTIONS.get(env)
|
|
|
7
|
+ if not connection_details:
|
|
|
8
|
+ raise ValueError(f"Environment '{env}' not found in DATABASE_CONNECTIONS")
|
|
|
9
|
+
|
|
|
10
|
+ connection_string = (
|
|
|
11
|
+ f"DRIVER={connection_details['DRIVER']};"
|
|
|
12
|
+ f"SERVER={connection_details['SERVER']};"
|
|
|
13
|
+ f"DATABASE={connection_details['DATABASE']};"
|
|
|
14
|
+ f"UID={connection_details['UID']};"
|
|
|
15
|
+ f"PWD={connection_details['PWD']}"
|
|
|
16
|
+ )
|
|
|
17
|
+ return pyodbc.connect(connection_string)
|
|
|
18
|
+ except Exception as e:
|
|
|
19
|
+ raise RuntimeError(f"Failed to establish database connection: {e}")
|
|
|
20
|
+
|
|
|
@@ -0,0 +1,13 @@
|
|
|
1
|
+from django.shortcuts import render
|
|
|
2
|
+
|
|
|
3
|
+
|
|
|
4
|
+def index(request):
|
|
|
5
|
+ """
|
|
|
6
|
+ Renders the index page for the report app.
|
|
|
7
|
+ """
|
|
|
8
|
+ # Optional: Pass any context variables to the template
|
|
|
9
|
+ context = {
|
|
|
10
|
+ 'page_title': 'Report Dashboard',
|
|
|
11
|
+ 'reports': [], # Replace with actual data, e.g., a queryset from the database
|
|
|
12
|
+ }
|
|
|
13
|
+ return render(request, 'report/index.html', context)
|
|
|
@@ -0,0 +1,10 @@
|
|
|
1
|
+function COICtrl() {
|
|
|
2
|
+ return {
|
|
|
3
|
+ init() {
|
|
|
4
|
+ alert("HelloWorld")
|
|
|
5
|
+ },
|
|
|
6
|
+ clickMe() {
|
|
|
7
|
+ console.log(this.$event);
|
|
|
8
|
+ }
|
|
|
9
|
+ }
|
|
|
10
|
+}
|
|
|
@@ -0,0 +1,74 @@
|
|
|
1
|
+{% load static tailwind_tags %}
|
|
|
2
|
+{% load django_browser_reload %}
|
|
|
3
|
+
|
|
|
4
|
+
|
|
|
5
|
+<!DOCTYPE html>
|
|
|
6
|
+<html lang="en">
|
|
|
7
|
+<head>
|
|
|
8
|
+ <meta charset="UTF-8">
|
|
|
9
|
+ <meta name="viewport" content="width=device-width, initial-scale=1.0">
|
|
|
10
|
+ <title>{% block title %}My Django App{% endblock %}</title>
|
|
|
11
|
+
|
|
|
12
|
+ <!-- TailwindCSS -->
|
|
|
13
|
+ <!--
|
|
|
14
|
+ <script src="https://cdn.tailwindcss.com"></script> -->
|
|
|
15
|
+ {% tailwind_css %}
|
|
|
16
|
+ <script type="text/javascript" defer src="{% static "alpinejs/dist/cdn.min.js" %}"></script>
|
|
|
17
|
+ <link href="https://cdn.jsdelivr.net/npm/flowbite@2.5.2/dist/flowbite.min.css" rel="stylesheet" />
|
|
|
18
|
+ <script type="text/javascript" src="{% static "js/main.js" %}"></script>
|
|
|
19
|
+</head>
|
|
|
20
|
+<body class="bg-gray-100 text-gray-800">
|
|
|
21
|
+ <!-- Navbar -->
|
|
|
22
|
+ <nav class="fixed top-0 z-50 w-full bg-white border-b border-gray-200 dark:bg-gray-800 dark:border-gray-700">
|
|
|
23
|
+ <div class="px-3 py-3 lg:px-5 lg:pl-3">
|
|
|
24
|
+ <div class="flex items-center justify-between">
|
|
|
25
|
+ <div class="flex items-center justify-start">
|
|
|
26
|
+ <button data-drawer-target="logo-sidebar" data-drawer-toggle="logo-sidebar" aria-controls="logo-sidebar" type="button" class="inline-flex items-center p-2 text-sm text-gray-500 rounded-lg sm:hidden hover:bg-gray-100 focus:outline-none focus:ring-2 focus:ring-gray-200 dark:text-gray-400 dark:hover:bg-gray-700 dark:focus:ring-gray-600">
|
|
|
27
|
+ <span class="sr-only">Open sidebar</span>
|
|
|
28
|
+ <svg class="w-6 h-6" aria-hidden="true" fill="currentColor" viewBox="0 0 20 20" xmlns="http://www.w3.org/2000/svg">
|
|
|
29
|
+ <path clip-rule="evenodd" fill-rule="evenodd" d="M2 4.75A.75.75 0 012.75 4h14.5a.75.75 0 010 1.5H2.75A.75.75 0 012 4.75zm0 10.5a.75.75 0 01.75-.75h7.5a.75.75 0 010 1.5h-7.5a.75.75 0 01-.75-.75zM2 10a.75.75 0 01.75-.75h14.5a.75.75 0 010 1.5H2.75A.75.75 0 012 10z"></path>
|
|
|
30
|
+ </svg>
|
|
|
31
|
+ </button>
|
|
|
32
|
+ <a href="/" class="flex items-center ml-2">
|
|
|
33
|
+ <img src="https://flowbite.com/docs/images/logo.svg" class="h-8 mr-3" alt="Logo" />
|
|
|
34
|
+ <span class="self-center text-xl font-semibold dark:text-white">My App</span>
|
|
|
35
|
+ </a>
|
|
|
36
|
+ </div>
|
|
|
37
|
+ <div class="flex items-center">
|
|
|
38
|
+ <div class="ml-3">
|
|
|
39
|
+ <button type="button" class="flex text-sm bg-gray-800 rounded-full focus:ring-4 focus:ring-gray-300 dark:focus:ring-gray-600">
|
|
|
40
|
+ <span class="sr-only">Open user menu</span>
|
|
|
41
|
+ <img class="w-8 h-8 rounded-full" src="https://flowbite.com/docs/images/people/profile-picture-5.jpg" alt="User">
|
|
|
42
|
+ </button>
|
|
|
43
|
+ </div>
|
|
|
44
|
+ </div>
|
|
|
45
|
+ </div>
|
|
|
46
|
+ </div>
|
|
|
47
|
+ </nav>
|
|
|
48
|
+
|
|
|
49
|
+ <!-- Sidebar -->
|
|
|
50
|
+ <aside id="logo-sidebar" class="fixed top-0 left-0 z-40 w-64 h-screen pt-20 transition-transform -translate-x-full bg-white border-r border-gray-200 sm:translate-x-0 dark:bg-gray-800 dark:border-gray-700">
|
|
|
51
|
+ <div class="h-full px-3 pb-4 overflow-y-auto">
|
|
|
52
|
+ <ul class="space-y-2">
|
|
|
53
|
+ <li><a href="/dashboard/" class="flex items-center p-2 text-gray-900 rounded-lg hover:bg-gray-100 dark:hover:bg-gray-700 dark:text-white"><span class="ml-3">Dashboard</span></a></li>
|
|
|
54
|
+ <li><a href="/reports/" class="flex items-center p-2 text-gray-900 rounded-lg hover:bg-gray-100 dark:hover:bg-gray-700 dark:text-white"><span class="ml-3">Reports</span></a></li>
|
|
|
55
|
+ <li><a href="/settings/" class="flex items-center p-2 text-gray-900 rounded-lg hover:bg-gray-100 dark:hover:bg-gray-700 dark:text-white"><span class="ml-3">Settings</span></a></li>
|
|
|
56
|
+ </ul>
|
|
|
57
|
+ </div>
|
|
|
58
|
+ </aside>
|
|
|
59
|
+
|
|
|
60
|
+ <!-- Main Content -->
|
|
|
61
|
+ <div class="p-4 sm:ml-64">
|
|
|
62
|
+ <div class="mt-14">{% block content %}{% endblock %}</div>
|
|
|
63
|
+ </div>
|
|
|
64
|
+
|
|
|
65
|
+ <!-- Footer -->
|
|
|
66
|
+ <footer class="bg-gray-200 py-4 text-center">
|
|
|
67
|
+ <p class="text-sm text-gray-600">© 2024 My Django App. All rights reserved.</p>
|
|
|
68
|
+ </footer>
|
|
|
69
|
+ <script type="text/javascript" src="{% static "underscore/underscore-min.js" %}"></script>
|
|
|
70
|
+ <script src="https://cdn.jsdelivr.net/npm/flowbite@2.5.2/dist/flowbite.min.js"></script>
|
|
|
71
|
+
|
|
|
72
|
+ {% django_browser_reload_script %}
|
|
|
73
|
+</body>
|
|
|
74
|
+</html>
|
|
|
@@ -0,0 +1,5 @@
|
|
|
1
|
+from django.apps import AppConfig
|
|
|
2
|
+
|
|
|
3
|
+
|
|
|
4
|
+class ThemeConfig(AppConfig):
|
|
|
5
|
+ name = 'theme'
|
|
|
@@ -0,0 +1 @@
|
|
|
1
|
+node_modules
|
|
|
@@ -0,0 +1,28 @@
|
|
|
1
|
+{
|
|
|
2
|
+ "name": "theme",
|
|
|
3
|
+ "version": "3.8.0",
|
|
|
4
|
+ "description": "",
|
|
|
5
|
+ "scripts": {
|
|
|
6
|
+ "start": "npm run dev",
|
|
|
7
|
+ "build": "npm run build:clean && npm run build:tailwind",
|
|
|
8
|
+ "build:clean": "rimraf ../static/css/dist",
|
|
|
9
|
+ "build:tailwind": "cross-env NODE_ENV=production tailwindcss --postcss -i ./src/styles.css -o ../static/css/dist/styles.css --minify",
|
|
|
10
|
+ "dev": "cross-env NODE_ENV=development tailwindcss --postcss -i ./src/styles.css -o ../static/css/dist/styles.css -w",
|
|
|
11
|
+ "tailwindcss": "node ./node_modules/tailwindcss/lib/cli.js"
|
|
|
12
|
+ },
|
|
|
13
|
+ "keywords": [],
|
|
|
14
|
+ "author": "",
|
|
|
15
|
+ "license": "MIT",
|
|
|
16
|
+ "devDependencies": {
|
|
|
17
|
+ "@tailwindcss/aspect-ratio": "^0.4.2",
|
|
|
18
|
+ "@tailwindcss/forms": "^0.5.7",
|
|
|
19
|
+ "@tailwindcss/typography": "^0.5.10",
|
|
|
20
|
+ "cross-env": "^7.0.3",
|
|
|
21
|
+ "postcss": "^8.4.32",
|
|
|
22
|
+ "postcss-import": "^15.1.0",
|
|
|
23
|
+ "postcss-nested": "^6.0.1",
|
|
|
24
|
+ "postcss-simple-vars": "^7.0.1",
|
|
|
25
|
+ "rimraf": "^5.0.5",
|
|
|
26
|
+ "tailwindcss": "^3.4.0"
|
|
|
27
|
+ }
|
|
|
28
|
+}
|
|
|
@@ -0,0 +1,7 @@
|
|
|
1
|
+module.exports = {
|
|
|
2
|
+ plugins: {
|
|
|
3
|
+ "postcss-import": {},
|
|
|
4
|
+ "postcss-simple-vars": {},
|
|
|
5
|
+ "postcss-nested": {}
|
|
|
6
|
+ },
|
|
|
7
|
+}
|
|
|
@@ -0,0 +1,3 @@
|
|
|
1
|
+@tailwind base;
|
|
|
2
|
+@tailwind components;
|
|
|
3
|
+@tailwind utilities;
|
|
|
@@ -0,0 +1,57 @@
|
|
|
1
|
+/**
|
|
|
2
|
+ * This is a minimal config.
|
|
|
3
|
+ *
|
|
|
4
|
+ * If you need the full config, get it from here:
|
|
|
5
|
+ * https://unpkg.com/browse/tailwindcss@latest/stubs/defaultConfig.stub.js
|
|
|
6
|
+ */
|
|
|
7
|
+
|
|
|
8
|
+module.exports = {
|
|
|
9
|
+ content: [
|
|
|
10
|
+ /**
|
|
|
11
|
+ * HTML. Paths to Django template files that will contain Tailwind CSS classes.
|
|
|
12
|
+ */
|
|
|
13
|
+
|
|
|
14
|
+ /* Templates within theme app (<tailwind_app_name>/templates), e.g. base.html. */
|
|
|
15
|
+ '../templates/**/*.html',
|
|
|
16
|
+
|
|
|
17
|
+ /*
|
|
|
18
|
+ * Main templates directory of the project (BASE_DIR/templates).
|
|
|
19
|
+ * Adjust the following line to match your project structure.
|
|
|
20
|
+ */
|
|
|
21
|
+ '../../templates/**/*.html',
|
|
|
22
|
+
|
|
|
23
|
+ /*
|
|
|
24
|
+ * Templates in other django apps (BASE_DIR/<any_app_name>/templates).
|
|
|
25
|
+ * Adjust the following line to match your project structure.
|
|
|
26
|
+ */
|
|
|
27
|
+ '../../**/templates/**/*.html',
|
|
|
28
|
+
|
|
|
29
|
+ /**
|
|
|
30
|
+ * JS: If you use Tailwind CSS in JavaScript, uncomment the following lines and make sure
|
|
|
31
|
+ * patterns match your project structure.
|
|
|
32
|
+ */
|
|
|
33
|
+ /* JS 1: Ignore any JavaScript in node_modules folder. */
|
|
|
34
|
+ // '!../../**/node_modules',
|
|
|
35
|
+ /* JS 2: Process all JavaScript files in the project. */
|
|
|
36
|
+ // '../../**/*.js',
|
|
|
37
|
+
|
|
|
38
|
+ /**
|
|
|
39
|
+ * Python: If you use Tailwind CSS classes in Python, uncomment the following line
|
|
|
40
|
+ * and make sure the pattern below matches your project structure.
|
|
|
41
|
+ */
|
|
|
42
|
+ // '../../**/*.py'
|
|
|
43
|
+ ],
|
|
|
44
|
+ theme: {
|
|
|
45
|
+ extend: {},
|
|
|
46
|
+ },
|
|
|
47
|
+ plugins: [
|
|
|
48
|
+ /**
|
|
|
49
|
+ * '@tailwindcss/forms' is the forms plugin that provides a minimal styling
|
|
|
50
|
+ * for forms. If you don't like it or have own styling for forms,
|
|
|
51
|
+ * comment the line below to disable '@tailwindcss/forms'.
|
|
|
52
|
+ */
|
|
|
53
|
+ require('@tailwindcss/forms'),
|
|
|
54
|
+ require('@tailwindcss/typography'),
|
|
|
55
|
+ require('@tailwindcss/aspect-ratio'),
|
|
|
56
|
+ ],
|
|
|
57
|
+}
|
|
|
@@ -0,0 +1,19 @@
|
|
|
1
|
+{% load static tailwind_tags %}
|
|
|
2
|
+<!DOCTYPE html>
|
|
|
3
|
+<html lang="en">
|
|
|
4
|
+ <head>
|
|
|
5
|
+ <title>Django Tailwind</title>
|
|
|
6
|
+ <meta charset="UTF-8">
|
|
|
7
|
+ <meta name="viewport" content="width=device-width, initial-scale=1.0">
|
|
|
8
|
+ <meta http-equiv="X-UA-Compatible" content="ie=edge">
|
|
|
9
|
+ {% tailwind_css %}
|
|
|
10
|
+ </head>
|
|
|
11
|
+
|
|
|
12
|
+ <body class="bg-gray-50 font-serif leading-normal tracking-normal">
|
|
|
13
|
+ <div class="container mx-auto">
|
|
|
14
|
+ <section class="flex items-center justify-center h-screen">
|
|
|
15
|
+ <h1 class="text-5xl">Django + Tailwind = ❤️</h1>
|
|
|
16
|
+ </section>
|
|
|
17
|
+ </div>
|
|
|
18
|
+ </body>
|
|
|
19
|
+</html>
|
|
|
@@ -4,7 +4,7 @@ services:
|
|
4
|
4
|
db:
|
|
5
|
5
|
image: mcr.microsoft.com/mssql/server:2019-latest
|
|
6
|
6
|
user: root
|
|
7
|
|
- container_name: mssql_db
|
|
|
7
|
+ #container_name: mssql_db
|
|
8
|
8
|
environment:
|
|
9
|
9
|
SA_PASSWORD: "StrongPassw0rd!"
|
|
10
|
10
|
ACCEPT_EULA: "Y"
|
|
|
@@ -25,7 +25,7 @@ services:
|
|
25
|
25
|
|
|
26
|
26
|
web:
|
|
27
|
27
|
build: .
|
|
28
|
|
- container_name: django_web
|
|
|
28
|
+ #container_name: django_web
|
|
29
|
29
|
volumes:
|
|
30
|
30
|
- ./app:/app
|
|
31
|
31
|
ports:
|
|
|
@@ -37,3 +37,12 @@ django-tailwind # For TailwindCSS integration
|
|
37
|
37
|
|
|
38
|
38
|
# Cache optimization (optional)
|
|
39
|
39
|
django-cacheops # Caching ORM QuerySets
|
|
|
40
|
+
|
|
|
41
|
+pandas
|
|
|
42
|
+numpy
|
|
|
43
|
+scipy
|
|
|
44
|
+matplotlib
|
|
|
45
|
+xlrd
|
|
|
46
|
+seaborn
|
|
|
47
|
+django-browser-reload
|
|
|
48
|
+django-tailwind[reload]
|