wangdu_public_api/app/Modules/SendOrder/Models/SendOrderStat.php

<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/11/23
 * Time: 19:36
 */

namespace App\Modules\SendOrder\Models;

use App\Modules\Book\Models\BookConfig;
use DB;
use Illuminate\Database\Eloquent\Model;

class SendOrderStat extends Model
{
    protected $table = 'send_orders_stats';

    protected $fillable = ['send_order_id', 'book_name', 'bid', 'recharge_amount', 'uv', 'pv', 'paid_num', 'unpaid_num', 'distribution_channel_id', 'recharge_amount_in_half_day',
        'recharge_amount_in_two_weeks', 'send_time', 'cost', 'year_recharge_user_num', 'ticket_recharge_user_num', 'first_recharge_amount', 'first_recharge_user_num',
        'year_paid_num', 'year_unpaid_num', 'pay_user_num', 'force_user_num', 'register_user_num', 'distribution_channel_name', 'name', 'charge_type', 'create_time', 'recharge_amount_in_one_day', 'recharge_amount_in_three_days'];

            //获取派单obj
    static function search($params = [])
    {
        $order_field = 'distribution_channel_id';
        $order_type = isset($params['order_type']) ? $params['order_type'] : 'asc';

        if (isset($params['order_field']) && $params['order_field']) {
            switch ($params['order_field']) {
                case 1:
                    $order_field = 'recharge_amount_in_half_day';
                    break;
                case 2:
                    $order_field = 'recharge_amount_in_two_weeks';
                    break;
                case 3:
                    $order_field = 'recharge_amount';
                    break;
                case 4:
                    $order_field = 'first_recharge_amount';
                    break;
            }
        }

        $search_obj = self::orderBy($order_field, $order_type);
        if (isset($params['start_time']) && $params['start_time']) $search_obj->where('send_time', '>=', $params['start_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('send_time', '<=', $params['end_time']);
        if (isset($params['send_order_id']) && $params['send_order_id']) $search_obj->where('send_order_id', '=', $params['send_order_id']);
        if (isset($params['bid']) && $params['bid']) $search_obj->where('bid', '=', $params['bid']);
        if (isset($params['name']) && $params['name']) $search_obj->where('name', 'like', "%" . $params['name'] . "%");
        if (isset($params['book_name']) && $params['book_name']) $search_obj->where('book_name', '=', $params['book_name']);
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id', '=', $params['distribution_channel_id']);
        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) $search_obj->whereIn('distribution_channel_id', $params['distribution_channel_ids']);
        if (isset($params['order_status']) && $params['order_status']) {
            if ($params['order_status'] == 1) $search_obj->where('force_user_num', '<', 20);
            if ($params['order_status'] == 2) $search_obj->where('force_user_num', '>=', 20);
        }

        $search_obj->select(
            DB::raw('sum(cost) cost'),
            DB::raw('sum(recharge_amount_in_three_days) chargeAmountIn72hours'),
            DB::raw('sum(pv) clickNum'),
            DB::raw('sum(pay_user_num) pay_user_num'),
            DB::raw('sum(force_user_num) fansNum'),
            DB::raw('sum(first_recharge_user_num) first_recharge_user_num'),
            DB::raw('sum(register_user_num) register_num'));
        \Log::info($search_obj->toSql());
        return $search_obj->get();
    }


    static function generateStat($data)
    {
        $conditions = [
            'send_order_id' => $data['send_order_id']
        ];
        unset($data['send_order_id']);
        return self::updateOrCreate($conditions, $data);
    }

    //获取派单
    static function getList($params = [], $is_all = false)
    {
        $order_type = isset($params['order_type']) ? $params['order_type'] : 'desc';

        if (isset($params['order_field']) && $params['order_field']) {
            switch ($params['order_field']) {
                case 1:
                    $order_field = 'recharge_amount_in_one_day';
                    break;
                case 2:
                    $order_field = 'recharge_amount_in_three_days';
                    break;
                case 3:
                    $order_field = 'recharge_amount';
                    break;
                case 4:
                    $order_field = 'first_recharge_amount';
                    break;
                case 5:
                    $order_field = 'uv';
                    break;
                case 6:
                    $order_field = 'register_user_num';
                    break;
            }
        } else {
            $order_field = 'create_time';
        }
        $search_obj = self::orderBy($order_field, $order_type);
        if (isset($params['gender']) && $params['gender']) {
            $search_obj->join('books','books.id','=','send_orders_stats.bid')
                ->join('book_categories','book_categories.id','=','books.category_id')
                ->where('book_categories.pid', '=', $params['gender']);
        }
        if (isset($params['start_time']) && $params['start_time']) $search_obj->where('send_time', '>=', $params['start_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('send_time', '<=', $params['end_time']);
        if (isset($params['send_order_id']) && $params['send_order_id']) $search_obj->where('send_order_id', '=', $params['send_order_id']);
        if (isset($params['bid']) && $params['bid']) $search_obj->where('bid', '=', $params['bid']);
        if (isset($params['name']) && $params['name']) $search_obj->where('name', 'like', "%" . $params['name'] . "%");
        if (isset($params['book_name']) && $params['book_name']) $search_obj->where('book_name', 'like', '%'.$params['book_name'].'%');
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id', '=', $params['distribution_channel_id']);

        if (isset($params['order_status']) && $params['order_status']) {
            if ($params['order_status'] == 1) $search_obj->where('force_user_num', '<', 20);
            if ($params['order_status'] == 2) $search_obj->where('force_user_num', '>=', 20);
        }
        //\Log::info($search_obj->toSql());
        if ($is_all) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }

    //获取派单obj
    static function getObj($params = [])
    {
        $order_type = isset($params['order_type']) ? $params['order_type'] : 'desc';

        if (isset($params['order_field']) && $params['order_field']) {
            switch ($params['order_field']) {
                case 1:
                    $order_field = 'recharge_amount_in_half_day';
                    break;
                case 2:
                    $order_field = 'recharge_amount_in_two_weeks';
                    break;
                case 3:
                    $order_field = 'recharge_amount';
                    break;
                case 4:
                    $order_field = 'first_recharge_amount';
                    break;
            }
        } else {
            $order_field = 'create_time';
        }
        $search_obj = self::orderBy($order_field, $order_type);
        if (isset($params['start_time']) && $params['start_time']) $search_obj->where('create_time', '>=', $params['start_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('create_time', '<=', $params['end_time']);
        if (isset($params['send_order_id']) && $params['send_order_id']) $search_obj->where('send_order_id', '=', $params['send_order_id']);
        if (isset($params['bid']) && $params['bid']) $search_obj->where('bid', '=', $params['bid']);
        if (isset($params['name']) && $params['name']) $search_obj->where('name', 'like', "%" . $params['name'] . "%");
        if (isset($params['book_name']) && $params['book_name']) $search_obj->where('book_name', '=', $params['book_name']);
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id', '=', $params['distribution_channel_id']);

        if (isset($params['order_status']) && $params['order_status']) {
            if ($params['order_status'] == 1) $search_obj->where('force_user_num', '<', 20);
            if ($params['order_status'] == 2) $search_obj->where('force_user_num', '>=', 20);
        }
        return $search_obj;
    }

    static function getRechargeAmountByBookId($params = [], $is_out_side = true, $is_24_hour = true)
    {
        $search_obj = self::orderBy('create_time');
        if (isset($params['book_id']) && $params['book_id']) $search_obj->where('bid', $params['book_id']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('send_time', '<=', $params['end_time']);
        if (isset($params['start_time']) && $params['start_time']) $search_obj->where('send_time', '>=', $params['start_time']);

        if ($is_out_side) {
            $search_obj->where('register_user_num', '>=', 20);
        } else {
            $search_obj->where('register_user_num', '<', 20);
        }

        if ($is_24_hour) {
            $search_obj->select('recharge_amount_in_one_day');
            return $search_obj->sum('recharge_amount_in_one_day');
        } else {
            $search_obj->select('recharge_amount_in_three_days');
            return $search_obj->sum('recharge_amount_in_three_days');
        }
    }

    public static function getBookSendOrdersRechargeStats($channels) {
        // self::
        $sum_stats = self::select(DB::raw('count(id) as send_orders_num,
                sum(recharge_amount) as recharge_amount_sum,
                sum(paid_num) as paid_num_sum,
                sum(unpaid_num) as unpaid_num_sum,
                sum(pay_user_num) as pay_user_num_sum,
                bid')
            )
            //->where('date',date('Y-m-d'))
            ->whereIn('distribution_channel_id',$channels)
            ->whereNotNull('bid')
            ->groupBy('bid')
            ->orderBy('recharge_amount_sum','desc')
            ->paginate();
        $channels_str = implode(',',$channels);
        //\Log::info('sum_stats_origin:'.json_encode($sum_stats));
        foreach ($sum_stats as $sum_stat) {
            $yesterday_recharge =  DB::select("select book_name,sum(recharge_amount) as yesterday_recharge
            from send_orders_force_day_stats where distribution_channel_id in ($channels_str) and  bid =".($sum_stat->bid)." and date='".date('Y-m-d',strtotime('-1 day'))."'");
            $yesterday_recharge_sum = $yesterday_recharge[0]->yesterday_recharge;
            $book_name = $yesterday_recharge[0]->book_name;
            if(empty($book_name)){
                $book = BookConfig::where('bid',$sum_stat->bid)->first();
                if($book){
                    $book_name = $book->book_name;
                }
            }
            $sum_stat->yesterday_recharge = $yesterday_recharge_sum;
            $sum_stat->book_name = $book_name;
            $sum_stat->order_total = intval($sum_stat->paid_num_sum) + intval($sum_stat->unpaid_num_sum);
            $sum_stat->average_recharge = $sum_stat->pay_user_num_sum >0?round($sum_stat->recharge_amount_sum/$sum_stat->pay_user_num_sum,2):0;
            $sum_stat->success_rate = $sum_stat->order_total>0?(round($sum_stat->paid_num_sum/$sum_stat->order_total,2)*100).'%':0;
        }
        //\Log::info('sum_stats:'.json_encode($sum_stats));
        return $sum_stats;
    }

    /**
     * @param $channels
     * @return array
     */
    public static function exportBookSendOrdersRechargeStats($channels){
        \Log::info($channels);
        $channels_str = implode(',',$channels);
        $sum_stats = DB::select("select book_name,count(id) as send_orders_num,
                    sum(recharge_amount) as recharge_amount_sum,
                    sum(paid_num) as paid_num_sum,
                    sum(unpaid_num) as unpaid_num_sum,
                    sum(pay_user_num) as pay_user_num_sum,
                    bid from send_orders_stats where distribution_channel_id in ($channels_str)
                     group by bid"
            );
        $sum_stats = json_decode(json_encode($sum_stats),true);
        $yesterday = date('Y-m-d',strtotime('-1 day'));
        $sum_day_stats = DB::select("select bid,sum(recharge_amount) as yesterday_recharge from send_orders_force_day_stats 
          where date='{$yesterday}' and distribution_channel_id in ($channels_str) group by bid
          ");
        $sum_day_stats = json_decode(json_encode($sum_day_stats),true);
        $sum_stats_day_temp = [];
        /*foreach ($sum_stats as $sum_stat){
            $sum_stats_total[$sum_stats->bid] = $sum_stat;
        }*/
        foreach ($sum_day_stats as $sum_day_stat){
            $sum_stats_day_temp[$sum_day_stat['bid']] = $sum_day_stat;
        }
        $data = array();
        $header = ['书名','派单数','充值总额','充值人数','人均充值','昨日充值','成功订单数','订单总数','订单成功率'];
        foreach ($sum_stats as $key=>$value){
            $yesterday_recharge = array_key_exists($value['bid'],$sum_stats_day_temp)?$sum_stats_day_temp[$value['bid']]['yesterday_recharge']:0;
            $order_num = $value['paid_num_sum']+$value['unpaid_num_sum'];
            $data[] = [
                $value['book_name'],$value['send_orders_num'],
                $value['recharge_amount_sum'],$value['pay_user_num_sum'],
                $value['pay_user_num_sum']>0?round($value['recharge_amount_sum']/$value['pay_user_num_sum'],2):0,
                $yesterday_recharge, $value['paid_num_sum'], $order_num,
                $order_num>0?round($value['paid_num_sum']/$order_num,2):0
            ];
        }
        return ['header'=>$header,'data'=>$data];
    }
}