Методы получения информации о пациенте

-- в цоде
select 
	pv.Value as [Value],
	p.Name as [GroupName],
	p.Code as [GroupCode]
from oms_ParamValue pv 
inner join oms_param p on pv.rf_ParamID = p.ParamID
inner join oms_ParamGroupParam PGP ON P.ParamID = pgp.rf_ParamID
inner join oms_ParamGroup pg on pgp.rf_ParamGroupID = pg.ParamGroupId
where pg.Guid = 'D32D131F-AFB3-4F47-914F-0EB9256AEEF1'
and pv.PatientGUID in (@0) -- мкабы пациента

--В цоде
select top 1
	case when mkab.RH = 0 then 1 
	when mkab.RH = 1 then 2
	else 0 end as [RH], -- Если 0, то Undefined
	gb.NAME as [BloodGroup] -- Если не получили, то Undefined
from hlt_mkab mkab with(nolock)
left join hlt_GroupOfBloodRH gb with(nolock) on mkab.rf_GroupOfBloodID = gb.GroupOfBloodRHID
where UGUID IN (@0) -- мкабы пациента


-- Получение RW и HIV
select
	rt.Code as [Code],
	rt.ResearchName,
	res.Conclusion as [Result] -- Если нашли то пишем A12.06.011 в RW, а A26.06.048 в HIV
from lbr_research res with(nolock)
join lbr_ResearchType rt with(nolock) on rt.UGUID = res.rf_ResearchTypeUGUID
join lbr_LaboratoryResearch lr with(nolock) on lr.GUID = res.rf_LaboratoryResearchGUID
where rt.Code in ('A26.06.048', 'A12.06.011')
and lr.rf_MKABID in (select mkabid from hlt_mkab with(nolock) where UGUID IN(@0)) -- мкабы пациента 
and res.isComplete = 1

-- в цоде
declare @@listOfIDs table (id int);
insert @@listOfIDs(id) select mkabid from hlt_mkab with(nolock) where UGUID IN(@0); -- мкабы пациента 
select
	mkb.name as [Name],
	mkb.DS as [Code]
from hlt_TAP t with(nolock)
join oms_kl_DiseaseType dt with(nolock) on t.rf_kl_DiseaseTypeID = dt.kl_DiseaseTypeID
join Oms_mkb mkb with(nolock) on t.rf_MKBID = mkb.mkbid
where t.rf_MKABID in (select id from @@listOfIDs)
and dt.code = '1'
and t.rf_MKBID != 0
union
select
	mkb.name as [Name],
	mkb.DS as [Code]
from  hlt_TAP t with(nolock)
join oms_kl_DiseaseType dt with(nolock) on t.rf_kl_DiseaseType2ID = dt.kl_DiseaseTypeID
join Oms_mkb mkb with(nolock) on t.rf_MKB2ID = mkb.mkbid
where t.rf_MKABID in (select id from @@listOfIDs)
and dt.code = '1'
and t.rf_MKB2ID != 0

-- в цоде
select
	so.Date as [DateBegin],
	so.DataEnd as [DateEnd],
	sm.ServiceMedicalCode as [Code],
	sm.ServiceMedicalName as [Name],
	op.Name as [TypeName],
	op.Code as [TypeCode]
from stt_MedicalHistory mh with(nolock)
join stt_SurgicalOperation so with(nolock) on so.rf_MedicalHistoryID = mh.MedicalHistoryID
left join oms_ServiceMedical sm with(nolock) on sm.ServiceMedicalID = so.rf_kl_ServiceMedicalID
left join stt_Operation op with(nolock) on op.OperationID = so.rf_OperationID
where mh.rf_mkabid in (select mkabid from hlt_mkab with(nolock) where UGUID IN(@0)) -- мкабы пациента