Login Nag

recent.py

#!/usr/bin/python
import MySQLdb
import smtplib
import re
import os

# list of users to exclude by usernames
exclusions = []

# DB stuff
# go digging around the settings file for something like looks like db credentials
with open(os.path.abspath(os.path.join(os.path.dirname(__file__),"../../docroot/sites/default/settings.php")), 'r') as settings:
    regex = re.compile("\s+'[a-z]+' => '([a-zA-Z0-9_\.]+)',")
    for line in settings:
        if "'database' =>" in line:
            m = regex.match(line)
            database = m.group(1)
        elif "'username' =>" in line:
            m = regex.match(line)
            username = m.group(1)
        elif "'password' =>" in line:
            m = regex.match(line)
            password = m.group(1)
        elif "'host' => " in line:
            m = regex.match(line)
            host = m.group(1)

db = MySQLdb.connect(host=host, user=username, passwd=password, db=database)
cursor = db.cursor()

# Mail vars:
SERVER = ""
FROM = ""
server = smtplib.SMTP(SERVER)

def mail(who, interval, text):
    subject = "You have not logged in for at least " + str(interval) + " days"
    message = """\
From: %s
To: %s
Subject: %s
%s
""" % (FROM, ", ".join(who), subject, text)
    server.sendmail(FROM, who, message)

# > 120 days - these go to lori
cursor.execute("SELECT name, mail, FROM_UNIXTIME(login) FROM users WHERE status=1 AND FROM_UNIXTIME(login) < DATE_SUB(NOW(), INTERVAL 120 DAY) AND LOGIN != 0")
    
people = []
numrows = int(cursor.rowcount)
for x in range(0,numrows):
    row = cursor.fetchone()
    if row[0] not in exclusions:
        people.append((row[0], row[1], row[2]))

# put an email address in here
mail([], 120, '\n'.join(['\t'.join([p[0], p[1], p[2].strftime('%m/%d/%Y')]) for p in people]))

# > 30 days, but less than 120 days
message_text = """\
We miss you!

We noticed you haven't logged into Lane's website in over a month, and just wanted to remind you how important it is that you keep your webpages updated.

If you're having trouble, just reply to this eamail.

Thanks for helping us keep Lane current on the web!"""

cursor.execute("SELECT name, mail, FROM_UNIXTIME(login) FROM users WHERE status=1 AND FROM_UNIXTIME(login) > DATE_SUB(NOW(), INTERVAL 120 DAY) AND FROM_UNIXTIME(login) < DATE_SUB(NOW(), INTERVAL 30 DAY) AND LOGIN != 0")

people = []
numrows = int(cursor.rowcount)
for x in range(0,numrows):
    row = cursor.fetchone()
    if row[0] not in exclusions:
        mail([row[1]], 30, message_text)

server.quit()