admin_api/app/Http/Controllers/Admin/AccCallbackController.php

<?php


namespace App\Http\Controllers\Admin;


use App\Facade\TableName;
use App\Http\helper\NumConfig;
use App\Models\PrivateMail;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;

class AccCallbackController
{

    /*
select '55'+cast(PhoneNum as varchar) as Phone,(CASE
    WHEN CHARINDEX(' ', aw.BankUserName) > 0 THEN SUBSTRING(aw.BankUserName, 1, CHARINDEX(' ', aw.BankUserName) - 1)
    ELSE case when aw.BankUserName is null then 'Jogador' else aw.BankUserName end
    END)
from QPTreasureDB.dbo.GameScoreInfo gi
         left join QPAccountsDB.dbo.AccountsInfo af on gi.UserID = af.UserID
         left join QPAccountsDB.dbo.AccountPhone ap on ap.UserID = gi.UserID
         left join QPRecordDB.dbo.RecordUserTotalStatistics rt on rt.UserID = gi.UserID
         left join QPAccountsDB.dbo.AccountWithDrawInfo as aw on aw.UserID=gi.UserID
where af.Channel = 125
and gi.LastLogonDate < '2023-10-15'
and (rt.Recharge > 40)
--    or rt.Withdraw>0 or gi.Score>2000)
and ap.PhoneNum is not null
-- and gi.Score>5000
order by rt.Recharge desc;

    insert  into QPRecordDB.dbo.AccountsChangeApk (UserID, FromPackage, ToPackage,  ClickTimes, State, Channel) (
select af.UserID as UserID,'com.tiger.ourotiger' as FromPackage,'com.tiger.ourotiger2' as ToPackage,1 as ClickTimes,1 as State,125 as Channel
    from QPTreasureDB.dbo.GameScoreInfo gi
         left join QPAccountsDB.dbo.AccountsInfo af on gi.UserID = af.UserID
         left join QPAccountsDB.dbo.AccountPhone ap on ap.UserID = gi.UserID
         left join QPRecordDB.dbo.RecordUserTotalStatistics rt on rt.UserID = gi.UserID
         left join QPAccountsDB.dbo.AccountWithDrawInfo as aw on aw.UserID=gi.UserID
where af.Channel = 125
and gi.LastLogonDate < '2023-10-15')

    */

    public function sendBonusToAll(){
        $key="temptask_0930";
        if(Redis::exists($key)){
            echo "runover:".$key;
            return;
        }
        $bonus=5;
        $list=DB::table('QPRecordDB.dbo.RecordUserTotalStatistics as rs')
            ->leftJoin("QPAccountsDB.dbo.AccountsInfo as ai", "ai.UserID", "rs.UserID")
            ->whereRaw('rs.Recharge>=10 and ai.Channel = 125')
            ->selectRaw("ai.UserID")
            ->get();
            foreach ($list as $item){
                $this->bonusSendMail($item->UserID,$bonus);
            }
    }
    // 验证码查询
    public function make()
    {
//        echo $this->convertAccentsAndSpecialToNormal( 'Você acabou de receber R$8 bônus,entre no SlotsOuro para reivindicá-lo,se não conseguir entrar,exclua-o e baixe-o novamente,tudo bem.');
//        die;
        $key="taskCallback_013";
        $startid=0;
        if(Redis::exists($key)){
            $startid=Redis::get($key);
        }
//        $list = DB::table("QPTreasureDB.dbo.GameScoreInfo as gi")
//            ->leftJoin("QPAccountsDB.dbo.AccountsInfo as ai", "ai.UserID", "gi.UserID")
//            ->leftJoin("QPAccountsDB.dbo.AccountPhone as ap", "ai.UserID", "ap.UserID")
//            ->leftJoin("QPAccountsDB.dbo.AccountWithDrawInfo as aw", "ai.UserID", "aw.UserID")
//            ->leftJoin("QPRecordDB.dbo.RecordUserTotalStatistics as rs", "ai.UserID", "rs.UserID")
//            ->whereRaw("ai.UserID>$startid and rs.Recharge =0 and gi.Score > 500 and  gi.WinCount > 0 and ai.LastLogonDate < '2023-06-21' and ap.PhoneNum<>'' and ai.LastLogonDate > '2023-06-14'")
//            ->selectRaw("ai.UserID,ai.Channel,ap.PhoneNum,rs.Recharge,rs.Withdraw,ai.Channel,gi.Score,aw.BankUserName,ai.NickName")
//            ->orderBy('ai.UserID')
//            ->limit(2000)
//            ->get();

        $list = DB::table("QPTreasureDB.dbo.GameScoreInfo as gi")
            ->leftJoin("QPAccountsDB.dbo.AccountsInfo as ai", "ai.UserID", "gi.UserID")
            ->leftJoin("QPAccountsDB.dbo.AccountPhone as ap", "ai.UserID", "ap.UserID")
            ->leftJoin("QPAccountsDB.dbo.AccountWithDrawInfo as aw", "ai.UserID", "aw.UserID")
            ->leftJoin("QPRecordDB.dbo.RecordUserTotalStatistics as rs", "ai.UserID", "rs.UserID")
            ->whereRaw("ai.UserID>$startid and rs.Recharge > 20 and ai.LastLogonDate < '2023-10-27' and ap.PhoneNum<>'' and ai.LastLogonDate>'2023-10-20' and ai.Channel in (106) and ai.UserID not in (select UserID from QPRecordDB.dbo.AccountsChangeApk where AccountsChangeApk.State=2 and AccountsChangeApk.Channel=106)")
            ->selectRaw("ai.UserID,ai.Channel,ap.PhoneNum,rs.Recharge,rs.Withdraw,ai.Channel,gi.Score,aw.BankUserName,ai.NickName")
            ->orderBy('ai.UserID')
            ->limit(2000)
            ->get();

        echo "<PRE>";
        $reason = "更新失败召回";
        $lastid=DB::table("QPRecordDB.dbo.AccountsCallbackRecords")->selectRaw("max(ID) ID")->first()->ID;
        $lastid=$lastid??0;
        $lastid+=100000;

        $gameNames=[
            '100'=>'Slots Ouro',
            '101'=>'Slots Ouro',
            '110'=>'Slots Ouro',
            '103'=>'Slots Vencedor',
            '113'=>'Slots Vencedor',
            '104'=>'777 Moedas',
            '106'=>'RIO',
        ];
        $storeNames=[
            '100'=>'Play Store',
            '101'=>'Play Store',
            '110'=>'AppStore',
            '103'=>'Play Store',
            '113'=>'AppStore',
            '104'=>'Play Store',
            '106'=>'Play Store',
        ];

        foreach ($list as $item) {
//            print_r($item);
            $lastid++;
//            $bonus
            if($item->UserID>$startid)$startid=$item->UserID;

            $bonus=3;
            $pay=intval($item->Recharge);
            if($pay>=50000) {
                $bonus = 50;
            }elseif($pay>=10000){
                $bonus = 30;
            }elseif($pay>=5000){
                $bonus = 20;
            }elseif($pay>=1000){
                $bonus = 10;
            }elseif($pay>=100){
                $bonus = 8;
            }elseif($pay>=30){
                $bonus = 5;
            }



            $name=$item->BankUserName;
            if(isset($name)&&$name!=""){
                $name='Querida '.explode(" ",$name)[0].",";
            }else if(substr($item->NickName,0,1)!='U'){
                $name='Querida '.$item->NickName.',';
            }else{
                $name='';
            }
//            Você acabou de receber R$5 bônus, entre imediatamente em Ouro777 para reivindicá-lo, se você não conseguir entrar, exclua-o e baixe-o novamente, tudo bem.


            $gamename=$gameNames[$item->Channel];
            $store=$storeNames[$item->Channel];

//            $msg=" {$gamename}:{$name} entre no jogo para receber seu bônus de {$bonus} moedas. Se você tiver algum problema, basta excluir o jogo e baixá-lo novamente.";
            $code=substr($item->PhoneNum,strlen($item->PhoneNum)-6,4);
//            $msg = "Olá, o '$gamename' foi atualizado e você pode resgatar seu bônus de $bonus reais abrindo a atualização da $store";
//            $msg="Olá, o '$gamename' foi atualizado automaticamente, abra o jogo para resgatar seu bônus exclusivo de $bonus reais!";

            $msg="{".$code."} (OTP) ganhe  R$10 bônus. Caso haja algum problema com o jogo, acesse https://rio.ouro777.com para atualizações.";
            $msg=$this->convertAccentsAndSpecialToNormal($msg);

            $bonus=$bonus*100;
            $cashout= $item->Withdraw??0;
            $record = [
                'UserID' => $item->UserID,
                'Channel' => $item->Channel,
                'Msg' => $msg,
                'PhoneNum' => $item->PhoneNum,
                'Reason' => $reason,
                'Recharge' => $pay,
                'Profit' =>$pay -$cashout / 100,
                'State' => 1,
                'LeftCoin' => $item->Score,
                'Bonus'=>$bonus,
                'TrackToken' => $this->from10_to62($lastid),
//'SendDate'=>$item->,
//'Result'=>$item->,
            ];
            DB::connection("write")->table("QPRecordDB.dbo.AccountsCallbackRecords")->insert($record);
//            $this->bonusSendMail($item->UserID,$bonus);
            print_r($record);
        }
        Redis::set($key,$startid);
        Redis::expire($key,3600);



        return apiReturnSuc();
    }

    /**
     * Replaces special characters in a string with their "non-special" counterpart.
     *
     * Useful for friendly URLs.
     *
     * @access public
     * @param string
     * @return string
     */
    function convertAccentsAndSpecialToNormal($string)
    {
        $table = array(
            'À' => 'A', 'Á' => 'A', 'Â' => 'A', 'Ã' => 'A', 'Ä' => 'A', 'Å' => 'A', 'Ă' => 'A', 'Ā' => 'A', 'Ą' => 'A', 'Æ' => 'A', 'Ǽ' => 'A',
            'à' => 'a', 'á' => 'a', 'â' => 'a', 'ã' => 'a', 'ä' => 'a', 'å' => 'a', 'ă' => 'a', 'ā' => 'a', 'ą' => 'a', 'æ' => 'a', 'ǽ' => 'a',

            'Þ' => 'B', 'þ' => 'b', 'ß' => 'Ss',

            'Ç' => 'C', 'Č' => 'C', 'Ć' => 'C', 'Ĉ' => 'C', 'Ċ' => 'C',
            'ç' => 'c', 'č' => 'c', 'ć' => 'c', 'ĉ' => 'c', 'ċ' => 'c',

            'Đ' => 'Dj', 'Ď' => 'D',
            'đ' => 'dj', 'ď' => 'd',

            'È' => 'E', 'É' => 'E', 'Ê' => 'E', 'Ë' => 'E', 'Ĕ' => 'E', 'Ē' => 'E', 'Ę' => 'E', 'Ė' => 'E',
            'è' => 'e', 'é' => 'e', 'ê' => 'e', 'ë' => 'e', 'ĕ' => 'e', 'ē' => 'e', 'ę' => 'e', 'ė' => 'e',

            'Ĝ' => 'G', 'Ğ' => 'G', 'Ġ' => 'G', 'Ģ' => 'G',
            'ĝ' => 'g', 'ğ' => 'g', 'ġ' => 'g', 'ģ' => 'g',

            'Ĥ' => 'H', 'Ħ' => 'H',
            'ĥ' => 'h', 'ħ' => 'h',

            'Ì' => 'I', 'Í' => 'I', 'Î' => 'I', 'Ï' => 'I', 'İ' => 'I', 'Ĩ' => 'I', 'Ī' => 'I', 'Ĭ' => 'I', 'Į' => 'I',
            'ì' => 'i', 'í' => 'i', 'î' => 'i', 'ï' => 'i', 'į' => 'i', 'ĩ' => 'i', 'ī' => 'i', 'ĭ' => 'i', 'ı' => 'i',

            'Ĵ' => 'J',
            'ĵ' => 'j',

            'Ķ' => 'K',
            'ķ' => 'k', 'ĸ' => 'k',

            'Ĺ' => 'L', 'Ļ' => 'L', 'Ľ' => 'L', 'Ŀ' => 'L', 'Ł' => 'L',
            'ĺ' => 'l', 'ļ' => 'l', 'ľ' => 'l', 'ŀ' => 'l', 'ł' => 'l',

            'Ñ' => 'N', 'Ń' => 'N', 'Ň' => 'N', 'Ņ' => 'N', 'Ŋ' => 'N',
            'ñ' => 'n', 'ń' => 'n', 'ň' => 'n', 'ņ' => 'n', 'ŋ' => 'n', 'ʼn' => 'n',

            'Ò' => 'O', 'Ó' => 'O', 'Ô' => 'O', 'Õ' => 'O', 'Ö' => 'O', 'Ø' => 'O', 'Ō' => 'O', 'Ŏ' => 'O', 'Ő' => 'O', 'Œ' => 'O',
            'ò' => 'o', 'ó' => 'o', 'ô' => 'o', 'õ' => 'o', 'ö' => 'o', 'ø' => 'o', 'ō' => 'o', 'ŏ' => 'o', 'ő' => 'o', 'œ' => 'o', 'ð' => 'o',

            'Ŕ' => 'R', 'Ř' => 'R',
            'ŕ' => 'r', 'ř' => 'r', 'ŗ' => 'r',

            'Š' => 'S', 'Ŝ' => 'S', 'Ś' => 'S', 'Ş' => 'S',
            'š' => 's', 'ŝ' => 's', 'ś' => 's', 'ş' => 's',

            'Ŧ' => 'T', 'Ţ' => 'T', 'Ť' => 'T',
            'ŧ' => 't', 'ţ' => 't', 'ť' => 't',

            'Ù' => 'U', 'Ú' => 'U', 'Û' => 'U', 'Ü' => 'U', 'Ũ' => 'U', 'Ū' => 'U', 'Ŭ' => 'U', 'Ů' => 'U', 'Ű' => 'U', 'Ų' => 'U',
            'ù' => 'u', 'ú' => 'u', 'û' => 'u', 'ü' => 'u', 'ũ' => 'u', 'ū' => 'u', 'ŭ' => 'u', 'ů' => 'u', 'ű' => 'u', 'ų' => 'u',

            'Ŵ' => 'W', 'Ẁ' => 'W', 'Ẃ' => 'W', 'Ẅ' => 'W',
            'ŵ' => 'w', 'ẁ' => 'w', 'ẃ' => 'w', 'ẅ' => 'w',

            'Ý' => 'Y', 'Ÿ' => 'Y', 'Ŷ' => 'Y',
            'ý' => 'y', 'ÿ' => 'y', 'ŷ' => 'y',

            'Ž' => 'Z', 'Ź' => 'Z', 'Ż' => 'Z',
            'ž' => 'z', 'ź' => 'z', 'ż' => 'z',

            '“' => '"', '”' => '"', '‘' => "'", '’' => "'", '•' => '-', '…' => '...', '—' => '-', '–' => '-', '¿' => '?', '¡' => '!', '°' => ' degrees ',
            '¼' => ' 1/4 ', '½' => ' 1/2 ', '¾' => ' 3/4 ', '⅓' => ' 1/3 ', '⅔' => ' 2/3 ', '⅛' => ' 1/8 ', '⅜' => ' 3/8 ', '⅝' => ' 5/8 ', '⅞' => ' 7/8 ',
            '÷' => ' divided by ', '×' => ' times ', '±' => ' plus-minus ', '√' => ' square root ', '∞' => ' infinity ',
            '≈' => ' almost equal to ', '≠' => ' not equal to ', '≡' => ' identical to ', '≤' => ' less than or equal to ', '≥' => ' greater than or equal to ',
            '←' => ' left ', '→' => ' right ', '↑' => ' up ', '↓' => ' down ', '↔' => ' left and right ', '↕' => ' up and down ',
            '℅' => ' care of ', '℮' => ' estimated ',
            'Ω' => ' ohm ',
            '♀' => ' female ', '♂' => ' male ',
            '©' => ' Copyright ', '®' => ' Registered ', '™' => ' Trademark ',
        );

        $string = strtr($string, $table);
        // Currency symbols: £¤¥€  - we dont bother with them for now
        $string = preg_replace("/[^\x9\xA\xD\x20-\x7F]/u", "", $string);

        return $string;
    }
    public static function bonusSendMail($UserID,  $bonus)
    {
        $amount = $bonus;
        $TitleString = 'Por favor, reivindique seu bônus';
        $TextString = "Desejo-lhe sempre boa sorte e ganhe altos multiplicadores!";
        PrivateMail::sendMail(2, $UserID, $TitleString, $TextString, '30000,'.$amount, '', $amount, 3);
    }
    /**
     * 获取该条记录的自增ID
     * 将自增转换为62进制，并拼接网址 如：http://qetee.com/w7e
     * 用户访问到 http://qetee.com/w7e 时，提取短网址后缀 w7e
     * 将短网址后缀转换为10进制，得到自增ID号 如：123456
     * 使用查询该记录，进行业务逻辑处理(比如跳转)
     */

    /**
     * 十进制数转换成62进制
     *
     * @param integer $num
     * @return string
     */
    function from10_to62($num) {
        $to = 62;
        $dict = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $ret = '';
        do {
            $ret = $dict[bcmod($num, $to)] . $ret;
            $num = bcdiv($num, $to);
        } while ($num > 0);
        return $ret;
    }

    /**
     * 62进制数转换成十进制数
     *
     * @param string $num
     * @return string
     */
    function from62_to10($num) {
        $from = 62;
        $num = strval($num);
        $dict = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $len = strlen($num);
        $dec = 0;
        for($i = 0; $i < $len; $i++) {
            $pos = strpos($dict, $num[$i]);
            $dec = bcadd(bcmul(bcpow($from, $len - $i - 1), $pos), $dec);
        }
        return $dec;
    }
    // 绑定手机号 -- 手动
    public function bind_phone(Request $request, $UserID)
    {
        if ($request->isMethod('post')) {

            $post = $request->post();

            $Channel = DB::connection('write')->table(TableName::QPAccountsDB() . 'AccountsInfo')
                ->where('UserID', $UserID)->select('Channel')->first()->Channel;

            $IsUserBindPhone = DB::table(TableName::QPAccountsDB() . 'AccountPhone')
                ->where('UserID', $UserID)
                ->first();
            if ($IsUserBindPhone) {
                return apiReturnFail('用户已绑定');
            }
            DB::table(TableName::QPAccountsDB() . 'AccountPhone')
                ->insert([
                    'PhoneNum' => (int)$post['PhoneNum'],
                    'BindDate' => now(),
                    'LogonPass' => $post['LogonPass'],
                    'UserID' => $UserID,
                    'Channel' => $Channel,
                ]);

            return apiReturnSuc();
        } else {
            return view('admin.code.bind_phone', compact('UserID'));
        }
    }
}