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