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)