DROP FUNCTION if exists sp_irppackload;
drop procedure if exists sp_irppackload;

CREATE procedure sp_irppackload
(
    "FileID" uuid default null::uuid,
    "PackageName" text default null::text,
    "FileName" text default null::text,
    "Worker" uuid default null::uuid,
    x xml default null::xml,
    inout "IsSingleXmlFlk" boolean default null::boolean,
    inout "Content" text default null::text,
    inout "Filename" text default null::text,
    inout "TargetDirectory" text default null::text,
    inout "CodePage" text default null::text,
    inout "ErrorDescrxPath" text default null::text
)

LANGUAGE plpgsql
AS $procedure$
declare
_filename varchar(50); 
"_userId" uuid;
begin
    Create Temp Table TmpZGLV
    As
    select
        "FileID" 																	 as FileId
    ,    (xpath('//VERSION[1]/text()', z))[1]::varchar(5)                             as VERSION
    ,    to_timestamp((xpath('//DATA[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')  as DATA
    ,	 (xpath('//YEAR[1]/text()', z))[1]::text::int									 as YEAR
    ,	 (xpath('//MONTH[1]/text()', z))[1]::text::int									 as MONTH
    ,    (xpath('//SMO[1]/text()', z))[1]::text::varchar(5)                          as SMO
    ,    (xpath('//FILENAME[1]/text()', z))[1]::varchar(50)                          as FILENAME

    from
    (
        select unnest(xpath('/IRP_LIST/ZGLV', x)) as z
    ) t1;
   
    Create Temp Table TmpIrpItem
    As
    select
        gen_random_uuid()    as AID
    ,    gen_random_uuid() as PersonInfoId
    ,    (xpath('//N_IRP[1]/text()', z))[1]::varchar(36)                                   as N_IRP
    ,    (xpath('//IRP_TYPE[1]/text()', z))[1]::text::int                                   as IRP_TYPE
    ,    to_timestamp((xpath('//REG_DATE[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')   as REG_DATE 
    ,    (xpath('//REG_NUM[1]/text()', z))[1]::varchar(10)                                    as REG_NUM
    ,    to_timestamp((xpath('//DATE_CREATE[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')        as DATE_CREATE
    ,    to_timestamp((xpath('//DATE_CLOSE[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')        as DATE_CLOSE
    ,    to_timestamp((xpath('//DATE_INTERVAL[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')        as DATE_INTERVAL
    ,    (xpath('//FIO[1]/text()', z))[1]::varchar(100)                                    as FIO
    ,    (xpath('//SPOLIS[1]/text()', z))[1]::varchar(10)                                   as SPOLIS
    ,    (xpath('//NPOLIS[1]/text()', z))[1]::varchar(20)                                as NPOLIS
    ,    (xpath('//ST_OKATO[1]/text()', z))[1]::varchar(5)                                as ST_OKATO
    ,    (xpath('//WAY[1]/text()', z))[1]::text::int                                     as WAY  
    ,    (xpath('//TIP_INFO[1]/text()', z))[1]::text::int                                 as TIP_INFO
    ,    null::uuid 																		as INSURED -- заглушка 
    ,	 null::uuid as IPR -- заглушка
     
    ,    (xpath('//HANDLING[1]//TEXT[1]/text()', z))[1]::varchar(1000)                                as TEXT 
    ,    (xpath('//HANDLING[1]//ACTIONS[1]/text()', z))[1]::varchar(1000)                                as ACTIONS 
    ,    (xpath('//HANDLING[1]//MO[1]/text()', z))[1]::varchar(36)                               as MO 
    ,    (xpath('//HANDLING[1]//USL_OK[1]/text()', z))[1]::text::int                              as USL_OK 
    ,    (xpath('//HANDLING[1]//REASON[1]/text()', z))[1]::text::int                                as REASON 
    
    ,    to_timestamp((xpath('//COMPLAINT[1]//DATE_VIEW[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as DATE_VIEW 
    ,    to_timestamp((xpath('//COMPLAINT[1]//DATE_RET[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')  as DATE_RET 
    ,    (xpath('//COMPLAINT[1]//SUM_RET[1]/text()', z))[1]::text::decimal(15, 2)                         as SUM_RET 
    ,    (xpath('//COMPLAINT[1]//INIT_RET[1]/text()', z))[1]::text::int                                as INIT_RET 
    ,    (xpath('//COMPLAINT[1]//ORG[1]/text()', z))[1]::text::int                                as ORG 
    ,    (xpath('//COMPLAINT[1]//JUSTIFY[1]/text()', z))[1]::text::int                               as JUSTIFY 
    ,    (xpath('//COMPLAINT[1]//STATUS[1]/text()', z))[1]::text::int                           as STATUS 
    ,    (xpath('//COMPLAINT[1]//RESULT[1]/text()', z))[1]::text::int                                as RESULT 
    
    	,    (xpath('//COMPLAINT[1]//ORG_COMPLAINT[1]//MO[1]/text()', z))[1]::varchar(6)                                as ORG_COMP_MO
    
			,    to_timestamp((xpath('//COMPLAINT[1]//ORG_COMPLAINT[1]//COMPLAINT_INFO//DATE_IN[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as    DATE_IN
			,    to_timestamp((xpath('//COMPLAINT[1]//ORG_COMPLAINT[1]//COMPLAINT_INFO//DATE_OUT[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as    DATE_OUT    
			,    (xpath('//COMPLAINT[1]//ORG_COMPLAINT[1]//COMPLAINT_INFO//INFO[1]/text()', z))[1]::varchar(1000) as    INFO    
			,    (xpath('//COMPLAINT[1]//ORG_COMPLAINT[1]//COMPLAINT_INFO//ACTIONS[1]/text()', z))[1]::varchar(1000) as    COMP_ACTIONS    
			,    (xpath('//COMPLAINT[1]//ORG_COMPLAINT[1]//COMPLAINT_INFO//USL_OK[1]/text()', z))[1]::text::int as    COMP_USL_OK    
			,    (xpath('//COMPLAINT[1]//ORG_COMPLAINT[1]//COMPLAINT_INFO//REASON[1]/text()', z))[1]::text::int as    COMP_REASON    
			
	,    (xpath('//PROPOSITION[1]//TEXT[1]/text()', z))[1]::varchar(1000)                                as PROP_TEXT 
	,    (xpath('//PROPOSITION[1]//PROP_USL_OK[1]/text()', z))[1]::text::int                                as PROP_USL_OK 
    from
    (
        select unnest(xpath('/IRP_LIST/IRP', x)) as z
    ) t1;
   
   _filename = (select FILENAME from TmpZGLV);
 
   update TmpIrpItem t1
   set insured = (
   select ins.insuredid from iszl.t_insured as ins where ins.fio = t1.fio)
   where t1.insured is null;
   
   update TmpIrpItem t1
   set insured = (
   select insured from iszl.t_insured_policy_record as ins
   join iszl.t_policy  as p on p.policyid = ins.policy
   where p.number = t1.npolis and p.series = t1.spolis)
   where t1.insured is null;

  Create Temp Table TmpErrors
As
select
    1::int as code
,   'Застрахованный не найден' as comment
from TmpIrpItem p
where p.INSURED is null;

if not exists (select * from TmpErrors)
then
	
	update TmpIrpItem ii
	set ipr = (select pol.insuredpolicyrecordid from iszl.t_insured_policy_record pol
	where pol.insured = ii.insured 
	and pol.datebegin <= (case when ii.reg_date is null then ii.date_create else ii.reg_date end)::timestamp
	and (not(pol.datestop is not null and pol.datestop <= (case when ii.reg_date is null then ii.date_create else ii.reg_date end)::timestamp)));

"_userId" = (
	select tu.userid from vccc.t_users as tu
	join iszl.t_users as tu1 on tu.userid = tu1.userid
	join iszl.t_post tp on tu1.post = tp.postid
	where worker = "Worker" limit 1);
	
	if("_userId" is null)
	then
		"_userId" = (
		select tu.userid from vclib.t_identity as ti
		join vclib.t_users as tu on tu.identity = ti.identityid
		where ti.identityname = 'iszl_service_user' limit 1);
	end if;

	insert into vccc.t_appeal_to_call_center
           ("appealtocallcenterid"
           ,number
           ,numberwriting
           ,datetimecreate
           ,datetimewriting
           ,wayorganizationid
           ,how
           ,theme
           ,text
           ,result
           ,zh_d
           ,otv_t
           ,otv_kon
           ,dataplan
           ,dataplan2
           ,dateclose
           ,way
           ,adr
           ,phone
           ,email
           ,comments
           ,in_rd
           ,deleted
           ,deletereason
           ,redirected
           ,solution
           ,initiator
           ,datereturn
           ,returningorganization
           ,returningmosmoid
           ,sumreturn
           ,organizationid
           ,resultchoice
           ,complaintorganizationid
           ,addressedtoboss
           ,matches
           ,accepteduser
           ,responsibleuser
           ,acceptedappeal
           ,pr_out
           ,date_time_cross
           ,iscovid
           ,vaccinationcovid
           ,callbackplatform
           ,appealtype
           ,type_
           ,informedbox
           ,statusmodificationdate
           ,status
           ,changeduser)
     select
           ii.aid--<appealtocallcenterid, uniqueidentifier,>
           ,ii.n_irp--<number, varchar(36),>
           ,null--<numberwriting, varchar(36),>
           ,ii.date_create--<datetimecreate, datetime,>
           ,ii.reg_date--<datetimewriting, datetime,>
           ,(select organizationid from iszl.t_organization limit 1)--<wayorganizationid, uniqueidentifier,>
           ,0--<how, int,>
           ,null--<theme, uniqueidentifier,>
           ,ii.text
           ,ii.result
           ,null--<zh_d, int,>
           ,null--<otv_t, int,>
           ,null--<otv_kon, varchar(8),>
           ,null--<dataplan, datetime,>
           ,null--<dataplan2, datetime,>
           ,ii.date_close--<dateclose, datetime,>
           ,null--<way, int,>
           ,null--<adr, varchar([1]20),>
           ,null--<phone, varchar(20),>
           ,null--<email, varchar(40),>
           ,null--<comments, varchar(max),>
           ,null--<in_rd, int,>
           ,false--<deleted, bit,>
           ,null--<deletereason, varchar(255),>
           ,null--<redirected, int,>
           ,ii.result--<solution, int,>
           ,0--<initiator, int,>
           ,ii.date_ret--<datereturn, datetime,>
           ,ii.org--<returningorganization, int,>
           ,null--<returningmosmoid, uniqueidentifier,>
           ,ii.sum_ret
           ,org.organizationid--<organizationid, uniqueidentifier,>
           ,null--<resultchoice, int,>
           ,null--<complaintorganizationid, uniqueidentifier,>
           ,false--<addressedtoboss, bit,>
           ,false--<matches, bit,>
           ,"_userId"--<accepteduser, uniqueidentifier,>
           ,"_userId"--<responsibleuser, uniqueidentifier,>
           ,null--<acceptedappeal, uniqueidentifier,>
           ,null--<pr_out, int,>
           ,null--<date_time_cross, datetime,>
           ,false--<iscovid, bit,>
           ,false--<vaccinationcovid, bit,>
           ,false--<callbackplatform, bit,>
           ,(select appealtypeid from iszl.t_appeal_type apt where apt.irptype = ii.irp_type)
           ,(select entityid from vclib.t_entities e where e.entname = 'APPEAL_TO_CALL_CENTER' and e.schema = 'ISZL')--<type_, int,>
           ,false--<informedbox, bit,>
           ,now()--<statusmodificationdate, datetime,>
           ,null--<status, int,>
           ,"_userId"--<changeduser, uniqueidentifier,>
		   from TmpIrpItem as ii
		   	right join iszl.t_insured as ins on ins.insuredid = ii.insured
			right join iszl.t_insured_policy_record as ipr on ipr.insuredpolicyrecordid = ii.ipr
			right join iszl.t_policy as pol on ipr.policy = pol.policyid
			right join iszl.t_organization org on org.organizationid = ipr.smo
			where ii.insured is not null;
		
		insert into iszl.t_appeal_to_call_center
           (appealtocallcenterid
           ,number
           ,numberwriting
           ,irptype
           ,datetimecreate
           ,datetimewriting
           ,how
           ,theme
           ,text
           ,result
           ,zh_d
           ,otv_t
           ,otv_kon
           ,dataplan
           ,dataplan2
           ,dateclose
           ,insuredpolicyrecordz
           ,insuredpolicyrecordin
           ,insuredz
           ,insuredin
           ,way
           ,z_f
           ,z_i
           ,z_o
           ,z_dr
           ,z_enp
           ,z_smo
           ,z_doctype
           ,z_docser
           ,z_docnum
           ,adr
           ,phone
           ,email
           ,in_f
           ,in_i
           ,in_o
           ,in_dr
           ,in_enp
           ,in_smo
           ,in_doctype
           ,in_docser
           ,in_docnum
           ,comments
           ,in_rd
           ,deleted
           ,deletereason
           ,redirected
           ,solution
           ,initiator
           ,datereturn
           ,returningorganization
           ,sumreturn
           ,organization
           ,resultchoice
           ,complaintorganization
           ,addressedtoboss
           ,matches
           ,wayorganization
           ,accepteduser
           ,responsibleuser
           ,acceptedappeal
           ,returningmosmo
           ,in_mo
           ,pr_out
           ,date_time_cross
           ,iscovid
           ,vaccinationcovid
           ,callbackplatform
           ,appealtype)
     select
           ii.aid
           ,ii.n_irp
           ,null
           ,ii.irp_type
           ,ii.date_create
           ,ii.reg_date
           ,ii.way
           ,null
           ,ii.text
           ,ii.result
           ,null
           ,null
           ,null
           ,null--<dataplan, datetime,>
           ,null--<dataplan2, datetime,>
           ,ii.date_close--<dateclose, datetime,>
           ,ipr.insuredpolicyrecordid --<insuredpolicyrecordz, uniqueidentifier,>
           ,null--<insuredpolicyrecordin, uniqueidentifier,>
           ,ii.insured--<insuredz, uniqueidentifier,>
           ,null--<insuredin, uniqueidentifier,>
           ,null--<way, int,> тут вообще много вопросов с этим полем
           ,ipr.lastname--<z_f, varchar(40),>
           ,ipr.firstname--<z_i, varchar(40),>
           ,ipr.patronymic--<z_o, varchar(40),>
           ,ipr.birthday--<z_dr, datetime,>
           ,ins.enp--<z_enp, varchar(16),>
           ,org.code--<z_smo, varchar(5),>
           ,pol.omstype--<doctypeid, varchar(3),>
           ,pol.series--<docser, varchar(10),>
           ,pol.number--<docnum, varchar(20),>
           ,null--<adr, varchar(120),>
           ,null--<phone, varchar(20),>
           ,null--<email, varchar(40),>
           ,null--<in_f, varchar(40),>
           ,null--<in_i, varchar(40),>
           ,null--<in_o, varchar(40),>
           ,null--<in_dr, datetime,>
           ,null--<in_enp, varchar(16),>
           ,null--<in_smo, varchar(5),>
           ,null--<in_doctype, varchar(2),>
           ,null--<in_docser, varchar(10),>
           ,null--<in_docnum, varchar(20),>
           ,null--<comments, varchar(max),>
           ,null--<in_rd, int,>
           ,false--<deleted, bit,>
           ,null--<deletereason, varchar(255),>
           ,null--<redirected, int,>
           ,ii.result--<solution, int,>
           ,0--<initiator, int,>
           ,ii.date_ret--<datereturn, datetime,>
           ,ii.org--<returningorganization, int,>
           ,ii.sum_ret--<sumreturn, decimal(15,2),>
           ,org.organizationid--<organization, uniqueidentifier,>
           ,null--<resultchoice, int,>
           ,null--<complaintorganization, uniqueidentifier,>
           ,false--<addressedtoboss, bit,>
           ,false--<matches, bit,>
           ,(select organizationid from iszl.t_organization limit 1)--<wayorganization, uniqueidentifier,>
           ,"_userId"--<accepteduser, uniqueidentifier,>
           ,"_userId"--<responsibleuser, uniqueidentifier,>
           ,null--<acceptedappeal, uniqueidentifier,>
           ,null--<returningmosmo, uniqueidentifier,>
           ,null--<in_mo, uniqueidentifier,>
           ,null--<pr_out, int,>
           ,null--<date_time_cross, datetime,>
           ,0--<iscovid, int,>
           ,0--<vaccinationcovid, int,>
           ,false--<callbackplatform, bit,>
           ,(select appealtypeid from iszl.t_appeal_type apt where apt.irptype = ii.irp_type)
		from TmpIrpItem as ii
		right join iszl.t_insured ins on ins.insuredid = ii.insured
		right join iszl.t_insured_policy_record as ipr on ipr.insuredpolicyrecordid = ii.ipr
		right join iszl.t_policy as pol on ipr.policy = pol.policyid
		right join iszl.t_organization org on org.organizationid = ipr.smo
		where ii.insured is not null;
	
	insert into vccc.t_person_info
           (personinfoid
           ,fam
           ,im
           ,ot
           ,dr
           ,enp
           ,smoid
           ,moid
           ,doctypeid
           ,docser
           ,docnum
           ,appealtocallcenter
           ,type_)
     select
           ii.personinfoid--(<personinfoid, uniqueidentifier,>
           ,ins.lastname--<fam, varchar(40),>
           ,ins.firstname--<im, varchar(40),>
           ,ins.patronymic--<ot, varchar(40),>
           ,ins.birthday--<dr, datetime,>
           ,ins.enp--<enp, varchar(16),>
           ,org.code--<smoid, varchar(5),>
           ,null--<moid, varchar(6),>
           ,pol.omstype--<doctypeid, varchar(3),>
           ,pol.series--<docser, varchar(10),>
           ,pol.number--<docnum, varchar(20),>
           ,ii.aid--<appealtocallcenter, uniqueidentifier,>
           ,(select entityid from vclib.t_entities e where e.entname = 'PERSON_INFO_Z' and e.schema = 'ISZL')--<type_, int,>
		from tmpirpitem ii
		right join iszl.t_insured ins on ii.insured = ins.insuredid
		right join iszl.t_insured_policy_record as ipr on ipr.insuredpolicyrecordid = ii.ipr
		right join iszl.t_policy as pol on ipr.policy = pol.policyid
		right join iszl.t_organization org on org.organizationid = ipr.smo
		where ii.insured is not null;

insert into iszl.t_person_info_z
           (personinfoid
           ,insuredpolicyrecord
           ,insured)
     select
           ii.personinfoid --(<personinfoid, uniqueidentifier,>
           ,ipr.insuredpolicyrecordid--<insuredpolicyrecord, uniqueidentifier,>
           ,ii.insured--<insured, uniqueidentifier,>)
		from tmpirpitem ii
		right join iszl.t_insured ins on ii.insured = ins.insuredid
		right join iszl.t_insured_policy_record as ipr on ipr.insuredpolicyrecordid = ii.ipr
		where ii.insured is not null;
	
end if;
   	create temp table temp_table(issinglexmlflk boolean,"Content" text,tempfilename text
		,targetdirectory text,codepage text,errordescrxpath text);
   
   	if (select COUNT(*) from TmpErrors) != 0
   	then
   		insert into temp_table(issinglexmlflk,"Content",tempfilename,targetdirectory,codepage,errordescrxpath)
		    select true as issinglexmlflk,
		    '<?xml version="1.0" encoding="utf-8"?>
		    ' || 
		    (
		        xmlelement
		        (
		            name "ROOT"
		        ,    (
		                select
		                xmlagg
		                (
		                    xmlelement
		                    (
		                        name "ERR"
		                    ,    xmlforest
		                        (
		                            te.CODE AS "CODE"
		                        ,    te.COMMENT AS "COMMENT"
		                        )
		                    )
		                )
		                FROM TmpErrors te
		            )
		        )
		    )::text as err,
		    ('FLK_' || _FILENAME || '.xml')::text as filename,
		    'FLK'::text as targetdirectory,
		    null::text as codepage,
		    '//COMMENT'::text as errordescrxpath;
   	end if;
    
	select issinglexmlflk from temp_table into "IsSingleXmlFlk";
	select tt."Content" from temp_table tt into "Content";
	select tempfilename from temp_table into "Filename";
	select targetdirectory from temp_table into "TargetDirectory";
	select codepage from temp_table into "CodePage";
	select errordescrxpath from temp_table into "ErrorDescrxPath";

drop table TmpIrpItem;
drop table TmpZGLV;
drop table TmpErrors;
drop table temp_table;

end;
$procedure$;