admin_api/app/Models/Order.php

<?php

namespace App\Models;

use App\Facade\TableName;
use App\Http\helper\NumConfig;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Carbon;

class Order extends Model
{
    const TABLE = 'order';
    protected $table = self::TABLE;
    protected $fillable = ['id', 'user_id', 'order_title', 'amount', 'pay_at', 'order_sn', 'order_sn', 'finished_at', 'payment_sn'];
    public $timestamps = false;

    //今日充值总额和人数
    public static function today_pay()
    {
        $today_pay =
            DB::connection('read')->table('agent.dbo.order')->selectRaw('cast(sum(amount) as int) as today_pay_sum,count(DISTINCT user_id) as today_pay_count')
                // ->whereRaw('DATEDIFF(DAY, pay_at, GETDATE())=0')
                ->whereRaw('pay_at>CONVERT(varchar(10),GETDATE(),120)')
                ->lock('with(nolock)')
                ->first();
        return $today_pay;
    }

    //今日充值总额和人数
    public static function today_pay_byChannel()
    {
        $today_pay =[];
        $result=DB::connection('read')->table('agent.dbo.order')
                ->selectRaw('cast(sum(amount) as int) as today_pay_sum,count(DISTINCT user_id) as today_pay_count,Channel')
                ->whereRaw('pay_at>CONVERT(varchar(10),GETDATE(),120)')
                ->lock('with(nolock)')
                ->groupBy('Channel')->get();
//        var_dump($result);die;

        foreach ($result as $p){
            $today_pay[$p->Channel]=$p;
        }
        return $today_pay;
    }

    //昨日充值总额和人数
    public static function yesterday_pay()
    {
        $result = Cache::remember('yesterday_pay', Carbon::tomorrow(), function () {
            return $yesterday_pay =
                DB::connection('read')->table('agent.dbo.order')
                    ->selectRaw('cast(sum(amount) as int) as yesterday_pay_sum,count(DISTINCT user_id) as yesterday_pay_count,Channel')
                    ->whereRaw('DATEDIFF(DAY, pay_at, GETDATE())=1')
                    ->lock('with(nolock)')
                    ->groupBy('Channel')->get();
//                    ->first();
        });
        $yesterday_pay=[];
        foreach ($result as $p){
            $yesterday_pay[$p->Channel]=$p;
        }
        return $yesterday_pay;
    }
    public static function yesterday_pay_now()
    {
        $result = Cache::remember('yesterday_pay_now', 5, function () {
            return $yesterday_pay =
                DB::connection('read')->table('agent.dbo.order')
                    ->selectRaw('cast(sum(amount) as int) as yesterday_pay_sum,count(DISTINCT user_id) as yesterday_pay_count,Channel')
                    ->whereRaw('DATEDIFF(DAY, pay_at, GETDATE())=1 and pay_at<CONVERT(varchar(20),DATEADD(DAY,-1,GETDATE()),120)')
                    ->lock('with(nolock)')
                    ->groupBy('Channel')->get();
        });
        $yesterday_pay=[];
        foreach ($result as $p){
            $yesterday_pay[$p->Channel]=$p;
        }
        return $yesterday_pay;
    }

    //总充值金额
    public static function pay_sum()
    {
        return DB::table(TableName::QPRecordDB() . 'RecordUserTotalStatistics')
            ->where('Recharge', '>', 0)
            ->selectRaw('sum(Recharge) Recharge,count(UserID) payCount')
            ->first();
    }

    //总充值人数
    public static function pay_user_count()
    {
        return $pay_user_count =
            DB::connection('read')->table('QPAccountsDB.dbo.YN_VIPAccount')
                ->selectRaw('count(UserID) pay_user_count')
                ->first()->pay_user_count;
    }

    //d0新增付费人数
    public static function d0_day_pay()
    {
        $pay = DB::connection('read')->table(DB::raw('agent.dbo.[order] as a WITH (NOLOCK)'))
            ->selectRaw('
            cast(sum(a.amount)/100 as int) as d0_pay_num ,
            count(DISTINCT a.user_id) as day_pay_count,
            a.Channel')
            ->leftJoin(DB::raw('QPAccountsDB.dbo.AccountsInfo as ai WITH (NOLOCK)'), 'ai.UserID', '=', 'a.user_id')
            ->whereRaw('RegisterDate>=CONVERT(varchar(10),GETDATE(),120) and a.pay_at>CONVERT(varchar(10),GETDATE(),120)')
            ->groupBy('a.Channel')->get()->toArray();

        $withdraw = DB::connection('read')->table(DB::raw('QPAccountsDB.dbo.OrderWithDraw as ow WITH (NOLOCK)'))
            ->selectRaw('
             sum(cast(ow.WithDraw as int))/100  payout,
             sum(cast(ow.ServiceFee as int))/100 fee,
             count(ow.RecordID)               payout_count,
             count(distinct (ow.UserID))    payout_user_count,
             ai.Channel')
            ->leftJoin(DB::raw('QPAccountsDB.dbo.AccountsInfo as ai WITH (NOLOCK)'), 'ai.UserID', '=', 'ow.UserID')
            ->whereRaw('RegisterDate>=CONVERT(varchar(10),GETDATE(),120) ')
            ->groupBy('ai.Channel')->get()->toArray();
        $result=[];
        foreach ($pay as $value){
            $value=(array)$value;
            $result[$value['Channel']]=$value;
        }
        foreach ($withdraw as $value){
            $value=(array)$value;
            if(isset($result[$value['Channel']])){
                $result[$value['Channel']]=array_merge($result[$value['Channel']],$value);
            }else{
                $result[$value['Channel']]=$value;
            }

        }
        foreach ($result as &$item){
            $item['payout']=$item['payout']??0;
            $item['fee']=$item['fee']??0;
            $item['payout_count']=$item['payout_count']??0;
            $item['payout_user_count']=$item['payout_user_count']??0;
            $item['d0_pay_num']=$item['d0_pay_num']??0;
            $item['day_pay_count']=$item['day_pay_count']??0;
            $item['ltv_now']=$item['d0_pay_num']-$item['payout'];

        }
        return $result;

    }
    public static function d0_day_pay_num()
    {
        return $day_pay_count = DB::connection('read')->table('agent.dbo.order as a')
            ->selectRaw('count(DISTINCT a.user_id) as day_pay_count,a.Channel')
            ->leftJoin("QPAccountsDB.dbo.AccountsInfo as ai","ai.UserID","=","a.user_id")
            ->whereRaw('RegisterDate>=CONVERT(varchar(10),GETDATE(),120) and a.pay_at>CONVERT(varchar(10),GETDATE(),120)')
            ->lock('with(nolock)')
            ->groupBy('a.Channel')
            ->pluck('day_pay_count','Channel')->toArray();
    }
    //d0新增付费人数
    public static function d0_yday_pay_count()
    {
        return Cache::remember('d0_yday_pay_count1', Carbon::tomorrow(), function () {
            return $day_pay_count = DB::connection('read')->table('agent.dbo.order as a')

                ->selectRaw('count(DISTINCT a.user_id) as day_pay_count,a.Channel')
                ->leftJoin("QPAccountsDB.dbo.AccountsInfo as ai","ai.UserID","=","a.user_id")
                ->whereRaw('RegisterDate>=CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)')
                ->whereRaw('RegisterDate<CONVERT(varchar(10),GETDATE(),120)')
                ->whereRaw('a.pay_at<CONVERT(varchar(10),GETDATE(),120)')

                ->lock('with(nolock)')
                ->groupBy('a.Channel')
                ->pluck('day_pay_count','Channel')->toArray();
//                ->first()->day_pay_count;
        });

    }
    //d0新增付费人数
    public static function d0_yday_pay_count_now()
    {
        return  DB::connection('read')->table('agent.dbo.order as a')
            ->selectRaw('count(DISTINCT a.user_id) as day_pay_count,a.Channel')
           ->leftJoin("QPAccountsDB.dbo.AccountsInfo as ai","ai.UserID","=","a.user_id")
            ->whereRaw('RegisterDate>=CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)')
            ->whereRaw('a.pay_at<CONVERT(varchar(20),DATEADD(DAY,-1,GETDATE()),120)')
            ->lock('with(nolock)')
            ->groupBy('a.Channel')
            ->pluck('day_pay_count','Channel')->toArray();
//            ->first()->day_pay_count;
    }
    //今日新增付费人数
    public static function day_pay_count()
    {
        return $day_pay_count = DB::connection('read')->table('agent.dbo.order as a')
            ->selectRaw('count(DISTINCT a.user_id) as day_pay_count,Channel')
            ->whereNotIn('a.user_id', function ($query) {
                $query->select('b.user_id')
                    ->from('agent.dbo.order as b')
                    // ->whereRaw('DATEDIFF(DAY, b.pay_at, GETDATE())>0')
                    ->whereRaw('b.pay_at<CONVERT(varchar(10),GETDATE(),120)')
                    ->lock('with(nolock)')
                    ->groupBy('b.user_id');
            })
            // ->whereRaw('DATEDIFF(DAY, a.pay_at, GETDATE())=0')
            ->whereRaw('a.pay_at>CONVERT(varchar(10),GETDATE(),120)')
            ->groupBy('Channel')
            ->lock('with(nolock)')
            ->pluck('day_pay_count','Channel')->toArray();
//            ->first()->day_pay_count;
    }

    //昨日新增付费人数
    public static function yday_pay_count()
    {
        $yday_pay_count = Cache::remember('yday_pay_count', Carbon::tomorrow(), function () {
            return $yday_pay_count = DB::connection('read')->table('agent.dbo.order as a')
                ->selectRaw('count(DISTINCT a.user_id) as yday_pay_count,Channel')
                ->whereNotIn('a.user_id', function ($query) {
                    $query->select('b.user_id')
                        ->from('agent.dbo.order as b')
                        ->whereRaw('DATEDIFF(DAY, b.pay_at, GETDATE())>1')
                        ->lock('with(nolock)')
                        ->groupBy('b.user_id');
                })
                ->whereRaw('DATEDIFF(DAY, a.pay_at, GETDATE())=1')
                ->groupBy('Channel')
                ->lock('with(nolock)')
                ->pluck('yday_pay_count','Channel')->toArray();
//                ->first()->yday_pay_count;
        });
        return $yday_pay_count;
    }
    public static function yday_pay_count_now()
    {
        $yday_pay_count = Cache::remember('yday_pay_count_now', 5, function () {
            return $yday_pay_count = DB::connection('read')->table('agent.dbo.order as a')
                ->selectRaw('count(DISTINCT a.user_id) as yday_pay_count,Channel')
                ->whereNotIn('a.user_id', function ($query) {
                    $query->select('b.user_id')
                        ->from('agent.dbo.order as b')
                        ->whereRaw('DATEDIFF(DAY, b.pay_at, GETDATE())>1')
                        ->lock('with(nolock)')
                        ->groupBy('b.user_id');
                })
                ->whereRaw('DATEDIFF(DAY, a.pay_at, GETDATE())=1')
                ->whereRaw('a.pay_at<CONVERT(varchar(20),DATEADD(DAY,-1,GETDATE()),120)')
                ->groupBy('Channel')
                ->lock('with(nolock)')
                ->pluck('yday_pay_count','Channel')->toArray();
//                ->first()->yday_pay_count;
        });
        return $yday_pay_count;
    }

    // 返回礼包名称
    public static function GiftsName($GiftsID)
    {
        switch ($GiftsID) {

            case 301:
                $GiftsName = '首充';
                break;
            case 302:
                $GiftsName = '破产礼包';
                break;
            case 303:
                $GiftsName = '每日首充礼包';
                break;
            case 304:
                $GiftsName = '每日礼包';
                break;
            case 305:
                $GiftsName = '召回礼包';
                break;
            case 306:
                $GiftsName = 'freebonus礼包';
                break;
            case 401:
                $GiftsName = '大转盘';
                break;
            case 402:
                $GiftsName = '圣诞礼包';
                break;

            default:
                $GiftsName = '商城(充值)';
                break;
        }
        return $GiftsName;
    }

    public static function getUserUnPayOrder($uid){
        $date = date('Y-m-d H:i:s',time()-3600*2);
        $sql = "SELECT payment_code,count(1) as tt FROM [order] WHERE user_id=$uid and created_at>'$date' group by payment_code";
        $data = DB::select($sql);
        return $data?json_decode(json_encode($data),true):[];
    }

    public static function getUserPayOrder($uid){
        $sql = "SELECT payment_code,count(1) as tt FROM [order] WHERE user_id=$uid and pay_status=1 group by payment_code ";
        $data = DB::select($sql);
        return $data?json_decode(json_encode($data),true):[];
    }
}