# ------------------------------------------------------------------------------
# Convert JSON to Orders
# ------------------------------------------------------------------------------
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 header(connection,customerRef,orderType,date,hdr):
	hdrId = ""
	docRef = ""
	try:
		print("Delivery Date ",date)

#		cursor = connection.cursor()
		cursor = connection.cursor(dictionary=True)
		
# Get id for 'open' document status		
		dataQuery = "SELECT id FROM document_status WHERE lower(document_status_code) = 'open' LIMIT 1"
		cursor.execute(dataQuery) 
		row = cursor.fetchone()
		documentStsId = row['id']
		
# Test for Existing Reference		
		dataQuery = "SELECT id, document_reference FROM order_header WHERE customer_reference="+customerRef+" LIMIT 1"
		try:
			cursor.execute(dataQuery) 
			row = cursor.fetchone()
			if row is None:
				dataQuery = "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)"
				dataQuery += " VALUES(DEFAULT,'COR000000','"+customerRef+"','"+str(date)+"','"+str(date)+"',"+str(documentStsId)+",'"+str(orderType)+"','NI001','NI001','',now(),'system',now(),'system')"
				try:
					cursor.execute(dataQuery)
					connection.commit()
				except UnboundLocalError:
					raise 'Update Error'
				hdrId = cursor.lastrowid
				docRef = "000000"+str(hdrId)
				docRef = "COR"+docRef[-6:]
				dataQuery = "UPDATE order_header SET document_reference='"+docRef+"' WHERE id="+str(hdrId)+" LIMIT 1"
				try:
					cursor.execute(dataQuery)
					connection.commit()
				except UnboundLocalError:
					raise 'Update Error'
			else:
				hdrId = row['id']
				if (row['document_reference'] == "COR000000"):
					docRef = "000000"+str(hdrId)
					docRef = "COR"+docRef[-6:]
					dataQuery = "UPDATE order_header SET document_reference='"+docRef+"' WHERE id="+str(hdrId)+" LIMIT 1"
					try:
						cursor.execute(dataQuery)
						connection.commit()
					except UnboundLocalError:
						raise 'Update Error'
				else:
					docRef = row['document_reference']
		finally:
			cursor.close()
	except UnboundLocalError as e:
		print(e)
	
	print("hdrDocRef-",docRef)
#	print("hdrId-",hdrId)
	hdr.id = hdrId
	hdr.docRef = docRef
	hdr.customerRef = customerRef
	return hdr

def body(connection,hdr,segments,bdyId):
#	bdyId = ""
	vendorId = ""
	partNumber= ""
	advisedQty = 0
	sequence = ""
	storage = ""
	conversionFactor = 1
	lineNo = 0
	segnam = ""
	zone = ""
	
	print("bdyDocRef-",hdr.docRef)
	
	for key in segments:
#		print("key:"+str(key)+":"+str(segments)+"-"+str(bdyId))
		try:
			value = segments[key]
#			print("value"+str(value))
		except:
			pass
		if (key == "segnam"):
			segnam = value.strip()
		elif (key == "ran_order"):
			ranOrder = value.strip()
		elif (key == "part_number"):
			partNumber = value.strip()
		elif (key == "serialReference"):
			serialReference = value.strip()
		elif (key == "quantity"):
			expectedQty = value.strip()
		elif (key == "lineNo"):
			lineNo = value.strip()
		elif (key == "expectedDate"):
			expectedDate = value.strip()
		elif (key == "expectedTime"):
			expectedTime = value.strip()
		elif (key == "dock"):
			dock = value.strip()
		elif (key == "zone"):
			zone = value.strip()
	
	if (partNumber != ""):
		try:
# Test for Existing Part
			partId=""
			cursor = connection.cursor(dictionary=True)
			dataQuery = "SELECT id, vendor_id, conversion_factor FROM part WHERE part_number = '"+partNumber+"' LIMIT 1"
			try:
				cursor.execute(dataQuery) 
				row = cursor.fetchone()
				if row is not None:
					partId = row['id']
					vendorId = row['vendor_id']
					conversionFactor = row['conversion_factor']
			except UnboundLocalError:
				raise 'Update Error'
				
			qty = float(expectedQty.strip())
			qty = float(qty*conversionFactor)
		
			dataQuery = "SELECT id FROM order_body WHERE order_header_id="+str(hdr.id)+" AND part_number='"+partNumber+"' AND line_no="+str(lineNo)+" LIMIT 1"
			print ("bdyQry-",dataQuery)
			try:
				cursor.execute(dataQuery)
				row = cursor.fetchone()
				if row is None:
					dataQuery = "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,date_created,created_by,last_updated_date,last_updated_by)"
					if (partId == ""):
						dataQuery += " VALUES(DEFAULT,'"+hdr.docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(lineNo)+",'"+ranOrder+"',null,"+str(hdr.id)+",'"+hdr.customerRef+"','"+zone+"',now(),'system',now(),'system')"
					else:
						dataQuery += " VALUES(DEFAULT,'"+hdr.docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+str(lineNo)+",'"+ranOrder+"',"+str(partId)+","+str(hdr.id)+",'"+hdr.customerRef+"','"+zone+"',now(),'system',now(),'system')"
					try:
						cursor.execute(dataQuery)
						connection.commit()
					except UnboundLocalError:
						raise 'Update Error'
					bdyId = cursor.lastrowid
				else:
					bdyId = row['id']
					if ((qty != "") and (qty is not None)):
#						dataQuery = "UPDATE order_body SET document_reference='"+docRef+"',qty_expected=qty_expected+"+str(qty)+",last_updated_date=now(),last_updated_by='system' WHERE id="+str(bdyId)+" LIMIT 1"
						dataQuery = "UPDATE order_body SET document_reference='"+hdr.docRef+"',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(dataQuery)
							connection.commit()
						except UnboundLocalError:
							raise 'Update Error'
			except mysql.connector.Error as err:
				print("mysql Error: {}".format(err))
			finally:
				cursor.close()
		except UnboundLocalError as e:
			print(e)
			
	if (segnam == "Z2LTORH"):
		try:
			dateTime = expectedDate[0:4]+"-"+expectedDate[4:6]+'-'+expectedDate[6:8]+' '+expectedTime[0:2]+':'+expectedTime[2:4]+':'+expectedTime[4:6]
			cursor = connection.cursor()
			dataQuery = "UPDATE order_header SET expected_delivery_time='"+dateTime+"',last_updated_date=now(),last_updated_by='system' WHERE id="+str(hdrId)+" LIMIT 1"
			print ("hdrQry-",dataQuery)
			try:
				cursor.execute(dataQuery)
				connection.commit()
			except UnboundLocalError:
				raise 'Update Error'
			finally:
				cursor.close()
		except UnboundLocalError as e:
			print(e)
		
	return bdyId

def detail(connection,hdr,segments,bdyId):
	dtlId = ""
	return dtlId
	
# timestamp value	
def getts():
	ts = time.time()
	ts = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
	return ts

def main():
# hdr class
	class hdr_class:
#		def _init_(self):
		id = ""
		docRef = ""
		customerRef = ""

#	global bdyId, dtlId
	hdr = hdr_class
	bdyId = ''
	dtlId = ''
# Default Connection / System Settings
	defaults = default_setting.defaultSettings()
# mySql Connector
	connection = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
	
	file_path_json = os.path.join(dir_path, '../vimsjson/lromsgin.json')
	jsonFile = open(file_path_json)
	jsonObject = json.load(jsonFile, object_pairs_hook=OrderedDict)
#	print("jsonObject:",jsonObject)	
	for key in jsonObject:
		values = jsonObject[key]
# Order Header
		if (key == "lroHeader"):
			customerRef = values['hdrref']
			orderType = values['send_type']
#			dateTime = getts()
			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]
			hdr = header(connection,customerRef,orderType,dateTime,hdr)
			bdyId = ""
# Order Body			
		if ((key == "lroBody") and (hdr.id is not None)):
			if (type(values) == list):
				for segments in values:
#					bdyId = body(connection,hdr.id,hdr.docRef,hdr.customerRef,segments,bdyId)
					bdyId = body(connection,hdr,segments,bdyId)
			else:
#				    bdyId = body(connection,hdr.id,hdr.docRef,hdr.customerRef,values,bdyId)
				bdyId = body(connection,hdr,values,bdyId)
# Order Detail		
		if (key == "lroDetail"):
			if (type(values) == list):
				for segments in values:
					dtlId = detail(connection,hdr,segments,bdyId)
			else:
				    dtlId = detail(connection,hdr,values,bdyId)
# Order Header
		if (key == "vimsorderHeader"):
			customerRef = values['customerReference']
			orderType = values['orderType']
			dateTime = getts()
			hdr = header(connection,customerRef,orderType,dateTime)
			bdyId = ""
# Order Body			
		if (key == "vimsorderBody"):
			if (type(values) == list):
				for segments in values:
					bdyId = body(connection,hdr,segments,bdyId)
			else:
				    bdyId = body(connection,hdr,values,bdyId)
# Order Detail		
		if (key == "vimsorderDetail"):
			if (type(values) == list):
				for segments in values:
					dtlId = detail(connection,hdr.id,hdr.docRef,hdr.customerRef,segments,bdyId)
			else:
				    dtlId = detail(connection,hdr.id,hdr.docRef,hdr.customerRef,values,bdyId)
			
		try:
			os.remove(file_path_json)
		except:
			pass
			
if __name__ == '__main__':
	main()