Mercurial > sat_docs

comparison scripts/association/update_garradin.py @ 51:c34633e3f56a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
add a script to semi-automatically update garradin database (work in progress)
author souliane <souliane@mailoo.org>
date Mon, 28 Sep 2015 11:16:23 +0200
parents
children
comparison
equal deleted inserted replaced
50:fe116a11199b 51:c34633e3f56a
1 #!/usr/bin/python
2 # -*- coding: utf-8 -*-
3
4 # Scripts to update Garradin database from various CSV inputs.
5 # Copyright (C) 2015 Adrien Cossa <souliane@mailoo.org>
6
7 # This program is free software: you can redistribute it and/or modify
8 # it under the terms of the GNU Affero General Public License as published by
9 # the Free Software Foundation, either version 3 of the License, or
10 # (at your option) any later version.
11
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU Affero General Public License for more details.
16
17 # You should have received a copy of the GNU Affero General Public License
18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
19
20 import sqlite3
21 import csv, unicodecsv
22 import os
23 import os.path
24 import tempfile
25 from glob import glob
26 from time import strftime
27 from datetime import datetime
28 from collections import OrderedDict
29
30
31 ### PATHS AND OTHER STUFF TO CONFIGURE ###
32
33
34 DB_FILE = u"./association.sqlite"
35 INPUT_PATH = u"../../../sat_private"
36
37 APAYER_FILES = (u"apayer", u"SALUT-A-TOI_*.csv")
38 CM_FILES = (u"credit_mutuel/dernieres_operations", "*_*.csv")
39 DJANGO_FILES = (u"django", u"sat_website_subscriptions.csv")
40
41 LOG_PATH = u"./OUTPUT"
42 INFO, WARNING = 1, 2
43 LOG_LEVELS = (INFO, WARNING)
44
45
46 def dirtyGetDateOfFile(path):
47 """Extract the date at the end of the filename, just before the extension.
48
49 @return: datetime
50 """
51 # FIXME: there should be a better way to do that
52 token = os.path.basename(path)[-12:-4]
53 return datetime.strptime(token, "%Y%m%d")
54
55
56 ### !!! DO NOT MODIFY BELOW THIS LINE !!! ###
57
58
59 ### DATE AND TIME ###
60
61 DATE_ORIGIN = u"1970-01-01"
62 DATE_FORMAT = "%Y-%m-%d"
63 TODAY = strftime(DATE_FORMAT)
64
65
66 ### RETRIEVE DATABASE INFOS ###
67
68
69 class GarradinDB(object):
70
71 TABLES = ("membres", "cotisations", "cotisations_membres")
72
73 def __init__(self, cursor):
74 self.cursor = cursor
75
76 def getTableInfos(self):
77 infos = {}
78 for table in self.TABLES:
79 cursor.execute("PRAGMA table_info(%s)" % table)
80 rows = cursor.fetchall()
81 infos[table] = OrderedDict()
82 for row in rows:
83 # don't use {row[1]: row[2]...} : it messes the order
84 infos[table][row[1]] = row[2] # field name: field type
85 return infos
86
87 def getLastAutoUpdate(self):
88 """Get the datetime of the last database automatic update.
89
90 @return: datetime
91 """
92 self.cursor.execute("SELECT valeur FROM config WHERE cle='last_auto_update'")
93 row = self.cursor.fetchone()
94 try:
95 date = row[0]
96 except TypeError:
97 date = DATE_ORIGIN
98 return datetime.strptime(date, DATE_FORMAT)
99
100 def setLastAutoUpdate(self, date=TODAY):
101 """Set to date of the last database automatic update to today."""
102 self.cursor.execute("REPLACE INTO config VALUES ('last_auto_update', ?)", (date,))
103
104
105 con = sqlite3.connect(DB_FILE)
106 with con:
107 cursor = con.cursor()
108 db = GarradinDB(cursor)
109 TABLE_INFOS = db.getTableInfos()
110 LAST_AUTO_UPDATE = db.getLastAutoUpdate()
111
112
113 ### LOGGERS ###
114
115
116 class Logger(object):
117
118 def __init__(self, path):
119 if not os.path.isdir(path):
120 os.mkdir(path)
121 basename = "update_garradin_{day}_%s.log".format(day=strftime("%Y%m%d"))
122 self.info_file = os.path.join(path, basename % "info")
123 self.warning_file = os.path.join(path, basename % "warning")
124
125 def write(self, file, msg):
126 with open(file, 'a') as file:
127 file.write((u"[%s] %s\n" % (strftime("%d/%m/%Y@%H:%m:%S"), msg)).encode("utf-8"))
128
129 def info(self, msg):
130 self.write(self.info_file, msg)
131 if INFO in LOG_LEVELS:
132 print msg
133
134 def warning(self, msg):
135 self.write(self.warning_file, msg)
136 if WARNING in LOG_LEVELS:
137 print msg
138
139 logger = Logger(LOG_PATH)
140
141
142 ### CSV DIALECTS ###
143
144
145 # FIXME: Python CSV doesn't handle CSV file starting with a BOM
146
147 class my_dialect(csv.excel):
148 """Describe my properties for CSV files."""
149 delimiter = ';'
150 csv.register_dialect("my_dialect", my_dialect)
151
152
153 ### GENERIC ENTRIES' CLASSES ###
154
155
156 class Entry(OrderedDict):
157
158 TYPE_INTEGER = u"INTEGER"
159 TYPE_TEXT = u"TEXT"
160
161 ID_UNIQUE_KEYS = ("FIELD_NAME", "FIELD_SURNAME", "FIELD_EMAIL") # define unique members
162 ID_EQUAL_KEYS = ID_UNIQUE_KEYS # define equality, not even needed to update the database
163
164 FIELD_NAME = u"prenom"
165 FIELD_SURNAME = u"nom"
166 FIELD_EMAIL = u"email"
167
168 FIELDS = [FIELD_NAME, FIELD_SURNAME, FIELD_EMAIL]
169
170 MATCH_NONE = 0
171 MATCH_INCOMPLETE = 1
172 MATCH_UNIQUE = 2
173 MATCH_EQUAL = 3
174
175 matched = MATCH_NONE
176
177 def __init__(self, entry):
178 """
179
180 @param entry(dict or row): entry data
181 """
182 if isinstance(entry, dict):
183 data = OrderedDict({key: value for key, value in entry.iteritems() if key in self.FIELDS})
184 if hasattr(entry, "identityDict"):
185 data.update(entry.identityDict(self))
186 else:
187 data = self.row2dict(entry)
188 OrderedDict.__init__(self, data)
189
190 def __iter__(self):
191 # XXX: use the order of self.FIELDS and ignore OrderedDict internal order
192 for field in self.FIELDS:
193 if field in self:
194 yield field
195
196 def row2dict(self, row):
197 """Return a dict representation of a row.
198
199 @param row (list[unicode]): a row of information
200 @return: OrderedDict {unicode: unicode} with:
201 - key: field name
202 - value: field value
203 """
204 data = OrderedDict()
205 index = 0
206 for field in self.FIELDS:
207 if row[index]:
208 data[field] = row[index]
209 index += 1
210 return data
211
212 def identityDict(self, cls=None):
213 """Return a dict representing a user identity.
214
215 @param cls (class): if specified, the result dict will use cls's
216 identity fields for its own keys (instead of self's fields).
217 @return: dict{unicode: unicode}
218 """
219 if cls is None:
220 cls = self
221 return {getattr(cls, key): self[getattr(self, key)] for key in self.ID_EQUAL_KEYS}
222
223 def identityMatches(self, entry):
224 """Return a dict compiling the identity matches with another entry.
225
226 @param entry (Entry): another entry
227 @return: dict{unicode: bool}
228 """
229 return {key: self[getattr(self, key)] == entry[getattr(entry, key)] for key in self.ID_EQUAL_KEYS}
230
231 def identityMatch(self, entry):
232 """Return a value representing the matching level with another entry.
233
234 @param entry (Entry): another entry
235 @return: int in (Entry.MATCH_NONE, Entry.MATCH_INCOMPLETE, Entry.MATCH_UNIQUE, Entry.MATCH_EQUAL)
236 """
237 matches = self.identityMatches(entry)
238 if len([match for match in matches.values() if not match]) == 0:
239 return self.MATCH_EQUAL
240 match_name = matches["FIELD_NAME"] and matches["FIELD_SURNAME"]
241 if match_name and matches["FIELD_EMAIL"]:
242 return self.MATCH_UNIQUE
243 if match_name or matches["FIELD_EMAIL"]:
244 return self.MATCH_INCOMPLETE
245 return self.MATCH_NONE
246
247 def identityToString(self):
248 """Return a human-readable string representing a user identity.
249
250 @return: unicode
251 """
252 return "%s %s <%s>" % (self[self.FIELD_NAME], self[self.FIELD_SURNAME], self[self.FIELD_EMAIL])
253
254 def valuesToString(self):
255 """List the values.
256
257 @return: unicode
258 """
259 return u", ".join(self.values())
260
261 def fields(self):
262 """List the fields to which a value is associated.
263
264 @return: list[unicode]
265 """
266 return [field for field in self.FIELDS if field in self]
267
268 def fieldsToString(self):
269 """List the fields to which a value is associated.
270
271 @return: unicode
272 """
273 return u", ".join(self.fields())
274
275 def changeset(self):
276 """Return the list of fields and values as needed to update the database.
277
278 @return: tuple(unicode, list)
279 """
280 changes = ['%s=?' % field for field in self.fields()]
281 return (u", ".join(changes), self.values())
282
283
284 class GarradinDBEntry(Entry):
285
286 def __init__(self, cursor, entry):
287 self.cursor = cursor
288 Entry.__init__(self, entry)
289
290 def insertOrReplace(self):
291 """Insert or replace the entry in the garradin database."""
292 fields = self.fieldsToString()
293 args = self.values()
294 placeholders = ",".join([u"?" for arg in args])
295 # XXX: "INSERT OR REPLACE" is needed for updating lastrowid, "REPLACE" is not enough
296 request = "INSERT OR REPLACE INTO %s (%s) VALUES (%s)" % (self.TABLE, fields, placeholders)
297 self.cursor.execute(request, args)
298
299
300 class CSVEntry(Entry):
301
302 def date(self):
303 """Return the date associated with this entry.
304
305 @return: datetime
306 """
307 try:
308 format = self.DATE_FORMAT
309 except AttributeError:
310 format = DATE_FORMAT
311 return datetime.strptime(self[self.FIELD_DATE], format)
312
313
314 ### MEMBERS ENTRIES ###
315
316
317 class GarradinDBMember(GarradinDBEntry):
318
319 TABLE = u"membres"
320 FIELDS = TABLE_INFOS[TABLE].keys()
321
322 FIELD_ID_CATEGORY = u"id_categorie"
323 FIELD_PASSWORD = u"passe"
324 FIELD_AUTO_UPDATE = u"date_maj_auto"
325 FIELD_ADDRESS = u"adresse"
326
327 ID_EQUAL_KEYS = Entry.ID_UNIQUE_KEYS + ("FIELD_ADDRESS",)
328
329 def __init__(self, cursor, entry):
330 GarradinDBEntry.__init__(self, cursor, entry)
331 try:
332 self.pop(self.FIELD_PASSWORD)
333 except KeyError:
334 pass
335
336 if hasattr(entry, "address"):
337 self[self.FIELD_ADDRESS] = entry.address()
338
339 def setAutoUpdate(self):
340 """Update the value of the field self.FIELD_AUTO_UPDATE."""
341 self[self.FIELD_AUTO_UPDATE] = TODAY
342
343 def values(self):
344 """List the values.
345
346 @return: list
347 """
348 values = []
349 for field, value in self.iteritems():
350 if TABLE_INFOS[self.TABLE].get(field, self.TYPE_TEXT) == self.TYPE_TEXT:
351 value = u"%s" % value.replace('\r', '').replace('\n', '\\n')
352 values.append(unicode(value))
353 return values
354
355
356 class GarradinDBMembership(GarradinDBEntry):
357
358 TABLE = u"cotisations_membres"
359 FIELDS = TABLE_INFOS[TABLE].keys()
360
361 FIELD_MEMBER = u"id_membre"
362 FIELD_MEMBERSHIP = u"id_cotisation"
363 FIELD_DATE = u"date"
364
365
366 class GarradinDBMembershipType(GarradinDBEntry):
367
368 TABLE = u"cotisations"
369 FIELDS = TABLE_INFOS[TABLE].keys()
370
371 FIELD_AMOUNT = u"montant"
372
373
374 class GarradinCSVMember(CSVEntry):
375 """Like GarradinDBMember but with a "categorie" field in second position."""
376
377 FIELD_ID_CATEGORY = u"id_categorie"
378 FIELD_CATEGORY = u"categorie"
379 FIELD_ADDRESS = u"adresse"
380
381 FIELDS = GarradinDBMember.FIELDS[:1] + [FIELD_CATEGORY] + GarradinDBMember.FIELDS[1:]
382
383 def __init__(self, cursor, db_member):
384 """
385
386 @param db_member (GarradinDBMember)
387 """
388 CSVEntry.__init__(self, db_member)
389
390 if self.FIELD_ID_CATEGORY not in self:
391 self[self.FIELD_ID_CATEGORY] = 1
392 if self.FIELD_CATEGORY not in self:
393 cursor.execute("SELECT nom FROM membres_categories WHERE id=?", (self[self.FIELD_ID_CATEGORY],))
394 rows = cursor.fetchone()
395 self[self.FIELD_CATEGORY] = rows[0]
396
397
398 class ApayerCSVEntry(CSVEntry):
399
400 FIELD_NAME = u"Prenom"
401 FIELD_SURNAME = u"Nom"
402 FIELD_EMAIL = u"Courriel"
403 FIELD_AMOUNT = u"Montant"
404 FIELD_DATE = u"DatePaiement"
405 DATE_FORMAT = u"%d/%m/%y"
406
407 # XXX: there's a BOM at the beginning of the CSV file
408 FIELDS = ["Mnemonique", "TPE", "DatePaiement", "ObjetPaiement",
409 "AutreObjet", "Montant", "Devise", "Reference",
410 "Commentaire", "ReferencBancaire", "NumeroAutorisation",
411 "Nom", "Prenom", "Adresse", "CodePostal", "Ville", "Courriel",
412 "Etat", "MotifRefus", "Cvx", "Vld", "Brand", "Status3DS"]
413
414 FILTERED_OUT = ("", u"Mnemonique", "TPE", "Devise", "ReferencBancaire",
415 "NumeroAutorisation", "Cvx", "Vld", "Brand", "Status3DS")
416
417 def address(self):
418 return u"\n".join([self["Adresse"], self["CodePostal"], self["Ville"]])
419
420
421 class DjangoCSVEntry(CSVEntry):
422
423 FIELD_NAME = u"prenom"
424 FIELD_SURNAME = u"nom"
425 FIELD_EMAIL = u"courriel"
426 FIELD_AMOUNT = u"montant"
427 FIELD_DATE = u"date"
428
429 FIELDS = ["date", "prenom", "nom", "adresse", "courriel", "jid", "montant",
430 "moyen_paiement", "reference", "commentaire", "lettre_info",
431 "langue"]
432
433
434
435 ### ENTRIES FOR THE FINANCES ###
436
437
438 class GarradinDBOperation(GarradinDBEntry):
439
440 TABLE = u"compta_journal"
441
442
443 class GarradinCSVOperation(CSVEntry):
444
445 FIELDS = (u"Numéro mouvement", u"Date", u"Type de mouvement", u"Catégorie",
446 u"Libellé", u"Montant", u"Compte de débit - numéro",
447 u"Compte de débit - libellé", u"Compte de crédit - numéro",
448 u"Compte de crédit - libellé", u"Moyen de paiement",
449 u"Numéro de chèque", u"Numéro de pièce", u"Remarques")
450
451
452 class CmCSVOperation(CSVEntry):
453
454 FIELD_DATE = u"Date d'opération"
455 DATE_FORMAT = u"%d/%m/%Y"
456
457 FIELDS = (FIELD_DATE, u"Date de valeur", u"Débit", u"Crédit",
458 u"Libellé", u"Solde")
459
460
461 ### CLASSES FOR THE IMPORTS ###
462
463
464 class EntryMatcher(object):
465
466 def __init__(self, cursor):
467 self.cursor = cursor
468
469 def processEntryForCSVExport(self, entry, context):
470 """Return the processed entry ready to be exported to CSV,
471 or None if the entry is conflicting with another one already existing.
472
473 @param entry (Entry): an Entry instance
474 @param context (unicode): context indication for the user
475 @return: GarradinCSVMember or None
476 """
477 # this method is not optimised but it's sensible... don't mess with it.
478 tmp_entry = GarradinDBMember(self.cursor, entry)
479 base_entry = self.getMatchingEntry(tmp_entry, context)
480 if base_entry is None:
481 return None
482 base_entry.update(tmp_entry)
483
484 new_entry = GarradinCSVMember(self.cursor, base_entry)
485 new_entry.matched = base_entry.matched
486 return new_entry
487
488 def getMatchingEntry(self, new_entry, context):
489 """Try to get a matching entry, the behavior depends of the found match:
490
491 - no match at all: returns a new empty entry
492 - incomplete match: returns None and write a warning in the logs
493 - unique or equal match: returns the matching database entry
494
495 @param new_entry (GarradinDBMember): entry to be matched
496 @param context (unicode): context indication for the user
497 @return: GarradinDBMember or None:
498 """
499 self.cursor.execute("SELECT * FROM %s" % new_entry.TABLE)
500 rows = self.cursor.fetchall()
501
502 for row in rows:
503 row = list(row)
504 current_entry = GarradinDBMember(self, row)
505 match = current_entry.identityMatch(new_entry)
506 if match == Entry.MATCH_NONE:
507 continue # try to match next row
508
509 current_entry.matched = match
510
511 if match == Entry.MATCH_EQUAL:
512 return current_entry # we still need to update the membership
513 if match == Entry.MATCH_INCOMPLETE:
514 log = logger.warning
515 desc = (u"INCOMPLETE", u"A MANUAL check and process is required!")
516 elif match == Entry.MATCH_UNIQUE:
517 log = logger.info
518 desc = (u"unique", u"The database entry will be updated.")
519 log(u"A %s match has been found between these two entries from \"%s\" and garradin's \"%s\" table:" % (desc[0], context, current_entry.TABLE))
520 log(u" - %s" % new_entry.valuesToString())
521 log(u" - %s" % current_entry.valuesToString())
522 log(" --> %s" % desc[1])
523 return current_entry if match == Entry.MATCH_UNIQUE else None
524
525 return GarradinDBMember(self, {})
526
527 class MembershipManager(object):
528 def __init__(self, cursor):
529 self.cursor = cursor
530 self.memberships = {}
531
532 def setCache(self, email, amount, date):
533 """Write a membership in cache for later database update.
534
535 @param email (unicode): email adress of the member
536 @param amount (int): amount of the membership fee
537 @param date (datetime): date of the subscription
538 """
539 self.memberships[email] = (amount, date)
540
541 def getMembershipAmount(self, membership_id):
542 """Return a membership amount.
543
544 @param membership_id (int): membership ID in the "cotisations" table
545 @return: int
546 """
547 self.cursor.execute("SELECT %s FROM %s WHERE id=?" % (GarradinDBMembershipType.FIELD_AMOUNT, GarradinDBMembershipType.TABLE), (membership_id,))
548 return int(self.cursor.fetchone()[0])
549
550 def getMemberIdentity(self, member_id):
551 """Return a human-readable identity.
552
553 @param member_id (int): member ID in the "membres" table
554 @return: unicode
555 """
556 self.cursor.execute("SELECT * FROM %s WHERE id=?" % GarradinDBMember.TABLE, (member_id,))
557 return GarradinDBMember(self, self.cursor.fetchone()).identityToString()
558
559 def getMembershipType(self, amount):
560 """Return the suitable membership type ID and amount for the given amount.
561
562 @param amount (int): payment amount
563 @return: (int, int)
564 """
565 request = "SELECT id, MAX({field}) FROM {table} WHERE {field} <= ?".format(field=GarradinDBMembershipType.FIELD_AMOUNT, table=GarradinDBMembershipType.TABLE)
566 self.cursor.execute(request, (amount,))
567 return self.cursor.fetchone()
568
569 def updateMembershipFromCache(self, member_id, email):
570 """Look in the cache to update the membership fee of a member.
571
572 @param member_id (int): member ID in the "membres" table
573 @param email(unicode): member email
574 """
575 membership = self.memberships.pop(email, None)
576 if membership:
577 amount, date = membership
578 self.updateMembership(member_id, amount, date)
579
580 def updateMembership(self, member_id, amount, date_payment):
581 """Update the membership fee of a member.
582
583 @param member_id (int): member ID in the "membres" table
584 @param amount (float): amount of the membership fee
585 @param date_payment (unicode): date of the payment
586 """
587 member = self.getMemberIdentity(member_id)
588 self.cursor.execute("SELECT * FROM %s WHERE id_membre=?" % GarradinDBMembership.TABLE, (member_id,))
589 rows = self.cursor.fetchall()
590 entries = [GarradinDBMembership(self.cursor, row) for row in rows]
591 entries.sort(key=lambda entry: entry[entry.FIELD_DATE])
592
593 # first we check for inconsistency
594 last_date, last_amount = datetime.strptime(DATE_ORIGIN, DATE_FORMAT), 0
595 for entry in entries:
596 current_date = datetime.strptime(entry[entry.FIELD_DATE], DATE_FORMAT)
597 current_amount = self.getMembershipAmount(entry[entry.FIELD_MEMBERSHIP])
598 if (current_date - last_date).days < 365:
599 logger.warning(u"At least two memberships within less than one year have been registered for %s:" % member)
600 logger.warning(u" - € %d on %s" % (last_amount, last_date))
601 logger.warning(u" - € %d on %s" % (current_amount, current_date))
602 logger.warning(u" --> Please fix it MANUALLY!")
603 last_date, last_amount = current_date, current_amount
604
605 if (date_payment - last_date).days < 365:
606 amount += last_amount # cumulate two payments
607 else:
608 data = {GarradinDBMembership.FIELD_MEMBER: member_id,
609 GarradinDBMembership.FIELD_DATE: date_payment}
610 entry = GarradinDBMembership(self.cursor, data)
611
612 m_type_id, m_type_amount = self.getMembershipType(amount)
613 delta = amount - m_type_amount
614 if delta:
615 logger.warning(u"We received € %.2f from %s: a membership fee of € %d will be registered." % (amount, member, m_type_amount))
616 logger.warning(u" --> Please MANUALLY register a donation of € %.2f." % delta)
617 entry[entry.FIELD_MEMBERSHIP] = m_type_id
618 entry.insertOrReplace()
619
620 class MembersImporter(object):
621
622 def __init__(self, cursor):
623 self.cursor = cursor
624 self.fails = []
625 self.membership_manager = MembershipManager(cursor)
626
627 def fixRowFromLeadingEqualSign(self, row):
628 """Fix leading equal sign in CSV field since it is not handled by Python.
629
630 @param data (dict{unicode: unicode}): row data
631 """
632 for field, value in row.iteritems():
633 if value.startswith('="') and value.endswith('"'):
634 row[field] = value[2:-1]
635
636 def importFromCSV(self, input_csv, output_csv=None):
637 """Import members from a CSV to garradin.
638
639 @param input_csv (unicode): path to the input CSV file
640 @param output_csv (unicode): path to store intermediate Garradin CSV
641 """
642 tmp_csv = output_csv if output_csv else tempfile.mkstemp()[1]
643 matcher = EntryMatcher(self.cursor)
644 with open(input_csv, 'r') as csv_file:
645 reader = unicodecsv.DictReader(csv_file, dialect="my_dialect")
646 logger.info("Processing %s..." % input_csv)
647 with open(tmp_csv, 'w') as tmp_file:
648 writer = unicodecsv.DictWriter(tmp_file, GarradinCSVMember.FIELDS, dialect="my_dialect")
649 fails = []
650 for row in reader:
651 input_entry = self.CSV_ENTRY_CLASS(row)
652
653 date = input_entry.date()
654 if (LAST_AUTO_UPDATE - date).days > self.AGE_LIMIT:
655 continue # skip older entries
656
657 self.fixRowFromLeadingEqualSign(input_entry)
658 if not self.checkEntry(input_entry):
659 self.fails.append(input_entry)
660 continue
661 entry = matcher.processEntryForCSVExport(input_entry, self.CONTEXT)
662 if not entry:
663 continue
664 if entry.matched != Entry.MATCH_EQUAL:
665 writer.writerow(entry)
666
667 self.extraProcessEntry(input_entry, entry)
668 self.printFails()
669
670 self.importFromGarradinCSV(tmp_csv)
671 if not output_csv:
672 os.remove(tmp_csv)
673
674 def checkEntry(self, input_entry):
675 return True
676
677 def printFails(self):
678 pass
679
680 def extraProcessEntry(self, input_entry, entry):
681 pass
682
683 def importFromGarradinCSV(self, csv_file):
684 """Import a garradin CSV to the database.
685
686 @param csv_file (unicode): file to import
687 """
688 with open(csv_file, 'r') as file:
689 reader = unicodecsv.reader(file, dialect="my_dialect")
690 for row in reader:
691 row.pop(1) # remove the category name
692 db_entry = GarradinDBMember(self.cursor, row)
693 db_entry.setAutoUpdate()
694 db_entry.insertOrReplace()
695
696 self.membership_manager.updateMembershipFromCache(self.cursor.lastrowid, db_entry[db_entry.FIELD_EMAIL])
697
698 action = "updated" if db_entry.get('id', None) else "added"
699 logger.warning("The member %s has been %s." % (db_entry.identityToString(), action))
700
701
702 class ApayerImporter(MembersImporter):
703
704 CSV_ENTRY_CLASS = ApayerCSVEntry
705
706 # since we receive the CSV every Sunday, operations can be one week old
707 # without having been processed yet, even if you do a daily auto update
708 AGE_LIMIT = 14
709
710 CONTEXT = u"apayer"
711
712 STATE_ACCEPTED = u"Paiement accepté (payé)"
713
714 def checkEntry(self, input_entry):
715 return input_entry["Etat"] == self.STATE_ACCEPTED
716
717 def printFails(self):
718 if self.fails:
719 logger.warning(u"The following \"apayer\" operations have NOT been completed, you MAY want to contact the users:")
720 for row in self.fails:
721 fields = ["%s: %s" % (key, value) for key, value in row.iteritems() if key not in ApayerCSVEntry.FILTERED_OUT and value]
722 logger.warning(u" - %s" % ", ".join(fields))
723
724 def extraProcessEntry(self, input_entry, entry):
725 # update membership fee
726 date = input_entry.date()
727 amount = float(input_entry[input_entry.FIELD_AMOUNT])
728 if entry.get("id", None):
729 self.membership_manager.updateMembership(entry["id"], amount, date)
730 else: # we don't know the member ID yet
731 self.membership_manager.setCache(entry[entry.FIELD_EMAIL], amount, date)
732
733
734 class DjangoImporter(MembersImporter):
735
736 CSV_ENTRY_CLASS = DjangoCSVEntry
737
738 # if you do a daily auto update, this file is processed everyday
739 AGE_LIMIT = 2
740
741 CONTEXT = u"django form"
742
743 def extraProcessEntry(self, input_entry, entry):
744 date = input_entry.date()
745 if not entry.get("id", None): # new user
746 self.membership_manager.setCache(entry[entry.FIELD_EMAIL], 0, date)
747
748
749 class CmImporter(object):
750
751 AGE_LIMIT = 60
752
753 def importFromCSV(self, csv_file):
754 """Import financial operation from credit mutuel CSV to garradin.
755
756 @param csv_file (unicode): path to django form CSV file
757 """
758 raise NotImplementedError # TODO: update database with entry
759
760
761 ### MAIN CLASS ###
762
763
764 class GarradinUpdater(object):
765
766 def __init__(self, cursor):
767 self.cursor = cursor
768
769 def importFromApayer(self):
770 """Import CSV files from apayer"""
771 for apayer_csv in glob(os.path.join(INPUT_PATH, *APAYER_FILES)):
772 if dirtyGetDateOfFile(apayer_csv) >= LAST_AUTO_UPDATE:
773 ApayerImporter(self.cursor).importFromCSV(apayer_csv)
774
775 def importFromDjango(self):
776 """Import all Django CSV files"""
777 # FIXME: import JID, subscription to mailing list and comment
778 for django_csv in glob(os.path.join(INPUT_PATH, *DJANGO_FILES)):
779 DjangoImporter(self.cursor).importFromCSV(django_csv)
780
781 def importFromCm(self):
782 """Import all credit mutuel CSV files"""
783 for cm_csv in glob(os.path.join(INPUT_PATH, *CM_FILES)):
784 if dirtyGetDateOfFile(cm_csv) >= LAST_AUTO_UPDATE:
785 CmImporter(self.cursor).importFromCSV(cm_csv)
786
787
788 con = sqlite3.connect(DB_FILE)
789 with con:
790 cursor = con.cursor()
791 updater = GarradinUpdater(cursor)
792 updater.importFromApayer()
793 updater.importFromDjango()
794 #updater.importFromCm()
795 GarradinDB(cursor).setLastAutoUpdate("2015-06-01")