Function field and searcher question

Hi,

I created a custom field to get the availability of a room. The availability is determined if the room has folios in a specific date with a folio state in check_in, reserved or pending. The field works well but not the searcher.

Any help will be helpful. Thanks in advance.

    @classmethod
    def get_availability(cls, rooms, name):
        pool = Pool()

        Folio = pool.get('hotel.folio')

        result = {}
        ctx = Transaction().context

        arrival_date = ctx.get('arrival_date')
        departure_date = ctx.get('departure_date')

        for room in rooms:
            result[room.id] = 'available'

        if arrival_date and departure_date:
            for room in rooms:
                clause = [
                        ('arrival_date','<',departure_date),
                        ('departure_date','>',arrival_date),
                        ('room','=', room.id),
                ]

                folios = Folio.search(clause,
                    limit=1,
                    order=[('id','DESC')]
                )

                if folios:
                    for folio in folios:
                        if folio.registration_state == 'reserved':
                            result[room.id] = 'reserved'
                        elif folio.registration_state in ['check_in', 'pending'] :
                            result[room.id] = 'occupied'
        return result

    @classmethod
    def search_availability(cls, name, domain):
        pool = Pool()
        Folio = pool.get('hotel.folio')

        table = cls.__table__()
        folio = Folio.__table__()

        _, operator_, state = domain
        Operator = fields.SQL_OPERATORS[operator_]

        ctx = Transaction().context

        arrival_date = ctx.get('arrival_date')
        departure_date = ctx.get('departure_date')

        if arrival_date and departure_date:

            state_case = Case(
                ( ((folio.arrival_date < departure_date) &
                    (folio.departure_date > arrival_date) &
                    (folio.registration_state == 'pending')), 'reserved'),
                ( ((folio.arrival_date < departure_date) &
                    (folio.departure_date > arrival_date) &
                    (folio.registration_state == 'reserved')), 'reserved'),
                ( ((folio.arrival_date < departure_date) &
                    (folio.departure_date > arrival_date) &
                    (folio.registration_state == 'check_in')), 'occupied'),
                else_='available')

            state_query = (table.join(
                folio, 'LEFT', condition=table.id == folio.room,
                )
                .select(
                    table.id,
                    where=(
                        Operator(state_case, state)),
                    order_by=[folio.id.desc],
                    ),
                )

            return [('id', 'in', state_query)]
        return []

Hello,

First of all, I would recommand that you avoid using a search call in the for loop of your getter. If you are going to write a SQL query anyway for the searcher, you may as well reuse it there.

Second, what you are actually expecting is not clear enough to help you, especially since you did not explain what is the actual problem with the searcher.

My usual approach for complex searchers is to write the SQL directly, check it directly, then ā€œconvertā€ it to python-sql.

1 Like

First, thanks for the recommendation. I already take note of it.

In the searcher I want to filter the rooms that are available.

ROOM_AVAILABILITY = [
    ('occupied', 'Occupied'),
    ('reserved', 'Reserved'),
    ('available', 'Available'),
]

    availability = fields.Function(fields.Selection(ROOM_AVAILABILITY, 'Disponibilidad',),
        'get_availability', 
        searcher='search_availability'
    )

The room availability depends on a folio record, arrival_date, departure_date and registration_state. The actual problem with searcher is that return ā€˜availableā€™ when the room state is not ā€˜availableā€™. The state is well but the searcher not.

REGISTRATION_STATE = [
    ('', ''),
    ('reserved', 'Reserved'),
    ('pending', 'Pending'),
    ('check_in', 'Check In'),
    ('check_out', 'Check Out'),
    ('no_show', 'Not Showed'),
]
    arrival_date = fields.Date('Fecha de Llegada', required=True)
    departure_date = fields.Date('Fecha de Salida', required=True)
    registration_state = fields.Selection(REGISTRATION_STATE,
        'Estado de Registro', readonly=True, )

Also, I am going to write all in SQL.

Could you elaborate on what is wrong ?

1 Like

If I have more than one folio per day it returns ā€œavailableā€ in searcher fieldā€¦ If I only have one folio per day it determine ā€œavailableā€ in the room.

Sorry for the delay. I do not see how the query would actually lead to this result.

Could you give a detailed example of the records involved in your test case?

1 Like

Thanks for the answer,

I have the follow records for folio. In case only exist one record of the room in a folio, the searcher works well, but in case a room has more than one record the searcher gives me available.

The getter works well. Iā€™m still trying to write it with SQL.

id | arrival_date | departure_date | room | registration_state
----+--------------+----------------+------+--------------------
  1 | 2023-01-19   | 2023-01-21     |    1 | check_in
  8 | 2023-01-24   | 2023-01-27     |    1 | pending
 13 | 2023-01-26   | 2023-01-28     |    1 | reserved
 17 | 2023-01-27   | 2023-01-29     |    1 | reserved
 37 | 2023-02-01   | 2023-02-04     |    1 | check_in
 19 | 2023-02-01   | 2023-02-03     |    1 | check_out
 21 | 2023-02-01   | 2023-02-04     |    1 | check_out
 39 | 2023-02-02   | 2023-02-04     |    1 | reserved
 40 | 2023-02-08   | 2023-02-10     |    1 | reserved
 10 | 2023-01-26   | 2023-01-27     |    2 | pending
 16 | 2023-01-27   | 2023-01-29     |    2 | check_out
 22 | 2023-02-01   | 2023-02-04     |    2 | check_out
 20 | 2023-02-01   | 2023-02-03     |    2 | reserved
  2 | 2023-01-19   | 2023-01-21     |    3 | check_in
 11 | 2023-01-26   | 2023-01-27     |    3 | reserved
 15 | 2023-01-27   | 2023-01-28     |    3 | check_out
 23 | 2023-02-02   | 2023-02-03     |    3 | pending
  4 | 2023-01-19   | 2023-01-21     |    4 | check_in
 18 | 2023-01-27   | 2023-01-30     |    4 | reserved
 24 | 2023-02-01   | 2023-02-02     |    4 | pending
 12 | 2023-01-26   | 2023-01-28     |    5 | reserved
 25 | 2023-02-03   | 2023-02-05     |    5 | pending
  6 | 2023-01-19   | 2023-01-25     |    6 | check_in
 26 | 2023-02-04   | 2023-02-05     |    6 | pending
 27 | 2023-02-03   | 2023-02-04     |    7 | pending
 28 | 2023-02-01   | 2023-02-02     |    8 | pending
 29 | 2023-02-05   | 2023-02-06     |    8 | pending
 14 | 2023-01-26   | 2023-01-27     |   10 | check_out
 30 | 2023-02-06   | 2023-02-07     |   10 | pending
 31 | 2023-02-02   | 2023-02-04     |   11 | pending
 32 | 2023-02-01   | 2023-02-02     |   12 | pending
 33 | 2023-02-01   | 2023-02-03     |   13 | reserved
  7 | 2023-01-19   | 2023-01-20     |   14 | check_in
 34 | 2023-02-01   | 2023-02-04     |   14 | reserved
 35 | 2023-02-02   | 2023-02-03     |   15 | reserved
 36 | 2023-02-03   | 2023-02-04     |   16 | reserved
 38 | 2023-02-02   | 2023-02-03     |   18 | pending
  3 | 2023-01-19   | 2023-01-21     |   20 | check_out
  5 | 2023-01-19   | 2023-01-23     |   45 | check_in

I tried this query and it works the other way around, only show me ā€œavailableā€ the rooms that has a folio.

SELECT f.id, f.arrival_date, f.departure_date, f.room, f.registration_state FROM hotel_folio AS f INNER JOIN hotel_room AS r ON f.room = r.id WHERE f.registration_state = 'pending' AND f.arrival_date >= '2023-02-11' AND departure_date <= '2023-02-14' ORDER BY f.room, f.arrival_date ;

I still havenā€™t figured it out but Iā€™m trying. All recommendations are welcome.

You should probably include the conditions on the folioā€™s date in the condition parameter of the call to join, rather than in the Case.

The available you are getting probably comes from the else_, for folios that are outside the provided date

1 Like

Thanks for the suggestion, it works correctly with first cases. Let me try with more records.

At the moment this code works well:

    @classmethod
    def get_availability(cls, rooms, name):
        pool = Pool()

        Folio = pool.get('hotel.folio')

        result = {}
        
        ctx = Transaction().context
        arrival_date = ctx.get('arrival_date')
        departure_date = ctx.get('departure_date')
        
        cursor = Transaction().connection.cursor()
        result = dict((r.id, 'available') for r in rooms)

        if arrival_date and departure_date:

            room = cls.__table__()
            folio = Folio.__table__()
            state = cls.availability._field.sql_type().base

            for sub_ids in grouped_slice(rooms):
                red_sql = reduce_ids(room.id, sub_ids)
                cursor.execute(*room.join(folio,
                    condition=folio.room == room.id
                    ).select(room.id,
                        Case(
                            ((folio.registration_state == 'pending'), 'reserved'),
                            ((folio.registration_state == 'reserved'), 'reserved'),
                            ((folio.registration_state == 'check_in'), 'occupied'),
                            else_='available').cast(state),
                        where=( (folio.arrival_date < departure_date) 
                            & (folio.departure_date > arrival_date) 
                            ) & red_sql
                    )
                )

            for invoice_id, state_ in cursor:
                result[invoice_id] = state_

        return result

    @classmethod
    def search_availability(cls, name, domain):
        pool = Pool()
        Folio = pool.get('hotel.folio')

        table = cls.__table__()
        folio = Folio.__table__()

        _, operator_, state = domain
        Operator = fields.SQL_OPERATORS[operator_]

        ctx = Transaction().context

        arrival_date = ctx.get('arrival_date')
        departure_date = ctx.get('departure_date')

        if arrival_date and departure_date:

            state_case = Case(
                ((folio.registration_state == 'pending'), 'reserved'),
                ((folio.registration_state == 'reserved'), 'reserved'),
                ( (folio.registration_state == 'check_in'), 'occupied'),
            else_='available')

            state_query = (table.join(
                folio, 'LEFT', condition=((table.id == folio.room) & ((folio.arrival_date < departure_date) 
                    &(folio.departure_date > arrival_date) )),
                )
                .select(
                    table.id,
                    where=(Operator(state_case, state)),
                    order_by=[folio.id.desc],
                    #limit=1,
                    ),
                )

            return [('id', 'in', state_query)]
        return []