def check_if_expired():
	"""check if account is expired. If expired, do not allow login"""
	import conf
	# check if expires_on is specified
	if not hasattr(conf, 'expires_on'): return
	
	# check if expired
	from datetime import datetime, date
	expires_on = datetime.strptime(conf.expires_on, '%Y-%m-%d').date()
	if date.today() <= expires_on: return
	
	# if expired, stop user from logging in
	from webnotes.utils import formatdate
	if 'System Manager' in webnotes.user.roles:
		webnotes.response['server_messages'] = """Oops! \
			Your subscription expired on <b>%s</b>.
			
			Nothing catastrophic.
			
			Just drop in a mail at <b>[email protected]</b> and \
			we will guide you to get your account re-activated.""" % formatdate(conf.expires_on)
	else:
		webnotes.response['server_messages'] = """Oops! \
			Your subscription expired on <b>%s</b>.
			
			Nothing catastrophic.
					
			Just ask your System Manager to drop in a mail at <b>[email protected]</b> and \
			we will guide him to get your account re-activated.""" % formatdate(conf.expires_on)
	
	webnotes.response['message'] = 'Account Expired'
	raise webnotes.AuthenticationError

	def get_msg_html(self, out):
		with_value = [o[1] for o in out if o[0]]
		
		if with_value:
			with_value = "\n".join(with_value)
		else:
			with_value = "<p>There were no updates in the items selected for this digest.</p>"
		
		# seperate out no value items
		no_value = [o[1] for o in out if not o[0]]
		if no_value:
			no_value = """<h4>No Updates For:</h4>""" + "\n".join(no_value)
		
		date = self.doc.frequency == "Daily" and formatdate(self.from_date) or \
			"%s to %s" % (formatdate(self.from_date), formatdate(self.to_date))
		
		msg = digest_template % {
				"digest": self.doc.frequency + " Digest",
				"date": date,
				"company": self.doc.company,
				"with_value": with_value,
				"no_value": no_value or ""
			}
		
		return msg

	def get_msg_html(self, out):
		with_value = "\n".join([o[1] for o in out if o[0]])
		
		# seperate out no value items
		no_value = [o[1] for o in out if not o[0]]
		if no_value:
			no_value = """<hr><h4>No Updates For:</h4><br>""" + "\n".join(no_value)
		
		date = self.doc.frequency == "Daily" and formatdate(self.from_date) or \
			"%s to %s" % (formatdate(self.from_date), formatdate(self.to_date))
		
		msg = """<h2>%(digest)s</h2>
			<p style='color: grey'>%(date)s</p>
			<h4>%(company)s</h4>
			<hr>
			%(with_value)s
			%(no_value)s""" % {
				"digest": self.doc.frequency + " Digest",
				"date": date,
				"company": self.doc.company,
				"with_value": with_value,
				"no_value": no_value or ""
			}
		
		return msg

    def validate_leave_overlap(self):
        if not self.doc.name:
            self.doc.name = "New Leave Application"

        for d in webnotes.conn.sql(
            """select name, leave_type, posting_date, 
			from_date, to_date 
			from `tabLeave Application` 
			where 
			employee = %(employee)s
			and docstatus < 2
			and status in ("Open", "Approved")
			and (from_date between %(from_date)s and %(to_date)s 
				or to_date between %(from_date)s and %(to_date)s
				or %(from_date)s between from_date and to_date)
			and name != %(name)s""",
            self.doc.fields,
            as_dict=1,
        ):

            msgprint(
                'Employee : %s has already applied for %s between %s and %s on %s. Please refer Leave Application : <a href="#Form/Leave Application/%s">%s</a>'
                % (
                    self.doc.employee,
                    cstr(d["leave_type"]),
                    formatdate(d["from_date"]),
                    formatdate(d["to_date"]),
                    formatdate(d["posting_date"]),
                    d["name"],
                    d["name"],
                ),
                raise_exception=OverlapError,
            )

	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 upload_accounts_transactions(self):
        import csv

        data = csv.reader(self.get_csv_data().splitlines())

        abbr = sql("select concat(' - ',abbr) as abbr from tabCompany where name=%s", self.doc.company)
        updated = 0
        jv_name = ""
        # 		jv = Document('Journal Voucher')
        global line, jv, name, jv_go
        for line in data:
            if len(line) >= 7:  # Minimum no of fields
                if line[3] != jv_name:  # Create JV
                    if jv_name != "":
                        jv_go = get_obj("Journal Voucher", name, with_children=1)
                        jv_go.validate()
                        jv_go.on_submit()

                    jv_name = line[3]
                    jv = Document("Journal Voucher")
                    jv.voucher_type = line[0]
                    jv.naming_series = line[1]
                    jv.voucher_date = formatdate(line[2])
                    jv.posting_date = formatdate(line[2])
                    # 					jv.name = line[3]
                    jv.fiscal_year = self.doc.fiscal_year
                    jv.company = self.doc.company
                    jv.remark = len(line) == 8 and line[3] + " " + line[7] or line[3] + " Uploaded Record"
                    jv.docstatus = 1
                    jv.save(1)
                    name = jv.name

                    jc = addchild(jv, "entries", "Journal Voucher Detail", 0)
                    jc.account = line[4] + abbr[0][0]
                    jc.cost_center = len(line) == 9 and line[8] or self.doc.default_cost_center
                    if line[5] != "":
                        jc.debit = line[5]
                    else:
                        jc.credit = line[6]
                    jc.save()

                else:  # Create JV Child
                    jc = addchild(jv, "entries", "Journal Voucher Detail", 0)
                    jc.account = line[4] + abbr[0][0]
                    jc.cost_center = len(line) == 9 and line[8] or self.doc.default_cost_center
                    if line[5] != "":
                        jc.debit = line[5]
                    else:
                        jc.credit = line[6]
                    jc.save()
            else:
                msgprint("[Ignored] Incorrect format: %s" % str(line))
        if jv_name != "":
            jv_go = get_obj("Journal Voucher", name, with_children=1)
            jv_go.validate()
            jv_go.on_submit()

        msgprint("<b>%s</b> items updated" % updated)

 def validate_bill_no(self):
     if self.doc.bill_no and self.doc.bill_no.lower().strip() not in ["na", "not applicable", "none"]:
         b_no = sql(
             "select bill_no, name, ifnull(is_opening,'') from `tabPurchase Invoice` where bill_no = '%s' and credit_to = '%s' and docstatus = 1 and name != '%s' "
             % (self.doc.bill_no, self.doc.credit_to, self.doc.name)
         )
         if b_no and cstr(b_no[0][2]) == cstr(self.doc.is_opening):
             msgprint(
                 "Please check you have already booked expense against Bill No. %s in Purchase Invoice %s"
                 % (cstr(b_no[0][0]), cstr(b_no[0][1]))
             )
             raise Exception, "Validation Error"
         if not self.doc.remarks:
             self.doc.remarks = (
                 (self.doc.remarks or "")
                 + "\n"
                 + ("Against Bill %s dated %s" % (self.doc.bill_no, formatdate(self.doc.bill_date)))
             )
             if self.doc.ded_amount:
                 self.doc.remarks = (
                     (self.doc.remarks or "")
                     + "\n"
                     + ("Grand Total: %s, Tax Deduction Amount: %s" % (self.doc.grand_total, self.doc.ded_amount))
                 )
     else:
         if not self.doc.remarks:
             self.doc.remarks = "No Remarks"

    def validate_block_days(self):
        from_date = getdate(self.doc.from_date)
        to_date = getdate(self.doc.to_date)

        department = webnotes.conn.get_value("Employee", self.doc.employee, "department")
        if department:
            block_list = webnotes.conn.get_value("Department", department, "holiday_block_list")
            if block_list:
                if self.is_user_in_allow_list(block_list):
                    return
                for d in webnotes.conn.sql(
                    """select block_date, reason from
					`tabHoliday Block List Date` where parent=%s""",
                    block_list,
                    as_dict=1,
                ):
                    block_date = getdate(d.block_date)
                    if block_date > from_date and block_date < to_date:
                        webnotes.msgprint(
                            _("You cannot apply for a leave on the following date because it is blocked")
                            + ": "
                            + formatdate(d.block_date)
                            + _(" Reason: ")
                            + d.reason
                        )
                        raise LeaveDayBlockedError

	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 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 datetime_in_user_format(date_time):
	if not date_time:
		return ""
	if isinstance(date_time, basestring):
		date_time = get_datetime(date_time)
	from webnotes.utils import formatdate
	return formatdate(date_time.date()) + " " + date_time.strftime("%H:%M")

def check_if_expired():
    """check if account is expired. If expired, do not allow login"""
    from webnotes import conf

    # check if expires_on is specified
    if not "expires_on" in conf:
        return

    # check if expired
    from datetime import datetime, date

    expires_on = datetime.strptime(conf.expires_on, "%Y-%m-%d").date()
    if date.today() <= expires_on:
        return

    # if expired, stop user from logging in
    from webnotes.utils import formatdate

    msg = """Oops! Your subscription expired on <b>%s</b>.<br>""" % formatdate(conf.expires_on)

    if "System Manager" in webnotes.user.get_roles():
        msg += """Just drop in a mail at <b>[email protected]</b> and
			we will guide you to get your account re-activated."""
    else:
        msg += """Just ask your System Manager to drop in a mail at <b>[email protected]</b> and
		we will guide him to get your account re-activated."""

    webnotes.msgprint(msg)

    webnotes.response["message"] = "Account Expired"
    raise webnotes.AuthenticationError

	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 get_transaction_list(doctype, start):
    # find customer id
    customer = webnotes.conn.get_value("Contact", {"email_id": webnotes.session.user}, "customer")

    if customer:
        transactions = webnotes.conn.sql(
            """select name, creation, currency, grand_total_export 
			from `tab%s` where customer=%s and docstatus=1
			order by creation desc
			limit %s, 20"""
            % (doctype, "%s", "%s"),
            (customer, cint(start)),
            as_dict=True,
        )
        for doc in transactions:
            doc.items = ", ".join(
                webnotes.conn.sql_list(
                    """select item_name
				from `tab%s Item` where parent=%s limit 5"""
                    % (doctype, "%s"),
                    doc.name,
                )
            )
            doc.creation = formatdate(doc.creation)
        return transactions
    else:
        return []

	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 get_transaction_list(doctype, start, additional_fields=None):
	# find customer id
	customer = webnotes.conn.get_value("Contact", {"email_id": webnotes.session.user}, 
		"customer")
	
	if customer:
		if additional_fields:
			additional_fields = ", " + ", ".join(("`%s`" % f for f in additional_fields))
		else:
			additional_fields = ""
			
		transactions = webnotes.conn.sql("""select name, creation, currency, grand_total_export
			%s
			from `tab%s` where customer=%s and docstatus=1
			order by creation desc
			limit %s, 20""" % (additional_fields, doctype, "%s", "%s"), 
			(customer, cint(start)), as_dict=True)
		for doc in transactions:
			items = webnotes.conn.sql_list("""select item_name
				from `tab%s Item` where parent=%s limit 6""" % (doctype, "%s"), doc.name)
			doc.items = ", ".join(items[:5]) + ("..." if (len(items) > 5) else "")
			doc.creation = formatdate(doc.creation)
		return transactions
	else:
		return []

def validate_fiscal_year(date, fiscal_year, label="Date"):
    years = [f[0] for f in get_fiscal_years(date, label=label)]
    if fiscal_year not in years:
        webnotes.msgprint(
            ("%(label)s '%(posting_date)s': " + _("not within Fiscal Year") + ": '%(fiscal_year)s'")
            % {"label": label, "posting_date": formatdate(date), "fiscal_year": fiscal_year},
            raise_exception=1,
        )

def get_emails(start=0):
	emails = webnotes.conn.sql("""select name, subject, status, creation 
		from `tabEmail Inbox` where raised_by=%s 
		order by modified desc
		limit %s, 20""", (webnotes.session.user, cint(start)), as_dict=True)
	for t in emails:
		t.creation = formatdate(t.creation)
	
	return emails

	def validate_fiscal_year(self, fiscal_year, transaction_date, label):
		from accounts.utils import get_fiscal_year
		if get_fiscal_year(transaction_date)[0] != fiscal_year:
			msgprint(("%(label)s '%(posting_date)s': " + _("not within Fiscal Year") + \
				": '%(fiscal_year)s'") % {
					"label": label,
					"posting_date": formatdate(transaction_date),
					"fiscal_year": fiscal_year
				}, raise_exception=1)

def get_tickets(start=0):
	tickets = webnotes.conn.sql("""select name, subject, status, creation 
		from `tabSupport Ticket` where raised_by=%s 
		order by modified desc
		limit %s, 20""", (webnotes.session.user, cint(start)), as_dict=True)
	for t in tickets:
		t.creation = formatdate(t.creation)
	
	return tickets