/*
* Автор: Борисов Д.Г.
* Дата: 2014-04-21
* Задача: http://jira.softage.ru/jira/browse/SDFO-293
* Назначение: Поддержка совместимости нескольких ролей одного пользователя
* Изменяются объекты:
        alter table MESSAGES_TEMPLATES
		create or replace package body PKG_TEMPLATE_CFM

*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
  Для печати сообщений:
  DBMS_OUTPUT.put_line('<Message>');
*/
whenever sqlerror exit 1;
set serveroutput on;
set echo off;
declare
		filename      varchar2(256) := 'sfm.database-update.2014-04-21.MultipleRolesSupport.sql';
		purpose       varchar2(4000) := 'Поддержка совместимости нескольких ролей одного пользователя';
		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;

	/*
	****** Begin update script ******
	*/
		-- Транзакционный апдейт
		update messages_templates set role_id = 4 where role_id is null;
		delete from messages_templates where role_id not in (2, 4);
		update messages_templates set stage_id = 1 where role_id = 2;
		update messages_templates set role_id = null;
	/*
	****** End update script ******
	*/
	commit;

exception
	when others then
		rollback;
		raise;
end;
/

	/*
	****** Begin update script ******
	*/
		-- Необходимы свои транзакции, если требуются
		alter table messages_templates rename column role_id to is_editable;
		alter table messages_templates modify is_editable char default 'n' check (is_editable in ('y','n'));
		update messages_templates set is_editable = 'n';
		alter table messages_templates modify is_editable char default 'n' check (is_editable in ('y','n')) not null;
		update messages_templates set is_editable = 'y' where stage_id = 1;
		alter table messages_templates drop column stage_id;
		COMMENT ON COLUMN "KFM"."MESSAGES_TEMPLATES"."TEMPLATE_ID" IS 'Идентификатор шаблона';
		COMMENT ON COLUMN "KFM"."MESSAGES_TEMPLATES"."IS_EDITABLE" IS 'n - нередактриуемый шаблон, y - редактируемы шаблон для нового сообщения или бумажной формы';
		COMMENT ON COLUMN "KFM"."MESSAGES_TEMPLATES"."CREATION_DATE" IS 'Дата создания записи';
		COMMENT ON COLUMN "KFM"."MESSAGES_TEMPLATES"."STATE" IS '0 - неактивен, 1 - активен';
		COMMENT ON COLUMN "KFM"."MESSAGES_TEMPLATES"."TEMPLATE_XML" IS 'Тело шаблона';
		COMMENT ON COLUMN "KFM"."MESSAGES_TEMPLATES"."MSG_TYPE_ID" IS 'Идентификатор типа сообщения';
	/*
	****** End update script ******
	*/
create or replace
PACKAGE BODY       "PKG_TEMPLATE_CFM" as

  function GetTemplate(pMessId      in number,
                       pTemplateXml out clob) return number as
    v_result   number default 0;
    l_user_id  number;
    l_role     number default 0;
    l_mess_cfm mess_cfm%rowtype;
  begin
    --идентификатор текущего пользователя
    l_user_id := pkg_users.GetCurrentUserId;
    --роль с максимальным идентификатором текущего пользователя
    select sum(roleid)
	into l_role
	from (select distinct ta.roleid
        from user_roles ta
       where ta.userid = l_user_id
         and sysdate between ta.datebegin and ta.dateend);
    --если ролей нету то ошибка
    if l_role = 0 then
      v_result := -2000;
      return v_result;
    end if;
    --если сообщение новое захардкодим тип и этап прохождения сообщения
    --иначе выбирем тип и этап прохождения из базы
    if pMessId is null then
      l_mess_cfm.mess_type := 1;
      l_mess_cfm.stage     := 1;
    else
      select *
      into l_mess_cfm
      from mess_cfm
      where mess_cfm_id = pMessId;
    end if;
    --выбираем шаблон для создания уведомления по роли пользователя типу сообщения и этапу прохождения сообщения в системе
    BEGIN
      if (l_mess_cfm.stage in (1, 5) and bitand(l_role, 2) <> 0) then
        -- шаблон нового сообщения или бумажной формы для оператора ввода
        select template_xml
        into pTemplateXml
        from messages_templates
        where msg_type_id = l_mess_cfm.mess_type
        and is_editable = 'y'
        and state = 1;
      else
        select template_xml
        into pTemplateXml
        from messages_templates
        where msg_type_id = l_mess_cfm.mess_type
        and is_editable = 'n'
        and state = 1;
      end if;

      if pTemplateXml is not null then
        return v_result;
      end if;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        null;
    END;
    --выбираем шаблон для создания уведомления по роли пользователя  типу сообщения

    --если не найдено ниодго подходящего шаблона возвращаем ошибку
    v_result := -2000;
    return v_result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode, sqlerrm, 'GetTemplate');
      v_result := -2000;
      return v_result;
  end GetTemplate;

end PKG_TEMPLATE_CFM;
/
