_database_exporting_script.txt

/******************************************************************************/
/***          Generated by IBExpert 2009.06.05 28.04.2016 12:00:14          ***/
/******************************************************************************/



/******************************************************************************/
/***                                Domains                                 ***/
/******************************************************************************/

CREATE DOMAIN NO_YES AS
VARCHAR(1)
CHECK (VALUE IN ('Y', 'N'));



/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE GENERATOR GEN_LOG_FILE_ID;
SET GENERATOR GEN_LOG_FILE_ID TO 141;

CREATE GENERATOR IBE$LOG_TABLES_GEN;
SET GENERATOR IBE$LOG_TABLES_GEN TO 19;



/******************************************************************************/
/***                               Exceptions                               ***/
/******************************************************************************/

CREATE EXCEPTION ANOUTHER_DATA '''Товар отсутствует''';

CREATE EXCEPTION ERROR 'НЕВЕРНО ВВЕДЕНЫ ДАННЫЕ!';

CREATE EXCEPTION KOL_MUST_BE_POSITIVE 'кол должно быфть больше 0';

CREATE EXCEPTION NOT_ENOUGH_GOODS 'Required more goods';

CREATE EXCEPTION WRONG_DATA_KOL_EXCEPTION '''Введено отрицательное количество''';



SET TERM ^ ; 



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/

CREATE PROCEDURE ADD_ORDER (
    GOODS TYPE OF COLUMN TOVAR.NOMENCLATURE,
    AMOUNT TYPE OF COLUMN OPERATION.KOL,
    AGENT_NAME TYPE OF COLUMN AGENT.NAME_AG,
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN,
    TYPE_OP TYPE OF COLUMN OPERATION.TYPEOP,
    PRICE TYPE OF COLUMN OPERATION.PRICE,
    DEL_TIME TYPE OF COLUMN DELIVERY.TIME_A,
    DEL_DATE TYPE OF COLUMN OPERATION.POST_DATE,
    DELIV TYPE OF COLUMN OPERATION.DELIVERY)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE AGENT_NAMES
RETURNS (
    ANAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE AMOUNT_ITEM_WH (
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN,
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE)
RETURNS (
    AMOUNT TYPE OF COLUMN TOVAR_WH.KOL)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE CHOOSE_VEHICLE (
    DEL_DATE TYPE OF COLUMN OPERATION.POST_DATE,
    DEL_TIME TYPE OF COLUMN DELIVERY.TIME_A,
    CAP TYPE OF COLUMN VEHICLE.CAPACITY)
RETURNS (
    CAR_NUM TYPE OF COLUMN VEHICLE.NUM_V)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE COUNT_EMPTY_AGENTS
RETURNS (
    "COUNT" INTEGER)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE COUNTPOST (
    ID_TOV CHAR(10))
RETURNS (
    CNT INTEGER)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GET_AG (
    Y INTEGER)
RETURNS (
    AG_NAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GET_FIRST_QARTER_AGENTS (
    Y INTEGER)
RETURNS (
    AGENT_NAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GET_TOV (
    AG_NAME CHAR(20))
RETURNS (
    TOV_NAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GET_TOVAR_BY_PRICE (
    PRODUCT_PRICE INTEGER)
RETURNS (
    TOVAR_NAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GET_WH (
    WH_NAME CHAR(20))
RETURNS (
    RET_NAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GET_WH_BY_PRODUCTS (
    PRODUCT_NAME CHAR(20))
RETURNS (
    WAREHOUSE_NAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GETCOUNT (
    WHNAME CHAR(20))
RETURNS (
    KOL INTEGER)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GETCOUNT2 (
    WHNAME CHAR(20))
RETURNS (
    NAIMENS CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GETNOMENLIST1 (
    AGNAME CHAR(20))
RETURNS (
    NOMEN CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE GETWHLIST2 (
    WHNAME CHAR(20))
RETURNS (
    TOVARLIST CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE ITEM_NAMES
RETURNS (
    ANAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE ITEMBYCOUNT (
    COU NUMERIC(15,2))
RETURNS (
    TOVARNAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE ITEMS_NAMES_WH (
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN)
RETURNS (
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE NAME_AGENT (
    KVO NUMERIC(18,2))
RETURNS (
    N_AG CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE NEW_PROCEDURE (
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN,
    DATE_FROM TYPE OF COLUMN OPERATION.POST_DATE,
    DATE_TO TYPE OF COLUMN OPERATION.POST_DATE)
RETURNS (
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE,
    KOL TYPE OF COLUMN OPERATION.KOL)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE NEWTASK2 (
    I CHAR(1))
RETURNS (
    S VARCHAR(30))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE P1 (
    TOVARNAME CHAR(20),
    USER_ID CHAR(20))
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE RECALCULATION
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE SUM_OST (
    NUM_TOVAR CHAR(10),
    T_TOWN CHAR(10))
RETURNS (
    SUM_OST NUMERIC(15,0))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TASK_1 (
    I TYPE OF COLUMN AGENT.NAME_AG)
RETURNS (
    RNAME CHAR(31))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TASK_2
RETURNS (
    RNAME CHAR(31))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TASK_3 (
    I TYPE OF COLUMN TOVAR.NOMENCLATURE)
RETURNS (
    RNAME CHAR(31))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TASK1 (
    I INTEGER)
RETURNS (
    S VARCHAR(30))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TASK1_PROCEDURE (
    N INTEGER)
RETURNS (
    S CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TASK2 (
    FROMD DATE,
    TOD DATE)
RETURNS (
    NOM CHAR(20),
    OBOROT INTEGER)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TOV_OB (
    P_YEAR INTEGER,
    TOV CHAR(20))
RETURNS (
    SUM1 NUMERIC(15,2))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TOVARBYAGENT (
    AGENTNAME CHAR(20))
RETURNS (
    TOVARNAME CHAR(20),
    TOVDATE DATE)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TOVARBYDATE (
    DATE1 DATE,
    DATE2 DATE)
RETURNS (
    TOVARNAME CHAR(20),
    KOL1 NUMERIC(15,2),
    KOL2 NUMERIC(15,2))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TOVARS_BY_WH (
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN)
RETURNS (
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE TOVARS_LESS_COST (
    PRICE TYPE OF COLUMN OPERATION.PRICE)
RETURNS (
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE)
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE WAREHOUSES (
    NAIMEN CHAR(20))
RETURNS (
    NAIMENS CHAR(20))
AS
BEGIN
  SUSPEND;
END^


CREATE PROCEDURE WARENHOUSE_NAMES
RETURNS (
    ANAME CHAR(20))
AS
BEGIN
  SUSPEND;
END^



SET TERM ; ^



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE AGENT (
    ID_AG    CHAR(10) NOT NULL,
    NAME_AG  CHAR(20) NOT NULL,
    ADDRESS  CHAR(20) NOT NULL,
    PHONE    CHAR(10)
);

CREATE TABLE DELIVERY (
    ID_D    INTEGER NOT NULL,
    ID_OP   INTEGER NOT NULL,
    TIME_A  TIME NOT NULL,
    TIME_D  TIME NOT NULL,
    ID_V    CHAR(10) NOT NULL
);

CREATE TABLE IBE$LOG_BLOB_FIELDS (
    LOG_TABLES_ID   NUMERIC(18,0) NOT NULL,
    FIELD_NAME      VARCHAR(67) NOT NULL,
    OLD_CHAR_VALUE  VARCHAR(8000),
    NEW_CHAR_VALUE  VARCHAR(8000),
    OLD_BLOB_VALUE  BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    NEW_BLOB_VALUE  BLOB SUB_TYPE 0 SEGMENT SIZE 80
);

CREATE TABLE IBE$LOG_FIELDS (
    LOG_TABLES_ID  NUMERIC(18,0) NOT NULL,
    FIELD_NAME     VARCHAR(67) NOT NULL,
    OLD_VALUE      VARCHAR(255),
    NEW_VALUE      VARCHAR(255)
);

CREATE TABLE IBE$LOG_KEYS (
    LOG_TABLES_ID  NUMERIC(18,0) NOT NULL,
    KEY_FIELD      VARCHAR(67) NOT NULL,
    KEY_VALUE      VARCHAR(255)
);

CREATE TABLE IBE$LOG_TABLES (
    ID          NUMERIC(18,0) NOT NULL,
    TABLE_NAME  VARCHAR(67) NOT NULL,
    OPERATION   VARCHAR(1) NOT NULL,
    DATE_TIME   TIMESTAMP NOT NULL,
    USER_NAME   VARCHAR(67) NOT NULL
);

CREATE TABLE INOUT (
    WHNAME   CHAR(20),
    OPDATE   DATE,
    KOL      NUMERIC(15,2),
    SUMNUM   NUMERIC(15,2),
    USER_ID  CHAR(20)
);

CREATE TABLE LAST_DATE (
    L_DATE  DATE NOT NULL
);

CREATE TABLE LIBRARY (
    L_I   INTEGER,
    ADR   VARCHAR(40),
    NAME  VARCHAR(20)
);

CREATE TABLE LOG_FILE (
    ID      INTEGER NOT NULL,
    INFORM  CHAR(200),
    DDATA   DATE
);

CREATE TABLE LOG_INFO (
    ID      INTEGER,
    INFORM  VARCHAR(200),
    DDATE   DATE
);

CREATE TABLE OPERATION (
    ID          INTEGER NOT NULL,
    ID_TOVAR    CHAR(10) NOT NULL,
    ID_AG       CHAR(10) NOT NULL,
    ID_WH       CHAR(10) NOT NULL,
    TYPEOP      CHAR(1) NOT NULL,
    KOL         NUMERIC(15,2) NOT NULL,
    PRICE       NUMERIC(15,2),
    POST_DATE   DATE,
    DELIVERY    NO_YES NOT NULL,
    ORDER_DATE  DATE NOT NULL
);

CREATE TABLE TOVAR (
    ID_TOVAR      CHAR(10) NOT NULL,
    NOMENCLATURE  CHAR(20) NOT NULL,
    VOLUME        INTEGER NOT NULL
);

CREATE TABLE TOVAR_WH (
    ID        INTEGER NOT NULL,
    ID_WH     CHAR(10) NOT NULL,
    ID_TOVAR  CHAR(10) NOT NULL,
    KOL       NUMERIC(15,2)
);

CREATE TABLE VEHICLE (
    ID_V      CHAR(10) NOT NULL,
    NUM_V     CHAR(10) NOT NULL,
    CAPACITY  INTEGER NOT NULL
);

CREATE TABLE WAREHOUSE (
    ID_WH   CHAR(10) NOT NULL,
    NAIMEN  CHAR(20) NOT NULL,
    TOWN    CHAR(10)
);

INSERT INTO AGENT (ID_AG, NAME_AG, ADDRESS, PHONE) VALUES ('A1                                      ', 'Reliable                                                                        ', 'Azov                                                                            ', '55-55-55                                ');
INSERT INTO AGENT (ID_AG, NAME_AG, ADDRESS, PHONE) VALUES ('A2                                      ', 'OOO Partner                                                                     ', 'Rostov                                                                          ', '2-34-56-78                              ');
INSERT INTO AGENT (ID_AG, NAME_AG, ADDRESS, PHONE) VALUES ('A3                                      ', 'Astra                                                                           ', 'Rostov                                                                          ', '2-22-22-22                              ');
INSERT INTO AGENT (ID_AG, NAME_AG, ADDRESS, PHONE) VALUES ('A4                                      ', 'Tanais                                                                          ', 'Taganrog                                                                        ', '3-45-67                                 ');
INSERT INTO AGENT (ID_AG, NAME_AG, ADDRESS, PHONE) VALUES ('A5                                      ', 'OAO Leader                                                                      ', 'Bataysk                                                                         ', '45-45-67                                ');
INSERT INTO AGENT (ID_AG, NAME_AG, ADDRESS, PHONE) VALUES ('A6                                      ', 'Math Institute                                                                  ', 'Rostov                                                                          ', '111                                     ');

COMMIT WORK;

INSERT INTO TOVAR (ID_TOVAR, NOMENCLATURE, VOLUME) VALUES ('T1                                      ', 'Fax paper                                                                       ', 10);
INSERT INTO TOVAR (ID_TOVAR, NOMENCLATURE, VOLUME) VALUES ('T2                                      ', 'Folders                                                                         ', 5);
INSERT INTO TOVAR (ID_TOVAR, NOMENCLATURE, VOLUME) VALUES ('T3                                      ', 'Pencils(10 it.)                                                                 ', 3);
INSERT INTO TOVAR (ID_TOVAR, NOMENCLATURE, VOLUME) VALUES ('T4                                      ', 'Postcard                                                                        ', 1);
INSERT INTO TOVAR (ID_TOVAR, NOMENCLATURE, VOLUME) VALUES ('T5                                      ', 'Notebooks(12 it.)                                                               ', 2);
INSERT INTO TOVAR (ID_TOVAR, NOMENCLATURE, VOLUME) VALUES ('T6                                      ', 'Notepad                                                                         ', 3);
INSERT INTO TOVAR (ID_TOVAR, NOMENCLATURE, VOLUME) VALUES ('T7                                      ', 'Souvenir-octopus                                                                ', 6);
INSERT INTO TOVAR (ID_TOVAR, NOMENCLATURE, VOLUME) VALUES ('T8                                      ', 'Notebook(24 sh.)                                                                ', 2);

COMMIT WORK;

INSERT INTO WAREHOUSE (ID_WH, NAIMEN, TOWN) VALUES ('С1                                     ', 'Alex_s Closet                                                                   ', 'Frauden                                 ');
INSERT INTO WAREHOUSE (ID_WH, NAIMEN, TOWN) VALUES ('С2                                     ', 'Dima_s Secret                                                                   ', 'Beijing                                 ');
INSERT INTO WAREHOUSE (ID_WH, NAIMEN, TOWN) VALUES ('С3                                     ', 'Random Warehouse                                                                ', 'Fobos                                   ');
INSERT INTO WAREHOUSE (ID_WH, NAIMEN, TOWN) VALUES ('С4                                     ', 'God Bless                                                                       ', 'Olymp                                   ');
INSERT INTO WAREHOUSE (ID_WH, NAIMEN, TOWN) VALUES ('C5                                      ', 'Dima_s Warehouse                                                                ', 'Nizza                                   ');

COMMIT WORK;

INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (1, 'T1                                      ', 'A1                                      ', 'C5                                      ', 'R   ', 5, 0, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (2, 'T1                                      ', 'A1                                      ', 'C5                                      ', 'R   ', 5, 0, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (3, 'T1                                      ', 'A1                                      ', 'C5                                      ', 'R   ', 5, 0, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (4, 'T1                                      ', 'A1                                      ', 'C5                                      ', 'R   ', 5, 0, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (5, 'T1                                      ', 'A1                                      ', 'C5                                      ', 'R   ', 5, 0, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (6, 'T1                                      ', 'A1                                      ', 'C5                                      ', 'R   ', 5, 0, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (64, 'T7                                      ', 'A3                                      ', 'С1                                     ', 'A   ', -500, 0, '2016-04-21', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (65, 'T7                                      ', 'A3                                      ', 'С1                                     ', 'A   ', -500, 0, '2016-04-21', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (69, 'T2                                      ', 'A3                                      ', 'С2                                     ', 'R   ', 100, 100, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (72, 'T2                                      ', 'A3                                      ', 'С4                                     ', 'R   ', 40, 12, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (75, 'T3                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 100, 150, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (77, 'T3                                      ', 'A1                                      ', 'С2                                     ', 'R   ', 100, 10, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (80, 'T2                                      ', 'A6                                      ', 'C5                                      ', 'R   ', 50, 12, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (83, 'T8                                      ', 'A1                                      ', 'С4                                     ', 'R   ', 36, 48, '2016-04-15', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (86, 'T2                                      ', 'A2                                      ', 'С2                                     ', 'A   ', -49, 6, '2016-04-29', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (88, 'T2                                      ', 'A4                                      ', 'C5                                      ', 'A   ', -16, 66, '2016-04-25', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (89, 'T2                                      ', 'A2                                      ', 'C5                                      ', 'A   ', -9, 48, '2016-04-22', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (90, 'T2                                      ', 'A2                                      ', 'C5                                      ', 'A   ', -9, 48, '2016-04-22', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (91, 'T2                                      ', 'A4                                      ', 'С4                                     ', 'R   ', 54, 23, '2016-04-19', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (92, 'T8                                      ', 'A5                                      ', 'C5                                      ', 'R   ', 52, 86, '2016-04-28', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (94, 'T8                                      ', 'A5                                      ', 'C5                                      ', 'R   ', 43, 33, '2016-04-15', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (95, 'T8                                      ', 'A5                                      ', 'C5                                      ', 'R   ', 41, 34, '2016-04-15', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (96, 'T2                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 65, 100, '2016-04-21', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (97, 'T1                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 1, 0, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (54, 'T2                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 50, 12, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (56, 'T2                                      ', 'A3                                      ', 'С1                                     ', 'A   ', -50, 12, '2016-04-20', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (100, 'T3                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 72, 100, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (102, 'T1                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 1, 0, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (104, 'T1                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 1, 0, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (106, 'T1                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 10, 10, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (108, 'T2                                      ', 'A3                                      ', 'С1                                     ', 'R   ', 1, 50, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (110, 'T2                                      ', 'A5                                      ', 'С4                                     ', 'R   ', 100, 60, '2016-04-21', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (112, 'T1                                      ', 'A5                                      ', 'С2                                     ', 'A   ', -36, 12, '2016-04-26', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (114, 'T6                                      ', 'A3                                      ', 'С4                                     ', 'R   ', 1, 23, '2016-04-30', 'N', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (116, 'T8                                      ', 'A6                                      ', 'C5                                      ', 'A   ', -103, 15, '2016-04-29', 'Y', '2016-04-25');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (118, 'T2                                      ', 'A3                                      ', 'С4                                     ', 'R   ', 12, 12, '2016-04-28', 'Y', '2016-04-26');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (121, 'T2                                      ', 'A2                                      ', 'С4                                     ', 'R   ', 30, 0, '2016-04-28', 'N', '2016-04-27');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (134, 'T2                                      ', 'A1                                      ', 'С3                                     ', 'R   ', 30, 3, '2016-04-27', 'N', '2016-04-27');
INSERT INTO OPERATION (ID, ID_TOVAR, ID_AG, ID_WH, TYPEOP, KOL, PRICE, POST_DATE, DELIVERY, ORDER_DATE) VALUES (120, 'T7                                      ', 'A6                                      ', 'С1                                     ', 'R   ', 39, 114, '2016-04-27', 'N', '2016-04-27');

COMMIT WORK;

INSERT INTO VEHICLE (ID_V, NUM_V, CAPACITY) VALUES ('V1                                      ', 'A111AA                                  ', 100);
INSERT INTO VEHICLE (ID_V, NUM_V, CAPACITY) VALUES ('V2                                      ', 'A112AB                                  ', 300);
INSERT INTO VEHICLE (ID_V, NUM_V, CAPACITY) VALUES ('V3                                      ', 'A113AC                                  ', 50);
INSERT INTO VEHICLE (ID_V, NUM_V, CAPACITY) VALUES ('V4                                      ', 'A114AD                                  ', 200);
INSERT INTO VEHICLE (ID_V, NUM_V, CAPACITY) VALUES ('V5                                      ', 'A115AF                                  ', 70);
INSERT INTO VEHICLE (ID_V, NUM_V, CAPACITY) VALUES ('V6                                      ', 'A116AG                                  ', 500);

COMMIT WORK;

INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (1, 1, '10:00:00', '12:00:00', 'V1                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (2, 2, '10:00:00', '12:00:00', 'V2                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (3, 3, '10:00:00', '12:00:00', 'V3                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (4, 4, '10:00:00', '12:00:00', 'V4                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (5, 5, '10:00:00', '12:00:00', 'V5                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (6, 6, '10:00:00', '12:00:00', 'V6                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (71, 69, '08:00:00', '10:00:00', 'V6                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (74, 72, '21:00:00', '23:00:00', 'V4                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (76, 75, '07:00:00', '09:00:00', 'V2                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (79, 77, '09:00:00', '11:00:00', 'V2                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (55, 54, '13:00:00', '15:00:00', 'V2                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (57, 56, '16:00:00', '18:00:00', 'V2                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (82, 80, '08:00:00', '10:00:00', 'V2                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (85, 83, '10:00:00', '12:00:00', 'V1                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (87, 86, '11:00:00', '13:00:00', 'V2                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (99, 97, '09:00:00', '11:00:00', 'V3                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (101, 100, '10:00:00', '12:00:00', 'V6                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (103, 102, '09:00:00', '11:00:00', 'V5                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (105, 104, '07:00:00', '09:00:00', 'V3                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (107, 106, '16:00:00', '18:00:00', 'V1                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (109, 108, '18:00:00', '20:00:00', 'V3                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (111, 110, '17:00:00', '19:00:00', 'V6                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (113, 112, '16:00:00', '18:00:00', 'V6                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (117, 116, '15:00:00', '17:00:00', 'V2                                      ');
INSERT INTO DELIVERY (ID_D, ID_OP, TIME_A, TIME_D, ID_V) VALUES (119, 118, '21:00:00', '23:00:00', 'V5                                      ');

COMMIT WORK;

INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (3, 'ID_AG', 'p1        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (4, 'ID_AG', 'p2        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (5, 'ID_AG', 'p3        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (6, 'ID_AG', 'p4        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (7, 'ID_AG', 'p5        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (8, 'ID_AG', 'p6        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (9, 'ID_AG', 'A1        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (10, 'ID_AG', 'A2        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (11, 'ID_AG', 'A3        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (12, 'ID_AG', 'A4        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (13, 'ID_AG', 'A5        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (14, 'ID_AG', 'A6        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (15, 'ID_AG', 'A2        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (16, 'ID_AG', 'A3        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (17, 'ID_AG', 'A4        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (18, 'ID_AG', 'A5        ');
INSERT INTO IBE$LOG_KEYS (LOG_TABLES_ID, KEY_FIELD, KEY_VALUE) VALUES (19, 'ID_AG', 'A6        ');

COMMIT WORK;

INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (3, 'AGENT', 'U', '2016-04-13 22:06:45', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (4, 'AGENT', 'U', '2016-04-13 22:06:51', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (5, 'AGENT', 'U', '2016-04-13 22:06:54', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (6, 'AGENT', 'U', '2016-04-13 22:06:57', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (7, 'AGENT', 'U', '2016-04-13 22:07:01', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (8, 'AGENT', 'U', '2016-04-13 22:07:06', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (9, 'AGENT', 'U', '2016-04-13 22:08:31', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (10, 'AGENT', 'U', '2016-04-14 10:19:08', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (11, 'AGENT', 'U', '2016-04-14 10:19:13', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (12, 'AGENT', 'U', '2016-04-14 10:19:19', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (13, 'AGENT', 'U', '2016-04-14 10:19:39', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (14, 'AGENT', 'U', '2016-04-14 10:21:04', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (15, 'AGENT', 'U', '2016-04-14 10:21:09', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (16, 'AGENT', 'U', '2016-04-14 10:21:12', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (17, 'AGENT', 'U', '2016-04-14 10:21:18', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (18, 'AGENT', 'U', '2016-04-14 10:21:26', 'IT39');
INSERT INTO IBE$LOG_TABLES (ID, TABLE_NAME, OPERATION, DATE_TIME, USER_NAME) VALUES (19, 'AGENT', 'U', '2016-04-14 10:21:34', 'IT39');

COMMIT WORK;

INSERT INTO LAST_DATE (L_DATE) VALUES ('2016-04-28');

COMMIT WORK;

INSERT INTO LOG_FILE (ID, INFORM, DDATA) VALUES (125, 'T2         С4         30.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ', '2016-04-27');
INSERT INTO LOG_FILE (ID, INFORM, DDATA) VALUES (135, 'T2         С3         30.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ', '2016-04-27');
INSERT INTO LOG_FILE (ID, INFORM, DDATA) VALUES (136, 'T2         С3         30.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ', '2016-04-26');
INSERT INTO LOG_FILE (ID, INFORM, DDATA) VALUES (137, 'T2         С3         30.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ', '2016-04-26');
INSERT INTO LOG_FILE (ID, INFORM, DDATA) VALUES (138, 'T2         С4         30.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ', '2016-04-26');
INSERT INTO LOG_FILE (ID, INFORM, DDATA) VALUES (139, 'T7         С1         39.00                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ', '2016-04-26');

COMMIT WORK;

INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (1, 'С1                                     ', 'T2                                      ', 169);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (2, 'С1                                     ', 'T3                                      ', 228);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (3, 'С2                                     ', 'T1                                      ', 964);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (4, 'С3                                     ', 'T2                                      ', 324);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (5, 'С3                                     ', 'T4                                      ', 234);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (6, 'С3                                     ', 'T5                                      ', 1500);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (7, 'С3                                     ', 'T6                                      ', 34);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (70, 'С2                                     ', 'T2                                      ', 151);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (73, 'С4                                     ', 'T2                                      ', 642);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (115, 'С4                                     ', 'T6                                      ', 1);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (78, 'С2                                     ', 'T3                                      ', 100);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (81, 'C5                                      ', 'T2                                      ', 16);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (84, 'С4                                     ', 'T8                                      ', 36);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (93, 'C5                                      ', 'T8                                      ', 33);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (98, 'С1                                     ', 'T1                                      ', 13);
INSERT INTO TOVAR_WH (ID, ID_WH, ID_TOVAR, KOL) VALUES (141, 'С1                                     ', 'T7                                      ', 195);

COMMIT WORK;



/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE AGENT ADD CONSTRAINT PK_AGENT PRIMARY KEY (ID_AG);
ALTER TABLE DELIVERY ADD PRIMARY KEY (ID_D);
ALTER TABLE IBE$LOG_TABLES ADD PRIMARY KEY (ID);
ALTER TABLE LOG_FILE ADD CONSTRAINT PK_LOG_FILE PRIMARY KEY (ID);
ALTER TABLE OPERATION ADD CONSTRAINT PK_OPER PRIMARY KEY (ID);
ALTER TABLE TOVAR ADD CONSTRAINT PK_TOVAR PRIMARY KEY (ID_TOVAR);
ALTER TABLE TOVAR_WH ADD CONSTRAINT PK_TOVAR_WH PRIMARY KEY (ID);
ALTER TABLE VEHICLE ADD PRIMARY KEY (ID_V);
ALTER TABLE WAREHOUSE ADD CONSTRAINT PK_WAREHOUSE PRIMARY KEY (ID_WH);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE DELIVERY ADD CONSTRAINT FK_DELIVERY_1 FOREIGN KEY (ID_OP) REFERENCES OPERATION (ID);
ALTER TABLE DELIVERY ADD CONSTRAINT FK_DELIVERY_2 FOREIGN KEY (ID_V) REFERENCES VEHICLE (ID_V);
ALTER TABLE OPERATION ADD CONSTRAINT FK_OP_1 FOREIGN KEY (ID_TOVAR) REFERENCES TOVAR (ID_TOVAR);
ALTER TABLE OPERATION ADD CONSTRAINT FK_OP_2 FOREIGN KEY (ID_AG) REFERENCES AGENT (ID_AG);
ALTER TABLE OPERATION ADD CONSTRAINT FK_OP_3 FOREIGN KEY (ID_WH) REFERENCES WAREHOUSE (ID_WH);
ALTER TABLE TOVAR_WH ADD CONSTRAINT FK_TOVAR_WH_1 FOREIGN KEY (ID_WH) REFERENCES WAREHOUSE (ID_WH);
ALTER TABLE TOVAR_WH ADD CONSTRAINT FK_TOVAR_WH_2 FOREIGN KEY (ID_TOVAR) REFERENCES TOVAR (ID_TOVAR);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX IBE$LOG_BLOB_FIELDS_IDX1 ON IBE$LOG_BLOB_FIELDS (LOG_TABLES_ID);
CREATE INDEX IBE$LOG_FIELDS_IDX1 ON IBE$LOG_FIELDS (LOG_TABLES_ID);
CREATE INDEX IBE$LOG_KEYS_IDX1 ON IBE$LOG_KEYS (LOG_TABLES_ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                        Database event triggers                         ***/
/******************************************************************************/



/* Trigger: "updated" */
CREATE TRIGGER "updated"
ACTIVE ON CONNECT POSITION 0
AS
begin
  if ((select first(1) l_date from last_date) <> 'today') then
  begin
    execute procedure recalculation;
    update last_date
    set l_date = 'today';
  end
end
^

/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: CH_OP_TWH */
CREATE TRIGGER CH_OP_TWH FOR OPERATION
ACTIVE BEFORE INSERT POSITION 0
AS
begin

  if (new.typeop='A') then
        if (
            
            (select sum(o.kol)
            from operation o
            where o.id_tovar = new.id_tovar and o.id_wh = new.id_wh and o.order_date = 'today' )
            +
            (select first(1) kol
            from tovar_wh
            where id_tovar = new.id_tovar and id_wh = new.id_wh)
            < 0
        ) then
        exception not_enough_goods;

end
^

/* Trigger: GG */
CREATE TRIGGER GG FOR LOG_INFO
INACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.inform='bbb';
end
^

/* Trigger: IBE$AGENT_AU */
CREATE TRIGGER IBE$AGENT_AU FOR AGENT
ACTIVE AFTER UPDATE POSITION 32767
as
declare variable tid integer;
begin
  tid = gen_id(ibe$log_tables_gen,1);

  insert into ibe$log_tables (id, table_name, operation, date_time, user_name)
         values (:tid, 'AGENT', 'U', 'NOW', user);

  insert into ibe$log_keys (log_tables_id, key_field, key_value)
         values (:tid, 'ID_AG', old.id_ag);


end
^

/* Trigger: IBE$LOG_TABLES_BD */
CREATE TRIGGER IBE$LOG_TABLES_BD FOR IBE$LOG_TABLES
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
  DELETE FROM IBE$LOG_FIELDS WHERE LOG_TABLES_ID = OLD.ID;
  DELETE FROM IBE$LOG_BLOB_FIELDS WHERE LOG_TABLES_ID = OLD.ID;
  DELETE FROM IBE$LOG_KEYS WHERE LOG_TABLES_ID = OLD.ID;
END
^

/* Trigger: KOL_G_Z */
CREATE TRIGGER KOL_G_Z FOR TOVAR_WH
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin
  if (new.kol<0) then
  exception WRONG_DATA_KOL_EXCEPTION;
end
^

/* Trigger: LOG_FILE_BI */
CREATE TRIGGER LOG_FILE_BI FOR LOG_FILE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_log_file_id,1);
end
^

/* Trigger: LOG_INFO_BD0 */
CREATE TRIGGER LOG_INFO_BD0 FOR LOG_INFO
INACTIVE BEFORE DELETE POSITION 0
AS
begin
  insert into log_info(inform) values (old.inform);
end
^

/* Trigger: LOG_INFO_BI0 */
CREATE TRIGGER LOG_INFO_BI0 FOR LOG_INFO
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if(new.id is null) then new.id = gen_id(gen_log_file_id,1);
end
^

/* Trigger: LOG_INFO_BU0 */
CREATE TRIGGER LOG_INFO_BU0 FOR LOG_INFO
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (old.inform='1') then new.inform='5';
end
^

/* Trigger: NEW_ITEM */
CREATE TRIGGER NEW_ITEM FOR TOVAR_WH
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (not exists (select * from tovar_wh where (id_wh = new.id_wh) and (id_tovar = new.id_tovar))) then
    insert into tovar_wh values(gen_id(gen_log_file_id,1),new.id_wh,new.id_tovar,0);
end
^

SET TERM ; ^



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE ADD_ORDER (
    GOODS TYPE OF COLUMN TOVAR.NOMENCLATURE,
    AMOUNT TYPE OF COLUMN OPERATION.KOL,
    AGENT_NAME TYPE OF COLUMN AGENT.NAME_AG,
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN,
    TYPE_OP TYPE OF COLUMN OPERATION.TYPEOP,
    PRICE TYPE OF COLUMN OPERATION.PRICE,
    DEL_TIME TYPE OF COLUMN DELIVERY.TIME_A,
    DEL_DATE TYPE OF COLUMN OPERATION.POST_DATE,
    DELIV TYPE OF COLUMN OPERATION.DELIVERY)
AS
declare variable id integer = 0;
declare variable num char(10);
begin
  id = gen_id(gen_log_file_id, 1);

  if (:type_op = 'A') then
    amount = :amount * (-1);
  insert into operation values(
  :id,
  (select first(1) t.id_tovar from tovar t where t.nomenclature = :goods),
  (select first(1) a.id_ag from agent a where a.name_ag = :agent_name),
  (select first(1) w.id_wh from warehouse w where w.naimen = :wh_name),
  :type_op, 
  :amount,
  :price,
  :del_date,
  :deliv,
  'today');
  if (:deliv = 'Y') then
  begin
      execute procedure  choose_vehicle(:del_date,:del_time,:amount * (select first(1) t.volume from tovar t where t.nomenclature = :goods))
      returning_values :num;

      insert into delivery values(
      gen_id(gen_log_file_id, 1),
      :id, 
      dateadd(hour, -1, :del_time),
      dateadd(hour, 1, :del_time),
      (select first(1) v.id_v from vehicle v where v.num_v = :num));
  end
end^


ALTER PROCEDURE AGENT_NAMES
RETURNS (
    ANAME CHAR(20))
AS
declare AGENT_CURSOR cursor for (
    select NAME_AG
    from AGENT);
begin
    open agent_cursor;
    while (1=1) do
    begin
        fetch agent_cursor into: aname;
        if (row_count=0) then
        leave;
  suspend;
    end
   close agent_cursor;
end^


ALTER PROCEDURE AMOUNT_ITEM_WH (
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN,
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE)
RETURNS (
    AMOUNT TYPE OF COLUMN TOVAR_WH.KOL)
AS
begin
  FOR SELECT
    B.KOL
  FROM
    TOVAR A
  JOIN
    tovar_wh B
    ON A.ID_TOVAR = B.id_tovar
  JOIN
    WAREHOUSE C
    ON B.ID_WH = C.ID_WH
  WHERE C.NAIMEN = :WH_NAME AND
        A.nomenclature = :nomenclature
  INTO :AMOUNT
  DO suspend;
end^


ALTER PROCEDURE CHOOSE_VEHICLE (
    DEL_DATE TYPE OF COLUMN OPERATION.POST_DATE,
    DEL_TIME TYPE OF COLUMN DELIVERY.TIME_A,
    CAP TYPE OF COLUMN VEHICLE.CAPACITY)
RETURNS (
    CAR_NUM TYPE OF COLUMN VEHICLE.NUM_V)
AS
begin
  for select first(1) v.num_v
      from vehicle v
      where v.id_v not in ( select distinct v.id_v
                        from operation o join delivery d on o.id = d.id_op
                                        join vehicle v on v.id_v = d.id_v
                        where o.post_date = :del_date and d.time_d >= :del_time and d.time_a <= :del_time)
                   and v.capacity >= :cap  and :del_time >= '07:00:00' and :del_time <= '23:00:00'
      order by v.capacity
  into :car_num
  do suspend;
end^


ALTER PROCEDURE COUNT_EMPTY_AGENTS
RETURNS (
    "COUNT" INTEGER)
AS
begin

  SELECT
    COUNT (A.ID_AG)
  FROM
    AGENT A
  WHERE A.ID_AG NOT IN
    (
    SELECT
        DISTINCT A.ID_AG
    FROM
        AGENT A
    JOIN
        OPERATION B
    ON A.ID_AG = B.ID_AG
    )
  INTO :"COUNT";
  suspend;
end^


ALTER PROCEDURE COUNTPOST (
    ID_TOV CHAR(10))
RETURNS (
    CNT INTEGER)
AS
begin
  cnt=0;
  select count(ID_AG)    
  from OPERATION   
  where ID_TOVAR=:id_tov and TYPEOP='R'
  group by :id_tov   
  into :cnt;
end^


ALTER PROCEDURE GET_AG (
    Y INTEGER)
RETURNS (
    AG_NAME CHAR(20))
AS
declare C cursor for (select A.name_ag from AGENT A where A.id_ag in
(select O.id_ag from OPERATION O where EXTRACT(YEAR FROM O.post_date) = :y
 and EXTRACT(MONTH FROM O.post_date) between 5 and 8 ));

begin
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :ag_name;
    if (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^


ALTER PROCEDURE GET_FIRST_QARTER_AGENTS (
    Y INTEGER)
RETURNS (
    AGENT_NAME CHAR(20))
AS
 declare c cursor for (select name_ag
                                from agent
                                where id_ag in (select id_ag
                                                    from operation o
                                                    where typeop = 'A' and
                                                            extract(year from o.post_date) = :y) );
begin
    open c;
    while (1 = 1) do
    begin
        fetch c into :agent_name;
        if (row_count = 0) then
            leave;
        suspend;
    end
    close c;
end^


ALTER PROCEDURE GET_TOV (
    AG_NAME CHAR(20))
RETURNS (
    TOV_NAME CHAR(20))
AS
declare c cursor for (select T.nomenclature from TOVAR T where T.id_tovar in (select O.id_tovar
from OPERATION O
WHERE O.id_ag = (SELECT A.id_ag FROM AGENT A WHERE A.name_ag = :ag_name)));

begin
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :tov_name;
    if (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^


ALTER PROCEDURE GET_TOVAR_BY_PRICE (
    PRODUCT_PRICE INTEGER)
RETURNS (
    TOVAR_NAME CHAR(20))
AS
 declare c cursor for (select nomenclature from tovar
                                where id_tovar in (select id_tovar from operation where price < :product_price));
    begin
            open c;
            while (1 = 1) do
        begin
            fetch c into :tovar_name;
            if (row_count = 0) then
                leave;
            suspend;
        end
        close c;
    end^


ALTER PROCEDURE GET_WH (
    WH_NAME CHAR(20))
RETURNS (
    RET_NAME CHAR(20))
AS
begin
    for SELECT W.naimen FROM WAREHOUSE W WHERE W.id_wh = (select O.id_wh
    FROM OPERATION O
    GROUP BY O.id_wh
    HAVING avg(O.price) < (select MIN(O2.price) from OPERATION O2 where O2.id_wh =
    (select W2.id_wh from WAREHOUSE W2 WHERE W2.naimen = :wh_name)))
    INTO :ret_name do
    suspend;
END^


ALTER PROCEDURE GET_WH_BY_PRODUCTS (
    PRODUCT_NAME CHAR(20))
RETURNS (
    WAREHOUSE_NAME CHAR(20))
AS
 declare c cursor for (select naimen
                                from warehouse
                                where id_wh in (select id_wh
                                                    from operation
                                                    where typeop = 'A' and
                                                            id_tovar in (select id_tovar
                                                                            from tovar
                                                                            where nomenclature = :product_name)));
begin
    open c;
    while (1 = 1) do
    begin
        fetch c into :warehouse_name;
        if (row_count = 0) then
            leave;
        suspend;
    end
    close c;
end^


ALTER PROCEDURE GETCOUNT (
    WHNAME CHAR(20))
RETURNS (
    KOL INTEGER)
AS
begin
  for select count(o.id_ag) from tovar t  join operation o on t.id_tovar=o.id_tovar
    join warehouse w on w.id_wh=o.id_wh where o.typeop = 'A' AND w.naimen=:WHNAME
    HAVING COUNT(o.id_tovar) = 1
    into :KOL do
    suspend;
end^


ALTER PROCEDURE GETCOUNT2 (
    WHNAME CHAR(20))
RETURNS (
    NAIMENS CHAR(20))
AS
declare variable IDW char(10);
declare variable AVG_ integer;
begin
  idw = (Select id_wh from warehouse where naimen = :WHNAME );
  avg_ = (select avg(PRICE) from operation where id_wh = :idw and typeop = 'R');
  for select w.naimen from warehouse w join operation o on w.id_wh = o.id_wh
   where o.typeop = 'R' and o.id_wh != :idw
  group by w.id_wh,  w.naimen
  having
  avg(o.PRICE) < :avg_
  into :NAIMENS do
  suspend;
end^


ALTER PROCEDURE GETNOMENLIST1 (
    AGNAME CHAR(20))
RETURNS (
    NOMEN CHAR(20))
AS
declare C cursor for (
    select T.NOMENCLATURE
    from TOVAR T
    join OPERATION O on T.ID_TOVAR = O.ID_TOVAR
    join WAREHOUSE W on W.ID_WH = O.ID_WH
    join AGENT A on A.ID_AG = O.ID_AG
    where A.NAME_AG = :AGNAME);
begin
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :NOMEN;
    IF(ROW_COUNT = 0)THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
end^


ALTER PROCEDURE GETWHLIST2 (
    WHNAME CHAR(20))
RETURNS (
    TOVARLIST CHAR(20))
AS
declare variable W_ID char(10);
declare variable MAXX integer;
begin
    W_ID = (select distinct o.id_wh from warehouse o
    where o.naimen = :WHNAME);
    for select t.nomenclature from tovar t
    join operation o on o.id_tovar=t.id_tovar
    where o.id_wh = :W_ID
    group by t.nomenclature, o.id
        having count(id)<=1
    INTO tovarlist
    do
      suspend;

    for select t.name_ag from agent t
    join operation o on o.id_tovar=t.id_ag
    where o.id_wh = :W_ID
    group by t.name_ag, o.id
        having count(id)<=1
    INTO tovarlist
    do
      suspend;
end^


ALTER PROCEDURE ITEM_NAMES
RETURNS (
    ANAME CHAR(20))
AS
declare ITEM_CURSOR cursor for (
    select T.nomenclature
    from tovar T);
begin
    open ITEM_CURSOR;
    while (1=1) do
    begin
        fetch ITEM_CURSOR into: aname;
        if (row_count=0) then
        leave;
  suspend;
    end
   close ITEM_CURSOR;
end^


ALTER PROCEDURE ITEMBYCOUNT (
    COU NUMERIC(15,2))
RETURNS (
    TOVARNAME CHAR(20))
AS
declare variable idtov char(20) ;
begin
     for select O.id_tovar
     FROM OPERATION O
     WHERE (O.kol > :COU)
     GROUP BY O.id_tovar
     INTO :idtov do
     begin
        select T.nomenclature
        FROM TOVAR T
        WHERE (T.id_tovar = :idtov)
        into :TOVARNAME;
        suspend;
     end
end^


ALTER PROCEDURE ITEMS_NAMES_WH (
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN)
RETURNS (
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE)
AS
begin
  FOR SELECT
    DISTINCT A.NOMENCLATURE
  FROM
    TOVAR A
  JOIN
    tovar_wh B
    ON A.ID_TOVAR = B.id_tovar
  JOIN
    WAREHOUSE C
    ON B.ID_WH = C.ID_WH
  WHERE C.NAIMEN = :WH_NAME
  INTO :NOMENCLATURE
  DO suspend;
end^


ALTER PROCEDURE NAME_AGENT (
    KVO NUMERIC(18,2))
RETURNS (
    N_AG CHAR(20))
AS
begin
  /* Procedure Text */
  for
  select a.NAME_AG from agent a join operation o
  on a.id_ag = o.id_ag
  where (o.kol > :kvo)
  into: n_ag
  do suspend;
end^


ALTER PROCEDURE NEW_PROCEDURE (
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN,
    DATE_FROM TYPE OF COLUMN OPERATION.POST_DATE,
    DATE_TO TYPE OF COLUMN OPERATION.POST_DATE)
RETURNS (
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE,
    KOL TYPE OF COLUMN OPERATION.KOL)
AS
declare Q cursor for (
    select A.NOMENCLATURE, sum(B.KOL)
    from TOVAR A
    join OPERATION B on A.ID_TOVAR = B.ID_TOVAR
    join WAREHOUSE C on C.ID_WH = B.ID_WH
    where C.NAIMEN = :WH_NAME and
          B.POST_DATE >= :DATE_FROM and
          B.POST_DATE <= :DATE_TO
    group by A.NOMENCLATURE);

begin
  OPEN Q;
  FETCH Q INTO :NOMENCLATURE, :KOL;
  WHILE (ROW_COUNT>0) DO
    BEGIN
      suspend;
      FETCH Q INTO :NOMENCLATURE, :KOL;

    END
  CLOSE Q;
end^


ALTER PROCEDURE NEWTASK2 (
    I CHAR(1))
RETURNS (
    S VARCHAR(30))
AS
begin
  SELECT e.nomenclature
  FROM TOVAR e
  WHERE e.id_tovar in (SELECT s.id_tovar FROM Operation s
  WHERE s.id_wh = (select w.id_wh
                    from warehouse w
                    where w.naimen=:i))
  into :s;
end^


ALTER PROCEDURE P1 (
    TOVARNAME CHAR(20),
    USER_ID CHAR(20))
AS
begin
   insert into INOUT
   SELECT (SELECT W.naimen FROM WAREHOUSE W WHERE W.id_wh = O.id_wh), O.post_date, O.kol , iif(O.typeop = 'R', O.PRICE * (-1), O.PRICE) , :user_id from OPERATION O;
  suspend;
end^


ALTER PROCEDURE RECALCULATION
AS
declare variable WAREH type of column TOVAR_WH.ID_WH;
declare variable TOV type of column TOVAR_WH.ID_TOVAR;
declare variable SUM_KOL type of column OPERATION.KOL;
begin
    for select id_tovar, id_wh, sum(kol)
    from (select o.id_tovar, o.id_wh, o.kol
    from operation o
    where o.order_date between (select first (1) l_date from last_date) and current_date)
    group by id_tovar, id_wh
    into :tov, :wareh, :sum_kol
    do
    begin

            if (not exists (select * from tovar_wh where (id_wh = :wareh) and (id_tovar = :tov))) then
            insert into tovar_wh values(gen_id(gen_log_file_id,1),:wareh,:tov,:sum_kol);
            else
            update tovar_wh twh
            set twh.kol = :sum_kol + twh.kol
            where twh.id_wh = :wareh and twh.id_tovar = :tov;   
    end
end^


ALTER PROCEDURE SUM_OST (
    NUM_TOVAR CHAR(10),
    T_TOWN CHAR(10))
RETURNS (
    SUM_OST NUMERIC(15,0))
AS
begin
  SELECT SUM(TW.KOL) FROM TOVAR_WH TW WHERE TW.id_tovar = :NUM_TOVAR
  AND TW.id_wh IN (SELECT W.ID_WH FROM WAREHOUSE W WHERE W.TOWN = :T_TOWN)
  INTO :SUM_OST;
  suspend;
end^


ALTER PROCEDURE TASK_1 (
    I TYPE OF COLUMN AGENT.NAME_AG)
RETURNS (
    RNAME CHAR(31))
AS
  DECLARE C CURSOR FOR (Select T.nomenclature
from tovar T
where T.id_tovar in (SELECT e.id_tovar
  FROM Operation e
  WHERE e.id_ag in (SELECT A.id_ag FROM Agent A
  WHERE A.name_ag = :i) ));
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF(ROW_COUNT = 0)THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^


ALTER PROCEDURE TASK_2
RETURNS (
    RNAME CHAR(31))
AS
  DECLARE C CURSOR FOR (SELECT e.naimen
  FROM warehouse e
  WHERE e.id_wh in (SELECT s.id_wh FROM Operation s
  WHERE  EXTRACT (MONTH FROM POST_DATE) = 3));
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF(ROW_COUNT = 0)THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^


ALTER PROCEDURE TASK_3 (
    I TYPE OF COLUMN TOVAR.NOMENCLATURE)
RETURNS (
    RNAME CHAR(31))
AS
declare C cursor for (
    SELECT e.nomenclature
FROM TOVAR e

WHERE


 (select Sum(o.price)
                from operation O
                where o.id_tovar= e.id_tovar) <(select Sum(U.price)
                    from operation U where U.id_tovar in (select K.id_tovar
                            From Tovar K
                            where K.nomenclature=:i)));
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF(ROW_COUNT = 0)THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^


ALTER PROCEDURE TASK1 (
    I INTEGER)
RETURNS (
    S VARCHAR(30))
AS
begin
  SELECT e.name_ag
  FROM Agent e
  WHERE e.id_ag in (SELECT s.id_ag FROM Operation s
  WHERE s.post_date between '31.3.'||:i and '01.07.'||:i)
  into :s;
end^


ALTER PROCEDURE TASK1_PROCEDURE (
    N INTEGER)
RETURNS (
    S CHAR(20))
AS
declare "cur" cursor for (
    select A.NAME_AG
    from AGENT A
    join OPERATION O on A.ID_AG = O.ID_AG
    where extract(year from O.POST_DATE) = :N and
          extract(month from O.POST_DATE) >= 1 and
          extract(month from O.POST_DATE) <= 3);
 begin
   open "cur";
   fetch "cur" into :s;
   while (ROW_COUNT > 0) do
   begin
        suspend;
        fetch "cur" into :s;
   end
 end^


ALTER PROCEDURE TASK2 (
    FROMD DATE,
    TOD DATE)
RETURNS (
    NOM CHAR(20),
    OBOROT INTEGER)
AS
declare variable idw char(10);
declare variable p integer;
declare variable r integer;
begin
  /* Procedure Text */
  for select id_tovar, nomenclature from tovar
  into :idw, :nom
  do
   begin
    p = (select SUM(o.kol)  from operation o where
    o.id_tovar = :idw and typeop='A' and o.post_date BETWEEN :fromd and :tod);
    if (p is NULL) then p = 0;
    r =  (select sum(o.kol)  from operation o where
     o.id_tovar = :idw and typeop='R' and o.post_date BETWEEN :fromd and :tod);
     if (r is NULL) then r = 0;
   oborot = p - r;
    suspend;
  end
end^


ALTER PROCEDURE TOV_OB (
    P_YEAR INTEGER,
    TOV CHAR(20))
RETURNS (
    SUM1 NUMERIC(15,2))
AS
declare variable A numeric(15,2);
declare variable S numeric(15,2);
declare variable B char(1);
begin
sum1=0;
for select kol,price,typeop from operation  where extract(year from post_date)=:p_year and id_tovar=(select id_tovar from tovar t where t.nomenclature=:tov)
into :a,:s, :b do
begin
    if(:b = 'A')
     then sum1=sum1-:a*:s ;
     else  sum1=sum1+:a*:s;
end
suspend;
end^


ALTER PROCEDURE TOVARBYAGENT (
    AGENTNAME CHAR(20))
RETURNS (
    TOVARNAME CHAR(20),
    TOVDATE DATE)
AS
begin
     for select MAX(O.post_date), (SELECT T.nomenclature FROM TOVAR T WHERE T.id_tovar = O.id_tovar)
     FROM OPERATION O
     WHERE O.id_ag = (SELECT A.id_ag FROM AGENT A WHERE A.name_ag = :AGENTNAME)
     and O.typeop = 'R'
     GROUP BY O.id_tovar
     INTO :TOVDATE, :TOVARNAME do
  suspend;
end^


ALTER PROCEDURE TOVARBYDATE (
    DATE1 DATE,
    DATE2 DATE)
RETURNS (
    TOVARNAME CHAR(20),
    KOL1 NUMERIC(15,2),
    KOL2 NUMERIC(15,2))
AS
declare variable idtov char(20) ;
begin
     for select O.id_tovar
     FROM OPERATION O
     WHERE O.post_date BETWEEN :DATE1 AND :DATE2
     GROUP BY O.id_tovar
     INTO :idtov do
     begin

         select SUM(O2.kol) FROM OPERATION O2
         WHERE (o2.id_tovar = :idtov) and (O2.post_date BETWEEN :DATE1 AND :DATE2) and (O2.typeop  = 'A')
         INTO :kol1;
         if (kol1 IS NULL) then
         kol1 = 0;

         select SUM(o2.kol) FROM OPERATION o2
         WHERE (o2.id_tovar = :idtov) and (O2.post_date BETWEEN :DATE1 AND :DATE2) and (O2.typeop  = 'R')
         INTO :kol2;
         if (kol2 IS NULL) then
         kol2 = 0;

         select T.nomenclature FROM TOVAR T
         WHERE T.id_tovar = :idtov
         into :TOVARNAME;
         suspend;
     end
end^


ALTER PROCEDURE TOVARS_BY_WH (
    WH_NAME TYPE OF COLUMN WAREHOUSE.NAIMEN)
RETURNS (
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE)
AS
begin
  FOR SELECT
    DISTINCT A.NOMENCLATURE
  FROM
    TOVAR A
  JOIN
    OPERATION B
    ON A.ID_TOVAR = B.ID_TOVAR
  JOIN
    WAREHOUSE C
    ON B.ID_WH = C.ID_WH
  WHERE C.NAIMEN = :WH_NAME
  INTO :NOMENCLATURE
  DO suspend;
  IF (ROW_COUNT=0) THEN EXCEPTION WRONG_DATA_KOL_EXCEPTION;
end^


ALTER PROCEDURE TOVARS_LESS_COST (
    PRICE TYPE OF COLUMN OPERATION.PRICE)
RETURNS (
    NOMENCLATURE TYPE OF COLUMN TOVAR.NOMENCLATURE)
AS
begin
  FOR SELECT
    DISTINCT A.NOMENCLATURE
  FROM
    TOVAR A
  JOIN
    OPERATION B
    ON A.ID_TOVAR = B.ID_TOVAR
  WHERE B.PRICE < :PRICE
  INTO :NOMENCLATURE
  DO suspend;
  IF (ROW_COUNT=0) THEN EXCEPTION WRONG_DATA_KOL_EXCEPTION;
end^


ALTER PROCEDURE WAREHOUSES (
    NAIMEN CHAR(20))
RETURNS (
    NAIMENS CHAR(20))
AS
 declare variable avg_price int;
begin select avg(a.price) from operation a
where a.id_wh = (select b.id_wh from warehouse b where b.naimen=:naimen)
into :avg_price;
for select
a.naimen from warehouse a
where a.id_wh IN (select b.id_wh from operation b group by b.id_wh having avg(b.price) < :avg_price )
into :naimens
DO
suspend;
end^


ALTER PROCEDURE WARENHOUSE_NAMES
RETURNS (
    ANAME CHAR(20))
AS
declare WH_CURSOR cursor for (
    select W.naimen
    from warehouse W);
begin
    open WH_CURSOR;
    while (1=1) do
    begin
        fetch WH_CURSOR into: aname;
        if (row_count=0) then
        leave;
  suspend;
    end
   close WH_CURSOR;
end^



SET TERM ; ^


/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

DESCRIBE TABLE AGENT
'Таблица-справочник поставщиков и покупателей';

DESCRIBE TABLE OPERATION
'Операции поступления на склад - A и вывоза со склада - R';

DESCRIBE TABLE TOVAR
'Таблица справочник номенклатуры товаров';

DESCRIBE TABLE TOVAR_WH
'Оперативные данные по остаткам товара на складе';

DESCRIBE TABLE WAREHOUSE
'Таблица-справочник складов товара';