if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Export263decree66Xml]') and xtype = N'P')
drop procedure [dbo].[sp_Export263decree66Xml]
GO

---- =============================================
---- Author:		Ramil Enaliev, (C) Vitacore 2014
---- Create date: 2014-07-30
---- Description:	Формирует xml файлы для выгрузки в ТФОМС66 по приказу №189
---- =============================================
--CREATE PROCEDURE [dbo].[sp_Export263decree66Xml]
--	@lpuMain uniqueidentifier, @date datetime
--AS 
--BEGIN

--SET NOCOUNT ON;

---- подготовка параметров
--declare @lpuCode char(4), @ddmmgg char(6), @nn char(2), @fname char(15)

--select @lpuCode = lm.Code from [AKUZ].T_LPU_MAIN lm where lm.LpuID = @lpuMain
--select @ddmmgg = REPLACE(CONVERT(char(8), @date, 4), '.', '')
--select @nn = '01' -- сделать генерацию
--select @fname = '1' + @lpuCode + '_' + @ddmmgg +'_' + @nn

--select * from
--(
---- первое идет имя архива, дальше порядок без разницы
--select 'm0_' + @fname + '.zip' as 'FileName', '' as 'Content', 0 as 'Order'

--union

---- «1» - направления на госпитализацию
--select 'm1_' + @fname + '.xml',
--'<?xml version="1.0" encoding="Windows-1251"?>'
--+
--(		select 
--		--referal
--		hr.ReferralNumber as 'REFERRAL_NUMBER'
--		,convert(varchar(10), ref.ReferralDate, 20) as 'REFERRAL_DATE'
--		,CASE hr.HospitalizationType
--			WHEN 13001 THEN 0
--			WHEN 13002 THEN 1
--			WHEN 13003 THEN 2
--			ELSE 0 END  as 'HOSPITALISATION_TYPE'
--		,lpT.LpuCode as 'MO_TO'
--		,brT.BranchCode as 'BRANCH_TO'
--		,dT.Code as 'DIVISION_TO'
--		,sb.Code as 'STRUCTURE_BED'
--		,lp.LpuCode as 'MO_FROM'
--		,br.BranchCode as 'BRANCH_FROM'
--		,ref.MkbCode as 'MKB'
--		,ref.Diagnosis as 'DIAGNOSIS'
--		,convert(varchar(10), hr.PreHospitalDate, 20) as 'PLANNED_DATE'
--		,p.Code as 'DOC_CODE'
--		--polis
--		,pol.PolicyForm as 'POLICY_TYPE'
--		,pol.Serial as 'POLIS_SERIAL'
--		,pol.Number as 'POLIS_NUMBER'
--		-- as 'SMO_REGION'
--		,i.SmoCod as 'SMO'
--		--patient
--		,pat.FirstName as 'FIRST_NAME'
--		,pat.LastName as 'LAST_NAME'
--		,pat.FatherName as 'FATHER_NAME'
--		,pat.Sex as 'SEX'
--		,convert(varchar(10), pat.Birthday, 20) as 'BIRTHDAY'
--		,hr.Phone as 'PHONE'
--		,pat.PatientID as 'PATIENT'
				
--		from [AKUZ].T_HOSPITALIZATION_REFERRAL hr 
--		join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = hr.StructureBed
--		join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
--		--to
--		join [AKUZ].T_LPU_MAIN lpT on lpT.LpuID = ref.Lpu
--		join [AKUZ].T_BRANCH brT on brT.LpuID = hr.Branch
--		join [AKUZ].T_DIVISION dT on dT.DivisionID = ref.Division
--		--from
--		join [AKUZ].T_MEDICAL_RECORD mr on mr.MedicalRecordID = ref.MedicalRecordID
--		join [AKUZ].T_POST p on p.ResourceID = mr.Post
--		join [AKUZ].T_RESOURCE r on r.ResourceID = p.ResourceID
--		join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
--		join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
--		join [AKUZ].T_LPU_MAIN lp on lp.LpuID = br.LpuMain
--		--patient
--		join [AKUZ].T_AMBULANCE_RECORD ar on ar.MedicalRecordID = hr.MedicalRecordID
--		join [AKUZ].T_EVENT_BASE eb on eb.EventID = ar.AmbulanceEvent
--		join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
--		join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
--		--polis
--		join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID, ref.ReferralDate)
--		join [AKUZ].T_INSURANCE_COMPANY ic on ic.OrganizationID = pol.InsuranceCompany
--		join [AKUZ].T_INSURER i on i.InsurerID = ic.Insurer
--		where ref.ReferralDate = @date and lp.LpuID = @lpuMain
--		FOR XML PATH('HOSPITALISATION_REFERRAL'),
--		ROOT('LPU')
--	), 1

--union

---- «2» - информация о госпитализациях
--select 'm2_' + @fname + '.xml',
--'<?xml version="1.0" encoding="Windows-1251"?>'
--+
--(		select 
--		--hosp
--		db.DirectionNumber 'REFERRAL_NUMBER'
--		,convert(varchar(10), db.DirectionDate, 20) 'REFERRAL_DATE'	
--		,lpDir.LpuCode 'REFERRAL_MO'	
--		,lp.LpuCode as 'MO'
--		,br.BranchCode as 'BRANCH'
--		,convert(varchar(10), mov.MovementDate, 20) as 'HOSPITALISATION_DATE'
--		,convert(varchar(20), mov.MovementTime, 20) as 'HOSPITALISATION_TIME'
--		--polis
--		,pol.PolicyForm as 'POLICY_TYPE'
--		,pol.Serial as 'POLIS_SERIAL'
--		,pol.Number as 'POLIS_NUMBER'
--		-- as 'SMO_REGION'
--		,i.SmoCod as 'SMO'
--		--patient
--		,pat.FirstName as 'FIRST_NAME'
--		,pat.LastName as 'LAST_NAME'
--		,pat.FatherName as 'FATHER_NAME'
--		,pat.Sex as 'SEX'
--		,convert(varchar(10), pat.Birthday, 20) as 'BIRTHDAY'
--		,sb.Code as 'STRUCTURE_BED'
--		,d.Code as 'DIVISION'
--		,mov.MedCardNumber as 'MED_CARD_NUMBER'
--		,diag.Mkb as 'MKB'
--		,diag.Diagnosis as 'DIAGNOSIS'
--		,pat.PatientID as 'PATIENT'
--		,CASE mdc.HospitalizationType
--			WHEN 13001 THEN 0
--			WHEN 13002 THEN 1
--			WHEN 13003 THEN 2
--			ELSE 0 END  as 'FORM_MEDICAL_CARE'
--		from [AKUZ].T_MOVEMENT mov 
--		join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov.StructureBed
--		--LPU
--		join [AKUZ].T_DIVISION d on d.DivisionID = mov.Division
--		join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
--		join [AKUZ].T_LPU_MAIN lp on lp.LpuID = br.LpuMain
--		--patient
--		join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
--		join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
--		join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
--		--polis
--		join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID, mov.MovementDate)
--		join [AKUZ].T_INSURANCE_COMPANY ic on ic.OrganizationID = pol.InsuranceCompany
--		join [AKUZ].T_INSURER i on i.InsurerID = ic.Insurer
--		--MKB
--		join [AKUZ].T_RECEPTION_DIAGNOSIS rd on rd.EmergencyEvent = mov.EmergencyEvent
--		join [AKUZ].T_DIAGNOSIS diag on diag.MedicalRecordID = rd.MedicalRecordID
--		--referral
--		left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
--		left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
--		left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
--		--
--		left join [AKUZ].T_MEDICALCARD_REGISTRATION mdc on mdc.MovementID = mov.MovementID
--		where mov.MovementDate = @date and lp.LpuID = @lpuMain

--		FOR XML PATH('HOSPITALISATION'),
--		ROOT('LPU')
--	), 2

--union

---- «3» - информация об отказах
--select 'm3_' + @fname + '.xml',
--'<?xml version="1.0" encoding="Windows-1251"?>'
--+
--(		select 
--           chr.[ReasonCancel] as 'REASON'--Причина отмены
--           ,chr.[CancellationSource] as 'CANCEL_SOURSE'--Источник аннулирования
--           ,p.PatientID as 'PATIENT'--
--           ,hr.ReferralNumber as 'REFERRAL_NUMBER'--Номер направления
--		   ,convert(varchar(10), ref.ReferralDate, 20) as 'DATE'
--		   ,lpFrom.LpuCode as 'LPU'
--           ,br.BranchCode as 'BRANCH'
--		   ,convert(varchar(10), chr.DateCancel, 20) as 'DATE_CANCEL'
--		   ,0 as 'CANCEL_TYPE'
--		   ,chr.Descr as 'CANCEL_DESCRIPTION'
--		from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
--		--[AKUZ].T_HOSPITALIZATION_REFERRAL
--		left join [AKUZ].T_HOSPITALIZATION_REFERRAL hr on  hr.MedicalRecordID = chr.HospitalizationReferral
--		left join [AKUZ].T_REFERRAL ref on ref.MedicalRecordID = hr.MedicalRecordID
--		left join [AKUZ].T_AMBULANCE_RECORD ar on ar.MedicalRecordID = hr.MedicalRecordID
--		left join [AKUZ].T_EVENT_BASE eb on eb.EventID = ar.AmbulanceEvent
--		left join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
--		left join [AKUZ].T_PATIENT p on p.PatientID = ac.Patient
--		left join [AKUZ].T_MEDICAL_RECORD mr on mr.MedicalRecordID = ref.MedicalRecordID
--		left join [AKUZ].T_POST post on post.ResourceID = mr.Post
--		left join [AKUZ].T_RESOURCE r on r.ResourceID = post.ResourceID
--		left join [AKUZ].T_DIVISION d on d.DivisionID = r.Division
--		left join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
--		left join [AKUZ].T_LPU_MAIN lpFrom on lpFrom.LpuID = br.LpuMain--REFERRAL_MO
--		where ref.ReferralDate = @date and lpFrom.LpuID = @lpuMain
--		FOR XML PATH('CANCEL_HOSPITALISATION_REFERRAL'),
--		ROOT('LPU')
--	), 3

--union

---- «4» - сводка движения пациентов и занятости коек
--select 'm4_' + @fname + '.xml',
--'<?xml version="1.0" encoding="Windows-1251"?>'
--+
--(		select		
--			lp.LpuCode as 'MO_IN'
--			,d.Code as 'PODR'
--			,sb.Code as 'PROFBERTH'
--			,msp.BedsCount - msp.RepairBeds as 'PLACE'--развернуто коек всего
--			,msp.ChildrenFreeBeds + msp.ChilrenOccupiedBeds as 'PLACECH'--развернуто коек , дети
--			,msp.CameFromHospitalWardMan as 'HOSPMAN' --госпитализировано за прошедшие сутки мужчин
--			,msp.CameFromHospitalWardWoman as 'HOSPW' --госпитализировано за прошедшие сутки ж
--			,msp.CameFromHospitalWardChildren as 'HOSPCH' --госпитализировано за прошедшие сутки д
--			,msp.OutcomeMan as 'RETAIRMAN'--выбыло за прошедшие сутки м
--			,msp.OutcomeWoman as 'RETAIRW'--выбыло за прошедшие сутки ж
--			,msp.OutcomeChildren as 'RETAIRCH'--выбыло за прошедшие сутки д
--			,msp.DayEnd as 'PLACEOCUP'--число больных на конец дня, те число занятых коек всего
--			,msp.ChilrenOccupiedBeds as 'PLACEOCUPCH' --число занятых коек, дети
--			,msp.PlannedBedsCount - msp.DayEnd as 'PLACERESERV'--число свободных коек, всего
--			,msp.ChildrenFreeBeds as 'PLACERESERVCH' --число свободных коек, дети
--			,(select count(*) 
--			from [AKUZ].T_HOSPITALIZATION_REFERRAL hr
--			join [AKUZ].T_REFERRAL r on r.MedicalRecordID = hr.MedicalRecordID
--			where r.ReferralDate > @date  AND hr.StructureBed = sb.StructureBedID and lp.LpuID = @lpuMain
--			) as 'TURN'--состоит в ло всего

--			,(select count(*) 
--			from [AKUZ].T_HOSPITALIZATION_REFERRAL hr
--			join [AKUZ].T_REFERRAL r on r.MedicalRecordID = hr.MedicalRecordID
--			where r.ReferralDate > @date  AND hr.StructureBed = sb.StructureBedID and (sb.StructureBedType = 2 or sb.StructureBedType = 3) and lp.LpuID = @lpuMain
--			) as 'TURNCH'--состоит в ло дети

--			,(select count(*) 
--			from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
--			join [AKUZ].T_HOSPITALIZATION_REFERRAL hr on chr.HospitalizationReferral = hr.MedicalRecordID
--			where chr.DateCancel = @date AND hr.StructureBed = sb.StructureBedID and lp.LpuID = @lpuMain
--			) as 'NOHOSP'--отказ всего

--			,(select count(*) 
--			from [AKUZ].T_CANCEL_HOSPITALIZATION_REFERRAL chr
--			join [AKUZ].T_HOSPITALIZATION_REFERRAL hr on chr.HospitalizationReferral = hr.MedicalRecordID
--			where chr.DateCancel = @date AND hr.StructureBed = sb.StructureBedID and (sb.StructureBedType = 2 or sb.StructureBedType = 3) and lp.LpuID = @lpuMain
--			) as 'NOHOSPCH'--отказ дети
--		from [AKUZ].T_MOVEMENTS_SEVEN_PROFILE msp
--		join [AKUZ].T_MOVEMENTS_SEVEN ms on ms.MovementsSevenID = msp.MovementsSeven
--		join [AKUZ].T_MOVEMENTS_LPU mlpu on mlpu.MovementsLpuID = ms.MovementsLpu
--		join [AKUZ].T_LPU_MAIN lp on lp.LpuID = mlpu.LpuMain

--		join [AKUZ].T_DIVISION d on d.DivisionID = ms.Division
--		join [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dlsb on dlsb.Division = d.DivisionID
--		join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = dlsb.StructureBed

--		where mlpu.PostingDate = @date and lp.LpuID = @lpuMain
--		FOR XML PATH('MOVEMENTS_LPU'),
--		ROOT('LPU')
--), 4
--) t
--where Content is not null
--order by 3

--end
--go
