--增加分类
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, '--备份
DECLARE 
   @lBCK01 varchar(16)
  ,@lSDate varchar(10)
  ,@lEDate varchar(10)
  ,@Type int
  ,@lLike varchar(32)
  ,@RateMode int
  ,@VBI13 varchar(24)
  ,@IsName int
  ,@Sql varchar(4000),@curdate varchar(10),@lbce01 varchar(16)

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 = ''%s''
set @curdate = CONVERT(varchar(10), GETDATE(), 120)
SET @lbce01 = cast(%d as varchar)
IF @lLike>''''
BEGIN
  set @IsName=case when ascii(@lLike)>127  then 1 else 0 end --判断是否姓名检索  	
	SET @Sql=
	    --住院
	     '' 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 ''
	    +'' 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 ''+CASE @IsName WHEN 1 THEN ''B.VAA05='''''' ELSE ''B.VAA04='''''' END+@lLike+''''''''
	    +'' AND EXISTS(SELECT * FROM V_VAJ_2 WHERE ACF01=2 AND BCK01D=''+@lBCK01+'' AND VAA01=A.VAA01 AND VAJ53''+@VBI13+'' AND VAA07=A.VAE01 AND VAJ46 BETWEEN ''''''+@lSDate+'''''' AND ''''''+@lEDate+'''''')''
            +'' 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 ''
	    +'' 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 ''+CASE @IsName WHEN 1 THEN ''B.VAA05='''''' ELSE ''B.VAA03='''''' END+@lLike+''''''''
            +'' AND EXISTS(SELECT * FROM V_VAJ_1 WHERE ACF01=1 AND BCK01D=''+@lBCK01+'' AND VAA01=A.VAA01 AND VAJ53''+@VBI13+'' AND VAA07=A.VAC01 AND VAJ46 BETWEEN ''''''+@lSDate+'''''' AND ''''''+@lEDate+'''''')
            and not exists(select * from VBU1 where a.VAA01=VAA01 and VBU12<0)''	
END
ELSE BEGIN
  SET @Sql=
      --住院  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 ''
      +'' 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 VAJ2 WHERE ACF01=2 AND BCK01D=''+@lBCK01+'' AND VAA01=A.VAA01 AND VAJ53''+@VBI13+'' AND VAA07=A.VAE01 AND VAJ46 BETWEEN ''''''+@lSDate+'''''' AND ''''''+@lEDate+'''''')''
      +'' 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 ''
      +'' 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  EXISTS(SELECT * FROM V_VAJ_1	WHERE	ACF01=1 
    		AND VAA01=A.VAA01 
    		AND VAA07=A.VAC01 
    		AND BCK01D=''+@lBCK01+''
    		AND VAJ53''+@VBI13+''
    		AND VAJ46 BETWEEN ''''''+@lSDate+'''''' AND ''''''+@lEDate+''''''
        )
        and not exists(select * from VBU1 where a.VAA01=VAA01 and VBU12<0)''
END

EXEC (@sql)', 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_FULL 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

  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
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
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 = 1)
  and (@lLike='''' or (@lLike<>'''' and exists(select * from #tmpvaa e where e.VAA01 = b.VAA01)))', '2014-09-22 17:50:30', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=2035 已经存在.'

GO