# Author: Charles Ruggiero
# Date Created: 3/21/17
# Looks for tickets where the user input "For another user" and correctly assigns them
# Requires a form field setup and the variable name to be "anotherUser" in the database

import mysql.connector
import smtplib 
from datetime import datetime, time

try:

	#Current time for checking whether to send notification email
	now = datetime.now()
	now_time = now.time()

	#Email addres for errors
	adminEmail = "xxx@xxxx"

	#Connect directly to database
	cnx = mysql.connector.connect(user='XXXX', password='XXXX',
								  host='XXXX',
								  database='osticket')
	cursor = cnx.cursor(buffered=True)	
	cursor2 = cnx.cursor(buffered=True)	

	#Form Field is named "anotherUser", only get active tickets
	query = ('''SELECT a.value, d.ticket_id, d.number FROM ost_form_entry_values AS a 
				INNER JOIN ost_form_entry AS b ON b.id = a.entry_id 
				INNER JOIN ost_form_field AS c ON c.id = a.field_id 
				INNER JOIN ost_ticket AS d ON d.ticket_id = b.object_id
				WHERE c.name = 'anotherUser' AND a.value IS NOT NULL AND d.status_id = 1''')
	cursor.execute(query)
	#Loop over all staff
	for row in cursor:		
		#Get the email of the current ticket user
		query = ('''SELECT a.address FROM ost_user_email AS a 
					INNER JOIN ost_ticket AS b ON a.user_id = b.user_id
					WHERE b.ticket_id = ''' + str(row[1]))
		cursor2.execute(query)
		forEmailAddress = row[0].strip()
		ticketEmailAdddress = cursor2.fetchone()[0]
		#They don't match
		if forEmailAddress != ticketEmailAdddress:
			#Find the right user
			query = ("SELECT a.user_id FROM ost_user_email AS a WHERE a.address = '" + forEmailAddress + "'")
			cursor2.execute(query)
			newUserID = cursor2.fetchone()
			newUserID = newUserID[0]
			if newUserID is not None:
				#Link Back to the User Table
				query = ("UPDATE ost_ticket SET user_id = "+ str(newUserID) +" WHERE ticket_id = " + str(row[1]))
				cursor2.execute(query)
				cnx.commit()
			#Send an email once a day if the email isn't found (Task runs every ten minutes)
			elif now_time >= time(7,35) and now_time <= time(7,45):
				msgBody = 'The email address "' + forEmailAddress + '" was entered as the "Ticket for another user?" field but was not found for ticket #' + str(row[2]) 
				msgSubject = 'User Assignment: osTicket Sync'
				msgFrom = 'xxxx@xxxxxx'
				msgTo = [adminEmail]
				# Prepare actual message
				message = """\
From: %s
To: %s
Subject: %s

%s""" % (msgFrom, ", ".join(msgTo), msgSubject, msgBody)
				# Send the message via our own SMTP server, but don't include the
				# envelope header.
				s = smtplib.SMTP('smtp.k12.de.us')
				s.sendmail(msgFrom,msgTo,message)
				s.quit()
	cnx.close()
#Email all exceptions to me
except Exception as e:
	s = str(e)
	msgBody = s
	msgSubject = 'Error Notification: osTicket Sync'
	msgFrom = 'xxxx@xxxxx'
	msgTo = [adminEmail]
	# Prepare actual message

	message = """\
From: %s
To: %s
Subject: %s

%s""" % (msgFrom, ", ".join(msgTo), msgSubject, msgBody)
	
	
	# Send the message via our own SMTP server, but don't include the
	# envelope header.
	s = smtplib.SMTP('smtp.k12.de.us')
	s.sendmail(msgFrom,msgTo,message)
	s.quit()