def create_remarks(self):
r = []
if self.cheque_no:
if self.cheque_date:
r.append(_('Reference #{0} dated {1}').format(self.cheque_no, formatdate(self.cheque_date)))
else:
msgprint(_("Please enter Reference date"), raise_exception=1)
for d in self.get('entries'):
if d.against_invoice and d.credit:
currency = frappe.db.get_value("Sales Invoice", d.against_invoice, "currency")
r.append(_("{0} {1} against Invoice {1}").format(currency, fmt_money(flt(d.credit)), d.against_invoice))
if d.against_voucher and d.debit:
bill_no = frappe.db.sql("""select bill_no, bill_date, currency
from `tabPurchase Invoice` where name=%s""", d.against_voucher)
if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() \
not in ['na', 'not applicable', 'none']:
r.append(_('{0} {1} against Bill {2} dated {3}').format(bill_no[0][2],
fmt_money(flt(d.debit)), bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d'))))
if self.user_remark:
r.append(_("Note: {0}").format(self.user_remark))
if r:
self.remark = ("\n").join(r)
else:
frappe.msgprint(_("User Remarks is mandatory"), raise_exception=1)
def calculate_sales(self):
ss_list = frappe.db.sql("""
select name,title,grand_total,transaction_date from `tabQuotation` where project = %s and docstatus < 2""", self.name,as_dict = 1)
frappe.errprint(ss_list)
totalsales = 0
joiningtext = """<table class="table table-bordered table-condensed">
<tr>
<td>Document</td>
<td>Title</td>
<td>Date</td>
<td>Grand Total</td>
</tr>"""
for i, d in enumerate(ss_list):
joiningtext += """<tr>
<td>""" + str(d["name"]) +"""</td>
<td>""" + str(d["title"]) +"""</td>
<td>""" + str(d["transaction_date"]) +"""</td>
<td>""" + str(fmt_money(d["grand_total"], precision=2)) +"""</td>
</tr>"""
totalsales = totalsales + flt(d["grand_total"])
joiningtext += """<tr>
<td>Project Total</td>
<td></td>
<td></td>
<td>""" + str(fmt_money(totalsales, precision=2)) +"""</td>
</tr>"""
joiningtext += """</table><br>"""
self.sales_orders = joiningtext
def validate_overlapping_shipping_rule_conditions(self):
def overlap_exists_between(num_range1, num_range2):
"""
num_range1 and num_range2 are two ranges
ranges are represented as a tuple e.g. range 100 to 300 is represented as (100, 300)
if condition num_range1 = 100 to 300
then condition num_range2 can only be like 50 to 99 or 301 to 400
hence, non-overlapping condition = (x1 <= x2 < y1 <= y2) or (y1 <= y2 < x1 <= x2)
"""
(x1, x2), (y1, y2) = num_range1, num_range2
separate = (x1 <= x2 <= y1 <= y2) or (y1 <= y2 <= x1 <= x2)
return (not separate)
overlaps = []
for i in xrange(0, len(self.conditions)):
for j in xrange(i+1, len(self.conditions)):
d1, d2 = self.conditions[i], self.conditions[j]
if d1.as_dict() != d2.as_dict():
# in our case, to_value can be zero, hence pass the from_value if so
range_a = (d1.from_value, d1.to_value or d1.from_value)
range_b = (d2.from_value, d2.to_value or d2.from_value)
if overlap_exists_between(range_a, range_b):
overlaps.append([d1, d2])
if overlaps:
company_currency = erpnext.get_company_currency(self.company)
msgprint(_("Overlapping conditions found between:"))
messages = []
for d1, d2 in overlaps:
messages.append("%s-%s = %s " % (d1.from_value, d1.to_value, fmt_money(d1.shipping_amount, currency=company_currency)) +
_("and") + " %s-%s = %s" % (d2.from_value, d2.to_value, fmt_money(d2.shipping_amount, currency=company_currency)))
msgprint("\n".join(messages), raise_exception=OverlappingConditionError)
def validate_warehouse_account(self):
'''If perpetual inventory is set, and warehouse is linked,
the account balance and stock balance as of now must always match.
'''
from erpnext.accounts.utils import get_balance_on
from erpnext.stock.utils import get_stock_value_on
if not cint(frappe.defaults.get_global_default("auto_accounting_for_stock")):
return
if self.account_type == "Stock":
if self.is_group == 0 and not self.warehouse:
frappe.throw(_("Warehouse is mandatory for non group Accounts of type Stock"))
if self.warehouse:
# company must be same
if frappe.get_value('Warehouse', self.warehouse, 'company') != self.company:
frappe.throw(_("Warehouse company must be same as Account company"))
# balance must be same
stock_balance = get_stock_value_on(self.warehouse)
if self.is_new():
account_balance = 0.0
else:
account_balance = get_balance_on(self.name)
if account_balance != stock_balance:
frappe.throw(_('Account balance ({0}) and stock value ({1}) must be same')\
.format(fmt_money(account_balance, currency=self.account_currency),
fmt_money(stock_balance, currency=self.account_currency)))
elif self.warehouse:
self.warehouse = None
def validate_orders(self):
"""Validate totals, closed and docstatus for orders"""
for reference_name, total in self.reference_totals.iteritems():
reference_type = self.reference_types[reference_name]
account = self.reference_accounts[reference_name]
if reference_type in ("Sales Order", "Purchase Order"):
order = frappe.get_doc(reference_type, reference_name)
if order.docstatus != 1:
frappe.throw(_("{0} {1} is not submitted").format(reference_type, reference_name))
if flt(order.per_billed) >= 100:
frappe.throw(_("{0} {1} is fully billed").format(reference_type, reference_name))
if cstr(order.status) == "Closed":
frappe.throw(_("{0} {1} is closed").format(reference_type, reference_name))
account_currency = get_account_currency(account)
if account_currency == self.company_currency:
voucher_total = order.base_grand_total
formatted_voucher_total = fmt_money(voucher_total, order.precision("base_grand_total"),
currency=account_currency)
else:
voucher_total = order.grand_total
formatted_voucher_total = fmt_money(voucher_total, order.precision("grand_total"),
currency=account_currency)
if flt(voucher_total) < (flt(order.advance_paid) + total):
frappe.throw(_("Advance paid against {0} {1} cannot be greater \
than Grand Total {2}").format(reference_type, reference_name, formatted_voucher_total))
def create_remarks(self):
r = []
if self.cheque_no:
if self.cheque_date:
r.append(_('Reference #{0} dated {1}').format(self.cheque_no, formatdate(self.cheque_date)))
else:
msgprint(_("Please enter Reference date"), raise_exception=frappe.MandatoryError)
for d in self.get('accounts'):
if d.reference_type=="Sales Invoice" and d.credit:
r.append(_("{0} against Sales Invoice {1}").format(fmt_money(flt(d.credit), currency = self.company_currency), \
d.reference_name))
if d.reference_type=="Sales Order" and d.credit:
r.append(_("{0} against Sales Order {1}").format(fmt_money(flt(d.credit), currency = self.company_currency), \
d.reference_name))
if d.reference_type == "Purchase Invoice" and d.debit:
bill_no = frappe.db.sql("""select bill_no, bill_date
from `tabPurchase Invoice` where name=%s""", d.reference_name)
if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() \
not in ['na', 'not applicable', 'none']:
r.append(_('{0} against Bill {1} dated {2}').format(fmt_money(flt(d.debit), currency=self.company_currency), bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d'))))
if d.reference_type == "Purchase Order" and d.debit:
r.append(_("{0} against Purchase Order {1}").format(fmt_money(flt(d.credit), currency = self.company_currency), \
d.reference_name))
if self.user_remark:
r.append(_("Note: {0}").format(self.user_remark))
if r:
self.remark = ("\n").join(r) #User Remarks is not mandatory
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 get_price(item_code, price_list, customer_group, company, qty=1):
template_item_code = frappe.db.get_value("Item", item_code, "variant_of")
if price_list:
price = frappe.get_all("Item Price", fields=["price_list_rate", "currency"],
filters={"price_list": price_list, "item_code": item_code})
if template_item_code and not price:
price = frappe.get_all("Item Price", fields=["price_list_rate", "currency"],
filters={"price_list": price_list, "item_code": template_item_code})
if price:
pricing_rule = get_pricing_rule_for_item(frappe._dict({
"item_code": item_code,
"qty": qty,
"transaction_type": "selling",
"price_list": price_list,
"customer_group": customer_group,
"company": company,
"conversion_rate": 1,
"for_shopping_cart": True,
"currency": frappe.db.get_value("Price List", price_list, "currency")
}))
if pricing_rule:
if pricing_rule.pricing_rule_for == "Discount Percentage":
price[0].price_list_rate = flt(price[0].price_list_rate * (1.0 - (flt(pricing_rule.discount_percentage) / 100.0)))
if pricing_rule.pricing_rule_for == "Rate":
price[0].price_list_rate = pricing_rule.price_list_rate
price_obj = price[0]
if price_obj:
price_obj["formatted_price"] = fmt_money(price_obj["price_list_rate"], currency=price_obj["currency"])
price_obj["currency_symbol"] = not cint(frappe.db.get_default("hide_currency_symbol")) \
and (frappe.db.get_value("Currency", price_obj.currency, "symbol", cache=True) or price_obj.currency) \
or ""
uom_conversion_factor = frappe.db.sql("""select C.conversion_factor
from `tabUOM Conversion Detail` C
inner join `tabItem` I on C.parent = I.name and C.uom = I.sales_uom
where I.name = %s""", item_code)
uom_conversion_factor = uom_conversion_factor[0][0] if uom_conversion_factor else 1
price_obj["formatted_price_sales_uom"] = fmt_money(price_obj["price_list_rate"] * uom_conversion_factor, currency=price_obj["currency"])
if not price_obj["price_list_rate"]:
price_obj["price_list_rate"] = 0
if not price_obj["currency"]:
price_obj["currency"] = ""
if not price_obj["formatted_price"]:
price_obj["formatted_price"] = ""
return price_obj
def create_remarks(self):
r = []
if self.cheque_no:
if self.cheque_date:
r.append(_("Reference #{0} dated {1}").format(self.cheque_no, formatdate(self.cheque_date)))
else:
msgprint(_("Please enter Reference date"), raise_exception=frappe.MandatoryError)
for d in self.get("entries"):
if d.against_invoice and d.credit:
currency = frappe.db.get_value("Sales Invoice", d.against_invoice, "currency")
r.append(
_("{0} against Sales Invoice {1}").format(
fmt_money(flt(d.credit), currency=currency), d.against_invoice
)
)
if d.against_sales_order and d.credit:
currency = frappe.db.get_value("Sales Order", d.against_sales_order, "currency")
r.append(
_("{0} against Sales Order {1}").format(
fmt_money(flt(d.credit), currency=currency), d.against_sales_order
)
)
if d.against_voucher and d.debit:
bill_no = frappe.db.sql(
"""select bill_no, bill_date, currency
from `tabPurchase Invoice` where name=%s""",
d.against_voucher,
)
if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() not in ["na", "not applicable", "none"]:
r.append(
_("{0} {1} against Bill {2} dated {3}").format(
bill_no[0][2],
fmt_money(flt(d.debit)),
bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime("%Y-%m-%d")),
)
)
if d.against_purchase_order and d.debit:
currency = frappe.db.get_value("Purchase Order", d.against_purchase_order, "currency")
r.append(
_("{0} against Purchase Order {1}").format(
fmt_money(flt(d.credit), currency=currency), d.against_purchase_order
)
)
if self.user_remark:
r.append(_("Note: {0}").format(self.user_remark))
if r:
self.remark = ("\n").join(r) # User Remarks is not mandatory
def set_total_advance_paid(self):
if self.doctype == "Sales Order":
dr_or_cr = "credit_in_account_currency"
party = self.customer
else:
dr_or_cr = "debit_in_account_currency"
party = self.supplier
advance = frappe.db.sql(
"""
select
account_currency, sum({dr_or_cr}) as amount
from
`tabGL Entry`
where
against_voucher_type = %s and against_voucher = %s and party=%s
and docstatus = 1
""".format(
dr_or_cr=dr_or_cr
),
(self.doctype, self.name, party),
as_dict=1,
)
if advance:
advance = advance[0]
advance_paid = flt(advance.amount, self.precision("advance_paid"))
formatted_advance_paid = fmt_money(
advance_paid, precision=self.precision("advance_paid"), currency=advance.account_currency
)
frappe.db.set_value(self.doctype, self.name, "party_account_currency", advance.account_currency)
if advance.account_currency == self.currency:
order_total = self.grand_total
formatted_order_total = fmt_money(
order_total, precision=self.precision("grand_total"), currency=advance.account_currency
)
else:
order_total = self.base_grand_total
formatted_order_total = fmt_money(
order_total, precision=self.precision("base_grand_total"), currency=advance.account_currency
)
if self.currency == self.company_currency and advance_paid > order_total:
frappe.throw(
_("Total advance ({0}) against Order {1} cannot be greater than the Grand Total ({2})").format(
formatted_advance_paid, self.name, formatted_order_total
)
)
frappe.db.set_value(self.doctype, self.name, "advance_paid", advance_paid)
def decorate_quotation_doc(doc):
for d in doc.get_all_children():
if d.item_code:
d.update(frappe.db.get_value("Item", d.item_code,
["website_image", "description", "page_name"], as_dict=True))
d.formatted_rate = fmt_money(d.rate, currency=doc.currency)
d.formatted_amount = fmt_money(d.amount, currency=doc.currency)
elif d.charge_type:
d.formatted_tax_amount = fmt_money(flt(d.tax_amount) / doc.conversion_rate,
currency=doc.currency)
doc.formatted_grand_total_export = fmt_money(doc.grand_total_export,
currency=doc.currency)
return doc.as_dict()
def compare_expense_with_budget(args, cost_center, budget_amount, action_for, action):
actual_expense = get_actual_expense(args, cost_center)
if actual_expense > budget_amount:
diff = actual_expense - budget_amount
currency = frappe.db.get_value('Company', frappe.db.get_value('Cost Center',
cost_center, 'company'), 'default_currency')
msg = _("{0} Budget for Account {1} against Cost Center {2} is {3}. It will exceed by {4}").format(_(action_for),
frappe.bold(args.account), frappe.bold(cost_center),
frappe.bold(fmt_money(budget_amount, currency=currency)), frappe.bold(fmt_money(diff, currency=currency)))
if action=="Stop":
frappe.throw(msg, BudgetError)
else:
frappe.msgprint(msg, indicator='orange')
def decorate_quotation_doc(quotation_doc):
doc = frappe._dict(quotation_doc.as_dict())
for d in doc.get("items", []):
d.update(
frappe.db.get_value("Item", d["item_code"], ["website_image", "description", "page_name"], as_dict=True)
)
d["formatted_rate"] = fmt_money(d.get("rate"), currency=doc.currency)
d["formatted_amount"] = fmt_money(d.get("amount"), currency=doc.currency)
for d in doc.get("taxes", []):
d["formatted_tax_amount"] = fmt_money(flt(d.get("tax_amount_after_discount_amount")), currency=doc.currency)
doc.formatted_grand_total_export = fmt_money(doc.grand_total, currency=doc.currency)
return doc
def submit_salary_slips(self):
"""
Submit all salary slips based on selected criteria
"""
self.check_permission('write')
jv_name = ""
ss_list = self.get_sal_slip_list(ss_status=0)
submitted_ss = []
not_submitted_ss = []
for ss in ss_list:
ss_obj = frappe.get_doc("Salary Slip",ss[0])
ss_dict = {}
ss_dict["Employee Name"] = ss_obj.employee_name
ss_dict["Total Pay"] = fmt_money(ss_obj.net_pay,
currency = frappe.defaults.get_global_default("currency"))
ss_dict["Salary Slip"] = self.format_as_links(ss_obj.name)[0]
if ss_obj.net_pay<0:
not_submitted_ss.append(ss_dict)
else:
try:
ss_obj.submit()
submitted_ss.append(ss_dict)
except frappe.ValidationError:
not_submitted_ss.append(ss_dict)
if submitted_ss:
jv_name = self.make_accural_jv_entry()
return self.create_submit_log(submitted_ss, not_submitted_ss, jv_name)
def convert_to_simple_type(self, v, formatted=0): """Format date, time, longint values.""" return v from frappe.utils import formatdate, fmt_money if isinstance(v, (datetime.date, datetime.timedelta, datetime.datetime, long)): if isinstance(v, datetime.date): v = unicode(v) if formatted: v = formatdate(v) # time elif isinstance(v, (datetime.timedelta, datetime.datetime)): v = unicode(v) # long elif isinstance(v, long): v=int(v) # convert to strings... (if formatted) if formatted: if isinstance(v, float): v=fmt_money(v) elif isinstance(v, int): v = unicode(v) return v
def get_product_info(item_code):
"""get product price / stock info"""
if not is_cart_enabled():
return {}
qty = 0
cart_quotation = _get_cart_quotation()
template_item_code = frappe.db.get_value("Item", item_code, "variant_of")
stock_status = get_qty_in_stock(item_code, template_item_code)
in_stock = stock_status.in_stock
stock_qty = stock_status.stock_qty
price = get_price(item_code, template_item_code, cart_quotation.selling_price_list)
if price:
price["formatted_price"] = fmt_money(price["price_list_rate"], currency=price["currency"])
price["currency"] = not cint(frappe.db.get_default("hide_currency_symbol")) \
and (frappe.db.get_value("Currency", price.currency, "symbol") or price.currency) \
or ""
if frappe.session.user != "Guest":
item = cart_quotation.get({"item_code": item_code})
if item:
qty = item[0].qty
return {
"price": price,
"stock_qty": stock_qty,
"in_stock": in_stock,
"uom": frappe.db.get_value("Item", item_code, "stock_uom"),
"qty": qty,
"show_stock_qty": show_quantity_in_website()
}
def get_context(context):
context.no_cache = 1
# all these keys exist in form_dict
if not (set(expected_keys) - set(list(frappe.form_dict))):
for key in expected_keys:
context[key] = frappe.form_dict[key]
gateway_controller = get_gateway_controller(context.reference_doctype, context.reference_docname)
context.publishable_key = get_api_key(context.reference_docname, gateway_controller)
context.image = get_header_image(context.reference_docname, gateway_controller)
context['amount'] = fmt_money(amount=context['amount'], currency=context['currency'])
if frappe.db.get_value(context.reference_doctype, context.reference_docname, "is_a_subscription"):
payment_plan = frappe.db.get_value(context.reference_doctype, context.reference_docname, "payment_plan")
recurrence = frappe.db.get_value("Payment Plan", payment_plan, "recurrence")
context['amount'] = context['amount'] + " " + _(recurrence)
else:
frappe.redirect_to_message(_('Some information is missing'),
_('Looks like someone sent you to an incomplete URL. Please ask them to look into it.'))
frappe.local.flags.redirect_location = frappe.local.response.location
raise frappe.Redirect
def get_site_list(doctype, txt, filters, limit_start, limit_page_length=20, order_by=None):
memberships = frappe.db.sql("""select * from `tabMembership`
where member = %s order by creation desc""", frappe.session.user, as_dict=True)
for membership in memberships:
membership.amount = fmt_money(membership.amount, precision=2, currency=membership.currency)
return memberships
def get_formatted_value(value, add_symbol=True):
"""return formatted value"""
if not add_symbol:
return '{:.{pre}f}'.format(value, pre=(get_currency_precision() or 2))
currency_precision = get_currency_precision() or 2
company = frappe.db.get_default("company")
currency = frappe.get_doc("Company", company).default_currency or frappe.boot.sysdefaults.currency
return fmt_money(value, currency_precision, currency)
def get_booked_total(self, party_type, gle_field, label):
party_list = frappe.db.sql_list("select name from `tab{0}`".format(party_type))
total = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["party_type"]==party_type and gle["party"] in party_list:
total += gle[gle_field]
return total, self.get_html(label, self.currency, fmt_money(total))
def get_payment_entries(self):
if not (self.bank_account and self.from_date and self.to_date):
msgprint("Bank Account, From Date and To Date are Mandatory")
return
condition = ""
if not self.include_reconciled_entries:
condition = "and (clearance_date is null or clearance_date='0000-00-00')"
journal_entries = frappe.db.sql("""
select
"Journal Entry" as payment_document, t1.name as payment_entry,
t1.cheque_no as cheque_number, t1.cheque_date,
t2.debit_in_account_currency as debit, t2.credit_in_account_currency as credit,
t1.posting_date, t2.against_account, t1.clearance_date, t2.account_currency
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
t2.parent = t1.name and t2.account = %s and t1.docstatus=1
and t1.posting_date >= %s and t1.posting_date <= %s
and ifnull(t1.is_opening, 'No') = 'No' {0}
order by t1.posting_date ASC, t1.name DESC
""".format(condition), (self.bank_account, self.from_date, self.to_date), as_dict=1)
payment_entries = frappe.db.sql("""
select
"Payment Entry" as payment_document, name as payment_entry,
reference_no as cheque_number, reference_date as cheque_date,
if(paid_from=%(account)s, paid_amount, "") as credit,
if(paid_from=%(account)s, "", received_amount) as debit,
posting_date, ifnull(party,if(paid_from=%(account)s,paid_to,paid_from)) as against_account, clearance_date,
if(paid_to=%(account)s, paid_to_account_currency, paid_from_account_currency) as account_currency
from `tabPayment Entry`
where
(paid_from=%(account)s or paid_to=%(account)s) and docstatus=1
and posting_date >= %(from)s and posting_date <= %(to)s {0}
order by
posting_date ASC, name DESC
""".format(condition),
{"account":self.bank_account, "from":self.from_date, "to":self.to_date}, as_dict=1)
entries = sorted(list(payment_entries)+list(journal_entries),
key=lambda k: k['posting_date'] or getdate(nowdate()))
self.set('payment_entries', [])
self.total_amount = 0.0
for d in entries:
row = self.append('payment_entries', {})
d.amount = fmt_money(d.debit if d.debit else d.credit, 2, d.account_currency) + " " + (_("Dr") if d.debit else _("Cr"))
d.pop("credit")
d.pop("debit")
d.pop("account_currency")
row.update(d)
self.total_amount += flt(d.amount)
def validate_payment(self):
membership_fees = currency_wise_membership_plan_mapper\
.get(self.membership_type, {}).get(self.currency)
if cint(membership_fees) != cint(self.amount):
formated_membership_fees = fmt_money(membership_fees, precision=0, currency=self.currency)
frappe.msgprint("For Membership Type: {0}, the fees are {1}".format(self.membership_type,
formated_membership_fees), raise_exception=frappe.ValidationError,
title="Validate Payment Amount", indicator='red')
def modify_status(doc):
doc.status = ""
if flt(doc.outstanding_amount):
doc.status = '<span class="label %s"><i class="icon-fixed-width %s"></i> %s</span>' % \
("label-warning", "icon-exclamation-sign",
_("To Pay") + " = " + fmt_money(doc.outstanding_amount, currency=doc.currency))
else:
doc.status = '<span class="label %s"><i class="icon-fixed-width %s"></i> %s</span>' % \
("label-success", "icon-ok", _("Paid"))
def get_income(self, from_date=None, label=None):
accounts = [a["name"] for a in self.get_accounts() if a["root_type"]=="Income"]
income = 0
for gle in self.get_gl_entries(from_date or self.from_date, self.to_date):
if gle["account"] in accounts:
income += gle["credit"] - gle["debit"]
return income, self.get_html(label or self.meta.get_label("income"), self.currency,
fmt_money(income))
def get_expenses_booked(self):
accounts = [a["name"] for a in self.get_accounts() if a["root_type"]=="Expense"]
expense = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["account"] in accounts:
expense += gle["debit"] - gle["credit"]
return expense, self.get_html(self.meta.get_label("expenses_booked"), self.currency,
fmt_money(expense))
def get_new_sum(self, doctype, label, sum_field, date_field="creation"):
count_sum = frappe.db.sql("""select count(*), sum(ifnull(`{sum_field}`, 0))
from `tab{doctype}` where docstatus=1 and company = %s and
date(`{date_field}`)>=%s and date(`{date_field}`)<=%s""".format(sum_field=sum_field,
date_field=date_field, doctype=doctype), (self.company, self.from_date, self.to_date))
count, total = count_sum and count_sum[0] or (0, 0)
return count, self.get_html(label, self.currency,
"%s - (%s)" % (fmt_money(total), cstr(count)))
def get_booked_total(self, party_type, gle_field, label):
# account is of master_type Customer or Supplier
accounts = [a["name"] for a in self.get_accounts() if a["master_type"] == party_type]
total = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["account"] in accounts:
total += gle[gle_field]
return total, self.get_html(label, self.currency, fmt_money(total))
def compare_expense_with_budget(args, budget_amount, action_for, action, budget_against, amount=0):
actual_expense = amount or get_actual_expense(args)
if actual_expense > budget_amount:
diff = actual_expense - budget_amount
currency = frappe.get_cached_value('Company', args.company, 'default_currency')
msg = _("{0} Budget for Account {1} against {2} {3} is {4}. It will exceed by {5}").format(
_(action_for), frappe.bold(args.account), args.budget_against_field,
frappe.bold(budget_against),
frappe.bold(fmt_money(budget_amount, currency=currency)),
frappe.bold(fmt_money(diff, currency=currency)))
if (frappe.flags.exception_approver_role
and frappe.flags.exception_approver_role in frappe.get_roles(frappe.session.user)):
action = "Warn"
if action=="Stop":
frappe.throw(msg, BudgetError)
else:
frappe.msgprint(msg, indicator='orange')
def get_new_sum(self, doctype, label, sum_field):
count_sum = frappe.db.sql(
"""select count(*), sum(ifnull(`%s`, 0))
from `tab%s` where docstatus=1 and company = %s and
date(creation)>=%s and date(creation)<=%s"""
% (sum_field, doctype, "%s", "%s", "%s"),
(self.company, self.from_date, self.to_date),
)
count, total = count_sum and count_sum[0] or (0, 0)
return count, self.get_html(label, self.currency, "%s - (%s)" % (fmt_money(total), cstr(count)))
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>")
if df.get("fieldtype") == "Select":
value = _(value)
return value
def set_total_advance_paid(self):
if self.doctype == "Sales Order":
dr_or_cr = "credit_in_account_currency"
party = self.customer
else:
dr_or_cr = "debit_in_account_currency"
party = self.supplier
advance = frappe.db.sql("""
select
account_currency, sum({dr_or_cr}) as amount
from
`tabGL Entry`
where
against_voucher_type = %s and against_voucher = %s and party=%s
and docstatus = 1
""".format(dr_or_cr=dr_or_cr), (self.doctype, self.name, party), as_dict=1)
if advance:
advance = advance[0]
advance_paid = flt(advance.amount, self.precision("advance_paid"))
formatted_advance_paid = fmt_money(advance_paid, precision=self.precision("advance_paid"),
currency=advance.account_currency)
frappe.db.set_value(self.doctype, self.name, "party_account_currency",
advance.account_currency)
if advance.account_currency == self.currency:
order_total = self.grand_total
formatted_order_total = fmt_money(order_total, precision=self.precision("grand_total"),
currency=advance.account_currency)
else:
order_total = self.base_grand_total
formatted_order_total = fmt_money(order_total, precision=self.precision("base_grand_total"),
currency=advance.account_currency)
if self.currency == self.company_currency and advance_paid > order_total:
frappe.throw(_("Total advance ({0}) against Order {1} cannot be greater than the Grand Total ({2})")
.format(formatted_advance_paid, self.name, formatted_order_total))
frappe.db.set_value(self.doctype, self.name, "advance_paid", advance_paid)
def decorate_quotation_doc(quotation_doc):
doc = frappe._dict(quotation_doc.as_dict())
for d in doc.get("items", []):
d.update(
frappe.db.get_value("Item",
d["item_code"],
["website_image", "description", "page_name"],
as_dict=True))
d["formatted_rate"] = fmt_money(d.get("rate"), currency=doc.currency)
d["formatted_amount"] = fmt_money(d.get("amount"),
currency=doc.currency)
for d in doc.get("taxes", []):
d["formatted_tax_amount"] = fmt_money(flt(
d.get("tax_amount_after_discount_amount")),
currency=doc.currency)
doc.formatted_grand_total_export = fmt_money(doc.grand_total,
currency=doc.currency)
return doc
def compare_expense_with_budget(args, cost_center, budget_amount, action_for,
action):
actual_expense = get_actual_expense(args, cost_center)
if actual_expense > budget_amount:
diff = actual_expense - budget_amount
currency = frappe.db.get_value(
'Company',
frappe.db.get_value('Cost Center', cost_center, 'company'),
'default_currency')
msg = _(
"{0} Budget for Account {1} against Cost Center {2} is {3}. It will exceed by {4}"
).format(_(action_for), frappe.bold(args.account),
frappe.bold(cost_center),
frappe.bold(fmt_money(budget_amount, currency=currency)),
frappe.bold(fmt_money(diff, currency=currency)))
if action == "Stop":
frappe.throw(msg, BudgetError)
else:
frappe.msgprint(msg, indicator='orange')
def update_outstanding_amt(account,
party_type,
party,
against_voucher_type,
against_voucher,
on_cancel=False):
if party_type and party:
party_condition = " and party_type='{0}' and party='{1}'"\
.format(frappe.db.escape(party_type), frappe.db.escape(party))
else:
party_condition = ""
# get final outstanding amt
bal = flt(
frappe.db.sql(
"""
select sum(debit_in_account_currency) - sum(credit_in_account_currency)
from `tabGL Entry`
where against_voucher_type=%s and against_voucher=%s
and account = %s {0}""".format(party_condition),
(against_voucher_type, against_voucher, account))[0][0] or 0.0)
if against_voucher_type == 'Purchase Invoice':
bal = -bal
elif against_voucher_type == "Journal Entry":
against_voucher_amount = flt(
frappe.db.sql(
"""
select sum(debit_in_account_currency) - sum(credit_in_account_currency)
from `tabGL Entry` where voucher_type = 'Journal Entry' and voucher_no = %s
and account = %s and (against_voucher is null or against_voucher='') {0}""".
format(party_condition), (against_voucher, account))[0][0])
if not against_voucher_amount:
frappe.throw(
_("Against Journal Entry {0} is already adjusted against some other voucher"
).format(against_voucher))
bal = against_voucher_amount + bal
if against_voucher_amount < 0:
bal = -bal
# Validation : Outstanding can not be negative for JV
if bal < 0 and not on_cancel:
frappe.throw(
_("Outstanding for {0} cannot be less than zero ({1})").format(
against_voucher, fmt_money(bal)))
# Update outstanding amt on against voucher
if against_voucher_type in ["Sales Invoice", "Purchase Invoice", "Fees"]:
ref_doc = frappe.get_doc(against_voucher_type, against_voucher)
ref_doc.db_set('outstanding_amount', bal)
ref_doc.set_status(update=True)
def create_salary_slips(self):
"""
MODIFIED: ???
Creates salary slip for selected employees if already not created
"""
self.check_permission('write')
self.created = 1
emp_list = self.get_emp_list()
ss_list = []
if emp_list:
for emp in emp_list:
if not frappe.db.sql(
"""select
name from `tabSalary Slip`
where
docstatus!= 2 and
employee = %s and
start_date >= %s and
end_date <= %s and
company = %s
""", (emp['employee'], self.start_date, self.end_date, self.company)):
ss = frappe.get_doc({
"doctype":
"Salary Slip",
"salary_slip_based_on_timesheet":
self.salary_slip_based_on_timesheet,
"payroll_frequency":
self.payroll_frequency,
"start_date":
self.start_date,
"end_date":
self.end_date,
"employee":
emp['employee'],
"employee_name":
frappe.get_value("Employee", {"name": emp['employee']},
"employee_name"),
"company":
self.company,
"posting_date":
self.posting_date
})
ss.insert()
ss_dict = {}
ss_dict["Employee Name"] = ss.employee_name
ss_dict["Total Pay"] = fmt_money(
ss.rounded_total,
currency=frappe.defaults.get_global_default(
"currency"))
ss_dict["Salary Slip"] = format(ss.name)[0]
ss_list.append(ss_dict)
self.fill_salary_slips()
return ss_list
def get_new_sum(self, doctype, label, sum_field):
count_sum = frappe.db.sql(
"""select count(*), sum(ifnull(`%s`, 0))
from `tab%s` where docstatus=1 and company = %s and
date(creation)>=%s and date(creation)<=%s""" %
(sum_field, doctype, "%s", "%s", "%s"),
(self.doc.company, self.from_date, self.to_date))
count, total = count_sum and count_sum[0] or (0, 0)
return count, self.get_html(
label, self.currency,
"%s - (%s)" % (fmt_money(total), cstr(count)))
def update_outstanding_amt(account,
party_type,
party,
against_voucher_type,
against_voucher,
on_cancel=False):
if party_type and party:
party_condition = " and ifnull(party_type, '')='{0}' and ifnull(party, '')='{1}'"\
.format(frappe.db.escape(party_type), frappe.db.escape(party))
else:
party_condition = ""
# get final outstanding amt
bal = flt(
frappe.db.sql(
"""select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry`
where against_voucher_type=%s and against_voucher=%s
and account = %s {0}""".format(party_condition),
(against_voucher_type, against_voucher, account))[0][0] or 0.0)
if against_voucher_type == 'Purchase Invoice':
bal = -bal
elif against_voucher_type == "Journal Entry":
against_voucher_amount = flt(
frappe.db.sql(
"""
select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
from `tabGL Entry` where voucher_type = 'Journal Entry' and voucher_no = %s
and account = %s and ifnull(against_voucher, '') = '' {0}""".format(
party_condition), (against_voucher, account))[0][0])
if not against_voucher_amount:
frappe.throw(
_("Against Journal Entry {0} is already adjusted against some other voucher"
).format(against_voucher))
bal = against_voucher_amount + bal
if against_voucher_amount < 0:
bal = -bal
# Validation : Outstanding can not be negative for JV
if bal < 0 and not on_cancel:
frappe.throw(
_("Outstanding for {0} cannot be less than zero ({1})").format(
against_voucher, fmt_money(bal)))
# Update outstanding amt on against voucher
if against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
frappe.db.sql(
"update `tab%s` set outstanding_amount=%s where name=%s" %
(against_voucher_type, '%s', '%s'), (bal, against_voucher))
def get_expenses_booked(self):
accounts = [
a["name"] for a in self.get_accounts()
if a["root_type"] == "Expense"
]
expense = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["account"] in accounts:
expense += gle["debit"] - gle["credit"]
return expense, self.get_html(self.meta.get_label("expenses_booked"),
self.currency, fmt_money(expense))
def get_slot_price(item_code, qty, price_list=None):
from erpnext.shopping_cart.doctype.shopping_cart_settings.shopping_cart_settings import get_shopping_cart_settings
from erpnext.utilities.product import get_price
settings = get_shopping_cart_settings()
if not price_list:
price_list = settings.price_list
price = get_price(item_code, price_list, settings.default_customer_group,
settings.company, qty)
return fmt_money(float(price.price_list_rate) * float(qty),
currency=price.currency)
def get_booked_total(self, party_type, gle_field, label):
# account is of master_type Customer or Supplier
accounts = [
a["name"] for a in self.get_accounts()
if a["master_type"] == party_type
]
total = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["account"] in accounts:
total += gle[gle_field]
return total, self.get_html(label, self.currency, fmt_money(total))
def validate_overlapping_shipping_rule_conditions(self):
def overlap_exists_between(num_range1, num_range2):
"""
num_range1 and num_range2 are two ranges
ranges are represented as a tuple e.g. range 100 to 300 is represented as (100, 300)
if condition num_range1 = 100 to 300
then condition num_range2 can only be like 50 to 99 or 301 to 400
hence, non-overlapping condition = (x1 <= x2 < y1 <= y2) or (y1 <= y2 < x1 <= x2)
"""
(x1, x2), (y1, y2) = num_range1, num_range2
separate = (x1 <= x2 <= y1 <= y2) or (y1 <= y2 <= x1 <= x2)
return (not separate)
overlaps = []
for i in range(0, len(self.conditions)):
for j in range(i + 1, len(self.conditions)):
d1, d2 = self.conditions[i], self.conditions[j]
if d1.as_dict() != d2.as_dict():
# in our case, to_value can be zero, hence pass the from_value if so
range_a = (d1.from_value, d1.to_value or d1.from_value)
range_b = (d2.from_value, d2.to_value or d2.from_value)
if overlap_exists_between(range_a, range_b):
overlaps.append([d1, d2])
if overlaps:
company_currency = erpbee.get_company_currency(self.company)
msgprint(_("Overlapping conditions found between:"))
messages = []
for d1, d2 in overlaps:
messages.append(
"%s-%s = %s " %
(d1.from_value, d1.to_value,
fmt_money(d1.shipping_amount, currency=company_currency))
+ _("and") + " %s-%s = %s" %
(d2.from_value, d2.to_value,
fmt_money(d2.shipping_amount, currency=company_currency)))
msgprint("\n".join(messages),
raise_exception=OverlappingConditionError)
def get_new_sum(self, doctype, label, sum_field, date_field="creation"):
count_sum = frappe.db.sql(
"""select count(*), sum(ifnull(`{sum_field}`, 0))
from `tab{doctype}` where docstatus=1 and company = %s and
date(`{date_field}`)>=%s and date(`{date_field}`)<=%s""".format(
sum_field=sum_field, date_field=date_field, doctype=doctype),
(self.company, self.from_date, self.to_date))
count, total = count_sum and count_sum[0] or (0, 0)
return count, self.get_html(
label, self.currency,
"%s - (%s)" % (fmt_money(total), cstr(count)))
def validate_warehouse_account(self):
'''If perpetual inventory is set, and warehouse is linked,
the account balance and stock balance as of now must always match.
'''
from erpnext.accounts.utils import get_balance_on
from erpnext.stock.utils import get_stock_value_on
if not cint(
frappe.defaults.get_global_default(
"auto_accounting_for_stock")):
return
if self.account_type == "Stock":
if self.is_group == 0 and not self.warehouse:
frappe.throw(
_("Warehouse is mandatory for non group Accounts of type Stock"
))
if self.warehouse:
# company must be same
if frappe.get_value('Warehouse', self.warehouse,
'company') != self.company:
frappe.throw(
_("Warehouse company must be same as Account company"))
# balance must be same
stock_balance = get_stock_value_on(self.warehouse)
if self.is_new():
account_balance = 0.0
else:
account_balance = get_balance_on(self.name)
if account_balance != stock_balance:
frappe.throw(_('Account balance ({0}) and stock value ({1}) must be same')\
.format(fmt_money(account_balance, self.account_currency),
fmt_money(stock_balance, self.account_currency)))
elif self.warehouse:
self.warehouse = None
def create_remarks(self):
r = []
if self.cheque_no:
if self.cheque_date:
r.append(_('Reference #{0} dated {1}').format(self.cheque_no, formatdate(self.cheque_date)))
else:
msgprint(_("Please enter Reference date"), raise_exception=frappe.MandatoryError)
for d in self.get('entries'):
if d.against_invoice and d.credit:
currency = frappe.db.get_value("Sales Invoice", d.against_invoice, "currency")
r.append(_("{0} against Sales Invoice {1}").format(fmt_money(flt(d.credit), currency = currency), \
d.against_invoice))
if d.against_sales_order and d.credit:
currency = frappe.db.get_value("Sales Order", d.against_sales_order, "currency")
r.append(_("{0} against Sales Order {1}").format(fmt_money(flt(d.credit), currency = currency), \
d.against_sales_order))
if d.against_voucher and d.debit:
bill_no = frappe.db.sql("""select bill_no, bill_date, currency
from `tabPurchase Invoice` where name=%s""", d.against_voucher)
if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() \
not in ['na', 'not applicable', 'none']:
r.append(_('{0} {1} against Bill {2} dated {3}').format(bill_no[0][2],
fmt_money(flt(d.debit)), bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d'))))
if d.against_purchase_order and d.debit:
currency = frappe.db.get_value("Purchase Order", d.against_purchase_order, "currency")
r.append(_("{0} against Purchase Order {1}").format(fmt_money(flt(d.credit), currency = currency), \
d.against_purchase_order))
if self.user_remark:
r.append(_("Note: {0}").format(self.user_remark))
if r:
self.remark = ("\n").join(r) #User Remarks is not mandatory
def get_purchase_orders_items_overdue_list(self):
fields_po = "distinct `tabPurchase Order Item`.parent as po"
fields_poi = "`tabPurchase Order Item`.parent, `tabPurchase Order Item`.schedule_date, item_code," \
"received_qty, qty - received_qty as missing_qty, rate, amount"
sql_po = """select {fields} from `tabPurchase Order Item`
left join `tabPurchase Order` on `tabPurchase Order`.name = `tabPurchase Order Item`.parent
where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and curdate() > `tabPurchase Order Item`.schedule_date
and received_qty < qty order by `tabPurchase Order Item`.parent DESC,
`tabPurchase Order Item`.schedule_date DESC""".format(fields=fields_po)
sql_poi = """select {fields} from `tabPurchase Order Item`
left join `tabPurchase Order` on `tabPurchase Order`.name = `tabPurchase Order Item`.parent
where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and curdate() > `tabPurchase Order Item`.schedule_date
and received_qty < qty order by `tabPurchase Order Item`.idx""".format(fields=fields_poi)
purchase_order_list = frappe.db.sql(sql_po, as_dict=True)
purchase_order_items_overdue_list = frappe.db.sql(sql_poi, as_dict=True)
for t in purchase_order_items_overdue_list:
t.link = get_url_to_form("Purchase Order", t.parent)
t.rate = fmt_money(t.rate, 2, t.currency)
t.amount = fmt_money(t.amount, 2, t.currency)
return purchase_order_list, purchase_order_items_overdue_list
def get_income(self, from_date=None, label=None):
accounts = [
a["name"] for a in self.get_accounts()
if a["root_type"] == "Income"
]
income = 0
for gle in self.get_gl_entries(from_date or self.from_date,
self.to_date):
if gle["account"] in accounts:
income += gle["credit"] - gle["debit"]
return income, self.get_html(label or self.meta.get_label("income"),
self.currency, fmt_money(income))
def get_expenses_booked(self):
# account is PL Account and Debit type account
accounts = [
a["name"] for a in self.get_accounts()
if a["is_pl_account"] == "Yes" and a["debit_or_credit"] == "Debit"
]
expense = 0
for gle in self.get_gl_entries(self.from_date, self.to_date):
if gle["account"] in accounts:
expense += gle["debit"] - gle["credit"]
return expense, self.get_html(self.meta.get_label("expenses_booked"),
self.currency, fmt_money(expense))
def get_purchase_orders_items_overdue_list(self):
fields_po = "distinct `tabPurchase Order Item`.parent as po"
fields_poi = "`tabPurchase Order Item`.parent, `tabPurchase Order Item`.schedule_date, item_code," \
"received_qty, qty - received_qty as missing_qty, rate, amount"
sql_po = """select {fields} from `tabPurchase Order Item`
left join `tabPurchase Order` on `tabPurchase Order`.name = `tabPurchase Order Item`.parent
where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and curdate() > `tabPurchase Order Item`.schedule_date
and received_qty < qty order by `tabPurchase Order Item`.parent DESC,
`tabPurchase Order Item`.schedule_date DESC""".format(fields=fields_po)
sql_poi = """select {fields} from `tabPurchase Order Item`
left join `tabPurchase Order` on `tabPurchase Order`.name = `tabPurchase Order Item`.parent
where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and curdate() > `tabPurchase Order Item`.schedule_date
and received_qty < qty order by `tabPurchase Order Item`.idx""".format(fields=fields_poi)
purchase_order_list = frappe.db.sql(sql_po, as_dict=True)
purchase_order_items_overdue_list = frappe.db.sql(sql_poi, as_dict=True)
for t in purchase_order_items_overdue_list:
t.link = get_url_to_form("Purchase Order", t.parent)
t.rate = fmt_money(t.rate, 2, t.currency)
t.amount = fmt_money(t.amount, 2, t.currency)
return purchase_order_list, purchase_order_items_overdue_list
def validate_manual_distribution_totals(self):
tax_account_totals = {}
item_totals = {}
for tax in self.taxes:
if tax.distribution_criteria == "Manual" and tax.account_head:
if tax.account_head not in tax_account_totals:
tax_account_totals[tax.account_head] = 0.0
item_totals[tax.account_head] = 0.0
tax_account_totals[tax.account_head] += flt(tax.amount)
for item in self.items:
item_manual_distribution = item.manual_distribution or {}
if isinstance(item_manual_distribution, string_types):
item_manual_distribution = json.loads(item_manual_distribution)
for account_head in item_manual_distribution.keys():
if account_head in item_totals:
item_totals[account_head] += flt(
item_manual_distribution[account_head])
for account_head in tax_account_totals.keys():
digits = self.precision("total_taxes_and_charges")
diff = flt(tax_account_totals[account_head]) - flt(
item_totals[account_head])
diff = flt(diff, digits)
if abs(diff) > (2.0 / (10**digits)):
frappe.msgprint(
_("Tax amount for {} ({}) does not match the total in the manual distribution table ({})"
).format(
account_head,
fmt_money(tax_account_totals[account_head], digits,
self.currency),
fmt_money(item_totals[account_head], digits,
self.currency)))
def create_remarks(self):
r = []
if self.user_remark:
r.append(_("Note: {0}").format(self.user_remark))
if self.reference_account:
r.append(_('Reference Account: {0}').format(self.reference_account))
if self.cheque_no:
if self.cheque_date:
r.append(_('Reference #{0} dated {1}').format(self.cheque_no, formatdate(self.cheque_date)))
else:
r.append(_('Reference #{0}').format(self.cheque_no))
for d in self.get('accounts'):
if d.reference_type=="Sales Invoice" and d.credit:
r.append(_("{0} against Sales Invoice {1}").format(fmt_money(flt(d.credit), currency = self.company_currency), \
d.reference_name))
if d.reference_type=="Sales Order" and d.credit:
r.append(_("{0} against Sales Order {1}").format(fmt_money(flt(d.credit), currency = self.company_currency), \
d.reference_name))
if d.reference_type == "Purchase Invoice" and d.debit:
bill_no = frappe.db.sql("""select bill_no, bill_date
from `tabPurchase Invoice` where name=%s""", d.reference_name)
if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() \
not in ['na', 'not applicable', 'none']:
r.append(_('{0} against Bill {1} dated {2}').format(fmt_money(flt(d.debit), currency=self.company_currency), bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d'))))
if d.reference_type == "Purchase Order" and d.debit:
r.append(_("{0} against Purchase Order {1}").format(fmt_money(flt(d.debit), currency = self.company_currency), \
d.reference_name))
self.remark = "\n".join(r) if r else "" #User Remarks is not mandatory
def get_product_info(item_code):
"""get product price / stock info"""
if not cint(frappe.db.get_default("shopping_cart_enabled")):
return {}
cart_quotation = _get_cart_quotation()
price_list = cstr(
unquote(frappe.local.request.cookies.get("selling_price_list") or "")
or cart_quotation.selling_price_list)
warehouse = frappe.db.get_value("Item", item_code, "website_warehouse")
if warehouse:
in_stock = frappe.db.sql(
"""select actual_qty from tabBin where
item_code=%s and warehouse=%s""", (item_code, warehouse))
if in_stock:
in_stock = in_stock[0][0] > 0 and 1 or 0
else:
in_stock = -1
price = price_list and frappe.db.sql(
"""select price_list_rate, currency from
`tabItem Price` where item_code=%s and price_list=%s""",
(item_code, price_list),
as_dict=1) or []
price = price and price[0] or None
qty = 0
if price:
price["formatted_price"] = fmt_money(price["price_list_rate"],
currency=price["currency"])
price["currency"] = not cint(frappe.db.get_default("hide_currency_symbol")) \
and (frappe.db.get_value("Currency", price.currency, "symbol") or price.currency) \
or ""
if frappe.session.user != "Guest":
item = cart_quotation.get({"item_code": item_code})
if item:
qty = item[0].qty
return {
"price": price,
"stock": in_stock,
"uom": frappe.db.get_value("Item", item_code, "stock_uom"),
"qty": qty
}
def get_income(self, from_date=None, label=None):
# account is PL Account and Credit type account
accounts = [
a["name"] for a in self.get_accounts()
if a["is_pl_account"] == "Yes" and a["debit_or_credit"] == "Credit"
]
income = 0
for gle in self.get_gl_entries(from_date or self.from_date,
self.to_date):
if gle["account"] in accounts:
income += gle["credit"] - gle["debit"]
return income, self.get_html(label or self.meta.get_label("income"),
self.currency, fmt_money(income))
def create_remarks(self):
r = []
if self.doc.cheque_no:
if self.doc.cheque_date:
r.append(
'Via Reference #%s dated %s' %
(self.doc.cheque_no, formatdate(self.doc.cheque_date)))
else:
msgprint("Please enter Reference date", raise_exception=1)
for d in getlist(self.doclist, 'entries'):
if d.against_invoice and d.credit:
currency = frappe.db.get_value("Sales Invoice",
d.against_invoice, "currency")
r.append('%s %s against Invoice: %s' %
(cstr(currency), fmt_money(flt(
d.credit)), d.against_invoice))
if d.against_voucher and d.debit:
bill_no = frappe.db.sql(
"""select bill_no, bill_date, currency
from `tabPurchase Invoice` where name=%s""", d.against_voucher)
if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() \
not in ['na', 'not applicable', 'none']:
r.append('%s %s against Bill %s dated %s' %
(cstr(bill_no[0][2]), fmt_money(flt(d.debit)),
bill_no[0][0], bill_no[0][1] and formatdate(
bill_no[0][1].strftime('%Y-%m-%d')) or ''))
if self.doc.user_remark:
r.append("User Remark : %s" % self.doc.user_remark)
if r:
self.doc.remark = ("\n").join(r)
else:
frappe.msgprint("User Remarks is mandatory", raise_exception=1)
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 get_price(item_code, price_list, customer_group, company, qty=1):
template_item_code = frappe.db.get_value("Item", item_code, "variant_of")
if price_list:
price = frappe.get_all("Item Price", fields=["price_list_rate", "currency"],
filters={"price_list": price_list, "item_code": item_code})
if template_item_code and not price:
price = frappe.get_all("Item Price", fields=["price_list_rate", "currency"],
filters={"price_list": price_list, "item_code": template_item_code})
if price:
pricing_rule = get_pricing_rule_for_item(frappe._dict({
"item_code": item_code,
"qty": qty,
"transaction_type": "selling",
"price_list": price_list,
"customer_group": customer_group,
"company": company,
"conversion_rate": 1,
"for_shopping_cart": True
}))
if pricing_rule:
if pricing_rule.pricing_rule_for == "Discount Percentage":
price[0].price_list_rate = flt(price[0].price_list_rate * (1.0 - (flt(pricing_rule.discount_percentage) / 100.0)))
if pricing_rule.pricing_rule_for == "Price":
price[0].price_list_rate = pricing_rule.price_list_rate
price_obj = price[0]
if price_obj:
price_obj["formatted_price"] = fmt_money(price_obj["price_list_rate"], currency=price_obj["currency"])
price_obj["currency_symbol"] = not cint(frappe.db.get_default("hide_currency_symbol")) \
and (frappe.db.get_value("Currency", price_obj.currency, "symbol") or price_obj.currency) \
or ""
if not price_obj["price_list_rate"]:
price_obj["price_list_rate"] = 0
if not price_obj["currency"]:
price_obj["currency"] = ""
if not price_obj["formatted_price"]:
price_obj["formatted_price"] = ""
return price_obj
def test_currency_precision_de_format(self):
frappe.db.set_default("currency_precision", "4")
frappe.db.set_default("number_format", "#.###,##")
self.assertEqual(fmt_money(100), "100,00")
self.assertEqual(fmt_money(1000), "1.000,00")
self.assertEqual(fmt_money(10000), "10.000,00")
self.assertEqual(fmt_money(100000), "100.000,00")
self.assertEqual(fmt_money(100.23), "100,23")
self.assertEqual(fmt_money(1000.456), "1.000,456")
frappe.db.set_default("currency_precision", "")
def send_to_sms(BuyBackRequisition, method):
recipients = []
if BuyBackRequisition.phone_no:
phone_no = BuyBackRequisition.phone_no
recipients.append(cstr(phone_no))
message = """Dear %s , We received your device at '%s',
below are the details
Transaction ID: %s,
Device Received: %s,
Received Date: %s,
Offered Price: %s,
Your voucher will be sent to you in a separate email & sms correspondence.
Thank You.""" % (BuyBackRequisition.customer, BuyBackRequisition.warehouse,
BuyBackRequisition.name, BuyBackRequisition.item_name,
formatdate(BuyBackRequisition.creation),
fmt_money(flt(BuyBackRequisition.offered_price)))
send_sms(recipients, cstr(message))
def get_site_list(doctype,
txt,
filters,
limit_start,
limit_page_length=20,
order_by=None):
memberships = frappe.db.sql("""select * from `tabMembership`
where member = %s order by creation desc""",
frappe.session.user,
as_dict=True)
for membership in memberships:
membership.amount = fmt_money(membership.amount,
precision=2,
currency=membership.currency)
return memberships
def get_bank_balance(self):
# account is of type "Bank" or "Cash"
accounts = dict([[a["name"], [a["account_name"], 0]] for a in self.get_accounts()
if a["account_type"] in ["Bank", "Cash"]])
ackeys = accounts.keys()
for gle in self.get_gl_entries(None, self.to_date):
if gle["account"] in ackeys:
accounts[gle["account"]][1] += gle["debit"] - gle["credit"]
# build html
out = self.get_html(_("Bank/Cash Balance as on ") + formatdate(self.to_date), "", "")
for ac in ackeys:
if accounts[ac][1]:
out += "\n" + self.get_html(accounts[ac][0], self.currency,
fmt_money(accounts[ac][1]), style="margin-left: 17px")
return sum((accounts[ac][1] for ac in ackeys)), out
def get_context(context):
context.no_cache = 1
# all these keys exist in form_dict
if not (set(expected_keys) - set(list(frappe.form_dict))):
for key in expected_keys:
context[key] = frappe.form_dict[key]
if frappe.form_dict['payer_email']:
if frappe.form_dict['payer_email'] != frappe.session.user:
frappe.throw(_("Not permitted"), frappe.PermissionError)
else:
frappe.redirect_to_message(
_('Some information is missing'),
_('Looks like someone sent you to an incomplete URL. Please ask them to look into it.'
))
frappe.local.flags.redirect_location = frappe.local.response.location
raise frappe.Redirect
context.reference_docname = frappe.form_dict['order_id']
context.customercards = frappe.db.get_all(
"Moneris Vault",
fields={'*'},
filters={"user_id": frappe.session.user},
order_by="creation desc")
gateway_controller = get_gateway_controller(context.reference_doctype,
context.reference_docname)
context['amount'] = fmt_money(amount=context['amount'],
currency=context['currency'])
if frappe.db.get_value(context.reference_doctype,
context.reference_docname, "is_a_subscription"):
payment_plan = frappe.db.get_value(context.reference_doctype,
context.reference_docname,
"payment_plan")
recurrence = frappe.db.get_value("Payment Plan", payment_plan,
"recurrence")
context['amount'] = context['amount'] + " " + _(recurrence)
else:
frappe.redirect_to_message(
_('Some information is missing'),
_('Looks like someone sent you to an incomplete URL. Please ask them to look into it.'
))
frappe.local.flags.redirect_location = frappe.local.response.location
raise frappe.Redirect
