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 dateStyle = NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM') # 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 = './datacpmaster2.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 createWS(name, values, codeno, filters): global wb #wb.create_sheet(name) wo = wb['Data Cp'] ws = wb.copy_worksheet(wo) ws.title = name cust = findCustomer(codeno.replace("-", "")) ws["B2"] = cust[0].customer ws["B3"] = codeno.replace("-", "") ws["B4"] = cust[0].sizestring ws["B5"] = cust[0].spec bcode = ord('B') ccode = 2 for idx, val in enumerate(values): #print(idx) c = chr(bcode + idx) #print(val.lot_no) if ( bcode + idx) > ord('Z'): r = bcode + idx - ord('Z') c = 'A' + chr(ord('A') + (r - 1)) #print("c = "+c) ws.cell(column= ccode, row=45, value = val.lot_no) ms = findInMasterView('*', "PRO2 = '{0}'".format(val.lot_no)) #pressInfo = findPressInfo(codeno.replace("-", ""),val.lot_no, filters) #print("master view") #print(ms) if ms: ws.cell(column = ccode, row=35, value = ms[0].MI24) #ws[c+'38'] = ms[0].STD_Balance ws.cell(column = ccode , row=37, value = ms[0].PRO6) #ws[c+'44'] = ms[0].PRO5 ws.cell(column = ccode, row = 43, value = ms[0].PRO10) ws.cell(column = ccode, row = 42, value = ms[0].PRO12) #if pressInfo: ws.cell(column = ccode , row=39, value = val.Press_UserName) ws.cell(column = ccode, row = 40, value = val.Press_McName) #print(type(pressInfo[0].Insp_Date)) #ws[c+'46'].number_format = "Short Date" ws.cell(column = ccode, row = 46 , value = val.Insp_Date.date()) #print(type(ws[c+'46'].value)) #ws[c+'46'].style = dateStyle #ws[c+'46'].value = datetime.datetime(2010, 7, 21) #print(ws[c+'46'].number_format) #ws[c+'46']. = datetime.datetime ws.cell(column = ccode , row = 44, value = val.FG_Qty) dws = updateLength(val.lot_no) for j,v in enumerate(dws): ws.cell(column = ccode , row = 48+v.row_no , value = v.weight) dws = updateUb(val.lot_no) for j,v in enumerate(dws): ws.cell(column = ccode , row = 171+v.row_no , value = v.weight) ccode += 1 c1 = LineChart() c1.legend.position = "t"; c1.title = "Graph of Result Cp Unbalance" c1.title.font = Font(underline=Font.UNDERLINE_SINGLE) c1.y_axis.title = "Unb Weight" c1.y_axis.number_format = "#,##0.00" c1.x_axis.title = "Lot No" c1.width = 30 c1.height = 10 #print(len(values)) lt = Reference(ws, min_col=2, max_col=len(values)+1, min_row=45, max_row=45) data = Reference(ws, min_col=1, max_col=len(values)+1, min_row=34, max_row=34) maxUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=29, max_row=29) minUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=30, max_row=30) avgUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=31, max_row=31) vsStd = Reference(ws, min_col=1, max_col=len(values)+1, min_row=36, max_row=36) c1.add_data(data, 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(maxUB, 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[4] 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, "A7") #newws.title = name #copy data cp ws #insert values in the sheet 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) if len(merged) > 0: return merged return merged def findPressInfo(code, lot, year): global cursor global env fyear = year.replace("created_at", "Insp_Date") q = "select * from tb_fg_pressinfo_lotlist where ProductCode = '{0}' and LotNo = '{1}' and {2}" \ .format(code, lot, fyear) cursor.execute(q) return cursor.fetchall() 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 createExcel(codeno, filters): global cursor global wb global dateStyle ws = wb["Data Cp"] codeString = "" if codeno.find('-') != -1: codeString = codeno else: codeString = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10] if filters is not None: q = """select * from lot_summary_wb as lt inner join tb_fg_pressinfo_lotlist as press on lt.lot_no = press.LotNo and replace(lt.code, '-', '') = press.ProductCode where code = '{0}' and {1} order by press.Insp_Date asc """.format(codeString, filters) else: q = """select * from lot_summary_wb as lt inner join tb_fg_pressinfo_lotlist as press on lt.lot_no = press.LotNo and replace(lt.code, '-', '') = press.ProductCodes where code = '{0}' order by press.Insp_Date asc """.format(codeString) #print(q) cursor.execute(q) rows = cursor.fetchall() #print(rows) y = {} for r in rows: t = "{0}-{1}".format(r.created_at.year, r.created_at.month) if t in y: y[t].append(r) else: y[t] = [] y[t].append(r) #print(y) for k, v in y.items(): createWS(k,v, codeno, filters) #print(ws._charts) cust = findCustomer(codeno.replace("-", "")) ws["B2"] = cust[0].customer ws["B3"] = codeno.replace("-", "") ws["B4"] = cust[0].sizestring ws["B5"] = cust[0].spec bcode = ord('B') ccode = 2 for idx, val in enumerate(rows): #print(idx) c = chr(bcode + idx) #print(val.lot_no) if ( bcode + idx) > ord('Z'): r = bcode + idx - ord('Z') c = 'A' + chr(ord('A') + (r - 1)) #print("c = "+c) ws.cell(column= ccode, row=45, value = val.lot_no) ms = findInMasterView('*', "PRO2 = '{0}'".format(val.lot_no)) #pressInfo = findPressInfo(codeno.replace("-", ""),val.lot_no, filters) #print("master view") #print("ms") #print(ms) if ms: ws.cell(column = ccode, row=35, value = ms[0].MI24) #ws[c+'38'] = ms[0].STD_Balance ws.cell(column = ccode , row=37, value = ms[0].PRO6) #ws[c+'44'] = ms[0].PRO5 ws.cell(column = ccode, row = 43, value = ms[0].PRO10) ws.cell(column = ccode, row = 42, value = ms[0].PRO12) #if pressInfo: ws.cell(column = ccode , row=39, value = val.Press_UserName) ws.cell(column = ccode, row = 40, value = val.Press_McName) #print(type(pressInfo[0].Insp_Date)) #ws[c+'46'].number_format = "Short Date" ws.cell(column = ccode, row = 46 , value = val.Insp_Date.date()) #print(type(ws[c+'46'].value)) #ws[c+'46'].style = dateStyle #ws[c+'46'].value = datetime.datetime(2010, 7, 21) #print(ws[c+'46'].number_format) #ws[c+'46']. = datetime.datetime ws.cell(column = ccode , row = 44, value = val.FG_Qty) dws = updateLength(val.lot_no) for j,v in enumerate(dws): ws.cell(column = ccode , row = 48+v.row_no , value = v.weight) dws = updateUb(val.lot_no) for j,v in enumerate(dws): ws.cell(column = ccode , row = 171+v.row_no , value = v.weight) ccode += 1 ut = int(time.time()) filename = "cp{0}.xlsx".format(codeno.replace("-", "")) outfile = './public/excel/'+filename wb.save(filename = outfile) print(filename) if __name__ == "__main__": env = sys.argv[1] code = 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=db;DATABASE=OB2011DB;UID=admin;PWD=1234') cursor = cnxn.cursor() #print("code is "+code) createExcel(code, df)