DROP FUNCTION if exists sp_irppackload_magadan;
drop procedure if exists sp_irppackload_magadan;

create procedure sp_irppackload_magadan(
    "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,
	inout "Lexem" text default null::text
) language plpgsql as $procedure$
declare
    "_userId" uuid;

begin
    create temp table tmpzglv
    as
    select
	"FileID" as FileId,
	z.version,
	z.data,
	z.year,
	z.month,
	z.day,
	z.time,
	z.smo,
	z.filename
from
	xmltable('/IRP_LIST/ZGLV'
	passing x
	columns
		version varchar(5) path 'VERSION',
		data timestamp path 'DATA',
		year int path 'YEAR',
		month int path 'MONTH',
		day int path 'DAY',
		time int path 'TIME',
		smo varchar(5) path 'SMO',
		filename varchar(50) path 'FILENAME'
	) as z;

create temp table tmpirpitem
    as
    select
	gen_random_uuid() as personinfoidz,
	gen_random_uuid() as personinfoidin,
	ii.itemid,
	ii.n_irp,
	ii.date_create,
	ii.time_create,
	ii.irp_type,
	ii.how,
	ii.d_wr,
	cast(coalesce(ii.add_b, 0) as bool) as add_b,
	ii.way,
	ii.way_n,
	ii.theme,
	ii.c_org,
	cast(coalesce(ii.covid, 0) as bool) as covid,
	cast(coalesce(ii.covid_v, 0) as bool) as covid_v,
	ii."text",
	ii.pr_out,
	ii.date_cross,
	ii.time_cross,
	ii.otv_t,
	ii.otv_kon,
	ii.line_1,
	ii.employee_1,
	ii.line_2,
	ii.employee_it,
	ii.data_plan,
	ii.date_close,
	ii.result,
	ii.result_t,
	ii.status,
	ii.z_f,
	ii.z_i,
	ii.z_o,
	ii.z_dr,
	ii.z_enp,
	ii.z_smo,
	ii.z_doctype,
	ii.z_docser,
	ii.z_docnum,
	ii.adr,
	ii.phone,
	ii.email,
	ii.in_f,
	ii.in_i,
	ii.in_o,
	ii.in_dr,
	ii.in_enp,
	ii.in_smo,
	ii.in_doctype,
	ii.in_docser,
	ii.in_docnum,
	ii.zh_d,
	ii.zh_in,
	coalesce(ii.sol, 0) as sol,
	ii.date_return,
	ii.sum_return,
	ii.org_return
from
	xmltable('/IRP_LIST/IRP'
	passing x
	columns
		itemid varchar(36) path 'TF_ID',
		n_irp varchar(36) path 'N_IRP',
		date_create timestamp path 'DATE_CREATE',
		time_create varchar(5) path 'TIME_CREATE',
		irp_type int path 'IRP_TYPE',
		how int path 'HOW',
		d_wr timestamp path 'D_WR',
		add_b int path 'ADD_B',
		way int path 'WAY',
		way_n varchar(6) path 'WAY_N',
		theme varchar(7) path 'THEME',
		c_org varchar(6) path 'C_ORG',
		covid int path 'COVID',
		covid_v int path 'COVID_V',
		"text" varchar(1000) path 'TEXT',
		pr_out int path 'PR_OUT',
		date_cross timestamp path 'DATE_CROSS',
		time_cross varchar(5) path 'TIME_CROSS',
		otv_t int path 'OTV_T',
		otv_kon varchar(8) path 'OTV_KON',
		line_1 int path 'LINE_1',
		employee_1 varchar(36) path 'EMPLOYEE_1',
		line_2 int path 'LINE_2',
		employee_it varchar(36) path 'EMPLOYEE_IT',
		data_plan timestamp path 'DATA_PLAN',
		date_close timestamp path 'DATE_CLOSE',
		result int path 'RESULT',
		result_t varchar(4000) path 'RESULT_T',
		status int path 'STATUS',
		z_f varchar(40) path 'Z_SV/Z_F',
		z_i varchar(40) path 'Z_SV/Z_I',
		z_o varchar(40) path 'Z_SV/Z_O',
		z_dr timestamp path 'Z_SV/Z_DR',
		z_enp varchar(16) path 'Z_SV/Z_ENP',
		z_smo varchar(5) path 'Z_SV/Z_SMO',
		z_doctype varchar(2) path 'Z_SV/Z_DOCTYPE',
		z_docser varchar(10) path 'Z_SV/Z_DOCSER',
		z_docnum varchar(20) path 'Z_SV/Z_DOCNUM',
		adr varchar(120) path 'Z_SV/ADR',
		phone varchar(20) path 'Z_SV/PHONE',
		email varchar(40) path 'Z_SV/E-MAIL',
		in_f varchar(40) path 'IN_SV/IN_F',
		in_i varchar(40) path 'IN_SV/IN_I',
		in_o varchar(40) path 'IN_SV/IN_O',
		in_dr timestamp path 'IN_SV/IN_DR',
		in_enp varchar(16) path 'IN_SV/IN_ENP',
		in_smo varchar(5) path 'IN_SV/IN_SMO',
		in_doctype varchar(2) path 'IN_SV/IN_DOCTYPE',
		in_docser varchar(10) path 'IN_SV/IN_DOCSER',
		in_docnum varchar(20) path 'IN_SV/IN_DOCNUM',
		zh_d varchar(3) path 'ZH/ZH_D',
		zh_in int path 'ZH/ZH_IN',
		sol int path 'ZH/SOL',
		date_return timestamp path 'ZH/DATE_RETURN',
		sum_return varchar(15) path 'ZH/SUM_RETURN',
		org_return varchar(6) path 'ZH/ORG_RETURN'
	) as ii;

create temp table tmp_errors (
        im_pol varchar(10),
        bas_el varchar(10),
        oshib int,
        n_zap varchar(36),
        comment varchar(250)
    ) on commit drop;
-- errors: duplicates
insert
	into
	tmp_errors(im_pol, bas_el, comment, n_zap)
select
	'N_IRP',
	'NRP',
	'Обнаружены дубли записей по полю N_IRP!',
	n_irp
from
	tmpirpitem
group by
	n_irp
having
	count(*) > 1;

-- checks for nulls (way/way_n, c_org, d_wr)
    insert
	into
	tmp_errors(im_pol, bas_el, comment, n_zap)
    select
	'WAY_N',
	'NRP',
	'Не заполнена организация поступления для обращения!',
	n_irp
from
	tmpirpitem s
where
	s.way in (3, 4, 5)
	and (s.way_n is null
		or trim(s.way_n) = '');

insert
	into
	tmp_errors(im_pol, bas_el, comment, n_zap)
    select
	'C_ORG',
	'NRP',
	'Не заполнена объект обращения(организации) для жалобы!',
	n_irp
from
	tmpirpitem s
where
	s.irp_type = 2
	and (s.c_org is null
		or trim(s.c_org) = '');

insert
	into
	tmp_errors(im_pol, bas_el, comment, n_zap)
    select
	'D_WR',
	'NRP',
	'Не заполнена дата письменного обращения!',
	n_irp
from
	tmpirpitem s
where
	s.how in (2, 3, 4)
	and s.d_wr is null;

-- If no errors, perform inserts into target tables (logic preserved)
    if (
select
	count(*)
from
	tmp_errors) = 0 then
"_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 (first batch)
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,
	addressedtoboss,
	matches,
	accepteduser,
	responsibleuser,
	acceptedappeal,
	pr_out,
	date_time_cross,
	iscovid,
	vaccinationcovid,
	callbackplatform,
	appealtype,
	type_,
	informedbox,
	statusmodificationdate,
	status,
	changeduser,
	socialcategory)
select
	ii.ItemID::uuid,					--appealtocallcenterid,
	ii.n_irp,							--"number",
	null,								--numberwriting,
	ii.date_create,						--datetimecreate,
	ii.d_wr,							--datetimewriting,
	way_org.organizationid,				--wayorganizationid,
	ii.how,								--how,
	ath.appealthemeid,					--theme,
	ii."text",							--"text",
	ii.result_t,						--result,
	ii.zh_d,							--zh_d,
	ii.otv_t,							--otv_t,
	ii.otv_kon,							--otv_kon,
	ii.data_plan,						--dataplan,
	null,								--dataplan2,
	ii.date_close,						--dateclose,
	apw.appealwayid, --ii.way,			--way,
	ii.adr,								--adr,
	ii.phone,							--phone,
	ii.email,							--email,
	null,								--comments,
	null,								--in_rd,
	false,								--deleted,
	null,								--deletereason,
	null,								--redirected,
	ii.sol,								--solution,
	ii.zh_in,							--initiator,
	ii.date_return,						--datereturn,
	null,								--returningorganization,
	ret_org.organizationid,				--returningmosmoid,
	ii.sum_return::numeric(15, 2),		--sumreturn,
	null,								--organizationid,
	ii.result,							--resultchoice,
	ii.ADD_B,							--addressedtoboss,
	false,								--matches,
	null,								--accepteduser,
	null,								--responsibleuser,
	null,								--acceptedappeal,
	ii.pr_out,							--pr_out,
	ii.date_cross,						--date_time_cross,
	ii.covid,							--iscovid,
	ii.covid_v,							--vaccinationcovid,
	false,								--callbackplatform,
	apt.appealtypeid,					--appealtype,
	e.entityid,							--type_,
	false,								--informedbox,
	now(),								--statusmodificationdate,
	ii.status,							--status,
	"_userId",							--changeduser,
	null								--socialcategory
from
	tmpirpitem ii
left join iszl.t_organization way_org on
	way_org.code = ii.way_n
left join iszl.t_organization ret_org on
	ret_org.code = ii.org_return
left join iszl.t_appeal_type apt on
	apt.irptype = ii.irp_type
join vclib.t_entities e on
	e.entname = 'APPEAL_TO_CALL_CENTER'
	and e.schema = 'ISZL'
left join vccc.t_appeal_theme ath on ath.code = ii.theme
left join vccc.t_appeal_way apw on apw.code = ii.way;
-- Subsequent inserts preserving original logic
insert
	into
	ISZL.T_APPEAL_TO_CALL_CENTER
select
	ii.itemid::uuid,
	ii.n_irp,
	null,
	ii.irp_type,
	ii.date_create,
	ii.d_wr,
	ii.how,
	ath.appealthemeid,
	ii."text",
	ii.result_t,
	ii.zh_d,
	ii.otv_t,
	ii.otv_kon,
	ii.data_plan,
	null,
	ii.date_close,
	null,
	null,
	null,
	null,
	apw.appealwayid,
	ii.z_f,
	ii.z_i,
	ii.z_o,
	ii.z_dr,
	ii.z_enp,
	ii.z_smo,
	ii.z_doctype,
	ii.z_docser,
	ii.z_docnum,
	ii.adr,
	ii.phone,
	ii.email,
	ii.in_f,
	ii.in_i,
	ii.in_o,
	ii.in_dr,
	ii.in_enp,
	ii.in_smo,
	ii.in_doctype,
	ii.in_docser,
	ii.in_docnum,
	null,
	null,
	false,
	null,
	null,
	ii.sol,
	ii.zh_in,
	ii.date_return,
	null,
	ii.sum_return::numeric(15, 2),
	ii.result,
	ii.add_b,
	false,
	null,
	null,
	null,
	null,
	ii.pr_out,
	ii.date_cross,
	ii.covid::int,
	ii.covid_v::int,
	false, 
	apt.appealtypeid
from
	tmpirpitem ii
left join iszl.t_appeal_theme ath on ath.code = ii.theme
left join iszl.t_appeal_way apw on apw.code = ii.way
left join iszl.t_organization ret_org on ret_org.code = ii.org_return
left join iszl.t_appeal_type apt on apt.irptype = ii.irp_type;
-- Insert person info Z / IN and corresponding link tables
        insert
	into
	vccc.t_person_info(
	personinfoid,
	fam,
	im,
	ot,
	dr,
	enp,
	smoid,
	moid,
	doctypeid,
	docser,
	docnum,
	appealtocallcenter,
	type_)
select
	ii.personinfoidz,
	ii.z_f,
	ii.z_i,
	ii.z_o,
	ii.z_dr,
	ii.z_enp,
	ii.z_smo,
	null,
	ii.z_doctype,
	ii.z_docser,
	ii.z_docnum,
	ii.itemid::uuid,
	(
	select
		entityid
	from
		vclib.t_entities e
	where
		e.entname = 'PERSON_INFO_Z'
		and e.schema = 'ISZL'
	limit 1)
from
	tmpirpitem ii;

insert
	into
	iszl.t_person_info_z(personinfoid)
        select
	personinfoidz
from
	tmpirpitem;

insert
	into
	vccc.t_person_info(
	personinfoid,
	fam,
	im,
	ot,
	dr,
	enp,
	smoid,
	moid,
	doctypeid,
	docser,
	docnum,
	appealtocallcenter,
	type_)
select
	ii.personinfoidin,
	ii.in_f,
	ii.in_i,
	ii.in_o,
	ii.in_dr,
	ii.in_enp,
	ii.in_smo,
	null,
	ii.in_doctype,
	ii.in_docser,
	ii.in_docnum,
	ii.itemid::uuid,
	(
	select
		entityid
	from
		vclib.t_entities e
	where
		e.entname = 'PERSON_INFO_IN'
		and e.schema = 'ISZL'
	limit 1)
from
	tmpirpitem ii;

insert
	into
	iszl.t_person_info_in(personinfoid)
        select
	personinfoidin
from
	tmpirpitem;
-- complaints
		insert
	into
	vccc.t_appeal_complaints(
			appealcomplaintsid,
	appealtocallcenter,
	complaintorganizationid,
	type_)
		select
	gen_random_uuid(),
	ii.itemid::uuid,
	org.organizationid,
	(
	select
		entityid
	from
		vclib.t_entities e
	where
		e.entname = 'APPEAL_COMPLAINTS'
		and e.schema = 'ISZL'
	limit 1)
from
	tmpirpitem ii
join iszl.t_organization org on
	org.code = ii.c_org;
end if;

	create temp table temp_table(Res boolean,Err text, Lexem text);
	IF (SELECT count(*) FROM tmp_errors) != 0 THEN
		insert into temp_table(Res,Err, Lexem)
			select case when exists(select* from tmp_errors) then false else true end,
			case when exists(select* from tmp_errors) then '<?xml version="1.0" encoding="Windows-1251"?>
			' || 
			(
				xmlelement
				(
					name "ROOT",
						(
						select
						xmlagg
						(
							xmlelement
							(
								name "ERR",
								xmlforest
								(
									te.oshib AS "OSHIB",
									te.comment AS "COMMENT",
									te.n_zap AS "N_ZAP",
									te.im_pol AS "IM_POL",
									te.bas_el AS "BAS_EL"
								)
							)
						)
						FROM tmp_errors te
					)
				)
			)::xml end
			, null::text as lexem;

	ELSE
		insert into temp_table(Res,Err, Lexem)
			select true, null, 'new Process("ProvideInsuredProcess", new Apartment().GetInstance("ISZL.PERSON_INFO_IN", Cast("' || r.personinfoidin::text || '", "Guid"))).$Run()'
			from tmpirpitem r
			where not exists(select* from tmp_errors)
			union all
			select true, null, 'new Process("ProvideInsuredProcess", new Apartment().GetInstance("ISZL.PERSON_INFO_Z", Cast("' || r.personinfoidz::text || '", "Guid"))).$Run()'
			from tmpirpitem r
			where not exists(select* from tmp_errors);
	end if;

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

	drop table tmpirpitem;
	drop table tmpzglv;
	drop table tmp_errors;
	drop table temp_table;
end;

$procedure$;

-- End of translation
