def refreshTable(self, label, table, query): display.refreshLabel(label, "") try: cursor = self.data.cursor() result = cursor.execute(query) except Exception as e: table.setRowCount(0) display.refreshLabel(label, "Impossible d'afficher les données de la table : " + repr(e)) else: display.refreshGenericData(table, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_1, "") try: cursor = self.data.cursor() result = cursor.execute("SELECT noSpec, dateRep, noPlace, noRang, dateEmTick, noDos FROM LesTickets") except Exception as e: self.ui.table_fct_comp_1.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_1, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_1, result)
def refreshResult(self): #display.refreshLabel(self.ui.label_fct_comp_representation, "") try: cursor = self.data.cursor() result = cursor.execute("SELECT noSpec,nomSpec,dateRep,count(*) FROM LesSpectacles natural join LesRepresentations_base natural join LesTickets GROUP BY noSpec, nomSpec,dateRep,noRang,noPlace") except Exception as e: self.ui.tableresultat.setRowCount(0) display.refreshLabel(self.ui.label_res, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.tableresultat, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_6, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT noSpec, dateRep, COUNT(*) AS nbrPlacesR FROM LesTickets GROUP BY noSpec, dateRep") except Exception as e: self.ui.table_fct_comp_6.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_6, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_6, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_1, "") try: cursor = self.data.cursor() # TODO 1.1 : mettre à jour la requête et changer aussi le fichier ui correspondant result = cursor.execute("SELECT noSpec, dateRep, noPlace, noRang, dateEmTick, noDos FROM LesTickets") except Exception as e: self.ui.table_fct_comp_1.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_1, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_1, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_fournie_1, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT noSpec, dateRep FROM LesRepresentations WHERE nbPlacesDispoRep = (SELECT count(noPlace) FROM LesPlaces)" ) except Exception as e: self.ui.table_fct_fournie_1.setRowCount(0) display.refreshLabel( self.ui.label_fct_fournie_1, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_fournie_1, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_13, "") try: cursor = self.data.cursor() result = cursor.execute("SELECT numEq, num,pays FROM LesEquipes") except Exception as e: self.ui.table_fct_comp_13.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_13, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_13, result)
def refreshPlacesRes(self): display.refreshLabel(self.ui.label_fct_int_2, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT nomSpec,dateRep, nbPtot - nbPlacesRestantes FROM (SELECT noSpec, nomSpec, dateRep, nbPlacesRestantes, (SELECT COUNT(noPlace) FROM LesPlaces) nbPtot FROM LesRepresentations NATURAL JOIN LesSpectacles)" ) except Exception as e: self.ui.table_fct_int_2.setRowCount(0) display.refreshLabel( self.ui.label_fct_int_2, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_int_2, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_7, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT nomSpec, dateRep, promoRep, nbPlacesDispoRep " "FROM LesRepresentations " "NATURAL JOIN LesSpectacles" ) except Exception as e: self.ui.table_fct_comp_7.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_7, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_7, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_fournie_1, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT nomSpec, dateRep FROM LesSpectacles NATURAL JOIN LesRepresentations_base WHERE noSpec = 1" ) except Exception as e: self.ui.table_fct_fournie_1.setRowCount(0) display.refreshLabel( self.ui.label_fct_fournie_1, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_fournie_1, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_part2_1, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT numEq, AVG(ageSp) FROM LesEquipiers JOIN LesSportifs USING (numSp) GROUP BY numEq HAVING numEq IN (SELECT gold FROM LesResultats)" ) except Exception as e: self.ui.table_fct_part2_1.setRowCount(0) display.refreshLabel( self.ui.label_fct_part2_1, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_part2_1, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_fournie_1, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT nomSp, prenomSp, pays, categorieSp FROM LesSportifs_base JOIN LesEquipiers USING (numSp) WHERE numEq = 1" ) except Exception as e: self.ui.table_fct_fournie_1.setRowCount(0) display.refreshLabel( self.ui.label_fct_fournie_1, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_fournie_1, result)
def refreshRepVides(self): display.refreshLabel(self.ui.label_fct_int_1, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT noSpec, dateRep FROM LesRepresentations_base EXCEPT SELECT DISTINCT noSpec, dateRep FROM LesTickets" ) except Exception as e: self.ui.table_fct_int_1.setRowCount(0) display.refreshLabel( self.ui.label_fct_int_1, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_int_1, result)
def refreshResult(self): # TODO 1.7 : fonction à modifier pour que l'équipe ne propose que des valeurs possibles pour le pays choisi display.refreshLabel(self.ui.label_fct_comp_4, "") if self.ui.comboBox_fct_4_equipe.count() != 0: try: cursor = self.data.cursor() result = cursor.execute( "SELECT numSp, nomSp, prenomSp, categorieSp, dateNaisSp FROM LesSportifs_base JOIN LesEquipiers USING (numSp) WHERE pays = ? AND numEq=?", [ self.ui.comboBox_fct_4_pays.currentText(), self.ui.comboBox_fct_4_equipe.currentText() ]) except Exception as e: self.ui.table_fct_comp_4.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_4, "Impossible d'afficher les résultats : " + repr(e)) else: i = display.refreshGenericData(self.ui.table_fct_comp_4, result) if i == 0: display.refreshLabel(self.ui.label_fct_comp_4, "Aucun résultat") else: self.ui.table_fct_comp_4.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_4, "Aucun résultat")
def refreshResult(self): # TODO 1.5 : fonction à modifier pour remplacer la zone de saisie par une liste de valeurs prédéfinies dans l'interface une fois le fichier ui correspondant mis à jour display.refreshLabel(self.ui.label_fct_comp_2, "") button_binding = None if self.ui.radioButton.isChecked(): button_binding = self.ui.radioButton.text() if self.ui.radioButton_2.isChecked(): button_binding = self.ui.radioButton_2.text() if self.ui.radioButton_3.isChecked(): button_binding = self.ui.radioButton_3.text() if button_binding is None: self.ui.table_fct_comp_2.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_2, "Veuillez sélectionner un nom de catégorie") else: try: cursor = self.data.cursor() result = cursor.execute( "SELECT noPlace, noRang, noZone FROM LesZones NATURAL JOIN LesPlaces WHERE catZone = ?", [button_binding]) except Exception as e: self.ui.table_fct_comp_2.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_2, "Impossible d'afficher les résultats : " + repr(e)) else: i = display.refreshGenericData(self.ui.table_fct_comp_2, result) if i == 0: display.refreshLabel(self.ui.label_fct_comp_2, "Aucun résultat")
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_1, "") if not self.ui.lineEdit.text().strip(): self.ui.table_fct_comp_1.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_1, "Veuillez indiquer un numéro d'équipe") else: try: cursor = self.data.cursor() # TODO 1.1 : mettre à jour la requête pour ajouter dateNaisSp et changer aussi le fichier ui correspondant result = cursor.execute( "SELECT nomSp, prenomSp, pays, categorieSp,dateNaisSp FROM LesSportifs_base JOIN LesEquipiers USING (numSp) WHERE numEq = ?", [self.ui.lineEdit.text().strip()]) except Exception as e: self.ui.table_fct_comp_1.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_1, "Impossible d'afficher les résultats : " + repr(e)) else: i = display.refreshGenericData(self.ui.table_fct_comp_1, result) if i == 0: display.refreshLabel(self.ui.label_fct_comp_1, "Aucun résultat")
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_3, "") if not self.ui.cb_fct_comp_3.currentText(): self.ui.table_fct_comp_3.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_3, "Veuillez indiquer un nom de catégorie") else: try: cursor = self.data.cursor() result = cursor.execute( "SELECT numEp, nomEp, formeEp, nomDi, nbSportifsEp, dateEp FROM LesEpreuves WHERE categorieEp = ?", [self.ui.cb_fct_comp_3.currentText()]) except Exception as e: self.ui.table_fct_comp_3.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_3, "Impossible d'afficher les résultats : " + repr(e)) else: i = display.refreshGenericData(self.ui.table_fct_comp_3, result) if i == 0: display.refreshLabel(self.ui.label_fct_comp_3, "Aucun résultat")
def refreshSupp(self): display.refreshLabel(self.ui.label_5, "") # on récupère le numéro du spectacle concerné if not self.ui.comboBox_2.currentText(): self.ui.tableWidget.setRowCount(0) display.refreshLabel(self.ui.label_5, "Veuillez sélectionner un spectacle") else: cursor = self.data.cursor() cursor.execute( "SELECT noSpec FROM LesSpectacles WHERE nomSpec = ?", [self.ui.comboBox_2.currentText()]) numSpec = cursor.fetchone() binding = numSpec[0] try: cursor = self.data.cursor() result = cursor.execute( "SELECT dateRep, promoRep FROM LesRepresentations_base WHERE noSpec = ?", [binding]) except Exception as e: self.ui.tableWidget.setRowCount(0) display.refreshLabel( self.ui.label_5, "Impossible d'afficher les représentations : " + repr(e)) else: i = display.refreshGenericData(self.ui.tableWidget, result) display.refreshLabel( self.ui.label_5, "Cliquez sur la représentation à supprimer") if i == 0: display.refreshLabel( self.ui.label_5, "Il n'y a pas de représentations pour ce spectacle")
def refreshDossierSupp(self): display.refreshLabel(self.ui.label_4, "") if not self.ui.lineEdit_2.text().strip(): self.ui.tableWidget_2.setRowCount(0) display.refreshLabel(self.ui.label_4, "Veuillez indiquer un numéro de dossier") else: try: cursor = self.data.cursor() result = cursor.execute( "SELECT nomSpec,dateRep,noRang,noPlace,libelleCat FROM LesTickets NATURAL JOIN LesSpectacles WHERE noDos = ?", [int(self.ui.lineEdit_2.text().strip())]) except Exception as e: self.ui.tableWidget_2.setRowCount(0) display.refreshLabel( self.ui.label_4, "Impossible d'afficher ce dossier : " + repr(e)) else: i = display.refreshGenericData(self.ui.tableWidget_2, result) display.refreshLabel(self.ui.label_4, "Cliquez sur le ticket à supprimer") if i == 0: display.refreshLabel( self.ui.label_4, "Ce dossier n'existe pas, veuillez saisir un numéro de dossier valide" )
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_12, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT DISTINCT D.nomDi, E.nbSportifsEp, E.categorieEp, E.formeEp FROM LesDisciplines D LEFT OUTER JOIN LesEpreuves E ON D.nomDi=E.nomDi" ) except Exception as e: self.ui.table_fct_comp_12.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_12, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_12, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_11, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT numSP,nomSp, prenomSp, pays, categorieSp,dateNaisSp,ageSp FROM LesSportifs" ) except Exception as e: self.ui.table_fct_comp_11.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_11, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_11, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_part2_1, "") try: cursor = self.data.cursor() result = cursor.execute("SELECT pays, SUM(nbOr) as nbOr, SUM(nbArgent) as nbArgent, SUM(nbBronze) as nbBronze FROM " "(SELECT P.pays, COUNT(G.gold) as nbOr, 0 as nbArgent, 0 as nbBronze " "FROM (SELECT DISTINCT pays FROM LesSportifs) P " "LEFT JOIN (SELECT S.pays as gold FROM LesResultats R JOIN LesSportifs S ON R.gold=S.numSp) G ON P.pays=G.gold " "GROUP BY P.pays " "UNION ALL " "SELECT P.pays, 0 as nbOr, COUNT(S.silver) as ngArgent, 0 as nbBronze " "FROM (SELECT DISTINCT pays FROM LesSportifs) P " "LEFT JOIN (SELECT S.pays as silver FROM LesResultats R JOIN LesSportifs S ON R.silver=S.numSp) S ON P.pays=S.silver " "GROUP BY P.pays " "UNION ALL " "SELECT P.pays, 0 as nbOr, 0 as ngArgent, COUNT(B.bronze) as nbBronze " "FROM (SELECT DISTINCT pays FROM LesSportifs) P " "LEFT JOIN (SELECT S.pays as bronze FROM LesResultats R JOIN LesSportifs S ON R.bronze=S.numSp) B ON P.pays=B.bronze " "GROUP BY P.pays " "UNION ALL " "SELECT P.pays, COUNT(G.gold) as nbOr, 0 as nbArgent, 0 as nbBronze " "FROM (SELECT DISTINCT pays FROM LesSportifs) P " "LEFT JOIN (SELECT E.pays as gold FROM LesResultats R JOIN (SELECT DISTINCT numEq, pays FROM LesEquipiers JOIN LesSportifs USING(numSp)) E ON R.gold=E.numEq) G ON P.pays=G.gold " "GROUP BY P.pays " "UNION ALL " "SELECT P.pays, 0 as nbOr, COUNT(S.silver) as nbArgent, 0 as nbBronze " "FROM (SELECT DISTINCT pays FROM LesSportifs) P " "LEFT JOIN (SELECT E.pays as silver FROM LesResultats R JOIN (SELECT DISTINCT numEq, pays FROM LesEquipiers JOIN LesSportifs USING(numSp)) E ON R.silver=E.numEq) S ON P.pays=S.silver " "GROUP BY P.pays " "UNION ALL " "SELECT P.pays, 0 as nbOr, 0 as nbArgent, COUNT(B.bronze) as nbBronze " "FROM (SELECT DISTINCT pays FROM LesSportifs) P " "LEFT JOIN (SELECT E.pays as bronze FROM LesResultats R JOIN (SELECT DISTINCT numEq, pays FROM LesEquipiers JOIN LesSportifs USING(numSp)) E ON R.bronze=E.numEq) B ON P.pays=B.bronze " "GROUP BY P.pays) " "GROUP BY pays ORDER BY nbOr DESC, nbArgent DESC, nbBronze DESC") except Exception as e: self.ui.table_fct_part2_1.setRowCount(0) display.refreshLabel(self.ui.label_fct_part2_1, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_part2_1, result)
def refreshResult(self): sort = ["nomSpec, dateRep", "noDos"] display.refreshLabel(self.ui.label_error_fct_comp_8, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT noDos, nomSpec, dateRep, noPlace, noRang, libelleCat, dateEmTick, prixTicket " "FROM LesTickets_extension " "NATURAL JOIN LesSpectacles " "ORDER BY " + sort[1 if self.ui.radioButton_tri_dos_fct_comp_8. isChecked() else 0]) except Exception as e: self.ui.table_fct_comp_8.setRowCount(0) display.refreshLabel( self.ui.label_error_fct_comp_8, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_8, result)
def refreshResult(self): try: cursor = self.data.cursor() result = cursor.execute("SELECT * FROM LesTickets") except Exception as e: self.ui.table_fct_comp.setRowCount(0) else: i = display.refreshGenericData(self.ui.table_fct_comp, result) if i == 0: display.refreshLabel(self.ui.label_11, "Aucun résultat")
def refreshResult(self): display.refreshLabel(self.ui.label_error_fct_comp_6, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT dateRep, case noPlace when null then 0 else count(noPlace) end as nbPlacesReserv " "FROM LesRepresentations " "NATURAL JOIN LesSpectacles " "NATURAL LEFT JOIN LesTickets " "WHERE nomSpec = ? " "GROUP BY dateRep", [self.ui.comboBox_fct_comp_6.currentText()]) except Exception as e: self.ui.table_fct_comp_6.setRowCount(0) display.refreshLabel( self.ui.label_error_fct_comp_6, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_6, result)
def refreshResult(self): try: cursor = self.data.cursor() result = cursor.execute("WITH list_pays(id,pays) as (SELECT DISTINCT numSp as id, pays FROM LesSportifs UNION SELECT DISTINCT numEq, pays FROM LesEquipiers JOIN LesSportifs USING (numSp)),res_or(pays,nbOr) AS (SELECT pays, count(gold) as nbOr FROM list_pays LEFT JOIN LesResultats ON (id=gold) GROUP BY pays), res_silver(pays,nbArgent) as (SELECT pays, count(silver) as nbArgent FROM list_pays LEFT JOIN LesResultats ON (id=silver) GROUP BY pays),res_bronze(pays,nbBronze) as (SELECT pays, count(bronze) as nbBronze FROM list_pays LEFT JOIN LesResultats ON (id=bronze) GROUP BY pays) SELECT pays,nbOr,nbArgent,nbBronze FROM res_or JOIN res_silver USING (pays) JOIN res_bronze USING (pays) ORDER BY nbOr DESC, nbArgent DESC, nbBronze DESC") except Exception as e: self.ui.table_fct_part_2_2.setRowCount(0) display.refreshLabel(self.ui.label_fct_part_2_2, "Impossible d'afficher les résultats : " + repr(e)) else: i = display.refreshGenericData(self.ui.table_fct_part_2_2, result) if i == 0: display.refreshLabel(self.ui.label_fct_part_2_2, "Aucun résultat")
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_22, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT DISTINCT A.Pays,G.gold,S.silver,B.bronze " "FROM AllPays A LEFT OUTER JOIN GoldEtPay G ON (A.pays=G.pays)" "LEFT OUTER JOIN SilverEtPay S ON (A.pays=S.pays)" "LEFT OUTER JOIN BronzeEtPay B ON (A.pays=B.pays) " "GROUP BY A.Pays") except Exception as e: self.ui.table_fct_comp_22.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_22, "Impossible d'afficher les résultats : " + repr(e)) else: display.refreshGenericData(self.ui.table_fct_comp_22, result)
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_4, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT * FROM LesTickets NATURAL JOIN LesPlaces NATURAL JOIN LesZones WHERE noDos = ? AND catZone=?", [self.ui.listWidget_4_dossier.currentItem().text(), self.ui.comboBox_4_categorie.currentText()] ) except Exception as e: self.ui.table_fct_comp_4.setRowCount(0) display.refreshLabel(self.ui.label_fct_comp_4, "Impossible d'afficher les résultats : " + repr(e)) else: i = display.refreshGenericData(self.ui.table_fct_comp_4, result) if i == 0: display.refreshLabel(self.ui.label_fct_comp_4, "Aucun résultat")
def refreshResult(self): try: cursor = self.data.cursor() result = cursor.execute( "SELECT noSpec,dateRep, promoRep, nbrPlacesDisp FROM LesRepresentations" ) except Exception as e: self.ui.table_fct_comp_7.setRowCount(0) display.refreshLabel( self.ui.message_fct_comp_7, "Impossible d'afficher les résultats : " + repr(e)) else: i = display.refreshGenericData(self.ui.table_fct_comp_7, result) if i == 0: display.refreshLabel(self.ui.message_fct_comp_7, "Aucun résultat")
def refreshResult(self): display.refreshLabel(self.ui.label_fct_comp_3, "") try: cursor = self.data.cursor() result = cursor.execute( "SELECT noPlace, noRang, noZone FROM LesZones NATURAL JOIN LesPlaces WHERE catZone = ?", [self.ui.comboBox_fct_comp_3.currentText()]) except Exception as e: self.ui.table_fct_comp_3.setRowCount(0) display.refreshLabel( self.ui.label_fct_comp_3, "Impossible d'afficher les résultats : " + repr(e)) else: i = display.refreshGenericData(self.ui.table_fct_comp_3, result) if i == 0: display.refreshLabel(self.ui.label_fct_comp_3, "Aucun résultat")