wangdu_public_api/app/Console/Commands/Trade/GenerateOrderDayStat.php

<?php
/**
 * Created by PhpStorm.
 * User: songdb
 * Date: 2017/12/26
 * Time: 下午5:26
 */

namespace App\Console\Commands\Trade;

use App\Modules\SendOrder\Services\SendOrderForceDayStatService;
use App\Modules\Trade\Models\OrderDayStat;
use App\Modules\Trade\Services\OrderDayStatService;
use App\Modules\Channel\Services\ChannelService;
use App\Modules\Trade\Services\OrderStatService;
use Log;
use Illuminate\Console\Command;
use DB;

class GenerateOrderDayStat extends Command
{
    /**
     * 执行命令   php artisan generate_order_day_stat
     *
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'generate_order_day_stat';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '渠道订单日统计数据生成';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        print_r("======渠道订单日统计数据生成 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));

        Log::info("======渠道订单日统计数据生成 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));

        $channels = ChannelService::getAllChannels();

        $date = date('Y-m-d',strtotime('-1 day'));
        $end_time = date('Y-m-d H:i:s',strtotime($date) + 86400-1);
        if(count($channels))
        {

            $min_uid = DB::table('users')->where('created_at','>=',$date)->min('id');
            $max_uid = DB::table('users')->where('created_at','>=',$date)->where('created_at','<=',$end_time)->max('id');
            $once_num = 5000;
            $offset = 0;
            //实际注册用户数
            $start_uid = $min_uid;
            $real_reg_data = [];
            while (true)
            {
                $offset += $once_num;
                $end_user = DB::table('users')->select('id')->where('created_at','>=',$date)->where('created_at','<=',$end_time)->skip($offset)->limit(1)->first();
                $end_uid = $end_user ? $end_user->id : $max_uid;
                $reg_data = DB::select("select distribution_channel_id,count(1) num from users u where id >= {$start_uid} and id <= {$end_uid} and not exists (select id from users where openid = u.openid and id < {$start_uid} limit 1) group by distribution_channel_id");

                foreach ($reg_data as $_reg_data)
                {
                    @$real_reg_data[$_reg_data->distribution_channel_id] += (int)$_reg_data->num;
                }
                if($end_uid == $max_uid) break;

                $start_uid = $end_uid;
            }

            $channels->each(function($channel) use($date,$real_reg_data){
                $begin_time = strtotime($date)+86400;
                $real_reg_num = isset($real_reg_data[$channel->id]) ? $real_reg_data[$channel->id] : 0;
                if(strtotime($channel->created_at) <= $begin_time) OrderDayStatService::add($channel->id,$channel->channel_user_id, $date,$channel->nickname,$real_reg_num);
            });
            print_r("------订单汇总表更新start---");
            Log::info("======订单汇总表更新 【任务执行开始】=====".date("y-m-d H:i:s"."\n"));
            $channels->each(function($channel) use($date){
                OrderStatService::add($channel->id, $date);
            });
            Log::info("======订单汇总表更新 【任务执行结束】=====".date("y-m-d H:i:s"."\n"));
            print_r("-----订单汇总表更新end----");

            //更新订阅
            $this->generate_temp_table($date,$end_time);
            $subscribes = $this->getChannelSubscribeNumAndAmount($date);
            $channels->each(function($channel) use($subscribes,$date){
                $subscribe_num=0;
                $subscribe_amount=0;
                if(array_key_exists($channel->id,$subscribes)){
                    $subscribe_num = $subscribes[$channel->id]['user_num'];
                    $subscribe_amount =   $subscribes[$channel->id]['sum_fee'];
                }
                OrderDayStat::where('date',$date)
                    ->where('distribution_channel_id',$channel->id)
                    ->update(['subscribe_user_num'=>$subscribe_num,'subscribe_amount'=>$subscribe_amount]);
            });
            /*$gender_recharges = OrderDayStatService::getRechargeByGender($date,$end_time);
            foreach ($gender_recharges as $key=>$gender_recharge) {
                $order_day_stats = OrderDayStat::where('distribution_channel_id',$key)->where('date',$date)->first();
                if($order_day_stats){
                    $male_channel_recharge=$female_channel_recharge =0;
                    $gender_total = 0;
                    if(isset($gender_recharge[1])) {
                        $male_channel_recharge = $gender_recharge[1];
                        $gender_total += $male_channel_recharge;

                    }
                    if(isset($gender_recharge[2])) {
                        $female_channel_recharge = $gender_recharge[2];
                        $gender_total += $female_channel_recharge;

                    }
                    $left=$order_day_stats->total_recharge_amount -$gender_total;
                    $male_channel_recharge +=$left*($male_channel_recharge/$gender_total);
                    $female_channel_recharge +=$left*($female_channel_recharge/$gender_total);
                    $order_day_stats->male_channel_recharge=$male_channel_recharge;
                    $order_day_stats->female_channel_recharge=$female_channel_recharge;
                    $order_day_stats->save();

                }

            }*/
            //$date = date('Y-m-d',strtotime('-'.$i.' day'));
            /*sleep(5);
            $end_order_stats = DB::table('send_orders_force_day_stats')
                ->join('books','send_orders_force_day_stats.bid','=','books.id')
                ->join('book_categories','book_categories.id','=','books.category_id')
                ->where('send_orders_force_day_stats.date',$date)
                ->select([DB::raw('sum(send_orders_force_day_stats.recharge_amount) as total_recharge'),'pid','send_orders_force_day_stats.distribution_channel_id'])
                ->groupBy('send_orders_force_day_stats.distribution_channel_id')
                ->groupBy('book_categories.pid')
                ->get();
            $data_format = [];
            if($end_order_stats) {
                foreach ($end_order_stats as $key=>$end_order_stat) {
                    if(array_key_exists($end_order_stat->distribution_channel_id,$data_format)) {
                        $data_format[$end_order_stat->distribution_channel_id][$end_order_stat->pid]=$end_order_stat->total_recharge;
                    }else{
                        $data_format[$end_order_stat->distribution_channel_id]=array();
                        $data_format[$end_order_stat->distribution_channel_id][$end_order_stat->pid]=$end_order_stat->total_recharge;
                    }
                }

                foreach ($data_format as $k=>$each){
                    $male_recharge = $female_recharge = 0;
                    $total_recharge = 0;
                    foreach ($each  as $kk=>$v){
                        if($kk==1) {
                            $male_recharge = $v;
                            $total_recharge +=$v;
                        }
                        if($kk==2) {
                            $female_recharge =$v;
                            $total_recharge +=$v;
                        }
                    }
                    $order_day_stats = OrderDayStat::where('distribution_channel_id',$k)->where('date',$date)->first();
                    if($order_day_stats) {
                        $left = $order_day_stats->total_recharge_amount-$total_recharge;
                        if($male_recharge>0 || $female_recharge>0) {
                            $male_total = $male_recharge+($male_recharge/$total_recharge)*$left;
                            $female_total = $female_recharge+($female_recharge/$total_recharge)*$left;
                        }else{
                            $male_total = ($order_day_stats->total_recharge_amount)*0.2;
                            $female_total = ($order_day_stats->total_recharge_amount)*0.8;
                        }
                        $order_day_stats->male_channel_recharge = $male_total;
                        $order_day_stats->female_channel_recharge = $female_total;
                        $order_day_stats->save();
                    }
                }
            }
            $order_day_statss =  OrderDayStat::whereNull('male_channel_recharge')->whereNull('female_channel_recharge')->where('date',$date)->get();
            foreach($order_day_statss as $order_day_stats) {
                $order_day_stats->male_channel_recharge = ($order_day_stats->total_recharge_amount)*0.2;
                $order_day_stats->female_channel_recharge = ($order_day_stats->total_recharge_amount)*0.8;
                $order_day_stats->save();
            }

            $end_order_stats = DB::table('send_orders_force_day_stats')
                ->join('books','send_orders_force_day_stats.bid','=','books.id')
                ->join('book_categories','book_categories.id','=','books.category_id')
                ->where('send_orders_force_day_stats.date',$date)
                ->select([DB::raw('sum(send_orders_force_day_stats.recharge_amount) as total_recharge'),'pid'])
                ->groupBy('book_categories.pid')
                ->get();
            $male_recharge=$female_recharge=0;
            foreach ($end_order_stats as $end_order_stat){
                if($end_order_stat->pid==1) $male_recharge = $end_order_stat->total_recharge;
                if($end_order_stat->pid==2) $female_recharge = $end_order_stat->total_recharge;
            }
            $insert_data = compact('male_recharge','female_recharge');
            $insert_data['date'] = $date;
            $insert_data['created_at']=date('Y-m-d H:i:s');
            $insert_data['updated_at']=date('Y-m-d H:i:s');
            DB::table('order_gender_stats')->insert($insert_data);*/
        }


        Log::info("======渠道订单日统计数据生成 【任务执行结束】=====".date("y-m-d H:i:s"."\n"));
        print_r("======渠道订单日统计数据生成 【任务执行结束】=====".date("y-m-d H:i:s"."\n"));
    }

    protected function generate_temp_table($date,$end_time){
        DB::connection('chapter_order_mysql')->update("truncate table temp_chapter_order");
        DB::connection('chapter_order_mysql')->update("call temp_chapter_order_update('{$date}','{$end_time}')");
        DB::table('book_orders')
            ->select(['uid','distribution_channel_id',DB::raw("date(created_at) as date"),DB::raw("sum(fee) as fee")])
            ->whereBetween('created_at',[$date,$end_time])
            ->groupBy('uid')
            ->orderBy('id')
            ->chunk(5000,function($book_orders){
                $book_orders=json_decode(json_encode($book_orders),true);
                DB::connection('chapter_order_mysql')->table('temp_chapter_order')->insert($book_orders);
            });
    }

    protected function getChannelSubscribeNumAndAmount($date){
        $orders = DB::connection('chapter_order_mysql')
            ->table('temp_chapter_order')
            ->select(DB::raw("distribution_channel_id,count(distinct uid) as user_num,sum(fee) as sum_fee"))
            ->where('date',$date)
            ->groupBy('distribution_channel_id')
            ->get();
        $res=[];
        foreach ($orders as $order){
            $res[$order->distribution_channel_id] = json_decode(json_encode($order),true);
        }
        return $res;
    }
}