ALTER proc [dbo].[mp_w_RechargePortalPayPal_All]
(
@PayPalOrderNo nvarchar(50), --訂單號(hào)
@nAccountIDFrom int, --充值帳號(hào)
@nAccountIDTo int, --充入帳號(hào)
@cTotalMoney numeric(18,2), --總價(jià) ? ? ? ?用戶所在貨幣總價(jià)
@nMoneyType int, --貨幣類型 ? ?用戶所在貨幣類型 ? ?-- 非system貨幣
@nToGameID int, --充入游戲
@nToGameAreaID int, --充入游戲區(qū)域
@nToGameServerID int, --沖入游戲server
@GameRoleName nvarchar(50) , --沖入游戲角色名
@nVMoney int, --平臺(tái)幣(總)
@nGamePoint int, --游戲幣(總)
@AccountIP nvarchar(50), --充值IP
@AccountName nvarchar(150), --姓名
@AccountEmail nvarchar(150), --郵件
@AccountTel nvarchar(50), --電話
@PayType int, --支付方式
@BuyType int, --購買方式
@rv int out, -- >0成功 -1帳號(hào)不存在 -2帳號(hào)被封或凍結(jié)
@rOrderNo nvarchar(50) out,
@rBalanceMoney numeric(18,2) out
)
as
declare @rate decimal(18,6) --匯率
declare @LocalMoneyType int --系統(tǒng)的當(dāng)?shù)刎泿蓬愋?/p>
declare @orderID int --訂單ID
declare @LocalMoneyPrice numeric(18,2) --系統(tǒng)的當(dāng)?shù)刎泿?/p>
declare @sAccountFrom nvarchar(150) --充值帳戶
declare @sAccountTo nvarchar(150) --充入帳戶
declare @orderNO nvarchar(50) --訂單號(hào)
declare @SKUID nvarchar(58) --SKUID
set @rOrderNo='';
set @rBalanceMoney=0;
--推斷帳號(hào)是否存在
if not exists (select 1 from w_MainAccountInfo where nAccountID=@nAccountIDTo)
begin
set @rv=-1
return
end
--推斷賬號(hào)的狀態(tài)是否正常
if not exists(select 1 from w_MainAccountInfo ma left join w_MainAccountDetailInfo da on ma.NAccountID=da.NAccountID where ma.naccountID =@nAccountIDTo and da.coldtime<getdate() and ma.status=1)
begin
set @rv=-2
end
--推斷訂單號(hào)是否存在,存在則又一次生成
if exists (select 1 from w_OnlinePayOrderInfo where OrderNo=@PayPalOrderNo)
begin
set @PayPalOrderNo=dbo.f_generateOrderNO(@BuyType,@nToGameID,newid())
end
--查詢賬戶信息
select @sAccountFrom=sAccount from w_MainAccountInfo where nAccountID=@nAccountIDFrom
select @sAccountTo=sAccount from w_MainAccountInfo where nAccountID=@nAccountIDTo
--寫入訂單表
set @orderNO = @PayPalOrderNo -- 訂單號(hào)為卡號(hào) ?-- dbo.f_generateOrderNO(1,@cardCountry,newid())
--查詢匯率。將金額兌換為美元
if @nMoneyType <> 99
begin
select top 1 @rate = moneyprice/balanceMoneyPrice,@LocalMoneyType=balanceMoneyType from w_OnlinePayExchangeRate where moneyType = @nMoneyType order by AddDate desc
set @LocalMoneyPrice = @cTotalMoney / @rate
end
else
begin
set @rate=1
set @LocalMoneyPrice =@cTotalMoney
set @LocalMoneyType=@nMoneyType
end
--查詢贈(zèng)送點(diǎn)數(shù)
declare @GiftPoints int
declare @RealnGamePoint int
if(@nToGameID>0)
begin
--exec mp_w_RechargeGiftPoints @orderNO,@nToGameID,@PayType,@nGamePoint,@NaccountIDTo,@GiftPoints output
--declare @INGameServerName nvarchar(10)
--if(@nToGameID=3 or @nToGameID=12 or @nToGameID=18 or @nToGameID=19)
--begin
-- set @INGameServerName = 'S'+CONVERT(nvarchar(10),@GiftServerID)
--end
--exec mp_w_RechargeServerGiftPoints @orderNO,@nToGameID,@PayType,@nGamePoint,@nAccountIDTo,@INGameServerName,@GiftPoints output
exec mp_w_RechargeServerGiftPoints @orderNO,@nToGameID,@PayType,@nGamePoint,@nAccountIDTo,@nToGameServerID,@GiftPoints output
end
else if(@nToGameID=0)
begin
set @GiftPoints=0
end
--手工兌換上線后。調(diào)用存儲(chǔ)過程需刪除
--exec mp_w_RechargeGiftPoints @orderNO,@nToGameID,@PayType,@nGamePoint,@NaccountIDTo,@GiftPoints output
set @RealnGamePoint=@nGamePoint+@GiftPoints
--插入訂單表 OrderState為0
insert into w_OnlinePayOrderInfo(NaccountID,sAccount,OrderNo,MoneyPrice,MoneyType,OrderState
,BuyVMoneyNum,BuyGamePointNum,balanceMoneyPrice,balanceMoneyType,balanceRate,balancePoundage
,AddDate,PayDate,PayType,nAccountID_To,sAccount_To,UserHostIP
,SendVMoneyDate,GameID,MoneyOldPrice,RechargeArea,UserName,UserEmail
,UserTel,RechargeServerID)
values (@NaccountIDFrom,@sAccountFrom,@orderNO,@LocalMoneyPrice,@LocalMoneyType,0
,@nVMoney,@RealnGamePoint,@cTotalMoney,@nMoneyType,@rate,0
,getdate(),0,@PayType,@NaccountIDTo,@sAccountTo,@AccountIP
,0,@nToGameID,0,@nToGameAreaID,@AccountName,@AccountEmail
,@AccountTel,@nToGameServerID)?
select @orderID=@@identity
if(LEN( @AccountName)>58)
SET @SKUID=substring( @AccountName,1,56)
else
SET @SKUID=@AccountName
insert into w_OnlinePayOrderDetailInfo(OrderID,ItemNum,cVmoneyNum,cMoneyPrice,cMoneyType,cGamePointNum) values (@orderID,1,@nVMoney,@cTotalMoney,@nMoneyType,@nGamePoint)
insert into w_OnlinePayBuyerInfo(OrderID,INGameRoleName,INGameServerName,SKUID) values (@orderID,@GameRoleName,@nToGameServerID,@SKUID)
set @rv=@orderID
set @rOrderNo=@orderNO
set @rBalanceMoney=@LocalMoneyPrice
--記錄贈(zèng)送日志
if(@GiftPoints > 0)
begin
update w_OnlinepayGiftPointsHistory set orderid=@orderID,naccountid=@nAccountIDTo,
naccountname=@sAccountTo,rechargedate=getdate() where orderno=@OrderNo
end
----訂單日志
--insert into w_OnlinePayOrderLog(info) values ('PayPal_All訂單:' + @orderNO + '生成成功')
? /// <param name="storedProcName">存儲(chǔ)過程名</param>
? ? ? ? /// <param name="parameters">存儲(chǔ)過程參數(shù)</param>
? ? ? ? /// <param name="rowsAffected">影響的行數(shù)</param>
? ? ? ? /// <returns></returns>
? ? ? ? public static int RunProcedure(string storedProcName, int DataBaseType, IDataParameter[] parameters, out int rowsAffected)
? ? ? ? {
? ? ? ? ? ? using (SqlConnection connection = new SqlConnection(GetDBGameConnstring(DataBaseType)))
? ? ? ? ? ? {
? ? ? ? ? ? ? ? int result;
? ? ? ? ? ? ? ? connection.Open();
? ? ? ? ? ? ? ? SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
? ? ? ? ? ? ? ? command.CommandTimeout = 60;
? ? ? ? ? ? ? ? rowsAffected = command.ExecuteNonQuery();
? ? ? ? ? ? ? ? result = (int)command.Parameters["ReturnValue"].Value;
? ? ? ? ? ? ? ? //Connection.Close();
? ? ? ? ? ? ? ? return result;
? ? ? ? ? ? }
? ? ? ? }
? ? ?public static void OnlineRechargePayPalAll(string OrderNo, int AccountIDFrom, int AccountIDTo, string TotalMoney, int PriceType, int GameID, int GameArea, int AreaServerID, string sGameRoleName, int vMoney, int GamePoint, string IP, string Name, string Email, string Tel, int PayType, int BuyType, out int ErrorCode, out string ReturnOrderNo, out string BalanceMoney)
? ? ? ? {
? ? ? ? ? ? int iRows = 0;
? ? ? ? ? ? SqlParameter[] parsRechargePayPal = {
new SqlParameter("@PayPalOrderNo", SqlDbType.NVarChar,50),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@nAccountIDFrom", SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@nAccountIDTo", SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@cTotalMoney", SqlDbType.Decimal),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@nMoneyType",SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@nToGameID",SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@nToGameAreaID",SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@nToGameServerID",SqlDbType.Int),?
? ? ? ? ? ? ? ? ? ? new SqlParameter("@GameRoleName",SqlDbType.NVarChar,50),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@nVMoney",SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@nGamePoint",SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@AccountIP",SqlDbType.NVarChar,50),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@AccountName",SqlDbType.NVarChar,150),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@AccountEmail",SqlDbType.NVarChar,150),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@AccountTel",SqlDbType.NVarChar,50),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@PayType",SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@BuyType",SqlDbType.Int),
? ? ? ? ? ? ? ? ? ?
? ? ? ? ? ? ? ? ? ? new SqlParameter("@rv",SqlDbType.Int),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@rOrderNo",SqlDbType.NVarChar,50),
? ? ? ? ? ? ? ? ? ? new SqlParameter("@rBalanceMoney",SqlDbType.Decimal)};
? ? ? ? ? ? parsRechargePayPal[0].Value = OrderNo;
? ? ? ? ? ? parsRechargePayPal[1].Value = AccountIDFrom;
? ? ? ? ? ? parsRechargePayPal[2].Value = AccountIDTo;
? ? ? ? ? ? parsRechargePayPal[3].Value = TotalMoney;
? ? ? ? ? ? parsRechargePayPal[4].Value = PriceType;
? ? ? ? ? ? parsRechargePayPal[5].Value = GameID;
? ? ? ? ? ? parsRechargePayPal[6].Value = GameArea;
? ? ? ? ? ? parsRechargePayPal[7].Value = AreaServerID;
? ? ? ? ? ? parsRechargePayPal[8].Value = sGameRoleName;
? ? ? ? ? ? parsRechargePayPal[9].Value = vMoney;
? ? ? ? ? ? parsRechargePayPal[10].Value = GamePoint;
? ? ? ? ? ? parsRechargePayPal[11].Value = IP;
? ? ? ? ? ? parsRechargePayPal[12].Value = Name;
? ? ? ? ? ? parsRechargePayPal[13].Value = Email;
? ? ? ? ? ? parsRechargePayPal[14].Value = Tel;
? ? ? ? ? ? parsRechargePayPal[15].Value = PayType;
? ? ? ? ? ? parsRechargePayPal[16].Value = BuyType;
? ? ? ? ? ? parsRechargePayPal[17].Direction = ParameterDirection.Output;
? ? ? ? ? ? parsRechargePayPal[17].Value = 0;
? ? ? ? ? ? parsRechargePayPal[18].Direction = ParameterDirection.Output;
? ? ? ? ? ? parsRechargePayPal[18].Value = "";
? ? ? ? ? ? parsRechargePayPal[19].Direction = ParameterDirection.Output;
? ? ? ? ? ? parsRechargePayPal[19].Value = "0.0";
? ? ? ? ? ? parsRechargePayPal[19].Precision = 10;
? ? ? ? ? ? parsRechargePayPal[19].Scale = 2;
? ? ? ? ? ? DbHelperSQL.RunProcedure("mp_w_RechargePortalPayPal_All", parsRechargePayPal, out iRows);
? ? ? ? ? ? ErrorCode = CmnProc.getInt(parsRechargePayPal[17].Value);
? ? ? ? ? ? ReturnOrderNo = CmnProc.getString(parsRechargePayPal[18].Value);
? ? ? ? ? ? BalanceMoney = CmnProc.getString(parsRechargePayPal[19].Value);
? ? ? ? }
本文摘自 :https://blog.51cto.com/u