# Create / Update Receipt Data from ASN Interface Files

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

import default_setting

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

def partUpdate(cnx,partnumber,insp):
	try:
# pad / format date/time
#		dateTime = date[6:9]+"-"+date[3:5]+'-'+date[0:3]+' 00:00:01'
# Test for Existing Reference
		cursor = cnx.cursor()
#		dataQuery = "UPDATE part SET requires_inspection = "+insp+",last_updated=now(),last_updated_by='sys' WHERE part_number LIKE('%"+partNumber+"') LIMIT 1"
		dataQuery = "UPDATE part SET requires_inspection = "+insp+",last_updated=now(),last_updated_by='sys' WHERE LOCATE(part.part_number,'"+partnumber+"') >0 LIMIT 1"
		print("mysql:",dataQuery)
		try:
			cursor.execute(dataQuery)
			cnx.commit()
		finally:
			cursor.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 getTxt(filename,cnx):
	partnumber = ""
	insp = ""
	f = open(filename,'r')
	x = f.readline()
	while x:
		if (x[:14] == "SEGNAM:ZMARCEX"):
			s = x.find("IDOC:")
			if (s > -1):
				try:	
					e = x.find(",",s)
					if (e == -1):
						e = s+21
					partnumber = x[s+5:e]
				except:
					pass
			s = x.find("INSP:")
			if (s > -1):
				try:
					insp = x[s+5:s+6]
				except:
					pass
					
		if (partnumber != "") and (insp != ""):
			print("part:",partnumber," insp:",insp)
			partUpdate(cnx,partnumber,insp)
			partnumber = ""
			insp = ""
			

		ok = ""
		while (ok == ""):
			try:
				x = f.readline()
				ok = "ok"
			except:
				ok = ""
	f.close()
			
def main():
	ts = getts()
	print('Time:'+str(ts))
	dir_path = os.path.dirname(os.path.realpath(__file__))
# Default Connection / System Settings
	defaults = default_setting.defaultSettings()
	uploadDirs = default_setting.companySettings(defaults)
	print(uploadDirs['interfacein'])
# open txt file
#	filename = "C:/RRMC/insp.txt"
		
# mySql Connector
#	cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
	cnx = mysql.connector.connect(user='root',password='root',database='vantec',host='172.18.25.32')
		
# read "INSP" files
	for insp in glob.glob(uploadDirs['interfacein']+"/INSP*.txt"):
# split out directory & filename
#		path, filename = os.path.split(insp)
# Archive
#		shutil.copy(insp, glob.glob(uploadDirs['interfaceout']+filename))

# Process IDOC message
		getTxt(insp,cnx)
		
# remove processed file			
		if os.path.exists(insp):
			os.remove(insp)
		
if __name__ == '__main__':
	main()