wangdu_public_api/app/Modules/User/Services/UserDivisionCpcPropertyService.php

<?php
/**
 * Created by PhpStorm.
 * User: z-yang
 * Date: 2019/4/26
 * Time: 10:14
 */

namespace App\Modules\User\Services;

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

class UserDivisionCpcPropertyService
{

    public static function userLevel($openid){
        return UserDivisionCpcProperty::where('openid',$openid)->where('is_enable',1)->select('property','earliest_subscribe_time')->first();
    }

    public static function getUserSubscribeAndChargeInfoByUid($uid){
        $sql_format = "SELECT u.id,f.created_at as subscribe_time,u.openid,
(SELECT ifnull(sum(price),0) from orders where uid = u.id and `status` = 'PAID' LIMIT 1)as amount,
(SELECT ifnull(sum(price),0) from orders where uid=u.id and `status` = 'PAID' and created_at <= DATE_ADD(f.created_at,INTERVAL 3 day)) as three_day_amount
  FROM users u
JOIN force_subscribe_users f on u.id = f.uid 
WHERE u.openid in (SELECT openid from users WHERE  id = %s)";
        $result = DB::select(sprintf($sql_format,$uid));
        if($result){
            return self::level($result);
        }
        return [];
    }

    public static function getUserSubscribeAndChargeInfoByOpenid($openid){
        $sql_format = "SELECT u.id,f.created_at as subscribe_time,u.openid,
(SELECT ifnull(sum(price),0) from orders where uid = u.id and `status` = 'PAID' LIMIT 1)as amount,
  (SELECT ifnull(sum(price),0) from orders where uid=u.id and `status` = 'PAID' and created_at <= DATE_ADD(f.created_at,INTERVAL 3 day)) as three_day_amount
FROM users u
JOIN force_subscribe_users f on u.id = f.uid 
WHERE u.openid ='%s'";
        $result = DB::select(sprintf($sql_format,$openid));
        if($result){
            return self::level($result);
        }
        return [];
    }

    private static function level($res){
        $earliest_subscribe_time = $res[0]->subscribe_time;
        $subscribe_three_day_info = [];
        $subscribe_no_three_day_info = [];
        foreach ($res as $v){
            (strtotime($v->subscribe_time) < strtotime($earliest_subscribe_time)) && $earliest_subscribe_time = $v->subscribe_time;
            if(time()-strtotime($v->subscribe_time) >= 86400*3){
                array_push($subscribe_three_day_info,$v->amount);
            }else{
                array_push($subscribe_no_three_day_info,$v->three_day_amount);
            }
        }
        $result = [
            'earliest_subscribe_time'=>$earliest_subscribe_time,
            'property'=>'',
            'type'=>'',
            'openid'=> $res[0]->openid
        ];
        if($subscribe_three_day_info){
            //存量用户
            $result['type'] = 'CUILIANG';
            $amount = round(array_sum($subscribe_three_day_info)/count($subscribe_three_day_info),2);
            if($amount>15){
                $result['property'] = 'high';
            }elseif($amount >2){
                $result['property'] = 'medium';
            } elseif($amount >0){
                $result['property'] = 'low';
            } else{
                $result['property'] = 'none';
            }
        }else{
            //新用户
            $result['type'] = 'NEW';
            if($subscribe_no_three_day_info)
                $amount = max($subscribe_no_three_day_info);
            else
                $amount = 0;
            if($amount>=50){
                $result['property'] = 'high';
            }elseif($amount >2){
                $result['property'] = 'medium';
            } elseif($amount >0){
                $result['property'] = 'low';
            } else{
                $result['property'] = 'none';
            }
        }

        return $result;
    }

    public static function update($openid,$property='',$type=''){
        if(empty($property) && empty($type)) return ;
        $update_info = [];
        $property && $update_info['property'] = $property;
        $type && $update_info['type'] = $type;

        UserDivisionCpcProperty::where('openid',$openid)->where('is_enable',1)->update($update_info);
    }
    public static function createorUpdate($data){
        $old = UserDivisionCpcProperty::where('openid',$data['openid'])->where('is_enable',1)->first();
        if(!$old){
            UserDivisionCpcProperty::create([
                'openid'=>$data['openid'] ,
                'property'=>$data['property'] ,
                'is_enable'=>1 ,
                'type'=>$data['type'] ,
                'earliest_subscribe_time'=>$data['earliest_subscribe_time']
            ]);
        }else{
            $old->property = $data['property'];
            $old->type = $data['type'];
            $old->save();
        }
    }

    public static function afterForceSubscribe($uid){

        $sql_format = "SELECT u.id,f.created_at as subscribe_time,u.openid,
(SELECT ifnull(sum(price),0) from orders where uid = u.id and `status` = 'PAID' LIMIT 1)as amount,
(SELECT ifnull(sum(price),0) from orders where uid=u.id and `status` = 'PAID' and created_at <= DATE_ADD(f.created_at,INTERVAL 3 day)) as three_day_amount
  FROM users u
JOIN force_subscribe_users f on u.id = f.uid 
WHERE u.openid in (SELECT openid from users WHERE  id = %s)";
        $res = DB::select(sprintf($sql_format,$uid));
        if(!$res) return ;
        //判断用户属性
        $earliest_subscribe_time = $res[0]->subscribe_time;
        $subscribe_total_info = [];
        $subscribe_no_three_day_info = [];
        foreach ($res as $v){
            (strtotime($v->subscribe_time) < strtotime($earliest_subscribe_time)) && $earliest_subscribe_time = $v->subscribe_time;
            if(time()-strtotime($v->subscribe_time) >= 86400*3){
                array_push($subscribe_total_info,$v->amount);
            }else{
                array_push($subscribe_no_three_day_info,$v->three_day_amount);
            }
        }
        $result = [
            'earliest_subscribe_time'=>$earliest_subscribe_time,
            'property'=>'',
            'type'=>'',
            'openid'=> $res[0]->openid
        ];
        if($subscribe_total_info){
            //存量用户
            $result['type'] = 'CUILIANG';
            $amount = round(array_sum($subscribe_total_info)/count($subscribe_total_info),2);
            if($amount>15){
                $result['property'] = 'high';
            }elseif($amount >2){
                $result['property'] = 'medium';
            } elseif($amount >0){
                $result['property'] = 'low';
            } else{
                $result['property'] = 'none';
            }
        }else{
            //新用户
            $result['type'] = 'NEW';
            if($subscribe_no_three_day_info)
                $amount = max($subscribe_no_three_day_info);
            else
                $amount = 0;
            if($amount>50){
                $result['property'] = 'high';
            }elseif($amount >2){
                $result['property'] = 'medium';
            } elseif($amount >0){
                $result['property'] = 'low';
            } else{
                $result['property'] = 'none';
            }
        }
        //保存或者创建用户属性

        $old = DB::table('user_division_cpc_property')->where('openid',$result['openid'])->where('is_enable',1)->first();
        if(!$old){
            UserDivisionCpcProperty::create([
                'openid'=>$result['openid'] ,
                'property'=>$result['property'] ,
                'is_enable'=>1 ,
                'type'=>$result['type'] ,
                'earliest_subscribe_time'=>$result['earliest_subscribe_time'],
                'updated_at'=>date('Y-m-d H:i:s'),
                'created_at'=>date('Y-m-d H:i:s')
            ]);
        }else{
            DB::table('user_division_cpc_property')->where('openid',$result['openid'])->where('is_enable',1)->update(
                [
                    'type'=>$result['type'] ,
                    'property'=>$result['property'] ,
                    'updated_at'=>date('Y-m-d H:i:s')
                ]
            );
        }
    }
}