coi/app/report/excel.py

from openpyxl import load_workbook
import pyodbc
import time
import sys
from pprint import pprint
import datetime
from openpyxl.styles import NamedStyle, Font
from copy import deepcopy
from openpyxl.chart import LineChart, Reference, Series
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import  RichTextProperties,Paragraph,ParagraphProperties, CharacterProperties

dateStyle = NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM')

# Specifying the ODBC driver, server name, database, etc. directly
#cnxn = None
#cursor = None
'''cursor.execute('select * from data_wb')
rows = cursor.fetchall()
for row in rows:
    print(row)'''

wb = load_workbook(filename = './datacpmaster2.xlsx')

#wb.save(filename = './createfrompy.xlsx');
def findCustomer(code):
    global cursor
    global env

    tempResult = []
    views = []
    if env == "prod":
        views = ["bel_master_view", "e_master_view", "mg_master_view", "v_master_view"]
    else:
        views = ["bel_master_view_dev", "e_master_view_dev", "mg_master_view_dev", "v_master_view_dev"]

    ts = []

    select = "distinct MI24 as unbStd, PRO1C as customer, cast(PRO8 as varchar) + ' ' + cast(PRO9 as varchar) + cast(PRO10 as varchar)  +  'x' + cast(PRO11 as varchar ) + 'x' + cast(PRO12 as varchar) as sizestring, cast(PRO13 as varchar) + ' ' + cast(PRO14 as varchar) + ' ' + cast(PRO15 as varchar) + ' ' + cast(PRO16 as varchar) + ' ' + cast(PRO17 as varchar) + ' ' + cast(PRO18 as varchar) as spec"
    where = "PRO1 = '{0}'".format(code)
    for v in views:
        sqlQuery = "select {0} from {1} where {2}".format(select, v, where)
        ts.append(sqlQuery)
    merged = []
    for t in ts:
        merged += cursor.execute(t)
    return merged
def createWS(name, values, codeno, filters):
    global wb
    #wb.create_sheet(name)
    wo = wb['Data Cp']
    ws = wb.copy_worksheet(wo)
    ws.title = name
    cust = findCustomer(codeno.replace("-", ""))
    ws["B2"] = cust[0].customer
    ws["B3"] = codeno.replace("-", "")
    ws["B4"] = cust[0].sizestring
    ws["B5"] = cust[0].spec

    bcode = ord('B')
    ccode = 2
    for idx, val in enumerate(values):
        #print(idx)
        c = chr(bcode + idx)
        #print(val.lot_no)
        if  ( bcode + idx) > ord('Z'):
            r = bcode + idx - ord('Z')
            c = 'A' + chr(ord('A') + (r - 1))
            #print("c = "+c)

        ws.cell(column= ccode, row=45, value = val.lot_no)
        ms = findInMasterView('*', "PRO2 = '{0}'".format(val.lot_no))
        #pressInfo = findPressInfo(codeno.replace("-", ""),val.lot_no, filters)
        #print("master view")
        #print(ms)
        if ms:
            ws.cell(column = ccode, row=35, value = ms[0].MI24)
            #ws[c+'38'] = ms[0].STD_Balance
            ws.cell(column = ccode , row=37, value =  ms[0].PRO6)
            #ws[c+'44'] = ms[0].PRO5
            ws.cell(column = ccode, row = 43, value = ms[0].PRO10)
            ws.cell(column = ccode, row = 42, value = ms[0].PRO12)

        #if pressInfo:
        ws.cell(column = ccode , row=39, value = val.Press_UserName)
        ws.cell(column = ccode, row = 40, value = val.Press_McName)
        #print(type(pressInfo[0].Insp_Date))
        #ws[c+'46'].number_format = "Short Date"
        ws.cell(column = ccode, row = 46 , value = val.Insp_Date.date())
        #print(type(ws[c+'46'].value))
        #ws[c+'46'].style = dateStyle
        #ws[c+'46'].value = datetime.datetime(2010, 7, 21)

        #print(ws[c+'46'].number_format)

        #ws[c+'46']. = datetime.datetime
        ws.cell(column = ccode , row = 44, value = val.FG_Qty)

        dws = updateLength(val.lot_no)
        for j,v in enumerate(dws):
            ws.cell(column = ccode , row = 48+v.row_no , value  = v.weight)

        dws = updateUb(val.lot_no)
        for j,v in enumerate(dws):
            ws.cell(column = ccode , row = 171+v.row_no , value  = v.weight)

        ccode += 1

    c1 = LineChart()
    c1.legend.position = "t";
    c1.title = "Graph of Result Cp Unbalance"
    c1.title.font = Font(underline=Font.UNDERLINE_SINGLE)
    c1.y_axis.title = "Unb Weight"
    c1.y_axis.number_format = "#,##0.00"
    c1.x_axis.title  = "Lot No"
    c1.width = 30
    c1.height = 10


    #print(len(values))
    lt = Reference(ws, min_col=2, max_col=len(values)+1, min_row=45, max_row=45)
    data = Reference(ws, min_col=1, max_col=len(values)+1, min_row=34, max_row=34)

    maxUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=29, max_row=29)
    minUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=30, max_row=30)
    avgUB = Reference(ws, min_col=1, max_col=len(values)+1, min_row=31, max_row=31)
    vsStd = Reference(ws, min_col=1, max_col=len(values)+1, min_row=36, max_row=36)


    c1.add_data(data, from_rows=True, titles_from_data=True)
    c1.add_data(minUB, from_rows=True, titles_from_data=True)
    c1.add_data(avgUB, from_rows=True, titles_from_data=True)
    c1.add_data(vsStd, from_rows=True, titles_from_data=True)
    c1.add_data(maxUB, from_rows=True, titles_from_data=True)


    s1 = c1.series[0]
    s1.marker.symbol = "triangle"
    s1.graphicalProperties.line.noFill = True

    s1 = c1.series[1]
    s1.marker.symbol = "circle"
    s1.graphicalProperties.line.noFill = True

    s1 = c1.series[2]
    s1.marker.symbol = "square"
    s1.graphicalProperties.line.noFill = True

    s1 = c1.series[4]
    s1.marker.symbol = "diamond"
    s1.graphicalProperties.line.noFill = True

    c1.set_categories(lt)

    c1.x_axis.txPr = RichText(bodyPr=RichTextProperties(anchor="ctr",anchorCtr="1",rot="-2700000",
               spcFirstLastPara="1",vertOverflow="ellipsis",wrap="square"),
            p=[Paragraph(pPr=ParagraphProperties(defRPr=CharacterProperties()), endParaRPr=CharacterProperties())])

    ws.add_chart(c1, "A7")
    #newws.title = name
    #copy data cp ws
    #insert values in the sheet
def findInMasterView(select, where):
    global cursor
    global env

    tempResult = []
    views = []
    if env == "prod":
        views = ["bel_master_view", "e_master_view", "mg_master_view", "v_master_view"]
    else:
        views = ["bel_master_view_dev", "e_master_view_dev", "mg_master_view_dev", "v_master_view_dev"]



    ts = []
    for v in  views:
        sqlQuery = "select {0} from {1} where {2}".format(select, v, where)
        ts.append(sqlQuery);

    qj = ';'.join(ts)
    #print(qj)
    merged = []
    for t in ts:
        merged += cursor.execute(t)
        if len(merged) > 0:
            return merged
    return merged

def findPressInfo(code, lot, year):
    global cursor
    global env

    fyear = year.replace("created_at", "Insp_Date")
    q = "select * from tb_fg_pressinfo_lotlist where ProductCode = '{0}' and LotNo = '{1}' and {2}" \
    .format(code, lot, fyear)

    cursor.execute(q)
    return cursor.fetchall()

def updateLength(lotno):
    global cursor
    global wb

    q = "select * from data_wb where lot_no = '{0}' and judgement='OK' order by row_no asc".format(lotno)
    #print(q);
    return cursor.execute(q)

def updateUb(lotno):
    global cursor
    global wb

    q = "select * from data_wb where lot_no = '{0}' and judgement='NG' order by row_no asc".format(lotno)
    #print(q);
    return cursor.execute(q)

def createExcel(codeno, filters):
    global cursor
    global wb
    global dateStyle

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

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

    if filters is not None:
        q = """select * from lot_summary_wb as lt
        inner join  tb_fg_pressinfo_lotlist as press
        on lt.lot_no = press.LotNo
        and replace(lt.code, '-', '') = press.ProductCode
        where code = '{0}' and  {1}
        order by press.Insp_Date asc
        """.format(codeString, filters)
    else:
        q = """select * from lot_summary_wb as lt
inner join  tb_fg_pressinfo_lotlist as press
on lt.lot_no = press.LotNo
and replace(lt.code, '-', '') = press.ProductCodes
where code = '{0}'
order by press.Insp_Date asc
         """.format(codeString)

    #print(q)
    cursor.execute(q)
    rows = cursor.fetchall()
    #print(rows)
    y = {}
    for r in rows:
        t = "{0}-{1}".format(r.created_at.year, r.created_at.month)

        if t in y:
            y[t].append(r)
        else:
            y[t] = []
            y[t].append(r)

    #print(y)

    for k, v in y.items():
        createWS(k,v, codeno, filters)
    #print(ws._charts)
    cust = findCustomer(codeno.replace("-", ""))
    ws["B2"] = cust[0].customer
    ws["B3"] = codeno.replace("-", "")
    ws["B4"] = cust[0].sizestring
    ws["B5"] = cust[0].spec

    bcode = ord('B')
    ccode = 2
    for idx, val in enumerate(rows):
        #print(idx)
        c = chr(bcode + idx)
        #print(val.lot_no)
        if  ( bcode + idx) > ord('Z'):
            r = bcode + idx - ord('Z')
            c = 'A' + chr(ord('A') + (r - 1))
            #print("c = "+c)

        ws.cell(column= ccode, row=45, value = val.lot_no)
        ms = findInMasterView('*', "PRO2 = '{0}'".format(val.lot_no))
        #pressInfo = findPressInfo(codeno.replace("-", ""),val.lot_no, filters)
        #print("master view")
        #print("ms")
        #print(ms)
        if ms:
            ws.cell(column = ccode, row=35, value = ms[0].MI24)
            #ws[c+'38'] = ms[0].STD_Balance
            ws.cell(column = ccode , row=37, value =  ms[0].PRO6)
            #ws[c+'44'] = ms[0].PRO5
            ws.cell(column = ccode, row = 43, value = ms[0].PRO10)
            ws.cell(column = ccode, row = 42, value = ms[0].PRO12)

        #if pressInfo:
        ws.cell(column = ccode , row=39, value = val.Press_UserName)
        ws.cell(column = ccode, row = 40, value = val.Press_McName)
        #print(type(pressInfo[0].Insp_Date))
        #ws[c+'46'].number_format = "Short Date"
        ws.cell(column = ccode, row = 46 , value = val.Insp_Date.date())
        #print(type(ws[c+'46'].value))
        #ws[c+'46'].style = dateStyle
        #ws[c+'46'].value = datetime.datetime(2010, 7, 21)

        #print(ws[c+'46'].number_format)

        #ws[c+'46']. = datetime.datetime
        ws.cell(column = ccode , row = 44, value = val.FG_Qty)

        dws = updateLength(val.lot_no)
        for j,v in enumerate(dws):
            ws.cell(column = ccode , row = 48+v.row_no , value  = v.weight)

        dws = updateUb(val.lot_no)
        for j,v in enumerate(dws):
            ws.cell(column = ccode , row = 171+v.row_no , value  = v.weight)

        ccode += 1

    ut = int(time.time())
    filename = "cp{0}.xlsx".format(codeno.replace("-", ""))
    outfile = './public/excel/'+filename
    wb.save(filename = outfile)
    print(filename)

if __name__ == "__main__":
    env = sys.argv[1]
    code = sys.argv[2]
    #print(len(sys.argv))

    df = None
    if len(sys.argv) == 4:
        filter = sys.argv[3]
        #import ast
        #df = ast.literal_eval(filter)
        #print(df['date'])
        df = filter

    global cnxn
    global cursor

    if env == "prod":
        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.0.253;DATABASE=OB2011DB;UID=user1;PWD=1234')
        cursor = cnxn.cursor()
    else:
        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=db;DATABASE=OB2011DB;UID=admin;PWD=1234')
        cursor = cnxn.cursor()
    #print("code is "+code)

    createExcel(code, df)