USE [RGDB]
GO

/****** Object:  StoredProcedure [dbo].[Proc_GetYYQK1]    Script Date: 01/10/2026 10:22:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Proc_GetYYQK1]
	-- 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
	  @BZDate1 date, @BZDate2 date, @BZDate3 date, @BZDate4 date, @BZDate5 date, @BZDate6 date;
	  
	  set @BZDate1=DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, @report_date) ), 0);
	  set @BZDate2=@report_date
	  set @BZDate3=DATEADD(DAY,-DAY(@report_date)+1,@report_date);
	  set @BZDate4=@report_date
	  set @BZDate5=DATEADD(YEAR, DATEDIFF(YEAR, 0, @report_date), 0);
	  set @BZDate6=@report_date
	
	
	select ISNULL(a.drzb,0)drzb,ISNULL(a.drys,0)drys,ISNULL(a.drys,0)-ISNULL(a.drzb,0) drce,ISNULL(a.drzk,0)drzk,
	case when ISNULL(a.drzb,0)=0 then 0 else ROUND(((ISNULL(a.drysdcl,0)+ISNULL(a.drystz,0))*100/a.drzb),2) end drdcl, 
	case when ISNULL(a.bzzb,0)=0 then 0 else ROUND(((ISNULL(a.bzysdcl,0)+ISNULL(a.bzystz,0))*100/a.bzzb),2) end bzdcl,
	case when ISNULL(a.ydzb,0)=0 then 0 else ROUND(((ISNULL(a.ydysdcl,0)+ISNULL(a.ydystz,0))*100/a.ydzb),2) end yddcl,
	case when ISNULL(a.ydpkzb,0)=0 then 0 else ROUND(((ISNULL(a.ydysdcl,0)+ISNULL(a.ydystz,0))*100/a.ydpkzb),2) end ydpkdcl,
	ISNULL(a.ydys,0)+ISNULL(a.ydystz,0) ydys,
	case when ISNULL(a.ndzb,0)=0 then 0 else ROUND(((ISNULL(a.ndysdcl,0)+ISNULL(a.ndystz,0))*100/a.ndzb),2) end nddcl 
	from  (select JYYS_YSYS drzb,  
	(select SUM( ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0)  )+                                                                     
	ISNULL((select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date = @report_date),0)    
	from (select sid,id,amount,kjje from SC_RA_Orders where sid=@sid and report_date = @report_date ) t1)drys, 
	
	(select SUM(ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0)  )+                                                                     
	ISNULL((select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date = @report_date),0)    
	from (
	select a.sid,a.id,a.amount,a.kjje from SC_RA_Orders a 
	left join (select * from Base_Table where table_sid=@sid and table_year=YEAR(@report_date) and table_month=MONTH(@report_date)) 
	b on a.sid=b.table_sid and a.table_name=b.table_name 
	where a.sid=@sid and a.report_date = @report_date and ISNULL(b.table_dcljs,1)=1) t1 
	
	)drysdcl,                                                                        
	ISNULL((select SUM(menu_amount)-SUM(amount) from SC_RA_Orders where sid=@sid and report_date=@report_date),0)drzk, 
	           
	(select SUM( ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) )+                                                                      
	ISNULL((select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date between @BZDate1 and @BZDate2),0)    
	from (select sid,id,amount,kjje from SC_RA_Orders where sid=@sid and report_date between @BZDate1 and @BZDate2 ) t1 
	
	)bzys,
	
	(select SUM(ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) )+                                                                      
	ISNULL((select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date between @BZDate1 and @BZDate2),0)    
	from (
	select a.sid,a.id,a.amount,a.kjje from SC_RA_Orders a 
	left join (select * from Base_Table where table_sid=@sid ) 
	b on a.sid=b.table_sid and a.table_name=b.table_name and YEAR(a.report_date)=b.table_year and MONTH(a.report_date)=b.table_month
	where a.sid=@sid and a.report_date between @BZDate1 and @BZDate2 and ISNULL(b.table_dcljs,1)=1) t1 
	)bzysdcl,
	                                                                              
	(select SUM(JYYS_YSYS)bzzb from REP_JYYS where JYYS_StoreID=@sid and JYYS_Date between @BZDate1 and @BZDate2)bzzb,        
	(select JYYS_ydzb from REP_YJYYS WHERE JYYS_StoreID=@sid AND JYYS_Year=YEAR(@report_date) and JYYS_Month=MONTH(@report_date))ydzb,
	(select YPKYS_YDZB from REP_YPKYS where YPKYS_StoreID=@sid and YPKYS_Year=YEAR(@report_date) and YPKYS_Month=MONTH(@report_date))ydpkzb,       
	(select SUM(ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) )+                                                                      
	ISNULL((select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date between @BZDate3 and @BZDate4),0)    
	from (select sid,id,amount,kjje from SC_RA_Orders where sid=@sid and report_date between @BZDate3 and @BZDate4 ) t1 
	
	)ydys, 
	
	(select SUM(ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) )+                                                                      
	ISNULL((select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date between @BZDate3 and @BZDate4),0)    
	from (
	select a.sid,a.id,a.amount,a.kjje from SC_RA_Orders a 
	left join (select * from Base_Table where table_sid=@sid) b on a.sid=b.table_sid and a.table_name=b.table_name  
	and YEAR(a.report_date)=b.table_year and MONTH(a.report_date)=b.table_month
	where a.sid=@sid and a.report_date between @BZDate3 and @BZDate4 and ISNULL(b.table_dcljs,1)=1) t1 
	
	)ydysdcl,
	                                                                           
	(select JYYS_NDZB from REP_NJYYS WHERE JYYS_StoreID=@sid AND JYYS_Year=YEAR(@report_date))ndzb,                                 
	(select SUM(ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) )+                                                                    
	ISNULL((select ISNULL(SUM(value),0) from SC_Base_wmtg where sid=@sid and report_date between @BZDate5 and @BZDate6),0)  
	from (select sid,id,amount,kjje from SC_RA_Orders where sid=@sid and report_date between @BZDate5 and @BZDate6 ) t1 
	)ndys,
	
	(select SUM(ROUND(ISNULL(t1.amount,0)-ISNULL(t1.kjje,0),0) )+ISNULL((select ISNULL(SUM(value),0)                                                                       
	from SC_Base_wmtg where sid=@sid and report_date between @BZDate5 and @BZDate6),0)    
	from (
	select a.sid,a.id,a.amount,a.kjje from SC_RA_Orders a 
	left join (select * from Base_Table where table_sid=@sid) b on a.sid=b.table_sid and a.table_name=b.table_name  
	and YEAR(a.report_date)=b.table_year and MONTH(a.report_date)=b.table_month
	where a.sid=@sid and a.report_date between @BZDate5 and @BZDate6 and ISNULL(b.table_dcljs,1)=1) t1  
	
	)ndysdcl,
	
	(select ISNULL(SUM(value),0) from REP_YSTZ where sid=@sid and report_date = @report_date)drystz,
	(select ISNULL(SUM(value),0) from REP_YSTZ where sid=@sid and report_date between @BZDate1 and @BZDate2)bzystz,
	(select ISNULL(SUM(value),0) from REP_YSTZ where sid=@sid and report_date between @BZDate3 and @BZDate4)ydystz,
	(select ISNULL(SUM(value),0) from REP_YSTZ where sid=@sid and report_date between @BZDate5 and @BZDate6)ndystz                                                                            
	from REP_JYYS where JYYS_StoreID=@sid and JYYS_Date=@report_date) a


END

GO