# ------------------------------------------------------------------------------
# Convert JSON to TT Orders
# 2021-01-04 - test that DNS exists on vendor table
# 2021-02-26 - increase timeslot-dock to 2 chars.
# 2021-03-10 - get product_type_id from time_slot_master
# 2021-03-15 - pass order_type to getTimeSlot
# 2021-03-31 - Default Product_type_id to 1.
# 2021-04-06 - Don't update order_header if already picked.
# 2021-04-14 - Create LT.Orders based on Vendor convert_to_lt flag.
# 2021-04-21 - Force 'T' RANs to be lower-tier.
# 2021-12-06 - Pull HU013 orders forward by 24 hours
# ------------------------------------------------------------------------------
import os
import time
import datetime
import json
import mysql.connector
from collections import OrderedDict

import default_setting
import gettimeslot

# Get Current Path
dir_path = os.path.dirname(os.path.realpath(__file__))

def update_tt_ran_order(customerRef, sendCount, dateTime, segments):
    try:
# Default Connection / System Settings
        defaults = default_setting.defaultSettings()
# SQL connection
        cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
# Initialise tt_ran fields
        ranOrder = ""
        vandorCode = ""
        partNumber = ""
        inventoryKey = ""
        packType = ""
        dock = ""
        zone = ""
        qty = "0"
        date = ""
        time = ""
        toLoc = ""
        status = ""
        usage = "1"
        orderType = "STD"
        bdyLine = 0
# parse out keys / values (tuples)
        for key in segments:
#            print("key:"+str(key)+":"+str(segments))
            try:
                value = segments[key]
#                print("value"+str(value))
            except:
                pass
            if (key == "ran_order"):
                ranOrder = value
            elif (key == "vendor_code"):
                vendorCode = value
            elif (key == "part_number"):
                partNumber = value
            elif (key == "inv_key"):
                inventoryKey = value
            elif (key == "pack_type"):
                packType = value
            elif (key == "dock"):
                dock = value
            elif (key == "zone"):
                zone = value
            elif (key == "quantity"):
                expectedQty = value
            elif (key == "date"):
                date = value
            elif (key == "time"):
                time = value
            elif (key == "to_location"):
                toLoc = value
            elif (key == "usage"):
                usage = value
            elif (key == "status"):
                status = value
                
# Define mysql cursor
        cursor = cnx.cursor(dictionary=True)
        
# Check for Cancellation
        if (status == "C" or status == "c"):
            dataQuery = "UPDATE order_body SET blocked = 1 WHERE part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' AND qty_transacted=0"
            try:
                cursor.execute(dataQuery)
                cnx.commit()
            except Exception as Err:
                print ('Update Error 1'+str(Err)+dataQuery)
            dataQuery = "UPDATE tt_ran_order SET completed = 1 WHERE part_number='"+partNumber+"' AND ran_order='"+ranOrder+"'"
            try:
                cursor.execute(dataQuery)
                cnx.commit()
            except Exception as Err:
                print ('Update Error 1'+str(Err)+dataQuery)
            try:
                dataQuery = "SELECT id FROM document_status WHERE lower(document_status_code)='closed' LIMIT 1"
                cursor.execute(dataQuery) 
                row = cursor.fetchone()
                docId = 0
                if row is not None:
                    docId = row['id']
                if docId != 0 and docId is not None:
                    dataQuery = "SELECT order_header_id FROM order_body WHERE part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' LIMIT 1"
                    cursor.execute(dataQuery) 
                    row = cursor.fetchone()
                    if row is not None:
                        id = row['order_header_id']
                        dataQuery = "SELECT id FROM order_body WHERE order_header_id = "+str(id)+" AND (blocked = 0 OR blocked IS null) LIMIT 1"
                        cursor.execute(dataQuery) 
                        row = cursor.fetchone()
                        if row is None:
                            dataQuery = "UPDATE order_header SET document_status_id="+str(docId)+" WHERE id="+str(id)+" LIMIT 1"
                            cursor.execute(dataQuery)
                            cnx.commit()
            except Exception as Err:
                print ('Update Error 1'+str(Err)+dataQuery)
                
            print ('RAN:',ranOrder,'Cancelled')
            try:
                cursor.close()
                cnx.close()
            except:
                pass
            return
            
# check for 1234 timeslot
        if (time[0:4] == "1234"):
            orderType="EMG"
        else:
            orderType="STD"         
# test for valid DNS Code   
        i = 0
        vendorLt = 0
        sqlStr = "SELECT id,convert_to_lt FROM vendor WHERE duns_code='"+str(vendorCode)+"' LIMIT 1"
        try:
            cursor.execute(sqlStr) 
            for row in cursor.fetchall():
                i=i+1
                vendorLt = row['convert_to_lt']
        except Exception as Err:
            print ('Select Error 1'+str(Err)+dataQuery)
#        print("DNS",vendorCode,str(i))    
        if (i==0 or vendorCode == "" or vendorCode is None):
            print("FAIL-Invalid Vendor",vendorCode)
            return ""
                
# format required data/time             
        dateTime = date[0:4]+"-"+date[4:6]+'-'+date[6:8]+' '+time[0:2]+':'+time[2:4]+':'+time[4:6]

# Test for Existing Part
        partId=""
        convertToLt = ""
        conversionFactor = 1
        sqlStr = "SELECT id, vendor_id, conversion_factor, convert_to_lt AS convert_to_lt FROM part WHERE part_number = '"+partNumber+"' LIMIT 1"
        try:
            cursor.execute(sqlStr) 
            row = cursor.fetchone()
            if row is not None:
                partId = row['id']
                vendorId = row['vendor_id']
                conversionFactor = row['conversion_factor']
                convertToLt = row['convert_to_lt']
            else:
                print("FAIL-Unknown Part Number",partNumber)
        except UnboundLocalError:
            pass
# get / update pack_type_id
        packId = None;
        sqlStr = "SELECT id FROM pack_type WHERE name='"+packType+"' LIMIT 1"
        try:
            cursor.execute(sqlStr) 
            row = cursor.fetchone()
            if row is not None:
                packId = row['id']
            else:
                sqlStr = "INSERT INTO pack_type (id,date_created,created_by,last_updated_date,last_updated_by,name)"
                sqlStr += " VALUES(DEFAULT,now(),'system',now(),'system','"+packType+"')"
                cursor.execute(sqlStr)
                cnx.commit()
                packId = cursor.lastrowid 
        except UnboundLocalError:
            pass
# update part
        try:
            sqlStr = "UPDATE part SET inventory_key='"+inventoryKey+"'"
            if (packId != None):
                sqlStr +=",pack_type_id="+str(packId)
            sqlStr +=",last_updated=now() WHERE part_number ='"+partNumber+"' LIMIT 1"
            cursor.execute(sqlStr)
            cnx.commit()
        except UnboundLocalError:
            pass
            
        qty = float(expectedQty.strip())
        qty = float(qty*conversionFactor)
        if (qty >= 1000000):
            qty=0

# set lower-tier flag for 'T' RANs - HARD-CODED
        if ranOrder[0:1] == "T":
            convertToLt = "T1"
            orderType = "TR"
            time = '1700'
            zone = "TRIAL"
# set lower-tier flag based on usage - HARD-CODED
        if usage == "2":
            convertToLt = "T"
            orderType = "TR"
            time = '1700'
        elif usage == "3":
            convertToLt = "IP"
            orderType = "STD"
            time = '1200'
        elif usage == "4":
            convertToLt = "SV"
            orderType = "SRV"
            time = '0900'
        elif usage == "5":
            convertToLt = "T"
            orderType = "TR"
            time = '1700'

# Lower Tier Vendor
        if (vendorLt == 1 and (convertToLt == "" or convertToLt is None)):
            convertToLt = dock

        if ((convertToLt == "") or (convertToLt is None)):
# Top Tier / Call-Off Order

# Get Timeslot
            dateTime = date[0:4]+"-"+date[4:6]+'-'+date[6:8]+' '+time[0:2]+':'+time[2:4]+':'+'00'
# subtract 1 day from Hutchinson
            if (vendorCode == "0837021"):
                year = int(date[0:4])
                month = int(date[4:6])
                day = int(date[6:8])
                dayDt = datetime.datetime(year, month, day)
                dateTime = dayDt + datetime.timedelta(days=-1)
            timeSlot = gettimeslot.timeSlot(str(dateTime)[0:10], str(dateTime)[11:19], dock[0:2],orderType)
            if (timeSlot == ""):
                print("No TimeSlot",str(dateTime))
                return ""
            print("TT.timeslot",str(timeSlot))
            dupRan = 0
            qtyTran = 0
            cutOff = 0

            try:
                sqlStr = "SELECT id,scanned_qty,(CURRENT_DATE - DATE(last_updated)) AS cut_off FROM tt_ran_order WHERE id>0 AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' LIMIT 1"
                cursor.execute(sqlStr) 
                row = cursor.fetchone()
                if row is not None:
                    ttRanId = row['id']
                    qtyTran = row['scanned_qty']
                    cutOff = row['cut_off']
                    
                    print("cut_off",cutOff)
                    if (qtyTran <= 0 or cutOff >= 30):
                        dupRan = 0
                        sqlStr = "DELETE FROM tt_ran_order WHERE id>0 AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"'"
                        try:
                            cursor.execute(sqlStr)
                            cnx.commit()
                        except Exception as Err:
                            print ('Update Error '+str(Err)+sqlStr)
                        print("TT-RAN Deleted",ranOrder)
                    else:
                        dupRan = 1
                        sqlStr = "INSERT INTO transaction_history(id,short_code,transaction_reference_code,date_created,created_by,last_updated,last_updated_by,ran_or_order,part_number,txn_qty) VALUES(DEFAULT,'DUPRAN','DUPRAN',now(),'sys',now(),'sys','"+ranOrder+"','"+partNumber+"',"+str(qty)+")"
                        try:
                            cursor.execute(sqlStr)
                            cnx.commit()
                        except Exception as Err:
                            print ('Update Error '+str(Err)+sqlStr)
                        print("Duplicate Ran",ranOrder)
                        return ""
            except Exception as Err:
                print ('Update Error '+str(Err)+sqlStr)
#            print('Delete')
            try:
                sqlStr = "SELECT id FROM tt_ran_order WHERE ran_order='"+ranOrder+"' LIMIT 1"
                cursor.execute(sqlStr) 
                row = cursor.fetchone()
                if row is None:
                    sqlStr = "INSERT INTO tt_ran_order (id,date_created,last_updated,customer_reference_code,uploaded_count,ran_order,vendor,part_number,inventory_key,pack_type,dock,zone,expected_qty,required_date,to_location) VALUES(DEFAULT,now(),now()"
                    sqlStr = sqlStr+",'"
                    sqlStr = sqlStr+customerRef
                    sqlStr = sqlStr+"',"
                    sqlStr = sqlStr+sendCount
                    sqlStr = sqlStr+",'"
                    sqlStr = sqlStr+ranOrder+"','"
                    sqlStr = sqlStr+vendorCode+"','"
                    sqlStr = sqlStr+partNumber+"','"
                    sqlStr = sqlStr+inventoryKey+"','"
                    sqlStr = sqlStr+packType+"','"
                    sqlStr = sqlStr+dock+"','"
                    sqlStr = sqlStr+zone+"',"
                    sqlStr = sqlStr+str(qty)+",'"
                    sqlStr = sqlStr+dateTime+"','"
                    sqlStr = sqlStr+toLoc+"')"
    #                print("Insert:"+sqlStr)
                    cursor.execute(sqlStr)
                    cnx.commit()    
            except UnboundLocalError:
                print ('Update Error',sqlStr)
# Lower Tier / Works Order
        else:
            print("LOWER TIER")
            try:
                dupRan = 0
                qtyTran = 0
                cutOff = 0

                sqlStr = "SELECT id,qty_transacted,(CURRENT_DATE - DATE(last_updated_date)) AS cut_off FROM order_body WHERE id>0 AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' LIMIT 1"
                cursor.execute(sqlStr) 
                row = cursor.fetchone()
                if row is not None:
                    ordBdyId = row['id']
                    qtyTran = row['qty_transacted']
                    cutOff = row['cut_off']
                    
                    print("cut_off",cutOff)
                    if (qtyTran <= 0 or cutOff >= 30):
                        dupRan = 0
                        sqlStr = "DELETE FROM order_body WHERE id>0 AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"'"
                        try:
                            cursor.execute(sqlStr)
                            cnx.commit()
                        except Exception as Err:
                            print ('Update Error '+str(Err)+sqlStr)
                        print("RAN Deleted",ranOrder)
                    else:
                        dupRan = 1
                        sqlStr = "INSERT INTO transaction_history(id,short_code,transaction_reference_code,date_created,created_by,last_updated,last_updated_by,ran_or_order,part_number,txn_qty) VALUES(DEFAULT,'DUPRAN','DUPRAN',now(),'sys',now(),'sys','"+ranOrder+"','"+partNumber+"',"+str(qty)+")"
                        try:
                            cursor.execute(sqlStr)
                            cnx.commit()
                        except Exception as Err:
                            print ('Update Error '+str(Err)+sqlStr)
                        print("Duplicate Ran",ranOrder)
                        return ""
# Use convert to lt as dock
                dock = convertToLt
# Get Timeslot
                dateTime = date[0:4]+"-"+date[4:6]+'-'+date[6:8]+' '+time[0:2]+':'+time[2:4]+':'+'00'
# subtract 1 day from Hutchinson
                if (vendorCode == "0837021"):
                    year = int(date[0:4])
                    month = int(date[4:6])
                    day = int(date[6:8])
                    dayDt = datetime.datetime(year, month, day)
                    dateTime = dayDt + datetime.timedelta(days=-1)
                    date = str(dateTime)
                    dateTime = date[0:4]+"-"+date[5:7]+'-'+date[8:10]+' '+time[0:2]+':'+time[2:4]+':'+'00'
                timeSlot = gettimeslot.timeSlot(str(dateTime)[0:10], str(dateTime)[11:19], dock[0:2], orderType)
                if (timeSlot == ""):
                    print("Invalid Timeslot")
                    return ""
                print("LT.Timeslot",str(timeSlot))
# part & vendor values
                partId = ""
                vendorId = ""
                sqlStr = "SELECT part.id AS part_id,vendor.id AS vendor_id,part.conversion_factor AS conversion_factor FROM part JOIN vendor ON part.vendor_id=vendor.id WHERE part.part_number='"+partNumber+"' LIMIT 1"
                cursor.execute(sqlStr) 
                row = cursor.fetchone()
                if row is not None:
                    partId = row['part_id']
                    vendorId = row['vendor_id']
                    conversionFactor = row['conversion_factor']
# Get id for 'open' document status     
                sqlStr = "SELECT id FROM document_status WHERE lower(document_status_code) = 'open' LIMIT 1"
                cursor.execute(sqlStr) 
                row = cursor.fetchone()
                documentStsId = row['id']
# Get product_type_id from timeslot
                productType = ""
                productTypeId=1
                dataQuery = "SELECT time_slot_master.product_type_id AS product_type_id, product_type.product_type_code AS product_type_code FROM time_slot_master LEFT JOIN product_type ON time_slot_master.product_type_id=product_type.id WHERE destination = '"+str(dock)[0:2]+"' AND time_slot='"+timeSlot[11:19]+"' LIMIT 1"
                cursor.execute(dataQuery) 
                row = cursor.fetchone()
                if row is None:
                    dataQuery = "SELECT time_slot_master.product_type_id AS product_type_id, product_type.product_type_code AS product_type_code FROM time_slot_master LEFT JOIN product_type ON time_slot_master.product_type_id=product_type.id WHERE destination = '"+str(dock)[0:1]+"' AND time_slot='"+timeSlot[11:19]+"' LIMIT 1"
                    cursor.execute(dataQuery) 
                    row = cursor.fetchone()
                if row is None:
                    productTypeId = 1
                    productType = ""
                else:
                    productTypeId = row['product_type_id']
                    productType = row['product_type_code']
                if (productTypeId is None):
                    productTypeId = 1
#                print("PRODTYPE",productType,productTypeId)
# Test for Existing Reference
 #               sqlStr = "SELECT id, document_reference FROM order_header WHERE time_slot = '"+str(timeSlot)+"' AND dock_destination = '"+str(dock)+"' LIMIT 1"
                sqlStr = "SELECT id, document_reference,customer_reference FROM order_header WHERE time_slot = '"+str(timeSlot)+"' AND dock_destination = '"+str(dock)[0:2]+"'"
                sqlStr +=" AND EXISTS(SELECT id FROM document_status WHERE order_header.document_status_id=document_status.id AND lower(document_status.document_status_code) IN('open','allocated'))"
                sqlStr +=" LIMIT 1"
                try:
                    cursor.execute(sqlStr) 
                    row = cursor.fetchone()
                    if row is None:
                        bdyLine = 0
                        orderType = 'STD'
                        try:
                            customerReference = str(timeSlot[0:4])+str(timeSlot[5:7])+str(timeSlot[8:10])+" "+str(timeSlot[11:13])+str(timeSlot[14:16])+str(dock)[0:2]
                        except:
                            customerReference = None
                        if (customerReference == None):
                            sqlStr = "INSERT INTO order_header (id,document_reference,customer_reference,expected_delivery_time,time_slot,document_status_id,order_type,consignee,ship_to,dock_destination,product_type,date_created,created_by,last_updated_date,last_updated_by)"
                            sqlStr += " VALUES(DEFAULT,'COR000000',null,'"+str(dateTime)+"','"+str(timeSlot)+"',"+str(documentStsId)+",'"+str(orderType)+"','NI001','NI001','"+str(dock)[0:2]+"','"+productType+"',now(),'system',now(),'system')"
                        else:
                            sqlStr = "INSERT INTO order_header (id,document_reference,customer_reference,expected_delivery_time,time_slot,document_status_id,order_type,consignee,ship_to,dock_destination,product_type,date_created,created_by,last_updated_date,last_updated_by)"
                            sqlStr += " VALUES(DEFAULT,'COR000000','"+str(customerReference)+"','"+str(dateTime)+"','"+str(timeSlot)+"',"+str(documentStsId)+",'"+str(orderType)+"','NI001','NI001','"+str(dock)[0:2]+"','"+productType+"',now(),'system',now(),'system')"
                        try:
                            cursor.execute(sqlStr)
                            cnx.commit()
                        except UnboundLocalError:
                            raise 'Update Error'
                        hdrId = cursor.lastrowid
                        docRef = "000000"+str(hdrId)
                        docRef = "COR"+docRef[-6:]
                        sqlStr = "UPDATE order_header SET document_reference='"+docRef+"' WHERE id="+str(hdrId)+" LIMIT 1"
                        try:
                            cursor.execute(sqlStr)
                            cnx.commit()
                        except UnboundLocalError:
                            raise 'Update Error'
                    else:
                        hdrId = row['id']
                        customerReference = row['customer_reference']
                        if (row['document_reference'] == "COR000000"):
                            docRef = "000000"+str(hdrId)
                            docRef = "COR"+docRef[-6:]
                            sqlStr = "UPDATE order_header SET document_reference='"+docRef+"', last_updated_date=now(), last_updated_by='system' WHERE id="+str(hdrId)+" LIMIT 1"
                            try:
                                cursor.execute(sqlStr)
                                cnx.commit()
                            except UnboundLocalError:
                                raise 'Update Error'
                        else:
                            docRef = row['document_reference']
                        sqlStr = "SELECT line_no AS line_no FROM order_body WHERE order_header_id="+str(hdrId)+" ORDER BY line_no DESC LIMIT 1"
                        cursor.execute(sqlStr) 
                        row = cursor.fetchone()
                        if row is not None:
                            bdyLine = row['line_no']
    #                print("TEST",sqlStr)
                except Exception as e:
                    print("ERROR",str(e),sqlStr)
                
                try:
                    if (customerReference == None):
                       customerReference = str(timeSlot[0:4])+str(timeSlot[5:7])+str(timeSlot[8:10])+" "+str(timeSlot[11:13])+str(timeSlot[14:16])+str(dock)[0:2]
                    sqlStr = "SELECT id FROM order_body WHERE order_header_id="+str(hdrId)+" AND part_number='"+partNumber+"' AND ran_order='"+ranOrder+"' LIMIT 1"
    #                print ("bdyQry-",sqlStr)
                    cursor.execute(sqlStr)
                    row = cursor.fetchone()
                    if row is None:
                        bdyLine=bdyLine+1
                        sqlStr = "INSERT INTO order_body (id,document_reference,product_type_id,part_number,qty_expected,qty_transacted,difference,line_no,ran_order,part_id,order_header_id,customer_reference,zone_destination,dock_code,sequence,date_created,created_by,last_updated_date,last_updated_by,to_location,expected_delivery_time)"
                        sqlStr +=" VALUES(DEFAULT,'"+docRef+"'"
                        if (productTypeId != 0):
                            sqlStr +=","+str(productTypeId)
                        else:
                            sqlStr +=",null"
                        sqlStr +=",'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(bdyLine)+",'"+ranOrder+"'"
                        if (partId != ""):
                            sqlStr +=","+str(partId)
                        else:
                            sqlStr +=",null"
                        sqlStr +=","+str(hdrId)+",'"+customerReference+"','"+zone+"','"+dock+"',0,now(),'system',now(),'system','"+toLoc+"','"+str(dateTime)+"')"

    #                    if (partId == ""):
    #                        sqlStr += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(bdyLine)+",'"+ranOrder+"',null,"+str(hdrId)+",'"+hdr.customerRef+"','"+zone+"','"+dock+"',0,now(),'system',now(),'system','"+toLoc+"')"
    #                    else:
    #                        sqlStr += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(bdyLine)+",'"+ranOrder+"',"+str(partId)+","+str(hdrId)+",'"+customerRef+"','"+zone+"','"+dock+"',0,now(),'system',now(),'system','"+toLoc+"')"
                        try:
                            cursor.execute(sqlStr)
                            cnx.commit()
                        except UnboundLocalError:
                            raise 'Update Error'
                        bdyId = cursor.lastrowid
    #                    print("INSERT Body",sqlStr)
                    else:
                        bdyId = row['id']
                        if ((qty != "") and (qty is not None)):
    #                        sqlStr = "UPDATE order_body SET document_reference='"+docRef+"',qty_expected=qty_expected+"+str(qty)+",difference=qty_transacted-qty_expected,last_updated_date=now(),last_updated_by='system',to_location='"+toLoc+"' WHERE id="+str(bdyId)+" LIMIT 1"
                            sqlStr = "UPDATE order_body SET document_reference='"+docRef+"',qty_expected="+str(qty)+",difference=qty_transacted-qty_expected,last_updated_date=now(),last_updated_by='system',to_location='"+toLoc+"' WHERE id="+str(bdyId)+" LIMIT 1"
                            try:
                                cursor.execute(sqlStr)
                                cnx.commit()
                            except UnboundLocalError:
                                print ('Update Error')
    #                        print("UPDATE Body",sqlStr)
# populate assembly_forecast_build table
                    sqlStr = "SELECT id FROM parent_bom WHERE parent_part_number='"+str(partNumber)+"' LIMIT 1"
                    cursor.execute(sqlStr)
                    row = cursor.fetchone()
                    if row is not None:
                        sqlStr = "SELECT id FROM assembly_forecast_build WHERE ran_order='"+str(ranOrder)+"' LIMIT 1"
                        cursor.execute(sqlStr)
                        row = cursor.fetchone()
                        if row is None:
                            sqlStr = "INSERT INTO assembly_forecast_build (id,supplier_id,part_id,part_number,required_date,ran_order,qty_expected,qty_built,processed,date_created,created_by,last_updated,last_updated_by)"
                            sqlStr +=" VALUES(DEFAULT,"+str(vendorId)+","+str(partId)+",'"+partNumber+"','"+str(dateTime)+"','"+ranOrder+"',"+str(qty)+",0,0,now(),'system',now(),'system')"
                            try:
                                cursor.execute(sqlStr)
                                cnx.commit()
                            except UnboundLocalError:
                                print ('Update Error')
                except mysql.connector.Error as err:
                    print("mysql Error:",err,sqlStr)
            except Exception as err:
                print("LT ERROR",str(err),sqlStr)
    except Exception as err:
        print("ERROR-",str(err))
    finally:
        try:
            cursor.close()
            cnx.close()
        except:
            pass

def main():
    file_path_json = os.path.join(dir_path, '../vimsjson/ttomsgin.json')
    try:
        jsonFile = open(file_path_json)
    except:
        return
    jsonObject = json.load(jsonFile, object_pairs_hook=OrderedDict)
#    print("jsonObject:",jsonObject)

    for key in jsonObject:
        values = jsonObject[key]
        if (key == "suppordrHeader"):
            customerRef = values['hdrref']
            sendCount = values['send_count']
            dateTime = values['date'][0:4]+"-"+values['date'][4:6]+'-'+values['date'][6:8]+' '+values['time'][0:2]+':'+values['time'][2:4]+':'+values['time'][4:6]
            
        if (key == "suppordrBody"):
            if (type(values) == list):
                for segments in values:
                    update_tt_ran_order(customerRef, sendCount, dateTime, segments)
            else:
                update_tt_ran_order(customerRef, sendCount, dateTime, values)
            
    try:
        os.remove(file_path_json)
    except:
        pass
            
if __name__ == '__main__':
    main()