import datetime
import logging
import re
from orangescape.model.server.util.pyxlformatter import *
from orangescape.model.engine.util.NumericUtil import is_a_number
from orangescape.model.engine.util.DateUtil import xldate_from_date_tuple, xldate_from_datetime_tuple, xldate_as_tuple

"""NOTE: Please add the corresponding python format string in pyxlformatter.py if its not already exists"""
XML_DATE_FORMAT = 'yyyy-MM-ddTHH:mm:ss'
"""*****************************************************************************************************"""

categoryIdMapping = {
         "Category001":"General",
         "Category002":"Number",
         "Category003":"Currency",
         "Category004":"Accounting",
         "Category005":"Date",
         "Category006":"Time",
         "Category007":"Percentage",
         "Category008":"Fraction",
         "Category009":"Scientific",
         "Category010":"Text",
         "Category011":"Boolean",
         "String":"String",
         "Number":"Number",
         "Date":"Date",
         "Boolean":"Boolean"
         }

dateFormatMapping = {
                  "yyyy" : "%Y",
                  "yyy"  : "%Y",
                  "yy"   : "%y",
                  "y"    : "%y",
                  "m"    : "%m",
                  "mm"   : "%m",
                  "mmm"  : "%b",
                  "mmmm" : "%B",
                  "d"    : "%d",
                  "dd"   : "%d",
                  "ddd"  : "%a",
                  "dddd" : "%A",
                  "h"    : "%I",
                  "hh"   : "%H",
                  "s"    : "%S",
                  "ss"   : "%S",
                  "AM/PM": "%p",
                  'a':'%p'
                  }

DEFAULT_DATE_FORMAT = 'dd-MM-yyyy'
def getCellCategory(cell):
    return getCategoryNameById(cell.getFinalCategory())

def getCategoryNameById(categoryId):
    return categoryIdMapping.get(categoryId) or "General"

def convert_str_date(dateStr, dateFormat):
    pyDateFormat = date_format_dict.get(dateFormat) or date_format_dict.get(DEFAULT_DATE_FORMAT)
    if pyDateFormat:
        return datetime.datetime.strptime(dateStr, pyDateFormat)

def __convert_str_boolean_(boolStr):
    if boolStr.upper()=='TRUE':
        return 1.0
    if boolStr.upper()=='FALSE':
        return 0.0
    try:
        num = float(boolStr)
        if num != 0:
            return 1.0
    except Exception, e:
        return 0.0
    return 0.0

def format_record(component, metadata, record):
    session = component.getSession()
    for cellName, value in record.iteritems():
        cell = metadata.getPublicCellMetadata(cellName)
        if not cell.isStr():
            if value is not None and len(value)>0:
                if cell.getCategory()==DATE_CATEGORY:
                    record[cellName] = xldate_from_datetime_tuple(convert_str_date(value, cell.getOutputFormat() or cell.getInputFormat() or DEFAULT_DATE_FORMAT).timetuple()[:6], 0)
                elif cell.getCategory()==BOOLEAN_CATEGORY:
                    record[cellName] = __convert_str_boolean_(value)
            else:
                record[cellName] = None
        elif cell.isReferenceCell():
            parentMetadata = cell.getReferredSheetMetadata()
            parentSheet = session.getIfExists(value, parentMetadata) or session.getBySheetName(value, parentMetadata)
            if not parentSheet:
                record[cellName] = None
                errorMessage = 'Import: Parent sheet with SheetName %s not found for the reference cell %s for the record %s'%(value, cell.getCellName(), record.get('SheetName'))
                logging.warn(errorMessage)
            else:
                record[cellName] = parentSheet.getSheetId()
    return record


def convertToEngineFormat(value, category, formatStr):
    if value.strip() == '':
        return None
    functionName = 'convertTo%s'%category
    import ConversionUtil
    return getattr(ConversionUtil,functionName)(value.strip(), formatStr)

def convertToDate(value, formatStr):
    return xldate_from_datetime_tuple(convert_str_date(value,formatStr).timetuple()[:6], 0)

def convertToBoolean(value, formatStr):
    if value == 'TRUE':
        return 1.0
    return 0.0

def convertToNumber(value, formatStr):
    if is_a_number(value):
        return float(value)
    return None

def convertToString(value, formatStr):
    return value

def convertToText(value, formatStr):
    return value

def convertToGeneral(value, formatStr):
    return value

def convertToCurrency(value, formatStr):
    return value

def convertToAccounting(value, formatStr):
    return value

def convertFromEngineFormat(value, category, formatStr):
    functionName = 'convertFrom%s'%categoryIdMapping.get(category)
    import ConversionUtil
    return getattr(ConversionUtil,functionName)(value, formatStr)

def convertFromXMLFormat(value, categoryId, formatStr):
    if value.strip() == '':
        return None
    category = categoryIdMapping.get(categoryId)
    if category =='Date':
        return convertFromXMLDate(value, formatStr)
    if category == 'Boolean':
        return convertFromXMLBoolean(value, formatStr)
    else:
        return value

def convertFromXMLDate(value, formatStr):
    return xldate_from_datetime_tuple(convert_str_date(value, XML_DATE_FORMAT).timetuple()[:6], 0)

def convertFromXMLBoolean(value, formatStr):
    return convertToBoolean(value, formatStr)

def convertFromNumber(value, formatStr):
    if formatStr == '###':
        return int(value)
    else:
        return value

def convertToWSFormat(value, category, formatStr):
    functionName = 'convertToWS%s'%categoryIdMapping.get(category)
    import ConversionUtil
    return getattr(ConversionUtil, functionName)(value, formatStr)

def convertToWSDate(value, formatStr):
    if value<=60: #""" To avoid XLDateAmbiguous Exception from xldate_as_tuple() """
        return ''
    datetime_tuple=xldate_as_tuple(value,0)
    return datetime.datetime(*(datetime_tuple[0:6])).strftime("%Y-%m-%dT%H:%M:%S")

def convertToWSNumber(value, formatStr):
    if formatStr == '###':
        return int(value)
    return value

def convertToWSBoolean(value, formatStr):
    if value == 0.0:
        return 'FALSE'
    return 'TRUE'

def convertToWSString(value, formatStr):
    return value

def convertToWSText(value, formatStr):
    return value

def convertToWSGeneral(value, formatStr):
    return value

def convertToWSCurrency(value, formatStr):
    return value

def convertToWSAccounting(value, formatStr):
    return value

def convertFromEngineFormat(value, category, formatStr):
    functionName = 'convertFrom%s'%getCategoryNameById(category)
    import ConversionUtil
    return getattr(ConversionUtil,functionName)(value, formatStr)

def convertDateTime(value, formatStr):
    if value:
        regExYear = re.compile('[y]+',re.IGNORECASE)
        regExDate = re.compile('[d]+',re.IGNORECASE)
        regExMonth = re.compile('[m]+',re.IGNORECASE)
        regExHour = re.compile('[h]+',re.IGNORECASE)
        regExMinute = re.compile('h.[m]+',re.IGNORECASE)
        regExSecond = re.compile('[s]+', re.IGNORECASE)

        regExAMPM = re.compile('a$')
        formatStr = formatStr.replace('AM/PM', 'a')

        if (len(regExMinute.findall(formatStr)))> 0:
            changeStr = regExMinute.findall(formatStr)[0]
            formatStr = formatStr.replace(changeStr, re.sub(regExMonth, 'min', changeStr))
        if (len(regExYear.findall(formatStr)))> 0:
            formatStr = re.sub(regExYear, dateFormatMapping[regExYear.findall(formatStr)[0].lower()], formatStr)
        if (len(regExDate.findall(formatStr)))> 0:
            formatStr = re.sub(regExDate, dateFormatMapping[regExDate.findall(formatStr)[0].lower()], formatStr)
        if (len(regExMonth.findall(formatStr)))> 0:
            formatStr = re.sub(regExMonth, dateFormatMapping[regExMonth.findall(formatStr)[0].lower()], formatStr,1)
        if (len(regExHour.findall(formatStr)))> 0:
            formatStr = re.sub(regExHour, dateFormatMapping[regExHour.findall(formatStr)[0].lower()], formatStr)
        if (len(regExSecond.findall(formatStr)))> 0:
            formatStr = re.sub(regExSecond, dateFormatMapping[regExSecond.findall(formatStr)[0].lower()], formatStr)
        if (len(regExAMPM.findall(formatStr))>0):
            formatStr = re.sub(regExAMPM, dateFormatMapping[re.findall(regExAMPM, formatStr)[0].lower()], formatStr)
        formatStr = formatStr.replace('min', '%M')

        datetuple= xldate_as_tuple(value, 0)
        date= datetime.datetime(datetuple[0],datetuple[1],datetuple[2],datetuple[3],datetuple[4],datetuple[5])
        return date.strftime(formatStr)
    return ''

def convertFromDate(value, formatStr):
   return convertDateTime(value, formatStr)

def convertFromTime(value, formatStr):
   return convertDateTime(value, formatStr)

def convertFromNumber(value,formatStr ):
    return getFormattedNumberValue(value,formatStr)

def convertFromCurrency(value, formatStr):
    return getFormattedNumberValue(value,formatStr)

def convertFromAccounting(value, formatStr):
    return getFormattedNumberValue(value,formatStr)

def convertFromPercentage(value, formatStr):
    return getFormattedNumberValue(value,formatStr)

def convertFromFraction(value, formatStr):
    return getFormattedNumberValue(value,formatStr)

def convertFromScientific(value, formatStr):
    return getFormattedNumberValue(value,formatStr)

def convertFromText(value, formatStr):
    if value:
        return value
    else:
        return ''

def convertFromBoolean(value, formatStr):
    if value==1.0:
        if formatStr=='True/False':
            return True
        else:
            return 'Yes'
    else:
        if formatStr=='True/False':
            return False
        else:
            return 'No'

def convertFromScientific(value, formatStr):
    return getFormattedNumberValue(value,formatStr)

def number_formatwithComma(num, places=0):
    """Format a number according to locality and given places"""
    import locale
    locale.setlocale(locale.LC_ALL, "")
    return locale.format("%.*f", (places, num), True)

def number_formatForInteger(format,num, places=0):
    if places==0:
        format="'"+format+"%"+"d'%"+str(num)
    else:
        format="'"+format+"%."+str(places)+"f'%"+str(num)
    return eval(format)

def getFormattedNumberValue(val,format):
    place=0
    if not val:
        val=0
    if format.find('.')>0:
        if format.find('.')==format.rfind('.') :
            if format[0:format.find('.')].rfind('#')>0:
                place=len(format[format.rfind('.')+1:len(format)])
        else:
            place=len(format[format.rfind('.')+1:len(format)])
    hasComma=False
    if format.find(',')!=-1:
        hasComma=True
    symbol=format.replace(',','').replace('#','')
    if place>0:
        symbol=symbol[0:-1]
    if hasComma:
        return symbol+number_formatwithComma(val, place)
    else:
        return number_formatForInteger(symbol, val, place)

def convert_date_xl_to_py(xl_date):
    if not xl_date:
        return None
    datetime_tuple=xldate_as_tuple(xl_date,0)
    return datetime.datetime(*(datetime_tuple[0:6]))

def convert_date_py_to_xl(py_date):
    if not py_date:
        return None
    return xldate_from_datetime_tuple(py_date.timetuple()[:6], 0)
