Ejemplo n.º 1
0
        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());
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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());
            }
        }