zhuishuyun_quickapp/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 App\Modules\User\Models\UserDivisionCpcPropertyV2;
use DB;

class UserDivisionCpcPropertyService
{

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

    public static function getUserProperty($uid){
        $result = UserDivisionCpcProperty::join('users','users.openid','=','user_division_cpc_property.openid')
            ->where('users.id',$uid)
            ->select('user_division_cpc_property.property')->first();
        if($result)
            return $result->property;
        return '';
    }

    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')
                ]
            );
        }
    }

    /**
     *   Openid一对一注册uid，充值超过30元直接成为高净值用户，否则注册3天后做净值分档；
     *   Openid一对多注册uid，注册3天以上成为有效uid，有效uid充值总额比uid个数；
     *   净值区间无论一对一注册uid还是一对多注册uid：
     *   低净值：0<充值<10，openid数占比35.51%，充值占比6.03%；
     *   中净值:10=<充值<=30，openid数占比32.89%，充值占比23.81%；
     *   高净值:充值>30，openid数占比31.60%，充值占比70.16%;
     * @param $uid
     */
    public static function calculateUserPropertyV2($uid)
    {
        $sql = "SELECT users.id as uid,users.openid,users.created_at as register,(select SUM(price) from orders where orders.uid = users.id and `status` = 'PAID') as price FROM users 
WHERE openid in (SELECT openid FROM users WHERE id = $uid)";
        $result = DB::select($sql);
        if (!$result) return [];
        if(count($result) == 1 ){
            if($result[0]->price && $result[0]->price>30){
                return ['openid'=>$result[0]->openid,'property'=>'high'];
            }
            if(time()-strtotime($result[0]->register) < 3*86400){
                return ['openid'=>$result[0]->openid,'property'=>'undefined'];
            }
        }
        $valid_user_num = 0;
        $amount = 0;
        $openid = '';
        $all_amount = 0;
        foreach ($result as $item){
            $openid = $item->openid;
            if($item->price) $all_amount += $item->price;
            if(time()-strtotime($item->register) < 3*86400) continue;
            if($item->price) $amount += $item->price;
            $valid_user_num += 1;
        }
        if(!$valid_user_num){
            if($all_amount)return ['openid'=>$openid,'property'=>'undefined'];
            return [];
        }

        $average_amount = $amount/$valid_user_num;
        if($average_amount >30){
            return ['openid'=>$openid,'property'=>'high'];
        }elseif ($average_amount >=10){
            return ['openid'=>$openid,'property'=>'medium'];
        }else{
            return ['openid'=>$openid,'property'=>'low'];
        }
    }

    public static function userLevelV2($openid){
        $result =  UserDivisionCpcPropertyV2::where('openid',$openid)->where('is_enable',1)->select('property')->first();
        if($result)
            return $result->property;
        $sql_format = 'select id,created_at from users WHERE  openid="%s" ORDER by created_at limit 1';
        $info = DB::select(sprintf($sql_format,$openid));
        if(!$info) return 'unknown';
        foreach ($info as $item){
            if( time()-strtotime($item->created_at) > 3*86400 )
                return 'none';
        }
        return 'unknown';
    }

    public static function getUserPropertyV2($uid){
        $result = UserDivisionCpcPropertyV2::join('users','users.openid','=','user_division_cpc_property_v2.openid')
            ->where('users.id',$uid)
            ->select('user_division_cpc_property_v2.property')->first();
        if($result)
            return $result->property;
        $sql_format = 'select id,created_at from users WHERE  openid = (select openid FROM users WHERE id=%s) ORDER by created_at limit 1';
        $info = DB::select(sprintf($sql_format,$uid));
        if(!$info) return 'unknown';
        foreach ($info as $item){
            if( time()-strtotime($item->created_at) > 3*86400 )
                return 'none';
        }
        return 'unknown';
    }

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

        UserDivisionCpcPropertyV2::where('openid',$openid)->where('is_enable',1)->update($update_info);
    }

    public static function createorUpdateV2($data){
        $old = UserDivisionCpcPropertyV2::where('openid',$data['openid'])->where('is_enable',1)->first();
        if(!$old){
            UserDivisionCpcPropertyV2::create([
                'openid'=>$data['openid'] ,
                'property'=>$data['property'] ,
                'is_enable'=>1
            ]);
        }else{
            $old->property = $data['property'];
            $old->save();
        }
    }
}