# Charging Updates

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

import default_setting
	
def storageCharge (startDate, dayDate, cnx):
    try:
# connection
        cursor = cnx.cursor(dictionary=True)
    # Get Storage Charge
        strgCharge = 0
        dataQuery = "SELECT charge_master.id AS id,charge_master.std_charge AS std_charge,charge_type.charge_name AS charge_name FROM charge_master JOIN charge_type ON charge_master.charge_type_id=charge_type.id WHERE charge_master.id=2 LIMIT 1"
        cursor.execute(dataQuery) 
        i = 0
        
        for row in cursor.fetchall():
            i=i+1
            strgId = row['id']
            strgCharge = row['std_charge']
            chargeName = row['charge_name']
            
    # Get inventory count
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, COUNT(inventory_master.id) AS strg FROM inventory_master JOIN part ON inventory_master.part_id=part.id JOIN vendor ON part.vendor_id=vendor.id GROUP BY vendor.id ORDER BY vendor.id"
        cursor.execute(dataQuery) 
        
        for row in cursor.fetchall():
            vendorCode = row['vendor_code']
            strg = row['strg']
        
            strgQuery = "SELECT id FROM charge_body WHERE start_date='"+str(startDate)+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" LIMIT 1"
            cursor.execute(strgQuery) 
            i = 0
            for strgRow in cursor.fetchall():
                i = i+1
                id = strgRow['id']
            if i == 0:
                strgQuery = "INSERT INTO charge_body (id,start_date,end_date,vendor_code,charge_name,storage_charge_id,std_charge,box_qty,charge_value,date_created,created_by,last_updated,last_updated_by)"
                strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(strgCharge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload')"
            else:
                strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(strgCharge)+", box_qty="+str(strg)+", charge_value=std_charge*box_qty, last_updated=now(),last_updated_by='upload' WHERE id="+str(id)+" LIMIT 1"
            cursor.execute(strgQuery)	
            cnx.commit()
    except Exception as Err:
        print("FAIL-",str(Err),dataQuery+" "+strgQuery)
    finally:
        cursor.close()
	
    return "OK  -storageCharge"

def receiptCharge (startDate, dayDate, cnx):
    try:
# connection
        cursor = cnx.cursor(dictionary=True)
    # Get Storage Charge
        recCharge = 0
        dataQuery = "SELECT charge_master.id AS id,charge_master.std_charge AS std_charge,charge_type.charge_name AS charge_name FROM charge_master JOIN charge_type ON charge_master.charge_type_id=charge_type.id WHERE charge_master.id=1 LIMIT 1"
        cursor.execute(dataQuery) 
        i = 0       
        for row in cursor.fetchall():
            i=i+1
            strgId = row['id']
            strgCharge = row['std_charge']
            chargeName = row['charge_name']
            
    # Get receipt count
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, COUNT(transaction_history.id) AS strg FROM transaction_history JOIN part ON transaction_history.part_number=part.part_number JOIN vendor ON part.vendor_id=vendor.id WHERE transaction_history.short_code IN('RBTD','RBTP') AND substr(transaction_history.date_created,1,10) BETWEEN '"+str(startDate)+"' AND '"+str(dayDate)+"' GROUP BY vendor.id ORDER BY vendor.id"
        cursor.execute(dataQuery) 
        
        for row in cursor.fetchall():
            vendorCode = row['vendor_code']
            strg = row['strg']
        
            strgQuery = "SELECT id FROM charge_body WHERE start_date='"+str(startDate)+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" LIMIT 1"
            cursor.execute(strgQuery) 
            i = 0
            for strgRow in cursor.fetchall():
                i = i+1
                id = strgRow['id']
            if i == 0:
                strgQuery = "INSERT INTO charge_body (id,start_date,end_date,vendor_code,charge_name,storage_charge_id,std_charge,box_qty,charge_value,date_created,created_by,last_updated,last_updated_by)"
                strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(strgCharge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload')"
            else:
                strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(strgCharge)+", box_qty="+str(strg)+", charge_value=std_charge*box_qty, last_updated=now(),last_updated_by='upload' WHERE id="+str(id)+" LIMIT 1"
            cursor.execute(strgQuery)	
            cnx.commit()
    except Exception as Err:
        print("FAIL-",str(Err),dataQuery+" "+strgQuery)
    finally:
        cursor.close()
	
    return "OK  -receiptCharge "+dataQuery
    
def despCharge (startDate, dayDate, cnx):
    try:
# connection
        cursor = cnx.cursor(dictionary=True)
    # Get Storage Charge
        recCharge = 0
        dataQuery = "SELECT charge_master.id AS id,charge_master.std_charge AS std_charge,charge_type.charge_name AS charge_name FROM charge_master JOIN charge_type ON charge_master.charge_type_id=charge_type.id WHERE charge_master.id=7 LIMIT 1"
        cursor.execute(dataQuery) 
        i = 0       
        for row in cursor.fetchall():
            i=i+1
            strgId = row['id']
            strgCharge = row['std_charge']
            chargeName = row['charge_name']
            
    # Get receipt count
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, COUNT(transaction_history.id) AS strg FROM transaction_history JOIN part ON transaction_history.part_number=part.part_number JOIN vendor ON part.vendor_id=vendor.id WHERE transaction_history.short_code IN('SHIP','MNFST') AND substr(transaction_history.date_created,1,10) BETWEEN '"+str(startDate)+"' AND '"+str(dayDate)+"' GROUP BY vendor.id ORDER BY vendor.id"
        cursor.execute(dataQuery) 
        
        for row in cursor.fetchall():
            vendorCode = row['vendor_code']
            strg = row['strg']
        
            strgQuery = "SELECT id FROM charge_body WHERE start_date='"+str(startDate)+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" LIMIT 1"
            cursor.execute(strgQuery) 
            i = 0
            for strgRow in cursor.fetchall():
                i = i+1
                id = strgRow['id']
            if i == 0:
                strgQuery = "INSERT INTO charge_body (id,start_date,end_date,vendor_code,charge_name,storage_charge_id,std_charge,box_qty,charge_value,date_created,created_by,last_updated,last_updated_by)"
                strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(strgCharge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload')"
            else:
                strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(strgCharge)+", box_qty="+str(strg)+", charge_value=std_charge*box_qty, last_updated=now(),last_updated_by='upload' WHERE id="+str(id)+" LIMIT 1"
            cursor.execute(strgQuery)	
            cnx.commit()
    except Exception as Err:
        print("FAIL-",str(Err),dataQuery+" "+strgQuery)
    finally:
        cursor.close()
	
    return "OK  -despCharge "+dataQuery
	
def main():
    dayDate = datetime.date.today().strftime('%Y-%m-%d')
    if (len(sys.argv) > 1):
        dayDate = sys.argv[1]
    today = datetime.datetime.strptime(dayDate, '%Y-%m-%d')	
    day = today.strftime('%a')
    
    idx = (today.weekday() + 1) % 7
    sun = today - datetime.timedelta(idx)
    
    startDate = sun.strftime('%Y-%m-%d')
    if (len(sys.argv) > 2):
        startDate = sys.argv[2]

    # Default Connection / System Settings
    defaults = default_setting.defaultSettings()

    # mySql Connector
    cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])

    # Call updates
    print(startDate,dayDate)	
    response = storageCharge(startDate,dayDate,cnx)   
    print(response)
    
    response = receiptCharge(startDate,dayDate,cnx)   
    print(response)
    
    response = despCharge(startDate,dayDate,cnx)   
    print(response)
	
if __name__ == '__main__':
	main()