Merge branch 'develop' of https://github.com/frappe/erpnext into wip_payrec
diff --git a/erpnext/accounts/doctype/payment_reconciliation/__init__.py b/erpnext/accounts/doctype/payment_reconciliation/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation/__init__.py
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
new file mode 100644
index 0000000..14520c2
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
@@ -0,0 +1,64 @@
+// Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+// For license information, please see license.txt
+
+frappe.provide("erpnext.accounts");
+
+erpnext.accounts.PaymentReconciliationController = frappe.ui.form.Controller.extend({
+
+ onload: function() {
+ var me = this
+ this.frm.set_query ('party_account', function() {
+ return{
+ filters:[
+ ['Account', 'company', '=', me.frm.doc.company],
+ ['Account', 'group_or_ledger', '=', 'Ledger'],
+ ['Account', 'master_type', 'in', ['Customer', 'Supplier']]
+ ]
+ };
+ });
+
+ var help_content = ['<i class="icon-hand-right"></i> Note:',
+ '<ul>If you are unable to match the exact amount, then amend your Journal Voucher and split rows such that your amounts match the invoice you are trying to reconcile. </ul>'].join("\n");
+ this.frm.set_value("reconcile_help", help_content);
+ },
+
+
+ get_unreconciled_entries: function() {
+ var me = this;
+ return this.frm.call({
+ doc: me.frm.doc,
+ method: 'get_unreconciled_entries',
+ callback: function(r, rt) {
+ var invoices = [];
+
+ $.each(me.frm.doc.payment_reconciliation_invoices || [], function(i, row) {
+ if (row.invoice_number && !inList(invoices, row.invoice_number))
+ invoices.push(row.invoice_number);
+ });
+
+ frappe.meta.get_docfield("Payment Reconciliation Payment", "invoice_number",
+ me.frm.doc.name).options = invoices.join("\n");
+
+ $.each(me.frm.doc.payment_reconciliation_payments || [], function(i, p) {
+ if(!inList(invoices, cstr(p.invoice_number))) p.invoice_number = null;
+ });
+
+ refresh_field("payment_reconciliation_payments");
+ }
+ });
+
+ },
+
+ reconcile: function() {
+ var me = this;
+ return this.frm.call({
+ doc: me.frm.doc,
+ method: 'reconcile'
+ });
+ }
+
+});
+
+$.extend(cur_frm.cscript, new erpnext.accounts.PaymentReconciliationController({frm: cur_frm}));
+
+cur_frm.add_fetch('party_account', 'master_type', 'party_type')
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
new file mode 100644
index 0000000..40b5706
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
@@ -0,0 +1,162 @@
+{
+ "allow_copy": 1,
+ "creation": "2014-07-09 12:04:51.681583",
+ "custom": 0,
+ "docstatus": 0,
+ "doctype": "DocType",
+ "document_type": "",
+ "fields": [
+ {
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "label": "Company",
+ "options": "Company",
+ "permlevel": 0,
+ "reqd": 1
+ },
+ {
+ "depends_on": "",
+ "fieldname": "party_account",
+ "fieldtype": "Link",
+ "in_list_view": 0,
+ "label": "Party Account",
+ "options": "Account",
+ "permlevel": 0,
+ "reqd": 1,
+ "search_index": 0
+ },
+ {
+ "fieldname": "party_type",
+ "fieldtype": "Select",
+ "hidden": 1,
+ "in_list_view": 1,
+ "label": "Party Type",
+ "options": "Customer\nSupplier",
+ "permlevel": 0,
+ "read_only": 1,
+ "reqd": 0
+ },
+ {
+ "fieldname": "bank_cash_account",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Bank / Cash Account",
+ "options": "Account",
+ "permlevel": 0,
+ "reqd": 0,
+ "search_index": 0
+ },
+ {
+ "fieldname": "col_break1",
+ "fieldtype": "Column Break",
+ "label": "Column Break",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "from_date",
+ "fieldtype": "Date",
+ "in_list_view": 1,
+ "label": "From Date",
+ "permlevel": 0,
+ "search_index": 1
+ },
+ {
+ "fieldname": "to_date",
+ "fieldtype": "Date",
+ "in_list_view": 1,
+ "label": "To Date",
+ "permlevel": 0,
+ "search_index": 1
+ },
+ {
+ "fieldname": "minimum_amount",
+ "fieldtype": "Currency",
+ "label": "Minimum Amount",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "maximum_amount",
+ "fieldtype": "Currency",
+ "label": "Maximum Amount",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "get_unreconciled_entries",
+ "fieldtype": "Button",
+ "label": "Get Unreconciled Entries",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "sec_break1",
+ "fieldtype": "Section Break",
+ "label": "Unreconciled Payment Details",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "payment_reconciliation_payments",
+ "fieldtype": "Table",
+ "label": "Payment Reconciliation Payments",
+ "options": "Payment Reconciliation Payment",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "reconcile",
+ "fieldtype": "Button",
+ "label": "Reconcile",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "sec_break2",
+ "fieldtype": "Section Break",
+ "label": "Invoice/Journal Voucher Details",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "payment_reconciliation_invoices",
+ "fieldtype": "Table",
+ "label": "Payment Reconciliation Invoices",
+ "options": "Payment Reconciliation Invoice",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "reconcile_help",
+ "fieldtype": "Small Text",
+ "label": "",
+ "permlevel": 0,
+ "read_only": 1
+ }
+ ],
+ "hide_toolbar": 1,
+ "issingle": 1,
+ "modified": "2014-07-18 15:53:20.638456",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Payment Reconciliation",
+ "name_case": "",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "cancel": 0,
+ "create": 1,
+ "delete": 1,
+ "permlevel": 0,
+ "read": 1,
+ "role": "Accounts Manager",
+ "submit": 0,
+ "write": 1
+ },
+ {
+ "cancel": 0,
+ "create": 1,
+ "delete": 1,
+ "permlevel": 0,
+ "read": 1,
+ "role": "Accounts User",
+ "submit": 0,
+ "write": 1
+ }
+ ],
+ "sort_field": "modified",
+ "sort_order": "DESC"
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
new file mode 100644
index 0000000..be53aca
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -0,0 +1,178 @@
+# 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, t2.{dr_or_cr}, t2.is_advance
+ from
+ `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
+ where
+ t1.name = t2.parent and t1.docstatus = 1 and t2.account = %(party_account)s
+ and t2.{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)
+ group by t1.name, t2.name """.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('credit')) or flt(e.get('debit'))
+ 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(ifnull(%s, 0)), 0) as amount
+ from
+ `tabGL Entry`
+ where
+ account = %s and ifnull(%s, 0) > 0 %s
+ group by voucher_no, voucher_type""" % (dr_or_cr, '%s',
+ dr_or_cr, cond), (self.party_account), as_dict=True)
+
+ for d in invoice_list:
+ payment_amount = frappe.db.sql("""
+ select
+ ifnull(sum(ifnull(%s, 0)), 0)
+ from
+ `tabGL Entry`
+ where
+ account = %s and against_voucher_type = %s and ifnull(against_voucher, '') = %s""" %
+ (("credit" if self.party_type == "Customer" else "debit"), '%s', '%s', '%s'),
+ (self.party_account, d.voucher_type, d.voucher_no))
+
+ payment_amount = payment_amount[0][0] if payment_amount else 0
+
+ if d.amount > payment_amount:
+ non_reconciled_invoices.append({
+ 'voucher_no': d.voucher_no,
+ 'voucher_type': d.voucher_type,
+ 'posting_date': d.posting_date,
+ 'amount': flt(d.amount),
+ 'outstanding_amount': d.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', [])
+ if not non_reconciled_invoices:
+ frappe.throw(_("No invoices found to be reconciled"))
+
+
+ for e in non_reconciled_invoices:
+ ent = self.append('payment_reconciliation_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('amount'))
+ ent.outstanding_amount = e.get('outstanding_amount')
+
+ def reconcile(self, args):
+ self.get_invoice_entries()
+ self.validate_invoice()
+ dr_or_cr = "credit" if self.party_type == "Customer" else "debit"
+ lst = []
+ for e in self.get('payment_reconciliation_payments'):
+ lst.append({
+ 'voucher_no' : e.journal_voucher,
+ 'voucher_detail_no' : e.voucher_detail_number,
+ 'against_voucher_type' : e.invoice_type,
+ 'against_voucher' : e.invoice_number,
+ 'account' : self.party_account,
+ 'is_advance' : e.is_advance,
+ 'dr_or_cr' : dr_or_cr,
+ 'unadjusted_amt' : flt(e.amount),
+ 'allocated_amt' : flt(e.amount)
+ })
+
+ if lst:
+ from erpnext.accounts.utils import reconcile_against_document
+ reconcile_against_document(lst)
+ self.get_unreconciled_entries()
+ msgprint(_("Successfully Reconciled"))
+
+
+ def check_mandatory_to_fetch(self):
+ for fieldname in ["company", "party_account"]:
+ if not self.get(fieldname):
+ frappe.throw(_("Please select {0} first").format(self.meta.get_label(fieldname)))
+
+
+ def validate_invoice(self):
+ unreconciled_invoices = frappe._dict()
+ for d in self.get("payment_reconciliation_invoices"):
+ unreconciled_invoices.setdefault(d.invoice_type, {}).setdefault(d.invoice_number, d.outstanding_amount)
+
+ invoices_to_reconcile = []
+ for p in self.get("payment_reconciliation_payments"):
+ if p.invoice_type and p.invoice_number:
+ invoices_to_reconcile.append(p.invoice_number)
+
+ if p.invoice_number not in unreconciled_invoices.get(p.invoice_type):
+ frappe.throw(_("{0}: {1} not found in Invoice Details table")
+ .format(p.invoice_type, p.invoice_number))
+
+ if p.amount > unreconciled_invoices.get(p.invoice_type).get(p.invoice_number):
+ frappe.throw(_("Row {0}: Payment amount must be less than or equals to invoice outstanding amount").format(p.idx))
+
+ if not invoices_to_reconcile:
+ frappe.throw(_("Please select Invoice Type and Invoice Number in atleast one row"))
+
+ def check_condition(self, dr_or_cr):
+ cond = self.from_date and " and posting_date >= '" + self.from_date + "'" or ""
+ cond += self.to_date and " and posting_date <= '" + self.to_date + "'" or ""
+
+ if self.minimum_amount:
+ cond += " and ifnull(%s, 0) >= %s" % (dr_or_cr, self.minimum_amount)
+ if self.maximum_amount:
+ cond += " and ifnull(%s, 0) <= %s" % (dr_or_cr, self.maximum_amount)
+
+ return cond
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/payment_reconciliation_invoice/__init__.py b/erpnext/accounts/doctype/payment_reconciliation_invoice/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation_invoice/__init__.py
diff --git a/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.json b/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.json
new file mode 100644
index 0000000..4e4ee1a
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.json
@@ -0,0 +1,66 @@
+{
+ "creation": "2014-07-09 16:14:23.672922",
+ "docstatus": 0,
+ "doctype": "DocType",
+ "document_type": "",
+ "fields": [
+ {
+ "fieldname": "invoice_type",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Invoice Type",
+ "options": "Sales Invoice\nPurchase Invoice\nJournal Voucher",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "invoice_number",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Invoice Number",
+ "options": "",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "invoice_date",
+ "fieldtype": "Date",
+ "in_list_view": 1,
+ "label": "Invoice Date",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "col_break1",
+ "fieldtype": "Column Break",
+ "label": "Column Break",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "amount",
+ "fieldtype": "Currency",
+ "in_list_view": 1,
+ "label": "Amount",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "outstanding_amount",
+ "fieldtype": "Currency",
+ "in_list_view": 1,
+ "label": "Outstanding Amount",
+ "permlevel": 0,
+ "read_only": 1
+ }
+ ],
+ "istable": 1,
+ "modified": "2014-07-18 12:20:51.269974",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Payment Reconciliation Invoice",
+ "name_case": "",
+ "owner": "Administrator",
+ "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "DESC"
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.py b/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.py
new file mode 100644
index 0000000..3094a17
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe.model.document import Document
+
+class PaymentReconciliationInvoice(Document):
+ pass
diff --git a/erpnext/accounts/doctype/payment_reconciliation_payment/__init__.py b/erpnext/accounts/doctype/payment_reconciliation_payment/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation_payment/__init__.py
diff --git a/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
new file mode 100644
index 0000000..3dd36fc
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
@@ -0,0 +1,107 @@
+{
+ "creation": "2014-07-09 16:13:35.452759",
+ "docstatus": 0,
+ "doctype": "DocType",
+ "document_type": "",
+ "fields": [
+ {
+ "fieldname": "journal_voucher",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Journal Voucher",
+ "options": "Journal Voucher",
+ "permlevel": 0,
+ "read_only": 1,
+ "reqd": 0
+ },
+ {
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "in_list_view": 1,
+ "label": "Posting Date",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "amount",
+ "fieldtype": "Currency",
+ "in_list_view": 1,
+ "label": "Amount",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "is_advance",
+ "fieldtype": "Data",
+ "hidden": 1,
+ "label": "Is Advance",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "voucher_detail_number",
+ "fieldtype": "Data",
+ "hidden": 1,
+ "in_list_view": 0,
+ "label": "Voucher Detail Number",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "col_break1",
+ "fieldtype": "Column Break",
+ "label": "Column Break",
+ "permlevel": 0
+ },
+ {
+ "default": "Sales Invoice",
+ "fieldname": "invoice_type",
+ "fieldtype": "Select",
+ "in_list_view": 1,
+ "label": "Invoice Type",
+ "options": "Sales Invoice\nPurchase Invoice\nJournal Voucher",
+ "permlevel": 0,
+ "read_only": 0,
+ "reqd": 1
+ },
+ {
+ "fieldname": "invoice_number",
+ "fieldtype": "Select",
+ "in_list_view": 1,
+ "label": "Invoice Number",
+ "options": "",
+ "permlevel": 0,
+ "reqd": 1
+ },
+ {
+ "fieldname": "sec_break1",
+ "fieldtype": "Section Break",
+ "label": "",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "remark",
+ "fieldtype": "Small Text",
+ "in_list_view": 1,
+ "label": "Remark",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "col_break2",
+ "fieldtype": "Column Break",
+ "label": "Column Break",
+ "permlevel": 0
+ }
+ ],
+ "istable": 1,
+ "modified": "2014-07-18 15:53:15.589501",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Payment Reconciliation Payment",
+ "name_case": "",
+ "owner": "Administrator",
+ "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "DESC"
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.py b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.py
new file mode 100644
index 0000000..21e19bd
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe.model.document import Document
+
+class PaymentReconciliationPayment(Document):
+ pass