zhuishuyun_quickapp/app/Console/Commands/selectCrmSites.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use DB;
use Redis;

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

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

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $result = $this->selectSites();
        $data = [];
        $times = DB::table('crm_other_channels')->max('times');
        $times = $times?$times:0;
        $i = 1;
        foreach ($result as $item){
            $data[] = [
                'distribution_channel_id'=>$item,
                'times'=>$times+1,
                'created_at'=>date('Y-m-d H:i:s'),
                'updated_at'=>date('Y-m-d H:i:s')
            ];
            if($i% 100 == 0){
                DB::table('crm_other_channels')->insert($data);
                $data = [];
            }
            $i++;
        }

        if($data){
            DB::table('crm_other_channels')->insert($data);
        }
        Redis::del('crm:out_channel_sites');
        Redis::sadd('crm:out_channel_sites',$result);
    }

    public function selectSites(){
        $distribution_channel_id = $this->getSiteNoRegister();
        $setServiceSites = $this->getSiteSetServices();
        $opTenCompanyInfo = $this->getTopTenCompanySite();
        $innerSites = $this->getInnerSite();
        $result = [];
        foreach ($distribution_channel_id as $item){
            if(Redis::Hexists('specialChannelAuthInfoV2',$item)) continue;
            //去除设置了二维码的站点
            if(in_array($item,$setServiceSites)) continue;
            //去除内部站点
            if(in_array($item,$innerSites)) continue;
            //1个月的流水排名前十公司排除
            if(in_array($item,$opTenCompanyInfo)) continue;

            $result[] = $item;
        }
        return $result;
    }

    public function getTopTenCompanySite(){
        $data = $this->getTopTenCompanyInfo();
        $res = [];
        foreach ($data as $v){
            $res = array_merge($res,$v['distribution_channel_id']);
        }
        return $res;
    }
    //最近1个月的流水排名前十公司
    public function getTopTenCompanyInfo(){
        //order_day_stats
        $sql = 'SELECT company_id,GROUP_CONCAT( DISTINCT a.distribution_channel_id) as distribution_channel_id,GROUP_CONCAT(DISTINCT b.channel_user_id) as channel_user_ids ,SUM(a.total_recharge_amount) as total_recharge_amount  FROM order_day_stats a JOIN 
distribution_channels b on a.distribution_channel_id = b.id 
JOIN channel_users c on  b.channel_user_id = c.id
where a.date >= date(DATE_ADD(NOW(),INTERVAL -1 MONTH))
and c.company_id not in (1,2,3,18,760)
GROUP BY c.company_id
ORDER BY total_recharge_amount desc 
LIMIT 10';
        $result = DB::select($sql);
        $data = [];
        foreach ($result as $item){
            $data[] = [
                'company_id'=>$item->company_id,
                'distribution_channel_id'=>explode(',',$item->distribution_channel_id),
                'channel_user_ids'=>explode(',',$item->channel_user_ids),
                'total_recharge_amount'=>$item->total_recharge_amount
            ];
        }
        $res = [];
        foreach ($data as $v){
            $res = array_merge($res,$v['distribution_channel_id']);
        }

        return $data;
    }

    //内部账号
    public function getInnerSite():array{
        $sql = 'SELECT distribution_channels.id FROM distribution_channels JOIN channel_users on distribution_channels.channel_user_id = channel_users.id 
where channel_users.company_id in (1,2,3,18,760)';
        $result = DB::select($sql);
        $data = [];
        foreach ($result as $item){
            $data[] = $item->id;
        }
        return $data;
    }

    //最近两周没有新增注册用户的其他渠道站点（小于100）
    public function getSiteNoRegister():array{
        $sql = 'SELECT distribution_channel_id,COUNT(users.id) FROM users 
JOIN distribution_channels on users.distribution_channel_id = distribution_channels.id
WHERE users.created_at >= date(DATE_ADD(NOW(),INTERVAL -2 WEEK))
and distribution_channels.created_at <= date(DATE_ADD(NOW(),INTERVAL -1 MONTH))
and distribution_channel_id >0
GROUP BY distribution_channel_id
HAVING COUNT(users.id) <100';
        $result = DB::select($sql);
        $data = [];
        foreach ($result as $item){
            $data[] = $item->distribution_channel_id;
        }
        return $data;
    }

    //站点所用客服二维码为平台官方
    public function getSiteSetServices():array{
        $sql = 'SELECT distribution_channel_id FROM distribution_channel_settings';
        $result = DB::select($sql);
        $data = [];
        foreach ($result as $item){
            $data[] = $item->distribution_channel_id;
        }
        return $data;
    }
}