<?php
/**
 * 派单数据统计
 */

namespace App\Modules\SendOrder\Services;

use App\Modules\SendOrder\Models\SendOrderStat;
use DB;
use Redis;

class SendOrderStatisticsService
{

    public static function get_query_date(){
        $today = date('Y-m-d H:i:s');
//      $today = '2018-11-21 12:00:00';
        $today_time = strtotime($today);
        $week_date = self::getWeek($today_time);
        \Log::info('$week_date');
        \Log::info($week_date);
        $today = date('Y-m-d');
        $start_date = $end_date = '';
        // 算最后end_date计算时间
        // 周六~周五 小周期
        // 周六~下二个周一 大周期
        // 周一算上周的
        if($week_date['week_num'] ==1){
                $reduce_big_end_day = -7;
        }
        // 周日
        elseif($week_date['week_num'] ==0){
                $reduce_big_end_day = -6;
        }else{
                $reduce_big_end_day = 1-$week_date['week_num'];
        }
        $reduce_big_start_day = $reduce_big_end_day - 9;
        $reduce_small_start_day = $reduce_big_start_day;
        $reduce_small_end_day = $reduce_big_end_day - 3;
        \Log::info('$reduce_big_start_day:'.$reduce_big_start_day.' $reduce_big_end_day:'.$reduce_big_end_day.' $reduce_small_start_day:'.$reduce_small_start_day.' $reduce_small_end_day:'.$reduce_small_end_day);
         
        $small_start_day = date('Y-m-d',$today_time + 3600*24*$reduce_small_start_day);
        $small_end_day = date('Y-m-d',$today_time + 3600*24*$reduce_small_end_day);
        $big_start_day = date('Y-m-d',$today_time + 3600*24*$reduce_big_start_day);
        $big_end_day = date('Y-m-d',$today_time +3600*24*$reduce_big_end_day);
    
        $data = [
           'start_day'=>$small_start_day,
           'end_day'=>$small_end_day,
           'small_start_day'=>$small_start_day.' 00:00:00',
           'small_end_day'=>$small_end_day.' 23:59:59',
           'big_start_day'=>$big_start_day.' 00:00:00',
           'big_end_day'=>$big_end_day.' 23:59:59',
        ];
    
        \Log::info('get_query_date_end');
        \Log::info($data);
        return $data;
    }
    
    //php获取今天是星期几
    public static function getWeek($unixTime=''){
        $unixTime=is_numeric($unixTime)?$unixTime:time();
        $weekarray=array('日','一','二','三','四','五','六');
        $week_num = date('w',$unixTime);
        $data = [
          'week_num'=>$week_num,
          'week_name'=>'星期'.$weekarray[$week_num]
        ];
        return $data;
    }
    
    //充值总额：周六~周五该账号的充值总额
    public static function get_total_charge_amount($distribution_channel_id,$start_date,$end_date){
        $data =  DB::table('orders')
                        ->select(DB::raw('sum(price) as total_charge_amount '))
                        ->where('created_at','>=',$start_date)
                        ->where('created_at','<',$end_date)
                        ->where('status','PAID')
                        ->where('distribution_channel_id',$distribution_channel_id)
                        ->first();
        return isset($data->total_charge_amount) && $data->total_charge_amount ? $data->total_charge_amount:'0';
    }
    
    //充值人数：周六~周五该账号的充值人数
    public static function get_charge_user_num($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('orders')
        ->select(DB::raw('count(distinct uid) as user_num'))
        ->where('created_at','>=',$start_date)
        ->where('created_at','<',$end_date)
        ->where('status','PAID')
        ->where('distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->user_num) && $data->user_num ? $data->user_num:'0';
    }
    //成功订单数：周六~周五该账号的成功订单数
    public static function get_suc_order_num($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('orders')
        ->select(DB::raw('count(1) as order_num'))
        ->where('created_at','>=',$start_date)
        ->where('created_at','<',$end_date)
        ->where('status','PAID')
        ->where('distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->order_num) && $data->order_num ? $data->order_num:'0';
    }
    //实际消耗成本：周六~周五该账号实际派单对应的成本
    public static function get_real_cost_amount($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('send_orders')
        ->select(DB::raw('sum(cost) as real_cost_amount'))
        ->where('send_time','>=',$start_date)
        ->where('send_time','<',$end_date)
        ->where('is_enable','1')
        ->where('distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->real_cost_amount) && $data->real_cost_amount ? $data->real_cost_amount:'0';
    }
    //实际派单数：周六~周五该账号在派单表中派单ID计数
    public static function get_real_send_order_num($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('send_orders')
        ->select(DB::raw('count(1) as send_order_num'))
        ->where('send_time','>=',$start_date)
        ->where('send_time','<',$end_date)
        ->where('is_enable','1')
        ->where('distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->send_order_num) && $data->send_order_num ? $data->send_order_num:'0';
    }
    //强关率：周六~周五该账号派单的 总强关数/总注册数
    public static function get_force_subscribe_rate($new_subscribe_user_num,$register_user_num){
        if($register_user_num){
                return round($new_subscribe_user_num*100/$register_user_num,2);
        }else{
                return 0;
        }
    }
    //内推数量（强关人数不满20人算内推）：周六~周五该账号的内推数量
    public static function get_inner_send_order_num($distribution_channel_id,$start_date,$end_date){
        $inner_send_orders = DB::table('send_orders')
        ->select('send_orders.id',
                        DB::raw('(select count(1)  from force_subscribe_users where send_order_id=send_orders.id and is_subscribed=1 and distribution_channel_id=send_orders.distribution_channel_id) as subscribe_num'))
        ->where('send_time','>=',$start_date)
        ->where('send_time','<',$end_date)
        ->where('is_enable','1')
        ->where('distribution_channel_id',$distribution_channel_id)
        ->groupBy('send_orders.id')
        ->having('subscribe_num','<','20')
        ->get();
        \Log::info('$inner_send_orders');\Log::info($inner_send_orders);
        return count($inner_send_orders);
    }
    
    //内推数量(通过派单类型)：周六~周五该账号的内推数量
    public static function get_inner_send_order_num_by_order_type($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('send_orders')
        ->select(DB::raw('count(1) as send_order_num'))
        ->where('send_time','>=',$start_date)
        ->where('send_time','<',$end_date)
        ->where('promotion_type','INTERNAL')
        ->where('is_enable','1')
        ->where('distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->send_order_num) && $data->send_order_num ? $data->send_order_num:'0';
    }
    //新关用户数：周六~周五该账号的新关人数
    public static function get_new_subscribe_user_num($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('send_orders')
        ->join('force_subscribe_users','force_subscribe_users.send_order_id','send_orders.id')
        ->select(DB::raw('count(1) as new_subscribe_user_num'))
        ->where('send_orders.send_time','>=',$start_date)
        ->where('send_orders.send_time','<',$end_date)
        ->where('send_orders.is_enable','1')
        ->where('send_orders.distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->new_subscribe_user_num) && $data->new_subscribe_user_num ? $data->new_subscribe_user_num:'0';
    }
    
    //新关用户充值人数：周六~周五该账号的新关粉中，在周六~周一的充值人数
    // 以uid作为基准，一个用户的send_order_id会被覆盖
    public static function get_new_subscribe_charge_user_num($distribution_channel_id,$start_date,$end_date,$big_start_date,$big_end_date){
        $data = DB::table('send_orders')
        ->join('force_subscribe_users','force_subscribe_users.send_order_id','send_orders.id')
        ->join('orders','orders.uid','force_subscribe_users.uid')
        ->select(DB::raw('count(distinct orders.uid) as new_subscribe_charge_user_num'))
        ->where('orders.created_at','>=',$big_start_date)
        ->where('orders.created_at','<',$big_end_date)
        ->where('orders.status','PAID')
        ->where('send_orders.send_time','>=',$start_date)
        ->where('send_orders.send_time','<',$end_date)
        ->where('send_orders.is_enable','1')
        ->where('send_orders.distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->new_subscribe_charge_user_num) && $data->new_subscribe_charge_user_num ? $data->new_subscribe_charge_user_num:'0';
         
    }
    
    //新关用户充值金额：周六~周五该账号的新关粉中，在周六~周一的充值金额
    public static function get_new_subscribe_charge_amount($distribution_channel_id,$start_date,$end_date,$big_start_date,$big_end_date){
        $data = DB::table('send_orders')
        ->join('force_subscribe_users','force_subscribe_users.send_order_id','send_orders.id')
        ->join('orders','orders.uid','force_subscribe_users.uid')
        ->select(DB::raw('ifnull(sum(price),0) as new_subscribe_charge_amount'))
        ->where('orders.created_at','>=',$big_start_date)
        ->where('orders.created_at','<',$big_end_date)
        ->where('orders.status','PAID')
        ->where('send_orders.send_time','>=',$start_date)
        ->where('send_orders.send_time','<',$end_date)
        ->where('send_orders.is_enable','1')
        ->where('send_orders.distribution_channel_id',$distribution_channel_id)
        ->first();

        return isset($data->new_subscribe_charge_amount) && $data->new_subscribe_charge_amount ? $data->new_subscribe_charge_amount:'0';
    
    }
    //新关成本：实际消耗成本/新关用户数
    public static function get_new_subscribe_cost($real_cost_amount,$new_subscribe_user_num){
        if($new_subscribe_user_num){
                return round($real_cost_amount/$new_subscribe_user_num,2);
        }else{
                return 0;
        }
    }
    //新关单粉3天产出：分母：周六~周五的新关数；分子：这批粉周六~周一的充值总额；
    public static function get_new_subscribe_3day_out($new_subscribe_charge_amount,$new_subscribe_user_num){
        if($new_subscribe_user_num){
                return round($new_subscribe_charge_amount/$new_subscribe_user_num,2);
        }else{
                return 0;
        }
    }
    //新用户复充率：分母：周六~周五新关粉，在周六~周五的付费用户数；分子：这批付费用户在订单表属于复充的人数； 
    public static function get_new_subscribe_re_filling_rate($small_re_charge_user_num,$new_subscribe_charge_user_num){
        if($new_subscribe_charge_user_num){
                return round($small_re_charge_user_num*100/$new_subscribe_charge_user_num,2);
        }else{
                return 0;
        }
    }
    
    //新用户充值充值率（新关用户充值人数/新关用户数量）
    public static function get_new_subscribe_charge_rate($new_subscribe_charge_user_num,$new_subscribe_user_num){
        if($new_subscribe_user_num){
                return round($new_subscribe_charge_user_num*100/$new_subscribe_user_num,2);
        }else{
                return 0;
        }
    }

    //新注册用户数：周六~周五该账号的新注册人数,暂时注册时间不考虑
    public static function get_new_register_user_num($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('send_orders')
        ->join('users','users.send_order_id','send_orders.id')
        ->select(DB::raw('count(1) as new_register_user_num'))
        ->where('send_orders.send_time','>=',$start_date)
        ->where('send_orders.send_time','<',$end_date)
        ->where('send_orders.is_enable','1')
        ->where('send_orders.distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->new_register_user_num) && $data->new_register_user_num ? $data->new_register_user_num:'0';
    }
    
    //周六~周五新关粉，在周六~周五的付费用户数(算复充率专用，限制强关时间，其他不限制)
    public static function get_new_subscribe_small_charge_user_num($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('send_orders')
        ->join('force_subscribe_users','force_subscribe_users.send_order_id','send_orders.id')
        ->join('orders','force_subscribe_users.uid','orders.uid')
        ->select(DB::raw('count(distinct orders.uid) as new_subscribe_small_charge_user_num'))
        ->where('send_orders.send_time','>=',$start_date)
        ->where('send_orders.send_time','<',$end_date)
        ->where('send_orders.is_enable','1')
        ->where('orders.created_at','>=',$start_date)
        ->where('orders.created_at','<',$end_date)
        ->where('force_subscribe_users.created_at','>=',$start_date)
        ->where('force_subscribe_users.created_at','<',$end_date)
        ->where('orders.status','PAID')
        ->where('send_orders.distribution_channel_id',$distribution_channel_id)
        ->first();
        return isset($data->new_subscribe_small_charge_user_num) && $data->new_subscribe_small_charge_user_num ? $data->new_subscribe_small_charge_user_num:'0';
    
    }

    //周六~周五新关粉，在周六~周五的付费用户数(算复充率专用，限制强关时间，其他不限制)
    public static function get_new_subscribe_small_re_charge_user_num($distribution_channel_id,$start_date,$end_date,$big_start_date,$big_end_date){
        $data = DB::table('send_orders')
        ->join('force_subscribe_users','force_subscribe_users.send_order_id','send_orders.id')
        ->join('orders','force_subscribe_users.uid','orders.uid')
        ->select(DB::raw('count(distinct orders.id) as new_subscribe_small_re_charge_user_order_num'),'orders.uid')
        ->where('send_orders.send_time','>=',$start_date)
        ->where('send_orders.send_time','<',$end_date)
        ->where('send_orders.is_enable','1')
        ->where('orders.created_at','>=',$big_start_date)
        ->where('orders.created_at','<',$big_end_date)
//      ->where('force_subscribe_users.created_at','>=',$start_date)
//      ->where('force_subscribe_users.created_at','<',$end_date)
        ->where('orders.status','PAID')
        ->where('send_orders.distribution_channel_id',$distribution_channel_id)
        ->groupBy('orders.uid')
        ->having('new_subscribe_small_re_charge_user_order_num','>',1)
        ->get();
        return count($data);
    
    }
    
    //top3的书籍名称，派单量：对应书籍周六~周五的实际派单数
    public static function get_top_send_order_num_books($distribution_channel_id,$start_date,$end_date){
        $data = DB::table('send_orders')
        ->select(DB::raw('count(1) as send_order_num'),'book_id','book_name')
        ->where('send_time','>=',$start_date)
        ->where('send_time','<',$end_date)
        ->where('is_enable','1')
        ->where('distribution_channel_id',$distribution_channel_id)
        ->groupBy('book_id')
        ->orderBy('send_order_num','desc')
        ->limit(3)
        ->get();
        if(!empty($data)){
        	return $data->toArray();
        }
        return $data;
    }
    
    //top3的书籍名称，要求派单带来的注册用户，充值注册比：周六~周一该书的 充值总额/注册数
    public static function get_top_charge_register_books($distribution_channel_id,$start_date,$end_date){
        $charge_register_books = DB::table('send_orders')
        ->select(
                        DB::raw('ifnull((select sum(orders.price) from orders inner join users ON users.send_order_id=orders.send_order_id and users.id=orders.uid where orders.send_order_id=send_orders.id and status="PAID"),0) as recharge_amount'),
                        DB::raw('ifnull((select count(1) from users where users.send_order_id=send_orders.id),1) as register_num'),
                        'book_id','book_name')
        ->where('send_orders.send_time','>=',$start_date)
        ->where('send_orders.send_time','<',$end_date)
        ->where('send_orders.is_enable','1')
        ->where('send_orders.distribution_channel_id',$distribution_channel_id)
        ->groupBy('book_id')
        ->having('register_num','>','100')
        ->orderBy(DB::raw('(recharge_amount/register_num)'),'desc')
        ->limit(3)
        ->get();
    
        if(!empty($charge_register_books)){
                foreach($charge_register_books as $key=>$charge_register_book){
                    $charge_register_books[$key]->charge_register_number = (int)$charge_register_book->recharge_amount.'/'.$charge_register_book->register_num;
                    if($charge_register_book->register_num > 0){
                    	$charge_register_books[$key]->charge_register = round($charge_register_book->recharge_amount/$charge_register_book->register_num,2);
                    }else{
                    	$charge_register_books[$key]->charge_register = 0;
                    }
                }
        }
        if(!empty($charge_register_books)){
        	return $charge_register_books->toArray();
        }
        return $charge_register_books;
    }
    
    /**
     * 渠道周报表数据
     * @param Request $request
     */
    public static function getWeeklySendOrderStatistics($distribution_channel_id)
    {
    	if(empty($distribution_channel_id)){
    		return response()->error('PARAM_EMPTY');
    	}
    	\Log::info('getWeeklySendOrderStatistics_start:'.$distribution_channel_id);
    	$data = [];
    	try{
    		$date = SendOrderStatisticsService::get_query_date();
    		$data['date'] = [
	    		'start_day'=>$date['start_day'],
	    		'end_day'=>$date['end_day'],
	    		'small_start_day'=>$date['small_start_day'],
	    		'small_end_day'=>$date['small_end_day'],
	    		'big_start_day'=>$date['big_start_day'],
	    		'big_end_day'=>$date['big_end_day'],
    		];
    		
    		// 从redis取
    		$weekly_report_key = 'weeklyReport:distribution_channel_id:'.$distribution_channel_id.':start_day:'.$date['start_day'].':end_day:'.$date['end_day'];
    		$redis_data = Redis::get($weekly_report_key);
    		if(!empty($redis_data)){
    			\Log::info('redis_has_weekly_report:'.$weekly_report_key);
    			$data = object_to_array(json_decode($redis_data));
    			\Log::info($data);
    			return $data;
    		}
    		
    		 
    		// 充值统计
    		\Log::info('get_total_charge_amount_start:'.$distribution_channel_id);
    		$total_charge_amount = SendOrderStatisticsService::get_total_charge_amount($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_charge_user_num_start:'.$distribution_channel_id);
    		$charge_user_num = SendOrderStatisticsService::get_charge_user_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_suc_order_num_start:'.$distribution_channel_id);
    		$suc_order_num = SendOrderStatisticsService::get_suc_order_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_real_cost_amount_start:'.$distribution_channel_id);
    		$real_cost_amount = SendOrderStatisticsService::get_real_cost_amount($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		 
    		$data['charge_statistics'] = [
	    		'total_charge_amount' => $total_charge_amount,
	    		'charge_user_num' => $charge_user_num,
	    		'suc_order_num' => $suc_order_num,
	    		'real_cost_amount' => $real_cost_amount,
    		];
    		 
    		// 派单统计
    		\Log::info('get_new_register_user_num_start:'.$distribution_channel_id);
    		$new_register_user_num = SendOrderStatisticsService::get_new_register_user_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_new_subscribe_user_num_start:'.$distribution_channel_id);
    		$new_subscribe_user_num = SendOrderStatisticsService::get_new_subscribe_user_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_force_subscribe_rate_start:'.$distribution_channel_id);
    		$force_subscribe_rate = SendOrderStatisticsService::get_force_subscribe_rate($new_subscribe_user_num,$new_register_user_num);
    		\Log::info('get_real_send_order_num_start:'.$distribution_channel_id);
    		$real_send_order_num = SendOrderStatisticsService::get_real_send_order_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_inner_send_order_num_start:'.$distribution_channel_id);
    		$inner_send_order_num = SendOrderStatisticsService::get_inner_send_order_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_new_subscribe_charge_user_num_start:'.$distribution_channel_id);
    		$new_subscribe_charge_user_num = SendOrderStatisticsService::get_new_subscribe_charge_user_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day'],$date['big_start_day'],$date['big_end_day']);
    		\Log::info('get_new_subscribe_charge_amount_start:'.$distribution_channel_id);
    		$new_subscribe_charge_amount = SendOrderStatisticsService::get_new_subscribe_charge_amount($distribution_channel_id,$date['small_start_day'],$date['small_end_day'],$date['big_start_day'],$date['big_end_day']);
    		\Log::info('get_new_subscribe_cost_start:'.$distribution_channel_id);
    		$new_subscribe_cost = SendOrderStatisticsService::get_new_subscribe_cost($real_cost_amount,$new_subscribe_user_num);
    		\Log::info('get_new_subscribe_3day_out_start:'.$distribution_channel_id);
    		$new_subscribe_3day_out = SendOrderStatisticsService::get_new_subscribe_3day_out($new_subscribe_charge_amount,$new_subscribe_user_num);
    		//\Log::info('get_new_subscribe_small_charge_user_num_start:'.$distribution_channel_id);
    		//$new_subscribe_small_charge_user_num = SendOrderStatisticsService::get_new_subscribe_small_charge_user_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_new_subscribe_small_re_charge_user_num_start:'.$distribution_channel_id);
    		$small_re_charge_user_num = SendOrderStatisticsService::get_new_subscribe_small_re_charge_user_num($distribution_channel_id,$date['small_start_day'],$date['small_end_day'],$date['big_start_day'],$date['big_end_day']);
    		\Log::info('get_new_subscribe_re_filling_rate_start:'.$distribution_channel_id);
    		$new_subscribe_re_filling_rate = SendOrderStatisticsService::get_new_subscribe_re_filling_rate($small_re_charge_user_num,$new_subscribe_charge_user_num);
    		\Log::info('get_new_subscribe_charge_rate_start:'.$distribution_channel_id);
    		$new_subscribe_charge_rate = SendOrderStatisticsService::get_new_subscribe_charge_rate($new_subscribe_charge_user_num,$new_subscribe_user_num);
    
    		$data['send_order_statistics'] = [
	    		'real_send_order_num' =>$real_send_order_num,
	    		'force_subscribe_rate' => $force_subscribe_rate.'%',
	    		'inner_send_order_num' => $inner_send_order_num,
	    		'new_subscribe_user_num' => $new_subscribe_user_num,
	    		'new_subscribe_charge_user_num' => $new_subscribe_charge_user_num,
	    		'new_subscribe_charge_amount' => $new_subscribe_charge_amount,
	    		'new_subscribe_cost' => $new_subscribe_cost,
	    		'new_subscribe_3day_out' => $new_subscribe_3day_out,
	    		'new_subscribe_re_charge_user_num' => $small_re_charge_user_num,// 新关用户复充人数
	    		'new_subscribe_re_filling_rate' => $new_subscribe_re_filling_rate.'%',
	    		'new_subscribe_charge_rate' => $new_subscribe_charge_rate.'%',//新用户充值充值率
	    		'charge_register_rate' => $charge_user_num.'/'.$new_register_user_num,//充值注册比
    		];
    		 
    		// 书籍统计
    		\Log::info('get_top_send_order_num_books_start:'.$distribution_channel_id);
    		$top_send_order_num_books = SendOrderStatisticsService::get_top_send_order_num_books($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		\Log::info('get_top_charge_register_books_start:'.$distribution_channel_id);
    		$top_charge_register_books = SendOrderStatisticsService::get_top_charge_register_books($distribution_channel_id,$date['small_start_day'],$date['small_end_day']);
    		 
    		$data['book_statistics'] = [
	    		'top_send_order_num_books' => object_to_array($top_send_order_num_books),
	    		'top_charge_register_books' => object_to_array($top_charge_register_books)
    		];
    	}catch(Exception $e){
    		\Log::info('getWeeklySendOrderStatistics_ept:'.$e->getMessage());
    	}
    
    	Redis::set($weekly_report_key,json_encode($data));
    	
    	\Log::info('getWeeklySendOrderStatistics_end:'.$distribution_channel_id);
    	\Log::info($data);
    	return $data;
    }
    
}