-- CREATE USER attendance IDENTIFIED BY 12345

-- grant connect, resource to attendance;

DROP TABLE atd;
DROP TABLE student;
DROP TABLE teacher;
DROP TABLE class;

DROP SEQUENCE atd_no_seq;
DROP SEQUENCE student_s_no_seq;
DROP SEQUENCE teacher_t_no_seq;
DROP SEQUENCE class_class_no_seq;

CREATE TABLE class (
    class_no   NUMBER PRIMARY KEY,      -- 반 ID (기본키)
    class_name VARCHAR2(50) NOT NULL,   -- 반 이름
    t_no       NUMBER                   -- 담당 교사 ID (외래키, teacher 테이블 참조)
);

CREATE TABLE teacher (
    t_no     NUMBER PRIMARY KEY,           -- 교사 고유 번호
    t_id     VARCHAR2(50) UNIQUE NOT NULL, -- 로그인 아이디
    t_pw     VARCHAR2(255) NOT NULL,       -- 비밀번호 (암호화 권장)
    t_name   VARCHAR2(50) NOT NULL,        -- 이름
    t_tel    VARCHAR2(20),                 -- 전화번호
    class_no NUMBER,                       -- 담당 반 ID (외래키)
    CONSTRAINT fk_teacher_class FOREIGN KEY (class_no) REFERENCES class(class_no) 
);

CREATE TABLE  student (
    s_no    NUMBER PRIMARY KEY,            -- 학생 고유 번호
    s_id     VARCHAR2(50) UNIQUE NOT NULL, -- 로그인 아이디
    s_pw     VARCHAR2(255) NOT NULL,       -- 비밀번호 (암호화 권장)
    s_name   VARCHAR2(50) NOT NULL,        -- 이름
    s_birth  DATE NOT NULL,           	   -- 생년월일
    s_tel    VARCHAR2(20),                 -- 전화번호
    s_addr   VARCHAR2(255),            	   -- 주소
    class_no NUMBER,                 	   -- 반 ID (외래키)
    CONSTRAINT fk_student_class FOREIGN KEY (class_no) REFERENCES class(class_no)
);

CREATE TABLE atd (
    atd_no   NUMBER    PRIMARY KEY,       				  -- 출결 ID (기본키)
    s_no 	 NUMBER    NOT NULL,            			  -- 학생 ID (외래키)
    atd_date DATE      DEFAULT SYSDATE,   				  -- 출석 날짜 (기본값: 오늘 날짜)
    atd_time TIMESTAMP DEFAULT SYSTIMESTAMP, 		      -- 출석 시간 (기본값: 현재 시간)
    status 	 CHAR(1)   CHECK (status IN ('P', 'A', 'L')), -- 출석 상태 ('P': 출석, 'A': 결석, 'L': 지각)
    t_no	 NUMBER,                     				  -- 담당 교사 ID (외래키)
    CONSTRAINT fk_atd_student FOREIGN KEY (s_no) REFERENCES student(s_no),
    CONSTRAINT fk_atd_teacher FOREIGN KEY (t_no) REFERENCES teacher(t_no)
);

--CREATE OR REPLACE TRIGGER set_atd_status
--BEFORE INSERT ON atd
--FOR EACH ROW
--BEGIN
--    -- 출석 시간에 따라 출결 상태 자동 설정
--    IF TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS') <= '08:59:59' THEN
--        :NEW.status := 'P'; -- 출석
--    ELSIF TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS') BETWEEN '09:00:00' AND '12:59:59' THEN
--        :NEW.status := 'L'; -- 지각
--    ELSE
--        :NEW.status := 'A'; -- 결석
--    END IF;
--END;
CREATE OR REPLACE TRIGGER set_atd_status
BEFORE INSERT ON atd
FOR EACH ROW
BEGIN
    -- 만약 이미 상태가 지정되어 있으면, 트리거에서 덮어쓰지 않음
    IF :NEW.status IS NULL THEN
        -- 출석 시간에 따라 출결 상태 자동 설정
        IF TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS') <= '08:59:59' THEN
            :NEW.status := 'P'; -- 출석
        ELSIF TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS') BETWEEN '09:00:00' AND '12:59:59' THEN
            :NEW.status := 'L'; -- 지각
        ELSE
            :NEW.status := 'A'; -- 결석
        END IF;
    END IF;
END;

-- atd_id 시퀀스 생성
-- DROP SEQUENCE atd_id_seq;
CREATE SEQUENCE atd_no_seq
START WITH 1
INCREMENT BY 1;

-- s_no 시퀀스 생성 (학생 고유 번호)
-- DROP SEQUENCE student_s_no_seq;
CREATE SEQUENCE student_s_no_seq
START WITH 1
INCREMENT BY 1;

-- t_no 시퀀스 생성 (교사 고유 번호)
-- DROP SEQUENCE teacher_t_no_seq;
CREATE SEQUENCE teacher_t_no_seq
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE class_class_no_seq
START WITH 1
INCREMENT BY 1;

COMMIT;
import cx_Oracle as oci
from faker import Faker
import random
from datetime import datetime, timedelta

fake = Faker('ko-KR')

sid = 'XE'
host = '210.119.14.71'
port = 1521
username = 'attendance'
password = '12345'                                                                                                                                                                                       
def generate_date_of_birth():
    year = random.choice([2020, 2021, 2022])
    month = random.randint(1, 12)
    day = random.randint(1, 28)
    return f"{year}-{month:02d}-{day:02d}"

def random_time():
    status = random.choice(['P', 'L', 'A'])

    if status == 'P':
        time = fake.date_time_between(start_date='-1d', end_date='now').replace(hour=random.randint(7, 8), minute=random.randint(0, 59))
    elif status == 'L':
        time = fake.date_time_between(start_date='-1d', end_date='now').replace(hour=random.randint(9, 12), minute=random.randint(0, 59))
    else:
        time = fake.date_time_between(start_date='-1d', end_date='now').replace(hour=random.randint(13, 23), minute=random.randint(0, 59))

    return time, status

class AddData:
    def addCdata(self):
        print("addCdata 함수 실행됨")

        isSucceed = False
        conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
        cursor = conn.cursor()

        class_no_list = []

        try:
            conn.begin()

            query = '''
                INSERT INTO ATTENDANCE.CLASS(CLASS_NO, CLASS_NAME, T_NO)
                VALUES(ATTENDANCE.class_class_no_seq.nextval, :1, NULL)
            '''

            data_list = [(f"{i}반",) for i in range(1, 11)]

            cursor.executemany(query, data_list)
            conn.commit()

            print("CLASS 테이블에 데이터 삽입 완료")

            cursor.execute('SELECT class_no FROM ATTENDANCE.CLASS ORDER BY class_no')
            class_no_list = [row[0] for row in cursor.fetchall()]

            print("삽입된 CLASS_NO 목록:", class_no_list)

        except Exception as e:
            print("addCdata 오류 발생:", e)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

        return class_no_list

    def addTdata(self, class_no_list):
        print("addTdata 함수 실행됨")

        isSucceed = False
        conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
        cursor = conn.cursor()

        try:
            conn.begin()

            query = '''
                INSERT INTO ATTENDANCE.TEACHER (T_NO, T_ID, T_PW, T_NAME, T_TEL, CLASS_NO) 
                VALUES(ATTENDANCE.teacher_t_no_seq.nextval, :1, :2, :3, :4, :5)
            '''

            data_list = [(fake.user_name(), fake.password(), fake.name(), fake.phone_number(), class_no) for class_no in class_no_list]

            cursor.executemany(query, data_list)
            conn.commit()

            print("TEACHER 테이블에 데이터 삽입 완료")

            cursor.execute('SELECT T_NO, CLASS_NO FROM ATTENDANCE.TEACHER')
            teacher_no_list = cursor.fetchall()

            print("삽입된 TEACHER 목록:", teacher_no_list)

            update_query = '''
                UPDATE ATTENDANCE.CLASS 
                SET T_NO = :1 
                WHERE CLASS_NO = :2 AND T_NO IS NULL
            '''
            cursor.executemany(update_query, teacher_no_list)
            conn.commit()

            print("CLASS 테이블의 T_NO 업데이트 완료")

            isSucceed = True

        except Exception as e:
            print("addTdata 오류 발생:", e)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

        return isSucceed

    def addSdata(self, class_no_list):
        print("addSdata 함수 실행됨")

        isSucceed = False
        conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
        cursor = conn.cursor()

        try:
            conn.begin()

            query = '''
                INSERT INTO ATTENDANCE.STUDENT (S_NO, S_ID, S_PW, S_NAME, S_BIRTH, S_TEL, S_ADDR, CLASS_NO) 
                VALUES(ATTENDANCE.student_s_no_seq.nextval, :1, :2, :3, TO_DATE(:4, 'YYYY-MM-DD'), :5, :6, :7)
            '''

            student_data = [
                (str(fake.random_number(digits=6)), fake.password(), fake.name(), generate_date_of_birth(), fake.phone_number(), fake.address(), class_no)
                for class_no in class_no_list for _ in range(random.randint(25, 30))
            ]

            cursor.executemany(query, student_data)
            conn.commit()

            print(f"STUDENT 테이블에 {len(student_data)}개 데이터 삽입 완료")

            isSucceed = True

        except Exception as e:
            print("addSdata 오류 발생:", e)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

        return isSucceed

    def addAdata(self, class_no_list):
        print("addAdata 함수 실행됨")

        isSucceed = False
        conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
        cursor = conn.cursor()

        try:
            conn.begin()

            cursor.execute("SELECT S_NO, CLASS_NO FROM ATTENDANCE.STUDENT")
            student_list = cursor.fetchall()

            cursor.execute("SELECT T_NO, CLASS_NO FROM ATTENDANCE.TEACHER")
            teacher_dict = {row[1]: row[0] for row in cursor.fetchall()}

            attendance_data = [(s_no, random_time()[0], teacher_dict[class_no], random_time()[1]) 
                            for s_no, class_no in student_list if class_no in teacher_dict]

            query = '''
                INSERT INTO ATTENDANCE.ATD (ATD_no, S_NO, ATD_DATE, ATD_TIME, T_NO, STATUS) 
                VALUES(ATTENDANCE.atd_no_seq.nextval, :1, SYSDATE, :2, :3, :4)
            '''

            cursor.executemany(query, attendance_data)
            conn.commit()

            print(f"ATD 테이블에 {len(attendance_data)}개 출석 데이터 삽입 완료")

            isSucceed = True

        except Exception as e:
            print("addAdata 오류 발생:", e)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

        return isSucceed

if __name__ == "__main__":
    add_data = AddData()

    class_no_list = add_data.addCdata()
    if class_no_list:
        add_data.addTdata(class_no_list)
        add_data.addSdata(class_no_list)
        add_data.addAdata(class_no_list)
    else:
        print("CLASS 테이블 데이터 삽입 실패")
import cx_Oracle as oci
from faker import Faker
import random
from datetime import datetime, timedelta

fake = Faker('ko-KR')

sid = 'XE'
host = '210.119.14.71'
port = 1521
username = 'attendance'
password = '12345'
                                                                                                                                                                                       
def generate_date_of_birth():
    year = random.choice([2020, 2021, 2022])
    month = random.randint(1, 12)
    day = random.randint(1, 28)
    return f"{year}-{month:02d}-{day:02d}"

def random_time(current_date):
    status = random.choice(['P', 'L', 'A'])

    if status == 'P':
        time = current_date.replace(hour=random.randint(7, 8), minute=random.randint(0, 59))
    elif status == 'L':
        time = current_date.replace(hour=random.randint(9, 12), minute=random.randint(0, 59))
    else:
        time = current_date.replace(hour=random.randint(13, 23), minute=random.randint(0, 59))

    return time, status

class AddData:
    def addCdata(self):
        print("addCdata 함수 실행됨")

        isSucceed = False
        conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
        cursor = conn.cursor()

        class_no_list = []

        try:
            conn.begin()

            query = '''
                INSERT INTO ATTENDANCE.CLASS(CLASS_NO, CLASS_NAME, T_NO)
                VALUES(ATTENDANCE.class_class_no_seq.nextval, :1, NULL)
            '''

            data_list = [(f"{i}반",) for i in range(1, 11)]

            cursor.executemany(query, data_list)
            conn.commit()

            print("CLASS 테이블에 데이터 삽입 완료")

            cursor.execute('SELECT class_no FROM ATTENDANCE.CLASS ORDER BY class_no')
            class_no_list = [row[0] for row in cursor.fetchall()]

            print("삽입된 CLASS_NO 목록:", class_no_list)

        except Exception as e:
            print("addCdata 오류 발생:", e)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

        return class_no_list

    def addTdata(self, class_no_list):
        print("addTdata 함수 실행됨")

        isSucceed = False
        conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
        cursor = conn.cursor()

        try:
            conn.begin()

            query = '''
                INSERT INTO ATTENDANCE.TEACHER (T_NO, T_ID, T_PW, T_NAME, T_TEL, CLASS_NO) 
                VALUES(ATTENDANCE.teacher_t_no_seq.nextval, :1, :2, :3, :4, :5)
            '''

            data_list = [(fake.user_name(), fake.password(), fake.name(), fake.phone_number(), class_no) for class_no in class_no_list]

            cursor.executemany(query, data_list)
            conn.commit()

            print("TEACHER 테이블에 데이터 삽입 완료")

            cursor.execute('SELECT T_NO, CLASS_NO FROM ATTENDANCE.TEACHER')
            teacher_no_list = cursor.fetchall()

            print("삽입된 TEACHER 목록:", teacher_no_list)

            update_query = '''
                UPDATE ATTENDANCE.CLASS 
                SET T_NO = :1 
                WHERE CLASS_NO = :2 AND T_NO IS NULL
            '''
            cursor.executemany(update_query, teacher_no_list)
            conn.commit()

            print("CLASS 테이블의 T_NO 업데이트 완료")

            isSucceed = True

        except Exception as e:
            print("addTdata 오류 발생:", e)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

        return isSucceed

    def addSdata(self, class_no_list):
        print("addSdata 함수 실행됨")

        isSucceed = False
        conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
        cursor = conn.cursor()

        try:
            conn.begin()

            query = '''
                INSERT INTO ATTENDANCE.STUDENT (S_NO, S_ID, S_PW, S_NAME, S_BIRTH, S_TEL, S_ADDR, CLASS_NO) 
                VALUES(ATTENDANCE.student_s_no_seq.nextval, :1, :2, :3, TO_DATE(:4, 'YYYY-MM-DD'), :5, :6, :7)
            '''

            student_data = [
                (str(fake.random_number(digits=6)), fake.password(), fake.name(), generate_date_of_birth(), fake.phone_number(), fake.address(), class_no)
                for class_no in class_no_list for _ in range(random.randint(25, 30))
            ]

            cursor.executemany(query, student_data)
            conn.commit()

            print(f"STUDENT 테이블에 {len(student_data)}개 데이터 삽입 완료")

            isSucceed = True

        except Exception as e:
            print("addSdata 오류 발생:", e)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

        return isSucceed

    def addAdata(self, class_no_list):
        print("addAdata 함수 실행됨")

        isSucceed = False
        conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
        cursor = conn.cursor()

        try:
            conn.begin()

            cursor.execute("SELECT S_NO, CLASS_NO FROM ATTENDANCE.STUDENT")
            student_list = cursor.fetchall()

            cursor.execute("SELECT T_NO, CLASS_NO FROM ATTENDANCE.TEACHER")
            teacher_dict = {row[1]: row[0] for row in cursor.fetchall()}  

            attendance_data = []

            start_date = datetime(2025, 2, 1)  
            end_date = datetime(2025, 2, 28)  

            current_date = start_date
            while current_date <= end_date:
                if current_date.weekday() < 5:
                    for s_no, class_no in student_list:
                        if class_no in teacher_dict:  
                            atd_time, status = random_time(current_date) 

                            attendance_data.append((s_no, current_date.strftime('%Y-%m-%d'), atd_time.strftime('%Y-%m-%d %H:%M:%S'), teacher_dict[class_no], status))

                current_date += timedelta(days=1) 

            query = '''
                INSERT INTO ATTENDANCE.ATD (ATD_no, S_NO, ATD_DATE, ATD_TIME, T_NO, STATUS) 
                VALUES(ATTENDANCE.atd_no_seq.nextval, :1, TO_DATE(:2, 'YYYY-MM-DD'), TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'), :4, :5)
            '''

            cursor.executemany(query, attendance_data)
            conn.commit()

            print(f"ATD 테이블에 {len(attendance_data)}개 출석 데이터 삽입 완료 (2월 한 달간, 월~금만 기록)")

            isSucceed = True

        except Exception as e:
            print("addAdata 오류 발생:", e)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()

        return isSucceed

if __name__ == "__main__":
    add_data = AddData()

    class_no_list = add_data.addCdata()
    if class_no_list:
        add_data.addTdata(class_no_list)
        add_data.addSdata(class_no_list)
        add_data.addAdata(class_no_list)
    else:
        print("CLASS 테이블 데이터 삽입 실패")
import sys
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtCore import QDate, Qt
from PyQt5 import QtWidgets, uic
import cx_Oracle as oci

# DB 접속 정보
sid = 'XE'
host = '210.119.14.71'
port = 1521
username = 'attendance'
password = '12345'

class CustomCalendar(QCalendarWidget):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.symbols = {}  
        self.setVerticalHeaderFormat(QCalendarWidget.NoVerticalHeader)
        self.load_attendance_data()  

    def load_attendance_data(self):
        try:
            conn = oci.connect(f'{username}/{password}@{host}:{port}/{sid}')
            cursor = conn.cursor()

            query = '''
                SELECT ATD_DATE, STATUS 
                FROM ATTENDANCE.ATD 
                WHERE S_NO = 1 
                AND EXTRACT(MONTH FROM ATD_DATE) = 2
            '''
            cursor.execute(query)
            rows = cursor.fetchall()

            # 출석 상태를 달력 기호로 변환
            status_map = {'P': 'O', 'L': '△', 'A': 'X'}
            for date, status in rows:
                qdate = QDate(date.year, date.month, date.day)
                self.symbols[qdate] = status_map.get(status, "")

        except Exception as e:
            print("데이터베이스 오류:", e)
        finally:
            cursor.close()
            conn.close()

    def paintCell(self, painter, rect, date):
        super().paintCell(painter, rect, date)

        if date in self.symbols:
            # 출석 상태에 따라 색상 지정
            symbol = self.symbols[date]
            color_map = {'O': "blue", '△': "green", 'X': "red"}
            painter.setPen(QColor(color_map.get(symbol, "black")))  # 기본은 검은색

            # 폰트 크기 및 정렬 설정
            painter.setFont(QFont("Arial", 20, QFont.Bold))
            painter.drawText(rect, Qt.AlignCenter, symbol)  # 정중앙 배치

class AttendanceApp(QMainWindow):
    def __init__(self):
        super().__init__()
        uic.loadUi('./miniproject01/출석관리,통계.ui', self)  # UI 파일 로드

        # 기존 UI에서 QCalendarWidget 찾기
        old_calendar = self.findChild(QCalendarWidget, "calendarWidget")
        if old_calendar:
            # 기존 QCalendarWidget을 CustomCalendar로 변경
            self.custom_calendar = CustomCalendar(self)
            self.custom_calendar.setGeometry(old_calendar.geometry())  # 위치 유지
            self.custom_calendar.setObjectName("calendarWidget")

            # 기존 달력 삭제 후 새 달력 추가
            layout = old_calendar.parentWidget().layout()  # 부모 레이아웃 가져오기
            if layout:
                layout.replaceWidget(old_calendar, self.custom_calendar)  # 위젯 교체
            old_calendar.deleteLater()  # 기존 달력 제거

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = AttendanceApp()
    window.show()
    sys.exit(app.exec_())