zhuishuyun_quickapp/app/Modules/User/Services/UserSubscribeBehaviorStatsService.php

<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/11/21
 * Time: 10:42
 */

namespace App\Modules\User\Services;

use App\Modules\User\Models\UserSubscribeBehaviorStats;
use DB;

class UserSubscribeBehaviorStatsService
{
    public static function getRechargeSum($start_time,$end_time,$is_subscribed='') {
        $res = DB::table('orders')
            ->join('force_subscribe_users','orders.uid','=','force_subscribe_users.uid')
            ->where([
                ['orders.status','=','PAID'],
                ['force_subscribe_users.created_at','>=',$start_time],
                ['force_subscribe_users.created_at','<=',$end_time]
            ]);
        if(!empty($is_subscribed)){
            $res->where('force_subscribe_users.is_subscribed',$is_subscribed);
        }
        $res = $res->select(DB::raw("force_subscribe_users.appid,sum(orders.price) as fee_sum"))
            ->groupBy('force_subscribe_users.appid')
            ->get();
        $ret = array();
        \Log::info($res);
        foreach ($res as $item) {
            $ret[$item->appid] = $item->fee_sum;
        }
        return $ret;
    }

    public static function getRechargeSumByAppid($start_time,$end_time,$appid,$is_subscribed='') {
        $res = DB::table('orders')
            ->join('force_subscribe_users','orders.uid','=','force_subscribe_users.uid')
            ->where([
                ['orders.status','=','PAID'],
                ['force_subscribe_users.appid','=',$appid],
                ['force_subscribe_users.created_at','>=',$start_time],
                ['force_subscribe_users.created_at','<=',$end_time]
            ]);
        if(!empty($is_subscribed)){
            $res->where('force_subscribe_users.is_subscribed',$is_subscribed);
        }
        $ret = $res->sum('orders.price');
        return $ret;
    }

    public static function getUserSubNum($start_time,$end_time,$is_subscribed=''){
        $res = DB::table('force_subscribe_users')
            ->whereBetween('created_at',[$start_time,$end_time]);
        if(!empty($is_subscribed)){
            $res->where('is_subscribed',$is_subscribed);
        }
        $res = $res->groupBy('appid')
            ->select(DB::raw("appid,count(id) as user_sub_count"))
            ->get();
        $ret = array();
        foreach ($res as $item) {
            $ret[$item->appid] = $item->user_sub_count;
        }
        return $ret;
    }

    public static function getUserSubNumByAppid($start_time,$end_time,$appid,$is_subscribed=''){
        $res = DB::table('force_subscribe_users')
            ->whereBetween('created_at',[$start_time,$end_time])
            ->where('appid',$appid);
        if(!empty($is_subscribed)){
            $res->where('is_subscribed',$is_subscribed);
        }
        $ret = $res->count('id');
        //$ret = array();
        /*foreach ($res as $item) {
            $ret[$item->appid] = $item->fee_sum;
        }*/
        return $ret;
    }

    public static function getNewSubRechargeUserNum($start_time,$end_time) {
        $res = DB::table('force_subscribe_users')
            ->whereBetween('created_at',[$start_time,$end_time])
            ->groupBy('appid')
            ->select(DB::raw('force_subscribe_users.appid,count(force_subscribe_users.id) as new_sub_recharge_num'))
            ->whereExists(function($query){
                $query->select(DB::raw(1))
                    ->from('orders')
                    ->where('orders.status','PAID')
                    ->whereRaw('orders.uid = force_subscribe_users.uid');
            })
            ->get();
        $ret = array();
        foreach ($res as $item) {
            $ret[$item->appid] = $item->new_sub_recharge_num;
        }
        return $ret;
    }

    public static function getNewSubRechargeUserNumByAppid($start_time,$end_time,$appid) {
        $res = DB::table('force_subscribe_users')
            ->whereBetween('created_at',[$start_time,$end_time])
            ->where('appid',$appid)
            //->select(DB::raw('force_subscribe_users.appid,count(force_subscribe_users.id) as new_sub_recharge_num'))
            ->whereExists(function($query){
                $query->select(DB::raw(1))
                    ->from('orders')
                    ->where('orders.status','PAID')
                    ->whereRaw('orders.uid = force_subscribe_users.uid');
            })
            ->count('force_subscribe_users.id');
        return $res;
    }

    public static function getSubUserOrdersNum($start_time,$end_time) {
        $res = DB::table('orders')
            ->join('force_subscribe_users','force_subscribe_users.uid','=','orders.uid')
            ->whereBetween('orders.created_at',[$start_time,$end_time])
            ->where('orders.status','PAID')
            ->groupBy('force_subscribe_users.appid')
            ->select(DB::raw('force_subscribe_users.appid,count(orders.id) as sub_user_orders_num'))
            ->get();
        $ret = array();
        foreach ($res as $item) {
            $ret[$item->appid] = $item->sub_user_orders_num;
        }
        return $ret;
    }

    public static function generateData()
    {
        for($i=1;$i<=60;$i++) {
            $date = date('Y-m-d',strtotime(' -'.$i.' day'));
            $start_time = $date.' 00:00:00';
            $end_time = $date.' 23:59:59';
            $param = array();

            $recharge_sum = self::getRechargeSum($start_time,$end_time);
            foreach ($recharge_sum as $key => $value) {
                $param[$key]['recharge_sum'] = $value;
                $param[$key]['date'] = $date;
            }
            if($i==1){
                $new_subscribe_num = self::getUserSubNum($start_time,$end_time);
                foreach($new_subscribe_num as $k=>$v) {
                    $param[$k]['new_subscribe_num'] = $v;
                }
                $one_days_recharge = self::getRechargeSum($start_time,$end_time);
                foreach ($one_days_recharge as $k1=>$v1) {
                    $param[$k1]['one_day_recharge'] =$v1;
                }
                $actual_subscribe_num = self::getUserSubNum($start_time,$end_time,1);
                foreach ($actual_subscribe_num as $k2=>$v2) {
                    $param[$k2]['actual_subscribe_num'] = $v2;
                }
                $sub_user_orders_num = self::getSubUserOrdersNum($start_time,$end_time);
                foreach ($sub_user_orders_num as $k3=>$v3) {
                    $param[$k3]['sub_user_orders_num'] = $v3;
                }
            }
            if($i<=3) {
                $three_days_recharge = self::getRechargeSum($start_time,$end_time);
                foreach ($three_days_recharge as $k3=>$v3) {
                    $param[$k3]['three_days_recharge'] = $v3;
                }
            }
            if($i<=7) {
                $seven_days_recharge = self::getRechargeSum($start_time,$end_time);
                foreach ($seven_days_recharge as $k4=>$v4) {
                    $param[$k4]['seven_days_recharge'] = $v4;
                }
            }
            if($i<=30) {
                $thirty_days_recharge = self::getRechargeSum($start_time,$end_time);
                foreach ($thirty_days_recharge as $k5=>$v5) {
                    $param[$k5]['thirty_days_recharge'] = $v5;
                }
            }
            if($i<=60) {
                $sixty_days_recharge = self::getRechargeSum($start_time,$end_time);
                foreach ($sixty_days_recharge as $k6=>$v6) {
                    $param[$k6]['sixty_days_recharge'] = $v6;
                }
            }
            foreach ($param as $key=>$value) {
                $sign = array('appid'=>$key,'date'=>$date);
                $data = $value;
                UserSubscribeBehaviorStats::updateOrCreate($sign,$data);
            }
        }
        UserSubscribeBehaviorStats::chunk(1000,function($res) {
            foreach ($res as $item) {
                $start_time = ($item->date).' 00:00:00';
                $end_time = ($item->date).' 23:59:59';
                $actual_sub_num = self::getUserSubNumByAppid($start_time,$end_time,$item->appid,1);
                $recharge_sum = self::getRechargeSumByAppid($start_time,$end_time,$item->appid);
                $new_sub_recharge_num = self::getNewSubRechargeUserNumByAppid($start_time,$end_time,$item->appid);
                $item->actual_subscribe_num = $actual_sub_num;
                $item->recharge_sum = $recharge_sum;
                $item->new_sub_recharge_num = $new_sub_recharge_num;
                $item->save();
            }
        });
    }

    public static function getChannelStats($channel_id,$param=[]) {
        $filter = [
            ['official_accounts.distribution_channel_id','=',$channel_id],

        ];
        if($param) {
            if(isset($param['start_date']) && $param['start_date']) {
                $filter[] = ['user_subscribe_statistics.date','>=',$param['start_date']];
            }
            if(isset($param['end_date']) && $param['end_date']) {
                $filter[] = ['user_subscribe_statistics.date','<=',$param['end_date']];
            }
            if(isset($param['appid']) && $param['appid']) {
                $filter[] = ['user_subscribe_statistics.appid','=',$param['appid']];
            }
        }
        return UserSubscribeBehaviorStats::join('official_accounts','official_accounts.appid','=','user_subscribe_statistics.appid')
            ->select(['official_accounts.nickname','user_subscribe_statistics.id','user_subscribe_statistics.date','user_subscribe_statistics.new_subscribe_num',
            'user_subscribe_statistics.recharge_sum','user_subscribe_statistics.one_day_recharge','user_subscribe_statistics.three_days_recharge',
            'user_subscribe_statistics.actual_subscribe_num','user_subscribe_statistics.seven_days_recharge','user_subscribe_statistics.thirty_days_recharge',
            'user_subscribe_statistics.sixty_days_recharge','user_subscribe_statistics.new_sub_recharge_num','user_subscribe_statistics.sub_user_orders_num'])
            ->where($filter)
            ->orderBy('user_subscribe_statistics.date','desc')
            ->paginate();
    }

    public static function getChannelServiceAccount($channel_id) {
        $res = DB::table('official_accounts')
            ->select('nickname','appid')
            ->where('distribution_channel_id',$channel_id)
            ->get();
        return $res;
    }
}