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 "New Search";
# 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 "No records found.
";
exit;
}
if ( $row_count <= $results_per_page ) {
echo "{$row_count} records found.
";
} 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 "Viewing {$viewing} to {$row_count} of {$row_count} records found.
";
} else {
echo "Viewing {$viewing} to {$upto} of {$row_count} records found.
";
}
} else {
echo "Viewing 1 to {$results_per_page} of {$row_count} records found.
";
}
}
// Give the user an export button
$url_sql = urlencode($sql);
echo "";
# 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 <<
Court Case No.
A
D
|
State Case No. |
Name |
First name |
Last name |
Date of Birth |
Date Filed
A
D
|
Date Closed
A
D
|
Warrant Type |
Hearing Date |
Hearing Time |
Hearing Type |
Probation Start
A
D
|
Probation End
A
D
|
Probation Length |
In Jail?
A
D
|
Released To |
Bond Amount |
Bond Status |
Bond Type |
Bond Date |
Defense Attorney |
Charges |
Dockets |
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 " \n";
#foreach ( $row as $field => $value ) {
foreach ( $database_fields as $field ) {
if ( 'name' == $field && $akas_rows ) {
echo " | {$row[$field]} | \n";
} else {
echo " {$row[$field]} | \n";
}
}
echo " Charges | \n";
echo " Dockets | \n";
echo "
\n";
}
}
echo "";
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 "
Next Page>>";
} elseif ( $row_count > $results_per_page && $upto < $row_count ) {
echo "
Next Page >>";
}
echo <<
HTML;
?>