USE [RGDB]
GO

/****** Object:  StoredProcedure [dbo].[Proc_GetYYQK2]    Script Date: 01/10/2026 10:23:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Proc_GetYYQK2]
	-- Add the parameters for the stored procedure here
	@sid nvarchar(10),
	@report_date date
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	declare
	  @BDate date, 
	  @EDate date;
	  set @BDate=DATEADD(DAY,-DAY(@report_date)+1,@report_date);
	  set @EDate=@report_date

    declare
      @dtydys money;
    set @dtydys=0;
    select @dtydys=ISNULL(SUM( ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) ),0)   
	from (select sid,id,amount,kjje from SC_RA_Orders 
	where isyx=0 and ztlx='大厅' and sid=@sid and report_date between @BDate and @EDate) t1   

    declare
      @bfzs int,@bfrs int,@bfys money,@bfydys money;
    set @bfzs=0;set @bfrs=0;set @bfys=0;set @bfydys=0;
    select @bfzs=ISNULL(SUM(zts),0),@bfrs=ISNULL(SUM(people_num),0) 
    from SC_RA_Orders 
    where sid=@sid and report_date=@EDate and isyx=0 and ztlx='包厢'
    
    select @bfys=ISNULL(SUM( ROUND(ISNULL(dt1.amount,0)-ISNULL(dt1.kjje,0),0) ),0)+                                             
	ISNULL((select SUM(value) from SC_Base_wmtg where sid=@sid and report_date=@EDate),0)             
	from (select sid,id,zts,amount,kjje from SC_RA_Orders 
	where sid=@sid and report_date=@EDate and isyx=0 and ztlx='包厢')dt1                                                                                 
    
    select @bfydys=ISNULL(SUM( ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) ),0)+                                                                      
	ISNULL((select ISNULL(SUM(value),0) from 
	SC_Base_wmtg where sid=@sid and report_date between @BDate and @EDate),0)    
	from (select sid,id,SUM(amount)amount,SUM(kjje)kjje 
	from SC_RA_Orders 
	where isyx=0 and ztlx='包厢' and sid=@sid and report_date between @BDate and @EDate      
	group by sid,id) t1 
    
    declare
      @yxzs int,@yxrs int;
    set @yxzs=0;set @yxrs=0;
    select @yxzs=ISNULL(SUM(zts),0),@yxrs=ISNULL(SUM(people_num),0) 
    from SC_RA_Orders where sid=@sid and report_date=@EDate and isyx=1
    
    declare
      @yxys money,@yxydys money,@ydys money;
    set @yxys=0;set @yxydys=0;set @ydys=0;
    select @yxys=ISNULL(SUM( ROUND(ISNULL(dt1.amount,0)-ISNULL(dt1.kjje,0),0) ),0)                                            
	from (select sid,id,zts,amount,kjje from SC_RA_Orders 
	where sid=@sid and report_date=@EDate and isyx=1 )dt1                                                                                 
	
	select @yxydys=ISNULL(SUM( ROUND(ISNULL(dt1.amount,0)-ISNULL(dt1.kjje,0),0) ),0)                                             
	from (select sid,id,zts,amount,kjje from SC_RA_Orders 
	where sid=@sid and report_date between @BDate and @EDate and isyx=1 )dt1                                                                                 
	
	
	select @ydys=ISNULL(SUM( ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) ),0)+                                                                      
	ISNULL((select ISNULL(SUM(value),0) from SC_Base_wmtg 
	where sid=@sid and report_date between @BDate and @EDate),0)    
	from (select sid,id,amount,kjje from SC_RA_Orders 
	where sid=@sid and report_date between @BDate and @EDate) t1 
	
	

	select a.dtys,ISNULL(a.dtydys,0)dtydys,
	case when ISNULL(a.dtzs,0)=0 then 0 else ISNULL(ROUND((a.dtys/a.dtzs),2),0) end dtzj,a.dtzs,
	case when ISNULL(a.ydys,0)=0 then 0 else ISNULL(ROUND((a.dtydys*100/a.ydys),2),0) end dtyddcl,                 
	case when ISNULL(a.dtrs,0)=0 then 0 else ISNULL(ROUND((a.dtys/a.dtrs),2),0) end dtrj,a.bfys,a.bfydys,
	case when ISNULL(a.ydys,0)=0 then 0 else ISNULL(ROUND((a.bfydys*100/a.ydys),2),0) end bfyddcl,
	case when ISNULL(a.bfzs,0)=0 then 0 else ISNULL(ROUND((a.bfys/a.bfzs),2),0) end bfzj,a.bfzs,                        
	case when ISNULL(a.bfrs,0)=0 then 0 else ISNULL(ROUND((a.bfys/a.bfrs),2),0) end bfrj,a.yxys,                               
	case when ISNULL(a.yxzs,0)=0 then 0 else ISNULL(ROUND((a.yxys/a.yxzs),2),0) end yxzj,a.yxzs,                               
	case when ISNULL(a.yxrs,0)=0 then 0 else ISNULL(ROUND((a.yxys/a.yxrs),2),0) end yxrj,a.yxydys,
	case when ISNULL(a.ydys,0)=0 then 0 else ISNULL(ROUND((a.yxydys*100/a.ydys),2),0) end yxyddcl
	from                                  
	(select ISNULL(SUM(dt1.zts),0)dtzs,ISNULL(SUM(dt1.people_num),0)dtrs,
	ISNULL(SUM( ROUND(dt1.amount-dt1.kjje,0) ),0) dtys,
	@dtydys dtydys,
	@bfzs bfzs,
	@bfrs bfrs,
	@bfys bfys,
    @bfydys bfydys,
	@yxzs yxzs,
	@yxrs yxrs,
	@yxys yxys,
	@yxydys yxydys,
	@ydys ydys                                                                                              
	from (select sid,id,zts,people_num,amount,kjje from SC_RA_Orders 
	where sid=@sid and report_date=@EDate and isyx=0 and ztlx='大厅')dt1) a

END

GO