def save_school(self, school_data):
required_fields = {'LNG', 'LAT', 'CO_ENTIDADE', 'NO_ENTIDADE'}
if required_fields - set(school_data.keys()) == set():
brasil_environment_map = {
'urbana': 'urban',
'rural': 'rural',
}
environment = school_data.get('TP_LOCALIZACAO', '').lower()
environment = brasil_environment_map.get(environment, environment)
school, created = School.objects.update_or_create(
external_id=school_data['CO_ENTIDADE'],
country=self.country,
defaults={
'name': school_data['NO_ENTIDADE'],
'geopoint': Point(x=school_data['LNG'], y=school_data['LAT']),
'environment': environment,
'admin_1_name': school_data.get('NM_ESTADO', ''),
'admin_4_name': school_data.get('NM_MUNICIP', ''),
},
)
date = timezone.now().date()
school_weekly = SchoolWeeklyStatus.objects.filter(
school=school, week=date.isocalendar()[1], year=date.isocalendar()[0],
).last()
if not school_weekly:
school_weekly = SchoolWeeklyStatus.objects.filter(school=school).last()
if school_weekly:
# copy latest available one
school_weekly.id = None
school_weekly.year = get_current_year()
school_weekly.week = get_current_week()
else:
school_weekly = SchoolWeeklyStatus.objects.create(
school=school,
year=get_current_year(),
week=get_current_week(),
)
school_weekly.connectivity = True
school_weekly.computer_lab = bool(int(float(school_data.get('QT_COMP_ALUNO', 0))))
school_weekly.num_computers = int(float(school_data.get('QT_COMPUTADOR', 0)))
school_weekly.connectivity_type = school_data.get(
'TIPO_TECNOLOGIA', SchoolWeeklyStatus._meta.get_field('connectivity_type').default,
)
school_weekly.save()
def test_aggregate_country_daily_status_to_country_weekly_status(self):
CountryDailyStatusFactory(country=self.country,
date=datetime.now().date())
SchoolWeeklyStatusFactory(
school__country=self.country,
connectivity=True,
connectivity_speed=4000000,
coverage_availability=True,
coverage_type='unknown',
year=get_current_year(),
week=get_current_week(),
)
SchoolWeeklyStatusFactory(
school__country=self.country,
connectivity_speed=6000000,
year=get_current_year(),
week=get_current_week(),
)
country_weekly = CountryWeeklyStatus.objects.filter(
country=self.country).last()
if not country_weekly.is_verified:
country_weekly.update_country_status_to_joined()
update_country_weekly_status(self.country)
self.assertEqual(
CountryWeeklyStatus.objects.filter(country=self.country).count(),
1)
self.assertEqual(
CountryWeeklyStatus.objects.filter(
country=self.country).last().connectivity_speed, 5000000)
country_weekly = CountryWeeklyStatus.objects.filter(
country=self.country).last()
self.assertEqual(country_weekly.integration_status,
CountryWeeklyStatus.REALTIME_MAPPED)
self.assertEqual(
country_weekly.connectivity_availability,
CountryWeeklyStatus.CONNECTIVITY_TYPES_AVAILABILITY.realtime_speed)
self.assertEqual(
country_weekly.coverage_availability, CountryWeeklyStatus.
COVERAGE_TYPES_AVAILABILITY.coverage_availability)
def aggregate_school_daily_status_to_school_weekly_status(country) -> bool:
date = timezone.now().date()
week_ago = date - timedelta(days=7)
schools = School.objects.filter(
country=country,
id__in=SchoolDailyStatus.objects.filter(
date__gte=week_ago, ).values_list(
'school',
flat=True).order_by('school_id').distinct('school_id'),
).iterator()
updated = False
for school in schools:
updated = True
school_weekly, created = SchoolWeeklyStatus.objects.get_or_create(
school=school,
week=get_current_week(),
year=get_current_year(),
)
aggregate = SchoolDailyStatus.objects.filter(
school=school,
date__gte=week_ago,
).aggregate(
Avg('connectivity_speed'),
Avg('connectivity_latency'),
)
school_weekly.connectivity = True
school_weekly.connectivity_speed = aggregate['connectivity_speed__avg']
school_weekly.connectivity_latency = aggregate[
'connectivity_latency__avg']
prev_weekly = SchoolWeeklyStatus.objects.filter(
school=school, date__lt=school_weekly.date).last()
if prev_weekly:
school_weekly.num_students = prev_weekly.num_students
school_weekly.num_teachers = prev_weekly.num_teachers
school_weekly.num_classroom = prev_weekly.num_classroom
school_weekly.num_latrines = prev_weekly.num_latrines
school_weekly.running_water = prev_weekly.running_water
school_weekly.electricity_availability = prev_weekly.electricity_availability
school_weekly.computer_lab = prev_weekly.computer_lab
school_weekly.num_computers = prev_weekly.num_computers
school_weekly.save()
return updated
def test_aggregate_school_daily_status_to_school_weekly_status_connectivity_no(
self):
today = datetime.now().date()
SchoolDailyStatusFactory(school=self.school,
connectivity_speed=None,
date=today - timedelta(days=8))
SchoolWeeklyStatusFactory(
school=self.school,
week=get_current_week(),
year=get_current_year(),
connectivity=False,
)
aggregate_school_daily_status_to_school_weekly_status(self.country)
self.assertEqual(SchoolWeeklyStatus.objects.count(), 1)
self.assertEqual(SchoolWeeklyStatus.objects.last().connectivity, False)
def test_aggregate_school_daily_status_to_school_weekly_status_connectivity_unknown(
self):
# daily status is too old, so it wouldn't be involved into country calculations
today = datetime.now().date()
SchoolDailyStatusFactory(school=self.school,
connectivity_speed=None,
date=today - timedelta(days=8))
SchoolWeeklyStatusFactory(
school=self.school,
week=get_current_week(),
year=get_current_year(),
connectivity=None,
)
aggregate_school_daily_status_to_school_weekly_status(self.country)
self.assertEqual(SchoolWeeklyStatus.objects.count(), 1)
self.assertEqual(SchoolWeeklyStatus.objects.last().connectivity, None)
def update_country_weekly_status(country: Country):
last_weekly_status_country = country.last_weekly_status
country_status, created = CountryWeeklyStatus.objects.get_or_create(
country=country,
year=get_current_year(),
week=get_current_week(),
)
if created:
country.last_weekly_status = country_status
country.save()
country.last_weekly_status.integration_status = last_weekly_status_country.integration_status
country.last_weekly_status.save(update_fields=('integration_status', ))
# calculate pie charts. first we need to understand which case is applicable for country
latest_statuses = SchoolWeeklyStatus.objects.filter(
school__country=country, _school__isnull=False)
connectivity_types = CountryWeeklyStatus.CONNECTIVITY_TYPES_AVAILABILITY
coverage_types = CountryWeeklyStatus.COVERAGE_TYPES_AVAILABILITY
if RealTimeConnectivity.objects.filter(school__country=country).exists():
country_status.connectivity_availability = connectivity_types.realtime_speed
connectivity_stats = aggregate_connectivity_by_speed(latest_statuses)
elif latest_statuses.filter(connectivity_speed__gte=0).exists():
country_status.connectivity_availability = connectivity_types.static_speed
connectivity_stats = aggregate_connectivity_by_speed(latest_statuses)
elif latest_statuses.filter(connectivity__isnull=False).exists():
country_status.connectivity_availability = connectivity_types.connectivity
connectivity_stats = aggregate_connectivity_by_availability(
latest_statuses)
else:
country_status.connectivity_availability = connectivity_types.no_connectivity
connectivity_stats = aggregate_connectivity_default(latest_statuses)
if latest_statuses.exclude(
coverage_type=SchoolWeeklyStatus.COVERAGE_TYPES.unknown).exists():
country_status.coverage_availability = coverage_types.coverage_type
coverage_stats = aggregate_coverage_by_types(latest_statuses)
elif latest_statuses.filter(coverage_availability__isnull=False).exists():
country_status.coverage_availability = coverage_types.coverage_availability
coverage_stats = aggregate_coverage_by_availability(latest_statuses)
else:
country_status.coverage_availability = coverage_types.no_coverage
coverage_stats = aggregate_coverage_default(latest_statuses)
# remember connectivity pie chart
country_status.schools_connectivity_good = connectivity_stats[
ColorMapSchema.GOOD]
country_status.schools_connectivity_moderate = connectivity_stats[
ColorMapSchema.MODERATE]
country_status.schools_connectivity_no = connectivity_stats[
ColorMapSchema.NO]
country_status.schools_connectivity_unknown = connectivity_stats[
ColorMapSchema.UNKNOWN]
# remember coverage pie chart
country_status.schools_coverage_good = coverage_stats[ColorMapSchema.GOOD]
country_status.schools_coverage_moderate = coverage_stats[
ColorMapSchema.MODERATE]
country_status.schools_coverage_no = coverage_stats[ColorMapSchema.NO]
country_status.schools_coverage_unknown = coverage_stats[
ColorMapSchema.UNKNOWN]
# calculate speed & latency where available
schools_stats = latest_statuses.aggregate(
total=Count('*'),
connectivity_speed=Avg('connectivity_speed',
filter=Q(connectivity_speed__gt=0)),
connectivity_latency=Avg('connectivity_latency',
filter=Q(connectivity_latency__gt=0)),
)
country_status.connectivity_speed = schools_stats['connectivity_speed']
country_status.connectivity_latency = schools_stats['connectivity_latency']
country_status.schools_total = schools_stats['total']
schools_with_data = country_status.schools_connectivity_moderate + country_status.schools_connectivity_good
country_status.schools_connected = schools_with_data
if country_status.schools_total:
country_status.schools_with_data_percentage = 1.0 * country_status.schools_connected
country_status.schools_with_data_percentage /= country_status.schools_total
else:
country_status.schools_with_data_percentage = 0
# move country status as far as we can
if country_status.integration_status == CountryWeeklyStatus.COUNTRY_CREATED and country_status.schools_total:
country_status.integration_status = CountryWeeklyStatus.SCHOOL_OSM_MAPPED
if country_status.integration_status == CountryWeeklyStatus.JOINED and country_status.schools_total:
country_status.integration_status = CountryWeeklyStatus.SCHOOL_MAPPED
if country_status.integration_status == CountryWeeklyStatus.SCHOOL_MAPPED and any(
[
country_status.schools_connectivity_good,
country_status.schools_connectivity_moderate,
country_status.schools_connectivity_no,
country_status.schools_coverage_good,
country_status.schools_coverage_moderate,
country_status.schools_coverage_no,
]):
country_status.integration_status = CountryWeeklyStatus.STATIC_MAPPED
if country_status.integration_status == CountryWeeklyStatus.STATIC_MAPPED \
and country_status.connectivity_availability == connectivity_types.realtime_speed:
country_status.integration_status = CountryWeeklyStatus.REALTIME_MAPPED
country_status.avg_distance_school = country.calculate_avg_distance_school(
)
country_status.save()