def recalculate_bin_qty(self, new_name):
from erpnext.stock.stock_balance import repost_stock
frappe.db.auto_commit_on_many_writes = 1
existing_allow_negative_stock = frappe.db.get_value("Stock Settings", None, "allow_negative_stock")
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", 1)
for warehouse in frappe.db.sql("select warehouse from `tabBin` where item_code=%s", new_name):
repost_stock(new_name, warehouse[0])
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", existing_allow_negative_stock)
frappe.db.auto_commit_on_many_writes = 0
def execute():
bins = frappe.db.sql("""select item_code, warehouse, count(*) from `tabBin`
group by item_code, warehouse having count(*) > 1""", as_dict=True)
for d in bins:
try:
frappe.db.sql("delete from tabBin where item_code=%s and warehouse=%s", (d.item_code, d.warehouse))
repost_stock(d.item_code, d.warehouse, allow_zero_rate=True, only_actual=False, only_bin=True)
frappe.db.commit()
except:
frappe.db.rollback()
def execute():
frappe.reload_doc('manufacturing', 'doctype', 'production_order_item')
frappe.reload_doc('manufacturing', 'doctype', 'production_order')
modified_items = frappe.db.sql_list("""
select name from `tabItem`
where is_stock_item=1 and modified >= '2016-10-31'
""")
if not modified_items:
return
item_warehouses_with_transactions = []
transactions = ("Sales Order Item", "Material Request Item",
"Purchase Order Item", "Stock Ledger Entry", "Packed Item")
for doctype in transactions:
item_warehouses_with_transactions += list(
frappe.db.sql(
"""
select distinct item_code, warehouse
from `tab{0}` where docstatus=1 and item_code in ({1})""".format(
doctype, ', '.join(['%s'] * len(modified_items))),
tuple(modified_items)))
item_warehouses_with_transactions += list(
frappe.db.sql(
"""
select distinct production_item, fg_warehouse
from `tabProduction Order` where docstatus=1 and production_item in ({0})""".
format(', '.join(['%s'] * len(modified_items))),
tuple(modified_items)))
item_warehouses_with_transactions += list(
frappe.db.sql(
"""
select distinct pr_item.item_code, pr.source_warehouse
from `tabProduction Order` pr, `tabProduction Order Item` pr_item
where pr_item.parent and pr.name and pr.docstatus=1 and pr_item.item_code in ({0})"""
.format(', '.join(['%s'] * len(modified_items))),
tuple(modified_items)))
item_warehouses_with_bin = list(
frappe.db.sql("select distinct item_code, warehouse from `tabBin`"))
item_warehouses_with_missing_bin = list(
set(item_warehouses_with_transactions) - set(item_warehouses_with_bin))
for item_code, warehouse in item_warehouses_with_missing_bin:
repost_stock(item_code, warehouse)
def recalculate_bin_qty(self, newdn):
from erpnext.stock.stock_balance import repost_stock
frappe.db.auto_commit_on_many_writes = 1
existing_allow_negative_stock = frappe.db.get_value("Stock Settings", None, "allow_negative_stock")
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", 1)
for item in frappe.db.sql("""select distinct item_code from (
select name as item_code from `tabItem` where is_stock_item=1
union
select distinct item_code from tabBin) a"""):
repost_stock(item[0], newdn)
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", existing_allow_negative_stock)
frappe.db.auto_commit_on_many_writes = 0
def recalculate_bin_qty(self, newdn):
from erpnext.stock.stock_balance import repost_stock
frappe.db.auto_commit_on_many_writes = 1
existing_allow_negative_stock = frappe.db.get_value("Stock Settings", None, "allow_negative_stock")
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", 1)
for item in frappe.db.sql("""select distinct item_code from (
select name as item_code from `tabItem` where is_stock_item=1
union
select distinct item_code from tabBin) a"""):
repost_stock(item[0], newdn)
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", existing_allow_negative_stock)
frappe.db.auto_commit_on_many_writes = 0
def recalculate_bin_qty(self, new_name):
from erpnext.stock.stock_balance import repost_stock
frappe.db.auto_commit_on_many_writes = 1
existing_allow_negative_stock = frappe.db.get_value("Stock Settings", None, "allow_negative_stock")
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", 1)
repost_stock_for_warehouses = frappe.db.sql_list("""select distinct warehouse
from tabBin where item_code=%s""", new_name)
# Delete all existing bins to avoid duplicate bins for the same item and warehouse
frappe.db.sql("delete from `tabBin` where item_code=%s", new_name)
for warehouse in repost_stock_for_warehouses:
repost_stock(new_name, warehouse)
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", existing_allow_negative_stock)
frappe.db.auto_commit_on_many_writes = 0
def recalculate_bin_qty(self, new_name):
from erpnext.stock.stock_balance import repost_stock
frappe.db.auto_commit_on_many_writes = 1
existing_allow_negative_stock = frappe.db.get_value("Stock Settings", None, "allow_negative_stock")
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", 1)
repost_stock_for_warehouses = frappe.db.sql_list("""select distinct warehouse
from tabBin where item_code=%s""", new_name)
# Delete all existing bins to avoid duplicate bins for the same item and warehouse
frappe.db.sql("delete from `tabBin` where item_code=%s", new_name)
for warehouse in repost_stock_for_warehouses:
repost_stock(new_name, warehouse)
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", existing_allow_negative_stock)
frappe.db.auto_commit_on_many_writes = 0
def execute():
bins = frappe.db.sql(
"""select item_code, warehouse, count(*) from `tabBin`
group by item_code, warehouse having count(*) > 1""",
as_dict=True)
for d in bins:
try:
frappe.db.sql(
"delete from tabBin where item_code=%s and warehouse=%s",
(d.item_code, d.warehouse))
repost_stock(d.item_code,
d.warehouse,
allow_zero_rate=True,
only_actual=False,
only_bin=True)
frappe.db.commit()
except:
frappe.db.rollback()
def execute():
frappe.reload_doc('manufacturing', 'doctype', 'work_order_item')
frappe.reload_doc('manufacturing', 'doctype', 'work_order')
modified_items = frappe.db.sql_list("""
select name from `tabItem`
where is_stock_item=1 and modified >= '2016-10-31'
""")
if not modified_items:
return
item_warehouses_with_transactions = []
transactions = ("Sales Order Item", "Material Request Item", "Purchase Order Item",
"Stock Ledger Entry", "Packed Item")
for doctype in transactions:
item_warehouses_with_transactions += list(frappe.db.sql("""
select distinct item_code, warehouse
from `tab{0}` where docstatus=1 and item_code in ({1})"""
.format(doctype, ', '.join(['%s']*len(modified_items))), tuple(modified_items)))
item_warehouses_with_transactions += list(frappe.db.sql("""
select distinct production_item, fg_warehouse
from `tabWork Order` where docstatus=1 and production_item in ({0})"""
.format(', '.join(['%s']*len(modified_items))), tuple(modified_items)))
item_warehouses_with_transactions += list(frappe.db.sql("""
select distinct pr_item.item_code, pr_item.source_warehouse
from `tabWork Order` pr, `tabWork Order Item` pr_item
where pr_item.parent and pr.name and pr.docstatus=1 and pr_item.item_code in ({0})"""
.format(', '.join(['%s']*len(modified_items))), tuple(modified_items)))
item_warehouses_with_bin = list(frappe.db.sql("select distinct item_code, warehouse from `tabBin`"))
item_warehouses_with_missing_bin = list(
set(item_warehouses_with_transactions) - set(item_warehouses_with_bin))
for item_code, warehouse in item_warehouses_with_missing_bin:
repost_stock(item_code, warehouse)
def recalculate_bin_qty(self, new_name):
from erpnext.stock.stock_balance import repost_stock
existing_allow_negative_stock = frappe.db.get_value(
"Stock Settings", None, "allow_negative_stock")
frappe.db.set_value("Stock Settings", None, "allow_negative_stock", 1)
repost_stock_for_warehouses = frappe.get_all(
"Stock Ledger Entry",
"warehouse",
filters={"item_code": new_name},
pluck="warehouse",
distinct=True,
)
# Delete all existing bins to avoid duplicate bins for the same item and warehouse
frappe.db.delete("Bin", {"item_code": new_name})
for warehouse in repost_stock_for_warehouses:
repost_stock(new_name, warehouse)
frappe.db.set_value("Stock Settings", None, "allow_negative_stock",
existing_allow_negative_stock)
