ALTER TABLE EGAIS_DETAIL ADD QUANT DM_DOUBLE;

SET SQL DIALECT 3;


SET TERM ^ ;



CREATE OR ALTER TRIGGER EGAIS_DETAIL_BI0 FOR EGAIS_DETAIL
ACTIVE BEFORE INSERT POSITION 0
AS
declare variable barcode dm_text;
declare variable counter integer;
begin
  if (new.egais_barcode is null) then new.egais_barcode= '-';
  counter = 1;
  for select result_string from pr_split(new.egais_barcode, ascii_char(13)||ascii_char(10)) into :barcode do
    begin
      if (counter = 1) then
        new.egais_barcode = barcode;
      else
        insert into egais_detail(doc_detail_id, egais_barcode, producer_inn, producer_kpp)
          values (new.doc_detail_id, :barcode, new.producer_inn, new.producer_kpp);
      counter = 2;
    end
end
^


SET TERM ; ^


SET TERM ^ ;

create or alter procedure PR_EGAIS_DETAIL_OBSHEPIT (
    DOC_DETAIL_ID DM_ID,
    EGAIS_BARCODE DM_BLOBTEXT,
    PRODUCER_INN DM_TEXT,
    PRODUCER_KPP DM_TEXT,
    DDA_QUANT DM_DOUBLE,
    DOC_ID DM_ID)
as
declare variable QUANT_CUR_EGAIS_DETAIL DM_DOUBLE;
declare variable PART_ID DM_ID;
declare variable NAME_ID DM_TEXT;
declare variable QUANT_SALES DM_DOUBLE;
declare variable QUANT_TMP DM_DOUBLE;
begin

 select part_id from doc_detail dd where id = :DOC_DETAIL_ID into :part_id;
 select w.NAME_ID from parts p inner join wares w on p.ware_id = w.id where p.id = :part_id into :NAME_ID;

 -- Если товар не алкоголь и не пиво, ничего не пишем
 if ( (select count(1) from group_detail gd where gd.group_id in (-20,-21) and gd.grouptable in ('PARTS.NAME_ID','PARTS') and gd.grouptable_id in (cast(:part_id as dm_text),:NAME_ID)) = 0) then
  exit;

 --Определяем сколько уже записано алкоголя (вскрытых упаковок) в журнале продаж
 select sum(ed.quant) from egais_detail ed inner join doc_detail dd on dd.id = ed.doc_detail_id where dd.part_id =:part_id into :QUANT_CUR_EGAIS_DETAIL;
 if (QUANT_CUR_EGAIS_DETAIL is null) then QUANT_CUR_EGAIS_DETAIL = 0;

 --Определяем, сколько уже было продано, до текущего чека
 select abs(sum(dd.quant)) from doc_detail dd inner join docs d on dd.doc_id = d.id where dd.part_id = :part_id and d.doc_type in (3,9) and dd.doc_id <> :doc_id into :QUANT_SALES;
 if (QUANT_SALES is null) then QUANT_SALES = 0;

 --Если продажа, то
 QUANT_TMP = -1*DDA_QUANT + :QUANT_SALES - :QUANT_CUR_EGAIS_DETAIL;
 if (:QUANT_TMP > 0) then
     insert into egais_detail (doc_detail_id, egais_barcode, producer_inn, producer_kpp, QUANT)
       values (:doc_detail_id, :egais_barcode, :producer_inn, :producer_kpp, trunc(:QUANT_TMP)+iif(((:QUANT_TMP-trunc(:QUANT_TMP))=0 or (abs(:QUANT_TMP) < 0.001)),0,1));

 --Если возврат, то
 if (DDA_QUANT > 0) then
 begin
--     exception EX_DEBUG QUANT_CUR_EGAIS_DETAIL;
     QUANT_TMP = -1*DDA_QUANT + :QUANT_SALES;
     QUANT_TMP = trunc(:QUANT_TMP)+iif(((:QUANT_TMP-trunc(:QUANT_TMP))=0 or (abs(:QUANT_TMP) < 0.001)),0,1);
     QUANT_TMP = :QUANT_CUR_EGAIS_DETAIL - QUANT_TMP;
     update egais_detail ed set ed.quant = quant - :QUANT_TMP where ed.doc_detail_id =
       (select max(ed2.doc_detail_id) from egais_detail ed2 inner join doc_detail dd on dd.id = ed2.doc_detail_id where dd.part_id = :part_id and (abs(ed2.quant) - abs(:QUANT_TMP)) >= 0 );
 end

 delete from EGAIS_DETAIL where quant < 0.01;
  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT;
GRANT SELECT ON PARTS TO PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT;
GRANT SELECT ON WARES TO PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT;
GRANT SELECT ON GROUP_DETAIL TO PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT;
GRANT SELECT,INSERT,DELETE,UPDATE ON EGAIS_DETAIL TO PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT;
GRANT SELECT ON DOCS TO PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT TO PROCEDURE PR_TMP;
GRANT EXECUTE ON PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_DOC_COMMIT (
    DOC_ID type of DM_ID,
    SESSION_ID type of DM_ID)
as
declare variable BASE_TYPE type of DM_STATUS;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable DSCSUMMA type of DM_DOUBLE;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable PRICE_TYPE DM_STATUS;
declare variable CONTRACT_ID DM_ID;
declare variable DOC_DETAIL_ID DM_ID;
declare variable EGAIS_BARCODE DM_BLOBTEXT;
declare variable PRODUCER_INN DM_TEXT;
declare variable PRODUCER_KPP DM_TEXT;
declare variable DDA_QUANT DM_DOUBLE;
declare variable DDA_DOC_ID DM_ID;
begin

select dt.base_type, d.price_type, d.contract_id from docs d left join doc_types dt on d.doc_type=dt.id where d.id=:doc_id into :base_type, :price_type, :contract_id;
if (base_type = 4) then execute procedure pr_doc_virtual_commit(:doc_id);
else
if (base_type in (1,3,6,8)) then
begin
execute procedure pr_doc_prihod_commit(:doc_id,0);
end
else if (base_type in (2,7,9)) then
execute procedure pr_doc_rashod_commit(:doc_id,0);
else
exception EX_WRONGDOC_BASE_TYPE;

/* nicky edit */
if (exists (select * from docs d where d.id = :doc_id and d.doc_type in (3, 9))) then
  begin
    for select dd.id, dda.egais_barcode, dda.producer_inn, dda.producer_kpp, dda.quant, dda.doc_id
      from doc_detail dd join doc_detail_active dda on dd.doc_id = dda.doc_id and dd.part_id = dda.part_id
      where dd.doc_id = :doc_id into :DOC_DETAIL_ID, :EGAIS_BARCODE, :PRODUCER_INN, :PRODUCER_KPP, :DDA_QUANT, :DDA_DOC_ID do
     execute procedure PR_EGAIS_DETAIL_OBSHEPIT (DOC_DETAIL_ID, :EGAIS_BARCODE, :PRODUCER_INN, :PRODUCER_KPP, :DDA_QUANT, :DDA_DOC_ID);
  end
/* nicky edit end*/

delete from doc_detail_active where doc_id=:doc_id;
if (base_type = 3) then
begin
delete from warebase where part_id in (select part_id from doc_detail where doc_id = :doc_id and quant < 0 and part_type = 1);
end
if (base_type = 4) then
select sum(summa), sum(summa_o), sum(sum_ndso),sum(SUM_NDSR), sum(price*quant/10000), sum(sum_dsc) from
doc_detail_virtual where doc_id=:doc_id into :SUMMA, :SUMMA_O, :SUM_NDSO,:SUM_NDSR, :DSCSUMMA, :sum_dsc;
else
select sum(summa), sum(summa_o), sum(sum_ndso),sum(SUM_NDSR), sum(price*quant/10000), sum(sum_dsc) from
doc_detail where doc_id=:doc_id into :SUMMA, :SUMMA_O, :SUM_NDSO,:SUM_NDSR, :DSCSUMMA, :sum_dsc;
DSCSUMMA=DSCSUMMA*10000;
update docs set
commitsession_id=:session_id,
status=1,
POSTDT='now',
checkdata=(select CHECKDATA from PR_DOCCHECKDATA(:doc_id)),
SUMMA=:SUMMA,
SUMMA_O=:SUMMA_O,
SUM_NDSO=:SUM_NDSO,
SUM_NDSR=:SUM_NDSR,
CALCSUMMA=:DSCSUMMA,
sum_dsc=:sum_dsc,
cashdata=(iif(:base_type=1,-:SUMMA,null))
where id=:doc_id;
execute procedure PR_UPDBLOCKINFO_BY_DOC(:doc_id,1);
--if ((base_type = 2) and (contract_id<>0)) then
if (contract_id<>0) then
begin
if (base_type = 2) then
execute procedure PR_CREDITCOMMIT(:contract_id);
if (base_type in  (8,9)) then
execute procedure PR_CREDITREINITCOMMIT(:contract_id,:session_id);
--    execute procedure PR_CREDITCOMMIT(:contract_id);
end
execute procedure pr_doevent('EV_DOCCOMMIT');
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,UPDATE ON DOCS TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT ON DOC_TYPES TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_VIRTUAL_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_PRIHOD_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_RASHOD_COMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT,DELETE ON DOC_DETAIL_ACTIVE TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT,DELETE ON WAREBASE TO PROCEDURE PR_DOC_COMMIT;
GRANT SELECT ON DOC_DETAIL_VIRTUAL TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOCCHECKDATA TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_UPDBLOCKINFO_BY_DOC TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_CREDITCOMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_CREDITREINITCOMMIT TO PROCEDURE PR_DOC_COMMIT;
GRANT EXECUTE ON PROCEDURE PR_DOEVENT TO PROCEDURE PR_DOC_COMMIT;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_CREDITREINIT;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO PROCEDURE PR_DOC_SPLITBYBASEAGENT;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_DOC_COMMIT TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_TMP (
    DOC_ID type of DM_ID,
    SESSION_ID type of DM_ID)
as
declare variable BASE_TYPE type of DM_STATUS;
declare variable SUMMA type of DM_DOUBLE;
declare variable SUMMA_O type of DM_DOUBLE;
declare variable SUM_NDSO type of DM_DOUBLE;
declare variable SUM_NDSR DM_DOUBLE;
declare variable DSCSUMMA type of DM_DOUBLE;
declare variable SUM_DSC type of DM_DOUBLE;
declare variable PRICE_TYPE DM_STATUS;
declare variable CONTRACT_ID DM_ID;
declare variable DOC_DETAIL_ID DM_ID;
declare variable EGAIS_BARCODE DM_BLOBTEXT;
declare variable PRODUCER_INN DM_TEXT;
declare variable PRODUCER_KPP DM_TEXT;
declare variable DDA_QUANT DM_DOUBLE;
declare variable DDA_DOC_ID DM_ID;
begin
    delete from EGAIS_DETAIL;

    for select dd.id, p.egais_barcode, p.producer_inn, p.producer_kpp, dd.quant, dd.doc_id
      from doc_detail dd
       left join parts p on dd.part_id = p.id
       left join docs d on d.id = dd.doc_id
      where dd.doc_commitdate > '15.06.2016' and status = 1 and doc_type in (3,9)
       into :DOC_DETAIL_ID, :EGAIS_BARCODE, :PRODUCER_INN, :PRODUCER_KPP, :DDA_QUANT, :DDA_DOC_ID do
     execute procedure PR_EGAIS_DETAIL_OBSHEPIT (DOC_DETAIL_ID, :EGAIS_BARCODE, :PRODUCER_INN, :PRODUCER_KPP, 0, 0);

end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT,DELETE ON EGAIS_DETAIL TO PROCEDURE PR_TMP;
GRANT SELECT ON DOC_DETAIL TO PROCEDURE PR_TMP;
GRANT SELECT ON PARTS TO PROCEDURE PR_TMP;
GRANT SELECT ON DOCS TO PROCEDURE PR_TMP;
GRANT EXECUTE ON PROCEDURE PR_EGAIS_DETAIL_OBSHEPIT TO PROCEDURE PR_TMP;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_TMP TO PROCEDURE PR_CREDITREINIT;
GRANT EXECUTE ON PROCEDURE PR_TMP TO PROCEDURE PR_DOC_SPLITBYBASEAGENT;
GRANT EXECUTE ON PROCEDURE PR_TMP TO STANDART;
GRANT EXECUTE ON PROCEDURE PR_TMP TO SYSDBA;

execute procedure PR_TMP;

DROP PROCEDURE PR_TMP;

/******************************************************************************/

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



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: VW_ALKO */
CREATE OR ALTER VIEW VW_ALKO(
    ID,
    DOC_TYPE,
    CAPTION,
    DOCDATE,
    EGAIS_BARCODE,
    SNAME,
    ORIG_CODE,
    CAPACITY,
    QUANT)
AS
select d.id, d.doc_type, d.caption, d.docdate, ed.egais_barcode as egais_barcode, dd.sname, dd.EGAIS_PRODUCT_VCODE as orig_code, dd.capacity, -ed.quant
from egais_detail ed
left join vw_doc_detail dd on dd.id = ed.doc_detail_id
left join vw_docs d on d.id = dd.doc_id
where d.doc_type = 3
;




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