def reserve_lecture_user(lecture_idx, user_idx):
conn = database.get_connection()
sql = '''
insert into lecture_reservation_table(lecture_idx, user_idx, reservation_time)
values(%s, %s, sysdate())
'''
cursor = conn.cursor()
cursor.execute(sql, (lecture_idx, user_idx))
conn.commit()
conn.close()
def drop_lecture(lecture_idx):
conn = database.get_connection()
sql = '''
delete from lecture_table
where lecture_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (lecture_idx))
conn.commit()
conn.close()
def add_user(user_name, user_id, user_pw, user_email):
conn = database.get_connection()
sql = '''
insert into user_table(user_id, user_pw, user_email, user_name, user_type)
values(%s,%s,%s,%s,%s)
'''
cursor = conn.cursor()
cursor.execute(sql, (user_id, user_pw, user_email, user_name, 'A'))
conn.commit()
conn.close()
def requested_lecture_drop(lecture_idx, user_idx):
conn = database.get_connection()
sql = '''
delete from lecture_reservation_table
where lecture_idx = %s and user_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (lecture_idx, user_idx))
conn.commit()
conn.close()
def board_write(board_title,bc_writer_idx,board_content):
conn = database.get_connection()
sql = '''
insert into board_content(bc_subject,bc_writer_idx,bc_date,bc_content,board_type_idx)
values(%s,%s,DEFAULT,%s,1)
'''
cursor = conn.cursor()
cursor.execute(sql, (board_title,bc_writer_idx,board_content))
conn.commit()
conn.close()
def cal_time(lecture_idx):
conn = database.get_connection()
sql = '''
select lecture_deadline
from lecture_table
where lecture_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (lecture_idx))
result = cursor.fetchone()
conn.close()
return result[0]
def get_total_board_cnt():
conn = database.get_connection()
sql = '''
select count(*)
from lecture
'''
cursor = conn.cursor()
cursor.execute(sql)
result = cursor.fetchone()
conn.close()
return result[0]
def add_lecture(lecture_name, lecture_capacity, lecture_target, lecture_price,
lecture_teacher, lecture_deadline, lecture_start, lecture_end):
conn = database.get_connection()
sql = '''
insert into lecture_table(lecture_name,lecture_enrollment, lecture_capacity, lecture_target, lecture_price, lecture_teacher, lecture_deadline, lecture_start, lecture_end)
values(%s, 0, %s, %s, %s, %s, %s, %s, %s)
'''
cursor = conn.cursor()
cursor.execute(
sql, (lecture_name, lecture_capacity, lecture_target, lecture_price,
lecture_teacher, lecture_deadline, lecture_start, lecture_end))
conn.commit()
conn.close()
def count_lecture_user(lecture_idx):
conn = database.get_connection()
sql = '''
select count(*)
from lecture_reservation_table
where lecture_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (lecture_idx))
result = cursor.fetchone()
conn.close()
return result
def get_user_lecture_data(lecture_idx, user_idx):
conn = database.get_connection()
sql = '''
select *
from lecture_reservation_table
where lecture_idx = %s and user_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (lecture_idx, user_idx))
result = cursor.fetchone()
conn.close()
return result
def get_lecture_content(lecture_idx):
conn = database.get_connection()
sql = '''
select lecture_idx, lecture_name, lecture_start, lecture_end, lecture_enrollment, lecture_capacity, lecture_target, lecture_price, lecture_teacher
from lecture_table
where lecture_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (lecture_idx))
result = cursor.fetchone()
conn.close()
return result
def check_user_type(user_idx):
conn = database.get_connection()
sql = '''
select user_type
from user_table
where user_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (user_idx))
result = cursor.fetchone()
conn.close()
return result[0]
def board_list():
conn = database.get_connection()
sql = '''
select bor.bc_idx, bor.bc_subject, u.user_name, bor.bc_date, bor.bc_content
from board_content bor
inner join user_table u
on bor.bc_idx = u.user_idx
where board_type_idx=1
'''
cursor = conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
conn.close()
return result
def kakao_user_check(user_kakao_idx):
conn = database.get_connection()
sql = '''
select user_kakao_idx
from user_table
where user_kakao_idx=%s
'''
cursor = conn.cursor()
cursor.execute(sql, (user_kakao_idx))
result = cursor.fetchone()
if result == None:
conn.close()
return 'No'
else:
user_kakao_idx = result[0]
conn.close()
return user_kakao_idx
def user_kakao_user_id(headers):
url = "https://kapi.kakao.com/v2/user/me"
response = requests.request("POST", url, headers=headers)
kakao_id_json = json.loads(
((response.text).encode('utf-8'))) # 카카오 로그인 회원 id json
user_kakao_idx = kakao_id_json["id"] # 카카오 로그인 회원 id (string)
conn = database.get_connection()
sql = '''
select user_name
from user_table
where user_kakao_idx=%s
'''
cursor = conn.cursor()
cursor.execute(sql, user_kakao_idx)
result = cursor.fetchone()
return result[0]
def login_check(user_id, user_pw):
conn = database.get_connection()
sql = '''
select user_idx, user_pw, user_type, user_name
from user_table
where user_id=%s and user_pw=%s
'''
cursor = conn.cursor()
cursor.execute(sql, (user_id, user_pw))
result = cursor.fetchone()
print(result)
if result == None:
conn.close()
return 'No'
else:
conn.close()
return result
def email_check(email):
conn = database.get_connection()
sql = '''
select user_name
from user_table
where user_email=%s
'''
cursor = conn.cursor()
cursor.execute(sql, (user_email))
result = cursor.fetchone()
print(result)
if result == None:
conn.close()
return 'No'
else:
conn.close()
return result
def get_lecture_list(page):
# if 문 써도됨
# page = 1
conn = database.get_connection()
# 현재 페이지의 글 목록의 시작 글의 인덱스
# start_idx = (int(page) - 1) * 10
sql = '''
select lecture_idx, lecture_name, lecture_start, lecture_end, lecture_capacity, lecture_target, lecture_price, lecture_teacher
from lecture_table
limit 0, 10
'''
cursor = conn.cursor()
# cursor.execute(sql, (start_idx))
cursor.execute(sql)
result = cursor.fetchall()
conn.close()
return result
