zhuishuyun_quickapp/app/Modules/SendOrder/Services/SendOrderStatService.php

<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/12/2
 * Time: 15:36
 */

namespace App\Modules\SendOrder\Services;

use App\Modules\Channel\Services\ChannelService;
use App\Modules\SendOrder\Models\SendOrderStat;
use DB;

class SendOrderStatService
{
    /**
     * 获取派单汇总统计信息
     * @param $params 字段列表
     * @param string $isAll 是否获取所有
     * @return mixed
     */
    static function getSendOrderStats($params = [], $is_all = false)
    {
        return SendOrderStat::getSendOrderStats($params, $is_all);
    }

    //生成统计
    static function generateStat()
    {
        $fields = DB::raw("send_order_id,max(date) max_date,book_name,bid,sum(uv) sum_uv,sum(pv) sum_pv,sum(recharge_amount) sum_recharge_amount,sum(paid_num) sum_paid_num,sum(unpaid_num) sum_unpaid_num,
        distribution_channel_id,max(recharge_amount_in_half_day) max_recharge_amount_in_half_day,max(recharge_amount_in_one_day) max_recharge_amount_in_one_day,max(recharge_amount_in_two_weeks) max_recharge_amount_in_two_weeks,
        max(recharge_amount_in_three_days) max_recharge_amount_in_three_days,max(recharge_amount_in_one_day) max_recharge_amount_in_one_day,send_time,cost,sum(year_recharge_user_num) sum_year_recharge_user_num,
        sum(ticket_recharge_user_num) sum_ticket_recharge_user_num,sum(first_recharge_amount) sum_first_recharge_amount, sum(first_recharge_user_num) sum_first_recharge_user_num,sum(year_paid_num) sum_year_paid_num,
        sum(year_unpaid_num) sum_year_unpaid_num,sum(pay_user_num) sum_pay_user_num,sum(force_user_num) sum_force_user_num,sum(register_user_num) sum_register_user_num,distribution_channel_name,charge_type,name,create_time");
        $stats = DB::table('send_orders_force_day_stats')->select($fields)->groupBy('send_order_id')->get();

        foreach ($stats as $stat)
        {
            $end_time = date("Y-m-d",strtotime($stat->max_date)+86400);
            $data = [];
            $data['send_order_id'] = $stat->send_order_id;
            $data['book_name'] = $stat->book_name;
            $data['bid'] = $stat->bid;
            $data['uv'] = $stat->sum_uv;
            $data['pv'] = $stat->sum_pv;
            $data['recharge_amount'] = $stat->sum_recharge_amount;
            $data['paid_num'] = $stat->sum_paid_num;
            $data['unpaid_num'] = $stat->sum_unpaid_num;

            $data['distribution_channel_id'] = $stat->distribution_channel_id;
            $data['recharge_amount_in_half_day'] = $stat->max_recharge_amount_in_half_day;
            $data['recharge_amount_in_one_day'] = $stat->max_recharge_amount_in_one_day;
            $data['recharge_amount_in_two_weeks'] = $stat->max_recharge_amount_in_two_weeks;
            $data['recharge_amount_in_three_days'] = $stat->max_recharge_amount_in_three_days;
            $data['recharge_amount_in_one_day'] = $stat->max_recharge_amount_in_one_day;
            $data['send_time'] = $stat->send_time;
            $data['cost'] = $stat->cost;

            //付费用户数
            $data['year_recharge_user_num'] = (int)DB::table('orders')
                                                    ->where('send_order_id',$stat->send_order_id)
                                                    ->distinct('uid')->where('order_type','YEAR')
                                                    ->where('created_at','<',$end_time)
                                                    ->where('status','PAID')
                                                    ->count();
            $data['ticket_recharge_user_num'] = (int)DB::table('orders')->where('send_order_id',$stat->send_order_id)->distinct('uid')->where('order_type','RECHARGE')->where('created_at','<',$end_time)->where('status','PAID')->count();

            $data['pay_user_num'] = (int)DB::table('orders')->where('send_order_id',$stat->send_order_id)->distinct('uid')->where('status','PAID')->where('created_at','<',$end_time)->count();

            $data['first_recharge_amount'] = $stat->sum_first_recharge_amount;
            $data['first_recharge_user_num'] = $stat->sum_first_recharge_user_num;
            $data['year_paid_num'] = $stat->sum_year_paid_num;
            $data['year_unpaid_num'] = $stat->sum_year_unpaid_num;
            $data['force_user_num'] = $stat->sum_force_user_num;
            $data['register_user_num'] = $stat->sum_register_user_num;
            $data['distribution_channel_name'] = $stat->distribution_channel_name;
            $data['charge_type'] = $stat->charge_type;
            $data['name'] = $stat->name;
            $data['create_time'] = $stat->create_time;

            SendOrderStat::generateStat($data);
        }
    }

    //获取派单
    static function getList($params=[],$is_all = false)
    {
        return SendOrderStat::getList($params,$is_all);
    }

    //获取派单obj
    static function getObj($params=[])
    {
        return SendOrderStat::getObj($params);
    }

    static function generateStatsByDayStats($data,$date)
    {
        //$yesterday = date('Y-m-d',strtotime($date.' -1 day'));
        //$save_data = [];
        foreach ($data as $key=>$item) {
            $yest_data =  SendOrderStat::where('send_order_id',$item['send_order_id'])->first();
            $end_time = date('Y-m-d',strtotime($date)+86400);
            $save_data = array();
            if($yest_data){
                $save_data = array(
                    'send_order_id' => $yest_data->send_order_id,
                    'book_name' => $yest_data->book_name,
                    'bid'   => $yest_data->bid,
                    'uv' => $item['uv'] + $yest_data->uv,
                    'pv' => $item['pv'] + $yest_data->pv,
                    'recharge_amount' => $item['recharge_amount'] + $yest_data->recharge_amount,
                    'paid_num' => $item['paid_num'] + $yest_data->paid_num,
                    'unpaid_num' => $item['unpaid_num'] + $yest_data->unpaid_num,
                    'distribution_channel_id' => $item['distribution_channel_id'],
                    'recharge_amount_in_half_day' => ($item['recharge_amount_in_half_day']>=$yest_data->recharge_amount_in_half_day)?$item['recharge_amount_in_half_day']:$yest_data->recharge_amount_in_half_day,
                    'recharge_amount_in_one_day' => ($item['recharge_amount_in_one_day']>=$yest_data->recharge_amount_in_one_day)?$item['recharge_amount_in_one_day']:$yest_data->recharge_amount_in_one_day,
                    'recharge_amount_in_two_weeks' => ($item['recharge_amount_in_two_weeks']>=$yest_data->recharge_amount_in_two_weeks)?$item['recharge_amount_in_two_weeks']:$yest_data->recharge_amount_in_two_weeks,
                    'recharge_amount_in_three_days' => ($item['recharge_amount_in_three_days']>=$yest_data->recharge_amount_in_three_days)?$item['recharge_amount_in_three_days']:$yest_data->recharge_amount_in_three_days,

                    'send_time' => $item['send_time'],
                    'cost' => $item['cost'],

                    //付费用户数
                    'year_recharge_user_num' => (int)DB::table('orders')
                        ->where('send_order_id',$item['send_order_id'])
                        ->distinct('uid')->where('order_type','YEAR')
                        ->where('created_at','<',$end_time)
                        ->where('status','PAID')
                        ->count(),
                    'ticket_recharge_user_num' => (int)DB::table('orders')
                        ->where('send_order_id',$item['send_order_id'])
                        ->distinct('uid')->where('order_type','RECHARGE')
                        ->where('created_at','<',$end_time)
                        ->where('status','PAID')
                        ->count(),
                    'pay_user_num' => (int)DB::table('orders')
                        ->where('send_order_id',$item['send_order_id'])
                        ->distinct('uid')
                        ->where('status','PAID')
                        ->where('created_at','<',$end_time)
                        ->count(),
                    'first_recharge_amount' => $item['first_recharge_amount'] + $yest_data->first_recharge_amount,
                    'first_recharge_user_num' => $item['first_recharge_user_num'] + $yest_data->first_recharge_user_num,
                    'year_paid_num' => $item['year_paid_num'] + $yest_data->year_paid_num,
                    'year_unpaid_num' => $item['year_unpaid_num'] + $yest_data->year_unpaid_num,
                    'force_user_num' => $item['force_user_num'] + $yest_data->force_user_num,
                    'register_user_num' =>$item['register_user_num'] + $yest_data->register_user_num,
                    'distribution_channel_name' => $item['distribution_channel_name'],
                    'charge_type' => $item['charge_type'],
                    'name' => $item['name'],
                    'create_time' => $item['create_time']
                );
            }else{
                $save_data = array(
                    'send_order_id' => $item['send_order_id'],
                    'book_name' => $item['book_name'],
                    'bid'   => $item['bid'],
                    'uv' => $item['uv'] ,
                    'pv' => $item['pv'] ,
                    'recharge_amount' => $item['recharge_amount'],
                    'paid_num' => $item['paid_num'],
                    'unpaid_num' => $item['unpaid_num'],
                    'distribution_channel_id' => $item['distribution_channel_id'],
                    'recharge_amount_in_half_day' => $item['recharge_amount_in_half_day'],
                    'recharge_amount_in_one_day' => $item['recharge_amount_in_one_day'],
                    'recharge_amount_in_two_weeks' => $item['recharge_amount_in_two_weeks'],
                    'recharge_amount_in_three_days' => $item['recharge_amount_in_three_days'],

                    'send_time' => $item['send_time'],
                    'cost' => $item['cost'],

                    //付费用户数
                    'year_recharge_user_num' => (int)DB::table('orders')
                        ->where('send_order_id',$item['send_order_id'])
                        ->distinct('uid')->where('order_type','YEAR')
                        ->where('created_at','<',$end_time)
                        ->where('status','PAID')
                        ->count(),
                    'ticket_recharge_user_num' => (int)DB::table('orders')
                        ->where('send_order_id',$item['send_order_id'])
                        ->distinct('uid')->where('order_type','RECHARGE')
                        ->where('created_at','<',$end_time)
                        ->where('status','PAID')
                        ->count(),
                    'pay_user_num' => (int)DB::table('orders')
                        ->where('send_order_id',$item['send_order_id'])
                        ->distinct('uid')
                        ->where('status','PAID')
                        ->where('created_at','<',$end_time)
                        ->count(),
                    'first_recharge_amount' => $item['first_recharge_amount'],
                    'first_recharge_user_num' => $item['first_recharge_user_num'],
                    'year_paid_num' => $item['year_paid_num'],
                    'year_unpaid_num' => $item['year_unpaid_num'],
                    'force_user_num' => $item['force_user_num'],
                    'register_user_num' =>$item['register_user_num'],
                    'distribution_channel_name' => ChannelService::getChannelCompanyNameById($item['distribution_channel_id']),
                    'charge_type' => $item['charge_type'],
                    'name' => $item['name'],
                    'create_time' => $item['create_time']
                );
            }
            SendOrderStat::updateOrCreate(['send_order_id'=>$item['send_order_id']],$save_data);
        }
    }

    static function generateStatsByChunk(){
        $fields = DB::raw("send_order_id,max(date) max_date,book_name,bid,sum(uv) sum_uv,sum(pv) sum_pv,sum(recharge_amount) sum_recharge_amount,sum(paid_num) sum_paid_num,sum(unpaid_num) sum_unpaid_num,
        distribution_channel_id,max(recharge_amount_in_half_day) max_recharge_amount_in_half_day,max(recharge_amount_in_one_day) max_recharge_amount_in_one_day,max(recharge_amount_in_two_weeks) max_recharge_amount_in_two_weeks,
        max(recharge_amount_in_three_days) max_recharge_amount_in_three_days,max(recharge_amount_in_one_day) max_recharge_amount_in_one_day,send_time,cost,sum(year_recharge_user_num) sum_year_recharge_user_num,
        sum(ticket_recharge_user_num) sum_ticket_recharge_user_num,sum(first_recharge_amount) sum_first_recharge_amount, sum(first_recharge_user_num) sum_first_recharge_user_num,sum(year_paid_num) sum_year_paid_num,
        sum(year_unpaid_num) sum_year_unpaid_num,sum(pay_user_num) sum_pay_user_num,sum(force_user_num) sum_force_user_num,sum(register_user_num) sum_register_user_num,distribution_channel_name,charge_type,name,create_time");
        DB::table('send_orders_force_day_stats')
            ->select($fields)
            ->groupBy('send_order_id')
            ->orderBy('send_order_id')
            ->chunk(1000,function($stats){
                foreach ($stats as $stat)
                {
                    $end_time = date("Y-m-d",strtotime($stat->max_date)+86400);
                    $data = [];
                    $data['send_order_id'] = $stat->send_order_id;
                    $data['book_name'] = $stat->book_name;
                    $data['bid'] = $stat->bid;
                    $data['uv'] = $stat->sum_uv;
                    $data['pv'] = $stat->sum_pv;
                    $data['recharge_amount'] = $stat->sum_recharge_amount;
                    $data['paid_num'] = $stat->sum_paid_num;
                    $data['unpaid_num'] = $stat->sum_unpaid_num;

                    $data['distribution_channel_id'] = $stat->distribution_channel_id;
                    $data['recharge_amount_in_half_day'] = $stat->max_recharge_amount_in_half_day;
                    $data['recharge_amount_in_one_day'] = $stat->max_recharge_amount_in_one_day;
                    $data['recharge_amount_in_two_weeks'] = $stat->max_recharge_amount_in_two_weeks;
                    $data['recharge_amount_in_three_days'] = $stat->max_recharge_amount_in_three_days;
                    $data['recharge_amount_in_one_day'] = $stat->max_recharge_amount_in_one_day;
                    $data['send_time'] = $stat->send_time;
                    $data['cost'] = $stat->cost;

                    //付费用户数
                    $data['year_recharge_user_num'] = (int)DB::table('orders')
                        ->where('send_order_id', $stat->send_order_id)
                        ->distinct('uid')->where('order_type', 'YEAR')
                        ->where('created_at', '<', $end_time)
                        ->where('status', 'PAID')
                        ->count();
                    $data['ticket_recharge_user_num'] = (int)DB::table('orders')->where('send_order_id', $stat->send_order_id)->distinct('uid')->where('order_type', 'RECHARGE')->where('created_at', '<', $end_time)->where('status', 'PAID')->count();

                    $data['pay_user_num'] = (int)DB::table('orders')->where('send_order_id', $stat->send_order_id)->distinct('uid')->where('status', 'PAID')->where('created_at', '<', $end_time)->count();

                    $data['first_recharge_amount'] = $stat->sum_first_recharge_amount;
                    $data['first_recharge_user_num'] = $stat->sum_first_recharge_user_num;
                    $data['year_paid_num'] = $stat->sum_year_paid_num;
                    $data['year_unpaid_num'] = $stat->sum_year_unpaid_num;
                    $data['force_user_num'] = $stat->sum_force_user_num;
                    $data['register_user_num'] = $stat->sum_register_user_num;
                    $data['distribution_channel_name'] = $stat->distribution_channel_name;
                    $data['charge_type'] = $stat->charge_type;
                    $data['name'] = $stat->name;
                    $data['create_time'] = $stat->create_time;

                    SendOrderStat::generateStat($data);
                }
            });
    }

    public static function getSendOrdersRechargeDetail($distribution_channels)
    {
        return SendOrderStat::getBookSendOrdersRechargeStats($distribution_channels);
    }

    public static function exportSendOrdersRechargeDetail($distribution_channels)
    {
        return SendOrderStat::exportBookSendOrdersRechargeStats($distribution_channels);
    }

    static function getChannelPromotionBook($channels, $params, $isAll = false)
    {
        $orderBy = 'register_user_num';
        $obj = SendOrderStat::select(
            DB::raw('date(send_orders_stats.send_time) as date'),
            'send_orders_stats.book_name',
            'send_orders_stats.bid',
            "channel_users.company_name",
            "channel_users.company_id",
            DB::raw('sum(register_user_num) as register_user_num'),
            DB::raw('sum(uv_one_day) as uv_one_day'),
            DB::raw('sum(uv_three_day) as uv_three_day'),
            DB::raw('sum(recharge_amount_in_three_days) as recharge_amount_in_three_days'),
            DB::raw('sum(recharge_amount_in_one_day) as recharge_amount_in_one_day'),
            DB::raw('sum(recharge_amount_in_one_day)/sum(uv_one_day) as percent_recharge_devide_uv'),
            DB::raw('sum(recharge_amount_in_three_days)/sum(uv_three_day) as percent_recharge_devide_uv_three_day')

        )
            ->join("distribution_channels", 'send_orders_stats.distribution_channel_id', "=", "distribution_channels.id")
            ->join("channel_users", 'distribution_channels.channel_user_id', "=", "channel_users.id")
            ->join('send_order_extra_stats', 'send_orders_stats.send_order_id', '=', 'send_order_extra_stats.send_order_id')
            ->groupBy('channel_users.company_id')
            ->groupBy('send_orders_stats.book_name')
            ->groupBy(DB::raw('date(send_orders_stats.send_time)'));

        if ($channels) {
            $obj->whereIn('send_orders_stats.distribution_channel_id', $channels);
        }

        if (isset($params['company_name']) && $params['company_name']) {
            $obj->where('channel_users.company_name', $params['company_name']);
        }

        if (isset($params['book_name']) && $params['book_name']) {
            $obj->where('send_orders_stats.book_name', 'like', '%' . $params['book_name'] . '%');
        }

        if (isset($params['begin_date']) && $params['begin_date']) {
            $obj->where(DB::raw('date(send_orders_stats.send_time)'), '>=', $params['begin_date']);
        }

        if (isset($params['end_date']) && $params['end_date']) {
            $obj->where(DB::raw('date(send_orders_stats.send_time)'), '<=', $params['end_date']);
        }

        if (isset($params['orderBy']) && $params['orderBy']) {
            if ($params['orderBy'] == 'register_user_num') {
                $orderBy = 'register_user_num';
            }

            if ($params['orderBy']== 'percent_recharge_devide_uv') {
                $orderBy = 'percent_recharge_devide_uv';
            }

            if ($params['orderBy']== 'percent_recharge_devide_uv_three_day') {
                $orderBy = 'percent_recharge_devide_uv_three_day';
            }
        }

        $obj->orderBy($orderBy, 'desc');
        if ($isAll) {
            return $obj->get();
        } else {
            return $obj->paginate();
        }
    }
}