SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MakeSendingEXBPackage]') and xtype = N'P')
drop procedure [dbo].[sp_MakeSendingEXBPackage]
GO
-- =============================================
-- Author:		ЗАО Витакор
-- Create date: 2022.11.15
-- Description:	Формирование пакета профосмотра/диспансеризации в ТФОМС
-- =============================================
CREATE PROCEDURE [dbo].[sp_MakeSendingEXBPackage] 
	@packageID uniqueidentifier
	, @date datetime
	, @lpuCode varchar(10)
	, @result varchar(max) OUTPUT
As
Begin
	SET NOCOUNT ON;

	DECLARE @today [datetime] = cast(cast(cast(getdate() as float) as int) as datetime)
	SELECT @Result = '<?xml version="1.0" encoding="utf-8"?>'+( 
			select
			'1.0'												as 'ZGLV/VERSION'
			,convert(varchar(10), @date, 20)					as 'ZGLV/DATA'
			,@packageID											as 'ZGLV/ID'
			,@lpuCode											as 'ZGLV/MCOD'
			,(
			
			select -- ZL
					 it.SendingEXBItemID						as 'ID_PAC'
					,p.LastName									as 'FAM'
					,p.FirstName								as 'IM'
					,p.FatherName								as 'OT'
					,convert(varchar(10), p.Birthday, 20)		as 'DR'
					,isnull(p.ENP,(case when t1.PolicyForm in (2,3) then t1.Number else '' end)) as 'ENP'
					,case t1.PolicyForm 
						when 1 then 1
						when 2 then 3
						when 3 then 3
						when 4 then 2
						end as 'VPOLIS'
					,t1.Serial									as 'SPOLIS'
					,t1.Number									as 'NPOLIS'
					,p.SNILS									as 'SNILS'

					,convert(varchar(10), e.EventDate, 20)									as 'PP/DATA_1'
					,convert(varchar(10), e.CloseDate, 20)									as 'PP/DATA_2'
					,(case et.ExaminationType
						when 1 then 'ОПВ' 
						when 2 then 'ДВ4' 
						when 4 then 'ДВ2' 
						when 9 then ( case when et.Code = '1017' then 'УД1' else 'УД2' end )
						else '' end ) as 'PP/DISP'
					,0 as 'PP/P_OTK'
					,ex.V017DispResult as 'PP/RSLT_D'
					,ac.Number as 'PP/NHISTORY'
					,isnull(BunchVisit.MKBCode,'') as 'PP/DS1'
					,(case when BunchVisit.SicknessMode in (1,2) then '1' else null end) as 'PP/DS1_PR'
					,isnull((select top 1 '1' 
							from akuz.T_PRESCRIPTION_OF_OUTCOME pres
							where BunchVisit.Outcome = pres.OutcomeOfInspection
								and pres.DS_ONK = 1),'0') as 'PP/DS_ONK'
					,isnull((case when BunchVisit.CheckUpStatus = 19602 then '1'
								when BunchVisit.CheckUpStatus in (19603,19604,19605,19606,19607) then '2'
								else '3' end),'3') as 'PP/PR_D_N'
					
					, ( -- USL
					select(
					select 
						convert(varchar(10), l.ExecutionDate, 20) as 'DATE_IN'
						,convert(varchar(10), l.ExecutionDate, 20) as 'DATE_OUT'
						,(case when l.[Status]=3 then 1 else 0 end)			as 'P_OTK'
						,ms.Code			as 'CODE_USL'
						,ms.[Name]			as 'USL'
						,isnull(v21.Id,'')	as 'PRVS'
						,isnull(w.Snils,'') as 'CODE_MD'
					FROM AKUZ.T_EXAMINATION_TYPE_LINK l
						left join akuz.T_MEDICAL_SERVICE ms on l.MedicalService = ms.MedicalServiceID
						--left join akuz.T_SPECIALITY spec on l.Speciality = spec.SpecialityID
						left join akuz.T_POST p on p.ResourceID = case 
																	when l.[type]=0 then (select v.Post from akuz.T_VISIT v where l.BunchID = v.VisitID)
																	when l.[type]=1 then (select mr.Post 
																							from akuz.T_DIAGNOSTICS_RESULTS dr
																							join akuz.T_MEDICAL_RECORD mr on dr.MedicalRecordID = mr.MedicalRecordID
																							where l.BunchID = dr.MedicalRecordID)
																end
						left join akuz.T_V021_SPECIALITY v21 on p.V021Speciality = v21.V021SpecialityID
						left join akuz.T_WORKER w on p.Worker = w.WorkerID
					where l.ExaminationBase = e.EventID
					FOR XML PATH('USL'),TYPE
					) FOR XML PATH(''),
					TYPE
					) as 'PP'

			from akuz.T_SENDING_EXB_ITEM it
				join akuz.T_EXAMINATION_BASE ex on it.ExaminationBase = ex.EventID
				join akuz.t_event_base e on  ex.EventID = e.EventID 
				join akuz.t_ambulance_card ac on e.AmbulanceCard = ac.AmbulanceCardID
				join akuz.t_patient p on ac.Patient = p.PatientID
				left join AKUZ.T_POLICY t1 ON t1.PolicyID = 
									( SELECT TOP 1 
										CASE WHEN t4.ValidThrough IS NULL OR @today<=t4.ValidThrough 
											THEN t4.PolicyID ELSE NULL
										END
									FROM AKUZ.T_POLICY t4
									LEFT JOIN AKUZ.T_PATIENT t5 ON t5.PatientID = t4.Patient
									WHERE t4.type_ = (select EntityID from vclib.T_ENTITIES where entname like 'OMS_POLICY')
									AND t5.PatientID=p.PatientID
									AND @today>=t4.IssueDate
									ORDER BY t4.IssueDate DESC,IsNull(t4.ValidThrough, dateadd(YEAR, 100, t4.IssueDate)) DESC)
				join akuz.T_EXAMINATION_TYPE et on ex.ExaminationType = et.ExaminationTypeID
				left join (select top 1 l.ExaminationBase EventID, oc.MKBCode, oc.SicknessMode, oc.CheckUpStatus, oc.MedicalRecordID Outcome 
					from akuz.T_EXAMINATION_TYPE_LINK l 
						join akuz.T_MEDICAL_SERVICE ms on l.MedicalService = ms.MedicalServiceID
						join akuz.T_VISIT v on l.BunchID = v.VisitID
						join akuz.T_OUTCOME_OF_INSPECTION oc on v.OutcomeRecord = oc.MedicalRecordID
					--where l.ExaminationBase = e.EventID and [type]=0 and ms.Code = 'B04.047.002'
					where [type]=0 and ms.Code = 'B04.047.002'
					order by l.ExecutionDate desc) BunchVisit on BunchVisit.EventID = e.EventID

			where it.SendingExbPackage = @packageID
			FOR XML PATH('ZL'),
			TYPE
			)
			FOR XML PATH(''),
			ROOT('PP_LIST')
	) 

return;

End