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 AddAdditionalParties()
{
var additional = new List<string> { "DVU", "ADM", "Freie Union", "Christliche Mitte", "ZENTRUM", DataImportGeneral.Impartially };
using (var context = new ElectionDBEntities())
{
foreach (var entry in additional)
{
context.Parties.Add(new Party
{
Name = entry
});
}
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();
}
}
// Quite similar to GenerateVotesFor2013. High number of clones!
public static void GenerateVotesFor2009(string filename)
{
const int electionId = 1;
const int partyNameLineIndex = 2;
const int startLineIndex = 4;
const int wahlkreisIdIndex = 0;
const int allowedVoterIndex = 3;
const int voterIndex = 4;
const int invalidErststimmeIndex = 5;
const int invalidZweitstimmeIndex = 6;
const int startPartyColumnIndex = 9;
const int skippedBundeslandId = 900;
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 || wahlkreisId >= skippedBundeslandId)
{
// Increse i because net line is empty
i++;
continue;
}
Console.WriteLine("Process wahlkreis {0}", wahlkreisId);
var amountAllowedVoters = int.Parse(line[allowedVoterIndex].Replace(" ", ""));
var amountVoters = int.Parse(line[voterIndex].Replace(" ", ""));
var invalidErstimmen = int.Parse(line[invalidErststimmeIndex].Replace(" ", ""));
var invalidZweitstimmen = int.Parse(line[invalidZweitstimmeIndex].Replace(" ", ""));
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; k++)
{
var partyName = parsedFile.ElementAt(partyNameLineIndex)[k].Trim();
var filteredPartyName = DataImportGeneral.PartyNameDictionary.ContainsKey(partyName)
? DataImportGeneral.PartyNameDictionary[partyName]
: partyName;
var partyId = partyDictionary[filteredPartyName];
var erststimme = parsedFile.ElementAt(partyNameLineIndex + 1)[k].Trim().Equals("Erststimmen");
if (erststimme)
{
// Erststimmen
int partyErststimmen;
var parsedErst = int.TryParse(line[k].Replace(" ", ""), 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);
}
else
{
// Zweitstimmen
int partyZweitstimmen;
var parsedZweit = int.TryParse(line[k].Replace(" ", ""), out partyZweitstimmen);
if (!parsedZweit)
{
partyZweitstimmen = 0;
}
GenerateZweitstimmenVotes(context, electionId, wahlkreisId, partyZweitstimmen, partyId);
}
}
Console.WriteLine("");
}
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 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 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();
}
}
public Token BuildToken(string tokenString, string ip)
{
if (ip == null)
{
throw new Exception("Unknown IP-Address.");
}
var delaySeconds = CheckHandleRequest(tokenString, ip);
if (delaySeconds > 0)
{
// to many invalid requests. Used to handle brute force attacks
throw new PublicException(
string.Format(
"Sie haben bereits zu viele ungültige Token abgegeben.\n" +
"Sie können ein neues Token erst wieder in {0} Sekunden ({1:hh:mm:ss}) abgeben.",
delaySeconds, DateTime.Now.AddSeconds(delaySeconds)));
}
var token = new Token(tokenString);
if (!token.IsValid())
{
// save invalid token request
using (var context = new ElectionDBEntities())
{
context.InvalidTokenRequests.Add(new InvalidTokenRequest
{
IP = ip,
Token = tokenString,
Timestamp = DateTime.Now
});
context.SaveChanges();
}
throw new PublicException(string.Format("Das Token '{0}' ist ungültig.", tokenString));
}
if (!ReservedToken.ContainsKey(token))
{
using (var context = new ElectionDBEntities())
{
var existing = context.UsedTokens.SingleOrDefault(t => t.TokenString.Equals(tokenString));
// new token requested
if (existing != null)
{
throw new PublicException(string.Format(
"Das Token '{0}' wurde bereits genutzt. Sie dürfen es daher nicht noch einmal verwenden.", existing.TokenString));
}
// add new token to database
context.UsedTokens.Add(new UsedToken
{
TokenString = tokenString
});
context.SaveChanges();
}
ReservedToken.Add(token, ip);
}
else
{
// token for actual voting requested
var fromIp = ReservedToken[token];
if (!ip.Equals(fromIp))
{
throw new Exception("Wrong IP-Address detected.");
}
}
return token;
}
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 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 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 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 bool PerformVote(ElectionVoteViewModel model, string ip)
{
var token = _tokenHandler.BuildToken(model.TokenString, ip);
var wahlkreis = GetWahlkreis(token.GetWahlkreisId());
using (var context = new ElectionDBEntities())
{
//Validate voted person and party
var electablePeople = GetElectablePeople(context, token.GetElectionId(), token.GetWahlkreisId());
var electableParties = GetElectableParties(context, token.GetElectionId(), wahlkreis.BundeslandId);
if (model.VotedPersonId > 0 && !electablePeople.Select(p => p.Id).Contains(model.VotedPersonId ?? -1))
{
throw new PublicException(
string.Format(
"Die Person mit Id {0} darf in diesem Wahlkreis bei dieser Wahl nicht gewählt werden.",
model.VotedPersonId));
}
if (model.VotedPartyId > 0 && !electableParties.Select(p => p.Id).Contains(model.VotedPartyId ?? -1))
{
throw new PublicException(
string.Format(
"Die Partei mit Id {0} darf in diesem Wahlkreis bei dieser Wahl nicht gewählt werden.",
model.VotedPartyId));
}
context.Erststimmes.Add(new Erststimme
{
Election_Id = token.GetElectionId(),
Wahlkreis_Id = token.GetWahlkreisId(),
Person_Id = model.VotedPersonId > 0 ? model.VotedPersonId : null,
});
context.Zweitstimmes.Add(new Zweitstimme
{
Election_Id = token.GetElectionId(),
Wahlkreis_Id = token.GetWahlkreisId(),
Party_Id = model.VotedPartyId > 0 ? model.VotedPartyId : null
});
context.SaveChanges();
_tokenHandler.FinishedToken(token);
}
return true;
}