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 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_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 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 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 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 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):
		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 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
	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"):
				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 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 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 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
		})