zhuishuyun_quickapp/app/Console/Commands/BookTest.php

<?php

namespace App\Console\Commands;

use App\Jobs\SendTexts;
use App\Modules\Book\Services\BookSubscribleChapterService;
use App\Modules\Product\Services\ProductService;
use App\Modules\Subscribe\Services\ChapterOrderService;
use App\Modules\Subscribe\Services\SubstituteOrderService;
use App\Modules\User\Services\ReadRecordService;
use App\Modules\User\Services\UserService;
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();
        //$this->transfromUserOrder();
        //$this->clearUserReadRecord();
        //$this->deleteToLongReadRecord();
        $this->clearUserReadRecord();
    }


    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);
            Redis::del('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);
        Redis::del('book_before_five_uvs');
    }

    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);
            Redis::del('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);
            Redis::del('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')
                ]);
            }
        }
        Redis::del('subscribe_page_uv_send_order_ids');
        Redis::del('pay_page_uv_send_order_ids');
    }

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

    private function testBookChapterStats(){

        function t($bid,$vip_seq){
            $data = [];
            $key1 = 'smartPushTestBookPayPageUv:bid:%s';
            $pay_page_uv = 0;

            $key2 = 'smartPushTestBookPaidUv:bid:%s';
            $paid_num = 0;

            $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')
                ];
                //Redis::del($sequence_uv);
            }
            if($data){
                DB::table('test_book_smart_push_stats20190709')->insert($data);
            }
        }
        for ($i = 1;$i<6539;$i++){
            t($i,0);
        }
    }
    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)));
            Redis::del( 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)));

            Redis::del( 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){
                if($book_info){
                    $subscribe_chaper_seq = $book_info->force_subscribe_chapter_seq;
                }else{
                    $subscribe_chaper_seq = 0;
                }
            }
            $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)));

                Redis::del( 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_info->book_name:'xx',
                    '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]);
            }
        }

        Redis::del('sendOrderIdToBid');
    }

    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)->select('to_bid','created_at')->get();
            $from_bids = YqUserBidRelation::where('uid',$item->uid)->where('from_bid','>',0)->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');
                    $end_time = date('Y-m-d H:i:s',strtotime($time)+2*86400);
                    if(!$to_bid) continue;
                    $fee = $chapter_model->where('uid',$item->uid)
                        ->where('bid',$to_bid)
                        ->where('created_at','>=',$time)
                        ->where('created_at','<=',$end_time)
                        ->sum('fee');
                    $amount = Order::where('uid',$item->uid)
                        ->where('from_bid',$to_bid)
                        ->where('created_at','>=',$time)
                        ->where('created_at','<=',$end_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');
                    $end_time = date('Y-m-d H:i:s',strtotime($time)+2*86400);
                    $fee = $chapter_model->where('uid',$item->uid)
                        ->where('bid',$bid)
                        ->where('created_at','>=',$time)
                        ->where('created_at','<=',$end_time)
                        ->sum('fee');
                    $amount = Order::where('uid',$item->uid)
                        ->where('from_bid',$bid)
                        ->where('created_at','>=',$time)
                        ->where('created_at','<=',$end_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 = [];
                }
            }
        }
    }

    private function transfromUserOrder(){
        $user = DB::table('users')
            ->where('distribution_channel_id',4815)
            ->where('created_at','>=','2019-01-31 15:00:00')
            ->select('openid','id')
            ->get();
        $chapter_model = new ChapterOrder();
        foreach ($user as $v){
            Log::info('now user is :');
            Log::info('openid is: '.$v->openid.',old uid is :'.$v->id);
            $froce_user = DB::table('force_subscribe_users')
                ->where('openid',$v->openid)
                ->where('distribution_channel_id',384)
                ->select('uid')
                ->first();
            if(!$froce_user){
                continue;
            }
            Log::info('$froce_user user is :');
            Log::info('$froce_user is: '.$froce_user->uid);

            if($froce_user && $froce_user->uid != $v->id){
                $chapter_model->setCurrentTable($froce_user->uid);
                $data = [];
                //章节订购
                $chapter_order_info = $chapter_model->where('uid',$froce_user->uid)->get();
                Log::info('$chapter_order_info user is :');
                Log::info($chapter_order_info);
                if($chapter_order_info){
                    foreach ($chapter_order_info as $chapter_order_item){
                        $data = [
                            'distribution_channel_id'=>4815,
                            'bid'=>$chapter_order_item->bid,
                            'cid'=>$chapter_order_item->cid,
                            'chapter_name'=>$chapter_order_item->chapter_name,
                            'book_name'=>$chapter_order_item->book_name,
                            'uid'=>$v->id,
                            'send_order_id'=>0,
                            'fee'=>0,
                            'created_at'=>$chapter_order_item->created_at,
                            'updated_at'=>date('Y-m-d H:i:s'),
                            'charge_balance'=>0,
                            'reward_balance'=>0
                        ];
                        $chapter_model->setCurrentTable($v->id);
                        $old = $chapter_model->where('uid',$v->id)
                            ->where('bid',$chapter_order_item->bid)
                            ->where('cid',$chapter_order_item->cid)
                            ->select('id')->first();
                        if(!$old){
                            $chapter_model->insert($data);
                        }

                    }
                }
                /*if($data){
                    Log::info('$chapter_order_info user $data is :');
                    Log::info($data);
                    $chapter_model->setCurrentTable($v->id);
                    $chapter_model->insert($data);
                }*/

                //单本订购
                if($froce_user && $froce_user->uid != $v->id){
                    $book_order_data = [];
                    $book_orders = DB::table('book_orders')->where('uid',$froce_user->uid)->get();
                    Log::info('$book_orders user  is :');
                    if($book_orders){
                        foreach ($book_orders as $book_order_item){
                            $book_order_data[] = [
                                'uid'=>$v->id,
                                'distribution_channel_id'=>4815,
                                'bid'=>$book_order_item->bid,
                                'book_name'=>$book_order_item->book_name,
                                'u'=>$book_order_item->u,
                                'send_order_id'=>0,
                                'charge_balance'=>0,
                                'reward_balance'=>0,
                                'fee'=>0,
                                'created_at'=>$book_order_item->created_at,
                                'updated_at'=>date('Y-m-d H:i:s'),
                            ];
                        }
                    }
                    if($book_order_data){
                        Log::info('$book_order_data user  is :');
                        Log::info($book_order_data);
                        foreach ($book_order_data as $book_o ){
                            $old = DB::table('book_orders')
                                ->where('uid',$book_o['uid'])
                                ->where('bid',$book_o['bid'])
                                ->select('id')->first();
                            if(!$old){
                                DB::table('book_orders')->insert($book_order_data);
                            }
                        }
                    }
                }


                //阅读记录
                $read_bids = Redis::hgetall('book_read:' . $froce_user->uid);

                Log::info('$read_bids   is :');
                Log::info($read_bids);

                if ($read_bids){
                    foreach ($read_bids as  $key=>$field){
                        Redis::hset('book_read:' . $v->id,$key,$field);
                    }
                }

                //用户信息
                $old_user = DB::table('users')->where('id', $froce_user->uid)->first();
                if($old_user){
                    DB::table('users')->where('id',$v->id)->update([
                        'balance'=>$old_user->balance,
                        'charge_balance'=>$old_user->charge_balance,
                        'reward_balance'=>$old_user->reward_balance,
                    ]);
                }

                //年费
                $year = DB::table('year_orders')->where('uid',$froce_user->uid)->orderBy('id','desc')->first();
                if($year){
                    $old_year = DB::table('year_orders')->where('uid',$v->id)->orderBy('id','desc')->first();
                    if(!$old_year){
                        DB::table('year_orders')->insert([[
                            'uid'=>$v->id,
                            'begin_time'=>$year->begin_time,
                            'end_time'=>$year->end_time,
                            'distribution_channel_id'=>4815,
                            'created_at'=>$year->created_at,
                            'updated_at'=>date('Y-m-d H:i:s'),
                            'fee'=>0,
                        ]]);
                    }
                }
            }

        }
    }

    private function clearUserReadRecord(){
        $i = 137406313;
        //$not_uid_key = ['last_read','send_order_id','sign_count','sign_counts','sign_info','sign_day','smart_push','inner_send_order_id','gxhp','property','bind_phone_status','ua'];
        $k = 0;
        while ( $i<108655782){
            $i++;
            $last_read = ReadRecordService::getByField($i,'last_read');
            if(!$last_read){
                $k++;
                Redis::del('book_read:'.$i,'userRecommendBids:'.$i);
                continue;
            }
            $last_info = explode('_',$last_read);
            if(!isset($last_info[2])){
                $k++;
                Redis::del('book_read:'.$i,'userRecommendBids:'.$i);
            }
            if( (time()-$last_info[2]) >=2*30*86400 ){
                $record = Redis::hgetall('book_read:'.$i);
                $data = [];

                foreach ($record as $ks=>$item){
                    $data[] = ['uid'=>$i,'field'=>$ks,'value'=>$item,
                        'created_at'=>date('Y-m-d H:i:s'),'updated_at'=>date('Y-m-d H:i:s')];
                }
                $k++;
                Redis::del('book_read:'.$i,'userRecommendBids:'.$i);
                DB::table('read_record_from_redis')->insert($data);
            }
        }
        echo 'delete ' . $k . ' keys' . PHP_EOL;
    }

    private function deleteToLongReadRecord(){
        $i = 10000;
        ReadRecordService::delTheLastRecord(109861757);
        while ($i <= 35600000){
            ReadRecordService::delTheLastRecord($i);
            $i++;
        }
    }

}