94vrSBQQ

1__author__ = 'jma'
2# Joacim Marklund, Lundalogik, 2016
3# Core version 0.8
4
5# region Imports
6import time
7import csv
8import shutil
9import sys
10import xlrd
11import unicodedata
12import limeclient as lc
13from os import rename, listdir, path, remove
14from configobj import ConfigObj
15from enum import IntEnum
16from termcolor import cprint
17from datetime import datetime
18from chardet.universaldetector import UniversalDetector
19# endregion
20
21# region Classes
22
23
24class LogMode(IntEnum):
25    Nothing = 1
26    Normal = 2
27    Full = 3
28
29
30class Importance(IntEnum):
31    Low = 1
32    Medium = 2
33    High = 3
34
35
36class SplitSide(IntEnum):
37    Left = 0
38    Right = 1
39
40
41# endregion
42
43# region Variables
44configFile = None
45configFileLogFile = None
46configFileServer = ''
47configFileDatabase = ''
48configFileDelimiter = ''
49configFileTimeOutLengthIntervals = -1
50configFileSleepInterval = -1
51configFileUsername = ''
52configFilePassword = ''
53
54workingLimeType = None
55workingConfig = None
56workingFile = None
57workingTablename = ''
58importIndex = 0
59clientlogin = None
60continueimport = True
61onlyedit = False
62logmode = LogMode.Nothing
63loghistory = False
64loghistorybody = []
65# endregion
66
67# region Custom Code
68#########################
69# CUSTOM CODE           #
70#########################
71
72# Put all of the custom code for the project here
73
74
75
76
77
78#########################
79# END CUSTOM CODE       #
80#########################
81# endregion
82
83
84# region Core loop
85def setupconfig(filename):
86    global configFile
87    global configFileLogFile
88    global configFileTimeOutLengthIntervals
89    global configFileSleepInterval
90    global configFileDelimiter
91    global configFileServer
92    global configFileDatabase
93    global configFileUsername
94    global configFilePassword
95    # global loghistoryenabled
96
97    configFile = ConfigObj(filename)
98    configFileLogFile = configFile['logfile']
99    configFileTimeOutLengthIntervals = configFile['timeOutLengthIntervals']
100    configFileSleepInterval = configFile['sleepInterval']
101    configFileDelimiter = configFile['delimiter']
102    configFileServer = configFile['server']
103    configFileDatabase = configFile['database']
104    configFileUsername = configFile['username']
105    configFilePassword = configFile['password']
106
107    log(Importance.Low, '[Setup config] successful')
108
109
110def getclientlogin(verifycert):
111    global clientlogin
112    client = None   # TODO: Is this necessary?
113    if configFileDatabase == '':
114        client = lc.LimeClient(configFileServer, verify_ssl_cert=verifycert)
115    else:
116        client = lc.LimeClient(configFileServer, configFileDatabase, verify_ssl_cert=verifycert)
117
118    clientlogin = client.login(configFileUsername, configFilePassword)
119
120    log(Importance.Low, '[Get Client Login] successful')
121    return
122
123
124def importloop(configfilename, usecert, methodpreimport, methodimports, methodpostimport, onlyeditfiles, importlogmode, loghistoryafterimport):
125    """ Runs the entire loop of managing the import files and the imports.
126        Will not try to connect to Lime if onlyeditfiles is True
127    """
128    global onlyedit
129    global logmode
130    global loghistory
131    onlyedit = onlyeditfiles
132    logmode = importlogmode
133    loghistory = loghistoryafterimport
134
135    # Load the config file
136    setupconfig(configfilename)
137
138    if onlyeditfiles is False:
139        methodpreimport()
140
141        # Login
142        getclientlogin(usecert)
143
144        # Begin working
145        methodimports()
146
147        # Print end
148        methodpostimport()
149        printfileendimport()
150
151        if loghistory:
152            importhistorylog()
153    else:
154        methodpreimport()
155        methodpostimport()
156
157
158#############################
159# Import management section #
160#############################
161
162def runimport():
163    """ Finishes the import setup and starts the import. Waits for the import to finish before returning,
164        or returns if timer runs out
165    """
166
167    if continueimport:
168        job = postsetupimport()
169        job = waitforjobtocomplete(job)
170        printjobstatus(job)
171        printfileimportcompleted(job)
172
173        if loghistory:
174            addimporttohistorylog(job)
175    else:
176        log(Importance.High, '[Runimport][ERROR] Import has been aborted')
177
178    return
179
180
181def postsetupimport():
182    """ Creates and returns the import job, and prints current errors """
183    workingConfig.save()
184
185    configstatus = workingConfig.validate()
186    if len(configstatus.hal['errors']) > 0:
187        log(Importance.High, '[Postsetupimport] ' + str(configstatus.hal['errors']))
188        job = None
189    else:
190        log(Importance.Medium, 'Importing [' + workingFile + ']')
191        job = lc.ImportJobs(clientlogin).create(workingConfig)
192
193    return job
194
195
196def prepareimport(filename, tablename, behavior=lc.ImportConfig.CreateAndUpdate):
197    """ Setups the importer to import from filename's tablename. Add custom import behavior if wanted """
198    file = configFile[filename]
199
200    if thefileexists(filename):
201        with open(file, encoding=get_file_encoding(file)) as content:
202            f = lc.ImportFiles(clientlogin).create(filename='importfile' + str(importIndex + 1) + '.csv',
203                                                   content=content)
204            f.delimiter = configFileDelimiter
205            f.save()
206
207        global workingLimeType
208        global workingConfig
209        global workingFile
210        global workingTablename
211
212        workingTablename = tablename
213        workingLimeType = lc.LimeTypes(clientlogin).get_by_name(tablename)
214        workingConfig = lc.ImportConfigs(clientlogin).create(lime_type=workingLimeType, importfile=f)
215        workingFile = filename
216
217        workingConfig.behavior = behavior
218
219        # Print begin
220        printfilebeginimport(filename, tablename)
221
222        return True
223    else:
224        printfilefiledoesnotexist(filename)
225        return False
226
227
228def waitforjobtocomplete(job):
229    """ Checks for the job completion status and returns the latest job status when completed.
230        Also increases the import index
231    """
232    timeoutlength = int(configFileTimeOutLengthIntervals)
233    intervals = int(configFileSleepInterval)
234
235    for i in range(timeoutlength):
236            time.sleep(intervals)
237            job = job.refresh()
238            if job.status != 'pending' and job.status != 'running':
239                break
240    global importIndex
241    importIndex += 1
242
243    return job
244# endregion
245
246# region Print to file
247#########################
248# Print to log file section #
249#########################
250
251
252def printfilefiledoesnotexist(importfile):
253    with open(configFileLogFile, 'a', encoding=get_file_encoding(configFile[importfile])) as file:
254        file.write(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) +
255                   ' Import file does not exist: ' + str(configFile[importfile]) + '\n')
256
257
258def printfilebeginimport(sourcename, tablename):
259    sourcepath = configFile[sourcename]
260
261    with open(configFileLogFile, 'a', encoding=get_file_encoding(sourcepath)) as file:
262        file.write(
263            '--- Import started (' + sourcename + ')[' + sourcepath + '] towards [' + tablename + ']: ' +
264            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' --- \n')
265
266
267def printfileendimport():
268    with open(configFileLogFile, 'a', encoding=get_file_encoding(configFileLogFile)) as file:
269        file.write('--- Import ended: ' + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' --- \n\n')
270
271
272def printfileimportcompleted(job):
273    with open(configFileLogFile, 'a', encoding=get_file_encoding(configFileLogFile)) as file:
274        file.write(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' Import #' + str(importIndex) + ': ' +
275                   job.status + ' Created: ' + str(job.created_count) + ' Updated: ' + str(job.updated_count) + '\n')
276
277        if job.has_errors:
278            errors = job.errors.errors[:10]
279            for i in range(0, len(errors)):
280                file.write(str(errors[i]) + '\n')
281
282
283def printimporttofile():
284    with open(configFileLogFile, 'a', encoding=get_file_encoding(configFileLogFile)) as file:
285        file.write('--- Import started: ' + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' --- \n')
286# endregion
287
288# region Print to console
289############################
290# Print to console section #
291############################
292
293
294def log(importance, message):
295    """The method used in the importcore for printing messages to console. Uses termcolor for pretty coloring """
296    global logmode
297
298    if logmode > LogMode.Nothing:
299        if logmode < LogMode.Full and importance < Importance.Medium:
300            return
301        else:
302            textcolor = 'cyan'
303            if importance == Importance.High:
304                textcolor = 'red'
305            elif importance == Importance.Medium:
306                textcolor = 'green'
307            cprint(message, textcolor)
308
309
310# Prints some info about the job when it's completed
311def printjobstatus(job):
312    global importIndex
313    log(Importance.Medium, 'Job #' + str(importIndex) + ' completed: [' + workingFile + ']')
314    log(Importance.Medium, '-- Status: ' + str(job.status))
315    log(Importance.Medium, '-- Created: ' + str(job.created_count))
316    log(Importance.Medium, '-- Updated: ' + str(job.updated_count))
317    log(Importance.Medium, '-- Errors: ' + str(job.errors_count))
318
319    if job.errors_count > 0:
320        for i in range(job.errors_count):
321            log(Importance.High, job.errors.errors[i])
322# endregion
323
324# region Mapping
325###################
326# Mapping section #
327###################
328
329
330def addmapping(column, field, key=None):
331    """ Adds a simple mapping to the current workingconfig using the current workingLimeType"""
332    global continueimport
333
334    if continueimport == True:
335        workingConfig.add_mapping(lc.SimpleFieldMapping(column=column, field=workingLimeType.fields[field], key=key))
336        if isinstance(workingLimeType.fields[field], (int)) and column == '':
337            column = None
338        if onlyedit == False:
339            continueimport = validatefieldlength(field, column)
340
341
342def addrelation(relationtable, column, field):
343    """ Adds a relation mapping to the current workingconfig on the relationtable's field"""
344
345    relation = workingLimeType.relations[relationtable]
346    table = relation.related
347    relationmapping = lc.RelationMapping(relation=relation, column=column, key_field=table.fields[field])
348    workingConfig.add_mapping(relationmapping)
349
350
351def addoptionmapping(columnname, fieldname, defaultkey, optionarray):
352    """ Adds a option mapping to the given option field
353        Make sure to put the option name first in the array and the column value second
354    """
355    field = workingLimeType.fields[fieldname]
356    mapping = lc.OptionFieldMapping(field=field, column=columnname)
357    mapping.default = field.option_by_key(defaultkey)
358
359    for row in optionarray:
360        mapping.map_option(column_val=row[0], option=field.option_by_key(row[1]))
361
362    workingConfig.add_mapping(mapping)
363
364
365def validatefieldlength(field, column):
366    if workingLimeType.fields[field].type == 'string':
367        fieldlength = workingLimeType.fields[field].length
368
369        # Get longest instance of this field in the import file
370        columnheader = column
371
372        filepath = configFile[workingFile]
373        content = []
374
375        delimiter = '\t'
376        if configFileDelimiter == ';':
377            delimiter = configFileDelimiter
378
379        with open(filepath, 'r', encoding=get_file_encoding(filepath)) as readfile:
380
381            reader = csv.reader(readfile, delimiter=configFileDelimiter)
382
383            # Find headers
384            headers = next(reader)
385            index = 0
386            totalcolumns = len(headers)
387            for line in headers:
388                if line == columnheader:
389                    break
390                index += 1
391
392            if index == totalcolumns:
393                log(Importance.High, '[Validatefieldlength][WARNING][' + filepath + '] The header [' + columnheader + '] could not be found.')
394                return False
395
396            # Reset file reader and search for content in column index
397            readfile.seek(0)
398
399            maxlength = -sys.maxsize
400            longestcontent = ''
401            skippedheaders = False
402
403            for line in readfile:
404                if skippedheaders is False:
405                    skippedheaders = True
406                    continue
407                columns = line.split(delimiter)
408                if len(columns) >= totalcolumns:
409                    if len(columns[index]) > maxlength:
410                        maxlength = len(columns[index])
411                        longestcontent = columns[index]
412                    if maxlength > fieldlength:
413                        log(Importance.High, '[Validatefieldlength][ERROR][' + filepath + '] Max length exceeded in column [' + column + '], ' + str(fieldlength) + ' characters allowed, "' + longestcontent + '" contains ' + str(maxlength) + ' characters!')
414                        return False
415                else:
416                    log(Importance.High, '[Validatefieldlength][WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)
417
418        content.clear()
419
420        # log(Importance.High, field + ' ' + column + ' ' + str(maxlength) + ' ' + str(fieldlength) + ' ' + str(maxlength < fieldlength) + ' ' + longestcontent)
421        return maxlength <= fieldlength
422    return True
423
424# endregion
425
426# region File management
427
428def splitcolumn(filename, splitside, sourcecolumnname, newcolumnname, columndelimiter, emptytext, encoding=None):
429    """ Splits column data into a new column. Splits by columndelimiter """
430    filepath = configFile[filename]
431
432    rows = []
433    if encoding is None:
434        encoding = get_file_encoding(filepath)
435
436    # Read all the data
437    with open(filepath, 'r', encoding=encoding) as fileinput:
438        reader = csv.reader(fileinput, delimiter=configFileDelimiter)
439
440        # Find headers
441        headers = next(reader)
442        columnindex = 0
443        for line in headers:
444            if line == sourcecolumnname:
445                break
446            columnindex += 1
447
448        for row in reader:
449            if len(row) >= 1:
450                if len(row[columnindex]) == 0:
451                    rows.append(emptytext)
452                else:
453                    column = row[columnindex].split(columndelimiter, 1)
454                    if splitside == SplitSide.Left or (len(column) > 1 and splitside == SplitSide.Right):
455                        rows.append(column[splitside])
456                    else:
457                        rows.append(emptytext)
458
459    addcolumnwithdata(filename, rows, newcolumnname, encoding)
460
461
462def createimportfile(sourcename, destinationname, encoding=None):
463    
464    sourcepath = configFile[sourcename]
465    destinationpath = configFile[destinationname]
466
467    content = []
468
469    if encoding is None:
470        encoding = get_file_encoding(sourcepath)
471
472    with open(sourcepath, 'r', encoding=encoding) as readfile:
473        for line in readfile:
474            content.append(line)
475
476    writetofilefromarray(destinationname, content)
477    content.clear()
478
479    log(Importance.Medium, '[Create import file] (' + sourcename + ')[' + sourcepath + '] was used to create (' + destinationname + ')[' + destinationpath + ']')
480
481
482def writetofile(filename, rows):
483    filepath = configFile[filename]
484    with open(filepath, 'w', encoding=get_file_encoding(filepath)) as fileoutput:
485        writer = csv.writer(fileoutput, delimiter=configFileDelimiter, lineterminator='\n')
486        writer.writerows(rows)
487
488
489def writetofilefromarray(filename, rowarray):
490    filepath = configFile[filename]
491    encoding = 'utf-8'
492    if path.isfile(filepath):
493        encoding = get_file_encoding(filepath)
494    with open(filepath, 'w', encoding=encoding) as fileoutput:
495        for row in rowarray:
496            fileoutput.write(row)
497
498
499def cutfileatrow(sourcefilename, maxrows):
500    filepath = configFile[sourcefilename]
501    destinationfilepath = configFile[sourcefilename]
502
503    rows = []
504    # Read all the data
505    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
506        reader = csv.reader(fileinput, delimiter=configFileDelimiter)
507
508        counter = 0
509
510        # Read rows and add the new values
511        for row in reader:
512            if counter >= maxrows:
513                break
514
515            if len(row) != 0:
516                rows.append(row)
517
518            counter += 1
519
520    writetofile(sourcefilename, rows)
521    log(Importance.Low, '[Cutfileatrow][' + filepath + '] File has been cut at ' + str(maxrows) + ' into [' + sourcefilename + ']')
522
523
524def removerowifempty(filename, columnheader, encoding=None):
525    filepath = configFile[filename]
526    filepathdestination = configFile[filename]
527    content = []
528
529    if encoding is None:
530        encoding = get_file_encoding(filepath)
531        
532    with open(filepath, 'r', encoding=encoding) as readfile:
533        content = __removerowifempty_job(filepath, columnheader, readfile, content)
534
535    writetofilefromarray(filename, content)
536    content.clear()
537
538
539def __removerowifempty_job(filepath, columnheader, readfile, content):
540    """ The method doing the actual job of removing rows"""
541
542    reader = csv.reader(readfile, delimiter=configFileDelimiter)
543
544    # Find headers
545    headers = next(reader)
546    index = 0
547    totalcolumns = len(headers)
548    for line in headers:
549        if line == columnheader:
550            break
551        index += 1
552
553    if index == totalcolumns:
554            log(Importance.High, '[Removerows][WARNING][' + filepath + '] The header [' + columnheader + '] could not be found.')
555            return False
556
557    # Reset file reader and search for content in column index
558    readfile.seek(0)
559
560    for line in readfile:
561        log(Importance.Low, line)
562
563        delimiter = '\t'
564        if configFileDelimiter == ';':
565            delimiter = configFileDelimiter
566
567        columns = line.split(delimiter)
568        if len(columns) >= totalcolumns:
569            if len(columns[index]) != 0:
570                content.append(line)
571            else:
572                log(Importance.Medium, '[Removerowifempty][WARNING][' + filepath + '] Row removed (' + columnheader + ' missing): ' + line)
573        else:
574            log(Importance.Medium, '[Removerowifempty][WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)
575
576    return content
577
578
579def removerows(filename, count):
580    """ Removes count rows from the file starting from the top """
581    filepath = configFile[filename]
582    newfilepath = configFile[filename]
583
584    rows = []
585    # Read all the data
586    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
587        reader = csv.reader(fileinput, delimiter=configFileDelimiter)
588
589        try:
590            # Skip count rows
591            for x in range(0, count):
592                next(reader)
593
594            # Read rows and add the new values
595            for row in reader:
596                if len(row) != 0:
597                    rows.append(row)
598
599            writetofile(filename, rows)
600        except:
601            log(Importance.High, '[Remove rows] File most likely does not contain enough rows. Method aborted.')
602
603
604def removerowequalto(filename, columnheader, text):
605    filepath = configFile[filename]
606    content = []
607
608    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as readfile:
609
610        reader = csv.reader(readfile, delimiter=configFileDelimiter)
611
612        # Find headers
613        headers = next(reader)
614        index = 0
615        totalcolumns = len(headers)
616        for line in headers:
617            if line == columnheader:
618                break
619            index += 1
620
621        if index == totalcolumns:
622            log(Importance.High, '[Removerowequalto][WARNING][' + filepath + '] The header [' + columnheader + '] could not be found.')
623            return False
624
625        # Reset file reader and search for content in column index
626        readfile.seek(0)
627
628        addedheaders = False
629
630        for line in readfile:
631            if addedheaders == False:
632                addedheaders = True
633                content.append(line)
634                continue
635
636            delimiter = '\t'
637            if configFileDelimiter == ';':
638                delimiter = configFileDelimiter
639
640            columns = line.split(delimiter)
641            if len(columns) >= totalcolumns:
642                if columns[index] != text:
643                    content.append(line)
644                else:
645                    log(Importance.Medium, '[Removerowequalto][UPDATE][' + filepath + '] Row removed (' + columnheader + ' equal to "' + text + '"): ' + line)
646            else:
647                log(Importance.High, '[Removerowequalto][WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)
648
649    writetofilefromarray(filename, content)
650    content.clear()
651
652
653def removerownotequalto(filename, columnheader, text):
654    filepath = configFile[filename]
655    content = []
656
657    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as readfile:
658
659        reader = csv.reader(readfile, delimiter=configFileDelimiter)
660
661        # Find headers
662        headers = next(reader)
663        index = 0
664        totalcolumns = len(headers)
665        for line in headers:
666            if line == columnheader:
667                break
668            index += 1
669
670        if index == totalcolumns:
671            log(Importance.High, '[Removerownotequalto][WARNING][' + filepath + '] The header [' + columnheader + '] could not be found.')
672            return False
673
674        # Reset file reader and search for content in column index
675        readfile.seek(0)
676
677        addedheaders = False
678
679        for line in readfile:
680            if addedheaders is False:
681                addedheaders = True
682                content.append(line)
683                continue
684
685            delimiter = '\t'
686            if configFileDelimiter == ';':
687                delimiter = configFileDelimiter
688
689            columns = line.split(delimiter)
690            if len(columns) >= totalcolumns:
691                if columns[index] == text:
692                    content.append(line)
693                else:
694                    log(Importance.Medium, '[Removerownotequalto][UPDATE][' + filepath + '] Row removed ([' + columnheader + '] not equal to "' + text + '"): ' + line)
695            else:
696                log(Importance.High, '[Removerownotequalto][WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)
697
698    writetofilefromarray(filename, content)
699    content.clear()
700
701
702def addheaderstofile(filename, headerstring, encoding=None):
703    filepath = configFile[filename]
704    content = []
705    if encoding is None:
706        encoding = get_file_encoding(filepath)
707
708    with open(filepath, 'r', encoding) as readfile:
709        content.append(headerstring + '\n')
710        for line in readfile:
711            content.append(line)
712
713    writetofilefromarray(filename, content)
714    content.clear()
715
716
717def addvaluetoallrows(filename, columnname, value):
718    """ Adds the value to all the rows in the new columnname and outputs to newfilename
719    """
720    filepath = configFile[filename]
721    newfilepath = configFile[filename]
722
723    rows = []
724    # Read all the data
725    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
726        reader = csv.reader(fileinput, delimiter=configFileDelimiter)
727
728        # Add headers
729        row = next(reader)
730        row.append(columnname)
731        rows.append(row)
732
733        # Read rows and add the new values
734        for row in reader:
735            if len(row) != 0:
736                row.append(value)
737                rows.append(row)
738
739    writetofile(filename, rows)
740
741
742def addvaluetorow(filename, columnheader, newcolumnname, conditions, notfoundvalue):
743    """ Adds a new column to newfilename from filename.
744        Looks for the conditions[0] in the columnindex and puts conditions[1] in the new column.
745    """
746    filepath = configFile[filename]
747    conditionindex = 0
748    resultindex = 1
749
750    rows = []
751    # Read all the data
752    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
753        reader = csv.reader(fileinput, delimiter=configFileDelimiter)
754
755        # Find headers
756        headers = next(reader)
757        indexColumn = 0
758
759        for line in headers:
760            if line == columnheader:
761                break
762            indexColumn += 1
763
764        fileinput.seek(0)
765
766        # Add headers
767        row = next(reader)
768        row.append(newcolumnname)
769        rows.append(row)
770
771        # Read rows and add the new values
772        for row in reader:
773            if len(row) != 0:
774                content = row[indexColumn]
775                rowadded = False
776                for condition in conditions:
777                    if content == condition[conditionindex]:
778                        row.append(condition[resultindex])
779                        rows.append(row)
780                        rowadded = True
781                        log(Importance.Low, row)
782                if not rowadded:
783                    row.append(notfoundvalue)
784                    rows.append(row)
785                    log(Importance.Low, row)
786
787    writetofile(filename, rows)
788
789
790def addcolumnwithdata(filename, columnarray, newcolumnname, encoding=None):
791    filepath = configFile[filename]
792    newfilepath = configFile[filename]
793
794    rows = []
795    if encoding is None:
796        encoding = get_file_encoding(filepath)
797
798    # Read all the data
799    with open(filepath, 'r', encoding=encoding) as fileinput:
800        reader = csv.reader(fileinput, delimiter=configFileDelimiter)
801
802        # Add headers
803        row = next(reader)
804        row.append(newcolumnname)
805        rows.append(row)
806
807        # Read rows and add the new values
808        index = 0
809        for row in reader:
810            content = columnarray[index]
811            row.append(content)
812            rows.append(row)
813            log(Importance.Low, '[Addcolumnwithdata][' + filepath + '] Added "' + columnarray[index] + '" to [' + newcolumnname + '] in row: ' + str(row))
814            index += 1
815
816    writetofile(filename, rows)
817
818
819def addcombinedcolumn(filename, columnheader1, columnheader2, newcolumnname, separator):
820    """ Adds a new column to newfilename from filename.
821        Combines the value from column1 and column2 with the separator in between
822    """
823    filepath = configFile[filename]
824    newfilepath = configFile[filename]
825
826    rows = []
827    # Read all the data
828    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
829        reader = csv.reader(fileinput, delimiter=configFileDelimiter)
830
831        # Find headers
832        headers = next(reader)
833        indexColumn1 = 0
834        indexColumn2 = 0
835        totalcolumns = len(headers)
836
837        for line in headers:
838            if line == columnheader1:
839                break
840            indexColumn1 += 1
841
842        fileinput.seek(0)
843
844        for line in headers:
845            if line == columnheader2:
846                break
847            indexColumn2 += 1
848
849        fileinput.seek(0)
850
851        # Add new header
852        row = next(reader)
853        row.append(newcolumnname)
854        rows.append(row)
855
856        print(indexColumn1)
857        print(indexColumn2)
858
859        # Read rows and add the new values
860        for row in reader:
861            if len(row) != 0:
862                content = row[indexColumn1] + separator + row[indexColumn2]
863                row.append(content)
864                rows.append(row)
865
866    writetofile(filename, rows)
867
868
869def addsummarizedcolumn(filename, columnstosummarize, newcolumnname):
870    """ Adds a new column to filename.
871        Combines the values from columnheaders
872    """
873    filepath = configFile[filename]
874
875    rows = []
876    # Read all the data
877    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as fileinput:
878        reader = csv.reader(fileinput, delimiter=configFileDelimiter)
879
880        # Fins header index in columnheaders
881        columnheaders = []
882
883        # Find headers
884        headers = next(reader)
885        totalcolumns = len(headers)
886
887        for column in columnstosummarize:
888            columnIndex = 0
889            for line in headers:
890                if line == column:
891                    columnheaders.append(columnIndex)
892                    break
893                columnIndex += 1
894
895        fileinput.seek(0)
896
897        # Add new header
898        row = next(reader)
899        row.append(newcolumnname)
900        rows.append(row)
901
902        # Read rows, summarize columns and add the new value
903        for row in reader:
904            if len(row) != 0:
905                rowvalue = 0
906
907                for column in columnheaders:
908                    if len(row[column]) != 0:
909                        if __is_number(row[column]):
910                            value = row[column].replace(',', '.')
911
912                            # Try adding as a float
913                            try:
914                                value = float(value)
915                                rowvalue += value
916                            except ValueError:
917                                pass
918
919                            # Try adding as an integer
920                            try:
921                                value = unicodedata.numeric(value)
922                                rowvalue += value
923                            except (TypeError, ValueError):
924                                pass
925
926                row.append(rowvalue)
927                rows.append(row)
928
929    writetofile(filename, rows)
930
931
932def adddatetofilename(file, extension):
933    filename = configFile[file]
934    newname = filename.replace('.' + extension, time.strftime(' %Y-%m-%d') + '.' + extension)
935    rename(filename, newname)
936    log(Importance.Medium, filename + ' has been renamed ' + newname)
937
938
939def movefile(filesource, filedestination):
940    filepathsource = configFile[filesource]
941    filepathdestination = configFile[filedestination]
942    shutil.move(filepathsource, filepathdestination)
943    log(Importance.Medium, filepathsource + ' has been moved to ' + filepathdestination)
944
945
946def copyfile(filesource, filedestination):
947    filepathsource = configFile[filesource]
948    filepathdestination = configFile[filedestination]
949    shutil.copyfile(filepathsource, filepathdestination)
950    log(Importance.Medium, filepathsource + ' has been copied to ' + filepathdestination)
951
952
953def removefile(filesource):
954    filepathsource = configFile[filesource]
955    remove(filepathsource)
956    log(Importance.Medium, filepathsource + ' has been removed')
957
958
959def getimportfile(filename):
960    return configFile[filename]
961
962
963def thefileexists(filename):
964    exists = path.isfile(configFile[filename])
965    log(Importance.Low, '[' + configFile[filename] + '] exists: ' + str(exists))
966    return exists
967
968# endregion
969
970# region Validation
971
972
973def __is_number(s):
974    """
975    Determines if 's' is an integer or a float
976    Returns bool
977    Replaces ',' with '.' - you might need to do that as well if you are using data as numeric
978    """
979    s = s.replace(',', '.')
980    try:
981        float(s)
982        return True
983    except ValueError:
984        pass
985
986    try:
987        unicodedata.numeric(s)
988        return True
989    except (TypeError, ValueError):
990        pass
991
992    return False
993
994# endregion
995
996# region Excel file management
997
998
999def convertfromexcel(sheetname, datecolumns):
1000
1001    x = xlrd.open_workbook(configFile['excel'])
1002    x1 = x.sheet_by_name(sheetname)
1003
1004    founddatecolumns = []
1005
1006    # Get datecolumns columnindex
1007    i = 0 # The first row, containing column names
1008    for j in range(x1.ncols):
1009        for datename in datecolumns:
1010            if datename == x1.cell(i,j).value:
1011                founddatecolumns.append(j)
1012
1013    content = []
1014
1015    # Add the file data to content[]
1016    for rownum in range(x1.nrows):
1017        content.append(x1.row_values(rownum))
1018
1019    # Convert date columns to dates
1020    counter = -1
1021    for row in content:
1022        counter += 1
1023        for founddate in founddatecolumns: # For each found date column
1024            cell = x1.cell_value(counter, founddate)
1025
1026            # Replace float with date
1027            if counter > 0 and len(str(cell)) > 0:
1028                cellasdate = datetime(*xlrd.xldate_as_tuple(cell, x.datemode))
1029                row[founddate] = cellasdate
1030
1031    writetofile('excelout', content)
1032
1033    return
1034
1035# endregion
1036
1037# region HistoryLogging
1038
1039
1040def addimporttohistorylog(job):
1041    global loghistorybody
1042    logtext = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ' Import #' + str(importIndex) + ' ->['  + workingTablename + ']: ' + \
1043                   job.status + ' Created: ' + str(job.created_count) + ' Updated: ' + str(job.updated_count)
1044
1045    if job.has_errors:
1046        logtext += ' Errors: ' + str(len(job.errors.errors))
1047
1048    logtext += '\n'
1049
1050    loghistorybody.append(logtext)
1051
1052
1053def importhistorylog():
1054
1055    # Create temp file to import
1056
1057    historynote = ""
1058    content = []
1059
1060    for line in loghistorybody:
1061        historynote += line + '\n'
1062
1063    content.append(historynote)
1064
1065    writetofilefromarray('historyFile', content)
1066    content.clear()
1067
1068    addheaderstofile('historyFile', 'note')
1069    addvaluetoallrows('historyFile', 'type', 'import')
1070    addvaluetoallrows('historyFile', 'coworker', 'Autoimport')
1071
1072    log(Importance.Medium, 'Logging import history in Lime..')
1073    if prepareimport('historyFile', configFile['historyTable'], lc.ImportConfig.CreateAndUpdate):
1074        addmapping('note', configFile['historyNoteField'], True)
1075        # addrelation('coworker', 'coworker', 'firstname')
1076
1077        optionlist = []
1078        optionlist.append([configFile['historyTypeKey'], 'import'])  # option key, row value
1079        addoptionmapping('type', configFile['historyTypeField'], configFile['historyTypeKey'], optionlist)
1080
1081        runimport()
1082# endregion
1083
1084# region Development (Not safe to use)
1085
1086
1087def getfilefromftp(user, password, url, fileurl):
1088    # from ftplib import FTP
1089    # import urllib, http.cookiejar as cookielib
1090    # import urllib.parse
1091    # import urllib.request
1092    # import requests
1093    #
1094    # r = requests.get(url, auth=(user, password))
1095
1096    # import os
1097    # from ftplib import FTP
1098    #
1099    # ftp = FTP(url, user, password)
1100
1101
1102    #
1103    import urllib
1104    import urllib.request
1105    import urllib.parse
1106    import http.cookiejar
1107
1108    username = user
1109    password = password
1110
1111    cj = http.cookiejar.CookieJar()
1112    opener = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(cj))
1113    login_data = urllib.parse.urlencode({'username': username, 'password': password}).encode('utf-8')
1114    r = opener.open(url, login_data)
1115    # resp = opener.open(fileurl)
1116    print(r.read())
1117
1118    # import requests
1119    # response = requests.get(fileurl,verify=False, auth=(user, password))
1120    # print(response.text)
1121
1122
1123    # ftp.login()
1124    # ftp.retrlines("LIST")
1125    #
1126    # ftp.cwd("folderOne")
1127    # ftp.cwd("subFolder") # or ftp.cwd("folderOne/subFolder")
1128    #
1129    # listing = []
1130    # ftp.retrlines("LIST", listing.append)
1131    # words = listing[0].split(None, 8)
1132    # filename = words[-1].lstrip()
1133    #
1134    # # download the file
1135    # local_filename = os.path.join(r"c:\myfolder", filename)
1136    # lf = open(local_filename, "wb")
1137    # ftp.retrbinary("RETR " + filename, lf.write, 8*1024)
1138    # lf.close()
1139
1140
1141def getallfilesinfolder(folderpath, extension):
1142
1143    importfiles = []
1144    for filename in listdir(folderpath):
1145        if not path.isfile(path.join(folderpath, filename)):
1146            continue
1147        name, fileextension = path.splitext(filename)
1148
1149        if fileextension == extension:
1150            importfiles.append(filename)
1151
1152    log(Importance.Medium, 'Found ' + str(len(importfiles)) + ' files in dir: ' + folderpath)
1153    for filename in sorted(importfiles):
1154        importfilename = path.join(folderpath, filename)
1155        log(Importance.Low, importfilename)
1156
1157    return
1158
1159
1160# From Screenbolaget. TODO: Generalize method
1161def removerowifnotnumber(source, destination, columnheader):
1162
1163    filepath = configFile[source]
1164    content = []
1165
1166    with open(filepath, 'r', encoding=get_file_encoding(filepath)) as readfile:
1167
1168        reader = csv.reader(readfile, delimiter=configFileDelimiter)
1169
1170        # Find headers
1171        headers = next(reader)
1172        index = 0
1173        totalcolumns = len(headers)
1174        for line in headers:
1175            if line == columnheader:
1176                break
1177            index += 1
1178
1179        # Reset file reader and search for content in column index
1180        readfile.seek(0)
1181
1182        skippedHeaders = False
1183        for line in readfile:
1184            columns = line.split(';')
1185            if len(columns) >= totalcolumns:
1186                if skippedHeaders == False:
1187                    content.append(line)
1188                    skippedHeaders = True
1189                if (__is_number(columns[index]) and len(columns[index].strip()) == 6) or len(columns[index].strip()) == 0:
1190                    content.append(line)
1191                else:
1192                    print('[WARNING][' + filepath + '] Row removed (' + columnheader + ' is not the correct format): ' + line)
1193            else:
1194                print('[WARNING][' + filepath + '] Row does not contain enough columns: (' + str(len(columns)) + '): ' + line)
1195
1196    writetofilefromarray(destination, content)
1197    content.clear()
1198
1199# checks every line in the file until encoding is detected
1200def get_file_encoding(filePath):
1201    detector = UniversalDetector()
1202    filePath.ljust(60),
1203    detector.reset()
1204    for line in open(file=filePath, mode='rb'):
1205        detector.feed(line)
1206        if detector.done:
1207            break
1208    detector.close()
1209    return detector.result['encoding']
1210# endregion