IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetMovementsCardView]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetMovementsCardView]
GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Dmitriy Plaksin
---- Create date: 2010-11-24
---- Description:	Заполнение T_MOVEMENTS_CARD_VIEW
---- =============================================
--CREATE PROCEDURE [dbo].[sp_GetMovementsCardView] 
--AS
--BEGIN
--    SET NOCOUNT ON;
    
--	delete from T_MOVEMENTS_CARD_VIEW

--	--Переменные курсора
--	declare 
--		@ViewID uniqueidentifier --Индекс
--		,@Division uniqueidentifier --Отделение (не реанимация)
--		,@StructureBed uniqueidentifier --Профиль койки в отделении
--		,@DDateBegin datetime --Дата поступления в отделение
--		,@DDateEnd datetime --Дата выписки из отделения
--		,@Reanimation uniqueidentifier --Отделение (реанимация)
--		,@ReanimationsBed uniqueidentifier --Профиль койки в реанимации
--		,@RDateBegin datetime --Дата поступления в реанимацию
--		,@RDateEnd datetime --Дата выписки из реанимации
--		,@DateBegin datetime --Стат дата поступления
--		,@DateEnd datetime --Стат дата выписки
--		,@MovementType int --Исход
--		,@DeathInReanimation bit --Признак смерти в реанимации
--		,@MedicalRegistration uniqueidentifier --Парент на MEDICAL_REGISTRATION (MOVEMENT)
--		,@MovementCardID uniqueidentifier
--		--,@RowNumber decimal(31,10)
--		,@last_perc int

--	--Вспомогательные переменные
--		,@PrevEndDate datetime
--		,@MovID uniqueidentifier
--		,@PrevDiv uniqueidentifier
--		,@DifDiv uniqueidentifier
--		,@PrevViewID uniqueidentifier

--	declare @all decimal(31,10)
--	select @all = COUNT(*) from [dbo].V_MOVEMENTS_CARD
--	set @last_perc = 0

--	--print 'Start'

--	--Курсор
--	DECLARE crs CURSOR LOCAL STATIC FOR
--	SELECT 
--		ViewID ,Division ,StructureBed ,DDateBegin ,DDateEnd ,Reanimation ,ReanimationsBed ,RDateBegin ,RDateEnd 
--		,DateBegin ,DateEnd ,MovementType ,DeathInReanimation ,MedicalRegistration --,ROW_NUMBER() over (order by MedicalRegistration, DDateBegin, DDateEnd, Division asc)
--	From 
--	(
--		Select 
--			DateBegin = case when t2.DDateBegin < t2.RDateBegin or t2.RDateBegin is null then t2.DDateBegin else t2.RDateBegin end 
--			,DateEnd = case when t2.DDateEnd > t2.RDateEnd or t2.RDateEnd is null then t2.DDateEnd else t2.RDateEnd end 
--			,t2.ViewID, t2.Division ,t2.StructureBed ,t2.DDateBegin ,t2.DDateEnd ,t2.Reanimation ,t2.ReanimationsBed ,t2.RDateBegin ,t2.RDateEnd 
--			,t2.MovementType ,t2.DeathInReanimation ,t2.MedicalRegistration
--		From
--		(
--			Select 
--				Reanimation = md.Division
--				,RDateBegin = mc.BeginCureDate
--				,RDateEnd = ml.PostingDate
--				,ReanimationsBed = mc.StructureBed
--				,DeathInReanimation = case when mc.MovementType = 6 then 1 else 0 end
--				,t3.DDateBegin, t3.DDateEnd, t3.ViewID ,t3.Division ,t3.StructureBed, t3.MovementType ,t3.MedicalRegistration
--			from 
--				(
--					Select 
--						Reanimation = 
--							(
--								select top 1 mc.MovementsCardID
--								from [AKUZ].T_MOVEMENTS_CARD mc
--									join [AKUZ].T_MOVEMENTS_DIVISION md on md.MovementsDivisionID = mc.MovementsDivision
--										join [AKUZ].T_DIVISION d on d.DivisionID = md.Division
--									left join [AKUZ].T_MOVEMENTS_LPU ml on ml.MovementsLpuID = md.MovementsLpu
--								where d.DivisionType = 12806 
--											and 
--										mc.MovementType in (1,3,6,7,9,11,13)
--											and
--										mc.MedicalcardRegistration = t4.MedicalRegistration
--											and
--										(t4.DDateEnd = BeginCureDate or t4.DDateBegin = ml.PostingDate)
--							)
--						,t4.DDateBegin, t4.DDateEnd, t4.ViewID ,t4.Division ,t4.StructureBed, t4.MovementType ,t4.MedicalRegistration
--					from 
--					(
--						SELECT
--								ViewID = NEWID()--ViewID uniqueidentifier --Индекс
--								,Division = md.Division--Division uniqueidentifier --Отделение (не реанимация)
--								,StructureBed = mc.StructureBed--StructureBed uniqueidentifier --Профиль койки в отделении
--								,DDateBegin = mc.BeginCureDate--DDateBegin datetime --Дата поступления в отделение
--								,DDateEnd = ml.PostingDate--DDateEnd datetime --Дата выписки из отделения
--								,MovementType = mc.MovementType--MovementType int --Исход
--								,MedicalRegistration = mc.MedicalcardRegistration--MedicalRegistration uniqueidentifier --Парент на MEDICAL_REGISTRATION (MOVEMENT)
--								,MovementCardID = mc.MovementsCardID--MovementCardID uniqueidentifier
--						from [AKUZ].T_MOVEMENTS_CARD mc
--							left join [AKUZ].T_DIVISION mcd on mcd.DivisionID = mc.Division
--							left join [AKUZ].T_MOVEMENTS_DIVISION md on md.MovementsDivisionID = mc.MovementsDivision
--							left join [AKUZ].T_MOVEMENTS_LPU ml on ml.MovementsLpuID = md.MovementsLpu
--						where mc.MovementType in (1,3,6,7,9,11,13)
--					) t4
--				) t3
--				Left join [AKUZ].T_MOVEMENTS_CARD mc on mc.MovementsCardID = t3.Reanimation
--					Left join [AKUZ].T_MOVEMENTS_DIVISION md on md.MovementsDivisionID = mc.MovementsDivision
--					Left join [AKUZ].T_MOVEMENTS_LPU ml on ml.MovementsLpuID = md.MovementsLpu
--		) t2
--	) t1
--	order by MedicalRegistration, DDateBegin, DDateEnd, Division asc



--	--Открываем курсор
--	Open crs
--	FETCH NEXT FROM crs 
--	INTO @ViewID ,@Division ,@StructureBed ,@DDateBegin ,@DDateEnd ,@Reanimation ,@ReanimationsBed ,@RDateBegin ,@RDateEnd 
--		,@DateBegin ,@DateEnd ,@MovementType ,@DeathInReanimation ,@MedicalRegistration --,@RowNumber 

--	WHILE @@FETCH_STATUS = 0
--	BEGIN 

--		IF (
--				@PrevEndDate is not null
--				and
--				@PrevEndDate = @DDateBegin
--				and
--				12806 = ( select d.DivisionType from [AKUZ].T_DIVISION d where d.DivisionID = @PrevDiv )
--				and
--				@MovID = @MedicalRegistration
--				and 
--				@DifDiv = @Division
--			)
--		begin
--			update mcv
--			set mcv.DateEnd = @DDateEnd
--				,mcv.RDateEnd = @DDateBegin
--				,mcv.MovementType = @MovementType
--			from T_MOVEMENTS_CARD_VIEW mcv
--			where mcv.ViewID = @PrevViewID
--		end

--		ELSE

--		begin
--			INSERT INTO T_MOVEMENTS_CARD_VIEW([Division] ,[StructureBed] ,[DDateBegin] ,[DDateEnd] ,[Reanimation] ,[ReanimationsBed] ,[RDateBegin] ,[RDateEnd] ,[DateBegin] ,[DateEnd] ,[MovementType] ,[DeathInReanimation] ,[MedicalRegistration] ,[ViewID])
--			select @Division ,@StructureBed ,@DDateBegin ,@DDateEnd ,@Reanimation ,@ReanimationsBed ,@RDateBegin ,@RDateEnd ,@DateBegin ,@DateEnd ,@MovementType ,@DeathInReanimation ,@MedicalRegistration ,@ViewID
			
--			set @DifDiv = @Division
--			set @PrevViewID = @ViewID
--		end

--		set @PrevEndDate = @DDateEnd
--		set @MovID = @MedicalRegistration
--		set @PrevDiv = @Division

----		declare @perc int
----		set @perc = (@RowNumber/@all)*100.0

----		if (@perc <> @last_perc)
----		begin
----			--raiserror ('fds', 1, 34)
----			print (@RowNumber/@all)*100
----			set @last_perc = @perc
----		end

--		-- след. запись
--		FETCH NEXT FROM crs 
--		INTO @ViewID ,@Division ,@StructureBed ,@DDateBegin ,@DDateEnd ,@Reanimation ,@ReanimationsBed ,@RDateBegin ,@RDateEnd 
--			,@DateBegin ,@DateEnd ,@MovementType ,@DeathInReanimation ,@MedicalRegistration --,@RowNumber 
--	END

--	CLOSE crs
--	deallocate crs

--	update t
--	set t.BedsNumber = 
--	(
--		select top 1 a.BiSexBed
--		from [AKUZ].T_AMOUNT_BED a
--			join [AKUZ].T_DIVISION_LINK_STRUCTURE_BED l on l.DivisionLinkStructureBedID = a.DivisionLinkStructureBed
--			join [AKUZ].T_DIVISION d on d.DivisionID = l.Division
--		where l.Division = t.Division
--				and 
--			t.DateEnd >= a.DateBegin
--				and
--			(t.DateEnd <= a.DateEnd or a.DateEnd is null)
--	)
--	from T_MOVEMENTS_CARD_VIEW t

--END