def get_incoming_rate(self, args):
incoming_rate = 0
if self.doc.purpose == "Sales Return" and \
(self.doc.delivery_note_no or self.doc.sales_invoice_no):
sle = webnotes.conn.sql(
"""select name, posting_date, posting_time,
actual_qty, stock_value, warehouse from `tabStock Ledger Entry`
where voucher_type = %s and voucher_no = %s and
item_code = %s limit 1""", ((self.doc.delivery_note_no and "Delivery Note"
or "Sales Invoice"), self.doc.delivery_note_no
or self.doc.sales_invoice_no, args.item_code),
as_dict=1)
if sle:
args.update({
"posting_date": sle[0].posting_date,
"posting_time": sle[0].posting_time,
"sle": sle[0].name,
"warehouse": sle[0].warehouse,
})
previous_sle = get_previous_sle(args)
incoming_rate = (flt(sle[0].stock_value) - flt(previous_sle.get("stock_value"))) / \
flt(sle[0].actual_qty)
else:
incoming_rate = get_incoming_rate(args)
return incoming_rate
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
from stock.stock_ledger import get_previous_sle
in_rate = 0
if args.get("serial_no"):
in_rate = get_avg_purchase_rate(args.get("serial_no"))
elif args.get("bom_no"):
result = webnotes.conn.sql(
"""select ifnull(total_cost, 0) / ifnull(quantity, 1)
from `tabBOM` where name = %s and docstatus=1 and is_active=1""",
args.get("bom_no"))
in_rate = result and flt(result[0][0]) or 0
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == 'FIFO':
if not previous_sle:
return 0.0
previous_stock_queue = json.loads(
previous_sle.get('stock_queue', '[]') or '[]')
in_rate = previous_stock_queue and \
get_fifo_rate(previous_stock_queue, args.get("qty") or 0) or 0
elif valuation_method == 'Moving Average':
in_rate = previous_sle.get('valuation_rate') or 0
return in_rate
def get_stock_and_rate(self):
"""get stock and incoming rate on posting date"""
for d in getlist(self.doclist, 'mtn_details'):
args = webnotes._dict({
"item_code":
d.item_code,
"warehouse":
d.s_warehouse or d.t_warehouse,
"posting_date":
self.doc.posting_date,
"posting_time":
self.doc.posting_time,
"qty":
d.s_warehouse and -1 * d.transfer_qty or d.transfer_qty,
"serial_no":
d.serial_no,
"bom_no":
d.bom_no,
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get(
"qty_after_transaction") or 0
# get incoming rate
if not flt(d.incoming_rate):
d.incoming_rate = self.get_incoming_rate(args)
d.amount = flt(d.transfer_qty) * flt(d.incoming_rate)
def get_stock_and_rate(self):
"""get stock and incoming rate on posting date"""
for d in getlist(self.doclist, 'mtn_details'):
ss = cstr(d.fetch).replace('\n', '')
q = webnotes.conn.sql(
"select quantity from `tabPacking items` where name ='" + ss +
"'",
as_list=1)
#webnotes.errprint(q)
args = webnotes._dict({
"item_code":
d.item_code,
"warehouse":
d.s_warehouse or d.t_warehouse,
"posting_date":
self.doc.posting_date,
"posting_time":
self.doc.posting_time,
"qty":
d.s_warehouse and -1 * q[0][0] or d.q[0][0],
"serial_no":
d.serial_no,
"bom_no":
d.bom_no,
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get(
"qty_after_transaction") or 0
# get incoming rate
if not flt(d.incoming_rate):
d.incoming_rate = self.get_incoming_rate(args)
d.amount = flt(d.qty) * flt(d.incoming_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))
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 get_incoming_rate(self, args):
incoming_rate = 0
if self.doc.purpose == "Sales Return" and (self.doc.delivery_note_no or self.doc.sales_invoice_no):
sle = webnotes.conn.sql(
"""select name, posting_date, posting_time,
actual_qty, stock_value, warehouse from `tabStock Ledger Entry`
where voucher_type = %s and voucher_no = %s and
item_code = %s and ifnull(is_cancelled, 'No') = 'No' limit 1""",
(
(self.doc.delivery_note_no and "Delivery Note" or "Sales Invoice"),
self.doc.delivery_note_no or self.doc.sales_invoice_no,
args.item_code,
),
as_dict=1,
)
if sle:
args.update(
{
"posting_date": sle[0].posting_date,
"posting_time": sle[0].posting_time,
"sle": sle[0].name,
"warehouse": sle[0].warehouse,
}
)
previous_sle = get_previous_sle(args)
incoming_rate = (flt(sle[0].stock_value) - flt(previous_sle.get("stock_value"))) / flt(
sle[0].actual_qty
)
else:
incoming_rate = get_incoming_rate(args)
return incoming_rate
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
from stock.stock_ledger import get_previous_sle
in_rate = 0
if args.get("serial_no"):
in_rate = get_avg_purchase_rate(args.get("serial_no"))
elif args.get("bom_no"):
result = webnotes.conn.sql(
"""select ifnull(total_cost, 0) / ifnull(quantity, 1)
from `tabBOM` where name = %s and docstatus=1 and is_active=1""",
args.get("bom_no"),
)
in_rate = result and flt(result[0][0]) or 0
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == "FIFO":
if not previous_sle:
return 0.0
previous_stock_queue = json.loads(previous_sle.get("stock_queue", "[]") or "[]")
in_rate = previous_stock_queue and get_fifo_rate(previous_stock_queue, args.get("qty") or 0) or 0
elif valuation_method == "Moving Average":
in_rate = previous_sle.get("valuation_rate") or 0
return in_rate
def get_warehouse_details(self, args):
args = json.loads(args)
args.update({"posting_date": self.doc.posting_date, "posting_time": self.doc.posting_time})
args = webnotes._dict(args)
ret = {
"actual_qty": get_previous_sle(args).get("qty_after_transaction") or 0,
"incoming_rate": self.get_incoming_rate(args),
}
return ret
def get_warehouse_details(self, args):
import json
args, actual_qty, in_rate = json.loads(args), 0, 0
args.update({
"posting_date": self.doc.posting_date,
"posting_time": self.doc.posting_time
})
ret = {
"actual_qty" : get_previous_sle(args).get("qty_after_transaction") or 0,
"incoming_rate" : get_incoming_rate(args)
}
return ret
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 get_warehouse_details(self, args):
args = json.loads(args)
args.update({
"posting_date": self.doc.posting_date,
"posting_time": self.doc.posting_time,
})
args = webnotes._dict(args)
ret = {
"actual_qty": get_previous_sle(args).get("qty_after_transaction")
or 0,
"incoming_rate": self.get_incoming_rate(args)
}
return ret
def get_stock_and_rate(self):
"""get stock and incoming rate on posting date"""
for d in getlist(self.doclist, 'mtn_details'):
args = webnotes._dict({
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.doc.posting_date,
"posting_time": self.doc.posting_time,
"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
"serial_no": d.serial_no,
"bom_no": d.bom_no,
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0
# get incoming rate
if not flt(d.incoming_rate):
d.incoming_rate = self.get_incoming_rate(args)
d.amount = flt(d.transfer_qty) * flt(d.incoming_rate)
def get_stock_and_rate(self):
"""get stock and incoming rate on posting date"""
for d in getlist(self.doclist, 'mtn_details'):
ss=cstr(d.fetch).replace('\n','')
q =webnotes.conn.sql("select quantity from `tabPacking items` where name ='"+ss+"'",as_list=1)
#webnotes.errprint(q)
args = webnotes._dict({
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.doc.posting_date,
"posting_time": self.doc.posting_time,
"qty": d.s_warehouse and -1*q[0][0] or d.q[0][0],
"serial_no": d.serial_no,
"bom_no": d.bom_no,
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0
# get incoming rate
if not flt(d.incoming_rate):
d.incoming_rate = self.get_incoming_rate(args)
d.amount = flt(d.qty) * flt(d.incoming_rate)
