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);
        }
Пример #2
0
        public static string SELECT_ALUMNOS_ADMITIDOS(long oid_modulo, DateTime fecha_examen, Dictionary <string, PromocionInfo> promociones)
        {
            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 alumno_parte            = nHManager.Instance.GetSQLTable(typeof(AlumnoParteRecord));

            string query = "SELECT DISTINCT APR.*, TO_ASCII(A.\"APELLIDOS\", 'LATIN1') AS \"APELLIDOS\", TO_ASCII(A.\"NOMBRE\", 'LATIN1') AS \"NOMBRE\", PR.\"NOMBRE\" AS \"PROMOCION\", A.\"ID\" AS \"DNI\" " +
                           "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\" = 'FALSE' " /*+ 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\" = 'TRUE'" /*+ 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\" = 'TRUE' " /*+ 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\", 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 " + alumno_promocion + " as apromo ON (apromo.\"OID_ALUMNO\" = a.\"OID\")   " +
                         "                INNER JOIN " + promocion + " as pr ON (pr.\"OID\" = apromo.\"OID_PROMOCION\")      " +
                         "                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\"     " +
                          * "                UNION     " +
                          * "                SELECT m.\"OID\" AS \"OID_MODULO\", a.\"OID\" AS \"OID_ALUMNO\", 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 " + alumno_promocion + " as apromo ON (apromo.\"OID_ALUMNO\" = a.\"OID\")   " +
                          * "                INNER JOIN " + promocion + " as pr ON (pr.\"OID\" = apromo.\"OID_PROMOCION\")      " +
                          * "                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\"    ) 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') 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') 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') 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') 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""
		                            OR ""FECHA1"" - interval '1 year' <= ""FECHA2""
		                            OR ""FECHA2"" - interval '1 year' <= ""FECHA3""))"        ;

            if (promociones != null && promociones.Count > 0)
            {
                string subquery = string.Empty;
                foreach (KeyValuePair <string, PromocionInfo> item in promociones)
                {
                    subquery += item.Value.Oid.ToString() + ", ";
                }

                query += " AND PR.\"OID\" IN (" + subquery.Substring(0, subquery.Length - 2) + ") ";
            }
            query += "ORDER BY \"APELLIDOS\", \"NOMBRE\", \"PROMOCION\";";

            return(query);
        }