# -*- coding: utf-8 -*-
class PeriodZp < ActiveRecord::Base
  belongs_to :employee
  belongs_to :period
  has_many :payments
  has_many :payrolls
  validates_presence_of :employee_id, :message =>"Сотрудник!"
  validates_presence_of :period_id, :message =>"Сотрудник!"

  def self.check_and_save_sum
    bads = []
    pzs=PeriodZp.find(:all, :conditions =>"period_id >= 2338")
    for pz in pzs do
      if pz.payrolls.sum(:sum).to_i != pz.payroll_sum.to_i
        bads << pz.period.month
        pz.update_attributes(:payroll_sum =>  pz.payrolls.sum(:sum))
      end
      if pz.payments.sum(:sum).to_i != pz.payment_sum.to_i 
        bads << pz.period.month
        pz.update_attributes(:payment_sum =>  pz.payments.sum(:sum))
      end
    end
    return bads.join(", ")
  end



  def self.for_period_and_employee(period_id, employee_id)
    @period_zp = PeriodZp.find(:first, :conditions => "period_id = #{period_id} AND employee_id = #{employee_id}") 
    unless @period_zp
      @period_zp = PeriodZp.create(:employee_id => employee_id, :period_id => period_id)
    end
    return @period_zp
  
  end


  def self.for_date_and_employee(date, employee_id)
    period = Period.for_date(date)
    if period
      @period_zp = PeriodZp.find(:first, :conditions => "period_id = #{period.id} AND employee_id = #{employee_id}") 
      unless @period_zp
        @period_zp = PeriodZp.create(:employee_id => employee_id, :period_id => period.id)
      end
      return @period_zp
    else 
      return nil
    end
  end

def self.dept_on_date(date, employee_id)
  #  @period = Period.for_date(date.to_date)
  PeriodZp.find_by_sql("
    SELECT     
           SUM(IFNULL(payroll_sum, 0) - IFNULL(payment_sum, 0)) +  
           IFNULL((SELECT 
                   SUM(IFNULL(p.sum, 0)) sum_pl 
                       FROM payrolls p
                         LEFT JOIN period_zps pz ON p.period_zp_id = pz.id
                             LEFT JOIN periods pr ON pz.period_id = pr.id
                                 WHERE LAST_DAY(pr.month) = DATE(#{date.to_date}) AND pz.employee_id = #{employee_id}), 0) -
           IFNULL((SELECT 
                   SUM(IFNULL(p.sum, 0)) sum_mn 
                    FROM payments p
                      WHERE DATE(DATE_ADD(operation_date, INTERVAL #{Time.now.utc_offset} SECOND)) >= DATE('#{date.to_date.beginning_of_month}') AND DATE(DATE_ADD(operation_date, INTERVAL #{Time.now.utc_offset} SECOND)) <= DATE('#{date.to_date}') AND p.employee_id = #{employee_id}), 0) sum
                         FROM period_zps 
                         LEFT JOIN periods per ON per.id = period_zps.period_id
    WHERE per.month < DATE('#{date.beginning_of_month}') AND employee_id = #{employee_id}        
")[0].sum
  
  end


end