import gradio as gr import pandas as pd import gspread from google.oauth2.service_account import Credentials import json import os from datetime import datetime, timedelta import plotly.graph_objects as go import plotly.express as px from io import BytesIO from reportlab.lib import colors from reportlab.lib.pagesizes import letter, A4 from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib.units import inch import tempfile from analytics_tab import AnalyticsTab from delete_tab import DeleteTab # Create downloads directory if it doesn't exist DOWNLOADS_DIR = os.path.join(os.getcwd(), "downloads") os.makedirs(DOWNLOADS_DIR, exist_ok=True) # Google Sheets Setup SCOPES = ['https://www.googleapis.com/auth/spreadsheets'] def format_indian_currency(amount): """Format number in Indian currency format (1,00,000)""" try: amount = float(amount) s = f"{abs(amount):.2f}" integer_part, decimal_part = s.split('.') # Indian numbering system if len(integer_part) <= 3: formatted = integer_part else: last_three = integer_part[-3:] remaining = integer_part[:-3] # Add commas every 2 digits for the remaining part formatted_remaining = "" for i, digit in enumerate(reversed(remaining)): if i > 0 and i % 2 == 0: formatted_remaining = "," + formatted_remaining formatted_remaining = digit + formatted_remaining formatted = formatted_remaining + "," + last_three result = f"{formatted}.{decimal_part}" return f"-{result}" if amount < 0 else result except: return str(amount) def parse_date(date_str): """Parse date in DD-MM-YY format to YYYY-MM-DD""" try: # Try DD-MM-YY format first parts = date_str.strip().split('-') if len(parts) == 3: day, month, year = parts # Handle 2-digit year if len(year) == 2: year = f"20{year}" return f"{year}-{month.zfill(2)}-{day.zfill(2)}" except: pass return date_str def format_date_display(date_str): """Format date from YYYY-MM-DD to DD-MM-YY for display""" try: date_obj = datetime.strptime(date_str, "%Y-%m-%d") return date_obj.strftime("%d-%m-%y") except: return date_str def get_google_sheets_client(): """Initialize Google Sheets client""" try: # Get credentials from environment variable creds_json = os.environ.get('GOOGLE_SHEETS_CREDENTIALS') if not creds_json: raise ValueError("GOOGLE_SHEETS_CREDENTIALS not found in environment") creds_dict = json.loads(creds_json) creds = Credentials.from_service_account_info(creds_dict, scopes=SCOPES) client = gspread.authorize(creds) return client except Exception as e: raise Exception(f"Failed to initialize Google Sheets client: {str(e)}") def get_sheet(): """Get the Google Sheet""" client = get_google_sheets_client() sheet_id = os.environ.get('SHEET_ID') if not sheet_id: raise ValueError("SHEET_ID not found in environment") return client.open_by_key(sheet_id) def initialize_sheets(): """Initialize sheets with headers if empty""" try: spreadsheet = get_sheet() # Initialize Transactions sheet try: trans_sheet = spreadsheet.worksheet("Transactions") except: trans_sheet = spreadsheet.add_worksheet(title="Transactions", rows="1000", cols="10") if not trans_sheet.row_values(1): trans_sheet.update('A1:F1', [['Date', 'Farmer Name', 'Bank Account', 'Amount', 'Type', 'Timestamp']]) # Initialize Metadata sheet try: meta_sheet = spreadsheet.worksheet("Metadata") except: meta_sheet = spreadsheet.add_worksheet(title="Metadata", rows="1000", cols="10") if not meta_sheet.row_values(1): meta_sheet.update('A1:B1', [['Type', 'Value']]) meta_sheet.update('A2:B2', [['farmer', '']]) meta_sheet.update('A3:B3', [['bank', '']]) return True except Exception as e: print(f"Error initializing sheets: {str(e)}") return False def get_farmers(): """Get list of registered farmers""" try: spreadsheet = get_sheet() meta_sheet = spreadsheet.worksheet("Metadata") data = meta_sheet.get_all_values() farmers = [] for row in data: if len(row) >= 2 and row[0] == 'farmer' and row[1]: farmers.extend([f.strip() for f in row[1].split(',') if f.strip()]) return sorted(list(set(farmers))) except Exception as e: print(f"Error getting farmers: {str(e)}") return [] def get_banks(): """Get list of registered bank accounts""" try: spreadsheet = get_sheet() meta_sheet = spreadsheet.worksheet("Metadata") data = meta_sheet.get_all_values() banks = [] for row in data: if len(row) >= 2 and row[0] == 'bank' and row[1]: banks.extend([b.strip() for b in row[1].split(',') if b.strip()]) return sorted(list(set(banks))) except Exception as e: print(f"Error getting banks: {str(e)}") return [] def refresh_farmers(): """Refresh farmer dropdown""" return gr.Dropdown(choices=get_farmers()) def refresh_banks(): """Refresh bank dropdown""" return gr.Dropdown(choices=get_banks()) def register_farmer(farmer_name): """Register a new farmer""" try: if not farmer_name or not farmer_name.strip(): return "❌ Please enter a farmer name" farmer_name = farmer_name.strip() farmers = get_farmers() if farmer_name in farmers: return f"❌ Farmer '{farmer_name}' is already registered" spreadsheet = get_sheet() meta_sheet = spreadsheet.worksheet("Metadata") data = meta_sheet.get_all_values() # Find the farmer row farmer_row = None for idx, row in enumerate(data): if len(row) >= 1 and row[0] == 'farmer': farmer_row = idx + 1 break if farmer_row: current_farmers = data[farmer_row - 1][1] if len(data[farmer_row - 1]) > 1 else "" new_farmers = f"{current_farmers},{farmer_name}" if current_farmers else farmer_name meta_sheet.update_cell(farmer_row, 2, new_farmers) return f"✅ Farmer '{farmer_name}' registered successfully!" except Exception as e: return f"❌ Error registering farmer: {str(e)}" def register_bank(bank_name): """Register a new bank account""" try: if not bank_name or not bank_name.strip(): return "❌ Please enter a bank account name" bank_name = bank_name.strip() banks = get_banks() if bank_name in banks: return f"❌ Bank account '{bank_name}' is already registered" spreadsheet = get_sheet() meta_sheet = spreadsheet.worksheet("Metadata") data = meta_sheet.get_all_values() # Find the bank row bank_row = None for idx, row in enumerate(data): if len(row) >= 1 and row[0] == 'bank': bank_row = idx + 1 break if bank_row: current_banks = data[bank_row - 1][1] if len(data[bank_row - 1]) > 1 else "" new_banks = f"{current_banks},{bank_name}" if current_banks else bank_name meta_sheet.update_cell(bank_row, 2, new_banks) return f"✅ Bank account '{bank_name}' registered successfully!" except Exception as e: return f"❌ Error registering bank: {str(e)}" def add_transaction(date, farmer_name, bank_account, amount, trans_type): """Add a new transaction""" try: if not all([date, farmer_name, bank_account, amount, trans_type]): return "❌ Please fill in all fields" farmer_name = farmer_name.strip() bank_account = bank_account.strip() # Check if farmer exists farmers = get_farmers() if farmer_name not in farmers: return f"❌ Farmer '{farmer_name}' not found. Please register the farmer first or check spelling." # Check if bank exists banks = get_banks() if bank_account not in banks: return f"❌ Bank account '{bank_account}' not found. Please register the bank first." try: amount = float(amount) except: return "❌ Amount must be a valid number" # Parse and convert date formatted_date = parse_date(date) # Format amount based on type if trans_type == "Outgoing (Lent to Farmer)": amount = -abs(amount) else: # Incoming amount = abs(amount) spreadsheet = get_sheet() trans_sheet = spreadsheet.worksheet("Transactions") timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S") trans_sheet.append_row([formatted_date, farmer_name, bank_account, amount, trans_type, timestamp]) return f"✅ Transaction added successfully! Amount: ₹{format_indian_currency(amount)}" except Exception as e: return f"❌ Error adding transaction: {str(e)}" def calculate_interest(principal, start_date, end_date, interest_rate=2.0): """Calculate monthly interest (default 2%) prorated daily""" try: start = datetime.strptime(start_date, "%Y-%m-%d") end = datetime.strptime(end_date, "%Y-%m-%d") days = (end - start).days # interest_rate% per month = interest_rate/30 per day daily_rate = (interest_rate / 100) / 30 interest = principal * daily_rate * days return interest except: return 0 def create_pdf_report(farmer_name, df, total_lent, total_returned, interest_accrued, remaining_balance): """Generate PDF report of transaction history""" try: # Create PDF with custom name in current directory pdf_filename = f"{farmer_name.replace(' ', '_')}_transactions_with_interest.pdf" # Remove old file if exists if os.path.exists(pdf_filename): os.remove(pdf_filename) doc = SimpleDocTemplate(pdf_filename, pagesize=letter, rightMargin=30, leftMargin=30, topMargin=30, bottomMargin=18) elements = [] styles = getSampleStyleSheet() # Title title_style = ParagraphStyle( 'CustomTitle', parent=styles['Heading1'], fontSize=24, textColor=colors.HexColor('#1f77b4'), spaceAfter=30, alignment=1 ) elements.append(Paragraph(f"Transaction History: {farmer_name}", title_style)) elements.append(Spacer(1, 12)) # Summary statistics summary_data = [ ['Metric', 'Amount (₹)'], ['Total Lent', format_indian_currency(total_lent)], ['Total Returned', format_indian_currency(total_returned)], ['Interest Accrued', format_indian_currency(interest_accrued)], ['Current Balance', f'{format_indian_currency(abs(remaining_balance))} {"(Farmer owes you)" if remaining_balance < 0 else "(You owe farmer)"}'] ] summary_table = Table(summary_data, colWidths=[3*inch, 3*inch]) summary_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f77b4')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 14), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.beige), ('GRID', (0, 0), (-1, -1), 1, colors.black), ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 1), (-1, -1), 12), ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.lightgrey]), ])) elements.append(summary_table) elements.append(Spacer(1, 20)) # Transaction history title elements.append(Paragraph("Detailed Transaction History", styles['Heading2'])) elements.append(Spacer(1, 12)) # Transaction table with highlighted interest rows trans_data = [['Date', 'Description', 'Bank Account', 'Amount (₹)', 'Balance (₹)']] for _, row in df.iterrows(): trans_data.append([ format_date_display(str(row['Date'])), str(row['Description']), str(row['Bank Account']), format_indian_currency(row['Amount']), format_indian_currency(row['Balance']) ]) trans_table = Table(trans_data, colWidths=[1.2*inch, 1.8*inch, 1.5*inch, 1.2*inch, 1.2*inch]) # Base style table_style = [ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#2ca02c')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('GRID', (0, 0), (-1, -1), 1, colors.black), ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 1), (-1, -1), 8), ] # Highlight interest rows in red for idx, row in df.iterrows(): row_num = idx + 1 # +1 because header is row 0 if 'Interest Accrued' in str(row['Description']): table_style.append(('BACKGROUND', (0, row_num), (-1, row_num), colors.Color(1, 0.8, 0.8))) table_style.append(('TEXTCOLOR', (0, row_num), (-1, row_num), colors.red)) trans_table.setStyle(TableStyle(table_style)) elements.append(trans_table) # Build PDF doc.build(elements) # Verify file was created and get its absolute path abs_path = os.path.abspath(pdf_filename) if os.path.exists(abs_path): file_size = os.path.getsize(abs_path) print(f"PDF created successfully: {abs_path}, Size: {file_size} bytes") return abs_path else: print(f"PDF file not found at: {abs_path}") return None except Exception as e: print(f"Error creating PDF: {str(e)}") import traceback traceback.print_exc() return None def get_farmer_report(farmer_name, end_date=None, interest_rate=None): """Generate complete report for a farmer with interest calculations and PDF""" try: if not farmer_name or not farmer_name.strip(): return None, None, "❌ Please enter a farmer name", None, None farmer_name = farmer_name.strip() # Set default interest rate if not provided if interest_rate is None or interest_rate == "": interest_rate = 2.0 else: try: interest_rate = float(interest_rate) except: return None, None, "❌ Interest rate must be a valid number", None, None # Set end date if end_date and end_date.strip(): try: end_date_parsed = parse_date(end_date) end_date_obj = datetime.strptime(end_date_parsed, "%Y-%m-%d") except: return None, None, "❌ Invalid date format. Please use DD-MM-YY", None, None else: end_date_obj = datetime.now() end_date_str = end_date_obj.strftime("%Y-%m-%d") spreadsheet = get_sheet() trans_sheet = spreadsheet.worksheet("Transactions") data = trans_sheet.get_all_values() if len(data) <= 1: return None, None, f"❌ No transactions found for farmer '{farmer_name}'", None, None # Create DataFrame df = pd.DataFrame(data[1:], columns=data[0]) df = df[df['Farmer Name'] == farmer_name] if df.empty: return None, None, f"❌ No transactions found for farmer '{farmer_name}'", None, None df['Amount'] = pd.to_numeric(df['Amount'], errors='coerce') df['Date'] = pd.to_datetime(df['Date'], errors='coerce') # Filter transactions up to end_date df = df[df['Date'] <= end_date_obj] if df.empty: return None, None, f"❌ No transactions found for farmer '{farmer_name}' up to {format_date_display(end_date_str)}", None, None df = df.sort_values('Date') # Calculate running balance with interest principal_due = 0 # actual loan outstanding total_interest = 0 # accumulated interest total_lent = 0 total_returned = 0 last_date = None surplus_to_farmer = 0 # if farmer paid extra transactions_with_interest = [] for idx, row in df.iterrows(): amount = row['Amount'] current_date = row['Date'].strftime("%Y-%m-%d") # 1) Apply interest ONLY on principal_due (only when farmer owes us) if principal_due > 0 and last_date: interest = calculate_interest(principal_due, last_date, current_date, interest_rate) total_interest += interest principal_due += interest transactions_with_interest.append({ 'Date': current_date, 'Description': 'Interest Accrued', 'Bank Account': '-', 'Amount': -interest, 'Balance': -principal_due }) # 2) Process transaction if amount < 0: # We lent money lend = abs(amount) principal_due += lend total_lent += lend else: # Farmer paid money payment = amount total_returned += payment if payment <= principal_due: principal_due -= payment else: surplus_to_farmer += (payment - principal_due) principal_due = 0 # Record transaction transactions_with_interest.append({ 'Date': current_date, 'Description': row['Type'], 'Bank Account': row['Bank Account'], 'Amount': amount, 'Balance': -principal_due if principal_due > 0 else surplus_to_farmer }) last_date = current_date # if amount < 0: # total_lent += abs(amount) # else: # total_returned += amount # transactions_with_interest.append({ # 'Date': current_date, # 'Description': row['Type'], # 'Bank Account': row['Bank Account'], # 'Amount': amount, # 'Balance': -principal_due if principal_due > 0 else surplus_to_farmer # }) # last_date = current_date # Calculate interest up to end date if principal_due > 0 and last_date: interest = calculate_interest(principal_due, last_date, end_date_str, interest_rate) total_interest += interest principal_due += interest transactions_with_interest.append({ 'Date': end_date_str, 'Description': f'Interest Accrued (to {format_date_display(end_date_str)})', 'Bank Account': '-', 'Amount': -interest, 'Balance': -principal_due }) # Create detailed transaction DataFrame detailed_df = pd.DataFrame(transactions_with_interest) # Format amounts in detailed_df for display detailed_df_display = detailed_df.copy() detailed_df_display['Date'] = detailed_df_display['Date'].apply(format_date_display) detailed_df_display['Amount'] = detailed_df_display['Amount'].apply(format_indian_currency) detailed_df_display['Balance'] = detailed_df_display['Balance'].apply(format_indian_currency) # Summary statistics remaining_balance = ( -principal_due if principal_due > 0 else surplus_to_farmer ) interest_accrued = total_interest remaining_balance = ( -principal_due if principal_due > 0 else surplus_to_farmer ) # Create summary HTML summary_html = f"""

📊 Summary for {farmer_name}

Report calculated up to: {format_date_display(end_date_str)} | Interest Rate: {interest_rate}% per month

💰 Total Lent ₹{format_indian_currency(total_lent)}
💵 Total Returned ₹{format_indian_currency(total_returned)}
📈 Interest Accrued ₹{format_indian_currency(interest_accrued)}
🔢 Current Balance ₹{format_indian_currency(abs(remaining_balance))}
{'(Farmer owes you)' if remaining_balance < 0 else '(You owe farmer)'}
""" # Create visualizations bank_dist = df.groupby('Bank Account')['Amount'].apply(lambda x: abs(x[x < 0].sum())).reset_index() bank_dist.columns = ['Bank Account', 'Amount Lent'] fig_bank = go.Figure(go.Bar( x=bank_dist['Amount Lent'], y=bank_dist['Bank Account'], orientation='h', marker_color='lightblue', text=bank_dist['Amount Lent'].apply(lambda x: f'₹{format_indian_currency(x)}'), textposition='auto' )) fig_bank.update_layout( title=f"Money Lent to {farmer_name} by Bank Account", xaxis_title="Amount (₹)", yaxis_title="Bank Account", height=350, font=dict(size=12) ) fig_pie = go.Figure(data=[go.Pie( labels=['Returned', 'Outstanding (with interest)'], values=[total_returned, abs(remaining_balance)], marker_colors=['#27ae60', '#e74c3c'], textinfo='label+percent+value', texttemplate='%{label}
₹' + format_indian_currency(total_returned) + '
(%{percent})' )]) fig_pie.update_layout( title=f"Payment Status for {farmer_name}", height=350, font=dict(size=12) ) # Generate PDF with original numeric values pdf_path = create_pdf_report(farmer_name, detailed_df, total_lent, total_returned, interest_accrued, remaining_balance) return detailed_df_display, pdf_path, summary_html, fig_bank, fig_pie except Exception as e: import traceback traceback.print_exc() return None, None, f"❌ Error generating report: {str(e)}", None, None def clear_farmer_report(): """Clear all report outputs""" return None, None, "", None, None # Initialize sheets on startup initialize_sheets() # Initialize Analytics Tab analytics = AnalyticsTab(get_sheet, get_farmers, format_indian_currency, format_date_display) delete_tab = DeleteTab(get_sheet, get_farmers, format_indian_currency, format_date_display) # Gradio Interface with gr.Blocks(title="Farmer Ledger Tracker", theme=gr.themes.Soft()) as app: gr.Markdown("# 🌾 Farmer Ledger Tracker") gr.Markdown("Track loans to farmers with customizable monthly interest (default 2%, prorated daily)") with gr.Tabs(): # Tab 1: Add Transactions with gr.Tab("📝 Add Transaction"): gr.Markdown("## Add New Transaction") gr.Markdown("Enter transaction details below (Date format: DD-MM-YY):") with gr.Row(): trans_date = gr.Textbox(label="Date (DD-MM-YY)", placeholder="15-01-24", scale=1) with gr.Row(): trans_farmer = gr.Dropdown(label="Farmer Name", choices=[], allow_custom_value=True, scale=9) refresh_farmer_btn_tab1 = gr.Button("🔄", scale=1, size="sm") with gr.Row(): with gr.Row(): trans_bank = gr.Dropdown(label="Bank Account", choices=[], allow_custom_value=True, scale=9) refresh_bank_btn_tab1 = gr.Button("🔄", scale=1, size="sm") trans_amount = gr.Number(label="Amount (₹)", value=0, scale=1) trans_type = gr.Radio( label="Transaction Type", choices=["Outgoing (Lent to Farmer)", "Incoming (Farmer Paid Back)"], value="Outgoing (Lent to Farmer)" ) add_trans_btn = gr.Button("Add Transaction", variant="primary", size="lg") trans_status = gr.Textbox(label="Status", interactive=False) gr.Markdown("---") # Register entities section - smaller and less prominent with gr.Accordion("⚙️ Register New Farmer or Bank Account", open=False): gr.Markdown("### Register New Entities") gr.Markdown("*Use this section to add new farmers or bank accounts to the system*") with gr.Row(): with gr.Column(): new_farmer_input = gr.Textbox(label="New Farmer Name", placeholder="Enter farmer name", scale=1) register_farmer_btn = gr.Button("Register Farmer", size="sm") farmer_status = gr.Textbox(label="Status", interactive=False, scale=1) with gr.Column(): new_bank_input = gr.Textbox(label="New Bank Account", placeholder="Enter bank name", scale=1) register_bank_btn = gr.Button("Register Bank Account", size="sm") bank_status = gr.Textbox(label="Status", interactive=False, scale=1) # Event handlers for Tab 1 refresh_farmer_btn_tab1.click( fn=refresh_farmers, outputs=[trans_farmer] ) refresh_bank_btn_tab1.click( fn=refresh_banks, outputs=[trans_bank] ) register_farmer_btn.click( fn=register_farmer, inputs=[new_farmer_input], outputs=[farmer_status] ) register_bank_btn.click( fn=register_bank, inputs=[new_bank_input], outputs=[bank_status] ) add_trans_btn.click( fn=add_transaction, inputs=[trans_date, trans_farmer, trans_bank, trans_amount, trans_type], outputs=[trans_status] ) # Tab 2: View Reports with gr.Tab("📊 Farmer Reports"): gr.Markdown("## Generate Farmer Report") with gr.Row(): report_farmer = gr.Dropdown( label="Select Farmer", choices=[], allow_custom_value=True, scale=9 ) refresh_farmer_btn_tab2 = gr.Button("🔄", scale=1, size="sm") gr.Markdown("### Optional: Customize Report Parameters") gr.Markdown("*Leave blank to use current date and 2% default interest rate*") with gr.Row(): report_end_date = gr.Textbox( label="Calculate up to Date (DD-MM-YY)", placeholder="Leave blank for today", scale=1 ) report_interest_rate = gr.Number( label="Interest Rate (% per month)", value=2.0, placeholder="2.0", scale=1 ) with gr.Row(): generate_report_btn = gr.Button("Generate Report", variant="primary", size="lg") clear_report_btn = gr.Button("Clear Report", variant="secondary", size="lg") # Summary section report_summary = gr.HTML() # Download PDF with gr.Row(): pdf_download = gr.File(label="📄 Download Transaction History PDF", interactive=False) gr.Markdown("---") # Charts with gr.Row(): bank_chart = gr.Plot(label="Distribution by Bank Account") pie_chart = gr.Plot(label="Payment Status") gr.Markdown("---") # Transaction table report_df = gr.Dataframe( label="Detailed Transaction History (with Interest Calculations)", interactive=False, wrap=True ) # Event handlers for Tab 2 refresh_farmer_btn_tab2.click( fn=refresh_farmers, outputs=[report_farmer] ) generate_report_btn.click( fn=get_farmer_report, inputs=[report_farmer, report_end_date, report_interest_rate], outputs=[report_df, pdf_download, report_summary, bank_chart, pie_chart] ) clear_report_btn.click( fn=clear_farmer_report, inputs=[], outputs=[report_df, pdf_download, report_summary, bank_chart, pie_chart] ) # Tab 3: Analytics & Insights analytics.create_tab() delete_tab.create_tab() if __name__ == "__main__": app.launch()