def _execute(filters=None):
if not filters: filters = {}
columns = get_columns()
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
data = []
for d in item_list:
row = [d.gst_hsn_code, d.description, d.stock_uom, d.stock_qty]
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
total_tax += flt(item_tax.get("tax_amount"))
row += [d.base_net_amount + total_tax]
row += [d.base_net_amount]
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row += [item_tax.get("tax_amount", 0)]
data.append(row)
if data:
data = get_merged_data(columns, data) # merge same hsn code data
return columns, data
def get_itemised_tax_breakup_html(doc):
if not doc.taxes:
return
frappe.flags.company = doc.company
# get headers
tax_accounts = []
for tax in doc.taxes:
if getattr(tax, "category", None) and tax.category=="Valuation":
continue
if tax.description not in tax_accounts:
tax_accounts.append(tax.description)
headers = get_itemised_tax_breakup_header(doc.doctype + " Item", tax_accounts)
# get tax breakup data
itemised_tax, itemised_taxable_amount = get_itemised_tax_breakup_data(doc)
get_rounded_tax_amount(itemised_tax, doc.precision("tax_amount", "taxes"))
frappe.flags.company = None
return frappe.render_template(
"templates/includes/itemised_tax_breakup.html", dict(
headers=headers,
itemised_tax=itemised_tax,
itemised_taxable_amount=itemised_taxable_amount,
tax_accounts=tax_accounts,
company_currency=erpnext.get_company_currency(doc.company)
)
)
def get_serialized_values(self, sle):
incoming_rate = flt(sle.incoming_rate)
actual_qty = flt(sle.actual_qty)
serial_no = cstr(sle.serial_no).split("\n")
if incoming_rate < 0:
# wrong incoming rate
incoming_rate = self.valuation_rate
stock_value_change = 0
if incoming_rate:
stock_value_change = actual_qty * incoming_rate
elif actual_qty < 0:
# In case of delivery/stock issue, get average purchase rate
# of serial nos of current entry
stock_value_change = -1 * flt(frappe.db.sql("""select sum(purchase_rate)
from `tabSerial No` where name in (%s)""" % (", ".join(["%s"]*len(serial_no))),
tuple(serial_no))[0][0])
new_stock_qty = self.qty_after_transaction + actual_qty
if new_stock_qty > 0:
new_stock_value = (self.qty_after_transaction * self.valuation_rate) + stock_value_change
if new_stock_value > 0:
# calculate new valuation rate only if stock value is positive
# else it remains the same as that of previous entry
self.valuation_rate = new_stock_value / new_stock_qty
if not self.valuation_rate and sle.voucher_detail_no:
allow_zero_rate = self.check_if_allow_zero_valuation_rate(sle.voucher_type, sle.voucher_detail_no)
if not allow_zero_rate:
self.valuation_rate = get_valuation_rate(sle.item_code, sle.warehouse,
sle.voucher_type, sle.voucher_no, self.allow_zero_rate,
currency=erpnext.get_company_currency(sle.company))
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
from erpnext.stock.stock_ledger import get_previous_sle, get_valuation_rate
if isinstance(args, basestring):
args = json.loads(args)
in_rate = 0
if (args.get("serial_no") or "").strip():
in_rate = get_avg_purchase_rate(args.get("serial_no"))
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == 'FIFO':
if previous_sle:
previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
elif valuation_method == 'Moving Average':
in_rate = previous_sle.get('valuation_rate') or 0
if not in_rate:
voucher_no = args.get('voucher_no') or args.get('name')
in_rate = get_valuation_rate(args.get('item_code'), args.get('warehouse'),
args.get('voucher_type'), voucher_no, args.get('allow_zero_valuation'),
currency=erpnext.get_company_currency(args.get('company')), company=args.get('company'))
return in_rate
def validate_quantity(doc, args, ref, valid_items, already_returned_items):
fields = ['stock_qty']
if doc.doctype in ['Purchase Receipt', 'Purchase Invoice']:
fields.extend(['received_qty', 'rejected_qty'])
already_returned_data = already_returned_items.get(args.item_code) or {}
company_currency = erpnext.get_company_currency(doc.company)
stock_qty_precision = get_field_precision(frappe.get_meta(doc.doctype + " Item")
.get_field("stock_qty"), company_currency)
for column in fields:
returned_qty = flt(already_returned_data.get(column, 0)) if len(already_returned_data) > 0 else 0
if column == 'stock_qty':
reference_qty = ref.get(column)
current_stock_qty = args.get(column)
else:
reference_qty = ref.get(column) * ref.get("conversion_factor", 1.0)
current_stock_qty = args.get(column) * args.get("conversion_factor", 1.0)
max_returnable_qty = flt(reference_qty, stock_qty_precision) - returned_qty
label = column.replace('_', ' ').title()
if reference_qty:
if flt(args.get(column)) > 0:
frappe.throw(_("{0} must be negative in return document").format(label))
elif returned_qty >= reference_qty and args.get(column):
frappe.throw(_("Item {0} has already been returned")
.format(args.item_code), StockOverReturnError)
elif abs(flt(current_stock_qty, stock_qty_precision)) > max_returnable_qty:
frappe.throw(_("Row # {0}: Cannot return more than {1} for Item {2}")
.format(args.idx, max_returnable_qty, args.item_code), StockOverReturnError)
def get_price_list_currency_and_exchange_rate(args):
if not args.price_list:
return {}
if args.doctype in ['Quotation', 'Sales Order', 'Delivery Note', 'Sales Invoice']:
args.update({"exchange_rate": "for_selling"})
elif args.doctype in ['Purchase Order', 'Purchase Receipt', 'Purchase Invoice']:
args.update({"exchange_rate": "for_buying"})
price_list_currency = get_price_list_currency(args.price_list)
price_list_uom_dependant = get_price_list_uom_dependant(args.price_list)
plc_conversion_rate = args.plc_conversion_rate
company_currency = get_company_currency(args.company)
if (not plc_conversion_rate) or (price_list_currency and args.price_list_currency \
and price_list_currency != args.price_list_currency):
# cksgb 19/09/2016: added args.transaction_date as posting_date argument for get_exchange_rate
plc_conversion_rate = get_exchange_rate(price_list_currency, company_currency,
args.transaction_date, args.exchange_rate) or plc_conversion_rate
return frappe._dict({
"price_list_currency": price_list_currency,
"price_list_uom_dependant": price_list_uom_dependant,
"plc_conversion_rate": plc_conversion_rate
})
def _get_party_details(party=None, account=None, party_type="Customer", company=None, posting_date=None,
price_list=None, currency=None, doctype=None, ignore_permissions=False, fetch_payment_terms_template=True):
out = frappe._dict(set_account_and_due_date(party, account, party_type, company, posting_date, doctype))
party = out[party_type.lower()]
if not ignore_permissions and not frappe.has_permission(party_type, "read", party):
frappe.throw(_("Not permitted for {0}").format(party), frappe.PermissionError)
party = frappe.get_doc(party_type, party)
currency = party.default_currency if party.default_currency else get_company_currency(company)
set_address_details(out, party, party_type, doctype, company)
set_contact_details(out, party, party_type)
set_other_values(out, party, party_type)
set_price_list(out, party, party_type, price_list)
out["taxes_and_charges"] = set_taxes(party.name, party_type, posting_date, company, out.customer_group, out.supplier_type)
if fetch_payment_terms_template:
out["payment_terms_template"] = get_pyt_term_template(party.name, party_type, company)
if not out.get("currency"):
out["currency"] = currency
# sales team
if party_type=="Customer":
out["sales_team"] = [{
"sales_person": d.sales_person,
"allocated_percentage": d.allocated_percentage or None
} for d in party.get("sales_team")]
return out
def validate_overlapping_shipping_rule_conditions(self):
def overlap_exists_between(num_range1, num_range2):
"""
num_range1 and num_range2 are two ranges
ranges are represented as a tuple e.g. range 100 to 300 is represented as (100, 300)
if condition num_range1 = 100 to 300
then condition num_range2 can only be like 50 to 99 or 301 to 400
hence, non-overlapping condition = (x1 <= x2 < y1 <= y2) or (y1 <= y2 < x1 <= x2)
"""
(x1, x2), (y1, y2) = num_range1, num_range2
separate = (x1 <= x2 <= y1 <= y2) or (y1 <= y2 <= x1 <= x2)
return (not separate)
overlaps = []
for i in xrange(0, len(self.conditions)):
for j in xrange(i+1, len(self.conditions)):
d1, d2 = self.conditions[i], self.conditions[j]
if d1.as_dict() != d2.as_dict():
# in our case, to_value can be zero, hence pass the from_value if so
range_a = (d1.from_value, d1.to_value or d1.from_value)
range_b = (d2.from_value, d2.to_value or d2.from_value)
if overlap_exists_between(range_a, range_b):
overlaps.append([d1, d2])
if overlaps:
company_currency = erpnext.get_company_currency(self.company)
msgprint(_("Overlapping conditions found between:"))
messages = []
for d1, d2 in overlaps:
messages.append("%s-%s = %s " % (d1.from_value, d1.to_value, fmt_money(d1.shipping_amount, currency=company_currency)) +
_("and") + " %s-%s = %s" % (d2.from_value, d2.to_value, fmt_money(d2.shipping_amount, currency=company_currency)))
msgprint("\n".join(messages), raise_exception=OverlappingConditionError)
def get_accounts_data(self, account=None):
accounts = []
self.validate_mandatory()
company_currency = erpnext.get_company_currency(self.company)
precision = get_field_precision(frappe.get_meta("Exchange Rate Revaluation Account")
.get_field("new_balance_in_base_currency"), company_currency)
for d in self.get_accounts_from_gle():
current_exchange_rate = d.balance / d.balance_in_account_currency \
if d.balance_in_account_currency else 0
new_exchange_rate = get_exchange_rate(d.account_currency, company_currency, self.posting_date)
new_balance_in_base_currency = flt(d.balance_in_account_currency * new_exchange_rate)
gain_loss = flt(new_balance_in_base_currency, precision) - flt(d.balance, precision)
if gain_loss:
accounts.append({
"account": d.account,
"party_type": d.party_type,
"party": d.party,
"account_currency": d.account_currency,
"balance_in_base_currency": d.balance,
"balance_in_account_currency": d.balance_in_account_currency,
"current_exchange_rate": current_exchange_rate,
"new_exchange_rate": new_exchange_rate,
"new_balance_in_base_currency": new_balance_in_base_currency
})
return accounts
def get_accounts_from_gle(self):
company_currency = erpnext.get_company_currency(self.company)
accounts = frappe.db.sql_list("""
select name
from tabAccount
where is_group = 0
and report_type = 'Balance Sheet'
and root_type in ('Asset', 'Liability', 'Equity')
and account_type != 'Stock'
and company=%s
and account_currency != %s
order by name""",(self.company, company_currency))
account_details = []
if accounts:
account_details = frappe.db.sql("""
select
account, party_type, party, account_currency,
sum(debit_in_account_currency) - sum(credit_in_account_currency) as balance_in_account_currency,
sum(debit) - sum(credit) as balance
from `tabGL Entry`
where account in (%s)
group by account, party_type, party
having sum(debit) != sum(credit)
order by account
""" % ', '.join(['%s']*len(accounts)), tuple(accounts), as_dict=1)
return account_details
def get_account_balance_and_party_type(account, date, company, debit=None, credit=None, exchange_rate=None):
"""Returns dict of account balance and party type to be set in Journal Entry on selection of account."""
if not frappe.has_permission("Account"):
frappe.msgprint(_("No Permission"), raise_exception=1)
company_currency = erpnext.get_company_currency(company)
account_details = frappe.db.get_value("Account", account, ["account_type", "account_currency"], as_dict=1)
if not account_details:
return
if account_details.account_type == "Receivable":
party_type = "Customer"
elif account_details.account_type == "Payable":
party_type = "Supplier"
else:
party_type = ""
grid_values = {
"balance": get_balance_on(account, date),
"party_type": party_type,
"account_type": account_details.account_type,
"account_currency": account_details.account_currency or company_currency,
# The date used to retreive the exchange rate here is the date passed in
# as an argument to this function. It is assumed to be the date on which the balance is sought
"exchange_rate": get_exchange_rate(date, account, account_details.account_currency,
company, debit=debit, credit=credit, exchange_rate=exchange_rate)
}
# un-set party if not party type
if not party_type:
grid_values["party"] = ""
return grid_values
def get_exchange_rate(posting_date, account=None, account_currency=None, company=None,
reference_type=None, reference_name=None, debit=None, credit=None, exchange_rate=None):
from erpnext.setup.utils import get_exchange_rate
account_details = frappe.db.get_value("Account", account,
["account_type", "root_type", "account_currency", "company"], as_dict=1)
if not account_details:
frappe.throw(_("Please select correct account"))
if not company:
company = account_details.company
if not account_currency:
account_currency = account_details.account_currency
company_currency = erpnext.get_company_currency(company)
if account_currency != company_currency:
if reference_type in ("Sales Invoice", "Purchase Invoice") and reference_name:
exchange_rate = frappe.db.get_value(reference_type, reference_name, "conversion_rate")
elif account_details and account_details.account_type == "Bank" and \
((account_details.root_type == "Asset" and flt(credit) > 0) or
(account_details.root_type == "Liability" and debit)):
exchange_rate = get_average_exchange_rate(account)
# The date used to retreive the exchange rate here is the date passed
# in as an argument to this function.
if not exchange_rate and account_currency and posting_date:
exchange_rate = get_exchange_rate(account_currency, company_currency, posting_date)
else:
exchange_rate = 1
# don't return None or 0 as it is multipled with a value and that value could be lost
return exchange_rate or 1
def get_data(filters):
accounts = frappe.db.sql("""select name, parent_account, account_name, root_type, report_type, lft, rgt
from `tabAccount` where company=%s order by lft""", filters.company, as_dict=True)
company_currency = erpnext.get_company_currency(filters.company)
if not accounts:
return None
accounts, accounts_by_name, parent_children_map = filter_accounts(accounts)
min_lft, max_rgt = frappe.db.sql("""select min(lft), max(rgt) from `tabAccount`
where company=%s""", (filters.company,))[0]
gl_entries_by_account = {}
set_gl_entries_by_account(filters.company, filters.from_date,
filters.to_date, min_lft, max_rgt, filters, gl_entries_by_account, ignore_closing_entries=not flt(filters.with_period_closing_entry))
opening_balances = get_opening_balances(filters)
total_row = calculate_values(accounts, gl_entries_by_account, opening_balances, filters, company_currency)
accumulate_values_into_parents(accounts, accounts_by_name)
data = prepare_data(accounts, filters, total_row, parent_children_map, company_currency)
data = filter_out_zero_value_rows(data, parent_children_map,
show_zero_values=filters.get("show_zero_values"))
return data
def get_currency(filters):
"""
Returns a dictionary containing currency information. The keys of the dict are
- company: The company for which we are fetching currency information. if no
company is specified, it will fallback to the default company.
- company currency: The functional currency of the said company.
- presentation currency: The presentation currency to use. Only currencies that
have been used for transactions will be allowed.
- report date: The report date.
:param filters: Report filters
:type filters: dict
:return: str - Currency
"""
company = get_appropriate_company(filters)
company_currency = get_company_currency(company)
presentation_currency = filters['presentation_currency'] if filters.get('presentation_currency') else company_currency
report_date = filters.get('to_date')
if not report_date:
fiscal_year_to_date = get_from_and_to_date(filters.get('to_fiscal_year'))["to_date"]
report_date = formatdate(get_datetime_str(fiscal_year_to_date), "dd-MM-yyyy")
currency_map = dict(company=company, company_currency=company_currency, presentation_currency=presentation_currency, report_date=report_date)
return currency_map
def _execute(filters=None, additional_table_columns=None, additional_query_columns=None):
if not filters: filters = {}
filters.update({"from_date": filters.get("date_range") and filters.get("date_range")[0], "to_date": filters.get("date_range") and filters.get("date_range")[1]})
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.get('company'))
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
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 = []
for d in item_list:
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 = [d.item_code, d.item_name, d.item_group, d.description, d.parent, d.posting_date, d.customer, d.customer_name]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.customer_group, d.debit_to, ", ".join(mode_of_payments.get(d.parent, [])),
d.territory, d.project, d.company, d.sales_order,
delivery_note, d.income_account, d.cost_center, d.stock_qty, d.stock_uom
]
if d.stock_uom != d.uom and d.stock_qty:
row += [(d.base_net_rate * d.qty)/d.stock_qty, d.base_net_amount]
else:
row += [d.base_net_rate, d.base_net_amount]
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row += [item_tax.get("tax_rate", 0), item_tax.get("tax_amount", 0)]
total_tax += flt(item_tax.get("tax_amount"))
row += [total_tax, d.base_net_amount + total_tax, company_currency]
data.append(row)
return columns, data
def _execute(filters=None, additional_table_columns=None, additional_query_columns=None):
if not filters: filters = {}
filters.update({"from_date": filters.get("date_range")[0], "to_date": filters.get("date_range")[1]})
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
aii_account_map = get_aii_accounts()
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency,
doctype="Purchase Invoice", tax_doctype="Purchase Taxes and Charges")
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
for d in item_list:
if not d.stock_qty:
continue
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
elif d.po_detail:
purchase_receipt = ", ".join(po_pr_map.get(d.po_detail, []))
expense_account = d.expense_account or aii_account_map.get(d.company)
row = [d.item_code, d.item_name, d.item_group, d.description, d.parent, d.posting_date, d.supplier,
d.supplier_name]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.credit_to, d.mode_of_payment, d.project, d.company, d.purchase_order,
purchase_receipt, expense_account, d.stock_qty, d.stock_uom, d.base_net_amount / d.stock_qty, d.base_net_amount
]
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row += [item_tax.get("tax_rate", 0), item_tax.get("tax_amount", 0)]
total_tax += flt(item_tax.get("tax_amount"))
row += [total_tax, d.base_net_amount + total_tax, company_currency]
data.append(row)
return columns, data
def get_reference_details(reference_doctype, reference_name, party_account_currency):
total_amount = outstanding_amount = exchange_rate = bill_no = None
ref_doc = frappe.get_doc(reference_doctype, reference_name)
company_currency = ref_doc.get("company_currency") or erpnext.get_company_currency(ref_doc.company)
if reference_doctype == "Fees":
total_amount = ref_doc.get("grand_total")
exchange_rate = 1
outstanding_amount = ref_doc.get("outstanding_amount")
elif reference_doctype == "Journal Entry" and ref_doc.docstatus == 1:
total_amount = ref_doc.get("total_amount")
if ref_doc.multi_currency:
exchange_rate = get_exchange_rate(party_account_currency, company_currency, ref_doc.posting_date)
else:
exchange_rate = 1
outstanding_amount = get_outstanding_on_journal_entry(reference_name)
elif reference_doctype != "Journal Entry":
if party_account_currency == company_currency:
if ref_doc.doctype == "Expense Claim":
total_amount = ref_doc.total_sanctioned_amount
elif ref_doc.doctype == "Employee Advance":
total_amount = ref_doc.advance_amount
else:
total_amount = ref_doc.base_grand_total
exchange_rate = 1
else:
total_amount = ref_doc.grand_total
# Get the exchange rate from the original ref doc
# or get it based on the posting date of the ref doc
exchange_rate = ref_doc.get("conversion_rate") or \
get_exchange_rate(party_account_currency, company_currency, ref_doc.posting_date)
if reference_doctype in ("Sales Invoice", "Purchase Invoice"):
outstanding_amount = ref_doc.get("outstanding_amount")
bill_no = ref_doc.get("bill_no")
elif reference_doctype == "Expense Claim":
outstanding_amount = flt(ref_doc.get("total_sanctioned_amount")) \
- flt(ref_doc.get("total_amount+reimbursed")) - flt(ref_doc.get("total_advance_amount"))
elif reference_doctype == "Employee Advance":
outstanding_amount = ref_doc.advance_amount - flt(ref_doc.paid_amount)
else:
outstanding_amount = flt(total_amount) - flt(ref_doc.advance_paid)
else:
# Get the exchange rate based on the posting date of the ref doc
exchange_rate = get_exchange_rate(party_account_currency,
company_currency, ref_doc.posting_date)
return frappe._dict({
"due_date": ref_doc.get("due_date"),
"total_amount": total_amount,
"outstanding_amount": outstanding_amount,
"exchange_rate": exchange_rate,
"bill_no": bill_no
})
def validate_conversion_rate(self):
# validate conversion rate
company_currency = erpnext.get_company_currency(self.doc.company)
if not self.doc.currency or self.doc.currency == company_currency:
self.doc.currency = company_currency
self.doc.conversion_rate = 1.0
else:
validate_conversion_rate(self.doc.currency, self.doc.conversion_rate,
self.doc.meta.get_label("conversion_rate"), self.doc.company)
self.doc.conversion_rate = flt(self.doc.conversion_rate)
def set_incoming_rate(self): for d in self.items: if d.s_warehouse: args = self.get_args_for_incoming_rate(d) d.basic_rate = get_incoming_rate(args) elif d.allow_zero_valuation_rate and not d.s_warehouse: d.basic_rate = 0.0 elif d.t_warehouse and not d.basic_rate: d.basic_rate = get_valuation_rate(d.item_code, d.t_warehouse, self.doctype, d.name, d.allow_zero_valuation_rate, currency=erpnext.get_company_currency(self.company))
def test_purchase_order_creation(self):
bo = make_blanket_order(blanket_order_type="Purchasing")
po = make_purchase_order(bo.name)
po.currency = get_company_currency(po.company)
po.schedule_date = today()
po.items[0].qty = 10
po.submit()
self.assertEqual(po.doctype, "Purchase Order")
self.assertEqual(len(po.get("items")), len(bo.get("items")))
# check the rate, quantity and updation for the ordered quantity
self.assertEqual(po.items[0].rate, po.items[0].rate)
bo = frappe.get_doc("Blanket Order", bo.name)
self.assertEqual(po.items[0].qty, bo.items[0].ordered_qty)
# test the quantity
po1 = make_sales_order(bo.name)
po1.currency = get_company_currency(po1.company)
self.assertEqual(po1.items[0].qty, (bo.items[0].qty-bo.items[0].ordered_qty))
def test_sales_order_creation(self):
bo = make_blanket_order(blanket_order_type="Selling")
so = make_sales_order(bo.name)
so.currency = get_company_currency(so.company)
so.delivery_date = today()
so.items[0].qty = 10
so.submit()
self.assertEqual(so.doctype, "Sales Order")
self.assertEqual(len(so.get("items")), len(bo.get("items")))
# check the rate, quantity and updation for the ordered quantity
self.assertEqual(so.items[0].rate, bo.items[0].rate)
bo = frappe.get_doc("Blanket Order", bo.name)
self.assertEqual(so.items[0].qty, bo.items[0].ordered_qty)
# test the quantity
so1 = make_sales_order(bo.name)
so1.currency = get_company_currency(so1.company)
self.assertEqual(so1.items[0].qty, (bo.items[0].qty-bo.items[0].ordered_qty))
def validate_currency(self):
company_currency = erpnext.get_company_currency(self.company)
account_currency = get_account_currency(self.account)
if not self.account_currency:
self.account_currency = company_currency
if account_currency != self.account_currency:
frappe.throw(_("{0} {1}: Accounting Entry for {2} can only be made in currency: {3}")
.format(self.voucher_type, self.voucher_no, self.account,
(account_currency or company_currency)), InvalidAccountCurrency)
if self.party_type and self.party:
validate_party_gle_currency(self.party_type, self.party, self.company, self.account_currency)
def set_rounded_total(self):
if frappe.db.get_single_value("Global Defaults", "disable_rounded_total"):
self.doc.rounded_total = self.doc.base_rounded_total = 0
return
if self.doc.meta.get_field("rounded_total"):
self.doc.rounded_total = round_based_on_smallest_currency_fraction(self.doc.grand_total,
self.doc.currency, self.doc.precision("rounded_total"))
if self.doc.meta.get_field("base_rounded_total"):
company_currency = erpnext.get_company_currency(self.doc.company)
self.doc.base_rounded_total = \
round_based_on_smallest_currency_fraction(self.doc.base_grand_total,
company_currency, self.doc.precision("base_rounded_total"))
def get_dashboard_info(self):
info = {
"total_paid": 0,
"total_unpaid": 0,
"currency": erpnext.get_company_currency(self.company)
}
fees_amount = frappe.db.sql("""select sum(grand_total), sum(outstanding_amount) from tabFees
where fee_schedule=%s and docstatus=1""", (self.name))
if fees_amount:
info["total_paid"] = flt(fees_amount[0][0]) - flt(fees_amount[0][1])
info["total_unpaid"] = flt(fees_amount[0][1])
return info
def set_missing_accounts_and_fields(self):
if not self.company:
self.company = frappe.defaults.get_defaults().company
if not self.currency:
self.currency = erpnext.get_company_currency(self.company)
if not (self.receivable_account and self.income_account and self.cost_center):
accounts_details = frappe.get_all("Company",
fields=["default_receivable_account", "default_income_account", "cost_center"],
filters={"name": self.company})[0]
if not self.receivable_account:
self.receivable_account = accounts_details.default_receivable_account
if not self.income_account:
self.income_account = accounts_details.default_income_account
if not self.cost_center:
self.cost_center = accounts_details.cost_center
if not self.student_email:
self.student_email = self.get_student_emails()
def get_outstanding(args):
if not frappe.has_permission("Account"):
frappe.msgprint(_("No Permission"), raise_exception=1)
if isinstance(args, basestring):
args = json.loads(args)
company_currency = erpnext.get_company_currency(args.get("company"))
if args.get("doctype") == "Journal Entry":
condition = " and party=%(party)s" if args.get("party") else ""
against_jv_amount = frappe.db.sql("""
select sum(debit_in_account_currency) - sum(credit_in_account_currency)
from `tabJournal Entry Account` where parent=%(docname)s and account=%(account)s {0}
and (reference_type is null or reference_type = '')""".format(condition), args)
against_jv_amount = flt(against_jv_amount[0][0]) if against_jv_amount else 0
amount_field = "credit_in_account_currency" if against_jv_amount > 0 else "debit_in_account_currency"
return {
amount_field: abs(against_jv_amount)
}
elif args.get("doctype") in ("Sales Invoice", "Purchase Invoice"):
party_type = "Customer" if args.get("doctype") == "Sales Invoice" else "Supplier"
invoice = frappe.db.get_value(args["doctype"], args["docname"],
["outstanding_amount", "conversion_rate", scrub(party_type)], as_dict=1)
exchange_rate = invoice.conversion_rate if (args.get("account_currency") != company_currency) else 1
if args["doctype"] == "Sales Invoice":
amount_field = "credit_in_account_currency" \
if flt(invoice.outstanding_amount) > 0 else "debit_in_account_currency"
else:
amount_field = "debit_in_account_currency" \
if flt(invoice.outstanding_amount) > 0 else "credit_in_account_currency"
return {
amount_field: abs(flt(invoice.outstanding_amount)),
"exchange_rate": exchange_rate,
"party_type": party_type,
"party": invoice.get(scrub(party_type))
}
def get_price_list_currency_and_exchange_rate(args):
if not args.price_list:
return {}
price_list_currency = get_price_list_currency(args.price_list)
price_list_uom_dependant = get_price_list_uom_dependant(args.price_list)
plc_conversion_rate = args.plc_conversion_rate
company_currency = get_company_currency(args.company)
if (not plc_conversion_rate) or (price_list_currency and args.price_list_currency \
and price_list_currency != args.price_list_currency):
# cksgb 19/09/2016: added args.transaction_date as posting_date argument for get_exchange_rate
plc_conversion_rate = get_exchange_rate(price_list_currency, company_currency,
args.transaction_date) or plc_conversion_rate
return frappe._dict({
"price_list_currency": price_list_currency,
"price_list_uom_dependant": price_list_uom_dependant,
"plc_conversion_rate": plc_conversion_rate
})
def calculate_totals(self):
self.doc.grand_total = flt(self.doc.get("taxes")[-1].total
if self.doc.get("taxes") else self.doc.net_total)
self.doc.total_taxes_and_charges = flt(self.doc.grand_total - self.doc.net_total,
self.doc.precision("total_taxes_and_charges"))
self._set_in_company_currency(self.doc, ["total_taxes_and_charges"])
if self.doc.doctype in ["Quotation", "Sales Order", "Delivery Note", "Sales Invoice"]:
self.doc.base_grand_total = flt(self.doc.grand_total * self.doc.conversion_rate) \
if self.doc.total_taxes_and_charges else self.doc.base_net_total
else:
self.doc.taxes_and_charges_added = self.doc.taxes_and_charges_deducted = 0.0
for tax in self.doc.get("taxes"):
if tax.category in ["Valuation and Total", "Total"]:
if tax.add_deduct_tax == "Add":
self.doc.taxes_and_charges_added += flt(tax.tax_amount_after_discount_amount)
else:
self.doc.taxes_and_charges_deducted += flt(tax.tax_amount_after_discount_amount)
self.doc.round_floats_in(self.doc, ["taxes_and_charges_added", "taxes_and_charges_deducted"])
self.doc.base_grand_total = flt(self.doc.grand_total * self.doc.conversion_rate) \
if (self.doc.taxes_and_charges_added or self.doc.taxes_and_charges_deducted) \
else self.doc.base_net_total
self._set_in_company_currency(self.doc, ["taxes_and_charges_added", "taxes_and_charges_deducted"])
self.doc.round_floats_in(self.doc, ["grand_total", "base_grand_total"])
if self.doc.meta.get_field("rounded_total"):
self.doc.rounded_total = round_based_on_smallest_currency_fraction(self.doc.grand_total,
self.doc.currency, self.doc.precision("rounded_total"))
if self.doc.meta.get_field("base_rounded_total"):
company_currency = erpnext.get_company_currency(self.doc.company)
self.doc.base_rounded_total = \
round_based_on_smallest_currency_fraction(self.doc.base_grand_total,
company_currency, self.doc.precision("base_rounded_total"))
def execute(filters=None):
if not filters: filters = {}
columns = get_columns(filters)
entries = get_entries(filters)
item_details = get_item_details()
data = []
company_currency = get_company_currency(filters["company"])
for d in entries:
if d.stock_qty > 0 or filters.get('show_return_entries', 0):
data.append([
d.name, d.customer, d.territory, d.warehouse, d.posting_date, d.item_code,
item_details.get(d.item_code, {}).get("item_group"), item_details.get(d.item_code, {}).get("brand"),
d.stock_qty, d.base_net_amount, d.sales_person, d.allocated_percentage, d.contribution_amt, company_currency
])
if data:
total_row = [""]*len(data[0])
data.append(total_row)
return columns, data
def get_moving_average_values(self, sle): actual_qty = flt(sle.actual_qty) new_stock_qty = flt(self.qty_after_transaction) + actual_qty if new_stock_qty >= 0: if actual_qty > 0: if flt(self.qty_after_transaction) <= 0: self.valuation_rate = sle.incoming_rate else: new_stock_value = (self.qty_after_transaction * self.valuation_rate) + \ (actual_qty * sle.incoming_rate) self.valuation_rate = new_stock_value / new_stock_qty elif sle.outgoing_rate: if new_stock_qty: new_stock_value = (self.qty_after_transaction * self.valuation_rate) + \ (actual_qty * sle.outgoing_rate) self.valuation_rate = new_stock_value / new_stock_qty else: self.valuation_rate = sle.outgoing_rate else: if flt(self.qty_after_transaction) >= 0 and sle.outgoing_rate: self.valuation_rate = sle.outgoing_rate if not self.valuation_rate and actual_qty > 0: self.valuation_rate = sle.incoming_rate # Get valuation rate from previous SLE or Item master, if item does not have the # allow zero valuration rate flag set if not self.valuation_rate and sle.voucher_detail_no: allow_zero_valuation_rate = self.check_if_allow_zero_valuation_rate(sle.voucher_type, sle.voucher_detail_no) if not allow_zero_valuation_rate: self.valuation_rate = get_valuation_rate(sle.item_code, sle.warehouse, sle.voucher_type, sle.voucher_no, self.allow_zero_rate, currency=erpnext.get_company_currency(sle.company))
def get_data(filters, mode_of_payments):
data = []
conditions = get_conditions(filters)
entry = frappe.db.sql(
"""
select branch, mode_of_payment, sum(net_pay) as net_pay, sum(gross_pay) as gross_pay
from `tabSalary Slip` sal
where docstatus = 1 %s
group by branch, mode_of_payment
""" % (conditions),
as_dict=1,
)
branch_wise_entries, gross_pay = prepare_data(entry)
branches = frappe.db.sql_list("""
select distinct branch from `tabSalary Slip` sal
where docstatus = 1 %s
""" % (conditions))
total_row = {"total": 0, "branch": "Total"}
for branch in branches:
total = 0
row = {"branch": branch}
for mode in mode_of_payments:
if branch_wise_entries.get(branch).get(mode):
row[mode] = branch_wise_entries.get(branch).get(mode)
total += branch_wise_entries.get(branch).get(mode)
row["total"] = total
data.append(row)
total_row = get_total_based_on_mode_of_payment(data, mode_of_payments)
total_deductions = gross_pay - total_row.get("total")
report_summary = []
if data:
data.append(total_row)
data.append({})
data.append({
"branch": "<b>Total Gross Pay</b>",
mode_of_payments[0]: gross_pay
})
data.append({
"branch": "<b>Total Deductions</b>",
mode_of_payments[0]: total_deductions
})
data.append({
"branch": "<b>Total Net Pay</b>",
mode_of_payments[0]: total_row.get("total")
})
currency = erpnext.get_company_currency(filters.company)
report_summary = get_report_summary(gross_pay, total_deductions,
total_row.get("total"), currency)
return data, total_row, report_summary
def _execute(filters=None,
additional_table_columns=None,
additional_query_columns=None):
if not filters: filters = {}
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
aii_account_map = get_aii_accounts()
if item_list:
itemised_tax, tax_columns = get_tax_accounts(
item_list,
columns,
company_currency,
doctype="Purchase Invoice",
tax_doctype="Purchase Taxes and Charges")
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
for d in item_list:
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
elif d.po_detail:
purchase_receipt = ", ".join(po_pr_map.get(d.po_detail, []))
expense_account = d.expense_account or aii_account_map.get(d.company)
row = [
d.item_code, d.item_name, d.item_group, d.parent, d.posting_date,
d.supplier, d.supplier_name
]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.credit_to, d.mode_of_payment, d.project, d.company,
d.purchase_order, purchase_receipt, expense_account, d.stock_qty,
d.stock_uom, d.base_net_amount / d.stock_qty, d.base_net_amount
]
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row += [item_tax.get("tax_rate", 0), item_tax.get("tax_amount", 0)]
total_tax += flt(item_tax.get("tax_amount"))
row += [total_tax, d.base_net_amount + total_tax, company_currency]
data.append(row)
return columns, data
def make_accrual_jv_entry(self):
self.check_permission("write")
earnings = self.get_salary_component_total(component_type="earnings") or {}
deductions = self.get_salary_component_total(component_type="deductions") or {}
payroll_payable_account = self.payroll_payable_account
jv_name = ""
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
if earnings or deductions:
journal_entry = frappe.new_doc("Journal Entry")
journal_entry.voucher_type = "Journal Entry"
journal_entry.user_remark = _("Accrual Journal Entry for salaries from {0} to {1}").format(
self.start_date, self.end_date
)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
accounting_dimensions = get_accounting_dimensions() or []
accounts = []
currencies = []
payable_amount = 0
multi_currency = 0
company_currency = erpnext.get_company_currency(self.company)
# Earnings
for acc_cc, amount in earnings.items():
exchange_rate, amt = self.get_amount_and_exchange_rate_for_journal_entry(
acc_cc[0], amount, company_currency, currencies
)
payable_amount += flt(amount, precision)
accounts.append(
self.update_accounting_dimensions(
{
"account": acc_cc[0],
"debit_in_account_currency": flt(amt, precision),
"exchange_rate": flt(exchange_rate),
"cost_center": acc_cc[1] or self.cost_center,
"project": self.project,
},
accounting_dimensions,
)
)
# Deductions
for acc_cc, amount in deductions.items():
exchange_rate, amt = self.get_amount_and_exchange_rate_for_journal_entry(
acc_cc[0], amount, company_currency, currencies
)
payable_amount -= flt(amount, precision)
accounts.append(
self.update_accounting_dimensions(
{
"account": acc_cc[0],
"credit_in_account_currency": flt(amt, precision),
"exchange_rate": flt(exchange_rate),
"cost_center": acc_cc[1] or self.cost_center,
"project": self.project,
},
accounting_dimensions,
)
)
# Payable amount
exchange_rate, payable_amt = self.get_amount_and_exchange_rate_for_journal_entry(
payroll_payable_account, payable_amount, company_currency, currencies
)
accounts.append(
self.update_accounting_dimensions(
{
"account": payroll_payable_account,
"credit_in_account_currency": flt(payable_amt, precision),
"exchange_rate": flt(exchange_rate),
"cost_center": self.cost_center,
},
accounting_dimensions,
)
)
journal_entry.set("accounts", accounts)
if len(currencies) > 1:
multi_currency = 1
journal_entry.multi_currency = multi_currency
journal_entry.title = payroll_payable_account
journal_entry.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
self.update_salary_slip_status(jv_name=jv_name)
except Exception as e:
if type(e) in (str, list, tuple):
frappe.msgprint(e)
raise
return jv_name
def company_currency(self): if not hasattr(self, "__company_currency"): self.__company_currency = erpnext.get_company_currency(self.company) return self.__company_currency
def get_fifo_values(self, sle):
incoming_rate = flt(sle.incoming_rate)
actual_qty = flt(sle.actual_qty)
outgoing_rate = flt(sle.outgoing_rate)
if actual_qty > 0:
if not self.stock_queue:
self.stock_queue.append([0, 0])
# last row has the same rate, just updated the qty
if self.stock_queue[-1][1] == incoming_rate:
self.stock_queue[-1][0] += actual_qty
else:
if self.stock_queue[-1][0] > 0:
self.stock_queue.append([actual_qty, incoming_rate])
else:
qty = self.stock_queue[-1][0] + actual_qty
self.stock_queue[-1] = [qty, incoming_rate]
else:
qty_to_pop = abs(actual_qty)
while qty_to_pop:
if not self.stock_queue:
# Get valuation rate from last sle if exists or from valuation rate field in item master
is_sample_item = self.check_if_sample_item(
sle.voucher_type, sle.voucher_detail_no)
if not is_sample_item:
_rate = get_valuation_rate(
sle.item_code,
sle.warehouse,
sle.voucher_type,
sle.voucher_no,
self.allow_zero_rate,
currency=erpnext.get_company_currency(sle.company))
else:
_rate = 0
self.stock_queue.append([0, _rate])
index = None
if outgoing_rate > 0:
# Find the entry where rate matched with outgoing rate
for i, v in enumerate(self.stock_queue):
if v[1] == outgoing_rate:
index = i
break
# If no entry found with outgoing rate, collapse stack
if index == None:
new_stock_value = sum(
(d[0] * d[1] for d in self.stock_queue
)) - qty_to_pop * outgoing_rate
new_stock_qty = sum(
(d[0] for d in self.stock_queue)) - qty_to_pop
self.stock_queue = [[
new_stock_qty, new_stock_value / new_stock_qty
if new_stock_qty > 0 else outgoing_rate
]]
break
else:
index = 0
# select first batch or the batch with same rate
batch = self.stock_queue[index]
if qty_to_pop >= batch[0]:
# consume current batch
qty_to_pop = qty_to_pop - batch[0]
self.stock_queue.pop(index)
if not self.stock_queue and qty_to_pop:
# stock finished, qty still remains to be withdrawn
# negative stock, keep in as a negative batch
self.stock_queue.append(
[-qty_to_pop, outgoing_rate or batch[1]])
break
else:
# qty found in current batch
# consume it and exit
batch[0] = batch[0] - qty_to_pop
qty_to_pop = 0
stock_value = sum(
(flt(batch[0]) * flt(batch[1]) for batch in self.stock_queue))
stock_qty = sum((flt(batch[0]) for batch in self.stock_queue))
if stock_qty:
self.valuation_rate = stock_value / flt(stock_qty)
if not self.stock_queue:
self.stock_queue.append([
0, sle.incoming_rate or sle.outgoing_rate
or self.valuation_rate
])
def company_currency(self): return erpnext.get_company_currency(self.company)
def validate_employee_advance_account(self):
company_currency = erpnext.get_company_currency(self.company)
if (self.advance_account and
company_currency != frappe.db.get_value('Account', self.advance_account, 'account_currency')):
frappe.throw(_("Advance account currency should be same as company currency {0}")
.format(company_currency))