import arrow import calendar import time from decimal import Decimal from datetime import datetime, timedelta from Schemas import BuildDecisionBookPayments, AccountRecords, ApiEnumDropdown, Build, BuildDecisionBook # from ServicesApi.Schemas.building.decision_book import BuildDecisionBookPayments, BuildDecisionBook # from ServicesApi.Schemas.building.build import Build # from ServicesApi.Schemas.account.account import AccountRecords # from ServicesApi.Schemas.others.enums import ApiEnumDropdown from time import perf_counter from sqlalchemy import select, func, distinct, cast, Date, String, literal, desc, and_, or_, case from Controllers.Postgres.engine import get_session_factory def find_last_day_of_month(date_value): today = date_value.date() _, last_day = calendar.monthrange(today.year, today.month) return datetime(today.year, today.month, last_day, 23, 59, 59) def find_first_day_of_month(date_value): today = date_value.date() return datetime(today.year, today.month, 1) def add_month_to_date(date_value): month_to_process = date_value.month year_to_process = date_value.year if date_value.month == 12: month_to_process = 1 year_to_process += 1 else: month_to_process += 1 _, last_day = calendar.monthrange(year_to_process, month_to_process) return datetime(year_to_process, month_to_process, 1), datetime(year_to_process, month_to_process, last_day) class BuildDuesTypes: def __init__(self): self.debit: ApiEnumDropdownShallowCopy = None self.add_debit: ApiEnumDropdownShallowCopy = None self.renovation: ApiEnumDropdownShallowCopy = None self.lawyer_expence: ApiEnumDropdownShallowCopy = None self.service_fee: ApiEnumDropdownShallowCopy = None self.information: ApiEnumDropdownShallowCopy = None class ApiEnumDropdownShallowCopy: id: int uuid: str enum_class: str key: str value: str def __init__(self, id: int, uuid: str, enum_class: str, key: str, value: str): self.id = id self.uuid = uuid self.enum_class = enum_class self.key = key self.value = value def get_enums_from_database(): build_dues_types = BuildDuesTypes() with ApiEnumDropdown.new_session() as session: ApiEnumDropdown.set_session(session) debit_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-D").first() # Debit add_debit_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-A").first() # Add Debit renovation_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-R").first() # Renovation late_payment_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-L").first() # Lawyer expence service_fee_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-S").first() # Service fee information_enum_shallow = ApiEnumDropdown.query.filter_by(enum_class="BuildDuesTypes", key="BDT-I").first() # Information build_dues_types.debit = ApiEnumDropdownShallowCopy( debit_enum_shallow.id, str(debit_enum_shallow.uu_id), debit_enum_shallow.enum_class, debit_enum_shallow.key, debit_enum_shallow.value ) build_dues_types.add_debit = ApiEnumDropdownShallowCopy( add_debit_enum_shallow.id, str(add_debit_enum_shallow.uu_id), add_debit_enum_shallow.enum_class, add_debit_enum_shallow.key, add_debit_enum_shallow.value ) build_dues_types.renovation = ApiEnumDropdownShallowCopy( renovation_enum_shallow.id, str(renovation_enum_shallow.uu_id), renovation_enum_shallow.enum_class, renovation_enum_shallow.key, renovation_enum_shallow.value ) build_dues_types.lawyer_expence = ApiEnumDropdownShallowCopy( late_payment_enum_shallow.id, str(late_payment_enum_shallow.uu_id), late_payment_enum_shallow.enum_class, late_payment_enum_shallow.key, late_payment_enum_shallow.value ) build_dues_types.service_fee = ApiEnumDropdownShallowCopy( service_fee_enum_shallow.id, str(service_fee_enum_shallow.uu_id), service_fee_enum_shallow.enum_class, service_fee_enum_shallow.key, service_fee_enum_shallow.value ) build_dues_types.information = ApiEnumDropdownShallowCopy( information_enum_shallow.id, str(information_enum_shallow.uu_id), information_enum_shallow.enum_class, information_enum_shallow.key, information_enum_shallow.value ) return [build_dues_types.debit, build_dues_types.lawyer_expence, build_dues_types.add_debit, build_dues_types.renovation, build_dues_types.service_fee, build_dues_types.information] def retrieve_remainder_balance_set_if_needed(account_record_id: int, session): """Update the remainder_balance of an account after spending money. Args: account_record: The account record to update amount_spent: The amount spent in this transaction session: Database session Returns: bool: True if all money is spent, False otherwise """ AccountRecords.set_session(session) account_record_remainder_balance = session.query(func.sum(func.abs(BuildDecisionBookPayments.payment_amount))).filter( BuildDecisionBookPayments.account_records_id == account_record_id, BuildDecisionBookPayments.account_is_debit == False ).scalar() if not account_record_remainder_balance: account_record_remainder_balance = 0 account_record = AccountRecords.query.filter_by(id=account_record_id).first() account_record.remainder_balance = -1 * abs(account_record_remainder_balance) if account_record_remainder_balance != 0 else 0 account_record.save() return account_record_remainder_balance def check_current_debt_to_pay_from_database_is_closed(ref_id: int, session): session.commit() BuildDecisionBookPayments.set_session(session) payment_row_book = BuildDecisionBookPayments.query.filter(BuildDecisionBookPayments.ref_id == str(ref_id), BuildDecisionBookPayments.account_is_debit == True).first() debt_paid = session.query(func.sum(func.abs(BuildDecisionBookPayments.payment_amount))).filter( BuildDecisionBookPayments.ref_id == str(ref_id), BuildDecisionBookPayments.account_is_debit == False ).scalar() payment_row_book.debt_paid = abs(debt_paid) if debt_paid else 0 # Debt Reminder is how much money is needed to close record payment_row_book.debt_to_pay = abs(payment_row_book.payment_amount) - abs(payment_row_book.debt_paid) # Debt To Pay is how much money is needed to close record payment_row_book.is_closed = payment_row_book.debt_to_pay == 0 payment_row_book.save() def close_payment_book(payment_row_book, account_record, value, session): """Create a credit entry in BuildDecisionBookPayments to close a debt. Args: payment_row_book: The debit entry to be paid account_record: The account record containing the funds value: The amount to pay session: Database session Returns: The newly created payment record """ BuildDecisionBookPayments.set_session(session) account_record_remainder_balance = retrieve_remainder_balance_set_if_needed(account_record_id=account_record.id, session=session) print(f'NOT Updated remainder balance: {account_record_remainder_balance} | Account record id: {account_record.id}') # Create a new credit entry (payment) new_row = BuildDecisionBookPayments.create( ref_id=str(payment_row_book.uu_id), payment_plan_time_periods=payment_row_book.payment_plan_time_periods, period_time=payment_row_book.period_time, currency=payment_row_book.currency, account_records_id=account_record.id, account_records_uu_id=str(account_record.uu_id), build_parts_id=payment_row_book.build_parts_id, build_parts_uu_id=str(payment_row_book.build_parts_uu_id), payment_amount=abs(value), # Negative for credit entries payment_types_id=payment_row_book.payment_types_id, payment_types_uu_id=str(payment_row_book.payment_types_uu_id), process_date_m=payment_row_book.process_date.month, process_date_y=payment_row_book.process_date.year, process_date=payment_row_book.process_date, build_decision_book_item_id=payment_row_book.build_decision_book_item_id if payment_row_book.build_decision_book_item_id else None, build_decision_book_item_uu_id=str(payment_row_book.build_decision_book_item_uu_id) if payment_row_book.build_decision_book_item_uu_id else None, decision_book_project_id=payment_row_book.decision_book_project_id if payment_row_book.decision_book_project_id else None, decision_book_project_uu_id=str(payment_row_book.decision_book_project_uu_id) if payment_row_book.decision_book_project_uu_id else None, build_decision_book_id=payment_row_book.build_decision_book_id if payment_row_book.build_decision_book_id else None, build_decision_book_uu_id=str(payment_row_book.build_decision_book_uu_id) if payment_row_book.build_decision_book_uu_id else None, is_confirmed=True, account_is_debit=False, ) # Save the new payment record saved_row = new_row.save() session.commit() session.refresh(saved_row) account_record_remainder_balance = retrieve_remainder_balance_set_if_needed(account_record_id=account_record.id, session=session) print(f'Updated remainder balance: {account_record_remainder_balance} | Account record id: {account_record.id}') check_current_debt_to_pay_from_database_is_closed(ref_id=new_row.ref_id, session=session) return account_record_remainder_balance def refresh_ids_to_iterate_account_records(session, limit, offset): return session.query(AccountRecords.id).filter( AccountRecords.active == True, func.abs(AccountRecords.currency_value) - func.abs(AccountRecords.remainder_balance) > 0, AccountRecords.currency_value > 0, ).order_by(AccountRecords.bank_date.desc()).limit(limit).offset(offset).all() def get_ids_to_iterate_account_records(session, limit, offset): """Get account records to process with pagination. Args: session: Database session limit: Maximum number of records to return offset: Number of records to skip Returns: List of account record IDs """ today = datetime.now() return session.query(AccountRecords.id).filter( AccountRecords.active == True, func.abs(AccountRecords.currency_value) - func.abs(AccountRecords.remainder_balance) > 0, AccountRecords.currency_value > 0, AccountRecords.build_parts_id.isnot(None), cast(AccountRecords.bank_date, Date) < find_first_day_of_month(today).date() ).order_by(AccountRecords.bank_date.desc()).limit(limit).offset(offset).all() def get_read_payment_type(payment_result_type_uu_id, payment_type_list): for payment_type in payment_type_list: if payment_type.uuid == payment_result_type_uu_id: return payment_type.key return None def same_month_actions(limit=10, offset=0): """Main function to process account records and find debits. Args: limit: Maximum number of records to process offset: Number of records to skip """ today, build_id, start_time = datetime.now(), 1, perf_counter() session_factory = get_session_factory() session = session_factory() payment_type_list = get_enums_from_database() print(f"Processing with limit={limit}, offset={offset}") account_records = get_ids_to_iterate_account_records(session=session, limit=limit, offset=offset) print(f"Found {len(account_records)} account records to process") for account_record in account_records: AccountRecords.set_session(session) BuildDecisionBookPayments.set_session(session) account_record_selected = AccountRecords.query.filter_by(id=account_record.id).first() payment_result_type_uu_id = account_record_selected.payment_result_type_uu_id # Extract month and year from the bank transaction date instead of using current date bank_date = account_record_selected.bank_date bank_month = bank_date.month bank_year = bank_date.year priority_uuids = [payment_result_type_uu_id, *[pt.uuid for pt in payment_type_list if pt.uuid != payment_result_type_uu_id]] order_payment_by_type = case(*[(BuildDecisionBookPayments.payment_types_uu_id == uuid, index) for index, uuid in enumerate(priority_uuids)], else_=len(priority_uuids)) same_month_build_decision_book_debits = BuildDecisionBookPayments.query.filter( BuildDecisionBookPayments.account_is_debit.is_(True), BuildDecisionBookPayments.active.is_(True), BuildDecisionBookPayments.build_parts_id == account_record_selected.build_parts_id, func.extract('month', BuildDecisionBookPayments.process_date) == bank_month, func.extract('year', BuildDecisionBookPayments.process_date) == bank_year, BuildDecisionBookPayments.is_closed.is_(False), # BuildDecisionBookPayments.payment_types_uu_id == payment_result_type_uu_id, ).order_by(order_payment_by_type, BuildDecisionBookPayments.process_date.desc()).all() match_payment_type = get_read_payment_type(payment_result_type_uu_id, payment_type_list) print('fund', dict( id=account_record_selected.id, build_parts_id=account_record_selected.build_parts_id, payment_result_type_uu_id=match_payment_type, bank_date=arrow.get(account_record_selected.bank_date).format('YYYY-MM-DD HH:mm:ss'), currency_value=account_record_selected.currency_value, remainder_balance=account_record_selected.remainder_balance, length_matches=len(same_month_build_decision_book_debits), )) for same_month_build_decision_book_debit in same_month_build_decision_book_debits: match_payment_type = get_read_payment_type(same_month_build_decision_book_debit.payment_types_uu_id, payment_type_list) print('debt', dict( id=same_month_build_decision_book_debit.id, payment_type=match_payment_type, debt_date=arrow.get(same_month_build_decision_book_debit.process_date).format('YYYY-MM-DD HH:mm:ss'), payment_amount=same_month_build_decision_book_debit.payment_amount, debt_paid=same_month_build_decision_book_debit.debt_paid, debt_to_pay=same_month_build_decision_book_debit.debt_to_pay, is_closed=same_month_build_decision_book_debit.is_closed, )) if __name__ == "__main__": import sys # Default values limit = 10 offset = 0 # Parse command line arguments if len(sys.argv) > 1: try: limit = int(sys.argv[1]) except ValueError: print(f"Error: Invalid limit value '{sys.argv[1]}'. Using default limit={limit}") if len(sys.argv) > 2: try: offset = int(sys.argv[2]) except ValueError: print(f"Error: Invalid offset value '{sys.argv[2]}'. Using default offset={offset}") same_month_actions(limit=limit, offset=offset)