pradan_budget.py

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

import frappe
import json
from pradan.pradan.utils import *
from frappe.utils import getdate, add_years, add_days
from frappe.model.document import Document

class PradanBudget(Document):
    def validate(self):
        if self.project:
            is_able_to_create_budget(self.project, 1)
        self.check_duplicate_projects()
        self.calculate_totals()
        self.validate_totals()

    def after_insert(self):
        self.set_initial_values()
        self.generate_allocation_rows()
        self.generate_staff_and_project_wise_salary()

    def before_submit(self):
        self.set_allocations()

    def on_update_after_submit(self):
        self.set_allocations()
        self.calculate_totals()
        self.validate_totals()

    def set_allocations(self):
        for row in self.allocations:
            budget_item_entry = create_or_update_budget_item_entry(row)
            if not row.budget_item_entry:
                row.budget_item_entry = budget_item_entry
                row.db_set('budget_item_entry', budget_item_entry)

    def on_trash(self):
        for row in self.allocations:
            if row.budget_item_entry:
                frappe.db.delete('Pradan Budget Item Entry', row.budget_item_entry)
        frappe.db.commit()

    def on_submit(self):
        if not self.generated:
            frappe.throw('Set availability before submit')

    def check_duplicate_projects(self):
        budget_id = get_budget_from_project(self.project)
        if budget_id and budget_id!= self.name:
            frappe.throw('Budget already created for this Project')

    @frappe.whitelist()
    def generate_allocation_rows(self):
        self.allocations = []
        for fy_year in self.fy_wise_breakup:
            for ledger_row in self.ledger_mapping:
                row = self.append('allocations')
                row.project = self.project
                row.budget_item = ledger_row.budget_line_item
                row.sub_budget_item = ledger_row.sub_budget_line_item
                row.fiscal_year = fy_year.fiscal_year
                row.available_amount = 0
        self.year_wise_allocation = []
        for year_wise_alloc in self.year_wise_breakup:
            for ledger_row in self.ledger_mapping:
                row = self.append('year_wise_allocation')
                row.project = self.project
                row.budget_item = ledger_row.budget_line_item
                row.sub_budget_item = ledger_row.sub_budget_line_item
                row.from_date = year_wise_alloc.from_date
                row.to_date = year_wise_alloc.to_date
                row.available_amount = 0
        self.generated = 1
        self.save()

    def set_initial_values(self):
        if self.project:
            teams = get_project_teams(self.project)
            self.teams = []
            for team in teams:
                self.append('teams', {'teams':team})
            budget_items = get_budget_items(self.project)
            self.ledger_mapping = []
            for budget_item in budget_items:
                row = self.append('ledger_mapping')
                row.budget_line_item = budget_item.budget_line_item
                row.sub_budget_line_item = budget_item.sub_budget_line_item
                row.year_maximum = 0
                row.fy_maximum = 0
                row.year_total = 0
                row.fy_total = 0

        if self.year_wise_breakdown_count:
            year_wise_breakups = get_year_wise_breakup_from_project(self.project)
            self.year_wise_breakup = []
            for year_wise_breakup in year_wise_breakups:
                self.append('year_wise_breakup',{
                    'from_date': year_wise_breakup.from_date,
                    'to_date': year_wise_breakup.to_date,
                    'amount': 0
                })
        if self.project_start_date and self.project_end_date:
            fiscal_years = get_fiscal_years(self.project_start_date, self.project_end_date)
            self.fy_wise_breakup = []
            for fy_year in fiscal_years:
                row = self.append('fy_wise_breakup')
                row.fiscal_year = fy_year
                row.amount = 0

    def calculate_fy_totals(self):
        fy_total = 0
        for row in self.fy_wise_breakup:
            amount = get_fy_total(self.name, row.fiscal_year)
            row.amount = amount
            row.db_set('amount', amount)
            fy_total += amount
        self.fy_wise_total = fy_total
        self.fy_wise_balance = self.budget_size - fy_total
        self.db_set('fy_wise_total', fy_total)
        self.db_set('fy_wise_balance', self.budget_size - fy_total)

    def calculate_year_totals(self):
        year_total = 0
        for row in self.year_wise_breakup:
            amount = get_year_total(self.name, row.from_date, row.to_date)
            row.amount = amount
            row.db_set('amount', amount)
            year_total += amount
        self.year_wise_total = year_total
        self.year_wise_balance = self.budget_size - year_total
        self.db_set('year_wise_total', year_total)
        self.db_set('year_wise_balance', self.budget_size - year_total)

    def validate_totals(self):
        if not self.budget_size:
            frappe.throw('Please define the Budget Size at Project level.')
        if self.budget_size < self.fy_wise_total:
            frappe.throw('Total FY wise budget exceeded than Budget Size.')
        if self.budget_size < self.year_wise_total:
            frappe.throw('Total Year wise budget exceeded than Budget Size.')
        if self.budget_size < self.total_fy_maximum:
            frappe.throw('Total FY maximum budget exceeded than Budget Size.')
        if self.budget_size < self.total_year_maximum:
            frappe.throw('Total year maximum budget exceeded than Budget Size.')

    def calculate_totals(self):
        self.calculate_fy_totals()
        self.calculate_year_totals()
        values = get_year_and_fy_maximum(self.name)
        self.total_year_maximum = values.total_year_max
        self.total_fy_maximum = values.total_fy_max
        self.db_set('total_year_maximum', values.total_year_max)
        self.db_set('total_fy_maximum', values.total_fy_max)
        for row in self.ledger_mapping:
            year_total = float(get_item_wise_year_total(self.name, row.sub_budget_line_item))
            fy_total = float(get_item_wise_fy_total(self.name, row.sub_budget_line_item))
            if row.year_maximum and year_total:
                if float(row.year_maximum) < year_total:
                    frappe.throw('Total year wise budget exceeded than maximum assigned budget for `Row: {0}`'.format(row.idx))
            if row.fy_maximum and fy_total:
                if float(row.fy_maximum) < fy_total:
                    frappe.throw('Total fy wise budget exceeded than maximum assigned budget for `Row: {0}`'.format(row.idx))
            row.year_total = get_item_wise_year_total(self.name, row.sub_budget_line_item)
            row.fy_total = get_item_wise_fy_total(self.name, row.sub_budget_line_item)
            row.db_set('year_total', get_item_wise_year_total(self.name, row.sub_budget_line_item))
            row.db_set('fy_total', get_item_wise_fy_total(self.name, row.sub_budget_line_item))

    def generate_staff_and_project_wise_salary(self):
        if self.teams and self.fy_wise_breakup:
            for team in self.teams:
                for fy in self.fy_wise_breakup:
                    if not frappe.db.exists('Staff and Project wise Salary', { 'teams':team.teams, 'fiscal_year':fy.fiscal_year }):
                        staff_and_project_wise_salary = frappe.new_doc('Staff and Project wise Salary')
                        staff_and_project_wise_salary.teams = team.teams
                        staff_and_project_wise_salary.fiscal_year = fy.fiscal_year
                        staff_and_project_wise_salary.insert()

    def on_cancel(self):
        if self.workflow_state == 'Rejected' and not self.rejection_feedback:
            frappe.throw("Please add Rejection Feedback prior to Reject the Budget")

def create_or_update_budget_item_entry(args):
    '''
        Method to create or update budget item entry
    '''
    if args.budget_item_entry:
        budget_item_entry_doc = frappe.get_doc('Pradan Budget Item Entry', args.budget_item_entry)
    else:
        budget_item_entry_doc = frappe.new_doc('Pradan Budget Item Entry')
    budget_item_entry_doc.pradan_budget = args.parent
    budget_item_entry_doc.project = args.project
    budget_item_entry_doc.budget_item = args.budget_item
    budget_item_entry_doc.sub_budget_item = args.sub_budget_item
    budget_item_entry_doc.project_duration = args.project_duration
    budget_item_entry_doc.fiscal_year = args.fiscal_year
    budget_item_entry_doc.available_amount = args.available_amount
    budget_item_entry_doc.allocated_amount = 0
    budget_item_entry_doc.balance_amount = args.available_amount
    budget_item_entry_doc.flags.ignore_mandatory = True
    budget_item_entry_doc.save(ignore_permissions=True)
    return budget_item_entry_doc.name

@frappe.whitelist()
def get_team_wise_budget(project, available_amount, sub_budget_item, fiscal_year, natural_head=None):
    '''
        Method to get default team and will equaly distribute amount
    '''
    # salary_natural_heads = ['Stipend to Apprentices', 'Salary & Benefits - Contractual Staff', 'Salary & Benefits - Associates', 'Salary & Benefits - Professionals']
    budget_id = get_budget_from_project(project)
    budget_item_entry = get_budget_item_entry(budget_id, sub_budget_item, fiscal_year)
    available_amount = float(available_amount)
    data = { 'is_salary_head':0, 'team_wise_budget':[] }
    total_teams, team_amount = 0, 0
    if budget_id and budget_item_entry:
        if not frappe.db.exists('Project', project):
            frappe.throw('Project `{0}` not found'.format(project))
        project_teams = frappe.db.get_all('Project Teams', filters={ 'parent':project }, fields=['name', 'teams'])
        budget_teams = frappe.db.get_all('Team wise Budget', filters={ 'parent':budget_item_entry }, fields=['name', 'teams', 'allocated_amount', 'remarks'])

        if budget_teams:
            for team in budget_teams:
                data['team_wise_budget'].append({'project':project, 'teams': team.teams, 'allocated_amount':team.allocated_amount, 'remarks':team.remarks })
        elif project_teams:
            total_teams = len(project_teams)
            team_amount = available_amount/total_teams
            for team in project_teams:
                data['team_wise_budget'].append({'project':project, 'teams': team.teams, 'allocated_amount':team_amount, 'remarks':team.remarks })
    return data

@frappe.whitelist()
def allocate_budget_to_teams(project, sub_budget_item, fiscal_year, values):
    '''
        Method to make the string in to json and allocate to each teams
    '''
    budget_id = get_budget_from_project(project)
    budget_item_entry = get_budget_item_entry(budget_id, sub_budget_item, fiscal_year)
    data = json.loads(values)
    if not frappe.db.exists('Pradan Budget Item Entry', budget_item_entry):
        frappe.throw('Budget Item Entry does not exists')
    if data.get('team_wise_budget'):
        team_wise_budget = data.get('team_wise_budget')
        budget_item_entry_doc = frappe.get_doc('Pradan Budget Item Entry', budget_item_entry)
        old_data = budget_item_entry_doc.teams
        budget_item_entry_doc.teams = []
        idx = 0
        if old_data:
            message = '<div><p>{0} has made changes as follows</p>'.format(frappe.session.user)
        else:
            message = False
        for row in team_wise_budget:
            team_wise_budget_row = budget_item_entry_doc.append('teams')
            team_wise_budget_row.budget_code = budget_item_entry_doc.name
            team_wise_budget_row.teams = row.get('teams')
            team_wise_budget_row.allocated_amount = row.get('allocated_amount')
            team_wise_budget_row.used_amount = 0
            team_wise_budget_row.remarks = row.get('remarks')
            if old_data:
                old_amount = 0
                if len(old_data)>=idx+1:
                    old_amount = old_data[idx].get('allocated_amount')
                new_amount = row.get('allocated_amount') or 0
                teams = row.get('teams')
                message += '<p>{0}. Allocated Amount has been changed from {1} to {2} for Teams : {3}</p>'.format(idx+1, int(old_amount), int(new_amount), teams)
            idx += 1
        budget_item_entry_doc.save(ignore_permissions=True)
        if message:
            message += '</div>'
            budget_item_entry_doc.add_comment("Comment", text=message)
        mark_as_allocated(budget_item_entry)
    return 1

@frappe.whitelist()
def mark_as_allocated(budget_item_entry):
    '''
        Method to make the item row as allocated
    '''
    if frappe.db.exists('Pradan Budget Items', { 'budget_item_entry':budget_item_entry }):
        name = frappe.db.get_value('Pradan Budget Items', { 'budget_item_entry':budget_item_entry })
        frappe.db.set_value('Pradan Budget Items', name, 'allocated', 1)
        frappe.db.commit()
        frappe.msgprint(msg='Budget allocated successfully', indicator='green', alert=True)

def get_fy_total(budget_id, fiscal_year):
    '''
        Method to get total amount made availble for a fiscal year for all items
    '''
    total = 0
    query = '''
        SELECT
            IFNULL(SUM(available_amount), 0) as total
        FROM
            `tabPradan Budget Items`
        WHERE
            parent = "{0}" AND
            fiscal_year = "{1}"
    '''
    output=frappe.db.sql(query.format(budget_id, fiscal_year), as_list=1)
    if output:
        total = output[0][0]
    return total

def get_year_total(budget_id, from_date, to_date):
    '''
        Method to get total amount made availble for a fiscal year for all items
    '''
    total = 0
    query = '''
        SELECT
            IFNULL(SUM(available_amount), 0) as total
        FROM
            `tabYear wise Allocation`
        WHERE
            parent = "{0}" AND
            from_date = DATE("{1}") AND
            to_date = DATE("{2}")
    '''
    output=frappe.db.sql(query.format(budget_id, getdate(from_date), getdate(to_date)), as_list=1)
    if output:
        total = output[0][0]
    return total


def get_year_and_fy_maximum(budget_id):
    '''
        Method to get total year_max and fy_max value
    '''
    values = { 'total_year_max':0, 'fy_maximum':0 }
    query = '''
        SELECT
            IFNULL(SUM(year_maximum), 0) as total_year_max,
            IFNULL(SUM(fy_maximum), 0) as total_fy_max
        FROM
            `tabLedger Mapping`
        WHERE
            parent = "{0}"
    '''
    output=frappe.db.sql(query.format(budget_id), as_dict=1)
    if output:
        values = output[0]
    return values


def get_item_wise_fy_total(budget_id, sub_budget_item):
    '''
        Method to get FY wise total amount made availble for a sub budget item
    '''
    total = 0
    query = '''
        SELECT
            IFNULL(SUM(available_amount), 0) as total
        FROM
            `tabPradan Budget Items`
        WHERE
            parent = "{0}" AND
            sub_budget_item = "{1}"
    '''
    output=frappe.db.sql(query.format(budget_id, sub_budget_item), as_list=1)
    if output:
        total = output[0][0]
    return total

def get_item_wise_year_total(budget_id, sub_budget_item):
    '''
        Method to get year wise total amount made availble for a sub budget item
    '''
    total = 0
    query = '''
        SELECT
            IFNULL(SUM(available_amount), 0) as total
        FROM
            `tabYear wise Allocation`
        WHERE
            parent = "{0}" AND
            sub_budget_item = "{1}"
    '''
    output=frappe.db.sql(query.format(budget_id, sub_budget_item), as_list=1)
    if output:
        total = output[0][0]
    return total


@frappe.whitelist()
def get_budget_item_entry(budget_id, sub_budget_item, fiscal_year):
    '''
        Method to get Budget Item Entry for a sub budget item wrt fiscal_year
    '''
    budget_item_entry = False
    if frappe.db.exists('Pradan Budget Items', { 'parent':budget_id, 'sub_budget_item':sub_budget_item, 'fiscal_year':fiscal_year }):
        budget_item_entry = frappe.db.get_value('Pradan Budget Items', { 'parent':budget_id, 'sub_budget_item':sub_budget_item, 'fiscal_year':fiscal_year }, 'budget_item_entry')
    return budget_item_entry

@frappe.whitelist()
def is_able_to_create_budget(project, throw_error=0):
    '''
        Mehtod to check the availability to create budget from project
    '''
    able_to_create = 1
    if frappe.db.exists('Project', project):
        #Directly return 0 if budget already created
        budget_id = get_budget_from_project(project)
        if frappe.db.exists('Pradan Budget', budget_id):
            return 0
        project_size, project_start_date = frappe.db.get_value('Project', project, ['budget_size', 'project_start_date'])
        project_end_date, project_periodduration = frappe.db.get_value('Project', project, ['project_end_date', 'project_periodduration'])
        #Check project size
        if not project_size:
            if throw_error:
                frappe.throw('Please set Budget size')
            else:
                able_to_create = 0
        #Check project start date
        if not project_start_date:
            if throw_error:
                frappe.throw('Please set project start date')
            else:
                able_to_create = 0
        #Check project end date
        if not project_end_date:
            if throw_error:
                frappe.throw('Please set project end date!')
            else:
                able_to_create = 0
        #Check project duration
        if not project_periodduration:
            if throw_error:
                frappe.throw('Please set project duration!')
            else:
                able_to_create = 0
        #Check budget line items
        if not frappe.db.exists('Budget Line Items', { 'parent':project }):
            if throw_error:
                frappe.throw('Please create budget line items')
            else:
                able_to_create = 0
        #Check teams
        if not frappe.db.exists('Project Teams', { 'parent':project }):
            if throw_error:
                frappe.throw('Please set teams against this project')
            else:
                able_to_create = 0
        #Check Fiscal Years
        missing_fy = get_missing_fiscal_years(project_start_date, project_end_date)
        if missing_fy:
            if throw_error:
                frappe.throw('Please create missing fiscal years : {0}'.format(missing_fy))
            else:
                able_to_create = 0
    else:
        if throw_error:
            frappe.throw('Project `{0}` not found', project)
        else:
            able_to_create = 0
    return able_to_create

def get_year_wise_breakup_from_project(project):
    '''
        Method to get Year wise breakup from Project
    '''
    year_wise_breakup = []
    if frappe.db.exists('Year Wise Breakdown', { 'parent':project, 'parentfield':'year_wise_breakdown', 'parenttype':'Project' }):
        year_wise_breakup = frappe.db.get_all('Year Wise Breakdown', filters={ 'parent':project, 'parentfield':'year_wise_breakdown', 'parenttype':'Project' }, fields=['from_date', 'to_date'], order_by='idx asc')
    return year_wise_breakup

@frappe.whitelist()
def reject_with_feedback(budget_id, feedback):
    '''
        Method to Reject the budget with feedback
    '''
    if frappe.db.exists('Pradan Budget', budget_id) and feedback:
        frappe.db.set_value('Pradan Budget', budget_id, 'rejection_feedback', feedback)
        frappe.db.set_value('Pradan Budget', budget_id, 'workflow_state', 'Rejected')
        frappe.get_doc('Pradan Budget', budget_id).cancel()
    return 1