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_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 _data(self, query):
data = super(EnergyMeterExporterView, self)._data(query)
for item in data:
item = list(item)
meter = ElectricMeter.get(item[1])
item[1] = meter.allotment.member.lastname
item[2] = meter.comment or meter.allotment.member.firstname
year = get_selected_year()
value_sec3last_year = meter.get_value(year - 3)
value_sec2last_year = meter.get_value(year - 2)
value_last_year = meter.get_value(year - 1)
value_current_year = meter.get_value(year)
item[-4] = self._get_value(value_sec3last_year)
item[-3] = self._get_value(value_sec2last_year)
item[-2] = self._get_value(value_last_year)
item[-1] = self._get_value(value_current_year)
yield item
item = [0, 'Hauptzähler', 'kWh', 0, 48328153, '', '', '', '']
for i in range(-1, -5, -1):
price = (EnergyPrice.query().filter(EnergyPrice.year == year + i +
1).first())
if price:
item[i] = price.value
yield item
item = [0, 'Endabrechnung', 'microcent', 0, 48328153, '', '', '', '']
for i in range(-1, -5, -1):
price = (EnergyPrice.query().filter(EnergyPrice.year == year + i +
1).first())
if price:
item[i] = price.bill
yield item
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 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 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_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 get_discount_to(electric_meter_id, request=None):
meter = ElectricMeter.get(electric_meter_id)
allotment = meter.allotment
if meter.discount_to:
member = meter.discount_to
else:
member = meter.allotment.member
return '{} ({}, {})'.format(allotment.number, member.lastname,
member.firstname)
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 downgrade():
from sw.allotmentclub import ElectricMeter
import transaction
db = risclog.sqlalchemy.db.get_database()
db._engines['portal'] = dict(
engine=op.get_bind().engine,
alembic_location="sw.allotmentclub:portal",
)
to_delete = (ElectricMeter.query().filter(
ElectricMeter.number == NEW_NUMBER).one())
to_delete.get_value(YEAR).delete()
to_delete.delete()
old = (ElectricMeter.query().filter(
ElectricMeter.number == OLD_NUMBER).one())
old.get_value(YEAR - 1).delete()
transaction.commit()
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 upgrade():
from sw.allotmentclub import ElectricMeter
import transaction
db = risclog.sqlalchemy.db.get_database()
db._engines['portal'] = dict(
engine=op.get_bind().engine,
alembic_location="sw.allotmentclub:portal",
)
old = (ElectricMeter.query().filter(
ElectricMeter.number == OLD_NUMBER).one())
old.replace(old_value=OLD_VALUE,
new_number=NEW_NUMBER,
new_value=NEW_VALUE)
transaction.commit()
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 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 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 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__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
