/*
* Автор: Галкин А.И.
* Дата: 2014-10-01
* Задача: http://jira.softage.ru/jira/browse/SDFO-355
* Назначение: Добавление сущностей необходимых для синхронизации НСИ и СЛК
* Изменяются объекты:
		create table EIAS_EXP_FM1_REVISION
        create table EIAS_EXP_FM1_REV_ANALYTICTYPES
        create table EIAS_EXP_ANALYTICTYPES
        create table EIAS_EXP_ANALYTICCODES
		add function ToGuidWithDash
		add package PKG_NSI_SYNC
		add procedure LoadMessageSchemaFromFile
		add procedure LoadMessageXamlFromFile
*/

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

declare
	filename      varchar2(256) := 'sfm.database-update.2014-10-01.NSI_SYNC.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 ******
	*/
		-- Транзакционный апдейт
		-- execute immediate '';
	/*
	****** End update script ******
	*/
	commit;

exception
	when others then
		rollback;
		raise;
end;
/


CREATE TABLE KFM.EIAS_EXP_FM1_REVISION
(
  FM1_REVISION_GUID  VARCHAR2(32 CHAR),
  FM1_VERSION        INTEGER                    NOT NULL,
  START_DATE         DATE                       NOT NULL,
  END_DATE           DATE
);

COMMENT ON TABLE KFM.EIAS_EXP_FM1_REVISION IS 'Ревизии формы ФМ1';

COMMENT ON COLUMN KFM.EIAS_EXP_FM1_REVISION.FM1_REVISION_GUID IS 'GUID ревизии ФМ1';

COMMENT ON COLUMN KFM.EIAS_EXP_FM1_REVISION.FM1_VERSION IS 'Номер версии ФМ1 (1,2,4-текущая)';

COMMENT ON COLUMN KFM.EIAS_EXP_FM1_REVISION.START_DATE IS 'Дата вступления в силу данного FM1_REVISION_GUID';

COMMENT ON COLUMN KFM.EIAS_EXP_FM1_REVISION.END_DATE IS 'Дата, после наступления которой СДФО должно отклонять формы ФМ1 с данным FM1_REVISION_GUID';

CREATE OR REPLACE PUBLIC SYNONYM EIAS_EXP_FM1_REVISION FOR KFM.EIAS_EXP_FM1_REVISION;

ALTER TABLE KFM.EIAS_EXP_FM1_REVISION ADD (
  PRIMARY KEY
  (FM1_REVISION_GUID)
  USING INDEX
  ENABLE VALIDATE);

GRANT DELETE, INSERT, SELECT, UPDATE ON KFM.EIAS_EXP_FM1_REVISION TO KFM_ROLE;

CREATE TABLE KFM.EIAS_EXP_FM1_REV_ANALYTICTYPES
(
  FM1_REVISION_GUID  VARCHAR2(32 CHAR)          NOT NULL,
  ANALYTICTYPE       INTEGER                    NOT NULL
);

COMMENT ON TABLE KFM.EIAS_EXP_FM1_REV_ANALYTICTYPES IS 'Таблица связи справочников с ревизией формы ФМ1';

COMMENT ON COLUMN KFM.EIAS_EXP_FM1_REV_ANALYTICTYPES.FM1_REVISION_GUID IS 'GUID ревизии ФМ1';

COMMENT ON COLUMN KFM.EIAS_EXP_FM1_REV_ANALYTICTYPES.ANALYTICTYPE IS 'Идентификатор справочника из ANALYTICTYPES';

CREATE OR REPLACE PUBLIC SYNONYM EIAS_EXP_FM1_REV_ANALYTICTYPES FOR KFM.EIAS_EXP_FM1_REV_ANALYTICTYPES;

ALTER TABLE KFM.EIAS_EXP_FM1_REV_ANALYTICTYPES ADD (
  PRIMARY KEY
  (FM1_REVISION_GUID, ANALYTICTYPE)
  USING INDEX
  ENABLE VALIDATE);


GRANT DELETE, INSERT, SELECT, UPDATE ON KFM.EIAS_EXP_FM1_REV_ANALYTICTYPES TO KFM_ROLE;

CREATE TABLE KFM.EIAS_EXP_ANALYTICTYPES
(
  ANALYTICTYPE  INTEGER,
  DATACODE      INTEGER                         NOT NULL,
  CHILDTYPE     INTEGER,
  NAME          VARCHAR2(4000 char)                 NOT NULL,
  NAME_KZ       VARCHAR2(4000 char),
  SHORTNAME     VARCHAR2(1000 char),
  SHORTNAME_KZ  VARCHAR2(1000 char),
  START_DATE    DATE
);

COMMENT ON TABLE KFM.EIAS_EXP_ANALYTICTYPES IS 'Тип справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICTYPES.ANALYTICTYPE IS 'Идентификатор справочника. Вычисляется из типа справочника и номера версии по формуле: номер версии*10000 + DATACODE';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICTYPES.DATACODE IS 'Тип справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICTYPES.CHILDTYPE IS 'Идентификатор (ANALYTICTYPE)справочника следующего уровня в иерархии.';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICTYPES.NAME IS 'Название справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICTYPES.NAME_KZ IS 'Название справочника на казахском';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICTYPES.SHORTNAME IS 'Короткое название справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICTYPES.SHORTNAME_KZ IS 'Короткое название справочника на казахском';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICTYPES.START_DATE IS 'Дата вступления справочника в силу';

CREATE OR REPLACE PUBLIC SYNONYM EIAS_EXP_ANALYTICTYPES FOR KFM.EIAS_EXP_ANALYTICTYPES;


ALTER TABLE KFM.EIAS_EXP_ANALYTICTYPES ADD (
  PRIMARY KEY
  (ANALYTICTYPE)
  USING INDEX
  ENABLE VALIDATE);

GRANT DELETE, INSERT, SELECT, UPDATE ON KFM.EIAS_EXP_ANALYTICTYPES TO KFM_ROLE;


CREATE TABLE KFM.EIAS_EXP_ANALYTICCODES
(
  ANALYTICTYPE  INTEGER                         NOT NULL,
  ANALYTICCODE  INTEGER                         NOT NULL,
  EXTRACODE     VARCHAR2(100 CHAR),
  PARENTCODE    INTEGER,
  NAME          VARCHAR2(4000 char)                 NOT NULL,
  NAME_KZ       VARCHAR2(4000 char),
  SHORTNAME     VARCHAR2(1000 char),
  SHORTNAME_KZ  VARCHAR2(1000 char)
);

COMMENT ON TABLE KFM.EIAS_EXP_ANALYTICCODES IS 'Значения справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICCODES.ANALYTICTYPE IS 'Идентификатор справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICCODES.ANALYTICCODE IS 'Код элемента справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICCODES.EXTRACODE IS 'Дополнительный код элемента справочника (Например для стран: RU, KZ, ...)';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICCODES.PARENTCODE IS 'Код элемента из родительского справочника (справочника у которого ANALYTICTYPES.CHILDTYPE = ANALYTICTYPE данной записи)';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICCODES.NAME IS 'Название элемента справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICCODES.NAME_KZ IS 'Название элемента справочника на казахском';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICCODES.SHORTNAME IS 'Короткое название элемента справочника';

COMMENT ON COLUMN KFM.EIAS_EXP_ANALYTICCODES.SHORTNAME_KZ IS 'Короткое название элемента справочника на казахском';


CREATE OR REPLACE PUBLIC SYNONYM EIAS_EXP_ANALYTICCODES FOR KFM.EIAS_EXP_ANALYTICCODES;


ALTER TABLE KFM.EIAS_EXP_ANALYTICCODES ADD (
  PRIMARY KEY
  (ANALYTICTYPE, ANALYTICCODE)
  USING INDEX
  ENABLE VALIDATE);

GRANT DELETE, INSERT, SELECT, UPDATE ON KFM.EIAS_EXP_ANALYTICCODES TO KFM_ROLE;

CREATE OR REPLACE FUNCTION KFM.ToGuidWithDash (guid VARCHAR2)
   RETURN VARCHAR2
IS
   result   VARCHAR2 (36);
BEGIN
   SELECT    SUBSTR (guid, 1, 8)
          || '-'
          || SUBSTR (guid, 9, 4)
          || '-'
          || SUBSTR (guid, 13, 4)
          || '-'
          || SUBSTR (guid, 17, 4)
          || '-'
          || SUBSTR (guid, 21, 12)
     INTO result
     FROM DUAL;

   RETURN result;
END ToGuidWithDash;
/

CREATE OR REPLACE PUBLIC SYNONYM ToGuidWithDash FOR KFM.ToGuidWithDash;


GRANT EXECUTE ON KFM.ToGuidWithDash TO KFM_ROLE;


CREATE OR REPLACE PACKAGE KFM.PKG_NSI_SYNC
AS
   /******************************************************************************
      NAME:       PKG_NSI_SYNC
      PURPOSE:    Синхронизация НСИ между подсистемами
   ******************************************************************************/

   /*
   *** Синхронизация таблиц внутреннего сегмента с СФМ
   */
   PROCEDURE EIAS_2_KFM (fm1InsertRowCount   OUT INTEGER,
                         fm1UpdateRowCount   OUT INTEGER,
                         atInsertRowCount    OUT INTEGER,
                         atUpdateRowCount    OUT INTEGER,
                         acInsertRowCount    OUT INTEGER,
                         acUpdateRowCount    OUT INTEGER);
END PKG_NSI_SYNC;
/


CREATE OR REPLACE PUBLIC SYNONYM PKG_NSI_SYNC FOR KFM.PKG_NSI_SYNC;


GRANT EXECUTE ON KFM.PKG_NSI_SYNC TO KFM_ROLE;


CREATE OR REPLACE PACKAGE BODY KFM.PKG_NSI_SYNC
AS
   /******************************************************************************
      NAME:       PKG_NSI_SYNC
      PURPOSE:    Синхронизация НСИ между подсистемами
   ******************************************************************************/

   PROCEDURE FROM_EIAS_SYNC_FM1_REVISION (insertRowCount   IN OUT INTEGER,
                                          updateRowCount   IN OUT INTEGER)
   IS
   BEGIN
      FOR changeData
         IN (WITH v
                  AS (SELECT E.*,
                             -- Если запись отсутствует то пишем 1 в колонку "вставка"
                             NVL2 (L.FM1_REVISION_GUID, 0, 1) INS,
                             -- Если запись отличается то пишем 1 в колонку "обновление"
                             CASE
                                WHEN    E.START_DATE != L.START_DATE
                                     OR E.END_DATE != L.END_DATE
                                THEN
                                   1
                                ELSE
                                   0
                             END
                                UPD
                        FROM EIAS_EXP_FM1_REVISION e
                             LEFT OUTER JOIN
                             FM1_REVISION L
                                ON e.FM1_REVISION_GUID =
                                      REPLACE (L.FM1_REVISION_GUID, '-'))
             SELECT *
               FROM v
              WHERE INS = 1 OR UPD = 1)
      LOOP
         IF (changeData.INS = 1)                                    -- Вставка
         THEN
            INSERT INTO FM1_REVISION (FM1_REVISION_GUID,
                                      FM1_VERSION,
                                      START_DATE,
                                      END_DATE)
                 VALUES (ToGuidWithDash (changeData.FM1_REVISION_GUID),
                         changeData.FM1_VERSION,
                         changeData.START_DATE,
                         changeData.END_DATE);

            insertRowCount := insertRowCount + 1;
         ELSE
            IF (changeData.UPD = 1)                              -- Обновление
            THEN
               UPDATE FM1_REVISION
                  SET START_DATE = changeData.START_DATE,
                      END_DATE = changeData.END_DATE
                WHERE REPLACE (FM1_REVISION_GUID, '-') =
                         changeData.FM1_REVISION_GUID;

               updateRowCount := updateRowCount + 1;
            END IF;
         END IF;
      END LOOP;
   END;

   PROCEDURE FROM_EIAS_SYNC_AC (insertRowCount   IN OUT INTEGER,
                                updateRowCount   IN OUT INTEGER)
   IS
   BEGIN
      FOR changeData
         IN (WITH v
                  AS (SELECT E.*,
                             -- Если запись отсутствует то пишем 1 в колонку "вставка"
                             NVL2 (L.ANALYTICTYPE, 0, 1) INS,
                             -- Если запись отличается то пишем 1 в колонку "обновление"
                             CASE
                                WHEN    E.EXTRACODE != L.EXTRACODE
                                     OR UNISTR (E.NAME) != L.NAME
                                     OR UNISTR (E.NAME_KZ) != L.NAME_KZ
                                     OR UNISTR (E.SHORTNAME) != L.SHORTNAME
                                     OR UNISTR (E.SHORTNAME_KZ) !=
                                           L.SHORTNAME_KZ
                                THEN
                                   1
                                ELSE
                                   0
                             END
                                UPD
                        FROM EIAS_EXP_ANALYTICCODES e
                             LEFT OUTER JOIN ANALYTICCODES L
                                ON     E.ANALYTICTYPE = L.ANALYTICTYPE
                                   AND E.ANALYTICCODE = L.ANALYTICCODE
                             LEFT OUTER JOIN ANALYTICTYPES at
                                ON L.ANALYTICTYPE = AT.ANALYTICTYPE
                        WHERE (AT.ANALYTICTYPE IS NULL OR AT.IS_LOCAL = 0))
             SELECT *
               FROM v
              WHERE INS = 1 OR UPD = 1)
      LOOP
         IF (changeData.INS = 1)                                    -- Вставка
         THEN
            INSERT INTO ANALYTICCODES (ANALYTICTYPE,
                                       ANALYTICCODE,
                                       EXTRACODE,
                                       PARENTCODE,
                                       NAME,
                                       NAME_KZ,
                                       SHORTNAME,
                                       SHORTNAME_KZ)
                 VALUES (changeData.ANALYTICTYPE,
                         changeData.ANALYTICCODE,
                         changeData.EXTRACODE,
                         changeData.PARENTCODE,
                         UNISTR (changeData.NAME),
                         UNISTR (changeData.NAME_KZ),
                         UNISTR (changeData.SHORTNAME),
                         UNISTR (changeData.SHORTNAME_KZ));

            insertRowCount := insertRowCount + 1;
         ELSE
            IF (changeData.UPD = 1)                              -- Обновление
            THEN
               UPDATE ANALYTICCODES
                  SET EXTRACODE = changeData.EXTRACODE,
                      NAME = UNISTR (changeData.NAME),
                      NAME_KZ = UNISTR (changeData.NAME_KZ),
                      SHORTNAME = UNISTR (changeData.SHORTNAME),
                      SHORTNAME_KZ = UNISTR (changeData.SHORTNAME_KZ)
                WHERE     ANALYTICTYPE = changeData.ANALYTICTYPE
                      AND ANALYTICCODE = changeData.ANALYTICCODE;

               updateRowCount := updateRowCount + 1;
            END IF;
         END IF;
      END LOOP;
   END;

   PROCEDURE FROM_EIAS_SYNC_AT (insertRowCount   IN OUT INTEGER,
                                updateRowCount   IN OUT INTEGER)
   IS
   BEGIN
      FOR changeData
         IN (WITH v
                  AS (    SELECT E.*,
                                 -- Если запись отсутствует то пишем 1 в колонку "вставка"
                                 NVL2 (L.ANALYTICTYPE, 0, 1) INS,
                                 -- Если запись отличается то пишем 1 в колонку "обновление"
                                 CASE
                                    WHEN    UNISTR (E.NAME) != L.NAME
                                         OR UNISTR (E.NAME_KZ) != L.NAME_KZ
                                         OR UNISTR (E.SHORTNAME) != L.SHORTNAME
                                         OR UNISTR (E.SHORTNAME_KZ) !=
                                               L.SHORTNAME_KZ
                                         OR E.START_DATE != L.START_DATE
                                    THEN
                                       1
                                    ELSE
                                       0
                                 END
                                    UPD
                            FROM EIAS_EXP_ANALYTICTYPES e
                                 LEFT OUTER JOIN ANALYTICTYPES L
                                    ON E.ANALYTICTYPE = L.ANALYTICTYPE
                            WHERE (L.ANALYTICTYPE IS NULL OR L.IS_LOCAL = 0)
                      -- Связали иерархию, иначе вставка не пройдет
                      START WITH e.CHILDTYPE IS NULL
                      CONNECT BY PRIOR e.ANALYTICTYPE = e.CHILDTYPE)
             SELECT *
               FROM v
              WHERE INS = 1 OR UPD = 1)
      LOOP
         IF (changeData.INS = 1)                                    -- Вставка
         THEN
            INSERT INTO ANALYTICTYPES (ANALYTICTYPE,
                                       DATACODE,
                                       CHILDTYPE,
                                       NAME,
                                       NAME_KZ,
                                       SHORTNAME,
                                       SHORTNAME_KZ,
                                       START_DATE,
                                       IS_LOCAL)
                 VALUES (changeData.ANALYTICTYPE,
                         changeData.DATACODE,
                         changeData.CHILDTYPE,
                         UNISTR (changeData.NAME),
                         UNISTR (changeData.NAME_KZ),
                         UNISTR (changeData.SHORTNAME),
                         UNISTR (changeData.SHORTNAME_KZ),
                         changeData.START_DATE,
                         0);

            insertRowCount := insertRowCount + 1;
         ELSE
            IF (changeData.UPD = 1)                              -- Обновление
            THEN
               UPDATE ANALYTICTYPES
                  SET NAME = UNISTR (changeData.NAME),
                      NAME_KZ = UNISTR (changeData.NAME_KZ),
                      SHORTNAME = UNISTR (changeData.SHORTNAME),
                      SHORTNAME_KZ = UNISTR (changeData.SHORTNAME_KZ),
                      START_DATE = changeData.START_DATE
                WHERE ANALYTICTYPE = changeData.ANALYTICTYPE;

               updateRowCount := updateRowCount + 1;
            END IF;
         END IF;
      END LOOP;
   END;

   PROCEDURE FROM_EIAS_SYNC_FM1_REV_AT
   IS
   BEGIN
      -- Можно делать только вставку - это табличка связей
      INSERT INTO FM1_REV_ANALYTICTYPES (FM1_REVISION_GUID, ANALYTICTYPE)
         WITH V
              AS (SELECT E.*
                    FROM EIAS_EXP_FM1_REV_ANALYTICTYPES e
                         LEFT OUTER JOIN
                         FM1_REV_ANALYTICTYPES L
                            ON     E.FM1_REVISION_GUID =
                                      REPLACE (L.FM1_REVISION_GUID, '-')
                               AND E.ANALYTICTYPE = L.ANALYTICTYPE
                   WHERE NVL2 (L.FM1_REVISION_GUID, 0, 1) = 1)
         SELECT ToGuidWithDash (FM1_REVISION_GUID), ANALYTICTYPE
           FROM v;
   END;

   PROCEDURE EIAS_2_KFM (fm1InsertRowCount   OUT INTEGER,
                         fm1UpdateRowCount   OUT INTEGER,
                         atInsertRowCount    OUT INTEGER,
                         atUpdateRowCount    OUT INTEGER,
                         acInsertRowCount    OUT INTEGER,
                         acUpdateRowCount    OUT INTEGER)
   IS
   BEGIN
      fm1InsertRowCount := 0;
      fm1UpdateRowCount := 0;
      atInsertRowCount := 0;
      atUpdateRowCount := 0;
      acInsertRowCount := 0;
      acUpdateRowCount := 0;

      FROM_EIAS_SYNC_FM1_REVISION (fm1InsertRowCount, fm1UpdateRowCount);
      FROM_EIAS_SYNC_AT (atInsertRowCount, atUpdateRowCount);
      FROM_EIAS_SYNC_FM1_REV_AT;
      FROM_EIAS_SYNC_AC (acInsertRowCount, acUpdateRowCount);
   END;
END PKG_NSI_SYNC;
/


CREATE OR REPLACE PUBLIC SYNONYM PKG_NSI_SYNC FOR KFM.PKG_NSI_SYNC;


GRANT EXECUTE ON KFM.PKG_NSI_SYNC TO KFM_ROLE;

CREATE OR REPLACE PROCEDURE KFM.LoadMessageSchemaFromFile (
   p_dirName        VARCHAR2,
   p_fileName       VARCHAR2,
   p_versionCode    VARCHAR2)
IS
   l_bfile   BFILE;
   l_blob    BLOB;
BEGIN
   SELECT MV.XML_SCHEMA
     INTO l_blob
     FROM MESSAGES_VERSIONS mv
    WHERE MV.VERSIONCODE = p_versionCode;

   l_bfile := BFILENAME (p_dirName, p_fileName);
   DBMS_LOB.OPEN (l_bfile, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.LoadFromFile (DEST_LOB   => l_blob,
                          SRC_LOB    => l_bfile,
                          AMOUNT     => DBMS_LOB.GETLENGTH (l_bfile));
   DBMS_LOB.CLOSE (l_bfile);
END LoadMessageSchemaFromFile;
/

GRANT EXECUTE ON KFM.LoadMessageSchemaFromFile TO KFM_ROLE;

CREATE OR REPLACE PROCEDURE KFM.LoadMessageXamlFromFile (
   p_dirName        VARCHAR2,
   p_fileName       VARCHAR2,
   p_isEditable     NUMBER,
   p_versionCode    VARCHAR2)
IS
   l_bfile   BFILE;
   l_blob    BLOB;
BEGIN
   NULL;
END LoadMessageXamlFromFile;
/

GRANT EXECUTE ON KFM.LoadMessageXamlFromFile TO KFM_ROLE;
