import os
import time
import datetime
import mysql.connector 

import default_setting

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

# Create IDOC Segments from Closed Receipt		
def getReceipt(connection):
	VBELN = ""
	LIFEX = ""
	QUALF = ""
	POSNR = ""
	MATNR = ""
	LFIMG = ""
	QUALF = ""
# Get Current TimeStamp
	ts = time.time()
	asnTs = datetime.datetime.fromtimestamp(ts).strftime('_%Y-%m-%d_%H:%M:%S')
	ts = datetime.datetime.fromtimestamp(ts).strftime('%Y%m%d_%H%M%S')	
#	print("TIME:",asnTs,ts[0:8],ts[9:15])
	
# open File for Output (for input to the next process)
#	file_path = os.path.join(dir_path, '../vimsout/idoctranout.txt')
	file_path = os.path.join(dir_path, '../vimsin/idoctranin.txt')
	idocFile = open(file_path,"w")
	
	try:			
# Get Document_closed id
		cursor = connection.cursor()
		dataQuery = "SELECT id FROM document_status WHERE lower(document_status_code)='closed' LIMIT 1"
		try:
			cursor.execute(dataQuery) 
			row = cursor.fetchone()
			docStsId = row[0]
		except UnboundLocalError:
			docStsId = 3
# Get Document_transmitted id
		cursor = connection.cursor()
		dataQuery = "SELECT id FROM document_status WHERE lower(document_status_code)='transmitted' LIMIT 1"
		try:
			cursor.execute(dataQuery) 
			row = cursor.fetchone()
			docTrnId = row[0]
		except UnboundLocalError:
			docStsId = 4

# Get 'closed' Receipt_Headers			
		dataQuery = "SELECT receipt_header.id AS id, document_reference, customer_reference, haulier_reference FROM receipt_header WHERE document_status_id="+str(docStsId)
		try:
			cursor.execute(dataQuery) 
			hdrRows = cursor.fetchall()
			
			for hdrRow in hdrRows:
# Open Output File
				rcpName = hdrRow[1]
				if (rcpName[0:3] != "RCP"):
					rcpName = "RCP"+rcpName
				file_path = os.path.join(dir_path, '../vimsin/'+rcpName+'.txt')
#				file_path = os.path.join(dir_path, '../vimsin/RCP'+hdrRow[1]+'.txt')
				idocFile = open(file_path,"w")
# IDOC Header
				idocStr = "EDI_DC40>TABNAM:EDI_DC40,DIRECT:2,DOCREL:702,IDOCTYP:DELVRY05,CIMTYP:ZDELVRY05,MESTYP:ZLSPSHPCON,CREDAT:"+ts[0:8]+",CRETIM:"+ts[9:15]+",SNDPOR:LSP,SNDPRT:LS,SNDPRN:VEL001,RCVPOR:SAPRRP,RCVPRT:LS,RCVPRN:RRP400"+"\n"
				idocFile.write(idocStr)
				
				VBELN = hdrRow[2]
				LIFEX = hdrRow[3]
				idocStr = "E1EDL20>SEGNAM:E1EDL20,VBELN:"+str(VBELN)+",LIFEX:"+str(LIFEX)+"\n"
				idocFile.write(idocStr)
				idocStr = "E1EDL18>SEGNAM:E1EDL18,QUALF:PGI"+"\n"
				idocFile.write(idocStr)

				dataQuery = "SELECT receipt_body.id AS id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, receipt_body.received_qty AS received_qty FROM receipt_body JOIN part ON receipt_body.part_id=part.id WHERE receipt_header_id="+str(hdrRow[0])
				cursor.execute(dataQuery)
				bdyRows = cursor.fetchall()
				seq = 0
				for bdyRow in bdyRows:
					cnvFactor = 1
					try:
						cnvFactor = bdyRow[2]
					except UnboundLocalError:
						cnvFactor = 1
					MATNR = bdyRow[1]
					seq = seq+1
					POSNR = "000000"+str(seq)
					POSNR = POSNR[-6:]
#					LFIMG = float(bdyRow[3]/cnvFactor)
					LFIMG = ("{0:.3f}".format(bdyRow[3]/cnvFactor))
					
					idocStr = "E1EDL24>SEGNAM:E1EDL24,POSNR:"+str(POSNR)+",MATNR:"+str(MATNR)+",LFIMG:"+str(LFIMG)+"\n"
					idocFile.write(idocStr)
					
					print("VBELN:",VBELN,",LIFEX:",LIFEX,",POSNR:",POSNR,",MATNR:",MATNR,"LFIMG:",LFIMG)
					
				idocStr = "E1EDL19>SEGNAM:E1EDL19,QUALF:QUA"+"\n"
				idocFile.write(idocStr)
				idocFile.close()
# flag Receipt as Transmitted
				dataRctQuery = "UPDATE receipt_header SET document_status_id='"+str(docTrnId)+"', last_updated_date=now() WHERE id="+str(hdrRow[0])+" LIMIT 1"
				try:
					cursor.execute(dataRctQuery)
					connection.commit()
				except UnboundLocalError:
					raise 'Update Error'
		finally:
			cursor.close()
#			idocFile.close()
	except UnboundLocalError as e:
		print(e)
	
	return
	
def getts():
	ts = time.time()
	ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S')
	return ts

def main():
# 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'])
# timestamp
	ts = getts()
	file_path = os.path.join(dir_path, '../vimsout/idoctranout.txt')
	idocFile = open(file_path,"w")
		
# Get Closed Rexceipts & return Idoc values
	getReceipt(connection)
	
	idocFile.close()
				
if __name__ == '__main__':
	main()