wangdu_public_api/app/Modules/SendOrder/Services/SendOrderForceDayStatService.php

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

namespace App\Modules\SendOrder\Services;

use App\Modules\Book\Models\BookConfig;
use App\Modules\Channel\Services\ChannelService;
use App\Modules\SendOrder\Models\SendOrderForceDayStat;
use App\Modules\Trade\Services\OrderService;
use DB;
use Redis;
class SendOrderForceDayStatService
{
    //按书统计
    static function getSendOrderDayStatsByBook($params)
    {
        return SendOrderForceDayStat::getSendOrderDayStatsByBook($params);
    }

    //生成
    static function generateForceDayStat($date)
    {
        $end_send_time = date('Y-m-d H:i:s', strtotime($date) + 86400 - 1);
        $fields = DB::raw("
              send_order_id,
              count(1) paid_num,
              sum(price) recharge_amount,
              count(distinct uid) pay_user_num
        ");
        //TODO 解除limit
        $order_data = DB::table('orders')->select($fields)->where('status', 'PAID')->where('created_at','>=', $date)->where('created_at','<=', $end_send_time)->groupBy('send_order_id')->get();
        $order_data_array = [];
        if ($order_data) {
            foreach ($order_data as $order) {
                $order_data_array[$order->send_order_id] = $order;
            }
        }
        $order_total_fields = DB::raw("
                send_order_id,
                count(1) order_num
        ");
        $order_total_data = DB::table('orders')->select($order_total_fields)->where('created_at','>=', $date)->where('created_at','<=', $end_send_time)->groupBy('send_order_id')->get();
        $order_total_data_array = [];
        if ($order_total_data) {
            foreach ($order_total_data as $_order) {
                $order_total_data_array[$_order->send_order_id] = $_order;
            }
        }
        //注册用户
        $register_fields = DB::raw("
              send_order_id,
              count(1) num
        ");
        $register_data = DB::table('users')->select($register_fields)->where('created_at','>=', $date)->where('created_at','<=', $end_send_time)->groupBy('send_order_id')->get();
        $register_data_array = [];
        if ($register_data) {
            foreach ($register_data as $_register_data) {
                $register_data_array[$_register_data->send_order_id] = $_register_data->num;
            }
        }
        //满足有订单,有注册用户,有访问其中之一的派单才进行统计

        $send_order_list_has_order = $order_total_data->pluck('send_order_id')->toArray();
        $send_order_list_has_register = $register_data->pluck('send_order_id')->toArray();
        $send_order_list_has_visit =  Redis::SMEMBERS('send_order'.$date);
        $send_order_list = array_values(array_unique(array_merge($send_order_list_has_order,$send_order_list_has_register,$send_order_list_has_visit)));

        foreach ($chunk_result = array_chunk($send_order_list, 500) as $send_order_ids)
        {
            $send_orders = DB::table('send_orders')->whereIn('id',$send_order_ids)->where('send_time', '>', '2019-01-01')->where('send_time', '<=', $end_send_time)->get();
            $return_data = [];
            foreach ($send_orders as $send_order) {
                $data = [];
                $data['send_order_id'] = $send_order->id;
                $data['date'] = $date;
                $data['create_time'] = $send_order->created_at;
                $data['name'] = $send_order->name;
                $data['charge_type'] = $send_order->charge_type;

                if(isset($book_name_array[$send_order->book_id])){
                    $book_name = $book_name_array[$send_order->book_id];
                }else{
                    $book_name =self::getBookNameByBid($send_order->book_id);
                    $book_name_array[$send_order->book_id] = $book_name;
                }
                $data['book_name'] = $book_name;
                $data['bid'] = $send_order->book_id;
                $uv = SendOrderService::getUvInfo($send_order->id);
                $pv = SendOrderService::getPvInfo($send_order->id);

                $data['total_uv'] = isset($uv['total']) ? $uv['total'] : 0;
                $data['uv'] = isset($uv[$date]) ? $uv[$date] : 0;
                $data['total_pv'] = isset($pv['total']) ? $pv['total'] : 0;
                $data['pv'] = isset($pv[$date]) ? $pv[$date] : 0;
                $data['recharge_amount'] = isset($order_data_array[$send_order->id]) ? $order_data_array[$send_order->id]->recharge_amount : 0;
                $data['paid_num'] = isset($order_data_array[$send_order->id]) ? $order_data_array[$send_order->id]->paid_num : 0;
                $data['unpaid_num'] = isset($order_total_data_array[$send_order->id]) ? $order_total_data_array[$send_order->id]->order_num - $data['paid_num'] : 0;
                $data['pay_user_num'] = isset($order_data_array[$send_order->id]) ? $order_data_array[$send_order->id]->pay_user_num : 0;
                $data['distribution_channel_id'] = $send_order->distribution_channel_id;
                $data['distribution_channel_name'] = ChannelService::getChannelCompanyNameById($send_order->distribution_channel_id);//ChannelService::getChannelNicknameById($send_order->distribution_channel_id);
                $data['cost'] = $send_order->cost;
                $data['send_time'] = $send_order->send_time;

                $total_order_num = $data['unpaid_num'] + $data['paid_num'];

                //强关用户数
                $force_user_num = DB::table('force_subscribe_users')
                    ->where('send_order_id', $send_order->id)
                    ->where('created_at','>=',date('Y-m-d 00:00:00',strtotime($date)))
                    ->where('created_at','<=',date('Y-m-d 23:59:59',strtotime($date)))
                    ->count();
                $data['force_user_num'] = $force_user_num;

                //注册用户数
                $data['register_user_num'] = isset($register_data_array[$send_order->id]) ? $register_data_array[$send_order->id] : 0;

                //首充数据
                if ($data['pay_user_num']) {
                    $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 created_at>='{$begin_time}' and created_at<'{$end_time}' and send_order_id = '{$send_order->id}' and status ='PAID' and not exists (select uid from orders where a.uid = uid and created_at < '{$begin_time}' and status= 'PAID' limit 1)");
                    $first_data = DB::select("select count(distinct uid) count,sum(price) amount from orders a where created_at>='{$begin_time}' and created_at<'{$end_time}' and send_order_id = '{$send_order->id}' and status ='PAID' and pay_type=1");
                    
                    $data['first_recharge_user_num'] = (int)$first_data[0]->count;
                    $data['first_recharge_amount'] = (float)$first_data[0]->amount;
                } else {
                    $data['first_recharge_user_num'] = 0;
                    $data['first_recharge_amount'] = 0;
                }

                //12小时充值数据
                if ($send_order->send_time) {
                    $end_timestamp = min(strtotime($send_order->send_time) + 14 * 24 * 3600, strtotime($date) + 86400);
                    $end_time = date("Y-m-d H:i:s", $end_timestamp);
                    $data['recharge_amount_in_two_weeks'] = OrderService::getAmount(['send_order_id' => $send_order->id,  'end_time' => $end_time]);
                    $end_timestamp = min(strtotime($send_order->send_time) + 12 * 3600, strtotime($date) + 86400);
                    $end_time = date("Y-m-d H:i:s", $end_timestamp);
                    $data['recharge_amount_in_half_day'] = OrderService::getAmount(['send_order_id' => $send_order->id, 'end_time' => $end_time]);
                    //24小时
                    $end_time = date("Y-m-d H:i:s", min(strtotime($send_order->send_time) + 24 * 3600,strtotime($date) + 86400));
                    $data['recharge_amount_in_one_day'] = OrderService::getAmount(['send_order_id' => $send_order->id,  'end_time' => $end_time]);
                    //3天
                    $end_time = date("Y-m-d H:i:s", min(strtotime($send_order->send_time) + 3 * 24 * 3600,strtotime($date) + 86400));
                    $data['recharge_amount_in_three_days'] = OrderService::getAmount(['send_order_id' => $send_order->id,  'end_time' => $end_time]);
                } else {
                    $data['recharge_amount_in_two_weeks'] = 0;
                    $data['recharge_amount_in_half_day'] = 0;
                    $data['recharge_amount_in_one_day'] = 0;
                    $data['recharge_amount_in_three_days'] = 0;
                }

                //包年充值用户数
                $data['year_recharge_user_num'] = $data['pay_user_num'] ? (int)DB::table('orders')->where('created_at','>=',date('Y-m-d 00:00:00',strtotime($date)))->where('created_at','<=',date('Y-m-d 23:59:59',strtotime($date)))->where('order_type', 'YEAR')->where('status', 'PAID')->distinct('uid')->where('send_order_id', $send_order->id)->count() : 0;
                $data['ticket_recharge_user_num'] = $data['pay_user_num'] - $data['year_recharge_user_num'];
                $data['year_paid_num'] = $data['pay_user_num'] ? (int)DB::table('orders')->where('created_at','>=',date('Y-m-d 00:00:00',strtotime($date)))->where('created_at','<=',date('Y-m-d 23:59:59',strtotime($date)))->where('order_type', 'YEAR')->where('status', 'PAID')->where('send_order_id', $send_order->id)->count() : 0;
                $data['year_unpaid_num'] = $total_order_num ? (int)DB::table('orders')->where('created_at','>=',date('Y-m-d 00:00:00',strtotime($date)))->where('created_at','<=',date('Y-m-d 23:59:59',strtotime($date)))->where('order_type', 'YEAR')->where('status', 'UNPAID')->where('send_order_id', $send_order->id)->count() : 0;
                SendOrderForceDayStat::generateForceDayStat($data);
                $return_data[] = $data;
            }
            SendOrderStatService::generateStatsByDayStats($return_data,$date);
            //});
        }
        //return $return_data;
    }


    static function generateSingleForceDayStat($send_order_id,$date)
    {
        $end_send_time = date('Y-m-d H:i:s', strtotime($date) + 86400 - 1);

        $fields = DB::raw("
              send_order_id,
              count(1) paid_num,
              sum(price) recharge_amount,
              count(distinct uid) pay_user_num
        ");
        //TODO 解除limit
        $order_data = DB::table('orders')->select($fields)
            ->where('status', 'PAID')->where('created_at','>=', $date)
            ->where('send_order_id',$send_order_id)
            ->where('created_at','<=', $end_send_time)->groupBy('send_order_id')->get();
        $order_data_array = [];
        if ($order_data) {
            foreach ($order_data as $order) {
                $order_data_array[$order->send_order_id] = $order;
            }
        }
        $order_total_fields = DB::raw("
                send_order_id,
                count(1) order_num
        ");
        $order_total_data = DB::table('orders')->select($order_total_fields)
            ->where('created_at','>=', $date)
            ->where('created_at','<=', $end_send_time)
            ->where('send_order_id',$send_order_id)
            ->groupBy('send_order_id')->get();
        $order_total_data_array = [];
        if ($order_total_data) {
            foreach ($order_total_data as $_order) {
                $order_total_data_array[$_order->send_order_id] = $_order;
            }
        }

        //注册用户
        $register_fields = DB::raw("
              send_order_id,
              count(1) num
        ");
        $register_data = DB::table('users')->select($register_fields)
            ->where('created_at','>=', $date)
            ->where('created_at','<=', $end_send_time)
            ->where('send_order_id',$send_order_id)
            ->groupBy('send_order_id')->get();
        $register_data_array = [];
        if ($register_data) {
            foreach ($register_data as $_register_data) {
                $register_data_array[$_register_data->send_order_id] = $_register_data->num;
            }
        }


        DB::table('send_orders')->where('is_enable', 1)
            //->where('send_time', '<=', $end_send_time)
            ->where('id', '<=', $send_order_id)
            ->orderBy('id')->chunk(1000,function($send_orders) use($date,$order_data_array,$order_total_data_array,$register_data_array){
//        $send_orders = SendOrderService::search(compact('end_send_time'), true);
            $return_data = [];
            foreach ($send_orders as $send_order) {
                $data = [];
                $data['send_order_id'] = $send_order->id;
                $data['date'] = $date;
                $data['create_time'] = $send_order->created_at;
                $data['name'] = $send_order->name;
                $data['charge_type'] = $send_order->charge_type;

                if(isset($book_name_array[$send_order->book_id])){
                    $book_name = $book_name_array[$send_order->book_id];
                }else{
                    $book_name =self::getBookNameByBid($send_order->book_id);
                    $book_name_array[$send_order->book_id] = $book_name;
                }
                $data['book_name'] = $book_name;
                $data['bid'] = $send_order->book_id;
                $uv = SendOrderService::getUvInfo($send_order->id);
                $pv = SendOrderService::getPvInfo($send_order->id);

                $data['total_uv'] = isset($uv['total']) ? $uv['total'] : 0;
                $data['uv'] = isset($uv[$date]) ? $uv[$date] : 0;
                $data['total_pv'] = isset($pv['total']) ? $pv['total'] : 0;
                $data['pv'] = isset($pv[$date]) ? $pv[$date] : 0;
                $data['recharge_amount'] = isset($order_data_array[$send_order->id]) ? $order_data_array[$send_order->id]->recharge_amount : 0;
                $data['paid_num'] = isset($order_data_array[$send_order->id]) ? $order_data_array[$send_order->id]->paid_num : 0;
                $data['unpaid_num'] = isset($order_total_data_array[$send_order->id]) ? $order_total_data_array[$send_order->id]->order_num - $data['paid_num'] : 0;
                $data['pay_user_num'] = isset($order_data_array[$send_order->id]) ? $order_data_array[$send_order->id]->pay_user_num : 0;
                $data['distribution_channel_id'] = $send_order->distribution_channel_id;
                $data['distribution_channel_name'] = ChannelService::getChannelCompanyNameById($send_order->distribution_channel_id);//ChannelService::getChannelNicknameById($send_order->distribution_channel_id);
                $data['cost'] = $send_order->cost;
                $data['send_time'] = $send_order->send_time;

                $total_order_num = $data['unpaid_num'] + $data['paid_num'];

                //强关用户数
                $force_user_num = DB::table('force_subscribe_users')
                    ->where('send_order_id', $send_order->id)
                    ->where('created_at','>=',date('Y-m-d 00:00:00',strtotime($date)))
                    ->where('created_at','<=',date('Y-m-d 23:59:59',strtotime($date)))
                    ->count();
                $data['force_user_num'] = $force_user_num;

                //注册用户数
                $data['register_user_num'] = isset($register_data_array[$send_order->id]) ? $register_data_array[$send_order->id] : 0;

                //首充数据
                if ($data['pay_user_num']) {
                    $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 created_at>='{$begin_time}' and created_at<'{$end_time}' and send_order_id = '{$send_order->id}' and status ='PAID' and not exists (select uid from orders where a.uid = uid and created_at < '{$begin_time}' and status= 'PAID' limit 1)");
                    $data['first_recharge_user_num'] = (int)$first_data[0]->count;
                    $data['first_recharge_amount'] = (float)$first_data[0]->amount;
                } else {
                    $data['first_recharge_user_num'] = 0;
                    $data['first_recharge_amount'] = 0;
                }

                //12小时充值数据
                if ($send_order->send_time) {
                    $end_timestamp = min(strtotime($send_order->send_time) + 7 * 24 * 3600, strtotime($date) + 86400);
                    $end_time = date("Y-m-d H:i:s", $end_timestamp);
                    $data['recharge_amount_in_two_weeks'] = OrderService::getAmount(['send_order_id' => $send_order->id,  'end_time' => $end_time]);
                    $end_timestamp = min(strtotime($send_order->send_time) + 12 * 3600, strtotime($date) + 86400);
                    $end_time = date("Y-m-d H:i:s", $end_timestamp);
                    $data['recharge_amount_in_half_day'] = OrderService::getAmount(['send_order_id' => $send_order->id, 'end_time' => $end_time]);
                    //24小时
                    $end_time = date("Y-m-d H:i:s", min(strtotime($send_order->send_time) + 24 * 3600,strtotime($date) + 86400));
                    $data['recharge_amount_in_one_day'] = OrderService::getAmount(['send_order_id' => $send_order->id,  'end_time' => $end_time]);
                    //3天
                    $end_time = date("Y-m-d H:i:s", min(strtotime($send_order->send_time) + 3 * 24 * 3600,strtotime($date) + 86400));
                    $data['recharge_amount_in_three_days'] = OrderService::getAmount(['send_order_id' => $send_order->id,  'end_time' => $end_time]);
                } else {
                    $data['recharge_amount_in_two_weeks'] = 0;
                    $data['recharge_amount_in_half_day'] = 0;
                    $data['recharge_amount_in_one_day'] = 0;
                    $data['recharge_amount_in_three_days'] = 0;
                }
                //包年充值用户数
                $data['year_recharge_user_num'] = $data['pay_user_num'] ? (int)DB::table('orders')->where('created_at','>=',date('Y-m-d 00:00:00',strtotime($date)))->where('created_at','<=',date('Y-m-d 23:59:59',strtotime($date)))->where('order_type', 'YEAR')->where('status', 'PAID')->distinct('uid')->where('send_order_id', $send_order->id)->count() : 0;
                $data['ticket_recharge_user_num'] = $data['pay_user_num'] - $data['year_recharge_user_num'];
                $data['year_paid_num'] = $data['pay_user_num'] ? (int)DB::table('orders')->where('created_at','>=',date('Y-m-d 00:00:00',strtotime($date)))->where('created_at','<=',date('Y-m-d 23:59:59',strtotime($date)))->where('order_type', 'YEAR')->where('status', 'PAID')->where('send_order_id', $send_order->id)->count() : 0;
                $data['year_unpaid_num'] = $total_order_num ? (int)DB::table('orders')->where('created_at','>=',date('Y-m-d 00:00:00',strtotime($date)))->where('created_at','<=',date('Y-m-d 23:59:59',strtotime($date)))->where('order_type', 'YEAR')->where('status', 'UNPAID')->where('send_order_id', $send_order->id)->count() : 0;

                SendOrderForceDayStat::generateForceDayStat($data);
                $return_data[] = $data;
            }
            SendOrderStatService::generateStatsByDayStats($return_data,$date);
        });
        //return $return_data;
    }


    /**
     * 管理后台获历史取派单信息
     * @param $params 字段列表
     * @param string $isAll 是否获取所有
     * @return mixed
     */
    static function getHistorySendOrders($params = [], $is_all = false)
    {
        return SendOrderForceDayStat::getHistorySendOrders($params, $is_all);
    }

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

    private static function getBookNameByBid($bid){
        $book = BookConfig::where('bid',$bid)->select('book_name')->first();
        if($book)
            return $book->book_name;
        return 'unknown';
    }

    public static function getSendOrdersDayStatsByBid($date) {
        $sql = "select bid, book_name,sum(recharge_amount) as rechareg_amount,sum(uv) uv,
                   sum(pv) pv,sum(paid_num) success_order_num,book_categories.pid as type,date,
                   sum(unpaid_num) as unpaid_num,sum(register_user_num) as register_user_num,NOW() as created_at,NOW() as updated_at
                    from send_orders_force_day_stats force index(date_bid)
                    inner join books on books.id=send_orders_force_day_stats.bid
                    INNER JOIN book_categories ON book_categories.id=books.category_id
                     where total_uv > 20 and date ='{$date}'
										 group by bid";
        return \DB::select($sql);
    }
}