refactor: patch for fixing broken bins
fix(patch): delete fully broken bins
if bin doesn't have item_code or warehouse then it's not recoverable.
diff --git a/erpnext/patches/v13_0/add_bin_unique_constraint.py b/erpnext/patches/v13_0/add_bin_unique_constraint.py
index 29ae631..979fcf5 100644
--- a/erpnext/patches/v13_0/add_bin_unique_constraint.py
+++ b/erpnext/patches/v13_0/add_bin_unique_constraint.py
@@ -11,35 +11,47 @@
def execute():
+ delete_broken_bins()
+ delete_and_patch_duplicate_bins()
- duplicate_rows = frappe.db.sql("""
+def delete_broken_bins():
+ # delete useless bins
+ frappe.db.sql("delete from `tabBin` where item_code is null or warehouse is null")
+
+def delete_and_patch_duplicate_bins():
+
+ duplicate_bins = frappe.db.sql("""
SELECT
- item_code, warehouse
+ item_code, warehouse, count(*) as bin_count
FROM
tabBin
GROUP BY
item_code, warehouse
HAVING
- COUNT(*) > 1
+ bin_count > 1
""", as_dict=1)
- for row in duplicate_rows:
- bins = frappe.get_list("Bin",
- filters={"item_code": row.item_code,
- "warehouse": row.warehouse},
- fields=["name"],
- order_by="creation",
- )
+ for duplicate_bin in duplicate_bins:
+ existing_bins = frappe.get_list("Bin",
+ filters={
+ "item_code": duplicate_bin.item_code,
+ "warehouse": duplicate_bin.warehouse
+ },
+ fields=["name"],
+ order_by="creation",)
- for x in range(len(bins) - 1):
- frappe.delete_doc("Bin", bins[x].name)
+ # keep last one
+ existing_bins.pop()
+
+ for broken_bin in existing_bins:
+ frappe.delete_doc("Bin", broken_bin.name)
qty_dict = {
- "reserved_qty": get_reserved_qty(row.item_code, row.warehouse),
- "indented_qty": get_indented_qty(row.item_code, row.warehouse),
- "ordered_qty": get_ordered_qty(row.item_code, row.warehouse),
- "planned_qty": get_planned_qty(row.item_code, row.warehouse),
- "actual_qty": get_balance_qty_from_sle(row.item_code, row.warehouse)
+ "reserved_qty": get_reserved_qty(duplicate_bin.item_code, duplicate_bin.warehouse),
+ "indented_qty": get_indented_qty(duplicate_bin.item_code, duplicate_bin.warehouse),
+ "ordered_qty": get_ordered_qty(duplicate_bin.item_code, duplicate_bin.warehouse),
+ "planned_qty": get_planned_qty(duplicate_bin.item_code, duplicate_bin.warehouse),
+ "actual_qty": get_balance_qty_from_sle(duplicate_bin.item_code, duplicate_bin.warehouse)
}
- update_bin_qty(row.item_code, row.warehouse, qty_dict)
+ update_bin_qty(duplicate_bin.item_code, duplicate_bin.warehouse, qty_dict)