# -*- coding: utf-8 -*- class HandlingController < ApplicationController before_filter :isAuthorization # around_filter :isAccessSuper BOM = "\377\376" #Byte Order Mark def new_ring empl = Employee.find_by_login 'alexo' ret = Ring.new(Report.last, empl) render :text => "Ответ: " + ret end def save_pricelist_to_csv filename = "Report #{Russian::strftime(Date.today)}.csv" require "fastercsv" @date_from = '01.01.2019'.to_date @date_to ='31.12.2019'.to_date cond = [] cond << "r.department = 'alp' AND r.closed_at IS NULL" cond = cond.join(" AND ") ActiveRecord::Base.connection.execute("SET group_concat_max_len = 2048;") @reports = Report.find_by_sql( "SELECT GROUP_CONCAT(DISTINCT CONCAT(done_employees.empl_name, ' - ', done_employees.sum) SEPARATOR '\n') as empls, GROUP_CONCAT(DISTINCT CONCAT(dm.material_name, ' - ', dm.count) SEPARATOR '\n') as mats, GROUP_CONCAT(DISTINCT CONCAT(IFNULL(w.name, p.name), ' - ', dw.count) SEPARATOR '\n') as wrks, IF((r.get_money_sum = 0 OR r.get_money_sum = NULL), '', CONCAT('НЕСТАНДАРТНАЯ РАБОТА: ', r.com_conflicts, ' - ', r.get_money_sum, '\n') ) as not_standart_wrks, IF((r.request_id IS NULL), '', IF((r.request_closed IS TRUE), 'Закрыт', 'Открыт')) as req_status, r.id as id, r.request_id, r.name, r.date, r.sum, r.status, ct.name as cat_n, r.weather_temp, r.weather_rain, r.com_imperfection, r.com_stay_work as comment, ed.login as ed_name, r.topology_map topology_map, r.request_bonus_sum, r.login_from, r.get_money_sum, r.com_conflicts FROM reports r LEFT JOIN done_employees on done_employees.report_id = r.id LEFT JOIN pricelist.pl_people ed on r.update_user_id = ed.id LEFT JOIN done_materials dm ON dm.report_id = r.id LEFT JOIN category ct ON ct.id = r.category LEFT JOIN done_works dw ON dw.report_id = r.id LEFT JOIN works AS w ON dw.work_id=w.id && dw.pricelist=0 LEFT JOIN pricelist.pl_items AS p ON dw.work_id=p.id && dw.pricelist=1 WHERE DATE(r.date) BETWEEN DATE('#{@date_from}') AND DATE('#{@date_to}') AND #{cond} GROUP BY r.id ORDER BY r.request_id, r.date,r.name") @mats = @reports.map{|i| i.done_materials.map{|i| i.material_name}}.flatten.uniq.sort @people = @reports.map{|i| i.done_employees.map{|i| i.empl_name}}.flatten.uniq.sort @works = @reports.map{|i| i.done_works.map{|i| i.work.name}}.flatten.uniq.sort csv_string = FasterCSV.generate do |csv| a1 = ['','','','','','','','','','','','','',] a1 << @people a1 << @works a1 << "Нестандартная работа" a1 << @mats csv << a1.flatten @reports.each do |rep| # empls = rep.employees # rep.empls, # rep.wrks, # rep.not_standart_wrks, # @template.materials_for_index(rep), st = [rep.id, rep.login_from, rep.name, rep.date.to_datetime.strftime("%d.%m.%Y"), rep.sum.to_i, rep.request_bonus_sum.to_i, rep.cat_n, rep.comment, rep.ed_name, @template.request_link(rep.request_id), rep.req_status, @template.y_n(rep.weather_temp.to_i), @template.y_n(rep.weather_rain.to_i), ] for e in @people do if rep.done_employees.map{|i| i.empl_name}.flatten.include? e st << 1 else st << '' end end for e in @works do dw = rep.done_works.select{|i| i.work.name == e} if dw.size > 0 st << dw.sum{|i| i.count.to_f} else st << '' end end if rep.get_money_sum.to_f > 0 st << rep.com_conflicts else st << '' end for e in @mats do dw = rep.done_materials.select{|i| i.material_name == e} if dw.size > 0 st << dw.sum{|i| i.count.to_f} else st << '' end end csv << st.flatten end end csv_string content = BOM + Iconv.conv("utf-16le", "utf-8", csv_string) send_data content, :only_path => false, :filename => filename end def save_pricelist_to_csv2 require "fastercsv" @price = Division.alp.pricelist filename = "Report #{Russian::strftime(Date.today)}.csv" title = "Прайс Альпы" csv_string = FasterCSV.generate do |csv| ar = Array.new(2, "") ar[0] = title; csv << ar csv << [ "ID", "Раздел", "Наименование", "З/П", "Единица измерения", "Стоимость для клиента" ] @price.each do |obj| csv << [obj.id, obj.section_name, obj.name, obj.rate, obj.measure_name, obj.price ] end end csv_string content = BOM + Iconv.conv("utf-16le", "utf-8", csv_string) send_data content, :only_path => false, :filename => filename end def upload_csv end def create pl = [] all_rows = 0 bad = 0 tracker = {} periods = [] require 'fastercsv' require 'iconv' #@converter = Iconv.new('UTF-8', 'Windows-1251') @text = params[:file].read @tr_ids = [] FasterCSV.parse(@text, :col_sep => ',') do |row| case params[:t_type].to_i when 1 # Остатки all_rows += 1 empl = Employee.find_by_nic row[0] sum = row[1].gsub(",", ".").to_f pays = Payment.find(:all, :conditions => "employee_id = #{empl.id}", :order => "period_zp_id DESC") for pay in pays do if pay.period.closed == true pay.period.update_attributes(:closed => false) pay.reload end if pay.sum < sum if pay.destroy_with_check sum -= pay.sum end else if pay.destroy_with_check sum_new = pay.sum - sum p=Payment.new(:sum => sum_new, :period_zp_id => pay.period_zp_id, :employee_id => pay.employee_id, :description => pay.description, :operation_date => pay.operation_date) p.save_with_check sum = 0 end end pl << empl.nic if sum == 0 break if sum == 0 end when 2 # Начисления all_rows += 1 empl = Employee.find_by_nic row[0] sum = row[1].gsub(",", ".").to_f period = Period.find row[2] period_zp = PeriodZp.for_period_and_employee(period.id, empl.id) payr = Payroll.new(:sum => sum, :employee_id => empl.id, :period_zp => period_zp, :zp_type_id => 2, :description => "Автоматически созданное начисление") if payr.save_with_check pl << payr.id end when 3 # Выплаты if all_rows == 0 @dates = row else c = 0 row.each do |r| if c == 0 @empl = Employee.find_by_nic r else date = @dates[c].to_date # debugger sum = r.to_s.gsub(",", ".").to_f if sum > 0 period = Period.for_date(date) period_zp = PeriodZp.for_period_and_employee(period.id, @empl.id) p=Payment.new(:sum => sum, :period_zp_id =>period_zp.id, :employee_id => @empl.id, :description => "Автоматически созданная выплата", :operation_date => date) if p.save_with_check pl << p.id end end end c += 1 end end all_rows += 1 end end render :text => "Всего: " + (all_rows).to_s + " Хорошие: " + pl.join(", ")# + " Плохие: " + bad.to_s end end