-- 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 테이블 데이터 삽입 실패")
[x] 시간에 따른 출석변화 처리
[x] 한 학급에 담임선생님 배정
[x] 한 학급에 25~30명의 학생들 배정
추가본(INSERT구문)
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_())