Ejemplo n.º 1
0
 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;
     }
 }
Ejemplo n.º 2
0
 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;
     }
 }
Ejemplo n.º 3
0
 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']);
 }