//ShowMeAllTheBoxes handler page displays the historical data
func ShowMeAllTheBoxes(rw http.ResponseWriter, r *http.Request) {
	lastMonth := time.Now().AddDate(0, 0, -30).Format("2006-01-02 00:00:00")
	database, tx := boxcheckerbackend.MakeDBConnection()
	var UserID, LastWeek, AllTime, i int
	var Date string

	mytools.Debug("Retrieving Historical Rows")
	rows, err := database.Query("SELECT * FROM historical WHERE date >= ? ORDER BY userid", lastMonth)
	mytools.CheckError("Query Members Table", err)

	fmt.Fprintln(rw, "This is the raw data for all the box calculations, only the last 30 days is shown.")
	fmt.Fprintln(rw, "UserId		Date		\"This week\"	\"All Time\"")
	i = 1

	for rows.Next() {
		err := rows.Scan(&UserID, &Date, &LastWeek, &AllTime)
		mytools.CheckError("scan row results", err)
		fmt.Fprintln(rw, fmt.Sprint(UserID, "	", Date, "	", LastWeek, "	", AllTime))
		i++
	}
	err = rows.Err()
	mytools.CheckError("after last row", err)
	rows.Close()
	err = tx.Commit()
	mytools.CheckError("tx.Commit()", err)
	database.Close()
}
//ShowMeTheBoxesOld try to use only one template.
func ShowMeTheBoxesOld(rw http.ResponseWriter, r *http.Request) {
	/*
		Query the database Members table and then cycles through the  ordered results
		and prints out some html templates in a table.
	*/
	go mytools.Log("Access", fmt.Sprint("/ShowMeTheBoxesOld Request from ", r.RemoteAddr, " at ", time.Now()))
	database, tx := boxcheckerbackend.MakeDBConnection()
	var i, j, CurrentMemberCount int
	var AverageBoxes float32
	var PageData ShowMePageData

	//var stats ClanStats
	const white = "#FFFFFF"
	const green = "#99FF99"
	const greenDark = "#7ACC7A"
	const brown = "#CC8845"
	const brownDark = "#996633"
	const red = "#FFCCCC"
	const redDark = "#CCA5A5"

	CurrentMemberCount = len(boxcheckerbackend.GetClanMembers().Members)
	TableRows := make([]TableRow, CurrentMemberCount)

	err := database.QueryRow("SELECT AVG(`lastmonthboxcount`) FROM `members`").Scan(&AverageBoxes)
	mytools.CheckError("AverageBoxes", err)
	PageData.Average = int(AverageBoxes)

	page, err := template.ParseFiles("showmetheboxesold.html")
	mytools.CheckError("Parsing template", err)

	//query database for members table
	rows, err := database.Query("SELECT * FROM members WHERE active = ? ORDER BY lastmonthboxcount DESC", true)

	i = 1
	j = 0

	for rows.Next() {
		TableRows[j].Comment = ""
		TableRows[j].Position = i

		err := rows.Scan(&TableRows[j].UserID, &TableRows[j].Name, &TableRows[j].BoxCount, &TableRows[j].Days, &TableRows[j].Since, &TableRows[j].Active, &TableRows[j].LastActiveDate)
		mytools.CheckError("scan row results", err)
		if TableRows[j].Days > 0 { //avoid div by 0
			TableRows[j].AverageBoxesPerDay = int(TableRows[j].BoxCount / TableRows[j].Days)
		} else {
			TableRows[j].AverageBoxesPerDay = 0
		}
		if TableRows[j].BoxCount > int(AverageBoxes) { //green
			if i%2 == 0 {
				TableRows[j].Colour = greenDark
			} else {
				TableRows[j].Colour = green
			}
		} else { //red rows for below AverageBoxes
			if i%2 == 0 {
				TableRows[j].Colour = redDark
			} else {
				TableRows[j].Colour = red
			}
		}
		if TableRows[j].BoxCount == 0 { //Commence shitter logic for the guys with no boxes
			if i%2 == 0 { //row shading
				TableRows[j].Colour = brownDark
			} else {
				TableRows[j].Colour = brown
			}
			TableRows[j].Comment = "No Box Shitter"
			if TableRows[j].Name == "Powderworx" {
				TableRows[j].Comment = "Head Shitter" //Colour commentary
			}
			TableRows[j].LastActiveDate = playerchecker.GetPlayerData(TableRows[j].UserID).LastBattleTime.Format("2006-01-02")
		}
		//'special' handling logics  first place has no life etc.
		if TableRows[j].BoxCount < PageData.Average { //Nestingthese checks removes the need for an if j>0 check
			if TableRows[j-1].BoxCount > PageData.Average {
				TableRows[j].Comment = "So Close!"
				TableRows[j-1].Comment = "Phew!"
			}
		}
		if TableRows[j].BoxCount == PageData.Average {
			TableRows[j].Comment = "Mr. Average"
		}
		i++
		j++
	}
	TableRows[0].Comment = "No life Super Stah!"

	TableRows[9].Comment = "Top Ten Baby!"

	PageData.Rows = TableRows
	mytools.CheckError("Executing Page", page.Execute(rw, PageData))

	//Clean up
	rows.Close()
	err = tx.Commit()
	mytools.CheckError("tx.Commit()", err)
	database.Close()
}
//ShowMeTheBoxesNew is a page handler does more than it should
func ShowMeTheBoxesNew(rw http.ResponseWriter, r *http.Request) {
	/*
		Query the database Members table and then cycles through the  ordered results
		and prints out some html templates in a table.
	*/

	go mytools.Log("Access", fmt.Sprint("/ShowMeTheBoxes Request from ", r.RemoteAddr, " at ", time.Now()))
	database, tx := boxcheckerbackend.MakeDBConnection()
	var i, j, CurrentMemberCount int
	var AverageBoxes float32
	var PageData ShowMePageData

	//var stats ClanStats

	CurrentMemberCount = len(boxcheckerbackend.GetClanMembers().Members)
	TableRows := make([]TableRow, CurrentMemberCount)

	err := database.QueryRow("SELECT AVG(`lastmonthboxcount`) FROM `members`").Scan(&AverageBoxes)
	mytools.CheckError("AverageBoxes", err)
	PageData.Average = int(AverageBoxes)

	page, err := template.ParseFiles("showmetheboxes.html")
	mytools.CheckError("Parsing template", err)

	//query database for members table
	rows, err := database.Query("SELECT * FROM members WHERE active = ? ORDER BY lastmonthboxcount DESC", true)

	i = 1
	j = 0

	for rows.Next() {
		TableRows[j].Comment = ""
		TableRows[j].Position = i

		err := rows.Scan(&TableRows[j].UserID, &TableRows[j].Name, &TableRows[j].BoxCount, &TableRows[j].Days, &TableRows[j].Since, &TableRows[j].Active, &TableRows[j].LastActiveDate)
		mytools.CheckError("scan row results", err)
		if TableRows[j].Days > 0 { //avoid div by 0
			TableRows[j].AverageBoxesPerDay = int(TableRows[j].BoxCount / TableRows[j].Days)
		} else {
			TableRows[j].AverageBoxesPerDay = 0
		}
		if TableRows[j].BoxCount > 0 {
			TableRows[j].LastActiveDate = ""
		}
		if TableRows[j].BoxCount == 0 { //Commence shitter logic for the guys with no boxes
			TableRows[j].Comment = "No Box Shitter"
			if TableRows[j].Name == "Powderworx" {
				TableRows[j].Comment = "Head Shitter" //Colour commentary
			}
			//TableRows[j].LastActiveDate = playerchecker.GetPlayerData(TableRows[j].UserID).LastBattleTime.Format("2006-01-02")
		}

		/*		if TableRows[j].BoxCount < PageData.Average { //Nesting these checks removes the need for an if j>0 check
				if TableRows[j-1].BoxCount > PageData.Average {
					TableRows[j].Comment = "So Close!" //almost above average
					TableRows[j-1].Comment = "Phew!"   //barely above average
				}
			}*/
		if TableRows[j].Name == "BluesmanRS" {
			TableRows[j].Comment = "Brasil!"
		}
		i++
		j++
	}
	TableRows[0].Comment = "No life Super Stah!"
	TableRows[9].Comment = "Top Ten Baby!"
	//get the rows into the page data struct then execute the page.
	PageData.Rows = TableRows
	ColourCodeRowsAverage(&PageData)

	mytools.CheckError("Executing Page", page.Execute(rw, PageData))

	//Clean up
	rows.Close()
	err = tx.Commit()
	mytools.CheckError("tx.Commit()", err)
	database.Close()

}