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=""
		conversionFactor = 1
		sqlStr = "SELECT id, vendor_id, conversion_factor 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']
		except UnboundLocalError:
			pass
				
		qty = float(expectedQty.strip())
		qty = float(qty*conversionFactor)
		if (qty >= 1000000):
			qty=0
		
		convertToLt = ""
		sqlStr = "SELECT 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:
				convertToLt = row['convert_to_lt']
		except UnboundLocalError:
				raise 'Update Error'
		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()