<commit_before>import webnotes

def execute():
	# add index
	webnotes.conn.commit()
	webnotes.conn.sql("""create index item_code_warehouse
		on `tabDelivery Note Packing Item` (item_code, warehouse)""")
	webnotes.conn.begin()

	repost_reserved_qty()
	cleanup_wrong_sle()

def repost_reserved_qty():
	from webnotes.utils import flt
	bins = webnotes.conn.sql("select item_code, warehouse, name, reserved_qty from `tabBin`")
	i = 0
	for d in bins:
		i += 1
		print i
		reserved_qty = webnotes.conn.sql("""
			select sum((dnpi_qty / so_item_qty) * (so_item_qty - so_item_delivered_qty))
			from (select
			qty as dnpi_qty,
			(
				select qty from `tabSales Order Item`
				where name = dnpi.parent_detail_docname
			) as so_item_qty,
			(
				select ifnull(delivered_qty, 0) from `tabSales Order Item`
				where name = dnpi.parent_detail_docname
			) as so_item_delivered_qty
			from 
			(
				select qty, parent_detail_docname
				from `tabDelivery Note Packing Item` dnpi_in
				where item_code = %s and warehouse = %s
				and parenttype="Sales Order"
				and exists (select * from `tabSales Order` so
				where name = dnpi_in.parent and docstatus = 1 and status != 'Stopped')
			) dnpi) tab""", (d[0], d[1]))

		if flt(d[3]) != flt(reserved_qty[0][0]):
			print d[3], reserved_qty[0][0]
			webnotes.conn.sql("""
				update `tabBin` set reserved_qty = %s where name = %s
			""", (reserved_qty and reserved_qty[0][0] or 0, d[2]))
		
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])
			repost_bin(d[0], d[1])
	
def create_comment(dn):
	from webnotes.model.doc import Document
	cmt = Document('Comment')
	for arg in ['comment', 'comment_by', 'comment_by_fullname', 'comment_doctype', \
		'comment_docname']:
		cmt.fields[arg] = args[arg]
	cmt.comment = 'Cancelled by administrator due to wrong entry in packing list'
	cmt.comment_by = 'Administrator'
	cmt.comment_by_fullname = 'Administrator'
	cmt.comment_doctype = 'Stock Ledger Entry'
	cmt.comment_docname = dn
	cmt.save(1)
	
	
def repost_bin(item, wh):
	from webnotes.model.code import get_obj	
	bin = webnotes.conn.sql("select name from `tabBin` \
		where item_code = %s and warehouse = %s", (item, wh))
			
	get_obj('Bin', bin[0][0]).update_entries_after(posting_date = '2012-07-01', posting_time = '12:05')<commit_msg>fixed patch: repost stock due to wrong packing list<commit_after>import webnotes

def execute():
	# add index
	webnotes.conn.commit()
	try:
		webnotes.conn.sql("""create index item_code_warehouse
			on `tabDelivery Note Packing Item` (item_code, warehouse)""")
	except:
		pass
	webnotes.conn.begin()

	repost_reserved_qty()
	cleanup_wrong_sle()

def repost_reserved_qty():
	from webnotes.utils import flt
	bins = webnotes.conn.sql("select item_code, warehouse, name, reserved_qty from `tabBin`")
	i = 0
	for d in bins:
		i += 1
		print i
		reserved_qty = webnotes.conn.sql("""
			select sum((dnpi_qty / so_item_qty) * (so_item_qty - so_item_delivered_qty))
			from (select
			qty as dnpi_qty,
			(
				select qty from `tabSales Order Item`
				where name = dnpi.parent_detail_docname
			) as so_item_qty,
			(
				select ifnull(delivered_qty, 0) from `tabSales Order Item`
				where name = dnpi.parent_detail_docname
			) as so_item_delivered_qty
			from 
			(
				select qty, parent_detail_docname
				from `tabDelivery Note Packing Item` dnpi_in
				where item_code = %s and warehouse = %s
				and parenttype="Sales Order"
				and exists (select * from `tabSales Order` so
				where name = dnpi_in.parent and docstatus = 1 and status != 'Stopped')
			) dnpi) tab""", (d[0], d[1]))

		if flt(d[3]) != flt(reserved_qty[0][0]):
			print d[3], reserved_qty[0][0]
			webnotes.conn.sql("""
				update `tabBin` set reserved_qty = %s where name = %s
			""", (reserved_qty and reserved_qty[0][0] or 0, d[2]))
		
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])
			repost_bin(d[0], d[1])
	
def create_comment(dn):
	from webnotes.model.doc import Document
	cmt = Document('Comment')
	cmt.comment = 'Cancelled by administrator due to wrong entry in packing list'
	cmt.comment_by = 'Administrator'
	cmt.comment_by_fullname = 'Administrator'
	cmt.comment_doctype = 'Stock Ledger Entry'
	cmt.comment_docname = dn
	cmt.save(1)
	
	
def repost_bin(item, wh):
	from webnotes.model.code import get_obj	
	bin = webnotes.conn.sql("select name from `tabBin` \
		where item_code = %s and warehouse = %s", (item, wh))
			
	get_obj('Bin', bin[0][0]).update_entries_after(posting_date = '2012-07-01', posting_time = '12:05')
{'fraction_non_alphanumeric': 0.11716573816155988, 'fraction_numerical': 0.012186629526462395, 'mean_word_length': 3.9913119026933104, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3751005', 'n_tokens_mistral': 2437, 'n_tokens_neox': 2144, 'n_words': 562}