#!C:/Users/administrator.AUTDOM01/AppData/Local/Programs/Python/Python36-32/python.exe

import os
import sys
import shutil
import time
import datetime
import mysql.connector 
import ftplib

import default_setting

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

# Create IDOC Segments from Closed Receipt		
def getPick(connection, documentReference):
	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:
			docTrnId = 4

# Get 'closed' Pick_Headers			
		dataQuery = "SELECT document_header.id AS id, document_header.document_reference_code AS document_reference, document_header.customer_reference_code AS customer_reference, document_header.order_type AS order_type FROM document_header"
		if (documentReference != "" and documentReference != None):
			dataQuery = dataQuery + " WHERE document_reference_code = '"+documentReference+"' LIMIT 1"
		else:
			dataQuery = dataQuery + " 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, '../../vimsout/'+pickName+'.txt')
				idocFile = open(file_path,"w")
# IDOC Header
				idocStr = "EDI_DC40>TABNAM:EDI_DC40,DIRECT:2,DOCREL:702,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]
# Test for Aftersales Order				
				if (hdrRow[3] == "601" or hdrRow[3] == "916"):
					idocStr = "E1LTCOH>SEGNAM:E1LTCOH,LGNUM:LSP,TANUM:"+str(TANUM)+",SQUIT:,QUKNZ:"+"\n"
				else:
					idocStr = "E1LTCOH>SEGNAM:E1LTCOH,LGNUM:LSP,TANUM:"+str(TANUM)+",SQUIT:,QUKNZ:1"+"\n"
				idocFile.write(idocStr)

				dataQuery = "SELECT document_body.id AS id, part.part_number AS part_number, part.conversion_factor AS conversion_factor, document_body.qty_expected AS qty_expected, document_body.qty_transacted AS qty_transacted, (document_body.qty_expected-document_body.qty_transacted) AS diff, document_body.our_line_no AS line_no FROM document_body JOIN part ON document_body.part_id=part.id WHERE document_body.document_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 document_header SET document_status_id='"+str(docTrnId)+"', last_updated=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:
		return "FAIL-"+str(e)
	
	try:
		return "OK  -"+file_path
	except UnboundLocalError:
		return "FAIL-Document Error "+documentReference+" "+dataQuery

def ftpput(ip,user,pwd,idoc):
	session = ftplib.FTP(ip,user,pwd)
	file = open(idoc,'rb')                  					# file to send
	session.storbinary('STOR c:/rrmc/output/'+idoc, file)     	# send the file
	file.close()                                    			# close file and FTP
	session.quit()
	
def getts():
	ts = time.time()
	ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S')
	return ts

def main():

	result = "FAIL-IDOC Error"
	
# 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")

# check for Document_reference
	if (len(sys.argv)>1):
		documentReference = sys.argv[1]
	else:
		documentReference = ""
		
# Get Closed Picks & return Idoc values
	result = getPick(connection,documentReference)
	
#	idocFile.close()
	
	return result
				
if __name__ == '__main__':
	result = main()
	
	if (result[0:2] == "OK"):
		idoc = result[5:]
#		ftpput("172.18.25.32","administrator","1t'sa£1_Coin",idoc)
		path, fileName = os.path.split(idoc)
#		print ("TEST1-",fileName)
		try:
			shutil.copy(idoc, "//172.18.25.32/rrfiles/output/"+fileName)
#			shutil.copy(idoc, "/test1.txt")
			print("OK  -"+str(fileName))
		except AssertionError as Err:
			print("FAIL-"+str(fileName)+str(Err))
		except Exception as Err:
			print("FAIL-Unknown error"+str(Err))