# -*- coding: utf-8 -*-
### Шаблоны смен. Бывают двух типов: рабочие смены (WorkShift) и смены менеджеров (ManagerShift)
class RegularShift < ActiveRecord::Base
  belongs_to :employee
  has_many :shifts
  
  attr_accessor_with_default :checked, true


  named_scope :with_employee_and_department, lambda { |empl, dep| empl.present? ? { :conditions => "regular_shifts.employee_id = '#{empl}' AND regular_shifts.department = '#{dep}'" }: { } }
  


  def self.for_department(dep)
    RegularShift.find_by_sql("SELECT
                              IFNULL(GROUP_CONCAT(IF((s.day_of_week = 1), CONCAT(TIME_FORMAT(s.time_from, '%k:%i'), ' - ', TIME_FORMAT(s.time_to, '%k:%i')), null) SEPARATOR ''), 'вых') monday,
                              IFNULL(GROUP_CONCAT(IF((s.day_of_week = 2), CONCAT(TIME_FORMAT(s.time_from, '%k:%i'), ' - ', TIME_FORMAT(s.time_to, '%k:%i')), null) SEPARATOR ''), 'вых') tuesday,
                              IFNULL(GROUP_CONCAT(IF((s.day_of_week = 3), CONCAT(TIME_FORMAT(s.time_from, '%k:%i'), ' - ', TIME_FORMAT(s.time_to, '%k:%i')), null) SEPARATOR ''), 'вых') wednesday,
                              IFNULL(GROUP_CONCAT(IF((s.day_of_week = 4), CONCAT(TIME_FORMAT(s.time_from, '%k:%i'), ' - ', TIME_FORMAT(s.time_to, '%k:%i')), null) SEPARATOR ''), 'вых') thursday,
                              IFNULL(GROUP_CONCAT(IF((s.day_of_week = 5), CONCAT(TIME_FORMAT(s.time_from, '%k:%i'), ' - ', TIME_FORMAT(s.time_to, '%k:%i')), null) SEPARATOR ''), 'вых') friday,
                              IFNULL(GROUP_CONCAT(IF((s.day_of_week = 6), CONCAT(TIME_FORMAT(s.time_from, '%k:%i'), ' - ', TIME_FORMAT(s.time_to, '%k:%i')), null) SEPARATOR ''), 'вых') saturday,
                              IFNULL(GROUP_CONCAT(IF((s.day_of_week = 7), CONCAT(TIME_FORMAT(s.time_from, '%k:%i'), ' - ', TIME_FORMAT(s.time_to, '%k:%i')), null) SEPARATOR ''), 'вых') sunday,
                               e.login nic, e.id employee_id
                               FROM regular_shifts s LEFT JOIN pricelist.pl_people e ON e.id = s.employee_id WHERE s.department = '#{dep}' GROUP BY e.login ORDER BY e.login ")
  end


  private
  

end