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
class Envelope(ObjectFieldGroupBase):
# Data usage
#
# Used to administer the Free-will envelope programme.
# Records the envelope used by a person in a particular year
#
public_interface = (
IntField('year'),
ObjectReferenceField('counterparty'),
ObjectReferenceField('person'),
IntField('envelope_number'),
)
def _db_columns_from_model(self):
# now that model class is connected to the DB expose a readonly property for the id
id_field = IntField(ID_PROPERTY_NAME, is_mutable=False)
id_field.create_property_on_class(self._model_class, ID_FIELDNAME)
# map the id column
columns = {
ID_FIELDNAME: Column(ID_COLUMN_NAME, Integer, key=ID_FIELDNAME)
}
for source, dest in self._model_class.constructor_to_internal:
columns[dest.name] = Column(source.name, self._db_column_type_map[type(dest)], key=dest.name)
return columns
class AClass(ObjectFieldGroupBase):
public_interface = (
IntField('ref_no'),
StringField('name'),
BooleanField('is_running'),
AClassStatusField('status'),
DateField('date'),
)
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 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'),
)
def envelopes_from_gsheet(session, extract_from_detailed_ledger):
gs_field_year = IntField('year')
gs_field_counterparty = IntField('counterpartyid')
gs_field_parishioner = IntField('parishionerid')
gs_field_envelope_number = IntField('envelope number')
envelope_gsheet = ListFieldGroup((
gs_field_year,
gs_field_counterparty,
gs_field_parishioner,
UnusedField('household id'),
gs_field_envelope_number,
))
field_mappings = tuple(
zip((
gs_field_year,
gs_field_counterparty,
gs_field_parishioner,
gs_field_envelope_number,
), Envelope.constructor_parameters))
field_casts = {
'counterpartyid':
CounterpartyQuery(session).instance_finder('reference_no', int),
'parishionerid':
PersonQuery(session).instance_finder('parishioner_reference_no', int),
}
envelope_mapping = Mapping(envelope_gsheet,
Envelope.constructor_parameters, field_mappings,
field_casts)
envelopes = extract_from_detailed_ledger(
'FWE records', 'A1', ('year', 'counterpartyid', 'parishionerid',
'household id', 'envelope number'))
load_class(session, envelopes, envelope_mapping, Envelope)
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)
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 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 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.'
),
)
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 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)