coi/app/report/measuremente...

# -*- coding: utf-8 -*-
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 shutil import copyfile
from copy import copy

dateStyle = NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM')
from openpyxl.styles.numbers import FORMAT_DATE_DATETIME
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",
"FLATNESS": "AF",
"Cen": "AG",
"Weight": "AH",
"Result": "AI",
}
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",
        "61": "U"
        }
# 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',read_only=False, keep_links=False, data_only=False)
#wb0 = load_workbook(filename = './testfile.xlsx', read_only=False, keep_links=False, data_only=False)

#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
    applyStyleName(ws["B2"], "blackborder")
    ws["B3"] = codeno.replace("-", "")
    applyStyleName(ws["B3"], "blackborder")
    ws["B4"] = cust[0].sizestring
    applyStyleName(ws["B4"], "blackborder")
    ws["B5"] = cust[0].spec
    applyStyleName(ws["B5"], "blackborder")

    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)

        applyStyleName(ws.cell(column= ccode, row=45, value = val.lot_no), "blackborder")
        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 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 {1} order by lot_no asc, row_no asc
        """.format(codeString, filters)
    else:
        qMsData = """
        select * from data_ms where code='{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", 3)
    #find lot in a codeno
    sql = generateSQLDataMS(codeno, filters)
    sql2 = findProductDimension(codeno)

    cursor.execute(sql)
    row = cursor.fetchall()

    cursor.execute(sql2)
    row2 = cursor.fetchall()

    temp =  {}
    #map data into lotno list

    #find max, min
    sizes = {}
    stdSizes = {}
    num = 0
    for k in row2:
        if k.Size_Id in codeMap:
            sn = codeMap[k.Size_Id].lower()+"size"
            sizes[sn] = []
            stdSizes[sn] = k

    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])
            maxMaster = float(stdSizes[keySize].Std) + float(stdSizes[keySize].TolUp)
            minMaster = float(stdSizes[keySize].Std) + float(stdSizes[keySize].TolUn)
            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:
                    pass

        #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)
        ws.cell(1, bp, value = "%s-size (bar)" % (keySize.upper().replace("SIZE", "")))
        #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)

        c1 = LineChart()
        pp = ParagraphProperties(defRPr=cp)

        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])*2.7
        c1.title = "%s-size (X bar)" % (keySize.upper().replace("SIZE", ""))
        #c1.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
        c1.y_axis.title = "Value(mm.)"
        c1.x_axis.title = "Lot 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)

        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])*2.7
        c2.title = "%s-size (R)" % (keySize.upper().replace("SIZE", ""))
        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

def createCp(codeno, filters):
    global cursor
    global wb
    global dateStyle
    global codeMap
    ws = wb.create_sheet("CP Press")
    #find lot in a codeno
    sql = generateSQLDataMS(codeno, filters)
    sql2 = findProductDimension(codeno)

    cursor.execute(sql)
    row = cursor.fetchall()

    cursor.execute(sql2)
    row2 = cursor.fetchall()

    temp =  {}
    #map data into lotno list

    #find max, min
    sizes = {}
    stdSizes = {}

    for k in row2:
        if k.Size_Id in codeMap:
            sn = codeMap[k.Size_Id].lower()+"size"
            sizes[sn] = []
            stdSizes[sn] = k

    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])
            maxMaster = float(stdSizes[keySize].Std) + float(stdSizes[keySize].TolUp)
            minMaster = float(stdSizes[keySize].Std) + float(stdSizes[keySize].TolUn)
            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])):
                ta[j][i] = l[i][j]

        #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)
        ws.cell(1, bp, value = "%s-size (bar)" % (keySize.upper().replace("SIZE", "")))
        #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)

        c1 = LineChart()
        pp = ParagraphProperties(defRPr=cp)

        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
        c1.title = "%s-size (X bar)" % (keySize.upper().replace("SIZE", ""))
        #c1.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
        c1.y_axis.title = "Value(mm.)"
        c1.x_axis.title = "Lot 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)

        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
        c2.title = "%s-size (R)" % (keySize.upper().replace("SIZE", ""))
        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 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)
    #print(sql)
    sql2 = findProductDimension(codeno)

    cursor.execute(sql)
    row = cursor.fetchall()

    cursor.execute(sql2)
    row2 = cursor.fetchall()

    temp =  {}
    #map data into lotno list

    #find max, min
    sizes = {}
    stdSizes = {}

    for k in row2:
        if k.Size_Id in codeMap:
            sn = codeMap[k.Size_Id].lower()+"size"
            sizes[sn] = []
            stdSizes[sn] = k

    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():
        #print(k, v)
        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])
                maxMaster = float(stdSizes[k].Std) + float(stdSizes[k].TolUp)
                minMaster = float(stdSizes[k].Std) + float(stdSizes[k].TolUn)
                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
            c1.title = "%s-size (actual) " % (k.upper().replace("SIZE", ""))
            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
    global stdSizeMapColumn

    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_Dimension_ForInsProcess where ProductCode='{0}'""".format(codeString.replace("-", ""))
    #print(stdSizes)
    cursor.execute(stdSizes)
    rows = cursor.fetchall()


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

    enableCols = []
    for r in rows:
        minv = float(r.Std) + float(r.TolUn)
        stdv = float(r.Std)
        maxv = float(r.Std) + float(r.TolUp)
        #print(r.Size_Name.replace("size", "").replace("Size", "").strip())
        #print(r.Size_Name.replace("size", "").replace("Size", "").strip() in stdSizeMapColumn)

        if r.Size_Name.replace("size", "").replace("Size", "").strip() in stdSizeMapColumn:
            colName = stdSizeMapColumn[r.Size_Name.replace("size", "").replace("Size", "").strip()]
            #print(colName)
            #print(colName+"12")
            #print(min)
            ws.column_dimensions[colName].hidden = False
            ws.column_dimensions[colName].bestFit = True
            ws.column_dimensions[colName].width = 8

            #print(colName)
            #print(ws.column_dimensions[colName].hidden)
            #print(ws.column_dimensions[colName].collapsed)
            #print(ws.column_dimensions[colName].bestFit)
            #print(ws.column_dimensions[colName].width)



            ws[colName+"12"] = minv #min
            applyStyleName(ws[colName+"12"], "border", "0.00")
            ws[colName+"11"] = stdv #std
            applyStyleName(ws[colName+"11"], "border", "0.00")
            ws[colName+"10"] = maxv #max
            applyStyleName(ws[colName+"10"], "border", "0.00")
            enableCols.append(r.Size_Name.replace("size", "").replace("Size", "").strip())

    #print(enableCols)
    maxQuery = [ "max(%ssize) as %s"% ( x.lower(), x.upper() )  for x in enableCols]
    #print(maxQuery)
    if filters is not None:
        if codeno != "undefined":
            qt = "select {2} from data_ms where code='{0}' and ( (size1 > 0) or (size2 > 0)) and {1}".format(codeString, filters, ",".join(maxQuery))
        else:
            qt = "select * from data_ms where  ( (size1 > 0) or (size2 > 0)) and {1}".format(codeString, filters, ",".join(maxQuery))


    else:
        if codeno != "undefined":
            qt = "select {1} from data_ms where code='{0}' and ( (size1 > 0 ) or (size2 > 0))".format(codeString, ",".join(maxQuery))
        else:
            qt = "select * from data_ms where ( (size1 > 0) or (size2 > 0))".format(codeString, ",".join(maxQuery))


    #print("qt = ", qt)
    cursor.execute(qt)
    rows = cursor.fetchall()
    for r in rows:
        #print(r.cursor_description)
        for c in r.cursor_description:
            #print(c)
            #print(getattr(r, c[0]))
            colName = stdSizeMapColumn[c[0]]
            ws[colName+"8"] = getattr(r, c[0])
            applyStyleName(ws[colName+"8"], "border")

    maxQuery = [ "min(%ssize) as %s"% ( x.lower(), x.upper() )  for x in enableCols]


    #print(maxQuery)
    if filters is not None:
        if codeno != "undefined":
            qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery))
        else:
            qt = "select * from data_ms where  {1}".format(codeString, filters, ",".join(maxQuery))


    else:
        if codeno != "undefined":
            qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery))
        else:
            qt = "select * from data_ms".format(codeString, ",".join(maxQuery))
    '''
    if filters is not None:
        qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery))
    else:
        qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery))
    '''

    #print(qt)


    cursor.execute(qt)
    rows = cursor.fetchall()
    for r in rows:
        #print(r.cursor_description)
        for c in r.cursor_description:
            #print(c)
            #print(getattr(r, c[0]))
            colName = stdSizeMapColumn[c[0]]
            ws[colName+"9"] = getattr(r, c[0])
            applyStyleName(ws[colName+"9"], "border")

    #query data
    if filters is not None:
        qMsData = """
        select * from data_ms where code='{0}' and {1} order by lot_no asc, row_no asc
        """.format(codeString, filters)
    else:
        qMsData = """
        select * from data_ms where code='{0}' order by lot_no asc, row_no asc
        """.format(codeString)

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


    for r in rows:
        isNG = False
        if r.dsize == 0 and r.hsize == 0 and r.tsize == 0:
            continue

        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), "border", "m/d/yy")

            ws.cell(column = 2, row = startRow, value = r.code)
            applyStyleName(ws.cell(column = 2, row = startRow), "border")

            ws.cell(column = 3, row = startRow, value=r.lot_no)
            applyStyleName(ws.cell(column = 3, row = startRow), "border")

            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), "border")

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


        colstr = stdSizeMapColumn.get("D")
        ws[colstr + str(startRow)] = r.dsize
        applyStyleName(ws[colstr + str(startRow)], "border")

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

        colstr = stdSizeMapColumn.get("T")
        ws[colstr + str(startRow)] = r.tsize
        applyStyleName(ws[colstr + str(startRow)], "border")

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

        colstr = stdSizeMapColumn.get("H")
        applyStyleName(ws[colstr + str(startRow)], "border")

        if ( r.hdev is not None) and ( r.hsizeproxy is not None ) and (r.hsizeproxy != 'null')  and ( r.hdev.strip() != "") and (r.hdev.strip() != 'null'):
            ws[colstr + str(startRow)] = "%s/%s" % (r.hsizeproxy.strip(), r.hdev.strip())
        else:
            if ( r.hsizeproxy is not None ) and ( r.hsizeproxy.strip() != 'null'):
                proxyStrip = r.hsizeproxy.strip()
                if proxyStrip == "OK" or proxyStrip == "NG":
                    ws[colstr + str(startRow)] = proxyStrip
                else:
                    try:
                        ws[colstr + str(startRow)] = float(proxyStrip.replace("+", ""))
                    except Exception as e:
                        pass



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

        colstr = stdSizeMapColumn.get("C")
        ws[colstr + str(startRow)] = r.csize
        applyStyleName(ws[colstr + str(startRow)], "border")

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


        colstr = stdSizeMapColumn.get("E")
        ws[colstr + str(startRow)] = r.esize
        applyStyleName(ws[colstr + str(startRow)], "border")
        if r.esizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("F")
        ws[colstr + str(startRow)] = r.fsize
        applyStyleName(ws[colstr + str(startRow)], "border")
        if r.fsizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("G")
        ws[colstr + str(startRow)] = r.gsize
        applyStyleName(ws[colstr + str(startRow)], "border")
        if r.gsizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("J")
        ws[colstr+str(startRow)] = r.jsize
        applyStyleName(ws[colstr + str(startRow)], "border")

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


        colstr = stdSizeMapColumn.get("K")
        ws[colstr+str(startRow)] = r.ksize
        applyStyleName(ws[colstr + str(startRow)], "border")

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

        colstr = stdSizeMapColumn.get("M")
        ws[colstr+str(startRow)] = r.msize
        applyStyleName(ws[colstr + str(startRow)], "border")

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

        colstr = stdSizeMapColumn.get("N")
        ws[colstr+str(startRow)] = r.nsize
        applyStyleName(ws[colstr + str(startRow)], "border")

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

        colstr = stdSizeMapColumn.get("P")
        ws[colstr+str(startRow)] = r.psize
        applyStyleName(ws[colstr + str(startRow)], "border")

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

        colstr = stdSizeMapColumn.get("U")
        ws[colstr+str(startRow)] = r.usize
        applyStyleName(ws[colstr + str(startRow)], "border")

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

        colstr = stdSizeMapColumn.get("W")
        ws[colstr+str(startRow)] = r.wsize
        applyStyleName(ws[colstr + str(startRow)], "border")

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



        colstr = stdSizeMapColumn.get("Weight")
        ws[colstr+str(startRow)] = r.weight
        applyStyleName(ws[colstr + str(startRow)], "border")

        colstr = stdSizeMapColumn.get("Result")
        applyStyleName(ws[colstr + str(startRow)], "border")

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


        '''
        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)
    if codeno != "undefined":
        cust = findCustomer(codeno.replace("-", ""))
        ws["B3"] = codeno.replace("-", "")
        applyStyleName(ws["B3"], "border")

        if cust:
            ws["B2"] = cust[0].customer
            applyStyleName(ws["B2"], "border")

            ws["B4"] = cust[0].sizestring
            applyStyleName(ws["B4"], "border")

            ws["B5"] = cust[0].spec
            applyStyleName(ws["B5"], "border")

def enableColFunc(ws, code, enableCols):
    global cursor
    global stdSizeMapColumn
    # std sizes
    stdSizes = """
    select * from AllProduct_Dimension_ForInsProcess where ProductCode='{0}'""".format(code.replace("-", ""))
    #print(stdSizes)
    cursor.execute(stdSizes)
    rows = cursor.fetchall()

    for r in rows:
        if r.Size_Name.replace("size", "").replace("Size", "").strip() in stdSizeMapColumn:
            colName = stdSizeMapColumn[r.Size_Name.replace("size", "").replace("Size", "").strip()]
            #print(colName+"12")
            #print(min)
            ws.column_dimensions[colName].hidden = False
            stripSize =  r.Size_Name.replace("size", "").replace("Size").strip()
            if stripSize not in enableCols:
                enableCols.append(stripSize)

def applyStyleName(cell, styleName, format=None):
    cell.style = styleName
    if format is not None:
        #print(format)
        cell.number_format = format
    else:
        cell.number_format = "0.00"

def createExcelDaily(codeno, filters, segment):
    global cursor
    global wb
    global dateStyle
    #wb["Data Measurement"].sheet_state = 'hidden'
    ws  = wb["Daily"]
    codeString = ""



    for idx in range(1, 13):
        ws.row_dimensions[idx].hidden = True

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

    enableCols = []

    if codeno != "" and codeno != "undefined":
        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_Dimension_ForInsProcess where ProductCode='{0}'""".format(codeString.replace("-", ""))
        #print(stdSizes)
        cursor.execute(stdSizes)
        rows = cursor.fetchall()
        for r in rows:
            minv = float(r.Std) + float(r.TolUn)
            stdv = float(r.Std)
            maxv = float(r.Std) + float(r.TolUp)

            if r.Size_Name.replace("size", "").strip() in stdSizeMapColumn:
                colName = stdSizeMapColumn[r.Size_Name.replace("size", "").strip()]
                #print(colName+"12")
                #print(min)
                ws.column_dimensions[colName].hidden = False
                #ws[colName+"12"] = minv #min
                #ws[colName+"11"] = stdv #std
                #ws[colName+"10"] = maxv #max
                enableCols.append(r.Size_Name.replace("size", "").strip())




    if codeno ==  "undefined":
        distinct_qt = "select distinct(code) code from data_ms where  {1}".format(codeString, filters)
        cursor.execute(distinct_qt)
        rows = cursor.fetchall()

        #print("dist = ", distinct_qt)

        for r in rows:
            enableColFunc(ws, r.code, enableCols)

    maxQuery = [ "max(%ssize) as %s"% ( x.lower(), x.upper() )  for x in enableCols]
    #print(maxQuery)
    if filters is not None:
        if codeno != "undefined":
            qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery))
        else:
            qt = "select {2} from data_ms where  {1}".format(codeString, filters, ",".join(maxQuery))


    else:
        if codeno != "undefined" and codeno != "":
            qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery))
        else:
            qt = "select {1} from data_ms".format(codeString, ",".join(maxQuery))


    #print("qt = ", qt)

    cursor.execute(qt)
    rows = cursor.fetchall()
    for r in rows:
        #print(r.cursor_description)
        #enableColFunc(ws, r.code, enableCols)
        for c in r.cursor_description:
            #print(c)
            #print(getattr(r, c[0]))
            if c[0] in stdSizeMapColumn:
                colName = stdSizeMapColumn[c[0]]
                #ws[colName+"8"] = getattr(r, c[0])

    maxQuery = [ "min(%ssize) as %s"% ( x.lower(), x.upper() )  for x in enableCols]


    #print(maxQuery)
    if filters is not None:
        if codeno != "undefined":
            qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery))
        else:
            qt = "select {2} from data_ms where  {1}".format(codeString, filters, ",".join(maxQuery))


    else:
        if codeno != "undefined":
            qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery))
        else:
            qt = "select {1} from data_ms".format(codeString, ",".join(maxQuery))
    '''
    if filters is not None:
        qt = "select {2} from data_ms where code='{0}' and {1}".format(codeString, filters, ",".join(maxQuery))
    else:
        qt = "select {1} from data_ms where code='{0}'".format(codeString, ",".join(maxQuery))
    '''




    cursor.execute(qt)
    rows = cursor.fetchall()
    for r in rows:
        #print(r.cursor_description)
        for c in r.cursor_description:
            #print(c)
            #print(getattr(r, c[0]))
            if c[0] in stdSizeMapColumn:
                colName = stdSizeMapColumn[c[0]]
                #ws[colName+"9"] = getattr(r, c[0])

    #query data
    joinString = joinSelect = ""
    #print("segment = ", segment)
    if segment != None:
        joinSelect = ",lt.*"
        joinString = "inner join LotTracking lt on ms.id = lt.datams_id and lt.machineGroup = '{0}'" .format(segment)
        filters = "ms."+filters
    else:
        joinSelect = ",lt.*"
        joinString = "inner join LotTracking lt on ms.id = lt.datams_id" .format(segment)
        filters = "ms."+filters
    #print(joinString)
    if filters is not None:
        if codeno != "undefined":
            qMsData = """
            select ms.* {3} from data_ms ms {2} where code='{0}' and {1} order by lot_no asc, row_no asc
            """.format(codeString, filters, joinString, joinSelect)
        else:
            qMsData = """
            select ms.* {3} from data_ms ms {2} where  {1} order by lot_no asc, row_no asc
            """.format(codeString, filters, joinString, joinSelect)
    else:
        if codeno != "undefined":
            qMsData = """
            select ms.* {2} from data_ms ms {1} where code='{0}' order by lot_no asc, row_no asc
            """.format(codeString, joinString, joinSelect)
        else:
            qMsData = """
            select ms.* {2} from data_ms ms {1} order by lot_no asc, row_no asc
            """.format(codeString, joinString, joinSelect)

    #print(qMsData)
    cursor.execute(qMsData)
    rows = cursor.fetchall()
    startRow = 15

    styleName = "border"
    for r in rows:
        isNG = False

        '''
        if r.dsize == 0 and r.hsize == 0 and r.tsize == 0:
            continue
        '''

        if int(r.row_no) == 1:
            ws.cell(column = 1, row = startRow, value = r.created_at).number_format = "m/d/yy"
            ws.cell(column = 1, row = startRow).style = styleName

            ws.cell(column = 2, row = startRow, value = r.code)
            ws.cell(column = 2, row = startRow).style = styleName

            ws.cell(column = 3, row = startRow, value=r.lot_no)
            ws.cell(column = 3, row = startRow).style = styleName
            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"
            ws.cell(column = 4, row = startRow).style = styleName

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

        ws["AJ"+str(startRow)] = r.emp_id
        ws["AJ"+str(startRow)].style = styleName

        ws["AK"+str(startRow)] = r.created_at
        applyStyleName(ws["AK"+str(startRow)], styleName, FORMAT_DATE_DATETIME)


        colstr = stdSizeMapColumn.get("D")
        ws[colstr + str(startRow)] = r.dsize
        applyStyleName(ws[colstr + str(startRow)], styleName)
        if r.dsizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("T")
        ws[colstr + str(startRow)] = r.tsize
        applyStyleName(ws[colstr + str(startRow)], styleName)
        if r.tsizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("H")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        if ( r.hdev is not None) and ( r.hsizeproxy is not None ) and (r.hsizeproxy != 'null')  and ( r.hdev.strip() != "") and (r.hdev.strip() != 'null'):
            ws[colstr + str(startRow)] = "%s/%s" % (r.hsizeproxy.strip(), r.hdev.strip())
        else:
            if ( r.hsizeproxy is not None ) and ( r.hsizeproxy.strip() != 'null'):
                proxyStrip = r.hsizeproxy.strip()
                if proxyStrip == "OK" or proxyStrip == "NG":
                    ws[colstr + str(startRow)] = proxyStrip
                else:
                    try:
                        ws[colstr + str(startRow)] = float(proxyStrip.replace("+", ""))
                    except Exception as e:
                        pass



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

        colstr = stdSizeMapColumn.get("C")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr + str(startRow)] = r.csize
        if r.csizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)


        colstr = stdSizeMapColumn.get("E")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr + str(startRow)] = r.esize
        if r.esizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("F")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr + str(startRow)] = r.fsize
        if r.fsizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("G")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr + str(startRow)] = r.gsize
        if r.gsizeOk == "NG":
            isNG = True
            ws[colstr + str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("J")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr+str(startRow)] = r.jsize
        if r.jsizeOk == "NG":
            isNG = True
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)


        colstr = stdSizeMapColumn.get("K")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr+str(startRow)] = r.ksize
        if r.ksizeOk == "NG":
            isNG = True
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("M")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr+str(startRow)] = r.msize
        if r.msizeOk == "NG":
            isNG = True
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("N")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr+str(startRow)] = r.nsize
        if r.nsizeOk == "NG":
            isNG = True
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("P")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr+str(startRow)] = r.psize
        if r.psizeOk == "NG":
            isNG = True
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("U")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr+str(startRow)] = r.usize
        if r.usizeOk == "NG":
            isNG = True
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)

        colstr = stdSizeMapColumn.get("W")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr+str(startRow)] = r.wsize
        if r.wsizeOk == "NG":
            isNG = True
            ws[colstr+ str(startRow)].font = Font(color = colors.RED)



        colstr = stdSizeMapColumn.get("Weight")
        applyStyleName(ws[colstr + str(startRow)], styleName)
        ws[colstr+str(startRow)] = r.weight

        colstr = stdSizeMapColumn.get("Result")
        applyStyleName(ws[colstr + str(startRow)], styleName)

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


        '''
        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.
        #print(wb.style_names)
        #print(ws["AJ"+str(startRow)].style)
        ws["AJ"+str(startRow)] = r.emp_id
        ws["AJ"+str(startRow)].style = styleName

        ws["AK"+str(startRow)] = r.created_at
        applyStyleName(ws["AK"+str(startRow)], styleName, FORMAT_DATE_DATETIME)

        ws["AL"+str(startRow)] = "Dimension out of specs" if isNG else ""
        applyStyleName(ws["AL"+str(startRow)], styleName)

        ws["AN"+str(startRow)] = str(r.machineId)
        applyStyleName(ws["AN"+str(startRow)], styleName)

        ws["AO"+str(startRow)] = str(r.machineGroup)
        applyStyleName(ws["AO"+str(startRow)], styleName)

        startRow += 1


    #print(ws._charts)
    if codeno != "undefined":
        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
    segment = None
    #print(len(sys.argv))
    if len(sys.argv) > 3:
        filter = sys.argv[3]
        #import ast
        #df = ast.literal_eval(filter)
        #print(df['date'])
        df = filter
    if len(sys.argv) > 4:
        segment = sys.argv[4]

    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)


    if code != "undefined"  and code != "":
        wb["Daily"].sheet_state = "hidden"
        createExcel(code, df)
        createChart(code, df)
        createXBarChart(code, df)
        #createCp(code, df)

    else:
        wb["Data Measurement"].sheet_state = "hidden"
        wb["Chart"].sheet_state = "hidden"
        createExcelDaily(code, df, segment);
    #createXRangeChart(code, df)


    '''
    thin_border = Border(left=Side(style='thick'),
                     right=Side(style='thick'),
                     top=Side(style='thick'),
                     bottom=Side(style='thick'))
    wtd['C1'].value = 1234
    wtd["C1"].border = thin_border
    wtd['D1'].value = "Hello"
    '''


    #print wb.get_sheet_names()
    #print wb0.get_sheet_names()

    ut = int(time.time())
    if code != "undefined":
        filename = "measurement{0}_{1}.xlsx".format(code.replace("-", ""), ut)
    else:
        filename = "measurement_{1}.xlsx".format(code.replace("-", ""), ut)
    outfile = './public/excel/'+filename
    wb.save(filename = outfile)
    wb.close()
    print(filename)