68 lines
2.6 KiB
Python
68 lines
2.6 KiB
Python
class InquiryQueries:
|
|
"""문의사항(Inquiries) 페이지 관련 쿼리"""
|
|
# 필터링을 위한 기본 쿼리 (WHERE 1=1 포함)
|
|
SELECT_BASE = "SELECT * FROM inquiries WHERE 1=1"
|
|
ORDER_BY_DESC = "ORDER BY no DESC"
|
|
|
|
# 상세 조회
|
|
SELECT_BY_ID = "SELECT * FROM inquiries WHERE id = %s"
|
|
|
|
# 답변 업데이트 (handled_date 포함)
|
|
UPDATE_REPLY = """
|
|
UPDATE inquiries
|
|
SET reply = %s, status = %s, handler = %s, handled_date = %s
|
|
WHERE id = %s
|
|
"""
|
|
|
|
# 답변 삭제 (초기화)
|
|
DELETE_REPLY = """
|
|
UPDATE inquiries
|
|
SET reply = '', status = '미확인', handled_date = ''
|
|
WHERE id = %s
|
|
"""
|
|
|
|
class DashboardQueries:
|
|
"""대시보드(Dashboard) 및 프로젝트 현황 관련 쿼리"""
|
|
# 가용 날짜 목록 조회
|
|
GET_AVAILABLE_DATES = "SELECT DISTINCT crawl_date FROM projects_history ORDER BY crawl_date DESC"
|
|
|
|
# 최신 수집 날짜 조회
|
|
GET_LAST_CRAWL_DATE = "SELECT MAX(crawl_date) as last_date FROM projects_history"
|
|
|
|
# 특정 날짜 프로젝트 데이터 JOIN 조회
|
|
GET_PROJECT_LIST = """
|
|
SELECT m.project_nm, m.short_nm, m.department, m.master,
|
|
h.recent_log, h.file_count, m.continent, m.country
|
|
FROM projects_master m
|
|
LEFT JOIN projects_history h ON m.project_id = h.project_id AND h.crawl_date = %s
|
|
ORDER BY m.project_id ASC
|
|
"""
|
|
|
|
# 활성도 분석을 위한 프로젝트 목록 조회
|
|
GET_PROJECT_LIST_FOR_ANALYSIS = """
|
|
SELECT m.project_id, m.project_nm, m.short_nm, h.recent_log, h.file_count
|
|
FROM projects_master m
|
|
LEFT JOIN projects_history h ON m.project_id = h.project_id AND h.crawl_date = %s
|
|
"""
|
|
|
|
class CrawlerQueries:
|
|
"""크롤러(Crawler) 데이터 동기화 관련 쿼리"""
|
|
# 마스터 정보 UPSERT (INSERT OR UPDATE)
|
|
UPSERT_MASTER = """
|
|
INSERT INTO projects_master (project_id, project_nm, short_nm, master, continent, country)
|
|
VALUES (%s, %s, %s, %s, %s, %s)
|
|
ON DUPLICATE KEY UPDATE
|
|
project_nm = VALUES(project_nm), short_nm = VALUES(short_nm),
|
|
master = VALUES(master), continent = VALUES(continent), country = VALUES(country)
|
|
"""
|
|
|
|
# 부서 정보 업데이트
|
|
UPDATE_DEPARTMENT = "UPDATE projects_master SET department = %s WHERE project_id = %s"
|
|
|
|
# 히스토리(로그/파일수) 저장
|
|
UPSERT_HISTORY = """
|
|
INSERT INTO projects_history (project_id, crawl_date, recent_log, file_count)
|
|
VALUES (%s, CURRENT_DATE(), %s, %s)
|
|
ON DUPLICATE KEY UPDATE recent_log=VALUES(recent_log), file_count=VALUES(file_count)
|
|
"""
|