# Populate vehicle table from IDOC

import os
import time
import datetime
import mysql.connector

import default_setting

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

# update vehicle table
def updateVehicle(connection,vehicle,model):
	result = ""
	try:
		cursor = connection.cursor(dictionary=True)
		
# Get id for existing vehicle	
		dataQuery = "SELECT id FROM vehicle WHERE vehicle='"+vehicle+"' LIMIT 1"
		try:
			cursor.execute(dataQuery) 
			row = cursor.fetchone()
			if row is None:
				dataQuery = "INSERT INTO vehicle (id,vehicle,model,date_created,created_by,last_updated,last_updated_by)"
				dataQuery += " VALUES(DEFAULT,'"+vehicle+"','"+model+"',now(),'system',now(),'system')"
				try:
					cursor.execute(dataQuery)
					connection.commit()
				except UnboundLocalError:
					raise 'Update Error'
			else:
				id = row['id']
				dataQuery = "UPDATE vehicle SET model='"+model+"',last_updated=now() WHERE id="+str(id)+" LIMIT 1"
				try:
					cursor.execute(dataQuery)
					connection.commit()
				except UnboundLocalError:
					raise 'Update Error'
		finally:
			cursor.close()
	except UnboundLocalError as e:
		print(e)
	
	return result

# Main Processing
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'])
		
# Read IDOC Transaction
	file_path = os.path.join(dir_path, '../vimsinterface/vimsin/vehmod.txt')
#	file_path = "c:/web/veldb/rdtrrmc/rdtvims/vehmod.txt";
	iDocTran = open(file_path,"r")
	l = 0
	vehicle = ""
	model = ""
	for line in iDocTran:
		line = line.replace('\n',' ')
		line = line.replace(',',' ')
#		print("LINE:",line)
		
		lineList = line.split()
		vehicle = lineList[0]
		model = lineList[3]		
		print ("vehicle",vehicle,"model",model)
		
		result = updateVehicle(connection,vehicle, model)
		
	return True

if __name__ == '__main__':
		main()