531 lines
14 KiB
Vala
531 lines
14 KiB
Vala
/*
|
||
* Copyright 2019 Chris Cromer
|
||
*
|
||
* Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
|
||
*
|
||
* 1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
|
||
*
|
||
* 2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
|
||
*
|
||
* 3. Neither the name of the copyright holder nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
|
||
*
|
||
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
|
||
*/
|
||
|
||
namespace Colegio {
|
||
using Constants;
|
||
using Misc;
|
||
using DB;
|
||
using DB.Wrapper;
|
||
using Postgres;
|
||
|
||
[GtkTemplate (ui = "/cl/cromer/ubb/colegio/query.window.ui")]
|
||
public class QueryWindow : Gtk.ApplicationWindow {
|
||
private Connection conn;
|
||
public enum Query {
|
||
Q1,
|
||
Q2,
|
||
Q3,
|
||
Q4,
|
||
Q5,
|
||
Q6
|
||
}
|
||
private Query query;
|
||
private enum Q1Column {
|
||
ALUMNO,
|
||
APODERADO,
|
||
CURSO,
|
||
JEFE,
|
||
ASISTENTE,
|
||
N_COLUMNS
|
||
}
|
||
private enum Q2Column {
|
||
PROFESOR,
|
||
N_COLUMNS
|
||
}
|
||
private enum Q3Column {
|
||
NOMBRE,
|
||
CANTIDAD,
|
||
N_COLUMNS
|
||
}
|
||
private enum Q4Column {
|
||
CURSO,
|
||
CANTIDAD,
|
||
N_COLUMNS
|
||
}
|
||
private enum Q5Column {
|
||
NOMBRE,
|
||
PROMEDIO,
|
||
N_COLUMNS
|
||
}
|
||
private enum Q6Column {
|
||
NOMBRE,
|
||
PORCENTAJE,
|
||
N_COLUMNS
|
||
}
|
||
private Gtk.ListStore list_store;
|
||
|
||
[GtkChild]
|
||
private Gtk.ScrolledWindow scroll_window;
|
||
|
||
[GtkChild]
|
||
private Gtk.Label statement;
|
||
|
||
[GtkChild]
|
||
private Gtk.Label sql;
|
||
|
||
[GtkChild]
|
||
private Gtk.Button run_query;
|
||
|
||
[GtkChild]
|
||
private Gtk.Button close_query;
|
||
|
||
private Gtk.TreeView query_tree;
|
||
|
||
[GtkCallback]
|
||
public void on_clicked_button (Gtk.Button button) {
|
||
if (button == run_query) {
|
||
list_store.clear ();
|
||
if (query == Query.Q1) {
|
||
var res = conn.db.exec ("
|
||
SELECT CONCAT_WS(' ', AL.nombres, AL.apellidos) AS alumno,
|
||
CONCAT_WS(' ', AP.nombres, AP.apellidos) AS apoderado,
|
||
CO.nombre curso,
|
||
CONCAT_WS(' ', PJ.nombres, PJ.apellidos) AS jefe,
|
||
CONCAT_WS(' ', PA.nombres, PA.apellidos) AS asistente
|
||
FROM alumno AL
|
||
JOIN apoderado AP ON (AP.rut_apoderado = AL.rut_apoderado)
|
||
JOIN cursar CU ON (CU.rut_alumno = AL.rut_alumno)
|
||
JOIN curso CO ON (CO.id_curso = CU.id_curso)
|
||
JOIN profesor PJ ON (PJ.rut_profesor = CO.rut_profesor)
|
||
LEFT JOIN asistente ASI ON (ASI.id_curso = CO.id_curso)
|
||
LEFT JOIN profesor PA ON (PA.rut_profesor = ASI.rut_profesor)
|
||
JOIN ciudad C ON (C.id_ciudad = AL.id_ciudad)
|
||
JOIN region R ON (R.id_region = C.id_region)
|
||
WHERE (
|
||
CO.anyo = 2019 AND (
|
||
R.nombre_region = 'Bío Bío' OR R.nombre_region = 'Ñuble'
|
||
)
|
||
);
|
||
");
|
||
if (res.get_status () != ExecStatus.TUPLES_OK) {
|
||
#if DEBUG
|
||
error (conn.db.get_error_message ());
|
||
#else
|
||
warning (conn.db.get_error_message ());
|
||
#endif
|
||
}
|
||
else {
|
||
var wra = new ResultWrapper (res);
|
||
List<Q1> list = new List<Q1> ();
|
||
int n = res.get_n_tuples ();
|
||
for (int i = 0; i < n; i++) {
|
||
try {
|
||
var result = new Q1 (wra.get_string_n (i, "alumno"),
|
||
wra.get_string_n (i, "apoderado"),
|
||
wra.get_string_n (i, "curso"),
|
||
wra.get_string_n (i, "jefe"),
|
||
wra.get_string_n (i, "asistente")
|
||
);
|
||
list.append (result);
|
||
}
|
||
catch (Error e) {
|
||
#if DEBUG
|
||
error (e.message);
|
||
#else
|
||
warning (e.message);
|
||
#endif
|
||
}
|
||
}
|
||
|
||
list.foreach ((entry) => {
|
||
Gtk.TreeIter iter;
|
||
list_store.append (out iter);
|
||
list_store.set (iter,
|
||
Q1Column.ALUMNO, entry.alumno,
|
||
Q1Column.APODERADO, entry.apoderado,
|
||
Q1Column.CURSO, entry.curso,
|
||
Q1Column.JEFE, entry.jefe,
|
||
Q1Column.ASISTENTE, entry.asistente);
|
||
});
|
||
}
|
||
}
|
||
else if (query == Query.Q2) {
|
||
list_store.clear ();
|
||
var res = conn.db.exec ("
|
||
SELECT CONCAT_WS(' ', P.nombres, P.apellidos) AS profesor FROM profesor P
|
||
JOIN curso C ON (C.rut_profesor = P.rut_profesor)
|
||
WHERE P.rut_profesor NOT IN (
|
||
SELECT P2.rut_profesor FROM profesor P2
|
||
JOIN actividad A ON (A.rut_profesor = P2.rut_profesor)
|
||
JOIN actividad_nivel A2 ON (A2.id_actividad = A.id_actividad)
|
||
JOIN nivel N ON (N.id_nivel = A2.id_nivel)
|
||
WHERE (N.nombre = 'Primero' OR N.nombre = 'Segundo')
|
||
);
|
||
");
|
||
if (res.get_status () != ExecStatus.TUPLES_OK) {
|
||
#if DEBUG
|
||
error (conn.db.get_error_message ());
|
||
#else
|
||
warning (conn.db.get_error_message ());
|
||
#endif
|
||
}
|
||
else {
|
||
var wra = new ResultWrapper (res);
|
||
List<Q2> list = new List<Q2> ();
|
||
int n = res.get_n_tuples ();
|
||
for (int i = 0; i < n; i++) {
|
||
try {
|
||
var result = new Q2 (wra.get_string_n (i, "profesor"));
|
||
list.append (result);
|
||
}
|
||
catch (Error e) {
|
||
#if DEBUG
|
||
error (e.message);
|
||
#else
|
||
warning (e.message);
|
||
#endif
|
||
}
|
||
}
|
||
|
||
list.foreach ((entry) => {
|
||
Gtk.TreeIter iter;
|
||
list_store.append (out iter);
|
||
list_store.set (iter,
|
||
Q2Column.PROFESOR, entry.profesor);
|
||
});
|
||
}
|
||
}
|
||
else if (query == Query.Q3) {
|
||
list_store.clear ();
|
||
var res = conn.db.exec ("
|
||
SELECT A.nombre, COUNT(*) FROM actividad A
|
||
JOIN participar P ON (P.id_actividad = A.id_actividad)
|
||
JOIN alumno A2 ON (A2.rut_alumno = P.rut_alumno)
|
||
WHERE (A2.fecha_nacimiento >= '2006-01-01' AND A2.fecha_nacimiento <= '2008-12-31')
|
||
GROUP BY (A.nombre)
|
||
HAVING COUNT(*) > 5;
|
||
");
|
||
if (res.get_status () != ExecStatus.TUPLES_OK) {
|
||
#if DEBUG
|
||
error (conn.db.get_error_message ());
|
||
#else
|
||
warning (conn.db.get_error_message ());
|
||
#endif
|
||
}
|
||
else {
|
||
var wra = new ResultWrapper (res);
|
||
List<Q3> list = new List<Q3> ();
|
||
int n = res.get_n_tuples ();
|
||
for (int i = 0; i < n; i++) {
|
||
try {
|
||
var result = new Q3 (wra.get_string_n (i, "nombre"),
|
||
wra.get_int_n (i, "count")
|
||
);
|
||
list.append (result);
|
||
}
|
||
catch (Error e) {
|
||
#if DEBUG
|
||
error (e.message);
|
||
#else
|
||
warning (e.message);
|
||
#endif
|
||
}
|
||
}
|
||
|
||
list.foreach ((entry) => {
|
||
Gtk.TreeIter iter;
|
||
list_store.append (out iter);
|
||
list_store.set (iter,
|
||
Q3Column.NOMBRE, entry.nombre,
|
||
Q3Column.CANTIDAD, entry.cantidad);
|
||
});
|
||
}
|
||
}
|
||
else if (query == Query.Q4) {
|
||
list_store.clear ();
|
||
var res = conn.db.exec ("
|
||
SELECT M.curso, M.cantidad FROM matriculados M
|
||
WHERE (
|
||
M.cantidad = (
|
||
SELECT MIN(M2.cantidad) FROM matriculados M2
|
||
)
|
||
);
|
||
");
|
||
if (res.get_status () != ExecStatus.TUPLES_OK) {
|
||
#if DEBUG
|
||
error (conn.db.get_error_message ());
|
||
#else
|
||
warning (conn.db.get_error_message ());
|
||
#endif
|
||
}
|
||
else {
|
||
var wra = new ResultWrapper (res);
|
||
List<Q4> list = new List<Q4> ();
|
||
int n = res.get_n_tuples ();
|
||
for (int i = 0; i < n; i++) {
|
||
try {
|
||
var result = new Q4 (wra.get_string_n (i, "curso"),
|
||
wra.get_int_n (i, "cantidad")
|
||
);
|
||
list.append (result);
|
||
}
|
||
catch (Error e) {
|
||
#if DEBUG
|
||
error (e.message);
|
||
#else
|
||
warning (e.message);
|
||
#endif
|
||
}
|
||
}
|
||
|
||
list.foreach ((entry) => {
|
||
Gtk.TreeIter iter;
|
||
list_store.append (out iter);
|
||
list_store.set (iter,
|
||
Q4Column.CURSO, entry.curso,
|
||
Q4Column.CANTIDAD, entry.cantidad);
|
||
});
|
||
}
|
||
}
|
||
else if (query == Query.Q5) {
|
||
list_store.clear ();
|
||
var res = conn.db.exec ("
|
||
SELECT A.nombre, AVG(r.nota) FROM asignatura A
|
||
JOIN registro R ON (R.id_asignatura = A.id_asignatura)
|
||
JOIN asociado A2 ON (A2.id_asignatura = A.id_asignatura)
|
||
JOIN alumno A3 ON (A3.rut_alumno = R.rut_alumno)
|
||
WHERE (
|
||
A2.id_curso = '6A-2018'
|
||
AND R.rut_alumno NOT IN (
|
||
SELECT A4.rut_alumno FROM alumno A4
|
||
JOIN participar P ON (P.rut_alumno = A4.rut_alumno)
|
||
JOIN actividad A5 ON (A5.id_actividad = P.id_actividad)
|
||
WHERE (A5.nombre = 'Rugby' AND A4.rut_alumno = A3.rut_alumno)
|
||
)
|
||
)
|
||
GROUP BY (A.nombre);
|
||
");
|
||
if (res.get_status () != ExecStatus.TUPLES_OK) {
|
||
#if DEBUG
|
||
error (conn.db.get_error_message ());
|
||
#else
|
||
warning (conn.db.get_error_message ());
|
||
#endif
|
||
}
|
||
else {
|
||
var wra = new ResultWrapper (res);
|
||
List<Q5> list = new List<Q5> ();
|
||
int n = res.get_n_tuples ();
|
||
for (int i = 0; i < n; i++) {
|
||
try {
|
||
var result = new Q5 (wra.get_string_n (i, "nombre"),
|
||
wra.get_float_n (i, "avg")
|
||
);
|
||
list.append (result);
|
||
}
|
||
catch (Error e) {
|
||
#if DEBUG
|
||
error (e.message);
|
||
#else
|
||
warning (e.message);
|
||
#endif
|
||
}
|
||
}
|
||
|
||
list.foreach ((entry) => {
|
||
Gtk.TreeIter iter;
|
||
list_store.append (out iter);
|
||
list_store.set (iter,
|
||
Q5Column.NOMBRE, entry.nombre,
|
||
Q5Column.PROMEDIO, entry.promedio.to_string ("%.1f"));
|
||
});
|
||
}
|
||
}
|
||
else if (query == Query.Q6) {
|
||
list_store.clear ();
|
||
var res = conn.db.exec ("
|
||
SELECT A2.nombre, TRUNC(CAST(T1.cantidad AS DECIMAL(3,2)) * 100 / CAST(T2.cantidad AS DECIMAL(3,2)),2) AS porcentaje
|
||
FROM total_aprobados T1
|
||
JOIN total_alumnos T2 ON (T2.asignatura = T1.asignatura)
|
||
JOIN asociado A ON (T1.asignatura = A.id_asignatura AND T2.asignatura = A.id_asignatura)
|
||
JOIN asignatura A2 ON (A2.id_asignatura = A.id_asignatura)
|
||
WHERE ((CAST(T1.cantidad AS DECIMAL(3,2)) * 100 / CAST(T2.cantidad AS DECIMAL(3,2))) < 50);
|
||
");
|
||
if (res.get_status () != ExecStatus.TUPLES_OK) {
|
||
#if DEBUG
|
||
error (conn.db.get_error_message ());
|
||
#else
|
||
warning (conn.db.get_error_message ());
|
||
#endif
|
||
}
|
||
else {
|
||
var wra = new ResultWrapper (res);
|
||
List<Q6> list = new List<Q6> ();
|
||
int n = res.get_n_tuples ();
|
||
for (int i = 0; i < n; i++) {
|
||
try {
|
||
var result = new Q6 (wra.get_string_n (i, "nombre"),
|
||
wra.get_float_n (i, "porcentaje")
|
||
);
|
||
list.append (result);
|
||
}
|
||
catch (Error e) {
|
||
#if DEBUG
|
||
error (e.message);
|
||
#else
|
||
warning (e.message);
|
||
#endif
|
||
}
|
||
}
|
||
|
||
list.foreach ((entry) => {
|
||
Gtk.TreeIter iter;
|
||
list_store.append (out iter);
|
||
list_store.set (iter,
|
||
Q6Column.NOMBRE, entry.nombre,
|
||
Q6Column.PORCENTAJE, entry.porcentaje.to_string () + "%");
|
||
});
|
||
}
|
||
}
|
||
else {
|
||
this.close ();
|
||
}
|
||
}
|
||
else if (button == close_query) {
|
||
this.close ();
|
||
}
|
||
}
|
||
|
||
public QueryWindow (Gtk.Application application, Connection conn, Query query) {
|
||
Object (application: application);
|
||
this.conn = conn;
|
||
this.query = query;
|
||
|
||
// Load scroll window's content
|
||
var builder = new Gtk.Builder ();
|
||
try {
|
||
builder.add_from_resource ("/cl/cromer/ubb/colegio/query.tree.ui");
|
||
builder.connect_signals (null);
|
||
if (query == Query.Q1) {
|
||
query_tree = builder.get_object ("query_tree_q1") as Gtk.TreeView;
|
||
}
|
||
else if (query == Query.Q2) {
|
||
query_tree = builder.get_object ("query_tree_q2") as Gtk.TreeView;
|
||
}
|
||
else if (query == Query.Q3) {
|
||
query_tree = builder.get_object ("query_tree_q3") as Gtk.TreeView;
|
||
}
|
||
else if (query == Query.Q4) {
|
||
query_tree = builder.get_object ("query_tree_q4") as Gtk.TreeView;
|
||
}
|
||
else if (query == Query.Q5) {
|
||
query_tree = builder.get_object ("query_tree_q5") as Gtk.TreeView;
|
||
}
|
||
else if (query == Query.Q6) {
|
||
query_tree = builder.get_object ("query_tree_q6") as Gtk.TreeView;
|
||
}
|
||
else {
|
||
this.close ();
|
||
}
|
||
query_tree.set_visible (true);
|
||
|
||
scroll_window.add (query_tree);
|
||
}
|
||
catch (Error e) {
|
||
// This error is not fatal, so let's keep going
|
||
warning (e.message);
|
||
}
|
||
|
||
this.set_visible (true); // This fixes: Gtk-CRITICAL **: 23:58:22.139: gtk_box_gadget_distribute: assertion 'size >= 0' failed in GtkScrollbar
|
||
}
|
||
|
||
public void initialize () {
|
||
if (query == Query.Q1) {
|
||
this.set_title ("(Q1) Alumnos de Bı́obio o Ñuble");
|
||
|
||
list_store = new Gtk.ListStore (Q1Column.N_COLUMNS,
|
||
typeof (string),
|
||
typeof (string),
|
||
typeof (string),
|
||
typeof (string),
|
||
typeof (string));
|
||
|
||
query_tree.set_model (list_store);
|
||
|
||
statement.set_text (Q1_STATEMENT);
|
||
|
||
sql.set_text (Q1_SQL);
|
||
}
|
||
else if (query == Query.Q2) {
|
||
this.set_title ("(Q2) Profesores sin actividad");
|
||
|
||
list_store = new Gtk.ListStore (Q2Column.N_COLUMNS,
|
||
typeof (string));
|
||
|
||
query_tree.set_model (list_store);
|
||
|
||
statement.set_text (Q2_STATEMENT);
|
||
|
||
sql.set_text (Q2_SQL);
|
||
}
|
||
else if (query == Query.Q3) {
|
||
this.set_title ("(Q3) Actividades");
|
||
|
||
list_store = new Gtk.ListStore (Q3Column.N_COLUMNS,
|
||
typeof (string),
|
||
typeof (uint));
|
||
|
||
query_tree.set_model (list_store);
|
||
|
||
statement.set_text (Q3_STATEMENT);
|
||
|
||
sql.set_text (Q3_SQL);
|
||
}
|
||
else if (query == Query.Q4) {
|
||
this.set_title ("(Q4) Cursos con menor matriculados");
|
||
|
||
list_store = new Gtk.ListStore (Q4Column.N_COLUMNS,
|
||
typeof (string),
|
||
typeof (uint));
|
||
|
||
query_tree.set_model (list_store);
|
||
|
||
statement.set_text (Q4_STATEMENT);
|
||
|
||
sql.set_text (Q4_SQL);
|
||
}
|
||
else if (query == Query.Q5) {
|
||
this.set_title ("(Q5) Notas de asignaturas");
|
||
|
||
list_store = new Gtk.ListStore (Q5Column.N_COLUMNS,
|
||
typeof (string),
|
||
typeof (string));
|
||
|
||
query_tree.set_model (list_store);
|
||
|
||
statement.set_text (Q5_STATEMENT);
|
||
|
||
sql.set_text (Q5_SQL);
|
||
}
|
||
else if (query == Query.Q6) {
|
||
this.set_title ("(Q6) Asignaturas con bajo aprobación");
|
||
|
||
list_store = new Gtk.ListStore (Q6Column.N_COLUMNS,
|
||
typeof (string),
|
||
typeof (string));
|
||
|
||
query_tree.set_model (list_store);
|
||
|
||
statement.set_text (Q6_STATEMENT);
|
||
|
||
sql.set_text (Q6_SQL);
|
||
}
|
||
else {
|
||
GLib.print ("Consulta inválida!\n");
|
||
this.close ();
|
||
}
|
||
}
|
||
}
|
||
}
|