#!/usr/bin/python

import re, MySQLdb


# connect
db = MySQLdb.connect(host="sql.snm-hgkz.ch", user="snmcrime", passwd="kr1m1naL", db="snmcrime")
# create a cursor
cursor = db.cursor()



# open file
f = open("michigannew/michiganALL.html")

first = True
offense_para = []
offense_desc = []

# read first line
line = True

# iterate through file
while line:
	
	# read line
	line = f.readline()
	
	res = re.search("(\d{8})<br>", line)
	if res != None:
		
		# o_id
		o_id = res.group(1)
		
		# not first person
		if first == False:
			print ""
			print "'" + o_id + "', '" + lname + "', '" + fname + "', '" + sex + "', '" + race + "', '" + dob + "', '" + height + "', '" + weight + "', '" + hair + "', '" + eye + "', '" + address + "', '" + zip + "', '" + city + "', '" + state + "', '" + photo_path
			print offense_para, offense_desc
			
			# person
			birthdate = dob[6:]+"-"+dob[0:2]+"-"+dob[3:5]
			sqlperson = "INSERT INTO person (o_id, last_name, first_name, sex, race, birthdate, height, weight, hair, eyecolour, address, zip, city, state) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s);" % ("'"+o_id+"'", "'"+lname+"'", "'"+fname+"'", "'"+sex+"'", "'"+race+"'", "'"+birthdate+"'", "'"+height+"'", "'"+weight+"'", "'"+hair+"'", "'"+eye+"'", "'"+address+"'", int(zip), "'"+city+"'", "'"+state+"'")
			print sqlperson
			# execute SQL statement
			cursor.execute(sqlperson)

			#id = int(cursor.insert_id())
			cursor.execute("SELECT max(id) FROM person")
			row = cursor.fetchone()
			id = row[0]
			#id=100
			
			for i in range(0, len(offense_para)):
				sqloffense = "INSERT INTO offense (fk_person_id, offense_para, offense_descr) VALUES (%d, %s, %s)" % (id, "'"+offense_para[i]+"'", "'"+offense_desc[i]+"'")
				print sqloffense
				# execute SQL statement
				cursor.execute(sqloffense)
			
			sqlphoto = "INSERT INTO photo (fk_person_id, photo_path) VALUES (%d, %s)" % (id, "'www.mipsor.state.mi.us/mipsor/images/"+photo_path+"'")
			print sqlphoto
			# execute SQL statement
			cursor.execute(sqlphoto)
			
			# reinit
			offense_para = []
			offense_desc = []
			
		first = False
		
	else:
		
		
		# name
		res = re.search('<font color = "#004080"><strong>Name: </strong></font><font color = "#00CC33">(.*)/</font>', line)
		if res != None:
			name = res.group(1)
			pos = name.find('/')
			lname = name[:pos]
			fname = name[(pos+1):]
			
		# sex
		res = re.search('<font color = "#004080"><strong>Sex: </strong></font>(.)', line)
		if res != None:
			sex = res.group(1)
		
		# race
		res = re.search('<font color = "#004080"><strong>Race: </strong></font>(.)', line)
		if res != None:
			race = res.group(1)
			
		# dob
		res = re.search('<font color = "#004080"><strong>Dob: </strong></font>(.*)<br>', line)
		if res != None:
			dob = res.group(1)
		
		# height
		res = re.search('<font color = "#004080"><strong>Height: </strong></font>(\d*)', line)
		if res != None:
			height = res.group(1)
		
		# weight
		res = re.search('<font color = "#004080"><strong>Weight: </strong></font>(\d*)', line)
		if res != None:
			weight = res.group(1)
		
		# hair
		res = re.search('<font color = "#004080"><strong>Hair: </strong></font>(.*)\r', line)
		if res != None:
			hair = res.group(1)
		
		# eye
		res = re.search('<font color = "#004080"><strong>Eye: </strong></font>(\w*)<br>', line)
		if res != None:
			eye = res.group(1)
		
		# address
		res = re.search('<font color = "#004080"><strong>Address: </strong></font>(.*)\r', line)
		if res != None:
			address = res.group(1)
			
		# city
		res = re.search('<font color = "#004080"><strong>City: </strong></font>(.*)\r', line)
		if res != None:
			city = res.group(1)
			
		# state
		res = re.search('<font color = "#004080"><strong>State: </strong></font>(..)', line)
		if res != None:
			state = res.group(1)
			
		# zip
		res = re.search('<font color = "#004080"><strong>Zip: </strong></font>(\d*)<br>', line)
		if res != None:
			zip = res.group(1)
			
		# offense_para
		res = re.search('<font color = "#004080"><strong>Offense: </strong></font>(.*)\r', line)
		if res != None:
			offense_para.append(res.group(1))
			
		# offense_desc
		res = re.search('<font color = "#004080"><strong>Description: </strong></font>(.*)<br>', line)
		if res != None:
			offense_desc.append(res.group(1))
		
		# photo_path
		res = re.search('<A HREF="javascript:Show_Image\(\'/mipsor/images/(\d*.jpg)\'\)"><strong>Click HERE for Photograph</strong></a>', line)
		if res != None:
			photo_path = res.group(1)
		

# close file
f.close()