private function getLegalStatusArrayForDBInsert($legal_status, $legal_description, $legal_exp_date, $id) { // get legal id using the legal name $legal_id = $this->findLegalStatusIdFromName($legal_status); // if legal id is found return an array suitable for DB insertion if ($legal_id != null) { // get date for DB insert $datesHelper = new DatesHelper(); $legal_exp_date = $datesHelper->makeDBFriendlyDate($legal_exp_date); return array('legal_lookup_id' => $legal_id, 'description' => '', 'exp_date' => null, 'benefiter_id' => $id); } else { // return null if legal status was not found return null; } }
public function searchBenefiters($request) { // checks if all_required is set and gives $allRequired a value $allRequired = !empty($request['all_required']) ? (bool) $request['all_required'] : false; $queryString = "select b.*, gl.gender, msl.marital_status_title, el.education_title, wll.description as legal_working_status, wtll.work_title, floor(datediff(current_date, str_to_date(b.birth_date, '%Y-%m-%d'))/365) as age_in_years, count(mv.id) as incidents_counter, date(b.created_at) as created_at_date from benefiters as b left join benefiters_legal_status as bls on b.id = bls.benefiter_id left join medical_visits as mv on b.id = mv.benefiter_id left join medical_examination_results as mer on mv.id = mer.medical_visit_id left join medical_medication as mm on mv.id = mm.medical_visit_id left join genders_lookup as gl on b.gender_id = gl.id left join marital_status_lookup as msl on b.marital_status_id = msl.id left join education_lookup as el on b.education_id = el.id left join working_legally_lookup as wll on b.working_legally = wll.id left join work_title_list_lookup as wtll on b.work_title_id = wtll.id"; $queryString2 = " and deleted_at is null group by b.id"; // if $allRequired is true, return all the benefiters from DB if ($allRequired) { Log::info("Returning all benefiters from DB."); return \DB::select(\DB::raw($queryString . " where 1=1" . $queryString2)); } $datesHelper = new DatesHelper(); $firstWhereParameter = true; $firstWhereParameterExternalSelect = true; if ($request['folder_number'] != "") { $queryString = $queryString . " where " . "b.folder_number like '%" . $request['folder_number'] . "%'"; $firstWhereParameter = false; } if ($request['lastname'] != "") { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . "b.lastname like '%" . $request['lastname'] . "%'"; $firstWhereParameter = false; } if ($request['fname'] != "") { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . "b.name like '%" . $request['fname'] . "%'"; $firstWhereParameter = false; } if ($request['fathers_name'] != "") { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . "b.fathers_name like '%" . $request['fathers_name'] . "%'"; $firstWhereParameter = false; } if ($request['telephone'] != "") { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . "b.telephone='" . $request['telephone'] . "'"; $firstWhereParameter = false; } if ($request['birth_date'] != "") { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . "b.birth_date='" . $datesHelper->makeDBSearchFriendlyDate($datesHelper->makeDBFriendlyDate($request['birth_date'])) . "'"; $firstWhereParameter = false; } if ($request['origin_country'] != "") { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . "b.origin_country='" . $request['origin_country'] . "'"; $firstWhereParameter = false; } if ($request['medical_location_id'] != 0) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . "mv.medical_location_id=" . $request['medical_location_id']; $firstWhereParameter = false; } if ($request['marital_status_id'] != 0) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'b.marital_status_id=' . $request['marital_status_id']; $firstWhereParameter = false; } if ($request['legal_status_id'] != 0) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'bls.legal_lookup_id=' . $request['legal_status_id']; $firstWhereParameter = false; } if ($request['education_id'] != 0) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'b.education_id=' . $request['education_id']; $firstWhereParameter = false; } if (!empty($request['gender_id'])) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'b.gender_id=' . $request['gender_id']; $firstWhereParameter = false; } if ($request['work_title_id'] != 0) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'b.work_title_id=' . $request['work_title_id']; $firstWhereParameter = false; } if ($request['drug'] != "") { $drugId = $this->getMedicationIdFromName($request['drug']); if ($drugId != null) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'mm.medication_lookup_id=' . $drugId; $firstWhereParameter = false; } } if ($request['incident_type_id'] != 0) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'mv.medical_incident_id=' . $request['incident_type_id']; $firstWhereParameter = false; } if ($request['doctor_name'] != "") { $doctors = $this->getDoctorIdFromName($request['doctor_name']); if ($doctors != null) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } if (count($doctors) > 1) { $queryString = $queryString . '('; $firstDoctor = true; foreach ($doctors as $doctor) { if (!$firstDoctor) { $queryString = $queryString . ' or '; } $queryString = $queryString . 'mv.doctor_id=' . $doctor->id; $firstDoctor = false; } $queryString = $queryString . ')'; } else { $queryString = $queryString . 'mv.doctor_id=' . $doctors[0]->id; } $firstWhereParameter = false; } } if ($request['examination_results_id'] != 0) { if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'mer.results_lookup_id=' . $request['examination_results_id']; $firstWhereParameter = false; } if ($request['incident_from'] != "" and $request['incident_to'] != "") { // if difference in days between the two dates is negative, the incident_to date is earlier if ($datesHelper->getDifferenceInDays($request['incident_from'], $request['incident_to']) < 0) { $tmp = $request['incident_from']; $request['incident_from'] = $request['incident_to']; $request['incident_to'] = $tmp; } if (!$firstWhereParameter) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'mv.medical_visit_date between \'' . $datesHelper->makeDBSearchFriendlyDate($datesHelper->makeDBFriendlyDate($request['incident_from'])) . '\' and \'' . $datesHelper->makeDBSearchFriendlyDate($datesHelper->makeDBFriendlyDate($request['incident_to'])) . '\''; $firstWhereParameter = false; } $queryString = "select * from (" . $queryString . $queryString2 . ") as median_table"; if ($request['age'] != "" and is_numeric($request['age'])) { $queryString = $queryString . " where "; $queryString = $queryString . 'age_in_years=' . $request['age']; $firstWhereParameterExternalSelect = false; } if ($request['incidents_number'] != "") { if (!$firstWhereParameterExternalSelect) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'incidents_counter=' . $request['incidents_number']; $firstWhereParameterExternalSelect = false; } if ($request['insertion_date'] != "") { if (!$firstWhereParameterExternalSelect) { $queryString = $queryString . " and "; } else { $queryString = $queryString . " where "; } $queryString = $queryString . 'created_at_date=\'' . $datesHelper->makeDBSearchFriendlyDate($datesHelper->makeDBFriendlyDate($request['insertion_date'])) . '\''; $firstWhereParameterExternalSelect = false; } // if there is a parameter passed to the query, make a DB query // else return null if (!$firstWhereParameter or !$firstWhereParameterExternalSelect) { Log::info("The search benefiter DB query is: " . $queryString); return \DB::select(\DB::raw($queryString)); } else { Log::info("No parameters passed from benefiter search form!"); return null; } }
private function getPsychosocialSessionArrayForDBEdit($request) { $datesHelper = new DatesHelper(); return array('session_date' => $datesHelper->makeDBFriendlyDate($request['session_date']), 'psychosocial_theme_id' => $request['psychosocial_theme'], 'session_comments' => $request['session_comments'], 'medical_location_id' => $request['medical_location_id']); }