if exists(select * from sys.tables where name = 'RR_DIRECTION' and Schema_Id = Schema_Id('idb'))
drop table idb.RR_DIRECTION ;
if exists(select * from sys.tables where name = 'RR_DIRECTION_BENEFITS' and Schema_Id = Schema_Id('idb'))
drop table idb.RR_DIRECTION_BENEFITS ;
-- =============================================
-- Author:        (C) Vitacard
-- Create date: 2018-01-18
-- Description:    Загрзка направлений в рр регистр
-- =============================================
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LoadRRDirection]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LoadRRDirection]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RDS].[sp_LoadRRDirection]') AND type in (N'P', N'PC'))
DROP PROCEDURE [RDS].[sp_LoadRRDirection]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [RDS].[sp_LoadRRDirection] 
    @CurrentDateTime datetime = NULL
    ,@UserID uniqueidentifier
AS
BEGIN
--что sql полнял что появились колонки
--select top 1 ID,RNumber
----,* 
--from idb.RR_DIRECTION
--select top 1 ID from idb.RR_DIRECTION_BENEFITS

	update idb.RR_DIRECTION set ID = newID()
	update idb.RR_DIRECTION_BENEFITS set ID = newID()
	
	update idb.RR_DIRECTION set SS = null where SS = ''
	
	update idb.RR_DIRECTION set [NDOK] = null where [NDOK] = ''
	update idb.RR_DIRECTION set [SDOK] = null where [SDOK] = ''

 -- Поиск по СНИЛС + ДР
    update rd
    set rd.RealPac = rp.RrPatientID
    from idb.RR_DIRECTION rd
        join [RDS].T_RR_PATIENT rp on rp.SNILS = rd.SS
    where 
		rd.RealPac is null
        and rd.SS is not null
        and  rd.SS <> ''
        and rd.SS <> '              '
		and (rp.DateEnd is null or rp.DateEnd >= GETDATE())

    -- Поиск по серии и номеру документа
    update rd
    set rd.RealPac = rp.RrPatientID
    from idb.RR_DIRECTION rd
        join [RDS].T_RR_PATIENT rp on rp.DocSeries = rd.[SDOK] and rp.DocNumber = rd.[NDOK]
    where 
		rd.RealPac is null
		and rd.[SDOK] is not null
        and rd.[NDOK] is not null
		and rd.[SDOK] <> ''
		and rd.[NDOK] <> ''
		and (rp.DateEnd is null or rp.DateEnd >= GETDATE())
		and rp.Birthday = rd.[DROD]

    -- Поиск по ФИО + ДР
    update rd
    set rd.RealPac = rp.RrPatientID
    from idb.RR_DIRECTION rd
        join [RDS].T_RR_PATIENT rp on rp.LastName = rd.[FAM] and rp.FirstName = rd.[IM] and (rp.FatherName = rd.[OT] or (rp.FatherName is null and rd.[OT] is null)) and rp.Birthday = rd.[DROD]
			--and ((rd.SS is null and  rp.Snils is null) or rp.Snils = rd.SS)
    where 
		rd.RealPac is null
		and (rp.DateEnd is null or rp.DateEnd >= GETDATE())

--update d
--set d.[RNumber] = t.num
--from idb.RR_DIRECTION d
--join (
--		select *,row_number() over (order by dd.[ID]) as 'num' from idb.RR_DIRECTION dd
--) t on t.ID = d.id
--where d.RealPac is null

	INSERT INTO [RDS].[T_IMPORT_ELEMENTS]
	           ([ImportElementsID]
	           ,[Error]
	           ,[Users]
	           ,[LoadDate]
	           ,[type_]
	           ,[FileName])
	SELECT
	       rd.ID
	       ,null
	       ,@UserID
	       ,@CurrentDateTime
	       ,(select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'IMPORT_RR_DIRECTION' and e.[Schema] ='RDS' )
	       ,rd.FileNameColumn
	from idb.RR_DIRECTION rd
	union 
	SELECT
	       rdb.ID
	       ,null
	       ,@UserID
	       ,@CurrentDateTime
	       ,(select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'IMPORT_RR_DIRECTION_BENEFITS' and e.[Schema] ='RDS' )
	       ,rdb.FileNameColumn
	from idb.RR_DIRECTION_BENEFITS rdb

	INSERT INTO [RDS].[T_IMPORT_RR_DIRECTION]
	           ([ImportElementsID]
	           ,[KOD]
	           ,[PAC_ID]
	           ,[FAM]
	           ,[IM]
	           ,[OT]
	           ,[Sex]
	           ,[DROD]
	           ,[Snils]
	           ,[DocType]
	           ,[SDOK]
	           ,[NDOK]
	           ,[KODADR]
	           ,[LPU_INF]
	           ,[VID_I]
	           ,[DAT_PROV]
			   ,[REAL_PAC_ID]
			   ,TIPD
			   ,SOST_R)
	select		rd.[ID]
	           ,[KOD]
	           ,[PAC_ID]
	           ,[FAM]
	           ,[IM]
	           ,[OT]
	           ,case when [W] = 'М' then 10301 else 10302 end
	           ,[DROD]
	           ,SS
	           ,e.EnumID
	           ,[SDOK]
	           ,[NDOK]
	           ,[KODADR]
	           ,[LPU_INF]
	           ,[VID_I]
	           ,[DAT_PROV]
			   ,ISNULL(RealPac, PAC_ID)
			   --,ISNULL(RealPac, (select ISNULL(max(RrPatientID)+ rd.[RNumber], rd.[RNumber]) from RDS.T_RR_PATIENT))
			   ,TIPD
			   ,SOST_R
	from idb.RR_DIRECTION rd
		join [RDS].[T_IMPORT_ELEMENTS] ie on ie.ImportElementsID = rd.ID
		left join VCLib.T_ENUMS e on e.Code = TIPD and e.EnumTypeID = 105 and ie.Users = @UserID
	
	INSERT INTO [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS]
	           ([ImportElementsID]
	           ,[ImportRrDirection]
	           ,[KOD]
	           ,[Benefits]
	           ,[MKB]
	           ,[DOP]
	           ,[NDOK]
	           ,[VID_I])
	select		rdb.ID
	           ,rd.ImportElementsID
	           ,rdb.[KOD]
	           ,b.BenefitsID
	           ,rdb.[MKB]
	           ,rdb.[DOP]
	           ,rdb.[NDOK]
	           ,rdb.[VID_I]
	from idb.RR_DIRECTION_BENEFITS rdb
		join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = case when rdb.GR = '95' then 5 else Cast(rdb.[VID] as int) + 1 end
		left join RDS.T_BENEFITS b on b.BenefitCode = rdb.GR and b.BenefitViews = e.EnumID
		cross apply 
			(
				select top 1 * from [RDS].[T_IMPORT_RR_DIRECTION] rd where rd.KOD = rdb.[KOD]
			) rd
		join [RDS].[T_IMPORT_ELEMENTS] ie on ie.ImportElementsID = rdb.ID
		join [RDS].[T_IMPORT_ELEMENTS] iee on iee.ImportElementsID = rd.ImportElementsID and ie.Users = @UserID and iee.Users = @UserID

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Обнаружен дубликат направлений в рамках данной МО или данные на этого человека предоставила другая организация!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where 
		exists(select * from [RDS].[T_IMPORT_ELEMENTS] iee 
							join [RDS].[T_IMPORT_RR_DIRECTION] rdd on iee.ImportElementsID = rdd.ImportElementsID
							where 
								rd.ImportElementsID <> rdd.ImportElementsID --and iee.Users = @UserID 
								and ((rd.Snils is not null and rdd.snils is not null and rd.Snils = rdd.snils) or (rd.FAM = rdd.FAM and rd.IM = rdd.IM and rd.DROD = rdd.DROD and (rd.OT is not null and rdd.OT is not null and rd.OT = rdd.OT)) 
								or (rd.[SDOK] is not null and rd.[NDOK] is not null and rdd.[SDOK] is not null and rdd.[NDOK] is not null and rd.[SDOK] = rdd.[SDOK] and rd.[NDOK] = rdd.[NDOK]))
				)
		--and ie.Users = @UserID
	

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Для человека старше 3х лет льгота 57(ребенок первых 3х лет жизни) не действует!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] rdb on ie.ImportElementsID = rdb.ImportElementsID
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on rd.ImportElementsID = rdb.ImportRrDirection
		join RDS.T_BENEFITS b on b.BenefitsID = rdb.Benefits
		join [VCLib].T_ENUMS e on e.EnumID = b.BenefitViews
	where 
		b.BenefitCode = '57' and e.Code = 2 
        and rd.DROD <= DATEADD(YEAR, -3, GETDATE())
		and ie.Users = @UserID
		and rdb.VID_I <> 3

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' СНИЛС указан не корректно!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where 
		len(Rtrim(Ltrim(rd.Snils))) <> 14
		and ie.Users = @UserID


	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' СНИЛС обязательное поле!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where 
		rd.Snils is null 
        and rd.DROD <= DATEADD(month, -6, GETDATE())
		and ie.Users = @UserID

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Пациент присутствует в федеральном регистре!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where ie.Users = @UserID
		and exists 
		(select * from RDS.T_FR_PATIENT fr  
				cross apply 
					(
						select top 1 * 
						from [RDS].T_FR_RECORD rec 
						where rec.Snils = fr.Snils 
						order by rec.Version desc
					) t
			where 
				fr.Birthday = rd.DROD 
				and (fr.DE_EDV > Getdate() or fr.DE_EDV is null or fr.DE_EDV = '') 
				and t.Deleted = 0 
				and ((rd.Snils is not null and fr.Snils = rd.Snils) or (rd.[SDOK] is not null and rd.[NDOK] is not null and fr.DocSeries = rd.SDOK and fr.DocNumber = rd.NDOK) 
				or (fr.LastName = rd.[FAM] and fr.FirstName = rd.[IM] and (fr.FatherName = rd.[OT] or (fr.FatherName is null and rd.[OT] is null)) and ((rd.Snils is null and  fr.Snils is null) or fr.Snils = rd.Snils)))
		)
	
	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Не указан ID пациента(PAC_ID)!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where 
		(rd.REAL_PAC_ID is null or rd.REAL_PAC_ID = 0) 
		and rd.VID_I <> 1
		and ie.Users = @UserID

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Пациент уже присутствует в базе! ID пациента(PAC_ID): ' + ISNULL(Cast(rd.PAC_ID as varchar(20)), '')
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID and ie.Users = @UserID
	where 
		rd.VID_I = 1 
		and rd.PAC_ID is not null 
		and rd.PAC_ID <> 0

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Пациент не найден в базе!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where 
		rd.VID_I <> 1 
		and (rd.REAL_PAC_ID is null or rd.REAL_PAC_ID = 0 or (rd.REAL_PAC_ID is not null and rd.REAL_PAC_ID <> 0 and rd.PAC_ID is not null and rd.PAC_ID <> 0 and rd.PAC_ID <> rd.REAL_PAC_ID) or not exists(select * from RDS.T_RR_PATIENT rr where rr.RrPatientID = rd.REAL_PAC_ID))
		and ie.Users = @UserID
	
	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Не верный код МО!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where
		not exists (select * from RDS.T_DLO_LPU dl where dl.MCOD = rd.LPU_INF)
		and ie.Users = @UserID
	
	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Нет данных о льготах!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where 
		rd.VID_I = 1 
		and not exists (select * from [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] db where db.ImportRrDirection = ie.ImportElementsID)
		and ie.Users = @UserID
	

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Дубль льгот!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] rd on ie.ImportElementsID = rd.ImportElementsID
	where 
		rd.VID_I = 1
		and ie.Users = @UserID 
		and exists (select * from [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] db 
								where 
									db.ImportElementsID <> rd.ImportElementsID 
									and db.ImportRrDirection = rd.ImportRrDirection 
									and db.Benefits = rd.Benefits)
	
	
	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Данная льгота от указанной МО уже добавлена!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] rd on ie.ImportElementsID = rd.ImportElementsID
		join RDS.T_IMPORT_RR_DIRECTION rrd on rrd.ImportElementsID = rd.ImportRrDirection
	where 
		rd.VID_I = 1
		and ie.Users = @UserID
		and exists (select * from [RDS].[T_RR_PATIENT_BENEFIT] db 
									join RDS.T_RR_PATIENT rrp on rrp.Id = db.RrPatient
								where 
									rrp.RrPatientID = rrd.REAL_PAC_ID 
									and db.Benefits = rd.Benefits 
									and (db.EndDate is null or db.EndDate > GETDATE())
									and rrd.LPU_INF = db.LPU_INF)

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Льгота от указаной МО не найдена !'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] rdb on ie.ImportElementsID = rdb.ImportElementsID
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on rd.ImportElementsID = rdb.ImportRrDirection
	where 
		rdb.VID_I <> 1
		and ie.Users = @UserID
		and not exists (select * from [RDS].[T_RR_PATIENT_BENEFIT] pb 
										join RDS.T_RR_PATIENT rrp on rrp.Id = pb.RrPatient
									where 
										pb.LPU_INF = rd.LPU_INF 
										and rdb.Benefits = pb.Benefits 
										and rrp.RrPatientID = rd.PAC_ID)
	
	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Льгота 95 не должна включаться в Региональный Регистр !'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] rdb on ie.ImportElementsID = rdb.ImportElementsID
		join RDS.T_BENEFITS b on b.BenefitsID = rdb.Benefits
	where  
		b.BenefitCode = '95'
		and ie.Users = @UserID
	
	if(exists (select top 1 * from RDS.T_SICKNESS_GROUP s
									join [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] rdb on rdb.Benefits = s.Benefits
									join [RDS].[T_IMPORT_ELEMENTS] ie  on ie.ImportElementsID = rdb.ImportElementsID
								where ie.Users = @UserID))	
	begin

		update ie
			set ie.Error = IsNull(ie.Error, ' ') + ' Не правильный код МКБ для указанной льготы!'
		from [RDS].[T_IMPORT_ELEMENTS] ie 
			join [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] rdb on ie.ImportElementsID = rdb.ImportElementsID
			join RDS.T_BENEFITS b on b.BenefitsID = rdb.Benefits
			join [VCLib].T_ENUMS e on e.EnumID = b.BenefitViews
		where 
			e.Code = 3
			and ie.Users = @UserID
			and not exists (select * from RDS.T_SICKNESS_GROUP s 
									 where 
										s.Benefits = b.BenefitsID
										and s.MKBK >= rdb.MKB
										and s.MKBN <= rdb.MKB)
	end

	update ie
		set ie.Error = IsNull(ie.Error, ' ') + ' Имеются ошибки в льготах!'
	from [RDS].[T_IMPORT_ELEMENTS] ie 
		join [RDS].[T_IMPORT_RR_DIRECTION] rd on ie.ImportElementsID = rd.ImportElementsID
	where 
		ie.Users = @UserID
		and exists (select * from [RDS].[T_IMPORT_RR_DIRECTION_BENEFITS] db 
									join RDS.T_IMPORT_ELEMENTS iee on iee.ImportElementsID = db.ImportElementsID
							 where 
							 	db.ImportRrDirection = ie.ImportElementsID 
							 	and iee.Error is not null)
	
update [RDS].[T_IMPORT_ELEMENTS] 
	set Error = null 
where Error = '' and Users = @UserID

delete from idb.RR_DIRECTION_BENEFITS
delete from idb.RR_DIRECTION
END
