# -*- coding: utf-8 -*-
class AlpsController < ApplicationController
    $department = 'alp'
    $department_id = '158'

  before_filter :isAuthorization
  before_filter :set_var
  before_filter :get_reports, :only => [:index, :index_excel]
  before_filter :get_stats, :only => [:stats, :stats_excel]



  def index
    @employees = Division.alp.employees.working
    if (params[:filter_month] && params[:filter_month] != '' && params[:filter_year])
      month = params[:filter_month].to_s
      year = params[:filter_year].to_s
    else
      month = Date.today.month.to_s
      year = Date.today.year.to_s
    end
    if params[:filter_day] && params[:filter_day] != ""
      @date = (month +'/'+params[:filter_day]+'/' + year).to_date
      date_from = @date
      date_to = @date
    else
      @date = (month +'/01/' + year).to_date
      date_from = @date
      date_to = @date.end_of_month
    end

    if @boss
      @filter_empl =  params[:filter_empl]
      @wage = 0
    else
      @filter_empl = @current_user.id
      @wage = 0 #@current_user.done_employees.alp.in_month(@date).sum(:sum)
    end

   
    cond = []
    cond << "r.department = 'alp' AND r.closed_at IS NULL"
    if @filter_empl != '' && @filter_empl != nil
      cond << "r.id IN (SELECT de.report_id FROM done_employees de WHERE de.empl_id = #{@filter_empl})"
    end

    if params[:works] && params[:works].size > 0
      cond << "dw.work_id IN (#{params[:works].join(', ')})"
    end
    cond << "status = #{params[:filter_status]}" if params[:filter_status] && params[:filter_status] != '5'
    cond << "r.category = #{params[:filter_worktype]}" if params[:filter_worktype] && params[:filter_worktype] != ''
    if params[:topology_map] && !params[:topology_map].blank?
      cond << "r.topology_map = #{params[:topology_map].to_bool}"
    end
    cond = cond.join(" AND ")
    ActiveRecord::Base.connection.execute("SET group_concat_max_len = 2048;")
      @reports = 
      Report.find_by_sql(
                           "SELECT GROUP_CONCAT(DISTINCT CONCAT('<li>', done_employees.empl_name, '  (', done_employees.sum, '<font class=currency> руб.</font>)</li>') SEPARATOR ' ') as empls,
                                   GROUP_CONCAT(DISTINCT CONCAT('<li>', dm.material_name, ' - ', dm.count, '</li>') SEPARATOR ' ') as mats, 
                                   GROUP_CONCAT(DISTINCT CONCAT('<li>', IFNULL(w.name, p.name), ' - ', dw.count, '</li>') SEPARATOR ' ') as wrks,
                                   IF((r.get_money_sum = 0 OR r.get_money_sum = NULL), '', CONCAT('<li>', 'НЕСТАНДАРТНАЯ РАБОТА: ', r.com_conflicts, ' - ', r.get_money_sum, '</li>') ) as not_standart_wrks, 
                                                        IF((r.request_id IS NULL), '', IF((r.request_closed IS TRUE), 'Закрыт', 'Открыт')) as req_status, 
                            r.id as id, r.request_id, r.name, r.date, r.sum, r.status, ct.name as cat_n, r.weather_temp, r.weather_rain, r.com_imperfection, r.com_stay_work as comment, ed.login as ed_name, r.topology_map topology_map, r.request_bonus_sum, r.login_from
                               FROM reports r 
                                    LEFT JOIN done_employees on done_employees.report_id = r.id 
                                    LEFT JOIN pricelist.pl_people ed on r.update_user_id = ed.id 
                                    LEFT JOIN done_materials dm ON dm.report_id = r.id 
                                    LEFT JOIN category ct ON ct.id = r.category 
                                    LEFT JOIN done_works dw ON dw.report_id = r.id
                                    LEFT JOIN works AS w ON dw.work_id=w.id && dw.pricelist=0
                                    LEFT JOIN pricelist.pl_items AS p ON dw.work_id=p.id && dw.pricelist=1
                                         WHERE DATE(r.date) BETWEEN DATE('#{date_from}') AND DATE('#{date_to}') AND #{cond}
                                               GROUP BY r.id ORDER BY r.request_id, r.date,r.name")
      

  end

  def new
    if params[:t_id]
      @report = Report.new(:date => Time.at(params[:t_date].to_i), :status => 2, :department => "alp", :category => params[:t_ct].to_i, :com_stay_work => params[:com_stay_work].to_s, :request_id => params[:t_id], :name => params[:t_login].to_s, :login_from => params[:t_login_start].to_s)
      empl = Employee.find_by_login params[:t_empl]
#      @report.done_employees.build(:employee => empl)
    else
      @current_user = Employee.find(session[:user_id])
      @report = Report.new(:date => Date.today, :status => 2, :department => "alp", :categor => Category.alp.active.first)
#      @report.done_employees.build(:employee => @current_user)
      @report.rep_logins.build
    end
    @pricelist = Division.alp.pricelist
    @top_works = (@report.done_works.map{|i| i.work}).uniq
    @pricelist = @top_works + (@pricelist - @top_works)
    @main_empl = Employee.find 2299
  end

  def create
    @report = Report.new(params[:report])
    if @report.done_employees.size == 0
      flash.now[:error] =  "Выберите сотрудника!"
    else
      unless @report.save
        flash.now[:error] =  (@report.errors.each_full { |msg| puts msg }).to_s
      end
    end
    respond_to do |format|
      unless flash.size > 0
        flash[:notice] = 'Запись обновлена'
        format.html { redirect_to alps_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
        format.xml  { head :ok }
      else
        @current_user = Employee.find(session[:user_id]) 
        @pricelist = Division.alp.pricelist
    @top_works = (@report.done_works.map{|i| i.work}).uniq
    @pricelist = @top_works + (@pricelist - @top_works)
    @main_empl = Employee.find 2299
        format.html { render :action => "new", :report => @report }
      end
    end
  end


 def edit
    @report = Report.find(params[:id])
    @pricelist = Division.alp.pricelist
    @top_works = (@report.done_works.map{|i| i.work}).uniq
    @pricelist = @top_works + (@pricelist - @top_works)
    @main_empl = Employee.find 2299
  end

  def update
    @report = Report.find(params[:id])
    respond_to do |format|
      if @report.update_attributes(params[:report])
        flash[:notice] = 'Запись обновлена'
        format.html { redirect_to alps_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
        format.xml  { head :ok }
      else
        @main_empl = Employee.find 2299
    @pricelist = Division.alp.pricelist
    @top_works = (@report.done_works.map{|i| i.work}).uniq
    @pricelist = @top_works + (@pricelist - @top_works)
        
        flash.now[:error] =  (@report.errors.each_full { |msg| puts msg }).to_s
        format.html { render :action => "edit" }
        format.xml  { render :xml => @report.errors, :status => :unprocessable_entity }
      end
    end
  end

  def destroy
    @report =   Report.find(params[:id])
    if @report.destroy
      flash[:notice] = 'Отчет № ' + params[:id] + ' был удален!'
    else
      messages = ''
      flash[:error] = @report.errors.each_full {|msg| messages += msg + "<br/>"}
    end
    respond_to do |format|
      format.html { redirect_to alps_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
    end
  end

  def change_price
    if params[:id].to_i != 0
      @report = Report.find params[:id]
#      @report.done_works.destroy_all
    else
      @report = Report.new
      @report.department = 'alp'
    end
    @report.category =  params[:type]
    
    @pricelist = Division.alp.pricelist
    @top_works = (@report.done_works.map{|i| i.work}).uniq
    @pricelist = @top_works + (@pricelist - @top_works)
    render :update do |page|
      page.replace_html  'pricelist_table', :partial => 'pricelist', :collection => @pricelist, :locals =>{:done_works => @report.done_works}
      page.call "ChangeMaterials"
    end
  end

  def update_sum
    if params[:id]
      @report = Report.find params[:id]
    else
      @report = Report.new(:department => 'alp')
    end
    @report.attributes = params[:report]

    @report.calculate
     render :update do |page|
      page.replace_html  'sums', :partial => 'sum', :locals =>{:report => @report}
    end
  end


   def stats

   end

  def stats_excel
    i = 0
    require 'spreadsheet'
    Spreadsheet.client_encoding = 'UTF-8'
    book = Spreadsheet::Workbook.new
    dataBook1 = book.create_worksheet :name => 'З/П сотрудниов'
      row = dataBook1.row(i)
      row[0] = "Дата"
      row[1] = "Тип работ"
      row[2] = "Сотрудники"
      row[3] = "Начальная точка"
      row[4] = "Конечная точка"
      row[5] = "Марка кабеля"
      row[6] = "Общий метраж"
      row[7] = "Метраж по чердакам"
      row[8] = "Сумма за работы"

    for login in @logins do
        i += 1
        row = dataBook1.row(i)
        row[0] = login.report.date.to_date
        row[1] = login.report.category_name
        row[2] = login.report.done_employees.map{|b| b.empl_name}.join(", ")
        row[3] = login.from_name
        row[4] = login.to_name
        row[5] = login.material_name
        row[6] = login.cable_length
        row[7] = login.attic_length
        row[8] = login.sum
    end
   # Write book
    @file = 'excel-' + rand().to_s + '.xls'
    book.write 'public/'+@file
    render :template => 'alps/index_excel.html.erb', :layout => false
  end


  def update_value
    report = Report.find(params[:id])
    if report
      params.delete("id"); params.delete("controller"); params.delete("action")
      report.update_attributes(params)
    end
    render :update do |page|
      page.call "ChangeRow", report.id, report.sum
    end
  end

  def stats_for_reports
    # Стоимость отчета = Общая ЗП + кусок за смены (кол-во смен в день / стоимость смены за день)
   if (params[:filter_month] && params[:filter_month] != '' && params[:filter_year])
      month = params[:filter_month].to_s
      year = params[:filter_year].to_s
    else
      month = Date.today.month.to_s
      year = Date.today.year.to_s
    end
 @date = (month +'/01/' + year).to_date

    @reports = Report.find_by_sql(
    "SELECT c.name cat_name, r.date, r.id, r.login_from, r.name, r.name, r.sum, SUM((s.sum/IFNULL((SELECT w.working_days FROM working_days w WHERE DATE(w.month) = DATE('#{@date}') LIMIT 1), 1))/(SELECT COUNT(rr.id) FROM reports rr WHERE DATE(rr.date) = DATE(r.date) AND (SELECT COUNT(de2.id) FROM done_employees de2 WHERE de2.report_id = rr.id AND de2.empl_id = de.empl_id) > 0)) smeni                           
            FROM done_employees de           
            LEFT JOIN reports r ON de.report_id = r.id
            LEFT JOIN category c ON r.category = c.id 
            LEFT JOIN pricelist.pl_people e ON e.id = de.empl_id
            LEFT JOIN salaries s ON s.division_id = e.division_id AND e.position = s.position AND s.date_from <= '#{@date}'                    

             WHERE r.department = 'alp' AND r.closed_at IS NULL AND DATE(r.date) BETWEEN '#{@date.beginning_of_month}' AND '#{@date.end_of_month}' GROUP BY r.id
              ")
  end


  def search
    @page_title = 'Поиск отчетов'

    if !params[:s].to_s.blank?
      name = params[:s]
      if (params[:date_from] && params[:date_to])
       @date_from = params[:date_from].to_date
       @date_to = params[:date_to].to_date
     else
       @date_from = Date.today - 2.month
       @date_to = Date.today.end_of_month
     end
      if params[:division].to_s.blank?
        cond = ""
      else
        cond = " AND r.department = '#{params[:division]}'"
      end
    ActiveRecord::Base.connection.execute("SET group_concat_max_len = 2048;")
      @reports = 
      Report.find_by_sql(
                           "SELECT GROUP_CONCAT(DISTINCT CONCAT('<li>', done_employees.empl_name, '  (', done_employees.sum, '<font class=currency> руб.</font>)</li>') SEPARATOR ' ') as empls,
                                   GROUP_CONCAT(DISTINCT CONCAT('<li>', dm.material_name, ' - ', dm.count, '</li>') SEPARATOR ' ') as mats, 
                                   GROUP_CONCAT(DISTINCT CONCAT('<li>', IFNULL(w.name, p.name), ' - ', dw.count, '</li>') SEPARATOR ' ') as wrks,                                   
                            r.id as id,  r.name, r.date, r.sum, r.status, ct.name as cat_n,  ed.login as ed_name, r.tracker_id, r.request_id, r.department
                               FROM reports r 
                                    LEFT JOIN done_employees on done_employees.report_id = r.id 
                                    LEFT JOIN pricelist.pl_people ed on r.update_user_id = ed.id 
                                    LEFT JOIN done_materials dm ON dm.report_id = r.id 
                                    LEFT JOIN category ct ON ct.id = r.category 
                                    LEFT JOIN done_works dw ON dw.report_id = r.id
                                    LEFT JOIN works AS w ON dw.work_id=w.id && dw.pricelist=0
                                    LEFT JOIN pricelist.pl_items AS p ON dw.work_id=p.id && dw.pricelist=1
                                         WHERE r.name LIKE('#{name}') AND DATE(r.date) BETWEEN DATE('#{@date_from}') AND DATE('#{@date_to}') #{cond}
                                               GROUP BY r.id ORDER BY r.date,r.name LIMIT 1000")
      else
        @reports = ''
        @date_from = Date.today - 1.month
        @date_to = Date.today.end_of_month
    end


  end

  
private

  def get_reports
      db_name = Rails.configuration.database_configuration[Rails.env]["database"]
    if (params[:filter_month] && params[:filter_month] != '' && params[:filter_year])
      month = params[:filter_month].to_s
      year = params[:filter_year].to_s
    else
      month = Date.today.month.to_s
      year = Date.today.year.to_s
    end
    order = []
    if params[:name_s]
      order << db_name+".reports.name " + params[:name_s]
    end
    if params[:sdate]
      order << db_name+".reports.date " + params[:sdate]
    end
    if params[:ssum]
      order << db_name+".reports.sum " + params[:ssum]
    end
    if params[:sstatus]
      order << db_name+".reports.status " + params[:sstatus]
    end

    if order.empty?
      ord = "#{db_name}.reports.date, #{db_name}.reports.name"
    else
      ord = order.join(",")
    end

    @date = (month +'/01/' + year).to_date
    empl =  params[:filter_empl]
    worktype = params[:filter_worktype]
    status = params[:filter_status]
    @reports = Report.alp.in_month(@date).with_empl(empl).with_category(worktype).with_status(status).only_with_sale_materials(params[:sale_material]).only_with_money(params[:only_with_money]).only_with_works(params[:only_with_works]).find(:all, :order => ord)
  end

  def get_stats
     db_name = Rails.configuration.database_configuration[Rails.env]["database"]
    @page_title = 'Статистика по з/п'
    @employees = Division.alp.employees  # ЗП
     @bonus = []
     if params[:filter_month]
       month = params[:filter_month].to_s
       year = params[:filter_year].to_s
     else
       month = Date.today.month.to_s
       year = Date.today.year.to_s
     end
   if params[:filter_day] && params[:filter_day] != '0'     
     @date_from = (month +'/' + params[:filter_day] +'/' + year).to_date
     @date_to = (month +'/' + params[:filter_day] +'/' + year).to_date
   else   
     @date_from = (month +'/1/' + year).to_date
     @date_to = @date_from.end_of_month
   end
    work_days = WorkingDay.find(:first, :conditions => {:month => @date_from.beginning_of_month})
    if work_days
      @working_days = work_days.working_days.to_i
    else
      @working_days = 1
    end

    @category = params[:filter_worktype]
    if @boss
      @empl =  params[:filter_empl] if params[:filter_empl].to_i > 0 
    else
      @empl = @current_user.id
    end
    @date = @date_from
    cond = ''; cond2 ='';
    @reports = Report.alp.in_month(@date).find(:all, :conditions =>"status = 2")


    if @category && @category != ''
      cond += "AND r.category = #{@category} "
      @reports = @reports.select{|i| i.category == @category.to_i}
    end
    if @empl && @empl != '0' && !@empl.blank?
      cond += "AND de.empl_id = #{@empl}"
      cond2 = "empl_id = #{@empl} AND"
      @reports = @reports.select{|i| i.done_employees.select{|i| i.empl_id == @empl}.size > 0}
    end    
     @d_employees = DoneEmployee.find_by_sql(
    "SELECT e.login as nic, 
            e.id empl_id, 
            e.division_id,
            e.active,
            de.sum as sum, 
            de.de_count de_count,
            de.bonus as bonus,
           de.request_uniq request_uniq,            
           (SELECT s.sum FROM #{db_name}.salaries s WHERE s.date_from <= '#{@date}' AND s.division_id = e.division_id AND e.position = s.position ORDER BY date_from DESC LIMIT 1) as oklad,
            COUNT(sal.count_days) shifts 
                    FROM pricelist.pl_people e
            LEFT JOIN (SELECT GROUP_CONCAT(r.request_id  SEPARATOR ', ') request_uniq, COUNT(de.id) de_count, SUM(r.request_bonus_sum/r.work_time) as bonus, de.empl_id empl_id, SUM(de.sum) sum FROM #{db_name}.done_employees de 
            LEFT JOIN reports r ON de.report_id = r.id WHERE r.status = '2' AND r.department = 'alp' AND r.closed_at IS NULL AND DATE(r.date) BETWEEN '#{@date.beginning_of_month}' AND '#{@date.end_of_month}' #{cond} GROUP BY de.empl_id) de  ON e.id = de.empl_id

            LEFT JOIN (SELECT DAY(DATE(r.date)) count_days, de.empl_id empl_id FROM #{db_name}.done_employees de LEFT JOIN reports r ON de.report_id = r.id WHERE r.closed_at IS NULL AND r.status = '2' AND r.department = 'alp' AND DATE(r.date) BETWEEN '#{@date.beginning_of_month}' AND '#{@date.end_of_month}' #{cond} GROUP BY empl_id, count_days) sal  ON e.id = sal.empl_id


                                                               GROUP BY nic HAVING #{cond2} (de.sum > 0 OR (e.active = 'Y' AND e.division_id = 158))")

     # Бригадирские
     @payrolls = []
     period = Period.for_month(@date).for_department('alp')[0]
      manager_shifts = ManagerShift.for_department('alp').for_month(@date)
      manager_shifts.group_by{|i| i.employee}.each do |empl, shifts|
        date_shifts = shifts.map{|i| i.shift_date}.flatten
        # за отчеты
        br_sum = period.reports.select{|i| date_shifts.include? i.date.to_date}.map{|i| i.done_employees}.flatten.sum{|i| i.sum.to_f} * 0.1
        # за смены
        e_shifts = period.reports.select{|i| date_shifts.include? i.date.to_date}.map{ |i| i.done_employees}.flatten.group_by{|i| i.empl_id}
        days = WorkingDay.for_month(period.month).first
        working_days = days.working_days
        e_shifts.each do |empl_id, shifts|
          empl2 = Employee.find empl_id
          salary = Salary.find(:first, :conditions =>["division_id = ? AND position = ?", empl2.division_id.to_i, empl2.position], :order =>'date_from')
          if salary
            salary_for_day = salary.sum / working_days
            br_sum += shifts.map{ |i| i.report.date.to_date}.uniq.size * salary_for_day * 0.1              
          end
        end
        @payrolls << Payroll.new(:sum => br_sum, :employee_id => empl.id, :period_id => period.id, :zp_type_id => 3, :description => "Бригадирские - 10%") if br_sum.to_f > 0
      end

     
  end


  def set_var
    Employee.current_user = Employee.find(session[:user_id]) unless session[:user_id].nil?
    @current_user = Employee.current_user    
    @boss = (@current_user.accesses.for_department('alp').size > 0 && @current_user.accesses.for_department('alp').first.access_write == 1)
    $department = 'alp'
    $department_id = '158'
    cookies[:sys_dep] = { :value => $department }
  end



end