def retrieve_border_station_locations(self, request, *args, **kwargs):
"""
retrieve all locations for a particular border_station
"""
station = BorderStation.objects.get(id=self.kwargs['pk'])
Location.get_or_create_other_location(station)
Location.get_or_create_leave_location(station)
self.object_list = self.filter_queryset(
self.get_queryset().filter(border_station=station))
if request.GET.get('include_inactive') is None:
self.object_list = self.object_list.filter(active=True)
if request.GET.get('location_type') is not None:
self.object_list = self.object_list.filter(
location_type=request.GET.get('location_type'))
self.object_list = self.object_list.order_by('-active', 'name')
serializer = self.get_serializer(self.object_list, many=True)
return Response(serializer.data)
def processArrests(self, file_name, start_year, start_month, end_year,
end_month, country):
results = {}
with open(file_name) as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
if row['Closed Station'] != '' and row['Closed Arrested'] != '':
self.processArrest(row['Closed Station'],
row['Closed Arrested'], results)
if row['Inactive Station'] != '' and row[
'Inactive Arrested'] != '':
self.processArrest(row['Inactive Station'],
row['Inactive Arrested'], results)
if row['Active Station'] != '' and row['Active Arrested'] != '':
self.processArrest(row['Active Station'],
row['Active Arrested'], results)
stations = BorderStation.objects.filter(
operating_country__name=country)
start_year_month = 100 * start_year + start_month
for station in stations:
year = start_year
month = start_month
year_month = 100 * year + month
end_year_month = 100 * end_year + end_month
if station.station_name in results:
station_result = results[station.station_name]
expected_total = 0
for key in station_result.keys():
expected_total += station_result[key]
else:
station_result = {}
expected_total = 0
other_location = Location.get_or_create_other_location(station)
total = 0
while year_month <= end_year_month:
location_total = LocationStatistics.objects.filter(
location__border_station=station,
year_month=year_month).exclude(
location=other_location).aggregate(
Sum('arrests'))['arrests__sum']
if location_total is None:
location_total = 0
if year_month in station_result:
result_total = station_result[year_month]
#print (station.station_name, 'result_total', year_month, result_total)
total += result_total
else:
result_total = 0
if location_total > result_total:
diff = result_total
entries = LocationStatistics.objects.filter(
location__border_station=station,
year_month=year_month)
for entry in entries:
entry.arrests = 0
entry.save()
else:
diff = result_total - location_total
if diff > 0:
try:
location_stats = LocationStatistics.objects.get(
location=other_location, year_month=year_month)
except ObjectDoesNotExist:
location_stats = LocationStatistics()
location_stats.location = other_location
location_stats.year_month = year_month
location_stats.arrests = diff
location_stats.save()
month += 1
if month > 12:
month = 1
year += 1
year_month = 100 * year + month
print(station.station_name, expected_total, total)
# Process arrests prior to the starting date
for year_month in station_result.keys():
if year_month < start_year_month:
try:
location_stats = LocationStatistics.objects.get(
location=other_location, year_month=year_month)
except ObjectDoesNotExist:
location_stats = LocationStatistics()
location_stats.location = other_location
location_stats.year_month = year_month
location_stats.arrests = station_result[year_month]
total += station_result[year_month]
location_stats.save()
print(station.station_name, expected_total, total, station_result)
def process_station_statistics(self, file_name, start_year, start_month,
end_year, end_month, to_include):
process_location_statistics = False
for key in self.location_map.keys():
if key in to_include and to_include[key]:
process_location_statistics = True
process_location_staff = False
for key in self.staff_map.keys():
if key in to_include and to_include[key]:
process_location_staff = True
with open(file_name) as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
station_code = row['Station Code']
try:
station = BorderStation.objects.get(
station_code=station_code)
except ObjectDoesNotExist:
print('Unable to find station with code', station_code)
continue
month = start_month
year = start_year
while year * 100 + month <= end_year * 100 + end_month:
if month < 10:
str_month = '0' + str(month)
else:
str_month = str(month)
year_month = str(year) + str_month
year_month_csv = ' ' + str(year) + ' ' + str_month
try:
entry = StationStatistics.objects.get(
station=station, year_month=year_month)
except ObjectDoesNotExist:
entry = StationStatistics()
entry.station = station
entry.year_month = year_month
for key in self.station_map.keys():
if key + year_month_csv in row:
value = row[key + year_month_csv]
value = value.replace(',', '')
if value != '':
setattr(entry, self.station_map[key], value)
entry.save()
if process_location_statistics:
self.reset_location_data(station, year_month,
to_include)
other_location = Location.get_or_create_other_location(
station)
try:
entry = LocationStatistics.objects.get(
location=other_location, year_month=year_month)
except ObjectDoesNotExist:
entry = LocationStatistics()
entry.location = other_location
entry.year_month = year_month
modified = False
for key in self.location_map.keys():
#print (year_month, key, to_include[key], key + year_month_csv in row)
if to_include[key] and key + year_month_csv in row:
value = row[key + year_month_csv]
value = value.replace(',', '')
if value != '':
setattr(entry, self.location_map[key],
value)
modified = True
if modified:
entry.save()
if process_location_staff:
self.reset_location_staff(station, year_month,
to_include)
other_location = Location.get_or_create_other_location(
station)
try:
entry = LocationStatistics.objects.get(
location=other_location, year_month=year_month)
except ObjectDoesNotExist:
entry = LocationStatistics()
entry.location = other_location
entry.year_month = year_month
general_staff = Staff.get_or_create_general_staff(
station)
try:
entry = LocationStaff.objects.get(
location=other_location,
staff=general_staff,
year_month=year_month)
except ObjectDoesNotExist:
entry = LocationStaff()
entry.location = other_location
entry.staff = general_staff
entry.year_month = year_month
modified = False
for key in self.staff_map.keys():
if to_include[key] and key + year_month_csv in row:
value = row[key + year_month_csv]
value = value.replace(',', '')
if value != '':
setattr(entry, self.staff_map[key], value)
modified = True
if modified:
entry.save()
month += 1
if month > 12:
year += 1
month = 1
def processLocationStatistics(self, file_name, start_year, start_month,
end_year, end_month, mapping, to_include):
process_location_statistics = False
for key in self.location_map.keys():
if key in to_include and to_include[key]:
process_location_statistics = True
process_location_staff = False
for key in self.staff_map.keys():
if key in to_include and to_include[key]:
process_location_staff = True
with open(file_name) as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
tmp_station_code = row['Station Code']
try:
station = BorderStation.objects.get(
station_code=station_code)
except:
continue
month = start_month
year = start_year
while year * 100 + month <= end_year * 100 + end_month:
if month < 10:
str_month = '0' + str(month)
else:
str_month = str(month)
year_month = str(year) + str_month
year_month_csv = ' ' + str(year) + ' ' + str_month
if process_location_statistics:
self.reset_location_data(station, year_month,
to_include)
if process_location_staff:
self.reset_location_staff(station, year_month,
to_include)
skip = False
with open(file_name) as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
tmp_station_code = row['Station Code']
tmp_location = row['Location'].strip()
if tmp_location in mapping:
tmp_location = mapping[tmp_location]
if tmp_location == 'TOTAL' or tmp_location == '':
continue
if tmp_location != '' and tmp_location != 'TOTAL' and len(
tmp_station_code) == 3:
station_code = tmp_station_code
try:
station = BorderStation.objects.get(
station_code=station_code)
skip = False
except ObjectDoesNotExist:
print('Unable to find station with code', station_code)
skip = True
continue
elif skip:
continue
try:
if tmp_location == '__Other':
location = Location.get_or_create_other_location(
station)
else:
location = Location.objects.get(
border_station=station, name__iexact=tmp_location)
except ObjectDoesNotExist:
print('Unable to find location with name',
'"' + tmp_location + '"')
continue
month = start_month
year = start_year
while year * 100 + month <= end_year * 100 + end_month:
if month < 10:
str_month = '0' + str(month)
else:
str_month = str(month)
year_month = str(year) + str_month
year_month_csv = ' ' + str(year) + ' ' + str_month
if process_location_statistics:
try:
entry = LocationStatistics.objects.get(
location=location, year_month=year_month)
except ObjectDoesNotExist:
entry = LocationStatistics()
entry.location = location
entry.year_month = year_month
modified = False
for key in self.location_map.keys():
if key + year_month_csv in row:
value = row[key + year_month_csv]
value = value.replace(',', '')
old_value = getattr(entry,
self.location_map[key])
if old_value is None:
old_value = 0
if value != '':
setattr(entry, self.location_map[key],
old_value + int(value))
#print (station.station_code, location.name, key + year_month_csv, old_value, value, entry.id)
modified = True
if modified:
entry.save()
if process_location_staff:
general_staff = Staff.get_or_create_general_staff(
station)
try:
entry = LocationStaff.objects.get(
location=location,
staff=general_staff,
year_month=year_month)
except ObjectDoesNotExist:
entry = LocationStaff()
entry.location = location
entry.staff = general_staff
entry.year_month = year_month
modified = False
for key in self.staff_map.keys():
if key + year_month_csv in row:
value = row[key + year_month_csv]
value = value.replace(',', '')
if value != '':
setattr(entry, self.staff_map[key], value)
modified = True
if modified:
entry.save()
month += 1
if month > 12:
year += 1
month = 1
def process_fixes(self, fix_file, fixes):
with open(fix_file) as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
if row['Station1'] != '':
if row['New Location'] == 'TRUE':
fixes['new_location'].append({
'station':
row['Station1'],
'location':
row['Location']
})
elif row['Matching SL Name'] == '':
print('__Other for', row['Location'])
fixes['mapping'][row['Location']] = "__Other"
else:
fixes['mapping'][
row['Location']] = row['Matching SL Name']
if row['New Location Name'] != '':
fixes['rename'][row['Current Location']] = {
'station': row['Station2'],
'new_location': row['New Location Name']
}
if row['Currently Monitoring'] == 'FALSE':
fixes['not_monitoring'][
row['Current Location']] = row['Station2']
for new_location in fixes['new_location']:
try:
existing = Location.objects.get(
border_station__station_name=new_location['station'],
name=new_location['location'])
print('location', new_location['location'],
'already exists for station',
new_location['station'])
except ObjectDoesNotExist:
location = Location()
print('new_location, border_station',
new_location['station'])
location.border_station = BorderStation.objects.get(
station_name=new_location['station'])
location.name = new_location['location']
location.save()
for key, value in fixes['rename'].items():
try:
location = Location.objects.get(
border_station__station_name=value['station'],
name=key)
location.name = value['new_location']
location.save()
except ObjectDoesNotExist:
print('rename location', key, value, 'not found')
for key, value in fixes['not_monitoring'].items():
try:
location = Location.objects.get(
border_station__station_name=value, name=key)
except ObjectDoesNotExist:
print('Unable to find location for station', value,
'location', key)
location.active = False
location.save()
for key, value in fixes['mapping'].items():
if value in fixes['rename']:
fixes['mapping'][key] = fixes['rename'][value][
'new_location']
def handle(self, *args, **options):
current_date = datetime.datetime.now()
if options['year'] and options['month']:
year = options['year'][0]
month = options['month'][0]
else:
month = current_date.month
year = current_date.year
month -= 1
if month < 1:
month = 12 + month
year -= 1
if month > 11:
end_date = str(year + 1) + '-01-01'
else:
end_date = str(year) + '-' + str(month + 1) + '-01'
start_date = str(year) + '-' + str(month) + '-01'
last_country = None
year_month = year * 100 + month
if month == 1:
prior_year_month = (year - 1) * 100 + 12
else:
prior_year_month = year_month - 1
# create exchange rate entries
countries = Country.objects.all()
for country in countries:
try:
exchange = CountryExchange.objects.get(country=country,
year_month=year_month)
except ObjectDoesNotExist:
exchange = CountryExchange()
exchange.country = country
exchange.year_month = year_month
try:
prior = CountryExchange.objects.get(
country=country, year_month=prior_year_month)
exchange.exchange_rate = prior.exchange_rate
except ObjectDoesNotExist:
exchange.exchange_rate = 1.0
exchange.save()
# make sure location statistics exists for each active location
locations = Location.objects.filter(active=True)
for location in locations:
if location.border_station is not None and 'hasProjectStats' in location.border_station.features:
try:
location_statistics = LocationStatistics.objects.get(
location=location, year_month=year_month)
except ObjectDoesNotExist:
location_statistics = LocationStatistics()
location_statistics.location = location
location_statistics.year_month = year_month
location_statistics.intercepts = 0
location_statistics.intercepts_evidence = 0
location_statistics.intercepts_high_risk = 0
location_statistics.intercepts_invalid = 0
country = location_statistics.location.border_station.operating_country
if 'legal_arrest_and_conviction' in country.options and country.options[
'legal_arrest_and_conviction']:
location_statistics.arrests = 0
location_statistics.save()
intercepts = IntercepteeCommon.objects.filter(
person__role='PVOT',
interception_record__logbook_second_verification_date__gte=
start_date,
interception_record__logbook_second_verification_date__lt=end_date,
interception_record__date_of_interception__gte='2020-10-01')
for intercept in intercepts:
country = intercept.interception_record.station.operating_country
try:
location = Location.objects.get(
border_station=intercept.interception_record.station,
name__iexact=intercept.interception_record.location)
except ObjectDoesNotExist:
location = Location.get_or_create_other_location(
intercept.interception_record.station)
try:
location_statistics = LocationStatistics.objects.get(
location=location, year_month=year_month)
except ObjectDoesNotExist:
location_statistics = LocationStatistics()
location_statistics.year_month = year_month
location_statistics.location = location
location_statistics.intercepts = 0
location_statistics.intercepts_evidence = 0
location_statistics.intercepts_high_risk = 0
location_statistics.intercepts_invalid = 0
if 'legal_arrest_and_conviction' in country.options and country.options[
'legal_arrest_and_conviction']:
location_statistics.arrests = 0
if intercept.interception_record.logbook_second_verification.startswith(
'Evidence'):
location_statistics.intercepts_evidence += 1
elif intercept.interception_record.logbook_second_verification.startswith(
'High'):
location_statistics.intercepts_high_risk += 1
elif intercept.interception_record.logbook_second_verification.startswith(
'Should not'):
location_statistics.intercepts_invalid += 1
location_statistics.intercepts = location_statistics.intercepts_evidence + location_statistics.intercepts_high_risk
location_statistics.save()
for country in countries:
if 'legal_arrest_and_conviction' in country.options and country.options[
'legal_arrest_and_conviction']:
# Count arrests
suspects = LegalCaseSuspect.objects.filter(
legal_case__station__operating_country=country,
arrest_submitted_date__gte=start_date,
arrest_submitted_date__lt=end_date)
for suspect in suspects:
try:
location = Location.objects.get(
border_station=suspect.legal_case.station,
name__iexact=suspect.legal_case.location)
except ObjectDoesNotExist:
location = Location.get_or_create_other_location(
suspect.legal_case.station)
try:
location_statistics = LocationStatistics.objects.get(
location=location, year_month=year_month)
except ObjectDoesNotExist:
location_statistics = LocationStatistics()
location_statistics.year_month = year_month
location_statistics.location = location
location_statistics.intercepts = 0
location_statistics.intercepts_evidence = 0
location_statistics.intercepts_high_risk = 0
location_statistics.intercepts_invalid = 0
location_statistics.arrests = 0
location_statistics.arrests += 1
border_stations = BorderStation.objects.all().order_by(
'operating_country')
for station in border_stations:
country = station.operating_country
try:
entry = StationStatistics.objects.get(year_month=year_month,
station=station)
except ObjectDoesNotExist:
entry = StationStatistics()
entry.year_month = year_month
entry.station = station
entry.save()
if entry.subcommittee_members is None:
entry.subcommittee_members = CommitteeMember.objects.filter(
border_station=station).count()
if entry.active_monitor_locations is None:
locations = Location.objects.filter(border_station=station,
location_type='monitoring',
active=True)
entry.active_monitor_locations = len(locations)
if entry.active_shelters is None:
shelters = Location.objects.filter(border_station=station,
location_type='shelter',
active=True)
entry.active_shelters = len(shelters)
if 'legal_arrest_and_conviction' in country.options and country.options[
'legal_arrest_and_conviction'] and entry.convictions is None:
entry.convictions = 0
# Budget
try:
budget = BorderStationBudgetCalculation.objects.get(
border_station=station,
month_year__year=year,
month_year__month=month)
entry.budget = budget.station_total()
except ObjectDoesNotExist:
pass
entry.gospel = GospelVerification.objects.filter(
vdf__station=station,
form_changes='No',
date_of_followup__gte=start_date,
date_of_followup__lt=end_date).count()
# gospel
# empowerment
if 'legal_arrest_and_conviction' in country.options and country.options[
'legal_arrest_and_conviction']:
# Count convictions
entry.convictions = LegalCaseSuspect.objects.filter(
verdict='Conviction',
legal_case__station=station,
verdict_submitted_date__gte=start_date,
verdict_submitted_date__lt=end_date).count()
# cif count
entry.save()
def update_station_data(self, request):
station_id = request.data['station']
year_month = request.data['year_month']
station = BorderStation.objects.get(id=station_id)
if request.data['budget'] == '':
budget = None
else:
budget = request.data['budget']
if request.data['empowerment'] == '':
empowerment = None
else:
empowerment = request.data['empowerment']
try:
station_statistics = StationStatistics.objects.get(station=station, year_month=year_month)
except ObjectDoesNotExist:
station_statistics = StationStatistics()
station_statistics.station = station
station_statistics.year_month = year_month
station_statistics.budget = budget
station_statistics.empowerment = empowerment
if 'hasStaff' in station.features and (not station.operating_country.enable_all_locations or not 'hasLocationStaffing' in station.features):
other_location = Location.get_or_create_other_location(station)
general_staff = Staff.get_or_create_general_staff(station)
if request.data['staff'] == '':
staff_value = None
else:
staff_value = request.data['staff']
if staff_value is None:
try:
location_staff = LocationStaff.objects.get(location=other_location, staff=general_staff, year_month=year_month)
location_staff.work_fraction = None
location_staff.save()
except ObjectDoesNotExist:
# LocationStaff does not exist and new values are blank - so ignore
pass
else:
try:
location_staff = LocationStaff.objects.get(location=other_location, staff=general_staff, year_month=year_month)
except ObjectDoesNotExist:
location_staff = LocationStaff()
location_staff.location = other_location
location_staff.staff = general_staff
location_staff.year_month = year_month
location_staff.work_fraction = staff_value
location_staff.save()
if not station.operating_country.enable_all_locations:
# if enable_all_lcoations is not true, then the arrests can be updated here
arrests = request.data['arrests']
if arrests is None or arrests=="":
try:
location_statistics = LocationStatistics.objects.get(location=other_location, year_month=year_month)
location_statistics.arrests = None
location_statistics.save()
except ObjectDoesNotExist:
# LocationStatistics does not exist and new valuse are blank - so ignore
pass
else:
try:
location_statistics = LocationStatistics.objects.get(location=other_location, year_month=year_month)
except ObjectDoesNotExist:
location_statistics = LocationStatistics()
location_statistics.location = other_location
location_statistics.year_month = year_month
location_statistics.arrests = arrests
location_statistics.save()
station_statistics.save()
serializer = StationStatisticsSerializer(station_statistics, context={'request': request})
return Response(serializer.data)
def process_row(self, in_row, renum):
irf = IrfBangladesh()
row = dict(in_row)
irf_number = row['IRF #'].strip()
if irf_number in self.cleaned:
cleaned_addresses = self.cleaned[irf_number]
else:
print('Unable to find new address for IRF # ', irf_number)
return
if irf_number == renum['old'].strip():
row['IRF #'] = renum['new']
else:
print('renumber fail ',row['IRF #'],renum['old'])
return
station_code = renum['new'][:3]
if station_code is None:
print ('Unable to process row with Station Code of None')
return
try:
station = BorderStation.objects.get(station_code=station_code)
except Exception:
print('Unable to locate station for code ' + station_code)
return
location_name = row['Location']
if location_name is not None and location_name.strip() != '':
existing = Location.objects.filter(name=location_name, border_station = station)
if len(existing) < 1:
location = Location()
location.name = location_name
location.border_station = station
location.save()
irf.entered_by = Account.objects.get(id=10022)
irf.station = station
irf.status = 'in-progress'
for question_id in ImportBangladesh.main_questions.keys():
entry = ImportBangladesh.main_questions[question_id]
if 'columns' not in entry:
continue
if len(entry['columns']) > 1:
pass
else:
column = entry['columns'][0]
value = row[column]
question = Question.objects.get(id=question_id)
#print('<<<<',question.export_name, '>>>>', value)
if 'contains' in entry:
if value is not None and entry['contains'] in value:
result = True
else:
result = False
elif 'map' in entry:
if value in entry['map']:
result = entry['map'][value]
else:
result = value
elif question.answer_type.name == 'Checkbox' and (question.params is None or 'textbox' not in question.params):
if value is None or value.upper() == 'NO':
result = False
elif value.upper() == 'YES':
result = True
elif question.answer_type.name == 'DateTime':
local_time = parse(value)
tz = pytz.timezone(station.time_zone)
result = tz.localize(local_time)
elif ((question.answer_type.name == 'Integer' or question.answer_type.name == 'Float') and
(value is None or value.strip() == '')):
result = 0
else:
result = value
if (result is None or result == '') and 'default' in entry:
result = entry['default']
storage = QuestionStorage.objects.get(question_id = question_id)
setattr(irf, storage.field_name, result)
if irf.irf_number is None or irf.irf_number == '':
return
try:
irf.save()
except Exception as exc:
print (irf.irf_number, exc.args[0])
return
for idx in range(1,3):
prefix = 'Victim ' + str(idx) + ' '
addr_prefix = 'V' + str(idx) + ' '
self.create_interceptee(row, 'v', prefix, addr_prefix, irf, cleaned_addresses)
for idx in range(1,3):
prefix = 'Trafficker ' + str(idx) + ' '
addr_prefix = 'T' + str(idx) + ' '
self.create_interceptee(row, 't', prefix, addr_prefix, irf, cleaned_addresses)
irf.status = 'approved'
form_data = FormData(irf, self.form)
validate = ValidateForm(self.form, form_data, True)
validate.validate()
if len(validate.errors) < 1:
irf.save()