# -*- coding: utf-8 -*-
class AmsController < ApplicationController
#
   # Отдел
  $department = 'ams'
  $department_id = '151'


  # Авторизация (файл: controllers/application.rb)
  before_filter :isAuthorization
  before_filter :set_var

  # Права доступа (файл: controllers/application.rb)
  around_filter :isAccessRead
  around_filter :isAccessWrite, :only => :saveReport
  around_filter :isAccessDelete, :only => :delReport
  around_filter :isAccessSuper, :only => :tracker

  # Конструктор
  before_filter :construct

  def construct

    # Устанавливаем сессионную куку.
    cookies[:sys_dep] = { :value => $department }

    # Пункты меню
      $menu =
        [
          {"url"=>"index", "title"=>"Сводная таблица"},
          {"url"=>"stats", "title"=>"Статистика"},
         {"url"=>"stats_materials", "title"=>"Статистика по материалам"},
          {"url"=>"/managers", "title"=>"Диспетчеры"},
          {"url"=>"tracker", "title"=>"Имитация Трекера"},
          {"url"=>"search", "title"=>"Поиск"},
          {"url"=>"serials", "title"=>"Серийники"},
          {"url"=>"/periods?department=ams", "title"=>"Закрыть период"},
          {"url"=>"/access", "title"=>"Доступ"}
        ]


      # Тип работ над объектом
      $category_info = [
            {"name" => "Канальная",
             "price" => 0
            },
            {"name" => "Клиентская",
             "price" => 0
            },
            {"name" => "Обслуживание",
             "price" => 0
            },
            {"name" => "Несетевая",
             "price" => 0
            },
            {"name" => "Модернизация",
             "price" => 0
            }
          ]
      $category_info = CategoryDB.getCategory($department)

      # Статусы
      $status = [{'name'=>'Готово'}, {"name"=>'Отклонено'}, {'name'=>'Утверждено'}]


      # Вид аварии
      $crash_type = [
            {"name" => "Частная",
             "price" => 0
            },
            {"name" => "Корпоративная",
             "price" => 0
            }
      ]

      # Сотрудники
      $employeesAll = EmployeesDB.getEmployees("*")
    only_active = params[:all_empls] == 'true' ? 'false' : 'true'
      $employeesGroup = EmployeesDB.getEmployees(session[:user_department], only_active)

      # Формируем сессию
      params.each { |key, value| session[""+key+""] = value }
      $session = session

  end


  def stats_materials
    if (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
    @employees = Division.ams.employees  # ЗП
    @reports = Report.ams.in_month(@date).with_category(params[:filter_worktype]).with_empl(params[:filter_empl]).with_any_material
 end


  def index
    @employees = Division.ams.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
    @filter_empl =  params[:filter_empl]

    if @boss
      @wage = 0
    else
      @wage = @current_user.done_employees.in_month(@date).sum(:sum)
    end
    
   
    cond = []
    cond << "r.department = 'ams' AND r.closed_at IS NULL"
    if @filter_empl != '' && @filter_empl.to_i != 0
      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
    if params[:filter_status] && params[:filter_status] != '5'
      if params[:filter_status].to_i == 0
        cond << "(status = '0' OR status IS NULL)"
      else
        cond << "status = #{params[:filter_status]}"
      end
    end
    cond << "r.category = #{params[:filter_worktype]}" if params[:filter_worktype] && params[:filter_worktype] != ''
    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>', #{DoneMaterial.inst_type_for_sql}, ': ', 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, r.department, r.date, r.name, r.sum, r.status, ct.name as cat_n,  ed.login as ed_name, r.tracker_id, r.topology_problem_tracker_id, r.transport_sum
                               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 AND (dm.inst_type != 6 OR dm.pay_type != 4)
                                    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.date,r.name")
      if params[:manager] && params[:manager] != ''
        manager_reports = ManagerShift.for_month(@date).for_departments(['ams', 'mams']).with_employee(params[:manager]).map{|i| i.reports}.flatten
        @reports = @reports.select{|i| manager_reports.include? i }
      end

      
  end


  def tracker
    ### PAGE INFO
    @page_title = 'Имитация Трекера'
    @currPage = "tracker"

    now = Time.now.strftime("%d.%m.%Y %H:%M")
    @date_start = now
    @date_stop = now
  end

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

    @report.calculate
     render :update do |page|
      page.replace_html  'sums', :partial => 'sum', :locals =>{:report => @report}
      page.replace_html  'itog', :partial => 'itog', :locals =>{:report => @report}
    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 = 'ams'
    end
    @report.category =  params[:type]
    @pricelist = Division.ams.pricelist.with_category(@report.category)
    @top_works = (@report.done_works.map{|i| i.work} + DoneWork.top_works(@report)).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 new
    #http://sdelka.rinet.net/ams/new?park=&employee_login=Chichibab&employee_login2=kerp&crash_login=kozh5&tracker_id=1018208&date_in=09.11.2017+15%3A17%3A31&date_out=09.11.2017+19%3A09%3A41&crash_type=4
    if (@current_user.accesses.for_department('ams').size > 0 && @current_user.accesses.for_department('ams').first.access_superuser == 1) ||  params[:tracker_id].to_i > 0
      

      if params[:tracker_id] && params[:resp]
        if params[:resp].to_s.downcase != @current_user.login.downcase
          redirect_to :controller => :logout, :action => :index          
        end
      end
      category = Category.from_tracker_to_ams(params[:crash_type].to_i)
      date = params[:date_in].to_s.blank? ? Time.now : params[:date_in].to_s 
      date_stop = params[:date_out].to_s.blank? ? Time.now : params[:date_out].to_s 

      @report = Report.new(:name => params[:crash_login].to_s, :date => date, :date_stop => date_stop, :tracker_id => params[:tracker_id].to_s, :category => category, :serial_new => "", :serial_old => "", :department => 'ams', :transport_sum => params[:park], :status => 0)
      if params[:employee_login] && !params[:employee_login].to_s.blank? 
        empl = Employee.find_by_login params[:employee_login]
        @report.done_employees.build(:employee => empl) if empl
      end
      if params[:employee_login2] && !params[:employee_login2].to_s.blank? 
        empl2 = Employee.find_by_login params[:employee_login2]
        @report.done_employees.build(:employee => empl2) if empl2
      end
      @pricelist = Division.ams.pricelist.with_category(@report.category)
      @top_works = (@report.done_works.map{|i| i.work} + DoneWork.top_works(@report)).uniq
      @pricelist = @top_works + (@pricelist - @top_works)

    else
      flash[:error] = "Новые отчеты следует создавать из трекера!"
      redirect_to :action => 'index'
    end
  end

  def create
    @report = Report.new(params[:report])
    unless @report.save
      flash.now[:error] =  (@report.errors.each_full { |msg| puts msg }).to_s
    end
    respond_to do |format|
      unless flash.size > 0
        flash[:notice] = 'Запись обновлена'
        format.html { redirect_to ams_index_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
        format.xml  { head :ok }
      else
        @pricelist = Division.ams.pricelist.personnel.with_category(@report.category)
        format.html { render :action => "new", :report => @report }
      end
    end
  end

def edit
  @report = Report.find(params[:id])
  @pricelist =  Division.ams.pricelist.with_category(@report.category)
  @top_works = (@report.done_works.map{|i| i.work} + DoneWork.top_works(@report)).uniq
  @pricelist = (@top_works + (@pricelist - @top_works)).compact.uniq
 end

  def update
    @report = Report.find(params[:id])
    respond_to do |format|
      if @boss || @report.status != 2
        if @report.update_attributes(params[:report])
        flash[:notice] = 'Запись обновлена'
        format.html { redirect_to ams_index_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
        format.xml  { head :ok }
      else
          flash.now[:error] = @report.errors.each_full { |msg| puts msg }
          format.html { render :action => "edit" }
          format.xml  { render :xml => @report.errors, :status => :unprocessable_entity }
        end
      else
        flash[:error] = 'У вас недостаточно прав для изменения отчета'
      end
    end

  end

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


  def addReport2
    # Page info
    @page_title = 'Добавить / Редактировать отчет'
    @currPage = "addReport"



    ## INFO
    if(params[:id]) then @id = params[:id].to_i else @id = 0 end
    @crash_login = (params[:crash_login]) ? Check.isText(params[:crash_login]) : ""
    @employee_login = (params[:employee_login]) ? Check.isText(params[:employee_login]) : ""
    @employee_id = EmployeesDB.getUserId(@employee_login)
    if params[:employee_login2] && params[:employee_login2] != ''
      @employee_id2 = EmployeesDB.getUserId(params[:employee_login2]) 
      @employee_login2 = params[:employee_login2]
    end
      @date_in = (params[:date_in]) ? Check.isText(params[:date_in]) : ""
    @date_out = (params[:date_out]) ? Check.isText(params[:date_out]) : ""
    @crash_type = (params[:crash_type]) ? Check.isText(params[:crash_type]) : ""
    @tracker_id = (params[:tracker_id]) ? Check.isText(params[:tracker_id]) : ""
    @client_type = ""
    @serial_new = ""
    @serial_old = ""
    @com_trouble = "" # В чем возникла сложность
    @electro = "0" # Переделка электричества
    @weld = "0" # Переделка электричества
    @dhcp_login = "" # Логин клиента у которого был левый DHCP
    @arrangements_dhcp = "" # Какие меры были приняты , чтобы ситуация не повторялась

    @get_money = "1" # Полученные деньги с клиента
    @com_get_money = "" # Обоснование невзятия денег

    @crash_close = "0" # Авария устранена ?
    # НЕТ
    @diagnosis = "0"    # Проведена ли подробная диагностика
    @global_reasons = "" # Авария не устранена по независящим от Ринет причинам

    # ДА
    @crash_style = "0" # Вид аварии

    # HTML шаблоны сотрудников
    tmp = ApplicationHelper::HTMLtemplates.new
    @HTMLemployees = tmp.getHtmlGroupEmployees($employeesGroup)
    if @id > 0
    # Рабочая группа
    @employees_done = EmployeesDoneDB.getEmployees(@id)
    em = EmployeesDB.getEmployeesWithLeavePerson(@employees_done.map{|i| i['id']}.compact!)
    @HTMLemployeesAll = tmp.getHtmlGroupEmployees(em)
    else
    @HTMLemployeesAll = tmp.getHtmlGroupEmployees($employeesAll)
    end
    @HTMLDoneMaterials = ApplicationHelper::HTMLtemplates.getHtmlDoneStoreMaterials(@id)

    clientType='P'

    if(@id>0)
      @id = @id.to_s
      begin
        ReportsDB.find(@id)
      rescue
        print 'Такого идентификатора - НЕТ.'
      else
        report = ReportsDB.find_by_sql("SELECT * FROM reports WHERE id="+@id+"")
        @crash_login = report[0]['name']
        @date_in = Time.parse(report[0]['date']).strftime("%d.%m.%Y %H:%M")
        @date_out = Time.parse(report[0]['date_stop']).strftime("%d.%m.%Y %H:%M")
        #@date_in = report[0]['date']
        #@date_out = report[0]['date_stop']
        @crash_type = report[0]['category']
        @client_type = report[0]['client_type']
        @tracker_id = report[0]['tracker_id']
        @serial_new = report[0]['serial_new']
        @serial_old = report[0]['serial_old']
        @com_trouble = report[0]['com_trouble'] # В чем возникла сложность
        @electro = report[0]['electro'].to_s # Переделка электричества
        @weld = report[0]['weld'].to_s # Переделка электричества
        @dhcp_login = report[0]['dhcp_login'] # Логин клиента у которого был левый DHCP
        @arrangements_dhcp = report[0]['com_other_materials'] # Какие меры были приняты , чтобы ситуация не повторялась

        @get_money = report[0]['get_money'].to_s # Полученные деньги с клиента
        @com_get_money = report[0]['com_get_money'] # Обоснование невзятия денег

        @total_sum_get_money = report[0]['total_sum_get_money'].to_s
        @total_sum_works = report[0]['total_sum_works'].to_s
        @total_sum_materials = report[0]['total_sum_materials'].to_s
        @total_sum_comment = report[0]['total_sum_comment'].to_s


        @crash_close = report[0]['close'].to_s # Авария устранена ?
        # НЕТ
        @diagnosis = report[0]['diagnosis'].to_s    # Проведена ли подробная диагностика
        @global_reasons = report[0]['global_reasons'] # Авария не устранена по независящим от Ринет причинам

        # ДА
        @crash_style = report[0]['style'].to_s # Вид аварии

        clientType=(@crash_style==1) ? 'C' : 'P'



      end
    end

    @pricelistWorks = PricelistDB.getPositions($department_id, @id, clientType)
    @HTMLPricelistWorks = ApplicationHelper::HTMLtemplates.getHTMLPricelistWorks($department_id, @id, clientType)

  end




  def delReport
    if(params[:id].to_i>0)
      id = params[:id].to_i
    else
      id = 0
    end

    if(id>0)
      begin
        @report = Report.find(id)
      rescue
        print 'Такого идентификатора - НЕТ.'
      else
        if @report.destroy
          flash[:notice] = "Отчет удален"
        else
          flash[:error] = @report.errors.each_full { |msg| puts msg }
        end
        redirect_to :action => "index"
      end
    end
  end




  def saveReport
    crash_login = params[:title]
    date_in = Time.parse(params[:date_in]).strftime("%Y-%m-%d %H:%M:%S")
    date_out = Time.parse(params[:date_out]).strftime("%Y-%m-%d %H:%M:%S")

    crash_type = params[:crash_type]
    crash_close = params[:crash_close]
    crash_style = params[:crash_style]
    get_money = params[:get_money]
    get_money_sum = params[:get_money_sum]
    diagnosis = params[:diagnosis]
    global_reasons = params[:global_reasons]
    com_get_money = [:com_get_money]
    serial_new = params[:serial_new]
    serial_old = params[:serial_old]
    com_trouble = params[:com_trouble]
    electro = params[:electro]
    weld = params[:weld]
    tracker_id = params[:tracker_id]
    dhcp_login = params[:dhcp_login]
    arrangements_dhcp = params[:arrangements_dhcp]
    client_type = params[:client_type]

    total_sum_get_money = params[:total_sum_get_money].to_s
    total_sum_works = params[:total_sum_works].to_s
    total_sum_materials = params[:total_sum_materials].to_s
    total_sum_comment = params[:total_sum_comment].to_s




    ######################################################## Записываем в таблицу REPORTS
    if(params[:id].to_i>0)
      report_id = params[:id].to_i
      sv_report = ReportsDB.find(report_id)
    else
      report_id=0;
      sv_report = ReportsDB.new
    end

    # Checking report....
    check_hash = ReportsDB.issetReport(crash_login, date_in, crash_type, params[:empl], $department, report_id)

    # get
    sv_report.date = date_in
    sv_report.name = crash_login
    sv_report.date_stop = date_out
    sv_report.category = crash_type
    sv_report.close = crash_close
    sv_report.style = crash_style
    sv_report.get_money = get_money
    sv_report.get_money_sum = get_money_sum
    sv_report.diagnosis = diagnosis
    sv_report.global_reasons = global_reasons
    sv_report.serial_new = serial_new
    sv_report.serial_old = serial_old
    sv_report.com_trouble = com_trouble
    sv_report.electro = electro
    sv_report.weld = weld
    sv_report.tracker_id = tracker_id
    sv_report.dhcp_login = dhcp_login
    sv_report.com_conflicts = arrangements_dhcp
    sv_report.department = $department
    sv_report.client_type = client_type

    sv_report.total_sum_get_money = total_sum_get_money
    sv_report.total_sum_works = total_sum_works
    sv_report.total_sum_materials = total_sum_materials
    sv_report.total_sum_comment = total_sum_comment
    sv_report.check_hash = check_hash

    # save
    sv_report.save

    ####################################################### Получаем последний элемент или элемент редактирования
    if(report_id==0)
      last_element_db = ReportsDB.find_by_sql("SELECT id FROM reports WHERE department='"+$department+"' ORDER BY id DESC LIMIT 1")
      report_id = last_element_db[0]['id']
      isMaterials_save = true # поменять в TRUE! установлено в fALSE для теста
    else
      report_id = report_id
      isMaterials_save = false
    end


    ####################################################### Сохраняем работы (из прайс-листа)
    # get and save
      if(report_id.to_i>0)
        WorksPricelistDoneDB.delWorks(report_id)
      end

      params.each {|key, value|
          key = "#{key}"
          value = "#{value}"

          if(key.index("work_") && value.to_i>0)
            work_id = key[5..-1]
            if(work_id.to_i>0)
              sv_work = WorksDoneDB.new
              sv_work.work_id = work_id
              sv_work.report_id = report_id
              sv_work.date_create_report = date_in
              sv_work.count = value
              sv_work.department = $department
              sv_work.pricelist = 1
              sv_work.save
            end
          end
        }




    ####################################################### Сохраняем потраченные материалы на склад (отписываем)
    if(isMaterials_save)
        params.each {|key, value|
            key = "#{key}"
            value = "#{value}"

            if(key.index("material_") && value.to_i>0)
              material_info = key[9..-1].split(/\|/)

              material_id = material_info[0]
              store_employee_name = material_info[1]
              employee_id = EmployeesDB.getUserId(store_employee_name)
              store_employee_id = AssetsDB.getUserId(store_employee_name)

              material_name = AssetsDB.getMaterialName(material_id)

              # Price
              price = (params["mprice_"+material_id+"|"+store_employee_name+""]) ? params["mprice_"+material_id+"|"+store_employee_name+""] : 0


              # Serials
              serials = (params["serial_"+material_id]) ? params["serial_"+material_id] : 'false'

              if(serials!='false')
                # Save Serials
                for serial in serials

                  sv_serial = SerialsDoneDB.new
                  sv_serial.material_id = material_id
                  sv_serial.serial = serial
                  sv_serial.date = Time.now.to_s(:db)
                  sv_serial.report_id = report_id
                  sv_serial.date_create_report = date_in


                  sv_serial.employee_id = employee_id
                  sv_serial.store_employee_id = store_employee_id
                  sv_serial.employee_name = store_employee_name
                  sv_serial.department = $department
                  sv_serial.save

                end
              end


              # Сохраняем ЛОГ материала
              sv_materials = MaterialsDoneDB.new
              sv_materials.material_id = material_id
              sv_materials.material_name = material_name
              sv_materials.date = Time.now.to_s(:db)
              sv_materials.report_id = report_id
              sv_materials.date_create_report = date_in
              sv_materials.count = value

              sv_materials.employee_id = employee_id
              sv_materials.store_employee_id = store_employee_id
              sv_materials.employee_name = store_employee_name
              sv_materials.department = $department
              sv_materials.price = price
              if(check_hash)
                sv_materials.save
              end

              # Сохраняем в базу склада
              # .saveMaterials(material_id, value, store_employee)

            end
          }
        # Сохраняем в базу склада
       # if(session[:isAdmin].to_s == 'true')
          StoreReport.sendData(report_id)
       # end
    end



    ######################################################## Сохраняем рабочую группу
    # get and save
    if(report_id.to_i>0)
      EmployeesDoneDB.delEmployees(report_id)
    end

    # GET
    allEmployees = params[:empl]



    i=0
    for emplId in allEmployees
      if(emplId.to_i>0)
        sv_empl = EmployeesDoneDB.new
        sv_empl.report_id = report_id
        sv_empl.empl_id = emplId
        # Определяем имя сотрудника
        sv_empl.empl_name = EmployeesDB.getUserName(emplId)
        sv_empl.date_create_report = date_in
        sv_empl.department = $department
        sv_empl.save
        i+=1
      end
    end

    # Считаем сумму з/п и обновляем базу
    countSum = EmployeesPay.countEmployeeSum(report_id)


    # отладка
    #@emplPay_t = $emplPay_t
    #@addWorkPay_t = $addWorkPay_t
    #@stopWorkPay_t = $stopWorkPay_t
    #@addPay_t = $addPay_t




    ################################################### ФИНАЛЬНЫЕ ДЕЙСТВИЯ
    # редирект
    #link = url_for(:controller => "private", :action => "index")

    redirect_to :action => "index"

  end


  def stats
    # Page info
    @page_title = 'Статистика З/П'
    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.ams.in_month(@date).with_status(params[:filter_status]).find(:all, :include => [:employees])
    @d_employees = @reports.map{|i| i.employees}.flatten.uniq.compact.sort_by(&:login)
    @employees = @d_employees
    if (params[:filter_empl] && params[:filter_empl] != '')
     @d_employees = @d_employees.select{|i| i.login == (Employee.find params[:filter_empl]).login}
    end
  end

   def managers_wage
     if (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

     @categories = Category.ams
     @date = (month +'/01/' + year).to_date
     if params[:filter_weld] &&  params[:filter_weld].to_i == 0
       dep = ['ams']       
       sel_cats = " AND reports.category IN (#{Category.ams.select{|i| i.process_id == 44}.map{|i| i.id}.join(', ')})"
    elsif params[:filter_weld] &&  params[:filter_weld].to_i == 1
      dep = ['mams']
      sel_cats = " AND reports.category IN (#{Category.ams.select{|i| i.process_id == 43}.map{|i| i.id}.join(', ')})"
    else
      dep = ['soft', 'mams']
      sel_cats = ''
    end
    

     @managers = ManagerShift.for_departments(dep).for_month(@date).find(:all, :order => "employee_id, shift_date")#.soft.in_month(@date) #.with_categories(cats)
     @reports = Report.find_by_sql(["SELECT * FROM reports  WHERE DATE(date) IN (?) AND closed_at IS null AND department = 'ams' #{sel_cats}", @managers.map{|i| i.shift_date}])
     @schedule = WorkingDay.for_month(@date).first
   end


  def serials
    ### PAGE INFO
    @page_title = 'Серийные номера свитчей'
    @currPage = "serials"
    @result = {}

    # Получаем список сотрудников
    employeesNames = {}
    for val in $employeesAll
      employeesNames[val['id']] = val['login']
    end

    allData = ReportsDB.find_by_sql("SELECT
                        a.*,
                        b.empl_id,
                        b.empl_name
                        FROM reports AS a
                        LEFT JOIN done_employees AS b ON(b.report_id=a.id)
                        WHERE
                        (serial_new!=0 || serial_old!=0)
                    ")

                    i=0
    for res in allData
      report_id = res['id'].to_i
        if(!@result[report_id])
          @result[report_id] = {}
          @result[report_id]['name'] = res['name']
         # date = res['date'].split(' ').reverse.join("/")
          date = res['date'].split(' ')
          date[0] = date[0].split('-').reverse.join(".")
          @result[report_id]['date'] = date.join(" ")
          @result[report_id]['serial_new'] = res['serial_new'].to_s
          @result[report_id]['serial_old'] = res['serial_old'].to_s

          @result[report_id]['report_id'] = report_id
          @result[report_id]['numsort'] = i
          i+=1
        end


        # ================================================================ Сохраняем сотрудников
        empl_name = employeesNames[res['empl_id'].to_i].to_s
        if(@result[report_id]['employees'])

          # Проверяем был ли уже сотрудник обработан для этого отчета
          check = false
          @result[report_id]['employees'].each {|empl| if(empl.has_value?(empl_name)) then check=true end }

          if(check==false)
            @result[report_id]['employees'] += [{'name'=>empl_name, 'sum'=>res['empl_sum']}]
          end
        else
          @result[report_id]['employees'] = [{'name'=>employeesNames[res['empl_id'].to_i], 'sum'=>res['empl_sum']}]
        end


    end

     # Делаем возможность сортироки через базу (З.Ы. Идиотская руби) - 2 (пересобираем массив)
    a=0
    @result2 = []
    while a<@result.size
     @result.each {|reportId, info|
       if(info['numsort'].to_i==a.to_i)
            @result2[a]=info
            #@result.delete(reportId)
            break
       end
      }
      a+=1
    end
    @result = @result2

  end



  def search

    ### PAGE INFO
    @page_title = 'Поиск отчетов'
    

    if params[:s] && !params[:s].to_s.blank?
      name = params[:s]
    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.topology_problem_tracker_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}')
                                               GROUP BY r.id ORDER BY r.date,r.name LIMIT 1000")
      else
      @reports = ''
    end


  end

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

end







class EmployeesPay

  def self.countEmployeeSum(report_id)
    # ПОлучаем инфу о отчете
    report_info = ReportsDB.find_by_sql("SELECT * FROM reports WHERE id="+report_id.to_s+"")
    category = report_info[0]['category'].to_s
    serial_new = report_info[0]['serial_new'].to_s
    serial_old = report_info[0]['serial_old'].to_s
    com_trouble = report_info[0]['com_trouble'].to_s
    electro = report_info[0]['electro'].to_s
    weld = report_info[0]['weld'].to_s
    dhcp_login = report_info[0]['dhcp_login'].to_s
    arrangements_dhcp = report_info[0]['arrangements_dhcp'].to_s
    diagnostic = report_info[0]['diagnosis'].to_s
    crash_close  = report_info[0]['close'].to_i
    global_reasons = report_info[0]['global_reasons'].to_i
    crash_style = report_info[0]['style'].to_i
    get_money = report_info[0]['get_money'].to_i
    get_money_sum = report_info[0]['get_money_sum'].to_i
    date_in = report_info[0]['date']
    date_out = report_info[0]['date_stop']

    work_hours = ((Time.parse(date_out).to_i - Time.parse(date_in).to_i)/60)/24
    #work_hours = 4


    # Получаем сохранённых сотрудников
    result_employee_done = EmployeesDoneDB.find_by_sql("SELECT * FROM done_employees WHERE report_id="+report_id.to_s+" ORDER BY role ASC")
    employeesCount = result_employee_done.length.to_i

    # Рассчет


    ### Проблема с качеством
    if(category=="30")
      # Осложнена ли авария
      bonus = 620
      if(employeesCount>1)
        bonus = bonus * employeesCount
        bonus *=0.7
      end
    end

    # Канальная или Модернизация или Проблема на узле
    if(category=="16" || category=="20" || category=="31")
      # Осложнена ли авария
      bonus = (serial_new!='' || serial_old!='' || com_trouble!='' || electro!='' || weld!='' || dhcp_login!='' || arrangements_dhcp!='') ? 1200 : 800
      if(employeesCount>1)
        bonus = bonus * employeesCount
        bonus *=0.7
      end
    end

    # Клиентская
    if(category=="17")

      if(crash_close == 1) # Авария не устранена

        if(diagnostic=='0') # Диагностика проводилась
          bonus = 100
        else
          if(global_reasons==0) # авария не устранена по независ от ринет причинам
            bonus = 75
          end
        end


      else

        if(crash_style==0) # Частная
          if(get_money==0) # есть деньги
            if(diagnostic=='0') # Диагностика проводилась
               bonus = (employeesCount>1) ? 250+70 : 600 # 30.05.2014  было 500
            else
               bonus = (employeesCount>1) ? 250+120 : 600
            end
          else
            bonus = (employeesCount>1) ? 250 : 460 # 30.05.2014  было 360
          end
          if(employeesCount>1) then bonus *= employeesCount end
        else
          bonus = 620
          if(employeesCount>1)
            bonus = bonus * employeesCount
            bonus *=0.7
          end
        end
      end
    end


    # Обслуживание
    if(category=="18")
      if(employeesCount>1)
        bonus = get_money_sum/2
        pay = bonus / employeesCount
      else
        bonus = get_money_sum/2
        pay = bonus
      end
    end

    # Не сетевая
    if(category=="19")
      bonus = work_hours.to_i*130
      bonus = bonus * employeesCount
    end

    # сумма премии каждому работнику
    if(category!="18")
     pay = bonus / employeesCount
    end




    totalProjectSum = 0
    for res in result_employee_done
      sum = 0
      emplId = res['empl_id']
      db_id = res['id'].to_i



      # СТОИМОСТИ


      # Отладка
      #$emplPay_t = emplPay
      #$addWorkPay_t = addWorkPay
      #$stopWorkPay_t = stopWorkPay
      #addPay_t = addPay


      # Добавляем проверки если нужны......
      sum = pay

      # Округление (2 знака после запятой)
      sum = sprintf("%.2f", sum).to_f

      # Доля участия
      hand = 1.00/employeesCount

      # Обновляем таблицу
      empl = EmployeesDoneDB.find(db_id)
      empl.sum = sum
      empl.hand = hand
      empl.save

      totalProjectSum += sum

    end



    ## Обновляем отчет на общую сумму сделки
    report = ReportsDB.find(report_id)
    report.sum = totalProjectSum
    report.save


  end

 

end