Database/wbs_DB.py at master · CodeCraft-NSU/Database · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
"""
    CodeCraft PMS Project
    파일명 : wbs_DB.py
    마지막 수정 날짜 : 2025/02/17
"""

import pymysql
from mysql_connection import db_connect

# 진척도(WBS) 항목 여러 개 추가 및 기존 데이터 삭제 후 재삽입
def add_multiple_wbs(wbs_data, pid):
    connection = db_connect()
    cur = connection.cursor(pymysql.cursors.DictCursor)

    try:
        # 새로운 데이터 추가
        wbs_data_with_pid = [item + [pid] for item in wbs_data]
        add_multiple_wbs_query = """
        INSERT INTO progress (group1, group2, group3, group4, work, output_file, manager, note, ratio, start_date, end_date, group1no, group2no, group3no, group4no, p_no)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cur.executemany(add_multiple_wbs_query, wbs_data_with_pid)
        connection.commit()
        return True
    except Exception as e:
        connection.rollback()
        print(f"Error [add_multiple_wbs] : {e}")
        return e
    finally:
        cur.close()
        connection.close()

# 특정 프로젝트의 모든 WBS 항목 조회
def fetch_all_wbs(pid):
    connection = db_connect()
    cur = connection.cursor(pymysql.cursors.DictCursor)

    try:
        cur.execute("SELECT /*+ INDEX(progress idx_progress_pno_group1to4) */ * FROM progress WHERE p_no = %s ORDER BY group1no, group2no, group3no, group4no", (pid,))
        result = cur.fetchall()
        return result
    except Exception as e:
        print(f"Error [fetch_all_wbs] : {e}")
        return e
    finally:
        cur.close()
        connection.close()

# 특정 프로젝트의 모든 WBS 항목 삭제
def delete_all_wbs(pid):
    connection = db_connect()
    cur = connection.cursor(pymysql.cursors.DictCursor)

    try:
        cur.execute("SELECT COUNT(*) AS cnt FROM progress WHERE p_no = %s", (pid,))
        result = cur.fetchone()

        if result['cnt'] == 0:
            print(f"Error [delete_all_wbs] : Progress data does not exist in Project UID {pid}.")
            return False

        cur.execute("DELETE FROM progress WHERE p_no = %s", (pid,))
        connection.commit()
        return True
    except Exception as e:
        connection.rollback()
        print(f"Error [delete_all_wbs] : {e}")
        return e
    finally:
        cur.close()
        connection.close()

# 특정 프로젝트의 WBS에서 대분류 항목별로 진척률의 평균을 조회하는 함수
# 프로젝트 번호를 매개 변수로 받는다
def fetch_wbs_ratio(pid):
    connection = db_connect()
    cur = connection.cursor(pymysql.cursors.DictCursor)

    try:
        fetch_wbs_ratio = """
        SELECT group1no, group1, CAST(ROUND(AVG(ratio)) AS UNSIGNED) AS ratio
        FROM progress
        WHERE p_no = %s
        GROUP BY group1no, group1
        """
        cur.execute(fetch_wbs_ratio, (pid,))
        result = cur.fetchall()
        return result
    except Exception as e:
        print(f"Error [fetch_wbs_ratio] : {e}")
        return e
    finally:
        cur.close()
        connection.close()