feat: process loss report, fix set pl query condition
diff --git a/erpnext/manufacturing/doctype/work_order/work_order.py b/erpnext/manufacturing/doctype/work_order/work_order.py
index c37a1c9..1cdbc5f 100644
--- a/erpnext/manufacturing/doctype/work_order/work_order.py
+++ b/erpnext/manufacturing/doctype/work_order/work_order.py
@@ -233,6 +233,7 @@
SELECT name FROM `tabStock Entry`
WHERE
work_order=%s
+ AND purpose='Manufacture'
AND docstatus=1
)
""", (self.name, ))[0][0])
diff --git a/erpnext/stock/report/process_loss_report/__init__.py b/erpnext/stock/report/process_loss_report/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/report/process_loss_report/__init__.py
diff --git a/erpnext/stock/report/process_loss_report/process_loss_report.js b/erpnext/stock/report/process_loss_report/process_loss_report.js
new file mode 100644
index 0000000..078b9e1
--- /dev/null
+++ b/erpnext/stock/report/process_loss_report/process_loss_report.js
@@ -0,0 +1,37 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Process Loss Report"] = {
+ filters: [
+ {
+ label: __("Company"),
+ fieldname: "company",
+ fieldtype: "Link",
+ options: "Company",
+ mandatory: true,
+ default: frappe.defaults.get_user_default("Company"),
+ },
+ {
+ label: __("Item"),
+ fieldname: "item",
+ fieldtype: "Link",
+ options: "Item",
+ mandatory: false,
+ },
+ {
+ label: __("From Date"),
+ fieldname: "from_date",
+ fieldtype: "Date",
+ mandatory: true,
+ default: frappe.datetime.year_start(),
+ },
+ {
+ label: __("To Date"),
+ fieldname: "to_date",
+ fieldtype: "Date",
+ mandatory: true,
+ default: frappe.datetime.get_today(),
+ },
+ ]
+};
diff --git a/erpnext/stock/report/process_loss_report/process_loss_report.json b/erpnext/stock/report/process_loss_report/process_loss_report.json
new file mode 100644
index 0000000..afe4aff
--- /dev/null
+++ b/erpnext/stock/report/process_loss_report/process_loss_report.json
@@ -0,0 +1,29 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-08-24 16:38:15.233395",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-08-24 16:38:15.233395",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Process Loss Report",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Work Order",
+ "report_name": "Process Loss Report",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Manufacturing User"
+ },
+ {
+ "role": "Stock User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/process_loss_report/process_loss_report.py b/erpnext/stock/report/process_loss_report/process_loss_report.py
new file mode 100644
index 0000000..be0f015
--- /dev/null
+++ b/erpnext/stock/report/process_loss_report/process_loss_report.py
@@ -0,0 +1,132 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import frappe
+from typing import Dict, List, Tuple
+
+Filters = frappe._dict
+Row = frappe._dict
+Data = List[Row]
+Columns = List[Dict[str, str]]
+QueryArgs = Dict[str, str]
+
+def execute(filters: Filters) -> Tuple[Columns, Data]:
+ columns = get_columns()
+ data = get_data(filters)
+ return columns, data
+
+def get_data(filters: Filters) -> Data:
+ query_args = get_query_args(filters)
+ data = run_query(query_args)
+ update_data_with_total_pl_value(data)
+ return data
+
+def get_columns() -> Columns:
+ return [
+ {
+ 'label': 'Work Order',
+ 'fieldname': 'name',
+ 'fieldtype': 'Link',
+ 'options': 'Work Order',
+ 'width': '200'
+ },
+ {
+ 'label': 'Item',
+ 'fieldname': 'production_item',
+ 'fieldtype': 'Link',
+ 'options': 'Item',
+ 'width': '100'
+ },
+ {
+ 'label': 'Status',
+ 'fieldname': 'status',
+ 'fieldtype': 'Data',
+ 'width': '100'
+ },
+ {
+ 'label': 'Qty To Manufacture',
+ 'fieldname': 'qty',
+ 'fieldtype': 'Float',
+ 'width': '150'
+ },
+ {
+ 'label': 'Manufactured Qty',
+ 'fieldname': 'produced_qty',
+ 'fieldtype': 'Float',
+ 'width': '150'
+ },
+ {
+ 'label': 'Process Loss Qty',
+ 'fieldname': 'process_loss_qty',
+ 'fieldtype': 'Float',
+ 'width': '150'
+ },
+ {
+ 'label': 'Actual Manufactured Qty',
+ 'fieldname': 'actual_produced_qty',
+ 'fieldtype': 'Float',
+ 'width': '150'
+ },
+ {
+ 'label': 'Total FG Value',
+ 'fieldname': 'total_fg_value',
+ 'fieldtype': 'Float',
+ 'width': '150'
+ },
+ {
+ 'label': 'Total Raw Material Value',
+ 'fieldname': 'total_rm_value',
+ 'fieldtype': 'Float',
+ 'width': '150'
+ },
+ {
+ 'label': 'Total Process Loss Value',
+ 'fieldname': 'total_pl_value',
+ 'fieldtype': 'Float',
+ 'width': '150'
+ },
+ ]
+
+def get_query_args(filters: Filters) -> QueryArgs:
+ query_args = {}
+ query_args.update(filters)
+ query_args.update(
+ get_filter_conditions(filters)
+ )
+ return query_args
+
+def run_query(query_args: QueryArgs) -> Data:
+ return frappe.db.sql("""
+ SELECT
+ wo.name, wo.status, wo.production_item, wo.qty,
+ wo.produced_qty, wo.process_loss_qty,
+ (wo.produced_qty - wo.process_loss_qty) as actual_produced_qty,
+ sum(se.total_incoming_value) as total_fg_value,
+ sum(se.total_outgoing_value) as total_rm_value
+ FROM
+ `tabWork Order` wo INNER JOIN `tabStock Entry` se
+ ON wo.name=se.work_order
+ WHERE
+ process_loss_qty > 0
+ AND wo.company = %(company)s
+ AND se.docstatus = 1
+ AND se.posting_date BETWEEN %(from_date)s AND %(to_date)s
+ %(item_filter)s
+ GROUP BY
+ se.work_order
+ """, query_args, as_dict=1)
+
+def update_data_with_total_pl_value(data: Data) -> None:
+ for row in data:
+ value_per_unit_fg = row['total_fg_value'] / row['actual_produced_qty']
+ row['total_pl_value'] = row['process_loss_qty'] * value_per_unit_fg
+
+def get_filter_conditions(filters: Filters) -> QueryArgs:
+ filter_conditions = dict(item_filter="")
+ if "item" in filters:
+ production_item = filters.get("item")
+ filter_conditions.update(
+ {"item_filter": f"wo.production_item='{production_item}'"}
+ )
+ return filter_conditions
+