Gross profit report considering returned items (#10734)
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.py b/erpnext/accounts/report/gross_profit/gross_profit.py
index e2106e2..07f6979 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.py
+++ b/erpnext/accounts/report/gross_profit/gross_profit.py
@@ -17,7 +17,6 @@
gross_profit_data = GrossProfitGenerator(filters)
data = []
- source = gross_profit_data.grouped_data if filters.get("group_by") != "Invoice" else gross_profit_data.data
group_wise_columns = frappe._dict({
"invoice": ["parent", "customer", "customer_group", "posting_date","item_code", "item_name","item_group", "brand", "description", \
@@ -45,7 +44,7 @@
columns = get_columns(group_wise_columns, filters)
- for src in source:
+ for src in gross_profit_data.grouped_data:
row = []
for col in group_wise_columns.get(scrub(filters.group_by)):
row.append(src.get(col))
@@ -103,6 +102,7 @@
self.load_stock_ledger_entries()
self.load_product_bundle()
self.load_non_stock_items()
+ self.get_returned_invoice_items()
self.process()
def process(self):
@@ -143,40 +143,68 @@
row.gross_profit_percent = 0.0
# add to grouped
- if self.filters.group_by != "Invoice":
- self.grouped.setdefault(row.get(scrub(self.filters.group_by)), []).append(row)
-
- self.data.append(row)
+ self.grouped.setdefault(row.get(scrub(self.filters.group_by)), []).append(row)
if self.grouped:
self.get_average_rate_based_on_group_by()
- else:
- self.grouped_data = []
def get_average_rate_based_on_group_by(self):
# sum buying / selling totals for group
self.grouped_data = []
for key in self.grouped.keys():
- for i, row in enumerate(self.grouped[key]):
- if i==0:
- new_row = row
- else:
- new_row.qty += row.qty
- new_row.buying_amount += row.buying_amount
- new_row.base_amount += row.base_amount
+ if self.filters.get("group_by") != "Invoice":
+ for i, row in enumerate(self.grouped[key]):
+ if i==0:
+ new_row = row
+ else:
+ new_row.qty += row.qty
+ new_row.buying_amount += row.buying_amount
+ new_row.base_amount += row.base_amount
+ new_row = self.set_average_rate(new_row)
+ self.grouped_data.append(new_row)
+ else:
+ for i, row in enumerate(self.grouped[key]):
+ if row.parent in self.returned_invoices \
+ and row.item_code in self.returned_invoices[row.parent]:
+ returned_item_rows = self.returned_invoices[row.parent][row.item_code]
+ for returned_item_row in returned_item_rows:
+ row.qty += returned_item_row.qty
+ row.base_amount += returned_item_row.base_amount
+ row.buying_amount = row.qty * row.buying_rate
+ if row.qty:
+ row = self.set_average_rate(row)
+ self.grouped_data.append(row)
- new_row.gross_profit = new_row.base_amount - new_row.buying_amount
- new_row.gross_profit_percent = ((new_row.gross_profit / new_row.base_amount) * 100.0) \
- if new_row.base_amount else 0
- new_row.buying_rate = (new_row.buying_amount / new_row.qty) \
- if new_row.qty else 0
- new_row.base_rate = (new_row.base_amount / new_row.qty) \
- if new_row.qty else 0
+ def set_average_rate(self, new_row):
+ new_row.gross_profit = new_row.base_amount - new_row.buying_amount
+ new_row.gross_profit_percent = ((new_row.gross_profit / new_row.base_amount) * 100.0) \
+ if new_row.base_amount else 0
+ new_row.buying_rate = (new_row.buying_amount / new_row.qty) if new_row.qty else 0
+ new_row.base_rate = (new_row.base_amount / new_row.qty) if new_row.qty else 0
+ return new_row
- self.grouped_data.append(new_row)
+ def get_returned_invoice_items(self):
+ returned_invoices = frappe.db.sql("""
+ select
+ si.name, si_item.item_code, si_item.qty, si_item.base_amount, si.return_against
+ from
+ `tabSales Invoice` si, `tabSales Invoice Item` si_item
+ where
+ si.name = si_item.parent
+ and si.docstatus = 1
+ and si.is_return = 1
+ """, as_dict=1)
+
+ self.returned_invoices = frappe._dict()
+ for inv in returned_invoices:
+ self.returned_invoices.setdefault(inv.return_against, frappe._dict())\
+ .setdefault(inv.item_code, []).append(inv)
def skip_row(self, row, product_bundles):
- if self.filters.get("group_by") != "Invoice" and not row.get(scrub(self.filters.get("group_by"))):
+ if self.filters.get("group_by") != "Invoice":
+ if not row.get(scrub(self.filters.get("group_by"))):
+ return True
+ elif row.get("is_return") == 1:
return True
def get_buying_amount_from_product_bundle(self, row, product_bundle):
@@ -268,20 +296,26 @@
sales_person_cols = ""
sales_team_table = ""
- self.si_list = frappe.db.sql("""select `tabSales Invoice Item`.parenttype, `tabSales Invoice Item`.parent,
- `tabSales Invoice`.posting_date, `tabSales Invoice`.posting_time, `tabSales Invoice`.project, `tabSales Invoice`.update_stock,
- `tabSales Invoice`.customer, `tabSales Invoice`.customer_group, `tabSales Invoice`.territory,
- `tabSales Invoice Item`.item_code, `tabSales Invoice Item`.item_name, `tabSales Invoice Item`.description,
- `tabSales Invoice Item`.warehouse, `tabSales Invoice Item`.item_group, `tabSales Invoice Item`.brand,
- `tabSales Invoice Item`.dn_detail, `tabSales Invoice Item`.delivery_note, `tabSales Invoice Item`.stock_qty as qty,
- `tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.base_net_amount, `tabSales Invoice Item`.name as "item_row"
+ self.si_list = frappe.db.sql("""
+ select
+ `tabSales Invoice Item`.parenttype, `tabSales Invoice Item`.parent,
+ `tabSales Invoice`.posting_date, `tabSales Invoice`.posting_time,
+ `tabSales Invoice`.project, `tabSales Invoice`.update_stock,
+ `tabSales Invoice`.customer, `tabSales Invoice`.customer_group,
+ `tabSales Invoice`.territory, `tabSales Invoice Item`.item_code,
+ `tabSales Invoice Item`.item_name, `tabSales Invoice Item`.description,
+ `tabSales Invoice Item`.warehouse, `tabSales Invoice Item`.item_group,
+ `tabSales Invoice Item`.brand, `tabSales Invoice Item`.dn_detail,
+ `tabSales Invoice Item`.delivery_note, `tabSales Invoice Item`.stock_qty as qty,
+ `tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.base_net_amount,
+ `tabSales Invoice Item`.name as "item_row", `tabSales Invoice`.is_return
{sales_person_cols}
from
- `tabSales Invoice`
- inner join `tabSales Invoice Item` on `tabSales Invoice Item`.parent = `tabSales Invoice`.name
+ `tabSales Invoice` inner join `tabSales Invoice Item`
+ on `tabSales Invoice Item`.parent = `tabSales Invoice`.name
{sales_team_table}
where
- `tabSales Invoice`.docstatus = 1 {conditions} {match_cond}
+ `tabSales Invoice`.docstatus=1 {conditions} {match_cond}
order by
`tabSales Invoice`.posting_date desc, `tabSales Invoice`.posting_time desc"""
.format(conditions=conditions, sales_person_cols=sales_person_cols,