First of all I am new with CodeIgniter and still in the process of learning.
I have created an app on React Native that selects date and time to create an appointment. I am using CodeIgniter as my back-end. The appointment has a date
, start time
, and end time
.
The conditions for creating appointment are:
If an appointment is already created, such as:
date = 15-01-2020
,start time = 7:00pm
,end time = 8:00pm
.Then user CANNOT create another appointment such as:
date = 15-01-2020
,start time = 7:00pm
,end time = 8:00pm
(similar date and time since 7pm to 8pm already booked)
date = 15-01-2020
,start time = 7:20pm
,end time = 7:40pm
(overlapping inside already booked appointment)
date = 15-01-2020
,start time = 7:45pm
,end time = 8:45pm
(start time overlapping with already booked appointment)
date = 15-01-2020
,start time = 6:45pm
,end time = 7:45pm
(end time overlapping with already booked appointment)
date = 15-01-2020
,start time = 6:00pm
,end time = 9:00pm
(overlapping with already booked appointment)Also, user should not be able to create appointment with time such as:
start time = 8:00pm
,end time = 7:00pm
(wherestart time > end time
)
From the above conditions, I have been able to achieve only condition no.1. However, there’s another problem I faced, where If start time = 7:00pm
to end time = 8:00pm
is booked, I CANNOT create from start time = 8:00pm
to end time = 9:00pm
. Code snippet provided below:
This is the function I have created (with the help of a friend) on the back-end MODEL:
public function check_datetime_range($where) { $this->db->where('freelancer_id', $where['freelancer_id']); $this->db->where("DATE_FORMAT(app_date_start, '%Y-%m-%d') = ", date('Y-m-d', strtotime($where['app_date_start']))); $this->db->group_start(); $this->db->group_start(); $this->db->where('app_date_start >=', $where['app_date_start']); $this->db->where('app_date_end <=', $where['app_date_end']); $this->db->group_end(); $this->db->or_group_start(); $this->db->where('app_date_start >=', date('Y-m-d H:i:s', strtotime($where['app_date_start'])-3600)); $this->db->group_start(); $this->db->where('app_date_end <=', $where['app_date_start']); $this->db->or_where('app_date_end <=', $where['app_date_end']); $this->db->group_end(); $this->db->group_end(); $this->db->group_end(); return $this->db->get($this->_table_name)->num_rows(); } //the code below are from the CONTROLLER. //Information for: app_date_start and app_date_end (it contains both date and time). /*$start = new DateTime($this->post('app_date') . ' ' . $this->post('start_time')); $end = new DateTime($this->post('app_date') . ' ' . $this->post('end_time')); $save['app_date_start'] = $start->format('Y-m-d H:i:s'); $save['app_date_end'] = $end->format('Y-m-d H:i:s');*/
Any help would be appreciated since these are all a bit overwhelming at the moment.
Advertisement
Answer
Solved:
public function check_datetime_range($where) { $this->db->where('freelancer_id', $where['freelancer_id']); $this->db->where("DATE_FORMAT(app_date_start, '%Y-%m-%d') = ", date('Y-m-d', strtotime($where['app_date_start']))); $this->db->group_start(); // Example: // start time = 7:00pm, end time = 8:00pm, date = 15-01-2020 (Existing Appointment) //1. Prevents overlapping time with an existing appointment // start time = 7:00pm, end time = 8:00pm, date = 15-01-2020 // OR // start time = 6:00pm, end time = 9:00pm, date = 15-01-2020 $this->db->group_start(); $this->db->where('app_date_start >=', $where['app_date_start']); $this->db->where('app_date_end <=', $where['app_date_end']); $this->db->group_end(); //2. Prevents overlapping time from within an existing appointment // start time = 7:15pm, end time = 7:45pm, date = 15-01-2020 $this->db->or_group_start(); $this->db->where('app_date_start <=', $where['app_date_start']); $this->db->where('app_date_end >=', $where['app_date_end']); $this->db->group_end(); //3. Prevents overlapping end time inside an existing appointment // start time = 6:45pm, end time = 7:45pm, date = 15-01-2020 $this->db->or_group_start(); $this->db->where('app_date_start >', $where['app_date_start']); $this->db->where('app_date_start <', $where['app_date_end']); $this->db->where('app_date_end >', $where['app_date_end']); $this->db->group_end(); //4. Prevents overlapping start time inside an existing appointment // start time = 7:45pm, end time = 8:45pm, date = 15-01-2020 $this->db->or_group_start(); $this->db->where('app_date_start <', $where['app_date_start']); $this->db->where('app_date_start <', $where['app_date_end']); $this->db->where('app_date_end >', $where['app_date_start']); $this->db->where('app_date_end <', $where['app_date_end']); $this->db->group_end(); //5. Prevents start time > end time // start time = 7:00pm, end time = 6:00pm $this->db->or_group_start(); $this->db->where('app_date_start >', $where['app_date_end']); $this->db->group_end(); $this->db->group_end(); return $this->db->get($this->_table_name)->num_rows(); }