Booked Scheduler Community Support
October 22, 2017, 06:59:19 PM *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
News: Booked is proud to recommend Shift Capsule in the employee shift scheduling space www.ShiftCapsule.com
 
   Home   Help Login Register  
Pages: [1]
  Print  
Author Topic: Search Reservation Date not match the result  (Read 107 times)
giggslam
Newbie
*

Karma: 0
Posts: 3


« on: August 12, 2017, 08:52:03 AM »

Hi all,

I found the "Date Range" of "Search Reservation" is not match the result.
For example, the Date Range is "8/30/2017" to "8/31/2017", but the result cannot show the "8/31/2017" reservation.
After checked the DEBUG sql, I found it using "start_date <= '2017-08-29 16:00:00' AND ( end_date >= '2017-08-30 16:00:00' " for the parameter.
I am using the "Asia/Hong_Kong" (+8) for the default timezone. And, no any problem for another parts.

Any idea how to fix it?
Thanks a lot.



upload photo on web

upload photo on web

Code:
2017-08-12T11:55:12+08:00 [14036] DEBUG sql - MySql Query: SELECT
ri.*, rs.date_created as date_created, rs.last_modified as last_modified, rs.description as description, rs.status_id as status_id, rs.title, rs.repeat_type, rs.repeat_options,
owner.fname as owner_fname, owner.lname as owner_lname, owner.user_id as owner_id, owner.phone as owner_phone, owner.position as owner_position, owner.organization as owner_organization, owner.email as email, owner.language, owner.timezone,
resources.name, resources.resource_id, resources.schedule_id, resources.status_id as resource_status_id, resources.resource_status_reason_id, resources.buffer_time, resources.color, resources.enable_check_in, resources.auto_release_minutes, ru.reservation_user_level,
start_reminder.minutes_prior AS start_reminder_minutes, end_reminder.minutes_prior AS end_reminder_minutes,
(SELECT GROUP_CONCAT(groups.group_id)
FROM user_groups groups WHERE owner.user_id = groups.user_id) as owner_group_list,

(SELECT GROUP_CONCAT(CONCAT(participants.user_id, '=', CONCAT(participant_users.fname, " ", participant_users.lname)) SEPARATOR "!sep!")
FROM reservation_users participants INNER JOIN users participant_users ON participants.user_id = participant_users.user_id WHERE participants.reservation_instance_id = ri.reservation_instance_id AND participants.reservation_user_level = 2) as participant_list,

(SELECT GROUP_CONCAT(CONCAT(invitees.user_id, '=', CONCAT(invitee_users.fname, " ", invitee_users.lname)) SEPARATOR "!sep!")
FROM reservation_users invitees INNER JOIN users invitee_users ON invitees.user_id = invitee_users.user_id WHERE invitees.reservation_instance_id = ri.reservation_instance_id AND invitees.reservation_user_level = 3) as invitee_list,

(SELECT GROUP_CONCAT(CONCAT(cav.custom_attribute_id,'=', cav.attribute_value) SEPARATOR "!sep!")
FROM custom_attribute_values cav WHERE cav.entity_id = ri.series_id AND cav.attribute_category = 1) as attribute_list,

(SELECT GROUP_CONCAT(CONCAT(p.name, "=", p.value) SEPARATOR "!sep!")
FROM user_preferences p WHERE owner.user_id = p.user_id) as preferences,

(SELECT GROUP_CONCAT(CONCAT(guests.email, "=", guests.reservation_user_level) SEPARATOR "!sep!")
FROM reservation_guests guests WHERE guests.reservation_instance_id = ri.reservation_instance_id) as guest_list
FROM reservation_instances ri
INNER JOIN reservation_series rs ON rs.series_id = ri.series_id
INNER JOIN reservation_users ru ON ru.reservation_instance_id = ri.reservation_instance_id
INNER JOIN users ON users.user_id = rs.owner_id
INNER JOIN users owner ON owner.user_id = rs.owner_id
INNER JOIN reservation_resources rr ON rs.series_id = rr.series_id
INNER JOIN resources ON rr.resource_id = resources.resource_id
INNER JOIN schedules ON resources.schedule_id = schedules.schedule_id
LEFT JOIN reservation_reminders start_reminder ON start_reminder.series_id = rs.series_id AND start_reminder.reminder_type = 0
LEFT JOIN reservation_reminders end_reminder ON end_reminder.series_id = rs.series_id AND end_reminder.reminder_type = 1

WHERE ( rs.status_id <> 2
AND ru.reservation_user_level = '1' ) AND (1=1 AND ( users.user_id = '21' ) AND ( 0=1 OR ( start_date <= '2017-08-29 16:00:00' AND ( end_date >= '2017-08-30 16:00:00' ) ) OR ( start_date >= '2017-08-29 16:00:00' AND ( start_date <= '2017-08-30 16:00:00' ) ) OR ( end_date >= '2017-08-29 16:00:00' AND ( end_date <= '2017-08-30 16:00:00' ) ) )) GROUP BY ri.reservation_instance_id, rr.resource_id, ri.series_id
ORDER BY ri.start_date ASC LIMIT 100 OFFSET 0;
Logged
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.20 | SMF © 2006-2007, Simple Machines Valid XHTML 1.0! Valid CSS!