--do $$
--begin
--	if exists 
--	(
--		select * from pg_proc p
--		where p.proname = 'sp_export263decree66xml'
--	)
--	then
--		drop function public.sp_export263decree66xml(uuid, timestamp);
--	end if;
--end;
--$$ language plpgsql;
---- =============================================
---- Author:		Ramil Enaliev, (C) Vitacore 2014
---- Create date: 2014-07-30
---- Description:	Формирует xml файлы для выгрузки в ТФОМС66 по приказу №189
---- =============================================
--CREATE or REPLACE FUNCTION public.sp_Export263decree66Xml
--(
--	"LpuMain" UUID
--,	"Date" TimeStamp
--)
--RETURNS table (FileName text, "Content" text, "Order" int) AS
--$$
--Declare
--	lpuCodeVar char(4);
--	ddmmgg char(6);
--	nn char(2);
--	fname char(15);
--BEGIN
--	select lm.Code 
--	from akuz.T_LPU_MAIN lm 
--	into lpuCodeVar
--	where lm.LpuID = "LpuMain";
--	ddmmgg := to_char(current_timestamp::TIMESTAMP,'DDMMYY');
--	nn := '01'; -- сделать генерацию
--	fname := '1' || lpuCodeVar || '_' || ddmmgg || '_' || nn;

--	return query
--	select t.FileName, t."Content", t."Order"
--	from
--	(
--		-- первое идет имя архива, дальше порядок без разницы
--		select 'm0_' || fname || '.zip' as FileName, '' as "Content", 0 as "Order"
--		union all
--		-- «1» - направления на госпитализацию
--		select 'm1_' || fname || '.xml'
--		,	'<?xml version="1.0" encoding="Windows-1251"?>'
--		||
--		(		
--			select 
--			xmlelement
--			(
--				name "LPU"
--			,	xmlagg
--				(
--					xmlforest
--					(
--						xmlforest
--						(
--							--referal
--							hr.ReferralNumber as "REFERRAL_NUMBER"
--						,	to_char(ref.ReferralDate, 'YYYY-MM-DD') 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"
--						,	to_char(hr.PreHospitalDate, 'YYYY-MM-DD') as "PLANNED_DATE"
--						,	p.Code as "DOC_CODE"
--							--polis
--						,	pol.PolicyForm as "POLICY_TYPE"
--						,	pol.Serial as "POLIS_SERIAL"
--						,	pol.Number as "POLIS_NUMBER"
--						,	i.SmoCod as "SMO"
--							--patient
--						,	pat.FirstName as "FIRST_NAME"
--						,	pat.LastName as "LAST_NAME"
--						,	pat.FatherName as "FATHER_NAME"
--						,	pat.Sex as "SEX"
--						,	to_char(pat.Birthday, 'YYYY-MM-DD') as "BIRTHDAY"
--						,	hr.Phone as "PHONE"
--						,	pat.PatientID as "PATIENT"
--						) as "HOSPITALISATION_REFERRAL"
--					)
--				)
--			)		
--			from akuz.T_HOSPITALIZATION_REFERRAL hr 
--			inner join 
--				akuz.T_STRUCTURE_BED sb 
--				on sb.StructureBedID = hr.StructureBed
--			inner join 
--				akuz.T_REFERRAL ref 
--				on ref.MedicalRecordID = hr.MedicalRecordID
--			--to
--			inner join 
--				akuz.T_LPU_MAIN lpT 
--				on lpT.LpuID = ref.Lpu
--			inner join 
--				akuz.T_BRANCH brT 
--				on brT.LpuID = hr.Branch
--			inner join 
--				akuz.T_DIVISION dT 
--				on dT.DivisionID = ref.Division
--			--from
--			inner join 
--				akuz.T_MEDICAL_RECORD mr 
--				on mr.MedicalRecordID = ref.MedicalRecordID
--			inner join 
--				akuz.T_POST p 
--				on p.ResourceID = mr.Post
--			inner join 
--				akuz.T_RESOURCE r 
--				on r.ResourceID = p.ResourceID
--			inner join 
--				akuz.T_DIVISION d 
--				on d.DivisionID = r.Division
--			inner join 
--				akuz.T_BRANCH br 
--				on br.LpuID = d.Branch
--			inner join 
--				akuz.T_LPU_MAIN lp 
--				on lp.LpuID = br.LpuMain
--			--patient
--			inner join 
--				akuz.T_AMBULANCE_RECORD ar 
--				on ar.MedicalRecordID = hr.MedicalRecordID
--			inner join 
--				akuz.T_EVENT_BASE eb 
--				on eb.EventID = ar.AmbulanceEvent
--			inner join 
--				akuz.T_AMBULANCE_CARD ac 
--				on ac.AmbulanceCardID = eb.AmbulanceCard
--			inner join 
--				akuz.T_PATIENT pat 
--				on pat.PatientID = ac.Patient
--			--polis
--			inner join 
--				akuz.T_POLICY pol 
--				on pol.PolicyID = dbo.fn_GetOmsPolicy(pat.PatientID, ref.ReferralDate)
--			inner join 
--				akuz.T_INSURANCE_COMPANY ic 
--				on ic.OrganizationID = pol.InsuranceCompany
--			inner join 
--				akuz.T_INSURER i 
--				on i.InsurerID = ic.Insurer
--			where ref.ReferralDate = "Date"
--			and lp.LpuID = "LpuMain"
--		)
--		,	1
--		union all
--		-- «2» - информация о госпитализациях
--		select 'm2_' || fname || '.xml'
--		,	'<?xml version="1.0" encoding="Windows-1251"?>'
--		||
--		(		
--			select
--			xmlelement
--			(
--				name "LPU"
--			,	xmlagg
--				(
--					xmlforest
--					(
--						xmlforest
--						(
--							--hosp
--							db.DirectionNumber as  "REFERRAL_NUMBER"
--						,	to_char(db.DirectionDate, 'YYYY-MM-DD') as "REFERRAL_DATE'"
--						,	lpDir.LpuCode as "REFERRAL_MO"
--						,	lp.LpuCode as "MO"
--						,	br.BranchCode as "BRANCH"
--						,	to_char(mov.MovementDate, 'YYYY-MM-DD') as "HOSPITALISATION_DATE"
--						,	to_char(mov.MovementTime, 'YYYY-MM-DD HH24:MI:SS') as "HOSPITALISATION_TIME"
--							--polis
--						,	pol.PolicyForm as "POLICY_TYPE"
--						,	pol.Serial as "POLIS_SERIAL"
--						,	pol.Number as "POLIS_NUMBER"
--						,	i.SmoCod as "SMO"
--							--patient
--						,	pat.FirstName as "FIRST_NAME"
--						,	pat.LastName as "LAST_NAME"
--						,	pat.FatherName as "FATHER_NAME"
--						,	pat.Sex as "SEX"
--						,	to_char(pat.Birthday, 'YYYY-MM-DD') 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"
--						) as "HOSPITALISATION"
--					)
--				) 				
--			)
-- 			from akuz.T_MOVEMENT mov 
-- 			inner join 
--				akuz.T_STRUCTURE_BED sb 
--				on sb.StructureBedID = mov.StructureBed
-- 			--LPU
-- 			inner join 
--				akuz.T_DIVISION d 
--				on d.DivisionID = mov.Division
-- 			inner join 
--				akuz.T_BRANCH br 
--				on br.LpuID = d.Branch
-- 			inner join 
--				akuz.T_LPU_MAIN lp 
--				on lp.LpuID = br.LpuMain
-- 			--patient
-- 			inner join 
--				akuz.T_EVENT_BASE eb 
--				on eb.EventID = mov.EmergencyEvent
-- 			inner join 
--				akuz.T_AMBULANCE_CARD ac 
--				on ac.AmbulanceCardID = eb.AmbulanceCard
-- 			inner join 
--				akuz.T_PATIENT pat 
--				on pat.PatientID = ac.Patient
-- 			--polis
-- 			inner join 
--				akuz.T_POLICY pol 
--				on pol.PolicyID = dbo.fn_GetOmsPolicy(pat.PatientID, mov.MovementDate)
-- 			inner join 
--				akuz.T_INSURANCE_COMPANY ic 
--				on ic.OrganizationID = pol.InsuranceCompany
-- 			inner join 
--				akuz.T_INSURER i 
--				on i.InsurerID = ic.Insurer
-- 			----MKB
-- 			inner join 
--				akuz.T_RECEPTION_DIAGNOSIS rd 
--				on rd.EmergencyEvent = mov.EmergencyEvent
-- 			inner 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.Lpu
-- 			--
-- 			left join 
--				akuz.T_MEDICALCARD_REGISTRATION mdc 
--				on mdc.MovementID = mov.MovementID
-- 			where mov.MovementDate = "Date"
--			and lp.LpuID = "LpuMain"
-- 		)
-- 		,	2
--		union all
--		-- «3» - информация об отказах
--		select 'm3_' || fname || '.xml'
--		,	'<?xml version="1.0" encoding="Windows-1251"?>'
--		||
--		(		
--			select
--			xmlelement
--			(
--				name "LPU"
--			,	xmlagg
--				(
--					xmlforest
--					(
--						xmlforest
--						(
--							chr.ReasonCancel as "REASON"
--						,	chr.CancellationSource as "CANCEL_SOURSE"
--						,	p.PatientID as "PATIENT"
--						,	hr.ReferralNumber as "REFERRAL_NUMBER"
--						,	to_char(ref.ReferralDate, 'YYYY-MM-DD') as "DATE"
--						,	lpFrom.LpuCode as "LPU"
--						,	br.BranchCode as "BRANCH"
--						,	to_char(chr.DateCancel, 'YYYY-MM-DD') as "DATE_CANCEL"
--						,	0 as "CANCEL_TYPE"
--						,	chr.Descr as "CANCEL_DESCRIPTION"
--						) as "CANCEL_HOSPITALISATION_REFERRAL"
--					)
--				)
--			)
-- 			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"
-- 		)
-- 		,	3
--		union all
--		-- «4» - сводка движения пациентов и занятости коек
--		select 'm3_' || fname || '.xml'
--		,	'<?xml version="1.0" encoding="Windows-1251"?>' || '<!--TODO-->'
--		,	4
--	) t
--	where t."Content" is not null
--	order by t."Order";
--END;
--$$ LANGUAGE plpgsql stable;