--增加分类
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


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=645)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(645, 32, 'Sel_RegisterA', Null, '医师工作站中检索候诊或转诊病人基本信息', 1, 1, Null, 1, 'DECLARE @lVAC51 int, @aDate Datetime, @aday int
declare @lPara varchar(20),@lBCE01 int,@lBCK01 int,@BAS02 varchar(20), @lPara2 varchar(20)
SET @lVAC51 = %d
set @aDay = %d
set @lPara = %s
set @lBCK01 = %d
set @lBCE01 = %d
set @BAS02  = %s

set @aDate = Getdate()  
set @lPara2 = dbo.GetSysParamValue(100,105002,238)  

if object_id(''tempdb..#tmpVAC'') is not null drop table #tmpVAC

select b.VAA01,b.VAC01,b.AAU01,b.BCK01A,b.UAA01,b.BCK01B,b.VAC36,b.VAA10,b.VAC02,b.VAC06,b.VAC10,b.BCE03A
  ,b.VAC42,b.BAK05,b.VAC51,b.BCE03C,b.ABC02,b.VAC34,b.VAC45,b.BAS02A,b.BDP02,a.BDP05
  ,ISNULL(c.VAD18,0) VAD18,isnull(c.VAD21,0) VAD21,isnull(c.VAD22,0) VAD22,isnull(c.ROWNR,0) VAD23,c.VAD17,c.VAD25
 into #tmpVAC
from VAC1 b with(nolock)
join BDP1 a on a.BDP02 = b.BDP02
left join VAD1 c on c.VAC01 = b.VAC01
where b.VAC09 <> -3 and b.VAC34 = 0 and b.VAC51 = @lVAC51 AND b.VAC45 = 1 and @aDate <= b.VAC72
  AND ((@lPara = ''05'' or (@lPara = ''06'' and (b.BCE01A = @lBCE01 or isnull(b.BCE01A,0)=0)))
     or (@lPara not in(''05'',''06'') and b.BCK01A = @lBCK01 
      and ((@lPara = ''03'')or(@lPara = ''01'' and (b.BCE01A = @lBCE01 or b.BCE01A=0))
      or(@lPara = ''02'' and b.BAS02A = @BAS02)or (@lPara = ''04'' and b.BCE01A = @lBCE01) ) ) )
  and ((@lPara2 <> ''1'') or (@lPara2 = ''1'' and b.BCK01A = @lBCK01) )   
  
SELECT a.VAA01,a.VAA02,a.VAA03,a.VAA05,a.VAA15,e.ABW02,f.AAU02, h.VCB04
  ,Agep = CASE WHEN isnull(b.VAA10,0)=0 THEN NULL else (ltrim(str(b.VAA10))+f.AAU02) END
  ,b.VAC01,b.VAC02,b.VAC06,b.VAC10,b.BCE03A,b.VAC36,b.VAC42,b.BAK05,isnull(b.VAC51,0) as VAC51
  ,b.BCE03C, c2.BCK03 AS BCK03B, c.BCK03, d.UAA03, d.UAA02, b.ABC02
  ,b.VAC34,b.VAC45,b.BAS02A,b.VAD18,b.VAD21,b.VAD22,b.VAD23,b.VAD17,b.VAD25,b.BDP02,b.BDP05
FROM VAA1 AS a with(nolock)
JOIN #tmpVAC AS b ON a.VAA01 = b.VAA01 
LEFT JOIN ABW1 AS e ON a.ABW01 = e.ABW01
LEFT JOIN AAU1 AS f ON b.AAU01 = f.AAU01
LEFT JOIN BCK1 AS c ON b.BCK01A = c.BCK01
LEFT JOIN UAA1 AS d ON b.UAA01 = d.UAA01
LEFT JOIN BCK1 AS c2 ON b.BCK01B = c2.BCK01
LEFT JOIN VBU1 h with(nolock) ON a.VBU01 = h.VBU01
Order By b.VAD18 desc,b.VAD17,b.VAD25,b.VAC36 DESC  

if object_id(''tempdb..#tmpVAC'') is not null drop table #tmpVAC', '2015-11-25 13:11:45', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=645 已经存在.'

GO