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);
}
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);
}
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);
}
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);
}
public static void UpgradeSettings()
{
ModulePrincipal.UpgradeSettings();
}
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);
}
