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