lyfz_repos/经典版源码/document/数据库维护脚本/接单系统财务统计.sql

declare @sdate nvarchar(20)	--开始时间;
declare @edate nvarchar(20)	--结束时间;

set @sdate = '2015-01-07'
set @edate = '2015-07-07'

--财务统计;
--总收入不包含储值卡扣款;
--营业收入不包含其他收入;
--净收入 = 总收入 - 总支出;
--成交订单数: 收了预约收款的订单;
--补款:预约补款;
--摄影二销\化妆二销\选片二销:后期收款;

/*

总收入 = 订单定金(支付方式 != 储值卡扣款), 订单补款(支付方式 != 储值卡扣款),订单后期(支付方式 != 储值卡扣款),其他收入(支付方式 != 储值卡扣款),积分兑换,会员充值;
解析:会员充值 有可能会用来交订单定金、订单补款、订单后期，所以这三种收款不能是储值卡扣款的；

营业收入 = 订单定金(支付方式可以是储值卡扣款), 订单补款(支付方式可以是储值卡扣款), 订单后期(支付方式可以是储值卡扣款);

*/

/*
select * FROM gudingfeiyong WHERE dat >= @sdate and dat <= @edate
select * FROM singleincome WHERE dat >= @sdate and dat <= @edate and (sale2type is null or sale2type = '')
select * FROM dindanbukuanview2 WHERE date >= @sdate and date <= @edate
select * FROM singleincomemoney WHERE date >= @sdate and date <= @edate
select * FROM memberview WHERE date >= @sdate and date <= @edate
*/
go



--
--注:singleincome表中,sale2type is null or sale2type = '' 表示的是其他收入; 非空的情况下,需要与singleincomemoney结合成视图singleincomemoenyview才是二销收入;
--
CREATE PROCEDURE SP_FINANCIAL_REPORTING
(
@STRAT_DATE NVARCHAR(20),	-- 查询起始日期;
@ENDOF_DATE NVARCHAR(20),	-- 查询结束日期;
@SUM1 FLOAT OUTPUT,			-- 总收入;
@SUM2 FLOAT OUTPUT,			-- 营业收入;
@SUM3 FLOAT OUTPUT,			-- 总支出;
@SUM4 FLOAT OUTPUT,			-- 净收入;
@SUM5 FLOAT OUTPUT,			-- 订单数;
@SUM6 FLOAT OUTPUT,			-- POS机刷卡金额;
@SUM7 FLOAT OUTPUT,			-- 储值卡扣款金额;
@SUM8 FLOAT OUTPUT,			-- 积分兑换;
@SUM9 FLOAT OUTPUT			-- 柜台现金 如果查询的是当天的,需要显示该值,否则不显示柜台现金(柜台现金只有当天的才有意义);
) AS
BEGIN
--1总收入,不包含'储值卡扣款',但包含其他收入:
SET @SUM1 = ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincome WHERE paytype <> '储值卡扣款' and dat >= @STRAT_DATE and dat <= @ENDOF_DATE and (sale2type is null or sale2type = '')),0) --不包含'储值卡扣款'的'其他收入';
--SET @SUM1 = ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincome WHERE paytype <> '储值卡扣款' and dat >= @STRAT_DATE and dat <= @ENDOF_DATE ),0) --不包含'储值卡扣款'
SET @SUM1 = @SUM1 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM dindanbukuanview2 WHERE paytype <> '储值卡扣款' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0) --不包含'储值卡扣款'
SET @SUM1 = @SUM1 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincomemoneyview WHERE paytype <> '储值卡扣款' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0) --不包含'储值卡扣款'
SET @SUM1 = @SUM1 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM memberview WHERE date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
--SELECT @SUM1 '总收入'

--2.营业收入,只包含订单收入,不包含其他收入;
--SET @SUM2 = ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincome WHERE dat >= @STRAT_DATE and dat <= @ENDOF_DATE and (sale2type is null or sale2type = '')),0) -- 不包含其他收入,只有订单二销;
SET @SUM2 = ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM dindanbukuanview2 WHERE date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
SET @SUM2 = @SUM2 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincomemoneyview WHERE date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
--SELECT @SUM2 '营业收入'

--3.总支出;
SET @SUM3 = ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM gudingfeiyong WHERE dat >= @STRAT_DATE and dat <= @ENDOF_DATE),0)
--SELECT @SUM3 '总支出'

--4.净收入 = 总收入 - 总支出;
SET @SUM4 = ISNULL((@SUM1 - @SUM3),0)
--SELECT @SUM4 '净收入'

--5.成交订单数 = 过去补款的订单不算,只算规定时间内的订单预约收款(交订金)数量; --bz字段命名歧义;
SET @SUM5 = ISNULL((SELECT count(DISTINCT id) FROM dindanbukuanview2 WHERE bz = '预约收款' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
--SELECT @SUM5 '订单数'

--6.POS机刷卡金额,其他收入、订单收入、订单二销、会员充值中使用POS机支付方式的所有收款记录;
SET @SUM6 = ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincome WHERE paytype = 'POS机刷卡' and dat >= @STRAT_DATE and dat <= @ENDOF_DATE and (sale2type is null or sale2type = '')),0)
SET @SUM6 = @SUM6 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM dindanbukuanview2 WHERE paytype = 'POS机刷卡' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
SET @SUM6 = @SUM6 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincomemoneyview WHERE paytype = 'POS机刷卡' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
SET @SUM6 = @SUM6 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM memberview WHERE moneytype = 'POS机刷卡' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
--SELECT @SUM6 'POS机刷卡金额'

--7.储值卡扣款金额，其他收入、订单收入、其他二销中使用储值卡扣款的所有记录;
SET @SUM7 = ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincome WHERE paytype = '储值卡扣款' and dat >= @STRAT_DATE and dat <= @ENDOF_DATE and (sale2type is null or sale2type = '')),0)
SET @SUM7 = @SUM7 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM dindanbukuanview2 WHERE paytype = '储值卡扣款' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
SET @SUM7 = @SUM7 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincomemoneyview WHERE paytype = '储值卡扣款' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
--SELECT @SUM7 '储值卡扣款金额'

--8.积分兑换;
SET @SUM8 = ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincome WHERE paytype = '积分兑换' and dat >= @STRAT_DATE and dat <= @ENDOF_DATE and (sale2type is null or sale2type = '')),0)
SET @SUM8 = @SUM8 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM dindanbukuanview2 WHERE paytype = '积分兑换' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
SET @SUM8 = @SUM8 + ISNULL((SELECT SUM(convert(FLOAT,money,10)) FROM singleincomemoneyview WHERE paytype = '积分兑换' and date >= @STRAT_DATE and date <= @ENDOF_DATE),0)
--SELECT @SUM8 '积分兑换'

--9.柜台现金 = 净收入 - 积分兑换 - POS机刷卡;
SET @SUM9 = ISNULL((@SUM4 - @SUM8 - @SUM6),0)
--SELECT @SUM9 '柜台现金'
END



DECLARE	@return_value int,@SUM1 float,@SUM2 float,@SUM3 float,@SUM4 float,@SUM5 float,@SUM6 float,@SUM7 float,@SUM8 float,@SUM9 float

EXEC	@return_value = [dbo].[SP_FINANCIAL_REPORTING]
		@STRAT_DATE = N'2015-07-01',@ENDOF_DATE = N'2015-07-30',
		@SUM1 = @SUM1 OUTPUT,@SUM2 = @SUM2 OUTPUT,
		@SUM3 = @SUM3 OUTPUT,@SUM4 = @SUM4 OUTPUT,
		@SUM5 = @SUM5 OUTPUT,@SUM6 = @SUM6 OUTPUT,
		@SUM7 = @SUM7 OUTPUT,@SUM8 = @SUM8 OUTPUT,
		@SUM9 = @SUM9 OUTPUT

SELECT	@SUM1 as N'总收入',
		@SUM2 as N'营业收入',
		@SUM3 as N'总支出',
		@SUM4 as N'净收入',
		@SUM5 as N'成交订单数',
		@SUM6 as N'POS机刷卡金额',
		@SUM7 as N'储值卡扣款金额',
		@SUM8 as N'积分兑换',
		@SUM9 as N'柜台现金'
GO