from .models import FixedAsset from django.utils import timezone from datetime import timedelta, date from dateutil.relativedelta import relativedelta from .serializers import FixedAssetSerializer from ledgers.models import SystemTransactions from ledgers.ledgers_helper import * from django.db import connection # from datetime import date # from django.utils import timezone def depreciation(asset_id, organisation_id, user_id, branch_id): # Fetch the fixed asset and its depreciation rate fixed_asset = FixedAsset.objects.select_related('depreciation_rate').filter(deleted=False, pk=asset_id).first() if not fixed_asset: print("Asset not found.") return None depreciation_rate = fixed_asset.depreciation_rate # Current date current_date = timezone.now() # Last depreciation date last_depreciation_date = fixed_asset.last_depreciation if timezone.is_naive(last_depreciation_date): last_depreciation_date = timezone.make_aware(last_depreciation_date) # Calculate the next depreciation date based on frequency if fixed_asset.frequency == 'Annually': next_depreciation_date = last_depreciation_date + relativedelta(years=1) elif fixed_asset.frequency == 'Quarterly': next_depreciation_date = last_depreciation_date + relativedelta(months=3) elif fixed_asset.frequency == 'Monthly': next_depreciation_date = last_depreciation_date + relativedelta(months=1) elif fixed_asset.frequency == 'Bi-Weekly': next_depreciation_date = last_depreciation_date + timedelta(weeks=2) elif fixed_asset.frequency == 'Weekly': next_depreciation_date = last_depreciation_date + timedelta(weeks=1) elif fixed_asset.frequency == 'Daily': next_depreciation_date = last_depreciation_date + timedelta(days=1) else: print("Invalid depreciation frequency.") return None # Check if the depreciation period has passed if current_date >= next_depreciation_date: # Check if the depreciated amount equals the asset amount if fixed_asset.depreciated_amount >= fixed_asset.amount: print("Depreciated amount is equal to or greater than the asset amount. No depreciation applied.") return None amount_depreciated = round((depreciation_rate.rate / 100) * fixed_asset.amount, 2) fixed_asset.depreciated_amount = fixed_asset.depreciated_amount + amount_depreciated fixed_asset.last_depreciation = next_depreciation_date heading = "Depreciating "+fixed_asset.name reference_no = fixed_asset.reference_no+'-'+generate_reference_no('exp', organisation_id, 'exp') debitCredit= SystemTransactions(amount=amount_depreciated, heading="Depreciating", record_date=current_date,reference_no=reference_no, debit_chart_id=fixed_asset.deprecation_dr_acct_id, credit_chart_id=fixed_asset.deprecation_cr_acct_id, branch_id=branch_id, added_by=user_id) debitCredit.save() serializer = FixedAssetSerializer(fixed_asset, data={'depreciated_amount': fixed_asset.depreciated_amount}, partial=True) if serializer.is_valid(): serializer.save() print("Depreciation applied and saved.") return True else: print("Failed to save depreciation: ", serializer.errors) else: print("Depreciation period has not yet passed.") # def get_fixed_asset_report(start_date: date, end_date: date): # with connection.cursor() as cursor: # cursor.execute(f"""SELECT F.id, F.name AS asset_name, F.description AS asset_desc, # F.amount, F.payment_method, D.description, D.rate, # D.frequency, d.deleted AS dep_rate_deleted, F.deleted AS asset_deleted, # F.date AS asset_date, F.last_depreciation, F.reference_no, F.cheque_no, F.voucher_no, # (SELECT COALESCE(SUM(amount),0) FROM fixed_asset WHERE id = F.id AND date::DATE < {start_date}) AS asset_sum_at_start_date, # (SELECT COALESCE(SUM(amount),0) FROM fixed_asset WHERE id = F.id AND (date::DATE >= {start_date} AND date::DATE <= {end_date})) AS asset_addition, # (SELECT COALESCE(SUM(previous_value - current_value),0) FROM depreciation_history WHERE asset_id_id = F.id AND created_at::DATE < {start_date}) dep_sum_as_at_start_date, # (SELECT COALESCE(SUM(previous_value - current_value),0) FROM depreciation_history WHERE asset_id_id = F.id AND (created_at::DATE >= {start_date} AND created_at::DATE <= {end_date})) dep_sum_change_of_period # FROM fixed_asset F INNER JOIN depreciation_rate D ON D.id = F.depreciation_rate_id """) # # cursor.execute(""" # # WITH asset_sums AS ( # # SELECT # # id, # # SUM(CASE WHEN date::DATE < %s THEN amount ELSE 0 END) AS asset_sum_at_start_date, # # SUM(CASE WHEN date::DATE BETWEEN %s AND %s THEN amount ELSE 0 END) AS asset_addition # # FROM fixed_asset # # GROUP BY id # # ), # # depreciation_sums AS ( # # SELECT # # asset_id_id, # # SUM(CASE WHEN created_at::DATE < %s THEN previous_value - current_value ELSE 0 END) AS dep_sum_as_at_start_date, # # SUM(CASE WHEN created_at::DATE BETWEEN %s AND %s THEN previous_value - current_value ELSE 0 END) AS dep_sum_change_of_period # # FROM depreciation_history # # GROUP BY asset_id_id # # ) # # SELECT # # F.id, # # F.name AS asset_name, # # F.description AS asset_desc, # # F.amount, # # F.payment_method, # # D.description, # # D.rate, # # D.frequency, # # D.deleted AS dep_rate_deleted, # # F.deleted AS asset_deleted, # # F.date AS asset_date, # # F.last_depreciation, # # F.reference_no, # # F.cheque_no, # # F.voucher_no, # # COALESCE(ASUM.asset_sum_at_start_date, 0), # # COALESCE(ASUM.asset_addition, 0), # # COALESCE(DSUM.dep_sum_as_at_start_date, 0), # # COALESCE(DSUM.dep_sum_change_of_period, 0) # # FROM fixed_asset F # # INNER JOIN depreciation_rate D ON D.id = F.depreciation_rate_id # # LEFT JOIN asset_sums ASUM ON ASUM.id = F.id # # LEFT JOIN depreciation_sums DSUM ON DSUM.asset_id_id = F.id # # WHERE F.deleted = FALSE AND D.deleted = FALSE; # # """, [start_date, start_date, end_date, start_date, start_date, end_date]) # columns = [col[0] for col in cursor.description] # return [dict(zip(columns, row)) for row in cursor.fetchall()] def get_fixed_asset_report(start_date: date, end_date: date, branch_id: int): with connection.cursor() as cursor: cursor.execute(""" SELECT F.id, F.name AS asset_name, F.description AS asset_desc, F.amount, F.payment_method, D.description, D.rate, D.frequency, D.deleted AS dep_rate_deleted, F.deleted AS asset_deleted, F.date AS asset_date, F.last_depreciation, F.reference_no, F.cheque_no, F.voucher_no, (SELECT COALESCE(SUM(amount), 0) FROM fixed_asset WHERE id = F.id AND date::DATE < %s) AS asset_sum_at_start_date, (SELECT COALESCE(SUM(amount), 0) FROM fixed_asset WHERE id = F.id AND date::DATE BETWEEN %s AND %s) AS asset_addition, (SELECT COALESCE(SUM(previous_value - current_value), 0) FROM depreciation_history WHERE asset_id_id = F.id AND created_at::DATE < %s) AS dep_sum_as_at_start_date, (SELECT COALESCE(SUM(previous_value - current_value), 0) FROM depreciation_history WHERE asset_id_id = F.id AND created_at::DATE BETWEEN %s AND %s) AS dep_sum_change_of_period FROM fixed_asset F INNER JOIN depreciation_rate D ON D.id = F.depreciation_rate_id WHERE F.branch_id = %s AND F.deleted = false AND D.deleted = false """, [start_date, start_date, end_date, start_date, start_date, end_date, branch_id]) columns = [col[0] for col in cursor.description] return [dict(zip(columns, row)) for row in cursor.fetchall()]