1

I want users to be able to check the calendar for dates already booked so the person won't be able to book a room if it has been booked already. I used the lte and gte to check for dates but it's very inconsistent.

See what my DB looks like below.

enter image description here

The main problem I'm facing,

From June 2 to June 13 have been booked for hotelroom id 1. It's returning available instead of not available.

>>> start_date='2016-06-02'
>>> end_date='2016-06-13'
>>>    check_for_bookings=HotelCalendar.objects.filter(Q(checkin_booked_date__gte=start_date) | Q(checkout_booked_date__lte=end_date), hotelrooms_id=1)
>>> if check_for_bookings:
...     print 'not available'
... else:
...     print 'available'
...
available
>>>

I selected from June 3 to June 14 and tested it with the below query and it worked. It showed that the room is not available.

>>> start_date='2016-06-03'
>>> end_date='2016-06-14'
>>> check_for_bookings=HotelCalendar.objects.filter(Q(checkin_booked_date__gte=start_date)|Q(checkout_booked_date__lte=end_date), hotelrooms_id=1)
>>> if check_for_bookings:
...     print 'not available'
... else:
...     print 'available'
...
not available

The question is why did the first query failed to return 'not available' when the dates have been booked.?

What other query can I run to make it efficient?

smack
  • 922
  • 11
  • 21

1 Answers1

2

Your conditions have been swapped gte <> lte . The second query worked because there is a matching date '2016-06-14' for hotelrooms_id=1.

But you want to check if start_date and end_date are within range checkin_booked_date to checkout_booked_date:

check_for_bookings = HotelCalendar.objects.filter(checkin_booked_date__lte=start_date, 
                                                  checkout_booked_date__gte=end_date,
                                                  hotelrooms_id=1)

Use exists if you only need to check and not fetch the objects:

if HotelCalendar.objects.filter(checkin_booked_date__lte=start_date,
                                checkout_booked_date__gte=end_date, 
                                hotelrooms_id=1).exists():

Update:

From this SO answer, we can tell if start and end dates overlap with the dates of occupancy of a client:

from datetime import datetime as dt

hotelcalendar = HotelCalendar.objects.filter(hotelrooms_id=1)

start_date = dt.strptime(start_date, '%Y-%m-%d')
end_date = dt.strptime(end_date, '%Y-%m-%d')

if hotelcalendar.checkin_booked_date <= end_date and hotelcalendar.checkout_booked_date >= start_date:
     print "not available"
else:
     print "available"

Update:

I tweaked it this way: I changed 'filter' to 'get' because it will return 'AttributeError'. And I used datetime.date() directly. And it worked fine so far!

>>> import datetime
>>> hotelcalendar= HotelCalendar.objects.get(hotelrooms_id=1)
>>> start_date= datetime.date(2016, 06, 14)
>>> end_date= datetime.date(2016, 06, 19)
>>> if hotelcalendar.checkin_booked_date <= end_date and hotelcalendar.checkout_booked_date >= start_date:
...     print 'not available'
... else:
...     print 'available'
...
not available

>>> hotelcalendar= HotelCalendar.objects.get(hotelrooms_id=1)
>>> start_date= datetime.date(2016, 06, 15)
>>> end_date= datetime.date(2016, 06, 19)
>>> if hotelcalendar.checkin_booked_date <= end_date and  hotelcalendar.checkout_booked_date >= start_date:
...     print 'not available'
... else:
...     print 'available'
...
available
>>> hotelcalendar= HotelCalendar.objects.get(hotelrooms_id=3)
>>> start_date= datetime.date(2016, 06, 02)
>>> end_date= datetime.date(2016, 06, 10)
>>> if hotelcalendar.checkin_booked_date <= end_date and hotelcalendar.checkout_booked_date >= start_date:
...     print 'not available'
... else:
...     print 'available'
...
not available
>>>
Community
  • 1
  • 1
Moses Koledoye
  • 77,341
  • 8
  • 133
  • 139
  • Thanks for the reply bro.. It worked but when I set the start_date='2016-06-14' and end_date='2016-06-19', I got 'available'. I was thinking it should be 'not available' because ' start_date='2016-06-14' has been saved already in the calendar as checkout_date for hotelroom 1. What do you think? – smack Jun 17 '16 at 17:28
  • Even for hotelroom_id 3, when I selected start_date= '2016-06-02', end_date='2016-06-10' I was able to book and that shouldn't be. What do you think? – smack Jun 17 '16 at 17:53
  • Oops, I figured this would break with overlaps. I've added an update – Moses Koledoye Jun 17 '16 at 18:34
  • You mean hotelcalendar.checkin_booked_date? Also I've tested it with the Q option and I even used | and & operators yet they failed.. | will work when checking already booked room and will fail when the room is free. The & operator is the same as your first answer. – smack Jun 17 '16 at 19:25
  • Do the comparison in python. I found a post that can help detect overlaps. See updates – Moses Koledoye Jun 17 '16 at 19:42
  • Bro thanks! I've updated your answer and marked it. Now the question is, since the user will input the date directly in the field like '2016-06-04' do I need to convert the date to (2016, 06, 04) and use datetime.date(user_input_date) before I can compare or Django wil take care of it itself? – smack Jun 18 '16 at 08:07
  • Glad it worked. You'll need to convert it. But you can use `datetime.datetime.strptime('2016-06-04', '%Y-%m-%d')` to convert from the string format into a `datetime` object. – Moses Koledoye Jun 18 '16 at 08:13
  • Don't you think the conversion will be long.. like after converting to datetime object, I will have to change the format to (2016, 06, 04)? – smack Jun 18 '16 at 09:03
  • You can use the `datetime` object directly without further need for changes – Moses Koledoye Jun 18 '16 at 09:10
  • You know what bro.. I just tried it directly without converting and it worked! Thanks for the support! You rock. – smack Jun 18 '16 at 10:09