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 getPick(connection):
	VBELN = ""
	LIFEX = ""
	QUALF = ""
	POSNR = ""
	MATNR = ""
	LFIMG = ""
	QUALF = ""
# Get Current TimeStamp
	ts = time.time()
	pickTs = 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:",pickTs,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)='picked' 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' Pick_Headers			
		dataQuery = "SELECT pick_header.id AS id, pick_header.document_reference AS document_reference, pick_header.customer_reference AS customer_reference FROM pick_header WHERE document_status_id="+str(docStsId)
		try:
			cursor.execute(dataQuery) 
			hdrRows = cursor.fetchall()
			
			for hdrRow in hdrRows:
# Open Output File
				pickName = hdrRow[1]
				if (pickName[0:3] != "PCK"):
					pickName = "PCK"+pickName
				file_path = os.path.join(dir_path, '../vimsin/'+pickName+'.txt')
				idocFile = open(file_path,"w")
# IDOC Header
				idocStr = "EDI_DC40>TABNAM:EDI_DC40,DIRECT:2,DOCREL:700,IDOCTYP:WMTCID03,CIMTYP:ZWMTCID,MESTYP:ZWMTOCO,CREDAT:"+ts[0:8]+",CRETIM:"+ts[9:15]+",SNDPOR:LSP,SNDPRT:LS,SNDPRN:VEL001,RCVPOR:SAPRRP,RCVPRT:LS,RCVPRN:RRP400"+"\n"
				idocFile.write(idocStr)
				
				TANUM = hdrRow[2]
				idocStr = "E1LTCOH>SEGNAM:E1LTCOH,LGNUM:LSP,TANUM:"+str(TANUM)+",SQUIT:,QUKNZ:1"+"\n"
				idocFile.write(idocStr)

				dataQuery = "SELECT pick_detail.id AS id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, pick_detail.qty_allocated AS qty_allocated, pick_detail.qty_scanned AS qty_scanned, (pick_detail.qty_allocated-pick_detail.qty_scanned) AS diff, pick_detail.line_no AS line_no FROM pick_detail JOIN pick_body ON pick_detail.pick_body_id=pick_body.id JOIN part ON pick_detail.part_id=part.id WHERE pick_body.pick_header_id="+str(hdrRow[0])
				cursor.execute(dataQuery)
				dtlRows = cursor.fetchall()
				seq = 0
				for dtlRow in dtlRows:
					cnvFactor = 1
					try:
						cnvFactor = dtlRow[2]
					except UnboundLocalError:
						cnvFactor = 1
					MATNR = dtlRow[1]
					seq = seq+1
					TAPOS = "0000"+str(dtlRow[6])
					TAPOS = TAPOS[-4:]
#					LFIMG = float(dtlRow[3]/cnvFactor)
					try:
						NISTA = ("{0:.0f}".format(dtlRow[4]/cnvFactor))
					except:
						NISTA = 0
					try:
						NDIFA = ("{0:.0f}".format(dtlRow[5]/cnvFactor))
					except:
						NDIFA = 0
					if ((NISTA != 0) and (NDIFA != 0)):
						idocStr = "E1LTCOI>SEGNAM:E1LTCOI,TAPOS:"+str(TAPOS)+",NISTA:"+str(NISTA)+",NDIFA:"+str(NDIFA)+",ALTME:EA,KZDIF:"+"\n"
						idocFile.write(idocStr)
					
					print("TANUM:",TANUM,",TAPOS:",TAPOS,",MATNR:",MATNR,"NISTA:",NISTA,"NDIFA:",NDIFA)
					
				idocFile.close()
# flag Receipt as Transmitted
				dataRctQuery = "UPDATE pick_header SET document_status_id='"+str(docTrnId)+"', last_updated_date=now(),last_updated_by='Transmitted' 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 Picks & return Idoc values
	getPick(connection)
	
	idocFile.close()
				
if __name__ == '__main__':
	main()
	
	