from openpyxl import load_workbook import pyodbc import time import sys from pprint import pprint import datetime from openpyxl.styles import NamedStyle, Font, colors, Border, Side import openpyxl.drawing.text as drawingText 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 import openpyxl.utils as utils from openpyxl.chart.text import RichText from .utils import create_db_connection dateStyle = NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM') codeMap = { "001": "D", "002": "T", "003": "H", "02": "P", "03": "E", "39": "E", "04": "W", "05": "JK", "06": "F", "51": "F", "07": "G", "B2": "G", "08": "U", "09": "M", "10": "A", "11": "N", "12": "J", "13": "X", "14": "Flatness", "15": "K", "16": "Thickness", "17": "Q", "18": "S", "72": "X" } # 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 = './measurement-temp.xlsx') wb = load_workbook(filename = './hardness-temp.xlsx') #print(wb["Cert"]._images) #wb.save(filename = './createfrompy.xlsx'); def getConditionRef(sid): global cursor try: query = f"select * from [TABLE_TI-T4] t4 where t4.TIT4 = {sid}" return cursor.execute(query) except: return [] def findCustomer(code, lotno): 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, MI13 as sampling, MI15, MI14, MI16, MI17, MI18, MI19, MI20, MI21, MI22, MI23, PRO5, PRO27,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, isnull(PRO13,'') + ' ' + isnull(PRO14,'') + ' ' + isnull(PRO15,'') + ' ' + isnull(PRO16, '') + ' ' + isnull(PRO17,'') + ' ' + isnull(PRO18,'') as spec" where = "PRO1 = '{0}' and PRO2 = '{1}'".format(code, lotno) for v in views: sqlQuery = "select {0} from {1} where {2}".format(select, v, where) ts.append(sqlQuery) #print(";".join(ts)) 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 Measurement'] 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 findProductDimension(codeno): stdSizes = """ select * from AllProduct_Dimension_ForInsProcess where ProductCode='{0}'""".format(codeno.replace("-", "")) #print(stdSizes) return stdSizes def findProductPress(codeno): stdSizes = """ select * from AllProduct_PressPosition_PressWeight where ProductCode='{0}'""".format(codeno.replace("-", "")) #print(stdSizes) return stdSizes def generateSQLDataMS(codeno, filters): if codeno.find('-') != -1: codeString = codeno else: codeString = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10] if filters is not None: qMsData = """ select * from data_ms where code='{0}' and (size1 > 0 or size2 > 0) and {1} order by lot_no asc, row_no asc """.format(codeString, filters) else: qMsData = """ select * from data_ms where code='{0}' and (size1 > 0 or size2 > 0 ) order by lot_no asc, row_no asc """.format(codeString) return qMsData 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 actualChart(ws, title, data, position_data, body_data, cat_pos, position): ''' for row in rows: ws.append(row) ''' if isinstance(position_data, str): rs = ws[position_data] else: rs = tuple(ws.iter_rows(**position_data)) for i in range(len(rs)): for j in range(len(rs[i])): print(rs[i][j]) rs[i][j].value = data[i][j] #ws["A1"].value = rows #print(cells) print(rs) #return c1 = LineChart() c1.width = 10 #c1.height = 10 c1.title = title c1.style = 13 c1.y_axis.title = 'Size' c1.x_axis.title = 'Lot' #print(ws.title+"?"+position_data) if isinstance(body_data, str): rdata = Reference(range_string=ws.title+"!"+body_data) else: rdata = Reference(ws, **body_data) c1.add_data(rdata, titles_from_data=True) if isinstance(cat_pos, str): lots = Reference(range_string=ws.title+"!"+cat_pos) else: lots = Reference(ws, **cat_pos) c1.set_categories(lots) # Style the lines s1 = c1.series[0] s1.marker.symbol = "triangle" s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline s1.graphicalProperties.line.noFill = True s2 = c1.series[1] s2.graphicalProperties.line.solidFill = "00AAAA" s2.graphicalProperties.line.dashStyle = "sysDot" s2.graphicalProperties.line.width = 100050 # width in EMUs s2 = c1.series[2] s2.smooth = True # Make the line smooth if isinstance(position,str): ws.add_chart(c1, position) else: ptemp = get_column_letter(position[1]) print("ptemp = ", ptemp) ws.add_chart(c1, ptemp + str(position[0])) def createXBarChart(codeno, filters): global cursor global wb global dateStyle global codeMap ws = wb.create_sheet("X Bar,R Chart") #find lot in a codeno sql = generateSQLDataMS(codeno, filters) sql2 = findProductPress(codeno) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() #print(list(row2)) temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} pressType1 = pressType2 = pressType3 = "" for k in row2: if k.PressType_1 != "" and k.PressType_1 is not None: sizes["size1"] = [] stdSizes["size1"] = k pressType1 = k.PressType_1 if k.PressType_2 != "" and k.PressType_2 is not None: sizes["size2"] = [] stdSizes["size2"] = k pressType2 = k.PressType_2 if k.Press_T != "" and k.Press_T is not None: sizes["tpresssize"] = [] stdSizes["tpresssize"] = k pressType3 = "Thickness Press" num = 0 for r in row: #print(r) if r.lot_no not in temp: temp[r.lot_no] = [] temp[r.lot_no].append(r) for k,v in sizes.items(): sizes[k].append(getattr(r, k, None)) #print("temp = ", temp) #print("temp keys = ", temp.keys()); for k,v in temp.items(): #print("k = ", k) num = len(v) #break #print("num = ", num) if num > 5: num = 5 #filter only dimensions in master #print("TEMP == \n") #print(sizes) avgSize = {} minSize = {} maxSize = {} data2 = [] data2.append(["Lot No"]) for k,v in temp.items(): data2[0].append(k) #print("data 2 = ", data2) #create maxtrix in form ''' Lot, 1, 2, 3, 4, 5, max, min xxx, v, v, v, v, v, v, v ''' bp = 1 for keySize, keyValue in sizes.items(): l = [] n0 = 0 beginRow = 2 #print("key size = %s\n" %(keySize) ) for k,v in temp.items(): a0 = [] a0.append(k) sv = len(v) if sv > 5: sv = 5 n0 = sv for i in range(sv): a0.append(getattr(v[i], keySize, 0)) avg = sum(a0[1:]) / len(a0[1:]) a0.append(avg) r = max(a0[1:]) - min(a0[1:]) a0.append(r) #print("std size ", stdSizes[keySize]) max_min = 0 if k == "size1": max_min = stdSizes[k].PressWeight_1 if k == "size2": max_min = stdSizes[k].PressWeight_2 if k == "tpresssize": maxMaster = stdSizes[k].PressT + stdSizes[k].Press_T_Tol minMaster = stdSizes[k].PressT - stdSizes[k].Press_T_Tol else: maxMaster = minMaster = float(max_min) a0.append(maxMaster) a0.append(minMaster) l.append(a0) #print("l = ", l) header = ["Lot No"] + [str(i+1) for i in range(n0)] + ["avg", "r", "max", "min"] #print("header =", header) l = [header] + l; #print("l = ", l) ''' tranpose above matrix into form: Lot,xxx, xxx, xxx, 1, v, v, v 2, v, v, v ... ''' m, n = len(l), len(l[0]) #print("mxn = ", m, n) ta = [ [0 for j in range(m)] for i in range(n)] #print("ta = ", ta) for i in range(len(l)): for j in range(len(l[i])): try: ta[j][i] = l[i][j] except Exception as e: return #print("TA ", ta) #print(len(ta), len(ta[0])) #print("===\n") #print(beginRow, beginRow + len(ta) - 1, bp, (bp-1) + len(ta[0])) wsRows = tuple(ws.iter_rows(min_row=beginRow, max_row=beginRow + len(ta) - 1, min_col=bp, max_col=(bp-1)+len(ta[0]))) for i in range(len(wsRows)): for j in range(len(wsRows[i])): #print(i,j) wsRows[i][j].value = ta[i][j] wsRows[i][j].number_format = '0.00' bd = Side(style='medium', color="000000") wsRows[i][j].border = Border(left=bd, top=bd, right=bd, bottom=bd) if keySize == "size1": barTitle = pressType1 if keySize == "size2": barTitle = pressType2 if keySize == "tpresssize": barTitle = pressType3 ws.cell(1, bp, value = "%s (bar)" % (barTitle)) #bp += len(ta[0]) + 2 #continue cpos = utils.get_column_letter(bp) + "15" font_test = drawingText.Font(typeface='Calibri') cp = CharacterProperties(latin=font_test, sz=1000) pp = ParagraphProperties(defRPr=cp) c1 = LineChart() cp1 = CharacterProperties(latin=font_test, sz=1400) pp1 = ParagraphProperties(defRPr=cp1) minValues = Reference(ws, min_row=beginRow+9, min_col=bp+1, max_row=beginRow+9, max_col=(bp-1)+len(ta[0]) ) minSerie = Series(minValues, title="Min") c1.append(minSerie) maxValues = Reference(ws, min_row=beginRow+8, min_col=bp+1, max_row=beginRow+8, max_col=(bp-1)+len(ta[0]) ) maxSerie = Series(maxValues, title="Max") c1.append(maxSerie) avgValues = Reference(ws, min_row=beginRow+6, min_col=bp+1, max_row=beginRow+6, max_col=(bp-1)+len(ta[0]) ) avgSerie = Series(avgValues, title="Avg") avgSerie.marker.symbol = "circle" avgSerie.marker.graphicalProperties.solidFill = "4f81bd" # Marker filling avgSerie.graphicalProperties.line.solidFill = "4f81bd" c1.append(avgSerie) lots = Reference(ws,min_row=beginRow, min_col=bp+1, max_row=beginRow, max_col=(bp-1)+len(ta[0])) c1.set_categories(lots) c1.width = len(ta[0])*3.5 if keySize == "size1": c1.title = "%s (X Bar) " % (pressType1) if keySize == "size2": c1.title = "%s (X Bar) " % (pressType2) if keySize == "tpresssize": c1.title = "%s (X Bar) " % (pressType3) #c1.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) if keySize == "tpresssize": c1.y_axis.title = "Value(mm.)" else: c1.y_axis.title = "Value(mg.)" c1.x_axis.title = "Lot No." c1.x_axis.title.tx.rich.p[0].pPr = pp# Works! if c1.y_axis.title != None: c1.y_axis.title.tx.rich.p[0].pPr = pp# Works! c1.title.tx.rich.p[0].pPr = pp1# Works! c1.legend.position = "b" ws.add_chart(c1, cpos) cpos = utils.get_column_letter(bp) + "30" c2 = LineChart() #c2.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) rValues = Reference(ws, min_row=beginRow+7, min_col=bp+1, max_row=beginRow+7, max_col=(bp-1)+len(ta[0]) ) rSerie = Series(rValues, title="R") c2.append(rSerie) lots = Reference(ws,min_row=beginRow, min_col=bp+1, max_row=beginRow, max_col=(bp-1)+len(ta[0])) c2.set_categories(lots) c2.width = len(ta[0])*3.5 if keySize == "size1": c2.title = "%s (R) " % (pressType1) if keySize == "size2": c2.title = "%s (R) " % (pressType2) if keySize == "tpresssize": c2.title = "%s (R) " % (pressType3) c2.y_axis.title = "Value(mm.)" c2.x_axis.title = "Lot No." c2.x_axis.title.tx.rich.p[0].pPr = pp# Works! c2.y_axis.title.tx.rich.p[0].pPr = pp# Works! c2.title.tx.rich.p[0].pPr = pp1# Works! c2.legend.position = "b" ws.add_chart(c2, cpos) #move column cursor bp += len(ta[0]) + 5 #create each size table and chart types # actual chart def createPressCP(codeno, filters): global cursor global wb global dateStyle global codeMap ws = wb.create_sheet("Press CP") #find lot in a codeno sql = generateSQLDataMS(codeno, filters) sql2 = findProductPress(codeno) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} #select distinct lot no bq = '''select ds.code,\ ds.lot_no, max(ds.size1) maxsize1, min(ds.size1) minsize1, max(ds.size2) maxsize2, min(ds.size2) minsize2 ,\ avg(ds.size1) avgSize1 , avg(ds.size2) avgSize2, stdev(ds.size1) sdsize1, stdev(ds.size2) sdsize2, \ empids = STUFF((SELECT ',' + str(emp_id) FROM data_ms b WHERE b.lot_no = ds.lot_no group by emp_id FOR XML PATH('')), 1, 2, ''), \ min(ds.created_at) minca,\ pp.Mold_D, pp.Center_D, pp.PressWeight_1, pp.PressWeight_2, pp.PressType_1, pp.PressType_2, pp.UWeight, pp.Press_Ton, pp.PO_Qty \ from data_ms ds \ inner join AllProduct_PressPosition_PressWeight pp on pp.ProductCode = Replace('{0}', '-', '') \ and pp.Lot_No = ds.lot_no \ where \ ds.code = '{0}' and ( (ds.size1 > 0) or (ds.size2 > 0) ) \ group by ds.code, ds.lot_no, pp.Press_Ton, pp.PO_Qty, pp.Mold_D, pp.Center_D, pp.PressWeight_1, pp.PressWeight_2, pp.PressType_1, pp.PressType_2, pp.UWeight'''.format(codeno) #print("bq ", bq) cursor.execute(bq) cp_rows = cursor.fetchall() #print(cp_rows) max1Ub = ["Max1 Weight."] min1Ub = ["Min1 Weight."] max2Ub = ["Max2 Weight."] min2Ub = ["Min2 Weight."] avg1Ub = ["Avg1 Weight."] avg2Ub = ["Avg2 Weight."] r1Ub = ["R1"] r2Ub = ["R2"] sd1 = ["sd1"] sd2 = ["sd2"] w1 = ["Weight(1) std"] w2 = ["Weight(2) std"] unitW = ["Unit Weight"] op = ["Operator"] press = ["Press M/C"] holes = ["Hole (mm)"] dias = ["Dia (mm)"] qtys = ["QTY"] lotnos = ["Lot no."] inspecs = ["Inspection Date"] for r in cp_rows: max1Ub.append(r.maxsize1) min1Ub.append(r.minsize1) max2Ub.append(r.maxsize2) min2Ub.append(r.minsize2) avg1Ub.append(r.avgSize1) avg2Ub.append(r.avgSize2) r1Ub.append(r.maxsize1 - r.minsize1) r2Ub.append(r.maxsize2 - r.minsize2) sd1.append(r.sdsize1) sd2.append(r.sdsize2) w1.append(r.PressWeight_1) w2.append(r.PressWeight_2) unitW.append(r.UWeight) op.append(r.empids) press.append(r.Press_Ton) holes.append(r.Mold_D) dias.append(r.Center_D) qtys.append(r.PO_Qty) lotnos.append(r.lot_no) #print("xdfdsf = "+r.minca.strftime("%m/%d/%Y %H:%M:%S")) inspecs.append(r.minca.strftime("%m/%d/%Y %H:%M:%S")) dataArray = [ max1Ub, #0 min1Ub, #1 max2Ub, #2 min2Ub, #3 avg1Ub, #4 avg2Ub,#5 r1Ub, #6 r2Ub, sd1, sd2,#9 w1, w2, unitW, #12 op, press, holes, dias, qtys, lotnos, inspecs ] #print(dataArray) startRow = 20 startCol = 2 for i, row in enumerate(dataArray): for j, o in enumerate(row): cell = ws.cell(startRow + i, startCol +j) cell.value = o applyStyleName(cell, "allborder") lc = LineChart() lc.title = "Result CP Press Chart" lc.y_axis.title = "Press.weight (g)" lc.x_axis.title = "Lot No." lc.width = len(dataArray[0]) * 2.5 #print("width ", lc.width) if lc.width <= 5: lc.width = 10 enables = [0, 1,2,3,4,5, 10,11 ] for r in enables: data = Reference(ws, min_row=startRow+r, max_row=startRow+r, min_col=startCol, max_col=startCol + len(dataArray[0])) lc.add_data(data, from_rows=True, titles_from_data=True) lots = Reference(ws, min_row=startRow + 18, max_row=startRow+18, min_col=startCol+1, max_col=startCol+len(dataArray[0]) ) lc.set_categories(lots) for se in lc.series: se.graphicalProperties.line.noFill = True se.marker.symbol = "auto" lc.series[6].graphicalProperties.line.noFill = False lc.series[7].graphicalProperties.line.noFill = False font_test = drawingText.Font(typeface='Calibri') cp = CharacterProperties(latin=font_test, sz=1000) pp = ParagraphProperties(defRPr=cp) cp1 = CharacterProperties(latin=font_test, sz=1400) pp1 = ParagraphProperties(defRPr=cp1) lc.x_axis.title.tx.rich.p[0].pPr = pp# Works! lc.y_axis.title.tx.rich.p[0].pPr = pp# Works lc.title.tx.rich.p[0].pPr = pp1# Works! lc.legend.position = "t" lc.marker = True ws.add_chart(lc, "B2") #create press thickness #find max, min sizes = {} stdSizes = {} #select distinct lot no bq = '''select ds.code,\ ds.lot_no, max(ds.tpresssize) maxsize1, min(ds.tpresssize) minsize1,\ avg(ds.tpresssize) avgSize1 , stdev(ds.tpresssize) sdsize1, \ empids = STUFF((SELECT ',' + str(emp_id) FROM data_ms b WHERE b.lot_no = ds.lot_no group by emp_id FOR XML PATH('')), 1, 2, ''), \ min(ds.created_at) minca,\ pp.Mold_D, pp.Center_D, pp.Press_Ton, pp.PO_Qty, pp.Press_T \ from data_ms ds \ inner join AllProduct_PressPosition_PressWeight pp on pp.ProductCode = Replace('{0}', '-', '') \ and pp.Lot_No = ds.lot_no \ where \ ds.code = '{0}' and ( (ds.size1 > 0) or (ds.size2 > 0) or (ds.tpresssize > 0)) \ group by ds.code, ds.lot_no, pp.Press_Ton, pp.PO_Qty, pp.Mold_D, pp.Center_D, pp.Press_T'''.format(codeno) #print("bq ", bq) cursor.execute(bq) cp_rows = cursor.fetchall() #print(cp_rows) max1Ub = ["Max1 Weight."] min1Ub = ["Min1 Weight."] avg1Ub = ["Avg1 Weight."] r1Ub = ["R1"] sd1 = ["sd1"] w1 = ["Weight(1) std"] unitW = ["Unit Weight"] press_t = ["T Press"] op = ["Operator"] press = ["Press M/C"] holes = ["Hole (mm)"] dias = ["Dia (mm)"] qtys = ["QTY"] lotnos = ["Lot no."] inspecs = ["Inspection Date"] for r in cp_rows: max1Ub.append(r.maxsize1) min1Ub.append(r.minsize1) avg1Ub.append(r.avgSize1) r1Ub.append(r.maxsize1 - r.minsize1) sd1.append(r.sdsize1) press_t.append(r.Press_T) op.append(r.empids) press.append(r.Press_Ton) holes.append(r.Mold_D) dias.append(r.Center_D) qtys.append(r.PO_Qty) lotnos.append(r.lot_no) #print("xdfdsf = "+r.minca.strftime("%m/%d/%Y %H:%M:%S")) inspecs.append(r.minca.strftime("%m/%d/%Y %H:%M:%S")) dataArray = [ max1Ub, #0 min1Ub, #1 avg1Ub, #2 r1Ub, #3 sd1, #4 press_t, #5 op, press, holes, dias, qtys, lotnos, inspecs ] #print(dataArray) startRow = 20 startCol = 15 for i, row in enumerate(dataArray): for j, o in enumerate(row): cell = ws.cell(startRow + i, startCol +j) cell.value = o applyStyleName(cell, "allborder") lc = LineChart() lc.title = "Result CP Thickness press Chart" lc.y_axis.title = "TPress (mm)" lc.x_axis.title = "Lot No." lc.width = len(dataArray[0]) * 2.5 #print("width ", lc.width) if lc.width <= 5: lc.width = 10 enables = [0, 1,2,3,4,5 ] for r in enables: data = Reference(ws, min_row=startRow+r, max_row=startRow+r, min_col=startCol, max_col=startCol + len(dataArray[0])) lc.add_data(data, from_rows=True, titles_from_data=True) lots = Reference(ws, min_row=startRow + 11, max_row=startRow+11, min_col=startCol+1, max_col=startCol+len(dataArray[0]) ) lc.set_categories(lots) for se in lc.series: se.graphicalProperties.line.noFill = True se.marker.symbol = "auto" lc.series[5].graphicalProperties.line.noFill = False #lc.series[7].graphicalProperties.line.noFill = False font_test = drawingText.Font(typeface='Calibri') cp = CharacterProperties(latin=font_test, sz=1000) pp = ParagraphProperties(defRPr=cp) cp1 = CharacterProperties(latin=font_test, sz=1400) pp1 = ParagraphProperties(defRPr=cp1) lc.x_axis.title.tx.rich.p[0].pPr = pp# Works! lc.y_axis.title.tx.rich.p[0].pPr = pp# Works lc.title.tx.rich.p[0].pPr = pp1# Works! lc.legend.position = "t" lc.marker = True ws.add_chart(lc, "O2") def applyStyleName(cell, styleName, format=None): cell.style = styleName if format is not None: #print(format) cell.number_format = format def createChart(codeno, filters): global cursor global wb global dateStyle global codeMap #ws = wb.create_sheet("chart2") #find lot in a codeno sql = generateSQLDataMS(codeno, filters) sql2 = findProductPress(codeno) #print(filters) #print(sql) cursor.execute(sql) row = cursor.fetchall() cursor.execute(sql2) row2 = cursor.fetchall() temp = {} #map data into lotno list #find max, min sizes = {} stdSizes = {} pressType1 = pressType2 = "" for k in row2: if k.PressType_1 != "" and k.PressType_1 is not None: sizes["size1"] = [] stdSizes["size1"] = k pressType1 = k.PressType_1 if k.PressType_2 != "" and k.PressType_2 is not None: sizes["size2"] = [] stdSizes["size2"] = k pressType2 = k.PressType_2 if k.Press_T != "" and k.Press_T is not None: sizes["tpresssize"] = [] stdSizes["tpresssize"] = k pressType3 = k.Press_T for r in row: #print(r) if r.lot_no not in temp: temp[r.lot_no] = [] temp[r.lot_no].append(r) for k,v in sizes.items(): sizes[k].append(getattr(r, k, None)) #filter only dimensions in master #print("TEMP == \n") #print(sizes) avgSize = {} minSize = {} maxSize = {} for k, v in sizes.items(): if len(v) > 0 and v[0] is not None: avgSize[k] = sum(v)/len(v) minSize[k] = min(v) maxSize[k] = max(v) #print("avg size \n",avgSize) #print("min size \n",minSize) #print("max size \n",maxSize) for k1,v1 in temp.items(): #print("\nlot %s \n" %( k1)) ws = wb.create_sheet("lot %s actual" %(k1)) i = 0 bp = 1 for k,v in sizes.items(): #print("%s actual" % (k)) data = [ ["Max (%s) size" % (k) ], ['Min'], ['Actual'], ['No.'], ['Lot No.'] ] nrow = len(data) for v2 in v1: #print("std size ", stdSizes[keySize]) max_min = 0 if k == "size1": max_min = stdSizes[k].PressWeight_1 if k == "size2": max_min = stdSizes[k].PressWeight_2 if k == "tpresssize": max_min = stdSizes[k].Press_T if max_min is None: continue if k == "size1" or k == "size2": maxMaster = minMaster = float(max_min) else: maxMaster = stdSizes[k].Press_T + stdSizes[k].Press_T_Tol minMaster = stdSizes[k].Press_T - stdSizes[k].Press_T_Tol data[0].append(maxMaster) data[1].append(minMaster) data[2].append(getattr(v2, k)) data[3].append(v2.row_no) data[4].append(v2.lot_no) #print(data) #gen chart #print("min col ", bp) wsRows = tuple(ws.iter_rows(min_row=1, max_row=len(data), min_col=bp, max_col= (bp - 1)+len(data[0]))) for i in range(len(wsRows)): for j in range(len(wsRows[i])): #print(i,j) #data[i][j]) #print(wsRows[i][j]) wsRows[i][j].value = data[i][j] if i != 3: wsRows[i][j].number_format = '0.00' bd = Side(style='medium', color="000000") wsRows[i][j].border = Border(left=bd, top=bd, right=bd, bottom=bd) cpos = utils.get_column_letter(bp) + "10" font_test = drawingText.Font(typeface='Calibri') cp = CharacterProperties(latin=font_test, sz=1000) pp = ParagraphProperties(defRPr=cp) cp1 = CharacterProperties(latin=font_test, sz=1400) pp1 = ParagraphProperties(defRPr=cp1) c1 = LineChart() maxValues = Reference(ws, min_row=1, min_col=bp+1, max_row=1, max_col=(bp-1)+len(data[0]) ) maxSerie = Series(maxValues, title="Max") maxSerie.graphicalProperties.line.solidFill = "FF0000" c1.append(maxSerie) minValues = Reference(ws, min_row=2, min_col=bp+1, max_row=2, max_col=(bp-1)+len(data[0]) ) minSerie = Series(minValues, title="Min") minSerie.graphicalProperties.line.solidFill = "000000" c1.append(minSerie) acValues = Reference(ws, min_row=3, min_col=bp+1, max_row=3, max_col=(bp-1)+len(data[0]) ) acSerie = Series(acValues, title="Actual") acSerie.marker.graphicalProperties.solidFill = "4f81bd" # Marker filling acSerie.graphicalProperties.line.solidFill = "4f81bd" acSerie.marker.symbol = "circle" c1.append(acSerie) lots = Reference(ws,min_row=4, min_col=bp+1, max_row=4, max_col=(bp-1)+len(data[0])) c1.set_categories(lots) c1.width = len(data[0])*1.8 if k == "size1": c1.title = "%s (X Bar) " % (pressType1) if k == "size2": c1.title = "%s (X Bar) " % (pressType2) if k == "tpresssize": c1.title = "%s (X Bar) " % ("Thickness press") c1.y_axis.title = "Value(mm.)" c1.x_axis.title = "Item No." c1.x_axis.title.tx.rich.p[0].pPr = pp# Works! c1.y_axis.title.tx.rich.p[0].pPr = pp# Works! c1.title.tx.rich.p[0].pPr = pp1# Works! c1.legend.position = "b" ws.add_chart(c1, cpos) bp += len(data[0]) + 2 #print() #print("M"+str(bp)) #bp += coEndCell[1]+1+10 #print(ws.calculate_dimension()) #create each size table and chart types # actual chart def createExcel(lotno, staff, man, env="dev"): # global cursor # global wb # global dateStyle cnxn = create_db_connection(env) #wb["Styles"].sheet_state = 'hidden' ws = wb["Cert"] codeString = "" qMsData = """ select * from data where lot_no = '{0}' order by updated_at desc, row_no asc """.format(lotno) cursor.execute(f"select * from data where lot_no ='{lotno}' order by created_at desc") r2 = cursor.fetchall() types = [] for r20 in r2: if r20.r_type not in types: types.append(r20.r_type) #print(types) cursor.execute(qMsData) rows = cursor.fetchall() startRow = 19 codeno = "" for r in rows: codeno = r.code if codeno.find('-') != -1: codeString = codeno else: codeString = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10] break #print(ws._charts) cust = findCustomer(codeno.replace("-", ""), lotno) ws["C8"] = codeno.replace("-", "") lc = lotno[0:3].lower() + lotno[3:len(lotno)].upper() ws["C11"] = lc #print(cust) cond1 = getConditionRef(cust[0].MI14) cond2 = getConditionRef(cust[0].MI15) c1text = "" for c in cond1: c1text = c.TIT4NE c2text = "" for c in cond2: c2text = c.TIT4NE #print(c1text, c2text) mi17 = "" if cust[0].MI17 is None: mi17 = "None" else: mi17 = cust[0].MI17 mi16 = str(int(cust[0].MI16)).replace("\\", "") o1 = cust[0].MI18 o2 = cust[0].MI19 if o2 < o1: ot = o1 o1 = o2 o2 = ot mi21 = "" if cust[0].MI21 is None: mi21 = "None" else: mi21 = cust[0].MI21 mi20 = str(int(cust[0].MI20)).replace("\\", "") o3 = cust[0].MI22 o4 = cust[0].MI23 if o3 < o4: ot = o3 o3 = o4 o4 = ot ws["C16"] = "Hardness: "+ c2text + " " +c1text + f", {types[0]}: {o1:.2f} - {o2:.2f} ({mi16} {mi17.upper()})" applyStyleName(ws["C16"], "defaultfont") if cust: ws["C7"] = cust[0].customer ws["C10"] = cust[0].sizestring ws["C9"] = cust[0].spec #print(cust[0].PRO5) #print(cust[0].PRO27) ws["C12"] = str(int(cust[0].PRO5 - cust[0].PRO27)) + " Pcs" ws["C45"] = staff ws["G45"] = man rn = [] for r in rows: isNG = False if r.r_type != types[0]: continue if r.row_no not in rn: rn.append(r.row_no) else: continue ws.cell(column=3, row=startRow, value=int(r.row_no)) applyStyleName(ws.cell(column=3, row=startRow), "allborder", "0") codeno = r.code if codeno.find('-') != -1: codeString = codeno else: codeString = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10] for i in range(0, r.ndata): ws.cell(column=3 + i + 1, row=startRow, value=getattr(r, f"p{i+1}")) applyStyleName(ws.cell(column=3 + i + 1, row=startRow), "allborder") applyStyleName(ws.cell(column=6, row=startRow), "allborder") ws.cell(column=7, row=startRow, value=r.avg) applyStyleName(ws.cell(column=7, row=startRow), "allborder") ws.cell(column=8, row=startRow, value=r.rgrade) applyStyleName(ws.cell(column=8, row=startRow), "allborder") startRow += 1 lastRow = r if len(types) < 2: #ws.delete_rows(26, 5) ws.row_dimensions[26].hidden = True ws.row_dimensions[27].hidden = True ws.row_dimensions[28].hidden = True ws.row_dimensions[29].hidden = True ws.row_dimensions[30].hidden = True else: ws["C26"] = "Hardness: "+ c2text + " " +c1text + f", {types[1]}: {o3:.2f} - {o4:.2f} ({mi20} {mi21.upper()})" startRow = 29 rn = [] for r in rows: isNG = False if r.r_type != types[1]: continue if r.row_no not in rn: rn.append(r.row_no) else: continue ws.cell(column=3, row=startRow, value=int(r.row_no)) applyStyleName(ws.cell(column=3, row=startRow), "allborder", "0") codeno = r.code if codeno.find('-') != -1: codeString = codeno else: codeString = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10] for i in range(0, r.ndata): ws.cell(column=3 + i + 1, row=startRow, value=getattr(r, f"p{i+1}")) applyStyleName(ws.cell(column=3 + i + 1, row=startRow), "allborder") applyStyleName(ws.cell(column=6, row=startRow), "allborder") ws.cell(column=7, row=startRow, value=r.avg) applyStyleName(ws.cell(column=7, row=startRow), "allborder") ws.cell(column=8, row=startRow, value=r.rgrade) applyStyleName(ws.cell(column=8, row=startRow), "allborder") startRow += 1 lastRow = r #ws["C13"] = lastRow.created_at ws["C13"] = datetime.datetime.now() ws["C13"].number_format = "mmmm,d yyyy" ut = int(time.time()) filename = f"hardness_{lotno.replace('-', '')}_{ut}.xlsx" outfile = os.path.join(settings.MEDIA_ROOT, 'excel', filename) # Ensure the directory exists os.makedirs(os.path.dirname(outfile), exist_ok=True) wb.save(filename=outfile) return os.path.join('excel', filename) # Return relative path for media URL usage if __name__ == "__main__": env = sys.argv[1] #code = sys.argv[2] lotno = sys.argv[2] staff = sys.argv[3] man = sys.argv[4] #print(len(sys.argv)) 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(lotno, staff, man) #createXRangeChart(code, df) ut = int(time.time()) filename = "hardness{0}_{1}.xlsx".format(lotno.replace("-", ""), ut) outfile = './public/excel/'+filename wb.save(filename = outfile) print(filename)