'required', 'no_of_patients' => 'required', 'no_of_doc' => 'required', 'service_type_id' => 'required', 'schedule_from' => 'required', 'schedule_to' => 'required', ); public static function getLoggedInUserDetails($user_id) { return DB::table('users')->select('system_user_type', 'centre_id')->where(['id' => $user_id, 'flag' => 0])->first(); } /* * get slot details monthly * created on: 24/07/2017 */ public static function manageMonthlyView($param = array()) { $center_id = $param['center_id']; $month = $param['month']; $day = $param['day']; $week = $param['week']; $year = isset($param['year']) ? $param['year'] : date('Y'); $opd = $param['opd']; $slot_view_type = $param['view_type']; //dd($slot_view_type); $db_prefix = config('constants.DB_PREFIX'); $return = []; //setting the condition based on which view Month,day,week if (!empty($month)) { $result = Slot::select( 'centre_slot_breakdown.id', 'centre_slot_breakdown.location_id', 'centre_slot_breakdown.centre_id', 'centre_slot_breakdown.service_type_id', 'centre_slot_breakdown.date', // 'centre_slot_breakdown.no_of_patients ', 'centre_slot_breakdown.no_of_patients', 'centre_slot_breakdown.no_of_doctors', 'centre_slot_breakdown.is_inactive', 'centre_slot_breakdown.flag', DB::raw("DAYOFWEEK(date)day_index"), DB::raw("DAYNAME(date)day_name"), // DB::raw( // '(select COUNT(*) from '.$db_prefix.'slots where '.$db_prefix.'slots.centre_slot_breakdown_id = '.$db_prefix.'centre_slot_breakdown.id AND '.$db_prefix.'slots.flag = 0) available_slot_count' // ), DB::raw('SUM(maximum_booking_allowed) AS maximum_booking_allowed'), DB::raw('SUM(maximum_booking_allowed) AS available_slot_count'), DB::raw('SUM(no_of_booking) AS occupied_slot_count'), DB::raw("SUM(maximum_booking_allowed - no_of_booking)available") // DB::raw('(select COUNT(*) from '.$db_prefix.'slots where '.$db_prefix.'slots.centre_slot_breakdown_id = '.$db_prefix.'centre_slot_breakdown.id AND '.$db_prefix.'slots.flag = 1) occupied_slot_count') ) ->whereRaw('MONTH(date) = ' . $month . '') ->where(['centre_slot_breakdown.centre_id' => $center_id]) ->where(['centre_slot_breakdown.service_type_id' => $opd]) ->whereIn('centre_slot_breakdown.flag', [0, 1]) ->leftjoin('slots', 'slots.centre_slot_breakdown_id', '=', 'centre_slot_breakdown.id'); if ($slot_view_type == 'monthly') { $result->groupBy('slots.centre_slot_breakdown_id'); } $slots = $result->get(); if (count($slots) > 0) { //params of result// $return['day_slot_pair'] = ''; //$return['only_days'] = ''; //in 01,02,---31 $return['weekly_off'] = ''; //Center wise off days in a week $return['holidays'] = ''; //According to AKJ holoday calender $return['first_weeks_day_slot_pair'] = ''; //when the month starts not from Monday $return['holidays'] = ''; //According to AKJ holoday calender //params of result $first_weeks_day_slot_pair = []; $rest_weeks_day_slot_pair = []; $weekly_off = []; $holidays = []; $inactive_days = []; //geting the inactive days// foreach ($slots as $key => $single) { if ($single->flag == 1 || $single->is_inactive) { $inactive_days[] = date('d', strtotime($single->date)); } } //geting the inactive days// $start_date_of_the_slot = $slots[0]->date; // First day of the month. $start_date = date('Y-m-01', strtotime($start_date_of_the_slot)); $the_month_start_on_day_number = date('N', strtotime($start_date)) + 1; // +1 is done to match the day number with the one we are getting from query. As sql is starting the week from Sunday and PHP is starting the week from Monday // Last day of the month. $end_date = date('Y-m-t', strtotime($start_date_of_the_slot)); //Getting all days of a given month i.e 01,02----31 $only_days = Slot::date_range($start_date, $end_date, $step = '+1 day', $output_format = 'd'); //Making all the days associate with respective slot details if (!empty($only_days)) { //$day_slot_pair = ''; $day_num = $the_month_start_on_day_number; foreach ($only_days as $key => $a_day) { foreach ($slots as $index => $a_day_slot) { $day_slot_pair[$key]['day_num'] = $day_num; $the_day_of_the_month = date('d', strtotime($a_day_slot->date)); if ($the_day_of_the_month == $a_day) { //dd($the_day_of_the_month); $day_slot_pair[$key]['day'] = $a_day; $day_slot_pair[$key]['slot'] = $a_day_slot; } else { $day_slot_pair[$key]['day'] = $a_day; } } $day_num = $day_num + 1; if ($day_slot_pair[$key]['day_num'] == 7) { $day_num = 1; } } } //Making all the days associate with respective slot details foreach ($day_slot_pair as $key => $value) { $date = $year . '-' . $month . '-' . $value['day']; $day_slot_pair[$key]['full_date'] = $date; } //Making all the days associate with respective slot details //Making above array split into chunks of 7 if ($the_month_start_on_day_number == 2) { $rest_weeks_day_slot_pair = array_chunk($day_slot_pair, 7); } else { $first_week_day_values = 7 - ($the_month_start_on_day_number - 2); $first_weeks_day_slot_pair = []; for ($i = 0; $i < $first_week_day_values; $i++) { $first_weeks_day_slot_pair[] = $day_slot_pair[$i]; } $rest_day_slot_pair = array_slice($day_slot_pair, $first_week_day_values); $rest_weeks_day_slot_pair = array_chunk($rest_day_slot_pair, 7); } //Making above array split into chunks of 7 //Getting all dates of a given month in 2020-00-01 format $get_dates = Slot::date_range($start_date, $end_date, $step = '+1 day', $output_format = 'Y-m-d'); //Getting center details $center_details = DB::table('centre_attributes') ->select('booking_starts_from', 'working_hrs', 'lunch_starts_from', 'lunch_ends_at', 'weekday') ->where(['centre_id' => $center_id, 'flag' => 0])->first(); //weekday is the days when center remains closed weekly $weekly_off = $center_details->weekday; //Formatting the weekly off indeces into the format supported by PHP. if (!empty($weekly_off)) { $weekly_off = explode(',', $weekly_off); foreach ($weekly_off as $key => $offday) { $weekly_off[$key] = $offday + 1; } } //Getting Holiday if any in the given duration $holidays = Slot::identifyHolidays($get_dates); //Results needed for the month_view of a slot $return['first_weeks_day_slot_pair'] = $first_weeks_day_slot_pair; $return['day_slot_pair'] = $rest_weeks_day_slot_pair; //$return['only_days'] = $only_days; //in 01,02,---31 $return['weekly_off'] = $weekly_off; $return['holidays'] = $holidays; $return['the_month_start_on_day_number'] = $the_month_start_on_day_number; $return['inactive_days'] = $inactive_days; //pr($return); die; return $return; } else { return $return; } } else { return $return; } } /* * get slot details Weekly * created on: 28/09/2020 */ public static function manageWeeklyView($param = array()) { //dd($param); $return = []; $center_id = $param['center_id']; $month = $param['month']; $day = $param['day']; $week = $param['week']; $week_starts = $param['week_starts']; $week_ends = $param['week_ends']; $year = isset($param['year']) ? $param['year'] : date('Y'); $opd = $param['opd']; $slot_view_type = $param['view_type']; //Getting center details $center_details = DB::table('centre_attributes') ->select('booking_starts_from', 'working_hrs', 'lunch_starts_from', 'lunch_ends_at', 'weekday') ->where(['centre_id' => $center_id, 'flag' => 0])->first(); //weekday is the days when center remains closed weekly //END if (!empty($center_details)) { $slot_created = false; //To check whether any slot has been created or not $startDay = empty($week_starts) ? date('Y-m-d', strtotime('monday this week')) : $week_starts; //Monday //$lastDay_in_which_month = date( 'Y-m-d', strtotime( 'sunday this week' ) ); //usualy Sunday $lastDay = empty($week_ends) ? date('Y-m-d', strtotime('sunday this week')) : $week_ends; $view_from_month = date('F', strtotime($startDay)); $view_to_month = date('F', strtotime($lastDay)); $curreent_week = date('dS', strtotime($startDay)) . ' ' . $view_from_month . ' - ' . date('dS', strtotime($lastDay)) . ' ' . $view_to_month; //Getting the array of inactive days $weekly_off = $center_details->weekday; //you must add 1 to start the week from Sunday if (!empty($weekly_off)) { $weekly_off = explode(',', $weekly_off); foreach ($weekly_off as $key => $offday) { $weekly_off[$key] = $offday + 1; } } //END $days_and_holiday = Slot::getHolidayListSlotCreation([ 'schedule_from' => $startDay, 'schedule_to' => $lastDay, ]); $get_all_days = $days_and_holiday['all_dates']; foreach ($get_all_days as $key => $value) { //dd($value); $w = date("w", strtotime($value['each_day'])) + 1; //you must add 1 to start the week from Sunday if (in_array($w, $weekly_off)) { $get_all_days[$key]['is_inactive'] = 1; } } //$get_all_days = Slot::date_range($startDay, $lastDay, $step = '+1 day', $output_format = 'Y-m-d' ); //generating the day's name and the date associated with it array for generating the Table Header $date_name_arr = []; if (!empty($get_all_days)) { foreach ($get_all_days as $index => $a_day) { $date_name_arr[$index]['date'] = $a_day['each_day']; $date_name_arr[$index]['day_num'] = date('w', strtotime($a_day['each_day'])) + 1; //in php date, the week starts from sunday with index 0. incrementing by 1, to make sure week starts counting from 1. $date_name_arr[$index]['day_name'] = date('D', strtotime($a_day['each_day'])); $date_name_arr[$index]['is_inactive'] = $a_day['is_inactive']; } } $return['date_name_arr'] = $date_name_arr; //END //Week ends// $weekly_off = $center_details->weekday; //Formatting the weekly off indeces into the format supported by PHP. if (!empty($weekly_off)) { $weekly_off = explode(',', $weekly_off); foreach ($weekly_off as $key => $offday) { $weekly_off[$key] = $offday + 1; } } $return['weekly_off'] = $weekly_off; //Center wise off days in a week $return['inactive_days'] = []; //Week ends// //Getting total working hours $lunch_hour_duration_in_sec = strtotime($center_details->lunch_ends_at) - strtotime($center_details->lunch_starts_from); $working_hour_ends_at_sec = strtotime($center_details->booking_starts_from) + $lunch_hour_duration_in_sec + $center_details->working_hrs * 60 * 60; $working_hour_ends_at = date('g:i A', $working_hour_ends_at_sec); //Getting total working hours //Getting total working hour in hourly basis $step = $opd == 3 ? 2 * 60 * 60 : 60 * 60; // range of maximum 2 hours for surgery $range_before_lunch = Slot::slot_range($center_details->booking_starts_from, $center_details->lunch_starts_from, $step); // 1 hours each $range_after_lunch = Slot::slot_range($center_details->lunch_ends_at, $working_hour_ends_at, $step); // 1 hours each //$range = Slot::slot_range($center_details->booking_starts_from, $working_hour_ends_at, $step); // 1 hours each $range['slots'] = array_merge($range_before_lunch['slots'], $range_after_lunch['slots']); $db_prefix = config('constants.DB_PREFIX'); $result = []; $day_slot_pair = []; if (!empty($range)) { $slotarr = []; foreach ($range['slots'] as $in => $value) { $range_arr = explode('-', $value); //$slotarr[$in]['is_inactive'] = $slots->is_inactive; $slot_starts_at = $range_arr[0]; $slot_ends_at = $range_arr[1]; $slotarr[$in]['slot_starts_from'] = $slot_starts_at; $slotarr[$in]['slot_ends_at'] = $slot_ends_at; $slotarr[$in]['range'] = $value; if (strtotime($range_arr[0]) == strtotime($center_details->lunch_starts_from)) { $slotarr[$in]['lunch_started'] = 1; } else { $slotarr[$in]['lunch_started'] = 0; } //dd($get_all_days); if (!empty($get_all_days)) { $day_slot_details = []; foreach ($get_all_days as $key => $a_single_day) { //dd($a_single_day); $slot_starts_at_datetime = date('H:i:s', strtotime($slot_starts_at)); $slot_ends_at_datetime = date('H:i:s', strtotime($slot_ends_at)); if ($slotarr[$in]['lunch_started'] == 0) { if ($a_single_day['each_day'] == "2020-10-22") { //dd($slot_ends_at_datetime); } $result = Slot::select( 'centre_slot_breakdown.id', 'centre_slot_breakdown.location_id', 'centre_slot_breakdown.centre_id', 'centre_slot_breakdown.service_type_id', 'centre_slot_breakdown.date', 'centre_slot_breakdown.no_of_patients', 'centre_slot_breakdown.no_of_doctors', 'centre_slot_breakdown.is_inactive', 'centre_slot_breakdown.flag', 'slots.flag as slot_flag', DB::raw("DAYOFWEEK(date)day_index"), DB::raw("DAYNAME(date)day_name"), DB::raw( '(select COUNT(*) from ' . $db_prefix . 'slots where ' . $db_prefix . 'slots.centre_slot_breakdown_id = ' . $db_prefix . 'centre_slot_breakdown.id AND ' . $db_prefix . 'slots.flag = 0) available_slot_count' ), DB::raw('(select COUNT(*) from ' . $db_prefix . 'slots where ' . $db_prefix . 'slots.centre_slot_breakdown_id = ' . $db_prefix . 'centre_slot_breakdown.id AND ' . $db_prefix . 'slots.flag = 1) occupied_slot_count'), DB::raw("SUM(maximum_booking_allowed)total_maximum_booking_allowed"), DB::raw("SUM(no_of_booking)total_no_of_booking"), DB::raw("SUM(maximum_booking_allowed - no_of_booking)available") ) ->where(['centre_slot_breakdown.centre_id' => $center_id]) ->where(['centre_slot_breakdown.service_type_id' => $opd]) ->where(['date' => $a_single_day['each_day']]) ->whereIn('centre_slot_breakdown.flag', [0, 1]) ->whereBetween('slots.start_datetime', [$slot_starts_at_datetime, $slot_ends_at_datetime]) ->where('slots.end_datetime', '<=', $slot_ends_at_datetime) ->whereIn('slots.flag', [0, 1]) ->leftjoin('slots', 'slots.centre_slot_breakdown_id', '=', 'centre_slot_breakdown.id') ->orderBy('date', 'ASC'); $result->groupBy('slots.centre_slot_breakdown_id'); $slots = $result->get(); $slot_status = ''; //Active Slot $day_slot_details[$key]['slot_result'] = $slots; if (count($slots) == 0) { $day_slot_details[$key]['is_inactive'] = $a_single_day['is_inactive']; $day_slot_details[$key]['slot_status'] = ''; } else { $slot_created = true; $day_slot_details[$key]['is_inactive'] = $slots[0]->is_inactive; $slot_status = $slots[0]->slot_flag == 1 ? 'inactive' : 'active'; $day_slot_details[$key]['slot_status'] = $slot_status; } $day_slot_details[$key]['date'] = $a_single_day['each_day']; } else { $day_slot_details[$key]['slot_status'] = ''; $day_slot_details[$key]['slot_result'] = []; $day_slot_details[$key]['date'] = $a_single_day['each_day']; } } } $slotarr[$in]['day_slot_details'] = $day_slot_details; } } // dd($slotarr); //setting the condition based on which view Month,day,week $return['weekly_off'] = $weekly_off; $return['slotarr'] = $slotarr; $return['current_week'] = $curreent_week; $return['slot_created'] = $slot_created; $return['weeks_monday'] = $startDay; $return['weeks_sunday'] = $lastDay; //dd($return); return $return; } else { return $return; } } /* * get slot details Daily * created on: 28/09/2020 */ public static function manageDailyView($param = array()) { $center_id = $param['center_id']; $month = $param['month']; $day = $param['day']; $week = $param['week']; $year = isset($param['year']) ? $param['year'] : date('Y'); $opd = $param['opd']; $date = $param['date']; $slot_view_type = $param['view_type']; $todays_date = empty($day) ? date('Y-m-d') : $date; $db_prefix = config('constants.DB_PREFIX'); $return = []; //setting the condition based on which view Month,day,week $result = Slot::select( 'centre_slot_breakdown.id', 'centre_slot_breakdown.location_id', 'centre_slot_breakdown.centre_id', 'centre_slot_breakdown.service_type_id', 'centre_slot_breakdown.date', 'centre_slot_breakdown.no_of_patients', 'centre_slot_breakdown.no_of_doctors', 'centre_slot_breakdown.is_inactive', 'centre_slot_breakdown.flag', DB::raw("DAYOFWEEK(date)day_index"), DB::raw("DAYNAME(date)day_name"), DB::raw( '(select COUNT(*) from ' . $db_prefix . 'slots where ' . $db_prefix . 'slots.centre_slot_breakdown_id = ' . $db_prefix . 'centre_slot_breakdown.id AND ' . $db_prefix . 'slots.flag = 0) available_slot_count' ), DB::raw('(select COUNT(*) from ' . $db_prefix . 'slots where ' . $db_prefix . 'slots.centre_slot_breakdown_id = ' . $db_prefix . 'centre_slot_breakdown.id AND ' . $db_prefix . 'slots.flag = 1) occupied_slot_count') ) ->whereRaw('MONTH(date) = ' . $month . '') ->where(['centre_slot_breakdown.centre_id' => $center_id]) ->where(['centre_slot_breakdown.service_type_id' => $opd]) ->where(['centre_slot_breakdown.date' => $todays_date]) ->whereIn('centre_slot_breakdown.is_inactive', [0, 1]) ->whereIn('centre_slot_breakdown.flag', [0, 1]) ->leftjoin('slots', 'slots.centre_slot_breakdown_id', '=', 'centre_slot_breakdown.id'); $result->groupBy('slots.centre_slot_breakdown_id'); $slots = $result->get(); //Data from center_slot breakdown table of a partiular date. if (count($slots) > 0) { $no_of_chambers = $slots[0]->no_of_doctors; //params of result// $return['weekly_off'] = ''; //Center wise off days in a week $return['holidays'] = ''; //According to AKJ holoday calender //params of result// $weekly_off = []; $holidays = []; //Getting the slot details of each day $slots_details = DB::table('slots') ->select('id', 'start_time', 'end_time', 'no_of_booking', 'maximum_booking_allowed', 'flag', DB::raw('(maximum_booking_allowed-no_of_booking)available') ) ->where(['slots.centre_slot_breakdown_id' => $slots[0]->id]) ->get(); //Getting the chamber details of each slot if (!empty($slots_details)) { foreach ($slots_details as $key => $slot) { $chambers = DB::table('slot_chambers')->select('id', 'flag', 'slot_id', 'chambers')->where(['slot_id' => $slot->id])->get(); $slots_details[$key]->chamber_details = $chambers; } } //getting slot details of each chember// $chamber_slot_pair = []; $chamber_occupied = []; for ($i = 0; $i < $no_of_chambers; $i++) { $chamber_slot_pair[$i]['chamber_no'] = $i + 1; $chamber_slot_pair[$i]['slot_details'] = $slots_details; foreach ($slots_details as $key => $slot) { for ($k = 1; $k <= $slot->no_of_booking; $k++) { $chamber_occupied[] = $k; } $slot->chamber_occupied = $chamber_occupied; $chamber_occupied = []; } } //dd($chamber_slot_pair); //End $center_slot_breakdown_id = $slots[0]->id; //Getting slot breakup per day $slots_breakup = DB::table('slots') ->select('id', 'start_time', 'end_time') ->where(['slots.centre_slot_breakdown_id' => $center_slot_breakdown_id]) ->distinct('start_time') ->get(); //Getting slots breakup per day //Getting all dates of a given month in 2020-00-01 format $get_dates = Slot::date_range($todays_date, $todays_date, $step = '+1 day', $output_format = 'Y-m-d'); //Getting center details $center_details = DB::table('centre_attributes') ->select('booking_starts_from', 'working_hrs', 'lunch_starts_from', 'lunch_ends_at', 'weekday') ->where(['centre_id' => $center_id, 'flag' => 0])->first(); //weekday is the days when center remains closed weekly $weekly_off = $center_details->weekday; //Formatting the weekly off indeces into the format supported by PHP. if (!empty($weekly_off)) { $weekly_off = explode(',', $weekly_off); foreach ($weekly_off as $key => $offday) { $weekly_off[$key] = $offday + 1; } } //Getting the lunch hour //Getting the lunch hour $lunch_hour = date('g:i A', strtotime($center_details->lunch_starts_from)); //Getting the lunch hour //getting the number of slots before the lunch time starts $no_of_slot_before_lunch = 0; foreach ($slots_breakup as $key => $value) { if (strtotime($value->start_time) < strtotime($lunch_hour)) { $no_of_slot_before_lunch++; } } //END //Getting Holiday if any in the given duration $holidays = Slot::identifyHolidays($get_dates); //Results needed for the month_view of a slot $return['no_of_chambers'] = $no_of_chambers; $return['slots_breakup'] = $slots_breakup; $return['chamber_slot_pair'] = $chamber_slot_pair; //$return['slots'] = $slots; //$return['slots_breakup'] = $slots_breakup; //$return['only_days'] = $only_days; //in 01,02,---31 $return['weekly_off'] = $weekly_off; $return['holidays'] = $holidays; $return['lunch_starts'] = $lunch_hour; $return['is_an_inactive_day'] = $slots[0]->is_inactive; $return['no_of_slot_before_lunch'] = $no_of_slot_before_lunch; $return['todays_date'] = $todays_date; return $return; } else { return $return; } } /* * get Surgery slot details Daily based on time span * created on: 06/10/2020 */ public static function getSurgerySlotDayTimeWise($param = array()) { $center_id = $param['center_id']; $opd = $param['opd']; $todays_date = date('Y-m-d', strtotime($param['date'])); $span = $param['span']; $span_arr = explode('-', $span); $span_starts_at = $span_arr[0]; $span_ends_at = $span_arr[1]; $db_prefix = config('constants.DB_PREFIX'); $return = []; //dd($span_ends_at); //setting the condition based on which view Month,day,week $result = Slot::select( 'centre_slot_breakdown.id', 'centre_slot_breakdown.location_id', 'centre_slot_breakdown.centre_id', 'centre_slot_breakdown.service_type_id', 'centre_slot_breakdown.date', 'centre_slot_breakdown.no_of_patients', 'centre_slot_breakdown.no_of_doctors', 'centre_slot_breakdown.is_inactive', 'centre_slot_breakdown.flag', DB::raw("DAYOFWEEK(date)day_index"), DB::raw("DAYNAME(date)day_name"), DB::raw( '(select COUNT(*) from ' . $db_prefix . 'slots where ' . $db_prefix . 'slots.centre_slot_breakdown_id = ' . $db_prefix . 'centre_slot_breakdown.id AND ' . $db_prefix . 'slots.flag = 0) available_slot_count' ), DB::raw('(select COUNT(*) from ' . $db_prefix . 'slots where ' . $db_prefix . 'slots.centre_slot_breakdown_id = ' . $db_prefix . 'centre_slot_breakdown.id AND ' . $db_prefix . 'slots.flag = 1) occupied_slot_count') ) ->where(['centre_slot_breakdown.centre_id' => $center_id]) ->where(['centre_slot_breakdown.service_type_id' => $opd]) ->where(['centre_slot_breakdown.date' => $todays_date]) ->whereIn('centre_slot_breakdown.is_inactive', [0, 1]) ->whereIn('centre_slot_breakdown.flag', [0, 1]) ->leftjoin('slots', 'slots.centre_slot_breakdown_id', '=', 'centre_slot_breakdown.id'); $result->groupBy('slots.centre_slot_breakdown_id'); $slots = $result->get(); //Data from center_slot breakdown table of a partiular date. if (count($slots) > 0) { $no_of_chambers = $slots[0]->no_of_doctors; //params of result// $return['weekly_off'] = ''; //Center wise off days in a week $return['holidays'] = ''; //According to AKJ holoday calender //params of result// $weekly_off = []; $holidays = []; $span_starts_at_datetime = date("H:i:s", strtotime($span_starts_at)); $span_ends_at_datetime = date("H:i:s", strtotime($span_ends_at)); //dd($starts_at_datetime); //dd(date("H:i", strtotime($span_ends_at))); //Getting the slot details of each day $slots_details = DB::table('slots') ->select('id', 'start_time', 'no_of_booking', 'maximum_booking_allowed', 'flag', DB::raw('(maximum_booking_allowed-no_of_booking)available') ) ->where(['slots.centre_slot_breakdown_id' => $slots[0]->id]) ->whereBetween('start_datetime', [$span_starts_at_datetime, $span_ends_at_datetime]) ->where('slots.end_datetime', '<=', $span_ends_at_datetime) ->get(); //Getting the chamber details of each slot if (!empty($slots_details)) { foreach ($slots_details as $key => $slot) { $chambers = DB::table('slot_chambers')->select('id', 'flag', 'slot_id', 'chambers')->where(['slot_id' => $slot->id])->get(); $slots_details[$key]->chamber_details = $chambers; } } //getting slot details of each chember// $chamber_slot_pair = []; $chamber_occupied = []; for ($i = 0; $i < $no_of_chambers; $i++) { $chamber_slot_pair[$i]['chamber_no'] = $i + 1; $chamber_slot_pair[$i]['slot_details'] = $slots_details; foreach ($slots_details as $key => $slot) { for ($k = 1; $k <= $slot->no_of_booking; $k++) { $chamber_occupied[] = $k; } $slot->chamber_occupied = $chamber_occupied; $chamber_occupied = []; } } //dd($chamber_slot_pair); //End $center_slot_breakdown_id = $slots[0]->id; //Getting slot breakup per span basis $slots_breakup = DB::table('slots') ->select('id', 'start_time') ->where(['slots.centre_slot_breakdown_id' => $center_slot_breakdown_id]) ->whereBetween('start_datetime', [$span_starts_at_datetime, $span_ends_at_datetime]) ->where('slots.end_datetime', '<=', $span_ends_at_datetime) ->distinct('start_time') ->get(); //Getting slots breakup per day //Getting all dates of a given month in 2020-00-01 format $get_dates = Slot::date_range($todays_date, $todays_date, $step = '+1 day', $output_format = 'Y-m-d'); //Getting center details $center_details = DB::table('centre_attributes') ->select('booking_starts_from', 'working_hrs', 'lunch_starts_from', 'lunch_ends_at', 'weekday') ->where(['centre_id' => $center_id, 'flag' => 0])->first(); //weekday is the days when center remains closed weekly $weekly_off = $center_details->weekday; //Formatting the weekly off indeces into the format supported by PHP. if (!empty($weekly_off)) { $weekly_off = explode(',', $weekly_off); foreach ($weekly_off as $key => $offday) { $weekly_off[$key] = $offday + 1; } } //Getting the lunch hour //Getting the lunch hour $lunch_hour = date('g:i A', strtotime($center_details->lunch_starts_from)); //Getting the lunch hour //getting the number of slots before the lunch time starts $no_of_slot_before_lunch = 0; foreach ($slots_breakup as $key => $value) { if (strtotime($value->start_time) < strtotime($lunch_hour)) { $no_of_slot_before_lunch++; } } //END //Getting Holiday if any in the given duration $holidays = Slot::identifyHolidays($get_dates); //Results needed for the month_view of a slot $return['no_of_chambers'] = $no_of_chambers; $return['slots_breakup'] = $slots_breakup; $return['chamber_slot_pair'] = $chamber_slot_pair; //$return['slots'] = $slots; //$return['slots_breakup'] = $slots_breakup; //$return['only_days'] = $only_days; //in 01,02,---31 $return['weekly_off'] = $weekly_off; $return['holidays'] = $holidays; $return['lunch_starts'] = $lunch_hour; $return['is_an_inactive_day'] = $slots[0]->is_inactive; $return['no_of_slot_before_lunch'] = $no_of_slot_before_lunch; // dd($return); return $return; } else { //dd($return); return $return; } } public function getAddedByAttribute() { return (!empty($this->first_name) && !empty($this->last_name)) ? $this->first_name . " " . $this->last_name : 'N/A'; } public function getUpdatedByAttribute() { return (!empty($this->updated_by_first_name) && !empty($this->updated_by_last_name)) ? $this->updated_by_first_name . " " . $this->updated_by_last_name : 'N/A'; } //Getting the date list with the Holiday Flag (0 => not a holiday 1 => A holiday) public static function getHolidayList($param = []) { $result = ['all_dates' => '', 'holidays' => '']; $get_dates = Slot::date_range($param['schedule_from'], $param['schedule_to'], $step = '+1 day', $output_format = 'Y-m-d'); $holidays = Slot::identifyHolidays($get_dates); $result['all_dates'] = $get_dates; $result['holidays'] = $holidays; return $result; } //Getting the date list with the Holiday Flag (0 => not a holiday 1 => A holiday) //Getting the date list with the Holiday Flag (0 => not a holiday 1 => A holiday) public static function getHolidayListSlotCreation($param = []) { $result = ['all_dates' => '', 'holidays' => '']; $get_dates = Slot::date_range($param['schedule_from'], $param['schedule_to'], $step = '+1 day', $output_format = 'Y-m-d'); $all_dates = []; $holidays = Slot::identifyHolidaysSlotCreation($get_dates); if (!empty($holidays) && !empty($get_dates)) { foreach ($get_dates as $key => $value) { $all_dates[$key]['each_day'] = $value; $all_dates[$key]['is_inactive'] = in_array($value, $holidays) ? 1 : 0; } $result['all_dates'] = $all_dates; $result['holidays'] = $holidays; } else { if (empty($holidays) && !empty($get_dates)) { foreach ($get_dates as $index => $value) { $all_dates[$index]['each_day'] = $value; $all_dates[$index]['is_inactive'] = 0; } $result['all_dates'] = $all_dates; $result['holidays'] = $holidays; } } return $result; } //Getting the date list with the Holiday Flag (0 => not a holiday 1 => A holiday) //function to identify holidays in a given day list// public static function identifyHolidays($days) { $holiday_flagged = []; $month = date('m', strtotime($days[0])); $year = date('Y', strtotime($days[0])); $holiday_details = DB::table('holiday') ->select('date_from', 'date_to') ->where('flag', '=', 0) ->get(); if (!empty($holiday_details) && count($holiday_details) > 0 && !empty($days) && count($days) > 0) { foreach ($days as $key => $each_day) { foreach ($holiday_details as $key => $assigned_holiday) { if (($assigned_holiday->date_from <= $each_day) && ($each_day <= $assigned_holiday->date_to)) { $holiday_flagged[] = date('d', strtotime($each_day)); } } } } return $holiday_flagged; } //function to identify holidays in a given day list// //function to identify holidays in a given day list while slot creation// public static function identifyHolidaysSlotCreation($days) { $holiday_flagged = []; $month = date('m', strtotime($days[0])); $year = date('Y', strtotime($days[0])); $holiday_details = DB::table('holiday') ->select('date_from', 'date_to') ->where('flag', '=', 0) ->get(); if (!empty($holiday_details) && count($holiday_details) > 0 && !empty($days) && count($days) > 0) { foreach ($days as $key => $each_day) { foreach ($holiday_details as $key => $assigned_holiday) { if (($assigned_holiday->date_from <= $each_day) && ($each_day <= $assigned_holiday->date_to)) { $holiday_flagged[] = date('Y-m-d', strtotime($each_day)); } } } } return $holiday_flagged; } //function to identify holidays in a given day list// //Geting all the dates within a selected date range public static function date_range($first, $last, $step, $output_format) { $dates = array(); $current = strtotime($first); $last = strtotime($last); while ($current <= $last) { $dates[] = date($output_format, $current); $current = strtotime($step, $current); } return $dates; } //Geting all the dates within a selected date range //Slot cration functions Backup public static function getSlotArray($param = []) { $center_details = DB::table('centre_attributes') ->select('booking_starts_from', 'working_hrs', 'lunch_starts_from', 'lunch_ends_at') ->where(['location_id' => $param['location_id'], 'centre_id' => $param['center_id'], 'flag' => 0])->first(); $slot_duration_in_sec = $param['slot_duration_in_sec']; $merged = ''; if (!empty($center_details)) { $lunch_hour_duration_in_sec = strtotime($center_details->lunch_ends_at) - strtotime($center_details->lunch_starts_from); //$lunch_hour_duration_in_hour = ($lunch_hour_duration_in_sec /3600); $working_hour_ends_at_sec = strtotime($center_details->booking_starts_from) + $lunch_hour_duration_in_sec + $center_details->working_hrs * 60 * 60; $working_hour_ends_at = date('g:i A', $working_hour_ends_at_sec); $first_half_starts_at = $center_details->booking_starts_from; $first_half_ends_at = $center_details->lunch_starts_from; $slot_arr_before_lunch = Slot::slot_range($first_half_starts_at, $first_half_ends_at, $slot_duration_in_sec); //$slot_after_lunch_starts_at = date('g:i A',(strtotime($slot_arr_before_lunch['slot_ends']) + $lunch_hour_duration_in_sec)); $slot_after_lunch_starts_at = $center_details->lunch_ends_at; $slot_arr_after_lunch = Slot::slot_range($slot_after_lunch_starts_at, $working_hour_ends_at, $slot_duration_in_sec); $merged = array_merge($slot_arr_before_lunch['slots'], $slot_arr_after_lunch['slots']); } return $merged; } //Slot cration functions public static function getSlotArrayCreation($param = []) { $center_details = DB::table('centre_attributes') ->select('booking_starts_from', 'working_hrs', 'lunch_starts_from', 'lunch_ends_at') ->where(['location_id' => $param['location_id'], 'centre_id' => $param['center_id'], 'flag' => 0])->first(); $slot_duration_in_sec = $param['slot_duration_in_sec']; $merged = ''; if (!empty($center_details)) { $lunch_hour_duration_in_sec = strtotime($center_details->lunch_ends_at) - strtotime($center_details->lunch_starts_from); //$lunch_hour_duration_in_hour = ($lunch_hour_duration_in_sec /3600); $working_hour_ends_at_sec = strtotime($center_details->booking_starts_from) + $lunch_hour_duration_in_sec + $center_details->working_hrs * 60 * 60; $working_hour_ends_at = date('g:i A', $working_hour_ends_at_sec); $first_half_starts_at = $center_details->booking_starts_from; $first_half_ends_at = $center_details->lunch_starts_from; $slot_arr_before_lunch = Slot::slot_range($first_half_starts_at, $first_half_ends_at, $slot_duration_in_sec); //dd($slot_arr_before_lunch); //$slot_after_lunch_starts_at = date('g:i A',(strtotime($slot_arr_before_lunch['slot_ends']) + $lunch_hour_duration_in_sec)); $slot_after_lunch_starts_at = $center_details->lunch_ends_at; $slot_arr_after_lunch = Slot::slot_range($slot_after_lunch_starts_at, $working_hour_ends_at, $slot_duration_in_sec); $merged = array_merge($slot_arr_before_lunch['slots'], $slot_arr_after_lunch['slots']); } return $merged; } public static function slot_range($first, $last, $step) { $slots = array(); $current = strtotime($first); $last_slot_ends_at = $last; $last = strtotime($last); $res = []; $loop = true; $slot_ends = ''; $expected_end_of_next_slot = $current + $step; while ($expected_end_of_next_slot <= $last) { $from_slot = date('g:i A', $current); $to_slot = date('g:i A', ($current + $step)); $slots[] = $from_slot . '-' . $to_slot; $current = $current + $step; $expected_end_of_next_slot = $current + $step; $slot_ends = date('g:i A', $current); } $res['slots'] = $slots; $res['slot_ends'] = $slot_ends; return $res; } public static function slot_range_creation($first, $last, $step) { $slots = array(); $current = strtotime($first); $last_slot_ends_at = $last; $last = strtotime($last); $res = []; $loop = true; $slot_ends = ''; $expected_end_of_next_slot = $current + $step; while ($current <= $last) { $from_slot = date('g:i A', $current); $to_slot = date('g:i A', ($current + $step)); $slots[] = $from_slot . '-' . $to_slot; $current = $current + $step; $expected_end_of_next_slot = $current + $step; $slot_ends = date('g:i A', $current); } $res['slots'] = $slots; $res['slot_ends'] = $slot_ends; return $res; } //Slot cration functions //Get all slots center wise and month/day/week, OPD wise. public static function getSlots($param = []) { //dd($param); $center_id = $param['center_id']; $month = $param['month']; $day = $param['day']; $week = $param['week']; $opd = $param['opd']; $db_prefix = config('constants.DB_PREFIX'); $result = []; //setting the condition based on which view Month,day,week if (!empty($month)) { $slots = DB::table('centre_slot_breakdown') ->select( 'centre_slot_breakdown.id', 'centre_slot_breakdown.location_id', 'centre_slot_breakdown.centre_id', 'centre_slot_breakdown.service_type_id', 'centre_slot_breakdown.date', 'centre_slot_breakdown.no_of_patients', 'centre_slot_breakdown.no_of_doctors', 'centre_slot_breakdown.flag', DB::raw("DAYOFWEEK(date)day_index"), DB::raw("DAYNAME(date)day_name"), 'slots.centre_slot_breakdown_id', 'slots.start_time', 'slots.end_time', 'slots.slot_type', 'slots.flag', 'slots.id as slot_id', DB::raw( '(select COUNT(*) from ' . $db_prefix . 'slots where ' . $db_prefix . 'slots.centre_slot_breakdown_id = ' . $db_prefix . 'centre_slot_breakdown.id AND ' . $db_prefix . 'slots.flag = 0) available_slot_count' ), DB::raw('(select COUNT(*) from ' . $db_prefix . 'slots where ' . $db_prefix . 'slots.centre_slot_breakdown_id = ' . $db_prefix . 'centre_slot_breakdown.id AND ' . $db_prefix . 'slots.flag = 1) occupied_slot_count') ) ->whereRaw('MONTH(date) = ' . $month . '') ->where(['centre_slot_breakdown.centre_id' => $center_id]) ->where(['centre_slot_breakdown.flag' => 0]) ->leftjoin('slots', 'slots.centre_slot_breakdown_id', '=', 'centre_slot_breakdown.id') ->get(); $start_date_of_the_slot = $slots[0]->date; // First day of the month. $start_date = date('Y-m-01', strtotime($start_date_of_the_slot)); // Last day of the month. $end_date = date('Y-m-t', strtotime($start_date_of_the_slot)); //Getting all days of a given month i.e 01,02----31 $only_days = Slot::date_range($start_date, $end_date, $step = '+1 day', $output_format = 'd'); //Getting all dates of a given month in 2020-00-01 format $get_dates = Slot::date_range($start_date, $end_date, $step = '+1 day', $output_format = 'Y-m-d'); //Getting center details $center_details = DB::table('centre_attributes') ->select('booking_starts_from', 'working_hrs', 'lunch_starts_from', 'lunch_ends_at', 'weekday') ->where(['centre_id' => $center_id, 'flag' => 0])->first(); //weekday is the days when center remains closed weekly $weekly_off = $center_details->weekday; //Formatting the weekly off indeces into the format supported by PHP. if (!empty($weekly_off)) { $weekly_off = explode(',', $weekly_off); foreach ($weekly_off as $key => $value) { $weekly_off[$key] = $value + 1; } } //Getting Holiday if any in the given duration $holidays = Slot::identifyHolidays($get_dates); //Results needed for the month_view of a slot $result['slots'] = $slots; $result['only_days'] = $only_days; //in 01,02,---31 $result['weekly_off'] = $weekly_off; $result['holidays'] = $holidays; return $result; //$off_and_holodays_of_center = check_which day is a holiday according to the holiday table } else { return $result; } } }