public void GetData(string electionKey, string officeKey, string questionKey = null)
            {
                int?questionId = null;

                if (int.TryParse(questionKey, out var id))
                {
                    questionId = id;
                }
                DataTable = VotePage.EnableIssueGroups
          ? ElectionsPoliticians.GetCompareCandidateIssuesNew3(electionKey, officeKey,
                                                               questionId)
          : ElectionsPoliticians.GetCompareCandidateIssuesNew(electionKey, officeKey,
                                                              questionId);
            }
        protected void Page_Load(object sender, EventArgs e)
        {
            Server.ScriptTimeout = 1800;
            var electionKey           = Request.QueryString["election"];
            var csvType               = Request.QueryString["type"];
            var includeCandidates     = csvType != "BM";
            var includeCandidateInfo  = csvType == "NA" || csvType == "WA";
            var includeAnswers        = csvType == "OA" || csvType == "WA";
            var includeBallotMeasures = csvType == "BM";

            if (IsNullOrWhiteSpace(electionKey))
            {
                throw new VoteException("Election key is missing.");
            }
            var electionDescription = Elections.GetElectionDesc(electionKey);

            if (IsNullOrWhiteSpace(electionDescription))
            {
                throw new VoteException("Election key is invalid.");
            }

            // make sure it's a valid filename
            var invalidFileChars = Path.GetInvalidFileNameChars();

            electionDescription = Regex.Replace(electionDescription, ".",
                                                match => new[] { ' ', ',', '"', '\'' }.Contains(match.Value[0]) ||
                                                invalidFileChars.Contains(match.Value[0])
            ? "_"
            : match.Value);
            electionDescription = Regex.Replace(electionDescription, "__+", "_");

            // get the data
            var table = includeBallotMeasures
        ? Referendums.GetElectionCsvReferendumData(electionKey)
        : ElectionsPoliticians.GetElectionCsvCandidateData(electionKey);

            // if we're including answers, get the answers data for each office in the election
            var qas     = new List <QuestionAndAnswer>();
            var columns = new List <dynamic>();

            if (includeAnswers)
            {
                var answers = new List <DataRow>();
                // get a list of all offices in the election
                var allOfficeKeys = table.Rows.OfType <DataRow>().Select(r => r.OfficeKey()).Distinct()
                                    .ToList();
                // collect all the answers
                foreach (var officeKey in allOfficeKeys)
                {
                    var oldAnswerCutoff =
                        ElectionsOffices.GetOldAnswerCutoffDate(electionKey, officeKey);
                    // the GroupBy is to eliminate duplicate answers if a question is in more than one issue
                    answers.AddRange(ElectionsPoliticians.GetCompareCandidateIssuesNew(electionKey, officeKey)
                                     .Rows.OfType <DataRow>()
                                     .GroupBy(r => new
                    {
                        PoliticianKey = r.PoliticianKey(),
                        QuestionKey   = r.QuestionKey(),
                        Sequence      = r.Sequence()
                    })
                                     .Select(g => g.First()));
                    // convert the answers to QuestionAndAnswer format
                    foreach (var p in answers.GroupBy(r => r.PoliticianKey()))
                    {
                        qas.AddRange(ResponsiveIssuesReport.SplitOutVideos(ResponsiveIssuesReport.GetQuestionAndAnswerList(p,
                                                                                                                           Politicians.GetData(p.Key)[0], false)).Where(qa => qa.ResponseDate > oldAnswerCutoff));
                    }
                }
                // analyze qas to determine which topic columns we need to include
                columns.AddRange(qas
                                 .GroupBy(qa =>
                                          new { qa.QuestionKey, IsYouTube = !IsNullOrWhiteSpace(qa.YouTubeUrl) }).Select(
                                     g => new
                {
                    g.Key.QuestionKey,
                    g.Key.IsYouTube,
                    Topic = $"{g.First().Question}{(g.Key.IsYouTube ? " Video" : Empty)}"
                }).OrderBy(q => q.Topic));
            }

            // create the csv
            string csv;

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

                // write headers
                csvWriter.AddField("Jurisdiction");
                csvWriter.AddField("State Code");
                if (csvType != "OK")
                {
                    csvWriter.AddField("County");
                    csvWriter.AddField("City or District");
                    csvWriter.AddField("Election Name");
                    csvWriter.AddField("Election Date");
                    csvWriter.AddField("VoteUSA Election Id");
                }

                if (includeCandidates && csvType != "OK")
                {
                    csvWriter.AddField("Office");
                    csvWriter.AddField("Office Class");
                    csvWriter.AddField("District");
                    csvWriter.AddField("VoteUSA Office Id");
                    csvWriter.AddField("Running Mate?");
                    csvWriter.AddField("Candidate");
                    csvWriter.AddField("First Name");
                    csvWriter.AddField("Middle Name");
                    csvWriter.AddField("Nickname");
                    csvWriter.AddField("Last Name");
                    csvWriter.AddField("Suffix");
                    csvWriter.AddField("Party");
                    csvWriter.AddField("VoteUSA Id");
                }

                if (csvType == "OK")
                {
                    csvWriter.AddField("County Code");
                    csvWriter.AddField("County");
                    csvWriter.AddField("Local Key");
                    csvWriter.AddField("Local Name");
                    csvWriter.AddField("Election Key");
                    csvWriter.AddField("Office Key");
                    csvWriter.AddField("Office");
                    csvWriter.AddField("Politician Key");
                    csvWriter.AddField("Politician Password");
                    csvWriter.AddField("Candidate");
                    csvWriter.AddField("Party Code");
                    csvWriter.AddField("Ad Enabled");
                    csvWriter.AddField("YouTube Video Url");
                    csvWriter.AddField("YouTube Channel or Playlist Url");
                    csvWriter.AddField("Compare Candidates Url");
                    csvWriter.AddField("Type");
                    csvWriter.AddField("Date");
                    csvWriter.AddField("Amount");
                    csvWriter.AddField("Email");
                    csvWriter.AddField("Banner Ad Url");
                }

                if (includeCandidateInfo)
                {
                    csvWriter.AddField("Intro Url");
                    csvWriter.AddField("Photo100 Url");
                    csvWriter.AddField("Photo200 Url");
                    csvWriter.AddField("Photo300 Url");
                    csvWriter.AddField("Postal Street Address");
                    csvWriter.AddField("Postal City, State Zip");
                    csvWriter.AddField("Phone");
                    csvWriter.AddField("Email");
                    csvWriter.AddField("Date of Birth");
                }

                if (!includeAnswers && !includeBallotMeasures && csvType != "OK")
                {
                    csvWriter.AddField("General Philosophy");
                    csvWriter.AddField("Personal and Family");
                    csvWriter.AddField("Education");
                    csvWriter.AddField("Profession");
                    csvWriter.AddField("Military");
                    csvWriter.AddField("Civic");
                    csvWriter.AddField("Political Experience");
                    csvWriter.AddField("Religious Affiliation");
                    csvWriter.AddField("Accomplishment and Awards");
                }

                if (includeCandidateInfo)
                {
                    csvWriter.AddField("Website Url");
                    csvWriter.AddField("Facebook Url");
                    csvWriter.AddField("YouTube Url");
                    csvWriter.AddField("Flickr Url");
                    csvWriter.AddField("Twitter Url");
                    csvWriter.AddField("RSS Feed Url");
                    csvWriter.AddField("Wikipedia Url");
                    csvWriter.AddField("BallotPedia Url");
                    csvWriter.AddField("Vimeo Url");
                    csvWriter.AddField("Google+ Url");
                    csvWriter.AddField("LinkedIn Url");
                    csvWriter.AddField("Pinterest Url");
                    csvWriter.AddField("Blogger Url");
                    csvWriter.AddField("Podcast Url");
                    csvWriter.AddField("Instagram Url");
                    csvWriter.AddField("GoFundMe Url");
                    csvWriter.AddField("Crowdpac Url");
                }

                if (includeAnswers)
                {
                    foreach (var column in columns)
                    {
                        csvWriter.AddField(column.Topic);
                    }
                }

                if (includeBallotMeasures)
                {
                    csvWriter.AddField("Ballot Measure Title");
                    csvWriter.AddField("Ballot Measure Description");
                    csvWriter.AddField("Ballot Measure Detail");
                    csvWriter.AddField("Ballot Measure Detail URL");
                    csvWriter.AddField("Ballot Measure Full Text");
                    csvWriter.AddField("Ballot Measure Full Text URL");
                    csvWriter.AddField("Ballot Measure Passed");
                }

                csvWriter.Write(streamWriter);

                var stateCode = Elections.GetStateCodeFromKey(electionKey);

                // do a first pass to get counties for all locals
                var allLocals = new List <string>();
                foreach (var row in table.Rows.Cast <DataRow>())
                {
                    if (!IsNullOrWhiteSpace(row.LocalKey()))
                    {
                        allLocals.Add(row.LocalKey());
                    }
                }

                var countiesForLocals =
                    LocalIdsCodes.FindCountiesWithNames(stateCode, allLocals.Distinct());

                var rows = table.Rows.Cast <DataRow>();

                if (csvType == "OK")
                {
                    rows = rows.OrderBy(r => r.OfficeLevel())
                           //.ThenBy(r => r.DistrictCode())
                           //.ThenBy(r => r.OfficeOrderWithinLevel())
                           //.ThenBy(r => r.OfficeLine1())
                           //.ThenBy(r => r.OfficeLine2())
                           .ThenBy(r => Offices.FormatOfficeName(r),
                                   MixedNumericComparer.Instance)
                           .ThenBy(r => r.OrderOnBallot())
                           .ThenBy(r => r.PoliticianKey(), StringComparer.OrdinalIgnoreCase)
                           .ThenBy(r => r.IsRunningMate());
                }

                foreach (var row in rows)
                {
                    string jurisdiction;
                    var    politicianKey = Empty;
                    if (includeBallotMeasures)
                    {
                        if (!IsNullOrWhiteSpace(row.LocalKey()))
                        {
                            jurisdiction = "Local";
                        }
                        else if (!IsNullOrWhiteSpace(row.CountyCode()))
                        {
                            jurisdiction = "County";
                        }
                        else
                        {
                            jurisdiction = "State";
                        }
                    }
                    else
                    {
                        politicianKey = row.IsRunningMate()
              ? row.RunningMateKey()
              : row.PoliticianKey();
                        switch (Offices.GetElectoralClass(row.OfficeClass()))
                        {
                        case ElectoralClass.USPresident:
                        case ElectoralClass.USSenate:
                        case ElectoralClass.USHouse:
                            jurisdiction = "Federal";
                            break;

                        case ElectoralClass.USGovernors:
                        case ElectoralClass.State:
                            jurisdiction = "State";
                            break;

                        case ElectoralClass.County:
                            jurisdiction = "County";
                            break;

                        case ElectoralClass.Local:
                            jurisdiction = "Local";
                            break;

                        default:
                            jurisdiction = Empty;
                            break;
                        }
                    }

                    var photo100Url = Empty;
                    if (includeCandidateInfo)
                    {
                        var qsc100 = new QueryStringCollection
                        {
                            {
                                "id", politicianKey
                            },
                            {
                                "Col", "Headshot100"
                            }
                        };
                        var photo100Uri = UrlManager.GetSiteUri("image.aspx", qsc100);
                        photo100Url = new UriBuilder(photo100Uri)
                        {
                            Scheme = Uri.UriSchemeHttps,
                            Host   = UrlManager.GetCanonicalLiveHostName(photo100Uri.Host),
                            Port   = 443
                        }.Uri.ToString();
                    }

                    var photo200Url = Empty;
                    if (includeCandidateInfo)
                    {
                        var qsc200 = new QueryStringCollection
                        {
                            { "id", politicianKey }, { "Col", "Profile200" }
                        };
                        var photo200Uri = UrlManager.GetSiteUri("image.aspx", qsc200);
                        photo200Url = new UriBuilder(photo200Uri)
                        {
                            Scheme = Uri.UriSchemeHttps,
                            Host   = UrlManager.GetCanonicalLiveHostName(photo200Uri.Host),
                            Port   = 443
                        }.Uri.ToString();
                    }

                    var photo300Url = Empty;
                    if (includeCandidateInfo)
                    {
                        var qsc300 = new QueryStringCollection
                        {
                            { "id", politicianKey }, { "Col", "Profile300" }
                        };
                        var photo300Uri = UrlManager.GetSiteUri("image.aspx", qsc300);
                        photo300Url = new UriBuilder(photo300Uri)
                        {
                            Scheme = Uri.UriSchemeHttps,
                            Host   = UrlManager.GetCanonicalLiveHostName(photo300Uri.Host),
                            Port   = 443
                        }.Uri.ToString();
                    }

                    var introUrl = Empty;
                    if (includeCandidateInfo)
                    {
                        var introUri = UrlManager.GetIntroPageUri(politicianKey);
                        introUrl = new UriBuilder(introUri)
                        {
                            Scheme = Uri.UriSchemeHttps,
                            Host   = UrlManager.GetCanonicalLiveHostName(introUri.Host),
                            Port   = 443
                        }.Uri.ToString();
                    }

                    var district = Empty;
                    if (includeCandidates)
                    {
                        if (int.TryParse(row.DistrictCode(), out var districtNumber))
                        {
                            district = districtNumber.ToString(CultureInfo.InvariantCulture);
                        }
                    }

                    // convert to simple name if national
                    var partyName = Empty;
                    if (includeCandidates)
                    {
                        partyName = Parties.GetNationalPartyDescription(row.PartyCode(),
                                                                        row.PartyName());
                    }

                    var county = IsNullOrWhiteSpace(row.County()) ? Empty : row.County();
                    var local  = Empty;
                    if (!IsNullOrWhiteSpace(row.LocalKey()))
                    {
                        local  = row.LocalDistrict();
                        county = Join(", ", countiesForLocals[row.LocalKey()].Select(c => c.Text));
                    }

                    csvWriter.AddField(jurisdiction);
                    csvWriter.AddField(stateCode);
                    if (csvType != "OK")
                    {
                        csvWriter.AddField(county);
                        csvWriter.AddField(local);
                        csvWriter.AddField(row.ElectionDescription());
                        csvWriter.AddField(row.ElectionDate().ToString("d"));
                        csvWriter.AddField(row.ElectionKey());
                    }

                    if (includeCandidates && csvType != "OK")
                    {
                        csvWriter.AddField(Offices.FormatOfficeName(row));
                        csvWriter.AddField(Offices.GetOfficeClassShortDescriptionExtended(row));
                        csvWriter.AddField(district);
                        csvWriter.AddField(row.OfficeKey());
                        csvWriter.AddField(row.IsRunningMate() ? row.PoliticianKey() : Empty);
                        csvWriter.AddField(Politicians.FormatName(row));
                        csvWriter.AddField(row.FirstName());
                        csvWriter.AddField(row.MiddleName());
                        csvWriter.AddField(row.Nickname());
                        csvWriter.AddField(row.LastName());
                        csvWriter.AddField(row.Suffix());
                        csvWriter.AddField(partyName);
                        csvWriter.AddField(politicianKey);
                    }

                    if (csvType == "OK")
                    {
                        var youTubeVideoUrl     = Empty;
                        var youTubeAdWebAddress = row.AdUrl();
                        if (!IsNullOrWhiteSpace(youTubeAdWebAddress))
                        {
                            youTubeVideoUrl = youTubeAdWebAddress.IsValidYouTubeVideoUrl()
                ? youTubeAdWebAddress
                : "channel or playlist";
                        }

                        var adEnabled = Empty;
                        if (!IsNullOrWhiteSpace(row.AdType()))
                        {
                            adEnabled = row.AdEnabled() ? "E" : "D";
                        }

                        var compareUrl =
                            UrlManager.GetCompareCandidatesPageUri(row.ElectionKey(), row.OfficeKey()) +
                            $"&ad={politicianKey}";

                        csvWriter.AddField(row.CountyCode());
                        csvWriter.AddField(row.County().SafeString());
                        csvWriter.AddField(row.LocalKey());
                        csvWriter.AddField(row.LocalDistrict().SafeString());
                        csvWriter.AddField(row.ElectionKey());
                        csvWriter.AddField(row.OfficeKey());
                        csvWriter.AddField(Offices.FormatOfficeName(row.OfficeLine1(),
                                                                    row.OfficeLine2(), row.OfficeKey()));
                        csvWriter.AddField(politicianKey);
                        csvWriter.AddField(row.Password());
                        csvWriter.AddField(Politicians.FormatName(row));
                        csvWriter.AddField(row.PartyCode().SafeString());
                        csvWriter.AddField(adEnabled);
                        csvWriter.AddField(youTubeVideoUrl);
                        csvWriter.AddField(row.YouTubeWebAddress());
                        csvWriter.AddField($"=HYPERLINK(\"{compareUrl}\",\"{compareUrl}\")");
                        csvWriter.AddField(Empty);
                        csvWriter.AddField(Empty);
                        csvWriter.AddField(Empty);
                        csvWriter.AddField(Empty);
                        csvWriter.AddField(Empty);
                    }

                    if (includeCandidateInfo)
                    {
                        csvWriter.AddField(introUrl);
                        csvWriter.AddField(photo100Url);
                        csvWriter.AddField(photo200Url);
                        csvWriter.AddField(photo300Url);
                        csvWriter.AddField(row.PublicAddress());
                        csvWriter.AddField(row.PublicCityStateZip());
                        csvWriter.AddField(row.PublicPhone());
                        csvWriter.AddField(row.PublicEmail());
                        csvWriter.AddField(row.DateOfBirth().ToString("d"));
                    }

                    if (!includeAnswers && !includeBallotMeasures && csvType != "OK")
                    {
                        csvWriter.AddField(row.GeneralStatement().SafeString());
                        csvWriter.AddField(row.Personal().SafeString());
                        csvWriter.AddField(row.Education().SafeString());
                        csvWriter.AddField(row.Profession().SafeString());
                        csvWriter.AddField(row.Military().SafeString());
                        csvWriter.AddField(row.Civic().SafeString());
                        csvWriter.AddField(row.Political().SafeString());
                        csvWriter.AddField(row.Religion().SafeString());
                        csvWriter.AddField(row.Accomplishments().SafeString());
                    }

                    if (includeCandidateInfo)
                    {
                        csvWriter.AddField(row.PublicWebAddress());
                        csvWriter.AddField(row.FacebookWebAddress());
                        csvWriter.AddField(row.YouTubeWebAddress());
                        csvWriter.AddField(row.FlickrWebAddress());
                        csvWriter.AddField(row.TwitterWebAddress());
                        csvWriter.AddField(row.RssFeedWebAddress());
                        csvWriter.AddField(row.WikipediaWebAddress());
                        csvWriter.AddField(row.BallotPediaWebAddress());
                        csvWriter.AddField(row.VimeoWebAddress());
                        csvWriter.AddField(row.GooglePlusWebAddress());
                        csvWriter.AddField(row.LinkedInWebAddress());
                        csvWriter.AddField(row.PinterestWebAddress());
                        csvWriter.AddField(row.BloggerWebAddress());
                        csvWriter.AddField(row.PodcastWebAddress());
                        csvWriter.AddField(row.WebstagramWebAddress());
                        csvWriter.AddField(row.GoFundMeWebAddress());
                        csvWriter.AddField(row.CrowdpacWebAddress());
                    }

                    if (includeAnswers)
                    {
                        var data = qas.Where(qa => qa.PoliticianKey == politicianKey)
                                   .OrderByDescending(qa => qa.ResponseDate)
                                   .ToList();
                        foreach (var column in columns)
                        {
                            var response = data.FirstOrDefault(d => d.QuestionKey == column.QuestionKey &&
                                                               d.HasVideo == column.IsYouTube);
                            var field = Empty;
                            if (response != null)
                            {
                                if (response.HasVideo)
                                {
                                    field = response.YouTubeUrl;
                                }
                                else
                                {
                                    field = $"{response.Answer}" +
                                            $"\n\n{(IsNullOrWhiteSpace(response.AnswerSource) ? Empty : $" Source: {response.AnswerSource}")}" +
                                            $" ({response.AnswerDate:M/d/yyyy})";
                                }
                            }
                            csvWriter.AddField(field);
                        }
                    }

                    if (includeBallotMeasures)
                    {
                        csvWriter.AddField(row.ReferendumTitle());
                        csvWriter.AddField(row.ReferendumDescription());
                        csvWriter.AddField(row.ReferendumDetail());
                        csvWriter.AddField(row.ReferendumDetailUrl());
                        csvWriter.AddField(row.ReferendumFullText());
                        csvWriter.AddField(row.ReferendumFullTextUrl());
                        csvWriter.AddField(row.IsPassed() ? "Y" : Empty);
                    }

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

            // download
            var filename = electionDescription;

            switch (csvType)
            {
            case "NA":
                filename += " without topics";
                break;

            case "OA":
                filename += " - topics only";
                break;

            case "WA":
                filename += " - all data including topics";
                break;

            case "OK":
                filename += " - names and keys only";
                break;

            case "BM":
                filename += " - Ballot Measures";
                break;
            }
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition",
                               $"attachment;filename=\"{filename}.csv\"");
            Response.Write("\xfeff"); // BOM
            Response.Write(csv);
            Response.End();
        }