budget_tool.py

# Copyright (c) 2023, New Indictranstech and contributors
# For license information, please see license.txt

import frappe
import json
from frappe.model.document import Document
from pradan.pradan.utils import *
from frappe.utils import get_absolute_url

class BudgetTool(Document):
    #Not saving this document to avoid modified error when more than one persons/sessions are doing same action in this page
    def validate(self):
        frappe.throw('You were not able to do this action. Please try again.')

@frappe.whitelist()
def create_budget_if_not_exists(project):
    '''
        Method to create Budget if it is not created
    '''
    budget_id = get_budget_from_project(project)
    if not budget_id:
        budget_doc = frappe.new_doc('Pradan Budget')
        budget_doc.project = project
        budget_doc.save()
        budget_id = budget_doc.name

    #Preparing HTML Tables
    budget_doc = frappe.get_doc('Pradan Budget', budget_id)
    data = []
    allocation_status = 'Draft'
    workflow_state = budget_doc.workflow_state
    if budget_doc.docstatus:
        allocation_status='Submitted'
    budget_items = budget_doc.ledger_mapping

    #Defining Columns
    columns = ['BL Code', 'BLI', 'SBL Code', 'SBLI']
    year = 1
    columns.append('Project Years Maximum')
    for year_wise in budget_doc.year_wise_breakup:
        columns.append('Project Year {0}'.format(year))
        year += 1
    columns.append('Project Total')
    columns.append('Check')
    columns.append('FY Maximum')
    for fy_year in budget_doc.fy_wise_breakup:
        columns.append(fy_year.fiscal_year)
    columns.append('FY Total')
    columns.append('FY Check')
    columns.append('Budget Note')

    #Defining Data rows
    previous_budget_line_item = budget_items[0].budget_line_item
    for budget_item in budget_items:
        if previous_budget_line_item != budget_item.budget_line_item:
            sub_total_row = get_sub_total_row(budget_id, previous_budget_line_item)
            data.append(sub_total_row)
            previous_budget_line_item = budget_item.budget_line_item
        row = get_budget_item_details(project, budget_item)
        data.append(row)
    if budget_items:
        sub_total_row = get_sub_total_row(budget_id, budget_items[-1].budget_line_item)
        data.append(sub_total_row)

    total_row = get_total_row(project)

    html_data = frappe.render_template('pradan/doctype/budget_tool/budget_tool.html', {
        'columns': columns,
        'data': data,
        'status': allocation_status,
        'total_row': total_row,
        'workflow_state': workflow_state
    });
    return {
        'html':html_data,
        'budget_id': budget_doc.name,
        'status': allocation_status,
        'workflow_state': budget_doc.workflow_state,
        'budget_owner': budget_doc.owner
    }

def get_budget_item_details(project, budget_item):
    budget_id = get_budget_from_project(project)
    budget_line_item = budget_item.budget_line_item
    sub_budget_item = budget_item.sub_budget_line_item
    fy_max = budget_item.fy_maximum
    year_max = budget_item.year_maximum
    budget_notes = budget_item.budget_notes or ''
    data = []

    #Set comon data
    data.append({ 'type':'text', 'value': budget_line_item, 'read_only':1, 'primary': 1, 'ref_link': get_absolute_url('Budget Item', budget_line_item) })
    data.append({ 'type':'text', 'value': frappe.db.get_value('Budget Item', budget_line_item, 'budget_item_name'), 'read_only':1, 'primary': 1 })
    data.append({ 'type':'text', 'value': sub_budget_item, 'read_only':1, 'primary': 1, 'ref_link': get_absolute_url('Budget Item', sub_budget_item) })
    data.append({ 'type':'text', 'value': frappe.db.get_value('Budget Item', sub_budget_item, 'budget_item_name'), 'read_only':1, 'primary': 1 })

    #Get FY wise data
    query = '''
        SELECT
            fiscal_year,
            available_amount
        FROM
            `tabPradan Budget Items`
        WHERE
            parent = '{0}' AND
            sub_budget_item = '{1}'
        ORDER BY
            idx ASC
    '''
    budget_item_details = frappe.db.sql(query.format(budget_id, sub_budget_item), as_dict=1)
    if budget_item_details:
        #Get year wise data
        query = '''
            SELECT
                sub_budget_item,
                available_amount
            FROM
                `tabYear wise Allocation`
            WHERE
                parent = '{0}' AND
                sub_budget_item = '{1}'
            ORDER BY
                idx ASC
        '''
        year_wise_budget = frappe.db.sql(query.format(budget_id, sub_budget_item), as_dict=1)
        year_total = 0
        bg_color = '#ffffff'
        data.append({ 'type':'number', 'value': int(year_max), 'read_only':0, 'class_name':'text-right year_max', 'bg_color':bg_color })
        for row in year_wise_budget:
            year_total += row.available_amount
            data.append({ 'type':'number', 'value': int(row.available_amount), 'read_only':0, 'class_name':'text-right year_input', 'bg_color':bg_color })
        year_balance = year_max - year_total
        data.append({ 'type':'number', 'value': int(year_total), 'read_only':1, 'class_name':'text-right year_total', 'bg_color':bg_color })
        data.append({ 'type':'number', 'value': int(year_balance), 'read_only':1, 'class_name':'text-right year_balance', 'bg_color':bg_color })
        fy_total = 0
        data.append({ 'type':'number', 'value': int(fy_max), 'read_only':1, 'class_name':'text-right fy_max', 'bg_color':bg_color })
        for row in budget_item_details:
            if is_allocated(budget_id, row.fiscal_year, sub_budget_item):
                bg_color = '#23cd4a'
            else:
                bg_color = '#ffffff'
            fy_total += row.available_amount
            fy_read_only = is_period_closing_voucher_created(row.fiscal_year)
            data.append({ 'type':'number', 'value': int(row.available_amount), 'read_only':fy_read_only, 'class_name':'text-right fy_input', 'bg_color':bg_color })
        fy_balance = fy_max - fy_total
        data.append({ 'type':'number', 'value': int(fy_total), 'read_only':1, 'class_name':'text-right fy_total', 'bg_color':bg_color })
        data.append({ 'type':'number', 'value': int(fy_balance), 'read_only':1, 'class_name':'text-right fy_balance', 'bg_color':bg_color })
        data.append({ 'type':'text', 'value': budget_notes, 'read_only':0, 'class_name':'budget_notes', 'bg_color':bg_color })
        allocation_details = get_allocation_status(project, sub_budget_item)
        allocated = allocation_details.get('allocated')
        allocation_message = allocation_details.get('message') or ''
        if 'Budget Admin (HO)' not in frappe.get_roles(frappe.session.user):
            allocated = 1
            allocation_message = "You don't have permission to Allocate"
        data.append({ 'type':'button', 'allocated':allocated, 'message':allocation_message })
    return data

@frappe.whitelist()
def save_budget_data(project, data):
    budget_id = get_budget_from_project(project)
    ledger_mapping = frappe.db.get_all('Ledger Mapping', filters={ 'parent': budget_id}, fields=['name', 'parent', 'sub_budget_line_item'], order_by='idx asc')
    budget_items_count = len(ledger_mapping)
    year_wise_breakup = frappe.db.get_all('Year wise Breakup', filters={ 'parent': budget_id}, fields=['name', 'parent', 'from_date', 'to_date'], order_by='idx asc')
    years = len(year_wise_breakup)
    fy_wise_breakup = frappe.db.get_all('FY wise Breakup', filters={ 'parent': budget_id}, fields=['name', 'parent', 'fiscal_year'], order_by='idx asc')
    fys = len(fy_wise_breakup)
    data = json.loads(data)
    idx = 0
    for row in data:
        sub_budget_item = ledger_mapping[idx].sub_budget_line_item
        year_wise_budget = []
        fy_wise_budget = []
        year_max = 0
        fy_max = 0
        budget_notes = ''
        if years:
            #Set year wise available_amount
            year_max = row[5]
            j = 6
            for year_wise in year_wise_breakup:
                amount = row[j] or 0
                update_year_wise_allocation_id(budget_id, sub_budget_item, year_wise.from_date, year_wise.to_date, float(amount))
                j += 1

            #Set FY wise available_amount
            if fys:
                fy_max = row[8+years]
                k = 9+years
                for fy_wise in fy_wise_breakup:
                    amount = row[k] or 0
                    update_fy_wise_allocation_id(budget_id, sub_budget_item, fy_wise.fiscal_year, float(amount))
                    k += 1
                budget_notes = row[k+2]

        #set maximum for fy and year wise, budget notes
        row_id = ledger_mapping[idx].name
        frappe.db.set_value('Ledger Mapping', row_id, 'year_maximum', year_max)
        frappe.db.set_value('Ledger Mapping', row_id, 'fy_maximum', fy_max)
        frappe.db.set_value('Ledger Mapping', row_id, 'budget_notes', budget_notes)
        idx += 1

    #To trigger toal calculation at databasebudget_item_details
    budget_doc = frappe.get_doc('Pradan Budget', budget_id)
    budget_doc.save()
    return 1

@frappe.whitelist()
def submit_form(project):
    budget_id = get_budget_from_project(project)
    if budget_id:
        budget_doc = frappe.get_doc('Pradan Budget', budget_id)
        budget_doc.submit()
        return 1
    else:
        return 0

@frappe.whitelist()
def get_allocation_data(project, sub_budget_item):
    '''
        Method to get remaining Fiscal Years and teams that need to be allocated
    '''
    fiscal_years = []
    available_amount = []
    labels = []
    budget_id = get_budget_from_project(project)
    if frappe.db.exists('Pradan Budget Items', { 'parent':budget_id, 'sub_budget_item':sub_budget_item }):
        fiscal_years = frappe.db.get_all('Pradan Budget Items', { 'parent':budget_id, 'sub_budget_item':sub_budget_item }, pluck='fiscal_year', order_by='fiscal_year asc')
        available_amount = frappe.db.get_all('Pradan Budget Items', { 'parent':budget_id, 'sub_budget_item':sub_budget_item }, pluck='available_amount', order_by='fiscal_year asc')
        allocation_status = frappe.db.get_all('Pradan Budget Items', { 'parent':budget_id, 'sub_budget_item':sub_budget_item }, pluck='allocated', order_by='fiscal_year asc')
        for status in allocation_status:
            label = 'Reallocate' if status else 'Allocate'
            labels.append(label)
    return { 'fiscal_years':fiscal_years, 'available_amount':available_amount, 'labels':labels }

@frappe.whitelist()
def get_allocation_status(project, sub_budget_item):
    '''
        Method will return 1 if budget allocation is done for all fiscal years else return 0. Also Budget should be Submitted
    '''
    budget_id = get_budget_from_project(project)
    workflow_state = frappe.db.get_value('Pradan Budget', budget_id, 'workflow_state')
    if workflow_state=='Approved':
        message = "Already allocated"
    else:
        message = "Budget not Approved"
    if workflow_state=='Approved':
        return { 'allocated':0, 'message':message }
    else:
        return { 'allocated':1, 'message':message }

@frappe.whitelist()
def get_total_row(project):
    budget_id = get_budget_from_project(project)
    budget_doc = frappe.get_doc('Pradan Budget', budget_id)
    total_row = []
    # Setting primary values
    total_row.append({ 'value':'' })
    total_row.append({ 'value':'' })
    total_row.append({ 'value':'' })
    total_row.append({ 'value':'Total' })
    total_row.append({ 'value':'' })

    # Setting totals for years
    total_row.append({ 'value': int(budget_doc.total_year_maximum) })
    for row in budget_doc.year_wise_breakup:
        total_row.append({ 'value': int(row.amount) })
    total_row.append({ 'value': int(budget_doc.year_wise_total) })
    total_row.append({ 'value': int(budget_doc.total_year_maximum - budget_doc.year_wise_total) })

    # Setting totals for fys
    total_row.append({ 'value': int(budget_doc.total_fy_maximum) })
    for row in budget_doc.fy_wise_breakup:
        total_row.append({ 'value': int(row.amount) })
    total_row.append({ 'value': int(budget_doc.fy_wise_total) })
    total_row.append({ 'value': int(budget_doc.total_fy_maximum - budget_doc.fy_wise_total) })
    return total_row

def update_fy_wise_allocation_id(budget_id, sub_budget_item, fy, amount):
    '''
        Method to get id of `allocations` child table row
    '''
    if frappe.db.exists('Pradan Budget Items', { 'parent':budget_id, 'sub_budget_item':sub_budget_item , 'fiscal_year':fy }):
        row_id = frappe.db.get_value('Pradan Budget Items', { 'parent':budget_id, 'sub_budget_item':sub_budget_item , 'fiscal_year':fy })
        frappe.db.set_value('Pradan Budget Items', row_id, 'available_amount', amount)

def update_year_wise_allocation_id(budget_id, sub_budget_item, from_date, to_date, amount):
    '''
        Method to get id of `year_wise_allocation` child table row
    '''
    if frappe.db.exists('Year wise Allocation', { 'parent':budget_id, 'sub_budget_item':sub_budget_item , 'from_date':from_date, 'to_date':to_date }):
        row_id = frappe.db.get_value('Year wise Allocation', { 'parent':budget_id, 'sub_budget_item':sub_budget_item , 'from_date':from_date, 'to_date':to_date })
        frappe.db.set_value('Year wise Allocation', row_id, 'available_amount', amount)

def get_sub_total_row(budget_id, budget_line_item):
    '''
        Method to get Sub Total row for each budget line item
    '''
    budget_doc = frappe.get_doc('Pradan Budget', budget_id)
    sub_total_row = []
    sub_total_row.append({ 'type':'text', 'value': '', 'read_only':1, 'class_name':'sub_total', 'style':'border-right: 0 !important;' })
    sub_total_row.append({ 'type':'text', 'value': '', 'read_only':1, 'class_name':'sub_total', 'style':'border-left: 0 !important; border-right: 0 !important;' })
    sub_total_row.append({ 'type':'text', 'value': '', 'read_only':1, 'class_name':'sub_total', 'style':'border-left: 0 !important; border-right: 0 !important;' })
    sub_total_row.append({ 'type':'text', 'value': 'Sub Total', 'read_only':1, 'class_name':'sub_total', 'style':'border-left: 0 !important;' })

    #Year wise total
    year_totals = get_budget_item_wise_year_total(budget_id, budget_line_item)
    year_maximum = year_totals.get('year_maximum') or 0
    year_total = year_totals.get('year_total') or 0
    year_balance = year_totals.get('year_balance') or 0
    sub_total_row.append({ 'value': int(year_maximum), 'read_only':1, 'class_name':'text-right sub_total' })
    for row in budget_doc.year_wise_breakup:
        amount = get_budget_item_wise_total_for_an_year(budget_id, budget_line_item, row.from_date, row.to_date)
        sub_total_row.append({ 'value': int(amount), 'read_only':1, 'class_name':'text-right sub_total' })
    sub_total_row.append({ 'value': int(year_total), 'read_only':1, 'class_name':'text-right sub_total' })
    sub_total_row.append({ 'value': int(year_balance), 'read_only':1, 'class_name':'text-right sub_total' })

    #FY wise total
    fy_totals = get_budget_item_wise_fy_total(budget_id, budget_line_item)
    fy_maximum = fy_totals.get('fy_maximum') or 0
    fy_total = fy_totals.get('fy_total') or 0
    fy_balance = fy_totals.get('fy_balance') or 0
    sub_total_row.append({ 'value': int(fy_maximum), 'read_only':1, 'class_name':'text-right sub_total' })
    for row in budget_doc.fy_wise_breakup:
        amount = get_budget_item_wise_total_for_an_fy(budget_id, budget_line_item, row.fiscal_year)
        sub_total_row.append({ 'value': int(amount), 'read_only':1, 'class_name':'text-right sub_total' })
    sub_total_row.append({ 'value': int(fy_total), 'read_only':1, 'class_name':'text-right sub_total' })
    sub_total_row.append({ 'value': int(fy_balance), 'read_only':1, 'class_name':'text-right sub_total' })
    return sub_total_row

def get_budget_item_wise_year_total(budget_id, budget_line_item):
    '''
        Method to get maximum, total and balance year value for a budget item
    '''
    year_maximum, year_total, year_balance = 0, 0, 0
    query = '''
        SELECT
            SUM(year_maximum) as year_maximum,
            SUM(year_total) as year_total
        FROM
            `tabLedger Mapping`
        WHERE
            parent = '{0}' AND
            budget_line_item = '{1}'
    '''
    result = frappe.db.sql(query.format(budget_id, budget_line_item), as_dict=1)
    if result:
        year_maximum = result[0].get('year_maximum') or 0
        year_total = result[0].get('year_total') or 0
        year_balance = year_maximum - year_total
    return { 'year_maximum': year_maximum, 'year_total': year_total, 'year_balance': year_balance }

def get_budget_item_wise_fy_total(budget_id, budget_line_item):
    '''
        Method to get maximum, total and balance fy value for a budget item
    '''
    fy_maximum, fy_total, fy_balance = 0, 0, 0
    query = '''
        SELECT
            SUM(fy_maximum) as fy_maximum,
            SUM(fy_total) as fy_total
        FROM
            `tabLedger Mapping`
        WHERE
            parent = '{0}' AND
            budget_line_item = '{1}'
    '''
    result = frappe.db.sql(query.format(budget_id, budget_line_item), as_dict=1)
    if result:
        fy_maximum = result[0].get('fy_maximum') or 0
        fy_total = result[0].get('fy_total') or 0
        fy_balance = fy_maximum - fy_total
    return { 'fy_maximum': fy_maximum, 'fy_total': fy_total, 'fy_balance': fy_balance }

def get_budget_item_wise_total_for_an_fy(budget_id, budget_line_item, fiscal_year):
    '''
        Method to get total availability for an fiscal year
    '''
    total_amount = 0
    query = '''
        SELECT
            SUM(available_amount) as total_amount
        FROM
            `tabPradan Budget Items`
        WHERE
            parent = '{0}' AND
            budget_item = '{1}' AND
            fiscal_year = '{2}'
    '''
    result = frappe.db.sql(query.format(budget_id, budget_line_item, fiscal_year), as_dict=1)
    if result:
        total_amount = result[0].get('total_amount') or 0
    return total_amount

def get_budget_item_wise_total_for_an_year(budget_id, budget_line_item, from_date, to_date):
    '''
        Method to get total availability for an year
    '''
    total_amount = 0
    query = '''
        SELECT
            SUM(available_amount) as total_amount
        FROM
            `tabYear wise Allocation`
        WHERE
            parent = '{0}' AND
            budget_item = '{1}' AND
            from_date = '{2}' AND
            to_date = '{3}'
    '''
    result = frappe.db.sql(query.format(budget_id, budget_line_item, from_date, to_date), as_dict=1)
    if result:
        total_amount = result[0].get('total_amount') or 0
    return total_amount

def is_allocated(budget_id, fiscal_year, sub_budget_item):
    '''
        Method to check wether allocation is done or not
    '''
    allocated_amount = 0
    query = '''
        SELECT
            bali.allocated_amount as allocated_amount
        FROM
            `tabBudget Allocation Line Item` as bali,
            `tabBudget Allocation` as ba
        WHERE
            bali.parent = ba.name AND
            ba.pradan_budget = %(budget_id)s AND
            ba.fiscal_year = %(fiscal_year)s AND
            bali.sub_budget_line_item = %(sub_budget_item)s
    '''
    result = frappe.db.sql(query, { 'budget_id':budget_id, 'fiscal_year':fiscal_year, 'sub_budget_item':sub_budget_item }, as_dict=1)
    if result:
        if result[0]:
            if result[0].get('allocated_amount'):
                return 1
    return 0

def is_period_closing_voucher_created(fiscal_year):
    '''
        Method to check whether Period Closing Voucher is created or not
    '''
    period_closing_voucher_created = 0
    if frappe.db.exists('Period Closing Voucher', { 'fiscal_year':fiscal_year, 'docstatus':1 }):
        period_closing_voucher_created = 1
    return period_closing_voucher_created