forked from F3Nation-Community/PAXminer
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathPAXcharter.py
executable file
·169 lines (155 loc) · 7.68 KB
/
PAXcharter.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
#!/usr/bin/env python3
'''
This script was written by Beaker from F3STL. Questions? @srschaecher on twitter or [email protected].
This script queries Slack for all PAX Users and their respective beatdown attendance. It then generates bar graphs
on attendance for each member and sends it to them in a private Slack message.
'''
from slack_sdk import WebClient
from slack_sdk.errors import SlackApiError
import pandas as pd
import pymysql.cursors
import configparser
import datetime
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import sys
import time
import os
import logging
# This handler does retries when HTTP status 429 is returned
from slack_sdk.http_retry.builtin_handlers import RateLimitErrorRetryHandler
# 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 = config['aws']['db']
db = sys.argv[1]
# Set Slack token
key = sys.argv[2]
slack = WebClient(token=key)
# Enable rate limited error retries
rate_limit_handler = RateLimitErrorRetryHandler(max_retry_count=5)
slack.retry_handlers.append(rate_limit_handler)
#Define AWS Database connection criteria
mydb = pymysql.connect(
host=host,
port=port,
user=user,
password=password,
db=db,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
#Get Current Year, Month Number and Name
d = datetime.datetime.now()
d = d - datetime.timedelta(days=7)
thismonth = d.strftime("%m")
thismonthname = d.strftime("%b")
thismonthnamelong = d.strftime("%B")
yearnum = d.strftime("%Y")
# Set up logging
logging.basicConfig(filename='../logs/' + db + '/PAXcharter_error.log',
filemode = 'a',
format='%(asctime)s %(levelname)-8s %(message)s',
datefmt = '%Y-%m-%d %H:%M:%S',
level = logging.INFO)
logging.info("Running PAXminer for " + db)
print('Looking for all Slack Users for ' + db + '. Stand by...')
# 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
print('Now pulling all of those users beatdown attendance records... Stand by...')
## Define Slack Message function
def send_slack_message(channel, message, file):
return slack.files_upload(
channels=channel,
initial_comment=message,
file=file
)
# Query AWS by user ID for attendance history
#users_df = users_df.iloc[:10] # THIS LINE IS FOR TESTING PURPOSES, THIS FORCES ONLY n USER ROWS TO BE SENT THROUGH THE PIPE
total_graphs = 0 # Sets a counter for the total number of graphs made (users with posting data)
pause_on = [ 50, 100, 150, 200, 250, 300, 350, 400, 450, 500, 550, 600, 650, 700, 750, 800, 850, 900, 950, 1000 ]
for user_id in users_df['user_id']:
try:
attendance_tmp_df = pd.DataFrame([]) # creates an empty dataframe to append to
with mydb.cursor() as cursor:
sql = "SELECT * FROM attendance_view WHERE PAX = (SELECT user_name FROM users WHERE user_id = %s) AND YEAR(Date) = %s ORDER BY Date"
user_id_tmp = user_id
val = (user_id_tmp, yearnum)
cursor.execute(sql, val)
attendance_tmp = cursor.fetchall()
attendance_tmp_df = pd.DataFrame(attendance_tmp)
month = []
day = []
year = []
count = attendance_tmp_df.shape[0]
#if user_id_tmp == 'U0187M4NWG4': #Use this to send a graph to only 1 specific PAX
if count > 0: # This sends a graph to ALL PAX who have attended at least 1 beatdown
for Date in attendance_tmp_df['Date']:
#for index, row in attendance_tmp_df.iterrows():
datee = datetime.datetime.strptime(Date, "%Y-%m-%d")
month.append(datee.strftime("%B"))
day.append(datee.day)
year.append(datee.year)
pax = attendance_tmp_df.iloc[0]['PAX']
attendance_tmp_df['Month'] = month
attendance_tmp_df['Day'] = day
attendance_tmp_df['Year'] = year
attendance_tmp_df.sort_values(by=['Date'], inplace=True)
attendance_tmp_df.groupby(['Month', 'AO'], sort=False).size().unstack().plot(kind='bar',stacked=True)
plt.title('Number of posts from '+ pax + ' by AO/Month for ' + yearnum)
plt.legend(loc = 'center left', bbox_to_anchor=(1, 0.5), frameon = False)
plt.ioff()
plt.savefig('../plots/' + db + '/' + user_id_tmp + "_" + thismonthname + yearnum + '.jpg', bbox_inches='tight') #save the figure to a file
total_graphs = total_graphs + 1
message = 'Hey ' + pax + "! Here is your monthly posting summary for " + yearnum + ". \nPush yourself, get those bars higher every month! SYITG!"
file = '../plots/' + db + '/' + user_id_tmp + "_" + thismonthname + yearnum + '.jpg'
channel = user_id_tmp
#manual_graphs = [240,241,242,244,245,246,247,249,250]
if total_graphs > 0: # This is a count of total users processed, in case of error during processing. Set the total_graphs > to whatever # comes next in the log file row count.
print(total_graphs, 'PAX posting graph created for user', pax, 'Sending to Slack now... hang tight!')
#slack.chat.post_message(user_id_tmp, 'Hey ' + pax + "! Here is your monthly posting summary for " + yearnum + ". \nPush yourself, get those bars higher every month! SYITG!")
#if (total_graphs in pause_on):
#time.sleep(45)
try:
response = send_slack_message(channel, message, file)
#attendance_tmp_df.hist()
os.system("echo " + user_id_tmp + " " + pax + " >>" + "../logs/" + db + "/PAXcharter.log")
except:
os.system("echo Error: " + user_id_tmp + " >>" + "../logs/" + db + "/PAXcharter.log")
logging.warning("Slack Error - Message not sent:", pax, user_id_tmp)
print("Slack error on " + pax + " " + user_id_tmp)
raise e
else:
print(pax + ' skipped')
except:
print("An exception occurred for User ID " + user_id)
finally:
plt.close('all') #Note - this was added after the December 2020 processing, make sure this works
print('Total graphs made:', total_graphs)
mydb.close()