create PROCEDURE total_mySaleDuty
as
BEGIN
DECLARE @a int,@error int
DECLARE @b int,@errorb int
DECLARE @c_dutyId int;
DECLARE @c_dutyName VARCHAR(100);
DECLARE @c_proxyCount int;
DECLARE @c_saleCount int;
DECLARE @c_buyCount int;
DECLARE @c_count int;
DECLARE @c_userId int;
set @b=1;
set @errorb=0;
set @a=1;
set @error=0;
DECLARE findManager_cur CURSOR for
SELECT userId FROM QPNewLobbyDB.dbo.qzmajiang_duty WHERE dutyLevel = 1
OPEN findManager_cur FETCH next from findManager_cur into @c_userId;
while @@FETCH_STATUS = 0
begin
DECLARE findSaleDuty_cur CURSOR for
SELECT
d.dutyId,
MAX (d.dutyName) AS dutyName,
COUNT (DISTINCT u.userId) AS proxyCount,
SUM (CASE WHEN l.typeId = 1 THEN l.roomCardCount ELSE 0 END) saleCount,
SUM (CASE WHEN l.typeId = 2 THEN c.currentPrice ELSE 0 END) buyCount
FROM QPNewLobbyDB.dbo.qzmajiang_duty d
LEFT JOIN QPNewLobbyDB.dbo.qzmajiang_proxy_user u ON d.dutyId = u.dutyId
LEFT JOIN QPNewLobbyDB.dbo.qzmajiang_proxy_log l ON u.userId = l.fromUserId
AND DATEDIFF(MONTH,l.createTime,GETDATE()) = 0
LEFT JOIN QPNewLobbyDB.dbo.qzmajiang_roomcard_config c ON l.configId = c.configId
WHERE parentDutyId = (SELECT dutyId FROM QPNewLobbyDB.dbo.qzmajiang_duty WHERE userId =@c_userId AND dutyLevel = 1)
GROUP BY d.dutyId
OPEN findSaleDuty_cur FETCH next from findSaleDuty_cur into @c_dutyId,@c_dutyName,@c_proxyCount,@c_saleCount,@c_buyCount;
while @@FETCH_STATUS = 0
begin
select @c_count = count(1) from QPNewLobbyDB.dbo.qzmajiang_history_total where dutyId = @c_dutyId and DATEDIFF(MONTH, createTime, GETDATE())= 0 ;
if(@c_count=0)
begin
insert into QPNewLobbyDB.dbo.qzmajiang_history_total values(@c_dutyId,@c_dutyName,@c_proxyCount,@c_saleCount,@c_buyCount,GETDATE())
end
else
begin
update QPNewLobbyDB.dbo.qzmajiang_history_total set proxyCount=@c_proxyCount,saleCount=@c_saleCount,buyCount=@c_buyCount,createTime=GETDATE()
where dutyId=@c_dutyId and DATEDIFF(MONTH, createTime, GETDATE())= 0
end
set @a=@a+1
set @error= @error + @@ERROR
fetch next from findSaleDuty_cur into @c_dutyId,@c_dutyName,@c_proxyCount,@c_saleCount,@c_buyCount
end
close findSaleDuty_cur
deallocate findSaleDuty_cur
set @b=@b+1
set @errorb= @errorb + @@ERROR
fetch next from findManager_cur into @c_userId
end
close findManager_cur
deallocate findManager_cur
END
