if exists ( select * from sys.procedures p where p.name = 'sp_LoadCancregister' ) DROP PROCEDURE [dbo].[sp_LoadCancregister]
GO

CREATE PROCEDURE [dbo].[sp_LoadCancregister]
  @CurrentDateTime datetime = null,
  @UserID uniqueidentifier = null,
  @IntegrationMessage uniqueidentifier = null,
  @FileName varchar(max) = null
AS 
BEGIN

select
    NEWID() ID,
    oc.Column0 FullName,
    convert(datetime, oc.Column1, 104) BD,
    (select top 1 s.IDPOL from ISZL.T_V005_SEX s where left(s.POLNAME, 1) = left(oc.Column2, 1)) Sex,
    convert(datetime, oc.Column3, 4) ATD,
    oc.Column4 ATC,
    oc.Column5 DC,
    convert(datetime, oc.Column6, 4) DD,
    oc.Column7 DS,
    convert(datetime, oc.Column8, 4) ARD,
    oc.Column9 ARC,
    oc.Column10 RN,
    oc.Column11 RC,
    oc.Column12 Addr
        into #onk_cancregister
    from idb.ONK_CANCREGISTER_0 oc;

    alter table #onk_cancregister
    add int_id int identity;

	update #onk_cancregister
	set FullName = REPLACE(FullName, ' ', SPACE(1));

    insert into [ISZL].[T_ONK_CANCREGISTER]
               (
               [OnkCancregisterId],
			   [Fam],
			   [Im],
			   [Ot],
               [FullName],
               [BirthDate],
               [Sex],
               [RegNumber],
               [RegionCode],
               [Address]
               )
        select
               oc.ID,
			   SUBSTRING(oc.FullName, 1, CHARINDEX(SPACE(1), oc.FullName, 1)-1),
			   case CHARINDEX(SPACE(1), oc.FullName, CHARINDEX(SPACE(1), oc.FullName, 1)+1)
					when 0
					then 
						SUBSTRING(oc.FullName,
						CHARINDEX(SPACE(1), oc.FullName, 1)+1,
						LEN(oc.FullName))
					else
						SUBSTRING(oc.FullName,
						CHARINDEX(SPACE(1), oc.FullName, 1)+1,
						CHARINDEX(SPACE(1), oc.FullName, CHARINDEX(SPACE(1), oc.FullName, 1)+1)-1-CHARINDEX(SPACE(1), oc.FullName, 1))
				end,
			   case CHARINDEX(SPACE(1), oc.FullName, CHARINDEX(SPACE(1), oc.FullName, 1)+1)
					when 0 then null
					else
						SUBSTRING(oc.FullName,
						CHARINDEX(SPACE(1), oc.FullName, CHARINDEX(SPACE(1), oc.FullName, 1)+1) +1,
						LEN(oc.FullName))
				end,
               oc.FullName,
               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 NEWID(), p.ID, oc_p.ATD, oc_p.ATC
        from #onk_cancregister oc_p
                OUTER APPLY(
                select top 1 *
                from #onk_cancregister oc_a
                where oc_a.int_id < oc_p.int_id and oc_a.FullName is not null
                order by oc_a.int_id desc
                ) p
                where oc_p.FullName is null and oc_p.ATD is not null and oc_p.ATC is not null
		union
		select NEWID(), oc.ID, oc.ATD, oc.ATC
		from #onk_cancregister oc
		where oc.FullName is not null and oc.ATD is not null and oc.ATC is not null;


		insert into [ISZL].[T_ONK_CANCREGISTER_ACCOUNTING_REMOVAL]
               (
               [OnkCancregisterAccountingRemovalID],
               [OnkCancregister],
               [Date],
               [Code]
               )
        select NEWID(), p.ID, oc_p.ARD, oc_p.ARC
        from #onk_cancregister oc_p
                OUTER APPLY(
                select top 1 *
                from #onk_cancregister oc_a
                where oc_a.int_id < oc_p.int_id and oc_a.FullName is not null
                order by oc_a.int_id desc
                ) p
                where oc_p.FullName is null and oc_p.ARD is not null and oc_p.ARC is not null
		union
		select NEWID(), oc.ID, oc.ARD, oc.ARC
		from #onk_cancregister oc
		where oc.FullName is not null and oc.ARD is not null and oc.ARC is not null;


		insert into [ISZL].[T_ONK_CANCREGISTER_DIAGNOSIS]
               (
               [OnkCancregisterDiagnosisID],
               [OnkCancregister],
               [Date],
               [Code],
			   [Stage]
               )
        select NEWID(), p.ID, oc_p.DD, oc_p.DC, oc_p.DS
        from #onk_cancregister oc_p
                OUTER APPLY(
                select top 1 *
                from #onk_cancregister oc_a
                where oc_a.int_id < oc_p.int_id and oc_a.FullName is not null
                order by oc_a.int_id desc
                ) p
                where oc_p.FullName is null and (oc_p.DD is not null or oc_p.DC is not null)
		union
		select NEWID(), 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);

		select null, null, null as lexem
		union
		select null, null, 'new Process("ProvideInsuredProcess", new Apartment().GetInstance("ISZL.ONK_CANCREGISTER", Cast("'+ cast(oc.ID as varchar(36)) +'", "Guid"))).$Run()'
		from #onk_cancregister oc
		where FullName is not null;

drop table #onk_cancregister;

END
