import os
import time
import datetime
import json
import mysql.connector 
import csv

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,'"+str(customerRef)+"','"+str(haulierRef)+"',1,'"+str(date)+"',1,null,now(),'system',now(),'system','"+str(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,",DATA:",segments)
	
	for key in segments:
#		print("key:"+str(key)+":"+str(segments[key])+"-"+str(bdyId))
		try:
			value = segments[key]
#			print("value"+str(value))
		except:
			pass
		if (key.lower() == "segnam"):
			segnam = value.strip()
		elif (key.lower() == "partnumber"):
			partNumber = value.strip()
		elif (key.lower() == "matnr"):
			partNumber = value.strip()
		elif (key.lower() == "serialreference"):
			serialReference = value.strip()
		elif (key.lower() == "prodn"):
			serialReference = value.strip()
		elif (key.lower() == "advisedqty"):
			advisedQty = value.strip()
		elif (key.lower() == "lfimg"):
			advisedQty = value.strip()
		elif (key.lower() == "squnt"):
			advisedQty = value.strip()
		elif (key.lower() == "sequence"):
			sequence = value.strip()
		elif (key.lower() == "posnr"):
			sequence = value.strip()
		elif (key.lower() == "storage"):
			storage = value.strip()
		elif (key.lower() == "storage_info"):
			storage = value.strip()
		elif (key.lower() == "jisind"):
			jisInd = value.strip()
		elif (key.lower() == "jis_ind"):
			jisInd = value.strip()
	
#	if (partNumber != ""):
#	print("SEGMENT:",segnam)
	if (segnam == "E2EDL24"):
		try:
# Test for Existing Part
			partId=""
			conversionFactor=1
			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 mysql.connector.Error as err:
				print('Update Error',err,'-',dataQuery)
# 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 mysql.connector.Error as err:
					print('Update Error',err,'-',dataQuery)
#			print("TEST1:",vendorId,"-",partNumber,"-",hdrId,"-",advisedQty,"-",conversionFactor)
			try:
				qty = float(advisedQty.strip())
				qty = int(qty*conversionFactor)
			except:
				qty=0
			
			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 mysql.connector.Error as err:
						print('Update Error',err,'-',dataQuery)
					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 mysql.connector.Error as err:
				print('Update Error',err,'-',dataQuery)
			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 mysql.connector.Error as err:
					print ('Update Error',err)
			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 mysql.connector.Error as err:
				print('Update Error',err,'-',dataQuery)
			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)
					else:
						qty = 1000			
			except mysql.connector.Error as err:
				print('Update Error',err,'-',dataQuery)
#			print("SQL1:",dataQuery)
			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 mysql.connector.Error as err:
							print ('Update Error',err)
						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, updated
		updated = False
# 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'])

# Read csv file		
		file_path_csv = os.path.join(dir_path, '../vimsin/idoctranin.txt')
		with open(file_path_csv) as csv_file:
		
			print("CSV")
			
			values = {}
			updated = False
			csv_reader = csv.reader(csv_file, delimiter=',')
			line_count = 0
			for row in csv_reader:
				customerRef = ""
				externalRef = ""
				dateTime = ""
				try:
					if (row[0] == "SEGNAM:E2EDL20"):
						for segments in row:
							if (segments.split(":")[0] == "VBELN"):
								customerRef = segments.split(":")[1]
#								print("customerRef:",customerRef)
							if (segments.split(":")[0] == "LIFEX"):
								externalRef = segments.split(":")[1]
							if (segments.split(":")[0] == "PODAT"):
								dateTime = segments.split(":")[1][0:4]+"-"+segments.split(":")[1][4:6]+'-'+segments.split(":")[1][6:8]
							if (segments.split(":")[0] == "POTIM"):
								dateTime = dateTime+' '+segments.split(":")[1][0:2]+':'+segments.split(":")[1][2:4]+':'+segments.split(":")[1][4:6]
						print("E2EDL20:customerRef:",customerRef,",externalRef:",externalRef,",dateTime:",str(dateTime))
						hdr = header(connection,customerRef,externalRef,dateTime)
						bdyId = ""
						if (hdr.docRef != ""):
							updated = True
					if (row[0] == "SEGNAM:Z2EDL20"):
						try:
							values = {}
							for segments in row:
								values[segments.split(":")[0]] = segments.split(":")[1]
#							print("Z2EDL20:",values,"-",hdr.id)	
							bdyId = body(connection,hdr.id,hdr.docRef,values,bdyId)
						except:
#							pass
							print("ERROR1-Z2EDL20:",values)
					if (row[0] == "SEGNAM:E2EDL24"):
						try:
							values = {}
							for segments in row:
								values[segments.split(":")[0]] = segments.split(":")[1]
#							print("E2EDL24:",values)
							bdyId = body(connection,hdr.id,hdr.docRef,values,bdyId)
						except:
#							pass
							print("ERROR2-E2EDL24:",values)
					if (row[0] == "SEGNAM:Z2EDL24"):
						try:
							values = {}
							for segments in row:
								values[segments.split(":")[0]] = segments.split(":")[1]
#							print("Z2EDL24:",values)
							bdyId = body(connection,hdr.id,hdr.docRef,values,bdyId)
						except:
#							pass
							print("ERROR3-Z2EDL24:",values)
					if (row[0] == "SEGNAM:E2EDL52"):
						try:
							values = {}
							for segments in row:
								values[segments.split(":")[0]] = segments.split(":")[1]
#							print("E2EDL52:",values)
							bdyId = body(connection,hdr.id,hdr.docRef,values,bdyId)	
						except:
#							pass
							print("ERROR4-E2EDL52:",values)
				except:
					print("ERROR:")	
			try:
				os.remove(file_path_csv)
			except:
				pass
				
			return updated
			
	except:
		print("ERROR")
		return False
		
if __name__ == '__main__':
	main()