def test_can_download_and_upload_energy_list(browser):
from sw.allotmentclub import EnergyValue, ElectricMeter
year = datetime.now().year
setUp()
assert [8165, 8411
] == sorted([v.value for v in ElectricMeter.get(4).energy_values])
browser.login()
browser.open('http://localhost/electricity/export')
wb = openpyxl.load_workbook(BytesIO(browser.contents))
sheet = wb.get_active_sheet()
for index, row in enumerate(sheet.rows):
if index in (0, 1):
continue
row[-3].value = row[5].value or 0 + 100
row[-1].value = True
if row[0].value == 4:
row[-1].value = False
to_import = BytesIO()
wb.save(to_import)
to_import.seek(0)
browser._upload(
'http://localhost/electricity/import',
('import.xlsx',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
to_import))
assert 16 == (EnergyValue.query().filter(EnergyValue.year == year).count())
assert 1 == (EnergyValue.query().filter(EnergyValue.year == year).filter(
EnergyValue.estimated_value.is_(True)).count())
assert [100, 8165, 8411
] == sorted([v.value for v in ElectricMeter.get(4).energy_values])
def test__EnergyValue__update_data_5(database):
"""Creates bookings for advanced pay for the next year."""
from sw.allotmentclub import EnergyValue, ElectricMeter, EnergyPrice
from sw.allotmentclub import Booking, BookingKind, BankingAccount
setUp()
BookingKind.create(title='Energieabschlag I')
BookingKind.create(title='Energieabschlag II')
BankingAccount.create(number='3')
EnergyPrice.create(year=2016,
price=3020,
normal_fee=81700,
power_fee=243300)
meter = ElectricMeter.get(1)
value = EnergyValue.create(electric_meter=meter, year=2016, value=7132)
assert Booking.query().filter(Booking.accounting_year == 2017).all() == []
value.update_member()
value.update_usage()
value.update_data()
ap1, ap2 = Booking.query().filter(Booking.accounting_year == 2017).all()
assert ap1.value == ap2.value == -267800
assert ap1.member == ap2.member == value.member
assert ap1.booking_day == datetime.date(2017, 3, 31)
assert ap2.booking_day == datetime.date(2017, 6, 30)
assert ap1.purpose == 'Energieabschlag I für Zähler 318992603'
assert ap2.purpose == 'Energieabschlag II für Zähler 318992603'
def get_to_pay(energy_value_id, request=None):
value = EnergyValue.get(energy_value_id)
member = value.member
allotments = '/'.join(str(a.number) for a in member.allotments)
if not allotments:
allotments = 'n/a'
return '{} ({}, {})'.format(allotments, member.lastname, member.firstname)
def add_data(self, line):
year = datetime.datetime.now().year
if line[1].value == 'Hauptzähler':
EnergyPrice.find_or_create(
year=year).value = line[self.cell_index].value
transaction.savepoint()
return
if line[1].value == 'Endabrechnung':
EnergyPrice.find_or_create(
year=year).bill = line[self.cell_index].value
transaction.savepoint()
return
if line[0].value is None and line[1].value is None:
return
meter = ElectricMeter.by_number(line[4].value)
if meter is None:
meter = ElectricMeter.get(line[0].value)
if line[4].value:
meter.number = line[4].value
if not line[self.cell_index].value:
value = int(line[self.cell_index - 1].value)
estimated = True
else:
value = int(line[self.cell_index].value)
estimated = not line[self.cell_index + 2].value
energy_value = EnergyValue.create(electric_meter=meter,
year=year,
value=value,
estimated_value=estimated)
if energy_value not in meter.energy_values:
meter.energy_values.append(energy_value)
energy_value.update_member()
energy_value.update_usage() # verbrauch berechnen
transaction.savepoint()
def test__EnergyValue__fee_4(database):
"""Fee is 0 no power_fee."""
from sw.allotmentclub import EnergyValue, ElectricMeter, EnergyPrice
from sw.allotmentclub import Allotment, Member
EnergyPrice.create(year=2016, power_fee=12345)
assert 0 == EnergyValue.create(electric_meter=ElectricMeter.create(
allotment=Allotment.create(number='123', member=Member.create())),
year=2016)._fee
def update(self):
self.update_price()
values = (EnergyValue.query().filter(
EnergyValue.discounted.is_(None)).filter(
EnergyValue.year == get_selected_year()).all())
for value in values:
value.update_data()
self.send_report()
def test__EnergyValue__price_2(database):
"""Price is 0 if EnergyPrice has no price."""
from sw.allotmentclub import EnergyValue, EnergyPrice, ElectricMeter
from sw.allotmentclub import Member, Allotment
EnergyPrice.create(year=2016)
assert 0 == EnergyValue.create(electric_meter=ElectricMeter.create(
allotment=Allotment.create(number='123', member=Member.create())),
year=2016)._price
def test__EnergyValue__fee_6(database):
"""Fee is power_fee if electic_power."""
from sw.allotmentclub import EnergyValue, ElectricMeter, EnergyPrice
from sw.allotmentclub import Allotment, Member
EnergyPrice.create(year=2016, normal_fee=1, power_fee=3)
assert 3 == EnergyValue.create(electric_meter=ElectricMeter.create(
allotment=Allotment.create(number='123', member=Member.create()),
electric_power=True),
year=2016)._fee
def test__EnergyValue__fee_2(database):
"""Fee is 0 for disconnected meters."""
from sw.allotmentclub import EnergyValue, ElectricMeter, EnergyPrice
from sw.allotmentclub import Allotment, Member
EnergyPrice.create(year=2016)
assert 0 == EnergyValue.create(electric_meter=ElectricMeter.create(
allotment=Allotment.create(number='123', member=Member.create()),
disconnected=True),
year=2016)._fee
def test__EnergyValue__price_3(database):
"""Price is price * usage."""
from sw.allotmentclub import EnergyValue, ElectricMeter, EnergyPrice
from sw.allotmentclub import Member, Allotment
EnergyPrice.create(year=2016, price=2)
assert 200 == EnergyValue.create(electric_meter=ElectricMeter.create(
allotment=Allotment.create(number='123', member=Member.create())),
year=2016,
usage=100)._price
def test__EnergyValue__update_data_4(database):
"""Advance pay is zero if under THRESHOLD."""
from sw.allotmentclub import EnergyValue, ElectricMeter
from sw.allotmentclub.electricity import THRESHOLD
setUp()
value = (EnergyValue.query().join(ElectricMeter).filter(
EnergyValue.year == 2014).filter(
ElectricMeter.number == '21292097').one())
assert value.price <= THRESHOLD
assert value.usage == 25
assert value.price == 75500
assert value.advance_pay == 0
def test__EnergyValue__update_data_6(database):
"""Creates multiple bookings for multiple meters of the same allotment."""
from sw.allotmentclub import EnergyValue, ElectricMeter, EnergyPrice
from sw.allotmentclub import Booking, BookingKind, BankingAccount
setUp()
BookingKind.create(title='Energieabrechnung')
BookingKind.create(title='Energieabschlag I')
BankingAccount.create(number='3')
EnergyPrice.create(year=2016,
price=3020,
normal_fee=81700,
power_fee=243300)
meter = ElectricMeter.get(1)
value = EnergyValue.create(electric_meter=meter, year=2016, value=7132)
meter2 = ElectricMeter.get(2)
value2 = EnergyValue.create(electric_meter=meter2, year=2016, value=14123)
meter.allotment = meter2.allotment
assert Booking.query().filter(Booking.accounting_year == 2017).all() == []
value.update_member()
value.update_usage()
value.update_data()
value2.update_member()
value2.update_usage()
value2.update_data()
ap1, ap2 = Booking.query().filter(Booking.accounting_year == 2017).all()
assert ap1.value != ap2.value
assert ap1.member == ap2.member == value.member
assert ap1.booking_day == ap2.booking_day == datetime.date(2017, 3, 31)
assert ap1.purpose == 'Energieabschlag I für Zähler 318992603'
assert ap2.purpose == 'Energieabschlag I für Zähler 136426011'
enab1, enab2 = Booking.query().filter(
Booking.accounting_year == 2016).all()
assert enab1.value != enab2.value
assert enab1.member == enab2.member == value.member
assert enab1.booking_day == enab2.booking_day == datetime.date(2016, 8, 31)
assert enab1.purpose == 'Energieabrechnung für Zähler 318992603'
assert enab2.purpose == 'Energieabrechnung für Zähler 136426011'
def __call__(self):
year = datetime.datetime.now().year
# Sonderbehandlung Satellitenanlage
meter = ElectricMeter.get(124)
if meter and not meter.get_value(year):
last = meter.get_last_value()
energy_value = EnergyValue.create(electric_meter=meter,
year=year,
value=last.value + 552)
energy_value.update_member()
energy_value.update_usage() # verbrauch berechnen
if energy_value not in meter.energy_values:
meter.energy_values.append(energy_value)
return super(EnergyMeterImporterView, self).__call__()
def test__EnergyValue__update_data_3(database):
"""Member is updated with owner of meter."""
from sw.allotmentclub import EnergyValue, ElectricMeter, Member, Allotment
mueller = Member.create(lastname='Müller')
meyer = Member.create(lastname='Meyer')
mueller_allotment = Allotment.create(number='123', member=mueller)
meter = ElectricMeter.create(allotment=mueller_allotment)
value = EnergyValue.create(electric_meter=meter, year=2016)
assert value.member is None
value.update_member()
assert value.member is mueller
# Its possible that someone pays the bill explicitely for a meter
meter.discount_to = meyer
value.member = None
value.update_member()
assert value.member is meyer
def test__EnergyValue__update_data_1(database):
"""Leaves EnergyValue as is if already discounted."""
from sw.allotmentclub import EnergyValue
value = EnergyValue.create(value=42,
usage=1,
price=1,
fee=1,
whole_price=1,
to_pay=1,
advance_pay=1,
discounted=True)
value.update_data()
assert value.value == 42
assert value.price == 1
assert value.usage == 1
assert value.fee == 1
assert value.whole_price == 1
assert value.to_pay == 1
assert value.advance_pay == 1
def import_energy_meters():
from sw.allotmentclub import Allotment, ElectricMeter, EnergyValue
from sw.allotmentclub import EnergyPrice
EnergyPrice.find_or_create(year=2014,
value=1342354,
bill=42312300,
price=3020,
normal_fee=81700,
power_fee=243300)
EnergyPrice.find_or_create(year=2015, value=1354334, bill=42134200)
for allot, number, v2013, v2014, power, disc, comment in [
['102', '318992603', '6893', '6893', '', '', ''],
['104', '136426011', '10019', '10634', 'X', '', ''],
['106', '21785640', '23154', '24207', '', '', ''],
['108', '81112116', '8165', '8411', '', '', ''],
['110', '31850195', '65811', '66345', '', '', ''],
['112', '20232757', '56221', '56371', '', '', ''],
['114', '364754', '7361', '7407', 'X', '', ''],
['118', '21292097', '935', '960', '', '', ''],
['122', '0063487695', '7028', '7988', 'X', '', ''],
['124', '4270447', '7671', '8033', '', '', ''],
['203', '21181284', '5933', '5997', '', '', ''],
['203', '3295328', '1307', '1349', '', '', 'Satanlage'],
['249', '20868068', '12115', '12115', '', '', ''],
['251', '20236014', '17339', '17352', '', '', 'Wasserpumpe'],
['328', '409120', '5075', '5075', '', '', ''],
['405', '8056675', '66018', '66098', '', '', '']
]:
allotment = Allotment.query().filter(Allotment.number == allot).one()
meter = ElectricMeter.create(allotment=allotment,
number=number,
electric_power=bool(power),
disconnected=bool(disc),
comment=comment)
for year, value in [(2013, v2013), (2014, v2014)]:
value = EnergyValue.create(electric_meter=meter,
year=year,
value=int(value))
value.update_member()
value.update_usage()
value.update_data()
meter.energy_values.append(value)
transaction.savepoint()
def update_price(self):
org_id = self.request.user.organization_id
current_year = get_selected_year()
price = (EnergyPrice.query().filter(
EnergyValue.organization_id == org_id).filter(
EnergyPrice.year == current_year).one())
price.usage_hauptzaehler = price.value - (EnergyPrice.query().filter(
EnergyValue.organization_id == org_id).filter(
EnergyPrice.year == current_year - 1).one().value)
price.usage_members = sum(e.usage for e in (EnergyValue.query().filter(
EnergyValue.year == current_year).filter(
EnergyValue.organization_id == org_id)))
price.leakage_current = price.usage_hauptzaehler - price.usage_members
price.price = price.bill / price.usage_hauptzaehler
self.phases = sum(3 if e.electric_power else 1
for e in (ElectricMeter.query().filter(
ElectricMeter.disconnected.is_(False)).filter(
ElectricMeter.organization_id == org_id)))
price.normal_fee = price.leakage_current * price.price / self.phases
price.power_fee = price.normal_fee * 3
def test__EnergyValue__update_data_2(database):
"""Updates bill data of EnergyValue."""
from sw.allotmentclub import EnergyValue, ElectricMeter, EnergyPrice
setUp()
EnergyPrice.create(year=2016,
price=3020,
normal_fee=81700,
power_fee=243300)
meter = ElectricMeter.get(1)
value = EnergyValue.create(electric_meter=meter, year=2016, value=7132)
assert value.price is None
value.update_member()
value.update_usage()
value.update_data()
assert 239 == value.usage
assert 721780 == value.price
assert 81700 == value.fee
assert value.whole_price == value.fee + value.price
assert value.whole_price == value.to_pay
assert 267826 == value.advance_pay
def test__EnergyValue__price_1(database):
"""Price is 0 if no EnergyPrice is defined for the year."""
from sw.allotmentclub import EnergyValue
assert 0 == EnergyValue.create(year=2016)._price
def test__EnergyValue__fee_1(database):
"""Fee is 0 if no energy price."""
from sw.allotmentclub import EnergyValue
assert 0 == EnergyValue.create(year=2016)._fee
def test_value_has_a_member(database):
from sw.allotmentclub import EnergyValue, Member
setUp()
value = EnergyValue.get(1)
assert Member.get(1) == value.member
def test__EnergyValue__usage_1(database):
"""Usage is 0 if value is None."""
from sw.allotmentclub import EnergyValue, ElectricMeter
assert 0 == EnergyValue.create(electric_meter=ElectricMeter.create(),
year=2016)._usage