<commit_before># Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe

from frappe.utils import flt

from frappe import msgprint, _

from frappe.model.document import Document

class PaymentReconciliation(Document):
	def get_unreconciled_entries(self):
		self.get_jv_entries()
		self.get_invoice_entries()

	def get_jv_entries(self):
		self.check_mandatory_to_fetch()
		dr_or_cr = "credit" if self.party_type == "Customer" else "debit"

		cond = self.check_condition(dr_or_cr)

		bank_account_condition = "t2.against_account like %(bank_cash_account)s" \
				if self.bank_cash_account else "1=1"

		jv_entries = frappe.db.sql("""
			select
				t1.name as voucher_no, t1.posting_date, t1.remark,
				t2.name as voucher_detail_no, {dr_or_cr} as payment_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 ifnull(t2.reference_type, '') in ('', 'Sales Order', 'Purchase Order')
				{cond}
				and (CASE
					WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
					THEN 1=1
					ELSE {bank_account_condition}
				END)
			""".format(**{
				"dr_or_cr": dr_or_cr,
				"cond": cond,
				"bank_account_condition": bank_account_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)

		self.add_payment_entries(jv_entries)

	def add_payment_entries(self, jv_entries):
		self.set('payments', [])
		for e in jv_entries:
			ent = self.append('payments', {})
			ent.journal_entry = e.get('voucher_no')
			ent.posting_date = e.get('posting_date')
			ent.amount = flt(e.get('payment_amount'))
			ent.remark = e.get('remark')
			ent.voucher_detail_number = e.get('voucher_detail_no')
			ent.is_advance = e.get('is_advance')

	def get_invoice_entries(self):
		#Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against
		non_reconciled_invoices = []
		dr_or_cr = "debit" if self.party_type == "Customer" else "credit"
		cond = self.check_condition(dr_or_cr)

		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 {cond}
				and (CASE
					WHEN voucher_type = 'Journal Entry'
					THEN ifnull(against_voucher, '') = ''
					ELSE 1=1
				END)
			group by voucher_type, voucher_no
		""".format(**{
			"cond": cond,
			"dr_or_cr": dr_or_cr
		}), {
			"party_type": self.party_type,
			"party": self.party,
			"account": self.receivable_payable_account,
		}, as_dict=True)

		for d in invoice_list:
			payment_amount = frappe.db.sql("""
				select
					ifnull(sum(ifnull({0}, 0)), 0)
				from
					`tabGL Entry`
				where
					party_type = %(party_type)s and party = %(party)s
					and account = %(account)s and {0} > 0
					and against_voucher_type = %(against_voucher_type)s
					and ifnull(against_voucher, '') = %(against_voucher)s
			""".format("credit" if self.party_type == "Customer" else "debit"), {
				"party_type": self.party_type,
				"party": self.party,
				"account": self.receivable_payable_account,
				"against_voucher_type": d.voucher_type,
				"against_voucher": d.voucher_no
			})

			payment_amount = payment_amount[0][0] if payment_amount else 0

			if d.invoice_amount - payment_amount > 0.005:
				non_reconciled_invoices.append({
					'voucher_no': d.voucher_no,
					'voucher_type': d.voucher_type,
					'posting_date': d.posting_date,
					'invoice_amount': flt(d.invoice_amount),
					'outstanding_amount': flt(d.invoice_amount - payment_amount, 2)
				})

		self.add_invoice_entries(non_reconciled_invoices)

	def add_invoice_entries(self, non_reconciled_invoices):
		#Populate 'invoices' with JVs and Invoices to reconcile against
		self.set('invoices', [])

		for e in non_reconciled_invoices:
			ent = self.append('invoices', {})
			ent.invoice_type = e.get('voucher_type')
			ent.invoice_number = e.get('voucher_no')
			ent.invoice_date = e.get('posting_date')
			ent.amount = flt(e.get('invoice_amount'))
			ent.outstanding_amount = e.get('outstanding_amount')

	def reconcile(self, args):
		for e in self.get('payments'):
			if not e.invoice_number:
				frappe.throw(_("Row {0}: Please select Invoice Number for reconcilation").format(e.idx))

			if " | " in e.invoice_number:<commit_msg>[fix] [payment-reconciliation] ignore rows with blank invoice numbers<commit_after># Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe

from frappe.utils import flt

from frappe import msgprint, _

from frappe.model.document import Document

class PaymentReconciliation(Document):
	def get_unreconciled_entries(self):
		self.get_jv_entries()
		self.get_invoice_entries()

	def get_jv_entries(self):
		self.check_mandatory_to_fetch()
		dr_or_cr = "credit" if self.party_type == "Customer" else "debit"

		cond = self.check_condition(dr_or_cr)

		bank_account_condition = "t2.against_account like %(bank_cash_account)s" \
				if self.bank_cash_account else "1=1"

		jv_entries = frappe.db.sql("""
			select
				t1.name as voucher_no, t1.posting_date, t1.remark,
				t2.name as voucher_detail_no, {dr_or_cr} as payment_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 ifnull(t2.reference_type, '') in ('', 'Sales Order', 'Purchase Order')
				{cond}
				and (CASE
					WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
					THEN 1=1
					ELSE {bank_account_condition}
				END)
			""".format(**{
				"dr_or_cr": dr_or_cr,
				"cond": cond,
				"bank_account_condition": bank_account_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)

		self.add_payment_entries(jv_entries)

	def add_payment_entries(self, jv_entries):
		self.set('payments', [])
		for e in jv_entries:
			ent = self.append('payments', {})
			ent.journal_entry = e.get('voucher_no')
			ent.posting_date = e.get('posting_date')
			ent.amount = flt(e.get('payment_amount'))
			ent.remark = e.get('remark')
			ent.voucher_detail_number = e.get('voucher_detail_no')
			ent.is_advance = e.get('is_advance')

	def get_invoice_entries(self):
		#Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against
		non_reconciled_invoices = []
		dr_or_cr = "debit" if self.party_type == "Customer" else "credit"
		cond = self.check_condition(dr_or_cr)

		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 {cond}
				and (CASE
					WHEN voucher_type = 'Journal Entry'
					THEN ifnull(against_voucher, '') = ''
					ELSE 1=1
				END)
			group by voucher_type, voucher_no
		""".format(**{
			"cond": cond,
			"dr_or_cr": dr_or_cr
		}), {
			"party_type": self.party_type,
			"party": self.party,
			"account": self.receivable_payable_account,
		}, as_dict=True)

		for d in invoice_list:
			payment_amount = frappe.db.sql("""
				select
					ifnull(sum(ifnull({0}, 0)), 0)
				from
					`tabGL Entry`
				where
					party_type = %(party_type)s and party = %(party)s
					and account = %(account)s and {0} > 0
					and against_voucher_type = %(against_voucher_type)s
					and ifnull(against_voucher, '') = %(against_voucher)s
			""".format("credit" if self.party_type == "Customer" else "debit"), {
				"party_type": self.party_type,
				"party": self.party,
				"account": self.receivable_payable_account,
				"against_voucher_type": d.voucher_type,
				"against_voucher": d.voucher_no
			})

			payment_amount = payment_amount[0][0] if payment_amount else 0

			if d.invoice_amount - payment_amount > 0.005:
				non_reconciled_invoices.append({
					'voucher_no': d.voucher_no,
					'voucher_type': d.voucher_type,
					'posting_date': d.posting_date,
					'invoice_amount': flt(d.invoice_amount),
					'outstanding_amount': flt(d.invoice_amount - payment_amount, 2)
				})

		self.add_invoice_entries(non_reconciled_invoices)

	def add_invoice_entries(self, non_reconciled_invoices):
		#Populate 'invoices' with JVs and Invoices to reconcile against
		self.set('invoices', [])

		for e in non_reconciled_invoices:
			ent = self.append('invoices', {})
			ent.invoice_type = e.get('voucher_type')
			ent.invoice_number = e.get('voucher_no')
			ent.invoice_date = e.get('posting_date')
			ent.amount = flt(e.get('invoice_amount'))
			ent.outstanding_amount = e.get('outstanding_amount')

	def reconcile(self, args):
		for e in self.get('payments'):
			if e.invoice_number and " | " in e.invoice_number:
{'fraction_non_alphanumeric': 0.1278712818884302, 'fraction_numerical': 0.009844395046046363, 'mean_word_length': 4.049706039551042, 'pattern_counts': {'":': 34, '<': 3, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6649330', 'n_tokens_mistral': 4217, 'n_tokens_neox': 3552, 'n_words': 723}