import mysql.connector
import os
import sys
import time
import datetime
# import xlwt
import xlsxwriter    

import vimsmail

def load_dotenv_simple(path):
    env = {}
    with open(path, "r", encoding="utf-8") as f:
        for raw in f:
            line = raw.strip()
            if not line or line.startswith("#"):
                continue
            if "=" not in line:
                continue
            key, val = line.split("=", 1)
            key = key.strip()
            val = val.strip()
            # remove optional surrounding quotes
            if (val.startswith('"') and val.endswith('"')) or (val.startswith("'") and val.endswith("'")):
                val = val[1:-1]
            env[key] = val
#            print("env=",key,val)
    return env

def strip_quotes(v):
    if not v:
        return v
    v = v.strip()
    if (v.startswith('"') and v.endswith('"')) or (v.startswith("'") and v.endswith("'")):
        return v[1:-1]
    return v

def getDataSheet1(cnx,book):
    sheet1 = book.add_worksheet("Total Data")

    for col in range(20):
        sheet1.set_column(col, col, 20)
    sheet1.set_column(6,6, None, fmt_date)
    sheet1.set_column(7,7, None, fmt_time)
    sheet1.set_column(19,19, None, fmt_date)
    sheet1.set_column(20,20, None, fmt_time)
    sheet1.write(0,0,"Order")
    sheet1.write(0,1,"Project")
    sheet1.write(0,2,"Actual Comp Date")	
    sheet1.write(0,3,"Planned Ship Date")	
    sheet1.write(0,4,"Actual Ship Date")	
    sheet1.write(0,5,"Status")  	
    sheet1.write(0,6,"Date")    	
    sheet1.write(0,7,"Time")    	
    sheet1.write(0,8,"Include") 	
    sheet1.write(0,9,"Position")	
    sheet1.write(0,10,"Part No.")	
    sheet1.write(0,11,"Description")	
    sheet1.write(0,12,"Quantity")	
    sheet1.write(0,13,"Date/Time")	
    sheet1.write(0,14,"Warehouse")	
    sheet1.write(0,15,"Type")	
    sheet1.write(0,16,"Comments")	
    sheet1.write(0,17,"Order+Pos.")	
    sheet1.write(0,18,"Shortage")	
    sheet1.write(0,19,"Build Date")	
    sheet1.write(0,20,"Build Time")

    try:
        dataQuery = """
                    SELECT pick_plan.customer_reference,
                    pick_plan.project,
                    daytgt_daytarget.act_comp,
                    daytgt_daytarget.plan_ship,
                    daytgt_daytarget.act_ship,
                    pick_plan.status,
                    DATE(pick_plan.production_date) AS date,TIME(pick_plan.production_date) as time,
                    CASE WHEN SUBSTR(customer_reference,1,1)='L' THEN 'no' ELSE 'yes' END AS include,
                    pick_plan.line_no AS pos,
                    pick_plan.part_number, part.part_description,
                    pick_plan.qty_expected AS qty,
                    '' AS date_time,
                    '' AS warehouse,
                    pick_plan.transaction_type AS type,
                    '' AS comment,
                    CONCAT(pick_plan.customer_reference,pick_plan.line_no) AS order_pos,
                    CASE WHEN daytgt_short.id IS null then '' ELSE 'shortage' END AS shortage,
                    DATE(pick_plan.build_date) AS build_date, TIME(pick_plan.build_date) AS build_time
                    FROM pick_plan
                    LEFT JOIN daytgt_daytarget ON pick_plan.customer_reference=daytgt_daytarget.ORD_NO
                    LEFT JOIN part ON pick_plan.part_number=part.part_number
                    LEFT JOIN daytgt_short ON pick_plan.customer_reference=daytgt_short.p1order AND pick_plan.line_no=daytgt_short.p1pos
                    WHERE pick_plan.production_date < DATE(now())
                    AND SUBSTR(pick_plan.customer_reference,1,1) <> 'X'
                    AND SUBSTR(pick_plan.customer_reference,1,1) <> 'L'
                    ORDER BY pick_plan.customer_reference,pick_plan.project         
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        r = 1
        for row in data:
            c = 0
            i = 0
            for col in row:
                sheet1.write(r,c,col)
                c=c+1
            r = r+1
#           if (r>65000):
#               break
    except Exception as Err:
        print ('MySql Error',Err)
        
    finally:
        try:
            cursor.close()
        except:
            pass

def getDataSheet2(cnx,book):
    sheet2 = book.add_worksheet("Order Status")

    for col in range(80):
        sheet2.set_column(col, col, 20)
    sheet2.set_column(3,3, None, fmt_date)
    sheet2.set_column(4,4, None, fmt_time)
    sheet2.set_column(5,5, None, fmt_date)
    sheet2.set_column(6,6, None, fmt_time)
    sheet2.write(0,0,"Order")
    sheet2.write(0,1,"Project")
    sheet2.write(0,2,"Status")
    sheet2.write(0,3,"Date")    	
    sheet2.write(0,4,"Time") 	
    sheet2.write(0,5,"Sale Date")	
    sheet2.write(0,6,"Sale Time")	
    sheet2.write(0,7,"Model")	
    sheet2.write(0,8,"Yes/No")

    try:
        dataQuery = """
                    SELECT customer_reference,
                    project,
                    MAX(status) AS status,
                    MAX(DATE(production_date)) AS production_date,
                    MAX(TIME(production_date)) AS production_time,
                    CASE WHEN MAX(sale_date) IS NOT null AND MAX(DATE(sale_date)) <> '0001-01-01' THEN MAX(DATE(sale_date)) ELSE DATE(now()) END AS sale_date,
                    CASE WHEN MAX(sale_date) IS NOT null AND MAX(DATE(sale_date)) <> '0001-01-01' THEN MAX(TIME(sale_date)) ELSE null END AS sale_time,
                    MAX(MODL) AS model,
                    CASE WHEN MAX(MODL) IS null then 'no' else 'yes' END AS yesno
                    FROM pick_plan
                    LEFT JOIN daytgt_daytarget ON pick_plan.customer_reference=ORD_NO
                    WHERE production_date < now()
                    GROUP BY pick_plan.customer_reference, pick_plan.project
                    ORDER BY pick_plan.customer_reference,pick_plan.project                 
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        r = 1
        for row in data:
            c = 0
            i = 0
            for col in row:
                sheet2.write(r,c,col)
                c=c+1
#            sheet2.write_formula(r,7,"=VLOOKUP(B2,'file:///J:/Assembly Schedules/30 Sales Per day/Month End Activities/Month End Status/Current month download.xls'#$mse.$A$1:$B$65535,2,FALSE())")
#            sheet2.write_formula(r,8,"=IF($H2>0,'YES','NO')")
            r = r+1
#           if (r>65000):
#               break
    except Exception as Err:
        print ('MySql Error',Err)
        
    finally:
        try:
            cursor.close()
        except:
            pass

def getDataSheet3(cnx,book):
    sheet3 = book.add_worksheet("data")

    for col in range(80):
        sheet3.set_column(col, col, 20)
    
    sheet3.write(0,0,"To Issue");
    sheet3.write(0,2,"To RTS");
    sheet3.write(0,4,"Backflush");
    sheet3.write(0,6,"Shortage");

    try:
        dataQuery = """
                    SELECT 
                    pick_plan.customer_reference,
                    SUM(CASE 
                    WHEN pick_plan.transaction_type = 'issue' AND part.part_description LIKE('%DLF%') THEN 1
                    WHEN pick_plan.transaction_type = 'issue' AND part.part_description LIKE('%CEMENT%') THEN 1
                    ELSE 0 END) AS issueBackflush,
                    SUM(CASE
                    WHEN pick_plan.transaction_type = 'receipt' AND part.part_description LIKE('%DLF%') THEN 1
                    WHEN pick_plan.transaction_type = 'receipt' AND part.part_description LIKE('%CEMENT%') THEN 1
                    ELSE 0 END) AS receiptBackflush,
                    SUM(CASE WHEN pick_plan.transaction_type = 'issue' AND daytgt_short.id IS NOT null then 1 ELSE 0 END) AS issueShortage,
                    SUM(CASE WHEN pick_plan.transaction_type = 'receipt' AND daytgt_short.id IS NOT null then 1 ELSE 0 END) AS receiptShortage,
                    SUM(CASE WHEN pick_plan.transaction_type = 'issue' THEN 1 ELSE 0 END) AS issue,
                    SUM(CASE WHEN pick_plan.transaction_type = 'receipt' THEN 1 ELSE 0 END) AS receipt
                    FROM pick_plan
                    LEFT JOIN part ON pick_plan.part_number=part.part_number
                    LEFT JOIN daytgt_short ON
                    pick_plan.customer_reference=p1order AND  pick_plan.part_number = daytgt_short.p1pno
                    WHERE pick_plan.production_date < DATE(now())
                    GROUP BY pick_plan.customer_reference
                    ORDER BY pick_plan.customer_reference              
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)

        data = cursor.fetchall()
        customer_reference = []
        issue = []
        receipt = []
        backflush = []
        shortage = []

        for r in data:
            customer_reference.append(r[0])
            backflush.append(r[1]+r[2])
            shortage.append(r[3]+r[4])
            issue.append(r[5]-(r[1]+r[3]))
            receipt.append(r[6]-(r[2]+r[4]))

    except Exception as Err:
        print ('MySql Error',Err)
        
    finally:
        try:
            cursor.close()
        except:
            pass

    r = 0
    for i in range(len(customer_reference)):
        if issue[i] > 0:
            r=r+1
            sheet3.write(r, 0, customer_reference[i])
            sheet3.write(r, 1, issue[i])
    r = 0
    for i in range(len(customer_reference)):
        if receipt[i] > 0:
            r=r+1
            sheet3.write(r, 2, customer_reference[i])
            sheet3.write(r, 3, receipt[i])
    r = 0
    for i in range(len(customer_reference)):
        if backflush[i] > 0:
            r=r+1
            sheet3.write(r, 4, customer_reference[i])
            sheet3.write(r, 5, backflush[i])
    r = 0
    for i in range(len(customer_reference)):
        if shortage[i] > 0:
            r=r+1
            sheet3.write(r, 6, customer_reference[i])
            sheet3.write(r, 7, shortage[i])

def getDataSheet4(cnx,book):
    sheet4 = book.add_worksheet("Summary")

    sheet4.set_column(2,2, None, fmt_date)
    for col in range(80):
        sheet4.set_column(col, col, 20)
    
    sheet4.write(0,0,"Order")
    sheet4.write(0,1,"Order Status")	
    sheet4.write(0,2,"Planned Completion")	
    sheet4.write(0,3,"Lines To Issue")	
    sheet4.write(0,4,"Lines To RTS")	
    sheet4.write(0,5,"Backflush Parts")	
    sheet4.write(0,6,"Shortage Parts")	
    sheet4.write(0,7,"Total Lines")

    try:
        dataQuery = """
                    SELECT 
                    pick_plan.customer_reference,
                    MAX(pick_plan.status),
                    MAX(DATE(pick_plan.production_date)),
                    SUM(CASE WHEN pick_plan.transaction_type = 'issue' THEN 1 ELSE 0 END) AS issue,
                    SUM(CASE WHEN pick_plan.transaction_type = 'receipt' THEN 1 ELSE 0 END) AS receipt,
                    SUM(CASE 
                    WHEN pick_plan.transaction_type = 'issue' AND part.part_description LIKE('%DLF%') THEN 1
                    WHEN pick_plan.transaction_type = 'issue' AND part.part_description LIKE('%CEMENT%') THEN 1
                    ELSE 0 END) AS issueBackflush,
                    SUM(CASE
                    WHEN pick_plan.transaction_type = 'receipt' AND part.part_description LIKE('%DLF%') THEN 1
                    WHEN pick_plan.transaction_type = 'receipt' AND part.part_description LIKE('%CEMENT%') THEN 1
                    ELSE 0 END) AS receiptBackflush,
                    SUM(CASE WHEN pick_plan.transaction_type = 'issue' AND daytgt_short.id IS NOT null then 1 ELSE 0 END) AS issueShortage,
                    SUM(CASE WHEN pick_plan.transaction_type = 'receipt' AND daytgt_short.id IS NOT null then 1 ELSE 0 END) AS receiptShortage
                    FROM pick_plan
                    LEFT JOIN part ON pick_plan.part_number=part.part_number
                    LEFT JOIN daytgt_short ON
                    pick_plan.customer_reference=p1order AND  pick_plan.part_number = daytgt_short.p1pno
                    WHERE pick_plan.production_date < DATE(now())
                    GROUP BY pick_plan.customer_reference
                    ORDER BY pick_plan.customer_reference              
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()

        r = 1
        for row in data:
            sheet4.write(r,0,row[0])
            sheet4.write(r,1,row[1])
            sheet4.write(r,2,row[2])
            sheet4.write(r,3,row[3]-(row[5]+row[7]))
            sheet4.write(r,4,row[4]-(row[6]+row[8]))
            sheet4.write(r,5,row[5]+row[6])
            sheet4.write(r,6,row[7]+row[8])
            sheet4.write_formula(r,7,"=SUM(d"+str(r+1)+"..g"+str(r+1)+")")
            r = r+1
            

    except Exception as Err:
        print ('MySql Error',Err)
        
    finally:
        try:
            cursor.close()
        except:
            pass

def getDataSheet5(cnx,book):
    sheet5 = book.add_worksheet("Reject")

    for col in range(20):
        sheet5.set_column(col, col, 20)
    sheet5.set_column(4,4, None, fmt_date)

    sheet5.write(0,0,"Order")
    sheet5.write(0,1,"Part")
    sheet5.write(0,2,"Description")
    sheet5.write(0,3,"Qty.")
    sheet5.write(0,4,"Reject Date")
    sheet5.write(0,5,"Reason")
    sheet5.write(0,6,"Team Leader")
    sheet5.write(0,7,"To")
    sheet5.write(0,8,"Model")
    sheet5.write(0,9,"Serial")
    sheet5.write(0,10,"Status")

    try:
        dataQuery = """
                    SELECT pick_plan.customer_reference,
                    pick_plan.part_number, 
                    part.part_description,
                    pick_plan.qty_expected AS qty,
                    DATE(pick_plan.production_date) AS date,
                    '' AS reason,
                    '' AS team_leader,
                    pick_plan.zone_destination,
                    daytgt_daytarget.modl AS model,
                    daytgt_daytarget.serl AS serial,
                    pick_plan.status
                    FROM pick_plan
                    LEFT JOIN daytgt_daytarget ON pick_plan.customer_reference=daytgt_daytarget.ORD_NO
                    LEFT JOIN part ON pick_plan.part_number=part.part_number
                    LEFT JOIN daytgt_short ON pick_plan.customer_reference=daytgt_short.p1order AND pick_plan.line_no=daytgt_short.p1pos
                    WHERE pick_plan.production_date < DATE(now())
                    AND pick_plan.transaction_type='receipt'
                    ORDER BY pick_plan.customer_reference,pick_plan.project         
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        r = 1
        for row in data:
            c = 0
            i = 0
            for col in row:
                sheet5.write(r,c,col)
                c=c+1
            r = r+1
#           if (r>65000):
#               break
    except Exception as Err:
        print ('MySql Error',Err)
        
    finally:
        try:
            cursor.close()
        except:
            pass

def getDataSheet6(cnx,book):
    sheet6 = book.add_worksheet("Running Total")

    for col in range(20):
        sheet6.set_column(col, col, 25)
    sheet6.set_column(0,0, None, fmt_date)

    sheet6.write(0,0,"Date")
    sheet6.write(0,1,"Order Status")
    sheet6.write(0,2,"No. Orders")
    sheet6.write(0,3,"Issue Lines")
    sheet6.write(0,4,"RTS Lines")
    sheet6.write(0,5,"Backflush Lines")
    sheet6.write(0,6,"Shortage Lines")
    sheet6.write(0,7,"Total")

    try:
        dataQuery = """
                    WITH agg AS (
                    SELECT
                    SUBSTR(pick_plan.production_date,1,10) AS production_date,
                    pick_plan.status,
                    COUNT(DISTINCT customer_reference) AS orderCount,
                    SUM(CASE 
                    WHEN pick_plan.transaction_type = 'issue' 
                        AND part.part_description LIKE('%DLF%') THEN 1
                    WHEN pick_plan.transaction_type = 'issue' 
                        AND part.part_description LIKE('%CEMENT%') THEN 1
                    ELSE 0 
                    END) AS issueBackflush,
                    SUM(CASE
                    WHEN pick_plan.transaction_type = 'receipt' 
                        AND part.part_description LIKE('%DLF%') THEN 1
                    WHEN pick_plan.transaction_type = 'receipt' 
                        AND part.part_description LIKE('%CEMENT%') THEN 1
                    ELSE 0 
                    END) AS receiptBackflush,
                    SUM(CASE 
                    WHEN pick_plan.transaction_type = 'issue' 
                        AND daytgt_short.id IS NOT NULL THEN 1 
                    ELSE 0 
                    END) AS issueShortage,
                    SUM(CASE 
                    WHEN pick_plan.transaction_type = 'receipt' 
                        AND daytgt_short.id IS NOT NULL THEN 1 
                    ELSE 0 
                    END) AS receiptShortage,
                    SUM(CASE WHEN pick_plan.transaction_type = 'issue' THEN 1 ELSE 0 END) AS issue,
                    SUM(CASE WHEN pick_plan.transaction_type = 'receipt' THEN 1 ELSE 0 END) AS receipt
                FROM pick_plan
                LEFT JOIN part 
                    ON pick_plan.part_number = part.part_number
                LEFT JOIN daytgt_short
                    ON pick_plan.customer_reference = p1order
                AND pick_plan.part_number = daytgt_short.p1pno
                WHERE pick_plan.production_date < DATE(NOW())
                GROUP BY DATE(pick_plan.production_date),pick_plan.status
                ORDER BY DATE(pick_plan.production_date),pick_plan.status
                )
                SELECT
                production_date,
                status,
                orderCount,
                issueBackflush,
                receiptBackflush,
                issueShortage,
                receiptShortage,
                issue,
                receipt,
                (issue - issueBackflush - issueShortage) AS issueNet,
                (receipt - receiptBackflush - receiptShortage) AS receiptNet
                FROM agg
                ORDER BY production_date,status;
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()

        r = 0
        orderSum=0
        issueSum=0
        receiptSum=0
        shortSum=0
        backflushSum=0
        actorderSum=0
        actissueSum=0
        actreceiptSum=0
        actshortSum=0
        actbackflushSum=0
        cloorderSum=0
        cloissueSum=0
        cloreceiptSum=0
        closhortSum=0
        clobackflushSum=0
        comorderSum=0
        comissueSum=0
        comreceiptSum=0
        comshortSum=0
        combackflushSum=0
        plaorderSum=0
        plaissueSum=0
        plareceiptSum=0
        plashortSum=0
        plabackflushSum=0
        relorderSum=0
        relissueSum=0
        relreceiptSum=0
        relshortSum=0
        relbackflushSum=0
        toborderSum=0
        tobissueSum=0
        tobreceiptSum=0
        tobshortSum=0
        tobbackflushSum=0

        prvDate=None
       
        for row in data:
            if prvDate == None:
                prvDate = row[0]

            if prvDate != row[0]:
                r=r+1
                sheet6.write(r,0,prvDate)
                sheet6.write(r,1,'')
                sheet6.write(r,2,orderSum)
                sheet6.write(r,3,issueSum)
                sheet6.write(r,4,receiptSum)
                sheet6.write(r,5,shortSum)
                sheet6.write(r,6,backflushSum)
                sheet6.write(r,7,orderSum+issueSum+receiptSum+shortSum+backflushSum)
            
                r=r+1
                sheet6.write(r,0,prvDate)
                sheet6.write(r,1,'Active')
                sheet6.write(r,2,actorderSum)
                sheet6.write(r,3,actissueSum)
                sheet6.write(r,4,actreceiptSum)
                sheet6.write(r,5,actshortSum)
                sheet6.write(r,6,actbackflushSum)
                sheet6.write(r,7,actorderSum+actissueSum+actreceiptSum+actshortSum+actbackflushSum)
            
                r=r+1
                sheet6.write(r,0,prvDate)
                sheet6.write(r,1,'Closed')
                sheet6.write(r,2,cloorderSum)
                sheet6.write(r,3,cloissueSum)
                sheet6.write(r,4,cloreceiptSum)
                sheet6.write(r,5,closhortSum)
                sheet6.write(r,6,clobackflushSum)
                sheet6.write(r,7,cloorderSum+cloissueSum+cloreceiptSum+closhortSum+clobackflushSum)
            
                r=r+1
                sheet6.write(r,0,prvDate)
                sheet6.write(r,1,'Completed')
                sheet6.write(r,2,comorderSum)
                sheet6.write(r,3,comissueSum)
                sheet6.write(r,4,comreceiptSum)
                sheet6.write(r,5,comshortSum)
                sheet6.write(r,6,combackflushSum)
                sheet6.write(r,7,comorderSum+comissueSum+comreceiptSum+comshortSum+combackflushSum)
            
                r=r+1
                sheet6.write(r,0,prvDate)
                sheet6.write(r,1,'Planned')
                sheet6.write(r,2,plaorderSum)
                sheet6.write(r,3,plaissueSum)
                sheet6.write(r,4,plareceiptSum)
                sheet6.write(r,5,plashortSum)
                sheet6.write(r,6,plabackflushSum)
                sheet6.write(r,7,plaorderSum+plaissueSum+plareceiptSum+plashortSum+plabackflushSum)
            
                r=r+1
                sheet6.write(r,0,prvDate)
                sheet6.write(r,1,'Released')
                sheet6.write(r,2,relorderSum)
                sheet6.write(r,3,relissueSum)
                sheet6.write(r,4,relreceiptSum)
                sheet6.write(r,5,relshortSum)
                sheet6.write(r,6,relbackflushSum)
                sheet6.write(r,7,relorderSum+relissueSum+relreceiptSum+relshortSum+relbackflushSum)
            
                r=r+1
                sheet6.write(r,0,prvDate)
                sheet6.write(r,1,'To be Completed')
                sheet6.write(r,2,toborderSum)
                sheet6.write(r,3,tobissueSum)
                sheet6.write(r,4,tobreceiptSum)
                sheet6.write(r,5,tobshortSum)
                sheet6.write(r,6,tobbackflushSum)
                sheet6.write(r,7,toborderSum+tobissueSum+tobreceiptSum+tobshortSum+tobbackflushSum)

            prvDate = row[0]

            if (row[1] == ''):
                orderSum=orderSum+row[2]
                issueSum=issueSum+row[9]
                receiptSum=receiptSum+row[10]
                shortSum=shortSum+row[3]+row[5]
                backflushSum=backflushSum+row[4]+row[6]
            if (row[1] == 'Active'):
                actorderSum=actorderSum+row[2]
                actissueSum=actissueSum+row[9]
                actreceiptSum=actreceiptSum+row[10]
                actshortSum=actshortSum+row[3]+row[5]
                actbackflushSum=actbackflushSum+row[4]+row[6]
            if (row[1] == 'Closed'):
                cloorderSum=cloorderSum+row[2]
                cloissueSum=cloissueSum+row[9]
                cloreceiptSum=cloreceiptSum+row[10]
                closhortSum=closhortSum+row[3]+row[5]
                clobackflushSum=clobackflushSum+row[4]+row[6]
            if (row[1] == 'Completed'):
                comorderSum=comorderSum+row[2]
                comissueSum=comissueSum+row[9]
                comreceiptSum=comreceiptSum+row[10]
                comshortSum=comshortSum+row[3]+row[5]
                combackflushSum=combackflushSum+row[4]+row[6]
            if (row[1] == 'Planned'):
                plaorderSum=plaorderSum+row[2]
                plaissueSum=plaissueSum+row[9]
                plareceiptSum=plareceiptSum+row[10]
                plashortSum=plashortSum+row[3]+row[5]
                plabackflushSum=plabackflushSum+row[4]+row[6]
            if (row[1] == 'Released'):
                relorderSum=relorderSum+row[2]
                relissueSum=relissueSum+row[9]
                relreceiptSum=relreceiptSum+row[10]
                relshortSum=relshortSum+row[3]+row[5]
                relbackflushSum=relbackflushSum+row[4]+row[6]
            if (row[1] == 'To be Completed'):
                toborderSum=toborderSum+row[2]
                tobissueSum=tobissueSum+row[9]
                tobreceiptSum=tobreceiptSum+row[10]
                tobshortSum=tobshortSum+row[3]+row[5]
                tobbackflushSum=tobbackflushSum+row[4]+row[6]

#               if (r>65000):
#                   break
        r=r+1
        sheet6.write(r,0,prvDate)
        sheet6.write(r,1,'')
        sheet6.write(r,2,orderSum)
        sheet6.write(r,3,issueSum)
        sheet6.write(r,4,receiptSum)
        sheet6.write(r,5,shortSum)
        sheet6.write(r,6,backflushSum)
        sheet6.write(r,7,orderSum+issueSum+receiptSum+shortSum+backflushSum)
    
        r=r+1
        sheet6.write(r,0,prvDate)
        sheet6.write(r,1,'Active')
        sheet6.write(r,2,actorderSum)
        sheet6.write(r,3,actissueSum)
        sheet6.write(r,4,actreceiptSum)
        sheet6.write(r,5,actshortSum)
        sheet6.write(r,6,actbackflushSum)
        sheet6.write(r,7,actorderSum+actissueSum+actreceiptSum+actshortSum+actbackflushSum)
    
        r=r+1
        sheet6.write(r,0,prvDate)
        sheet6.write(r,1,'Closed')
        sheet6.write(r,2,cloorderSum)
        sheet6.write(r,3,cloissueSum)
        sheet6.write(r,4,cloreceiptSum)
        sheet6.write(r,5,closhortSum)
        sheet6.write(r,6,clobackflushSum)
        sheet6.write(r,7,cloorderSum+cloissueSum+cloreceiptSum+closhortSum+clobackflushSum)
    
        r=r+1
        sheet6.write(r,0,prvDate)
        sheet6.write(r,1,'Completed')
        sheet6.write(r,2,comorderSum)
        sheet6.write(r,3,comissueSum)
        sheet6.write(r,4,comreceiptSum)
        sheet6.write(r,5,comshortSum)
        sheet6.write(r,6,combackflushSum)
        sheet6.write(r,7,comorderSum+comissueSum+comreceiptSum+comshortSum+combackflushSum)
    
        r=r+1
        sheet6.write(r,0,prvDate)
        sheet6.write(r,1,'Planned')
        sheet6.write(r,2,plaorderSum)
        sheet6.write(r,3,plaissueSum)
        sheet6.write(r,4,plareceiptSum)
        sheet6.write(r,5,plashortSum)
        sheet6.write(r,6,plabackflushSum)
        sheet6.write(r,7,plaorderSum+plaissueSum+plareceiptSum+plashortSum+plabackflushSum)
    
        r=r+1
        sheet6.write(r,0,prvDate)
        sheet6.write(r,1,'Released')
        sheet6.write(r,2,relorderSum)
        sheet6.write(r,3,relissueSum)
        sheet6.write(r,4,relreceiptSum)
        sheet6.write(r,5,relshortSum)
        sheet6.write(r,6,relbackflushSum)
        sheet6.write(r,7,relorderSum+relissueSum+relreceiptSum+relshortSum+relbackflushSum)
    
        r=r+1
        sheet6.write(r,0,prvDate)
        sheet6.write(r,1,'To be Completed')
        sheet6.write(r,2,toborderSum)
        sheet6.write(r,3,tobissueSum)
        sheet6.write(r,4,tobreceiptSum)
        sheet6.write(r,5,tobshortSum)
        sheet6.write(r,6,tobbackflushSum)
        sheet6.write(r,7,toborderSum+tobissueSum+tobreceiptSum+tobshortSum+tobbackflushSum)


    except Exception as Err:
        print ('MySql Error',Err)
        
    finally:
        try:
            cursor.close()
        except:
            pass


def getDataSheet7(cnx,book):
    try:
        dataQuery = """
                    WITH agg AS (
                    SELECT
                    SUBSTR(pick_plan.production_date,1,10) AS production_date,
                    COUNT(DISTINCT customer_reference) AS orderCount,
                    SUM(CASE 
                    WHEN pick_plan.transaction_type = 'issue' 
                        AND part.part_description LIKE('%DLF%') THEN 1
                    WHEN pick_plan.transaction_type = 'issue' 
                        AND part.part_description LIKE('%CEMENT%') THEN 1
                    ELSE 0 
                    END) AS issueBackflush,
                    SUM(CASE
                    WHEN pick_plan.transaction_type = 'receipt' 
                        AND part.part_description LIKE('%DLF%') THEN 1
                    WHEN pick_plan.transaction_type = 'receipt' 
                        AND part.part_description LIKE('%CEMENT%') THEN 1
                    ELSE 0 
                    END) AS receiptBackflush,
                    SUM(CASE 
                    WHEN pick_plan.transaction_type = 'issue' 
                        AND daytgt_short.id IS NOT NULL THEN 1 
                    ELSE 0 
                    END) AS issueShortage,
                    SUM(CASE 
                    WHEN pick_plan.transaction_type = 'receipt' 
                        AND daytgt_short.id IS NOT NULL THEN 1 
                    ELSE 0 
                    END) AS receiptShortage,
                    SUM(CASE WHEN pick_plan.transaction_type = 'issue' THEN 1 ELSE 0 END) AS issue,
                    SUM(CASE WHEN pick_plan.transaction_type = 'receipt' THEN 1 ELSE 0 END) AS receipt
                FROM pick_plan
                LEFT JOIN part 
                    ON pick_plan.part_number = part.part_number
                LEFT JOIN daytgt_short
                    ON pick_plan.customer_reference = p1order
                AND pick_plan.part_number = daytgt_short.p1pno
                WHERE pick_plan.production_date < DATE(NOW())
                GROUP BY DATE(pick_plan.production_date)
                ORDER BY DATE(pick_plan.production_date)
                )
                SELECT
                production_date,
                orderCount,
                issueBackflush,
                receiptBackflush,
                issueShortage,
                receiptShortage,
                issue,
                receipt,
                (issue - issueBackflush - issueShortage) AS issueNet,
                (receipt - receiptBackflush - receiptShortage) AS receiptNet
                FROM agg
                ORDER BY production_date;
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()

        series_names = ["Order Lines", "Issue Lines", "RTS Lines", "Shortage Lines", "Backflush Lines"]
        sheet7 = book.add_worksheet("Running Total Chart")
        for col in range(20):
            sheet7.set_column(col, col, 12)
        sheet7.write(0,0,"Date")
        sheet7.write(0,2,series_names[0])
        sheet7.write(0,3,series_names[1])
        sheet7.write(0,4,series_names[2])
        sheet7.write(0,5,series_names[3])
        sheet7.write(0,6,series_names[4])

        r = 0
        orderSum=0
        issueSum=0
        receiptSum=0
        shortSum=0
        backflushSum=0

        prvDate = None

        for row in data:
            if prvDate == None:
                prvDate = row[0]

            if prvDate != row[0]:
                r=r+1
                d = datetime.datetime.strptime(prvDate, "%Y-%m-%d")
                sheet7.write_datetime(r, 0, d, fmt_date)
                sheet7.write(r, 1, d.strftime("%Y-%m-%d"))
                sheet7.write(r,2,orderSum)
                sheet7.write(r,3,issueSum)
                sheet7.write(r,4,receiptSum)
                sheet7.write(r,5,shortSum)
                sheet7.write(r,6,backflushSum)

            prvDate = row[0]

            orderSum=orderSum+row[1]
            issueSum=issueSum+row[8]
            receiptSum=receiptSum+row[9]
            shortSum=shortSum+row[2]+row[4]
            backflushSum=backflushSum+row[3]+row[5]

        r=r+1
        d = datetime.datetime.strptime(prvDate, "%Y-%m-%d")
        sheet7.write_datetime(r, 0, d, fmt_date)
        sheet7.write(r, 1, d.strftime("%Y-%m-%d"))
        sheet7.write(r,2,orderSum)
        sheet7.write(r,3,issueSum)
        sheet7.write(r,4,receiptSum)
        sheet7.write(r,5,shortSum)
        sheet7.write(r,6,backflushSum)

    except Exception as Err:
        print ('MySql Error',Err)
        
    finally:
        try:
            cursor.close()
        except:
            pass

# --- Build ranges ---
    first_row = 1
    last_row = r

# Categories = range (A2:A{n+1})
    categories = ["Running Total Chart", first_row, 1, last_row, 1]

# --- Create chart ---
    chart = book.add_chart({"type": "column", "subtype": "stacked"})
    chart.set_title({"name": "Issue by Date"})
    chart.set_x_axis({
    "name": "Date",
#    "date_axis": True,
#    "num_format": "YYYY-MM-DD",
    })
    chart.set_y_axis({"name": "Count"})

# Add each stacked series
    for c, name in enumerate(series_names, start=2):
        values = ["Running Total Chart", first_row, c, last_row, c]
        chart.add_series({
            "name":       name,
            "categories": categories,
            "values":     values,
        })

# legend position
    chart.set_legend({"position": "bottom"})

# Insert chart
    sheet7.insert_chart("I2", chart, {"x_scale": 1.5, "y_scale": 1.2})


if __name__ == '__main__':
# check for filter
    if (len(sys.argv)>1):
        filter = sys.argv[1]
    else:
        filter = ""
    reportpath = "f:/kukfiles/parts_to_issue/"
    sender = "vimsreports@vanteceurope.com"

    env = load_dotenv_simple('f:/webroot/kukupload/.env')
    db_host = env.get("DB_HOST") or env.get("db_host")
    db_name = env.get("DB_DATABASE") or env.get("db_database")
    db_user = env.get("DB_USERNAME") or env.get("db_username")
    db_pass = env.get("DB_PASSWORD") or env.get("db_password")
    db_port = env.get("DB_PORT")
    db_host = strip_quotes(db_host)
    db_name = strip_quotes(db_name)
    db_user = strip_quotes(db_user)
    db_pass = strip_quotes(db_pass)

    cnx = mysql.connector.connect(
        host=db_host,
        database=db_name,
        user=db_user,
        password=db_pass
#        port=db_port
    )


    tablename = "VIMS_partToIss"
    book = xlsxwriter.Workbook(reportpath+"/"+tablename+".xlsx")
    book.set_calc_mode('automatic')
    fmt_datetime = book.add_format({'num_format': 'yyyy/mm/dd hh:mm'})
    fmt_date = book.add_format({'num_format': 'YYYY-MM-DD'})
    fmt_time = book.add_format({'num_format': 'hh:mm'})
    fmt_int = book.add_format({"num_format": "0"})

    getDataSheet1(cnx,book)
    getDataSheet2(cnx,book)
    getDataSheet3(cnx,book)
    getDataSheet4(cnx,book)
    getDataSheet5(cnx,book)
    getDataSheet6(cnx,book)
    getDataSheet7(cnx,book)

    book.close()
    cnx.close()

#    recipients = vimsmail.getRecipients(defaults,"partstoissue")
    recipients = "geoff.thorpe@vanteceurope.com"
    vimsmail.createMail(reportpath, sender, recipients,"")