wangdu_public_api/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)
            ->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();
            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;
                }

                foreach ($param as $key => $value) {
                    $sign = array('appid' => $key, 'date' => $date);
                    $data = $value;
                    UserSubscribeBehaviorStats::updateOrCreate($sign, $data);
                }
            }
            self::generateChargeData();
            break;
        }
        //更新近两个月每个服务号每天的净关
        $temp = 0;
        while (true) {
            $result = UserSubscribeBehaviorStats::where('date', '>=', date('Y-m-d', strtotime('-2 month -1 day')))
                ->orderBy('id')->where('id', '>', $temp)->limit(1000)->get();
            if ($result->isEmpty()) break;
            foreach ($result as $item) {
                $temp = $item->id;
                $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);
                $item->actual_subscribe_num = $actual_sub_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;
    }

    public static function getList($date = '', $appid = '')
    {
        return UserSubscribeBehaviorStats::getList($date, $appid);
    }

    public static function generateChargeData()
    {
        $start_day = date('Y-m-d', time() - 86400);
        $end_day =  $start_day . ' 23:59:59';
        $sql = "SELECT DISTINCT distribution_channel_id  FROM orders WHERE created_at >= '%s' and created_at < '%s' AND `status` = 'PAID'";
        $distribution_channel_ids = DB::select(sprintf($sql, $start_day, $end_day));
        $datetime1 = date_create($start_day);
        $two_month = date('Y-m-d', strtotime('-2 month -1 day'));
        foreach ($distribution_channel_ids as $item) {
            $recharge_info  = DB::table('orders')->where('orders.distribution_channel_id', $item->distribution_channel_id)
                ->join('force_subscribe_users', 'force_subscribe_users.uid', '=', 'orders.uid')
                ->where('orders.created_at', '>=', $start_day)
                ->where('orders.created_at', '<=', $end_day)
                ->where('status', 'PAID')
                ->groupBy(DB::raw('date(force_subscribe_users.created_at)'))
                ->groupBy('force_subscribe_users.appid')
                ->select(
                    DB::raw('force_subscribe_users.appid,date(force_subscribe_users.created_at) as date,SUM(price) as amount,count(*) as sub_user_orders_num,count(case when orders.pay_type =1 then orders.id else null end ) as new_sub_recharge_num')
                )
                ->get();
            foreach ($recharge_info as $item_recharge) {
                $datetime2 = date_create($item_recharge->date);
                $interval = date_diff($datetime2, $datetime1)->d + 1;
                $data = [];
                $data[] = 'sub_user_orders_num';
                if ($interval <= 1) {
                    $data[] = 'one_day_recharge';
                    //self::updateOne($item_recharge->appid,$item_recharge->date,$item_recharge->amount,'one_day_recharge');
                }
                if ($interval <= 3) {
                    $data[] = 'three_days_recharge';
                    //self::updateOne($item_recharge->appid,$item_recharge->date,$item_recharge->amount,'three_days_recharge');
                }
                if ($interval <= 7) {
                    $data[] = 'seven_days_recharge';
                    //self::updateOne($item_recharge->appid,$item_recharge->date,$item_recharge->amount,'seven_days_recharge');
                }
                if ($interval <= 30) {
                    $data[] = 'thirty_days_recharge';
                    //self::updateOne($item_recharge->appid,$item_recharge->date,$item_recharge->amount,'thirty_days_recharge');
                }
                if ($interval <= 60) {
                    $data[] = 'sixty_days_recharge';
                    //self::updateOne($item_recharge->appid,$item_recharge->date,$item_recharge->amount,'sixty_days_recharge');
                }
                if ($item_recharge->date >= $two_month) {
                    //recharge_sum
                    $data[] = 'recharge_sum';
                    $data[] = 'new_sub_recharge_num';
                }
                self::updateOne($item_recharge->appid, $item_recharge->date, $item_recharge->amount, $data);
            }
        }
    }

    private static function updateOne($appid, $date, $amount, $field)
    {
        if (!$field) {
            return;
        }
        $sign = array('appid' => $appid, 'date' => $date);
        $record = UserSubscribeBehaviorStats::where($sign)->select('id')->first();
        if ($record) {
            $update_data = [];
            foreach ($field as $item) {
                if ($item == 'one_day_recharge') {
                    $update_data[$item] = $amount;
                } else {
                    // three_days_recharge=>DB::raw('three_days_recharge+'.$amount)
                    $update_data[$item] = DB::raw($item . '+' . $amount);
                }
            }
            UserSubscribeBehaviorStats::where($sign)->update($update_data);
        } else {
            $insert_data = [];
            foreach ($field as $item) {
                $insert_data[$item] = $amount;
            }
            $insert_data['appid'] = $appid;
            $insert_data['date'] = $date;
            UserSubscribeBehaviorStats::create($insert_data);
        }
    }
}