ALTER TABLE W$INV_DETAIL ADD PRICE_O DM_DOUBLE;


/******************************************************************************/
/***               Generated by IBExpert 02.07.2020 12:48:04                ***/
/******************************************************************************/

/******************************************************************************/
/***      Following SET SQL DIALECT is just for the Database Comparer       ***/
/******************************************************************************/
SET SQL DIALECT 3;



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


CREATE GENERATOR GEN_EGAIS_DETAIL_BARCODES_ID;

CREATE TABLE EGAIS_DETAIL_BARCODES (
    ID             DM_ID NOT NULL /* DM_ID = BIGINT */,
    DDA_ID         DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    DOC_ID         DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    PART_ID        DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    EGAIS_BARCODE  DM_TEXT NOT NULL /* DM_TEXT = VARCHAR(250) */,
    ALCCODE        DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    QUANT          DM_STATUS /* DM_STATUS = INTEGER */,
    COMMITDATE     DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    INSERTDT       DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    DOC_DETAIL_ID  DM_ID_NULL /* DM_ID_NULL = BIGINT */,
    BOX_NUM        DM_TEXT /* DM_TEXT = VARCHAR(250) */
);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE EGAIS_DETAIL_BARCODES ADD CONSTRAINT PK_EGAIS_DETAIL_BARCODES PRIMARY KEY (ID);


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

CREATE INDEX EGAIS_DETAIL_BARCODES_IDX1 ON EGAIS_DETAIL_BARCODES (DDA_ID);
CREATE INDEX EGAIS_DETAIL_BARCODES_IDX2 ON EGAIS_DETAIL_BARCODES (DOC_ID);
CREATE INDEX EGAIS_DETAIL_BARCODES_IDX3 ON EGAIS_DETAIL_BARCODES (PART_ID);
CREATE INDEX EGAIS_DETAIL_BARCODES_IDX4 ON EGAIS_DETAIL_BARCODES (EGAIS_BARCODE);
CREATE INDEX EGAIS_DETAIL_BARCODES_IDX5 ON EGAIS_DETAIL_BARCODES (DDA_ID, PART_ID);
CREATE INDEX EGAIS_DETAIL_BARCODES_IDX6 ON EGAIS_DETAIL_BARCODES (PART_ID, QUANT, DDA_ID);
CREATE INDEX EGAIS_DETAIL_BARCODES_IDX7 ON EGAIS_DETAIL_BARCODES (PART_ID, DOC_ID);
CREATE INDEX EGAIS_DETAIL_BARCODES_IDX8 ON EGAIS_DETAIL_BARCODES (PART_ID, DDA_ID);


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



SET TERM ^ ;



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



/* Trigger: EGAIS_DETAIL_BARCODES_BI */
CREATE OR ALTER TRIGGER EGAIS_DETAIL_BARCODES_BI FOR EGAIS_DETAIL_BARCODES
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.commitdate is null) then
    new.commitdate = current_timestamp;
  if (new.insertdt is null) then
    new.insertdt = current_timestamp;
  if (new.id is null) then
    new.id = gen_id(gen_egais_detail_barcodes_id,1);
  if (new.dda_id is null) then new.dda_id = 0;
  if (new.doc_id is null) then new.doc_id = 0;
  if (new.part_id is null) then new.part_id = 0;

end
^

SET TERM ; ^



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


SET TERM ^ ;

create or alter procedure PR_PART_HISTORY_TEMP (
    PART_ID type of DM_ID,
    DEEP type of DM_STATUS)
returns (
    DOC_ID type of DM_ID,
    QUANT type of DM_DOUBLE,
    SNAME DM_TEXT,
    PRICE type of DM_DOUBLE,
    PRICE_O type of DM_DOUBLE,
    DISCOUNT type of DM_DOUBLE,
    SUMMA type of DM_DOUBLE,
    SUMMA_O type of DM_DOUBLE,
    DCARD type of DM_TEXT,
    SUM_NDSO type of DM_DOUBLE,
    NAC type of DM_DOUBLE,
    DOC_STATUS type of DM_STATUS,
    DOC_TYPE type of DM_ID,
    SDOC_TYPE type of DM_TEXT,
    AGENT_ID type of DM_ID,
    SAGENT type of DM_TEXT,
    DOCNUM type of DM_TEXT,
    DOCDATE type of DM_DATETIME,
    DOCCAPTION type of DM_TEXT,
    DOCINSERTDT type of DM_DATETIME,
    DOCPOSTDT type of DM_DATETIME,
    DOCVNUM type of DM_ID,
    DOCVSHIFT type of DM_ID,
    APART_ID type of DM_ID,
    MOTHERPART_ID type of DM_ID,
    ADEEP type of DM_STATUS,
    OWNER type of DM_TEXT)
as
declare variable CURQUANT DM_DOUBLE;
declare variable TEK_PART_ID DM_ID;
declare variable KOEF type of DM_STATUS;
declare variable NEW_PART_ID DM_ID;
declare variable GET_HISTORY DM_ID_NULL;
declare variable BASE_TYPE DM_STATUS;
begin
  apart_id=:part_id;
  curquant=0;
  adeep=:deep;

  /*Выборка параметра поиска истории*/
  select param_value from params where param_id='GET_HISTORY' into :GET_HISTORY;

  /* История по партии*/
  if (:get_history=0) then
   begin

     tek_part_id=:part_id;

     if (not((motherpart_id is null) or (motherpart_id=0))) then
       begin
        for
         select
          DOC_ID,QUANT,PRICE,PRICE_O,DISCOUNT,SUMMA,SUMMA_O,DCARD,SUM_NDSO,NAC,DOC_STATUS,DOC_TYPE,SDOC_TYPE,AGENT_ID,SAGENT,DOCNUM,DOCDATE,DOCCAPTION,DOCINSERTDT,DOCPOSTDT,DOCVNUM,DOCVSHIFT,APART_ID,motherpart_id, adeep
         from pr_part_history_temp(:motherpart_id,0)
         into
          :DOC_ID,:QUANT,:PRICE,:PRICE_O,:DISCOUNT,:SUMMA,:SUMMA_O,:DCARD,:SUM_NDSO,:NAC,:DOC_STATUS,:DOC_TYPE,:SDOC_TYPE,:AGENT_ID,:SAGENT,:DOCNUM,:DOCDATE,:DOCCAPTION,:DOCINSERTDT,:DOCPOSTDT,:DOCVNUM,:DOCVSHIFT,:APART_ID,:motherpart_id, :adeep
         do suspend;
       end
     for
      select dt.sname, dt.doc_id, dt.quant, dt.price,dt.price_o, dt.discount, dt.summa, dt.summa_o, dt.dcard, dt.sum_ndso, dt.nac, 1
      from vw_doc_detail dt where part_id=:tek_part_id
      union all
      select dt.sname, dt.doc_id, dt.quant, dt.price,dt.price_o, dt.discount, dt.summa, dt.summa_o, dt.dcard, dt.sum_ndso, dt.nac, 0
      from vw_doc_detail_deleted dt where part_id=:tek_part_id
      order by 1
     into :sname, :doc_id, :quant, :price, :PRICE_O, :discount, :summa, :summa_o, :dcard, :sum_ndso, :nac, :koef
     do
      begin
       curquant=curquant+quant*koef;
       select d.status, d.doc_type, dt.caption, d.agent_id, a.caption, d.docnum, d.docdate, d.caption, d.insertdt, d.postdt, d.vnum, d.vshift, u.username
       from docs d left join agents a on d.agent_id=a.id
         left join doc_types dt on d.doc_type=dt.id
         left join users u on d.owner=u.id
       where d.id=:doc_id
       into :DOC_STATUS,:DOC_TYPE,:SDOC_TYPE,:AGENT_ID,:SAGENT,:DOCNUM,:DOCDATE,:DOCCAPTION,:DOCINSERTDT,:DOCPOSTDT,:DOCVNUM,:DOCVSHIFT, :owner;
       suspend;
      end

     for
      select dt.sname, dt.doc_id, dt.quant, dt.price, dt.price_o, dt.discount, dt.summa, dt.summa_o, dt.dcard, dt.sum_ndso, dt.nac
      from vw_doc_detail_active dt where part_id=:tek_part_id order by dt.doc_id
      into :sname, :doc_id, :quant, :price, :PRICE_O, :discount, :summa, :summa_o, :dcard, :sum_ndso, :nac
     do
      begin
       curquant=curquant+quant;
       select d.status, d.doc_type, dt.caption, d.agent_id, a.caption, d.docnum, d.docdate, d.caption, d.insertdt, d.postdt, d.vnum, d.vshift, u.username, dt.base_type
       from docs d left join agents a on d.agent_id=a.id
         left join doc_types dt on d.doc_type=dt.id
         left join users u on d.owner=u.id
       where d.id=:doc_id
       into :DOC_STATUS,:DOC_TYPE,:SDOC_TYPE,:AGENT_ID,:SAGENT,:DOCNUM,:DOCDATE,:DOCCAPTION,:DOCINSERTDT,:DOCPOSTDT,:DOCVNUM,:DOCVSHIFT,:owner, :base_type;
       suspend;
       if (base_type=3) then
        begin
         for
          select id from parts where motherpart_id=:part_id into :new_part_id
         do
          begin
           if (new_part_id is not null) then
            begin
             for
              select
               DOC_ID,QUANT,PRICE,PRICE_O,DISCOUNT,SUMMA,SUMMA_O,DCARD,SUM_NDSO,NAC,DOC_STATUS,DOC_TYPE,SDOC_TYPE,AGENT_ID,SAGENT,DOCNUM,DOCDATE,DOCCAPTION,DOCINSERTDT,DOCPOSTDT,DOCVNUM,DOCVSHIFT,APART_ID,motherpart_id, adeep
              from pr_part_history_temp(:new_part_id,:deep+1)
              into
               :DOC_ID,:QUANT,:PRICE, :PRICE_O,:DISCOUNT,:SUMMA,:SUMMA_O,:DCARD,:SUM_NDSO,:NAC,:DOC_STATUS,:DOC_TYPE,:SDOC_TYPE,:AGENT_ID,:SAGENT,:DOCNUM,:DOCDATE,:DOCCAPTION,:DOCINSERTDT,:DOCPOSTDT,:DOCVNUM,:DOCVSHIFT,:APART_ID,:motherpart_id, :adeep
             do suspend;
            end
          end
        end
      end
   end
  /* История по наименованию*/
  if (:get_history=1) then
   begin
    if (not((motherpart_id is null) or (motherpart_id=0))) then
       begin
        for
         select
          DOC_ID,QUANT,PRICE,PRICE_O,DISCOUNT,SUMMA,SUMMA_O,DCARD,SUM_NDSO,NAC,DOC_STATUS,DOC_TYPE,SDOC_TYPE,AGENT_ID,SAGENT,DOCNUM,DOCDATE,DOCCAPTION,DOCINSERTDT,DOCPOSTDT,DOCVNUM,DOCVSHIFT,APART_ID,motherpart_id, adeep
         from pr_part_history_temp(:motherpart_id,0)
         into
          :DOC_ID,:QUANT,:PRICE,:PRICE_O,:DISCOUNT,:SUMMA,:SUMMA_O,:DCARD,:SUM_NDSO,:NAC,:DOC_STATUS,:DOC_TYPE,:SDOC_TYPE,:AGENT_ID,:SAGENT,:DOCNUM,:DOCDATE,:DOCCAPTION,:DOCINSERTDT,:DOCPOSTDT,:DOCVNUM,:DOCVSHIFT,:APART_ID,:motherpart_id, :adeep
         do suspend;
       end
    for select p.id  from parts p left join wares w on p.ware_id=w.id where w.name_id=(select w1.name_id from parts p1 left join wares w1 on w1.id=p1.ware_id where p1.id=:part_id) into :tek_part_id do
     begin
     for
      select dt.sname, dt.doc_id, dt.quant, dt.price, dt.price_o, dt.discount, dt.summa, dt.summa_o, dt.dcard, dt.sum_ndso, dt.nac, 1
      from vw_doc_detail dt where part_id=:tek_part_id
      union all
      select dt.sname, dt.doc_id, dt.quant, dt.price,dt.price_o, dt.discount, dt.summa, dt.summa_o, dt.dcard, dt.sum_ndso, dt.nac, 0
      from vw_doc_detail_deleted dt where part_id=:tek_part_id
      order by 1
     into :sname, :doc_id, :quant, :price, :PRICE_O, :discount, :summa, :summa_o, :dcard, :sum_ndso, :nac, :koef
     do
      begin
       curquant=curquant+quant*koef;
       select d.status, d.doc_type, dt.caption, d.agent_id, a.caption, d.docnum, d.docdate, d.caption, d.insertdt, d.postdt, d.vnum, d.vshift, u.username
       from docs d left join agents a on d.agent_id=a.id
         left join doc_types dt on d.doc_type=dt.id
         left join users u on d.owner=u.id
       where d.id=:doc_id
       into :DOC_STATUS,:DOC_TYPE,:SDOC_TYPE,:AGENT_ID,:SAGENT,:DOCNUM,:DOCDATE,:DOCCAPTION,:DOCINSERTDT,:DOCPOSTDT,:DOCVNUM,:DOCVSHIFT, :owner;
       suspend;
      end

     for
      select dt.sname, dt.doc_id, dt.quant, dt.price, dt.price_o, dt.discount, dt.summa, dt.summa_o, dt.dcard, dt.sum_ndso, dt.nac
      from vw_doc_detail_active dt where part_id=:tek_part_id order by dt.doc_id
      into :sname, :doc_id, :quant, :price, :PRICE_O, :discount, :summa, :summa_o, :dcard, :sum_ndso, :nac
     do
      begin
       curquant=curquant+quant;
       select d.status, d.doc_type, dt.caption, d.agent_id, a.caption, d.docnum, d.docdate, d.caption, d.insertdt, d.postdt, d.vnum, d.vshift, u.username, dt.base_type
       from docs d left join agents a on d.agent_id=a.id
         left join doc_types dt on d.doc_type=dt.id
         left join users u on d.owner=u.id
       where d.id=:doc_id
       into :DOC_STATUS,:DOC_TYPE,:SDOC_TYPE,:AGENT_ID,:SAGENT,:DOCNUM,:DOCDATE,:DOCCAPTION,:DOCINSERTDT,:DOCPOSTDT,:DOCVNUM,:DOCVSHIFT,:owner, :base_type;
       suspend;
       if (base_type=3) then
        begin
         for
          select id from parts where motherpart_id=:part_id into :new_part_id
         do
          begin
           if (new_part_id is not null) then
            begin
             for
              select
               DOC_ID,QUANT,PRICE,PRICE_O,DISCOUNT,SUMMA,SUMMA_O,DCARD,SUM_NDSO,NAC,DOC_STATUS,DOC_TYPE,SDOC_TYPE,AGENT_ID,SAGENT,DOCNUM,DOCDATE,DOCCAPTION,DOCINSERTDT,DOCPOSTDT,DOCVNUM,DOCVSHIFT,APART_ID,motherpart_id, adeep
              from pr_part_history_temp(:new_part_id,:deep+1)
              into
               :DOC_ID,:QUANT,:PRICE,:PRICE_O,:DISCOUNT,:SUMMA,:SUMMA_O,:DCARD,:SUM_NDSO,:NAC,:DOC_STATUS,:DOC_TYPE,:SDOC_TYPE,:AGENT_ID,:SAGENT,:DOCNUM,:DOCDATE,:DOCCAPTION,:DOCINSERTDT,:DOCPOSTDT,:DOCVNUM,:DOCVSHIFT,:APART_ID,:motherpart_id, :adeep
             do suspend;
            end
          end
        end
      end
   end
 end
end^

SET TERM ; ^

/* Следующие операторы GRANT сгенерированы автоматически */

GRANT SELECT ON PARAMS TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY_TEMP TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON VW_DOC_DETAIL TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON VW_DOC_DETAIL_DELETED TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON DOCS TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON AGENTS TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON USERS TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON VW_DOC_DETAIL_ACTIVE TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON PARTS TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT SELECT ON WARES TO PROCEDURE PR_PART_HISTORY_TEMP;

/* Существующие привилегии на эту процедуру */

GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY_TEMP TO PROCEDURE PR_PART_HISTORY;
GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY_TEMP TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY_TEMP TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY_TEMP TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_PART_HISTORY (
    PART_ID type of DM_ID,
    DEEP type of DM_STATUS)
returns (
    DOC_ID type of DM_ID,
    QUANT type of DM_DOUBLE,
    SNAME DM_TEXT,
    PRICE type of DM_DOUBLE,
    PRICE_O DM_DOUBLE,
    DISCOUNT type of DM_DOUBLE,
    SUMMA type of DM_DOUBLE,
    SUMMA_O type of DM_DOUBLE,
    DCARD type of DM_TEXT,
    SUM_NDSO type of DM_DOUBLE,
    NAC type of DM_DOUBLE,
    DOC_STATUS type of DM_STATUS,
    DOC_TYPE type of DM_ID,
    SDOC_TYPE type of DM_TEXT,
    AGENT_ID type of DM_ID,
    SAGENT type of DM_TEXT,
    DOCNUM type of DM_TEXT,
    DOCDATE type of DM_DATETIME,
    DOCCAPTION type of DM_TEXT,
    DOCINSERTDT type of DM_DATETIME,
    DOCPOSTDT type of DM_DATETIME,
    DOCVNUM type of DM_ID,
    DOCVSHIFT type of DM_ID,
    APART_ID type of DM_ID,
    MOTHERPART_ID type of DM_ID,
    ADEEP type of DM_STATUS,
    ENDQUANT DM_DOUBLE,
    OWNER type of DM_TEXT)
as
declare variable CURQUANT type of DM_DOUBLE;
begin
  apart_id=:part_id;
  adeep=:deep;
  curquant=0;
  --AVO Исправление от 29-03-2019
  --добавлена переменная ENDQUANT вычисления остатка после операции
  ENDQUANT=0;
  for select * from pr_part_history_temp(:part_id,:deep) ORDER by cast(docdate as DM_Date), doc_id into
  :doc_id, :quant,:sname,:price,:price_o,:discount,:summa,:summa_o,:dcard,:sum_ndso,:nac,:doc_status,:doc_type,:sdoc_type,:agent_id,:sagent,
  :docnum,:docdate,:doccaption,:docinsertdt,:docpostdt,:docvnum,:docvshift,:apart_id,:motherpart_id,:adeep,:owner do
   begin
    if (doc_status<>-1) then ENDQUANT=Round(ENDQUANT+quant,5);
    else ENDQUANT=Round(ENDQUANT,5);
    if (doc_status<>-1) then
     curquant=curquant+quant;
    suspend;
   end
  if (deep>0) then exit;
  QUANT=curquant;
  sname=null;
  DOC_ID=NULL;PRICE=NULL;PRICE_O=NULL;DISCOUNT=NULL;SUMMA=NULL;SUMMA_O=NULL;DCARD=NULL;SUM_NDSO=NULL;NAC=NULL;DOC_STATUS=NULL;
  DOC_TYPE=NULL;SDOC_TYPE=NULL;AGENT_ID=NULL;SAGENT=NULL;DOCNUM=NULL;DOCDATE=NULL;DOCCAPTION=NULL;DOCINSERTDT=NULL;
  DOCPOSTDT=NULL;DOCVNUM=NULL;DOCVSHIFT=NULL;APART_ID=NULL;motherpart_id=NULL;adeep=NULL;owner=null;
  suspend;
end^

SET TERM ; ^

/* Следующие операторы GRANT сгенерированы автоматически */

GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY_TEMP TO PROCEDURE PR_PART_HISTORY;

/* Существующие привилегии на эту процедуру */

GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY TO PROCEDURE PR_PART_HISTORY_TEMP;
GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_PART_HISTORY TO SYSDBA;