-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathExpiring patrons.py
122 lines (97 loc) · 4.03 KB
/
Expiring patrons.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
#!/usr/bin/env python3
"""Produces and sends expiration warning e-mails to patrons whose cards are 30 days out from expiring
Author: Jeremy Goldstein, based in part on code provided by Gem Stone-Logan
Contact Info: jgoldstein@minlib.net
"""
import psycopg2
import smtplib
import csv
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.utils import formatdate
from email import encoders
from datetime import date
from dateutil.relativedelta import relativedelta
#use file to gather stats for how many messages are generated each day
#compare numbers with Expired_Patrons.csv to see how many patrons renewed in the 30 day window
csvFile = 'Stats\Expiring_Patrons.csv'
# These are variables for the email that will be sent.
# Make sure to use your own library's email server (emaihost)
#
emailhost = ''
emailuser = ''
emailpass = ''
emailport = ''
# Enter your own email information
emailfrom= ''
#Connecting to Sierra PostgreSQL database
#Enter your username, password and host
conn = psycopg2.connect("dbname='iii' user='' host='' port='1032' password='' sslmode='require'")
#Opening a session and querying the database for weekly new items
cursor = conn.cursor()
cursor.execute(open("Expiring patrons.sql","r").read())
#For now, just storing the data in a variable. We'll use it later.
rows = cursor.fetchall()
conn.close()
#Track # of generated messages count
with open(csvFile, 'a') as tempFile:
newRow = '\n' + str(len(rows)) + ',' + str(date.today() + relativedelta(days=30))
tempFile.write(newRow)
tempFile.close()
#Sending the email message
smtp = smtplib.SMTP(emailhost, emailport)
#for Google connection
smtp.ehlo()
smtp.starttls()
smtp.login(emailuser, emailpass)
for rownum, row in enumerate(rows):
# emailto can send to multiple addresses by separating emails with commas
emailto = [str(row[2])]
emailsubject = "It's time to renew your library card"
#Creating the email message
#Plain text version
text = '''Dear {} {},
This is a reminder that your library card will expire on {}. Visit or contact any Minuteman library for information on how to renew your account and continue access to over 6 million items.
***This is an automated email***'''.format(str(row[0]),str(row[1]),str(row[3]))
#HTML version
html = '''
<html>
<head></head>
<body style="background-color:#202941;">
<table style="width: 70%; margin-left: 15%; margin-right: 15%; border: 0; cellspacing: 0; cellpadding: 0; background-color: #FFFFFF;">
<tr>
<img src="cid:image1" style="height: 135px; width: 135px; display: block; margin-left: auto; margin-right: auto;" alt="placeholder">
<font face="Scala Sans, Calibri, Arial"; size="3">
<p>Dear {} {},<br><br>
This is a reminder that your library card will expire on {}.<br>
Visit or contact any <a href="http://www.mln.lib.ma.us/info/">Minuteman library</a> for information on how to renew your account and continue access to over 6 million items.<br><br>
***This is an automated email. Do not reply.***<br><br>
</font>
</p>
<img src="http://www.mln.lib.ma.us/graphics/logo-print-small.jpg" style="height: 32px; width: 188px; display: block; margin-left: auto; margin-right: auto;" alt="Minuteman logo">
</tr>
</table>
</body>
</html>'''.format(str(row[0]),str(row[1]),str(row[3]))
msg = MIMEMultipart('alternative')
part1 = MIMEText(text,'plain')
part2 = MIMEText(html, 'html')
msg['From'] = emailfrom
if type(emailto) is list:
msg['To'] = ', '.join(emailto)
else:
msg['To'] = emailto
msg['Date'] = formatdate(localtime = True)
msg['Subject'] = emailsubject
msg.attach (part1)
msg.attach (part2)
pic = open('clock.png', 'rb')
msgImage = MIMEImage(pic.read())
pic.close()
msgImage.add_header('Content-ID', '<image1>')
msg.attach(msgImage)
smtp.sendmail(emailfrom, emailto, msg.as_string())
rownum+1
smtp.quit()