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

<?php

namespace App\Modules\Trade\Models;

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

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 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 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 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['uid']) && $params['uid']) $search_obj->where('uid', $params['uid']);
        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 getByTradeNo($distribution_channel_id, $trade_no)
    {
        return self::where('distribution_channel_id', $distribution_channel_id)->where('trade_no', $trade_no)->first();
    }

    //获取派单下的普通用户订单总数
    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 getOrdersByActivityId($channelId, $createdAt, $activityId)
    {
        $result = self::select('uid', 'status', 'price', 'activity_id', 'product_id')
            ->where('distribution_channel_id', $channelId)
            ->where('created_at', '>=', $createdAt)
            ->where('activity_id', $activityId)
            ->get();

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

    public static function getUserLastestOrder($uid)
    {
        if (empty($uid)) {
            return [];
        }

        $result = self::where('uid', $uid)->where('status', 'PAID')->orderBy('id')->limit(1)->get();
        return $result ? $result->toArray() : [];
    }
}