admin_api/app/Services/StoredProcedure.php

<?php


namespace App\Services;

use App\Facade\TableName;
use App\Http\helper\NumConfig;
use App\Models\AccountsInfo;
use App\Notification\TelegramBot;
use App\Util;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use PHPUnit\Util\RegularExpressionTest;

// 执行存储过程
class StoredProcedure
{
    /**
     * 用户单控标签
     * @param int $UserID
     * @param int $type -- 1,充值  2.提现 3.单控 4.无  5.单控结束  6.进其他1  7.进其他2  8.出
     * @param $value
     *
     */
    public static function user_label($UserID, $type, $value = 0)
    {
        if ($type == 2) {   // 提现和充值  统一 元
            $value = $value / NumConfig::NUM_VALUE;
        }
        // DB::connection('write')->select("SET NOCOUNT ON use QPAccountsDB exec CheckAccountsLabel $UserID,$type,$value,1");
    }

    /**
     * 受限用户获取标签
     * @param int $UserID 用户ID
     * @return array
     */
    public static function GetUserTabTypeEx($UserID)
    {
        $result = DB::connection('write')->select("SET NOCOUNT ON use QPTreasureDB exec GSP_GP_GetUserTabTypeEx $UserID");
        return $result;
    }

    // 执行存储过程，-- 防刷处理机制
    public static function SetUserTabType($UserID)
    {
        $result = DB::connection('write')->select("SET NOCOUNT ON use QPAccountsDB exec GSP_GP_SetUserTabState  $UserID,13");
        return $result;
    }

    // 查询用户标签
    public static function getUserTab($UserID)
    {
        $result = DB::connection('write')->select("SET NOCOUNT ON use QPAccountsDB exec GSP_GP_GetUserTabAllType  $UserID");
        return $result;
    }

    // 周卡
    public static function BuyMonthCard($UserID, $CardID, $orderSN)
    {
        $result = DB::connection('write')->select("SET NOCOUNT ON use QPPlatformDB exec GSP_GP_BuyMonthCard  $UserID,$CardID,$orderSN");
        return $result;
    }

    /**
     * 首充10元存储
     * @param $UserID // 用户ID
     * @param $RechargeNum // 充值金额  分
     * @param int $type // 1、购买验证[能不能充10元首充]  2、购买完成[首充成功后调用]
     * @return array
     */
    public static function FirstCharge($UserID, $RechargeNum = 0, $type = 1)
    {
        if ($type == 1) {   // 购买时的判断
            $result = DB::connection('write')->select("SET NOCOUNT ON use QPAccountsDB exec GSP_GR_RechargeActiveCheck  $UserID");
        } else {
            $result = DB::connection('write')->select("SET NOCOUNT ON use QPAccountsDB exec GSP_GR_RechargeActiveInit  $UserID,$RechargeNum");
        }
        return $result;
    }

    /**
     * 网页注册-- 裂变
     * @param string $Accounts 用户帐号
     * @param string $NickName 用户昵称
     * @param string $Spreader 推荐帐号
     * @param string $LogonPass 登录密码
     * @param string $InsurePass 银行密码
     * @param int $FaceI 头像标识
     * @param int $Gender 用户性别
     * @param string $PassPortID 身份证号
     * @param string $Compellation 真实名字
     * @param string $ClientIP 连接地址
     * @param string $MachineID 机器标识
     * @param int $Type 注册类型，0：facebook(微信),1：游客,2：手机号
     * @param int $Channel 渠道 0、100 官方渠道
     * @param string $PackageName 包名
     * @return mixed
     */
    public static function ALLRegisterAccounts($Accounts, $NickName, $Spreader, $LogonPass, $InsurePass, $FaceI, $Gender, $PassPortID, $Compellation, $ClientIP, $MachineID, $Type, $Channel, $PackageName)
    {

        $sql = "DECLARE  @return_value int
        set nocount on
        EXEC  @return_value =QPAccountsDB.dbo.GSP_GP_ALLRegisterAccountsN '$Accounts',$NickName,$Spreader, '$LogonPass','$InsurePass', $FaceI, $Gender, $PassPortID, '$Compellation', '$ClientIP', '$MachineID', $Type, $Channel, '$PackageName', 0,1
        SELECT 'ReturnValue' = @return_value";

        $maxRetries = 2;
        $retryCount = 0;
        $success = false;
        while ($retryCount < $maxRetries && !$success) {
            try {
                $dbh = DB::connection()->getPdo();

                $dbh->exec("USE QPAccountsDB");
                $stmt = $dbh->prepare($sql);

                $stmt->execute();
                $retval = $stmt->fetch(\PDO::FETCH_ASSOC);
                return $retval;
            } catch (\Exception $e) {
                //已经有数据立马返回
                $retval = AccountsInfo::where('Accounts', $Accounts . $Channel)->first();
                if ($retval) {
                    $retval = $retval->toArray();
                    return $retval;
                }

                if ($e->getCode() == '40001') { // Deadlock error code
                    $retryCount++;
                    sleep(1); // Wait for a second before retrying
                    continue;
                }
                Log::error("Reg Error :" . $e->getMessage() . "|||" . $sql . "|||" . json_encode($retval));
            }

        }


        return $retval;
    }


    /**
     * 绑定手机号
     * @param string $phone 手机号
     * @param string $LogonPass 登录密码
     * @param int $UserID 登录密码
     * @return mixed
     */
    public static function bindSms($phone, $LogonPass, $UserID)
    {
        $dbh = DB::connection()->getPdo();

        $stmt = $dbh->prepare("DECLARE  @return_value int
        set nocount on use QPAccountsDB
        EXEC  @return_value = GSP_GP_BindSmsDirect '$phone', '$LogonPass',$UserID
        SELECT 'ReturnValue' = @return_value");

        $stmt->execute();

        $retval = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $retval;
    }


    /**
     * 裂变 -- 充值返利 获取可领额度
     * @param $UserID
     * @param $Type -- 1下级，2下下级
     * @return array
     */
    public static function recharge($UserID, $Type)
    {
        $result = DB::connection('write')->select("SET NOCOUNT ON use QPAccountsDB exec AgentQueryUserList  $UserID,$Type");
        return $result;
    }

    /**
     * 裂变 -- 领取充值额度
     * @param $UserID
     * @param $Type
     * @return array
     */
    public static function receiveRecharge($UserID, $Type)
    {
        $result = DB::connection('write')->select("SET NOCOUNT ON use QPAccountsDB exec AgentRebateWithDraw  $UserID,$Type");
        return $result;
    }

    /**
     * 获取登陆IP
     * @param $PackageName
     * @return array
     */
    public static function getLoginIPMap($PackageName)
    {
        $result = DB::connection('write')->select("SET NOCOUNT ON use QPPlatformDB exec GSP_GR_GetLoginIPMap '{$PackageName}'");
        return $result;
    }


    public static function addPlatformData($UserID, $Type, $Money)
    {
        Log::info('addPlatformData执行：'.$Type,compact("Type","Money"));

        $isFirst = $Extra = $Channel = 0;
        if ($Type == 3) {
            $isFirst = DB::table(TableName::QPRecordDB() . 'RecordUserDataStatisticsNew')
                ->where('UserID', $UserID)
                ->where('DateID', date('Ymd'))
                ->value('Recharge');
        } elseif ($Type == 4) {
            $isFirst = DB::table(TableName::QPRecordDB() . 'RecordUserDataStatisticsNew')
                ->where('UserID', $UserID)
                ->where('DateID', date('Ymd'))
                ->value('Withdraw');

            Util::WriteLog('addPlatformData_with',$isFirst);
        }

        // 查找用户渠道
        $Channel = DB::connection('write')->table(TableName::QPAccountsDB() . 'AccountsInfo')
            ->where('UserID', $UserID)->select('Channel', 'RegisterDate')->first();

        $RegisterDate = $Channel->RegisterDate ?? '';
        $Channel = $Channel->Channel ?? 0;
        if (empty((int)$isFirst)) $Extra = 1;

        if($UserID == 4293017 && $Type == 3){
//            $Channel = 101;
        }

        $result = DB::connection('write')->select("DECLARE  @return_value int
        SET NOCOUNT ON use QPRecordDB exec GSP_GP_AddPlatformData $Type,$Channel,$Money,$Extra
        SELECT 'ReturnValue' = @return_value
        ");


        if ($Type == 3 || $Type == 4) {
            (new \App\Models\RecordPlatformData())->OldUser($UserID, $Channel, $Money, $Extra,$Type);
            (new \App\Models\RecordPlatformData())->Today($Channel, $Money, $RegisterDate, $Extra,$Type);
            if($Type == 3){
                // 广告来源充值统计
                (new AdRecharge())->attribute($UserID, $RegisterDate, $Money,$Extra);

            }

            $callback = DB::table(TableName::agent() . 'UserCallBack')->where('UserID', $UserID)->first();
            if($callback){
                $update = [];
                if($Type == 3){
                    $update = [
                        'recharge' => $callback->recharge+$Money,
                        //'CallbackDate' => date('Y-m-d H:i:s')
                    ];
                }

                if($Type == 4){
                    $update = [
                        'withdraw' => $callback->withdraw+$Money,
                        //'CallbackDate' => date('Y-m-d H:i:s')
                    ];
                }
                if($update)
                    DB::table(TableName::agent() . 'UserCallBack')->where('UserID', $UserID)->update($update);
            }

        }

        return $result;

    }

    /**
     * 用户可提现余额
     * @param $UserID
     * @return mixed
     */
    public static function GetWithDrawLimit($UserID)
    {
        $dbh = DB::connection()->getPdo();

        $stmt = $dbh->prepare("DECLARE  @return_value int
        set nocount on use QPAccountsDB
        EXEC  @return_value = GSP_GR_GetWithDrawLimit $UserID
        SELECT 'ReturnValue' = @return_value");

        $stmt->execute();

        $retval = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $retval;
    }

    public static function QueryVersionNew($CurVersion, $UserID, $Channel, $ChannelIdx, $VersionCode, $UserVPN, $LocalIP, $LocalTime, $PackageName)
    {
        $result = DB::connection('write')->select("SET NOCOUNT ON use QPAccountsDB exec GSP_GP_QueryVersionNew $CurVersion,$UserID,$Channel,$ChannelIdx,$VersionCode,$UserVPN,$LocalIP,$LocalTime,'$PackageName'");
        return $result;
    }


}