public ResultItem GetResultItem(int riderNo)
{
SQLiteCommand query = new SQLiteCommand();
query.CommandText = "SELECT rider_no, rider_first, rider_last, rider_dob, phone, email, member FROM [" + Year +
"_rider] WHERE rider_no = @noparam;";
query.CommandType = System.Data.CommandType.Text;
query.Parameters.Add(new SQLiteParameter("@noparam", riderNo));
query.Connection = ClubConn;
SQLiteDataReader reader = DoTheReader(query);
ResultItem item = new ResultItem();
while (reader.Read())
{
item.BackNo = reader.GetInt32(0);
item.RiderNo = reader.GetInt32(1);
item.Rider = reader.GetString(2) + " " + reader.GetString(3);
item.HorseNo = reader.GetInt32(4);
item.Horse = reader.GetString(5);
item.ShowNo = reader.GetInt32(6);
item.ShowDate = reader.GetString(7);
item.ClassNo = reader.GetInt32(8);
item.ClassName = reader.GetString(9);
item.Place = reader.GetInt32(10);
item.Time = reader.GetDecimal(11);
item.Points = reader.GetInt32(12);
item.PayIn = reader.GetDecimal(13);
item.PayOut = reader.GetDecimal(14);
}
reader.Close();
ClubConn.Close();
return item;
}
// Optional: sort (default is back_no)
public List<ResultItem> GetResultItemList(ResultSort sort = ResultSort.Default)
{
// Case statment for sort column
string sortString = null;
switch (sort)
{
case ResultSort.BackNo: sortString = "b.back_no, s.date, c.class_no"; break;
case ResultSort.Class: sortString = "c.class_no"; break;
case ResultSort.Horse: sortString = "h.horse_no"; break;
case ResultSort.Place: sortString = "t.place"; break;
case ResultSort.Rider: sortString = "r.rider_last, r.rider_first, s.date, c.class_no, t.place"; break;
case ResultSort.Show: sortString = "s.date, c.class_no"; break;
default: sortString = "back_no"; break;
}
string query = "SELECT b.back_no, r.rider_no, r.rider_first, r.rider_last, h.horse_no, h.horse_name, " +
"s.show_no, s.date, c.class_no, c.class_name, t.place, t.time, t.points, t.paid_in, t.paid_out " +
"FROM [" + Year + "_result] AS t JOIN [" + Year + "_backNo] AS b ON t.back_no = b.back_no " +
"JOIN [" + Year + "_rider] AS r ON b.rider_no = r.rider_no " +
"JOIN [" + Year + "_horse] AS h ON b.horse_no = h.horse_no " +
"JOIN [" + Year + "_class] AS c ON t.class_no = c.class_no " +
"JOIN [" + Year + "_show] AS s ON t.show_no = s.show_no " + "ORDER BY " + sort + ";";
SQLiteDataReader reader = DoTheReader(ClubConn, query);
List<ResultItem> resultItemList = new List<ResultItem>();
ResultItem item;
reader = DoTheReader(ClubConn, query);
while (reader.Read())
{
item = new ResultItem();
item.BackNo = reader.GetInt32(0);
item.RiderNo = reader.GetInt32(1);
item.Rider = reader.GetString(2) + " " + reader.GetString(3);
item.HorseNo = reader.GetInt32(4);
item.Horse = reader.GetString(5);
item.ShowNo = reader.GetInt32(6);
item.ShowDate = reader.GetString(7);
item.ClassNo = reader.GetInt32(8);
item.ClassName = reader.GetString(9);
item.Place = reader.GetInt32(10);
item.Time = reader.GetDecimal(11);
item.Points = reader.GetInt32(12);
item.PayIn = reader.GetDecimal(13);
item.PayOut = reader.GetDecimal(14);
resultItemList.Add(item);
}
reader.Close();
ClubConn.Close();
return resultItemList;
}
private string GetHeaderName(ReportType type, ResultItem item)
{
string header = String.Empty;
switch (type)
{
case ReportType.Category: header = item.ShowDate ; break;
case ReportType.Class: header = item.ShowDate; break;
//case ReportType.Horse: header = Database.GetHorseItem(no).Name; break;
//case ReportType.Rider: RiderItem riderItem = Database.GetRiderItem(no);
// header = riderItem.FirstName + " " + riderItem.LastName;
// break;
//case ReportType.Show: ShowItem showItem = Database.GetShowItem(no);
// header = showItem.Date.ToString("MM/dd/yyyy");
// if (showItem.Name != null) { header += " - " + showItem.Name; }
// break;
case ReportType.Year: header = Year.ToString(); break;
default: header = null; break;
}
return header;
}
