
import pdb
import os
import sys
import inspect
import shutil
import string
import datetime
from dateutil import parser

import xlsxwriter

import arcpy

import avi_attribute_qc

import avi_record

from srd.srd_exception import *
import srd.srd_logging as srd_logging
import srd.srd_misc as srd_misc
import srd.srd_audit_log as srd_audit_log
import srd.srd_featureclass_info as srd_featureclass_info
import srd.srd_recordset as srd_recordset

__author__ = 'Doug Crane'
__version__ = '1.0'

__all__ = ['AVI_AttributeTest']

# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
def _initBasePath():
    '''
    Dummy def to get location of where tool is deployed.
    '''

    return inspect.getabsfile(_initBasePath)
        
# ----------------------------------------------------------
class AVI_AttributeTest(object):
    '''
    Provides methods for creating AVI geodatabase feature class
    '''

    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def __init__(self):
        '''
        Parameters:
        '''

        # Set the workspace to temp FGDB as well.
        arcpy.env.XYTolerance = .001
        arcpy.env.XYResolution = .0001

        self._logChn = srd_logging.SRD_Log()
        
        pyPath = _initBasePath()
        pyName = os.path.basename(pyPath)
        pyDir = os.path.dirname(pyPath)
        pyBaseName,ext = os.path.splitext(pyName)

        self.pyDir = pyDir

        self.testDir = os.path.join(pyDir, 'ATT_TEST_DATA')

        # Each time process is run a copy of the following template is
        # taken and its feature class is populated with test data
        self.templateFGDBName = 'TEST_TEMPLATE.gdb'
        self.templateFGDBPath = os.path.join(self.testDir, self.templateFGDBName)

        self.outFGDBBaseName = 'ATT_TEST_'

        self.tblName = 'ATT_TEST'

        # Test data is entered by the user in the following worksheet
        self.xlsPath = os.path.join(self.testDir, 'AVI_ATTRIBUTE_TEST.xlsx')

        # Location of test data is provided in popup text file.
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def formatFilename(self, fileName):
        '''
        Take a string and return a valid filename constructed from the string.
        Uses a whitelist approach: any characters not present in valid_chars are
        removed. Also spaces are replaced with underscores.
         
        Note: this method may produce invalid filenames such as ``, `.` or `..`
        When I use this method I prepend a date string like '2009_01_15_19_46_32_'
        and append a file extension like '.txt', so I avoid the potential of using
        an invalid filename.
         
        '''

        valid_chars = "-_.() %s%s" % (string.ascii_letters, string.digits)
        filename = ''.join(c for c in fileName if c in valid_chars)
        filename = filename.replace(' ','_') # I don't like spaces in filenames.
        
        return filename
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def showPopupMsg(self, msg):
        '''
        Create a text file and populate with message then displays using notepad.

        Parameters:
            msg - message to display in file.
        '''

        txtBaseName = 'popup'

        # Delete existing instances of the popup text file.
        txtName = '%s.txt' % txtBaseName
        txtPath = os.path.join(self.testDir, txtName)
        idx = 0
        while os.path.exists(txtPath):
            try:
                os.remove(txtPath)
            except:
                pass
            idx += 1
            txtName = '%s_%s.txt' % (txtBaseName, idx)
            txtPath = os.path.join(self.testDir, txtName)

        # Word wrap message to better display 
        outMsg = srd_misc.wordWrap(msg)
        
        # Write popup msg to txt file
        with open(txtPath, 'w') as outChn:
            outChn.write('PLEASE NOTE:\n')
            outChn.write('%s\n' % outMsg)

        # Open and display with default TXT editor
        if os.path.exists(txtPath):
            os.startfile(txtPath)
        
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def getTestFGDB(self):
        '''
        Generates a new FGDB based on the template. This copy will be
        populated with the test data.
        '''

        scratchPath = arcpy.CreateUniqueName('%s.gdb' % self.outFGDBBaseName, self.testDir)

        arcpy.Copy_management(self.templateFGDBPath, scratchPath)

        return scratchPath
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def FGDBCleanup(self):
        '''
        If a lot of testing is performed then many copies of the template
        FGDB are created. This method will cleanup the copies where there
        is not schema locks on the data.
        '''

        self._logChn.logMsg('\n*** Performing FGDB cleanup...')
        
        idx = ''
        fgdbPath = os.path.join(self.testDir, '%s.gdb' % self.outFGDBBaseName)
        if os.path.exists(fgdbPath):
            try:
                self._logChn.logMsg('Deleting: %s' % fgdbPath)
                shutil.rmtree(fgdbPath)
            except:
                self._logChn.logMsg('   Could not delete: %s' % fgdbPath)

        for idx in range(0,500):
            fgdbPath = os.path.join(self.testDir, '%s%s.gdb' % (self.outFGDBBaseName, idx))
            if os.path.exists(fgdbPath):
                try:
                    self._logChn.logMsg('Deleting: %s' % fgdbPath)
                    shutil.rmtree(fgdbPath)
                except:
                    self._logChn.logMsg('   Could not delete: %s' % fgdbPath)

        # Delete existing instances of the popup text file.
        txtBaseName = 'popup'
        for idx in range(0,500):
            txtName = '%s_%s.txt' % (txtBaseName, idx)
            txtPath = os.path.join(self.testDir, txtName)
            try:
                os.remove(txtPath)
            except:
                pass

        txtBaseName = self.outFGDBBaseName
        for idx in range(0,500):
            txtName = '%s%s.txt' % (txtBaseName, idx)
            txtPath = os.path.join(self.testDir, txtName)
            try:
                os.remove(txtPath)
            except:
                pass
            
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def runAttQC(self, sheetPath, sheetName):
        '''
        This method will create a copy of the template FGDB and populate
        it with data from the attribute worksheet. There are two worksheets
        one designed to test a single AVI record and the other to test all
        instances of test data the user has compiled.

        Parameters:
            sheetPath - path to the workbook containing the AVI worksheets

            sheetName - name of the worksheet to use to populate the FGDB table
        '''

        
        
        # Generate a new version of FGDB by copying the template
        fgdbPath = self.getTestFGDB()
        tblPath = os.path.join(fgdbPath, self.tblName)

        # If no path to a workbook is passed then it defaults to the
        # one in the attribute testing folder.
        if not sheetPath:
            workbookFolder = os.path.dirname(self.xlsPath)
            sheetPath = os.path.join(self.xlsPath, '%s$' % sheetName)
        else:
            workbookFolder = os.path.dirname(sheetPath)
            sheetPath = os.path.join(sheetPath, '%s$' % sheetName)
            
        self._logChn.logMsg('\n*** Appending data from worksheet: %s contained in:\n %s' % (sheetName, sheetPath))
        
        arcpy.Append_management(sheetPath, tblPath, 'NO_TEST')

        # Execute the attribute QC on created table.
        self._logChn.logMsg('\n*** Running AVI ATT QC...')
        auditObj = avi_attribute_qc.AVI_AttributeAudit(tblPath)
        auditObj.auditAttributes()        

        # Name of QC report will be same as XLSX
        workbookPath = os.path.dirname(sheetPath)
        workbookName = os.path.basename(workbookPath)
        txtName,ext = os.path.splitext(workbookName)
        
        self.genMsgLog(fgdbPath, auditObj.version(), txtName, workbookFolder)

    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def genMsgLog(self, fgdbPath, qcVersion, txtName, workbookFolder):
        '''
        Generates a text file that contains the AVI call as well as the
        attribute validation for each record processed.

        Parameters
            fgdbPath - path toe the FGDB containing the validation log

            qcVersion - version of the AVI QC that was run.
        '''

        
        # Table that contains the AVI records.
        tblPath = os.path.join(fgdbPath, self.tblName)

        aviRecUtilObj = avi_record.AVI_Record(tblPath)
        attDumpDict = aviRecUtilObj.buildAttributeRecDictByOID()
        polyNumDict = aviRecUtilObj.buildPolyNumDictByOID()
        
        logPath = os.path.join(fgdbPath, 'ATT_TEST_att_log')
        
        # Read the errors and warnings associated with records.
        logDict = {}
        if not arcpy.Exists(logPath):
            self._logChn.logMsg('\n***\n*** No error-warning log file found\n***')
        else:
            with arcpy.da.SearchCursor(logPath, ['ID', 'TYPE', 'MESSAGE']) as cursor:
                for row in cursor:
                    oid = row[0]
                    logType = row[1]
                    msg = row[2]

                    if oid in logDict:
                        errWarningLists = logDict[oid]
                    else:
                        errWarningLists = [[],[]]

                    errList = errWarningLists[0]
                    warnList = errWarningLists[1]

                    if logType.upper() == 'ERROR':
                        errList.append(msg)
                    else:
                        warnList.append(msg)

                    errWarningLists = [errList, warnList]
                    logDict[oid] = errWarningLists

        reportName = '%s.txt' % txtName
        reportPath = os.path.join(workbookFolder, reportName)

        # Generate the text report.
        outChn = open(reportPath, 'w')

        outChn.write('AVI QC Test Results\n')
        outChn.write('Audit FGDB: %s\n' % fgdbPath)
        outChn.write('Processing Date: %s\n' % datetime.date.today())
        outChn.write('AVI QC Version: %s\n' % qcVersion)
        outChn.write('Test Run By: %s\n' % os.environ.get('USERNAME'))

        for oid,recDump in attDumpDict.items():
            outChn.write('--------------------------------------------------------\n')
            outChn.write('OID: %s\n' % oid)
            if oid in polyNumDict:
                polyNum = polyNumDict[oid]
            else:
                polyNum = 0
            outChn.write('POLY_NUM: %s\n' % polyNum)
            outChn.write('%s\n' % recDump[0])
            outChn.write('%s\n\n' % recDump[1])
            
            if oid in logDict:
                errList,warnList = logDict[oid]
            else:
                errList = []
                warnList = []

            if errList:
                outChn.write('---- ERRORS\n\n')
                for err in errList:
                    outChn.write('%s\n\n' %err)
            else:
                outChn.write('---- NO ERRORS\n\n')
                
            if warnList:
                outChn.write('---- WARNINGS\n\n')
                for warning in warnList:
                    outChn.write('%s\n\n' %warning)
            else:
                outChn.write('---- NO WARNINGS\n\n')
                
        outChn.close()

        # Display the report.
        if os.path.exists(reportPath):
            os.startfile(reportPath)

    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def fcToXLS(self, fcPath, workbookName):
        '''
        This method is used to extract AVI records from a feature
        class and create an Excel XLS workbook that can be used to
        test AVI calls. This tool is intended to be called from within
        ArcMap where a selection has been made on the features the user\
        wishes to test. The number of records is will convert is limited
        to 1000.

        Parameters:
            fcPath = path to the feature class.

            workbookName - name of workbook to create. This is options
                           and if not passed then uses the fcName taken
                           from the fcPath
        '''

        selCnt = int(arcpy.GetCount_management(fcPath).getOutput(0))
        if selCnt > 1000:
            self._logChn.logWarning('\n***\n*** This tool works on a selection set and only accepts a maximum of 1000 records, %s features are selected in your chosen layer\n***' % selCnt)
            return False

        if selCnt < 1:
            self._logChn.logWarning('\n***\n*** No features are selected for export\n***')
            return False

        self._logChn.logWarning('\n***\n*** Exporting %s features to Excel...\n***' % selCnt)
        
        # Name of workbook will be a unique name based on name of feature class
        desc = arcpy.Describe(fcPath)
        fcBaseName = desc.baseName
        fullPath = desc.catalogPath

        # Make sure it is a valid file name.
        if workbookName:
            baseName = self.formatFilename(workbookName)
        else:
            baseName = self.formatFilename(fcBaseName)

        # Ensure the file name is unique
        fcXlsPath = os.path.join(self.testDir, '%s.xlsx' % baseName)
        idx = 0
        while os.path.exists(fcXlsPath):
            idx += 1
            fcXlsPath = os.path.join(self.testDir, '%s_%s.xlsx' % (baseName, idx))

        # There is a problem with the most recent version of MS Office
        # where if you export using the feature class to EXCEL tool
        # you cannot directly use the result XLS. We get around this
        # problem by using xlsxwriter to perform the export.
        self._logChn.logMsg('Exporting selected features to XLSX: %s' % fcXlsPath)

        # Generate list of fields excluding those we do not want
        fcFieldNameList = []
        fieldTypeDict = {}
        for fcField in arcpy.ListFields(fcPath):
            if not fcField.type.lower() in ('blob', 'geometry', 'globalid', 'guid', 'raster'):
                fcFieldNameList.append(fcField.name)
                fieldTypeDict[fcField.name] = fcField.type

        # Build dictionary where key is field name and value is index in list of field names
        fieldNameIdxDict = {value: idx for idx, value in enumerate(fcFieldNameList)}

        workbook = xlsxwriter.Workbook(fcXlsPath)
        worksheet = workbook.add_worksheet('TEST_REC')

        # Write the header row using field names                
        row = 0
        col = 0
        for fieldName in fcFieldNameList:
            worksheet.write(row, col, fieldName)
            col += 1
            
        # Write rows of field values
        rowIdx = 1
        with arcpy.da.SearchCursor(fcPath, fcFieldNameList) as cursor:
            for row in cursor:
                colIdx = 0
                for fieldName in fcFieldNameList:
                    val = row[fieldNameIdxDict[fieldName]]
                    if fieldName in fieldTypeDict:
                        val = self.getValueByType(val, fieldTypeDict[fieldName])
                    worksheet.write(rowIdx, colIdx, val)
                    colIdx += 1
                rowIdx += 1

        # Add some metadata to the XLSX
        worksheet2 = workbook.add_worksheet('INFO')

        colIdx = 0
        rowIdx = 0
        worksheet2.write(rowIdx, colIdx, 'Extraction Date: %s' % datetime.date.today())
        rowIdx += 1
        worksheet2.write(rowIdx, colIdx, 'Source Table: %s' % fullPath)
        rowIdx += 1
        worksheet2.write(rowIdx, colIdx, 'Extraction Run By: %s' % os.environ.get('USERNAME'))

        workbook.close()        
        
        msg = '\n***\n*** Exported XLS is:\n%s\n***' % fcXlsPath
        self._logChn.logWarning(msg)
        self.showPopupMsg(msg)
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def getValueByType(self, srcFldVal, fieldType):
        '''
        Returns a value based on the field type
        
        Parameters:
            srcFldVal - the source value

            fieldType - the field type for field
        '''

        # If type are not the same then convert.
        if fieldType.lower() == 'integer':
            outFldVal = self.toInteger(srcFldVal)
        elif fieldType.lower() == 'smallinteger':
            outFldVal = self.toShortInteger(srcFldVal)
        elif fieldType.lower() == 'double':
            outFldVal = self.toFloat(srcFldVal)
        elif fieldType.lower() == 'single':
            outFldVal = self.toFloat(srcFldVal)
        elif fieldType.lower() == 'date':
            outFldVal = self.toDateStr(srcFldVal)
        elif fieldType.lower() == 'string':
            outFldVal = self.toString(srcFldVal)
        elif fieldType.lower() == 'oid':
            outFldVal = self.toInteger(srcFldVal)
        else:
            self._logChn.logError('Unsupported output data type: %s' % fieldType)
            outFldVal = srcFldVal

        return outFldVal
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def toString(self, srcFldVal):

        if srcFldVal:
            return str(srcFldVal)
        else:
            return ''

    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def toInteger(self, srcFldVal):

        if srcFldVal:
            # If the string is a float value then truncate at decimal point.
            if isinstance(srcFldVal, (str, unicode)):
                if '.' in srcFldVal:
                    tmp = srcFldVal.split('.')
                    if len(tmp) > 1:
                        srcFldVal = tmp[0]
                    if not srcFldVal:
                        srcFldVal = '0'            
            try:
                outFldVal = int(srcFldVal)
                return outFldVal
            except:
                self._logChn.logError('   *** Cannot translate: %s to integer (source value type: %s)' % (srcFldVal, type(srcFldVal)))
                return None
        else:
            return 0
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def toShortInteger(self, srcFldVal):

        if srcFldVal:
            # If the string is a float value then truncate at decimal point.
            if isinstance(srcFldVal, (str, unicode)):
                if '.' in srcFldVal:
                    tmp = srcFldVal.split('.')
                    if len(tmp) > 1:
                        srcFldVal = tmp[0]
                    if not srcFldVal:
                        srcFldVal = '0'
            try:
                outFldVal = int(srcFldVal)
                if outFldVal < -32768 or outFldVal > 32767:
                    self._logChn.logWarning('   *** Cannot translate: %s to short integer' % srcFldVal)
                    return None
                else:
                    return outFldVal
            except:
                self._logChn.logError('   *** Cannot translate: %s to short integer (source value type: %s)' % (srcFldVal, type(srcFldVal)))
                return None
        else:
            return 0
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def toFloat(self, srcFldVal):

        if srcFldVal:
            try:
                outFldVal = float(srcFldVal)
                return outFldVal
            except:
                self._logChn.logError('   *** Cannot translate: %s to float' % srcFldVal)
                return None
        else:
            return 0.0
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def toDateStr(self, srcFldVal):

        if srcFldVal:
            try:
                outFldVal = srcFldVal.strftime("%m/%d/%Y, %H:%M:%S")
                return outFldVal
            except:
                return srcFldVal
        else:
            return None
    # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def toDate(self, srcFldVal):

        if srcFldVal:
            try:
                outFldVal = parser.parse(srcFldVal)
                return outFldVal
            except:
                self._logChn.logError('   *** Cannot translate: %s to date' % srcFldVal)
                return None
        else:
            return None

#====================================================================
if __name__ == '__main__':

    # Modify as required.
    arcpy.env.overwriteOutput = True
    # If using geographic features then may be best to modify these.
    arcpy.env.XYTolerance = .001
    arcpy.env.XYResolution = .0001
    arcpy.gp.logHistory = False
    
    ##============================================================
    ## YOUR TEST CODE HERE

    myObj = AVI_AttributeTest()
        
    ##print myObj.getTestFGDB()

    ##myObj.FGDBCleanup()

    sheetPathArg = ''
    sheetPathArg = r'D:\pythonscripts\avi_class\ATT_TEST_DATA\AVI_VALIDATION_TEST_CASES.xlsx'
    sheetNameArg = 'TEST_REC'
    #sheetNameArg = 'ALL_RECS'
    
    ##myObj.runAttQC(sheetPathArg, sheetNameArg)

    fgdbPathArg = r'd:\pythonscripts\avi_class\ATT_TEST_DATA\ATT_TEST_3.gdb'
    versionArg = 'Nov 25, 2019'
    ##myObj.genMsgLog(fgdbPathArg, versionArg)
    
    fcPathArg = r'D:\pythonscripts\avi_class\test_data\tst_avi.lyr'
    fcPathArg = r'D:\pythonscripts\avi_class\test_data\test.gdb\AVIE_CopyFeatures'
    myObj.fcToXLS(fcPathArg, '')
    
