def get_party_account(party_type, party, company):
"""Returns the account for the given `party`.
Will first search in party (Customer / Supplier) record, if not found,
will search in group (Customer Group / Supplier Group),
finally will return default."""
if not company:
frappe.throw(_("Please select a Company"))
if not party:
return
account = frappe.db.get_value("Party Account",
{"parenttype": party_type, "parent": party, "company": company}, "account")
if not account and party_type in ['Customer', 'Supplier']:
party_group_doctype = "Customer Group" if party_type=="Customer" else "Supplier Group"
group = frappe.get_cached_value(party_type, party, scrub(party_group_doctype))
account = frappe.db.get_value("Party Account",
{"parenttype": party_group_doctype, "parent": group, "company": company}, "account")
if not account and party_type in ['Customer', 'Supplier']:
default_account_name = "default_receivable_account" \
if party_type=="Customer" else "default_payable_account"
account = frappe.get_cached_value('Company', company, default_account_name)
existing_gle_currency = get_party_gle_currency(party_type, party, company)
if existing_gle_currency:
if account:
account_currency = frappe.db.get_value("Account", account, "account_currency", cache=True)
if (account and account_currency != existing_gle_currency) or not account:
account = get_party_gle_account(party_type, party, company)
return account
def validate_inter_company_accounts(self):
if self.voucher_type == "Inter Company Journal Entry" and self.inter_company_journal_entry_reference:
doc = frappe.get_doc("Journal Entry", self.inter_company_journal_entry_reference)
account_currency = frappe.get_cached_value('Company', self.company, "default_currency")
previous_account_currency = frappe.get_cached_value('Company', doc.company, "default_currency")
if account_currency == previous_account_currency:
if self.total_credit != doc.total_debit or self.total_debit != doc.total_credit:
frappe.throw(_("Total Credit/ Debit Amount should be same as linked Journal Entry"))
def sales_invoice_validate(doc):
#Validate company
if doc.doctype != 'Sales Invoice':
return
if not doc.company_address:
frappe.throw(_("Please set an Address on the Company '%s'" % doc.company), title=_("E-Invoicing Information Missing"))
else:
validate_address(doc.company_address)
company_fiscal_regime = frappe.get_cached_value("Company", doc.company, 'fiscal_regime')
if not company_fiscal_regime:
frappe.throw(_("Fiscal Regime is mandatory, kindly set the fiscal regime in the company {0}")
.format(doc.company))
else:
doc.company_fiscal_regime = company_fiscal_regime
doc.company_tax_id = frappe.get_cached_value("Company", doc.company, 'tax_id')
doc.company_fiscal_code = frappe.get_cached_value("Company", doc.company, 'fiscal_code')
if not doc.company_tax_id and not doc.company_fiscal_code:
frappe.throw(_("Please set either the Tax ID or Fiscal Code on Company '%s'" % doc.company), title=_("E-Invoicing Information Missing"))
#Validate customer details
customer = frappe.get_doc("Customer", doc.customer)
if customer.customer_type == _("Individual"):
doc.customer_fiscal_code = customer.fiscal_code
if not doc.customer_fiscal_code:
frappe.throw(_("Please set Fiscal Code for the customer '%s'" % doc.customer), title=_("E-Invoicing Information Missing"))
else:
if customer.is_public_administration:
doc.customer_fiscal_code = customer.fiscal_code
if not doc.customer_fiscal_code:
frappe.throw(_("Please set Fiscal Code for the public administration '%s'" % doc.customer), title=_("E-Invoicing Information Missing"))
else:
doc.tax_id = customer.tax_id
if not doc.tax_id:
frappe.throw(_("Please set Tax ID for the customer '%s'" % doc.customer), title=_("E-Invoicing Information Missing"))
if not doc.customer_address:
frappe.throw(_("Please set the Customer Address"), title=_("E-Invoicing Information Missing"))
else:
validate_address(doc.customer_address)
if not len(doc.taxes):
frappe.throw(_("Please set at least one row in the Taxes and Charges Table"), title=_("E-Invoicing Information Missing"))
else:
for row in doc.taxes:
if row.rate == 0 and row.tax_amount == 0 and not row.tax_exemption_reason:
frappe.throw(_("Row {0}: Please set at Tax Exemption Reason in Sales Taxes and Charges".format(row.idx)),
title=_("E-Invoicing Information Missing"))
for schedule in doc.payment_schedule:
if schedule.mode_of_payment and not schedule.mode_of_payment_code:
schedule.mode_of_payment_code = frappe.get_cached_value('Mode of Payment',
schedule.mode_of_payment, 'mode_of_payment_code')
def create_account(company):
salary_account = frappe.db.get_value("Account", "Salary - " + frappe.get_cached_value('Company', company, 'abbr'))
if not salary_account:
frappe.get_doc({
"doctype": "Account",
"account_name": "Salary",
"parent_account": "Indirect Expenses - " + frappe.get_cached_value('Company', company, 'abbr'),
"company": company
}).insert()
return salary_account
def get_credit_limit(customer, company):
credit_limit = None
if customer:
credit_limit, customer_group = frappe.get_cached_value("Customer",
customer, ["credit_limit", "customer_group"])
if not credit_limit:
credit_limit = frappe.get_cached_value("Customer Group", customer_group, "credit_limit")
if not credit_limit:
credit_limit = frappe.get_cached_value('Company', company, "credit_limit")
return flt(credit_limit)
def __init__(self, args, allow_zero_rate=False, allow_negative_stock=None, via_landed_cost_voucher=False, verbose=1):
from frappe.model.meta import get_field_precision
self.exceptions = []
self.verbose = verbose
self.allow_zero_rate = allow_zero_rate
self.allow_negative_stock = allow_negative_stock
self.via_landed_cost_voucher = via_landed_cost_voucher
if not self.allow_negative_stock:
self.allow_negative_stock = cint(frappe.db.get_single_value("Stock Settings",
"allow_negative_stock"))
self.args = args
for key, value in iteritems(args):
setattr(self, key, value)
self.previous_sle = self.get_sle_before_datetime()
self.previous_sle = self.previous_sle[0] if self.previous_sle else frappe._dict()
for key in ("qty_after_transaction", "valuation_rate", "stock_value"):
setattr(self, key, flt(self.previous_sle.get(key)))
self.company = frappe.db.get_value("Warehouse", self.warehouse, "company")
self.precision = get_field_precision(frappe.get_meta("Stock Ledger Entry").get_field("stock_value"),
currency=frappe.get_cached_value('Company', self.company, "default_currency"))
self.prev_stock_value = self.previous_sle.stock_value or 0.0
self.stock_queue = json.loads(self.previous_sle.stock_queue or "[]")
self.valuation_method = get_valuation_method(self.item_code)
self.stock_value_difference = 0.0
self.build()
def execute():
"""
Patch Reference:
1. check whether warehouse is associated to company or not
2. if warehouse is associated with company
a. create warehouse group for company
b. set warehouse group as parent to other warehouses and set is_group as 0
3. if warehouses is not associated with company
a. get distinct companies from stock ledger entries
b. if sle have only company,
i. set default company to all warehouse
ii. repeat 2.a and 2.b
c. if have multiple companies,
i. create group warehouse without company
ii. repeat 2.b
"""
frappe.reload_doc("stock", "doctype", "warehouse")
if check_is_warehouse_associated_with_company():
for company in frappe.get_all("Company", fields=["name", "abbr"]):
make_warehouse_nestedset(company)
else:
sle_against_companies = frappe.db.sql_list("""select distinct company from `tabStock Ledger Entry`""")
if len(sle_against_companies) == 1:
company = frappe.get_cached_value('Company', sle_against_companies[0],
fieldname=["name", "abbr"], as_dict=1)
set_company_to_warehouse(company.name)
make_warehouse_nestedset(company)
elif len(sle_against_companies) > 1:
make_warehouse_nestedset()
def autoname(self):
if self.company:
suffix = " - " + frappe.get_cached_value('Company', self.company, "abbr")
if not self.healthcare_service_unit_name.endswith(suffix):
self.name = self.healthcare_service_unit_name + suffix
else:
self.name = self.healthcare_service_unit_name
def get_party_adjustment_amounts(self):
conditions = self.prepare_conditions()
income_or_expense = "Expense" if self.filters.party_type == "Customer" else "Income"
invoice_dr_or_cr = "debit" if self.filters.party_type == "Customer" else "credit"
reverse_dr_or_cr = "credit" if self.filters.party_type == "Customer" else "debit"
gl_entries = frappe.db.sql("""
select
posting_date, account, party, voucher_type, voucher_no, debit, credit
from
`tabGL Entry`
where
docstatus < 2
and (voucher_type, voucher_no) in (
select voucher_type, voucher_no from `tabGL Entry` gle, `tabAccount` acc
where acc.name = gle.account and acc.root_type = '{income_or_expense}'
and gle.posting_date between %(from_date)s and %(to_date)s and gle.docstatus < 2
) and (voucher_type, voucher_no) in (
select voucher_type, voucher_no from `tabGL Entry` gle
where gle.party_type=%(party_type)s and ifnull(party, '') != ''
and gle.posting_date between %(from_date)s and %(to_date)s and gle.docstatus < 2 {conditions}
)
""".format(conditions=conditions, income_or_expense=income_or_expense), self.filters, as_dict=True)
self.party_adjustment_details = {}
adjustment_voucher_entries = {}
for gle in gl_entries:
adjustment_voucher_entries.setdefault((gle.voucher_type, gle.voucher_no), [])
adjustment_voucher_entries[(gle.voucher_type, gle.voucher_no)].append(gle)
for voucher_gl_entries in itervalues(adjustment_voucher_entries):
parties = {}
accounts = {}
has_irrelevant_entry = False
for gle in voucher_gl_entries:
if gle.account == self.round_off_account:
continue
elif gle.party:
parties.setdefault(gle.party, 0)
parties[gle.party] += gle.get(reverse_dr_or_cr) - gle.get(invoice_dr_or_cr)
elif frappe.get_cached_value("Account", gle.account, "root_type") == income_or_expense:
accounts.setdefault(gle.account, 0)
accounts[gle.account] += gle.get(invoice_dr_or_cr) - gle.get(reverse_dr_or_cr)
else:
has_irrelevant_entry = True
if parties and accounts:
if len(parties) == 1:
party = parties.keys()[0]
for account, amount in iteritems(accounts):
self.party_adjustment_details.setdefault(party, {})
self.party_adjustment_details[party].setdefault(account, 0)
self.party_adjustment_details[party][account] += amount
elif len(accounts) == 1 and not has_irrelevant_entry:
account = accounts.keys()[0]
for party, amount in iteritems(parties):
self.party_adjustment_details.setdefault(party, {})
self.party_adjustment_details[party].setdefault(account, 0)
self.party_adjustment_details[party][account] += amount
def execute(filters=None):
if cint(frappe.db.get_single_value('Accounts Settings', 'use_custom_cash_flow')):
from erpnext.accounts.report.cash_flow.custom_cash_flow import execute as execute_custom
return execute_custom(filters=filters)
period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year,
filters.periodicity, filters.accumulated_values, filters.company)
cash_flow_accounts = get_cash_flow_accounts()
# compute net profit / loss
income = get_data(filters.company, "Income", "Credit", period_list, filters=filters,
accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters,
accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
data = []
company_currency = frappe.get_cached_value('Company', filters.company, "default_currency")
for cash_flow_account in cash_flow_accounts:
section_data = []
data.append({
"account_name": cash_flow_account['section_header'],
"parent_account": None,
"indent": 0.0,
"account": cash_flow_account['section_header']
})
if len(data) == 1:
# add first net income in operations section
if net_profit_loss:
net_profit_loss.update({
"indent": 1,
"parent_account": cash_flow_accounts[0]['section_header']
})
data.append(net_profit_loss)
section_data.append(net_profit_loss)
for account in cash_flow_account['account_types']:
account_data = get_account_type_based_data(filters.company,
account['account_type'], period_list, filters.accumulated_values)
account_data.update({
"account_name": account['label'],
"account": account['label'],
"indent": 1,
"parent_account": cash_flow_account['section_header'],
"currency": company_currency
})
data.append(account_data)
section_data.append(account_data)
add_total_row_account(data, section_data, cash_flow_account['section_footer'],
period_list, company_currency)
add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency)
columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company)
return columns, data
def validate_max_discount(self):
for d in self.get("items"):
if d.item_code:
discount = flt(frappe.get_cached_value("Item", d.item_code, "max_discount"))
if discount and flt(d.discount_percentage) > discount:
frappe.throw(_("Maximum discount for Item {0} is {1}%").format(d.item_code, discount))
def prepare_data(accounts, filters, total_row, parent_children_map, based_on):
data = []
company_currency = frappe.get_cached_value('Company', filters.get("company"), "default_currency")
for d in accounts:
has_value = False
row = {
"account_name": d.account_name or d.name,
"account": d.name,
"parent_account": d.parent_account,
"indent": d.indent,
"fiscal_year": filters.get("fiscal_year"),
"currency": company_currency,
"based_on": based_on
}
for key in value_fields:
row[key] = flt(d.get(key, 0.0), 3)
if abs(row[key]) >= 0.005:
# ignore zero values
has_value = True
row["has_value"] = has_value
data.append(row)
data.extend([{},total_row])
return data
def execute(filters=None):
if not filters.periodicity: filters.periodicity = "Monthly"
period_list = get_period_list(
filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity,
filters.accumulated_values, filters.company
)
mappers = get_mappers_from_db()
cash_flow_accounts = setup_mappers(mappers)
# compute net profit / loss
income = get_data(
filters.company, "Income", "Credit", period_list, filters=filters,
accumulated_values=filters.accumulated_values, ignore_closing_entries=True,
ignore_accumulated_values_for_fy=True
)
expense = get_data(
filters.company, "Expense", "Debit", period_list, filters=filters,
accumulated_values=filters.accumulated_values, ignore_closing_entries=True,
ignore_accumulated_values_for_fy=True
)
net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
company_currency = frappe.get_cached_value('Company', filters.company, "default_currency")
data = compute_data(filters, company_currency, net_profit_loss, period_list, mappers, cash_flow_accounts)
_add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency)
columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company)
return columns, data
def set_account_currency(filters):
if filters.get("account") or (filters.get('party') and len(filters.party) == 1):
filters["company_currency"] = frappe.get_cached_value('Company', filters.company, "default_currency")
account_currency = None
if filters.get("account"):
account_currency = get_account_currency(filters.account)
elif filters.get("party"):
gle_currency = frappe.db.get_value(
"GL Entry", {
"party_type": filters.party_type, "party": filters.party[0], "company": filters.company
},
"account_currency"
)
if gle_currency:
account_currency = gle_currency
else:
account_currency = (None if filters.party_type in ["Employee", "Student", "Shareholder", "Member"] else
frappe.db.get_value(filters.party_type, filters.party[0], "default_currency"))
filters["account_currency"] = account_currency or filters.company_currency
if filters.account_currency != filters.company_currency and not filters.presentation_currency:
filters.presentation_currency = filters.account_currency
return filters
def get_depreciation_accounts(asset):
fixed_asset_account = accumulated_depreciation_account = depreciation_expense_account = None
accounts = frappe.db.get_value("Asset Category Account",
filters={'parent': asset.asset_category, 'company_name': asset.company},
fieldname = ['fixed_asset_account', 'accumulated_depreciation_account',
'depreciation_expense_account'], as_dict=1)
if accounts:
fixed_asset_account = accounts.fixed_asset_account
accumulated_depreciation_account = accounts.accumulated_depreciation_account
depreciation_expense_account = accounts.depreciation_expense_account
if not accumulated_depreciation_account or not depreciation_expense_account:
accounts = frappe.get_cached_value('Company', asset.company,
["accumulated_depreciation_account", "depreciation_expense_account"])
if not accumulated_depreciation_account:
accumulated_depreciation_account = accounts[0]
if not depreciation_expense_account:
depreciation_expense_account = accounts[1]
if not fixed_asset_account or not accumulated_depreciation_account or not depreciation_expense_account:
frappe.throw(_("Please set Depreciation related Accounts in Asset Category {0} or Company {1}")
.format(asset.asset_category, asset.company))
return fixed_asset_account, accumulated_depreciation_account, depreciation_expense_account
def scrap_asset(asset_name):
asset = frappe.get_doc("Asset", asset_name)
if asset.docstatus != 1:
frappe.throw(_("Asset {0} must be submitted").format(asset.name))
elif asset.status in ("Cancelled", "Sold", "Scrapped"):
frappe.throw(_("Asset {0} cannot be scrapped, as it is already {1}").format(asset.name, asset.status))
depreciation_series = frappe.get_cached_value('Company', asset.company, "series_for_depreciation_entry")
je = frappe.new_doc("Journal Entry")
je.voucher_type = "Journal Entry"
je.naming_series = depreciation_series
je.posting_date = today()
je.company = asset.company
je.remark = "Scrap Entry for asset {0}".format(asset_name)
for entry in get_gl_entries_on_asset_disposal(asset):
entry.update({
"reference_type": "Asset",
"reference_name": asset_name
})
je.append("accounts", entry)
je.flags.ignore_permissions = True
je.submit()
frappe.db.set_value("Asset", asset_name, "disposal_date", today())
frappe.db.set_value("Asset", asset_name, "journal_entry_for_scrap", je.name)
asset.set_status("Scrapped")
frappe.msgprint(_("Asset scrapped via Journal Entry {0}").format(je.name))
def validate_selling_price(self):
def throw_message(item_name, rate, ref_rate_field):
frappe.throw(_("""Selling rate for item {0} is lower than its {1}. Selling rate should be atleast {2}""")
.format(item_name, ref_rate_field, rate))
if not frappe.db.get_single_value("Selling Settings", "validate_selling_price"):
return
if hasattr(self, "is_return") and self.is_return:
return
for it in self.get("items"):
if not it.item_code:
continue
last_purchase_rate, is_stock_item = frappe.get_cached_value("Item", it.item_code, ["last_purchase_rate", "is_stock_item"])
last_purchase_rate_in_sales_uom = last_purchase_rate / (it.conversion_factor or 1)
if flt(it.base_rate) < flt(last_purchase_rate_in_sales_uom):
throw_message(it.item_name, last_purchase_rate_in_sales_uom, "last purchase rate")
last_valuation_rate = frappe.db.sql("""
SELECT valuation_rate FROM `tabStock Ledger Entry` WHERE item_code = %s
AND warehouse = %s AND valuation_rate > 0
ORDER BY posting_date DESC, posting_time DESC, name DESC LIMIT 1
""", (it.item_code, it.warehouse))
if last_valuation_rate:
last_valuation_rate_in_sales_uom = last_valuation_rate[0][0] / (it.conversion_factor or 1)
if is_stock_item and flt(it.base_rate) < flt(last_valuation_rate_in_sales_uom):
throw_message(it.name, last_valuation_rate_in_sales_uom, "valuation rate")
def make_address(args, customer):
if not args.get('address_line1'):
return
name = args.get('name')
if not name:
data = get_customers_address(customer)
name = data[customer].get('name') if data else None
if name:
address = frappe.get_doc('Address', name)
else:
address = frappe.new_doc('Address')
address.country = frappe.get_cached_value('Company', args.get('company'), 'country')
address.append('links', {
'link_doctype': 'Customer',
'link_name': customer
})
address.is_primary_address = 1
address.is_shipping_address = 1
address.update(args)
address.flags.ignore_mandatory = True
address.save(ignore_permissions=True)
def validate_account_currency(self):
if not self.account_currency:
self.account_currency = frappe.get_cached_value('Company', self.company, "default_currency")
elif self.account_currency != frappe.db.get_value("Account", self.name, "account_currency"):
if frappe.db.get_value("GL Entry", {"account": self.name}):
frappe.throw(_("Currency can not be changed after making entries using some other currency"))
def set_missing_values(source, target):
from erpnext.controllers.accounts_controller import get_default_taxes_and_charges
quotation = frappe.get_doc(target)
company_currency = frappe.get_cached_value('Company', quotation.company, "default_currency")
if quotation.quotation_to == 'Customer' and quotation.party_name:
party_account_currency = get_party_account_currency("Customer", quotation.party_name, quotation.company)
else:
party_account_currency = company_currency
quotation.currency = party_account_currency or company_currency
if company_currency == quotation.currency:
exchange_rate = 1
else:
exchange_rate = get_exchange_rate(quotation.currency, company_currency,
quotation.transaction_date, args="for_selling")
quotation.conversion_rate = exchange_rate
# get default taxes
taxes = get_default_taxes_and_charges("Sales Taxes and Charges Template", company=quotation.company)
if taxes.get('taxes'):
quotation.update(taxes)
quotation.run_method("set_missing_values")
quotation.run_method("calculate_taxes_and_totals")
if not source.with_items:
quotation.opportunity = source.name
def autoname(self):
if self.company:
suffix = " - " + frappe.get_cached_value('Company', self.company, "abbr")
if not self.warehouse_name.endswith(suffix):
self.name = self.warehouse_name + suffix
else:
self.name = self.warehouse_name
def get_default_deferred_account(args, item, fieldname=None):
if item.get("enable_deferred_revenue") or item.get("enable_deferred_expense"):
return (item.get(fieldname)
or args.get(fieldname)
or frappe.get_cached_value('Company', args.company, "default_"+fieldname))
else:
return None
def validate_conversion_rate(args, meta):
from erpnext.controllers.accounts_controller import validate_conversion_rate
if (not args.conversion_rate
and args.currency==frappe.get_cached_value('Company', args.company, "default_currency")):
args.conversion_rate = 1.0
# validate currency conversion rate
validate_conversion_rate(args.currency, args.conversion_rate,
meta.get_label("conversion_rate"), args.company)
args.conversion_rate = flt(args.conversion_rate,
get_field_precision(meta.get_field("conversion_rate"),
frappe._dict({"fields": args})))
if (not args.plc_conversion_rate
and args.price_list_currency==frappe.db.get_value("Price List", args.price_list, "currency", cache=True)):
args.plc_conversion_rate = 1.0
# validate price list currency conversion rate
if not args.get("price_list_currency"):
throw(_("Price List Currency not selected"))
else:
validate_conversion_rate(args.price_list_currency, args.plc_conversion_rate,
meta.get_label("plc_conversion_rate"), args.company)
if meta.get_field("plc_conversion_rate"):
args.plc_conversion_rate = flt(args.plc_conversion_rate,
get_field_precision(meta.get_field("plc_conversion_rate"),
frappe._dict({"fields": args})))
def create_purchase_order(**args):
po = frappe.new_doc("Purchase Order")
args = frappe._dict(args)
if args.transaction_date:
po.transaction_date = args.transaction_date
po.schedule_date = add_days(nowdate(), 1)
po.company = args.company or "_Test Company"
po.supplier = args.customer or "_Test Supplier"
po.is_subcontracted = args.is_subcontracted or "No"
po.currency = args.currency or frappe.get_cached_value('Company', po.company, "default_currency")
po.conversion_factor = args.conversion_factor or 1
po.supplier_warehouse = args.supplier_warehouse or None
po.append("items", {
"item_code": args.item or args.item_code or "_Test Item",
"warehouse": args.warehouse or "_Test Warehouse - _TC",
"qty": args.qty or 10,
"rate": args.rate or 500,
"schedule_date": add_days(nowdate(), 1),
"include_exploded_items": args.get('include_exploded_items', 1)
})
if not args.do_not_save:
po.insert()
if not args.do_not_submit:
if po.is_subcontracted == "Yes":
supp_items = po.get("supplied_items")
for d in supp_items:
d.reserve_warehouse = args.warehouse or "_Test Warehouse - _TC"
po.submit()
return po
def set_missing_values(self):
if not self.asset_category:
self.asset_category = frappe.get_cached_value("Item", self.item_code, "asset_category")
if self.item_code and not self.get('finance_books'):
finance_books = get_item_details(self.item_code, self.asset_category)
self.set('finance_books', finance_books)
def get_children(doctype, parent, company, is_root=False):
from erpnext.accounts.report.financial_statements import sort_accounts
parent_fieldname = 'parent_' + doctype.lower().replace(' ', '_')
fields = [
'name as value',
'is_group as expandable'
]
filters = [['docstatus', '<', 2]]
filters.append(['ifnull(`{0}`,"")'.format(parent_fieldname), '=', '' if is_root else parent])
if is_root:
fields += ['root_type', 'report_type', 'account_currency'] if doctype == 'Account' else []
filters.append(['company', '=', company])
else:
fields += ['account_currency'] if doctype == 'Account' else []
fields += [parent_fieldname + ' as parent']
acc = frappe.get_list(doctype, fields=fields, filters=filters)
if doctype == 'Account':
sort_accounts(acc, is_root, key="value")
company_currency = frappe.get_cached_value('Company', company, "default_currency")
for each in acc:
each["company_currency"] = company_currency
each["balance"] = flt(get_balance_on(each.get("value"), in_account_currency=False))
if each.account_currency != company_currency:
each["balance_in_account_currency"] = flt(get_balance_on(each.get("value")))
return acc
def get_stock_rbnb_difference(posting_date, company):
stock_items = frappe.db.sql_list("""select distinct item_code
from `tabStock Ledger Entry` where company=%s""", company)
pr_valuation_amount = frappe.db.sql("""
select sum(pr_item.valuation_rate * pr_item.qty * pr_item.conversion_factor)
from `tabPurchase Receipt Item` pr_item, `tabPurchase Receipt` pr
where pr.name = pr_item.parent and pr.docstatus=1 and pr.company=%s
and pr.posting_date <= %s and pr_item.item_code in (%s)""" %
('%s', '%s', ', '.join(['%s']*len(stock_items))), tuple([company, posting_date] + stock_items))[0][0]
pi_valuation_amount = frappe.db.sql("""
select sum(pi_item.valuation_rate * pi_item.qty * pi_item.conversion_factor)
from `tabPurchase Invoice Item` pi_item, `tabPurchase Invoice` pi
where pi.name = pi_item.parent and pi.docstatus=1 and pi.company=%s
and pi.posting_date <= %s and pi_item.item_code in (%s)""" %
('%s', '%s', ', '.join(['%s']*len(stock_items))), tuple([company, posting_date] + stock_items))[0][0]
# Balance should be
stock_rbnb = flt(pr_valuation_amount, 2) - flt(pi_valuation_amount, 2)
# Balance as per system
stock_rbnb_account = "Stock Received But Not Billed - " + frappe.get_cached_value('Company', company, "abbr")
sys_bal = get_balance_on(stock_rbnb_account, posting_date, in_account_currency=False)
# Amount should be credited
return flt(stock_rbnb) + flt(sys_bal)
def get_net_profit_loss(income, expense, period_list, company, currency=None, consolidated=False):
total = 0
net_profit_loss = {
"account_name": "'" + _("Profit for the year") + "'",
"account": "'" + _("Profit for the year") + "'",
"warn_if_negative": True,
"currency": currency or frappe.get_cached_value('Company', company, "default_currency")
}
has_value = False
for period in period_list:
key = period if consolidated else period.key
total_income = flt(income[-2][key], 3) if income else 0
total_expense = flt(expense[-2][key], 3) if expense else 0
net_profit_loss[key] = total_income - total_expense
if net_profit_loss[key]:
has_value=True
total += flt(net_profit_loss[key])
net_profit_loss["total"] = total
if has_value:
return net_profit_loss
def make_depreciation_entry(self):
asset = frappe.get_doc("Asset", self.asset)
fixed_asset_account, accumulated_depreciation_account, depreciation_expense_account = \
get_depreciation_accounts(asset)
depreciation_cost_center, depreciation_series = frappe.get_cached_value('Company', asset.company,
["depreciation_cost_center", "series_for_depreciation_entry"])
je = frappe.new_doc("Journal Entry")
je.voucher_type = "Depreciation Entry"
je.naming_series = depreciation_series
je.posting_date = self.date
je.company = self.company
je.remark = "Depreciation Entry against {0} worth {1}".format(self.asset, self.difference_amount)
je.append("accounts", {
"account": accumulated_depreciation_account,
"credit_in_account_currency": self.difference_amount,
"cost_center": depreciation_cost_center or self.cost_center
})
je.append("accounts", {
"account": depreciation_expense_account,
"debit_in_account_currency": self.difference_amount,
"cost_center": depreciation_cost_center or self.cost_center
})
je.flags.ignore_permissions = True
je.submit()
self.db_set("journal_entry", je.name)
def validate_serial_no_based_delivery(self):
reserved_items = []
normal_items = []
for item in self.items:
if item.ensure_delivery_based_on_produced_serial_no:
if item.item_code in normal_items:
frappe.throw(_("Cannot ensure delivery by Serial No as \
Item {0} is added with and without Ensure Delivery by \
Serial No.").format(item.item_code))
if item.item_code not in reserved_items:
if not frappe.get_cached_value("Item", item.item_code, "has_serial_no"):
frappe.throw(_("Item {0} has no Serial No. Only serilialized items \
can have delivery based on Serial No").format(item.item_code))
if not frappe.db.exists("BOM", {"item": item.item_code, "is_active": 1}):
frappe.throw(_("No active BOM found for item {0}. Delivery by \
Serial No cannot be ensured").format(item.item_code))
reserved_items.append(item.item_code)
else:
normal_items.append(item.item_code)
if not item.ensure_delivery_based_on_produced_serial_no and \
item.item_code in reserved_items:
frappe.throw(_("Cannot ensure delivery by Serial No as \
Item {0} is added with and without Ensure Delivery by \
Serial No.").format(item.item_code))
def get_bom_items_as_dict(bom,
company,
qty=1,
fetch_exploded=1,
fetch_scrap_items=0,
include_non_stock_items=False,
fetch_qty_in_stock_uom=True):
item_dict = {}
# Did not use qty_consumed_per_unit in the query, as it leads to rounding loss
query = """select
bom_item.item_code,
bom_item.idx,
item.item_name,
sum(bom_item.{qty_field}/ifnull(bom.quantity, 1)) * %(qty)s as qty,
item.image,
bom.project,
item.stock_uom,
item.item_group,
item.allow_alternative_item,
item_default.default_warehouse,
item_default.expense_account as expense_account,
item_default.buying_cost_center as cost_center
{select_columns}
from
`tab{table}` bom_item
JOIN `tabBOM` bom ON bom_item.parent = bom.name
JOIN `tabItem` item ON item.name = bom_item.item_code
LEFT JOIN `tabItem Default` item_default
ON item_default.parent = item.name and item_default.company = %(company)s
where
bom_item.docstatus < 2
and bom.name = %(bom)s
and item.is_stock_item in (1, {is_stock_item})
{where_conditions}
group by item_code, stock_uom
order by idx"""
is_stock_item = 0 if include_non_stock_items else 1
if cint(fetch_exploded):
query = query.format(
table="BOM Explosion Item",
where_conditions="",
is_stock_item=is_stock_item,
qty_field="stock_qty",
select_columns=""", bom_item.source_warehouse, bom_item.operation,
bom_item.include_item_in_manufacturing, bom_item.description,
(Select idx from `tabBOM Item` where item_code = bom_item.item_code and parent = %(parent)s limit 1) as idx"""
)
items = frappe.db.sql(query, {
"parent": bom,
"qty": qty,
"bom": bom,
"company": company
},
as_dict=True)
elif fetch_scrap_items:
query = query.format(table="BOM Scrap Item",
where_conditions="",
select_columns=", bom_item.idx, item.description",
is_stock_item=is_stock_item,
qty_field="stock_qty")
items = frappe.db.sql(query, {
"qty": qty,
"bom": bom,
"company": company
},
as_dict=True)
else:
query = query.format(
table="BOM Item",
where_conditions="",
is_stock_item=is_stock_item,
qty_field="stock_qty" if fetch_qty_in_stock_uom else "qty",
select_columns=
""", bom_item.uom, bom_item.conversion_factor, bom_item.source_warehouse,
bom_item.idx, bom_item.operation, bom_item.include_item_in_manufacturing,
bom_item.description """)
items = frappe.db.sql(query, {
"qty": qty,
"bom": bom,
"company": company
},
as_dict=True)
for item in items:
if item.item_code in item_dict:
item_dict[item.item_code]["qty"] += flt(item.qty)
else:
item_dict[item.item_code] = item
for item, item_details in item_dict.items():
for d in [["Account", "expense_account", "stock_adjustment_account"],
["Cost Center", "cost_center", "cost_center"],
["Warehouse", "default_warehouse", ""]]:
company_in_record = frappe.db.get_value(d[0],
item_details.get(d[1]),
"company")
if not item_details.get(d[1]) or (company_in_record and
company != company_in_record):
item_dict[item][d[1]] = frappe.get_cached_value(
'Company', company, d[2]) if d[2] else None
return item_dict
def set_expense_account(doc):
for row in doc.items:
if row.is_finished_item and not row.expense_account:
row.expense_account = frappe.get_cached_value(
"Company", doc.company, "stock_adjustment_account")
def get_company_country(company):
return frappe.get_cached_value('Company', company, 'country')
def validate_selling_price(self):
def throw_message(idx, item_name, rate, ref_rate_field):
throw(
_("""Row #{0}: Selling rate for item {1} is lower than its {2}.
Selling {3} should be atleast {4}.<br><br>Alternatively,
you can disable selling price validation in {5} to bypass
this validation.""").format(
idx,
bold(item_name),
bold(ref_rate_field),
bold("net rate"),
bold(rate),
get_link_to_form("Selling Settings", "Selling Settings"),
),
title=_("Invalid Selling Price"),
)
if self.get("is_return") or not frappe.db.get_single_value(
"Selling Settings", "validate_selling_price"):
return
is_internal_customer = self.get("is_internal_customer")
valuation_rate_map = {}
for item in self.items:
if not item.item_code or item.is_free_item:
continue
last_purchase_rate, is_stock_item = frappe.get_cached_value(
"Item", item.item_code,
("last_purchase_rate", "is_stock_item"))
last_purchase_rate_in_sales_uom = last_purchase_rate * (
item.conversion_factor or 1)
if flt(item.base_net_rate) < flt(last_purchase_rate_in_sales_uom):
throw_message(item.idx, item.item_name,
last_purchase_rate_in_sales_uom,
"last purchase rate")
if is_internal_customer or not is_stock_item:
continue
valuation_rate_map[(item.item_code, item.warehouse)] = None
if not valuation_rate_map:
return
or_conditions = (f"""(item_code = {frappe.db.escape(valuation_rate[0])}
and warehouse = {frappe.db.escape(valuation_rate[1])})"""
for valuation_rate in valuation_rate_map)
valuation_rates = frappe.db.sql(
f"""
select
item_code, warehouse, valuation_rate
from
`tabBin`
where
({" or ".join(or_conditions)})
and valuation_rate > 0
""",
as_dict=True,
)
for rate in valuation_rates:
valuation_rate_map[(rate.item_code,
rate.warehouse)] = rate.valuation_rate
for item in self.items:
if not item.item_code or item.is_free_item:
continue
last_valuation_rate = valuation_rate_map.get(
(item.item_code, item.warehouse))
if not last_valuation_rate:
continue
last_valuation_rate_in_sales_uom = last_valuation_rate * (
item.conversion_factor or 1)
if flt(item.base_net_rate) < flt(last_valuation_rate_in_sales_uom):
throw_message(item.idx, item.item_name,
last_valuation_rate_in_sales_uom,
"valuation rate")
def get_balance_on(account=None,
date=None,
party_type=None,
party=None,
company=None,
in_account_currency=True,
cost_center=None,
ignore_account_permission=False):
if not account and frappe.form_dict.get("account"):
account = frappe.form_dict.get("account")
if not date and frappe.form_dict.get("date"):
date = frappe.form_dict.get("date")
if not party_type and frappe.form_dict.get("party_type"):
party_type = frappe.form_dict.get("party_type")
if not party and frappe.form_dict.get("party"):
party = frappe.form_dict.get("party")
if not cost_center and frappe.form_dict.get("cost_center"):
cost_center = frappe.form_dict.get("cost_center")
cond = []
if date:
cond.append("posting_date <= %s" % frappe.db.escape(cstr(date)))
else:
# get balance of all entries that exist
date = nowdate()
if account:
acc = frappe.get_doc("Account", account)
try:
year_start_date = get_fiscal_year(date, company=company, verbose=0)[1]
except FiscalYearError:
if getdate(date) > getdate(nowdate()):
# if fiscal year not found and the date is greater than today
# get fiscal year for today's date and its corresponding year start date
year_start_date = get_fiscal_year(nowdate(), verbose=1)[1]
else:
# this indicates that it is a date older than any existing fiscal year.
# hence, assuming balance as 0.0
return 0.0
if account:
report_type = acc.report_type
else:
report_type = ""
if cost_center and report_type == 'Profit and Loss':
cc = frappe.get_doc("Cost Center", cost_center)
if cc.is_group:
cond.append(""" exists (
select 1 from `tabCost Center` cc where cc.name = gle.cost_center
and cc.lft >= %s and cc.rgt <= %s
)""" % (cc.lft, cc.rgt))
else:
cond.append("""gle.cost_center = %s """ %
(frappe.db.escape(cost_center, percent=False), ))
if account:
if not (frappe.flags.ignore_account_permission
or ignore_account_permission):
acc.check_permission("read")
if report_type == 'Profit and Loss':
# for pl accounts, get balance within a fiscal year
cond.append("posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" \
% year_start_date)
# different filter for group and ledger - improved performance
if acc.is_group:
cond.append("""exists (
select name from `tabAccount` ac where ac.name = gle.account
and ac.lft >= %s and ac.rgt <= %s
)""" % (acc.lft, acc.rgt))
# If group and currency same as company,
# always return balance based on debit and credit in company currency
if acc.account_currency == frappe.get_cached_value(
'Company', acc.company, "default_currency"):
in_account_currency = False
else:
cond.append("""gle.account = %s """ %
(frappe.db.escape(account, percent=False), ))
if party_type and party:
cond.append("""gle.party_type = %s and gle.party = %s """ %
(frappe.db.escape(party_type),
frappe.db.escape(party, percent=False)))
if company:
cond.append("""gle.company = %s """ %
(frappe.db.escape(company, percent=False)))
if account or (party_type and party):
if in_account_currency:
select_field = "sum(debit_in_account_currency) - sum(credit_in_account_currency)"
else:
select_field = "sum(debit) - sum(credit)"
bal = frappe.db.sql("""
SELECT {0}
FROM `tabGL Entry` gle
WHERE {1}""".format(select_field, " and ".join(cond)))[0][0]
# if bal is None, return 0
return flt(bal)
def check_country_change(self):
frappe.flags.country_change = False
if not self.get('__islocal') and \
self.country != frappe.get_cached_value('Company', self.name, 'country'):
frappe.flags.country_change = True
def execute(filters=None):
if not filters: filters = {}
conditions, filters = get_conditions(filters)
columns = get_columns(filters)
att_map = get_attendance_list(conditions, filters)
emp_map = get_employee_details(filters)
holiday_list = [emp_map[d]["holiday_list"] for d in emp_map if emp_map[d]["holiday_list"]]
default_holiday_list = frappe.get_cached_value('Company', filters.get("company"), "default_holiday_list")
holiday_list.append(default_holiday_list)
holiday_list = list(set(holiday_list))
holiday_map = get_holiday(holiday_list, filters["month"])
data = []
leave_types = frappe.db.sql("""select name from `tabLeave Type`""", as_list=True)
leave_list = [d[0] for d in leave_types]
columns.extend(leave_list)
columns.extend([_("Total Late Entries") + ":Float:120", _("Total Early Exits") + ":Float:120"])
for emp in sorted(att_map):
emp_det = emp_map.get(emp)
if not emp_det:
continue
row = [emp, emp_det.employee_name, emp_det.branch, emp_det.department, emp_det.designation,
emp_det.company]
total_p = total_a = total_l = 0.0
for day in range(filters["total_days_in_month"]):
status = att_map.get(emp).get(day + 1, "None")
status_map = {"Present": "P", "Absent": "A", "Half Day": "HD", "On Leave": "L", "None": "", "Holiday":"<b>H</b>"}
if status == "None" and holiday_map:
emp_holiday_list = emp_det.holiday_list if emp_det.holiday_list else default_holiday_list
if emp_holiday_list in holiday_map and (day+1) in holiday_map[emp_holiday_list]:
status = "Holiday"
row.append(status_map[status])
if status == "Present":
total_p += 1
elif status == "Absent":
total_a += 1
elif status == "On Leave":
total_l += 1
elif status == "Half Day":
total_p += 0.5
total_a += 0.5
total_l += 0.5
row += [total_p, total_l, total_a]
if not filters.get("employee"):
filters.update({"employee": emp})
conditions += " and employee = %(employee)s"
elif not filters.get("employee") == emp:
filters.update({"employee": emp})
leave_details = frappe.db.sql("""select leave_type, status, count(*) as count from `tabAttendance`\
where leave_type is not NULL %s group by leave_type, status""" % conditions, filters, as_dict=1)
time_default_counts = frappe.db.sql("""select (select count(*) from `tabAttendance` where \
late_entry = 1 %s) as late_entry_count, (select count(*) from tabAttendance where \
early_exit = 1 %s) as early_exit_count""" % (conditions, conditions), filters)
leaves = {}
for d in leave_details:
if d.status == "Half Day":
d.count = d.count * 0.5
if d.leave_type in leaves:
leaves[d.leave_type] += d.count
else:
leaves[d.leave_type] = d.count
for d in leave_list:
if d in leaves:
row.append(leaves[d])
else:
row.append("0.0")
row.extend([time_default_counts[0][0],time_default_counts[0][1]])
data.append(row)
return columns, data
def before_save(self):
if self.get("__islocal") or not self.stock_uom:
self.stock_uom = frappe.get_cached_value('Item', self.item_code,
'stock_uom')
self.set_projected_qty()
def update_default_domain_actions_and_get_state():
domain = frappe.get_cached_value('Company', erpnext.get_default_company(), 'domain')
update_domain_actions(domain)
return get_domain_actions_state(domain)
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_rootwise_opening_balances(filters, report_type):
additional_conditions = ""
if not filters.show_unclosed_fy_pl_balances:
additional_conditions = " and posting_date >= %(year_start_date)s" \
if report_type == "Profit and Loss" else ""
if not flt(filters.with_period_closing_entry):
additional_conditions += " and ifnull(voucher_type, '')!='Period Closing Voucher'"
if filters.cost_center:
lft, rgt = frappe.db.get_value('Cost Center', filters.cost_center,
['lft', 'rgt'])
additional_conditions += """ and cost_center in (select name from `tabCost Center`
where lft >= %s and rgt <= %s)""" % (lft, rgt)
if filters.finance_book:
fb_conditions = " AND finance_book = %(finance_book)s"
if filters.include_default_book_entries:
fb_conditions = " AND (finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)"
additional_conditions += fb_conditions
accounting_dimensions = get_accounting_dimensions(as_list=False)
query_filters = {
"company":
filters.company,
"from_date":
filters.from_date,
"report_type":
report_type,
"year_start_date":
filters.year_start_date,
"finance_book":
filters.finance_book,
"company_fb":
frappe.db.get_value("Company", filters.company, 'default_finance_book')
}
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))
additional_conditions += "and {0} in %({0})s".format(
dimension.fieldname)
query_filters.update(
{dimension.fieldname: filters.get(dimension.fieldname)})
gle = frappe.db.sql("""
select
account, sum(debit) as opening_debit, sum(credit) as opening_credit
from `tabGL Entry`
where
company=%(company)s
{additional_conditions}
and (posting_date < %(from_date)s or ifnull(is_opening, 'No') = 'Yes')
and account in (select name from `tabAccount` where report_type=%(report_type)s)
group by account""".format(additional_conditions=additional_conditions),
query_filters,
as_dict=True)
opening = frappe._dict()
for d in gle:
opening.setdefault(d.account, d)
return opening
def set_payable_account(self):
if not self.payable_account and not self.is_paid:
self.payable_account = frappe.get_cached_value(
'Company', self.company,
'default_expense_claim_payable_account')
def set_cost_center(self):
if not self.cost_center:
self.cost_center = frappe.get_cached_value('Company', self.company,
'cost_center')
def get_basic_details(args, item, overwrite_warehouse=True):
"""
:param args: {
"item_code": "",
"warehouse": None,
"customer": "",
"conversion_rate": 1.0,
"selling_price_list": None,
"price_list_currency": None,
"price_list_uom_dependant": None,
"plc_conversion_rate": 1.0,
"doctype": "",
"name": "",
"supplier": None,
"transaction_date": None,
"conversion_rate": 1.0,
"buying_price_list": None,
"is_subcontracted": "Yes" / "No",
"ignore_pricing_rule": 0/1
"project": "",
barcode: "",
serial_no: "",
currency: "",
update_stock: "",
price_list: "",
company: "",
order_type: "",
is_pos: "",
project: "",
qty: "",
stock_qty: "",
conversion_factor: "",
against_blanket_order: 0/1
}
:param item: `item_code` of Item object
:return: frappe._dict
"""
if not item:
item = frappe.get_doc("Item", args.get("item_code"))
if item.variant_of:
item.update_template_tables()
item_defaults = get_item_defaults(item.name, args.company)
item_group_defaults = get_item_group_defaults(item.name, args.company)
brand_defaults = get_brand_defaults(item.name, args.company)
defaults = frappe._dict({
'item_defaults': item_defaults,
'item_group_defaults': item_group_defaults,
'brand_defaults': brand_defaults
})
warehouse = get_item_warehouse(item, args, overwrite_warehouse, defaults)
if args.get('doctype') == "Material Request" and not args.get('material_request_type'):
args['material_request_type'] = frappe.db.get_value('Material Request',
args.get('name'), 'material_request_type', cache=True)
expense_account = None
if args.get('doctype') == 'Purchase Invoice' and item.is_fixed_asset:
from erpnext.assets.doctype.asset_category.asset_category import get_asset_category_account
expense_account = get_asset_category_account(fieldname = "fixed_asset_account", item = args.item_code, company= args.company)
#Set the UOM to the Default Sales UOM or Default Purchase UOM if configured in the Item Master
if not args.get('uom'):
if args.get('doctype') in sales_doctypes:
args.uom = item.sales_uom if item.sales_uom else item.stock_uom
elif (args.get('doctype') in ['Purchase Order', 'Purchase Receipt', 'Purchase Invoice']) or \
(args.get('doctype') == 'Material Request' and args.get('material_request_type') == 'Purchase'):
args.uom = item.purchase_uom if item.purchase_uom else item.stock_uom
else:
args.uom = item.stock_uom
if (args.get("batch_no") and
item.name != frappe.get_cached_value('Batch', args.get("batch_no"), 'item')):
args['batch_no'] = ''
out = frappe._dict({
"item_code": item.name,
"item_name": item.item_name,
"description": cstr(item.description).strip(),
"image": cstr(item.image).strip(),
"warehouse": warehouse,
"income_account": get_default_income_account(args, item_defaults, item_group_defaults, brand_defaults),
"expense_account": expense_account or get_default_expense_account(args, item_defaults, item_group_defaults, brand_defaults) ,
"discount_account": None or get_default_discount_account(args, item_defaults),
"cost_center": get_default_cost_center(args, item_defaults, item_group_defaults, brand_defaults),
'has_serial_no': item.has_serial_no,
'has_batch_no': item.has_batch_no,
"batch_no": args.get("batch_no"),
"uom": args.uom,
"min_order_qty": flt(item.min_order_qty) if args.doctype == "Material Request" else "",
"qty": flt(args.qty) or 1.0,
"stock_qty": flt(args.qty) or 1.0,
"price_list_rate": 0.0,
"base_price_list_rate": 0.0,
"rate": 0.0,
"base_rate": 0.0,
"amount": 0.0,
"base_amount": 0.0,
"net_rate": 0.0,
"net_amount": 0.0,
"discount_percentage": 0.0,
"supplier": get_default_supplier(args, item_defaults, item_group_defaults, brand_defaults),
"update_stock": args.get("update_stock") if args.get('doctype') in ['Sales Invoice', 'Purchase Invoice'] else 0,
"delivered_by_supplier": item.delivered_by_supplier if args.get("doctype") in ["Sales Order", "Sales Invoice"] else 0,
"is_fixed_asset": item.is_fixed_asset,
"last_purchase_rate": item.last_purchase_rate if args.get("doctype") in ["Purchase Order"] else 0,
"transaction_date": args.get("transaction_date"),
"against_blanket_order": args.get("against_blanket_order"),
"bom_no": item.get("default_bom"),
"weight_per_unit": args.get("weight_per_unit") or item.get("weight_per_unit"),
"weight_uom": args.get("weight_uom") or item.get("weight_uom")
})
if item.get("enable_deferred_revenue") or item.get("enable_deferred_expense"):
out.update(calculate_service_end_date(args, item))
# calculate conversion factor
if item.stock_uom == args.uom:
out.conversion_factor = 1.0
else:
out.conversion_factor = args.conversion_factor or \
get_conversion_factor(item.name, args.uom).get("conversion_factor")
args.conversion_factor = out.conversion_factor
out.stock_qty = out.qty * out.conversion_factor
# calculate last purchase rate
if args.get('doctype') in purchase_doctypes:
from erpnext.buying.doctype.purchase_order.purchase_order import item_last_purchase_rate
out.last_purchase_rate = item_last_purchase_rate(args.name, args.conversion_rate, item.name, out.conversion_factor)
# if default specified in item is for another company, fetch from company
for d in [
["Account", "income_account", "default_income_account"],
["Account", "expense_account", "default_expense_account"],
["Cost Center", "cost_center", "cost_center"],
["Warehouse", "warehouse", ""]]:
if not out[d[1]]:
out[d[1]] = frappe.get_cached_value('Company', args.company, d[2]) if d[2] else None
for fieldname in ("item_name", "item_group", "barcodes", "brand", "stock_uom"):
out[fieldname] = item.get(fieldname)
if args.get("manufacturer"):
part_no = get_item_manufacturer_part_no(args.get("item_code"), args.get("manufacturer"))
if part_no:
out["manufacturer_part_no"] = part_no
else:
out["manufacturer_part_no"] = None
out["manufacturer"] = None
else:
data = frappe.get_value("Item", item.name,
["default_item_manufacturer", "default_manufacturer_part_no"] , as_dict=1)
if data:
out.update({
"manufacturer": data.default_item_manufacturer,
"manufacturer_part_no": data.default_manufacturer_part_no
})
child_doctype = args.doctype + ' Item'
meta = frappe.get_meta(child_doctype)
if meta.get_field("barcode"):
update_barcode_value(out)
if out.get("weight_per_unit"):
out['total_weight'] = out.weight_per_unit * out.stock_qty
return out
def get_customer_outstanding(customer,
company,
ignore_outstanding_sales_order=False,
cost_center=None):
# Outstanding based on GL Entries
cond = ""
if cost_center:
lft, rgt = frappe.get_cached_value("Cost Center", cost_center,
['lft', 'rgt'])
cond = """ and cost_center in (select name from `tabCost Center` where
lft >= {0} and rgt <= {1})""".format(lft, rgt)
outstanding_based_on_gle = frappe.db.sql(
"""
select sum(debit) - sum(credit)
from `tabGL Entry` where party_type = 'Customer'
and party = %s and company=%s {0}""".format(cond), (customer, company))
outstanding_based_on_gle = flt(
outstanding_based_on_gle[0][0]) if outstanding_based_on_gle else 0
# Outstanding based on Sales Order
outstanding_based_on_so = 0.0
# if credit limit check is bypassed at sales order level,
# we should not consider outstanding Sales Orders, when customer credit balance report is run
if not ignore_outstanding_sales_order:
outstanding_based_on_so = frappe.db.sql(
"""
select sum(base_grand_total*(100 - per_billed)/100)
from `tabSales Order`
where customer=%s and docstatus = 1 and company=%s
and per_billed < 100 and status != 'Closed'""", (customer, company))
outstanding_based_on_so = flt(
outstanding_based_on_so[0][0]) if outstanding_based_on_so else 0.0
# Outstanding based on Delivery Note, which are not created against Sales Order
unmarked_delivery_note_items = frappe.db.sql("""select
dn_item.name, dn_item.amount, dn.base_net_total, dn.base_grand_total
from `tabDelivery Note` dn, `tabDelivery Note Item` dn_item
where
dn.name = dn_item.parent
and dn.customer=%s and dn.company=%s
and dn.docstatus = 1 and dn.status not in ('Closed', 'Stopped')
and ifnull(dn_item.against_sales_order, '') = ''
and ifnull(dn_item.against_sales_invoice, '') = ''
""", (customer, company),
as_dict=True)
outstanding_based_on_dn = 0.0
for dn_item in unmarked_delivery_note_items:
si_amount = frappe.db.sql(
"""select sum(amount)
from `tabSales Invoice Item`
where dn_detail = %s and docstatus = 1""", dn_item.name)[0][0]
if flt(dn_item.amount) > flt(si_amount) and dn_item.base_net_total:
outstanding_based_on_dn += ((flt(dn_item.amount) - flt(si_amount)) \
/ dn_item.base_net_total) * dn_item.base_grand_total
return outstanding_based_on_gle + outstanding_based_on_so + outstanding_based_on_dn
def get_default_currency():
'''Returns the currency of the default company'''
company = get_default_company()
if company:
return frappe.get_cached_value('Company', company, 'default_currency')
def _execute(filters=None,
additional_table_columns=None,
additional_query_columns=None):
if not filters: filters = {}
columns = get_columns(additional_table_columns, filters)
company_currency = frappe.get_cached_value('Company',
filters.get('company'),
'default_currency')
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns,
company_currency)
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
total_row_map = {}
skip_total_row = 0
prev_group_by_value = ''
if filters.get('group_by'):
grand_total = get_grand_total(filters, 'Sales Invoice')
customer_details = get_customer_details()
item_details = get_item_details()
for d in item_list:
customer_record = customer_details.get(d.customer)
item_record = item_details.get(d.item_code)
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = {
'item_code': d.item_code,
'item_name': item_record.item_name,
'item_group': item_record.item_group,
'description': d.description,
'invoice': d.parent,
'posting_date': d.posting_date,
'customer': d.customer,
'customer_name': customer_record.customer_name,
'customer_group': customer_record.customer_group,
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: d.get(col)})
row.update({
'debit_to':
d.debit_to,
'mode_of_payment':
", ".join(mode_of_payments.get(d.parent, [])),
'territory':
d.territory,
'project':
d.project,
'company':
d.company,
'sales_order':
d.sales_order,
'delivery_note':
d.delivery_note,
'income_account':
d.income_account,
'cost_center':
d.cost_center,
'stock_qty':
d.stock_qty,
'stock_uom':
d.stock_uom
})
if d.stock_uom != d.uom and d.stock_qty:
row.update({
'rate': (d.base_net_rate * d.qty) / d.stock_qty,
'amount': d.base_net_amount
})
else:
row.update({'rate': d.base_net_rate, 'amount': d.base_net_amount})
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row.update({
frappe.scrub(tax + ' Rate'):
item_tax.get('tax_rate', 0),
frappe.scrub(tax + ' Amount'):
item_tax.get('tax_amount', 0),
})
total_tax += flt(item_tax.get('tax_amount'))
row.update({
'total_tax': total_tax,
'total': d.base_net_amount + total_tax,
'currency': company_currency
})
if filters.get('group_by'):
row.update({'percent_gt': flt(row['total'] / grand_total) * 100})
group_by_field, subtotal_display_field = get_group_by_and_display_fields(
filters)
data, prev_group_by_value = add_total_row(data, filters,
prev_group_by_value, d,
total_row_map,
group_by_field,
subtotal_display_field,
grand_total, tax_columns)
add_sub_total_row(row, total_row_map, d.get(group_by_field, ''),
tax_columns)
data.append(row)
if filters.get('group_by') and item_list:
total_row = total_row_map.get(prev_group_by_value
or d.get('item_name'))
total_row['percent_gt'] = flt(total_row['total'] / grand_total * 100)
data.append(total_row)
data.append({})
add_sub_total_row(total_row, total_row_map, 'total_row', tax_columns)
data.append(total_row_map.get('total_row'))
skip_total_row = 1
return columns, data, None, None, None, skip_total_row
def make_quotation(domain):
# get open opportunites
opportunity = get_random("Opportunity", {
"status": "Open",
"with_items": 1
})
if opportunity:
from erpnext.crm.doctype.opportunity.opportunity import make_quotation
qtn = frappe.get_doc(make_quotation(opportunity))
qtn.insert()
frappe.db.commit()
qtn.submit()
frappe.db.commit()
else:
# make new directly
# get customer, currency and exchange_rate
customer = get_random("Customer")
company_currency = frappe.get_cached_value(
"Company", erpnext.get_default_company(), "default_currency")
party_account_currency = get_party_account_currency(
"Customer", customer, erpnext.get_default_company())
if company_currency == party_account_currency:
exchange_rate = 1
else:
exchange_rate = get_exchange_rate(party_account_currency,
company_currency,
args="for_selling")
qtn = frappe.get_doc({
"creation": frappe.flags.current_date,
"doctype": "Quotation",
"quotation_to": "Customer",
"party_name": customer,
"currency": party_account_currency or company_currency,
"conversion_rate": exchange_rate,
"order_type": "Sales",
"transaction_date": frappe.flags.current_date,
})
add_random_children(
qtn,
"items",
rows=3,
randomize={
"qty": (1, 5),
"item_code": ("Item", {
"has_variants": "0",
"is_fixed_asset": 0,
"domain": domain
}),
},
unique="item_code",
)
qtn.insert()
frappe.db.commit()
qtn.submit()
frappe.db.commit()
def get_outstanding_invoices(party_type,
party,
account,
condition=None,
filters=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice",
"outstanding_amount") or 2
if account:
root_type = frappe.get_cached_value("Account", account, "root_type")
party_account_type = "Receivable" if root_type == "Asset" else "Payable"
else:
party_account_type = erpnext.get_party_account_type(party_type)
if party_account_type == 'Receivable':
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
held_invoices = get_held_invoices(party_type, party)
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_date, due_date,
ifnull(sum({dr_or_cr}), 0) as invoice_amount
from
`tabGL Entry`
where
party_type = %(party_type)s and party = %(party)s
and account = %(account)s and {dr_or_cr} > 0
{condition}
and ((voucher_type = 'Journal Entry'
and (against_voucher = '' or against_voucher is null))
or (voucher_type not in ('Journal Entry', 'Payment Entry')))
group by voucher_type, voucher_no
order by posting_date, name""".format(dr_or_cr=dr_or_cr,
condition=condition or ""), {
"party_type": party_type,
"party": party,
"account": account,
},
as_dict=True)
payment_entries = frappe.db.sql("""
select against_voucher_type, against_voucher,
ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
from `tabGL Entry`
where party_type = %(party_type)s and party = %(party)s
and account = %(account)s
and {payment_dr_or_cr} > 0
and against_voucher is not null and against_voucher != ''
group by against_voucher_type, against_voucher
""".format(payment_dr_or_cr=payment_dr_or_cr), {
"party_type": party_type,
"party": party,
"account": account
},
as_dict=True)
pe_map = frappe._dict()
for d in payment_entries:
pe_map.setdefault((d.against_voucher_type, d.against_voucher),
d.payment_amount)
for d in invoice_list:
payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
if outstanding_amount > 0.5 / (10**precision):
if (filters and filters.get("outstanding_amt_greater_than")
and not (outstanding_amount >=
filters.get("outstanding_amt_greater_than")
and outstanding_amount <=
filters.get("outstanding_amt_less_than"))):
continue
if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
outstanding_invoices.append(
frappe._dict({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
'invoice_amount': flt(d.invoice_amount),
'payment_amount': payment_amount,
'outstanding_amount': outstanding_amount,
'due_date': d.due_date
}))
outstanding_invoices = sorted(
outstanding_invoices,
key=lambda k: k['due_date'] or getdate(nowdate()))
return outstanding_invoices
def autoname(self):
company_abbr = frappe.get_cached_value('Company', self.company, "abbr")
self.name = " - ".join([self.period_name, company_abbr])
def make_item_gl_entries(self, gl_entries, warehouse_account=None):
if erpnext.is_perpetual_inventory_enabled(self.company):
stock_rbnb = self.get_company_default(
"stock_received_but_not_billed")
landed_cost_entries = get_item_account_wise_additional_cost(
self.name)
expenses_included_in_valuation = self.get_company_default(
"expenses_included_in_valuation")
warehouse_with_no_account = []
stock_items = self.get_stock_items()
provisional_accounting_for_non_stock_items = cint(
frappe.db.get_value(
"Company", self.company,
"enable_provisional_accounting_for_non_stock_items"))
for d in self.get("items"):
if d.item_code in stock_items and flt(d.valuation_rate) and flt(
d.qty):
if warehouse_account.get(d.warehouse):
stock_value_diff = frappe.db.get_value(
"Stock Ledger Entry",
{
"voucher_type": "Purchase Receipt",
"voucher_no": self.name,
"voucher_detail_no": d.name,
"warehouse": d.warehouse,
"is_cancelled": 0,
},
"stock_value_difference",
)
warehouse_account_name = warehouse_account[
d.warehouse]["account"]
warehouse_account_currency = warehouse_account[
d.warehouse]["account_currency"]
supplier_warehouse_account = warehouse_account.get(
self.supplier_warehouse, {}).get("account")
supplier_warehouse_account_currency = warehouse_account.get(
self.supplier_warehouse, {}).get("account_currency")
remarks = self.get("remarks") or _(
"Accounting Entry for Stock")
# If PR is sub-contracted and fg item rate is zero
# in that case if account for source and target warehouse are same,
# then GL entries should not be posted
if (flt(stock_value_diff) == flt(d.rm_supp_cost)
and warehouse_account.get(self.supplier_warehouse)
and warehouse_account_name
== supplier_warehouse_account):
continue
self.add_gl_entry(
gl_entries=gl_entries,
account=warehouse_account_name,
cost_center=d.cost_center,
debit=stock_value_diff,
credit=0.0,
remarks=remarks,
against_account=stock_rbnb,
account_currency=warehouse_account_currency,
item=d,
)
# GL Entry for from warehouse or Stock Received but not billed
# Intentionally passed negative debit amount to avoid incorrect GL Entry validation
credit_currency = (get_account_currency(
warehouse_account[d.from_warehouse]["account"])
if d.from_warehouse else
get_account_currency(stock_rbnb))
credit_amount = (
flt(d.base_net_amount, d.precision("base_net_amount"))
if credit_currency == self.company_currency else flt(
d.net_amount, d.precision("net_amount")))
if credit_amount:
account = warehouse_account[d.from_warehouse][
"account"] if d.from_warehouse else stock_rbnb
self.add_gl_entry(
gl_entries=gl_entries,
account=account,
cost_center=d.cost_center,
debit=-1 * flt(d.base_net_amount,
d.precision("base_net_amount")),
credit=0.0,
remarks=remarks,
against_account=warehouse_account_name,
debit_in_account_currency=-1 * credit_amount,
account_currency=credit_currency,
item=d,
)
# Amount added through landed-cos-voucher
if d.landed_cost_voucher_amount and landed_cost_entries:
for account, amount in iteritems(
landed_cost_entries[(d.item_code, d.name)]):
account_currency = get_account_currency(account)
credit_amount = (flt(amount["base_amount"]) if (
amount["base_amount"]
or account_currency != self.company_currency)
else flt(amount["amount"]))
self.add_gl_entry(
gl_entries=gl_entries,
account=account,
cost_center=d.cost_center,
debit=0.0,
credit=credit_amount,
remarks=remarks,
against_account=warehouse_account_name,
credit_in_account_currency=flt(
amount["amount"]),
account_currency=account_currency,
project=d.project,
item=d,
)
# sub-contracting warehouse
if flt(d.rm_supp_cost) and warehouse_account.get(
self.supplier_warehouse):
self.add_gl_entry(
gl_entries=gl_entries,
account=supplier_warehouse_account,
cost_center=d.cost_center,
debit=0.0,
credit=flt(d.rm_supp_cost),
remarks=remarks,
against_account=warehouse_account_name,
account_currency=
supplier_warehouse_account_currency,
item=d,
)
# divisional loss adjustment
valuation_amount_as_per_doc = (
flt(d.base_net_amount, d.precision("base_net_amount"))
+ flt(d.landed_cost_voucher_amount) +
flt(d.rm_supp_cost) + flt(d.item_tax_amount))
divisional_loss = flt(
valuation_amount_as_per_doc - stock_value_diff,
d.precision("base_net_amount"))
if divisional_loss:
if self.is_return or flt(d.item_tax_amount):
loss_account = expenses_included_in_valuation
else:
loss_account = (self.get_company_default(
"default_expense_account",
ignore_validation=True) or stock_rbnb)
cost_center = d.cost_center or frappe.get_cached_value(
"Company", self.company, "cost_center")
self.add_gl_entry(
gl_entries=gl_entries,
account=loss_account,
cost_center=cost_center,
debit=divisional_loss,
credit=0.0,
remarks=remarks,
against_account=warehouse_account_name,
account_currency=credit_currency,
project=d.project,
item=d,
)
elif (d.warehouse not in warehouse_with_no_account or
d.rejected_warehouse not in warehouse_with_no_account):
warehouse_with_no_account.append(d.warehouse)
elif (d.item_code not in stock_items and not d.is_fixed_asset
and flt(d.qty)
and provisional_accounting_for_non_stock_items):
self.add_provisional_gl_entry(
d, gl_entries, self.posting_date,
d.get("provisional_expense_account"))
if warehouse_with_no_account:
frappe.msgprint(
_("No accounting entries for the following warehouses") +
": \n" + "\n".join(warehouse_with_no_account))
def get_payable_account(company):
return frappe.get_cached_value('Company', company,
'default_payable_account')
def set_pos_fields(self, for_validate=False):
"""Set retail related fields from POS Profiles"""
from erpnext.stock.get_item_details import get_pos_profile_item_details, get_pos_profile
if not self.pos_profile:
pos_profile = get_pos_profile(self.company) or {}
self.pos_profile = pos_profile.get('name')
profile = {}
if self.pos_profile:
profile = frappe.get_doc('POS Profile', self.pos_profile)
if not self.get('payments') and not for_validate:
update_multi_mode_option(self, profile)
if self.is_return and not for_validate:
add_return_modes(self, profile)
if profile:
if not for_validate and not self.customer:
self.customer = profile.customer
self.ignore_pricing_rule = profile.ignore_pricing_rule
self.account_for_change_amount = profile.get(
'account_for_change_amount') or self.account_for_change_amount
self.set_warehouse = profile.get('warehouse') or self.set_warehouse
for fieldname in ('currency', 'letter_head', 'tc_name', 'company',
'select_print_heading', 'write_off_account',
'taxes_and_charges', 'write_off_cost_center',
'apply_discount_on', 'cost_center',
'tax_category', 'ignore_pricing_rule',
'company_address', 'update_stock'):
if not for_validate:
self.set(fieldname, profile.get(fieldname))
if self.customer:
customer_price_list, customer_group = frappe.db.get_value(
"Customer", self.customer,
['default_price_list', 'customer_group'])
customer_group_price_list = frappe.db.get_value(
"Customer Group", customer_group, 'default_price_list')
selling_price_list = customer_price_list or customer_group_price_list or profile.get(
'selling_price_list')
else:
selling_price_list = profile.get('selling_price_list')
if selling_price_list:
self.set('selling_price_list', selling_price_list)
# set pos values in items
for item in self.get("items"):
if item.get('item_code'):
profile_details = get_pos_profile_item_details(
profile.get("company"), frappe._dict(item.as_dict()),
profile)
for fname, val in iteritems(profile_details):
if (not for_validate) or (for_validate
and not item.get(fname)):
item.set(fname, val)
# fetch terms
if self.tc_name and not self.terms:
self.terms = frappe.db.get_value("Terms and Conditions",
self.tc_name, "terms")
# fetch charges
if self.taxes_and_charges and not len(self.get("taxes")):
self.set_taxes()
if not self.account_for_change_amount:
self.account_for_change_amount = frappe.get_cached_value(
'Company', self.company, 'default_cash_account')
return profile
def get_state_optional_field_value(workflow_name, state):
return frappe.get_cached_value('Workflow Document State', {
'parent': workflow_name,
'state': state
}, 'is_optional_state')
def get_invoice_dict(self, row=None):
def get_item_dict():
default_uom = frappe.db.get_single_value("Stock Settings",
"stock_uom") or _("Nos")
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)))
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
})
if not row:
return None
party_type = "Customer"
income_expense_account_field = "income_account"
if self.invoice_type == "Purchase":
party_type = "Supplier"
income_expense_account_field = "expense_account"
item = get_item_dict()
args = 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(party_type):
row.party,
"doctype":
"Sales Invoice"
if self.invoice_type == "Sales" else "Purchase Invoice",
"currency":
frappe.get_cached_value('Company', self.company,
"default_currency")
})
accounting_dimension = get_accounting_dimensions()
for dimension in accounting_dimension:
args.update({dimension: item.get(dimension)})
if self.invoice_type == "Sales":
args["is_pos"] = 0
return args
def _valid_for_reserve(item_code, warehouse):
if item_code and warehouse and [item_code, warehouse] not in item_wh_list \
and frappe.get_cached_value("Item", item_code, "is_stock_item"):
item_wh_list.append([item_code, warehouse])
def get_default_currency():
"""Returns the currency of the default company"""
company = get_default_company()
if company:
return frappe.get_cached_value("Company", company, "default_currency")
def get_company_default_inventory_account(company):
return frappe.get_cached_value('Company', company,
'default_inventory_account')
def _execute(filters,
additional_table_columns=None,
additional_query_columns=None):
if not filters:
filters = frappe._dict({})
invoice_list = get_invoices(filters, additional_query_columns)
columns, income_accounts, tax_accounts, unrealized_profit_loss_accounts = get_columns(
invoice_list, additional_table_columns)
if not invoice_list:
msgprint(_("No record found"))
return columns, invoice_list
invoice_income_map = get_invoice_income_map(invoice_list)
internal_invoice_map = get_internal_invoice_map(invoice_list)
invoice_income_map, invoice_tax_map = get_invoice_tax_map(
invoice_list, invoice_income_map, income_accounts)
# Cost Center & Warehouse Map
invoice_cc_wh_map = get_invoice_cc_wh_map(invoice_list)
invoice_so_dn_map = get_invoice_so_dn_map(invoice_list)
company_currency = frappe.get_cached_value('Company',
filters.get("company"),
"default_currency")
mode_of_payments = get_mode_of_payments([inv.name for inv in invoice_list])
data = []
for inv in invoice_list:
# invoice details
sales_order = list(
set(invoice_so_dn_map.get(inv.name, {}).get("sales_order", [])))
delivery_note = list(
set(invoice_so_dn_map.get(inv.name, {}).get("delivery_note", [])))
cost_center = list(
set(invoice_cc_wh_map.get(inv.name, {}).get("cost_center", [])))
warehouse = list(
set(invoice_cc_wh_map.get(inv.name, {}).get("warehouse", [])))
row = {
'invoice': inv.name,
'posting_date': inv.posting_date,
'customer': inv.customer,
'customer_name': inv.customer_name
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: inv.get(col)})
row.update({
'customer_group':
inv.get("customer_group"),
'territory':
inv.get("territory"),
'tax_id':
inv.get("tax_id"),
'receivable_account':
inv.debit_to,
'mode_of_payment':
", ".join(mode_of_payments.get(inv.name, [])),
'project':
inv.project,
'owner':
inv.owner,
'remarks':
inv.remarks,
'sales_order':
", ".join(sales_order),
'delivery_note':
", ".join(delivery_note),
'cost_center':
", ".join(cost_center),
'warehouse':
", ".join(warehouse),
'currency':
company_currency
})
# map income values
base_net_total = 0
for income_acc in income_accounts:
if inv.is_internal_customer and inv.company == inv.represents_company:
income_amount = 0
else:
income_amount = flt(
invoice_income_map.get(inv.name, {}).get(income_acc))
base_net_total += income_amount
row.update({frappe.scrub(income_acc): income_amount})
# Add amount in unrealized account
for account in unrealized_profit_loss_accounts:
row.update({
frappe.scrub(account):
flt(internal_invoice_map.get((inv.name, account)))
})
# net total
row.update({'net_total': base_net_total or inv.base_net_total})
# tax account
total_tax = 0
for tax_acc in tax_accounts:
if tax_acc not in income_accounts:
tax_amount_precision = get_field_precision(
frappe.get_meta("Sales Taxes and Charges").get_field(
"tax_amount"),
currency=company_currency) or 2
tax_amount = flt(
invoice_tax_map.get(inv.name, {}).get(tax_acc),
tax_amount_precision)
total_tax += tax_amount
row.update({frappe.scrub(tax_acc): tax_amount})
# total tax, grand total, outstanding amount & rounded total
row.update({
'tax_total': total_tax,
'grand_total': inv.base_grand_total,
'rounded_total': inv.base_rounded_total,
'outstanding_amount': inv.outstanding_amount
})
data.append(row)
return columns, data
def get_data(self, party_naming_by, args):
from erpnext.accounts.utils import get_currency_precision
self.currency_precision = get_currency_precision() or 2
self.dr_or_cr = "debit" if args.get(
"party_type") == "Customer" else "credit"
future_vouchers = self.get_entries_after(self.filters.report_date,
args.get("party_type"))
if not self.filters.get("company"):
self.filters["company"] = frappe.db.get_single_value(
'Global Defaults', 'default_company')
self.company_currency = frappe.get_cached_value(
'Company', self.filters.get("company"), "default_currency")
return_entries = self.get_return_entries(args.get("party_type"))
data = []
self.pdc_details = get_pdc_details(args.get("party_type"),
self.filters.report_date)
gl_entries_data = self.get_entries_till(self.filters.report_date,
args.get("party_type"))
if gl_entries_data:
voucher_nos = [d.voucher_no for d in gl_entries_data] or []
dn_details = get_dn_details(args.get("party_type"), voucher_nos)
self.voucher_details = get_voucher_details(args.get("party_type"),
voucher_nos, dn_details)
if self.filters.based_on_payment_terms and gl_entries_data:
self.payment_term_map = self.get_payment_term_detail(voucher_nos)
for gle in gl_entries_data:
if self.is_receivable_or_payable(gle, self.dr_or_cr,
future_vouchers, return_entries):
outstanding_amount, credit_note_amount, payment_amount = self.get_outstanding_amount(
gle, self.filters.report_date, self.dr_or_cr,
return_entries)
temp_outstanding_amt = outstanding_amount
temp_credit_note_amt = credit_note_amount
if abs(outstanding_amount) > 0.1 / 10**self.currency_precision:
if self.filters.based_on_payment_terms and self.payment_term_map.get(
gle.voucher_no):
for d in self.payment_term_map.get(gle.voucher_no):
# Allocate payment amount based on payment terms(FIFO order)
payment_amount, d.payment_amount = self.allocate_based_on_fifo(
payment_amount, d.payment_term_amount)
term_outstanding_amount = d.payment_term_amount - d.payment_amount
# Allocate credit note based on payment terms(FIFO order)
credit_note_amount, d.credit_note_amount = self.allocate_based_on_fifo(
credit_note_amount, term_outstanding_amount)
term_outstanding_amount -= d.credit_note_amount
row_outstanding = term_outstanding_amount
# Allocate PDC based on payment terms(FIFO order)
d.pdc_details, d.pdc_amount = self.allocate_pdc_amount_in_fifo(
gle, row_outstanding)
if term_outstanding_amount > 0:
row = self.prepare_row(
party_naming_by, args, gle,
term_outstanding_amount,
d.credit_note_amount, d.due_date,
d.payment_amount, d.payment_term_amount,
d.description, d.pdc_amount, d.pdc_details)
data.append(row)
if credit_note_amount:
row = self.prepare_row_without_payment_terms(
party_naming_by, args, gle,
temp_outstanding_amt, temp_credit_note_amt)
data.append(row)
else:
row = self.prepare_row_without_payment_terms(
party_naming_by, args, gle, outstanding_amount,
credit_note_amount)
data.append(row)
return data
