<?php
namespace App\Models;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Session;


class Slot extends Model {

	protected $table = 'centre_slot_breakdown';
	protected $appends = ['added_by', 'updated_by'];

	public static $rules = array(
		'center_id' => '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;
		}
	}

}