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, 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)