def update_stock_ledger_entry(self): # update stock ledger entry from stock.stock_ledger import update_entries_after if flt(self.doc.conversion_factor) != flt(1): sql( "update `tabStock Ledger Entry` set stock_uom = '%s', actual_qty = ifnull(actual_qty,0) * '%s' where item_code = '%s' " % (self.doc.new_stock_uom, self.doc.conversion_factor, self.doc.item_code) ) else: sql( "update `tabStock Ledger Entry` set stock_uom = '%s' where item_code = '%s' " % (self.doc.new_stock_uom, self.doc.item_code) ) # acknowledge user msgprint("Stock Ledger Entries Updated Successfully.") # update item valuation if flt(self.doc.conversion_factor) != flt(1): wh = sql("select name from `tabWarehouse`") for w in wh: update_entries_after({"item_code": self.doc.item_code, "warehouse": w[0]}) # acknowledge user msgprint("Item Valuation Updated Successfully.")
def update_stock_ledger_entry(self): # update stock ledger entry from stock.stock_ledger import update_entries_after if flt(self.doc.conversion_factor) != flt(1): sql("update `tabStock Ledger Entry` set stock_uom = '%s', actual_qty = ifnull(actual_qty,0) * '%s' where item_code = '%s' " % (self.doc.new_stock_uom, self.doc.conversion_factor, self.doc.item_code)) else: sql("update `tabStock Ledger Entry` set stock_uom = '%s' where item_code = '%s' " % (self.doc.new_stock_uom, self.doc.item_code)) # acknowledge user msgprint("Stock Ledger Entries Updated Successfully.") # update item valuation if flt(self.doc.conversion_factor) != flt(1): wh = sql("select name from `tabWarehouse`") for w in wh: update_entries_after({ "item_code": self.doc.item_code, "warehouse": w[0] }) # acknowledge user msgprint("Item Valuation Updated Successfully.")
def execute(): from stock.stock_ledger import update_entries_after item_warehouse = [] # update valuation_rate in transaction doctypes = {"Purchase Receipt": "purchase_receipt_details", "Purchase Invoice": "entries"} for dt in doctypes: for d in webnotes.conn.sql("""select name from `tab%s` where modified >= '2013-05-09' and docstatus=1""" % dt): rec = webnotes.get_obj(dt, d[0]) rec.update_valuation_rate(doctypes[dt]) for item in rec.doclist.get({"parentfield": doctypes[dt]}): webnotes.conn.sql("""update `tab%s Item` set valuation_rate = %s where name = %s"""% (dt, '%s', '%s'), tuple([item.valuation_rate, item.name])) if dt == "Purchase Receipt": webnotes.conn.sql("""update `tabStock Ledger Entry` set incoming_rate = %s where voucher_detail_no = %s""", (item.valuation_rate, item.name)) if [item.item_code, item.warehouse] not in item_warehouse: item_warehouse.append([item.item_code, item.warehouse]) for d in item_warehouse: try: update_entries_after({"item_code": d[0], "warehouse": d[1], "posting_date": "2013-01-01", "posting_time": "00:05:00"}) webnotes.conn.commit() except: pass
def update_sle(self): """ Recalculate valuation rate in all sle after pr posting date""" from stock.stock_ledger import update_entries_after for pr in self.selected_pr: pr_obj = get_obj('Purchase Receipt', pr, with_children=1) for d in getlist(pr_obj.doclist, 'purchase_receipt_details'): if flt(d.qty): d.valuation_rate = (flt(d.purchase_rate) + (flt(d.rm_supp_cost) / flt(d.qty)) + (flt(d.item_tax_amount) / flt(d.qty)) ) / flt(d.conversion_factor) d.save() if d.serial_no: self.update_serial_no(d.serial_no, d.valuation_rate) sql("update `tabStock Ledger Entry` set incoming_rate = '%s' where voucher_detail_no = '%s'" % (flt(d.valuation_rate), d.name)) res = sql( """select item_code, warehouse, posting_date, posting_time from `tabStock Ledger Entry` where voucher_detail_no = %s LIMIT 1""", d.name, as_dict=1) # update valuation rate after pr posting date if res: update_entries_after(res[0])
def cleanup_wrong_sle(): sle = webnotes.conn.sql(""" select item_code, warehouse, voucher_no, name from `tabStock Ledger Entry` sle where voucher_type = 'Delivery Note' and not exists( select name from `tabDelivery Note Packing Item` where item_code = sle.item_code and qty = abs(sle.actual_qty) and parent = sle.voucher_no ) and not exists ( select name from `tabDelivery Note Item` where item_code = sle.item_code and qty = abs(sle.actual_qty) and parent = sle.voucher_no ) """) if sle: for d in sle: webnotes.conn.sql("update `tabStock Ledger Entry` set is_cancelled = 'Yes' where name = %s", d[3]) create_comment(d[3]) update_entries_after({ "item_code": d[0], "warehouse": d[1], "posting_date": "2012-07-01", "posting_time": "12:05" })
def execute(): webnotes.conn.auto_commit_on_many_writes = 1 pr_items = webnotes.conn.sql( """select item_code, warehouse, serial_no, valuation_rate, name from `tabPurchase Receipt Item` where ifnull(serial_no, '') != '' and docstatus = 1""", as_dict=True) item_warehouse = [] for item in pr_items: serial_nos = cstr(item.serial_no).strip().split("\n") serial_nos = map(lambda x: x.strip(), serial_nos) if cstr(item.serial_no) != "\n".join(serial_nos): webnotes.conn.sql( """update `tabPurchase Receipt Item` set serial_no = %s where name = %s""", ("\n".join(serial_nos), item.name)) if [item.item_code, item.warehouse] not in item_warehouse: item_warehouse.append([item.item_code, item.warehouse]) webnotes.conn.sql( """update `tabSerial No` set purchase_rate = %s where name in (%s)""" % ('%s', ', '.join(['%s'] * len(serial_nos))), tuple([item.valuation_rate] + serial_nos)) for d in item_warehouse: try: update_entries_after({"item_code": d[0], "warehouse": d[1]}) except: continue webnotes.conn.auto_commit_on_many_writes = 0
def cleanup_wrong_sle(): sle = webnotes.conn.sql(""" select item_code, warehouse, voucher_no, name from `tabStock Ledger Entry` sle where voucher_type = 'Delivery Note' and not exists( select name from `tabDelivery Note Packing Item` where item_code = sle.item_code and qty = abs(sle.actual_qty) and parent = sle.voucher_no ) and not exists ( select name from `tabDelivery Note Item` where item_code = sle.item_code and qty = abs(sle.actual_qty) and parent = sle.voucher_no ) """) if sle: print sle for d in sle: webnotes.conn.sql( "update `tabStock Ledger Entry` set is_cancelled = 'Yes' where name = %s", d[3]) create_comment(d[3]) update_entries_after({ "item_code": d[0], "warehouse": d[1], "posting_date": "2012-07-01", "posting_time": "12:05" })
def update_sle(self): """ Recalculate valuation rate in all sle after pr posting date""" from stock.stock_ledger import update_entries_after for pr in self.selected_pr: pr_obj = get_obj("Purchase Receipt", pr, with_children=1) for d in getlist(pr_obj.doclist, "purchase_receipt_details"): if flt(d.qty): d.valuation_rate = ( flt(d.purchase_rate) + (flt(d.rm_supp_cost) / flt(d.qty)) + (flt(d.item_tax_amount) / flt(d.qty)) ) / flt(d.conversion_factor) d.save() self.update_serial_no(d.serial_no, d.valuation_rate) sql( "update `tabStock Ledger Entry` set incoming_rate = '%s' where voucher_detail_no = '%s'" % (flt(d.valuation_rate), d.name) ) res = sql( """select item_code, warehouse, posting_date, posting_time from `tabStock Ledger Entry` where voucher_detail_no = %s LIMIT 1""", d.name, as_dict=1, ) # update valuation rate after pr posting date if res: update_entries_after(res[0])
def execute(): webnotes.conn.auto_commit_on_many_writes = 1 pr_items = webnotes.conn.sql("""select item_code, warehouse, serial_no, valuation_rate, name from `tabPurchase Receipt Item` where ifnull(serial_no, '') != '' and docstatus = 1""", as_dict=True) item_warehouse = [] for item in pr_items: serial_nos = cstr(item.serial_no).strip().split("\n") serial_nos = map(lambda x: x.strip(), serial_nos) if cstr(item.serial_no) != "\n".join(serial_nos): webnotes.conn.sql("""update `tabPurchase Receipt Item` set serial_no = %s where name = %s""", ("\n".join(serial_nos), item.name)) if [item.item_code, item.warehouse] not in item_warehouse: item_warehouse.append([item.item_code, item.warehouse]) webnotes.conn.sql("""update `tabSerial No` set purchase_rate = %s where name in (%s)""" % ('%s', ', '.join(['%s']*len(serial_nos))), tuple([item.valuation_rate] + serial_nos)) for d in item_warehouse: try: update_entries_after({"item_code": d[0], "warehouse": d[1] }) except: continue webnotes.conn.auto_commit_on_many_writes = 0
def repost_actual_qty(item_code, warehouse): from stock.stock_ledger import update_entries_after try: update_entries_after({"item_code": item_code, "warehouse": warehouse}) except: pass
def on_rename(self, newdn, olddn, merge=False): webnotes.conn.set_value("Account", {"account_type": "Warehouse", "master_name": olddn}, "master_name", newdn) if merge: from stock.stock_ledger import update_entries_after for item_code in webnotes.conn.sql("""select item_code from `tabBin` where warehouse=%s""", newdn): update_entries_after({"item_code": item_code, "warehouse": newdn})
def on_rename(self, newdn, olddn, merge=False): if merge: from stock.stock_ledger import update_entries_after for item_code in webnotes.conn.sql( """select item_code from `tabBin` where warehouse=%s""", newdn): update_entries_after({ "item_code": item_code, "warehouse": newdn })
def on_rename(self, newdn, olddn, merge=False): webnotes.conn.sql("update tabItem set item_code = %s where name = %s", (newdn, olddn)) if self.doc.page_name: from webnotes.webutils import clear_cache clear_cache(self.doc.page_name) if merge: from stock.stock_ledger import update_entries_after for wh in webnotes.conn.sql("""select warehouse from `tabBin` where item_code=%s""", newdn): update_entries_after({"item_code": newdn, "warehouse": wh[0]})
def on_rename(self, newdn, olddn, merge=False): webnotes.conn.sql("update tabItem set item_code = %s where name = %s", (newdn, olddn)) if self.doc.page_name: from webnotes.webutils import clear_cache clear_cache(self.doc.page_name) if merge: from stock.stock_ledger import update_entries_after for wh in webnotes.conn.sql("""select warehouse from `tabBin` where item_code=%s""", newdn): update_entries_after({"item_code": newdn, "warehouse": wh})
def execute(): import webnotes res = webnotes.conn.sql("""select distinct item_code, warehouse from `tabStock Ledger Entry` where posting_time > '00:00:00' and posting_time < '00:01:00'""", as_dict=1) webnotes.conn.sql("update `tabStock Ledger Entry` set posting_time = '00:00:00' where posting_time > '00:00:00' and posting_time < '00:01:00'") from stock.stock_ledger import update_entries_after for d in res: update_entries_after({ "item_code": d.item_code, "warehouse": d.warehouse, })
def execute(): import webnotes res = webnotes.conn.sql("""select distinct item_code, warehouse from `tabStock Ledger Entry` where posting_time > '00:00:00' and posting_time < '00:01:00'""", as_dict=1) webnotes.conn.sql("update `tabStock Ledger Entry` set posting_time = '00:00:00' where posting_time > '00:00:00' and posting_time < '00:01:00'") from stock.stock_ledger import update_entries_after for d in res: update_entries_after({ "item_code": d.item_code, "warehouse": d.warehouse, })
def execute(): import webnotes from stock.stock_ledger import update_entries_after res = webnotes.conn.sql("select distinct item_code, warehouse from `tabStock Ledger Entry`") i=0 for d in res: try: update_entries_after({ "item_code": d[0], "warehouse": d[1]}) except: pass i += 1 if i%100 == 0: webnotes.conn.sql("commit") webnotes.conn.sql("start transaction")
def execute(): import webnotes from stock.stock_ledger import update_entries_after res = webnotes.conn.sql("select distinct item_code, warehouse from `tabStock Ledger Entry`") i=0 for d in res: try: update_entries_after({ "item_code": d[0], "warehouse": d[1]}) except: pass i += 1 if i%100 == 0: webnotes.conn.sql("commit") webnotes.conn.sql("start transaction")
def on_rename(self, new, old, merge=False): webnotes.conn.set_value("Account", { "account_type": "Warehouse", "master_name": old }, "master_name", new) if merge: from stock.stock_ledger import update_entries_after for item_code in webnotes.conn.sql( """select item_code from `tabBin` where warehouse=%s""", new): update_entries_after({ "item_code": item_code, "warehouse": new })
def update_stock(self, args): self.update_qty(args) if args.get("actual_qty"): from stock.stock_ledger import update_entries_after if not args.get("posting_date"): args["posting_date"] = nowdate() # update valuation and qty after transaction for post dated entry update_entries_after({ "item_code": self.doc.item_code, "warehouse": self.doc.warehouse, "posting_date": args.get("posting_date"), "posting_time": args.get("posting_time") })
def execute(): import webnotes from stock.stock_ledger import update_entries_after webnotes.conn.auto_commit_on_many_writes = 1 res = webnotes.conn.sql("""select distinct sle.item_code, sle.warehouse from `tabStock Ledger Entry` sle where (select has_serial_no from tabItem where name=sle.item_code)='Yes'""") for d in res: try: update_entries_after({"item_code": d[0], "warehouse": d[1]}) except: pass webnotes.conn.auto_commit_on_many_writes = 0
def update_stock(self, args): self.update_qty(args) if args.get("actual_qty"): from stock.stock_ledger import update_entries_after if not args.get("posting_date"): posting_date = nowdate() # update valuation and qty after transaction for post dated entry update_entries_after({ "item_code": self.doc.item_code, "warehouse": self.doc.warehouse, "posting_date": args.get("posting_date"), "posting_time": args.get("posting_time") })
def execute(): import webnotes from stock.stock_ledger import update_entries_after webnotes.conn.auto_commit_on_many_writes = 1 res = webnotes.conn.sql("""select distinct sle.item_code, sle.warehouse from `tabStock Ledger Entry` sle where (select has_serial_no from tabItem where name=sle.item_code)='Yes'""") for d in res: try: update_entries_after({ "item_code": d[0], "warehouse": d[1]}) except: pass webnotes.conn.auto_commit_on_many_writes = 0
def delete_stock_ledger_entries(self): """ Delete Stock Ledger Entries related to this Stock Reconciliation and repost future Stock Ledger Entries""" existing_entries = webnotes.conn.sql("""select item_code, warehouse from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation' and voucher_no=%s""", self.doc.name, as_dict=1) # delete entries webnotes.conn.sql("""delete from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation' and voucher_no=%s""", self.doc.name) # repost future entries for selected item_code, warehouse for entries in existing_entries: update_entries_after({ "item_code": entries.item_code, "warehouse": entries.warehouse, "posting_date": self.doc.posting_date, "posting_time": self.doc.posting_time })
def delete_stock_ledger_entries(self): """ Delete Stock Ledger Entries related to this Stock Reconciliation and repost future Stock Ledger Entries""" existing_entries = webnotes.conn.sql("""select item_code, warehouse from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation' and voucher_no=%s""", self.doc.name, as_dict=1) # delete entries webnotes.conn.sql("""delete from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation' and voucher_no=%s""", self.doc.name) # repost future entries for selected item_code, warehouse for entries in existing_entries: update_entries_after({ "item_code": entries.item_code, "warehouse": entries.warehouse, "posting_date": self.doc.posting_date, "posting_time": self.doc.posting_time })
def delete_and_repost_sle(self): """ Delete Stock Ledger Entries related to this voucher and repost future Stock Ledger Entries""" existing_entries = webnotes.conn.sql("""select distinct item_code, warehouse from `tabStock Ledger Entry` where voucher_type=%s and voucher_no=%s""", (self.doc.doctype, self.doc.name), as_dict=1) # delete entries webnotes.conn.sql("""delete from `tabStock Ledger Entry` where voucher_type=%s and voucher_no=%s""", (self.doc.doctype, self.doc.name)) # repost future entries for selected item_code, warehouse for entries in existing_entries: update_entries_after({ "item_code": entries.item_code, "warehouse": entries.warehouse, "posting_date": self.doc.posting_date, "posting_time": self.doc.posting_time })
def delete_and_repost_sle(self): """ Delete Stock Ledger Entries related to this voucher and repost future Stock Ledger Entries""" existing_entries = webnotes.conn.sql("""select distinct item_code, warehouse from `tabStock Ledger Entry` where voucher_type=%s and voucher_no=%s""", (self.doc.doctype, self.doc.name), as_dict=1) # delete entries webnotes.conn.sql("""delete from `tabStock Ledger Entry` where voucher_type=%s and voucher_no=%s""", (self.doc.doctype, self.doc.name)) # repost future entries for selected item_code, warehouse for entries in existing_entries: update_entries_after({ "item_code": entries.item_code, "warehouse": entries.warehouse, "posting_date": self.doc.posting_date, "posting_time": self.doc.posting_time })
def execute(): from stock.stock_ledger import update_entries_after item_warehouse = [] # update valuation_rate in transaction doctypes = { "Purchase Receipt": "purchase_receipt_details", "Purchase Invoice": "entries" } for dt in doctypes: for d in webnotes.conn.sql("""select name from `tab%s` where modified >= '2013-05-09' and docstatus=1""" % dt): rec = webnotes.get_obj(dt, d[0]) rec.update_valuation_rate(doctypes[dt]) for item in rec.doclist.get({"parentfield": doctypes[dt]}): webnotes.conn.sql( """update `tab%s Item` set valuation_rate = %s where name = %s""" % (dt, '%s', '%s'), tuple([item.valuation_rate, item.name])) if dt == "Purchase Receipt": webnotes.conn.sql( """update `tabStock Ledger Entry` set incoming_rate = %s where voucher_detail_no = %s""", (item.valuation_rate, item.name)) if [item.item_code, item.warehouse] not in item_warehouse: item_warehouse.append([item.item_code, item.warehouse]) for d in item_warehouse: try: update_entries_after({ "item_code": d[0], "warehouse": d[1], "posting_date": "2013-01-01", "posting_time": "00:05:00" }) webnotes.conn.commit() except: pass
def set_stock_balance_as_per_serial_no(item_code=None, posting_date=None, posting_time=None, fiscal_year=None): from webnotes.utils import flt, cstr from webnotes.model.doc import Document from stock.utils import update_bin from stock.stock_ledger import update_entries_after from accounts.utils import get_fiscal_year if not posting_date: posting_date = nowdate() if not posting_time: posting_time = nowtime() if not fiscal_year: fiscal_year = get_fiscal_year(posting_date)[0] condition = " and item.name='%s'" % item_code.replace("'", "\'") if item_code else "" bin = webnotes.conn.sql("""select bin.item_code, bin.warehouse, bin.actual_qty, item.stock_uom from `tabBin` bin, tabItem item where bin.item_code = item.name and item.has_serial_no = 'Yes' %s""" % condition) for d in bin: serial_nos = webnotes.conn.sql("""select count(name) from `tabSerial No` where item_code=%s and warehouse=%s and status = 'Available' and docstatus < 2""", (d[0], d[1])) if serial_nos and flt(serial_nos[0][0]) != flt(d[2]): print d[0], d[1], d[2], serial_nos[0][0] sle = webnotes.conn.sql("""select valuation_rate, company from `tabStock Ledger Entry` where item_code = %s and warehouse = %s and ifnull(is_cancelled, 'No') = 'No' order by posting_date desc limit 1""", (d[0], d[1])) sl_entries = { 'doctype' : 'Stock Ledger Entry', 'item_code' : d[0], 'warehouse' : d[1], 'transaction_date' : nowdate(), 'posting_date' : posting_date, 'posting_time' : posting_time, 'voucher_type' : 'Stock Reconciliation (Manual)', 'voucher_no' : '', 'voucher_detail_no' : '', 'actual_qty' : flt(serial_nos[0][0]) - flt(d[2]), 'stock_uom' : d[3], 'incoming_rate' : sle and flt(serial_nos[0][0]) > flt(d[2]) and flt(sle[0][0]) or 0, 'company' : sle and cstr(sle[0][1]) or 0, 'fiscal_year' : fiscal_year, 'is_cancelled' : 'No', 'batch_no' : '', 'serial_no' : '' } sle = Document(fielddata=sl_entries) sle = sle.insert() args = sl_entries.copy() args.update({ "sle_id": sle.name, "is_amended": 'No' }) update_bin(args) update_entries_after({ "item_code": d[0], "warehouse": d[1], "posting_date": posting_date, "posting_time": posting_time })
def repost_actual_qty(item_code, warehouse): from stock.stock_ledger import update_entries_after try: update_entries_after({ "item_code": item_code, "warehouse": warehouse }) except: pass
def on_rename(self, newdn, olddn, merge=False): if merge: from stock.stock_ledger import update_entries_after for item_code in webnotes.conn.sql("""select item_code from `tabBin` where warehouse=%s""", newdn): update_entries_after({"item_code": item_code, "warehouse": newdn})
def repost_actual_qty(self, item_code, warehouse=None): from stock.stock_ledger import update_entries_after if not warehouse: warehouse = self.doc.name update_entries_after({ "item_code": item_code, "warehouse": warehouse })
def repost_actual_qty(self, item_code, warehouse=None): from stock.stock_ledger import update_entries_after if not warehouse: warehouse = self.doc.name update_entries_after({ "item_code": item_code, "warehouse": warehouse })