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
