public function getReportDataForRegisteredBenefiters() { try { // get benefiter registration number for any particular month. $benefitersCount = \DB::select(\DB::raw("select date_format(created_at, '%Y-%m') as created_at, count(id) as idcounter from benefiters group by date_format(created_at, '%Y %m')")); // $benefitersCount = \DB::select(\DB::raw("select created_at, count(id) as idcounter from benefiters group by year(created_at), month(created_at)")); } catch (\Exception $e) { Log::error("A problem occured while trying to count the users based on registration date.\n" . $e); return null; } Log::info("Returning result with users based on their registration date."); return $benefitersCount; }
private function getDoctorIdFromName($doctorName) { $tmp = \DB::select(\DB::raw('select id from users where (user_role_id = 1 or user_role_id = 2) and (lastname like "%' . $doctorName . '%" or name like "%' . $doctorName . '%")')); if ($tmp != null) { Log::info("Returning the doctors ids."); return $tmp; } else { Log::error("Couldn't find the doctor's id"); return null; } }
public function processWorkTime($emp_mx_id, $punch_date) { $total_work_time = 0; $in_times = []; $out_times = []; $difference = 0; $total_out_time = 0; $total_work_time = \DB::select(" SELECT\n TIMESTAMPDIFF(MINUTE, MIN(punch_trg_datetime), MAX(punch_trg_datetime)) total_time\n FROM tik_tok_attendance\n WHERE emp_mx_id = '{$emp_mx_id}' AND\n punch_trg_date = '{$punch_date}' AND DATE_FORMAT(punch_trg_datetime , '%H') >= 8 ;\n ")[0]->total_time; //Latha and Alex if ($emp_mx_id != 'MX057' || $emp_mx_id != 'MX076') { $all_in_time_array = \DB::select("SELECT punch_trg_datetime FROM tik_tok_attendance WHERE punch_trg_date = '{$punch_date}' AND emp_mx_id = '{$emp_mx_id}' AND punch_type = 'In' AND DATE_FORMAT(punch_trg_datetime , '%H') >= 8 ORDER BY punch_trg_datetime,punch_trg_id ASC ;"); $all_out_time_array = \DB::select("SELECT punch_trg_datetime FROM tik_tok_attendance WHERE punch_trg_date = '{$punch_date}' AND emp_mx_id = '{$emp_mx_id}' AND punch_type = 'Out' AND DATE_FORMAT(punch_trg_datetime , '%H') >= 8 ORDER BY punch_trg_datetime,punch_trg_id ASC ;"); foreach ($all_in_time_array as $all_in_time_array_item) { $in_times[] = $all_in_time_array_item->punch_trg_datetime; } foreach ($all_out_time_array as $all_out_time_array_item) { $out_times[] = $all_out_time_array_item->punch_trg_datetime; } $in_time_count = count($in_times); $out_time_count = count($out_times); if ($in_time_count == $out_time_count) { foreach ($out_times as $key => $out_time_value) { if (!empty($in_times[$key + 1])) { $num_temp = $key + 1; $difference = \DB::connection('mysql_dummy')->select(" SELECT TIMESTAMPDIFF(MINUTE, '{$in_times[$num_temp]}', '{$out_time_value}' ) difference ; ")[0]; $total_work_time += $difference->difference; $total_out_time += abs($difference->difference); } } } else { if ($in_time_count > $out_time_count) { $evaluate_date = new \DateTime($in_times[$in_time_count - 1]); $num_temp = $in_time_count - 1; if (intval($evaluate_date->format('H')) < 19) { } $difference = \DB::connection('mysql_dummy')->select(" SELECT TIMESTAMPDIFF(MINUTE, '{$in_times[$num_temp]}', '{$punch_date} 19:30:00' ) difference ; ")[0]; $total_work_time += $difference->difference; } } } return "{$total_work_time},{$total_out_time}"; }
private function getAllBenefitersForCsvFileDownload() { $queryString = "select b.*, gl.gender, msl.marital_status_title, el.education_title, wll.description " . "as legal_working_status, wtll.work_title, " . "carl.description as country_abandon_reason from benefiters as b " . "left join benefiters_legal_status as bls on b.id = bls.benefiter_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 " . "left join country_abandon_reasons_lookup as carl on b.country_abandon_reason_id = carl.id " . "where deleted_at is null group by b.id"; return \DB::select(\DB::raw($queryString)); }