/** * 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; } }