__author__ = 'jma'
# Joacim Marklund, Lundalogik, 2016
# Core version 0.8

# region Imports
import time
import csv
import shutil
import sys
import xlrd
import unicodedata
import limeclient as lc
from os import rename, listdir, path, remove
from configobj import ConfigObj
from enum import IntEnum
from termcolor import cprint
from datetime import datetime
from chardet.universaldetector import UniversalDetector
# endregion

# region Classes


class LogMode(IntEnum):
    Nothing = 1
    Normal = 2
    Full = 3


class Importance(IntEnum):
    Low = 1
    Medium = 2
    High = 3


class SplitSide(IntEnum):
    Left = 0
    Right = 1


# endregion

# region Variables
configFile = None
configFileLogFile = None
configFileServer = ''
configFileDatabase = ''
configFileDelimiter = ''
configFileTimeOutLengthIntervals = -1
configFileSleepInterval = -1
configFileUsername = ''
configFilePassword = ''

workingLimeType = None
workingConfig = None
workingFile = None
workingTablename = ''
importIndex = 0
clientlogin = None
continueimport = True
onlyedit = False
logmode = LogMode.Nothing
loghistory = False
loghistorybody = []
# endregion

# region Custom Code
#########################
# CUSTOM CODE           #
#########################

# Put all of the custom code for the project here





#########################
# END CUSTOM CODE       #
#########################
# endregion


# region Core loop
def setupconfig(filename):
    global configFile
    global configFileLogFile
    global configFileTimeOutLengthIntervals
    global configFileSleepInterval
    global configFileDelimiter
    global configFileServer
    global configFileDatabase
    global configFileUsername
    global configFilePassword
    # global loghistoryenabled

    configFile = ConfigObj(filename)
    configFileLogFile = configFile['logfile']
    configFileTimeOutLengthIntervals = configFile['timeOutLengthIntervals']
    configFileSleepInterval = configFile['sleepInterval']
    configFileDelimiter = configFile['delimiter']
    configFileServer = configFile['server']
    configFileDatabase = configFile['database']
    configFileUsername = configFile['username']
    configFilePassword = configFile['password']

    log(Importance.Low, '[Setup config] successful')


def getclientlogin(verifycert):
    global clientlogin
    client = None   # TODO: Is this necessary?
    if configFileDatabase == '':
        client = lc.LimeClient(configFileServer, verify_ssl_cert=verifycert)
    else:
        client = lc.LimeClient(configFileServer, configFileDatabase, verify_ssl_cert=verifycert)

    clientlogin = client.login(configFileUsername, configFilePassword)

    log(Importance.Low, '[Get Client Login] successful')
    return


def importloop(configfilename, usecert, methodpreimport, methodimports, methodpostimport, onlyeditfiles, importlogmode, loghistoryafterimport):
    """ Runs the entire loop of managing the import files and the imports.
        Will not try to connect to Lime if onlyeditfiles is True
    """
    global onlyedit
    global logmode
    global loghistory
    onlyedit = onlyeditfiles
    logmode = importlogmode
    loghistory = loghistoryafterimport

    # Load the config file
    setupconfig(configfilename)

    if onlyeditfiles is False:
        methodpreimport()

        # Login
        getclientlogin(usecert)

        # Begin working
        methodimports()

        # Print end
        methodpostimport()
        printfileendimport()

        if loghistory:
            importhistorylog()
    else:
        methodpreimport()
        methodpostimport()


#############################
# Import management section #
#############################

def runimport():
    """ Finishes the import setup and starts the import. Waits for the import to finish before returning,
        or returns if timer runs out
    """

    if continueimport:
        job = postsetupimport()
        job = waitforjobtocomplete(job)
        printjobstatus(job)
        printfileimportcompleted(job)

        if loghistory:
            addimporttohistorylog(job)
    else:
        log(Importance.High, '[Runimport][ERROR] Import has been aborted')

    return


def postsetupimport():
    """ Creates and returns the import job, and prints current errors """
    workingConfig.save()

    configstatus = workingConfig.validate()
    if len(configstatus.hal['errors']) > 0:
        log(Importance.High, '[Postsetupimport] ' + str(configstatus.hal['errors']))
        job = None
    else:
        log(Importance.Medium, 'Importing [' + workingFile + ']')
        job = lc.ImportJobs(clientlogin).create(workingConfig)

    return job


def prepareimport(filename, tablename, behavior=lc.ImportConfig.CreateAndUpdate):
    """ Setups the importer to import from filename's tablename. Add custom import behavior if wanted """
    file = configFile[filename]

    if thefileexists(filename):
        with open(file, encoding=get_file_encoding(file)) as content:
            f = lc.ImportFiles(clientlogin).create(filename='importfile' + str(importIndex + 1) + '.csv',
                                                   content=content)
            f.delimiter = configFileDelimiter
            f.save()

        global workingLimeType
        global workingConfig
        global workingFile
        global workingTablename

        workingTablename = tablename
        workingLimeType = lc.LimeTypes(clientlogin).get_by_name(tablename)
        workingConfig = lc.ImportConfigs(clientlogin).create(lime_type=workingLimeType, importfile=f)
        workingFile = filename

        workingConfig.behavior = behavior

        # Print begin
        printfilebeginimport(filename, tablename)

        return True
    else:
        printfilefiledoesnotexist(filename)
        return False


def waitforjobtocomplete(job):
    """ Checks for the job completion status and returns the latest job status when completed.
        Also increases the import index
    """
    timeoutlength = int(configFileTimeOutLengthIntervals)
    intervals = int(configFileSleepInterval)

    for i in range(timeoutlength):
            time.sleep(intervals)
            job = job.refresh()
            if job.status != 'pending' and job.status != 'running':
                break
    global importIndex
    importIndex += 1

    return job
# endregion

# region Print to file
#########################
# Print to log file section #
#########################


def printfilefiledoesnotexist(importfile):
    with open(configFileLogFile, 'a', encoding=get_file_encoding(configFile[importfile])) as file:
        file.write(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) +
                   ' Import file does not exist: ' + str(configFile[importfile]) + '\n')


def printfilebeginimport(sourcename, tablename):
    sourcepath = configFile[sourcename]

    with open(configFileLogFile, 'a', encoding=get_file_encoding(sourcepath)) as file:
        file.write(
            '--- Import started (' + sourcename + ')[' + sourcepath + '] towards [' + tablename + ']: ' +
            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' --- \n')


def printfileendimport():
    with open(configFileLogFile, 'a', encoding=get_file_encoding(configFileLogFile)) as file:
        file.write('--- Import ended: ' + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' --- \n\n')


def printfileimportcompleted(job):
    with open(configFileLogFile, 'a', encoding=get_file_encoding(configFileLogFile)) as file:
        file.write(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' Import #' + str(importIndex) + ': ' +
                   job.status + ' Created: ' + str(job.created_count) + ' Updated: ' + str(job.updated_count) + '\n')

        if job.has_errors:
            errors = job.errors.errors[:10]
            for i in range(0, len(errors)):
                file.write(str(errors[i]) + '\n')


def printimporttofile():
    with open(configFileLogFile, 'a', encoding=get_file_encoding(configFileLogFile)) as file:
        file.write('--- Import started: ' + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' --- \n')
# endregion

# region Print to console
############################
# Print to console section #
############################


def log(importance, message):
    """The method used in the importcore for printing messages to console. Uses termcolor for pretty coloring """
    global logmode

    if logmode > LogMode.Nothing:
        if logmode < LogMode.Full and importance < Importance.Medium:
            return
        else:
            textcolor = 'cyan'
            if importance == Importance.High:
                textcolor = 'red'
            elif importance == Importance.Medium:
                textcolor = 'green'
            cprint(message, textcolor)


# Prints some info about the job when it's completed
def printjobstatus(job):
    global importIndex
    log(Importance.Medium, 'Job #' + str(importIndex) + ' completed: [' + workingFile + ']')
    log(Importance.Medium, '-- Status: ' + str(job.status))
    log(Importance.Medium, '-- Created: ' + str(job.created_count))
    log(Importance.Medium, '-- Updated: ' + str(job.updated_count))
    log(Importance.Medium, '-- Errors: ' + str(job.errors_count))

    if job.errors_count > 0:
        for i in range(job.errors_count):
            log(Importance.High, job.errors.errors[i])
# endregion

# region Mapping
###################
# Mapping section #
###################


def addmapping(column, field, key=None):
    """ Adds a simple mapping to the current workingconfig using the current workingLimeType"""
    global continueimport

    if continueimport == True:
        workingConfig.add_mapping(lc.SimpleFieldMapping(column=column, field=workingLimeType.fields[field], key=key))
        if isinstance(workingLimeType.fields[field], (int)) and column == '':
            column = None
        if onlyedit == False:
            continueimport = validatefieldlength(field, column)


def addrelation(relationtable, column, field):
    """ Adds a relation mapping to the current workingconfig on the relationtable's field"""

    relation = workingLimeType.relations[relationtable]
    table = relation.related
    relationmapping = lc.RelationMapping(relation=relation, column=column, key_field=table.fields[field])
    workingConfig.add_mapping(relationmapping)


def addoptionmapping(columnname, fieldname, defaultkey, optionarray):
    """ Adds a option mapping to the given option field
        Make sure to put the option name first in the array and the column value second
    """
    field = workingLimeType.fields[fieldname]
    mapping = lc.OptionFieldMapping(field=field, column=columnname)
    mapping.default = field.option_by_key(defaultkey)

    for row in optionarray:
        mapping.map_option(column_val=row[0], option=field.option_by_key(row[1]))

    workingConfig.add_mapping(mapping)


def validatefieldlength(field, column):
    if workingLimeType.fields[field].type == 'string':
        fieldlength = workingLimeType.fields[field].length

        # Get longest instance of this field in the import file
        columnheader = column

        filepath = configFile[workingFile]
        content = []

        delimiter = '\t'
        if configFileDelimiter == ';':
            delimiter = configFileDelimiter

        with open(filepath, 'r', encoding=get_file_encoding(filepath)) as readfile:

            reader = csv.reader(readfile, delimiter=configFileDelimiter)

            # Find headers
            headers = next(reader)
            index = 0
            totalcolumns = len(headers)
            for line in headers:
                if line == columnheader:
                    break
                index += 1

            if index == totalcolumns:
                log(Importance.High, '[Validatefieldlength][WARNING][' + filepath + '] The header [' + columnheader + '] could not be found.')
                return False

            # Reset file reader and search for content in column index
            readfile.seek(0)

            maxlength = -sys.maxsize
            longestcontent = ''
            skippedheaders = False

            for line in readfile:
                if skippedheaders is False:
                    skippedheaders = True
                    continue
                columns = line.split(delimiter)
                if len(columns) >= totalcolumns:
                    if len(columns[index]) > maxlength:
                        maxlength = len(columns[index])
                        longestcontent = columns[index]
                    if maxlength > fieldlength:
                        log(Importance.High, '[Validatefieldlength][ERROR][' + filepath + '] Max length exceeded in column [' + column + '], ' + str(fieldlength) + ' characters allowed, "' + longestcontent + '" contains ' + str(maxlength) + ' characters!')
                        return False
                else:
                    log(Importance.High, '[Validatefieldlength][WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)

        content.clear()

        # log(Importance.High, field + ' ' + column + ' ' + str(maxlength) + ' ' + str(fieldlength) + ' ' + str(maxlength < fieldlength) + ' ' + longestcontent)
        return maxlength <= fieldlength
    return True

# endregion

# region File management

def splitcolumn(filename, splitside, sourcecolumnname, newcolumnname, columndelimiter, emptytext, encoding=None):
    """ Splits column data into a new column. Splits by columndelimiter """
    filepath = configFile[filename]

    rows = []
    if encoding is None:
        encoding = get_file_encoding(filepath)

    # Read all the data
    with open(filepath, 'r', encoding=encoding) as fileinput:
        reader = csv.reader(fileinput, delimiter=configFileDelimiter)

        # Find headers
        headers = next(reader)
        columnindex = 0
        for line in headers:
            if line == sourcecolumnname:
                break
            columnindex += 1

        for row in reader:
            if len(row) >= 1:
                if len(row[columnindex]) == 0:
                    rows.append(emptytext)
                else:
                    column = row[columnindex].split(columndelimiter, 1)
                    if splitside == SplitSide.Left or (len(column) > 1 and splitside == SplitSide.Right):
                        rows.append(column[splitside])
                    else:
                        rows.append(emptytext)

    addcolumnwithdata(filename, rows, newcolumnname, encoding)


def createimportfile(sourcename, destinationname, encoding=None):

    sourcepath = configFile[sourcename]
    destinationpath = configFile[destinationname]

    content = []

    if encoding is None:
        encoding = get_file_encoding(sourcepath)

    with open(sourcepath, 'r', encoding=encoding) as readfile:
        for line in readfile:
            content.append(line)

    writetofilefromarray(destinationname, content)
    content.clear()

    log(Importance.Medium, '[Create import file] (' + sourcename + ')[' + sourcepath + '] was used to create (' + destinationname + ')[' + destinationpath + ']')


def writetofile(filename, rows):
    filepath = configFile[filename]
    with open(filepath, 'w', encoding=get_file_encoding(filepath)) as fileoutput:
        writer = csv.writer(fileoutput, delimiter=configFileDelimiter, lineterminator='\n')
        writer.writerows(rows)


def writetofilefromarray(filename, rowarray):
    filepath = configFile[filename]
    encoding = 'utf-8'
    if path.isfile(filepath):
        encoding = get_file_encoding(filepath)
    with open(filepath, 'w', encoding=encoding) as fileoutput:
        for row in rowarray:
            fileoutput.write(row)


def cutfileatrow(sourcefilename, maxrows):
    filepath = configFile[sourcefilename]
    destinationfilepath = configFile[sourcefilename]

    rows = []
    # Read all the data
    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
        reader = csv.reader(fileinput, delimiter=configFileDelimiter)

        counter = 0

        # Read rows and add the new values
        for row in reader:
            if counter >= maxrows:
                break

            if len(row) != 0:
                rows.append(row)

            counter += 1

    writetofile(sourcefilename, rows)
    log(Importance.Low, '[Cutfileatrow][' + filepath + '] File has been cut at ' + str(maxrows) + ' into [' + sourcefilename + ']')


def removerowifempty(filename, columnheader, encoding=None):
    filepath = configFile[filename]
    filepathdestination = configFile[filename]
    content = []

    if encoding is None:
        encoding = get_file_encoding(filepath)

    with open(filepath, 'r', encoding=encoding) as readfile:
        content = __removerowifempty_job(filepath, columnheader, readfile, content)

    writetofilefromarray(filename, content)
    content.clear()


def __removerowifempty_job(filepath, columnheader, readfile, content):
    """ The method doing the actual job of removing rows"""

    reader = csv.reader(readfile, delimiter=configFileDelimiter)

    # Find headers
    headers = next(reader)
    index = 0
    totalcolumns = len(headers)
    for line in headers:
        if line == columnheader:
            break
        index += 1

    if index == totalcolumns:
            log(Importance.High, '[Removerows][WARNING][' + filepath + '] The header [' + columnheader + '] could not be found.')
            return False

    # Reset file reader and search for content in column index
    readfile.seek(0)

    for line in readfile:
        log(Importance.Low, line)

        delimiter = '\t'
        if configFileDelimiter == ';':
            delimiter = configFileDelimiter

        columns = line.split(delimiter)
        if len(columns) >= totalcolumns:
            if len(columns[index]) != 0:
                content.append(line)
            else:
                log(Importance.Medium, '[Removerowifempty][WARNING][' + filepath + '] Row removed (' + columnheader + ' missing): ' + line)
        else:
            log(Importance.Medium, '[Removerowifempty][WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)

    return content


def removerows(filename, count):
    """ Removes count rows from the file starting from the top """
    filepath = configFile[filename]
    newfilepath = configFile[filename]

    rows = []
    # Read all the data
    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
        reader = csv.reader(fileinput, delimiter=configFileDelimiter)

        try:
            # Skip count rows
            for x in range(0, count):
                next(reader)

            # Read rows and add the new values
            for row in reader:
                if len(row) != 0:
                    rows.append(row)

            writetofile(filename, rows)
        except:
            log(Importance.High, '[Remove rows] File most likely does not contain enough rows. Method aborted.')


def removerowequalto(filename, columnheader, text):
    filepath = configFile[filename]
    content = []

    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as readfile:

        reader = csv.reader(readfile, delimiter=configFileDelimiter)

        # Find headers
        headers = next(reader)
        index = 0
        totalcolumns = len(headers)
        for line in headers:
            if line == columnheader:
                break
            index += 1

        if index == totalcolumns:
            log(Importance.High, '[Removerowequalto][WARNING][' + filepath + '] The header [' + columnheader + '] could not be found.')
            return False

        # Reset file reader and search for content in column index
        readfile.seek(0)

        addedheaders = False

        for line in readfile:
            if addedheaders == False:
                addedheaders = True
                content.append(line)
                continue

            delimiter = '\t'
            if configFileDelimiter == ';':
                delimiter = configFileDelimiter

            columns = line.split(delimiter)
            if len(columns) >= totalcolumns:
                if columns[index] != text:
                    content.append(line)
                else:
                    log(Importance.Medium, '[Removerowequalto][UPDATE][' + filepath + '] Row removed (' + columnheader + ' equal to "' + text + '"): ' + line)
            else:
                log(Importance.High, '[Removerowequalto][WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)

    writetofilefromarray(filename, content)
    content.clear()


def removerownotequalto(filename, columnheader, text):
    filepath = configFile[filename]
    content = []

    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as readfile:

        reader = csv.reader(readfile, delimiter=configFileDelimiter)

        # Find headers
        headers = next(reader)
        index = 0
        totalcolumns = len(headers)
        for line in headers:
            if line == columnheader:
                break
            index += 1

        if index == totalcolumns:
            log(Importance.High, '[Removerownotequalto][WARNING][' + filepath + '] The header [' + columnheader + '] could not be found.')
            return False

        # Reset file reader and search for content in column index
        readfile.seek(0)

        addedheaders = False

        for line in readfile:
            if addedheaders is False:
                addedheaders = True
                content.append(line)
                continue

            delimiter = '\t'
            if configFileDelimiter == ';':
                delimiter = configFileDelimiter

            columns = line.split(delimiter)
            if len(columns) >= totalcolumns:
                if columns[index] == text:
                    content.append(line)
                else:
                    log(Importance.Medium, '[Removerownotequalto][UPDATE][' + filepath + '] Row removed ([' + columnheader + '] not equal to "' + text + '"): ' + line)
            else:
                log(Importance.High, '[Removerownotequalto][WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)

    writetofilefromarray(filename, content)
    content.clear()


def addheaderstofile(filename, headerstring, encoding=None):
    filepath = configFile[filename]
    content = []
    if encoding is None:
        encoding = get_file_encoding(filepath)

    with open(filepath, 'r', encoding) as readfile:
        content.append(headerstring + '\n')
        for line in readfile:
            content.append(line)

    writetofilefromarray(filename, content)
    content.clear()


def addvaluetoallrows(filename, columnname, value):
    """ Adds the value to all the rows in the new columnname and outputs to newfilename
    """
    filepath = configFile[filename]
    newfilepath = configFile[filename]

    rows = []
    # Read all the data
    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
        reader = csv.reader(fileinput, delimiter=configFileDelimiter)

        # Add headers
        row = next(reader)
        row.append(columnname)
        rows.append(row)

        # Read rows and add the new values
        for row in reader:
            if len(row) != 0:
                row.append(value)
                rows.append(row)

    writetofile(filename, rows)


def addvaluetorow(filename, columnheader, newcolumnname, conditions, notfoundvalue):
    """ Adds a new column to newfilename from filename.
        Looks for the conditions[0] in the columnindex and puts conditions[1] in the new column.
    """
    filepath = configFile[filename]
    conditionindex = 0
    resultindex = 1

    rows = []
    # Read all the data
    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
        reader = csv.reader(fileinput, delimiter=configFileDelimiter)

        # Find headers
        headers = next(reader)
        indexColumn = 0

        for line in headers:
            if line == columnheader:
                break
            indexColumn += 1

        fileinput.seek(0)

        # Add headers
        row = next(reader)
        row.append(newcolumnname)
        rows.append(row)

        # Read rows and add the new values
        for row in reader:
            if len(row) != 0:
                content = row[indexColumn]
                rowadded = False
                for condition in conditions:
                    if content == condition[conditionindex]:
                        row.append(condition[resultindex])
                        rows.append(row)
                        rowadded = True
                        log(Importance.Low, row)
                if not rowadded:
                    row.append(notfoundvalue)
                    rows.append(row)
                    log(Importance.Low, row)

    writetofile(filename, rows)


def addcolumnwithdata(filename, columnarray, newcolumnname, encoding=None):
    filepath = configFile[filename]
    newfilepath = configFile[filename]

    rows = []
    if encoding is None:
        encoding = get_file_encoding(filepath)

    # Read all the data
    with open(filepath, 'r', encoding=encoding) as fileinput:
        reader = csv.reader(fileinput, delimiter=configFileDelimiter)

        # Add headers
        row = next(reader)
        row.append(newcolumnname)
        rows.append(row)

        # Read rows and add the new values
        index = 0
        for row in reader:
            content = columnarray[index]
            row.append(content)
            rows.append(row)
            log(Importance.Low, '[Addcolumnwithdata][' + filepath + '] Added "' + columnarray[index] + '" to [' + newcolumnname + '] in row: ' + str(row))
            index += 1

    writetofile(filename, rows)


def addcombinedcolumn(filename, columnheader1, columnheader2, newcolumnname, separator):
    """ Adds a new column to newfilename from filename.
        Combines the value from column1 and column2 with the separator in between
    """
    filepath = configFile[filename]
    newfilepath = configFile[filename]

    rows = []
    # Read all the data
    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
        reader = csv.reader(fileinput, delimiter=configFileDelimiter)

        # Find headers
        headers = next(reader)
        indexColumn1 = 0
        indexColumn2 = 0
        totalcolumns = len(headers)

        for line in headers:
            if line == columnheader1:
                break
            indexColumn1 += 1

        fileinput.seek(0)

        for line in headers:
            if line == columnheader2:
                break
            indexColumn2 += 1

        fileinput.seek(0)

        # Add new header
        row = next(reader)
        row.append(newcolumnname)
        rows.append(row)

        print(indexColumn1)
        print(indexColumn2)

        # Read rows and add the new values
        for row in reader:
            if len(row) != 0:
                content = row[indexColumn1] + separator + row[indexColumn2]
                row.append(content)
                rows.append(row)

    writetofile(filename, rows)


def addsummarizedcolumn(filename, columnstosummarize, newcolumnname):
    """ Adds a new column to filename.
        Combines the values from columnheaders
    """
    filepath = configFile[filename]

    rows = []
    # Read all the data
    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
        reader = csv.reader(fileinput, delimiter=configFileDelimiter)

        # Fins header index in columnheaders
        columnheaders = []

        # Find headers
        headers = next(reader)
        totalcolumns = len(headers)

        for column in columnstosummarize:
            columnIndex = 0
            for line in headers:
                if line == column:
                    columnheaders.append(columnIndex)
                    break
                columnIndex += 1

        fileinput.seek(0)

        # Add new header
        row = next(reader)
        row.append(newcolumnname)
        rows.append(row)

        # Read rows, summarize columns and add the new value
        for row in reader:
            if len(row) != 0:
                rowvalue = 0

                for column in columnheaders:
                    if len(row[column]) != 0:
                        if __is_number(row[column]):
                            value = row[column].replace(',', '.')

                            # Try adding as a float
                            try:
                                value = float(value)
                                rowvalue += value
                            except ValueError:
                                pass

                            # Try adding as an integer
                            try:
                                value = unicodedata.numeric(value)
                                rowvalue += value
                            except (TypeError, ValueError):
                                pass

                row.append(rowvalue)
                rows.append(row)

    writetofile(filename, rows)


def adddatetofilename(file, extension):
    filename = configFile[file]
    newname = filename.replace('.' + extension, time.strftime(' %Y-%m-%d') + '.' + extension)
    rename(filename, newname)
    log(Importance.Medium, filename + ' has been renamed ' + newname)


def movefile(filesource, filedestination):
    filepathsource = configFile[filesource]
    filepathdestination = configFile[filedestination]
    shutil.move(filepathsource, filepathdestination)
    log(Importance.Medium, filepathsource + ' has been moved to ' + filepathdestination)


def copyfile(filesource, filedestination):
    filepathsource = configFile[filesource]
    filepathdestination = configFile[filedestination]
    shutil.copyfile(filepathsource, filepathdestination)
    log(Importance.Medium, filepathsource + ' has been copied to ' + filepathdestination)


def removefile(filesource):
    filepathsource = configFile[filesource]
    remove(filepathsource)
    log(Importance.Medium, filepathsource + ' has been removed')


def getimportfile(filename):
    return configFile[filename]


def thefileexists(filename):
    exists = path.isfile(configFile[filename])
    log(Importance.Low, '[' + configFile[filename] + '] exists: ' + str(exists))
    return exists

# endregion

# region Validation


def __is_number(s):
    """
    Determines if 's' is an integer or a float
    Returns bool
    Replaces ',' with '.' - you might need to do that as well if you are using data as numeric
    """
    s = s.replace(',', '.')
    try:
        float(s)
        return True
    except ValueError:
        pass

    try:
        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False

# endregion

# region Excel file management


def convertfromexcel(sheetname, datecolumns):

    x = xlrd.open_workbook(configFile['excel'])
    x1 = x.sheet_by_name(sheetname)

    founddatecolumns = []

    # Get datecolumns columnindex
    i = 0 # The first row, containing column names
    for j in range(x1.ncols):
        for datename in datecolumns:
            if datename == x1.cell(i,j).value:
                founddatecolumns.append(j)

    content = []

    # Add the file data to content[]
    for rownum in range(x1.nrows):
        content.append(x1.row_values(rownum))

    # Convert date columns to dates
    counter = -1
    for row in content:
        counter += 1
        for founddate in founddatecolumns: # For each found date column
            cell = x1.cell_value(counter, founddate)

            # Replace float with date
            if counter > 0 and len(str(cell)) > 0:
                cellasdate = datetime(*xlrd.xldate_as_tuple(cell, x.datemode))
                row[founddate] = cellasdate

    writetofile('excelout', content)

    return

# endregion

# region HistoryLogging


def addimporttohistorylog(job):
    global loghistorybody
    logtext = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' Import #' + str(importIndex) + ' ->['  + workingTablename + ']: ' + \
                   job.status + ' Created: ' + str(job.created_count) + ' Updated: ' + str(job.updated_count)

    if job.has_errors:
        logtext += ' Errors: ' + str(len(job.errors.errors))

    logtext += '\n'

    loghistorybody.append(logtext)


def importhistorylog():

    # Create temp file to import

    historynote = ""
    content = []

    for line in loghistorybody:
        historynote += line + '\n'

    content.append(historynote)

    writetofilefromarray('historyFile', content)
    content.clear()

    addheaderstofile('historyFile', 'note')
    addvaluetoallrows('historyFile', 'type', 'import')
    addvaluetoallrows('historyFile', 'coworker', 'Autoimport')

    log(Importance.Medium, 'Logging import history in Lime..')
    if prepareimport('historyFile', configFile['historyTable'], lc.ImportConfig.CreateAndUpdate):
        addmapping('note', configFile['historyNoteField'], True)
        # addrelation('coworker', 'coworker', 'firstname')

        optionlist = []
        optionlist.append([configFile['historyTypeKey'], 'import'])  # option key, row value
        addoptionmapping('type', configFile['historyTypeField'], configFile['historyTypeKey'], optionlist)

        runimport()
# endregion

# region Development (Not safe to use)


def getfilefromftp(user, password, url, fileurl):
    # from ftplib import FTP
    # import urllib, http.cookiejar as cookielib
    # import urllib.parse
    # import urllib.request
    # import requests
    #
    # r = requests.get(url, auth=(user, password))

    # import os
    # from ftplib import FTP
    #
    # ftp = FTP(url, user, password)


    #
    import urllib
    import urllib.request
    import urllib.parse
    import http.cookiejar

    username = user
    password = password

    cj = http.cookiejar.CookieJar()
    opener = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(cj))
    login_data = urllib.parse.urlencode({'username': username, 'password': password}).encode('utf-8')
    r = opener.open(url, login_data)
    # resp = opener.open(fileurl)
    print(r.read())

    # import requests
    # response = requests.get(fileurl,verify=False, auth=(user, password))
    # print(response.text)


    # ftp.login()
    # ftp.retrlines("LIST")
    #
    # ftp.cwd("folderOne")
    # ftp.cwd("subFolder") # or ftp.cwd("folderOne/subFolder")
    #
    # listing = []
    # ftp.retrlines("LIST", listing.append)
    # words = listing[0].split(None, 8)
    # filename = words[-1].lstrip()
    #
    # # download the file
    # local_filename = os.path.join(r"c:\myfolder", filename)
    # lf = open(local_filename, "wb")
    # ftp.retrbinary("RETR " + filename, lf.write, 8*1024)
    # lf.close()


def getallfilesinfolder(folderpath, extension):

    importfiles = []
    for filename in listdir(folderpath):
        if not path.isfile(path.join(folderpath, filename)):
            continue
        name, fileextension = path.splitext(filename)

        if fileextension == extension:
            importfiles.append(filename)

    log(Importance.Medium, 'Found ' + str(len(importfiles)) + ' files in dir: ' + folderpath)
    for filename in sorted(importfiles):
        importfilename = path.join(folderpath, filename)
        log(Importance.Low, importfilename)

    return


# From Screenbolaget. TODO: Generalize method
def removerowifnotnumber(source, destination, columnheader):

    filepath = configFile[source]
    content = []

    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as readfile:

        reader = csv.reader(readfile, delimiter=configFileDelimiter)

        # Find headers
        headers = next(reader)
        index = 0
        totalcolumns = len(headers)
        for line in headers:
            if line == columnheader:
                break
            index += 1

        # Reset file reader and search for content in column index
        readfile.seek(0)

        skippedHeaders = False
        for line in readfile:
            columns = line.split(';')
            if len(columns) >= totalcolumns:
                if skippedHeaders == False:
                    content.append(line)
                    skippedHeaders = True
                if (__is_number(columns[index]) and len(columns[index].strip()) == 6) or len(columns[index].strip()) == 0:
                    content.append(line)
                else:
                    print('[WARNING][' + filepath + '] Row removed (' + columnheader + ' is not the correct format): ' + line)
            else:
                print('[WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)

    writetofilefromarray(destination, content)
    content.clear()

# checks every line in the file until encoding is detected
def get_file_encoding(filePath):
    detector = UniversalDetector()
    filePath.ljust(60),
    detector.reset()
    for line in open(file=filePath, mode='rb'):
        detector.feed(line)
        if detector.done:
            break
    detector.close()
    return detector.result['encoding']
# endregion