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 s.POLNAME = oc.Column2) 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;

    insert into [ISZL].[T_ONK_CANCREGISTER]
               (
               [OnkCancregisterId],
               [FullName],
               [BirthDate],
               [Sex],
               [RegNumber],
               [RegionCode],
               [Address]
               )
        select
               oc.ID,
               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 * from #onk_cancregister;

drop table #onk_cancregister;

END
