# 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'=>'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['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' @records = ActiveRecord::Base.connection.execute("select surveys_id, surveys_name, completed_survey_id, completed_survey_date, supervisor_id, supervisor_name, task_id, task_name, manager_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, supervisors.id AS supervisor_id, supervisors.name AS supervisor_name, task_types.id AS task_id, task_types.name AS task_name, managers.name AS manager_name, shipping_addresses.name AS shipping_address, shipping_addresses.address AS 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(number_answers.value AS varchar) WHEN answers.as_answer_type = 'CalculateAnswer' THEN Cast(calculate_answers.value 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 supervisor_completed_surveys ON completed_surveys.id = supervisor_completed_surveys.completed_survey_id INNER JOIN tasks ON supervisor_completed_surveys.task_id = tasks.id LEFT JOIN shipping_addresses ON shipping_addresses.id = tasks.shipping_address_id LEFT JOIN managers ON tasks.manager_id = managers.id INNER JOIN task_types ON tasks.task_type_id = task_types.id INNER JOIN supervisors ON supervisor_completed_surveys.supervisor_id = supervisors.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 supervisors.id IN (#{supervisors})) 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, supervisor_id, supervisor_name, task_id, task_name, shipping_address, manager_name, 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(:supervisor), t(:task_type), 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['supervisor_name'], record['task_name'], record['manager_name'], record['shipping_address'], record['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,20,25,25,20 sheet.rows[1].cells[(7..100)].each { |c| c.style = bold_cell_with_rotation } sheet.column_info[(7..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, supervisors.id AS supervisor_id, supervisors.name AS supervisor_name, task_types.id AS task_id, task_types.name AS task_name, 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(number_answers.value AS varchar) WHEN answers.as_answer_type = 'CalculateAnswer' THEN Cast(calculate_answers.value 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 supervisor_completed_surveys ON completed_surveys.id = supervisor_completed_surveys.completed_survey_id INNER JOIN tasks ON supervisor_completed_surveys.task_id = tasks.id INNER JOIN task_types ON tasks.task_type_id = task_types.id INNER JOIN supervisors ON supervisor_completed_surveys.supervisor_id = supervisors.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 supervisors.id IN (#{supervisors}) 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(:supervisor), t(:task_type), 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['supervisor_name'], a['task_name'], a['surveys_name'], a['question_name'], a['answer'], a['is_selected'] ], style: default_cell end sheet.merge_cells("A1:G1") sheet.column_widths 10,15,25,30,15,20,20 end end end