summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--.gitignore8
-rw-r--r--akas.php51
-rw-r--r--charges.php50
-rwxr-xr-xdaily_run.sh57
-rw-r--r--daily_run_report.php49
-rw-r--r--dockets.php51
-rw-r--r--export.php24
-rw-r--r--index.php196
-rw-r--r--interesting_defendants/disposition_groups86
-rw-r--r--interesting_defendants/excludable_charges438
-rw-r--r--interesting_defendants/fatal_dispositions71
-rw-r--r--interesting_defendants/interesting_defendants.php213
-rw-r--r--interesting_defendants/safe_dispositions43
-rwxr-xr-xmiami-dade_clerk_courts.py393
-rwxr-xr-xmiami_dade_clerk_courts.py612
-rw-r--r--search.php461
-rw-r--r--style.css87
17 files changed, 2492 insertions, 398 deletions
diff --git a/.gitignore b/.gitignore
index e482462..9b1463e 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,6 +1,4 @@
-addinfo_req
-case_req
-dockets_req
-clerk_fields
-possible_errors
+*.pyc
*.swp
+mdcc.log
+interesting_defendants/output
diff --git a/akas.php b/akas.php
new file mode 100644
index 0000000..f6e657b
--- /dev/null
+++ b/akas.php
@@ -0,0 +1,51 @@
+<?php
+
+echo <<<HTML
+<!DOCTYPE html>
+
+<html>
+<head>
+ <title>Miami-Dade Clerk of Courts: AKAs</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.');
+
+echo <<<HTML
+<table class="results_table">
+ <tr class="tablehead">
+ <th>ID</th>
+ <th>Case ID</th>
+ <th>Last name</th>
+ <th>First name</th>
+ <th>Middle name</th>
+ <th>Race</th>
+ <th>Sex</th>
+ </tr>
+HTML;
+
+echo "<h3>AKAs for case <a href='./search.php?case_id={$_REQUEST['case_id']}'><em>{$_GET['case_no']}</em></a></h3>";
+
+$st = $dbh->prepare("SELECT * FROM akas WHERE case_id = '{$_GET['case_id']}'");
+if ( $st->execute() ) {
+ $alt = 'alt';
+ while ( $row = $st->fetch(PDO::FETCH_ASSOC) ) {
+ $alt = $alt ? '' : 'alt';
+ echo " <tr class='$alt'>\n";
+ foreach ( $row as $field ) {
+ echo " <td>$field</td>\n";
+ }
+ echo " </tr>\n";
+ }
+}
+
+echo <<<HTML
+</table>
+
+</body>
+</html>
+HTML;
+
+?>
diff --git a/charges.php b/charges.php
new file mode 100644
index 0000000..e0eb95d
--- /dev/null
+++ b/charges.php
@@ -0,0 +1,50 @@
+<?php
+
+echo <<<HTML
+<!DOCTYPE html>
+
+<html>
+<head>
+ <title>Miami-Dade Clerk of Courts: Charges </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.');
+
+echo <<<HTML
+<table class="results_table">
+ <tr class="tablehead">
+ <th>ID</th>
+ <th>Case ID</th>
+ <th>Seq. No.</th>
+ <th>Charge</th>
+ <th>Charge Type</th>
+ <th>Disposition</th>
+ </tr>
+HTML;
+
+echo "<h3>Charges for case <a href='./search.php?case_id={$_REQUEST['case_id']}'><em>{$_GET['case_no']}</em></a></h3>";
+
+$st = $dbh->prepare("SELECT * FROM charges WHERE case_id = '{$_GET['case_id']}'");
+if ( $st->execute() ) {
+ $alt = 'alt';
+ while ( $row = $st->fetch(PDO::FETCH_ASSOC) ) {
+ $alt = $alt ? '' : 'alt';
+ echo " <tr class='$alt'>\n";
+ foreach ( $row as $field ) {
+ echo " <td>$field</td>\n";
+ }
+ echo " </tr>\n";
+ }
+}
+
+echo <<<HTML
+</table>
+
+</body>
+</html>
+HTML;
+
+?>
diff --git a/daily_run.sh b/daily_run.sh
new file mode 100755
index 0000000..94395cd
--- /dev/null
+++ b/daily_run.sh
@@ -0,0 +1,57 @@
+#!/bin/bash
+
+# This date is just for display. Since this script will get run at 10pm UTC
+# and we are in Eastern Time, set the date as yesterday.
+DATE=$(TZ=America/New_York date +'%A, %B %d, %Y, %l:%M%p')
+
+# Get the highest current court_case_no from the database
+YEAR=$(date +'%y')
+PASSWD=$(cat ../../mysql_passwd)
+QUERY="SELECT court_case_no FROM cases WHERE court_case_no LIKE 'f-$YEAR-%' ORDER BY court_case_no DESC LIMIT 250"
+CASE_NOS=$(mysql -u root -p$PASSWD -ss -e "$QUERY" mdcc)
+
+# Turn list into an array
+cases=($CASE_NOS)
+
+MAX_CASE_NO="${cases[0]}"
+
+# Here we go back over the latest cases to fill in gaps, since the case numbers
+# are not always entered sequentially on any given day.
+idx=1
+shift
+for case in ${cases[@]}
+do
+ SEQ_PAD=${case:5}
+ SEQ=$(echo $SEQ_PAD | sed 's/^0*//')
+ if [[ ! -z ${cases[$idx]} ]]
+ then
+ NEXT_SEQ_PAD=${cases[$idx]:5}
+ NEXT_SEQ=$(echo $NEXT_SEQ_PAD | sed 's/^0*//')
+ if [ $NEXT_SEQ != $((SEQ-1)) ]
+ then
+ let "gap = SEQ - NEXT_SEQ"
+ let "GAP_START = NEXT_SEQ + 1"
+ let "GAP_STOP = SEQ - 1"
+ ./miami_dade_clerk_courts.py $GAP_START $GAP_STOP $YEAR
+ fi
+ let "idx = idx + 1"
+ fi
+done
+
+CASE_SEQ_PAD=${MAX_CASE_NO:5}
+
+# The case number in the database has leading zeros. Strip them off.
+START=$(echo $CASE_SEQ_PAD | sed 's/^0*//')
+
+# Fetch all the latest entries
+./miami_dade_clerk_courts.py $START 0 $YEAR
+
+# Get the new highest court_case_no after fetching all the latest
+QUERY="SELECT court_case_no FROM cases WHERE court_case_no LIKE 'f-$YEAR-%' ORDER BY court_case_no DESC LIMIT 1"
+NEW_CASE_NO=$(mysql -u root -p$PASSWD -ss -e "$QUERY" mdcc)
+
+# Send a link with the results to some people
+RCPT_TO="udighe@gmail.com jinita.thakkar@gmail.com"
+SUBJECT="MDCC daily run for $DATE"
+BODY="http://dighelaw.com/mdcc/search.php?between=$MAX_CASE_NO:$NEW_CASE_NO"
+echo $BODY | mail -s "$SUBJECT" $RCPT_TO
diff --git a/daily_run_report.php b/daily_run_report.php
new file mode 100644
index 0000000..8cab7e9
--- /dev/null
+++ b/daily_run_report.php
@@ -0,0 +1,49 @@
+<?php
+
+echo <<<HTML
+<!DOCTYPE html>
+
+<html>
+<head>
+ <title>Miami-Dade Clerk of Courts: Daily run report</title>
+ <link rel='stylesheet' media='all' type='text/css' href='style.css' />
+</head>
+
+<body>
+
+<h3>MDCC daily run report</h3>
+HTML;
+
+$dbh = new PDO('mysql:host=localhost;dbname=mdcc', 'mdcc', 'Mdcc.');
+
+for ( $i=0; $i<7; $i++ ) {
+ $this_day = date("Y-m-d", strtotime("-{$i} days"));
+ $pretty_day = date("l, F j, Y", strtotime("-{$i} days"));
+ $sql_first_run = "SELECT count(court_case_no) AS cnt FROM cases WHERE DATE(date_entered) = '{$this_day}' AND TIME(date_entered) < '22:00:00'";
+ $sql_second_run = "SELECT count(court_case_no) AS cnt FROM cases WHERE DATE(date_entered) = '{$this_day}' AND TIME(date_entered) >= '22:00:00'";
+
+ $st_first_run = $dbh->prepare($sql_first_run);
+ if ( $st_first_run->execute() ) {
+ $first_run_count = $st_first_run->fetchColumn();
+ }
+ $st_second_run = $dbh->prepare($sql_second_run);
+ if ( $st_second_run->execute() ) {
+ $second_run_count = $st_second_run->fetchColumn();
+ }
+
+ echo <<<HTML
+<div class="pull">
+ <div>$pretty_day:</div>
+ <div class="pull_count">Noon pull: {$first_run_count}</div>
+ <div class="pull_count">6PM pull: {$second_run_count}</div>
+</div>
+HTML;
+
+}
+
+echo <<<HTML
+</body>
+</html>
+HTML;
+
+?>
diff --git a/dockets.php b/dockets.php
new file mode 100644
index 0000000..6cba902
--- /dev/null
+++ b/dockets.php
@@ -0,0 +1,51 @@
+<?php
+
+echo <<<HTML
+<!DOCTYPE html>
+
+<html>
+<head>
+ <title>Miami-Dade Clerk of Courts: Dockets</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.');
+
+echo <<<HTML
+<table class="results_table">
+ <tr class="tablehead">
+ <th>ID</th>
+ <th>Seq. No.</th>
+ <th>Date</th>
+ <th>Docket</th>
+ <th>Case ID</th>
+ </tr>
+HTML;
+
+
+echo "<h3>Dockets for case <a href='./search.php?case_id={$_REQUEST['case_id']}'><em>{$_GET['case_no']}</em></a></h3>";
+
+$st = $dbh->prepare("SELECT * FROM dockets WHERE case_id = '{$_GET['case_id']}'");
+if ( $st->execute() ) {
+ $alt = 'alt';
+ while ( $row = $st->fetch(PDO::FETCH_ASSOC) ) {
+ $alt = $alt ? '' : 'alt';
+ echo " <tr class='$alt'>\n";
+ foreach ( $row as $field ) {
+ echo " <td>$field</td>\n";
+ }
+ echo " </tr>\n";
+ }
+}
+
+echo <<<HTML
+</table>
+
+</body>
+</html>
+HTML;
+
+?>
diff --git a/export.php b/export.php
new file mode 100644
index 0000000..b9cdfac
--- /dev/null
+++ b/export.php
@@ -0,0 +1,24 @@
+<?php
+
+$dbh = new PDO('mysql:host=localhost;dbname=mdcc', 'mdcc', 'Mdcc.');
+
+$filename = '/var/www/dighelaw.com/www/mdcc/csv_exports/mdcc_export.csv';
+
+header('Content-Type: text/csv');
+header("Content-Disposition: attachment; filename={$filename}");
+header('Pragma: no-cache');
+
+$sql = $_REQUEST['sql'];
+$sql .= " INTO OUTFILE '{$filename}'";
+
+$st = $dbh->prepare($sql);
+if ( $st->execute() ) {
+ readfile($filename);
+} else {
+ $errors = $st->errorInfo();
+ print_r($errors);
+}
+
+unlink($filename);
+
+?>
diff --git a/index.php b/index.php
new file mode 100644
index 0000000..b84f90c
--- /dev/null
+++ b/index.php
@@ -0,0 +1,196 @@
+<!DOCTYPE html>
+
+<html lang="en">
+
+<head>
+ <meta http-equiv="Content-type" content="text/html;charset=UTF-8" />
+ <title>Miami-Dade Clerk of Courts: Search</title>
+ <link rel='stylesheet' media='all' type='text/css' href='style.css' />
+</head>
+
+<body id="case_search">
+
+<h2 style="text-align: center;">Miami-Dade Clerk of Courts case search</h2>
+
+<div id="content">
+
+ <div id="search_form">
+
+ <form id="search_cases" method="get" action="search.php">
+ <div>
+ <label for="case_years">Years</label>
+ <select id="case_years" name="case_years[]" multiple>
+<?php
+
+for ( $year = date("Y"); $year >= 1990; $year-- ) {
+ echo " <option value='$year'>$year</option>\n";
+}
+
+?>
+ </select>
+ </div>
+
+ <div>
+ <label for="case_num">Case #</label>
+ <input type="text" class="case_num" id="case_num" name="case_num" maxlength="6" />
+ to
+ <input type="text" class="case_num" id="case_num_to" name="case_num_to" maxlength="6" />
+ </div>
+
+ <div>
+ <label for="defendant">Defendant #</label>
+ <select id="defendant" name="defendant">
+ <option value='' selected='selected'> </option>
+<?php
+
+foreach ( range('A', 'Z') as $letter ) {
+ echo " <option value='$letter'>$letter</option>\n";
+}
+
+?>
+ </select>
+ </div>
+
+ <div>
+ <label for="name">Name</label>
+ <input type="text" id="name" name="name" />
+ </div>
+
+ <div>
+ <label for="charge">Charge</label>
+ <input type="text" id="charge" name="charge" />
+ </div>
+
+ <div>
+ <label for="disposition">Disposition</label>
+ <input type="text" id="disposition" name="disposition" />
+ </div>
+
+ <div>
+ <label for="docket">Docket</label>
+ <input type="text" id="docket" name="docket" />
+ </div>
+
+ <div>
+ <label for="in_jail">In jail?</label>
+ <select id="in_jail" name="in_jail">
+ <option value='' selected='selected'> </option>
+ <option value='Y'>Yes</option>
+ <option value='N'>No</option>
+ </select>
+ </div>
+
+ <div>
+ <label for="prob_given">Given probation?</label>
+ <input type="checkbox" id="prob_given" name="prob_given" />
+ </div>
+
+ <div>
+ <label for="prob_still">Still on probation?</label>
+ <input type="checkbox" id="prob_still" name="prob_still" />
+ </div>
+
+ <div>
+ <label for="prob_per_complete">Probation completed</label>
+ <select id="prob_per_complete" name="prob_per_complete">
+ <option value='' selected='selected'> </option>
+<?php
+
+for ( $per = 10; $per <= 100; $per += 10 ) {
+ echo " <option value='$per'>$per</option>\n";
+}
+
+?>
+ </select>%
+ </div>
+
+ <div>
+ <label for="prob_start_day">Probation start</label>
+ <select id="prob_start_day" name="prob_start_day">
+ <option value='' selected='selected'> </option>
+<?php
+
+for ( $day = 1; $day <= 31; $day++ ) {
+ echo " <option value='$day'>$day</option>\n";
+}
+
+?>
+ </select>
+ <select id="prob_start_month" name="prob_start_month">
+ <option value='' selected='selected'> </option>
+<?php
+
+for ( $month = 1; $month <= 12; $month++ ) {
+ echo " <option value='$month'>$month</option>\n";
+}
+
+?>
+ </select>
+ <select id="prob_start_year" name="prob_start_year">
+ <option value='' selected='selected'> </option>
+<?php
+
+for ( $year = date("Y"); $year >= 1990; $year-- ) {
+ echo " <option value='$year'>$year</option>\n";
+}
+
+?>
+ </select> (dd/mm/yyyy)
+ </div>
+
+ <div>
+ <label for="prob_end_day">Probation end</label>
+ <select id="prob_end_day" name="prob_end_day">
+ <option value='' selected='selected'> </option>
+<?php
+
+for ( $day = 1; $day <= 31; $day++ ) {
+ echo " <option value='$day'>$day</option>\n";
+}
+
+?>
+ </select>
+ <select id="prob_end_month" name="prob_end_month">
+ <option value='' selected='selected'> </option>
+<?php
+
+for ( $month = 1; $month <= 12; $month++ ) {
+ echo " <option value='$month'>$month</option>\n";
+}
+
+?>
+ </select>
+ <select id="prob_end_year" name="prob_end_year">
+ <option value='' selected='selected'> </option>
+<?php
+
+for ( $year = date("Y"); $year >= 1990; $year-- ) {
+ echo " <option value='$year'>$year</option>\n";
+}
+
+?>
+ </select> (dd/mm/yyyy)
+ </div>
+
+ <div>
+ <label for="case_closed">Case closed?</label>
+ <select id="case_closed" name="case_closed">
+ <option value='' selected='selected'> </option>
+ <option value='no'>No</option>
+ <option value='yes'>Yes</option>
+ </select>
+ </div>
+
+ <div id="form_submit">
+ <input type="submit" value="Search" />
+ </div>
+
+ </form>
+
+ </div> <!-- end #search_form -->
+
+</div> <!-- end #content -->
+
+</body>
+
+</html>
diff --git a/interesting_defendants/disposition_groups b/interesting_defendants/disposition_groups
new file mode 100644
index 0000000..e0aded4
--- /dev/null
+++ b/interesting_defendants/disposition_groups
@@ -0,0 +1,86 @@
+1,ACQUITTAL/INSANITY
+1,ACQUITTED BY COURT
+1,ACQUITTED BY JURY
+1,ADJ WH 364 TASC/C.C.
+1,ADJ WH 364 TASC/PROB
+1,ADJ WH AND SENT-TASC
+1,ADJ WH C.C./ PROB
+1,ADJ WH COND:BOOT CMP
+1,ADJ WH PR SP CN TASC
+1,ADJ WH SNT/SORT FAIL
+1,ADJ WH SNT/SRT VOL T
+1,ADJ WH W/PROB-SORT
+1,ADJ WH-SORT/GRAD
+1,ADJ WH-SPL SENT-SORT
+1,ADJ WH/CTS/FINE/COST
+1,ADJ WHELD/BOOT CAMP
+1,ADJ WITHHELD/ SENT
+1,ADJ WITHHELD/PROB
+1,DISCHARGE
+1,DISM - SPEEDY TRIAL
+1,DISM W/O PREJUDICE
+1,DISM WITH PREJUDICE
+1,DISM-LACK OF PROS
+1,DISM/NO PROB CAUSE
+1,DISMISS/MODIFY
+1,DISMISS/TERM
+1,DISMISSED
+1,DISMISSED BY COURT
+1,DISMISSED-WITNESS
+1,VACATE PER MANDATE
+1,WH ADJ - JAIL TIME
+1,WH ADJ - RESTITUTION
+1,WH ADJ / CTS
+1,WH ADJ SP CND-PEG
+1,WH ADJ W/FINE & COST
+1,WH ADJ-C.C./SPC COND
+1,WH ADJ-COMM SERVICE
+1,WH ADJ-PROB SP COND
+1,WH ADJ-PROB W/CM SRV
+1,WH ADJ-SAL ARMY PROB
+1,WH ADJ-SPLIT SENT
+1,WH ADJ-SUSP ENT SENT
+1,WH ADJ-YOUTHFUL OFFN
+1,WH ADJ/PROB CONSEC
+1,WH ADJ/PROBATION
+1,WH ADJ/SENT SORT
+2,DEF PROSECUTION PGM
+2,DEFER DRUG PROGRAM
+2,DEFER FIRE ARM PGM
+2,DIV DEFR DRUG PGM
+2,DRUG COURT PRG PLEA
+2,NOLLE PROS
+2,NOLLE PROS - ADMIN
+2,NOLLE PROS - REST
+2,NOLLE PROS JDP
+2,NOLLE PROS- USAO/INS
+2,NOLLE PROS-ADM/WRNT
+2,NOLLE PROS-COMP 'DVI
+2,NOLLE PROS-COMP PTI
+2,NOLLE PROS-DRUG PGM
+2,NOLLE PROS-F/A PGM
+2,NOLLE PROS-WITNESS
+3,ABANDONED
+3,CASE FILED IN ERROR
+3,CIVIL PENALTY CLOSED
+3,CLOSED ADMIN JUDGE
+3,CLOSED/PER ADM MEMO
+3,NO ACTION
+3,NO INFORMATION
+3,NO TRUE BILL
+4,NO ACTION - FIL CTY
+4,NO ACTION - FIL TRAF
+4,NO ACTION FILED TRF
+4,NOLLE PROS FILED TRF
+4,NOLLE PROS/FILED CC
+4,NON JUDICIAL ACTION
+4,PETITION WITHDRAWN
+4,PROB RESTRD/REINSTAT
+4,PROB TERM/MOD/REV
+4,PROB W/COMM SERVICE
+4,PROBATION MODIFIED
+4,PROBATION REVOKED
+4,PROBATION TERMINATED
+4,VOLUNTARILY WTHDRAWN
+4,WITHDRAWN/TERM
+4,WITHDRAWN/TERMINATED
diff --git a/interesting_defendants/excludable_charges b/interesting_defendants/excludable_charges
new file mode 100644
index 0000000..f2847f7
--- /dev/null
+++ b/interesting_defendants/excludable_charges
@@ -0,0 +1,438 @@
+ARSON
+ARSON 1D/CONSPIRACY
+ARSON 1ST DEGREE
+ARSON 1ST/ATTEMPT
+ARSON 2ND DEGREE
+ARSON/2D/CONSPIRE
+ARSON/2D/SOLICIT
+ARSON/2ND/ATTEMPT
+ARSON/BODILY HARM
+ARSON/GREAT BOD HARM
+ASS AGG/PO/FF/ATTEMP
+ASS/AGG/BY PRISONER
+ASS/AGG/OFFICIAL EMP
+ASS/AGG/RELIG INSTI
+ASS/AGGR/CONSP
+ASS/AGG/RELIG INSTI
+ASS/AGGR/CONSP
+ASSAULT/AGG/DW/MASK
+ASSAULT/AGG/DWEAP
+ASSAULT/AGG/ELDER/DW
+ASSAULT/AGG/FELONY
+ASSAULT/AGG/FIREARM
+ASSAULT/AGG/LEO
+ASSAULT/AGG/POL/FIRE
+ASSAULT/AGG/PRINCIPL
+ASSAULT/AGGRAVATED
+ASSLT/AGG/6/FIREAR
+ASSLT/AGG/FEL/F ARM
+ASSLT/AGG/LEO/FA/ATT
+ASSLT/AGG/LEO/FIRERM
+ATT 2D MURDER/FIREAR
+ATT FEL MUR/DLY WEA
+ATT FEL MUR/FIREARM
+ATT FEL MURDER - PBL
+ATT FEL MURDER/IN/AR
+ATT FEL MURDER/INJUR
+ATT SEX ACTIV/MINOR
+ATT SOL 1ST DEG FEL
+ATT SOL 2ND DEG FEL
+ATTEMPT FEL MURDER
+BATT/AGG/BODHRM/WEAP
+BATT/AGG/BY PRISONER
+BATT/AGG/DDY WPN/ATT
+BATT/AGG/HARM/DWEAP
+BATT/AGG/OFFICIAL
+BATT/AGG/PREJ/WEA/HR
+BATT/AGG/WEA/FA/MASK
+BATT/AGG/WEAP/GANG
+BATT/AGG/WEAP/PRE/AT
+BATTERY/AGG/ATT
+BATTERY/AGG/BOD HARM
+BATTERY/AGG/CONSPIRE
+BATTERY/AGG/DWEAP
+BATTERY/AGG/ELDERLY
+BATTERY/AGG/FIREARM
+BATTERY/AGG/HRM/WEAP
+BATTERY/AGG/LEO
+BATTERY/AGG/POL/FIRE
+BATTERY/AGG/PREGNANT
+BATTERY/AGG/PRIN
+BATTERY/AGGRAVATED
+BOMB/ATT/FALS RPT/ST
+BOMB/BODILY HARM
+BOMB/DESTR DEV/CONSP
+BOMB/DESTR DEV/SOLIC
+BOMB/DISRUPTION
+BOMB/FALSE REPORT
+BOMB/FALSE RPT/STATE
+BOMB/GREAT BOD HARM
+BOMB/MAKE/POSN/THROW
+BOMB/THREAT TO THROW
+BUR/ASLT/BAT/ARD/SOL
+BUR/ASS/BAT/ARMD/CON
+BURG/ARMED/SOLICIT
+BURG/ASLT/BATT - PBL
+BURG/ASS/BAT/>7/1/01
+BURG/ASS/BAT/ARM/ATT
+BURG/DWL/STR/DAM-PBL
+BURG/DWL/STR/VEH-PBL
+BURG/OCC DWELL/SOLIC
+BURG/OCC DWL/MASK
+BURG/OCC/DWELL/ATT
+BURG/OCC/MASKED
+BURG/UNOC DWEL/GANG
+BURG/UNOCC DWEL/PREJ
+BURG/UNOCC DWELL/ATT
+BURG/UNOCC DWELL/CON
+BURG/UNOCC/DWL/MASK
+BURGLARY/ARMED - PBL
+BURGLARY/ARMED/ATT
+BURGLARY/ARMED/CONSP
+BURGLARY/ASSLT/ARMED
+BURGLARY/DWELL/CONSP
+BURGLARY/OCC/DWELL
+BURGLARY/UNOCC D/ATT
+BURGLARY/UNOCC DWELL
+BURGLARY/UNOCC/DWELL
+BURGLARY/W ASSLT/ATT
+BUY/SELL MINOR/SEX T
+CANN/TRF/10K>LBS
+CANN/TRF/25-2000LBS
+CANN/TRF/2K-10KLBS
+CANN/TRF/ARMD/ATTEMT
+CANNABIS/TRAFFICK
+CANNABIS/TRFK/10
+CANNABIS/TRFK/2-10K
+CANNABIS/TRFK/50-2K
+CANNABIS/TRFK/ARMED
+CANNABIS/TRFK/ATT
+CHILD AB/NO HARM/ATT
+CHILD ABU/NO HARM/FA
+CHILD ABUSE/AGG
+CHILD ABUSE/AGGRAV
+CHILD ABUSE/ATT
+CHILD ABUSE/BOD HARM
+CHILD ABUSE/FAIL RPT
+CHILD ABUSE/IMPREGNA
+CHILD ABUSE/INJURY
+CHILD ABUSE/NO HARM
+CHILD PORN/TRANSMIT
+CHILD/INFLICT PAIN
+CHLD AB/AGG/GRT HARM
+CHLD AB/HRM/AG BT/FA
+CHLD ABS/FAIL TO REP
+CHLD ABS/FALSE REPT
+CHLD ABS/NEG/FLS RPT
+CHLD ABS/REQ REPORT
+CHLD ABUSE/FALSE RPT
+CHLD NEG/GREAT HARM
+CHLD NEG/NO GRT HRM
+CHLD PORN/FRM O/S FL
+CHILD PORN/TRANSMIT
+CHLD AB/AGG/GRT HARM
+CHLD AB/HRM/AG BT/FA
+CHLD PORN/FRM O/S FL
+COCAINE/TRAF/CONSP
+COCAINE/TRAF/CONSPIR
+COCAINE/TRAFF/CONSP
+COCAINE/TRAFF/SOLIC
+COCAINE/TRAFFIC/ATT
+COCAINE/TRAFFICK
+COCAINE/TRF/200-400
+COCAINE/TRFK/10
+COCAINE/TRFK/2-10K
+COCAINE/TRFK/40
+COCAINE/TRFK/400-2K
+COCAINE/TRFK/400/ARM
+COKE/TRAF/40/CONSP
+COKE/TRAF/400>/<150K
+COKE/TRAFF/200>/<400
+COKE/TRAFF/28>/<200
+COKE/TRAFFICK/40
+COKE/TRF/150-300/PBL
+COKE/TRFK/28>/<150K
+COKE/TRFK/ARMED
+COKE/TRFK/ATTEMPT
+COKE/TRFK/CONSPIRACY
+COKE/TRFK/SOLICIT
+COKE/TRK/150K>/ARMED
+COMPUTER PORNOGRAPHY
+DRUGS/TRAFFICK/ATT
+DRUGS/TRAFFICKING
+DRUGS/TRFK/CONSPIRE
+DUI/MANSLAUGHTER
+DWI/MANSLAUGHTER
+ENG SEX/CHILD/ATTEMP
+ENGAGE/CHILD FAM SEX
+EXPLO/POSN/W/O/LICEN
+EXPLOSIVE/POSN
+EXPLOSIVE/TRANSPORT
+EXPLOSIVE/UNL POSN
+ECSTASY/TRF/10>/<200
+FLUNI/TRF/14>/<28GR
+FLUNI/TRF/4G>/<14GR
+FLUNOTRAZ/TRAFF/CONS
+GBL/TRAFF/1K><5K
+GBL/TRAFFICK/10K>
+FEL MUR/1ST/LEO/ATT
+HOME/INVASION
+HM INV/ROBB/W/O WEAP
+ILL DRG/TRAF/28/SOLI
+ILL DRG/TRF/30K>/DTH
+ILL DRG/TRFK/ARMED
+ILL DRGS/TRF/14>/<28
+ILL DRGS/TRF/4>/<14
+ILL DRGS/TRF/4>/<14G
+ILL DRUGS/TRAF/ARMED
+ILL DRUGS/TRAFF/2
+ILL DRUGS/TRAFFICK
+ILL DRUGS/TRF/ARM/AT
+ILL DRUGS/TRF/ATT
+ILL DRUGS/TRF/ATTEMP
+ILL DRUGS/TRFK/2
+ILL DRUGS/TRFK/30K>
+ILL DRUGS/TRFK/CONSP
+ILLDRG/TRF/28G>/<30K
+ILLDRG/TRF14>/<28SOL
+KID/CHLD<13/ARMD/ATT
+KIDNAP/-13/LL/SB
+KIDNAP/CHILD-13/ATT
+KIDNAP/FA/AGGB/CONSP
+KIDNAP/SOLIC
+KIDNAP/WEAP/FA/AGGB
+KIDNAP/WEAPON/CONSP
+KIDNAP/WP/FA/AGB/LEO
+KIDNAP/WP/FA/AGGB/AT
+KIDNAPPING Ð PBL
+KIDNAPPING/ATTEMPT
+KIDNAPPING/CHLD/GANG
+KIDNAPPING/CONSPIRE
+KIDNAPPING/DWEAP/LEO
+KIDNAPPING/LEO Ð PBL
+KIDNAPPING/MASKED
+KIDNAPPING/WEAP/AGGB
+KIDNAPPING/WEAPN/ATT
+L&L; ASS/CHILD/SOLICT
+L&L; ASSAULT/CHILD
+L&L; ASSLT/CHILD/ATT
+L&L; BATT ON CHILD
+L&L; BATT/ELD/DIS/ADL
+L&L; BEHAVIOR
+L&L; CD/<16/DF<18
+L&L; CHILD/ATT
+L&L; CHLD 12-16/DF<18
+L&L; CHLD <16/ARMED
+L&L; CHLD<12/D18>/ATT
+L&L; CHLD<12/DEF 18>
+L&L; CONDUCT/CHLD <16
+L&L; EXH/CLD<16/DF<18
+L&L; EXHI/ELD/DIS/ADL
+L&L; EXHIB <16 BY 18>
+L&L; EXHIB ON<16BY<18
+L&L; EXHIB/CHLD/<16
+L&L; EXHIB/CHLD/ONLIN
+L&L; MOL CHLD 12-16
+L&L; MOL/ELD/DISAB/AD
+L&L; MOLEST/CHILD/<12
+L&L; ON CHILD<16/ATTM
+L&L;/CH<12/DEF<18/ATT
+L&L;/CHD <12/DEF<18
+L&L;/CHILD/ARMED
+L&L;/EXHIB/CORR/FACIL
+L&LCHLD; <12/D18>/ATT
+LWD ACT/<16/<10/99
+LWD ASLT CHLD/<10/99
+LWD ASLT/<16/<10/99
+LWD ASLT/CHLD/<10/99
+MEN HLTH/SEX MISCND
+MANS/AGG/ELD/DIS ADL
+MANS/D WEAP/ATTEMPT
+MANSL/AGG/CHILD
+MANSLAUGHTER
+MANSLAUGHTER/ATTEMPT
+MANSLAUGHTER/DWEAP
+MANSLAUGHTER/LEO
+MANSLAUGHTER/LEO/ATT
+MR 2D/LEO/AT/DW/FA/B
+MUR 1D/DLY WEA/CONSP
+MUR 2/ATT/WEA/PREJ
+MUR 2/WEAP/AG BAT/AT
+MUR/PREMED/ATTEMPT
+MUR2D/ATTMP/DDLY WPN
+MURDER 1ST DEG/ATT
+MURDER 1ST DEG/CONSP
+MURDER 1ST DEG/LEO
+MURDER 1ST DEGREE
+MURDER 1ST/DWEAP/AT
+MURDER 1ST/DWEAP/ATT
+MURDER 1ST/PRIN/ATT
+MURDER 1ST/SOLICIT
+MURDER 2 DEG/FEL/PBL
+MURDER 2 DEG/LEO/PBL
+MURDER 2/FIREARM/ATT
+MURDER 2D/LEO/ATT/DW
+MURDER 2ND DEG - PBL
+MURDER 2ND DEG/ATT
+MURDER 2ND DEG/DWEAP
+MURDER 2ND DEG/PRIN
+MURDER 2ND DEGREE
+MURDER 2ND/ATT/WEAPN
+MURDER 2ND/DWEAP/LEO
+MURDER 2ND/FIREARM
+MURDER 2ND/LEO/ATT
+MURDER 2ND/PRIN/ATT
+MURDER 3RD DEG/ATT
+MURDER 3RD DEG/LEO
+MURDER 3RD DEGREE
+MURDER 3RD/DEA WEAPN
+MURDER/2D/CONSPIRAC
+MURDER/ATT/POL/FIRE
+MURDER/PREMED/ATT/FA
+OBSCENEMAT/MIN/ATT
+OBSCENE MAT/MINOR
+OBSCENE SHOW/MINOR
+ORG SCEME DEFR/CONSP
+ORG SCH FRD/50 ATT
+ORG SCHEME TO DEFRD
+ORG SCHM DEF/CONSPIR
+ORGANIZED FRAUD
+ORGANIZED FRAUD/50
+ORGANIZED FRD/0-20K
+ORGANIZED FRD/20-50K
+PCP/TRAF/28GR>/<200G
+PCP/TRAFF/400GR>
+PCP/TRAFFICK/40
+PCP/TRF/200G>/<400G
+PCP/TRFK/CONSPIRE
+PHEN/TRAFF/CONSP/ARM
+PHEN/TRAFF/CONSPIRE
+PHEN/TRF/10G><200G/A
+PHEN/TRF/10G><200GR
+PHEN/TRF/10GR>/SOLIC
+PHEN/TRF/200G><400G
+PHEN/TRF/400G>
+PHEN/TRF/ARMED
+PHENETHY/TRAFF/ARMED
+PREMED/MURDER1/LEO/A
+PROCUR -18 PROST/ATT
+PROCUR MINOR
+PROST/PROC <18/SOLIC
+PROST/PROCURE UN/18
+PROST/PROCURE<18/ARM
+RBRY/ARM/FA/DW - PBL
+RBRY/HM INV/FA - PBL
+ROB/HM INV/AGG BATT
+ROB/HOME INV/ARM/ATT
+ROB/SNAT/WEA/ATTEMPT
+ROBB/ARM/SOLIC
+ROBB/ARMED/ATT/MASK
+ROBB/ARMED/PREJUDICE
+ROBB/CARJACK/ARM/ATT
+ROBB/CARJACK/ATTEMPT
+ROBB/CARJACK/CONSPIR
+ROBB/CRJCK/ARM - PBL
+ROBB/HOME NV/ARM/CO
+ROBB/HOME INV/ATT
+ROBB/HOME INV/CONSP
+ROBB/HOME INVA/SOLIC
+ROBB/SA/W/A BATT/ATT
+ROBB/SDN/SNTCH/ATT
+ROBB/SNAT/F/A/DLY WE
+ROBB/STR/ARM/MASKED
+ROBB/STR/ATT/W/PREJU
+ROBB/STRARM/MASK/ATT
+ROBB/STRGARM/MASK
+ROBB/SUDDEN SNATCH
+ROBB/W/AGG BATT
+ROBB/W/AGG BATT
+ROBBERY
+ROBBERY/ARMED/ATT
+ROBBERY/ARMED/CONSP
+ROBBERY/ARMED/MASK
+ROBBERY/ARMED/PRIN
+ROBBERY/ARMED/WEAPON
+ROBBERY/CARJACK/ATT
+ROBBERY/CARJACKING
+ROBBERY/DWEAP/ATT
+ROBBERY/FORCE
+ROBBERY/HM INV/ARMED
+ROBBERY/SA/SOLICIT
+ROBBERY/STRARM/CONSP
+ROBBERY/STRARM/PRIN
+ROBBERY/STRGARM/PREJ
+ROBBERY/STRNGARM/ATT
+ROBBERY/STRONGARM
+ROBBRY/ARMD/PRIN/ATT
+SEXACT/FAMCHLD/ATT
+SEXACT/W/CHILD/SOLI
+SEX ACTIVITY/MINOR
+SEX BAT/BY THREATS
+SEX BAT/DWEAP/ATT
+SEXBAT/MULTPERP/F1
+SEXBAT/MULTPERP/F2
+SEXBAT/NOFORCE/ATT
+SEXBAT/NOHRM/ARMED
+SEX BAT/ON MINOR/ADT
+SEX BAT/ON MINOR/ATT
+SEX BAT/ON MINOR/MNR
+SEX BAT/THREATS/ATT
+SEX BATT/1/MENTAL
+SEX BATT/12>/COERCE
+SEX BATT/ARM/ATT/GNG
+SEX BATT/ARMED/GANG
+SEX BATT/BY LEO/CO
+SEX BATT/COERCE
+SEX BATT/FORCE/ATT
+SEX BATT/HLPLESS/ATT
+SEX BATT/LEO/ARM/ATT
+SEX BATT/LEO/CO/ARMD
+SEX BATT/MINOR/SOLIC
+SEX BATT/NO SER INJU
+SEX BATT/PHYS INCAP
+SEX BATT/VIC HELPLES
+SEX BATT/WEA/SOLICIT
+SEX BATTERY/ARMED
+SEX BATTERY/DRUGGED
+SEX OFF/CHNG ADDR
+SEX OFF/REP REQUIRED
+SEX OFF/RESD VIOL
+SEX OFFN/REG/COMPLY
+SEX OFFND/FAIL TO RE
+SEX OFFNDR/HAR/CON
+SEX OFND/PRED/RES/PR
+SEX OFND/RES VIOL
+SEX OFND/RPT STATE
+SEX OFND/VIOL REGIST
+SEX PERF/CH/PRO/ATT
+SEX PERF/CHILD/ATTEM
+SEX PERF/CHILD/POSN
+SEX PERF/CHILD/POSSN
+SEX PERF/CHILD/PROMO
+SEX PERF/CHILD/PROMT
+SEX PERF/CHILD/USE
+SEX W/FAM CHLD/ARMED
+SEXBAT/NO SER INJ AT
+SEXBAT/SER INJ/ATT
+SEXBAT/WEA/SER INJ/A
+SEXBAT/WEAP/SER INJ
+SEXBATT/NO SER INJ/C
+SEXBATT/NO SER INJ/G
+SOLIC/CHILD FAM SEX
+SOLIC/CHILD SEX ATT
+STALK/AGG/CYBER
+STALK/AGGRAV/MINOR
+STALKING
+STALKING/AGG/CT ORDR
+STALKING/AGG/FIREARM
+STALKING/AGG/PREJUDI
+STALKING/AGGRAVATED
+VID VOY/18> RESP <16
+VID VOY/24>/CHILD<16
+VIDEO VOYEUR/7/1/12
+VIDEO VOYEUR/DISSEM
+VIDEO VOYEURISM/ATT
+VIDEO VOYEURISM/D>18
+VIO INJ PRO AGT STAL
+VIOL INJ/DOM VIOLENC
+VOYEURISM
diff --git a/interesting_defendants/fatal_dispositions b/interesting_defendants/fatal_dispositions
new file mode 100644
index 0000000..c809fbe
--- /dev/null
+++ b/interesting_defendants/fatal_dispositions
@@ -0,0 +1,71 @@
+ADJ DEL COM HRS/FEL
+ADJ DEL COMM CTL/FEL
+ADJ DELINQ-COMT DJJ
+ADJ DELINQ PROB/FEL
+ADJ DEQ CC/PROB/FEL
+ADJUDGED DELINQUENT
+ADJUDGED GUILTY
+CNV-364 TASC/364 SRT
+CNV 364 TASC/COM CNT
+CNV/PRB/SPL SNT/CONC
+CNV/PROB SP CND SORT
+CNV/SNT-SORT FAILED
+CNV & SNT-SORT GRAD
+CNV/SNT-SORT/TRM VOL
+CONV 364 TASC/PROB
+CONV AND SENT
+CONV C.C./PROBATION
+CONV-C.C./SPEC COND
+CONV - COMM SERVICE
+CONV CONC-PROB CONS
+CONV/CTS/FINE/COST
+CONV FN/CST-SUSP POR
+CONVICTION/BOOT CAMP
+CONVICTION - JAIL
+CONVICTION-JAIL/FINE
+CONVICTION W/FINE
+CONVICTION W/PROB
+CONV PRB SP CD HART
+CONV PROB/BOOT CAMP
+CONV - PROB CONSEC
+CONV PROB SP CD/TASC
+CONV PROB SP CND-PEG
+CONV-PROB SPEC COND
+CONV PROB/SPLIT SENT
+CONV- REHAB PROGRAM
+CONV - RESTITUTION
+CONV/SALV ARMY PROB
+CONV & SENT-CONCUR
+CONV & SENT-CONSEC
+CONV & SENT-SORT
+CONV-SENT/SUSP POR
+CONV & SENT W/TASC
+CONV/SPLIT SENTENCE
+CONV SPLIT SENT-SORT
+CONV-SUSP ENTRY SENT
+CONV W/COMM CONTROL
+CONV W/CTS
+CONV W/FINE & COST
+CONV W/FINE OR SENT
+CONV W/FINE SUSP SEN
+CONV-YOUTHFUL OFFNDR
+DECEASED
+D. V. I. PROGRAM
+EXTRADITED
+FINDING OF GUILT
+FND/GUILT \\PROB VIOL
+FND INCOMP\\PROB VIOL
+FW QUASHED/ABANDONED
+FW QUASHED/EXTRADITE
+JRA/ADJ SAFE TO REL
+JRA/ADJ TO REM COMMT
+ON APPEAL
+SVP/ADJ SAFE TO REL
+SVP/ADJ TO REMAIN CO
+TRANS-ANOTHER AGENCY
+TRANSFER-COUNTY CRT
+TRANSFER TO CIRC CRT
+TRANSFER TO JUVENILE
+TRANSFER TO TRAFFIC
+TRANSF TO OTHER CASE
+TRANS OTHER JURIS
diff --git a/interesting_defendants/interesting_defendants.php b/interesting_defendants/interesting_defendants.php
new file mode 100644
index 0000000..485eb8d
--- /dev/null
+++ b/interesting_defendants/interesting_defendants.php
@@ -0,0 +1,213 @@
+<?php
+
+error_reporting(E_ALL ^ E_NOTICE);
+
+$dbh = new PDO('mysql:host=localhost;dbname=mdcc', 'mdcc', 'Mdcc.');
+
+$fatal_dispositions = file('./fatal_dispositions', FILE_IGNORE_NEW_LINES);
+$excludable_charges = file('./excludable_charges', FILE_IGNORE_NEW_LINES);
+$safe_dispositions = file('./safe_dispositions', FILE_IGNORE_NEW_LINES);
+$disposition_groups = file('./disposition_groups', FILE_IGNORE_NEW_LINES);
+
+// CSV files
+$def_no_grp_csv = fopen('./output/interesting_defendants_no_group.csv', 'w');
+$def_grp1_csv = fopen('./output/interesting_defendants_group_1.csv', 'w');
+$def_grp2_csv = fopen('./output/interesting_defendants_group_2.csv', 'w');
+$def_grp3_csv = fopen('./output/interesting_defendants_group_3.csv', 'w');
+$def_grp4_csv = fopen('./output/interesting_defendants_group_4.csv', 'w');
+$def_multiple_cases_csv = fopen('./output/interesting_defendants_with_multiple_cases.csv', 'w');
+$def_akas_csv = fopen('./output/interesting_defendants_with_akas.csv', 'w');
+$def_akas_multiple_cases_csv = fopen('./output/interesting_defendants_with_akas_and_multiple_cases.csv', 'w');
+
+// HTML files
+$def_no_grp_html = fopen('./output/interesting_defendants_no_group.html', 'w');
+$def_grp1_html = fopen('./output/interesting_defendants_group_1.html', 'w');
+$def_grp2_html = fopen('./output/interesting_defendants_group_2.html', 'w');
+$def_grp3_html = fopen('./output/interesting_defendants_group_3.html', 'w');
+$def_grp4_html = fopen('./output/interesting_defendants_group_4.html', 'w');
+$def_multiple_cases_html = fopen('./output/interesting_defendants_with_multiple_cases.html', 'w');
+$def_akas_html = fopen('./output/interesting_defendants_with_akas.html', 'w');
+$def_akas_multiple_cases_html = fopen('./output/interesting_defendants_with_akas_and_multiple_cases.html', 'w');
+
+// HTML template start
+$html_start = <<<HTML
+<html>
+<head>
+ <title>Interesting defendants</title>
+</head>
+<body>
+<table border="1">
+HTML;
+
+// HTML template end
+$html_end = <<<HTML
+</table>
+</body>
+</html>
+HTML;
+
+// Initialize the HTML files
+fwrite($def_no_grp_html, $html_start);
+fwrite($def_grp1_html, $html_start);
+fwrite($def_grp2_html, $html_start);
+fwrite($def_grp3_html, $html_start);
+fwrite($def_grp4_html, $html_start);
+fwrite($def_multiple_cases_html, $html_start);
+fwrite($def_akas_html, $html_start);
+
+$sql = "SELECT * FROM cases";
+
+$st = $dbh->prepare($sql);
+if ( $st->execute() ) {
+ while ( $case = $st->fetch(PDO::FETCH_ASSOC) ) {
+
+ $has_multiple_cases = false;
+ $has_akas = false;
+
+ $defendant_csv = array(
+ $case['court_case_no'],
+ $case['name'],
+ $case['date_birth'],
+ );
+
+ $defendant_html = <<<HTML
+
+<tr>
+ <td>
+ <a href='http://li554-21.members.linode.com/search.php?case_id={$case['id']}'>{$case['court_case_no']}</a>
+ </td>
+ <td>{$case['name']}</td>
+ <td>{$case['date_birth']}</td>
+ <td>[<a href='http://li554-21.members.linode.com/charges.php?case_id={$case['id']}&case_no={$case['court_case_no']}'>charges</a>]</td>
+</tr>
+
+HTML;
+
+ $sql_charge = "SELECT * FROM charges WHERE case_id = '{$case['id']}'";
+ $st_charge = $dbh->prepare($sql_charge);
+ $st_charge->execute();
+ $charges = $st_charge->fetchAll(PDO::FETCH_ASSOC);
+
+ // The default group is no group (i.e. 0)
+ $disposition_group = 0;
+
+ foreach ( $charges as $charge ) {
+
+ // Figure out into which, if any, disposition group
+ // this charge puts the case
+ foreach ( $disposition_groups as $group_and_disposition ) {
+ list($group,$disposition) = explode(',', $group_and_disposition);
+ if ( $charge['disposition'] == $disposition ) {
+ // Only move a case into a higher group, never backward
+ if ( $disposition_group == '0' || $group < $disposition_group ) {
+ $disposition_group = $group;
+ }
+
+ }
+ }
+
+ // Don't process this case if there is an empty disposition
+ if ( ! trim($charge['disposition']) ) {
+ continue(2);
+ }
+
+ // If the disposition is a "fatal" one then go to next case
+ if ( in_array( $charge['disposition'], $fatal_dispositions) ) {
+ continue(2);
+ }
+
+ // If the charge is excludable and the disposition is not a safe one then go to the next case
+ if ( in_array($charge['charge'], $excludable_charges) && ! in_array($charge['disposition'], $safe_dispositions) ) {
+ continue(2);
+ }
+
+ }
+
+ // Does this person have multiple cases, based on the same name and date of birth?
+ $sql_def = "SELECT count(*) from cases WHERE name = '{$case['name']}' AND date_birth = '{$case['date_birth']}' AND id != '{$case['id']}'";
+ $st_def = $dbh->prepare($sql_def);
+ $st_def->execute();
+ $case_count = $st_def->fetchColumn();
+ if ( $case_count != '0' ) {
+ $has_multiple_cases = true;
+ }
+
+ // Does this person has any AKAs?
+ $sql_akas = "SELECT count(*) FROM akas WHERE case_id = '{$case['id']}'";
+ $st_akas = $dbh->prepare($sql_akas);
+ $st_akas->execute();
+ if ( $st_akas->fetchColumn() != '0' ) {
+ $has_akas = true;
+ }
+
+ if ( $has_multiple_cases && ! $has_akas ) {
+ fputcsv($def_multiple_cases_csv, $defendant_csv);
+ fwrite($def_multiple_cases_html, $defendant_html);
+ }
+
+ if ( $has_akas && ! $has_multiple_cases ) {
+ fputcsv($def_akas_csv, $defendant_csv);
+ fwrite($def_akas_html, $defendant_html);
+ }
+
+ if ( $has_multiple_cases && $has_akas ) {
+ fputcsv($def_akas_multiple_cases_csv, $defendant_csv);
+ fwrite($def_akas_multiple_cases_html, $defendant_html);
+ }
+
+
+ // The highest quality list
+ if ( ! $has_multiple_cases && ! $has_akas ) {
+ switch ( $disposition_group ) {
+ case '1':
+ fputcsv($def_grp1_csv, $defendant_csv);
+ fwrite($def_grp1_html, $defendant_html);
+ break;
+ case '2':
+ fputcsv($def_grp2_csv, $defendant_csv);
+ fwrite($def_grp2_html, $defendant_html);
+ case '3':
+ fputcsv($def_grp3_csv, $defendant_csv);
+ fwrite($def_grp3_html, $defendant_html);
+ break;
+ case '4':
+ fputcsv($def_grp4_csv, $defendant_csv);
+ fwrite($def_grp4_html, $defendant_html);
+ break;
+ default:
+ fputcsv($def_no_grp_csv, $defendant_csv);
+ fwrite($def_no_grp_html, $defendant_html);
+ }
+ }
+
+ }
+}
+
+// Finalize HTML files
+fwrite($def_no_grp_html, $html_stop);
+fwrite($def_grp1_html, $html_stop);
+fwrite($def_grp2_html, $html_stop);
+fwrite($def_grp3_html, $html_stop);
+fwrite($def_grp4_html, $html_stop);
+fwrite($def_multiple_cases_html, $html_stop);
+fwrite($def_akas_html, $html_stop);
+fwrite($def_akas_multiple_cases_html, $html_stop);
+
+fclose($def_no_group_csv);
+fclose($def_grp1_csv);
+fclose($def_grp2_csv);
+fclose($def_grp3_csv);
+fclose($def_grp4_csv);
+fclose($def_multiple_cases_csv);
+fclose($def_akas_csv);
+fclose($def_akas_multiple_cases_csv);
+fclose($def_no_group_html);
+fclose($def_grp1_html);
+fclose($def_grp2_html);
+fclose($def_grp3_html);
+fclose($def_grp4_html);
+fclose($def_multiple_cases_html);
+fclose($def_akas_html);
+fclose($def_akas_multiple_cases_html);
+
+?>
diff --git a/interesting_defendants/safe_dispositions b/interesting_defendants/safe_dispositions
new file mode 100644
index 0000000..4be60b1
--- /dev/null
+++ b/interesting_defendants/safe_dispositions
@@ -0,0 +1,43 @@
+ABANDONED
+ACQUITTAL/INSANITY
+ACQUITTED BY COURT
+ACQUITTED BY JURY
+CASE FILED IN ERROR
+CLOSED ADMIN JUDGE
+CLOSED/PER ADM MEMO
+DEF PROSECUTION PGM
+DEFER DRUG PROGRAM
+DEFER FIRE ARM PGM
+DISCHARGE
+DISM - SPEEDY TRIAL
+DISM W/O PREJUDICE
+DISM WITH PREJUDICE
+DISM-LACK OF PROS
+DISM/NO PROB CAUSE
+DISMISS/MODIFY
+DISMISS/TERM
+DISMISSED
+DISMISSED BY COURT
+DISMISSED-WITNESS
+DIV DEFR DRUG PGM
+DRUG COURT PRG PLEA
+NO ACTION
+NO ACTION - FIL CTY
+NO ACTION - FIL TRAF
+NO ACTION FILED TRF
+NO INFORMATION
+NO TRUE BILL
+NOLLE PROS
+NOLLE PROS - ADMIN
+NOLLE PROS - REST
+NOLLE PROS FILED TRF
+NOLLE PROS JDP
+NOLLE PROS- USAO/INS
+NOLLE PROS-ADM/WRNT
+NOLLE PROS-COMP 'DVI
+NOLLE PROS-COMP PTI
+NOLLE PROS-DRUG PGM
+NOLLE PROS-F/A PGM
+NOLLE PROS-WITNESS
+NOLLE PROS/FILED CC
+NON JUDICIAL ACTION
diff --git a/miami-dade_clerk_courts.py b/miami-dade_clerk_courts.py
deleted file mode 100755
index 744c4c4..0000000
--- a/miami-dade_clerk_courts.py
+++ /dev/null
@@ -1,393 +0,0 @@
-#!/usr/bin/env python
-
-import sys
-from datetime import datetime
-import urllib
-import urllib2
-import re
-import string
-from BeautifulSoup import BeautifulSoup
-from sqlalchemy import create_engine
-from sqlalchemy.ext.declarative import declarative_base
-from sqlalchemy import Column, Integer, String, Text, Date, Numeric
-from sqlalchemy.orm import sessionmaker
-
-Base = declarative_base()
-db_engine = create_engine('mysql://clerk:Clerk.@localhost/clerk_courts', echo=False)
-Session = sessionmaker(bind=db_engine)
-db_session = Session()
-
-url = 'https://www2.miami-dadeclerk.com/CJIS/CaseSearch.aspx'
-years = ['13']
-max_case_not_found_count = 3
-
-# Form field names
-field_names = {
- 'case_type' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$txtCaseNo1_',
- 'case_year' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$txtCaseNo2_',
- 'case_seq' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$txtCaseNo3_',
- 'case_defendant' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$txtCaseNo4_',
- 'viewstate' : '__VIEWSTATE',
- 'eventval' : '__EVENTVALIDATION',
- 'button' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$btnCaseSearch',
- 'active_tab' : 'ctl00_cphPage_tcSearchMenu_ClientState',
-}
-
-# Fixed form field values
-field_values = {
- 'case_type' : 'F',
- 'button' : 'Submit',
- 'active_tab' : '{"ActiveTabIndex":0,"TabState":[true,true,true,true,true]}',
- 'viewstate' : '',
- 'eventval' : '',
- 'field_suffix' : '',
-}
-
-# Case ORM class
-class Case(Base):
- __tablename__ = 'cases'
- id = Column(Integer, primary_key=True)
- court_case_no = Column(String(30))
- state_case_no = Column(String(30))
- name = Column(String(100))
- date_birth = Column(Date)
- date_filed = Column(Date)
- date_closed = Column(Date)
- warrant_type = Column(String(25))
- hearing_date = Column(Date)
- hearing_time = Column(Integer)
- hearing_type = Column(String(25))
- defense_attorney = Column(String(100))
- prob_start_date = Column(Date)
- prob_end_date = Column(Date)
- prob_length = Column(String(25))
- in_jail = Column(String(10))
- released_to = Column(String(100))
- bond_amount = Column(Numeric(7,2))
- bond_status = Column(String(50))
- bond_type = Column(String(50))
- bond_date = Column(Date)
-
-# Charge ORM class
-class Charge(Base):
- __tablename__ = 'charges'
- id = Column(Integer, primary_key=True)
- case_id = Column(Integer)
- seq = Column(Integer)
- charge = Column(String(200))
- type = Column(String(50))
- disposition = Column(String(256))
-
-# Docket ORM class
-class Docket(Base):
- __tablename__ = 'dockets'
- id = Column(Integer, primary_key=True)
- case_id = Column(Integer)
- seq = Column(Integer)
- date = Column(Date)
- docket = Column(Text)
-
-class Akas(Base):
- __tablename__ = 'akas'
- id = Column(Integer, primary_key=True)
- case_id = Column(Integer)
- last_name = Column(String(50))
- first_name = Column(String(50))
- middle_name = Column(String(50))
- race = Column(String(25))
- sex = Column(String(10))
-
-# This is an ASP site, so we have some variables that must be passed with a
-# form submission that change from page to page
-def set_asp_vars():
- global field_values
- response = urllib2.urlopen(url)
- page = response.read()
- soup = BeautifulSoup(page)
- field_values['eventval'] = soup.find(id="__EVENTVALIDATION")['value']
- field_values['viewstate'] = soup.find(id="__VIEWSTATE")['value']
- field_suffix_string = soup.find(title="YY")['id']
- field_values['field_suffix'] = field_suffix_string.split('_')[-1]
-
-def fetch_page(post_fields):
- data = urllib.urlencode(post_fields)
- req = urllib2.Request(url, data)
- response = urllib2.urlopen(req)
- return response.read()
-
-# Checks for a "NOT FOUND" error in the returned HTML for a case
-def check_case_not_found(case_soup):
- try:
- error_text = case_soup.find(id='ctl00_cphPage_lblSearchError').text
- except:
- return False
- else:
- if 'NOT FOUND' in error_text:
- return True
- else:
- return False
-
-# Once we determine that a given case exists, process all the parts
-def process_case(case_soup):
- case_id = parse_case(case_soup)
-
- # Get any AKAs, if they exist
- has_akas = case_soup.find(id='ctl00_cphPage_lnkAKA')
- if has_akas:
- post_fields = {
- '__EVENTTARGET' : 'ctl00$cphPage$lnkAKA',
- '__VIEWSTATE' : case_soup.find(id='__VIEWSTATE')['value'],
- '__EVENTVALIDATION' : case_soup.find(id='__EVENTVALIDATION')['value'],
- }
- akas = fetch_page(post_fields)
- akas_soup = BeautifulSoup(akas)
- parse_akas(akas_soup, case_id)
-
- # Get charges
- parse_charges(case_soup, case_id)
-
- # Get case dockets
- post_fields = {
- '__EVENTTARGET' : 'ctl00$cphPage$lnkDockets',
- '__VIEWSTATE' : case_soup.find(id='__VIEWSTATE')['value'],
- '__EVENTVALIDATION' : case_soup.find(id='__EVENTVALIDATION')['value'],
- }
- dockets = fetch_page(post_fields)
- dockets_soup = BeautifulSoup(dockets)
- parse_dockets(dockets_soup, case_id)
-
- # Get additional case info
- post_fields = {
- '__EVENTTARGET' : 'ctl00$cphPage$lnkAdditionalInfo',
- '__VIEWSTATE' : case_soup.find(id='__VIEWSTATE')['value'],
- '__EVENTVALIDATION' : case_soup.find(id='__EVENTVALIDATION')['value'],
- }
- addinfo = fetch_page(post_fields)
- addinfo_soup = BeautifulSoup(addinfo)
- parse_addinfo(addinfo_soup)
-
-def parse_case(case_soup):
- # Assign fields
- court_case_no = case_soup.find(id='ctl00_cphPage_lblCaseNumber').text
- state_case_no = case_soup.find(id='ctl00_cphPage_lblStateCaseNo').text
- name = case_soup.find(id='ctl00_cphPage_lblName').text
- date_birth = format_date(case_soup.find(id='ctl00_cphPage_lblDoB').text)
- date_filed = format_date(case_soup.find(id='ctl00_cphPage_lblDateFiled').text)
- date_closed = format_date(case_soup.find(id='ctl00_cphPage_lblDateClosed').text)
- warrant_type = case_soup.find(id='ctl00_cphPage_lblWarrantType').text
- hearing_date = format_date(case_soup.find(id='ctl00_cphPage_lblHearingDate').text)
- hearing_time = case_soup.find(id='ctl00_cphPage_lblHearingTime').text.strip()
- hearing_type = case_soup.find(id='ctl00_cphPage_lblHearingType').text
- defense_attorney = case_soup.find(id='ctl00_cphPage_lblDefenseAttorney').text
-
- # Try to clean up the data bit to avoid needless MySQL warnings
- if not hearing_time:
- hearing_time = None
-
- new_case = Case(
- court_case_no = court_case_no,
- state_case_no = state_case_no,
- name = name,
- date_birth = date_birth,
- date_filed = date_filed,
- date_closed = date_closed,
- warrant_type = warrant_type,
- hearing_date = hearing_date,
- hearing_time = hearing_time,
- hearing_type = hearing_type,
- defense_attorney = defense_attorney,
- )
-
- db_session.add(new_case)
- db_session.commit()
-
- return new_case.id
-
-def parse_charges(case_soup, case_id):
- charges_section = case_soup.find(id="ctl00_cphPage_pnlCharges")
- charge_rows = charges_section.findAll('tr')
- charge_rows.pop(0) # We don't want the table headers
- charge_fields = ['seq','charge','type','disposition']
- charges = []
-
- for ridx,fields in enumerate(charge_rows):
- charge_data = {}
- for fidx,field in enumerate(fields.findAll('td', recursive=False)):
- # Tsk-tsk. Why the non-breaking spaces!
- charge_data[charge_fields[fidx]] = re.sub('&nbsp;', ' ', field.text)
- charges.append(charge_data)
-
- for charge in charges:
- new_charge = Charge(
- case_id = case_id,
- seq = charge['seq'],
- charge = charge['charge'],
- type = charge['type'],
- disposition = charge['disposition'],
- )
- db_session.add(new_charge)
-
- # Commit charges to database
- db_session.commit()
-
-def parse_dockets(dockets_soup, case_id):
- # This is pretty lame, but they don't have any useful ids, names or
- # attributes to accurately identify the right table. These classes are
- # probably good enough, though.
- docket_rows = dockets_soup.findAll('tr', attrs={'class':['RowBody','RowBodyAlt']})
- docket_fields = ['seq', 'date', 'book-page', 'docket']
- dockets = []
- for ridx,fields in enumerate(docket_rows):
- docket_data = {}
- for fidx,field in enumerate(fields.findAll('td', recursive=False)):
- # Tsk-tsk. Why the non-breaking spaces!
- docket_data[docket_fields[fidx]] = re.sub('&nbsp;', ' ', field.text)
- dockets.append(docket_data)
- for docket in dockets:
- new_docket = Docket(
- case_id = case_id,
- seq = docket['seq'],
- date = format_date(docket['date']),
- docket = docket['docket'],
- )
- db_session.add(new_docket)
-
- # Commit dockets to database
- db_session.commit()
-
-
-def parse_addinfo(addinfo_soup):
- court_case_no = addinfo_soup.find(id='ctl00_cphPage_lblCaseNumber').text
- case = db_session.query(Case).filter_by(court_case_no=court_case_no).one()
-
- # Assign values to existing case
- case.prob_start_date = format_date(addinfo_soup.find(id='ctl00_cphPage_lblProbationStartDate').text)
- case.prob_end_date = format_date(addinfo_soup.find(id='ctl00_cphPage_lblProbationEndDate').text)
- case.prob_length = addinfo_soup.find(id='ctl00_cphPage_lblProbationLength').text
- case.in_jail = addinfo_soup.find(id='ctl00_cphPage_lblDefendantinJail').text
- case.released_to = addinfo_soup.find(id='ctl00_cphPage_lblDefendantReleaseTo').text
- case.bond_amount = addinfo_soup.find(id='ctl00_cphPage_lblBondAmount').text
- case.bond_status = addinfo_soup.find(id='ctl00_cphPage_lblBondStatus').text
- case.bond_type = addinfo_soup.find(id='ctl00_cphPage_lblBondType').text
- case.bond_date = format_date(addinfo_soup.find(id='ctl00_cphPage_lblBondIssueDate').text)
-
- # Fix format of Bond Amount
- case.bond_amount = case.bond_amount.lstrip('$')
- case.bond_amount = case.bond_amount.replace(',', '')
-
- db_session.commit()
-
-def parse_akas(akas_soup, case_id):
- akas_count_text = akas_soup.find(id='ctl00_cphPage_lblDefendants').text
- akas_count = re.match('^\d+', akas_count_text).group(0)
- for num in range(0, int(akas_count)):
- # For readability, get field ids into variables
- last_name_id = 'ctl00_cphPage_rptDefendants_ctl0%s_lnkDefendantLastName' % num
- first_name_id = 'ctl00_cphPage_rptDefendants_ctl0%s_lnkDefendantFirstName' % num
- middle_name_id = 'ctl00_cphPage_rptDefendants_ctl0%s_lnkDefendantMiddleName' % num
- race_id = 'ctl00_cphPage_rptDefendants_ctl0%s_lnkDefendantRace' % num
- sex_id = 'ctl00_cphPage_rptDefendants_ctl0%s_lnkDefendantSex' % num
-
- # Now get the actual variables from the soup
- last_name = akas_soup.find(id=last_name_id).text
- first_name = akas_soup.find(id=first_name_id).text
- middle_name = akas_soup.find(id=middle_name_id).text
- race = akas_soup.find(id=race_id).text
- sex = akas_soup.find(id=sex_id).text
-
- new_aka = Akas(
- case_id = case_id,
- last_name = last_name,
- first_name = first_name,
- middle_name = first_name,
- race = race,
- sex = sex,
- )
-
- db_session.add(new_aka)
-
- db_session.commit()
-
-def format_date(date):
- try:
- date_obj = datetime.strptime(date, '%m/%d/%Y')
- if date_obj:
- return date_obj.strftime('%Y-%m-%d')
- else:
- return None
- except:
- return None
-
-def main():
-
- # Set ASP-specific form field variables
- set_asp_vars()
-
- for year in years:
- # Keep track of how many NOT FOUND errors we get.
- case_not_found_count = 0
-
- for seq in range(37,39):
- if case_not_found_count > max_case_not_found_count:
- # If our not_found_count exceeds the maximum set,
- # then assume that we have reached the end of all
- # cases for the given year and move on to the next
- # year
- break
-
- # Iterate through A-Z possible defendants
- for defendant in string.lowercase:
-
- # In the cases where we have multiple defendants, when
- # we reach the end of the defendants we do not want the
- # code to try to make the request without the defendant
- # field since this will return a duplicate of the A
- # defendant. This variable is a flag to signal this.
- try:
- has_multiple_defendants
- except:
- has_multiple_defendants = None
-
- # Get main case information
- post_fields = {
- field_names['case_type'] + field_values['field_suffix'] : field_values['case_type'],
- field_names['case_year'] + field_values['field_suffix'] : year,
- field_names['case_seq'] + field_values['field_suffix'] : seq,
- field_names['case_defendant'] + field_values['field_suffix'] : defendant,
- field_names['viewstate'] : field_values['viewstate'],
- field_names['eventval'] : field_values['eventval'],
- field_names['button'] : field_values['button'],
- field_names['active_tab'] : field_values['active_tab'],
- }
-
- case = fetch_page(post_fields)
- case_soup = BeautifulSoup(case)
- not_found = check_case_not_found(case_soup)
- if not_found:
- if has_multiple_defendants:
- break
- # Try again with no defendant sequence specification
- post_fields[ field_names['case_defendant'] + field_values['field_suffix'] ] = ''
- case = fetch_page(post_fields)
- case_soup = BeautifulSoup(case)
- not_found = check_case_not_found(case_soup)
- if not_found:
- # If the case wasn't found even without the defendant
- # number then it really must not exist
- case_not_found_count = case_not_found_count + 1
- else:
- # Reset the not_found_count and process case
- case_not_found_count = 0
- has_multiple_defendants = None
- process_case(case_soup)
-
- break
- else:
- has_multiple_defendants = True
- # Reset the not_found_count and process case
- case_not_found_count = 0
- process_case(case_soup)
-
-
-if __name__ == '__main__':
- main()
diff --git a/miami_dade_clerk_courts.py b/miami_dade_clerk_courts.py
new file mode 100755
index 0000000..5ec4353
--- /dev/null
+++ b/miami_dade_clerk_courts.py
@@ -0,0 +1,612 @@
+#!/usr/bin/env python
+
+import sys
+from datetime import datetime
+import urllib
+import urllib2
+import re
+import string
+import time
+import logging
+from BeautifulSoup import BeautifulSoup
+from sqlalchemy import create_engine
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy import Column, Integer, String, Text, Date, Numeric
+from sqlalchemy.orm import sessionmaker
+
+logging.basicConfig( filename='mdcc.log', level=logging.ERROR )
+
+Base = declarative_base()
+db_engine = create_engine('mysql://mdcc:Mdcc.@localhost/mdcc', echo=False)
+Session = sessionmaker(bind=db_engine)
+db_session = Session()
+
+# Global variables
+url = 'https://www2.miami-dadeclerk.com/CJIS/CaseSearch.aspx'
+max_case_not_found_count = 50
+
+
+# Form field names
+field_names = {
+ 'case_type' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$txtCaseNo1_',
+ 'case_year' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$txtCaseNo2_',
+ 'case_seq' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$txtCaseNo3_',
+ 'case_defendant' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$txtCaseNo4_',
+ 'viewstate' : '__VIEWSTATE',
+ 'eventval' : '__EVENTVALIDATION',
+ 'button' : 'ctl00$cphPage$tcSearchMenu$tpCaseSearch$btnCaseSearch',
+ 'active_tab' : 'ctl00_cphPage_tcSearchMenu_ClientState',
+}
+
+# Fixed form field values
+field_values = {
+ 'case_type' : 'F',
+ 'button' : 'Submit',
+ 'active_tab' : '{"ActiveTabIndex":0,"TabState":[true,true,true,true,true]}',
+ 'viewstate' : '',
+ 'eventval' : '',
+ 'field_suffix' : '',
+}
+
+# Case ORM class
+class Case(Base):
+ __tablename__ = 'cases'
+ id = Column(Integer, primary_key=True)
+ court_case_no = Column(String(30))
+ state_case_no = Column(String(30))
+ name = Column(String(100))
+ first_name = Column(String(50))
+ last_name = Column(String(50))
+ date_birth = Column(Date)
+ date_filed = Column(Date)
+ date_closed = Column(Date)
+ warrant_type = Column(String(25))
+ hearing_date = Column(Date)
+ hearing_time = Column(Integer)
+ hearing_type = Column(String(25))
+ defense_attorney = Column(String(100))
+ prob_start_date = Column(Date)
+ prob_end_date = Column(Date)
+ prob_length = Column(String(25))
+ in_jail = Column(String(10))
+ released_to = Column(String(100))
+ bond_amount = Column(Numeric(7,2))
+ bond_status = Column(String(50))
+ bond_type = Column(String(50))
+ bond_date = Column(Date)
+
+# Charge ORM class
+class Charge(Base):
+ __tablename__ = 'charges'
+ id = Column(Integer, primary_key=True)
+ case_id = Column(Integer)
+ seq = Column(Integer)
+ charge = Column(String(200))
+ type = Column(String(50))
+ disposition = Column(String(256))
+
+# Docket ORM class
+class Docket(Base):
+ __tablename__ = 'dockets'
+ id = Column(Integer, primary_key=True)
+ case_id = Column(Integer)
+ seq = Column(Integer)
+ date = Column(Date)
+ docket = Column(Text)
+
+class Akas(Base):
+ __tablename__ = 'akas'
+ id = Column(Integer, primary_key=True)
+ case_id = Column(Integer)
+ last_name = Column(String(50))
+ first_name = Column(String(50))
+ middle_name = Column(String(50))
+ race = Column(String(25))
+ sex = Column(String(10))
+
+
+# This is an ASP site, so we have some variables that must be passed with a
+# form submission that change form page to page
+def set_asp_vars():
+
+ global field_values
+
+ try:
+ response = urllib2.urlopen(url)
+ page = response.read()
+ soup = BeautifulSoup(page)
+ field_values['eventval'] = soup.find(id="__EVENTVALIDATION")['value']
+ field_values['viewstate'] = soup.find(id="__VIEWSTATE")['value']
+ field_suffix_string = soup.find(title="YY")['id']
+ field_values['field_suffix'] = field_suffix_string.split('_')[-1]
+ except KeyboardInterrupt:
+ raise
+ except:
+ logging.error('Failed to get initial ASP variables. %s' % sys.exc_info()[0])
+ return False
+
+
+# Fetches the actual page from the site
+def fetch_page(post_fields):
+
+ data = urllib.urlencode(post_fields)
+
+ attempt = 0
+ # Try to fetch the page 3 times before giving up
+ while attempt < 3:
+ try:
+ req = urllib2.Request(url, data)
+ response = urllib2.urlopen(req, timeout=30)
+ page = response.read()
+ return page
+ except (urllib2.HTTPError, urllib2.URLError) as e:
+ attempt = attempt + 1
+ if hasattr(e, 'reason'):
+ logging.error('%s: urllib2 error reason: %s' % (current_case, e.reason))
+ if hasattr(e, 'code'):
+ logging.error('%s: urllib2 error code: %s' % (current_case, e.code))
+ if e.code == 500:
+ sleeptime = 2 ** attempt * 10
+ time.sleep(sleeptime)
+ logging.error('%s: Trying again after sleeping for %d seconds' % (current_case, sleeptime))
+ # Reset base ASP vars just in case
+ set_asp_vars()
+ logging.error('%s: Also resetting ASP vars just in case.' % (current_case))
+ except KeyboardInterrupt:
+ raise
+ except:
+ logging.error('%s: fetch_page() exception %s - %s'
+ % (current_case, sys.exc_info()[0], sys.exc_info()[1]))
+
+ # If we reach this point then fetching the page totally failed
+ logging.error('%s: Failed to fetch page contents. %s'
+ % (current_case, sys.exc_info()[0]))
+ return False
+
+
+# Checks whether we have some sort of error condition in the page
+def check_case_not_found(case_soup):
+
+ try:
+ error_text = case_soup.find(id='ctl00_cphPage_lblSearchError').text
+ except KeyboardInterrupt:
+ raise
+ except:
+ return False
+ else:
+ if 'NOT FOUND' in error_text:
+ return True
+ elif 'Unable to retrieve case information' in error_text:
+ return True
+ elif 'CASE INFORMATION NOT ON FILE' in error_text:
+ return True
+ else:
+ return False
+
+# Checks for a "No dockets found" error condition
+def check_dockets_not_found(dockets_soup):
+
+ try:
+ error_text = dockets_soup.find(id='ctl00_cphPage_lblCaseError').text
+ except KeyboardInterrupt:
+ raise
+ except:
+ return False
+ else:
+ if 'No dockets were found' in error_text:
+ return True
+ else:
+ return False
+
+
+# Once we determine that a given case exists, process all the parts
+def process_case(case_soup):
+
+ case_id = parse_case(case_soup)
+
+ if not case_id:
+ return False
+
+ # Get any AKAs, if they exist
+ has_akas = case_soup.find(id='ctl00_cphPage_lnkAKA')
+ if has_akas:
+ post_fields = {
+ '__EVENTTARGET' : 'ctl00$cphPage$lnkAKA',
+ '__VIEWSTATE' : case_soup.find(id='__VIEWSTATE')['value'],
+ '__EVENTVALIDATION' : case_soup.find(id='__EVENTVALIDATION')['value'],
+ }
+ akas = fetch_page(post_fields)
+ if akas:
+ akas_soup = BeautifulSoup(akas)
+ parse_akas(akas_soup, case_id)
+
+ # Get charges
+ parse_charges(case_soup, case_id)
+
+ # Get case dockets
+ post_fields = {
+ '__EVENTTARGET' : 'ctl00$cphPage$lnkDockets',
+ '__VIEWSTATE' : case_soup.find(id='__VIEWSTATE')['value'],
+ '__EVENTVALIDATION' : case_soup.find(id='__EVENTVALIDATION')['value'],
+ }
+ dockets = fetch_page(post_fields)
+ if dockets:
+ dockets_soup = BeautifulSoup(dockets)
+ no_dockets = check_dockets_not_found(dockets_soup)
+ if not no_dockets:
+ parse_dockets(dockets_soup, case_id)
+
+ # Get additional case info
+ post_fields = {
+ '__EVENTTARGET' : 'ctl00$cphPage$lnkAdditionalInfo',
+ '__VIEWSTATE' : case_soup.find(id='__VIEWSTATE')['value'],
+ '__EVENTVALIDATION' : case_soup.find(id='__EVENTVALIDATION')['value'],
+ }
+ addinfo = fetch_page(post_fields)
+ if addinfo:
+ addinfo_soup = BeautifulSoup(addinfo)
+ parse_addinfo(addinfo_soup)
+
+
+# Parses the main case information
+def parse_case(case_soup):
+
+ # A dictionary of local variable names mapped to page ids
+ case_fields = {
+ 'court_case_no' : 'ctl00_cphPage_lblCaseNumber',
+ 'state_case_no' : 'ctl00_cphPage_lblStateCaseNo',
+ 'name' : 'ctl00_cphPage_lblName',
+ 'date_birth' : 'ctl00_cphPage_lblDoB',
+ 'date_filed' : 'ctl00_cphPage_lblDateFiled',
+ 'date_closed' : 'ctl00_cphPage_lblDateClosed',
+ 'warrant_type' : 'ctl00_cphPage_lblWarrantType',
+ 'hearing_date' : 'ctl00_cphPage_lblHearingDate',
+ 'hearing_time' : 'ctl00_cphPage_lblHearingTime',
+ 'hearing_type' : 'ctl00_cphPage_lblHearingType',
+ 'defense_attorney' : 'ctl00_cphPage_lblDefenseAttorney',
+ }
+
+ case_values = {}
+ for var, field_id in case_fields.iteritems():
+ try:
+ case_values[var] = case_soup.find(id=field_id).text
+ except KeyboardInterrupt:
+ raise
+ except:
+ # warrant_type won't always exist, so don't bail just because we
+ # don't find it, but otherwise do log and bail
+ if var == 'warrant_type':
+ pass
+ else:
+ logging.error('%s: Failed to find field %s in soup. %s' % (current_case, var, sys.exc_info()[0]))
+ logging.error('%s: Something is wrong with the soup. Here it is: %s' % (current_case, case_soup))
+ continue
+
+ # Try to clean up the data bit to avoid needless MySQL warnings
+ if not case_values['hearing_time'].strip():
+ case_values['hearing_time'] = None
+
+ # Before we try to add this case, be sure that for some reason
+ # it doesn't already exist
+ case_exists = db_session.query(Case).filter_by(court_case_no=case_values['court_case_no']).first()
+
+ if not case_exists:
+ new_case = Case(
+ court_case_no = case_values['court_case_no'],
+ state_case_no = case_values['state_case_no'],
+ name = case_values['name'],
+ first_name = case_values['name'].split(',')[1].strip(),
+ last_name = case_values['name'].split(',')[0].strip(),
+ date_birth = format_date(case_values['date_birth']),
+ date_filed = format_date(case_values['date_filed']),
+ date_closed = format_date(case_values['date_closed']),
+ hearing_date = format_date(case_values['hearing_date']),
+ hearing_time = case_values['hearing_time'],
+ hearing_type = case_values['hearing_type'],
+ defense_attorney = case_values['defense_attorney'],
+ )
+
+ # warrant_type won't always exist, so check before trying to assign it
+ try:
+ new_case.warrant_type = case_values['warrant_type']
+ except KeyboardInterrupt:
+ raise
+ except:
+ pass
+
+ db_session.add(new_case)
+ db_session.commit()
+
+ return new_case.id
+ else:
+ return False
+
+
+# Parses the case soup for the list of charges agains the defendant
+def parse_charges(case_soup, case_id):
+
+ try:
+ charges_section = case_soup.find(id="ctl00_cphPage_pnlCharges")
+ except KeyboardInterrupt:
+ raise
+ except:
+ logging.error('%s: Failed to find charges for case ID %s. %s' % (current_case, sys.exc_info()[0]))
+ return False
+
+ charge_rows = charges_section.findAll('tr')
+
+ if charge_rows:
+ charge_rows.pop(0) # We don't want the table headers
+ charge_fields = ['seq','charge','type','disposition']
+ charges = []
+
+ for ridx,fields in enumerate(charge_rows):
+ charge_data = {}
+ for fidx,field in enumerate(fields.findAll('td', recursive=False)):
+ # Why the non-breaking spaces? Remove them.
+ charge_data[charge_fields[fidx]] = re.sub('&nbsp;', ' ', field.text)
+ charges.append(charge_data)
+
+ for charge in charges:
+ new_charge = Charge(
+ case_id = case_id,
+ seq = charge['seq'],
+ charge = charge['charge'],
+ type = charge['type'],
+ disposition = charge['disposition'],
+ )
+ db_session.add(new_charge)
+
+ # Commit charges to database
+ db_session.commit()
+ else:
+ logging.error('%s: No charges found.' % current_case)
+
+
+
+# Parses any dockets associated with the case
+def parse_dockets(dockets_soup, case_id):
+
+ # This is pretty lame, but they don't have any useful ids, names or
+ # attributes to accurately identify the right table. These classes are
+ # probably good enough, though.
+ docket_rows = dockets_soup.findAll('tr', attrs={'class':['RowBody','RowBodyAlt']})
+ docket_fields = ['seq', 'date', 'book-page', 'docket']
+ dockets = []
+ if docket_rows:
+ for ridx,fields in enumerate(docket_rows):
+ docket_data = {}
+ for fidx,field in enumerate(fields.findAll('td', recursive=False)):
+ # Why the non-breaking spaces? Remove them.
+ docket_data[docket_fields[fidx]] = re.sub('&nbsp;', ' ', field.text)
+ dockets.append(docket_data)
+ for docket in dockets:
+ new_docket = Docket(
+ case_id = case_id,
+ seq = docket['seq'],
+ date = format_date(docket['date']),
+ docket = docket['docket'],
+ )
+ db_session.add(new_docket)
+
+ # Commit dockets to database
+ db_session.commit()
+ else:
+ logging.error('%s: No dockets found.' % current_case)
+
+
+# Parses the soup for the Additional Information section for the case
+def parse_addinfo(addinfo_soup):
+
+ try:
+ court_case_no = addinfo_soup.find(id='ctl00_cphPage_lblCaseNumber').text
+ case = db_session.query(Case).filter_by(court_case_no=court_case_no).one()
+ except KeyboardInterrupt:
+ raise
+ except:
+ logging.error('%s: Error while looking for existing case in addinfo. %s' % (current_case, sys.exc_info()[0]))
+ return False
+
+ # Map db field names to corresponding page ids
+ case_fields = {
+ 'prob_start_date' : 'ctl00_cphPage_lblProbationStartDate',
+ 'prob_end_date' : 'ctl00_cphPage_lblProbationEndDate',
+ 'prob_length' : 'ctl00_cphPage_lblProbationLength',
+ 'in_jail' : 'ctl00_cphPage_lblDefendantinJail',
+ 'released_to' : 'ctl00_cphPage_lblDefendantReleaseTo',
+ 'bond_amount' : 'ctl00_cphPage_lblBondAmount',
+ 'bond_status' : 'ctl00_cphPage_lblBondStatus',
+ 'bond_type' : 'ctl00_cphPage_lblBondType',
+ 'bond_date' : 'ctl00_cphPage_lblBondIssueDate',
+ }
+
+ case_values = {}
+ for var, field_id in case_fields.iteritems():
+ try:
+ case_values[var] = addinfo_soup.find(id=field_id).text
+ except KeyboardInterrupt:
+ raise
+ except:
+ logging.error('%s: Failed to find a value for the addinfo field %s. %s' % (current_case, sys.exc_info()[0]))
+ continue
+
+ # Assign values to existing case
+ case.prob_start_date = format_date(case_values['prob_start_date'])
+ case.prob_end_date = format_date(case_values['prob_end_date'])
+ case.prob_length = case_values['prob_length']
+ case.in_jail = case_values['in_jail']
+ case.released_to = case_values['released_to']
+ case.bond_amount = case_values['bond_amount']
+ case.bond_status = case_values['bond_status']
+ case.bond_type = case_values['bond_type']
+ case.bond_date = format_date(case_values['bond_date'])
+
+ # Fix format of Bond Amount
+ case.bond_amount = case.bond_amount.lstrip('$')
+ case.bond_amount = case.bond_amount.replace(',', '')
+
+ db_session.commit()
+
+
+# Parses page listing any AKAs for the defendant
+def parse_akas(akas_soup, case_id):
+ try:
+ akas_count_text = akas_soup.find(id='ctl00_cphPage_lblDefendants').text
+ akas_count = re.match('^\d+', akas_count_text).group(0)
+ except KeyboardInterrupt:
+ raise
+ except:
+ logging.error('%s: Failed to determine akas_count %s. %s' % (current_case, sys.exc_info()[0]))
+ return False
+
+ for num in range(0, int(akas_count)):
+
+ # We have to pad numbers less than 10
+ if num < 10:
+ num = '0%s' % num
+
+ # For readability, get field ids into variables
+ last_name_id = 'ctl00_cphPage_rptDefendants_ctl%s_lnkDefendantLastName' % num
+ first_name_id = 'ctl00_cphPage_rptDefendants_ctl%s_lnkDefendantFirstName' % num
+ middle_name_id = 'ctl00_cphPage_rptDefendants_ctl%s_lnkDefendantMiddleName' % num
+ race_id = 'ctl00_cphPage_rptDefendants_ctl%s_lnkDefendantRace' % num
+ sex_id = 'ctl00_cphPage_rptDefendants_ctl%s_lnkDefendantSex' % num
+
+ # Map db fields to coresponding page id
+ akas_fields = {
+ 'last_name' : last_name_id,
+ 'first_name' : first_name_id,
+ 'middle_name' : middle_name_id,
+ 'race' : race_id,
+ 'sex' : sex_id,
+ }
+
+ akas_values = {}
+ for var, field_id in akas_fields.iteritems():
+ try:
+ akas_values[var] = akas_soup.find(id=field_id).text
+ except KeyboardInterrupt:
+ raise
+ except:
+ logging.error('%s: Failed to find a value for akas field %s. %s' % (current_case, var, sys.exc_info()[0]))
+ continue
+
+ new_aka = Akas(
+ case_id = case_id,
+ last_name = akas_values['last_name'],
+ first_name = akas_values['first_name'],
+ middle_name = akas_values['first_name'],
+ race = akas_values['race'],
+ sex = akas_values['sex'],
+ )
+
+ db_session.add(new_aka)
+
+ db_session.commit()
+
+
+# Format the date to something acceptable for the database
+def format_date(date):
+
+ try:
+ if date.strip():
+ date_obj = datetime.strptime(date, '%m/%d/%Y')
+ if date_obj:
+ return date_obj.strftime('%Y-%m-%d')
+ else:
+ return None
+ except KeyboardInterrupt:
+ raise
+ except:
+ logging.error('%s: Failed to create a date object for date %s. %s' % (current_case, date, sys.exc_info()[0]))
+ return None
+
+
+def main():
+
+ # We set this shortly so that our logging facility can associate
+ # a case number with errors being logged.
+ global current_case
+
+ # Set ASP-specific form field variables
+ set_asp_vars()
+
+ for year in years:
+ # Keep track of how many NOT FOUND errors we get.
+ case_not_found_count = 0
+
+ for seq in range(seq_start, seq_end):
+
+ if case_not_found_count >= max_case_not_found_count:
+ # If our not_found_count exceeds the maximum set,
+ # then assume that we have reached the end of all
+ # cases for the given year and move on to the next
+ # year
+ logging.error('%s: Case not found count exceeded %s, quitting' % (current_case, case_not_found_count))
+ break
+
+ # Iterate through A-Z possible defendants
+ for defendant in string.lowercase:
+
+ current_case = 'F-%s-%s-%s' % (year, seq, defendant)
+
+ # Get main case information
+ post_fields = {
+ field_names['case_type'] + field_values['field_suffix'] : field_values['case_type'],
+ field_names['case_year'] + field_values['field_suffix'] : year,
+ field_names['case_seq'] + field_values['field_suffix'] : seq,
+ field_names['case_defendant'] + field_values['field_suffix'] : defendant,
+ field_names['viewstate'] : field_values['viewstate'],
+ field_names['eventval'] : field_values['eventval'],
+ field_names['button'] : field_values['button'],
+ field_names['active_tab'] : field_values['active_tab'],
+ }
+
+ # Try to fetch a given case 3 times before
+ # giving up and moving to the next sequence
+ case = fetch_page(post_fields)
+ if case:
+ case_soup = BeautifulSoup(case)
+ not_found = check_case_not_found(case_soup)
+ if not_found:
+ current_case = 'F-%s-%s' % (year, seq)
+ # We only want to try stripping the defendant letter on 'a',
+ # since for anything after we will have already tried
+ # stripping it, and don't want to do it again.
+ if defendant != 'a':
+ break
+ # Try again with no defendant sequence specification
+ post_fields[ field_names['case_defendant'] + field_values['field_suffix'] ] = ''
+ case = fetch_page(post_fields)
+ if case:
+ case_soup = BeautifulSoup(case)
+ not_found = check_case_not_found(case_soup)
+ if not_found:
+ # If the case wasn't found even without the defendant
+ # number then it really must not exist
+ case_not_found_count = case_not_found_count + 1
+ else:
+ # Reset the not_found_count and process case
+ case_not_found_count = 0
+ process_case(case_soup)
+
+ break
+ else:
+ current_case = 'F-%s-%s-%s' % (year, seq, defendant)
+ # Reset the not_found_count and process case
+ case_not_found_count = 0
+ process_case(case_soup)
+ else:
+ break
+
+
+if __name__ == '__main__':
+ # Get starting sequence number and years from command line arguments
+ seq_start = int(sys.argv[1])
+ if int(sys.argv[2]) == 0:
+ seq_end = 100000
+ else:
+ seq_end = int(sys.argv[2]) + 1
+ years = sys.argv[3:]
+
+ main()
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;
+
+?>
diff --git a/style.css b/style.css
new file mode 100644
index 0000000..323d302
--- /dev/null
+++ b/style.css
@@ -0,0 +1,87 @@
+#case_search {
+ background-color: #cccccc;
+}
+
+.results_table {
+ font-size: x-small;
+}
+
+.results_table th a {
+ text-decoration: none;
+}
+
+.sort_asc {
+ color: #e6e600;
+ margin-right: 5px;
+}
+
+.sort_desc {
+ color: #29a329;
+}
+
+#search_again {
+ color: blue;
+}
+
+
+td {
+ border: 1px solid #000000;
+ padding: 3px 7px 2px 7px;
+}
+
+th {
+ font-size: 1.1em;
+ padding: 3px 7px 2px 7px;
+ text-align: left;
+ color: #ffffff;
+}
+
+tr.tablehead {
+ background-color: #000000;
+}
+
+tr.alt {
+ background-color: #eeeeee;
+}
+
+.case_num { width: 5em; }
+
+#search_cases div {
+ margin-bottom: 1ex;
+}
+
+label {
+ display: inline-block;
+ width: 25ex;
+}
+
+#content {
+ text-align: center;
+}
+
+#search_form {
+ display: inline-block;
+ text-align: left;
+}
+
+#form_submit {
+ text-align: center;
+ margin-top: 2em;
+}
+
+#search_cases #name, #charge, #disposition, #docket {
+ width: 40ex;
+}
+
+label { vertical-align: top; }
+
+#export {
+ margin-bottom: 1ex;
+}
+
+#export a {
+ color: blue;
+}
+
+.pull { margin-bottom: 2em; }
+.pull_count { margin-left: 1em; }
generated by cgit v1.2.1 (git 2.18.0) at 2021-02-25 11:02:12 +0000