import cx_Oracle

def _safe_index(arr = [], val = ""):
	try:
		return arr.index(val)
	except ValueError:
		return -1

log = []
logNames = []

def readLogs(args):
	start = 0
	end = 0
	for filename in args:
		start = len(log)
		f = open(filename, 'r')
		log.extend([i for i in f.readlines() if _safe_index(i, "CEmailDef") > -1])
		end = len(log)
		f.close()
		logNames.append((start,end,filename))

def getLogName(lineNum = 0):
	return [log[2] for log in logNames if log[0] <= lineNum and log[1] > lineNum][0]

def readDatabase():
	try:
		conn = cx_Oracle.connect("c_jmelloy@ods_prod.world")
		curs = conn.cursor()

		sql = "select key_value, msg_id from site_email_outbound where status in (101, 100) and key_value is not null"

		curs.execute(sql)

		row = curs.fetchone()
		put_away = []
		print len(log)
		while(row):
			print row[0],
			i = 0
			for logline in log:
				if _safe_index(logline, row[0]) > -1:
					print "Match found in %s" % getLogName(i)
					if (_safe_index(logline, "cancel.asp") > -1 or
						_safe_index(logline, "forgot_password.asp") > -1 or
						_safe_index(logline, "rx_autorefill.asp") > -1 or
						_safe_index(logline, "site_error.asp") > -1 or
						_safe_index(logline, "shipment.asp") > -1 or
						_safe_index(logline, "vitaminadvisor.asp") > -1):

					   	put_away.append(row[1])
						break
				i += 1
			if i == len(log):
				print "No match found"

			row = curs.fetchone()
		print "%d matches found " % len(put_away)

		w = open("update.sql", "w")

		w.write("select status, count(*) from site_email_outbound where msg_id in (')")
		w.write("',\n'".join(put_away))
		w.write("') group by status;\n\n")
		w.write("update site_email_outbound set status = 1002 where msg_id in ('")
		w.write("',\n'".join(put_away))
		w.write("');\n\n")
		w.write("select status, count(*) from site_email_outbound where msg_id in (')")
		w.write("',\n'".join(put_away))
		w.write("') group by status;")
		w.close()

	finally:
		conn.close()

if __name__ == "__main__":
	import sys
	readLogs(sys.argv[1:])
	readDatabase()