DROP proc _S_TIGUE
GO
create proc _S_TIGUE
    @x_rd_group  char(20),
    @x_rstd  char(2)
    with ENCRYPTION as 

    select roomno , sum(money) as money  into #ls_groom from groom where ps=2 group by roomno

    select a.roomno,
           a.r1,
           a.gno,
           a.max_tz as expend_tz,
           b.money
           into #tigue_expend
           from tigue a, #ls_groom b
           where b.roomno=*a.roomno



    select r1 as roomno, sum(money) as expend, sum(expend_tz) as expend_tz_s into #tx_groom from #tigue_expend where len(r1)>=3 group by r1

    select gno         , sum(money) as expend_g, sum(expend_tz) as expend_tz_g into #tx_groom_g from #tigue_expend where gno<>0 group by gno

    select a.roomno,
           a.r1 as tx,
           a.gno,
           a.money,
           a.expend_tz,
           b.expend,
           c.expend_g,
           b.expend_tz_s,
           c.expend_tz_g
           into #tigue_expend_0
           from #tigue_expend a ,#tx_groom b,#tx_groom_g c
           where b.roomno=*a.r1 and
                 c.gno=*a.gno
                 order by r1

    update #tigue_expend_0 set money=0 where money is null
    update #tigue_expend_0 set expend=money where expend is null
    update #tigue_expend_0 set expend=expend_g where gno>0
    update #tigue_expend_0 set expend_tz=expend_tz_s where len(tx)>=3
    update #tigue_expend_0 set expend_tz=expend_tz_g where gno>0

/*
    update #tigue_expend_0 set expend_tz=0 where expend_tz is null
    update #tigue_expend_0 set expend_tz=0 where expend_tz is null
*/

    if @x_rstd='  '
    select tigue.FLOOR,
           tigue.roomno,
           tigue.R1,
           tigue.GNO,
           tigue.name,
           tigue.kindrstd,
           tigue.kindsale,
           tigue.strate,
           tigue.r_ser_rate,
           tigue.price,
           tigue.ser,
           tigue.idate,
           tigue.odate,
           tigue.state,
           tigue.clean,
           space(50) as s_,
           tigue.remark,
           tigue.time,
           tigue.lctime,
           tigue.lockmark,
           stroom.rd,
           #tigue_expend_0.money,
           #tigue_expend_0.expend,
           #tigue_expend_0.expend_tz as max_tz
           from tigue,stroom,#tigue_expend_0
           where tigue.kindrstd*=stroom.rstd and
                 tigue.roomno*=#tigue_expend_0.roomno and
                 tigue.kindrstd in (select rstd from stroom where rd_group=@x_rd_group)
           order by tigue.floor,tigue.roomno
    else
    select tigue.FLOOR,
           tigue.roomno,
           tigue.R1,
           tigue.GNO,
           tigue.name,
           tigue.kindrstd,
           tigue.kindsale,
           tigue.strate,
           tigue.r_ser_rate,
           tigue.price,
           tigue.ser,
           tigue.idate,
           tigue.odate,
           tigue.state,
           tigue.clean,
           space(50) as s_,
           tigue.remark,
           tigue.time,
           tigue.lctime,
           tigue.lockmark,
           stroom.rd,
           #tigue_expend_0.money,
           #tigue_expend_0.expend,
           #tigue_expend_0.expend_tz as max_tz
           from tigue,stroom,#tigue_expend_0
           where tigue.kindrstd*=stroom.rstd and
                 tigue.roomno*=#tigue_expend_0.roomno and
                 tigue.kindrstd in (select rstd from stroom where rstd=@x_rstd)
           order by tigue.floor,tigue.roomno
GO