$text = "Just these committees";
if (isset($_POST["search_committees"])) {
$text = htmlspecialchars($_POST["search_committees"]);
}
echo "<div class=\"left\">";
echo "<input type=\"hidden\" id=\"match_committee\" name=\"match_committee\" value=\"no\">";
echo "<input type=\"text\" id=\"search_committees\" name=\"search_committees\" value=\"{$text}\" onkeyup=\"fill_committee_list(event);\" onFocus=\"document.getElementById('search_comms').checked=true; if(this.value == 'Just these committees') {this.value = '';}\" onBlur=\"if(this.value == '') {this.value = 'Just these committees';}\" class=\"font_input input_border caps_text1\" alt=\"Just These Committees\">";
echo "<div id=\"committees\" class=\"input_border caps_search_dropbox\"></div>";
echo "</div>";
echo "<hr class=\"caps_hr1\">";
?>
<!-- Dates -->
<h2 class="clear_both font_title caps_option_title">Dates:
<?php
display_tooltip("Search contributions by the date range or election cycle in which they were made.", 20, -20, 160, "right");
echo "</h2>";
$checked = "";
if (isset($_POST["date_select"])) {
if ($_POST["date_select"] == "all") {
$checked = "checked";
}
} else {
$checked = "checked";
}
# This is the default option for this radio button
echo "<input type=\"radio\" id=\"all_dates\" name=\"date_select\" value=\"all\" class=\"clear_both left caps_radio1\" {$checked} alt=\"All Dates and Election Cycles\">";
echo "<label for=\"all_dates\" class=\"font_input caps_label1\">All dates and election cycles</label>";
$checked = "";
if (isset($_POST["date_select"])) {
if ($_POST["date_select"] == "range") {
function display_data($parse_data)
{
global $web_db;
# Set this variable to control the maximum number of records that the download csv file is available.
$max_download_records = 150000;
$where = $parse_data[0];
$summary_type = $parse_data[1];
if ($where == "") {
echo "You have not entered any search data, please select a criteria on the side.";
} else {
$search_join = "";
if (strpos($where, "contributions_search_donors") !== false) {
$search_join .= "INNER JOIN contributions_search_donors ON(contributions_search.id = contributions_search_donors.id) ";
}
if (strpos($where, "smry_candidates") !== false) {
$search_join .= "INNER JOIN smry_candidates USING(MapLightCandidateNameID) ";
}
if (strpos($where, "smry_offices") !== false) {
$search_join .= "INNER JOIN smry_offices USING(MapLightCandidateOfficeID) ";
}
if (strpos($where, "smry_committees") !== false) {
$search_join .= "INNER JOIN smry_committees USING(MapLightCommitteeID) ";
}
if (strpos($where, "smry_propositions") !== false) {
$search_join .= "INNER JOIN smry_propositions USING(PropositionID) ";
}
if (isset($_POST["debug"])) {
if ($_GET["debug"] == "y") {
echo "<b>totals</b>:<br>SELECT COUNT(*) AS records, SUM(TransactionAmount) AS total FROM(SELECT DISTINCT ContributionID, TransactionAmount FROM contributions_search {$search_join} {$where}) AS UniqueContribs<br><pre>";
print_r($parse_data[3]);
echo "</pre><p>";
}
}
$result = $web_db->prepare("SELECT COUNT(*) AS records, SUM(TransactionAmount) AS total FROM(SELECT DISTINCT ContributionID, TransactionAmount FROM contributions_search {$search_join} {$where}) AS UniqueContribs");
$result->execute($parse_data[3]);
$totals_row = $result->fetchAll(PDO::FETCH_ASSOC);
$totals_row = $totals_row[0];
if ($totals_row["records"] == 0) {
echo "Your search did not return any records.";
} else {
# Calculate total pages based on display rows
if (isset($_POST["return_rows"])) {
$limit = $_POST["return_rows"];
} else {
$limit = 10;
}
$total_pages = intval(($totals_row["records"] - 1) / $limit) + 1;
# Get page # to display
if (isset($_POST["page"])) {
$page = $_POST["page"];
} else {
$page = 1;
}
if (isset($_POST["page_button"])) {
switch ($_POST["page_button"]) {
case "Next":
$page++;
break;
case "Previous":
$page--;
break;
default:
$page = $_POST["page_button"];
break;
}
}
# Reset the page to 1 if you selected a smaller set then is currently displayed, or if the search button was used
if ($page > $total_pages || isset($_POST["search_btn"])) {
$page = 1;
}
# Determine rows being displayed
$first_row = ($page - 1) * $limit + 1;
$last_row = $first_row + $limit - 1;
if ($first_row > $totals_row["records"]) {
$first_row = 1;
}
if ($last_row > $totals_row["records"]) {
$last_row = $totals_row["records"];
}
$sort = "TransactionDateEnd";
$sort_order = "DESC";
if (isset($_POST["sort"])) {
$sort = $_POST["sort"];
$sort_order = $_POST["sort_order"];
}
$field_set = "";
$fields = array("RecipientCandidateNameNormalized|Recipient Name|", "RecipientCommitteeNameNormalized|Recipient Committee|", "RecipientCommitteeID|Recipient Committee ID|", "RecipientCandidateOffice|Office Sought|", "ballot_measures|Ballot Measure(s)|MultiLine", "DonorNameNormalized|Contributor Name|", "DonorCommitteeID|Contributor ID|", "TransactionAmount|Amount|Currency", "TransactionDateEnd|Date|Date", "DonorEmployerNormalized|Contributor Employer|", "DonorOccupationNormalized|Contributor Occupation|", "DonorState|Contributor State|");
if (isset($_POST["field_list"])) {
$field_set = $_POST["field_list"];
}
if (isset($_POST["fields"])) {
$field_set = $_POST["fields"];
}
if ($field_set == "Show more fields") {
$fields = array("RecipientCandidateNameNormalized|Recipient Name|", "RecipientCommitteeNameNormalized|Recipient Committee|", "RecipientCommitteeID|Recipient Committee ID|", "RecipientCandidateOffice|Office Sought|", "RecipientCandidateDistrict|District|", "ballot_measures|Ballot Measure(s)|MultiLine", "DonorNameNormalized|Contributor Name|", "DonorCommitteeID|Contributor ID|", "TransactionAmount|Amount|Currency", "TransactionDateEnd|Date|Date", "DonorEmployerNormalized|Contributor Employer|", "DonorOccupationNormalized|Contributor Occupation|", "DonorState|Contributor State|", "DonorZipCode|Contributor Zip Code|", "DonorCity|Contributor City|", "TransactionType|Transaction Type|", "Election|Election|Date", "ElectionCycle|Cycle|");
}
$sort_fields = array("RecipientCandidateNameNormalized|Recipient Name", "RecipientCommitteeNameNormalized|Recipient Committee", "RecipientCommitteeID|Recipient Committee ID", "RecipientCandidateOffice|Office Sought", "RecipientCandidateDistrict|District", "ballot_measures|Ballot Measure(s)", "DonorNameNormalized|Contributor Name", "DonorCommitteeID|Contributor ID", "TransactionAmount|Amount", "TransactionDateEnd|Date", "DonorEmployerNormalized|Contributor Employer", "DonorOccupationNormalized|Contributor Occupation", "DonorState|Contributor State", "DonorZipCode|Contributor Zip Code", "DonorCity|Contributor City", "TransactionType|Transaction Type", "Election|Election", "ElectionCycle|Cycle");
# make sure the sort value is in the expected set of values in case of SQL injection
$sort_validate = array("RecipientCandidateNameNormalized", "RecipientCommitteeNameNormalized", "RecipientCommitteeID", "RecipientCandidateOffice", "RecipientCandidateDistrict", "ballot_measures", "DonorNameNormalized", "DonorCommitteeID", "TransactionAmount", "TransactionDateEnd", "DonorEmployerNormalized", "DonorOccupationNormalized", "DonorState", "DonorZipCode", "DonorCity", "TransactionType", "Election", "ElectionCycle");
if (!in_array($sort, $sort_validate)) {
$sort = "TransactionDateEnd";
}
if (isset($_GET["debug"])) {
if ($_GET["debug"] == "y") {
echo "<b>records</b>:<br>SELECT contributions.*, ballot_measures FROM contributions LEFT JOIN contributions_grouped USING(ContributionID) INNER JOIN contributions_search ON(contributions.id = contributions_search.id) {$search_join} {$where} GROUP BY ContributionID ORDER BY {$sort} {$sort_order} LIMIT " . ($page - 1) * $limit . ",{$limit}<br><pre>";
print_r($parse_data[3]);
echo "</pre><p>";
}
}
$result = $web_db->prepare("SELECT contributions.*, ballot_measures FROM contributions LEFT JOIN contributions_grouped USING(ContributionID) INNER JOIN contributions_search ON(contributions.id = contributions_search.id) {$search_join} {$where} GROUP BY ContributionID ORDER BY {$sort} {$sort_order} LIMIT " . ($page - 1) * $limit . ",{$limit}");
$result->execute($parse_data[3]);
$rows_returned = $result->rowCount();
echo "<div id=\"caps_results\">";
echo "<div class=\"font_results_header\"><strong>Search Results</strong></div>";
echo "<div class=\"font_results_header\"><strong>\$" . number_format($totals_row["total"], 2, ".", ",") . "</strong> in " . number_format($totals_row["records"], 0, ".", ",") . " contributions ";
display_tooltip("This is the total amount received using the specified search criteria.", -160, 10, 180, "");
echo "</div><hr class=\"caps_hr1\">";
# display summary breakdowns for different criteria
for ($i = 0; $i < strlen($summary_type); $i++) {
switch (substr($summary_type, $i, 1)) {
case "D":
# Donor(Contributor) summary
echo "<div class=\"font_results_header\"><strong>" . strtoupper($_POST["contributor"]) . "</strong> has contributed</div>";
echo "<div id=\"caps_breakdown_box\">";
$employee = "";
$query = "SELECT\n IsEmployee,\n CandidateContribution,\n BallotMeasureContribution,\n SUM(TransactionAmount) AS TotalAmount\n FROM(SELECT DISTINCT\n contributions.ContributionID,\n contributions.IsEmployee,\n contributions.CandidateContribution,\n contributions.BallotMeasureContribution,\n contributions.TransactionAmount\n FROM contributions\n LEFT JOIN contributions_grouped USING(ContributionID)\n INNER JOIN contributions_search ON(contributions.id = contributions_search.id)\n {$search_join}\n {$where}) AS UniqueContributions\n GROUP BY IsEmployee, CandidateContribution, BallotMeasureContribution\n ORDER BY IsEmployee, CandidateContribution, BallotMeasureContribution";
$result2 = $web_db->prepare($query);
$result2->execute($parse_data[3]);
foreach ($result2->fetchAll(PDO::FETCH_ASSOC) as $row2) {
if ($row2["IsEmployee"] != $employee) {
if ($row2["IsEmployee"] == "Y") {
echo "<b>Employee Contributions</b><br>";
} else {
if ($employee == "Y") {
echo " <br>";
}
echo "<b>Organizational Contributions</b><br>";
}
$employee = $row2["IsEmployee"];
}
if ($row2["CandidateContribution"] == "Y" && $row2["BallotMeasureContribution"] == "N") {
echo " <b>\$" . number_format($row2["TotalAmount"], 2, ".", ",") . "</b> to <b>candidates</b><br>";
}
if ($row2["CandidateContribution"] == "N" && $row2["BallotMeasureContribution"] == "Y") {
echo " <b>\$" . number_format($row2["TotalAmount"], 2, ".", ",") . "</b> to <b>ballot measures</b><br>";
}
if ($row2["CandidateContribution"] == "N" && $row2["BallotMeasureContribution"] == "N") {
echo " <b>\$" . number_format($row2["TotalAmount"], 2, ".", ",") . "</b> to <b>other committees</b><br>";
}
}
echo "</div><hr class=\"caps_hr1\">";
break;
case "C":
# Candidates summary
echo "<div class=\"font_results_header\"><strong>" . strtoupper($_POST["search_candidates"]) . "</strong> has received</div>";
echo "<div id=\"caps_breakdown_box\">";
$query = "SELECT\n RecipientCommitteeNameNormalized,\n RecipientCommitteeID,\n COUNT(*) AS TotalCount, \n SUM(TransactionAmount) AS TotalAmount\n FROM(SELECT DISTINCT\n contributions.ContributionID,\n contributions.RecipientCommitteeNameNormalized,\n contributions.RecipientCommitteeID,\n contributions.TransactionAmount\n FROM contributions\n LEFT JOIN contributions_grouped USING(ContributionID)\n INNER JOIN contributions_search ON(contributions.id = contributions_search.id)\n {$search_join}\n {$where}) AS UniqueContributions\n GROUP BY RecipientCommitteeID, RecipientCommitteeNameNormalized\n ORDER BY RecipientCommitteeID, RecipientCommitteeNameNormalized";
$result2 = $web_db->prepare($query);
$result2->execute($parse_data[3]);
foreach ($result2->fetchAll(PDO::FETCH_ASSOC) as $row2) {
echo "<b>({$row2["RecipientCommitteeID"]}) {$row2["RecipientCommitteeNameNormalized"]}</b> has raised \$" . number_format($row2["TotalAmount"], 2, ".", ",") . " in " . number_format($row2["TotalCount"], 0, ".", ",") . " contributions<br>";
}
echo "</div><hr class=\"caps_hr1\">";
break;
case "O":
# Offices summary
echo "<div class=\"font_results_header\"><strong>Top 5 Candidates for " . strtoupper($_POST["office_list"]) . "</strong> have received</div>";
echo "<div id=\"caps_breakdown_box\">";
$query = "SELECT\n RecipientCandidateNameNormalized,\n ElectionCycle,\n COUNT(*) AS TotalCount, \n SUM(TransactionAmount) AS TotalAmount\n FROM(SELECT DISTINCT\n contributions.ContributionID,\n contributions.RecipientCandidateNameNormalized,\n contributions.ElectionCycle,\n contributions.TransactionAmount\n FROM contributions\n LEFT JOIN contributions_grouped USING(ContributionID)\n INNER JOIN contributions_search ON(contributions.id = contributions_search.id)\n {$search_join}\n {$where}) AS UniqueContributions\n GROUP BY ElectionCycle, RecipientCandidateNameNormalized\n ORDER BY TotalAmount DESC\n LIMIT 5";
$result2 = $web_db->prepare($query);
$result2->execute($parse_data[3]);
foreach ($result2->fetchAll(PDO::FETCH_ASSOC) as $row2) {
echo "<b>{$row2["RecipientCandidateNameNormalized"]} in {$row2["ElectionCycle"]}</b> has raised \$" . number_format($row2["TotalAmount"], 2, ".", ",") . " in " . number_format($row2["TotalCount"], 0, ".", ",") . " contributions<br>";
}
echo "</div><hr class=\"caps_hr1\">";
break;
case "E":
# Ballots Election summary
$election = substr($_POST["proposition_list"], 4);
if (intval(strtotime($election)) > 0) {
$election_date = date("F Y", strtotime($election));
echo "<div class=\"font_results_header\"><strong>Ballot Measures</strong> on the {$election_date} ballot have received</div>";
echo "<div id=\"caps_breakdown_box\">";
$query = "SELECT\n Target,\n COUNT(*) AS TotalCount, \n SUM(TransactionAmount) AS TotalAmount,\n SUM(IF(PositionID = 1,1,0)) AS SupportCount,\n SUM(IF(PositionID=1,TransactionAmount,0)) AS SupportAmount,\n SUM(IF(PositionID = 2,1,0)) AS OpposeCount,\n SUM(IF(PositionID=2,TransactionAmount,0)) AS OpposeAmount\n FROM(SELECT DISTINCT\n contributions.ContributionID,\n contributions.Target,\n PositionID,\n contributions.TransactionAmount\n FROM contributions\n LEFT JOIN contributions_grouped USING(ContributionID)\n INNER JOIN contributions_search ON(contributions.id = contributions_search.id)\n {$search_join}\n {$where}) AS UniqueContributions\n GROUP BY Target\n ORDER BY Target";
$result2 = $web_db->prepare($query);
$result2->execute($parse_data[3]);
foreach ($result2->fetchAll(PDO::FETCH_ASSOC) as $row2) {
if (strpos($row2["Target"], "-") !== false) {
echo "<p><b>" . substr($row2["Target"], 0, strrpos($row2["Target"], " - ")) . "</b>" . substr($row2["Target"], strrpos($row2["Target"], " - ")) . "<br>";
} else {
echo "<p><b>{$row2["Target"]}</b><br>";
}
echo " \$" . number_format($row2["TotalAmount"], 2, ".", ",") . " total raised - " . number_format($row2["TotalCount"], 0, ".", ",") . " contributions<br>";
echo " - Support: \$" . number_format($row2["SupportAmount"], 2, ".", ",") . " raised - " . number_format($row2["SupportCount"], 0, ".", ",") . " contributions<br>";
echo " - Oppose: \$" . number_format($row2["OpposeAmount"], 2, ".", ",") . " raised - " . number_format($row2["OpposeCount"], 0, ".", ",") . " contributions";
echo "</p>";
}
echo "</div><hr class=\"caps_hr1\">";
}
break;
}
}
$criteria = urlencode(serialize($parse_data[2]));
$data = urlencode(serialize($parse_data[3]));
echo "<h2 class=\"font_large_header caps_title1\">Contributions</h2>";
echo "<div id=\"caps_filter_box\">";
echo "Show";
echo "<select id=\"show\" name=\"return_rows\" class=\"font_input input_border caps_select4\">";
if ($limit == 10) {
echo "<option selected>10</option>";
} else {
echo "<option>10</option>";
}
if ($limit == 25) {
echo "<option selected>25</option>";
} else {
echo "<option>25</option>";
}
if ($limit == 50) {
echo "<option selected>50</option>";
} else {
echo "<option>50</option>";
}
if ($limit == 100) {
echo "<option selected>100</option>";
} else {
echo "<option>100</option>";
}
echo "</select>";
echo "rows Sort by";
echo "<select id=\"sort\" name=\"sort\" class=\"font_input input_border caps_select4\">";
foreach ($sort_fields as $sort_item) {
$item_data = explode("|", $sort_item);
if ($sort == $item_data[0]) {
echo "<option value=\"{$item_data[0]}\" SELECTED>{$item_data[1]}</option>";
} else {
echo "<option value=\"{$item_data[0]}\">{$item_data[1]}</option>";
}
}
echo "</select><select id=\"sort_order\" name=\"sort_order\" class=\"font_input input_border caps_select4\">";
if ($sort_order == "ASC") {
echo "<option value=\"ASC\" SELECTED>Ascending</option><option VALUE=\"DESC\">Descending</option>";
} else {
echo "<option value=\"ASC\">Ascending</option><option VALUE=\"DESC\" SELECTED>Descending</option>";
}
echo "</select>";
echo "<input type=\"submit\" value=\"Update\" id=\"caps_update_btn\">";
# Do not display the download option if there are more records then allowed to download
if ($totals_row["records"] <= $max_download_records) {
echo "<div class=\"right\">";
echo "<a href=\"download_csv.php?w=" . urlencode($where) . "&d={$data}&c={$criteria}\" class=\"download_csv\">Download CSV</a> ";
display_tooltip("Download the search results as a CSV file, which can be opened in most spreadsheet software.", -180, 10, 160, "");
echo "</div> <!-- end download_box -->";
}
echo "</div> <!-- end filter_box -->";
echo "<div class=\"font_input\">Showing <strong>" . number_format($first_row, 0, ".", ",") . "</strong> to <strong>" . number_format($last_row, 0, ".", ",") . "</strong> of <strong>" . number_format($totals_row["records"], 0, ".", ",") . "</strong> rows ";
$field_msg = "Show more fields";
if ($field_set == "Show more fields") {
$field_msg = "Show fewer fields";
}
echo "<input type=\"submit\" name=\"fields\" value=\"{$field_msg}\" id=\"caps_field_btn\">";
display_tooltip("Show more columns in the table for additional information on contributions.", -180, 10, 160, "");
echo "</div>";
echo "<div id=\"caps_table_box\">";
echo "<table title=\"search table\" summary=\"search table\" class=\"caps_table\">";
echo "<thead>";
echo "<tr>";
$count = 1;
foreach ($fields as $field) {
$field_data = explode("|", $field);
echo "<th id=\"c{$count}\">{$field_data[1]}</th>";
$count++;
}
echo "</tr>";
echo "</thead>";
echo "<tbody>";
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo "<tr>";
$count = 1;
foreach ($fields as $field) {
$field_data = explode("|", $field);
switch ($field_data[2]) {
case "Date":
if (date("F j, Y", strtotime($row[$field_data[0]])) == "December 31, 1969") {
echo "<td headers=\"c{$count}\"><I>unknown</I></td>";
} else {
echo "<td headers=\"c{$count}\">" . date("M j, Y", strtotime($row[$field_data[0]])) . "</td>";
}
break;
case "Currency":
echo "<td headers=\"c{$count}\" style=\"text-align:right\">\$" . number_format($row[$field_data[0]], 2, ".", ",") . "</td>";
break;
case "MultiLine":
echo "<td headers=\"c{$count}\">" . str_replace(" | ", "<hr>", $row[$field_data[0]]) . "</td>";
break;
default:
echo "<td headers=\"c{$count}\">{$row[$field_data[0]]}</td>";
break;
}
$count++;
}
echo "</tr>";
}
echo "</tbody>";
echo "</table>";
echo "</div> <!-- end caps_table_box -->";
# Pagination section
echo "<div class=\"center\">";
echo "<input type=\"hidden\" name=\"page\" value=\"{$page}\">";
echo "<input type=\"hidden\" name=\"field_list\" value=\"{$field_set}\">";
if ($total_pages > 1) {
if ($page > 1) {
echo "<input type=\"submit\" name=\"page_button\" value=\"Previous\" id=\"caps_previous_btn\">";
}
if ($total_pages >= 3) {
for ($page_btn = 1; $page_btn <= $total_pages; $page_btn++) {
if ($page == $page_btn) {
echo "<input type=\"submit\" name=\"page_button\" value=\"{$page_btn}\" id=\"caps_current_page_btn\">";
} else {
echo "<input type=\"submit\" name=\"page_button\" value=\"{$page_btn}\" class=\"caps_page_btn\">";
}
if ($page_btn == 10) {
break;
}
}
}
if ($page < $total_pages && $page < 10) {
echo "<input type=\"submit\" name=\"page_button\" value=\"Next\" id=\"caps_next_btn\">";
}
}
$result = $web_db->query("SELECT * FROM smry_last_update");
$row = $result->fetchAll(PDO::FETCH_ASSOC);
$last_update = $row[0]["LastUpdate"];
echo "<p> </p>";
echo "<div class=\"font_input\"><p>This page will not display more than 1,000 entries.</p>";
# Do not display the download option if there are more records then allowed to download
if ($totals_row["records"] <= $max_download_records) {
echo "(To view the entire set of search results, <a href=\"download_csv.php?w=" . urlencode($where) . "&d={$data}&c={$criteria}\" class=\"download_csv\">download the CSV</a> file.)";
}
echo "</div><br><div class=\"font_small\">Contributions data is current as of " . date("F j, Y", strtotime($last_update)) . ".</div><br>";
echo "</div> <!-- end center -->";
echo "</div> <!-- end caps_results -->";
}
}
}
