import os
import time
import datetime
import json
import mysql.connector 

# import default_setting

def update_tt_ran_order(customerRef, sendCount, dateTime, values):
	try:
#		defaults = default_setting.defaultSettings()
#		cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
		cnx = mysql.connector.connect(user='root', password='root',host='172.16.1.70',database='vims')
		for segments in values:
			ranOrder = ""
			vandorCode = ""
			partNumber = ""
			inventoryKey = ""
			packType = ""
			dock = ""
			zone = ""
			qty = "0"
			date = ""
			time = ""
			for key in segments:
				print("key:"+str(key))
				value = segments[key]
				print("value"+str(value))
				if (key == "ran_order"):
					ranOrder = value
				if (key == "vendor_code"):
					vendorCode = value
				if (key == "part_number"):
					partNumber = value
				if (key == "inv_key"):
					inventoryKey = value
				if (key == "pack_type"):
					packType = value
				if (key == "dock"):
					dock = value
				if (key == "zone"):
					zone = value
				if (key == "quantity"):
					qty = value
				if (key == "date"):
					date = value
				if (key == "time"):
					time = value
				
			dateTime = date[0:4]+"-"+date[4:6]+'-'+date[6:8]+' '+time[0:2]+':'+time[2:4]+':'+time[4:6]
				
			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+qty+",'"
				sqlStr = sqlStr+dateTime+"')"
				print("Insert:"+sqlStr)
				cursor = cnx.cursor()
				cursor.execute(sqlStr)
				cnx.commit()
#				result = cursor.fetchall()
#				print (result)				
			except UnboundLocalError:
				raise 'Update Error'
		
	finally:
		try:
			cursor.close()
			cnx.close()
		except:
			pass
		
if __name__ == '__main__':
	
#	while True:
	
	jsonFile = open("c:\\python\\vimsjson\\IDOCTranOut.json")
	jsonObject = json.load(jsonFile)
		
	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"):
			update_tt_ran_order(customerRef, sendCount, dateTime, values)
			
		try:
			os.remove("c:\\python\\vimsjson\\IDOCTranOut.json")
		except:
			pass