public static string SELECT_ALUMNOS_ADMITIDOS(long oid_modulo, bool desarrollo, long oid_examen, ExamenPromocionList promociones, DateTime fecha_examen)
{
bool criterio_nota = ModulePrincipal.GetCriterioPorcentajeMinimoExamenAprobadoSetting();
bool criterio_faltas = ModulePrincipal.GetCriterioPorcentajeMaximoFaltasModuloSetting();
long porcentaje_nota = ModulePrincipal.GetPorcentajeMinimoExamenAprobadoSetting();
long porcentaje_faltas = ModulePrincipal.GetPorcentajeMaximoFaltasModuloSetting();
string clase_teorica = nHManager.Instance.GetSQLTable(typeof(ClaseTeoricaRecord));
string plan_estudios = nHManager.Instance.GetSQLTable(typeof(PlanEstudiosRecord));
string modulo = nHManager.Instance.GetSQLTable(typeof(ModuloRecord));
string promocion = nHManager.Instance.GetSQLTable(typeof(PromocionRecord));
string alumno_promocion = nHManager.Instance.GetSQLTable(typeof(AlumnoPromocionRecord));
string alumno = nHManager.Instance.GetSQLTable(typeof(AlumnoRecord));
string alumno_examen = nHManager.Instance.GetSQLTable(typeof(AlumnoExamenRecord));
string examen = nHManager.Instance.GetSQLTable(typeof(ExamenRecord));
string parte_asistencia = nHManager.Instance.GetSQLTable(typeof(ParteAsistenciaRecord));
string clase_parte = nHManager.Instance.GetSQLTable(typeof(Clase_ParteRecord));
string plan_extra = nHManager.Instance.GetSQLTable(typeof(PlanExtraRecord));
string clase_extra = nHManager.Instance.GetSQLTable(typeof(ClaseExtraRecord));
string respuesta_alumno_examen = nHManager.Instance.GetSQLTable(typeof(Respuesta_Alumno_ExamenRecord));
string pregunta_examen = nHManager.Instance.GetSQLTable(typeof(PreguntaExamenRecord));
string horario = nHManager.Instance.GetSQLTable(typeof(HorarioRecord));
string alumno_parte = nHManager.Instance.GetSQLTable(typeof(AlumnoParteRecord));
string query = "SELECT DISTINCT A.*, TO_ASCII(A.\"APELLIDOS\", 'LATIN1') AS AP, TO_ASCII(A.\"NOMBRE\", 'LATIN1') AS NOM " +
"FROM " + plan_estudios + " AS P " +
/*"INNER JOIN " + clase_teorica + " AS C ON (C.\"OID_PLAN\" = P.\"OID\") " +
* "INNER JOIN " + modulo + " AS M ON (C.\"OID_MODULO\" = M.\"OID\") " +*/
"INNER JOIN " + promocion + " AS PR ON (PR.\"OID_PLAN\" = P.\"OID\") " +
"INNER JOIN " + alumno_promocion + " AS APR ON (APR.\"OID_PROMOCION\" = PR.\"OID\") " +
"INNER JOIN " + alumno + " AS A ON (A.\"OID\" = APR.\"OID_ALUMNO\") " +
"WHERE TRUE "; //M.\"OID\" = " + oid_modulo.ToString() + " ";
if (criterio_nota)
{
if (!desarrollo)
{
query += " AND A.\"OID\" NOT IN ( " +
" SELECT AL.\"OID\" " +
" FROM " + alumno + " AS AL " +
" INNER JOIN " + alumno_examen + " AS AE ON (AE.\"OID_ALUMNO\" = AL.\"OID\") " +
" INNER JOIN " + examen + " AS E ON (E.\"OID\" = AE.\"OID_EXAMEN\") " +
" WHERE E.\"OID_MODULO\" = " + oid_modulo.ToString() + " AND AE.\"PRESENTADO\" = TRUE AND E.\"DESARROLLO\" = " + desarrollo.ToString() + " AND E.\"OID\" != " + oid_examen.ToString() + " " +
" AND AE.\"CALIFICACION\" >= " + porcentaje_nota.ToString() + " " +
") ";
}
else
{
query += " AND A.\"OID\" NOT IN ( " +
" SELECT DISTINCT AL.\"OID\" " +
" FROM " + alumno + " AS AL " +
" INNER JOIN " + alumno_examen + " AS AE ON (AE.\"OID_ALUMNO\" = AL.\"OID\") " +
" INNER JOIN " + examen + " AS E ON (E.\"OID\" = AE.\"OID_EXAMEN\") " +
" INNER JOIN ( " +
" SELECT AE.\"OID\" AS \"OID_ALUMNO_EXAMEN\", COUNT(R.\"CALIFICACION\") AS \"APROBADAS\" " +
" FROM " + alumno + " AS AL " +
" INNER JOIN " + alumno_examen + " AS AE ON (AE.\"OID_ALUMNO\" = AL.\"OID\") " +
" INNER JOIN " + examen + " AS E ON (E.\"OID\" = AE.\"OID_EXAMEN\") " +
" INNER JOIN " + respuesta_alumno_examen + " AS R ON (R.\"OID_ALUMNO_EXAMEN\" = AE.\"OID\") " +
" WHERE E.\"OID_MODULO\" = " + oid_modulo.ToString() + " AND E.\"DESARROLLO\" = " + desarrollo.ToString() + " AND R.\"CALIFICACION\" >= " + porcentaje_nota.ToString() + " " +
" GROUP BY AE.\"OID\") AS Q1 ON (Q1.\"OID_ALUMNO_EXAMEN\" = AE.\"OID\") " +
" INNER JOIN ( " +
" SELECT E.\"OID\" AS \"OID_EXAMEN\", COUNT(P.\"OID\") AS \"TOTALES\" " +
" FROM " + examen + " AS E " +
" INNER JOIN " + pregunta_examen + " AS P ON (P.\"OID_EXAMEN\" = E.\"OID\") " +
" WHERE E.\"OID_MODULO\" = " + oid_modulo.ToString() + " AND E.\"DESARROLLO\" = " + desarrollo.ToString() + " AND E.\"OID\" != " + oid_examen.ToString() + " " +
" GROUP BY E.\"OID\") AS Q2 ON (Q2.\"OID_EXAMEN\" = E.\"OID\") " +
" WHERE Q1.\"APROBADAS\" = Q2.\"TOTALES\" " +
") ";
}
}
if (criterio_faltas)
{
query += " AND A.\"OID\" NOT IN ( " +
" SELECT AL.\"OID_ALUMNO\" " +
" FROM ( " +
" SELECT CAST(SUM(\"DURACION\") / SUM(QUERY1.\"TOTAL\") * 100 as numeric(10,2)) AS \"PORC\", \"OID_MODULO\", QUERY1.\"OID_ALUMNO\" " +
" FROM( " +
" SELECT m.\"OID\" AS \"OID_MODULO\", a.\"OID\" AS \"OID_ALUMNO\", pr.\"OID\" AS \"OID_PROMOCION\", COUNT(cp.\"OID\") AS \"DURACION\", QUERY2.\"TOTAL\" AS \"TOTAL\" " +
" FROM ( " +
" SELECT PE.\"OID\" AS \"PLAN2\", MOD.\"TEXTO\" AS \"MODULO2\", COUNT(C.\"OID\") AS \"TOTAL\" " +
" FROM " + plan_estudios + " AS PE " +
" INNER JOIN " + clase_teorica + " AS C ON ( C.\"OID_PLAN\" = PE.\"OID\") " +
" INNER JOIN " + modulo + " AS MOD ON ( C.\"OID_MODULO\" = MOD.\"OID\") " +
" GROUP BY \"PLAN2\", \"MODULO2\" ) AS QUERY2, " + alumno_parte + " as ap " +
" INNER JOIN " + alumno + " as a ON (a.\"OID\" = ap.\"OID_ALUMNO\") " +
" INNER JOIN " + parte_asistencia + " as p ON (p.\"OID\" = ap.\"OID_PARTE\") " +
" INNER JOIN " + horario + " AS hr ON (hr.\"OID\" = p.\"OID_HORARIO\") " +
" INNER JOIN " + alumno_promocion + " as apromo ON (apromo.\"OID_ALUMNO\" = a.\"OID\") " +
" INNER JOIN " + promocion + " as pr ON (pr.\"OID\" = apromo.\"OID_PROMOCION\" AND hr.\"OID_PROMOCION\" = pr.\"OID\") " +
" INNER JOIN " + plan_estudios + " as pl ON (pl.\"OID\" = pr.\"OID_PLAN\") " +
" INNER JOIN " + clase_parte + " as cp ON (p.\"OID\" = cp.\"OID_PARTE\") " +
" INNER JOIN " + clase_teorica + " as ct ON (ct.\"OID\" = cp.\"OID_CLASE\") " +
" INNER JOIN " + modulo + " as m ON (m.\"OID\" = ct.\"OID_MODULO\") " +
" WHERE ap.\"FALTA\" = 'true' AND ap.\"RECUPERADA\" = 'false' AND cp.\"TIPO\" = 1 AND \"PLAN2\" = pl.\"OID\" AND \"MODULO2\" = m.\"TEXTO\" " +
/*" GROUP BY \"TOTAL\", m.\"OID\", a.\"OID\", pr.\"OID\" " +
* " UNION " +
* " SELECT m.\"OID\" AS \"OID_MODULO\", a.\"OID\" AS \"OID_ALUMNO\", pr.\"OID\" AS \"OID_PROMOCION\", COUNT(cp.\"OID\") AS \"DURACION\", QUERY2.\"TOTAL\" AS \"TOTAL\" " +
* " FROM ( " +
* " SELECT PE.\"OID\" AS \"PLAN2\", MOD.\"TEXTO\" AS \"MODULO2\", COUNT(C.\"OID\") AS \"TOTAL\" " +
* " FROM " + plan_extra + " AS PE " +
* " INNER JOIN " + clase_extra + " AS C ON ( C.\"OID_PLAN\" = PE.\"OID\") " +
* " INNER JOIN " + modulo + " AS MOD ON ( C.\"OID_MODULO\" = MOD.\"OID\") " +
* " GROUP BY \"PLAN2\", \"MODULO2\" ) AS QUERY2, \"0001\".\"Alumno_Parte\" as ap " +
* " INNER JOIN " + alumno + " as a ON (a.\"OID\" = ap.\"OID_ALUMNO\") " +
* " INNER JOIN " + parte_asistencia + " as p ON (p.\"OID\" = ap.\"OID_PARTE\") " +
* " INNER JOIN " + horario + " AS hr ON (hr.\"OID\" = p.\"OID_HORARIO\") " +
* " INNER JOIN " + alumno_promocion + " as apromo ON (apromo.\"OID_ALUMNO\" = a.\"OID\") " +
* " INNER JOIN " + promocion + " as pr ON (pr.\"OID\" = apromo.\"OID_PROMOCION\" AND hr.\"OID_PROMOCION\" = pr.\"OID\") " +
* " INNER JOIN " + plan_extra + " as pl ON (pl.\"OID\" = pr.\"OID_PLAN_EXTRA\") " +
* " INNER JOIN " + clase_parte + " as cp ON (p.\"OID\" = cp.\"OID_PARTE\") " +
* " INNER JOIN " + clase_extra + " as ct ON (ct.\"OID\" = cp.\"OID_CLASE\") " +
* " INNER JOIN " + modulo + " as m ON (m.\"OID\" = ct.\"OID_MODULO\") " +
* " WHERE ap.\"FALTA\" = 'true' AND ap.\"RECUPERADA\" = 'false' AND cp.\"TIPO\" = 3 AND \"PLAN2\" = pl.\"OID\" AND \"MODULO2\" = m.\"TEXTO\" " +
*/" GROUP BY \"TOTAL\", m.\"OID\", a.\"OID\", pr.\"OID\" ) AS QUERY1 " +
" WHERE \"OID_MODULO\" = " + oid_modulo.ToString() + " " +
" GROUP BY \"OID_MODULO\", QUERY1.\"OID_ALUMNO\")AS AL " +
" WHERE AL.\"PORC\" > " + porcentaje_faltas.ToString() + " " +
") ";
}
/*query += @" AND A.""OID"" NOT IN (
* SELECT ""OID_ALUMNO""
* FROM (
* SELECT ""OID_ALUMNO"",
* COALESCE( (SELECT DISTINCT xi.""FECHA_EXAMEN""
* FROM ( SELECT AE.""OID"", AE.""OID_ALUMNO"", EX.""FECHA_EXAMEN""
* FROM " + alumno_examen + @" AS AE
* INNER JOIN " + examen + @" AS EX ON AE.""OID_EXAMEN"" = EX.""OID""
* WHERE AE.""PRESENTADO"" = 'TRUE' AND EX.""OID_MODULO"" = " + oid_modulo.ToString() + @" AND EX.""FECHA_EXAMEN"" > '2012-08-31' AND EX.""DESARROLLO"" = " + desarrollo.ToString() + @") xi
* WHERE xi.""OID_ALUMNO"" = xo.""OID_ALUMNO""
* ORDER BY ""FECHA_EXAMEN"" DESC
* OFFSET 0 LIMIT 1), '9999-12-31') AS ""FECHA1"",
* COALESCE(
* (SELECT DISTINCT xi.""FECHA_EXAMEN""
* FROM ( SELECT AE.""OID"", AE.""OID_ALUMNO"", EX.""FECHA_EXAMEN""
* FROM " + alumno_examen + @" AS AE
* INNER JOIN " + examen + @" AS EX ON AE.""OID_EXAMEN"" = EX.""OID""
* WHERE AE.""PRESENTADO"" = 'TRUE' AND EX.""OID_MODULO"" = " + oid_modulo.ToString() + @" AND EX.""FECHA_EXAMEN"" > '2012-08-31' AND EX.""DESARROLLO"" = " + desarrollo.ToString() + @") xi
* WHERE xi.""OID_ALUMNO"" = xo.""OID_ALUMNO""
* ORDER BY ""FECHA_EXAMEN"" DESC
* OFFSET 1 LIMIT 1), '9999-12-31') AS ""FECHA2"",
* COALESCE(
* (SELECT DISTINCT xi.""FECHA_EXAMEN""
* FROM ( SELECT AE.""OID"", AE.""OID_ALUMNO"", EX.""FECHA_EXAMEN""
* FROM " + alumno_examen + @" AS AE
* INNER JOIN " + examen + @" AS EX ON AE.""OID_EXAMEN"" = EX.""OID""
* WHERE AE.""PRESENTADO"" = 'TRUE' AND EX.""OID_MODULO"" = " + oid_modulo.ToString() + @" AND EX.""FECHA_EXAMEN"" > '2012-08-31' AND EX.""DESARROLLO"" = " + desarrollo.ToString() + @") xi
* WHERE xi.""OID_ALUMNO"" = xo.""OID_ALUMNO""
* ORDER BY ""FECHA_EXAMEN"" DESC
* OFFSET 2 LIMIT 1), '9999-12-31') AS ""FECHA3""
* FROM ( SELECT DISTINCT ""OID_ALUMNO""
* FROM ( SELECT AE.""OID"", AE.""OID_ALUMNO"", EX.""FECHA_EXAMEN""
* FROM " + alumno_examen + @" AS AE
* INNER JOIN " + examen + @" AS EX ON AE.""OID_EXAMEN"" = EX.""OID""
* WHERE AE.""PRESENTADO"" = 'TRUE' AND EX.""OID_MODULO"" = " + oid_modulo.ToString() + @" AND EX.""FECHA_EXAMEN"" > '2012-08-31' AND EX.""DESARROLLO"" = " + desarrollo.ToString() + @") AS XM) xo
* ) AS Q
* WHERE ""FECHA1"" != '9999-12-31' AND ""FECHA2"" != '9999-12-31' AND ""FECHA3"" != '9999-12-31'
* AND date '" + fecha_examen.ToString("yyyy-MM-dd") + @"' - interval '1 year' <= ""FECHA1"")";*/
query += @" AND A.""OID"" NOT IN (
SELECT ""OID_ALUMNO""
FROM (
SELECT xi.""PRESENTADOS"", ""OID_ALUMNO""
FROM ( SELECT AE.""OID_ALUMNO"",COUNT(AE.""OID_ALUMNO"") AS ""PRESENTADOS""
FROM " + alumno_examen + @" AS AE
INNER JOIN " + examen + @" AS EX ON AE.""OID_EXAMEN"" = EX.""OID""
WHERE AE.""PRESENTADO"" = 'TRUE' AND EX.""OID_MODULO"" = " + oid_modulo.ToString() + @" AND EX.""FECHA_EXAMEN"" > '" + fecha_examen.AddYears(-1).ToString("yyyy-MM-dd") + @"' AND EX.""FECHA_EXAMEN"" < '" + fecha_examen.ToString("yyyy-MM-dd") + @"' AND EX.""DESARROLLO"" = " + desarrollo.ToString()
+ @" GROUP BY ""OID_ALUMNO"") xi
) AS Q
WHERE ""PRESENTADOS"" >= 3)";
if (oid_examen > 0)
{
//ExamenInfo info = ExamenInfo.Get(oid_examen, false);
//info.LoadChilds(typeof(ExamenPromocion), false);
if (promociones.Count > 0)
{
// string ep = nHManager.Instance.GetSQLTable(typeof(ExamenPromocionRecord));
// query += @" AND PR.""OID"" IN ( SELECT ""OID_PROMOCION""
// FROM " + ep + @"
// WHERE ""OID_EXAMEN"" = " + oid_examen.ToString() + @")";
string list_promos = "(";
foreach (ExamenPromocionInfo expr in promociones)
{
list_promos += expr.OidPromocion.ToString() + ", ";
}
list_promos = list_promos.Substring(0, list_promos.Length - 2);
list_promos += ")";
query += @" AND PR.""OID"" IN " + list_promos;
}
}
query += " ORDER BY \"APELLIDOS\", \"NOMBRE\";";
return(query);
}
/// <summary>
/// Retrieve the complete list from db
/// </summary>
/// <returns>AlumnoList</returns>
public static AlumnoList GetAlumnosAdmitidosList(long oid_modulo, bool desarrollo, long oid_examen, ExamenPromocionList promociones, DateTime fecha_examen, bool childs)
{
CriteriaEx criteria = Alumno.GetCriteria(Alumno.OpenSession());
criteria.Childs = childs;
criteria.Query = AlumnoList.SELECT_ALUMNOS_ADMITIDOS(oid_modulo, desarrollo, oid_examen, promociones, fecha_examen);
//No criteria. Retrieve all de List
AlumnoList list = DataPortal.Fetch <AlumnoList>(criteria);
CloseSession(criteria.SessionCode);
return(list);
}
