import mysql.connector
import os
import sys
import time
import datetime    

import default_setting

def getData(vendorCode,defaults):
    weekDates = ["sunday","monday","tuesday","wednesday","thursday","friday","saturday"]
    dayDate = datetime.date.today().strftime('%Y-%m-%d')
    dayOffset = 0
    
# today-date
    if (len(sys.argv) > 2):
        dayDate = sys.argv[2]
        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) > 3):
        startDate = sys.argv[3]
        
    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')
        if (len(sys.argv) <=2 or (len(sys.argv) > 2 and dayDate < sys.argv[2])):
            weekDates[i] = dayDate
        idx = idx+1
    
    if (len(sys.argv) > 2):
        weekDates[6] = sys.argv[2]
        
    cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
    tablename = "transaction_history"
    	
    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 = ""
        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):
# write / update Weekly balance
                if ((prvVendor is not None) and (prvVendor != "")):
                    writeBalance(defaults,prvVendor,prvProdType,prvPart,weekDates[6],recBalance,delBalance,balance,recBalCase,delBalCase,balCase)
                    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):
                                delBalance = delBalance+row['txn_qty']
                                delBalCase = delBalCase+1
                                balance = balance-row['txn_qty']
                                balCase = balCase-1
                        else:
                            if (row['txn_qty'] is not None):
                                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
# write / update Weekly balance
        if ((prvVendor is not None) and (prvVendor != "")):
            writeBalance(defaults,prvVendor,prvProdType,prvPart,weekDates[6],recBalance,delBalance,balance,recBalCase,delBalCase,balCase)
            openBalance = 0
            recBalance = 0
            recBalCase = 0
            delBalance = 0
            delBalCase = 0
            balance = 0
            balCase = 0
    except Exception as Err:
        print ('MySql Error',Err,dataQuery)		
    finally:
        try:
            cursor.close()
            cnx.close()
            book.close()
        except:
            pass
            
    return

# 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()
        print("Balance",dataQuery)
    except Exception as Err:
        print ('MySql Error',Err,dataQuery)		
    finally:
        try:
            cursor.close()
            cnx.close()
        except:
            pass
		
#	return reportName

if __name__ == '__main__':
    
    vendorCode = sys.argv[1]
    
    filter = ""

    defaults = default_setting.defaultSettings()
    result = getData(vendorCode,defaults)
	
#	print(defaults)