mailing/inactives.py

469 lines
14 KiB
Python

import os
import sys
from datetime import datetime, timezone, timedelta
import time
try:
import psycopg2
except ModuleNotFoundError as mod_not_found:
print(f"{mod_not_found}'. Run 'pip install -r requirements.txt' and try again")
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import smtplib
from smtplib import SMTPException, SMTPAuthenticationError, SMTPConnectError, SMTPRecipientsRefused
import socket
from socket import gaierror
def smtp_config():
secrets_filepath = "secrets/secrets.txt"
smtp_host = get_parameter("smtp_host", secrets_filepath)
smtp_user_login = get_parameter("smtp_user_login", secrets_filepath)
smtp_user_password = get_parameter("smtp_user_password", secrets_filepath)
email_subject = get_parameter("email_subject", secrets_filepath)
return (smtp_host, smtp_user_login, smtp_user_password, email_subject)
def db_config():
config_filepath = "config/db_config.txt"
mastodon_db = get_parameter("mastodon_db", config_filepath)
mastodon_db_user = get_parameter("mastodon_db_user", config_filepath)
mailing_db = get_parameter("mailing_db", config_filepath)
mailing_db_user = get_parameter("mailing_db_user", config_filepath)
mailing_db_table = get_parameter("mailing_db_table", config_filepath)
inactive_days = get_parameter("inactive_days", config_filepath)
return (mastodon_db, mastodon_db_user, mailing_db, mailing_db_user, mailing_db_table, inactive_days)
def get_parameter( parameter, file_path ):
# Check if secrets file exists
if not os.path.isfile(file_path):
if file_path == "secrets/secrets.txt":
print("File %s not found, exiting. Run setup.py."%file_path)
elif file_path == "config/db_config.txt":
print("File %s not found, exiting. Run db-setup.py."%file_path)
sys.exit(0)
# Find parameter in file
with open( file_path ) as f:
for line in f:
if line.startswith( parameter ):
return line.replace(parameter + ":", "").strip()
# Cannot find parameter, exit
print(file_path + " Missing parameter %s "%parameter)
print("Run setup.py")
sys.exit(0)
class Inactives:
name = "Inactives"
def __init__(self):
smtp_host, smtp_user_login, smtp_user_password, email_subject = smtp_config()
mastodon_db, mastodon_db_user, mailing_db, mailing_db_user, mailing_db_table, inactive_days = db_config()
self.smtp_host = smtp_host
self.smtp_user_login = smtp_user_login
self.smtp_user_password = smtp_user_password
self.email_subject = email_subject
self.mastodon_db = mastodon_db
self.mastodon_db_user = mastodon_db_user
self.mailing_db = mailing_db
self.mailing_db_user = mailing_db_user
self.mailing_db_table = mailing_db_table
self.inactive_days = inactive_days
self.now = datetime.now(timezone.utc)
def id(self):
###############################################################################
# get id of inactive users from inactive database
conn = None
try:
conn = psycopg2.connect(database = self.mailing_db, user = self.mailing_db_user, password = "", host = "/var/run/postgresql", port = "5432")
cur = conn.cursor()
cur.execute("SELECT account_id FROM " + self.mailing_db_table)
rows = cur.fetchall()
if rows != []:
inactive_users_id = []
for row in rows:
inactive_users_id.append(row[0])
else:
inactive_users_id = []
cur.close()
return inactive_users_id
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def delete(self,ids_lst):
print(f'\nChecking {len(ids_lst)} ids to delete reactivated or deleted accounts...')
i = 0
while i < len(ids_lst):
seen = self.check_alive(self, ids_lst[i])
try:
conn = psycopg2.connect(database = self.mailing_db, user = self.mailing_db_user, password = "", host = "/var/run/postgresql", port = "5432")
cur = conn.cursor()
cur.execute("SELECT emailed_at FROM " + self.mailing_db_table + " where account_id=(%s)", (ids_lst[i],))
row = cur.fetchone()
email_datetime = row[0]
email_datetime = email_datetime.replace(tzinfo=None)
if seen != None:
reactivated = email_datetime < seen
last_year = datetime.today() - timedelta(days=int(self.inactive_days))
if reactivated == True or seen == None or seen > last_year: #if inactive user had reactivated its account or had deleted it we must delete related row from 'mailing_db_table'
cur.execute("DELETE FROM " + self.mailing_db_table + " where account_id=(%s)", (ids_lst[i],))
print(f"Deleting user {ids_lst[i]}")
conn.commit()
cur.close()
i += 1
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def get_inactives(self):
###############################################################################
# Connect to Mastodon's Postgres DB to get last six months inactive users
###############################################################################
conn = None
try:
conn = psycopg2.connect(database = self.mastodon_db, user = self.mastodon_db_user, password = "", host = "/var/run/postgresql", port = "5432")
cur = conn.cursor()
cur.execute("select account_id, email, current_sign_in_at from users where current_sign_in_at < now() - interval '" + self.inactive_days + " days' and disabled=False and approved=True order by current_sign_in_at desc")
rows = cur.fetchall()
inactive_account_id = []
inactive_email = []
current_sign_in_at = []
for row in rows:
inactive_account_id.append(row[0])
inactive_email.append(row[1])
current_sign_in_at.append(row[2])
cur.close()
print(f'inactive accounts found: {len(inactive_account_id)}')
return (inactive_account_id, inactive_email, current_sign_in_at)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def usernames(self, account_ids):
########################################################################################################
# get accounts usernames #
########################################################################################################
inactive_usernames = []
i = 0
while i < len(account_ids):
conn = None
try:
conn = psycopg2.connect(database = self.mastodon_db, user = self.mastodon_db_user, password = "", host = "/var/run/postgresql", port = "5432")
cur = conn.cursor()
inactive_id = account_ids[i]
cur.execute("select username from accounts where id = '%s';", [inactive_id])
row = cur.fetchone()
new_username = row[0]
inactive_usernames.append(new_username)
i += 1
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return inactive_usernames
def mailing(self, account_ids, account_emails, account_usernames, account_current_sign_in_ats):
###########################################################################################################
# email inactive users
try:
fp = open('message.txt')
text = fp.read()
message = MIMEText(text)
fp.close()
except:
print("message.txt file not found! Create it and write in the message you want for your inactive users.")
sys.exit(0)
i = 0
while i < len(account_emails):
been_emailed = self.email_sent(self, account_ids[i])
if been_emailed == False:
# Create message object instance
msg = MIMEMultipart()
# Declare message elements
msg['From'] = self.smtp_user_login
msg['To'] = account_emails[i]
msg['Subject'] = account_usernames[i] + " " + self.email_subject
# Add the message body to the object instance
msg.attach(message)
try:
# Create the server connection
server = smtplib.SMTP(self.smtp_host)
# Switch the connection over to TLS encryption
server.starttls()
# Authenticate with the server
server.login(self.smtp_user_login, self.smtp_user_password)
# Send the message
server.sendmail(msg['From'], msg['To'], msg.as_string())
# Disconnect
server.quit()
print("Successfully sent email message to %s" % msg['To'])
emailed = True
self.write_db(self, self.now, account_ids[i], account_usernames[i], account_emails[i], self.now, emailed, account_current_sign_in_ats[i])
i += 1
time.sleep(5)
except SMTPAuthenticationError as auth_error:
print(auth_error)
sys.exit(":-(")
except socket.gaierror as socket_error:
print(socket_error)
print("Unknown SMTP server")
sys.exit(":-(")
except SMTPRecipientsRefused as recip_error:
print(recip_error)
emailed = False
self.write_db(self, self.now, account_ids[i], account_usernames[i], account_emails[i], self.now, emailed, account_current_sign_in_ats[i])
i += 1
else:
emailed = True
self.write_db(self, self.now, account_ids[i], account_usernames[i], account_emails[i], self.now, emailed, account_current_sign_in_ats[i])
i += 1
@staticmethod
def write_db(self, now, id, username, email, emailed_at, emailed, current_sign_in_at):
###################################################################################
# write to mailing database the status of inactive users
insert_line = "INSERT INTO " + self.mailing_db_table + "(datetime, account_id, username, email, emailed_at, emailed, current_sign_in_at) VALUES(%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING"
conn = None
try:
conn = psycopg2.connect(database = self.mailing_db, user = self.mailing_db_user, password = "", host = "/var/run/postgresql", port = "5432")
cur = conn.cursor()
cur.execute("SELECT account_id FROM " + self.mailing_db_table + " where account_id=(%s)", (id,))
row = cur.fetchone()
if row == None:
cur.execute(insert_line, (now, id, username, email, now, emailed, current_sign_in_at))
else:
if emailed == True:
cur.execute("SELECT datetime FROM " + self.mailing_db_table + " where account_id=(%s)", (id,))
row = cur.fetchone()
delta = now-row[0]
cur.execute("UPDATE " + self.mailing_db_table + " SET elapsed_days=(%s), email=(%s), emailed=(%s), current_sign_in_at=(%s) where account_id=(%s)", (delta.days, email, emailed, current_sign_in_at, id))
print(f"Updating user {str(id)}")
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
@staticmethod
def email_sent(self, account_id):
###############################################################################
# check if inactive user had been already emailed
been_emailed = False
conn = None
try:
conn = psycopg2.connect(database = self.mailing_db, user = self.mailing_db_user, password = "", host = "/var/run/postgresql", port = "5432")
cur = conn.cursor()
cur.execute("SELECT emailed FROM " + self.mailing_db_table + " where account_id=(%s)", (account_id,))
row = cur.fetchone()
if row != None:
been_emailed = row[0]
cur.close()
return been_emailed
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
@staticmethod
def check_alive(self, id):
conn = None
try:
conn = psycopg2.connect(database = self.mastodon_db, user = self.mastodon_db_user, password = "", host = "/var/run/postgresql", port = "5432")
cur = conn.cursor()
cur.execute("select current_sign_in_at from users where account_id=(%s)", (id,))
row = cur.fetchone()
if row != None:
seen = row[0]
else:
seen = None
cur.close()
return seen
except (Exception, psycopg2.DatabaseError) as error:
print(error)