data.py
15.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
import operator
import sqlite3
import datetime
import logging
import pickle
def byteify(input):
if isinstance(input, dict):
return {byteify(key): byteify(value) for key, value in input.iteritems()}
elif isinstance(input, list):
return [byteify(element) for element in input]
elif isinstance(input, unicode):
return input.encode('utf-8')
else:
return input
def log(message):
try:
logging.warning("{} - {}".format(datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'), message))
except:
pass
##################
## Database Calls
##################
# Converts a row into a dictionary
def dict_factory(cursor, row):
if row == None:
return None
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
def db_get_credit(member_id):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
credits = c.execute("SELECT credits FROM members WHERE member_id = ?;", (member_id,)).fetchone()
if credits:
return credits[0]
def db_buy_ticket(member_id, amount):
log("buying ticket - DB")
try:
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
credits = c.execute("SELECT credits, tickets FROM members WHERE member_id = ?;", (member_id,)).fetchone()
log(credits)
if not credits:
log('Bad account lookup, missing credits')
return False, "Unable to find your account"
if credits[1] >= 20:
return False, "You have the maximum number of tickets for this raffle"
if int(credits[0]) - int(100*amount) < 0:
log('Not enough credits')
return False, "You do not have enough credits to purchase a ticket. Credits: {} Tickets: {}".format(credits[0], credits[1])
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
cost = int(100 * amount)
c.execute("""UPDATE members SET credits = credits - ?, tickets = tickets + ?
WHERE member_id = ?;""", (cost, amount, member_id))
conn.commit()
return True, int(credits[1]) + int(amount)
except Exception as e:
log(e)
def db_update_credit(member_id, amount):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
credits = c.execute("SELECT credits FROM members WHERE member_id = ?;", (member_id,)).fetchone()
if not credits:
return False, "Unable to find your account"
if int(credits[0]) + int(amount) < 0:
return False, "You do not have enough credits to cover amount requested. Credit: {} Amount Requested: {}".format(credits[0], amount)
if int(credits[0]) < 0:
c.execute("""UPDATE members SET credits = 0
WHERE member_id = ?;""", (amount, member_id))
conn.commit()
c.execute("""UPDATE members SET credits = credits + ?
WHERE member_id = ?;""", (amount, member_id))
conn.commit()
return True, ""
def db_add_minigame(member_id, minigame_name, state):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
db_state = c.execute("SELECT state FROM minigames WHERE member_id = ? AND minigame_name = ?;", (member_id, minigame_name)).fetchone()
if not db_state:
c.execute("""INSERT INTO minigames(member_id, minigame_name, state)
VALUES(?, ?, ?);""", (member_id, minigame_name, state))
conn.commit()
else:
c.execute("""UPDATE minigames SET state = ?
WHERE member_id = ? AND minigame_name = ?;""", (state, member_id, minigame_name))
conn.commit()
def db_get_minigame_state(member_id, minigame_name):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
state = c.execute("SELECT state FROM minigames WHERE member_id = ? AND minigame_name = ?;", (member_id, minigame_name)).fetchone()
if state:
return state[0]
def db_delete_minigame_state(member_id, minigame_name):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
c.execute("DELETE FROM minigames WHERE member_id = ? AND minigame_name = ?;", (member_id, minigame_name))
conn.commit()
def db_add_message(message, delivery_time, channel, message_from, message_to, user_id):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
c.execute("""INSERT INTO messages(message, delivery_time, channel, message_from, message_to, user_id)
VALUES(?, ?, ?, ?, ?, ?);""", (message, delivery_time, channel, message_from, message_to, user_id))
conn.commit()
def db_delete_sent_message(message_id):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
c.execute("DELETE FROM messages WHERE message_id = ?;", (message_id,))
conn.commit()
def db_get_aliases(member_id):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
aliases = c.execute("SELECT alias_name FROM aliases WHERE member_id = ?;", (member_id,)).fetchall()
if aliases:
alias_list = []
for alias in aliases:
alias_list.append(alias[0])
return alias_list
else:
return None
def db_add_aliases(member_id, alias_name):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
c.execute("INSERT INTO aliases(alias_name, member_id) VALUES (?, ?);", (alias_name, member_id,))
conn.commit()
def db_get_messages():
msg_conn = sqlite3.connect('db.sqlite3')
c = msg_conn.cursor()
messages = c.execute("SELECT * FROM messages WHERE datetime('now') >= datetime( replace(delivery_time, '/', '-'));").fetchall()
if messages:
db_messages = []
for message in messages:
db_messages.append(dict_factory(c, message))
else:
db_messages = None
msg_conn.close()
return db_messages
def db_get_whoplayed(game_name):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
members = c.execute("""SELECT m.member_name, xmg.launch_count
FROM members m
INNER JOIN xmember_games xmg ON
m.member_id = xmg.member_id
INNER JOIN games g ON
g.game_id = xmg.game_id
WHERE g.game_name COLLATE nocase = ?
Order By xmg.launch_count, m.member_name DESC;""", (game_name,)).fetchall()
member_list = {}
for member in members:
member_list[member[0]] = member[1]
#log(member_list)
return sorted(member_list.items(), reverse=True, key=operator.itemgetter(1))
#return sorted(member_list, reverse=True, key=lambda tup: tup[1])
def db_get_games(username):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
games = c.execute("""SELECT g.game_name, xmg.launch_count FROM games g
INNER JOIN xmember_games xmg ON
g.game_id = xmg.game_id
INNER JOIN members m ON
m.member_id = xmg.member_id
WHERE m.member_name COLLATE nocase = ?;""", (username,)).fetchall()
games_list = {}
for game in games:
games_list[game[0]] = game[1]
return games_list
def db_get_games_list(limit):
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
games_list = c.execute("""SELECT g.game_name, count(DISTINCT xmg.member_id)
FROM games g
INNER JOIN xmember_games xmg ON
g.game_id = xmg.game_id
Group By xmg.game_id
Order By COUNT(DISTINCT xmg.member_id) DESC
LIMIT ?""", (limit,)).fetchall()
return games_list
def db_add_game(member_id, game_name):
# Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
games = c.execute("SELECT game_id FROM games WHERE game_name = ?;", (game_name,)).fetchone()
db_game_id = 0
if not games:
log("Adding Game: {}".format(game_name,))
c.execute("INSERT INTO games(game_name) VALUES(?);", (game_name,))
conn.commit()
db_game_id = c.execute("select last_insert_rowid();").fetchone()[0]
else:
db_game_id = games[0]
#log("DB Game ID: {}".format(db_game_id,))
member_games = c.execute("SELECT launch_count FROM xmember_games WHERE game_id = ? AND member_id = ?;", (db_game_id, member_id)).fetchone()
if not member_games:
#log("Inserting Member Games: {}, {}".format(db_game_id, member_id))
c.execute("INSERT INTO xmember_games(game_id, member_id, launch_count) VALUES(?, ?, 1);", (db_game_id, member_id))
conn.commit()
else:
#log("Updating Member Games: {}, {}".format(db_game_id, member_id))
c.execute("UPDATE xmember_games SET launch_count = launch_count + 1 WHERE game_id = ? AND member_id = ?;", (db_game_id, member_id))
conn.commit()
def db_get_all_members():
# Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
member_conn = sqlite3.connect('db.sqlite3')
c = member_conn.cursor()
results = c.execute("SELECT member_id, member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game, credits, tickets FROM members;").fetchall()
member_conn.close()
members_list = []
for member in results:
members_list.append(dict_factory(c, member))
return members_list
def db_get_pankration_record(member_id):
# Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
pan_record = sqlite3.connect('db.sqlite3')
c = pan_record.cursor()
result = None
result = c.execute("SELECT * FROM pankration WHERE member_id = ?;", (member_id,)).fetchone()
pan_record.close()
return dict_factory(c, result)
def db_add_monster(member_id, monster):
pan_record = db_get_pankration_record(member_id)
log("{}".format(member_id))
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
db_state = c.execute("SELECT monster_inventory FROM pankration WHERE member_id = ?;", (member_id,)).fetchone()
if not db_state:
c.execute("""INSERT INTO pankration(member_id, wins, losses, monster_inventory)
VALUES(?, ?, ?, ?);""", (member_id, 0, 0, pickle.dumps([monster])))
conn.commit()
else:
log(db_state)
inv = str(db_state[0])
log(inv)
monster_inventory = pickle.loads(inv)
monster_inventory.append(monster)
c.execute("""UPDATE pankration SET monster_inventory = ?
WHERE member_id = ?;""", (pickle.dumps(monster_inventory), member_id))
conn.commit()
def db_get_member(discord_id=None, username=None):
# Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
member_conn = sqlite3.connect('db.sqlite3')
c = member_conn.cursor()
result = None
if discord_id:
result = c.execute("SELECT member_id, member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game, credits, tickets FROM members WHERE discord_id = ?;", (discord_id,)).fetchone()
elif username:
result = c.execute("SELECT member_id, member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game, credits, tickets FROM members WHERE member_name = ?;", (username,)).fetchone()
member_conn.close()
return dict_factory(c, result)
def db_create_member(member):
# Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
c.execute("""INSERT INTO members (member_name, discord_id, discord_mention,
is_afk, afk_at, status, prev_status,
status_change_at, current_game)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);""", (member.name.lower(),
member.id, member.mention(),
0, datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'),
'online', 'offline',
datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'),
member.game_id))
conn.commit()
if member.game_id != None:
db_add_game(db_get_member(member.id)['member_id'], member.game_id)
def db_update_member(member, db_member):
# Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name
db_member_id = db_member['member_id']
db_membername = db_member['member_name']
status = db_member['status']
prev_status = db_member['prev_status']
conn = sqlite3.connect('db.sqlite3')
c = conn.cursor()
if member.name.lower() != db_membername:
log("Member Name changed! {} to {}".format(byteify(db_membername), byteify(member.name.lower())))
c.execute("UPDATE members SET member_name = ? WHERE discord_id = ?;", (member.name.lower(), member.id,))
conn.commit()
aliases = c.execute("SELECT * FROM aliases WHERE alias_name = ? AND member_id = ?;", (db_membername, db_member_id)).fetchone()
log("Alias list for user: {}".format(aliases))
if aliases == None:
log("creating new alias: {}, {}".format(byteify(db_membername), db_member_id))
c.execute("INSERT INTO aliases (alias_name, member_id) VALUES (?, ?);", (db_membername, db_member_id))
conn.commit()
if member.status == 'idle':
afk_at = datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S')
is_afk = True
else:
is_afk = False
status_change_at = None
if status != member.status:
prev_status = status
status = member.status
status_change_at = datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S')
if is_afk:
#log("is afk")
c.execute("""UPDATE members
SET is_afk = ?, afk_at = ?, status = ?,
prev_status = ?, status_change_at = ?
WHERE discord_id = ?;""", (1, afk_at, status, prev_status, status_change_at, member.id))
conn.commit()
else:
#log("is not afk")
c.execute("""UPDATE members
SET is_afk = ?, status = ?,
prev_status = ?, status_change_at = ?
WHERE discord_id = ?;""", (0, status, prev_status, status_change_at, member.id))
conn.commit()
c.execute("UPDATE members SET current_game = ? WHERE discord_id = ?;", (member.game_id, member.id))
conn.commit()
#log("Member: {} \nMember GameID: {} db Game id: {}".format(member, member.game_id, db_member['current_game']))
if member.game_id != None and member.game_id != db_member['current_game']:
db_add_game(db_member['member_id'], member.game_id)