def reple(self): # 댓글 달기 버튼 누르면 실행하는 함수
text = self.ed_reple.text()
if len(text) == 0:
pyautogui.alert('뭘 먹었는지, 맛은 어땠는지 내용을 남기렴..')
else:
score = float(self.combo.currentText()[:3])
# print(score)
self.ed_reple.setText('') # 댓글 달리면 댓글창 내용 리셋
sql = '''
INSERT INTO restaurant_reple(
r_idx,
user_id,
reple,
score,
rep_time
) VALUES(
:r_idx,
:user_id,
:reple,
:score,
sysdate
)
'''
# rep_time = time.ctime()
db = DbConn()
db.execute(
sql, {
'r_idx': self.idx,
'user_id': self.parent.user_id,
'reple': text,
'score': score,
})
self.refresh_user_score()
self.parent.route_page('cafe_re', self.idx)
def cancel_party(self):
db = DbConn()
query = "UPDATE matjip_party SET member_list = :member_list , cur_member = cur_member-1 WHERE p_idx = :p_idx"
self.mem_list.remove(self.parent.user_name)
member_list = ",".join(self.mem_list)
db.execute(query, {"member_list": member_list, "p_idx": self.p_idx})
self.alert_msg("파티 탈퇴 성공 !")
self.parent.route_page("party_detail", self.p_idx)
def bring_info(self, idx):
db = DbConn()
sql = '''
select r_name, naver_idx, image_url
from restaurant
where r_idx =''' + str(idx)
rows = db.execute(sql)
# print(rows[0][1]) # [('대학로수제모찌', 'https://store.naver.com/restaurants/detail?id=1205920548')] -> 리스트 안에 튜플 형식 1개 값이 담겨있음
return rows
def bring_re(self, idx):
db = DbConn()
sql = '''
select r_idx, user_id, reple, score, rep_time, status
from restaurant_reple
where r_idx =''' + str(idx)
rows = db.execute(sql)
# print(rows)
return rows
def bringdata(self):
db = DbConn()
sql = '''
select r_idx, r_name, main_menu, price, score, site_score, review, site_review, distance
from restaurant
where r_category like '카페%'
order by score DESC, site_score DESC, review DESC, site_review DESC
'''
rows = db.execute(sql)
# print(rows)
return rows
def select_restaurant_url(self):
# self.params 를 가지고 셀렉트 돌려서 식당정보 가져오기
sql_select_restaurant_url = """
SELECT
IMAGE_URL
FROM restaurant
WHERE r_idx = {}
""".format(self.params)
db = DbConn()
self.img_url = db.execute(sql_select_restaurant_url)[0][0]
def set_reple(self):
reple = self.lineedit_reple.text()
if reple != '':
db = DbConn()
query = "INSERT INTO party_reple(p_idx, user_id, reple, rep_time, status) VALUES (:p_idx, :user_id, :reple, sysdate, 1)"
db.execute(
query, {
"p_idx": self.p_idx,
"user_id": self.parent.user_id,
"reple": reple
})
self.parent.route_page("party_detail", self.p_idx)
def login(self):
user_id = self.le_id.text()
user_pw = self.le_pw.text()
query = "select name from jhta_user where user_id = :user_id and pwd = :pwd"
db = DbConn()
result = db.execute(query, {'user_id': user_id, 'pwd': user_pw})
if len(result) == 0:
QMessageBox.question(self, "Error!", "아이디 또는 패스워드를 확인해주세요.",
QMessageBox.Yes)
else:
self.parent.user_name = result[0][0]
self.parent.user_id = user_id
self.parent.route_page('menu')
def new_party(self):
title = self.le_title.text()
# max_member = self.le_max_member.text()
max_member = self.le_max_member.value()
end_time = self.le_end_time.dateTime().toString("yyyy-MM-dd hh:mm:ss")
user_id = self.parent.user_id
user_name = self.parent.user_name
# print(title, max_member, end_time)
db = DbConn()
q1 = "SELECT * FROM matjip_party WHERE user_id = :user_id AND end_time > SYSDATE AND status = 1"
res = db.execute(q1, {'user_id': user_id})
if len(res) == 0:
query = """
INSERT INTO matjip_party
(title, user_id, cur_member, max_member, member_list, status, end_time)
VALUES (:title, :user_id, 1, :max_member, :member_list, 1, TO_DATE(:end_time, 'yyyy-MM-dd hh24:mi:ss'))
"""
db.execute(
query, {
'title': title,
'user_id': user_id,
'max_member': max_member,
'member_list': user_name,
'end_time': end_time
})
self.alert_msg("모집 등록이 완료되었습니다.")
self.parent.route_page('party')
else:
self.alert_msg("이미 모집중인 파티가 있습니다.")
db.disconnect()
def find(self):
name = self.le_name.text()
if name == '':
self.alert_msg("이름을 입력하세요.")
return False
birth = self.le_birth.text()
if birth == '':
self.alert_msg("생년월일을 입력하세요.")
return False
db = DbConn()
q1 = "select user_id, pwd from jhta_user where name = :name and birth = :birth"
result = db.execute(q1, {'birth' : birth, 'name' : name})
if len(result) > 0:
self.alert_msg("ID : "+ result[0][0] + " PW : " + result[0][1])
self.parent.route_page("login")
else:
self.alert_msg("해당 정보가 없습니다.")
def join_party(self):
db = DbConn()
query = "UPDATE matjip_party SET member_list = :member_list , cur_member = :cur_member WHERE p_idx = :p_idx"
cur_member = self.cur_mem
if cur_member < self.max_mem:
self.mem_list.append(self.parent.user_name)
member_list = ",".join(self.mem_list)
cur_member += 1
db.execute(
query, {
"member_list": member_list,
"cur_member": cur_member,
"p_idx": self.p_idx
})
self.alert_msg("파티에 참가하였습니다.")
self.parent.route_page("party")
else:
self.alert_msg("파티가 가득 찼습니다. 메롱~")
def new_reple(self):
text = self.lineedit_reple.text()
if len(text) == 0:
pyautogui.alert("뭘 먹었나요? 맛은 어땠어요? 댓글을 달아주세요~")
else:
score = float(self.combobox_score.currentText()[:3])
# print(score,type(score), text, "라고 댓글생성함")
self.lineedit_reple.setText("")
sql_reple_insert = """
INSERT INTO restaurant_reple(
r_idx,
user_id,
reple,
score,
rep_time
) VALUES (
:r_idx,
:user_id,
:reple,
:score,
sysdate
)
"""
rep_time = time.localtime()
db = DbConn()
db.execute(
sql_reple_insert, {
'r_idx': self.params,
'user_id': self.parent.user_id,
'reple': text,
'score': score,
})
self.refresh_user_score()
self.parent.route_page('restaurant_reple', self.params)
def refresh_user_score(self):
sql_select_score = """
SELECT
score
FROM
restaurant_reple
WHERE
r_idx = :r_idx
"""
db = DbConn()
self.score_list = db.execute(sql_select_score, {'r_idx': self.idx})
self.review_num = len(self.score_list)
total_score = 0
for score in self.score_list:
total_score += score[0]
avg_score = round(total_score / self.review_num, 2)
# print(avg_score)
# db에 입력하기
sql_update_score = """
UPDATE restaurant SET
score = :score,
review = :review
WHERE r_idx = :r_idx
"""
db = DbConn()
self.score_list = db.execute(sql_update_score, {
'score': avg_score,
'review': self.review_num,
'r_idx': self.idx
})
def regist(self):
user_id = self.le_id.text()
if user_id == '':
self.alert_msg("아이디를 입력해주세요.")
return False
pwd = self.le_pw.text()
if pwd == '':
self.alert_msg("비밀번호를 입력해주세요.")
return False
name = self.le_name.text()
if name == '':
self.alert_msg("이름을 입력해주세요.")
return False
birth = self.le_birth.text()
if birth == '':
self.alert_msg("생년월일을 입력해주세요.")
return False
tel = self.le_tel.text()
if tel == '':
self.alert_msg("전화번호를 입력해주세요.")
return False
db = DbConn()
q1 = "select * from jhta_user where user_id = :user_id"
res = db.execute(q1, {'user_id': user_id})
if len(res) == 0:
query = "INSERT INTO JHTA_USER (user_id, pwd, name, birth, tel) VALUES (:user_id, :pwd, :name, :birth, :tel)"
db.execute(
query, {
'user_id': user_id,
'pwd': pwd,
'name': name,
'birth': birth,
'tel': tel
})
self.alert_msg("회원가입이 완료되었습니다.")
self.parent.route_page('login')
else:
self.alert_msg("사용 할 수 없는 아이디 입니다.")
db.disconnect()
def select_restaurant(self):
# self.params 를 가지고 셀렉트 돌려서 식당정보 가져오기
sql_select_restaurant = """
SELECT
R_NAME,
R_CATEGORY,
PRICE,
DISTANCE,
SCORE,
SITE_SCORE,
REVIEW,
SITE_REVIEW,
MAIN_MENU,
naver_idx
FROM restaurant
WHERE r_idx = {}
""".format(self.params)
# print(sql_select_restaurant)
db = DbConn()
self.db_result_restaurant = db.execute(sql_select_restaurant)
# print(self.db_result_restaurant, "이거 확인")
# self.list_num = len(self.db_result_restaurant)
self.naver_idx = self.db_result_restaurant[0][9]
# print(self.naver_idx)
url_place = "https://store.naver.com/restaurants/detail?entry=pll&id="
self.site_url = url_place + str(self.naver_idx)
# print(self.site_url)
sql_select_restaurant_reple = """
SELECT
rep_time,
user_id,
score,
reple
FROM restaurant_reple
WHERE r_idx = {}
ORDER BY rep_time DESC
""".format(self.params)
# print(sql_select_restaurant)
db = DbConn()
self.db_result_restaurant_reple = db.execute(
sql_select_restaurant_reple)
def del_party(self):
db = DbConn()
query = "UPDATE matjip_party SET status = 0 WHERE p_idx = :p_idx"
datas = db.execute(query, {"p_idx": self.p_idx})
self.alert_msg("파티가 삭제되었습니다.")
self.parent.route_page("party")
def get_party_reple_data(self):
db = DbConn()
query = "SELECT * FROM party_reple WHERE p_idx = :p_idx ORDER BY rep_time"
datas = db.execute(query, {"p_idx": self.p_idx})
return datas
def get_party_data(self):
db = DbConn()
query = "SELECT * FROM matjip_party WHERE p_idx = :p_idx"
datas = db.execute(query, {"p_idx": self.p_idx})
return datas
# import pyautogui
# print(pyautogui.position())
# from classes/db_conn import *
import sys
sys.path.append('.')
from classes.DbConn import *
db = DbConn()
print(db.execute("SELECT * FROM dept"))
def create_restaurant_table(self):
self.restaurant_idx = []
sql_select_restaurant = """
SELECT
R_NAME,
R_CATEGORY,
PRICE,
DISTANCE,
SCORE,
SITE_SCORE,
REVIEW,
SITE_REVIEW,
MAIN_MENU,
R_IDX
FROM restaurant
WHERE R_CATEGORY not like '%카페%'
ORDER BY score DESC, review DESC, site_score DESC
"""
db = DbConn()
self.db_result_restaurant = db.execute(sql_select_restaurant)
# print(self.db_result_restaurant)
self.list_num = len(self.db_result_restaurant)
self.table = QTableWidget()
# self.table.setSelectionBehavior(QTableView.SelectRows) # multiple row 선택 가능
self.table.setSelectionMode(QAbstractItemView.SingleSelection)
# row, column 갯수 설정해야만 tablewidget 사용할수있다.
self.table.setColumnCount(10)
self.table.setRowCount(self.list_num)
# column header 명 설정.
self.table.setHorizontalHeaderLabels(["식당", "카테고리", "가격", "거리", "평점", "네이버 점수", "리뷰수", "네이버 리뷰수", "대표메뉴", "리뷰확인"])
row_num = 0
for row in self.db_result_restaurant:
col_num = 0
for data in row:
if col_num == 9:
# 글씨 쓰는게 아니라 버튼만 설치
item_widget = QPushButton("리뷰확인")
self.btn_restaurant_reple.append([item_widget, data])
self.table.setCellWidget(row_num, col_num, item_widget)
self.restaurant_idx.append(data)
else:
self.table.setItem(row_num, col_num, QTableWidgetItem(str(data)))
col_num += 1
row_num += 1
self.table.setEditTriggers(QAbstractItemView.NoEditTriggers) # edit 금지 모드
self.table.setColumnWidth(2, 70) #컬럼 사이즈 설정
self.table.setColumnWidth(3, 50) #컬럼 사이즈 설정
self.table.setColumnWidth(4, 50) #컬럼 사이즈 설정
self.table.setColumnWidth(5, 50) #컬럼 사이즈 설정
self.table.setColumnWidth(6, 50) #컬럼 사이즈 설정
self.table.setColumnWidth(7, 50) #컬럼 사이즈 설정
self.table.setColumnWidth(8, 150) #컬럼 사이즈 설정
self.table.setColumnWidth(9, 60) #컬럼 사이즈 설정
self.layout.addWidget(self.table, 1, 0, 1,2)