/****** Object:  StoredProcedure [dbo].[sp_AddDoctorVisitNotify]    Script Date: 20.10.2014 13:13:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'sp_AddDoctorVisitNotify')
                    AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE [dbo].[sp_AddDoctorVisitNotify]
GO
-- =============================================
-- Author:  Timur R. Khakimov  2014
-- Create date: 2014-10-13
-- Description: Заполнение таблицы информацией о закрытых случаях
-- =============================================

CREATE PROCEDURE [dbo].[sp_AddDoctorVisitNotify] @MedicalRecordID uniqueidentifier, @IsNew bit
AS
BEGIN
	 IF @IsNew = 1
	 BEGIN
		DECLARE @DoctorVisitNotifyServiceStatus as int 
		set @DoctorVisitNotifyServiceStatus = (SELECT [Status] from [AKUZ].[T_VCSN_SERVICES] where [VCSNServiceName]='DoctorVisitNotify')
		IF @DoctorVisitNotifyServiceStatus = 1
		BEGIN
			DECLARE @PatientID as uniqueidentifier, 
					@SpecialityName as varchar(255),
					@DoctorFIO as varchar(255),
					@LpuCode as varchar(4),
					@BranchCode as varchar(16),
					@PostID as uniqueidentifier,
					@IsMovementResult as bit,
					@IndexType as int
			SELECT 

			@PatientID = AmbulanceCard.Patient,
			@SpecialityName = Speciality.Name,
			@DoctorFIO = Worker.FIO,
			@LpuCode = LpuMain.Code,
			@PostID = PResource.ResourceIdGuid,
			@BranchCode = Branch.BranchCode,
			@IndexType = MedicalRecord.type_,
			@IsMovementResult = 0

			FROM [AKUZ].[T_OUTCOME_OF_INSPECTION] OutcomeOfInspection

			JOIN [AKUZ].[T_AMBULANCE_CARD_RECORD] AmbulanceCardRecord on (AmbulanceCardRecord.MedicalRecordID = OutcomeOfInspection.MedicalRecordID)
			JOIN [AKUZ].[T_AMBULANCE_CARD] AmbulanceCard on (AmbulanceCard.AmbulanceCardID = AmbulanceCardRecord.AmbulanceCard)
			JOIN [AKUZ].[T_MEDICAL_RECORD] MedicalRecord on (MedicalRecord.MedicalRecordID = OutcomeOfInspection.MedicalRecordID)
			JOIN [AKUZ].[T_RESOURCE] PResource on (PResource.ResourceID = MedicalRecord.Post)
			JOIN [AKUZ].[T_SPECIALITY] Speciality on (Speciality.SpecialityID = PResource.Speciality)
			JOIN [AKUZ].[T_POST] Post on (Post.ResourceID = MedicalRecord.Post)
			JOIN [AKUZ].[T_WORKER] Worker on (Worker.WorkerID = Post.Worker)
			JOIN [AKUZ].[T_DIVISION] Division on (Division.DivisionID = PResource.Division)
			JOIN [AKUZ].[T_BRANCH] Branch on (Branch.LpuID = Division.Branch)
			JOIN [AKUZ].[T_LPU_MAIN] LpuMain on (LpuMain.LpuID = Branch.LpuMain)

			WHERE OutcomeOfInspection.MedicalRecordID = @MedicalRecordID
			IF @IndexType = 1110 SET @IsMovementResult = 1
			INSERT INTO [AKUZ].[T_DOCTOR_VISIT_NOTIFY2] (DoctorVisitNotifyID, PatientID, SpecName, DoctorFIO, LpuCode, BranchCode, IsMovementResult, MedicalRecordID, CreationDT, PostID)
			VALUES(NEWID(), @PatientID, @SpecialityName, @DoctorFIO, @LpuCode, @BranchCode, @IsMovementResult, @MedicalRecordID, GETDATE(), @PostID)
		END
	 END
	 ELSE DELETE FROM [AKUZ].[T_DOCTOR_VISIT_NOTIFY2] where [MedicalRecordID] = @MedicalRecordID AND [CreationDT] > DATEADD (DAY , -1 , GETDATE())
END
GO


