Spaces:
Sleeping
Sleeping
File size: 17,582 Bytes
0672540 acc078a 0672540 1e87919 f48b9a1 1e87919 acc078a ca5766c f3a03d8 0672540 b600303 1e87919 acc078a 0672540 1e87919 0672540 acc078a 0672540 acc078a a15a6d1 acc078a 0672540 1e87919 0672540 a15a6d1 f3a03d8 a15a6d1 2ad24bd a15a6d1 acc078a 2ad24bd 0672540 2ad24bd 9b62bd4 0672540 1e87919 acc078a a15a6d1 1e87919 acc078a a15a6d1 1e87919 0672540 4abf268 0672540 f48b9a1 0672540 4abf268 0672540 b600303 2ad24bd a15a6d1 2ad24bd b600303 0672540 1e87919 0672540 1e87919 0672540 1e87919 acc078a 1e87919 0672540 acc078a a15a6d1 acc078a 2ad24bd 4abf268 f3a03d8 0672540 acc078a 9627998 0672540 a15a6d1 0672540 f3a03d8 acc078a 1e87919 acc078a f3a03d8 1e87919 acc078a 1e87919 acc078a 0672540 e5a42bb 0672540 e5a42bb b600303 ca5766c b600303 8fe9118 0672540 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 |
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() |