private List <string> TestExecuteDataMigrationScripts(string[] scriptsDescriptions, string expectedResult, bool skipScriptsWithWrongOrder = false) { using (var container = new RhetosTestContainer()) { var log = new List <string>(); container.AddLogMonitor(log); var sqlExecuter = container.Resolve <ISqlExecuter>(); sqlExecuter.ExecuteSql("DELETE FROM Rhetos.DataMigrationScript"); var sqlBatches = container.Resolve <SqlTransactionBatches>(); foreach (string scriptsDescription in scriptsDescriptions) { var scriptsProvider = new SimpleScriptsProvider(scriptsDescription); var configuration = new MockConfiguration { { "DataMigration.SkipScriptsWithWrongOrder", skipScriptsWithWrongOrder } }; var dataMigration = new DataMigration(sqlExecuter, container.Resolve <ILogProvider>(), scriptsProvider, configuration, sqlBatches); dataMigration.ExecuteDataMigrationScripts(); } var report = new List <string>(); sqlExecuter.ExecuteReader("SELECT Path, Active FROM Rhetos.DataMigrationScript ORDER BY OrderExecuted", reader => report.Add(reader.GetString(0).Replace(".sql", "") + (reader.GetBoolean(1) ? "" : "-"))); Assert.AreEqual(expectedResult, string.Join(", ", report)); return(log); } }
/// <summary> /// copies the key map table from the dest connection to the source /// so you can do inspect and troubleshoot the mapping in the source connection /// </summary> public async Task <(DbObject @object, int rowCount)> ImportKeyMapTableAsync(DataMigration dataMigration) { DbObject result = null; int rowCount = 0; await ExecuteWithConnectionsAsync(dataMigration, async (source, dest) => { var migrator = await GetMigratorAsync(dest); result = SqlMigrator <int> .KeyMapTable; if (await source.TableExistsAsync(result)) { await source.ExecuteAsync($"DROP TABLE [{result.Schema}].[{result.Name}]"); } if (!await source.SchemaExistsAsync(result.Schema)) { await source.ExecuteAsync($"CREATE SCHEMA [{result.Schema}]"); } string query = $"SELECT * FROM [{result.Schema}].[{result.Name}]"; var data = await dest.QueryTableAsync(query); rowCount = data.Rows.Count; var createTable = await dest.SqlCreateTableAsync(result.Schema, result.Name, query); await source.ExecuteAsync(createTable); await BulkInsert.ExecuteAsync(data, source, result, 35, new BulkInsertOptions() { IdentityInsert = true }); }); return(result, rowCount); }
private string GetDataMigrationInsert(DataMigration dm) { return(new FluentMySqlInsert("dataMigration") .WithColumns("id", "yearId", "notes", "dataMigrationDate") .WithValues(dm.Id, dm.Year.Id, dm.Notes, dm.DataMigrationDate) .ToString()); }
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IWebHostEnvironment env) { DataMigration.Initialize(app.ApplicationServices); if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } app.UseRouting(); app.UseAuthentication(); app.UseAuthorization(); app.UseCors("CorsPolicy"); app.UseSwagger(); app.UseSwaggerUI(c => { c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1"); c.RoutePrefix = string.Empty; }); app.UseEndpoints(endpoints => { endpoints.MapControllers(); }); }
private async Task <bool> CommitMigrationAsync(DataMigration migration) { var success = true; using (var context = _dbContext) { foreach (var statement in migration.Statements) { try { await context.ExecuteScalarAsync <int>( System.Data.CommandType.Text, statement); } catch (Exception ex) { if (_errors == null) { _errors = new List <Exception>(); } ; _errors.Add(ex); success = false; } } } return(success); }
public static async Task Main(String[] args) { var host = CreateHostBuilder(args).Build(); DataMigration.Setup(host.Services.CreateScope()); await host.RunAsync(); }
/// <summary> /// Run all defined data migrations that have not been run yet /// </summary> /// <param name="db">current database connection</param> /// <returns>Count of migrations run</returns> public static int RunMigrations(IDbConnection db) { var migrationsRun = 0; var migrationTypes = GetMigrationTypes(); var alreadyExecutedMigrations = db.Query <DataMigration>("select * from [{0}]".Fmt(db.GetTableName <DataMigration>())).ToList(); if (!alreadyExecutedMigrations.Any(x => x.Name == typeof(InitializeDbMigration).Name)) { // Create all data, run data init migration var schemaBuilder = new SchemaBuilder(db); schemaBuilder.GenerateSchema(false); var dataInitMigration = new InitializeDbMigration(); dataInitMigration.Execute(db); // Populate all migrations as run var migrations = migrationTypes.Select(x => new DataMigration { Name = x.Name }); return(db.BulkInsert(migrations)); } var scriptedObjectsToRecreate = new List <Type>(); foreach (var migration in migrationTypes.Where(t => !alreadyExecutedMigrations.Any(m => m.Name == t.Name)).Select(x => (IMigration)Activator.CreateInstance(x)).OrderBy(x => x.Order)) { // Collect the scripted objects to recreate after migrations are run if (migration.ScriptedObjectsToRecreate != null && migration.ScriptedObjectsToRecreate.Any()) { foreach (var viewType in migration.ScriptedObjectsToRecreate.Where(viewType => !scriptedObjectsToRecreate.Any(v => v == viewType))) { scriptedObjectsToRecreate.Add(viewType); } } migration.Execute(db); // add migration to database var migrationData = new DataMigration { CreatedOn = DateTime.UtcNow, Name = migration.GetType().Name }; db.Save(migrationData); migrationsRun++; } if (scriptedObjectsToRecreate.Any()) { foreach (var viewType in scriptedObjectsToRecreate) { db.RecreateScriptedObject(viewType); } } return(migrationsRun); }
private void InitCityData() { try { UpdateCityListXML update = new UpdateCityListXML(); bool result = update.Download(); if (result) { DataMigration dm = new DataMigration(); dm.LoadCityXML(); //刷新城市数据 InitCiyCondition(); } } catch (Exception ex) { log.WriteLog(ex.ToString()); } }
private void UpdateWebsiteData() { try { UpdateWebsiteXML update = new UpdateWebsiteXML(); bool result = update.Download(); if (result) { DataMigration dm = new DataMigration(); dm.LoadWebsiteXML(); //刷新网址数据 GetFixedWebsiteData(); InitConfigWebsite(); } } catch (Exception ex) { log.WriteLog(ex.ToString()); } }
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory) { //setting log loggerFactory.AddConsole(LogLevel.Error, true); //setting configuration var configBuilder = new ConfigurationBuilder() .SetBasePath(env.ContentRootPath) .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true) .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true, reloadOnChange: true) .AddJsonFile("Secrets/secrets.json", optional: true, reloadOnChange: true) .AddJsonFile($"Secrets/secrets.{env.EnvironmentName}.json", optional: true, reloadOnChange: true); LoadTestConfig.UseConfiguration(configBuilder.Build()); var config = new LoadTestConfig(); //building pipeline var appBuilder = app.UseMiddleware <EventResultMiddleware>(); //settign Forwarded Headers appBuilder = appBuilder.UseForwardedHeaders(new ForwardedHeadersOptions() { ForwardedHeaders = ForwardedHeaders.All }); //setting default route for controllers appBuilder.UseMvcWithDefaultRoute(); //serialization settings JsonConvert.DefaultSettings = new Func <JsonSerializerSettings>(() => { return(DefaultJsonSerializer); }); //executing migration var migration = new DataMigration(config); migration.Execute(); }
/// <summary> /// Run all defined data migrations that have not been run yet /// </summary> /// <param name="db">current database connection</param> /// <returns>Count of migrations run</returns> public static int RunMigrations(IDbConnection db) { var migrationsRun = 0; var migrationTypes = GetMigrationTypes(); if (!db.TableExists(db.GetTableName <DataMigration>())) { // Create all data, run data init migration var schemaBuilder = new SchemaBuilder(db); schemaBuilder.GenerateSchema(false); var dataInitMigration = new InitializeDbMigration(); dataInitMigration.Execute(db); // Populate all migrations as run var migrations = migrationTypes.Select(x => new DataMigration { Name = x.Name }); return(db.BulkInsert(migrations)); } var alreadyExecutedMigrations = db.Query <DataMigration>("select * from [{0}]".Fmt(db.GetTableName <DataMigration>())); foreach (var migration in migrationTypes.Where(t => !alreadyExecutedMigrations.Any(m => m.Name == t.Name)).Select(x => (IMigration)Activator.CreateInstance(x)).OrderBy(x => x.Order)) { migration.Execute(db); // add migration to database var migrationData = new DataMigration { CreatedOn = DateTime.UtcNow, Name = migration.GetType().Name }; db.Save(migrationData); migrationsRun++; } return(migrationsRun); }
public static void PreStart() { var db = ObjectFactory.GetInstance <IDbConnection>(); if (!db.TableExists(db.GetTableName <DataMigration>())) { db.CreateTable <DataMigration>(); } var alreadyExecutedMigrations = db.Query <DataMigration>("select * from [{0}]".Fmt(db.GetTableName <DataMigration>())); var migrationTypes = GetMigrationTypes(); foreach (var migration in migrationTypes.Where(t => !alreadyExecutedMigrations.Any(m => m.Name == t.Name)).Select(x => (IMigration)Activator.CreateInstance(x)).OrderBy(x => x.Order)) { migration.Execute(db); // add migration to database var migrationData = new DataMigration { CreatedOn = DateTime.UtcNow, Name = migration.GetType().Name }; db.Save(migrationData); } }
/// <summary> /// 数据迁移系统 /// </summary> /// <param name="key">迁移目标</param> /// <param name="token">认证Token</param> /// <returns></returns> public BaseApiResponse <string> Post(int key, string token) { var response = new BaseApiResponse <string>() { Status = StatusEnum.Success.ToString() }; if (string.Compare(token, "hsjf", StringComparison.OrdinalIgnoreCase) != 0) { response.Status = StatusEnum.Failed.ToString(); response.Message = "非法Token"; return(response); } try { var dataMigration = new DataMigration(); switch (key) { case 1: // 更新案件号 dataMigration.UpdateCaseNum(); break; case 2: // 更新年利率 dataMigration.TransferAnnualRate(); break; case 3: // 更新审批期限 dataMigration.TransferAuditTerm(); break; case 4: // 签约合并文件 dataMigration.MortgageFileMerge(); break; case 5: dataMigration.InsertHisCase(); break; case 6: var list = dataMigration.GetNoFileCase(); if (list != null) { response.Message = Newtonsoft.Json.JsonConvert.SerializeObject(list); } break; default: response.Status = StatusEnum.Failed.ToString(); response.Message = "key无法识别"; break; } } catch (Exception ex) { response.Status = StatusEnum.Failed.ToString(); response.Message = ex.Message; } return(response); }
private async Task ExecuteWithConnectionsAsync(DataMigration migration, Func <SqlConnection, SqlConnection, Task> execute) => await ExecuteWithConnectionsAsync(migration.SourceConnection, migration.DestConnection, execute);
public async Task AddStepColumnsAsync(DataMigration migration, DataMigration.Step step) => await AddStepColumnsAsync(migration.SourceConnection, migration.DestConnection, step, migration.GetParameters());
public Task ParseTask(Issue issue) { if (issue == null || issue.Fields == null) { return(null); } var jiraBaseUrl = ConfigurationManager.AppSettings["JiraBaseUrl"]; var ticket = default(Task); switch (issue.Fields.IssueType.Name) { case "Acceptance Test": ticket = new AcceptanceTest(issue, jiraBaseUrl); break; case "Bug": ticket = new Bug(issue, jiraBaseUrl); break; case "Defect": ticket = new Defect(issue, jiraBaseUrl); break; case "Data Fix": ticket = new DataFix(issue, jiraBaseUrl); break; case "Data Migration": ticket = new DataMigration(issue, jiraBaseUrl); break; case "Epic": ticket = new Epic(issue, jiraBaseUrl); break; case "Change Request": ticket = new ChangeRequest(issue, jiraBaseUrl); break; case "Technical Ticket": ticket = new TechnicalTicket(issue, jiraBaseUrl); break; case "Spike": ticket = new Spike(issue, jiraBaseUrl); break; case "Task": ticket = new StorySubTask(issue, jiraBaseUrl); break; default: // Story ticket = new Story(issue, jiraBaseUrl); break; } if (ticket.Issue.Fields.SubTasks.Count > 0) { ticket.SubTasks = ParseSubTasks(ticket.Issue.Fields.SubTasks); } ticket.TransitionHistory = ParseTransitionHistory(ticket); return(ticket); }
public void CreateDataMigration(string featureName) { Context.Output.WriteLine(T("Creating Data Migration for {0}", featureName)); ExtensionDescriptor extensionDescriptor = _extensionManager.AvailableExtensions().FirstOrDefault(extension => DefaultExtensionTypes.IsModule(extension.ExtensionType) && extension.Features.Any(feature => String.Equals(feature.Id, featureName, StringComparison.OrdinalIgnoreCase))); if (extensionDescriptor == null) { Context.Output.WriteLine(T("Creating data migration failed: target Feature {0} could not be found.", featureName)); return; } string dataMigrationFolderPath = HostingEnvironment.MapPath("~/Modules/" + extensionDescriptor.Id + "/"); string dataMigrationFilePath = dataMigrationFolderPath + "Migrations.cs"; string moduleCsProjPath = HostingEnvironment.MapPath(string.Format("~/Modules/{0}/{0}.csproj", extensionDescriptor.Id)); if (!Directory.Exists(dataMigrationFolderPath)) { Directory.CreateDirectory(dataMigrationFolderPath); } if (File.Exists(dataMigrationFilePath)) { Context.Output.WriteLine(T("Data migration already exists in target Module {0}.", extensionDescriptor.Id)); return; } List <SchemaCommand> commands = _schemaCommandGenerator.GetCreateFeatureCommands(featureName, false).ToList(); string dataMigrationText; using (var stringWriter = new StringWriter()) { var interpreter = new CodeGenerationCommandInterpreter(stringWriter); foreach (var command in commands) { interpreter.Visit(command); stringWriter.WriteLine(); } var migrationTemplate = new DataMigration { Session = new Dictionary <string, object>() }; migrationTemplate.Session["FeatureName"] = featureName; migrationTemplate.Session["Commands"] = stringWriter.ToString(); migrationTemplate.Initialize(); dataMigrationText = migrationTemplate.TransformText(); } File.WriteAllText(dataMigrationFilePath, dataMigrationText); string projectFileText = File.ReadAllText(moduleCsProjPath); // The string searches in solution/project files can be made aware of comment lines. if (projectFileText.Contains("<Compile Include")) { string compileReference = string.Format("<Compile Include=\"{0}\" />\r\n ", "Migrations.cs"); projectFileText = projectFileText.Insert(projectFileText.LastIndexOf("<Compile Include"), compileReference); } else { string itemGroupReference = string.Format("</ItemGroup>\r\n <ItemGroup>\r\n <Compile Include=\"{0}\" />\r\n ", "Migrations.cs"); projectFileText = projectFileText.Insert(projectFileText.LastIndexOf("</ItemGroup>"), itemGroupReference); } File.WriteAllText(moduleCsProjPath, projectFileText); TouchSolution(Context.Output); Context.Output.WriteLine(T("Data migration created successfully in Module {0}", extensionDescriptor.Id)); }
public void InsertData() { var dataMigration = new DataMigration(_context); dataMigration.InsertData(); }
public DataModel CreateDataModel(string databaseDirectory, string fileDsnLocation) { // 99 - 08 // 09 - 11 - added week 0 score to players, added course name to week // 12 - 13 - added status to player const int lastYear = 2015; Dictionary <string, Player> namesToPlayers = new Dictionary <string, Player>(); ICollection <Player> extraInvalidPlayers = new List <Player>(); HashSet <string> setOfPlayers = new HashSet <string>(); int teamIndex = 1, courseIndex = 1, weekIndex = 1, yearIndex = 1, yearDataIndex = 1, teamMatchupIndex = 1, playerIndex = 1, matchupIndex = 1, resultIndex = 1, dataMigrationIndex = 1, teamYearDataIndex = 1; Dictionary <string, Team> teamNameToTeam = new Dictionary <string, Team>(); Dictionary <string, Course> courseNameToCourse = new Dictionary <string, Course>(); Dictionary <int, Year> yearIdToYear = new Dictionary <int, Year>(); Dictionary <int, Year> yearValueToYear = new Dictionary <int, Year>(); Dictionary <int, TeamMatchup> teamMatchupIdMatchup = new Dictionary <int, TeamMatchup>(); ICollection <MatchUp> allMatchUps = new List <MatchUp>(); ICollection <Result> allResults = new List <Result>(); ICollection <YearData> yearDatas = new List <YearData>(); ICollection <TeamYearData> teamYearDatas = new List <TeamYearData>(); ICollection <Week> allWeeks = new List <Week>(); ICollection <DataMigration> dataMigrationDatas = new List <DataMigration>(); // Add fake team and fake players (these will be used later) var teamOfLostPlayers = new Team { Id = teamIndex++, Name = "Dummy Team", ValidTeam = false }; var noShowPlayer = new Player(playerIndex++) { Name = "No Show", CurrentHandicap = 0, ValidPlayer = false }; namesToPlayers[noShowPlayer.Name] = noShowPlayer; var nonLeagueSub = new Player(playerIndex++) { Name = "Non-League Sub", CurrentHandicap = 0, ValidPlayer = false }; namesToPlayers[nonLeagueSub.Name] = nonLeagueSub; for (int year = 1999; year < lastYear; year++) { OdbcCommand cmd; OdbcConnection connection; string yearStr = Convert.ToString(year); string connectionString = @"filedsn=" + fileDsnLocation + "; Uid=Admin; Pwd=bigmatt; DBQ=" + databaseDirectory + @"\golf" + yearStr.Substring(2) + ".mdb"; using (connection = new OdbcConnection(connectionString)) { connection.Open(); cmd = new OdbcCommand(); cmd.Connection = connection; // Year var newYear = new Year { Id = yearIndex++, Value = year, Complete = year == DateTime.Now.Year ? false : true }; yearIdToYear[newYear.Id] = newYear; yearValueToYear[year] = newYear; // add year data for both no show and non-league sub { var noShowPlayerYd = new YearData { FinishingHandicap = 20, Id = yearDataIndex++, Player = noShowPlayer, StartingHandicap = 20, Rookie = false, Team = teamOfLostPlayers, Week0Score = 99, Year = newYear }; yearDatas.Add(noShowPlayerYd); noShowPlayer.AddYearData(noShowPlayerYd); } { var nonLeagueSubYd = new YearData { FinishingHandicap = 20, Id = yearDataIndex++, Player = nonLeagueSub, StartingHandicap = 20, Rookie = false, Team = teamOfLostPlayers, Week0Score = 99, Year = newYear }; yearDatas.Add(nonLeagueSubYd); nonLeagueSub.AddYearData(nonLeagueSubYd); } // Data migration DateTime migrationDate = DateTime.UtcNow; if (year != DateTime.Now.Year) { migrationDate.AddDays(20); } var dataMigration = new DataMigration { Id = dataMigrationIndex++, Year = newYear, Notes = yearStr, DataMigrationDate = migrationDate }; dataMigrationDatas.Add(dataMigration); // Weeks cmd.CommandText = "SELECT * FROM WeekTable"; Dictionary <int, Week> weekTempIdToWeek = new Dictionary <int, Week>(); Dictionary <int, int> weekIdToTeamMatchupIndex = new Dictionary <int, int>(); using (var weekReader = cmd.ExecuteReader()) { while (weekReader.Read()) { string courseName = string.Empty; int coursePar = Convert.ToInt32(weekReader.GetString(1)); Course course = null; if (year < 2009) { courseName = Convert.ToString(year) + " Course " + Convert.ToString(coursePar); } else { courseName = weekReader.GetString(3); //coursePar = Convert.ToInt32(weekReader.GetString(1)); } if (!courseNameToCourse.TryGetValue(courseName, out course)) { course = new Course { Name = courseName, Id = courseIndex++, Par = coursePar }; courseNameToCourse.Add(courseName, course); } var weekDate = weekReader.GetString(2).Replace("Sept.", "September"); Week week = new Week { SeasonIndex = weekReader.GetInt32(0), Course = course, Date = DateTime.Parse(weekDate), Id = weekIndex++, Year = newYear }; if (week.SeasonIndex != 0) { week.PairingId = ((week.SeasonIndex - 1) % 3) + 1; allWeeks.Add(week); } else { week.PairingId = 1; } // // TODO: Manually set some pairing indexes for 2014. // weekTempIdToWeek[week.SeasonIndex] = week; weekIdToTeamMatchupIndex[week.SeasonIndex] = 0; } } if (year == 2014) { weekTempIdToWeek[10].PairingId = 3; weekTempIdToWeek[11].PairingId = 1; weekTempIdToWeek[12].PairingId = 2; weekTempIdToWeek[13].PairingId = 3; weekTempIdToWeek[14].PairingId = 1; weekTempIdToWeek[15].PairingId = 2; weekTempIdToWeek[16].PairingId = 3; weekTempIdToWeek[17].PairingId = 3; weekTempIdToWeek[18].PairingId = 1; } // Now that we are done processing weeks, assign playoff values to each one. var lastTwoWeeksOfYear = weekTempIdToWeek.Values.OrderByDescending(x => x.SeasonIndex).Take(2); foreach (var week in lastTwoWeeksOfYear) { week.IsPlayoff = true; } // Teams cmd.CommandText = "SELECT * FROM TeamTable"; Dictionary <int, Team> teamIdToTeam = new Dictionary <int, Team>(); using (var teamReader = cmd.ExecuteReader()) { while (teamReader.Read()) { var teamId = teamReader.GetInt32(0); var teamName = teamReader.GetString(1); if (string.Equals(teamName, "Mentally Handicapped", StringComparison.OrdinalIgnoreCase)) { teamName = "Golf Gods"; } Team team = null; if (!teamNameToTeam.TryGetValue(teamName, out team)) { bool validTeam = true; // Garbage team from 2005. Throw it awayyy if (string.Equals(teamName, "Handicap", StringComparison.OrdinalIgnoreCase)) { validTeam = false; } team = new Team() { Name = teamName, Id = teamIndex++, ValidTeam = validTeam }; teamNameToTeam.Add(teamName, team); } TeamYearData tyd = new TeamYearData { Id = teamYearDataIndex++, TeamId = team.Id, Year = yearValueToYear[year] }; teamYearDatas.Add(tyd); teamIdToTeam.Add(teamId, team); } } // Add our dummy team :\ if (!teamIdToTeam.ContainsKey(0) && !teamIdToTeam.ContainsKey(99)) { teamIdToTeam[0] = teamIdToTeam[99] = teamOfLostPlayers; TeamYearData tyd = new TeamYearData { Id = teamYearDataIndex++, TeamId = teamOfLostPlayers.Id, Year = yearValueToYear[year] }; teamYearDatas.Add(tyd); } // Players cmd.CommandText = "SELECT * FROM PlayerTable"; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { string playerName = reader.GetString(0); int playersTeam = reader.GetInt32(1); int startingHandicap = 0; bool isRookie = false; int week0Score = -1; // If the year is before 2009, we'll update the handicaps later if (year >= 2009) { week0Score = reader.GetInt32(2); startingHandicap = week0Score - 36; } // If we have player status, then set the rookie value based on status. // Otherwise, everyone is assumed a veteran. if (year >= 2011) { var status = reader.GetString(3); isRookie = string.Equals(status, "new", StringComparison.OrdinalIgnoreCase) ? true : false; } Player player = null; if (!namesToPlayers.TryGetValue(playerName, out player)) { player = new Player(playerIndex++) { Name = playerName, CurrentHandicap = startingHandicap, ValidPlayer = true }; namesToPlayers[playerName] = player; setOfPlayers.Add(playerName); } // TODO: Needs to be something else //player.Team = teamIdToTeam[playersTeam]; // Current handicap will be updated by handicap code later player.CurrentHandicap = startingHandicap; // finishing handicap will be updated later. YearData yearData = new YearData { Player = player, Rookie = isRookie, StartingHandicap = startingHandicap, FinishingHandicap = startingHandicap, Year = yearValueToYear[year], Id = yearDataIndex++, Team = teamIdToTeam[playersTeam], Week0Score = week0Score == -1 ? 0 : week0Score // only years > 2009 will have a valid value here. }; yearDatas.Add(yearData); player.AddYearData(yearData); } } var playoffWeeksInOrder = weekTempIdToWeek.Values.Where(x => x.IsPlayoff).OrderBy(x => x.SeasonIndex).ToList(); // Matches cmd.CommandText = "SELECT * FROM MatchTable"; using (var matchReader = cmd.ExecuteReader()) { LinkedList <Tuple <int, TeamMatchup> > allTeamMatchupsForYear = new LinkedList <Tuple <int, TeamMatchup> >(); while (matchReader.Read()) { int weekId = matchReader[2] == System.DBNull.Value ? -1 : (matchReader.GetInt32(2)); int matchId = matchReader[1] == System.DBNull.Value ? -1 : Convert.ToInt32(matchReader.GetString(1)); if (matchId == -1 || weekId == -1) { continue; } var team1IdType = matchReader[3].GetType(); var team2IdType = matchReader[4].GetType(); if (team1IdType == System.DBNull.Value.GetType() || team2IdType == System.DBNull.Value.GetType()) { continue; } int team1Id = typeof(string) == team1IdType?Convert.ToInt32(matchReader.GetString(3)) : matchReader.GetInt32(3); int team2Id = typeof(string) == team2IdType?Convert.ToInt32(matchReader.GetString(4)) : matchReader.GetInt32(4); // TODO/NOTE: Treating null as "false". Change if it should be treated as true. bool matchComplete = matchReader[0].GetType() == DBNull.Value.GetType() || string.Equals(matchReader.GetString(0), "N", StringComparison.OrdinalIgnoreCase) ? false : true; if (!matchComplete && team1Id == 0 && team2Id == 0) { continue; } var weekForMatchup = weekTempIdToWeek[weekId]; var matchOrderInWeek = weekIdToTeamMatchupIndex[weekId]; var teamMatchup = new TeamMatchup { //MatchOrderInWeek = matchOrderInWeek, Week = weekForMatchup, Id = teamMatchupIndex++, MatchComplete = matchComplete, Team1 = teamIdToTeam[team1Id], Team2 = teamIdToTeam[team2Id], MatchId = matchId, PlayoffType = weekForMatchup.IsPlayoff ? GetPlayoffType(playoffWeeksInOrder.IndexOf(weekForMatchup), matchOrderInWeek) : null }; allTeamMatchupsForYear.AddLast(new Tuple <int, TeamMatchup>(weekId, teamMatchup)); weekIdToTeamMatchupIndex[weekId] = matchOrderInWeek + 1; teamMatchupIdMatchup[teamMatchup.Id] = teamMatchup; } var lookups = allTeamMatchupsForYear.ToLookup(x => x.Item1, x => x.Item2); foreach (var l in lookups) { var orderedMatches = l.OrderBy(x => x.MatchId).ToList(); for (int i = 0; i < orderedMatches.Count; i++) { orderedMatches[i].MatchOrderInWeek = i; } } } // Results cmd.CommandText = "SELECT * FROM ResultsTable"; using (var resultsReader = cmd.ExecuteReader()) { while (resultsReader.Read()) { var weekId = resultsReader.GetInt32(0); var team1Id = resultsReader.GetInt32(1); var score1 = resultsReader.GetInt32(3); var points1 = resultsReader.GetInt32(4); var team2Id = resultsReader.GetInt32(5); var score2 = resultsReader.GetInt32(7); var points2 = resultsReader.GetInt32(8); var player1Name = resultsReader.GetString(2); var player2Name = resultsReader.GetString(6); // TODO: Can we throw this out? It doesn't seem to make sense //if (team1Id == team2Id && weekId != 0) //{ // throw new ArgumentException("team1: " + team1Id + ", team2: " + team2Id + ", week: " + weekId); //} Player player1 = null; Player player2 = null; if (!setOfPlayers.Contains(player1Name)) { //Console.WriteLine("player missing: " + player1Name); } if (!setOfPlayers.Contains(player2Name)) { //Console.WriteLine("player missing: " + player2Name); } if (!namesToPlayers.TryGetValue(player1Name, out player1)) { var invalidPlayer = GetProperInvalidPlayer(player1Name, noShowPlayer, nonLeagueSub); // this is a special 2011 if (invalidPlayer == null) { if (year != 2011) { throw new InvalidOperationException("invalid player found in year not 2011: " + player1Name); } invalidPlayer = new Player(playerIndex++) { Name = player1Name, ValidPlayer = false, CurrentHandicap = 0 }; var invalidPlayerYd = new YearData { Year = newYear, Week0Score = 99, StartingHandicap = 20, FinishingHandicap = 20, Id = yearDataIndex++, Team = teamOfLostPlayers, Rookie = false, Player = invalidPlayer }; invalidPlayer.AddYearData(invalidPlayerYd); yearDatas.Add(invalidPlayerYd); extraInvalidPlayers.Add(invalidPlayer); } player1 = invalidPlayer; } if (!namesToPlayers.TryGetValue(player2Name, out player2)) { var invalidPlayer = GetProperInvalidPlayer(player2Name, noShowPlayer, nonLeagueSub); // this is a special 2011 if (invalidPlayer == null) { if (year != 2011) { throw new InvalidOperationException("invalid player found in year not 2011: " + player2Name); } invalidPlayer = new Player(playerIndex++) { Name = player2Name, ValidPlayer = false, CurrentHandicap = 0 }; var invalidPlayerYd = new YearData { Year = newYear, Week0Score = 99, StartingHandicap = 20, FinishingHandicap = 20, Id = yearDataIndex++, Team = teamOfLostPlayers, Rookie = false, Player = invalidPlayer }; invalidPlayer.AddYearData(invalidPlayerYd); yearDatas.Add(invalidPlayerYd); extraInvalidPlayers.Add(invalidPlayer); } player2 = invalidPlayer; } // okay, now that we have valid players, continue on. Team team1 = teamIdToTeam[team1Id]; if (!teamIdToTeam.ContainsKey(team2Id)) { //Console.WriteLine("Where is dis team: " + team2Id); } Team team2 = teamIdToTeam[team2Id]; Week week = weekTempIdToWeek[weekId]; // Week 0's are a really special case... we allow team IDs to match here. if (weekId == 0) { // Take player1 and player2 and set their current handicaps // Set the week0 score in to the year data's starting handicap. int player1Handicap = score1 - 36; int player2Handicap = score2 - 36; var player1YearDatas = yearDatas.Where(y => y.Player.Id == player1.Id && y.Year.Value == year); var player2YearDatas = yearDatas.Where(y => y.Player.Id == player2.Id && y.Year.Value == year); // Some folks show up in the results table for week 0 even though they aren't valid // for the year... if that makes sense. In this case, we'll just throw out the handicap value. if (player1.ValidPlayer && team1Id != 99) { // current handicap for a player will be overwritten with each year we pass. // So, eventually the last year iteration that will be seen will replace the current handicap // with the correct value. player1.CurrentHandicap = player1Handicap; var player1YearDataForThisYear = player1YearDatas.First(); player1YearDataForThisYear.Week0Score = score1; // finishing handicap will be fixed later when we process handicaps. player1YearDataForThisYear.StartingHandicap = player1YearDataForThisYear.FinishingHandicap = player1Handicap; } if (player2.ValidPlayer && team2Id != 99) { player2.CurrentHandicap = player2Handicap; var player2YearDataForThisYear = player2YearDatas.First(); player2YearDataForThisYear.Week0Score = score2; // finishing handicap will be fixed later when we process handicaps. player2YearDataForThisYear.StartingHandicap = player2YearDataForThisYear.FinishingHandicap = player2Handicap; } // Console.WriteLine("could not find matchup."); continue; } // Team matchups should be unique based on team ID and week ID for a year. var teamMatchups = teamMatchupIdMatchup.Values.Where(t => ((t.Team1.Id == teamIdToTeam[team1Id].Id || t.Team2.Id == teamIdToTeam[team2Id].Id) || (t.Team1.Id == teamIdToTeam[team2Id].Id || t.Team2.Id == teamIdToTeam[team1Id].Id)) && t.Week.SeasonIndex == weekId && t.Week.Year.Value == year); if (teamMatchups.Count() == 0) { throw new InvalidOperationException("Should've found a team matchup... " + Convert.ToString(team1Id) + " " + Convert.ToString(weekId)); } if (team1Id == team2Id && year != 2011) { throw new ArgumentException("Teams can only play eachother in week 0 and 2011."); } // AGerber - 7/3/2014 allow no show/no show matches to show up in data. // Don't do anything with invalid players that play themselves! //if (player1.Id == 1 && player2.Id == 1) //{ // continue; //} var teamMatchup = teamMatchups.First(); MatchUp matchup = new MatchUp { Id = matchupIndex++, TeamMatchup = teamMatchup, Player1 = player1, Player2 = player2 }; allMatchUps.Add(matchup); Result player1Result = new Result { Year = newYear, Player = player1, Matchup = matchup, Points = points1, Score = score1, Id = resultIndex++, Team = team1 }; allResults.Add(player1Result); team1.AddResult(player1Result); player1.AddResult(player1Result); Result player2Result = new Result { Year = newYear, Player = player2, Matchup = matchup, Points = points2, Score = score2, Id = resultIndex++, Team = team2 }; allResults.Add(player2Result); team2.AddResult(player2Result); player2.AddResult(player2Result); matchup.Result1 = player1Result; matchup.Result2 = player2Result; } } connection.Close(); } } DataModel dm = new DataModel { Teams = new List <Team>(teamNameToTeam.Values) { teamOfLostPlayers }, Years = yearValueToYear.Values, Players = namesToPlayers.Values.Concat(extraInvalidPlayers).ToList(), MatchUp = allMatchUps, TeamMatchup = teamMatchupIdMatchup.Values, Weeks = allWeeks, YearDatas = yearDatas, Results = allResults, Courses = courseNameToCourse.Values, LeaderBoardDatas = new List <LeaderBoardData>(), LeaderBoards = new List <LeaderBoard>(), DataMigrations = dataMigrationDatas, TeamYearData = teamYearDatas }; ProcessHandicaps(dm); BuildLeaderboards(dm); return(dm); }