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

import default_setting
import vimsmail

def getData(vendorCode,defaults,reportpath,shortOnly):
    cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])

    reportName = reportpath+"/fwd_shortage.xlsx"
    book = xlsxwriter.Workbook(reportName)
    cell_formatdatetime = book.add_format()
    cell_formatdatetime.set_num_format("yyyy-mm-dd hh:mm")
    cell_formatdate = book.add_format()
    cell_formatdate.set_num_format("dd/mm/yyyy")
    cell_formattime = book.add_format()
    cell_formattime.set_num_format("hh:mm")
#    currency_format = book.add_format({'num_format': '$#,##0.00'})
#    sheet1.write('A1', 1234.56, currency_format)
    sheet1 = book.add_worksheet("Sheet 1")

    sheet1.write(0,0, "Duns")
    sheet1.write(0,1, "Customer")
    sheet1.write(0,2, "Code")
    sheet1.write(0,3, "Part_Number")
    sheet1.write(0,4, "Date")
    sheet1.write(0,5, "Time")
    sheet1.write(0,6, "Ran")
    sheet1.write(0,7, "Order_qty")
    sheet1.write(0,8, "Inventory_qty")
    sheet1.write(0,9, "Action")
    sheet1.write(0,10, "Reference")

    try:
        dataQuery = "SELECT vendor.duns_code AS duns,vendor.vendor_name AS customer,vendor.vendor_reference_code AS vendor_code,part.part_number AS part_number,DATE(order_header.expected_delivery_time) AS date,TIME(order_header.expected_delivery_time) AS time,order_body.ran_order AS ran,SUM(order_body.qty_expected-order_body.qty_transacted) AS order_qty,0 AS qty_outs,null AS action,null AS pui,order_header.customer_reference AS order_ref"
        dataQuery +=" FROM order_header"
        dataQuery +=" JOIN order_body ON order_header.id=order_body.order_header_id"
        dataQuery +=" LEFT JOIN part ON order_body.part_id=part.id"
        dataQuery +=" LEFT JOIN vendor ON part.vendor_id=vendor.id"
        dataQuery +=" WHERE"
        if ((vendorCode != "") and (vendorCode != "''")):
            dataQuery +=" vendor.vendor_reference_code='"+vendorCode+"' AND"
        dataQuery +=" (order_body.qty_expected-order_body.qty_transacted) >0 AND"
        dataQuery +=" (order_body.blocked IS null OR order_body.blocked <> 1) AND"
        dataQuery +=" DATE(order_header.expected_delivery_time)>=DATE(now())"
        dataQuery +=" GROUP BY duns,customer,vendor_code,part_number,order_ref,date,time,ran"
        dataQuery +=" ORDER BY duns,customer,vendor_code,part_number,order_ref,date,time,ran"
        cursor = cnx.cursor(dictionary=True)
        cursor.execute(dataQuery)
        data = cursor.fetchall()
#		print("TEST",dataQuery,cursor.rowcount)
        r = 1
        i = 0
        prvKey = ""
        invQty = 0
        ordQty = 0
        action = ""
        for row in data:
            ordQty = row['order_qty']
            if (prvKey != row['part_number']):
                prvKey = row['part_number']
                invQuery = "SELECT CAST(SUM(inventory_master.inventory_qty/part.conversion_factor) AS SIGNED) AS inventory_qty FROM inventory_master"
                invQuery +=" JOIN part ON inventory_master.part_id=part.id"
                invQuery +=" JOIN inventory_status ON inventory_master.inventory_status_id=inventory_status.id"
                invQuery +=" WHERE part.part_number='"+str(prvKey)+"' AND lower(inventory_status_code) IN('ok','noran','rcv')"
                invQuery +=" AND (inventory_master.requires_inspection IS null OR inventory_master.requires_inspection=0)"
                invCursor = cnx.cursor(dictionary=True)
                invCursor.execute(invQuery)
                invData = invCursor.fetchall()
                for invRow in invData:
                    invQty = invRow['inventory_qty'] 
                if (invQty == None):
                    invQty = 0
	#				print (row['duns'],row['part_number'],invQty)
#            if (invQty >= ordQty):
#                invQty = invQty-ordQty
#            else:
#                invQty = 0
            print (row['duns'],row['part_number'],row['ran'],ordQty,invQty)
            if (shortOnly != "Y") or (shortOnly == "Y" and (ordQty > invQty)):
                sheet1.write(r,0,row['duns'])
                sheet1.write(r,1,row['customer'])
                sheet1.write(r,2,row['vendor_code'])
                sheet1.write(r,3,row['part_number'])
                sheet1.write(r,4,row['date'],cell_formatdate)
                sheet1.write(r,5,row['time'],cell_formattime)
                sheet1.write(r,6,row['ran'])
                sheet1.write(r,7,ordQty)
                sheet1.write(r,8,invQty)
                sheet1.write(r,9,'')
                sheet1.write(r,10,row['order_ref'])
                r = r+1
            if (invQty >= ordQty):
                invQty = invQty-ordQty
            else:
                invQty = 0
        if (r == 1):
            sheet1.write(r+1,0,"No Results")
    except Exception as err:
        print ('MySql Error',str(err),invQuery)
        
    finally:
        try:
            cursor.close()
            cnx.close()
            book.close()
        except:
            pass
            
    return reportName

if __name__ == '__main__':
    emailTo = ""
    shortOnly = 'Y'

    print(len(sys.argv))

    vendorCode = sys.argv[1].replace("'","")
    if (len(sys.argv) > 2 and (sys.argv[2] != '' and sys.argv[2] != "''")):
        emailTo= sys.argv[2]
    if (len(sys.argv) > 3 and (sys.argv[3] != '' and sys.argv[3] != "''")):
        shortOnly= sys.argv[3]
        
    filter = ""
    reportpath = "c:/reports/fwdshort"
    if (emailTo != ""):
        reportpath = reportpath+"/"+emailTo

    if not os.path.exists(reportpath):
        os.makedirs(reportpath)

    sender = "vims@vantec-gl.com"
    defaults = default_setting.defaultSettings()
    reportName = getData(vendorCode,defaults,reportpath,shortOnly)
    if (emailTo != ""):
        recipients = vimsmail.getRecipients(defaults,emailTo)	
        vimsmail.createMail(reportpath, sender, recipients,"")
	
#	print(defaults)