from openpyxl import load_workbook import pyodbc import time import sys from pprint import pprint import datetime from openpyxl.styles import NamedStyle, Font from copy import deepcopy from openpyxl.chart import LineChart, Reference, Series from openpyxl.chart.text import RichText from openpyxl.drawing.text import RichTextProperties,Paragraph,ParagraphProperties, CharacterProperties # Specifying the ODBC driver, server name, database, etc. directly #cnxn = None #cursor = None '''cursor.execute('select * from data_wb') rows = cursor.fetchall() for row in rows: print(row)''' wb = load_workbook(filename = './dctemplate2.xlsx') #wb.save(filename = './createfrompy.xlsx'); def findCustomer(code): global cursor global env tempResult = [] views = [] if env == "prod": views = ["bel_master_view", "e_master_view", "mg_master_view", "v_master_view"] else: views = ["bel_master_view_dev", "e_master_view_dev", "mg_master_view_dev", "v_master_view_dev"] ts = [] 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" where = "PRO1 = '{0}'".format(code) for v in views: sqlQuery = "select {0} from {1} where {2}".format(select, v, where) ts.append(sqlQuery) merged = [] for t in ts: merged += cursor.execute(t) return merged def findInMasterView(select, where): global cursor global env tempResult = [] views = [] if env == "prod": views = ["bel_master_view", "e_master_view", "mg_master_view", "v_master_view"] else: views = ["bel_master_view_dev", "e_master_view_dev", "mg_master_view_dev", "v_master_view_dev"] ts = [] for v in views: sqlQuery = "select {0} from {1} where {2}".format(select, v, where) ts.append(sqlQuery); qj = ';'.join(ts) #print(qj) merged = [] for t in ts: merged += cursor.execute(t) return merged def updateLength(lotno): global cursor global wb q = "select * from data_wb where lot_no = '{0}' and judgement='OK' order by row_no asc".format(lotno) #print(q); return cursor.execute(q) def updateUb(lotno): global cursor global wb q = "select * from data_wb where lot_no = '{0}' and judgement='NG' order by row_no asc".format(lotno) #print(q); return cursor.execute(q) def getMachineName(machineId): global cursor q = "select * from machines_wb where id = {0}".format(machineId) #print(q) cursor.execute(q) rs = cursor.fetchall() return rs[0].name def getMachineDetail(machineId): global cursor q = "select * from machines_wb where id = {0}".format(machineId) #print(q) cursor.execute(q) rs = cursor.fetchall() return rs[0] def createDayChart(chartTitle): global wb ws = wb['Chart'] wsd = wb['Daily Check'] c1 = LineChart() c1.legend.position = "t"; c1.title = chartTitle c1.title.font = Font(underline=Font.UNDERLINE_SINGLE) c1.y_axis.title = "Std. Weight(g)" c1.y_axis.number_format = "#,##0.00" c1.x_axis.title = "Date" c1.width = 30 c1.height = 10 #print(len(values)) MINDATACOL = 2 MAXDATACOL = 2 + 31 lt = Reference(wsd, min_col=3, max_col=33, min_row=5, max_row=5) std = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=6, max_row=6) data = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=10, max_row=10) maxUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=15, max_row=15) minUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=21, max_row=21) avgUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=26, max_row=26) vsStd = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=32, max_row=32) vsStd2 = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=37, max_row=37) c1.add_data(data, from_rows=True, titles_from_data=True) c1.add_data(maxUB, from_rows=True, titles_from_data=True) c1.add_data(minUB, from_rows=True, titles_from_data=True) c1.add_data(avgUB, from_rows=True, titles_from_data=True) c1.add_data(vsStd, from_rows=True, titles_from_data=True) c1.add_data(vsStd2, from_rows=True, titles_from_data=True) c1.add_data(std, from_rows=True, titles_from_data=True) s1 = c1.series[0] s1.marker.symbol = "triangle" s1.graphicalProperties.line.noFill = True s1 = c1.series[1] s1.marker.symbol = "circle" s1.graphicalProperties.line.noFill = True s1 = c1.series[2] s1.marker.symbol = "square" s1.graphicalProperties.line.noFill = True s1 = c1.series[3] s1.marker.symbol = "auto" s1.graphicalProperties.line.noFill = True s1 = c1.series[4] s1.marker.symbol = "diamond" s1.graphicalProperties.line.noFill = True s1 = c1.series[5] s1.marker.symbol = "auto" s1.graphicalProperties.line.noFill = True s1 = c1.series[6] #s1.marker.symbol = "diamond" #s1.graphicalProperties.line.noFill = True c1.set_categories(lt) '''c1.x_axis.txPr = RichText(bodyPr=RichTextProperties(anchor="ctr",anchorCtr="1",rot="-2700000", spcFirstLastPara="1",vertOverflow="ellipsis",wrap="square"), p=[Paragraph(pPr=ParagraphProperties(defRPr=CharacterProperties()), endParaRPr=CharacterProperties())])''' ws.add_chart(c1, "b3") #pass def createNightChart(chartTitle): global wb ws = wb['Chart'] wsd = wb['Daily Check'] c1 = LineChart() c1.legend.position = "t"; c1.title = chartTitle c1.title.font = Font(underline=Font.UNDERLINE_SINGLE) c1.y_axis.title = "Std. Weight(g)" c1.y_axis.number_format = "#,##0.00" c1.x_axis.title = "Date" c1.width = 30 c1.height = 10 #print(len(values)) MINDATACOL = 2 MAXDATACOL = 2 + 31 lt = Reference(wsd, min_col=3, max_col=33, min_row=5, max_row=5) std = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=6, max_row=6) #begin data data = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=51, max_row=51) maxUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=56, max_row=56) minUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=62, max_row=62) avgUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=67, max_row=67) vsStd = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=73, max_row=73) vsStd2 = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=78, max_row=78) c1.add_data(data, from_rows=True, titles_from_data=True) c1.add_data(maxUB, from_rows=True, titles_from_data=True) c1.add_data(minUB, from_rows=True, titles_from_data=True) c1.add_data(avgUB, from_rows=True, titles_from_data=True) c1.add_data(vsStd, from_rows=True, titles_from_data=True) c1.add_data(vsStd2, from_rows=True, titles_from_data=True) c1.add_data(std, from_rows=True, titles_from_data=True) s1 = c1.series[0] s1.marker.symbol = "triangle" s1.graphicalProperties.line.noFill = True s1 = c1.series[1] s1.marker.symbol = "circle" s1.graphicalProperties.line.noFill = True s1 = c1.series[2] s1.marker.symbol = "square" s1.graphicalProperties.line.noFill = True s1 = c1.series[3] s1.marker.symbol = "auto" s1.graphicalProperties.line.noFill = True s1 = c1.series[4] s1.marker.symbol = "diamond" s1.graphicalProperties.line.noFill = True s1 = c1.series[5] s1.marker.symbol = "auto" s1.graphicalProperties.line.noFill = True s1 = c1.series[6] #s1.marker.symbol = "diamond" #s1.graphicalProperties.line.noFill = True c1.set_categories(lt) '''c1.x_axis.txPr = RichText(bodyPr=RichTextProperties(anchor="ctr",anchorCtr="1",rot="-2700000", spcFirstLastPara="1",vertOverflow="ellipsis",wrap="square"), p=[Paragraph(pPr=ParagraphProperties(defRPr=CharacterProperties()), endParaRPr=CharacterProperties())])''' ws.add_chart(c1, "b22") def createExcel(codeno, filters): global cursor global wb ws = wb["Daily Check"] machineName = getMachineName(codeno) machineDetail = getMachineDetail(codeno) ws["C2"].value = machineName ws["C3"].value = filters.replace("created_at between", "").replace("and", "to").replace("'", "") ws["C43"].value = machineName ws["C44"].value = filters.replace("created_at between", "").replace("and", "to").replace("'", "") ws["C4"].value = machineDetail.machineStd; if filters is not None: q = "select * from daily_checks_wb where machine_id = '{0}' and {1}".format(codeno, filters) else: q = "select * from daily_checks_wb where machine_id = '{0}'".format(codeno) #print(q) cursor.execute(q) rows = cursor.fetchall() #print(rows) a0740 = []; a1230 = [] a1700 = [] a0030 = [] a1940 = [] a0500 = [] for i in range(31): a0740.append([]) a1230.append([]) a1700.append([]) a0030.append([]) a1940.append([]) a0500.append([]) for r in rows: i = r.created_at.day if r.shift == "07.40": a0740[i-1].append(r) if r.shift == "12.30": a1230[i-1].append(r) if r.shift == "17.00": #a1700.append(r) a1700[i-1].append(r) if r.shift == "00.30": a0030[i-1].append(r) if r.shift == "19.40": a1940[i-1].append(r) if r.shift == "05.00": a0500[i-1].append(r) #print(a0740) for idx, val in enumerate(a0740): j = 7 k = 12 users = [] #print(val) ok = 0 ng = 0 for v in val: #print(j , idx + ) #print(v.judgment) if v.judgement == "OK": ok += 1 if ok > 3: continue ws.cell(row = j , column = idx + 3 , value = v.weight) j += 1 if v.judgement == "NG": ng += 1 if ng > 3: continue ws.cell(row = k , column = idx + 3 , value = v.weight) k += 1 #print(v.empid) if v.empid not in users: #print("insert in users") users.append(v.empid) #print(val) if len(val) > 0: q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users)) #print(q) cursor.execute(q) us = cursor.fetchall() names = [x.fname + " " + x.lname for x in us] ws.cell(row = 17, column = idx + 3, value = ",".join(names)) for idx, val in enumerate(a1230): j = 18 k = 23 users = [] ok = ng = 0 for v in val: #print(j , idx) if v.judgement == "OK": ok += 1 if ok > 3: continue ws.cell(row = j , column = idx + 3 , value = v.weight) j += 1 if v.judgement == "NG": ng += 1 if ng > 3: continue ws.cell(row = k , column = idx + 3 , value = v.weight) k += 1 if v.empid not in users: users.append(v.empid) if len(val) > 0: q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users)) #print(q) cursor.execute(q) us = cursor.fetchall() names = [x.fname + " " + x.lname for x in us] ws.cell(row = 28, column = idx + 3, value = ",".join(names)) for idx, val in enumerate(a1700): j = 29 k = 34 users = [] ok = ng = 0 for v in val: #print(j , idx) if v.judgement == "OK": ok += 1 if ok > 3: continue ws.cell(row = j , column = idx + 3 , value = v.weight) j += 1 if v.judgement == "NG": ng += 1 if ng > 3: continue ws.cell(row = k , column = idx + 3 , value = v.weight) k += 1 if v.empid not in users: users.append(v.empid) if len(val) > 0: q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users)) #print(q) cursor.execute(q) us = cursor.fetchall() names = [x.fname + " " + x.lname for x in us] ws.cell(row = 39, column = idx + 3, value = ",".join(names)) for idx, val in enumerate(a1940): j = 47 k = 53 users = [] ok = ng = 0 for v in val: #print(j , idx) if v.judgement == "OK": ok += 1 if ok > 3: continue ws.cell(row = j , column = idx + 3 , value = v.weight) j += 1 if v.judgement == "NG": ng += 1 if ng > 3: continue ws.cell(row = k , column = idx + 3 , value = v.weight) k += 1 if v.empid not in users: users.append(v.empid) if len(val) > 0: q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users)) #print(q) cursor.execute(q) us = cursor.fetchall() names = [x.fname + " " + x.lname for x in us] ws.cell(row = 58, column = idx + 3, value = ",".join(names)) for idx, val in enumerate(a0030): j = 59 k = 64 users = [] ok = ng = 0 for v in val: #print(j , idx) if v.judgement == "OK": ok += 1 if ok > 3: continue ws.cell(row = j , column = idx + 3 , value = v.weight) j += 1 if v.judgement == "NG": ng += 1 if ng > 3: continue ws.cell(row = k , column = idx + 3 , value = v.weight) k += 1 if v.empid not in users: users.append(v.empid) if len(val) > 0: q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users)) #print(q) cursor.execute(q) us = cursor.fetchall() names = [x.fname + " " + x.lname for x in us] ws.cell(row = 69, column = idx + 3, value = ",".join(names)) for idx, val in enumerate(a0500): j = 70 k = 75 users = [] ok = ng = 0 for v in val: #print(j , idx) if v.judgement == "OK": ok += 1 if ok > 3: continue ws.cell(row = j , column = idx + 3 , value = v.weight) j += 1 if v.judgement == "NG": ng += 1 if ng > 3: continue ws.cell(row = k , column = idx + 3 , value = v.weight) k += 1 if v.empid not in users: users.append(v.empid) if len(val) > 0: q = "select * from users where empid in ({0})".format(",".join("'{0}'".format(w) for w in users)) #print(q) cursor.execute(q) us = cursor.fetchall() names = [x.fname + " " + x.lname for x in us] ws.cell(row = 80, column = idx + 3, value = ",".join(names)) createDayChart("Daily Check of Day Shift ({0})".format(machineName)) createNightChart("Daily Check of Night Shift ({0})".format(machineName)) ut = int(time.time()) filename = "dc{0}.xlsx".format(str(ut)) outfile = './public/excel/'+filename wb.save(filename = outfile) print(filename) if __name__ == "__main__": env = sys.argv[1] machine = sys.argv[2] #print(len(sys.argv)) df = None if len(sys.argv) == 4: filter = sys.argv[3] #import ast #df = ast.literal_eval(filter) #print(df['date']) df = filter global cnxn global cursor if env == "prod": cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.0.253;DATABASE=OB2011DB;UID=user1;PWD=1234') cursor = cnxn.cursor() else: cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=OB2011DB;UID=admin;PWD=1234') cursor = cnxn.cursor() #print("code is "+code) createExcel(machine, df)