def get_pagenation_info(type_idx, page, user_idx): if type_idx == 0: sql = '''SELECT count(*) mess_num FROM message WHERE receiver_no = %s and mess_hidden = 0''' else: sql = '''SELECT count(*) mess_num FROM message WHERE sender_no = %s and mess_send_hidden = 0''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx)) result = cursor.fetchone() count = result[0] page_count = count // 10 if page_count % 10 > 0: page_count += 1 min = ((int(page) - 1) // 10) * 10 + 1 max = min + 9 if max > page_count: max = page_count prev = min - 1 next = max + 1 conn.close() return page_count, min, max, prev, next
def get_content(board_idx, content_idx): sql = '''SELECT b_class, b_title, b_text, mem_nic, b_date, x.mem_no, b_cost FROM board x, member y WHERE x.mem_no = y.mem_no and x.b_no = %s and x.b_class = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (content_idx, board_idx)) result = cursor.fetchone() data_dic = {} data_dic['user_name'] = result[3] data_dic['content_subject'] = result[1] data_dic['content_text'] = result[2] data_dic['content_file'] = None data_dic['content_idx'] = content_idx data_dic['content_board_idx'] = result[0] data_dic['content_writer_idx'] = result[5] data_dic['content_date'] = result[4] data_dic['board_cost'] = result[6] conn.close() return data_dic
def store_item_list(store_idx): sql = '''select item_no, item_name, item_cost, bonus_gold, detail, image_url from item_list where store_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, store_idx) result = cursor.fetchall() item_list = [] for row in result: temp_dic = {} temp_dic['item_idx'] = row[0] temp_dic['item_name'] = row[1] temp_dic['item_cost'] = row[2] temp_dic['bonus_gold'] = row[3] temp_dic['detail'] = row[4] temp_dic['image_url'] = row[5] item_list.append(temp_dic) conn.close() return item_list
def check_identity_return_id(mem_name, mem_phone, mem_birth, mem_sex): sql = '''SELECT mem_no FROM member_info WHERE (mem_name = %s and mem_phone = %s and mem_birth = %s and mem_sex = %s)''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (mem_name, mem_phone, mem_birth, mem_sex)) result = cursor.fetchone() if result == None: conn.close() return 'NO' else: sql = '''SELECT mem_id FROM member WHERE mem_no = %s''' cursor.execute(sql, (result[0])) result = cursor.fetchone() find_id = result[0] conn.close() return find_id
def random_message_send_pro(count, mess_text, sender_no, item_name): conn = get_connection() cursor = conn.cursor() count = int(count) sql = '''select mem_no from member order by rand() limit %s''' cursor.execute(sql, count) row = cursor.fetchall() sql = '''DELETE FROM inventory WHERE item_name = %s AND mem_no = %s LIMIT 1''' cursor.execute(sql, (item_name, sender_no)) sql = "insert into message(mess_text, mess_date, sender_no, receiver_no) VALUES('" + str( mess_text) + "', NOW(), " + str(sender_no) + ", " + str( row[0][0]) + ")" for i in row[1:]: sql += ", ('" + str(mess_text) + "', NOW(), " + str( sender_no) + ", " + str(i[0]) + ")" cursor.execute(sql) conn.commit() conn.close() return 'OK'
def message_list(type_idx, user_idx, page): if type_idx == 0: sql = '''SELECT MES.mess_no, MES.mess_text, MES.mess_read, MES.mess_date, MEM.mem_nic sender_nic FROM message MES, member MEM WHERE receiver_no = %s AND MEM.mem_no = sender_no and mess_hidden = 0 ORDER BY mess_no DESC LIMIT 10 OFFSET %s''' else: sql = '''SELECT MES.mess_no, MES.mess_text, MES.mess_read, MES.mess_date, MEM.mem_nic receiver_nic FROM message MES, member MEM WHERE sender_no = %s AND MEM.mem_no = receiver_no and mess_send_hidden = 0 ORDER BY mess_no DESC LIMIT 10 OFFSET %s''' conn = get_connection() cursor = conn.cursor() start = (page - 1) * 10 cursor.execute(sql, (user_idx, start)) result_list = cursor.fetchall() conn.close() data_list = [] for row in result_list: temp_dic = {} temp_dic['message_idx'] = row[0] temp_dic['message_text'] = row[1] temp_dic['message_read'] = row[2] temp_dic['message_date'] = row[3] temp_dic['message_receiver_nic'] = row[4] data_list.append(temp_dic) return data_list
def get_pagenation_info(page, user_idx) : sql = '''SELECT count(*) FROM betting WHERE mem_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx)) result = cursor.fetchone() count = result[0] page_count = count // 10 if page_count % 10 > 0 : page_count += 1 min = ((int(page) - 1) // 10) * 10 + 1 max = min + 9 if max > page_count : max = page_count prev = min - 1 next = max + 1 conn.close() return page_count, min, max, prev, next
def user_info(user_idx) : sql = '''select x.mem_id, x.mem_nic, x.mem_gold, x.mem_silver, x.mem_mil, y.mem_phone, y.mem_name, mem_exp from member x, member_info y where x.mem_no = y.mem_no and x.mem_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx)) result = cursor.fetchall() user_info = {} user_info['user_id'] = result[0][0] user_info['user_nickname'] = result[0][1] user_info['user_gold'] = result[0][2] user_info['user_silver'] = result[0][3] user_info['user_mil'] = result[0][4] user_info['user_phone'] = result[0][5] user_info['user_name'] = result[0][6] user_info['user_lev'] = int(result[0][7]/10) conn.close() return user_info
def use_change_nickname(user_idx, change_nickname) : sql = '''select inv_no from inventory where mem_no = %s and item_name = "닉네임 변경권"''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchone() if result == None : conn.close() return 'NO' sql ='''DELETE FROM inventory WHERE item_name = "닉네임 변경권" AND mem_no = %s LIMIT 1''' cursor.execute(sql, (user_idx)) sql = '''update member set mem_nic = %s where mem_no = %s''' cursor.execute(sql, (change_nickname, user_idx)) conn.commit() conn.close() return change_nickname
def usage_history(user_idx, type, page) : start = (page -1) * 10 if type == 0 : sql = '''SELECT X.slv_date, Y.usage_detail, X.slv_price FROM silver_report X, silver_detail Y WHERE X.mem_no = %s AND X.slv_usage = Y.slv_usage ORDER BY X.slv_no DESC LIMIT 10 OFFSET %s''' elif type == 1 : sql = '''SELECT X.gld_date, Y.usage_detail, X.gld_price FROM gold_report X, gold_detail Y WHERE X.mem_no = %s AND X.gld_usage = Y.gld_usage ORDER BY X.gld_no DESC LIMIT 10 OFFSET %s''' elif type == 2 : sql = '''SELECT X.mil_date, Y.usage_detail, X.mil_price FROM mileage_report X, mileage_detail Y WHERE X.mem_no = %s AND X.mil_usage = Y.mil_usage ORDER BY X.mil_no DESC LIMIT 10 OFFSET %s''' elif type == 3 : pass conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, start)) result = cursor.fetchall() usage_history = [] for i in result : data_list = {} data_list['date'] = i[0] data_list['detail'] = i[1] data_list['price'] = i[2] usage_history.append(data_list) conn.close() return usage_history
def reply_list(content_idx): sql = '''select x.r_text, x.r_date, y.mem_nic, y.mem_no, x.r_no, y.mem_icon from reply x, member y where x.r_hidden = 0 and x.b_no = %s and x.mem_no = y.mem_no''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (content_idx)) result_list = cursor.fetchall() conn.close() data_list = [] for row in result_list: temp_dic = {} temp_dic['reply_text'] = row[0] temp_dic['reply_date'] = row[1] temp_dic['reply_nickname'] = row[2] temp_dic['reply_writer_idx'] = row[3] temp_dic['reply_idx'] = row[4] temp_dic['user_icon'] = row[5] data_list.append(temp_dic) return data_list
def add_reply(r_text, mem_no, b_no): sql = '''INSERT INTO reply(r_text, r_date, mem_no, b_no) VALUES (%s, now(),%s, %s)''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (r_text, mem_no, b_no)) sql = '''select mem_reply from mem_daily where mem_no = %s''' cursor.execute(sql, (mem_no)) result = cursor.fetchone() if result[0] < 5: sql = '''UPDATE mem_daily x, member y SET x.mem_reply = x.mem_reply + 1, y.mem_mil = y.mem_mil + 2 WHERE x.mem_no = %s and y.mem_no = %s''' cursor.execute(sql, (mem_no, mem_no)) sql = '''INSERT INTO mileage_report(mil_date, mil_price, mil_usage, mem_no) VALUES(NOW(), 2, 1, %s)''' cursor.execute(sql, mem_no) session['user_mil'] += 2 conn.commit() conn.close()
def purchasing_ability(user_idx, content_idx): sql = '''SELECT x.mem_gold, y.b_cost FROM member x, board y WHERE x.mem_no = %s and y.b_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, content_idx)) result = cursor.fetchall() sql2 = '''select mem_exp from member_info where mem_no = %s''' cursor.execute(sql2, (user_idx)) result2 = cursor.fetchone() conn.close() user_gold = result[0][0] content_cost = result[0][1] if result2[0] >= 50: if user_gold >= content_cost: return content_cost else: return 'NO' else: return '3'
def purchasing_content(board_cost, user_idx, content_idx): sql = '''UPDATE member SET mem_gold = mem_gold + %s WHERE mem_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (int(-board_cost), user_idx)) sql = '''INSERT INTO gold_report(gld_date, gld_price, gld_usage, mem_no) VALUES(NOW(), %s, 2, %s)''' cursor.execute(sql, (int(-board_cost), user_idx)) sql = '''INSERT INTO board_access(b_no, mem_no) VALUES (%s, %s)''' cursor.execute(sql, (content_idx, user_idx)) sql = '''SELECT mem_gold FROM member WHERE mem_no = %s''' cursor.execute(sql, (user_idx)) remain_gold = cursor.fetchone() sql = '''UPDATE member M SET mem_gold = mem_gold + (SELECT b_cost FROM board WHERE b_no = %s) WHERE M.mem_no = (SELECT B.mem_no FROM board B WHERE b_no = %s)''' cursor.execute(sql, (content_idx, content_idx)) sql = '''INSERT INTO gold_report(gld_date, gld_price, gld_usage, mem_no) VALUES(NOW(), (SELECT b_cost FROM board WHERE b_no = %s), 2, (SELECT mem_no FROM board WHERE b_no = %s))''' cursor.execute(sql, (content_idx, content_idx)) conn.commit() conn.close() return remain_gold[0]
def check_identity_return_pw(mem_id, mem_name, mem_phone, mem_birth, mem_sex): sql = '''SELECT mem_no FROM member_info WHERE ((select mem_no from member where mem_id = %s)=mem_no) and mem_name = %s and mem_phone = %s and mem_birth = %s and mem_sex = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (mem_id, mem_name, mem_phone, mem_birth, mem_sex)) result = cursor.fetchone() if result == None: conn.close() return 'NO' else: import string import random string_pool = string.ascii_letters + string.digits pw = "" for __ in range(10): pw += random.choice(string_pool) sql = "UPDATE member SET mem_pw = %s WHERE mem_no = %s" hash = generate_password_hash(pw) cursor.execute(sql, (hash, result[0])) conn.commit() conn.close() return pw
def user_item_list(user_idx) : sql = '''SELECT RAW.item_no, RAW.item_num, image_url, detail, RAW.item_name FROM (SELECT item_no, item_name, count(*) item_num FROM inventory WHERE mem_no = %s GROUP BY item_name) RAW LEFT JOIN (SELECT item_no, image_url, detail, item_name FROM item_list) RAW2 ON RAW2.item_no = RAW.item_no''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql,(user_idx)) result_list = cursor.fetchall() item_list = [] for row in result_list : temp_dic = {} temp_dic['item_idx'] = row[0] temp_dic['item_num'] = row[1] temp_dic['image_url'] = row[2] temp_dic['detail'] = row[3] temp_dic['item_name'] = row[4] item_list.append(temp_dic) conn.close() return item_list
def free_gold(user_idx) : sql = '''select mem_gold, mem_free_gold from member x join(select mem_no, mem_free_gold from mem_daily) a on a.mem_no = x.mem_no where x.mem_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchone() if result[0] == 0 : if result[1] > 2 : sql2 = 'update mem_daily set mem_free_gold = mem_free_gold + 1 where mem_no = %s' cursor.execute(sql2, user_idx) sql3 = 'update member set mem_gold = mem_gold + 10000 where mem_no = %s' cursor.execute(sql3, user_idx) sql4 = "INSERT INTO gold_report(gld_date, gld_price, gld_usage, mem_no) VALUES(NOW(), (10000, 5, %s)" cursor.execute(sql4, (user_idx)) conn.commit() conn.close() return 'OK' else : conn.close() return '2' else : conn.close() return '3'
def purchasing_silver_item(user_idx, item_idx): sql = '''select mem_silver, y.item_cost from member, item_list y where mem_no = %s and item_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, item_idx)) result = cursor.fetchone() user_silver = result[0] item_price = result[0] if user_silver < item_price: conn.close() return 'NO' else: sql = '''UPDATE member SET mem_silver = mem_silver - (SELECT item_cost FROM item_list WHERE item_no = %s), mem_gold = mem_gold + (SELECT bonus_gold FROM item_list WHERE item_no = %s) WHERE mem_no = %s''' cursor.execute(sql, (item_idx, item_idx, user_idx)) sql = '''INSERT INTO silver_report(slv_date, slv_price, slv_usage, mem_no) VALUES(NOW(), - (SELECT item_cost FROM item_list WHERE item_no = %s), 1, %s)''' cursor.execute(sql, (item_idx, user_idx)) if item_idx != '0' and item_idx != '1' and item_idx != '2': sql = '''INSERT INTO inventory(item_name, purchase_time ,mem_no, item_no) VALUES((SELECT item_name FROM item_list WHERE item_no = %s), NOW(), %s, %s)''' cursor.execute(sql, (item_idx, user_idx, item_idx)) if item_idx == '6': sql = '''INSERT INTO gold_report(gld_date, gld_price, gld_usage, mem_no) VALUES(NOW(), (SELECT bonus_gold FROM item_list WHERE item_no = %s), 0, %s)''' cursor.execute(sql, (item_idx, user_idx)) else: sql = '''INSERT INTO gold_report(gld_date, gld_price, gld_usage, mem_no) VALUES(NOW(), (SELECT bonus_gold FROM item_list WHERE item_no = %s), 0, %s)''' cursor.execute(sql, (item_idx, user_idx)) sql = "update member set mem_icon = %s where mem_no = %s" cursor.execute(sql, (item_idx, user_idx)) sql = '''select mem_silver, mem_gold from member where mem_no = %s''' conn.commit() cursor.execute(sql, (user_idx)) result = cursor.fetchall() conn.close() return result
def user_ip(user_idx, ip): if ip == '121.138.72.230' or ip == '39.7.57.143': pass else: sql = "insert into mem_ip (mem_no, mem_ip, login_date) values ((select mem_no from member where mem_no = %s), %s, NOW())" conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, ip)) conn.commit() conn.close()
def user_secession_cancel(user_idx): sql = "update member set mem_secession = NULL where mem_no = %s" conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) conn.commit() conn.close() return 'OK'
def reply_delete(r_no): sql = '''UPDATE reply SET r_hidden = 1 WHERE (r_no = %s)''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (r_no)) conn.commit() conn.close()
def delete_content(content_idx): sql = '''UPDATE board SET b_hidden = 1 WHERE b_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (content_idx)) conn.commit() conn.close()
def add_user(mem_id, mem_pw, mem_nic, gold): sql = '''INSERT INTO member(mem_id, mem_pw, mem_nic, mem_gold) VALUES(%s, %s, %s, %s)''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (mem_id, mem_pw, mem_nic, gold)) conn.commit() conn.close()
def change_password(user_idx, password): sql = "UPDATE member SET mem_pw = %s WHERE mem_no = %s" hash = generate_password_hash(password) conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (hash, user_idx)) conn.commit() conn.close() return 'OK'
def user_secession_check(user_idx): sql = '''select if(day(NOW()) - day(mem_secession)<7, 1, 2) from member where mem_no = %s and mem_secession is not null''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchone() conn.close() return result
def betting_check(user_idx) : sql = "select mem_gold from member where mem_no = %s" conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchone() conn.close() return result[0]
def betting_cancel(user_idx, bet_no) : sql = '''select mem_bet_cancel from mem_daily where mem_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchone() if result[0] > 2 : conn.close() return '2' else : sql = "select x.bet_date, min(y.game_date) from betting x, betting_detail y where x.bet_no = %s and y.bet_no = %s" cursor.execute(sql, (bet_no, bet_no)) result = cursor.fetchone() bet_time = result[0] game_time = result[1] if (datetime.datetime.today() - bet_time).seconds < 600 : if datetime.datetime.now() >= game_time : conn.close() return '4' sql = "update mem_daily set mem_bet_cancel = mem_bet_cancel +1 where mem_no = %s" cursor.execute(sql, user_idx) sql3 = "update member set mem_gold = mem_gold + (select bet_gold from betting where bet_no = %s) where mem_no = %s" cursor.execute(sql3, (bet_no, user_idx)) sql4 = "INSERT INTO gold_report(gld_date, gld_price, gld_usage, mem_no) VALUES(NOW(), (select bet_gold from betting where bet_no = %s), 4, %s)" cursor.execute(sql4, (bet_no, user_idx)) sql2 = "DELETE FROM betting WHERE (bet_no = %s)" cursor.execute(sql2, bet_no) conn.commit() conn.close() return 'OK' else : conn.close() return '3'
def get_pagenation_info(board_idx, page): if board_idx == 3 and session['admin'] != 1: sql = '''SELECT count(*) FROM board WHERE b_class = %s and b_hidden = 0 and mem_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (board_idx, session['user_idx'])) result = cursor.fetchone() else: sql = '''SELECT count(*) FROM board WHERE b_class = %s and b_hidden = 0''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (board_idx)) result = cursor.fetchone() count = result[0] page_count = count // 10 if page_count % 10 > 0: page_count += 1 min = ((int(page) - 1) // 10) * 10 + 1 max = min + 9 if max > page_count: max = page_count prev = min - 1 next = max + 1 conn.close() return page_count, min, max, prev, next
def purchasing_mileage_item(user_idx, item_idx): sql = '''select mem_mil, y.item_cost from member, item_list y where mem_no = %s and item_no = %s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, item_idx)) result = cursor.fetchone() user_mileage = result[0] item_price = result[0] if user_mileage < item_price: conn.close() return 'NO' else: sql = '''UPDATE member SET mem_mil = mem_mil - (SELECT item_cost FROM item_list WHERE item_no = %s), mem_gold = mem_gold + (SELECT bonus_gold FROM item_list WHERE item_no = %s) WHERE mem_no = %s''' cursor.execute(sql, (item_idx, item_idx, user_idx)) conn.commit() sql = '''INSERT INTO inventory(item_name, purchase_time ,mem_no, item_no) VALUES((SELECT item_name FROM item_list WHERE item_no = %s), NOW(), %s, %s)''' cursor.execute(sql, (item_idx, user_idx, item_idx)) conn.commit() sql = '''INSERT INTO mileage_report(mil_date, mil_price, mil_usage, mem_no) VALUES(NOW(),-(SELECT item_cost FROM item_list WHERE item_no = %s), 3, %s)''' cursor.execute(sql, (item_idx, user_idx)) conn.commit() sql = '''INSERT INTO gold_report(gld_date, gld_price, gld_usage, mem_no) VALUES(NOW(), (SELECT bonus_gold FROM item_list WHERE item_no = %s), 0, %s)''' cursor.execute(sql, (item_idx, user_idx)) conn.commit() sql = '''select mem_mil, mem_gold from member where mem_no = %s''' cursor.execute(sql, (user_idx)) result = cursor.fetchall() conn.close() return result
def get_board_name(board_idx): sql = '''select b_title from board where b_no=%s''' conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (board_idx)) result = cursor.fetchone() board_name = result conn.close() return board_name