/** * Return the prevalence counts for all TestTypes for the given date range * * @param $from, $to */ public static function getPrevalenceCounts($from, $to, $testTypeID = 0, $ageRange = null) { $toPlusOne = date_add(new DateTime($to), date_interval_create_from_date_string('1 day')); // TODO: Should be changed to a more flexible format i.e. that supports localization $data = Test::select(DB::raw("test_types.id as id, test_types.name as test, " . "COUNT(DISTINCT tests.specimen_id) as total, " . "COUNT(DISTINCT IF((test_results.result='Positive' OR " . "(measure_ranges.alphanumeric = test_results.result AND measure_ranges.interpretation = 'Positive'))," . "tests.specimen_id,NULL)) positive, " . "COUNT(DISTINCT IF((test_results.result='Negative' OR " . "(measure_ranges.alphanumeric = test_results.result AND measure_ranges.interpretation = 'Negative'))," . "tests.specimen_id,NULL)) negative, " . "ROUND(COUNT(DISTINCT IF((test_results.result = 'Positive' OR " . "(measure_ranges.alphanumeric = test_results.result AND measure_ranges.interpretation = 'Positive'))" . ", tests.specimen_id, NULL))*100/COUNT(DISTINCT tests.specimen_id ) , 2 ) AS rate"))->join('test_types', 'tests.test_type_id', '=', 'test_types.id')->join('testtype_measures', 'test_types.id', '=', 'testtype_measures.test_type_id')->join('measure_ranges', 'testtype_measures.measure_id', '=', 'measure_ranges.measure_id')->join('measures', 'testtype_measures.measure_id', '=', 'measures.id')->join('test_results', function ($join) { $join->on('tests.id', '=', 'test_results.test_id')->on('testtype_measures.measure_id', '=', 'test_results.measure_id'); })->join('measure_types', 'measure_types.id', '=', 'measures.measure_type_id')->whereIn('test_status_id', array(Test::COMPLETED, Test::VERIFIED))->where(function ($query) use($testTypeID) { if ($testTypeID != 0) { $query->where('tests.test_type_id', $testTypeID); } })->where(function ($query) { $query->where('measure_ranges.alphanumeric', '=', 'Positive')->orWhere('measure_ranges.alphanumeric', '=', 'Negative')->orWhere('measure_ranges.interpretation', '=', 'Positive')->orWhere('measure_ranges.interpretation', '=', 'Negative'); }); if ($ageRange) { $data = $data->join('visits', 'tests.visit_id', '=', 'visits.id')->join('patients', 'visits.patient_id', '=', 'patients.id'); $age = explode('-', $ageRange); $ageStart = $age[0]; $ageEnd = $age[1]; $now = new DateTime('now'); $clonedDate = clone $now; $finishDate = $clonedDate->sub(new DateInterval('P' . $ageStart . 'Y'))->format('Y-m-d'); $clonedDate = clone $now; $startDate = $clonedDate->sub(new DateInterval('P' . $ageEnd . 'Y'))->format('Y-m-d'); $data = $data->whereBetween('dob', [$startDate, $finishDate]); } $data = $data->whereBetween('time_created', array($from, $toPlusOne))->groupBy('test_types.id')->get(); return $data; }
/** * Get months: return months for time_created column when filter dates are set */ public static function getMonths($from, $to) { $today = "'" . date("Y-m-d") . "'"; $year = date('Y'); $tests = Test::select('time_created')->distinct(); if (strtotime($from) === strtotime($today)) { $tests = $tests->where('time_created', 'LIKE', $year . '%'); } else { $toPlusOne = date_add(new DateTime($to), date_interval_create_from_date_string('1 day')); $tests = $tests->whereBetween('time_created', array($from, $toPlusOne)); } $allDates = $tests->lists('time_created')->toArray(); asort($allDates); $yearMonth = function ($value) { return strtotime(substr($value, 0, 7)); }; $allDates = array_map($yearMonth, $allDates); $allMonths = array_unique($allDates); $dates = array(); foreach ($allMonths as $date) { $dateInfo = getdate($date); $dates[] = array('months' => $dateInfo['mon'], 'label' => substr($dateInfo['month'], 0, 3), 'annum' => $dateInfo['year']); } return json_encode($dates); }
/** * Get the tests performed by a user * * @return db resultset */ public static function getTestsPerformed($from, $to, $userID = 0) { $tests = Test::select(['id'])->whereBetween('time_completed', [$from, $to]); if ($userID > 0) { $tests = $tests->where('tested_by', '=', $userID); } return $tests->get(); }