# -*- coding: utf-8 -*-
class Mod2Controller < ApplicationController
    $department = 'mod'
    $department_id = '241'
  before_filter :isAuthorization
  before_filter :set_var
  before_filter :get_reports, :only => [:index, :index_excel]
  before_filter :get_stats, :only => [:stats, :stats_excel]
  after_filter :get_pricelist, :only =>[:edit]
  def new
    # Parameters: {"com_stay_work"=>"повесил ящик", "t_id"=>"214460", "t_ct"=>"2", "t_empl"=>"alexo", "t_login"=>"mpol5"}
    @page_title = 'Модернизация: новый отчет'
    @currPage = "new"   
    category = nil
    if params[:t_id]
      t_id = params[:t_id].to_i
      user = Employee.find_by_login params[:t_empl]
      if params[:t_date]
        t_date = Time.at(params[:t_date].to_i)
      else
        t_date = Time.now
      end   
      if params[:t_ct] && (Category.exists? params[:t_ct])
        category = params[:t_ct] 
      else
        category = Category.active.mod.first.id
      end
      description = Iconv.conv("utf-8", "KOI8-R", @template.strip_tags(params[:com_stay_work]))

      if @report=Report.find(:first, :conditions =>"DATE(date) = '#{t_date.to_date}' AND request_id = #{t_id}")
        redirect_to edit_mod2_path(:id => @report.id, :com_stay_work => description), :notice => "Отчет с данным id уже существует. " # :action => 'edit', :id => @report.id, :com_stay_work => description
      else
        @report = Report.new(:department => 'mod', :date => t_date, :name => params[:t_login], :request_id => params[:t_id], :category => category, :com_stay_work => description, :dhcp_login => 'new', :status => 0)
        @report.done_employees.build(:employee => user, :time_addwork => '', :time_stopwork =>'')
      end
   
    elsif @boss # UNCOMMENT
   # else
      @report = Report.new(:department => 'mod', :date => Time.now, :category => Category.active.mod.first.id, :dhcp_login => 'new', :status => 0)
      @report.done_employees.build(:employee => @current_user, :time_addwork => '', :time_stopwork =>'')
    else  # UNCOMMENT
      flash[:error] = "Новые отчеты следует создавать из реквеста!"
      redirect_to :action => 'index'
    end
    get_pricelist

  end

  def create
    @report = Report.new(params[:report])

    @report.save
    if @report.new_record?
      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 mod2_index_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
        format.xml  { head :ok }
      else
        get_pricelist
        format.html { render :action => "new", :report => @report }
      end
    end
  end

 def edit
   @report = Report.find(params[:id])
   @report.com_stay_work = params[:com_stay_work] if params[:com_stay_work]
   get_pricelist
 end

  def update
    @report = Report.find(params[:id])
    respond_to do |format|
      if @boss || @report.status != 2
        if @report.update_attributes(params[:report])
          if params[:docs]
            params[:docs].each { |doc| @report.topology_files.create(:doc => doc)}
          end
        flash[:notice] = 'Запись обновлена'
        format.html { redirect_to mod2_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 }
          get_pricelist
          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 || @report.status != 2
      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 mod2_index_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 = 'mod'
    end
    @report.category =  params[:type]
    
    @pricelist = Division.mod.pricelist
    @top_works = (@report.done_works.map{|i| i.work} + DoneWork.top_works_for_mod(@report)).uniq
    @pricelist = @top_works + (@pricelist - @top_works)
#    debugger
    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 index
  end

    def index_excel
    require 'spreadsheet'
    Spreadsheet.client_encoding = 'UTF-8'
    book = Spreadsheet::Workbook.new
    dataBook1 = book.create_worksheet :name => 'book'
    #  debugger
    i = 0
  row = dataBook1.row(i)
      row[0] = "ID"
      row[1] = "Логин"
      row[2] = "Сотрудник"
      row[3] = "Дата"
      row[4] = "Сумма"
      row[5] = "Поквестовка"
      row[6] = "Тип работ"
      row[7] = "Работы"
      row[8] = "Материалы"
      row[9] = "Коммент"
      row[10] = "Последняя правка"
      row[11] = "Request"
      row[12] = "Выходной"    
      row[13] = "Статус"
    for report in @reports do
      i += 1
      row = dataBook1.row(i)
      row[0] = report.id
      row[1] = report.name
      row[2] = report.empls.to_s.gsub("<li>", "").gsub("</li>", "\r").gsub("<font class=currency>", "").gsub("</font>", "")
      row[3] = report.date.to_date
      row[4] = report.sum.to_f
      row[5] = report.request_bonus_sum.to_i
      row[6] = report.cat_n
      row[7] = (report.wrks.to_s.gsub("<li>", "").gsub("</li>", "\r") + report.not_standart_wrks.to_s.gsub("<li>", "").gsub("</li>", "\r"))
      row[8] = report.mats.to_s.gsub("<li>", "").gsub("</li>", "\r")
      row[9] = report.comment
      row[10] = report.ed_name
      row[11] = report.request_id
      row[12] = report.weather_temp
      row[13] = report.status_name
    end
    # Write book
    @file = 'excel-' + rand().to_s + '.xls'
    book.write 'public/'+@file
    render :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 update_sum
    if params[:id]
      @report = Report.find params[:id]
    else
      @report = Report.new
      @report.department = 'mod'
    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
   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] = "За смены (будние) "
      row[4] = "Кол-во смен (выходные) "
      row[5] = "За смены (выходные) "
      row[6] = "Количество отработанных часов"
      row[7] = "Количество часов допработ"
      row[8] = "Количество часов простоя"
      row[9] = "По отчетам"
      row[10] = "Итого"
    for e in @d_employees do
      i += 1
      row = dataBook1.row(i)
      row[0] = e.nic
      row[1] = e.oklad
      row[2] = e.shifts
      row[3] = c= ((e.oklad.to_f / @working_days).round * e.shifts.to_i)
      row[4] = e.shifts_weekend
      row[5] = d=(e.oklad.to_f / @working_days).round * e.shifts_weekend.to_i * 2
      row[6] = e.time_work
      row[7] = e.time_dop_work
      row[8] = e.prostoy
      row[9] = e.sum.to_f.round()
      row[10] = (e.sum.to_f + c + d).round()
  
    end
    # Write book
    @file = 'excel-' + rand().to_s + '.xls'
    book.write 'public/'+@file
    render :template => "mod/index_excel", :layout => false
  end


  def confirm
    @report = Report.new(params[:report])
    @report.dhcp_login = 'new'
    @problems = (params[:sanps] == '1') #   # Отчет с (без) проблем
    @report.calculate
    render :update do |page|
      page.replace_html  'yield2', :partial => 'confirm', :locals =>{:report => @report}
      page.call 'modal_window', 'window2'
    end
  end


  def topologies
    if params[:date_from].to_s != ""
      @date_from = params[:date_from].to_date
    else
      @date_from = Date.today.beginning_of_month
    end
    if params[:date_to].to_s != ""
      @date_to = params[:date_to].to_date
    else
      @date_to = Date.today.end_of_month
    end

    cond = []
    cond << "r.department = 'mod' AND r.closed_at IS NULL"
    cond << "r.category = #{params[:filter_worktype]}" if params[:filter_worktype].to_s != ''
    cond << "DATE(r.date) BETWEEN DATE('#{@date_from}') AND DATE('#{@date_to}')"
    @having = "tps_size > 0" if params[:topology_files].to_s == '2'
    @having = "tps_size = 0" if params[:topology_files].to_s == '1'
    if @having
      @having = "HAVING "+@having
    end

    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 COUNT(tps.id) tps_size, GROUP_CONCAT(DISTINCT CONCAT('<li>', done_employees.empl_name,  '</li>') SEPARATOR ' ') as empls,
                                   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.com_imperfection, r.com_stay_work as comment, ed.login as ed_name, r.topology_map topology_map, r.request_bonus_sum, r.topology_problem_tracker_id
                               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 category ct ON ct.id = r.category 
                                    LEFT JOIN topology_files tps ON r.id = tps.report_id 
                                         WHERE  #{cond}
                                               GROUP BY r.id #{@having} ORDER BY r.date, r.name")
      

  end


private

  def get_pricelist
    if @report      
      @pricelist = Division.mod.pricelist.with_category(@report.category)
      @top_works = (@report.done_works.map{|i| i.work} + DoneWork.top_works_for_mod(@report)).uniq      
      @pricelist = @top_works + (@pricelist - @top_works).flatten.compact.uniq            
      @categories = (Category.active.mod + [@report.categor]).uniq    
    end
  end


  def get_reports
    @employees = Division.mod.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 || @topologist
      @filter_empl =  params[:filter_empl]
      @wage = 0
    else
      @filter_empl = @current_user.id
      @wage = @current_user.done_employees.mod.in_month(@date).sum(:sum)
    end

   
    cond = []
    cond << "r.department = 'mod' 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>', #{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,
                                   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.com_imperfection, r.com_stay_work as comment, ed.login as ed_name, r.topology_map topology_map, r.request_bonus_sum, r.topology_problem_tracker_id
                               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  dhcp_login = 'new' AND 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 get_stats
      db_name = Rails.configuration.database_configuration[Rails.env]["database"]

    @page_title = 'Статистика по з/п'
    @employees = Employee.working.from_division(241)  # ЗП    
    @bonus = []
    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
    work_days = WorkingDay.find(:first, :conditions => {:month => @date.beginning_of_month})
    if work_days
      @working_days = work_days.working_days.to_i
      @weekends = work_days.weekends
    else
      @working_days = 1
      @weekends = 1
    end
    @category = params[:filter_worktype]
    if @boss
      @empl =  params[:filter_empl]
    else
      @empl = @current_user.id
    end
    cond = ''; cond2 = '';
    if @category && @category != ''
      cond += "AND r.category = #{@category} "
    end
    if @empl && @empl != ''
      cond += "AND de.empl_id = #{@empl}"
      cond2 +="id = #{@empl} AND "
    end    
   

    if @date >= '01.12.2013'.to_date                 
      @d_employees = DoneEmployee.find_by_sql(
      "SELECT e.login as nic, 
               e.id id, 
               e.division_id,
               e.active,
               de.sum as sum, 
               de.bonus as bonus,
               de.request_uniq request_uniq,
               de.r_size r_size,
               CONVERT(de.time_work, UNSIGNED INTEGER) as time_work, 
               CONVERT(de.ented_intime, UNSIGNED INTEGER) as time_dop_work, 
               '0' as prostoy, 
               s.sum as oklad, shifts.shifts, shifts.shifts_weekend 
                   FROM pricelist.pl_people e
                        LEFT JOIN (SELECT COUNT(de.empl_id) r_size, GROUP_CONCAT(r.request_id  SEPARATOR ', ') request_uniq, SUM(de.time_work) time_work, SUM((r.request_bonus_sum * de.time_work)/r.work_time) bonus, SUM(de.ented_intime) ented_intime, 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 AND r.closed_at IS NULL WHERE r.status = '2' AND r.department = 'mod' AND r.dhcp_login = 'new' 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.id = (SELECT id FROM salaries ss WHERE ss.division_id = e.division_id AND e.position = ss.position AND ss.date_from <= '#{@date}' ORDER BY ss.date_from DESC LIMIT 1)
                        LEFT JOIN (SELECT SUM(IF((DAY(s.shift_date) NOT IN (#{@weekends} )), 1, 0)) as shifts, SUM(IF((DAY(s.shift_date) IN (#{@weekends} )), 1, 0)) as shifts_weekend, s.employee_id empl_id FROM #{db_name}.shifts s WHERE type = 'WorkShift' AND department = 'mod' 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 shifts.shifts_weekend > 0 OR (e.active = 'Y' AND e.division_id = 241))")
    
    else

    @d_employees = DoneEmployee.find_by_sql(
    "SELECT e.login as nic, 
            e.id id, 
            e.division_id,
            e.active,
            de.sum as sum, 
            CONVERT(de.time_work, UNSIGNED INTEGER) as time_work, 
            CONVERT(de.time_addwork, UNSIGNED INTEGER) as time_dop_work, 
            CONVERT(de.time_stopwork, UNSIGNED INTEGER) as prostoy, 
            s.sum as oklad,
            shifts.shifts, shifts.shifts_weekend 
                                                FROM pricelist.pl_people e
            LEFT JOIN (SELECT SUM(de.time_work) time_work, SUM(de.time_stopwork) time_stopwork, SUM(de.time_addwork) time_addwork, 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 AND closed_at IS NULL WHERE r.status = '2' AND r.department = 'mod' 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 SUM(IF((DAY(s.shift_date) NOT IN (#{@weekends} )), 1, 0)) as shifts, SUM(IF((DAY(s.shift_date) IN (#{@weekends} )), 1, 0)) as shifts_weekend, s.employee_id empl_id FROM #{db_name}.shifts s WHERE type = 'WorkShift' AND department = 'mod' 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 shifts.shifts_weekend > 0 OR (e.active = 'Y' AND e.division_id = 241))")
  end
    
  end



  def set_var
    Employee.current_user = Employee.find(session[:user_id]) unless session[:user_id].nil?
    $department = 'mod2'
    $department_id = '241'
    cookies[:sys_dep] = { :value => $department }
    @current_user = Employee.current_user
    if (accesses = @current_user.accesses.for_department('mod')).size > 0     
      @boss = true if accesses.first.access_superuser == 1
      @superu = true if accesses.first.access_admin == 1
      @topologist = true if accesses.first.access_delete == 1
    end
  end


end