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