from orangescape.model.engine.util.DateUtil import *
from orangescape.model.engine.util.DateUtil import _leap
from orangescape.model.engine.exception.Error import FormulaArgumentError
from orangescape.model.engine.exception.ErrorMessage import *

from re import search,split
from datetime import datetime, timedelta, date
from time import localtime,strftime,strptime,time

def YEAR(argList):
    try:
        datetimetuple = xldate_as_tuple(argList[0], 0)
        return datetimetuple[0]
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","YEAR")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("YEAR")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("YEAR")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"YEAR")

def MONTH(argList):
    try:
        datetimetuple = xldate_as_tuple(argList[0], 0)
        return datetimetuple[1]
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","MONTH")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("MONTH")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("MONTH")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"MONTH")

def DAY(argList):
    try:
        datetimetuple = xldate_as_tuple(argList[0], 0)
        return datetimetuple[2]
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","DAY")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("DAY")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("DAY")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"DAY")

def HOUR(argList):
    try:
        datetimetuple = xldate_as_tuple(argList[0], 0)
        return datetimetuple[3]
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","HOUR")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("HOUR")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("HOUR")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"HOUR")

def MINUTE(argList):
    try:
        datetimetuple = xldate_as_tuple(argList[0], 0)
        return datetimetuple[4]
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","MINUTE")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("MINUTE")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("MINUTE")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"MINUTE")

def SECOND(argList):
    try:
        datetimetuple = xldate_as_tuple(argList[0], 0)
        return datetimetuple[5]
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","SECOND")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("SECOND")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("SECOND")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"SECOND")

def NOW(argList):
    try:
        dttuple = localtime(time())
        datetimetuple = (dttuple[0],dttuple[1],dttuple[2],dttuple[3],dttuple[4],dttuple[5])
        return xldate_from_datetime_tuple(datetimetuple, 0)
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","NOW")
    except XLDateBadTuple, e:
        raise FormulaArgumentError, Engine2010 % (e,"NOW")

def TODAY(argList):
    try:
        dttuple = localtime(time())
        datetimetuple = (dttuple[0],dttuple[1],dttuple[2])
        return xldate_from_date_tuple(datetimetuple, 0)
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","TODAY")
    except XLDateBadTuple, e:
        raise FormulaArgumentError, Engine2010 % (e,"TODAY")

def TIMEVALUE(argList):
    if type(argList[0]) in (long, float):
        return argList[0]

def DATE(argList):
    try:
        year = int(argList[0])
        month = int(argList[1])
        day = int(argList[2])
        month_max_days = [0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]

        # YEAR Checking
        if (year >= 0 and year <= 1899):
            year=1900+year

        # MONTH Checking
        if (month > 12 or month < 0):
            year=year+(month / 12)
            month=month % 12
        if month==0:
            year=year-1
            month=12

        # +VE DAYS Checking
        while day > month_max_days[month]:
            if day==0:
                month=month-1
                if month==0:
                    year=year-1
                    month=12

            if ISLEAPYEAR([year]) and month==2:
                month_max_days[month]=29
            else:
                month_max_days[2]=28

            day=day-month_max_days[month]

            if day==0:
                day=1
            month=month+1
            if (month > 12):
                year=year+(month / 12)
                month=month % 12
                if month==0:
                    month=12
                    year=year-1

            if ISLEAPYEAR([year]) and month==2:
                month_max_days[month]=29
            else:
                month_max_days[2]=28

        # -VE DAYS Checking
        while day <= 0:
            if year==1900 and month==1 and day==0:
                return 0    # Date Serial Number is 0
            month=month-1
            if month==0:
                month=12
                year=year-1

            if ISLEAPYEAR([year]) and month==2:
                month_max_days[month]=29
            else:
                month_max_days[2]=28

            day=day+month_max_days[month]

        datetimetuple = (year,month,day)
        return xldate_from_date_tuple(datetimetuple, 0)
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","DATE")
    except XLDateBadTuple, e:
        raise FormulaArgumentError, Engine2010 % (e,"DATE")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"DATE")

def TIME(argList):
    try:
        hour = argList[0]
        if hour<0:
            raise FormulaArgumentError, Engine2010 % ('Invalid hour %r' % (hour),"TIME")
        hour = int(hour) % 24.0    # if hour>23 then hour=hour%24
        minute = int(argList[1])
        second = int(argList[2])
        TimeFraction = ((second / 60.0 + minute) / 60.0 + hour) / 24.0
        if TimeFraction<0.0:
            raise FormulaArgumentError, Engine2010 % ('Invalid (hour, minute, second): %r' % ((hour, minute, second),),"TIME")
        return TimeFraction
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"TIME")

def ISLEAPYEAR(argList):
    # Leap year Checking
    year = argList[0]
    if(year%400 ==0 or (year%100 != 0 and year%4 == 0)):
        return True
    return False

def DAYS360(argList):
    try:
        #Ref: http://www.bettersolutions.com/excel/EDH113/V3743810501.htm
        Start_Date=argList[0]
        End_Date=argList[1]
        isEndDateSmall=False
        if Start_Date > End_Date:
            isEndDateSmall=True
            temp = End_Date
            End_Date = Start_Date
            Start_Date = temp

        StartMonthDays = [0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        EndMonthDays = [0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]

        DateTimeTuple = xldate_as_tuple(Start_Date, 0)
        StartDate = (DateTimeTuple[0],DateTimeTuple[1],DateTimeTuple[2])

        DateTimeTuple = xldate_as_tuple(End_Date, 0)
        EndDate = (DateTimeTuple[0],DateTimeTuple[1],DateTimeTuple[2])

        Method = 0.0        # Default FALSE (U.S. Days360 Format)
        if len(argList)>2:
            if argList[2]!=0:
                Method = 1.0

        StartYear = StartDate[0]
        StartMonth = StartDate[1]
        StartDay = StartDate[2]

        EndYear = EndDate[0]
        EndMonth = EndDate[1]
        EndDay = EndDate[2]

        if ISLEAPYEAR([StartYear]) and StartMonth==2:
            StartMonthDays[2]=29
        else:
            StartMonthDays[2]=28

        if ISLEAPYEAR([EndYear]) and EndMonth==2:
            EndMonthDays[2]=29
        else:
            EndMonthDays[2]=28

        # define Named Constants
        DAYS360_US,DAYS360_EUROPEAN = (0,1)

        if Method==DAYS360_US:
            if StartDay == StartMonthDays[StartMonth] and StartMonth!=0:
                StartDay = 30
            if EndDay == 31:
                if StartDay < 30:
                    EndDay = 1
                    EndMonth = EndMonth + 1
                    if EndMonth > 12:
                        EndMonth = 1
                        EndYear = EndYear + 1
                else:
                    EndDay = 30
        elif Method==DAYS360_EUROPEAN:
            if StartDay > 30:
                StartDay = 30
            if EndDay > 30:
                EndDay = 30

        # Source: http://www.experts-exchange.com/Programming/Languages/Scripting/Python/Q_23689891.html
        Years = EndYear - StartYear       # YEAR DIFFERENCE
        Months = EndMonth - StartMonth    # MONTH DIFFERENCE
        if Months < 0:
          Years -= 1
          Months += 12
        DiffMonths = Months + (Years * 12)
        Days360=(DiffMonths * 30) + (EndDay - StartDay)
        if isEndDateSmall:
            Days360=Days360 * (-1)
        return Days360
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","DAYS360")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("DAYS360")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("DAYS360")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"DAYS360")

def getMonth(arg):
    monthdict = {'jan':{'no':1,'length':7,'name':'january'},'feb':{'no':2,'length':8,'name':'february'},'mar':{'no':3,'length':5,'name':'march'},'apr':{'no':4,'length':5,'name':'april'},'may':{'no':5,'length':3,'name':'may'},'jun':{'no':6,'length':4,'name':'june'},'jul':{'no':7,'length':4,'name':'july'},'aug':{'no':8,'length':6,'name':'august'},'sep':{'no':9,'length':9,'name':'september'},'oct':{'no':10,'length':7,'name':'october'},'nov':{'no':11,'length':8,'name':'november'},'dec':{'no':12,'length':8,'name':'december'}}
    month = arg[0:3].lower()
    key_list = monthdict.keys()
    if key_list.count(month) == 0:
        raise FormulaArgumentError,Engine2020 % (arg,'MONTH')
    for i in monthdict.keys():
        if i == month :
            if monthdict[i]['name'][0:len(arg)] == arg.lower():
                return monthdict[i]['no']
            else:
                raise FormulaArgumentError,Engine2020 % (arg,'MONTH')

def getYear(date_list):
    if len(date_list[0]) == 4:
        return int(date_list[0])
    elif len(date_list) == 3:
        if len(date_list[2]) == 2:
            if int(date_list[2]) < 30 :
                date_list[2] = '20'+date_list[2]
            else:
                date_list[2] = '19'+date_list[2]
            return int(date_list[2])
        elif len(date_list[2]) == 4:
             return int(date_list[2])
        else:
            raise FormulaArgumentError,Engine2020 % (str(int(date_list[2])),'YEAR')
    else:
        if len(date_list[1]) == 4:
            return int(date_list[1])
        return date.today().year


def checkDate(year,month,date):
    month_max_days = [0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
    if month == 2:
        if _leap(year) and date <= 29:
            pass
        elif (date <= 28):
            pass
        else:
            raise FormulaArgumentError,Engine2020 % (date,'DATEVALUE')
    else:
        if month_max_days[month] < date:
            raise FormulaArgumentError,Engine2020 % (date,'DATEVALUE')

def DATEVALUE(argList):
    try:
        date_str = str(argList[0])
        # using split we are seperation a raw string date into raw_list which will have day,month,year as list values
        # To split each characters by '-', '/' ,space or ','(comma)
        raw_list =  split('[-,/, ,,]', date_str)
        date_list = list()
        for i in raw_list:
            if i != '':
                date_list.append(i)
        if len(date_list) == 1:
            raise FormulaArgumentError,Engine2020 % (argList[0],'DATEVALUE')

        # There are 4 if conditions to find 4 differnt formats based on the possible input values to this function.
        # 1st one is for date vlues like '2009/01/25'
        # 2nd one is for date values like '09-january-04'
        # 3rd for january 4, 2009
        # 4th for 4 january 2009
        if (date_str.count('/') > 0 or date_str.count('-') > 0) and  search('[A-Za-z]', date_str) == None:
            r_year = getYear(date_list)
            if len(date_list[0]) == 4:
                r_month = int(date_list[1])
                r_date = int(date_list[2])
            else:
                r_month = int(date_list[0])
                if r_month > 12:
                    raise FormulaArgumentError,Engine2020 % (r_month,'DATEVALUE')
                r_date = int(date_list[1])

        elif search('[A-Za-z]', date_str) != None and search('[/-]',date_str)!= None:
            if len(date_list) == 2:
                if search('[A-Za-z]', date_list[0]):
                    r_month = getMonth(date_list[0])
                    r_date = int(date_list[1])
                else:
                    r_month = getMonth(date_list[1])
                    r_date = int(date_list[0])
            else:
                r_month = getMonth(date_list[1])
                r_date = int(date_list[0])
            r_year = getYear(date_list)

        elif search(',' , date_str):
            r_month = getMonth(date_list[0])
            r_date = int(date_list[1])
            r_year = getYear(date_list)

        elif search(' ' , date_str) and search('[/-]',date_str) == None:
            r_year = getYear(date_list)
            if len(date_list) == 2:
                if search('[a-zA-Z]', date_list[0]):
                    r_month = getMonth(date_list[0])
                    if len(date_list[1]) <= 2:
                        r_date = int(date_list[1])
                    elif len(date_list[1]) == 3:
                        raise FormulaArgumentError,Engine2020 % (str(date_list[1]),'DATEVALUE')
                    else:
                        r_date = 1
                else:
                    r_month = getMonth(date_list[1])
                    r_date = int(date_list[0])
            else:
                r_month = getMonth(date_list[1])
                r_date = int(date_list[0])
        else:
            raise FormulaArgumentError,Engine2020 % (argList[0],'DATEVALUE')

        checkDate(r_year, r_month, r_date)
        result= (r_year,r_month,r_date)
        return xldate_from_date_tuple(result ,0)
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","DATEVALUE")
    except XLDateBadTuple, e:
        raise FormulaArgumentError, Engine2010 % (e,"DATEVALUE")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"DATEVALUE")

def WEEKDAY(argList):
    try:
        datenumber = argList[0]
        if datenumber < 1:
            raise FormulaArgumentError, Engine2021 % (datenumber,"WEEKDAY")

        option = 1            # Default type is 1
        if len(argList)>1:
            option = int(argList[1])
            if option not in (1,2,3,11,12,13,14,15,16,17):
                raise FormulaArgumentError, Engine2009 % ("return type","WEEKDAY")

        dttuple = xldate_as_tuple(datenumber, 0)

        year=dttuple[0]
        month=dttuple[1]
        day=dttuple[2]

        if year<1900: year=year+1900
        if year==1900 and month==1 and day==0:
            return 6
        else:
            dt = datetime(year, month, day)
            WeekdayFromPython = dt.weekday()
            if option == 3:
                return WeekdayFromPython
            else:
                weekday = WeekdayFromPython + 1
                python_excel_optionsMapping = {11:0,2:0,12:1,13:2,14:3,15:4,16:5,17:6,1:6}
                weekstart = python_excel_optionsMapping[option]
                weekday = weekday - python_excel_optionsMapping[option]
                if weekday <= 0: return weekday + 7
                return weekday

    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","WEEKDAY")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("WEEKDAY")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("WEEKDAY")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"WEEKDAY")

def WEEKNUM(argList):
    try:
        dateNumber = argList[0]
        dateTuple = xldate_as_tuple(dateNumber,0)
        year = dateTuple[0]
        month = dateTuple[1]
        date = dateTuple[2]

        python_excel_optionsMapping = {11:0,2:0,12:1,13:2,14:3,15:4,16:5,17:6,1:6}
        option  = None
        if len(argList)>1:
            option = int(argList[1])
            if option not in (1,2,11,12,13,14,15,16,17):
                raise FormulaArgumentError, Engine2009 % ("return type","WEEKNUM")
            else:
                option = python_excel_optionsMapping[option]
        dttuple = xldate_as_tuple(dateNumber, 0)
        yearFirstWeekday = datetime(year,1,1).weekday()
        if option is None: option = yearFirstDateNum
        dateWeekday = datetime(year,month,date).weekday()
        yeatFirstDateNumber = xldate_from_date_tuple((year,1,1),0)
        weeknum = int (((dateNumber - yeatFirstDateNumber) / 7 ) + 1)
        if dateWeekday >= option and dateWeekday < yearFirstWeekday :
            return weeknum +1
        return weeknum

    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","WEEKNUM")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("WEEKNUM")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("WEEKNUM")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"WEEKNUM")

def WORKDAY(argList):
    try:
        serialnumber = int(argList[0])
        nofdays = int(argList[1])
        if serialnumber==0.0 and nofdays==0.0: return 0.0
        holidays = []    # holiday(s) are optional
        if len(argList)>2:
            for holiday in argList[2:]:
                if holiday>2958465:
                    raise FormulaArgumentError, Engine2008 % ("WORKDAY")
                elif holiday<0:
                    raise FormulaArgumentError, Engine2011 % ("WORKDAY")
                holidays.append(int(holiday))

        dttuple = xldate_as_tuple(serialnumber, 0)
        startdate = date(dttuple[0], dttuple[1], dttuple[2])

        if nofdays==0: day=0
        elif nofdays>0: day=1
        else: day=-1
        i=1
        while i<=abs(nofdays):
            newdate = startdate+timedelta(days=day)
            # For check whether it an weekend.
            weekday = newdate.weekday()
            # For check whether it is in holidays list.
            dttuple=newdate.timetuple()
            serialnumber=xldate_from_date_tuple((dttuple[0], dttuple[1], dttuple[2]), 0)
            # If the day is not weekend(5->Saturday & 6->Sunday) nor holiday then count as working day
            if weekday!=5 and weekday!=6 and (serialnumber not in holidays):
                i=i+1
            if nofdays>=0: day+=1
            else: day-=1
        return serialnumber
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","WORKDAY")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("WORKDAY")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("WORKDAY")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"WORKDAY")

def last_day_of_month(date):
    if date.month == 12:
        return date.replace(day=31)
    return date.replace(month=date.month+1, day=1) - timedelta(days=1)

def EDATE(argList):
    try:
        serial_no_of_date = argList[0]
        months = abs(int(argList[1]))
        date_tuple = xldate_as_tuple(serial_no_of_date, 0)
        a_date =  date_tuple[2]
        year = date_tuple[0]
        month = date_tuple[1]

        while months > 0:
            if argList[1] > 0:
                if month == 12:
                    month -= 12
                    year += 1
                month += 1

            elif argList[1] < 0:
                if month == 1:
                    month = 12
                    year -= 1
                else:
                    month -=1

            months -= 1
        last_date = last_day_of_month(date(year,month,1)).day
        if a_date > last_date:a_date = last_date
        temp_tuple =(year,month,a_date,0,0,0)

        return xldate_from_datetime_tuple(temp_tuple, 0)
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","EDATE")
    except XLDateBadTuple, e:
        raise FormulaArgumentError, Engine2010 % (e,"EDATE")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("EDATE")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("EDATE")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"EDATE")

def getDate(month,year):
    month_max_days = [0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
    if month == 2:
        if _leap(year):
            return 29
        else:
            return 28
    else:
        return month_max_days[month]

def EOMONTH(argList):
    try:
        serial_no_of_date =argList[0]
        months = abs(int(argList[1]))
        date_tuple = xldate_as_tuple(serial_no_of_date, 0)
        date =  date_tuple[2]
        year = date_tuple[0]
        month = date_tuple[1]
        while months > 0:
            if argList[1] > 0:
                month += 1
                if month > 12:
                    month -= 12
                    year += 1

            elif argList[1] < 0:
                month -=1
                if month <= 0:
                    month = 12
                    year -= 1
            months -= 1

        date = getDate(month,year)
        temp_tuple =(year,month,date,0,0,0)
        return xldate_from_datetime_tuple(temp_tuple, 0)
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","EOMONTH")
    except XLDateBadTuple, e:
        raise FormulaArgumentError, Engine2010 % (e,"EOMONTH")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("EOMONTH")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("EOMONTH")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"EOMONTH")

def NETWORKDAYS(argList):
    try:
        # Returns Number of working days between two dates.
        # NETWORKDAYS = (5*(LastSunday-FirstMonday)/7+WorkdaysB4FirstMonday+WorkdaysAfterLastSunday+1) - holidays
        startdate = int(argList[0])
        enddate = int(argList[1])

        if startdate>enddate:    # Swap two dates
            startdate,enddate = (enddate,startdate)

        # Count Number of Holidays
        holidays=0
        if len(argList)>2:
            for holiday in argList[2:]:
                if holiday>2958465:
                    raise FormulaArgumentError, Engine2008 % ("NETWORKDAYS")
                elif holiday<0:
                    raise FormulaArgumentError, Engine2011 % ("NETWORKDAYS")
                holiday=int(holiday)
                dt = xldate_as_tuple(holiday, 0)
                weekday = date(dt[0],dt[1], dt[2]).weekday()
                if weekday not in (5,6) and holiday>=startdate and holiday<=enddate:
                    holidays=holidays+1

        MONDAY, FRIDAY, SUNDAY = (0,4,6)    # Named Constants

        # For find FirstMonday
        dt = xldate_as_tuple(startdate, 0)
        weekday = date(dt[0],dt[1], dt[2]).weekday()
        WorkdaysB4FirstMonday=0
        FirstMonday=startdate
        if weekday!=MONDAY:
            if weekday<=FRIDAY:
                WorkdaysB4FirstMonday = FRIDAY-weekday+1
            FirstMonday = startdate+(SUNDAY-weekday)+1

        # For find LastSunday
        dt = xldate_as_tuple(enddate, 0)
        weekday = date(dt[0],dt[1], dt[2]).weekday()
        WorkdaysAfterLastSunday=0
        LastSunday=enddate
        if weekday<SUNDAY:
            WorkdaysAfterLastSunday=5    # Default 5 Workdays
            if weekday<FRIDAY:
                WorkdaysAfterLastSunday = weekday+1
            LastSunday = enddate-(weekday+1)

        networkdays = (5*(LastSunday-FirstMonday)/7+WorkdaysB4FirstMonday+WorkdaysAfterLastSunday+1) - holidays
        if argList[0]>argList[1]:
            networkdays = networkdays * (-1)
        return networkdays

    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","NETWORKDAYS")
    except XLDateBadTuple, e:
        raise FormulaArgumentError, Engine2010 % (e,"NETWORKDAYS")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("NETWORKDAYS")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("NETWORKDAYS")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"NETWORKDAYS")

def YEARFRAC(argList):
    try:
        #Ref: http://www.codecogs.com/d-ox/finance/banking/yearfraction.php
        startdate = int(argList[0])
        enddate = int(argList[1])

        basis=0
        if len(argList)>2:
            basis=int(argList[2])
            if basis not in (0,1,2,3,4):     # (0,1,2,3,4) are basis
                raise FormulaArgumentError, Engine2009 % ("basis","YEARFRAC")

        if(enddate<startdate):  # swap dates.
            startdate,enddate = (enddate,startdate)

        # Named Constants
        US_NASD30_360,ACTUAL_ACTUAL,ACTUAL_360,ACTUAL_365,EUROPEAN30_360=xrange(5)

        if basis==US_NASD30_360:
            DAYS360_US=0.0
            listDates=[startdate, enddate,DAYS360_US]
            return DAYS360(listDates)/360.0
        elif basis==ACTUAL_ACTUAL:
            thedate = xldate_as_tuple(startdate, 0)
            startyear,startmonth=thedate[0],thedate[1]
            thedate = xldate_as_tuple(enddate, 0)
            endyear,endmonth=thedate[0],thedate[1]

            diff = enddate - startdate
            if diff<=366 and ((ISLEAPYEAR([startyear]) and startmonth<2) or (ISLEAPYEAR([endyear]) and endmonth>2)):
                return diff/366.0
            elif diff<=365:
                return diff/365.0
            else:
                yearsum,yearcount=0.0,0
                while startyear<=endyear:
                    if ISLEAPYEAR([startyear]):
                        yearsum+=366.0
                    else:
                        yearsum+=365.0
                    startyear+=1
                    yearcount+=1
                return (enddate - startdate)/(yearsum/yearcount)
        elif basis==ACTUAL_360:
            return (enddate - startdate)/360.0
        elif basis==ACTUAL_365:
            return (enddate - startdate)/365.0
        elif basis==EUROPEAN30_360:
            DAYS360_EUROPEAN=1.0
            listDates=[startdate, enddate,DAYS360_EUROPEAN]
            return DAYS360(listDates)/360.0
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","YEARFRAC")
    except XLDateBadTuple, e:
        raise FormulaArgumentError, Engine2010 % (e,"YEARFRAC")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("YEARFRAC")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("YEARFRAC")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"YEARFRAC")

""" To Do: Testcases need to be added in Database(or JSON). """
def DAYSINMONTH(argList):
    """ Returns the number of days in a month based on year (including leap year) """
    try:
        datetimetuple = xldate_as_tuple(argList[0], 0)
        year = datetimetuple[0]
        month = datetimetuple[1]
        return [0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month]+ISLEAPYEAR([year])
    except XLDateBadDatemode, e:
        raise FormulaArgumentError, Engine2009 % ("date mode","DAYSINMONTH")
    except XLDateNegative, e:
        raise FormulaArgumentError, Engine2011 % ("DAYSINMONTH")
    except XLDateTooLarge, e:
        raise FormulaArgumentError, Engine2008 % ("DAYSINMONTH")
    except XLDateAmbiguous, e:
        raise FormulaArgumentError, Engine2021 % (e.args[0],"DAYSINMONTH")
