def applying(self, args):
id = args["id"]
list = []
conn = util.open_db()
with conn.cursor() as cursor:
# 0 1 2 3 4 5
applying_alba = "select distinct board.no,board.storename,board.start_time,board.end_time,board.local,board.local_sub from board left join getAlba on board.no = getAlba.no where getAlba.id =\'%s\'" %(id)
cursor.execute(applying_alba)
rows = cursor.fetchall()
for row in rows:
upper = util.get_local_name(cursor, util.get_upper_local(cursor, row[4]))
lower = util.get_local_name(cursor, row[4])
local = upper + " " + lower + " " + row[5]
list.append({
"no": row[0],
"storename": row[1],
"start_time": row[2],
"end_time": row[3],
"local": local
})
util.close_db(conn)
response = {"applying": list}
print(response)
return response
def supporter_list(self, args):
list = []
id = args["id"]
conn = util.open_db()
with conn.cursor() as cursor:
get_board_query = "select no, storename from board where id=\'%s\';" %(id)
cursor.execute(get_board_query)
rows = cursor.fetchall()
for row in rows:
# 0 1 2 3
applyer_query = "select distinct member.id, member.gender, member.local_main, member.phone from member left join getAlba on member.id = getAlba.id where getAlba.no = %d" %(row[0])
cursor.execute(applyer_query)
x = cursor.fetchall()
for y in x:
upper = util.get_local_name(cursor, util.get_upper_local(cursor, y[2]))
lower = util.get_local_name(cursor, y[2])
local = upper + " " + lower
list.append({
"no": row[0],
"storename" : row[1],
"id": y[0],
"gender": util.get_gender_name(cursor, y[1]),
"local": local,
"phone": y[3]
})
response = {"applyer":list}
util.close_db(conn=conn)
return response
def test_select(self):
conn = connect_db()
id = 0
cursor = conn.execute('select status from QSBK_AUTHOR where id = %d' % id)
print(cursor)
#print(cursor.arraysize)
for row in cursor:
status = row[0]
assert status is None
print(status)
close_db(conn)
def test_select(self):
conn = connect_db()
id = 0
cursor = conn.execute('select status from QSBK_AUTHOR where id = %d' %
id)
print(cursor)
#print(cursor.arraysize)
for row in cursor:
status = row[0]
assert status is None
print(status)
close_db(conn)
def store_anonymous_jokes(jokes):
results = []
cur_time = int(time.time())
for joke in jokes:
results.append(
(joke.md5_content, 0, joke.id, joke.author, joke.num_likes,
joke.content.decode('utf-8'), u'qiushibaike', cur_time))
conn = connect_db()
print len(set([r[0] for r in results]))
conn.executemany('REPLACE INTO JOKE VALUES (?,?,?,?,?,?,?,?)', results)
conn.commit()
close_db(conn)
def get_board_info(self, args):
response = {}
board_no = int(args["no"])
conn = util.open_db()
with conn.cursor() as cursor:
query = "select * from board where no=%d;" % (board_no)
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
job = None
job_query = "select name from data_code where id=%d;" % (
row[7])
cursor.execute(job_query)
x = cursor.fetchall()
for i in x:
job = i[0]
upper_id = util.get_upper_local(cursor, row[10])
response.update({
"no":
row[0],
"title":
row[1],
"storename":
row[2],
"start_time":
row[3],
"end_time":
row[4],
"urgency":
row[5],
"job_condition":
row[6],
"job":
job,
"favorable_condition":
row[8],
"detail":
row[9],
"address":
util.get_local_name(cursor, upper_id) + " " +
util.get_local_name(cursor, row[10]) + " " + row[11],
"id":
row[12],
"phone":
row[13]
})
util.close_db(conn=conn)
return response
def get_alba(self, request):
id = request["id"]
no = request["no"]
if id == None or no == None:
return exception.UNLOGINED_USER
conn = util.open_db()
with conn.cursor() as cursor:
query = "insert into getAlba values(\'%s\', %d);" %(id,no)
cursor.execute(query)
conn.commit()
util.close_db(conn=conn)
return self.GET_ALBA_SUCCESS
def login(self, request):
id = request["id"]
pwd = request["pwd"]
isLoginSuccess = True
conn = util.open_db()
with conn.cursor() as cursor:
query = "select * from member where id = %s and pwd = %s;"
cursor.execute(query, (id, pwd))
rows = cursor.fetchall()
if len(rows) == 0:
isLoginSuccess = False
util.close_db(conn=conn)
response = {"authonization": isLoginSuccess}
return response
def get_job_list(self):
response = {}
job_list = []
conn = util.open_db()
with conn.cursor() as cursor:
query = "select name from data_code where type=%s;"
cursor.execute(query, '직종')
rows = cursor.fetchall()
for row in rows:
job_list.append(row[0])
util.close_db(conn=conn)
response.update({"job_list": job_list})
return response
def get_local_list(self, local_name):
response = {}
local_list = []
conn = util.open_db()
with conn.cursor() as cursor:
t_local_id = util.get_local_id(cursor=cursor,
local_name=local_name)
query = "select local_name from local where local_upper=%d;" % (
t_local_id)
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
local_list.append(row[0])
util.close_db(conn=conn)
response.update({"local_list": local_list})
return response
def regist_board(self, request):
conn = util.open_db()
job = None
local = None
title = request["title"]
storename = request["storename"]
start_time = request["start_time"]
end_time = request["end_time"]
urgency = int(request["urgency"])
job_condition = request["job_condition"]
job_name = request["job"]
favorable_condition = request["favorable_condition"]
detail = request["detail"]
local_name = request["local"]
local_sub = request["local_sub"]
id = request["id"]
phone = request["phone"]
with conn.cursor() as cursor:
job_query = "select id from data_code where name=\'%s\' and type='직종';" % (
job_name)
cursor.execute(job_query)
x = cursor.fetchall()
job = x[0][0]
local_query = "select local_id from local where local_name=\'%s\';" % (
local_name)
cursor.execute(local_query)
x = cursor.fetchall()
local = x[0][0]
query = "insert into board values(null, \'%s\', \'%s\', \'%s\', \'%s\', %d, \'%s\', %d, \'%s\', \'%s\', %d, \'%s\', \'%s\', \'%s\');" % (
title, storename, start_time, end_time, urgency, job_condition,
job, favorable_condition, detail, local, local_sub, id, phone)
cursor.execute(query)
conn.commit()
util.close_db(conn=conn)
def select_home_with_unfilter(self, local_name, job_name):
conn = util.open_db()
with conn.cursor() as cursor:
simple_board_list = {}
query = "select storename, local, local_sub, start_time, end_time, urgency, no from board "
filter = self.adjust_filter(cursor=cursor,
local_name=local_name,
job_name=job_name)
if filter != None:
query = query + filter
query = query + ";"
print(query)
cursor.execute(query)
rows = cursor.fetchall()
simple_board_list = self.set_json(cursor, rows, simple_board_list)
util.close_db(conn=conn)
return simple_board_list
async def on_ready():
print("Deployment Successful")
dbdat = open("db.dat", "r")
con = psycopg2.connect(dbdat.read().strip(), sslmode='require')
#con = psycopg2.connect(os.environ["DATABASE_URL"], sslmode='require')
cur = con.cursor()
cur.execute("SELECT * FROM current")
current = cur.fetchall()[0]
handler.set_current_puzzle(current)
print(handler.get_current_puzzle())
# answer = "tests"
# command = "UPDATE answers SET answers = array_append(answers, \'" + answer + "\') WHERE url = \'" + "test" + "\'"
# print(command)
# cur.execute(command)
#command = "SELECT answers FROM answers WHERE url = \'" + current[0] + "\'"
#statement = "INSERT INTO answers(url, answers) VALUES (\'" + "test" + "\', ARRAY[]::TEXT[])"
#print(statement)
#cur.execute(statement)
command = "SELECT answers FROM answers WHERE url = \'test\'"
print(command)
cur.execute(command)
grabbed_answers = cur.fetchall()
print(grabbed_answers)
if grabbed_answers:
answers = grabbed_answers[0][0]
handler.set_guesses(answers)
print(answers)
close_db(con, cur)
await client.change_presence(activity=discord.Game(name="python3.6"))
def sign_up(self, request):
id = request["id"]
pwd = request["pwd"]
name = request["name"]
gender_name = request["gender"]
birthday = request["birthday"]
phone = request["phone"]
local_main = request["local_main"]
local_sub = request["local_sub"]
job_name = request["job"]
conn = util.open_db()
with conn.cursor() as cursor:
if self.check_user_duplication(cursor=cursor,
name=name,
birthday=birthday,
phone=phone):
util.close_db(conn=conn)
return exception.REGISTED_USER
if self.check_id_duplication(cursor, id):
util.close_db(conn=conn)
return exception.ID_DUPLCATION
gender = util.get_gender_code(cursor=cursor,
gender_name=gender_name)
local = util.get_local_id(cursor=cursor, local_name=local_main)
job = util.get_job_id(cursor=cursor, job_name=job_name)
query = "insert into member values(\'%s\', \'%s\', \'%s\', %d, \'%s\', \'%s\', %d, \'%s\', %d);" \
%(id, pwd, name, gender, birthday, phone, local, local_sub, job)
cursor.execute(query)
conn.commit()
util.close_db(conn=conn)
return {"message": "Regist OK"}
async def on_message(message):
channel = message.channel
if message.content == ".new":
cur.execute("SELECT * FROM puzzles")
puzzles = cur.fetchall()
handler.set_puzzles(puzzles)
if (len(puzzles) == 0):
await channel.send("No puzzles")
else:
print(puzzles)
cur.execute("SELECT * FROM solved")
solved = cur.fetchall()
handler.set_solved(rows[0])
random.seed(datetime.now())
puzzles = handler.get_puzzles()
randIndex = random.randint(0, len(puzzles))
#randIndex = 1
await channel.send(puzzles[randIndex][0])
handler.set_current_puzzle(puzzles[randIndex])
dbdat = open("db.dat", "r")
con = psycopg2.connect(dbdat.read().strip(), sslmode='require')
#con = psycopg2.connect(os.environ["DATABASE_URL"], sslmode='require')
cur = con.cursor()
cur.execute("DELETE FROM current")
statement = "INSERT INTO current (url, solution) VALUES (\'" + \
puzzles[randIndex][0] + "\',\'" + puzzles[randIndex][1] + "\')"
cur.execute(statement)
try:
statement = "INSERT INTO answers(url, answers) VALUES (\'" + puzzles[randIndex][0] + "\', ARRAY[]::TEXT[])"
print(statement)
cur.execute(statement)
except:
print("Already in db")
close_db(con, cur)
clear_answers(handler)
if message.content == ".current":
await channel.send(handler.get_current_puzzle()[0][0])
if ".answer" in message.content:
print(message.content.split())
if (message.content.split()[1] == "<ANSWER>"):
print("help line")
else:
#await channel.send(message.content.split(".answer"))
answer = message.content.split()[1]
answer.upper().replace(" ", "")
handler.get_guesses().append(answer)
dbdat = open("db.dat", "r")
con = psycopg2.connect(dbdat.read().strip(), sslmode='require')
#con = psycopg2.connect(os.environ["DATABASE_URL"], sslmode='require')
cur = con.cursor()
command = "UPDATE answers SET answers = array_append(answers,\'" + answer + "\') WHERE url = \'" + handler.get_current_puzzle()[0] + "\'"
cur.execute(command)
if answer == handler.get_current_puzzle()[1]:
await channel.send("Correct!!")
cur.execute("INSERT into solved (url) VALUES (\'" + handler.get_current_puzzle()[0] + "\')")
else:
await channel.send(handler.get_current_puzzle()[1])
close_db(con, cur)
if message.content == ".show":
for answer in handler.get_guesses():
await channel.send(answer)
if message.content == ".clear":
await channel.send("Are you sure? (Enter '.yes.clear' if so)")
if message.content == ".yes.clear":
clear_answers(handler)
if message.content == ".refresh":
await channel.send("Are you sure? (Enter '.yes.refresh' if so)")
if message.content == ".yes.refresh":
dbdat = open("db.dat", "r")
con = psycopg2.connect(dbdat.read().strip(), sslmode='require')
#con = psycopg2.connect(os.environ["DATABASE_URL"], sslmode='require')
cur = con.cursor()
# Are you sure?
cur.execute("DELETE FROM puzzles")
print("Pulling")
handler.pull_puzzles_test()
puzzles = handler.get_puzzles()
print(puzzles)
for p in puzzles:
statement = "INSERT INTO puzzles (url, solution) VALUES (\'" + p[0] + "\',\'" + p[1] + "\')"
print(statement)
cur.execute(statement)
close_db(con, cur)
if message.content == ".help":
await channel.send(help_string())
