def unregistered_obj(self, db: Session):
auditlog.unregister(models.SimpleModel)
obj = models.SimpleModel(text="No history")
db.add(obj)
db.commit()
db.refresh(obj)
return obj
def handle(self, *args, **options):
code_to_city_name = read_post_codes(options['pcf_file'])
from auditlog.registry import auditlog
# Unregister Contact model from auditlog
auditlog.unregister(Contact)
contacts = Contact.objects.filter(city__isnull=True)
self.stdout.write('{} contacts without city'.format(contacts.count()))
for contact in contacts:
if not contact.postal_code:
self.stdout.write(
'Contact id {} has no postal code. Skipping.'.format(
contact.id))
continue
if contact.postal_code not in code_to_city_name.keys():
self.stdout.write(
'Contact id {} has an unknown postal code "{}". Skipping.'.
format(contact.id, contact.postal_code))
continue
contact.city = code_to_city_name[contact.postal_code]
contact.save()
def execute(self):
from auditlog.registry import auditlog
# Unregister all models from auditlog when importing
for model in list(auditlog._registry.keys()):
auditlog.unregister(model)
self.import_leases()
def execute(self):
from auditlog.registry import auditlog
# Unregister all models from auditlog when importing
for model in list(auditlog._registry.keys()):
auditlog.unregister(model)
self.update_credit_notes()
self.update_interest_invoices()
def handle(self, *args, **options): # noqa: C901 TODO
from auditlog.registry import auditlog
# Unregister all models from auditlog when importing
for model in list(auditlog._registry.keys()):
auditlog.unregister(model)
detailed_plans = Area.objects.filter(type=AreaType.DETAILED_PLAN)
if not detailed_plans:
self.stdout.write(
"Detailed plan: No detailed plans found in area table")
return
for detailed_plan in detailed_plans:
match_data = {
"identifier": detailed_plan.identifier,
}
detailed_plan_class = None
if detailed_plan.metadata.get("state_name") == "Vireillä":
detailed_plan_class = DetailedPlanClass.PENDING
if detailed_plan.metadata.get("state_name") == "Voimassa":
detailed_plan_class = DetailedPlanClass.EFFECTIVE
lawfulness_date = detailed_plan.metadata.get("final_date")
acceptor = ""
plan_stage = ""
diary_number = ""
pre_detailed_plan = Area.objects.filter(
type=AreaType.PRE_DETAILED_PLAN,
identifier=detailed_plan.identifier)
if pre_detailed_plan:
acceptor = pre_detailed_plan.metadata.get("acceptor")
plan_stage = pre_detailed_plan.metadata.get("plan_stage")
diary_number = pre_detailed_plan.metadata.get("diary_number")
other_data = {
"acceptor": acceptor,
"detailed_plan_class": detailed_plan_class,
"plan_stage": plan_stage,
"diary_number": diary_number,
"lawfulness_date": lawfulness_date,
}
DetailedPlan.objects.update_or_create(defaults=other_data,
**match_data)
def handle(self, *args, **options):
destination_path = (
Path(settings.MEDIA_ROOT) / CollectionLetterTemplate.file.field.upload_to
)
if not self.check_is_directory_writable(destination_path):
raise CommandError('Directory "" is not writable'.format(destination_path))
source_path = Path(options["source_directory"])
from auditlog.registry import auditlog
auditlog.unregister(CollectionLetterTemplate)
for name, template in TEMPLATE_NAMES.items():
self.stdout.write(name)
source_filename = source_path / template["filename"]
if not source_filename.exists():
self.stdout.write(
' Template file "{}" does not exist in the source directory {}'.format(
template["filename"], source_path
)
)
continue
try:
clt = CollectionLetterTemplate.objects.get(name=name)
self.stdout.write(" Template already exists. Overwriting.")
destination_filename = clt.file.name
except CollectionLetterTemplate.DoesNotExist:
self.stdout.write(" Creating new template.")
destination_filename = (
Path(CollectionLetterTemplate.file.field.upload_to)
/ template["filename"]
)
CollectionLetterTemplate.objects.create(
name=name, file=str(destination_filename)
)
destination_path = Path(settings.MEDIA_ROOT) / destination_filename
self.stdout.write(
' Copying "{}" to "{}"'.format(source_filename, destination_path)
)
copyfile(source_filename, destination_path)
def log_event(request, start, end, action: int, extra=None):
if start == None and end == None:
raise ValueError('start and end cannot both be none!')
object_key = start.id if start != None else end.id
instance = start if start != None else end
# Determine if instance has created/last updated
# We don't want to log these fields
exclude = []
try:
instance._meta.get_field('created')
exclude.append('created')
except:
pass
try:
instance._meta.get_field('last_updated')
exclude.append('last_updated')
except:
pass
# Register the type to allow logging
auditlog.register(type(instance), exclude_fields=exclude)
log = LogEntry.objects.log_create(
instance,
object_pk=object_key,
object_id=object_key,
object_repr=str(start) if start != None else str(end),
action=action,
changes=model_instance_diff(start, end),
actor_id=request.user.id,
timestamp=timezone.now(),
content_type_id=
6, # This is the id for auditlog listed in django_content_types table
remote_addr=get_client_ip(request),
additional_data=extra)
# Unregister the type to disable auto (duplicate) logging
auditlog.unregister(type(instance))
def handle(self, *args, **options): # noqa: C901 TODO
from auditlog.registry import auditlog
logging.basicConfig(level=logging.INFO, format="%(message)s", stream=sys.stdout)
verbosity = options.get("verbosity")
if verbosity == 0:
logger.setLevel(logging.WARNING)
elif verbosity >= 2:
logger.setLevel(logging.DEBUG)
# Unregister all models from auditlog when importing
for model in list(auditlog._registry.keys()):
auditlog.unregister(model)
for rent in Rent.objects.all().iterator():
try:
rent.calculate_payable_rent()
except Exception as e:
logger.exception(
"Failed to calculate payable rent (%s): %s" % (rent.id, str(e))
)
continue
def handle(self, *args, **options): # noqa: C901 TODO
from auditlog.registry import auditlog
# Unregister all models from auditlog when importing
for model in list(auditlog._registry.keys()):
auditlog.unregister(model)
leases = Lease.objects.all()
for lease in leases:
self.stdout.write("Lease #{} {}:".format(lease.id,
lease.identifier))
lease_areas = {
la.get_normalized_identifier(): la
for la in lease.lease_areas.all()
}
self.stdout.write(" Existing lease areas: {}".format(", ".join(
lease_areas.keys())))
areas = Area.objects.filter(type=AreaType.LEASE_AREA,
identifier=str(lease.identifier))
if not areas:
self.stdout.write(
"Lease #{} {}: No lease areas found in area table".format(
lease.id, lease.identifier))
for area in areas:
plan_unit_ids_handled = []
area_identifier = area.get_normalized_identifier()
if area_identifier not in lease_areas.keys():
self.stdout.write(
"Lease #{} {}: Area id {} not in lease areas of lease!"
.format(lease.id, lease.identifier, area_identifier))
continue
lease_areas[area_identifier].geometry = area.geometry
lease_areas[area_identifier].save()
self.stdout.write(" Lease area FOUND. SAVED.")
# Clear all plots and plan units safely which aren't in contracts
del_plots = (lease_areas[area_identifier].plots.exclude(
in_contract=True).delete())
self.stdout.write(
" Cleared existing current Plots ({}) not in contract".
format(del_plots))
try:
# Get intersected areas with lease area's geometry but exclude the type of lease area and plot
# division and also geometries which only touches the lease area
intersected_areas = (Area.objects.filter(
geometry__intersects=area.geometry).exclude(type__in=[
AreaType.LEASE_AREA, AreaType.PLOT_DIVISION
]).exclude(geometry__touches=area.geometry))
except InternalError as e:
self.stdout.write(str(e))
continue
for intersect_area in intersected_areas:
self.stdout.write(" #{} {} {}".format(
intersect_area.id,
intersect_area.identifier,
intersect_area.type,
))
# As of 21.1.2020, there are about 250 of plan unit area objects that have {'area': None, ...}
# in their metadata json field. I suspect this is due to accidental duplications in the source db
# since it seems like there is usually another object with identical metadata and identifier fields
# (except also having a value for the 'area' key) to be found, which we want to actually use.
if not intersect_area.metadata.get("area"):
self.stdout.write(
"Lease #{} {}: DISCARD area {}: no 'area' value in metadata"
.format(lease.id, lease.identifier,
intersect_area.id))
continue
try:
# Discard too small intersect area
intersection = (
intersect_area.geometry
& lease_areas[area_identifier].geometry)
intersection.transform(3879)
if intersection.area < 1:
self.stdout.write(
"Lease #{} {}: DISCARD area {}: intersection area too small"
.format(lease.id, lease.identifier,
intersect_area.id))
continue
except GEOSException as e:
self.stdout.write(str(e))
continue
if (intersect_area.type == AreaType.REAL_PROPERTY
or intersect_area.type
== AreaType.UNSEPARATED_PARCEL):
# If the intersect area's type is real property (kiinteistö) or unseparated parcel (määräala)
# then update or create the intersect area to plots
match_data = {
"lease_area":
lease_areas[area_identifier],
"type":
PlotType[intersect_area.type.value.upper()],
"identifier":
intersect_area.get_denormalized_identifier(),
"is_master":
True,
}
rest_data = {
"area":
float(intersect_area.metadata.get("area")),
"section_area":
intersection.area,
"registration_date":
intersect_area.metadata.get("registration_date"),
"repeal_date":
intersect_area.metadata.get("repeal_date"),
"geometry":
intersect_area.geometry,
}
(plot, plot_created) = Plot.objects.update_or_create(
defaults=rest_data, **match_data)
self.stdout.write(
"Lease #{} {}: Plot #{} ({}) saved".format(
lease.id, lease.identifier, plot.id,
plot.type))
elif intersect_area.type == AreaType.PLAN_UNIT:
# Find the plot division (tonttijako) that intersects the most
plot_division_area = (Area.objects.filter(
geometry__intersects=intersect_area.geometry,
type=AreaType.PLOT_DIVISION,
).extra(
select={
"interarea":
"ST_Area(ST_Transform(ST_Intersection(geometry, '{}'), 3879))"
.format(intersect_area.geometry)
}).order_by("-interarea").first())
# If the plot division area exist then create/update plan unit informations
if plot_division_area and plot_division_area.interarea > 0:
# Get or create plot division state
(
plot_division_state,
created,
) = PlotDivisionState.objects.get_or_create(
name=plot_division_area.metadata.get(
"state_name"))
# Get detailed plan area
detailed_plan_area = Area.objects.filter(
type=AreaType.DETAILED_PLAN,
identifier=intersect_area.metadata.get(
"detailed_plan_identifier"),
).first()
# The variable is unused for some reason
detailed_plan_latest_processing_date = None
# If detailed plan area exist, set the identifier
detailed_plan_identifier = None
if detailed_plan_area:
detailed_plan_identifier = detailed_plan_area.identifier
# Get or create plan unit type
(
plan_unit_type,
created,
) = PlanUnitType.objects.get_or_create(
name=intersect_area.metadata.get("type_name"))
# Get or create plan unit state
(
plan_unit_state,
created,
) = PlanUnitState.objects.get_or_create(
name=intersect_area.metadata.get("state_name"))
# If the intersect area has intended use name, get or create it to plan unit intended use
plan_unit_intended_use = None
if intersect_area.metadata.get(
"intended_use_name"):
(
plan_unit_intended_use,
created,
) = PlanUnitIntendedUse.objects.get_or_create(
name=intersect_area.metadata.get(
"intended_use_name"))
match_data = {
"lease_area":
lease_areas[area_identifier],
"identifier":
intersect_area.get_denormalized_identifier(),
"is_master":
True,
}
rest_data = {
"area":
float(intersect_area.metadata.get("area")),
"section_area":
intersection.area,
"geometry":
intersect_area.geometry,
"plot_division_identifier":
plot_division_area.identifier,
"plot_division_date_of_approval":
plot_division_area.metadata.get(
"date_of_approval"),
"plot_division_effective_date":
plot_division_area.metadata.get(
"effective_date"),
"plot_division_state":
plot_division_state,
"detailed_plan_identifier":
detailed_plan_identifier,
"detailed_plan_latest_processing_date":
detailed_plan_latest_processing_date,
"plan_unit_type":
plan_unit_type,
"plan_unit_state":
plan_unit_state,
"plan_unit_intended_use":
plan_unit_intended_use,
"plan_unit_status":
plan_unit_state.to_enum(),
}
# Get or create plan unit
(
plan_unit,
plan_unit_created,
) = PlanUnit.objects.get_or_create(
defaults=rest_data, **match_data)
if not plan_unit_created:
for attr, value in rest_data.items():
setattr(plan_unit, attr, value)
plan_unit.save()
self.stdout.write(
"Lease #{} {}: PlanUnit #{} saved".format(
lease.id, lease.identifier, plan_unit.id))
plan_unit_ids_handled.append(plan_unit.id)
PlanUnit.objects.filter(
lease_area=lease_areas[area_identifier]).exclude(
id__in=plan_unit_ids_handled).delete()
def execute(self): # noqa: C901 'Command.handle' is too complex
from auditlog.registry import auditlog
# Unregister model from auditlog when importing
auditlog.unregister(RelatedLease)
lease_identifier_to_id = {}
with connection.cursor() as django_cursor:
django_cursor.execute("""
SELECT l.id, lt.identifier leasetype, lm.identifier municipality, ld.identifier district, li.sequence
FROM leasing_lease l
JOIN leasing_leaseidentifier li ON l.identifier_id = li.id
JOIN leasing_leasetype lt on li.type_id = lt.id
JOIN leasing_municipality lm on li.municipality_id = lm.id
JOIN leasing_district ld on li.district_id = ld.id
ORDER BY lt.identifier, lm.identifier, ld.identifier, li.sequence
""")
for row in django_cursor.fetchall():
identifier = "{}{}{:02}-{}".format(row[1], row[2], int(row[3]),
row[4])
lease_identifier_to_id[identifier] = row[0]
cursor = self.cursor
query = """
SELECT * FROM (
SELECT ALKUOSA, JUOKSU, LIITTYY_ALKUOSA, LIITTYY_JUOKSU, ROW_NUMBER() OVER (ORDER BY ALKUOSA, JUOKSU) rn
FROM VUOKRAUS
WHERE LIITTYY_ALKUOSA != ALKUOSA OR LIITTYY_JUOKSU != JUOKSU
AND TILA != 'S'
ORDER BY ALKUOSA, JUOKSU
) t
WHERE rn >= {}
""".format(self.offset)
cursor.execute(query)
vuokraus_rows = rows_to_dict_list(cursor)
vuokraus_count = len(vuokraus_rows)
self.stdout.write("{} relations".format(vuokraus_count))
count = 0
found = 0
for lease_row in vuokraus_rows:
count += 1
from_lease = "{}-{}".format(lease_row["LIITTYY_ALKUOSA"],
lease_row["LIITTYY_JUOKSU"])
to_lease = "{}-{}".format(lease_row["ALKUOSA"],
lease_row["JUOKSU"])
try:
from_lease_id = lease_identifier_to_id[from_lease]
except KeyError:
self.stderr.write(
'From lease "{}" not found.'.format(from_lease))
continue
try:
to_lease_id = lease_identifier_to_id[to_lease]
except KeyError:
self.stderr.write('To lease "{}" not found.'.format(to_lease))
continue
self.stdout.write(" {} #{} -> {} #{}".format(
from_lease, from_lease_id, to_lease, to_lease_id))
found += 1
(related_lease,
related_lease_created) = RelatedLease.objects.get_or_create(
from_lease_id=from_lease_id,
to_lease_id=to_lease_id,
type=LeaseRelationType.OTHER,
)
self.stdout.write("{}/{} found".format(found, count))
def setUp(self):
auditlog.unregister(SimpleModel)
self.obj = SimpleModel.objects.create(text='No history')
def handle(self, *args,
**options): # noqa: C901 'Command.handle' is too complex TODO
from auditlog.registry import auditlog
# Unregister all models from auditlog when importing
for model in list(auditlog._registry.keys()):
auditlog.unregister(model)
leases = Lease.objects.all()
for lease in leases:
self.stdout.write('Lease #{} {}:'.format(lease.id,
lease.identifier))
lease_areas = {
normalize_identifier(la.identifier): la
for la in lease.lease_areas.all()
}
self.stdout.write(' Existing lease areas: {}'.format(', '.join(
lease_areas.keys())))
areas = Area.objects.filter(type=AreaType.LEASE_AREA,
identifier=str(lease.identifier))
if not areas:
self.stdout.write(' No lease areas found in area table')
for area in areas:
property_identifier = '{}-{}-{}-{}{}'.format(
area.metadata.get('municipality', '0')
if area.metadata.get('municipality') else '0',
area.metadata.get('district', '0')
if area.metadata.get('district') else '0',
area.metadata.get('group', '0')
if area.metadata.get('group') else '0',
area.metadata.get('unit', '0')
if area.metadata.get('unit') else '0',
'-{}'.format(area.metadata.get('mvj_unit', '0'))
if 'mvj_unit' in area.metadata else '',
)
area_identifier = normalize_identifier(property_identifier)
self.stdout.write(' {} -> {}'.format(property_identifier,
area_identifier))
if area_identifier not in lease_areas.keys():
self.stdout.write(' Lease area NOT FOUND!')
continue
lease_areas[area_identifier].geometry = area.geometry
lease_areas[area_identifier].save()
self.stdout.write(' Lease area FOUND. SAVED.')
try:
other_areas = Area.objects.filter(
geometry__intersects=area.geometry).exclude(type__in=[
AreaType.LEASE_AREA, AreaType.PLOT_DIVISION
]).exclude(geometry__touches=area.geometry)
except InternalError as e:
self.stdout.write(str(e))
continue
for other_area in other_areas:
self.stdout.write(' #{} {} {}'.format(
other_area.id, other_area.identifier, other_area.type))
try:
intersection = other_area.geometry & lease_areas[
area_identifier].geometry
intersection.transform(3879)
except GEOSException as e:
self.stdout.write(str(e))
continue
self.stdout.write(' intersection area {} m^2'.format(
intersection.area))
# Discard too small intersecting areas
if intersection.area < 1:
self.stdout.write(' DISCARD')
continue
if other_area.type == AreaType.REAL_PROPERTY or other_area.type == AreaType.UNSEPARATED_PARCEL:
match_data = {
'lease_area':
lease_areas[area_identifier],
'type':
PlotType[other_area.type.value.upper()],
'identifier':
denormalize_identifier(other_area.identifier),
'in_contract':
False,
}
rest_data = {
'area':
float(other_area.metadata.get('area')),
'section_area':
intersection.area,
'registration_date':
other_area.metadata.get('registration_date'),
'repeal_date':
other_area.metadata.get('repeal_date'),
'geometry':
other_area.geometry,
}
(plot, plot_created) = Plot.objects.update_or_create(
defaults=rest_data, **match_data)
self.stdout.write(' Plot #{} ({}) saved.'.format(
plot.id, plot.type))
elif other_area.type == AreaType.PLAN_UNIT:
# Find the plot division that intersects the most
plot_area = Area.objects.filter(
geometry__intersects=other_area.geometry,
type=AreaType.PLOT_DIVISION
).extra(
select={
'interarea':
'ST_Area(ST_Transform(ST_Intersection(geometry, \'{}\'), 3879))'
.format(other_area.geometry)
}).order_by('-interarea').first()
if plot_area and plot_area.interarea > 0:
(plot_division_state, created
) = PlotDivisionState.objects.get_or_create(
name=plot_area.metadata.get('state_name'))
detailed_plan_area = Area.objects.filter(
type=AreaType.DETAILED_PLAN,
identifier=other_area.metadata.get(
'detailed_plan_identifier')).first()
if detailed_plan_area:
detailed_plan_identifier = detailed_plan_area.identifier
detailed_plan_latest_processing_date = None
else:
detailed_plan_identifier = None
detailed_plan_latest_processing_date = None
(plan_unit_type,
created) = PlanUnitType.objects.get_or_create(
name=other_area.metadata.get('type_name'))
(plan_unit_state,
created) = PlanUnitState.objects.get_or_create(
name=other_area.metadata.get('state_name'))
if other_area.metadata.get('intended_use_name'):
(plan_unit_intended_use, created
) = PlanUnitIntendedUse.objects.get_or_create(
name=other_area.metadata.get(
'intended_use_name'))
else:
plan_unit_intended_use = None
match_data = {
'lease_area':
lease_areas[area_identifier],
'identifier':
denormalize_identifier(other_area.identifier),
'in_contract':
False,
}
rest_data = {
'area':
float(other_area.metadata.get('area')),
'section_area':
intersection.area,
'geometry':
other_area.geometry,
'plot_division_identifier':
plot_area.identifier,
'plot_division_date_of_approval':
plot_area.metadata.get('date_of_approval'),
'plot_division_effective_date':
plot_area.metadata.get('effective_date'),
'plot_division_state':
plot_division_state,
'detailed_plan_identifier':
detailed_plan_identifier,
'detailed_plan_latest_processing_date':
detailed_plan_latest_processing_date,
'plan_unit_type':
plan_unit_type,
'plan_unit_state':
plan_unit_state,
'plan_unit_intended_use':
plan_unit_intended_use,
}
(plan_unit, plan_unit_created
) = PlanUnit.objects.update_or_create(
defaults=rest_data, **match_data)
self.stdout.write(' PlanUnit #{} saved.'.format(
plan_unit.id))
self.stdout.write('')
def handle(self, *args, **options): # noqa
from auditlog.registry import auditlog
# Unregister models from auditlog
auditlog.unregister(Collateral)
wb = load_workbook(filename=options['excel_file'])
sheet = wb.active
max_row = sheet.max_row
for row_num in range(2, max_row):
lease_identifier_cell = sheet.cell(row=row_num,
column=LEASE_IDENTIFIER_COLUMN)
amount_cell = sheet.cell(row=row_num, column=AMOUNT_COLUMN)
paid_date_cell = sheet.cell(row=row_num, column=PAID_DATE_COLUMN)
returned_date_cell = sheet.cell(row=row_num,
column=RETURNED_DATE_COLUMN)
note_cell = sheet.cell(row=row_num, column=NOTE_COLUMN)
if lease_identifier_cell.value is None:
continue
if lease_identifier_cell.value is None or amount_cell.value is None:
amount = Decimal(0)
else:
try:
amount = Decimal(str(amount_cell.value))
except decimal.InvalidOperation:
amount = Decimal(0)
lease_identifier_cell_value = lease_identifier_cell.value.strip()
# Fix typos in excel
if lease_identifier_cell_value.startswith('S120-'):
lease_identifier_cell_value = 'S0' + lease_identifier_cell_value[
1:]
if lease_identifier_cell_value[0:2] == 'So':
lease_identifier_cell_value = 'S0' + lease_identifier_cell_value[
2:]
lease_identifiers = re.findall(r'[A-Z]\d{4}-\d+',
lease_identifier_cell_value,
re.IGNORECASE)
if not lease_identifiers:
continue
paid_date = get_date_from_value(paid_date_cell.value)
if not paid_date:
# Try to find date from the lease identifier column
paid_date = get_date_from_value(lease_identifier_cell.value)
returned_date = get_date_from_value(returned_date_cell.value)
for lease_identifier in lease_identifiers:
try:
lease = Lease.objects.get_by_identifier(
lease_identifier.upper())
except Lease.DoesNotExist:
self.stderr.write(
'Lease "{}" not found'.format(lease_identifier))
continue
lease_contract = lease.contracts.filter(
type=1).first() # 1 = Vuokrasopimus
if not lease_contract:
self.stdout.write(
'Lease "{}" no lease contract found. Creating.'.format(
lease_identifier))
lease_contract = Contract.objects.create(
lease=lease,
type_id=1,
signing_note=
'Vakuuksien tuontia varten luotu tyhjä sopimus')
(collateral,
collateral_created) = Collateral.objects.get_or_create(
contract=lease_contract,
type_id=2, # 2 = Rahavakuus
total_amount=amount,
paid_date=paid_date,
returned_date=returned_date,
note=note_cell.value)
def handle(self, *args, **options): # noqa: C901 TODO
from auditlog.registry import auditlog
logging.basicConfig(level=logging.INFO, format="%(message)s", stream=sys.stdout)
verbosity = options.get("verbosity")
if verbosity == 0:
LOG.setLevel(logging.WARNING)
elif verbosity >= 2:
LOG.setLevel(logging.DEBUG)
# Unregister all models from auditlog when importing
for model in list(auditlog._registry.keys()):
auditlog.unregister(model)
leases = Lease.objects.all()
LOG.info("Processing %s objects.", leases.count())
for lease in leases:
LOG.debug("Lease #%s %s:", lease.id, lease.identifier)
lease_areas = {
la.get_normalized_identifier(): la for la in lease.lease_areas.all()
}
LOG.debug(" Existing lease areas: %s", ", ".join(lease_areas.keys()))
areas = Area.objects.filter(
type=AreaType.LEASE_AREA, identifier=str(lease.identifier)
)
if not areas:
LOG.debug(
"Lease #%s %s: No lease areas found in area table",
lease.id,
lease.identifier,
)
for area in areas:
plot_ids_handled = []
plan_unit_ids_handled = []
area_identifier = area.get_normalized_identifier()
if area_identifier not in lease_areas.keys():
LOG.debug(
"Lease #%s %s: Area id %s not in lease areas of lease!",
lease.id,
lease.identifier,
area_identifier,
)
continue
lease_areas[area_identifier].geometry = area.geometry
lease_areas[area_identifier].save()
try:
# Get intersected areas with lease area's geometry but exclude the type of lease area and plot
# division and also geometries which only touches the lease area
intersected_areas = (
Area.objects.filter(geometry__intersects=area.geometry)
.exclude(type__in=[AreaType.LEASE_AREA, AreaType.PLOT_DIVISION])
.exclude(geometry__touches=area.geometry)
)
except InternalError:
LOG.exception("Failed to get intersected areas")
continue
for intersect_area in intersected_areas:
LOG.debug(
" #%s %s %s",
intersect_area.id,
intersect_area.identifier,
intersect_area.type,
)
# As of 21.1.2020, there are about 250 of plan unit area objects that have {'area': None, ...}
# in their metadata json field. I suspect this is due to accidental duplications in the source db
# since it seems like there is usually another object with identical metadata and identifier fields
# (except also having a value for the 'area' key) to be found, which we want to actually use.
if not intersect_area.metadata.get("area"):
LOG.debug(
"Lease #%s %s: DISCARD area %s: no 'area' value in metadata",
lease.id,
lease.identifier,
intersect_area.id,
)
continue
try:
# Discard too small intersect area
intersection = (
intersect_area.geometry
& lease_areas[area_identifier].geometry
)
intersection.transform(3879)
if intersection.area < 1:
LOG.debug(
"Lease #%s %s: DISCARD area %s: intersection area too small",
lease.id,
lease.identifier,
intersect_area.id,
)
continue
except GEOSException as e:
LOG.exception(
"Discarding too small intersect area failed %s", e
)
continue
if (
intersect_area.type == AreaType.REAL_PROPERTY
or intersect_area.type == AreaType.UNSEPARATED_PARCEL
):
# If the intersect area's type is real property (kiinteistö) or unseparated parcel (määräala)
# then update or create the intersect area to plots
match_data = {
"lease_area": lease_areas[area_identifier],
"type": PlotType[intersect_area.type.value.upper()],
"identifier": intersect_area.get_denormalized_identifier(),
"is_master": True,
}
rest_data = {
"area": float(intersect_area.metadata.get("area")),
"section_area": intersection.area,
"registration_date": intersect_area.metadata.get(
"registration_date"
),
"repeal_date": intersect_area.metadata.get("repeal_date"),
"geometry": intersect_area.geometry,
"master_timestamp": datetime.now(),
}
(plot, plot_created) = Plot.objects.get_or_create(
defaults=rest_data, **match_data
)
if not plot_created:
rest_data.pop("master_timestamp")
for attr, value in rest_data.items():
setattr(plot, attr, value)
plot.save()
plot_ids_handled.append(plot.id)
LOG.debug(
"Lease #%s %s: Plot #%s (%s) saved",
lease.id,
lease.identifier,
plot.id,
plot.type,
)
elif intersect_area.type == AreaType.PLAN_UNIT:
# Find the plot division (tonttijako) that intersects the most
plot_division_area = (
Area.objects.filter(
geometry__intersects=intersect_area.geometry,
type=AreaType.PLOT_DIVISION,
)
.extra(
select={
"interarea": "ST_Area(ST_Transform(ST_Intersection(geometry, '{}'), 3879))".format(
intersect_area.geometry
)
}
)
.order_by("-interarea")
.first()
)
# If the plot division area exist then create/update plan unit informations
if plot_division_area and plot_division_area.interarea > 0:
# Get or create plot division state
(
plot_division_state,
created,
) = PlotDivisionState.objects.get_or_create(
name=plot_division_area.metadata.get("state_name")
)
# Get detailed plan area
detailed_plan_area = Area.objects.filter(
type=AreaType.DETAILED_PLAN,
identifier=intersect_area.metadata.get(
"detailed_plan_identifier"
),
).first()
# The variable is unused for some reason
detailed_plan_latest_processing_date = None
# If detailed plan area exist, set the identifier
detailed_plan_identifier = None
if detailed_plan_area:
detailed_plan_identifier = detailed_plan_area.identifier
# Get or create plan unit type
(
plan_unit_type,
created,
) = PlanUnitType.objects.get_or_create(
name=intersect_area.metadata.get("type_name")
)
# Get or create plan unit state
(
plan_unit_state,
created,
) = PlanUnitState.objects.get_or_create(
name=intersect_area.metadata.get("state_name")
)
# If the intersect area has intended use name, get or create it to plan unit intended use
plan_unit_intended_use = None
if intersect_area.metadata.get("intended_use_name"):
(
plan_unit_intended_use,
created,
) = PlanUnitIntendedUse.objects.get_or_create(
name=intersect_area.metadata.get(
"intended_use_name"
)
)
match_data = {
"lease_area": lease_areas[area_identifier],
"identifier": intersect_area.get_denormalized_identifier(),
"is_master": True,
}
rest_data = {
"area": float(intersect_area.metadata.get("area")),
"section_area": intersection.area,
"geometry": intersect_area.geometry,
"plot_division_identifier": plot_division_area.identifier,
"plot_division_date_of_approval": plot_division_area.metadata.get(
"date_of_approval"
),
"plot_division_effective_date": plot_division_area.metadata.get(
"effective_date"
),
"plot_division_state": plot_division_state,
"detailed_plan_identifier": detailed_plan_identifier,
"detailed_plan_latest_processing_date": detailed_plan_latest_processing_date,
"plan_unit_type": plan_unit_type,
"plan_unit_state": plan_unit_state,
"plan_unit_intended_use": plan_unit_intended_use,
"master_timestamp": datetime.now(),
}
# Set plan unit status
if plan_unit_state.to_enum() is not None:
rest_data[
"plan_unit_status"
] = plan_unit_state.to_enum()
# Get or create plan unit
(
plan_unit,
plan_unit_created,
) = PlanUnit.objects.get_or_create(
defaults=rest_data, **match_data
)
if not plan_unit_created:
rest_data.pop("master_timestamp")
for attr, value in rest_data.items():
setattr(plan_unit, attr, value)
plan_unit.save()
plan_unit_ids_handled.append(plan_unit.id)
LOG.debug(
"Lease #%s %s: PlanUnit #%s saved",
lease.id,
lease.identifier,
plan_unit.id,
)
Plot.objects.filter(lease_area=lease_areas[area_identifier]).exclude(
id__in=plot_ids_handled
).delete()
PlanUnit.objects.filter(
lease_area=lease_areas[area_identifier]
).exclude(id__in=plan_unit_ids_handled).delete()
def setUp(self):
auditlog.unregister(SimpleModel)
self.obj = SimpleModel.objects.create(text='No history')
def execute(self): # noqa: C901 'Command.handle' is too complex
from auditlog.registry import auditlog
# Unregister all models from auditlog when importing
for model in list(auditlog._registry.keys()):
auditlog.unregister(model)
cursor = self.cursor
query = """
SELECT * FROM (
SELECT p.*, ROW_NUMBER() OVER (ORDER BY ALKUPVM) rn
FROM PERUSTE p
ORDER BY ALKUPVM
) t
WHERE rn >= {}
""".format(self.offset)
cursor.execute(query)
peruste_rows = rows_to_dict_list(cursor)
peruste_count = len(peruste_rows)
count = 0
if self.offset:
count = self.offset - 1
peruste_count += self.offset
self.stdout.write('{} basis of rent rows'.format(peruste_count))
for basis_of_rent_row in peruste_rows:
count += 1
self.stdout.write('PERUSTE #{} ({}/{})'.format(
basis_of_rent_row['PERUSTE'], count, peruste_count))
if basis_of_rent_row['KORTTELI'] is None:
self.stdout.write(' KORTTELI missing. Skipping.')
continue
index = None
if basis_of_rent_row['KK'] and basis_of_rent_row['VUOSI']:
try:
index = Index.objects.get(month=basis_of_rent_row['KK'],
year=basis_of_rent_row['VUOSI'])
except Index.DoesNotExist:
self.stdout.write(
'Index VUOSI {} KK {} does not exist!'.format(
basis_of_rent_row['VUOSI'],
basis_of_rent_row['KK']))
notes = []
if basis_of_rent_row['PERUSTETXT']:
notes.append(basis_of_rent_row['PERUSTETXT'].strip())
if basis_of_rent_row['ALENNUSTXT']:
notes.append(basis_of_rent_row['ALENNUSTXT'].strip())
(basis_of_rent, created) = BasisOfRent.objects.get_or_create(
plot_type_id=BASIS_OF_RENT_PLOT_TYPE_MAP[
basis_of_rent_row['TONTTITYYPPI']],
start_date=basis_of_rent_row['ALKUPVM'].date()
if basis_of_rent_row['ALKUPVM'] else None,
end_date=basis_of_rent_row['LOPPUPVM'].date()
if basis_of_rent_row['LOPPUPVM'] else None,
detailed_plan_identifier=basis_of_rent_row['KAAVANO'],
financing_id=FINANCING_MAP[basis_of_rent_row['RAHOITUSMUOTO']]
if basis_of_rent_row['RAHOITUSMUOTO'] else None,
management_id=MANAGEMENT_MAP[
basis_of_rent_row['HALLINTAMUOTO']]
if basis_of_rent_row['HALLINTAMUOTO'] else None,
lease_rights_end_date=basis_of_rent_row['VUOKRAUSOIKEUSPVM'].
date() if basis_of_rent_row['VUOKRAUSOIKEUSPVM'] else None,
index=index,
note='\n\n'.join(notes) if notes else None,
)
property_identifier = get_real_property_identifier(
basis_of_rent_row)
(basis_of_rent_property_identifier,
created) = BasisOfRentPropertyIdentifier.objects.get_or_create(
basis_of_rent=basis_of_rent, identifier=property_identifier)
decision_column_prefixes = ['KLK', 'MUU']
for decision_column_prefix in decision_column_prefixes:
decision_maker_string = basis_of_rent_row['{}_PAATTAJA'.format(
decision_column_prefix)]
decision_datetime = basis_of_rent_row['{}_PAATOSPVM'.format(
decision_column_prefix)]
decision_section_string = basis_of_rent_row['{}_PYKALA'.format(
decision_column_prefix)]
if not decision_maker_string or not decision_datetime:
continue
(
basis_of_rent_decision, created
) = BasisOfRentDecision.objects.get_or_create(
basis_of_rent=basis_of_rent,
reference_number=None, # TODO
decision_maker_id=DECISION_MAKER_MAP[decision_maker_string]
if decision_maker_string else None,
decision_date=decision_datetime.date()
if decision_datetime else None,
section=decision_section_string
if decision_section_string else None,
)
query = """
SELECT *
FROM PERUSTE_HINNAT
WHERE PERUSTE = {}
""".format(basis_of_rent_row['PERUSTE'])
cursor.execute(query)
hinta_rows = rows_to_dict_list(cursor)
for rate_row in hinta_rows:
build_permission_type = BASIS_OF_RENT_BUILD_PERMISSION_MAP[(
rate_row['RAKENNUSOIKEUSTYYPPI'], rate_row['ERITTELY'])]
amount = rate_row['MUU_HINTA_ARVIO']
area_unit = BASIS_OF_RENT_RATE_AREA_UNIT_MAP[
rate_row['MUU_HINTA_YKS']]
if not amount:
amount = rate_row['KLK_HINTA_ARVIO']
area_unit = BASIS_OF_RENT_RATE_AREA_UNIT_MAP[
rate_row['KLK_HINTA_YKS']]
if not amount:
amount = rate_row['HINTA_ARVIO']
area_unit = BASIS_OF_RENT_RATE_AREA_UNIT_MAP[
rate_row['HINTA_YKS']]
(basis_of_rent_rate,
created) = BasisOfRentRate.objects.get_or_create(
basis_of_rent=basis_of_rent,
build_permission_type_id=build_permission_type,
amount=amount,
area_unit=area_unit,
)
def handle(self, *args, **options):
from auditlog.registry import auditlog
auditlog.unregister(InterestRate)
r = requests.get(
'https://www.suomenpankki.fi/WebForms/ReportViewerPage.aspx',
params={
'report':
'/tilastot/markkina-_ja_hallinnolliset_korot/viitekorko_fi',
'output': 'xml',
})
if r.status_code != 200:
raise CommandError('Failed to download interest rates')
root = ElementTree.fromstring(r.content)
for year_element in root.findall('.//{viitekorko_fi}matrix1_year'):
current_year = int(year_element.attrib.get('txtb_year'))
for period_element in year_element.findall(
'.//{viitekorko_fi}matrix1_Period'):
current_period_string = period_element.attrib.get('Period')
period_match = re.match(
r'(?P<start_day>\d+)\.(?P<start_month>\d+)\..(?P<end_day>\d+)\.(?P<end_month>\d+)',
current_period_string)
if not period_match:
continue
start_date = datetime.date(
year=current_year,
month=int(period_match.group('start_month')),
day=int(period_match.group('start_day')))
end_date = datetime.date(
year=current_year,
month=int(period_match.group('end_month')),
day=int(period_match.group('end_day')))
currency_elements = period_element.findall(
'./{viitekorko_fi}col_grp_currency_Collection/{viitekorko_fi}col_grp_currency'
)
rates = {}
for currency_element in currency_elements:
interest_type_string = currency_element.attrib.get(
'txtb_currency')
if interest_type_string not in [
'Korkolain perusteella vahvistettu viitekorko',
'Viivästyskorko, kun velasta ei ole sovittu maksettavaksi korkoa'
]:
continue
cell = currency_element.find('./{viitekorko_fi}Cell')
interest_value_string = cell.attrib.get('txtb_value', '')
if not interest_value_string:
continue
interest_value = Decimal(cell.attrib.get('txtb_value', ''))
rates[interest_type_string] = interest_value
self.stdout.write('{} - {} {}: {}'.format(
start_date, end_date, interest_type_string,
interest_value))
InterestRate.objects.update_or_create(
start_date=start_date,
end_date=end_date,
reference_rate=rates[
'Korkolain perusteella vahvistettu viitekorko'],
penalty_rate=rates[
'Viivästyskorko, kun velasta ei ole sovittu maksettavaksi korkoa']
)
def handle(self, *args, **options):
from auditlog.registry import auditlog
auditlog.unregister(InterestRate)
r = requests.get(
"https://www.suomenpankki.fi/WebForms/ReportViewerPage.aspx",
params={
"report":
"/tilastot/markkina-_ja_hallinnolliset_korot/viitekorko_fi",
"output": "xml",
},
)
if r.status_code != 200:
raise CommandError("Failed to download interest rates")
root = ElementTree.fromstring(r.content)
for year_element in root.findall(".//{viitekorko_fi}matrix1_year"):
current_year = int(year_element.attrib.get("txtb_year"))
for period_element in year_element.findall(
".//{viitekorko_fi}matrix1_Period"):
current_period_string = period_element.attrib.get("Period")
period_match = re.match(
r"(?P<start_day>\d+)\.(?P<start_month>\d+)\..(?P<end_day>\d+)\.(?P<end_month>\d+)",
current_period_string,
)
if not period_match:
continue
start_date = datetime.date(
year=current_year,
month=int(period_match.group("start_month")),
day=int(period_match.group("start_day")),
)
end_date = datetime.date(
year=current_year,
month=int(period_match.group("end_month")),
day=int(period_match.group("end_day")),
)
currency_elements = period_element.findall(
"./{viitekorko_fi}col_grp_currency_Collection/{viitekorko_fi}col_grp_currency"
)
rates = {}
for currency_element in currency_elements:
interest_type_string = currency_element.attrib.get(
"txtb_currency")
if interest_type_string not in [
"Korkolain perusteella vahvistettu viitekorko",
"Viivästyskorko, kun velasta ei ole sovittu maksettavaksi korkoa",
]:
continue
cell = currency_element.find("./{viitekorko_fi}Cell")
interest_value_string = cell.attrib.get("txtb_value", "")
if not interest_value_string:
continue
interest_value = Decimal(cell.attrib.get("txtb_value", ""))
rates[interest_type_string] = interest_value
self.stdout.write("{} - {} {}: {}".format(
start_date, end_date, interest_type_string,
interest_value))
InterestRate.objects.update_or_create(
start_date=start_date,
end_date=end_date,
reference_rate=rates[
"Korkolain perusteella vahvistettu viitekorko"],
penalty_rate=rates[
"Viivästyskorko, kun velasta ei ole sovittu maksettavaksi korkoa"],
)
def importa_contratos():
auditlog.unregister(Contrato)
auditlog.unregister(ContratoUnidade)
auditlog.unregister(DocumentoFiscal)
auditlog.unregister(FiscaisUnidade)
auditlog.unregister(Divisao)
auditlog.unregister(Nucleo)
auditlog.unregister(Unidade)
auditlog.unregister(Coad)
cria_coad()
digecon = cria_digecon()
nucleos = cria_nucleos(digecon)
dotacoes = {}
apaga_contratos()
for index, row in df.iterrows():
tc = row['TC'].strip()
eol_unidade = row['EOL_UNIDADE'].strip()
print(f'Cont:{index} Importando Linha:{row["LINHA"]} TC:{tc}')
empresa_data = {'nome': row['EMPRESA'], 'cnpj': clean_id(row['CNPJ'])}
empresa_contratada = importa_empresa(empresa_data)
servico = de_para_servicos(row["OBJETO"])
tipo_servico = importa_tipo_servico({'nome': servico})
equipamento = de_para_equipamento(row['EQP'])
delta = row['TERMINO'] - row['INICIO']
vigencia_em_dias = delta.days
unidade_data = {
'codigo_eol': eol_unidade,
'cep': row['CEP_UNIDADE'],
'nome': row['NOME_UNIDADE'],
'equipamento': equipamento,
'tipo_unidade': row['EQP']
}
unidade = importa_unidade(unidade_data)
contrato_data = {
'termo_contrato': tc,
'processo': row['PROCESSO'],
'tipo_servico': tipo_servico,
'nucleo_responsavel': nucleos[equipamento],
'empresa_contratada': empresa_contratada,
'data_ordem_inicio': row['INICIO'],
'vigencia_em_dias': vigencia_em_dias,
'situacao': Contrato.SITUACAO_ATIVO,
'estado_contrato': de_para_estado(row['SITUACAO'], ),
'data_encerramento': row['TERMINO']
}
contrato = importa_contrato(contrato_data)
contrato_detalhe_data = {
'contrato': contrato,
'unidade': unidade,
'valor_mensal': row['TOTAL_MENSAL'],
# TODO Rever a gravação de dotações em função de mudanças do modelo após importação inicial.
# 'dotacao_orcamentaria': row['DOTACAO'],
'lote': row['LOTE'],
}
importa_contrato_detalhe(contrato_detalhe_data)
# Grava dotacoes do contrato
if contrato.termo_contrato not in dotacoes:
dotacoes[contrato.termo_contrato] = set()
dotacoes[contrato.termo_contrato].add(row['DOTACAO'])
importa_dres()
grava_dotacoes(dotacoes)
if __name__ == '__main__':
importa_contratos()
