def post_process_it_h4(cursor, cmte_id):
"""
helper function:
merge transactions and entities after transactions are loaded
"""
transaction_list = cursor.fetchone()[0]
if not transaction_list:
return []
# if not transaction_list:
# if (
# not back_ref_transaction_id
# ): # raise exception for non_child transaction loading
# raise NoOPError(
# "No transactions found."
# )
# else: # return empy list for child transaction loading
# return []
merged_list = []
for item in transaction_list:
entity_id = item.get("payee_entity_id")
data = {"entity_id": entity_id, "cmte_id": cmte_id}
entity_list = get_entities(data)
dictEntity = entity_list[0]
# cand_entity = {}
# if item.get("beneficiary_cand_entity_id"):
# cand_data = {
# "entity_id": item.get("beneficiary_cand_entity_id"),
# "cmte_id": cmte_id,
# }
# cand_entity = get_entities(cand_data)[0]
# cand_entity = candify_it(cand_entity)
merged_dict = {**item, **dictEntity}
merged_list.append(merged_dict)
return merged_list
def get_list_all_schedD(report_id, cmte_id, transaction_type_identifier):
"""
load sched_d items from DB
"""
try:
with connection.cursor() as cursor:
# GET all rows from schedA table
# GET single row from schedA table
query_string = """
SELECT cmte_id,
report_id,
line_num,
transaction_type_identifier,
transaction_id,
entity_id,
beginning_balance,
balance_at_close,
incurred_amount,
payment_amount,
back_ref_transaction_id,
back_ref_sched_name,
last_update_date
FROM public.sched_d
WHERE report_id = %s
AND cmte_id = %s
AND delete_ind is distinct from 'Y'
"""
type_filter = 'AND transaction_type_identifier = %s'
if transaction_type_identifier:
cursor.execute(
"""SELECT json_agg(t) FROM (""" + query_string +
type_filter + """) t""",
(report_id, cmte_id, transaction_type_identifier),
)
else:
cursor.execute(
"""SELECT json_agg(t) FROM (""" + query_string + """) t""",
(report_id, cmte_id),
)
schedD_list = cursor.fetchone()[0]
if schedD_list is None:
raise NoOPError(
"The Report id:{} does not have any schedD transactions".
format(report_id))
merged_list = []
for dictD in schedD_list:
entity_id = dictD.get("entity_id")
q_data = {"entity_id": entity_id, "cmte_id": cmte_id}
entity_list = get_entities(q_data)
dictEntity = entity_list[0]
# merged_dict = {**dictD, **dictEntity}
merged_list.append({**dictD, **dictEntity})
return merged_list
except Exception:
raise
def put_schedF(data):
"""
update sched_F item
here we are assuming entity_id are always referencing something already in our DB
"""
try:
check_mandatory_fields_SF(data)
# check_transaction_id(data.get('transaction_id'))
if "entity_id" in data:
get_data = {
"cmte_id": data.get("cmte_id"),
"entity_id": data.get("entity_id"),
}
# need this update for FEC entity
if get_data["entity_id"].startswith("FEC"):
get_data["cmte_id"] = "C00000000"
prev_entity_list = get_entities(get_data)
entity_data = put_entities(data)
entity_flag = True
else:
entity_data = post_entities(data)
entity_flag = False
existing_expenditure = get_existing_expenditure_amount(
data.get("cmte_id"), data.get("transaction_id"))
try:
entity_id = entity_data.get("entity_id")
data["payee_entity_id"] = entity_id
put_sql_schedF(data)
# if debt payment, need to update debt balance
if data.get("transaction_type_identifier") == "COEXP_PARTY_DEBT":
if float(existing_expenditure) != float(
data.get("expenditure_amount")):
update_sched_d_parent(
data.get("cmte_id"),
data.get("back_ref_transaction_id"),
data.get("expenditure_amount"),
existing_expenditure,
)
except Exception as e:
# if exceptions saving shced_a, remove entities or rollback entities too
if entity_flag:
entity_data = put_entities(prev_entity_list[0])
else:
get_data = {
"cmte_id": data.get("cmte_id"),
"entity_id": entity_id
}
remove_entities(get_data)
raise Exception(
"The put_sql_schedF function is throwing an error: " + str(e))
return data
except:
raise
def post_schedF(data):
"""
function for handling POST request for sF, need to:
1. generatye new transaction_id
2. validate data
3. save data to db
"""
try:
# check_mandatory_fields_SA(datum, MANDATORY_FIELDS_SCHED_A)
data["transaction_id"] = get_next_transaction_id("SF")
validate_sF_data(data)
if "entity_id" in data:
get_data = {
"cmte_id": data.get("cmte_id"),
"entity_id": data.get("entity_id"),
}
# need this update for FEC entity
if get_data["entity_id"].startswith("FEC"):
get_data["cmte_id"] = "C00000000"
prev_entity_list = get_entities(get_data)
entity_data = put_entities(data)
entity_flag = True
else:
entity_data = post_entities(data)
entity_flag = False
try:
entity_id = entity_data.get("entity_id")
data["payee_entity_id"] = entity_id
logger.debug(data)
post_sql_schedF(data)
# update sched_d parent if coexp debt payment
if data.get("transaction_type_identifier") == "COEXP_PARTY_DEBT":
update_sched_d_parent(
data.get("cmte_id"),
data.get("back_ref_transaction_id"),
data.get("expenditure_amount"),
)
except Exception as e:
# if exceptions saving shced_a, remove entities or rollback entities too
if entity_flag:
entity_data = put_entities(prev_entity_list[0])
else:
get_data = {
"cmte_id": data.get("cmte_id"),
"entity_id": entity_id
}
remove_entities(get_data)
raise Exception(
"The post_sql_schedF function is throwing an error: " + str(e))
return data
except:
raise
def get_list_schedD(report_id, cmte_id, transaction_id):
try:
with connection.cursor() as cursor:
# GET single row from schedA table
# query_string = """
# SELECT cmte_id,
# report_id,
# line_num,
# transaction_type_identifier,
# transaction_id,
# entity_id,
# beginning_balance,
# balance_at_close,
# incurred_amount,
# payment_amount,
# last_update_date
# FROM public.sched_d WHERE report_id = %s AND cmte_id = %s
# AND transaction_id = %s AND delete_ind is distinct from 'Y'
# """
query_string = """
SELECT cmte_id,
report_id,
line_num,
transaction_type_identifier,
transaction_id,
entity_id,
beginning_balance,
balance_at_close,
incurred_amount,
payment_amount,
back_ref_transaction_id,
back_ref_sched_name,
balance_at_close,
last_update_date
FROM public.sched_d WHERE report_id = %s AND cmte_id = %s
AND transaction_id = %s AND delete_ind is distinct from 'Y'
"""
cursor.execute(
"""SELECT json_agg(t) FROM (""" + query_string + """) t""",
(report_id, cmte_id, transaction_id),
)
schedD_list = cursor.fetchone()[0]
if schedD_list is None:
raise NoOPError(
"The transaction id: {} does not exist or is deleted".
format(transaction_id))
merged_list = []
for dictD in schedD_list:
entity_id = dictD.get("entity_id")
q_data = {"entity_id": entity_id, "cmte_id": cmte_id}
entity_list = get_entities(q_data)
dictEntity = entity_list[0]
# merged_dict = {**dictD, **dictEntity}
merged_list.append({**dictD, **dictEntity})
return merged_list
except Exception:
raise
def post_schedD(datum):
"""save sched_d item and the associated entities."""
try:
# check_mandatory_fields_SA(datum, MANDATORY_FIELDS_SCHED_A)
if "entity_id" in datum:
get_data = {
"cmte_id": datum.get("cmte_id"),
"entity_id": datum.get("entity_id"),
}
# need this update for FEC entity
if get_data["entity_id"].startswith("FEC"):
get_data["cmte_id"] = "C00000000"
prev_entity_list = get_entities(get_data)
entity_data = put_entities(datum)
entity_flag = True
else:
entity_data = post_entities(datum)
entity_flag = False
# continue to save transaction
entity_id = entity_data.get("entity_id")
datum["entity_id"] = entity_id
transaction_id = get_next_transaction_id("SD")
datum["transaction_id"] = transaction_id
validate_sd_data(datum)
# save entities rirst
# if 'creditor_entity_id' in datum:
# get_data = {
# 'cmte_id': datum.get('cmte_id'),
# 'entity_id': datum.get('creditor_entity_id')
# }
# prev_entity_list = get_entities(get_data)
# entity_data = put_entities(datum)
# else:
# entity_data = post_entities(datum)
# continue to save transaction
# creditor_entity_id = entity_data.get('creditor_entity_id')
# datum['creditor_entity_id'] = creditor_entity_id
# datum['line_number'] = disclosure_rules(datum.get('line_number'), datum.get('report_id'), datum.get('transaction_type'), datum.get('contribution_amount'), datum.get('contribution_date'), entity_id, datum.get('cmte_id'))
# trans_char = "SD"
# transaction_id = get_next_transaction_id(trans_char)
# datum['transaction_id'] = transaction_id
try:
post_sql_schedD(datum)
except Exception as e:
if entity_flag:
entity_data = put_entities(prev_entity_list[0])
else:
get_data = {
"cmte_id": datum.get("cmte_id"),
"entity_id": entity_id
}
remove_entities(get_data)
raise Exception(
"The post_sql_schedD function is throwing an error: " + str(e))
return datum
except:
raise
def put_schedD(datum):
"""update sched_d item
here we are assuming creditor_entoty_id are always referencing something already in our DB
"""
try:
# save entity data first
if "entity_id" in datum:
get_data = {
"cmte_id": datum.get("cmte_id"),
"entity_id": datum.get("entity_id"),
}
# need this update for FEC entity
if get_data["entity_id"].startswith("FEC"):
get_data["cmte_id"] = "C00000000"
prev_entity_list = get_entities(get_data)
entity_data = put_entities(datum)
entity_flag = True
else:
entity_data = post_entities(datum)
entity_flag = False
# continue to save transaction
entity_id = entity_data.get("entity_id")
datum["entity_id"] = entity_id
check_mandatory_fields_SD(datum)
transaction_id = check_transaction_id(datum.get("transaction_id"))
# flag = False
# if 'entity_id' in datum:
# flag = True
# get_data = {
# 'cmte_id': datum.get('cmte_id'),
# 'entity_id': datum.get('entity_id')
# }
# prev_entity_list = get_entities(get_data)
# entity_data = put_entities(datum)
# else:
# entity_data = post_entities(datum)
# entity_id = entity_data.get('entity_id')
# datum['entity_id'] = entity_id
cmte_id = datum.get("cmte_id")
report_id = datum.get("report_id")
current_close_balance = float(datum.get("balance_at_close"))
existing_close_balance = float(
get_existing_close_balance(cmte_id, report_id, transaction_id))
try:
put_sql_schedD(datum)
# do downstream proprgation if necessary
if not existing_close_balance == current_close_balance:
do_downstream_propagation(transaction_id,
current_close_balance)
except Exception as e:
if entity_flag:
entity_data = put_entities(prev_entity_list[0])
else:
get_data = {
"cmte_id": datum.get("cmte_id"),
"entity_id": entity_id
}
remove_entities(get_data)
raise Exception(
"The put_sql_schedD function is throwing an error: " + str(e))
return datum
except:
raise
def get_list_schedF(report_id, cmte_id, transaction_id, is_back_ref=False):
try:
with connection.cursor() as cursor:
# GET single row from schedA table
_sql = """SELECT json_agg(t) FROM ( SELECT
sf.cmte_id,
sf.report_id,
sf.transaction_type_identifier,
sf.transaction_id,
sf.back_ref_transaction_id,
sf.back_ref_sched_name,
sf.coordinated_exp_ind,
sf.designating_cmte_id,
sf.designating_cmte_name,
sf.subordinate_cmte_id,
sf.subordinate_cmte_name,
sf.subordinate_cmte_street_1,
sf.subordinate_cmte_street_2,
sf.subordinate_cmte_city,
sf.subordinate_cmte_state,
sf.subordinate_cmte_zip,
sf.payee_entity_id as entity_id,
sf.expenditure_date,
sf.expenditure_amount,
sf.aggregate_general_elec_exp,
sf.line_number,
sf.transaction_type,
sf.purpose,
sf.category_code,
sf.payee_cmte_id,
sf.payee_cand_id as beneficiary_cand_id,
sf.payee_cand_last_name as cand_last_name,
sf.payee_cand_fist_name as cand_first_name,
sf.payee_cand_middle_name as cand_middle_name,
sf.payee_cand_prefix as cand_prefix,
sf.payee_cand_suffix as cand_suffix,
sf.payee_cand_office as cand_office,
sf.payee_cand_state as cand_office_state,
sf.payee_cand_district as cand_office_district,
sf.memo_code,
sf.memo_text,
sf.delete_ind,
sf.create_date,
sf.last_update_date,
(SELECT DISTINCT ON (e.ref_cand_cmte_id) e.entity_id
FROM public.entity e WHERE e.entity_id not in (select ex.entity_id from excluded_entity ex where ex.cmte_id = sf.cmte_id)
AND substr(e.ref_cand_cmte_id,1,1) != 'C' AND e.ref_cand_cmte_id = sf.payee_cand_id AND e.delete_ind is distinct from 'Y'
ORDER BY e.ref_cand_cmte_id DESC, e.entity_id DESC) AS beneficiary_cand_entity_id
FROM public.sched_f sf
WHERE sf.report_id = %s AND sf.cmte_id = %s
AND sf.delete_ind is distinct from 'Y'
"""
if is_back_ref:
_sql = _sql + """ AND sf.back_ref_transaction_id = %s) t"""
else:
_sql = _sql + """ AND sf.transaction_id = %s) t"""
cursor.execute(_sql, (report_id, cmte_id, transaction_id))
schedF_list = cursor.fetchone()[0]
if schedF_list is None:
if not is_back_ref:
raise NoOPError(
"No sched_f transaction found for transaction_id {}".
format(transaction_id))
else:
return schedF_list
merged_list = []
for dictF in schedF_list:
entity_id = dictF.get("entity_id")
data = {"entity_id": entity_id, "cmte_id": cmte_id}
entity_list = get_entities(data)
dictEntity = entity_list[0]
del dictEntity["cand_office"]
del dictEntity["cand_office_state"]
del dictEntity["cand_office_district"]
merged_dict = {**dictF, **dictEntity}
merged_list.append(merged_dict)
return merged_list
except Exception:
raise
def get_list_all_schedF(report_id, cmte_id):
try:
with connection.cursor() as cursor:
# GET single row from schedA table
_sql = """SELECT json_agg(t) FROM ( SELECT
cmte_id,
report_id,
transaction_type_identifier,
transaction_id,
back_ref_transaction_id,
back_ref_sched_name,
coordinated_exp_ind,
designating_cmte_id,
designating_cmte_name,
subordinate_cmte_id,
subordinate_cmte_name,
subordinate_cmte_street_1,
subordinate_cmte_street_2,
subordinate_cmte_city,
subordinate_cmte_state,
subordinate_cmte_zip,
payee_entity_id as entity_id,
expenditure_date,
expenditure_amount,
aggregate_general_elec_exp,
line_number,
transaction_type,
purpose,
category_code,
payee_cmte_id,
payee_cand_id as beneficiary_cand_id,
payee_cand_last_name as cand_last_name,
payee_cand_fist_name as cand_first_name,
payee_cand_middle_name as cand_middle_name,
payee_cand_prefix as cand_prefix,
payee_cand_suffix as cand_suffix,
payee_cand_office as cand_office,
payee_cand_state as cand_office_state,
payee_cand_district as cand_office_district,
memo_code,
memo_text,
delete_ind,
create_date,
last_update_date
FROM public.sched_f
WHERE report_id = %s AND cmte_id = %s
AND delete_ind is distinct from 'Y') t
"""
cursor.execute(_sql, (report_id, cmte_id))
schedF_list = cursor.fetchone()[0]
if schedF_list is None:
raise NoOPError(
"No sched_F transaction found for report_id {} and cmte_id: {}"
.format(report_id, cmte_id))
merged_list = []
for dictF in schedF_list:
entity_id = dictF.get("entity_id")
data = {"entity_id": entity_id, "cmte_id": cmte_id}
entity_list = get_entities(data)
dictEntity = entity_list[0]
merged_dict = {**dictF, **dictEntity}
merged_list.append(merged_dict)
return merged_list
except Exception:
raise