--判断此方案是否已流产，如流产给用户退钱 alter proc SP_Buymoney @schemenumber varchar(50), @sysusername varchar(50), @percent decimal(18,2), --要除去的百分比的钱 @queshstate tinyint , --方案表中的撤单状态 @sysdealtype TinyInt, --网站系统保底的交易类型 @dealtype TinyInt --交易类型 as begin --开始事务 declare @intErrorCode int declare @isweb decimal(18,2) declare @userid_system int declare @sysBlance money declare @rebuymoney_self money --方案发起人跟单的钱 declare @rebuymoney_with money --方案跟单人的钱 declare @selfblance money --用户账上的余额 declare @withmoney money --跟单人的钱 declare @count int --跟单人数量 declare @i int=0 declare @BuyDetailID int --跟单人id declare @RelatedUserID int --跟单用户userid begin try begin transaction select @intErrorCode=@@ERROR --每执行一次任务，接收一次错误信息 declare @selfmoney money--自购金额 declare @amout money--购买的总金额 declare @together money --跟单人总金额 select @selfmoney=BuyedMoney from T_Scheme where SchemeNumber=@schemenumber select @together=sum(Price) from T_BuyDetails where SchemeNumber=@schemenumber--跟单人的总金额 set @amout=@together+@selfmoney--已购买总金额 print @amout --已购买总金额 declare @money money--方案总金额 declare @result money --所差金额 select @money=BuyMonery from T_Scheme where SchemeNumber=@schemenumber set @result=@money-@amout --610 --所差金额 print @result --print @result--总金额减去已买金额剩下的钱）（此方案还差的钱）--算还差多少。从保底里减相对应差的钱、如保底不够刚全减走。。 declare @diff money declare @userid int declare @freeze money declare @vacany money --最终空缺的钱。 declare @diffmoney money --标记变量用于退款 select @diff=AssureMonery from T_Scheme where SchemeNumber=@schemenumber --保底的钱 print @diff select @userid=UserID from T_Scheme where SchemeNumber=@schemenumber print @userid select @freeze=Freeze from T_Users where UserID=@userid print @freeze if(@result>=@diff) --空缺所差的钱大于或等于保底金额 begin set @amout=@amout+@diff --加上保底后已买的总金额 select @intErrorCode=@@ERROR set @vacany=@result-@diff --最终空缺的钱（加上保底后还差的钱）。 print @vacany select @intErrorCode=@@ERROR end else --保底的钱大于所差的钱。 begin set @amout=@amout+@result ---所有购买方案的钱（加上保底的） print @amout select @intErrorCode=@@ERROR end declare @Demwer int select @userid_system=UserID from T_Users where Username=@sysusername if(@money<=@amout) begin --可以出票了 set @vacany=0 ---表示不方案总金额与购买金额这间不差钱 set @Demwer=0 update T_Scheme set IsFullCommission=1,QuashStatus=0 where @money<=@amout and SchemeNumber=@schemenumber --可以出票把是否满员：改为已满员 未撤单 ---已满员。把用户购买彩票的钱从冻结金额里面减去，加到系统账户余额里面。 if(@result>=@diff) begin update T_Users set Freeze=Freeze-@diff-@selfmoney where UserID=@userid --冻结金额减去空缺的保底钱 减去自购的钱 set @diffmoney=@diff end else begin update T_Users set Freeze=Freeze-@result-@selfmoney,Balance=Balance+(@diff-@result) where UserID=@userid --冻结金额要减去空缺保底的钱,把多余的钱退还给用户 set @diffmoney=@result --从保底中减去的钱 end --（加上保底后已够方案总金额：满员）发起人跟系统加钱 update T_Users set Balance=Balance+@selfmoney+@diffmoney where Username=@sysusername select @sysBlance=Balance from T_Users where UserID=@userid_system insert into T_UserDealDetails(UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@userid_system,@sysdealtype,@vacany,@schemenumber,@userid,@sysBlance) --（加上保底后已够方案总金额：满员）跟单人给系统加钱 select @count= COUNT(ID) from T_BuyDetails where SchemeNumber=@schemenumber print @count set @i=0 while(1=1) begin select @BuyDetailID=ID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @RelatedUserID=UserID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @withmoney=Price from T_BuyDetails where ID=@BuyDetailID declare @c money select @c= Freeze from T_Users where UserID=@RelatedUserID if (@c-@withmoney)<=0 begin break end print convert(varchar(20),@c)+'-'+convert(varchar(20),@withmoney)+'='+convert(varchar(20),(@c-@withmoney)) --update T_Users set Freeze=Freeze-@withmoney where UserID=@RelatedUserID set @selfblance=0 select @selfblance=Balance from T_Users where UserID=@RelatedUserID insert into T_UserDealDetails (UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@RelatedUserID,@dealtype,@withmoney,@schemenumber,@userid,@selfblance) --print @BuyDetailID --print('delete T_BuyDetails where UserID='+@RelatedUserID+' and SchemeNumber='''+@schemenumber+''' and ID='+@BuyDetailID+'') print 'd-e' --print 'delete T_BuyDetails where UserID='+@RelatedUserID+' and SchemeNumber='+@schemenumber+' and ID='+@BuyDetailID+'' set @i=@i+1 if @i=@count begin return end print @i end end else --购买金额不等于方案总金额 begin --判断是否大于90%自己认购金额+跟单金额/总金额 set @isweb=@amout/@money if(@isweb>@percent)--网站保底减钱记录（交易类型为13 ，购买彩票），@vancy begin update T_Users set Balance=Balance-@vacany where UserID=@userid_system select @sysBlance=Balance from T_Users where UserID=@userid_system insert into T_UserDealDetails(UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@userid_system,@sysdealtype,@vacany,@schemenumber,@userid,@sysBlance) set @Demwer=0 update T_Scheme set IsFullCommission=1,QuashStatus=0 where @money<=@amout and SchemeNumber=@schemenumber --网站保底后也可以出票把是不否满员：改为已满员 ---已满员。把用户购买彩票的钱从冻结金额里面减去，加到系统账户余额里面。 if(@result>=@diff) begin update T_Users set Freeze=Freeze-@diff-@selfmoney where UserID=@userid --冻结金额减去空缺的保底钱 减去自购的钱 set @diffmoney=@diff end else begin update T_Users set Freeze=Freeze-@result-@selfmoney,Balance=Balance+(@diff-@result) where UserID=@userid --冻结金额要减去空缺保底的钱,把多余的钱退还给用户 set @diffmoney=@result --从保底中减去的钱 end --（加上保底后已够方案总金额：满员）发起人跟系统加钱 update T_Users set Balance=Balance+@selfmoney+@diffmoney where Username=@sysusername select @sysBlance=Balance from T_Users where UserID=@userid_system insert into T_UserDealDetails(UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@userid_system,@sysdealtype,@vacany,@schemenumber,@userid,@sysBlance) --（加上保底后已够方案总金额：满员）跟单人给系统加钱 select @count= COUNT(ID) from T_BuyDetails where SchemeNumber=@schemenumber print @count set @i=0 while(1=1) begin select @BuyDetailID=ID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @RelatedUserID=UserID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @withmoney=Price from T_BuyDetails where ID=@BuyDetailID update T_Users set Freeze=Freeze-@withmoney where UserID=@RelatedUserID set @selfblance=0 select @selfblance=Balance from T_Users where UserID=@RelatedUserID insert into T_UserDealDetails (UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@RelatedUserID,@dealtype,@withmoney,@schemenumber,@userid,@selfblance) delete from T_BuyDetails where UserID=@RelatedUserID and SchemeNumber=@schemenumber and ID=@BuyDetailID set @i=@i+1 if @i=@count begin return end end return end else --总进度不到90%，此单流产、 begin set @Demwer=1 --表示流产、进行撤单操作 --给发起人退款(此操作退的是方案表中的钱。没退跟单表中的钱) update T_Scheme set QuashStatus=@queshstate where SchemeNumber=@schemenumber update T_Users set Balance=Balance+@selfmoney+@diff,Freeze=Freeze-@selfmoney-@diff where UserID=@userid select @selfblance=Balance from T_Users insert into T_UserDealDetails(UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@userid,@dealtype,@selfmoney+@diff,@schemenumber,@userid,@selfblance) --给跟单人退款 set @count=0 select @count= COUNT(ID) from T_BuyDetails where SchemeNumber=@schemenumber print @count set @i=0 while(@count>@i) begin select @BuyDetailID=ID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @RelatedUserID=UserID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @withmoney=Price from T_BuyDetails where ID=@BuyDetailID update T_Users set Balance=Balance+@withmoney,Freeze=Freeze-@withmoney where UserID=@RelatedUserID set @selfblance=0 select @selfblance=Balance from T_Users where UserID=@RelatedUserID insert into T_UserDealDetails (UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@RelatedUserID,@dealtype,@withmoney,@schemenumber,@userid,@selfblance) delete from T_BuyDetails where UserID=@RelatedUserID and SchemeNumber=@schemenumber and ID=@BuyDetailID set @i=@i+1 if @i=@count begin return end end end end commit transaction end try begin catch --事物回滚 rollback transaction end catch select @Demwer as 'isbill' --是否可出票 end