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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2035)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(2035, 77, 'Sql_医技病人列表费用', '医技病人列表费用', '医技病人列表，通过费用查询', 1, 0, '', 1, 'DECLARE 
   @lBCK01 varchar(16)
  ,@lSDate varchar(10)
  ,@lEDate varchar(10)
  ,@Type int
  ,@lLike varchar(32)
  ,@RateMode int
  ,@VBI13 int
  ,@IsName int
  ,@Sql varchar(4000),@curdate varchar(10),@lbce01 varchar(16)
  ,@CRMMode int

SET @Type=%d  --0 未完成   1 已完成 
SET @lBCK01 = cast(%d as varchar)
SET @lSDate = ''%s''
SET @lEDate = CONVERT(varchar(10), cast(''%s'' AS datetime)+1, 120)
SET @lLike = %s
SET @RateMode = %d
SET @VBI13 = %d
set @curdate = CONVERT(varchar(10), GETDATE(), 120)
SET @lbce01 = cast(%d as varchar)
set @CRMMode=dbo.GetSysParamValue(9999,9999,7)
IF Object_id(''tempdb..#tmpVAJ'') IS NOT NULL DROP TABLE #tmpVAJ
IF Object_id(''tempdb..#Tmpvaa'') IS NOT NULL DROP TABLE #Tmpvaa
select j.ACF01,VAA07,VAJ04,VAJ05
into #tmpvaj
from V_VAJ_1 j
join VAA1 a on j.VAA01=a.VAA01
where BCK01D = @lBCK01 and VAJ47 between @lSDate and @lEDate and (VAJ05 = 2 or (VAJ05=1 and ((@CRMMode>=1 and A.VBU01>0) or ACF01=2)))
  and ((@VBI13=1 and VAJ53=1)or(@VBI13<>1 and VAJ53 in (0,3))) 
union all
select ACF01,VAA07,VAJ04,VAJ05
from VAJT
where BCK01D = @lBCK01 and VAJ47 between @lSDate and @lEDate and @VBI13 <> 1
union all
select j.ACF01,VAA07,VAJ04,VAJ05
from V_VAJ_2 j
join VAA1 a on j.VAA01=a.VAA01
where BCK01D = @lBCK01 and VAJ47 between @lSDate and @lEDate and (VAJ05 = 2 or (VAJ05=1 and ((@CRMMode>=1 and A.VBU01>0) or ACF01=2)))
  and ((@VBI13=1 and VAJ53=1)or(@VBI13<>1 and VAJ53 in (0,3))) 

  select VAA01 into #tmpvaa
  from VAA1
  where (@lLike <> '''' and (VAA03 = @lLike or VAA04 = @lLike or VAA02 = @lLike or VAA05 = @lLike or ABBRP = @lLike) )
    or (@lLike = '''' and 1=2)
    
--AND BCK01D<>@lBCK01
SELECT A.VAA01, A.VAE01 VAA07, B.VAA05, B.ABW01, B.VAA04 VAA_NO, 2 VAF04, A.VAE11 InDate,isnull(c.BCE01,0) BCE01,c.BCE03,c.VCZ11,c.VCZ17,2 ACF01,B.VAA12
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.VAE44>=1 AND A.VAE44<=3 
  and exists(select * from #tmpvaj d where d.ACF01=2 and d.VAA07 = a.VAE01)
  and (@lLike='''' or (@lLike<>'''' and exists(select * from #tmpvaa e where e.VAA01 = b.VAA01)))
union all
SELECT A.VAA01, A.VAC01 VAA07, B.VAA05, B.ABW01, B.VAA03 VAA_NO, 1 VAF04, A.VAC35 InDate,isnull(c.BCE01,0) BCE01,c.BCE03,c.VCZ11,c.VCZ17,1 ACF01,B.VAA12
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.VAC34>=1 and /*划价的也显示出来，所以要注释掉*/
  exists(select * from #tmpVAJ d where d.VAA07 = a.VAC01 and d.ACF01 in (1,4))
  and (@lLike='''' or (@lLike<>'''' and exists(select * from #tmpvaa e where e.VAA01 = b.VAA01)))
  
IF Object_id(''tempdb..#tmpVAJ'') IS NOT NULL DROP TABLE #tmpVAJ
IF Object_id(''tempdb..#Tmpvaa'') IS NOT NULL DROP TABLE #Tmpvaa  ', '2017-03-09 11:15:44', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=2035 已经存在.'

GO