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