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