# report = {'name'=> 'Дневные задачи супервайзеров', 'format'=>'xlsx', 'report_params_attributes'=>[{'name'=>'date_after', 'view_name'=>'Дата от', 'param_type'=> 'date', 'required'=> true}, {'name'=>'date_before', 'view_name'=>'Дата до', 'param_type'=> 'date', 'required'=> true}, {'name'=>'supervisors', 'view_name'=>'Супервайзеры', 'param_type'=> 'supervisors_multiselect', 'required'=> false}, {'name'=>'supervisor_groups', 'view_name'=>'Группы супервайзеров', 'param_type'=> 'supervisor_groups_multiselect', 'required'=> false}, {'name'=>'summary', 'view_name'=>'Сводный', 'param_type'=> 'boolean', 'required'=> false}]} @params['date_before'].blank? ? date_before = Date.today : date_before = @params['date_before'] @params['date_after'].blank? ? date_after = Date.today : date_after = @params['date_after'] if @params['supervisor_groups'].blank? if @params['supervisors'].blank? supervisors = Supervisor.available_for_user(@user).collect(&:id).join(',') else supervisors = @params['supervisors'].join(',') end else supervisors = [] SupervisorGroup.where(id: @params['supervisor_groups']).each do |supervisor_group| supervisors += supervisor_group.children_supervisors.collect(&:id) end supervisors = supervisors.uniq.join(',') end if supervisors.length == 0 supervisors = 'null' end if @params['summary'] == 'true' @tasks = TaskType.order(:id) @records = ActiveRecord::Base.connection.execute(" SELECT supervisors.name as supervisor, task_types.name as task_name, COUNT(tasks.id) as count FROM tasks LEFT JOIN dayli_tasks ON dayli_tasks.id = tasks.dayli_task_id LEFT JOIN supervisors ON supervisors.id = dayli_tasks.supervisor_id LEFT JOIN task_types ON tasks.task_type_id = task_types.id WHERE supervisors.id IN (#{supervisors}) and dayli_tasks.date >= '#{date_after.to_date.strftime('%Y-%m-%d')}' and dayli_tasks.date <= '#{date_before.to_date.strftime('%Y-%m-%d')}' GROUP BY supervisors.name, task_types.name ORDER BY supervisors.name") if date_after == date_before @date_period = date_after else @date_period = "#{date_after} - #{date_before}" end supervisors = @records.collect {|x| x['supervisor']}.uniq task_names = @records.collect {|x| x['task_name']}.uniq @header_row = [t(:supervisor)] + task_names @rows = [] supervisors.each do |supervisor| @row = [supervisor] task_names.each_with_index do |task_name, index| task_row = @records.select {|record| record["task_name"] == task_name && record["supervisor"] == supervisor } if task_row.count > 0 @row += [task_row.first['count']] else @row += [0] end end @rows << @row end wb = xlsx_package.workbook wb.styles do |s| header_cell = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 12, :b => true, :alignment => { :horizontal=> :center, :wrap_text => true } bold_cell = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 10, :b => true, :alignment => { :horizontal=> :right, :wrap_text => true } default_cell = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 10, :alignment => { :horizontal=> :right, :wrap_text => true } bold_cell_with_rotation = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 10, :b => true, :alignment => { :horizontal=> :center, :wrap_text => true, :textRotation => 90, :vertical => :center} wb.add_worksheet(:name => 'Sheet1') do |sheet| sheet.add_row [t(:supervisor_tasks_report)], :style => header_cell sheet.add_row ["#{t(:date_period)} : #{@date_period}",'','',''], :style => header_cell sheet.add_row [''] sheet.add_row @header_row, style: bold_cell @rows.each do |row| sheet.add_row row, style: default_cell end sheet.column_widths 30 sheet.merge_cells("A1:D1") sheet.merge_cells("A2:D2") end end else @records = ActiveRecord::Base.connection.execute("SELECT dayli_tasks.date as date, supervisors.name as supervisor, managers.name as manager, shipping_addresses.name as shipping_address, task_types.name as task_type, note_task_results.note as note FROM tasks INNER JOIN dayli_tasks ON dayli_tasks.id = tasks.dayli_task_id INNER JOIN supervisors ON supervisors.id = dayli_tasks.supervisor_id LEFT JOIN managers ON managers.id = tasks.manager_id LEFT JOIN shipping_addresses on shipping_addresses.id = tasks.shipping_address_id LEFT JOIN offices on offices.id = tasks.office_id INNER JOIN task_types ON tasks.task_type_id = task_types.id LEFT JOIN task_results ON (tasks.id = task_results.task_id AND task_results.as_task_result_type = 'NoteTaskResult') LEFT JOIN note_task_results ON task_results.as_task_result_id = note_task_results.id WHERE supervisors.id IN (#{supervisors}) and dayli_tasks.date >= '#{date_after.to_date.strftime('%Y-%m-%d')}' and dayli_tasks.date <= '#{date_before.to_date.strftime('%Y-%m-%d')}' ORDER BY dayli_tasks.date") wb = xlsx_package.workbook wb.styles do |s| header_cell = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 12, :b => true, :alignment => { :horizontal=> :center, :wrap_text => true } bold_cell = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 10, :b => true, :alignment => { :horizontal=> :right, :wrap_text => true } default_cell = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 10, :alignment => { :horizontal=> :right, :wrap_text => true } wb.add_worksheet(:name => 'Sheet1') do |sheet| sheet.add_row [t(:supervisor_tasks_report)], :style => header_cell sheet.add_row [ t(:datetime), t(:supervisor), t(:shipping_address), t(:office), t(:manager), t(:task_type), t(:note)], style: bold_cell @records.each do |record| sheet.add_row [ record['date'], record['supervisor'], record['shipping_address'], record['office'], record['manager'], record['task_type'], record['note']], style: default_cell end sheet.column_widths 10,15,20,20,20,20,20 sheet.merge_cells("A1:G1") end end end