# sample_one.py

"""
Created  : Tue Jul 10 13:55:00 2012
Author   : Jak_o_Shadows
Link     : http://jak-o-shadows.users.sourceforge.net/python/wxpy/dblistctrl.html
Licensed : Under the GPL general public license v3.
Updated  : For wxPython Phoenix - 17/08/2019 (d/m/y) :-)
"""

import sqlite3
import random
import wx
import wx.lib.mixins.listctrl as listmix

# class MyFrame
# class MyApp

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

class MyFrame(wx.Frame, listmix.ListCtrlAutoWidthMixin):
    def __init__(self, *args, **kwds):
        kwds["style"] = wx.DEFAULT_FRAME_STYLE
        wx.Frame.__init__(self, *args, **kwds)

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

        # For allowing sorting in descending order.
        self.oldC = -1
        self.reverse = False
        self.command = "SELECT * FROM tbl"

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

        # Simplified init method.
        self.SetProperties()
        self.CreateCtrls()
        self.SetupDataBase()   # Populate the database.
        self.BindEvents()
        self.DoLayout()

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

    def SetProperties(self):
        """
        ...
        """

        self.SetTitle("Sample one (fill a list control with a database)")
        self.SetIcon(wx.Icon('wxwin.ico'))


    def CreateCtrls(self):
        """
        ...
        """

        self.listCtrl = wx.ListCtrl(self, -1,
                                    style=wx.LC_REPORT
                                       #| wx.BORDER_SUNKEN
                                       #| wx.LC_SORT_ASCENDING
                                       #| wx.LC_NO_HEADER
                                       | wx.LC_VRULES
                                       | wx.LC_HRULES
                                       | wx.LC_SINGLE_SEL
                                       )

        self.listCtrl.SetBackgroundColour("#ccff9c")

        self.listCtrl.InsertColumn(0, "Column - A")
        self.listCtrl.InsertColumn(1, "Column - B")
        self.listCtrl.InsertColumn(2, "Column - C")

        self.listCtrl.SetColumnWidth(0, 200)
        self.listCtrl.SetColumnWidth(1, 200)
        self.listCtrl.SetColumnWidth(2, 200)


    def SetupDataBase(self):
        """
        Open the database, add a table.
        """

        # Open a connection to a DataBase in RAM.
        self.con = sqlite3.connect(":memory:")

        command = "CREATE TABLE tbl(id INTEGER PRIMARY KEY AUTOINCREMENT,\
                                    a INT,\
                                    b TEXT,\
                                    c REAL)"

        # Add a table (tbl) with 4 columns).
        self.con.execute(command)
        self.con.commit()

        self.FillDataBase()


    def BindEvents(self):
        """
        Bind some events to an events handle.
        """

        self.Bind(wx.EVT_LIST_COL_CLICK, self.SetupSort, self.listCtrl)


    def DoLayout(self):
        """
        ...
        """

        sizer = wx.BoxSizer(wx.VERTICAL)

        sizer.Add(self.listCtrl, 1, wx.EXPAND, 0)

        self.SetSizer(sizer)
        sizer.Fit(self)
        self.Layout()


    def SetupSort(self, event):
        """
        Sets the command for filling the list control,
        based on what column is clicked.
        """

        # Get the column that was clicked on.
        c = event.GetColumn()

        if c == 0:
            # Order by first column.
            self.command = "SELECT * FROM tbl ORDER BY a"
        elif c == 1:
            # Order by second column.
            self.command = "SELECT * FROM tbl ORDER BY b"
        elif c == 2:
            # Order by third column.
            self.command = "SELECT * FROM tbl ORDER BY c"

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

        # Toggle reverse.
        if c == self.oldC:
            self.reverse = not self.reverse
        else:
            self.reverse = False

        # If reverse, append "DESC" to the select command.
        if self.reverse:
            self.command += " DESC"

        self.oldC = c
        self.FillLC()
        event.Skip()


    def FillDataBase(self):
        """
        ...
        """

        letters = "abcdefghijklmnopqrstuvwxyz"
        command = "INSERT INTO tbl VALUES(Null, ?, ?, ?)"

        for i in range(100):
            a = random.random()  # Add a random float from (0,26).
            b = random.random()  # Add a random float from (0,26).
            c = random.random()  # Add a random float from (0,26).
            # Add the data to the DataBase.
            self.con.execute(command, (a, b, c))

        # Use executemany.
        data = []

        for i in range(int(1e2)):
            a = random.random()   # Add a random float from (0,26).
            b = random.random()   # Add a random float from (0,26).
            c = random.random()   # Add a random float from (0,26).
            # Add the data to a list.
            data.append((a, b, c))

        # All data in the list is added to DataBase.
        self.con.executemany(command, data)
        # Commits the data, saving it.
        self.con.commit()
        # Update the list control.
        self.FillLC()


    def FillLC(self):
        """
        Fills the list control based on the sorting command.
        """

        # Since we're sorting, must delete all.
        self.listCtrl.DeleteAllItems()

        # Then get a list of tuples of all the data.
        data = self.con.execute(self.command).fetchall()

        index = 0
        for i in data:
            # Loop through and add it.
            self.listCtrl.Append(i[1:])

            if index % 2:
                self.listCtrl.SetItemBackgroundColour(index, "white")
            else:
                self.listCtrl.SetItemBackgroundColour(index, "#ccff9c")
            index += 1

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

class MyApp(wx.App):
    def OnInit(self):

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

        wx.InitAllImageHandlers()

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

        frame = MyFrame(None, -1, "")
        frame.SetSize(638, 350)
        self.SetTopWindow(frame)
        frame.Show(True)

        return True

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

def main():
    app = MyApp(False)
    app.MainLoop()

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

if __name__ == "__main__" :
    main()
