def get_conditions(self,
get_invoices=False,
get_payments=False,
get_return_invoices=False):
condition = " and company = '{0}' ".format(self.company)
if get_invoices:
condition += " and posting_date >= {0}".format(
frappe.db.escape(
self.from_invoice_date)) if self.from_invoice_date else ""
condition += " and posting_date <= {0}".format(
frappe.db.escape(
self.to_invoice_date)) if self.to_invoice_date else ""
dr_or_cr = ("debit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== 'Receivable' else "credit_in_account_currency")
if self.minimum_invoice_amount:
condition += " and `{0}` >= {1}".format(
dr_or_cr, flt(self.minimum_invoice_amount))
if self.maximum_invoice_amount:
condition += " and `{0}` <= {1}".format(
dr_or_cr, flt(self.maximum_invoice_amount))
elif get_return_invoices:
condition = " and doc.company = '{0}' ".format(self.company)
condition += " and doc.posting_date >= {0}".format(
frappe.db.escape(
self.from_payment_date)) if self.from_payment_date else ""
condition += " and doc.posting_date <= {0}".format(
frappe.db.escape(
self.to_payment_date)) if self.to_payment_date else ""
dr_or_cr = ("gl.debit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== 'Receivable' else "gl.credit_in_account_currency")
if self.minimum_invoice_amount:
condition += " and `{0}` >= {1}".format(
dr_or_cr, flt(self.minimum_payment_amount))
if self.maximum_invoice_amount:
condition += " and `{0}` <= {1}".format(
dr_or_cr, flt(self.maximum_payment_amount))
else:
condition += " and posting_date >= {0}".format(
frappe.db.escape(
self.from_payment_date)) if self.from_payment_date else ""
condition += " and posting_date <= {0}".format(
frappe.db.escape(
self.to_payment_date)) if self.to_payment_date else ""
if self.minimum_payment_amount:
condition += " and unallocated_amount >= {0}".format(flt(self.minimum_payment_amount)) if get_payments \
else " and total_debit >= {0}".format(flt(self.minimum_payment_amount))
if self.maximum_payment_amount:
condition += " and unallocated_amount <= {0}".format(flt(self.maximum_payment_amount)) if get_payments \
else " and total_debit <= {0}".format(flt(self.maximum_payment_amount))
return condition
def get_balance_on_voucher(voucher_type,
voucher_no,
party_type,
party,
account,
dr_or_cr=None):
if not dr_or_cr:
if erpnext.get_party_account_type(party_type) == 'Receivable':
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
res = frappe.db.sql(
"""
select ifnull(sum({dr_or_cr}), 0)
from `tabGL Entry`
where party_type=%(party_type)s and party=%(party)s and account=%(account)s
and ((voucher_type=%(voucher_type)s and voucher_no=%(voucher_no)s and (against_voucher is null or against_voucher=''))
or (against_voucher_type=%(voucher_type)s and against_voucher=%(voucher_no)s))
""".format(dr_or_cr=dr_or_cr), {
"voucher_type": voucher_type,
"voucher_no": voucher_no,
"party_type": party_type,
"party": party,
"account": account
})
return flt(res[0][0]) if res else 0.0
def get_unnallocated_parties(doctype, txt, searchfield, start, page_len,
filters):
company = filters["company"]
party_type = filters["party_type"]
bank_cash_account = filters["bank_cash_account"]
receivable_payable_account = filters["receivable_payable_account"]
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(party_type) == 'Receivable'
else "debit_in_account_currency")
bank_account_condition = ("t2.against_account like %(bank_cash_account)s"
if bank_cash_account else "1=1")
limit_cond = "limit 100"
paid_parties = frappe.db.sql(
"""
select
DISTINCT party
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
and t2.party_type = %(party_type)s
and t2.account = %(account)s and {dr_or_cr} > 0
and (t2.reference_type is null or t2.reference_type = '' or
(t2.reference_type in ('Sales Order', 'Purchase Order')
and t2.reference_name is not null and t2.reference_name != ''))
and (CASE
WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
THEN 1=1
ELSE {bank_account_condition}
END)
order by t1.posting_date {limit_cond}
""".format(
**{
"dr_or_cr": dr_or_cr,
"bank_account_condition": bank_account_condition,
"limit_cond": limit_cond
}), {
"party_type": party_type,
"account": receivable_payable_account,
"bank_cash_account": "%%%s%%" % bank_cash_account
},
as_dict=0)
unreconciled_parties = []
condition = ""
for party in paid_parties:
invoices = get_outstanding_invoices(party_type,
party,
receivable_payable_account,
condition=condition)
if invoices:
unreconciled_parties.append(party)
return unreconciled_parties
def reconcile(self, args):
for e in self.get('payments'):
e.invoice_type = None
if e.invoice_number and " | " in e.invoice_number:
e.invoice_type, e.invoice_number = e.invoice_number.split(" | ")
self.get_invoice_entries()
self.validate_invoice()
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency")
lst = []
for e in self.get('payments'):
if e.invoice_number and e.allocated_amount:
lst.append(frappe._dict({
'voucher_type': e.reference_type,
'voucher_no' : e.reference_name,
'voucher_detail_no' : e.reference_row,
'against_voucher_type' : e.invoice_type,
'against_voucher' : e.invoice_number,
'account' : self.receivable_payable_account,
'party_type': self.party_type,
'party': self.party,
'is_advance' : e.is_advance,
'dr_or_cr' : dr_or_cr,
'unadjusted_amount' : flt(e.amount),
'allocated_amount' : flt(e.allocated_amount)
}))
if lst:
from erpnext.accounts.utils import reconcile_against_document
reconcile_against_document(lst)
msgprint(_("Successfully Reconciled"))
self.get_unreconciled_entries()
def reconcile(self):
for e in self.get('payments'):
e.invoice_type = None
if e.invoice_number and " | " in e.invoice_number:
e.invoice_type, e.invoice_number = e.invoice_number.split(" | ")
self.get_invoice_entries()
self.validate_invoice()
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency")
lst = []
for e in self.get('payments'):
if e.invoice_number and e.allocated_amount:
lst.append(frappe._dict({
'voucher_type': e.reference_type,
'voucher_no' : e.reference_name,
'voucher_detail_no' : e.reference_row,
'against_voucher_type' : e.invoice_type,
'against_voucher' : e.invoice_number,
'account' : self.receivable_payable_account,
'party_type': self.party_type,
'party': self.party,
'dr_or_cr' : dr_or_cr,
'unadjusted_amount' : flt(e.amount),
'allocated_amount' : flt(e.allocated_amount)
}))
if lst:
from erpnext.accounts.utils import reconcile_against_document
reconcile_against_document(lst)
msgprint(_("Successfully Reconciled"))
self.get_unreconciled_entries()
def get_dr_or_cr_notes(self):
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== 'Receivable' else "debit_in_account_currency")
reconciled_dr_or_cr = ("debit_in_account_currency"
if dr_or_cr == "credit_in_account_currency" else
"credit_in_account_currency")
voucher_type = ('Sales Invoice' if self.party_type == 'Customer' else
"Purchase Invoice")
return frappe.db.sql(
""" SELECT `tab{doc}`.name as reference_name, %(voucher_type)s as reference_type,
(sum(`tabGL Entry`.{dr_or_cr}) - sum(`tabGL Entry`.{reconciled_dr_or_cr})) as amount
FROM `tab{doc}`, `tabGL Entry`
WHERE
(`tab{doc}`.name = `tabGL Entry`.against_voucher or `tab{doc}`.name = `tabGL Entry`.voucher_no)
and `tab{doc}`.is_return = 1 and `tabGL Entry`.against_voucher_type = %(voucher_type)s
and `tab{doc}`.docstatus = 1 and `tabGL Entry`.party = %(party)s
and `tabGL Entry`.party_type = %(party_type)s and `tabGL Entry`.account = %(account)s
GROUP BY `tabSales Invoice`.name
Having
amount > 0
""".format(doc=voucher_type,
dr_or_cr=dr_or_cr,
reconciled_dr_or_cr=reconciled_dr_or_cr), {
'party': self.party,
'party_type': self.party_type,
'voucher_type': voucher_type,
'account': self.receivable_payable_account
},
as_dict=1)
def reconcile(self, args):
for e in self.get('payments'):
e.invoice_type = None
if e.invoice_number and " | " in e.invoice_number:
e.invoice_type, e.invoice_number = e.invoice_number.split(" | ")
self.get_invoice_entries()
self.validate_invoice()
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency")
lst = []
dr_or_cr_notes = []
for e in self.get('payments'):
reconciled_entry = []
if e.invoice_number and e.allocated_amount:
if e.reference_type in ['Sales Invoice', 'Purchase Invoice']:
reconciled_entry = dr_or_cr_notes
else:
reconciled_entry = lst
reconciled_entry.append(self.get_payment_details(e, dr_or_cr))
if lst:
reconcile_against_document(lst)
if dr_or_cr_notes:
reconcile_dr_cr_note(dr_or_cr_notes, self.company)
msgprint(_("Successfully Reconciled"))
self.get_unreconciled_entries()
def get_balance_on_voucher(voucher_type,
voucher_no,
party_type,
party,
account,
dr_or_cr=None):
if not dr_or_cr:
if erpnext.get_party_account_type(party_type) == 'Receivable':
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
if isinstance(account, list):
account = ["'{0}'".format(frappe.db.escape(d)) for d in account]
account_condition = "account in ({0})".format(", ".join(account))
else:
account_condition = "account = '{0}'".format(frappe.db.escape(account))
res = frappe.db.sql(
"""
select ifnull(sum({dr_or_cr}), 0)
from `tabGL Entry`
where party_type=%(party_type)s and party=%(party)s and {account_condition}
and ((voucher_type=%(voucher_type)s and voucher_no=%(voucher_no)s and (against_voucher is null or against_voucher=''))
or (against_voucher_type=%(voucher_type)s and against_voucher=%(voucher_no)s))
""".format(dr_or_cr=dr_or_cr, account_condition=account_condition), {
"voucher_type": voucher_type,
"voucher_no": voucher_no,
"party_type": party_type,
"party": party
})
return flt(res[0][0]) if res else 0.0
def reconcile(self):
self.validate_allocation()
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== 'Receivable' else "debit_in_account_currency")
entry_list = []
dr_or_cr_notes = []
for row in self.get('allocation'):
reconciled_entry = []
if row.invoice_number and row.allocated_amount:
if row.reference_type in ['Sales Invoice', 'Purchase Invoice']:
reconciled_entry = dr_or_cr_notes
else:
reconciled_entry = entry_list
reconciled_entry.append(self.get_payment_details(
row, dr_or_cr))
if entry_list:
reconcile_against_document(entry_list)
if dr_or_cr_notes:
reconcile_dr_cr_note(dr_or_cr_notes, self.company)
msgprint(_("Successfully Reconciled"))
self.get_unreconciled_entries()
def validate_party_details(self):
if self.party:
if not frappe.db.exists(self.party_type, self.party):
frappe.throw(_("Invalid {0}: {1}").format(self.party_type, self.party))
if self.party_account and self.party_type in ("Customer", "Supplier"):
self.validate_account_type(self.party_account,
[erpnext.get_party_account_type(self.party_type)])
def validate_party_details(self):
if self.party:
if not frappe.db.exists(self.party_type, self.party):
frappe.throw(_("Invalid {0}: {1}").format(self.party_type, self.party))
if self.party_account and self.party_type in ("Customer", "Supplier"):
self.validate_account_type(self.party_account,
[erpnext.get_party_account_type(self.party_type)])
def check_if_advance_entry_modified(args):
"""
check if there is already a voucher reference
check if amount is same
check if jv is submitted
"""
if not args.get("unreconciled_amount"):
args.update({"unreconciled_amount": args.get("unadjusted_amount")})
ret = None
if args.voucher_type == "Journal Entry":
ret = frappe.db.sql(
"""
select t2.{dr_or_cr} from `tabJournal Entry` t1, `tabJournal Entry Account` t2
where t1.name = t2.parent and t2.account = %(account)s
and t2.party_type = %(party_type)s and t2.party = %(party)s
and (t2.reference_type is null or t2.reference_type in ("", "Sales Order", "Purchase Order"))
and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
and t1.docstatus=1 """.format(
dr_or_cr=args.get("dr_or_cr")
),
args,
)
else:
party_account_field = (
"paid_from" if erpnext.get_party_account_type(args.party_type) == "Receivable" else "paid_to"
)
if args.voucher_detail_no:
ret = frappe.db.sql(
"""select t1.name
from `tabPayment Entry` t1, `tabPayment Entry Reference` t2
where
t1.name = t2.parent and t1.docstatus = 1
and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
and t1.party_type = %(party_type)s and t1.party = %(party)s and t1.{0} = %(account)s
and t2.reference_doctype in ("", "Sales Order", "Purchase Order")
and t2.allocated_amount = %(unreconciled_amount)s
""".format(
party_account_field
),
args,
)
else:
ret = frappe.db.sql(
"""select name from `tabPayment Entry`
where
name = %(voucher_no)s and docstatus = 1
and party_type = %(party_type)s and party = %(party)s and {0} = %(account)s
and unallocated_amount = %(unreconciled_amount)s
""".format(
party_account_field
),
args,
)
if not ret:
throw(_("""Payment Entry has been modified after you pulled it. Please pull it again."""))
def get_advance_payment_entries(party_type, party, party_account, order_doctype,
order_list=None, include_unallocated=True, against_all_orders=False, against_account=None, limit=1000):
payment_entries_against_order, unallocated_payment_entries = [], []
party_account_type = erpnext.get_party_account_type(party_type)
party_account_field = "paid_from" if party_account_type == "Receivable" else "paid_to"
against_account_field = "paid_to" if party_account_type == "Receivable" else "paid_from"
payment_type = "Receive" if party_account_type == "Receivable" else "Pay"
against_account_condition = ""
if against_account:
against_account_condition = "and pe.{against_account_field} = {against_account}".format(
against_account_field=against_account_field, against_account=frappe.db.escape(against_account))
if order_list or against_all_orders:
if order_list:
reference_condition = " and pref.reference_name in ({0})" \
.format(', '.join(['%s'] * len(order_list)))
else:
reference_condition = ""
order_list = []
payment_entries_against_order = frappe.db.sql("""
select
"Payment Entry" as reference_type, pe.name as reference_name,
pe.remarks, pref.allocated_amount as amount, pref.name as reference_row,
pref.reference_name as against_order, pe.posting_date
from `tabPayment Entry` pe, `tabPayment Entry Reference` pref
where
pe.name = pref.parent and pe.{party_account_field} = %s and pe.payment_type = %s
and pe.party_type = %s and pe.party = %s and pe.docstatus = 1
and pref.reference_doctype = %s
{reference_condition} {against_account_condition}
order by pe.posting_date
limit %s
""".format(
party_account_field=party_account_field,
reference_condition=reference_condition,
against_account_condition=against_account_condition
), [party_account, payment_type, party_type, party, order_doctype] + order_list + [limit or 1000], as_dict=1)
if include_unallocated:
unallocated_payment_entries = frappe.db.sql("""
select "Payment Entry" as reference_type, name as reference_name, remarks, unallocated_amount as amount
from `tabPayment Entry` pe
where
{party_account_field} = %s and party_type = %s and party = %s and payment_type = %s
and docstatus = 1 and unallocated_amount > 0
{against_account_condition}
order by posting_date
limit %s
""".format(
party_account_field=party_account_field,
against_account_condition=against_account_condition
), [party_account, party_type, party, payment_type, limit or 1000], as_dict=1)
return list(payment_entries_against_order) + list(unallocated_payment_entries)
def get_outstanding_reference_documents(args):
if isinstance(args, string_types):
args = json.loads(args)
# confirm that Supplier is not blocked
if args.get('party_type') == 'Supplier':
supplier_status = get_supplier_block_status(args['party'])
if supplier_status['on_hold']:
if supplier_status['hold_type'] == 'All':
return []
elif supplier_status['hold_type'] == 'Payments':
if not supplier_status['release_date'] or getdate(nowdate()) <= supplier_status['release_date']:
return []
party_account_currency = get_account_currency(args.get("party_account"))
company_currency = frappe.get_cached_value('Company', args.get("company"), "default_currency")
# Get outstanding invoices
condition = ""
if args.get("voucher_type") and args.get("voucher_no"):
condition = " and voucher_type='{0}' and voucher_no='{1}'"\
.format(frappe.db.escape(args["voucher_type"]), frappe.db.escape(args["voucher_no"]))
# Add cost center condition
if args.get("cost_center") and get_allow_cost_center_in_entry_of_bs_account():
condition += " and cost_center='%s'" % args.get("cost_center")
negative_invoices = False
party_account_type = erpnext.get_party_account_type(args.get("party_type"))
if (args.get("payment_type") == "Receive" and party_account_type == "Payable") \
or (args.get("payment_type") == "Pay" and party_account_type == "Receivable"):
negative_invoices = True
outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"),
args.get("party_account"), condition=condition, negative_invoices=negative_invoices)
for d in outstanding_invoices:
d["exchange_rate"] = 1
if party_account_currency != company_currency:
if d.voucher_type in ("Sales Invoice", "Purchase Invoice", "Expense Claim"):
d["exchange_rate"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "conversion_rate")
elif d.voucher_type == "Journal Entry":
d["exchange_rate"] = get_average_party_exchange_rate_on_journal_entry(d.voucher_no,
args.get("party_type"), args.get("party"), args.get("party_account"))
if d.voucher_type in ("Purchase Invoice"):
d["bill_no"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "bill_no")
# Get all SO / PO which are not fully billed or aginst which full advance not paid
orders_to_be_billed = []
if not negative_invoices:
orders_to_be_billed = get_orders_to_be_billed(args.get("posting_date"),args.get("party_type"),
args.get("party"), party_account_currency, company_currency)
return outstanding_invoices + orders_to_be_billed
def check_condition(self):
cond = " and posting_date >= '{0}'".format(frappe.db.escape(self.from_date)) if self.from_date else ""
cond += " and posting_date <= '{0}'".format(frappe.db.escape(self.to_date)) if self.to_date else ""
dr_or_cr = ("debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable'
else "credit_in_account_currency")
if self.minimum_amount:
cond += " and `{0}` >= {1}".format(dr_or_cr, flt(self.minimum_amount))
if self.maximum_amount:
cond += " and `{0}` <= {1}".format(dr_or_cr, flt(self.maximum_amount))
return cond
def add_party_gl_entries(self, gl_entries):
if self.party_account:
if self.payment_type == "Receive":
against_account = self.paid_to
else:
against_account = self.paid_from
party_gl_dict = self.get_gl_dict({
"account":
self.party_account,
"party_type":
self.party_type,
"party":
self.party,
"against":
against_account,
"account_currency":
self.party_account_currency
})
dr_or_cr = "credit" if erpnext.get_party_account_type(
self.party_type) == 'Receivable' else "debit"
for d in self.get("references"):
gle = party_gl_dict.copy()
gle.update({
"against_voucher_type": d.reference_doctype,
"against_voucher": d.reference_name
})
allocated_amount_in_company_currency = flt(
flt(d.allocated_amount) * flt(d.exchange_rate),
self.precision("paid_amount"))
gle.update({
dr_or_cr + "_in_account_currency": d.allocated_amount,
dr_or_cr: allocated_amount_in_company_currency
})
gl_entries.append(gle)
if self.unallocated_amount:
base_unallocated_amount = base_unallocated_amount = self.unallocated_amount * \
(self.source_exchange_rate if self.payment_type=="Receive" else self.target_exchange_rate)
gle = party_gl_dict.copy()
gle.update({
dr_or_cr + "_in_account_currency": self.unallocated_amount,
dr_or_cr: base_unallocated_amount
})
gl_entries.append(gle)
def check_condition(self):
cond = " and posting_date >= {0}".format(frappe.db.escape(self.from_date)) if self.from_date else ""
cond += " and posting_date <= {0}".format(frappe.db.escape(self.to_date)) if self.to_date else ""
dr_or_cr = ("debit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable'
else "credit_in_account_currency")
if self.minimum_amount:
cond += " and `{0}` >= {1}".format(dr_or_cr, flt(self.minimum_amount))
if self.maximum_amount:
cond += " and `{0}` <= {1}".format(dr_or_cr, flt(self.maximum_amount))
return cond
def execute():
doctypes = [('Sales Invoice', 'Customer'),
('Purchase Invoice', 'Supplier'), ('Fees', 'Student'),
('Expense Claim', 'Employee')]
for dt, party_type in doctypes:
dr_or_cr = "debit - credit" if erpnext.get_party_account_type(
party_type) == 'Receivable' else "credit - debit"
frappe.db.sql("""
update `tabGL Entry`
set against_voucher = '', against_voucher_type = ''
where voucher_type = '{dt}' and against_voucher_type = voucher_type and against_voucher = voucher_no
and {dr_or_cr} > 0
""".format(dt=dt, dr_or_cr=dr_or_cr))
def get_jv_entries(self):
condition = self.get_conditions()
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== "Receivable" else "debit_in_account_currency")
bank_account_condition = (
"t2.against_account like %(bank_cash_account)s"
if self.bank_cash_account else "1=1")
journal_entries = frappe.db.sql(
"""
select
"Journal Entry" as reference_type, t1.name as reference_name,
t1.posting_date, t1.remark as remarks, t2.name as reference_row,
{dr_or_cr} as amount, t2.is_advance,
t2.account_currency as currency
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
and t2.party_type = %(party_type)s and t2.party = %(party)s
and t2.account = %(account)s and {dr_or_cr} > 0 {condition}
and (t2.reference_type is null or t2.reference_type = '' or
(t2.reference_type in ('Sales Order', 'Purchase Order')
and t2.reference_name is not null and t2.reference_name != ''))
and (CASE
WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
THEN 1=1
ELSE {bank_account_condition}
END)
order by t1.posting_date
""".format(
**{
"dr_or_cr": dr_or_cr,
"bank_account_condition": bank_account_condition,
"condition": condition,
}),
{
"party_type": self.party_type,
"party": self.party,
"account": self.receivable_payable_account,
"bank_cash_account": "%%%s%%" % self.bank_cash_account,
},
as_dict=1,
)
return list(journal_entries)
def add_party_gl_entries(self, gl_entries):
if self.party_account:
if self.payment_type=="Receive":
against_account = self.paid_to
else:
against_account = self.paid_from
party_gl_dict = self.get_gl_dict({
"account": self.party_account,
"party_type": self.party_type,
"party": self.party,
"against": against_account,
"account_currency": self.party_account_currency,
"cost_center": self.cost_center
})
dr_or_cr = "credit" if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit"
for d in self.get("references"):
gle = party_gl_dict.copy()
gle.update({
"against_voucher_type": d.reference_doctype,
"against_voucher": d.reference_name
})
allocated_amount_in_company_currency = flt(flt(d.allocated_amount) * flt(d.exchange_rate),
self.precision("paid_amount"))
gle.update({
dr_or_cr + "_in_account_currency": d.allocated_amount,
dr_or_cr: allocated_amount_in_company_currency
})
gl_entries.append(gle)
if self.unallocated_amount:
base_unallocated_amount = base_unallocated_amount = self.unallocated_amount * \
(self.source_exchange_rate if self.payment_type=="Receive" else self.target_exchange_rate)
gle = party_gl_dict.copy()
gle.update({
dr_or_cr + "_in_account_currency": self.unallocated_amount,
dr_or_cr: base_unallocated_amount
})
gl_entries.append(gle)
def get_difference_amount(self, child_row):
if child_row.get("reference_type") != 'Payment Entry': return
child_row = frappe._dict(child_row)
if child_row.invoice_number and " | " in child_row.invoice_number:
child_row.invoice_type, child_row.invoice_number = child_row.invoice_number.split(" | ")
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type) == 'Receivable' else "debit_in_account_currency")
row = self.get_payment_details(child_row, dr_or_cr)
doc = frappe.get_doc(row.voucher_type, row.voucher_no)
update_reference_in_payment_entry(row, doc, do_not_save=True)
return doc.difference_amount
def get_dr_or_cr_notes(self):
condition = self.get_conditions(get_return_invoices=True)
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== "Receivable" else "debit_in_account_currency")
reconciled_dr_or_cr = ("debit_in_account_currency"
if dr_or_cr == "credit_in_account_currency" else
"credit_in_account_currency")
voucher_type = "Sales Invoice" if self.party_type == "Customer" else "Purchase Invoice"
return frappe.db.sql(
""" SELECT doc.name as reference_name, %(voucher_type)s as reference_type,
(sum(gl.{dr_or_cr}) - sum(gl.{reconciled_dr_or_cr})) as amount, doc.posting_date,
account_currency as currency
FROM `tab{doc}` doc, `tabGL Entry` gl
WHERE
(doc.name = gl.against_voucher or doc.name = gl.voucher_no)
and doc.{party_type_field} = %(party)s
and doc.is_return = 1 and ifnull(doc.return_against, "") = ""
and gl.against_voucher_type = %(voucher_type)s
and doc.docstatus = 1 and gl.party = %(party)s
and gl.party_type = %(party_type)s and gl.account = %(account)s
and gl.is_cancelled = 0 {condition}
GROUP BY doc.name
Having
amount > 0
ORDER BY doc.posting_date
""".format(
doc=voucher_type,
dr_or_cr=dr_or_cr,
reconciled_dr_or_cr=reconciled_dr_or_cr,
party_type_field=frappe.scrub(self.party_type),
condition=condition or "",
),
{
"party": self.party,
"party_type": self.party_type,
"voucher_type": voucher_type,
"account": self.receivable_payable_account,
},
as_dict=1,
)
def get_jv_entries(self):
dr_or_cr = ("credit_in_account_currency" if erpnext.get_party_account_type(self.party_type) == 'Receivable'
else "debit_in_account_currency")
bank_account_condition = "t2.against_account like %(bank_cash_account)s" \
if self.bank_cash_account else "1=1"
limit_cond = "limit %s" % (self.limit or 1000)
journal_entries = frappe.db.sql("""
select
"Journal Entry" as reference_type, t1.name as reference_name,
t1.posting_date, t1.remark as remarks, t2.name as reference_row,
{dr_or_cr} as amount, t2.is_advance
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
and t2.party_type = %(party_type)s and t2.party = %(party)s
and t2.account = %(account)s and {dr_or_cr} > 0
and (t2.reference_type is null or t2.reference_type = '' or
(t2.reference_type in ('Sales Order', 'Purchase Order')
and t2.reference_name is not null and t2.reference_name != ''))
and (CASE
WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
THEN 1=1
ELSE {bank_account_condition}
END)
order by t1.posting_date {limit_cond}
""".format(**{
"dr_or_cr": dr_or_cr,
"bank_account_condition": bank_account_condition,
"limit_cond": limit_cond
}), {
"party_type": self.party_type,
"party": self.party,
"account": self.receivable_payable_account,
"bank_cash_account": "%%%s%%" % self.bank_cash_account
}, as_dict=1)
return list(journal_entries)
def get_dr_or_cr_notes(self):
dr_or_cr = ("credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== 'Receivable' else "debit_in_account_currency")
reconciled_dr_or_cr = ("debit_in_account_currency"
if dr_or_cr == "credit_in_account_currency" else
"credit_in_account_currency")
voucher_type = ('Sales Invoice' if self.party_type == 'Customer' else
"Purchase Invoice")
return frappe.db.sql(
""" SELECT doc.name as reference_name, %(voucher_type)s as reference_type,
(sum(gl.{dr_or_cr}) - sum(gl.{reconciled_dr_or_cr})) as amount,
account_currency as currency
FROM `tab{doc}` doc, `tabGL Entry` gl
WHERE
(doc.name = gl.against_voucher or doc.name = gl.voucher_no)
and doc.{party_type_field} = %(party)s
and doc.is_return = 1 and ifnull(doc.return_against, "") = ""
and gl.against_voucher_type = %(voucher_type)s
and doc.docstatus = 1 and gl.party = %(party)s
and gl.party_type = %(party_type)s and gl.account = %(account)s
and gl.is_cancelled = 0
GROUP BY doc.name
Having
amount > 0
""".format(doc=voucher_type,
dr_or_cr=dr_or_cr,
reconciled_dr_or_cr=reconciled_dr_or_cr,
party_type_field=frappe.scrub(self.party_type)), {
'party': self.party,
'party_type': self.party_type,
'voucher_type': voucher_type,
'account': self.receivable_payable_account
},
as_dict=1,
debug=1)
def check_if_advance_entry_modified(args):
"""
check if there is already a voucher reference
check if amount is same
check if jv is submitted
"""
ret = None
if args.voucher_type == "Journal Entry":
ret = frappe.db.sql("""
select t2.{dr_or_cr} from `tabJournal Entry` t1, `tabJournal Entry Account` t2
where t1.name = t2.parent and t2.account = %(account)s
and t2.party_type = %(party_type)s and t2.party = %(party)s
and (t2.reference_type is null or t2.reference_type in ("", "Sales Order", "Purchase Order"))
and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
and t1.docstatus=1 """.format(dr_or_cr = args.get("dr_or_cr")), args)
else:
party_account_field = ("paid_from"
if erpnext.get_party_account_type(args.party_type) == 'Receivable' else "paid_to")
if args.voucher_detail_no:
ret = frappe.db.sql("""select t1.name
from `tabPayment Entry` t1, `tabPayment Entry Reference` t2
where
t1.name = t2.parent and t1.docstatus = 1
and t1.name = %(voucher_no)s and t2.name = %(voucher_detail_no)s
and t1.party_type = %(party_type)s and t1.party = %(party)s and t1.{0} = %(account)s
and t2.reference_doctype in ("", "Sales Order", "Purchase Order")
and t2.allocated_amount = %(unadjusted_amount)s
""".format(party_account_field), args)
else:
ret = frappe.db.sql("""select name from `tabPayment Entry`
where
name = %(voucher_no)s and docstatus = 1
and party_type = %(party_type)s and party = %(party)s and {0} = %(account)s
and unallocated_amount = %(unadjusted_amount)s
""".format(party_account_field), args)
if not ret:
throw(_("""Payment Entry has been modified after you pulled it. Please pull it again."""))
def get_outstanding_journal_entries(party_account, party_type, party):
if erpnext.get_party_account_type(party_type) == "Receivable":
bal_dr_or_cr = "gle_je.credit_in_account_currency - gle_je.debit_in_account_currency"
payment_dr_or_cr = "gle_payment.debit_in_account_currency - gle_payment.credit_in_account_currency"
else:
bal_dr_or_cr = "gle_je.debit_in_account_currency - gle_je.credit_in_account_currency"
payment_dr_or_cr = "gle_payment.credit_in_account_currency - gle_payment.debit_in_account_currency"
return frappe.db.sql("""
select je.name, je.posting_date, je.user_remark,
ifnull(sum({bal_dr_or_cr}), 0) - (
select ifnull(sum({payment_dr_or_cr}), 0)
from `tabGL Entry` gle_payment
where
gle_payment.against_voucher_type = gle_je.voucher_type
and gle_payment.against_voucher = gle_je.voucher_no
and gle_payment.party_type = gle_je.party_type
and gle_payment.party = gle_je.party
and gle_payment.account = gle_je.account
and abs({payment_dr_or_cr}) > 0
) as balance
from `tabGL Entry` gle_je
inner join `tabJournal Entry` je on je.name = gle_je.voucher_no
where
gle_je.party_type = %(party_type)s and gle_je.party = %(party)s and gle_je.account = %(account)s
and gle_je.voucher_type = 'Journal Entry' and (gle_je.against_voucher = '' or gle_je.against_voucher is null)
and abs({bal_dr_or_cr}) > 0
group by gle_je.voucher_no
having abs(balance) > 0.005
order by gle_je.posting_date""".format(
bal_dr_or_cr=bal_dr_or_cr,
payment_dr_or_cr=payment_dr_or_cr
), {
"party_type": party_type,
"party": party,
"account": party_account
}, as_dict=1)
def get_outstanding_invoices(party_type,
party,
account,
condition=None,
limit=1000):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice",
"outstanding_amount") or 2
if erpnext.get_party_account_type(party_type) == 'Receivable':
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
invoice = 'Sales Invoice' if erpnext.get_party_account_type(
party_type) == 'Receivable' else 'Purchase Invoice'
held_invoices = get_held_invoices(party_type, party)
limit_cond = "limit %s" % (limit or 1000)
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount
from
`tabGL Entry`
where
party_type = %(party_type)s and party = %(party)s
and account = %(account)s and {dr_or_cr} > 0
{condition}
and ((voucher_type = 'Journal Entry'
and (against_voucher = '' or against_voucher is null))
or (voucher_type not in ('Journal Entry', 'Payment Entry')))
group by voucher_type, voucher_no
order by posting_date, name {limit_cond}""".format(dr_or_cr=dr_or_cr,
invoice=invoice,
condition=condition or "",
limit_cond=limit_cond),
{
"party_type": party_type,
"party": party,
"account": account,
},
as_dict=True)
payment_entries = frappe.db.sql("""
select against_voucher_type, against_voucher,
ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
from `tabGL Entry`
where party_type = %(party_type)s and party = %(party)s
and account = %(account)s
and {payment_dr_or_cr} > 0
and against_voucher is not null and against_voucher != ''
group by against_voucher_type, against_voucher
""".format(payment_dr_or_cr=payment_dr_or_cr), {
"party_type": party_type,
"party": party,
"account": account,
},
as_dict=True)
pe_map = frappe._dict()
for d in payment_entries:
pe_map.setdefault((d.against_voucher_type, d.against_voucher),
d.payment_amount)
for d in invoice_list:
payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
if outstanding_amount > 0.5 / (10**precision):
if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
due_date = frappe.db.get_value(
d.voucher_type, d.voucher_no,
"posting_date" if party_type == "Employee" else "due_date")
outstanding_invoices.append(
frappe._dict({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
'invoice_amount': flt(d.invoice_amount),
'payment_amount': payment_amount,
'outstanding_amount': outstanding_amount,
'due_date': due_date
}))
outstanding_invoices = sorted(
outstanding_invoices,
key=lambda k: k['due_date'] or getdate(nowdate()))
return outstanding_invoices
def get_outstanding_invoices(party_type, party, account, condition=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount")
if erpnext.get_party_account_type(party_type) == 'Receivable':
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"
invoice = 'Sales Invoice' if erpnext.get_party_account_type(
party_type) == 'Receivable' else 'Purchase Invoice'
held_invoices = get_held_invoices(party_type, party)
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount,
(
select ifnull(sum({payment_dr_or_cr}), 0)
from `tabGL Entry` payment_gl_entry
where payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
and if(invoice_gl_entry.voucher_type='Journal Entry',
payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no,
payment_gl_entry.against_voucher = invoice_gl_entry.against_voucher)
and payment_gl_entry.party_type = invoice_gl_entry.party_type
and payment_gl_entry.party = invoice_gl_entry.party
and payment_gl_entry.account = invoice_gl_entry.account
and {payment_dr_or_cr} > 0
) as payment_amount
from
`tabGL Entry` invoice_gl_entry
where
party_type = %(party_type)s and party = %(party)s
and account = %(account)s and {dr_or_cr} > 0
{condition}
and ((voucher_type = 'Journal Entry'
and (against_voucher = '' or against_voucher is null))
or (voucher_type not in ('Journal Entry', 'Payment Entry')))
group by voucher_type, voucher_no
having (invoice_amount - payment_amount) > 0.005
order by posting_date, name""".format(dr_or_cr=dr_or_cr,
invoice=invoice,
payment_dr_or_cr=payment_dr_or_cr,
condition=condition or ""), {
"party_type": party_type,
"party": party,
"account": account,
},
as_dict=True)
for d in invoice_list:
if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
due_date = frappe.db.get_value(
d.voucher_type, d.voucher_no,
"posting_date" if party_type == "Employee" else "due_date")
outstanding_invoices.append(
frappe._dict({
'voucher_no':
d.voucher_no,
'voucher_type':
d.voucher_type,
'posting_date':
d.posting_date,
'invoice_amount':
flt(d.invoice_amount),
'payment_amount':
flt(d.payment_amount),
'outstanding_amount':
flt(d.invoice_amount - d.payment_amount, precision),
'due_date':
due_date
}))
outstanding_invoices = sorted(
outstanding_invoices,
key=lambda k: k['due_date'] or getdate(nowdate()))
return outstanding_invoices
def get_outstanding_invoices(party_type, party, account, condition=None, filters=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2
if account:
root_type, account_type = frappe.get_cached_value(
"Account", account, ["root_type", "account_type"]
)
party_account_type = "Receivable" if root_type == "Asset" else "Payable"
party_account_type = account_type or party_account_type
else:
party_account_type = erpnext.get_party_account_type(party_type)
if party_account_type == "Receivable":
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
held_invoices = get_held_invoices(party_type, party)
invoice_list = frappe.db.sql(
"""
select
voucher_no, voucher_type, posting_date, due_date,
ifnull(sum({dr_or_cr}), 0) as invoice_amount,
account_currency as currency
from
`tabGL Entry`
where
party_type = %(party_type)s and party = %(party)s
and account = %(account)s and {dr_or_cr} > 0
and is_cancelled=0
{condition}
and ((voucher_type = 'Journal Entry'
and (against_voucher = '' or against_voucher is null))
or (voucher_type not in ('Journal Entry', 'Payment Entry')))
group by voucher_type, voucher_no
order by posting_date, name""".format(
dr_or_cr=dr_or_cr, condition=condition or ""
),
{
"party_type": party_type,
"party": party,
"account": account,
},
as_dict=True,
)
payment_entries = frappe.db.sql(
"""
select against_voucher_type, against_voucher,
ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
from `tabGL Entry`
where party_type = %(party_type)s and party = %(party)s
and account = %(account)s
and {payment_dr_or_cr} > 0
and against_voucher is not null and against_voucher != ''
and is_cancelled=0
group by against_voucher_type, against_voucher
""".format(
payment_dr_or_cr=payment_dr_or_cr
),
{"party_type": party_type, "party": party, "account": account},
as_dict=True,
)
pe_map = frappe._dict()
for d in payment_entries:
pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount)
for d in invoice_list:
payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
if outstanding_amount > 0.5 / (10**precision):
if (
filters
and filters.get("outstanding_amt_greater_than")
and not (
outstanding_amount >= filters.get("outstanding_amt_greater_than")
and outstanding_amount <= filters.get("outstanding_amt_less_than")
)
):
continue
if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
outstanding_invoices.append(
frappe._dict(
{
"voucher_no": d.voucher_no,
"voucher_type": d.voucher_type,
"posting_date": d.posting_date,
"invoice_amount": flt(d.invoice_amount),
"payment_amount": payment_amount,
"outstanding_amount": outstanding_amount,
"due_date": d.due_date,
"currency": d.currency,
}
)
)
outstanding_invoices = sorted(
outstanding_invoices, key=lambda k: k["due_date"] or getdate(nowdate())
)
return outstanding_invoices
def get_conditions(self,
get_invoices=False,
get_payments=False,
get_return_invoices=False):
condition = " and company = '{0}' ".format(self.company)
if self.get("cost_center") and (get_invoices or get_payments
or get_return_invoices):
condition = " and cost_center = '{0}' ".format(self.cost_center)
if get_invoices:
condition += (" and posting_date >= {0}".format(
frappe.db.escape(self.from_invoice_date))
if self.from_invoice_date else "")
condition += (" and posting_date <= {0}".format(
frappe.db.escape(self.to_invoice_date))
if self.to_invoice_date else "")
dr_or_cr = ("debit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== "Receivable" else "credit_in_account_currency")
if self.minimum_invoice_amount:
condition += " and {dr_or_cr} >= {amount}".format(
dr_or_cr=dr_or_cr, amount=flt(self.minimum_invoice_amount))
if self.maximum_invoice_amount:
condition += " and {dr_or_cr} <= {amount}".format(
dr_or_cr=dr_or_cr, amount=flt(self.maximum_invoice_amount))
elif get_return_invoices:
condition = " and doc.company = '{0}' ".format(self.company)
condition += (" and doc.posting_date >= {0}".format(
frappe.db.escape(self.from_payment_date))
if self.from_payment_date else "")
condition += (" and doc.posting_date <= {0}".format(
frappe.db.escape(self.to_payment_date))
if self.to_payment_date else "")
dr_or_cr = ("debit_in_account_currency"
if erpnext.get_party_account_type(self.party_type)
== "Receivable" else "credit_in_account_currency")
if self.minimum_invoice_amount:
condition += " and gl.{dr_or_cr} >= {amount}".format(
dr_or_cr=dr_or_cr, amount=flt(self.minimum_payment_amount))
if self.maximum_invoice_amount:
condition += " and gl.{dr_or_cr} <= {amount}".format(
dr_or_cr=dr_or_cr, amount=flt(self.maximum_payment_amount))
else:
condition += (" and posting_date >= {0}".format(
frappe.db.escape(self.from_payment_date))
if self.from_payment_date else "")
condition += (" and posting_date <= {0}".format(
frappe.db.escape(self.to_payment_date))
if self.to_payment_date else "")
if self.minimum_payment_amount:
condition += (" and unallocated_amount >= {0}".format(
flt(self.minimum_payment_amount)) if get_payments else
" and total_debit >= {0}".format(
flt(self.minimum_payment_amount)))
if self.maximum_payment_amount:
condition += (" and unallocated_amount <= {0}".format(
flt(self.maximum_payment_amount)) if get_payments else
" and total_debit <= {0}".format(
flt(self.maximum_payment_amount)))
return condition
def get_advance_journal_entries(party_type, party, party_account, order_doctype,
order_list=None, include_unallocated=True, against_all_orders=False, against_account=None, limit=None):
journal_entries = []
if erpnext.get_party_account_type(party_type) == "Receivable":
dr_or_cr = "credit_in_account_currency"
bal_dr_or_cr = "gle_je.credit_in_account_currency - gle_je.debit_in_account_currency"
payment_dr_or_cr = "gle_payment.debit_in_account_currency - gle_payment.credit_in_account_currency"
else:
dr_or_cr = "debit_in_account_currency"
bal_dr_or_cr = "gle_je.debit_in_account_currency - gle_je.credit_in_account_currency"
payment_dr_or_cr = "gle_payment.credit_in_account_currency - gle_payment.debit_in_account_currency"
limit_cond = "limit %(limit)s" if limit else ""
# JVs against order documents
if order_list or against_all_orders:
if order_list:
order_condition = "and ifnull(jea.reference_name, '') in ('{0}')" \
.format("', '".join([frappe.db.escape(d) for d in order_list]))
else:
order_condition = "and ifnull(jea.reference_name, '') != ''"
against_account_condition = "and jea.against_account like '%%{0}%%'".format(frappe.db.escape(against_account)) \
if against_account else ""
journal_entries += frappe.db.sql("""
select
"Journal Entry" as reference_type, je.name as reference_name, je.remark as remarks,
jea.{dr_or_cr} as amount, jea.name as reference_row, jea.reference_name as against_order
from
`tabJournal Entry` je, `tabJournal Entry Account` jea
where
je.name = jea.parent and jea.account = %(account)s
and jea.party_type = %(party_type)s and jea.party = %(party)s
and {dr_or_cr} > 0 and jea.reference_type = '{order_doctype}' and je.docstatus = 1
{order_condition} {against_account_condition}
order by je.posting_date
{limit_cond}""".format(
dr_or_cr=dr_or_cr,
order_doctype=order_doctype,
order_condition=order_condition,
against_account_condition=against_account_condition,
limit_cond=limit_cond
), {
"party_type": party_type,
"party": party,
"account": party_account,
"limit": limit
}, as_dict=1)
# Unallocated payment JVs
if include_unallocated:
against_account_condition = ""
if against_account:
against_account_condition = "and GROUP_CONCAT(gle_je.against) like '%%{0}%%'".format(frappe.db.escape(against_account))
journal_entries += frappe.db.sql("""
select
gle_je.voucher_type as reference_type, je.name as reference_name, je.remark as remarks,
ifnull(sum({bal_dr_or_cr}), 0) - (
select ifnull(sum({payment_dr_or_cr}), 0)
from `tabGL Entry` gle_payment
where
gle_payment.against_voucher_type = gle_je.voucher_type
and gle_payment.against_voucher = gle_je.voucher_no
and gle_payment.party_type = gle_je.party_type
and gle_payment.party = gle_je.party
and gle_payment.account = gle_je.account
and abs({payment_dr_or_cr}) > 0
) as amount
from `tabGL Entry` gle_je
inner join `tabJournal Entry` je on je.name = gle_je.voucher_no
where
gle_je.party_type = %(party_type)s and gle_je.party = %(party)s and gle_je.account = %(account)s
and gle_je.voucher_type = 'Journal Entry' and (gle_je.against_voucher = '' or gle_je.against_voucher is null)
and abs({bal_dr_or_cr}) > 0
group by gle_je.voucher_no
having amount > 0.005 {against_account_condition}
order by gle_je.posting_date
{limit_cond}""".format(
bal_dr_or_cr=bal_dr_or_cr,
payment_dr_or_cr=payment_dr_or_cr,
against_account_condition=against_account_condition,
limit_cond=limit_cond
), {
"party_type": party_type,
"party": party,
"account": party_account,
"limit": limit
}, as_dict=True)
return list(journal_entries)
def get_outstanding_invoices(party_type, party, account, condition=None, limit=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2
if erpnext.get_party_account_type(party_type) == 'Receivable':
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
invoice = 'Sales Invoice' if erpnext.get_party_account_type(party_type) == 'Receivable' else 'Purchase Invoice'
held_invoices = get_held_invoices(party_type, party)
limit_cond = "limit %s" % limit if limit else ""
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount
from
`tabGL Entry`
where
party_type = %(party_type)s and party = %(party)s
and account = %(account)s and {dr_or_cr} > 0
{condition}
and ((voucher_type = 'Journal Entry'
and (against_voucher = '' or against_voucher is null))
or (voucher_type not in ('Journal Entry', 'Payment Entry')))
group by voucher_type, voucher_no
order by posting_date, name {limit_cond}""".format(
dr_or_cr=dr_or_cr,
invoice = invoice,
condition=condition or "",
limit_cond = limit_cond
), {
"party_type": party_type,
"party": party,
"account": account,
}, as_dict=True)
payment_entries = frappe.db.sql("""
select against_voucher_type, against_voucher,
ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
from `tabGL Entry`
where party_type = %(party_type)s and party = %(party)s
and account = %(account)s
and {payment_dr_or_cr} > 0
and against_voucher is not null and against_voucher != ''
group by against_voucher_type, against_voucher
""".format(payment_dr_or_cr=payment_dr_or_cr), {
"party_type": party_type,
"party": party,
"account": account,
}, as_dict=True)
pe_map = frappe._dict()
for d in payment_entries:
pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount)
for d in invoice_list:
payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
if outstanding_amount > 0.5 / (10**precision):
if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
due_date = frappe.db.get_value(
d.voucher_type, d.voucher_no, "posting_date" if party_type == "Employee" else "due_date")
outstanding_invoices.append(
frappe._dict({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
'invoice_amount': flt(d.invoice_amount),
'payment_amount': payment_amount,
'outstanding_amount': outstanding_amount,
'due_date': due_date
})
)
outstanding_invoices = sorted(outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate()))
return outstanding_invoices
def check_if_advance_entry_modified(args):
"""
check if there is already a voucher reference
check if amount is same
check if jv is submitted
"""
ret = None
if args.voucher_type == "Journal Entry":
if args.voucher_detail_no:
ret = frappe.db.sql(
"""select je.name
from `tabJournal Entry` je, `tabJournal Entry Account` jea
where
je.name = jea.parent and jea.account = %(account)s and je.docstatus=1
and je.name = %(voucher_no)s and jea.name = %(voucher_detail_no)s
and jea.party_type = %(party_type)s and jea.party = %(party)s
and ifnull(jea.reference_type, '') in ('', 'Sales Order', 'Purchase Order', 'Employee Advance')
and jea.{dr_or_cr} = %(unadjusted_amount)s""".format(
dr_or_cr=args.dr_or_cr), args)
else:
if erpnext.get_party_account_type(args.party_type) == 'Receivable':
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
else:
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
ret = frappe.db.sql(
"""
select sum({dr_or_cr}) as outstanding_amount
from `tabGL Entry`
where
((voucher_type='Journal Entry' and voucher_no=%(voucher_no)s and (against_voucher is null or against_voucher=''))
or (against_voucher_type='Journal Entry' and against_voucher=%(voucher_no)s))
and party_type=%(party_type)s and party=%(party)s and account=%(account)s
having outstanding_amount=%(unadjusted_amount)s""".format(
dr_or_cr=dr_or_cr), args)
else:
party_account_field = ("paid_from" if erpnext.get_party_account_type(
args.party_type) == 'Receivable' else "paid_to")
if args.voucher_detail_no:
ret = frappe.db.sql(
"""select pe.name
from `tabPayment Entry` pe, `tabPayment Entry Reference` pref
where
pe.name = pref.parent and pe.docstatus = 1
and pe.name = %(voucher_no)s and pref.name = %(voucher_detail_no)s
and pe.party_type = %(party_type)s and pe.party = %(party)s and pe.{0} = %(account)s
and pref.reference_doctype in ('Sales Order', 'Purchase Order', 'Employee Advance')
and pref.allocated_amount = %(unadjusted_amount)s
""".format(party_account_field), args)
else:
ret = frappe.db.sql(
"""select name from `tabPayment Entry`
where
name = %(voucher_no)s and docstatus = 1
and party_type = %(party_type)s and party = %(party)s and {0} = %(account)s
and unallocated_amount = %(unadjusted_amount)s
""".format(party_account_field), args)
if not ret:
throw(
_("""Payment Entry has been modified after you pulled it. Please pull it again."""
))
def get_outstanding_invoices(party_type,
party,
account,
condition=None,
include_negative_outstanding=False):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice",
"outstanding_amount") or 2
if erpnext.get_party_account_type(party_type) == 'Receivable':
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
held_invoices = get_held_invoices(party_type, party)
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount
from
`tabGL Entry`
where
party_type = %(party_type)s and party = %(party)s and account = %(account)s
and (against_voucher = '' or against_voucher is null) and voucher_type != 'Payment Entry'
{condition}
group by voucher_type, voucher_no
order by posting_date, name
""".format(dr_or_cr=dr_or_cr, condition=condition or ""), {
"party_type": party_type,
"party": party,
"account": account
},
as_dict=True)
payment_entries = frappe.db.sql("""
select
against_voucher_type, against_voucher, ifnull(sum({payment_dr_or_cr}), 0) as payment_amount
from
`tabGL Entry`
where
party_type = %(party_type)s and party = %(party)s and account = %(account)s
and against_voucher is not null and against_voucher != ''
group by against_voucher_type, against_voucher
""".format(payment_dr_or_cr=payment_dr_or_cr), {
"party_type": party_type,
"party": party,
"account": account,
},
as_dict=True)
pe_map = frappe._dict()
for d in payment_entries:
pe_map.setdefault((d.against_voucher_type, d.against_voucher),
d.payment_amount)
for d in invoice_list:
payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0)
outstanding_amount = flt(d.invoice_amount - payment_amount, precision)
diff = abs(outstanding_amount
) if include_negative_outstanding else outstanding_amount
if diff > 0.5 / (10**precision):
if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
due_date = frappe.db.get_value(
d.voucher_type, d.voucher_no,
"posting_date" if party_type == "Employee" else "due_date")
outstanding_invoices.append(
frappe._dict({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
'invoice_amount': flt(d.invoice_amount),
'payment_amount': payment_amount,
'outstanding_amount': outstanding_amount,
'due_date': due_date
}))
outstanding_invoices = sorted(
outstanding_invoices,
key=lambda k:
(k['outstanding_amount'] > 0, k['due_date'] or getdate(nowdate())))
return outstanding_invoices
