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
|
|