
BEGIN TRANSACTION;
do $$
/********************************************************************************************************/
/*	Загрузка fias_AddressObjects: _20180531	 						*/
/********************************************************************************************************/

DECLARE
	v_Return_Error		Integer;		/* Код ошибки             */
	
BEGIN

ALTER TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS  fk_fias_AddressObjects_AddressObjects_PREVID;
ALTER TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS  fk_fias_AddressObjects_AddressObjects_NEXTID;
DROP   TABLE IF EXISTS fias_AddressObjects_temp;
DROP   TABLE IF EXISTS fias_DeletedAddressObjects_temp;

CREATE   TABLE fias_AddressObjects_temp AS 
  SELECT * FROM fias_AddressObjects WHERE AOID IS NULL;
CREATE   TABLE fias_DeletedAddressObjects_temp AS 
  SELECT * FROM fias_AddressObjects WHERE AOID IS NULL;
-- Загрузка во временную таблицу fias_AddressObjects_temp изменений в основном списке адресообразующих элементов ФИАС 

COPY  fias_AddressObjects_temp (ACTSTATUS,AOGUID,AOID,AOLEVEL,AREACODE,AUTOCODE,CENTSTATUS,CITYCODE,CODE,CURRSTATUS,ENDDATE,FORMALNAME,IFNSFL,IFNSUL,NEXTID,OFFNAME,OKATO,OKTMO,OPERSTATUS,
				PARENTGUID,PLACECODE,PLAINCODE,POSTALCODE,PREVID,REGIONCODE,SHORTNAME,STARTDATE,STREETCODE,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,CTARCODE,EXTRCODE,SEXTCODE,
				LIVESTATUS,NORMDOC,PLANCODE,CADNUM,DIVTYPE) 
FROM 'W:\Projects\Enisey GIS\Temp\FIAS_in_PostgreSQL\fias_csv\ADDROBJ24_20180531.csv'
WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8');
-- Загрузка во временную таблицу fias_DeletedAddressObjects_Temp
-- записей, которые должны быть удалены из основнго списка */

COPY  fias_DeletedAddressObjects_Temp
    (ACTSTATUS,AOGUID,AOID,AOLEVEL,AREACODE,AUTOCODE,CENTSTATUS,CITYCODE,CODE,CURRSTATUS,ENDDATE,FORMALNAME,IFNSFL,IFNSUL,
    NEXTID,OFFNAME,OKATO,OKTMO,OPERSTATUS,PARENTGUID,PLACECODE,PLAINCODE,POSTALCODE,PREVID,REGIONCODE,SHORTNAME,STARTDATE,
    STREETCODE,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,CTARCODE,EXTRCODE,SEXTCODE,LIVESTATUS,NORMDOC,PLANCODE,CADNUM,DIVTYPE) 
FROM 'W:\Projects\Enisey GIS\Temp\FIAS_in_PostgreSQL\fias_csv\DADDROBJ24_20180531.csv'
WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8');

-- Отключение ограничений CONSTRAINT.                            

ALTER   TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS  fk_fias_AddressObjects_AddressObjects_PREVID;
ALTER   TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS  fk_fias_AddressObjects_AddressObjects_NEXTID;

-- Обновление существующих записей основного списка fias_DeletedAddressObjects записей, данными обновления 
-- из временной таблицы fias_DeletedAddressObjects_Temp                            

UPDATE fias_AddressObjects ao SET ACTSTATUS=t.ACTSTATUS,
            AOGUID=t.AOGUID,
            AOLEVEL=t.AOLEVEL,
            AREACODE=t.AREACODE,
            AUTOCODE=t.AUTOCODE,
            CENTSTATUS=t.CENTSTATUS,
            CITYCODE=t.CITYCODE,
            CODE=t.CODE,
            CURRSTATUS=t.CURRSTATUS,
            ENDDATE=t.ENDDATE,
            FORMALNAME=t.FORMALNAME,
            IFNSFL=t.IFNSFL,
            IFNSUL=t.IFNSUL,
            NEXTID=t.NEXTID,
            OFFNAME=t.OFFNAME,
            OKATO=t.OKATO,
            OKTMO=t.OKTMO,
            OPERSTATUS=t.OPERSTATUS,
            PARENTGUID=t.PARENTGUID,
            PLACECODE=t.PLACECODE,
            PLAINCODE=t.PLAINCODE,
            POSTALCODE=t.POSTALCODE,
            PREVID=t.PREVID,
            REGIONCODE=t.REGIONCODE,
            SHORTNAME=t.SHORTNAME,
            STARTDATE=t.STARTDATE,
            STREETCODE=t.STREETCODE,
            TERRIFNSFL=t.TERRIFNSFL,
            TERRIFNSUL=t.TERRIFNSUL,
            UPDATEDATE=t.UPDATEDATE,
            CTARCODE=t.CTARCODE,
            EXTRCODE=t.EXTRCODE,
            SEXTCODE=t.SEXTCODE,
            LIVESTATUS=t.LIVESTATUS,
            NORMDOC=t.NORMDOC
      FROM fias_AddressObjects dao
         INNER JOIN fias_AddressObjects_temp t ON dao.AOID=t.AOID
   
      WHERE ao.AOID=dao.AOID;   

-- Удаление существующих записей основного списка fias_DeletedAddressObjects записей, на основании данных из временной таблицы fias_DeletedAddressObjects_Temp                           

DELETE FROM fias_AddressObjects ao WHERE EXISTS(SELECT 1 FROM fias_DeletedAddressObjects_Temp delao WHERE delao.AOID=ao.AOID);

-- Добавление вновь поступивших записей основного списка fias_DeletedAddressObjects записей, данными  из временной таблицы fias_DeletedAddressObjects_Temp   
-- Условие CODE LIKE '24%' означает, что выбираются только записи, относящиеся Касноярскому краю         */

INSERT INTO fias_AddressObjects
     (ACTSTATUS,AOGUID,AOID,AOLEVEL,AREACODE,AUTOCODE,CENTSTATUS,CITYCODE,CODE,CURRSTATUS,ENDDATE,FORMALNAME,IFNSFL,IFNSUL,
   NEXTID,OFFNAME,OKATO,OKTMO,OPERSTATUS,PARENTGUID,PLACECODE,PLAINCODE, POSTALCODE,PREVID,REGIONCODE,SHORTNAME,STARTDATE,
   STREETCODE,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,CTARCODE,EXTRCODE,SEXTCODE,LIVESTATUS,NORMDOC) 
SELECT    ACTSTATUS,AOGUID,AOID,AOLEVEL,AREACODE,AUTOCODE,CENTSTATUS,CITYCODE,CODE,CURRSTATUS,ENDDATE,FORMALNAME,IFNSFL,IFNSUL,
      NEXTID,OFFNAME,OKATO,OKTMO,OPERSTATUS,PARENTGUID,PLACECODE,PLAINCODE,POSTALCODE,PREVID,REGIONCODE,SHORTNAME,
      STARTDATE, STREETCODE,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,CTARCODE,EXTRCODE,SEXTCODE,LIVESTATUS,NORMDOC 
   FROM fias_AddressObjects_temp t
   WHERE CODE LIKE '24%' AND NOT EXISTS(SELECT * FROM fias_AddressObjects ao WHERE ao.AOID=t.AOID)
   ORDER BY CODE;

-- Исправление нарушений целостности fias_AddressObjects.                   -- Непустые ссылки на предыдущую и последующую записи заменяются значением NULL

UPDATE fias_AddressObjects ao SET NEXTID=NULL   
      WHERE ao.NEXTID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects nao  WHERE nao.AOID=ao.NEXTID);   
UPDATE fias_AddressObjects ao SET PREVID=NULL   
      WHERE ao.PREVID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects pao  WHERE pao.AOID=ao.PREVID);   

-- Восстановление ограничений CONSTRAINT.                            

ALTER   TABLE fias_AddressObjects ADD  CONSTRAINT fk_fias_AddressObjects_AddressObjects_PREVID FOREIGN KEY(PREVID)
                        REFERENCES fias_AddressObjects (AOID);
ALTER   TABLE fias_AddressObjects ADD  CONSTRAINT fk_fias_AddressObjects_AddressObjects_NEXTID FOREIGN KEY(NEXTID)
                        REFERENCES fias_AddressObjects (AOID);

-- Удаление временных таблиц из базы данных.                         

DROP   TABLE IF EXISTS fias_AddressObjects_temp;
DROP   TABLE IF EXISTS fias_DeletedAddressObjects_temp;
END;
$$LANGUAGE plpgsql;
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT (SELECT COUNT(*) FROM fias_AddressObjects) AS AOCount;



