Merge pull request #31198 from HarryPaulo/gross-profit-monthly-schedule
feat: two new groupby mode on gross profit: Monthly, Payment Term
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.js b/erpnext/accounts/report/gross_profit/gross_profit.js
index 158ff4d..3d37b58 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.js
+++ b/erpnext/accounts/report/gross_profit/gross_profit.js
@@ -35,7 +35,7 @@
"fieldname":"group_by",
"label": __("Group By"),
"fieldtype": "Select",
- "options": "Invoice\nItem Code\nItem Group\nBrand\nWarehouse\nCustomer\nCustomer Group\nTerritory\nSales Person\nProject",
+ "options": "Invoice\nItem Code\nItem Group\nBrand\nWarehouse\nCustomer\nCustomer Group\nTerritory\nSales Person\nProject\nMonthly\nPayment Term",
"default": "Invoice"
},
],
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.py b/erpnext/accounts/report/gross_profit/gross_profit.py
index 9668992..526ea9d 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.py
+++ b/erpnext/accounts/report/gross_profit/gross_profit.py
@@ -4,7 +4,7 @@
import frappe
from frappe import _, scrub
-from frappe.utils import cint, flt
+from frappe.utils import cint, flt, formatdate
from erpnext.controllers.queries import get_match_cond
from erpnext.stock.utils import get_incoming_rate
@@ -124,6 +124,23 @@
"gross_profit",
"gross_profit_percent",
],
+ "monthly": [
+ "monthly",
+ "qty",
+ "base_rate",
+ "buying_rate",
+ "base_amount",
+ "buying_amount",
+ "gross_profit",
+ "gross_profit_percent",
+ ],
+ "payment_term": [
+ "payment_term",
+ "base_amount",
+ "buying_amount",
+ "gross_profit",
+ "gross_profit_percent",
+ ],
}
)
@@ -317,6 +334,19 @@
"options": "territory",
"width": 100,
},
+ "monthly": {
+ "label": _("Monthly"),
+ "fieldname": "monthly",
+ "fieldtype": "Data",
+ "width": 100,
+ },
+ "payment_term": {
+ "label": _("Payment Term"),
+ "fieldname": "payment_term",
+ "fieldtype": "Link",
+ "options": "Payment Term",
+ "width": 170,
+ },
}
)
@@ -390,6 +420,9 @@
buying_amount = 0
for row in reversed(self.si_list):
+ if self.filters.get("group_by") == "Monthly":
+ row.monthly = formatdate(row.posting_date, "MMM YYYY")
+
if self.skip_row(row):
continue
@@ -445,17 +478,7 @@
def get_average_rate_based_on_group_by(self):
for key in list(self.grouped):
- 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 += flt(row.qty)
- new_row.buying_amount += flt(row.buying_amount, self.currency_precision)
- new_row.base_amount += flt(row.base_amount, self.currency_precision)
- new_row = self.set_average_rate(new_row)
- self.grouped_data.append(new_row)
- else:
+ if self.filters.get("group_by") == "Invoice":
for i, row in enumerate(self.grouped[key]):
if row.indent == 1.0:
if (
@@ -469,6 +492,44 @@
if flt(row.qty) or row.base_amount:
row = self.set_average_rate(row)
self.grouped_data.append(row)
+ elif self.filters.get("group_by") == "Payment Term":
+ for i, row in enumerate(self.grouped[key]):
+ invoice_portion = 0
+
+ if row.is_return:
+ invoice_portion = 100
+ elif row.invoice_portion:
+ invoice_portion = row.invoice_portion
+ else:
+ invoice_portion = row.payment_amount * 100 / row.base_net_amount
+
+ if i == 0:
+ new_row = row
+ self.set_average_based_on_payment_term_portion(new_row, row, invoice_portion)
+ else:
+ new_row.qty += flt(row.qty)
+ self.set_average_based_on_payment_term_portion(new_row, row, invoice_portion, True)
+
+ new_row = self.set_average_rate(new_row)
+ self.grouped_data.append(new_row)
+ else:
+ for i, row in enumerate(self.grouped[key]):
+ if i == 0:
+ new_row = row
+ else:
+ new_row.qty += flt(row.qty)
+ new_row.buying_amount += flt(row.buying_amount, self.currency_precision)
+ new_row.base_amount += flt(row.base_amount, self.currency_precision)
+ new_row = self.set_average_rate(new_row)
+ self.grouped_data.append(new_row)
+
+ def set_average_based_on_payment_term_portion(self, new_row, row, invoice_portion, aggr=False):
+ cols = ["base_amount", "buying_amount", "gross_profit"]
+ for col in cols:
+ if aggr:
+ new_row[col] += row[col] * invoice_portion / 100
+ else:
+ new_row[col] = row[col] * invoice_portion / 100
def is_not_invoice_row(self, row):
return (self.filters.get("group_by") == "Invoice" and row.indent != 0.0) or self.filters.get(
@@ -622,6 +683,20 @@
sales_person_cols = ""
sales_team_table = ""
+ if self.filters.group_by == "Payment Term":
+ payment_term_cols = """,if(`tabSales Invoice`.is_return = 1,
+ '{0}',
+ coalesce(schedule.payment_term, '{1}')) as payment_term,
+ schedule.invoice_portion,
+ schedule.payment_amount """.format(
+ _("Sales Return"), _("No Terms")
+ )
+ payment_term_table = """ left join `tabPayment Schedule` schedule on schedule.parent = `tabSales Invoice`.name and
+ `tabSales Invoice`.is_return = 0 """
+ else:
+ payment_term_cols = ""
+ payment_term_table = ""
+
if self.filters.get("sales_invoice"):
conditions += " and `tabSales Invoice`.name = %(sales_invoice)s"
@@ -644,10 +719,12 @@
`tabSales Invoice Item`.name as "item_row", `tabSales Invoice`.is_return,
`tabSales Invoice Item`.cost_center
{sales_person_cols}
+ {payment_term_cols}
from
`tabSales Invoice` inner join `tabSales Invoice Item`
on `tabSales Invoice Item`.parent = `tabSales Invoice`.name
{sales_team_table}
+ {payment_term_table}
where
`tabSales Invoice`.docstatus=1 and `tabSales Invoice`.is_opening!='Yes' {conditions} {match_cond}
order by
@@ -655,6 +732,8 @@
conditions=conditions,
sales_person_cols=sales_person_cols,
sales_team_table=sales_team_table,
+ payment_term_cols=payment_term_cols,
+ payment_term_table=payment_term_table,
match_cond=get_match_cond("Sales Invoice"),
),
self.filters,