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

import default_setting

def getData(partNumber,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 = "SELECT product_type,vendor,part_number,DATE('"+str(weekDates[0])+"') AS open_date,DATE('"+str(weekDates[6])+"') AS close_date,sum(open_case) AS open_case,sum(open_qty) AS open_qty,sum(rcv_case) AS rcv_case,sum(rcv_qty) AS rcv_qty,sum(ship_case) AS ship_case,sum(ship_qty) AS ship_qty,sum(open_case+rcv_case-ship_case) as bal_case,sum(open_qty+rcv_qty-ship_qty) AS bal_qty"
        dataQuery +=" FROM (SELECT product_type.product_type_code AS product_type,inventory_balance.vendor_code AS vendor, inventory_balance.part_number AS part_number,inventory_balance.balance_case AS open_case,inventory_balance.balance_qty AS open_qty, 0 AS rcv_case, 0 AS rcv_qty, 0 AS ship_case, 0 AS ship_qty FROM inventory_balance LEFT JOIN product_type ON inventory_balance.product_type_id=product_type.id WHERE inventory_balance.balance_date = DATE_FORMAT(DATE_SUB('"+str(weekDates[0])+"', INTERVAL ((7 + WEEKDAY(DATE_SUB('"+str(weekDates[0])+"', INTERVAL 1 WEEK)) - 5) % 7) DAY), '%Y-%m-%d')"
        dataQuery +=" UNION SELECT transaction_history.product_type_code AS product_type,transaction_history.vendor_code AS vendor, transaction_history.part_number AS part_number, 0 AS open_case,0 AS open_qty, SUM(IF(transaction_history.short_code IN('RBTC','RBTD','RBTP','SA-IN'),1,0)) AS rcv_case, SUM(IF(transaction_history.short_code IN('RBTC','RBTD','RBTP','SA-IN'),txn_qty,0)) AS rcv_qty, SUM(IF(transaction_history.short_code IN('SA-OUT','SHIP'),1,0)) AS ship_case, SUM(IF(transaction_history.short_code IN('SA-OUT','SHIP'),txn_qty,0)) AS ship_qty FROM transaction_history WHERE DATE(transaction_history.date_created) BETWEEN DATE('"+str(weekDates[0])+"') AND DATE('"+str(weekDates[6])+"') AND short_code IN('RBTC','RBTD','RBTP','SA-IN','SHIP','SA-OUT') GROUP BY product_type,vendor,part_number ORDER by product_type,vendor,part_number) AS tranhist"
        if (partNumber is not None and partNumber != ""):
            dataQuery +=" WHERE part_number='"+str(partNumber)+"'"
        dataQuery +=" GROUP BY product_type,vendor,part_number"
#        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'] is not None):
                vendorCode = row['vendor']
            if (row['part_number'] is not None):	
                partNumber = row['part_number']
            if (row['product_type'] is not None):	
                prodType = row['product_type']
            recBalCase = row['rcv_case']
            recBalance = row['rcv_qty']
            delBalCase = row['ship_case']
            delBalance = row['ship_qty']
            balCase = row['bal_case']
            balance = row['bal_qty']
            
            writeBalance(defaults,vendorCode,prodType,partNumber,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()
        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:
#        print("TEST")
        dataQuery = ""
        dataQuery = "SELECT id AS id FROM product_type WHERE product_type_code = '"+str(prodType)+"' LIMIT 1"
        cursor.execute(dataQuery)
        data = cursor.fetchall()
        prodTypeId = ""
        for row in data:
           prodTypeId = row['id']
        id = ""
        if (prodType != ""):
            dataQuery = "SELECT inventory_balance.id AS id FROM inventory_balance WHERE vendor_code='"+vendorCode+"' AND product_type_id="+str(prodTypeId)+" AND part_number='"+partNumber+"' AND balance_date='"+str(balanceDate)+"' LIMIT 1"
        else:
            dataQuery = "SELECT inventory_balance.id AS 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(prodTypeId)
            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__':
    
    partNumber = sys.argv[1]
    
    filter = ""

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