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