# -*- coding: utf-8 -*-
class CorpController < ApplicationController
#
   # Отдел
  $department = 'corp'
  $department_id = '149'


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

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

  # Конструктор
  before_filter :construct
  before_filter :set_users, :only => [:edit_shift, :update_shift]
  before_filter :get_stats, :only => [:stats, :stats_excel]

  def construct

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


      # Тип работ над объектом
      $category_info = CategoryDB.getCategory($department)

    # Пункты меню
      $menu =
        [
          {"url"=>"index", "title"=>"Сводная таблица"},
          {"url"=>"stats", "title"=>"Статистика"},
          {"url"=>"addReport", "title"=>"Добавить отчет"},
          {"url"=>"/work_shifts", "title"=>"Смены"},
      {"url"=>"reports", "title"=>"Общая статистика"},
         {"url"=>"work_stats", "title"=>"Статистика по работам"},
          {"url"=>"/works", "title"=>"Редактор работ"},
          {"url"=>"/access", "title"=>"Доступ"}
        ]

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

      # Сотрудники
      $employeesAll = EmployeesDB.getEmployees("*")
      $employeesGroup = EmployeesDB.getEmployees(session[:user_department])

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

  end


  def index
    redirect_to corp2_index_path
 #    ### PAGE INFO
 #    @page_title = 'Сводная таблица Корпотдела'
 #    @currPage = "index"

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


 #    # Получаем список всех работ
 #    allWorks = WorksDB.getWorks($department)


 #    # ФИЛЬТРАЦИЯ
 #    time = Time.new
 #    sqlFilterDepartment = "a.department='"+$department+"'"
 #    if($session['filter_month'] && $session['filter_month'].to_i>0) then sqlFilterMonth = "AND MONTH(a.date)="+$session['filter_month'].to_s+"" else sqlFilterMonth = "AND MONTH(a.date)="+time.month.to_s+"" end
 #    if($session['filter_day'] && $session['filter_day'].to_i>0) then sqlFilterDay = "AND DAY(a.date)="+$session['filter_day'].to_s else sqlFilterDay = '' end
 #    if($session['filter_year'] && $session['filter_year'].to_i>0) then sqlFilterYear = "AND YEAR(a.date)="+$session['filter_year'].to_s else sqlFilterYear = "AND YEAR(a.date)="+time.year.to_s+"" end
 #    if($session['filter_empl'] && $session['filter_empl'].to_i>0) then sqlFilterEmployee = "AND b.empl_id="+$session['filter_empl'].to_s else sqlFilterEmployee = '' end
 # #   if($session['filter_status'] && $session['filter_status']!='') then sqlFilterStatus = "AND a.status="+$session['filter_status'].to_s else sqlFilterStatus = '' end
 #    if($session['filter_worktype'] && $session['filter_worktype'].to_s !="") then sqlFilterWorkType = "AND a.category="+$session['filter_worktype'].to_s else sqlFilterWorkType = '' end


 #    # СОРТИРОВКА
 #    orderParams = ''
 #    if(params[:sdate])
 #      if(orderParams!='') then orderParams+=', ' end
 #      orderParams+='a.date '+params[:sdate]
 #    end
 #    if(params[:slogin])
 #      if(orderParams!='') then orderParams+=', ' end
 #      orderParams+='a.name '+params[:slogin]
 #    end
 #    if(params[:swtype])
 #      if(orderParams!='') then orderParams+=', ' end
 #      orderParams+='a.category '+params[:swtype]
 #    end
 #    if(params[:ssum])
 #      if(orderParams!='') then orderParams+=', ' end
 #      orderParams+='a.sum '+params[:ssum]
 #    end
 #    if(params[:sstatus])
 #      if(orderParams!='') then orderParams+=', ' end
 #      orderParams+='a.status '+params[:sstatus]
 #    end

 #    if(orderParams!='') then orderParams = "ORDER BY "+orderParams end
 #    if(orderParams=='') then orderParams = "ORDER BY a.name ASC" end

 #    # Пoлучаем инфу
 #    allData = ReportsDB.find_by_sql("SELECT
 #                        a.*,
 #                        b.empl_id,
 #                        b.sum AS empl_sum,
 #                        c.work_id,
 #                        c.count AS work_count,
 #                        d.employee_name,
 #                        d.material_name,
 #                        d.count,
 #                        e.show
 #                        FROM reports AS a
 #                        LEFT JOIN done_employees AS b ON(b.report_id=a.id)
 #                        LEFT JOIN done_works AS c ON(c.report_id=a.id)
 #                        LEFT JOIN done_materials AS d ON(d.report_id=a.id)
 #                        LEFT JOIN works AS e ON(c.work_id=e.id)
 #                        WHERE a.dhcp_login IS NULL AND 
 #                        "+sqlFilterDepartment+"
 #                        "+sqlFilterMonth+"
 #                        "+sqlFilterDay+"
 #                        "+sqlFilterYear+"
 #                        "+sqlFilterEmployee+"
 #                        "+sqlFilterWorkType+"
 #                        "+orderParams+"
 #                    ")


 #    @result = {}

 #    # Делаем возможность сортироки через базу (З.Ы. Идиотская руби) - 1

 #    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']
 #          @result[report_id]['date'] = res['date'].split('-').reverse.join(".")
 #          @result[report_id]['sum'] = res['sum'].to_i
 #          @result[report_id]['status'] = res['status']
 #          @result[report_id]['category'] = CategoryDB.getCategoryInfo(res['category'])['name']
 #          @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

 #        # ================================================================ Сохраняем работы
 #        if(res['work_id']!=nil)
 #            work_id = res['work_id'].to_i
 #            if(allWorks[work_id] && res['show'].to_i>0)
 #                work_name = allWorks[work_id]['name'].to_s
 #                work_price = allWorks[work_id]['price'].to_s
 #                work_measure = allWorks[work_id]['measure'].to_s
 #                work_sum = res['work_count'].to_i*work_price.to_i
 #                if(@result[report_id]['works'])

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

 #                  if(check==false)
 #                    @result[report_id]['works'] += [{'name'=>work_name, 'count'=>res['work_count'], 'price'=>work_price, 'sum'=>work_sum, 'measure'=>work_measure}]
 #                  end
 #                else
 #                  @result[report_id]['works'] = [{'name'=>work_name, 'count'=>res['work_count'], 'price'=>work_price, 'sum'=>work_sum, 'measure'=>work_measure}]
 #                end
 #            end
 #        end
 #        # ================================================================ Сохраняем материалы
 #        if(@result[report_id]['materials']==nil)
 #          @result[report_id]['materials']=[{"employee"=>res['employee_name'], "name"=>res['material_name'], "count"=>res['count']}]
 #        else
 #          check = false
 #          @result[report_id]['materials'].each {|wrk| if(wrk.has_value?(res['material_name'])) then check=true end }
 #          if(check==false)
 #            @result[report_id]['materials']+=[{"employee"=>res['employee_name'], "name"=>res['material_name'], "count"=>res['count']}]
 #          end
 #        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 addReport
    # Page info
    @page_title = 'Добавить / Редактировать отчет'
    @currPage = "addReport"

    ## INFO
    if(params[:id]) then @id = params[:id].to_i else @id = 0 end
    @name = ""
    @date = Time.now.to_date
    @status = ""
    @client_type = ""
    @category = 0
    @com_stay_work = ""
    @diagnosis = '0'
    @weld = '0'
    @lines = '0'
    @work_count_type = '0'


    # Ответственный
    @employee_responsible_id = session[:user_id].to_i
    @employee_responsible_name = EmployeesDB.getUserName(@employee_responsible_id)

    ## ADD INFO
    @works = WorksDB.getWorks($department)
    @works_done = [{}]
    @employees_done = [{}]

    # 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
      @employees_done = [{ 'id'=>""+@employee_responsible_id.to_s+"", 'role_name'=>'Ответственный', 'role'=>'1' }]
      @HTMLemployeesAll = tmp.getHtmlGroupEmployees($employeesAll)
    end


    # HTML шаблоны сотрудников
    tmp = ApplicationHelper::HTMLtemplates.new
    @HTMLemployees = tmp.getHtmlGroupEmployees($employeesGroup)
    @HTMLemployeesAll = tmp.getHtmlGroupEmployees($employeesAll)
    @HTMLDoneMaterials = ApplicationHelper::HTMLtemplates.getHtmlDoneStoreMaterials(@id)

    @HTMLPricelistWorks = ApplicationHelper::HTMLtemplates.getHTMLPricelistWorks($department_id, @id)




    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+"")
        @name = report[0]['name']
        @date = report[0]['date']
        @category = report[0]['category']
        @diagnosis = report[0]['diagnosis']
        @weld = report[0]['weld']
        @status = report[0]['status']
        @com_stay_work = report[0]['com_stay_work']
        @lines = report[0]['electro']
        @client_type = report[0]['client_type']
        @work_pay_type = report[0]['serial_new']
        @work_count_type = report[0]['work_count_type']
        @get_money = report[0]['get_money'].to_s # Полученные деньги с клиента
        @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
        @problem_tracker_id = report[0]['problem_tracker_id']

        # Выполненные работы
        @works_done = WorksDoneDB.getWorks(@id)


        # Рабочая группа
        @employees_done = EmployeesDoneDB.getEmployees(@id)


        # Потраченные материалы
        @materials = ""


      end
      # work_type=internet&client_login=ozper12-cis&employee_login=annet17

    elsif params[:client_login]
      @name = params[:client_login]
      @category = (params[:work_type] == 'internet' ? 8 : 9)      
    end

    #@HTMLAddWorks = ApplicationHelper::HTMLtemplates.getHTMLAddWorks($department, @id, @category)
  end




  def delReport
    id = params[:id].to_i
    if(id>0)
      begin
        @report = Report.find(id)
      rescue
        flash[:error] = 'Такого идентификатора - НЕТ.'
      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
    name = Check.isText(params[:title])
    workDate = params[:date]
    category = Check.intval(params[:category])
    diagnosis = Check.intval(params[:diagnosis])
    weld = Check.intval(params[:weld])
    status = Check.intval(params[:status])
    com_stay_work = Check.isText(params[:com_stay_work])
    lines = Check.intval(params[:lines])
    work_count_type = Check.intval(params[:work_count_type])
    work_pay_type = (params[:work_pay_type]) ? Check.intval(params[:work_pay_type]) : 1
    com_get_money = (params[:notStandartWorkCom]) ? Check.isText(params[:notStandartWorkCom]) : ""
    get_money_sum = (params[:notStandartWorkSum]) ? Check.isText(params[:notStandartWorkSum]) : ""
    get_money = params[:get_money]
    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
    client_type = params[:client_type]

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

    # Check isset report
    #check_hash = ReportsDB.issetReport(name, workDate, 1, params[:empl], $department, report_id)
  check_hash = 111
  unless params[:id].to_i > 0
  #  if category == '8' && ReportsDB.find(:all, :conditions => ["name = ? AND date BETWEEN ? AND ? and category = '8'", name, workDate.to_date.beginning_of_month, workDate.to_date.end_of_month]).size > 0
  #    sv_report.errors.add_to_base("Отчет с данным логином (#{name}) уже был создан в текущем месяце!")
  #  end
    end

        unless sv_report.errors.any?
    # get
    sv_report.date = workDate
    sv_report.name = name
    sv_report.category = category
    sv_report.status = status
    sv_report.weld = weld
    sv_report.diagnosis = diagnosis
    sv_report.department = $department
    sv_report.com_stay_work = com_stay_work
    sv_report.check_hash = check_hash
    sv_report.electro = lines
    sv_report.client_type = client_type
    sv_report.serial_new = work_pay_type
    sv_report.get_money = get_money
    sv_report.com_get_money = com_get_money
    sv_report.get_money_sum = get_money_sum
    sv_report.work_count_type = work_count_type
  sv_report.weather_temp = 1 # only for checked, temporary ...
          sv_report.problem_description = params[:problem_description]
    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

    # save
    sv_report.save
          @current_user = Employee.find(session[:user_id])          
          TrackerSoap.create_problem_report((Report.find sv_report.id), @current_user)

    ####################################################### Получаем последний элемент или элемент редактирования
    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
    else
      report_id = report_id
      isMaterials_save = false
    end


    ######################################################## Сохраняем выполненные работы не из прайса
    # get and save
    if(report_id.to_i>0)
      WorksDoneDB.delWorks(report_id)
    end
    worksUsed = {} # для рассчтеа з/п
    params.each {|key, value|
        key = "#{key}"
        value = "#{value}"

        if(key.index("addwork_") && value.to_i>0)
          work_id = key[8..-1]
          worksUsed[work_id] = value
          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 = workDate
            sv_work.count = value
            sv_work.department = $department
            sv_work.save
          end
        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 = workDate
              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 = workDate
              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]
          allRoles = params[:role]
          

    i=0
    totalProjectSum = 0.0
    for emplId in allEmployees
      if(emplId.to_i>0)
           pay = 0
      if(params["handpay_"+emplId+""])
          pay = params["handpay_"+emplId+""].gsub(",", ".").to_s
          totalProjectSum += params["handpay_"+emplId+""].gsub(",", ".").to_f
          # Сокращение з/п - 10%
        if(category != "12" && category != "14" && params[:id].to_i == 0)
       #   pay = pay.to_f - (pay.to_f*10/100)
          end
          end
        sv_empl = EmployeesDoneDB.new
        sv_empl.report_id = report_id
        sv_empl.empl_id = emplId
        sv_empl.sum = pay
        if allRoles
          sv_empl.role = allRoles[i]
        else
          report = Report.find params[:id]
          sv_empl.role = report.done_employees.size == 0 ? 1 : 0 
        end
        # Определяем имя сотрудника
        sv_empl.empl_name = EmployeesDB.getUserName(emplId)
        sv_empl.date_create_report = workDate
        sv_empl.department = $department
        sv_empl.save
        i+=1
      end
    end

    # Считаем сумму з/п и обновляем базу
    if(totalProjectSum == 0)
      countSum = EmployeesPay.countEmployeeSum(report_id)
    else
      ## Обновляем отчет на общую сумму сделки
      report = ReportsDB.find(report_id)
      report.sum = totalProjectSum
      report.save
    end




    # отладка
    #@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"

else
      errors = ''
      sv_report.errors.each_full{ |msg| errors += msg + "<br/>"}
      redirect_to :action => "addReport"
      flash[:error] = errors
    end
  end


  def stats_old
    # Page info
    @page_title = 'Статистика З/П'
    @currPage = "stats"


    # ФИЛЬТРАЦИЯ
    time = Time.new
    sqlFilterDepartment = "a.department='"+$department+"' && b.status=2"
    if($session['filter_month'] && $session['filter_month'].to_i>0) then sqlFilterMonth = "AND MONTH(b.date)="+$session['filter_month'].to_s+"" else sqlFilterMonth = "AND MONTH(b.date)="+time.month.to_s+"" end
    if($session['filter_day'] && $session['filter_day'].to_i>0) then sqlFilterDay = "AND DAY(b.date)="+$session['filter_day'].to_s else sqlFilterDay = '' end
    if($session['filter_year'] && $session['filter_year'].to_i>0) then sqlFilterYear = "AND YEAR(b.date)="+$session['filter_year'].to_s else sqlFilterYear = "AND YEAR(b.date)="+time.year.to_s+"" end
        # if($session['filter_month'] && $session['filter_month'].to_i>0) then sqlFilterMonth = "AND MONTH(a.date_create_report)="+$session['filter_month'].to_s+"" else sqlFilterMonth = "AND MONTH(a.date_create_report)="+time.month.to_s+"" end
    # if($session['filter_day'] && $session['filter_day'].to_i>0) then sqlFilterDay = "AND DAY(a.date_create_report)="+$session['filter_day'].to_s else sqlFilterDay = '' end
    # if($session['filter_year'] && $session['filter_year'].to_i>0) then sqlFilterYear = "AND YEAR(a.date_create_report)="+$session['filter_year'].to_s else sqlFilterYear = "AND YEAR(a.date_create_report)="+time.year.to_s+"" end
    if($session['filter_empl'] && $session['filter_empl'].to_i>0) then sqlFilterEmployee = "AND a.empl_id="+$session['filter_empl'].to_s else sqlFilterEmployee = '' end
      if($session['filter_worktype'] && $session['filter_worktype'].to_s !="") then sqlFilterWorkType = "AND b.category="+$session['filter_worktype'].to_s else sqlFilterWorkType = '' end

    result = EmployeesDoneDB.find_by_sql("SELECT
                                          a.*,
                                          b.status,
                                          b.date
                                          FROM
                                          done_employees AS a
                                          LEFT JOIN
                                          reports AS b ON(a.report_id=b.id)
                                          WHERE
                                          "+sqlFilterDepartment+"
                                          "+sqlFilterMonth+"
                                          "+sqlFilterDay+"
                                          "+sqlFilterYear+"
                                          "+sqlFilterEmployee+"
                      "+sqlFilterWorkType+"
                                          ORDER BY
                                          a.empl_name ASC
                                         ")

    @employees = [{}]
    @all_pay = 0
  @all_count = 0
    for data in result
        empl_id = data['empl_id'].to_i
        @all_pay+=data['sum'].to_i
      @all_count+=1

        if(@employees[empl_id])
          @employees[empl_id]['pay']+=data['sum'].to_i
          @employees[empl_id]['count']+=1
        else
          @employees[empl_id]={}
          @employees[empl_id]['empl_name']=data['empl_name']
          @employees[empl_id]['empl_id']=data['empl_id']
          @employees[empl_id]['pay']=data['sum'].to_i
          @employees[empl_id]['count']=1
          dates = result.select{|i| i.empl_id == data['empl_id']}.map{|i| i.date}.uniq
          @employees[empl_id]['dates'] =  dates.nil? ? 0 : dates.size
        end
    end
end

  def stats
    @currPage = "stats"
  end

  def stats_excel
    require 'spreadsheet'
    Spreadsheet.client_encoding = 'UTF-8'
    book = Spreadsheet::Workbook.new
    dataBook1 = book.create_worksheet :name => 'book'
    i = 0
      row = dataBook1.row(i)
      row[0] = "Сотрудник"
      row[1] = "Количество заявок"
      row[2] = "Количество смен"
      row[3] = "З/П"
    for e in @d_employees do
      i += 1
      row = dataBook1.row(i)
      row[0] = e.nic
      row[1] = e.time_work
      row[2] = e.shifts
      row[3] = e.sum.to_f.round()
    end
    # Write book
    @file = 'excel-' + rand().to_s + '.xls'
    book.write 'public/'+@file
    render :template => "mod/index_excel", :layout => false
  end

 def reports
    # Page info
  @page_title = 'Статистика подключений'
  @currPage = "reports"
  @finResult = [{}]

    # ФИЛЬТРАЦИЯ
  time = Time.new
  sqlFilterDepartment = "department='"+$department+"' && status=2"
  if($session['filter_year'] && $session['filter_year'].to_i>0) then sqlFilterYear = "AND YEAR(date)="+$session['filter_year'].to_s else sqlFilterYear = "AND YEAR(date)="+time.year.to_s+"" end
    if($session['filter_worktype'] && $session['filter_worktype'].to_s !="") then sqlFilterWorkType = "AND category="+$session['filter_worktype'].to_s else sqlFilterWorkType = '' end

  i=1
  while i<13
    sqlFilterMonth = "AND MONTH(date)="+i.to_s+""
    result = EmployeesDoneDB.find_by_sql("SELECT sum
                        FROM
                        reports
                        WHERE
                        "+sqlFilterDepartment+"
                        "+sqlFilterMonth+"
                        "+sqlFilterYear+"
                                  "+sqlFilterWorkType+"
                       ")

    res = {"SUM"=>0, "COUNT"=>0}
    for data in result
      res['SUM'] += data['sum'].to_i
      res['COUNT'] += 1
    end
      if(i==1)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Январь", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==2)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Февраль", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==3)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Март", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==4)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Апрель", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==5)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Май", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==6)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Июнь", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==7)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Июль", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==8)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Август", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==9)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Сентябрь", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==10)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Октябрь", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==11)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Ноябрь", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end
      if(i==12)
      @finResult += [{"NUM"=>i.to_s, "MONTH"=>"Декабрь", "COUNT"=>res['COUNT'], "SUM"=>res['SUM']}]
    end

    i+=1
  end
  end







  def allreports
    ### PAGE INFO
  @page_title = 'Сводная таблица Корпотдела'
  @currPage = "allreports"

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


  # Получаем список всех работ
  allWorks = WorksDB.getWorks($department)
  @works = WorksDB.getWorks($department, 8)


  # ФИЛЬТРАЦИЯ
  time = Time.new
  sqlFilterDepartment = "a.department='"+$department+"' && a.category IN (8)"
  if($session['filter_month'] && $session['filter_month'].to_i>0) then sqlFilterMonth = "AND MONTH(a.date)="+$session['filter_month'].to_s+"" else sqlFilterMonth = "AND MONTH(a.date)="+time.month.to_s+"" end
  if($session['filter_day'] && $session['filter_day'].to_i>0) then sqlFilterDay = "AND DAY(a.date)="+$session['filter_day'].to_s else sqlFilterDay = '' end
  if($session['filter_year'] && $session['filter_year'].to_i>0) then sqlFilterYear = "AND YEAR(a.date)="+$session['filter_year'].to_s else sqlFilterYear = '' end
  if($session['filter_empl'] && $session['filter_empl'].to_i>0) then sqlFilterEmployee = "AND b.empl_id="+$session['filter_empl'].to_s else sqlFilterEmployee = '' end
  if($session['filter_status'] && $session['filter_status']!='') then sqlFilterStatus = "AND a.status="+$session['filter_status'].to_s else sqlFilterStatus = '' end
    if($session['filter_worktype'] && $session['filter_worktype'].to_s !="") then sqlFilterWorkType = "AND a.category="+$session['filter_worktype'].to_s else sqlFilterWorkType = '' end


    # Пoлучаем инфу
    allData = ReportsDB.find_by_sql("SELECT
                        a.*,
                        b.empl_id,
                        b.sum AS empl_sum,
                        c.work_id,
                        c.count AS work_count,
                        d.employee_name,
                        d.material_name,
                        d.count
                        FROM reports AS a
                        LEFT JOIN done_employees AS b ON(b.report_id=a.id)
                        LEFT JOIN done_works AS c ON(c.report_id=a.id)
                                    LEFT JOIN done_materials AS d ON(d.report_id=a.id)
                        WHERE
                        "+sqlFilterDepartment+"
                        "+sqlFilterMonth+"
                        "+sqlFilterDay+"
                        "+sqlFilterYear+"
                        "+sqlFilterEmployee+"
                        "+sqlFilterStatus+"
                                    "+sqlFilterWorkType+"
                        ORDER BY a.date ASC
                    ")


    @result = {}

    # Делаем возможность сортироки через базу (З.Ы. Идиотская руби) - 1

    i=0
    for res in allData
      report_id = res['id'].to_i
      allWorkSum = WorksDoneDB.getWorksTotalPrice(report_id)
        if(!@result[report_id])
          @result[report_id] = {}
          @result[report_id]['name'] = res['name']
          @result[report_id]['date'] = res['date'].split('-').reverse.join(".")
          @result[report_id]['sum'] = res['sum'].to_i
          @result[report_id]['status'] = res['status']
          @result[report_id]['category'] = CategoryDB.getCategoryInfo(res['category'])['name']
          @result[report_id]['report_id'] = report_id
          @result[report_id]['numsort'] = i
      @result[report_id]['worksum'] = allWorkSum
          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

        # ================================================================ Сохраняем работы
        if(res['work_id']!=nil)
            work_id = res['work_id'].to_i
            if(allWorks[work_id])
                work_name = allWorks[work_id]['name'].to_s
                work_price = allWorks[work_id]['price'].to_s
                work_measure = allWorks[work_id]['measure'].to_s
                work_sum = res['work_count'].to_i*work_price.to_i
                if(@result[report_id]['works'])

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

                  if(check==false)
                    @result[report_id]['works'] += [{'id'=>work_id, 'name'=>work_name, 'count'=>res['work_count'], 'price'=>work_price, 'sum'=>work_sum, 'measure'=>work_measure}]
                  end
                else
                  @result[report_id]['works'] = [{'id'=>work_id, 'name'=>work_name, 'count'=>res['work_count'], 'price'=>work_price, 'sum'=>work_sum, 'measure'=>work_measure}]
                end
            end
        end
        # ================================================================ Сохраняем материалы
        if(@result[report_id]['materials']==nil)
          @result[report_id]['materials']=[{"employee"=>res['employee_name'], "name"=>res['material_name'], "count"=>res['count']}]
        else
          check = false
          @result[report_id]['materials'].each {|wrk| if(wrk.has_value?(res['material_name'])) then check=true end }
          if(check==false)
            @result[report_id]['materials']+=[{"employee"=>res['employee_name'], "name"=>res['material_name'], "count"=>res['count']}]
          end
        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 work_stats
    @page_title = 'Отчет по работам'
    @currPage = "report"
    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
    @works =  (Division.corp.pricelist.map{|i| [(i.name.to_s + " - " + i.rate.to_s), i.id]}+ Work.corp.map{|i| [(i.name.to_s + " - " + i.rate.to_s), i.id]}).sort
    if params[:work]
      @work = params[:work]
    else
      @work = ''
    end
    if params[:login]
      @login = params[:login]
    else
      @login = ''
    end
    @date = (month +'/01/' + year).to_date
    @reports = Report.find_by_sql("SELECT reports.name as name, IF((done_works.pricelist = 1), prlist.name, works.name) as work , SUM(done_works.count) as count, IF((done_works.pricelist = 1), prlist.price, works.rate) as price FROM reports LEFT JOIN done_works ON done_works.report_id = reports.id LEFT JOIN works ON works.id = done_works.work_id LEFT JOIN pricelist.pl_items prlist ON prlist.id = done_works.work_id WHERE reports.category='8' AND reports.department='corp' AND date BETWEEN '#{@date.beginning_of_month.to_date}' AND '#{@date.end_of_month.to_date}'  #{('AND reports.name LIKE "%'+@login +'%"') unless @login.size == 0} #{('AND done_works.work_id='+@work) unless @work.size == 0} GROUP BY reports.name, done_works.work_id")
  end

  # def shifts
  #   @page_title = 'Корп: смены'
  #   if (params[:date])
  #     @date = params[:date].to_date
  #   else
  #     @date = (Date.today)
  #   end
  #   @reports = Report.find_or_create_for_month(@date, 'corp')    
  # end
  
  # def edit_shift
  #   @page_title = 'Корп: редактирование смен'
  #   @report = Report.find(params[:id])
  #   @employees = (Employee.from_division(155).working + @report.done_employees.map{|i| i.employee}).uniq
  # end

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


  private
  def set_users
    @current_user = Employee.find(session[:user_id])
    @boss = (@current_user.accesses.for_department('corp').size > 0 && @current_user.accesses.for_department('corp').first.access_superuser == 1)
  end

   def get_stats
     db_name = Rails.configuration.database_configuration[Rails.env]["database"]

    @page_title = 'Статистика по з/п'
    @employees = Division.corp.employees  # ЗП
   
   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
    # debugger
   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   
  #   debugger
     @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
     @date = @date_from
    @category = params[:filter_worktype]
    @empl =  params[:filter_empl]
    cond = ''; cond2 = ''
    if @category && @category != ''
      cond += "AND r.category = #{@category} "
    end
    if @empl && @empl != '0' && !@empl.blank?
      cond += "AND de.empl_id = #{@empl}"
      cond2 = "empl_id = #{@empl} AND"
    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, 
            shifts.shifts shifts,
            de.time_work time_work,             
            s.sum as oklad
                    FROM pricelist.pl_people e
            LEFT JOIN (SELECT COUNT(de.id) time_work, 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 = 'corp' AND r.dhcp_login 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 #{db_name}.salaries s ON s.division_id = e.division_id AND e.position = s.position AND s.date_from <= '#{@date}'                    
            LEFT JOIN (SELECT COUNT(s.id) as shifts, s.employee_id empl_id FROM #{db_name}.shifts s WHERE type = 'WorkShift' AND department = 'corp' AND shift_date BETWEEN DATE('#{@date.beginning_of_month}') AND DATE('#{@date.end_of_month}') GROUP BY empl_id) shifts ON shifts.empl_id = e.id                                                           
                                                               GROUP BY nic HAVING #{cond2} (de.sum > 0 OR shifts.shifts > 0 OR (e.active = 'Y' AND e.division_id = 155))")
end


end




class EmployeesPay

  def self.countEmployeeSum(report_id)

    priceExtremCall = 0.3
    priceHolidayCall = 0.5


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

    report_info = ReportsDB.find_by_sql("SELECT * FROM reports WHERE id="+report_id.to_s+"")
    notStandartWorkSum = report_info[0]['get_money_sum'].to_s

    category = report_info[0]['category'].to_s
    categoryInfo = CategoryDB.getCategoryInfo(category)

    categoryPay = categoryInfo['price']

    categoryBonus = (category == "13") ? notStandartWorkSum : "0" #categoryInfo['bonus']

    extrem_call = (report_info[0]['diagnosis'].to_s == "1") ? priceExtremCall : 0
    holiday_call = (report_info[0]['weld'].to_s == "1") ? priceHolidayCall : 0

    lines = report_info[0]['electro'].to_i

    # Рассчет з/п

    allWorkSum = WorksDoneDB.getWorksTotalPrice(report_id) + WorksDoneDB.getPricelistWorksTotalPrice(report_id)
    if(category == "12" || category == "14")
      # РинЭд забЭрает 60%
      allWorkSum=allWorkSum.to_i - (allWorkSum.to_i*60/100)
    end


    if(categoryBonus == "0")
        categoryBonus = categoryBonus.to_i
        # Доп работы
        if(category == "12" && extrem_call>0) then categoryBonus += (categoryPay.to_i + allWorkSum.to_i) * extrem_call end
        if(category == "12" && holiday_call>0) then categoryBonus += (categoryPay.to_i + allWorkSum.to_i) * holiday_call end
    else
        # процент или фиксированная сумма
        if(categoryBonus.index("%"))
            categoryBonus = categoryBonus.sub("%", "").to_i
            #categoryBonus = allWorkSum.to_i * (categoryBonus/100)
            categoryBonus = categoryBonus*allWorkSum.to_i/100
        else
            categoryBonus = categoryBonus.to_i
            # Подключение телефонии
          #   if(category == "9") then categoryBonus = (lines.to_i>0) ? categoryBonus * lines : categoryBonus end
             # Доп работы или доп работы при подключении
             if(category == "12" || category == "14")
                categoryBonus += (extrem_call>0) ? categoryPay.to_i*extrem_call : categoryBonus
                categoryBonus += (holiday_call>0) ? categoryPay.to_i*holiday_call : categoryBonus
             end
        end
    end

    totalBonus = categoryPay.to_i + categoryBonus.to_i
    

    totalProjectSum = 0

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


     # if(empty($addbonus)) {
    #       // Доп работы
    #       if($this->workType==62380 && $this->extrem_call>1) $addbonus += ($bonus+$this->workSum)*($this->extrem_call-1);
    #       if($this->workType==62380 && $this->holiday_call>1) $addbonus += ($bonus+$this->workSum)*($this->holiday_call-1);
    #     } else {
    #       if(preg_match("/\%/sim", $addbonus)) {
    #         $addbonus = str_replace('%', '', $addbonus);
    #         $addbonus = $this->workSum*($addbonus/100);
    #       } else {
    #         // Подключение телефонии
    #         if($this->workType==62377) $addbonus = ($_REQUEST['lines']>0) ? $addbonus*$_REQUEST['lines'] : $addbonus;
    #         // Доп работы или доп работы при подключении
    #         if($this->workType==62380 || $this->workType==69424) $addbonus = $bonus*$this->extrem_call+$bonus*$this->holiday_call;
    #       }
    #     }



      # СТОИМОСТИ
      emplPay = (allWorkSum.to_i>0) ? (allWorkSum.to_i + totalBonus.to_i)/employeesCount : totalBonus.to_i/employeesCount
      #emplPay = allWorkSum
      # Сокращение з/п - 10%
      if category == "8"
        emplPay = (allWorkSum.to_i - (allWorkSum.to_i*10/100))/employeesCount + totalBonus.to_i/employeesCount
      elsif(category != "12" && category != "14" && category != "13" && category != "9" && category != "15")
        emplPay = emplPay - (emplPay*10/100)
      end

      sum = emplPay
      # Добавляем проверки если нужны......
      unless category == "14"   #### Если это не доп. работы при подключении  то ответственному платим на 200 р. больше.
      if sum*employeesCount > 300
### Вит попросил убрать 05.06.2013 
#     if res.role == 1
 #       sum =  (((sum*employeesCount) - 200)/employeesCount) + 200
 #     else
#        sum =  (((sum*employeesCount) - 200)/employeesCount)
 #     end
      else
      if res.role == 1
        sum =  sum + ((sum*employeesCount) * 10/100)
      else
        sum =  sum - (((sum*employeesCount) * 10/100)/(employeesCount -1))
      end
      end
      end
      sum = sprintf("%.2f", sum).to_f      # Округление (2 знака после запятой)
      # Доля участия
      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