lyfz_repos/经典版源码/document/数据库维护脚本/财务短信语句.sql

--------------------------------------------------------------------------------------------------------
use db
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.当天总收入;							--总收入要扣除储值卡消费的;
@end				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.当天总收入;
select @GrossReceipts = (sum(convert(int,money)) - @Oreder4) from dindanbukuan where 0=datediff(day,date,getdate())