# -*- coding: utf-8 -*-
class ConstrWorksController < ApplicationController
    $department = 'construction'
    $department_id = '676'
  before_filter :isAuthorization
  before_filter :set_var

  def index
#    @reports = Report.for_department('construction').in_period(@date_from, @date_to).with_category(params[:filter_worktype]).with_status(params[:filter_status]).with_empl(params[:filter_empl]).with_constr_manager(params[:manager]).with_constr_engineer(params[:engineer]).find(:all, :include => [:done_materials, :report_factors])
#    @employee_filter = Report.for_department('construction').in_period(@date - 1.year, @date).map{|i| i.done_employees}.flatten.map{|c| [c.empl_name.downcase, c.empl_id]}.uniq.sort
    
    @employee_filter = Employee.working.flatten.map{|c| [c.nic.downcase, c.id]}.uniq.sort
    if params[:date_from].to_s != ''
      @date_from = params[:date_from].to_date
      @date_to = params[:date_to].to_date      
    else # Если не задано, то за текущий месяц
      @date_from = @date.beginning_of_month
      @date_to = @date.end_of_month
    end

    
    order = []
    if params[:sname]
      order << "reports.name " + params[:sname]
    end
    
    if params[:sdate]
      order << "reports.date " + params[:sdate]
    end
    if params[:sid]
      order << "reports.id " + params[:sid]
    end
    if params[:sempl]
      order << "done_employees.empl_name " + params[:sempl]
    end


    if params[:stypework]
      order << "reports.category " + params[:stypework]
    end
    if params[:smaterial]
      order << "done_materials.material_name " + params[:smaterial]
    end
    if params[:swork]
      order << "(reports.total_sum_works * 1)" + params[:swork]
    end
    if params[:smatbuy]
      order << "(done_materials.purchase_price * 1)" + params[:smatbuy]
    end
    if params[:smatsell]
      order << "(reports.total_sum_materials * 1) " + params[:smatsell]
    end
    if params[:seditor]
      order << "reports.update_user_id " + params[:seditor]
    end
    if params[:ssum]
      order << "reports.sum " + params[:ssum]
    end
    if params[:sstatus]
      order << "reports.status " + params[:sstatus]
    end
    if params[:cr_at]
      order << "reports.created_at " + params[:cr_at]
    end
    if params[:up_at]
      order << "reports.tracker_date_works " + params[:up_at]
    end
    if order.empty?
      ord = "reports.date, reports.name"
    else
      ord = order.join(",")
    end

    if params[:filter_empl] && params[:filter_empl] != ""
      @filter_empl =  params[:filter_empl].to_i
    elsif !(@boss || @disp) && params[:filter_empl] != ""
      @filter_empl = @current_user.id
      
    else
      @filter_empl = ''
    end

 
    cond = []
    cond << "(DATE(reports.date) >= ('#{@date_from}') AND DATE(reports.date) <= ('#{@date_to}'))" if @date_from
    cond << "reports.closed_at IS NULL"
    cond << "reports.department = 'construction'"
       if params[:factor].to_a.size > 0
    cond << "report_factors.factor_id IN (#{params[:factor].keys.join(', ')})"
    end
    
    cond << %(reports.name LIKE "#{params[:cl_login]}") if params[:cl_login].present?
    if params[:filter_status].present? && params[:filter_status].to_i != 5
      if params[:filter_status].to_i == 0
        cond << "(reports.status = 0 OR status IS NULL)"
      else
        cond << "reports.status = #{params[:filter_status]}"
      end
    end
    cond << "done_employees.empl_id = #{@filter_empl}" if @filter_empl.present?
    cond << "reports.category = #{params[:filter_worktype]}" if params[:filter_worktype].to_i != 0
    cond << "reports.total_sum_materials > 0" if params[:sale_material].to_s.to_bool == true        
    cond << "reports.total_sum_works > 0" if params[:only_with_works].to_s.to_bool == true   
    cond = cond.join(" AND ")
    
    @reports = Report.find_by_sql("
SELECT 
 reports.* , 
 (SELECT 
  GROUP_CONCAT(
    CONCAT('<li/>', 
           empl_name,  
           ' (', 
           REPLACE(FORMAT(sum, 0), ',', ''), 
           '<font class=currency>р.</font>)', 
           IF(reports.zp_fine_sum > 0, (CONCAT('<div class=red>', 'Штраф: ', FORMAT(reports.zp_fine_sum, 0), '</div>')), ''))) empls
           
  FROM 
          done_employees 
  WHERE 
           report_id= reports.id
  ) empls,
 GROUP_CONCAT(DISTINCT CONCAT('<li>', p.name, ' - ', dw.count, '</li>') SEPARATOR ' ') as wrks,  
  (SELECT
    GROUP_CONCAT(
    CONCAT(
           material_name, 
           IF(count > 1, CONCAT(': ', count, ' шт.'), ''), 
           IF(price > 0, CONCAT(' (цена-', price, ') '), ''), 
           IF(bonus_sale > 0, CONCAT(' (бонус-', bonus_sale, ') '), '')
          )
SEPARATOR '<br>')
 FROM 
          done_materials dm
  WHERE 
           report_id= reports.id
) mat_view,
(SELECT 
 SUM(purchase_price*count)
 FROM 
          done_materials dm
  WHERE 
           report_id= reports.id
) purchase_sum,
    category.name cat_name,
    IFNULL((SELECT e.nic FROM #{Employee.connection.current_database}.#{Employee.table_name} e WHERE e.id = reports.update_user_id), '') last_user_update
FROM 
 reports 
LEFT JOIN
 done_materials ON done_materials.report_id = reports.id
LEFT JOIN
 done_employees ON done_employees.report_id = reports.id
LEFT JOIN
 done_works dw ON dw.report_id = reports.id
LEFT JOIN
 pricelist.pl_items p ON dw.work_id = p.id
LEFT JOIN 
 category ON category.id = reports.category
LEFT JOIN 
 report_factors ON report_factors.report_id = reports.id
WHERE #{cond} 
GROUP BY reports.id
ORDER BY #{ord}
LIMIT 3000
")

    
    @wage = @current_user.done_employees.in_month(@date).sum(:sum)
    @duty =  PeriodZp.dept_on_date(Date.today, @current_user.id)
end

  
  def new
    @report = Report.new(:department => 'construction', :date => Time.now, :status => 0, :category => Category.construction.first.id)
    @report.done_employees.build(:employee => @current_user, :date_create_report => Date.today)
    @report.disp_wage = 0
    @pricelist = Pricelist.with_category(@report.category)
   # @pricelist = (@top_works + (@pricelist.sort_by{|i| [i.section.ordered, i.section_id, i.ordered]} - @top_works)).compact
    @factors = @report.report_factors
    @employees = Employee.working
  end

  def create
    @report = Report.new(params[:report])
    @report.department = 'construction'
    @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 construction_index_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
        format.xml  { head :ok }
      else
        @pricelist = Pricelist.with_category(@report.category)
#        @pricelist = (@top_works + (@pricelist.sort_by{|i| [i.section.ordered, i.section_id, i.ordered]} - @top_works)).compact   
        @factors = @report.report_factors

        format.html { render :action => "new", :report => @report }
      end
    end
  end


  def edit
    @report = Report.find params[:id]
    @pricelist = Pricelist.with_category(@report.category)
  #  @pricelist = (@top_works + (@pricelist.sort_by{|i| [i.section.ordered, i.section_id, i.ordered]} - @top_works)).compact   
    @factors = @report.report_factors    
    @employees = (Employee.working + @report.done_employees.map{|i| i.employee}).uniq
  end

  def update
    @report = Report.find(params[:id])
    respond_to do |format|
      @report.attributes = params[:report]
      if @boss || @disp || @report.status.to_i == 0 || (@disp_fast && @report.weld.to_i == 1) || (@disp_fast && @report.weld.to_i != 1 && !@report.status_changed?)
        if @report.save
        flash[:notice] = 'Запись обновлена'
        format.html { redirect_to construction_index_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
        format.xml  { head :ok }
      else
    @factors = @report.report_factors    
    @employees = (Employee.working + @report.done_employees.map{|i| i.employee}).uniq

        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 #|| @report.status == 0
      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 constr_works_path(:filter_month => @report.date.to_date.month, :filter_year => @report.date.to_date.year)}
    end
  end



  def update_sum
    if params[:id]
      @report = Report.find params[:id]
    else
      @report = Report.new
      @report.department = 'construction'
    end
    @report.attributes = params[:report]
    @report.calculate
    @factors = @report.report_factors
    render :update do |page|
      page.replace_html  'sums', :partial => 'sum', :locals =>{:report => @report}
      page.replace_html  'itog', :partial => 'itog', :locals =>{:report => @report}
      page.call "change_all_hand_sum"
    end
  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.for_department("construction").in_month(@date).with_status(params[:filter_status]).with_category(params[:filter_worktype]).find(:all, :include => [:employees])
    @d_employees = @reports.map{|i| i.employees}.flatten.uniq.compact.sort_by{ |i| i.nic.downcase}
    @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 get_stats
    @page_title = 'Статистика по з/п'
    @employees = Employee.working
    @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
    WorkShift.calculate_shifts(@date)
    @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
    cond += " AND status = #{params[:filter_status]}"  if params[:filter_status]
    if @empl && @empl != ''
      cond += "AND de.empl_id = #{@empl}"
      cond2 +="id = #{@empl} AND "
    end    
      work_days = WorkingDay.find(:first, :conditions => {:month => @date.beginning_of_month})
    if work_days
      @working_days = work_days.working_days.to_i
    else
      @working_days = 1
    end
    @d_employees = DoneEmployee.find_by_sql(
    "SELECT e.nic as nic, 
            e.id id, 
            e.show,
            s.static_sum static_sum,
            p.name position_name,
            (s.sum / #{@working_days}) shift_wage,
            (SELECT COUNT(id) FROM shifts WHERE type = 'WorkShift' AND employee_id = e.id AND status = 1 AND DATE(shift_date) BETWEEN DATE('#{@date.beginning_of_month}') AND DATE('#{@date.end_of_month}') ) shifts_size,
            de.sum as sum,
            de.report_size, 
            s.sum as oklad
                                                FROM people e
            LEFT JOIN (SELECT de.empl_id empl_id, COUNT(r.id) report_size, SUM(de.sum) sum FROM done_employees de LEFT JOIN reports r ON de.report_id = r.id AND closed_at IS NULL WHERE r.department = 'construction' 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 positions p ON p.id = e.position_id               
            LEFT JOIN salaries s ON e.position_id = s.position_id AND s.date_from <= '#{@date}'
               
                                                               GROUP BY nic HAVING #{cond2} (de.sum > 0 OR e.show is TRUE)")
  end
    

    def report_works
    if (params[:date_from] && params[:date_to])
       @date_from = params[:date_from].to_date
       @date_to = params[:date_to].to_date
     else
       @date_from = Date.today.beginning_of_month
       @date_to = Date.today.end_of_month
     end
    cond = ["r.department = 'construction'"]

    @categories = Category.construction
    if params[:categories]
       @cats =  params[:categories].to_a
       sel_cats = "AND category IN (#{@cats.join(', ')})"
     else
       @cats = nil
       sel_cats = ''
     end

    if params[:only_with_status_2] &&  params[:only_with_status_2] == "true"
      cond << "IFNULL(r.status, 0) = 2 "
    else
      cond << "IFNULL(r.status, 0) != 1"
    end
    cond = cond.join(" AND ")
    @reports = Report.find_by_sql(["SELECT * FROM reports r WHERE #{cond}  AND r.closed_at IS null #{sel_cats} AND DATE(r.date) BETWEEN DATE(?) AND DATE(?)", @date_from, @date_to])
    @works = DoneWork.find(:all, :include =>[:report, :work], :conditions =>["report_id IN (?)", @reports.map{|i| i.id}])
    @materials = DoneMaterial.find(:all, :include =>[:report], :conditions =>["report_id IN (?)", @reports.map{|i| i.id}])
    if params[:other_works] && params[:other_works] == "true"
      @other_works = @reports.select{|i| i.get_money_sum.to_f > 0} #.map{|i| [i.com_conflicts, i.get_money_sum]}
    else
      @other_works = []
    end
  end




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



  private

  def set_var
    @date = Date.today
    @employee = Employee.find(:all, :conditions => "division_id = 676")
    Employee.current_user = Employee.find(session[:user_id]) unless session[:user_id].nil?
    $department = 'construction'
    $department_id = '676'
    cookies[:sys_dep] = { :value => $department }
    @current_user = Employee.current_user
    @boss = (@current_user.accesses.for_department('construction').size > 0 && @current_user.accesses.for_department('construction').first.access_superuser == 1)
     if (params[:date_from] && params[:date_to])
       @date_from = params[:date_from].to_date
       @date_to = params[:date_to].to_date
     else
       @date_from = Date.today.beginning_of_month
       @date_to = Date.today.end_of_month
     end

  end



end