DOC_ORDER_PAYS

Оплата заказа

Репликационная таблица doc_order_pays из БД Химчистка хранит ссылки на документы оплаты заказов

select
  dop.id              , --
  dop.doc_order_id    , -- Оплачиваемый заказ
  dop.doc_type        , --
  dop.doc_kassa_id    , -- Платеж через кассу
  dop.doc_bank_id     , -- Платеж через банк
  dop.doc_card_id     , -- Платеж через карту
  dop.doc_bonus_id    , -- Платеж через бонус
  dop.doc_deposit_id  , -- Платеж через депозит
  dop.DEP_ID          , --
  dop.DEP_SRC_ID      , --
  dop.OWN_ID          , --
  dop.doc_id          , -- Документ об оплате
  dop.LAST_DEP_ID       --
from doc_order_pays dop

select d.doc_num,
       d.debet,
       d.kredit,
       sum(coalesce(d2.debet, 0) - coalesce(d2.kredit, 0))
from docs d
  left join docs_order dor on dor.doc_id = d.doc_id
  left join doc_order_pays dop on dop.doc_order_id = dor.id
  left join docs d2 on d2.doc_id = dop.doc_id
where d.doc_type = 5
group by 1,2,3

select coalesce(sum(iif(dop.doc_kassa_id   is not null, coalesce(d.debet,0) - coalesce(d.kredit,0),0)),0) as SumKassa,
       coalesce(sum(iif(dop.doc_bank_id    is not null, coalesce(d.debet,0) - coalesce(d.kredit,0),0)),0) as SumBank,
       coalesce(sum(iif(dop.doc_card_id    is not null, coalesce(d.debet,0) - coalesce(d.kredit,0),0)),0) as SumCard,
       coalesce(sum(iif(dop.doc_deposit_id is not null, coalesce(d.debet,0) - coalesce(d.kredit,0),0)),0) as SumDeposit,
       coalesce(sum(iif(dop.doc_bonus_id   is not null, coalesce(d.debet,0) - coalesce(d.kredit,0),0)),0) as SumBonus
from doc_order_pays dop
  inner join docs d on d.doc_id = dop.doc_id

select dop.doc_order_id, 
       sum(dk.debet) - sum(dk.kredit)  as kassa, 
       sum(dc.debet) - sum(dc.kredit)  as cards,
       sum(dd.debet) - sum(dd.kredit)  as deposits, 
       sum(dbs.debet)- sum(dbs.kredit) as bonuses, 
       sum(db.debet) - sum(db.kredit)  as banks
from doc_order_pays dop
  left join docs_kassa dk on dk.doc_id=dop.doc_kassa_id
  left join docs_card dc on dc.doc_id=dop.doc_card_id
  left join docs_deposit dd on dd.doc_id=dop.doc_deposit_id
  left join docs_bonus dbs on dbs.doc_id=dop.doc_bonus_id
  left join docs_bank db on db.doc_id=dop.doc_bank_id     
group by 1

select
        d.doc_id,
        dor.id,
        d.doc_date,      -- Дата заказ
        d.doc_num,       -- № заказа
        d.kredit,        -- начислено
        d.debet,          -- Уплачено
        d.kredit-d.debet as Delta, -- Задолженность
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_kassa dk on dk.doc_id=dop.doc_kassa_id
             where dop.doc_order_id=dor.id) as SumKassa,
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_bank dk on dk.doc_id=dop.doc_bank_id
             where dop.doc_order_id=dor.id) as SumBank,
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_card dk on dk.doc_id=dop.doc_card_id
             where dop.doc_order_id=dor.id) as SumCard,
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_deposit dk on dk.doc_id=dop.doc_deposit_id
             where dop.doc_order_id=dor.id) as SumDeposit,
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_bonus dk on dk.doc_id=dop.doc_bonus_id
             where dop.doc_order_id=dor.id) as SumBonus -- Платежи по заказу бонусами
--        (SumKassa+SumBank+SumCard+SumDeposit+SumBonus) as SumTotal
from  docs d
left join docs_order dor on dor.doc_id=d.doc_id
where d.doc_type=5 -- заказ
      and d.doc_id=614036

select 
        z.doc_id,
        --dor.id,
        z.doc_date,      -- Дата заказ
        z.doc_num,       -- № заказа
        z.kredit,        -- начислено
        z.debet,          -- Уплачено
        z.Delta, -- Задолженность
 	coalesce(z.SumKassa,0) as SumKassa,
        coalesce(z.SumBank,0) as SumBank,
        coalesce(z.SumCard,0) as SumCard,
        coalesce(z.SumDeposit,0) as SumDeposit,
        coalesce(z.SumBonus,0) as SumBonus,
        coalesce(SumKassa,0)+coalesce(SumBank,0)+coalesce(SumCard,0)+coalesce(SumDeposit,0)+coalesce(SumBonus,0) as SumTotal,
        z.debet-(coalesce(SumKassa,0)+coalesce(SumBank,0)+coalesce(SumCard,0)+coalesce(SumDeposit,0)+coalesce(SumBonus,0)) as differenc
from (select
        d.doc_id,
        --dor.id,
        d.doc_date,      -- Дата заказ
        d.doc_num,       -- № заказа
        d.kredit,        -- начислено
        d.debet,          -- Уплачено
        d.kredit-d.debet as Delta, -- Задолженность
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_kassa dk on dk.doc_id=dop.doc_kassa_id
             where dop.doc_order_id=dor.id) as SumKassa,
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_bank dk on dk.doc_id=dop.doc_bank_id
             where dop.doc_order_id=dor.id) as SumBank,
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_card dk on dk.doc_id=dop.doc_card_id
             where dop.doc_order_id=dor.id) as SumCard,
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_deposit dk on dk.doc_id=dop.doc_deposit_id
             where dop.doc_order_id=dor.id) as SumDeposit,
        (select sum(dk.debet) from doc_order_pays dop
             inner join docs_bonus dk on dk.doc_id=dop.doc_bonus_id
             where dop.doc_order_id=dor.id) as SumBonus
	from  docs d
	inner join docs_order dor on dor.doc_id=d.doc_id
--	left join docs_order dor on dor.doc_id=d.doc_id
--	where d.doc_type=5 -- заказ
      ) z
where z.doc_num='0000542'

Назад