public static List <Outing> GetPlayerOutings(int season)
{
Dictionary <int, Outing> outDict = new Dictionary <int, Outing>();
Dictionary <int, Dictionary <int, Group> > groupDict = new Dictionary <int, Dictionary <int, Group> >();
const string sql = @"
select OutingID, o.CourseID, OutingDate, SkinValue, HicValue, LowNetValue, KPValue, c.Name, Slope, Rating, HoleID, HoleNumber, Par, Committed, Settled, NoFlipSkins
from Outing o
inner join Course c on c.CourseID = o.CourseID
inner join Hole h on h.CourseID = c.CourseID
where o.season = @season and o.Committed = '1' and h.Par != 0
order by OutingDate desc
";
using (SqlConnection conn = DB.GetConnection(Settings.GetDSN()))
{
using (SqlDataReader rdr = DB.ExecSQLQuery(sql, conn, "@season", season.ToString()))
{
while (rdr.Read())
{
int outingID = rdr.GetInt32(0);
// If this outing not found in dictionary, create a new outing and add it
if (!outDict.ContainsKey(outingID))
{
outDict[outingID] = new Outing
{
OutingID = outingID,
CourseID = rdr.GetInt32(1),
OutingDate = rdr.GetDateTime(2),
SkinValue = rdr.GetDecimal(3),
HicValue = rdr.GetDecimal(4),
LowNetValue = rdr.GetDecimal(5),
KPValue = rdr.GetDecimal(6),
Committed = rdr.GetStringOrDefault(13) == "1",
Settled = rdr.GetStringOrDefault(14) == "1",
NoFlipSkins = rdr.GetStringOrDefault(15) == "1",
Season = season,
Course = new Course
{
CourseID = rdr.GetInt32(1),
Name = rdr.GetString(7),
Slope = rdr.GetInt32(8),
Rating = rdr.GetDecimal(9)
}
};
}
Outing outing = outDict[outingID];
// Get hole info from all records
outing.Course.HoleList.Add(new Hole
{
HoleID = rdr.GetInt32(10),
HoleNumber = rdr.GetInt32(11),
Par = rdr.GetInt32(12),
CourseID = outing.CourseID
});
}
}
// If no outings, can return now
if (outDict.Count == 0)
{
return(outDict.Values.ToList());
}
const string groupsql = @"
select g.GroupID, g.OutingID, p.PlayerID, p.LastName, p.FirstName, p.GHIN, p.HIndex, p.Email, p.TextNumber, s.HoleNumber, s.Strokes, s.HIndex, s.Hics, s.KP, s.SkinCarryOvers
from OutingGroup g
left join GroupPlayer pg on pg.GroupID = g.GroupID
left join Player p on p.PlayerID = pg.PlayerID
left join Score s on s.PlayerID = p.PlayerID and s.OutingID = g.OutingID
left join Outing o on o.Outingid = g.OutingID
where o.Committed = '1'";
using (SqlDataReader rdr = DB.ExecSQLQuery(groupsql, conn))
{
while (rdr.Read())
{
int groupID = rdr.GetInt32(0);
int outingID = rdr.GetInt32(1);
if (!groupDict.ContainsKey(outingID))
{
groupDict[outingID] = new Dictionary <int, Group>();
}
if (!groupDict[outingID].ContainsKey(groupID))
{
groupDict[outingID].Add(groupID, new Group
{
GroupID = groupID,
OutingID = outingID
});
}
int?playerID = rdr.IsDBNull(2) ? (int?)null : rdr.GetInt32(2);
if (playerID.HasValue)
{
// We may have already added this player since we are getting multiple rows in the query because
// of the join with the Scores table.
if (groupDict[outingID][groupID].PlayerList.All(x => x.Player.PlayerID != playerID))
{
Player p = new Player
{
PlayerID = (int)playerID,
LastName = rdr.GetString(3),
FirstName = rdr.GetString(4),
GHIN = rdr.GetString(5),
// Set the HIndex in this player instance to that found in the Score table since it
// is the HIndex for the player at the time the score was added whereas the HINdex
// in the Player table is their current HIndex
HIndex = rdr.IsDBNull(11) ? (decimal?)null : rdr.GetDecimal(11),
Email = rdr.GetString(7),
TextNumber = rdr.GetString(8)
};
groupDict[outingID][groupID].PlayerList.Add(new PlayerOuting {
Player = p, Season = season
});
}
// Get PlayerOuting
PlayerOuting po = groupDict[outingID][groupID].PlayerList.Find(x => x.Player.PlayerID == playerID);
// Add scores to player outing if found
if (!rdr.IsDBNull(9))
{
po.Scores.Add(new Score
{
HoleNumber = rdr.GetInt32(9),
Strokes = rdr.IsDBNull(10) ? 0 : rdr.GetInt32(10),
HIndex = rdr.IsDBNull(11) ? (decimal?)null : rdr.GetDecimal(11),
Hics = rdr.GetInt32(12),
KP = rdr.GetStringOrDefault(13) == "1",
SkinCarryOvers = rdr.GetStringOrDefault(14) == "1",
PlayerID = po.Player.PlayerID,
OutingID = outingID
});
}
}
}
}
}
foreach (int outid in outDict.Keys)
{
// Add groups in dictionary to Outings' group list
if (groupDict.ContainsKey(outid))
{
outDict[outid].GroupList = groupDict[outid].Values.ToList();
}
// Get list of holes.
List <int> holeNumList = outDict[outid].Course.HoleList.Select(x => x.HoleNumber).ToList();
// Add Score entries for each hole for each player if not already present
foreach (PlayerOuting po in outDict[outid].GroupList.SelectMany(x => x.PlayerList)) // This gets a list of all the players across the groups
{
foreach (int holenum in holeNumList)
{
// Check to see if the player's score list already contains an entry for this hole
if (po.Scores.Any(x => x.HoleNumber == holenum))
{
continue;
}
// Hole not found in list of scores for player so add a blank score
po.Scores.Add(new Score
{
HIndex = po.Player.HIndex,
HoleNumber = holenum,
OutingID = outDict[outid].OutingID,
PlayerID = po.Player.PlayerID,
Strokes = 0,
Hics = 0
});
}
// Sort the player's scores by holenumber so they are in sequence in UI
po.Scores.Sort((x, y) => x.HoleNumber.CompareTo(y.HoleNumber));
}
// Calculate skins for each score
CalculateSkins(outDict[outid]);
}
return(outDict.Values.ToList());
}
public static Outing GetOuting(int outingID)
{
const string sql = @"
select o.OutingID, o.CourseID, o.OutingDate, o.SkinValue, o.HicValue, o.LowNetValue, o.KPValue, c.Name, c.Slope, c.Rating, h.HoleID, h.HoleNumber, h.Par, o.Committed, o.season, o.Settled, o.NoFlipSkins
from Outing o
inner join Course c on c.CourseID = o.CourseID
inner join Hole h on h.CourseID = c.CourseID
where o.OutingID = @outingid and h.Par != 0
";
Outing outing = null;
Dictionary <int, Group> groupDict = new Dictionary <int, Group>();
using (SqlConnection conn = DB.GetConnection(Settings.GetDSN()))
{
using (SqlDataReader rdr = DB.ExecSQLQuery(sql, conn, "@outingid", outingID.ToString()))
{
while (rdr.Read())
{
if (outing == null)
{
// get common outing fields and course fields from first record
outing = new Outing
{
OutingID = rdr.GetInt32(0),
CourseID = rdr.GetInt32(1),
OutingDate = rdr.GetDateTime(2),
SkinValue = rdr.GetDecimal(3),
HicValue = rdr.GetDecimal(4),
LowNetValue = rdr.GetDecimal(5),
KPValue = rdr.GetDecimal(6),
Committed = rdr.GetStringOrDefault(13) == "1",
Season = rdr.GetInt32(14),
Settled = rdr.GetStringOrDefault(15) == "1",
NoFlipSkins = rdr.GetStringOrDefault(16) == "1",
Course = new Course
{
CourseID = rdr.GetInt32(1),
Name = rdr.GetString(7),
Slope = rdr.GetInt32(8),
Rating = rdr.GetDecimal(9)
}
};
}
// Get hole info from all records
outing.Course.HoleList.Add(new Hole
{
HoleID = rdr.GetInt32(10),
HoleNumber = rdr.GetInt32(11),
Par = rdr.GetInt32(12),
CourseID = outing.CourseID
});
}
}
if (outing == null)
{
return(null);
}
const string groupsql = @"
select g.GroupID, g.OutingID, p.PlayerID, p.LastName, p.FirstName, p.GHIN, p.HIndex, p.Email, p.TextNumber, s.HoleNumber, s.Strokes, s.HIndex, s.Hics, s.KP, s.SkinCarryOvers
from OutingGroup g
left join GroupPlayer pg on pg.GroupID = g.GroupID
left join Player p on p.PlayerID = pg.PlayerID
left join Score s on s.PlayerID = p.PlayerID and s.OutingID = g.OutingID
where g.OutingID = @outingid";
using (SqlDataReader rdr = DB.ExecSQLQuery(groupsql, conn, "@outingid", outingID.ToString()))
{
while (rdr.Read())
{
int groupID = rdr.GetInt32(0);
if (!groupDict.ContainsKey(groupID))
{
groupDict.Add(groupID, new Group
{
GroupID = groupID,
OutingID = rdr.GetInt32(1)
});
}
int?playerID = rdr.IsDBNull(2) ? (int?)null : rdr.GetInt32(2);
if (playerID.HasValue)
{
// We may have already added this player since we are getting multiple rows in the query because
// of the join with the Scores table.
if (groupDict[groupID].PlayerList.All(x => x.Player.PlayerID != playerID))
{
Player p = new Player
{
PlayerID = (int)playerID,
LastName = rdr.GetString(3),
FirstName = rdr.GetString(4),
GHIN = rdr.GetString(5),
HIndex = rdr.IsDBNull(6) ? (decimal?)null : rdr.GetDecimal(6),
Email = rdr.GetString(7),
TextNumber = rdr.GetString(8)
};
groupDict[groupID].PlayerList.Add(new PlayerOuting {
Player = p, Season = outing.Season
});
}
// Get PlayerOuting
PlayerOuting po = groupDict[groupID].PlayerList.Find(x => x.Player.PlayerID == playerID);
// Add scores to player outing if found
if (!rdr.IsDBNull(9))
{
po.Scores.Add(new Score
{
HoleNumber = rdr.GetInt32(9),
Strokes = rdr.IsDBNull(10) ? 0 : rdr.GetInt32(10),
HIndex = rdr.IsDBNull(11) ? (decimal?)null : rdr.GetDecimal(11),
Hics = rdr.GetInt32(12),
KP = rdr.GetStringOrDefault(13) == "1",
SkinCarryOvers = rdr.GetStringOrDefault(14) == "1",
PlayerID = po.Player.PlayerID,
OutingID = groupDict[groupID].OutingID
});
}
}
}
}
}
// Add groups in dictionary to Outing group list
if (groupDict.Count > 0)
{
outing.GroupList = groupDict.Values.ToList();
}
// Get list of holes.
List <int> holeNumList = outing.Course.HoleList.Select(x => x.HoleNumber).ToList();
// Add Score entries for each hole for each player if not already present
foreach (PlayerOuting po in outing.GroupList.SelectMany(x => x.PlayerList)) // This gets a list of all the players across the groups
{
foreach (int holenum in holeNumList)
{
// Check to see if the player's score list already contains an entry for this hole
if (po.Scores.Any(x => x.HoleNumber == holenum))
{
continue;
}
// Hole not found in list of scores for player so add a blank score
po.Scores.Add(new Score
{
HIndex = po.Player.HIndex,
HoleNumber = holenum,
OutingID = outing.OutingID,
PlayerID = po.Player.PlayerID,
Strokes = 0,
Hics = 0
});
}
// Sort the player's scores by holenumber so they are in sequence in UI
po.Scores.Sort((x, y) => x.HoleNumber.CompareTo(y.HoleNumber));
}
// Calculate skins for each score
CalculateSkins(outing);
return(outing);
}
public static List <Outing> GetOutings(int season)
{
Dictionary <int, Outing> outDict = new Dictionary <int, Outing>();
const string sql = @"
select OutingID, o.CourseID, OutingDate, SkinValue, HicValue, LowNetValue, KPValue, c.Name, Slope, Rating, HoleID, HoleNumber, Par, Committed, Season, Settled, NoFlipSkins
from Outing o
inner join Course c on c.CourseID = o.CourseID
inner join Hole h on h.CourseID = c.CourseID
where o.season = @season
order by OutingDate desc
";
using (SqlConnection conn = DB.GetConnection(Settings.GetDSN()))
{
using (SqlDataReader rdr = DB.ExecSQLQuery(sql, conn, "@season", season.ToString()))
{
while (rdr.Read())
{
int outingID = rdr.GetInt32(0);
// If this outing not found in dictionary, create a new outing and add it
if (!outDict.ContainsKey(outingID))
{
outDict[outingID] = new Outing
{
OutingID = outingID,
CourseID = rdr.GetInt32(1),
OutingDate = rdr.GetDateTime(2),
SkinValue = rdr.GetDecimal(3),
HicValue = rdr.GetDecimal(4),
LowNetValue = rdr.GetDecimal(5),
KPValue = rdr.GetDecimal(6),
Committed = rdr.GetStringOrDefault(13) == "1",
Season = rdr.GetInt32(14),
Settled = rdr.GetStringOrDefault(15) == "1",
NoFlipSkins = rdr.GetStringOrDefault(16) == "1",
Course = new Course
{
CourseID = rdr.GetInt32(1),
Name = rdr.GetString(7),
Slope = rdr.GetInt32(8),
Rating = rdr.GetDecimal(9)
}
};
}
Outing outing = outDict[outingID];
// Get hole info from all records
outing.Course.HoleList.Add(new Hole
{
HoleID = rdr.GetInt32(10),
HoleNumber = rdr.GetInt32(11),
Par = rdr.GetInt32(12),
CourseID = outing.CourseID
});
}
}
return(outDict.Values.ToList());
}
}