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