# -*- 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)