/******************************************************************************/ 
 /***          Generated by IBExpert 2004.08.02 17.08.2004 14:35:19          ***/ 
 /******************************************************************************/ 

SET SQL DIALECT  3 ;

SET NAMES WIN1251;

CREATE DATABASE 'c:\tst.gdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE  4096 
DEFAULT CHARACTER SET WIN1251;



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

CREATE GENERATOR GEN_BILL_ID;

CREATE GENERATOR GEN_BILL_ITEMS_ID;

CREATE GENERATOR GEN_GOODS_ID;

 /* Из - за лени я создал генератор на номера чеков */ 
CREATE GENERATOR GEN_BILL_NUMBER_ID;
SET GENERATOR GEN_BILL_NUMBER_ID TO  4 ;




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


 /* Чек */ 
CREATE TABLE BILL (
    BILL_ID      INTEGER NOT NULL,
    BILL_NUMBER  INTEGER,
    MOMENT       TIMESTAMP DEFAULT 'NOW',
    SUMMA        NUMERIC( 12 , 2 )
);

 /* Позиции чеков */ 
CREATE TABLE BILL_ITEMS (
    BILL_ITEMS_ID  INTEGER NOT NULL,
    GOODS_ID       INTEGER NOT NULL,
    BILL_ID        INTEGER NOT NULL,
    QNT            DECIMAL( 15 , 2 ) DEFAULT  0  NOT NULL,
    PRICE          DECIMAL( 15 , 2 ) DEFAULT  0  NOT NULL,
    SUMMA          COMPUTED BY (QNT * PRICE)
);

 /* Товары */ 
CREATE TABLE GOODS (
    GOODS_ID  INTEGER NOT NULL,
    NAME      VARCHAR( 50 ) CHARACTER SET WIN1251
);




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

ALTER TABLE BILL ADD CONSTRAINT PK_BILL PRIMARY KEY (BILL_ID);
ALTER TABLE BILL_ITEMS ADD CONSTRAINT PK_BILL_ITEMS PRIMARY KEY (BILL_ITEMS_ID);
ALTER TABLE GOODS ADD CONSTRAINT PK_GOODS PRIMARY KEY (GOODS_ID);


 /******************************************************************************/ 
 /***                              Foreign Keys                              ***/ 
 /******************************************************************************/ 
 /* Ну, тут сам исправь, как надо и что надо...*/ 
ALTER TABLE BILL_ITEMS ADD CONSTRAINT FK_BILL_ID FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID);
ALTER TABLE BILL_ITEMS ADD CONSTRAINT FK_GOODS_ID FOREIGN KEY (GOODS_ID) REFERENCES GOODS (GOODS_ID);


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


SET TERM ^ ;


 /* Trigger: BILL_BI */ 
CREATE TRIGGER BILL_BI FOR BILL
ACTIVE BEFORE INSERT POSITION  0 
AS
BEGIN
  IF (NEW.BILL_ID IS NULL) THEN
    NEW.BILL_ID = GEN_ID(GEN_BILL_ID, 1 );
END
^

 /* Trigger: BILL_ITEMS_AIUD0 */ 
 /* Автогенерация суммы в чеке при изменении позиций */ 
CREATE TRIGGER BILL_ITEMS_AIUD0 FOR BILL_ITEMS
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION  0 
AS
declare variable current_bill_id integer;
begin
  if (deleting) then
    current_bill_id = old.bill_id;
  else
    current_bill_id = new.bill_id;

  update bill b set
    b.summa = (select sum(Summa) from bill_items b_i where b_i.bill_id = :current_bill_id)
  where b.bill_id = :current_bill_id;

end
^

 /* Trigger: BILL_ITEMS_BI */ 
CREATE TRIGGER BILL_ITEMS_BI FOR BILL_ITEMS
ACTIVE BEFORE INSERT POSITION  0 
AS
BEGIN
  IF (NEW.BILL_ITEMS_ID IS NULL) THEN
    NEW.BILL_ITEMS_ID = GEN_ID(GEN_BILL_ITEMS_ID, 1 );
END
^

 /* Trigger: BILL_NUMBER_BI */ 
CREATE TRIGGER BILL_NUMBER_BI FOR BILL
ACTIVE BEFORE INSERT POSITION  0 
AS
BEGIN
  IF (NEW.BILL_NUMBER IS NULL) THEN
    NEW.BILL_NUMBER = GEN_ID(GEN_BILL_NUMBER_ID, 1 );
END
^


SET TERM ; ^

 /******************************************************************************/ 
 /***          Generated by IBExpert 2004.08.02 17.08.2004 14:39:06          ***/ 
 /******************************************************************************/ 

SET SQL DIALECT  3 ;

SET NAMES WIN1251;

CONNECT 'c:\tst.gdb' USER 'SYSDBA' PASSWORD 'masterkey';

INSERT INTO GOODS ( NAME) VALUES ('Огурцы');
INSERT INTO GOODS ( NAME) VALUES ('Помидоры');
INSERT INTO GOODS ( NAME) VALUES ('Картошка');

COMMIT WORK;

INSERT INTO BILL (MOMENT, SUMMA) VALUES ('2004-08-17 00:15:00',  0 );
INSERT INTO BILL (MOMENT, SUMMA) VALUES ('2004-08-18 00:20:00',  0 );
INSERT INTO BILL (MOMENT, SUMMA) VALUES ('2004-08-19 00:21:00',  0 );

COMMIT WORK;

INSERT INTO BILL_ITEMS (GOODS_ID, BILL_ID, QNT, PRICE) VALUES ( 1 ,  1 ,  1 ,  5 );
INSERT INTO BILL_ITEMS (GOODS_ID, BILL_ID, QNT, PRICE) VALUES ( 2 ,  1 ,  2 ,  7 );
INSERT INTO BILL_ITEMS (GOODS_ID, BILL_ID, QNT, PRICE) VALUES ( 3 ,  1 ,  3 ,  11 );
INSERT INTO BILL_ITEMS (GOODS_ID, BILL_ID, QNT, PRICE) VALUES ( 1 ,  2 ,  4 ,  5 );
INSERT INTO BILL_ITEMS (GOODS_ID, BILL_ID, QNT, PRICE) VALUES ( 2 ,  1 ,  5 ,  11 );

COMMIT WORK;

select b_i.*, b.moment, g.name from bill_items b_i
  join bill b on (b_i.bill_id = b.bill_id)
  join goods g on (b_i.goods_id = g.goods_id)

select avg(b_i.summa), g.name from bill_items b_i
  join bill b on (b_i.bill_id = b.bill_id)
  join goods g on (b_i.goods_id = g.goods_id)
 group by g.name, g.goods_id

select avg(b_i.summa), g.name from bill_items b_i
  join bill b on (b_i.bill_id = b.bill_id)
  join goods g on (b_i.goods_id = g.goods_id)
 where b.moment between :From and :To
 group by g.name, g.goods_id