/******************************************************************************/
/***          Generated by IBExpert 2010.03.23 11.08.2010 10:30:49          ***/
/******************************************************************************/





SET TERM ^ ; 



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

CREATE PROCEDURE PRINTCHECKBYCASHOPER (
    KINDOPER INTEGER,
    IDPRINTER INTEGER,
    DATEDOC DATE,
    SUMMA DOUBLE PRECISION,
    IDUSER INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE PRINTCHECKXREPORT (
    IDPRINTER INTEGER,
    DATEDOC DATE)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE PRINTCHECKXREPORTGETSUM (
    IDSHIFT INTEGER,
    IDPRINTER INTEGER,
    DATEDOC DATE)
RETURNS (
    NAME VARCHAR(50),
    SUMMA DOUBLE PRECISION)
AS
BEGIN
  SUSPEND;
END^



SET TERM ; ^



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



CREATE TABLE KKM_LIST (
    ID               INTEGER NOT NULL,
    NAME             CHAR80,
    FIELDLABELNAME   CHAR(20),
    ASKCHANGE        INTEGER,
    PRINTKOLINCHECK  INTEGER,
    PRINTLABELNAME   INTEGER,
    NOTDELFORMULA    VARCHAR(200),
    TITLETEXT        BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    TITLETEXTB       BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    FOOTTEXT         BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    FOOTTEXTB        BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    COUNTTITLEROW    INTEGER,
    COUNTTITLEROWB   INTEGER,
    COUNTFOOTROW     INTEGER,
    COUNTFOOTROWB    INTEGER,
    WRITEINFOTODOC   INTEGER,
    KINDKKM          INTEGER,
    STATUS           INTEGER,
    RECORDCOLOR      VARCHAR(20),
    RECORDFONT       VARCHAR(20),
    NOMERKKM         CHAR20,
    IDSUBJECT        INTEGER
);


CREATE TABLE KKM_OPERATIONS (
    ID           INTEGER NOT NULL,
    IDKKM        N_INTEGER,
    IDPRINTER    N_INTEGER,
    IDUSER       N_INTEGER,
    DATEDOC      DATE,
    SUMMA        NMONEY,
    KINDOPER     N_INTEGER,
    IDOWNER      INTEGER,
    IDINOWNER    INTEGER,
    RECORDCOLOR  VARCHAR(20),
    RECORDFONT   VARCHAR(20),
    STATUS       N_INTEGER,
    IDSHIFT      N_INTEGER
);


CREATE TABLE KKM_SHIFTS (
    ID           INTEGER NOT NULL,
    IDKKM        N_INTEGER,
    IDPRINTER    N_INTEGER,
    IDUSERBEGIN  N_INTEGER,
    IDUSEREND    N_INTEGER,
    DATEBEGIN    DATE,
    DATEEND      DATE,
    SUMMABEGIN   NMONEY,
    SUMMAIN      NMONEY,
    SUMMAOUT     NMONEY,
    SUMMAEND     NMONEY,
    IDOWNER      INTEGER,
    IDINOWNER    INTEGER,
    RECORDCOLOR  VARCHAR(20),
    RECORDFONT   VARCHAR(20),
    STATUS       N_INTEGER
);




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

ALTER TABLE KKM_LIST ADD PRIMARY KEY (ID);
ALTER TABLE KKM_OPERATIONS ADD PRIMARY KEY (ID);
ALTER TABLE KKM_SHIFTS ADD PRIMARY KEY (ID);


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

CREATE INDEX KKM_NAME ON KKM_LIST (NAME);


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


SET TERM ^ ;



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



/* Trigger: KKM_OPERATIONS_AI0 */
CREATE TRIGGER KKM_OPERATIONS_AI0 FOR KKM_OPERATIONS
ACTIVE AFTER INSERT OR DELETE POSITION 0
AS
begin
 if (updating) then
    begin
      if (old.kindoper = 0) then
          update KKM_Shifts set summain = summain - old.summa where id = old.idshift;
      else
      if (old.kindoper = 1) then
          update KKM_Shifts set summaout = summaout - old.summa where id = old.idshift;
      else
      if (old.kindoper = 2) then
          update KKM_Shifts set summain = summain - old.summa where id = old.idshift;
      else
      if (old.kindoper = 3) then
          update KKM_Shifts set summaout = summaout - old.summa where id = old.idshift;


      if (new.kindoper = 0) then
          update KKM_Shifts set summain = summain + new.summa where id = new.idshift;
      else
      if (new.kindoper = 1) then
          update KKM_Shifts set summaout = summaout + new.summa where id = new.idshift;
      else
      if (new.kindoper = 2) then
          update KKM_Shifts set summain = summain + new.summa where id = new.idshift;
      else
      if (new.kindoper = 3) then
          update KKM_Shifts set summaout = summaout + new.summa where id = new.idshift;

    end
   else
 if (inserting) then
    begin
      if (new.kindoper = 0) then
          update KKM_Shifts set summain = summain + new.summa where id = new.idshift;
      else
      if (new.kindoper = 1) then
          update KKM_Shifts set summaout = summaout + new.summa where id = new.idshift;
      else
      if (new.kindoper = 2) then
          update KKM_Shifts set summain = summain + new.summa where id = new.idshift;
      else
      if (new.kindoper = 3) then
          update KKM_Shifts set summaout = summaout + new.summa where id = new.idshift;
    end
   else
 if (deleting) then
    begin
      if (old.kindoper = 0) then
          update KKM_Shifts set summain = summain - old.summa where id = old.idshift;
      else
      if (old.kindoper = 1) then
          update KKM_Shifts set summaout = summaout - old.summa where id = old.idshift;
      else
      if (old.kindoper = 2) then
          update KKM_Shifts set summain = summain - old.summa where id = old.idshift;
      else
      if (old.kindoper = 3) then
          update KKM_Shifts set summaout = summaout - old.summa where id = old.idshift;

    end
end
^


/* Trigger: KKM_SHIFTS_BIU0 */
CREATE TRIGGER KKM_SHIFTS_BIU0 FOR KKM_SHIFTS
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
    new.summaend = new.summabegin + new.summain - new.summaout;
end
^


SET TERM ; ^



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


SET TERM ^ ;

ALTER PROCEDURE PRINTCHECKBYCASHOPER (
    KINDOPER INTEGER,
    IDPRINTER INTEGER,
    DATEDOC DATE,
    SUMMA DOUBLE PRECISION,
    IDUSER INTEGER)
AS
declare variable COMMAND integer;
declare variable LINE char(100);
declare variable IDPRINTQUEUE integer;
declare variable NAMEPRINTER varchar(50);
declare variable NAMEOPER varchar(50);
declare variable NAMEUSER varchar(50);
declare variable ORGNAME varchar(100);
begin
  if ((IdPrinter = 0 ) or (IdPrinter is null))  then
    begin
       exception ex_restaurant_notfindprinter;
    end
  else
    begin
        select p.name, coalesce(c.orgname,' ') from restaurant_printers p
            left outer join commonspr c on c.id = p.idsubject
            where p.id = :idprinter into :nameprinter, :orgname;

        select w.username from users w
            where w.id = :IDUSER into :nameuser;
        /*Получаем Id очереди*/
        select gen_id(RESTAURANT_PRINTQUEUE,1) from setting into :IdPrintQueue;
        /* Пишем заголовок */
        /*Операция*/
       command = 64;
       line = trim(orgname);
       insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
       (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

       command = 64;
       line = 'Дата:' ||udf_formatdatetime('dd.mm.yy hh:nn:ss',current_timestamp);
       insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
       (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

       if (KindOper =0) then
        NameOper = 'Внесение';
       else
          NameOper = 'Выдача';
       command = 56;
       Line = udf_padc(Trim(NameOper),' ',21);
       insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
       (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

       command = 64;
       Line = 'Касса: '||Trim(NamePrinter);
       insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
       (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

       command = 64;
       Line = 'Оператор:'|| Trim(nameuser);
       insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
       (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

       command = 57;
       line = 'Сумма:'||udf_padl(udf_FormatFloat('# ##0.00',summa),'.',28-12);
       insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
       (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

        command = 64;
        line = ' ';
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

        command = 64;
        line = 'Сдал(а): ';
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

        command = 64;
        line = udf_padl('_____________________',' ',42);
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

        line = 'Принял(а): '||Trim(nameuser);
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

        command = 64;
        line = ' ';
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);


        command = 64;
        line = udf_padl('_____________________',' ',42);
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

        command = 64;
        line = ' ';
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

        command =  65;
        line = 'Документ: '||idprintqueue||' Время:' ||udf_formatdatetime('dd.mm.yy hh:nn:ss',current_timestamp);
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);
        command = 64;
        line = ' ';
        insert into restaurant_printqueueitems (Id,IDPRINTQUEUE,Command,Line) values
        (gen_id(restaurant_printqueueitems,1),:IDPRINTQUEUE,:command,:Line);

       /*Пишем в заголовок очереди*/
       insert into restaurant_printqueue (Id,IDPRINTER,IDWAITER,IDACCOUNT,IDSERVICE,datebegin,Status)
       values (:idprintqueue,:idprinter,:iduser,0,0,current_timestamp,0);
  end
end^


ALTER PROCEDURE PRINTCHECKXREPORT (
    IDPRINTER INTEGER,
    DATEDOC DATE)
AS
begin
  /* Procedure Text */

end^


ALTER PROCEDURE PRINTCHECKXREPORTGETSUM (
    IDSHIFT INTEGER,
    IDPRINTER INTEGER,
    DATEDOC DATE)
RETURNS (
    NAME VARCHAR(50),
    SUMMA DOUBLE PRECISION)
AS
begin
  name = '';
  summa = 0;
  suspend;
end^



SET TERM ; ^
