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

        if($tags['video_watch'] ?? []) {
            $sql->whereRaw(sprintf("json_contains(a.video_watch, cast('[%s]' as json))",
                join(',', $tags['video_watch'])));
        }
        if($tags['video_charge'] ?? []) {
            $sql->whereRaw(sprintf("json_contains(a.video_charge, cast('[%s]' as json))",
                join(',', $tags['video_charge'])));
        }
        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]]
        ];
    }
}