# 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'=>'managers', 'view_name'=>'Менеджеры', 'param_type'=> 'managers_multiselect', 'required'=> false},{'name'=>'manager_groups', 'view_name'=>'Группы менеджеров', 'param_type'=> 'manager_groups_multiselect', 'required'=> false},{'name'=>'survey', 'view_name'=>'Анкета', 'param_type'=> 'survey_select', 'required'=> true},{'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'] survey = @params['survey'].to_i @questions = Question.where(survey_id: survey).order(:id) if @params['manager_groups'].blank? if @params['managers'].blank? managers = Manager.available_for_user(@user).collect(&:id).join(',') else managers = @params['managers'].join(',') end else managers = [] ManagerGroup.where(id: params['manager_groups']).each do |manager_group| managers += manager_group.children_managers.collect(&:id) end managers = managers.uniq.join(',') end if managers.length == 0 managers = 'null' end if @params['summary'] == 'true' @records = ActiveRecord::Base.connection.execute("select surveys_id, surveys_name, completed_survey_id, completed_survey_date, manager_id, manager_name, shipping_address_id, shipping_address_name, shipping_address_address, question_id, question_name, array_to_string(array_agg(answer), ',') AS answer from ( SELECT surveys.id AS surveys_id, surveys.name AS surveys_name, completed_surveys.id AS completed_survey_id, completed_surveys.date AS completed_survey_date, managers.id AS manager_id, managers.name AS manager_name, shipping_addresses.id AS shipping_address_id, shipping_addresses.name AS shipping_address_name, shipping_addresses.address AS shipping_address_address, questions.id AS question_id, questions.name AS question_name, answers.as_answer_id, answers.as_answer_type, CASE WHEN answers.as_answer_type = 'TextAnswer' THEN text_answers.value WHEN answers.as_answer_type = 'BooleanAnswer' THEN (CASE WHEN boolean_answers.value THEN '#{t(:truth)}' ELSE '#{t(:lie)}' END) WHEN answers.as_answer_type = 'NumberAnswer' THEN Cast(round(cast(number_answers.value as numeric),1) AS varchar) WHEN answers.as_answer_type = 'CalculateAnswer' THEN Cast(round(cast(calculate_answers.value as numeric),1) AS varchar) WHEN answers.as_answer_type = 'SelectAnswer' OR answers.as_answer_type = 'MultiselectAnswer' THEN select_answer_options.value END AS answer, CASE WHEN select_answer_options.id = select_answers.select_answer_option_id OR select_answer_options.id = multiselect_answers_select_answer_options.select_answer_option_id THEN '#{t(:truth)}' ELSE 'NULL' END AS is_selected FROM surveys INNER JOIN completed_surveys ON surveys.id = completed_surveys.survey_id AND completed_surveys.date BETWEEN '#{date_after.to_date.strftime("%Y-%m-%d")}' AND '#{date_before.to_date.end_of_day.strftime("%Y-%m-%d %T")}' INNER JOIN manager_completed_surveys ON completed_surveys.id = manager_completed_surveys.completed_survey_id INNER JOIN route_points ON manager_completed_surveys.route_point_id = route_points.id INNER JOIN managers ON manager_completed_surveys.manager_id = managers.id INNER JOIN shipping_addresses ON route_points.shipping_address_id = shipping_addresses.id INNER JOIN questions ON surveys.id = questions.survey_id LEFT JOIN answers ON questions.id = answers.question_id AND completed_surveys.id = answers.completed_survey_id LEFT JOIN text_answers ON answers.as_answer_id = text_answers.id AND answers.as_answer_type = 'TextAnswer' LEFT JOIN boolean_answers ON answers.as_answer_id = boolean_answers.id AND answers.as_answer_type = 'BooleanAnswer' LEFT JOIN number_answers ON answers.as_answer_id = number_answers.id AND answers.as_answer_type = 'NumberAnswer' LEFT JOIN calculate_answers ON answers.as_answer_id = calculate_answers.id AND answers.as_answer_type = 'CalculateAnswer' LEFT JOIN select_answer_options ON questions.id = select_answer_options.question_id LEFT JOIN select_answers ON answers.as_answer_id = select_answers.id AND answers.as_answer_type = 'SelectAnswer' AND select_answers.select_answer_option_id = select_answer_options.id LEFT JOIN multiselect_answers ON answers.as_answer_id = multiselect_answers.id AND answers.as_answer_type = 'MultiselectAnswer' LEFT JOIN multiselect_answers_select_answer_options ON multiselect_answers.id = multiselect_answers_select_answer_options.multiselect_answer_id AND multiselect_answers_select_answer_options.select_answer_option_id = select_answer_options.id WHERE surveys.id = #{survey} AND managers.id IN (#{managers})) data WHERE as_answer_type = 'TextAnswer' OR as_answer_type = 'BooleanAnswer' OR as_answer_type = 'NumberAnswer' OR as_answer_type = 'CalculateAnswer' OR (as_answer_type = 'SelectAnswer' AND is_selected = '#{t(:truth)}') OR (as_answer_type = 'MultiselectAnswer' AND is_selected = '#{t(:truth)}') group by surveys_id, surveys_name, completed_survey_id, completed_survey_date, manager_id, manager_name, shipping_address_id, shipping_address_name, shipping_address_address, question_id, question_name ORDER BY surveys_id, completed_survey_id, question_id") 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, :vertical => :center } bold_cell = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 10, :b => true, :alignment => { :horizontal=> :center, :wrap_text => true, :vertical => :center } default_cell = s.add_style :bg_color => "FFFFFF", :fg_color => "000000", :border => { :style => :thin, :color => "00" }, :sz => 10, :alignment => { :horizontal=> :center, :wrap_text => true, :vertical => :center } 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(:completed_survey) ], :style => header_cell # names for table header question_names = [] # ids for table search question_ids = [] @questions.each do |q| question_names << q.name question_ids << q.id.to_s end @header_row = [t(:datetime), t(:manager), t(:shipping_address), t(:address), t(:survey)] + question_names sheet.add_row @header_row, :height => 100, style: bold_cell current_completed_survey_id = -1 answers = Array.new(question_ids.count) record_row = [] for i in 0..@records.count - 1 record = @records[i] # if new completed survey, then begin collect new row if current_completed_survey_id != record['completed_survey_id'] current_completed_survey_id = record['completed_survey_id'] # if not first record, then insert row into table if !record_row.empty? sheet.add_row record_row += answers, style: default_cell # redefine answers array answers = Array.new(question_ids.count) end record_row = [record['completed_survey_date'].to_date.strftime('%d.%m.%Y'), record['manager_name'], record['shipping_address_name'], record['shipping_address_address'], record['surveys_name']] end answers[question_ids.index(record['question_id'])] = record['answer'] end # insert last row if exist if !record_row.empty? sheet.add_row record_row += answers, style: default_cell end # Add Summary row summaries_answers = Array.new(question_ids.count) question_ids.each do |question_id| summaries_answers[question_ids.index(question_id)] = @records.select {|record| record["question_id"].to_i == question_id.to_i }.map{ |h| h['answer'].to_i }.sum end sheet.add_row ['', '', '', '', t(:total) + ':'] + summaries_answers, style: bold_cell sheet.merge_cells("A1:K1") sheet.column_widths 10,20,25,35,10 sheet.rows[1].cells[(5..100)].each { |c| c.style = bold_cell_with_rotation } sheet.column_info[(5..100)].each{ |c| c.width = 8 } end end else @records = ActiveRecord::Base.connection.execute("SELECT surveys.id AS surveys_id, surveys.name AS surveys_name, completed_surveys.id AS completed_survey_id, completed_surveys.date AS completed_survey_date, managers.id AS manager_id, managers.name AS manager_name, shipping_addresses.id AS shipping_address_id, shipping_addresses.name AS shipping_address_name, shipping_addresses.address AS shipping_address_address, questions.id AS question_id, questions.name AS question_name, answers.as_answer_id, CASE WHEN answers.as_answer_type = 'TextAnswer' THEN text_answers.value WHEN answers.as_answer_type = 'BooleanAnswer' THEN (CASE WHEN boolean_answers.value THEN '#{t(:truth)}' ELSE '#{t(:lie)}' END) WHEN answers.as_answer_type = 'NumberAnswer' THEN Cast(round(cast(number_answers.value as numeric),1) AS varchar) WHEN answers.as_answer_type = 'CalculateAnswer' THEN Cast(round(cast(calculate_answers.value as numeric),1) AS varchar) WHEN answers.as_answer_type = 'SelectAnswer' OR answers.as_answer_type = 'MultiselectAnswer' THEN select_answer_options.value END AS answer, CASE WHEN select_answer_options.id = select_answers.select_answer_option_id OR select_answer_options.id = multiselect_answers_select_answer_options.select_answer_option_id THEN '#{t("yes")}' ELSE NULL END AS is_selected FROM surveys INNER JOIN completed_surveys ON surveys.id = completed_surveys.survey_id AND completed_surveys.date BETWEEN '#{date_after.to_date.strftime("%Y-%m-%d")}' AND '#{date_before.to_date.end_of_day.strftime("%Y-%m-%d %T")}' INNER JOIN manager_completed_surveys ON completed_surveys.id = manager_completed_surveys.completed_survey_id INNER JOIN route_points ON manager_completed_surveys.route_point_id = route_points.id INNER JOIN managers ON manager_completed_surveys.manager_id = managers.id INNER JOIN shipping_addresses ON route_points.shipping_address_id = shipping_addresses.id INNER JOIN questions ON surveys.id = questions.survey_id LEFT JOIN answers ON questions.id = answers.question_id AND completed_surveys.id = answers.completed_survey_id LEFT JOIN text_answers ON answers.as_answer_id = text_answers.id AND answers.as_answer_type = 'TextAnswer' LEFT JOIN boolean_answers ON answers.as_answer_id = boolean_answers.id AND answers.as_answer_type = 'BooleanAnswer' LEFT JOIN number_answers ON answers.as_answer_id = number_answers.id AND answers.as_answer_type = 'NumberAnswer' LEFT JOIN calculate_answers ON answers.as_answer_id = calculate_answers.id AND answers.as_answer_type = 'CalculateAnswer' LEFT JOIN select_answer_options ON questions.id = select_answer_options.question_id LEFT JOIN select_answers ON answers.as_answer_id = select_answers.id AND answers.as_answer_type = 'SelectAnswer' AND select_answers.select_answer_option_id = select_answer_options.id LEFT JOIN multiselect_answers ON answers.as_answer_id = multiselect_answers.id AND answers.as_answer_type = 'MultiselectAnswer' LEFT JOIN multiselect_answers_select_answer_options ON multiselect_answers.id = multiselect_answers_select_answer_options.multiselect_answer_id AND multiselect_answers_select_answer_options.select_answer_option_id = select_answer_options.id WHERE surveys.id = #{survey} AND managers.id IN (#{managers}) ORDER BY surveys.id, completed_surveys.id, questions.id") 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(:completed_survey) ], :style => header_cell sheet.add_row [ t(:datetime), t(:manager), t(:shipping_address), t(:address), t(:survey), t(:question), t(:answer), t(:is_selected) ], style: bold_cell @records.each do |a| sheet.add_row [ a['completed_survey_date'].to_date.strftime('%d-%m-%Y'), a['manager_name'], a['shipping_address_name'], a['shipping_address_address'], a['surveys_name'], a['question_name'], a['answer'], a['is_selected'] ], style: default_cell end sheet.merge_cells("A1:H1") sheet.column_widths 10,15,25,30,15,20,20,20 end end end