<?php

namespace App\Console\Commands\SuperiorBooks;

use App\Modules\Book\Services\SuperiorHistoryBookService;
use App\Modules\Channel\Models\CompanySpecialBook;
use DB;
use Illuminate\Console\Command;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '临时任务';

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

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //$array = ['recent_read','subscribe','text_sign','text_search','readerover','readrecord','daily_sign'];//

//        $array = ['daily_sign'];

        //foreach ($array as $v) {
        $books = DB::table('superior_history_books')->where('status',1)->get();
        foreach ($books as $book){
            DB::table('chapter')->where()->first();
        }
        return true;
        $v = $this->argument('scene');
             DB::connection('chapter_order_mysql')
                ->table($v.'_user_sub')
                 ->orderBy('id')
                ->where('fee', -1)->chunk(10000, function ($users) use($v){
                    foreach ($users as $user) {
                        $fee = DB::connection('chapter_order_mysql')->table('chapter_orders' . (($user->uid) % 512))
                            ->where('uid', $user->uid)
                            ->where('created_at', '<=', '2018-12-07 23:59:59')
                            ->sum('fee');
                        DB::connection('chapter_order_mysql')
                            ->table($v.'_user_sub')
                            ->where('uid',$user->uid)
                            ->update(['fee'=>$fee]);
                    }
                });
        //}
        return;
        //
       /* $companies = DB::table('companies')->where('is_important',1)->get();
        $internal_books = DB::table('book_configs')->where('is_on_shelf',1)->get();
        foreach ($companies as $company) {
            foreach ($internal_books as $internal_book){
                $data = ['bid'=>$internal_book->bid,'company_id'=>$company->id];
                CompanySpecialBook::updateOrCreate($data);
            }
        }*/
       /*for($i=0;$i<512;$i++){

           $count = DB::connection('chapter_order_mysql')->select("SELECT co.uid FROM ".'chapter_orders'."$i as co WHERE co.created_at>='2018-11-15 00:00:00' AND EXISTS(SELECT 1 FROM user_pay WHERE user_pay.uid=co.uid)");
           $chapter_order = DB::connection('chapter_order_mysql')
               ->select("SELECT co2.uid as Uid,sum(co2.fee) AS Fee FROM ".'chapter_orders'.$i." AS co2 WHERE co2.uid IN(SELECT co.uid FROM ".'chapter_orders'."$i as co WHERE co.created_at>='2018-11-15 00:00:00' AND EXISTS(SELECT 1 FROM user_pay WHERE user_pay.uid=co.uid)) GROUP BY co2.uid");
       }*/
       /* $res = DB::select("SELECT yq_move_channels.distribution_channel_id AS distribution_channelId,bills.recharge_amount as rechargeAmount,settlement_price AS settlementPrice,bills.recharge_amount-bills.settlement_price AS diff,bills.id as bill_id FROM bills,yq_move_channels WHERE bills.rate=0.9 AND bills.distribution_channel_id = yq_move_channels.distribution_channel_id and bills.date >= date(yq_move_channels.created_at) and yq_move_channels.created_at <= '2018-11-05'");
        foreach ($res as $item) {
            //DB::table('financial_stats')->where('distribution_channel_id',$item->distribution_channelId)->update()
            DB::select("UPDATE financial_stats SET financial_stats.enable_withdrawal_amount=financial_stats.enable_withdrawal_amount+".($item->diff)." WHERE financial_stats.distribution_channel_id=".$item->distribution_channelId);
            DB::select("UPDATE bills SET bills.settlement_price=bills.recharge_amount,bills.rate=1 WHERE bills.id=".($item->bill_id));
            \Log::info('changed:channel_id:'.($item->distribution_channelId).'diff:'.($item->diff).'bill_id:'.($item->bill_id));
        }*/
       \Log::info("user subscribe data task start-----time:".date('Y-m-d H:i:s'));
       print_r("user subscribe data task start-----time:".date('Y-m-d H:i:s'));
        $count =1;
       DB::table('force_subscribe_users')
           ->join('orders','orders.uid','=','force_subscribe_users.uid')
           ->select(DB::raw('distinct force_subscribe_users.uid'))
           ->where('orders.status','PAID')
           ->where('orders.created_at','>=','2018-11-01')
           ->whereBetween('subscribe_time',['2018-11-01','2018-11-25 23:59:59'])
           ->orderBy('uid')
           ->chunk(10000,function($res) use(&$count){
               $data = [];
               foreach ($res as $item){
                   $books_order = DB::table('book_orders')
                       ->where('uid',$item->uid)
                       ->select(['uid','bid','created_at','fee as sum_fee','book_name'])
                       ->whereBetween('created_at',['2018-11-01','2018-12-03 23:59:59'])
                       ->get();
                   $books_order = json_decode(json_encode($books_order),true);
                   $chapter_orders = DB::connection('chapter_order_mysql')
                       ->table('chapter_orders'.(($item->uid)%512))
                       ->select(DB::raw('uid,bid,min(created_at) as created_at,book_name,sum(fee) as sum_fee'))
                       ->groupBy('bid')
                       ->where('uid',$item->uid)
                       ->get();
                   $chapter_orders = json_decode(json_encode($chapter_orders),true);
                   $temps = array_merge($books_order,$chapter_orders);
                   foreach ($temps as $temp){
                       $data[]=array($temp['uid'],$temp['bid'],$temp['book_name'],$temp['sum_fee'],$temp['created_at']);
                   }
               }
               $header = ['uid','bid','book_name','订阅书币','订阅时间'];
               saveExcelData($header,$data,storage_path('app/subscribe_data'.$count.'.xlsx'));
               print_r("done jobs count:".$count);
               $count++;
           });
       print_r("user subscribe data task end-----time:".date('Y-m-d H:i:s'));
       \Log::info("user subscribe data task end-----time:".date('Y-m-d H:i:s'));
    }
}
