# 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'=>'customers', 'view_name'=>'Клиенты', 'param_type'=> 'customers_multiselect', 'required'=> false},{'name'=>'with_items', 'view_name'=>'Со строками', 'param_type'=> 'boolean', 'required'=> false}]} @params['managers'].blank? ? managers = Manager.available_for_user(@user).collect(&:id).join(',') : managers = @params['managers'].join(',') @params['customers'].blank? ? customers = Customer.available_for_user(@user).collect(&:id).join(',') : customers = @params['customers'].join(',') @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[:with_items] == 'true' @audit_documents = ActiveRecord::Base.connection.execute( "SELECT audit_documents.id as number, audit_documents.date as date, audit_documents.percentage_shelves as percentage_shelves, managers.name as manager, shipping_addresses.name as shipping_address, shipping_addresses.address as address, customers.name as customer, audit_document_items.quantity as quantity, audit_document_items.price as price, audit_document_items.face as face, audit_document_items.facing as facing, audit_document_items.golden_shelf as golden_shelf, products.name as product FROM audit_documents LEFT JOIN audit_document_items on audit_document_items.audit_document_id = audit_documents.id INNER JOIN shipping_addresses on shipping_addresses.id = audit_documents.shipping_address_id INNER JOIN customers ON shipping_addresses.customer_id = customers.id INNER JOIN managers ON managers.id = audit_documents.manager_id INNER JOIN products ON products.id = audit_document_items.product_id WHERE managers.id IN (#{managers}) AND customers.id IN (#{customers}) AND audit_documents.date BETWEEN '#{date_after.to_date.strftime("%Y-%m-%d")}' AND '#{date_before.to_date.strftime("%Y-%m-%d")}' ORDER BY audit_documents.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(:audit_documents)], :style => header_cell sheet.add_row [ t(:number), t(:datetime), t(:manager), t(:customer), t(:shipping_address), t(:address), t(:product), t(:quantity), t(:price), t(:face), t(:facing), t(:golden_shelf)], style: bold_cell @audit_documents.each do |audit_document| sheet.add_row [ audit_document['number'], l(audit_document['date'].to_datetime, format: :default), audit_document['manager'], audit_document['customer'], audit_document['shipping_address'], audit_document['address'], audit_document['product'], audit_document['quantity'], audit_document['price'], audit_document['face'], audit_document['facing'], audit_document['golden_shelf'] == 't' ? t(:yes) : t(:not)], style: default_cell end sheet.column_widths 10,10,30,30,30,40,25,15,15,15,15,15 sheet.merge_cells("A1:L1") end end else @audit_documents = ActiveRecord::Base.connection.execute( "SELECT audit_documents.id as number, audit_documents.date as date, audit_documents.percentage_shelves as percentage_shelves, managers.name as manager, shipping_addresses.name as shipping_address, shipping_addresses.address as address, customers.name as customer FROM audit_documents INNER JOIN shipping_addresses on shipping_addresses.id = audit_documents.shipping_address_id INNER JOIN customers ON shipping_addresses.customer_id = customers.id INNER JOIN managers ON managers.id = audit_documents.manager_id WHERE managers.id IN (#{managers}) AND customers.id IN (#{customers}) AND audit_documents.date BETWEEN '#{date_after.to_date.strftime("%Y-%m-%d")}' AND '#{date_before.to_date.strftime("%Y-%m-%d")}' ORDER BY audit_documents.id" ) # Report generate 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(:audit_documents)], :style => header_cell sheet.add_row [t(:number), t(:datetime), t(:manager), t(:customer), t(:shipping_address), t(:address), t(:percentage_shelves)], style: bold_cell @audit_documents.each do |audit_document| sheet.add_row [ audit_document['number'], l(audit_document['date'].to_datetime, format: :default), audit_document['manager'], audit_document['customer'], audit_document['shipping_address'], audit_document['address'], audit_document['percentage_shelves']], style: default_cell end sheet.column_widths 10,10,30,30,30,40,15 sheet.merge_cells("A1:G1") end end end