def register():
	form = RegistrationForm(request.form)
	if request.method == 'POST' and form.validate():
		db = db_connect()
		cur = db.cursor()
		salt = get_salt()
		password_hash = phash(form.password.data + salt)
		# TODO: Clean up this handling.
		# Handles case where email is not present, inserts NULL below.
		# Notice lack of single quotes in query which facilitates this.
		if form.email.data != "":
			email = "'{}'".format(form.email.data)
		else:
			email = "NULL"

		query = "INSERT INTO users (username, l_username, first_name, " \
				"last_name, password_hash, salt, email) VALUES " \
				"('{username}', LOWER('{username}'), '{first_name}', " \
				"'{last_name}', '{password_hash}', '{salt}', {email})".format(
						username=form.username.data,
						first_name=form.first_name.data,
						last_name=form.last_name.data,
						password_hash=password_hash,
						salt=salt,
						email=email
					)
		cur.execute(query)
		db.commit()

		session['logged'] = form.username.data
		return redirect(url_for('homepage'))
	
	return render_template("register.html", form=form)

def hours():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  fname=request.args.get('firstname')
  lname=request.args.get('lastname')
  classes=request.args.get('subject')
  username=session['username']
  query = "SELECT numId FROM users WHERE email='%s'" % (username)
  cur.execute(query)
  student = cur.fetchone()
  sId = student['numId']
  username = fname + " " + lname
  b=[]
  IDquery = "SELECT numId FROM users WHERE firstname = '%s' AND lastname = '%s'" % (fname, lname)
  cur.execute(IDquery)
  user = cur.fetchone()
  numId = user['numId']
  appQuery = "SELECT dayofweek, hourof FROM times WHERE studentId = '%s' AND available = '0'" % (numId)
  cur.execute(appQuery)
  apps = cur.fetchall()
  for thing in apps:
    time = thing['hourof']
    day = thing['dayofweek']
    app = time + day
    b.append(app)
  return render_template('hours.html', name=username, a=b, tutorId=numId, studentId=sId, course=classes)

def another_page():
  
    print('anotherpage')
    scoop = {'postername': MySQLdb.escape_string(request.form['postername']),
               'activity': MySQLdb.escape_string(request.form['activity']),
               'rank': request.form['rank']
             
               }
    
    if request.method == 'POST':
        db = utils.db_connect()
        cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
        
        query = "INSERT INTO club_name (postername) VALUES ('" + MySQLdb.escape_string(request.form['postername']) + "')"
        # Print query to console (useful for debugging)
        print query
        cur.execute(query)
        id=cur.lastrowid
        #db.commit()
        
        query2 = "INSERT INTO activity (club_id, activity, rank) VALUES (" + str(id) + ", '" + MySQLdb.escape_string(request.form['activity']) + "', '" + request.form['rank'] + "')"
        # Print query to console (useful for debugging)
        print query2
        cur.execute(query2)
        db.commit()
        
        
    cur.execute('SELECT DISTINCT cn.postername, a.activity, a.rank FROM club_name cn NATURAL JOIN activity a')
    rows = cur.fetchall()

    return render_template('another_page.html', club_name=rows, activity = rows, scoop = scoop)

def register():
  #If they registered for an account
  if request.method == 'POST':
    
    #set up database connections
    db = utils.db_connect()
    cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    
    #get form results.
    username = MySQLdb.escape_string(request.form['username'])
    password = MySQLdb.escape_string(request.form['pw'])
    zipcode = MySQLdb.escape_string(request.form['zipcode'])
    
    #testing in terminal
    print "Hi " + username + " " + password + " " + zipcode
    
    #Insert into 'users' table
    #query = "INSERT INTO users (username, password, zipcode) VALUES ('";
    #query += request.form['username'] + "','" + request.form['pw'] + "','" + request.form['zipcode'] + "')"
    #Hash it
    ###ADD ZIPCODE TO USERS TABLE  
    query = "INSERT INTO users (username, password, zipcode) VALUES ('%s', SHA2('%s', 0), '%d')" % (username, password, int(zipcode))
    print query          #testing in terminal
      
    cur.execute(query)
    db.commit()
    
    return render_template('login.html', selectedMenu='Login')
  
  return render_template('register.html', selectedMenu='Register', name = currentUser)

def export():
	# Request for export data.
	if request.method == 'POST':
		db = db_connect()
		cur = db.cursor(MySQLdb.cursors.DictCursor);
		query = "SELECT DATE(h.entry_start) AS start_date, TIME(h.entry_start) AS start_time, DATE(h.entry_end) AS end_date, TIME(h.entry_end) AS end_time, h.severity FROM headache_entries h JOIN users u ON h.user_id = u.id WHERE u.l_username = LOWER('{}')".format(session['logged'])
		cur.execute(query)
		# Get all entries at once.
		entries = cur.fetchall();

		# Save results parsed as csv to file in-memory.
		string_buffer = StringIO.StringIO()
		w = csv.DictWriter(string_buffer, entries[0].keys())
		w.writeheader()
		w.writerows(entries)
		csv_content = string_buffer.getvalue()
		string_buffer.close()

		# Response with export data.
		return Response(csv_content,
				mimetype="text/csv",
				headers={
					"Content-Disposition": "attachment;filename=export.csv"
					})

	return render_template("export.html")

def register():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  errorMail = ""
  errorFirst = ""
  errorLast = ""
  errorPass = ""
  error = ""
  if request.method == 'POST':
    firstname=request.form['firstname']
    lastname=request.form['lastname']
    email=request.form['email']
    password=request.form['password']
    if "mail.umw.edu" in email and firstname and lastname and password:
      query = "INSERT INTO users (firstname,lastname,email,password,accountStatus) VALUES('%s','%s','%s','%s',3);" % (firstname,lastname,email,password)
      cur.execute(query)
      db.commit()
      return redirect(url_for('login'))
    else:
      error = "true"
      if "mail.umw.edu" or "umw.edu" not in email or not email:
        errorMail = "true"
      if not firstname:
        errorFirst = "true"
      if not lastname:
        errorLast = "true"
      if not password:
        errorPass = "true"
  return render_template('register.html', errorMail=errorMail, errorFirst=errorFirst, errorLast=errorLast, errorPass=errorPass, error=error)

def estateadd2():
    db = utils.db_connect()
    cur = db.cursor()    

    if request.method == 'POST':       #if user has submitted something
      
      if 'address' in request.form:  #if user is adding an estate
        damageType = MySQLdb.escape_string(request.form['damageType'])
        address = request.form['address']
        query = "INSERT INTO basicHouse (address,county,state,price) VALUES ('" + address +"', '"+MySQLdb.escape_string(request.form['county'])+"', '"+MySQLdb.escape_string(request.form['state'])+"', "+MySQLdb.escape_string(request.form['price'])+")"
        print(query)
        cur.execute(query)
        db.commit()
        query = "INSERT INTO house_damages (type,house_id,cost) VALUES ('"
        query+=damageType+"', (SELECT house_id FROM basicHouse WHERE address= '"+ address+"' GROUP BY address) , '"+ MySQLdb.escape_string(request.form['damageCost']) + "');" 
        print(query)
        cur.execute(query)
        #rows = cur.fetchall()
        db.commit()
        
      if 'damAddress' in request.form: #if adding damages to existing estate
        address = MySQLdb.escape_string(request.form['damAddress'])
        damageType = MySQLdb.escape_string(request.form['damDamageType'])
        damageCost = MySQLdb.escape_string(request.form['damDamageCost'])
        query = "INSERT INTO house_damages (house_id,type,cost) VALUES ((SELECT house_id FROM basicHouse WHERE address = '" + address + "'),'"+ damageType+"',"+damageCost + ");" 
        print(query)
        cur.execute(query)
        db.commit()
        
    return render_template('index.html', name = currentUser)

def createTutor():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  created = " "
  if request.method == 'POST':
      row = []
      first = request.form['firstName']
      last = request.form['lastName']
      email = request.form['email']
      course = request.form['course']
      password = request.form['password']
      query2 = "SELECT * FROM users WHERE email = '%s';" % (email)
      cur.execute(query2)
      test = cur.fetchone()
      if test:
        if test['accountStatus'] == 1:
          created = "admin"
        elif test['accountStatus'] == 2:
          created = "no"
        elif test['accountStatus'] == 3:
          created = "updated"
          #if the query here does not activate, take out classes + and leave it '%s'
          query3 = "UPDATE users SET accountStatus = 2, classes = classes + '%s' WHERE email = '%s';" % (course, email)
          cur.execute(query3)
      else:
        created = "yes"
        query = "INSERT INTO users (firstname,lastname,email,password,accountStatus,classes) VALUES('%s','%s','%s','%s',2, '%s');" % (first,last,email,password, course)
        cur.execute(query)
        db.commit()
  return render_template('createTutor.html', created=created)

def randome():     
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  trivia =  request.form.get("triviatype")
  ranNum = random.randint(1,5)
  
  if ranNum == 1:
    tCol = 'laws'
    cur.execute('select content,state from '+tCol+' order by rand() limit 1')
  elif ranNum == 2:
    tCol = 'trivia'
    cur.execute('select content from '+tCol+' order by rand() limit 1')
  elif ranNum == 3:
    tCol = 'sayings'
    cur.execute('select content, author from '+tCol+' order by rand() limit 1')
  elif ranNum == 4:  
    tCol = 'fortuneCookies'
    cur.execute('select content from '+tCol+' order by rand() limit 1')
    print 'select content from '+tCol+' order by rand() limit 1'
  elif ranNum == 5:  
    tCol = 'meme'
    cur.execute('select imageLink,content from '+tCol+' order by rand() limit 1')
  rows = cur.fetchall()
  print ranNum, rows
  
  return render_template('randdisplay.html', trivia = trivia, rows = rows)

def gChoose2():     
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  genre =  request.form.get("genreT")
  ranNum = random.randint(1,5)

  if ranNum == 1:
    trivia = 'Laws'
    cur.execute('select content,state from laws where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  elif ranNum == 2:
    trivia = 'Trivia'
    cur.execute('select content from trivia where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  elif ranNum == 3:
    trivia = 'Sayings'
    cur.execute('select content, author from sayings where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  elif ranNum == 4:  
    trivia = 'Fortune Cookies'
    cur.execute('select content from fortuneCookies where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  elif ranNum == 5:  
    trivia = 'Meme'
    cur.execute('select imageLink,content from meme where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  
  rows = cur.fetchall()

  return render_template('genreDisplay.html', genre = genre, rows = rows, trivia = trivia)

def trivia2():     
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  trivia =  request.form.get("triviatype")

  if trivia == 'Laws':
    tCol = 'laws'
    cur.execute('select content,state from '+tCol+' order by rand() limit 3')
    #cur.execute('select content, state from ' + tCol + ';')
  elif trivia == 'Trivia':
    tCol = 'trivia'
    cur.execute('select content from '+tCol+' order by rand() limit 3')
    #cur.execute('select content from '+ tCol + ';' )
  elif trivia == 'Sayings':
    tCol = 'sayings'
    cur.execute('select content, author from '+tCol+' order by rand() limit 3')
    #cur.execute('select content, author from ' + tCol + ';')
  elif trivia == 'Fortune Cookies':
    tCol = 'fortuneCookies'
    cur.execute('select content from '+tCol+' order by rand() limit 3')
    #cur.execute('select content from ' + tCol + ';')
  elif trivia == 'Meme':
    tCol = 'meme'
    cur.execute('select content, imageLink from '+tCol+' order by rand() limit 3')
    #cur.execute('select content from ' + tCol + ';')
    
  rows = cur.fetchall()
  print rows
  return render_template('triviadisplay.html', trivia=trivia, rows=rows)

def report():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  query = 'select * from games'
  cur.execute(query)
  rows = cur.fetchall()
  return render_template('report.html', games=rows, selectedMenu='report')

def report2():
  
  firstname = request.form['firstname']
  lastname = request.form['lastname']
  username = request.form['username']
  password = request.form['password']
  school = request.form['school']
  city = request.form['city']
  state = request.form['state']
  game = request.form['game']
  
  #query = "SELECT id from games where title = '" + game + "'"
  #"(SELECT id from users where users.username ='" + username + "' AND users.password ='" + password + "')"
    
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)

  query = "INSERT INTO users (firstname, lastname, username, password, game) VALUES ('";
  query += request.form['firstname'] + "', '" + request.form['lastname'] + "', '" + username + "', '" + password + "', (SELECT id from games where games.title = '" + game + "'))"
  print query
  cur.execute(query)
  db.commit()
  
  query = "INSERT INTO userInfo (userid, school, city, state) VALUES ((SELECT id from users where users.username ='" + username + "' AND users.password ='" + password + "'),'" + school + "' , '" + city + "', '" + state + "')"   
  print query
  cur.execute(query)
  db.commit()
    
  return redirect(url_for('list'))

def register():
  global currentUser
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  # if user typed in a post ...
  if request.method == 'POST':
    un = MySQLdb.escape_string(request.form['username'])
    pw = MySQLdb.escape_string(request.form['pw'])
    stop = 0
    query = "SELECT COUNT(*) FROM users"
    cur.execute(query)
    countBefore = cur.fetchall()
    query = "INSERT INTO users (username) SELECT name FROM (SELECT '%s' AS name) t WHERE NOT EXISTS (SELECT * FROM users WHERE username = '%s')" % (un, un)
    cur.execute(query)
    db.commit( )
    query = "SELECT COUNT(*) FROM users"
    cur.execute(query)
    countAfter = cur.fetchall()
    if countAfter == countBefore:
      stop = 1
    if stop != 1:
      query2 = "SELECT id FROM users WHERE username = '%s'" % (un)
      cur.execute(query2)
      tida = cur.fetchall( )
      tid = tida[0]['id']
      query3 = "INSERT INTO user_passwords (password, user_id) VALUES (SHA2('%s',0), %d)" % (pw, tid)
      cur.execute(query3)
      db.commit( )
      currentUser = un
      return redirect(url_for('mainIndex'))
    else:
      warn = "That username already exists!"
      return render_template('warning.html', warn = warn)
  return render_template('register.html', curus = currentUser)

def report2():
  query = 'INSERT'
  query2 = 'INSERT'
  query3 = 'INSERT'
  query4 = 'INSERT'
  print query
  print query2
  print query3
  print query4
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  query = "INSERT INTO Star_Wars (Name) VALUES ('"
  query += request.form['name'] + "')"
  query2 = "INSERT INTO Appearance (Species, Gender) VALUES ('"
  query2 += request.form['species'] + "', '" + request.form['gender'] + "')"
  query3 = "INSERT INTO Ability_Scores (Str, Dex, Con, Intl, Wis, Cha) VALUES ("
  query3 += request.form['str'] + ", " + request.form['dex'] + ", " + request.form['con'] + ", " + request.form['intl'] + ", " + request.form['wis'] + ", " + request.form['cha'] + ")"
  query4 = "INSERT INTO Class_Levels (Soldier, Jedi, Scoundrel, Scout, Noble) VALUES ("
  query4 += request.form['soldier'] + ", " + request.form['jedi'] + ", " + request.form['scoundrel'] + ", " + request.form['scout'] + ", " + request.form['noble'] + ")"
  
  print query
  print query2
  print query3
  print query4
  cur.execute(query);
  cur.execute(query2);
  cur.execute(query3);
  cur.execute(query4);
  db.commit()
  return redirect(url_for('starWars'))

def editTutor2():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  classes = request.args.get('classes')
  tempClass = classes.split(',')
  first = request.args.get('first')
  last = request.args.get('last')
  created=""
  line = ""
  email = request.args.get('email')
  if request.method == "POST":
    num = request.form['CourseNum']
    subject = request.form['Subject']
    if num and subject:
      course = subject + "-" + num
      query = "SELECT classes FROM users WHERE email = '" + email + "' AND classes LIKE '%" + course + "%';"
      cur.execute(query)
      test = cur.fetchone()
    else:
      course = ""
      test = None
    delete = request.form['tutorCourse']
    if test:
      created="exist"
    else:
      if course and not delete:
        created = "updated"
        query3 = "UPDATE users SET classes = CONCAT(classes, ',%s') WHERE email = '%s';" % (course, email)
        cur.execute(query3)
        db.commit()
      elif course and delete:
        created = "both"
        for data in tempClass:
          if data != delete:
            if line == "":
              line = data
            else:
              line = line + "," + data
        query3 = "UPDATE users SET classes = '%s' WHERE email = '%s';" % (line, email)
        cur.execute(query3)
        db.commit()
        query3 = "UPDATE users SET classes = CONCAT(classes, ',%s') WHERE email = '%s';" % (course, email)
        cur.execute(query3)
        db.commit()
      elif not course and delete:
        created = "deleted"
        for data in tempClass:
          if data != delete:
            if line == "":
              line = data
            else:
              line = line + "," + data
        query3 = "UPDATE users SET classes = '%s' WHERE email = '%s';" % (line, email)
        cur.execute(query3)
        db.commit()
      elif not course and not delete:
        created = "nothing"
      return redirect(url_for('editTutor', created = created))
  return render_template('editTutor2.html', classes = tempClass, created=created, first=first, last=last)

def sched3():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  selClass = request.form['class']
  query = "SELECT firstname, lastname, numId FROM users WHERE classes LIKE '%" + selClass + "%';"
  cur.execute(query)
  tutors = cur.fetchall()
  return render_template('sched3.html', results = tutors, course=selClass)  

def damages2():
    db = utils.db_connect()
    cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)   
    query = "SELECT b.address, hd.type, hd.cost FROM basicHouse b INNER JOIN house_damages hd ON b.house_id = hd.house_id AND b.address LIKE '%" + MySQLdb.escape_string(request.form['address']) + "%'ORDER BY b.address;" 
    cur.execute(query)
    rows = cur.fetchall()
    print(rows)
    return render_template('damages2.html', name = currentUser,damages = rows)

def friendDebtIndex():
		global currentUser
		db = utils.db_connect()
		cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
		query1 = "SELECT ul.username, ud.transaction, ud.description, ud.debt_amount from user_list ul join user_debt ud on ul.id = ud.id where ul.username <> '%s'" % (currentUser)
		cur.execute(query1)
		rows = cur.fetchall()
		db.commit()
		return render_template('friendDebt.html', selectedMenu = 'FriendsInDebt',friend_debt=rows)

def logout():
    global currentUser
    db = utils.db_connect()
    cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    # if user typed in a post ...
    if request.method == 'POST':
      currentUser = ''
      return redirect(url_for('mainIndex'))
    return render_template('logout.html', curus = currentUser)