lyfz_repos/经典版源码/document/数据库维护脚本/财务查询存储过程.sql

--------------------------------------------------------------------------------------------------------
alter proc Query_Finance  
@OrderCount			int	output,		--1.当天订单数;							--
@ReceiptCount		int	output,		--2.当天收款次数;						--
@Oreder1			int	output,		--3.当天订单预约收款;					--
@Oreder2			int	output,		--4.当天订单补款;						--
@Oreder3			int	output,		--5.当天订单后期补款;					--
@Oreder4			int	output,		--6.当天订单使用储值卡消费金额;			--
@OtherReceipts		int	output,		--7.当天其他收入;						--
@Payout				int	output,		--8.当天支出;							--
@OtherSalary		int	output,		--9.当天其他二销;						--
@MemberReceipts		int	output,		--A.当天会员收入;						--
@GrossReceipts		int	output,		--B.当天总收入;							--总收入要扣除储值卡消费的;
@NetReceipt			int	output		--C.当天纯收入;							--; 
as
begin

--1.获得当天的订单数目;
select @OrderCount = count(distinct id) from dindanbukuan where 0=datediff(day,date,getdate())

--2.当天收款次数;
select @ReceiptCount = count(id) from dindanbukuan where 0=datediff(day,date,getdate())

--3.当天订单预约收款;
select @Oreder1 = sum(convert(int,money)) from dindanbukuan where 0=datediff(day,date,getdate()) and bz='预约收款'

--4.当天订单补款;
select @Oreder2 = sum(convert(int,money)) from dindanbukuan where 0=datediff(day,date,getdate()) and bz='预约补款'

--5.当天订单后期补款;
select @Oreder3 = sum(convert(int,money)) from dindanbukuan where 0=datediff(day,date,getdate()) and bz not in('预约收款','预约补款')

--6.当天订单使用储值卡消费金额;
select @Oreder4 = sum(convert(int,money)) from dindanbukuan where 0=datediff(day,date,getdate()) and paytype = '储值卡扣款'

--7.当天其他收入;
select @OtherReceipts = sum(convert(float,money)) from singleincome where 0=datediff(day,dat,getdate()) and sale2type is null

--8.当天支出;
select @Payout = sum(convert(float,money)) from gudingfeiyong where 0=datediff(day,dat,getdate())

--9.当天其他二销;
select @OtherSalary = sum(convert(float,money)) from singleincomemoneyview where 0=datediff(day,date,getdate())

--10.当天会员收入;
select @MemberReceipts = sum(convert(float,money)) from memberview where 0=datediff(day,date,getdate())

--11.当天的总收入;
set @GrossReceipts = 0

if @Oreder1 is not null
begin
	set @GrossReceipts = @GrossReceipts + @Oreder1
end

if @Oreder2 is not null
begin
	set @GrossReceipts = @GrossReceipts + @Oreder2
end

if @Oreder3 is not null
begin
	set @GrossReceipts = @GrossReceipts + @Oreder3
end

if @Oreder4 is not null
begin
	set @GrossReceipts = @GrossReceipts - @Oreder4
end

if @OtherReceipts is not null
begin
	set @GrossReceipts = @GrossReceipts + @OtherReceipts
end

if @OtherSalary is not null
begin
	set @GrossReceipts = @GrossReceipts + @OtherSalary
end

--12.当天的纯收入;
set @NetReceipt = (@GrossReceipts - @Payout)

end