/*
* Автор: Маркелов С.В.
* Дата: 2015-02-03
* Задача: http://192.168.2.17:3000/issues/1263
* Назначение: FM1_REVISION_ID NOT NULL 
* Изменяются объекты:
		update package PKG_NSI_SYNC
*/

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

declare
	filename      varchar2(256) := 'sfm.database-update.2015-02-03.FixPKG_NSI_SYNC-NewFM1Revision.sql';
	purpose       varchar2(4000) := 'FM1_REVISION_ID NOT NULL';
	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_NSI_SYNC
AS
   /******************************************************************************
      NAME:       PKG_NSI_SYNC
      PURPOSE:    Синхронизация НСИ между подсистемами
   ******************************************************************************/

   PROCEDURE FROM_EIAS_SYNC_FM1_REVISION (insertRowCount   IN OUT INTEGER,
                                          updateRowCount   IN OUT INTEGER)
   IS
      v_rev FM1_REVISION%ROWTYPE;
   BEGIN
      FOR changeData
         IN (WITH v
                  AS (SELECT E.*,
                             -- Если запись отсутствует то пишем 1 в колонку "вставка"
                             NVL2 (L.FM1_REVISION_GUID, 0, 1) INS,
                             -- Если запись отличается то пишем 1 в колонку "обновление"
                             CASE
                                WHEN    NVL(E.START_DATE, to_date('01.01.1900 12:00:00', 'DD.MM.YYYY HH24:MI:SS')) !=
                                          NVL(L.START_DATE, to_date('01.01.1900 12:00:00', 'DD.MM.YYYY HH24:MI:SS'))
                                     OR NVL(E.END_DATE, to_date('01.01.1900 12:00:00', 'DD.MM.YYYY HH24:MI:SS')) !=
                                          NVL(L.END_DATE, to_date('01.01.1900 12:00:00', 'DD.MM.YYYY HH24:MI:SS'))
                                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
            v_rev.FM1_REVISION_GUID := ToGuidWithDash (changeData.FM1_REVISION_GUID);
            v_rev.FM1_VERSION := changeData.FM1_VERSION;
            v_rev.START_DATE := changeData.START_DATE;
            v_rev.END_DATE := changeData.END_DATE;
            v_rev.FM1_REVISION_ID := 0;
        
            INSERT INTO FM1_REVISION VALUES v_rev;
            
            insert into MESSAGES_VERSIONS(MESS_TYPE, VERSIONCODE, BEGINDATE, VERSIONID, STATE)
            values(1, v_rev.FM1_REVISION_GUID, v_rev.START_DATE, v_rev.FM1_VERSION, 1);
            
            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;

                update MESSAGES_VERSIONS
                set BEGINDATE = changeData.START_DATE,
                  ENDDATE = changeData.END_DATE
                where REPLACE (VERSIONCODE, '-') = 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    NVL(E.EXTRACODE, 'NULL') != NVL(L.EXTRACODE, 'NULL')
                                     OR NVL(E.NAME, 'NULL') != NVL(L.NAME, 'NULL')
                                     OR NVL(E.NAME_KZ, 'NULL') != NVL(L.NAME_KZ, 'NULL')
                                     OR NVL(E.SHORTNAME, 'NULL') != NVL(L.SHORTNAME, 'NULL')
                                     OR NVL(E.SHORTNAME_KZ, 'NULL') != NVL(L.SHORTNAME_KZ, 'NULL')
                                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,
                         changeData.NAME,
                         changeData.NAME_KZ,
                         changeData.SHORTNAME,
                         changeData.SHORTNAME_KZ);

            insertRowCount := insertRowCount + 1;
         ELSE
            IF (changeData.UPD = 1)                              -- Обновление
            THEN
               UPDATE ANALYTICCODES
                  SET EXTRACODE = changeData.EXTRACODE,
                      NAME = changeData.NAME,
                      NAME_KZ = changeData.NAME_KZ,
                      SHORTNAME = changeData.SHORTNAME,
                      SHORTNAME_KZ = 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    NVL(E.NAME, 'NULL') != NVL(L.NAME, 'NULL')
                                         OR NVL(E.NAME_KZ, 'NULL') != NVL(L.NAME_KZ, 'NULL')
                                         OR NVL(E.SHORTNAME, 'NULL') != NVL(L.SHORTNAME, 'NULL')
                                         OR NVL(E.SHORTNAME_KZ, 'NULL') != NVL(L.SHORTNAME_KZ, 'NULL')
                                         OR NVL(E.START_DATE, to_date('01.01.1900 12:00:00', 'DD.MM.YYYY HH24:MI:SS')) !=
                                              NVL(L.START_DATE, to_date('01.01.1900 12:00:00', 'DD.MM.YYYY HH24:MI:SS'))
                                    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,
                         changeData.NAME,
                         changeData.NAME_KZ,
                         changeData.SHORTNAME,
                         changeData.SHORTNAME_KZ,
                         changeData.START_DATE,
                         0);

            insertRowCount := insertRowCount + 1;
         ELSE
            IF (changeData.UPD = 1)                              -- Обновление
            THEN
               UPDATE ANALYTICTYPES
                  SET NAME = changeData.NAME,
                      NAME_KZ = changeData.NAME_KZ,
                      SHORTNAME = changeData.SHORTNAME,
                      SHORTNAME_KZ = 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;