Код: Выделить всё
REATE PROCEDURE GET_ALL_DATE_SUM (
FIRM INTEGER,
SOME_CUR NUMERIC(16,4))
RETURNS (
DOC_ID INTEGER,
DOC_TO_ID INTEGER,
DOC_USER_ID INTEGER,
DOC_TYPE INTEGER,
DOC_SUM NUMERIC(16,4),
OT_SUM NUMERIC(16,4),
OT_DATE DATE,
OPL_SUM NUMERIC(16,4),
OPL_DATE DATE,
END_DATE DATE,
DOC_AGENT VARCHAR(100),
DOC_NO VARCHAR(32),
THE_DATE DATE,
DOC_CUR_ID INTEGER,
DOC_CUR_VAL NUMERIC(16,4),
OLD_END_DATE DATE)
AS
begin
FOR SELECT DISTINCT i.id, i.TO_ID, i.USER_ID, i.DOC_TYPE, cast((i.doc_sum*i.currency_value) as numeric(16,2)) r_sum, cast(ot.DOC_SUM as numeric(16,2)), ot.THE_DATE, cast(opl.DOC_SUM as numeric(16,2)), opl.THE_DATE, i.DOC_NO, i.THE_DATE, i.CURRENCY_ID, i.CURRENCY_VALUE,i.END_date
FROM invoices i, pid p, GET_DOC_SUM_SEB ot, GET_DOC_SUM_SEB_OPL opl
WHERE i.interval_id <> 9000
AND i.doc_type in (1, 5, 11)
AND ot.PARENT_ID=i.id
AND i.CURRENCY_ID = 1
AND opl.PARENT_ID=i.id
AND p.pid=i.id
AND i.from_id in (SELECT id FROM customer WHERE type_id=1 AND firm = :firm)
UNION ALL
SELECT DISTINCT i.id, i.TO_ID, i.USER_ID, i.DOC_TYPE, cast((i.doc_sum*:SOME_CUR) as numeric(16,2)) r_sum, cast(ot.DOC_SUM as numeric(16,2)), ot.THE_DATE, cast(opl.DOC_SUM as numeric(16,2)), opl.THE_DATE, i.DOC_NO, i.THE_DATE, i.CURRENCY_ID, i.CURRENCY_VALUE,i.END_date
FROM invoices i, pid p, GET_DOC_SUM_SEB ot, GET_DOC_SUM_SEB_OPL opl
WHERE i.interval_id <> 9000
AND i.doc_type in (1, 5, 11)
AND ot.PARENT_ID=i.id
AND i.CURRENCY_ID <> 1
AND opl.PARENT_ID=i.id
AND p.pid=i.id
AND i.from_id in (SELECT id FROM customer WHERE type_id=1 AND firm = :firm)
INTO :DOC_ID, :DOC_TO_ID, :DOC_USER_ID, :DOC_TYPE, :DOC_SUM, :OT_SUM, :OT_DATE, :OPL_SUM, :OPL_DATE, :DOC_NO, :THE_DATE, :DOC_CUR_ID, :DOC_CUR_VAL,:OLD_END_DATE
DO BEGIN
END_DATE=null ;
if ((OT_SUM = OPL_SUM) AND (OT_SUM = DOC_SUM) AND (DOC_SUM = OPL_SUM)) then
begin
if(OT_DATE > OPL_DATE) then END_DATE = OT_DATE;
else END_DATE = OPL_DATE;
end
suspend;
END
/* Procedure Text */
endКод: Выделить всё
?CREATE PROCEDURE GET_DOC_SUM_SEB
RETURNS (
PARENT_ID INTEGER,
DOC_SUM NUMERIC(16,4),
THE_DATE DATE)
AS
begin
FOR SELECT DISTINCT h.PARENT_ID out_id, sum(i.doc_sum) out_sum, max(i.the_date) out_date
FROM GET_DOC_HISTORY h, INVOICES I
WHERE h.ID=i.id
AND i.doc_type in (1, 11)
AND i.interval_id <> 9000
GROUP BY h.parent_ID
INTO :PARENT_ID, :DOC_SUM, :THE_DATE
DO BEGIN
suspend;
END
end
CREATE PROCEDURE GET_DOC_SUM_SEB_OPL
RETURNS (
PARENT_ID INTEGER,
DOC_SUM NUMERIC(16,4),
THE_DATE DATE)
AS
begin
FOR SELECT DISTINCT h.PARENT_ID out_id, sum(i.doc_sum) out_sum, max(i.the_date) out_date
FROM GET_DOC_HISTORY h, INVOICES I
WHERE h.ID=i.id
AND i.doc_type in (SELECT id FROM DOC_LIST WHERE is_mny = 1 AND is_in=1)
AND i.interval_id <> 9000
GROUP BY h.parent_ID
INTO :PARENT_ID, :DOC_SUM, :THE_DATE
DO BEGIN
suspend;
END
end
CREATE PROCEDURE GET_DOC_HISTORY
RETURNS (
PARENT_ID INTEGER,
ID INTEGER)
AS
DECLARE VARIABLE I INTEGER;
begin
FOR
SELECT ID
FROM invoices
INTO :PARENT_ID
DO
BEGIN
EXECUTE PROCEDURE GET_ROOT PARENT_ID RETURNING_VALUES ID;
IF (ID IS NULL) THEN ID = PARENT_ID;
I = ID;
SUSPEND;
IF (I is not null) THEN
BEGIN
FOR
SELECT ID_OUT
FROM GET_DOC_HIST(:I)
INTO :ID
DO BEGIN
SUSPEND;
END
END
END
end
CREATE PROCEDURE GET_DOC_HIST (
ID_IN INTEGER)
RETURNS (
ID_OUT INTEGER)
AS
DECLARE VARIABLE ID_TEMP INTEGER;
begin
FOR SELECT ID FROM pid WHERE pid = :ID_IN INTO :ID_TEMP
DO BEGIN
ID_OUT = ID_TEMP;
SUSPEND;
IF (ID_TEMP IS NOT NULL) THEN
FOR
SELECT ID_OUT
FROM GET_DOC_HIST(:ID_TEMP)
INTO :ID_OUT
DO IF (ID_OUT IS NOT NULL) THEN SUSPEND;
END
end
CREATE TABLE INVOICES (
ID IDN /* INTEGER NOT NULL */,
FROM_ID IDN /* INTEGER NOT NULL */,
TO_ID IDN /* INTEGER NOT NULL */,
DOC_TYPE SMALLINT NOT NULL,
THE_DATE DATE NOT NULL,
INTERVAL_ID SMALLINT NOT NULL,
DOC_SUM MNY /* NUMERIC(16,4) */ DEFAULT 0,
CURRENCY_ID SMALLINT NOT NULL,
CURRENCY_VALUE NUMERIC(16,4) NOT NULL,
DSC VARCHAR(200),
FIRM_ID SMALLINT NOT NULL,
DOC_NO VARCHAR(32),
USER_ID INTEGER NOT NULL,
RESPONSIBLE_ID INTEGER,
END_DATE DATE
);
CREATE TABLE PID (
ID IDN /* INTEGER NOT NULL */,
PID IDN /* INTEGER NOT NULL */
);Код: Выделить всё
Возникает вопрос: понимаем что есть метаданные?
CREATE TABLE DOC_LIST (
ID IDN /* INTEGER NOT NULL */,
PID IDN /* INTEGER NOT NULL */,
NAME VARCHAR(20) NOT NULL,
IS_IN SMALLINT NOT NULL,
FULLNAME VARCHAR(60),
NEED_PARENT_ID SMALLINT NOT NULL,
DOC_INTERVAL SMALLINT NOT NULL,
CREATEPART_ID SMALLINT NOT NULL,
IS_MNY SMALLINT NOT NULL,
IS_LOCAL SMALLINT NOT NULL
);
ALTER TABLE DOC_LIST ADD PRIMARY KEY (ID);
CREATE TABLE CUSTOMER (
ID IDN /* INTEGER NOT NULL */,
PID IDN /* INTEGER NOT NULL */,
NAME VARCHAR(20) NOT NULL,
FIRM SMALLINT,
TYPE_ID SMALLINT NOT NULL,
FULLNAME VARCHAR(60),
ADRES VARCHAR(50),
PHONE VARCHAR(50),
D_NSV VARCHAR(16),
D_NPID VARCHAR(16),
OKPO VARCHAR(20),
RS VARCHAR(20),
BANK VARCHAR(64),
ADRES_UR VARCHAR(50),
MFO VARCHAR(8),
DIRECTOR VARCHAR(64),
EMAIL VARCHAR(100)
);
ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);
CREATE INDEX IDX_CUSTOMER ON CUSTOMER (PID);
ALTER INDEX IDX_CUSTOMER INACTIVE;