public IEnumerable <RollCallVote> GetRollCallVotes(string directoryRoot, string congress, string session)
{
List <RollCallVote> rollCallVotes = new List <RollCallVote>();
string path = Path.Combine(directoryRoot, "data");
DirectoryInfo dataDirectory = new DirectoryInfo(path);
var congressDirectories = dataDirectory.EnumerateDirectories();
var matchingCongressDirectory = congressDirectories.FirstOrDefault(d => d.Name.Equals(congress, StringComparison.InvariantCultureIgnoreCase));
if (matchingCongressDirectory == null)
{
return(default(IEnumerable <RollCallVote>));
}
var sessionDirectories = matchingCongressDirectory.GetDirectories();
var matchingSessionDirectory = sessionDirectories.FirstOrDefault(d => d.Name.Equals(session, StringComparison.InvariantCultureIgnoreCase));
if (matchingSessionDirectory == null)
{
return(default(IEnumerable <RollCallVote>));
}
var rollCallVoteFiles = Directory.EnumerateFiles(matchingSessionDirectory.FullName)
.Where(f => !f.EndsWith("summary.xml"));
foreach (var rollCallVoteFile in rollCallVoteFiles)
{
using (StreamReader reader = new StreamReader(rollCallVoteFile))
{
RollCallVote vote = (RollCallVote)rollCallVoteSerializer.Deserialize(reader);
rollCallVotes.Add(vote);
}
}
return(rollCallVotes);
}
private static void ProcessRollCallVote(Dictionary <string, int> legislativeItems, RollCallVote vote, SqlConnection connection, Dictionary <string, int> senators)
{
if (!legislativeItems.TryGetValue(vote.VoteNumber, out int legislativeItemID))
{
legislativeItemID = -1;
}
Dictionary <int, (int Id, int SenatorId, int legislativeItemID, string VoteCast)> votesBySenatorId =
connection.Query <(int Id, int SenatorId, int legislativeItemID, string VoteCast)> ($@"
SELECT * FROM dbo.Votes WHERE LegislativeItemID = @LegislativeItemID
", new { LegislativeItemID = legislativeItemID }).ToDictionary(v => v.SenatorId, v => v);
var stopwatch = Stopwatch.StartNew();
foreach (Member member in vote.Members.MemberElements)
{
ProcessMember(senators, member, connection, legislativeItemID, votesBySenatorId);
}
stopwatch.Stop();
Console.WriteLine($"Inserted ({stopwatch.ElapsedMilliseconds}) votes for ({vote.VoteNumber}), session: {vote.Congress}|{vote.Session}");
}
private static void InsertLegislativeItem(SqlConnection connection, int currentSessionId, RollCallVote vote)
{
using SqlCommand command = connection.CreateCommand();
command.CommandText = $@"
BEGIN TRAN t1;
UPDATE dbo.LegislativeItems SET Title = @Title, VoteDate = @VoteDate, ModifyDate = @ModifyDate, VoteQuestionText = @VoteQuestionText,
VoteDocumentText = @VoteDocumentText, MajorityRequirement = @MajorityRequirement, DocumentCongress = @DocumentCongress,
DocumentType = @DocumentType, DocumentNumber = @DocumentNumber, DocumentName = @DocumentName, DocumentTitle = @DocumentTitle,
DocumentShortTitle = @DocumentShortTitle, AmendmentNumber = @AmendmentNumber, AmendmentToDocumentNumber = @AmendmentToDocumentNumber,
AmendmentPurpose = @AmendmentPurpose, Issue = @Issue, IssueLink = @IssueLink, Question = @Question, Result = @Result, YeaCount = @YeaCount,
NayCount = @NayCount, PresentCount = @PresentCount, AbsentCount = @AbsentCount, TieBreakerByWhom = @TieBreakerByWhom, TieBreakerVote = @TieBreakerVote
WHERE SessionID = @SessionID and VoteNumber = @VoteNumber
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO dbo.LegislativeItems(SessionId, Title, VoteNumber, VoteDate, ModifyDate, VoteQuestionText, VoteDocumentText,
MajorityRequirement, DocumentCongress, DocumentType, DocumentNumber, DocumentName, DocumentTitle, DocumentShortTitle,
AmendmentNumber, AmendmentToDocumentNumber, AmendmentPurpose, Issue, IssueLink, Question, Result, YeaCount, NayCount,
PresentCount, AbsentCount, TieBreakerByWhom, TieBreakerVote)
VALUES (@SessionId, @Title, @VoteNumber, @VoteDate, @ModifyDate, @VoteQuestionText,
@VoteDocumentText, @MajorityRequirement, @DocumentCongress, @DocumentType, @DocumentNumber, @DocumentName,
@DocumentTitle, @DocumentShortTitle, @AmendmentNumber, @AmendmentToDocumentNumber, @AmendmentPurpose, @Issue,
@IssueLink, @Question, @Result, @YeaCount, @NayCount, @PresentCount, @AbsentCount, @TieBreakerByWhom, @TieBreakerVote)
END
COMMIT TRAN t1;
";
command.Parameters.AddWithValue("@SessionID", currentSessionId);
command.Parameters.AddWithValue("@Title", vote.VoteTitle);
command.Parameters.AddWithValue("@VoteNumber", vote.VoteNumber);
command.Parameters.AddWithValue("@VoteDate", DateTime.TryParse(vote.VoteDate, out DateTime voteDate) ? voteDate : (object)DBNull.Value);
//command.Parameters.AddWithValue("@ModifyDate", vote.ModifyDate);
command.Parameters.AddWithValue("@ModifyDate", DBNull.Value);
string questionText = vote.VoteQuestionText?.Length > 500 ? vote.VoteQuestionText.Substring(0, 500) : vote.VoteQuestionText;
command.Parameters.AddWithValue("@VoteQuestionText", questionText);
command.Parameters.AddWithValue("@VoteDocumentText", vote.VoteDocumentText);
command.Parameters.AddWithValue("@MajorityRequirement", vote.MajorityRequirement);
//command.Parameters.AddWithValue("@DocumentCongress", vote.Document.Congress);
command.Parameters.AddWithValue("@DocumentCongress", DBNull.Value);
command.Parameters.AddWithValue("@DocumentType", vote.Document?.Type ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@DocumentNumber", vote.Document?.Number ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@DocumentName", vote.Document?.Name ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@DocumentTitle", vote.Document?.Title ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@DocumentShortTitle", vote.Document?.ShortTitle ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@AmendmentNumber", vote.Amendment?.Number ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@AmendmentToDocumentNumber", vote.Amendment?.ToDocumentNumber ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@AmendmentPurpose", vote.Amendment?.Purpose ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@Issue", DBNull.Value);
command.Parameters.AddWithValue("@IssueLink", DBNull.Value);
command.Parameters.AddWithValue("@Question", vote.Question);
string voteResult = vote.VoteResult?.Length > 250 ? vote.VoteResult.Substring(0, 250) : vote.VoteResult;
command.Parameters.AddWithValue("@Result", voteResult);
command.Parameters.AddWithValue("@YeaCount", vote.Count.Yeas);
command.Parameters.AddWithValue("@NayCount", vote.Count.Nays);
command.Parameters.AddWithValue("@PresentCount", vote.Count.Present);
command.Parameters.AddWithValue("@AbsentCount", vote.Count.Absent);
command.Parameters.AddWithValue("@TieBreakerByWhom", vote.TieBreaker.ByWhom);
command.Parameters.AddWithValue("@TieBreakerVote", vote.TieBreaker.TieBreakerVote);
Console.WriteLine($"Insert/update LegItem : session: {currentSessionId} voteNum: {vote.VoteNumber}");
int result = command.ExecuteNonQuery();
}
