lacagette_shifts.py 1.21 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
# -*- coding: utf-8 -*-

from openerp import _, api, models, fields

class LaCagetteShifts(models.Model):

    _name = "lacagette_shifts"

    @api.model
    def get_active_shifts(self):
        """ Get shifts, counting active registrations to each of them"""

        # Get registrations which:
        #   - belong to an active shift
        #   - are in an accepted state
        #   - begin before now
        #   - don't have an end date OR end after now
        # Group by shift and count
        sql = """SELECT str.shift_template_id, COUNT(str.shift_template_id) AS seats_active_registration
                     FROM shift_template st
                     LEFT JOIN shift_template_registration str ON str.shift_template_id = st.id
                     LEFT JOIN shift_template_registration_line strl ON strl.registration_id = str.id
                     WHERE active IS TRUE
                     AND str.state IN ('draft', 'open', 'done', 'replacing')
                     AND strl.date_begin < NOW()
                     AND (strl.date_end IS NULL OR strl.date_end >= NOW())
                     GROUP BY str.shift_template_id"""
        self.env.cr.execute(sql)
        res = self.env.cr.dictfetchall()
        return (res)