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..cf62fd3
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
@@ -0,0 +1,40 @@
+// 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']]
+ ]
+ };
+ });
+ },
+
+ get_unreconciled_entries: function() {
+ var me = this;
+ if(!this.frm.doc.company || !this.frm.doc.party_account) {
+ if(!this.frm.doc.company) {
+ msgprint(__("Please enter Company"));
+ } else {
+ msgprint(__("Please enter Party Account"));
+ }
+ } else {
+ return this.frm.call({
+ doc: me.frm.doc,
+ method: 'get_unreconciled_entries'
+ });
+ }
+ }
+});
+
+$.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..12253d0
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
@@ -0,0 +1,152 @@
+{
+ "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": 0,
+ "in_list_view": 1,
+ "label": "Party Type",
+ "options": "Customer\nSupplier",
+ "permlevel": 0,
+ "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/JV Details",
+ "permlevel": 0
+ },
+ {
+ "fieldname": "payment_reconciliation_invoices",
+ "fieldtype": "Table",
+ "label": "Payment Reconciliation Invoices",
+ "options": "Payment Reconciliation Invoice",
+ "permlevel": 0,
+ "read_only": 1
+ }
+ ],
+ "issingle": 1,
+ "modified": "2014-07-11 15:01:47.133918",
+ "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..fc1ff24
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -0,0 +1,119 @@
+# 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):
+ jv_entries = self.get_jv_entries()
+ self.add_payment_entries(jv_entries)
+ invoice_entries = self.get_invoice_entries()
+
+ self.add_invoice_entries(invoice_entries)
+
+ def get_jv_entries(self):
+ self.check_mandatory()
+
+ dr_or_cr = "credit" if self.party_type == "Customer" else "debit"
+
+ cond = self.check_condition(dr_or_cr)
+
+ 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.%s, t2.is_advance
+ from
+ `tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
+ where
+ t1.name = t2.parent and t1.docstatus = 1 and t2.account = %s
+ and t2.%s > 0 and ifnull(t2.against_voucher, '')='' and ifnull(t2.against_invoice, '')=''
+ and ifnull(t2.against_jv, '')='' %s
+ group by t1.name, t2.name """ % (dr_or_cr, '%s', dr_or_cr, cond), (self.party_account),
+ as_dict = True)
+ return 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')
+
+ def get_invoice_entries(self):
+ #Fetch JVs, Sales and Purchase Invoices for 'payment_reconciliation_invoices' to reconcile against
+ non_reconciled_invoices = []
+ self.check_mandatory()
+
+ 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"), 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})
+
+ return 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:
+ return
+ 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 check_mandatory(self):
+ pass
+
+ 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..008b326
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.json
@@ -0,0 +1,59 @@
+{
+ "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",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "fieldname": "invoice_date",
+ "fieldtype": "Date",
+ "in_list_view": 1,
+ "label": "Invoice Date",
+ "permlevel": 0,
+ "read_only": 1
+ },
+ {
+ "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-09 17:15:00.069551",
+ "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..e136881
--- /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
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe.model.document import Document
+
+class PaymentReconciliationInvoice(Document):
+ pass
\ No newline at end of file
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..231dfae
--- /dev/null
+++ b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
@@ -0,0 +1,80 @@
+{
+ "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
+ },
+ {
+ "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": "Data",
+ "in_list_view": 1,
+ "label": "Invoice Number",
+ "permlevel": 0,
+ "reqd": 1
+ },
+ {
+ "fieldname": "remark",
+ "fieldtype": "Text",
+ "in_list_view": 1,
+ "label": "Remark",
+ "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
+ }
+ ],
+ "istable": 1,
+ "modified": "2014-07-14 16:48:45.875052",
+ "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..9082ef9
--- /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
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe.model.document import Document
+
+class PaymentReconciliationPayment(Document):
+ pass
\ No newline at end of file