public static void AddPeople(string filename)
{
const int titleIndex = 2;
const int lastnameIndex = 3;
const int firstnameIndex = 4;
var parsedFile = DataImportGeneral.ParseFile(filename);
const int startIndex = 1;
using (var context = new ElectionDBEntities())
{
for (var i = startIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
var person = new Person
{
Title = string.IsNullOrWhiteSpace(line[titleIndex]) ? null : line[titleIndex].Trim(),
Firstname = string.IsNullOrWhiteSpace(line[firstnameIndex]) ? null : line[firstnameIndex].Trim(),
Lastname = line[lastnameIndex].Trim()
};
context.People.Add(person);
Console.WriteLine("Added {0} {1} {2}", person.Title, person.Firstname, person.Lastname);
}
Console.WriteLine("Save changes...");
context.SaveChanges();
}
}
public static void AddParties(string filename)
{
const int lineIndex = 2;
const int lineStartIndex = 19;
var parsedFile = DataImportGeneral.ParseFile(filename);
var line = parsedFile.ElementAt(lineIndex);
using (var context = new ElectionDBEntities())
{
for (var i = lineStartIndex; i < line.Count; i++)
{
var name = line[i];
if (!string.IsNullOrWhiteSpace(name))
{
context.Parties.Add(new Party
{
Name = name.Trim()
});
}
}
context.SaveChanges();
}
}
public static void AddCandidateList(string filename)
{
const int firstnameIndex = 1;
const int lastnameIndex = 2;
const int partynameIndex = 3;
const int bundeslandIndex = 5;
const int positionIndex = 6;
var parsedFile = DataImportGeneral.ParseFile(filename);
const int startIndex = 1;
using (var context = new ElectionDBEntities())
{
var bundeslandDictionary = context.Bundeslands.ToDictionary(b => b.Name, b => b.Id);
for (var i = startIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
var result = GetTitleFirstnameLastnamePartynameFromLine(line);
var firstname = result.ElementAt(firstnameIndex);
var lastname = result.ElementAt(lastnameIndex);
var partyname = result.ElementAt(partynameIndex);
var bundeslandName = line[bundeslandIndex].Trim();
if (string.IsNullOrWhiteSpace(bundeslandName))
{
continue;
}
var bundeslandId = bundeslandDictionary[bundeslandName];
var position = int.Parse(line[positionIndex]);
var existing = GetExistingCandidate(context, firstname, lastname, partyname, line[2]);
var entry = new CandidateList
{
Election_Id = electionId,
Bundesland_Id = bundeslandId,
Person_Id = existing.Id,
Position = position
};
context.CandidateLists.Add(entry);
Console.WriteLine("Added person {0} for bundesland {1} on position {2} in election {3}", existing.Id, bundeslandId, position, electionId);
}
context.SaveChanges();
}
}
public static void AddPartyAffiliations(string filename)
{
const int personIdIndex = 1;
const int partyIndex = 6;
var parsedFile = DataImportGeneral.ParseFile(filename);
const int startIndex = 1;
using (var context = new ElectionDBEntities())
{
for (var i = startIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
// Get Party
var partyName = line[partyIndex].Trim();
var filteredPartyName = DataImportGeneral.PartyNameDictionary.ContainsKey(partyName)
? DataImportGeneral.PartyNameDictionary[partyName]
: partyName;
var party = context.Parties.SingleOrDefault(r => r.Name == filteredPartyName);
if (party == null)
{
// Parteilos if no party was set
if (string.IsNullOrWhiteSpace(partyName))
{
party = context.Parties.Single(p => p.Name == DataImportGeneral.Impartially);
}
else
{
// Error: Party was set but not found in the database
throw new Exception(string.Format("Party {0} does not exist in the database!", partyName));
}
}
var personId = int.Parse(line[personIdIndex]);
context.PartyAffiliations.Add(new PartyAffiliation
{
Election_Id = electionId,
Person_Id = personId,
Party_Id = party.Id
});
Console.WriteLine("Added person {0} to party '{1}' for election {2}", personId, party.Name, electionId);
}
context.SaveChanges();
}
}
public static void AddPeople(string filename)
{
const int titleIndex = 0;
const int firstnameIndex = 1;
const int lastnameIndex = 2;
const int partynameIndex = 3;
var parsedFile = DataImportGeneral.ParseFile(filename);
const int startIndex = 1;
using (var context = new ElectionDBEntities())
{
for (var i = startIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
var result = GetTitleFirstnameLastnamePartynameFromLine(line);
var title = result.ElementAt(titleIndex);
var firstname = result.ElementAt(firstnameIndex);
var lastname = result.ElementAt(lastnameIndex);
var partyname = result.ElementAt(partynameIndex);
var existing = context.People.Where(p => p.Firstname == firstname && p.Lastname == lastname);
if (existing.Any(e => e.PartyAffiliations.Any(a => a.Party.Name == partyname)))
{
if (!(firstname == "Andreas" && lastname == "Müller" && int.Parse(line[2]) == 1959))
{
Console.WriteLine("{0} {1} {2} ({3}) does already exist", title ?? "", firstname, lastname,
partyname);
continue;
}
}
var entry = new Person
{
Title = title,
Firstname = firstname,
Lastname = lastname
};
context.People.Add(entry);
Console.WriteLine("Added {0} {1} {2} ({3})", title ?? "", firstname, lastname, partyname);
}
context.SaveChanges();
}
}
public static void AddIsElectableCandidate(string filename)
{
const int firstnameIndex = 1;
const int lastnameIndex = 2;
const int partynameIndex = 3;
const int wahlkreisIdIndex = 4;
var parsedFile = DataImportGeneral.ParseFile(filename);
const int startIndex = 1;
using (var context = new ElectionDBEntities())
{
for (var i = startIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
var result = GetTitleFirstnameLastnamePartynameFromLine(line);
var firstname = result.ElementAt(firstnameIndex);
var lastname = result.ElementAt(lastnameIndex);
var partyname = result.ElementAt(partynameIndex);
int wahlkreisId;
var parsed = int.TryParse(line[wahlkreisIdIndex], out wahlkreisId);
if (!parsed)
{
continue;
}
var existing = GetExistingCandidate(context, firstname, lastname, partyname, line[2]);
var entry = new IsElectableCandidate
{
Election_Id = electionId,
Person_Id = existing.Id,
Wahlkreis_Id = wahlkreisId
};
context.IsElectableCandidates.Add(entry);
Console.WriteLine("Added person {0} is electable at wahlkreis {1} in election {2}", existing.Id, wahlkreisId, electionId);
}
context.SaveChanges();
}
}
public static void AddCandidateList(string filename)
{
const int personIdIndex = 1;
const int bundeslandIndex = 8;
const int positionIndex = 9;
var parsedFile = DataImportGeneral.ParseFile(filename);
const int startIndex = 1;
using (var context = new ElectionDBEntities())
{
var idDictionary = context.Bundeslands.ToDictionary(d => d.Name, d => d.Id);
for (var i = startIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
var bundeslandShort = line[bundeslandIndex];
if (string.IsNullOrWhiteSpace(bundeslandShort))
{
continue;
}
var bundeslandName = DataImportGeneral.BundeslaenderShortDictionary[bundeslandShort];
var bundeslandId = idDictionary[bundeslandName];
var personId = int.Parse(line[personIdIndex]);
var position = int.Parse(line[positionIndex]);
var entry = new CandidateList
{
Election_Id = electionId,
Person_Id = personId,
Bundesland_Id = bundeslandId,
Position = position
};
context.CandidateLists.Add(entry);
}
context.SaveChanges();
}
}
public static void AddIsElectableCandidate(string filename)
{
const int personIdIndex = 1;
const int wahlkreisIdIndex = 7;
var parsedFile = DataImportGeneral.ParseFile(filename);
const int startIndex = 1;
using (var context = new ElectionDBEntities())
{
for (var i = startIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
int wahlkreisId;
var parsed = int.TryParse(line[wahlkreisIdIndex], out wahlkreisId);
if (!parsed)
{
continue;
}
var personId = int.Parse(line[personIdIndex]);
var entry = new IsElectableCandidate
{
Election_Id = electionId,
Person_Id = personId,
Wahlkreis_Id = wahlkreisId
};
context.IsElectableCandidates.Add(entry);
Console.WriteLine("Added person {0} is electable in wahlkreis {1} for election {2}", personId, wahlkreisId, electionId);
}
context.SaveChanges();
}
}
public static void AddPopulationData()
{
var populationDictionary = new Dictionary <string, int>
{
{ "Schleswig-Holstein", 2686085 },
{ "Mecklenburg-Vorpommern", 1585032 },
{ "Hamburg", 1559655 },
{ "Niedersachsen", 7354892 },
{ "Bremen", 575805 },
{ "Brandenburg", 2418267 },
{ "Sachsen-Anhalt", 2247673 },
{ "Berlin", 3025288 },
{ "Nordrhein-Westfalen", 15895182 },
{ "Sachsen", 4005278 },
{ "Hessen", 5388350 },
{ "Thüringen", 2154202 },
{ "Rheinland-Pfalz", 3672888 },
{ "Bayern", 11353264 },
{ "Baden-Württemberg", 9482902 },
{ "Saarland", 919402 }
};
DataImportGeneral.AddPopulationData(electionId, populationDictionary);
}
public static void AddPopulationData()
{
var populationDictionary = new Dictionary <string, int>
{
{ "Schleswig-Holstein", 2834300 },
{ "Mecklenburg-Vorpommern", 1664400 },
{ "Hamburg", 1772100 },
{ "Niedersachsen", 7947200 },
{ "Bremen", 661900 },
{ "Brandenburg", 2522500 },
{ "Sachsen-Anhalt", 2381900 },
{ "Berlin", 3431700 },
{ "Nordrhein-Westfalen", 17933100 },
{ "Sachsen", 4192800 },
{ "Hessen", 6065000 },
{ "Thüringen", 2267800 },
{ "Rheinland-Pfalz", 4028400 },
{ "Bayern", 12519700 },
{ "Baden-Württemberg", 10749500 },
{ "Saarland", 1030300 }
};
DataImportGeneral.AddPopulationData(electionId, populationDictionary);
}
// Quite similar to GenerateVotesFor2009. High number of clones!
public static void GenerateVotesFor2013(string filename)
{
const int electionId = 2;
const int partyNameLineIndex = 2;
const int startLineIndex = 5;
const int wahlkreisIdIndex = 0;
const int bundeslandIdIndex = 2;
const int allowedVoterIndex = 3;
const int voterIndex = 7;
const int invalidErststimmeIndex = 11;
const int invalidZweitstimmeIndex = 13;
const int startPartyColumnIndex = 19;
const int skippedBundeslandId = 99;
var parsedFile = DataImportGeneral.ParseFile(filename);
using (var context = new ElectionDBEntities())
{
var election = context.Elections.Single(e => e.Id == electionId);
Console.WriteLine("Generate votes for election {0}", electionId);
Console.WriteLine();
var partyDictionary = context.Parties.ToDictionary(p => p.Name, p => p.Id);
for (var i = startLineIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
int wahlkreisId;
var parsed = int.TryParse(line[wahlkreisIdIndex], out wahlkreisId);
if (!parsed || string.IsNullOrWhiteSpace(line[bundeslandIdIndex]) || int.Parse(line[bundeslandIdIndex]) == skippedBundeslandId)
{
// Increse i because net line is empty
i++;
continue;
}
Console.WriteLine("Process wahlkreis {0}", wahlkreisId);
var amountAllowedVoters = int.Parse(line[allowedVoterIndex]);
var amountVoters = int.Parse(line[voterIndex]);
var invalidErstimmen = int.Parse(line[invalidErststimmeIndex]);
var invalidZweitstimmen = int.Parse(line[invalidZweitstimmeIndex]);
GeneratePeopleForWahlkreis(context, election, wahlkreisId, amountAllowedVoters, amountVoters);
GenerateErststimmenVotes(context, electionId, wahlkreisId, invalidErstimmen, null);
GenerateZweitstimmenVotes(context, electionId, wahlkreisId, invalidZweitstimmen, null);
// Generate votes for parties
for (var k = startPartyColumnIndex; k < line.Count - 1; k += 4)
{
var partyName = parsedFile.ElementAt(partyNameLineIndex)[k].Trim();
var filteredPartyName = DataImportGeneral.PartyNameDictionary.ContainsKey(partyName)
? DataImportGeneral.PartyNameDictionary[partyName]
: partyName;
var partyId = partyDictionary[filteredPartyName];
// Erststimmen
int partyErststimmen;
var parsedErst = int.TryParse(line[k], out partyErststimmen);
if (!parsedErst)
{
partyErststimmen = 0;
}
// Calculate only if needed afterwards
var person = partyErststimmen == 0 ? null :
context.People.FirstOrDefault(
p =>
p.IsElectableCandidates.Any(
c => c.Election_Id == electionId && c.Wahlkreis_Id == wahlkreisId) &&
p.PartyAffiliations.Any(a => a.Election_Id == electionId && a.Party_Id == partyId));
// Hack because some candidates are missing
if (person == null && partyErststimmen > 0)
{
person = new Person
{
Lastname = GeneratedPeopleLastname
};
context.People.Add(person);
context.PartyAffiliations.Add(new PartyAffiliation
{
Election_Id = electionId,
Party_Id = partyId,
Person = person
});
context.IsElectableCandidates.Add(new IsElectableCandidate
{
Election_Id = electionId,
Wahlkreis_Id = wahlkreisId,
Person = person
});
}
GenerateErststimmenVotes(context, electionId, wahlkreisId, partyErststimmen, person);
// Zweitstimmen
int partyZweitstimmen;
var parsedZweit = int.TryParse(line[k + 2], out partyZweitstimmen);
if (!parsedZweit)
{
partyZweitstimmen = 0;
}
GenerateZweitstimmenVotes(context, electionId, wahlkreisId, partyZweitstimmen, partyId);
}
Console.WriteLine("");
}
context.SaveChanges();
}
}
public static void AddElection()
{
DataImportGeneral.AddElection(new DateTime(2013, 9, 22), 598);
}
public static void AddPartyAffiliations(string filename)
{
const int firstnameIndex = 1;
const int lastnameIndex = 2;
const int partynameIndex = 3;
var parsedFile = DataImportGeneral.ParseFile(filename);
const int startIndex = 1;
using (var context = new ElectionDBEntities())
{
var partyDictionary = context.Parties.ToDictionary(p => p.Name, p => p.Id);
for (var i = startIndex; i < parsedFile.Count; i++)
{
var line = parsedFile.ElementAt(i);
var result = GetTitleFirstnameLastnamePartynameFromLine(line);
var firstname = result.ElementAt(firstnameIndex);
var lastname = result.ElementAt(lastnameIndex);
var partyname = result.ElementAt(partynameIndex);
var partyId = partyDictionary[partyname];
// Hack because there are two persons with the same name that cannot be distinguished on a better way
Person existing;
if (firstname == "Stephan" && lastname == "Beyer")
{
var allExisting = context.People.Where(p => p.Firstname == firstname && p.Lastname == lastname);
existing = partyname == DataImportGeneral.Impartially ? allExisting.OrderBy(r => r.Id).First() : allExisting.OrderByDescending(r => r.Id).First();
}
else if (firstname == "Mario" && lastname == "Ertel")
{
var allExisting = context.People.Where(p => p.Firstname == firstname && p.Lastname == lastname);
existing = partyname == "NPD" ? allExisting.OrderBy(r => r.Id).First() : allExisting.OrderByDescending(r => r.Id).First();
}
else if (firstname == "Christian" && lastname == "Fischer")
{
var allExisting = context.People.Where(p => p.Firstname == firstname && p.Lastname == lastname);
existing = partyname == "NPD" ? allExisting.OrderBy(r => r.Id).First() : allExisting.OrderByDescending(r => r.Id).First();
}
else if (firstname == "Frank" && lastname == "Hofmann")
{
var allExisting = context.People.Where(p => p.Firstname == firstname && p.Lastname == lastname);
existing = partyname == "SPD" ? allExisting.OrderBy(r => r.Id).First() : allExisting.OrderByDescending(r => r.Id).First();
}
else if (firstname == "Andreas" && lastname == "Schwarz")
{
var allExisting = context.People.Where(p => p.Firstname == firstname && p.Lastname == lastname);
existing = partyname == "GRÜNE" ? allExisting.OrderBy(r => r.Id).First() : allExisting.OrderByDescending(r => r.Id).First();
}
else if (firstname == "Andreas" && lastname == "Weber")
{
var allExisting = context.People.Where(p => p.Firstname == firstname && p.Lastname == lastname);
existing = partyname == "BüSo" ? allExisting.OrderBy(r => r.Id).First() : allExisting.OrderByDescending(r => r.Id).First();
}
else if (firstname == "Andreas" && lastname == "Müller")
{
var allExisting = context.People.Where(p => p.Firstname == firstname && p.Lastname == lastname);
existing = int.Parse(line[2]) == 1964 ? allExisting.OrderBy(r => r.Id).First() : allExisting.OrderByDescending(r => r.Id).First();
}
else
{
existing = context.People.Single(p => p.Firstname == firstname && p.Lastname == lastname &&
(!p.PartyAffiliations.Any() ||
p.PartyAffiliations.Any(
a =>
a.Election_Id == DataImport2013.electionId &&
a.Party_Id == partyId)));
}
var entry = new PartyAffiliation
{
Election_Id = electionId,
Person_Id = existing.Id,
Party_Id = partyId
};
context.PartyAffiliations.Add(entry);
Console.WriteLine("Added person {0} to party {1} in election {2}", existing.Id, partyId, electionId);
}
context.SaveChanges();
}
}