-- =============================================
-- Author: tanghong
-- Create date: 20130628154520
-- =============================================
CREATE PROCEDURE [dbo].[PersonalPrizeCount]
@EndDate datetime,
@iGroup int,
@TradeSum money out,
@commission money out,
@realsum money out
AS
BEGIN
SET NOCOUNT ON;
set @TradeSum = 0
set @commission = 0
set @realsum = 0
declare @EquiNO int
--declare @prize int
declare equipment_cursor SCROLL CURSOR FOR
select EquiNo from Equipment where groupno in ( select id from GetGroupTreeAllSons(@iGroup) ) for read only
open equipment_cursor
fetch from equipment_cursor into @EquiNO
while @@fetch_status=0
begin
declare @tempsum money
declare @tempcommsion money
declare @temprealsum money
declare @LastTradeID int
declare @newLastTradeid int
select @LastTradeID = isnull(MAX(LastTradeID),0) from statPrizeCount where EquiNo = @EquiNo
select @tempsum = isnull(sum(tradesum),0), @newLastTradeid = ISNULL(max(tradeid), 0) from tradelist where equipmentno = @EquiNo and tradeid > @LastTradeID and referdate < @EndDate
set @tradesum = @tempsum + @tradesum
declare @money0 money, @money1 money, @money2 money, @money3 money, @money4 money, @money5 money
declare @prize0 float, @prize1 float, @prize2 float, @prize3 float, @prize4 float, @prize5 float
select @money0 = isnull(money0,0), @prize0 = isnull(sameMoney,0),
@money1 = isnull(money,0), @prize1 = isnull(prize,0),
@money2 = isnull(money2,0), @prize2 = isnull(prize2,0),
@money3 = isnull(money3,0), @prize3 = isnull(prize3,0),
@money4 = isnull(money4,0), @prize4 = isnull(prize4,0),
@money5 = isnull(money5,0), @prize5 = isnull(prize5,0)
from prize where groupid = @iGroup
if @tempsum <= @money0
set @tempcommsion = @prize0
else if @tempsum <= @money1
set @tempcommsion = @tempsum * @prize1 / 100
else if @tempsum <= @money2
set @tempcommsion = @tempsum * @prize2 / 100
else if @tempsum <= @money3
set @tempcommsion = @tempsum * @prize3 / 100
else if @tempsum <= @money4
set @tempcommsion = @tempsum * @prize4 / 100
else if @tempsum <= @money5
set @tempcommsion = @tempsum * @prize5 / 100
else
set @tempcommsion = 0
set @commission = @tempcommsion + @commission
set @temprealsum = @tempsum - @tempcommsion
declare @ddd datetime;
select @ddd = EndDate from statprizecount where LastTradeID = @LastTradeID and EquiNo = @EquiNo
insert into statprizecount(EquiNo, EndDate, TradeSum, Commsion, RealSum, LastTradeID, OperTime, startDate)
values(@EquiNO, @EndDate, @tempsum, @tempcommsion, @temprealsum, @newLastTradeid, GETDATE(), @ddd)
print @EquiNO
fetch from equipment_cursor into @EquiNO
end
close equipment_cursor
deallocate equipment_cursor
set @realsum = @TradeSum - @commission
END
GO
调用代码
double t1,t2,t3;
strSql.Format(_T("declare @t1 money, @t2 money, @t3 money EXECUTE PersonalPrizeCount '%s',%d,@t1 out, @t2 out, @t3 out select 't1' = @t1, 't2' = @t2, 't3' = @t3"), LPCTSTR(strDtTo), m_iGroup);
CADORecordset rst(&CGenericBasic::m_DataBase);
rst.Open(LPCTSTR(strSql));
if(!rst.IsEOF())
{
rst.GetFieldValue(0, t1);
rst.GetFieldValue(1, t2);
rst.GetFieldValue(2, t3);
}
create procedure [dbo].[ComputeRefund]
@accountno int,
@subsidyrefund money out,
@cashrefund money out
as
begin
set nocount on;
set @cashrefund = 0
set @subsidyrefund = 0
--删除临时表
if OBJECT_ID('tempdb..#aaa') is not null Begin
drop table #aaa
end
SELECT
IDENTITY(INT,1,1) as seq,--添加自增列
CONVERT(CHAR(7), DATETIME, 120) AS 月份,
ACCOUNTNO,
cast(0 as money) as 原补助结余,
cast(0 as money) as 原现金结余,
SUM(CASE TRADETYPE WHEN 1 THEN TRADESUM WHEN 2 THEN TRADESUM ELSE 0 END) AS 充值,
SUM(CASE TRADETYPE WHEN 5 THEN TRADESUM ELSE 0 END) AS 补助,
SUM(CASE TRADETYPE WHEN 132 THEN TRADESUM ELSE 0 END) AS 消费,
cast(0 as money) as 补助结余,
cast(0 as money) as 现金结余
into #aaa
FROM TradeList
WHERE (ACCOUNTNO = @accountno)
GROUP BY CONVERT(CHAR(7), DATETIME, 120), ACCOUNTNO
ORDER BY ACCOUNTNO
declare per_cursor cursor for select seq, 原补助结余, 原现金结余, 充值, 补助, 消费, 补助结余, 现金结余 from #aaa
declare @seq int, @原补助结余 money, @原现金结余 money, @充值 money, @补助 money, @消费 money, @补助结余 money, @现金结余 money
OPEN per_cursor
FETCH NEXT FROM per_cursor INTO @seq, @原补助结余, @原现金结余, @充值, @补助, @消费, @补助结余, @现金结余
WHILE @@FETCH_STATUS = 0
BEGIN
--print @seq
if (@消费 <= (@原补助结余+@补助)) begin
set @补助结余 = (@原补助结余+@补助-@消费)
set @现金结余 = (@原现金结余+@充值)
end
else begin
set @补助结余 = 0
set @现金结余 = (@原补助结余+@原现金结余+@充值+@补助-@消费)
end
--print @现金结余
update #aaa set 补助结余 = @补助结余, 现金结余 = @现金结余 where seq = @seq
IF exists (SELECT seq from #aaa where seq = @seq + 1)
update #aaa set 原补助结余 = @补助结余, 原现金结余 = @现金结余 where seq = @seq + 1
FETCH NEXT FROM per_cursor INTO @seq, @原补助结余, @原现金结余, @充值, @补助, @消费, @补助结余, @现金结余
END
CLOSE per_cursor
DEALLOCATE per_cursor
set @subsidyrefund = @补助结余
set @cashrefund = @现金结余
end