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__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 _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__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_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 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__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 send_report(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())
data = json.loads(json.dumps(price))
data['phases'] = self.phases
data['price'] = format_eur(data['price'])
data['bill'] = format_eur(data['bill'])
data['normal_fee'] = format_eur(data['normal_fee'])
data['power_fee'] = format_eur(data['power_fee'])
subject = "Bericht Energieabrechnung {year}".format(**data)
body = """
<p>Sehr geehrter Vorstand,</p>
<p>anbei die Kennzahlen zur diesjährigen Energieabrechnung:</p>
<table>
<tr><td>Verbrauch Hauptzähler: </td><td>{usage_hauptzaehler} kWh</td></tr>
<tr><td>Verbrauch der Mitglieder (inkl. SAT-Anlage und
Vereinsgebäude): </td><td>{usage_members} kWh</td></tr>
<tr><td colspan=2></td></tr>
<tr><td>Verluststrom: </td><td>{usage_hauptzaehler} kWh - {usage_members} kWh = {leakage_current} kWh</td></tr>
<tr><td colspan=2></td></tr>
<tr><td>Preis pro kWh: </td><td>{bill} / {usage_hauptzaehler} kWh = {price}/kWh</td></tr>
<tr><td colspan=2></td></tr>
<tr><td>Grundgebühr 1 Phase ({phases} insgesamt): </td><td>{leakage_current} kWh * {price}/kWh / {phases} = {normal_fee}</td></tr>
<tr><td>Grundgebühr einfacher Zähler (1 Phase): </td><td>{normal_fee}</td></tr>
<tr><td>Grundgebühr Kraftstromzähler (3 Phase): </td><td>{power_fee}</td></tr>
</table><br /><br />""".format(**data) # noqa
send_mail("*****@*****.**", subject, body,
User.by_username('system'))