coi/app/report/dailyreport.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

# 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 = './dctemplate2.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 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)
    return merged

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 getMachineName(machineId):
    global cursor
    q = "select * from machines_wb where id = {0}".format(machineId)
    #print(q)
    cursor.execute(q)
    rs = cursor.fetchall()
    return rs[0].name

def getMachineDetail(machineId):
    global cursor
    q = "select * from machines_wb where id = {0}".format(machineId)
    #print(q)
    cursor.execute(q)
    rs = cursor.fetchall()
    return rs[0]

def createDayChart(chartTitle):
    global wb
    ws = wb['Chart']
    wsd = wb['Daily Check']
    c1 = LineChart()
    c1.legend.position = "t";
    c1.title = chartTitle
    c1.title.font = Font(underline=Font.UNDERLINE_SINGLE)
    c1.y_axis.title = "Std. Weight(g)"
    c1.y_axis.number_format = "#,##0.00"
    c1.x_axis.title  = "Date"
    c1.width = 30
    c1.height = 10


    #print(len(values))
    MINDATACOL = 2
    MAXDATACOL = 2 + 31
    lt = Reference(wsd, min_col=3, max_col=33, min_row=5, max_row=5)

    std = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=6, max_row=6)
    data = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=10, max_row=10)

    maxUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=15, max_row=15)
    minUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=21, max_row=21)
    avgUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=26, max_row=26)
    vsStd = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=32, max_row=32)
    vsStd2 = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=37, max_row=37)


    c1.add_data(data, from_rows=True, titles_from_data=True)
    c1.add_data(maxUB, 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(vsStd2, from_rows=True, titles_from_data=True)
    c1.add_data(std, 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[3]
    s1.marker.symbol = "auto"
    s1.graphicalProperties.line.noFill = True

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

    s1 = c1.series[5]
    s1.marker.symbol = "auto"
    s1.graphicalProperties.line.noFill = True

    s1 = c1.series[6]
    #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, "b3")
    #pass
def createNightChart(chartTitle):
    global wb
    ws = wb['Chart']
    wsd = wb['Daily Check']
    c1 = LineChart()
    c1.legend.position = "t";
    c1.title = chartTitle
    c1.title.font = Font(underline=Font.UNDERLINE_SINGLE)
    c1.y_axis.title = "Std. Weight(g)"
    c1.y_axis.number_format = "#,##0.00"
    c1.x_axis.title  = "Date"
    c1.width = 30
    c1.height = 10


    #print(len(values))
    MINDATACOL = 2
    MAXDATACOL = 2 + 31
    lt = Reference(wsd, min_col=3, max_col=33, min_row=5, max_row=5)

    std = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=6, max_row=6)
    #begin data
    data = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=51, max_row=51)

    maxUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=56, max_row=56)
    minUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=62, max_row=62)
    avgUB = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=67, max_row=67)
    vsStd = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=73, max_row=73)
    vsStd2 = Reference(wsd, min_col=MINDATACOL, max_col=MAXDATACOL, min_row=78, max_row=78)


    c1.add_data(data, from_rows=True, titles_from_data=True)
    c1.add_data(maxUB, 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(vsStd2, from_rows=True, titles_from_data=True)
    c1.add_data(std, 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[3]
    s1.marker.symbol = "auto"
    s1.graphicalProperties.line.noFill = True

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

    s1 = c1.series[5]
    s1.marker.symbol = "auto"
    s1.graphicalProperties.line.noFill = True

    s1 = c1.series[6]
    #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, "b22")

def createExcel(codeno, filters):
    global cursor
    global wb
    ws  = wb["Daily Check"]


    machineName = getMachineName(codeno)
    machineDetail = getMachineDetail(codeno)

    ws["C2"].value = machineName
    ws["C3"].value = filters.replace("created_at between", "").replace("and", "to").replace("'", "")

    ws["C43"].value = machineName
    ws["C44"].value = filters.replace("created_at between", "").replace("and", "to").replace("'", "")

    ws["C4"].value = machineDetail.machineStd;

    if filters is not None:
        q = "select * from daily_checks_wb where machine_id = '{0}' and  {1}".format(codeno, filters)
    else:
        q = "select * from daily_checks_wb where machine_id = '{0}'".format(codeno)


    #print(q)
    cursor.execute(q)
    rows = cursor.fetchall()
    #print(rows)
    a0740 = [];
    a1230 = []
    a1700 = []
    a0030 = []
    a1940 = []
    a0500 = []

    for i in range(31):
        a0740.append([])
        a1230.append([])
        a1700.append([])
        a0030.append([])
        a1940.append([])
        a0500.append([])


    for r in rows:
        i = r.created_at.day
        if r.shift == "07.40":
            a0740[i-1].append(r)
        if r.shift == "12.30":
            a1230[i-1].append(r)
        if r.shift == "17.00":
            #a1700.append(r)
            a1700[i-1].append(r)
        if r.shift == "00.30":
            a0030[i-1].append(r)
        if r.shift == "19.40":
            a1940[i-1].append(r)
        if r.shift == "05.00":
            a0500[i-1].append(r)


    #print(a0740)
    for idx, val in enumerate(a0740):
        j = 7
        k = 12
        users = []
        #print(val)
        ok = 0
        ng = 0
        for v in val:
            #print(j , idx + )
            #print(v.judgment)
            if v.judgement == "OK":
                ok += 1
                if ok > 3:
                    continue
                ws.cell(row = j , column = idx + 3 , value = v.weight)
                j += 1
            if v.judgement == "NG":
                ng += 1
                if ng > 3:
                    continue
                ws.cell(row = k , column = idx + 3 , value = v.weight)
                k += 1

            #print(v.empid)
            if v.empid not in users:
                #print("insert in users")
                users.append(v.empid)

        #print(val)
        if len(val) > 0:
            q = "select * from users where empid  in ({0})".format(",".join("'{0}'".format(w) for w in users))
            #print(q)
            cursor.execute(q)
            us = cursor.fetchall()
            names = [x.fname + " " + x.lname for x in us]
            ws.cell(row = 17, column = idx + 3, value = ",".join(names))

    for idx, val in enumerate(a1230):
        j = 18
        k = 23
        users = []
        ok = ng = 0
        for v in val:
            #print(j , idx)
            if v.judgement == "OK":
                ok += 1
                if ok > 3:
                    continue
                ws.cell(row = j , column = idx + 3 , value = v.weight)
                j += 1
            if v.judgement == "NG":
                ng += 1
                if ng > 3:
                    continue

                ws.cell(row = k , column = idx + 3 , value = v.weight)
                k += 1

            if v.empid not in users:
                users.append(v.empid)

        if len(val) > 0:
            q = "select * from users where empid  in ({0})".format(",".join("'{0}'".format(w) for w in users))
            #print(q)
            cursor.execute(q)
            us = cursor.fetchall()
            names = [x.fname + " " + x.lname for x in us]
            ws.cell(row = 28, column = idx + 3, value = ",".join(names))

    for idx, val in enumerate(a1700):
        j = 29
        k = 34
        users = []
        ok = ng = 0

        for v in val:
            #print(j , idx)
            if v.judgement == "OK":
                ok += 1
                if ok > 3:
                    continue

                ws.cell(row = j , column = idx + 3 , value = v.weight)
                j += 1
            if v.judgement == "NG":
                ng += 1
                if ng > 3:
                    continue

                ws.cell(row = k , column = idx + 3 , value = v.weight)
                k += 1
            if v.empid not in users:
                users.append(v.empid)

        if len(val) > 0:
            q = "select * from users where empid  in ({0})".format(",".join("'{0}'".format(w) for w in users))
            #print(q)
            cursor.execute(q)
            us = cursor.fetchall()
            names = [x.fname + " " + x.lname for x in us]
            ws.cell(row = 39, column = idx + 3, value = ",".join(names))

    for idx, val in enumerate(a1940):
        j = 47
        k = 53
        users = []
        ok = ng = 0
        for v in val:
            #print(j , idx)
            if v.judgement == "OK":
                ok += 1
                if ok > 3:
                    continue

                ws.cell(row = j , column = idx + 3 , value = v.weight)
                j += 1
            if v.judgement == "NG":
                ng += 1
                if ng > 3:
                    continue

                ws.cell(row = k , column = idx + 3 , value = v.weight)
                k += 1
            if v.empid not in users:
                users.append(v.empid)

        if len(val) > 0:
            q = "select * from users where empid  in ({0})".format(",".join("'{0}'".format(w) for w in users))

            #print(q)
            cursor.execute(q)
            us = cursor.fetchall()
            names = [x.fname + " " + x.lname for x in us]
            ws.cell(row = 58, column = idx + 3, value = ",".join(names))

    for idx, val in enumerate(a0030):
        j = 59
        k = 64
        users = []
        ok = ng = 0
        for v in val:
            #print(j , idx)
            if v.judgement == "OK":
                ok += 1
                if ok > 3:
                    continue

                ws.cell(row = j , column = idx + 3 , value = v.weight)
                j += 1
            if v.judgement == "NG":
                ng += 1
                if ng > 3:
                    continue

                ws.cell(row = k , column = idx + 3 , value = v.weight)
                k += 1
            if v.empid not in users:
                users.append(v.empid)

        if len(val) > 0:
            q = "select * from users where empid  in ({0})".format(",".join("'{0}'".format(w) for w in users))

            #print(q)
            cursor.execute(q)
            us = cursor.fetchall()
            names = [x.fname + " " + x.lname for x in us]
            ws.cell(row = 69, column = idx + 3, value = ",".join(names))

    for idx, val in enumerate(a0500):
        j = 70
        k = 75
        users = []
        ok = ng = 0
        for v in val:
            #print(j , idx)
            if v.judgement == "OK":
                ok += 1
                if ok > 3:
                    continue

                ws.cell(row = j , column = idx + 3 , value = v.weight)
                j += 1
            if v.judgement == "NG":
                ng += 1
                if ng > 3:
                    continue
                ws.cell(row = k , column = idx + 3 , value = v.weight)
                k += 1
            if v.empid not in users:
                users.append(v.empid)

        if len(val) > 0:
            q = "select * from users where empid  in ({0})".format(",".join("'{0}'".format(w) for w in users))
            #print(q)
            cursor.execute(q)
            us = cursor.fetchall()
            names = [x.fname + " " + x.lname for x in us]
            ws.cell(row = 80, column = idx + 3, value = ",".join(names))

    createDayChart("Daily Check of Day Shift ({0})".format(machineName))

    createNightChart("Daily Check of Night Shift ({0})".format(machineName))

    ut = int(time.time())
    filename = "dc{0}.xlsx".format(str(ut))
    outfile = './public/excel/'+filename
    wb.save(filename = outfile)
    print(filename)

if __name__ == "__main__":
    env = sys.argv[1]
    machine = 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(machine, df)