Added a script that returns the members that need renewal as well as ballots
[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
118
119
120 def execute_query(query):
121     log.debug('MySQL Query: %s', query)
122     DATABASE = "foundation"
123     mysql_p = subprocess.Popen(['mysql', DATABASE], stdin=subprocess.PIPE, stdout=subprocess.PIPE)
124     SQL_result, SQL_error = mysql_p.communicate(query)
125     if SQL_error:
126         sys.stderr.write('Error Executing SQL: %s' % SQL_error)
127     if not SQL_result:
128         log.info('NULL Result from SQL')
129         infile = StringIO.StringIO("")
130     else:
131         infile = StringIO.StringIO(SQL_result)
132         _ = infile.next() # The first line is garbage, I think it's MySQL output
133     return infile
134
135 def get_members_which_need_renewal(mode):
136     """
137     Generates and executes a SQL Query which asks the foundation's database
138     for members that need to renew.
139     The base date is calculated from the mode argument:
140         election:   Last years 07-01 until this years 07-01
141         year:       Beginning of the year, i.e. 01.01. until 31.12.
142         month:      From 1st of this month to end of this month
143         day:        Members that need to renew this day
144     """
145     ELECTION_FROM_DATE = """CONCAT(
146                             YEAR(
147                                 DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
148                             ),
149                             '-07-01')
150                         """.strip()# Last years July, 1st
151     ELECTION_TO_DATE = """CONCAT(YEAR(CURDATE()), '-07-01')""" # This years July, 1st
152
153     DAY_FROM_DATE = """DATE_FORMAT(NOW(),"%Y-%m-%d")"""
154     DAY_TO_DATE = DAY_FROM_DATE
155
156     MONTH_FROM_DATE = """DATE_FORMAT(NOW(),"%Y-%m-01")"""
157     MONTH_TO_DATE = """LAST_DAY(NOW())"""
158
159     YEAR_FROM_DATE = """DATE_FORMAT(NOW(),"%Y-01-01")"""
160     YEAR_TO_DATE = """DATE_FORMAT(NOW(),"%Y-12-31")"""
161
162     (mysql_from_date, mysql_to_date) = {
163             'election': (ELECTION_FROM_DATE, ELECTION_TO_DATE),
164             'day':      (DAY_FROM_DATE,DAY_TO_DATE),
165             'month':    (MONTH_FROM_DATE, MONTH_TO_DATE),
166             'year':     (YEAR_FROM_DATE, YEAR_TO_DATE),
167         }[mode]
168     QUERY = '''
169                SET names 'utf8';
170                SELECT CONCAT(firstname, ';', lastname, ';', email, ';', last_renewed_on)
171                FROM foundationmembers
172                WHERE   last_renewed_on >= DATE_SUB(
173                          %(mysql_from_date)s ,
174                          INTERVAL 2 YEAR
175                       )
176                     AND last_renewed_on <= DATE_SUB(
177                          %(mysql_to_date)s ,
178                          INTERVAL 2 YEAR
179                        )
180                ORDER BY last_renewed_on;
181             '''.strip()
182     QUERY %= {'mysql_from_date': mysql_from_date,
183               'mysql_to_date': mysql_to_date}
184     infile = execute_query(QUERY)
185     memberlist = [Member.from_csv(line.strip()) for line in infile]
186     return memberlist
187
188 def get_members_election_token(election_id):
189     '''
190     Execute a SQL query to get a list of members with their temporary election
191     token.
192     
193     The token need to be created first, i.e. using smth like
194     
195     INSERT INTO election_tmp_tokens (election_id, member_id, tmp_token)
196     SELECT @election_id, id, SUBSTRING(MD5(RAND()) FROM 1 FOR 24) AS tmp_token
197     FROM electorate;
198     '''    
199     QUERY = '''
200                SET names 'utf8';
201                SELECT CONCAT(firstname, ';', lastname, ';', email, ';', tmp_token)
202                FROM election_tmp_tokens, foundationmembers
203                WHERE election_id = %(election_id)s
204                  AND member_id = id;
205             '''.strip()
206     QUERY %= {'election_id': election_id}
207     result = execute_query(QUERY)
208     memberlist = [Member.from_csv(line.strip()) for line in result]
209
210     return memberlist
211
212 if __name__ == "__main__":
213     parser = OptionParser()
214     parser.add_option("-l", "--loglevel", dest="loglevel",
215                       help="Sets the loglevel to one of debug, info, warn, "
216                             "error, critical", default="info")
217     parser.add_option("-m", "--mode", dest="mode",
218                       help="Which members to get: one of year, month, day, election"
219                             " [default: %default]", default="month")
220     parser.add_option("-s", "--send-mail", dest="sendmail",
221                       help="Do indeed send mail [default: %default]",
222                       action="store_true",
223                       default=False)
224     parser.add_option("-t", "--token", dest="token_for_election",
225                       help="Process temporary token, instead of renewals."
226                       "Please give the election id, which you should know, as "
227                       "argument to this parameter. A program argument is also required"
228                       " to read the instructions as a template."
229                       "[default: %default]",
230                       default=False)
231     parser.add_option("-1", "--one-only", dest="oneonlyaddress",
232                       help="Send one mail only to this address [default: %default]",
233                       default=None)
234     parser.add_option("-f", "--from-address", dest="fromaddress",
235                       help="Use that as sending address [default: %default]",
236                       default="Tobias Mueller <tobiasmue@gnome.org>")
237     (options, args) = parser.parse_args()
238     loglevel = {'debug': logging.DEBUG, 'info': logging.INFO,
239                 'warn': logging.WARN, 'error': logging.ERROR,
240                 'critical': logging.CRITICAL}.get(options.loglevel, "warn")
241     LOGFORMAT = "%(asctime)s %(levelname)-8s %(name)s %(message)s"
242     DATEFORMAT = '%Y-%m-%d %H:%M:%S'
243     logging.basicConfig(level=loglevel, format=LOGFORMAT, datefmt=DATEFORMAT)
244     log = logging.getLogger('main')
245     
246     if not options.token_for_election: # This is the default. We care about renewals
247         email_subject = 'Your GNOME Foundation Membership is about to expire'
248         logmsg = 'Needs Renewal: %s, %s, %s, %s'
249         template = TEMPLATE
250         members = get_members_which_need_renewal(options.mode)
251     else: # This is with -t option. We process election tokens.
252         email_subject = 'GNOME Foundation Board of Directors Elections %d - Voting Instructions' % datetime.date.today().year
253         logmsg = 'Sending Token: %s, %s, %s, %s'
254         template = open(args[0], "r").read()
255         members = get_members_election_token(options.token_for_election)
256         
257         
258
259     if options.oneonlyaddress:
260         members = [Member("firstname", "lastname", 
261                           options.oneonlyaddress, "2000-05-23")]
262         
263     
264     for member in members:
265         firstname, lastname, email, token_or_last_renewed_on = member.firstname, member.lastname, member.email, member.token_or_last_renewed_on
266         log.warn(logmsg, lastname, firstname, email, token_or_last_renewed_on)
267         emailtext = template % {'firstname':firstname, 'lastname':lastname,
268                                  'email': email, 'token_or_last_renewed_on': token_or_last_renewed_on,
269                                }
270         log.debug('The email to be sent is: %s', emailtext)
271         to = email
272         fromaddress = options.fromaddress
273         subject = email_subject
274         try:
275             if options.sendmail or options.oneonlyaddress:
276                 send_email(to, subject, emailtext, fromaddress)
277         except smtplib.SMTPException,e :
278             log.error('Error sending to %s: %s', to, e)
279             try:
280                 tfile = tempfile.NamedTemporaryFile(delete=False)
281             except TypeError:
282                 tfile = tempfile.NamedTemporaryFile()
283             tfile.writeline('To: %s' % to)
284             tfile.writeline('Subject: %s' % subject)
285             tfile.writeline('')
286             tfile.writeline('%s' % emailtext)
287             log.critical('eMail put in %s', tfile.name)