get_renewees: Order by lastname instead of last renewal date
[conservancy:voting.git] / bin / get_renewees.py
1 #!/usr/bin/env python
2 """
3 This program gets the members from the MySQL Database that need renewal.
4 By starting with --mode=day you can select only those members that need to
5 renew today.
6 With --mode=year you get the members that need to renew this year, i.e. 
7 whole 2011.
8
9 One can send email, too. Use the --send-mail switch to send email.
10
11 If you want to test your setup, you can use --one-only=your@address.com to test
12 whether email actually works or what From address is used, which is
13 configurable with --from-address=other@address.com.
14
15 In order to work properly, you need to have your MySQL client configured 
16 properly, i.e. ~/.my.cnf should be set up with the appropriate 
17 credentials:
18 [client]
19 host=button-back
20 user=anonvoting
21 password=foobar
22 default-character-set=utf8
23
24 The reason to call MySQL client and not use the Python library is 
25 mainly, that the MySQL bindings are not installed.
26 """
27 import datetime
28 try:
29     from email.mime.text import MIMEText
30     from email.mime.nonmultipart import MIMENonMultipart
31     from email.charset import Charset
32 except ImportError:
33     from email.MIMEText import MIMEText
34     from email.Charset import Charset
35     from email.MIMENonMultipart import MIMENonMultipart
36 import logging
37 from optparse import OptionParser
38 import smtplib
39 import StringIO
40 import subprocess
41 import sys
42 import tempfile
43
44 __author__ = "Tobias Mueller"
45 __copyright__ = "Copyright 2011, The GNOME Project"
46 __credits__ = ["Tobias Mueller",]
47 __license__ = "GPLv3+"
48 __version__ = "1.0.0"
49 __maintainer__ = "Tobias Mueller"
50 __email__ = "tobiasmue@gnome.org"
51                     
52
53 TEMPLATE = '''
54 Dear %(firstname)s,
55
56 the last time, your GNOME Foundation Membership was renewed, was on
57 %(last_renewed_on)s.
58 The term of a membership is two years.
59 If you want to continue being a member, you have to renew your 
60 membership.
61
62 Please see http://foundation.gnome.org/membership/ for details.
63
64 Thanks,
65   The GNOME Membership and Elections Committee
66 '''.strip()
67
68 log = logging.getLogger()
69
70 class MTText(MIMEText):
71     def __init__(self, _text, _subtype='plain', _charset='utf-8'):
72         if not isinstance(_charset, Charset):
73             _charset = Charset(_charset)
74         if isinstance(_text,unicode):
75             _text = _text.encode(_charset.input_charset)
76         MIMENonMultipart.__init__(self, 'text', _subtype,
77                                        **{'charset': _charset.input_charset})
78         self.set_payload(_text, _charset)
79
80
81 def send_email(to, subject, emailtext, from_address = None, smtp_server = 'localhost'):
82     log = logging.getLogger('eMail')
83     s = None
84     from_address = from_address or "GNOME Membership and Elections Committee <membership-committee@gnome.org>"
85     
86     msg = MTText(emailtext)
87     msg['To'] = to
88     msg['From'] = from_address
89     msg['Subject'] = subject
90     msgstr = msg.as_string()
91
92     if s is None:
93         s = smtplib.SMTP()
94         s.connect(smtp_server)
95     try:
96         log.info('Trying to send to %s, %s', to, subject)
97         s.sendmail(from_address, [to,], msgstr)
98     except smtplib.SMTPException,e :
99         log.warning("Error: Could not send to %s!" % (to,))
100         raise e
101
102     #if s:
103     #    s.quit()
104     
105 class Member(object):
106     def __init__(self, firstname, lastname, email, token_or_last_renewed_on):
107         self.firstname = firstname
108         self.lastname = lastname
109         self.email = email
110         self.token_or_last_renewed_on = token_or_last_renewed_on
111         
112     @classmethod
113     def from_csv(cls, csvstring):
114         firstname, lastname, email, token_or_last_renewed_on = csvstring.strip().split(';')
115         return Member(firstname, lastname, email, token_or_last_renewed_on)
116     
117     def __str__(self):
118         if False: # string.format is too recent Python
119             fmt = "{firstname} {lastname} <{email}> (token_or_last_renewed_on)"
120             return fmt.format(self)
121         fmt = "%(firstname)s %(lastname)s <%(email)s> (%(token_or_last_renewed_on)s)"
122         return fmt % self.__dict__
123
124     def __repr__(self):
125         fmt = "<Member <%(email)s> (%(token_or_last_renewed_on)s)>"
126         return fmt % self.__dict__
127
128
129
130 def execute_query(query):
131     log.debug('MySQL Query: %s', query)
132     DATABASE = "foundation"
133     mysql_p = subprocess.Popen(['mysql', DATABASE], stdin=subprocess.PIPE, stdout=subprocess.PIPE)
134     SQL_result, SQL_error = mysql_p.communicate(query)
135     if SQL_error:
136         sys.stderr.write('Error Executing SQL: %s' % SQL_error)
137     if not SQL_result:
138         log.info('NULL Result from SQL')
139         infile = StringIO.StringIO("")
140     else:
141         infile = StringIO.StringIO(SQL_result)
142         _ = infile.next() # The first line is garbage, I think it's MySQL output
143     return infile
144
145 def get_members_which_need_renewal(mode):
146     """
147     Generates and executes a SQL Query which asks the foundation's database
148     for members that need to renew.
149     The base date is calculated from the mode argument:
150         election:   Last years 07-01 until this years 07-01
151         year:       Beginning of the year, i.e. 01.01. until 31.12.
152         month:      From 1st of this month to end of this month
153         day:        Members that need to renew this day
154     """
155     ELECTION_FROM_DATE = """CONCAT(
156                             YEAR(
157                                 DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
158                             ),
159                             '-07-01')
160                         """.strip()# Last years July, 1st
161     ELECTION_TO_DATE = """CONCAT(YEAR(CURDATE()), '-07-01')""" # This years July, 1st
162
163     DAY_FROM_DATE = """DATE_FORMAT(NOW(),"%Y-%m-%d")"""
164     DAY_TO_DATE = DAY_FROM_DATE
165
166     MONTH_FROM_DATE = """DATE_FORMAT(NOW(),"%Y-%m-01")"""
167     MONTH_TO_DATE = """LAST_DAY(NOW())"""
168
169     YEAR_FROM_DATE = """DATE_FORMAT(NOW(),"%Y-01-01")"""
170     YEAR_TO_DATE = """DATE_FORMAT(NOW(),"%Y-12-31")"""
171
172     (mysql_from_date, mysql_to_date) = {
173             'election': (ELECTION_FROM_DATE, ELECTION_TO_DATE),
174             'day':      (DAY_FROM_DATE,DAY_TO_DATE),
175             'month':    (MONTH_FROM_DATE, MONTH_TO_DATE),
176             'year':     (YEAR_FROM_DATE, YEAR_TO_DATE),
177         }[mode]
178     QUERY = '''
179                SET names 'utf8';
180                SELECT CONCAT(firstname, ';', lastname, ';', email, ';', last_renewed_on)
181                FROM foundationmembers
182                WHERE   last_renewed_on >= DATE_SUB(
183                          %(mysql_from_date)s ,
184                          INTERVAL 2 YEAR
185                       )
186                     AND last_renewed_on <= DATE_SUB(
187                          %(mysql_to_date)s ,
188                          INTERVAL 2 YEAR
189                        )
190                ORDER BY lastname;
191             '''.strip()
192     QUERY %= {'mysql_from_date': mysql_from_date,
193               'mysql_to_date': mysql_to_date}
194     infile = execute_query(QUERY)
195     memberlist = [Member.from_csv(line.strip()) for line in infile]
196     return memberlist
197
198 def get_members_election_token(election_id):
199     '''
200     Execute a SQL query to get a list of members with their temporary election
201     token.
202     
203     The token need to be created first, i.e. using smth like
204     
205     INSERT INTO election_tmp_tokens (election_id, member_id, tmp_token)
206     SELECT @election_id, id, SUBSTRING(MD5(RAND()) FROM 1 FOR 24) AS tmp_token
207     FROM electorate;
208     '''    
209     QUERY = '''
210                SET names 'utf8';
211                SELECT CONCAT(firstname, ';', lastname, ';', email, ';', tmp_token)
212                FROM election_tmp_tokens, foundationmembers
213                WHERE election_id = %(election_id)s
214                  AND member_id = id;
215             '''.strip()
216     QUERY %= {'election_id': election_id}
217     result = execute_query(QUERY)
218     memberlist = [Member.from_csv(line.strip()) for line in result]
219
220     return memberlist
221
222 if __name__ == "__main__":
223     parser = OptionParser()
224     parser.add_option("-l", "--loglevel", dest="loglevel",
225                       help="Sets the loglevel to one of debug, info, warn, "
226                             "error, critical", default="info")
227     parser.add_option("-m", "--mode", dest="mode",
228                       help="Which members to get: one of year, month, day, election"
229                             " [default: %default]", default="month")
230     parser.add_option("-s", "--send-mail", dest="sendmail",
231                       help="Do indeed send mail [default: %default]",
232                       action="store_true",
233                       default=False)
234     parser.add_option("-t", "--token", dest="token_for_election",
235                       help="Process temporary token, instead of renewals."
236                       "Please give the election id, which you should know, as "
237                       "argument to this parameter. A program argument is also required"
238                       " to read the instructions as a template."
239                       "[default: %default]",
240                       default=False)
241     parser.add_option("-1", "--one-only", dest="oneonlyaddress",
242                       help="Send one mail only to this address [default: %default]",
243                       default=None)
244     parser.add_option("-f", "--from-address", dest="fromaddress",
245                       help="Use that as sending address [default: %default]",
246                       default="Tobias Mueller <tobiasmue@gnome.org>")
247     (options, args) = parser.parse_args()
248     loglevel = {'debug': logging.DEBUG, 'info': logging.INFO,
249                 'warn': logging.WARN, 'error': logging.ERROR,
250                 'critical': logging.CRITICAL}.get(options.loglevel, "warn")
251     LOGFORMAT = "%(asctime)s %(levelname)-8s %(name)s %(message)s"
252     DATEFORMAT = '%Y-%m-%d %H:%M:%S'
253     logging.basicConfig(level=loglevel, format=LOGFORMAT, datefmt=DATEFORMAT)
254     log = logging.getLogger('main')
255     
256     if not options.token_for_election: # This is the default. We care about renewals
257         email_subject = 'Your GNOME Foundation Membership is about to expire'
258         logmsg = 'Needs Renewal: %s, %s, %s, %s'
259         template = TEMPLATE
260         members = get_members_which_need_renewal(options.mode)
261     else: # This is with -t option. We process election tokens.
262         email_subject = 'GNOME Foundation Board of Directors Elections %d - Voting Instructions' % datetime.date.today().year
263         logmsg = 'Sending Token: %s, %s, %s, %s'
264         template = open(args[0], "r").read()
265         members = get_members_election_token(options.token_for_election)
266         
267         
268
269     if options.oneonlyaddress:
270         members = [Member("firstname", "lastname", 
271                           options.oneonlyaddress, "2000-05-23")]
272         
273     
274     for member in members:
275         firstname, lastname, email, token_or_last_renewed_on = member.firstname, member.lastname, member.email, member.token_or_last_renewed_on
276         log.warn(logmsg, lastname, firstname, email, token_or_last_renewed_on)
277         emailtext = template % {'firstname':firstname, 'lastname':lastname,
278                                  'email': email, 'token_or_last_renewed_on': token_or_last_renewed_on,
279                                  'token': token_or_last_renewed_on,
280                                  'last_renewed_on': token_or_last_renewed_on,
281                                }
282         log.debug('The email to be sent is: %s', emailtext)
283         to = email
284         fromaddress = options.fromaddress
285         subject = email_subject
286         try:
287             if options.sendmail or options.oneonlyaddress:
288                 send_email(to, subject, emailtext, fromaddress)
289         except smtplib.SMTPException,e :
290             log.error('Error sending to %s: %s', to, e)
291             try:
292                 tfile = tempfile.NamedTemporaryFile(delete=False)
293             except TypeError:
294                 tfile = tempfile.NamedTemporaryFile()
295             tfile.writeline('To: %s' % to)
296             tfile.writeline('Subject: %s' % subject)
297             tfile.writeline('')
298             tfile.writeline('%s' % emailtext)
299             log.critical('eMail put in %s', tfile.name)