if not exists(select * from syscolumns where name = 'VCJ56' and object_Name(id)='VCJ1')
  alter table VCJ1 add VCJ56 varchar(128)
--增加分类
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=2019 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2019)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(2019, 32, 'Sql_门诊首页病人信息', Null, '门诊首页病人基本信息', 1, 0, Null, 1, 'declare @VAA07 int,@vbn19 int
set @vaa07 = %d
set @vbn19 =  ISNULL((select MAX(VBN19) fvbn19 from VBN1 where VAA07 = @VAA07 and VBN12 >= 0),0)
select a.VAC01,a.VAA01,a.VAC75 VAA05,a.VAC74 VAA03,a.VAC78 VAA12,b.VAA25
,case when b.VAA57<''1911-01-01'' then null else b.VAA57 end VAA57
  ,a.VAA10,a.ABC02,b.ACC02,b.ABQ02,a.ACK01,a.VAC16,a.VAC77 VAA15
  ,b.VAA20,a.VAC27,a.VAC28,a.VAC29,b.VAA33,b.VAA34,b.VAA39,b.VAA38
  ,a.BAK05,a.VAC46, convert(varchar(10),isnull(a.VAC40,0)) VAC40 ,convert(varchar(10),isnull(a.VAC06,0)) VAC06,a.VAC76 ABW01,a.AAU01
  ,case when a.VAC41<''1911-01-01'' then null else a.VAC41 end VAC41
  ,convert(varchar(10),isnull(a.VAC62,0)) VAC62,convert(varchar(10),isnull(a.VAC63,0)) VAC63,a.VAC21,c.BCK01,c.VBN09,c.VBN10,c.VBN11
  ,c.VBN14,c.BCE03,d.BCK03,c.VBN04,c.VBN12,c.BCK01B,isnull(d1.BCK03,d2.BCK03) BCK03B
  ,case when VBN12 >= 1 then ''已入院'' else '''' end as  FState
  ,case when isnull(a.VAC68,'''') = '''' then dbo.GetAgeDetail(a.VAA10,a.AAU01) else a.VAC68 end as VAC68
  ,case when isnull(a.VAC68,'''') = '''' then CAST(isnull(a.VAA10,0) as varchar)+ISNULL(f.AAU02,'''') 
           else dbo.GetPatAge(a.VAC68,0,0,0) end as Agep
  ,g.VCJ08,b.VAA35 ,b.BDX02,convert(varchar(50),isnull(b.BEP06B,0)) BEP06B,g.VCJ16,g.ADX01,g.VCJ18,h.ADX02
  ,g.VCJ02,g.VCJ03,g.VCJ04,g.VCJ05,g.VCJ06,g.VCJ07,a.AAZ02,a.VAC23,a.VAC24,b.VAA44
  ,g.VCJ19,isnull(m.IAA01,0) IAA01,m.IAK05,a.VAC02,a.VAC36,n.ABL02,convert(varchar(10),g.VCJ20) VCJ20
  ,convert(varchar(10),isnull(g.VCJ21,0)) VCJ21,convert(varchar(10),isnull(g.VCJ22,0)) VCJ22,convert(varchar(10),isnull(g.VCJ23,0)) VCJ23,g.VCJ24,convert(varchar(10),isnull(g.VCJ33,0)) VCJ33,convert(varchar(10),isnull(g.VCJ34,0)) VCJ34,convert(varchar(10),isnull(g.VCJ35,0)) VCJ35,convert(varchar(10),isnull(g.VCJ36,0)) VCJ36
  ,case when g.VCJ25<''1911-01-01'' then null else g.VCJ25 end VCJ25
  ,case when g.VCJ29<''1911-01-01'' then null else g.VCJ29 end VCJ29
  ,case when g.VCJ30<''1911-01-01'' then null else g.VCJ30 end VCJ30  
  ,case when g.VCJ38<''1911-01-01'' then null else g.VCJ38 end VCJ38,convert(varchar(10),isnull(g.VCJ37,0)) VCJ37 ,g.VCJ44  
  ,c.VBN20,c.VBN21,g.VCJ56,a.BDP02 ,cast(null as image) VAA05img ,convert(varchar(10),isnull(g.VCJ66,0)) VCJ66,convert(varchar(10),isnull(g.VCJ67,0)) VCJ67
from VAC1 a with(nolock)
JOIN VAA1 b with(nolock) ON a.VAA01 = b.VAA01
left join VBN1 c with(nolock) on a.VAC01 = c.VAA07 and c.VBN12 >= 0 and c.VBN19 = @vbn19
left join BCK1 d with(nolock) on d.BCK01 = c.BCK01
LEFT JOIN AAU1 f with(nolock) ON f.AAU01 = a.AAU01 
left join VCJ1 g with(nolock) on g.VAA07 = a.VAC01
left join ADX1 h with(nolock) on h.ADX01 = g.ADX01
left join BCK1 d1 with(nolock) on d1.BCK01 = c.BCK01B
left join BCK1 d2 with(nolock) on d2.BCK01 = a.BCK01A
Left Join IAR1 m with(nolock) on m.IAR32 <> 2 and m.ACF01 =0 and m.VAA07 = a.vac01
left join ABL1 n with(nolock) on n.ABL01 = b.ABL01
join BDP1 g1 with(nolock) on g1.BDP02 = a.BDP02 
where a.VAC01 = @vaa07', '2020-01-09 09:53:48', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=2019 已经存在.'

GO


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