Ejemplo n.º 1
0
        public int InsertUserTeamChip(UserTeamChips userTeamChips, SqlConnection db)
        {
            int rowsAffected = 0;

            try
            {
                using (IDataReader reader = userTeamChips.GetDataReader())
                {
                    using (var bulkCopy = new SqlBulkCopy(db))
                    {
                        bulkCopy.BulkCopyTimeout      = 0;
                        bulkCopy.BatchSize            = 500;
                        bulkCopy.DestinationTableName = "UserTeamChipStaging";
                        bulkCopy.EnableStreaming      = true;

                        // Add your column mappings here
                        //bulkCopy.ColumnMappings.Add("name", "chip_name");
                        bulkCopy.ColumnMappings.Add("time", "chip_time");
                        bulkCopy.ColumnMappings.Add("userteamid", "userteamid");
                        bulkCopy.ColumnMappings.Add("event", "gameweekid");
                        bulkCopy.ColumnMappings.Add("chip", "chipid");

                        bulkCopy.WriteToServer(reader);
                        rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
                    }
                }
                return(rowsAffected);
            }
            catch (Exception ex)
            {
                Logger.Error("UserTeamChip Repository (insert) error: " + ex.Message);
                return(rowsAffected);
                //throw ex;
            }
        }
        public static void GetUserTeamChipJson(int userTeamId, UserTeamChips userTeamChipsInsert, UserTeamGameweekHistoryData userTeamHistoryData, SqlConnection db)
        {
            try
            {
                //Logger.Out("GetUserTeamChipJson: Gameweek " + Convert.ToString(gameweekId) + " - starting");

                //Load UserTeamChip data
                UserTeamChipRepository userTeamChipRepository = new UserTeamChipRepository();

                List <UserTeamChipId> userTeamChipIds = new List <UserTeamChipId>();

                //List<UserTeamChipId> userTeamChipIds = userTeamChipRepository.GetAllUserTeamChipIdsForUserTeamId(userTeamId, db);
                userTeamChipIds = userTeamChipRepository.GetAllUserTeamChipIdsForUserTeamId(userTeamId, db);

                foreach (UserTeamChip userTeamChip in userTeamHistoryData.chips)
                {
                    userTeamChip.userteamid = userTeamId;
                    userTeamChip.chip       = userTeamChipRepository.GetChipIdFromName(userTeamChip.name, db);

                    UserTeamChipId userTeamChipId = new UserTeamChipId
                                                    (
                        userTeamId,
                        userTeamChip.@event,
                        userTeamChip.chip
                                                    );

                    //UserTeamChipIds = userTeamChipRepository.GetAllUserTeamPickIdsForUserTeamIdAndGameweekIdAndChipId(userTeamChipId);

                    //needed if want to assign value from parent to add into db table
                    //userTeamChip.entry = userTeamId;

                    userTeamChipId.gameweekid = userTeamChip.@event;
                    userTeamChipId.chipid     = userTeamChip.chip;

                    if (!userTeamChipIds.Contains(userTeamChipId) && !userTeamChipsInsert.Contains(userTeamChip))
                    {
                        userTeamChipsInsert.Add(userTeamChip);
                    }
                    //else
                    //{
                    //    userTeamChipRepository.UpdateUserTeamChip(userTeamChip);
                    //}
                }
                //Logger.Out("GetUserTeamChipJson: Gameweek " + Convert.ToString(gameweekId) + " - completed");
            }
            catch (Exception ex)
            {
                Logger.Error("GetUserTeamChipJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
                throw new Exception("GetUserTeamChipJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
                //GetUserTeamChipJson(userTeamId, userTeamChipsInsert, userTeamHistoryData);
            }
        }
 private static int WriteUserTeamChipToDB(UserTeamChips userTeamChipInsert, SqlConnection db)
 {
     try
     {
         UserTeamChipRepository userTeamChipRepository = new UserTeamChipRepository();
         int userTeamChipRowsInserted = userTeamChipRepository.InsertUserTeamChip(userTeamChipInsert, db);
         return(userTeamChipRowsInserted);
     }
     catch (Exception ex)
     {
         Logger.Error("Program WriteUserTeamChipToDB error: " + ex.Message);
         return(0);
     }
 }
        public static void WriteUserTeamChipToDB(int pageId, UserTeamChips userTeamChipsInsert, SqlConnection db)
        {
            try
            {
                UserTeamChipRepository userTeamChipRepository = new UserTeamChipRepository();

                //Write UserTeamChip to the db
                Globals.UserTeamChipRowsInserted = userTeamChipRepository.InsertUserTeamChip(userTeamChipsInsert, db);
                Logger.Out("UserTeamChip bulk insert complete (PageId: " + Convert.ToString(pageId) + ")");

                userTeamChipsInsert.Clear();
            }
            catch (Exception ex)
            {
                Logger.Error("Program WriteUserTeamChipToDB error: " + ex.Message);
            }
        }
        public static void GetUserTeamDataJsonLoop(int startingUserTeamId, int userTeamId, List <int> toDoUserTeamIds, List <int> existingUserTeamIds, List <int> userTeamIdsWithSeasons, string userTeamUrl, UserTeams userTeamInsert, UserTeamGameweekHistories userTeamGameweekHistoriesInsert, UserTeamChips userTeamChipsInsert, UserTeamSeasons userTeamSeasonsInsert, UserTeamClassicLeagues userTeamClassicLeaguesInsert, UserTeamH2hLeagues userTeamH2hLeaguesInsert, int userTeamRetries, SqlConnection db)
        {
            //int userTeamRowsProcessed = 0;

            try
            {
                if (db.State == ConnectionState.Closed)
                {
                    db.ConnectionString = ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam202021"].ConnectionString;
                    db.Open();
                }

                //userTeamSeasonNames = userTeamSeasonRepository.GetAllUserTeamSeasonNamesForUserTeamId(userTeamId, db);

                //if (userTeamSeasonNames.Count == 0)
                //{
                // Get the fantasyPremierLeaguePl1ayerData using JSON.NET
                //FantasyPremierLeagueAPIClient.GetUserTeamDataJson(userTeamId, existingUserTeamIds, userTeamIdsWithSeasons, userTeamUrl, userTeamInsert, userTeamGameweekHistoriesInsert, userTeamChipsInsert, userTeamSeasonsInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamRetries, userTeamRowsAdded, db);

                //Process UserTeam, UserTeamClassicLeague, UserTeamH2hLeague, UserTeamCup
                FantasyPremierLeagueAPIUserTeam.GetUserTeamData(userTeamId, existingUserTeamIds, userTeamUrl, userTeamInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, db);

                //Process UserTeamSeasonHistory
                if (!userTeamIdsWithSeasons.Contains(userTeamId))
                {
                    FantasyPremierLeagueAPIGameweekHistory.GetUserTeamHistoryDataJson(userTeamId, userTeamIdsWithSeasons, userTeamGameweekHistoriesInsert, userTeamChipsInsert, userTeamSeasonsInsert, db);
                }
                //}
            }
            catch (Exception ex)
            {
                Logger.Error("GetUserTeamDataJsonLoop data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
            }
        }
        static void Main(string[] args)
        {
            XmlConfigurator.Configure();

            //string LogName = GetType().Assembly.GetName().Name + ".log";
            //log4net.GlobalContext.Properties["LogName"] = LogName;

            UserTeamRepository              userTeamRepository              = new UserTeamRepository();
            UserTeamSeasonRepository        userTeamSeasonRepository        = new UserTeamSeasonRepository();
            UserTeamClassicLeagueRepository userTeamClassicLeagueRepository = new UserTeamClassicLeagueRepository();

            UserTeams                 userTeamInsert                  = new UserTeams();
            UserTeamSeasons           userTeamSeasonsInsert           = new UserTeamSeasons();
            UserTeamGameweekHistories userTeamGameweekHistoriesInsert = new UserTeamGameweekHistories();
            UserTeamChips             userTeamChipsInsert             = new UserTeamChips();
            UserTeamClassicLeagues    userTeamClassicLeaguesInsert    = new UserTeamClassicLeagues();
            UserTeamH2hLeagues        userTeamH2hLeaguesInsert        = new UserTeamH2hLeagues();

            SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam202021"].ConnectionString);

            try
            {
                Logger.Out("Starting...");
                Logger.Out("");

                int userTeamRetries = 0;

                int  startingUserTeamId = 1;
                bool test = true;

                if (args.Length == 0)
                {
                    System.Console.WriteLine("No arguments passed into program");
                }
                else
                {
                    test = int.TryParse(args[0], out startingUserTeamId);
                    if (test == false)
                    {
                        System.Console.WriteLine("Arguments passed into program were not integer");
                        return;
                    }
                }

                Logger.Out("Starting UserTeams data load");
                Logger.Out("");

                FantasyPremierLeagueAPI_Bootstrap.GetPlayerBootstrapDataJson();

                string userTeamUrl = ConfigSettings.ReadSetting("userTeamURL");
                //string userTeamLeaguesUrl = ConfigSettings.ReadSetting("userTeamLeaguesURL");

                using (db)
                {
                    db.Open();

                    SetLatestGameweek("UserTeamGameweekHistory");
                    SetActualGameweek("UserTeamGameweekPick");

                    //List<string> userTeamSeasonNames;

                    Logger.Out("Starting GetAllUserTeamIds call");
                    List <int> existingUserTeamIds = userTeamRepository.GetAllUserTeamIds(startingUserTeamId, db);
                    Logger.Out("Starting GetAllUserTeamIdsWithLeagues call");
                    List <int> userTeamIdsWithLeagues = userTeamClassicLeagueRepository.GetAllUserTeamIdsWithLeagues(startingUserTeamId, db);
                    Logger.Out("Starting GetAllUserTeamIdsWithSeasons call");
                    List <int> userTeamIdsWithSeasons = userTeamSeasonRepository.GetAllUserTeamIdsWithSeasons(startingUserTeamId, db);

                    int userTeamsToProcess = Globals.bootstrapUserTeamCount - startingUserTeamId;

                    if (userTeamsToProcess < 0)
                    {
                        userTeamsToProcess = 0;
                    }

                    List <int> userTeamIds = Enumerable.Range(startingUserTeamId, userTeamsToProcess).ToList();

                    //List<int> toDoUserTeamIds = userTeamIds.Except(userTeamIdsWithLeagues).ToList();
                    List <int> toDoUserTeamIds = userTeamIds.Except(existingUserTeamIds).ToList();

                    Logger.Out("");
                    Logger.Out("Starting UserTeamId" + startingUserTeamId.ToString());
                    Logger.Out("");

                    Globals.apiCalls                = 0;
                    Globals.apiUserTeamCalls        = 0;
                    Globals.apiUserTeamHistoryCalls = 0;
                    Globals.userTeamInsertCount     = 0;

                    FantasyPremierLeagueAPIClient.GetUserTeamDataJson(startingUserTeamId, toDoUserTeamIds, existingUserTeamIds, userTeamIdsWithSeasons, userTeamUrl, userTeamInsert, userTeamGameweekHistoriesInsert, userTeamChipsInsert, userTeamSeasonsInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamRetries, db);
                    db.Close();
                }

                Logger.Out("UserTeams data load complete");
                Logger.Out("");

                Logger.Out("Finished!!!");

                //// Wait for user input - keep the program running
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Logger.Error("Program error: " + ex.Message);
                throw ex;
            }
        }
Ejemplo n.º 7
0
        public static bool GetLeagueDataJson(int leagueId, List <int> userTeamIds, string userTeamLeaguesUrl, string userTeamUrl, UserTeams userTeamsUpdateInsert, UserTeamCupMatches userTeamCupInsert, UserTeamClassicLeagues userTeamClassicLeaguesInsert, UserTeamH2hLeagues userTeamH2hLeaguesInsert, UserTeamGameweekHistories userTeamGameweekHistoriesInsert, UserTeamPicks userTeamPicksInsert, UserTeamPickAutomaticSubs userTeamPickAutomaticSubsInsert, UserTeamChips userTeamChipsInsert, UserTeamTransferHistoryData userTeamTransferHistoriesInsert, UserTeamSeasons userTeamSeasonsInsert, SqlConnection db)
        {
            UserTeamRepository                userTeamRepository                = new UserTeamRepository();
            UserTeamPickRepository            userTeamPickRepository            = new UserTeamPickRepository();
            UserTeamGameweekHistoryRepository userTeamGameweekHistoryRepository = new UserTeamGameweekHistoryRepository();
            UserTeamTransferHistoryRepository userTeamTransferHistoryRepository = new UserTeamTransferHistoryRepository();
            UserTeamClassicLeagueRepository   userTeamClassicLeagueRepository   = new UserTeamClassicLeagueRepository();
            UserTeamSeasonRepository          userTeamSeasonRepository          = new UserTeamSeasonRepository();

            try
            {
                int        userTeamId        = 0;
                bool       has_next          = false;
                List <int> leagueUserTeamIds = new List <int>();

                string url = string.Format(userTeamLeaguesUrl, leagueId, Globals.PageId);

                JsonSerializer serializer = new JsonSerializer()
                {
                    Formatting = Formatting.None
                };

                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

                using (HttpClient client = new HttpClient())
                    using (Stream s = client.GetStreamAsync(url).Result)
                        using (StreamReader sr = new StreamReader(s))
                            using (JsonReader reader = new JsonTextReader(sr))
                            {
                                Globals.ApiCalls     += 1;
                                Globals.ApiPageCalls += 1;

                                // read the json from a stream
                                // json size doesn't matter because only a small piece is read at a time from the HTTP request
                                var leagueStandingsData = serializer.Deserialize <LeagueStandingsData>(reader);

                                if (leagueStandingsData != null)
                                {
                                    League    league    = leagueStandingsData.league;
                                    Standings standings = leagueStandingsData.standings;

                                    string leagueName = league.name;
                                    has_next = standings.has_next;

                                    Logger.Out(leagueName + " (" + Convert.ToString(leagueId) + "): Page " + Convert.ToString(Globals.PageId));
                                    Logger.Out("");

                                    if (db.State == ConnectionState.Closed)
                                    {
                                        db.ConnectionString = ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam"].ConnectionString;
                                        db.Open();
                                        Logger.Error("GetLeagueDataJson Info (LeagueId: " + leagueId.ToString() + ", PageId:" + Globals.PageId.ToString() + "): Reopening closed db connection");
                                    }

                                    List <int> pageUserTeamIds = new List <int>();

                                    foreach (TeamLeaguePosition teamLeaguePosition in standings.results)
                                    {
                                        userTeamId = teamLeaguePosition.entry;

                                        //Add each UserTeamId to a list for checking if GameweekHistory, TransferHistory, and Picks have already been processed for that GW by UserTeamId
                                        pageUserTeamIds.Add(userTeamId);
                                    }

                                    using (DataTable dtUserMaxGWForUserTeamForGameweekHistory = userTeamGameweekHistoryRepository.GetMaxGameweekIdFromUserTeamGameweekHistoryForUserTeamIds(pageUserTeamIds, db))
                                        using (DataTable dtUserMaxGWForUserTeamForPicks = userTeamPickRepository.GetMaxGameweekIdFromUserTeamPickForUserTeamIds(pageUserTeamIds, db))
                                            using (DataTable dtUserMaxGWForUserTeamForTransferHistory = userTeamTransferHistoryRepository.GetMaxGameweekIdFromUserTeamTransferHistoryForUserTeamIds(pageUserTeamIds, db))
                                                using (DataTable dtClassicLeagueCountForUserTeam = userTeamClassicLeagueRepository.GetClassicLeagueCountFromUserTeamClassicLeagueForUserTeamIds(pageUserTeamIds, db))
                                                    using (DataTable dtSeasonCountForUserTeam = userTeamSeasonRepository.GetSeasonCountFromUserTeamSeasonForUserTeamIds(pageUserTeamIds, db))
                                                        using (DataTable dtUserTeam = userTeamRepository.GetUserTeamForUserTeamIds(pageUserTeamIds, db))
                                                        {
                                                            foreach (TeamLeaguePosition teamLeaguePosition in standings.results)
                                                            {
                                                                userTeamId = teamLeaguePosition.entry;

                                                                Globals.MaxGWFromGameweekHistoryForUserTeamId             = GetColumnNameValue(userTeamId, dtUserMaxGWForUserTeamForGameweekHistory, "gameweekid");
                                                                Globals.MaxGWFromPicksForUserTeamId                       = GetColumnNameValue(userTeamId, dtUserMaxGWForUserTeamForPicks, "gameweekid");
                                                                Globals.MaxGWFromTransferHistoryForUserTeamId             = GetColumnNameValue(userTeamId, dtUserMaxGWForUserTeamForTransferHistory, "gameweekid");
                                                                Globals.LeagueCountFromUserTeamClassicLeagueForUserTeamId = GetColumnNameValue(userTeamId, dtClassicLeagueCountForUserTeam, "leagueCount");
                                                                Globals.SeasonCountFromUserTeamSeasonForUserTeamId        = GetColumnNameValue(userTeamId, dtSeasonCountForUserTeam, "seasonCount");
                                                                Globals.ExistingUserTeamId = GetColumnNameValue(userTeamId, dtUserTeam, "userteamid");

                                                                FantasyPremierLeagueAPIClient.GetUserTeamDataJson(userTeamId, userTeamIds, userTeamUrl, userTeamsUpdateInsert, userTeamCupInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamGameweekHistoriesInsert, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, userTeamChipsInsert, userTeamTransferHistoriesInsert, userTeamSeasonsInsert, db);
                                                            }
                                                        }
                                    pageUserTeamIds.Clear();
                                }
                                return(has_next);
                            }
            }
            catch (Exception ex)
            {
                Logger.Error("GetLeagueDataJson data exception (LeagueId: " + leagueId.ToString() + ", PageId:" + Globals.PageId.ToString() + "): " + ex.Message);

                bool has_next = true;

                if (Globals.LeagueRetries < 10)
                {
                    has_next = GetLeagueDataJson(leagueId, userTeamIds, userTeamLeaguesUrl, userTeamUrl, userTeamsUpdateInsert, userTeamCupInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamGameweekHistoriesInsert, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, userTeamChipsInsert, userTeamTransferHistoriesInsert, userTeamSeasonsInsert, db);
                    Globals.LeagueRetries += 1;
                }
                else
                {
                    Logger.Error("GetLeagueDataJson data exception (LeagueId: " + leagueId.ToString() + "):  League/Page doesn't exist skipping to next!!!");
                    //Program.WriteToDB(pageId, userTeamGameweekHistoriesInsert, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, userTeamChipsInsert, userTeamTransferHistoriesInsert, userTeamSeasonsInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, db);
                    Globals.LeagueRetries = 0;
                    //throw new Exception("GetLeagueDataJson data exception (LeagueId: " + leagueId.ToString() + ", PageId:" + Globals.PageId.ToString() + "): " + ex.Message);
                }

                return(has_next);
            }
        }
        public static void WriteToDB(int pageId, UserTeams userTeamsUpdateInsert, UserTeamCupMatches userTeamCupInsert, UserTeamClassicLeagues userTeamClassicLeaguesInsert, UserTeamH2hLeagues userTeamH2hLeaguesInsert, UserTeamGameweekHistories userTeamGameweekHistoriesInsert, UserTeamPicks userTeamPicksInsert, UserTeamPickAutomaticSubs userTeamPickAutomaticSubsInsert, UserTeamChips userTeamChipsInsert, UserTeamTransferHistoryData userTeamTransferHistoriesInsert, UserTeamSeasons userTeamSeasonsInsert, SqlConnection db)
        {
            try
            {
                Globals.UserTeamUpdateRowsInserted            = 0;
                Globals.UserTeamPickRowsInserted              = 0;
                Globals.UserTeamPickAutomaticSubsRowsInserted = 0;
                Globals.UserTeamGameweekHistoryRowsInserted   = 0;
                Globals.UserTeamChipRowsInserted              = 0;
                Globals.UserTeamTransferHistoryRowsInserted   = 0;
                Globals.UserTeamSeasonRowsInserted            = 0;
                Globals.UserTeamClassicLeagueRowsInserted     = 0;
                Globals.UserTeamH2hLeagueRowsInserted         = 0;
                Globals.UserTeamCupRowsInserted = 0;

                Logger.Out("");

                WriteUserTeamToDB(Globals.PageId, userTeamsUpdateInsert, db);
                WriteUserTeamChipToDB(Globals.PageId, userTeamChipsInsert, db);
                WriteUserTeamGameweekHistoryToDB(Globals.PageId, userTeamGameweekHistoriesInsert, db);
                WriteUserTeamPicksToDB(Globals.PageId, userTeamPicksInsert, db);
                WriteUserTeamPickAutomaticSubsToDB(Globals.PageId, userTeamPickAutomaticSubsInsert, db);
                WriteUserTeamTransferHistoryToDB(Globals.PageId, userTeamTransferHistoriesInsert, db);
                WriteUserTeamSeasonToDB(Globals.PageId, userTeamSeasonsInsert, db);
                WriteUserTeamClassicLeagueToDB(Globals.PageId, userTeamClassicLeaguesInsert, db);
                WriteUserTeamH2hLeagueToDB(Globals.PageId, userTeamH2hLeaguesInsert, db);
                WriteUserTeamCupToDB(Globals.PageId, userTeamCupInsert, db);

                Logger.Out("");
                Logger.Out("UserTeam: " + Convert.ToString(Globals.UserTeamInsertCount) + " rows inserted");
                Logger.Out("UserTeamCup: " + Convert.ToString(Globals.UserTeamCupRowsInserted) + " rows inserted");
                Logger.Out("UserTeamUpdates: " + Convert.ToString(Globals.UserTeamUpdateRowsInserted) + " rows inserted");
                Logger.Out("UserTeamSeason: " + Convert.ToString(Globals.UserTeamSeasonRowsInserted) + " rows inserted");
                Logger.Out("UserTeamClassicLeague: " + Convert.ToString(Globals.UserTeamClassicLeagueRowsInserted) + " rows inserted");
                Logger.Out("UserTeamH2hLeague: " + Convert.ToString(Globals.UserTeamH2hLeagueRowsInserted) + " rows inserted");
                Logger.Out("UserTeamGameweekHistory: " + Convert.ToString(Globals.UserTeamGameweekHistoryRowsInserted) + " rows inserted");
                Logger.Out("UserTeamTransferHistory: " + Convert.ToString(Globals.UserTeamTransferHistoryRowsInserted) + " rows inserted");
                Logger.Out("UserTeamChip: " + Convert.ToString(Globals.UserTeamChipRowsInserted) + " rows inserted");
                Logger.Out("UserTeamPick: " + Convert.ToString(Globals.UserTeamPickRowsInserted) + " rows inserted");
                Logger.Out("UserTeamPickAutomaticSub: " + Convert.ToString(Globals.UserTeamPickAutomaticSubsRowsInserted) + " rows inserted");
                Logger.Out("");
                Logger.Out("API Page calls: " + Convert.ToString(Globals.ApiPageCalls));
                Logger.Out("API UserTeam calls: " + Convert.ToString(Globals.ApiUserTeamCalls));
                Logger.Out("API UserTeamHistory calls: " + Convert.ToString(Globals.ApiUserTeamHistoryCalls));
                Logger.Out("API UserTeamTransferHistory calls: " + Convert.ToString(Globals.ApiUserTeamTransferHistoryCalls));
                Logger.Out("API UserTeamPick calls: " + Convert.ToString(Globals.ApiUserTeamPickCalls));
                Logger.Out("API calls: " + Convert.ToString(Globals.ApiCalls));
                Logger.Out("");
            }
            catch (Exception ex)
            {
                Logger.Error("Program WriteToDB error: " + ex.Message);
                //WriteToDB(userTeamRowsInserted, userTeamSeasonRowsInserted, userTeamInsert, userTeamSeasonsInsert, db);
            }
        }
        static void Main(string[] args)
        {
            XmlConfigurator.Configure();

            //int pageId;
            int pageCnt;
            int leagueId = 314;

            UserTeams                   userTeamsUpdateInsert           = new UserTeams();
            UserTeamPicks               userTeamPicksInsert             = new UserTeamPicks();
            UserTeamPickAutomaticSubs   userTeamPickAutomaticSubsInsert = new UserTeamPickAutomaticSubs();
            UserTeamGameweekHistories   userTeamGameweekHistoriesInsert = new UserTeamGameweekHistories();
            UserTeamChips               userTeamChipsInsert             = new UserTeamChips();
            UserTeamTransferHistoryData userTeamTransferHistoriesInsert = new UserTeamTransferHistoryData();
            UserTeamSeasons             userTeamSeasonsInsert           = new UserTeamSeasons();
            UserTeamClassicLeagues      userTeamClassicLeaguesInsert    = new UserTeamClassicLeagues();
            UserTeamH2hLeagues          userTeamH2hLeaguesInsert        = new UserTeamH2hLeagues();

            UserTeamCupMatches userTeamCupInsert = new UserTeamCupMatches();

            UserTeamRepository userTeamRepository = new UserTeamRepository();

            SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam"].ConnectionString);

            try
            {
                Logger.Out("Starting...");
                Logger.Out("");

                int pageId;
                Globals.PageId = 1;

                int insertInterval;
                Globals.InsertInterval = 10;

                bool test = true;

                if (args.Length == 0)
                {
                    //System.Console.WriteLine("No arguments passed into program");
                    Logger.Out("No arguments passed into program");
                }
                else
                {
                    test = int.TryParse(args[0], out pageId);

                    if (test == false)
                    {
                        Logger.Out("Argument[0] passed into program doesnn't exist or was not integer");
                        return;
                    }
                    else
                    {
                        Globals.PageId = pageId;
                    }

                    test = int.TryParse(args[1], out insertInterval);

                    if (test == false)
                    {
                        //System.Console.WriteLine("Arguments passed into program were not integer");
                        Logger.Out("Argument[1] InsertInterval passed into program doesnn't exist or were not integer");
                        return;
                    }
                    else
                    {
                        Globals.InsertInterval = insertInterval;
                    }
                }

                Logger.Out("Starting UserTeams data load");
                Logger.Out("");

                string userTeamUrl        = ConfigSettings.ReadSetting("userTeamURL");
                string userTeamLeaguesUrl = ConfigSettings.ReadSetting("userTeamLeaguesURL");

                using (db)
                {
                    db.Open();

                    bool has_next = true;

                    Globals.UserTeamRetries = 0;
                    pageCnt = 0;

                    List <int> userTeamIds = userTeamRepository.GetAllUserTeamIds(db);

                    Globals.ApiCalls                        = 0;
                    Globals.ApiPageCalls                    = 0;
                    Globals.ApiUserTeamCalls                = 0;
                    Globals.ApiUserTeamHistoryCalls         = 0;
                    Globals.ApiUserTeamTransferHistoryCalls = 0;
                    Globals.ApiUserTeamPickCalls            = 0;
                    Globals.UserTeamInsertCount             = 0;

                    while (has_next == true)
                    {
                        SetLatestGameweek("UserTeamGameweekHistory");
                        SetActualGameweek("UserTeamGameweekPick");

                        pageCnt += 1;

                        if (db.State == ConnectionState.Closed)
                        {
                            db.ConnectionString = ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam"].ConnectionString;
                            db.Open();
                            Logger.Error("Program Info (LeagueId: " + leagueId.ToString() + ", PageId:" + Globals.PageId.ToString() + "): Reopening closed db connection");
                        }

                        has_next = FantasyPremierLeagueLeaguesAPIClient.GetLeagueDataJson(leagueId, userTeamIds, userTeamLeaguesUrl, userTeamUrl, userTeamsUpdateInsert, userTeamCupInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamGameweekHistoriesInsert, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, userTeamChipsInsert, userTeamTransferHistoriesInsert, userTeamSeasonsInsert, db);

                        if (pageCnt >= Globals.InsertInterval)
                        {
                            WriteToDB(Globals.PageId, userTeamsUpdateInsert, userTeamCupInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamGameweekHistoriesInsert, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, userTeamChipsInsert, userTeamTransferHistoriesInsert, userTeamSeasonsInsert, db);

                            Globals.ApiCalls                        = 0;
                            Globals.ApiPageCalls                    = 0;
                            Globals.ApiUserTeamCalls                = 0;
                            Globals.ApiUserTeamHistoryCalls         = 0;
                            Globals.ApiUserTeamTransferHistoryCalls = 0;
                            Globals.ApiUserTeamPickCalls            = 0;
                            Globals.UserTeamInsertCount             = 0;

                            pageCnt = 0;
                        }

                        Globals.PageId += 1;
                    }

                    WriteToDB(Globals.PageId, userTeamsUpdateInsert, userTeamCupInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamGameweekHistoriesInsert, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, userTeamChipsInsert, userTeamTransferHistoriesInsert, userTeamSeasonsInsert, db);

                    db.Close();
                }

                Logger.Out("UserTeams data load complete");
                Logger.Out("");

                Logger.Out("Finished!!!");

                //// Wait for user input - keep the program running
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Logger.Error(ex.Message);
                //Logger.Error(userTeamName + " caused error!!!");

                if (db.State == ConnectionState.Closed)
                {
                    db.ConnectionString = ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam"].ConnectionString;
                    db.Open();
                    Logger.Error("Program Exception Info (LeagueId: " + leagueId.ToString() + ", PageId:" + Globals.PageId.ToString() + "): Reopening closed db connection");
                }

                //If error is thrown from sub program write existing records to the DB
                WriteToDB(Globals.PageId, userTeamsUpdateInsert, userTeamCupInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamGameweekHistoriesInsert, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, userTeamChipsInsert, userTeamTransferHistoriesInsert, userTeamSeasonsInsert, db);

                //db.Close();

                //throw ex;
            }
        }
        public static void GetUserTeamHistoryDataJson(int userTeamId, List <int> userTeamIdsWithSeasons, UserTeamGameweekHistories userTeamGameweekHistoriesInsert, UserTeamChips userTeamChipsInsert, UserTeamSeasons userTeamSeasonsInsert, SqlConnection db)
        {
            try
            {
                string userTeamHistoryUrl = ConfigSettings.ReadSetting("userTeamHistoryURL");

                userTeamHistoryUrl = string.Format(userTeamHistoryUrl, userTeamId);

                HttpClient     client     = new HttpClient();
                JsonSerializer serializer = new JsonSerializer();
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
                using (Stream s = client.GetStreamAsync(userTeamHistoryUrl).Result)
                    using (StreamReader sr = new StreamReader(s))
                        using (JsonReader reader = new JsonTextReader(sr))
                        {
                            Globals.apiCalls += 1;
                            Globals.apiUserTeamHistoryCalls += 1;

                            // read the json from a stream
                            // json size doesn't matter because only a small piece is read at a time from the HTTP request
                            var userTeamHistoryData = serializer.Deserialize <UserTeamGameweekHistoryData>(reader);

                            //Process UserTeamSeasonHistory
                            if (userTeamHistoryData.past != null && !userTeamIdsWithSeasons.Contains(userTeamId))
                            {
                                GetUserTeamSeasonJson(userTeamId, userTeamHistoryData, userTeamSeasonsInsert, db);
                            }

                            //Process UserTeamGameweekHistory
                            if (userTeamHistoryData.current != null && !userTeamIdsWithSeasons.Contains(userTeamId))
                            {
                                GetUserTeamGameweekHistoryJson(userTeamId, userTeamHistoryData, userTeamGameweekHistoriesInsert, db);
                            }

                            //Process UserTeamChip
                            if (userTeamHistoryData.chips != null && !userTeamIdsWithSeasons.Contains(userTeamId))
                            {
                                GetUserTeamChipJson(userTeamId, userTeamChipsInsert, userTeamHistoryData, db);
                            }
                        }
            }
            catch (Exception ex)
            {
                Logger.Error("GetUserTeamHistoryDataJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
                throw new Exception("GetUserTeamHistoryDataJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
            }
        }
Ejemplo n.º 11
0
        public static void GetUserTeamDataJson(int userTeamId, List <int> userTeamIds, string userTeamUrl, UserTeams userTeamsUpdateInsert, UserTeamCupMatches userTeamCupInsert, UserTeamClassicLeagues userTeamClassicLeaguesInsert, UserTeamH2hLeagues userTeamH2hLeaguesInsert, UserTeamGameweekHistories userTeamGameweekHistoriesInsert, UserTeamPicks userTeamPicksInsert, UserTeamPickAutomaticSubs userTeamPickAutomaticSubsInsert, UserTeamChips userTeamChipsInsert, UserTeamTransferHistoryData userTeamTransferHistoriesInsert, UserTeamSeasons userTeamSeasonsInsert, SqlConnection db)
        {
            try
            {
                //Process UserTeam, UserTeamClassicLeague, UserTeamH2hLeague, UserTeamCup
                ////if (Globals.ExistingUserTeamId == 0 || Globals.LeagueCountFromUserTeamClassicLeagueForUserTeamId == 0)
                //{
                FantasyPremierLeagueAPIUserTeamLeagueAndCup.GetUserTeamLeagueAndCupJson(userTeamId, userTeamIds, userTeamUrl, userTeamsUpdateInsert, userTeamCupInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, db);
                //}
                //else
                //{
                //    Logger.Out("UserTeam: " + Convert.ToString(userTeamId) + " - skipped (already exists with leagues)");
                //}

                //Process UserTeamGameweekHistory, UserTeamChip, UserTeamSeasonHistory
                if (Globals.MaxGWFromGameweekHistoryForUserTeamId < Globals.LatestGameweek)
                {
                    FantasyPremierLeagueAPIGameweekHistory.GetUserTeamHistoryDataJson(userTeamId, userTeamGameweekHistoriesInsert, userTeamChipsInsert, userTeamSeasonsInsert, db);
                }

                //Process UserTeamPick, UserTeamPickAutomaticSub
                //Doesn't check UserTeamPickAutomaticSub - which will be missing if run during an active Gameweek - matches are in progress or before have been played)
                if (Globals.MaxGWFromPicksForUserTeamId < Globals.ActualGameweek && Globals.ActualGameweek > 0)
                {
                    FantasyPremierLeagueAPIPick.GetUserTeamPickDataJson(userTeamId, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, db);
                }

                //Process UserTeamTransferHistory
                if (Globals.MaxGWFromTransferHistoryForUserTeamId < Globals.ActualGameweek && Globals.ActualGameweek > 0)
                {
                    FantasyPremierLeagueAPITransferHistory.GetUserTeamTransferHistoryDataJson(userTeamId, userTeamTransferHistoriesInsert, db);
                }

                Logger.Out("");
            }
            catch (Exception ex)
            {
                Logger.Error("GetUserTeamDataJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
                //throw new Exception("GetUserTeamDataJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
                if (Globals.UserTeamRetries < 10)
                {
                    Globals.UserTeamRetries += 1;
                    GetUserTeamDataJson(userTeamId, userTeamIds, userTeamUrl, userTeamsUpdateInsert, userTeamCupInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamGameweekHistoriesInsert, userTeamPicksInsert, userTeamPickAutomaticSubsInsert, userTeamChipsInsert, userTeamTransferHistoriesInsert, userTeamSeasonsInsert, db);
                }
                else
                {
                    Logger.Error("GetUserTeamDataJson data exception (UserTeamId: " + userTeamId.ToString() + "): Issue processing User Team skipping to next!!!");
                    Thread.Sleep(20000);
                }
            }
        }
        public static void GetUserTeamDataJson(int startingUserTeamId, List <int> toDoUserTeamIds, List <int> existingUserTeamIds, List <int> userTeamIdsWithSeasons, string userTeamUrl, UserTeams userTeamInsert, UserTeamGameweekHistories userTeamGameweekHistoriesInsert, UserTeamChips userTeamChipsInsert, UserTeamSeasons userTeamSeasonsInsert, UserTeamClassicLeagues userTeamClassicLeaguesInsert, UserTeamH2hLeagues userTeamH2hLeaguesInsert, int userTeamRetries, SqlConnection db)
        {
            int userTeamId = 0;

            Globals.userTeamRowsProcessed = 0;

            try
            {
                foreach (int loopUserTeamId in toDoUserTeamIds)
                {
                    userTeamId = loopUserTeamId;

                    //url = string.Format(userTeamUrl, userTeamId);

                    FantasyPremierLeagueAPIClientLoop.GetUserTeamDataJsonLoop(startingUserTeamId, userTeamId, toDoUserTeamIds, existingUserTeamIds, userTeamIdsWithSeasons, userTeamUrl, userTeamInsert, userTeamGameweekHistoriesInsert, userTeamChipsInsert, userTeamSeasonsInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, userTeamRetries, db);

                    if (Globals.userTeamRowsProcessed >= 500)
                    {
                        WriteToDB(userTeamInsert, userTeamGameweekHistoriesInsert, userTeamChipsInsert, userTeamSeasonsInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, db);

                        Globals.userTeamRowsProcessed = 0;

                        Logger.Out(userTeamId.ToString());
                        Logger.Out("");
                    }
                    else
                    {
                        Globals.userTeamRowsProcessed += 1;
                    }

                    existingUserTeamIds.Add(userTeamId);
                    //Globals.processedUserTeamIds.Add(userTeamId);

                    //var itemToRemove = toDoUserTeamIds.SingleOrDefault(r => r == userTeamId);
                    //toDoUserTeamIds.Remove(itemToRemove);
                }

                WriteToDB(userTeamInsert, userTeamGameweekHistoriesInsert, userTeamChipsInsert, userTeamSeasonsInsert, userTeamClassicLeaguesInsert, userTeamH2hLeaguesInsert, db);
            }
            catch (Exception ex)
            {
                Logger.Error("GetUserTeamDataJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
                Globals.userTeamRowsProcessed += 1;
            }
        }
        public static void WriteToDB(UserTeams userTeamInsert, UserTeamGameweekHistories userTeamGameweekHistoriesInsert, UserTeamChips userTeamChipsInsert, UserTeamSeasons userTeamSeasonsInsert, UserTeamClassicLeagues userTeamClassicLeaguesInsert, UserTeamH2hLeagues userTeamH2hLeaguesInsert, SqlConnection db)
        {
            try
            {
                int userTeamRowsInserted = 0;
                int userTeamGameweekHistoryRowsInserted = 0;
                int userTeamChipRowsInserted            = 0;
                int userTeamSeasonRowsInserted          = 0;
                int userTeamClassicLeagueRowsInserted   = 0;
                int userTeamH2hLeagueRowsInserted       = 0;

                //Write UserTeam to the db
                userTeamRowsInserted = WriteUserTeamToDB(userTeamInsert, db);
                //userTeamRowsInserted = userTeamRepository.InsertUserTeam(userTeamInsert, db);

                //Write UserTeamChip to the db
                userTeamChipRowsInserted = WriteUserTeamChipToDB(userTeamChipsInsert, db);
                //userTeamChipRowsInserted = userTeamChipRepository.InsertUserTeamChip(userTeamChipsInsert, db);

                //Write UserTeamGameweekHistory to the db
                userTeamGameweekHistoryRowsInserted = WriteUserTeamGameweekHistoryToDB(userTeamGameweekHistoriesInsert, db);
                //userTeamGameweekHistoryRowsInserted = userTeamGameweekHistoryRepository.InsertUserTeamGameweekHistories(userTeamGameweekHistoriesInsert, db);

                //Write UserTeamSeason to the db
                userTeamSeasonRowsInserted = WriteUserTeamSeasonToDB(userTeamSeasonsInsert, db);
                //userTeamSeasonRowsInserted = userTeamSeasonRepository.InsertUserTeamSeason(userTeamSeasonsInsert, db);

                //Write UserTeamClassicLeague to the db
                userTeamClassicLeagueRowsInserted = WriteUserTeamClassicLeagueToDB(userTeamClassicLeaguesInsert, db);
                //userTeamClassicLeagueRowsInserted = userTeamClassicLeagueRepository.InsertUserTeamClassicLeague(userTeamClassicLeaguesInsert, db);

                //Write UserTeamH2hLeague to the db
                userTeamH2hLeagueRowsInserted = WriteUserTeamH2hLeagueToDB(userTeamH2hLeaguesInsert, db);
                //userTeamH2hLeagueRowsInserted = userTeamH2hLeagueRepository.InsertUserTeamH2hLeague(userTeamH2hLeaguesInsert, db);

                Logger.Out("");
                Logger.Out("UserTeam bulk insert complete (UserTeam rows inserted: " + Convert.ToString(userTeamRowsInserted) + ")");
                Logger.Out("UserTeamGameweekHistory bulk insert complete (UserTeamGameweekHistory rows inserted: " + Convert.ToString(userTeamGameweekHistoryRowsInserted) + ")");
                Logger.Out("UserTeamChip bulk insert complete (UserTeamChip rows inserted: " + Convert.ToString(userTeamChipRowsInserted) + ")");
                Logger.Out("UserTeamSeason bulk insert complete (UserTeamSeason rows inserted: " + Convert.ToString(userTeamSeasonRowsInserted) + ")");
                Logger.Out("UserTeamClassicLeague bulk insert complete (UserTeamClassicLeague rows inserted: " + Convert.ToString(userTeamClassicLeagueRowsInserted) + ")");
                Logger.Out("UserTeamH2hLeague bulk insert complete (UserTeamH2hLeague rows inserted: " + Convert.ToString(userTeamH2hLeagueRowsInserted) + ")");
                Logger.Out("");

                Logger.Out("API UserTeam calls: " + Convert.ToString(Globals.apiUserTeamCalls));
                Logger.Out("API UserTeamHistory calls: " + Convert.ToString(Globals.apiUserTeamHistoryCalls));
                Logger.Out("API calls: " + Convert.ToString(Globals.apiCalls));
                Logger.Out("");

                userTeamInsert.Clear();
                userTeamGameweekHistoriesInsert.Clear();
                userTeamChipsInsert.Clear();
                userTeamSeasonsInsert.Clear();
                userTeamClassicLeaguesInsert.Clear();
                userTeamH2hLeaguesInsert.Clear();

                Globals.apiCalls                = 0;
                Globals.apiUserTeamCalls        = 0;
                Globals.apiUserTeamHistoryCalls = 0;
                Globals.userTeamInsertCount     = 0;
            }
            catch (Exception ex)
            {
                Logger.Error("Program WriteToDB error: " + ex.Message);
                //WriteToDB(userTeamRowsInserted, userTeamSeasonRowsInserted, userTeamInsert, userTeamSeasonsInsert, db);
            }
        }
        public static void GetUserTeamHistoryDataJson(int userTeamId, UserTeamGameweekHistories userTeamGameweekHistoriesInsert, UserTeamChips userTeamChipsInsert, UserTeamSeasons userTeamSeasonsInsert, SqlConnection db)
        {
            try
            {
                string userTeamHistoryUrl = ConfigSettings.ReadSetting("userTeamHistoryURL");

                userTeamHistoryUrl = string.Format(userTeamHistoryUrl, userTeamId);

                JsonSerializer serializer = new JsonSerializer()
                {
                    Formatting = Formatting.None
                };

                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

                using (HttpClient client = new HttpClient())
                    using (Stream s = client.GetStreamAsync(userTeamHistoryUrl).Result)
                        using (StreamReader sr = new StreamReader(s))
                            using (JsonReader reader = new JsonTextReader(sr))
                            {
                                Globals.ApiCalls += 1;
                                Globals.ApiUserTeamHistoryCalls += 1;

                                // read the json from a stream
                                // json size doesn't matter because only a small piece is read at a time from the HTTP request
                                var userTeamHistoryData = serializer.Deserialize <UserTeamGameweekHistoryData>(reader);

                                if (userTeamHistoryData != null)
                                {
                                    //Process UserTeamChip
                                    if (userTeamHistoryData.chips != null)
                                    {
                                        GetUserTeamChipJson(userTeamId, userTeamChipsInsert, userTeamHistoryData, db);
                                    }

                                    //Process UserTeamSeasonHistory
                                    if (userTeamHistoryData.past != null)
                                    {
                                        GetUserTeamSeasonJson(userTeamId, userTeamHistoryData, userTeamSeasonsInsert, db);
                                    }

                                    //Process UserTeamGameweekHistory
                                    if (Globals.MaxGWFromGameweekHistoryForUserTeamId < Globals.LatestGameweek && Globals.LatestGameweek > 0)
                                    {
                                        GetUserTeamGameweekHistoryJson(userTeamId, userTeamHistoryData, userTeamGameweekHistoriesInsert, db);
                                    }
                                }
                                else
                                {
                                    Logger.Out("UserTeamGameweekHistory: " + Convert.ToString(userTeamId) + " - skipped - null userTeamHistoryData (" + userTeamHistoryUrl + ")");
                                }
                            }
            }
            catch (Exception ex)
            {
                Logger.Error("GetUserTeamHistoryDataJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
                //throw new Exception("GetUserTeamHistoryDataJson data exception (UserTeamId: " + userTeamId.ToString() + "): " + ex.Message);
            }
        }