# 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'=>'distance', 'view_name'=>'Расстояние (метр)', 'param_type'=> 'warehouses_multiselect', 'required'=> false}, {'name'=>'detail', 'view_name'=>'Детализация', 'param_type'=> 'boolean', 'required'=> false}, {'name'=>'summary', 'view_name'=>'Сводный', 'param_type'=> 'boolean', 'required'=> false}]} 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 = @manager_ids.uniq.join(',') end if managers.length == 0 managers = 'null' end @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'] @params['distance'].blank? ? distance = 500 : distance = @params['date_after'].to_i.abs if params[:detail] == 'true' @records = ActiveRecord::Base.connection.execute("SELECT manager_groups.name as manager_group, managers.name AS manager_name, dates::date as date, shipping_addresses.name AS shipping_address_name, shipping_addresses.external_key AS shipping_address_external_key, shipping_addresses.address, (SELECT count(id) FROM orders WHERE route_point_id = route_points.id) AS orders, (SELECT count(id) FROM audit_documents WHERE route_point_id = route_points.id) AS audit_documents, (SELECT count(id) FROM refunds WHERE route_point_id = route_points.id) AS refunds, (SELECT count(id) FROM route_point_photos WHERE route_point_id = route_points.id) AS photos, (SELECT count(id) FROM manager_completed_surveys WHERE route_point_id = route_points.id) AS completed_surveys, (CASE WHEN EXISTS (SELECT id FROM orders WHERE route_point_id = route_points.id AND ST_DWITHIN(coordinates, shipping_addresses.coordinates, #{distance})) OR EXISTS (SELECT id FROM audit_documents WHERE route_point_id = route_points.id AND ST_DWITHIN(coordinates, shipping_addresses.coordinates, #{distance})) OR EXISTS (SELECT id FROM refunds WHERE route_point_id = route_points.id AND ST_DWITHIN(coordinates, shipping_addresses.coordinates, #{distance})) OR EXISTS (SELECT id FROM route_point_photos WHERE route_point_id = route_points.id AND ST_DWITHIN(coordinates, shipping_addresses.coordinates, #{distance})) OR EXISTS (SELECT completed_surveys.id FROM completed_surveys INNER JOIN manager_completed_surveys ON completed_surveys.id = manager_completed_surveys.completed_survey_id WHERE manager_completed_surveys.route_point_id = route_points.id AND ST_DWITHIN(completed_surveys.coordinates, shipping_addresses.coordinates, #{distance})) THEN true ELSE false END) AS verified_by_gps FROM managers CROSS JOIN generate_series('#{date_after.to_date.strftime("%Y-%m-%d")}', '#{date_before.to_date.strftime("%Y-%m-%d")}', '1 day'::interval) dates INNER JOIN template_routes ON template_routes.manager_id = managers.id AND template_routes.day_of_week = date_part('isodow', dates::date) AND template_routes.validity = true LEFT JOIN routes ON routes.manager_id = managers.id AND routes.date = dates::date LEFT JOIN template_route_points ON template_routes.id = template_route_points.template_route_id AND template_route_points.validity = true AND date_part('week', dates::date) IN (SELECT number FROM week_numbers WHERE template_route_point_id = template_route_points.id) LEFT JOIN route_points ON route_points.route_id = routes.id AND template_route_points.shipping_address_id = route_points.shipping_address_id LEFT JOIN shipping_addresses ON template_route_points.shipping_address_id = shipping_addresses.id LEFT JOIN manager_groups ON managers.manager_group_id = manager_groups.id WHERE managers.id IN (#{managers}) ORDER BY routes.date, managers.name") @records = @records.sort_by { |k| k['manager_name'].to_s } if date_before == date_after @date_period = date_after else @date_period = "#{date_after} - #{date_before}" 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 } wb.add_worksheet(:name => 'Sheet1') do |sheet| sheet.add_row [t(:detail_route_execution)], :style => header_cell sheet.add_row ["#{t(:date_period)} : #{@date_period}",'','','','','','','','','','',''], :style => header_cell sheet.add_row [''] sheet.add_row [t(:datetime), t(:manager_group), t(:manager), t(:external_key), t(:shipping_address), t(:address), t(:visited), t(:orders), t(:photos), t(:audit_documents), t(:refunds), t(:completed_surveys), t(:verified_by_gps)], style: bold_cell @records.each do |record| sheet.add_row [ record['date'], record['manager_group'], record['manager_name'], record['shipping_address_external_key'], record['shipping_address_name'], record['address'], (record['orders'].to_i + record['photos'].to_i + record['audit_documents'].to_i + record['refunds'].to_i + record['completed_surveys'].to_i) > 0 ? t('yes') : t('not'), record['orders'].to_i, record['photos'].to_i, record['audit_documents'].to_i, record['refunds'].to_i, record['completed_surveys'].to_i, record['verified_by_gps'] == 't' ? t('yes') : t('not')], style: default_cell end sheet.column_widths 10,18,18,10,22,30,10,10,15,15,10,15 sheet.merge_cells("A1:M1") sheet.merge_cells("A2:M2") end end else @records = ActiveRecord::Base.connection.execute("SELECT raw_data.date, manager_id, manager.name AS manager_name, manager_group.name AS group_name, sum(raw_data.template_route_points_count) AS points_count, sum(raw_data.visited_template_route_point) AS visited_points, sum(raw_data.verified_template_route_point) AS verified_points, sum(raw_data.route_points_with_orders) AS visit_with_order, sum(raw_data.route_points_with_photos) AS visit_with_photo, sum(raw_data.route_points_with_audit_documents) AS visit_with_audit, sum(raw_data.route_points_with_refunds) AS visit_with_refund, sum(raw_data.route_points_with_completed_surveys) AS visit_with_completed_survey, sum(raw_data.route_points_with_documents) AS visit_total FROM (SELECT dates::date AS date, manager.id AS manager_id, template_route.id AS template_route_id, route.id AS route_id, case when template_route_point.id is null then 0 else 1 end AS template_route_points_count, 0 AS visited_template_route_point, 0 AS verified_template_route_point, 0 AS route_points_with_orders, 0 AS route_points_with_audit_documents, 0 AS route_points_with_refunds, 0 AS route_points_with_photos, 0 AS route_points_with_completed_surveys, 0 AS route_points_with_documents FROM managers manager CROSS JOIN generate_series('#{date_after.to_date.strftime("%Y-%m-%d")}', '#{date_before.to_date.strftime("%Y-%m-%d")}', '1 day'::interval) dates LEFT JOIN template_routes template_route ON template_route.manager_id = manager.id AND template_route.day_of_week = date_part('isodow', dates::date) LEFT JOIN routes route ON route.manager_id = manager.id AND route.date = dates::date LEFT JOIN template_route_points template_route_point ON template_route_point.template_route_id = template_route.id AND template_route_point.validity = TRUE AND date_part('week', dates::date) IN (SELECT number FROM week_numbers WHERE template_route_point_id = template_route_point.id) WHERE manager.id IN (#{managers}) AND manager.validity = TRUE UNION ALL SELECT route.date, route.manager_id, template_route_point.template_route_id, route.id AS route_id, sum(0) AS template_route_points_count, sum(CASE WHEN EXISTS (SELECT id FROM orders WHERE route_point_id = route_point.id) OR EXISTS (SELECT id FROM audit_documents WHERE route_point_id = route_point.id) OR EXISTS (SELECT id FROM refunds WHERE route_point_id = route_point.id) OR EXISTS (SELECT id FROM route_point_photos WHERE route_point_id = route_point.id) THEN 1 ELSE 0 END) AS visited_template_route_point, sum(CASE WHEN EXISTS (SELECT id FROM orders WHERE route_point_id = route_point.id AND ST_DWITHIN(coordinates, shipping_address.coordinates, #{distance})) OR EXISTS (SELECT id FROM audit_documents WHERE route_point_id = route_point.id AND ST_DWITHIN(coordinates, shipping_address.coordinates, #{distance})) OR EXISTS (SELECT id FROM refunds WHERE route_point_id = route_point.id AND ST_DWITHIN(coordinates, shipping_address.coordinates, #{distance})) OR EXISTS (SELECT id FROM route_point_photos WHERE route_point_id = route_point.id AND ST_DWITHIN(coordinates, shipping_address.coordinates, #{distance})) THEN 1 ELSE 0 END) AS verified_template_route_point, 0 AS route_points_with_orders, 0 AS route_points_with_audit_documents, 0 AS route_points_with_refunds, 0 AS route_points_with_photos, 0 AS route_points_with_completed_surveys, 0 AS route_points_with_documents FROM routes route INNER JOIN template_routes template_route ON template_route.day_of_week = date_part('isodow', route.date) AND template_route.manager_id = route.manager_id INNER JOIN route_points route_point ON route.id = route_point.route_id INNER JOIN template_route_points template_route_point ON route_point.shipping_address_id = template_route_point.shipping_address_id AND template_route_point.validity = TRUE AND template_route_point.template_route_id = template_route.id AND date_part('week', route.date) IN (SELECT number FROM week_numbers WHERE template_route_point_id = template_route_point.id) INNER JOIN shipping_addresses shipping_address on shipping_address.id = route_point.shipping_address_id WHERE route.manager_id IN (#{managers}) AND route.date BETWEEN '#{date_after.to_date.strftime("%Y-%m-%d")}' AND '#{date_before.to_date.strftime("%Y-%m-%d")}' GROUP BY route.date, route.manager_id, template_route_point.template_route_id, route.id UNION ALL SELECT route_point_visits.date, route_point_visits.manager_id, NULL AS template_route_id, route_point_visits.route_id, sum(route_point_visits.template_route_points_count) AS template_route_points_count, sum(route_point_visits.visited_template_route_point) AS visited_template_route_point, sum(route_point_visits.verified_template_route_point) AS verified_template_route_point, sum(route_point_visits.has_orders) AS route_points_with_orders, sum(route_point_visits.has_audit_documents) AS route_points_with_audit_documents, sum(route_point_visits.has_refunds) AS route_points_with_refunds, sum(route_point_visits.has_photos) AS route_points_with_photos, sum(route_point_visits.has_completed_surveys) AS route_points_with_completed_surveys, sum(CASE WHEN route_point_visits.has_orders > 0 OR route_point_visits.has_audit_documents > 0 OR route_point_visits.has_refunds > 0 OR route_point_visits.has_photos > 0 OR route_point_visits.has_completed_surveys > 0 THEN 1 ELSE 0 END) AS route_points_with_documents FROM ( SELECT route.date, route.manager_id, route.id AS route_id, 0 AS template_route_points_count, 0 AS visited_template_route_point, 0 AS verified_template_route_point, CASE WHEN EXISTS (SELECT id FROM orders WHERE route_point_id = route_point.id) THEN 1 ELSE 0 END AS has_orders, CASE WHEN EXISTS (SELECT id FROM audit_documents WHERE route_point_id = route_point.id) THEN 1 ELSE 0 END AS has_audit_documents, CASE WHEN EXISTS (SELECT id FROM refunds WHERE route_point_id = route_point.id) THEN 1 ELSE 0 END AS has_refunds, CASE WHEN EXISTS (SELECT id FROM manager_completed_surveys WHERE route_point_id = route_point.id) THEN 1 ELSE 0 END AS has_completed_surveys, CASE WHEN EXISTS (SELECT id FROM route_point_photos WHERE route_point_id = route_point.id) THEN 1 ELSE 0 END AS has_photos FROM routes route INNER JOIN route_points route_point ON route.id = route_point.route_id WHERE route.manager_id IN (#{managers}) AND route.date BETWEEN '#{date_after.to_date.strftime("%Y-%m-%d")}' AND '#{date_before.to_date.strftime("%Y-%m-%d")}') route_point_visits GROUP BY route_point_visits.date, route_point_visits.manager_id, route_point_visits.route_id ) raw_data LEFT JOIN managers manager ON raw_data.manager_id = manager.id LEFT JOIN manager_groups manager_group ON manager.manager_group_id = manager_group.id GROUP BY raw_data.date, manager_id, manager.name, manager_group.name ORDER BY date") if params[:summary] == 'true' wb = xlsx_package.workbook persent_execution = @records.map { |h| h['visited_points'].to_i }.sum > 0 ? (@records.map { |h| h['visited_points'].to_f }.sum / @records.map { |h| h['points_count'].to_f }.sum * 100).round(2) : 0 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=> :left, :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(:qos_trade_points), ''], :style => header_cell sheet.add_row [t(:datetime), @records.first ? @records.first['date'] : ''], :style => [bold_cell, default_cell] sheet.add_row [t(:points_count), @records.map { |h| h['points_count'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:points_visited), @records.map { |h| h['visited_points'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:points_verified), @records.map { |h| h['verified_points'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:points_with_orders), @records.map { |h| h['visit_with_order'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:points_with_photos), @records.map { |h| h['visit_with_photo'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:points_with_audit_documents), @records.map { |h| h['visit_with_audit'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:points_with_completed_surveys), @records.map { |h| h['visit_with_completed_survey'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:points_with_refunds), @records.map { |h| h['points_with_refund'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:total_points_count), @records.map { |h| h['visit_total'].to_i }.sum], :style => [bold_cell, default_cell] sheet.add_row [t(:percent_execution), "#{persent_execution}%"], :style => [bold_cell, default_cell] sheet.merge_cells("A1:B1") end end else @records = @records.sort_by { |k| k['group_name'].to_s } if date_after == date_before @date_period = date_after else @date_period = "#{date_after} - #{date_before}" 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 } wb.add_worksheet(:name => 'Sheet1') do |sheet| sheet.add_row [t(:qos_trade_points)], :style => header_cell sheet.add_row ["#{t(:date_period)} : #{@date_period}",'','',''], :style => header_cell sheet.add_row [''] # sheet.add_row [t(:datetime), t(:manager_group), t(:manager), t(:points_count), t(:points_visited), t(:points_verified), t(:points_with_orders), t(:points_with_photos), t(:points_with_audit_documents), t(:points_with_refunds), t(:total_points_count), t(:percent_execution)], style: bold_cell sheet.add_row [t(:datetime), t(:manager_group), t(:manager), t(:points_count), t(:points_visited), t(:points_verified), t(:points_with_orders), t(:points_with_photos), t(:points_with_audit_documents), t(:points_with_refunds), t(:points_with_completed_surveys), t(:total_points_count), t(:percent_execution)], style: bold_cell @records.each do |record| sheet.add_row [ record['date'], record['group_name'], record['manager_name'], record['points_count'].to_i, record['visited_points'].to_i, record['verified_points'].to_i, record['visit_with_order'].to_i, record['visit_with_photo'].to_i, record['visit_with_audit'].to_i, record['visit_with_refund'].to_i, record['visit_with_completed_survey'].to_i, record['visit_total'].to_i, "#{record['visited_points'].to_f >0 ? (record['visited_points'].to_f / record['points_count'].to_f * 100).round(2) : 0}%" ], style: default_cell end sheet.add_row [ t(:total), '', '', @records.map { |h| h['points_count'].to_i }.sum, @records.map { |h| h['visited_points'].to_i }.sum, @records.map { |h| h['verified_points'].to_i }.sum, @records.map { |h| h['visit_with_order'].to_i }.sum, @records.map { |h| h['visit_with_photo'].to_i }.sum, @records.map { |h| h['visit_with_audit'].to_i }.sum, @records.map { |h| h['visit_with_refund'].to_i }.sum, @records.map { |h| h['visit_with_completed_survey'].to_i }.sum, @records.map { |h| h['visit_total'].to_i }.sum, "#{@records.map { |h| h['visited_points'].to_i }.sum > 0 ? (@records.map { |h| h['visited_points'].to_f }.sum / @records.map { |h| h['points_count'].to_f }.sum * 100).round(2) : 0}%" ], style: bold_cell sheet.column_widths 10,15,20,15,15,15,15,15,15,15,15,15,15 sheet.merge_cells("A1:K1") sheet.merge_cells("A2:D2") end end end end