def validate_conversion_rate(args, meta):
from epaas.controllers.accounts_controller import validate_conversion_rate
if (not args.conversion_rate
and args.currency==dataent.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"),
dataent._dict({"fields": args})))
if args.price_list:
if (not args.plc_conversion_rate
and args.price_list_currency==dataent.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"),
dataent._dict({"fields": args})))
def update_values(dt, tax_table):
rate_field_precision = get_field_precision(
dataent.get_meta(dt + " Item").get_field("rate"))
tax_amount_precision = get_field_precision(
dataent.get_meta(tax_table).get_field("tax_amount"))
# update net_total, discount_on
dataent.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
dataent.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
dataent.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 get_accounts_data(self, account=None):
accounts = []
self.validate_mandatory()
company_currency = epaas.get_company_currency(self.company)
precision = get_field_precision(dataent.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 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 dataent.model.meta import get_field_precision
if parentfield and not isinstance(parentfield, string_types):
parentfield = parentfield.parentfield
cache_key = parentfield or "main"
if not hasattr(self, "_precision"):
self._precision = dataent._dict()
if cache_key not in self._precision:
self._precision[cache_key] = dataent._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 = dataent.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_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:
dataent.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(dataent.get_meta(
"Landed Cost Item").get_field("applicable_charges"),
currency=dataent.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:
dataent.throw(
_("Total Applicable Charges in Purchase Receipt Items table must be same as Total Taxes and Charges"
))
def round_off_debit_credit(gl_map):
precision = get_field_precision(
dataent.get_meta("GL Entry").get_field("debit"),
currency=dataent.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:
dataent.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 get_ordered_to_be_billed_data(args):
doctype, party = args.get('doctype'), args.get('party')
child_tab = doctype + " Item"
precision = get_field_precision(
dataent.get_meta(child_tab).get_field("billed_amt"),
currency=get_default_currency()) or 2
project_field = get_project_field(doctype, party)
return dataent.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 not in ('Closed', 'Completed')
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 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 = epaas.get_company_currency(doc.company)
stock_qty_precision = get_field_precision(
dataent.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:
dataent.throw(
_("{0} must be negative in return document").format(label))
elif returned_qty >= reference_qty and args.get(column):
dataent.throw(
_("Item {0} has already been returned").format(
args.item_code), StockOverReturnError)
elif abs(flt(current_stock_qty,
stock_qty_precision)) > max_returnable_qty:
dataent.throw(
_("Row # {0}: Cannot return more than {1} for Item {2}"
).format(args.idx, max_returnable_qty, args.item_code),
StockOverReturnError)
def __init__(self,
args,
allow_zero_rate=False,
allow_negative_stock=None,
via_landed_cost_voucher=False,
verbose=1):
from dataent.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(
dataent.db.get_single_value("Stock Settings",
"allow_negative_stock"))
self.args = args
for key, value in iteritems(args):
setattr(self, key, value)
self.previous_sle = self.get_sle_before_datetime()
self.previous_sle = self.previous_sle[
0] if self.previous_sle else dataent._dict()
for key in ("qty_after_transaction", "valuation_rate", "stock_value"):
setattr(self, key, flt(self.previous_sle.get(key)))
self.company = dataent.db.get_value("Warehouse", self.warehouse,
"company")
self.precision = get_field_precision(
dataent.get_meta("Stock Ledger Entry").get_field("stock_value"),
currency=dataent.get_cached_value('Company', self.company,
"default_currency"))
self.prev_stock_value = self.previous_sle.stock_value or 0.0
self.stock_queue = json.loads(self.previous_sle.stock_queue or "[]")
self.valuation_method = get_valuation_method(self.item_code)
self.stock_value_difference = 0.0
self.build()
def update_against_account(voucher_type, voucher_no):
entries = dataent.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 = epaas.get_company_currency(entries[0].company)
precision = get_field_precision(dataent.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:
dataent.db.set_value("GL Entry", d.name, "against", new_against)
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(dataent.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 = dataent.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 dataent.db.get_value("Item", item_code, "gst_hsn_code"):
continue
itemised_tax.setdefault(item_code, dataent._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] = dataent._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 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(
dataent.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 = dataent.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, dataent._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_value = flt(item_tax_amount,
tax_amount_precision)
tax_value = (tax_value * -1 if
(doctype == 'Purchase Invoice'
and name in deducted_tax) else
tax_value)
itemised_tax.setdefault(
d.name, {})[description] = dataent._dict({
"tax_rate":
tax_rate,
"tax_amount":
tax_value
})
except ValueError:
continue
elif charge_type == "Actual" and tax_amount:
for d in invoice_item_row.get(parent, []):
itemised_tax.setdefault(
d.name, {})[description] = dataent._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 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 = dataent._dict(fieldtype=df)
if not df:
df = dataent._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 = dataent._dict(df)
if value is None:
value = ""
elif translated:
value = dataent._(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 update_values(dt, tax_table):
dataent.reload_doctype(dt)
dataent.reload_doctype(dt + " Item")
dataent.reload_doctype(tax_table)
net_total_precision = get_field_precision(
dataent.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(
dataent.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(
dataent.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
dataent.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
dataent.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
dataent.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))