Esempio n. 1
0
        public static void CheckDBVersion()
        {
            ApplicationSettingInfo dbVersion = ApplicationSettingInfo.Get(Settings.Default.DB_VERSION_VARIABLE);

            //Version de base de datos equivalente o no existe la variable
            if ((dbVersion.Value == string.Empty) ||
                (String.CompareOrdinal(dbVersion.Value, ModulePrincipal.GetDBVersion()) == 0))
            {
                return;
            }
            //Version de base de datos superior
            else if (String.CompareOrdinal(dbVersion.Value, ModulePrincipal.GetDBVersion()) > 0)
            {
                throw new iQException(String.Format(Library.Resources.Messages.DB_VERSION_HIGHER,
                                                    dbVersion.Value,
                                                    ModulePrincipal.GetDBVersion(),
                                                    Settings.Default.NAME),
                                      iQExceptionCode.DB_VERSION_MISSMATCH);
            }
            //Version de base de datos inferior
            else if (String.CompareOrdinal(dbVersion.Value, ModulePrincipal.GetDBVersion()) < 0)
            {
                throw new iQException(String.Format(Library.Resources.Messages.DB_VERSION_LOWER,
                                                    dbVersion.Value,
                                                    ModulePrincipal.GetDBVersion(),
                                                    Settings.Default.NAME),
                                      iQExceptionCode.DB_VERSION_MISSMATCH);
            }
        }
        /// <summary>
        /// Construye la tabla
        /// </summary>
        /// <param name="type"></param>
        /// <param name="schema"></param>
        /// <param name="sesion"></param>
        /// <returns></returns>
        public static string SELECT_BY_INSTRUCTOR_PROMOCION(long oid, DateTime fecha_inicio, DateTime fecha_fin)
        {
            string sip       = nHManager.Instance.GetSQLTable(typeof(Submodulo_Instructor_PromocionRecord));
            string submodulo = nHManager.Instance.GetSQLTable(typeof(SubmoduloRecord));
            string modulo    = nHManager.Instance.GetSQLTable(typeof(ModuloRecord));
            string promocion = nHManager.Instance.GetSQLTable(typeof(PromocionRecord));
            string si        = nHManager.Instance.GetSQLTable(typeof(Submodulo_InstructorRecord));

            string query;

            query = "SELECT SIP.*," +
                    "       COALESCE(M.\"NUMERO\",0) || ' ' || COALESCE(M.\"TEXTO\",'') AS \"MODULO\", " +
                    "       COALESCE(S.\"CODIGO\",'') || ' ' || COALESCE(S.\"TEXTO\",'') AS \"SUBMODULO\", " +
                    "       COALESCE(P.\"NUMERO\",'') || ' ' || COALESCE(P.\"NOMBRE\",'') AS \"PROMOCION\" , " +
                    "       M.\"OID\" AS \"OID_MODULO\"" +
                    " FROM " + sip + " AS SIP" +
                    " LEFT JOIN " + submodulo + " AS S ON SIP.\"OID_SUBMODULO\" = S.\"OID\"" +
                    " LEFT JOIN " + modulo + " AS M ON S.\"OID_MODULO\" = M.\"OID\"" +
                    " LEFT JOIN " + promocion + " AS P ON P.\"OID\" = SIP.\"OID_PROMOCION\"" +
                    " WHERE SIP.\"OID_INSTRUCTOR_PROMOCION\" = " + oid.ToString() + " ";

            if (ModulePrincipal.GetMostrarInstructoresAutorizadosSetting())
            {
                query += @" AND S.""OID"" NOT IN (	SELECT ""OID_SUBMODULO""
					                                        FROM "                     + si + @"
					                                        WHERE ('"                     + fecha_inicio.ToString("MM-dd-yyyy") + @"' BETWEEN COALESCE(""FECHA_INICIO"", '01-01-0001') AND COALESCE(""FECHA_FIN"", '12-31-2999') OR 
						                                        '"                         + fecha_fin.ToString("MM-dd-yyyy") + @"' BETWEEN COALESCE(""FECHA_INICIO"", '01-01-0001') AND COALESCE(""FECHA_FIN"", '12-31-2999'))
                                                                 AND ""OID_INSTRUCTOR"" = SIP.""OID_INSTRUCTOR"") ";
            }

            query += " ORDER BY M.\"NUMERO_ORDEN\", S.\"CODIGO_ORDEN\"";

            return(query);
        }
        /// <summary>
        /// Construye la tabla
        /// </summary>
        /// <param name="type"></param>
        /// <param name="schema"></param>
        /// <param name="sesion"></param>
        /// <returns></returns>
        public static string SELECT_BY_INSTRUCTOR_PROMOCION(long oid)
        {
            string sip                  = nHManager.Instance.GetSQLTable(typeof(Submodulo_Instructor_PromocionRecord));
            string submodulo            = nHManager.Instance.GetSQLTable(typeof(SubmoduloRecord));
            string modulo               = nHManager.Instance.GetSQLTable(typeof(ModuloRecord));
            string promocion            = nHManager.Instance.GetSQLTable(typeof(PromocionRecord));
            string submodulo_instructor = nHManager.Instance.GetSQLTable(typeof(Submodulo_InstructorRecord));

            string query;

            query = "SELECT SIP.*," +
                    "       COALESCE(M.\"NUMERO\",0) || ' ' || COALESCE(M.\"TEXTO\",'') AS \"MODULO\", " +
                    "       COALESCE(S.\"CODIGO\",'') || ' ' || COALESCE(S.\"TEXTO\",'') AS \"SUBMODULO\", " +
                    "       COALESCE(P.\"NUMERO\",'') || ' ' || COALESCE(P.\"NOMBRE\",'') AS \"PROMOCION\" , " +
                    "       M.\"OID\" AS \"OID_MODULO\"" +
                    " FROM " + sip + " AS SIP" +
                    " LEFT JOIN " + submodulo + " AS S ON SIP.\"OID_SUBMODULO\" = S.\"OID\"" +
                    " LEFT JOIN " + modulo + " AS M ON S.\"OID_MODULO\" = M.\"OID\"" +
                    " LEFT JOIN " + promocion + " AS P ON P.\"OID\" = SIP.\"OID_PROMOCION\"" +
                    " WHERE SIP.\"OID_INSTRUCTOR_PROMOCION\" = " + oid.ToString();

            if (ModulePrincipal.GetMostrarInstructoresAutorizadosSetting())
            {
                query += " AND S.\"OID\" NOT IN (	SELECT \"OID_SUBMODULO\" "+
                         "                               FROM " + submodulo_instructor + " " +
                         "                               WHERE \"OID_INSTRUCTOR\" = SIP.\"OID_INSTRUCTOR\" AND COALESCE(\"FECHA_FIN\", '12-31-2999') >= current_date) ";
            }

            query += " ORDER BY M.\"NUMERO_ORDEN\", S.\"CODIGO_ORDEN\"";

            return(query);
        }
Esempio n. 4
0
        internal static string WHERE(QueryConditions conditions)
        {
            string query;
            string si = nHManager.Instance.GetSQLTable(typeof(Submodulo_InstructorRecord));

            query = "   WHERE TRUE";

            if (conditions.Instructor != null && conditions.Instructor.Oid > 0)
            {
                query += " AND SI.\"OID_INSTRUCTOR\" = " + conditions.Instructor.Oid;
            }

            if (ModulePrincipal.GetMostrarInstructoresAutorizadosSetting())
            {
                query += @" AND SI.""OID_SUBMODULO"" NOT IN (	SELECT ""OID_SUBMODULO""
					                                            FROM "                     + si + @"
					                                            WHERE ('"                     + conditions.FechaAuxIniLabel + @"' BETWEEN COALESCE(""FECHA_INICIO"", '01-01-0001') AND COALESCE(""FECHA_FIN"", '12-31-2999') OR 
						                                            '"                         + conditions.FechaAuxFinLabel + @"' BETWEEN COALESCE(""FECHA_INICIO"", '01-01-0001') AND COALESCE(""FECHA_FIN"", '12-31-2999'))";

                if (conditions.Instructor != null && conditions.Instructor.Oid > 0)
                {
                    query += " AND \"OID_INSTRUCTOR\" = " + conditions.Instructor.Oid;
                }

                query += ") ";
            }

            return(query);
        }
Esempio n. 5
0
        public static string SELECT_PARTES_PRACTICAS()
        {
            string parte_asistencia     = nHManager.Instance.GetSQLTable(typeof(ParteAsistenciaRecord));
            string empleado             = nHManager.Instance.GetSQLTable(typeof(InstructorRecord));
            string horairo              = nHManager.Instance.GetSQLTable(typeof(HorarioRecord));
            string sesion               = nHManager.Instance.GetSQLTable(typeof(SesionRecord));
            string clase                = nHManager.Instance.GetSQLTable(typeof(ClasePracticaRecord));
            string clase_parte          = nHManager.Instance.GetSQLTable(typeof(Clase_ParteRecord));
            string promocion            = nHManager.Instance.GetSQLTable(typeof(PromocionRecord));
            string submodulo_instructor = nHManager.Instance.GetSQLTable(typeof(Submodulo_InstructorRecord));

            string query = string.Empty;

            if (ModulePrincipal.GetMostrarInstructoresAutorizadosSetting())
            {
                query = "SELECT DISTINCT PR.\"NOMBRE\" AS \"PROMOCION\", " +
                        "P.*, " +
                        "COALESCE(AU.\"AUTORIZADO\", I.\"NOMBRE\") AS \"INSTRUCTOR\", " +
                        "IE.\"NOMBRE\" AS \"INSTRUCTOR_EFECTIVO\" " +
                        "FROM " + parte_asistencia + " AS P " +
                        "INNER JOIN " + empleado + " AS I ON (P.\"OID_INSTRUCTOR\" = I.\"OID\") " +
                        "INNER JOIN " + empleado + " AS IE ON (P.\"OID_INSTRUCTOR_EFECTIVO\" = IE.\"OID\") " +
                        "INNER JOIN " + horairo + " AS H ON (P.\"OID_HORARIO\" = H.\"OID\") " +
                        "INNER JOIN " + sesion + " AS S ON (S.\"OID_HORARIO\" = H.\"OID\" AND S.\"FECHA\" = P.\"FECHA\") " +
                        "INNER JOIN " + clase + " AS CP ON (S.\"OID_CLASE_PRACTICA\" = CP.\"OID\") " +
                        "LEFT JOIN (    SELECT SI.\"OID_INSTRUCTOR\", SI.\"OID_SUBMODULO\", SI.\"FECHA_INICIO\", SI.\"FECHA_FIN\", IA.\"NOMBRE\" AS \"AUTORIZADO\"" +
                        "               FROM " + submodulo_instructor + " AS SI " +
                        "               INNER JOIN " + empleado + " AS IA ON IA.\"OID\" = SI.\"OID_INSTRUCTOR_SUPLENTE\") " +
                        "       AS AU ON AU.\"OID_INSTRUCTOR\" = I.\"OID\" AND AU.\"OID_SUBMODULO\" = CP.\"OID_SUBMODULO\" AND P.\"FECHA\" BETWEEN COALESCE(AU.\"FECHA_INICIO\", '01-01-0001') AND COALESCE(AU.\"FECHA_FIN\", '12-31-9999') " +
                        "INNER JOIN " + clase_parte + " AS C_P ON (C_P.\"OID_CLASE\" = CP.\"OID\" AND C_P.\"TIPO\" = 2 AND C_P.\"OID_PARTE\" = P.\"OID\") " +
                        "INNER JOIN " + promocion + " AS PR ON (H.\"OID_PROMOCION\" = PR.\"OID\") " +
                        "ORDER BY P.\"FECHA\", \"PROMOCION\";";
            }
            else
            {
                query = "SELECT DISTINCT PR.\"NOMBRE\" AS \"PROMOCION\", " +
                        "P.*, " +
                        "I.\"NOMBRE\" AS \"INSTRUCTOR\", " +
                        "IE.\"NOMBRE\" AS \"INSTRUCTOR_EFECTIVO\" " +
                        "FROM " + parte_asistencia + " AS P " +
                        "INNER JOIN " + empleado + " AS I ON (P.\"OID_INSTRUCTOR\" = I.\"OID\") " +
                        "INNER JOIN " + empleado + " AS IE ON (P.\"OID_INSTRUCTOR_EFECTIVO\" = IE.\"OID\") " +
                        "INNER JOIN " + horairo + " AS H ON (P.\"OID_HORARIO\" = H.\"OID\") " +
                        "INNER JOIN " + sesion + " AS S ON (S.\"OID_HORARIO\" = H.\"OID\" AND S.\"FECHA\" = P.\"FECHA\") " +
                        "INNER JOIN " + clase + " AS CP ON (S.\"OID_CLASE_PRACTICA\" = CP.\"OID\") " +
                        "INNER JOIN " + clase_parte + " AS C_P ON (C_P.\"OID_CLASE\" = CP.\"OID\" AND C_P.\"TIPO\" = 2 AND C_P.\"OID_PARTE\" = P.\"OID\") " +
                        "INNER JOIN " + promocion + " AS PR ON (H.\"OID_PROMOCION\" = PR.\"OID\") " +
                        "ORDER BY P.\"FECHA\", \"PROMOCION\";";
            }

            return(query);
        }
        internal static string SELECT_BY_HORARIO(long oid_horario, bool lock_table)
        {
            string s  = nHManager.Instance.GetSQLTable(typeof(SesionRecord));
            string i  = nHManager.Instance.GetSQLTable(typeof(InstructorRecord));
            string sm = nHManager.Instance.GetSQLTable(typeof(SubmoduloRecord));
            string ct = nHManager.Instance.GetSQLTable(typeof(ClaseTeoricaRecord));
            string cp = nHManager.Instance.GetSQLTable(typeof(ClasePracticaRecord));
            string ce = nHManager.Instance.GetSQLTable(typeof(ClaseExtraRecord));
            string si = nHManager.Instance.GetSQLTable(typeof(Submodulo_InstructorRecord));

            bool autorizados = ModulePrincipal.GetMostrarInstructoresAutorizadosSetting();

            string query;

            if (!autorizados)
            {
                query = "SELECT DISTINCT S.*, S.\"OID_PROFESOR\" AS \"OID_AUTORIZADO\" " +
                        " FROM " + s + " AS S" +
                        " WHERE S.\"OID_HORARIO\" = " + oid_horario.ToString() +
                        " ORDER BY S.\"FECHA\", S.\"HORA\"";
            }
            else
            {
                query = "SELECT DISTINCT S.*," +
                        " COALESCE(I.\"OID\", S.\"OID_PROFESOR\") AS \"OID_AUTORIZADO\"" +
                        " FROM " + s + " AS S" +
                        " INNER JOIN ( SELECT COALESCE(CT.\"OID_SUBMODULO\", COALESCE(CP.\"OID_SUBMODULO\", COALESCE(CE.\"OID_SUBMODULO\", 0))) AS \"OID_SUBMODULO\"," +
                        "           S.\"OID\" AS \"OID_SESION\"" +
                        "       FROM " + s + " AS S" +
                        "       LEFT JOIN " + ct + " AS CT ON CT.\"OID\" = S.\"OID_CLASE_TEORICA\"" +
                        "       LEFT JOIN " + cp + " AS CP ON CP.\"OID\" = S.\"OID_CLASE_PRACTICA\"" +
                        "       LEFT JOIN " + ce + " AS CE ON CE.\"OID\" = S.\"OID_CLASE_EXTRA\")" +
                        "   AS C ON C.\"OID_SESION\" = S.\"OID\"" +
                        " LEFT JOIN " + sm + " AS SM ON SM.\"OID\" = C.\"OID_SUBMODULO\"" +
                        " LEFT JOIN " + si + " AS SI ON SI.\"OID_SUBMODULO\" = SM.\"OID\" AND SI.\"OID_INSTRUCTOR\" = S.\"OID_PROFESOR\" AND S.\"FECHA\" BETWEEN COALESCE(SI.\"FECHA_INICIO\", '01-01-0001') AND COALESCE(SI.\"FECHA_FIN\", '12-31-2999')" +
                        " LEFT JOIN " + i + " AS I ON I.\"OID\" = SI.\"OID_INSTRUCTOR_SUPLENTE\"" +
                        " WHERE S.\"OID_HORARIO\" = " + oid_horario.ToString() +
                        " ORDER BY S.\"FECHA\", S.\"HORA\"";
            }

            //if (lock_table) query += " FOR UPDATE OF S NOWAIT";

            return(query);
        }
        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);
        }
Esempio n. 8
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);
        }
Esempio n. 9
0
 public static void UpgradeSettings()
 {
     ModulePrincipal.UpgradeSettings();
 }
Esempio n. 10
0
        private static string SELECT_DISPONIBILIDAD_SEMANAL(DateTime fecha_inicio, bool all)
        {
            string empleado       = nHManager.Instance.GetSQLTable(typeof(InstructorRecord));
            string disponibilidad = nHManager.Instance.GetSQLTable(typeof(DisponibilidadRecord));

            string fecha = fecha_inicio.Year.ToString("0000") +
                           "-" + fecha_inicio.Month.ToString("00") +
                           "-" + fecha_inicio.Day.ToString("00");

            long m_ini  = ModulePrincipal.GetHoraInicioDisponibilidadMananaSetting();
            long m_fin  = ModulePrincipal.GetHoraFinDisponibilidadMananaSetting();
            long t1_ini = ModulePrincipal.GetHoraInicioDisponibilidadTarde1Setting();
            long t1_fin = ModulePrincipal.GetHoraFinDisponibilidadTarde1Setting();
            long t2_ini = ModulePrincipal.GetHoraInicioDisponibilidadTarde2Setting();
            long t2_fin = ModulePrincipal.GetHoraFinDisponibilidadTarde2Setting();

            bool autorizados = ModulePrincipal.GetMostrarInstructoresAutorizadosSetting();

            string m_str   = string.Empty;
            string t1_str  = string.Empty;
            string t2_str  = string.Empty;
            string ms_str  = string.Empty;
            string t1s_str = string.Empty;
            string t2s_str = string.Empty;

            m_str = " \"L8AM\" = TRUE";
            for (long i = m_ini - 1; i < m_fin; i++)
            {
                m_str += " OR \"L" + i.ToString() + "\" = TRUE";

                if (i < 5)
                {
                    if (ms_str != string.Empty)
                    {
                        ms_str += " OR ";
                    }
                    ms_str += "\"S" + i.ToString() + "\" = TRUE";
                }
            }

            for (long i = t1_ini - 1; i < t1_fin; i++)
            {
                if (t1_str != string.Empty)
                {
                    t1_str += " OR ";
                }
                t1_str += "\"L" + i.ToString() + "\" = TRUE";

                if (i < 5)
                {
                    if (t1s_str != string.Empty)
                    {
                        t1s_str += " OR ";
                    }
                    t1s_str += "\"S" + i.ToString() + "\" = TRUE";
                }
            }

            for (long i = t2_ini - 1; i < t2_fin; i++)
            {
                if (t2_str != string.Empty)
                {
                    t2_str += " OR ";
                }
                t2_str += "\"L" + i.ToString() + "\" = TRUE";

                if (i < 5)
                {
                    if (t2s_str != string.Empty)
                    {
                        t2s_str += " OR ";
                    }
                    t2s_str += "\"S" + i.ToString() + "\" = TRUE";
                }
            }

            string query = "SELECT DISTINCT I.\"OID\", I.\"APELLIDOS\", I.\"NOMBRE_PROPIO\", ";

            query += " \"ND_L\" AS \"LUNES_ND\", \"ND_M\" AS \"MARTES_ND\", \"ND_X\" AS \"MIERCOLES_ND\", \"ND_J\" AS \"JUEVES_ND\", \"ND_V\" AS \"VIERNES_ND\", \"ND_S\" AS \"SABADO_ND\"";

            if (m_str != string.Empty)
            {
                query += ",(" + m_str + ") AS \"LUNES_M\" " +
                         ",(" + m_str.Replace("L", "M") + ") AS \"MARTES_M\" " +
                         ",(" + m_str.Replace("L", "X") + ") AS \"MIERCOLES_M\" " +
                         ",(" + m_str.Replace("L", "J") + ") AS \"JUEVES_M\" " +
                         ",(" + m_str.Replace("L", "V") + ") AS \"VIERNES_M\" ";
            }
            else
            {
                query += ", FALSE AS \"LUNES_M\" " +
                         ", FALSE AS \"MARTES_M\" " +
                         ", FALSE AS \"MIERCOLES_M\" " +
                         ", FALSE AS \"JUEVES_M\" " +
                         ", FALSE AS \"VIERNES_M\" ";
            }

            if (ms_str != string.Empty)
            {
                query += ",(" + ms_str + ") AS \"SABADO_M\" ";
            }
            else
            {
                query += ", FALSE AS \"SABADO_M\" ";
            }

            if (t1_str != string.Empty)
            {
                query += ",(" + t1_str + ") AS \"LUNES_T1\" " +
                         ",(" + t1_str.Replace("L", "M") + ") AS \"MARTES_T1\" " +
                         ",(" + t1_str.Replace("L", "X") + ") AS \"MIERCOLES_T1\" " +
                         ",(" + t1_str.Replace("L", "J") + ") AS \"JUEVES_T1\" " +
                         ",(" + t1_str.Replace("L", "V") + ") AS \"VIERNES_T1\" ";
            }
            else
            {
                query += ", FALSE AS \"LUNES_T1\" " +
                         ", FALSE AS \"MARTES_T1\" " +
                         ", FALSE AS \"MIERCOLES_T1\" " +
                         ", FALSE AS \"JUEVES_T1\" " +
                         ", FALSE AS \"VIERNES_T1\" ";
            }

            if (t1s_str != string.Empty)
            {
                query += ",(" + t1s_str + ") AS \"SABADO_T1\" ";
            }
            else
            {
                query += ", FALSE AS \"SABADO_T1\" ";
            }

            if (t2_str != string.Empty)
            {
                query += ",(" + t2_str + ") AS \"LUNES_T2\" " +
                         ",(" + t2_str.Replace("L", "M") + ") AS \"MARTES_T2\" " +
                         ",(" + t2_str.Replace("L", "X") + ") AS \"MIERCOLES_T2\" " +
                         ",(" + t2_str.Replace("L", "J") + ") AS \"JUEVES_T2\" " +
                         ",(" + t2_str.Replace("L", "V") + ") AS \"VIERNES_T2\" ";
            }
            else
            {
                query += ", FALSE AS \"LUNES_T2\" " +
                         ", FALSE AS \"MARTES_T2\" " +
                         ", FALSE AS \"MIERCOLES_T2\" " +
                         ", FALSE AS \"JUEVES_T2\" " +
                         ", FALSE AS \"VIERNES_T2\" ";
            }

            if (t2s_str != string.Empty)
            {
                query += ",(" + t2s_str + ") AS \"SABADO_T2\" ";
            }
            else
            {
                query += ", FALSE AS \"SABADO_T2\" ";
            }

            query += "FROM " + empleado + " AS I " +
                     "INNER JOIN " + disponibilidad + " AS D ON (D.\"OID_INSTRUCTOR\" = I.\"OID\") " +
                     "WHERE D.\"FECHA_INICIO\" = '" + fecha + "' " +
                     "AND (";

            for (long i = m_ini - 1; i < t2_fin; i++)
            {
                query += "\"L" + i.ToString() + "\" OR ";
                query += "\"M" + i.ToString() + "\" OR ";
                query += "\"X" + i.ToString() + "\" OR ";
                query += "\"J" + i.ToString() + "\" OR ";
                query += "\"V" + i.ToString() + "\" OR ";
            }

            for (long i = m_ini - 1; i < 5; i++)
            {
                query += "\"S" + i.ToString() + "\" OR ";
            }

            query += "\"L8AM\" OR \"M8AM\" OR \"X8AM\" OR \"J8AM\" OR \"V8AM\" OR \"ND_L\" OR \"ND_M\" OR \"ND_X\" OR \"ND_J\" OR \"ND_V\" OR \"ND_S\") ";
            if (autorizados)
            {
                query += "AND I.\"MTOE\" = 'TRUE' ";
            }


            if (all)
            {
                query +=
                    "UNION " +
                    "SELECT DISTINCT I.\"OID\", I.\"APELLIDOS\", I.\"NOMBRE_PROPIO\", ";

                query += " \"ND_L\" AS \"LUNES_ND\", \"ND_M\" AS \"MARTES_ND\", \"ND_X\" AS \"MIERCOLES_ND\", \"ND_J\" AS \"JUEVES_ND\", \"ND_V\" AS \"VIERNES_ND\", \"ND_S\" AS \"SABADO_ND\"";

                if (m_str != string.Empty)
                {
                    query += ",(" + m_str + ") AS \"LUNES_M\" " +
                             ",(" + m_str.Replace("L", "M") + ") AS \"MARTES_M\" " +
                             ",(" + m_str.Replace("L", "X") + ") AS \"MIERCOLES_M\" " +
                             ",(" + m_str.Replace("L", "J") + ") AS \"JUEVES_M\" " +
                             ",(" + m_str.Replace("L", "V") + ") AS \"VIERNES_M\" ";
                }
                else
                {
                    query += ", FALSE AS \"LUNES_M\" " +
                             ", FALSE AS \"MARTES_M\" " +
                             ", FALSE AS \"MIERCOLES_M\" " +
                             ", FALSE AS \"JUEVES_M\" " +
                             ", FALSE AS \"VIERNES_M\" ";
                }

                if (ms_str != string.Empty)
                {
                    query += ",(" + ms_str + ") AS \"SABADO_M\" ";
                }
                else
                {
                    query += ", FALSE AS \"SABADO_M\" ";
                }

                if (t1_str != string.Empty)
                {
                    query += ",(" + t1_str + ") AS \"LUNES_T1\" " +
                             ",(" + t1_str.Replace("L", "M") + ") AS \"MARTES_T1\" " +
                             ",(" + t1_str.Replace("L", "X") + ") AS \"MIERCOLES_T1\" " +
                             ",(" + t1_str.Replace("L", "J") + ") AS \"JUEVES_T1\" " +
                             ",(" + t1_str.Replace("L", "V") + ") AS \"VIERNES_T1\" ";
                }
                else
                {
                    query += ", FALSE AS \"LUNES_T1\" " +
                             ", FALSE AS \"MARTES_T1\" " +
                             ", FALSE AS \"MIERCOLES_T1\" " +
                             ", FALSE AS \"JUEVES_T1\" " +
                             ", FALSE AS \"VIERNES_T1\" ";
                }

                if (t1s_str != string.Empty)
                {
                    query += ",(" + t1s_str + ") AS \"SABADO_T1\" ";
                }
                else
                {
                    query += ", FALSE AS \"SABADO_T1\" ";
                }

                if (t2_str != string.Empty)
                {
                    query += ",(" + t2_str + ") AS \"LUNES_T2\" " +
                             ",(" + t2_str.Replace("L", "M") + ") AS \"MARTES_T2\" " +
                             ",(" + t2_str.Replace("L", "X") + ") AS \"MIERCOLES_T2\" " +
                             ",(" + t2_str.Replace("L", "J") + ") AS \"JUEVES_T2\" " +
                             ",(" + t2_str.Replace("L", "V") + ") AS \"VIERNES_T2\" ";
                }
                else
                {
                    query += ", FALSE AS \"LUNES_T2\" " +
                             ", FALSE AS \"MARTES_T2\" " +
                             ", FALSE AS \"MIERCOLES_T2\" " +
                             ", FALSE AS \"JUEVES_T2\" " +
                             ", FALSE AS \"VIERNES_T2\" ";
                }

                if (t2s_str != string.Empty)
                {
                    query += ",(" + t2s_str + ") AS \"SABADO_T2\" ";
                }
                else
                {
                    query += ", FALSE AS \"SABADO_T2\" ";
                }

                query += "FROM " + empleado + " AS I " +
                         "INNER JOIN " + disponibilidad + " AS D ON (D.\"OID_INSTRUCTOR\" = I.\"OID\") " +
                         "WHERE D.\"FECHA_INICIO\" = '" + fecha + "' " +
                         "AND NOT (";

                for (long i = m_ini - 1; i < t2_fin; i++)
                {
                    query += "\"L" + i.ToString() + "\" OR ";
                    query += "\"M" + i.ToString() + "\" OR ";
                    query += "\"X" + i.ToString() + "\" OR ";
                    query += "\"J" + i.ToString() + "\" OR ";
                    query += "\"V" + i.ToString() + "\" OR ";
                }

                for (long i = m_ini - 1; i < 5; i++)
                {
                    query += "\"S" + i.ToString() + "\" OR ";
                }

                query += "\"L8AM\" OR \"M8AM\" OR \"X8AM\" OR \"J8AM\" OR \"V8AM\" OR \"ND_L\" OR \"ND_M\" OR \"ND_X\" OR \"ND_J\" OR \"ND_V\" OR \"ND_S\") ";
                if (autorizados)
                {
                    query += "AND I.\"MTOE\" = 'TRUE' ";
                }

                query +=

                    "UNION  " +
                    "SELECT I.\"OID\", I.\"APELLIDOS\", I.\"NOMBRE_PROPIO\", " +
                    "FALSE AS \"LUNES_ND\", FALSE AS \"MARTES_ND\", FALSE AS \"MIERCOLES_ND\", FALSE AS \"JUEVES_ND\", FALSE AS \"VIERNES_ND\", FALSE AS \"SABADO_ND\", FALSE AS \"LUNES_M\", " +
                    "FALSE AS \"MARTES_M\", FALSE AS \"MIERCOLES_M\", FALSE AS \"JUEVES_M\", FALSE AS \"VIERNES_M\", " +
                    "FALSE AS \"SABADO_M\", FALSE AS \"LUNES_T1\", FALSE AS \"MARTES_T1\", FALSE AS \"MIERCOLES_T1\", " +
                    "FALSE AS \"JUEVES_T1\", FALSE AS \"VIERNES_T1\", FALSE AS \"SABADO_T1\", FALSE AS \"LUNES_T2\", " +
                    "FALSE AS \"MARTES_T2\", FALSE AS \"MIERCOLES_T2\", FALSE AS \"JUEVES_T2\", FALSE AS \"VIERNES_T2\", " +
                    "FALSE AS \"SABADO_T2\" " +
                    "FROM " + empleado + " AS I " +
                    "WHERE (I.\"ACTIVO\" = TRUE AND I.\"OID\" NOT IN (SELECT D.\"OID_INSTRUCTOR\" " +
                    "                        FROM " + disponibilidad + " AS D " +
                    "                        WHERE D.\"FECHA_INICIO\" = '" + fecha + "')) ";
                if (autorizados)
                {
                    query += "OR I.\"MTOE\" = 'FALSE' ";
                }
            }

            query += "ORDER BY \"APELLIDOS\", \"NOMBRE_PROPIO\";";

            return(query);
        }