Files
TK-FB-Project/api.hyungi.net/models/WorkAnalysis.js
Hyungi Ahn 2b1c7bfb88 feat: 다수 기능 개선 - 순찰, 출근, 작업분석, 모바일 UI 등
- 순찰/점검 기능 개선 (zone-detail 페이지 추가)
- 출근/근태 시스템 개선 (연차 조회, 근무현황)
- 작업분석 대분류 그룹화 및 마이그레이션 스크립트
- 모바일 네비게이션 UI 추가
- NAS 배포 도구 및 문서 추가

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
2026-02-09 14:41:01 +09:00

519 lines
22 KiB
JavaScript

// models/WorkAnalysis.js - 향상된 버전
class WorkAnalysis {
constructor(db) {
this.db = db;
}
// 기본 통계 조회
async getBasicStats(startDate, endDate) {
const query = `
SELECT
COALESCE(SUM(work_hours), 0) as total_hours,
COUNT(*) as total_reports,
COUNT(DISTINCT project_id) as active_projects,
COUNT(DISTINCT worker_id) as active_workers,
SUM(CASE WHEN work_status_id = 2 THEN 1 ELSE 0 END) as error_reports,
ROUND(AVG(work_hours), 2) as avg_hours_per_report
FROM daily_work_reports
WHERE report_date BETWEEN ? AND ?
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate]);
const stats = results[0];
const errorRate = stats.total_reports > 0
? (stats.error_reports / stats.total_reports) * 100
: 0;
return {
totalHours: parseFloat(stats.total_hours) || 0,
totalReports: parseInt(stats.total_reports) || 0,
activeProjects: parseInt(stats.active_projects) || 0,
activeworkers: parseInt(stats.active_workers) || 0,
errorRate: parseFloat(errorRate.toFixed(2)) || 0,
avgHoursPerReport: parseFloat(stats.avg_hours_per_report) || 0
};
} catch (error) {
throw new Error(`기본 통계 조회 실패: ${error.message}`);
}
}
// 일별 작업시간 추이
async getDailyTrend(startDate, endDate) {
const query = `
SELECT
report_date as date,
SUM(work_hours) as hours,
COUNT(*) as reports,
COUNT(DISTINCT worker_id) as workers,
SUM(CASE WHEN work_status_id = 2 THEN 1 ELSE 0 END) as errors
FROM daily_work_reports
WHERE report_date BETWEEN ? AND ?
GROUP BY report_date
ORDER BY report_date
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate]);
return results.map(row => ({
date: row.date,
hours: parseFloat(row.hours) || 0,
reports: parseInt(row.reports) || 0,
workers: parseInt(row.workers) || 0,
errors: parseInt(row.errors) || 0
}));
} catch (error) {
throw new Error(`일별 추이 조회 실패: ${error.message}`);
}
}
// 작업자별 통계
async getWorkerStats(startDate, endDate) {
const query = `
SELECT
dwr.worker_id,
w.worker_name,
SUM(dwr.work_hours) as totalHours,
COUNT(*) as totalReports,
ROUND(AVG(dwr.work_hours), 2) as avgHours,
COUNT(DISTINCT dwr.project_id) as projectCount,
SUM(CASE WHEN dwr.work_status_id = 2 THEN 1 ELSE 0 END) as errorCount,
COUNT(DISTINCT dwr.report_date) as workingDays
FROM daily_work_reports dwr
LEFT JOIN workers w ON dwr.worker_id = w.worker_id
WHERE dwr.report_date BETWEEN ? AND ?
GROUP BY dwr.worker_id, w.worker_name
ORDER BY totalHours DESC
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate]);
return results.map(row => ({
worker_id: row.worker_id,
worker_name: row.worker_name || `작업자 ${row.worker_id}`,
totalHours: parseFloat(row.totalHours) || 0,
totalReports: parseInt(row.totalReports) || 0,
avgHours: parseFloat(row.avgHours) || 0,
projectCount: parseInt(row.projectCount) || 0,
errorCount: parseInt(row.errorCount) || 0,
workingDays: parseInt(row.workingDays) || 0,
errorRate: row.totalReports > 0 ? parseFloat(((row.errorCount / row.totalReports) * 100).toFixed(2)) : 0
}));
} catch (error) {
throw new Error(`작업자별 통계 조회 실패: ${error.message}`);
}
}
// 프로젝트별 통계
async getProjectStats(startDate, endDate) {
const query = `
SELECT
dwr.project_id,
p.project_name,
SUM(dwr.work_hours) as totalHours,
COUNT(*) as totalReports,
COUNT(DISTINCT dwr.worker_id) as workerCount,
ROUND(AVG(dwr.work_hours), 2) as avgHours,
SUM(CASE WHEN dwr.work_status_id = 2 THEN 1 ELSE 0 END) as errorCount,
COUNT(DISTINCT dwr.report_date) as activeDays
FROM daily_work_reports dwr
LEFT JOIN projects p ON dwr.project_id = p.project_id
WHERE dwr.report_date BETWEEN ? AND ?
GROUP BY dwr.project_id, p.project_name
ORDER BY totalHours DESC
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate]);
return results.map(row => ({
project_id: row.project_id,
project_name: row.project_name || `프로젝트 ${row.project_id}`,
totalHours: parseFloat(row.totalHours) || 0,
totalReports: parseInt(row.totalReports) || 0,
workerCount: parseInt(row.workerCount) || 0,
avgHours: parseFloat(row.avgHours) || 0,
errorCount: parseInt(row.errorCount) || 0,
activeDays: parseInt(row.activeDays) || 0,
errorRate: row.totalReports > 0 ? parseFloat(((row.errorCount / row.totalReports) * 100).toFixed(2)) : 0
}));
} catch (error) {
throw new Error(`프로젝트별 통계 조회 실패: ${error.message}`);
}
}
// 작업유형별 통계
async getWorkTypeStats(startDate, endDate) {
const query = `
SELECT
dwr.work_type_id,
wt.name as work_type_name,
SUM(dwr.work_hours) as totalHours,
COUNT(*) as totalReports,
ROUND(AVG(dwr.work_hours), 2) as avgHours,
COUNT(DISTINCT dwr.worker_id) as workerCount,
SUM(CASE WHEN dwr.work_status_id = 2 THEN 1 ELSE 0 END) as errorCount,
COUNT(DISTINCT dwr.project_id) as projectCount
FROM daily_work_reports dwr
LEFT JOIN work_types wt ON dwr.work_type_id = wt.id
WHERE dwr.report_date BETWEEN ? AND ?
GROUP BY dwr.work_type_id, wt.name
ORDER BY totalHours DESC
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate]);
return results.map(row => ({
work_type_id: row.work_type_id,
work_type_name: row.work_type_name || `작업유형 ${row.work_type_id}`,
totalHours: parseFloat(row.totalHours) || 0,
totalReports: parseInt(row.totalReports) || 0,
avgHours: parseFloat(row.avgHours) || 0,
workerCount: parseInt(row.workerCount) || 0,
errorCount: parseInt(row.errorCount) || 0,
projectCount: parseInt(row.projectCount) || 0,
errorRate: row.totalReports > 0 ? parseFloat(((row.errorCount / row.totalReports) * 100).toFixed(2)) : 0
}));
} catch (error) {
throw new Error(`작업유형별 통계 조회 실패: ${error.message}`);
}
}
// 최근 작업 현황
async getRecentWork(startDate, endDate, limit = 50) {
// work_type_id 컬럼에는 task_id가 저장됨 (tasks 테이블 우선 조회)
// task_id로 매칭되면 해당 task의 work_type_id로 공정(대분류) 조회
// 매칭 안 되면 직접 work_types 테이블 조회 (레거시 데이터 호환)
// error_type_id는 issue_report_items 테이블 참조 (issue-categories.html에서 관리)
const query = `
SELECT
dwr.id,
dwr.report_date,
dwr.worker_id,
w.worker_name,
dwr.project_id,
p.project_name,
p.job_no,
dwr.work_type_id as original_work_type_id,
COALESCE(
CASE WHEN t.task_id IS NOT NULL THEN t.work_type_id ELSE NULL END,
wt.id
) as work_type_id,
COALESCE(
CASE WHEN t.task_id IS NOT NULL THEN wt2.name ELSE NULL END,
wt.name
) as work_type_name,
t.task_name as task_name,
dwr.work_status_id,
wst.name as work_status_name,
dwr.error_type_id,
iri.item_name as error_type_name,
irc.category_name as error_category_name,
dwr.work_hours,
dwr.created_by,
u.name as created_by_name,
dwr.created_at
FROM daily_work_reports dwr
LEFT JOIN workers w ON dwr.worker_id = w.worker_id
LEFT JOIN projects p ON dwr.project_id = p.project_id
LEFT JOIN work_types wt ON dwr.work_type_id = wt.id
LEFT JOIN tasks t ON dwr.work_type_id = t.task_id
LEFT JOIN work_types wt2 ON t.work_type_id = wt2.id
LEFT JOIN work_status_types wst ON dwr.work_status_id = wst.id
LEFT JOIN issue_report_items iri ON dwr.error_type_id = iri.item_id
LEFT JOIN issue_report_categories irc ON iri.category_id = irc.category_id
LEFT JOIN users u ON dwr.created_by = u.user_id
WHERE dwr.report_date BETWEEN ? AND ?
ORDER BY dwr.created_at DESC
LIMIT ?
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate, parseInt(limit)]);
return results.map(row => ({
id: row.id,
report_date: row.report_date,
worker_id: row.worker_id,
worker_name: row.worker_name || `작업자 ${row.worker_id}`,
project_id: row.project_id,
project_name: row.project_name || `프로젝트 ${row.project_id}`,
job_no: row.job_no || 'N/A',
work_type_id: row.work_type_id,
work_type_name: row.work_type_name || `작업유형 ${row.original_work_type_id}`,
task_name: row.task_name || null,
work_status_id: row.work_status_id,
work_status_name: row.work_status_name || '정상',
error_type_id: row.error_type_id,
error_type_name: row.error_type_name || null,
error_category_name: row.error_category_name || null,
work_hours: parseFloat(row.work_hours) || 0,
created_by: row.created_by,
created_by_name: row.created_by_name || '미지정',
created_at: row.created_at
}));
} catch (error) {
throw new Error(`최근 작업 현황 조회 실패: ${error.message}`);
}
}
// 요일별 패턴 분석
async getWeekdayPattern(startDate, endDate) {
const query = `
SELECT
DAYOFWEEK(report_date) as day_of_week,
CASE DAYOFWEEK(report_date)
WHEN 1 THEN '일요일'
WHEN 2 THEN '월요일'
WHEN 3 THEN '화요일'
WHEN 4 THEN '수요일'
WHEN 5 THEN '목요일'
WHEN 6 THEN '금요일'
WHEN 7 THEN '토요일'
END as day_name,
SUM(work_hours) as total_hours,
COUNT(*) as total_reports,
ROUND(AVG(work_hours), 2) as avg_hours,
COUNT(DISTINCT worker_id) as active_workers
FROM daily_work_reports
WHERE report_date BETWEEN ? AND ?
GROUP BY DAYOFWEEK(report_date)
ORDER BY day_of_week
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate]);
return results.map(row => ({
dayOfWeek: row.day_of_week,
dayName: row.day_name,
totalHours: parseFloat(row.total_hours) || 0,
totalReports: parseInt(row.total_reports) || 0,
avgHours: parseFloat(row.avg_hours) || 0,
activeworkers: parseInt(row.active_workers) || 0
}));
} catch (error) {
throw new Error(`요일별 패턴 분석 실패: ${error.message}`);
}
}
// 에러 분석
// error_type_id는 issue_report_items 테이블 참조 (issue-categories.html에서 관리)
async getErrorAnalysis(startDate, endDate) {
const query = `
SELECT
dwr.error_type_id,
iri.item_name as error_type_name,
irc.category_name as error_category_name,
COUNT(*) as error_count,
SUM(dwr.work_hours) as total_hours,
COUNT(DISTINCT dwr.worker_id) as affected_workers,
COUNT(DISTINCT dwr.project_id) as affected_projects
FROM daily_work_reports dwr
LEFT JOIN issue_report_items iri ON dwr.error_type_id = iri.item_id
LEFT JOIN issue_report_categories irc ON iri.category_id = irc.category_id
WHERE dwr.report_date BETWEEN ? AND ?
AND dwr.work_status_id = 2
GROUP BY dwr.error_type_id, iri.item_name, irc.category_name
ORDER BY error_count DESC
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate]);
return results.map(row => ({
error_type_id: row.error_type_id,
error_type_name: row.error_type_name || `에러유형 ${row.error_type_id}`,
error_category_name: row.error_category_name || null,
errorCount: parseInt(row.error_count) || 0,
totalHours: parseFloat(row.total_hours) || 0,
affectedworkers: parseInt(row.affected_workers) || 0,
affectedProjects: parseInt(row.affected_projects) || 0
}));
} catch (error) {
throw new Error(`에러 분석 실패: ${error.message}`);
}
}
// 월별 비교 분석
async getMonthlyComparison(year) {
const query = `
SELECT
MONTH(report_date) as month,
MONTHNAME(report_date) as month_name,
SUM(work_hours) as total_hours,
COUNT(*) as total_reports,
COUNT(DISTINCT worker_id) as active_workers,
COUNT(DISTINCT project_id) as active_projects,
SUM(CASE WHEN work_status_id = 2 THEN 1 ELSE 0 END) as error_count
FROM daily_work_reports
WHERE YEAR(report_date) = ?
GROUP BY MONTH(report_date), MONTHNAME(report_date)
ORDER BY month
`;
try {
const [results] = await this.db.execute(query, [year]);
return results.map(row => ({
month: row.month,
monthName: row.month_name,
totalHours: parseFloat(row.total_hours) || 0,
totalReports: parseInt(row.total_reports) || 0,
activeworkers: parseInt(row.active_workers) || 0,
activeProjects: parseInt(row.active_projects) || 0,
errorCount: parseInt(row.error_count) || 0,
errorRate: row.total_reports > 0 ? parseFloat(((row.error_count / row.total_reports) * 100).toFixed(2)) : 0
}));
} catch (error) {
throw new Error(`월별 비교 분석 실패: ${error.message}`);
}
}
// 작업자별 전문분야 분석
async getWorkerSpecialization(startDate, endDate) {
const query = `
SELECT
dwr.worker_id,
w.worker_name,
dwr.work_type_id,
wt.name as work_type_name,
dwr.project_id,
p.project_name,
SUM(dwr.work_hours) as totalHours,
COUNT(*) as totalReports,
ROUND((SUM(dwr.work_hours) / (
SELECT SUM(work_hours)
FROM daily_work_reports
WHERE worker_id = dwr.worker_id
AND report_date BETWEEN ? AND ?
)) * 100, 2) as percentage
FROM daily_work_reports dwr
LEFT JOIN workers w ON dwr.worker_id = w.worker_id
LEFT JOIN work_types wt ON dwr.work_type_id = wt.id
LEFT JOIN projects p ON dwr.project_id = p.project_id
WHERE dwr.report_date BETWEEN ? AND ?
GROUP BY dwr.worker_id, w.worker_name, dwr.work_type_id, wt.name, dwr.project_id, p.project_name
HAVING totalHours > 0
ORDER BY dwr.worker_id, totalHours DESC
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate, startDate, endDate]);
return results.map(row => ({
worker_id: row.worker_id,
worker_name: row.worker_name || `작업자 ${row.worker_id}`,
work_type_id: row.work_type_id,
work_type_name: row.work_type_name || `작업유형 ${row.work_type_id}`,
project_id: row.project_id,
project_name: row.project_name || `프로젝트 ${row.project_id}`,
totalHours: parseFloat(row.totalHours) || 0,
totalReports: parseInt(row.totalReports) || 0,
percentage: parseFloat(row.percentage) || 0
}));
} catch (error) {
throw new Error(`작업자별 전문분야 분석 실패: ${error.message}`);
}
}
// 대시보드용 종합 데이터
async getDashboardData(startDate, endDate) {
try {
// 병렬로 모든 데이터 조회
const [
stats,
dailyTrend,
workerStats,
projectStats,
workTypeStats,
recentWork
] = await Promise.all([
this.getBasicStats(startDate, endDate),
this.getDailyTrend(startDate, endDate),
this.getWorkerStats(startDate, endDate),
this.getProjectStats(startDate, endDate),
this.getWorkTypeStats(startDate, endDate),
this.getRecentWork(startDate, endDate, 20)
]);
return {
stats,
dailyTrend,
workerStats,
projectStats,
workTypeStats,
recentWork,
metadata: {
period: `${startDate} ~ ${endDate}`,
timestamp: new Date().toISOString()
}
};
} catch (error) {
throw new Error(`대시보드 데이터 조회 실패: ${error.message}`);
}
}
// 프로젝트별-작업별 시간 분석용 데이터 조회 (공정/대분류 기준)
async getProjectWorkTypeRawData(startDate, endDate) {
// work_type_id 컬럼에는 task_id가 저장됨 (tasks 테이블 우선 조회)
// task_id로 매칭되면 해당 task의 work_type_id로 공정 조회
// 매칭 안 되면 직접 work_types 조회 (레거시 데이터 호환)
const query = `
SELECT
COALESCE(p.project_id, dwr.project_id) as project_id,
COALESCE(p.project_name, CONCAT('프로젝트 ', dwr.project_id)) as project_name,
COALESCE(p.job_no, 'N/A') as job_no,
COALESCE(
CASE WHEN t.task_id IS NOT NULL THEN t.work_type_id ELSE NULL END,
wt.id
) as work_type_id,
COALESCE(
CASE WHEN t.task_id IS NOT NULL THEN wt2.name ELSE NULL END,
wt.name,
CONCAT('작업유형 ', dwr.work_type_id)
) as work_type_name,
-- 총 시간
SUM(dwr.work_hours) as total_hours,
-- 정규 시간 (work_status_id = 1)
SUM(CASE WHEN dwr.work_status_id = 1 THEN dwr.work_hours ELSE 0 END) as regular_hours,
-- 에러 시간 (work_status_id = 2)
SUM(CASE WHEN dwr.work_status_id = 2 THEN dwr.work_hours ELSE 0 END) as error_hours,
-- 작업 건수
COUNT(*) as total_reports,
COUNT(CASE WHEN dwr.work_status_id = 1 THEN 1 END) as regular_reports,
COUNT(CASE WHEN dwr.work_status_id = 2 THEN 1 END) as error_reports,
-- 에러율 계산
ROUND(
(SUM(CASE WHEN dwr.work_status_id = 2 THEN dwr.work_hours ELSE 0 END) /
SUM(dwr.work_hours)) * 100, 2
) as error_rate_percent
FROM daily_work_reports dwr
LEFT JOIN projects p ON dwr.project_id = p.project_id
LEFT JOIN work_types wt ON dwr.work_type_id = wt.id
LEFT JOIN tasks t ON dwr.work_type_id = t.task_id
LEFT JOIN work_types wt2 ON t.work_type_id = wt2.id
WHERE dwr.report_date BETWEEN ? AND ?
GROUP BY dwr.project_id, p.project_name, p.job_no,
COALESCE(
CASE WHEN t.task_id IS NOT NULL THEN t.work_type_id ELSE NULL END,
wt.id
),
COALESCE(
CASE WHEN t.task_id IS NOT NULL THEN wt2.name ELSE NULL END,
wt.name
)
ORDER BY p.project_name, work_type_name
`;
try {
const [results] = await this.db.execute(query, [startDate, endDate]);
return results;
} catch (error) {
throw new Error(`프로젝트-작업유형별 원본 데이터 조회 실패: ${error.message}`);
}
}
}
module.exports = WorkAnalysis;