SET TERM ^ ;

create or alter procedure UTPR_VALUE4SQL (
    source type of DM_TEXT)
returns (
    S varchar(255))
as
begin
  if (source is null) then
    s='NULL';
  else
    s=''''||replace(trim(source),'''','''''')||'''';
  suspend;
end^

SET TERM ; ^

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

GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO SYSDBA;


SET TERM ^ ;

create or alter procedure AA_COWA_GET_ACTUAL_WARE_ID (
    NAME_ID type of DM_UUID,
    IZG_ID type of DM_UUID,
    COUNTRY_ID type of DM_UUID,
    BARCODE type of DM_TEXT)
returns (
    ACTUAL_WARE_ID type of DM_UUID)
as
declare variable MAXLOG_INSERTDT type of DM_DATETIME;
declare variable ID type of DM_UUID;
declare variable LOG_INSERTDT type of DM_DATETIME;
declare variable TMPACTUAL_WARE_ID type of DM_UUID;
begin
   maxlog_insertdt='01.01.2000';
   for select id from wares where NAME_ID=:name_id and BARCODE=:BARCODE into id do
   begin
     LOG_INSERTDT=null;
     select first 1 log_insertdt, actual_ware_id from wares_log where actual_ware_id=:id order by LOG_INSERTDT desc into LOG_INSERTDT, tmpactual_ware_id;
     if ((LOG_INSERTDT is not null) and (LOG_INSERTDT>maxlog_insertdt)) then
     begin
       maxlog_insertdt=LOG_INSERTDT;
       actual_ware_id=tmpactual_ware_id;
     end
   end 
   suspend;
end^

SET TERM ; ^

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

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

GRANT EXECUTE ON PROCEDURE AA_COWA_GET_ACTUAL_WARE_ID TO SYSDBA;

/******************************************************************************/
/***               Generated by IBExpert 16.02.2024 12:27:38                ***/
/******************************************************************************/

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



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


CREATE GENERATOR GEN_COWA_4REP_ID;

CREATE TABLE COWA_4REP (
    ID              DM_ID NOT NULL /* DM_ID = BIGINT */,
    WARE_ID         DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    ACTUAL_WARE_ID  DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    INSERTDT        DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);



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

ALTER TABLE COWA_4REP ADD CONSTRAINT PK_COWA_4REP PRIMARY KEY (ID);


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



SET TERM ^ ;



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



/* Trigger: COWA_4REP_BI */
CREATE OR ALTER TRIGGER COWA_4REP_BI FOR COWA_4REP
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_cowa_4rep_id,1);
  new.insertdt=current_timestamp;
end
^
SET TERM ; ^



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




CREATE INDEX DOC_DETAIL_DELETED_IDX8 ON DOC_DETAIL_DELETED (WARE_ID);

ALTER TABLE WARES
ADD WHASH DM_ID;

ALTER TABLE WARES_LOG
ADD ACTUAL_WARE_ID DM_UUID_NULL;


CREATE INDEX WARES_LOG_IDX10 ON WARES_LOG (ACTUAL_WARE_ID);

delete from DOC_DETAIL_ACTIVE_TREB;
delete from DOC_DETAIL_VIRTUAL;


SET TERM ^ ;

create or alter procedure AA_COWA_COMPRESS_WARE (
    ID type of DM_UUID,
    ACTUAL_WARE_ID type of DM_UUID,
    DOUPDATE integer)
returns (
    S type of DM_BLOBTEXT)
as
declare variable OLD_NAME_ID DM_UUID_NULL;
declare variable NEW_NAME_ID DM_UUID_NULL;
begin
  if (DOUPDATE is null) then DOUPDATE=0;
    s=null;
    select name_id from wares where id=:id into :old_name_id;
    select name_id from wares where id=:ACTUAL_WARE_ID into :new_name_id;

/*cf20180306    select
      'update or insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,D$UUID,D$SRVUPDDT,SNAME,MGN_NAME,MGN_ID,MGN_SOURCE,WHASH) values '||
        '('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','''||INSERTDT||''','||PACKET||','''||L_ID||''','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(SNAME))||','||(select s from UTPR_VALUE4SQL(MGN_NAME))||','||(select s from UTPR_VALUE4SQL(MGN_ID))||','||(select s from UTPR_VALUE4SQL(substring(MGN_SOURCE from 1 for 250)))||','||(select s from UTPR_VALUE4SQL(WHASH))||');'
     from wares where id=:actual_ware_id into s;
*/
    select
      'update or insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,D$UUID,D$SRVUPDDT,SNAME,MGN_NAME,MGN_ID,MGN_SOURCE,WHASH) values '||
        '('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','''||INSERTDT||''','||PACKET||','''||L_ID||''','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(substring(SNAME from 1 for 250)))||','||(select s from UTPR_VALUE4SQL(substring(MGN_NAME from 1 for 250)))||','||(select s from UTPR_VALUE4SQL(MGN_ID))||','||(select s from UTPR_VALUE4SQL(substring(MGN_SOURCE from 1 for 250)))||','||(select s from UTPR_VALUE4SQL(WHASH))||');'
     from wares where id=:actual_ware_id into s;
    suspend;

    if (DOUPDATE<>0) then update wares_log set actual_ware_id=null, d$srvupddt=current_timestamp where id=:actual_ware_id and actual_ware_id is not null;
    s='update wares_log set actual_ware_id=null, d$srvupddt=current_timestamp where actual_ware_id is not null and id='''||:actual_ware_id||''';';
    suspend;

    if (DOUPDATE<>0) then update wares_log set actual_ware_id=:actual_ware_id, d$srvupddt=current_timestamp where actual_ware_id=:id;
    s='update wares_log set actual_ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where actual_ware_id='''||:id||''';';
    suspend;

    s=null;
    if (not exists(select id from wares_log where id=:id and actual_ware_id=:actual_ware_id)) then
    begin -- логично, что нет записи об удалении вареса - создаем ее, без g$distribute - будем отправлять по g$tasks, централизованно всем сразу, одним пакетом
      if (DOUPDATE<>0) then
        insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID,ACTUAL_WARE_ID, D$SRVUPDDT, LOG_INSERTDT)
          select ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,L_ID, :actual_ware_id, current_timestamp, current_timestamp from wares where id=:id;
      else s='нет записи об удалении вареса (WARE_ID='||id||', actual_ware_id='||actual_ware_id||')';
    end
    if (not exists(select id from  wares_log wl where wl.id=:id and actual_ware_id=:actual_ware_id)) then
       exception ex_wrong_db 'эээ';
    select first 1
      'update or insert into wares_log (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID,INSERTDT,PACKET,LOG_ID,LOG_INSERTDT,L_ID,D$UUID,D$SRVUPDDT,ACTUAL_WARE_ID) '||
        'values  ('''||ID||''','''||NAME_ID||''','''||IZG_ID||''','''||COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(ORIG_CODE))||','''||ORIG_NAME_ID||''','''||ORIG_IZG_ID||''','''||ORIG_COUNTRY_ID||''','||(select s from UTPR_VALUE4SQL(BARCODE))||','||Z_ID||','||(select s from UTPR_VALUE4SQL(SKLAD_ID))||','||(select s from UTPR_VALUE4SQL(INSERTDT))||','||PACKET||','''||LOG_ID||''','||(select s from UTPR_VALUE4SQL(LOG_INSERTDT))||','||L_ID||','''||D$UUID||''',current_timestamp,'||(select s from UTPR_VALUE4SQL(ACTUAL_WARE_ID))||');'
      from wares_log wl where wl.id=:id and actual_ware_id=:actual_ware_id order by log_insertdt into s;
    suspend;

    --Ольга в сделаем приведение в детализации контрактов
--    if (DOUPDATE<>0) then update AGENTS_CONTRACTS_DETAIL set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
--    s='update AGENTS_CONTRACTS_DETAIL set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
--    suspend;
    --Ольга В приведем в таблицах плана WARES_ADD_DATA WARES_PLAN
--    if (DOUPDATE<>0) then update WARES_ADD_DATA set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
--    s='update WARES_ADD_DATA set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
--    suspend;
--    if (DOUPDATE<>0) then update WARES_PLAN set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
--    s='update WARES_PLAN set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
--    suspend;
--    if (OLD_NAME_ID<>NEW_NAME_ID) then
--    begin
--      if (DOUPDATE<>0) then update WARES_PLAN set name_id=:new_name_id, d$srvupddt=current_timestamp where name_id=:old_name_id;
--      s='update WARES_PLAN set name_id='''||:new_name_id||''', d$srvupddt=current_timestamp where name_id='''||:old_name_id||''';';
--      suspend;
--    end

    --2018-07-26 A.K.
--    if (DOUPDATE<>0) then update WARES_ANALOG set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
--    s='update WARES_ANALOG set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
--    suspend;
--    if (DOUPDATE<>0) then update WARES_ANALOG set WARE_ID_ANALOG=:actual_ware_id, d$srvupddt=current_timestamp where WARE_ID_ANALOG=:id;
--    s='update WARES_ANALOG set WARE_ID_ANALOG='''||:actual_ware_id||''', d$srvupddt=current_timestamp where WARE_ID_ANALOG='''||:id||''';';
--    suspend;


    if (DOUPDATE<>0) then update parts set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
    s='update parts set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
    suspend;
--    if (DOUPDATE<>0) then update doc_detail_active set ware_id=:actual_ware_id where ware_id=:id;
    s='update doc_detail_active set ware_id='''||:actual_ware_id||''' where ware_id='''||:id||''';';
    suspend;
--    if (DOUPDATE<>0) then update warebase_distr set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
--    s='update warebase_distr set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
--    suspend;
    if (DOUPDATE<>0) then update doc_detail_active_treb set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
    s='update doc_detail_active_treb set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
    suspend;
    if (DOUPDATE<>0) then update DOC_DETAIL_DELETED set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
    s='update DOC_DETAIL_DELETED set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
    suspend;
    if (DOUPDATE<>0) then update DOC_DETAIL_VIRTUAL set ware_id=:actual_ware_id, d$srvupddt=current_timestamp where ware_id=:id;
    s='update DOC_DETAIL_VIRTUAL set ware_id='''||:actual_ware_id||''', d$srvupddt=current_timestamp where ware_id='''||:id||''';';
    suspend;
    if (DOUPDATE<>0) then delete from wares where id=:id;  --триггер при удалении из wares должен быть неактивен
    s='delete from wares where id='''||:id||''';';
    suspend;
    if (DOUPDATE<>0) then delete from g$distribute where uuid=:id and tablename='WARES';
    s='delete from g$distribute where uuid='''||:id||''' and tablename=''WARES'';';
    suspend;
  WHEN ANY DO
    EXCEPTION ex_wrong_db '[id='||id||';  ACTUAL_WARE_ID='||ACTUAL_WARE_ID||']';
end^

SET TERM ; ^

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

GRANT SELECT,DELETE ON WARES TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT EXECUTE ON PROCEDURE UTPR_VALUE4SQL TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,INSERT,UPDATE ON WARES_LOG TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON PARTS TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_ACTIVE_TREB TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_DELETED TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,UPDATE ON DOC_DETAIL_VIRTUAL TO PROCEDURE AA_COWA_COMPRESS_WARE;
GRANT SELECT,DELETE ON G$DISTRIBUTE TO PROCEDURE AA_COWA_COMPRESS_WARE;

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

GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARE TO PROCEDURE AA_COWA_COMPRESS_WARES;
GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARE TO PROCEDURE AA_COWA_EQUIV_WARES;
GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARE TO SYSDBA;


SET TERM ^ ;

create or alter procedure AA_COWA_COMPRESS_WARES (
    DOUPDATE integer)
as
declare variable ID type of DM_UUID;
declare variable ACTUAL_WARE_ID type of DM_UUID;
declare variable NAME_ID type of DM_UUID;
declare variable IZG_ID type of DM_UUID;
declare variable COUNTRY_ID type of DM_UUID;
declare variable BARCODE type of DM_TEXT;
declare variable WLIST type of DM_BLOBTEXT;
declare variable MIN_ACTUAL_WARE_ID type of DM_UUID;
declare variable TS type of DM_BLOBTEXT;
declare variable I integer;
declare variable SHIFT integer;
declare variable TASK_ID type of DM_ID;
declare variable S DM_BLOBTEXT;
begin
  for select min(id), NAME_ID, BARCODE, list(distinct ''''||id||'''') from wares
--  where sname containing 'пробио'
    group by NAME_ID,BARCODE
  having count(1)>1
  into min_actual_ware_id, NAME_ID,BARCODE, WLIST do
  begin
   actual_ware_id=null;
   if (char_length(wlist)>10000) then
     select ACTUAL_WARE_ID from AA_COWA_GET_ACTUAL_WARE_ID(:NAME_ID,:IZG_ID,:COUNTRY_ID,:BARCODE) into actual_ware_id;
   else
     execute statement 'select first 1 actual_ware_id from wares_log where actual_ware_id in ('||wlist||') order by log_insertdt desc' into actual_ware_id;
   if (actual_ware_id is null) then
     actual_ware_id=min_actual_ware_id;
   if (not exists(select id from wares where id=:actual_ware_id)) then
     exception ex_wrong_db 'не найден актуальный товар в wares: actual_ware_id='||actual_ware_id;
   for select id from wares where NAME_ID=:name_id and BARCODE=:BARCODE and id<>:actual_ware_id into :id do
   begin
     insert into COWA_4REP (ware_id, actual_ware_id) values (:id,:actual_ware_id);
     for select s from AA_COWA_COMPRESS_WARE(:id,:actual_ware_id,:DOUPDATE) into :s do
     begin
       if (s is null) then
         exception ex_wrong_db 'id='||id||'; actual_ware_id='||actual_ware_id;
       else
         execute statement :s;
     end
   end
  end
end^

SET TERM ; ^

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

GRANT SELECT ON WARES TO PROCEDURE AA_COWA_COMPRESS_WARES;
GRANT EXECUTE ON PROCEDURE AA_COWA_GET_ACTUAL_WARE_ID TO PROCEDURE AA_COWA_COMPRESS_WARES;
GRANT INSERT ON COWA_4REP TO PROCEDURE AA_COWA_COMPRESS_WARES;
GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARE TO PROCEDURE AA_COWA_COMPRESS_WARES;

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

GRANT EXECUTE ON PROCEDURE AA_COWA_COMPRESS_WARES TO SYSDBA;




SET TERM ^ ;

CREATE OR ALTER trigger parts_wp_aiu0 for parts
inactive after insert or update position 0
AS
begin
  execute procedure updpr_warebaseparts(new.id,null,null,null);
end^

SET TERM ; ^


execute procedure AA_COWA_COMPRESS_WARES(1);



SET TERM ^ ;

CREATE OR ALTER trigger parts_wp_aiu0 for parts
active after insert or update position 0
AS
begin
  execute procedure updpr_warebaseparts(new.id,null,null,null);
end^

SET TERM ; ^


--execute procedure updpr_warebaseparts(null,null,null,null);
--execute procedure DBGPR_MAKEWAREBASEFROMDOCS;