# TableGrid.py
"""Edit an Oracle Table using wxGrid control

Copyright (c) 1999 SIA "ANK"

this module is free software.  it may be used under same terms as Python itself

History:
26-sep-1999 [als] created
02-oct-1999 [als] it seems to work, in the 1st approximation
05-oct-1990 [als] renamed to TableGrid.py since i've renounced the 8.3 scheme
"""
__version__ ="$Revision: 1.1 $"
# $RCSfile: TableGrid.py,v $

import sys, string, types
if __debug__:
    sys.path.insert(0, "E:\\LIS_Oracle\\UI.Python")
from wxPython.wx import * # GUI
from wxPython.lib.sizers import *
from gettext import _	# i18n

import debug
from debug import trace

tableUPDATE =1
tableINSERT =2
tableDELETE =4

col_IS_KEY =1
col_NULLABLE =2
col_READONLY =4

class SQLcolumn:
    """Column description for TableGrid
    
    class attributes:
       NULLFORMAT -- string displayed in cells whose value is NULL
       maxwidth - maximum column widths (dictionary keyed by SQL type)
    object attributes:
       attr - bitmask with bits col_IS_KEY, col_NULLABLE and col_READONLY
       name - SQL column name
       header - display title
       width - grid column width (in chars)
       sql_type - SQL type name
       size - display size from dbi description
       isize - internal size
       precision
       scale
    methods:
       SetDescription(type_code, display_size,
           internal_size, precision, scale, null_ok)
       FormatValue()
       PackValue()
       IsKey()
       IsNullable()
       IsReadOnly()
    """
    NULLFORMAT =".null."
    # maximum grid column widths (in characters)
    # if column type not present, None is used
    maxwidth ={ "NUMBER":12, None:20 }
    
    def __init__(self, name, attr=0, header=None, width=None):
        """create SQLcolumn object"""
        self.name =name
        self.attr =attr
        if header ==None: self.header =name
        else: self.header =header
        self.width =width
        self.sql_type =self.size =self.isize =self.precision =self.scale =None
    
    def SetDescription(self, type_code, display_size,
           internal_size=None, precision=None, scale=None, null_ok=1):
        """update column attributes from dbi.description"""
        self.sql_type =type_code
        self.size =display_size
        self.isize =internal_size
        self.precision =precision
        self.scale =scale
        if null_ok: self.attr =self.attr | col_NULLABLE
        else: self.attr =self.attr & ~col_NULLABLE
        # update display width (or should this be separate method?)
        if self.width ==None: self.width =display_size
        if self.width <0: self.width =0
        if self.width >display_size: self.width =display_size
        # do not display too large columns
        if self.maxwidth.has_key(type_code):
            if self.width >self.maxwidth[type_code]:
                self.width =self.maxwidth[type_code]
        else:
            if self.width >self.maxwidth[None]:
                self.width =self.maxwidth[None]
    
    def FormatValue(self, value):
        """format data for display"""
        if value ==None: return self.NULLFORMAT
        return str(value)
    
    def PackValue(self, value):
        """convert value from display format to internal one"""
        if self.IsNullable() and value ==self.NULLFORMAT: return None
        if self.sql_type =="NUMBER":
            if self.scale: return string.atof(value)
            else: return string.atoi(value)
        # XXX do something with dates?
        return value
    
    def IsKey(self):
        """return true if this column is key"""
        return (self.attr & col_IS_KEY) or 0
    
    def IsNullable(self):
        """return true if this column may contain NULL values"""
        return (self.attr & col_NULLABLE) or 0
    
    def IsReadOnly(self):
        """return true if this column is read-only"""
        return (self.attr & col_READONLY) or 0

class TableGrid(wxPanel):
    """display/edit sql data in grid
    
    class attributes:
        NULLFORMAT -- string displayed in cells whose value is NULL
    """
    NULLFORMAT =".null."
    
    def __init__(self, parent, dbc, tablename, columns, where="",
        id=-1, pos=wxDefaultPosition, size=wxDefaultSize
    ):
        """create a TableGrid window
        
        dbc -- dbi connection object
        columns -- list of displayed fields.
        each column is described by dictionary in the form:
            { field_name : [ attrib, header, width], ...}
            field_name may be "*" to select all fields
            attrib is bitmask with col_IS_KEY and col_READONLY bits
            header is column header
            width is column width (in chars)
            attrib, header, and width may be omitted
        parent, id, pos, size -- passed to wxPanel
        """
        wxPanel.__init__(self, parent, id, pos, size)
        
        trace("wxPanel.__init__ done")
        # layout
        buttons ={}
        ID_DEL =wxNewId()
        buttons["save"] =wxButton(self, wxID_SAVE, _("Save"))
        buttons["revert"] =wxButton(self, wxID_REVERT, _("Revert"))
        buttons["delete"] =wxButton(self, ID_DEL, _("Delete"))
        buttons["new"] =wxButton(self, wxID_NEW, _("New"))
        self.buttons =buttons
        self.button_frame =wxBoxSizer(wxHORIZONTAL)
        self.button_frame.AddMany([
            (buttons["save"], 1),
            (buttons["revert"], 1),
            (buttons["delete"], 1),
            (buttons["new"], 1)
        ])
        EVT_BUTTON(self, wxID_SAVE, self.Commit)
        EVT_BUTTON(self, wxID_REVERT, self.Requery)
        EVT_BUTTON(self, wxID_NEW, self.AppendRow)
        EVT_BUTTON(self, ID_DEL, self.DelRow)
        trace("buttons created")
        self.grid =wxGrid(self, -1)
        trace( "grid created")
        self.sizer =wxBoxSizer(wxVERTICAL, self.GetSize())
        self.sizer.AddMany([ (self.grid, 1), (self.button_frame, 0) ])
        self.sizer.CalcMin()
        trace("layout done")
        
        # data
        self.dbc =dbc
        self.keys =[]
        self.data =[]
        self.rows =[]
        self.dirty =0
        self.keynames =[]
        self.colnames ={}
        self.tablename =tablename
        self.columns =columns
        self.UpdateColnames()
        self.where =where

        if not self.FindColAttr(col_IS_KEY):
            debug.show_error("No key fields found")
            self.Destroy()
            return
        # update handling data
        self.curRow =0
        self.curCol =0
        EVT_GRID_SELECT_CELL(self.grid, self.SaveCell)
        
        # fetch data and adjust grid
        self.select()
    
    def FindColAttr(self, attribute):
        """return a list of columns having attribute"""
        columns =filter(
            lambda colno, testbit=attribute, cols=self.columns:
                cols[colno].attr & testbit,
            range(0, len(self.columns)))
        return map(lambda colno, cols=self.columns: cols[colno], columns)
    
    def UpdateColnames(self):
        """set self.colnames and self.colnames_uc to self.columns names"""
        self.colnames ={}
        self.colnames_uc ={}
        self.colnames_lc ={}
        uc =string.upper
        for col in self.columns:
            colname =col.name
            self.colnames[colname] =col
            self.colnames_uc[uc(colname)] =col
    
    def FindColByName(self, name, col_list =None):
        """find given name in self.columns()"""
        if self.colnames.has_key(name): return self.colnames[name]
        colname =string.upper(name)
        if self.colnames_uc.has_key(colname): return self.colnames_uc[colname]
        else: return None
    
    def select(self):
        """perform SQL select and update grid display"""
        trace("select")
        try:
            # save original field list.
            columns =self.columns
            # construct select list.
            dql =""
            for col in map(lambda col: col.name, self.columns):
                if (col =="*"): dql ="*"
                else:
                    # star can't be mixed with colnames
                    if dql =="*": pass
                    elif dql: dql =dql +", " +col
                    else: dql =col
            
            # make select statement    
            dql ="SELECT " +dql +" FROM " +self.tablename
            if self.where: dql =dql +" WHERE " +self.where
            
            trace("running " +dql)
            dbh =self.dbc.cursor()
            dbh.execute(dql)
            
            # make new column list matching result columns
            self.keynames =[]
            columns =[]
            clist =dbh.description
            # (name, type_code, display_size, internal_size,
            # precision, scale, null_ok)
            for col in clist:
                cdef =self.FindColByName(col[0]) or SQLcolumn(col[0])
                # grrr... how can i make this w/o creating local vars?
                (sql_type, size, isize, prec, dec, null) =col[1:]
                cdef.SetDescription(sql_type, size, isize, prec, dec, null)
                columns.append(cdef)
                if cdef.IsKey(): self.keynames.append(cdef.name)
            self.columns =columns
            self.UpdateColnames()
            
            # fetch data
            data =dbh.fetchall()
            # resize grid and write headers
            grid =self.grid
            grid.BeginBatch()
            grid.CreateGrid(len(data), len(self.columns))
            colno =0
            charwidth =self.GetCharWidth() *7/5   # don't trust metrics
            # set display column names and titles
            for col in range(0, len(self.columns)):
                cdef =self.columns[col]
                grid.SetColumnWidth(col, cdef.width *charwidth)
                grid.SetLabelValue(wxHORIZONTAL, cdef.header, col)
            
            # display data
            self.data =[]
            self.keys =[]
            self.rows =[]
            for row in range(0, len(data)):
                self.data.append([])
                self.keys.append([])
                self.rows.append(0)
                self.DisplayRow(row, data[row])
            
            # let wxGrid adjust it's display
            grid.UpdateDimensions()
            grid.AdjustScrollbars()
            # say "i'm clean"
            self.dirty =0
            self.buttons["save"].Enable(FALSE)
            # position cursor and save cell value for validation
            if self.curRow <0:
                self.curRow =0
            elif self.curRow >=len(self.rows):
                self.curRow =len(self.rows) -1
            if self.curCol <0:
                self.curCol =0
            elif self.curCol >=len(self.colnames):
                self.curCol =len(self.colnames) -1
            grid.EndBatch()
            grid.Refresh()
            grid.SetGridCursor(self.curRow, self.curCol)
        
        except:
            debug.show_exc()
            while self.grid.GetBatchCount() >0:
                self.grid.EndBatch()
    
    def DisplayRow(self, row, data):
        """put row data both into grid cells and internal buffer"""
        grid =self.grid
        self.data[row] =[]
        self.keys[row] =[]
        for col in range(0, len(data)):
            cdef =self.columns[col]
            cdata =data[col]
            self.data[row].append(cdata)
            if cdef.IsKey(): self.keys[row].append(cdata)
            self.SetCellValue(row, col)
        
    def SaveColWidths(self):
        """save column widths to restore display afer new select()"""
        grid =self.grid
        columns =self.columns
        if len(columns) !=grid.GetCols():
            raise RuntimeError, "Invalid columns found"
        charwidth =self.GetCharWidth() *7/5   # same as in select()
        for col in range(0, len(self.columns)):
            self.columns[col].width =grid.GetColumnWidth(col) /charwidth
    
    def Requery(self, event=None):
        """re-read table data"""
        if self.dirty:
            msg =wxMessageDialog(self,
                _("Table was modified.\nLose unsaved changes?"),
                _("Confirmation"),
                wxYES_NO | wxNO_DEFAULT | wxCENTRE | wxICON_QUESTION)
            if msg.ShowModal() !=wxID_YES: return
        self.SaveColWidths()
        self.select()
    
    def AppendRow(self, event=None):
        """insert new blank row"""
        grid =self.grid
        grid.BeginBatch()
        grid.AppendRows()
        self.data.append([])
        self.keys.append([])
        self.rows.append(tableINSERT)
        row =len(self.data) -1
        self.DisplayRow(row, [None] *len(self.colnames))
        grid.UpdateDimensions()
        self.SetMark(row)
        grid.SetGridCursor(row, 1)
        grid.AdjustScrollbars()
        grid.EndBatch()
    
    def DelRow(self, event=None):
        """delete current row; recall previously deleted"""
        trace("delete called with row %s from event %s" %(self.curRow, str(event)))
        row =self.curRow
        self.rows[row] =self.rows[row] ^ tableDELETE
        self.blot()
        self.SetMark(row)
    
    def Commit(self, event=None):
        """send changes to sql backend"""
        rows =self.rows
        for row in range(0, len(rows)):
            upd_flags =rows[row]
            if upd_flags:  # row is changed
                try:
                    if upd_flags & tableDELETE:
                        if not (upd_flags & tableINSERT):
                            self.SQLdelete(row)
                    elif upd_flags & tableINSERT:
                        self.SQLinsert(row)
                    else:
                        self.SQLupdate(row)
                except:
                    debug.show_exc()
        # requery (data may be changed by insert trigger)
        self.select()
    
    def SaveCell(self, event):
        """handle cell value changes
        
        called on cell change.  if current cell value differs
        from previously saved, updates data buffer.
        """
        trace("SaveCell: (%i, %i) => (%i, %i)" %(self.curRow, self.curCol, event.m_row, event.m_col))
        grid =self.grid
        (row, col) =(self.curRow, self.curCol)
        cdef =self.columns[col]
        if not cdef.IsReadOnly():
            try:
                oldValue =self.data[row][col]
                newValue =cdef.PackValue(grid.GetCellValue(row, col))
                if (type(oldValue) !=type(newValue)) or (oldValue !=newValue):
                    self.blot()
                    self.data[row][col] =newValue
                    self.rows[row] =self.rows[row] | tableUPDATE
                    self.SetMark(row)
            except:
                debug.show_exc()
        self.SetCellValue(row, col)
        # save new cell position
        (self.curRow, self.curCol) =(event.m_row, event.m_col)
        # sometimes i see strange things... may this help?
        thiscell =grid.GetCurrentRect()
        grid.Refresh(TRUE, thiscell)
        # goto text area
        entry =grid.GetTextItem()
        entry.SetSelection(0, entry.GetLastPosition())
        entry.SetFocus()
    
    def SetMark(self, row):
        """set/clear marker on the right side of the row label"""
        grid =self.grid
        label =str(row +1)
        rowflags =self.rows[row]
        if rowflags & tableDELETE: label ="---"
        elif rowflags & tableINSERT: label =label +"+"
        elif rowflags & tableUPDATE: label =label +"*"
        grid.SetLabelValue(wxVERTICAL, label, row)
        grid.Refresh()
    
    def SetCellValue(self, row, col):
        """display formatted data"""
        cdata =self.data[row][col]
        grid =self.grid
        font =grid.GetCellTextFont(row, col)
        if cdata ==None: font.SetStyle(wxITALIC)
        else: font.SetStyle(wxNORMAL)
        grid.SetCellTextFont(font, row, col)
        grid.SetCellValue(self.columns[col].FormatValue(cdata), row, col)
    
    def blot(self):
        """make me "dirty" (enable Commit())"""
        self.dirty =1
        self.buttons["save"].Enable(TRUE)
    
    def keycols(self):
        """return key column names (in data column order)"""
        return filter(lambda col: col.IsKey(), self.columns)
    
    def SQLdelete(self, row):
        """delete row from sql server table"""
        keycols =self.keycols()
        dml ="DELETE " +self.tablename +" WHERE " \
            +string.join(map(
                lambda n, keys=keycols: "%s=:p%s" %(keys[n].name, n +1),
                range(0, len(keycols))), " AND ")
        trace("executing " +dml)
        dbh =self.dbc.prepare(dml)
        dbh.execute(self.keys[row])
    
    def SQLinsert(self, row):
        """delete row from sql server table"""
        columns =self.columns
        # generally speaking, we can filter out null values here,
        # but this will probably ivalidate dbi statement cache
        dml ="INSERT INTO " +self.tablename +" (" \
            +string.join(map(lambda col: col.name, columns), ", ") \
            +")\n VALUES (" \
            +string.join(map(lambda n: ":p%s" %n,
                range(1, len(columns) +1)), ", ") \
            +")"
        trace("executing " +dml)
        dbh =self.dbc.prepare(dml)
        dbh.execute(tuple(self.data[row]))
    
    def SQLupdate(self, row):
        """update sql server table row"""
        keycols =self.keycols()
        dml ="UPDATE " +self.tablename +" SET " +string.join(map(
                lambda n, columns=self.columns:
                    "%s=:p%s" %(columns[n].name, n +1),
                range(0, len(self.columns))), ", ") \
            +"\n WHERE " +string.join(map(
                lambda n, columns=keycols, offset=len(self.columns) +1:
                    "%s=:p%s" %(columns[n].name, n +offset),
                range(0, len(keycols))), " AND ")
        trace("executing " +dml)
        dbh =self.dbc.prepare(dml)
        dbh.execute(tuple(self.data[row] +self.keys[row]))
    
    def Validate(self, event):
        """currently not used"""

        """if table is clean, it's safe to leave; otherwise prompt to save"""
        if self.dirty:
            if event.CanVeto(): buttons =wxYES_NO | wxCANCEL
            else: buttons =wxYES_NO
            msg =wxMessageDialog(self,
                _("Table was modified.\nCommit changes?"),
                _("Confirmation"),
                buttons | wxCENTRE | wxICON_QUESTION)
            reply =msg.ShowModal()
            if reply ==wxID_CANCEL: event.Veto(TRUE)
            else:
                if reply ==wxID_YES: self.Commit()
                self.SaveColWidths()
                # self.select()
    
    def OnSize(self, event):
      self.sizer.Layout(event.GetSize())
    
#----------------------------------------------------------------------
if __name__ == '__main__':
    import DCOracle
    dbc =DCOracle.Connect("scott/tiger")
    
    class MyFrame(wxFrame):
        """Very standard Frame class. Nothing special here!"""
        def __init__(self):
            wxFrame.__init__(self, NULL, -1, "TableGrid Test")
            self.sizer =wxBoxSizer(wxVERTICAL)
            table =TableGrid(self, dbc, "scott.emp",
                (SQLcolumn("empno", col_IS_KEY, "Emp.No", 6), SQLcolumn("*")))
            id =wxNewId()
            button =wxButton(self, id, "Exit")
            EVT_BUTTON(self, id, self.Exit)
            self.sizer.AddMany([(table, 1), (button, 0)])
        def OnSize(self, event):
            size = self.GetClientSize()
            self.sizer.Layout(size)
        def Exit(self, event):
            self.Close()

    class MyApp(wxApp):
        """This class is even less interesting than MyFrame."""
        def OnInit(self):
            frame = MyFrame()
            frame.Show(TRUE)
            self.SetTopWindow(frame)
            return TRUE
    
    app = MyApp(0)
    app.MainLoop()