/*
* Автор: Галкин А.И.
* Дата: 2014-01-17
* Назначение: Обновление до правильной версии PKG_COMMON
* Изменяются объекты:
		add function PKG_COMMON.DecodeBase64
*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
  Для печати сообщений:
  DBMS_OUTPUT.put_line('<Message>');
*/
whenever sqlerror exit 1;
set serveroutput on;
set echo off;

declare
	filename      varchar2(256) := 'sfm.database-update.2014-01-17.UpdatePKG_COMMON.sql';
	purpose       varchar2(4000) := 'Обновление до правильной версии PKG_COMMON';
	script_exists number := 0;
begin

	--delete from DB_Update_History where script_name = filename;	commit;
	select count(*)
		into script_exists
		from DB_Update_History
	 where script_name = filename;

	if (script_exists > 0) then
		RAISE_APPLICATION_ERROR(-20001, '"' || filename || '" уже присутствует в базе');
	else
		insert into DB_Update_History
			(script_name, purpose)
		values
			(filename, purpose);
	end if;

	commit;

exception
	when others then
		rollback;
		raise;
end;

/

CREATE OR REPLACE PACKAGE BODY KFM."PKG_COMMON" as

  function toClOb(pBlob blob) return clob is
    x clob;

    dest_offset NUMBER := 1;
    src_offset  NUMBER := 1;
    amount      INTEGER := dbms_lob.lobmaxsize;
    blob_csid   NUMBER := dbms_lob.default_csid;
    lang_ctx    INTEGER := dbms_lob.default_lang_ctx;
    warning     INTEGER := dbms_lob.NO_WARNING;
  begin
    if length(pBlob) = 0 or pBlob is null then
      return null;
    end if;
    DBMS_LOB.CREATETEMPORARY(lob_loc => x, cache => TRUE);
    dbms_lob.CONVERTTOCLOB(x, pBlob, amount, dest_offset, src_offset, blob_csid, lang_ctx, warning);
    return x;
  end;

  function GetFormattedGuid(pguid varchar2,
                            pres  out varchar2) return number is
    result    number default 0;
    ptempgiud varchar2(50 char);
  begin
    ptempgiud := replace(upper(trim(pguid)), '-');
    if length(ptempgiud) <> 32 then
      result := -1300; -- Неверный формат GUID
      pres   := pguid;
    else
      pres := substr(replace(upper(trim(ptempgiud)), '-'), 1, 8) || '-' ||
              substr(replace(upper(trim(ptempgiud)), '-'), 9, 4) || '-' ||
              substr(replace(upper(trim(ptempgiud)), '-'), 13, 4) || '-' ||
              substr(replace(upper(trim(ptempgiud)), '-'), 17, 4) || '-' ||
              substr(replace(upper(trim(ptempgiud)), '-'), 21, 12);
    end if;
    return result;
  end;

  function GetSimpleGuid(pGuid varchar2) return varchar2 is
    res varchar2(50 char);
  begin
    res := replace(upper(trim(pguid)), '-');
    return res;
  end;

  function GetBool(param in number) return varchar2 is
  begin
    case param
      when 0 then
        return 'False';
      when 1 then
        return 'True';
      else
        return 'False';
    end case;
  end;

  function IsRightCodeVal(plogid     number,
                          pTableName varchar2,
                          pFieldName varchar2,
                          pCodeType  number,
                          pException out varchar2) return number is
    result number default 0;

  begin

    execute immediate 'declare
                       cursor pcur is select ' || pFieldName || ' FieldVal from ' || pTableName || ' where log_id=' || plogid || ';
                       pExc varchar2(3000 char);
                       pError varchar2(1000 char);
                    begin
                        pExc:='''';
                        for prec in pcur
                        loop
                           if prec.FieldVal is not null then
                              if not pkg_analytic.containsCode(' || pCodeType || ',prec.FieldVal) then
                               pError:=''Значение p''||:fname||'' код не соответствует справочнику: ''||prec.FieldVal;
                               pExc:=pExc||pError||chr(10);
                              end if;
                           end if;
                        end loop;
                        :pExcFull := pExc;
                    end;'
      using in pFieldName, out pException;
    if pException is not null then

      return - 1;
    end if;
    return result;
  end;

  procedure LogWork(pLogText in varchar2) is
    pragma autonomous_transaction;
  begin
    insert into logworktab
      (logtext,
       logtime)
    values
      (pLogText,
       sysdate());
    commit;
  end;

  function ConvertToNumber(val varchar2) return number is
    result number default 0;
  begin

    result := replace(val, ',', '.');
    return result;
  exception
    when others then
      result := replace(val, '.', ',');
      return result;
  end;

  function BoolToNumber(boolname in varchar2) return number is
  begin
    if boolname = 'True' then
      return 1;
    elsif boolname = 'False' then
      return 0;
    else
      return null;
    end if;
  end;

  procedure InsOrUpdMessagesListTable(ServerType in varchar2) is

    maxrole    number;
    proleid    number default 2;
    pcountid   number default 1;
    pTableName varchar2(30 char);
  begin
    case ServerType
      when 'SFM' then
        pTableName := 'MESSAGES_CFM';
      when 'KFM' then
        pTableName := 'MESSAGES_OFM';
      else
        return;
    end case;

    delete from mess_listdic_roledest;
    select sum(roleid)
    into maxrole
    from roles;
    loop
      exit when proleid > maxrole;

      insert into mess_listdic_roledest
        (roleid,
         softname,
         listnum,
         statusid,
         stagesid,
         doctype)
        select proleid,
               softname,
               mld.listnum,
               mls.statusid,
               mls.stagesid,
               mls.doctype
        from soft,
             messages_list_dic   mld,
             messages_list_setup mls
        where mld.tablename = pTableName
        and

               mls.listid = mld.listid
           and mld.rolemodule = softname
           and ((mld.listexclude = 1 and
               (mls.statusid, mls.stagesid, mls.doctype) not in
               (select mls1.statusid, mls1.stagesid, mls1.doctype
                    from messages_list_setup mls1, messages_list_dic mld1
                   where mld1.tablename = pTableName
                     and mls1.listid = mld1.listid
                     and mld1.listexclude = 1
                     and mld1.rolemodule = softname
                     and bitand(proleid, mld1.roledestination) = 0)) or
               (mld.listexclude = 0 and
               bitand(proleid, mld.roledestination) <> 0));

      proleid  := proleid + 2;
      pcountid := pcountid + 1;
    end loop;
  end;
  
    FUNCTION DecodeBase64 (txt IN CLOB)
       RETURN VARCHAR2
    IS
       v_varchar2   VARCHAR2 (4000);
       v_raw        RAW (4000);
    BEGIN
       SELECT DBMS_LOB.SUBSTR (txt, 4000, 1) INTO v_varchar2 FROM DUAL;

       v_raw := UTL_RAW.CAST_TO_RAW (txt);

       RETURN UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_decode (v_raw));
    END;

end PKG_Common;
/
