from AppliDef import *


def ChercheProd(Cur, ListChamp, Nom):
    ListProds = []
    try:
        Cur.execute("SELECT " + ListChamp + " FROM PRODUITS WHERE NOMS = '" + Nom + "' order by NOMS")
        ListProds = Cur.fetchall()
    except:
        Cur.execute('SELECT ' + ListChamp + ' FROM PRODUITS WHERE NOMS = \"%' + Nom + '%\" order by NOMS')
        ListProds = Cur.fetchall()
    finally:
        if len(ListProds) == 0:
            Cur.execute('SELECT ' + ListChamp + ' FROM PRODUITS WHERE NOMS LIKE \"%' + Nom + '%\" order by NOMS')
            ListProds = Cur.fetchall()
    return ListProds


class FrmConsomProd(tk.Toplevel):
    def __init__(self, container, RatioX, RatioY, Type, Appli):
        tk.Toplevel.__init__(self, container)
        self.MainAppli = Appli
        self.parent = container
        self.RatioX = RatioX
        self.RatioY = RatioY
        self.Type = Type
        self.ListConsomProd = []
        self.TabProd = None
        self.ClasseListe = []
        self.StringClasse = None
        self.ListClasse = None
        self.FrmAjoutConsomProd = None
        self.LineSelect = 0
        self.MenuClicDroitTabConsomProd = []

        screen = ctypes.windll.user32
        FrmFond = Image.open('Media/FrmAjout/Fond/Fond.jpg')
        self.SX = int((float(FrmFond.size[0]) * float(self.RatioX)))
        self.SY = int((float(FrmFond.size[1]) * float(self.RatioY)))

        coefY = float(ConfigResolution(self.MainAppli.Nconfig, 'YTitreFrm', self.MainAppli.curSave))

        self.ImgFrmFond = []
        self.ImgFrmFond = RedimImg(self.SX, self.SY, 'Media/FrmAjout/Fond/')
        self.CanvasFrmFond = CanvasApp(self, width=self.SX, height=self.SY)
        self.ImgCanvasFrmFond = self.CanvasFrmFond.create_image(0, 0, anchor=tk.NW, image=self.ImgFrmFond[0])

        self.geometry(str(self.SX) + "x" + str(self.SY) + "+" + str(int(screen.GetSystemMetrics(0) * 0.002)) + "+" +
                      str(int(screen.GetSystemMetrics(1) * 0.031)))
        self.resizable(width=False, height=False)
        self.overrideredirect(True)
        self.lift()

        self.ImgBtnClose, wsize, hsize, self.CanvasBtnClose, self.ImgCanvasBtnClose = CanInWindow(
            'Media/FrmAjout/BtnClose/', self.RatioX, self.RatioY, self.CanvasFrmFond, tk.CENTER,
            int(self.SX * 0.988), int(self.SY * 0.014), 0)
        ChangeCursor(self.CanvasBtnClose)
        self.CanvasBtnClose.bind('<Motion>',
                                 lambda action: self.CanvasBtnClose.itemconfig(self.ImgCanvasBtnClose,
                                                                               image=self.ImgBtnClose[1]))
        self.CanvasBtnClose.bind('<ButtonRelease-1>', self.CacheFrmConsomProd)

        Titre = ''
        Table = ''
        if Type == "Consom":
            Titre = 'Récapitulatif des consommations annuelles de produits chimiques'
            Table = 'ConsomProd'
        elif Type == "Recyclage":
            Titre = 'Récapitulatif des produits chimiques à recycler'
            Table = 'ProdChimHS'
        self.LabelTag = LabelInCanvas(self.CanvasFrmFond, Titre, '#E0E0E0',
                                      AppliFont('Helvetica', self.MainAppli.SizeFont, 'normal', 0),
                                      int(self.SX * 0.005), int(self.SY * coefY), tk.NW)

        LabelInCanvas(self.CanvasFrmFond, 'Année scolaire: ', '#E0E0E0',
                      AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0), int(self.SX * 0.9),
                      int(self.SY * coefY), tk.NE)

        self.MainAppli.cur.execute("SELECT Annee FROM '" + Table + "' order by Produit")
        self.ListAnnee = self.MainAppli.cur.fetchall()

        self.AnScolListe = []
        for i in range(len(self.ListAnnee)):
            if self.ListAnnee[i][0] not in self.AnScolListe:
                self.AnScolListe.append(self.ListAnnee[i][0])
        if self.MainAppli.AnScol not in self.AnScolListe:
            self.AnScolListe.append(self.MainAppli.AnScol)
        self.AnScolListe.sort()
        self.AnScol = tk.StringVar()
        self.AnScol.set(self.MainAppli.AnScol)
        self.ListAnScol = ListeDeroulante(self.CanvasFrmFond, int(self.SX * 0.01), textvariable=self.AnScol,
                                          values=self.AnScolListe)
        self.CanvasFrmFond.create_window(int(self.SX * 0.9), int(self.SY * coefY), anchor=tk.NW,
                                         window=self.ListAnScol)

        self.CanvasMenu = CanvasMenu(self.CanvasFrmFond, 'Media/FrmAjout/Menu/', int(self.SX * 0.995),
                                     int(self.SY * 0.965), int(self.SX * 0.003), int(self.SY * 0.031), tk.NW)
        if Type == "Consom":
            self.ListAnScol.bind('<<ComboboxSelected>>', self.ActionAffichTabConsomProd)
            LabelInCanvas(self.CanvasFrmFond, 'Tri par: ', '#E0E0E0',
                          AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0), int(self.SX * 0.9),
                          int(self.SY * 0.035), tk.NE)

            self.TriListe = ['Produit & Classe', 'Produit', 'Classe']
            self.StringTri = tk.StringVar()
            self.StringTri.set(self.TriListe[0])
            self.ListTri = ListeDeroulante(self.CanvasFrmFond, int(self.SX * 0.01), textvariable=self.StringTri,
                                           values=self.TriListe)
            self.CanvasFrmFond.create_window(int(self.SX * 0.9), int(self.SY * 0.035), anchor=tk.NW,
                                             window=self.ListTri)
            self.ListTri.bind('<<ComboboxSelected>>', self.ActionAffichTabConsomProd)
            self.AffichTabConsomProd()
        elif Type == "Recyclage":
            self.ListAnScol.bind('<<ComboboxSelected>>', self.ActionAffichTabRecyclProd)
            self.AffichTabRecyclProd()

        self.BtnPlus = BtnAction(self.CanvasFrmFond, int(self.SX * 0.985), int(self.SY * 0.044), self.RatioX * 0.52,
                                 self.RatioX * 0.52, 'Media/FrmConsomProd/BtnPlus/')
        self.BtnPlus.CanvasBtn.bind('<ButtonRelease-1>', self.AjoutConsomProd)

        self.CanvasFrmFond.focus_set()
        self.CanvasFrmFond.bind('<Motion>', self.MoveOnCanvas)

        self.CanvasFrmFond.pack()

    def ActionAffichTabRecyclProd(self, _):
        self.AffichTabRecyclProd()

    def AffichTabRecyclProd(self):
        self.EffaceMenu()
        LabelInCanvas(self.CanvasMenu.CanvasMenu, 'Produits chimiques à Recycler - ' + self.AnScol.get(), '#E0E0E0',
                      AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0),
                      int(int(self.CanvasMenu.CanvasMenu['width']) * 0.01),
                      int(int(self.CanvasMenu.CanvasMenu['height']) * 0.004), tk.NW)
        self.MainAppli.cur.execute("SELECT " + "Produit, Quant, UnitMes" +
                                   " FROM ProdChimHS WHERE Annee = \"" + self.AnScol.get() + "\" order by Produit")
        self.ListConsomProd = self.MainAppli.cur.fetchall()

        self.TabProd = Tableau(self.CanvasMenu.CanvasMenu, len(self.ListConsomProd), 3,
                               ['Produit', 'Quantité', 'Unit. Mes.'],
                               [0.065, 0.02, 0.015],
                               AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0),
                               AppliFont('Helvetica', self.MainAppli.SizeFont, 'normal', 0), self)
        for i in range(len(self.ListConsomProd)):
            for j in range(3):
                self.TabProd.TabLabels[i][j]['text'] = self.ListConsomProd[i][j]

    def ActionAffichTabConsomProd(self, _):
        self.AffichTabConsomProd()

    def AffichTabConsomProd(self):
        self.EffaceMenu()
        LabelInCanvas(self.CanvasMenu.CanvasMenu, 'Consommation produits chimiques - ' + self.AnScol.get(), '#E0E0E0',
                      AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0),
                      int(int(self.CanvasMenu.CanvasMenu['width']) * 0.01),
                      int(int(self.CanvasMenu.CanvasMenu['height']) * 0.004), tk.NW)
        if self.StringTri.get() == 'Produit & Classe':
            self.MainAppli.cur.execute("SELECT " + "Produit, Classe , Quant, UnitMes" +
                                       " FROM ConsomProd WHERE Annee = \"" + self.AnScol.get() + "\" order by Produit")
            self.ListConsomProd = self.MainAppli.cur.fetchall()

            self.TabProd = Tableau(self.CanvasMenu.CanvasMenu, len(self.ListConsomProd), 4,
                                   ['Produit', 'Classe', 'Quantité', 'Unit. Mes.'],
                                   [0.065, 0.02, 0.02, 0.015],
                                   AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0),
                                   AppliFont('Helvetica', self.MainAppli.SizeFont, 'normal', 0), self)

            for i in range(len(self.ListConsomProd)):
                for j in range(4):
                    self.TabProd.TabLabels[i][j]['text'] = self.ListConsomProd[i][j]

            self.InitMenuClicDroit()

        elif self.StringTri.get() == 'Produit':
            self.MainAppli.cur.execute("SELECT " + "Produit, Quant, UnitMes" +
                                       " FROM ConsomProd WHERE Annee = \"" + self.AnScol.get() + "\" order by Produit")
            self.ListConsomProd = self.MainAppli.cur.fetchall()
            listprod = []
            Index = 0
            for i in range(len(self.ListConsomProd)):
                ProdPresent = False
                for j in range(len(listprod)):
                    if self.ListConsomProd[i][0] in listprod[j]:
                        ProdPresent = True
                        Index = j
                if not ProdPresent:
                    listprod.append([self.ListConsomProd[i][0], self.ListConsomProd[i][1], self.ListConsomProd[i][2]])
                else:
                    listprod[Index][1] = str(round(float(self.ListConsomProd[i][1].replace(",", "."))
                                                   + float(listprod[Index][1].replace(",", ".")), 1)).replace(".", ",")
            self.TabProd = Tableau(self.CanvasMenu.CanvasMenu, len(listprod), 4,
                                   ['Produit', 'Quantité consommée', 'Quantité en stock', 'Unit. Mes.'],
                                   [0.065, 0.015, 0.015, 0.015],
                                   AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0),
                                   AppliFont('Helvetica', self.MainAppli.SizeFont, 'normal', 0), self)
            self.ListConsomProd = []
            for i in range(len(listprod)):
                for j in range(2):
                    self.TabProd.TabLabels[i][j]['text'] = listprod[i][j]
                self.TabProd.TabLabels[i][3]['text'] = listprod[i][2]
                LProd = ChercheProd(self.MainAppli.cur, self.MainAppli.ListChampsTabProd, listprod[i][0])
                if len(LProd) > 0:
                    self.TabProd.TabLabels[i][2]['text'] = LProd[0][18]
                self.ListConsomProd.append(listprod[i])

            self.InitMenuClicDroit()
            self.UpdateQUtil(listprod)

        elif self.StringTri.get() == 'Classe':
            LabelInCanvas(self.CanvasMenu.CanvasMenu, '- Classe: ',
                          '#E0E0E0',
                          AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0),
                          int(int(self.CanvasMenu.CanvasMenu['width']) * 0.165),
                          int(int(self.CanvasMenu.CanvasMenu['height']) * 0.004), tk.NW)
            self.ClasseListe = ['PC2', 'PSI2', 'PCSI', 'PCSI (PC)', 'PCSI (PC-PSI)', 'TIPE', 'LABO']
            self.StringClasse = tk.StringVar()
            self.StringClasse.set(self.ClasseListe[0])
            self.ListClasse = ListeDeroulante(self.CanvasMenu.CanvasMenu,
                                              int(int(self.CanvasMenu.CanvasMenu['width']) * 0.01),
                                              textvariable=self.StringClasse,
                                              values=self.ClasseListe)
            self.CanvasMenu.CanvasMenu.create_window(int(int(self.CanvasMenu.CanvasMenu['width']) * 0.2),
                                                     int(int(self.CanvasMenu.CanvasMenu['height']) * 0.004),
                                                     anchor=tk.NW,
                                                     window=self.ListClasse)
            self.ListClasse.bind('<<ComboboxSelected>>', self.ActionTriConsomProdByClass)
            self.TriConsomProdByClass()

    def ActionTriConsomProdByClass(self, _):
        self.TriConsomProdByClass()

    def TriConsomProdByClass(self):
        self.MainAppli.cur.execute("SELECT " + "Produit, Quant, UnitMes" +
                                   " FROM ConsomProd WHERE Annee = \"" + self.AnScol.get() +
                                   "\" and Classe = \"" + self.StringClasse.get() + "\" order by Produit")
        self.ListConsomProd = self.MainAppli.cur.fetchall()
        self.TabProd = Tableau(self.CanvasMenu.CanvasMenu, len(self.ListConsomProd), 3,
                               ['Produit', 'Quantité', 'Unit. Mes.'],
                               [0.065, 0.02, 0.015],
                               AppliFont('Helvetica', self.MainAppli.SizeFont, 'bold', 0),
                               AppliFont('Helvetica', self.MainAppli.SizeFont, 'normal', 0), self)
        for i in range(len(self.ListConsomProd)):
            for j in range(3):
                self.TabProd.TabLabels[i][j]['text'] = self.ListConsomProd[i][j]

        self.InitMenuClicDroit()

    def InitMenuClicDroit(self):
        self.MenuClicDroitTabConsomProd = []
        for i in range(len(self.ListConsomProd)):
            self.MenuClicDroitTabConsomProd.append(tk.Menu(self.TabProd.TabLabels[i][0], tearoff=0))
            self.MenuClicDroitTabConsomProd[i].add_command(label='Commander le produit', command=self.CommandProdCD)

            def GestEvt(evt, l=i):
                return self.CommandProd(evt, l)

            self.TabProd.TabLabels[i][0].bind('<Double-1>', GestEvt)

            def GestEvt2(evt, L=i):
                return self.AffichMenuClicDroit(evt, self.MenuClicDroitTabConsomProd, L)

            self.TabProd.TabLabels[i][0].bind("<Button-3>", GestEvt2)

    def EffaceMenu(self):
        for widget in self.CanvasMenu.CanvasMenu.winfo_children():
            widget.destroy()

    def AjoutConsomProd(self, _):
        self.FrmAjoutConsomProd = FrmAjoutConsomProd(self.MainAppli.CanvasFrmMain, self.RatioX, self.RatioY,
                                                     self.Type, self)

    def UpdateQUtil(self, ListProd):
        for i in range(len(ListProd)):
            MAJ = False
            LProd = ChercheProd(self.MainAppli.cur, self.MainAppli.ListChampsTabProd, ListProd[i][0])
            if len(LProd) > 0:
                if LProd[0][19] is not None and LProd[0][19] != '':
                    if float(ListProd[i][1].replace(',', '.')) > float(LProd[0][19].replace(',', '.')):
                        MAJ = True
                else:
                    MAJ = True
                if MAJ:
                    Rep = tkMB.askyesno('Mise à jour des consommations annuelles de produits chimiques',
                                        'La quantité consommée annuelle de "' +
                                        ListProd[i][0] + '" est supérieure à celle enregistrée. '
                                                         'Voulez-vous mettre à jour les données?', parent=self)
                    if Rep:
                        self.MainAppli.cur.execute(
                            "UPDATE PRODUITS SET QUTIL ='" + ListProd[i][1] + "' WHERE NOMS='" + LProd[0][0] + "'")
                        self.MainAppli.DataBaseChimie.commit()
                        self.MainAppli.SaveListModifDTB('Chimie',
                                                        'Mise à jour - Consommation annuelle produit chimique - '
                                                        + LProd[0][0])

    def CommandProd(self, _, l):
        self.ActionCommandProd(l)

    def ActionCommandProd(self, N):
        self.destroy()
        self.MainAppli.NAction = 1
        self.MainAppli.ActionV(8)
        self.MainAppli.FrmCommandProd.ActionAjoutProdACom(0)
        self.MainAppli.FrmCommandProd.FrmAjoutProdACom.EntryProd.Data.set(self.ListConsomProd[N][0])
        self.MainAppli.FrmCommandProd.FrmAjoutProdACom.ActionModifProd()

    def CommandProdCD(self):
        self.ActionCommandProd(self.LineSelect)

    def AffichMenuClicDroit(self, event, MenuClicDroitTab, j):
        MenuClicDroitTab[j].post(event.x_root, event.y_root)
        self.LineSelect = j

    def MoveOnCanvas(self, _):
        self.CanvasBtnClose.itemconfig(self.ImgCanvasBtnClose, image=self.ImgBtnClose[0])
        self.MainAppli.CanvasBtnQuit.itemconfig(self.MainAppli.ImgCanvasBtnQuit, image=self.MainAppli.ImgBtnQuit[0])

    def CacheFrmConsomProd(self, _):
        self.CanvasBtnClose.itemconfig(self.ImgCanvasBtnClose, image=self.ImgBtnClose[0])
        self.MainAppli.ListBtnActionV[6].CanvasBtn.itemconfig(self.MainAppli.ListBtnActionV[6].ImgCanvasBtn,
                                                              image=self.MainAppli.ListBtnActionV[6].ImgBtn[2])
        self.destroy()
        self.MainAppli.parent.lift()
        self.MainAppli.Action1()


class FrmAjoutConsomProd(tk.Toplevel):
    def __init__(self, container, RatioX, RatioY, Type, Appli):
        tk.Toplevel.__init__(self, container)
        self.MainAppli = Appli
        self.parent = container
        self.RatioX = RatioX
        self.RatioY = RatioY
        self.Type = Type
        self.ListConsomProd = []
        self.TabProd = None
        self.ClasseListe = self.MainAppli.MainAppli.ListClasses
        self.TitreClasse = None
        self.Classe = tk.StringVar()
        self.ListeClasse = None
        self.EntryProd = None
        self.ListBoxProd = None
        self.ListProds = []
        self.TitreQuant = None
        self.EntryQuant = None
        self.UnitListe = ["g", "mL", "Unité"]
        self.Unit = tk.StringVar()
        self.ListeUnit = None
        self.WindowListeUnit = None
        self.BtnOK = None
        self.BtnAnnul = None

        screen = ctypes.windll.user32
        FrmFond = Image.open('Media/FrmAjout/Fond/Fond.jpg')
        self.SX = int((float(FrmFond.size[0]) * float(self.RatioX)))
        self.SY = int((float(FrmFond.size[1]) * float(self.RatioY)))

        coefY = float(ConfigResolution(self.MainAppli.MainAppli.Nconfig, 'YTitreFrm', self.MainAppli.MainAppli.curSave))

        self.ImgFrmFond = []
        self.ImgFrmFond = RedimImg(self.SX, self.SY, 'Media/FrmAjout/Fond/')
        self.CanvasFrmFond = CanvasApp(self, width=self.SX, height=self.SY)
        self.ImgCanvasFrmFond = self.CanvasFrmFond.create_image(0, 0, anchor=tk.NW, image=self.ImgFrmFond[0])

        self.geometry(str(self.SX) + "x" + str(self.SY) + "+" + str(int(screen.GetSystemMetrics(0) * 0.002)) + "+" +
                      str(int(screen.GetSystemMetrics(1) * 0.031)))
        self.resizable(width=False, height=False)
        self.overrideredirect(True)
        self.lift()

        self.ImgBtnClose, wsize, hsize, self.CanvasBtnClose, self.ImgCanvasBtnClose = CanInWindow(
            'Media/FrmAjout/BtnClose/', self.RatioX, self.RatioY, self.CanvasFrmFond, tk.CENTER,
            int(self.SX * 0.988), int(self.SY * 0.014), 0)
        ChangeCursor(self.CanvasBtnClose)
        self.CanvasBtnClose.bind('<Motion>',
                                 lambda action: self.CanvasBtnClose.itemconfig(self.ImgCanvasBtnClose,
                                                                               image=self.ImgBtnClose[1]))
        self.CanvasBtnClose.bind('<ButtonRelease-1>', self.CacheFrmAjoutConsomProd)
        Titre = ''
        if Type == "Consom":
            Titre = 'Ajouter des consommations de produits chimiques'
        elif Type == "Recyclage":
            Titre = 'Ajouter des produits chimiques à recycler'
        self.LabelTag = LabelInCanvas(self.CanvasFrmFond, Titre, '#E0E0E0',
                                      AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont, 'normal', 0),
                                      int(self.SX * 0.005), int(self.SY * coefY), tk.NW)

        self.CanvasMenu1 = CanvasMenu(self.CanvasFrmFond, 'Media/FrmAjout/Menu/', int(self.SX * 0.664),
                                      int(self.SY * 0.965), int(self.SX * 0.003), int(self.SY * 0.031), tk.NW)

        self.CanvasMenu2 = CanvasMenu(self.CanvasFrmFond, 'Media/FrmAjout/Menu/', int(self.SX * 0.331),
                                      int(self.SY * 0.965), int(self.SX * 0.998), int(self.SY * 0.031), tk.NE)

        self.AffichConsomProd()
        self.AffichSelectProd()

        self.CanvasFrmFond.focus_set()
        self.CanvasFrmFond.bind('<Motion>', self.MoveOnCanvas)

        self.CanvasFrmFond.pack()

    def AffichConsomProd(self):
        self.EffaceCanvasMenu1()
        Titre = ''
        Ncol = 0
        if self.Type == "Consom":
            Titre = 'Consommation produits chimiques - '
            self.MainAppli.MainAppli.cur.execute("SELECT " + "Produit, Classe , Quant, UnitMes" +
                                                 " FROM ConsomProd WHERE Annee = \"" +
                                                 self.MainAppli.MainAppli.AnScol + "\" order by Produit")
            Ncol = 4
        elif self.Type == "Recyclage":
            Titre = 'Produits chimiques à recycler - '
            self.MainAppli.MainAppli.cur.execute("SELECT " + "Produit, Quant, UnitMes" +
                                                 " FROM ProdChimHS WHERE Annee = \"" +
                                                 self.MainAppli.MainAppli.AnScol + "\" order by Produit")
            Ncol = 3

        LabelInCanvas(self.CanvasMenu1.CanvasMenu, Titre + self.MainAppli.MainAppli.AnScol, '#E0E0E0',
                      AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont, 'bold', 0),
                      int(int(self.CanvasMenu1.CanvasMenu['width']) * 0.01),
                      int(int(self.CanvasMenu1.CanvasMenu['height']) * 0.004), tk.NW)

        self.ListConsomProd = self.MainAppli.MainAppli.cur.fetchall()

        self.TabProd = Tableau(self.CanvasMenu1.CanvasMenu, len(self.ListConsomProd), Ncol,
                               ['Produit', 'Classe', 'Quantité', 'Unit. Mes.'],
                               [0.066, 0.02, 0.02, 0.013],
                               AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont, 'bold', 0),
                               AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont, 'normal', 0), self)
        for i in range(len(self.ListConsomProd)):
            for j in range(Ncol):
                self.TabProd.TabLabels[i][j]['text'] = self.ListConsomProd[i][j]

    def AffichSelectProd(self):
        LabelInCanvas(self.CanvasMenu2.CanvasMenu, "Sélection d'un produit chimique", '#E0E0E0',
                      AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont, 'bold', 0),
                      int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.01),
                      int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.004), tk.NW)

        if self.Type == 'Consom':
            self.TitreClasse = self.CanvasMenu2.CanvasMenu.create_text(
                int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.72),
                int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.06), text='Classe :',
                font=AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont + 1, 'bold', 0), anchor=tk.W)

            self.Classe.set(self.ClasseListe[0])
            self.ListeClasse = ListeDeroulante(self.CanvasMenu2.CanvasMenu,
                                               int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.02),
                                               textvariable=self.Classe, values=self.ClasseListe)
            self.CanvasMenu2.CanvasMenu.create_window(int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.82),
                                                      int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.06),
                                                      anchor=tk.W,
                                                      window=self.ListeClasse)
        self.EntryProd = EntryData(self.CanvasMenu2.CanvasMenu, int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.108),
                                   int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.5),
                                   int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.12), tk.CENTER,
                                   AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont + 2, 'bold', 0))

        self.EntryProd.EntryData.config(justify='center')
        self.EntryProd.EntryData.bind('<KeyRelease>', self.ModifProd)

        self.ListBoxProd = Scrollable_ListBox(self.CanvasMenu2.CanvasMenu,
                                              int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.01),
                                              int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.15),
                                              int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.158),
                                              int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.042))

        self.ListBoxProd.ListBox.bind('<<ListboxSelect>>', self.OnSelectListBox)

        self.TitreQuant = self.CanvasMenu2.CanvasMenu.create_text(
            int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.01),
            int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.88), text='Sélection de la quantité',
            font=AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont, 'bold', 1), anchor=tk.W)

        self.EntryQuant = EntryData(self.CanvasMenu2.CanvasMenu, int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.025),
                                    int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.05),
                                    int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.92), tk.W,
                                    AppliFont('Helvetica', self.MainAppli.MainAppli.SizeFont + 2, 'bold', 0))

        self.EntryQuant.EntryData.config(justify='center')
        self.EntryQuant.EntryData.bind('<KeyRelease>', self.ModifQuant)

        self.Unit.set(self.UnitListe[0])
        self.ListeUnit = ListeDeroulante(self.CanvasMenu2.CanvasMenu,
                                         int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.015),
                                         textvariable=self.Unit, values=self.UnitListe)
        self.WindowListeUnit = self.CanvasMenu2.CanvasMenu.create_window(
            int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.3),
            int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.92), anchor=tk.W, window=self.ListeUnit)

        self.BtnOK = BtnAction(self.CanvasMenu2.CanvasMenu, int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.88),
                               int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.95), self.RatioX * 0.75,
                               self.RatioX * 0.75, 'Media/FrmAjout/BtnOK2/')
        self.BtnOK.CanvasBtn.bind('<ButtonRelease-1>', self.ValidAjoutProd)
        self.BtnAnnul = BtnAction(self.CanvasMenu2.CanvasMenu, int(int(self.CanvasMenu2.CanvasMenu['width']) * 0.95),
                                  int(int(self.CanvasMenu2.CanvasMenu['height']) * 0.95), self.RatioX * 0.75,
                                  self.RatioX * 0.75, 'Media/FrmAjout/BtnAnnul2/')
        self.BtnAnnul.CanvasBtn.bind('<ButtonRelease-1>', self.AnnulAjoutProd)

        self.InitSelectProd()

    def InitSelectProd(self):
        if self.Type == "Consom":
            self.Classe.set(self.ClasseListe[0])
        self.EntryProd.Data.set('')
        self.EntryProd.EntryData.focus()
        self.EntryQuant.Data.set('')
        self.Unit.set(self.UnitListe[0])
        self.AffichListprod()

    def AffichListprod(self):
        self.MainAppli.MainAppli.cur.execute("select " + self.MainAppli.MainAppli.ListChampsTabProd +
                                             " from PRODUITS order by NOMS")
        self.ListProds = self.MainAppli.MainAppli.cur.fetchall()

        self.ListBoxProd.ListBox.delete(0, tk.END)
        for i in range(len(self.ListProds)):
            self.ListBoxProd.ListBox.insert(tk.END, self.ListProds[i][0])
        self.ListBoxProd.ListBox.selection_set(0)
        if len(self.ListProds) > 0:
            self.EntryProd.Data.set(self.ListProds[0][0])
            self.Unit.set(self.ListProds[0][1])

    def ModifProd(self, _):
        self.MainAppli.MainAppli.cur.execute('SELECT ' + self.MainAppli.MainAppli.ListChampsTabProd +
                                             ' FROM PRODUITS WHERE NOMS LIKE \'%' +
                                             self.EntryProd.Data.get() + '%\' order by NOMS')
        self.ListProds = self.MainAppli.MainAppli.cur.fetchall()
        self.ListBoxProd.ListBox.delete(0, tk.END)
        for i in range(len(self.ListProds)):
            self.ListBoxProd.ListBox.insert(tk.END, self.ListProds[i][0])
        self.ListBoxProd.ListBox.selection_set(0)

    def ModifQuant(self, _):
        try:
            float(self.EntryQuant.Data.get().replace(',','.'))
        except:
            self.EntryQuant.Data.set('')

    def OnSelectListBox(self, evt):
        try:
            w = evt.widget
            IndexElt = int(w.curselection()[0])
            self.EntryProd.Data.set(self.ListProds[IndexElt][0])
            self.Unit.set(self.ListProds[IndexElt][1])
        except:
            pass

    def ValidAjoutProd(self, _):
        self.BtnOK.CanvasBtn.itemconfig(self.BtnOK.ImgCanvasBtn, image=self.BtnOK.ImgBtn[0])
        if self.EntryProd.EntryData.get() != '' and self.EntryQuant.Data.get() != '':
            if self.Type == "Consom":
                self.MajConsomProd(self.EntryProd.EntryData.get(), self.Classe.get(), self.Unit.get(),
                                   float(self.EntryQuant.Data.get().replace(',', '.')))
            elif self.Type == "Recyclage":
                self.MajRecyclProd(self.EntryProd.EntryData.get(), self.Unit.get(),
                                   float(self.EntryQuant.Data.get().replace(',', '.')))
            self.AffichConsomProd()
            self.InitSelectProd()
        else:
            tkMB.showwarning('Attention', "Veuillez choisir un produit chimique et indiquer la quantité !", parent=self)
            self.lift()

    def MajRecyclProd(self, Prod, UnitMes, Quant):
        self.MainAppli.MainAppli.cur.execute("SELECT Quant FROM ProdChimHS WHERE Annee ='"
                                             + self.MainAppli.MainAppli.AnScol
                                             + "' and produit = '" + Prod + "'")
        ListConsomProd = self.MainAppli.MainAppli.cur.fetchall()
        if len(ListConsomProd) > 0:
            QuantConsom = str(round(Quant + float(ListConsomProd[0][0].replace(',', '.')), 1)).replace('.', ',')
            self.MainAppli.MainAppli.cur.execute("UPDATE ProdChimHS SET Quant ='" + QuantConsom + "' WHERE Produit='"
                                                 + Prod + "' and Annee ='" + self.MainAppli.MainAppli.AnScol + "'")
        else:
            QuantConsom = str(round(Quant, 1)).replace('.', ',')
            data = [(self.MainAppli.MainAppli.AnScol, Prod, UnitMes, QuantConsom)]
            self.MainAppli.MainAppli.cur.execute(
                "INSERT INTO ProdChimHS (Annee, Produit, UnitMes, Quant) VALUES(?,?,?,?)", data[0])
        self.MainAppli.MainAppli.DataBaseChimie.commit()
        self.MainAppli.MainAppli.SaveListModifDTB('Chimie', 'Mise à jour - Produit chimique recyclé - ' + Prod)
        ListProds = self.ChercheProd(Prod)
        QuantStock = str(round(float(ListProds[0][18].replace(',', '.')) - Quant, 1)).replace('.', ',')
        self.MainAppli.MainAppli.cur.execute(
            "UPDATE PRODUITS SET QSTOCK ='" + QuantStock + "' WHERE NOMS='" + ListProds[0][0] + "'")
        self.MainAppli.MainAppli.DataBaseChimie.commit()
        self.MainAppli.MainAppli.SaveListModifDTB('Chimie',
                                                  'Mise à jour - Stock produit chimique après recyclage - ' +
                                                  ListProds[0][0])

    def MajConsomProd(self, Prod, Classe, UnitMes, Quant):
        self.MainAppli.MainAppli.cur.execute("SELECT Quant FROM ConsomProd WHERE Annee ='"
                                             + self.MainAppli.MainAppli.AnScol
                                             + "' and produit = '" + Prod + "' and Classe ='"
                                             + Classe + "'")
        ListConsomProd = self.MainAppli.MainAppli.cur.fetchall()
        if len(ListConsomProd) > 0:
            QuantConsom = str(round(Quant + float(ListConsomProd[0][0].replace(',', '.')), 1)).replace('.', ',')
            self.MainAppli.MainAppli.cur.execute("UPDATE ConsomProd SET Quant ='" + QuantConsom + "' WHERE Produit='"
                                                 + Prod + "' and Annee ='" + self.MainAppli.MainAppli.AnScol
                                                 + "'and Classe ='" + Classe + "'")
        else:
            QuantConsom = str(round(Quant, 1)).replace('.', ',')
            data = [(self.MainAppli.MainAppli.AnScol, Prod, Classe, UnitMes, QuantConsom)]
            self.MainAppli.MainAppli.cur.execute(
                "INSERT INTO ConsomProd(Annee, Produit, Classe , UnitMes, Quant) VALUES(?,?,?,?,?)", data[0])
        self.MainAppli.MainAppli.DataBaseChimie.commit()
        self.MainAppli.MainAppli.SaveListModifDTB('Chimie', 'Mise à jour - Consommation produit chimique - ' + Prod)
        ListProds = self.ChercheProd(Prod)
        QuantStock = str(round(float(ListProds[0][18].replace(',', '.')) - Quant, 1)).replace('.', ',')
        self.MainAppli.MainAppli.cur.execute(
            "UPDATE PRODUITS SET QSTOCK ='" + QuantStock + "' WHERE NOMS='" + ListProds[0][0] + "'")
        self.MainAppli.MainAppli.DataBaseChimie.commit()
        self.MainAppli.MainAppli.SaveListModifDTB('Chimie', 'Mise à jour - Stock produit chimique après consommation - '
                                                  + ListProds[0][0])

    def ChercheProd(self, Nom):
        ListProds = []
        try:
            self.MainAppli.MainAppli.cur.execute("SELECT " + self.MainAppli.MainAppli.ListChampsTabProd +
                                                 " FROM PRODUITS WHERE NOMS = '" + Nom + "' order by NOMS")
            ListProds = self.MainAppli.MainAppli.cur.fetchall()
        except:
            self.MainAppli.MainAppli.cur.execute('SELECT ' + self.MainAppli.MainAppli.ListChampsTabProd +
                                                 ' FROM PRODUITS WHERE NOMS LIKE \'%' + Nom + '%\' order by NOMS')
            ListProds = self.MainAppli.MainAppli.cur.fetchall()
        finally:
            if len(ListProds) == 0:
                self.MainAppli.MainAppli.cur.execute('SELECT ' + self.MainAppli.MainAppli.ListChampsTabProd +
                                                     ' FROM PRODUITS WHERE NOMS LIKE \'%' + Nom + '%\' order by NOMS')
                ListProds = self.MainAppli.MainAppli.cur.fetchall()
        return ListProds

    def EffaceCanvasMenu1(self):
        for widget in self.CanvasMenu1.CanvasMenu.winfo_children():
            widget.destroy()

    def AnnulAjoutProd(self, _):
        self.CacheFrmAjoutConsomProd(_)

    def CacheFrmAjoutConsomProd(self, _):
        if self.Type == "Recyclage":
            self.MainAppli.AffichTabRecyclProd()
        elif self.Type == "Consom":
            self.MainAppli.AffichTabConsomProd()
        self.CanvasBtnClose.itemconfig(self.ImgCanvasBtnClose, image=self.ImgBtnClose[0])
        self.destroy()
        self.MainAppli.FrmAjoutConsomProd = None
        self.MainAppli.MainAppli.FrmLift()
        self.MainAppli.lift()

    def MoveOnCanvas(self, _):
        self.CanvasBtnClose.itemconfig(self.ImgCanvasBtnClose, image=self.ImgBtnClose[0])
        self.MainAppli.MainAppli.CanvasBtnQuit.itemconfig(self.MainAppli.MainAppli.ImgCanvasBtnQuit,
                                                          image=self.MainAppli.MainAppli.ImgBtnQuit[0])
