def create_remarks(self):
		r = []
		if self.doc.cheque_no :
			if self.doc.cheque_date:
				r.append('Via Reference #%s dated %s' % 
					(self.doc.cheque_no, formatdate(self.doc.cheque_date)))
			else :
				msgprint("Please enter Reference date", raise_exception=1)
		
		for d in getlist(self.doclist, 'entries'):
			if d.against_invoice and d.credit:
				currency = webnotes.conn.get_value("Sales Invoice", d.against_invoice, "currency")
				r.append('%s %s against Invoice: %s' % 
					(cstr(currency), fmt_money(flt(d.credit)), d.against_invoice))
					
			if d.against_voucher and d.debit:
				bill_no = webnotes.conn.sql("""select bill_no, bill_date, currency 
					from `tabPurchase Invoice` where name=%s""", d.against_voucher)
				if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() \
						not in ['na', 'not applicable', 'none']:
					r.append('%s %s against Bill %s dated %s' % 
						(cstr(bill_no[0][2]), fmt_money(flt(d.debit)), bill_no[0][0], 
						bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d')) or ''))
	
		if self.doc.user_remark:
			r.append("User Remark : %s"%self.doc.user_remark)

		if r:
			self.doc.remark = ("\n").join(r)
		else:
			webnotes.msgprint("User Remarks is mandatory", raise_exception=1)

	def check_credit_limit(self, account, company, tot_outstanding):
		# Get credit limit
		credit_limit_from = 'Customer'

		cr_limit = sql("select t1.credit_limit from tabCustomer t1, `tabAccount` t2 where t2.name='%s' and t1.name = t2.master_name" % account)
		credit_limit = cr_limit and flt(cr_limit[0][0]) or 0
		if not credit_limit:
			credit_limit = get_value('Company', company, 'credit_limit')
			credit_limit_from = 'global settings in the Company'
		
		# If outstanding greater than credit limit and not authorized person raise exception
		if credit_limit > 0 and flt(tot_outstanding) > credit_limit and not self.get_authorized_user():
			msgprint("Total Outstanding amount (%s) for <b>%s</b> can not be greater than credit limit (%s). To change your credit limit settings, please update the <b>%s</b>" \
				% (fmt_money(tot_outstanding), account, fmt_money(credit_limit), credit_limit_from), raise_exception=1)

def decorate_quotation_doclist(doclist):
	for d in doclist:
		if d.item_code:
			d.fields.update(webnotes.conn.get_value("Item", d.item_code, 
				["website_image", "description", "page_name"], as_dict=True))
			d.formatted_rate = fmt_money(d.export_rate, currency=doclist[0].currency)
			d.formatted_amount = fmt_money(d.export_amount, currency=doclist[0].currency)
		elif d.charge_type:
			d.formatted_tax_amount = fmt_money(d.tax_amount / doclist[0].conversion_rate,
				currency=doclist[0].currency)

	doclist[0].formatted_grand_total_export = fmt_money(doclist[0].grand_total_export,
		currency=doclist[0].currency)
	
	return [d.fields for d in doclist]

	def convert_to_simple_type(self, v, formatted=0):
		import datetime
		from webnotes.utils import formatdate, fmt_money

		# date
		if type(v)==datetime.date:
			v = str(v)
			if formatted:
				v = formatdate(v)
		
		# time	
		elif type(v)==datetime.timedelta:
			h = int(v.seconds/60/60)
			v = str(h) + ':' + str(v.seconds/60 - h*60)
			if v[1]==':': 
				v='0'+v
		
		# datetime
		elif type(v)==datetime.datetime:
			v = str(v)
		
		# long
		elif type(v)==long: 
			v=int(v)
		
		# convert to strings... (if formatted)
		if formatted:
			if type(v)==float:
				v=fmt_money(v)
			if type(v)==int:
				v=str(v)
		
		return v

	def create_remarks(self):
		r = []
		if self.doc.cheque_no :
			if self.doc.cheque_date:
				r.append('Via Reference #%s dated %s' % (self.doc.cheque_no, formatdate(self.doc.cheque_date)))
			else :
				msgprint("Please enter Reference date")
				raise Exception
		
		for d in getlist(self.doclist, 'entries'):
			if d.against_invoice and d.credit:
				currency = sql("select currency from `tabSales Invoice` where name = '%s'" % d.against_invoice)
				currency = currency and currency[0][0] or ''
				r.append('%s %s against Invoice: %s' % (cstr(currency), fmt_money(flt(d.credit)), d.against_invoice))
			if d.against_voucher and d.debit:
				bill_no = sql("select bill_no, bill_date, currency from `tabPurchase Invoice` where name=%s", d.against_voucher)
				if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() not in ['na', 'not applicable', 'none']:
					bill_no = bill_no and bill_no[0]
					r.append('%s %s against Bill %s dated %s' % (bill_no[2] and cstr(bill_no[2]) or '', fmt_money(flt(d.debit)), bill_no[0], bill_no[1] and formatdate(bill_no[1].strftime('%Y-%m-%d')) or ''))
	
		if self.doc.user_remark:
			r.append("User Remark : %s"%self.doc.user_remark)

		if r:
			self.doc.remark = ("\n").join(r)

def update_outstanding_amt(account, against_voucher_type, against_voucher, on_cancel=False):
	# get final outstanding amt
	bal = flt(webnotes.conn.sql("""select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0)) 
		from `tabGL Entry` 
		where against_voucher_type=%s and against_voucher=%s and account = %s""", 
		(against_voucher_type, against_voucher, account))[0][0] or 0.0)

	if against_voucher_type == 'Purchase Invoice':
		bal = -bal
	elif against_voucher_type == "Journal Voucher":
		against_voucher_amount = flt(webnotes.conn.sql("""
			select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
			from `tabGL Entry` where voucher_type = 'Journal Voucher' and voucher_no = %s
			and account = %s and ifnull(against_voucher, '') = ''""", 
			(against_voucher, account))[0][0])
		bal = against_voucher_amount + bal
		if against_voucher_amount < 0:
			bal = -bal
	
	# Validation : Outstanding can not be negative
	if bal < 0 and not on_cancel:
		webnotes.throw(_("Outstanding for Voucher ") + against_voucher + _(" will become ") + 
			fmt_money(bal) + _(". Outstanding cannot be less than zero. \
			 	Please match exact outstanding."))
		
	# Update outstanding amt on against voucher
	if against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
		webnotes.conn.sql("update `tab%s` set outstanding_amount=%s where name='%s'" %
		 	(against_voucher_type, bal, against_voucher))

	def update_outstanding_amt(self):
		# get final outstanding amt
		bal = flt(sql("""select sum(debit) - sum(credit) from `tabGL Entry` 
			where against_voucher=%s and against_voucher_type=%s 
			and ifnull(is_cancelled,'No') = 'No'""", 
			(self.doc.against_voucher, self.doc.against_voucher_type))[0][0] or 0.0)
		
		if self.doc.against_voucher_type == 'Purchase Invoice':
			bal = -bal
			
		elif self.doc.against_voucher_type == "Journal Voucher":
			against_voucher_amount = flt(webnotes.conn.sql("""select sum(debit) - sum(credit)
				from `tabGL Entry` where voucher_type = 'Journal Voucher' and voucher_no = %s
				and account = %s""", (self.doc.against_voucher, self.doc.account))[0][0])
			
			bal = against_voucher_amount + bal
			if against_voucher_amount < 0:
				bal = -bal
			
		# Validation : Outstanding can not be negative
		if bal < 0 and self.doc.is_cancelled == 'No':
			msgprint(_("Outstanding for Voucher ") + self.doc.against_voucher + 
				_(" will become ") + fmt_money(bal) + _(". Outstanding cannot be less than zero. \
				 	Please match exact outstanding."), raise_exception=1)
			
		# Update outstanding amt on against voucher
		if self.doc.against_voucher_type in ["Sales Invoice", "Purchase Invoice"]:
			sql("update `tab%s` set outstanding_amount=%s where name='%s'"%
			 	(self.doc.against_voucher_type, bal, self.doc.against_voucher))

	def convert_to_simple_type(self, v, formatted=0):
		import datetime
		from webnotes.utils import formatdate, fmt_money

		# date
		if type(v)==datetime.date:
			v = unicode(v)
			if formatted:
				v = formatdate(v)
		
		# time	
		elif type(v)==datetime.timedelta:
			v = unicode(v)
		
		# datetime
		elif type(v)==datetime.datetime:
			v = unicode(v)
		
		# long
		elif type(v)==long: 
			v=int(v)
		
		# convert to strings... (if formatted)
		if formatted:
			if type(v)==float:
				v=fmt_money(v)
			if type(v)==int:
				v=str(v)
		
		return v

	def convert_to_simple_type(self, v, formatted=0):
		from webnotes.utils import formatdate, fmt_money

		if isinstance(v, (datetime.date, datetime.timedelta, datetime.datetime, long)):
			if isinstance(v, datetime.date):
				v = unicode(v)
				if formatted:
					v = formatdate(v)
		
			# time
			elif isinstance(v, (datetime.timedelta, datetime.datetime)):
				v = unicode(v)
				
			# long
			elif isinstance(v, long): 
				v=int(v)

		# convert to strings... (if formatted)
		if formatted:
			if isinstance(v, float):
				v=fmt_money(v)
			elif isinstance(v, int):
				v = unicode(v)

		return v

def get_children():
	args = webnotes.form_dict
	ctype, company = args['ctype'], args['comp']
	
	company_field = ctype=='Account' and 'company' or 'company_name'

	# root
	if args['parent'] == company:
		acc = webnotes.conn.sql(""" select 
			name as value, if(group_or_ledger='Group', 1, 0) as expandable
			from `tab%s`
			where ifnull(parent_%s,'') = ''
			and %s = %s	and docstatus<2 
			order by name""" % (ctype, ctype.lower().replace(' ','_'), company_field, '%s'),
				args['parent'], as_dict=1)
	else:	
		# other
		acc = webnotes.conn.sql("""select 
			name as value, if(group_or_ledger='Group', 1, 0) as expandable
	 		from `tab%s` 
			where ifnull(parent_%s,'') = %s
			and docstatus<2 
			order by name""" % (ctype, ctype.lower().replace(' ','_'), '%s'),
				args['parent'], as_dict=1)
				
	if ctype == 'Account':
		currency = webnotes.conn.sql("select default_currency from `tabCompany` where name = %s", company)[0][0]
		for each in acc:
			bal = get_balance_on(each.get("value"))
			each['balance'] = currency + ' ' + fmt_money(bal)
		
	return acc

	def get_new_sum(self, doctype, label, sum_field):
		count_sum = webnotes.conn.sql("""select count(*), sum(ifnull(`%s`, 0))
			from `tab%s` where docstatus < 2 and company = %s and
			date(creation)>=%s and date(creation)<=%s""" % (sum_field, doctype, "%s",
			"%s", "%s"), (self.doc.company, self.from_date, self.to_date))
		count, total = count_sum and count_sum[0] or (0, 0)
		
		return count, self.get_html(label, self.currency, 
			"%s - (%s)" % (fmt_money(total), cstr(count)))

	def check_credit_limit(self, total_outstanding):
		# Get credit limit
		credit_limit_from = 'Customer'

		cr_limit = webnotes.conn.sql("""select t1.credit_limit from tabCustomer t1, `tabAccount` t2 
			where t2.name=%s and t1.name = t2.master_name""", self.doc.name)
		credit_limit = cr_limit and flt(cr_limit[0][0]) or 0
		if not credit_limit:
			credit_limit = webnotes.conn.get_value('Company', self.doc.company, 'credit_limit')
			credit_limit_from = 'Company'
		
		# If outstanding greater than credit limit and not authorized person raise exception
		if credit_limit > 0 and flt(total_outstanding) > credit_limit \
				and not self.get_authorized_user():
			msgprint("""Total Outstanding amount (%s) for <b>%s</b> can not be \
				greater than credit limit (%s). To change your credit limit settings, \
				please update in the <b>%s</b> master""" % (fmt_money(total_outstanding), 
				self.doc.name, fmt_money(credit_limit), credit_limit_from), raise_exception=1)

def modify_status(doc):
	doc.status = ""
	if flt(doc.outstanding_amount):
		doc.status = '<span class="label %s"><i class="icon-fixed-width %s"></i> %s</span>' % \
			("label-warning", "icon-exclamation-sign", 
			_("To Pay") + " = " + fmt_money(doc.outstanding_amount, currency=doc.currency))
	else:
		doc.status = '<span class="label %s"><i class="icon-fixed-width %s"></i> %s</span>' % \
			("label-success", "icon-ok", _("Paid"))
		

    def get_booked_total(self, party_type, gle_field, label):
        # account is of master_type Customer or Supplier
        accounts = [a["name"] for a in self.get_accounts() if a["master_type"] == party_type]

        total = 0
        for gle in self.get_gl_entries(self.from_date, self.to_date):
            if gle["account"] in accounts:
                total += gle[gle_field]

        return total, self.get_html(label, self.currency, fmt_money(total))

	def get_income(self, from_date=None, label=None):
		# account is PL Account and Credit type account
		accounts = [a["name"] for a in self.get_accounts()
			if a["is_pl_account"]=="Yes" and a["debit_or_credit"]=="Credit"]
			
		income = 0
		for gle in self.get_gl_entries(from_date or self.from_date, self.to_date):
			if gle["account"] in accounts:
				income += gle["credit"] - gle["debit"]
		
		return income, self.get_html(label or "Income", self.currency, fmt_money(income))

	def get_expenses_booked(self):
		# account is PL Account and Debit type account
		accounts = [a["name"] for a in self.get_accounts()
			if a["is_pl_account"]=="Yes" and a["debit_or_credit"]=="Debit"]
			
		expense = 0
		for gle in self.get_gl_entries(self.from_date, self.to_date):
			if gle["account"] in accounts:
				expense += gle["debit"] - gle["credit"]
		
		return expense, self.get_html("Expenses", self.currency, fmt_money(expense))

	def get_bank_balance(self):
		# account is of type "Bank or Cash"
		accounts = dict([[a["name"], [a["account_name"], 0]] for a in self.get_accounts()
			if a["account_type"]=="Bank or Cash"])
		ackeys = accounts.keys()
		
		for gle in self.get_gl_entries(None, self.to_date):
			if gle["account"] in ackeys:
				accounts[gle["account"]][1] += gle["debit"] - gle["credit"]
		
		# build html
		out = self.get_html("Bank/Cash Balance", "", "")
		for ac in ackeys:
			if accounts[ac][1]:
				out += "\n" + self.get_html(accounts[ac][0], self.currency,
					fmt_money(accounts[ac][1]), style="margin-left: 17px")
		return sum((accounts[ac][1] for ac in ackeys)), out

    def get_party_total(self, party_type, gle_field, label):
        import re

        # account is of master_type Customer or Supplier
        accounts = [a["name"] for a in self.get_accounts() if a["master_type"] == party_type]

        # account is "Bank or Cash"
        bc_accounts = [esc(a["name"], "()|") for a in self.get_accounts() if a["account_type"] == "Bank or Cash"]
        bc_regex = re.compile("""(%s)""" % "|".join(bc_accounts))

        total = 0
        for gle in self.get_gl_entries(self.from_date, self.to_date):
            # check that its made against a bank or cash account
            if gle["account"] in accounts and gle["against"] and bc_regex.findall(gle["against"]):
                val = gle["debit"] - gle["credit"]
                total += (gle_field == "debit" and 1 or -1) * val

        return total, self.get_html(label, self.currency, fmt_money(total))

	def update_outstanding_amt(self):
		# get final outstanding amt
		bal = flt(sql("select sum(debit)-sum(credit) from `tabGL Entry` where against_voucher=%s and against_voucher_type=%s and ifnull(is_cancelled,'No') = 'No'", (self.doc.against_voucher, self.doc.against_voucher_type))[0][0] or 0.0)
		
		if self.doc.against_voucher_type=='Purchase Invoice':
			# amount to debit
			bal = -bal
			
		# Validation : Outstanding can not be negative
		if bal < 0 and self.doc.is_cancelled == 'No':
			msgprint("""Outstanding for Voucher %s will become %s. 
				Outstanding cannot be less than zero. Please match exact outstanding.""" % 
				 (self.doc.against_voucher, fmt_money(bal)))
			raise Exception
			
		# Update outstanding amt on against voucher
		sql("update `tab%s` set outstanding_amount=%s where name='%s'"%
		 	(self.doc.against_voucher_type, bal, self.doc.against_voucher))

def get_product_info(item_code):
	"""get product price / stock info"""
	if not cint(webnotes.conn.get_default("shopping_cart_enabled")):
		return {}
	
	cart_quotation = _get_cart_quotation()
	
	price_list = webnotes.cookies.get("selling_price_list").value

	warehouse = webnotes.conn.get_value("Item", item_code, "website_warehouse")
	if warehouse:
		in_stock = webnotes.conn.sql("""select actual_qty from tabBin where
			item_code=%s and warehouse=%s""", (item_code, warehouse))
		if in_stock:
			in_stock = in_stock[0][0] > 0 and 1 or 0
	else:
		in_stock = -1
		
	price = price_list and webnotes.conn.sql("""select ip.ref_rate, pl.currency from
		`tabItem Price` ip, `tabPrice List` pl where ip.parent = pl.name and 
		ip.item_code=%s and ip.parent=%s""", 
		(item_code, price_list), as_dict=1) or []
	
	price = price and price[0] or None
	qty = 0

	if price:
		price["formatted_price"] = fmt_money(price["ref_rate"], currency=price["currency"])
		
		price["currency"] = not cint(webnotes.conn.get_default("hide_currency_symbol")) \
			and (webnotes.conn.get_value("Currency", price.currency, "symbol") or price.currency) \
			or ""
		
		if webnotes.session.user != "Guest":
			item = cart_quotation.doclist.get({"item_code": item_code})
			if item:
				qty = item[0].qty

	return {
		"price": price,
		"stock": in_stock,
		"uom": webnotes.conn.get_value("Item", item_code, "stock_uom"),
		"qty": qty
	}