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)
def subjects_from_gsheet(session, extract_from_detailed_ledger):
subject_gsheet = get_gsheet_fields(Subject, {
'name': 'subject',
})
subject_mapping = Mapping(subject_gsheet, Subject.constructor_parameters)
subjects = extract_from_detailed_ledger(
'report lookups', 'A3',
('subject', 'select vestry summary', 'easter vestry summary'))
load_class(session, subjects, subject_mapping, Subject)
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)
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)
def statement_item_from_gsheet(session, extract_from_detailed_ledger):
statement_item_gsheet = get_gsheet_fields(StatementItem, None)
field_casts = {
'account': AccountQuery(session).instance_finder('account_no', None),
'date': cast_dmy_date_from_string,
'debit': strip_commas,
'credit': strip_commas,
'balance': ignore_na,
'designated balance': cast_designated_balance,
}
statement_item_mapping = Mapping(statement_item_gsheet,
StatementItem.constructor_parameters,
field_casts=field_casts)
statement_items = extract_from_detailed_ledger(
'bank statements', 'A1',
('account', 'date', 'details', 'currency', 'debit', 'credit',
'balance', 'detail override', 'designated balance'))
load_class(session, statement_items, statement_item_mapping, StatementItem)
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)
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)
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)