DROP FUNCTION if exists sp_loadcancregister(uuid,uuid,character varying,timestamp without time zone);

create or replace function sp_LoadCancregister
(
"UserID" uuid default null::uuid,
"IntegrationMessage" uuid default null::uuid,
"FileName" varchar(255) default null::varchar(255),
"CurrentDateTime" timestamp without time zone default null::timestamp without time zone
) returns table (code integer, xml text, lexem text) as 
$BODY$
begin
create temp table onk_cancregister as
select
gen_random_uuid() as id,
oc."Column0"::varchar(255) as fullname,
oc."Column1"::timestamp as BD,
(select s.idpol from iszl.t_v005_sex s where lower(left(s.polname, 1)) = lower(oc."Column2") limit 1) as sex,
oc."Column3"::timestamp as ATD,
oc."Column4"::integer as ATC,
oc."Column5"::varchar(255) as DC,
oc."Column6"::timestamp as DD,
oc."Column7"::varchar(255) as DS,
oc."Column8"::timestamp as ARD,
oc."Column9"::integer as ARC,
oc."Column10"::integer as RN,
oc."Column11"::varchar(255) as RC,
oc."Column12"::varchar(255) as addr
from idb."ONK_CANCREGISTER_0" oc;

alter table onk_cancregister
add column int_id serial;

insert into iszl.t_onk_cancregister
               (
               onkcancregisterid,
               fullname,
			   im,
			   fam,
			   ot,
               birthdate,
               sex,
               regnumber,
               regioncode,
               address
               )
        select
               oc.id,
               oc.fullname,
			   split_part(oc.fullname, ' ', 1),
			   split_part(oc.fullname, ' ', 2),
			   case when split_part(oc.fullname, ' ', 3) = '' then null
				else split_part(oc.fullname, ' ', 3)
				end,
               oc.BD,
               oc.sex,
               oc.RN,
               oc.RC,
               oc.addr
        from onk_cancregister oc
        where oc.fullname is not null;

insert into iszl.t_onk_cancregister_accounting_take
               (
               OnkCancregisterAccountingTakeID,
               onkcancregister,
               date,
               code
               )
        select gen_random_uuid(), person1.id, take.ATD, take.ATC
        from onk_cancregister take
		inner join lateral(
			select person.id, person.ATD, person.ATC
			from onk_cancregister person
			where person.int_id < take.int_id and person.fullname is not null
			order by person.int_id desc
			limit 1
		) person1
		on true
		where take.fullname is null and (take.ATD is not null or take.ATC is not null)
		union
		select gen_random_uuid(), oc.id, oc.ATD, oc.ATC
		from onk_cancregister oc
		where oc.fullname is not null and (oc.ATD is not null or oc.ATC is not null);


insert into iszl.t_onk_cancregister_accounting_removal
               (
               onkcancregisteraccountingremovalid,
               onkcancregister,
               date,
               code
               )
        select gen_random_uuid(), person.id, remove.ARD, remove.ARC
        from onk_cancregister remove
		inner join lateral(
			select person.id, person.ARD, person.ARC
			from onk_cancregister person
			where person.int_id < remove.int_id and person.fullname is not null
			order by person.int_id desc
			limit 1
		) person
		on true
		where remove.fullname is null and (remove.ARD is not null or remove.ARC is not null)
		union
		select gen_random_uuid(), oc.id, oc.ARD, oc.ARC
		from onk_cancregister oc
		where oc.fullname is not null and (oc.ARD is not null or oc.ARC is not null);

insert into iszl.t_onk_cancregister_diagnosis
               (
               onkcancregisterdiagnosisid,
               onkcancregister,
               date,
               code,
			   stage
               )
        select gen_random_uuid(), person.id, diagnosis.DD, diagnosis.DC, diagnosis.DS
        from onk_cancregister diagnosis
		inner join lateral(
			select person.id, person.DD, person.DC, person.DS
			from onk_cancregister person
			where person.int_id < diagnosis.int_id and person.fullname is not null
			order by person.int_id desc
			limit 1
		) person
		on true
		where diagnosis.fullname is null and (diagnosis.DD is not null or diagnosis.DC is not null or diagnosis.DS is not null)
		union
		select gen_random_uuid(), oc.id, oc.DD, oc.DC, oc.DS
		from onk_cancregister oc
		where oc.fullname is not null and (oc.DD is not null or oc.DC is not null or oc.DS is not null);

    return query
	select cast(null as int), cast(null as text), cast(null as text) lexem
	union
	select null, null, 'new Process("ProvideInsuredProcess", new Apartment().GetInstance("ISZL.ONK_CANCREGISTER", Cast("' || oc.id::text || '", "Guid"))).$Run()'
    from onk_cancregister oc
    where oc.fullname is not null;

drop table onk_cancregister;
end;  $BODY$
language plpgsql VOLATILE
  COST 100;
alter function sp_LoadCancregister(uuid, uuid, varchar(255), timestamp without time zone) OWNER TO postgres;