мета-данные страницы
Различия
Здесь показаны различия между двумя версиями данной страницы.
| Следующая версия | Предыдущая версия | ||
|
doc_order_pays [04.05.2020 09:51] Анисютин создано |
doc_order_pays [26.10.2020 06:47] |
||
|---|---|---|---|
| Строка 1: | Строка 1: | ||
| - | ====== doc_order_pays ====== | ||
| - | |||
| - | **Оплата заказа** | ||
| - | |||
| - | ===== Назначение ===== | ||
| - | |||
| - | Репликационная таблица **doc_order_pays** из БД **Химчистка** хранит ссылки на документы оплаты заказов | ||
| - | |||
| - | ===== Поля таблицы ===== | ||
| - | |||
| - | <sxh sql> | ||
| - | 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 | ||
| - | </sxh> | ||
| - | |||
| - | ==== Оплата заказа 1 ==== | ||
| - | |||
| - | <sxh sql> | ||
| - | </sxh> | ||
| - | |||
| - | ==== Оплата заказов 1 ==== | ||
| - | |||
| - | <sxh sql> | ||
| - | 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 | ||
| - | </sxh> | ||
| - | |||
| - | ==== Оплата заказов 2 ==== | ||
| - | |||
| - | <sxh sql> | ||
| - | 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 | ||
| - | </sxh> | ||
| - | |||
| - | ==== Оплата заказов 3 ==== | ||
| - | |||
| - | <sxh sql> | ||
| - | 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 | ||
| - | </sxh> | ||
| - | |||
| - | ==== Оплата заказов 4 ==== | ||
| - | |||
| - | <sxh sql> | ||
| - | 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 | ||
| - | </sxh> | ||
| - | |||
| - | ==== Оплата заказов - проверка таблиц docs и docs_order ==== | ||
| - | |||
| - | <sxh sql> | ||
| - | 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' | ||
| - | </sxh> | ||
| - | |||
| - | |||
| - | |||
| - | ===== Использование таблицы ===== | ||
| - | |||
| - | |||
| - | [[:таблицы_данных|Назад]]\\ | ||