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("Sheet 1")

    for col in range(20):
        sheet1.set_column(col, col, 20)
    sheet1.set_column(0,0, None, fmt_date)
    sheet1.write(0,0,"Current Date")
    sheet1.write(0,1,"Model")
    sheet1.write(0,2,"Serial")
    sheet1.write(0,3,"Prod.Start Date")
    sheet1.write(0,4,"Prod.Start Time")
    sheet1.write(0,5,"Hard Short")
    sheet1.write(0,6,"Soft Short")
    sheet1.write(0,7,"AAA Outs.")
    try:
        dataQuery = """
                    SELECT DATE(now()) AS curr_date,MODL, SERL, 
                    PROD_START AS prod_date, PROD_STARTT AS prod_time,
                    HARD_SHORT, SOFT_SHORT,AAA_OUTS 
                    FROM daytgt_daytarget 
                    WHERE hard_short>0 OR soft_short>0 OR AAA_OUTS>0 
                    AND prod_start > current_date;            
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        r = 1
        for row in data:
            c = 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("Sheet 2")

    for col in range(80):
        sheet2.set_column(col, col, 20)
    sheet2.write(0,0,"Run No.")
    sheet2.write(0,1,"Model")
    sheet2.write(0,2,"Serial")
    sheet2.write(0,3,"Order")
    sheet2.write(0,4,"Position")
    sheet2.write(0,5,"Issue To")
    sheet2.write(0,6,"Part No.")
    sheet2.write(0,7,"Desc.")
    sheet2.write(0,8,"Shortage")
    sheet2.write(0,9,"Order")	
    sheet2.write(0,10,"On Hand")
    sheet2.write(0,11,"Inventory")
    sheet2.write(0,12,"On Hold")
    sheet2.write(0,13,"Allocated")
    sheet2.write(0,14,"Unavailable")
    sheet2.write(0,15,"Receipt")
    sheet2.write(0,16,"KTX")
    sheet2.write(0,17,"Insp.")
    sheet2.write(0,18,"In Trans")
    sheet2.write(0,19,"RTV")
    sheet2.write(0,20,"Quar.")
    sheet2.write(0,21,"RTS")
    sheet2.write(0,22,"AAARTS")
    sheet2.write(0,23,"Total")
    sheet2.write(0,24,"ASN")
    sheet2.write(0,25,"INHSEF01 TOTAL")
    sheet2.write(0,26,"INHSEM01 TOTAL")
    sheet2.write(0,27,"INHSEP01 TOTAL")
    sheet2.write(0,28,"INHSEA01 TOTAL")
    sheet2.write(0,29,"U3PR01 TOTAL")
    sheet2.write(0,30,"Vendor")
    sheet2.write(0,31,"Hard Shortage")
    sheet2.write(0,32,"Start")
    sheet2.write(0,33,"Build")
    sheet2.write(0,34,"Sale")
    sheet2.write(0,35,"Status")
    sheet2.write(0,36,"Project")
    sheet2.write(0,37,"Prod_YMD")
    sheet2.write(0,38,"Build_YMD")
    sheet2.write(0,39,"Sale_YMD")
    sheet2.write(0,40,"P1PNME")
    sheet2.write(0,41,"POT")
    sheet2.write(0,42,"MNL")
    sheet2.write(0,43,"UTC")
    sheet2.write(0,44,"Plannr")
    sheet2.write(0,45,"Item")
    sheet2.write(0,46,"P3_Desc.")
    sheet2.write(0,47,"BP Code")
    sheet2.write(0,48,"P3_Name")
    sheet2.write(0,49,"Ast")
    sheet2.write(0,50,"Prev.")
    sheet2.write(0,51,"Day 01")
    sheet2.write(0,52,"Day 02")
    sheet2.write(0,53,"Day 03")
    sheet2.write(0,54,"Day 04")
    sheet2.write(0,55,"Day 05")
    sheet2.write(0,56,"Day 06")
    sheet2.write(0,57,"Day 07")
    sheet2.write(0,58,"Day 08")
    sheet2.write(0,59,"Day 09")
    sheet2.write(0,60,"Day 10")
    sheet2.write(0,61,"P3_Order")
    sheet2.write(0,62,"P3_Pos")
    sheet2.write(0,63,"P3SEQ")
    sheet2.write(0,64,"P3_Qty")
    sheet2.write(0,65,"Due Date")
    sheet2.write(0,66,"Date")
    sheet2.write(0,67,"Pack")
    sheet2.write(0,68,"Promise")
    sheet2.write(0,69,"6Days")
    sheet2.write(0,70,"Comment")
    sheet2.write(0,71,"Liability")
    sheet2.write(0,72,"Confirmed")
    sheet2.write(0,73,"MODL")
    sheet2.write(0,74,"PROD_DATE")
    sheet2.write(0,75,"PROD_TIME")

    try:
        dataQuery = """
                    SELECT a.*,b.modl,b.prod_start AS   
                    prod_date,b.prod_startt AS prod_time FROM  
                    daytgt_short a LEFT JOIN                
                    daytgt_daytarget b ON                  
                    a.p1order=b.ord_no                  
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        r = 1
        for row in data:
            c = 0
            i = 0
            for col in row:
                if i == 0:
                    i=i+1
                    continue
                sheet2.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 getDataSheet3(cnx,book):
    sheet3 = book.add_worksheet("Sheet 3")

    for col in range(20):
        sheet3.set_column(col, col, 15)
    sheet3.set_column(10,11, None, fmt_date)
    sheet3.write(0,0,"Original Ref.")
    sheet3.write(0,1,"Pick Ref.")
    sheet3.write(0,2,"Location")
    sheet3.write(0,3,"Part Number")
    sheet3.write(0,4,"Qty")
    sheet3.write(0,5,"Order Number")
    sheet3.write(0,6,"Pos.")
    sheet3.write(0,7,"To Location")
    sheet3.write(0,8,"Serial")
    sheet3.write(0,9,"Model")	
    sheet3.write(0,10,"Order Date")
    sheet3.write(0,11,"Expected Delivery")
    sheet3.write(0,12,"Supplier")

    try:
        dataQuery = """
                    SELECT ph1.original_pick_header_reference AS orig_ref, 
                    ph1.document_reference AS reference,
                    im.location_code AS location,
                    pb.part_number, pb.qty_expected-pb.qty_transacted AS pick_qty,
                    ob.customer_reference,ob.line_no,
                    pb.zone_destination,
                    oh.baan_serial,oh.machine_model,
                    DATE(oh.date_created) AS order_date,
                    DATE(oh.expected_delivery_time) AS delivery_date,
                    v.vendor_reference_code AS supplier
                    FROM pick_header ph1
                    LEFT JOIN pick_header ph2 ON ph1.original_pick_header_reference=ph2.document_reference
                    JOIN pick_body pb ON pb.pick_header_id=ph1.id
                    LEFT JOIN order_header oh ON oh.pick_header_id=ph2.id
                    LEFT JOIN order_body ob ON ob.order_header_id=oh.id
                    LEFT JOIN part p ON pb.part_number=p.part_number
                    LEFT JOIN vendor v ON p.vendor_id=v.id
                    LEFT JOIN (
                    SELECT
                        part_number,
                        MAX(location_code) AS location_code
                    FROM inventory_master
                    WHERE inventory_status_id = 1
                    GROUP BY part_number
                    ) im
                    ON pb.part_number = im.part_number
                    WHERE ph1.original_pick_header_reference IS NOT null
                    AND pb.processed=0
                    ORDER BY ph1.date_created                
                """
        cursor = cnx.cursor()
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        r = 1
        for row in data:
            c = 0
            i = 0
            for col in row:
                sheet3.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

if __name__ == '__main__':
# check for filter
    if (len(sys.argv)>1):
        filter = sys.argv[1]
    else:
        filter = ""
    reportpath = "f:/kukfiles/daily_target/"
    sender = "vimsreport.gb@logisteed.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)

    defaults = {}
    defaults['dbhost']=db_host
    defaults['dbuser']=db_user
    defaults['dbpwd']=db_pass
    defaults['dbase']=db_name

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


    tablename = "VIMS_daytarget"
    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)

    book.close()
    cnx.close()

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