# ------------------------------------------------------------------------------
# Convert JSON to Orders
# ------------------------------------------------------------------------------
import os
import time
import datetime
import json
import mysql.connector 

import default_setting

# Get Current Path
dir_path = os.path.dirname(os.path.realpath(__file__))

# hdr class
class hdr:
	def _init_(self):
		id = ""
		docRef = ""
		customerRef = ""
def header(connection,customerRef,orderType,date):
	hdrId = ""
	docRef = ""
	try:
		print("Delivery Date ",date)

		cursor = connection.cursor()
		
# 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[0]
		
# 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,date_created,created_by,last_updated_date,last_updated_by)"
				dataQuery += " VALUES(DEFAULT,'COR000000','"+customerRef+"','"+str(date)+"','"+str(date)+"',"+str(documentStsId)+",'"+str(orderType)+"',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[0]
				if (row[1] == "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[1]
		finally:
			cursor.close()
	except UnboundLocalError as e:
		print(e)
	
	print("hdrDocRef-",docRef)
	
	hdr.id = hdrId
	hdr.docRef = docRef
	hdr.customerRef = customerRef
	return hdr

def body(connection,hdrId,docRef,customerRef,segments,bdyId):
#	bdyId = ""
	vendorId = ""
	partNumber= ""
	advisedQty = 0
	sequence = ""
	storage = ""
	conversionFactor = 1
	
	print("bdyDocRef-",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 == "partNumber"):
			partNumber = value.strip()
		elif (key == "serialReference"):
			serialReference = value.strip()
		elif (key == "expectedQty"):
			expectedQty = value.strip()
		elif (key == "lineNo"):
			lineNo = value.strip()
		elif (key == "expectedDate"):
			expectedDate = value.strip()
		elif (key == "expectedTime"):
			expectedTime = value.strip()
	
#	if (partNumber != ""):
	if (segnam == "E2LTORI"):
		try:
# Test for Existing Part
			partId=""
			cursor = connection.cursor()
			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[0]
					vendorId = row[1]
					conversionFactor = row[2]
			except UnboundLocalError:
				raise 'Update Error'
				
			qty = float(expectedQty.strip())
			qty = float(qty*conversionFactor)
		
			dataQuery = "SELECT id FROM order_body WHERE order_header_id="+str(hdrId)+" AND part_number='"+partNumber+"' AND line_no="+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,part_id,order_header_id,customer_reference,date_created,created_by,last_updated_date,last_updated_by)"
					if (partId == ""):
						dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+lineNo+",null,"+str(hdrId)+","+customerRef+",now(),'system',now(),'system')"
					else:
						dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0-"+str(qty)+","+lineNo+","+str(partId)+","+str(hdrId)+","+customerRef+",now(),'system',now(),'system')"
					try:
						cursor.execute(dataQuery)
						connection.commit()
					except UnboundLocalError:
						raise 'Update Error'
					bdyId = cursor.lastrowid
				else:
					bdyId = row[0]
					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"
						try:
							cursor.execute(dataQuery)
							connection.commit()
						except UnboundLocalError:
							raise 'Update Error'
			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,hdrId,docRef,customerRef,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():
	global hdr, 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/idoctranout.json')
	jsonFile = open(file_path_json)
	jsonObject = json.load(jsonFile)
		
	for key in jsonObject:
		values = jsonObject[key]
# 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.id,hdr.docRef,hdr.customerRef,segments,bdyId)
			else:
				    bdyId = body(connection,hdr.id,hdr.docRef,hdr.customerRef,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()