История страницы

Select convert(varchar(50), dir.GUID) as Guid, 
       isnull(dir.Number, '') as Number, 
       isnull(nomservice.CODE, '') as CodeNomservice,
       isnull(nomservice.NAME, '') as NameNomservice,
       isnull(convert(varchar(50),lpufrom.GUIDLPU), '00000000-0000-0000-0000-000000000000') as LpuSourceGuid,
       isnull(mkab.N_POL, '') as NumberPolis,
       isnull(mkab.S_POL, '') as SeriesPolis,
       isnull(mkb.DS, '') as CodeMkb,
	   reseachType.Code as CodeReseachType   
from lbr_LaboratoryResearch dir with (NOLOCK) 
inner join lbr_Research reseach with (NOLOCK) on reseach.rf_LaboratoryResearchGUID = dir.GUID
inner join lbr_ResearchType reseachType with (NOLOCK) on reseachType.UGUID = reseach.rf_ResearchTypeUGUID
inner join oms_kl_NomService nomservice with (NOLOCK) on nomservice.kl_NomServiceID = reseachType.rf_kl_NomServiceID
inner join hlt_MKAB mkab with (NOLOCK) on mkab.MKABID = dir.rf_MKABID
inner join oms_mkb mkb with (NOLOCK) on mkb.MkbId = dir.rf_MKBID
inner join oms_lpu lpufrom with (NOLOCK) on lpuFrom.LPUID = dir.rf_LPUSenderID
where dir.GUID = @0

if not exists (select * from lbr_LaboratoryResearch with (NOLOCK) where Number = '{4}')
begin 
insert into lbr_LaboratoryResearch(Date_Direction,	rf_MKABID, rf_mkbId, Number, Pat_Birthday,	
                                   Pat_Family, Pat_Name, Pat_Ot,
                                   Pat_S_POL, Pat_N_POL, rf_LPUSenderID, rf_LPUID, DateCreate)
values (convert(datetime,'{0}'),  
        isnull((Select top 1 MKABID from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
		isnull((Select MKBID from oms_mkb with (NOLOCK) where DS = '{3}'), 0),
		'{4}',
		isnull((Select top 1  convert(datetime, DATE_BD, 104) from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
		isnull((Select top 1 FAMILY from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
		isnull((Select top 1 NAME from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
		isnull((Select top 1 OT from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
        '{1}', 
        '{2}',
        isnull((Select top 1 LPUID from oms_lpu with (NOLOCK) where GUIDLPU = '{5}'), 0),
		isnull((Select top 1 LPUID from oms_lpu with (NOLOCK) where GUIDLPU = '{6}'), 0),
        GetDAte())

Select scope_identity()

declare @@researchTypeGuid uniqueidentifier, @@laboratoryResearchGuid uniqueidentifier
Select @@researchTypeGUID = UGuid from lbr_ResearchType with (NOLOCK) where code = '{7}'
Select @@laboratoryResearchGuid = GUID from lbr_LaboratoryResearch with (NOLOCK) where LaboratoryResearchId = scope_identity()

insert into lbr_Research(rf_LaboratoryResearchGuid, rf_ResearchTypeUGUID)
values (@@laboratoryResearchGuid, @@researchTypeGuid)
end

select top 1 LaboratoryResearchID from lbr_LaboratoryResearch where Number = '{4}'

Select convert(varchar(50), dir.GUID) as Guid,
        isnull(dir.Num, '')  as Number,
        isnull(prvs.C_PRVS, '') as CodeSpecialty,
        isnull(prvs.PRVS_NAME, '') as NameSpecialty,
        isnull(convert(varchar(50), lpufrom.GUIDLPU), '00000000-0000-0000-0000-000000000000') as lpuSourceGuid,
        isnull(mkab.N_POL, '') as NumberPolis,
        isnull(mkab.S_POL, '') as SeriesPolis,
        isnull(mkb.DS, '') as CodeMkb
from hlt_direction dir with (NOLOCK)
inner join hlt_Typedirection typ with (NOLOCK) on typ.TypeDirectionID = dir.rf_TypeDirectionID
inner join oms_PRVS prvs with (NOLOCK) on dir.rf_PRVSID = prvs.PRVSID
inner join oms_lpu lpufrom with (NOLOCK) on lpuFrom.LPUID = dir.rf_LPUSenderID
inner join hlt_LPUDoctor docFrom with (NOLOCK) on docFrom.LPUDoctorID = dir.rf_LPUDoctorID
inner join hlt_MKAB mkab with (NOLOCK) on mkab.MKABID = dir.rf_MKABID
inner join oms_mkb mkb with (NOLOCK) on mkb.MkbId = dir.rf_MKBID
inner join oms_lpu lputo with (NOLOCK) on lputo.LPUID = dir.rf_LPUID
where convert(varchar(50), dir.GUID) = @0

if not exists (select * from hlt_Direction with (NOLOCK) where Num = '{6}')
begin 
insert into hlt_Direction(Date,	rf_MKABID, rf_mkbID, rf_LPUID,	rf_TypeDirectionID,	
                            rf_LPUSenderID,	FAMILY,	NAME,	OT,	BD,	Address, 
						  S_POL,	N_POL, Num,	rf_PRVSID,	DatePlan)
 values (convert(datetime,'{0}'), 
	   isnull((Select top 1 MKABID from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
	   isnull((Select MKBID from oms_mkb with (NOLOCK) where DS = '{3}'), 0),
         isnull((Select top 1 LPUID from oms_lpu with (NOLOCK) where GUIDLPU = '{4}'), 0), 
	   3,
         isnull((Select top 1 LPUID from oms_lpu with (NOLOCK) where GUIDLPU = '{5}'), 0),
         isnull((Select top 1 FAMILY from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
         isnull((Select top 1 NAME from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
         isnull((Select top 1 OT from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
         isnull((Select top 1  convert(datetime, DATE_BD, 104) from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
         isnull((Select top 1 Adres from hlt_mkab with (NOLOCK) where S_POL = '{1}' and N_pol = '{2}'), 0),
         '{1}',
         '{2}',
         '{6}',
         isnull((Select top 1 PRVSID from oms_PRVS with (NOLOCK) where C_PRVS = '{7}'), 0),
         convert(datetime,'{0}'))
Select scope_identity()
end
else 
select DirectionID from hlt_Direction with (NOLOCK) where Num = '{6}'