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));
}
