Text                                                                                                                                                                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE procedure usp_bq_fymxcx   
 @syxh   ut_syxh,  --首页序号   
 @lb   ut_bz=0,  --类别   
 @dxmdm  ut_kmdm=null, --大项目代码   
    @idm        ut_xh9=0,       --药idm   
 @ypdm       ut_xmdm='',  --药品代码   
 @jsxh  ut_xh12=null, --结算序号   
 @cxlb  ut_bz=0,   
    @sffp       ut_bz=0      --是否按发票大项目汇总查询   
   
as   
/**********   
[版本号]4.0.0.0.0   
[创建时间]2001.11.21   
[作者]朱伟杰   
[版权] Copyright ? 1998-2001上海金仕达-卫宁医疗信息技术有限公司   
[描述] 住院系统--费用明细查询   
[功能说明]   
 病人费用明细查询(存储过程做优化,分成三个存储过程做,此存储过程只查大项)   
[参数说明]   
 @syxh   首页序号   
 @lb   类别 0=大项费用   
 @dxmdm  ut_kmdm 大项目代码（lb=1时使用）   
    @idm        药idm（lb=2时使用）   
 @ypdm       药品代码（lb=2时使用）   
 @jsxh  结算序号   
 @cxlb  0=所有，1=所有药费，2=所有非药费   
[返回值]   
[结果集、排序]   
 成功：数据集   
 错误："F","错误信息"   
[调用的sp]   
[调用实例]   
[修改历史]   
 yxp 小项修改前后名称显示不一   
 hcy 修改药品的大项代码从药品标志取.   
 2003.10.15 Wang Yi     
  1）返回的自费金额中要扣除分类支付部分   
  2）返回结果集增加分类支付金额，前台通过检查此项金额是否大于0来判断是否是分类支付项目   
 2003.10.24 Wang Yi   
  存储过程做优化,分成三个存储过程做,此存储过程只查大项。   
  为避免修改住院结算模块，接口参数不做改动！！！   
**********/   
set nocount on   
   
declare @patid ut_syxh   
   
select @patid=patid from ZY_BRSYK where syxh=@syxh and brzt<>9   
if @@rowcount=0 or @@error<>0   
begin   
 select "F","该病人不存在！"   
 return   
end   
   
if @lb <> 0     
begin   
 select "F", "传入参数不正确"   
 return   
end   
   
create table #tempbqdx   
(   
 jsxh          ut_xh12           not null, --结算序号   
 lb       ut_bz    not null, --类别   
    dxmdm         ut_kmdm               null, --大项代码   
    dxmmc         ut_mc16              null, --大项名称   
    xmje          ut_money           null, --项目金额   
    zfje          ut_money              null, --自费金额   
    yhje          ut_money              null, --优惠金额   
    yeje          ut_money              null, --婴儿金额   
    kbje as (xmje - zfje),  --可报金额   
 memo    ut_memo    null  --备注   
 ,flzfje  ut_money  null  --分类支付金额,add by Wang Yi, 2003.10.15   
)   
   
   
if @sffp=0     
insert #tempbqdx(jsxh,lb,dxmdm,dxmmc,xmje,zfje,yhje,yeje,flzfje)   
    select b.jsxh,0,b.dxmdm,b.dxmmc,b.xmje,b.zfje,b.yhje,b.yeje,b.flzfje   
 from ZY_BRJSK a (nolock),ZY_BRJSMXK b (nolock)   
 where a.xh=b.jsxh and a.syxh=@syxh and a.jlzt=0 and b.xmje<>0   
    order by b.dxmdm   
else   
insert #tempbqdx(jsxh,lb,dxmdm,dxmmc,xmje,zfje,yhje,yeje,flzfje)   
    select b.jsxh,0,b.fpxmdm,b.fpxmmc,sum(b.xmje),sum(b.zfje),sum(b.yhje),sum(b.yeje),sum(b.flzfje)     
 from ZY_BRJSK a (nolock),ZY_BRJSMXK b (nolock)   
 where a.xh=b.jsxh and a.syxh=@syxh and a.jlzt=0 and b.xmje<>0   
    group by b.jsxh,b.fpxmdm,b.fpxmmc   
    order by b.fpxmdm   
   
select jsxh, sum(yeje) as yeje,sum(zfje) as zfje   
into #yeje from #tempbqdx where lb=0 group by jsxh   
   
insert #tempbqdx(jsxh,lb,dxmdm,dxmmc,xmje,zfje,yhje,yeje,flzfje)   
select a.xh,1,"","总计",a.zje,b.zfje,a.yhje,b.yeje,a.flzfje   
 from ZY_BRJSK a (nolock),#yeje b (nolock)     
 where a.syxh=@syxh and a.jlzt=0  and a.xh*=b.jsxh  --and a.zje<>0  hkh on 2003.04.29   
   
insert #tempbqdx(jsxh,lb,dxmdm,dxmmc,xmje,zfje,yhje,yeje,flzfje)   
select xh,3,null,null,null,null,null,null,null   
 from ZY_BRJSK (nolock) where syxh=@syxh and jlzt=0   
   
insert #tempbqdx(jsxh,lb,dxmdm,dxmmc,xmje,zfje,yhje,yeje,flzfje)   
select jsxh,2,null,"押金累计",sum(jje-dje),null,null,null,null   
 from ZYB_BRYJK (nolock) where syxh=@syxh and czlb in (0,1,2,3,4,5,6) and     
  jsxh in (select jsxh from #tempbqdx)   
 group by jsxh   
   
select dxmdm "项目代码",dxmmc "项目名称",xmje "项目金额",flzfje "分类支付金额",zfje-flzfje "自费金额",kbje "可报金额",yhje "减免金额",jsxh "结算序号",yeje "婴儿金额"   
 from #tempbqdx order by jsxh,lb,dxmdm   
  
--modify ylf 病人明细查询使用大项目还是发票项目   
delete BQ_BRXXCX_YLF where syxh=@syxh   
insert into BQ_BRXXCX_YLF   
select @syxh,@sffp   
   
return   
   
   
  

---------------------
01     西药费              5575.8500             .0000                 .0000                 5575.8500             .0000                 12723          .0000
10     治疗费              487.9000              .0000                 .0000                 487.9000              .0000                 12723          .0000
12     化验费              2555.0000             .0000                 .0000                 2555.0000             .0000                 12723          .0000
14     DSA              4200.0000             .0000                 .0000                 4200.0000             .0000                 12723          .0000
17     输氧费              34.0000               .0000                 .0000                 34.0000               .0000                 12723          .0000
19     诊疗费              38.0000               .0000                 .0000                 38.0000               .0000                 12723          .0000
32     手术费              1057.0000             .0000                 .0000                 1057.0000             .0000                 12723          .0000
42     护理费              196.0000              .0000                 .0000                 196.0000              .0000                 12723          .0000
43     床位费              687.5000              .0000                 .0000                 687.5000              .0000                 12723          .0000
46     材料费              19719.9000            .0000                 .0000                 19719.9000            .0000                 12723          .0000
47     输液费              340.8000              .0000                 .0000                 340.8000              .0000                 12723          .0000
48     病理检验             120.0000              .0000                 .0000                 120.0000              .0000                 12723          .0000
49     换药费              90.0000               .0000                 .0000                 90.0000               .0000                 12723          .0000
51     麻醉费              231.0000              .0000                 .0000                 231.0000              .0000                 12723          .0000
       总计               35332.9500            .0000                 .0000                 35332.9500            .0000                 12723          .0000
NULL   押金累计             35332.9500            NULL                  NULL                  NULL                  NULL                  12723          NULL
NULL   NULL             NULL                  NULL                  NULL                  NULL                  NULL                  12723          NULL