SET TERM ^ ;

create or alter procedure PR_SPLIT (
    SOURCE_STRING DM_BLOBTEXT,
    SPLIT_STRING DM_TEXT)
returns (
    RESULT_STRING DM_TEXT1024)
as
declare variable CHAR2 integer;
declare variable CHAR1 integer;
begin
  char1 = 1;
  char2 = 1;
  while (char2 > 0) do
    begin
      char2 = position(split_string, source_string, char1);
      if (char2 <> 0) then
        begin
          result_string = substring(source_string from char1 for (char2 - char1));
          char1 = char2 + char_length(split_string);
          suspend;
        end
      else
        if (char1 <= char_length(source_string)) then
          begin
            result_string = substring(source_string from char1);
            suspend;
          end
    end
end^

SET TERM ; ^

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

GRANT EXECUTE ON PROCEDURE PR_SPLIT TO SYSDBA;

/******************************************************************************/
/***                Generated by IBExpert 18.02.2020 9:24:24                ***/
/******************************************************************************/

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



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



CREATE TABLE WORDDICT (
    STATUS      DM_STATUS /* DM_STATUS = INTEGER */,
    INSERTDT    DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
    ORIGIN      DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    SUB         DM_TEXT /* DM_TEXT = VARCHAR(250) */,
    D$UUID      DM_UUID /* DM_UUID = CHAR(36) NOT NULL */,
    D$SRVUPDDT  DM_DATETIME /* DM_DATETIME = TIMESTAMP */
);




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

ALTER TABLE WORDDICT ADD CONSTRAINT PK_WORDDICT PRIMARY KEY (D$UUID);


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

CREATE INDEX WORDDICT_IDX2 ON WORDDICT COMPUTED BY (upper(origin));


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



SET TERM ^ ;



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



/* Trigger: WORDDICT_AD_DISTR */
CREATE OR ALTER TRIGGER WORDDICT_AD_DISTR FOR WORDDICT
ACTIVE AFTER DELETE POSITION 0
AS
begin
  update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WORDDICT',old.d$uuid,2,null)
  matching (TABLENAME,UUID);
end
^


/* Trigger: WORDDICT_BI_DISTR */
CREATE OR ALTER TRIGGER WORDDICT_BI_DISTR FOR WORDDICT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.d$uuid is null) then
  begin
    new.d$uuid=UUID_TO_CHAR(GEN_UUID());
    new.d$srvupddt='2000-01-01';
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WORDDICT',new.d$uuid,0,null) matching (TABLENAME,UUID);
  end
end
^


/* Trigger: WORDDICT_BU_DISTR */
CREATE OR ALTER TRIGGER WORDDICT_BU_DISTR FOR WORDDICT
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if (new.D$SRVUPDDT=old.D$SRVUPDDT) then
    update or insert into g$distribute (TABLENAME,UUID,SOPER,FROM_PROFILE_ID) values ('WORDDICT',new.d$uuid,1,null)
    matching (TABLENAME,UUID);
end
^

SET TERM ; ^



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


SET TERM ^ ;

create or alter procedure PR_FINDEX (
    STRFIND DM_TEXT,
    FIELDS DM_TEXT1024)
returns (
    PART_ID DM_ID)
as
declare variable STR1 DM_TEXT1024;
declare variable STR2 DM_TEXT1024;
declare variable STR3 varchar(5000);
declare variable STR4 DM_TEXT1024;
declare variable STR5 varchar(5000);
declare variable STR6 DM_TEXT1024;
declare variable ISDICT smallint;
begin
  if (char_length(trim(strfind)) = 0) then exit;

  isdict = iif((exists (select count(*) from RDB$RELATIONS where upper(RDB$RELATION_NAME) = 'WORDDICT')), 1, 0);
  strfind = upper(trim(strfind));
  str4 = '';
  str5 = '';
  for select result_string from pr_split(:fields, ';') into :str1 do
    begin
      str3 = '';
      fields = '';
      for select result_string from pr_split(:strfind, ' ') into :str2 do
        if (char_length(str2) > 1) then
          begin
            if ((isdict = 1) and (exists (select * from worddict where upper(origin) = upper(:str2)))) then
              select sub from worddict where upper(origin) = upper(:str2) into :str2;
            if (char_length(str4) = 0) then --если первое поле и первое слово в строке поиска, ищем STARTING WITH по МНН
              begin
                str4 = '(upper(w.' || str1 || ') starting with ''' || str2 || ''')';
                str6 = '(upper(v.preparedvalue) starting with ''' || str2 || ''')';
              end
            if (fields = '-') then --если НЕ первое слово в строке поиска, ищем CONTAINING по списку полей
              str3 = str3 || ' and (upper(cast(w.' || str1 || ' as varchar(250))) containing ''' || str2 || ''')';
            fields = '-';
          end
      if (char_length(str3) > 0) then str5 = str5 || ' or (' || substring(str3 from 6) || ')';
    end
  if (char_length(str4 || str5) > 0) then
    begin
      str5 = 'select w.part_id from (select v.id from (select distinct v.preparedvalue from wares w join vals v on w.name_id = v.id ' ||
        'where ' || str4 || ' and char_length(v.preparedvalue) > 2 union select distinct v.preparedvalue from wares w join vals v on w.name_id = v.id where ' ||
        str6 || ' and char_length(v.preparedvalue) > 2) a join vals v on a.preparedvalue = v.preparedvalue) b ' ||
        'join warebase w on b.id = w.name_id' || iif(char_length(str5) > 0, ' where ' || substring(str5 from 5), '');
      for execute statement (:str5) into :part_id do suspend;
    end
end^

SET TERM ; ^

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

GRANT EXECUTE ON PROCEDURE PR_SPLIT TO PROCEDURE PR_FINDEX;
GRANT SELECT ON WORDDICT TO PROCEDURE PR_FINDEX;

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

GRANT EXECUTE ON PROCEDURE PR_FINDEX TO SYSDBA;

SET TERM ^ ;

create or alter procedure PR_FINDQUERY (
    STRFIND DM_TEXT,
    FIELDS DM_TEXT1024)
returns (
    QUERYTEXT varchar(4096))
as
declare variable STR1 DM_TEXT1024;
declare variable STR2 DM_TEXT1024;
declare variable STR3 varchar(5000);
declare variable ISDICT smallint;
declare variable STRFIRST DM_TEXT1024;
begin
  isdict = iif((exists (select count(*) from RDB$RELATIONS where upper(RDB$RELATION_NAME) = 'WORDDICT')), 1, 0);
  strfind = upper(trim(replace(strfind, '''', '')));
  str3 = '';
  strfirst = '';
  for select result_string from pr_split(:fields, ';') into :str1 do
    begin
      querytext = '';
      for select result_string from pr_split(:strfind, ' ') into :str2 do
        if (char_length(str2) > 1) then
          begin
            if ((isdict = 1) and (exists (select * from worddict where upper(origin) = upper(:str2)))) then
              select sub from worddict where upper(origin) = upper(:str2) into :str2;
             if (char_length(strfirst) = 0) then strfirst = upper(str2);
            querytext = querytext || ' and upper(cast(' || str1 || ' as varchar(250))) containing ''' || str2 || '''';
          end
      if (char_length(querytext) > 0) then str3 = str3 || ' or (' || substring(querytext from 6) || ')';
    end
  str3 = substring(str3 from 5);

  if (char_length(str3) = 0) then
    querytext = 'select * from vw_warebase_kassa order by sname, part_id';
  else
    querytext = 'select iif(upper(a.sname) starting with ''' || strfirst || ''', 0, 1) as ordr, ' ||
      'a.* from (select * from (select * from vw_warebase_kassa where ' || str3 ||
      ') union select k.* from pr_findex(''' || strfind || ''', ''' || fields || ''') pf left join vw_warebase_kassa k ' ||
      'on pf.part_id = k.part_id) a order by ordr, sname, part_id';
  suspend;
end^

SET TERM ; ^

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

GRANT EXECUTE ON PROCEDURE PR_SPLIT TO PROCEDURE PR_FINDQUERY;
GRANT SELECT ON WORDDICT TO PROCEDURE PR_FINDQUERY;

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

GRANT EXECUTE ON PROCEDURE PR_FINDQUERY TO SYSDBA;