def get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.db.get_value("Account", filters["account"], ["lft", "rgt"])
conditions.append("""account in (select name from tabAccount
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if filters.get("cost_center"):
filters.cost_center = get_cost_centers_with_children(filters.cost_center)
conditions.append("cost_center in %(cost_center)s")
if filters.get("voucher_no"):
conditions.append("voucher_no=%(voucher_no)s")
if filters.get("group_by") == "Group by Party" and not filters.get("party_type"):
conditions.append("party_type in ('Customer', 'Supplier')")
if filters.get("party_type"):
conditions.append("party_type=%(party_type)s")
if filters.get("party"):
conditions.append("party in %(party)s")
if not (filters.get("account") or filters.get("party") or
filters.get("group_by") in ["Group by Account", "Group by Party"]):
conditions.append("posting_date >=%(from_date)s")
conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')")
if filters.get("project"):
conditions.append("project in %(project)s")
if filters.get("finance_book"):
if filters.get("include_default_book_entries"):
conditions.append("(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)")
else:
conditions.append("finance_book in (%(finance_book)s)")
if not filters.get("show_cancelled_entries"):
conditions.append("is_cancelled = 0")
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("GL Entry")
if match_conditions:
conditions.append(match_conditions)
accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
if filters.get(dimension.fieldname):
if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
filters.get(dimension.fieldname))
conditions.append("{0} in %({0})s".format(dimension.fieldname))
else:
conditions.append("{0} in (%({0})s)".format(dimension.fieldname))
return "and {}".format(" and ".join(conditions)) if conditions else ""
def merge_similar_entries(gl_map):
merged_gl_map = []
accounting_dimensions = get_accounting_dimensions()
for entry in gl_map:
# if there is already an entry in this account then just add it
# to that entry
same_head = check_if_in_list(entry, merged_gl_map,
accounting_dimensions)
if same_head:
same_head.debit = flt(same_head.debit) + flt(entry.debit)
same_head.debit_in_account_currency = \
flt(same_head.debit_in_account_currency) + flt(entry.debit_in_account_currency)
same_head.credit = flt(same_head.credit) + flt(entry.credit)
same_head.credit_in_account_currency = \
flt(same_head.credit_in_account_currency) + flt(entry.credit_in_account_currency)
else:
merged_gl_map.append(entry)
company = gl_map[0].company if gl_map else erpbee.get_default_company()
company_currency = erpbee.get_company_currency(company)
precision = get_field_precision(
frappe.get_meta("GL Entry").get_field("debit"), company_currency)
# filter zero debit and credit entries
merged_gl_map = filter(
lambda x: flt(x.debit, precision) != 0 or flt(x.credit, precision) !=
0, merged_gl_map)
merged_gl_map = list(merged_gl_map)
return merged_gl_map
def make_gl_entries(self):
gl_entries = []
net_pl_balance = 0
dimension_fields = ['t1.cost_center']
accounting_dimensions = get_accounting_dimensions()
for dimension in accounting_dimensions:
dimension_fields.append('t1.{0}'.format(dimension))
dimension_filters, default_dimensions = get_dimension_filters()
pl_accounts = self.get_pl_balances(dimension_fields)
for acc in pl_accounts:
if flt(acc.balance_in_company_currency):
gl_entries.append(self.get_gl_dict({
"account": acc.account,
"cost_center": acc.cost_center,
"account_currency": acc.account_currency,
"debit_in_account_currency": abs(flt(acc.balance_in_account_currency)) \
if flt(acc.balance_in_account_currency) < 0 else 0,
"debit": abs(flt(acc.balance_in_company_currency)) \
if flt(acc.balance_in_company_currency) < 0 else 0,
"credit_in_account_currency": abs(flt(acc.balance_in_account_currency)) \
if flt(acc.balance_in_account_currency) > 0 else 0,
"credit": abs(flt(acc.balance_in_company_currency)) \
if flt(acc.balance_in_company_currency) > 0 else 0
}, item=acc))
net_pl_balance += flt(acc.balance_in_company_currency)
if net_pl_balance:
cost_center = frappe.db.get_value("Company", self.company,
"cost_center")
gl_entry = self.get_gl_dict({
"account":
self.closing_account_head,
"debit_in_account_currency":
abs(net_pl_balance) if net_pl_balance > 0 else 0,
"debit":
abs(net_pl_balance) if net_pl_balance > 0 else 0,
"credit_in_account_currency":
abs(net_pl_balance) if net_pl_balance < 0 else 0,
"credit":
abs(net_pl_balance) if net_pl_balance < 0 else 0,
"cost_center":
cost_center
})
for dimension in accounting_dimensions:
gl_entry.update({
dimension:
default_dimensions.get(self.company, {}).get(dimension)
})
gl_entries.append(gl_entry)
from erpbee.accounts.general_ledger import make_gl_entries
make_gl_entries(gl_entries)
def get_invoice_dict(self, row=None):
def get_item_dict():
cost_center = row.get('cost_center') or frappe.get_cached_value(
'Company', self.company, "cost_center")
if not cost_center:
frappe.throw(
_("Please set the Default Cost Center in {0} company.").
format(frappe.bold(self.company)))
income_expense_account_field = "income_account" if row.party_type == "Customer" else "expense_account"
default_uom = frappe.db.get_single_value("Stock Settings",
"stock_uom") or _("Nos")
rate = flt(row.outstanding_amount) / flt(row.qty)
return frappe._dict({
"uom": default_uom,
"rate": rate or 0.0,
"qty": row.qty,
"conversion_factor": 1.0,
"item_name": row.item_name or "Opening Invoice Item",
"description": row.item_name or "Opening Invoice Item",
income_expense_account_field: row.temporary_opening_account,
"cost_center": cost_center
})
item = get_item_dict()
invoice = frappe._dict({
"items": [item],
"is_opening":
"Yes",
"set_posting_time":
1,
"company":
self.company,
"cost_center":
self.cost_center,
"due_date":
row.due_date,
"posting_date":
row.posting_date,
frappe.scrub(row.party_type):
row.party,
"is_pos":
0,
"doctype":
"Sales Invoice"
if self.invoice_type == "Sales" else "Purchase Invoice",
"update_stock":
0
})
accounting_dimension = get_accounting_dimensions()
for dimension in accounting_dimension:
invoice.update({dimension: item.get(dimension)})
return invoice
def make_gl_entries(self):
company_currency = frappe.get_cached_value('Company', self.company,
"default_currency")
gl_entries = []
invoice_fields = [
"debit_to", "party_account_currency", "conversion_rate",
"cost_center"
]
accounting_dimensions = get_accounting_dimensions()
invoice_fields.extend(accounting_dimensions)
for d in self.invoices:
inv = frappe.db.get_value("Sales Invoice",
d.sales_invoice,
invoice_fields,
as_dict=1)
if d.outstanding_amount:
outstanding_in_company_currency = flt(
d.outstanding_amount * inv.conversion_rate,
d.precision("outstanding_amount"))
ar_credit_account_currency = frappe.get_cached_value(
"Account", self.accounts_receivable_credit, "currency")
gl_entries.append(self.get_gl_dict({
"account": inv.debit_to,
"party_type": "Customer",
"party": d.customer,
"against": self.accounts_receivable_credit,
"credit": outstanding_in_company_currency,
"credit_in_account_currency": outstanding_in_company_currency \
if inv.party_account_currency==company_currency else d.outstanding_amount,
"cost_center": inv.cost_center,
"against_voucher": d.sales_invoice,
"against_voucher_type": "Sales Invoice"
}, inv.party_account_currency, item=inv))
gl_entries.append(self.get_gl_dict({
"account": self.accounts_receivable_credit,
"party_type": "Customer",
"party": d.customer,
"against": inv.debit_to,
"debit": outstanding_in_company_currency,
"debit_in_account_currency": outstanding_in_company_currency \
if ar_credit_account_currency==company_currency else d.outstanding_amount,
"cost_center": inv.cost_center,
"against_voucher": d.sales_invoice,
"against_voucher_type": "Sales Invoice"
}, ar_credit_account_currency, item=inv))
make_gl_entries(gl_entries,
cancel=(self.docstatus == 2),
update_outstanding='No')
def add_accounting_dimensions_filters(self, conditions, values):
accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
if self.filters.get(dimension.fieldname):
if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
self.filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
self.filters.get(dimension.fieldname))
conditions.append("{0} in %s".format(dimension.fieldname))
values.append(tuple(self.filters.get(dimension.fieldname)))
def get_conditions(filters):
conditions = ""
if filters.get("company"): conditions += " and company=%(company)s"
if filters.get("customer"): conditions += " and customer = %(customer)s"
if filters.get("from_date"): conditions += " and posting_date >= %(from_date)s"
if filters.get("to_date"): conditions += " and posting_date <= %(to_date)s"
if filters.get("owner"): conditions += " and owner = %(owner)s"
if filters.get("mode_of_payment"):
conditions += """ and exists(select name from `tabSales Invoice Payment`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)"""
if filters.get("cost_center"):
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.cost_center, '') = %(cost_center)s)"""
if filters.get("warehouse"):
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.warehouse, '') = %(warehouse)s)"""
if filters.get("brand"):
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.brand, '') = %(brand)s)"""
if filters.get("item_group"):
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.item_group, '') = %(item_group)s)"""
accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
common_condition = """
and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
"""
for dimension in accounting_dimensions:
if filters.get(dimension.fieldname):
if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
filters.get(dimension.fieldname))
conditions += common_condition + "and ifnull(`tabSales Invoice Item`.{0}, '') in %({0})s)".format(dimension.fieldname)
else:
conditions += common_condition + "and ifnull(`tabSales Invoice Item`.{0}, '') in (%({0})s))".format(dimension.fieldname)
return conditions
def get_result(filters, account_details):
accounting_dimensions = []
if filters.get("include_dimensions"):
accounting_dimensions = get_accounting_dimensions()
gl_entries = get_gl_entries(filters, accounting_dimensions)
data = get_data_with_opening_closing(filters, account_details,
accounting_dimensions, gl_entries)
result = get_result_as_list(data, filters)
return result
def make_gl_entries(self):
if not self.dunning_amount:
return
gl_entries = []
invoice_fields = ["project", "cost_center", "debit_to", "party_account_currency", "conversion_rate", "cost_center"]
inv = frappe.db.get_value("Sales Invoice", self.sales_invoice, invoice_fields, as_dict=1)
accounting_dimensions = get_accounting_dimensions()
invoice_fields.extend(accounting_dimensions)
dunning_in_company_currency = flt(self.dunning_amount * inv.conversion_rate)
default_cost_center = frappe.get_cached_value('Company', self.company, 'cost_center')
gl_entries.append(
self.get_gl_dict({
"account": inv.debit_to,
"party_type": "Customer",
"party": self.customer,
"due_date": self.due_date,
"against": self.income_account,
"debit": dunning_in_company_currency,
"debit_in_account_currency": self.dunning_amount,
"against_voucher": self.name,
"against_voucher_type": "Dunning",
"cost_center": inv.cost_center or default_cost_center,
"project": inv.project
}, inv.party_account_currency, item=inv)
)
gl_entries.append(
self.get_gl_dict({
"account": self.income_account,
"against": self.customer,
"credit": dunning_in_company_currency,
"cost_center": inv.cost_center or default_cost_center,
"credit_in_account_currency": self.dunning_amount,
"project": inv.project
}, item=inv)
)
make_gl_entries(gl_entries, cancel=(self.docstatus == 2), update_outstanding="No", merge_entries=False)
def book_revenue_via_journal_entry(doc,
credit_account,
debit_account,
against,
amount,
base_amount,
posting_date,
project,
account_currency,
cost_center,
item,
deferred_process=None,
submit='No'):
if amount == 0: return
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.posting_date = posting_date
journal_entry.company = doc.company
journal_entry.voucher_type = 'Deferred Revenue' if doc.doctype == 'Sales Invoice' \
else 'Deferred Expense'
debit_entry = {
'account': credit_account,
'credit': base_amount,
'credit_in_account_currency': amount,
'party_type':
'Customer' if doc.doctype == 'Sales Invoice' else 'Supplier',
'party': against,
'account_currency': account_currency,
'reference_name': doc.name,
'reference_type': doc.doctype,
'reference_detail_no': item.name,
'cost_center': cost_center,
'project': project,
}
credit_entry = {
'account': debit_account,
'debit': base_amount,
'debit_in_account_currency': amount,
'party_type':
'Customer' if doc.doctype == 'Sales Invoice' else 'Supplier',
'party': against,
'account_currency': account_currency,
'reference_name': doc.name,
'reference_type': doc.doctype,
'reference_detail_no': item.name,
'cost_center': cost_center,
'project': project,
}
for dimension in get_accounting_dimensions():
debit_entry.update({dimension: item.get(dimension)})
credit_entry.update({dimension: item.get(dimension)})
journal_entry.append('accounts', debit_entry)
journal_entry.append('accounts', credit_entry)
try:
journal_entry.save()
if submit:
journal_entry.submit()
except:
frappe.db.rollback()
traceback = frappe.get_traceback()
frappe.log_error(message=traceback)
frappe.flags.deferred_accounting_error = True
def validate_expense_against_budget(args):
args = frappe._dict(args)
if args.get('company') and not args.fiscal_year:
args.fiscal_year = get_fiscal_year(args.get('posting_date'),
company=args.get('company'))[0]
frappe.flags.exception_approver_role = frappe.get_cached_value(
'Company', args.get('company'), 'exception_budget_approver_role')
if not args.account:
args.account = args.get("expense_account")
if not (args.get('account')
and args.get('cost_center')) and args.item_code:
args.cost_center, args.account = get_item_details(args)
if not args.account:
return
for budget_against in ['project', 'cost_center'
] + get_accounting_dimensions():
if (args.get(budget_against) and args.account
and frappe.db.get_value("Account", {
"name": args.account,
"root_type": "Expense"
})):
doctype = frappe.unscrub(budget_against)
if frappe.get_cached_value('DocType', doctype, 'is_tree'):
lft, rgt = frappe.db.get_value(doctype,
args.get(budget_against),
["lft", "rgt"])
condition = """and exists(select name from `tab%s`
where lft<=%s and rgt>=%s and name=b.%s)""" % (
doctype, lft, rgt, budget_against) #nosec
args.is_tree = True
else:
condition = "and b.%s=%s" % (
budget_against, frappe.db.escape(args.get(budget_against)))
args.is_tree = False
args.budget_against_field = budget_against
args.budget_against_doctype = doctype
budget_records = frappe.db.sql("""
select
b.{budget_against_field} as budget_against, ba.budget_amount, b.monthly_distribution,
ifnull(b.applicable_on_material_request, 0) as for_material_request,
ifnull(applicable_on_purchase_order, 0) as for_purchase_order,
ifnull(applicable_on_booking_actual_expenses,0) as for_actual_expenses,
b.action_if_annual_budget_exceeded, b.action_if_accumulated_monthly_budget_exceeded,
b.action_if_annual_budget_exceeded_on_mr, b.action_if_accumulated_monthly_budget_exceeded_on_mr,
b.action_if_annual_budget_exceeded_on_po, b.action_if_accumulated_monthly_budget_exceeded_on_po
from
`tabBudget` b, `tabBudget Account` ba
where
b.name=ba.parent and b.fiscal_year=%s
and ba.account=%s and b.docstatus=1
{condition}
""".format(condition=condition, budget_against_field=budget_against),
(args.fiscal_year, args.account),
as_dict=True) #nosec
if budget_records:
validate_budget_records(args, budget_records)
def get_columns(filters):
if filters.get("presentation_currency"):
currency = filters["presentation_currency"]
else:
if filters.get("company"):
currency = get_company_currency(filters["company"])
else:
company = get_default_company()
currency = get_company_currency(company)
columns = [
{
"label": _("GL Entry"),
"fieldname": "gl_entry",
"fieldtype": "Link",
"options": "GL Entry",
"hidden": 1
},
{
"label": _("Posting Date"),
"fieldname": "posting_date",
"fieldtype": "Date",
"width": 90
},
{
"label": _("Account"),
"fieldname": "account",
"fieldtype": "Link",
"options": "Account",
"width": 180
},
{
"label": _("Debit ({0})").format(currency),
"fieldname": "debit",
"fieldtype": "Float",
"width": 100
},
{
"label": _("Credit ({0})").format(currency),
"fieldname": "credit",
"fieldtype": "Float",
"width": 100
},
{
"label": _("Balance ({0})").format(currency),
"fieldname": "balance",
"fieldtype": "Float",
"width": 130
}
]
columns.extend([
{
"label": _("Voucher Type"),
"fieldname": "voucher_type",
"width": 120
},
{
"label": _("Voucher No"),
"fieldname": "voucher_no",
"fieldtype": "Dynamic Link",
"options": "voucher_type",
"width": 180
},
{
"label": _("Against Account"),
"fieldname": "against",
"width": 120
},
{
"label": _("Party Type"),
"fieldname": "party_type",
"width": 100
},
{
"label": _("Party"),
"fieldname": "party",
"width": 100
},
{
"label": _("Project"),
"options": "Project",
"fieldname": "project",
"width": 100
}
])
if filters.get("include_dimensions"):
for dim in get_accounting_dimensions(as_list = False):
columns.append({
"label": _(dim.label),
"options": dim.label,
"fieldname": dim.fieldname,
"width": 100
})
columns.extend([
{
"label": _("Cost Center"),
"options": "Cost Center",
"fieldname": "cost_center",
"width": 100
},
{
"label": _("Against Voucher Type"),
"fieldname": "against_voucher_type",
"width": 100
},
{
"label": _("Against Voucher"),
"fieldname": "against_voucher",
"fieldtype": "Dynamic Link",
"options": "against_voucher_type",
"width": 100
},
{
"label": _("Supplier Invoice No"),
"fieldname": "bill_no",
"fieldtype": "Data",
"width": 100
},
{
"label": _("Remarks"),
"fieldname": "remarks",
"width": 400
}
])
return columns
def get_items_from_plans(self, plans, prorate=0):
"""
Returns the `Item`s linked to `Subscription Plan`
"""
if prorate:
prorate_factor = get_prorata_factor(self.current_invoice_end,
self.current_invoice_start)
items = []
party = self.party
for plan in plans:
plan_doc = frappe.get_doc('Subscription Plan', plan.plan)
item_code = plan_doc.item
if self.party == 'Customer':
deferred_field = 'enable_deferred_revenue'
else:
deferred_field = 'enable_deferred_expense'
deferred = frappe.db.get_value('Item', item_code, deferred_field)
if not prorate:
item = {
'item_code':
item_code,
'qty':
plan.qty,
'rate':
get_plan_rate(plan.plan, plan.qty, party,
self.current_invoice_start,
self.current_invoice_end),
'cost_center':
plan_doc.cost_center
}
else:
item = {
'item_code':
item_code,
'qty':
plan.qty,
'rate':
get_plan_rate(plan.plan, plan.qty, party,
self.current_invoice_start,
self.current_invoice_end, prorate_factor),
'cost_center':
plan_doc.cost_center
}
if deferred:
item.update({
deferred_field: deferred,
'service_start_date': self.current_invoice_start,
'service_end_date': self.current_invoice_end
})
accounting_dimensions = get_accounting_dimensions()
for dimension in accounting_dimensions:
if plan_doc.get(dimension):
item.update({dimension: plan_doc.get(dimension)})
items.append(item)
return items
def create_invoice(self, prorate):
"""
Creates a `Invoice`, submits it and returns it
"""
doctype = 'Sales Invoice' if self.party_type == 'Customer' else 'Purchase Invoice'
invoice = frappe.new_doc(doctype)
invoice.set_posting_time = 1
invoice.posting_date = self.current_invoice_start if self.generate_invoice_at_period_start \
else self.current_invoice_end
invoice.cost_center = self.cost_center
if doctype == 'Sales Invoice':
invoice.customer = self.party
else:
invoice.supplier = self.party
if frappe.db.get_value('Supplier', self.party,
'tax_withholding_category'):
invoice.apply_tds = 1
## Add dimensions in invoice for subscription:
accounting_dimensions = get_accounting_dimensions()
for dimension in accounting_dimensions:
if self.get(dimension):
invoice.update({dimension: self.get(dimension)})
# Subscription is better suited for service items. I won't update `update_stock`
# for that reason
items_list = self.get_items_from_plans(self.plans, prorate)
for item in items_list:
invoice.append('items', item)
# Taxes
tax_template = ''
if doctype == 'Sales Invoice' and self.sales_tax_template:
tax_template = self.sales_tax_template
if doctype == 'Purchase Invoice' and self.purchase_tax_template:
tax_template = self.purchase_tax_template
if tax_template:
invoice.taxes_and_charges = tax_template
invoice.set_taxes()
# Due date
if self.days_until_due:
invoice.append(
'payment_schedule', {
'due_date':
add_days(invoice.posting_date, cint(self.days_until_due)),
'invoice_portion':
100
})
# Discounts
if self.additional_discount_percentage:
invoice.additional_discount_percentage = self.additional_discount_percentage
if self.additional_discount_amount:
invoice.discount_amount = self.additional_discount_amount
if self.additional_discount_percentage or self.additional_discount_amount:
discount_on = self.apply_additional_discount
invoice.apply_discount_on = discount_on if discount_on else 'Grand Total'
# Subscription period
invoice.from_date = self.current_invoice_start
invoice.to_date = self.current_invoice_end
invoice.flags.ignore_mandatory = True
invoice.save()
invoice.submit()
return invoice