# Charging Updates
# -----------------------------------------------------------------------------------------------------------
# 2021-04-14 - document_status changed to document_status_id
# 2021-06-02 - Don't update processed charge_body rows
# -----------------------------------------------------------------------------------------------------------
import sys
import os
import mysql.connector
import datetime
import time

import default_setting

def weekDays(startDate):
    try:
        weekDates = ["sunday","monday","tuesday","wednesday","thursday","friday","saturday"]
        dayOffset = 0
        
        today = datetime.datetime.strptime(startDate, '%Y-%m-%d')	
        day = today.strftime('%a')

        idx = (today.weekday() + 1) % 7
        sun = today - datetime.timedelta(idx)
    
# week-start-date
        firstDate = sun.strftime('%Y-%m-%d')

        print(firstDate,today,day)
# get a list of the allowed dates from Sunday - Saturday
        for i in range(7):
            day = sun + datetime.timedelta(i)
            dayDate = day.strftime('%Y-%m-%d')
            print("day",dayDate)
            weekDates[i] = dayDate 
    except Exception as err:
        print("Date Errors:",err)
        
    return weekDates

def calcCharges(vendor, startDate, endDate, chargeDays, cnx):
    print(startDate,endDate+'\n')

    response = clearCharge(vendor,startDate,endDate,cnx)   
    print(response+'\n')

    response = storageCharge(vendor,startDate,endDate,chargeDays,cnx)   
    print(response+'\n')

    response = receiptCharge(vendor,startDate,endDate,chargeDays,cnx)   
    print(response+'\n')

    response = despCharge(vendor,startDate,endDate,chargeDays,cnx)   
    print(response+'\n')

    response = decantCharge(vendor,startDate,endDate,chargeDays,cnx)   
    print(response+'\n')

    response = inspectCharge(vendor,startDate,endDate,chargeDays,cnx)   
    print(response+'\n')
    
    response = reportCharge(vendor,startDate,endDate,chargeDays,cnx)   
    print(response+'\n')
    
def clearCharge (vendor,startDate, dayDate, cnx):
    try:
# connection
        cursor = cnx.cursor(dictionary=True)		
        dataQuery = "DELETE FROM charge_body WHERE id>0 AND start_date='"+str(startDate)+"' AND charge_header_id IS null"
        if (vendor != "ALL"):
            dataQuery += " AND vendor_code='"+vendor+"'"
        cursor.execute(dataQuery)
        cnx.commit()
    except Exception as Err:
        print("FAIL-",str(Err),dataQuery+" "+strgQuery)
    finally:
        cursor.close()

    return "OK  -clearCharge "+dataQuery

def storageCharge (vendor,startDate, dayDate, chargeDays, cnx):
    try:
        strgQuery = ""
        chargeName = ""
        strgId = 0
        strgCharge = 0
# connection
        cursor = cnx.cursor(dictionary=True)
        cursor1 = cnx.cursor(dictionary=True)
# Get Storage Charge
        strgCharge = 0 
# Get document_status_id
        docStsId=1
        dataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status"
        cursor.execute(dataQuery) 
        for row in cursor:
            if (row['document_status_code'] is not None and row['document_status_code'].lower() == 'open'):
                docStsId = row['id']
# Get inventory count
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, CASE WHEN part.storage_charge_id IS NOT null THEN part.storage_charge_id WHEN vendor.storage_charge_id IS NOT null THEN vendor.storage_charge_id ELSE 0 END AS storage_charge_id, 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"
        if (vendor != "ALL"):
            dataQuery +=" WHERE vendor.vendor_reference_code='"+vendor+"'"
        dataQuery +=" GROUP BY vendor.id,storage_charge_id ORDER BY vendor.id, vendor.storage_charge_id"
        cursor.execute(dataQuery) 
        
        for row in cursor.fetchall():
            chargeName = ""
            strgId = 0
            strgCharge = 0
            vendorCode = row['vendor_code']
            strg = row['strg']
            dataChargeQuery = "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="+str(row['storage_charge_id'])+" LIMIT 1"
            cursor1.execute(dataChargeQuery) 
            for chargeRow in cursor1:
                strgId = chargeRow['id']
                strgCharge = chargeRow['std_charge']
                chargeName = chargeRow['charge_name']
                if (strgId != 0):
                    print("STORAGE-",vendorCode,strgId,chargeName,strg)
                    strgQuery = "SELECT id FROM charge_body WHERE start_date='"+startDate+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" AND charge_header_id IS NOT null LIMIT 1"
                    cursor.execute(strgQuery)
                    i=0
                    for strgRow in cursor:
                        i = 1
                    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,document_status_id)"
                        strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(strgCharge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload',"+str(docStsId)+")"
#                    else:
#                        strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(strgCharge)+", box_qty="+str(strg)+",document_status_id="+str(docStsId)+", 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 "+dataQuery

def receiptCharge (vendor,startDate, dayDate, chargeDays, cnx):
    try:
        strgQuery = ""
# connection
        cursor = cnx.cursor(dictionary=True)
        cursor1 = cnx.cursor(dictionary=True)
# Get document_status_id
        docStsId=1
        dataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status"
        cursor.execute(dataQuery) 
        for row in cursor:
            if (row['document_status_code'] is not None and row['document_status_code'].lower() == 'open'):
                docStsId = row['id']
# Get Storage Charge
        recCharge = 0
        chargeName = ""
        strgId = 0
        strgCharge = 0
            
# Get receipt count
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, CASE WHEN part.receipt_charge_id IS NOT null THEN part.receipt_charge_id WHEN vendor.receipt_charge_id IS NOT null THEN vendor.receipt_charge_id ELSE 0 END AS receipt_charge_id, 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"
        dataQuery +=" WHERE"
        if (vendor != "ALL"):
            dataQuery +=" vendor.vendor_reference_code='"+vendor+"' AND "
        dataQuery +=" transaction_history.short_code IN('RBTD','RBTP')"
        dataQuery +=" AND DATE(transaction_history.date_created) IN("+chargeDays+")"
        dataQuery +=" GROUP BY vendor.id, receipt_charge_id ORDER BY vendor.id, receipt_charge_id"
        cursor.execute(dataQuery) 
        for row in cursor.fetchall():
            vendorCode = row['vendor_code']
            strg = row['strg']
            chargeName = ""
            strgId = 0
            strgCharge = 0
            dataChargeQuery = "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="+str(row['receipt_charge_id'])+" LIMIT 1"
            cursor1.execute(dataChargeQuery) 
            for chargeRow in cursor1:
                strgId = chargeRow['id']
                strgCharge = chargeRow['std_charge']
                chargeName = chargeRow['charge_name']
                if (strgId != 0):
                    print("RECEIPT-",vendorCode,strgId,chargeName,strg)
                    strgQuery = "SELECT id FROM charge_body WHERE start_date='"+startDate+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" AND charge_header_id IS NOT null LIMIT 1"
                    cursor.execute(strgQuery)
                    i=0
                    for strgRow in cursor:
                        i = 1
                    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,document_status_id)"
                        strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(strgCharge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload',"+str(docStsId)+")"
#                    else:
#                        strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(strgCharge)+", box_qty="+str(strg)+",document_status_id="+str(docStsId)+", 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 (vendor,startDate, dayDate, chargeDays, cnx):
    try:
        strgQuery = ""
# connection
        cursor = cnx.cursor(dictionary=True)
        cursor1 = cnx.cursor(dictionary=True)
# Get document_status_id
        docStsId=1
        dataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status"
        cursor.execute(dataQuery) 
        for row in cursor:
            if (row['document_status_code'] is not None and row['document_status_code'].lower() == 'open'):
                docStsId = row['id']
# Get Despatch Charge
        recCharge = 0
        chargeName = ""
        strgId = 0
        strgCharge = 0
			
# Get Ship count
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, CASE WHEN part.despatch_charge_id IS NOT null THEN part.despatch_charge_id WHEN vendor.despatch_charge_id IS NOT null THEN vendor.despatch_charge_id ELSE 0 END AS despatch_charge_id, 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"
        dataQuery +=" WHERE"
        if (vendor != "ALL"):
            dataQuery +=" vendor.vendor_reference_code='"+vendor+"' AND "
        dataQuery +=" ((vendor.vendor_reference_code IN('YA003','YA007') AND transaction_history.short_code IN('RBTC','RBTD','RBTP'))"
        dataQuery +=" OR (vendor.vendor_reference_code NOT IN('YA003','YA007') AND transaction_history.short_code IN('SHIP','SHIP')))"
        dataQuery +=" AND DATE(transaction_history.date_created) IN("+chargeDays+")"
        dataQuery +=" GROUP BY vendor.id, despatch_charge_id ORDER BY vendor.id, despatch_charge_id"
        cursor.execute(dataQuery) 

        for row in cursor.fetchall():
            chargeName = ""
            strgId = 0
            strgCharge = 0
            vendorCode = row['vendor_code']
            strg = row['strg']
            dataChargeQuery = "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="+str(row['despatch_charge_id'])+" LIMIT 1"
            cursor1.execute(dataChargeQuery) 
            for chargeRow in cursor1:
                strgId = chargeRow['id']
                strgCharge = chargeRow['std_charge']
                chargeName = chargeRow['charge_name']
                if (strgId != 0):
                    print("DESPATCH-",vendorCode,strgId,chargeName,strg)
                    strgQuery = "SELECT id FROM charge_body WHERE start_date='"+startDate+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" AND charge_header_id IS NOT null LIMIT 1"
                    cursor.execute(strgQuery)
                    i=0
                    for strgRow in cursor:
                        i = 1
                    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,document_status_id)"
                        strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(strgCharge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload',"+str(docStsId)+")"
#                    else:
#                        strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(strgCharge)+", box_qty="+str(strg)+",document_status_id="+str(docStsId)+", 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 decantCharge (vendor,startDate, dayDate, chargeDays, cnx):
    try:
        strgQuery = ""
# connection
        cursor = cnx.cursor(dictionary=True)
        cursor1 = cnx.cursor(dictionary=True)
# Get document_status_id
        docStsId=1
        dataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status"
        cursor.execute(dataQuery) 
        for row in cursor:
            if (row['document_status_code'] is not None and row['document_status_code'].lower() == 'open'):
                docStsId = row['id']
# Get Decant Charge
        decantCharge = 0
        chargeName = ""
        strgId = 0
        strgCharge = 0

# Get Ship count - for decant
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, CASE WHEN part.decant_charge_id IS NOT null THEN part.decant_charge_id WHEN vendor.decant_charge_id IS NOT null THEN vendor.decant_charge_id ELSE 0 END AS charge_id, 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"
        dataQuery +=" WHERE"
        if (vendor != "ALL"):
            dataQuery +=" vendor.vendor_reference_code='"+vendor+"' AND "
        dataQuery +=" transaction_history.short_code IN('SHIP','SHIP')"
        dataQuery +=" AND DATE(transaction_history.date_created) IN("+chargeDays+")"
        dataQuery +=" GROUP BY vendor.id, charge_id ORDER BY vendor.id, charge_id"
        cursor.execute(dataQuery) 
        for row in cursor.fetchall():
            chargeName = ""
            strgId = 0
            strgCharge = 0
            vendorCode = row['vendor_code']
            strg = row['strg']
            dataChargeQuery = "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="+str(row['charge_id'])+" LIMIT 1"
            cursor1.execute(dataChargeQuery) 
            for chargeRow in cursor1:
                strgId = chargeRow['id']
                strgCharge = chargeRow['std_charge']
                chargeName = chargeRow['charge_name']           
                if (strgId != 0):
                    print("DECANT-",vendorCode,strgId,chargeName,strg)
                    strgQuery = "SELECT id FROM charge_body WHERE start_date='"+startDate+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" AND charge_header_id IS NOT null LIMIT 1"
                    cursor.execute(strgQuery)
                    i=0
                    for strgRow in cursor:
                        i = 1
                    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,document_status_id)"
                        strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(strgCharge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload',"+str(docStsId)+")"
#                    else:
#                        strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(strgCharge)+", box_qty="+str(strg)+",document_status_id="+str(docStsId)+", 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  -decantCharge "+dataQuery

def inspectCharge (vendor,startDate, dayDate, chargeDays, cnx):
    try:
        strgQuery = ""
# connection
        cursor = cnx.cursor(dictionary=True)
        cursor1 = cnx.cursor(dictionary=True)
# Get document_status_id
        docStsId=1
        dataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status"
        cursor.execute(dataQuery) 
        for row in cursor:
            if (row['document_status_code'] is not None and row['document_status_code'].lower() == 'open'):
                docStsId = row['id']
# Get Inspect Charge
        charge = 0
        chargeName = ""
        strgId = 0
        strgCharge = 0    
# Get Ship count
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, CASE WHEN part.inspect_charge_id IS NOT null THEN part.inspect_charge_id WHEN vendor.inspect_charge_id IS NOT null THEN vendor.inspect_charge_id ELSE 0 END AS charge_id, COUNT(transaction_history.id) AS strg FROM transaction_history"
        dataQuery +=" JOIN part ON transaction_history.part_number=part.part_number"
        dataQuery +=" JOIN vendor ON part.vendor_id=vendor.id"
        dataQuery +=" WHERE"
        if (vendor != "ALL"):
            dataQuery +=" vendor.vendor_reference_code='"+vendor+"' AND "
        dataQuery +=" ((vendor.vendor_reference_code IN('YA003','YA007') AND transaction_history.short_code IN('SHIP','SHIP'))"
        dataQuery +=" OR (vendor.vendor_reference_code NOT IN('YA003','YA007') AND transaction_history.short_code IN('PBTM','PBTP')))"
        dataQuery +=" AND part.wi_code IS NOT null"
        dataQuery +=" AND DATE(transaction_history.date_created) IN("+chargeDays+")"
        dataQuery +=" GROUP BY vendor.id, charge_id ORDER BY vendor.id, charge_id"
        cursor.execute(dataQuery) 
        for row in cursor.fetchall():
            chargeName = ""
            strgId = 0
            strgCharge = 0
            vendorCode = row['vendor_code']
            strg = row['strg']
            dataChargeQuery = "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="+str(row['charge_id'])+" LIMIT 1"
            cursor1.execute(dataChargeQuery) 
            for chargeRow in cursor1:
                strgId = chargeRow['id']
                charge = chargeRow['std_charge']
                chargeName = chargeRow['charge_name']
                print("DESPATCH-",vendorCode,strgId,chargeName,strg)
                strgQuery = "SELECT id FROM charge_body WHERE start_date='"+startDate+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" AND charge_header_id IS NOT null LIMIT 1"
                cursor.execute(strgQuery)
                i=0
                for strgRow in cursor:
                    i = 1
                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,document_status_id)"
                    strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(charge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload',"+str(docStsId)+")"
#                else:
#                    strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(charge)+", box_qty="+str(strg)+",document_status_id="+str(docStsId)+", 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  -inspCharge "+dataQuery

def reportCharge (vendor,startDate, dayDate, chargeDays, cnx):
    try:
        strgQuery = ""
# connection
        cursor = cnx.cursor(dictionary=True)
        cursor1 = cnx.cursor(dictionary=True)
# Get document_status_id
        docStsId=1
        dataQuery = "SELECT id AS id, document_status_code AS document_status_code FROM document_status"
        cursor.execute(dataQuery) 
        for row in cursor:
            if (row['document_status_code'] is not None and row['document_status_code'].lower() == 'open'):
                docStsId = row['id']
# Get Report Charge
        charge = 0
        chargeName = ""
        strgId = 0
        strgCharge = 0    
# Get Ship count
        strg = 0
        vendorCode = ''
        dataQuery = "SELECT vendor.vendor_reference_code AS vendor_code, CASE WHEN vendor.report_charge_ids IS NOT null THEN vendor.report_charge_ids ELSE 0 END AS charge_id, 1 AS strg FROM vendor"
        dataQuery +=" LEFT JOIN part ON part.vendor_id=vendor.id"
        if (vendor != "ALL"):
            dataQuery +=" WHERE vendor.vendor_reference_code='"+vendor+"'"
        dataQuery +=" GROUP BY vendor.id, charge_id ORDER BY vendor.id, charge_id"
        cursor.execute(dataQuery) 
        for row in cursor.fetchall():
            chargeName = ""
            strgId = 0
            strgCharge = 0
            vendorCode = row['vendor_code']
            strg = row['strg']
            dataChargeQuery = "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="+str(row['charge_id'])+" LIMIT 1"
            cursor1.execute(dataChargeQuery) 
            for chargeRow in cursor1:
                strgId = chargeRow['id']
                charge = chargeRow['std_charge']
                chargeName = chargeRow['charge_name']
                print("REPORT-",vendorCode,strgId,chargeName,strg)
                strgQuery = "SELECT id FROM charge_body WHERE start_date='"+startDate+"' AND vendor_code='"+vendorCode+"' AND storage_charge_id="+str(strgId)+" AND charge_header_id IS NOT null LIMIT 1"
                cursor.execute(strgQuery)
                i=0
                for strgRow in cursor:
                    i = 1
                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,document_status_id)"
                    strgQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+vendorCode+"','"+chargeName+"',"+str(strgId)+","+str(charge)+","+str(strg)+",std_charge*box_qty,now(),'upload',now(),'upload',"+str(docStsId)+")"
#                else:
#                    strgQuery = "UPDATE charge_body SET end_date='"+str(dayDate)+"', std_charge="+str(charge)+", box_qty="+str(strg)+",document_status_id="+str(docStsId)+", 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  -reportCharge "+dataQuery
	
def main():

# 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'])
    
# Initial Parameters - vendor, from-date, to-date, charging-pattern
    vendor = "ALL"
    if (len(sys.argv) > 1):
        if (sys.argv[1] != "" and sys.argv[1] != "''"):
            vendor = sys.argv[1]
        
    dayDate = datetime.date.today().strftime('%Y-%m-%d')
    if (len(sys.argv) > 3):
        dayDate = sys.argv[3]
        
    today = datetime.datetime.strptime(dayDate, '%Y-%m-%d')	
    day = today.strftime('%a')
    # get previous Sunday Date    
    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]
    if (len(sys.argv) > 4):
        weekPattern = sys.argv[4]
    else:
        weekPattern = "1111111"
        
    weekDates = weekDays(startDate)
    i = 0
    prvMonth = weekDates[0][5:7]
    endDate = weekDates[6]
    chargeDays = ""
# processing Charges for week (or part week on change of month)
    for i in range(len(weekDates)):
        if (prvMonth != weekDates[i][5:7]):
            calcCharges(vendor,startDate,endDate,chargeDays,cnx)
            startDate = weekDates[i]
            prvMonth = weekDates[i][5:7]
            chargeDays = ""
        if weekPattern[i:i+1] == "1":
            if len(chargeDays)>0:
                chargeDays += ","
            chargeDays += "'"+str(weekDates[i])+"'"
        endDate = weekDates[i]            
    if (chargeDays != ""):
        calcCharges(vendor,startDate,endDate,chargeDays,cnx)        
    
if __name__ == '__main__':
    main()