есть 4 таблицы, 2 основные и 2 дочерние
1я основная
| Код |
CREATE TABLE ARRIVAL (
ID "INT" NOT NULL /* "INT" = INTEGER */,
SUPPLIER_ID "INT" /* "INT" = INTEGER */,
INVOICE_NUM STRING100 COLLATE PXW_CYRL /* STRING100 = VARCHAR(100) */,
DATE_INPUT "DATE" /* "DATE" = DATE */,
COEFF FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
SUMM_WO_NDS COMPUTED BY ((select SUMM_WO_NDS from SP_SUMM_ARRIVAL(ARRIVAL.ID))),
SUMM_RETAIL COMPUTED BY ((select SUMM_RETAIL from SP_SUMM_ARRIVAL(ARRIVAL.ID))),
SUM_VAT COMPUTED BY (((select SUMM_VAT from SP_SUMM_ARRIVAL(ARRIVAL.ID)))),
SUMM_W_VAT COMPUTED BY (((select SUMM_W_VAT from SP_SUMM_ARRIVAL(ARRIVAL.ID)))),
EXPENSE_NUMBERS STRING1000 COLLATE PXW_CYRL /* STRING1000 = VARCHAR(1000) */
);
|
1я дочерня
| Код |
CREATE TABLE ARRIVAL_DET (
ID "INT" NOT NULL /* "INT" = INTEGER */,
ARRIVAL_ID "INT" NOT NULL /* "INT" = INTEGER */,
PREPARAT_ID "INT" /* "INT" = INTEGER */,
AMOUNT FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
PRICE_WO_NDS FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
VAT FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
PRICE_W_VAT FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
PRICE_RETAIL FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
UNIT_ID "INT" /* "INT" = INTEGER */,
SUMM_WO_NDS COMPUTED BY ((PRICE_WO_NDS * AMOUNT)),
SUMM_RETAIL COMPUTED BY ((AMOUNT * PRICE_RETAIL)),
W_VAT BOOLEAN /* BOOLEAN = SMALLINT */,
EXP_AMOUNT FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
FILIALS STRING100 COLLATE PXW_CYRL /* STRING100 = VARCHAR(100) */,
SERIES STRING100 COLLATE PXW_CYRL /* STRING100 = VARCHAR(100) */
);
|
тебя должны интересовать поля ID, ARRIVAL_ID, AMOUNT, DATE_INPUT
это был приход
2я основная
| Код |
CREATE TABLE EXPENSE (
ID "INT" NOT NULL /* "INT" = INTEGER */,
ID_FILIAL "INT" /* "INT" = INTEGER */,
DATE_OUT "DATE" /* "DATE" = DATE */,
SUMM_RETAIL COMPUTED BY ((select SUMM_RETAIL from SP_SUMM_EXPENSE(EXPENSE.ID))),
REMARK STRING100 COLLATE PXW_CYRL /* STRING100 = VARCHAR(100) */,
INVOICE_NUM STRING25 COLLATE PXW_CYRL /* STRING25 = VARCHAR(25) */,
ARRIVAL_NUMBERS STRING1000 COLLATE PXW_CYRL /* STRING1000 = VARCHAR(1000) */
);
|
2я дочерняя
| Код |
CREATE TABLE EXPENSE_DET (
ID "INT" NOT NULL /* "INT" = INTEGER */,
EXPENSE_ID "INT" /* "INT" = INTEGER */,
PREPARAT_ID "INT" /* "INT" = INTEGER */,
AMOUNT FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
PRICE_RETAIL FLOAT1 /* FLOAT1 = DOUBLE PRECISION */,
UNIT_ID "INT" /* "INT" = INTEGER */,
SUMM_RETAIL COMPUTED BY (((AMOUNT * PRICE_RETAIL))),
SERIES STRING100 COLLATE PXW_CYRL /* STRING100 = VARCHAR(100) */
);
|
это был расход
теперь делаем вьюху
| Код |
CREATE VIEW VW_OBOROTKA(
PREPARAT_ID,
A_DATE,
AD_AMOUNT,
SUMM_RETAIL)
AS
SELECT
AD.PREPARAT_ID,
A.DATE_INPUT,
AD.AMOUNT,
AD.PRICE_RETAIL * AD.AMOUNT
FROM ARRIVAL_DET AD
LEFT JOIN ARRIVAL A ON (AD.arrival_ID = A.ID)
UNION ALL
SELECT
ED.PREPARAT_ID,
E.DATE_OUT,
-ED.AMOUNT,
-ED.PRICE_RETAIL * ED.AMOUNT
FROM EXPENSE_DET ED
LEFT JOIN EXPENSE E ON (ED.EXPENSE_ID = E.ID)
; |
она всё выгребает из прихода и расхода
вот тебе остатки:
| Код |
SELECT
P.ID,
P.Name,
P.PRODUCER,
P.SERIES,
P."TERM",
P.PRICE_W_VAT,
P.PRICE_RETAIL,
U.NAME as Unit_Name,
U.ID as UNIT_ID,
(SELECT SUM(O.AD_AMOUNT) FROM VW_OBOROTKA O
where (O.PREPARAT_ID = P.id) and (O.A_DATE <= :date)
GROUP BY O.PREPARAT_ID) AS OstatokAmount,
(SELECT SUM(O.SUMM_RETAIL) FROM VW_OBOROTKA O
where (O.PREPARAT_ID = P.id) and (O.A_DATE <= :date)
GROUP BY O.PREPARAT_ID) AS OstatokSumm
FROM PREPARATS P
left join UNITS U ON (U.ID = P.UNIT_ID)
|
теперь в оборотку выгребаем из этой вьюхи
| Код |
CREATE VIEW VW_OBOROTKA2(
PREPARAT_ID,
A_DATE,
AD_AMOUNT,
SUMM_RETAIL,
SUPPLIER_ID,
ID_FILIAL)
AS
SELECT
AD.PREPARAT_ID,
A.DATE_INPUT,
AD.AMOUNT,
AD.PRICE_RETAIL * AD.AMOUNT,
A.SUPPLIER_ID,
null
FROM ARRIVAL_DET AD
LEFT JOIN ARRIVAL A ON (AD.arrival_ID = A.ID)
UNION ALL
SELECT
ED.PREPARAT_ID,
E.DATE_OUT,
-ED.AMOUNT,
-ED.PRICE_RETAIL * ED.AMOUNT,
null,
e.ID_FILIAL
FROM EXPENSE_DET ED
LEFT JOIN EXPENSE E ON (ED.EXPENSE_ID = E.ID)
;
--------
SELECT
P.ID,
P.Name,
--Сальдо на начало
(SELECT SUM(O.AD_AMOUNT) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.A_DATE < :DateBegin) and (o.SUPPLIER_ID = :s_id) and (O.AD_AMOUNT > 0)
GROUP BY O.PREPARAT_ID) AS AmountOnBegin,
(SELECT SUM(O.SUMM_RETAIL) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.A_DATE < :DateBegin) and (o.SUPPLIER_ID = :s_id) and (O.AD_AMOUNT > 0)
GROUP BY O.PREPARAT_ID) AS PriceOnBegin,
--Обороты по приходу
(SELECT SUM(O.AD_AMOUNT) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.AD_AMOUNT > 0)and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd) and (o.SUPPLIER_ID = :s_id)
GROUP BY O.PREPARAT_ID) AS ArrivalAmount,
(SELECT SUM(O.SUMM_RETAIL) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.SUMM_RETAIL > 0)and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd) and (o.SUPPLIER_ID = :s_id)
GROUP BY O.PREPARAT_ID) AS ArrivalPrice,
--Обороты по расходу
(SELECT -SUM(O.AD_AMOUNT) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.AD_AMOUNT < 0) and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd) and (o.ID_FILIAL = :f_id)
GROUP BY O.PREPARAT_ID) AS ExpenseAmount,
(SELECT -SUM(O.SUMM_RETAIL) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.SUMM_RETAIL < 0) and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd) and (o.ID_FILIAL = :f_id)
GROUP BY O.PREPARAT_ID) AS ExpensePrice
FROM PREPARATS P
WHERE
((SELECT SUM(O.AD_AMOUNT) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.A_DATE < :DateBegin) and (o.SUPPLIER_ID = :s_id) and (O.AD_AMOUNT > 0)
GROUP BY O.PREPARAT_ID) is not null)
or
((SELECT SUM(O.SUMM_RETAIL) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.A_DATE < :DateBegin) and (o.SUPPLIER_ID = :s_id) and (O.AD_AMOUNT > 0)
GROUP BY O.PREPARAT_ID) is not null)
or
((SELECT SUM(O.AD_AMOUNT) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.AD_AMOUNT > 0)and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd) and (o.SUPPLIER_ID = :s_id)
GROUP BY O.PREPARAT_ID) is not null)
or
((SELECT -SUM(O.AD_AMOUNT) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.AD_AMOUNT < 0) and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd) and (o.ID_FILIAL = :f_id)
GROUP BY O.PREPARAT_ID) is not null)
or
((SELECT -SUM(O.AD_AMOUNT) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.AD_AMOUNT < 0) and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd) and (o.ID_FILIAL = :f_id)
GROUP BY O.PREPARAT_ID) is not null)
or
((SELECT -SUM(O.SUMM_RETAIL) FROM VW_OBOROTKA2 O
where (O.PREPARAT_ID = P.id) and (O.SUMM_RETAIL < 0) and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd) and (o.ID_FILIAL = :f_id)
GROUP BY O.PREPARAT_ID) is not null)
|
вот такая канитель |
((