def merge_chy3_letters(template_filename):
current_year = date.today().year
consider_last_n_years = int(configuration.tax.chy3.consider_last_n_years)
last_n_years = tuple(
range(current_year - consider_last_n_years + 1, current_year + 1))
valid_from_tax_year = current_year - 1
donation_threshold = Decimal(configuration.tax.chy3.minimum_donation)
drive_config = configuration.gdrive
gdrive = get_gdrive_service(configuration)
gdocs = get_gdocs_service(configuration)
template_file_id = drive_config.chy3_template_doc_id
working_folder = '.'
full_merge_pdf_filename = f"chy3_letters_from_{valid_from_tax_year}.pdf"
with session_scope() as session:
targets = PPSQuery(session).chy3_completion_targets(
last_n_years, donation_threshold)
with TemporaryDirectory(dir=working_folder,
prefix='chy3_merge_') as temp_dir:
output_files = list(
_merge_letters(gdrive, gdocs, temp_dir, template_file_id,
template_filename, valid_from_tax_year,
targets))
full_merge_pdf_filepath = os.path.join(temp_dir,
full_merge_pdf_filename)
concatenate(output_files, full_merge_pdf_filepath)
upload_to_gdrive(gdrive, full_merge_pdf_filepath,
full_merge_pdf_filename, PDF_MIME_TYPE,
configuration.charity.admin.email)
def output_statement_items(module_name, drive_config, output_csv,
output_spreadsheet, account_collection,
statement_items):
if output_spreadsheet:
gsheet = open_sheet(configuration, output_spreadsheet)
if gsheet:
import gspread
worksheet_name = drive_config.statement_items_sheet_name
try:
worksheet = gsheet.worksheet(worksheet_name)
except gspread.exceptions.WorksheetNotFound:
worksheet = None
if worksheet:
LOG.info(
f"Merging with existing worksheet, {worksheet_name}, on gsheet {output_spreadsheet}"
)
else:
worksheet = gsheet.add_worksheet(title=worksheet_name)
LOG.info(
f"Adding new sheet, {worksheet_name}, to existing gsheet {output_spreadsheet}"
)
_merge(statement_items, worksheet, account_collection)
else:
_, worksheet = _new_sheet(module_name, drive_config,
output_spreadsheet)
LOG.info(f"Writing to new gsheet {output_spreadsheet}")
_append(statement_items, worksheet)
elif output_csv:
with output_csv:
statement_item_csv(statement_items, output_csv)
else:
with session_scope() as session:
for statement_item in statement_items:
session.add(statement_item)
def get_account_collection(account_file):
if account_file:
account_collection = accounts_from_csv(account_file)
else:
with session_scope() as session:
account_collection = AccountQuery(session).collection()
return account_collection
def load_detailed_ledger():
sheets_config = configuration.gdrive
extract_from_detailed_ledger = extract_from_sheet(
configuration, sheets_config.ledger_sheet_id)
extract_from_tax_rebates = extract_from_sheet(
configuration, sheets_config.tax_rebates_sheet_id)
try:
with session_scope() as session:
accounts_from_gsheet(session, extract_from_detailed_ledger)
funds_from_gsheet(session, extract_from_detailed_ledger)
nominal_accounts_from_gsheet(session, extract_from_detailed_ledger)
subjects_from_gsheet(session, extract_from_detailed_ledger)
counterparty_from_gsheet(session, extract_from_detailed_ledger)
envelopes_from_gsheet(session, extract_from_detailed_ledger)
tax_rebates_from_gsheet(session, extract_from_detailed_ledger)
tax_rebate_submissions_from_gsheet(session,
extract_from_tax_rebates)
ppses_from_gsheet(session, extract_from_detailed_ledger)
organisations = OrganisationQuery(session).collection()
tax_rebate_submissions = TaxRebateSubmissionQuery(
session).collection()
reorganise_tax_rebates(session, organisations,
tax_rebate_submissions)
statement_item_from_gsheet(session, extract_from_detailed_ledger)
transactions_from_gsheet(session, extract_from_detailed_ledger)
except Exception as ex:
LOG.exception(ex)
def reconcile():
try:
with session_scope() as session:
TransactionCheckQuery(session).reconcile()
except Exception as ex:
LOG.exception(ex)
def transform_parishioners():
try:
with session_scope() as session:
households = HouseholdQuery(session).collection()
address_map = reorganise_households(session, households)
parishioners = ParishionerQuery(session).collection()
reorganise_parishioners(session, parishioners, address_map)
except Exception as ex:
LOG.exception(ex)
def load_parish_list():
sheets_config = configuration.gdrive
sheet_id = sheets_config.parish_list_sheet_id
extract_from_parish_list = extract_from_sheet(configuration, sheet_id)
try:
with session_scope() as session:
households_from_gsheet(session, extract_from_parish_list)
parishioners_from_gsheet(session, extract_from_parish_list)
except Exception as ex:
LOG.exception(ex)
def create_operational_database(config_file):
configuration = DotMap(yaml.full_load(config_file))
logging.config.dictConfig(configuration.logging.toDict())
logger = logging.getLogger(__file__)
try:
config_dict = configuration.db.toDict()
admin_connection_string = configuration.db.admin_connection.format(
**config_dict)
engine = create_engine(admin_connection_string, echo=False)
Session.configure(bind=engine)
with session_scope() as session:
session.execute(CREATE_ROLE_SQL.format(**config_dict))
restricted_connection_string = configuration.db.restricted_connection.format(
**config_dict)
if database_exists(restricted_connection_string):
logger.info(
f"Database {restricted_connection_string} already exists.")
else:
create_database(restricted_connection_string)
logger.info(f"Created database {restricted_connection_string}.")
engine = create_engine(restricted_connection_string, echo=False)
Session.configure(bind=engine)
with session_scope() as session:
session.execute(
"grant all privileges on database {operational_db_name} to {restricted_user}"
.format(**config_dict))
instructions = f"""Operational database created.
Now set sqlalchemy.url = {restricted_connection_string} in alembic.ini and run alembic migrations.
(venv) gordon@dev-workstation:~/projects/glod$ PYTHONPATH=./src alembic upgrade head
"""
logger.info(instructions)
except Exception as ex:
logger.exception(ex)