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'])
    if (shortOnly != "Y"):
        reportName = reportpath + "/fwd_orders.xlsx"
    else:
        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, order_header.product_type"
        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 = ""
        prvType = ""
        invQty = 0
        ordQty = 0
        action = ""
        for row in data:
            ordQty = row['order_qty']
            if (prvKey != row['part_number']):
                prvKey = row['part_number']
                prvType = row['product_type']
                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 += " JOIN product_type on inventory_master.product_type_id=product_type.id"
                invQuery += " WHERE (part.part_number='" + str(prvKey) + "' AND product_type.product_type_code ='" + str(
                    prvType) + "') 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']
                    print(invQuery)
                if (invQty == None):
                    invQty = 0
                    print(invQuery)
                    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)
            print("Inv Quant:", 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 = invQty - ordQty
        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)
        if (reportName and reportName != ''):
            reportpath = reportName
        vimsmail.createMail(reportpath, sender, recipients, "")
    #    print(reportName)
    if os.path.exists(reportName):
        os.remove(reportName)

#	print(defaults)