test/app/Http/Services/HomeService.php

<?php
/**
 * 首页服务类
 * @copyright 引擎力
 * @author lc
 * @since 2021-06-23
 */

namespace App\Http\Services;

use App\Http\Models\Role;
use App\Http\Models\Site;
use App\Http\Models\SitesStatus;
use App\Http\Models\User;
use App\Http\Models\WeekTaskInfo;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class HomeService
{
    //逾期任务列表,默认前端,设计27
    public function getTaskList($role = Role::TYPE_WEB)
    {
        $taskInfo = WeekTaskInfo::query();

        $userIds = User::query()
            ->where('status', 1)
            ->whereIn('role_id', [$role])
            ->pluck('id');
        $taskInfo->whereIn('user_type', $userIds);

        $taskList = $taskInfo
                ->where('type', 'now')
                ->where('status', '!=', 'ok')
                ->orderBy('deadline', 'asc')
                ->limit(7)->get() ?? [];
        return $taskList;
    }

    //折线任务图
    public function getTaskByWeek($role = Role::TYPE_WEB)
    {
        $userList = User::query()
            ->where('status', 1)
            ->where('role_id', $role)
            ->select('id', 'nickname')
            ->get();
        $ids = array_column($userList->toArray(), 'id');

        $taskList = WeekTaskInfo::query()
            ->whereIn('user_type', $ids)
            ->where('status', '!=', 'ok')
            ->where('deadline', '<', date('Y-m-d'))
            ->selectRaw("user_type,count(id) as count")
            ->groupBy('user_type')
            ->get();

        foreach ($userList as $item) {
            $item->count = 0;

            foreach ($taskList as $value) {
                if ($item->id == $value->user_type) {
                    $item->count = $value->count;
                }
            }
        }

        $data = [
            'name' => array_column($userList->toArray(), 'nickname'),
            'list' => array_column($userList->toArray(), 'count'),
        ];

        return $data;
    }

    //项目总揽
    public function getProjectOverview()
    {
        $sitesStatusList = SitesStatus::query()
            ->where('status', 404)
            ->where('is_handle', 0)
            ->where('url', 'NOT LIKE', '%javascript:%')
            ->where('url', 'NOT LIKE', '%tel:%')
            ->where('url', 'NOT LIKE', '%mailto:%')
            ->where('url', 'NOT LIKE', '%$%')
            ->where('url', 'NOT LIKE', '%skype%')
            ->where('url', 'NOT LIKE', '%whatsapp%')
            ->selectRaw('count(*) as count,site_id')
            ->groupBy('site_id')
            ->orderBy('count', 'desc')
            ->get();

        $siteIds = array_column($sitesStatusList->toArray(), 'site_id');

        $siteList = Site::query()->with(['users'])->select('id', 'cn_title')->whereIn('id', $siteIds)->get();

        foreach ($siteList as $value) {
            $value->userId = $value->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->first()->id ?? '';
            $value->name = $value->users->where('role_id', Role::TYPE_OPTIMIZATION_EDITING)->first()->nickname ?? '';
        }

        $list = [];
        foreach ($siteList as $value) {
            $list[] = [
                'id' => $value->id,
                'cn_title' => $value->cn_title,
                'name' => $value->name,
                'user_id' => $value->userId,
            ];
        }
        $user = User::query()->where('status', 1)->pluck('username', 'id');
        $array = [];
        $list = $this->arrayGroupBy($list, 'user_id');
        foreach ($list as $key => $value) {
            $array[] = [
                'userId' => $user[$key] ?? '',
                'count' => count($value)
            ];
        }

        $list = [
            'user' => array_column($array, 'userId'),
            'count' => array_column($array, 'count'),
        ];

        $thisMonth = [date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')];
        $lastMonth = [date('Y-m-01 00:00:00', strtotime('first day of -1 month')),date('Y-m-t 23:59:59', strtotime('first day of -1 month'))];

        //这个月上线的项目总数
        $thisMonthOnlineSite = Site::query()->whereBetween('online_at', $thisMonth)->count() ?? 0;
        //上月上线的项目总数
        $lastMonthOnlineSite = Site::query()->whereBetween('online_at', $lastMonth)->count() ?? 0;
        $ratioOnlineSite = $this->compareAdd($thisMonthOnlineSite, $lastMonthOnlineSite);

        //这个月签单的项目总数
        $thisMonthOrderSite = Site::query()->whereBetween('sign_at', $thisMonth)->count() ?? 0;
        //上月下签的项目总数
        $lastMonthOrderSite = Site::query()->whereBetween('sign_at', $lastMonth)->count() ?? 0;
        $ratioOrderSite = $this->compareAdd($thisMonthOrderSite, $lastMonthOrderSite);

        //这个月达标的项目总数
        $thisMonthQualifiedSite = Site::query()->whereBetween('reach_at', $thisMonth)->count() ?? 0;
        //上月达标的项目总数
        $lastMonthQualifiedSite = Site::query()->whereBetween('reach_at', $lastMonth)->count() ?? 0;
        $ratioQualifiedSite = $this->compareAdd($thisMonthQualifiedSite, $lastMonthQualifiedSite);

        //这个月下单的项目总数
        $thisMonthAssignSite = Site::query()->whereBetween('order_at', $thisMonth)->count() ?? 0;
        //上月下单的项目总数
        $lastMonthAssignSite = Site::query()->whereBetween('order_at', $lastMonth)->count() ?? 0;
        $ratioAssignSite = $this->compareAdd($thisMonthAssignSite, $lastMonthAssignSite);

        return [$list, $thisMonthOnlineSite, $ratioOnlineSite, $thisMonthOrderSite, $ratioOrderSite, $thisMonthQualifiedSite, $ratioQualifiedSite, $thisMonthAssignSite, $ratioAssignSite];
    }

    //环比计算
    function compareAdd($a, $b)
    {
        $a = (int)$a;
        $b = (int)$b;
        if ($a == $b) {
            return "无变动";
        } elseif ($b == 0 && $a > 0) {
            return 100;
        } elseif ($a == 0 && $b > 0) {
            return -100;
        } elseif ($a > $b) {
            $c = round(($a - $b) / $b, 2);
            $c = $c * 100;
            return $c;
        } elseif ($a < $b) {
            $c = round(($a - $b) / $b, 2);
            $c = $c * 100;
            return $c;
        } else {
            return "系统错误";
        }
    }


    public static function getMonth($time = '', $format = 'Y-m-d')
    {
        $time = $time != '' ? $time : time();
        //获取当前周几
        $week = date('d', $time);
        $date = [];
        for ($i = 1; $i <= date('t', $time); $i++) {
            $date[$i] = date($format, strtotime('+' . $i - $week . ' days', $time));
        }
        return $date;
    }

    public function getProjectStatusList($role = Role::TYPE_WEB)
    {
        $userIds = User::query()->where('role_id', $role)->pluck('id')->toArray() ?? [];
        if ($role == Role::TYPE_WEB) {
            $userIds[] = 'wfp-web';
        } else {
            $userIds[] = 'wfp-design';
        }
        //逾期
        $task1 = WeekTaskInfo::query()
            ->whereIn('user_type', $userIds)
            ->where('status', '!=', 'ok')
            ->where('deadline', '<', date('Y-m-d'))
            ->selectRaw("day,count(id) as ids")
            ->groupBy('day')
            ->orderBy('day', 'asc')->pluck('ids', 'day')->toArray();

        foreach ($task1 as $key => $item) {
            if ($key == 0) {
                $task1[7] = $item;
                unset($task1[$key]);
            }
        }

        //进行中
        $task2 = WeekTaskInfo::query()
            ->whereIn('user_type', $userIds)
            ->where('status', '!=', 'ok')
            ->where('deadline', '>', date('Y-m-d'))
            ->selectRaw("day,count(id) as ids")
            ->groupBy('day')
            ->orderBy('day', 'asc')->pluck('ids', 'day')->toArray();
        foreach ($task2 as $key => $item) {
            if ($key == 0) {
                $task2[7] = $item;
                unset($task2[$key]);
            }
        }
        //完成
        $task3 = WeekTaskInfo::query()
            ->whereIn('user_type', $userIds)
            ->where('status', '=', 'ok')
            ->selectRaw("day,count(id) as ids")
            ->groupBy('day')
            ->orderBy('day', 'asc')->pluck('ids', 'day')->toArray();

        foreach ($task3 as $key => $item) {
            if ($key == 0) {
                $task3[7] = $item;
                unset($task3[$key]);
            }
        }
        $result = $this->setWeekData($task1);
        foreach ($result as $key => $value) {
            $result[$key] = '-' . $value;
        }
        $type = [
            'left' => $result,
            'series' => $this->setWeekData($task2),
            'inside' => $this->setWeekData($task3),
        ];

        return $type;
    }

    public function setWeekData($task)
    {
        $num = [1, 2, 3, 4, 5, 6, 7];
        foreach ($num as $item) {
            if (!array_key_exists($item, $task)) {
                $task[$item] = 0;
            }
        }
        ksort($task);
        return array_merge($task);
    }

    public function getMonthBySiteList()
    {
        $whereBetween = [date("Y-m-d", strtotime("first day of -3 month")), date('Y-m-t')];

        $siteOrderList = $this->getMonthBySiteField($whereBetween, 'sign');
        $siteOnlineList = $this->getMonthBySiteField($whereBetween, 'online');
        $siteReachList = $this->getMonthBySiteField($whereBetween, 'reach');

        $monthList = [
            date("Ym"),
            date("Ym", strtotime("first day of -1 month")),
            date("Ym", strtotime("first day of -2 month")),
            date("Ym", strtotime("first day of -3 month")),
        ];
        foreach ($monthList as $item) {
            $this->getMonthSiteList($item, $siteOrderList, $siteOnlineList, $siteReachList);
        }
        ksort($siteOrderList);
        ksort($siteOnlineList);
        ksort($siteReachList);

        $months = ['product'];
        $orderList = ['签单'];
        $onlineList = ['上线'];
        $reachList = ['达标'];

        foreach ($siteOrderList as $key => $item) {
            $months[] = $key . '月';
            $orderList[] = $item;
        }
        foreach ($siteOnlineList as $key => $item) {
            $onlineList[] = $item;
        }
        foreach ($siteReachList as $key => $item) {
            $reachList[] = $item;
        }

        $data = [
            $months, $orderList, $onlineList, $reachList,
        ];

        return $data;
    }

    public function getMonthBySiteList2()
    {
        $whereBetween = [date("Y-m-d 00:00:00", strtotime("first day of -6 month")), date('Y-m-t 23:59:59')];
        $siteOrderList = $this->getMonthBySiteField($whereBetween, 'sign');
        $siteOnlineList = $this->getMonthBySiteField($whereBetween, 'online');

        $siteReachList = $this->getMonthBySiteField($whereBetween, 'reach');
        $siteRenewalList = $this->getMonthBySiteField($whereBetween, 'renewal');
        $siteExpiredList = $this->getMonthBySiteField($whereBetween, 'expired');

        $monthList = [
            date("Ym"),
            date("Ym", strtotime("first day of -1 month")),
            date("Ym", strtotime("first day of -2 month")),
            date("Ym", strtotime("first day of -3 month")),
            date("Ym", strtotime("first day of -4 month")),
            date("Ym", strtotime("first day of -5 month")),
            date("Ym", strtotime("first day of -6 month")),
        ];

        foreach ($monthList as $item) {

            $this->getMonthSiteList($item, $siteOrderList, $siteOnlineList, $siteReachList);

            foreach ($siteRenewalList as $key => $value) {
                if (!array_key_exists($item, $siteRenewalList)) {
                    $siteRenewalList[$item] = 0;
                }
            }
            foreach ($siteExpiredList as $key => $value) {
                if (!array_key_exists($item, $siteExpiredList)) {
                    $siteExpiredList[$item] = 0;
                }
            }
        }

        ksort($siteOrderList);
        ksort($siteOnlineList);
        ksort($siteRenewalList);
        ksort($siteReachList);
        ksort($siteExpiredList);

        $months = [];
        $orderList = [];
        $onlineList = [];
        $renewalList = [];
        $reachList = [];
        $expiredList = [];

        foreach ($siteOrderList as $key => $item) {
            $months[] = $key . '月';
            $orderList[] = $item;
        }
        foreach ($siteOnlineList as $key => $item) {
            $onlineList[] = $item;
        }
        foreach ($siteRenewalList as $key => $item) {
            $renewalList[] = $item;
        }
        foreach ($siteReachList as $key => $item) {
            $reachList[] = $item;
        }
        foreach ($siteExpiredList as $key => $item) {
            $expiredList[] = $item;
        }

        $data = [
            'title' => ['签单', '上线', '达标', '到期', '续费'],
            'months' => $months,
            'orderList' => $orderList,
            'onlineList' => $onlineList,
            'reachList' => $reachList,
            'expiredList' => $expiredList,
            'renewalList' => $renewalList,
        ];

        return $data;
    }

    /**
     * 根据字段和条件筛选以日期分组查询分组
     * @param $whereBetween
     * @param $whereField
     * @return array
     */
    public function getMonthBySiteField($whereBetween, $whereField)
    {
        $siteOrderList = Site::query()
            ->whereNotNull("{$whereField}_at")
            ->whereBetween("{$whereField}_at", $whereBetween)
            ->selectRaw("DATE_FORMAT({$whereField}_at, '%Y%m') as {$whereField}_date,count(id) as ids")
            ->groupBy("{$whereField}_date")
            ->orderBy("{$whereField}_date", 'desc')
            ->pluck('ids', "{$whereField}_date")->toArray();

        return $siteOrderList;
    }

    /**
     * 测速
     * @return array|mixed
     */
    public function getSpeedMeasurement()
    {
        $siteList = Site::query()
            ->whereIn('status', [2, 3])
            ->select('id', 'old_id', 'domain', 'cn_title', 'speed_memo')
            ->whereNotNull('database')->get();

        $pcSpeedMeasurement = [];
        $mobileSpeedMeasurement = [];

        $speedMeasurementList = DB::table('app_speed_measurement_cache')->get();
        foreach ($speedMeasurementList as $key => $value) {
            $array = json_decode($value->cache, true);
            $oldId = str_replace('cache:app/Http/Controllers/SpeedMeasurementController/index:', '', $value->key);
            $pcSpeedMeasurement[$oldId] = $array['pc'];
            $mobileSpeedMeasurement[$oldId] = $array['mobile'];
        }

        foreach ($siteList as $item) {
            $item->pcSpeedMeasurement = $pcSpeedMeasurement[$item->old_id] ?? '';
            $item->mobileSpeedMeasurement = $mobileSpeedMeasurement[$item->old_id] ?? '';
        }

        foreach ($siteList as $key => $value) {
            if ($value->pcSpeedMeasurement > 79) {
                unset($siteList[$key]);
            }
        }

        $siteIds = array_column($siteList->toArray(), 'id');
        $userIds = User::query()->where('role_id', Role::TYPE_WEB)->where('status', 1)->pluck('id');

        $user = DB::table('user_has_sites')->whereIn('user_id', $userIds)->get()->toJson();
        $user = \GuzzleHttp\json_decode($user, true);

        foreach ($user as $key => $value) {
            if (!in_array($value['site_id'], $siteIds)) {
                unset($user[$key]);
            }
        }

        $list = $this->arrayGroupBy($user, 'user_id');
        $users = [];
        foreach ($list as $key => $value) {
            $users[] = [
                'user_id' => $key,
                'count' => count($value),
            ];
        }

        $nameList = User::query()->where('role_id', Role::TYPE_WEB)->pluck('nickname', 'id');

        foreach ($users as $key => $user) {
            $users[$key]['name'] = $nameList[$user['user_id']];
        }

        $array = [
            'list' => array_column($users, 'name'),
            'count' => array_column($users, 'count'),
        ];

        return $array;
    }

    public function arrayGroupBy($arr, $key)
    {
        $grouped = [];
        foreach ($arr as $value) {
            $grouped[$value[$key]][] = $value;
        }
        if (func_num_args() > 2) {
            $args = func_get_args();
            foreach ($grouped as $key => $value) {
                $parameter = array_merge($value, array_slice($args, 2, func_num_args()));
                $grouped[$key] = call_user_func_array('array_group_by', $parameter);
            }
        }
        return $grouped;
    }

    private function substrFormat($text, $length, $replace = '...', $encoding = 'UTF-8')
    {
        if ($text && mb_strlen($text, $encoding) > $length) {
            return mb_substr($text, 0, $length, $encoding) . $replace;
        }
        return $text;
    }

    private function getMonthSiteList(&$item, &$siteOrderList, &$siteOnlineList, &$siteReachList)
    {
        foreach ($siteOrderList as $key => $value) {
            if (!array_key_exists($item, $siteOrderList)) {
                $siteOrderList[$item] = 0;
            }
        }
        foreach ($siteOnlineList as $key => $value) {
            if (!array_key_exists($item, $siteOnlineList)) {
                $siteOnlineList[$item] = 0;
            }
        }
        foreach ($siteReachList as $key => $value) {
            if (!array_key_exists($item, $siteReachList)) {
                $siteReachList[$item] = 0;
            }
        }
    }

    public function getSiteListByProjectManagers()
    {
        $dateList = [];
        for ($i = 11; $i >= 0; $i--) {
            $whereBetween = [
                date("Y-m-d 00:00:00", strtotime("first day of -{$i} month")),
                date('Y-m-t 23:59:59', strtotime("first day of -{$i} month"))
            ];
            $list = User::query()
                ->select('id', 'role_id', 'nickname')
                ->where('status', 1)
                ->where('role_id', 25)->withCount(
                    ['sites as relation' => function (Builder $query) use ($whereBetween) {
                        $query->whereBetween('online_at', $whereBetween);
                    }])->get()->toArray();
            $dateList[date("Y-m", strtotime("first day of -{$i} month"))] = $list;
        }
        return $this->getResult($dateList);
    }

    public function getSiteListByOptimizeEditing()
    {
        $dateList = [];
        for ($i = 11; $i >= 0; $i--) {
            $whereBetween = [
                date("Y-m-d 00:00:00", strtotime("first day of -{$i} month")),
                date('Y-m-t 23:59:59', strtotime("first day of -{$i} month"))
            ];
            $list = User::query()
                ->select('id', 'role_id', 'nickname')
                ->where('status', 1)
                ->where('role_id', 32)->withCount(
                    ['sites as relation' => function (Builder $query) use ($whereBetween) {
                        $query->whereBetween('reach_at', $whereBetween);
                    }])->get()->toArray();
            $dateList[date("Y-m", strtotime("first day of -{$i} month"))] = $list;
        }
        return $this->getResult($dateList);

    }

    public function getSiteListByProjectHousekeeper()
    {
        $dateList = [];
        for ($i = 11; $i >= 0; $i--) {
            $whereBetween = [
                date("Y-m-d 00:00:00", strtotime("first day of -{$i} month")),
                date('Y-m-t 23:59:59', strtotime("first day of -{$i} month"))
            ];
            $list = User::query()
                ->select('id', 'role_id', 'nickname')
                ->where('status', 1)
                ->where('role_id', 7)->withCount(
                    ['sites as relation' => function (Builder $query) use ($whereBetween) {
                        $query->whereBetween('renewal_at', $whereBetween);
                    }])->get()->toArray();
            $dateList[date("Y-m", strtotime("first day of -{$i} month"))] = $list;
        }
        return $this->getResult($dateList);
    }


    public function getSiteRenewListByProjectHousekeeper()
    {
        $dateList = [];
        for ($i = 11; $i >= 0; $i--) {
            $whereBetween = [
                date("Y-m-d 00:00:00", strtotime("first day of -{$i} month")),
                date('Y-m-t 23:59:59', strtotime("first day of -{$i} month"))
            ];
            $list = User::query()
                ->select('id', 'role_id', 'nickname')
                ->where('status', 1)
                ->where('role_id', 7)->withCount(
                    ['sites as relation' => function (Builder $query) use ($whereBetween) {
                        $query->whereBetween('renewal_at', $whereBetween);
                        $query->select(DB::raw("sum(renewal_amount) as relation"));
                    }])->get()->toArray();
            $dateList[date("Y-m", strtotime("first day of -{$i} month"))] = $list;
        }
        return $this->getResult($dateList);
    }

    public function getResult($dateList)
    {
        $title = [];
        $nameList = [];
        $result = [];

        foreach ($dateList as $key => $value) {
            $title[] = $key;
            $nameList = array_column($value, 'nickname');
        }
        array_push($nameList, '总数');

        $user = [];
        foreach ($nameList as $key => $value) {
            if ($value == '总数') {
                $user[$value] = true;
            } else {
                $user[$value] = false;
            }
        }

        foreach ($dateList as $key => $value) {
            foreach ($value as $kk => $vv) {
                foreach ($nameList as $kkk => $vvv) {
                    if ($vvv == $vv['nickname']) {
                        $result[$vvv][] = $vv['relation'] ?? 0;
                    }
                }
            }
        }

        $list = [];
        for ($i = 0; $i < count($title); $i++) {
            $num = 0;
            foreach ($result as $key => $value) {
                $num += $value[$i];
            }
            $list[] = $num;
        }
        $result['总数'] = $list;

        $data = [
            'title' => $title,
            'name' => $nameList,
            'list' => $result,
            'user' => $user,
        ];

        $userList = [];
        foreach ($data['name'] as $value) {
            foreach ($data['list'] as $key => $vv) {
                if ($value == $key) {
                    $userList[] = array_sum($vv);
                }
            }
        }
        $data['sum'] = $userList;
        return $data;
    }


}