chore: Use JSON style response and use ORM
- Use JSON style response for report columns
- Use ORM instead of frappe.db.sql
- Remove returned % from list view
diff --git a/erpnext/accounts/report/delivered_items_to_be_billed/delivered_items_to_be_billed.py b/erpnext/accounts/report/delivered_items_to_be_billed/delivered_items_to_be_billed.py
index 2aea3f6..515fd99 100644
--- a/erpnext/accounts/report/delivered_items_to_be_billed/delivered_items_to_be_billed.py
+++ b/erpnext/accounts/report/delivered_items_to_be_billed/delivered_items_to_be_billed.py
@@ -14,19 +14,93 @@
def get_column():
return [
- _("Delivery Note") + ":Link/Delivery Note:160",
- _("Date") + ":Date:100",
- _("Customer") + ":Link/Customer:120",
- _("Customer Name") + "::120",
- _("Item Code") + ":Link/Item:120",
- _("Amount") + ":Currency:100",
- _("Billed Amount") + ":Currency:100",
- _("Returned Amount") + ":Currency:120",
- _("Pending Amount") + ":Currency:100",
- _("Item Name") + "::120",
- _("Description") + "::120",
- _("Project") + ":Link/Project:120",
- _("Company") + ":Link/Company:120",
+ {
+ "label": _("Delivery Note"),
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "options": "Delivery Note",
+ "width": 160
+ },
+ {
+ "label": _("Date"),
+ "fieldname": "date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": _("Customer"),
+ "fieldname": "customer",
+ "fieldtype": "Link",
+ "options": "Customer",
+ "width": 120
+ },
+ {
+ "label": _("Customer Name"),
+ "fieldname": "customer_name",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Item Code"),
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": 120
+ },
+ {
+ "label": _("Amount"),
+ "fieldname": "amount",
+ "fieldtype": "Currency",
+ "width": 100,
+ "options": "Company:company:default_currency"
+ },
+ {
+ "label": _("Billed Amount"),
+ "fieldname": "billed_amount",
+ "fieldtype": "Currency",
+ "width": 100,
+ "options": "Company:company:default_currency"
+ },
+ {
+ "label": _("Returned Amount"),
+ "fieldname": "returned_amount",
+ "fieldtype": "Currency",
+ "width": 120,
+ "options": "Company:company:default_currency"
+ },
+ {
+ "label": _("Pending Amount"),
+ "fieldname": "pending_amount",
+ "fieldtype": "Currency",
+ "width": 120,
+ "options": "Company:company:default_currency"
+ },
+ {
+ "label": _("Item Name"),
+ "fieldname": "item_name",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Description"),
+ "fieldname": "description",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Project"),
+ "fieldname": "project",
+ "fieldtype": "Link",
+ "options": "Project",
+ "width": 120
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "width": 120
+ }
]
def get_args():
diff --git a/erpnext/accounts/report/received_items_to_be_billed/received_items_to_be_billed.py b/erpnext/accounts/report/received_items_to_be_billed/received_items_to_be_billed.py
index c7d4384..e9e9c9c 100644
--- a/erpnext/accounts/report/received_items_to_be_billed/received_items_to_be_billed.py
+++ b/erpnext/accounts/report/received_items_to_be_billed/received_items_to_be_billed.py
@@ -14,19 +14,93 @@
def get_column():
return [
- _("Purchase Receipt") + ":Link/Purchase Receipt:160",
- _("Date") + ":Date:100",
- _("Supplier") + ":Link/Supplier:120",
- _("Supplier Name") + "::120",
- _("Item Code") + ":Link/Item:120",
- _("Amount") + ":Currency:100",
- _("Billed Amount") + ":Currency:100",
- _("Returned Amount") + ":Currency:120",
- _("Pending Amount") + ":Currency:120",
- _("Item Name") + "::120",
- _("Description") + "::120",
- _("Project") + ":Link/Project:120",
- _("Company") + ":Link/Company:120",
+ {
+ "label": _("Purchase Receipt"),
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "options": "Purchase Receipt",
+ "width": 160
+ },
+ {
+ "label": _("Date"),
+ "fieldname": "date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": _("Supplier"),
+ "fieldname": "supplier",
+ "fieldtype": "Link",
+ "options": "Supplier",
+ "width": 120
+ },
+ {
+ "label": _("Supplier Name"),
+ "fieldname": "supplier_name",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Item Code"),
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": 120
+ },
+ {
+ "label": _("Amount"),
+ "fieldname": "amount",
+ "fieldtype": "Currency",
+ "width": 100,
+ "options": "Company:company:default_currency"
+ },
+ {
+ "label": _("Billed Amount"),
+ "fieldname": "billed_amount",
+ "fieldtype": "Currency",
+ "width": 100,
+ "options": "Company:company:default_currency"
+ },
+ {
+ "label": _("Returned Amount"),
+ "fieldname": "returned_amount",
+ "fieldtype": "Currency",
+ "width": 120,
+ "options": "Company:company:default_currency"
+ },
+ {
+ "label": _("Pending Amount"),
+ "fieldname": "pending_amount",
+ "fieldtype": "Currency",
+ "width": 120,
+ "options": "Company:company:default_currency"
+ },
+ {
+ "label": _("Item Name"),
+ "fieldname": "item_name",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Description"),
+ "fieldname": "description",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Project"),
+ "fieldname": "project",
+ "fieldtype": "Link",
+ "options": "Project",
+ "width": 120
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "width": 120
+ }
]
def get_args():
diff --git a/erpnext/controllers/sales_and_purchase_return.py b/erpnext/controllers/sales_and_purchase_return.py
index e11289d..5299b25 100644
--- a/erpnext/controllers/sales_and_purchase_return.py
+++ b/erpnext/controllers/sales_and_purchase_return.py
@@ -206,35 +206,26 @@
def get_returned_qty_map_for_row(row_name, doctype):
child_doctype = doctype + " Item"
reference_field = frappe.scrub(child_doctype) if doctype == "Purchase Receipt" else "dn_detail"
- reference_field = "child." + reference_field
- columns = ""
+
+ fields = [
+ "sum(abs(`tab{0}`.qty)) as qty".format(child_doctype),
+ "sum(abs(`tab{0}`.stock_qty)) as stock_qty".format(child_doctype)
+ ]
if doctype == "Purchase Receipt":
- columns += ", sum(abs(child.rejected_qty)) as rejected_qty, \
- sum(abs(child.received_qty)) as received_qty, \
- sum(abs(child.received_stock_qty)) as received_stock_qty"
+ fields += [
+ "sum(abs(`tab{0}`.rejected_qty)) as rejected_qty".format(child_doctype),
+ "sum(abs(`tab{0}`.received_qty)) as received_qty".format(child_doctype),
+ "sum(abs(`tab{0}`.received_stock_qty)) as received_stock_qty".format(child_doctype)
+ ]
- data = frappe.db.sql("""
- select
- sum(abs(child.qty)) as qty,
- sum(abs(child.stock_qty)) as stock_qty,
- %(columns)s
- from
- `tab{0}` child, `tab{1}` parent
- where
- child.parent = parent.name
- and parent.docstatus = 1
- and parent.is_return = 1
- and {2} = %(row_name)s
- """.format(child_doctype, doctype, reference_field),
- {
- "row_name": row_name,
- "columns": columns,
- "child_doctype": child_doctype,
- "doctype": doctype,
- "reference_field": reference_field
- },
- as_dict=1)
+ data = frappe.db.get_list(doctype,
+ fields = fields,
+ filters = [
+ [doctype, "docstatus", "=", 1],
+ [doctype, "is_return", "=", 1],
+ [child_doctype, reference_field, "=", row_name]
+ ])
return data[0]
diff --git a/erpnext/stock/doctype/delivery_note/delivery_note.json b/erpnext/stock/doctype/delivery_note/delivery_note.json
index 111e394..c9f8d08 100644
--- a/erpnext/stock/doctype/delivery_note/delivery_note.json
+++ b/erpnext/stock/doctype/delivery_note/delivery_note.json
@@ -1257,7 +1257,6 @@
"depends_on": "eval:!doc.__islocal",
"fieldname": "per_returned",
"fieldtype": "Percent",
- "in_list_view": 1,
"label": "% Returned",
"no_copy": 1,
"print_hide": 1,
@@ -1268,7 +1267,7 @@
"idx": 146,
"is_submittable": 1,
"links": [],
- "modified": "2020-11-19 11:22:09.056684",
+ "modified": "2020-11-30 12:54:45.407289",
"modified_by": "Administrator",
"module": "Stock",
"name": "Delivery Note",
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.json b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.json
index 749b131..5bb3095 100755
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.json
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.json
@@ -1110,7 +1110,6 @@
"depends_on": "eval:!doc.__islocal",
"fieldname": "per_returned",
"fieldtype": "Percent",
- "in_list_view": 1,
"label": "% Returned",
"no_copy": 1,
"print_hide": 1,
@@ -1121,7 +1120,7 @@
"idx": 261,
"is_submittable": 1,
"links": [],
- "modified": "2020-11-19 11:21:25.465966",
+ "modified": "2020-11-30 12:54:23.278500",
"modified_by": "Administrator",
"module": "Stock",
"name": "Purchase Receipt",
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
index 7e619bd..97e0fa7 100644
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
@@ -550,19 +550,17 @@
# Update Billing % based on pending accepted qty
total_amount, total_billed_amount = 0, 0
for item in pr_doc.items:
- returned_qty = frappe.db.sql("""
- select sum(abs(child.qty)) as qty
- from
- `tabPurchase Receipt Item` child,
- `tabPurchase Receipt` parent
- where
- child.parent = parent.name
- and parent.docstatus = 1
- and parent.is_return = 1
- and child.purchase_receipt_item = %(row_name)s
- """, {"row_name": item.name})
- returned_qty = returned_qty[0][0] if returned_qty else 0
+ return_data = frappe.db.get_list("Purchase Receipt",
+ fields = [
+ "sum(abs(`tabPurchase Receipt Item`.qty)) as qty"
+ ],
+ filters = [
+ ["Purchase Receipt", "docstatus", "=", 1],
+ ["Purchase Receipt", "is_return", "=", 1],
+ ["Purchase Receipt Item", "purchase_receipt_item", "=", item.name]
+ ])
+ returned_qty = return_data[0].qty if return_data else 0
returned_amount = flt(returned_qty) * flt(item.rate)
pending_amount = flt(item.amount) - returned_amount
total_billable_amount = pending_amount if item.billed_amt <= pending_amount else item.billed_amt