company_attendance_report.rb

# coding: utf-8
# Public: Класс отчета по звонкам полученным от МТС
module Reports
  class CompanyAttendance < ::Resque::Reports::CsvReport
    include Resque::Reports::Common::BatchedReport

    attr_accessor :from, :to

    encoding CP1251
    directory File.join(Rails.root, 'public', 'system', 'company_attendance_csv_export')

    table do
      column 'ID компании', :id
      column 'Название', :name
      column 'Привязана', :users_created_at
      column 'Создана', :created_at
      column 'Модерация', :moderation
      column 'Пакет', :packet
      column 'Интернет-магазин', :is_online_shop
      column 'Главный регион', :main_region_name
      column 'Кол-во публикуемых', :public_count
      column 'Кол-во архивных', :archive_count
      column 'UGC товары', :ugc_count
      column 'Товары (редакторы)', :editor_count
      column prev_m_first_day_date.to_s, :prev_month_visits
      column curr_m_first_day_date.to_s, :current_month_visits
      column 'Дата подключения тестового пакета', :latest_test_packet_date
      column 'Авторизаций в текущем месяце', :visits_in_current_month
      column 'Авторизаций за предыдущий месяц', :visits_in_prev_month
    end

    # Internal: see Resque::Reports::Common::BatchedReport
    delegate :connection => :Company

    # event handlers
    # Internal: see gem resque-reports
    def progress_message(*)
      'Выгрузка отчета Company Attendance'
    end

    protected

    # Текущая дата.
    #
    # Returns Date.
    def self.date
      Date.today
    end

    # Первый день текущего месяца
    #
    # Returns Date.
    def self.curr_m_first_day_date
      date.beginning_of_month
    end

    # Первый день прошлого месяца
    #
    # Returns Date.
    def self.prev_m_first_day_date
      date.prev_month.beginning_of_month
    end

    # TODO: тут с алиасами разобраться надо. Взять тот запрос который в base_query и посмотреть как он (Arel) алиасы
    #       для подзапросов создает
    # Internal: see Resque::Reports::Common::BatchedReport
    def select
      <<-SQL.strip_heredoc
        SELECT c.id, c.name, date(cu.created_at) AS users_created_at, date(c.created_at) AS created_at,
        CASE
          WHEN c.state = 'accepted' THEN 'одобрена'
          WHEN c.state = 'rejected' THEN 'отклонена'
          WHEN c.state = 'pending' THEN 'новая'
          WHEN c.state = 'changed' THEN 'изменена'
        END AS moderation,
        c.packet,
        CASE WHEN c.online_store_type IS NOT NULL AND c.is_online_shop IS TRUE THEN 'Россия'
              WHEN c.online_store_type IS NOT NULL THEN 'да'
              ELSE 'нет'
        END AS is_online_shop,
        r.name as main_region_name, prods.public_count, prods.archive_count, prods.ugc_count, prods.editor_count, prods.yml_count,
        COALESCE(prev_m.visits, 0) prev_month_visits,
        COALESCE(curr_m.visits, 0) current_month_visits,
        date(tp.created_at) latest_test_packet_date,
        COALESCE(cos.visits_in_current_month, 0) visits_in_current_month,
        COALESCE(cos.visits_in_prev_month, 0) visits_in_prev_month
      SQL
    end

    # Internal: see Resque::Reports::Common::BatchedReport
    def models
      [Company, Region, CompanyUser,
       TestPacket, Denormalization::Models::CompanyOnlineStat,
       Product, CompanyStatisticTotalByMonth]
    end

    # Internal: see Resque::Reports::Common::BatchedReport
    def base_query
      company, region, comp_user, test_packet, company_online_stat, product, stat_by_months = tables.values

      company_users =
        comp_user
          .project(comp_user[:company_id], comp_user[:created_at].minimum.as('created_at'))
          .group(comp_user[:company_id])
          .having(comp_user[:created_at].minimum.in(from, to)) # TODO: придумать как вынести в filter

      public_products = product.where(product[:is_public].eq(true).and(product[:state].not_eq('deleted')))
      archive_products = product.where(product[:is_public].eq(false).and(product[:state].not_eq('deleted')))
      ugc_products = product.where(product[:is_public].eq(true).and(product[:state].not_eq('deleted')))

      editor_products =
        product.where(
          product[:is_public].eq(true)
            .and(product[:state].not_eq('deleted'))
            .and(product[:create_user_type].eq('editor')
          )
      )

      yml_products =
        product.where(
          product[:is_public].eq(true)
            .and(product[:state].not_eq('deleted'))
            .and(product[:create_user_type].eq('yml')
          )
      )

      products =
        company
          .project(
            company[:id].as(:company_id),
            public_products[:id].count.as(:public_count),
            archive_products[:id].count.as(:archive_count),
            ugc_products[:id].count.as(:ugc_count),
            editor_products[:id].count.as(:editor_count),
            yml_products[:id].count.as(:yml_count),
          )
          .join(product).on(product[:company_id].eq(company[:id]))
          .join(public_products, Arel::Nodes::OuterJoin).on(public_products[:id].eq(product[:id]))
          .join(archive_products, Arel::Nodes::OuterJoin).on(archive_products[:id].eq(product[:id]))
          .join(ugc_products, Arel::Nodes::OuterJoin).on(ugc_products[:id].eq(product[:id]))
          .join(editor_products, Arel::Nodes::OuterJoin).on(editor_products[:id].eq(product[:id]))
          .join(yml_products, Arel::Nodes::OuterJoin).on(yml_products[:id].eq(product[:id]))
          .where(company[:state].not_eq('deleted'))
          .group(company[:id])

      prev_month_stat = stat_by_months.where(stat_by_months[:date].eq(prev_m_first_day_date))
      curr_month_stat = stat_by_months.where(stat_by_months[:date].eq(curr_m_first_day_date))

      company
        .join(region).on(region[:id].eq(company[:main_region_id]))
        .join(company_users).on(company_users[:company_id].eq(company[:id]))
        .join(
          test_packet.project(test_packet[:id], test_packet[:company_id, test_packet[:created_at]]),
          Arel::Nodes::OuterJoin
        ).on(test_packet[:company_id].eq(company[:id]))
        .join(company_online_stat, Arel::Nodes::OuterJoin).on(company_online_stat[:company_id].eq(company[:id]))
        .join(products, Arel::Nodes::OuterJoin).on(company_online_stat[:company_id].eq(company[:id]))
        .join(prev_month_stat, Arel::Nodes::OuterJoin).on(prev_month_stat[:company_id].eq(company[:id]))
        .join(curr_month_stat, Arel::Nodes::OuterJoin).on(curr_month_stat[:company_id].eq(company[:id]))
        .where(company[:state].not_eq('deleted').and(company[:tmp].eq(false)))
    end
  end
end