--增加分类
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=2033 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2033)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(2033, 77, 'Sql_医技特需检索', Null, '医技条形码检索、医技刷卡检索', 1, 0, Null, 1, 'DECLARE @lBCK01 int, @lSDate datetime, @lEDate datetime, @Type int, @lLike varchar(32), @RateMode int,@curdate date,@lbce01 int,@VBI13 int
SET @Type=%d  --0 未完成   1 已完成 
SET @lBCK01 = %d
SET @lSDate = ''%s''
SET @lEDate = ''%s''
SET @lEDate = @lEDate + 1
SET @lLike = %s
SET @RateMode = %d --0 条形码  >0刷卡 
SET @VBI13 = %d
set @lbce01 = %d
set @curdate = GETDATE()
if @RateMode=0
begin
  SELECT 
    CASE B.ACF01 WHEN 1 THEN A.VAA03 WHEN 2 THEN A.VAA04 END VAA_No
    , A.VAA01
    , B.VAA07
    , A.VAA05
    , A.ABW01
    , B.ACF01 VAF04
    , B.VBQ12 InDate,isnull(c.BCE01,0) BCE01,c.BCE03,c.VCZ11,c.VCZ17,A.VAA12,B.ACF01 
  FROM VAA1 A
  JOIN V_VBQ_FULL B ON A.VAA01=B.VAA01
  left join VCZ1 c on c.ACF01 = b.ACF01 and c.VAA07 = b.VAA07 and c.VCZ02 = @curdate and c.BCK01 = @lBCK01
  WHERE
    B.BCK01C=@lBCK01
    AND B.VBQ15=@lLike
    AND B.VBQ12>=@lSDate
    AND B.VBQ12<=@lEDate
end
else begin
  SELECT
    A.VAE02 VAF_No, 
    A.VAA01,  
    A.VAE01 VAA07, 
    B.VAA04 VAA_NO, 
    B.VAA05, 
    B.ABW01,
    2 VAF04,
    A.VAE11 InDate,isnull(c.BCE01,0) BCE01,c.BCE03,c.VCZ11,c.VCZ17,B.VAA12,2 ACF01
  FROM VAE1 A
  JOIN VAA1 B ON A.VAA01=B.VAA01
  left join VCZ1 c on c.ACF01 = 2 and c.VAA07 = a.VAE01 and c.VCZ02 = @curdate and c.BCK01 = @lBCK01
  WHERE
    a.VAA01=@lLike
    and exists(select * from V_VAJ_2 where vaj47 between @lSDate and @lEDate and bck01d=@lBCK01 and vaa07=a.vae01 and acf01=2 and VAJ53=@type)
  
  UNION ALL

  SELECT
    A.VAC02 VAF_No, 
    A.VAA01,  
    A.VAC01 VAA07, 
    B.VAA03 VAA_NO, 
    B.VAA05, 
    B.ABW01,
    1 VAF04,
    A.VAC35 InDate ,isnull(c.BCE01,0) BCE01,c.BCE03,c.VCZ11,c.VCZ17,B.VAA12,1 ACF01
  FROM VAC1 A
  JOIN VAA1 B ON A.VAA01=B.VAA01
  left join VCZ1 c on c.ACF01 = 1 and c.VAA07 = a.VAC01 and c.VCZ02 = @curdate and c.BCK01 = @lBCK01
  WHERE
    a.VAA01=@lLike
    and exists(select * from V_VAJ_1 where vaj47 between @lSDate and @lEDate and bck01d=@lBCK01 and vaa07=a.vac01 and acf01=1 and VAJ53=@type)
end	', '2017-03-09 11:03:14', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=2033 已经存在.'

GO