--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO


--删除旧记录
delete from SYS_Scripts where id=997 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=997)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(997, 51, 'SQL_MZPatientInfo', Null, '据据门诊号、姓名、五笔、拼音、身份证等进行检索病人信息', 1, 1, '<?xml version="1.0"?>
<Configs Ver="1"><Win WD="800" HG="400" SZ="1"/><Grid Head="1" AWS="1" FFUse="1"><Sql>Declare @FieldName varchar(20),
        @context varchar(128),
        @LastInfo varchar(10),
        @LastCRMBook varchar(10)

SET @FieldName = ''%s'' 
SET @context = ''%s''
IF @FieldName = ''a.VAA03''
BEGIN
  SELECT @context = dbo.GetFillVAA03(@context)
END

IF Object_id(''tempdb..#tmpPatInfo997'') IS NOT NULL DROP TABLE #tmpPatInfo997
IF Object_id(''tempdb..#tmpReg997'') IS NOT NULL DROP TABLE #tmpReg997

SELECT @LastInfo = value 
from sys_parameters with(nolock)
where ProductID =100 and ProgramID = 103005 and ParamNo =44

SELECT @LastCRMBook = value 
from sys_parameters with(nolock)
where ProductID =100 and ProgramID = 103005 and ParamNo =59

SET @LastInfo = ISNULL(@LastInfo , ''0'')
SET @LastCRMBook = ISNULL(@LastCRMBook , ''0'')

select a.* into #tmpPatInfo997
from VAA1 a with(nolock)
  left join VBU1 d with(nolock) on d.VBU01 = a.VBU01 
where %s = @context 

update #tmpPatInfo997 set BEP06B = 0 where BEP06B IS NULL
--优先取上次挂号记录中的病人类别，如果没有则取默认病人类别
declare @defBDP02 varchar(32)
--select @defBDP02=BDP02 from BDP1 where ISDEF=1

select a.VAA01 , a.VAC01 , a.BCB01A , a.BCK01A , a.BCE03A , a.VAC36,a.VAC68,a.BDP02 ,a.ABC02,c.SCA01,c.SCF01,c.SCF04 into #tmpReg997 
from VAC1 a with(nolock)
  join #tmpPatInfo997 b on b.VAA01 = a.VAA01 
  left join SCF1 c with(nolock) on a.SCF01=c.SCF01 and c.SCF11 &lt;= 2
where @LastInfo = ''1'' and a.VAC45 = 1 
union all
select a.VAA01, d.SCF01,d.BCE01A,d.BCK01,d.BCE03A ,d.SCF10,null,a.BDP02,null,a.SCA01,d.SCF01,d.SCF04 
from SCA1 a with(nolock)
 join #tmpPatInfo997 c on c.VAA01 = a.VAA01 
 join SCF1 d with(nolock) on d.SCA01 = a.SCA01 and d.SCF11 &lt;= 2
where @LastCRMBook =''1''

SELECT a.VAA01, a.VAA02, a.VAA03, a.VAA04, a.VAA05, a.VAA06, a.ABBRP, a.ABBRW, a.VAA14 ,a.VAA15, a.VAA16, a.VAA33, a.VAA34  , a.ABW01 ,
  a.VAA12 , a.VAA10 , a.AAU01 , a.ACK01 , ISNULL(g.BDP02,a.BDP02) BDP02, a.AAT02  , a.ACC02  , a.ABQ02 , ISNULL(e.ABC02 , a.ABC02) ABC02 , a.VAA35 , a.BDX02 , 
  d.VCB04 ,case when a.ABW01=''1'' then ''男'' when a.ABW01=''2'' then ''女'' else ''未知'' end ABW02 , a.VAA31 , a.VAA32
  ,  a.VBU01 , d.VBU14 , e.BCK01A , e.BCB01A , e.BCE03A , f.BCK03 , e.VAC36  , e.VAC01,e.VAC68 ,v.ATTRS,d.VBU08,a.VAA40 ,a.VAA41 ,a.VAA42,a.VAA43,
  a.BEP06B , d.VBU15 , d.VBU17 , d.VBU30,a.IAK05,e.BDP02,a.VAA47,a.VAA48,a.VAA49,a.VAA82,e.SCF01,e.SCA01,e.SCF04,a.VAA25
From #tmpPatInfo997 a with(nolock)
  LEFT JOIN VBU1 d with(nolock) on a.VBU01 = d.VBU01
  LEFT JOIN #tmpReg997 e on e.VAA01 = a.VAA01 
  LEFT join BCK1 f with(nolock) on f.BCK01 = e.BCK01A 
  left join BDP1 g with(nolock) on g.BDP02=e.BDP02 and g.ACF01 in(1,3)
  left join VFA1 v with(nolock) on v.VAA01=a.VAA01
where %s like @context and a.VAA01A = 0
order by e.VAC36 desc

IF Object_id(''tempdb..#tmpPatInfo997'') IS NOT NULL DROP TABLE #tmpPatInfo997
IF Object_id(''tempdb..#tmpReg997'') IS NOT NULL DROP TABLE #tmpReg997
</Sql><HD><Ie FD="VAA03" CA="门诊号" WD="60" SZ="1"/><Ie FD="VAA02" CA="卡号" WD="50" SZ="1"/><Ie FD="VAA05" CA="姓名" WD="50" SZ="1"/><Ie FD="ABW02" CA="性别" WD="20" SZ="1"/><Ie FD="VAA10" CA="年龄" WD="20" SZ="1"/><Ie FD="BDP02" CA="病人类别" WD="50" SZ="1"/><Ie FD="VAA12" CA="出生日期" WD="80" SZ="1"/><Ie FD="VAA15" CA="证件号" WD="60"/><Ie FD="BCK03" CA="就诊科室" WD="50" SZ="1"/><Ie FD="BCE03A" CA="就诊医生" WD="50" SZ="1"/><Ie FD="VAC36" CA="就诊日期" WD="60" SZ="1"/><Ie FD="VAA33" CA="地址" WD="100" SZ="1"/></HD></Grid><Tree FFUse="1"/></Configs>
', 1, 'Select a.VAA01, a.VAA02, a.VAA03, a.VAA04, a.VAA05, a.VAA06, a.ABBRP, a.ABBRW, a.VAA15,a.IAK05
 From VAA1 a with(nolock) 
Join 
(select a.VAA01, Max(b.VAC01) VAC01 
From VAA1 a with(nolock)
 Join VAC1 b with(nolock) On a.VAA01 = b.VAA01 Group by a.VAA01) ab
On a.VAA01 = ab.VAA01', '2019-08-13 16:07:15', '(8065)冯义强', 0, 0)
else
print 'SYS_Scripts.id=997 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 997)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=997
GO