def save(self, key, value): year = get_selected_year() if key == 'buyer_id': buyer = Member.get(value) for allotment in self.context.allotments: allotment.member = buyer SaleHistory.create(seller_id=self.context.id, buyer_id=buyer.id) self.context.leaving_year = year Booking.create(banking_account_id=2, booking_day=datetime.date.today(), purpose='Aufnahmebeitrag', value=-250000, member_id=buyer.id, kind_id=13, accounting_year=year) else: sale = (SaleHistory.query().filter( SaleHistory.date == datetime.date.today()).filter( SaleHistory.seller_id == self.context.id)).one() buyer = Member.get(sale.buyer_id) query = dict( transfer_member_due='Mitgliedsbeitrag {}'.format(year), transfer_advance_pay_one='Energieabschlag I', transfer_advance_pay_two='Energieabschlag II', transfer_energy_bill='Energieabrechnung', transfer_assignment_due='Arbeitsstunden {}'.format(year))[key] for booking in (Booking.query().filter( Booking.accounting_year == year).filter( Booking.purpose.ilike('%{}%'.format(query))).filter( Booking.member == self.context)).all(): booking.member = buyer return True
def test__mail__MailAssignmentPreview_1(browser): """It can send Fehlende Arbeitsstunden.""" from sw.allotmentclub import Message, User, Member, Assignment from sw.allotmentclub import AssignmentAttendee from sw.allotmentclub.conftest import import_members import_members() assignment = Assignment.find_or_create( day=datetime.now(), accounting_year=datetime.now().year ) AssignmentAttendee.find_or_create( assignment=assignment, member=Member.query().filter(Member.lastname == 'Wehrmann').one(), hours=5) # No letter AssignmentAttendee.find_or_create( assignment=assignment, member=Member.query().filter(Member.lastname == 'Hennig').one(), hours=3) # Needs to pay less verein = Member.find_or_create(lastname="Verein") Message.create(id=245, members=[verein], user=User.create(), accounting_year=2018, subject="Fehlende Arbeitsstunden", body=MISSING_ASSIGMENT_BODY) transaction.commit() browser.login() with mock.patch('sw.allotmentclub.browser.letter.datetime') as dt: dt.now.return_value = datetime(2016, 3, 25) browser.open('http://localhost/mail/245/preview') assertFileEqual(browser.contents, 'test_mail_fehlarbeitsstunden_1.pdf')
def test_SEPASammlerExportView_2(browser): """Testing SEPASammler Energieabrechnung Sammelüberweisung XML export.""" from sw.allotmentclub import SEPASammler, SEPASammlerEntry, BookingKind from sw.allotmentclub import Member import datetime import lxml.etree kind = BookingKind.find_or_create( title='Energieabrechnung', shorttitle='ENAB') sammler = SEPASammler.create( booking_day='2018-03-31', accounting_year=2018, kind=kind, is_ueberweisung=True) for iban, value in (('DE12500105170648489890', 2540500), ('EE342200221034126658', 8213400)): SEPASammlerEntry.find_or_create( sepasammler=sammler, value=value, member=Member.create( lastname='Müller', direct_debit=True, iban=iban, bic='NOLADE21HAL', direct_debit_date=datetime.date(2017, 1, 1))) setUp() browser.login() browser.open( 'http://localhost/accounts/sepa_sammler/{}/export'.format(sammler.id)) doc = lxml.etree.fromstring(browser.contents.encode('utf-8')) assert '1075.39' == doc.find('.//CtrlSum', namespaces=doc.nsmap).text assert '2' == doc.find('.//NbOfTxs', namespaces=doc.nsmap).text assert ['254.05', '821.34'] == [ d.text for d in doc.findall('.//InstdAmt', namespaces=doc.nsmap) ]
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_SEPASammlerExportView_1(browser): """Testing SEPASammler Energieabschlag 1 XML export.""" from sw.allotmentclub import SEPASammler, SEPASammlerEntry, BookingKind from sw.allotmentclub import Member import datetime import lxml.etree kind = BookingKind.find_or_create( title='Energieabschlag I', shorttitle='ENA1') sammler = SEPASammler.create( booking_day='2018-03-31', accounting_year=2018, kind=kind) for iban, value in (('DE12500105170648489890', 2540500), ('EE342200221034126658', 8213400)): SEPASammlerEntry.find_or_create( sepasammler=sammler, value=value, member=Member.create( lastname='Müller', direct_debit=True, iban=iban, bic='NOLADE21HAL', direct_debit_date=datetime.date(2017, 1, 1))) setUp() browser.login() browser.open( 'http://localhost/accounts/sepa_sammler/{}/export'.format(sammler.id)) doc = lxml.etree.fromstring(browser.contents.encode('utf-8')) assert '1075.39' == doc.find('.//CtrlSum', namespaces=doc.nsmap).text assert 'Mueller, ' == doc.findall('.//Nm', namespaces=doc.nsmap)[-1].text assert 'MUELLER' == doc.find('.//MndtId', namespaces=doc.nsmap).text
def get_balance(value, request=None): from sqlalchemy.sql.expression import true, false, case import zope.component import risclog.sqlalchemy member = Member.get(value) db = zope.component.getUtility(risclog.sqlalchemy.interfaces.IDatabase) bookings = (db.query(func.sum( Booking.value)).join(Member).filter(Booking.member == member).filter( Booking.is_splitted == false()).filter( Booking.booking_day <= get_booking_future()).filter( Booking.accounting_year == get_selected_year())) sepa = (db.query( func.sum( case( [(SEPASammler.is_ueberweisung == true(), 0 - SEPASammlerEntry.value)], else_=SEPASammlerEntry.value, ).label('Betrag'), )).join(Member).join(SEPASammler).filter( SEPASammlerEntry.member == member).filter( SEPASammler.booking_day <= get_booking_future()).filter( SEPASammler.accounting_year == get_selected_year())) booking_sum = bookings.one()[0] sepa_sum = sepa.one()[0] booking_sum = 0 if booking_sum is None else booking_sum sepa_sum = 0 if sepa_sum is None else sepa_sum return format_eur_with_color(booking_sum + sepa_sum)
def add_transaction(data, account): from sw.allotmentclub import Member accounting_year = data['date'].year org_title = None org = Organization.get(account.organization_id) if org: org_title = org.title if data['currency'] != 'EUR': raise ValueError( "Couldn't import booking {} because its not in Eur.".format( data['purpose'])) value = int(data['amount'].amount * 10000) sammlers = get_sepa_sammlers(data, account) booking = Booking.find_or_create( organization_id=account.organization_id, banking_account=account, booking_day=data['date'], purpose=data['purpose'], recipient=(data['applicant_name'] if data['applicant_name'] is not None else org_title), value=value) if booking.id: return booking.accounting_year = accounting_year booking.booking_text = data['posting_text'] booking.iban = data['applicant_iban'] booking.bic = data['applicant_bin'] if booking.iban: try: booking.member = (Member.query().filter( Member.iban == booking.iban).filter( Member.organization_id == booking.organization_id).one()) except Exception: pass if booking.member: try: debit_booking = (Booking.query().filter( Booking.value == 0 - booking.value).filter(Booking.member == booking.member).filter( Booking.accounting_year == booking.accounting_year).filter( Booking.organization_id == booking.organization_id).one()) except Exception: pass else: booking.kind = debit_booking.kind if booking.booking_text == 'SAMMEL-LS-EINZUG': if not booking.recipient: booking.recipient = org_title sum_ = 0 for sammler in sammlers: sum_ += sammler.value if (booking.value - 100) > sum_: imported, open_ = booking.split(sum_) imported.booking_text = imported.booking_text + ' Verrechnet' open_.organization_id = booking.organization_id imported.organization_id = booking.organization_id sum_ += sammler.value return 1
def test_imported_bookings_are_auto_assigned(database, member): from sw.allotmentclub import Member member = Member.query().one() member.iban = 'DE79800537624440000377' member.bic = 'NOLADE21HAL' import_bookings() booking = _getOne() assert 'Mittag' == booking.member.lastname
def setUp(): from sw.allotmentclub import Member, Message, User verein = Member.create(lastname="Verein") mustermann = Member.create(lastname="Mustermann", firstname="Max") user = User.create(username='******') greeting = ('Sehr geehrte{{deflection}} {{appellation}} ' '{{title}} {{lastname}},\n\n') Message.create(id=242, members=[verein], user=user, accounting_year=2015, subject="Info-Brief", body=greeting+"**Info** an alle Mitglieder") Message.create(id=243, members=[mustermann], user=user, accounting_year=2015, subject="Willkommen", body=greeting+"Willkommen im Verein.") Message.create(id=244, members=[mustermann], user=user, accounting_year=2016, subject="Beitragsabrechnung", body=greeting) transaction.commit()
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__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__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_members(): parser = gocept.logging.ArgumentParser( description="Import members from excel file.") parser.add_argument('-c', '--config', default='portal.ini', help='Specify the config file. (default: portal.ini)') parser.add_argument('-i', '--input', default='members.xslx', help='Specify the input file. (default: members.xslx)') options = parser.parse_args() app = Application.from_filename(options.config) registry = pyramid.registry.Registry( bases=(zope.component.getGlobalSiteManager(), )) config = pyramid.config.Configurator(settings=app.settings, registry=registry) config.setup_registry(settings=app.settings) request = pyramid.testing.DummyRequest(_registry=registry) request.client_addr = '127.0.0.1' context = pyramid.threadlocal.manager.get().copy() context['request'] = request context['registry'] = registry pyramid.threadlocal.manager.push(context) with open(options.input, 'r') as file: reader = csv.reader(file, delimiter=';') for id, line in enumerate(reader): if id == 0: continue Member.find_or_create(title='', appellation='Herr', organization_id=2, lastname=line[0], firstname=line[1], street=line[2], zip=line[3], city=line[4], country='Deutschland', phone=line[5], mobile=line[6], email=line[7], birthday=line[8]) transaction.commit()
def setUp(): from sw.allotmentclub import Member, Message, User, SentMessageInfo mustermann = Member.create(lastname="Mustermann", firstname="Max", email='*****@*****.**') user = User.create(username='******') msg = Message.create(id=242, members=[mustermann], user=user, accounting_year=2016, subject="Test", body="") SentMessageInfo.create(message=msg, tag='*****@*****.**', address='*****@*****.**') transaction.commit()
def test_member_active_passive(database): from sw.allotmentclub import Member, Allotment member = Member.create() assert member.active is False Allotment.create(member=member) assert member.active is True
def setUp(): from sw.allotmentclub import Assignment, AssignmentAttendee, Member member = Member.create(firstname='Gerd', lastname='Mittag') ass = Assignment.create(day=datetime.datetime(2015, 3, 7, 9), purpose='Frühjahrsputz', responsible=member, accounting_year=2015) AssignmentAttendee.create(assignment=ass, member=member, hours=4) transaction.commit() return member, ass
def member(request, database): """Fixture creating a member.""" def delete_member(): Member.query().filter(Member.lastname == 'Mittag').one().delete() database.session.flush() member = Member.create(firstname='Gerd', lastname='Mittag') database.session.flush() request.addfinalizer(delete_member) return member
def test__mail__MailPreviewView_1(browser, setUp): """It generates pdfs for recipients regardless of email.""" from sw.allotmentclub import Message, Member message = Message.get(243) message.members.append(Member.create( email='*****@*****.**', street='Musterstrasse')) transaction.commit() browser.login() with mock.patch('sw.allotmentclub.browser.letter.datetime') as dt: dt.now.return_value = datetime(2016, 3, 25) browser.open('http://localhost/mail/243/preview') assertFileEqual(browser.contents, 'test_mail_preview_1.pdf')
def recipients(self): recipients = self.context.members + self.context.externals organization_id = self.request.user.organization_id if not recipients: return [None] members = self.context.members if members and members[0].lastname == 'Verein': return (Member.query().filter( Member.leaving_year.is_(None)).filter( Member.organization_id == organization_id).filter( Member.get_post.is_(True)).all()) return recipients
def setUpSEPASammlerUpdate(kind_shorttitle): from sw.allotmentclub import ( SEPASammler, Booking, BookingKind, BankingAccount, Member) kind = BookingKind.find_or_create(title='', shorttitle=kind_shorttitle) sammler = SEPASammler.create( booking_day='2016-06-30', accounting_year=2016, kind=kind) for value in [-2500, -821300]: Booking.find_or_create( value=value, banking_account=BankingAccount.find_or_create(), accounting_year=2016, kind=kind, member=Member.create(direct_debit=True)) setUp() return sammler
def test__mail__MailElectricityPreview_1(browser): """It can send Energieabrechnungen.""" from sw.allotmentclub import Message, User, Member from sw.allotmentclub.conftest import import_energy_meters, import_members import_members() import_energy_meters() verein = Member.find_or_create(lastname="Verein") Message.create(id=245, members=[verein], user=User.create(), accounting_year=2014, subject="Energieabrechnung", body=ENERGIEABRECHNUNG_BODY) transaction.commit() browser.login() with mock.patch('sw.allotmentclub.browser.letter.datetime') as dt: dt.now.return_value = datetime(2016, 3, 25) browser.open('http://localhost/mail/245/preview') assertFileEqual(browser.contents, 'test_mail_energieabrechnung_1.pdf')
def test_BankingAccountListReportView_1(browser): from sw.allotmentclub import Booking, BookingKind, Member from ...conftest import assertFileEqual import transaction kind = BookingKind.find_or_create( title='Energieabschlag I', shorttitle='ENA1') BookingKind.find_or_create( title='Energieabschlag II', shorttitle='ENA2') member = Member.find_or_create(lastname='Wehrmann', firstname='Sebastian') setUp() for b in Booking.query(): b.kind = kind b.member = member transaction.commit() browser.login() browser.open('http://localhost/accounts/report.pdf?for_year=2015') assertFileEqual(browser.contents, 'test_account_report_1.pdf')
def test__mail__MailSentView_1(browser, setUp, mailer): """It does not sent mail if already sent.""" from sw.allotmentclub import Message, Member assert 0 == len(mailer.outbox) message = Message.get(243) message.members.append(Member.create( email='*****@*****.**', street='Musterstrasse')) transaction.commit() browser.login() browser.open('http://localhost/mail/243/send') assert browser.json['message'] == '1 E-Mail(s) erfolgreich versendet' assert 1 == len(mailer.outbox) assert 'Willkommen' in mailer.outbox[0].subject browser.open('http://localhost/mail/243/send') assert browser.json['message'] == 'Keine E-Mail versendet' assert 1 == len(mailer.outbox) assert browser.json['redirect'] == '/api/mail/243/download'
class MemberAssignmentQuery(sw.allotmentclub.browser.base.Query): formatters = { 'Stunden': lambda mid, request: Member.get(mid).assignment_hours, } data_class = {'Mitglied': 'expand'} data_hide = { 'Stunden': 'phone', } def select(self): return (self.db.query( Member.id.label('#'), (to_string(Member.lastname).concat(', ').concat( to_string(Member.firstname).concat(' (').concat( func.coalesce(string_agg(Allotment.number), 'n/a')).concat(')'))).label('Mitglied'), Member.id.label('Stunden'), ).select_from(Allotment).join( Member, Allotment.member_id == Member.id).group_by(Member.id))
def __call__(self): data = self.request.json sender = data['FromFull']['Email'].lower() if sender == '*****@*****.**': return Response('ok') if data['Subject'].startswith('***** SPAM'): return Response('ok') message = Message.create(inbound=True, organization_id=self.organization_id, user=User.by_username('system')) member = Member.query().filter(Member.email == sender).first() if member: message.members.append(member) message.organization_id = member.organization_id else: external = (ExternalRecipient.query().filter( ExternalRecipient.email == sender).first()) if not external: external = ExternalRecipient.create(email=sender) external.lastname = data['FromFull']['Name'] external.organization_id = self.organization_id message.externals.append(external) message.organization_id = external.organization_id message.subject = data['Subject'] message.body = (data['HtmlBody'] if data['HtmlBody'] else data['TextBody']) message.sent = self.sent_date(data) message.accounting_year = message.sent.year for attachment in data['Attachments']: Attachment.create(message=message, organization_id=message.organization_id, filename=attachment["Name"], mimetype=attachment["ContentType"], size=attachment["ContentLength"], data=base64.b64decode(attachment["Content"])) return Response('ok')
def import_members(max=99999): from sw.allotmentclub import Member, Allotment, Parcel for line in [ [ '60', '102', '', 'Frau', 'Ines', 'Groß', 'Mittelweg 7', '01458', 'Ottendorf-Okrilla', '', '', '' ], [ '62', '104', '', 'Herr', 'Reiner', 'Pfeil', 'Schillerstr. 42', '06247', 'Bad Lauchstädt', '', '034635 32731', '' ], [ '64', '106', '', 'Frau', 'Astrid', 'Ritter', 'Brandenburgische Str. 27', '15366', 'Hönow', '', '', '' ], [ '67', '108', '', 'Herr', 'Sebastian', 'Wehrmann', 'Geseniusstr. 34', '06110', 'Halle', '', '', '0172 1096832' ], [ '70', '110', '', 'Herr', 'Johannes', 'Hennig', 'A.-Einstein-' 'Str. 15', '06237', 'Leuna', '', '03461 502682', '' ], [ '74', '112', '', 'Frau', 'Regina', 'Esser', 'Ringstr. 42', '06886', 'Wittenberg', '', '03491 662813', '' ], [ '76', '114', 'Dr.', 'Frau', 'Brigitte', 'Helbig', 'Wolfgang-' 'Heinze-Str. 20', '04277', 'Leipzig', '', '0341 3520609', '' ], [ '83', '118', '', 'Frau', 'Margit ', 'Götze', 'Heinrich-Heine' '-Str. 19', '06237', 'Leuna', '', '03461 811244', '' ], [ '92/93', '122/ 225', '', 'Herr', 'Claus', 'Masthoff', 'Paul-' 'Thiersch-Str. 16', '06124', 'Halle', '', '0345 6876407', '' ], [ '94/50', '124', '', 'Frau', 'Britta', 'Grimmling', 'Fors 190', '87391', 'Bollstabruk', 'Schweden', '', '0157 84943178' ], [ '150', '249/251', '', 'Herr', 'Lutz', 'Rösler', 'Cloppenburger Str. 12', '06126', 'Halle', '', '', '' ], [ '100/137', '405/406', '', 'Frau', 'Marlies', 'Leutloff', 'Wei' 'ßenfelser Str. 11c', '06231', 'Bad Dürrenberg', '', '', '' ], [ '', '', '', 'Herr', 'Günter', 'Tillack', 'Harry-S.-Truman-' 'Allee 4', '14167', 'Berlin', '', '', '0162 9541236' ], [ '153', '328', '', '', '', 'Leuna Bungalowgemeinschaft Roter ' 'See e.V.', 'Postfach 1106', '06773', 'Gräfenhainichen', '', '', '' ], ['58', '203', '', '', '', 'Werkstatt', '', '', '', '', '', ''] ]: if '/' in line[0] or '/' in line[1]: lines = [line[:], line[:]] else: lines = [line] if '/' in line[0]: p1, p2 = line[0].split('/') lines[0][0] = p1.strip() lines[1][0] = p2.strip() if '/' in line[1]: p1, p2 = line[1].split('/') lines[0][1] = p1.strip() lines[1][1] = p2.strip() for line in lines: member = Member.find_or_create(firstname=line[4], lastname=line[5]) member.title = line[2] member.appellation = line[3] member.street = line[6] member.zip = line[7] member.city = line[8] member.country = line[9] or 'Deutschland' member.phone = line[10] member.mobile = line[11] if line[1].strip(): allotment = Allotment.find_or_create(number=int(line[1]), member=member) if line[0].strip(): Parcel.find_or_create(number=int(line[0]), allotment=allotment) transaction.savepoint()
def delete_member(): Member.query().filter(Member.lastname == 'Mittag').one().delete() database.session.flush()
def export_members_vcf(): parser = gocept.logging.ArgumentParser( description="Export members in vcf format.") parser.add_argument('-c', '--config', default='portal.ini', help='Specify the config file. (default: portal.ini)') options = parser.parse_args() Application.from_filename(options.config) for organization in Organization.query().all(): output = "" for m in (Member.query().filter( Member.organization_id == organization.id).filter( Member.leaving_year.is_(None))): output += """BEGIN:VCARD VERSION:3.0 PRODID:-//Sebastian Wehrmann//sw.allotmentclub//DE N:{lastname};{firstname};;; FN:{firstname} {lastname} ADR;type=HOME;type=pref:;;{street};{city};;{zip};{country}""".format( lastname=m.lastname, firstname=m.firstname, street=m.street, city=m.city, zip=m.zip, country=m.country) if m.email: output += """ EMAIL;type=INTERNET;type=HOME;type=pref:{}""".format(m.email) if m.mobile: output += """ TEL;type=CELL;type=VOICE;type=pref:{}""".format(m.mobile) if m.phone: output += """ TEL;type=HOME;type=VOICE:{}""".format(m.phone) if m.birthday: output += """ BDAY:{}""".format(m.birthday.strftime('%Y%m%d')) if m.allotments: output += """ NOTE:Bungalow: {}""".format('/'.join(str(a.number) for a in m.allotments)) elif m.passive_allotment: output += """ NOTE:Bungalow: {}""".format('/'.join( str(a.number) for a in m.passive_allotment)) output += """ ORG:{org} REV:{rev}""".format(org=organization.title, rev=datetime.datetime.now().isoformat()) output += """ UID:{uid} X-RADICALE-NAME:{uid}.vcf END:VCARD """.format(uid=m.id) path = os.path.join(os.path.expanduser("~"), '.config/radicale/collections', VCF_FILES[organization.id]) if not os.path.exists(path): os.makedirs(path) with open(os.path.join(path, 'addressbook.vcf'), 'w') as f: f.write(output)