Révision 5ea0db1a
Ajouté par Jérôme Schneider il y a plus de 9 ans
calebasse/ressources/management/commands/reinit_schools_from_xls.py | ||
---|---|---|
1 |
# -*- coding: utf-8 -*- |
|
2 |
|
|
3 |
import re |
|
4 |
import os |
|
5 |
import xlrd |
|
6 |
|
|
7 |
from django.core.exceptions import ImproperlyConfigured |
|
8 |
from django.core.management.base import BaseCommand, CommandError |
|
9 |
|
|
10 |
from calebasse.ressources.models import School, SchoolType, Service |
|
11 |
|
|
12 |
|
|
13 |
class Command(BaseCommand): |
|
14 |
args = '<xls_folder>' |
|
15 |
help = 'Remove schools and import them from xls files' |
|
16 |
|
|
17 |
creche = SchoolType.objects.get(name=u'Crèche') |
|
18 |
maternelle = SchoolType.objects.get(name=u'Ecole maternelle') |
|
19 |
primaire = SchoolType.objects.get(name=u'Ecole primaire') |
|
20 |
college = SchoolType.objects.get(name=u'Collège') |
|
21 |
lycee = SchoolType.objects.get(name=u'Lycée') |
|
22 |
inconnu = SchoolType.objects.get(name=u'Inconnu') |
|
23 |
services = Service.objects.all() |
|
24 |
|
|
25 |
def clean_string(self, string): |
|
26 |
if isinstance(string, float): |
|
27 |
string = unicode("%d" % string) |
|
28 |
elif not isinstance(string, unicode): |
|
29 |
string = unicode(string) |
|
30 |
string = re.sub(r'\s+$', u'', string) |
|
31 |
string = re.sub(r'^\s+', u'', string) |
|
32 |
string = re.sub(r'\s+', u' ', string) |
|
33 |
string = re.sub(r'\(.*?\)', u'', string) |
|
34 |
return string |
|
35 |
|
|
36 |
def format_phone(self, number): |
|
37 |
num = unicode("%d" % number) |
|
38 |
num = unicode("0%s %s %s %s %s" % \ |
|
39 |
(num[0], num[1:3], num[3:5], num[5:7], num[7:9])) |
|
40 |
return num |
|
41 |
|
|
42 |
def save_schools(self, schools): |
|
43 |
for school in schools: |
|
44 |
mschool = School.objects.create(**school) |
|
45 |
mschool.save() |
|
46 |
mschool.services = self.services |
|
47 |
|
|
48 |
def parse_xls(self, book, mapper, private=False, first_row=1): |
|
49 |
""" Parse an xlrd book and return a dictionnary mapped with |
|
50 |
ressources.models.School object |
|
51 |
""" |
|
52 |
values = [] |
|
53 |
sheet = book.sheet_by_index(0) |
|
54 |
for i in range(first_row, sheet.nrows - 1): |
|
55 |
value = {'school_type': None} |
|
56 |
row = sheet.row(i) |
|
57 |
# Name |
|
58 |
value['name'] = self.clean_string(row[mapper['name']].value) |
|
59 |
# School Type |
|
60 |
school_type = self.clean_string(row[mapper['school_type']].value) |
|
61 |
school_type = school_type.replace('.', '') |
|
62 |
if school_type.lower() in (u"primaire", u"elémentaire"): |
|
63 |
value['school_type'] = self.primaire |
|
64 |
elif school_type.lower() in u"maternelle": |
|
65 |
value['school_type'] = self.maternelle |
|
66 |
elif school_type.lower() in (u"lp", u"lycée", u"sep", u"lycée professionnel"): |
|
67 |
value['school_type'] = self.lycee |
|
68 |
elif school_type.lower() == u"collège": |
|
69 |
value['school_type'] = self.college |
|
70 |
else: |
|
71 |
print "! WARNING: school type %r unmapple" % school_type |
|
72 |
value['school_type'] = self.inconnu |
|
73 |
# Address |
|
74 |
value['address'] = self.clean_string(row[mapper['address']].value) |
|
75 |
value['address_complement'] = u"" |
|
76 |
for j in mapper['address_complement']: |
|
77 |
value['address_complement'] += row[j].value + " " |
|
78 |
value['address_complement'] = self.clean_string(value['address_complement']) |
|
79 |
value['zip_code'] = self.clean_string(row[mapper['zip_code']].value) |
|
80 |
value['city'] = self.clean_string(row[mapper['city']].value) |
|
81 |
if isinstance(row[mapper['phone']].value, basestring): |
|
82 |
value['phone'] = self.clean_string(row[mapper['phone']].value) |
|
83 |
value['phone'] = value['phone'][:14] |
|
84 |
else: |
|
85 |
type(row[mapper['phone']].value) |
|
86 |
value['phone'] = self.format_phone(row[mapper['phone']].value) |
|
87 |
value['director_name'] = u"" |
|
88 |
for j in mapper['director_name']: |
|
89 |
value['director_name'] += row[j].value + " " |
|
90 |
value['director_name'] = self.clean_string(value['director_name']) |
|
91 |
value['private'] = private |
|
92 |
if value['name']: |
|
93 |
values.append(value) |
|
94 |
return values |
|
95 |
|
|
96 |
def handle(self, *args, **options): |
|
97 |
if len(args) != 1: |
|
98 |
raise CommandError('xls_folder is mandatory') |
|
99 |
xls_folder = args[0] |
|
100 |
print "Removing all schools ..." |
|
101 |
School.objects.all().delete() |
|
102 |
# Premier degre publique |
|
103 |
mapper = { |
|
104 |
'school_type': 0, |
|
105 |
'city': 1, |
|
106 |
'name': 2, |
|
107 |
'address': 4, |
|
108 |
'zip_code': 5, |
|
109 |
'phone': 6, |
|
110 |
'director_name': [7], |
|
111 |
'address_complement': [], |
|
112 |
} |
|
113 |
ecoles_pub = os.path.join(xls_folder, '1d_pub_ecoles.xls') |
|
114 |
print "Parsing %r ..." % ecoles_pub |
|
115 |
book = xlrd.open_workbook(ecoles_pub) |
|
116 |
schools = self.parse_xls(book, mapper) |
|
117 |
# Premier degre privee |
|
118 |
ecoles_priv = os.path.join(xls_folder, '1d_priv_ecoles.xls') |
|
119 |
print "Parsing %r ..." % ecoles_priv |
|
120 |
book = xlrd.open_workbook(ecoles_priv) |
|
121 |
book = xlrd.open_workbook(ecoles_priv) |
|
122 |
schools += self.parse_xls(book, mapper, True, 4) |
|
123 |
|
|
124 |
# Second degre publique |
|
125 |
mapper = { |
|
126 |
'city': 2, |
|
127 |
'school_type': 3, |
|
128 |
'name': 4, |
|
129 |
'address': 5, |
|
130 |
'address_complement': [6, 7], |
|
131 |
'zip_code': 8, |
|
132 |
'director_name': [10, 9], |
|
133 |
'phone': 15, |
|
134 |
} |
|
135 |
xls = os.path.join(xls_folder, '2d_pub.xls') |
|
136 |
print "Parsing %r ..." % xls |
|
137 |
book = xlrd.open_workbook(xls) |
|
138 |
schools += self.parse_xls(book, mapper) |
|
139 |
# Second degre privee |
|
140 |
xls = os.path.join(xls_folder, '2d_priv.xls') |
|
141 |
print "Parsing %r ..." % xls |
|
142 |
book = xlrd.open_workbook(xls) |
|
143 |
schools += self.parse_xls(book, mapper, True) |
|
144 |
|
|
145 |
self.save_schools(schools) |
|
146 |
|
Formats disponibles : Unified diff
ressources: new command reinit_schools_from_xls which reset schools
Usage example :
python manage.py reinit_schools_from_xls /home/jschneider/apps/calebasse/xls
WARNING: this script delete all schools