Merge branch 'develop' into gross-profit-product-bundle
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.js b/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
index 1e37e4b..2dd3d69 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
@@ -443,7 +443,7 @@
this.frm.refresh_field("outstanding_amount");
this.frm.refresh_field("paid_amount");
this.frm.refresh_field("base_paid_amount");
- },
+ }
currency() {
this._super();
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.js b/erpnext/accounts/report/gross_profit/gross_profit.js
index ba17a94..5a6346c 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.js
+++ b/erpnext/accounts/report/gross_profit/gross_profit.js
@@ -36,5 +36,20 @@
"options": "Invoice\nItem Code\nItem Group\nBrand\nWarehouse\nCustomer\nCustomer Group\nTerritory\nSales Person\nProject",
"default": "Invoice"
},
- ]
+ ],
+ "tree": true,
+ "name_field": "parent",
+ "parent_field": "parent_invoice",
+ "initial_depth": 3,
+ "formatter": function(value, row, column, data, default_formatter) {
+ value = default_formatter(value, row, column, data);
+
+ if (data && !data.parent_invoice) {
+ value = $(`<span>${value}</span>`);
+ var $value = $(value).css("font-weight", "bold");
+ value = $value.wrap("<p></p>").parent().html();
+ }
+
+ return value;
+ },
}
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.py b/erpnext/accounts/report/gross_profit/gross_profit.py
index c949d9b..cdc929b 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.py
+++ b/erpnext/accounts/report/gross_profit/gross_profit.py
@@ -41,7 +41,27 @@
columns = get_columns(group_wise_columns, filters)
- for idx, src in enumerate(gross_profit_data.grouped_data):
+ if filters.group_by == 'Invoice':
+ column_names = get_column_names()
+
+ # to display item as Item Code: Item Name
+ columns[0] = 'Sales Invoice:Link/Item:300'
+ # removing Item Code and Item Name columns
+ del columns[4:6]
+
+ for src in gross_profit_data.si_list:
+ row = frappe._dict()
+ row.indent = src.indent
+ row.parent_invoice = src.parent_invoice
+ row.currency = filters.currency
+
+ for col in group_wise_columns.get(scrub(filters.group_by)):
+ row[column_names[col]] = src.get(col)
+
+ data.append(row)
+
+ else:
+ for idx, src in enumerate(gross_profit_data.grouped_data):
row = []
for col in group_wise_columns.get(scrub(filters.group_by)):
row.append(src.get(col))
@@ -50,7 +70,7 @@
if idx == len(gross_profit_data.grouped_data)-1:
row[0] = frappe.bold("Total")
data.append(row)
-
+
return columns, data
def get_columns(group_wise_columns, filters):
@@ -93,12 +113,38 @@
return columns
+def get_column_names():
+ return frappe._dict({
+ 'parent': 'sales_invoice',
+ 'customer': 'customer',
+ 'customer_group': 'customer_group',
+ 'posting_date': 'posting_date',
+ 'item_code': 'item_code',
+ 'item_name': 'item_name',
+ 'item_group': 'item_group',
+ 'brand': 'brand',
+ 'description': 'description',
+ 'warehouse': 'warehouse',
+ 'qty': 'qty',
+ 'base_rate': 'avg._selling_rate',
+ 'buying_rate': 'valuation_rate',
+ 'base_amount': 'selling_amount',
+ 'buying_amount': 'buying_amount',
+ 'gross_profit': 'gross_profit',
+ 'gross_profit_percent': 'gross_profit_%',
+ 'project': 'project'
+ })
+
class GrossProfitGenerator(object):
def __init__(self, filters=None):
self.data = []
self.average_buying_rate = {}
self.filters = frappe._dict(filters)
self.load_invoice_items()
+
+ if filters.group_by == 'Invoice':
+ self.group_items_by_invoice()
+
self.load_stock_ledger_entries()
self.load_product_bundle()
self.load_non_stock_items()
@@ -112,7 +158,12 @@
self.currency_precision = cint(frappe.db.get_default("currency_precision")) or 3
self.float_precision = cint(frappe.db.get_default("float_precision")) or 2
- for row in self.si_list:
+ grouped_by_invoice = True if self.filters.get("group_by") == "Invoice" else False
+
+ if grouped_by_invoice:
+ buying_amount = 0
+
+ for row in reversed(self.si_list):
if self.skip_row(row, self.product_bundles):
continue
@@ -134,12 +185,20 @@
row.buying_amount = flt(self.get_buying_amount(row, row.item_code),
self.currency_precision)
+ if grouped_by_invoice:
+ if row.indent == 1.0:
+ buying_amount += row.buying_amount
+ elif row.indent == 0.0:
+ row.buying_amount = buying_amount
+ buying_amount = 0
+
# get buying rate
- if row.qty:
- row.buying_rate = flt(row.buying_amount / row.qty, self.float_precision)
- row.base_rate = flt(row.base_amount / row.qty, self.float_precision)
+ if flt(row.qty):
+ row.buying_rate = flt(row.buying_amount / flt(row.qty), self.float_precision)
+ row.base_rate = flt(row.base_amount / flt(row.qty), self.float_precision)
else:
- row.buying_rate, row.base_rate = 0.0, 0.0
+ if self.is_not_invoice_row(row):
+ row.buying_rate, row.base_rate = 0.0, 0.0
# calculate gross profit
row.gross_profit = flt(row.base_amount - row.buying_amount, self.currency_precision)
@@ -171,7 +230,7 @@
if i==0:
new_row = row
else:
- new_row.qty += row.qty
+ 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)
@@ -183,16 +242,19 @@
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.qty += flt(returned_item_row.qty)
row.base_amount += flt(returned_item_row.base_amount, self.currency_precision)
- row.buying_amount = flt(row.qty * row.buying_rate, self.currency_precision)
- if row.qty or row.base_amount:
+ row.buying_amount = flt(flt(row.qty) * flt(row.buying_rate), self.currency_precision)
+ if (flt(row.qty) or row.base_amount) and self.is_not_invoice_row(row):
row = self.set_average_rate(row)
self.grouped_data.append(row)
self.add_to_totals(row)
self.set_average_gross_profit(self.totals)
self.grouped_data.append(self.totals)
+ def is_not_invoice_row(self, row):
+ return (self.filters.get("group_by") == "Invoice" and row.indent != 0.0) or self.filters.get("group_by") != "Invoice"
+
def set_average_rate(self, new_row):
self.set_average_gross_profit(new_row)
new_row.buying_rate = flt(new_row.buying_amount / new_row.qty, self.float_precision) if new_row.qty else 0
@@ -354,6 +416,107 @@
.format(conditions=conditions, sales_person_cols=sales_person_cols,
sales_team_table=sales_team_table, match_cond = get_match_cond('Sales Invoice')), self.filters, as_dict=1)
+ def group_items_by_invoice(self):
+ """
+ Turns list of Sales Invoice Items to a tree of Sales Invoices with their Items as children.
+ """
+
+ parents = []
+
+ for row in self.si_list:
+ if row.parent not in parents:
+ parents.append(row.parent)
+
+ parents_index = 0
+ for index, row in enumerate(self.si_list):
+ if parents_index < len(parents) and row.parent == parents[parents_index]:
+ invoice = frappe._dict({
+ 'parent_invoice': "",
+ 'indent': 0.0,
+ 'parent': row.parent,
+ 'posting_date': row.posting_date,
+ 'posting_time': row.posting_time,
+ 'project': row.project,
+ 'update_stock': row.update_stock,
+ 'customer': row.customer,
+ 'customer_group': row.customer_group,
+ 'item_code': None,
+ 'item_name': None,
+ 'description': None,
+ 'warehouse': None,
+ 'item_group': None,
+ 'brand': None,
+ 'dn_detail': None,
+ 'delivery_note': None,
+ 'qty': None,
+ 'item_row': None,
+ 'is_return': row.is_return,
+ 'cost_center': row.cost_center,
+ 'base_net_amount': frappe.db.get_value('Sales Invoice', row.parent, 'base_net_total')
+ })
+
+ self.si_list.insert(index, invoice)
+ parents_index += 1
+
+ else:
+ # skipping the bundle items rows
+ if not row.indent:
+ row.indent = 1.0
+ row.parent_invoice = row.parent
+ row.parent = row.item_code
+
+ # if not self.si_list[parents_index-1].base_net_amount:
+ # self.si_list[parents_index-1].base_net_amount = 0
+
+ # self.si_list[parents_index-1].base_net_amount += row.base_net_amount
+
+ if frappe.db.exists('Product Bundle', row.item_code):
+ self.add_bundle_items(row, index)
+
+ def add_bundle_items(self, product_bundle, index):
+ bundle_items = frappe.get_all(
+ 'Product Bundle Item',
+ filters = {
+ 'parent': product_bundle.item_code
+ },
+ fields = ['item_code', 'qty']
+ )
+
+ for i, item in enumerate(bundle_items):
+ item_name, description, item_group, brand = self.get_bundle_item_details(item.item_code)
+
+ bundle_item = frappe._dict({
+ 'parent_invoice': product_bundle.item_code,
+ 'indent': product_bundle.indent + 1,
+ 'parent': item.item_code,
+ 'posting_date': product_bundle.posting_date,
+ 'posting_time': product_bundle.posting_time,
+ 'project': product_bundle.project,
+ 'customer': product_bundle.customer,
+ 'customer_group': product_bundle.customer_group,
+ 'item_code': item.item_code,
+ 'item_name': item_name,
+ 'description': description,
+ 'warehouse': product_bundle.warehouse,
+ 'item_group': item_group,
+ 'brand': brand,
+ 'dn_detail': product_bundle.dn_detail,
+ 'delivery_note': product_bundle.delivery_note,
+ 'qty': (flt(product_bundle.qty) * flt(item.qty)),
+ 'item_row': None,
+ 'is_return': product_bundle.is_return,
+ 'cost_center': product_bundle.cost_center
+ })
+
+ self.si_list.insert((index+i+1), bundle_item)
+
+ def get_bundle_item_details(self, item_code):
+ return frappe.db.get_value(
+ 'Item',
+ item_code,
+ ['item_name', 'description', 'item_group', 'brand']
+ )
+
def load_stock_ledger_entries(self):
res = frappe.db.sql("""select item_code, voucher_type, voucher_no,
voucher_detail_no, stock_value, warehouse, actual_qty as qty
diff --git a/erpnext/selling/doctype/quotation/quotation.json b/erpnext/selling/doctype/quotation/quotation.json
index 3eba62b..6a638a3 100644
--- a/erpnext/selling/doctype/quotation/quotation.json
+++ b/erpnext/selling/doctype/quotation/quotation.json
@@ -84,6 +84,8 @@
"rounding_adjustment",
"rounded_total",
"in_words",
+ "bundle_items_section",
+ "packed_items",
"payment_schedule_section",
"payment_terms_template",
"payment_schedule",
@@ -926,6 +928,17 @@
"label": "Lost Reasons",
"options": "Quotation Lost Reason Detail",
"read_only": 1
+ },
+ {
+ "fieldname": "packed_items",
+ "fieldtype": "Table",
+ "label": "Bundle Items",
+ "options": "Packed Item"
+ },
+ {
+ "fieldname": "bundle_items_section",
+ "fieldtype": "Section Break",
+ "label": "Bundle Items"
}
],
"icon": "fa fa-shopping-cart",
@@ -933,7 +946,7 @@
"is_submittable": 1,
"links": [],
"max_attachments": 1,
- "modified": "2020-10-30 13:58:59.212060",
+ "modified": "2021-08-24 17:56:39.199033",
"modified_by": "Administrator",
"module": "Selling",
"name": "Quotation",
diff --git a/erpnext/selling/doctype/quotation/quotation.py b/erpnext/selling/doctype/quotation/quotation.py
index e4f8a47..6cd03dd 100644
--- a/erpnext/selling/doctype/quotation/quotation.py
+++ b/erpnext/selling/doctype/quotation/quotation.py
@@ -31,6 +31,9 @@
if self.items:
self.with_items = 1
+ from erpnext.stock.doctype.packed_item.packed_item import make_packing_list
+ make_packing_list(self)
+
def validate_valid_till(self):
if self.valid_till and getdate(self.valid_till) < getdate(self.transaction_date):
frappe.throw(_("Valid till date cannot be before transaction date"))
diff --git a/erpnext/selling/doctype/selling_settings/selling_settings.json b/erpnext/selling/doctype/selling_settings/selling_settings.json
index 717fd9b..531cf65 100644
--- a/erpnext/selling/doctype/selling_settings/selling_settings.json
+++ b/erpnext/selling/doctype/selling_settings/selling_settings.json
@@ -23,6 +23,7 @@
"maintain_same_rate_action",
"editable_price_list_rate",
"validate_selling_price",
+ "editable_bundle_item_rates",
"sales_transactions_settings_section",
"so_required",
"dn_required",
@@ -191,6 +192,12 @@
"fieldname": "sales_transactions_settings_section",
"fieldtype": "Section Break",
"label": "Transaction Settings"
+ },
+ {
+ "default": "0",
+ "fieldname": "editable_bundle_item_rates",
+ "fieldtype": "Check",
+ "label": "Calculate Product Bundle Price based on Child Items' Rates"
}
],
"icon": "fa fa-cog",
@@ -198,7 +205,7 @@
"index_web_pages_for_search": 1,
"issingle": 1,
"links": [],
- "modified": "2021-08-06 22:25:50.119458",
+ "modified": "2021-08-24 22:08:34.470897",
"modified_by": "Administrator",
"module": "Selling",
"name": "Selling Settings",
diff --git a/erpnext/selling/doctype/selling_settings/selling_settings.py b/erpnext/selling/doctype/selling_settings/selling_settings.py
index b219e7e..b54559a 100644
--- a/erpnext/selling/doctype/selling_settings/selling_settings.py
+++ b/erpnext/selling/doctype/selling_settings/selling_settings.py
@@ -15,6 +15,7 @@
class SellingSettings(Document):
def on_update(self):
self.toggle_hide_tax_id()
+ self.toggle_editable_rate_for_bundle_items()
def validate(self):
for key in ["cust_master_name", "campaign_naming_by", "customer_group", "territory",
@@ -33,6 +34,11 @@
make_property_setter(doctype, "tax_id", "hidden", self.hide_tax_id, "Check", validate_fields_for_doctype=False)
make_property_setter(doctype, "tax_id", "print_hide", self.hide_tax_id, "Check", validate_fields_for_doctype=False)
+ def toggle_editable_rate_for_bundle_items(self):
+ editable_bundle_item_rates = cint(self.editable_bundle_item_rates)
+
+ make_property_setter("Packed Item", "rate", "read_only", not(editable_bundle_item_rates), "Check", validate_fields_for_doctype=False)
+
def set_default_customer_group_and_territory(self):
if not self.customer_group:
self.customer_group = get_root_of('Customer Group')
diff --git a/erpnext/stock/doctype/packed_item/packed_item.json b/erpnext/stock/doctype/packed_item/packed_item.json
index bb396e8..00c175f 100644
--- a/erpnext/stock/doctype/packed_item/packed_item.json
+++ b/erpnext/stock/doctype/packed_item/packed_item.json
@@ -16,6 +16,7 @@
"conversion_factor",
"column_break_9",
"qty",
+ "rate",
"uom",
"section_break_9",
"serial_no",
@@ -215,13 +216,20 @@
"fieldname": "conversion_factor",
"fieldtype": "Float",
"label": "Conversion Factor"
+ },
+ {
+ "fetch_from": "item_code.valuation_rate",
+ "fieldname": "rate",
+ "fieldtype": "Currency",
+ "in_list_view": 1,
+ "label": "Rate"
}
],
"idx": 1,
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2021-05-26 07:08:05.111385",
+ "modified": "2021-08-20 23:31:05.393712",
"modified_by": "Administrator",
"module": "Stock",
"name": "Packed Item",
diff --git a/erpnext/stock/doctype/packed_item/packed_item.py b/erpnext/stock/doctype/packed_item/packed_item.py
index 4ab71bd..a42bde2 100644
--- a/erpnext/stock/doctype/packed_item/packed_item.py
+++ b/erpnext/stock/doctype/packed_item/packed_item.py
@@ -86,6 +86,9 @@
cleanup_packing_list(doc, parent_items)
+ if frappe.db.get_single_value("Selling Settings", "editable_bundle_item_rates"):
+ update_product_bundle_price(doc, parent_items)
+
def cleanup_packing_list(doc, parent_items):
"""Remove all those child items which are no longer present in main item table"""
delete_list = []
@@ -103,6 +106,45 @@
if d not in delete_list:
doc.append("packed_items", d)
+def update_product_bundle_price(doc, parent_items):
+ """Updates the prices of Product Bundles based on the rates of the Items in the bundle."""
+
+ parent_items_index = 0
+ bundle_price = 0
+ parent_items_doctype = doc.items[0].doctype
+
+ for bundle_item in doc.get("packed_items"):
+ if parent_items[parent_items_index][0] == bundle_item.parent_item:
+ bundle_price += bundle_item.qty * bundle_item.rate
+ else:
+ update_parent_item_price(doc, parent_items_doctype, parent_items[parent_items_index][0], bundle_price)
+
+ bundle_price = 0
+ parent_items_index += 1
+
+ # for the last product bundle
+ update_parent_item_price(doc, parent_items_doctype, parent_items[parent_items_index][0], bundle_price)
+ doc.reload()
+
+def update_parent_item_price(doc, parent_items_doctype, parent_item_code, bundle_price):
+ parent_item_doc_name = frappe.db.get_value(
+ parent_items_doctype,
+ {
+ 'parent': doc.name,
+ 'item_code': parent_item_code
+ },
+ 'name'
+ )
+
+ current_parent_item_price = frappe.db.get_value(parent_items_doctype, parent_item_doc_name, 'amount')
+ if current_parent_item_price != bundle_price:
+ frappe.db.set_value(parent_items_doctype, parent_item_doc_name, 'amount', bundle_price)
+ update_parent_item_rate(parent_items_doctype, parent_item_doc_name, bundle_price)
+
+def update_parent_item_rate(parent_items_doctype, parent_item_doc_name, bundle_price):
+ parent_item_qty = frappe.db.get_value(parent_items_doctype, parent_item_doc_name, 'qty')
+ frappe.db.set_value(parent_items_doctype, parent_item_doc_name, 'rate', (bundle_price/parent_item_qty))
+
@frappe.whitelist()
def get_items_from_product_bundle(args):
args = json.loads(args)