/**
* Get the Surveillance Data
*
* @return db resultset
*/
public static function getSurveillanceData($from, $to)
{
$diseases = Disease::all();
$surveillances = array();
$testTypeIds = array();
//Foreach disease create a query string for the different test types
foreach (Disease::all() as $disease) {
$count = 0;
$testTypeQuery = '';
//For a single disease creating a query string for it's different test types
foreach ($disease->reportDiseases as $reportDisease) {
if ($count == 0) {
$testTypeQuery = 't.test_type_id=' . $reportDisease->test_type_id;
} else {
$testTypeQuery = $testTypeQuery . ' or t.test_type_id=' . $reportDisease->test_type_id;
}
$testTypeIds[] = $reportDisease->test_type_id;
$count++;
}
//For a single disease holding the test types query string and disease id
if (!empty($testTypeQuery)) {
$surveillances[$disease->id]['test_type_id'] = $testTypeQuery;
$surveillances[$disease->id]['disease_id'] = $disease->id;
}
}
//Getting an array of measure ids from an array of test types
$measureIds = Test::getMeasureIdsByTestTypeIds($testTypeIds);
//Getting an array of positive interpretations from an array of measure ids
$positiveRanges = Test::getPositiveRangesByMeasureIds($measureIds);
$idCount = 0;
$positiveRangesQuery = '';
//Formating the positive ranges into part of the the query string
//ZEEK: not the best design, causes the query to be unreasonably big. Isn't what we need a 'Positive' result
/*foreach ($positiveRanges as $positiveRange) {
if ($idCount == 0) {
$positiveRangesQuery = "tr.result='".$positiveRange."'";
} else {
$positiveRangesQuery = $positiveRangesQuery." or tr.result='".$positiveRange."'";
}
$idCount++;
}*/
if (count($positiveRanges) > 0) {
/*Stool culture organims results are neither positive or negative.
* Since they are free text like 'Shingella isolated', it is complicated using a standard query
* due to the possiblity of user typing wide range of non standard text. The reason why querying for non negatives
*/
$positiveRangesQuery = "tr.result='Positive' or tr.result<>'Negative'";
}
//echo print_r($positiveRangesQuery);
// Query only if there are entries for surveillance
if (!empty($surveillances) && !empty($positiveRangesQuery)) {
//Select surveillance data for the defined diseases
$query = "SELECT * FROM ";
foreach ($surveillances as $surveillance) {
$t_id = '';
$organism_id = 0;
if ($surveillance['disease_id'] == 2) {
$t_id = 'd.test_id';
$organism_id = 8;
} elseif ($surveillance['disease_id'] == 3) {
$t_id = 'd.test_id';
$organism_id = 9;
} else {
$t_id = 't.id';
}
$query = $query . "(SELECT ";
$query = $query . "COUNT(DISTINCT if((" . $surveillance['test_type_id'] . "),t.id,NULL)) as " . $surveillance['disease_id'] . "_total," . "COUNT(DISTINCT if(((" . $surveillance['test_type_id'] . ") and DATE_SUB(NOW(), INTERVAL 5 YEAR)<p.dob),t.id,NULL)) as " . $surveillance['disease_id'] . "_less_five_total, " . "COUNT(DISTINCT if(((" . $surveillance['test_type_id'] . ") and (" . $positiveRangesQuery . "))," . $t_id . " ,NULL)) as " . $surveillance['disease_id'] . "_positive," . "COUNT(DISTINCT if(((" . $surveillance['test_type_id'] . ") and (" . $positiveRangesQuery . ") and DATE_SUB(NOW(), INTERVAL 5 YEAR)<p.dob)," . $t_id . ",NULL)) as " . $surveillance['disease_id'] . "_less_five_positive";
$query = $query . " FROM tests t " . "INNER JOIN test_results tr ON t.id=tr.test_id " . "JOIN visits v ON v.id=t.visit_id " . "LEFT JOIN (SELECT DISTINCT(`test_id`) FROM `drug_susceptibility` WHERE organism_id = " . $organism_id . " ) d ON tr.test_id=d.test_id " . "JOIN patients p ON v.patient_id=p.id ";
if ($from) {
$query = $query . "WHERE (time_created BETWEEN '" . $from . "' AND '" . $to . "')";
}
$query = $query . ") AS s" . $surveillance['disease_id'];
//Add no JOIN if it is the last variable in the array
if ($surveillance == end($surveillances)) {
$query = $query . " ";
} else {
$query = $query . " JOIN";
}
}
//echo $query;
$data = DB::select($query);
$data = json_decode(json_encode($data), true);
return $data[0];
} else {
return null;
}
}
/**
* Manage Diseases reported on
* @param
*/
public function disease()
{
if (Input::all()) {
$rules = array();
$newDiseases = Input::get('new-diseases');
if (Input::get('new-diseases')) {
// create an array that form the rules array
foreach ($newDiseases as $key => $value) {
//Ensure no duplicate disease
$rules['new-diseases.' . $key . '.disease'] = 'unique:diseases,name';
}
}
$validator = Validator::make(Input::all(), $rules);
if ($validator->fails()) {
return Redirect::route('reportconfig.disease')->withErrors($validator);
} else {
$allDiseaseIds = array();
//edit or leave disease entries as is
if (Input::get('diseases')) {
$diseases = Input::get('diseases');
foreach ($diseases as $id => $disease) {
$allDiseaseIds[] = $id;
$diseases = Disease::find($id);
$diseases->name = $disease['disease'];
$diseases->save();
}
}
//save new disease entries
if (Input::get('new-diseases')) {
$diseases = Input::get('new-diseases');
foreach ($diseases as $id => $disease) {
$diseases = new Disease();
$diseases->name = $disease['disease'];
$diseases->save();
$allDiseaseIds[] = $diseases->id;
}
}
//check if action is from a form submission
if (Input::get('from-form')) {
// Delete any pre-existing disease entries
//that were not captured in any of the above save loops
$allDiseases = Disease::all(array('id'));
$deleteDiseases = array();
//Identify disease entries to be deleted by Ids
foreach ($allDiseases as $key => $value) {
if (!in_array($value->id, $allDiseaseIds)) {
//Allow delete if not in use
$inUseByReports = Disease::find($value->id)->reportDiseases->toArray();
if (empty($inUseByReports)) {
// The disease is not in use
$deleteDiseases[] = $value->id;
}
}
}
//Delete disease entry if any
if (count($deleteDiseases) > 0) {
Disease::destroy($deleteDiseases);
}
}
}
}
$diseases = Disease::all();
return view('reportconfig.disease')->with('diseases', $diseases);
}
