
CREATE PROCEDURE [dbo].[sp_GetSchetFFOMSD1XmlMain] (
	  @SchetID UNIQUEIDENTIFIER
	, @SessionId UNIQUEIDENTIFIER
	, @WeekNumber INT
	, @Year INT
	, @Month INT
	, @DateFrom varchar(100)
	, @DateTo varchar(100)
	, @TFOMScode varchar(2)
	)
AS
BEGIN

	PRINT 'var declare'

	DECLARE @D1ExportID UNIQUEIDENTIFIER
		, @id UNIQUEIDENTIFIER = newid()
		, @SchetCount INT
		, @NewLine VARCHAR(2) = CHAR(10) + CHAR(13)
		, @Msg VARCHAR(300)
		, @FileNumber INT
		, @MainFileName VARCHAR(100)
		, @PersFileName VARCHAR(100)
		, @CODE_MO VARCHAR(6)
		, @OldFileName varchar(100)
		, @FileNamePrefix varchar(2)
		, @SchetYear int
		, @SchetMonth int

	SET @year = nullif(@year, 0)
	SET @month = nullif(@month, 0)
	SET @msg = @newline

	PRINT 'check vars'

	IF @WeekNumber = 0
	BEGIN
		SET @msg += 'Не заполнен номер недели' + @NewLine

		RAISERROR (
				@msg
				, 18
				, 1
				)

		RETURN
	END

	PRINT 'Get vars from schet'

	SELECT @CODE_MO = t0.CODE_MO
		,@OldFileName = t2.Name
		,@SchetYear = t0.[Year]
		,@SchetMonth = t0.[Month]
	FROM ies.t_schet t0
	JOIN ies.t_schet_file t1 ON t0.schetid = t1.schet
	JOIN vclib.t_file t2 ON t2.fileid = t1.fileid
	WHERE t0.SchetID = @SchetID

	set @FileNamePrefix = substring(@OldFileName,1,2)

	PRINT 'get disp export id'
	
	SELECT @D1ExportId = D1ExportId
		, @SchetCount = ExportCount
		, @FileNumber = Number
	FROM ies.T_D1_EXPORT
	WHERE WeekNumber = @WeekNumber
		AND SessionId = @SessionId
		AND Number != 0
		AND isnull(ExportCount, 0) != 0

	PRINT 'check if first file'

	IF @D1ExportId IS NULL --first file detected
	BEGIN
		SET @D1ExportId = newid()
		SET @SchetCount = (
				SELECT count(*)
				FROM IES.T_SCHET t0
				LEFT JOIN IES.T_SCHET_FILE t1 ON t1.Schet = t0.SchetID
				LEFT JOIN VCLib.T_FILE t2 ON t2.FileID = t1.FileID
				WHERE (
						(
							t0.[YEAR] = @Year
							OR @Year IS NULL
							)
						AND (
							t0.[MONTH] = @Month
							OR @Month IS NULL
							)
						AND NOT t2.Name IS NULL
						AND t2.Name LIKE 'HW%'
						AND (
							(t0.type_) in(select top 1 EntityID from vclib.T_ENTITIES where entname = 'COVID_SCHET')
							)
						AND t0.ReceivedDate >= @DateFrom
						AND t0.ReceivedDate < dateadd(Day, 1, @DateTo)
						AND t0.IsDelete = 0
						)
				)

		INSERT INTO ies.T_D1_EXPORT
		VALUES (
			@D1ExportId
			, @SessionId
			, @WeekNumber
			, 0
			, @SchetCount
			)

		SET @FileNumber = 1 
	END

	PRINT 'set filenames'

	SET @MainFileName = @FileNamePrefix + 'M' + @CODE_MO + 'T'+ @TFOMScode +'_' +  convert(varchar(2),@SchetYear-2000) +  right('0'+convert(varchar(2),@SchetMonth),2) + convert(varchar(10),@WeekNumber) + convert(varchar(10),@FileNumber)
	SET @PersFileName = 'L' + right(@MainFileName,len(@MainFileName)-1)

	PRINT 'export this file'

	--SELECT convert(VARCHAR(36), @id) + '-' + convert(VARCHAR(100), @FileNumber) + '.xml'
	--	, '<a></a>'

	PRINT 'raise count'

	UPDATE ies.T_D1_EXPORT
	SET Number += 1
	WHERE D1ExportId = @D1ExportID

	PRINT 'start export file'

	SELECT @MainFileName + '.xml'
		, '<?xml version="1.0" encoding="Windows-1251"?>
' + (
			SELECT
				--ZGLV
				'3.2' AS 'ZGLV/VERSION'
				, CONVERT(VARCHAR(10), t1.DATA, 126) AS 'ZGLV/DATA'
				, @MainFileName AS 'ZGLV/FILENAME'
				, t1.SD_Z 'ZGLV/SD_Z'
				--SCHET
				, t1.CODE AS 'SCHET/CODE'
				, t1.CODE_MO AS 'SCHET/CODE_MO'
				, t1.YEAR AS 'SCHET/YEAR'
				, right(convert(VARCHAR, t1.MONTH + 100), 2) AS 'SCHET/MONTH'
				, t1.NSCHET AS 'SCHET/NSCHET'
				, CONVERT(VARCHAR(10), t1.DSCHET, 126) AS 'SCHET/DSCHET'
				, t1.PLAT AS 'SCHET/PLAT'
				, t1.SUMMAV AS 'SCHET/SUMMAV'
				, t1.COMENTS AS 'SCHET/COMENTS'
				, t1.SUMMAP AS 'SCHET/SUMMAP'
				, CASE WHEN t1.SANK_MEK = 0 THEN NULL ELSE t1.SANK_MEK END AS 'SCHET/SANK_MEK'
				, CASE WHEN t1.SANK_MEE = 0 THEN NULL ELSE t1.SANK_MEE END AS 'SCHET/SANK_MEE'
				, CASE WHEN t1.SANK_EKMP = 0 THEN NULL ELSE t1.SANK_EKMP END AS 'SCHET/SANK_EKMP'
				, (
					SELECT d.IDDT
					FROM [IES].T_V016_DISPT d
					WHERE d.V016ID = t1.DISP
					) AS 'SCHET/DISP'
				--ZAP
				, (
					SELECT t2.N_ZAP AS 'N_ZAP'
						, t2.PR_NOV AS 'PR_NOV'
						-- PACIENT
						, t2.ID_PAC AS 'PACIENT/ID_PAC'
						, t2.VPOLIS AS 'PACIENT/VPOLIS'
						, t2.SPOLIS AS 'PACIENT/SPOLIS'
						, t2.NPOLIS AS 'PACIENT/NPOLIS'
						, t2.ENP AS 'PACIENT/ENP'
						, t2.ST_OKATO AS 'PACIENT/ST_OKATO'
						, t2.SMO AS 'PACIENT/SMO'
						--, t2.SMO_OGRN AS 'PACIENT/SMO_OGRN'
						--, t2.SMO_OK AS 'PACIENT/SMO_OK'
						, t2.SMO_NAM AS 'PACIENT/SMO_NAM'
						, t2.INV AS 'PACIENT/INV'
						, t2.MSE as 'PACIENT/MSE'
						, t2.NOVOR AS 'PACIENT/NOVOR'
						,t2.VNOV_D AS 'PACIENT/VNOV_D'  
						--Z_SLUCH
						, (
							SELECT ssa.IDCASE as 'IDCASE'
								,ssa.USL_OK AS 'USL_OK'
								,ssa.VIDPOM AS 'VIDPOM'
								,ssa.FOR_POM AS 'FOR_POM'
								,ssa.NPR_MO AS 'NPR_MO'
								,CONVERT(VARCHAR(10),ssa.NPR_DATE,126) AS 'NPR_DATE'
								,ssa.LPU AS 'LPU'
								,ssa.VBR AS 'VBR'
								,CONVERT(VARCHAR(10),ssa.DATE_Z_1,126) AS 'DATE_Z_1'
								,CONVERT(VARCHAR(10),ssa.DATE_Z_2,126) AS 'DATE_Z_2'
								,ssa.P_OTK as 'P_OTK'
								,ssa.KD_Z AS 'KD_Z'
								,ssa.VNOV_M AS 'VNOV_M'
								,ssa.RSLT AS 'RSLT'
								,ssa.RSLT_D AS 'RSLT_D'
								,ssa.ISHOD AS 'ISHOD'
								,case when ssa.OS_SLUCH='' then null else ssa.OS_SLUCH end AS 'OS_SLUCH'
								,ssa.VB_P AS 'VB_P'
								, (
									SELECT t3.SL_ID as 'SL_ID'
										,t3.VID_HMP AS 'VID_HMP'
										,t3.METOD_HMP AS 'METOD_HMP'
										,t3.LPU_1 AS 'LPU_1'
										,t3.PODR AS 'PODR'
										,t3.PROFIL AS 'PROFIL'
										,(select top 1 v020.idk_pr from ies.t_v020_bed_profile v020 where t3.profil_k = v020.v020bedprofileid) AS 'PROFIL_K'
										,t3.DET AS 'DET'
										,CONVERT(VARCHAR(10),t3.TAL_D,126) AS 'TAL_D'
										,t3.TAL_NUM AS 'TAL_NUM'
										,CONVERT(VARCHAR(10),t3.TAL_P,126) AS 'TAL_P'
										,(select top 1 v025.idpc from ies.t_v025_kpc v025 where v025.v025kpcid = t3.p_cel) as 'P_CEL'
										,t3.NHISTORY AS 'NHISTORY'
										,t3.P_PER AS 'P_PER'
										,CONVERT(VARCHAR(10),t3.DATE_1,126) AS 'DATE_1'
										,CONVERT(VARCHAR(10),t3.DATE_2,126) AS 'DATE_2'
										,t3.KD AS 'KD'
										,t3.DS0 AS 'DS0'
										,t3.DS1 AS 'DS1'
										,t3.DS1_PR AS 'DS1_PR'
										,(
											SELECT t4.MKB AS 'DS2'
											FROM [IES].T_SCHET_SLUCH_DS t4
											WHERE t4.SchetSluch = t3.SchetSluchID and t4.MKBType = 0 and t4.pr_ds2_n is null
											FOR
											XML PATH(''),
											TYPE
										)
										,( 
											SELECT t4.MKB	AS 'DS3'
											FROM [IES].T_SCHET_SLUCH_DS t4
											WHERE t4.SchetSluch = t3.SchetSluchID and t4.MKBType = 1
											FOR
											XML PATH(''),
											TYPE
										)
										,(select top 1 v027.idcz from ies.t_v027_c_zab v027 where v027.v027czabid = t3.c_zab) as 'C_ZAB'
										,t3.DS_ONK as 'DS_ONK'
										,t3.PR_D_N AS 'PR_D_N'
										,t3.DN as 'DN'
										,t3.CODE_MES1 AS 'CODE_MES1'
										,t3.CODE_MES2 AS 'CODE_MES2'
										,( 
											SELECT t4.MKB AS 'DS2'
											, t4.DS2_PR as 'DS2_PR'
											, t4.PR_DS2_N as 'PR_DS2_N'
											FROM [IES].T_SCHET_SLUCH_DS t4
											WHERE t4.SchetSluch = t3.SchetSluchID and t4.MKBType = 0  and t4.pr_ds2_n is not null
											FOR
											XML PATH('DS2_N'),
											TYPE
										)
										,t3.REAB as 'REAB'
										,(select v021.code_spec from ies.t_v021_med_spec v021 where v021.idspec =t3.prvs3) as 'PRVS'                                               
										,t3.VERS_SPEC as 'VERS_SPEC'
										,t3.IDDOKT AS 'IDDOKT'
										,t3.ED_COL AS 'ED_COL'
										,t3.TARIF AS 'TARIF'
										,t3.SUMV as 'SUM_M'
										,(
											SELECT CONVERT(VARCHAR(10), md.DATA_INJ, 126) AS 'DATA_INJ'
												,(
												SELECT TOP 1 v024.ScheDrugGrCd
												FROM ies.T_V032_DRUG_GR_REG v024
												WHERE v024.V032DrugGrRegID = md.CODE_SH
												) AS 'CODE_SH'
												,(SELECT TOP 1 ID_LEKP
												FROM [IES].[T_N020_ONK_LEKP]
												WHERE N020OnkLekpID = [REGNUM]
												) AS 'REGNUM'
												,md.COD_MARK AS 'COD_MARK'
												,(select (select top 1 v034.UnitCode from [IES].[T_V034_UNIT_TYPE] v034 where md.ED_IZM = v034.V034UnitTypeID) as 'ED_IZM'
														,md.DOSE_INJ as 'DOSE_INJ'
														,(select top 1 v035.MethCode from [IES].[T_V035_MET_INTRO] v035 where md.[METHOD_INJ] = v035.V035MetIntroID) as 'METHOD_INJ'
														,md.COL_INJ AS 'COL_INJ'
													FOR XML PATH('LEK_DOSE')
													,TYPE
												)
											FROM [IES].T_SCHET_SLUCH_LEK_PR md WITH (NOLOCK)
											WHERE md.SchetSluch = t3.SchetSluchID
											FOR XML PATH('LEK_PR')
											,TYPE
										)
										, (
											SELECT t4.IDSERV AS 'IDSERV'
												,t4.LPU AS 'LPU'
												,t4.LPU_1 AS 'LPU_1'
												,t4.PODR AS 'PODR'
												,t4.PROFIL AS 'PROFIL'
												,t4.VID_VME AS 'VID_VME'
												,t4.DET AS 'DET'
												,CONVERT(VARCHAR(10),t4.DATE_IN,126) AS 'DATE_IN'
												,CONVERT(VARCHAR(10),t4.DATE_OUT,126) AS 'DATE_OUT'
												,t4.DS AS 'DS'
												,t4.P_OTK AS 'P_OTK'
												,t4.CODE_USL AS 'CODE_USL'
												,t4.KOL_USL AS 'KOL_USL'
												,t4.TARIF AS 'TARIF'
												,t4.SUMV_USL AS 'SUMV_USL'
												,(
													SELECT CONVERT(VARCHAR(10), md.DATE_MED, 126) AS 'DATE_MED'
														,md.CODE_MEDDEV AS 'CODE_MEDDEV'
														,md.NUMBER_SER AS 'NUMBER_SER'
													FROM [IES].T_SCHET_USL_MED_DEV md WITH (NOLOCK)
													WHERE md.SchetUsl = t4.SchetUslID
													FOR XML PATH('MED_DEV')
													,TYPE
												)
												,(
													  SELECT mr.MR_N AS 'MR_N'
													 ,(select top 1 v021.code_spec from ies.t_v021_med_spec v021 where v021.idspec =mr.prvs) as 'PRVS' 
													 ,mr.CODE_MD AS 'CODE_MD'
													FROM [IES].T_MR_USL_N mr with(nolock) 		
													WHERE mr.SchetUsl = t4.SchetUslID
													ORDER BY mr.MR_N
			
													FOR
													XML PATH('MR_USL_N'),
													TYPE
												)
												,t4.NPL AS 'NPL'
												,t4.COMENTU AS 'COMENTU'
											FROM [IES].T_SCHET_USL t4 WITH (NOLOCK)
											WHERE t4.SchetSluch = t3.SchetSluchID
											ORDER BY t4.IDSERV
											FOR XML PATH('USL')
												, TYPE
											)
										, t3.COMENTSL AS 'COMENTSL'
									FROM [IES].T_SCHET_SLUCH t3
									WHERE ssa.SchetSluchAccomplishedID = t3.SchetSluchAccomplished
										AND t3.isdelete = 0
									ORDER BY t3.DATE_1
										, DATE_2
									FOR XML PATH('SL')
										, TYPE
									)
								, ssa.IDSP AS 'IDSP'
								, ssa.SUMV AS 'SUMV'
								, ssa.OPLATA AS 'OPLATA'
								, ssa.SUMP AS 'SUMP'
								, (
									SELECT doubled.ExpertiseErrorID AS 'S_CODE'
										, CASE WHEN doubled.id > 1 THEN 0 ELSE ssa.sank_mek END AS 'S_SUM'
										, 1 AS 'S_TIP'
										, (
											SELECT ss.SL_ID AS 'SL_ID'
											FROM ies.T_SCHET_SLUCH ss
											WHERE ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID -- and ss.SchetSluchID = t5.SchetSluch
											FOR XML PATH('')
												, TYPE
											)
										, isnull((
												SELECT TOP 1 f014.Kod
												FROM ies.T_F014_DENY_REASON f014
												WHERE f014.F014DenyReasonID = doubled.F014DenyReason
												), 53) AS 'S_OSN'
										, CONVERT(VARCHAR(10), doubled.createdate, 126) AS 'DATE_ACT'
										, doubled.Number AS 'NUM_ACT'
										, doubled.Name AS 'S_COM'
										, 1 AS 'S_IST'
									FROM (
										SELECT t6.ExpertiseErrorID
											, t6.F014DenyReason
											, t6.FOMScode
											, t6.Name
											, sa.createdate
											, sa.number
											, ROW_NUMBER() OVER (
												PARTITION BY t6.ExpertiseErrorID
												, t6.FOMScode
												, t6.Name ORDER BY t6.FOMScode
												) AS [ids]
											, ROW_NUMBER() OVER (
												PARTITION BY t5.SchetSluchAccomplished ORDER BY t6.FOMScode
												) AS [id]
											, t5.SchetSluchAccomplished
										FROM [IES].T_ACT_ITEM_ERROR t4 WITH (NOLOCK)
										JOIN [IES].T_SCHET_ACT_ITEM t5 WITH (NOLOCK) ON t5.SchetActItemID = t4.SchetActItem
										JOIN ies.T_SCHET_ACT sa WITH (NOLOCK) ON t5.schetact = sa.schetactid
										JOIN [IES].T_EXPERTISE_ERROR t6 WITH (NOLOCK) ON t6.ExpertiseErrorID = t4.ExpertiseError
										WHERE t5.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
											AND t5.IsAddToAct = 1
										GROUP BY t6.ExpertiseErrorID
											, t6.F014DenyReason
											, t6.FOMScode
											, t6.Name
											, t5.SchetSluchAccomplished
											, sa.createdate
											, sa.number
										) doubled
									WHERE doubled.Ids = 1
									FOR XML PATH('SANK')
										, TYPE
									)
								, (
									--sank
									SELECT t5.S_CODE AS 'S_CODE'
										, t5.S_SUM AS 'S_SUM'
										, t5.S_TIP AS 'S_TIP'
										, (
											SELECT ss.SL_ID AS 'SL_ID'
											FROM ies.T_SCHET_SLUCH ss
											WHERE t5.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
												AND ss.SchetSluchID = t5.SchetSluch
											FOR XML PATH('')
												, TYPE
											)
										, (
											SELECT TOP 1 f014.kod
											FROM ies.t_f014_deny_reason f014
											WHERE f014.f014denyreasonid = t5.s_osn
												AND f014.datebeg <= ssa.date_z_2
												AND (
													f014.dateend IS NULL
													OR f014.dateend >= ssa.date_z_2
													)
											) AS 'S_OSN'
										, t5.DATE_ACT AS 'DATE_ACT'
										, t5.NUM_ACT AS 'NUM_ACT'
										, (
											SELECT ssse.F004Expert AS 'CODE_EXP'
											FROM ies.T_SCHET_SLUCH_SANK_EXP ssse WITH (NOLOCK)
											WHERE ssse.SchetSluchSank = t5.SchetSluchSankID
											FOR XML PATH('')
												, TYPE
											)
										, t5.S_COM AS 'S_COM'
										, t5.S_IST AS 'S_IST'
									FROM [IES].T_SCHET_SLUCH_SANK t5 WITH (NOLOCK)
									WHERE t5.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
									FOR XML PATH('SANK')
										, TYPE
									)
								, CASE WHEN ssa.SUMV - ssa.SUMP <> 0 THEN ssa.SUMV - ssa.SUMP ELSE NULL END AS 'SANK_IT'
							FROM [IES].[T_SCHET_SLUCH_ACCOMPLISHED] ssa
							WHERE ssa.SchetZap = t2.SchetZapID
							FOR XML PATH('Z_SL')
								, TYPE
							)
					FROM [IES].T_SCHET_ZAP t2
					JOIN [IES].T_SCHET s ON (s.SchetID = t2.Schet)
					WHERE t2.Schet = t1.SchetID
					FOR XML PATH('ZAP')
						, TYPE
					)
			FROM [IES].T_SCHET t1
			WHERE t1.SChetID = @SchetID
			FOR XML PATH('')
				, ROOT('ZL_LIST')
			)
	
	UNION
	
	SELECT @PersFileName + '.xml'
		, '<?xml version="1.0" encoding="Windows-1251"?>'
		+ replace(replace(
		(
			SELECT
				--ZGLV
				'3.2' AS 'ZGLV/VERSION'
				, CONVERT(VARCHAR(10), t1.DATA, 126) AS 'ZGLV/DATA'
				, @PersFileName AS 'ZGLV/FILENAME'
				, @MainFileName AS 'ZGLV/FILENAME1'
				--PERS
				, (
					SELECT DISTINCT t2.ID_PAC AS 'ID_PAC'
						, t2.FAM AS 'FAM'
						, t2.IM AS 'IM'
						, t2.OT AS 'OT'
						, t2.W AS 'W'
						, CONVERT(VARCHAR(10), t2.DR, 126) AS 'DR'
						, cast((
								SELECT Split AS 'DOST'
								FROM [dbo].fn_Split(t2.DOST, ',')
								FOR XML PATH('')
									, TYPE
								) AS VARCHAR(max))
						, t2.TEL AS 'TEL'
						, t2.FAM_P AS 'FAM_P'
						, t2.IM_P AS 'IM_P'
						, t2.OT_P AS 'OT_P'
						, t2.W_P AS 'W_P'
						, CONVERT(VARCHAR(10), t2.DR_P, 126) AS 'DR_P'
						, cast((
								SELECT Split AS 'DOST_P'
								FROM [dbo].fn_Split(t2.DOST_P, ',')
								FOR XML PATH('')
									, TYPE
								) AS VARCHAR(max))
						, t2.MR AS 'MR'
						, t2.DOCTYPE AS 'DOCTYPE'
						, t2.DOCSER AS 'DOCSER'
						, t2.DOCNUM AS 'DOCNUM'
						, CONVERT(VARCHAR(10), t2.DOCDATE, 126) AS 'DOCDATE'
						, t2.DOCORG AS 'DOCORG'
						, t2.SNILS AS 'SNILS'
						, t2.OKATOG AS 'OKATOG'
						, t2.OKATOP AS 'OKATOP'
						, t2.COMENTP AS 'COMENTP'
					FROM [IES].T_SCHET_ZAP t2
					WHERE t2.Schet = t1.SchetID
					FOR XML PATH('PERS')
						, TYPE
					)
			FROM [IES].T_SCHET t1
			WHERE t1.SChetID = @SchetID
			FOR XML PATH('')
				, ROOT('PERS_LIST')
			),'&lt;','<'),'&gt;', '>')

	PRINT 'end export file'
	PRINT 'delete if last'

	IF @FileNumber + 1 = @SchetCount
	BEGIN
		DELETE
		FROM ies.T_D1_EXPORT
		WHERE D1ExportId = @D1ExportID
	END
END
