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

USE [db]
GO
/****** 对象:  StoredProcedure [dbo].[Query_Finance]    脚本日期: 01/09/2015 16:33:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------------------------------------------
alter proc [dbo].[Query_Finance]  
as
begin
declare
@OrderCount			int	,		--1.当天订单数;							--
@ReceiptCount		int	,		--2.当天收款次数;						--
@Oreder1			int	,		--3.当天订单预约收款;					--
@Oreder2			int	,		--4.当天订单补款;						--
@Oreder3			int	,		--5.当天订单后期补款;					--
@Oreder4			int	,		--6.当天订单使用储值卡消费金额;			--
@OtherReceipts		int	,		--7.当天其他收入;						--
@Payout				int	,		--8.当天支出;							--
@OtherSalary		int	,		--9.当天其他二销;						--
@MemberReceipts		int	,		--A.当天会员收入;						--
@GrossReceipts		int	,		--B.当天总收入;							--总收入要扣除储值卡消费的;
@NetReceipt			int			--C.当天纯收入;							--; 

--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
	set @GrossReceipts = @GrossReceipts + @Oreder1
else
	set @Oreder1 = 0

if @Oreder2 is not null
	set @GrossReceipts = @GrossReceipts + @Oreder2
else
	set @Oreder2 = 0

if @Oreder3 is not null
	set @GrossReceipts = @GrossReceipts + @Oreder3
else
	set @Oreder3 = 0

if @Oreder4 is not null
	set @GrossReceipts = @GrossReceipts - @Oreder4
else
	set @Oreder4 = 0

if @MemberReceipts is not null
	set @GrossReceipts = @GrossReceipts + @MemberReceipts
else
	set @MemberReceipts = 0

if @OtherReceipts is not null
	set @GrossReceipts = @GrossReceipts + @OtherReceipts
else
	set @OtherReceipts = 0

if @OtherSalary is not null
	set @GrossReceipts = @GrossReceipts + @OtherSalary
else
	set @OtherSalary = 0

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

select 
@OrderCount		'OrderCount',					--当天订单数;
@ReceiptCount	'ReceiptCount',					--当天收款次数;
@Oreder1		'OrdersReceivables',			--当天订单预约收款;
@Oreder2		'OrdersReplenishment',			--当天订单补款;
@Oreder3		'LateReplenishment',			--当天订单后期补款;
@Oreder4		'StoredCardSpending',			--当天储值卡消费;
@OtherReceipts	'OtherReceipts',				--当天其他收入;
@Payout			'Payout',						--当天支出;
@OtherSalary	'OtherSalary',					--当天的其他二销;
@MemberReceipts 'MemberReceipts',				--当天会员收入;
@GrossReceipts	'GrossReceipts',				--当天的总收入;
@NetReceipt		'NetReceipt'					--当天的净收入;
end