coi/app/report/hardnessexce...

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)