zhuishuyun_quickapp/app/Console/Commands/BookTest.php

<?php

namespace App\Console\Commands;

use App\Modules\Book\Services\BookSubscribleChapterService;
use App\Modules\Subscribe\Services\ChapterOrderService;
use App\Modules\YunQi\Models\BookUser;
use App\Modules\YunQi\Models\YqUserBidRelation;
use App\Modules\YunQi\Models\YqZsyTest;
use App\Modules\YunQi\Services\YqZsyTestService;
use GuzzleHttp\Client;
use Illuminate\Console\Command;

use Log;
use DB;
use Redis;
use App\Modules\Book\Models\Book;
use App\Modules\Book\Models\Chapter;
use App\Modules\SendOrder\Models\SendOrder;
use App\Modules\SendOrder\Services\SendOrderService;
use App\Modules\Subscribe\Models\Order;
use App\Jobs\SendStatisticsList;
use App\Modules\OfficialAccount\Models\ForceSubscribeUsers;
use App\Modules\User\Services\UserSignService;
use App\Modules\Book\Models\BookUvStat;
use App\Modules\Subscribe\Models\ChapterOrder;

class BookTest extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'book:test
                                {--bid= : the id of a book}';

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

    /**
     * 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()
    {
        //$this->data1();
        //$this->testTest();
        //$this->activity();
        //$this->testBookSmartPushStats();
        //$this->sendOrderStats105();
        $this->you2();
    }


    public function activity()
    {
        $res = \App\Modules\Activity\Services\ActivityService::ActivityStatsI(16);
        $str = "日期,site id,活动页面访问uv,按钮uv,生成订单数,成功支付订单数,首充用户数,点击阅读页面文字链uv,阅读页面文字链订单数,阅读页面文字链成功订单数,阅读页面文字链首充用户数,签到回调文字链uv,签到回调文字链生成订单数,签到回调文字链成功成功订单数,签到回调文字链首充用户数\r\n";
        $str = mb_convert_encoding($str, 'gbk');
        $file_name = date('Y-m-d') . '.csv';
        file_put_contents($file_name, $str);
        $temp = '';
        foreach ($res as $val) {
            $temp .= "{$val['day']},{$val['siteid']},{$val['uv']},{$val['button_uv']},{$val['order_num']},{$val['success_order_num']},{$val['first_charge_num']},{$val['reader_uv']},{$val['reader_order']},{$val['reader_success_order']},{$val['reader_first_charge']},{$val['signcallback_uv']},{$val['signcallback_order']},{$val['signcallback_success_order']},{$val['signcallback_first_charge']}\r\n";
        }
        file_put_contents($file_name, $temp, FILE_APPEND);
    }

    public function data1(){
        //1,图书uv
        $keys = Redis::SMEMBERS('book_before_five_uvs');
        if(!$keys){
            return 0;
        }
        $data = [];
        $book_name_arr = [];
        $i = 0;
        foreach ($keys as $k){
            $k_array = explode('_',$k);
            $uv = Redis::scard('book_before_five_uv_'.$k);
            if(isset($book_name_arr[$k_array[1]]) &&  !empty($book_name_arr[$k_array[1]])){
                $book_name = $book_name_arr[$k_array[1]];
            }else{
                $book_name_info = DB::table('book_configs')->where('bid',$k_array[1])->select('book_name')->first();
                if($book_name_info){
                    $book_name_arr[$k_array[1]] = $book_name_info->book_name;
                    $book_name = $book_name_info->book_name;
                }else{
                    $book_name = '未知';
                }
            }

            $data[] = [
                'bid'=>$k_array[1],
                'day'=>$k_array[0],
                'sequence'=>$k_array[2],
                'book_name'=>$book_name,
                'uv'=>$uv,
                'created_at'=>date('Y-m-d H:i:s'),
                'updated_at'=>date('Y-m-d H:i:s')
            ];
            $i++;
            if($i && $i%200 == 0){
                DB::table('book_five_chapter_uv')->insert($data);
                $data = [];
            }
        }
        DB::table('book_five_chapter_uv')->insert($data);
    }

    public function data2(){
        //强关
        $keys = Redis::SMEMBERS('subscribe_page_uv_send_order_ids');
        $data = [];
        $i = 0;
        foreach ($keys as $k){
            $subscribe_uv = Redis::scard('subscribe_page_uv'.$k);
            $data[] = [
                'send_order_id'=>$k,
                'subscribe_uv'=>$subscribe_uv,
                'pay_uv'=>0,
                'created_at'=>date('Y-m-d H:i:s'),
                'updated_at'=>date('Y-m-d H:i:s')
            ];
            $i++;
            if($i && $i%200 == 0){
                DB::table('subscribe_pay_page_uv')->insert($data);
                $data = [];
            }
        }
        $keys = null;
        $k = null;
        DB::table('subscribe_pay_page_uv')->insert($data);
        $data = [];
        //支付
        $keys = Redis::SMEMBERS('pay_page_uv_send_order_ids');
        foreach ($keys as $v){
            $pay_uv = Redis::scard('pay_page_uv'.$v);
            $o = DB::table('subscribe_pay_page_uv')->where('send_order_id',$v)->first();
            if($o){
                DB::table('subscribe_pay_page_uv')->where('send_order_id',$v)->update([
                    'pay_uv'=>$pay_uv
                ]);
            }else{
                DB::table('subscribe_pay_page_uv')->insert([
                    'send_order_id'=>$v,
                    'subscribe_uv'=>0,
                    'pay_uv'=>$pay_uv,
                    'created_at'=>date('Y-m-d H:i:s'),
                    'updated_at'=>date('Y-m-d H:i:s')
                ]);
            }
        }

    }

    public function testTest(){
        $res = \App\Modules\Statistic\Services\WapVisitStatService::smartPushTestBookStats(6);
        echo \Hashids::encode(6).PHP_EOL;
        print_r($res);
    }

    private function testBookSmartPushStats(){
        $sql1 = 'TRUNCATE test_book_smart_push_stats';
        DB::update($sql1);
        $result = DB::table('book_configs')->whereIn('test_status',[1,2])->select('bid','vip_seq')->get();
        /*foreach ($result as $v){
            t($v->bid,$v->vip_seq);
        }*/
        function t($bid,$vip_seq){
            $data = [];
            $key1 = 'smartPushTestBookPayPageUv:bid:%s';
            $pay_page_uv = (int)(Redis::scard(sprintf($key1,$bid)));

            $key2 = 'smartPushTestBookPaidUv:bid:%s';
            $paid_num = (int)(Redis::scard(sprintf($key2,$bid)));

            $amount = Redis::Hget('smartPushTestBookPaidAmount',$bid);
            if(!$amount)
                $amount = 0;

            for ($i = 1;$i<=60;$i++){
                $key3 = 'smartPushTestBookChapterUv:bid:%s:seq:%s';
                $sequence_uv = (int)(Redis::scard(sprintf($key3,$bid,$i)));
                if(!$sequence_uv){
                    continue;
                }
                $data[] = [
                    'bid'=>$bid,
                    'sequence'=>$i,
                    'sequence_uv'=>$sequence_uv,
                    'vip_sequence'=>$vip_seq,
                    'pay_page_uv'=>$pay_page_uv,
                    'paid_user_num'=>$paid_num,
                    'amount'=>$amount,
                    'created_at'=>date('Y-m-d H:i:s'),
                    'updated_at'=>date('Y-m-d H:i:s')
                ];
            }
            if($data){
                DB::table('test_book_smart_push_stats')->insert($data);
            }
        }
        foreach ($result as $v){
            t($v->bid,$v->vip_seq);
        }
    }

    private function sendOrderStats105(){
        $sql1 = 'TRUNCATE send_order_stats_105';
        DB::update($sql1);
        $send_order_ids = Redis::hgetall('sendOrderIdToBid');
        if(!$send_order_ids){
            return ;
        }

        function one(int $sendOrderId,int $time){
            $info = SendOrderService::getById($sendOrderId);
            if(!$info || !$info->book_id){
                return ;
            }
            $subscribe_chaper_seq = 0;
            if($info && $info->subscribe_chapter_seq){
                $subscribe_chaper_seq = $info->subscribe_chapter_seq;
            }
            if(!$subscribe_chaper_seq){
                $subscribe = BookSubscribleChapterService::getSubcribleChapter($info->book_id,$info->distribution_channel_id);
                if ($subscribe) {
                    $subscribe_chaper_seq =   (isset($subscribe->subscribe_chapter_id) && $subscribe->subscribe_chapter_id > 0) ? $subscribe->subscribe_chapter_id : 0;
                }
            }

            $key_1 = 'accurateSendOrderSubscribePageUv:bid:%s:sendorderid:'.$sendOrderId;
            //强关页面uv
            $subscribe_uv = (int)(Redis::scard(sprintf($key_1,$info->book_id)));

            $key_2 = 'accurateSendOrderPayPageUv:bid:%s:send_order_id:'.$sendOrderId;
            //付费页面UV
            $pay_page_uv = (int)(Redis::scard(sprintf($key_2,$info->book_id)));
            //强关数
            $subscribe_num = DB::table('force_subscribe_users')
                ->where('send_order_id',$sendOrderId)
                ->where('bid',$info->book_id)
                ->where('created_at','>=',date('Y-m-d H:i:s',$time))
                ->count();

            $paid_user_num = DB::table('orders')
                ->where('send_order_id',$sendOrderId)
                ->where('from_bid',$info->book_id)
                ->where('status','PAID')
                ->where('created_at','>=',date('Y-m-d H:i:s',$time))
                ->count();
            $book_info = DB::table('book_configs')->where('bid',$info->book_id)->select('book_name','force_subscribe_chapter_seq','vip_seq')->first();
            $send_order_chapter_sequence_info = DB::table('chapters')->where('id',$info->chapter_id)->select('sequence')->first();
            $send_order_chapter_sequence = 0;
            if($send_order_chapter_sequence_info && $send_order_chapter_sequence_info->sequence){
                $send_order_chapter_sequence = $send_order_chapter_sequence_info->sequence;
            }
            if(!$subscribe_chaper_seq){
                $subscribe_chaper_seq = $book_info->force_subscribe_chapter_seq;
            }
            $vip_seq = 0;
            $vip_seq_info = Chapter::where('bid',$info->book_id)->where('is_vip',1)->orderBy('sequence')->select('sequence')->first();
            if($vip_seq_info){
                $vip_seq = $vip_seq_info->sequence;
            }
            $data = [];
            for ($i = 1;$i<=30;$i++){
                $key_3 = 'accurateSendOrderChapterUv:bid:%s:seq:%s:sendorderid:'.$sendOrderId;
                $chapter_seq_uv = (int)(Redis::scard(sprintf($key_3,$info->book_id,$i)));
                if(!$chapter_seq_uv){
                    continue;
                }
                $data[] = [
                    'send_order_id'=>$sendOrderId,
                    'send_order_chapter_seq'=>$send_order_chapter_sequence,
                    'bid'=>$info->book_id,
                    'book_name'=>$book_info->book_name,
                    'chaper_sequence'=>$i,
                    'chapter_uv'=>$chapter_seq_uv,
                    'subscribe_chaper_seq'=>$subscribe_chaper_seq,
                    'subscribe_page_uv'=>$subscribe_uv,
                    'subscribe_user_num'=>$subscribe_num,
                    'vip_chapter_sequence'=>$vip_seq,
                    'pay_page_uv'=>$pay_page_uv,
                    'paid_user_num'=>$paid_user_num,
                    'created_at'=>date('Y-m-d H:i:s'),
                    'updated_at'=>date('Y-m-d H:i:s')
                ];
           }
           if($data){
               DB::table('send_order_stats_105')->insert($data);
           }
        }

        foreach ($send_order_ids as $send_order_id=> $v){
            $array = explode('-',$v);
            if(count($array) == 2){
                one($send_order_id,$array[1]);
            }
        }
    }

    private function you2(){
        $sql1 = 'TRUNCATE yq_stats2';
        DB::update($sql1);

        $info = BookUser::where('type','ENABLE')->select('bid','uid','type','updated_at')
            ->orderBy('updated_at')->limit(1500)->get();
        $info2 = BookUser::where('type','GROUP_1')
            ->select('bid','uid','type','updated_at')->orderBy('updated_at')->limit(1500)->get();
        $this->you2Type($info,'GROUP_2');
        $this->you2Type($info2,'GROUP_1');
    }


    private function you2Type($info,$type){
        $result = [];
        $chapter_model = new ChapterOrder();
        foreach ($info as $item){
            $to_bids = YqUserBidRelation::where('uid',$item->uid)->where('to_bid','>',0)->whereColumn('to_bid','!=','from_bid')->select('to_bid','created_at')->get();
            $from_bids = YqUserBidRelation::where('uid',$item->uid)->where('from_bid','>',0)->whereColumn('to_bid','!=','from_bid')->select('from_bid','created_at')->get();
            $chapter_model->setCurrentTable($item->uid);
            if($to_bids){
                foreach ($to_bids as $v){
                    $to_bid = $v->to_bid;
                    $time = $v->created_at->format('Y-m-d H:i:s');
                    if(!$to_bid) continue;
                    $fee = $chapter_model->where('uid',$item->uid)
                        ->where('bid',$to_bid)
                        ->where('created_at','>=',$time)
                        ->sum('fee');
                    $amount = Order::where('uid',$item->uid)
                        ->where('from_bid',$to_bid)
                        ->where('created_at','>=',$time)
                        ->where('status','PAID')
                        ->sum('price');
                    $result[] = [
                        'bid'=>$to_bid,
                        'uid'=>$item->uid,
                        'fee'=>$fee,
                        'amount'=>$amount,
                        'start_time'=>$time,
                        'type'=>'to',
                        'group'=>$type,
                        'created_at'=>date('Y-m-d H:i:s'),
                        'updated_at'=>date('Y-m-d H:i:s')
                    ];
                }
                if($result){
                    DB::table('yq_stats2')->insert($result);
                    $result = [];
                }
            }
            $to_bid = null;
            if($from_bids){
                foreach ($from_bids as $from_bid){
                    $bid = $from_bid->from_bid;
                    $test = YqZsyTestService::getByBid($bid);
                    if($test) continue;
                    $time = $from_bid->created_at->format('Y-m-d H:i:s');
                    $fee = $chapter_model->where('uid',$item->uid)
                        ->where('bid',$bid)
                        ->where('created_at','>=',$time)
                        ->sum('fee');
                    $amount = Order::where('uid',$item->uid)
                        ->where('from_bid',$bid)
                        ->where('created_at','>=',$time)
                        ->where('status','PAID')
                        ->sum('price');
                    $result[] = [
                        'bid'=>$bid,
                        'uid'=>$item->uid,
                        'fee'=>$fee,
                        'amount'=>$amount,
                        'start_time'=>$time,
                        'type'=>'from',
                        'group'=>$type,
                        'created_at'=>date('Y-m-d H:i:s'),
                        'updated_at'=>date('Y-m-d H:i:s')
                    ];
                }
                if($result){
                    DB::table('yq_stats2')->insert($result);
                    $result = [];
                }
            }
        }
    }

}