def insert_stock_ledger_entries(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from stock.utils import get_valuation_method
from stock.stock_ledger import get_previous_sle
row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
if not self.doc.reconciliation_json:
msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1)
data = json.loads(self.doc.reconciliation_json)
for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
row = webnotes._dict(zip(row_template, row))
previous_sle = get_previous_sle({
"item_code": row.item_code,
"warehouse": row.warehouse,
"posting_date": self.doc.posting_date,
"posting_time": self.doc.posting_time
})
change_in_qty = row.qty != "" and \
(flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))
change_in_rate = row.valuation_rate != "" and \
(flt(row.valuation_rate) != flt(previous_sle.get("valuation_rate")))
if get_valuation_method(row.item_code) == "Moving Average":
self.sle_for_moving_avg(row, previous_sle, change_in_qty, change_in_rate)
else:
self.sle_for_fifo(row, previous_sle, change_in_qty, change_in_rate)
def insert_stock_ledger_entries(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from stock.utils import get_valuation_method
from stock.stock_ledger import get_previous_sle
row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
if not self.doc.reconciliation_json:
msgprint(_("""Stock Reconciliation file not uploaded"""),
raise_exception=1)
data = json.loads(self.doc.reconciliation_json)
for row_num, row in enumerate(data[data.index(self.head_row) + 1:]):
row = webnotes._dict(zip(row_template, row))
row["row_num"] = row_num
previous_sle = get_previous_sle({
"item_code":
row.item_code,
"warehouse":
row.warehouse,
"posting_date":
self.doc.posting_date,
"posting_time":
self.doc.posting_time
})
# check valuation rate mandatory
if row.qty != "" and not row.valuation_rate and \
flt(previous_sle.get("qty_after_transaction")) <= 0:
webnotes.msgprint(
_("As existing qty for item: ") + row.item_code +
_(" at warehouse: ") + row.warehouse +
_(" is less than equals to zero in the system, \
valuation rate is mandatory for this item"),
raise_exception=1)
change_in_qty = row.qty != "" and \
(flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))
change_in_rate = row.valuation_rate != "" and \
(flt(row.valuation_rate) - flt(previous_sle.get("valuation_rate")))
if get_valuation_method(row.item_code) == "Moving Average":
self.sle_for_moving_avg(row, previous_sle, change_in_qty,
change_in_rate)
else:
self.sle_for_fifo(row, previous_sle, change_in_qty,
change_in_rate)
def update_entries_after(args, verbose=1):
"""
update valution rate and qty after transaction
from the current time-bucket onwards
args = {
"item_code": "ABC",
"warehouse": "XYZ",
"posting_date": "2012-12-12",
"posting_time": "12:00"
}
"""
if not _exceptions:
webnotes.local.stockledger_exceptions = []
previous_sle = get_sle_before_datetime(args)
qty_after_transaction = flt(previous_sle.get("qty_after_transaction"))
valuation_rate = flt(previous_sle.get("valuation_rate"))
stock_queue = json.loads(previous_sle.get("stock_queue") or "[]")
stock_value = flt(previous_sle.get("stock_value"))
prev_stock_value = flt(previous_sle.get("stock_value"))
entries_to_fix = get_sle_after_datetime(previous_sle or \
{"item_code": args["item_code"], "warehouse": args["warehouse"]}, for_update=True)
valuation_method = get_valuation_method(args["item_code"])
stock_value_difference = 0.0
for sle in entries_to_fix:
if sle.serial_no or not cint(webnotes.conn.get_default("allow_negative_stock")):
# validate negative stock for serialized items, fifo valuation
# or when negative stock is not allowed for moving average
if not validate_negative_stock(qty_after_transaction, sle):
qty_after_transaction += flt(sle.actual_qty)
continue
if sle.serial_no:
valuation_rate = get_serialized_values(qty_after_transaction, sle, valuation_rate)
elif valuation_method == "Moving Average":
valuation_rate = get_moving_average_values(qty_after_transaction, sle, valuation_rate)
else:
valuation_rate = get_fifo_values(qty_after_transaction, sle, stock_queue)
qty_after_transaction += flt(sle.actual_qty)
# get stock value
if sle.serial_no:
stock_value = qty_after_transaction * valuation_rate
elif valuation_method == "Moving Average":
stock_value = (qty_after_transaction > 0) and \
(qty_after_transaction * valuation_rate) or 0
else:
stock_value = sum((flt(batch[0]) * flt(batch[1]) for batch in stock_queue))
# rounding as per precision
from webnotes.model.meta import get_field_precision
meta = webnotes.get_doctype("Stock Ledger Entry")
stock_value = flt(stock_value, get_field_precision(meta.get_field("stock_value"),
webnotes._dict({"fields": sle})))
stock_value_difference = stock_value - prev_stock_value
prev_stock_value = stock_value
# update current sle
webnotes.conn.sql("""update `tabStock Ledger Entry`
set qty_after_transaction=%s, valuation_rate=%s, stock_queue=%s,
stock_value=%s, stock_value_difference=%s where name=%s""",
(qty_after_transaction, valuation_rate,
json.dumps(stock_queue), stock_value, stock_value_difference, sle.name))
if _exceptions:
_raise_exceptions(args, verbose)
# update bin
if not webnotes.conn.exists({"doctype": "Bin", "item_code": args["item_code"],
"warehouse": args["warehouse"]}):
bin_wrapper = webnotes.bean([{
"doctype": "Bin",
"item_code": args["item_code"],
"warehouse": args["warehouse"],
}])
bin_wrapper.ignore_permissions = 1
bin_wrapper.insert()
webnotes.conn.sql("""update `tabBin` set valuation_rate=%s, actual_qty=%s,
stock_value=%s,
projected_qty = (actual_qty + indented_qty + ordered_qty + planned_qty - reserved_qty)
where item_code=%s and warehouse=%s""", (valuation_rate, qty_after_transaction,
stock_value, args["item_code"], args["warehouse"]))
def update_entries_after(args, verbose=1):
"""
update valution rate and qty after transaction
from the current time-bucket onwards
args = {
"item_code": "ABC",
"warehouse": "XYZ",
"posting_date": "2012-12-12",
"posting_time": "12:00"
}
"""
global _exceptions
_exceptions = []
previous_sle = get_sle_before_datetime(args)
qty_after_transaction = flt(previous_sle.get("qty_after_transaction"))
valuation_rate = flt(previous_sle.get("valuation_rate"))
stock_queue = json.loads(previous_sle.get("stock_queue") or "[]")
stock_value = 0.0
entries_to_fix = get_sle_after_datetime(previous_sle or \
{"item_code": args["item_code"], "warehouse": args["warehouse"]}, for_update=True)
valuation_method = get_valuation_method(args["item_code"])
for sle in entries_to_fix:
if sle.serial_no or not cint(
webnotes.conn.get_default("allow_negative_stock")):
# validate negative stock for serialized items, fifo valuation
# or when negative stock is not allowed for moving average
if not validate_negative_stock(qty_after_transaction, sle):
qty_after_transaction += flt(sle.actual_qty)
continue
if sle.serial_no:
valuation_rate = get_serialized_values(qty_after_transaction, sle,
valuation_rate)
elif valuation_method == "Moving Average":
valuation_rate = get_moving_average_values(qty_after_transaction,
sle, valuation_rate)
else:
valuation_rate = get_fifo_values(qty_after_transaction, sle,
stock_queue)
qty_after_transaction += flt(sle.actual_qty)
# get stock value
if sle.serial_no:
stock_value = qty_after_transaction * valuation_rate
elif valuation_method == "Moving Average":
stock_value = (qty_after_transaction > 0) and \
(qty_after_transaction * valuation_rate) or 0
else:
stock_value = sum(
(flt(batch[0]) * flt(batch[1]) for batch in stock_queue))
# print sle.posting_date, sle.actual_qty, sle.incoming_rate, stock_queue, stock_value
# update current sle
webnotes.conn.sql(
"""update `tabStock Ledger Entry`
set qty_after_transaction=%s, valuation_rate=%s, stock_queue=%s,
stock_value=%s where name=%s""",
(qty_after_transaction, valuation_rate, json.dumps(stock_queue),
stock_value, sle.name))
if _exceptions:
_raise_exceptions(args, verbose)
# update bin
if not webnotes.conn.exists({
"doctype": "Bin",
"item_code": args["item_code"],
"warehouse": args["warehouse"]
}):
bin_wrapper = webnotes.bean([{
"doctype": "Bin",
"item_code": args["item_code"],
"warehouse": args["warehouse"],
}])
bin_wrapper.ignore_permissions = 1
bin_wrapper.insert()
webnotes.conn.sql(
"""update `tabBin` set valuation_rate=%s, actual_qty=%s,
stock_value=%s,
projected_qty = (actual_qty + indented_qty + ordered_qty + planned_qty - reserved_qty)
where item_code=%s and warehouse=%s""",
(valuation_rate, qty_after_transaction, stock_value, args["item_code"],
args["warehouse"]))
