# sample_two.py

import sys
import os
import platform
import sqlite3
from   random import sample
from   string import ascii_letters
import wx
import wx.lib.mixins.listctrl as listmix

app_dir = os.path.split(os.path.abspath(sys.argv[0]))[0]
database_dir = os.path.join(app_dir, "data")
dbFile = os.path.join(database_dir, "Database.db3")     # dbFile

# def create_db
# class MyConnection
# class MyVirtualList
# class MyFrame
# class MyApp

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

def create_db():
    """
    Creer une base de donnees de test,
    et la remplir avec des donnees aleatoires.
    """

    #------------
        
    # Create/connect to a permanent file database.
    con = sqlite3.connect(dbFile, isolation_level=None,    # dbFile
                          detect_types=sqlite3.PARSE_DECLTYPES)

    # Establish the cursor, needed to execute the connected db.
    cur = con.cursor()

    #------------
    
    try:
        # Create/execute a table (ex : Database) :
        cur.execute("""CREATE TABLE Database (Ids INTEGER PRIMARY KEY,
                                              Name TEXT,
                                              Surname TEXT,
                                              Age TEXT,
                                              Email TEXT,
                                              Phone TEXT)""")
    except sqlite3.OperationalError:
        print ("Impossible de creer la base de donnees "
               "(peut-etre parce qu'elle existe deja ?)")

        return

    #------------
    
    # Important : if you make changes to the database commits
    # current data to the db file (data is persistant now).
    con.commit()

    sql = """INSERT INTO Database (Name,
                                   Surname,
                                   Age,
                                   Email,
                                   Phone)
                           VALUES (?, ?, ?, ?, ?)"""

    cur.execute("BEGIN")

    #------------
    
    for i in range(600000):
        cur.execute(sql, ["".join(sample(ascii_letters, 10))]*5)
    con.commit()

#-------------------------------------------------------------------------------
    
class MyConnection(object):
    """
    Maintient une connexion avec la base de donnees.
    """    
    def __init__(self):

        #------------
        
        # Return database folder.
        self.database_dir = wx.GetApp().GetDatabaseDir()

        # Loading "Database" file.  
        dbFile = os.path.join(self.database_dir, "Database.db3")
        
        #------------
        
        # Create/connect to a permanent file database.
        self.con = sqlite3.connect(dbFile, isolation_level=None,    # dbFile
                                   detect_types=sqlite3.PARSE_DECLTYPES)

        # Establish the cursor, needed to execute the connected db.       
        self.cur = self.con.cursor()

    #---------------------------------------------------------------------------
        
    def OnNumberRows(self):
        """
        Retourne le nombre de lignes dans la table.
        """

        self.cur.execute("SELECT count(Name) FROM Database")

        return self.cur.fetchall()[0][0]

    
    def OnOneRow(self, qui):
        """
        Retourne une ligne de la table.
        """

        sql = "SELECT * FROM Database WHERE Ids=?"
        self.cur.execute(sql, (qui,))

        return self.cur.fetchall()[0]

    
    def OnAll(self):
        """
        Renvoie toutes les donnees de la table en meme temps.
        """

        self.cur.execute("SELECT * from Database")

        while True:
            ligne = self.cur.fetchone()
            if not ligne:
                raise StopIteration
            yield ligne
        
#-------------------------------------------------------------------------------
        
class MyVirtualList(wx.ListCtrl, listmix.ListCtrlAutoWidthMixin):
    """
    Creer une ListCtrl virtuelle.
    """
    def __init__(self, parent):
        wx.ListCtrl.__init__(self, parent, -1,
                             style=wx.LC_REPORT|wx.LC_VIRTUAL|
                             wx.LC_HRULES|wx.LC_VRULES) 

        #------------
        
        # Reference de la base de donnees.        
        self.con = self.GetParent().con 

        #------------
        
        # Je dois dire combien de row la liste doit avoir.
        self.SetItemCount(self.con.OnNumberRows())

        #------------
        
        self.attr1 = wx.ItemAttr()
        self.attr1.SetBackgroundColour("#fbefff")  #fefea7

        #------------
        
        # mixins.
        listmix.ListCtrlAutoWidthMixin.__init__(self)
        
    #---------------------------------------------------------------------------
        
    def OnGetItemText(self, row, col):
        """
        Retour d'un article "c" de la ligne "r".
        C'est la magie de la virtualisation.
        """
        
        # Parce que le premier ID de la table et 1.
        data = self.con.OnOneRow(row+1) 

        return str(data[col])

    
    def OnGetItemAttr(self, row):
        """
        ???.
        """
        
        data = self.con.OnOneRow(row+1)

        index = data[0]
        if index % 2:
            return self.attr1

        else:
            return None
        
#-------------------------------------------------------------------------------
            
class MyFrame(wx.Frame): 
    """
    Creer une frame principale.
    """
    def __init__(self, *args, **kwds):
        wx.Frame.__init__(self, *args, **kwds)

        #------------
 
        # Return icons folder.
        self.icons_dir = wx.GetApp().GetIconsDir()      

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

        # Simplified init method.
        self.SetProperties()
        self.CreateCtrls()
        self.BindEvents()
        self.DoLayout()

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

        self.CenterOnScreen(wx.BOTH)
        
    #---------------------------------------------------------------------------       

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

        frameicon = wx.Icon(os.path.join(self.icons_dir,
                                         "wxwin.ico"),
                            type=wx.BITMAP_TYPE_ICO)

        self.SetIcon(frameicon)        
        self.SetTitle("Sample_two")
        self.SetSize((700, 500))
        self.SetTransparent(255)
        self.SetBackgroundColour("white")
        
        
    def CreateCtrls(self):
        """
        ???.    
        """

        self.panel = wx.Panel(self)

        #------------
        
        self.ConnectDb()

        #------------
        
        self.search = wx.TextCtrl(self.panel, -1, "")
        self.btn = wx.Button(self.panel, -1, "Search")

        #------------
        
        self.list = MyVirtualList(self.panel)

        for n, c in enumerate(("A", "B", "C", 
                               "D", "E", "F")): 
            self.list.InsertColumn(n, c)
            self.list.SetColumnWidth(n, 100)
       

    def ConnectDb(self):
        """
        ???. 
        """

        self.panel.con = MyConnection()

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

        self.btn.Bind(wx.EVT_BUTTON, self.OnSearch, self.btn)


    def DoLayout(self):
        """
        ???.
        """
  
        s1 = wx.BoxSizer(wx.VERTICAL)
        s2 = wx.BoxSizer(wx.HORIZONTAL)

        #------------
        
        s1.Add(self.list, 1, wx.ALL|wx.EXPAND, 5)
        s2.Add(self.search, 1, wx.ALL|wx.EXPAND, 5)
        s2.Add(self.btn, 1, wx.ALL|wx.EXPAND, 5)
        s1.Add(s2, 0, wx.ALL|wx.EXPAND, 5)

        #------------
        
        self.panel.SetSizer(s1)
        
        
    def OnSearch(self,event):
        """
        ???.
        """
        
        SearchText = self.search.GetValue()
        print ("Search text : "), SearchText

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

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

        #------------
        
        self.SetAppName("Sample_two")

        #------------
        
        self.installDir = os.path.split(os.path.abspath(sys.argv[0]))[0]

        #------------
        
        frame = MyFrame(None, -1, "")
        self.SetTopWindow(frame)
        frame.Show(True)
    
        return True

    #---------------------------------------------------------------------------
        
    def GetInstallDir(self):
        """
        Returns the installation directory for my application.
        """
        
        return self.installDir


    def GetDatabaseDir(self):
        """
        Returns the database directory for application.
        """
        
        database_dir = os.path.join(self.installDir, "data")        
        return database_dir


    def GetIconsDir(self):
        """
        Returns the icons directory for my application.
        """
        
        icons_dir = os.path.join(self.installDir, "icons")        
        return icons_dir
    
#-------------------------------------------------------------------------------
    
def main():
    app = MyApp(redirect=False)
    # Demarrage du gestionnaire d'evenements (boucle principale).
    app.MainLoop()

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

if __name__ == "__main__" :
    print ("Je vais creer la base de donnees .....")
    create_db()    
    main()
