duanju_manage/modules/Audience/Services/UserGroupService.php

<?php

namespace Modules\Audience\Services;

use Illuminate\Support\Facades\DB;

class UserGroupService
{
    public static function getSQL($tags) {
        $now = time();
        $sql = DB::table('user_some_info as a');
        if(strlen($tags['attention_hour'] ?? '') || (1 == ($tags['in_48_hour'] ?? 1)) ||
        strlen($tags['interact_hour'] ?? '') || ($tags['gzh_appids'] ?? [])) {
            $sql->join('wechat_official_user_info as b', 'a.uid', '=', 'b.uid');
            if(strlen($tags['attention_hour'] ?? '')) {
                $filter = self::filterTime($tags['attention_hour'], 'hour');
                foreach ($filter as $f) {
                    $sql->where([
                        ['b.subscribe_time', $f[0], $f[1]]
                    ]);
                }
            }
            if(1 == ($tags['in_48_hour'] ?? 1)) {
                $filter = self::filterTime('0-48', 'hour');
                foreach ($filter as $f) {
                    $sql->where([
                        ['b.subscribe_time', $f[0], $f[1]]
                    ]);
                }
            }
            if(strlen($tags['interact_hour'] ?? '')) {
                $filter = self::filterTime($tags['interact_hour'], 'hour');
                foreach ($filter as $f) {
                    $sql->where([
                        ['b.active_at', $f[0], $f[1]]
                    ]);
                }
            }
            if($tags['gzh_appids'] ?? []) {
                $sql->whereIn('b.gzh_appid', $tags['gzh_appids']);
            }
        }

        if(strlen($tags['last_watch_day'] ?? '')) {
            $filter = self::filterTime($tags['last_watch_day'], 'day');
            foreach ($filter as $f) {
                $sql->where('a.last_watch_at', $f[0], $f[1]);
            }
        }

        if(strlen($tags['register_day'] ?? '')) {
            $filter = self::filterTime($tags['register_day'], 'day');
            foreach ($filter as $f) {
                $sql->where('a.register_at', $f[0], $f[1]);
            }
        }

        if(strlen($tags['total_watch_day'] ?? '')) {
            $filter = self::filterNum($tags['total_watch_day']);
            foreach ($filter as $f) {
                $sql->where('a.total_watch_day', $f[0], $f[1]);
            }
        }

        if($tags['charge_type'] ?? ''){
            switch ($tags['charge_type']) {
                case 4:
                    $sql->where('a.is_vip', 1)
                        ->where('a.vip_end_at', '>=', $now);
                    break;
                default:
                    $sql->where('a.charge_type', $tags['charge_type']);
                    break;
            }
        }

        if($tags['total_charge_money'] ?? '') {
            $filter = self::filterNum($tags['total_charge_money']);
            foreach ($filter as $f) {
                $sql->where('a.total_charge_money', $f[0], $f[1]);
            }
        }

        if($tags['avg_charge_money'] ?? '') {
            $filter = self::filterNum($tags['avg_charge_money']);
            foreach ($filter as $f) {
                $sql->where('a.avg_charge_money', $f[0], $f[1]);
            }
        }
        if($tags['charge_num'] ?? '') {
            $filter = self::filterNum($tags['charge_num']);
            foreach ($filter as $f) {
                $sql->where('a.charge_num', $f[0], $f[1]);
            }
        }
        if($tags['remain_coin'] ?? '') {
            $filter = self::filterNum($tags['remain_coin']);
            foreach ($filter as $f) {
                $sql->where('a.remain_coin', $f[0], $f[1]);
            }
        }

        if(strlen($tags['last_charge_day'] ?? '')) {
             $filter = self::filterTime($tags['last_charge_day']);
             foreach ($filter as $f) {
                 $sql->where('a.last_charge_at', $f[0], $f[1]);
             }
        }
        if($tags['uid'] ?? 0) {
            $sql->where('b.uid', $tags['uid']);
        }
        return $sql;
    }


    public static function filterTime($time, $type='day') {
        $factor = 'hour' == $type ?  3600 :  24 * 3600;
        $arr = explode('-', $time);
        if(0 == $arr[1]) $arr[1] = PHP_INT_MAX;
        $now = time();
        return [
            ['>=', $arr[0] * $factor + $now],
            ['<=', $arr[1] * $factor + $now],
        ];
    }

    public static function filterNum($numStr) {
        $arr = explode('-', $numStr);
        if(0 == $arr[1]) $arr[1] = PHP_INT_MAX;
        return [
            ['>=', $arr[0]],
            ['<=', $arr[1]]
        ];
    }

    public static function dealPayVideo($uids, $labelInfo) {
        $videoIds = $labelInfo['video_charge'] ?? [];
        if($videoIds && count($uids)) {
            $maxUid = max($uids);
            $minUid = min($uids);
            $_uids = collect();
            $_uids = $_uids->merge(DB::table('orders')->where([
                ['status', '<>', 'UNPAID']
            ])->whereIn('video_id', $videoIds)
                ->when(count($uids) < 500, function ($query) use($uids){
                    return $query->whereIn('uid', $uids);
                }, function ($query) use($minUid, $maxUid){
                    return $query->where('uid', '>=', $minUid)
                        ->where('uid', '<=', $maxUid);
                })
                ->distinct()
                ->select('uid')->get()->pluck('uid'))->unique();
            return collect($uids)->intersect($_uids->unique())->unique()->toArray();
        } else {
            return $uids;
        }
    }

    public static function dealHistoryReadBooks($uids, $labelInfo) {
        $videoIds = $labelInfo['video_watch'] ?? [];
        if($videoIds && count($uids)) {
            $maxUid = max($uids);
            $minUid = min($uids);
            $_uids = collect();
            foreach ($videoIds as $videoId) {
                $tableName = 'video_users_'. ($videoId % 8);
                $_uids = $_uids->merge(DB::table($tableName)
                    ->where(['video_id' => $videoId])
                    ->when(count($uids) < 500, function ($query) use($uids){
                        return $query->whereIn('uid', $uids);
                    }, function ($query) use($minUid, $maxUid){
                        return $query->where('uid', '>=', $minUid)
                            ->where('uid', '<=', $maxUid);
                    })
                    ->select('uid')->get()->pluck('uid'));
            }
            return collect($uids)->intersect($_uids->unique())->unique()->toArray();
        } else {
            return $uids;
        }
    }

}