jucai/app/Http/Controllers/Api/Config/BiController.php

<?php

namespace App\Http\Controllers\Api\Config;

use App\Http\Controllers\Api\ApiBaseController;
use App\Models\Company;
use App\Models\Config;
use App\Models\Jobs;
use App\Models\Recruit;
use App\Models\RecruitAppointInfo;
use App\Models\RecruitPost;
use App\Models\Resume;
use Illuminate\Support\Facades\DB;

class BiController extends ApiBaseController
{
    public function jkq()
    {
        $data = [];

        //人才数据
        $talent                   = Config::whereIn('type_id', [78, 79, 80, 81, 82])->get();
        $talent                   = $this->_valueToKey($talent, 'alias');
        $data['talent_level']     = [
            ['name' => '第一层次', 'count' => (int)$talent['talent_level_1']['value']],
            ['name' => '第二层次', 'count' => (int)$talent['talent_level_2']['value']],
            ['name' => '第三层次', 'count' => (int)$talent['talent_level_3']['value']],
            ['name' => '第四层次', 'count' => (int)$talent['talent_level_4']['value']],
            ['name' => '第五层次', 'count' => (int)$talent['talent_level_5']['value']],
            ['name' => '第六层次', 'count' => (int)$talent['talent_level_6']['value']],
            ['name' => '第七层次', 'count' => (int)$talent['talent_level_7']['value']],
        ];
        $data['talent_industry']  = [
            ['name' => $talent['talent_industry_trade']['name'], 'count' => (int)$talent['talent_industry_trade']['value']],
            ['name' => $talent['talent_industry_goods']['name'], 'count' => (int)$talent['talent_industry_goods']['value']],
            ['name' => $talent['talent_industry_clothing']['name'], 'count' => (int)$talent['talent_industry_clothing']['value']],
            ['name' => $talent['talent_industry_drug']['name'], 'count' => (int)$talent['talent_industry_drug']['value']],
            ['name' => $talent['talent_industry_medical']['name'], 'count' => (int)$talent['talent_industry_medical']['value']],
            ['name' => $talent['talent_industry_hotel']['name'], 'count' => (int)$talent['talent_industry_hotel']['value']],
            ['name' => $talent['talent_industry_traffic']['name'], 'count' => (int)$talent['talent_industry_traffic']['value']],
            ['name' => $talent['talent_industry_other']['name'], 'count' => (int)$talent['talent_industry_other']['value']],
        ];
        $data['talent_age']       = [
            ['name' => $talent['talent_age_1']['name'], 'count' => (int)$talent['talent_age_1']['value']],
            ['name' => $talent['talent_age_2']['name'], 'count' => (int)$talent['talent_age_2']['value']],
            ['name' => $talent['talent_age_3']['name'], 'count' => (int)$talent['talent_age_3']['value']],
            ['name' => $talent['talent_age_4']['name'], 'count' => (int)$talent['talent_age_4']['value']],
        ];
        $data['talent_education'] = [
            ['name' => $talent['talent_education_1']['name'], 'count' => (int)$talent['talent_education_1']['value']],
            ['name' => $talent['talent_education_2']['name'], 'count' => (int)$talent['talent_education_2']['value']],
            ['name' => $talent['talent_education_3']['name'], 'count' => (int)$talent['talent_education_3']['value']],
        ];

        //企业数和岗位数
        $company_count       = Company::where('is_jkq', 1)->count();
        $job_count           = Jobs::whereHas('company', function ($query) {
            $query->where('is_jkq', 1);
        })->count();
        $data['total_count'] = [
            ['name' => '企业数', 'count' => $company_count],
            ['name' => '岗位数', 'count' => $job_count],
            ['name' => '硕博人才数', 'count' => (int)$talent['talent_total']['value']],
        ];

        //企业所属行业
        $company_trade_data  = Company::where('is_jkq', 1)->select(['trade', DB::raw('count(trade) as count')])->groupBy('trade')->get();
        $company_trade       = [];
        $company_trade_other = 0;
        foreach ($company_trade_data as $v) {
            switch ($v['trade']) {
                case 11:
                    $company_trade[] = ['name' => '贸易/进出口', 'count' => $v['count']];
                    break;
                case 13:
                    $company_trade[] = ['name' => '消费品(食/饮/烟酒)', 'count' => $v['count']];
                    break;
                case 14:
                    $company_trade[] = ['name' => '服装/纺织/皮革', 'count' => $v['count']];
                    break;
                case 19:
                    $company_trade[] = ['name' => '制药/生物工程', 'count' => $v['count']];
                    break;
                case 21:
                    $company_trade[] = ['name' => '医疗设备/器械', 'count' => $v['count']];
                    break;
                case 36:
                    $company_trade[] = ['name' => '酒店/旅游', 'count' => $v['count']];
                    break;
                case 37:
                    $company_trade[] = ['name' => '交通/运输/物流', 'count' => $v['count']];
                    break;
                default:
                    $company_trade_other += $v['count'];
                    break;
            }
        }
        $company_trade[]       = ['name' => '其他', 'count' => $company_trade_other];
        $data['company_trade'] = $company_trade;

        //企业需求工种
        $job_category_data  = Jobs::whereHas('company', function ($query) {
            $query->where('is_jkq', 1);
        })->select(['topclass', DB::raw('count(topclass) as count')])->groupBy('topclass')->get();
        $job_category       = [];
        $job_category_other = 0;
        foreach ($job_category_data as $v) {
            switch ($v['topclass']) {
                case 1:
                    $job_category[] = ['name' => '销售|市场|客服|贸易', 'count' => $v['count']];
                    break;
                case 74:
                    $job_category[] = ['name' => '计算机|通信', 'count' => $v['count']];
                    break;
                case 96:
                    $job_category[] = ['name' => '服装/纺织/皮革', 'count' => $v['count']];
                    break;
                case 116:
                    $job_category[] = ['name' => '生产|质管|技工', 'count' => $v['count']];
                    break;
                case 136:
                    $job_category[] = ['name' => '电子|电气|能源|化工', 'count' => $v['count']];
                    break;
                case 225:
                    $job_category[] = ['name' => '医疗|制药|环保', 'count' => $v['count']];
                    break;
                case 258:
                    $job_category[] = ['name' => '学生|社工|科研|农业|其他', 'count' => $v['count']];
                    break;
                default:
                    $job_category_other += $v['count'];
                    break;
            }
        }
        $job_category[]       = ['name' => '其他', 'count' => $job_category_other];
        $data['job_category'] = $job_category;

        //企业薪酬范围
        $job_wage_1       = Jobs::whereHas('company', function ($query) {
            $query->where('is_jkq', 1);
        })->where('wage_min', '<=', 3000)->count();
        $job_wage_2       = Jobs::whereHas('company', function ($query) {
            $query->where('is_jkq', 1);
        })->where('wage_min', '>=', 3000)->where('wage_min', '<', 5000)->count();
        $job_wage_3       = Jobs::whereHas('company', function ($query) {
            $query->where('is_jkq', 1);
        })->where('wage_min', '>=', 5000)->where('wage_min', '<', 10000)->count();
        $job_wage_4       = Jobs::whereHas('company', function ($query) {
            $query->where('is_jkq', 1);
        })->where('wage_max', '>=', 10000)->count();
        $data['job_wage'] = [
            ['name' => '3000以下/月', 'count' => $job_wage_1],
            ['name' => '3000-5000/月', 'count' => $job_wage_2],
            ['name' => '5000-10000/月', 'count' => $job_wage_3],
            ['name' => '10000以上/月', 'count' => $job_wage_4],
        ];

        //企业学历范围
        $job_education_data  = Jobs::whereHas('company', function ($query) {
            $query->where('is_jkq', 1);
        })->select(['education', DB::raw('count(education) as count')])->groupBy('education')->get();
        $job_education       = [];
        $job_education_other = 0;
        foreach ($job_education_data as $v) {
            switch ($v['education']) {
                case 69:
                    $job_education[] = ['name' => '大专', 'count' => $v['count']];
                    break;
                case 70:
                    $job_education[] = ['name' => '本科', 'count' => $v['count']];
                    break;
                case 71:
                    $job_education[] = ['name' => '硕士', 'count' => $v['count']];
                    break;
                case 72:
                    $job_education[] = ['name' => '博士', 'count' => $v['count']];
                    break;
                case 73:
                    $job_education[] = ['name' => '博士后', 'count' => $v['count']];
                    break;
                default:
                    $job_education_other += $v['count'];
                    break;
            }
        }
        $job_education[]       = ['name' => '其他', 'count' => $job_education_other];
        $data['job_education'] = $job_education;

        //求职者性别
        $resume_sex_data = Resume::select(['sex', DB::raw('count(sex) as count')])->groupBy('sex')->get();
        $resume_sex      = [];
        $resume_total    = 0;
        foreach ($resume_sex_data as $v) {
            $resume_total += $v['count'];
            switch ($v['sex']) {
                case 0:
                    $resume_sex[] = ['name' => '未知', 'count' => $v['count']];
                    break;
                case 1:
                    $resume_sex[] = ['name' => '男', 'count' => $v['count']];
                    break;
                case 2:
                    $resume_sex[] = ['name' => '女', 'count' => $v['count']];
                    break;
            }
        }
        $resume_sex[]       = ['name' => '总人数', 'count' => $resume_total];
        $data['resume_sex'] = $resume_sex;

        //求职者求职工种
        $resume_trade_1       = Resume::where('trade', 'like', "11%")->count();
        $resume_trade_2       = Resume::where('trade', 'like', "13%")->count();
        $resume_trade_3       = Resume::where('trade', 'like', "14%")->count();
        $resume_trade_4       = Resume::where('trade', 'like', "19%")->count();
        $resume_trade_5       = Resume::where('trade', 'like', "21%")->count();
        $resume_trade_6       = Resume::where('trade', 'like', "36%")->count();
        $resume_trade_7       = Resume::where('trade', 'like', "37%")->count();
        $data['resume_trade'] = [
            ['name' => '贸易/进出口', 'count' => $resume_trade_1],
            ['name' => '消费品(食/饮/烟酒)', 'count' => $resume_trade_2],
            ['name' => '服装/纺织/皮革', 'count' => $resume_trade_3],
            ['name' => '制药/生物工程', 'count' => $resume_trade_4],
            ['name' => '医疗设备/器械', 'count' => $resume_trade_5],
            ['name' => '酒店/旅游', 'count' => $resume_trade_6],
            ['name' => '交通/运输/物流', 'count' => $resume_trade_7],
            ['name' => '其他', 'count' => ($resume_total - $resume_trade_1 - $resume_trade_2 - $resume_trade_3 - $resume_trade_4 - $resume_trade_5 - $resume_trade_6 - $resume_trade_7)],
        ];

        //求职者年龄分布
        $year               = date('Y');
        $resume_age_1       = Resume::where('birthdate', '>', $year - 30)->count();
        $resume_age_2       = Resume::where('birthdate', '>', $year - 39)->where('birthdate', '<', $year - 30)->count();
        $resume_age_3       = Resume::where('birthdate', '>', $year - 49)->where('birthdate', '<', $year - 40)->count();
        $resume_age_4       = Resume::where('birthdate', '<', $year - 50)->count();
        $data['resume_age'] = [
            ['name' => '30岁以下', 'count' => $resume_age_1],
            ['name' => '30-39岁', 'count' => $resume_age_2],
            ['name' => '40-49岁', 'count' => $resume_age_3],
            ['name' => '50岁以上', 'count' => $resume_age_4],
        ];

        //求职者学历分布
        $resume_education_data  = Resume::select(['education', DB::raw('count(education) as count')])->groupBy('education')->get();
        $resume_education       = [];
        $resume_education_other = 0;
        foreach ($resume_education_data as $v) {
            switch ($v['education']) {
                case 69:
                    $resume_education[] = ['name' => '大专', 'count' => $v['count']];
                    break;
                case 70:
                    $resume_education[] = ['name' => '本科', 'count' => $v['count']];
                    break;
                case 71:
                    $resume_education[] = ['name' => '硕士', 'count' => $v['count']];
                    break;
                case 72:
                    $resume_education[] = ['name' => '博士', 'count' => $v['count']];
                    break;
                case 73:
                    $resume_education[] = ['name' => '博士后', 'count' => $v['count']];
                    break;
                default:
                    $resume_education_other += $v['count'];
                    break;
            }
        }
        $resume_education[]       = ['name' => '其他', 'count' => $resume_education_other];
        $data['resume_education'] = $resume_education;

        return response()->json($data);
    }

    /**
     * 聚合
     */
    public function agg()
    {
        $data = [];

        //求职者性别
        $resume_sex_data = Resume::select(['sex', DB::raw('count(sex) as count')])->groupBy('sex')->get();
        $resume_sex      = [];
        $resume_total    = 0;
        foreach ($resume_sex_data as $v) {
            $resume_total += $v['count'];
            switch ($v['sex']) {
                case 0:
                    $resume_sex[] = ['name' => '未知', 'count' => $v['count']];
                    break;
                case 1:
                    $resume_sex[] = ['name' => '男', 'count' => $v['count']];
                    break;
                case 2:
                    $resume_sex[] = ['name' => '女', 'count' => $v['count']];
                    break;
            }
        }
        $resume_sex[]       = ['name' => '总人数', 'count' => $resume_total];
        $data['resume_sex'] = $resume_sex;

        //求职者求职工种
        $resume_trade_1       = Resume::where('trade', 'like', "11%")->count();
        $resume_trade_2       = Resume::where('trade', 'like', "13%")->count();
        $resume_trade_3       = Resume::where('trade', 'like', "14%")->count();
        $resume_trade_4       = Resume::where('trade', 'like', "19%")->count();
        $resume_trade_5       = Resume::where('trade', 'like', "21%")->count();
        $resume_trade_6       = Resume::where('trade', 'like', "36%")->count();
        $resume_trade_7       = Resume::where('trade', 'like', "37%")->count();
        $data['resume_trade'] = [
            ['name' => '贸易/进出口', 'count' => $resume_trade_1],
            ['name' => '消费品(食/饮/烟酒)', 'count' => $resume_trade_2],
            ['name' => '服装/纺织/皮革', 'count' => $resume_trade_3],
            ['name' => '制药/生物工程', 'count' => $resume_trade_4],
            ['name' => '医疗设备/器械', 'count' => $resume_trade_5],
            ['name' => '酒店/旅游', 'count' => $resume_trade_6],
            ['name' => '交通/运输/物流', 'count' => $resume_trade_7],
            ['name' => '其他', 'count' => ($resume_total - $resume_trade_1 - $resume_trade_2 - $resume_trade_3 - $resume_trade_4 - $resume_trade_5 - $resume_trade_6 - $resume_trade_7)],
        ];

        //求职者年龄分布
        $year               = date('Y');
        $resume_age_1       = Resume::where('birthdate', '>', $year - 30)->count();
        $resume_age_2       = Resume::where('birthdate', '>', $year - 39)->where('birthdate', '<', $year - 30)->count();
        $resume_age_3       = Resume::where('birthdate', '>', $year - 49)->where('birthdate', '<', $year - 40)->count();
        $resume_age_4       = Resume::where('birthdate', '<', $year - 50)->count();
        $data['resume_age'] = [
            ['name' => '30岁以下', 'count' => $resume_age_1],
            ['name' => '30-39岁', 'count' => $resume_age_2],
            ['name' => '40-49岁', 'count' => $resume_age_3],
            ['name' => '50岁以上', 'count' => $resume_age_4],
        ];

        //求职者学历分布
        $resume_education_data  = Resume::select(['education', DB::raw('count(education) as count')])->groupBy('education')->get();
        $resume_education       = [];
        $resume_education_other = 0;
        foreach ($resume_education_data as $v) {
            switch ($v['education']) {
                case 69:
                    $resume_education[] = ['name' => '大专', 'count' => $v['count']];
                    break;
                case 70:
                    $resume_education[] = ['name' => '本科', 'count' => $v['count']];
                    break;
                case 71:
                    $resume_education[] = ['name' => '硕士', 'count' => $v['count']];
                    break;
                case 72:
                    $resume_education[] = ['name' => '博士', 'count' => $v['count']];
                    break;
                case 73:
                    $resume_education[] = ['name' => '博士后', 'count' => $v['count']];
                    break;
                default:
                    $resume_education_other += $v['count'];
                    break;
            }
        }
        $resume_education[]       = ['name' => '其他', 'count' => $resume_education_other];
        $data['resume_education'] = $resume_education;

        //招考数据
        $data['recruit_count'] = Recruit::count();
        $data['recruit_post']  = RecruitPost::count();
        $data['recruit_apply'] = RecruitAppointInfo::count();

        //学历和年龄
        $recruit_apply_data = RecruitAppointInfo::select(['birthday','education'])->get();
        $recruit_education = [
            '专科' => ['name' => '专科', 'count' => 0],
            '本科' => ['name' => '本科', 'count' => 0],
            '硕士' => ['name' => '硕士', 'count' => 0],
            '博士' => ['name' => '博士', 'count' => 0],
            '其他' => ['name' => '其他', 'count' => 0],
        ];
        $recruit_age       = [
            '30岁及以下' => ['name' => '30岁及以下', 'count' => 0],
            '30到35岁' => ['name' => '30到35岁', 'count' => 0],
            '35到40岁' => ['name' => '35到40岁', 'count' => 0],
            '40岁以上'  => ['name' => '40岁以上', 'count' => 0],
        ];
        foreach ($recruit_apply_data as $v) {
            if (!empty($recruit_education[$v['education']])) {
                $recruit_education[$v['education']]['count']++;
            } else {
                $recruit_education['其他']['count']++;
            }

            $birth = mb_substr($v['birthday'], 0, 4);
            if ($birth >= ($year - 30)) {
                $recruit_age['30岁及以下']['count']++;
            } elseif ($birth >= ($year - 35) && $birth < ($year - 30)) {
                $recruit_age['30到35岁']['count']++;
            } elseif ($birth >= ($year - 40) && $birth < ($year - 35)) {
                $recruit_age['35到40岁']['count']++;
            } else {
                $recruit_age['40岁以上']['count']++;
            }
        }

        $data['recruit_education'] = array_values($recruit_education);
        $data['recruit_age'] = array_values($recruit_age);

        return response()->json($data);
    }

    private function _valueToKey($arr, $key)
    {
        $res = [];
        foreach ($arr as $v) {
            $res[$v[$key]] = $v;
        }

        return $res;
    }
}