def get_period_date_ranges(period, fiscal_year=None, year_start_date=None):
	from dateutil.relativedelta import relativedelta

	if not year_start_date:
		year_start_date, year_end_date = webnotes.conn.get_value("Fiscal Year", 
			fiscal_year, ["year_start_date", "year_end_date"])

	increment = {
		"Monthly": 1,
		"Quarterly": 3,
		"Half-Yearly": 6,
		"Yearly": 12
	}.get(period)

	period_date_ranges = []
	for i in xrange(1, 13, increment):
		period_end_date = getdate(year_start_date) + relativedelta(months=increment, days=-1)
		if period_end_date > getdate(year_end_date):
			period_end_date = year_end_date
		period_date_ranges.append([year_start_date, period_end_date])
		year_start_date = period_end_date + relativedelta(days=1)
		if period_end_date == year_end_date:
			break

	return period_date_ranges

def send_notification(new_rv):
	"""Notify concerned persons about recurring invoice generation"""
	subject = "Invoice : " + new_rv.doc.name

	com = new_rv.doc.company   # webnotes.conn.get_value('Control Panel', '', 'letter_head')

	hd = '''<div><h2>%s</h2></div>
			<div><h3>Invoice: %s</h3></div>
			<table cellspacing= "5" cellpadding="5"  width = "100%%">
				<tr>
					<td width = "50%%"><b>Customer</b><br>%s<br>%s</td>
					<td width = "50%%">Invoice Date	   : %s<br>Invoice Period : %s to %s <br>Due Date	   : %s</td>
				</tr>
			</table>
		''' % (com, new_rv.doc.name, new_rv.doc.customer_name, new_rv.doc.address_display, getdate(new_rv.doc.posting_date).strftime("%d-%m-%Y"), \
		getdate(new_rv.doc.invoice_period_from_date).strftime("%d-%m-%Y"), getdate(new_rv.doc.invoice_period_to_date).strftime("%d-%m-%Y"),\
		getdate(new_rv.doc.due_date).strftime("%d-%m-%Y"))
	
	
	tbl = '''<table border="1px solid #CCC" width="100%%" cellpadding="0px" cellspacing="0px">
				<tr>
					<td width = "15%%" bgcolor="#CCC" align="left"><b>Item</b></td>
					<td width = "40%%" bgcolor="#CCC" align="left"><b>Description</b></td>
					<td width = "15%%" bgcolor="#CCC" align="center"><b>Qty</b></td>
					<td width = "15%%" bgcolor="#CCC" align="center"><b>Rate</b></td>
					<td width = "15%%" bgcolor="#CCC" align="center"><b>Amount</b></td>
				</tr>
		'''
	for d in getlist(new_rv.doclist, 'entries'):
		tbl += '<tr><td>' + d.item_code +'</td><td>' + d.description+'</td><td>' + cstr(d.qty) +'</td><td>' + cstr(d.basic_rate) +'</td><td>' + cstr(d.amount) +'</td></tr>'
	tbl += '</table>'

	totals ='''<table cellspacing= "5" cellpadding="5"  width = "100%%">
					<tr>
						<td width = "50%%"></td>
						<td width = "50%%">
							<table width = "100%%">
								<tr>
									<td width = "50%%">Net Total: </td><td>%s </td>
								</tr><tr>
									<td width = "50%%">Total Tax: </td><td>%s </td>
								</tr><tr>
									<td width = "50%%">Grand Total: </td><td>%s</td>
								</tr><tr>
									<td width = "50%%">In Words: </td><td>%s</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr><td>Terms and Conditions:</td></tr>
					<tr><td>%s</td></tr>
				</table>
			''' % (new_rv.doc.net_total,
			new_rv.doc.other_charges_total,new_rv.doc.grand_total,
			new_rv.doc.in_words,new_rv.doc.terms)


	msg = hd + tbl + totals
	
	sendmail(new_rv.doc.notification_email_address, subject=subject, msg = msg)

	def notify(self):
		import datetime
		from webnotes.utils import getdate
		serial_nos = webnotes.conn.sql(\
				"""SELECT name, warranty_expiry_date,\
					  amc_expiry_date, warranty_amc_status,\
					  brand, product_code,\
					  customer, territory\
				   FROM `tabSerial No`\
				   WHERE (((DATEDIFF(NOW(),warranty_expiry_date) between -30 and 365) AND ifnull(amc_expiry_date, '') = '')\
				   OR (DATEDIFF(NOW(), amc_expiry_date) between -30 and 365 AND warranty_amc_status = 'Under AMC'))\
				   AND warranty_amc_status in ('Under Warranty', 'Under AMC')\
				""", as_dict=1)
		for s in serial_nos:
			status = s['warranty_amc_status']
			try:
				amc_ex_date = getdate(s['amc_expiry_date'])
			except:
				amc_ex_date = s['amc_expiry_date']
			try:
				warranty_ex_date = getdate(s['warranty_expiry_date'])
			except:
				warranty_ex_date = s['warranty_expiry_date']
			days = 1 # intializing to 1 coz it wont be true even if we dont have warranty or expiry date
			if status == 'Under Warranty' and warranty_ex_date:
				days = (datetime.datetime.now().date() - warranty_ex_date).days
			elif status == 'Under AMC' and amc_ex_date:
				days = (datetime.datetime.now().date() - amc_ex_date).days
			if days % 30 == 0 and s['territory']:
				self.send_mail(s, days)

	def update_production_order(self, is_submit):
		if self.doc.production_order:
			pro_obj = get_obj("Production Order", self.doc.production_order)
			if flt(pro_obj.doc.docstatus) != 1:
				msgprint("""You cannot do any transaction against 
					Production Order : %s, as it's not submitted"""
					% (pro_obj.doc.name), raise_exception=1)
					
			if pro_obj.doc.status == 'Stopped':
				msgprint("""You cannot do any transaction against Production Order : %s, 
					as it's status is 'Stopped'"""% (pro_obj.doc.name), raise_exception=1)
					
			if getdate(pro_obj.doc.posting_date) > getdate(self.doc.posting_date):
				msgprint("""Posting Date of Stock Entry cannot be before Posting Date of 
					Production Order: %s"""% cstr(self.doc.production_order), raise_exception=1)
					
			if self.doc.process == 'Backflush':
				pro_obj.doc.produced_qty = flt(pro_obj.doc.produced_qty) + \
					(is_submit and 1 or -1 ) * flt(self.doc.fg_completed_qty)
				args = {
					"item_code": pro_obj.doc.production_item,
					"posting_date": self.doc.posting_date,
					"planned_qty": (is_submit and -1 or 1 ) * flt(self.doc.fg_completed_qty)
				}
				get_obj('Warehouse', pro_obj.doc.fg_warehouse).update_bin(args)
				
			pro_obj.doc.status = (flt(pro_obj.doc.qty)==flt(pro_obj.doc.produced_qty)) \
				and 'Completed' or 'In Process'
			pro_obj.doc.save()

def execute(filters=None):
	priority_map = {"High": 3, "Medium": 2, "Low": 1}
	
	todo_list = runreport(doctype="ToDo", fields=["name", "date", "description",
		"priority", "reference_type", "reference_name", "assigned_by", "owner"], 
		filters=[["ToDo", "checked", "!=", 1]])
	
	todo_list.sort(key=lambda todo: (priority_map.get(todo.priority, 0), 
		todo.date and getdate(todo.date) or getdate("1900-01-01")), reverse=True)
		
	columns = [_("ID")+":Link/ToDo:90", _("Priority")+"::60", _("Date")+ ":Date", 
		_("Description")+"::150", _("Assigned To/Owner") + ":Link/Profile:120", 
		_("Assigned By")+":Link/Profile:120", _("Reference")+"::200"]

	result = []
	for todo in todo_list:
		if todo.reference_type:
			todo.reference = """<a href="#Form/%s/%s">%s: %s</a>""" % \
				(todo.reference_type, todo.reference_name, todo.reference_type, todo.reference_name)
		else:
			todo.reference = None
		result.append([todo.name, todo.priority, todo.date, todo.description,
			todo.owner, todo.assigned_by, todo.reference])
	
	return columns, result

        def get_weekly_off_date_list(self, year_start_date, year_end_date):
                from webnotes.utils import getdate
                year_start_date, year_end_date = getdate(year_start_date), getdate(year_end_date)

                from dateutil import relativedelta
                from datetime import timedelta
                import calendar

                date_list = []
                date_list1 = []
                if self.doc.weekly_off=='3rd Saturday':
                        webnotes.errprint(self.doc.weekly_off)
                        weekday = getattr(calendar, ('Saturday').upper())
                        reference_date = year_start_date + relativedelta.relativedelta(weekday=weekday)
                        while reference_date <= year_end_date:
                                date_list1.append(reference_date)
                                reference_date += timedelta(days=7)
                        for dt in date_list1:
                                if dt.day>14 :
                                        if dt.day <22:
                                                #webnotes.errprint(dt)
                                                ch = addchild(self.doc, 'holiday_list_details', 'Holiday', self.doclist)
                                                ch.description = self.doc.weekly_off
                                                ch.holiday_date = dt
                        return date_list
                else:
                        weekday = getattr(calendar, (self.doc.weekly_off).upper())
                        reference_date = year_start_date + relativedelta.relativedelta(weekday=weekday)
                        while reference_date <= year_end_date:
                                date_list.append(reference_date)
                                reference_date += timedelta(days=7)
                        return date_list

    def validate_posting_date(self):
        yr = sql(
            """select year_start_date, adddate(year_start_date, interval 1 year)
			from `tabFiscal Year` where name=%s""",
            (self.doc.fiscal_year,),
        )
        self.year_start_date = yr and yr[0][0] or ""
        self.year_end_date = yr and yr[0][1] or ""

        # Posting Date should be within closing year
        if getdate(self.doc.posting_date) < getdate(self.year_start_date) or getdate(self.doc.posting_date) > getdate(
            self.year_end_date
        ):
            msgprint("Posting Date should be within Closing Fiscal Year")
            raise Exception

            # Period Closing Entry
        pce = sql(
            "select name from `tabPeriod Closing Voucher` \
			where posting_date > '%s' and fiscal_year = '%s' and docstatus = 1"
            % (self.doc.posting_date, self.doc.fiscal_year)
        )
        if pce and pce[0][0]:
            msgprint(
                "Another Period Closing Entry: %s has been made after posting date: %s"
                % (cstr(pce[0][0]), self.doc.posting_date)
            )
            raise Exception

    def validate(self):
        if getdate(self.doc.timesheet_date) > getdate(nowdate()):
            msgprint("You can not prepare timesheet for future date")
            raise Exception

        chk = sql(
            "select name from `tabTimesheet` where timesheet_date=%s and owner=%s and status!='Cancelled' and name!=%s",
            (self.doc.timesheet_date, self.doc.owner, self.doc.name),
        )
        if chk:
            msgprint("You have already created timesheet " + cstr(chk and chk[0][0] or "") + " for this date.")
            raise Exception

        import time

        for d in getlist(self.doclist, "timesheet_details"):
            if d.act_start_time and d.act_end_time:
                d1 = time.strptime(d.act_start_time, "%H:%M")
                d2 = time.strptime(d.act_end_time, "%H:%M")

                if d1 > d2:
                    msgprint("Start time can not be greater than end time. Check for Task Id : " + cstr(d.task_id))
                    raise Exception
                elif d1 == d2:
                    msgprint("Start time and end time can not be same. Check for Task Id : " + cstr(d.task_id))
                    raise Exception

    def __init__(self, company, start, end, interval):
        from webnotes.utils import getdate
        from webnotes.model.code import get_obj
        import webnotes

        self.company = company
        self.abbr = webnotes.conn.get_value("Company", company, "abbr")
        self.start = getdate(start)
        self.end = getdate(end)

        self.interval = interval

        self.glc = get_obj("GL Control")
        self.cash_accounts = [
            d[0]
            for d in webnotes.conn.sql(
                """
			select name from tabAccount 
			where account_type='Bank or Cash'
			and company = %s and docstatus = 0 
			""",
                company,
            )
        ]

        self.receivables_group = webnotes.conn.get_value("Company", company, "receivables_group")
        self.payables_group = webnotes.conn.get_value("Company", company, "payables_group")

        # list of bank and cash accounts
        self.bc_list = [
            s[0] for s in webnotes.conn.sql("select name from tabAccount where account_type='Bank or Cash'")
        ]

	def validate_warranty_status(self):
		if self.doc.warranty_expiry_date and getdate(self.doc.warranty_expiry_date) >= datetime.date.today() and self.doc.maintenance_status == 'Out of Warranty':
			msgprint("Warranty expiry date and maintenance status mismatch. Please verify")
			raise Exception
		elif (not self.doc.warranty_expiry_date or getdate(self.doc.warranty_expiry_date) < datetime.date.today()) and self.doc.maintenance_status == 'Under Warranty':
			msgprint("Warranty expiry date and maintenance status mismatch. Please verify")
			raise Exception

def send_daily_summary(for_date=None, event_date=None):
	if not for_date:
		for_date = add_days(today(), days=-1)
	if not event_date:
		event_date = today()
	
	formatted_date = getdate(for_date).strftime("%a, %d %B, %Y")
	formatted_event_date = getdate(event_date).strftime("%a, %d %B, %Y")
	subject = "[AAP Ka Manch] Updates for {formatted_date}".format(formatted_date=formatted_date)
	unit_post_map = get_unit_post_map(for_date, event_date)
	
	if not unit_post_map:
		# no updates!
		return
		
	for user in webnotes.conn.sql_list("""select name from `tabProfile`
		where user_type='Website User' and enabled=1 and name not in ('Administrator', 'Guest')"""):
		
		summary = prepare_daily_summary(user, unit_post_map, {"subject": subject, "formatted_date": formatted_date,
			"formatted_event_date": formatted_event_date})
		
		if not summary:
			# no access!
			continue
		
		send(recipients=[user], 
			subject=subject, 
			message=summary,
		
			# to allow unsubscribe
			doctype='Profile', 
			email_field='name', 
			
			# for tracking sent status
			ref_doctype="Profile", ref_docname=user)

def get_last_purchase_details(item_code, doc_name=None, conversion_rate=1.0):
	"""returns last purchase details in stock uom"""
	# get last purchase order item details
	last_purchase_order = webnotes.conn.sql("""\
		select po.name, po.transaction_date, po.conversion_rate,
			po_item.conversion_factor, po_item.purchase_ref_rate, 
			po_item.discount_rate, po_item.purchase_rate
		from `tabPurchase Order` po, `tabPurchase Order Item` po_item
		where po.docstatus = 1 and po_item.item_code = %s and po.name != %s and 
			po.name = po_item.parent
		order by po.transaction_date desc, po.name desc
		limit 1""", (item_code, cstr(doc_name)), as_dict=1)

	# get last purchase receipt item details		
	last_purchase_receipt = webnotes.conn.sql("""\
		select pr.name, pr.posting_date, pr.posting_time, pr.conversion_rate,
			pr_item.conversion_factor, pr_item.purchase_ref_rate, pr_item.discount_rate,
			pr_item.purchase_rate
		from `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
		where pr.docstatus = 1 and pr_item.item_code = %s and pr.name != %s and
			pr.name = pr_item.parent
		order by pr.posting_date desc, pr.posting_time desc, pr.name desc
		limit 1""", (item_code, cstr(doc_name)), as_dict=1)

	purchase_order_date = getdate(last_purchase_order and last_purchase_order[0].transaction_date \
		or "1900-01-01")
	purchase_receipt_date = getdate(last_purchase_receipt and \
		last_purchase_receipt[0].posting_date or "1900-01-01")

	if (purchase_order_date > purchase_receipt_date) or \
			(last_purchase_order and not last_purchase_receipt):
		# use purchase order
		last_purchase = last_purchase_order[0]
		purchase_date = purchase_order_date
		
	elif (purchase_receipt_date > purchase_order_date) or \
			(last_purchase_receipt and not last_purchase_order):
		# use purchase receipt
		last_purchase = last_purchase_receipt[0]
		purchase_date = purchase_receipt_date
		
	else:
		return webnotes._dict()
	
	conversion_factor = flt(last_purchase.conversion_factor)
	out = webnotes._dict({
		"purchase_ref_rate": flt(last_purchase.purchase_ref_rate) / conversion_factor,
		"purchase_rate": flt(last_purchase.purchase_rate) / conversion_factor,
		"discount_rate": flt(last_purchase.discount_rate),
		"purchase_date": purchase_date
	})

	conversion_rate = flt(conversion_rate) or 1.0
	out.update({
		"import_ref_rate": out.purchase_ref_rate / conversion_rate,
		"import_rate": out.purchase_rate / conversion_rate,
		"rate": out.purchase_rate
	})
	
	return out

    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 get_as_on_balance(self, account_name, fiscal_year, as_on, credit_or_debit, lft, rgt):
		# initialization
		det = webnotes.conn.sql("select start_date, opening from `tabAccount Balance` where period = %s and account = %s", (fiscal_year, account_name))
		from_date, opening, debit_bal, credit_bal, closing_bal = det and det[0][0] or getdate(nowdate()), det and flt(det[0][1]) or 0, 0, 0, det and flt(det[0][1]) or 0

		# prev month closing
		prev_month_det = webnotes.conn.sql("select end_date, debit, credit, balance from `tabAccount Balance` where account = %s and end_date <= %s and fiscal_year = %s order by end_date desc limit 1", (account_name, as_on, fiscal_year))
		if prev_month_det:
			from_date = getdate(add_days(prev_month_det[0][0].strftime('%Y-%m-%d'), 1))
			opening = 0
			debit_bal = flt(prev_month_det[0][1])
			credit_bal = flt(prev_month_det[0][2])
			closing_bal = flt(prev_month_det[0][3])

		# curr month transaction
		if getdate(as_on) >= from_date:
			curr_month_bal = webnotes.conn.sql("select SUM(t1.debit), SUM(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= %s AND t1.posting_date <= %s and ifnull(t1.is_opening, 'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s and ifnull(t1.is_cancelled, 'No') = 'No'", (from_date, as_on, lft, rgt))
			curr_debit_amt, curr_credit_amt = flt(curr_month_bal[0][0]), flt(curr_month_bal[0][1])
			debit_bal = curr_month_bal and debit_bal + curr_debit_amt or debit_bal
			credit_bal = curr_month_bal and credit_bal + curr_credit_amt or credit_bal

			if credit_or_debit == 'Credit':
				curr_debit_amt, curr_credit_amt = -1*flt(curr_month_bal[0][0]), -1*flt(curr_month_bal[0][1])
			closing_bal = closing_bal + curr_debit_amt - curr_credit_amt

		return flt(debit_bal), flt(credit_bal), flt(closing_bal)

 def set_last_contact_date(self):
   if self.doc.contact_date_ref and self.doc.contact_date_ref != self.doc.contact_date:
     if getdate(self.doc.contact_date_ref) < getdate(self.doc.contact_date):
       self.doc.last_contact_date=self.doc.contact_date_ref
     else:
       msgprint("Contact Date Cannot be before Last Contact Date")
       raise Exception

	def check_freezing_date(self, adv_adj):
		if not adv_adj:
			acc_frozen_upto = get_value('Global Defaults', None, 'acc_frozen_upto')
			if acc_frozen_upto:
				bde_auth_role = get_value( 'Global Defaults', None,'bde_auth_role')
				if getdate(self.doc.posting_date) <= getdate(acc_frozen_upto) and not bde_auth_role in webnotes.user.get_roles():
					msgprint("You are not authorized to do/modify back dated accounting entries before %s." % getdate(acc_frozen_upto).strftime('%d-%m-%Y'), raise_exception=1)

def get_fy_details(fy_start_date, fy_end_date):
	start_year = getdate(fy_start_date).year
	if start_year == getdate(fy_end_date).year:
		fy = cstr(start_year)
	else:
		fy = cstr(start_year) + '-' + cstr(start_year + 1)
	return fy

	def get_loan_details(self):
		end= self.doc.doi_closing
		list1 = []	
		#list1.append(self.doc.doi_start)
		#webnotes.errprint(start)
		#webnotes.errprint(number)
		#webnotes.errprint((cstr(self.doc.doi_start) + datetime.timedelta(12*365/12)).isoformat())
		#self.doc.doi_closing=(self.doc.doi_start + datetime.timedelta(12*365/12)).isformat()
		#webnotes.errprint(self.doc.doi_closing)
		#j=0
		j=self.doc.number_of_installments
		dt=self.doc.doi_start
		for j in range(0,j):
			date=add_months(getdate(dt),1)
			#ebnotes.errprint(["j",date])
		 	#ebnotes.errprint(["hii",end])
			if date<=getdate(end):
				list1.append(date)
			dt=date
			#webnotes.errprint(date)
			#ebnotes.errprint(list1)
			self.doclist=self.doc.clear_table(self.doclist,'installment')

		for i in list1:
			#ebnotes.errprint("in for loop")
			#self.doclist=self.doc.clear_table(self.doclist,'installment')
			ch = addchild(self.doc, 'installment', 
					'Loan Installment Details', self.doclist)
			ch.date_of_installment = i
			ch.amount_to_be_paid =self.doc.amount_per_month 
			ch.status='Unpaid'			

  def get_dashboard_values(self, arg=''):
    d = get_defaults()
    self.fiscal_year = d['fiscal_year']
    if arg:
      company = arg
    else:
      company = d['company']

    r = {}
    r['Income'] = self.bl('Income', company)
    r['Expenses'] = self.bl('Expenses', company)

    r['Profit'] = []
    for i in range(3):
      r['Profit'].append(r['Income'][i] - r['Expenses'][i])
    
    r['Current Assets'] = self.bl_bs('Current Assets', company, getdate(d['year_start_date']))
    r['Current Liabilities'] = self.bl_bs('Current Liabilities', company, getdate(d['year_start_date']))
    
    r['Working Capital'] = []
    for i in range(3):
      r['Working Capital'].append(r['Current Assets'][i] - r['Current Liabilities'][i])

    r['Bank Accounts'] = self.bl_bs('Bank Accounts', company, getdate(d['year_start_date']))
    
    r['Top Customers'] = convert_to_lists(self.get_top_5_cust(company))
    r['Top Expenses'] = convert_to_lists(self.get_top_5_exp(company))
    
    return r

def execute(filters=None):
	if not filters: filters = {}
	float_preceision = webnotes.conn.get_default("float_preceision")

	condition =get_condition(filters)

	avg_daily_outgoing = 0
	diff = ((getdate(filters.get("to_date")) - getdate(filters.get("from_date"))).days)+1
	if diff <= 0:
		webnotes.msgprint("To Date should not be less than eual to From Date",raise_exception=1)

	columns = get_columns()
	items = get_item_info()
	consumed_item_map = get_consumed_items(condition)
	delivered_item_map = get_delivered_items(condition)

	data = []
	for item in items:

		total_outgoing = consumed_item_map.get(item.name, 0)+delivered_item_map.get(item.name,0)
		avg_daily_outgoing = flt(total_outgoing/diff, float_preceision)
		reorder_level = (avg_daily_outgoing * flt(item.lead_time_days)) + flt(item.min_order_qty)

		data.append([item.name, item.item_name, item.description, item.min_order_qty, item.lead_time_days, 
			consumed_item_map.get(item.name, 0), delivered_item_map.get(item.name,0), total_outgoing, 
			avg_daily_outgoing, reorder_level])

	return columns , data