dashboard.js 3.0 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697
  1. const express = require('express');
  2. const router = express.Router();
  3. const { pool } = require('../config/db');
  4. // 获取仪表盘统计数据
  5. router.get('/stats', async (req, res) => {
  6. try {
  7. // 获取职位统计
  8. const [jobsStats] = await pool.query(`
  9. SELECT
  10. COUNT(*) AS totalJobs,
  11. SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS activeJobs,
  12. SUM(pending_resumes) AS totalPendingResumes,
  13. SUM(passed_resumes) AS totalPassedResumes
  14. FROM jobs
  15. WHERE status != 'deleted'
  16. `);
  17. // 获取候选人统计
  18. const [candidatesStats] = await pool.query(`
  19. SELECT
  20. COUNT(*) AS totalCandidates,
  21. SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pendingCandidates,
  22. SUM(CASE WHEN status = 'passed' THEN 1 ELSE 0 END) AS passedCandidates,
  23. SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) AS rejectedCandidates,
  24. SUM(CASE WHEN status = 'interviewed' THEN 1 ELSE 0 END) AS interviewedCandidates,
  25. AVG(match_score) AS averageMatchScore
  26. FROM candidates
  27. `);
  28. // 计算通过率
  29. const passRate = candidatesStats[0].totalCandidates > 0
  30. ? Math.round(
  31. (candidatesStats[0].passedCandidates / candidatesStats[0].totalCandidates) * 100
  32. )
  33. : 0;
  34. res.json({
  35. ...jobsStats[0],
  36. ...candidatesStats[0],
  37. passRate,
  38. averageMatchScore: Math.round(candidatesStats[0].averageMatchScore || 0)
  39. });
  40. } catch (error) {
  41. console.error('Get dashboard stats error:', error);
  42. res.status(500).json({ error: 'Server error' });
  43. }
  44. });
  45. // 获取最近活动
  46. router.get('/recent-activity', async (req, res) => {
  47. try {
  48. // 合并候选人状态变更和面试安排作为最近活动
  49. const [activities] = await pool.query(`
  50. (
  51. SELECT
  52. csh.id,
  53. c.name AS candidate_name,
  54. j.title AS job_title,
  55. CONCAT(csh.old_status, ' → ', csh.new_status) AS activity_type,
  56. 'status_change' AS activity_category,
  57. csh.change_time AS activity_time
  58. FROM candidate_status_history csh
  59. JOIN candidates c ON csh.candidate_id = c.id
  60. JOIN jobs j ON c.job_id = j.id
  61. ORDER BY csh.change_time DESC
  62. LIMIT 5
  63. )
  64. UNION ALL
  65. (
  66. SELECT
  67. ci.id,
  68. c.name AS candidate_name,
  69. j.title AS job_title,
  70. CONCAT('面试安排: ', i.title) AS activity_type,
  71. 'interview' AS activity_category,
  72. ci.interview_time AS activity_time
  73. FROM candidate_interviews ci
  74. JOIN candidates c ON ci.candidate_id = c.id
  75. JOIN jobs j ON c.job_id = j.id
  76. JOIN interviews i ON ci.interview_id = i.id
  77. ORDER BY ci.interview_time DESC
  78. LIMIT 5
  79. )
  80. ORDER BY activity_time DESC
  81. LIMIT 10
  82. `);
  83. res.json(activities);
  84. } catch (error) {
  85. console.error('Get recent activity error:', error);
  86. res.status(500).json({ error: 'Server error' });
  87. }
  88. });
  89. module.exports = router;