def get_items(warehouse, posting_date, posting_time):
items = frappe.get_list("Bin", fields=["item_code"], filters={"warehouse": warehouse}, as_list=1)
items += frappe.get_list("Item", fields=["name"], filters= {"is_stock_item": 1, "has_serial_no": 0,
"has_batch_no": 0, "has_variants": 0, "disabled": 0, "default_warehouse": warehouse},
as_list=1)
res = []
for item in set(items):
stock_bal = get_stock_balance(item[0], warehouse, posting_date, posting_time,
with_valuation_rate=True)
if frappe.db.get_value("Item",item[0],"disabled") == 0:
res.append({
"item_code": item[0],
"warehouse": warehouse,
"qty": stock_bal[0],
"item_name": frappe.db.get_value('Item', item[0], 'item_name'),
"valuation_rate": stock_bal[1],
"current_qty": stock_bal[0],
"current_valuation_rate": stock_bal[1]
})
return res
def get_stock_balance_for(item_code,
warehouse,
posting_date,
posting_time,
batch_no=None,
with_valuation_rate=True):
frappe.has_permission("Stock Reconciliation", "write", throw=True)
item_dict = frappe.db.get_value("Item",
item_code,
["has_serial_no", "has_batch_no"],
as_dict=1)
serial_nos = ""
if item_dict.get("has_serial_no"):
qty, rate, serial_nos = get_qty_rate_for_serial_nos(
item_code, warehouse, posting_date, posting_time, item_dict)
else:
qty, rate = get_stock_balance(item_code,
warehouse,
posting_date,
posting_time,
with_valuation_rate=with_valuation_rate)
if item_dict.get("has_batch_no"):
qty = get_batch_qty(batch_no, warehouse) or 0
return {'qty': qty, 'rate': rate, 'serial_nos': serial_nos}
def get_items(warehouse, posting_date, posting_time):
lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
items = frappe.db.sql("""select item_code, warehouse from tabBin
where exists(select name from `tabWarehouse` where lft >= %s and rgt <= %s and name=`tabBin`.warehouse)
""", (lft, rgt))
items += frappe.db.sql("""select name, default_warehouse from tabItem
where exists(select name from `tabWarehouse` where lft >= %s and rgt <= %s and name=`tabItem`.default_warehouse)
and is_stock_item = 1 and has_serial_no = 0 and has_batch_no = 0 and has_variants = 0 and disabled = 0
""", (lft, rgt))
res = []
for item, wh in set(items):
stock_bal = get_stock_balance(item, wh, posting_date, posting_time,
with_valuation_rate=True)
if frappe.db.get_value("Item", item, "disabled") == 0:
res.append({
"item_code": item,
"warehouse": wh,
"qty": stock_bal[0],
"item_name": frappe.db.get_value('Item', item, 'item_name'),
"valuation_rate": stock_bal[1],
"current_qty": stock_bal[0],
"current_valuation_rate": stock_bal[1]
})
return res
def validate_data(self):
def _get_msg(row_num, msg):
return _("Row # {0}: ").format(row_num + 1) + msg
self.validation_messages = []
item_warehouse_combinations = []
default_currency = frappe.db.get_default("currency")
# validate no of rows
if len(self.items) > 100:
frappe.throw(_("""Max 100 rows for Stock Reconciliation."""))
for row_num, row in enumerate(self.items):
# find duplicates
if [row.item_code, row.warehouse] in item_warehouse_combinations:
self.validation_messages.append(_get_msg(row_num, _("Duplicate entry")))
else:
item_warehouse_combinations.append([row.item_code, row.warehouse])
self.validate_item(row.item_code, row_num + 1)
# validate warehouse
if not frappe.db.get_value("Warehouse", row.warehouse):
self.validation_messages.append(_get_msg(row_num, _("Warehouse not found in the system")))
# if both not specified
if row.qty in ["", None] and row.valuation_rate in ["", None]:
self.validation_messages.append(
_get_msg(row_num, _("Please specify either Quantity or Valuation Rate or both"))
)
# do not allow negative quantity
if flt(row.qty) < 0:
self.validation_messages.append(_get_msg(row_num, _("Negative Quantity is not allowed")))
# do not allow negative valuation
if flt(row.valuation_rate) < 0:
self.validation_messages.append(_get_msg(row_num, _("Negative Valuation Rate is not allowed")))
if row.qty and not row.valuation_rate:
row.valuation_rate = get_stock_balance(
row.item_code, row.warehouse, self.posting_date, self.posting_time, with_valuation_rate=True
)[1]
if not row.valuation_rate:
# try if there is a buying price list in default currency
buying_rate = frappe.db.get_value(
"Item Price",
{"item_code": row.item_code, "buying": 1, "currency": default_currency},
"price_list_rate",
)
if buying_rate:
row.valuation_rate = buying_rate
# throw all validation messages
if self.validation_messages:
for msg in self.validation_messages:
msgprint(msg)
raise frappe.ValidationError(self.validation_messages)
def get_ordered_putaway_rules(item_code, company, source_warehouse=None):
"""Returns an ordered list of putaway rules to apply on an item."""
filters = {
"item_code": item_code,
"company": company,
"disable": 0
}
if source_warehouse:
filters.update({"warehouse": ["!=", source_warehouse]})
rules = frappe.get_all("Putaway Rule",
fields=["name", "item_code", "stock_capacity", "priority", "warehouse"],
filters=filters,
order_by="priority asc, capacity desc")
if not rules:
return False, None
vacant_rules = []
for rule in rules:
balance_qty = get_stock_balance(rule.item_code, rule.warehouse, nowdate())
free_space = flt(rule.stock_capacity) - flt(balance_qty)
if free_space > 0:
rule["free_space"] = free_space
vacant_rules.append(rule)
if not vacant_rules:
# After iterating through rules, if no rules are left
# then there is not enough space left in any rule
return True, None
vacant_rules = sorted(vacant_rules, key = lambda i: (i['priority'], -i['free_space']))
return False, vacant_rules
def issue_existing_serial_and_batch(self, sl_entries, serialized_items=[]):
from erpnext.stock.stock_ledger import get_stock_ledger_entries
for row in self.items:
if row.item_code not in serialized_items: continue
serial_nos = get_serial_nos(row.serial_no) or []
# To issue existing serial nos
if row.current_qty and (row.current_serial_no):
args = self.get_sle_for_items(row)
args.update({
'actual_qty': -1 * row.current_qty,
'serial_no': row.current_serial_no,
'batch_no': row.batch_no,
'valuation_rate': row.current_valuation_rate
})
if row.current_serial_no:
args.update({
'qty_after_transaction': 0,
})
sl_entries.append(args)
qty_after_transaction = 0
for serial_no in serial_nos:
args = self.get_sle_for_items(row, [serial_no])
previous_sle = get_stock_ledger_entries({
"item_code": row.item_code,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
"serial_no": serial_no
}, "<", "desc", "limit 1")
previous_sle = previous_sle and previous_sle[0] or {}
if previous_sle and row.warehouse != previous_sle.get("warehouse"):
# If serial no exists in different warehouse
warehouse = previous_sle.get("warehouse", '') or row.warehouse
if not qty_after_transaction:
qty_after_transaction = get_stock_balance(row.item_code,
warehouse, self.posting_date, self.posting_time)
qty_after_transaction -= 1
new_args = args.copy()
new_args.update({
'actual_qty': -1,
'qty_after_transaction': qty_after_transaction,
'warehouse': warehouse,
'valuation_rate': previous_sle.get("valuation_rate")
})
sl_entries.append(new_args)
def validate_data(self):
def _get_msg(row_num, msg):
return _("Row # {0}: ").format(row_num+1) + msg
self.validation_messages = []
item_warehouse_combinations = []
default_currency = frappe.db.get_default("currency")
for row_num, row in enumerate(self.items):
# find duplicates
if [row.item_code, row.warehouse] in item_warehouse_combinations:
self.validation_messages.append(_get_msg(row_num, _("Duplicate entry")))
else:
item_warehouse_combinations.append([row.item_code, row.warehouse])
self.validate_item(row.item_code, row_num+1)
# validate warehouse
if not frappe.db.get_value("Warehouse", row.warehouse):
self.validation_messages.append(_get_msg(row_num, _("Warehouse not found in the system")))
# if both not specified
if row.qty in ["", None] and row.valuation_rate in ["", None]:
self.validation_messages.append(_get_msg(row_num,
_("Please specify either Quantity or Valuation Rate or both")))
# do not allow negative quantity
if flt(row.qty) < 0:
self.validation_messages.append(_get_msg(row_num,
_("Negative Quantity is not allowed")))
# do not allow negative valuation
if flt(row.valuation_rate) < 0:
self.validation_messages.append(_get_msg(row_num,
_("Negative Valuation Rate is not allowed")))
if row.qty and not row.valuation_rate:
row.valuation_rate = get_stock_balance(row.item_code, row.warehouse,
self.posting_date, self.posting_time, with_valuation_rate=True)[1]
if not row.valuation_rate:
# try if there is a buying price list in default currency
buying_rate = frappe.db.get_value("Item Price", {"item_code": row.item_code,
"buying": 1, "currency": default_currency}, "price_list_rate")
if buying_rate:
row.valuation_rate = buying_rate
else:
# get valuation rate from Item
row.valuation_rate = frappe.get_value('Item', row.item_code, 'valuation_rate')
# throw all validation messages
if self.validation_messages:
for msg in self.validation_messages:
msgprint(msg)
raise frappe.ValidationError(self.validation_messages)
def get_items(warehouse, posting_date, posting_time, as_dict = 0, as_list = 0):
items = frappe.get_list("Bin", fields=["item_code"], filters={"warehouse": warehouse}, as_list=1)
items += frappe.get_list("Item", fields=["name"], filters= {"is_stock_item": 1, "has_serial_no": 0,
"has_batch_no": 0, "has_variants": 0, "disabled": 0, "default_warehouse": warehouse},
as_list=1)
res = []
for item in set(items):
stock_bal = get_stock_balance(item[0], warehouse, posting_date, posting_time,
with_valuation_rate=True)
#JDLP - 2017-01-30, ajout de batch
if frappe.db.get_value("Item",item[0],"disabled") == 0 and frappe.db.get_value("Item",item[0],"has_batch_no") == 0:
if as_dict:
res.append({
"item_code": item[0],
"warehouse": warehouse,
"qty": stock_bal[0],
"item_name": frappe.db.get_value('Item', item[0], 'item_name'),
"valuation_rate": stock_bal[1],
"current_qty": stock_bal[0],
"current_valuation_rate": stock_bal[1],
"batch_no": ""
})
if as_list:
item_attributes = frappe.get_all("Item Variant Attribute",{"parent":item[0]},["attribute","attribute_value"])
species = ""
construction = ""
flooring_grade = ""
flooring_width = ""
thickness = ""
if item_attributes:
if print_debug: frappe.errprint("item_attributes : " + cstr(item_attributes))
for attribute in item_attributes:
if attribute.attribute == "Essence" : species = attribute.attribute_value
if attribute.attribute == "Hardwood Construction" : construction = attribute.attribute_value
if attribute.attribute == "Flooring Grade" : flooring_grade = attribute.attribute_value
if attribute.attribute == "Flooring Width" : flooring_width = attribute.attribute_value
if attribute.attribute == "Flooring Thickness" : thickness = attribute.attribute_value
res.append([
item[0],
"",
stock_bal[0],
stock_bal[1],
species,
construction,
flooring_grade,
flooring_width,
thickness,
"",
"",
"",
""
])
return res
def validate_item_balance(self):
# able to delete new Plants
source_warehouse = self.get_source_warehouse()
item = frappe.get_doc("Item", self.get("item_code"))
qty = get_stock_balance(item.item_code, source_warehouse)
if qty < self.qty:
frappe.throw("The provided quantity <strong>{0}</strong> exceeds stock balance in warehouse {1}. "
"Stock balance remaining <strong>{2}</strong>".format(self.qty, source_warehouse, qty))
def get_product_bundle_rate(self,row,product_bundle):
#Get the valuation rates of the component items
pack = []
bundle_item = frappe.get_doc("Product Bundle",row.item_code)
for each in bundle_item.items:
item_value = get_stock_balance(item_code=each.item_code,warehouse=row.warehouse,posting_date=row.posting_date, with_valuation_rate = True)[1]*flt(each.qty)
pack.append(item_value*row.qty)
print(f"\n\n\nValue of component {each.item_code}:{item_value}")
return(sum(pack))
def _changed(item): qty, rate = get_stock_balance(item.item_code, item.warehouse, self.posting_date, self.posting_time, with_valuation_rate=True) if (item.qty==None or item.qty==qty) and (item.valuation_rate==None or item.valuation_rate==rate): return False else: item.current_qty = qty item.current_valuation_rate = rate self.difference_amount += (flt(item.qty or qty) * flt(item.valuation_rate or rate) - (flt(qty) * flt(rate))) return True
def get_stock_balance_for(item_code, warehouse, posting_date, posting_time):
frappe.has_permission("Stock Reconciliation", "write", throw = True)
qty, rate = get_stock_balance(item_code, warehouse,
posting_date, posting_time, with_valuation_rate=True)
return {
'qty': qty,
'rate': rate
}
def has_item_modified(self, item):
qty = get_stock_balance(item.item_code, self.target_warehouse)
if item.item_group == "Flower":
origin_qty = self.flower
elif item.item_group == "Other Plant Material":
origin_qty = self.other_material
else:
origin_qty = self.waste
return flt(origin_qty) != flt(qty)
def _changed(item): qty, rate = get_stock_balance(item.item_code, item.warehouse, self.posting_date, self.posting_time, with_valuation_rate=True) if (item.qty==None or item.qty==qty) and (item.valuation_rate==None or item.valuation_rate==rate): return False else: item.current_qty = qty item.current_valuation_rate = rate self.difference_amount += (flt(item.qty or qty) * flt(item.valuation_rate or rate) - (flt(qty) * flt(rate))) return True
def get_stock_balance_for(item_code, warehouse, posting_date, posting_time):
frappe.has_permission("Stock Reconciliation", "write", throw=True)
qty, rate = get_stock_balance(item_code,
warehouse,
posting_date,
posting_time,
with_valuation_rate=True)
return {'qty': qty, 'rate': rate}
def validate_capacity(self):
stock_uom = frappe.db.get_value("Item", self.item_code, "stock_uom")
balance_qty = get_stock_balance(self.item_code, self.warehouse, nowdate())
if flt(self.stock_capacity) < flt(balance_qty):
frappe.throw(_("Warehouse Capacity for Item '{0}' must be greater than the existing stock level of {1} {2}.")
.format(self.item_code, frappe.bold(balance_qty), stock_uom),
title=_("Insufficient Capacity"))
if not self.capacity:
frappe.throw(_("Capacity must be greater than 0"), title=_("Invalid"))
def get_items(warehouse, posting_date, posting_time):
items = frappe.get_list("Item", fields=["name"], filters=
{"is_stock_item": 1, "has_serial_no": 0, "has_batch_no": 0})
for item in items:
item.item_code = item.name
item.warehouse = warehouse
item.qty, item.valuation_rate = get_stock_balance(item.name, warehouse,
posting_date, posting_time, with_valuation_rate=True)
item.current_qty = item.qty
item.current_valuation_rate = item.valuation_rate
del item["name"]
return items
def get_stock_balance_for(item_code, warehouse, posting_date, posting_time, batch_no = None):
frappe.has_permission("Stock Reconciliation", "write", throw = True)
qty, rate = get_stock_balance(item_code, warehouse,
posting_date, posting_time, with_valuation_rate=True, batch_no=batch_no)
if batch_no:
qty = get_item_warehouse_batch_actual_qty(item_code, warehouse, batch_no, posting_date, posting_time)
return {
'qty': qty,
'rate': rate
}
def get_items(warehouse, posting_date, posting_time):
items = frappe.get_list("Item", fields=["name"], filters=
{"is_stock_item": 1, "has_serial_no": 0, "has_batch_no": 0, "has_variants": 0})
for item in items:
item.item_code = item.name
item.warehouse = warehouse
item.qty, item.valuation_rate = get_stock_balance(item.name, warehouse,
posting_date, posting_time, with_valuation_rate=True)
item.current_qty = item.qty
item.current_valuation_rate = item.valuation_rate
del item["name"]
return items
def get_stock_balance_for(
item_code: str,
warehouse: str,
posting_date: str,
posting_time: str,
batch_no: Optional[str] = None,
with_valuation_rate: bool = True,
):
frappe.has_permission("Stock Reconciliation", "write", throw=True)
item_dict = frappe.get_cached_value("Item",
item_code,
["has_serial_no", "has_batch_no"],
as_dict=1)
if not item_dict:
# In cases of data upload to Items table
msg = _("Item {} does not exist.").format(item_code)
frappe.throw(msg, title=_("Missing"))
serial_nos = None
has_serial_no = bool(item_dict.get("has_serial_no"))
has_batch_no = bool(item_dict.get("has_batch_no"))
if not batch_no and has_batch_no:
# Not enough information to fetch data
return {"qty": 0, "rate": 0, "serial_nos": None}
# TODO: fetch only selected batch's values
data = get_stock_balance(
item_code,
warehouse,
posting_date,
posting_time,
with_valuation_rate=with_valuation_rate,
with_serial_no=has_serial_no,
)
if has_serial_no:
qty, rate, serial_nos = data
else:
qty, rate = data
if item_dict.get("has_batch_no"):
qty = (get_batch_qty(batch_no,
warehouse,
posting_date=posting_date,
posting_time=posting_time) or 0)
return {"qty": qty, "rate": rate, "serial_nos": serial_nos}
def get_stock_balance_for(item_code,
warehouse,
posting_date,
posting_time,
batch_no=None,
with_valuation_rate=True):
frappe.has_permission("Stock Reconciliation", "write", throw=True)
item_dict = frappe.db.get_value("Item",
item_code,
["has_serial_no", "has_batch_no"],
as_dict=1)
serial_nos = ""
if item_dict.get("has_serial_no"):
qty, rate, serial_nos = get_qty_rate_for_serial_nos(
item_code, warehouse, posting_date, posting_time, item_dict)
else:
qty, rate = get_stock_balance(item_code,
warehouse,
posting_date,
posting_time,
with_valuation_rate=with_valuation_rate)
if item_dict.get("has_batch_no"):
qty = get_batch_qty(batch_no, warehouse) or 0
stock_val = frappe.db.sql(
"""SELECT sum(stock_value),sum(actual_qty) from tabBin where item_code='{}'"""
.format(item_code))
warehouse_val = frappe.db.sql(
"""SELECT stock_value,actual_qty from tabBin where item_code='{}' and warehouse = '{}' """
.format(item_code, warehouse))
valuation_rate = 0.00
if stock_val:
if stock_val[0][0] and stock_val[0][1]:
valuation_rate = stock_val[0][0] / stock_val[0][1]
if warehouse_val:
if warehouse_val[0][0] and warehouse_val[0][1]:
rate = warehouse_val[0][0] / warehouse_val[0][1]
return {
'qty': qty,
'rate': rate,
'serial_nos': serial_nos,
'valuation_rate': valuation_rate
}
def get_warehouse_capacity_data(filters, start):
capacity_data = frappe.db.get_all('Putaway Rule',
fields=['item_code', 'warehouse','stock_capacity', 'company'],
filters=filters,
limit_start=start,
limit_page_length='11'
)
for entry in capacity_data:
balance_qty = get_stock_balance(entry.item_code, entry.warehouse, nowdate()) or 0
entry.update({
'actual_qty': balance_qty,
'percent_occupied': flt((flt(balance_qty) / flt(entry.stock_capacity)) * 100, 0)
})
return capacity_data
def get_items(warehouse, posting_date, posting_time):
items = frappe.get_list("Bin",
fields=["item_code"],
filters={"warehouse": warehouse},
as_list=1)
items += frappe.get_list("Item",
fields=["name"],
filters={
"is_stock_item": 1,
"has_serial_no": 0,
"has_batch_no": 0,
"has_variants": 0,
"disabled": 0,
"default_warehouse": warehouse
},
as_list=1)
res = []
for item in set(items):
stock_bal = get_stock_balance(item[0],
warehouse,
posting_date,
posting_time,
with_valuation_rate=True)
if frappe.db.get_value("Item", item[0], "disabled") == 0:
res.append({
"item_code":
item[0],
"warehouse":
warehouse,
"qty":
stock_bal[0],
"item_name":
frappe.db.get_value('Item', item[0], 'item_name'),
"valuation_rate":
stock_bal[1],
"current_qty":
stock_bal[0],
"current_valuation_rate":
stock_bal[1]
})
return res
def get_items(warehouse, posting_date, posting_time, company):
lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
items = frappe.db.sql(
"""
select i.name, i.item_name, bin.warehouse
from tabBin bin, tabItem i
where i.name=bin.item_code and i.disabled=0 and i.is_stock_item = 1
and i.has_variants = 0 and i.has_serial_no = 0 and i.has_batch_no = 0
and exists(select name from `tabWarehouse` where lft >= %s and rgt <= %s and name=bin.warehouse)
""",
(lft, rgt),
)
items += frappe.db.sql(
"""
select i.name, i.item_name, id.default_warehouse
from tabItem i, `tabItem Default` id
where i.name = id.parent
and exists(select name from `tabWarehouse` where lft >= %s and rgt <= %s and name=id.default_warehouse)
and i.is_stock_item = 1 and i.has_serial_no = 0 and i.has_batch_no = 0
and i.has_variants = 0 and i.disabled = 0 and id.company=%s
group by i.name
""",
(lft, rgt, company),
)
res = []
for d in set(items):
stock_bal = get_stock_balance(d[0],
d[2],
posting_date,
posting_time,
with_valuation_rate=True)
if frappe.db.get_value("Item", d[0], "disabled") == 0:
res.append({
"item_code": d[0],
"warehouse": d[2],
"qty": stock_bal[0],
"item_name": d[1],
"valuation_rate": stock_bal[1],
"current_qty": stock_bal[0],
"current_valuation_rate": stock_bal[1],
})
return res
def stock_availability_per_warehouse(item_code):
warehouses = frappe.db.get_list('Warehouse', filters={
'disabled': "0"
})
warehouse_json ={}
payload =[]
global_shortage ="yes"
for warehouse in warehouses:
warehouse_name = warehouse.name
balance = get_stock_balance(item_code, warehouse_name)
if balance >0:
global_shortage ="no"
warehouse_json["warehouse_name"] = warehouse_name
warehouse_json["balance"]=balance
payload.append(warehouse_json)
get_item_default_expense_account(item_code)
frappe.response["payload"]=payload
frappe.response["global_shortage"]=global_shortage
def _changed(item):
qty, rate = get_stock_balance(item.item_code, item.warehouse,
self.posting_date, self.posting_time, with_valuation_rate=True)
if (item.qty==None or item.qty==qty) and (item.valuation_rate==None or item.valuation_rate==rate):
return False
else:
# set default as current rates
if item.qty==None:
item.qty = qty
if item.valuation_rate==None:
item.valuation_rate = rate
item.current_qty = qty
item.current_valuation_rate = rate
self.difference_amount += (flt(item.qty, item.precision("qty")) * \
flt(item.valuation_rate or rate, item.precision("valuation_rate")) \
- flt(qty, item.precision("qty")) * flt(rate, item.precision("valuation_rate")))
return True
def _changed(item):
qty, rate = get_stock_balance(item.item_code, item.warehouse,
self.posting_date, self.posting_time, with_valuation_rate=True)
if (item.qty==None or item.qty==qty) and (item.valuation_rate==None or item.valuation_rate==rate):
return False
else:
# set default as current rates
if item.qty==None:
item.qty = qty
if item.valuation_rate==None:
item.valuation_rate = rate
item.current_qty = qty
item.current_valuation_rate = rate
self.difference_amount += (flt(item.qty, item.precision("qty")) * \
flt(item.valuation_rate or rate, item.precision("valuation_rate")) \
- flt(qty) * flt(rate))
return True
def get_acquired_warehouses(company):
acquired_warehouses = {}
filters = {"company": company, "disable": 0}
rules = frappe.get_all("Putaway Rule",
fields=[
"name", "item_code", "stock_capacity",
"priority", "warehouse"
],
filters=filters,
order_by="priority asc, capacity desc")
for rule in rules:
balance_qty = get_stock_balance(rule.item_code, rule.warehouse,
nowdate())
if balance_qty > 0:
acquired_warehouses.setdefault(rule.warehouse, rule.item_code)
return acquired_warehouses
def get_stock_item_details(warehouse, date, item=None, barcode=None):
out = {}
if barcode:
out["item"] = frappe.db.get_value(
"Item Barcode", filters={"barcode": barcode}, fieldname=["parent"]
)
if not out["item"]:
frappe.throw(_("Invalid Barcode. There is no Item attached to this barcode."))
else:
out["item"] = item
barcodes = frappe.db.get_values(
"Item Barcode", filters={"parent": out["item"]}, fieldname=["barcode"]
)
out["barcodes"] = [x[0] for x in barcodes]
out["qty"] = get_stock_balance(out["item"], warehouse, date)
out["value"] = get_stock_value_on(warehouse, date, out["item"])
out["image"] = frappe.db.get_value("Item", filters={"name": out["item"]}, fieldname=["image"])
return out
def get_warehouse_capacity_data(filters, start):
capacity_data = frappe.db.get_all(
"Putaway Rule",
fields=["item_code", "warehouse", "stock_capacity", "company"],
filters=filters,
limit_start=start,
limit_page_length="11",
)
for entry in capacity_data:
balance_qty = get_stock_balance(entry.item_code, entry.warehouse,
nowdate()) or 0
entry.update({
"actual_qty":
balance_qty,
"percent_occupied":
flt((flt(balance_qty) / flt(entry.stock_capacity)) * 100, 0),
})
return capacity_data
def get_stock_balance_for(item_code,
warehouse,
posting_date,
posting_time,
batch_no=None,
with_valuation_rate=True):
frappe.has_permission("Stock Reconciliation", "write", throw=True)
item_dict = frappe.db.get_value("Item",
item_code,
["has_serial_no", "has_batch_no"],
as_dict=1)
if not item_dict:
# In cases of data upload to Items table
msg = _("Item {} does not exist.").format(item_code)
frappe.throw(msg, title=_("Missing"))
serial_nos = ""
with_serial_no = True if item_dict.get("has_serial_no") else False
data = get_stock_balance(
item_code,
warehouse,
posting_date,
posting_time,
with_valuation_rate=with_valuation_rate,
with_serial_no=with_serial_no,
)
if with_serial_no:
qty, rate, serial_nos = data
else:
qty, rate = data
if item_dict.get("has_batch_no"):
qty = (get_batch_qty(batch_no,
warehouse,
posting_date=posting_date,
posting_time=posting_time) or 0)
return {"qty": qty, "rate": rate, "serial_nos": serial_nos}
def _changed(item): qty, rate = get_stock_balance(item.item_code, item.warehouse, self.posting_date, self.posting_time, with_valuation_rate=True) #JDLP - 2017-01-30 - batch_no if item.batch_no: qty = get_item_warehouse_batch_actual_qty(item.item_code, item.warehouse, item.batch_no, self.posting_date, self.posting_time) if (item.qty==None or item.qty==qty) and (item.valuation_rate==None or item.valuation_rate==rate): return False else: # set default as current rates if item.qty==None: item.qty = qty if item.valuation_rate==None: item.valuation_rate = rate item.current_qty = qty item.current_valuation_rate = rate self.difference_amount += (flt(item.qty or qty) * flt(item.valuation_rate or rate) - (flt(qty) * flt(rate))) return True
def get_items(warehouse, posting_date, posting_time, company):
items = [
d.item_code for d in frappe.get_list(
"Bin", fields=["item_code"], filters={"warehouse": warehouse})
]
items += frappe.db.sql_list(
'''select i.name from `tabItem` i, `tabItem Default` id where i.name = id.parent
and i.is_stock_item=1 and i.has_serial_no=0 and i.has_batch_no=0 and i.has_variants=0 and i.disabled=0
and id.default_warehouse=%s and id.company=%s''', (warehouse, company))
res = []
for item in set(items):
stock_bal = get_stock_balance(item[0],
warehouse,
posting_date,
posting_time,
with_valuation_rate=True)
if frappe.db.get_value("Item", item[0], "disabled") == 0:
res.append({
"item_code":
item[0],
"warehouse":
warehouse,
"qty":
stock_bal[0],
"item_name":
frappe.db.get_value('Item', item[0], 'item_name'),
"valuation_rate":
stock_bal[1],
"current_qty":
stock_bal[0],
"current_valuation_rate":
stock_bal[1]
})
return res
def raise_surplus_task_qty(item_code, quantity_required, except_warehouse):
warehouses = frappe.db.get_list('Warehouse', filters={
'disabled': "0",
'name':["!=",except_warehouse]
})
warehouse_json ={}
payload =[]
global_shortage ="yes"
#1. 1st we return warehouses with item balances, excluding our former store
for warehouse in warehouses:
warehouse_name = warehouse.name
balance = get_stock_balance(item_code, warehouse_name)
if balance >0:
global_shortage ="no"
warehouse_json["warehouse_name"] = warehouse_name
warehouse_json["balance"]=balance
payload.append(warehouse_json)
#[{"warehouse_name":"Stores-MTRH", "balance":6},{"warehouse_name":"Maintenance Store - MTRH", "balance":6}..]
#2. Loop through the store to return what each one of them can afford
qty_needed = quantity_required
args = frappe._dict(payload)
warehouse_dict ={}
payload_to_return =[]
for feasible_warehouse in args:
if qty_needed > 0: #IF WE STILL HAVE SOME BALANCE on QTY NEEDED
wh = feasible_warehouse.get("warehouse_name")
wh_balance = feasible_warehouse.get("balance")
warehouse_dict["warehouse_name"] = wh
if float(wh_balance) >= float(qty_needed):
warehouse_dict["can_afford"]=float(qty_needed) #CAN AFFORD THE WHOLE QTY NEEDED
qty_needed =0
else:
warehouse_dict["can_afford"]= float(wh_balance)#CAN AFFORD ONLY ITS BALANCE
qty_needed = qty_needed - float(wh_balance) #DEDUCT WHAT THE STORE CAN AFFORD FROM QTY NEEDED
payload_to_return.append(warehouse_dict)
frappe.response["whatremained"]=qty_needed
frappe.response["payload"]=warehouse_dict
frappe.response["global_shortage"]=global_shortage
def get_items_with_batch_no(warehouse, posting_date, posting_time, as_dict = 0, as_list = 0):
res = get_items(warehouse, posting_date, posting_time)
items = frappe.get_list("Item", fields=["name"], filters= {"is_stock_item": 1, "has_serial_no": 0,
"has_batch_no": 1, "has_variants": 0, "disabled": 0}, as_list=1)
for item in set(items):
#msgprint("item:" + cstr(item))
stock_bal = get_stock_balance(item[0], warehouse, posting_date, posting_time, with_valuation_rate=True)
if stock_bal[0] == 0: continue
batches = frappe.get_all('Batch', filters={'item': item[0]}, fields=['name'])
for batch in batches:
#msgprint("batche:" + cstr(batch["name"]))
qty = get_item_warehouse_batch_actual_qty(item[0], warehouse, batch["name"], posting_date, posting_time)
if qty == 0: continue
if as_list:
item_attributes = frappe.get_all("Item Variant Attribute",{"parent":item[0]},["attribute","attribute_value"])
species = ""
construction = ""
flooring_grade = ""
flooring_width = ""
thickness = ""
doc_batch = frappe.get_doc("Batch",{"name":batch["name"]})
if item_attributes:
if print_debug: frappe.errprint("item_attributes : " + cstr(item_attributes))
for attribute in item_attributes:
if attribute.attribute == "Essence" : species = attribute.attribute_value
if attribute.attribute == "Hardwood Construction" :
if attribute.attribute_value == "Massif":
construction = "Massif"
else:
construction = "Ing."
if attribute.attribute == "Flooring Grade" : flooring_grade = attribute.attribute_value
if attribute.attribute == "Flooring Width" : flooring_width = attribute.attribute_value
if attribute.attribute == "Flooring Thickness" : thickness = attribute.attribute_value
values = [
item[0],
batch["name"],
qty,
stock_bal[1],
species,
construction,
flooring_grade,
flooring_width,
thickness,
doc_batch.qty_per_box,
doc_batch.customer_batch_number,
doc_batch.milling,
doc_batch.description
]
if as_dict:
values = {
"item_code": item[0],
"warehouse": warehouse,
"qty": qty,
"item_name": frappe.db.get_value('Item', item[0], 'item_name'),
"valuation_rate": stock_bal[1],
"current_qty": qty,
"current_valuation_rate": stock_bal[1],
"batch_no": batch["name"]
}
res.append(values)
return res
def validate_data(self):
def _get_msg(row_num, msg):
return _("Row # {0}: ").format(row_num+1) + msg
self.validation_messages = []
item_warehouse_combinations = []
default_currency = frappe.db.get_default("currency")
for row_num, row in enumerate(self.items):
# find duplicates
if [row.item_code, row.warehouse, row.batch_no] in item_warehouse_combinations:#JDLP - 2017-01-30 - batch_no
self.validation_messages.append(_get_msg(row_num, _("Duplicate entry")))
else:
item_warehouse_combinations.append([row.item_code, row.warehouse, row.batch_no])#JDLP - 2017-01-30 - batch_no
self.validate_item(row.item_code, row_num+1)
# validate warehouse
if not frappe.db.get_value("Warehouse", row.warehouse):
self.validation_messages.append(_get_msg(row_num, _("Warehouse not found in the system")))
# if both not specified
if row.qty in ["", None] and row.valuation_rate in ["", None]:
self.validation_messages.append(_get_msg(row_num,
_("Please specify either Quantity or Valuation Rate or both")))
# do not allow negative quantity
if flt(row.qty) < 0:
self.validation_messages.append(_get_msg(row_num,
_("Negative Quantity is not allowed")))
# do not allow negative valuation
if flt(row.valuation_rate) < 0:
self.validation_messages.append(_get_msg(row_num,
_("Negative Valuation Rate is not allowed")))
if row.qty and not row.valuation_rate:
row.valuation_rate = get_stock_balance(row.item_code, row.warehouse,
self.posting_date, self.posting_time, with_valuation_rate=True)[1]
if not row.valuation_rate:
# try if there is a buying price list in default currency
buying_rate = frappe.db.get_value("Item Price", {"item_code": row.item_code,
"buying": 1, "currency": default_currency}, "price_list_rate")
if buying_rate:
row.valuation_rate = buying_rate
#JDLP - 2017-01-30 - batch_no
# if batch_no is required and not provided.
has_batch_no = frappe.db.get_value("Item", {"item_code": row.item_code}, "has_batch_no")
if has_batch_no == 1 and row.batch_no is None:
self.validation_messages.append(_get_msg(row_num,
_("Batch number is required")))
#raise frappe.ValidationError, _("Item: {0} managed batch-wise, batch number is required").format(item_code)
if has_batch_no == 0 and row.batch_no is not None:
self.validation_messages.append(_get_msg(row_num,
_("Batch number should be empty")))
#raise frappe.ValidationError, _("Item: {0} is not managed batch-wise, batch number should be empty").format(item_code)
#JDLP - 2017-01-30 - batch_no
# throw all validation messages
if self.validation_messages:
for msg in self.validation_messages:
msgprint(msg)
raise frappe.ValidationError(self.validation_messages)
def get_sle_for_serialized_items(self, row, sl_entries):
from erpnext.stock.stock_ledger import get_previous_sle
serial_nos = get_serial_nos(row.serial_no)
# To issue existing serial nos
if row.current_qty and (row.current_serial_no or row.batch_no):
args = self.get_sle_for_items(row)
args.update({
'actual_qty': -1 * row.current_qty,
'serial_no': row.current_serial_no,
'batch_no': row.batch_no,
'valuation_rate': row.current_valuation_rate
})
if row.current_serial_no:
args.update({
'qty_after_transaction': 0,
})
sl_entries.append(args)
qty_after_transaction = 0
for serial_no in serial_nos:
args = self.get_sle_for_items(row, [serial_no])
previous_sle = get_previous_sle({
"item_code": row.item_code,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
"serial_no": serial_no
})
if previous_sle and row.warehouse != previous_sle.get("warehouse"):
# If serial no exists in different warehouse
warehouse = previous_sle.get("warehouse", '') or row.warehouse
if not qty_after_transaction:
qty_after_transaction = get_stock_balance(row.item_code,
warehouse, self.posting_date, self.posting_time)
qty_after_transaction -= 1
new_args = args.copy()
new_args.update({
'actual_qty': -1,
'qty_after_transaction': qty_after_transaction,
'warehouse': warehouse,
'valuation_rate': previous_sle.get("valuation_rate")
})
sl_entries.append(new_args)
if row.qty:
args = self.get_sle_for_items(row)
args.update({
'actual_qty': row.qty,
'incoming_rate': row.valuation_rate,
'valuation_rate': row.valuation_rate
})
sl_entries.append(args)
if serial_nos == get_serial_nos(row.current_serial_no):
# update valuation rate
self.update_valuation_rate_for_serial_nos(row, serial_nos)
def get_available_putaway_capacity(rule):
stock_capacity, item_code, warehouse = frappe.db.get_value(
"Putaway Rule", rule, ["stock_capacity", "item_code", "warehouse"])
balance_qty = get_stock_balance(item_code, warehouse, nowdate())
free_space = flt(stock_capacity) - flt(balance_qty)
return free_space if free_space > 0 else 0
