1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
| from tkinter import *
import tkinter as tk
import tkinter.ttk
from tkinter import ttk
import os
import re
import psycopg2
import sys
import pprint
from subprocess import Popen
from PIL import ImageTk
from tkinter import font
def ListeChoix():
#Define our connection string
conn_string = "host='localhost' dbname='ddb' user='postgres' password='secret' port=5678"
# print the connection string we will use to connect
print ("Connecting to database")
# get a connection, if a connect cannot be made an exception will be raised here
conn = psycopg2.connect(conn_string)
# conn.cursor will return a cursor object, you can use this cursor to perform queries
cursor = conn.cursor()
# executer la requete pour obtenir la liste des statuts
cursor.execute("SELECT statut FROM coordination.opportunite group by statut")
# Récupérer les résultats depuis la bdd
records_statut = cursor.fetchall()
# Enlever les accolades
records_statut=[x for xs in records_statut for x in xs]
# executer la requete pour obtenir la liste des opportunités
#cursor.execute("SELECT id_opp FROM coordination.opportunite group by id_opp")
# Récupérer les résultats depuis la bdd
#records_id_opp = cursor.fetchall()
# enelever les accolades
#records_id_opp=[x for xs in records_id_opp for x in xs]
def updateSQLView(event):
# executer la requete pour copier les csv depuis le serveur postgres de metis
val = CategoryCombo.get()
cursor.execute(
"""
SELECT ROW_NUMBER() OVER(ORDER BY id_opp) id, *
FROM(
SELECT
a.id_opp,
a.nom,
a.com_dep,
a.emprise,
a.travaux,
e.prev_starr,
a.cables,
a.typ_cable,
a.prog_dsp,
CASE WHEN a.debut_trvx IS NULL THEN 'Inconnue'::character varying ELSE a.debut_trvx END AS debut_trvx,
a.moa,
d.nb_suf,
f.longueur_max as longueur,
CASE WHEN b.nb_chb_exists IS NULL THEN 0 ELSE b.nb_chb_exists END AS nb_chb_exists,
CASE WHEN g.nb_chb_a_creer IS NULL THEN 0 ELSE g.nb_chb_a_creer END AS nb_chb_a_creer,
CASE WHEN g.nb_chb_desserte IS NULL THEN 0 ELSE g.nb_chb_desserte END AS nb_chb_desserte,
CASE WHEN g.nb_chb_transport IS NULL THEN 0 ELSE g.nb_chb_transport END AS nb_chb_transport,
CASE WHEN g.nb_chb_indef IS NULL THEN 0 ELSE g.nb_chb_indef END AS nb_chb_indef
FROM coordination.opportunite a
LEFT JOIN rip1.vue_chambres_adn b ON a.id_opp like b.id_opp
LEFT JOIN administratif.vue_nb_suf_opp d ON a.id_opp like d.id_opp
LEFT JOIN coordination.vue_rapport_prev_starr e ON a.id_opp like e.id_opp
LEFT JOIN coordination.vue_rapport_longueur f ON a.id_opp like f.id_opp
LEFT JOIN coordination.vue_nb_chb_a_creer g ON a.id_opp like g.id_opp
where statut like '""" +val+
"""' GROUP BY
a.id_opp,a.nom, a.com_dep,a.emprise, a.travaux,e.prev_starr, a.cables,
a.typ_cable, a.prog_dsp, a.debut_trvx, a.moa, d.nb_suf, b.nb_chb_exists,
f.longueur_max, g.nb_chb_a_creer, g.nb_chb_desserte,g.nb_chb_transport, g.nb_chb_indef
order by id_opp DESC
)vue;
"""
"""
SELECT
row_number() over () AS id,
a.id_opp,
prev_starr,
lg_prev_st,
gc_typ_mut,
sum(CASE WHEN a.gc_typ_mut IS null THEN null ELSE longueur END) as lg_typ_mut,
gc_typ_int,
sum(CASE WHEN a.gc_typ_int IS null THEN null ELSE longueur END) as lg_typ_int,
sum(longueur) as longueur,
a.com_dep
FROM coordination.opportunite as a
LEFT JOIN rip1.vue_chambres_adn as b on a.id_opp=b.id_opp
LEFT JOIN administratif.vue_nb_suf_opp as d on a.id_opp=d.id_opp
where statut like '""" +val+
"""'GROUP BY
a.id_opp, com_dep, prev_starr,lg_prev_st, gc_typ_mut, gc_typ_int
ORDER BY id_opp
;
"""
)
p = Popen("script.bat", cwd=os.getcwd())
stdout, stderr = p.communicate()
def getUpdateData(event):
cursor.execute("SELECT id_opp FROM coordination.opportunite where statut LIKE '"+CategoryCombo.get()+"' group by id_opp")
res=cursor.fetchall()
res = [x for xs in res for x in xs]
AccountCombo['values'] = res
top=Toplevel(root) # créer la fenêtre (instancier)
# Creation labels pour les combobox
message = "Selectionner un statut" #définir le texte de l'étiquette
lab=Label(top, text=message).grid(row = 2,column = 1,padx = 1, pady=0) # définir l'étiquette
message = "Selectionner une opportunité\n (A venir)" #définir le texte de l'étiquette
labo=Label(top, text=message).grid(row = 4,column = 1,padx = 1, pady=0) # définir l'étiquette
# Creation des combobox
AccountCombo = tkinter.ttk.Combobox( top, width = 15)
sqlData = ListeChoix
CategoryCombo = tkinter.ttk.Combobox(top, values = records_statut)
# Ajouter les combobox
CategoryCombo.bind('<<ComboboxSelected>>', updateSQLView)
#AccountCombo.bind('<<ComboboxSelected>>', updateSQLView)
CategoryCombo.grid(row = 3,column = 1,padx = 10,pady = 25)
AccountCombo.grid(row = 5,column = 1,pady = 25,padx = 10)
def do_go():
ListButton.pack(fill=BOTH, expand=YES)
IconButton = Button( command=ListeChoix)
image = ImageTk.PhotoImage(file="C:/github_repo/github_repo_python/gui/rapports_color.png")
IconButton.config(image=image)
IconButton.image = image
IconButton.pack(fill=BOTH, expand=YES)
colorfont = font.Font(family='verdana', size=10, weight=font.BOLD)
ListButton = Button(text="Choisir dans la liste des opportunités à l\'étude", command=ListeChoix,font=colorfont, height=3 ).pack(side=TOP, anchor=W, fill=X, expand=YES)
root = Tk()
root.option_add('*font', ('verdana', 12, 'bold'))
root.title("Générateur de rapports - GUI")
root.mainloop() |
