USE [RGDB]
GO

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

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Proc_GetYYQK3]
	-- 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

    -- Insert statements for procedure here

	Select 
	case when ISNULL(a.drys,0)+ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0)=0 then 0 else 
	ROUND((ISNULL(a.drhx,0)*100/(ISNULL(a.drys,0)+ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0) )),2) end drhxzb,
	case when ISNULL(a.dyys,0)+ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0)=0 then 0 else 
	ROUND((ISNULL(a.dyhx,0)*100/(ISNULL(a.dyys,0)+ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0) )),2) end dyhxzb, 
	ISNULL(a.drlh,0)drlh,ISNULL(a.dylh,0)dylh,ISNULL(a.drcd,0)drcd,ISNULL(a.dycd,0)dycd,    
	ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0) drwm,
	ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0) dywm,
	ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0) drwmxs,
	ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0) dywmxs,
	ISNULL(a.drwmxx,0) drwmxx,ISNULL(a.dywmxx,0) dywmxx,
	ISNULL(a.drtg,0)drtg,ISNULL(a.dytg,0)dytg,ISNULL(a.drjs,0)drjs,
	case when ISNULL(a.drys,0)+ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0)=0 then 0 else 
	ROUND((ISNULL(a.drjs,0)*100/(ISNULL(a.drys,0)+ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0))),2) end drjszb,
	ISNULL(a.dyjs,0)dyjs,
	case when ISNULL(a.dyys,0)+ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0)=0 then 0 else 
	ROUND((ISNULL(a.dyjs,0)*100/(ISNULL(a.dyys,0)+ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0))),2) end dyjszb,
	ISNULL(a.drcs,0)drcs,
	case when ISNULL(a.drys,0)+ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0)=0 then 0 else 
	ROUND((ISNULL(a.drcs,0)*100/(ISNULL(a.drys,0)+ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0))),2) end drcszb,
	ISNULL(a.dycs,0)dycs,
	case when ISNULL(a.dyys,0)+ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0)=0 then 0 else 
	ROUND((ISNULL(a.dycs,0)*100/(ISNULL(a.dyys,0)+ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0))),2) end dycszb,
	ISNULL(a.drgz,0)drgz,
	case when ISNULL(a.drys,0)+ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0)=0 then 0 else 
	ROUND((ISNULL(a.drgz,0)*100/(ISNULL(a.drys,0)+ISNULL(a.drwmlr,0)+ISNULL(a.drwmxs,0)+ISNULL(a.drwmxx,0))),2) end drgzzb,
	ISNULL(a.dygz,0)dygz,
	case when ISNULL(a.dyys,0)+ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0)=0 then 0 else 
	ROUND((ISNULL(a.dygz,0)*100/(ISNULL(a.dyys,0)+ISNULL(a.dywmlr,0)+ISNULL(a.dywmxs,0)+ISNULL(a.dywmxx,0))),2) end dygzzb,
	
	(select ROUND(case when people_num=0 then quantity else quantity/people_num *100 end,0) drcwsql 
    from (select SUM(isnull(c.quantity,0)) quantity,SUM(ISNULL(a.people_num,0)) people_num 
	from SC_RA_Orders a
	left join (select distinct order_id,sid from SC_RA_Details 
	where report_date=@report_date and tj_name='外包茶水') b on a.id=b.order_id and a.sid=b.sid
	left join (select order_id,sid,sum(quantity)quantity from SC_RA_Details 
	where report_date=@report_date and menu_name like '%茶位%'
	group by order_id,sid)c on a.sid=c.sid and a.id=c.order_id
	where b.order_id is null and a.report_date=@report_date and a.sid=@sid and isyx=0 )t1) drcwsql,
	
	(select ROUND(case when people_num=0 then quantity else quantity/people_num *100 end,0) dycwsql 
    from (select SUM(isnull(c.quantity,0)) quantity,SUM(ISNULL(a.people_num,0)) people_num 
	from SC_RA_Orders a
	left join (select distinct order_id,sid from SC_RA_Details 
	where report_date between @BDate and @EDate and tj_name='外包茶水') b on a.id=b.order_id and a.sid=b.sid
	left join (select order_id,sid,sum(quantity)quantity from SC_RA_Details 
	where report_date between @BDate and @EDate and menu_name like '%茶位%'
	group by order_id,sid)c on a.sid=c.sid and a.id=c.order_id
	where b.order_id is null and  a.report_date between @BDate and @EDate and a.sid=@sid and isyx=0 )t1) dycwsql,
	
	
	(select ROUND(case when people_num=0 then sl else sl/people_num *100 end,0) drmjsql from (
	select SUM(isnull(b.sl,0)) sl,SUM(a.people_num)people_num from SC_RA_Orders a
	left join (select sid,order_id,sum(quantity)sl from SC_RA_Details a
	where (a.menu_name like '%毛巾%' or a.menu_name like '%餐位%') and a.report_date=@report_date
	group by sid,order_id) b on a.sid=b.sid and a.id=b.order_id
	where a.report_date=@report_date and a.sid=@sid and a.isyx=0) t1) drmjsql,
	
	(select ROUND(case when people_num=0 then sl else sl/people_num *100 end,0) dymjsql from (
	select SUM(isnull(b.sl,0)) sl,SUM(a.people_num)people_num from SC_RA_Orders a
	left join (select sid,order_id,sum(quantity)sl from SC_RA_Details a
	where (a.menu_name like '%毛巾%' or a.menu_name like '%餐位%') and a.report_date between @BDate and @EDate
	group by sid,order_id) b on a.sid=b.sid and a.id=b.order_id
	where a.report_date between @BDate and @EDate and a.sid=@sid and a.isyx=0) t1)dymjsql
	                          
	from (select ISNULL(SUM(t1.amount-t1.kjje),0) drys,                   
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details 
	where sid=@sid and report_date=@report_date and tj_name ='海鲜')drhx, 
	(select SUM(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0))                                                                      
	from (select sid,id,amount,kjje from SC_RA_Orders 
	where sid=@sid and report_date between @BDate and @EDate) t1 
	)dyys,                                                                             
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date between @BDate and @EDate and tj_name like '%海鲜%')dyhx,  
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date=@report_date and category_name like '%礼盒%')drlh,    
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date between @BDate and @EDate and category_name like '%礼盒%')dylh,
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date=@report_date and 
	(category_id='21' or category_id='302' or category_id='303' or category_id='304'))drcd,             
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date between @BDate and @EDate and 
	(category_id='21' or category_id='302' or category_id='303' or category_id='304'))dycd, 
	(select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date=@report_date)drwmlr,
	(select ISNULL(SUM(amount),0) from SC_RA_Orders where report_date=@report_date and sid=@sid and id in
	(select distinct order_id from SC_RA_Details where sid=@sid and menu_name like '%线上外卖%' and quantity>0))drwmxs,  
	(select ISNULL(SUM(amount),0) from SC_RA_Orders where report_date=@report_date and sid=@sid and id in
	(select distinct order_id from SC_RA_Details where sid=@sid and menu_name like '%线下外卖%' and quantity>0))drwmxx,             
	(select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date between @BDate and @EDate)dywmlr, 

	(select ISNULL(SUM(amount),0) from SC_RA_Orders where report_date between @BDate and @EDate and sid=@sid and id in
	(select distinct order_id from SC_RA_Details where sid=@sid and menu_name like '%线上外卖%' and quantity>0))dywmxs,  
	(select ISNULL(SUM(amount),0) from SC_RA_Orders where report_date between @BDate and @EDate and sid=@sid and id in
	(select distinct order_id from SC_RA_Details where sid=@sid and menu_name like '%线下外卖%' and quantity>0))dywmxx,

	(select ISNULL(SUM(cast(tg.drtg as money)),0) drtg from(select SUBSTRING(payway,CHARINDEX(':',payway,CHARINDEX('团购套餐',payway))+1, 
	CHARINDEX(';',payway,CHARINDEX('团购套餐',payway))-CHARINDEX(':',payway,CHARINDEX('团购套餐',payway))-1) drtg   
	from SC_RA_Orders where sid=@sid and report_date=@report_date and payway like '%团购套餐%') tg) drtg,                 
	(select ISNULL(SUM(cast(tg.dytg as money)),0) dytg from(select SUBSTRING(payway,CHARINDEX(':',payway,CHARINDEX('团购套餐',payway))+1, 
	CHARINDEX(';',payway,CHARINDEX('团购套餐',payway))-CHARINDEX(':',payway,CHARINDEX('团购套餐',payway))-1) dytg   
	from SC_RA_Orders where sid=@sid and report_date between @BDate and @EDate and payway like '%团购套餐%') tg) dytg,
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date=@report_date and tj_name like '%酒水%')drjs,
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date between @BDate and @EDate and tj_name like '%酒水%')dyjs,
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date=@report_date and tj_name like '%外包茶水%')drcs,
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date between @BDate and @EDate and tj_name like '%外包茶水%')dycs,
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date=@report_date and tj_name like'%外包果汁%')drgz,
	(select ISNULL(SUM(order_discount),0) from SC_RA_Details where sid=@sid and report_date between @BDate and @EDate and tj_name like '%外包果汁%')dygz
	from SC_RA_Orders t1
	where t1.sid=@sid and t1.report_date=@report_date) a


END

GO