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);
}
