def getReviews():
db = DB()
db.dbConnect()
db.setCursorDic()
print("getReviews 호출")
userIndex = request.form['userIndex']
getMine = request.form['getMine']
reviewCount = int(request.form['reviewCount'])
if getMine == "true":
sql = "SELECT evaluationIndex, user,id, mission,missionName, rating, weather, date, comment, picture, temperature ,User.grade as grade FROM MissionEvaluation join Mission on MissionEvaluation.mission = Mission.missionID join User on (MissionEvaluation.user = User.userIndex) WHERE MissionEvaluation.user={} and NOT date IS NULL ORDER BY date DESC LIMIT {} , 10".format(
userIndex, reviewCount)
else:
sql = "SELECT evaluationIndex,user,id,mission,missionName, rating, weather, date, comment, picture, temperature,User.grade as grade FROM MissionEvaluation join Mission on MissionEvaluation.mission = Mission.missionID join User on (MissionEvaluation.user = User.userIndex) WHERE NOT date IS NULL ORDER BY date DESC LIMIT {} , 10".format(
reviewCount)
try:
db.curs.execute(sql)
rows = db.curs.fetchall()
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
db.dbDisconnect()
rows = list({row['evaluationIndex']: row for row in rows}.values())
print(rows)
temp = json.dumps(rows, default=json_default).encode('utf-8')
return temp
def insertMissionCandidate():
'''
매개변수 : userIndex, missionName
:return : 성공 여부
'''
print("insertMissionCandidate 호출")
user = request.form['userIndex']
mission = request.form['missionName']
today = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
db = DB()
db.dbConnect()
db.setCursorDic()
print(mission)
sql = "INSERT INTO MissionCandidate (userIndex,date,missionName,likes,dislikes,likesRatio,duplicateCount) VALUES (%s,%s,%s,0,0,0.0,0)"
try:
db.curs.execute(sql, (user, today, mission))
db.conn.commit()
success = {'success': True}
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
success = {'success': False}
#db.dbDisconnect()
return json.dumps(success).encode('utf-8')
def search():
keyword = request.form['keyword']
db = DB()
db.dbConnect()
db.setCursorDic()
'''미션 후보에서 검색하기'''
sql = "SELECT missionName FROM MissionCandidate WHERE missionName LIKE '{}'".format(
'%{0}%'.format(keyword))
try:
db.curs.execute(sql)
rows1 = db.curs.fetchall()
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
'''공식 미션에서 검색하기'''
sql = "SELECT missionName FROM Mission WHERE missionName LIKE '{}'".format(
'%{0}%'.format(keyword))
try:
db.curs.execute(sql)
rows2 = db.curs.fetchall()
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
rows = []
if rows1:
rows = rows + rows1
if rows2:
rows = rows + rows2
if not rows:
rows = [{'missionName': '검색 결과 없음.'}]
return json.dumps(rows, default=json_default).encode('utf-8')
def checkId():
_id = request.form['id']
db = DB()
db.dbConnect()
db.setCursorDic()
'''아이디 중복 체크'''
sql = "SELECT id FROM User WHERE id=%s"
try:
db.curs.execute(sql, (_id, ))
row = db.curs.fetchone()
except Exception as e:
print(e)
db.dbDisconnect()
if row:
success = {'duplicate': True}
return json.dumps(success).encode('utf-8')
else:
success = {'duplicate': False}
return json.dumps(success).encode('utf-8')
def login():
if request.method =='POST':
_id = request.form['id']
_password = request.form['password']
db = DB()
db.dbConnect()
db.setCursorDic()
sql = "select * from User where id = %s and password = %s"
try:
db.curs.execute(sql, (_id, _password))
rows = db.curs.fetchone()
print("rows", rows)
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
if rows == None:
return json.dumps({'error':1}).encode('utf-8')
'''미션을 한 번도 안 했는지 확인인(설문조사 포)'''
sql = "select user from MissionEvaluation where user = %s"
try:
db.curs.execute(sql, (_id,))
row = db.curs.fetchone()
print("row",row)
except mysql.connector.Error as e:
print("error")
print("Error %d: %s" % (e.args[0], e.args[1]))
if row:
rows['isFirst'] = 0
else:
rows['isFirst'] = 1
db.dbDisconnect()
print("로그인",rows)
rows['error'] = 0
return json.dumps(rows).encode('utf-8')
elif request.method =='GET':
return 'GET'
def getMissionKing():
db = DB()
db.dbConnect()
db.setCursorDic()
sql = "SELECT MissionKing.idMissionKing, MissionKing.userIndex, MissionKing.which, MissionKing.ranking,MissionKing.number, MissionKing.emblem, User.id FROM MissionKing JOIN User ON (MissionKing.userIndex = User.userIndex and User.id IS NOT NULL)"
try:
db.curs.execute(sql)
rows = db.curs.fetchall()
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
return json.dumps(rows, default=json_default).encode('utf-8')
def resetCount():
'''
미션을 넘길 때 증가하는 카운트를 초기화 시킨다.
:return:
'''
db =DB()
db.dbConnect()
db.setCursorDic()
sql = "UPDATE User set count = 0, missionOrder = missionOrder+1"
#missionOrder를 증가시키는 것은 뺄 수도 있다.
try:
db.curs.execute(sql)
db.conn.commit()
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
def mypage():
print("mypage 호출")
user = request.form['userIndex']
time = request.form['time_affordable']
expense = request.form['expense_affordable']
push = request.form['push_notification']
db = DB()
db.dbConnect()
db.setCursorDic()
sql = "UPDATE User set time_affordable=%s,expense_affordable=%s, push_notification =%s WHERE userIndex=%s"
try:
db.curs.execute(sql, (time, expense, push, user))
db.conn.commit()
success = {'duplicate': True}
except Exception as e:
print(e)
success = {'duplicate': False}
return json.dumps(success).encode('utf-8')
def register():
print("register.py")
db = DB()
db.dbConnect()
db.setCursorDic()
if request.method == 'POST':
_id = request.form['id']
_password = request.form['password']
_age = request.form['age']
_gender = request.form['gender']
print(_id, _password, _age, _gender)
sql = "INSERT INTO User(id, password, age, gender) VALUES(%s,%s,%s,%s)"
try:
db.curs.execute(sql, (_id, _password, _age, _gender))
db.conn.commit()
success = {'success': True}
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
success = {'success': False}
sql = "SELECT userIndex FROM User WHERE id = %s"
try:
db.curs.execute(sql, (_id, ))
row = db.curs.fetchone()
user_id = row['userIndex']
success = {'success': True}
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
add_new_user(user_id)
return json.dumps(success).encode('utf-8')
elif request.method == 'GET':
return 'GET'
def getMissionCandidate():
print("\n\ngetMissionCandidate 호출\n\n")
'''
클라이언트에서 미션 후보들을 받아올 때 쓰이는 함수
최신순과 좋아요 순이 있다. 10개씩 받아온다.
매개변수 : userIndex, missionCandidateCount, mode
:return:
'''
userIndex = request.form['userIndex']
missionCandidateCount = int(request.form['missionCandidateCount'])
db = DB()
db.dbConnect()
db.setCursorDic()
#mode가 1이면 최신순, 0이면 좋아요가 많은 순
mode = int(request.form['mode'])
'''
missionName : 미션 내용
missionCandidateIndex : 미션 후보 인덱스
totalLikes : 좋아요 수
totalDislikes : 싫어요 수
totalDuplicateCount : 중복체크 수
userLikes : 유저가 좋아요 눌렀는지
userDislikes : 유저가 싫어요 눌렀는지
userDuplicateCount : 유저가 중복 눌렀는지
'''
if mode == 1:
sql = f"SELECT " \
f"MissionCandidate.missionName, " \
f"MissionCandidate.missionCandidateIndex AS missionCandidateIndex, " \
f"MissionCandidate.likes AS totalLikes, " \
f"MissionCandidate.dislikes AS totalDislikes, " \
f"MissionCandidate.duplicateCount AS totalDuplicateCount, " \
f"IFNULL(MissionCandidateEvaluation.likes,0) As userLikes, " \
f"IFNULL(MissionCandidateEvaluation.dislikes,0) As userDislikes, " \
f"IFNULL(MissionCandidateEvaluation.duplicateCount,0) As userDuplicateCount, " \
f"User.id AS user " \
f"FROM MissionCandidate " \
f"LEFT JOIN MissionCandidateEvaluation ON (MissionCandidate.missionCandidateIndex =MissionCandidateEvaluation.missionCandidateIndex and MissionCandidateEvaluation.userIndex = {userIndex}) " \
f"JOIN User ON MissionCandidate.userIndex = User.userIndex " \
f"ORDER BY date DESC LIMIT {missionCandidateCount}, 20"
elif mode == 0:
sql = f"SELECT " \
f"MissionCandidate.missionName, " \
f"MissionCandidate.missionCandidateIndex AS missionCandidateIndex " \
f"MissionCandidate.likes AS totalLikes, " \
f"MissionCandidate.dislikes AS totalDislikes, " \
f"MissionCandidate.duplicateCount AS totalDuplicateCount, " \
f"IFNULL(MissionCandidateEvaluation.likes,0) As userLikes, " \
f"IFNULL(MissionCandidateEvaluation.dislikes,0) As userDislikes, " \
f"IFNULL(MissionCandidateEvaluation.duplicateCount,0) As userDuplicateCount, " \
f"User.id AS user " \
f"FROM MissionCandidate " \
f"LEFT JOIN MissionCandidateEvaluation ON (MissionCandidate.missionCandidateIndex =MissionCandidateEvaluation.missionCandidateIndex and MissionCandidateEvaluation.userIndex = {userIndex})" \
f"JOIN User ON MissionCandidate.userIndex = User.userIndex " \
f"ORDER BY MissionCandidate.likes DESC LIMIT {missionCandidateCount}, 20"
try:
db.curs.execute(sql)
rows = db.curs.fetchall()
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
success = {'success': False}
print(rows)
db.dbDisconnect()
return json.dumps(rows, default=json_default).encode('utf-8')
def evaluation():
'''
좋아요나 싫어요, 중복체크를 늘리거나 줄인다.
매개변수 : userIndex, missionCandidateIndex, which => likes나 dislikes, count 중 하나의 값이 들어가 있음, value = 1 or -1
which : 1이면 likes, 2이면 dislikes, 3이면 count
(하나 늘리거나 줄이거나 그대로) 줄이는 경우는 클릭했다가 다시 클릭할 때
:return: success : true or false
'''
print("\n\nevaluation 호출\n\n")
userIndex = request.form['userIndex']
missioncandidateIndex = request.form['missionCandidateIndex']
which = int(request.form['which'])
if which == 1: # likes
likes = int(request.form['value'])
dislikes = 0
count = 0
elif which == 2: # dislikes
dislikes = int(request.form['value'])
likes = 0
count = 0
elif which == 3: # duplicate count
count = int(request.form['value'])
likes = 0
dislikes = 0
else:
print("which 매개변수 값 오류")
return json.dumps({
'success': False
}, default=json_default).encode('utf-8')
db = DB()
db.dbConnect()
db.setCursorDic()
#MissionCandidate 테이블의 likes, dislikes, count 값을 바꾼다.
sql = f"UPDATE MissionCandidate SET likes = likes+{likes}, dislikes = dislikes+{dislikes}, duplicateCount = duplicateCount + {count} WHERE missionCandidateIndex = {missioncandidateIndex}"
try:
db.curs.execute(sql)
success = {'success': True}
print("\nMissionCandidate 테이블 좋아요, 싫어요 중복 바꾸기 완료\n")
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
success = {'success': False}
db.dbDisconnect()
return json.dumps(success, default=json_default).encode('utf-8')
# MissionCandidateEvaluation 테이블에 likes, dislikes, duplicateCount 값을 넣는다.
# 0은 아직 평가 안 됨. 1은 평가 함( 좋아요 누름, 싫어요 누름, 등 )
id = userIndex + "." + missioncandidateIndex
sql = f"INSERT INTO MissionCandidateEvaluation (id, missionCandidateIndex, userIndex, likes,dislikes,duplicateCount) " \
f"VALUES ({id}, {missioncandidateIndex}, {userIndex}, {likes}, {dislikes}, {count}) ON DUPLICATE KEY " \
f"UPDATE likes = likes+{likes}, dislikes = dislikes + {dislikes}, duplicateCount = duplicateCount+{count}"
try:
db.curs.execute(sql)
db.conn.commit()
success = {'success': True}
print("\nMissionCandidateEvaluation 바꾸기 완료\n")
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
success = {'success': False}
db.dbDisconnect()
return json.dumps(success, default=json_default).encode('utf-8')
def updateMissionKing():
print("\nupdateMissionKing\n")
db = DB()
db.dbConnect()
db.setCursorDic()
sql = "SELECT userIndex, missionCount, grade FROM User ORDER BY missionCount DESC LIMIT 0, 10 "
try:
db.curs.execute(sql)
rows = db.curs.fetchall()
print("\n\n")
print(rows)
print("\n\n")
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
success = {'success': 'False'}
#미션왕 넣기
for i in range(len(rows)):
ranking = i + 1
row = rows[i]
which = 1 #1이면 미션왕 0이면 추천왕
id = str(which) + "." + str(ranking)
userIndex = row['userIndex']
count = row['missionCount']
emblem = "https://dailyhappiness.xyz/static/img/emblem/grade" + str(
row['grade']) + ".png"
sql = f"INSERT INTO MissionKing (idMissionKing,userIndex,which,ranking, number, emblem) " \
f"VALUES (%s,%s,%s,%s,%s,%s) " \
f"ON DUPLICATE KEY UPDATE userIndex = %s, number = %s, emblem=%s"
try:
db.curs.execute(sql, (id, userIndex, which, ranking, count, emblem,
userIndex, count, emblem))
db.conn.commit()
success = {'success': 'True'}
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
db.conn.rollback()
success = {'success': 'False'}
sql = "SELECT userIndex, missionCandidateCount, grade FROM User ORDER BY missionCandidateCount DESC LIMIT 0, 10 "
try:
db.curs.execute(sql)
rows2 = db.curs.fetchall()
print("\n\n")
print(rows2)
print("\n\n")
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
success = {'success': 'False'}
for i in range(len(rows2)):
ranking = i + 1
row = rows2[i]
which = 0 #1이면 미션왕 0이면 추천왕
id = str(which) + "." + str(ranking)
userIndex = row['userIndex']
count = row['missionCandidateCount']
emblem = "https://dailyhappiness.xyz/static/img/emblem/grade" + str(
row['grade']) + ".png"
sql = f"INSERT INTO MissionKing (idMissionKing,userIndex,which,ranking, number, emblem) " \
f"VALUES (%s,%s,%s,%s,%s,%s) " \
f"ON DUPLICATE KEY UPDATE userIndex = %s, number = %s, emblem=%s"
try:
db.curs.execute(sql, (id, userIndex, which, ranking, count, emblem,
userIndex, count, emblem))
db.conn.commit()
success = {'success': 'True'}
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
db.conn.rollback()
success = {'success': 'False'}
db.dbDisconnect()
return "<h1>success : " + success['success'] + "</h1>"
def uploadReview():
print("uploadImage 호출")
db = DB()
db.dbConnect()
db.setCursorDic()
weathers = get_weekly_weather_list()
weather_category = ['sunny', 'cloudy']
weekly_weather = get_weekly_weather(weathers, weather_category)
today_idx = get_today_idx()
print(request.form)
userIndex = request.form['userIndex']
missionIndex = request.form['missionIndex']
missionRating = request.form['missionRating']
location_lat = request.form['locationlat']
location_lon = request.form['locationlon']
rs = grid(location_lat, location_lon) # x,y 좌표
now = datetime.now()
now_date = now.strftime('%Y-%m-%d')
content = request.form['content']
getTodaysWeather(rs)
weather = getWeather()
temperature = getTemperature()
sql = "SELECT grade FROM User WHERE userIndex = %s"
try:
db.curs.execute(sql, (userIndex, ))
row = db.curs.fetchone()
current_grade = row['grade']
except Exception as e:
print(e)
sql = "UPDATE User SET missionCount = missionCount+1, isWeekFirst = 0 WHERE userIndex = %s"
try:
db.curs.execute(sql, (userIndex, ))
except Exception as e:
print(e)
db.conn.rollback()
success = {'success': False}
return success
sql = "UPDATE User " \
"SET grade= " \
"CASE " \
"WHEN missionCount BETWEEN 0 AND 2 THEN 1 " \
"WHEN missionCount BETWEEN 2 AND 3 THEN 2 " \
"WHEN missionCount BETWEEN 3 AND 4 THEN 3 " \
"WHEN missionCount BETWEEN 4 AND 5 THEN 4 " \
"WHEN missionCount BETWEEN 5 AND 6 THEN 5 " \
"WHEN missionCount BETWEEN 6 AND 7 THEN 6 " \
"WHEN missionCount BETWEEN 7 AND 8 THEN 7 " \
"WHEN missionCount BETWEEN 211 AND 240 THEN 8 " \
"WHEN missionCount BETWEEN 241 AND 270 THEN 9 " \
"WHEN missionCount BETWEEN 271 AND 300 THEN 10 " \
"ELSE 11 " \
"END " \
"WHERE userIndex = %s;"
try:
db.curs.execute(sql, (userIndex, ))
success = {'success': True}
except Exception as e:
print(e)
db.conn.rollback()
success = {'success': False}
return success
sql = "SELECT grade FROM User WHERE userIndex = %s"
try:
db.curs.execute(sql, (userIndex, ))
row = db.curs.fetchone()
after_grade = row['grade']
except Exception as e:
print(e)
db.conn.rollback()
if current_grade != after_grade:
if row['grade'] == 2:
success['level-up'] = "회색 클로버로 레벨업 했습니다."
elif row['grade'] == 3:
success['level-up'] = "갈색 클로버로 레벨업 했습니다."
elif row['grade'] == 4:
success['level-up'] = "연노랑 클로버로 레벨업 했습니다."
elif row['grade'] == 5:
success['level-up'] = "에메랄드 클로버로 레벨업 했습니다."
elif row['grade'] == 5:
success['level-up'] = "파랑 클로버로 레벨업 했습니다."
elif row['grade'] == 5:
success['level-up'] = "선홍 클로버로 레벨업 했습니다."
elif row['grade'] == 6:
success['level-up'] = "연보라 클로버로 레벨업 했습니다."
elif row['grade'] == 7:
success['level-up'] = "하늘 클로버로 레벨업 했습니다."
elif row['grade'] == 8:
success['level-up'] = "노랑 클로버로 레벨업 했습니다."
sql = "INSERT INTO MissionEvaluation(evaluationIndex, user, mission, rating, weather, date, comment, picture, temperature) VALUES(%s, %s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE rating=%s, weather=%s, date = %s,comment=%s,picture=%s,temperature=%s"
print(type(userIndex + "." + missionIndex), type(userIndex),
type(missionIndex), type(missionRating), type(weather),
type(now_date), type(content), type(filename), type(temperature))
try:
db.curs.execute(
sql,
(userIndex + "." + missionIndex, userIndex, missionIndex,
missionRating, weather, now_date, content, filename, temperature,
missionRating, weather, now_date, content, filename, temperature))
db.conn.commit()
success['success'] = True
except Exception as e:
print(e)
success['success'] = False
print(success)
#update_user_applicable_missions(int(userIndex), np.int64(missionIndex), 'done', today_idx, weekly_weather)
db.dbDisconnect()
return json.dumps(success).encode('utf-8')
def writeSurveyReview():
print("write Survey Review.py")
userIndex = request.form['userIndex']
missionID = request.form['missionID']
rating = request.form['rating']
isLast = int(request.form['isLast'])
print("islast", isLast)
id = str(userIndex) + "." + str(missionID)
getTodaysWeather({'x': 59, 'y': 125})
todaysWeather = getWeather()
todaysTemperature = getTemperature()
target_user_id = int(request.form['userIndex'])
db = DB()
db.dbConnect()
db.setCursorDic()
sql ="INSERT INTO MissionEvaluation (evaluationIndex, user, mission,rating, weather, temperature) " \
"VALUES (%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE " \
"rating=%s, weather=%s, temperature=%s"
try:
db.curs.execute(
sql, (id, userIndex, missionID, rating, todaysWeather,
todaysTemperature, rating, todaysWeather, todaysTemperature))
db.conn.commit()
row = {'end': 0}
except Exception as e:
print(e)
if isLast != 0:
print("isisLast", isLast)
sql = "UPDATE User SET didSurvey=1 WHERE userIndex = %s"
try:
db.curs.execute(sql, (userIndex, ))
db.conn.commit()
row = {'end': 1}
except Exception as e:
print(e)
sql = "SELECT user, mission, weather,temperature, rating FROM MissionEvaluation WHERE user = %s"
try:
db.curs.execute(sql, (userIndex, ))
mission_evaluation_list_db = db.curs.fetchall()
except mysql.connector.Error as e:
print("Error %s", e)
mission_evaluation_df = pd.DataFrame(
data=mission_evaluation_list_db,
columns=['user', 'mission', 'weather', 'temperature', 'rating'])
sql = "SELECT count(*) as cnt FROM MissionEvaluation"
try:
db.curs.execute(sql)
_cnt = db.curs.fetchone()
except mysql.connector.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
"""
#user_id = list(mission_evaluation_df.loc[:, 'user'])
mission_list = list(mission_evaluation_df.loc[:, 'mission'])
weather_list = list(mission_evaluation_df.loc[:, 'weather'])
temperature_list = list(mission_evaluation_df.loc[:, 'temperature'])
rating_list = list(mission_evaluation_df.loc[:, 'rating'])
data_num = len(rating_list)
#data_num = _cnt['cnt']
user_rating_init(target_user_id, mission_list, weather_list, temperature_list, rating_list, data_num)
"""
print("write_review> calculating_R_hat")
calculate_R_hat(0)
print("write_review> R_hat_complete")
return json.dumps(row).encode('utf-8')