def validate_conversion_rate(args, meta):
from erpnext.controllers.accounts_controller import validate_conversion_rate
if (not args.conversion_rate
and args.currency==frappe.db.get_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})))
# 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)
args.plc_conversion_rate = flt(args.plc_conversion_rate,
get_field_precision(meta.get_field("plc_conversion_rate"),
frappe._dict({"fields": args})))
def format_value(value, df, doc=None, currency=None):
# Convert dict to object if necessary
if (isinstance(df, dict)):
df = frappe._dict(df)
if df.get("fieldtype")=="Date":
return formatdate(value)
elif df.get("fieldtype") == "Currency" or (df.get("fieldtype")=="Float" and (df.options or "").strip()):
return fmt_money(value, precision=get_field_precision(df, doc),
currency=currency if currency else (get_field_currency(df, doc) if doc else None))
elif df.get("fieldtype") == "Float":
precision = get_field_precision(df, doc)
# show 1.000000 as 1
# options should not specified
if not df.options and value is not None:
temp = cstr(value).split(".")
if len(temp)==1 or cint(temp[1])==0:
precision = 0
return fmt_money(value, precision=precision)
elif df.get("fieldtype") == "Percent":
return "{}%".format(flt(value, 2))
if value is None:
value = ""
if df.get("fieldtype") in ("Text", "Small Text"):
if not re.search("(\<br|\<div|\<p)", value):
return value.replace("\n", "<br>")
return value
def precision(self, fieldname, parentfield=None):
from frappe.model.meta import get_field_precision
if parentfield and not isinstance(parentfield, basestring):
parentfield = parentfield.parentfield
if not hasattr(self, "_precision"):
self._precision = frappe._dict({
"default": cint(frappe.db.get_default("float_precision")) or 3,
"options": {}
})
if self._precision.setdefault(parentfield or "main", {}).get(fieldname) is None:
meta = frappe.get_meta(self.meta.get_field(parentfield).options if parentfield else self.doctype)
df = meta.get_field(fieldname)
if df.fieldtype == "Currency" and df.options and not self._precision.options.get(df.options):
self._precision.options[df.options] = get_field_precision(df, self)
if df.fieldtype == "Currency":
self._precision[parentfield or "main"][fieldname] = cint(self._precision.options.get(df.options)) or \
self._precision.default
elif df.fieldtype == "Float":
self._precision[parentfield or "main"][fieldname] = self._precision.default
return self._precision[parentfield or "main"][fieldname]
def precision(self, fieldname, parentfield=None):
"""Returns float precision for a particular field (or get global default).
:param fieldname: Fieldname for which precision is required.
:param parentfield: If fieldname is in child table."""
from frappe.model.meta import get_field_precision
if parentfield and not isinstance(parentfield, basestring):
parentfield = parentfield.parentfield
cache_key = parentfield or "main"
if not hasattr(self, "_precision"):
self._precision = frappe._dict()
if cache_key not in self._precision:
self._precision[cache_key] = frappe._dict()
if fieldname not in self._precision[cache_key]:
self._precision[cache_key][fieldname] = None
doctype = self.meta.get_field(parentfield).options if parentfield else self.doctype
df = frappe.get_meta(doctype).get_field(fieldname)
if df.fieldtype in ("Currency", "Float", "Percent"):
self._precision[cache_key][fieldname] = get_field_precision(df, self)
return self._precision[cache_key][fieldname]
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_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 __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 args.iteritems():
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.db.get_value("Company", self.company, "default_currency", cache=True))
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 update_values(dt, tax_table):
rate_field_precision = get_field_precision(frappe.get_meta(dt + " Item").get_field("rate"))
tax_amount_precision = get_field_precision(frappe.get_meta(tax_table).get_field("tax_amount"))
# update net_total, discount_on
frappe.db.sql("""
UPDATE
`tab{0}`
SET
total_taxes_and_charges = round(base_total_taxes_and_charges / conversion_rate, {1})
WHERE
docstatus < 2
and ifnull(base_total_taxes_and_charges, 0) != 0
and ifnull(total_taxes_and_charges, 0) = 0
""".format(dt, tax_amount_precision))
# update net_amount
frappe.db.sql("""
UPDATE
`tab{0}` par, `tab{1}` item
SET
item.net_amount = round(item.base_net_amount / par.conversion_rate, {2}),
item.net_rate = round(item.base_net_rate / par.conversion_rate, {2})
WHERE
par.name = item.parent
and par.docstatus < 2
and ((ifnull(item.base_net_amount, 0) != 0 and ifnull(item.net_amount, 0) = 0)
or (ifnull(item.base_net_rate, 0) != 0 and ifnull(item.net_rate, 0) = 0))
""".format(dt, dt + " Item", rate_field_precision))
# update tax in party currency
frappe.db.sql("""
UPDATE
`tab{0}` par, `tab{1}` tax
SET
tax.tax_amount = round(tax.base_tax_amount / par.conversion_rate, {2}),
tax.total = round(tax.base_total / conversion_rate, {2}),
tax.tax_amount_after_discount_amount = round(tax.base_tax_amount_after_discount_amount / conversion_rate, {2})
WHERE
par.name = tax.parent
and par.docstatus < 2
and ((ifnull(tax.base_tax_amount, 0) != 0 and ifnull(tax.tax_amount, 0) = 0)
or (ifnull(tax.base_total, 0) != 0 and ifnull(tax.total, 0) = 0)
or (ifnull(tax.base_tax_amount_after_discount_amount, 0) != 0 and
ifnull(tax.tax_amount_after_discount_amount, 0) = 0))
""".format(dt, tax_table, tax_amount_precision))
def format_value(value, df, doc=None, currency=None):
# Convert dict to object if necessary
if (isinstance(df, dict)):
df = frappe._dict(df)
if df.get("fieldtype") == "Date":
return formatdate(value)
elif df.get("fieldtype") == "Datetime":
return format_datetime(value)
elif df.get("fieldtype") == "Currency" or (df.get("fieldtype") == "Float"
and (df.options or "").strip()):
return fmt_money(value,
precision=get_field_precision(df, doc),
currency=currency if currency else
(get_field_currency(df, doc) if doc else None))
elif df.get("fieldtype") == "Float":
precision = get_field_precision(df, doc)
# show 1.000000 as 1
# options should not specified
if not df.options and value is not None:
temp = cstr(value).split(".")
if len(temp) == 1 or cint(temp[1]) == 0:
precision = 0
return fmt_money(value, precision=precision)
elif df.get("fieldtype") == "Percent":
return "{}%".format(flt(value, 2))
if value is None:
value = ""
if df.get("fieldtype") in ("Text", "Small Text"):
if not re.search("(\<br|\<div|\<p)", value):
return value.replace("\n", "<br>")
return value
def validate_conversion_rate(args, meta):
from erpnext.controllers.accounts_controller import validate_conversion_rate
# 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})))
# 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)
args.plc_conversion_rate = flt(args.plc_conversion_rate,
get_field_precision(meta.get_field("plc_conversion_rate"),
frappe._dict({"fields": args})))
def round_off_debit_credit(self, gl_map):
"""
NEW: add a rounding entry if necessary to balance credit/debit
"""
precision = get_field_precision(
frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.db.get_value("Company",
gl_map[0].company,
"default_currency",
cache=True))
debit_credit_diff = 0.0
for entry in gl_map:
entry.debit = flt(entry.debit, precision)
entry.credit = flt(entry.credit, precision)
debit_credit_diff += entry.debit - entry.credit
debit_credit_diff = flt(debit_credit_diff, precision)
round_off_account, round_off_cost_center = get_round_off_account_and_cost_center(
gl_map[0].company)
round_off_gle = frappe._dict()
for k in [
"voucher_type", "voucher_no", "company", "posting_date",
"remarks", "is_opening"
]:
round_off_gle[k] = gl_map[0][k]
round_off_gle.update({
"account":
round_off_account,
"debit_in_account_currency":
abs(debit_credit_diff) if debit_credit_diff < 0 else 0,
"credit_in_account_currency":
debit_credit_diff if debit_credit_diff > 0 else 0,
"debit":
abs(debit_credit_diff) if debit_credit_diff < 0 else 0,
"credit":
debit_credit_diff if debit_credit_diff > 0 else 0,
"cost_center":
round_off_cost_center,
"party_type":
None,
"party":
None,
"against_voucher_type":
None,
"against_voucher":
None
})
gl_map.append(round_off_gle)
def validate_conversion_rate(args, meta):
from erpnext.setup.doctype.currency.currency import validate_conversion_rate
from frappe.model.meta import get_field_precision
# 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})))
# 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)
args.plc_conversion_rate = flt(args.plc_conversion_rate,
get_field_precision(meta.get_field("plc_conversion_rate"),
frappe._dict({"fields": args})))
def validate_conversion_rate(args, meta):
from erpnext.setup.doctype.currency.currency import validate_conversion_rate
from frappe.model.meta import get_field_precision
# 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})))
# 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)
args.plc_conversion_rate = flt(args.plc_conversion_rate,
get_field_precision(meta.get_field("plc_conversion_rate"),
frappe._dict({"fields": args})))
def validate_account_for_perpetual_inventory(gl_map):
if cint(erpnext.is_perpetual_inventory_enabled(gl_map[0].company)):
account_list = [gl_entries.account for gl_entries in gl_map]
aii_accounts = [d.name for d in frappe.get_all("Account",
filters={'account_type': 'Stock', 'is_group': 0, 'company': gl_map[0].company})]
for account in account_list:
if account not in aii_accounts:
continue
# Always use current date to get stock and account balance as there can future entries for
# other items
account_bal, stock_bal, warehouse_list = get_stock_and_account_balance(account,
getdate(), gl_map[0].company)
if gl_map[0].voucher_type=="Journal Entry":
# In case of Journal Entry, there are no corresponding SL entries,
# hence deducting currency amount
account_bal -= flt(gl_map[0].debit) - flt(gl_map[0].credit)
if account_bal == stock_bal:
frappe.throw(_("Account: {0} can only be updated via Stock Transactions")
.format(account), StockAccountInvalidTransaction)
elif abs(account_bal - stock_bal) > 0.1:
precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.get_cached_value('Company', gl_map[0].company, "default_currency"))
diff = flt(stock_bal - account_bal, precision)
error_reason = _("Stock Value ({0}) and Account Balance ({1}) are out of sync for account {2} and it's linked warehouses.").format(
stock_bal, account_bal, frappe.bold(account))
error_resolution = _("Please create adjustment Journal Entry for amount {0} ").format(frappe.bold(diff))
stock_adjustment_account = frappe.db.get_value("Company",gl_map[0].company,"stock_adjustment_account")
db_or_cr_warehouse_account =('credit_in_account_currency' if diff < 0 else 'debit_in_account_currency')
db_or_cr_stock_adjustment_account = ('debit_in_account_currency' if diff < 0 else 'credit_in_account_currency')
journal_entry_args = {
'accounts':[
{'account': account, db_or_cr_warehouse_account : abs(diff)},
{'account': stock_adjustment_account, db_or_cr_stock_adjustment_account : abs(diff) }]
}
frappe.msgprint(msg="""{0}<br></br>{1}<br></br>""".format(error_reason, error_resolution),
raise_exception=StockValueAndAccountBalanceOutOfSync,
title=_('Values Out Of Sync'),
primary_action={
'label': _('Make Journal Entry'),
'client_action': 'erpnext.route_to_adjustment_jv',
'args': journal_entry_args
})
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 args.price_list:
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 test_update_child_with_precision(self):
from frappe.model.meta import get_field_precision
from frappe.custom.doctype.property_setter.property_setter import make_property_setter
precision = get_field_precision(frappe.get_meta("Sales Order Item").get_field("rate"))
make_property_setter("Sales Order Item", "rate", "precision", 7, "Currency")
so = make_sales_order(item_code= "_Test Item", qty=4, rate=200.34664)
trans_item = json.dumps([{'item_code' : '_Test Item', 'rate' : 200.34669, 'qty' : 4, 'docname': so.items[0].name}])
update_child_qty_rate('Sales Order', trans_item, so.name)
so.reload()
self.assertEqual(so.items[0].rate, 200.34669)
make_property_setter("Sales Order Item", "rate", "precision", precision, "Currency")
def format_value(value, df, doc=None, currency=None):
if df.fieldtype == "Date":
return formatdate(value)
elif df.fieldtype == "Currency":
return fmt_money(value,
precision=get_field_precision(df, doc),
currency=currency if currency else
(get_field_currency(df, doc) if doc else None))
elif df.fieldtype == "Float":
return fmt_money(value, precision=get_field_precision(df, doc))
elif df.fieldtype == "Percent":
return "{}%".format(flt(value, 2))
if value is None:
value = ""
if df.fieldtype in ("Text", "Small Text"):
if not re.search("(\<br|\<div|\<p)", value):
return value.replace("\n", "<br>")
return value
def format_value(value, df, doc=None):
if df.fieldtype == "Date":
return formatdate(value)
elif df.fieldtype == "Currency":
return fmt_money(value,
precision=get_field_precision(df, doc),
currency=get_field_currency(df, doc))
elif df.fieldtype == "Float":
return fmt_money(value)
elif df.fieldtype == "Percent":
return "{}%".format(flt(value, 2))
return value
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,
)
account_details = self.get_accounts_from_gle()
for d in account_details:
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,
})
if not accounts:
self.throw_invalid_response_message(account_details)
return accounts
def round_off_debit_credit(gl_map):
precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.db.get_value("Company", gl_map[0].company, "default_currency", cache=True))
debit_credit_diff = 0.0
for entry in gl_map:
entry.debit = flt(entry.debit, precision)
entry.credit = flt(entry.credit, precision)
debit_credit_diff += entry.debit - entry.credit
debit_credit_diff = flt(debit_credit_diff, precision)
if abs(debit_credit_diff) >= (5.0 / (10**precision)):
frappe.throw(_("Debit and Credit not equal for {0} #{1}. Difference is {2}.")
.format(gl_map[0].voucher_type, gl_map[0].voucher_no, debit_credit_diff))
elif abs(debit_credit_diff) >= (1.0 / (10**precision)):
make_round_off_gle(gl_map, debit_credit_diff)
def round_off_debit_credit(gl_map):
precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.db.get_value("Company", gl_map[0].company, "default_currency", cache=True))
debit_credit_diff = 0.0
for entry in gl_map:
entry.debit = flt(entry.debit, precision)
entry.credit = flt(entry.credit, precision)
debit_credit_diff += entry.debit - entry.credit
debit_credit_diff = flt(debit_credit_diff, precision)
if abs(debit_credit_diff) >= (2.0 / (10**precision)):
frappe.throw(_("Debit and Credit not equal for {0} #{1}. Difference is {2}.")
.format(gl_map[0].voucher_type, gl_map[0].voucher_no, debit_credit_diff))
elif abs(debit_credit_diff) >= (1.0 / (10**precision)):
make_round_off_gle(gl_map, debit_credit_diff)
def get_new_leaves(self, leave_type, new_leaves_allocated, leave_type_details, date_of_joining):
from frappe.model.meta import get_field_precision
precision = get_field_precision(frappe.get_meta("Leave Allocation").get_field("new_leaves_allocated"))
# Earned Leaves and Compensatory Leaves are allocated by scheduler, initially allocate 0
if leave_type_details.get(leave_type).is_compensatory == 1:
new_leaves_allocated = 0
elif leave_type_details.get(leave_type).is_earned_leave == 1:
if self.assignment_based_on == "Leave Period":
new_leaves_allocated = self.get_leaves_for_passed_months(leave_type, new_leaves_allocated, leave_type_details, date_of_joining)
else:
new_leaves_allocated = 0
# Calculate leaves at pro-rata basis for employees joining after the beginning of the given leave period
elif getdate(date_of_joining) > getdate(self.effective_from):
remaining_period = ((date_diff(self.effective_to, date_of_joining) + 1) / (date_diff(self.effective_to, self.effective_from) + 1))
new_leaves_allocated = ceil(new_leaves_allocated * remaining_period)
return flt(new_leaves_allocated, precision)
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 args.iteritems():
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.db.get_value("Company",
self.company,
"default_currency",
cache=True))
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 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 check_if_stock_and_account_balance_synced(posting_date,
company,
voucher_type=None,
voucher_no=None):
if not cint(erpbee.is_perpetual_inventory_enabled(company)):
return
accounts = get_stock_accounts(company, voucher_type, voucher_no)
stock_adjustment_account = frappe.db.get_value("Company", company,
"stock_adjustment_account")
for account in accounts:
account_bal, stock_bal, warehouse_list = get_stock_and_account_balance(
account, posting_date, company)
if abs(account_bal - stock_bal) > 0.1:
precision = get_field_precision(
frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.get_cached_value('Company', company,
"default_currency"))
diff = flt(stock_bal - account_bal, precision)
error_reason = _(
"Stock Value ({0}) and Account Balance ({1}) are out of sync for account {2} and it's linked warehouses as on {3}."
).format(stock_bal, account_bal, frappe.bold(account),
posting_date)
error_resolution = _("Please create an adjustment Journal Entry for amount {0} on {1}")\
.format(frappe.bold(diff), frappe.bold(posting_date))
frappe.msgprint(
msg="""{0}<br></br>{1}<br></br>""".format(
error_reason, error_resolution),
raise_exception=StockValueAndAccountBalanceOutOfSync,
title=_('Values Out Of Sync'),
primary_action={
'label':
_('Make Journal Entry'),
'client_action':
'erpbee.route_to_adjustment_jv',
'args':
get_journal_entry(account, stock_adjustment_account, diff)
})
def validate_applicable_charges_for_item(self):
based_on = self.distribute_charges_based_on.lower()
total = sum([flt(d.get(based_on)) for d in self.get("items")])
if not total:
frappe.throw(_("Total {0} for all items is zero, may be you should change 'Distribute Charges Based On'").format(based_on))
total_applicable_charges = sum([flt(d.applicable_charges) for d in self.get("items")])
precision = get_field_precision(frappe.get_meta("Landed Cost Item").get_field("applicable_charges"),
currency=frappe.get_cached_value('Company', self.company, "default_currency"))
diff = flt(self.total_taxes_and_charges) - flt(total_applicable_charges)
diff = flt(diff, precision)
if abs(diff) < (2.0 / (10**precision)):
self.items[-1].applicable_charges += diff
else:
frappe.throw(_("Total Applicable Charges in Purchase Receipt Items table must be same as Total Taxes and Charges"))
def validate_applicable_charges_for_item(self):
based_on = self.distribute_charges_based_on.lower()
total = sum([flt(d.get(based_on)) for d in self.get("items")])
if not total:
frappe.throw(_("Total {0} for all items is zero, may be you should change 'Distribute Charges Based On'").format(based_on))
total_applicable_charges = sum([flt(d.applicable_charges) for d in self.get("items")])
precision = get_field_precision(frappe.get_meta("Landed Cost Item").get_field("applicable_charges"),
currency=frappe.db.get_value("Company", self.company, "default_currency", cache=True))
diff = flt(self.total_taxes_and_charges) - flt(total_applicable_charges)
diff = flt(diff, precision)
if abs(diff) < (2.0 / (10**precision)):
self.items[-1].applicable_charges += diff
else:
frappe.throw(_("Total Applicable Charges in Purchase Receipt Items table must be same as Total Taxes and Charges"))
def get_ordered_to_be_billed_data(args):
doctype, party = args.get('doctype'), args.get('party')
child_tab = doctype + " Item"
precision = get_field_precision(
frappe.get_meta(child_tab).get_field("billed_amt"),
currency=get_default_currency()) or 2
project_field = get_project_field(doctype, party)
return frappe.db.sql("""
Select
`{parent_tab}`.name, `{parent_tab}`.{date_field},
`{parent_tab}`.{party}, `{parent_tab}`.{party}_name,
`{child_tab}`.item_code,
`{child_tab}`.base_amount,
(`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1)),
(`{child_tab}`.base_rate * ifnull(`{child_tab}`.returned_qty, 0)),
(`{child_tab}`.base_amount -
(`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1)) -
(`{child_tab}`.base_rate * ifnull(`{child_tab}`.returned_qty, 0))),
`{child_tab}`.item_name, `{child_tab}`.description,
{project_field}, `{parent_tab}`.company
from
`{parent_tab}`, `{child_tab}`
where
`{parent_tab}`.name = `{child_tab}`.parent and `{parent_tab}`.docstatus = 1
and `{parent_tab}`.status not in ('Closed', 'Completed')
and `{child_tab}`.amount > 0
and (`{child_tab}`.base_amount -
round(`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1), {precision}) -
(`{child_tab}`.base_rate * ifnull(`{child_tab}`.returned_qty, 0))) > 0
order by
`{parent_tab}`.{order} {order_by}
""".format(parent_tab='tab' + doctype,
child_tab='tab' + child_tab,
precision=precision,
party=party,
date_field=args.get('date'),
project_field=project_field,
order=args.get('order'),
order_by=args.get('order_by')))
def repost_gle_for_stock_vouchers(stock_vouchers, posting_date, company=None, warehouse_account=None):
def _delete_gl_entries(voucher_type, voucher_no):
frappe.db.sql("""delete from `tabGL Entry`
where voucher_type=%s and voucher_no=%s""", (voucher_type, voucher_no))
if not warehouse_account:
warehouse_account = get_warehouse_account_map(company)
precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit")) or 2
gle = get_voucherwise_gl_entries(stock_vouchers, posting_date)
for voucher_type, voucher_no in stock_vouchers:
existing_gle = gle.get((voucher_type, voucher_no), [])
voucher_obj = frappe.get_cached_doc(voucher_type, voucher_no)
expected_gle = voucher_obj.get_gl_entries(warehouse_account)
if expected_gle:
if not existing_gle or not compare_existing_and_expected_gle(existing_gle, expected_gle, precision):
_delete_gl_entries(voucher_type, voucher_no)
voucher_obj.make_gl_entries(gl_entries=expected_gle, from_repost=True)
else:
_delete_gl_entries(voucher_type, voucher_no)
def get_ordered_to_be_billed_data(args):
doctype, party = args.get('doctype'), args.get('party')
child_tab = doctype + " Item"
precision = get_field_precision(
frappe.get_meta(child_tab).get_field("billed_amt"),
currency=get_default_currency()) or 2
project_field = get_project_field(doctype, party)
# >>> Vamsi; 13012018; Excluding return items in 'Delivered Items To Be Billed' report
if ('additional_where' in args):
additional_where = """ and `{parent_tab}`.name not in (select `{parent_tab}`.return_against from `{parent_tab}` where `{parent_tab}`.is_return=1 )""".format(
parent_tab='tab' + doctype)
else:
additional_where = ""
# <<< Vamsi; 13012018; Excluding return items in 'Delivered Items To Be Billed' report
return frappe.db.sql("""
Select
`{parent_tab}`.name, `{parent_tab}`.{date_field}, `{parent_tab}`.{party}, `{parent_tab}`.{party}_name,
{project_field}, `{child_tab}`.item_code, `{child_tab}`.base_amount,
(`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1)),
(`{child_tab}`.base_amount - (`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1))),
`{child_tab}`.item_name, `{child_tab}`.description, `{parent_tab}`.company
from
`{parent_tab}`, `{child_tab}`
where
`{parent_tab}`.name = `{child_tab}`.parent and `{parent_tab}`.docstatus = 1 and `{parent_tab}`.status != 'Closed'
and `{child_tab}`.amount > 0 and round(`{child_tab}`.billed_amt *
ifnull(`{parent_tab}`.conversion_rate, 1), {precision}) < `{child_tab}`.base_amount {uyn_custom_condition}
order by
`{parent_tab}`.{order} {order_by}
""".format(parent_tab='tab' + doctype,
child_tab='tab' + child_tab,
precision=precision,
party=party,
date_field=args.get('date'),
project_field=project_field,
order=args.get('order'),
order_by=args.get('order_by'),
uyn_custom_condition=additional_where))
def process_debit_credit_difference(gl_map):
precision = get_field_precision(
frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.get_cached_value("Company", gl_map[0].company,
"default_currency"),
)
voucher_type = gl_map[0].voucher_type
voucher_no = gl_map[0].voucher_no
allowance = get_debit_credit_allowance(voucher_type, precision)
debit_credit_diff = get_debit_credit_difference(gl_map, precision)
if abs(debit_credit_diff) > allowance:
raise_debit_credit_not_equal_error(debit_credit_diff, voucher_type,
voucher_no)
elif abs(debit_credit_diff) >= (1.0 / (10**precision)):
make_round_off_gle(gl_map, debit_credit_diff, precision)
debit_credit_diff = get_debit_credit_difference(gl_map, precision)
if abs(debit_credit_diff) > allowance:
raise_debit_credit_not_equal_error(debit_credit_diff, voucher_type,
voucher_no)
def round_off_debit_credit(gl_map):
precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.get_cached_value('Company', gl_map[0].company, "default_currency"))
debit_credit_diff = 0.0
for entry in gl_map:
entry.debit = flt(entry.debit, precision)
entry.credit = flt(entry.credit, precision)
debit_credit_diff += entry.debit - entry.credit
debit_credit_diff = flt(debit_credit_diff, precision)
if gl_map[0]["voucher_type"] in ("Journal Entry", "Payment Entry"):
allowance = 5.0 / (10**precision)
else:
allowance = .5
if abs(debit_credit_diff) >= allowance:
frappe.throw(_("Debit and Credit not equal for {0} #{1}. Difference is {2}.")
.format(gl_map[0].voucher_type, gl_map[0].voucher_no, debit_credit_diff))
elif abs(debit_credit_diff) >= (1.0 / (10**precision)):
make_round_off_gle(gl_map, debit_credit_diff, precision)
def round_off_debit_credit(gl_map):
precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.get_cached_value('Company', gl_map[0].company, "default_currency"))
debit_credit_diff = 0.0
for entry in gl_map:
entry.debit = flt(entry.debit, precision)
entry.credit = flt(entry.credit, precision)
debit_credit_diff += entry.debit - entry.credit
debit_credit_diff = flt(debit_credit_diff, precision)
if gl_map[0]["voucher_type"] in ("Journal Entry", "Payment Entry"):
allowance = 5.0 / (10**precision)
else:
allowance = .5
if abs(debit_credit_diff) >= allowance:
frappe.throw(_("Debit and Credit not equal for {0} #{1}. Difference is {2}.")
.format(gl_map[0].voucher_type, gl_map[0].voucher_no, debit_credit_diff))
elif abs(debit_credit_diff) >= (1.0 / (10**precision)):
make_round_off_gle(gl_map, debit_credit_diff, precision)
def update_item_qty_from_availability(items):
if isinstance(items, string_types):
items = json.loads(items)
out = {}
actual_qty_map = {}
for d in items:
d = frappe._dict(d)
if d.item_code and d.warehouse:
if (d.item_code, d.warehouse) not in actual_qty_map:
actual_qty = frappe.db.sql(
"""select actual_qty from `tabBin`
where item_code = %s and warehouse = %s""", (d.item_code, d.warehouse))
actual_qty = actual_qty and flt(actual_qty[0][0]) or 0
actual_qty_map[(d.item_code, d.warehouse)] = actual_qty
out.setdefault(d.name, frappe._dict()).actual_qty = actual_qty
else:
out.setdefault(
d.name,
frappe._dict()).actual_qty = actual_qty_map[(d.item_code,
d.warehouse)]
for d in items:
d = frappe._dict(d)
if d.item_code and d.warehouse:
remaining_qty = actual_qty_map[(d.item_code, d.warehouse)]
if flt(d.stock_qty) > remaining_qty:
out[d.name].qty = flt(
remaining_qty / flt(d.conversion_factor),
get_field_precision(
frappe.get_meta("Delivery Note Item").get_field(
"qty")))
actual_qty_map[(d.item_code, d.warehouse)] = 0
else:
actual_qty_map[(d.item_code, d.warehouse)] -= flt(d.stock_qty)
return out
def precision(self, fieldname, parentfield=None):
from frappe.model.meta import get_field_precision
if parentfield and not isinstance(parentfield, basestring):
parentfield = parentfield.parentfield
cache_key = parentfield or "main"
if not hasattr(self, "_precision"):
self._precision = frappe._dict()
if cache_key not in self._precision:
self._precision[cache_key] = frappe._dict()
if fieldname not in self._precision[cache_key]:
self._precision[cache_key][fieldname] = None
doctype = self.meta.get_field(parentfield).options if parentfield else self.doctype
df = frappe.get_meta(doctype).get_field(fieldname)
if df.fieldtype in ("Currency", "Float", "Percent"):
self._precision[cache_key][fieldname] = get_field_precision(df, self)
return self._precision[cache_key][fieldname]
def precision(self, fieldname, parentfield=None):
from frappe.model.meta import get_field_precision
if parentfield and not isinstance(parentfield, basestring):
parentfield = parentfield.parentfield
cache_key = parentfield or "main"
if not hasattr(self, "_precision"):
self._precision = frappe._dict()
if cache_key not in self._precision:
self._precision[cache_key] = frappe._dict()
if fieldname not in self._precision[cache_key]:
self._precision[cache_key][fieldname] = None
doctype = self.meta.get_field(parentfield).options if parentfield else self.doctype
df = frappe.get_meta(doctype).get_field(fieldname)
if df.fieldtype in ("Currency", "Float", "Percent"):
self._precision[cache_key][fieldname] = get_field_precision(df, self)
return self._precision[cache_key][fieldname]
def update_against_account(voucher_type, voucher_no):
entries = frappe.db.get_all("GL Entry",
filters={"voucher_type": voucher_type, "voucher_no": voucher_no},
fields=["name", "party", "against", "debit", "credit", "account", "company"])
if not entries:
return
company_currency = erpnext.get_company_currency(entries[0].company)
precision = get_field_precision(frappe.get_meta("GL Entry")
.get_field("debit"), company_currency)
accounts_debited, accounts_credited = [], []
for d in entries:
if flt(d.debit, precision) > 0: accounts_debited.append(d.party or d.account)
if flt(d.credit, precision) > 0: accounts_credited.append(d.party or d.account)
for d in entries:
if flt(d.debit, precision) > 0:
new_against = ", ".join(list(set(accounts_credited)))
if flt(d.credit, precision) > 0:
new_against = ", ".join(list(set(accounts_debited)))
if d.against != new_against:
frappe.db.set_value("GL Entry", d.name, "against", new_against)
def get_ordered_to_be_billed_data(args):
doctype, party = args.get("doctype"), args.get("party")
child_tab = doctype + " Item"
precision = (
get_field_precision(frappe.get_meta(child_tab).get_field("billed_amt"), currency=get_default_currency()) or 2
)
project_field = get_project_field(doctype, party)
return frappe.db.sql(
"""
Select
`{parent_tab}`.name, `{parent_tab}`.{date_field}, `{parent_tab}`.{party}, `{parent_tab}`.{party}_name,
{project_field}, `{child_tab}`.item_code, `{child_tab}`.base_amount,
(`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1)),
(`{child_tab}`.base_amount - (`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1))),
`{child_tab}`.item_name, `{child_tab}`.description, `{parent_tab}`.company
from
`{parent_tab}`, `{child_tab}`
where
`{parent_tab}`.name = `{child_tab}`.parent and `{parent_tab}`.docstatus = 1 and `{parent_tab}`.status != 'Closed'
and `{child_tab}`.amount > 0 and round(`{child_tab}`.billed_amt *
ifnull(`{parent_tab}`.conversion_rate, 1), {precision}) < `{child_tab}`.base_amount
order by
`{parent_tab}`.{order} {order_by}
""".format(
parent_tab="tab" + doctype,
child_tab="tab" + child_tab,
precision=precision,
party=party,
date_field=args.get("date"),
project_field=project_field,
order=args.get("order"),
order_by=args.get("order_by"),
)
)
def get_tax_accounts(item_list, columns, company_currency, doctype="Sales Invoice", tax_doctype="Sales Taxes and Charges"):
item_row_map = {}
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
conditions = ""
tax_amount_precision = get_field_precision(frappe.get_meta(tax_doctype).get_field("tax_amount"),
currency=company_currency) or 2
for d in item_list:
invoice_item_row.setdefault(d.parent, []).append(d)
item_row_map.setdefault(d.parent, {}).setdefault(d.item_code or d.item_name, []).append(d)
tax_details = frappe.db.sql("""
select
parent, account_head, item_wise_tax_detail,
base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
and (description is not null and description != '')
and parent in (%s)
%s
order by description
""" % (tax_doctype, '%s', ', '.join(['%s']*len(invoice_item_row)), conditions),
tuple([doctype] + list(invoice_item_row)))
for parent, account_head, item_wise_tax_detail, tax_amount in tax_details:
if account_head not in tax_columns and tax_amount:
# as description is text editor earlier and markup can break the column convention in reports
tax_columns.append(account_head)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item_code, tax_data in item_wise_tax_detail.items():
if not frappe.db.get_value("Item", item_code, "gst_hsn_code"):
continue
itemised_tax.setdefault(item_code, frappe._dict())
if isinstance(tax_data, list):
tax_amount = tax_data[1]
else:
tax_amount = 0
for d in item_row_map.get(parent, {}).get(item_code, []):
item_tax_amount = tax_amount
if item_tax_amount:
itemised_tax.setdefault((parent, item_code), {})[account_head] = frappe._dict({
"tax_amount": flt(item_tax_amount, tax_amount_precision)
})
except ValueError:
continue
tax_columns.sort()
for account_head in tax_columns:
columns.append({
"label": account_head,
"fieldname": frappe.scrub(account_head),
"fieldtype": "Float",
"width": 110
})
return itemised_tax, tax_columns
def update_entries_after(args, allow_zero_rate=False, verbose=1):
"""
update valution rate and qty after transaction
from the current time-bucket onwards
args = {
"item_code": "ABC",
"warehouse": "XYZ",
"posting_date": "2012-12-12",
"posting_time": "12:00"
}
"""
if not _exceptions:
frappe.local.stockledger_exceptions = []
previous_sle = get_sle_before_datetime(args)
qty_after_transaction = flt(previous_sle.get("qty_after_transaction"))
valuation_rate = flt(previous_sle.get("valuation_rate"))
stock_queue = json.loads(previous_sle.get("stock_queue") or "[]")
stock_value = flt(previous_sle.get("stock_value"))
prev_stock_value = flt(previous_sle.get("stock_value"))
entries_to_fix = get_sle_after_datetime(previous_sle or \
{"item_code": args["item_code"], "warehouse": args["warehouse"]}, for_update=True)
valuation_method = get_valuation_method(args["item_code"])
stock_value_difference = 0.0
for sle in entries_to_fix:
if sle.serial_no or not cint(
frappe.db.get_value("Stock Settings", None,
"allow_negative_stock")):
# validate negative stock for serialized items, fifo valuation
# or when negative stock is not allowed for moving average
if not validate_negative_stock(qty_after_transaction, sle):
qty_after_transaction += flt(sle.actual_qty)
continue
if sle.serial_no:
valuation_rate = get_serialized_values(qty_after_transaction, sle,
valuation_rate)
qty_after_transaction += flt(sle.actual_qty)
else:
if sle.voucher_type == "Stock Reconciliation":
valuation_rate = sle.valuation_rate
qty_after_transaction = sle.qty_after_transaction
stock_queue = [[qty_after_transaction, valuation_rate]]
else:
if valuation_method == "Moving Average":
valuation_rate = get_moving_average_values(
qty_after_transaction, sle, valuation_rate,
allow_zero_rate)
else:
valuation_rate = get_fifo_values(qty_after_transaction,
sle, stock_queue,
allow_zero_rate)
qty_after_transaction += flt(sle.actual_qty)
# get stock value
if sle.serial_no:
stock_value = qty_after_transaction * valuation_rate
elif valuation_method == "Moving Average":
stock_value = qty_after_transaction * valuation_rate
else:
stock_value = sum(
(flt(batch[0]) * flt(batch[1]) for batch in stock_queue))
# rounding as per precision
from frappe.model.meta import get_field_precision
meta = frappe.get_meta("Stock Ledger Entry")
stock_value = flt(
stock_value,
get_field_precision(meta.get_field("stock_value"),
frappe._dict({"fields": sle})))
stock_value_difference = stock_value - prev_stock_value
prev_stock_value = stock_value
# update current sle
frappe.db.sql(
"""update `tabStock Ledger Entry`
set qty_after_transaction=%s, valuation_rate=%s, stock_queue=%s,
stock_value=%s, stock_value_difference=%s where name=%s""",
(qty_after_transaction, valuation_rate, json.dumps(stock_queue),
stock_value, stock_value_difference, sle.name))
if _exceptions:
_raise_exceptions(args, verbose)
# update bin
if not frappe.db.exists({
"doctype": "Bin",
"item_code": args["item_code"],
"warehouse": args["warehouse"]
}):
bin_wrapper = frappe.get_doc({
"doctype": "Bin",
"item_code": args["item_code"],
"warehouse": args["warehouse"],
})
bin_wrapper.ignore_permissions = 1
bin_wrapper.insert()
frappe.db.sql(
"""update `tabBin` set valuation_rate=%s, actual_qty=%s,
stock_value=%s,
projected_qty = (actual_qty + indented_qty + ordered_qty + planned_qty - reserved_qty)
where item_code=%s and warehouse=%s""",
(valuation_rate, qty_after_transaction, stock_value, args["item_code"],
args["warehouse"]))
def format_value(value, df=None, doc=None, currency=None, translated=False):
'''Format value based on given fieldtype, document reference, currency reference.
If docfield info (df) is not given, it will try and guess based on the datatype of the value'''
if isinstance(df, string_types):
df = frappe._dict(fieldtype=df)
if not df:
df = frappe._dict()
if isinstance(value, datetime.datetime):
df.fieldtype = 'Datetime'
elif isinstance(value, datetime.date):
df.fieldtype = 'Date'
elif isinstance(value, datetime.timedelta):
df.fieldtype = 'Time'
elif isinstance(value, int):
df.fieldtype = 'Int'
elif isinstance(value, float):
df.fieldtype = 'Float'
else:
df.fieldtype = 'Data'
elif (isinstance(df, dict)):
# Convert dict to object if necessary
df = frappe._dict(df)
if value is None:
value = ""
elif translated:
value = frappe._(value)
if not df:
return value
elif df.get("fieldtype")=="Date":
return formatdate(value)
elif df.get("fieldtype")=="Datetime":
return format_datetime(value)
elif df.get("fieldtype")=="Time":
return format_time(value)
elif value==0 and df.get("fieldtype") in ("Int", "Float", "Currency", "Percent") and df.get("print_hide_if_no_value"):
# this is required to show 0 as blank in table columns
return ""
elif df.get("fieldtype") == "Currency" or (df.get("fieldtype")=="Float" and (df.options or "").strip()):
return fmt_money(value, precision=get_field_precision(df, doc),
currency=currency if currency else (get_field_currency(df, doc) if doc else None))
elif df.get("fieldtype") == "Float":
precision = get_field_precision(df, doc)
# show 1.000000 as 1
# options should not specified
if not df.options and value is not None:
temp = cstr(value).split(".")
if len(temp)==1 or cint(temp[1])==0:
precision = 0
return fmt_money(value, precision=precision)
elif df.get("fieldtype") == "Percent":
return "{}%".format(flt(value, 2))
elif df.get("fieldtype") in ("Text", "Small Text"):
if not re.search("(\<br|\<div|\<p)", value):
return value.replace("\n", "<br>")
return value
def get_tax_accounts(item_list, columns, company_currency,
doctype="Sales Invoice", tax_doctype="Sales Taxes and Charges"):
import json
item_row_map = {}
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
conditions = ""
tax_amount_precision = get_field_precision(frappe.get_meta(tax_doctype).get_field("tax_amount"),
currency=company_currency) or 2
for d in item_list:
invoice_item_row.setdefault(d.parent, []).append(d)
item_row_map.setdefault(d.parent, {}).setdefault(d.item_code or d.item_name, []).append(d)
tax_details = frappe.db.sql("""
select
parent, description, item_wise_tax_detail,
base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
and (description is not null and description != '')
and parent in (%s)
%s
order by description
""" % (tax_doctype, '%s', ', '.join(['%s']*len(invoice_item_row)), conditions),
tuple([doctype] + list(invoice_item_row)))
for parent, description, item_wise_tax_detail, tax_amount in tax_details:
description = handle_html(description)
if description not in tax_columns and tax_amount:
# as description is text editor earlier and markup can break the column convention in reports
tax_columns.append(description)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item_code, tax_data in item_wise_tax_detail.items():
if not frappe.db.get_value("Item", item_code, "gst_hsn_code"):
continue
itemised_tax.setdefault(item_code, frappe._dict())
if isinstance(tax_data, list):
tax_amount = tax_data[1]
else:
tax_amount = 0
for d in item_row_map.get(parent, {}).get(item_code, []):
item_tax_amount = tax_amount
if item_tax_amount:
itemised_tax.setdefault(d.name, {})[description] = frappe._dict({
"tax_amount": flt(item_tax_amount, tax_amount_precision)
})
except ValueError:
continue
tax_columns.sort()
for desc in tax_columns:
columns.append(desc + " Amount:Currency/currency:160")
# columns += ["Total Amount:Currency/currency:110"]
return itemised_tax, tax_columns
def update_entries_after(args, allow_zero_rate=False, allow_negative_stock=False, verbose=1):
"""
update valution rate and qty after transaction
from the current time-bucket onwards
args = {
"item_code": "ABC",
"warehouse": "XYZ",
"posting_date": "2012-12-12",
"posting_time": "12:00"
}
"""
if not _exceptions:
frappe.local.stockledger_exceptions = []
if not allow_negative_stock:
allow_negative_stock = cint(frappe.db.get_default("allow_negative_stock"))
previous_sle = get_sle_before_datetime(args)
qty_after_transaction = flt(previous_sle.get("qty_after_transaction"))
valuation_rate = flt(previous_sle.get("valuation_rate"))
stock_queue = json.loads(previous_sle.get("stock_queue") or "[]")
stock_value = flt(previous_sle.get("stock_value"))
prev_stock_value = flt(previous_sle.get("stock_value"))
entries_to_fix = get_sle_after_datetime(
previous_sle or {"item_code": args["item_code"], "warehouse": args["warehouse"]}, for_update=True
)
valuation_method = get_valuation_method(args["item_code"])
stock_value_difference = 0.0
for sle in entries_to_fix:
if sle.serial_no or not allow_negative_stock:
# validate negative stock for serialized items, fifo valuation
# or when negative stock is not allowed for moving average
if not validate_negative_stock(qty_after_transaction, sle):
qty_after_transaction += flt(sle.actual_qty)
continue
if sle.serial_no:
valuation_rate = get_serialized_values(qty_after_transaction, sle, valuation_rate)
qty_after_transaction += flt(sle.actual_qty)
else:
if sle.voucher_type == "Stock Reconciliation":
valuation_rate = sle.valuation_rate
qty_after_transaction = sle.qty_after_transaction
stock_queue = [[qty_after_transaction, valuation_rate]]
else:
if valuation_method == "Moving Average":
valuation_rate = get_moving_average_values(
qty_after_transaction, sle, valuation_rate, allow_zero_rate
)
else:
valuation_rate = get_fifo_values(qty_after_transaction, sle, stock_queue, allow_zero_rate)
qty_after_transaction += flt(sle.actual_qty)
# get stock value
if sle.serial_no:
stock_value = qty_after_transaction * valuation_rate
elif valuation_method == "Moving Average":
stock_value = qty_after_transaction * valuation_rate
else:
stock_value = sum((flt(batch[0]) * flt(batch[1]) for batch in stock_queue))
# rounding as per precision
from frappe.model.meta import get_field_precision
meta = frappe.get_meta("Stock Ledger Entry")
stock_value = flt(
stock_value, get_field_precision(meta.get_field("stock_value"), frappe._dict({"fields": sle}))
)
stock_value_difference = stock_value - prev_stock_value
prev_stock_value = stock_value
# update current sle
frappe.db.sql(
"""update `tabStock Ledger Entry`
set qty_after_transaction=%s, valuation_rate=%s, stock_queue=%s,
stock_value=%s, stock_value_difference=%s where name=%s""",
(
qty_after_transaction,
valuation_rate,
json.dumps(stock_queue),
stock_value,
stock_value_difference,
sle.name,
),
)
if _exceptions:
_raise_exceptions(args, verbose)
# update bin
if not frappe.db.exists({"doctype": "Bin", "item_code": args["item_code"], "warehouse": args["warehouse"]}):
bin_wrapper = frappe.get_doc({"doctype": "Bin", "item_code": args["item_code"], "warehouse": args["warehouse"]})
bin_wrapper.ignore_permissions = 1
bin_wrapper.insert()
frappe.db.sql(
"""update `tabBin` set valuation_rate=%s, actual_qty=%s,
stock_value=%s,
projected_qty = (actual_qty + indented_qty + ordered_qty + planned_qty - reserved_qty)
where item_code=%s and warehouse=%s""",
(valuation_rate, qty_after_transaction, stock_value, args["item_code"], args["warehouse"]),
)
def update_values(dt, tax_table):
frappe.reload_doctype(dt)
frappe.reload_doctype(dt + " Item")
frappe.reload_doctype(tax_table)
net_total_precision = get_field_precision(frappe.get_meta(dt).get_field("net_total"))
for field in ("total", "base_total", "base_net_total"):
make_property_setter(dt, field, "precision", net_total_precision, "Select")
rate_field_precision = get_field_precision(frappe.get_meta(dt + " Item").get_field("rate"))
for field in ("net_rate", "base_net_rate", "net_amount", "base_net_amount", "base_rate", "base_amount"):
make_property_setter(dt + " Item", field, "precision", rate_field_precision, "Select")
tax_amount_precision = get_field_precision(frappe.get_meta(tax_table).get_field("tax_amount"))
for field in ("base_tax_amount", "total", "base_total", "tax_amount_after_discount_amount",
"base_tax_amount_after_discount_amount"):
make_property_setter(tax_table, field, "precision", tax_amount_precision, "Select")
# update net_total, discount_on
frappe.db.sql("""
UPDATE
`tab{0}`
SET
total = round(net_total, {1}),
base_total = round(net_total*conversion_rate, {1}),
net_total = round(base_net_total / conversion_rate, {1}),
apply_discount_on = "Grand Total"
WHERE
docstatus < 2
""".format(dt, net_total_precision))
# update net_amount
frappe.db.sql("""
UPDATE
`tab{0}` par, `tab{1}` item
SET
item.base_net_amount = round(item.base_amount, {2}),
item.base_net_rate = round(item.base_rate, {2}),
item.net_amount = round(item.base_amount / par.conversion_rate, {2}),
item.net_rate = round(item.base_rate / par.conversion_rate, {2}),
item.base_amount = round(item.amount * par.conversion_rate, {2}),
item.base_rate = round(item.rate * par.conversion_rate, {2})
WHERE
par.name = item.parent
and par.docstatus < 2
""".format(dt, dt + " Item", rate_field_precision))
# update tax in party currency
frappe.db.sql("""
UPDATE
`tab{0}` par, `tab{1}` tax
SET
tax.base_tax_amount = round(tax.tax_amount, {2}),
tax.tax_amount = round(tax.tax_amount / par.conversion_rate, {2}),
tax.base_total = round(tax.total, {2}),
tax.total = round(tax.total / conversion_rate, {2}),
tax.base_tax_amount_after_discount_amount = round(tax.tax_amount_after_discount_amount, {2}),
tax.tax_amount_after_discount_amount = round(tax.tax_amount_after_discount_amount / conversion_rate, {2})
WHERE
par.name = tax.parent
and par.docstatus < 2
""".format(dt, tax_table, tax_amount_precision))
def get_tax_accounts(item_list,
columns,
company_currency,
doctype="Sales Invoice",
tax_doctype="Sales Taxes and Charges"):
import json
item_row_map = {}
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
tax_amount_precision = get_field_precision(
frappe.get_meta(tax_doctype).get_field("tax_amount"),
currency=company_currency) or 2
for d in item_list:
invoice_item_row.setdefault(d.parent, []).append(d)
item_row_map.setdefault(d.parent,
{}).setdefault(d.item_code or d.item_name,
[]).append(d)
conditions = ""
if doctype == "Purchase Invoice":
conditions = " and category in ('Total', 'Valuation and Total') and base_tax_amount_after_discount_amount != 0"
deducted_tax = get_deducted_taxes()
tax_details = frappe.db.sql(
"""
select
name, parent, description, item_wise_tax_detail,
charge_type, base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
and (description is not null and description != '')
and parent in (%s)
%s
order by description
""" % (tax_doctype, '%s', ', '.join(
['%s'] * len(invoice_item_row)), conditions),
tuple([doctype] + list(invoice_item_row)))
for name, parent, description, item_wise_tax_detail, charge_type, tax_amount in tax_details:
description = handle_html(description)
if description not in tax_columns and tax_amount:
# as description is text editor earlier and markup can break the column convention in reports
tax_columns.append(description)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item_code, tax_data in item_wise_tax_detail.items():
itemised_tax.setdefault(item_code, frappe._dict())
if isinstance(tax_data, list):
tax_rate, tax_amount = tax_data
else:
tax_rate = tax_data
tax_amount = 0
if charge_type == "Actual" and not tax_rate:
tax_rate = "NA"
item_net_amount = sum([
flt(d.base_net_amount) for d in item_row_map.get(
parent, {}).get(item_code, [])
])
for d in item_row_map.get(parent, {}).get(item_code, []):
item_tax_amount = flt((tax_amount * d.base_net_amount) / item_net_amount) \
if item_net_amount else 0
if item_tax_amount:
tax_amount = flt(item_tax_amount,
tax_amount_precision)
tax_amount = (tax_amount * -1 if
(doctype == 'Purchase Invoice'
and name in deducted_tax) else
tax_amount)
itemised_tax.setdefault(
d.name, {})[description] = frappe._dict({
"tax_rate":
tax_rate,
"tax_amount":
tax_amount
})
except ValueError:
continue
elif charge_type == "Actual" and tax_amount:
for d in invoice_item_row.get(parent, []):
itemised_tax.setdefault(
d.name, {})[description] = frappe._dict({
"tax_rate":
"NA",
"tax_amount":
flt((tax_amount * d.base_net_amount) /
d.base_net_total, tax_amount_precision)
})
tax_columns.sort()
for desc in tax_columns:
columns.append(desc + " Rate:Data:80")
columns.append(desc + " Amount:Currency/currency:100")
columns += [
"Total Tax:Currency/currency:80", "Total:Currency/currency:100"
]
return itemised_tax, tax_columns
def get_tax_accounts(item_list, columns, company_currency,
doctype="Sales Invoice", tax_doctype="Sales Taxes and Charges"):
import json
item_row_map = {}
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
tax_amount_precision = get_field_precision(frappe.get_meta(tax_doctype).get_field("tax_amount"),
currency=company_currency) or 2
for d in item_list:
invoice_item_row.setdefault(d.parent, []).append(d)
item_row_map.setdefault(d.parent, {}).setdefault(d.item_code, []).append(d)
conditions = ""
if doctype == "Purchase Invoice":
conditions = " and category in ('Total', 'Valuation and Total')"
tax_details = frappe.db.sql("""
select
parent, description, item_wise_tax_detail,
charge_type, base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
and (description is not null and description != '')
and parent in (%s)
%s
order by description
""" % (tax_doctype, '%s', ', '.join(['%s']*len(invoice_item_row)), conditions),
tuple([doctype] + invoice_item_row.keys()))
for parent, description, item_wise_tax_detail, charge_type, tax_amount in tax_details:
if description not in tax_columns and tax_amount:
tax_columns.append(description)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item_code, tax_data in item_wise_tax_detail.items():
itemised_tax.setdefault(item_code, frappe._dict())
if isinstance(tax_data, list):
tax_rate, tax_amount = tax_data
else:
tax_rate = tax_data
tax_amount = 0
if charge_type == "Actual" and not tax_rate:
tax_rate = "NA"
item_net_amount = sum([flt(d.base_net_amount)
for d in item_row_map.get(parent, {}).get(item_code, [])])
for d in item_row_map.get(parent, {}).get(item_code, []):
item_tax_amount = flt((tax_amount * d.base_net_amount) / item_net_amount) \
if item_net_amount else 0
if item_tax_amount:
itemised_tax.setdefault(d.name, {})[description] = frappe._dict({
"tax_rate": tax_rate,
"tax_amount": flt(item_tax_amount, tax_amount_precision)
})
except ValueError:
continue
elif charge_type == "Actual" and tax_amount:
for d in invoice_item_row.get(parent, []):
itemised_tax.setdefault(d.name, {})[description] = frappe._dict({
"tax_rate": "NA",
"tax_amount": flt((tax_amount * d.base_net_amount) / d.base_net_total,
tax_amount_precision)
})
tax_columns.sort()
for desc in tax_columns:
columns.append(desc + " Rate:Data:80")
columns.append(desc + " Amount:Currency/currency:100")
columns += ["Total Tax:Currency/currency:80", "Total:Currency/currency:100"]
return itemised_tax, tax_columns
def get_precision(doctype, fieldname, currency=None, doc=None): """Get precision for a given field""" from frappe.model.meta import get_field_precision return get_field_precision(get_meta(doctype).get_field(fieldname), doc, currency)
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 = 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)
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 = [inv.name, inv.posting_date, inv.customer, inv.customer_name]
if additional_query_columns:
for col in additional_query_columns:
row.append(inv.get(col))
row += [
inv.get("customer_group"),
inv.get("territory"),
inv.get("tax_id"), inv.debit_to,
", ".join(mode_of_payments.get(inv.name,
[])), inv.project, inv.owner,
inv.remarks, ", ".join(sales_order), ", ".join(delivery_note),
", ".join(cost_center), ", ".join(warehouse), company_currency
]
# map income values
base_net_total = 0
for income_acc in income_accounts:
income_amount = flt(
invoice_income_map.get(inv.name, {}).get(income_acc))
base_net_total += income_amount
row.append(income_amount)
# net total
row.append(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)
row.append(tax_amount)
# total tax, grand total, outstanding amount & rounded total
row += [
total_tax, inv.base_grand_total, inv.base_rounded_total,
inv.outstanding_amount
]
data.append(row)
return columns, data
def get_precision(doctype, fieldname, currency=None, doc=None): """Get precision for a given field""" from frappe.model.meta import get_field_precision return get_field_precision(get_meta(doctype).get_field(fieldname), doc, currency)
def format_value(value, df=None, doc=None, currency=None, translated=False):
'''Format value based on given fieldtype, document reference, currency reference.
If docfield info (df) is not given, it will try and guess based on the datatype of the value'''
if isinstance(df, basestring):
df = frappe._dict(fieldtype=df)
if not df:
df = frappe._dict()
if isinstance(value, datetime.datetime):
df.fieldtype = 'Datetime'
elif isinstance(value, datetime.date):
df.fieldtype = 'Date'
elif isinstance(value, int):
df.fieldtype = 'Int'
elif isinstance(value, float):
df.fieldtype = 'Float'
else:
df.fieldtype = 'Data'
elif (isinstance(df, dict)):
# Convert dict to object if necessary
df = frappe._dict(df)
if value is None:
value = ""
elif translated:
value = frappe._(value)
if not df:
return value
elif df.get("fieldtype") == "Date":
return formatdate(value)
elif df.get("fieldtype") == "Datetime":
return format_datetime(value)
elif value == 0 and df.get("fieldtype") in (
"Int", "Float", "Currency",
"Percent") and df.get("print_hide_if_no_value"):
# this is required to show 0 as blank in table columns
return ""
elif df.get("fieldtype") == "Currency" or (df.get("fieldtype") == "Float"
and (df.options or "").strip()):
return fmt_money(value,
precision=get_field_precision(df, doc),
currency=currency if currency else
(get_field_currency(df, doc) if doc else None))
elif df.get("fieldtype") == "Float":
precision = get_field_precision(df, doc)
# show 1.000000 as 1
# options should not specified
if not df.options and value is not None:
temp = cstr(value).split(".")
if len(temp) == 1 or cint(temp[1]) == 0:
precision = 0
return fmt_money(value, precision=precision)
elif df.get("fieldtype") == "Percent":
return "{}%".format(flt(value, 2))
elif df.get("fieldtype") in ("Text", "Small Text"):
if not re.search("(\<br|\<div|\<p)", value):
return value.replace("\n", "<br>")
return value
