nanguaboss
200 lines
9.5 KiB
PHP
200 lines
9.5 KiB
PHP
<?php
|
||
/**
|
||
* Dashboard 首页数据接口 GET /api/dashboard/stats.php
|
||
* 返回：8 个指标卡 + 区域分布（国外=世界地图按国家 / 国内=中国地图按省份，企业/人员双维度）
|
||
* + 行业分布（企业/人员）+ 需求关键词（简化）
|
||
*/
|
||
require_once __DIR__ . '/../common/db.php';
|
||
require_once __DIR__ . '/../common/response.php';
|
||
require_once __DIR__ . '/../common/auth.php';
|
||
|
||
checkPermission('dashboard');
|
||
|
||
$pdo = DB::getInstance()->getPdo();
|
||
|
||
// ---------- 8 个指标卡 ----------
|
||
$sqlMetrics = [
|
||
'company_total' => "SELECT COUNT(*) AS c FROM companies WHERE is_active = 1",
|
||
'person_total' => "SELECT COUNT(*) AS c FROM persons WHERE is_active = 1",
|
||
'company_new' => "SELECT COUNT(*) AS c FROM companies WHERE is_active = 1 AND DATE(created_at) = CURDATE() - INTERVAL 1 DAY",
|
||
'person_new' => "SELECT COUNT(*) AS c FROM persons WHERE is_active = 1 AND DATE(created_at) = CURDATE() - INTERVAL 1 DAY",
|
||
'preliminary' => "SELECT
|
||
(SELECT COUNT(*) FROM preliminary_data WHERE is_active = 1 AND converted_id IS NULL)
|
||
+ (SELECT COUNT(*) FROM companies WHERE is_incomplete = 1)
|
||
+ (SELECT COUNT(*) FROM persons WHERE is_incomplete = 1)
|
||
+ (SELECT COUNT(*) FROM social_accounts WHERE is_incomplete = 1)
|
||
+ (SELECT COUNT(*) FROM media_commercial_attributes WHERE is_incomplete = 1) AS c",
|
||
'industry_count' => "SELECT COUNT(DISTINCT industry) AS c FROM companies WHERE industry IS NOT NULL AND industry <> ''",
|
||
'manufacturer' => "SELECT COUNT(*) AS c FROM companies WHERE business_role = 'manufacturer' AND is_active = 1",
|
||
'integrator' => "SELECT COUNT(*) AS c FROM companies WHERE business_role IN ('integrator','distributor') AND is_active = 1",
|
||
'media_resource' => "SELECT COUNT(*) AS c FROM media_commercial_attributes",
|
||
];
|
||
$metrics = [];
|
||
foreach ($sqlMetrics as $key => $sql) {
|
||
$metrics[$key] = (int)$pdo->query($sql)->fetchColumn();
|
||
}
|
||
|
||
// ---------- 区域分布 ----------
|
||
// 国外（世界地图）：企业按 country，人员按 nationality
|
||
$regionWorldCompany = $pdo->query(
|
||
"SELECT country AS name, COUNT(*) AS value
|
||
FROM companies
|
||
WHERE country IS NOT NULL AND country <> '' AND is_active = 1
|
||
GROUP BY country ORDER BY value DESC LIMIT 60"
|
||
)->fetchAll();
|
||
|
||
$regionWorldPerson = $pdo->query(
|
||
"SELECT nationality AS name, COUNT(*) AS value
|
||
FROM persons
|
||
WHERE nationality IS NOT NULL AND nationality <> '' AND is_active = 1
|
||
GROUP BY nationality ORDER BY value DESC LIMIT 60"
|
||
)->fetchAll();
|
||
if (!$regionWorldPerson) {
|
||
$regionWorldPerson = $pdo->query(
|
||
"SELECT work_location AS name, COUNT(*) AS value
|
||
FROM persons WHERE work_location IS NOT NULL AND work_location <> '' AND is_active = 1
|
||
GROUP BY work_location ORDER BY value DESC LIMIT 60"
|
||
)->fetchAll();
|
||
}
|
||
|
||
// 国内（中国地图）：按省份统计（企业取注册地址/国家；人员取工作所在地/家乡）
|
||
$regionChinaCompany = provinceCount(
|
||
$pdo->query("SELECT country, address FROM companies WHERE is_active = 1")->fetchAll(),
|
||
['country', 'address']
|
||
);
|
||
$regionChinaPerson = provinceCount(
|
||
$pdo->query("SELECT work_location, hometown FROM persons WHERE is_active = 1")->fetchAll(),
|
||
['work_location', 'hometown']
|
||
);
|
||
|
||
// ---------- 行业分布（企业直接取 industry；人员经工作经历关联到企业行业） ----------
|
||
$industryCompany = $pdo->query(
|
||
"SELECT industry AS name, COUNT(*) AS value
|
||
FROM companies
|
||
WHERE industry IS NOT NULL AND industry <> '' AND is_active = 1
|
||
GROUP BY industry ORDER BY value DESC LIMIT 20"
|
||
)->fetchAll();
|
||
|
||
$industryPerson = $pdo->query(
|
||
"SELECT c.industry AS name, COUNT(DISTINCT pwe.person_id) AS value
|
||
FROM person_work_experiences pwe
|
||
LEFT JOIN companies c ON c.id = pwe.company_id
|
||
WHERE pwe.is_active = 1 AND c.industry IS NOT NULL AND c.industry <> '' AND c.is_active = 1
|
||
GROUP BY c.industry ORDER BY value DESC LIMIT 20"
|
||
)->fetchAll();
|
||
|
||
// ---------- 需求关键词（简化：从 company_needs.description 提取高频词，占位） ----------
|
||
$needRows = $pdo->query(
|
||
"SELECT description FROM company_needs WHERE is_valid = 1 AND description IS NOT NULL AND description <> '' LIMIT 200"
|
||
)->fetchAll();
|
||
$keywordMap = [];
|
||
foreach ($needRows as $row) {
|
||
$parts = preg_split('/[，。、,.;；:：\s\/\|\-\+\/]+/u', $row['description']);
|
||
foreach ($parts as $part) {
|
||
$part = trim($part);
|
||
if (mb_strlen($part, 'utf-8') >= 2 && mb_strlen($part, 'utf-8') <= 12) {
|
||
$keywordMap[$part] = ($keywordMap[$part] ?? 0) + 1;
|
||
}
|
||
}
|
||
}
|
||
arsort($keywordMap);
|
||
$wordCloud = [];
|
||
$i = 0;
|
||
foreach ($keywordMap as $word => $count) {
|
||
if ($i++ >= 50) break;
|
||
$wordCloud[] = ['name' => $word, 'value' => $count];
|
||
}
|
||
|
||
Response::success([
|
||
'metrics' => $metrics,
|
||
'region' => [
|
||
'company' => ['world' => $regionWorldCompany, 'china' => $regionChinaCompany],
|
||
'person' => ['world' => $regionWorldPerson, 'china' => $regionChinaPerson],
|
||
],
|
||
'industry' => ['company' => $industryCompany, 'person' => $industryPerson],
|
||
'wordcloud' => $wordCloud,
|
||
]);
|
||
|
||
/**
|
||
* 从一组记录的文本字段中统计中国省份分布
|
||
* @param array $rows 记录列表（关联数组）
|
||
* @param array $fields 用于匹配的字段名
|
||
* @return array [{name:省份, value:数量}]
|
||
*/
|
||
function provinceCount($rows, $fields)
|
||
{
|
||
// 34 个省级行政区（含直辖市/自治区/特别行政区），匹配顺序：长名称优先
|
||
$provinces = [
|
||
'内蒙古自治区', '广西壮族自治区', '西藏自治区', '宁夏回族自治区', '新疆维吾尔自治区',
|
||
'黑龙江省', '河北省', '山西省', '辽宁省', '吉林省', '江苏省', '浙江省', '安徽省',
|
||
'福建省', '江西省', '山东省', '河南省', '湖北省', '湖南省', '广东省', '海南省',
|
||
'四川省', '贵州省', '云南省', '陕西省', '甘肃省', '青海省',
|
||
'北京', '天津', '上海', '重庆', '台湾', '香港', '澳门',
|
||
];
|
||
// 常见城市 -> 省份（地址/工作地只有城市名时使用）
|
||
$city2prov = [
|
||
'深圳' => '广东', '广州' => '广东', '东莞' => '广东', '佛山' => '广东',
|
||
'珠海' => '广东', '中山' => '广东', '惠州' => '广东', '汕头' => '广东', '江门' => '广东',
|
||
'杭州' => '浙江', '宁波' => '浙江', '温州' => '浙江', '嘉兴' => '浙江', '绍兴' => '浙江',
|
||
'南京' => '江苏', '苏州' => '江苏', '无锡' => '江苏', '常州' => '江苏', '南通' => '江苏',
|
||
'成都' => '四川', '绵阳' => '四川', '武汉' => '湖北', '宜昌' => '湖北', '长沙' => '湖南',
|
||
'郑州' => '河南', '洛阳' => '河南', '西安' => '陕西', '青岛' => '山东', '济南' => '山东',
|
||
'烟台' => '山东', '潍坊' => '山东', '大连' => '辽宁', '沈阳' => '辽宁', '哈尔滨' => '黑龙江',
|
||
'长春' => '吉林', '合肥' => '安徽', '芜湖' => '安徽', '福州' => '福建', '厦门' => '福建',
|
||
'泉州' => '福建', '昆明' => '云南', '贵阳' => '贵州', '南昌' => '江西', '石家庄' => '河北',
|
||
'太原' => '山西', '兰州' => '甘肃', '银川' => '宁夏', '西宁' => '青海', '乌鲁木齐' => '新疆',
|
||
'呼和浩特' => '内蒙古', '南宁' => '广西', '海口' => '海南', '三亚' => '海南',
|
||
'拉萨' => '西藏', '台北' => '台湾', '高雄' => '台湾', '香港' => '香港', '澳门' => '澳门',
|
||
];
|
||
|
||
$counts = [];
|
||
foreach ($rows as $row) {
|
||
$text = '';
|
||
foreach ($fields as $f) {
|
||
if (!empty($row[$f])) {
|
||
$text .= $row[$f] . ' ';
|
||
}
|
||
}
|
||
$province = null;
|
||
foreach ($provinces as $p) {
|
||
if (mb_strpos($text, $p) !== false) {
|
||
$province = $p;
|
||
break;
|
||
}
|
||
}
|
||
if ($province === null) {
|
||
foreach ($city2prov as $city => $p) {
|
||
if (mb_strpos($text, $city) !== false) {
|
||
$province = $p;
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
// 长名 -> 短名（与中国地图 echarts china.js 的省份名保持一致）
|
||
if ($province !== null) {
|
||
$shortMap = [
|
||
'内蒙古自治区' => '内蒙古', '广西壮族自治区' => '广西', '西藏自治区' => '西藏',
|
||
'宁夏回族自治区' => '宁夏', '新疆维吾尔自治区' => '新疆',
|
||
'黑龙江省' => '黑龙江', '河北省' => '河北', '山西省' => '山西', '辽宁省' => '辽宁',
|
||
'吉林省' => '吉林', '江苏省' => '江苏', '浙江省' => '浙江', '安徽省' => '安徽',
|
||
'福建省' => '福建', '江西省' => '江西', '山东省' => '山东', '河南省' => '河南',
|
||
'湖北省' => '湖北', '湖南省' => '湖南', '广东省' => '广东', '海南省' => '海南',
|
||
'四川省' => '四川', '贵州省' => '贵州', '云南省' => '云南', '陕西省' => '陕西',
|
||
'甘肃省' => '甘肃', '青海省' => '青海', '台湾省' => '台湾',
|
||
];
|
||
if (isset($shortMap[$province])) {
|
||
$province = $shortMap[$province];
|
||
}
|
||
}
|
||
if ($province !== null) {
|
||
$counts[$province] = ($counts[$province] ?? 0) + 1;
|
||
}
|
||
}
|
||
arsort($counts);
|
||
$result = [];
|
||
foreach ($counts as $name => $value) {
|
||
$result[] = ['name' => $name, 'value' => $value];
|
||
}
|
||
return $result;
|
||
}
|