Manually deleted file to fix merge conflict.HEAD master

author: Nathan Kinkade <nkinkade@creativecommons.org> 2014-05-19 18:18:52 -0400
committer: Nathan Kinkade <nkinkade@creativecommons.org> 2014-05-19 18:18:52 -0400
commit: fea92fb73fc066701a4e5e578edee7d737045a41 (patch)
tree: 8475a74fb159756896e90a802eca32e2545ed86d /search.php
parent: 03a01ac31a16cf7f44e827db15b7483b0ec330cd (diff)
parent: c1ea47789989b08e919645d8b8133cfea0ad97c9 (diff)
1 files changed, 461 insertions, 0 deletions
diff --git a/search.php b/search.php
new file mode 100644
index 0000000..936b6c0
--- /dev/null
+++ b/search.php
@@ -0,0 +1,461 @@
+<?php
+
+error_reporting(E_ALL ^ E_NOTICE);
+
+$results_per_page = '5000';
+
+echo <<<HTML
+<!DOCTYPE html>
+
+<html>
+<head>
+	<title>Miami-Dade Clerk of Courts: Cases</title>
+	<link rel='stylesheet' media='all' type='text/css' href='style.css' />
+</head>
+
+<body>
+HTML;
+
+$dbh = new PDO('mysql:host=localhost;dbname=mdcc', 'mdcc', 'Mdcc.');
+
+$database_fields = array(
+	'court_case_no',
+	'state_case_no',
+	'name',
+	'first_name',
+	'last_name',
+	'date_birth',
+	'date_filed',
+	'date_closed',
+	'warrant_type',
+	'hearing_date',
+	'hearing_time',
+	'hearing_type',
+	'prob_start_date',
+	'prob_end_date',
+	'prob_length',
+	'in_jail',
+	'released_to',
+	'bond_amount',
+	'bond_status',
+	'bond_type',
+	'bond_date',
+	'defense_attorney',
+);
+
+// Get all the request parameters into local variables for readability
+$request_params = array(
+	'case_id' => '',
+	'between' => '',
+	'case_years' => '',
+	'case_num' => '',
+	'case_num_to' => '',
+	'defendant' => '',
+	'name' => '',
+	'charge' => '',
+	'disposition' => '',
+	'case_closed' => '',
+	'docket' => '',
+	'in_jail' => '',
+	'prob_given' => '',
+	'prob_still' => '',
+	'prob_per_complete' => '',
+	'prob_start_day' => '',
+	'prob_start_month' => '',
+	'prob_start_year' => '',
+	'prob_end_day' => '',
+	'prob_end_month' => '',
+	'prob_end_year' => '',
+);
+foreach ( $request_params as $param => $value ) {
+	if ( isset($_REQUEST[$param]) && is_array($_REQUEST[$param]) ) {
+		$$param = $_REQUEST[$param];
+	} elseif ( isset($_REQUEST[$param]) ) {
+		$$param = trim($_REQUEST[$param]);
+	}
+}
+$select = "SELECT DISTINCT cases.* FROM cases ";
+$select_num_rows = "SELECT count(DISTINCT cases.id) FROM cases ";
+
+if ( $charge || $disposition ) {
+	$select .= "LEFT JOIN charges ON cases.id = charges.case_id ";
+	$select_num_rows .= "LEFT JOIN charges ON cases.id = charges.case_id ";
+}
+
+if ( $docket ) {
+	$select .= "LEFT JOIN dockets ON cases.id = dockets.case_id ";
+	$select_num_rows .= "LEFT JOIN dockets ON cases.id = dockets.case_id ";
+}
+
+// Two arrays for storing where clauses, one for clauses that need to be joined
+// by AND and one for those needing to be joined by OR
+$where_parts_and = array();
+$where_parts_or = array();
+
+if ( $case_id ) {
+	$where_parts_and[] = "id = '$case_id'";
+}
+
+// There are a limited set of circumstances where using a range will work, as
+// the MySQL between operator doesn't always work with wildcards.  Unset case
+// $case_years and $case_num and then populate $between, since we already have
+// code to handle that.
+if ( $case_num_to ) {
+	$case_year = substr($case_years[0], -2);
+	$case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT);
+	$case_num_to = str_pad($case_num_to, 6, '0', STR_PAD_LEFT);
+	$range_start = "F-{$case_year}-{$case_num}";
+	$range_end = "F-{$case_year}-{$case_num_to}";
+	$between = "$range_start:$range_end";
+	unset($case_years);
+	unset($case_num);
+}
+
+if ( $between ) {
+	list($btwn_start, $btwn_end) = explode(':', $between);
+	$where_parts_and[] = "court_case_no BETWEEN '$btwn_start' AND '$btwn_end'";
+}
+
+if ( $case_years && $case_num && $defendant ) {
+	foreach ( $case_years as $case_year ) {
+		$case_year = substr($case_year, -2);
+		$case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT);
+		$where_parts_or[] = "cases.court_case_no = 'F-{$case_year}-{$case_num}-{$defendant}'";
+	}
+} elseif ( $case_years && $case_num ) {
+	foreach ( $case_years as $case_year ) {
+		$case_year = substr($case_year, -2);
+		$case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT);
+		$where_parts_or[] = "cases.court_case_no LIKE 'F-{$case_year}-{$case_num}%'";
+	}
+} elseif ( $case_years && $defendant ) {
+	foreach ( $case_years as $case_year ) {
+		$case_year = substr($case_year, -2);
+		$where_parts_or[] = "cases.court_case_no LIKE 'F-{$case_year}-%-{$defendant}'";
+	}
+} elseif ( $case_num && $defendant ) {
+	$case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT);
+	$where_parts_and[] = "cases.court_case_no LIKE 'F-%-{$case_num}-{$defendant}'";
+} elseif ( $case_years ) {
+	foreach ( $case_years as $case_year ) {
+		$case_year = substr($case_year, -2);
+		$where_parts_or[] = "cases.court_case_no LIKE 'F-{$case_year}-%'";
+	}
+} elseif ( $case_num ) {
+	$case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT);
+	$where_parts_and[] = "cases.court_case_no LIKE 'F-%-{$case_num}%'";
+} elseif ( $defendant ) {
+	$where_parts_and[] = "cases.court_case_no LIKE 'F-%-%-{$defendant}'";
+}
+
+if ( $name ) {
+	$select .= " LEFT JOIN akas on cases.id = akas.case_id ";
+
+	$names = explode(',', $name);
+	foreach ( $names as $name ) {
+		$name = trim($name);
+		if ( preg_match('/^-(.*)$/', $name, $matches) ) {
+			$where_parts_and[] = "( cases.name NOT LIKE '%{$matches[1]}%'
+				OR cases.id NOT IN (
+					SELECT case_id FROM akas
+					WHERE last_name LIKE '{$matches[1]}%'
+					OR first_name LIKE '{$matches[1]}%' )
+			)";
+		} else {
+			$where_parts_and[] = "( cases.name LIKE '%{$name}%'
+				OR akas.last_name LIKE '{$name}%'
+				OR akas.first_name LIKE '{$name}%' )";
+		}
+	}
+}
+
+if ( $charge ) {
+	$charges = explode(',', $charge);
+	foreach ( $charges as $charge ) {
+		$charge = trim($charge);
+		if ( preg_match('/^-(.*)$/', $charge, $matches) ) {
+			$where_parts_and[] = "cases.id NOT IN ( SELECT case_id FROM charges WHERE charge LIKE '%{$matches[1]}%' )";
+		} else {
+			$where_parts_and[] = "charges.charge LIKE '%${charge}%'";
+		}
+	}
+}
+
+if ( $disposition ) {
+	$dispositions = explode(',', $disposition);
+	foreach ( $dispositions as $disposition ) {
+		$disposition = trim($disposition);
+		if ( preg_match('/^-(.*)$/', $disposition, $matches) ) {
+			$where_parts_and[] = "cases.id NOT IN ( SELECT case_id FROM charges WHERE disposition LIKE '%{$matches[1]}%' )";
+		} else {
+			$where_parts_and[] = "charges.disposition LIKE '%{$disposition}%'";
+		}
+	}
+}
+
+if ( $case_closed ) {
+	if ( $case_closed == 'yes' ) {
+		$where_parts_and[] = "cases.date_closed IS NOT NULL"; 
+	} elseif ( $case_closed == 'no' ) {
+		$where_parts_and[] = "cases.date_closed IS NULL"; 
+	}
+}
+
+if ( $in_jail ) {
+	if ( $in_jail == 'Y' ) {
+		$where_parts_and[] = "cases.in_jail = 'Y'";
+	} elseif ( $in_jail == 'N' ) {
+		$where_parts_and[] = "cases.in_jail = 'N'";
+	}
+}
+
+if ( $prob_given ) {
+	$where_parts_and[] = "cases.prob_start_date IS NOT NULL";
+}
+
+if ( $prob_still ) {
+	$today = date('Y-m-d');
+	$where_parts_and[] = "'$today' BETWEEN cases.prob_start_date AND cases.prob_end_date";
+}
+
+if ( $prob_per_complete ) {
+	$where_parts_and[] = "( DATEDIFF(prob_end_date, prob_start_date) * .{$prob_per_complete} <
+		DATEDIFF(CURDATE(), prob_start_date)
+		AND CURDATE() < prob_end_date )";
+}
+
+if ( $prob_start_year && $prob_start_month && $prob_start_day ) {
+	$where_parts_and[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}'
+		AND MONTH(cases.prob_start_date) = '{$prob_start_month}'
+		AND DAY(cases.prob_start_date) = '{$prob_start_day}'";
+} elseif ( $prob_start_year && $prob_start_month ) {
+	$where_parts_and[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}'
+		AND MONTH(cases.prob_start_date) = '{$prob_start_month}'";
+} elseif ( $prob_start_year && $prob_start_day ) {
+	$where_parts_and[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}'
+		AND DAY(cases.prob_start_date) = '{$prob_start_day}'";
+} elseif ( $prob_start_month && $prob_start_day ) {
+	$where_parts_and[] = "MONTH(cases.prob_start_date) = '{$prob_start_month}'
+		AND DAY(cases.prob_start_date) = '{$prob_start_day}'";
+} elseif ( $prob_start_year ) {
+	$where_parts_and[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}'";
+} elseif ( $prob_start_month ) {
+	$where_parts_and[] = "MONTH(cases.prob_start_date) = '{$prob_start_month}'";
+} elseif ( $prob_start_day ) {
+	$where_parts_and[] = "DAY(cases.prob_start_date) = '{$prob_start_day}'";
+}
+
+if ( $prob_end_year && $prob_end_month && $prob_end_day ) {
+	$where_parts_and[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}'
+		AND MONTH(cases.prob_end_date) = '{$prob_end_month}'
+		AND DAY(cases.prob_end_date) = '{$prob_end_day}'";
+} elseif ( $prob_end_year && $prob_end_month ) {
+	$where_parts_and[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}'
+		AND MONTH(cases.prob_end_date) = '{$prob_end_month}'";
+} elseif ( $prob_end_year && $prob_end_day ) {
+	$where_parts_and[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}'
+		AND DAY(cases.prob_end_date) = '{$prob_end_day}'";
+} elseif ( $prob_end_month && $prob_end_day ) {
+	$where_parts_and[] = "MONTH(cases.prob_end_date) = '{$prob_end_month}'
+		AND DAY(cases.prob_end_date) = '{$prob_end_day}'";
+} elseif ( $prob_end_year ) {
+	$where_parts_and[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}'";
+} elseif ( $prob_end_month ) {
+	$where_parts_and[] = "MONTH(cases.prob_end_date) = '{$prob_end_month}'";
+} elseif ( $prob_end_day ) {
+	$where_parts_and[] = "DAY(cases.prob_end_date) = '{$prob_end_day}'";
+}
+
+if ( $docket ) {
+	$dockets = explode(',', $docket);
+	foreach ( $dockets as $docket ) {
+		$docket = trim($docket);
+		if ( preg_match('/^-(.*)$/', $docket, $matches) ) {
+			$where_parts_and[] = "NOT EXISTS ( SELECT case_id FROM dockets WHERE docket LIKE '%{$matches[1]}%' AND dockets.case_id = cases.id )";
+		} else {
+			$where_parts_and[] = "dockets.docket LIKE '%{$docket}%'";
+		}
+	}
+}
+
+$where = '';
+
+if ( $where_parts_or ) {
+	foreach ( $where_parts_or as $or_part ) {
+		if ( $where ) {
+			$where .= " OR $or_part";
+		} else {
+			$where = "WHERE $or_part";
+		}
+	}
+}
+
+if ( $where_parts_and ) {
+	// The first element of the AND list should be appended to the existing
+	// where clause with an AND
+	if ( $where ) {
+		$where .= " AND " . array_shift($where_parts_and);
+	}
+	// If there are still any where_parts_or left, then join them with AND
+	if ( count($where_parts_and) ) {
+		foreach ( $where_parts_and as $and_part ) {
+			if ( $where ) {
+				$where .= " AND $and_part";
+			} else {
+				$where = "WHERE $and_part";
+			}
+		}
+	}
+}
+
+
+if ( empty($where) ) {
+	echo "You must enter at least one search criteria.";
+	exit;
+}
+	
+$sql = $select . $where;
+$sql_num_rows = $select_num_rows . $where;
+
+# If no ordering info was passed then order by court_case_no by default
+if ( $_REQUEST['orderby'] && $_REQUEST['orderdir'] ) {
+	$sql .= " ORDER BY {$_REQUEST['orderby']} {$_REQUEST['orderdir']}";
+} else {
+	$sql .= " ORDER BY court_case_no DESC";
+}
+
+echo "<a id='search_again' href='/mdcc/'>New Search</a>";
+
+# Get the total row count so we can display it to the user and deal with
+# pagination
+$st = $dbh->prepare($sql_num_rows);
+if ( $st->execute() ) {
+	$row_count = $st->fetchColumn();
+	if ( $row_count == '0' ) {
+		echo "<h3>No records found.</h3>";
+		exit;
+	}
+	if ( $row_count <= $results_per_page ) {
+		echo "<h3>{$row_count} records found.</h3>";
+	} elseif ( $_GET['page'] ) {
+		$viewing = ($_GET['page'] - 1) * $results_per_page + 1;
+		$upto = ($_GET['page'] - 1) * $results_per_page + $results_per_page;
+		if ( $upto >= $row_count ) {
+			echo "<h3>Viewing {$viewing} to {$row_count} of {$row_count} records found.</h3>";
+		} else {
+			echo "<h3>Viewing {$viewing} to {$upto} of {$row_count} records found.</h3>";
+		}
+	} else {
+		echo "<h3>Viewing 1 to {$results_per_page} of {$row_count} records found.</h3>";
+	}
+}
+
+// Give the user an export button
+$url_sql = urlencode($sql);
+echo "<div id='export'><a href='export.php?sql=$url_sql'>Export</a></div>";
+
+# Pagination
+if ( $_GET['page'] ) {
+	$start_num = ($_GET['page'] - 1) * $results_per_page;
+	$sql .= " LIMIT $start_num, $results_per_page";
+} else {
+	$sql .= " LIMIT 0, $results_per_page";
+}
+
+$st = $dbh->prepare($sql);
+if ( $st->execute() ) {
+	echo <<<HTML
+<table class="results_table">
+	<tr class='tablehead'>
+		<th>
+			Court Case No. <br />
+			<a class="sort_asc" title="Sort ascending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=court_case_no&orderdir=ASC">A</a>
+			<a class="sort_desc" title="Sort descending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=court_case_no&orderdir=DESC">D</a>
+		</th>
+		<th>State Case No.</th>
+		<th>Name</th>
+		<th>First name</th>
+		<th>Last name</th>
+		<th>Date of Birth</th>
+		<th>
+			Date Filed <br />
+			<a class="sort_asc" title="Sort ascending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=date_filed&orderdir=ASC">A</a>
+			<a class="sort_desc" title="Sort descending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=date_filed&orderdir=DESC">D</a>
+		</th>
+		<th>
+			Date Closed <br />
+			<a class="sort_asc" title="Sort ascending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=date_closed&orderdir=ASC">A</a>
+			<a class="sort_desc" title="Sort descending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=date_closed&orderdir=DESC">D</a>
+		</th>
+		<th>Warrant Type</th>
+		<th>Hearing Date</th>
+		<th>Hearing Time</th>
+		<th>Hearing Type</th>
+		<th>
+			Probation Start <br />
+			<a class="sort_asc" title="Sort ascending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=prob_start_date&orderdir=ASC">A</a>
+			<a class="sort_desc" title="Sort descending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=prob_start_date&orderdir=DESC">D</a>
+		</th>
+		<th>
+			Probation End <br />
+			<a class="sort_asc" title="Sort ascending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=prob_end_date&orderdir=ASC">A</a>
+			<a class="sort_desc" title="Sort descending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=prob_end_date&orderdir=DESC">D</a>
+		</th>
+		<th>Probation Length</th>
+		<th>
+			In Jail? <br />
+			<a class="sort_asc" title="Sort ascending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=in_jail&orderdir=ASC">A</a>
+			<a class="sort_desc" title="Sort descending" href="search.php?{$_SERVER['QUERY_STRING']}&orderby=in_jail&orderdir=DESC">D</a>
+		</th>
+		<th>Released To</th>
+		<th>Bond Amount</th>
+		<th>Bond Status</th>
+		<th>Bond Type</th>
+		<th>Bond Date</th>
+		<th>Defense Attorney</th>
+		<th>Charges</th>
+		<th>Dockets</th>
+	</tr>
+HTML;
+
+	$alt = 'alt';
+	while ( $row = $st->fetch(PDO::FETCH_ASSOC) ) {
+		$akas_sql = "SELECT * FROM akas where case_id = '{$row['id']}'";
+		$akas_st = $dbh->prepare($akas_sql);
+		if ( $akas_st->execute() ) {
+			$akas_rows = $akas_st->rowCount();
+		}
+		$alt = $alt ? '' : 'alt';
+		echo "	<tr class='$alt'>\n";
+		#foreach ( $row as $field => $value ) {
+		foreach ( $database_fields as $field ) {
+			if ( 'name' == $field && $akas_rows ) {
+				echo "		<td><a href='akas.php?case_id={$row['id']}&case_no={$row['court_case_no']}'>{$row[$field]}</a></td>\n";
+			} else {
+				echo "		<td>{$row[$field]}</td>\n";
+			}
+		}
+		echo "		<td><a href='charges.php?case_id={$row['id']}&case_no={$row['court_case_no']}'>Charges</td>\n";
+		echo "		<td><a href='dockets.php?case_id={$row['id']}&case_no={$row['court_case_no']}'>Dockets</td>\n";
+		echo "	</tr>\n";
+	}
+}
+
+echo "</table>";
+
+if ( $_GET['page'] && $row_count > $results_per_page && $upto < $row_count ) {
+	$next_page = $_GET['page'] + 1;
+	$pagination_query_string = preg_replace('/page=\d+/', "page=$next_page", $_SERVER['QUERY_STRING']);
+	echo "<br /><a href='search.php?$pagination_query_string'>Next Page&gt;&gt;</a>";
+} elseif ( $row_count > $results_per_page  && $upto < $row_count ) {
+	echo "<br /><a href='search.php?{$_SERVER['QUERY_STRING']}&page=2'>Next Page &gt;&gt;</a>";
+}
+
+echo <<<HTML
+
+</body>
+
+</html>
+HTML;
+
+?>
author	Nathan Kinkade <nkinkade@creativecommons.org>	2014-05-19 18:18:52 -0400
committer	Nathan Kinkade <nkinkade@creativecommons.org>	2014-05-19 18:18:52 -0400
commit	fea92fb73fc066701a4e5e578edee7d737045a41 (patch)
tree	8475a74fb159756896e90a802eca32e2545ed86d /search.php
parent	03a01ac31a16cf7f44e827db15b7483b0ec330cd (diff)
parent	c1ea47789989b08e919645d8b8133cfea0ad97c9 (diff)