forked from srschaecher/PAXminer
-
Notifications
You must be signed in to change notification settings - Fork 0
/
BDminer.py
executable file
·356 lines (332 loc) · 16.6 KB
/
BDminer.py
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
351
352
353
354
355
356
#!/usr/bin/env python3
'''
This script was written by Beaker from F3STL. Questions? @srschaecher on twitter or srschaecher@gmail.com.
This script queries Slack for User, Channel, and Conversation (channel) history and then parses all conversations to find Backblasts.
All Backblasts are then parsed to collect the BEATDOWN information for any given workout and puts those attendance records into the AWS F3STL database for recordkeeping.
'''
import warnings
from slack_sdk import WebClient
from datetime import datetime, timedelta
import dateparser
import pandas as pd
import pytz
import re
import pymysql.cursors
import configparser
import sys
import logging
import math
# Configure AWS credentials
config = configparser.ConfigParser();
config.read('../config/credentials.ini');
host = config['aws']['host']
port = int(config['aws']['port'])
user = config['aws']['user']
password = config['aws']['password']
db = sys.argv[1] # Use this for the multi-region automated update
# Set Slack token
key = sys.argv[2] # Use this for the multi-region automated update
slack = WebClient(token=key)
#Define AWS Database connection criteria
mydb = pymysql.connect(
host=host,
port=port,
user=user,
password=password,
db=db,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
# Set epoch and yesterday's timestamp for datetime calculations
epoch = datetime(1970, 1, 1)
yesterday = datetime.now() - timedelta(days = 1)
oldest = yesterday.timestamp()
today = datetime.now()
cutoff_date = today - timedelta(days = 14) # This tells BDminer to go back up to N days for message age
cutoff_date = cutoff_date.strftime('%Y-%m-%d')
# Set up logging
logging.basicConfig(filename='./logs/BDminer.log',
filemode = 'a',
format='%(asctime)s %(levelname)-8s %(message)s',
datefmt = '%Y-%m-%d %H:%M:%S',
level = logging.INFO)
logging.info("Running BDminer for " + db)
# Make users Data Frame
column_names = ['user_id', 'user_name', 'real_name']
users_df = pd.DataFrame(columns = column_names)
data = ''
while True:
users_response = slack.users_list(limit=1000, cursor=data)
response_metadata = users_response.get('response_metadata', {})
next_cursor = response_metadata.get('next_cursor')
users = users_response.data['members']
users_df_tmp = pd.json_normalize(users)
users_df_tmp = users_df_tmp[['id', 'profile.display_name', 'profile.real_name']]
users_df_tmp = users_df_tmp.rename(columns={'id' : 'user_id', 'profile.display_name' : 'user_name', 'profile.real_name' : 'real_name'})
users_df = users_df.append(users_df_tmp, ignore_index=True)
if next_cursor:
# Keep going from next offset.
#print('next_cursor =' + next_cursor)
data = next_cursor
else:
break
for index, row in users_df.iterrows():
un_tmp = row['user_name']
rn_tmp = row['real_name']
if un_tmp == "" :
row['user_name'] = rn_tmp
try:
with mydb.cursor() as cursor:
sql = "SELECT channel_id, ao FROM aos WHERE backblast = 1 AND archived = 0"
cursor.execute(sql)
channels = cursor.fetchall()
channels_df = pd.DataFrame(channels, columns={'channel_id', 'ao'})
finally:
print('Looking for new Beatdowns from the Slack Backblast posts! Stand by...')
# Get all channel conversation
messages_df = pd.DataFrame([]) #creates an empty dataframe to append to
for id in channels_df['channel_id']:
data = ''
while True:
try:
#print("Checking channel " + id) # <-- Use this if debugging any slack channels throwing errors
response = slack.conversations_history(channel=id, cursor=data)
response_metadata = response.get('response_metadata', {})
next_cursor = response_metadata.get('next_cursor')
messages = response.data['messages']
temp_df = pd.json_normalize(messages)
temp_df = temp_df[['user', 'type', 'text', 'ts']]
temp_df = temp_df.rename(columns={'user' : 'user_id', 'type' : 'message_type', 'ts' : 'timestamp'})
temp_df["channel_id"] = id
messages_df = messages_df.append(temp_df, ignore_index=True)
except:
print("Error: Unable to access Slack channel:", id, "in region:",db)
logging.warning("Error: Unable to access Slack channel %s in region %s", id, db)
if next_cursor:
# Keep going from next offset.
# print('next_cursor =' + next_cursor)
#print(next_cursor)
data = next_cursor
else:
#print('Finished channel', id)
break
# Calculate Date and Time columns
msg_date = []
msg_time = []
for ts in messages_df['timestamp']:
seconds_since_epoch = float(ts)
dt = epoch + timedelta(seconds=seconds_since_epoch)
dt = dt.replace(tzinfo=pytz.utc)
dt = dt.astimezone(pytz.timezone('America/Chicago'))
msg_date.append(dt.strftime('%Y-%m-%d'))
msg_time.append(dt.strftime('%H:%M:%S'))
messages_df['date'] = msg_date
messages_df['time'] = msg_time
# Merge the data frames into 1 joined DF
f3_df = pd.merge(messages_df, users_df)
f3_df = pd.merge(f3_df,channels_df)
f3_df = f3_df[['timestamp', 'date', 'time', 'channel_id', 'ao', 'user_id', 'user_name', 'real_name', 'text']]
# Now find only backblast messages (either "Backblast" or "Back Blast") - note .casefold() denotes case insensitivity - and pull out the PAX user ID's identified within
# This pattern finds the Q user ID
pat = r'(?<=\<).+?(?=>)' # This pattern finds username links within brackets <>
bd_df = pd.DataFrame([])
warnings.filterwarnings("ignore", category=DeprecationWarning) #This prevents displaying the Deprecation Warning that is present for the RegEx lookahead function used below
def bd_info():
# Find the Q information
qline = re.findall(r'(?<=\n)\*?V?Qs?\*?:.+?(?=\n)', str(text_tmp), re.MULTILINE) #This is regex looking for \nQ: with or without an * before Q
qids = re.findall(pat, str(qline), re.MULTILINE)
qids = [re.sub(r'@', '', i) for i in qids]
if qids:
qid = qids[0]
else:
qid = 'NA'
if len(qids) > 1:
coqid = qids[1]
else:
coqid = 'NA'
# Find the PAX Count line (if the Q put one in the BB)
pax_count = re.search(r'(?<=\n)\*?(?i)Count\*?:\*?.+?(?:$|\n)', str(text_tmp))
if pax_count:
pass
else:
pax_count = re.search(r'(?<=\n)\*?(?i)Total\*?:\*?.+?(?=\n)', str(text_tmp))
if pax_count:
pax_count = pax_count.group()
pax_count = re.findall('\d+', str(pax_count))
if pax_count:
pax_count = int(pax_count[0])
else:
pax_count = -1
if isinstance(pax_count, int):
pass
else:
pax_count = -1
# Find the FNGs line
fngline = re.findall(r'(?<=\n)\*?FNGs\*?:\*?.+?(?=\n)', str(text_tmp), re.MULTILINE) # This is regex looking for \nFNGs: with or without an * before Q
if fngline:
fngline = fngline[0]
fngs = re.sub('\*?FNGs\*?:\s?', '', str(fngline))
fngs = fngs.strip()
else:
fngs = 'None listed'
#Find the Date:
dateline = re.findall(r'(?<=\n)Date:.+?(?=\n)', str(text_tmp), re.IGNORECASE)
if dateline:
dateline = re.sub('xa0', ' ', str(dateline), flags=re.I)
#print("First dateline: " + dateline)
dateline = re.sub("Date:\s?", '', str(dateline), flags=re.I)
#print("Removed Date: " + dateline)
dateline = dateparser.parse(dateline) #dateparser is a flexible date module that can understand many different date formats
if dateline is None:
date_tmp = '2099-12-31' #sets a date many years in the future just to catch this error later (needs to be a future date)
else:
date_tmp = str(datetime.strftime(dateline, '%Y-%m-%d'))
else:
date_tmp = msg_date
#Find the AO line
aoline = re.findall(r'(?<=\n)\*?AO\*?:\*?.+?(?=\n)', str(text_tmp),re.MULTILINE) # This is regex looking for \nAO: with or without an *
if aoline:
ao_name = re.sub('\*?AO\*?:\s?', '', str(aoline))
ao_name = ao_name.strip()
else:
ao_name = 'Unknown'
global bd_df
new_row = {'msg_date' : msg_date, 'ao_id' : ao_tmp, 'bd_date' : date_tmp, 'q_user_id' : qid, 'coq_user_id' : coqid, 'pax_count' : pax_count, 'backblast' : text_tmp, 'fngs' : fngs, 'user_name' : user_name, 'user_id' : user_id, 'ao_name' : ao_name}
bd_df = bd_df.append(new_row, ignore_index = True)
# Iterate through the new bd_df dataframe, pull out the channel_name, date, and text line from Slack. Process the text line to find the beatdown info
for index, row in f3_df.iterrows():
ao_tmp = row['channel_id']
msg_date = row['date']
text_tmp = row['text']
text_tmp = re.sub('_\\xa0', ' ', str(text_tmp))
text_tmp = re.sub('\\xa0', ' ', str(text_tmp))
text_tmp = re.sub('_\*', '', str(text_tmp))
text_tmp = re.sub('\*_', '', str(text_tmp))
text_tmp = re.sub('\*', '', str(text_tmp))
user_name = row['user_name']
user_id = row['user_id']
if re.findall('^Slackblast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^\*Backblast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^Backblast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^\*Back blast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^Slack blast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^Sackblast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^\*Slackblast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^\*Slack blast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^\*Sackblast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^\*Slackbast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^Slackbast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^Sackdraft', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^\*Sackdraft', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
elif re.findall('^\*Backblast', text_tmp, re.IGNORECASE | re.MULTILINE):
bd_info()
# Now connect to the AWS database and insert some rows!
try:
with mydb.cursor() as cursor:
for index, row in bd_df.iterrows():
qc = 1
sql = "INSERT IGNORE into beatdowns (ao_id, bd_date, q_user_id, coq_user_id, pax_count, backblast, fngs) VALUES (%s, %s, %s, %s, %s, %s, %s)"
ao_id = row['ao_id']
msg_date = row['msg_date']
bd_date = row['bd_date']
q_user_id = row['q_user_id']
coq_user_id = row['coq_user_id']
pax_count = row['pax_count']
backblast = row['backblast']
user_name = row['user_name']
user_id = row['user_id']
fngs = row['fngs']
ao_name = row['ao_name']
val = (ao_id, bd_date, q_user_id, coq_user_id, pax_count, backblast, fngs)
if msg_date > cutoff_date:
if q_user_id == 'NA':
logging.warning("Q error for AO: %s, Date: %s, backblast from Q %s (ID %s) not imported", ao_id, msg_date, user_name, user_id)
print('Backblast error on Q at AO:', ao_id, 'Date:', msg_date, 'Posted By:', user_name, ". Slack message sent to Q. bd: ", bd_date, "cutoff:", cutoff_date)
slack.chat_postMessage(channel=user_id, text="Hey " + user_name + "! I just saw your backblast posted on " + msg_date + " at <#" + ao_id + ">. There seems to be a problem. The Q is not present or not tagged correctly. Can you fix it? The correct syntax is\n \nQ: @tag_the_q_here\n \nThanks!")
qc = 0
else:
pass
if pax_count == -1:
logging.warning("Count error for AO: %s, Date: %s, backblast from Q %s (ID %s) not imported", ao_id, msg_date, user_name, user_id)
print('Backblast error on Count - AO:', ao_id, 'Date:', msg_date, 'Posted By:', user_name, ". Slack message sent to Q.")
print(backblast)
slack.chat_postMessage(channel=user_id, text="Hey " + user_name + "! I just saw your backblast posted on " + msg_date + " at <#" + ao_id + ">. There seems to be a problem. The Count is not present or not entered correctly. Can you fix it? The correct syntax is \n \nCount: XX (You can also use 'Total:'). Use digits please. \n\nThanks!")
qc = 0
else:
pass
if bd_date == '2099-12-31':
logging.warning("Date error for AO: %s, Date: %s, backblast from Q %s (ID %s) not imported", ao_id, msg_date, user_name, user_id)
print('Backblast error on Date - AO:', ao_id, 'Date:', msg_date, 'Posted By:', user_name,". Slack message sent to Q. bd: ", bd_date, "cutoff:", cutoff_date)
slack.chat_postMessage(channel=user_id, text="Hey " + user_name + "! I just saw your backblast posted on " + msg_date + " at <#" + ao_id + ">. There seems to be a problem. The Date is not entered correctly. Can you fix it? I can understand most common date formats like: \n \nDate: 12-25-2020\nDate: 2020-12-25\nDate: 12/25/20\nDate: December 25, 2020\nAnd even more... which means you must have used something really weird.\n\nThanks!")
qc = 0
if qc == 1:
cursor.execute(sql, val)
mydb.commit()
if cursor.rowcount == 1:
print(cursor.rowcount, "records inserted.")
print('Beatdown Date:', bd_date)
print('Message Posting Date:', msg_date)
print('AO:', ao_name)
print('Q:', q_user_id)
print('Co-Q', coq_user_id)
print('Pax Count:',pax_count)
print('fngs:', fngs)
slack.chat_postMessage(channel=user_id, text="Hey " + user_name + "! I just captured and recorded your backblast for " + bd_date + " at <#" + ao_id + ">. I see you had " + str(math.trunc(pax_count)) + " PAX in attendance and FNGs were: " + str(fngs) + ". Thanks for posting your BB!")
print("Slack message sent to Q.")
logging.info("Backblast imported for AO: %s, Date: %s", ao_id, bd_date)
#Add the Q to the bd_attendance table as some Q's are forgetting to add themselves to the PAX line
if qc == 1:
if q_user_id == 'NA':
pass
else:
sql2 = "INSERT IGNORE into bd_attendance (user_id, ao_id, date, q_user_id) VALUES (%s, %s, %s, %s)"
user_id = row['q_user_id']
ao_id = row['ao_id']
date = row['bd_date']
val2 = (user_id, ao_id, date, user_id)
cursor.execute(sql2, val2)
mydb.commit()
if cursor.rowcount == 1:
print(cursor.rowcount, "Q's attendance at beatdown recorded.")
else:
pass
sql3 = "UPDATE beatdowns SET coq_user_id=NULL where coq_user_id = 'NA'"
cursor.execute(sql3)
mydb.commit()
sql4 = "UPDATE beatdowns SET fng_count=0 where fngs in ('none', 'None', 'None listed', 'NA', 'zero', '-', '') AND fng_count IS NULL"
cursor.execute(sql4)
mydb.commit()
sql5 = "UPDATE beatdowns SET fng_count = 0 where fngs like '0%' AND fng_count IS NULL"
cursor.execute(sql5)
mydb.commit()
sql6 = "UPDATE beatdowns SET fng_count = 1 where fngs like '1%' AND fng_count IS NULL"
cursor.execute(sql6)
mydb.commit()
sql7 = "UPDATE beatdowns SET fng_count = 2 where fngs like '2%' AND fng_count IS NULL"
cursor.execute(sql7)
mydb.commit()
sql8 = "UPDATE beatdowns SET fng_count = 3 where fngs like '3%' AND fng_count IS NULL"
cursor.execute(sql8)
mydb.commit()
sql9 = "UPDATE beatdowns SET fng_count = 4 where fngs like '4%' AND fng_count IS NULL"
cursor.execute(sql9)
mydb.commit()
sql10 = "UPDATE beatdowns SET fng_count = 5 where fngs like '5%' AND fng_count IS NULL"
cursor.execute(sql10)
mydb.commit()
finally:
mydb.close()
print('Finished. Beatdowns are up to date.')
logging.info("BDminer execution complete for region " + db)