zhuishuyun_quickapp/app/Console/Commands/BookSpider.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use GuzzleHttp\Client;
use DB;

class BookSpider extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'zytest';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'zytest';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
        $this->client = new Client(['timeout'  => 8.0,'allow_redirects'=>true]);
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //32401811
        $this->cpc();
    }

    private function cpc(){
        //32426552

        $sql_format = "SELECT u.id,f.created_at as subscribe_time,u.openid,
(SELECT ifnull(sum(price),0) from orders where uid = u.id and `status` = 'PAID' LIMIT 1)as amount,
(SELECT ifnull(sum(price),0) from orders where uid=u.id and `status` = 'PAID' and created_at <= DATE_ADD(f.created_at,INTERVAL 3 day)) as three_day_amount
  FROM users u
JOIN force_subscribe_users f on u.id = f.uid 
WHERE u.openid in (SELECT openid from users WHERE  id = %s)";
        $all = 32427;
        for ($i =0;$i<=$all;$i++){
            $subscribes = DB::table('force_subscribe_users')->where('id','>',$i*1000)->select('uid')->limit(1000)->get();
            if($subscribes->isEmpty()){
                break;
            }
            foreach ($subscribes as $value){
                $result = DB::select(sprintf($sql_format,$value->uid));
                if($result){
                    $user_property_info = $this->level($result);
                    $this->create($user_property_info);
                }
            }
        }
    }

    private function level($res){
        $earliest_subscribe_time = $res[0]->subscribe_time;
        $subscribe_three_day_info = [];
        $subscribe_no_three_day_info = [];
        foreach ($res as $v){
            (strtotime($v->subscribe_time) < strtotime($earliest_subscribe_time)) && $earliest_subscribe_time = $v->subscribe_time;
            if(time()-strtotime($v->subscribe_time) >= 86400*3){
                array_push($subscribe_three_day_info,$v->amount);
            }else{
                array_push($subscribe_no_three_day_info,$v->three_day_amount);
            }
        }
        $result = [
            'earliest_subscribe_time'=>$earliest_subscribe_time,
            'property'=>'',
            'type'=>'',
            'openid'=> $res[0]->openid
        ];
        if($subscribe_three_day_info){
            //存量用户
            $result['type'] = 'CUILIANG';
            $amount = round(array_sum($subscribe_three_day_info)/count($subscribe_three_day_info),2);
            if($amount>15){
                $result['property'] = 'high';
            }elseif($amount >2){
                $result['property'] = 'medium';
            } elseif($amount >0){
                $result['property'] = 'low';
            } else{
                $result['property'] = 'none';
            }
        }else{
            //新用户
            $result['type'] = 'NEW';
            if($subscribe_no_three_day_info)
                $amount = max($subscribe_no_three_day_info);
            else
                $amount = 0;
            if($amount>50){
                $result['property'] = 'high';
            }elseif($amount >2){
                $result['property'] = 'medium';
            } elseif($amount >0){
                $result['property'] = 'low';
            } else{
                $result['property'] = 'none';
            }
        }

        return $result;
    }

    private function create($data){
        $old = DB::table('user_division_cpc_property')->where('openid',$data['openid'])->where('is_enable',1)->first();
        if(!$old){
            DB::table('user_division_cpc_property')->insert([
               'openid'=>$data['openid'] ,
               'property'=>$data['property'] ,
               'is_enable'=>1 ,
               'type'=>$data['type'] ,
               'earliest_subscribe_time'=>$data['earliest_subscribe_time'] ,
                'created_at'=>date('Y-m-d H:i:s'),
                'updated_at'=>date('Y-m-d H:i:s')
            ]);
        }
    }

}