# 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'=>'warehouses', 'view_name'=>'Склады', 'param_type'=> 'warehouses_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['warehouses'].blank? ? warehouses = Warehouse.available_for_user(@user).collect(&:id).join(',') : warehouses = @params['warehouses'].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' @orders = ActiveRecord::Base.connection.execute( "SELECT orders.id as number, orders.date as date, orders.shipping_date as shipping_date, orders.exported_at as exported_at, managers.name as manager, shipping_addresses.name as shipping_address, shipping_addresses.address as address, customers.name as customer, price_lists.name as price_list, warehouses.name as warehouse, products.name as product, order_items.quantity as quantity, order_items.amount as amount, order_items.volume as volume, order_items.amount/order_items.quantity as price FROM orders INNER JOIN shipping_addresses on shipping_addresses.id = orders.shipping_address_id INNER JOIN customers ON shipping_addresses.customer_id = customers.id INNER JOIN managers ON managers.id = orders.manager_id INNER JOIN price_lists ON price_lists.id = orders.price_list_id INNER JOIN warehouses ON warehouses.id = orders.warehouse_id INNER JOIN order_items ON orders.id = order_items.order_id INNER JOIN products ON products.id = order_items.product_id WHERE managers.id IN (#{managers}) AND customers.id IN (#{customers}) AND warehouses.id IN (#{warehouses}) AND orders.date BETWEEN '#{date_after.to_date.strftime("%Y-%m-%d")}' AND '#{date_before.to_date.strftime("%Y-%m-%d")}' ORDER BY orders.date, managers.name" ) 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(:orders)], :style => header_cell sheet.add_row [ t(:number), t(:datetime), t(:shipping_date), t(:manager), t(:customer), t(:shipping_address), t(:address), t(:price_list), t(:warehouse), t(:product), t(:quantity), t(:price), t(:amount), t(:volume)], style: bold_cell @orders.each do |order| sheet.add_row [ order['number'], l(order['date'].to_datetime, format: :default), l(order['shipping_date'].to_datetime, format: :default), order['manager'], order['customer'], order['shipping_address'], order['address'], order['price_list'], order['warehouse'], order['product'], order['quantity'], order['price'], order['amount'], order['volume']], style: default_cell end sheet.column_widths 10,10,10,25,25,25,25,20,20,20,12,10,10,10 sheet.merge_cells("A1:N1") end end else @orders = ActiveRecord::Base.connection.execute( "SELECT orders.id as number, orders.date as date, orders.shipping_date as shipping_date, orders.exported_at as exported_at, managers.name as manager, shipping_addresses.name as shipping_address, shipping_addresses.address as address, customers.name as customer, price_lists.name as price_list, warehouses.name as warehouse, orders.pickup as pickup, orders.status as status, orders.amount as amount, orders.volume as volume, orders.comment as comment FROM orders INNER JOIN shipping_addresses on shipping_addresses.id = orders.shipping_address_id INNER JOIN customers ON shipping_addresses.customer_id = customers.id INNER JOIN managers ON managers.id = orders.manager_id INNER JOIN price_lists ON price_lists.id = orders.price_list_id INNER JOIN warehouses ON warehouses.id = orders.warehouse_id WHERE managers.id IN (#{managers}) AND customers.id IN (#{customers}) AND warehouses.id IN (#{warehouses}) AND orders.date BETWEEN '#{date_after.to_date.strftime("%Y-%m-%d")}' AND '#{date_before.to_date.strftime("%Y-%m-%d")}' ORDER BY orders.date, managers.name" ) # 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(:orders)], :style => header_cell sheet.add_row [ t(:number), t(:datetime), t(:shipping_date), t(:exported_date), t(:manager), t(:customer), t(:shipping_address), t(:address), t(:price_list), t(:warehouse), t(:pickup), t(:status), t(:order_amount), t(:order_volume), t(:comment)], style: bold_cell @orders.each do |order| sheet.add_row [ order['number'], l(order['date'].to_datetime, format: :default), l(order['shipping_date'].to_datetime, format: :default), order['exported_at'] ? l(order['exported_at'].to_datetime, format: :default) : t(:not_exported), order['manager'], order['customer'], order['shipping_address'], order['address'], order['price_list'], order['warehouse'], order['pickup'] == 't' ? t(:yes) : t(:not), t(Order::STATUSES[order['status'].to_i]), order['amount'], order['volume'], order['comment']], style: default_cell end sheet.column_widths 10,10,10,10,25,25,30,30,25,25,15,15,15,15,25 sheet.merge_cells("A1:O1") end end end