"""Script to take an excel worlist generated by LIMS at ALS and convert it into a filterable and more condensed
excel sheet for ease of use"""

from tkinter import filedialog
from tkinter import *
import xlrd, re, xlsxwriter, tkinter as tk
#regex setup to recognize different portions of a samples input data
regexDict = {'sampleRegex':re.compile('^L\d\d\d\d\d\d\d-\d|^WG\d\d\d\d\d\d\d\d-\d'),  'workgroupRegex':re.compile("^WG\d\d\d\d\d\d\d$|WG\d\d\d\d\d\d\d\'"),  'productCodeRegex':re.compile('MS(-\w+)?-VA$|-ICP-'),
 'conductivityRegex':re.compile('Conductivity'),  'turbidityRegex':re.compile('Turbidity'),  'dateRegex':re.compile('^20\d\d-\d\d-\d\d'),
 'priorityRegex':re.compile('^E(\d)?$|^EP?$|^P(\w)?$'),  'matrixRegex':re.compile('Water|Tissue|Soil|Seawater|Dustfall|Urine|Filter|Food|Bulk'),  'rawRegex':re.compile('M\d\d\d|NP\d\d\d'),
 'statusRegex':re.compile('AVAL|NEED|REDO|WIP|APPR|CALC'),  'unitRegex':re.compile('\d+ \w+'),  'chlorideRegex':re.compile('Chloride'),
 'excelDateRegex':re.compile('\d\d\d\d\d\.(\d+)?')}
clientNameIndex = 1
clientIdIndex = 3
accManagerIndex = 2

#extract needed data from excel sheet and save each sample as a list
def extractData(sheetName, arrayName, worklist):
    sampleIndex = []

    def extractRows(startIndex, endIndex):
        global sheet
        for z in range(startIndex, endIndex):
            for x in range(0, sheetName.ncols):
                if sheet.cell_type(z, x) != xlrd.XL_CELL_EMPTY:
                    if regexDict['excelDateRegex'].search(str(sheet.cell(z, x))) and not re.search(' ', str(sheet.cell(z,x))):
                        info.append(str(xlrd.xldate_as_datetime(float(sheet.cell(z, x).value), worklist.datemode)))
                    else:
                        info.append(sheetName.cell(z, x).value)

    for i in range(sheetName.nrows):
        if regexDict['sampleRegex'].search(sheetName.cell(i, 0).value):
            sampleIndex.append(i)

    for i in range(0, len(sampleIndex)):
        info = []
        if i < len(sampleIndex) - 1:
            extractRows(sampleIndex[i], sampleIndex[i + 1])
        else:
            extractRows(sampleIndex[i], sheetName.nrows)
        arrayName.append(info)


#format the basic sample lists to make sure that each item is in the correct index
def formatData(arrayNameIn, arrayNameOut):
    global priority

    def regexAdd(regexIn):
        global priority
        if regexDict[regexIn].search(str(arrayNameIn[i][z])):
            info.append(arrayNameIn[i][z])
        if regexIn == 'priorityRegex' and regexDict[regexIn].search(str(arrayNameIn[i][z])):
            priority = True

    def indexAdd(indexIn):
        if z == indexIn:
            info.append(arrayNameIn[i][z])

    def regexValAdd(regexIn):
        if regexDict[regexIn].search(str(arrayNameIn[i][z])):
            if (regexIn == 'conductivityRegex' and re.search('uS/cm', str(info))):
                d = re.search(':', str(arrayNameIn[i][z]))
                info[len(info) - 1] = arrayNameIn[i][z][d.start() + 2:]
            else:
                d = re.search(':', str(arrayNameIn[i][z]))
                info.append(arrayNameIn[i][z][d.start() + 2:])

    for i in range(0, len(arrayNameIn)):
        info = []
        priority = False
        for z in range(0, len(arrayNameIn[i])):
            regexAdd('sampleRegex')
            indexAdd(clientNameIndex)
            indexAdd(accManagerIndex)
            indexAdd(clientIdIndex)
            regexAdd('priorityRegex')
            regexValAdd('conductivityRegex')
            regexValAdd('turbidityRegex')
            regexValAdd('chlorideRegex')
            if re.search('Comments', str(arrayNameIn[i][z])):
                info.append(arrayNameIn[i][z+1])

        if priority is False:
            info.insert(4, '')
        if not re.search('uS/cm', str(info)):
            info.insert(5, '')
        if not re.search('mg/L|mg/kg', str(info)):
            info.insert(6, '')
        if not re.search('NTU', str(info)):
            info.insert(7, '')
        copyNum = 0
        productCodes = []
        matrix = []
        status = []
        dates = []
        wgCount = 0
        workGroups = []
        for z in range(0, len(arrayNameIn[i])):
            if regexDict['productCodeRegex'].search(str(arrayNameIn[i][z])):
                productCodes.append(arrayNameIn[i][z])
                continue
            if regexDict['matrixRegex'].search(str(arrayNameIn[i][z])):
                matrix.append(arrayNameIn[i][z])
                continue
            if regexDict['statusRegex'].search(str(arrayNameIn[i][z])):
                status.append(arrayNameIn[i][z])
                continue
            if regexDict['dateRegex'].search(str(arrayNameIn[i][z])):
                dates.append(arrayNameIn[i][z])
                continue
            if regexDict['workgroupRegex'].search(str(arrayNameIn[i][z])):
                workGroups.append(arrayNameIn[i][z])

        while copyNum < len(productCodes):
            if copyNum > 0:
                info = info.copy()
                del info[5:10]
                del info[-2]
            info.insert(5, productCodes[copyNum])
            info.insert(6, matrix[copyNum])
            info.insert(7, status[copyNum])
            info.insert(8, dates[copyNum * 2 + 1])
            wgpresent = False
            for z in range(0, len(arrayNameIn[i]) - 6):
                if re.search(productCodes[copyNum], arrayNameIn[i][z]) and wgCount < len(workGroups):
                    if re.search(workGroups[wgCount], arrayNameIn[i][z + 5]) or re.search(workGroups[wgCount], arrayNameIn[i][z + 6]):
                        info.insert(9, workGroups[wgCount])
                        wgCount += 1
                        wgpresent = True

            if wgpresent == False:
                info.insert(9, '')

            if len(info) < 13:
                info.append('')
            if re.search('-D-', str(info)) and re.search('D\d\d\d', str(arrayNameIn[i])):
                d = re.search('D\d\d\d', str(arrayNameIn[i]))
                info.insert(13, str(arrayNameIn[i])[d.start():d.start() + 4])
            else:
                if re.search('-T-', str(info)) and re.search('T\d\d\d', str(arrayNameIn[i])):
                    d = re.search('T\d\d\d', str(arrayNameIn[i]))
                    info.insert(13, str(arrayNameIn[i])[d.start():d.start() + 4])
                else:
                    info.insert(13, '')

            arrayNameOut.append(info)
            copyNum += 1


#write the new excel file
def writeFile(newFileName, arrayName):
    workbook = xlsxwriter.Workbook(newFileName + '.xlsx')
    unlocked = workbook.add_format({'locked': False})
    allInfo = workbook.add_worksheet('Worklist')
    allInfo.set_column('A:XDF', None, unlocked)
    #formats for different cell types
    boldFormat = workbook.add_format({'bold':True,  'bottom':True,  'locked':True})
    problemFormat = workbook.add_format({'bg_color':'yellow',  'bottom':True,  'locked':True})
    screenFormat = workbook.add_format({'bg_color':'black',  'font_color':'white',  'bottom':True,  'locked':True})
    standardFormat = workbook.add_format({'bottom':True,  'locked':True})
    twoFormat = workbook.add_format({'bg_color':'yellow',  'bottom':True,  'locked':True})
    fiveFormat = workbook.add_format({'bg_color':'blue',  'font_color':'white',  'bottom':True,  'locked':True})
    tenFormat = workbook.add_format({'bg_color':'purple',  'font_color':'white',  'bottom':True,  'locked':True})
    #list of problem clients to recognize and automatically list as screen
    problemClients = {'aecom':re.compile('aecom', re.I),
     'alexco':re.compile('alexco', re.I),  'indequim':re.compile('indequim', re.I),  'ch2m hill':re.compile('ch2m hill', re.I),
     'city of kelowna':re.compile('city of kelowna', re.I),  'core labs':re.compile('core labs', re.I),  'corix utilities':re.compile('corix utilities', re.I),
     'diand':re.compile('diand', re.I),  'diane c&w':re.compile('diane(.*)c&w', re.I),  'district of nanaimo':re.compile('district of nanaimo', re.I),
     'fmf cape breton':re.compile('fmf cape breton', re.I),  'fuchs lubricants':re.compile('fuchs lubricants', re.I),  'funsin':re.compile('funsin', re.I),
     'gibraltar mines':re.compile('gibraltar mines', re.I),  'goldcorp':re.compile('goldcorp', re.I),  'golder':re.compile('golder(.*)edmonton|golder(.*)yellowknife', re.I),
     'hcell':re.compile('hcell', re.I),  'highland':re.compile('highland valley copper', re.I),  'huckleberry mine':re.compile('huckleberry mine', re.I),
     'km canada marine tlp':re.compile('km canada marine tlp', re.I),  'lorax':re.compile('lorax', re.I),  'minera penasquito':re.compile('minera penasquito', re.I),
     'miramar':re.compile('miramar', re.I),  'nautilus':re.compile('nautilus', re.I),  'tetra tech':re.compile('tetra tech', re.I),
     'thomson creek':re.compile('thomson creek', re.I),  'tuprag':re.compile('tuprag', re.I), 'wood environment':re.compile('wood environment', re.I)}

    def fillWorksheetTitles(worksheetName):
        titles = [
         'Sample ID', 'Client Name', 'Acc Manager', 'Client ID', 'Priority', 'Product Code', 'Matrix', 'Status',
         'Due Date', 'Workgroup', 'Conductivity', 'Chloride', 'Turbidity', 'Raw Location', 'Suggested Dilution', 'Comments', 'Notes']
        col = 0
        for title in titles:
            worksheetName.write(0, col, title, boldFormat)
            col += 1

    fillWorksheetTitles(allInfo)
    allInfo.protect('', {'autofilter':True,  'sort':True})
    row = 1
    for i in range(0, len(arrayName)):
        problem = False
        conductivity = 'screen'
        blank = False
        col = 0
        water = False
        for z in range(0, len(arrayName[i])):
            if z == 1:
                for client in problemClients:
                    if problemClients[client].search(arrayName[i][z]):
                        problem = True

                if problem == True:
                    allInfo.write(row, col, arrayName[i][z], problemFormat)
                else:
                    allInfo.write(row, col, arrayName[i][z], standardFormat)
            else:
                if z == 3 and re.search('BLANK|^FB$|^TB$', arrayName[i][z]):
                    blank = True
                    allInfo.write(row, col, arrayName[i][z], standardFormat)
                else:
                    if z == 4:
                        if not arrayName[i][z] == '':
                            allInfo.write(row, col, arrayName[i][z], problemFormat)
                    if z == 6:
                        if arrayName[i][z] == 'Water':
                            water = True
                        allInfo.write(row, col, arrayName[i][z], standardFormat)
                    else:
                        if z == 10 and re.search('uS/cm', str(arrayName[i][z])):
                            tempStr = str(arrayName[i][z]).replace(',', '')
                            temp = re.findall('\d+', tempStr)
                            if len(temp) > 0:
                                conductivity = float(temp[0])
                            allInfo.write(row, col, arrayName[i][z], standardFormat)
                        else:
                            if z == 14:
                                allInfo.write(row, col + 1, arrayName[i][z], standardFormat)
                            else:
                                allInfo.write(row, col, arrayName[i][z], standardFormat)
            col += 1

        if water == True:
            if blank is True or re.search('<', arrayName[i][10]) and re.search('<', arrayName[i][12]):
                allInfo.write(row, 14, 'Blank', standardFormat)
            else:
                if problem is True or conductivity == 'screen' or conductivity >= 20000:
                    allInfo.write(row, 14, 'Screen/History', screenFormat)
                else:
                    if conductivity >= 10000:
                        allInfo.write(row, 14, 'X10', tenFormat)
                    else:
                        if conductivity >= 4000:
                            allInfo.write(row, 14, 'X5', fiveFormat)
                        else:
                            if conductivity >= 2000:
                                allInfo.write(row, 14, 'X2', twoFormat)
                            else:
                                allInfo.write(row, 14, 'Straight', standardFormat)
        else:
            allInfo.write(row, 14, '', standardFormat)
        row += 1

    allInfo.autofilter(0, 0, 0, len(arrayName[0]) + 1)
    allInfo.freeze_panes(1, 0)
    allInfo.set_column(0, 0, 11)
    allInfo.set_column(1, 1, 20)
    allInfo.set_column(2, 2, 14)
    allInfo.set_column(3, 3, 20)
    allInfo.set_column(4, 4, 10)
    allInfo.set_column(5, 5, 20)
    allInfo.set_column(8, 8, 20)
    allInfo.set_column(9, 9, 13)
    allInfo.set_column(10, 10, 13)
    allInfo.set_column(11, 12, 10)
    allInfo.set_column(13, 13, 12)
    allInfo.set_column(14, 14, 17)
    allInfo.set_column(15, 15, 60)
    allInfo.set_column(16, 16, 30, unlocked)
    workbook.close()

#browse for existing file in the system
def browsefunc():
    global fileName
    fileName = filedialog.askopenfilename()
    label.configure(text=fileName)

#browse for previous extracted file to import comments
def browsefunc2():
    global priorFile
    global priorList
    priorList = filedialog.askopenfilename()
    label3.configure(text=priorList)
    priorFile = True

#convert the full file
def convertFile():
    global outputName
    global rawInfo
    global sampleInfo
    global sheet
    global worklist
    worklist = xlrd.open_workbook(fileName)
    sheet = worklist.sheet_by_index(0)
    extractData(sheet, rawInfo, worklist)
    formatData(rawInfo, sampleInfo)
    if priorFile == True:
        commentList = xlrd.open_workbook(priorList)
        commentSheet = commentList.sheet_by_index(0)
        commentExtraction(commentSheet, sampleInfo)
    outputName = filedialog.askdirectory() + '/' + txt.get()
    writeFile(outputName, sampleInfo)
    worklist.release_resources()
    rawInfo = []
    sampleInfo = []

#extract comments from previously extracted file
def commentExtraction(sheetName, arrayName):
    sampleComments = []
    for i in range(1, sheetName.nrows):
        data = []
        if not re.search('empty', str(sheetName.cell(i, 15))):
            data.append(sheetName.cell(i, 0).value)
            data.append(sheetName.cell(i, 5).value)
            data.append(sheetName.cell(i, 16).value)
            sampleComments.append(data)

    for i in range(0, len(arrayName)):
        for z in range(0, len(sampleComments)):
            if sampleComments[z][0] == arrayName[i][0] and sampleComments[z][1] == arrayName[i][5]:
                arrayName[i].append(sampleComments[z][2])

#set up and run the gui for the program
rawInfo = []
sampleInfo = []
worklist = None
sheet = None
fileName = None
outputName = None
priorList = None
priorFile = False
folderSelected = None
priority = False
win = tk.Tk()
win.title('Worklist Converter')
label = tk.Label(win, text='File path')
browse1 = tk.Button(win, text='Choose Input File', command=browsefunc)
label3 = tk.Label(win, text='Previous Worklist')
browse2 = tk.Button(win, text='Select Previous Worklist', command=browsefunc2)
label2 = tk.Label(win, text='Output file name (no extension)')
txt = Entry(win, width=20)
goButton = tk.Button(win, text='Convert', command=convertFile)
label.pack()
browse1.pack(pady=10)
label3.pack()
browse2.pack(pady=10)
label2.pack()
txt.pack(pady=10)
goButton.pack()
win.mainloop()