tum
/
coi


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356
							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

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 = './press-temp.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 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(codeno, filters):
    global cursor
    global wb
    global dateStyle


    wb["Styles"].sheet_state = 'hidden'

    ws  = wb["Data Measurement"]
    codeString = ""

    if codeno.find('-') != -1:
        codeString = codeno
    else:
        codeString  = codeno[0] + "-" + codeno[1:5] + "-" + codeno[5:9] + "-" + codeno[9:10]

    # std sizes
    stdSizes = """
    select * from AllProduct_PressPosition_PressWeight where ProductCode='{0}'""".format(codeString.replace("-", ""))
    #print(stdSizes)
    cursor.execute(stdSizes)
    rows = cursor.fetchall()
    stdSizeMapColumn = {
    "D": "F",
    "T": "G",
    "H": "H",
    "A": "I",
    "B": "J",
    "C": "K",
    "E": "L",
    "F": "M",
    "G": "N",
    "J": "O",
    "K": "P",
    "L": "Q",
    "M": "R",
    "N": "S",
    "O": "T",
    "P": "U",
    "U": "V",
    "Q": "W",
    "R": "X",
    "S": "Y",
    "W": "Z",
    "Y": "AA",
    "Z": "AB",
    "X1": "AC",
    "X2": "AD",
    "Thickness": "AE",
    "Flatness": "AF",
    "Cen": "AG",
    "Weight": "AH",
    "Result": "AI",
    }

    '''
    for k,v in stdSizeMapColumn.items():
        if k not in ["Weight", "Result"]:
            ws.column_dimensions[v].hidden = True
    '''

    enableCols = []
    for r in rows:
        if r.PressType_1 != "" and r.PressType_1 is not None:
            ws["F8"] = r.PressWeight_1
            ws["F10"] = r.PressType_1
        else:
            ws.column_dimensions["F"].hidden = True

        if r.PressType_2 != "" and r.PressType_2 is not None:
            ws["G8"] = r.PressWeight_1
            ws["G10"] = r.PressType_1
        else:
            ws.column_dimensions["G"].hidden = True

        if r.Press_T != "" and r.Press_T is not None:
            ws["I8"] = r.Press_T
        else:
            ws.column_dimensions["I"].hidden = True


    if filters is not None:
        qMsData = """
        select a.*, b.v1, b.v2, b.ac1, b.ac2, b.ac3, b.ac4, b.ac5, b.ac6, b.c0 from data_ms a
        left join press_cal b
        on a.lot_no = b.lot_no
        where code='{0}' and ((size1 > 0) or (size2 > 0 ) or (tpresssize > 0)) and {1}
        order by lot_no asc, row_no asc
        """.format(codeString, filters.replace("created_at", "a.created_at"))
    else:
        qMsData = """
        select a.*, b.v1, b.v2, b.ac1, b.ac2, b.ac3, b.ac4, b.ac5, b.ac6, b.c0 from data_ms a
        left join press_cal b
        on a.lot_no = b.lot_no
        where code='{0}' and ((size1 > 0) or (size2 > 0 ) or (tpresssize > 0))
        order by lot_no asc, row_no asc
        """.format(codeString)


    cursor.execute(qMsData)
    rows = cursor.fetchall()
    startRow = 11


    for r in rows:
        isNG = False

        if int(r.row_no) == 1:
            ws.cell(column = 1, row = startRow, value = r.created_at).number_format = "m/d/yy"
            applyStyleName(ws.cell(column=1, row=startRow),"allborder",  "m/d/yy hh:mm")
            ws.cell(column = 2, row = startRow, value = r.code)
            applyStyleName(ws.cell(column=2, row=startRow), "allborder")
            ws.cell(column = 3, row = startRow, value=r.lot_no)
            applyStyleName(ws.cell(column=3, row=startRow), "allborder")
            countLot = """
            select count(*) as c from data_ms where lot_no = '{0}'
            """.format(r.lot_no)
            cursor.execute(countLot)
            countResult =  cursor.fetchall()
            #print(countResult)
            ws.cell(column = 4, row = startRow, value = countResult[0].c).number_format = "0"
            applyStyleName(ws.cell(column=4, row=startRow), "allborder")

        c = ws.cell(column=5, row = startRow, value = int(r.row_no))
        applyStyleName(ws.cell(column=5, row=startRow), "allborder")
        c.number_format = "0"


        colstr = "F"
        #print("x1", r.x1size)
        ws[colstr + str(startRow)] = r.size1
        applyStyleName(ws[colstr + str(startRow)], "allborder")

        if r.size1Ok == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = "G"
        ws[colstr + str(startRow)] = r.size2
        applyStyleName(ws[colstr + str(startRow)], "allborder")

        if r.size2Ok == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = "I"
        ws[colstr + str(startRow)] = r.tpresssize
        applyStyleName(ws[colstr + str(startRow)], "allborder")

        if r.tpresssizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = "J"
        ws[colstr + str(startRow)] = r.tpresssizeOk
        applyStyleName(ws[colstr + str(startRow)], "allborder")

        if r.tpresssizeOk == "NG":
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = "K"
        r.v1 = r.v1 or 0
        r.v2 = r.v2 or 0
        ws[colstr + str(startRow)] = r.v1 + r.v2
        ws[colstr + str(startRow)].number_format = "#,##0.00"
        applyStyleName(ws[colstr + str(startRow)], "allborder")


        colstr = "H"

        if isNG == True:
            ws[colstr+ str(startRow)] = "NG"
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)
        else:
            ws[colstr+ str(startRow)] = "OK"

        ws["N"+ str(startRow)] = r.ac1 or 0
        applyStyleName(ws["N" + str(startRow)], "allborder")

        ws["O"+ str(startRow)] = r.ac2 or 0
        applyStyleName(ws["O" + str(startRow)], "allborder")

        ws["P"+ str(startRow)] = r.ac3 or 0
        applyStyleName(ws["P" + str(startRow)], "allborder")

        ws["Q"+ str(startRow)] = r.ac4 or 0
        applyStyleName(ws["Q" + str(startRow)], "allborder")

        ws["R"+ str(startRow)] = r.ac5 or 0
        applyStyleName(ws["R" + str(startRow)], "allborder")

        ws["S"+ str(startRow)] = r.ac6 or 0
        applyStyleName(ws["S" + str(startRow)], "allborder")

        ws["T"+ str(startRow)] = r.c0 or 0
        applyStyleName(ws["T" + str(startRow)], "allborder")

        '''
        colstr = stdSizeMapColumn.get("X")
        ws[colstr+str(startRow)].value = r.xsize
        if r.xsizeOk == "NG":
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)

        '''


        #ws['U'+str(startRow)] = r.ysize or 0
        #ysize.font= Font(color = colors.Red)
        #ws['V'+str(startRow)] = r.zsize

        startRow += 1

    #print(ws._charts)
    cust = findCustomer(codeno.replace("-", ""))
    ws["B3"] = codeno.replace("-", "")
    if cust:
        ws["B2"] = cust[0].customer
        ws["B4"] = cust[0].sizestring
        ws["B5"] = cust[0].spec


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=localhost;DATABASE=OB2011DB;UID=admin;PWD=1234')
        cursor = cnxn.cursor()
    #print("code is "+code)

    createExcel(code, df)
    createChart(code, df)
    createXBarChart(code, df)
    createPressCP(code, df)
    #createXRangeChart(code, df)
    ut = int(time.time())
    filename = "press{0}_{1}.xlsx".format(code.replace("-", ""), ut)
    outfile = './public/excel/'+filename
    wb.save(filename = outfile)
    print(filename)