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