fuck.sql

SELECT
  *
FROM profiles_courier

LEFT JOIN (SELECT    ----   ДЖойним заказы с выручкой/предоплатой за последние 7 дней исключая now() - 1 day
  courier_id,
  count(orders.id) cashorders, -- Всего заказов
  sum(declared_value) cashsum, -- Общая сумма
  max(declared_value) maxcash  -- Максимальная сумма в 1 заказе

  FROM orders_order orders
    JOIN (SELECT order_id, declared_value
          FROM orders_orderprice) price on orders.id = price.order_id

    JOIN (SELECT order_id id,extra_service_template_id
          FROM orders_orderextraservice
          WHERE extra_service_template_id in (5,6) ) service USING (id)

    JOIN (SELECT order_id AS hashed_id,status,datetime
          FROM orders_orderstatus
          WHERE status = 'completed_successfully'
            AND datetime + '1 day'::INTERVAL < now() ) status USING (hashed_id)

  WHERE created_at_server > now() - '7 day'::INTERVAL
  GROUP BY courier_id
) completionscash on completionscash.courier_id=profiles_courier.id

LEFT JOIN (SELECT    ----   ДЖойним заказы с покупкой за свой счет за последние 7 дней исключая now() - 1 day
  courier_id,
  count(orders.id) buyorders,  -- Всего заказов
  sum(declared_value) buysum,   -- Общая сумма
  max(declared_value) maxbuy    -- Максимальная сумма в 1 заказе

  FROM orders_order orders
    JOIN (SELECT order_id, declared_value
          FROM orders_orderprice) price on orders.id = price.order_id

    JOIN (SELECT order_id id,extra_service_template_id
          FROM orders_orderextraservice
          WHERE extra_service_template_id = 4 ) service USING (id)

    JOIN (SELECT order_id AS hashed_id,status,datetime
          FROM orders_orderstatus
          WHERE status = 'completed_successfully'
            AND datetime + '1 day'::INTERVAL < now() ) status USING (hashed_id)

  WHERE created_at_server > now() - '7 day'::INTERVAL
  GROUP BY courier_id
) completionbuy on completionbuy.courier_id=profiles_courier.id

LEFT JOIN (SELECT    ----   ДЖойним выполненые заказы за все время
  courier_id,
  count(orders.id) totalorders

  FROM orders_order orders

    JOIN (SELECT order_id AS hashed_id,status
          FROM orders_orderstatus
          WHERE status = 'completed_successfully') status USING (hashed_id)
  GROUP BY courier_id
) completiontotal on completiontotal.courier_id=profiles_courier.id

LEFT JOIN (SELECT    ----   ДЖойним выполненые заказы за последние 7 дней исключая now() - 1 day
  courier_id,
  count(orders.id) totalorders14d

  FROM orders_order orders

    JOIN (SELECT order_id AS hashed_id,status
          FROM orders_orderstatus
          WHERE status = 'completed_successfully') status USING (hashed_id)
  WHERE orders.created_at_server > now() - '14 day'::INTERVAL
    AND orders.created_at_server + '1 day'::INTERVAL < now()
  GROUP BY courier_id
) completiontotal14d on completiontotal14d.courier_id=profiles_courier.id

JOIN   -- Джойним др. Джойн строгий, это автоматом исключит тех, кто не указал др
  (SELECT courier_id,less_25,more_29,more_25
    FROM courierprofile_birthday) birthday on birthday.courier_id=id

JOIN  -- Джойним нормальные доки
  (SELECT courier_id,count(DISTINCT doc_type) doc_count
    FROM courierprofile_documents
      WHERE doc_type IN ('Паспорт РФ','Автоправа','Пенсионное удостоверение',
                         'Паспорт другого государства','Свидетельство от рождении','Заграничный паспорт')
  GROUP BY courier_id) documents on documents.courier_id=id

JOIN  -- Джойним всякие балансы
  (SELECT courier_id,is_in24hours,balance,last_negative_balance_date,positive_date
    FROM score_from_courier_balance
  ) balance on balance.courier_id=id

LEFT JOIN  -- Заджойнм мобилу нестрогим джойном
  (SELECT DISTINCT on (courier_id)
     courier_id,device
    FROM couriers_couriermobileaccount) mobile on mobile.courier_id=id

LEFT JOIN   ---- Джойним дикую херню по фининсам
  (SELECT
  id,
  name,
  sum((intime=FALSE)::INTEGER) longpay


FROM
  (
SELECT
  negtranses.id,
  negtranses.name,
  negtranses.created_at,
  negtranses.amount,
  negtranses.balance_after_transaction,
  negtranses.transaction_id,
  postranses.created_at,
  postranses.amount,
  postranses.balance_after_transaction,
  postranses.transaction_id,
  (postranses.created_at-negtranses.created_at < '24 hour') intime

  FROM
(SELECT
  couriers.name,
  couriers.id,
  finacc.created_at,
  amount,
  balance_after_transaction,
  transaction_id

FROM profiles_courier couriers

JOIN
  (SELECT
    finacc.created_at,
    finacc.amount,
    finacc.balance_after_transaction,
    finacc.transaction_id,
    fincheck.courier_id
   FROM finances_accountingentry finacc
   JOIN finances_checkingaccount fincheck ON finacc.account_id = fincheck.id) finacc on finacc.courier_id=couriers.id

WHERE finacc.created_at > now() - '2 day'::INTERVAL
AND balance_after_transaction < -100) negtranses

join

(SELECT
  couriers.name,
  couriers.id,
  finacc.created_at,
  amount,
  balance_after_transaction,
  transaction_id

FROM profiles_courier couriers

left JOIN
  (SELECT
    finacc.created_at,
    finacc.amount,
    finacc.balance_after_transaction,
    finacc.transaction_id,
    fincheck.courier_id
   FROM finances_accountingentry finacc
   JOIN finances_checkingaccount fincheck ON finacc.account_id = fincheck.id) finacc on finacc.courier_id=couriers.id

WHERE finacc.created_at > now() - '2 day'::INTERVAL
AND balance_after_transaction > -100) postranses
      on negtranses.id=postranses.id
         AND negtranses.created_at<postranses.created_at
         AND negtranses.balance_after_transaction + postranses.amount = postranses.balance_after_transaction) last28dtrans

GROUP BY id,name)  longpayments on longpayments.id=profiles_courier.id

WHERE balance > -50  -- баланс >-50
  AND totalorders14d > 1  -- c -1день до -14 день выполнен 2 заказ
  AND doc_count > 0   -- doc_count > 0
  AND profiles_courier.created_at < now() - '14 days'::INTERVAL  -- зареган более чем 14 дней назад
  AND (longpayments.longpay < 1 or longpayments.longpay IS NULL )  -- Не было просрочек или транзакций

  AND (more_25 = TRUE  -- возраст больше 29
      OR completionbuy.maxbuy > 1500   -- делал выкуп за свой счет >2000р
      OR completionscash.cashsum > 4500   -- уже сделал успешно заказ с кешем на 4500руб
      OR completiontotal.totalorders > 30    -- сделал 30 заказов
      OR (completiontotal.totalorders > 10    -- сделал 10 но больше 60 дней назад зареган
          AND profiles_courier.created_at > now() - '60 days'::INTERVAL) )
;