if exists ( select * from sys.procedures p where p.name = 'sp_GetActsReportPeriodXml' ) DROP PROCEDURE [dbo].[sp_GetActsReportPeriodXml]
GO

CREATE PROCEDURE [dbo].[sp_GetActsReportPeriodXml](@year int, @month int)
as
begin
	set nocount on;
	declare @Number varchar(5)
	declare @filename varchar(20)
	declare @smo varchar(5)

	select @smo = smo.SMOCOD
	from [IES].T_COMPANY_SETTINGS cs
		join [IES].T_COMPANY comp on comp.CompanyID = cs.Company
		join [IES].T_F002_SMO smo on smo.SMOCOD = comp.Smo

	declare @NumberName varchar(64)




	set @NumberName = 'MAIN_MEDICAL_ACT_'+cast(@year as varchar)+cast(@month as varchar);
	exec @Number = [dbo].sp_GenerateNumber 662, @NumberName, 1, 1
	

	select me1.MedicalExpertiseID
	into #error_exp
	FROM [IES].T_MAIN_MEDICAL_ACT mma with(nolock)
	join ies.T_MEDICAL_REQUEST mr1 with(nolock) on mr1.MedicalRequestID = mma.MedicalRequest
	join ies.T_MEDICAL_EXPERTISE me1 with(nolock) on mr1.MedicalExpertise = me1.MedicalExpertiseID
	left join ies.T_F006_CONTROL_TYPE et with(nolock) on et.F006ControlTypeID = ISNULL(mma.F006ControlType, me1.F006ControlType)
	where --isnull(mma.R_23Order_year,'2019') = @year and mma.R_23Order_month = @month and 
		mma.DateConfirm is not null and ( mma.IsArchive=0 or mma.IsArchive is null)
		and et.F006ControlTypeID is null


	if (select count(*) from #error_exp)>0 
		begin
			declare @error_exps varchar(max) ='Не на всех отборах указан вид экспертизы (идентификаторы отбора): ' + char(10) + char(13) + (select STRING_AGG(convert(varchar(36),MedicalExpertiseID),char(10)+char(13)) from #error_exp)
			raiserror(@error_exps, 18,1)
		end



	set @filename = 'RS'+@smo+'T67_'+Right(cast(@year as varchar),2)+cast(@month as varchar)+@Number

	DECLARE @t1 table (FileName varchar(max), Xml varchar(max))
	INSERT @t1 (FileName, Xml)
			select
			@filename as 'FileName',
			'<?xml version="1.0" encoding="Windows-1251"?>' 
			+  (select
					--ZGLV
					@Number as 'ZGLV/CODE'
					,'3.0' AS 'ZGLV/VERSION' 
					,CONVERT(VARCHAR(10), GETDATE(), 126) AS 'ZGLV/DATE'
					,@year as 'ZGLV/YEAR'
					,@month as 'ZGLV/MONTH'
					,@smo as 'ZGLV/SMO'
					,@filename as 'ZGLV/FILENAME'
					,(SELECT 
						(SELECT 
							mma.MainMedicalActID as 'ID_ACT'
							,(
								--select top 1 s.CODE_MO 
								select top 1 mamr.mo
								from ies.T_MEDICAL_ACT ma 
								join ies.T_MEDICAL_REQUEST mamr on mamr.MedicalRequestID = ma.MedicalRequest
								--join ies.T_SCHET s on s.SchetID = ma.Schet
								where ma.MainMedicalAct = mma.MainMedicalActID
							) as 'CODE_MO'
							,mma.AdditionalNumber as 'N_ACT'
							,convert(varchar(10),mma.DatePost,23) as 'D_ACT'
							--S_ACT
							--AGREE_DATE_ACT
							--RECEIVE_DATE_ACT
							--,et.ExpertiseTypeID as 'V_ACTID'
							,et.IDVID as 'V_ACT'
							--R_ACT
							--select * from ies.T_MEDICAL_REQUEST_ITEM
							--,(select sum(isnull(ma.ActSum,0)) from ies.T_MEDICAL_ACT ma where ma.MainMedicalAct = mma.MainMedicalActID) as 'SUM_ACT'
				,isnull((select   isnull(sum(mri.mekcost) ,0) 
				+ isnull(sum(mri.meecost) ,0) 
				+ isnull(sum(mri.ekmpcost),0)  
				+ isnull(sum(mri.FineSum) ,0) from ies.T_MEDICAL_REQUEST_ITEM mri with(nolock) where mri.MedicalRequest = mr1.MedicalRequestID),0) as 'SUM_ACT'

							,isnull((select sum(isnull(mris.FineSum,0)) from IES.T_MEDICAL_REQUEST_ITEM mris 
								join ies.T_MEDICAL_ACT mas on mas.MedicalActID = mris.MedicalActMEK and mas.MainMedicalAct = mma.MainMedicalActID),0)
							+isnull((select sum(mris.FineSum) from IES.T_MEDICAL_REQUEST_ITEM mris 
								join ies.T_MEDICAL_ACT mas on mas.MedicalActID = mris.MedicalActMEE and mas.MainMedicalAct = mma.MainMedicalActID),0)
							+isnull((select sum(isnull(mris.FineSum,0)) from IES.T_MEDICAL_REQUEST_ITEM mris 
								join ies.T_MEDICAL_ACT mas on mas.MedicalActID = mris.MedicalActEKMP and mas.MainMedicalAct = mma.MainMedicalActID),0)  as 'SUM_ACT_PENALTY'

							,isnull((select sum(mris.MEKcost) from IES.T_MEDICAL_REQUEST_ITEM mris 
								join ies.T_MEDICAL_ACT mas on mas.MedicalActID = mris.MedicalActMEK and mas.MainMedicalAct = mma.MainMedicalActID),0)
							+isnull((select sum(mris.MEEcost) from IES.T_MEDICAL_REQUEST_ITEM mris 
								join ies.T_MEDICAL_ACT mas on mas.MedicalActID = mris.MedicalActMEE and mas.MainMedicalAct = mma.MainMedicalActID),0)
							+isnull((select sum(mris.EKMPcost) from IES.T_MEDICAL_REQUEST_ITEM mris 
								join ies.T_MEDICAL_ACT mas on mas.MedicalActID = mris.MedicalActEKMP and mas.MainMedicalAct = mma.MainMedicalActID),0)  as 'SUM_ACT_NOPAYMENT'
						FOR XML PATH ('ACT_ZGLV'),  TYPE
						)
						,(SELECT 
							sz.N_ZAP as 'N_ZAP'
							,s.VERSION as 'SCHET/VERSION'
							,s.CODE as 'SCHET/CODE'
							,s.CODE_MO as 'SCHET/CODE_MO'
							,s.YEAR as 'SCHET/YEAR'
							,s.MONTH as 'SCHET/MONTH'
							,s.NSCHET as 'SCHET/NSCHET'
							,CONVERT(VARCHAR(10), s.DSCHET, 126) as 'SCHET/DSCHET'
							,s.PLAT as 'SCHET/PLAT'

							,(select
								ssa.IDCASE as 'IDCASE'
								,(select
									ISNULL ( ss.SL_ID , ssa.IDCASE )  as 'SL_ID'
									,ss.SUMV as 'SUMV'
									,ssa.OPLATA as 'OPLATA'
									,ss.SUMP as 'SUMP'
									,substring(mri.EKMPDescription,1,250) as 'DESCR'
					,case when exists(select * from ies.T_MEDICAL_ERROR me with(nolock)	where me.SchetSluch = ss.SchetSluchID and me.IsMainError = 1  and me.MedicalRequestItem = mri.MedicalRequestItemID)
							then mri.FineSum else 0 end as 'SUM_PENALTY'
					,case when exists(select * from ies.T_MEDICAL_ERROR me with(nolock)	where me.SchetSluch = ss.SchetSluchID and me.IsMainError = 1  and me.MedicalRequestItem = mri.MedicalRequestItemID)
					then case mma.MainActType when 1 then MEEcost when 0 then MEKcost when 2 then  EKMPcost end    ---поменял местами условия типов 14.10.2021
					else 0 end as 'SUM_NOPAYMENT'
									--, as 'IS_DISSECT'
									--, as 'DISSECT'
									,(select 
										me.MedicalErrorID as 'S_CODE'
										,me.SupposedSankSum as 'S_SUM'
										,(case when mri.F042Trekmp is not null then 
											(SELECT top 1 f4.FAM FROM ies.T_F042_TREKMP  f42
											 join ies.T_F004_EXPERT f4 on f4.N_EXPERT=f42.N_EXPERT
											  where F042TrekmpID = mri.F042Trekmp)
											 else 
											 (SELECT top 1 f4.FAM FROM ies.T_F042_TREKMP f42
											 join ies.T_F004_EXPERT f4 on f4.N_EXPERT=f42.N_EXPERT
											 where F042TrekmpID = mre.F042Trekmp)											 
										end
										)as 'EXPERT'
										,dr.Kod as 'S_OSN'
										,me.IsMainError as 'IS_SANK'
										,substring(dr.Komment ,1,250) as 'S_COM'
										from ies.T_MEDICAL_ERROR me with(nolock)
											join [IES].T_F014_DENY_REASON dr on dr.F014DenyReasonID = me.DenyReason
											left join ies.T_MEDICAL_REQUEST_EXPERT mre on mre.MedicalRequestExpertID=me.MedicalRequestExpert
										where me.SchetSluch = ss.SchetSluchID and me.IsMainError = 1 and me.MedicalRequestItem = mri.MedicalRequestItemID
									FOR XML PATH ('DEFECT'), TYPE
									)
									,substring(ss.COMENTSL,1,250) as 'COMENTSL'
								from ies.T_SCHET_SLUCH ss 
								where ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID --and exists (select * from ies.T_MEDICAL_ERROR me with(nolock) where me.SchetSluch = ss.SchetSluchID and me.IsMainError = 1 and me.MedicalRequestItem = mri.MedicalRequestItemID)
								FOR XML PATH ('SL'),  TYPE
								)
							FOR XML PATH ('Z_SL'),  TYPE
							)
							from ies.T_MEDICAL_REQUEST_ITEM mri
								--join ies.T_MEDICAL_REQUEST mr with(nolock) on mri.MedicalRequest = mr.MedicalRequestID and mr1.MedicalRequestID=mr.MedicalRequestID
								join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa with(nolock) on mri.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID --and exists (select * from ies.T_MEDICAL_ERROR me with(nolock) where me.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID and me.IsMainError = 1 and me.MedicalRequestItem = mri.MedicalRequestItemID)
								cross apply (
									select top 1 SchetZap
									from ies.T_SCHET_SLUCH cass with(nolock)
									where cass.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
								) cazap								
								join ies.T_SCHET_ZAP sz with(nolock) on sz.SchetZapID = cazap.SchetZap
								join ies.T_SCHET s with(nolock) on s.SchetID = sz.Schet 
								left join ies.T_MEDICAL_ACT mamek with(nolock) on mri.MedicalActMEK = mamek.MedicalActID and mamek.MainMedicalAct = mma.MainMedicalActID
								left join ies.T_MEDICAL_ACT mam with(nolock) on mri.MedicalActMEE = mam.MedicalActID and mam.MainMedicalAct = mma.MainMedicalActID
								left join ies.T_MEDICAL_ACT mae with(nolock) on mri.MedicalActEKMP = mae.MedicalActID and mae.MainMedicalAct = mma.MainMedicalActID

								where mam.MedicalActID is not null or mae.MedicalActID is not null or mamek.MedicalActID is not null
						FOR XML PATH ('ZAP'),  TYPE
						)
					FROM [IES].T_MAIN_MEDICAL_ACT mma with(nolock)
					join ies.T_MEDICAL_REQUEST mr1 with(nolock) on mr1.MedicalRequestID = mma.MedicalRequest
					join ies.T_MEDICAL_EXPERTISE me1 with(nolock) on mr1.MedicalExpertise = me1.MedicalExpertiseID
					left join ies.T_F006_CONTROL_TYPE et with(nolock) on et.F006ControlTypeID = ISNULL(mma.F006ControlType, me1.F006ControlType)
					where --isnull(mma.R_23Order_year,'2019') = @year and mma.R_23Order_month = @month and 
						mma.DateConfirm is not null and ( mma.IsArchive=0 or mma.IsArchive is null)
					FOR XML PATH ('MED_ACT'), TYPE
					)
			FOR XML PATH (''), ROOT('MED_LIST')
			) as 'xml' 

			select * from @t1

			--Здесь пометка рег. поля, что выгрузился
			--update mma
			--set mma.isExport=1
			--FROM [IES].T_MAIN_MEDICAL_ACT mma with(nolock)
			--		join ies.T_MEDICAL_REQUEST mr1 with(nolock) on mr1.MedicalRequestID = mma.MedicalRequest
			--		join ies.T_MEDICAL_EXPERTISE me1 with(nolock) on mr1.MedicalExpertise = me1.MedicalExpertiseID
			--		left join ies.T_F006_CONTROL_TYPE et with(nolock) on et.F006ControlTypeID = ISNULL(mma.F006ControlType, me1.F006ControlType)
			--		where --isnull(mma.R_23Order_year,'2019') = @year and mma.R_23Order_month = @month and 
			--			mma.DateConfirm is not null and ( mma.IsArchive=0 or mma.IsArchive is null)
	end
GO