SQL-запрос отчета

seller_plan_report.sql

WITH accessible_users AS (
  SELECT  DISTINCT public.users.* FROM public.users  WHERE
  public.users.id IN (50034, 33322, 11233)
)
SELECT
1 AS condition,
MAX(ac.title) AS accounting_center, -- Центр учета
MAX(users.name) AS seller, -- Продавец
MAX(clients.id) AS client_id, -- ИД Клиента
MAX(clients.title) AS client_title, -- Наименование клиента
MAX(company_main.region_id) AS main_region_id, -- Основной регион
-- Коммерческие регионы
string_agg(string_agg(r.name, ','), ',') over (partition by MAX(clients.id)) AS commercial_regions,
MAX(bi_s.title) AS scenario_title, -- Сценарий (из счета, по которому клиент отобрался)
MAX(bi_s.average_check) AS scenario_average_check, -- Плановая сумма продажи (из сценария)
NULL::integer AS agreement_amount, -- Договоренности (только для УСЛОВИЯ 2)
NULL::date AS agreement_payment_date,-- Дата оплаты договоренности (только для УСЛОВИЯ 2)
MAX(bills.account_number) AS bill_account_number, --Номер счета
(
  SELECT SUM(lots.summary_price_currency) FROM lots WHERE lots.bill_id = MAX(bills.id) GROUP BY bills.id
)::numeric(10,2) AS summary_price_currency, -- Счета (сумма)
(
  SELECT 
    CASE lots2.activation_attributes->'type'
      -- Тип оплаты лота - Рассрочка
      WHEN 'LotActivationDefferedPay'
        THEN
          -- По всем лотам с типом оплаты Рассрочка суммируем поля Сумма предполагаемой оплаты
          -- для которых Дата планируемой оплаты <= Дата до фильтра отчета
          (
            SELECT
            (
              (
                SELECT SUM(regexp_replace(
regexp_replace(lots.activation_attributes->'payment_price_1', '[ |руб][.]?', '', 'g'),
',', '.')::numeric(10,2)) FROM lots 
                WHERE bill_id = bills.id 
              )
              +
              (
                SELECT SUM(regexp_replace(
regexp_replace(lots.activation_attributes->'payment_price_2', '[ |руб][.]?', '', 'g'),
',', '.')::numeric(10,2)) FROM lots
                WHERE bill_id = bills.id 
              )
              +
              (
                SELECT SUM(regexp_replace(
regexp_replace(lots.activation_attributes->'payment_price_3', '[ |руб][.]?', '', 'g'),
',', '.')::numeric(10,2)) FROM lots
                WHERE bill_id = bills.id 
              )
              -
              (
                -- Сумма фактических оплат счета
                SELECT SUM(value) FROM payments WHERE payments.bill_id = bills.id
                 GROUP BY payments.bill_id
              )
            )                    

          )

      -- Тип оплаты лота - не Рассрочка
      ELSE
        (
          -- 'Сумма счета в валюте' - 'сумма сумм в валюте оплат по счету'
          SELECT
          (
            -- 'Сумма счета в валюте'
            SELECT SUM(lots3.summary_price_currency) FROM lots lots3
            WHERE lots3.bill_id = bills.id GROUP BY lots3.bill_id
           )
           -
           (
             -- Общая сумма оплат по счету
             SELECT SUM(value) FROM payments
             WHERE payments.bill_id = bills.id GROUP BY payments.bill_id
           )
          FROM lots lots4 
          WHERE lots4.id = lots2.id 
          
          -- AND ((lots4.pay_up_to)::date BETWEEN '2016-02-01' AND '2016-05-01')
        )
    END
  FROM lots lots2 WHERE lots2.bill_id = bills.id
  ORDER BY lots2.id
  LIMIT 1
) AS income_period_sum,-- Сумма приходов (план) за период (???)
 -- Сумма оплат (факт) за период
(select sum(value_currency) from payments where bill_id = MAX(bills.id)) AS payments_period_sum,
-- Дата следующей плановой оплаты
concat_ws(',',
  string_agg(to_date(MAX(lots.activation_attributes->'payment_date_1'), 'DD.MM.YYYY')::text, ',')
    OVER (partition BY MAX(bills.id)),
  string_agg(to_date(MAX(lots.activation_attributes->'payment_date_2'), 'DD.MM.YYYY')::text, ',')
    OVER (partition BY MAX(bills.id)),
  string_agg(to_date(MAX(lots.activation_attributes->'payment_date_3'), 'DD.MM.YYYY')::text, ',')
    OVER (partition BY MAX(bills.id))
) as next_payment_date,
(
  SELECT MIN(date) FROM operations o
  WHERE o.manager_id = MAX(users.id) AND o.result_id IS NULL AND o.date >= now()::date
  GROUP BY o.manager_id
)::date AS next_operation_date, -- Дата следующего действия
(
  SELECT ot.goal_id FROM operations o
  INNER JOIN operation_templates ot ON ot.id = o.template_id
  WHERE o.manager_id = MAX(users.id) AND o.result_id IS NULL AND o.date >= now()::date
  ORDER BY o.date DESC LIMIT 1
) AS next_operation_goal_id, -- Цель
(
  SELECT ops.date FROM operations ops
  WHERE
    ops.status_id = 3 -- статус действия Завершена
    AND
    ops.manager_id = MAX(users.id)
    AND
    ops.company_id = MAX(clients.main_company_id)
  ORDER BY ops.date DESC LIMIT 1
)::date AS last_finished_operation_date, -- Дата последнего завершенного действия
(
  SELECT ops.result_id FROM operations ops
  WHERE
    ops.status_id = 3 -- статус действия Завершена
    AND
    ops.manager_id = MAX(users.id)
    AND
    ops.company_id = MAX(clients.main_company_id)
  ORDER BY ops.date DESC limit 1
) AS last_finished_operation_result_id -- Результат последнего завершенного действия
FROM accessible_users users
  INNER JOIN user_accounting_centers uac ON uac.user_id = users.id
    INNER JOIN accounting_centers ac ON ac.id = uac.accounting_center_id
INNER JOIN client_specialization_users csu ON csu.user_id = users.id
  INNER JOIN client_specializations cs ON cs.id = csu.client_specialization_id
    INNER JOIN clients ON clients.id = cs.client_id
      INNER JOIN companies company_main ON company_main.id = clients.main_company_id
      INNER JOIN companies c ON c.client_id = clients.id
        LEFT JOIN company_region_commercials crc ON crc.company_id = c.source_id
          INNER JOIN regions r ON r.id = crc.region_id AND r.parent_id IS NOT NULL
      INNER JOIN bills ON bills.client_id = clients.id
        INNER JOIN lots ON lots.bill_id = bills.id
        LEFT JOIN scenarios bi_s ON bi_s.id = bills.scenario_id
        INNER JOIN payments p ON p.bill_id = bills.id 
WHERE
  bills.piss_off = false
  AND
  lots.user_id = users.id
  AND
  (
    (
      lots.activation_attributes->'type' IN ('LotActivationPrepay', 'LotActivationMoneta', 'LotActivationPrepayNewSite')
      
    )
    OR
    (
      lots.activation_attributes->'type' = 'LotActivationDefferedPay'
      
    )
  )
GROUP BY bills.id

UNION ALL
(        SELECT
  2 AS condition,
  MAX(ac.title) AS accounting_center, -- ЦУ
  MAX(users.name) AS seller, -- Продавец
  MAX(clients.id) AS client_id, -- ИД Клиента
  MAX(clients.title) AS client_title, -- Наименование клиента
  MAX(company_main.region_id) AS main_region_id,  -- Основной регион
  (
    select string_agg(reg.name, ',') from company_region_commercials crc_2
    join regions reg ON reg.id = crc_2.region_id AND reg.parent_id IS NOT NULL
    WHERE crc_2.company_id = MAX(c.source_id)
  ) AS commercial_regions, -- Коммерческие регионы
  MAX(op_s.title) AS scenario_title, -- Сценарий (наименование сценария из действия, по которому клиент отобрался)
  MAX(op_s.average_check) AS scenario_average_check, -- Плановая сумма продажи (из сценария)
  MAX(op.agreement_amount) AS agreement_amount, -- Договоренности (условие 2)
  MAX(op.agreement_payment_date::date) AS agreement_payment_date, -- Дата оплаты договоренности (условие 2)
  NULL AS bill_account_number, --Номер счета: (условие 1)
  NULL AS summary_price_currency, -- Счета (сумма): (условие 1)
  NULL AS income_period_sum, -- Сумма приходов (план) за период (???)
  NULL AS payments_period_sum, -- Сумма оплат (факт) за период: (условие 1)
  NULL AS next_payment_date, -- Дата следующей плановой оплаты: (условие 1)
  (
    SELECT date FROM operations op_1
    WHERE
      op_1.manager_id = MAX(users.id) AND
      op_1.result_id IS NULL AND
      op_1.date >= now()::date
    ORDER BY date DESC LIMIT 1
  )::date AS next_operation_date, -- Дата следующего действия
  (
    SELECT ot_2.goal_id FROM operations op_2
      INNER JOIN operation_templates ot_2 ON ot_2.id = op_2.template_id
    WHERE
      op_2.manager_id = max(users.id) AND
      op_2.result_id IS NULL AND
      op_2.date >= now()::date
    ORDER BY op_2.date DESC LIMIT 1
  ) AS next_operation_goal_id, -- Цель
  (
    SELECT ops.date FROM operations ops
    WHERE
      ops.status_id = 3 AND -- статус действия Завершена
      ops.manager_id = MAX(users.id) AND
      ops.company_id = MAX(clients.main_company_id)
    ORDER BY ops.date DESC LIMIT 1
  )::date AS last_finished_operation_date, -- Дата последнего завершенного действия
  (
    SELECT ops.result_id FROM operations ops
    WHERE
      ops.status_id = 3 AND -- статус действия Завершена
      ops.manager_id = MAX(users.id) AND
      ops.company_id = MAX(clients.main_company_id)
    ORDER BY ops.date DESC limit 1
  ) AS last_finished_operation_result_id -- Результат последнего завершенного действия
FROM accessible_users users
  INNER JOIN user_accounting_centers uac ON uac.user_id = users.id
    INNER JOIN accounting_centers ac ON ac.id = uac.accounting_center_id
INNER JOIN client_specialization_users csu ON csu.user_id = users.id
  INNER JOIN client_specializations cs ON cs.id = csu.client_specialization_id
    INNER JOIN clients ON clients.id = cs.client_id
      INNER JOIN companies company_main ON company_main.id = clients.main_company_id
      INNER JOIN companies c ON c.client_id = clients.id
        LEFT JOIN company_region_commercials crc ON crc.company_id = c.source_id
          INNER JOIN regions r ON r.id = crc.region_id AND r.parent_id IS NOT NULL
        INNER JOIN operations op ON op.company_id = c.id
          LEFT JOIN scenarios op_s ON op_s.id = op.scenario_id
WHERE
  op.specialization_id = cs.specialization_id
  AND
  op.status_id = 3 -- статус действия: Завершена
  AND
  (
    (
      op.agreement_amount IS NOT NULL
      
    )
    OR
    op.result_id = 2468 -- результат: Принимает решение
  )
GROUP BY clients.id
ORDER BY MAX(op.date) DESC
)
ORDER BY seller, client_id