Ejemplo n.º 1
0
        protected static HtmlAnchor CreateAdminPoliticianAnchor(DataRow politician,
                                                                string anchorText, string target = "politician")
        {
            var href = SecurePoliticianPage.GetUpdateIntroPageUrl(politician.PoliticianKey());

            if (VotePage.IsPublicPage)
            {
                href = UrlManager.GetAdminUri(href).ToString();
            }
            var a = new HtmlAnchor
            {
                HRef      = href,
                Target    = target,
                Title     = "Edit Links, Picture, Bio & Reasons",
                InnerHtml = anchorText
            };

            return(a);
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            var stateCode = Request.QueryString["state"];

            if (IsNullOrWhiteSpace(stateCode))
            {
                throw new VoteException("State code is missing.");
            }
            if (!StateCache.IsValidStateCode(stateCode))
            {
                throw new VoteException("State code is invalid.");
            }
            var includeMissing = GetQueryString("m") == "1";
            var includeWith    = GetQueryString("w") == "1";
            var fileDesc       = includeMissing
        ? includeWith ? ".all" : ".missing-incumbents"
        : ".with-incumbents";

            // get the data
            var table = Counties.GetOfficesForCsv(stateCode);

            // create the csv
            string csv;

            using (var ms = new MemoryStream())
            {
                var streamWriter = new StreamWriter(ms);
                var csvWriter    = new SimpleCsvWriter();

                // write headers
                csvWriter.AddField("State Code");
                csvWriter.AddField("County Name");
                csvWriter.AddField("County Code");
                csvWriter.AddField("Office Name");
                csvWriter.AddField("Office Key");
                csvWriter.AddField("Incumbent Name");
                csvWriter.AddField("Politician Key");
                csvWriter.AddField("Election Key");
                csvWriter.Write(streamWriter);

                foreach (var row in table.Rows.OfType <DataRow>()
                         .Where(r => !IsNullOrWhiteSpace(r.ElectionKey()) &&
                                (includeMissing && IsNullOrWhiteSpace(r.PoliticianKey()) ||
                                 includeWith && !IsNullOrWhiteSpace(r.PoliticianKey()))))
                {
                    csvWriter.AddField(row.StateCode());
                    csvWriter.AddField(row.County());
                    csvWriter.AddField(row.CountyCode());
                    csvWriter.AddField(Offices.FormatOfficeName(row));
                    csvWriter.AddField(row.OfficeKey() ?? Empty);
                    csvWriter.AddField(Politicians.FormatName(row));
                    csvWriter.AddField(row.PoliticianKey() ?? Empty);
                    csvWriter.AddField(row.ElectionKey() ?? Empty);
                    csvWriter.AddField(
                        row.ElectionKey() == null ?
                        Empty
              : $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetAdminFolderPageUrl("election", "election", row.ElectionKey()))}\",\"Election Report\")");
                    csvWriter.AddField(
                        row.ElectionKey() == null || row.OfficeKey() == null ?
                        Empty
              : $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetOfficeWinnerPageUrl(row.ElectionKey(), row.OfficeKey()))}\",\"Identify Winners\")");

                    csvWriter.Write(streamWriter);
                }
                streamWriter.Flush();
                ms.Position = 0;
                csv         = new StreamReader(ms).ReadToEnd();
            }

            // download
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition",
                               $"attachment;filename=\"county-offices-{stateCode}{fileDesc}.csv\"");
            Response.Write("\xfeff"); // BOM
            Response.Write(csv);
            Response.End();
        }
Ejemplo n.º 3
0
        protected void Page_Load(object sender, EventArgs e)
        {
            var stateCode = Request.QueryString["state"];

            if (IsNullOrWhiteSpace(stateCode))
            {
                throw new VoteException("State code is missing.");
            }
            if (!StateCache.IsValidStateCode(stateCode))
            {
                throw new VoteException("State code is invalid.");
            }
            var includeMissing = GetQueryString("m") == "1";
            var includeWith    = GetQueryString("w") == "1";
            var fileDesc       = includeMissing
        ? (includeWith ? ".all" : ".missing-incumbents")
        : ".with-incumbents";

            // get the data
            var rows = LocalDistricts.GetOfficesForCsv(stateCode).Rows.OfType <DataRow>()
                       .Where(r => !IsNullOrWhiteSpace(r.ElectionKey()) &&
                              (includeMissing && IsNullOrWhiteSpace(r.PoliticianKey()) ||
                               includeWith && !IsNullOrWhiteSpace(r.PoliticianKey())))
                       .ToList();

            // apply counties to the data
            var countiesForLocals =
                LocalIdsCodes.FindCountiesWithNames(stateCode, rows.Select(r => r.LocalKey()).Distinct());
            var data = rows.SelectMany(r => countiesForLocals[r.LocalKey()].Select(c =>
                                                                                   new
            {
                Row        = r,
                CountyCode = c.Value,
                County     = c.Text,
                AlsoIn     = countiesForLocals[r.LocalKey()]
                             .Where(o => o.Text != c.Text).Select(o => o.Text).ToArray()
            }))
                       .OrderBy(r => r.County)
                       .ThenBy(r => r.Row.LocalDistrict())
                       .ThenBy(r => r.Row.OfficeLine1())
                       .ThenBy(r => r.Row.OfficeLine2())
                       .ToArray();

            // create the csv
            string csv;

            using (var ms = new MemoryStream())
            {
                var streamWriter = new StreamWriter(ms);
                var csvWriter    = new SimpleCsvWriter();

                // write headers
                csvWriter.AddField("State Code");
                csvWriter.AddField("County Name");
                csvWriter.AddField("County Code");
                csvWriter.AddField("Also In");
                csvWriter.AddField("Local Name");
                csvWriter.AddField("Local Key");
                csvWriter.AddField("Office Name");
                csvWriter.AddField("Office Key");
                csvWriter.AddField("Incumbent Name");
                csvWriter.AddField("Politician Key");
                csvWriter.AddField("Election Key");
                csvWriter.Write(streamWriter);

                foreach (var row in data)
                {
                    csvWriter.AddField(row.Row.StateCode());
                    csvWriter.AddField(row.County);
                    csvWriter.AddField(row.CountyCode);
                    csvWriter.AddField(Join(", ", row.AlsoIn));
                    csvWriter.AddField(row.Row.LocalDistrict());
                    csvWriter.AddField(row.Row.LocalKey());
                    csvWriter.AddField(Offices.FormatOfficeName(row.Row));
                    csvWriter.AddField(row.Row.OfficeKey() ?? Empty);
                    csvWriter.AddField(Politicians.FormatName(row.Row));
                    csvWriter.AddField(row.Row.PoliticianKey() ?? Empty);
                    csvWriter.AddField(row.Row.ElectionKey() ?? Empty);
                    csvWriter.AddField(
                        row.Row.ElectionKey() == null ?
                        Empty
              : $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetAdminFolderPageUrl("election", "election", row.Row.ElectionKey()))}\",\"Election Report\")");
                    csvWriter.AddField(
                        row.Row.ElectionKey() == null || row.Row.OfficeKey() == null ?
                        Empty
              : $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetOfficeWinnerPageUrl(row.Row.ElectionKey(), row.Row.OfficeKey()))}\",\"Identify Winners\")");

                    csvWriter.Write(streamWriter);
                }
                streamWriter.Flush();
                ms.Position = 0;
                csv         = new StreamReader(ms).ReadToEnd();
            }

            // download
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition",
                               $"attachment;filename=\"local-offices-{stateCode}{fileDesc}.csv\"");
            Response.Write("\xfeff"); // BOM
            Response.Write(csv);
            Response.End();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            SimpleCsvWriter csvWriter;
            StreamWriter    streamWriter;

            void FillDataRows(IList <int> candidates, IList <int> offices, int ballotMeasures,
                              string description = null)
            {
                if (_HasElections)
                {
                    csvWriter.AddField(description.SafeString());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.President].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.President].BlankIfZero());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.USSenate].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.USSenate].BlankIfZero());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.USHouse].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.USHouse].BlankIfZero());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.Governor].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.Governor].BlankIfZero());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.StateExecutive].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.StateExecutive].BlankIfZero());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.StateSenate].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.StateSenate].BlankIfZero());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.StateHouse].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.StateHouse].BlankIfZero());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.County].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.County].BlankIfZero());
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(offices[(int)OfficeHeading.Local].BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(candidates[(int)OfficeHeading.Local].BlankIfZero());
                }

                if (_HasBallotMeasures)
                {
                    csvWriter.AddField(ballotMeasures.BlankIfZero());
                }

                var allOffices    = 0;
                var allCandidates = 0;

                for (var o = OfficeHeading.President; o < OfficeHeading.Count; o++)
                {
                    allOffices    += offices[(int)o];
                    allCandidates += candidates[(int)o];
                }

                if (_HasOffices)
                {
                    csvWriter.AddField(allOffices.BlankIfZero());
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField(allCandidates.BlankIfZero());
                }

                csvWriter.Write(streamWriter);
            }

            bool IsBallotMeasure(DataRow r)
            {
                return(r.ReferendumKey() != null);
            }

            if (!DateTime.TryParse(Request.QueryString["from"], out var fromDate))
            {
                fromDate = DateTime.MinValue;
            }
            if (!DateTime.TryParse(Request.QueryString["to"], out var toDate))
            {
                toDate = DateTime.MaxValue;
            }
            var reportType = Request.QueryString["type"].SafeString();

            _HasCandidates     = reportType.Contains("C");
            _HasOffices        = reportType.Contains("O");
            _HasBallotMeasures = reportType.Contains("B");
            _HasElections      = Request.QueryString["detail"] == "E";

            for (var i = 0; i < (int)OfficeHeading.Count; i++)
            {
                _ElectionOffices[i] = new List <string>();
            }

            // get data table from database
            var table = ElectionsPoliticians.GetOfficesAndCandidatesReportData(fromDate, toDate, 0)
                        .Rows.OfType <DataRow>().ToList();

            // create dummy entries so months with no elections will show
            var lowDate = fromDate == DateTime.MinValue
        ? table.Min(r => r.ElectionDate())
        : fromDate;
            var highDate = toDate == DateTime.MaxValue
        ? table.Max(r => r.ElectionDate())
        : toDate;
            var monthList = new List <DateTime>();

            while (lowDate <= highDate)
            {
                monthList.Add(lowDate);
                lowDate = lowDate.AddMonths(1);
            }

            var dummies = monthList.Select(m => new
            {
                row           = (DataRow)null,
                MonthYear     = m.ToString("yyyyMM"),
                MonthYearDesc = m.ToString("MMM yyyy"),
                Year          = m.ToString("yyyy"),
                OfficeHeading = OfficeHeading.Other
            }).ToList();

            var data = table
                       .Select(r => new
            {
                row           = r,
                MonthYear     = r.ElectionDate().ToString("yyyyMM"),
                MonthYearDesc = r.ElectionDate().ToString("MMM yyyy"),
                Year          = r.ElectionDate().ToString("yyyy"),
                OfficeHeading = GetOfficeHeading(r.OfficeClass(), r.AlternateOfficeClass())
            })
                       .Union(dummies)
                       .GroupBy(r => r.row == null ? r.MonthYear : r.row.ElectionKeyState())
                       .GroupBy(el => el.First().MonthYear)
                       .OrderBy(g => g.Key)
                       .GroupBy(m => m.First().First().Year)
                       .ToList();

            using (var ms = new MemoryStream())
            {
                streamWriter = new StreamWriter(ms);
                csvWriter    = new SimpleCsvWriter();

                // write CSV headings
                csvWriter.AddField("Date");
                if (_HasElections)
                {
                    csvWriter.AddField("Election Title");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("President Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("President Candidates");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("US Senate Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("US Senate Candidates");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("US House Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("US House Candidates");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("Governor Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("Governor Candidates");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("State Executive Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("State Executive Candidates");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("State Senate Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("State Senate Candidates");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("State House Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("State House Candidates");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("Countywide Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("Countywide Candidates");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("Local Offices");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("Local Candidates");
                }
                if (_HasBallotMeasures)
                {
                    csvWriter.AddField("Ballot Measures");
                }
                if (_HasOffices)
                {
                    csvWriter.AddField("TOTAL OFFICES");
                }
                if (_HasCandidates)
                {
                    csvWriter.AddField("TOTAL CANDIDATES");
                }
                csvWriter.Write(streamWriter);

                foreach (var year in data)
                {
                    foreach (var monthYear in year)
                    {
                        foreach (var election in monthYear)
                        {
                            for (var i = 0; i < _ElectionOfficeCount.Length; i++)
                            {
                                _ElectionOffices[i].Clear();
                            }
                            foreach (var candidate in election)
                            {
                                if (candidate.row != null)
                                {
                                    if (IsBallotMeasure(candidate.row))
                                    {
                                        _ElectionBallotMeasures++;
                                    }
                                    else
                                    {
                                        _ElectionCandidates[(int)candidate.OfficeHeading]++;
                                        _ElectionOffices[(int)candidate.OfficeHeading].Add(candidate.row.OfficeKey());
                                    }
                                }
                            }
                            for (var i = 0; i < _ElectionOfficeCount.Length; i++)
                            {
                                _ElectionOfficeCount[i] = _ElectionOffices[i].Distinct().Count();
                            }
                            if (_HasElections)
                            {
                                var row = election.First().row;
                                if (row != null)
                                {
                                    csvWriter.AddField(row.ElectionDate().ToString("M/d/yyyy"));
                                    var desc =
                                        $"=HYPERLINK(\"{UrlManager.GetAdminUri(GetAdminFolderPageUrl("election", "election", row.ElectionKeyState(), "complete", "1"))}\",\"{row.ElectionDescription()}\")";
                                    FillDataRows(_ElectionCandidates, _ElectionOfficeCount, _ElectionBallotMeasures,
                                                 desc);
                                }
                            }
                            RollCounters(_ElectionCandidates, _MonthCandidates);
                            RollCounters(_ElectionOfficeCount, _MonthOfficeCount);
                            RollBallotMeasures(ref _ElectionBallotMeasures, ref _MonthBallotMeasures);
                        }
                        csvWriter.AddField($"Total {monthYear.First().First().MonthYearDesc}");
                        FillDataRows(_MonthCandidates, _MonthOfficeCount, _MonthBallotMeasures);
                        RollCounters(_MonthCandidates, _YearCandidates);
                        RollCounters(_MonthOfficeCount, _YearOfficeCount);
                        RollBallotMeasures(ref _MonthBallotMeasures, ref _YearBallotMeasures);
                    }
                    csvWriter.Write(streamWriter);
                    csvWriter.AddField($"TOTAL YEAR {year.First().First().First().Year}");
                    FillDataRows(_YearCandidates, _YearOfficeCount, _YearBallotMeasures);
                    RollCounters(_YearCandidates, _ReportCandidates);
                    RollCounters(_YearOfficeCount, _ReportOfficeCount);
                    RollBallotMeasures(ref _YearBallotMeasures, ref _ReportBallotMeasures);
                    csvWriter.Write(streamWriter);
                }
                csvWriter.AddField("REPORT TOTAL");
                FillDataRows(_ReportCandidates, _ReportOfficeCount, _ReportBallotMeasures);

                streamWriter.Flush();
                ms.Position = 0;
                var csv = new StreamReader(ms).ReadToEnd();

                var reportDesc = String.Empty;
                if (_HasOffices)
                {
                    if (_HasCandidates)
                    {
                        reportDesc += "Offices & Candidates";
                    }
                    else
                    {
                        reportDesc += "Offices";
                    }
                }
                else if (_HasCandidates)
                {
                    reportDesc += "Candidates";
                }
                else if (_HasBallotMeasures)
                {
                    reportDesc += "Ballot Measures";
                }
                reportDesc += " in Elections";
                if (fromDate != DateTime.MinValue)
                {
                    if (toDate != DateTime.MaxValue)
                    {
                        reportDesc += $" {fromDate:M-d-yyyy} to {toDate:M-d-yyyy}";
                    }
                    else
                    {
                        reportDesc += $" after {fromDate:M-d-yyyy}";
                    }
                }
                else if (toDate != DateTime.MaxValue)
                {
                    reportDesc += $" before {toDate:M-d-yyyy}";
                }
                if (_HasElections)
                {
                    reportDesc += " with election detail";
                }

                // download
                Response.Clear();
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition",
                                   $"attachment;filename=\"{reportDesc}.csv\"");
                Response.Write("\xfeff"); // BOM
                Response.Write(csv);
                Response.End();
            }
        }