import os
import time
import datetime
import json
import mysql.connector
from collections import OrderedDict

import default_setting

# 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 = ""
# 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"):
				qty = 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]
		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+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

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()