Exemplo n.º 1
0
 /**
  * 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;
     }
 }