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='root', password='root',host='172.16.1.70',database='vims') 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 = "" 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 # format required data/time dateTime = date[0:4]+"-"+date[4:6]+'-'+date[6:8]+' '+time[0:2]+':'+time[2:4]+':'+time[4:6] # check for top / lower tier parts cursor = cnx.cursor(dictionary=True) # 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-Invalid Part Number") except UnboundLocalError: pass qty = float(expectedQty.strip()) qty = float(qty*conversionFactor) if (qty >= 1000000): qty=0 if ((convertToLt == "") or (convertToLt is None)): # Top Tier / Call-Off Order try: sqlStr = "DELETE FROM tt_ran_order WHERE ran_order='"+ranOrder+"' AND scanned_qty = 0" # cursor = cnx.cursor() cursor.execute(sqlStr) cnx.commit() except UnboundLocalError: raise 'Update Error' try: sqlStr = "INSERT INTO tt_ran_order (id,date_created,customer_reference_code,uploaded_count,ran_order,vendor,part_number,inventory_key,pack_type,dock,zone,expected_qty,required_date) VALUES(DEFAULT,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+"')" print("Insert:"+sqlStr) cursor = cnx.cursor() cursor.execute(sqlStr) cnx.commit() except UnboundLocalError: raise 'Update Error' # Lower Tier / Works Order else: # 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' timeSlot = gettimeslot.timeSlot(str(dateTime)[0:10], str(dateTime)[11:19], dock) # 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'] # Test for Existing Reference sqlStr = "SELECT id, document_reference FROM order_header WHERE time_slot = '"+str(timeSlot)+"' AND dock_destination = '"+str(dock)+"' LIMIT 1" try: cursor.execute(sqlStr) row = cursor.fetchone() if row is None: bdyLine = 0 orderType = 'STD' 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,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)+"',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'] if (row['document_reference'] == "COR000000"): 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: 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'] except UnboundLocalError as e: print(e) sqlStr = "SELECT id FROM order_body WHERE order_header_id="+str(hdrId)+" AND part_number='"+partNumber+"' LIMIT 1" # print ("bdyQry-",sqlStr) try: 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)" 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')" 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')" try: cursor.execute(sqlStr) cnx.commit() except UnboundLocalError: raise 'Update Error' bdyId = cursor.lastrowid 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' WHERE id="+str(bdyId)+" LIMIT 1" try: cursor.execute(sqlStr) cnx.commit() except UnboundLocalError: raise 'Update Error' except mysql.connector.Error as err: print("mysql Error: {}".format(err)) finally: try: cursor.close() cnx.close() except: pass def main(): file_path_json = os.path.join(dir_path, '../vimsjson/ttomsgin.json') jsonFile = open(file_path_json) 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()