class Subject(ObjectFieldGroupBase): public_interface = ( StringField('name'), StringField('select_vestry_summary'), StringField('easter_vestry_summary'), )
class Transaction(ObjectFieldGroupBase): public_interface = ( IntField('reference_no'), StringField('public_code'), IntField('year'), IntField('month'), IntField('day'), ObjectReferenceField('counterparty'), PaymentMethodField('payment_method'), DescriptionField('description'), DecimalField('amount'), ObjectReferenceField('subject'), IncomeExpenditureField('income_expenditure'), StringField('FY'), # TODO clean up old data and make this an int field ObjectReferenceField('fund'), DescriptionField('comments')) def __str__(self): return '{0.__class__.__name__}({0._reference_no}, {0._public_code}, {0.book_date}, {0._counterparty})'.format( self) @property def book_date(self): if all((self._year, self._month, self._day)): return date(self._year, self._month, self._day) else: None
def funds_from_gsheet(session, extract_from_detailed_ledger): fund_gsheet = get_gsheet_fields( Fund, { 'name': 'fund', 'restriction': 'type', 'is parish fund': 'parish fund', 'is realised': 'realised', 'account': 'bank account id' }) fund_gsheet['restriction'] = StringField('restriction') fund_gsheet['parish fund'] = StringField('parish fund') fund_gsheet['realised'] = StringField('realised') fund_gsheet['bank account id'] = StringField('bank account id') field_casts = { 'type': conform_fund_restriction, 'parish fund': conform_yes_no, 'realised': conform_yes_no, 'bank account id': AccountQuery(session).instance_finder('reference_no', int) } fund_mapping = Mapping(fund_gsheet, Fund.constructor_parameters, field_casts=field_casts) funds = extract_from_detailed_ledger( 'funds', 'A11', ('fund', 'type', 'parish fund', 'realised', 'bank account id')) load_class(session, funds, fund_mapping, Fund)
def nominal_accounts_from_gsheet(session, extract_from_detailed_ledger): nominal_account_gsheet = get_gsheet_fields( NominalAccount, { 'code': 'Code', 'description': 'Description', 'SOFA heading': 'SOFA heading', 'category': 'Category', 'sub category': 'Sub-category', }) nominal_account_gsheet['SOFA heading'] = StringField('SOFA heading') nominal_account_gsheet['Category'] = StringField('Category') nominal_account_gsheet['Sub-category'] = StringField('Sub-category') field_casts = { 'SOFA heading': conform_sofa_heading, 'Category': conform_category, 'Sub-category': conform_sub_category, } nominal_account_mapping = Mapping(nominal_account_gsheet, NominalAccount.constructor_parameters, field_casts=field_casts) nominal_accounts = extract_from_detailed_ledger( 'RCB Nominal Accounts', 'A1', ('Code', 'Description', 'SOFA heading', 'Category', 'Sub-category')) load_class(session, nominal_accounts, nominal_account_mapping, NominalAccount)
class NominalAccount(ObjectFieldGroupBase): public_interface = ( StringField('code', is_mutable=False), StringField('description'), NominalAccountSOFAHeadingField('SOFA_heading'), NominalAccountCategoryField('category'), NominalAccountSubCategoryField('sub_category'), )
class AClass(ObjectFieldGroupBase): public_interface = ( IntField('ref_no'), StringField('name'), BooleanField('is_running'), AClassStatusField('status'), DateField('date'), )
class Address(ObjectFieldGroupBase): # Data usage # # 1. delivering messages by postal system # 2. arranging house visits # public_interface = ( StringField('address1', required=True), StringField('address2'), StringField('address3'), StringField('county'), StringField('countryISO', required=True), StringField('eircode'), StringField('telephone'), ) def post_label(self, addressees=None): address_1 = self.address1 address_2 = self.address2 if address_1 and address_2 and len( address_1) <= 3: # it's most likely a house number address_2 = f"{address_1}, {address_2}" address_1 = None label_fields = filter( lambda a: a, # drop None and empty strings (addressees, address_1, address_2, self.address3, self.county, self.eircode, COUNTRY_ISO_LOOKUP.get(self.countryISO))) return ",\n".join(label_fields)
def __init__(self, item_instance_class, account_collection): self._item_instance_class = item_instance_class self._account_collection = account_collection csv_field_account = StringField('account') csv_field_date = StringField('date') csv_field_details = StringField('details') csv_field_currency = StringField('currency') csv_field_debit = StringField('debit') csv_field_credit = StringField('credit') csv_field_balance = StringField('balance') csv_field_detail_override = ComputedStringField( 'detail_override', lambda fg, i: None) csv_field_designated_balance = ComputedStringField( 'designated_balance', lambda fg, i: StatementItemDesignatedBalance.No) statement_item_csv_fields = ListFieldGroup( (csv_field_account, UnusedField('_unused_'), csv_field_date, UnusedField('_unused_'), csv_field_details, csv_field_currency, csv_field_debit, csv_field_credit, csv_field_balance, csv_field_detail_override, csv_field_designated_balance)) field_mappings = tuple( zip((csv_field_account, csv_field_date, csv_field_details, csv_field_currency, csv_field_debit, csv_field_credit, csv_field_balance, csv_field_detail_override, csv_field_designated_balance), item_instance_class.constructor_parameters)) self.csv_to_constructor = Mapping( statement_item_csv_fields, item_instance_class.constructor_parameters, field_mappings, field_casts=dict(date=cast_dmy_date_from_string))
def _statement_item_export_fields(): field_names = tuple(field.name for field in StatementItem.constructor_parameters) csv_fields = tuple( TransformedStringField(name, _extract_account_no) if name == 'account' else StringField(name) for name in field_names if name not in COMPUTED_FIELDS) csv_fields[1]._strfmt = '%d/%m/%Y' return csv_fields
def tax_rebates_from_gsheet(session, extract_from_detailed_ledger): gs_field_parishioner_id = IntField('id') gs_field_status = StringField('status') gs_field_2015_rebate = StringField('2015 rebate') gs_field_2016_rebate = StringField('2016 rebate') gs_field_2017_rebate = StringField('2017 rebate') gs_field_2018_rebate = StringField('2018 rebate') tax_rebate_gsheet = ListFieldGroup( ( gs_field_parishioner_id, UnusedField('household id'), UnusedField('new pps'), gs_field_status, gs_field_2015_rebate, gs_field_2016_rebate, gs_field_2017_rebate, gs_field_2018_rebate, ) ) field_mappings = tuple(zip( ( gs_field_parishioner_id, gs_field_status, gs_field_2015_rebate, gs_field_2016_rebate, gs_field_2017_rebate, gs_field_2018_rebate, ), TaxRebate.constructor_parameters )) field_casts = { 'id': PersonQuery(session).instance_finder('parishioner_reference_no', int), } tax_rebate_mapping = Mapping(tax_rebate_gsheet, TaxRebate.constructor_parameters, field_mappings, field_casts) tax_rebates = extract_from_detailed_ledger( 'tax rebate responses', 'A1', ('id', 'household id', 'new pps', 'status', '2015 rebate', '2016 rebate', '2017 rebate', '2018 rebate') ) load_class(session, tax_rebates, tax_rebate_mapping, TaxRebate)
class PPS(ObjectFieldGroupBase): # Data usage # # Records PPS number for an individual in order that a tax rebate may be claimed # on funds donated to the parish. public_interface = ( ObjectReferenceField('person', required=True), PPSStatusField( 'status', required=True, default=PPSStatus.Requested, description='Has the parishioner responded to a request for a PPS?' ), StringField('pps'), StringField('name_override'), IntField('chy3_valid_year', description='The first financial year the most recent CHY3 form is valid from'), DescriptionField('notes') )
def accounts_from_gsheet(session, extract_from_detailed_ledger): account_gsheet = get_gsheet_fields(Account, {'reference no': 'id'}) account_gsheet['status'] = StringField('status') account_mapping = Mapping(account_gsheet, Account.constructor_parameters, field_casts=field_casts) accounts = extract_from_detailed_ledger( 'bank accounts', 'A1', ('id', 'purpose', 'status', 'name', 'institution', 'sort code', 'account no', 'BIC', 'IBAN')) load_class(session, accounts, account_mapping, Account)
class Counterparty(ObjectFieldGroupBase): # Data usage # # Associates an identifiable person with their financial activity # public_interface = ( IntField('reference_no'), StringField( 'bank_text', description='Used to identify donors from bank statements'), ObjectReferenceField('person'), ObjectReferenceField('organisation'), StringField('name_override'), StringField( 'method', description= 'Method whereby funds are received or dispersed. Used to aid reconciliation.' ), # TODO enum? BooleanField( 'has_SO_card', description='Has donor requested a standing order donor card.'), BooleanField( 'by_email', 'Has the donor agreed to receive communications by email?'), DescriptionField('notes', 'Free text record of unusual circumstances.'), ) def __str__(self): return '{0.__class__.__name__}({0._reference_no}, {0.name}, {0._bank_text})'.format( self) @property def name(self): return self._name_override if self._name_override else self._person.name @property def lookup_name(self): return self.name.lower()
def _statement_item_gsheet_export_fields(): transformed_fields_map = { 'account': TransformedStringField('account', _extract_account_no), 'date': StringField('date', strfmt='%d/%m/%Y'), 'debit': FloatField('debit'), 'credit': FloatField('credit'), 'balance': FloatField('balance'), } gsheet_fields = tuple( transformed_fields_map.get(field.name, field) for field in StatementItem.public_interface if field.name not in COMPUTED_FIELDS) return gsheet_fields
class TaxRebate(ObjectFieldGroupBase): # Data usage # # Record of the years in which a person's PPS was submitted in a rebate claim public_interface = ( ObjectReferenceField('person'), StringField('status'), StringField('2015_rebate'), StringField('2016_rebate'), StringField('2017_rebate'), StringField('2018_rebate'), ) def has_rebate_for_year(self, fy: int): field_name = f"{fy}_rebate" if hasattr(self, field_name): fy_info = getattr(self, field_name) if "claimed" in fy_info: filing_year = int(fy_info.replace(" - claimed", "")) return filing_year return None
class Household(ObjectFieldGroupBase): # Receives household records from parish list public_interface = ( IntField('reference_no', is_mutable=False), StringField('address1', required=True), StringField('address2'), StringField('address3'), StringField('county'), StringField('eircode'), StringField('telephone'), )
class Account(ObjectFieldGroupBase): public_interface = ( IntField('reference_no', is_mutable=False), StringField('purpose'), AccountStatusField('status'), StringField('name'), StringField('institution'), StringField('sort_code'), StringField('account_no'), StringField('BIC'), StringField('IBAN'), ) def __str__(self): return '{0.__class__.__name__}({0._reference_no}, {0._account_no})'.format( self)
class TaxRebateSubmission(ObjectFieldGroupBase): # Data usage # # Record of the years in which a person's PPS was submitted in a rebate claim public_interface = ( SubmissionStatusField( 'status', required=True, default=SubmissionStatus.Preparing, description= 'Records what stage in its lifecycle the submission is at.'), IntField('FY', required=True), DecimalField('calculated_rebate'), DateField('filing_date'), DecimalField('estimated_rebate', description='Estimated rebate from CDS1 form.'), StringField('notice_number'), DescriptionField('notes'), )
class Organisation(ObjectFieldGroupBase): # Data usage # # Represents a household or other organisation # People belong to an organisation # public_interface = ( StringField('name', required=True), OrganisationCategoryField('category', required=True), OrganisationStatusField('status', required=True, default=OrganisationStatus.Active), IntField( 'reference_no', required=True, is_mutable=False, description= 'Internal use. Refers to identity in source data. Required for initial data load.' ), )
def tax_rebate_submissions_from_gsheet(session, extract_from_tax_rebates): gs_field_status = StringField('status') gs_field_year = IntField('year') gs_field_cal_rebate = StringField('calculated rebate') gs_field_filing_date = StringField('filing date') gs_field_est_rebate = StringField('estimated rebate from CDS1') gs_field_notice_no = StringField('notice number') gs_field_notes = StringField('notes') tax_rebates_gsheet = ListFieldGroup( (gs_field_status, gs_field_year, UnusedField('parishoner count'), UnusedField('donor count'), UnusedField('donations'), gs_field_cal_rebate, gs_field_filing_date, gs_field_est_rebate, gs_field_notice_no, gs_field_notes)) field_mappings = tuple( zip((gs_field_status, gs_field_year, gs_field_cal_rebate, gs_field_filing_date, gs_field_est_rebate, gs_field_notice_no, gs_field_notes), TaxRebateSubmission.constructor_parameters)) field_casts = { 'status': lambda v, _: SubmissionStatus.Posted if v == 'posted' else SubmissionStatus.Revoked, 'calculated rebate': strip_commas, 'filing date': cast_dmy_date_from_string, 'estimated rebate from CDS1': strip_commas } tax_rebates_mapping = Mapping(tax_rebates_gsheet, TaxRebateSubmission.constructor_parameters, field_mappings, field_casts) tax_rebate_submissions = extract_from_tax_rebates( 'records', 'B1', ('status', 'year', 'parishoner count', 'donor count', 'donations', 'calculated rebate', 'filing date', 'estimated rebate from CDS1', 'notice number', 'notes')) load_class(session, tax_rebate_submissions, tax_rebates_mapping, TaxRebateSubmission)
class AReferringClass(ObjectFieldGroupBase): public_interface = ( StringField('name'), ObjectReferenceField('aclass'), )
def transactions_from_gsheet(session, extract_from_detailed_ledger): gs_field_reference_no = StringField('id') gs_field_public_code = StringField('reference') gs_field_year = StringField('year') gs_field_month = StringField('month') gs_field_day = StringField('day') gs_field_counterparty = StringField('counterparty_id') gs_field_payment_method = StringField('payment_method') gs_field_description = StringField('description') gs_field_amount = StringField('amount') gs_field_subject = StringField('subject') gs_field_income_expenditure = StringField('income_expenditure') gs_field_FY = StringField('FY') gs_field_fund = StringField('fund') gs_field_comments = StringField('comments') transaction_gsheet = ListFieldGroup(( gs_field_reference_no, gs_field_fund, gs_field_public_code, UnusedField('bank account'), UnusedField('compositeId'), gs_field_year, gs_field_month, gs_field_day, gs_field_counterparty, UnusedField('counterparty name'), UnusedField('household_id'), gs_field_payment_method, gs_field_description, gs_field_amount, gs_field_subject, gs_field_income_expenditure, gs_field_FY, UnusedField('sign'), UnusedField('net'), UnusedField('from bank statement'), UnusedField('reconciles'), UnusedField('bank stmt year'), UnusedField('year reconciles?'), UnusedField('monthText'), UnusedField('quarter'), UnusedField('subjectSummary'), UnusedField('fund type'), gs_field_comments, )) field_casts = { 'counterparty_id': CounterpartyQuery(session).instance_finder('reference_no', int), 'payment_method': cast_payment_method, 'amount': strip_commas, 'subject': SubjectQuery(session).instance_finder('name', None), 'income_expenditure': cast_income_expenditure, 'fund': FundQuery(session).instance_finder('name', None), } field_mappings = tuple( zip(( gs_field_reference_no, gs_field_public_code, gs_field_year, gs_field_month, gs_field_day, gs_field_counterparty, gs_field_payment_method, gs_field_description, gs_field_amount, gs_field_subject, gs_field_income_expenditure, gs_field_FY, gs_field_fund, gs_field_comments, ), Transaction.constructor_parameters)) transaction_mapping = Mapping(transaction_gsheet, Transaction.constructor_parameters, field_mappings, field_casts) transactions = extract_from_detailed_ledger( 'transactions', 'A1', ('id', 'fund', 'reference', 'bank account', 'compositeId', 'year', 'month', 'day', 'counterparty_id', 'counterparty_name', 'household_id', 'payment_method', 'description', 'amount', 'subject', 'income/expenditure', 'FY', 'sign', 'net', 'from bank statement', 'reconciles', 'bank stmt year', 'year reconciles?', 'monthText', 'quarter', 'subjectSummary', 'fund type', 'comments')) load_class(session, transactions, transaction_mapping, Transaction)
def counterparty_from_gsheet(session, extract_from_detailed_ledger): gs_field_id = IntField('id') gs_field_bank_text = StringField('bank text') gs_field_person = IntField('parishoner id') gs_field_organisation = IntField('household id') gs_field_name_override = StringField('name override') gs_field_method = StringField('method') gs_field_so_card = StringField('SO card?') gs_field_by_email = StringField('by email') gs_field_notes = StringField('notes') counterparty_gsheet = ListFieldGroup(( gs_field_id, gs_field_bank_text, gs_field_person, gs_field_organisation, UnusedField('main contact'), UnusedField('.'), gs_field_name_override, UnusedField('name'), UnusedField('_'), UnusedField('reverse lookup parishoner id'), UnusedField('reverse lookup cp id'), UnusedField('__'), UnusedField('___'), UnusedField('____'), UnusedField('_____'), UnusedField('______'), gs_field_method, gs_field_so_card, gs_field_by_email, gs_field_notes, )) field_mappings = tuple( zip(( gs_field_id, gs_field_bank_text, gs_field_person, gs_field_organisation, gs_field_name_override, gs_field_method, gs_field_so_card, gs_field_by_email, gs_field_notes, ), Counterparty.constructor_parameters)) field_casts = { 'parishoner id': PersonQuery(session).instance_finder('parishioner_reference_no', int), 'household id': OrganisationQuery(session).instance_finder('reference_no', int), 'SO card?': cast_yes_no, 'by email': cast_yes_no, } counterparty_mapping = Mapping(counterparty_gsheet, Counterparty.constructor_parameters, field_mappings, field_casts) counterparties = extract_from_detailed_ledger( 'counterparties', 'A1', ('id', 'bank text', 'parishoner id', 'household id', 'main contact', '.', 'name override', 'name', '_', 'reverse lookup parishoner id', 'reverse lookup cp id', '__', '___', '____', '_____', '______', 'method', 'SO card?', 'by email', 'notes')) load_class(session, counterparties, counterparty_mapping, Counterparty)
class ATypedClass(ObjectFieldGroupBase): public_interface = ( StringField('name'), IntField('type'), )
class Person(ObjectFieldGroupBase): # May be a degenerate object which just refers to an Organisation public_interface = ( ObjectReferenceField('organisation', required=True), StringField( 'family_name', description= 'Surname of individual. Used to correctly address communications to them.' ), StringField( 'given_name', description= 'First name of individual. Used to correctly address communications to them.' ), StringField( 'title', description= 'Honorific used in formal communications and when addressing letters.' ), PersonStatusField( 'status', required=True, default=PersonStatus.Active, description= 'Is the person living, deceased or has contact been lost with them.' ), StringField( 'mobile', description= 'In addition to facilitating voice communications may be used to supplement secure access to personal data.' ), StringField( 'other_phone', description='Supplementary phone number e.g. work direct dial, fax.' ), StringField( 'email', description= 'Primary means of electronic communication and identity for maintaining personal information.' ), IntField( 'parishioner_reference_no', is_mutable=False, description= 'Internal use. Refers to identity in source data. Required for initial data load.' ), ) def __str__(self): return '{0.__class__.__name__}({0._parishioner_reference_no}, {0.name})'.format( self) @property def name(self): if self._title: return '{0._title} {0._given_name} {0._family_name}'.format(self) else: return '{0._given_name} {0._family_name}'.format(self) @property def name_without_title(self): return '{0._given_name} {0._family_name}'.format(self)
def parishioners_from_gsheet(session, extract_from_parish_list): gs_field_id = IntField('id') gs_field_surname = StringField('SURNAME') gs_field_first_name = StringField('FIRST_NAME') gs_field_title = StringField('TITLE') gs_field_status = StringField('STATUS') gs_field_main_contact = StringField('main_contact') gs_field_household_ref_no = IntField('household_id') gs_field_mobile = StringField('mobile') gs_field_other_personal = StringField('other personal') gs_field_email = StringField('email') gs_field_gdpr_response = StringField('gdpr response?') gs_field_by_email = StringField('email?') gs_field_by_phone = StringField('phone?') gs_field_by_post = StringField('post?') gs_field_news = StringField('news?') gs_field_finance = StringField('finance?') parishioner_gsheet = ListFieldGroup(( gs_field_id, gs_field_surname, gs_field_first_name, gs_field_title, gs_field_status, gs_field_main_contact, gs_field_household_ref_no, UnusedField('ADDRESS1'), UnusedField('ADDRESS2'), UnusedField('ADDRESS3'), UnusedField('County'), UnusedField('EIRCODE'), UnusedField('TELEPHONE'), gs_field_mobile, gs_field_other_personal, gs_field_email, gs_field_gdpr_response, gs_field_by_email, gs_field_by_phone, gs_field_by_post, gs_field_news, gs_field_finance, )) field_mappings = tuple( zip(( gs_field_id, gs_field_surname, gs_field_first_name, gs_field_title, gs_field_status, gs_field_main_contact, gs_field_household_ref_no, gs_field_mobile, gs_field_other_personal, gs_field_email, gs_field_gdpr_response, gs_field_by_email, gs_field_by_phone, gs_field_by_post, gs_field_news, gs_field_finance, ), Parishioner.constructor_parameters)) parishioner_mapping = Mapping(parishioner_gsheet, Parishioner.constructor_parameters, field_mappings) parishioner_rows = extract_from_parish_list( 'parishioners', 'A1', ('id', 'SURNAME', 'FIRST_NAME', 'TITLE', 'STATUS', 'main contact?', 'household id', 'ADDRESS1', 'ADDRESS2', 'ADDRESS3', 'County', 'EIRCODE', 'landline', 'mobile', 'other personal', 'email', 'gdpr response?', 'email?', 'phone?', 'post?', 'news?', 'finance?')) parishioners = list( model_instances(parishioner_rows, parishioner_mapping, Parishioner)) session.add_all(parishioners)
class Parishioner(ObjectFieldGroupBase): # Receives parishioner records from parish list public_interface = ( IntField('reference_no', is_mutable=False), StringField('surname'), StringField('first_name'), StringField('title'), StringField('status'), StringField('main_contact'), IntField('household_ref_no'), StringField('mobile'), StringField('other'), StringField('email'), StringField('gdpr_response'), StringField('by_email'), StringField('by_phone'), StringField('by_post'), StringField('news'), StringField('finance'), )
class StatementItem(ObjectFieldGroupBase): public_interface = ( ObjectReferenceField('account'), # TODO: allow properties to be named differently to internal/db fields DateField('date'), StringField('details'), StringField('currency'), DecimalField('debit', use_custom_properties=True), DecimalField('credit', use_custom_properties=True), DecimalField('balance'), StringField('detail_override'), StatementItemDesignatedBalanceField( 'designated_balance', default=StatementItemDesignatedBalance.No)) # metaclass takes care of dealing with the args def __init__(self, *args, **kwargs): self._designated_balance = None def __str__(self): return '{0.__class__.__name__}({0._account}, {0._date}, {0.trimmed_details})'.format( self) @property def debit(self): return self._debit if self._debit is not None else Decimal('0.00') @debit.setter def debit(self, value): self._debit = value @property def credit(self): return self._credit if self._credit is not None else Decimal('0.00') @credit.setter def credit(self, value): self._credit = value @property def net(self): return self.credit - self.debit @property def year(self): return self._date.year @property def month(self): return self._date.month @property def day(self): return self._date.day @property def unified_details(self): return self._detail_override if self._detail_override else self._details @property def public_code(self): details = self.unified_details # lodgments if details.startswith('LODGMENT'): return details.replace('LODGMENT', '').strip() # direct payments out and in elif details.startswith('D0') or details.startswith('E0'): return details[0:6] # cheque numbers elif re.search(r'^\d{6}$', details): return details else: return None @property def trimmed_details(self): """ Drop any text after the specified strings :return: """ return re.sub(TRUNCATE_ON_PATTERN, '\g<1>', self.unified_details)
'in use': AccountStatus.Active, 'ready': AccountStatus.Active, 'closed': AccountStatus.Closed, } def conform_value(value, _): return ACCOUNT_STATUS_MAP.get(value.lower(), AccountStatus.Active) field_casts = dict(status=conform_value) account_csv_fields = Account.constructor_parameters.derive( replace_underscore_with_space, DictFieldGroup) account_csv_fields['reference no'].name = 'id' account_csv_fields['status'] = StringField('status') csv_to_constructor = Mapping(account_csv_fields, Account.constructor_parameters, field_casts=field_casts) def accounts_from_csv(account_csv): items = [] for row in DictReader(account_csv): account_args = csv_to_constructor.cast_from(row) items.append(Account(**account_args)) collection = AccountCollection(items) return collection