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,filter):
    weekDates = ["sunday","monday","tuesday","wednesday","thursday","friday","saturday"]
    dayDate = datetime.date.today().strftime('%Y-%m-%d')
    dayOffset = 7
    
# today-date
    if (len(sys.argv) > 3):
        dayDate = sys.argv[3]
        dayOffset = 0
        
    today = datetime.datetime.strptime(dayDate, '%Y-%m-%d')	
    day = today.strftime('%a')

    idx = (today.weekday() + 1) % 7
    sun = today - datetime.timedelta(idx)
    prvSun = sun - datetime.timedelta(days=dayOffset)
    
# week-start-date
    startDate = prvSun.strftime('%Y-%m-%d')
    
    if (len(sys.argv) > 4):
        startDate = sys.argv[4]
        
    print(startDate,today,day)
# get a list of the dates from Mon - Sun
    for i in range(7):
        day = prvSun + datetime.timedelta(i)
        dayDate = day.strftime('%Y-%m-%d')
        weekDates[i] = dayDate
        idx = idx+1
        
        
    cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
    tablename = "transaction_history"
    reportName = reportpath+"/recdel.xlsx"
    book = xlsxwriter.Workbook(reportName)
    cell_formatdate = book.add_format()
    cell_formatdate.set_num_format("yyyy/mm/dd hh:mm")
    sheet1 = book.add_worksheet("Sheet 1")
#	style1 = xlwt.XFStyle()
#	style1.num_format_str = 'DD-MM-YYYY HH:MM:SS'

    for col in range(20):
        sheet1.set_column(col, col, 20)
#	sheet1.set_column(0,0, None, cell_formatdate)
#	sheet1.write(0,0,"date_created")
    sheet1.write(0,0,"Vendor")
    sheet1.write(0,1,"part_number")
    sheet1.write(0,2,"Open Balance")
    sheet1.write(0,3,"Rcv."+str(weekDates[0]))
    sheet1.write(0,4,"Ship."+str(weekDates[0]))
    sheet1.write(0,5,"Rcv."+str(weekDates[1]))
    sheet1.write(0,6,"Ship."+str(weekDates[1]))
    sheet1.write(0,7,"Rcv."+str(weekDates[2]))
    sheet1.write(0,8,"Ship."+str(weekDates[2]))
    sheet1.write(0,9,"Rcv."+str(weekDates[3]))
    sheet1.write(0,10,"Ship."+str(weekDates[3]))
    sheet1.write(0,11,"Rcv."+str(weekDates[4]))
    sheet1.write(0,12,"Ship."+str(weekDates[4]))
    sheet1.write(0,13,"Rcv."+str(weekDates[5]))
    sheet1.write(0,14,"Ship."+str(weekDates[5]))
    sheet1.write(0,15,"Rcv."+str(weekDates[6]))
    sheet1.write(0,16,"Ship."+str(weekDates[6]))
    sheet1.write(0,17,"Close Balance")
	
    try:
        dataQuery = ""
        dataQuery = dataQuery + "SELECT substr(inventory_balance.balance_date,1,10) AS date_created"
        dataQuery = dataQuery + ", inventory_balance.vendor_code AS vendor_code"
        dataQuery = dataQuery + ", inventory_balance.part_number AS part_number, 'OPEN' AS short_code"
        dataQuery = dataQuery + ", sum(inventory_balance.balance_qty) AS txn_qty"
        dataQuery = dataQuery + ", inventory_balance.product_type_id AS product_type_id";
        dataQuery = dataQuery + " FROM inventory_balance"
        dataQuery = dataQuery + " LEFT JOIN part ON inventory_balance.part_number=part.part_number"
        dataQuery = dataQuery + " WHERE "
        if (vendorCode != ""):
            dataQuery = dataQuery + " inventory_balance.vendor_code='"+vendorCode+"' AND"
        dataQuery = dataQuery + " substr(inventory_balance.balance_date,1,10) < '"+str(weekDates[0])+"'"
        dataQuery = dataQuery + " GROUP BY vendor_code, part_number, short_code, date_created"
        dataQuery = dataQuery + " UNION"
        dataQuery = dataQuery + " SELECT substr(transaction_history.date_created,1,10) AS date_created"
        dataQuery = dataQuery + ", vendor.vendor_reference_code AS vendor_code"
        dataQuery = dataQuery + ", transaction_history.part_number AS part_number, transaction_history.short_code AS short_code"
        dataQuery = dataQuery + ", sum(CASE WHEN part.conversion_factor=0 THEN 0 WHEN part.conversion_factor IS null THEN 0 ELSE transaction_history.txn_qty / part.conversion_factor END) AS txn_qty"
        dataQuery = dataQuery + ", product_type.id AS product_type_id"
        dataQuery = dataQuery + " FROM transaction_history"
        dataQuery = dataQuery + " LEFT JOIN part ON transaction_history.part_number=part.part_number"
        dataQuery = dataQuery + " LEFT JOIN vendor ON part.vendor_id=vendor.id"
        dataQuery = dataQuery + " LEFT JOIN product_type ON transaction_history.product_type_code=product_type.product_type_code"
        dataQuery = dataQuery + " WHERE "
        if (vendorCode != ""):
            dataQuery = dataQuery + " vendor.vendor_reference_code = '"+vendorCode+"' AND"
        dataQuery = dataQuery + " substr(transaction_history.date_created,1,10) BETWEEN '"+str(weekDates[0])+"' AND '"+str(weekDates[6])+"'"
        dataQuery = dataQuery + " AND transaction_history.short_code IN('RBTD','RBTP','RBTC','SHIP')"
        dataQuery = dataQuery + " GROUP BY vendor_code, part_number, short_code, date_created"
        dataQuery = dataQuery + " ORDER BY vendor_code, part_number, short_code, date_created"
#		print("TEST-",dataQuery)
        #		cursor = cnx.cursor()
        cursor = cnx.cursor(dictionary=True)
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        prvKey = ""
        prvVendor = ""
        prvProdType = ""
        prvPart = ""
        r = 1
        weekRec = [0,0,0,0,0,0,0]
        weekShp = [0,0,0,0,0,0,0]
        openBalance = 0
        recBalance = 0
        recBalCase = 0
        delBalance = 0
        delBalCase = 0
        balance = 0
        balCase = 0
        vendorCode = ""
        prodType = ""
        partNumber=""
        for row in data:
            if (row['vendor_code'] is not None):
                vendorCode = row['vendor_code']
            if (row['part_number'] is not None):	
                partNumber = row['part_number']
            if (row['product_type_id'] is not None):	
                prodType = row['product_type_id']
# Change of Key
            if (prvKey == ""):
                prvKey = vendorCode+str(prodType)+partNumber
            if (prvKey != vendorCode+str(prodType)+partNumber):
                sheet1.write(r,0,prvVendor)
                sheet1.write(r,1,prvPart)
                sheet1.write(r,2,openBalance)
                sheet1.write(r,3,weekRec[0])
                sheet1.write(r,4,weekShp[0])
                sheet1.write(r,5,weekRec[1])
                sheet1.write(r,6,weekShp[1])
                sheet1.write(r,7,weekRec[2])
                sheet1.write(r,8,weekShp[2])
                sheet1.write(r,9,weekRec[3])
                sheet1.write(r,10,weekShp[3])
                sheet1.write(r,11,weekRec[4])
                sheet1.write(r,12,weekShp[4])
                sheet1.write(r,13,weekRec[5])
                sheet1.write(r,14,weekShp[5])
                sheet1.write(r,15,weekRec[6])
                sheet1.write(r,16,weekShp[6])
                sheet1.write(r,17,openBalance+balance)
# write / update Weekly balance
                if ((prvVendor is not None) and (prvVendor != "")):
                    writeBalance(defaults,prvVendor,prvProdType,prvPart,weekDates[6],recBalance,delBalance,balance,recBalCase,delBalCase,balCase)
                
                r=r+1
                weekRec = [0,0,0,0,0,0,0]
                weekShp = [0,0,0,0,0,0,0]
                openBalance = 0
                recBalance = 0
                recBalCase = 0
                delBalance = 0
                delBalCase = 0
                balance = 0
                balCase = 0
                
            if (row['short_code'] == "OPEN"):
                if (row['txn_qty'] is not None):
                    openBalance = openBalance+row['txn_qty']
#					balance = balance+row['txn_qty']
            else:
                for i in range(0,7):
                    if weekDates[i] == row['date_created']:
                        if row['short_code'] == "SHIP":
                            if (row['txn_qty'] is not None):
                                weekShp[i] = weekShp[i]+row['txn_qty']
                                delBalance = delBalance+row['txn_qty']
                                delBalCase = delBalCase+1
                                balance = balance-row['txn_qty']
                                balCase = balCase+1
                        else:
                            if (row['txn_qty'] is not None):
                                weekRec[i] = weekRec[i]+row['txn_qty']
                                recBalance = recBalance+row['txn_qty']
                                recBalCase = recBalCase+1
                                balance = balance+row['txn_qty']
                                balCase = balCase+1
            prvKey = vendorCode+str(prodType)+partNumber
            prvVendor = vendorCode
            prvProdType = prodType
            prvPart = partNumber
#			print("TEST1-",partNumber,weekRec,weekShp)
        if (prvVendor is not None):
            sheet1.write(r,0,prvVendor)
            sheet1.write(r,1,prvPart)
            sheet1.write(r,2,openBalance)
            sheet1.write(r,3,weekRec[0])
            sheet1.write(r,4,weekShp[0])
            sheet1.write(r,5,weekRec[1])
            sheet1.write(r,6,weekShp[1])
            sheet1.write(r,7,weekRec[2])
            sheet1.write(r,8,weekShp[2])
            sheet1.write(r,9,weekRec[3])
            sheet1.write(r,10,weekShp[3])
            sheet1.write(r,11,weekRec[4])
            sheet1.write(r,12,weekShp[4])
            sheet1.write(r,13,weekRec[5])
            sheet1.write(r,14,weekShp[5])
            sheet1.write(r,15,weekRec[6])
            sheet1.write(r,16,weekShp[6])
            sheet1.write(r,17,openBalance+balance)
# write / update Weekly balance
            if ((prvVendor is not None) and (prvVendor != "")):
                writeBalance(defaults,prvVendor,prvProdType,prvPart,weekDates[6],recBalance,delBalance,balance,recBalCase,delBalCase,balCase)
		
    except Exception as Err:
        print ('MySql Error',Err,dataQuery)		
    finally:
        try:
            cursor.close()
            cnx.close()
            book.close()
#			if not os.path.isdir(reportpath):
#				os.makedirs(reportpath)
#			book.save(reportpath+"\\"+tablename+".xlsx")
        except:
            pass
            
    return reportName

# write week balance
def writeBalance(defaults,vendorCode,prodType,partNumber,balanceDate,recBalance,delBalance,balanceQty,recBalCase,delBalCase,balCase):
    cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
    cursor = cnx.cursor(dictionary=True)
    tablename = "inventory_balance"
    try:
        dataQuery = ""
        id = ""
        if (prodType != ""):
            dataQuery = "SELECT id FROM inventory_balance WHERE vendor_code='"+vendorCode+"' AND product_type_id="+str(prodType)+" AND part_number='"+partNumber+"' AND balance_date='"+str(balanceDate)+"' LIMIT 1"
        else:
            dataQuery = "SELECT id FROM inventory_balance WHERE vendor_code='"+vendorCode+"' AND part_number='"+partNumber+"' AND balance_date='"+str(balanceDate)+"' LIMIT 1"
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        if (cursor.rowcount >0):
            for row in data:
                id = row['id']
            dataQuery = "UPDATE inventory_balance SET receipt_case="+str(recBalCase)+",receipt_qty="+str(recBalance)+",delivery_case="+str(delBalCase)+",delivery_qty="+str(delBalance)+",balance_case="+str(balCase)+",balance_qty="+str(balanceQty)+", last_updated=now(),last_updated_by='upload' WHERE id="+str(id)+" LIMIT 1";
        else:
            dataQuery = "INSERT INTO inventory_balance (id,vendor_code,product_type_id,part_number,balance_date,receipt_case,receipt_qty,delivery_case,delivery_qty,balance_case,balance_qty,date_created,created_by,last_updated,last_updated_by)"
            dataQuery = dataQuery + " VALUES(DEFAULT,'"+vendorCode+"'"
            if (prodType != ""):
                dataQuery = dataQuery + ","+str(prodType)
            else:
                dataQuery = dataQuery + ",null"
            dataQuery = dataQuery + ",'"+partNumber+"','"+str(balanceDate)+" 00:00:00',"+str(recBalCase)+","+str(recBalance)+","+str(delBalCase)+","+str(delBalance)+","+str(balCase)+","+str(balanceQty)+",now(),'upload',now(),'upload')"
        cursor.execute(dataQuery)
        cnx.commit()
    except Exception as Err:
        print ('MySql Error',Err,dataQuery)		
    finally:
        try:
            cursor.close()
            cnx.close()
        except:
            pass
		
#	return reportName

if __name__ == '__main__':
    emailTo = ""
    
    vendorCode = sys.argv[1]
    if (sys.argv[2] != '' and sys.argv[2] != "''"):
        emailTo= sys.argv[2]
    
# check for filter
    if (len(sys.argv)>5):
        filter = sys.argv[5]
    else:
        filter = ""
    reportpath = "c:/reports/recdel"
    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,filter)
    if (emailTo != ""):
        recipients = vimsmail.getRecipients(defaults,emailTo)	
        vimsmail.createMail(reportpath, sender, recipients,"")
	
#	print(defaults)