<commit_before># Copyright (c) 2013, Web Notes 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.account,
				t2.name as voucher_detail_no, {dr_or_cr} as payment_amount, t2.is_advance
			from
				`tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
			where
				t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
				and t2.account = %(party_account)s and {dr_or_cr} > 0
				and ifnull(t2.against_voucher, '')='' and ifnull(t2.against_invoice, '')=''
				and ifnull(t2.against_jv, '')='' {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_account": self.party_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('payment_reconciliation_payments', [])
		for e in jv_entries:
			ent = self.append('payment_reconciliation_payments', {})
			ent.journal_voucher = 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 'payment_reconciliation_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
				account = %s and {dr_or_cr} > 0 {cond}
			group by voucher_type, voucher_no
		""".format(**{
			"cond": cond,
			"dr_or_cr": dr_or_cr
		}), (self.party_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
					account = %s and {0} > 0
					and against_voucher_type = %s and ifnull(against_voucher, '') = %s
			""".format("credit" if self.party_type == "Customer" else "debit"),
			(self.party_account, d.voucher_type, d.voucher_no))

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

			if d.invoice_amount > payment_amount:
				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': d.invoice_amount - payment_amount})

		self.add_invoice_entries(non_reconciled_invoices)

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

		for e in non_reconciled_invoices:<commit_msg>[fix] Floating point issue fixed in payment reconciliation<commit_after># Copyright (c) 2013, Web Notes 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.account,
				t2.name as voucher_detail_no, {dr_or_cr} as payment_amount, t2.is_advance
			from
				`tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
			where
				t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
				and t2.account = %(party_account)s and {dr_or_cr} > 0
				and ifnull(t2.against_voucher, '')='' and ifnull(t2.against_invoice, '')=''
				and ifnull(t2.against_jv, '')='' {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_account": self.party_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('payment_reconciliation_payments', [])
		for e in jv_entries:
			ent = self.append('payment_reconciliation_payments', {})
			ent.journal_voucher = 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 'payment_reconciliation_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
				account = %s and {dr_or_cr} > 0 {cond}
			group by voucher_type, voucher_no
		""".format(**{
			"cond": cond,
			"dr_or_cr": dr_or_cr
		}), (self.party_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
					account = %s and {0} > 0
					and against_voucher_type = %s and ifnull(against_voucher, '') = %s
			""".format("credit" if self.party_type == "Customer" else "debit"),
			(self.party_account, d.voucher_type, 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 'payment_reconciliation_invoices' with JVs and Invoices to reconcile against
		self.set('payment_reconciliation_invoices', [])

		for e in non_reconciled_invoices:
{'fraction_non_alphanumeric': 0.11958245243128963, 'fraction_numerical': 0.011231501057082453, 'mean_word_length': 4.368085106382979, 'pattern_counts': {'":': 14, '<': 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': '7662061', 'n_tokens_mistral': 3315, 'n_tokens_neox': 2831, 'n_words': 576}