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 = ""
def header(connection,customerRef,externalRef ,date):
	hdrId = ""
	docRef = ""
	haulierRef = ""
	try:
		print("Delivery Date ",date)
# Test for Existing Reference
		cursor = connection.cursor()
		dataQuery = "SELECT id, document_reference FROM receipt_header WHERE customer_reference="+customerRef+" LIMIT 1"
		try:
			cursor.execute(dataQuery) 
			row = cursor.fetchone()
			if row is None:
				dataQuery = "INSERT INTO receipt_header (id,document_reference,product_type_id,customer_reference,haulier_reference,vendor_id,expected_delivery_date,document_status_id,inspect,date_created,created_by,last_updated_date,last_updated_by,delivery_note)"
				dataQuery += " VALUES(DEFAULT,'RCP000000',null,'"+customerRef+"','"+haulierRef+"',1,'"+str(date)+"',1,null,now(),'system',now(),'system','"+externalRef+"')"
				try:
					cursor.execute(dataQuery)
					connection.commit()
				except UnboundLocalError:
					raise 'Update Error'
				hdrId = cursor.lastrowid
				docRef = "000000"+str(hdrId)
				docRef = "RCP"+docRef[-6:]
				dataQuery = "UPDATE receipt_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] == "RCP000000"):
					docRef = "000000"+str(hdrId)
					docRef = "RCP"+docRef[-6:]
					dataQuery = "UPDATE receipt_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
	return hdr

def body(connection,hdrId,docRef,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 == "advisedQty"):
			advisedQty = value.strip()
		elif (key == "sequence"):
			sequence = value.strip()
		elif (key == "Storage"):
			storage = value.strip()
		elif (key == "jisInd"):
			jisInd = value.strip()
	
#	if (partNumber != ""):
	if (segnam == "E2EDL24"):
		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'
		
# Receipt Header Vendor Id	
			if (vendorId != ""):
				dataQuery = "UPDATE receipt_header SET vendor_id="+str(vendorId)+",last_updated_date=now(),last_updated_by='system' WHERE id="+str(hdrId)+" AND vendor_id IS null LIMIT 1"
				try:
					cursor.execute(dataQuery)
					connection.commit()
				except UnboundLocalError:
					raise 'Update Error'
				
			qty = float(advisedQty.strip())
			qty = int(qty*conversionFactor)
		
			dataQuery = "SELECT id FROM receipt_body WHERE receipt_header_id="+str(hdrId)+" AND part_number='"+partNumber+"' LIMIT 1"
			print ("bdyQry-",dataQuery)
			try:
				cursor.execute(dataQuery) 
				row = cursor.fetchone()
				if row is None:
					dataQuery = "INSERT INTO receipt_body (id,document_reference,product_type_id,part_number,advised_qty,received_qty,difference,part_id,receipt_header_id,document_status_id,date_created,created_by,last_updated_date,last_updated_by)"
					if (partId == ""):
						dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0,null,"+str(hdrId)+",1,now(),'system',now(),'system')"
					else:
						dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0,"+str(partId)+","+str(hdrId)+",1,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 receipt_body SET document_reference='"+docRef+"',advised_qty=advised_qty+"+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 == "Z2EDL20"):
		try:
			cursor = connection.cursor()
			dataQuery = "UPDATE receipt_header SET jis_ind='"+jisInd+"',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)
	
	if (segnam == "Z2EDL24"):
		try:
			cursor = connection.cursor()
			productTypeId = None
			if (storage == "PRUFCUBE" or storage == "PRUEFCUBE"):
				dataQuery = "SELECT id AS id FROM product_type WHERE lower(product_type_code)='prufcube' LIMIT 1"
				try:
					cursor.execute(dataQuery) 
					row = cursor.fetchone()
					if row is not None:
						productTypeId = row[0]
				except UnboundLocalError:
					raise 'Product Error'
			else:
				productTypeId = None
			if (productTypeId is None):
				dataQuery = "UPDATE receipt_body SET document_reference='"+docRef+"',last_updated_date=now(),last_updated_by='"+storage+"' WHERE id="+str(bdyId)+" LIMIT 1"
			else:
				dataQuery = "UPDATE receipt_body SET document_reference='"+docRef+"',last_updated_date=now(),product_type_id="+str(productTypeId)+",last_updated_by='"+storage+"' WHERE id="+str(bdyId)+" LIMIT 1"
			print ("bdyQry-",dataQuery)
			try:
				cursor.execute(dataQuery)
				connection.commit()
			except UnboundLocalError:
				raise 'Update Error'
			finally:
				cursor.close()
		except UnboundLocalError as e:
			print(e)
		
	if (segnam == "E2EDL52"):
		try:
# Test for Existing Part
			partId=""
			qty = float(advisedQty.strip())
			cursor = connection.cursor()
			dataQuery = "SELECT receipt_body.part_id AS part_id, part.vendor_id AS vendor_id, part.conversion_factor AS conversion_factor, receipt_body.part_number AS part_number FROM receipt_body LEFT JOIN part ON receipt_body.part_id=part.id WHERE receipt_body.id = "+str(bdyId)+" LIMIT 1"
			try:
				cursor.execute(dataQuery) 
				row = cursor.fetchone()
				if row is not None:
					partId = row[0]
					vendorId = row[1]
					conversionFactor = row[2]
					partNumber = row[3]
					if (conversionFactor is not None):
						qty = int(qty*conversionFactor)
			except UnboundLocalError:
				raise 'Update Error'
			if (partNumber != ""):
				dataQuery = "SELECT id FROM receipt_detail WHERE receipt_body_id="+str(bdyId)+" AND part_number="+str(partNumber)+" AND serial_reference='"+str(serialReference)+"' LIMIT 1"
				print ("dtlQry-",dataQuery)
				try:
					cursor.execute(dataQuery) 
					row = cursor.fetchone()
					if row is None:
						dataQuery = "INSERT INTO receipt_detail (id,document_reference,product_type_id,part_number,advised_qty,received_qty,cts_qty,difference,part_id,receipt_body_id,document_status_id,serial_reference,date_created,created_by,last_updated_date,last_updated_by)"
						if (partId == "" or partId is None):
							dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0,0,null,"+str(bdyId)+",1,'"+str(serialReference)+"',now(),'system',now(),'system')"
						else:
							dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0,0,"+str(partId)+","+str(bdyId)+",1,'"+str(serialReference)+"',now(),'system',now(),'system')"
						try:
							cursor.execute(dataQuery)
							connection.commit()
						except UnboundLocalError:
							raise 'Update Error'
						dtlId = cursor.lastrowid
					else:
						dtlId = row[0]
				except UnboundLocalError as e:
					print(e)
				finally:
					cursor.close()
		except UnboundLocalError as e:
			print(e)	
		
	return bdyId

def detail(connection,hdrId,docRef,segments,bdyId):
	vendorId = ""
	partNumber= ""
	advisedQty = 0
	sequence = ""
	storage = ""
	conversionFactor = 1
	
	print("dtlDocRef-",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()
		if (key == "serialReference"):
			serialReference = value.strip()
		elif (key == "advisedQty"):
			advisedQty = value.strip()
	
	if (segnam == "E2EDL52"):
		try:
# Test for Existing Part
			partId=""
			qty = float(advisedQty.strip())
			cursor = connection.cursor()
			dataQuery = "SELECT receipt_body.part_id AS part_id, part.vendor_id AS vendor_id, part.conversion_factor AS conversion_factor, part.part_number AS part_number FROM receipt_body JOIN part ON receipt_body.part_id=part.id WHERE receipt_body.id = "+str(bdyId)+" LIMIT 1"
			try:
				cursor.execute(dataQuery) 
				row = cursor.fetchone()
				if row is not None:
					partId = row[0]
					vendorId = row[1]
					conversionFactor = row[2]
					partNumber = row[3]
					qty = int(qty*conversionFactor)
			except UnboundLocalError:
				raise 'Update Error'
			dataQuery = "SELECT id FROM receipt_detail WHERE receipt_body_id="+str(bdyId)+" AND part_id="+str(partId)+" AND serial_reference='"+str(serialReference)+"' LIMIT 1"
			print ("dtlQry-",dataQuery)
			try:
				cursor.execute(dataQuery) 
				row = cursor.fetchone()
				if row is None:
					dataQuery = "INSERT INTO receipt_detail (id,document_reference,product_type_id,part_number,advised_qty,received_qty,cts_qty,difference,part_id,receipt_body_id,document_status_id,serial_reference,date_created,created_by,last_updated_date,last_updated_by)"
					if (partId == ""):
						dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0,0,null,"+str(bdyId)+",1,'"+str(serialReference)+"',now(),'system',now(),'system')"
					else:
						dataQuery += " VALUES(DEFAULT,'"+docRef+"',null,'"+partNumber+"',"+str(qty)+",0,0,0,"+str(partId)+","+str(bdyId)+",1,'"+str(serialReference)+"',now(),'system',now(),'system')"
					try:
						cursor.execute(dataQuery)
						connection.commit()
					except UnboundLocalError:
						raise 'Update Error'
					dtlId = cursor.lastrowid
				else:
					dtlId = row[0]
			except UnboundLocalError as e:
				print(e)
			finally:
				cursor.close()
		except UnboundLocalError as e:
			print(e)
	
	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():
	try:
		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]
# Receipt Header
			if (key == "receiptHeader"):
				customerRef = values['customerReference']
				externalRef = values['externalRef']
				dateTime = values['receiptDate'][0:4]+"-"+values['receiptDate'][4:6]+'-'+values['receiptDate'][6:8]+' '+values['receiptTime'][0:2]+':'+values['receiptTime'][2:4]+':'+values['receiptTime'][4:6]
				hdr = header(connection,customerRef,externalRef,dateTime)
				bdyId = ""
# Receipt Body			
			if (key == "receiptBody"):
				try:
					if (type(values) == list):
						for segments in values:
							bdyId = body(connection,hdr.id,hdr.docRef,segments,bdyId)
					else:
						bdyId = body(connection,hdr.id,hdr.docRef,values,bdyId)
				except:
					pass
# Receipt Detail		
			if (key == "receiptDetail"):
				try:
					if (type(values) == list):
						for segments in values:
							dtlId = detail(connection,hdr.id,hdr.docRef,segments,bdyId)
					else:
						dtlId = detail(connection,hdr.id,hdr.docRef,values,bdyId)
				except:
					pass
			try:
				os.remove(file_path_json)
			except:
				pass
	except:
		pass
if __name__ == '__main__':
	main()