wangdu_public_api/app/Modules/Trade/Models/Order.php

<?php

namespace App\Modules\Trade\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Order extends Model
{
    protected $table = 'orders';

    protected $fillable = ['distribution_channel_id', 'uid', 'created_at', 'product_id', 'price', 'status', 'pay_type', 'trade_no', 'pay_merchant_id', 'servicer', 'transaction_id', 'pay_end_at', 'create_ip', 'send_order_id', 'send_order_name', 'activity_id'];

    static function getByTradeNo($trade_no)
    {
        return self::where(['trade_no' => $trade_no])->first();
    }


    //查询
    static function search($params = [], $is_all = false)
    {
        //     	\Log::info('search:'.json_encode($params));
        $search_obj = self::orderBy('id', 'desc')->select(['orders.*', 'activity.name'])->leftjoin('activity', 'activity.id', '=', 'orders.activity_id');
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('orders.distribution_channel_id', $params['distribution_channel_id']);
        if (isset($params['begin_time']) && $params['begin_time']) $search_obj->where('orders.created_at', '>=', $params['begin_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('orders.created_at', '<=', $params['end_time'] . ' 23:59:59');
        if (isset($params['uid']) && $params['uid']) $search_obj->where('uid', $params['uid']);
        if (isset($params['inner_send_order_id']) && $params['inner_send_order_id']) $search_obj->where('inner_send_order_id', $params['inner_send_order_id']);
        if (isset($params['send_order_id']) && $params['send_order_id']) $search_obj->where('send_order_id', $params['send_order_id']);
        if (isset($params['activity_id']) && $params['activity_id']) $search_obj->where('activity_id', $params['activity_id']);
        if (isset($params['trade_no']) && $params['trade_no']) $search_obj->where('trade_no', $params['trade_no']);
        if (isset($params['transaction_id']) && $params['transaction_id']) $search_obj->where('transaction_id', $params['transaction_id']);
        if (isset($params['status']) && $params['status']) $search_obj->where('status', $params['status']);
        if (isset($params['activity_id']) && count($params['activity_id']) > 0) $search_obj->whereIn('activity_id', $params['activity_id']);
        if (isset($params['from_type']) && $params['from_type']) $search_obj->where('from_type', $params['from_type']);
        /*\Log::info('filter_:'.json_encode($params));
        \Log::info(json_encode($search_obj));
        \Log::info('my_sql:'.($search_obj->toSql()));*/
        //         \Log::info('my_sql:'.($search_obj->toSql()));
        if (isset($params['created_at']) && $params['created_at']) {
            \Log::info('created_at--:' . date('Y-m-d 00:00:00', strtotime($params['created_at'])));
            $search_obj->whereBetween('orders.created_at', [date('Y-m-d 00:00:00', strtotime($params['created_at'])), date('Y-m-d 23:59:59', strtotime($params['created_at']))]);
        }
        if ($is_all) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }

    //查询对象
    static function searchObj($params = [])
    {
        $search_obj = self::orderBy('id', 'desc')->select(['orders.*', 'activity.name'])->leftjoin('activity', 'activity.id', '=', 'orders.activity_id');
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id', $params['distribution_channel_id']);
        if (isset($params['begin_time']) && $params['begin_time']) $search_obj->where('orders.created_at', '>=', $params['begin_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('orders.created_at', '<=', $params['end_time'] . ' 23:59:59');
        if (isset($params['uid']) && $params['uid']) $search_obj->where('uid', $params['uid']);
        if (isset($params['send_order_id']) && $params['send_order_id']) $search_obj->where('send_order_id', $params['send_order_id']);
        if (isset($params['activity_id']) && $params['activity_id']) $search_obj->where('activity_id', $params['activity_id']);
        if (isset($params['trade_no']) && $params['trade_no']) $search_obj->where('trade_no', $params['trade_no']);
        if (isset($params['transaction_id']) && $params['transaction_id']) $search_obj->where('transaction_id', $params['transaction_id']);
        if (isset($params['status']) && $params['status']) $search_obj->where('status', $params['status']);

        if (isset($params['activity_id']) && count($params['activity_id']) > 0) $search_obj->whereIn('activity_id', $params['activity_id']);

        return $search_obj;
    }

    //总额统计
    static function getAmount($params = [])
    {
        $search_obj = self::where('status', 'PAID');
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id', $params['distribution_channel_id']);
        if (isset($params['begin_time']) && $params['begin_time']) $search_obj->where('created_at', '>=', $params['begin_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('created_at', '<=', $params['end_time']);
        if (isset($params['uid']) && $params['uid']) $search_obj->where('uid', $params['uid']);
        if (isset($params['send_order_id']) && $params['send_order_id']) $search_obj->where('send_order_id', $params['send_order_id']);
        if (isset($params['activity_id']) && $params['activity_id']) $search_obj->where('activity_id', $params['activity_id']);
        if (isset($params['distribution_channel_id_array']) && $params['distribution_channel_id_array']) $search_obj->whereIn('distribution_channel_id', $params['distribution_channel_id_array']);
        if (isset($params['not_in_pay_merchant_id']) && count($params['not_in_pay_merchant_id']) > 0) {
            $search_obj->whereNotIn('pay_merchant_id', $params['not_in_pay_merchant_id']);
        } else {
        }

        return (float)$search_obj->sum('price');
    }

    static function getAmountV2($params = [])
    {
        $search_obj = self::join('pay_merchants', 'pay_merchants.id', '=', 'orders.pay_merchant_id')->where('status', 'PAID');
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('orders.distribution_channel_id', $params['distribution_channel_id']);
        if (isset($params['begin_time']) && $params['begin_time']) $search_obj->where('orders.created_at', '>=', $params['begin_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('orders.created_at', '<=', $params['end_time']);
        if (isset($params['uid']) && $params['uid']) $search_obj->where('orders.uid', $params['uid']);
        if (isset($params['send_order_id']) && $params['send_order_id']) $search_obj->where('orders.send_order_id', $params['send_order_id']);
        if (isset($params['activity_id']) && $params['activity_id']) $search_obj->where('orders.activity_id', $params['activity_id']);
        if (isset($params['distribution_channel_id_array']) && $params['distribution_channel_id_array']) $search_obj->whereIn('orders.distribution_channel_id', $params['distribution_channel_id_array']);
        if (isset($params['not_in_pay_merchant_id']) && count($params['not_in_pay_merchant_id']) > 0) {
            $search_obj->whereNotIn('orders.pay_merchant_id', $params['not_in_pay_merchant_id']);
        }
        if (isset($params['is_self_channel']) && $params['is_self_channel']) $search_obj->where('pay_merchants.is_self_channel', $params['is_self_channel']);

        return (float)$search_obj->sum('orders.price');
    }

    //获取渠道当日数据
    static function getChannelToday($distribution_channel_id)
    {
        $begin_time = date('Y-m-d');
        $end_time   = date("Y-m-d", strtotime($begin_time) + 86400);
        return self::select([DB::Raw("sum(price) as success_amount,count(1) as paid_number")])
            ->where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->first();
    }

    //获取渠道当日数据
    static function getChannelsToday($distribution_channel_ids)
    {
        $begin_time = date('Y-m-d');
        $end_time   = date("Y-m-d", strtotime($begin_time) + 86400);
        return self::select([DB::Raw("sum(price) as success_amount,count(1) as paid_number")])
            ->whereIn('distribution_channel_id', $distribution_channel_ids)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->first();
    }

    //获取渠道当日数据
    static function getPaymerchantToday($pay_merchant_id)
    {
        $begin_time = date('Y-m-d');
        $end_time   = date("Y-m-d", strtotime($begin_time) + 86400);
        return self::select([DB::Raw("sum(price) as success_amount,count(1) as paid_number")])
            ->whereIn('pay_merchant_id', $pay_merchant_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->first();
    }

    //获取渠道当日实时统计数据
    static function getChannelTodayData($distribution_channel_id)
    {
        $begin_time = date('Y-m-d');
        $end_time   = date('Y-m-d', strtotime($begin_time) + 86400);
        $paid_info  = self::select([DB::Raw("order_type,sum(price) as success_amount,count(1) as paid_num")])
            ->where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->groupBy('order_type')
            ->get()
            ->toArray();

        $unpaid_info = self::select([DB::Raw("order_type,sum(price),count(1) as unpaid_num")])
            ->where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'UNPAID')
            ->groupBy('order_type')
            ->get()
            ->toArray();

        $ret = [
            'amount'          => 0,
            'paid_num'        => 0,
            'total_order_num' => 0,

            'recharge_unpaid_number'  => 0,
            'recharge_paid_number'    => 0,
            'recharge_success_amount' => 0,

            'year_unpaid_number'  => 0,
            'year_paid_number'    => 0,
            'year_success_amount' => 0

        ];
        foreach ($paid_info as $item) {
            $ret['amount']          += $item['success_amount'];
            $ret['paid_num']        += $item['paid_num'];
            $ret['total_order_num'] += $item['paid_num'];

            if ($item['order_type'] == 'RECHARGE') {
                $ret['recharge_paid_number']    += $item['paid_num'];
                $ret['recharge_success_amount'] += $item['success_amount'];
            }

            if ($item['order_type'] == 'YEAR') {
                $ret['year_paid_number']    += $item['paid_num'];
                $ret['year_success_amount'] += $item['success_amount'];
            }
        }
        foreach ($unpaid_info as $uitem) {
            $ret['total_order_num'] += $uitem['unpaid_num'];

            if ($uitem['order_type'] == 'RECHARGE') $ret['recharge_unpaid_number'] += $uitem['unpaid_num'];

            if ($uitem['order_type'] == 'YEAR') $ret['year_unpaid_number'] += $uitem['unpaid_num'];
        }
        return $ret;
    }

    //获取渠道日数据
    static function getChannelDateOrderInfo($distribution_channel_id, $date)
    {
        $begin_time = $date;
        $end_time   = date("Y-m-d", strtotime($begin_time) + 86400);
        return self::select([DB::Raw("sum(price) as success_amount,count(1) as paid_number")])
            ->where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->first();
    }

    //获取渠道对应支付通道总额
    static function getPayMerchantSourceAmount($params)
    {
        $search_obj = self::select([DB::Raw("pay_merchant_source,sum(price) as success_amount")], 'pay_merchant_source')->where('status', 'PAID');
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id', $params['distribution_channel_id']);
        if (isset($params['begin_time']) && $params['begin_time']) $search_obj->where('created_at', '>=', $params['begin_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('created_at', '<=', $params['end_time']);

        if (isset($params['not_in_pay_merchant_id']) && count($params['not_in_pay_merchant_id']) > 0) {
            $search_obj->whereNotIn('pay_merchant_id', $params['not_in_pay_merchant_id']);
        } else {
        }

        return $search_obj->groupBy('pay_merchant_source')->pluck('success_amount', 'pay_merchant_source')->toArray();
    }

    //获取付费用户数
    static function getPayUserNum($send_order_id)
    {
        return self::where('send_order_id', $send_order_id)->where('status', 'PAID')->distinct('uid')->count('uid');
    }

    //获取普通付费用户数
    static function getNormalPayUserNum($send_order_id)
    {
        return self::where('send_order_id', $send_order_id)->where('order_type', 'RECHARGE')->where('status', 'PAID')->distinct('uid')->count('uid');
    }

    //获取VIP付费用户数
    static function getVipPayUserNum($send_order_id)
    {
        return self::where('send_order_id', $send_order_id)->where('order_type', 'YEAR')->where('status', 'PAID')->distinct('uid')->count('uid');
    }

    //获取派单下的订单总数
    static function getOrderCount($send_order_id)
    {
        return self::where('send_order_id', $send_order_id)->where('status', 'PAID')->count();
    }

    //获取活动的订单总数
    static function getActivityOrderNum($params = [])
    {
        $search_obj = self::orderBy('created_at', 'desc');
        if (isset($params['status']) && $params['status']) $search_obj->where('status', $params['status']);
        if (isset($params['activity_id']) && $params['activity_id']) $search_obj->where('activity_id', $params['activity_id']);
        if (isset($params['begin_time']) && $params['begin_time']) $search_obj->where('created_at', '>=', $params['begin_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('created_at', '<=', $params['end_time']);
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id', $params['distribution_channel_id']);
        return $search_obj->count();
    }

    //获取派单下的普通用户订单总数
    static function getNormalOrderCount($send_order_id)
    {
        return self::where('send_order_id', $send_order_id)->where('order_type', 'RECHARGE')->where('status', 'PAID')->count();
    }

    //获取派单下的VIP用户的订单总数
    static function getVipOrderCount($send_order_id)
    {
        return self::where('send_order_id', $send_order_id)->where('order_type', 'YEAR')->where('status', 'PAID')->count();
    }

    //获取渠道日付费用户数
    static function getDayPayUserNumByChannelId($distribution_channel_id, $date)
    {
        $begin_time = $date;
        $end_time   = date("Y-m-d", strtotime($date) + 86400);
        return self::where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->distinct('uid')
            ->count('uid');
    }

    //获取渠道日包年vip付费用户数
    static function getDayVipPayUserNumByChannelId($distribution_channel_id, $date)
    {
        $begin_time = $date;
        $end_time   = date("Y-m-d", strtotime($date) + 86400);
        return self::where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->where('order_type', 'YEAR')
            ->distinct('uid')
            ->count('uid');
    }

    //获取渠道日普通充值付费用户数
    static function getDayRechargePayUserNumByChannelId($distribution_channel_id, $date)
    {
        $begin_time = $date;
        $end_time   = date("Y-m-d", strtotime($date) + 86400);
        return self::where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->where('order_type', 'RECHARGE')
            ->distinct('uid')
            ->count('uid');
    }

    //获取指定日期普通充值未支付订单数、支付订单数、总额
    static function getChannelDayTicketRechargeData($distribution_channel_id, $date)
    {
        $begin_time   = $date;
        $end_time     = date("Y-m-d", strtotime($date) + 86400);
        $paid_info    = self::select([DB::Raw("sum(price) as success_amount,count(1) as paid_num")])
            ->where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->where('order_type', 'RECHARGE')
            ->first();
        $unpaid_count = self::where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('order_type', 'RECHARGE')
            ->where('status', 'UNPAID')
            ->count();

        return [
            'ticket_recharge_paid_num'   => $paid_info->paid_num,
            'ticket_recharge_amount'     => (float)$paid_info->success_amount,
            'ticket_recharge_unpaid_num' => $unpaid_count
        ];
    }

    //获取指定日期包年充值未支付订单数、支付订单数、总额
    static function getChannelDayYearRechargeData($distribution_channel_id, $date)
    {
        $begin_time   = $date;
        $end_time     = date("Y-m-d", strtotime($date) + 86400);
        $paid_info    = self::select([DB::Raw("sum(price) as success_amount,count(1) as paid_num")])
            ->where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('status', 'PAID')
            ->where('order_type', 'YEAR')
            ->first();
        $unpaid_count = self::where('distribution_channel_id', $distribution_channel_id)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<', $end_time)
            ->where('order_type', 'YEAR')
            ->where('status', 'UNPAID')
            ->count();
        return [
            'year_recharge_paid_num'   => $paid_info->paid_num,
            'year_recharge_amount'     => (float)$paid_info->success_amount,
            'year_recharge_unpaid_num' => $unpaid_count
        ];
    }

    //获取渠道日首充数据
    static function getChannelDayFirstRechargeData($distribution_channel_id, $date)
    {
        $begin_time = $date;
        $end_time   = date("Y-m-d", strtotime($date) + 86400);
        $first_data = DB::select("select count(distinct uid) count,sum(price) amount from orders a where distribution_channel_id = {$distribution_channel_id} and created_at>'{$begin_time}' and created_at<'{$end_time}' and status ='PAID' and not exists (select uid from orders where a.uid = uid and created_at < '{$begin_time}' and status= 'PAID' and distribution_channel_id = {$distribution_channel_id} limit 1)");

        return [
            "count"  => (int)$first_data[0]->count,
            "amount" => (float)$first_data[0]->amount
        ];
    }

    //获取派单累计充值（追踪用户）
    static function getPromotionRegUserRechargeAmount($params = [])
    {
        $send_order_id = isset($params['send_order_id']) ? $params['send_order_id'] : 0;
        $end_time      = isset($params['end_time']) ? $params['end_time'] : date('Y-m-d H:i:s');
        if ($send_order_id) {
            $data = DB::select("select sum(price) amount from orders where status='PAID' and created_at <= '{$end_time}' and uid in (select id from users where send_order_id = '{$send_order_id}' )");
            return (float)$data[0]->amount;
        }
    }

    static function getRechargeAmountGroupByOfficial($date)
    {
        $search_obj = self::orderBy('orders.id', 'desc')->join('force_subscribe_users', 'orders.uid', '=', 'force_subscribe_users.uid')
            ->join('official_accounts', 'force_subscribe_users.appid', '=', 'official_accounts.appid')->groupBy('official_accounts.appid')->groupBy('orders.distribution_channel_id');

        if ($date) {
            $search_obj->where('orders.pay_end_at', '>=', $date . ' 00:00:00');
            $search_obj->where('orders.pay_end_at', '<=', $date . ' 23:59:59');
        }

        $search_obj->where('orders.status', 'PAID');

        $data                   = $search_obj->select(DB::raw('sum(orders.price) as recharge_amount'), 'official_accounts.nickname', 'official_accounts.appid', 'orders.distribution_channel_id')->get()->toArray();
        $chanenelRechargeAmount = [];
        foreach ($data as $item) {
            $distribution_channel_id = $item['distribution_channel_id'];
            if (!isset($chanenelRechargeAmount[$distribution_channel_id])) {
                $chanenelRechargeAmount[$distribution_channel_id] = 0;
            }
            $chanenelRechargeAmount[$distribution_channel_id] += (float)$item['recharge_amount'];
        }

        foreach ($data as &$dataItem) {
            $dataItem['date']       = $date;
            $dataItem['created_at'] = date('Y-m-d H:i:s');
            $dataItem['updated_at'] = date('Y-m-d H:i:s');
            foreach ($chanenelRechargeAmount as $key => $chanenelRechargeAmountItem) {
                if ($dataItem['distribution_channel_id'] == $key) {
                    $dataItem['channel_recharge_amount'] = $chanenelRechargeAmountItem;
                }
            }
        }
        return $data;
    }


    static function getRechargeAmountGroupByOfficialAccount($date)
    {
        $search_obj = self::orderBy('id', 'desc')->where('status', 'PAID')->select('uid', 'price', 'distribution_channel_id');

        if ($date) {
            $search_obj->where('created_at', '>=', $date . ' 00:00:00');
            $search_obj->where('created_at', '<=', $date . ' 23:59:59');
        }
        $result = $search_obj->get();

        $official_accounts_result = DB::table('official_accounts')->select('appid', 'nickname')->get();
        $temp_official_account    = [];
        foreach ($official_accounts_result as $official_accounts_result_item) {
            $temp_official_account[$official_accounts_result_item->appid] = $official_accounts_result_item->nickname;
        }
        $temp_channel               = [];
        $temp_channel_charge_amount = [];
        foreach ($result as $item) {
            if (!isset($temp_channel[$item->distribution_channel_id])) {
                $temp_channel[$item->distribution_channel_id] = [];
            }

            if (!isset($temp_channel_charge_amount[$item->distribution_channel_id])) {
                $temp_channel_charge_amount[$item->distribution_channel_id] = 0;
            }

            $force_subscribe_users_result = DB::table('force_subscribe_users')->where('uid', $item->uid)->where('is_subscribed', 1)->select('appid', 'distribution_channel_id')->first();
            if (!$force_subscribe_users_result) {
                $force_subscribe_users_result = DB::table('force_subscribe_users')->where('uid', $item->uid)->where('is_subscribed', 0)->select('appid', 'distribution_channel_id')->first();
            }
            if ($force_subscribe_users_result) {
                $temp_channel[$item->distribution_channel_id][$force_subscribe_users_result->appid][] = $item->price;
            } else {
                $temp_channel[$item->distribution_channel_id]['unknown'][] = $item->price;
            }
            $temp_channel_charge_amount[$item->distribution_channel_id] += $item->price;
        }
        //\Log::info($temp_channel);
        $finalArray = [];
        foreach ($temp_channel as $key => $temp_channel_item) {
            foreach ($temp_channel_item as $appid => $price) {
                $finalArray[] = [
                    'date'                    => $date,
                    'nickname'                => isset($temp_official_account[$appid]) ? $temp_official_account[$appid] : 'unknown',
                    'appid'                   => $appid,
                    'distribution_channel_id' => $key,
                    'recharge_amount'         => array_sum($price),
                    'channel_recharge_amount' => $temp_channel_charge_amount[$key],
                    'created_at'              => date('Y-m-d H:i:s'),
                    'updated_at'              => date('Y-m-d H:i:s')
                ];
            }
        }

        return $finalArray;
    }

    public static function getRechargeAmountGroupByPayMerchant($params)
    {
        $obj = self::join('pay_merchants', 'pay_merchants.id', '=', 'orders.pay_merchant_id')
            ->where('orders.status', '=', 'PAID');

        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
            $obj->where('orders.distribution_channel_id', $params['distribution_channel_id']);
        }
        if (isset($params['is_self_channel']) && $params['is_self_channel']) {
            $obj->where('pay_merchants.is_self_channel', 1);
        }
        if (isset($params['start_time']) && $params['start_time']) {
            $obj->where('orders.created_at', '>=', $params['start_time']);
        }
        if (isset($params['end_time']) && $params['end_time']) {
            $obj->where('orders.created_at', '<=', $params['end_time']);
        }
        $obj->groupBy('pay_merchants.pay_company_id');
        return $obj->select(DB::raw('sum(orders.price) as amount,orders.distribution_channel_id,pay_company_id,pay_company_name'))->get();
    }


    public static function getPayMerchantRechargeAmount($params)
    {
        $obj = self::where('pay_merchant_id', $params['pay_merchant_id']);

        if (isset($params['status']) && $params['status']) {
            $obj->where('status', $params['status']);
        }
        if (isset($params['start_time']) && $params['start_time']) {
            $obj->where('created_at', '>=', $params['start_time']);
        }
        if (isset($params['end_time']) && $params['end_time']) {
            $obj->where('created_at', '<=', $params['end_time']);
        }

        return $obj->sum('price');
    }

    public static function IndexRaw($index_raw)
    {
        $model = new static;
        $model->setTable(DB::raw($model->getTable() . ' ' . $index_raw));
        return $model;
    }

    #region 派单按用户注册统计

    /**
     * 获取时间段内的派单号
     * @param $start
     * @param $end
     * @return array
     */
    public function getSendOrderIdByTime($start, $end): array
    {
        $result = self::leftjoin('users', 'users.id', 'orders.uid')
            ->where([
                ['orders.created_at', '>=', $start],
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '>', 0]
            ])
            ->select('users.send_order_id')->distinct()->get();

        return $result ? $result->toArray() : [];
    }

    /**
     * 获取时间段内派单首充数,和首充金额
     * @param $start
     * @param $end
     * @return array
     */
    public function getSendOrderFirstPayCountAndPriceByTime($start, $end): array
    {
        $result = self::leftjoin('users', 'users.id', 'orders.uid')
            ->where([
                ['orders.created_at', '>=', $start],
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['orders.pay_type', '=', 1],
                ['users.send_order_id', '>', 0]
            ])
            ->select('users.send_order_id', DB::raw('count(1) first'), DB::raw('sum(orders.price) price'))
            ->groupby('users.send_order_id')->get();

        return $result ? $result->toArray() : [];
    }

    /**
     * 根据派单ID获取截止时间点前派单首充数,和首充金额
     * @param $send_order_id
     * @param $end
     * @return array
     */
    public function getSendOrderFirstPayCountAndPriceByID($send_order_id, $end): array
    {
        $result = self::join('users', 'users.id', 'orders.uid')
            ->where([
                ['users.send_order_id', '=', $send_order_id],
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['orders.pay_type', '=', 1]
            ])
            ->select('users.send_order_id', DB::raw('count(1) first'), DB::raw('sum(orders.price) price'))
            ->groupby('users.send_order_id')->get();

        return $result->isEmpty() ? [] : $result->first()->toArray();
    }

    /**
     * 获取时间段内派单充值数和总金额
     * @param $start
     * @param $end
     * @return array
     */
    public function getSendOrderSuccessPayCountByTime($start, $end): array
    {
        $result = self::leftjoin('users', 'users.id', 'orders.uid')
            ->where([
                ['orders.created_at', '>=', $start],
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '>', 0]
            ])
            ->select('users.send_order_id', DB::raw('count(1) num'), DB::raw('sum(orders.price) price'))->groupby('users.send_order_id')->get();

        return $result ? $result->toArray() : [];
    }

    /**
     * 根据派单ID获取时间段内派单充值数和总金额
     * @param $send_order_id
     * @param $end
     * @return array
     */
    public function getSendOrderSuccessPayCountByID($send_order_id, $end): array
    {
        $result = self::join('users', 'users.id', 'orders.uid')
            ->where([
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '=', $send_order_id]
            ])
            ->select('users.send_order_id', DB::raw('count(1) num'), DB::raw('count(distinct(uid)) u_num'), DB::raw('sum(orders.price) price'))
            ->groupby('users.send_order_id')->get();

        return $result->isEmpty() ? [] : $result->first()->toArray();
    }

    /**
     * 获取时间段内派单付费人数
     * @param $start
     * @param $end
     * @return array
     */
    public function getSendOrderSuccessPayUserCountByTime($start, $end): array
    {
        $result = self::join('users', 'users.id', 'orders.uid')
            ->where([
                ['orders.created_at', '>=', $start],
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '>', 0]
            ])
            ->select('users.send_order_id', DB::raw('count(distinct(users.id)) num'))
            ->groupby(['users.send_order_id'])->get();

        return $result ? $result->toArray() : [];
    }


    /**
     * 获取时间段内派单N小时充值金额
     * @param $start
     * @param $end
     * @param $hour
     * @return array
     */
    public function getSendOrderPayPriceByHour($start, $end, $hour): array
    {
        $second = $hour * 60 * 60;
        $result = self::leftjoin('users', 'users.id', 'orders.uid')
            ->where([
                ['orders.created_at', '>=', $start],
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '>', 0]
            ])
            ->whereRaw('TIMESTAMPDIFF(SECOND,users.created_at,orders.created_at)<=' . $second)
            ->select('users.send_order_id', DB::raw('sum(orders.price) price'))
            ->groupby('users.send_order_id')->get();

        return $result ? $result->toArray() : [];
    }

    /**
     * 获取时间段内派单N小时充值金额
     * @param $send_order_id
     * @param $end
     * @param $hour
     * @return array
     */
    public function getSendOrderPayPriceByIdAndHour($send_order_id, $end, $hour): array
    {
        $where  = [
            ['orders.created_at', '<', $end],
            ['orders.status', '=', 'PAID'],
            ['users.send_order_id', '=', $send_order_id]
        ];
        $second = $hour * 60 * 60;
        $result = self::leftjoin('users', 'users.id', 'orders.uid')
            ->where($where)
            ->whereRaw('TIMESTAMPDIFF(SECOND,users.created_at,orders.created_at)<=' . $second)
            ->select('users.send_order_id', DB::raw('sum(orders.price) price'))
            ->groupby('users.send_order_id')->get();

        return $result->isEmpty() ? [] : $result->first()->toArray();
    }

    /**
     * 获取时间段内派单N小时(首充或非首充33)用户数
     * @param     $start
     * @param     $end
     * @param     $hour
     * @param int $first_pay_type 首充类型 -1全部 0非首充  1首充
     * @return array
     */
    public function getSendOrderPayUserCountByHour($start, $end, $hour, $first_pay_type = -1): array
    {
        $where = [
            ['orders.created_at', '>=', $start],
            ['orders.created_at', '<', $end],
            ['orders.status', '=', 'PAID'],
            ['users.send_order_id', '>', 0]
        ];
        if ($first_pay_type == 1) {
            $where = [
                ['orders.created_at', '>=', $start],
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '>', 0],
                ['orders.pay_type', '=', 1]
            ];
        } elseif ($first_pay_type == 0) {
            $where = [
                ['orders.created_at', '>=', $start],
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '>', 0],
                ['orders.pay_type', '>', 1]
            ];
        }
        $second = $hour * 60 * 60;
        $result = self::leftjoin('users', 'users.id', 'orders.uid')
            ->where($where)
            ->whereRaw('TIMESTAMPDIFF(SECOND,users.created_at,orders.created_at)<=' . $second)
            ->select('users.send_order_id', DB::raw('count(distinct(users.id)) num'))
            ->groupby('users.send_order_id')->get();

        return $result ? $result->toArray() : [];
    }

    /**
     * 获取时间段内派单N小时(首充或非首充33)用户数
     * @param     $send_order_id
     * @param     $end
     * @param     $hour
     * @param int $first_pay_type 首充类型 -1全部 0非首充  1首充
     * @return array
     */
    public function getSendOrderPayUserCountByIdAndHour($send_order_id, $end, $hour, $first_pay_type = -1): array
    {
        $where = [
            ['orders.created_at', '<', $end],
            ['orders.status', '=', 'PAID'],
            ['users.send_order_id', '=', $send_order_id]
        ];
        if ($first_pay_type == 1) {
            $where = [
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '=', $send_order_id],
                ['orders.pay_type', '=', 1]
            ];
        } elseif ($first_pay_type == 0) {
            $where = [
                ['orders.created_at', '<', $end],
                ['orders.status', '=', 'PAID'],
                ['users.send_order_id', '=', $send_order_id],
                ['orders.pay_type', '>', 1]
            ];
        }
        $second = $hour * 60 * 60;
        $result = self::leftjoin('users', 'users.id', 'orders.uid')
            ->where($where)
            ->whereRaw('TIMESTAMPDIFF(SECOND,users.created_at,orders.created_at)<=' . $second)
            ->select('users.send_order_id', DB::raw('count(distinct(users.id)) num'))
            ->groupby('users.send_order_id')->get();

        return $result->isEmpty() ? [] : $result->first()->toArray();
    }
    #endregion
}