private void LoadSurfaceComboBox()
{
_cbSurface.Items.Clear();
string trackCode = CurrentTrackCode.Trim();
if (trackCode.Length <= 0)
return;
using (var dbr = new DbReader())
{
if (dbr.Open(string.Format(@"select distinct(surface COLLATE SQL_Latin1_General_CP1_CS_AS) 'surface' from race_description where track_code='{0}'", trackCode)))
{
while (dbr.MoveToNextRow())
{
_cbSurface.Items.Add(dbr.GetValue<string>("surface"));
}
}
}
if (_cbSurface.Items.Count > 0)
{
_cbSurface.SelectedIndex = 0;
}
}
private void LoadCurrentRaceInfo(DateTime date, string trackCode, int raceNumber)
{
_currentRaceInfo = null;
string sql = string.Format(
@"SELECT RACE_ID, DISTANCE, ABOUT_DISTANCE_FLAG, SURFACE, TRACK_CODE, TRACK_CONDITION FROM RACE_DESCRIPTION
WHERE DATE_OF_THE_RACE = '{0}' AND TRACK_CODE = '{1}' AND RACE_NUMBER = {2}", Utilities.GetDateInYYYYMMDD(date), trackCode, raceNumber);
using (var dbr = new DbReader())
{
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
_currentRaceInfo = new RaceInfo();
_currentRaceInfo.AboutFlag = dbr.GetValue<string>("ABOUT_DISTANCE_FLAG");
_currentRaceInfo.Distance = dbr.GetValue<double>("DISTANCE");
_currentRaceInfo.RaceId = dbr.GetValue<int>("RACE_ID");
_currentRaceInfo.Surface = dbr.GetValue<string>("SURFACE");
_currentRaceInfo.TrackCondition = dbr.GetValue<string>("TRACK_CONDITION");
_currentRaceInfo.TrackCode = trackCode;
}
}
}
}
public static bool RaceExistsInDb(string trackCode, DateTime date, int raceNumber)
{
int count = 0;
using (var dbr = new DbReader())
{
string sql = string.Format("SELECT COUNT(*) 'COUNTER' FROM RACE_DESCRIPTION WHERE TRACK_CODE = '{0}' AND DATE_OF_THE_RACE = '{1}' AND RACE_NUMBER = {2} ", trackCode, Utilities.GetDateInYYYYMMDD(date), raceNumber);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
count = dbr.GetValue<int>("COUNTER");
}
}
}
return count > 0;
}
private void Load()
{
string sql = string.Format("exec GetSummarizedStatsForTrainer '{0}'", Name);
using (var dbr = new DbReader())
{
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
StartersCount = dbr.GetValue<int>("STARTERS");
WinPercent = dbr.GetValue<double>("WIN_PERCENT");
ROI = dbr.GetValue<double>("ROI");
IV = dbr.GetValue<double>("IV");
}
}
}
}
static void LoadTrainers()
{
_trainerIds = new Dictionary<string, int>();
using (var dbr = new DbReader())
{
if (dbr.Open("select TRAINER_ID, ABBR_TRAINER_NAME FROM TRAINERS"))
{
while (dbr.MoveToNextRow())
{
int id = dbr.GetValue<int>("TRAINER_ID");
string name = dbr.GetValue<string>("ABBR_TRAINER_NAME");
_trainerIds.Add(name.ToUpper().Trim(),id);
}
}
}
}
public StarterInfo(int raceid, string horseName)
{
_existsInTheDb = false;
using (var dbr = new DbReader())
{
string sql = string.Format(@"EXEC GetHorseFractions {0}, '{1}' ", raceid, horseName);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
_fraction1 = dbr.GetValue<double>("CALL_1");
_fraction2 = dbr.GetValue<double>("CALL_2");
_fraction3 = dbr.GetValue<double>("CALL_3");
_finalTime = dbr.GetValue<double>("FINAL_CALL");
_existsInTheDb = true;
}
}
}
}
void Initialize()
{
_variantMap.Clear();
using (var dbr = new DbReader())
{
string sql = @"SELECT TRACK_CODE, RACING_DATE, TRACK_VARIANT_ESTIMATE, TRACK_VARIANT , NUMBER_OF_STARTERS_FOR_TV FROM TV_TRACK_VARIANT ";
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
string trackCode = dbr.GetValue<string>("TRACK_CODE");
string date = dbr.GetValue<string>("RACING_DATE");
double tv1 = dbr.GetValue<double>("TRACK_VARIANT_ESTIMATE");
double tv2 = dbr.GetValue<double>("TRACK_VARIANT");
int count = dbr.GetValue<int>("NUMBER_OF_STARTERS_FOR_TV");
_variantMap.Add(MakeKey(trackCode,date),count > 0 ? tv2 : tv1);
}
}
}
}
private void LoadListBox()
{
try
{
_listBox.Items.Clear();
using (var dbr = new DbReader())
{
if (dbr.Open(@"SELECT DISTINCT(ABBR_JOCKEY_NAME) 'JOCKEY' FROM RACE_STARTERS WHERE ABBR_JOCKEY_NAME != '' ORDER BY 'JOCKEY'"))
{
while (dbr.MoveToNextRow())
{
_listBox.Items.Add(dbr.GetValue<string>("JOCKEY"));
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
private PaceFigure(string trackCode, string date, int raceNumber, string horseNumber)
{
var sql = string.Format("exec GetPaceFigureForSpecificRace '{0}', '{1}', {2}, '{3}'", trackCode, date, raceNumber, horseNumber);
using (var dbr = new DbReader())
{
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
_figure1ForTheRace = dbr.GetValue<int>("figure1");
_figure2ForTheRace = dbr.GetValue<int>("figure2");
_finalCallForTheRace = dbr.GetValue<int>("finalFigure");
_figure1ForTheHorse = dbr.GetValue<int>("figure1ForTheHorse");
_figure2ForTheHorse = dbr.GetValue<int>("figure2ForTheHorse");
_finalCallForTheHorse = dbr.GetValue<int>("finalFigureForTheHorse");
}
}
}
}
public static List<DateTime> GetDates(string trackCode)
{
if (_racesPool.ContainsKey(trackCode))
{
return _racesPool[trackCode];
}
else
{
using (var dbr = new DbReader())
{
var dates = new List<DateTime>();
if (dbr.Open(string.Format(@"Select DATE_OF_THE_RACE FROM RACE_DESCRIPTION WHERE TRACK_CODE = '{0}'", trackCode)))
{
while (dbr.MoveToNextRow())
{
dates.Add(Utilities.MakeDateTime(dbr.GetValue<string>("DATE_OF_THE_RACE")));
}
}
_racesPool.Add(trackCode, dates);
return dates;
}
}
}
public static List<WinnerInfo> LoadFromDb(string trackCode, double distance, string surface, string aboutFlag)
{
var list = new List<WinnerInfo>();
using (var dbr = new DbReader())
{
string sql = GetSqlLoader(trackCode, distance, surface, aboutFlag);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
list.Add(new WinnerInfo(dbr));
}
}
}
list = RemoveZeroValues(list, (WinnerInfo wik) => wik.FirstFraction());
list = RemoveZeroValues(list, (WinnerInfo wik) => wik.SecondFraction());
list = RemoveZeroValues(list, (WinnerInfo wik) => wik.ThirdFraction());
return list;
}
private static int GetRaceIdFromDb(string trackCode, int year, int month, int day, int raceNumber)
{
int raceid = -1;
using (var dbr = new DbReader())
{
string date = string.Format("{0:0000}{1:00}{2:00}", year, month, day);
string sql = string.Format(@"SELECT RACE_ID FROM RACE_DESCRIPTION WHERE TRACK_CODE = '{0}' AND DATE_OF_THE_RACE = '{1}' AND RACE_NUMBER= {2}", trackCode, date, raceNumber);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
raceid = dbr.GetValue<int>("RACE_ID");
}
}
}
return raceid;
}
public List<double> ListOfAvailableDistancesPerSurface(string surface)
{
var distances = new List<double>();
using (var dbr = new DbReader())
{
string sqlLoader = string.Format(@"select distinct(DISTANCE) AS 'DIST' from RACE_DESCRIPTION where TRACK_CODE = '{0}' AND SURFACE = '{1}' COLLATE SQL_Latin1_General_CP1_CS_AS", this.TrackCode, surface);
if (dbr.Open(sqlLoader))
{
while (dbr.MoveToNextRow())
{
distances.Add(dbr.GetValue<double>("DIST"));
}
}
}
return distances;
}
private static double GetDistance(int raceid)
{
double distance = 0.0;
using (var dbr = new DbReader())
{
string sql = string.Format("Select DISTANCE from RACE_DESCRIPTION Where race_id = {0}", raceid);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
distance = dbr.GetValue<double>("DISTANCE");
}
}
}
return distance;
}
public static List<HorseInfo> LoadFromDb(Factor factor)
{
var hi = new List<HorseInfo>();
using (var dbr = new DbReader())
{
var sql = string.Format("EXEC SelectAllMatchingHorsesByFactorsFlag {0}", factor.BitMask);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
hi.Add(new HorseInfo(dbr));
}
}
}
return hi;
}
private void LoadGolendPaceFigureForThisHorse()
{
_goldenPaceFigureForThisHorse = -999;
if (!IsATurfRace)
{
using (var dbr = new DbReader())
{
int raceNumber = 0;
if (int.TryParse(RaceNumber, out raceNumber))
{
string sql = string.Format("select dbo.GetGoldenPaceFigureForSpecificStarter ('{0}','{1}',{2}, '{3}') 'GoldenPaceFigure' ", TrackCode, Utilities.GetDateInYYYYMMDD(Date), raceNumber, this.Parent.Name);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
_goldenPaceFigureForThisHorse = dbr.GetValue<double>("GoldenPaceFigure");
}
}
}
}
}
}
private void LoadGoldenTrackVariant()
{
_goldenTrackVariant = -999;
if (!IsATurfRace)
{
using (var dbr = new DbReader())
{
string sql = string.Format("select dbo.GetGoldenTrackVariant ('{0}','{1}' ) 'GoldenTrackVariant' ", TrackCode, Utilities.GetDateInYYYYMMDD(Date));
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
_goldenTrackVariant = dbr.GetValue<double>("GoldenTrackVariant");
}
}
}
}
}
private void LoadGoldenFigures()
{
_goldenFigureForTheWinnerOfTheRace = -999.0;
_goldenFigureForThisHorse = -999.0;
if (!IsATurfRace)
{
using (var dbr = new DbReader())
{
double lengthsBehind = 0.0;
string sql = string.Format("select dbo.GoldenSpeedFigure ('{0}', '{1}', {2}, {3}, {4}) 'SpeedFigure' ", TrackCode, Utilities.GetDateInYYYYMMDD(Date), WinnersFinalTime, lengthsBehind, DistanceInYards);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
_goldenFigureForTheWinnerOfTheRace = dbr.GetValue<double>("SpeedFigure");
}
}
int finalPosition = 0;
int.TryParse(_finalPosition, out finalPosition);
if (1 == finalPosition)
{
_goldenFigureForThisHorse = _goldenFigureForTheWinnerOfTheRace;
}
else if (0 == finalPosition)
{
_goldenFigureForThisHorse = -999.0;
}
else
{
lengthsBehind = _rawFinalCallDistanceFromLeader;
sql = string.Format("select dbo.GoldenSpeedFigure ( '{0}', '{1}', {2}, {3}, {4}) 'SpeedFigure' ", TrackCode, Utilities.GetDateInYYYYMMDD(Date), WinnersFinalTime, lengthsBehind, DistanceInYards);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
_goldenFigureForThisHorse = dbr.GetValue<double>("SpeedFigure");
}
}
}
}
}
LoadGolendPaceFigureForTheRace();
LoadGolendPaceFigureForThisHorse();
LoadGoldenTrackVariant();
_needsToLoadGoldenFigure = false;
}
public List<string> ListOfAvailableAboutFlagPerDistanceAndSurface(string surface, double distance)
{
var aboutFlags = new List<string>();
using (var dbr = new DbReader())
{
string sqlLoader = string.Format(@"select distinct(ABOUT_DISTANCE_FLAG) AS 'AFLAG' from RACE_DESCRIPTION where TRACK_CODE = '{0}' AND DISTANCE = {1} AND SURFACE = '{2}' COLLATE SQL_Latin1_General_CP1_CS_AS", this.TrackCode, distance, surface);
if (dbr.Open(sqlLoader))
{
while (dbr.MoveToNextRow())
{
aboutFlags.Add(dbr.GetValue<string>("AFLAG"));
}
}
}
return aboutFlags;
}
internal void AssignFinalOddsFromDb()
{
if (_raceID <= 0)
{
return;
}
using (var dbr = new DbReader())
{
string sql = string.Format("Select PROGRAM_NUMBER, ODDS From Race_starters where race_id = {0} AND PROGRAM_NUMBER != 'SCR'", _raceID);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
int programNumber;
if (int.TryParse(dbr.GetValue<string>("PROGRAM_NUMBER"), out programNumber))
{
double finalOdss = dbr.GetValue<double>("ODDS");
_horse.ForEach(h => {
if (h.GetProgramNumberWithoutEntryChar() == programNumber)
h.FinalOdds = finalOdss;
}
);
}
}
}
}
}
static void LoadLastUpdatedDay()
{
_lastDayInDataBase = new Dictionary<string, string>();
_lastDayInDataBase.Clear();
using (var dbr = new DbReader())
{
if (dbr.Open(@"SELECT TRACK_CODE, MAX(DATE_OF_THE_RACE) 'MAX_DATE' FROM RACE_DESCRIPTION GROUP BY TRACK_CODE "))
{
while (dbr.MoveToNextRow())
{
string trackCode = dbr.GetValue<string>("TRACK_CODE");
string maxDate = dbr.GetValue<string>("MAX_DATE");
_lastDayInDataBase.Add(trackCode.Trim().ToUpper(), maxDate);
}
}
}
}
public static Sire CreateSireFromDb(string name)
{
Sire s= null;
string sql = string.Format("select STATE, NAME, COUNTRY, YEAR_OF_BIRTH, SPI, DISTANCE, FIRST_TIME_STARTERS, MUD_STARTERS, TURF_STARTERS, ALL_WEATHER_STARTERS, AVG_WINNING_DISTANCE from sire_stats where name='{0}'", name);
using (var dbr = new DbReader())
{
if (dbr.Open(string.Format(sql)))
{
while (dbr.MoveToNextRow())
{
s = new Sire(dbr);
}
}
}
return s;
}
// June 18 2010
// Used from research programs
public void LoadResultsFromDb()
{
using (var dbr = new DbReader())
{
foreach (var race in _race)
{
string sql = @"SELECT PROGRAM_NUMBER, FAVORITE_FLAG, ODDS , OFFICIAL_POSITION
FROM RACE_STARTERS
WHERE TRACK_CODE = '{0}' AND RACING_DATE = '{1}' AND RACE_NUMBER = {2} AND PROGRAM_NUMBER != 'SCR' ";
sql = string.Format(sql, TrackCode, Date, race.RaceNumber);
if (dbr.Open(sql))
{
while (dbr.MoveToNextRow())
{
var pn = dbr.GetValue<string>("PROGRAM_NUMBER");
var fv = dbr.GetValue<int>("FAVORITE_FLAG");
var odds = dbr.GetValue<double>("ODDS");
var pos = dbr.GetValue<int>("OFFICIAL_POSITION");
var horse = race.GetHorseByProgramNumber(pn);
if (null != horse)
{
horse.WasTheBettingFavorite = (1 == fv);
horse.FinalOdds = odds;
horse.FinalPosition = pos;
}
}
}
}
}
}