def get_roominfo(): con = Mysql() roomindex = request.args.get("roomindex") query = "select * from ROOMINFO where roomindex = '" + roomindex + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() names = "" for i in range(int(query_result[0][7])): namequery = "select name from USERINFO where userid = '" + query_result[ 0][i + 3] + "'" con.cursor.execute(namequery) namequery_result = con.cursor.fetchall() names = names + (namequery_result[0][0]) + "," if (query_result): result = { 'result': 1, 'title': query_result[0][1], 'des': query_result[0][2], 'users': names, 'membercount': query_result[0][7] } else: result = {'result': -1, 'message': "unvalid"} con.close() return jsonify(result)
def bring_randomcode(): con = Mysql() roomindex = request.args.get("roomindex") checkquery = "select * from INVITESTRING where roomindex= '" + roomindex + "'" con.cursor.execute(checkquery) query_result = con.cursor.fetchall() # 비어있으면 생성 if not query_result: query = "insert into INVITESTRING (roomindex, randomstring) values ('" + roomindex + "', left(MD5('" + roomindex + "'),9))" con.cursor.execute(query) con.db.commit() checkquery = "select randomstring from INVITESTRING where roomindex= '" + roomindex + "'" con.cursor.execute(checkquery) finalresult = con.cursor.fetchall() if (finalresult): result = {'result': 1, 'randomstring': finalresult[0][0]} else: result = {'result': -1} con.close() return jsonify(result)
def get_calendar(): con = Mysql() roomindex = request.args.get("roomindex") date = request.args.get("date") query = "select color, title, startdate, enddate, remind, calendarindex from CALENDAR where roomindex = '" + roomindex + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() calinfo = [] if (query_result): for row in query_result: if row[2][:7] == date or row[3][:7] == date: calinfo.append({ "color": row[0], "title": row[1], "startdate": row[2], "enddate": row[3], "remind": row[4], "calendarindex": row[5] }) result = {'result': 1, 'calinfo': calinfo} else: result = {'result': -1} con.close() return jsonify(result)
def leave_room(): con = Mysql() roomindex = request.args.get("roomindex") userid = request.args.get("userid") checkquery = "select user1, user2, user3, user4, membercount from ROOMINFO where roomindex= '" + roomindex + "'" con.cursor.execute(checkquery) query_result = con.cursor.fetchall() membercount = query_result[0][4] if query_result[0][0] == userid: mynumber = 1 elif query_result[0][1] == userid: mynumber = 2 elif query_result[0][2] == userid: mynumber = 3 else: mynumber = 4 updatequery = "update ROOMINFO set user" + str( mynumber) + "= null, membercount = '" + str( membercount - 1) + "' where roomindex = '" + roomindex + "'" con.cursor.execute(updatequery) con.db.commit() updatequery1 = "update USERINFO set lastroom = NULL where userid = '" + userid + "'" con.cursor.execute(updatequery1) con.db.commit() con.close() return jsonify({'result': 1})
def get_stats(): con = Mysql() roomindex = request.args.get("roomindex") year = request.args.get("year") month = request.args.get("month") query = "select userid, weekofMonth, content, score from STATS where roomindex = '" + roomindex + "' and year = '" + year + "' and month = '" + month + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() statsinfo = [] for row in query_result: contentinfo = [] arr = row[2].split(',') for each in arr: query1 = "select title, score, donedate from TODO where todoindex = '" + each + "'" con.cursor.execute(query1) query_result1 = con.cursor.fetchall() contentinfo.append(query_result1) statsinfo.append({ "userid": row[0], "weekofMonth": row[1], "content": contentinfo, "score": row[3] }) result = {'result': 1, 'statsinfo': statsinfo} con.close() return jsonify(result)
def put_roomindex(): con = Mysql() userid = request.args.get("userid") index = request.args.get("index") query = "update USERINFO set lastroom = '" + index + "' where userid = '" + userid + "'" con.cursor.execute(query) con.db.commit() con.close() return jsonify({'result': 1})
def undo_todo(): con = Mysql() todoindex = request.args.get("todoindex") query = "update TODO set userid = 'null', donedate = 'null' where todoindex = '" + todoindex + "'" con.cursor.execute(query) con.db.commit() con.close() return jsonify({'result': 1})
def delete_board(): con = Mysql() boardindex = request.args.get("boardindex") query = "delete from BOARD where boardindex = '" + boardindex + "'" con.cursor.execute(query) con.db.commit() con.close() return jsonify({'result': 1})
def update_board(): con = Mysql() content = request.args.get("content") fixed = request.args.get("fixed") boardindex = request.args.get("boardindex") query = "update BOARD set content = '" + content + "', fixed = '" + fixed + "' where boardindex = '" + boardindex + "'" con.cursor.execute(query) con.db.commit() con.close() return jsonify({'result': 1})
def update_roominfo(): con = Mysql() roomindex = request.args.get("roomindex") roomtitle = request.args.get("title") roomdes = request.args.get("des") query = "update ROOMINFO set roomtitle = '" + roomtitle + "', roomdes = '" + roomdes + "' where roomindex = '" + roomindex + "'" con.cursor.execute(query) con.db.commit() con.close() return jsonify({'result': 1})
def delete_calendar(): con = Mysql() calindex = request.args.get("calindex") query = "delete from CALENDAR where calendarindex = '" + calindex + "'" con.cursor.execute(query) con.db.commit() con.close() return jsonify({'result': 1})
def update_userinfo(): con = Mysql() userid = request.args.get("userid") name = request.args.get("name") userpw = request.args.get("userpw") birthday = request.args.get("birthday") query = "update USERINFO set name = '" + name + "', userpw = '" + userpw + "', birthday = '" + birthday + "' where userid = '" + userid + "'" con.cursor.execute(query) con.db.commit() con.close() return jsonify({'result': 1})
def register(): con = Mysql() name = request.args.get("name") userid = request.args.get("userid") userpw = request.args.get("userpw") birthday = request.args.get("birthday") query = """insert into USERINFO (name, userid, userpw, birthday) values (%s, %s, %s, %s)""" con.cursor.execute(query, (name, userid, userpw, birthday)) con.db.commit() con.close() return jsonify({'result': 1})
def put_todo(): con = Mysql() roomindex = request.args.get("roomindex") title = request.args.get("title") duedate = request.args.get("duedate") score = request.args.get("score") remind = request.args.get("remind") query = """insert into TODO (roomindex, title, duedate, score, remind) values (%s, %s, %s, %s, %s)""" con.cursor.execute(query, (roomindex, title, duedate, score, remind)) con.db.commit() con.close() return jsonify({'result': 1})
def put_board(): con = Mysql() roomindex = request.args.get("roomindex") content = request.args.get("content") userid = request.args.get("userid") postdate = request.args.get("postdate") fixed = request.args.get("fixed") query = """insert into BOARD (roomindex, content, userid, postdate, fixed) values (%s, %s, %s, %s, %s)""" con.cursor.execute(query, (roomindex, content, userid, postdate, fixed)) con.db.commit() con.close() return jsonify({'result': 1})
def count_room(): con = Mysql() userid = request.args.get("userid") query = "select count(roomindex) from ROOMINFO where user1 = '" + userid + "' or user2 = '" + userid + "' or user3 = '" + userid + "' or user4 = '" + userid + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() if (query_result): result = {'result': 1, 'count': query_result[0][0]} else: result = {'result': -1, 'message': "unvalid"} con.close() return jsonify(result)
def get_name(): con = Mysql() userid = request.args.get("userid") query = "select name from USERINFO where userid = '" + userid + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() if (query_result): result = {'result': 1, "name": query_result[0][0]} else: result = {'result': -1, 'message': "unvalid"} con.close() return jsonify(result)
def login(): con = Mysql() userid = request.args.get("userid") userpw = request.args.get("userpw") query = "select * from USERINFO where userid = '" + userid + "' and userpw = '" + userpw + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() if (query_result): result = {'result': 1} else: result = {'result': -1, 'message': "unvalid"} con.close() return jsonify(result)
def save_calendar(): con = Mysql() roomindex = request.args.get("roomindex") color = request.args.get("color") title = request.args.get("title") startdate = request.args.get("startdate") enddate = request.args.get("enddate") remind = request.args.get("remind") memo = request.args.get("memo") query = """insert into CALENDAR (roomindex, color, title, startdate, enddate, remind, memo) values (%s, %s, %s, %s, %s, %s, %s)""" con.cursor.execute( query, (roomindex, color, title, startdate, enddate, remind, memo)) con.db.commit() con.close() return jsonify({'result': 1})
def update_todo(): con = Mysql() roomindex = request.args.get("roomindex") userid = request.args.get("userid") donedate = request.args.get("donedate") todoindex = request.args.get("todoindex") year = request.args.get("year") month = request.args.get("month") weekofMonth = request.args.get("weekofMonth") query = "update TODO set userid = '" + userid + "', donedate = '" + donedate + "' where todoindex = '" + todoindex + "'" con.cursor.execute(query) con.db.commit() scorequery = "select score from TODO where todoindex = '" + todoindex + "'" con.cursor.execute(scorequery) score_result = con.cursor.fetchall() selectquery = "select content, score from STATS where roomindex = '" + roomindex + "' and weekofMonth = '" + weekofMonth + "' and year = '" + year + "' and month = '" + month + "' and userid = '" + userid + "'" con.cursor.execute(selectquery) select_result = con.cursor.fetchall() if (select_result == ()): query = """insert into STATS (roomindex, userid, year, month, weekofMonth, score) values (%s, %s, %s, %s, %s, %s)""" con.cursor.execute(query, (roomindex, userid, year, month, weekofMonth, 0)) con.db.commit() contentstr = todoindex score = int(score_result[0][0]) else: contentstr = str(select_result[0][0]) + "," + todoindex score = int(select_result[0][1]) + int(score_result[0][0]) query1 = "update STATS set content = '" + contentstr + "', score = '" + str( score ) + "' where roomindex = '" + roomindex + "' and weekofMonth = '" + weekofMonth + "' and year = '" + year + "' and month = '" + month + "' and userid = '" + userid + "'" con.cursor.execute(query1) con.db.commit() con.close() return jsonify({'result': 1})
def get_userinfo(): con = Mysql() userid = request.args.get("userid") query = "select name, birthday from USERINFO where userid = '" + userid + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() if (query_result): result = { 'result': 1, 'name': query_result[0][0], 'birthday': query_result[0][1] } else: result = {'result': -1} con.close() return jsonify(result)
def get_rooms(): con = Mysql() userid = request.args.get("userid") query = "select roomindex, roomtitle from ROOMINFO where user1 = '" + userid + "' or user2 = '" + userid + "' or user3 = '" + userid + "' or user4 = '" + userid + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() roominfo = [] for row in query_result: roominfo.append({"index": row[0], 'title': row[1]}) if (query_result): result = {'result': 1, 'roominfo': roominfo} else: result = {'result': -1} con.close() return jsonify(result)
def get_todo(): con = Mysql() roomindex = request.args.get("roomindex") duedate = request.args.get("duedate") filter = request.args.get("filter") if filter == "0": query = "select todoindex, title, duedate, score, remind, userid, donedate from TODO where roomindex = '" + roomindex + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() elif filter == "1": query = "select todoindex, title, duedate, score, remind, userid, donedate from TODO where roomindex = '" + roomindex + "' and duedate = '" + duedate + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() else: # todo query = "select todoindex, title, duedate, score, remind, userid, donedate from TODO where roomindex = '" + roomindex + "' and duedate = '" + duedate + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() todo = [] for row in query_result: todo.append({ "index": row[0], 'title': row[1], 'duedate': row[2], 'score': row[3], 'remind': row[4], 'userid': row[5], 'donedate': row[6] }) if (query_result): result = {'result': 1, 'todo': todo} else: result = {'result': -1} con.close() return jsonify(result)
def create_room(): con = Mysql() roomtitle = request.args.get("roomtitle") roomdes = request.args.get("roomdes") user1 = request.args.get("user1") query = "insert into ROOMINFO (roomtitle, roomdes, user1, membercount) values ('" + roomtitle + "', '" + roomdes + "','" + user1 + "', '1')" con.cursor.execute(query) con.db.commit() check = "select roomindex from ROOMINFO where roomtitle = '" + roomtitle + "' and roomdes = '" + roomdes + "' and user1 ='" + user1 + "' order by roomindex DESC LIMIT 1" con.cursor.execute(check) cq = con.cursor.fetchall() roomindex = cq[0][0] updatequery = "update USERINFO set lastroom = '" + str( roomindex) + "' where userid = '" + user1 + "'" con.cursor.execute(updatequery) con.db.commit() con.close() return jsonify({'result': 1})
def get_board(): con = Mysql() roomindex = request.args.get("roomindex") query = "select boardindex, content, userid, postdate, fixed from BOARD where roomindex = '" + roomindex + "'" con.cursor.execute(query) query_result = con.cursor.fetchall() boardinfo = [] for row in query_result: boardinfo.append({ "boardindex": row[0], "content": row[1], "userid": row[2], "postdate": row[3], "fixed": row[4] }) result = {'result': 1, 'boardinfo': boardinfo} con.close() return jsonify(result)
def join_room(): con = Mysql() randomstring = request.args.get("randomstring") userid = request.args.get("userid") checkquery = "select roomindex from INVITESTRING where randomstring= '" + randomstring + "'" con.cursor.execute(checkquery) query_result = con.cursor.fetchall() roomindex = query_result[0][0] # randomstring이랑 같은 인덱스를 찾으면 if query_result: checkquery1 = "select membercount from ROOMINFO where roomindex = '" + roomindex + "'" con.cursor.execute(checkquery1) query_result1 = con.cursor.fetchall() membercount = query_result1[0][0] if membercount == 0: query = "update ROOMINFO set user1 = '" + userid + "', membercount = 1 where roomindex = '" + roomindex + "'" result = {"result": 1, "roomindex": roomindex} updatequery = "update USERINFO set lastroom = '" + roomindex + "' where userid = '" + userid + "'" con.cursor.execute(updatequery) con.db.commit() elif membercount == 1: query = "update ROOMINFO set user2 = '" + userid + "', membercount = 2 where roomindex = '" + roomindex + "'" result = {"result": 1, "roomindex": roomindex} updatequery = "update USERINFO set lastroom = '" + roomindex + "' where userid = '" + userid + "'" con.cursor.execute(updatequery) con.db.commit() elif membercount == 2: query = "update ROOMINFO set user3 = '" + userid + "', membercount = 3 where roomindex = '" + roomindex + "'" result = {"result": 1, "roomindex": roomindex} updatequery = "update USERINFO set lastroom = '" + roomindex + "' where userid = '" + userid + "'" con.cursor.execute(updatequery) con.db.commit() elif membercount == 3: query = "update ROOMINFO set user4 = '" + userid + "', membercount = 4 where roomindex = '" + roomindex + "'" result = {"result": 1, "roomindex": roomindex} updatequery = "update USERINFO set lastroom = '" + roomindex + "' where userid = '" + userid + "'" con.cursor.execute(updatequery) con.db.commit() else: result = {"result": 2, "roomindex": "full"} else: result = {"result": -1, "roomindex": "something wrong"} con.cursor.execute(query) con.db.commit() con.close() return jsonify(result)