# -*- coding: utf-8 -*- class ReportController < ApplicationController BOM = "\377\376" #Byte Order Mark before_filter :isAuthorization before_filter :set_var, :only => [:select_dates, :all_works_for_all_departments, :all_works_for_all_departments, :categories, :transport, :index] def update if params[:id] && (Report.exists? params[:id]) @report = Report.find params[:id] @report.update_attributes(:status => params[:status]) end render :update do |page| if @report.errors.any? page.alert("Возникли проблемы!") end end end def change_materials @employees = [] if params[:employees] || (params[:department] && params[:department] == 'alp') if params[:employees] && params[:employees].include?("reception") @employees = [Employee.new(:login => "reception")] elsif params[:employees] && params[:employees].include?("btul2") @employees = [Employee.new(:login => "btul2")] end empls = params[:employees].to_a.split(',').flatten.uniq - ['btul2'] - ['reception'] if empls.size > 0 @employees = (Employee.find empls) end @category = Category.find params[:category] if params[:department] && params[:department] == 'alp' @employees = (Employee.find_by_login 'Narbelet').to_a end if params[:report_date] && !params[:report_date].blank? @report_date = params[:report_date].to_date else @report_date = Date.today end end render :update do |page| page.call "clearMaterials" for empl in @employees do page.insert_html :bottom, 'materialsArea', :partial => '/shared/materials', :locals =>{:employee => empl, :category => @category} end # page.insert_html :bottom, 'materialsArea', :partial => '/shared/materials', :collection => @employees, :as => :employee, :locals =>{:category => @category} page.call "check_category" page.call "show_hide_price_mats" if @category && @category.department == 'private' # Для частников заполняем материалы из предзаказа реквеста page.call "check_preorder_mats" end end end def all_works_for_all_departments cond = [] if params[:department] && params[:department] != '' @dep = params[:department] cond << "r.department = '#{@dep}'" end if params[:date_from] @date_from = params[:date_from].to_date else @date_from = Date.today end if params[:date_to] @date_to = params[:date_to].to_date else @date_to = Date.today end if params[:category_ids] cond << "r.category IN (#{params[:category_ids].join(", ")})" end cond << "(DATE(r.date) BETWEEN DATE('#{@date_from}') AND DATE('#{@date_to}'))" cond = cond.join(" AND ") @works = DoneWork.find_by_sql("(SELECT GROUP_CONCAT(r.id SEPARATOR ', ') r_id, r.department r_department, r.name r_name, r.request_id request_id, CASE lgrt.conntype WHEN 1 THEN 'Подземная оптика' WHEN 2 THEN 'Воздушная оптика' WHEN 3 THEN 'Радио' WHEN 4 THEN 'Лазер' WHEN 5 THEN 'Аренда канала' WHEN 6 THEN 'Public Internet' WHEN 7 THEN 'Медь' ELSE '' END con_type, GROUP_CONCAT(r.date SEPARATOR ', ') r_date, c.name c_name, SUM(dw.count) dw_count, GROUP_CONCAT((SELECT GROUP_CONCAT(de.empl_name SEPARATOR ', ') FROM done_employees de WHERE de.report_id = r.id GROUP BY de.report_id) SEPARATOR ', ') de_names, IF((dw.pricelist = 1), prlist.price, works.price) dw_price, IF((dw.pricelist = 1), p_m.name, w_m.name) meas_name, SUM(IF((dw.pricelist = 1), CAST(prlist.price as DECIMAL) * CAST(dw.count AS DECIMAL), CAST(works.price as DECIMAL) * CAST(dw.count AS DECIMAL))) dw_sum, IF((dw.pricelist = 1), prlist.name, works.name) as dw_name, '' dm_name, '' dm_count, '' dm_type, CONCAT_WS(', ', (IF((r.department = 'corp' AND r.weld IS TRUE) OR (r.department = 'mod' AND r.weather_temp IS TRUE), 'Работа в выходной ', null)), (IF((r.department = 'corp' AND r.diagnosis IS TRUE), 'Срочный вызов', null))) dop_details, IFNULL(org.rusname, IF(EXISTS(SELECT * FROM object lgrt WHERE r.loginroot_id = lgrt.id), 'КРОНИКС ПЛЮС' COLLATE utf8_general_ci, '')) org, r.login_from login_from FROM done_works dw LEFT JOIN reports r ON r.id = dw.report_id LEFT JOIN requestbase lg ON r.login_id = lg.id LEFT JOIN object lgrt ON r.loginroot_id = lgrt.id LEFT JOIN organization org ON org.id = lg.org LEFT JOIN works ON works.id = dw.work_id LEFT JOIN pricelist.pl_items prlist ON prlist.id = dw.work_id LEFT JOIN measures AS p_m ON p_m.id=prlist.measure_id LEFT JOIN measures AS w_m ON w_m.id=works.measure LEFT JOIN category c ON c.id= r.category WHERE #{cond} AND r.closed_at IS NULL AND r.status = 2 AND (((DATE(r.date) < DATE('2014-05-01') OR r.dhcp_login = 'new') OR (r.department !='mod' AND r.department !='corp'))) GROUP BY r_name, c_name, dw_name HAVING dw_count > 0) UNION (SELECT GROUP_CONCAT(r.id SEPARATOR ', ') r_id, r.department r_department, r.name r_name, r.request_id request_id, (CASE lgrt.conntype WHEN 1 THEN 'Подземная оптика' WHEN 2 THEN 'Воздушная оптика' WHEN 3 THEN 'Радио' WHEN 4 THEN 'Лазер' WHEN 5 THEN 'Аренда канала' WHEN 6 THEN 'Public Internet' WHEN 7 THEN 'Медь' ELSE '' END) con_type, GROUP_CONCAT(r.date SEPARATOR ', ') r_date, c.name c_name, '' dw_count, GROUP_CONCAT((SELECT GROUP_CONCAT(de.empl_name SEPARATOR ', ') FROM done_employees de WHERE de.report_id = r.id GROUP BY de.report_id) SEPARATOR ', ') de_names, '' dw_price, '' meas_name, '' dw_sum, '' as dw_name, dm.material_name dm_name, SUM(dm.count) dm_count, (CASE dm.pay_type+0 WHEN 1 THEN (CASE (dm.inst_type+0) WHEN 1 THEN 'Установка без оплаты' WHEN 4 THEN 'Снятие с объекта сети' WHEN 6 THEN 'Замена снятого с объекта сети' END) WHEN 5 THEN 'Аренда в счет тарифа' WHEN 3 THEN 'Продажа' WHEN 2 THEN 'Аренда' WHEN 4 THEN 'Снятие с объекта сети' END) dm_type, CONCAT_WS(', ', (IF((r.department = 'corp' AND r.weld IS TRUE) OR (r.department = 'mod' AND r.weather_temp IS TRUE), 'Работа в выходной ', null)), (IF((r.department = 'corp' AND r.diagnosis IS TRUE), 'Срочный вызов', null))) dop_details, IFNULL(org.rusname, IF(EXISTS(SELECT * FROM object lgrt WHERE r.loginroot_id = lgrt.id), 'КРОНИКС ПЛЮС' COLLATE utf8_general_ci, '')) org, r.login_from login_from FROM done_materials dm LEFT JOIN reports r ON r.id = dm.report_id LEFT JOIN category c ON c.id= r.category LEFT JOIN requestbase lg ON r.login_id = lg.id LEFT JOIN object lgrt ON r.loginroot_id = lgrt.id LEFT JOIN organization org ON org.id = lg.org WHERE #{cond} AND r.status = 2 AND (r.dhcp_login = 'new' OR (r.department !='mod' AND r.department !='corp')) GROUP BY r_name, c_name, dm.id HAVING dm_count > 0) ORDER BY r_id ") # login_ids = @works.map{|i| [i.rl_from_id, i.rl_to_id]}.flatten.uniq @adresses = Login.address_for_multiple_logins([], @works.map{|i| i.r_name}.flatten.uniq + @works.map{|i| i.login_from}.flatten.uniq) end def dynamic_sum_for_works_for_employee cond = [] if params[:department] && params[:department] != '' @dep = params[:department] else @dep = 'private' end if params[:empl] && params[:empl] != '' cond << "de.empl_id = "+ params[:empl] end if params[:year_from] @year_from = ("01." + (params[:month_from].size < 2 ? "0" + params[:month_from] : params[:month_from]) + "." + params[:year_from]).to_date else @year_from = Date.today end if params[:year_to] @year_to = ("01." + (params[:month_to].size < 2 ? "0" + params[:month_to] : params[:month_to]) + "." + params[:year_to]).to_date.end_of_month else @year_to = Date.today.end_of_year end cond << "r.department = '#{@dep}'" if @dep cond << "(DATE(r.date) BETWEEN DATE('#{@year_from}') AND DATE('#{@year_to}'))" cond = cond.join(" AND ") @done_works = DoneWork.find_by_sql("SELECT de_all.r_all as all_reps, dw.pricelist, de.empl_name e_name, de.empl_id e_id, IF((dw.pricelist = 1), prlist.name, works.name) as w_name, dw.work_id w_id, SUM(dw.count) s_works, SUM(dw.rate * dw.count)/SUM(dw.count) average_sum, COUNT(r.id) r_count, MONTH(DATE(r.date)) r_month, YEAR(DATE(r.date)) r_year FROM done_works dw LEFT JOIN reports r ON r.id = dw.report_id JOIN done_employees de ON de.id = (SELECT id FROM done_employees de2 WHERE de2.report_id = r.id ORDER BY IFNULL(de2.role, 0) DESC LIMIT 1) LEFT JOIN works ON works.id = dw.work_id LEFT JOIN pricelist.pl_items prlist ON prlist.id = dw.work_id LEFT JOIN (SELECT COUNT(de2.id) r_all, MONTH(DATE(r2.date)) r_month, YEAR(DATE(r2.date)) r_year, de2.empl_id e_id FROM done_employees de2 LEFT JOIN reports r2 ON r2.id = de2.report_id WHERE r2.department = '#{@dep}' AND (DATE(r2.date) BETWEEN DATE('#{@year_from}') AND DATE('#{@year_to}')) GROUP BY e_id, r_month, r_year) de_all ON de_all.e_id = de.empl_id AND de_all.r_month = MONTH(DATE(r.date)) AND de_all.r_year = YEAR(DATE(r.date)) WHERE #{cond} AND r.status != 1 AND ((DATE(r.date) < DATE('2014-05-01') OR r.dhcp_login = 'new') OR (r.department !='mod' AND r.department !='corp')) GROUP BY e_id, w_id, r_month, r_year UNION( SELECT COUNT(r.id) as all_reps, '', de.empl_name e_name, de.empl_id e_id, CONCAT('За категорию работ: ', cat.name) as w_name, cat.id w_id, COUNT(r.id) s_works, SUM(IF(cat.id = 28, IF(r.client_price_type = 0, 420, 750), IF(cat.id IN (24, 53, 54), 420, cat.price)))/COUNT(r.id) average_sum, COUNT(r.id) r_count, MONTH(DATE(r.date)) r_month, YEAR(DATE(r.date)) r_year FROM reports r JOIN done_employees de ON de.id = (SELECT id FROM done_employees de2 WHERE de2.report_id = r.id ORDER BY IFNULL(de2.role, 0) DESC LIMIT 1) LEFT JOIN category cat ON cat.id = r.category WHERE #{cond} AND r.closed_at IS NULL AND r.status != 1 AND ((DATE(r.date) < DATE('2014-05-01') OR r.dhcp_login = 'new') OR (r.department !='mod' AND r.department !='corp')) GROUP BY e_id, cat.id, r_month, r_year HAVING average_sum > 0 ) UNION( SELECT de_all.r_all, 0, de.empl_name, de.empl_id, 'Дополнительные работы (ожидание, простой, доп. работы)', 0, SUM(IF((r.department = 'soft'), IF(r.get_money_sum > 0, 1, 0), r.get_money_sum / #{Report::CORP_dop_works} ) ) cw, IF((r.department = 'soft'), SUM(r.get_money_sum) / COUNT(r.id), #{Report::CORP_dop_works} ), SUM(IF(r.get_money_sum > 0, 1, 0)), MONTH(DATE(r.date)) m, YEAR(DATE(r.date)) y FROM done_employees de LEFT JOIN reports r ON r.id = de.report_id LEFT JOIN (SELECT COUNT(de2.id) r_all, MONTH(DATE(r2.date)) r_month, YEAR(DATE(r2.date)) r_year, de2.empl_id e_id FROM done_employees de2 LEFT JOIN reports r2 ON r2.id = de2.report_id WHERE r2.department = '#{@dep}' AND (DATE(r2.date) BETWEEN DATE('#{@year_from}') AND DATE('#{@year_to}')) GROUP BY e_id, r_month, r_year) de_all ON de_all.e_id = de.empl_id AND de_all.r_month = MONTH(DATE(r.date)) AND de_all.r_year = YEAR(DATE(r.date)) WHERE #{cond} AND de.id = (SELECT id FROM done_employees de2 WHERE de2.report_id = r.id ORDER BY IFNULL(de2.role, 0) DESC LIMIT 1) AND r.status != 1 AND ((r.dhcp_login = 'new' OR DATE(r.date) < DATE('2014-05-01')) OR (r.department !='mod' AND r.department !='corp')) GROUP BY de.empl_name, m, y HAVING cw > 0) ORDER BY e_name, w_id, r_year, r_month ") end def categories if params[:year_from] @year_from = ("01." + (params[:month_from].size < 2 ? "0" + params[:month_from] : params[:month_from]) + "." + params[:year_from]).to_date else @year_from = Date.today end if params[:year_to] @year_to = ("01." + (params[:month_to].size < 2 ? "0" + params[:month_to] : params[:month_to]) + "." + params[:year_to]).to_date.end_of_month else @year_to = Date.today.end_of_year end if params[:year_from] @reports = Report.find_by_sql([ "SELECT (CASE r.department WHEN 'private' THEN 'Частные инсталляторы' WHEN 'ams' THEN 'АМС' WHEN 'alp' THEN 'альпинисты' WHEN 'welder' THEN 'Сварщики' WHEN 'underground' THEN 'Подземное строительство' WHEN 'soft' THEN 'Софт отдел' WHEN 'corp' THEN 'Корп. отдел' WHEN 'mod' THEN 'Модернизаторы' END) dep, COUNT(r.id) r_size, c.name cat, SUM(r.total_sum_works) works_s, SUM(r.total_sum_materials) materials_s, IF(SUM(r.sum) < 0, 0, SUM(r.sum)) zp_s, SUM(CAST(dm.purchase_price as DECIMAL) * CAST(dm.count as DECIMAL)) mat_purch_sum FROM reports r LEFT JOIN category c ON c.id = r.category LEFT JOIN done_materials dm ON dm.report_id = r.id WHERE r.closed_at IS NULL AND (r.dhcp_login = 'new' OR (r.department !='mod' AND r.department !='corp')) AND DATE(r.date) BETWEEN (?) AND (?) GROUP BY dep, cat HAVING cat != ''", @year_from, @year_to]) require 'spreadsheet' Spreadsheet.client_encoding = 'UTF-8' list = Spreadsheet::Workbook.new book = list.create_worksheet :name => params[:year] n_i = 0 book.row(n_i).push "Раздел", "Категория работ", "Кол-во отчетов", "З/П", "Закупочная стоимость материалов", "Продано материалов", "Получено за работы" for r in @reports do book.row(n_i+=1).push r.dep, r.cat, r.r_size, r.zp_s, r.mat_purch_sum, r.materials_s, r.works_s end @file = 'categories-' +Russian::strftime(Date.today) + '.xls' list.write 'public/'+@file send_file 'public/'+@file end end def count_all_materials @materials = DoneMaterial.find_by_sql("SELECT SUM(IF(d.count> 0, d.count, 0)) ust, SUM(IF(d.count< 0, d.count*-1, 0)) snyat, IFNULL(r.department, d.id) dep, MONTH(DATE(d.date)) month FROM done_materials d LEFT JOIN reports r ON r.id = d.report_id WHERE r.closed_at IS NULL AND DATE(d.date) >= '2014-01-01' GROUP BY r.department, month") require 'spreadsheet' Spreadsheet.client_encoding = 'UTF-8' list = Spreadsheet::Workbook.new book = list.create_worksheet :name => "Материалы" n_i = 0 book.row(n_i).push "Месяц", "Подразделение", "Установлено", "Снято" for m in @materials do book.row(n_i+=1).push m.month, m.dep, m.ust, m.snyat end @file = 'materials-' +Russian::strftime(Date.today) + '.xls' list.write 'public/'+@file send_file 'public/'+@file end def transport 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 @department = params[:division] unless @department.blank? @done_employees = DoneEmployee.find(:all, :include => ["report", "employee"], :conditions => "(reports.status = '2' OR reports.department = 'alp') AND reports.department = '#{@department}' AND DATE(reports.date) BETWEEN DATE('#{@date.beginning_of_month}') AND DATE('#{@date.end_of_month}')", :order =>"DATE(reports.date)") login_ids = @done_employees.map{|i| i.report.login}.flatten.compact.map{|i| [i.id]}.flatten.uniq @adresses = Login.address_for_multiple_logins(login_ids, @done_employees.map{|i| i.report.name}.flatten.uniq) require 'spreadsheet' Spreadsheet.client_encoding = 'UTF-8' book = Spreadsheet::Workbook.new centre = Spreadsheet::Format.new :weight => :bold, :size => 12, :horizontal_align => :centre, :text_wrap => true shapka = Spreadsheet::Format.new :weight => :normal, :horizontal_align => :right, :size => 12 podval = Spreadsheet::Format.new :weight => :normal, :horizontal_align => :centre, :text_wrap => true podval2 = Spreadsheet::Format.new :weight => :normal, :horizontal_align => :left, :text_wrap => true, :top => :thin with_bord = Spreadsheet::Format.new :weight => :normal, :horizontal_align => :left, :border => :thin de = Spreadsheet::Format.new :weight => :normal, :horizontal_align => :centre, :text_wrap => true if @done_employees.size == 0 dataBook1 = book.create_worksheet :name => 'sheet' dataBook1.row(1).push "Записи не найдены!" end @done_employees.map{|i| i.employee}.uniq.each do |empl| n_i = 0 dataBook1 = book.create_worksheet :name => empl.lastname dws = @done_employees.select{|i| i.employee == empl} # Заголовок dataBook1.row(n_i+=1)[3] = "Приложение № 2"; dataBook1.row(n_i).default_format = shapka dataBook1.row(n_i+=1)[3] = "К положению о разъездном характере работы"; dataBook1.row(n_i).default_format = shapka dataBook1.row(n_i+=1)[3] = "ООО «Локальные сети»"; dataBook1.row(n_i).default_format = shapka dataBook1.row(n_i+=1)[0] = 'Общество с ограниченной ответственностью " Локальные сети " (ООО " Локальные сети "). Отчет за месяц '+ Russian::strftime(dws.first.report.date.to_date, "%B").to_s + " " + dws.first.report.date.to_date.year.to_s; dataBook1.row(n_i).default_format = centre; title = n_i dataBook1.merge_cells(n_i, 0, n_i, 3) #// Заголовок # Столбцы dataBook1.row(n_i+=1).push "", "Дата", "Адрес проведения работ", "Подтверждающий документ" dataBook1.column(0).width = 15; dataBook1.column(1).width = 11.2; dataBook1.column(2).width = 50; dataBook1.column(3).width = 25; #// Столбцы dws.each do |dw| n_i += 1 dataBook1.row(n_i).set_format(1, with_bord) dataBook1.row(n_i).set_format(2, with_bord) dataBook1.row(n_i).set_format(3, with_bord) address = @adresses.select{|i| [dw.report.login_id, dw.report.name].include? i.ulogin}.first dataBook1.row(n_i)[1] = Russian::strftime(dw.report.date.to_date) dataBook1.row(n_i)[2] = address.adr if address dataBook1.row(n_i)[3] = "чек" end dataBook1.rows.each{|d| (d.height = 15 if d)} dataBook1.row(title).height = 40 # Подвал dataBook1.row(n_i+=1)[0] = "Передвижение между объектами осуществлялось на личном автомобиле марки #{empl.car_model_name} с объемом двигателя #{empl.car_motor_name} л/с."; dataBook1.row(n_i).height = 40; dataBook1.row(n_i).default_format = podval; dataBook1.merge_cells(n_i, 0, n_i, 3) dataBook1.row(n_i+=2)[3] = "/ " + empl.lastname + " " + empl.firstname.first + "." + empl.secondname.first + "." dataBook1.row(n_i+=1)[2] = "Подпись"; dataBook1.row(n_i).set_format(2, podval2); dataBook1.row(n_i).height = 15 dataBook1.row(n_i)[3] = "ФИО"; dataBook1.row(n_i).set_format(3, podval2); #// Подвал end @file = 'razezd-' +Russian::strftime(Date.today) + '.xls' book.write 'public/'+@file send_file 'public/'+@file end end def call_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 @date = (month +'/01/' + year).to_date @reports = Tracker.get_reports_for_sozvons(@date) end def select_dates @date_from = Date.today.last_month.to_date @date_to = Date.today @works = (Work.only_active+Pricelist.all).sort_by{|i| i.name} end def load_works @works = (Work.only_active+Pricelist.all).sort_by{|i| i.name} if params[:division] && params[:division] != '' @works = @works.select{ |i| i.depts.include? params[:division]} end render :partial => "works" end def index if params[:date_from] require 'spreadsheet' Spreadsheet.client_encoding = 'UTF-8' book = Spreadsheet::Workbook.new dataBook1 = book.create_worksheet :name => 'book' @from = params[:date_from].to_date @to = params[:date_to].to_date filename = "Report #{Russian::strftime(Date.today)}.csv" title = "Отчеты за период #{Russian::strftime(@from)} - #{Russian::strftime(@to)}." row = dataBook1.row(0); row[5] = title cond = [] cond << "(DATE(r.date) BETWEEN DATE('#{@from}') AND DATE('#{@to}'))" cond << "r.category = #{params[:category]}" if params[:category] && params[:category] != '0' && params[:category] != "" cond << "de.empl_id = #{params[:empl]}" if params[:empl] && params[:empl] != '0' && params[:empl] != "" cond << "r.department = '#{params[:division]}'"if params[:division] && params[:division] != "" cond << "((((r.dhcp_login != 'new' OR r.dhcp_login IS NULL) AND DATE(r.date) <= DATE('2014/05/31')) OR (r.dhcp_login = 'new' AND DATE(r.date) > DATE('2014/05/31'))) OR r.department NOT IN ('mod', 'corp'))" ### УБРАТЬ КОГДА ОСТАНЕТСЯ ТОЛЬКО MOD2 и CORP2 works = [] ; works2 = []; @work_names_hash = { } params[:works].to_a.each do |key, val| if val.key? 'id' # @work_names_hash[val[:id]] = val[:name] works << "( dw.pricelist ="+val[:type]+" AND dw.work_id="+val[:id]+")" works2 << "( done_works.pricelist ="+val[:type]+" AND done_works.work_id="+val[:id]+")" end end cond << "( " + works.join(" OR ") + ") " if works.size > 0 cond2 = "AND ( " + works2.join(" OR ") + ") " if works2.size > 0 cond = cond.join(" AND ") @reports = Report.find_by_sql("SELECT '' to_name, r.id, r.department dep_name, cat.name cat_name, r.name name, GROUP_CONCAT(DISTINCT de.empl_name SEPARATOR ', ') empl_names, GROUP_CONCAT(DISTINCT CONCAT(dm.material_name, ': ', dm.count) SEPARATOR ', ') mat_names, DATE(r.date) as r_date, r.sum as sum, r.sum/(COUNT(DISTINCT de.id)) sum_for_person, '' cable_length, '' attic_length FROM reports r LEFT JOIN done_employees de ON de.report_id = r.id LEFT JOIN done_materials dm ON dm.report_id = r.id LEFT JOIN done_works dw ON dw.report_id = r.id LEFT JOIN category cat ON cat.id = r.category WHERE r.closed_at IS NULL AND r.department != 'alp' AND #{cond} GROUP BY r.id LIMIT 2000 UNION SELECT rl.to_id, r.id, 'alp', cat.name, rl.from_id, GROUP_CONCAT(de.empl_name SEPARATOR ', ') empl_names, GROUP_CONCAT(CONCAT(dm.material_name, ': ', dm.count) SEPARATOR ', ') mat_names, DATE(r.date) as r_date, rl.sum as sum, rl.sum/(COUNT(de.id)) sum_for_person, rl.cable_length, rl.attic_length FROM rep_logins rl LEFT JOIN reports r ON rl.report_id = r.id LEFT JOIN done_employees de ON de.report_id = r.id LEFT JOIN done_materials dm ON dm.report_id = r.id LEFT JOIN done_works dw ON dw.report_id = r.id LEFT JOIN category cat ON cat.id = r.category WHERE r.department = 'alp' AND r.closed_at IS NULL AND #{cond} GROUP BY rl.id") @adresses = Login.address_for_multiple_logins(@reports.map{|i| [i.name, i.to_name]}.flatten.uniq, @reports.map{|i| i.name}) @done_works = DoneWork.find(:all, :include => ["work"], :conditions =>["report_id IN (?) #{cond2}", @reports.map{|i| i.id}]) @done_works.each{|i| @work_names_hash[i.work_id] = i.work_name} @work_names = @work_names_hash.values n_i = 1 if params[:division] && params[:division] == "alp" dataBook1.row(n_i).push "Логин от", "Адрес от", "Логин к", "Адрес к", "Подразделение", "Тип работ", "Дата", "Cостав участников", "Cумма на всех", "Cумма на участника", "Состав оборудования", "Общий метраж", "По чердакам" else dataBook1.row(n_i).push "Логин", "Адрес", "Подразделение", "Тип работ", "Дата", "Cостав участников", "Cумма на всех", "Cумма на участника", "Состав оборудования" @work_names.size.times do |t| dataBook1.row(n_i)[9+t] = @work_names[t-1] end end @reports.each do |rep| @work_names_hash.each_key{|k| @work_names_hash[k] = ""} n_i += 1 dw = @done_works.select{|i| i.report_id.to_i == rep.id.to_i} dw.each{|i| @work_names_hash[i.work_id] = i.count} if params[:division] && params[:division] == "alp" ad=@adresses.select{|i| i.id.to_i == rep.name.to_i} ad2 =@adresses.select{|i| i.id.to_i == rep.to_name.to_i} dataBook1.row(n_i).push((ad.size > 0? ad.first.ulogin : ''), (ad.size > 0? ad.first.adr : ''), (ad2.size > 0? ad2.first.ulogin : ''), (ad2.size > 0? ad2.first.adr : ''), rep.dep_name, rep.cat_name, Russian::strftime(rep.r_date.to_date), rep.empl_names, rep.sum, rep.sum_for_person, rep.mat_names, rep.cable_length, rep.attic_length) else ad=@adresses.select{|i| i.ulogin == rep.name} dataBook1.row(n_i).push rep.name, (ad.size > 0? ad.first.adr : ''), rep.dep_name, rep.cat_name, Russian::strftime(rep.r_date.to_date), rep.empl_names, rep.sum, rep.sum_for_person, rep.mat_names end @work_names_hash.values.size.times do |t| dataBook1.row(n_i)[9+t] = @work_names_hash.values[t-1] end end @file = 'sdelka-' +Russian::strftime(Date.today) + '.xls' book.write 'public/'+@file send_file 'public/'+@file else redirect_to :action => :select_dates end end def index2 # require 'fastercsv' require 'spreadsheet' Spreadsheet.client_encoding = 'UTF-8' book = Spreadsheet::Workbook.new dataBook1 = book.create_worksheet :name => 'book' @from = params[:date_from].to_date @to = params[:date_to].to_date filename = "Report #{Russian::strftime(Date.today)}.csv" title = "Отчеты за период #{Russian::strftime(@from)} - #{Russian::strftime(@to)}." cond = [] cond << "DATE(r.date) BETWEEN '#{@from}' AND '#{@to}'" cond << "r.category = #{params[:category]}" if params[:category] && params[:category] != '0' && params[:category] != "" cond << "de.empl_id = #{params[:empl]}" if params[:empl] && params[:empl] != '0' && params[:empl] != "" cond << "r.department = '#{params[:division]}'"if params[:division] && params[:division] != "" works = [] works_names = [] params[:works].to_a.each do |key, val| if val.key? 'id' works << "( dw.pricelist ="+val[:type]+" AND dw.work_id="+val[:id]+")" works_names << val[:name] end end cond << "( " + works.join(" OR ") + ") " if works.size > 0 cond = cond.join(" AND ") @reports = Report.find_by_sql("SELECT r.id, r.department dep_name, cat.name cat_name, r.name name, GROUP_CONCAT(de.empl_name SEPARATOR ', ') empl_names, GROUP_CONCAT(CONCAT(dm.material_name, ': ', dm.count) SEPARATOR ', ') mat_names, DATE(r.date) as r_date, r.sum as sum, r.sum/(COUNT(de.id)) sum_for_person FROM reports r LEFT JOIN done_employees de ON de.report_id = r.id LEFT JOIN done_materials dm ON dm.report_id = r.id LEFT JOIN done_works dw ON dw.report_id = r.id LEFT JOIN category cat ON cat.id = r.category WHERE r.closed_at IS NULL AND r.department != 'alp' AND #{cond} GROUP BY r.id UNION r2.id, 'alp', cat2.name, lg.loginroot, GROUP_CONCAT(de2.empl_name SEPARATOR ', ') empl_names, GROUP_CONCAT(CONCAT(dm2.material_name, ': ', dm2.count) SEPARATOR ', ') mat_names, DATE(r2.date) as r_date, rl.sum as sum, rl.sum/(COUNT(de2.id)) sum_for_person FROM rel_logins rl LEFT JOIN done_employees de2 ON de2.report_id = r2.id LEFT JOIN reports r2 ON rl.report_id = r2.id LEFT JOIN request.loginroots lr ON lr.id = rl.from_id LEFT JOIN done_materials dm ON rl.material_id = dm.id LEFT JOIN category cat ON cat.id = r.category WHERE r.closed_at IS NULL AND r.department = 'alp' AND #{cond} GROUP BY rl.id") @done_works = DoneWork.find(:all, :conditions =>["report_id IN (?)", @reports.map{|i| i.id}]) if works_names.size > 0 @works = works_names.flatten.uniq else @works = @done_works.map{|i| i.work_name}.flatten.uniq end @adresses = Login.find_by_sql(["SELECT CONCAT('г. Москва, ', s.pre, ' ', s.npre, s.name, s.post, ' ', l.house, l.building) adr, l.loginroot ulogin FROM request.object l LEFT JOIN request.street s ON s.alias = l.alias WHERE l.loginroot IN (?) GROUP BY l.loginroot UNION SELECT CONCAT('г. Москва, ', ss.pre, ' ', ss.npre, ss.name, ss.post, ' ', ll.house, ll.building), ll.ulogin FROM request.requestbase ll LEFT JOIN request.street ss ON ss.alias = ll.alias WHERE ll.ulogin IN (?) GROUP BY ll.ulogin", @reports.map{|i| i.name}, @reports.map{|i| i.name}]) csv_string = FasterCSV.generate(:col_sep => ";") do |csv| ar = Array.new(8, "") ar[5] = title; csv << ar if @reports.first.dep_name == 'alp' # @logins = @reports.map{|i| i.logins}.flatten # csv << ["Логин от", "Адрес от", "Логин к", "Адрес к", "Подразделение", "Тип работ", "Дата", # "Cостав участников", # "Cумма на всех", # "Cумма на участника", # "Состав оборудования" # ] else @logins = nil csv << ["Логин", "Адрес", "Подразделение", "Тип работ", "Дата", "Cостав участников", "Cумма на всех", "Cумма на участника", "Состав оборудования", @works ].flatten end if @logins # @logins.each do |r| # sum_for_person = r.report.done_employees.size > 0 ? (r.sum / r.report.done_employees.size) : '0' # csv << [ # r.from_name, # r.from_address, # r.to_name, # r.to_address, # r.report.department_name, # r.report.category_name, # Russian::strftime(r.report.date.to_date), # r.report.done_employees.map{|i| i.empl_name}.join(" "), # r.sum, # sum_for_person, # ((r.done_material.material_name) if r.done_material).to_s + " Кол-во: " + r.cable_length.to_s # ] # end else @reports.each do |r| if r.dep_name == 'alp' sum = r.logins.sum(:sum) mat = r.logins.map{|i| [(i.done_material.material_name if i.done_material).to_s + " Кол-во: " + i.cable_length.to_s]}.join(", ") else sum = r.sum mat = r.mat_names end work_counts = [] for w in @works do ws = @done_works.select{|i| i.work_name == w && i.report_id.to_i == r.id.to_i} if ws.size> 0 work_counts << ws.first.count else work_counts << "" end end ad=@adresses.select{|i| i.ulogin == r.name}.first csv << [ r.name, if ad then ad.adr else '' end, r.dep_name, r.cat_name, Russian::strftime(r.r_date.to_date), r.empl_names, r.sum, r.sum_for_person, r.mat_names, work_counts ].flatten end end end csv_string content = BOM + Iconv.conv("utf-16le", "utf-8", csv_string) send_data content, :only_path => false, :filename => filename end def set_var Employee.current_user = @current_user = Employee.find(session[:user_id]) unless session[:user_id].nil? if (accesses = @current_user.accesses.for_department('report')).size > 0 @boss = true if accesses.first.access_superuser == 1 @superu = true if accesses.first.access_admin == 1 @reader = true if accesses.first.access_read == 1 || accesses.first.access_write == 1 end unless @reader || @superu || @boss @errors = "В доступе отказано!" render(:partial => "shared/errors") end end end