From 3f119ff96a0f482f26286d0dda188cee1a78f756 Mon Sep 17 00:00:00 2001 From: Gursheen Kaur Anand <40693548+GursheenK@users.noreply.github.com> Date: Sat, 3 Feb 2024 13:05:41 +0530 Subject: [PATCH] feat: reference for POS SI payments (#39523) * feat: reference field in SI payment * fix: document link for pos si * refactor: pos invoice queries --- .../doctype/bank_clearance/bank_clearance.py | 91 +++++---- .../sales_invoice_payment.json | 187 +++++++++--------- .../sales_invoice_payment.py | 21 ++ 3 files changed, 176 insertions(+), 123 deletions(-) diff --git a/erpnext/accounts/doctype/bank_clearance/bank_clearance.py b/erpnext/accounts/doctype/bank_clearance/bank_clearance.py index 8edd376f220..b067a58e392 100644 --- a/erpnext/accounts/doctype/bank_clearance/bank_clearance.py +++ b/erpnext/accounts/doctype/bank_clearance/bank_clearance.py @@ -5,7 +5,9 @@ import frappe from frappe import _, msgprint from frappe.model.document import Document +from frappe.query_builder.custom import ConstantColumn from frappe.utils import flt, fmt_money, getdate +from pypika import Order import erpnext @@ -157,39 +159,62 @@ def get_payment_entries_for_bank_clearance( pos_sales_invoices, pos_purchase_invoices = [], [] if include_pos_transactions: - pos_sales_invoices = frappe.db.sql( - """ - select - "Sales Invoice Payment" as payment_document, sip.name as payment_entry, sip.amount as debit, - si.posting_date, si.customer as against_account, sip.clearance_date, - account.account_currency, 0 as credit - from `tabSales Invoice Payment` sip, `tabSales Invoice` si, `tabAccount` account - where - sip.account=%(account)s and si.docstatus=1 and sip.parent = si.name - and account.name = sip.account and si.posting_date >= %(from)s and si.posting_date <= %(to)s - order by - si.posting_date ASC, si.name DESC - """, - {"account": account, "from": from_date, "to": to_date}, - as_dict=1, - ) - - pos_purchase_invoices = frappe.db.sql( - """ - select - "Purchase Invoice" as payment_document, pi.name as payment_entry, pi.paid_amount as credit, - pi.posting_date, pi.supplier as against_account, pi.clearance_date, - account.account_currency, 0 as debit - from `tabPurchase Invoice` pi, `tabAccount` account - where - pi.cash_bank_account=%(account)s and pi.docstatus=1 and account.name = pi.cash_bank_account - and pi.posting_date >= %(from)s and pi.posting_date <= %(to)s - order by - pi.posting_date ASC, pi.name DESC - """, - {"account": account, "from": from_date, "to": to_date}, - as_dict=1, - ) + si_payment = frappe.qb.DocType("Sales Invoice Payment") + si = frappe.qb.DocType("Sales Invoice") + acc = frappe.qb.DocType("Account") + + pos_sales_invoices = ( + frappe.qb.from_(si_payment) + .inner_join(si) + .on(si_payment.parent == si.name) + .inner_join(acc) + .on(si_payment.account == acc.name) + .select( + ConstantColumn("Sales Invoice").as_("payment_document"), + si.name.as_("payment_entry"), + si_payment.reference_no.as_("cheque_number"), + si_payment.amount.as_("debit"), + si.posting_date, + si.customer.as_("against_account"), + si_payment.clearance_date, + acc.account_currency, + ConstantColumn(0).as_("credit"), + ) + .where( + (si.docstatus == 1) + & (si_payment.account == account) + & (si.posting_date >= from_date) + & (si.posting_date <= to_date) + ) + .orderby(si.posting_date) + .orderby(si.name, order=Order.desc) + ).run(as_dict=True) + + pi = frappe.qb.DocType("Purchase Invoice") + + pos_purchase_invoices = ( + frappe.qb.from_(pi) + .inner_join(acc) + .on(pi.cash_bank_account == acc.name) + .select( + ConstantColumn("Purchase Invoice").as_("payment_document"), + pi.name.as_("payment_entry"), + pi.paid_amount.as_("credit"), + pi.posting_date, + pi.supplier.as_("against_account"), + pi.clearance_date, + acc.account_currency, + ConstantColumn(0).as_("debit"), + ) + .where( + (pi.docstatus == 1) + & (pi.cash_bank_account == account) + & (pi.posting_date >= from_date) + & (pi.posting_date <= to_date) + ) + .orderby(pi.posting_date) + .orderby(pi.name, order=Order.desc) + ).run(as_dict=True) entries = ( list(payment_entries) diff --git a/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.json b/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.json index ac3e527a1d9..f227000b54a 100644 --- a/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.json +++ b/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.json @@ -1,91 +1,98 @@ { - "actions": [], - "creation": "2016-05-08 23:49:38.842621", - "doctype": "DocType", - "editable_grid": 1, - "engine": "InnoDB", - "field_order": [ - "default", - "mode_of_payment", - "amount", - "column_break_3", - "account", - "type", - "base_amount", - "clearance_date" - ], - "fields": [ - { - "fieldname": "mode_of_payment", - "fieldtype": "Link", - "in_list_view": 1, - "label": "Mode of Payment", - "options": "Mode of Payment", - "reqd": 1 - }, - { - "default": "0", - "depends_on": "eval:parent.doctype == 'Sales Invoice'", - "fieldname": "amount", - "fieldtype": "Currency", - "in_list_view": 1, - "label": "Amount", - "options": "currency", - "reqd": 1 - }, - { - "fieldname": "column_break_3", - "fieldtype": "Column Break" - }, - { - "fieldname": "account", - "fieldtype": "Link", - "label": "Account", - "options": "Account", - "print_hide": 1, - "read_only": 1 - }, - { - "fetch_from": "mode_of_payment.type", - "fieldname": "type", - "fieldtype": "Read Only", - "label": "Type" - }, - { - "fieldname": "base_amount", - "fieldtype": "Currency", - "label": "Base Amount (Company Currency)", - "no_copy": 1, - "options": "Company:company:default_currency", - "print_hide": 1, - "read_only": 1 - }, - { - "fieldname": "clearance_date", - "fieldtype": "Date", - "label": "Clearance Date", - "print_hide": 1, - "read_only": 1, - "no_copy": 1 - }, - { - "default": "0", - "fieldname": "default", - "fieldtype": "Check", - "hidden": 1, - "label": "Default", - "read_only": 1 - } - ], - "istable": 1, - "links": [], - "modified": "2020-08-03 12:45:39.986598", - "modified_by": "Administrator", - "module": "Accounts", - "name": "Sales Invoice Payment", - "owner": "Administrator", - "permissions": [], - "quick_entry": 1, - "sort_field": "modified", - "sort_order": "DESC" - } \ No newline at end of file + "actions": [], + "creation": "2016-05-08 23:49:38.842621", + "doctype": "DocType", + "editable_grid": 1, + "engine": "InnoDB", + "field_order": [ + "default", + "mode_of_payment", + "amount", + "reference_no", + "column_break_3", + "account", + "type", + "base_amount", + "clearance_date" + ], + "fields": [ + { + "fieldname": "mode_of_payment", + "fieldtype": "Link", + "in_list_view": 1, + "label": "Mode of Payment", + "options": "Mode of Payment", + "reqd": 1 + }, + { + "default": "0", + "depends_on": "eval:parent.doctype == 'Sales Invoice'", + "fieldname": "amount", + "fieldtype": "Currency", + "in_list_view": 1, + "label": "Amount", + "options": "currency", + "reqd": 1 + }, + { + "fieldname": "column_break_3", + "fieldtype": "Column Break" + }, + { + "fieldname": "account", + "fieldtype": "Link", + "label": "Account", + "options": "Account", + "print_hide": 1, + "read_only": 1 + }, + { + "fetch_from": "mode_of_payment.type", + "fieldname": "type", + "fieldtype": "Read Only", + "label": "Type" + }, + { + "fieldname": "base_amount", + "fieldtype": "Currency", + "label": "Base Amount (Company Currency)", + "no_copy": 1, + "options": "Company:company:default_currency", + "print_hide": 1, + "read_only": 1 + }, + { + "fieldname": "clearance_date", + "fieldtype": "Date", + "label": "Clearance Date", + "no_copy": 1, + "print_hide": 1, + "read_only": 1 + }, + { + "default": "0", + "fieldname": "default", + "fieldtype": "Check", + "hidden": 1, + "label": "Default", + "read_only": 1 + }, + { + "fieldname": "reference_no", + "fieldtype": "Data", + "label": "Reference No" + } + ], + "istable": 1, + "links": [], + "modified": "2024-01-23 16:20:06.436979", + "modified_by": "Administrator", + "module": "Accounts", + "name": "Sales Invoice Payment", + "owner": "Administrator", + "permissions": [], + "quick_entry": 1, + "sort_field": "modified", + "sort_order": "DESC", + "states": [] +} diff --git a/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.py b/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.py index e6f49c7c82d..c7c5fcca117 100644 --- a/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.py +++ b/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.py @@ -7,4 +7,25 @@ from frappe.model.document import Document class SalesInvoicePayment(Document): + # begin: auto-generated types + # This code is auto-generated. Do not modify anything in this block. + + from typing import TYPE_CHECKING + + if TYPE_CHECKING: + from frappe.types import DF + + account: DF.Link | None + amount: DF.Currency + base_amount: DF.Currency + clearance_date: DF.Date | None + default: DF.Check + mode_of_payment: DF.Link + parent: DF.Data + parentfield: DF.Data + parenttype: DF.Data + reference_no: DF.Data | None + type: DF.ReadOnly | None + # end: auto-generated types + pass -- GitLab