billsummarizer / app.py
rahgadda's picture
Initial Draft
f3a03d8 verified
import streamlit as st
import pandas as pd
import base64
# Display user Error, Warning or Success Message
def fn_display_user_messages(lv_text, lv_type, mv_processing_message):
"""Display user Info, Error, Warning or Success Message"""
if lv_type == "Success":
with mv_processing_message.container():
st.success(lv_text)
elif lv_type == "Error":
with mv_processing_message.container():
st.error(lv_text)
elif lv_type == "Warning":
with mv_processing_message.container():
st.warning(lv_text)
else:
with mv_processing_message.container():
st.info(lv_text)
# Removing the length of the suffix from the end
def remove_suffix(input_string, suffix):
"""Removing the length of the suffix from the end"""
if input_string.endswith(suffix):
return input_string[:-len(suffix)]
else:
raise("Invalid Input")
# Function to generate balance and due date history
def fn_generate_data(mv_setup_df, mv_txn_df, mv_balance_df, mv_due_date_history_df):
"""Function to generate balance and due date history"""
lv_total_tenure_outstanding = 0
lv_total_posted_txns = 0
lv_total_due_amt = 0
lv_total_payment = 0
lv_total_waived = 0
lv_total_adj_minus = 0
lv_total_payment_appropriated = 0
lv_total_refund = 0
lv_index = 0
lv_txn_details = []
mv_txn_details_by_bill = []
lv_has_excess_payment = False
lv_has_charged_off = False
lv_payment_excess = 0
for lv_txn_index, lv_txn_row in mv_txn_df.iterrows():
lv_setup_record_type = mv_setup_df.loc[mv_setup_df['TXN_CODE'] == lv_txn_row['TXN_TCD_CODE']]['TYPE'].values
lv_balance_code = lv_txn_row['TXN_TCD_CODE']
lv_posted = 0
lv_adj_plus = 0
lv_adj_minus = 0
lv_waive = 0
lv_paid = 0
lv_payment_excess_paid = 0
lv_refund =0
if lv_balance_code.endswith("CHGOFF"):
lv_has_charged_off = True
print("Account is Charged OFF")
break
if(lv_balance_code != 'PAYMENT'):
lv_txn_details.append(lv_txn_row)
if(lv_setup_record_type == 'REFUND'):
lv_refund = lv_txn_row['TXN_AMT']
lv_total_refund += lv_refund
if lv_balance_code in mv_balance_df['BALANCE_CODE'].values:
lv_temp_row_id = mv_balance_df.index[mv_balance_df['BALANCE_CODE'] == lv_balance_code][0]
mv_balance_df.at[lv_temp_row_id, 'POSTED'] = 0
mv_balance_df.at[lv_temp_row_id, 'PAID'] = 0
mv_balance_df.at[lv_temp_row_id, 'ADJ_PLUS'] = 0
mv_balance_df.at[lv_temp_row_id, 'ADJ_MINUS'] = 0
mv_balance_df.at[lv_temp_row_id, 'WAIVE'] = 0
mv_balance_df.at[lv_temp_row_id, 'PAID_WITH_EXCESS'] = 0
mv_balance_df.at[lv_temp_row_id, 'REFUND'] += lv_refund
mv_balance_df.at[lv_temp_row_id, 'OUTSTANDING'] = mv_balance_df.at[lv_temp_row_id, 'POSTED'] + mv_balance_df.at[lv_temp_row_id, 'ADJ_PLUS'] + mv_balance_df.at[lv_temp_row_id, 'REFUND'] - mv_balance_df.at[lv_temp_row_id, 'ADJ_MINUS'] - mv_balance_df.at[lv_temp_row_id,'WAIVE'] - mv_balance_df.at[lv_temp_row_id, 'PAID'] - mv_balance_df.at[lv_temp_row_id,'PAID_WITH_EXCESS']
else:
mv_balance_df = pd.concat([mv_balance_df, pd.DataFrame(
{ 'BALANCE_CODE': lv_balance_code,
'POSTED': [0],
'PAID': [0],
'ADJ_PLUS': [0],
'ADJ_MINUS': [0],
'WAIVE': [0],
'PAID_WITH_EXCESS': [0],
'REFUND':[lv_refund],
'OUTSTANDING': [lv_refund]
})],
ignore_index=True)
if(len(lv_setup_record_type) == 0 and lv_balance_code != 'PAYMENT_EXCESS'):
if(lv_txn_row['TXN_AMT']>0) :
if lv_balance_code.endswith("ADJ_PLUS"):
lv_balance_code = remove_suffix(lv_balance_code, "_ADJ_PLUS")
lv_adj_plus = lv_txn_row['TXN_AMT']
lv_total_tenure_outstanding += lv_adj_plus
lv_total_posted_txns += lv_adj_plus
elif lv_balance_code.endswith("ADJ_MINUS"):
lv_balance_code = remove_suffix(lv_balance_code, "_ADJ_MINUS")
lv_adj_minus = lv_txn_row['TXN_AMT']
lv_total_adj_minus += lv_adj_minus
elif lv_balance_code.endswith("WAIVE"):
lv_balance_code = remove_suffix(lv_balance_code, "_WAIVE")
lv_waive = lv_txn_row['TXN_AMT']
lv_total_waived += lv_waive
else:
lv_posted = lv_txn_row['TXN_AMT']
lv_total_tenure_outstanding += lv_posted
lv_total_posted_txns += lv_posted
elif(lv_txn_row['TXN_AMT']<0):
lv_temp_paid = lv_txn_row['TXN_AMT']
if(lv_txn_row['PAID_WITH_EXCESS'] == 'Y'):
lv_payment_excess += lv_temp_paid
lv_payment_excess_paid = lv_temp_paid*-1
else:
lv_paid = lv_temp_paid
lv_total_payment_appropriated += (lv_temp_paid*-1)
if (lv_txn_row['TXN_AMT'] != 0 and lv_balance_code != 'PAYMENT'):
if lv_balance_code in mv_balance_df['BALANCE_CODE'].values:
lv_temp_row_id = mv_balance_df.index[mv_balance_df['BALANCE_CODE'] == lv_balance_code][0]
mv_balance_df.at[lv_temp_row_id, 'POSTED'] += lv_posted
mv_balance_df.at[lv_temp_row_id, 'PAID'] += lv_paid*-1
mv_balance_df.at[lv_temp_row_id, 'ADJ_PLUS'] += lv_adj_plus
mv_balance_df.at[lv_temp_row_id, 'ADJ_MINUS'] += lv_adj_minus
mv_balance_df.at[lv_temp_row_id, 'WAIVE'] += lv_waive
mv_balance_df.at[lv_temp_row_id, 'PAID_WITH_EXCESS'] += lv_payment_excess_paid
mv_balance_df.at[lv_temp_row_id, 'REFUND'] += lv_refund
mv_balance_df.at[lv_temp_row_id, 'OUTSTANDING'] = mv_balance_df.at[lv_temp_row_id, 'POSTED'] + mv_balance_df.at[lv_temp_row_id, 'ADJ_PLUS'] + mv_balance_df.at[lv_temp_row_id, 'REFUND'] - mv_balance_df.at[lv_temp_row_id, 'ADJ_MINUS'] - mv_balance_df.at[lv_temp_row_id,'WAIVE'] - mv_balance_df.at[lv_temp_row_id, 'PAID'] - mv_balance_df.at[lv_temp_row_id,'PAID_WITH_EXCESS']
else:
mv_balance_df = pd.concat([mv_balance_df, pd.DataFrame(
{ 'BALANCE_CODE': [lv_balance_code],
'POSTED': [lv_posted],
'PAID': [lv_paid],
'ADJ_PLUS': [lv_adj_plus],
'ADJ_MINUS': [lv_adj_minus],
'WAIVE': [lv_waive],
'PAID_WITH_EXCESS': [lv_payment_excess_paid],
'REFUND':[lv_refund],
'OUTSTANDING': [lv_posted + lv_adj_plus + lv_refund - lv_paid - lv_adj_minus - lv_waive]
})],
ignore_index=True)
elif(lv_setup_record_type == "BILL"):
lv_due_generation_dt = lv_txn_row['TXN_DT']
lv_due_amount = lv_txn_row['TXN_AMT']
lv_total_due_amt += lv_due_amount
mv_due_date_history_df = pd.concat([
mv_due_date_history_df,
pd.DataFrame(
{
'DUE_GENERATION_DATE': [lv_due_generation_dt],
'DUE_AMOUNT': [lv_due_amount],
'ADDITIONAL_CHARGES': [round(lv_total_tenure_outstanding - lv_due_amount,2)],
'TOTAL_OUTSTANDING': [lv_total_tenure_outstanding],
}
)
], ignore_index=True
)
temp_mv_balance_df = mv_balance_df.copy()
if(lv_payment_excess >0):
temp_mv_balance_df = pd.concat([temp_mv_balance_df, pd.DataFrame(
{ 'BALANCE_CODE': "CREDIT",
'POSTED': [0],
'PAID': [0],
'ADJ_PLUS': [0],
'ADJ_MINUS': [0],
'WAIVE': [0],
'PAID_WITH_EXCESS': [lv_payment_excess],
'REFUND':[0],
'OUTSTANDING': [lv_payment_excess*-1]
})],
ignore_index=True)
temp_mv_balance_df.loc['Total']=temp_mv_balance_df.sum()
temp_mv_balance_df.loc[temp_mv_balance_df.index[-1], 'BALANCE_CODE'] = ''
mv_txn_details_by_bill.append({
'DUE_GENERATION_DATE': lv_due_generation_dt,
'TXN_DETAILS': lv_txn_details,
'BALANCE_DETAILS': temp_mv_balance_df
})
lv_txn_details = []
lv_index += 1
lv_total_tenure_outstanding = 0
elif(lv_setup_record_type == "IGNORE"):
print("Ignore txns record - "+lv_balance_code)
elif(lv_setup_record_type == "EXCESS"):
lv_has_excess_payment = True
lv_payment_excess += lv_txn_row['TXN_AMT']*-1
elif(lv_setup_record_type == "PAYMENT"):
lv_total_payment += lv_txn_row['TXN_AMT']
if(lv_payment_excess >0):
mv_balance_df = pd.concat([mv_balance_df, pd.DataFrame(
{ 'BALANCE_CODE': "CREDIT",
'POSTED': [0],
'PAID': [0],
'ADJ_PLUS': [0],
'ADJ_MINUS': [0],
'WAIVE': [0],
'PAID_WITH_EXCESS': [lv_payment_excess],
'REFUND':[0],
'OUTSTANDING': [lv_payment_excess*-1]
})],
ignore_index=True)
mv_balance_df.loc['Total']= mv_balance_df.sum()
mv_balance_df.loc[mv_balance_df.index[-1], 'BALANCE_CODE'] = ''
return mv_balance_df,mv_due_date_history_df, mv_txn_details_by_bill, lv_total_posted_txns, lv_total_payment, lv_total_due_amt, lv_total_payment_appropriated, lv_total_adj_minus, lv_total_waived, lv_total_refund, lv_has_excess_payment, lv_has_charged_off
# Main Program
def main():
# -- Streamlit Settings
st.set_page_config("Billing Summarizer",layout="wide")
st.header("Billing Summarizer πŸ’")
st.text("")
st.text("")
st.text("")
# -- Display Processing Details
mv_processing_message = st.empty()
st.text("")
st.text("")
# -- Input Upload
with st.sidebar:
st.header("Upload Files")
st.text("")
lv_setup_csv = st.file_uploader("Setup File", type=["csv"])
st.text("")
st.text("")
lv_txn_csv = st.file_uploader("Txn File", type=["csv"])
# -- Generate Bill Summary
if st.button("Generate Bill Summary"):
if lv_setup_csv != None and lv_txn_csv != None:
fn_display_user_messages("Processing Input Files","Success",mv_processing_message)
mv_setup_df = pd.read_csv(lv_setup_csv, sep=',')
mv_txn_df = pd.read_csv(lv_txn_csv, sep=',')
mv_balance_df = pd.DataFrame(columns=['BALANCE_CODE','POSTED','PAID','ADJ_PLUS','ADJ_MINUS','WAIVE','PAID_WITH_EXCESS','REFUND','OUTSTANDING'])
mv_due_date_history_df = pd.DataFrame(columns=['DUE_GENERATION_DATE','DUE_AMOUNT','ADDITIONAL_CHARGES','TOTAL_OUTSTANDING'])
with st.spinner("Generating response..."):
mv_balance_df,mv_due_date_history_df, mv_txn_details_by_bill, lv_total_posted_txns, lv_total_payment, lv_total_due_amt, lv_total_payment_appropriated, lv_total_adj_minus, lv_total_waived, lv_total_refund, lv_has_excess_payment, lv_has_charged_off = fn_generate_data(mv_setup_df, mv_txn_df, mv_balance_df, mv_due_date_history_df)
lv_summary = f"""
#### Summary:
- Total Txns Posted = **{round(lv_total_posted_txns,2)}**.
- Total Due Amounts = **{round(lv_total_due_amt,2)}**.
- Total Payment Received = **{round(lv_total_payment,2)}**.
- Total Payment Appropriated = **{round(lv_total_payment_appropriated,2)}**.
- Total Adjust Minus = **{round(lv_total_adj_minus,2)}**.
- Total Waived = **{round(lv_total_waived,2)}**.
- Difference of Payments = **{round(round(lv_total_payment,2) - round(lv_total_payment_appropriated,2),2)}**.
- Estimated Payoff Amount = **{round(round(lv_total_posted_txns,2) - round(lv_total_payment,2) - round(lv_total_adj_minus,2) - round(lv_total_waived,2) + round(lv_total_refund,2),2)}**.
"""
with st.container(border=True):
if lv_has_excess_payment:
lv_summary += f"""
- Account has Excess Payment Transaction.
"""
if lv_has_charged_off:
lv_summary += f"""
- Account is Charged off. Consider below as chargeoff balances
"""
st.markdown(lv_summary)
with st.expander("Balance Details"):
st.dataframe(mv_balance_df, use_container_width=True)
with st.expander("Due Date History"):
st.dataframe(mv_due_date_history_df, use_container_width=True)
with st.container(border=True):
st.subheader("Txn & Balance details By Due Date")
st.text("")
st.text("")
for row in mv_txn_details_by_bill:
with st.expander("Due Dt - "+str(row['DUE_GENERATION_DATE'])):
st.dataframe(row['BALANCE_DETAILS'], use_container_width=True)
st.dataframe(row['TXN_DETAILS'], use_container_width=True)
fn_display_user_messages("File Processing Completed Successfully","Success",mv_processing_message)
else:
fn_display_user_messages("Upload Input Files","Error",mv_processing_message)
# Loading Main
if __name__ == "__main__":
main()