
# C:\Python26\python.exe C:\PNWHerbaria\Scripts\imagemetadata.pyw

# Deleting a row:
# int GetGridCursorRow()
# EVT_GRID_CELL_RIGHT_CLICK

import os
import shutil
import wx
import wx.grid
import wx.html
import sqlite3
import time
from time import strftime


metadataDatabasePath = "C:\\PNWHerbaria\\Scripts\\SQLite\\imagemetadata.db"
folderNamesDatabasePath = "C:\\PNWHerbaria\\Scripts\\SQLite\\foldernames.db"
folderCodesDatabasePath = "C:\\PNWHerbaria\\Scripts\\SQLite\\foldercodes.db"
documentationPath = "C:\\PNWHerbaria\\Documentation\\Imaging-Documentation.pdf"

        
#---------------------------------------------------------------------------

class MainWindow(wx.Frame):
    def __init__(self, parent, title):
        wx.Frame.__init__(self, parent, title=title, size=(850,500))
        
        self.CreateStatusBar() # A Statusbar in the bottom of the window
        
        # Set up the menu:
        filemenu = wx.Menu()
        menuSave = filemenu.Append(wx.ID_SAVE, "&Save List to File"," Save metadata list to a database file for transfer to WTU")
        filemenu.AppendSeparator()
        menuExit = filemenu.Append(wx.ID_EXIT,"E&xit"," Terminate the program")
        
        adminmenu = wx.Menu()
        menuClear = adminmenu.Append(wx.ID_CLEAR, "&Clear Metadata List"," Clear the list of folder names (this can't be un-done!)")
        menuCodes = adminmenu.Append(wx.ID_EDIT, "Edit &Folder Codes"," Edit the list of folder codes")
        
        helpmenu = wx.Menu()
        menuAbout = helpmenu.Append(wx.ID_HELP, "&About"," Information about this program")
        menuDocumentation = helpmenu.Append(wx.ID_ABOUT, "&Documentation"," Help and Documentation")
        
        # Creat the menu bar and append menus:
        menuBar = wx.MenuBar()
        menuBar.Append(filemenu,"&File") # Adding the "filemenu" to the menu bar
        menuBar.Append(adminmenu,"A&dmin") # Adding the "adminmenu" to the menu bar
        menuBar.Append(helpmenu,"&Help") # Adding the "helpmenu" to the menu bar
        self.SetMenuBar(menuBar)  # Adding the menu bar to the Frame content.
        
        # Set events:
        self.Bind(wx.EVT_MENU, self.OnSave, menuSave)
        self.Bind(wx.EVT_MENU, self.OnExit, menuExit)
        self.Bind(wx.EVT_MENU, self.OnClear, menuClear)
        self.Bind(wx.EVT_MENU, self.OnEditCodes, menuCodes)
        self.Bind(wx.EVT_MENU, self.OnAbout, menuAbout)
        self.Bind(wx.EVT_MENU, self.OnDocumentation, menuDocumentation)
        
    def OnSave(self,e):
        dateTime = strftime("%Y%m%d-%H%M%S")
        self.dirName = ''
        self.fileName = "imagemetadata-%s.db" % (dateTime)
        dlg = wx.FileDialog(self, "Choose a file", self.dirName, self.fileName, "*.db", wx.FD_SAVE | wx.FD_OVERWRITE_PROMPT)
        if dlg.ShowModal() == wx.ID_OK:
            self.dirName = dlg.GetDirectory()
            self.fileName = dlg.GetFilename()
            shutil.copy2(metadataDatabasePath, os.path.join(self.dirName, self.fileName))
        dlg.Destroy()
    
    def OnClear(self,e):
        dlg = wx.MessageDialog(self, 'Are you sure you want to clear the list of folder names?  This action cannot be undone.', 'Caution', wx.YES_NO | wx.NO_DEFAULT | wx.ICON_QUESTION)
        if dlg.ShowModal() == wx.ID_YES:
            panel.listTable.GetTable().ClearTable()
        dlg.Destroy()

    def OnEditCodes(self,e):
        codeDlg = editFolderCodesDlg(None, -1, 'Edit Folder Codes')
        codeDlg.ShowModal()
        codeDlg.Destroy()
    
    def OnExit(self,e):
        self.Close(True)  # Close the frame.

    def OnAbout(self, event):
        description = """This is a simple application for recording image metadata
associated with the PNW Herbaria Consortium imaging and
databasing project."""

        licence = """MIT License:

Copyright (c) 2010, Ben Legler, Consortium of Pacific Northwest Herbaria

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS ORIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE."""

        info = wx.AboutDialogInfo()

        info.SetIcon(wx.Icon('Icons\\icon.png', wx.BITMAP_TYPE_PNG))
        info.SetName('Image Metadata, PNW Herbaria')
        #info.SetVersion('1.0')
        info.SetDescription(description)
        info.SetCopyright('(C) 2010 Ben Legler')
        info.SetWebSite('http://www.pnwherbaria.org/')
        info.SetLicence(licence)

        wx.AboutBox(info)

    def OnDocumentation(self, e):
        os.startfile(documentationPath)


#---------------------------------------------------------------------------

class MainPanel(wx.Panel):
    def __init__(self, parent):
        wx.Panel.__init__(self, parent)

        # create some sizers
        mainSizer = wx.BoxSizer(wx.VERTICAL)
        grid1 = wx.GridBagSizer(hgap=5, vgap=5)
        
        # Name of person doing imaging:
        self.nameLabel = wx.StaticText(self, label="Your name:")
        grid1.Add(self.nameLabel, pos=(0,0))
        imagedBy = self.GetImagedBy()
        self.nameField = wx.TextCtrl(self, value=imagedBy, size=(140,-1))
        grid1.Add(self.nameField, pos=(0,1))
        self.Bind(wx.EVT_TEXT, self.EvtName, self.nameField) # wx.EVT_CHAR
        
        # Index Herbariorum acronym or a made-up code for the collection being imaged:
        self.collectionLabel = wx.StaticText(self, label="Collection Acronym:")
        grid1.Add(self.collectionLabel, pos=(1,0))
        acronym = self.GetAcronym()
        self.collectionField = wx.TextCtrl(self, value=acronym, size=(60,-1))
        grid1.Add(self.collectionField, pos=(1,1))
        self.Bind(wx.EVT_TEXT, self.EvtCollection, self.collectionField) # wx.EVT_CHAR
        
        # Add Folder button:
        self.addFolderBtn = wx.Button(self, label="Add Folder")
        grid1.Add(self.addFolderBtn, pos=(2,1))
        self.Bind(wx.EVT_BUTTON, self.AddFolder, self.addFolderBtn)
        
        # add a spacer between the button and the metadata table:
        grid1.Add((10, 10), pos=(3,0))
        
        # Label for metadata grid:
        self.tableLabel = wx.StaticText(self, label="List of folders, in order imaged:")
        font = wx.Font(10, wx.DEFAULT, wx.NORMAL, wx.BOLD)
        self.tableLabel.SetFont(font) 
         
        # Table (grid) with list of folder names and metadata:
        self.listTable = MetadataGrid(self)
        
        mainSizer.Add(grid1, 0, wx.ALL, 5)
        mainSizer.Add(self.tableLabel, 0, wx.ALL, 5)
        mainSizer.Add(self.listTable, 1, wx.EXPAND | wx.ALL, 0)
        self.SetSizerAndFit(mainSizer)

    def EvtName(self, event):
        personName = event.GetString()
        
    def EvtCollection(self, event):
        collectionCode = event.GetString()

    def AddFolder(self,event):
        addDlg = addFolderDlg(None, -1, 'Add Folder Name')
        addDlg.ShowModal()
        addDlg.Destroy()

    def GetImagedBy(self):
        self.conn = sqlite3.connect(metadataDatabasePath)
        self.cursor = self.conn.cursor()
        self.cursor.execute("SELECT MAX(ID) FROM imagemetadata")
        for row in self.cursor:
            nextID = row[0]
        if nextID < 1:
            return ''
        else:
            self.cursor.execute("SELECT ImagedBy FROM imagemetadata WHERE ID=%i" % (nextID))
            for row in self.cursor:
                imagedBy = row[0]
        self.cursor.close()
        self.conn.close()
        if len(imagedBy) > 1:
            return imagedBy
        else:
            return ''

    def GetAcronym(self):
        self.conn = sqlite3.connect(metadataDatabasePath)
        self.cursor = self.conn.cursor()
        self.cursor.execute("SELECT MAX(ID) FROM imagemetadata")
        for row in self.cursor:
            nextID = row[0]
        if nextID < 1:
            return ''
        else:
            self.cursor.execute("SELECT Acronym FROM imagemetadata WHERE ID=%i" % (nextID))
            for row in self.cursor:
                acronym = row[0]
        self.cursor.close()
        self.conn.close()
        if len(acronym) > 1:
            return acronym
        else:
            return ''

#---------------------------------------------------------------------------

class addFolderDlg(wx.Dialog):
    def __init__(self, parent, id, title):
        wx.Dialog.__init__(self, parent, id, title, size=(550, 300))
        
        self.emptyList = ['']
        self.codeList = []
        self.setCodeList()
        
        #self.codeList = ['PNW', 'non-PNW', 'Type']
        
        # Sizers:
        vbox = wx.BoxSizer(wx.VERTICAL)
        nameGrid = wx.GridBagSizer(hgap=5, vgap=5)
        codeGrid = wx.GridBagSizer(hgap=5, vgap=5)
        hbox = wx.BoxSizer(wx.HORIZONTAL)
        
        # Controls:
        self.nameCodeLbl = wx.StaticText(self, label="Name Code:")
        self.nameCodeField = wx.TextCtrl(self, value="", size=(80,-1))
        self.sciNameLbl = wx.StaticText(self, label="Scientific Name:")
        self.sciNameField = wx.ComboBox(self, -1, "", (0, 0), (250, -1), self.emptyList, wx.CB_DROPDOWN)
        self.familyLbl = wx.StaticText(self, label="Family:")
        self.familyField = wx.TextCtrl(self, value="", size=(140,-1))
        
        self.folderCodeLbl = wx.StaticText(self, label="Folder Code:")
        self.folderCodeField = wx.ComboBox(self, -1, "", (0, 0), (140, -1), self.codeList, wx.CB_DROPDOWN)
        
        self.saveBtn = wx.Button(self, -1, 'Save', size=(70, 30))
        self.cancelBtn = wx.Button(self, -1, 'Cancel', size=(70, 30))
        
        self.instructions1 = wx.StaticText(self, label="  ")
        self.instructions2 = wx.StaticText(self, label="  INSTRUCTIONS: Use the Name Code box to enter a code consisting of the first 3 letters of the genus, the")
        self.instructions3 = wx.StaticText(self, label="  first 3 letters of the species, and optionally the first 3 letters of the subspecies or variety (e.g.: pinconcon")
        self.instructions4 = wx.StaticText(self, label="  for Pinus contorta var. contorta).  You can also enter only the first 3 letters of a genus, or the first 3 letters")
        self.instructions5 = wx.StaticText(self, label="  of a family.  Then, use the Scientific Name drop-down list to select a name.  If the name you want doesn't")
        self.instructions6 = wx.StaticText(self, label="  appear, you can manually type it in.  If the family name does not auto-fill, then enter it manually.")
        
        # Events:
        self.nameCodeField.Bind(wx.EVT_KILL_FOCUS, self.LookupNameCode)
        self.sciNameField.Bind(wx.EVT_COMBOBOX, self.LookupFamily)
        self.saveBtn.Bind(wx.EVT_BUTTON, self.OnSave)
        self.cancelBtn.Bind(wx.EVT_BUTTON, self.OnCancel)
        
        # Add controls to sizers:
        nameGrid.Add((10, 20), pos=(0,0))
        nameGrid.Add(self.nameCodeLbl, pos=(1,0))
        nameGrid.Add(self.nameCodeField, pos=(2,0))
        nameGrid.Add(self.sciNameLbl, pos=(1,1))
        nameGrid.Add(self.sciNameField, pos=(2,1))
        nameGrid.Add(self.familyLbl, pos=(1,2))
        nameGrid.Add(self.familyField, pos=(2,2))
        nameGrid.Add((10, 10), pos=(3,0))
        
        codeGrid.Add((26, 10), pos=(0,0))
        codeGrid.Add(self.folderCodeLbl, pos=(0,1))
        codeGrid.Add(self.folderCodeField, pos=(1,1))
        codeGrid.Add((26, 15), pos=(2,0))
        
        hbox.Add(self.saveBtn, 1)
        hbox.Add(self.cancelBtn, 1, wx.LEFT, 5)
        
        # Add sizers and instructions to frame:
        vbox.Add(nameGrid, 0, wx.ALIGN_CENTER)
        vbox.Add(codeGrid, 0, wx.ALIGN_LEFT)
        vbox.Add(hbox, 0, wx.ALIGN_CENTER)
        vbox.Add(self.instructions1, 0, wx.ALIGN_LEFT)
        vbox.Add(self.instructions2, 0, wx.ALIGN_LEFT)
        vbox.Add(self.instructions3, 0, wx.ALIGN_LEFT)
        vbox.Add(self.instructions4, 0, wx.ALIGN_LEFT)
        vbox.Add(self.instructions5, 0, wx.ALIGN_LEFT)
        vbox.Add(self.instructions6, 0, wx.ALIGN_LEFT)
        self.SetSizer(vbox)

    def setCodeList(self):
        self.conn = sqlite3.connect(folderCodesDatabasePath)
        self.cursor = self.conn.cursor()
        self.cursor.execute("SELECT FolderCode FROM foldercodes")
        nextID = 0
        for row in self.cursor:
            self.codeList.append(row[0].strip())
        self.cursor.close()
        self.conn.close()
        

    def LookupNameCode(self, e):
        self.comboList = []
        if self.nameCodeField.GetValue() != "":
            self.conn = sqlite3.connect(folderNamesDatabasePath)
            self.cursor = self.conn.cursor()
            self.cursor.execute("SELECT ScientificName FROM foldernames WHERE Code LIKE '%s%s' ORDER BY ScientificName" % (self.nameCodeField.GetValue(), '%'))
            for row in self.cursor:
                self.comboList.append(row[0])
            self.sciNameField.Clear()
            self.sciNameField.SetItems(self.comboList)
            self.cursor.close()
            self.conn.close()
        else:
            self.sciNameField.Clear()
        return False
    
    def LookupFamily(self, e):
        if self.sciNameField.GetValue() != "":
            self.conn = sqlite3.connect(folderNamesDatabasePath)
            self.cursor = self.conn.cursor()
            self.cursor.execute("SELECT Family FROM foldernames WHERE ScientificName='%s'" % (self.sciNameField.GetValue()))
            family = ""
            for row in self.cursor:
                family = row[0]
            self.cursor.close()
            self.conn.close()
            self.familyField.SetValue(family)
    
    def OnSave(self, e):
        self.conn = sqlite3.connect(metadataDatabasePath)
        self.cursor = self.conn.cursor()
        self.cursor.execute("SELECT MAX(ID) FROM imagemetadata")
        nextID = 0
        for row in self.cursor:
            nextID = row[0]
        if nextID < 1:
            nextID = 1
        else:
            nextID = nextID + 1
        self.cursor.close()
        self.conn.close()
        family = self.familyField.GetValue()
        if family == "":
            self.conn = sqlite3.connect(folderNamesDatabasePath)
            self.cursor = self.conn.cursor()
            self.cursor.execute("SELECT Family FROM foldernames WHERE ScientificName='%s'" % (self.sciNameField.GetValue()))
            family = ""
            for row in self.cursor:
                family = row[0]
            self.cursor.close()
            self.conn.close()
        values = [nextID, panel.nameField.GetValue(), panel.collectionField.GetValue(), strftime("%Y-%m-%d"), strftime("%H:%M:%S"), family, self.sciNameField.GetValue(), self.folderCodeField.GetValue()]
        panel.listTable.GetTable().AppendRow(values)
        self.Destroy()  # Close the dialog.
        
    def OnCancel(self, e):
        self.Destroy()  # Close the dialog.


#---------------------------------------------------------------------------

class editFolderCodesDlg(wx.Dialog):
    def __init__(self, parent, id, title):
        wx.Dialog.__init__(self, parent, id, title, size=(180, 250))
        
        codeList = ['']
        
        mainSizer = wx.BoxSizer(wx.VERTICAL)
        
        # Add Code button:
        self.addCodeBtn = wx.Button(self, label="Add Code")
        self.Bind(wx.EVT_BUTTON, self.AddCode, self.addCodeBtn)
        
        # Table (grid) with list of folder codes:
        self.codeTable = codeGrid(self)
        
        mainSizer.Add(self.addCodeBtn, 0, wx.ALL, 5)
        mainSizer.Add(self.codeTable, 1, wx.EXPAND | wx.ALL, 0)
        #self.SetSizerAndFit(mainSizer)
        self.SetSizer(mainSizer)
        
    def OnClose(self, e):
        self.Destroy()  # Close the dialog.

    def AddCode(self, e):
        # Add another row to folder code grid
        self.conn = sqlite3.connect(folderCodesDatabasePath)
        self.cursor = self.conn.cursor()
        self.cursor.execute("SELECT MAX(ID) FROM foldercodes")
        nextID = 0
        for row in self.cursor:
            nextID = row[0]
        if nextID < 1:
            nextID = 1
        else:
            nextID = nextID + 1
        self.cursor.close()
        self.conn.close()
        self.codeTable.GetTable().AppendRow([nextID, ''])
        

#---------------------------------------------------------------------------

class AboutBox(wx.Dialog):
    def __init__(self):
        wx.Dialog.__init__(self, None, -1, "About <<project>>",
            style=wx.DEFAULT_DIALOG_STYLE|wx.THICK_FRAME|wx.RESIZE_BORDER|
                wx.TAB_TRAVERSAL)
        hwin = HtmlWindow(self, -1, size=(400,200))
        vers = {}
        vers["python"] = sys.version.split()[0]
        vers["wxpy"] = wx.VERSION_STRING
        hwin.SetPage(aboutText % vers)
        btn = hwin.FindWindowById(wx.ID_OK)
        irep = hwin.GetInternalRepresentation()
        hwin.SetSize((irep.GetWidth()+25, irep.GetHeight()+10))
        self.SetClientSize(hwin.GetSize())
        self.CentreOnParent(wx.BOTH)
        self.SetFocus()


#---------------------------------------------------------------------------

class MetadataTable(wx.grid.PyGridTableBase):
    def __init__(self):
        wx.grid.PyGridTableBase.__init__(self)
 
        self.colLabels = ["Imaged By", "Acronym", "Date", "Time", "Family", "Scientific Name", "Folder Code"]
        self.sqlFields = ["ImagedBy", "Acronym", "Date", "Time", "Family", "FolderName", "FolderCode"]
 
        self.dataTypes = [wx.grid.GRID_VALUE_STRING,
                          wx.grid.GRID_VALUE_STRING,
                          wx.grid.GRID_VALUE_STRING,
                          wx.grid.GRID_VALUE_STRING,
                          wx.grid.GRID_VALUE_STRING,
                          wx.grid.GRID_VALUE_STRING,
                          wx.grid.GRID_VALUE_STRING,
                          wx.grid.GRID_VALUE_STRING,
                          ]
        
        self.placeHolderRecord = False
        
        self.recordID = []
        self.data = []
        self.conn = sqlite3.connect(metadataDatabasePath)
        self.cursor = self.conn.cursor()
        self.cursor.execute("SELECT * FROM imagemetadata")
        for row in self.cursor:
            self.recordID.append(row[0])
            self.data.append([row[1], row[2], row[3], row[4], row[5], row[6], row[7]])
        
        # This is a little hack to make sure the grid is visible at startup even if no records are present.
        # This fake record is then removed shortly after creation of the grid.
        if len(self.data) < 1:
            self.recordID.append(0)
            self.data.append(['', '', '', '', '', '', ''])
            self.placeHolderRecord = True

    def __del__(self):
        self.cursor.close()
        self.conn.close()
    
    
    # required methods for the wxPyGridTableBase interface:
 
    def GetNumberRows(self):
        return len(self.data)
 
    def GetNumberCols(self):
        return len(self.data[0])
 
    def IsEmptyCell(self, row, col):
        try:
            return not self.data[row][col]
        except IndexError:
            return True
 
    # Get/Set values in the table.  The Python version of these
    # methods can handle any data-type, (as long as the Editor and
    # Renderer understands the type too,) not just strings as in the
    # C++ version.
    def GetValue(self, row, col):
        try:
            return self.data[row][col]
        except IndexError:
            return ''
 
    def SetValue(self, row, col, value):
        try:
            self.data[row][col] = value
            self.cursor.execute("UPDATE imagemetadata SET %s='%s' WHERE ID=%i" % (self.sqlFields[col], value, self.recordID[row]))
            self.conn.commit()
            return True
        except IndexError:
            return False


    # Some optional methods:
    
    # Called when the grid needs to display labels
    def GetColLabelValue(self, col):
        return self.colLabels[col]
 
    # Called to determine the kind of editor/renderer to use by
    # default, doesn't necessarily have to be the same type used
    # natively by the editor/renderer if they know how to convert.
    def GetTypeName(self, row, col):
        return self.dataTypes[col]
 
    # Called to determine how the data can be fetched and stored by the
    # editor and renderer.  This allows you to enforce some type-safety
    # in the grid.
    def CanGetValueAs(self, row, col, typeName):
        colType = self.dataTypes[col].split(':')[0]
        if typeName == colType:
            return True
        else:
            return False
 
    def CanSetValueAs(self, row, col, typeName):
        return self.CanGetValueAs(row, col, typeName)


    # My custom methods:

    def AppendRow(self, v):
        try:
            self.cursor.execute("INSERT INTO imagemetadata VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (v[0], v[1], v[2], v[3], v[4], v[5], v[6], v[7]))
            self.conn.commit()
            self.recordID.append(v[0])
            self.data.append([v[1], v[2], v[3], v[4], v[5], v[6], v[7]])
            if len(self.data) > 1:
                msg = wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_APPENDED, 1)
            else:
                msg = wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_INSERTED, 1, 1)
            self.GetView().ProcessTableMessage(msg)
            return True
        except:
            return False

    def DeleteRow(self, r):
        self.cursor.execute("DELETE FROM imagemetadata WHERE ID=%i" % (self.recordID[r]))
        self.conn.commit()
        del self.recordID[r]
        del self.data[r]
        msg = wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_DELETED, len(self.data)-1, 1)
        self.GetView().ProcessTableMessage(msg)
    
    def ClearTable(self):
        try:
            self.cursor.execute("DELETE FROM imagemetadata")
            self.conn.commit()
            deletedCount = len(self.data)
            self.recordID = []
            self.data = []
            msg = wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_DELETED, 0, deletedCount)
            self.GetView().ProcessTableMessage(msg)
            return True
        except:
            return False


#---------------------------------------------------------------------------

class MetadataGrid(wx.grid.Grid):
    def __init__(self, parent):
        wx.grid.Grid.__init__(self, parent, -1)

        self.LoadTable()

        wx.grid.EVT_GRID_CELL_LEFT_DCLICK(self, self.OnLeftDClick)
        #wx.grid.EVT_GRID_CELL_RIGHT_CLICK(self, self.showPopupMenu)
        wx.grid.EVT_GRID_LABEL_RIGHT_CLICK(self, self.showPopupMenu)
        
        self.selectedRow = -1
        
    def LoadTable(self):
        table = MetadataTable()

        # The second parameter means that the grid is to take ownership of the
        # table and will destroy it when done.  Otherwise you would need to keep
        # a reference to it and call its Destroy method later.
        self.SetTable(table, True)

        self.SetRowLabelSize(50)
        self.SetMargins(0,0)
        #self.AutoSizeColumns(True)
        #self.SetColSize(0, 40)
        self.SetColSize(0, 100)
        self.SetColSize(1, 70)
        self.SetColSize(2, 70)
        self.SetColSize(3, 70)
        self.SetColSize(4, 100)
        self.SetColSize(5, 210)
        self.SetColSize(6, 120)
        #self.SetSelectionMode(wx.grid.Grid.SelectRows)
        
        # This is a hack to make sure the grid is visible even if there are no records to display:
        if table.placeHolderRecord == True:
            table.ClearTable()

    # Override the default behaviour of not starting the cell editor on double clicks, but only a second click.
    def OnLeftDClick(self, evt):
        if self.CanEnableCellControl():
            self.EnableCellEditControl()

    def showPopupMenu(self, event):
        self.selectedRow = event.GetRow()
        self.SelectRow(self.selectedRow)
        if not hasattr(self, "deleteID"):
            self.deleteID = wx.NewId()

        menu = wx.Menu()
        item = wx.MenuItem(menu, self.deleteID, "Delete Row")
        #frame.Bind(wx.EVT_MENU, self.OnPopupDeleteRow, item)
        self.Bind(wx.EVT_MENU, self.OnPopupDeleteRow, item)
        menu.AppendItem(item)

        self.PopupMenu(menu)
        menu.Destroy()

    def OnPopupDeleteRow(self, event):
        #rows = self.GetSelectedRows()
        row = self.selectedRow
        if row > -1:
            #row = rows[0]
            dlg = wx.MessageDialog(self, "Delete row %s?  This action cannot be undone." % (row + 1), "Caution", wx.YES_NO | wx.NO_DEFAULT | wx.ICON_QUESTION)
            if dlg.ShowModal() == wx.ID_YES:
                self.GetTable().DeleteRow(row)
                self.Refresh()
            dlg.Destroy()


#---------------------------------------------------------------------------

class codeTable(wx.grid.PyGridTableBase):
    def __init__(self):
        wx.grid.PyGridTableBase.__init__(self)
 
        self.colLabels = ["Folder Code"]
        self.sqlFields = ["FolderCode"]
 
        self.dataTypes = [wx.grid.GRID_VALUE_STRING,
                          ]
        
        self.placeHolderRecord = False
        
        self.recordID = []
        self.data = []
        self.conn = sqlite3.connect(folderCodesDatabasePath)
        self.cursor = self.conn.cursor()
        self.cursor.execute("SELECT * FROM foldercodes")
        for row in self.cursor:
            self.recordID.append(row[0])
            self.data.append([row[1]])
        
        # This is a little hack to make sure the grid is visible at startup even if no records are present.
        # This fake record is then removed shortly after creation of the grid.
        if len(self.data) < 1:
            self.recordID.append(0)
            self.data.append([''])
            self.placeHolderRecord = True

    def __del__(self):
        self.cursor.close()
        self.conn.close()
    
    
    # required methods for the wxPyGridTableBase interface:
 
    def GetNumberRows(self):
        return len(self.data)
 
    def GetNumberCols(self):
        return len(self.data[0])
 
    def IsEmptyCell(self, row, col):
        try:
            return not self.data[row][col]
        except IndexError:
            return True
 
    # Get/Set values in the table.  The Python version of these
    # methods can handle any data-type, (as long as the Editor and
    # Renderer understands the type too,) not just strings as in the
    # C++ version.
    def GetValue(self, row, col):
        try:
            return self.data[row][col]
        except IndexError:
            return ''
 
    def SetValue(self, row, col, value):
        try:
            self.data[row][col] = value
            self.cursor.execute("UPDATE foldercodes SET %s='%s' WHERE ID=%i" % (self.sqlFields[col], value, self.recordID[row]))
            self.conn.commit()
            return True
        except IndexError:
            return False


    # Some optional methods:
    
    # Called when the grid needs to display labels
    def GetColLabelValue(self, col):
        return self.colLabels[col]
 
    # Called to determine the kind of editor/renderer to use by
    # default, doesn't necessarily have to be the same type used
    # natively by the editor/renderer if they know how to convert.
    def GetTypeName(self, row, col):
        return self.dataTypes[col]
 
    # Called to determine how the data can be fetched and stored by the
    # editor and renderer.  This allows you to enforce some type-safety
    # in the grid.
    def CanGetValueAs(self, row, col, typeName):
        colType = self.dataTypes[col].split(':')[0]
        if typeName == colType:
            return True
        else:
            return False
 
    def CanSetValueAs(self, row, col, typeName):
        return self.CanGetValueAs(row, col, typeName)


    # My custom methods:

    def AppendRow(self, v):
        try:
            self.cursor.execute("INSERT INTO foldercodes VALUES (?, ?)", (v[0], v[1]))
            self.conn.commit()
            self.recordID.append(v[0])
            self.data.append([v[1]])
            if len(self.data) > 1:
                msg = wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_APPENDED, 1)
            else:
                msg = wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_INSERTED, 1, 1)
            self.GetView().ProcessTableMessage(msg)
            return True
        except:
            return False

    def DeleteRow(self, r):
        self.cursor.execute("DELETE FROM foldercodes WHERE ID=%i" % (self.recordID[r]))
        self.conn.commit()
        del self.recordID[r]
        del self.data[r]
        msg = wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_DELETED, len(self.data)-1, 1)
        self.GetView().ProcessTableMessage(msg)
    
    def ClearTable(self):
        try:
            self.cursor.execute("DELETE FROM foldercodes")
            self.conn.commit()
            deletedCount = len(self.data)
            self.recordID = []
            self.data = []
            msg = wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_DELETED, 0, deletedCount)
            self.GetView().ProcessTableMessage(msg)
            return True
        except:
            return False


#---------------------------------------------------------------------------

class codeGrid(wx.grid.Grid):
    def __init__(self, parent):
        wx.grid.Grid.__init__(self, parent, -1)

        self.LoadTable()

        wx.grid.EVT_GRID_CELL_LEFT_DCLICK(self, self.OnLeftDClick)
        wx.grid.EVT_GRID_LABEL_RIGHT_CLICK(self, self.showPopupMenu)
        
        self.selectedRow = -1
        
    def LoadTable(self):
        table = codeTable()

        # The second parameter means that the grid is to take ownership of the
        # table and will destroy it when done.  Otherwise you would need to keep
        # a reference to it and call its Destroy method later.
        self.SetTable(table, True)

        self.SetRowLabelSize(50)
        self.SetMargins(0,0)
        self.SetColSize(0, 100)
        
        # This is a hack to make sure the grid is visible even if there are no records to display:
        if table.placeHolderRecord == True:
            table.ClearTable()

    # Override the default behaviour of not starting the cell editor on double clicks, but only a second click.
    def OnLeftDClick(self, evt):
        if self.CanEnableCellControl():
            self.EnableCellEditControl()

    def showPopupMenu(self, event):
        self.selectedRow = event.GetRow()
        self.SelectRow(self.selectedRow)
        if not hasattr(self, "deleteID"):
            self.deleteID = wx.NewId()

        menu = wx.Menu()
        item = wx.MenuItem(menu, self.deleteID, "Delete Code")
        self.Bind(wx.EVT_MENU, self.OnPopupDeleteCode, item)
        menu.AppendItem(item)
        
        self.PopupMenu(menu)
        menu.Destroy()

    def OnPopupDeleteCode(self, event):
        row = self.selectedRow
        if row > -1:
            self.GetTable().DeleteRow(row)
            self.Refresh()


#---------------------------------------------------------------------------

app = wx.App(False)
frame = MainWindow(None, "Image Metadata, PNW Herbaria")
panel = MainPanel(frame)
frame.Show()
app.MainLoop()
