DROP FUNCTION if exists sp_BranchLoad_folder;
drop procedure if exists sp_BranchLoad_folder;

CREATE procedure sp_branchload_folder
(
    "FileID" uuid default null::uuid,
    "Worker" uuid default null::uuid,
    x xml default null::xml
,	inout "Res" boolean default null::boolean
,	inout "Err" xml default null::xml
)
LANGUAGE plpgsql
AS $procedure$
declare 
    _MO varchar(6);
    _FILENAME varchar(26);
begin
    Drop Table If Exists TmpZGLV;
    Create Temp Table TmpZGLV
    As
    select
        (xpath('//FILENAME[1]/text()', z))[1]::varchar(26)     as FILENAME
    ,    (xpath('//MO[1]/text()', z))[1]::varchar(6)    as MO
    from
    (
        select unnest(xpath('/BRANCH//HEADER', x)) as z
    ) t1;
    
    select 
        into _MO, _FILENAME
        t.MO, t.FILENAME
    from TmpZGLV t
    limit 1;
    
    Drop Table If Exists TmpZAP;
    Create Temp Table TmpZAP
    As
    select
        _FILENAME AS FILENAME
    ,    _MO AS MO        
    ,     gen_random_uuid() as ID
    ,    (xpath('//N_ZAP[1]/text()', z))[1]::text::int        as N_ZAP
    ,    (xpath('//TYPE[1]/text()', z))[1]::varchar(6)         as TYPE
    ,   (xpath('//BR_CODE[1]/text()', z))[1]::varchar(16)   as BR_CODE
    ,    (xpath('//NAME[1]/text()', z))[1]::varchar(266)     as NAME
    ,    (xpath('//ADDRESS[1]/text()', z))[1]::varchar(255)    as ADDRESS
    ,    (xpath('//PHONE[1]/text()', z))[1]::varchar(255)    as PHONE
    ,    (xpath('//RUCV[1]/text()', z))[1]::varchar(255)     as RUCV
    ,    (xpath('//ACTIVE[1]/text()', z))[1]::text::int        as ACTIVE
    ,    null::uuid                                            as BranchID
    from
    (
        select unnest(xpath('/BRANCH//BODY', x)) as z
    ) t1;
    
    update TmpZAP zap
    set BranchID = br.BranchID
    from ISZL.T_BRANCH br 
    where br.BR_CODE = zap.BR_CODE
    and exists(select* from ISZL.T_F003 lm where lm.MCOD = _MO);
    
    Drop Table If Exists TmpErrors;
    Create Temp Table TmpErrors
    As
    select 1::int as CODE, 'Обязательное поле BR_CODE. N_ZAP(' || zap.N_ZAP || ')'::varchar(250) as COMMENT
    from TmpZAP zap
    where zap.BR_CODE = '' or zap.BR_CODE is null
    union all
    select 4, 'Неверный тип операции. N_ZAP(' || zap.N_ZAP || ')'
    from TmpZAP zap
    where zap.TYPE not in ('Insert', 'Update', 'Delete');
    
    
    insert into TmpErrors (CODE,COMMENT)
    select 2, 'Подразделение было добавлено ранее. N_ZAP(' || zap.N_ZAP || ')'
    from TmpZAP zap
    where zap.BranchID is not null and zap.TYPE='Insert';
    
    -- вставка, если нет ошибок
    INSERT INTO ISZL.T_BRANCH(BranchID, BR_CODE, NAME, PHONE, ADDRESS, RUCV, ACTIVE, F003Mo)
        select zap.ID, zap.BR_CODE, zap.NAME, zap.PHONE, zap.ADDRESS, zap.RUCV
        , case when zap.ACTIVE = 0 then false else true end, _MO
        from TmpZAP zap
    where not exists(select* from TmpErrors) and zap.TYPE='Insert';
    
    --Update
    UPDATE ISZL.T_BRANCH t
       SET NAME = zap.Name
          ,PHONE = zap.PHONE
          ,ADDRESS = zap.ADDRESS
          ,RUCV = zap.RUCV
          ,ACTIVE = (case when zap.ACTIVE = 0 then '0'::boolean else '1'::boolean end)
          ,F003Mo = zap.MO
    from TmpZAP zap
    where t.BranchID = zap.BranchID
    and not exists(select* from TmpErrors) and t.BranchID = zap.BranchID and zap.TYPE = 'Update';

    if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_branch' and c.column_name = 'filename')
    then
        UPDATE ISZL.T_BRANCH t
           SET FILENAME = _FILENAME
        from TmpZAP zap
        where t.BranchID = zap.BranchID
        and not exists(select* from TmpErrors) and zap.TYPE = 'Update';
    end if;    
    
    -- вставка, если не найдена
    INSERT INTO ISZL.T_BRANCH(BranchID, BR_CODE, NAME, PHONE, ADDRESS, RUCV, ACTIVE, F003Mo)
            select zap.ID, zap.BR_CODE, zap.NAME, zap.PHONE, zap.ADDRESS, zap.RUCV
            , case when zap.ACTIVE = 0 then '0'::boolean else '1'::boolean end, _MO
            from TmpZAP zap
    where not exists(select* from TmpErrors) and zap.BranchID is null and zap.TYPE = 'Update';

    
    insert into TmpErrors
    select 3, 'Запись не найдена в ранее загруженных списках. N_ZAP(' || zap.N_ZAP || ')'
    from TmpZAP zap
    where zap.BranchID is null and zap.TYPE='Delete';

    delete from ISZL.T_BRANCH t
    where exists(select* from TmpZAP zap where t.BranchID = zap.BranchID and zap.TYPE = 'Delete')
        and not exists(select* from TmpErrors);
        
    if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_branch' and c.column_name = 'filename')
    then
        UPDATE ISZL.T_BRANCH t
           SET FILENAME = _FILENAME
        from TmpZAP zap
        where t.BranchID = zap.ID
        and not exists(select* from TmpErrors) and (zap.TYPE = 'Insert' or  (zap.BranchID is null and zap.TYPE = 'Update'));
    end if;    

   	create temp table temp_table(Res boolean,Err text);
   	insert into temp_table(Res,Err)
	    select case when exists(select* from TmpErrors) then '0'::boolean else '1'::boolean end
	    , case when exists(select* from TmpErrors) then '<?xml version="1.0" encoding="Windows-1251"?>
	    ' || 
	    (
	        xmlelement
	        (
	            name "ROOT"
	        ,    (
	                select
	                xmlagg
	                (
	                    xmlelement
	                    (
	                        name "ERR"
	                    ,    xmlforest
	                        (
	                            te.CODE AS "CODE"
	                        ,    te.COMMENT AS "COMMENT"
	                        )
	                    )
	                )
	                FROM TmpErrors te
	            )
	        )
	    )::text end;

   	select Res from temp_table into "Res";
	select Err from temp_table into "Err";

	drop table if exists temp_table;
    DROP TABLE IF EXISTS TmpZGLV;
    DROP TABLE IF EXISTS TmpZAP;
    DROP TABLE IF EXISTS TmpErrors;

end;
$procedure$;
