Skip to content

Instantly share code, notes, and snippets.

@ginos173

ginos173/Siar queries.sql

Last active December 18, 2023 16:29
Show Gist options
  • Save ginos173/26a11ab5e8d808574c562ff987c78da3 to your computer and use it in GitHub Desktop.
Save ginos173/26a11ab5e8d808574c562ff987c78da3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Siar queries.sql
-- Alumnos con salon ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT CONCAT(t4.titulo, ' ', t3.seccion),
concat(t1.paterno,
' ',
t1.materno,
', ',
t1.nombres)
AS alunomb
FROM alumno t1
JOIN salonal t2 ON t1.codalu = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
JOIN grados t4 ON t3.nivel = t4.nivel AND t3.grado = t4.grado
WHERE t3.ano = @ano
ORDER BY t4.ordenacade,
t4.nivel,
t4.orden,
t3.seccion,
alunomb;
-- ALUMNOS CON DETALLE MATRICULA
SELECT
t3.nivel as 'NIVEL',
CONCAT(t4.titulo) as 'GRADO',
t3.seccion as 'SECCION',
concat(t1.paterno,
' ',
t1.materno,
', ',
t1.nombres)
AS 'APELLIDOS_Y_NOMBRES',
IF(t5.estado = 1, "Matrículado","") as 'ESTADO',
DATE(t5.fecha) as 'FECHA',
TIME(t5.fecha) as 'HORA'
FROM alumno t1
JOIN salonal t2 ON t1.codalu = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
JOIN grados t4 ON t3.nivel = t4.nivel AND t3.grado = t4.grado
join prematricula t5 on t1.codalu = t5.alucod AND t5.ano=@ano
WHERE t3.ano = @ano
ORDER BY t4.ordenacade,
t4.nivel,
t4.orden,
t3.seccion,
t1.paterno, t1.materno, t1.nombres;
-- ALUMNOS CON VACUNA COVID
SELECT
t3.nivel, t3.grado, t3.seccion,
CONCAT(t4.titulo, ' ', t3.seccion) as 'Titulo Nivel',
t1.codalu,
concat(t1.paterno,
' ',
t1.materno,
', ',
t1.nombres)
AS alunomb,
COALESCE(t1.fechnaci, t1.fechnaci,'') as 'Fecha Nacimiento',
t5.vacunado,
t5.dosis
FROM alumno t1
JOIN salonal t2 ON t1.codalu = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
JOIN grados t4 ON t3.nivel = t4.nivel AND t3.grado = t4.grado
JOIN (select s1.alucod, JSON_UNQUOTE(JSON_EXTRACT(s1.data,"$.covid.vacunado")) as vacunado, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(s1.data,"$.covid.dosis")),'') as dosis from prematricula s1
where s1.ano=@ano) t5 ON t1.codalu = t5.alucod
WHERE t3.ano = @ano AND CONCAT(t3.nivel,t3.grado) IN ('A3','A4')
ORDER BY t4.ordenacade,
t4.nivel,
t4.orden,
t3.seccion,
alunomb asc;
-- ALUMNOS en salon con fechas de matriculas:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT
t3.ano as 'anho',
CONCAT(t4.titulo, ' ', t3.seccion) as 'nivel',
t1.codalu,
concat(t1.paterno,
' ',
t1.materno,
', ',
t1.nombres)
AS alunomb,
DAY(t5.fecha) as 'Dia de matricula',
MONTH(t5.fecha) as 'Mes de matricula',
YEAR(t5.fecha) as 'Anho de matricula'
FROM alumno t1
JOIN salonal t2 ON t1.codalu = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
JOIN grados t4 ON t3.nivel = t4.nivel AND t3.grado = t4.grado
left join (select s1.alucod,s1.ano,s1.estado, s1.fecha from
( select u1.alucod,u1.ano,u1.estado, u1.fecha from prematricula_2018 u1
UNION select u2.alucod, u2.ano, u2.estado, u2.fecha from prematricula u2
) s1 where s1.fecha IS NOT NULL AND s1.estado='1') t5 ON t1.codalu = t5.alucod AND t3.ano=t5.ano
WHERE t3.ano IN ('2017','2018','2019','2020','2021')
ORDER BY
t3.ano,
t4.ordenacade,
t4.nivel,
t4.orden,
t3.seccion,
alunomb;
-- Alunos con nacimiento ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT CONCAT(t4.titulo, ' ', t3.seccion),
concat(t1.paterno,
' ',
t1.materno,
', ',
t1.nombres)
AS alunomb,
t1.fechnaci,
IF(IFNULL(t1.nacipais,'') = '', t5.pais ,t1.nacipais) as Pais,
IF(IFNULL(t1.nacidep,'') = '', t5.ciudad ,t1.nacidep) as Departamento,
IF(IFNULL(t1.naciprov,'') = '', t5.provincia ,t1.naciprov) as Provincia,
IF(IFNULL(t1.nacidist,'') = '', t5.distrito ,t1.nacidist) as Distrito
FROM alumno t1
JOIN salonal t2 ON t1.codalu = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
JOIN grados t4 ON t3.nivel = t4.nivel AND t3.grado = t4.grado
LEFT JOIN
(select s1.ubinacicod, s2.nombre as distrito, s3.nombre as provincia, s4.nombre as ciudad, s5.nombre as pais
from ubigeonaci s1
join distrito s2 on s1.discod = s2.discod
join provincia s3 on s2.provcod = s3.provcod
join ciudad s4 on s3.ciucod = s4.ciucod
join pais s5 on s4.paiscod = s5.paiscod )
t5 on t1.ubinacicod = t5.ubinacicod
WHERE t3.ano = '2019'
ORDER BY t4.ordenacade,
t4.nivel,
t4.orden,
t3.seccion,
alunomb;
-- Vehiculos ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT CONCAT(T9.titulo,' ',T8.seccion), T1.alucod, CONCAT(T6.paterno, ' ',T6.materno, ', ',T6.nombres) as nombrealu, T1.tipval as propietario, T2.tipval, T3.tipval, T4.tipval, T5.tipval
FROM
(SELECT *
FROM prematrequisito
WHERE requisito = 'V' AND tipo = '1') AS T1
JOIN (SELECT *
FROM prematrequisito
WHERE requisito = 'V' AND tipo = '2') as T2 on T1.requisito= T2.requisito AND T1.reqtipo = T2.reqtipo AND T1.ano = T2.ano AND T1.alucod = T2.alucod AND T1.propicod = T2.propicod
JOIN (SELECT *
FROM prematrequisito
WHERE requisito = 'V' AND tipo = '3') as T3 on T1.requisito= T3.requisito AND T1.reqtipo = T3.reqtipo AND T1.ano = T3.ano AND T1.alucod = T3.alucod AND T1.propicod = T3.propicod
JOIN (SELECT *
FROM prematrequisito
WHERE requisito = 'V' AND tipo = '4') as T4 on T1.requisito= T4.requisito AND T1.reqtipo = T4.reqtipo AND T1.ano = T4.ano AND T1.alucod = T4.alucod AND T1.propicod = T4.propicod
JOIN (SELECT *
FROM prematrequisito
WHERE requisito = 'V' AND tipo = '5') as T5 on T1.requisito= T5.requisito AND T1.reqtipo = T5.reqtipo AND T1.ano = T5.ano AND T1.alucod = T5.alucod AND T1.propicod = T5.propicod
JOIN alumno T6 on T1.alucod = T6.codalu
JOIN salonal T7 on T1.alucod = T7.alucod
JOIN salon T8 on T7.salcod = T8.salcod
JOIN grados T9 on T8.nivel = T9.nivel AND T8.grado = T9.grado
WHERE T8.ano='2019'
ORDER BY T9.ordenacade, T9.orden, T8.seccion , nombrealu, T6.codalu, propietario
;
-- Emergencia ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT CONCAT(T9.titulo,' ',T8.seccion), T1.alucod, CONCAT(T6.paterno, ' ',T6.materno, ', ',T6.nombres) as nombrealu, T1.tipval as persona, T2.tipval as celular, T3.tipval as Documento, T4.tipval as Casa, T5.tipval as Correo
FROM
(SELECT *
FROM prematrequisito
WHERE requisito = 'E' AND tipo = '1') AS T1
JOIN (SELECT *
FROM prematrequisito
WHERE requisito = 'E' AND tipo = '2') as T2 on T1.requisito= T2.requisito AND T1.reqtipo = T2.reqtipo AND T1.ano = T2.ano AND T1.alucod = T2.alucod AND T1.propicod = T2.propicod
JOIN (SELECT *
FROM prematrequisito
WHERE requisito = 'E' AND tipo = '3') as T3 on T1.requisito= T3.requisito AND T1.reqtipo = T3.reqtipo AND T1.ano = T3.ano AND T1.alucod = T3.alucod AND T1.propicod = T3.propicod
JOIN (SELECT *
FROM prematrequisito
WHERE requisito = 'E' AND tipo = '4') as T4 on T1.requisito= T4.requisito AND T1.reqtipo = T4.reqtipo AND T1.ano = T4.ano AND T1.alucod = T4.alucod AND T1.propicod = T4.propicod
JOIN (SELECT *
FROM prematrequisito
WHERE requisito = 'E' AND tipo = '5') as T5 on T1.requisito= T5.requisito AND T1.reqtipo = T5.reqtipo AND T1.ano = T5.ano AND T1.alucod = T5.alucod AND T1.propicod = T5.propicod
JOIN alumno T6 on T1.alucod = T6.codalu
JOIN salonal T7 on T1.alucod = T7.alucod
JOIN salon T8 on T7.salcod = T8.salcod
JOIN grados T9 on T8.nivel = T9.nivel AND T8.grado = T9.grado
WHERE T8.ano='2019'
ORDER BY T9.ordenacade, T9.orden, T8.seccion , nombrealu, T6.codalu, persona
;
-- USO REI::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
select COUNT(DISTINCT t1.percod)
from personal t1
join saloncurso t2 on t1.percod = t2.percod
join salon t3 on t2.salcod = t3.salcod
join registrogrupo t4 on t1.percod = t4.percod and t2.salcod = t4.salcod
WHERE t3.ano = 2019
-- AND t3.nivel IN ('E')
-- AND t3.nivel IN ('M')
-- AND t3.nivel IN ('L','C','A')
;
select MONTH(t6.fechaedit), COUNT(DISTINCT t1.percod)
from personal t1
join saloncurso t2 on t1.percod = t2.percod
join salon t3 on t2.salcod = t3.salcod
join registrogrupo t4 on t1.percod = t4.percod and t2.salcod = t4.salcod
join registrogruponota t5 on t4.reggrucod = t5.reggrucod
join registronota t6 on t4.reggrucod = t6.reggrucod and t5.reggrunotcod = t6.reggrunotcod
WHERE t3.ano = 2019 AND t4.tipo IN ('S','O','V') AND MONTH(t6.fechaedit) IN (1,2,3) and YEAR(t6.fechaedit) = 2019 AND IFNULL(t6.nota ,'') != ""
-- AND t3.nivel IN ('E')
-- AND t3.nivel IN ('M')
-- AND t3.nivel IN ('L','C','A')
;
select MONTH(t5.fecha), COUNT(DISTINCT t1.percod)
from personal t1
join saloncurso t2 on t1.percod = t2.percod
join salon t3 on t2.salcod = t3.salcod
join registrogrupo t4 on t1.percod = t4.percod and t2.salcod = t4.salcod
join registrogruponota t5 on t4.reggrucod = t5.reggrucod
WHERE t3.ano = 2019 AND t4.tipo IN ('G') AND MONTH(t5.fecha) IN (1,2,3) and YEAR(t5.fecha) = 2019
-- AND t3.nivel IN ('E')
-- AND t3.nivel IN ('M')
-- AND t3.nivel IN ('L','C','A')
;
-- REPORTE PERSONAL DOCENTE Y ADMINISTRATIVO CON CONTACTO:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t1.usucod,t1.nombres,
CASE
WHEN t2.cargo='PRO' THEN 'Profesor'
WHEN t2.cargo='ADM' THEN 'Administrativo'
WHEN t2.cargo='AUX' THEN 'Auxiliar'
WHEN t2.cargo='ENF' THEN 'Enfermeria'
WHEN t2.cargo='PSI' THEN 'Psicologia'
WHEN t2.cargo='CAJ' THEN 'Caja'
ELSE 'Otros'
END as 'Tipo Usuario',
t2.telefono,
GROUP_CONCAT(t4.numero separator ' , ') as 'otros numeros',
t2.direccion,
t3.nombre AS 'distrito'
FROM usuario t1
JOIN personal t2 ON t1.cod = t2.percod
LEFT JOIN
(SELECT s1.ubiresicod, s2.nombre
FROM ubigeoresi s1 JOIN distrito s2 ON s1.discod = s2.discod) t3
ON t2.ubiresicod = t3.ubiresicod
left join telefono t4 on t4.usucod = t2.percod
WHERE t1.tipo NOT IN ('F', 'A') AND t1.estado = 1 AND t2.estado = 1
group by t1.usucod
ORDER BY t1.nombres;
-- REPORTE ASISTENCIAS COLEGIO ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT CONCAT(t4.titulo, ' ', t3.seccion),
t1.codalu,
CONCAT(t1.paterno, ' ', t1.materno, ', ', t1.nombres) AS alunomb,
t5.fecha,
CASE
WHEN t5.estado = 'F' THEN 'Falta'
WHEN t5.estado = 'T' THEN 'Tardanza'
END AS asistencia,
IF(t5.tipo='J','Justificada','')
FROM alumno t1
JOIN salonal t2 ON t1.codalu = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
JOIN grados t4 ON t3.nivel = t4.nivel AND t3.grado = t4.grado
JOIN asistencia t5 On t1.codalu = t5.alucod
WHERE t3.ano = '2019' AND t3.nivel='M' AND t3.grado='3' AND t5.fecha>20190909
group by t1.codalu, t5.fecha
ORDER BY t4.ordenacade,
t4.nivel,
t4.orden,
t3.seccion,
alunomb,
t5.fecha ;
-- REPORTE ASISTENCIAS ALUMNO RETIRADO
set @anoact= 2021;
set @alucod = '003377';
set @salcod = 'SA000672';
set @fechaRetirno = 20210322;
set @alunomb := (select CONCAT( paterno,' ', materno, ', ', nombres ) FROM alumno where codalu=@alucod);
select r2.nivel, r2.grado, r2.seccion,t2.nombre as 'Curso Nombre',@alunomb as 'Alumno' , DATE(t1.fecha) as 'Fecha asis',COALESCE(t3.nota,'P') as 'asistencia' from registrogruponota t1
join curso t2 on t1.curcod = t2.curcod
join salon r2 on t1.salcod = r2.salcod
left join (select s1.alucod,s1.salcod, s1.curcod ,s1.pericod,s1.tipo,DATE(s1.fecha) as fechadia,s1.nota from registronota s1
where s1.alucod=@alucod
and YEAR(s1.fecha) = @anoact
and s1.tipo='A') t3 on t1.salcod = t3.salcod AND t1.curcod=t3.curcod and t1.pericod = t3.pericod AND DATE(t1.fecha) = DATE(t3.fechadia)
where t1.salcod=@salcod and t1.tipo='G' and t1.estado='1' and t1.fecha <= @fechaRetirno;
-- RECORD ACADEMICO POR ALUMNO ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t5.codalu,
CONCAT(t5.paterno,
' ',
t5.materno,
', ',
t5.nombres)
AS alunomb,
CONCAT(t6.titulo, ' ', t4.seccion)
AS grado,
t4.ano,
t2.nombre as Curso,
t1.periodo
AS trimestre,
t1.titulo,
t1.nota
FROM notas t1
JOIN curso t2 ON t1.curcod = t2.curcod
JOIN saloncurso t3 ON t2.curcod = t3.curcod
JOIN salon t4
ON t3.salcod = t4.salcod
AND t1.salcod = t4.salcod
AND t2.ano = t4.ano
JOIN alumno t5 ON t1.alucod = t5.codalu
JOIN grados t6 ON t4.nivel = t6.nivel AND t4.grado = t6.grado
WHERE t1.tipo = 'P'
AND t1.alucod IN (select s1.alucod from salonal s1 join salon s2 on s1.salcod = s2.salcod where s2.ano='2019' AND s2.nivel='M' and s2.grado='3' AND s2.seccion IN ('B','D'))
AND t4.ano IN ('2017', '2018', '2019')
ORDER BY alunomb,
t4.ano,
t3.orden,
t1.periodo;
-- REPORTE CARGA ACADEMICA ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SET @ano = '2019';
SET @salcods =
(SELECT GROUP_CONCAT(t1.salcod, ',')
FROM salon t1
WHERE t1.nivel IN ('M',
'L',
'C',
'A',
'T'));
-- select u1.salcod, u1.nombregrado, GROUP_CONCAT(u1.nombrecurso), u2.fechadef, u2.pericod , u2.tipogrupo, u2.tiponota, u2.modalidad, u2.cargaid, GROUP_CONCAT(u2.titugrupo), count(*) from (
SELECT u1.salcod,
u1.nombregrado,
DATE(u2.fechadef),
CASE
WHEN u2.cargaid = 'EX' THEN 'Examenes'
WHEN u2.cargaid = 'OR' THEN 'Oral / Exposiciones / Ev. Por partes'
WHEN u2.cargaid = 'TR' THEN 'Tareas'
END
AS 'Tipo Carga',
count(*)
AS cantidad,
GROUP_CONCAT(u1.nombrecurso),
GROUP_CONCAT(u2.titugrupo, ' / ')
FROM (
SELECT t1.salcod,
CONCAT(t2.titulo, ' ', t1.seccion) AS nombregrado,
t4.curcod,
t4.nombre AS nombrecurso,
t2.ordenacade,
t1.nivel,
t1.grado,
t1.seccion,
t2.orden AS ordengrad,
t3.orden AS ordencurso
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN saloncurso t3 ON t1.salcod = t3.salcod
JOIN curso t4 ON t1.ano = t4.ano AND t3.curcod = t4.curcod
WHERE t1.ano = @ano AND FIND_IN_SET(t1.salcod, @salcods)
ORDER BY t2.ordenacade,
t1.nivel,
t2.orden,
t1.seccion,
t3.orden) u1
JOIN
(SELECT y1.salcod,
y1.curcod,
CONCAT(y1.titulo, '-', y2.titulo)
AS titugrupo,
y1.tipo
AS tipogrupo,
y2.tipo
AS tiponota,
y2.modalidad,
date(y2.fecha)
AS fechadef,
y2.pericod,
CASE
WHEN y1.tipo = 'S' THEN 'EX'
WHEN y2.modalidad = 'T' THEN 'TR'
END
AS cargaid
FROM registrogrupo y1
JOIN registrogruponota y2 ON y1.reggrucod = y2.reggrucod
WHERE FIND_IN_SET(y1.salcod, @salcods)
AND y2.tipo = 'N'
AND y2.fecha != '20190101'
AND (y1.tipo = 'S' OR y2.modalidad = 'T')
UNION ALL
SELECT y1.salcod,
y1.curcod,
CONCAT(y1.titulo, '-', y2.titulo) AS titugrupo,
y1.tipo AS tipogrupo,
y2.tipo AS tiponota,
y2.modalidad,
date(y3.fechaeval) AS fechadef,
y2.pericod,
'OR' AS cargaid
FROM registrogrupo y1
JOIN registrogruponota y2 ON y1.reggrucod = y2.reggrucod
JOIN registronota y3
ON y1.reggrucod = y3.reggrucod
AND y2.reggrunotcod = y3.reggrunotcod
WHERE FIND_IN_SET(y1.salcod, @salcods)
AND y2.tipo = 'N'
AND y2.fecha != '20190101'
AND y1.tipo = 'O'
GROUP BY DATE(y3.fechaeval), y3.reggrunotcod
ORDER BY fechadef) u2
ON u1.salcod = u2.salcod AND u1.curcod = u2.curcod
WHERE u2.fechadef >= '20191125' AND u2.pericod = 3
GROUP BY u1.salcod, DATE(u2.fechadef), u2.cargaid
ORDER BY u1.ordenacade,
u1.ordengrad,
u1.seccion,
u2.fechadef,
u1.ordencurso;
-- REPORTE NOTAS NO INGRESADAS LIBRETA :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t1.salcod,
CONCAT(t2.titulo, ' ', t1.seccion),
t4.nombre,
t5.nota,
t5.tipo
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN saloncurso t3 ON t1.salcod = t3.salcod
JOIN curso t4 ON t3.curcod = t4.curcod AND t1.ano = t4.ano
JOIN notas t5 ON t1.salcod = t5.salcod AND t4.curcod = t5.curcod
WHERE t1.ano = '2019'
AND t1.nivel = 'I'
AND t5.periodo = '3'
AND IF(t5.nota IS NULL, "", t5.nota) = ""
AND t5.tipo = 'P'
GROUP BY t1.salcod, t4.curcod, t5.nota
ORDER BY t2.ordenacade,
t1.nivel,
t2.orden,
t1.seccion,
t3.orden;
-- REPORTE PRE ACTAS CERRADAS ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t1.salcod, CONCAT(t2.titulo, ' ', t1.seccion), t3.nombre, IF(t4.cerrado = '1', 'Cerrado', 'Pendiente')
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN cursogrupo t3 ON t1.salcod = t3.salcod
join notagrupo t4 on t1.salcod = t4.salcod AND t3.curgrucod = t4.curgrucod
WHERE t1.ano = '2019' AND t4.tipo='P' and t1.nivel != "I"
group by t1.salcod, t3.curgrucod, t4.cerrado
ORDER BY t2.ordenacade,
t1.nivel,
t2.orden,
t1.seccion,
t3.orden
;
-- reporte Actas cerradas con profesor
SELECT t1.salcod, CONCAT(t2.titulo, ' ', t1.seccion) as 'Nivel y grado', t3.nombre as 'grupo', t6.nombreita, CONCAT(t8.paterno,' ', t8.nombres ) as 'Profesor' , IF(t4.cerrado = '1', 'Cerrado', 'Pendiente')
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN cursogrupo t3 ON t1.salcod = t3.salcod
join notagrupo t4 on t1.salcod = t4.salcod AND t3.curgrucod = t4.curgrucod
join listcursogrupo t5 on t3.curgrucod = t5.curgrucod AND t1.salcod = t5.salcod
join curso t6 on t5.curcod = t6.curcod
join saloncurso t7 on t6.curcod = t7.curcod AND t1.salcod = t7.salcod
join personal t8 on t7.percod = t8.percod
WHERE t1.ano = '2021' AND t4.tipo='P' -- and t1.nivel != "I"
group by t1.salcod, t3.curgrucod,t6.curcod, t4.cerrado
ORDER BY t2.ordenacade,
t1.nivel,
t2.orden,
t1.seccion,
t3.orden
;
-- ALUMNOS CON NOTAS ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT CONCAT(t2.titulo,' ',t1.seccion),
CONCAT(t6.paterno,
' ',
t6.materno,
', ',
t6.nombres),
t4.nombreita,
t7.nota
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN saloncurso t3 ON t1.salcod = t3.salcod
JOIN curso t4 ON t3.curcod = t4.curcod
JOIN salonal t5 ON t1.salcod = t5.salcod
JOIN alumno t6 ON t5.alucod = t6.codalu
JOIN notas t7
ON t1.salcod = t7.salcod
AND t4.curcod = t7.curcod
AND t6.codalu = t7.alucod
WHERE t1.ano = '2021'
AND t1.nivel IN ("E")
AND t7.periodo = '2'
AND t7.tipo = 'I'
AND t6.estado NOT IN ('R')
ORDER BY t2.ordenacade,
t2.orden,
t1.seccion,
CONCAT(t6.paterno,
' ',
t6.materno,
', ',
t6.nombres);
-- MIGRACIÓN SALONES / CURSOS :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
set @oldY = '2020';
set @newY = '2021';
set @seed := (select CAST(SUBSTRING(salcod, 3) AS integer ) from salon order by salcod DESC LIMIT 1);
insert into salon(salcod,nivel,grado,seccion,ano)
(select CONCAT('SA',SUBSTR( CONCAT('000000',@seed := @seed+1),-6)),nivel,grado,seccion,@newY from salon where ano=@oldY);
insert into curso(arecod,nombre,nombreita,abreviatura,nivel,grado,ano,tipo,area)
(select arecod, nombre, nombreita, abreviatura,nivel,grado,@newY,tipo,area from curso where ano=@oldY);
-- MIGRACIÓN NOTAS LIBRETA ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
INSERT INTO notas
(salcod,curcod,alucod,periodo,orden,nota,titulo,tipo,conversion,cerrado,fecha)
select t1.salcod, t3.curcod, t4.alucod, t5.periodo, t5.orden, '', t5.titulo, t5.tipo, t5.conversion, 1, NOW()
from salon t1
join saloncurso t2 on t1.salcod = t2.salcod
join curso t3 on t2.curcod = t3.curcod
join salonal t4 on t1.salcod = t4.salcod
join (select s2.nivel, s2.grado, s2.seccion, s3.abreviatura , s1.periodo, s1.orden, s1.titulo, s1.tipo, s1.conversion
from notas s1
join salon s2 on s1.salcod=s2.salcod
join curso s3 on s1.curcod = s3.curcod
join saloncurso s4 on s1.salcod=s4.salcod and s3.curcod=s4.curcod
where s2.ano = '2018'
group by s1.salcod, s1.curcod, s1.periodo, s1.notcod,s1.orden) t5 on t1.nivel = t5.nivel AND t1.grado = t5.grado AND t1.seccion = t5.seccion AND t3.abreviatura = t5.abreviatura
where t1.ano='2019' and t1.nivel='L' and t1.grado='1' and t1.seccion='U' and t3.abreviatura='LATI';
-- CHECK SI FALTAN NOTAS ALUMNOS
-- :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
set @ano = '2022';
select t1.nivel,t1.grado,t1.seccion, t1.salcod, t3.curcod, t5.nombre , t2.alucod, t4.* from salon t1
join salonal t2 on t1.salcod = t2.salcod
join saloncurso t3 on t1.salcod = t3.salcod
join curso t5 on t3.curcod = t5.curcod
left join notas t4 on t1.salcod = t4.salcod AND t3.curcod = t4.curcod AND t2.alucod = t4.alucod
where t1.ano = @ano AND t5.tipo IN (0,1) AND t1.nivel NOT IN ('I')
and COALESCE(t4.notcod,'') = ''
group by t2.alucod;
-- MIGRACION NOTAS LIBRETA (DANTE)
-- :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
-- INSERT INTO notas (salcod,curcod,alucod,periodo,orden,nota,titulo,tipo,conversion, conversionsoar, conversionaper ,cerrado,fecha)
select t1.salcod, t3.curcod, t4.alucod, t5.periodo, t5.orden, '', t5.titulo, t5.tipo, t5.conversion, t5.conversionsoar, t5.conversionaper , 1, NOW()
from salon t1
join saloncurso t2 on t1.salcod = t2.salcod
join curso t3 on t2.curcod = t3.curcod
join salonal t4 on t1.salcod = t4.salcod
join (select s2.nivel, s2.grado, s2.seccion, s3.abreviatura , s1.periodo, s1.orden, s1.titulo, s1.tipo, s1.conversion, s1.conversionsoar, s1.conversionaper
from notas s1
join salon s2 on s1.salcod=s2.salcod
join curso s3 on s1.curcod = s3.curcod
join saloncurso s4 on s1.salcod=s4.salcod and s3.curcod=s4.curcod
where s2.ano = '2021'
group by s1.salcod, s1.curcod, s1.periodo, s1.notcod,s1.orden) t5 on t1.nivel = t5.nivel AND t1.grado = t5.grado AND t1.seccion = t5.seccion AND t3.abreviatura = t5.abreviatura
where t1.ano='2022' and t1.nivel='M' and t1.grado='1';
--MIGRACION CONVERSIONES (DANTE)
-- :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
set @anofrom:='2021';
set @anoto:='2022';
insert into conversion (ano,`key`,nota_i,nota_p,metadata,orden)
select @anoto,t2.key, t2.nota_i,t2.nota_p,t2.metadata,t2.orden
from conversion t2 where t2.ano=@anofrom;
-- ACTUALIZACION ORDENES CURSOS CUANDO YA CREADOS :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
UPDATE saloncurso t1
JOIN salon t2 ON t1.salcod = t2.salcod
JOIN curso t3 ON t1.curcod = t3.curcod
SET t1.orden =
CASE
WHEN t3.abreviatura='ITAL' THEN 1
WHEN t3.abreviatura='COMU' THEN 2
WHEN t3.abreviatura='INGL' THEN 3
WHEN t3.abreviatura='MATE' THEN 4
WHEN t3.abreviatura='SCZE' THEN 5
WHEN t3.abreviatura='STOR' THEN 6
WHEN t3.abreviatura='GEOG' THEN 7
WHEN t3.abreviatura='SCLI' THEN 8
WHEN t3.abreviatura='DPCC' THEN 9
WHEN t3.abreviatura='ARTE' THEN 10
WHEN t3.abreviatura='TECN' THEN 11
WHEN t3.abreviatura='MUSI' THEN 12
WHEN t3.abreviatura='EDFI' THEN 13
WHEN t3.abreviatura='INFO' THEN 14
WHEN t3.abreviatura='RELI' THEN 15
WHEN t3.abreviatura='TEAT' THEN 16
WHEN t3.abreviatura='COND' THEN 17
ELSE 18
END
WHERE t2.ano = '2019'
AND t3.ano = '2019'
AND t2.nivel = 'M'
AND t2.grado = '3'
;
-- CIERRE NOTAS CON EXCLUSIONES :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
UPDATE notas t1
join saloncurso t2 on t1.salcod = t2.salcod AND t1.curcod = t2.curcod
join salon t3 on t2.salcod = t3.salcod
join curso t4 on t2.curcod = t4.curcod
SET t1.cerrado='1'
where t3.ano='2019' AND t4.ano='2019' AND t1.periodo='1' AND t4.abreviatura != 'COND'
AND t3.nivel='M' AND t3.grado='1' and t3.seccion IN ('B');
-- CARGA ACADEMICA SALONES :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t1.salcod,
t2.titulo,
t1.seccion,
t4.nombre,
su1.grupotitulo,
su1.tituloeval,
CASE
WHEN su1.asgrupotipo = 'S'
THEN
'Examen'
WHEN su1.asgrupotipo = 'O'
THEN
'Oral programado / evaluado'
WHEN su1.asgrupotipo = 'V'
THEN
IF(su1.modalidad = 'T', 'Tarea', 'Practicas / otros')
END
AS Categoria,
su1.fechafin
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN saloncurso t3 ON t1.salcod = t3.salcod
JOIN curso t4 ON t3.curcod = t4.curcod AND t1.ano = t4.ano
JOIN
(SELECT s1.salcod,
s1.curcod,
s1.titulo AS grupotitulo,
s2.titulo AS tituloeval,
s1.tipo asgrupotipo,
IF(s1.tipo = 'O', s3.fechaeval, s2.fecha) AS fechafin,
s2.modalidad
FROM registrogrupo s1
JOIN registrogruponota s2
ON s1.salcod = s2.salcod
AND s1.curcod = s2.curcod
AND s1.pericod = s2.pericod
AND s1.reggrucod = s2.reggrucod
LEFT JOIN registronota s3
ON s1.salcod = s3.salcod
AND s1.curcod = s3.curcod
AND s1.pericod = s3.pericod
AND s2.reggrunotcod = s3.reggrunotcod
WHERE s1.tipo NOT IN ('G', 'U')
GROUP BY s1.salcod,
s1.curcod,
s1.reggrucod,
s2.reggrunotcod,
fechafin) AS su1
ON t1.salcod = su1.salcod AND t4.curcod = su1.curcod
WHERE t1.ano = '2019'
AND (su1.fechafin BETWEEN '2019-08-26' AND '2019-08-30')
AND t1.nivel IN ('M')
ORDER BY t2.ordenacade,
t2.orden,
t1.seccion,
t3.orden;
-- CURSOS CON PROFESOR :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t1.salcod as 'SALCOD',
CONCAT(t2.titulo, ' ', t1.seccion) as 'NIVEL',
t3.curcod as 'CURCOD',
UPPER(t3.nombre) as 'CURSO NOMBRE',
UPPER(t3.nombreita) as 'CURSO ITALIANO',
IFNULL(CONCAT(t5.paterno,
' ',
t5.materno,
', ',
t5.nombres),
'') as 'PROFESOR'
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN curso t3
ON t1.ano = t3.ano AND t1.nivel = t3.nivel AND t1.grado = t3.grado
LEFT JOIN saloncurso t4
ON t1.salcod = t4.salcod AND t3.curcod = t4.curcod
LEFT JOIN personal t5 ON t4.percod = t5.percod
WHERE t1.ano = '2020' and t3.tipo IN ('0','1')
ORDER BY t2.ordenacade,
t1.nivel,
t2.orden,
t1.seccion, t3.nombre;
-- DESPUES DE RESTAURAR BACKUP LOCAL ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
grant all on *.* to 'root'@'%' identified by '' with grant option;
-- CONSOLIDADO EXCEL ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t1.salcod,
t1.curcod,
t1.alucod,
t1.periodo,
t1.notcod,
CONCAT(t6.titulo, ' ', t4.seccion)
AS grado,
CONCAT(t5.paterno,
' ',
t5.materno,
', ',
t5.nombres)
AS alunomb,
t2.nombreita
AS Curso,
t1.titulo
AS 'Nombre nota',
t1.nota
AS 'Nota Original',
''
AS 'Nota Modificada'
FROM notas t1
JOIN curso t2 ON t1.curcod = t2.curcod
JOIN saloncurso t3 ON t2.curcod = t3.curcod
JOIN salon t4
ON t3.salcod = t4.salcod
AND t1.salcod = t4.salcod
AND t2.ano = t4.ano
JOIN alumno t5 ON t1.alucod = t5.codalu
JOIN grados t6 ON t4.nivel = t6.nivel AND t4.grado = t6.grado
WHERE t1.tipo = 'P'
AND t4.ano IN ('2020')
AND t1.periodo = 1
AND CONCAT(t4.nivel, t4.grado, t4.seccion) IN ('E2A')
ORDER BY t4.ano,
t6.ordenacade,
t6.nivel,
t6.orden,
t4.seccion,
alunomb,
t3.orden,
t1.periodo;
-- CARGAR ARCHIVO DE MODIFICACIONES ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
select * from notas_temp;
truncate notas_temp;
set @ano='2023';
Select t1.salcod,t1.curcod,t1.alucod,t1.periodo,t1.notcod,t1.nota, IF(trim(coalesce(t2.nota, '')) != '', t2.nota, t2.nota_old ) as 'nueva nota',t1.tipo from
-- UPDATE
notas t1
INNER JOIN notas_temp t2
ON ( t1.salcod = t2.salcod
AND t1.curcod = t2.curcod
AND t1.alucod = t2.alucod
AND t1.periodo = t2.periodo
AND t1.notcod = t2.notcod
-- AND coalesce(t1.nota, '') = coalesce(t2.nota_old, '')
)
INNER JOIN salon t3
ON (t1.salcod = t3.salcod AND t2.salcod = t3.salcod)
-- SET t1.nota = IF(trim(coalesce(t2.nota, '')) != '', t2.nota, t2.nota_old )
WHERE t3.ano = @ano AND (trim(coalesce(t2.nota, '')) != '' OR t1.nota != t2.nota_old) AND t1.tipo IN ('P','PP');
-- FIX DANTE:::::
select * from
-- UPDATE
notas_temp
-- set nota_old = nota
where LENGTH(nota)>5
;
select * from
-- UPDATE
notas_temp
-- set nota_old = REPLACE(nota_old,'{','')
where LENGTH(nota_old)>5 and LOCATE('{',nota_old)>0 AND SUBSTRING(nota_old,1,5)!= '{"key';
Select t1.salcod,t1.curcod,t1.alucod,t1.periodo,t1.notcod,t1.nota, t2.nota_old as 'nueva nota',t1.tipo from
-- UPDATE
notas t1
INNER JOIN notas_temp t2
ON ( t1.salcod = t2.salcod
AND t1.curcod = t2.curcod
AND t1.alucod = t2.alucod
AND t1.periodo = t2.periodo
AND t1.notcod = t2.notcod)
INNER JOIN salon t3
ON (t1.salcod = t3.salcod AND t2.salcod = t3.salcod)
-- SET t1.nota = t2.nota_old
WHERE t3.ano = @ano AND t1.tipo = 'I' AND coalesce(t2.nota_old, '') != '';
--MODIFICACIONES COMPETENCIAS
SELECT * FROM come_temp;
truncate come_temp;
SET @ano = '2023';
-- UPDATE
SELECT t1.salcod, t1.curcod, t1.alucod, t1.pericod, t1.reggrucod, t1.reggrunotcod, t1.regnotcod, t1.nota, t2.nota as nota_nueva FROM
registronota t1
JOIN come_temp t2
ON t1.salcod = t2.salcod
AND t1.curcod = t2.curcod
AND t1.alucod = t2.codalu
AND t1.pericod = t2.periodo
AND t1.reggrucod = t2.reggrucod
AND t1.reggrunotcod = t2.reggrunotcod
AND t1.regnotcod = t2.regnotcod
-- SET t1.nota = t2.nota
where COALESCE(t2.regnotcod,'') != "" AND t2.nota != t1.nota;
-- INSERT INTO registronota (reggrucod, reggrunotcod, alucod, salcod, curcod, pericod, titulo, nota, tipo, conversion, orden, estado, fecha)
select t1.reggrucod, t1.reggrunotcod, t1.codalu as alucod, t1.salcod, t1.curcod , t1.periodo as pericod, t1.nombre_nota as titulo, t1.nota, t2.tipo, t2.conversion, t2.orden, t2.estado, t2.fecha from come_temp t1
join registrogruponota t2 on t1.reggrunotcod = t2.reggrunotcod
where coalesce(t1.regnotcod,'') = '' AND COALESCE(t1.nota,'') != '';
--PARA CAMBIAR COMENTARIOS APLICADOS
set @from:= "Lavora però necessita di guida.";
set @to:="Lavora però ha bisogno di essere guidato.";
select * from
-- UPDATE
notas t1
join salon t2 on t1.salcod = t2.salcod
-- SET t1.nota = REPLACE(t1.nota, @from, @to)
where t2.ano=@ano and t1.tipo = 'I' and t1.periodo IN (2,3) and INSTR(t1.nota, @from) > 0;
-- Informe de matricula diario RAIMONDI ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t1.alucod,
CONCAT(t2.paterno, ' ', t2.materno, ', ', t2.nombres) AS 'alunomb',
t1.fecha AS 'Fecha de matricula',
t3.niveltext AS 'Nivel y Grado'
FROM prematricula t1
JOIN alumno t2 ON t1.alucod = t2.codalu
LEFT JOIN
(SELECT s1.salcod,
s1.nivel,
s1.grado,
s1.seccion,
s2.alucod,
CONCAT(s3.titulo, ' ', s1.seccion) AS 'niveltext',
s3.ordenacade,
s3.orden
FROM salon s1
JOIN salonal s2 ON s1.salcod = s2.salcod
JOIN grados s3 ON s1.nivel = s3.nivel AND s1.grado = s3.grado
WHERE s1.ano = '2021'
ORDER BY s3.ordenacade,
s1.nivel,
s3.orden,
s1.seccion) t3
ON t1.alucod = t3.alucod
WHERE DATE(t1.fecha) >= 20210101 AND t1.estado = '1'
ORDER BY t3.ordenacade,
t3.nivel,
t3.orden,
t3.seccion,
alunomb;
-- Informe de matricula DANTE :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT t1.alucod,
CONCAT(t2.paterno,
' ',
t2.materno,
', ',
t2.nombres)
AS alunomb,
t4.anoact,
t4.anoprox,
t1.fechains
FROM soda_db.prematricula t1
JOIN soda_db.alumno t2 ON t1.alucod = t2.codalu
LEFT JOIN soda_db.edumap t3 ON t1.alucod = t3.cod AND t3.tipo = 'A'
LEFT JOIN edkdante.alumno t4 ON t3.educod = t4.codalu
WHERE t1.fechains >= 20210101 AND t1.estado = 1
ORDER BY t1.fechains;
-- ALUMNOS MATRICULADOS EN EL AÑO ACTUAL PERO SIN SALON ASIGNADO:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
select t1.alucod, CONCAT( t2.paterno, ' ', t2.materno, ', ',t2.nombres ) as alunomb, t3.nivel, t3.grado, t3.seccion from prematricula t1
join alumno t2 on t1.alucod = t2.codalu
left join tmp_edusalonal t3 on t1.alucod = t3.codalu
where t1.estado='1' AND YEAR(t1.fecha) = '2021'
AND t1.alucod NOT IN (SELECT s1.alucod from salonal s1
join salon s2 on s1.salcod = s2.salcod
where s2.ano='2021');
-- ALUMNOS SIN SALON DANTE::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
select t1.fechains, CONCAT(t2.paterno,' ',t2.materno,', ',t2.nombres ) as alunomb from prematricula t1
join alumno t2 on t1.alucod = t2.codalu
where t1.ano='2021' AND t2.codalu NOT IN (
select s2.alucod from salon s1
join salonal s2 on s1.salcod = s2.salcod
where s1.ano='2021'
);
-- MENSAJERIA REPORTES
SELECT t1.fecha,
t3.nombres AS 'Remitente',
t4.nombres AS 'Destinatario',
t2.hijos,
t1.asunto,
t1.mensaje
FROM mensajegrupo t1
JOIN mensaje t2 ON t1.mengrucod = t2.mengrucod
JOIN usuario t3 ON t1.usucod = t3.usucod
JOIN usuario t4 ON t2.usucod = t4.usucod
WHERE (t1.usucod = '914' OR t2.usucod = '914')
AND t1.usucod != 'US003509'
AND RTRIM(LTRIM(COALESCE(t2.hijos,""))) IN ('','US003017')
AND t1.fecha BETWEEN 20210101 AND 20211231
ORDER BY t1.fecha DESC;
-- MENSAJERIA REPORTES CON ADJUNTOS
SELECT t1.fecha,
-- t3.nombres AS 'Remitente',
t4.nombres AS 'Destinatario',
-- t2.hijos,
t1.asunto,
t1.mensaje
, CASE WHEN t2.estado=2 THEN 'Leido en intranet y correo' WHEN t2.estado='1' THEN 'Correo' WHEN t2.estado='0' THEN 'Leido en intranet y correo' END as 'Status'
, GROUP_CONCAT(DISTINCT IF(LTRIM(RTRIM(COALESCE(t5.file,'')))='','', CONCAT('<a href=\"http://siar.pe/upload/',t5.file,'\">adjunto</a>')) ) as 'Adjuntos'
FROM mensajegrupo t1
JOIN mensaje t2 ON t1.mengrucod = t2.mengrucod
JOIN usuario t3 ON t1.usucod = t3.usucod
JOIN usuario t4 ON t2.usucod = t4.usucod
LEFT JOIN adjunto t5 on t1.mengrucod = t5.mengrucod
WHERE
t1.fecha BETWEEN 20211101 AND 20220105
AND (
-- t1.asunto LIKE '%PENSIÓN ESCOLAR Y MATRICULA 2022%'
t1.asunto LIKE '%Reenvío - Información año escolar 2022 (DU002-2020)%'
)
AND t3.tipo NOT IN ('A','F','P')
AND t4.tipo IN ('F')
group by t1.mengrucod, t2.mencod
-- group by t1.mengrucod
ORDER BY t1.fecha DESC;
/*<MATRICULA::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::>*/
-- MATRICULA COLORCAR ALUMNOS EN SALON
SET @ano = '2022';
-- SET @matris = '005558,005571';
SET @matris = (SELECT GROUP_CONCAT(codalu) from edkraimondi.matricul where estado='3' );
SET @codalus = (SELECT GROUP_CONCAT(t1.codalu) FROM alumno t1 join prematricula t2 on t1.codalu = t2.alucod WHERE FIND_IN_SET(t1.codalu, @matris) AND YEAR(t2.fecha) = @ano AND t2.estado=1 AND t1.codalu NOT IN (SELECT t2.alucod FROM salon t1 JOIN salonal t2 ON t1.salcod = t2.salcod WHERE t1.ano = @ano));
select * from alumno where FIND_IN_SET(codalu, @codalus);
/*ALUMNOS A COLOCAR EN SALON*/
-- INSERT INTO salonal(salcod, alucod, estado)
SELECT t3.salcod,
t1.codalu,
'1'
FROM alumno t1
JOIN (SELECT s1.codalu,
SUBSTRING(s1.anoprox, 3, 1) AS 'nivel',
SUBSTRING(s1.anoprox, 1, 1) AS 'grado',
(CASE WHEN SUBSTRING(s1.anoprox, 3, 1) IN ('L','A') THEN 'U' ELSE s1.aula END) AS 'seccion'
FROM edkraimondi.alumno s1) t2 ON t1.codalu = t2.codalu
JOIN salon t3
ON t3.nivel = t2.nivel AND t3.grado = t2.grado AND t3.seccion = t2.seccion AND t3.ano = @ano
JOIN prematricula t4 on t1.codalu = t4.alucod AND t4.ano = @ano
WHERE FIND_IN_SET(t1.codalu, @codalus) AND t3.ano = @ano AND t4.ano= @ano AND t4.estado='1';
/*ALUMNOS QUE HAN CAMBIADO DE SALON*/
select t1.codalu, CONCAT(t4.nivel,t4.grado, t4.seccion) as 'siar', CONCAT( SUBSTRING(t2.anoprox, 3, 1), SUBSTRING(t2.anoprox, 1, 1) , t2.aula ) as 'edk' from alumno t1
join edkraimondi.alumno t2 on t1.codalu = t2.codalu
join salonal t3 on t1.codalu = t3.alucod
join salon t4 on t3.salcod = t4.salcod and t4.ano = @ano
where FIND_IN_SET(t1.codalu, @matris) AND CONCAT(t4.nivel,t4.grado, t4.seccion) != CONCAT( SUBSTRING(t2.anoprox, 3, 1), SUBSTRING(t2.anoprox, 1, 1) , CASE WHEN SUBSTRING(t2.anoprox, 3, 1) IN ('L','A') THEN 'U' ELSE t2.aula END );
/*ALUMNOS QUE DEBERIAN ESTAR EN SALON PERO POR ALGUNA RAZON NO LO ESTÁN*/
select t1.codalu, CONCAT( t1.paterno, ' ', t1.materno, ',', t1.nombres ) as alumno , t2.anoprox, t5.titulo, t2.aula, t4.estado as 'matestado' from alumno t1
left join edkraimondi.alumno t2 on t1.codalu = t2.codalu
left join (select u1.salcod, u2.alucod from salon u1
join salonal u2 on u1.salcod = u2.salcod
where u1.ano =@ano) t3 on t1.codalu = t3.alucod
left join prematricula t4 on t1.codalu = t4.alucod AND t4.ano=@ano
left join grados t5 on SUBSTRING(t2.anoprox, 3, 1) = t5.nivel AND SUBSTRING(t2.anoprox, 1, 1) = t5.grado
where FIND_IN_SET(t1.codalu , @matris) and COALESCE(t3.salcod,'' ) ='';
/*CHEQUEO UN ALUMNO EN PARTICULAR*/
select t1.codalu, t2.estado as matonline, t3.anoprox, t3.aula, t4.estado from alumno t1
left join prematricula t2 on t1.codalu = t2.alucod AND t2.ano=@ano
left join edkraimondi.alumno t3 on t1.codalu = t3.codalu
left join edkraimondi.matricul t4 on t1.codalu = t4.codalu
where t1.codalu='004164';
/*</MATRICULA::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::>*/
/*REPORTE WONG*/
SELECT t1.codalu,
t1.dni,
t1.nombres,
t1.paterno,
t1.materno,
DATE_FORMAT(t1.fechnaci, "%d/%m/%Y") AS 'Fecha Nacimiento',
t1.sexo,
t5.titulo as 'anho',
t2.aula as 'seccion',
'' as 'ocupacion',
CONCAT(LTRIM(RTRIM(t1.direccion)),' - ',COALESCE(t7.nombre, t1.distrito ,t1.residist, '' ) ) as 'direccion'
FROM alumno t1
JOIN edkraimondi.alumno t2 ON t1.codalu = t2.codalu
JOIN familia t3 ON t1.codapo = t3.codapo
JOIN prematricula t4 ON t1.codalu = t4.alucod AND t4.ano = '2022'
JOIN grados t5 on SUBSTRING(t2.anoprox, 3, 1) = t5.nivel AND SUBSTRING(t2.anoprox, 1, 1) = t5.grado
LEFT JOIN ubigeoresi t6 on t1.ubiresicod = t6.ubiresicod
LEFT JOIN distrito t7 on t6.discod = t7.discod
where t4.estado='1'
;
/*REPORTE MATRICULADOS DESDE EDUKIT NIVELES:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
SELECT t1.alucod as CODALU,
SUBSTRING( t3.anoprox,3,1 ) as NIVEL,
SUBSTRING( t3.anoprox,1,1 ) as GRADO,
t4.titulo as 'NIVEL GRADO',
t3.aula as 'SECCION',
t2.paterno as 'PATERNO',
t2.materno as 'MATERNO',
t2.nombres as 'NOMBRES',
'Matrículado' as 'ESTADO',
DATE_FORMAT( t1.fecha, "%Y-%m-%d") as 'FECHA',
DATE_FORMAT( t1.fecha, "%H:%i:%s") as 'HORA'
FROM prematricula t1
JOIN alumno t2 ON t1.alucod = t2.codalu
join edkraimondi.alumno t3 on t2.codalu = t3.codalu
left join grados t4 on SUBSTRING( t3.anoprox,3,1 ) = t4.nivel AND SUBSTRING( t3.anoprox,1,1 ) = t4.grado
WHERE t1.ano='2022' AND t1.estado = '1'
ORDER BY t4.ordenacade, t4.orden, t2.aula ;
/*<PLANTILLA REI BASICA>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano='2022';
set @ngs = 'E2D';
set @abr = 'DANZ';
set @pericod = '1';
set @feciniclass ='2022-03-01';
select t1.salcod, t1.nivel, t1.grado, t1.seccion, t2.curcod, t2.abreviatura , t2.nombre, t2.nombreita, t3.percod from salon t1
join curso t2 on t1.nivel = t2.nivel and t1.grado=t2.grado and t1.ano = t2.ano
join saloncurso t3 on t1.salcod=t3.salcod and t2.curcod = t3.curcod
where CONCAT(t1.nivel,t1.grado,t1.seccion) = @ngs and t1.ano=@ano and t2.abreviatura=@abr;
set @saldat = (select CONCAT(t1.salcod,'|',t2.curcod,'|',t3.percod,'|', t1.nivel,'|', t1.grado,'|', t1.seccion )from salon t1
join curso t2 on t1.nivel = t2.nivel and t1.grado=t2.grado and t1.ano = t2.ano
join saloncurso t3 on t1.salcod=t3.salcod and t2.curcod = t3.curcod
where CONCAT(t1.nivel,t1.grado,t1.seccion) = @ngs and t1.ano=@ano and t2.abreviatura=@abr);
set @salcod = (select SUBSTRING_INDEX(SUBSTRING_INDEX(@saldat,'|',1),'|',-1));
set @curcod = (select SUBSTRING_INDEX(SUBSTRING_INDEX(@saldat,'|',2),'|',-1));
set @percod = (select SUBSTRING_INDEX(SUBSTRING_INDEX(@saldat,'|',3),'|',-1));
-- CHEQUEO QUE ESTÄ CREADO
select * from registrogrupo where salcod=@salcod and curcod= @curcod;
/*COLUMNA ASISTENCIA*/
INSERT INTO registrogrupo(salcod, curcod, pericod, percod, percodcom, tipo, notcodm, titulo, comentario, estado, orden, regrucod_old, mdata)
VALUES (@salcod,@curcod,@pericod,@percod,null,'A',null,'Assenze','Assenze','1','1',null,'{}');
set @reggrucod = LAST_INSERT_ID();
insert into registrogruponota (reggrucod, salcod, curcod, pericod, titulo, tipo, conversion, valor, mengrucod, peso, comentario, orden, estado, fecha, fechafin, fechaeval, modalidad, mdata) VALUES
(@reggrucod,@salcod,@curcod,@pericod,'Stato','A','1',null,null,null,'Stato',1,1,@feciniclass,null,null,null,'{}');
/*COLUMNA ARGUMENTO*/
INSERT INTO registrogrupo(salcod, curcod, pericod, percod, percodcom, tipo, notcodm, titulo, comentario, estado, orden, regrucod_old, mdata)
VALUES (@salcod,@curcod,@pericod,@percod,null,'G',null,'Argumento','Argumento','1','2',null,'{}');
/*COLUMNA LEZIONE*/
INSERT INTO registrogrupo(salcod, curcod, pericod, percod, percodcom, tipo, notcodm, titulo, comentario, estado, orden, regrucod_old, mdata)
VALUES (@salcod,@curcod,@pericod,@percod,null,'L',null,'Lezione','Lezione','1','3',null,'{}');
set @reggrucod = LAST_INSERT_ID();
insert into registrogruponota (reggrucod, salcod, curcod, pericod, titulo, tipo, conversion, valor, mengrucod, peso, comentario, orden, estado, fecha, fechafin, fechaeval, modalidad, mdata) VALUES
(@reggrucod,@salcod,@curcod,@pericod,'+','U','2',null,null,null,'+',1,1,@feciniclass,null,null,null,'{}'),
(@reggrucod,@salcod,@curcod,@pericod,'-','U','2',null,null,null,'-',2,1,@feciniclass,null,null,null,'{}');
/*</PLANTILLA REI BASICA>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<DANTE SEGURO REPORTE>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
select
t3.codalu as codigo,
t4.educod as codigo_edukit,
'15001040' as ubigeo,
'' as agrupacion_familiar,
'207578' as plan,
CASE
WHEN UPPER(LTRIM(RTRIM(t5.tipodoci))) = 'DNI' THEN '2'
WHEN UPPER(LTRIM(RTRIM(t5.tipodoci))) = 'C.EXTRANJ' THEN '4'
WHEN UPPER(LTRIM(RTRIM(t5.tipodoci))) = 'PASAPORTE' THEN '6'
END as tipo_documento,
t5.nrodocid as num_documento,
t3.paterno as apellido_paterno,
t3.materno as apellido_materno,
t3.nombres as nombres,
t5.fecnaci as fec_nacimiento,
CONCAT(t3.direccion,' - ',t3.residist) as domicilio,
'E' as profesion_ocupasion,
'' as becado,
UPPER(t5.sexo) as sexo,
'' as estado_civil,
t1s1.titulo as anho,
t1.seccion as seccion,
'' as observaciones
from salon t1
join grados t1s1 on t1.nivel = t1s1.nivel AND t1.grado = t1s1.grado
join salonal t2 on t1.salcod = t2.salcod
join alumno t3 on t2.alucod = t3.codalu
left join edumap t4 on t3.codalu = t4.cod and t4.tipo='A'
left join edkdante.alumno t5 on t4.educod = t5.codalu
where t1.ano=@ano
ORDER BY apellido_paterno,apellido_materno,nombres;
/*</DANTE SEGURO REPORTE>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*</RAIMONDI Talleres gratuitos matriculados>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
select t1.nombre as tall_nombre , CONCAT(t5.paterno,' ',t5.materno,', ',t5.nombres) as alu_nombre from conceptopago t1
join obligacionpago t2 on t1.concod=t2.concod
join cronogramapago t3 on t2.oblipagcod = t3.oblipagcod
join tallerdetalle t4 on t2.oblipagcod = t4.oblipagcod
join alumno t5 on t2.alucod = t5.codalu
where t1.ano=@ano AND t4.estado='P'
order by tall_nombre, alu_nombre
;
/*</RAIMONDI Talleres gratuitos matriculados>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI Usuarios activos y profesores con curso>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
-- Reporte anual para verificacion de accesos y bajas que se envia a recursos humanos RR.HH.
set @ano='2023';
SELECT t1.percod,
t1.paterno,
t1.materno,
t1.nombres,
t3.text AS cargo_text
FROM personal t1
JOIN usuario t2 ON t1.percod = t2.cod AND t2.tipo NOT IN ('A', 'F')
LEFT JOIN detalle t3 ON t1.cargo = t3.val AND t3.propicod = 'CAR'
WHERE t1.estado = 1
AND t2.estado = 1
AND ( COALESCE(t3.tipo, '') != 'P'
OR ( COALESCE(t3.tipo, '') = 'P'
AND t1.percod IN
(SELECT DISTINCT s2.percod
FROM salon s1
JOIN saloncurso s2 ON s1.salcod = s2.salcod
WHERE s1.ano = @ano)))
ORDER BY t1.paterno, t1.materno, t1.nombres;
/*</RAIMONDI Usuarios activos y profesores con curso>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*</RAIMONDI info egresados y retirados años pasados>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
SELECT t1.ult_ano,
t1.alucod,
CONCAT(t4.paterno, ' ', t4.materno, ' ', t4.nombres) AS alunomb,
CONCAT(t5.titulo) AS ult_grado,
t3.seccion,
t6.titular,
CONCAT (t6.padape,', ', t6.padnom) as padre,
t6.padcel as padre_celular,
t6.padmail as padre_correo,
CONCAT (t6.madape ,', ', t6.madnom ) as madre,
t6.madcel as madre_celular,
t6.madmail as madre_correo,
CONCAT (t6.apoape ,', ', t6.aponom ) as apoderado,
t6.apocel as apoderado_celular,
t6.apomail as apoderado_correo
FROM (SELECT s2.alucod, MAX(s1.ano) AS ult_ano
FROM salon s1 JOIN salonal s2 ON s1.salcod = s2.salcod
GROUP BY s2.alucod
HAVING ult_ano < @ano) t1
JOIN salonal t2 ON t1.alucod = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
JOIN alumno t4 ON t1.alucod = t4.codalu
JOIN grados t5 ON t3.nivel = t5.nivel AND t3.grado = t5.grado
JOIN familia t6 ON t4.codapo = t6.codapo
WHERE t1.ult_ano = t3.ano
ORDER BY t1.ult_ano DESC,
t5.ordenacade,
t3.nivel,
t5.orden,
alunomb;
/*</RAIMONDI info egresados y retirados años pasados>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*</RAIMONDI REI reporte comportamiento clase>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @alucod = '003381';
SELECT CONCAT(t4.paterno, ' ', t4.materno, ' ', t4.nombres) AS alu_nomb,
CONCAT(t3.nivel, t3.grado, t3.seccion) AS niv_grad,
t1.fecha,
t2.nombreita,
t1.comentario
FROM (SELECT s1.alucod,
s1.curcod,
s1.salcod,
s1.fecha,
s1.comentario
FROM registronota s1
WHERE tipo = 'C' AND alucod = @alucod
UNION
SELECT s2.alucod,
s2.curcod,
s2.salcod,
s2.fecha,
s2.comentario
FROM registronota_2022 s2
WHERE tipo = 'C' AND alucod = @alucod) t1
JOIN curso t2 ON t1.curcod = t2.curcod
JOIN salon t3 ON t1.salcod = t3.salcod
JOIN alumno t4 ON t1.alucod = t4.codalu
ORDER BY t1.fecha DESC;
/*</RAIMONDI REI reporte comportamiento clase>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI Coreeccion ordenamiento cursos>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano = '2023';
set @ano_old = '2022';
set @afecta = "A4U";
set @norden = 0;
set @salcods = (select GROUP_CONCAT(t1.salcod) from salon t1 where find_in_set(CONCAT(t1.nivel,t1.grado,t1.seccion),@afecta) and t1.ano=@ano);
set @salcods_old = (select GROUP_CONCAT(t1.salcod) from salon t1 where find_in_set(CONCAT(t1.nivel,t1.grado,t1.seccion),@afecta) and t1.ano=@ano_old);
UPDATE saloncurso u1
-- select u1.*, u2.orden_generado from saloncurso u1
JOIN (
select t1.salcod, t1.nivel,t1.grado,t1.seccion, t1.ano, t2.curcod, t2.nombreita, t2.abreviatura, t3.orden, (@norden:= @norden +1) as orden_generado from salon t1
join curso t2 on t1.nivel = t2.nivel AND t1.grado = t2.grado AND t1.ano = t2.ano
join saloncurso t3 on t1.salcod = t3.salcod AND t2.curcod = t3.curcod
where find_in_set(t1.salcod,@salcods) AND UPPER(t2.abreviatura) NOT IN ('CPTV','COND') AND t2.tipo IN (1,0) -- AND COALESCE(t4.orden,'') != ''
order by t3.orden
) u2 on u1.salcod = u2.salcod AND u1.curcod = u2.curcod
SET u1.orden = u2.orden_generado
where FIND_IN_SET(u1.salcod , @salcods)
;
UPDATE saloncurso u1
-- select u1.*, u2.orden_generado from saloncurso u1
JOIN (
select t1.salcod, t1.nivel,t1.grado,t1.seccion, t1.ano, t2.curcod, t2.nombreita, t2.abreviatura, t3.orden, (@norden:= @norden +1) as orden_generado from salon t1
join curso t2 on t1.nivel = t2.nivel AND t1.grado = t2.grado AND t1.ano = t2.ano
join saloncurso t3 on t1.salcod = t3.salcod AND t2.curcod = t3.curcod
where find_in_set(t1.salcod,@salcods) AND UPPER(t2.abreviatura) NOT IN ('CPTV','COND') AND t2.tipo NOT IN (1,0)
order by t3.orden
) u2 on u1.salcod = u2.salcod AND u1.curcod = u2.curcod
SET u1.orden = u2.orden_generado
where FIND_IN_SET(u1.salcod , @salcods)
;
UPDATE saloncurso u1
-- select u1.*, u2.orden_generado from saloncurso u1
JOIN (
select t1.salcod, t1.nivel,t1.grado,t1.seccion, t1.ano, t2.curcod, t2.nombreita, t2.abreviatura, t3.orden, (@norden:= @norden +1) as orden_generado from salon t1
join curso t2 on t1.nivel = t2.nivel AND t1.grado = t2.grado AND t1.ano = t2.ano
join saloncurso t3 on t1.salcod = t3.salcod AND t2.curcod = t3.curcod
where find_in_set(t1.salcod,@salcods) AND UPPER(t2.abreviatura) IN ('CPTV')
order by t3.orden
) u2 on u1.salcod = u2.salcod AND u1.curcod = u2.curcod
SET u1.orden = u2.orden_generado
where FIND_IN_SET(u1.salcod , @salcods)
;
UPDATE saloncurso u1
-- select u1.*, u2.orden_generado from saloncurso u1
JOIN (
select t1.salcod, t1.nivel,t1.grado,t1.seccion, t1.ano, t2.curcod, t2.nombreita, t2.abreviatura, t3.orden, (@norden:= @norden +1) as orden_generado from salon t1
join curso t2 on t1.nivel = t2.nivel AND t1.grado = t2.grado AND t1.ano = t2.ano
join saloncurso t3 on t1.salcod = t3.salcod AND t2.curcod = t3.curcod
where find_in_set(t1.salcod,@salcods) AND UPPER(t2.abreviatura) IN ('COND')
order by t3.orden
) u2 on u1.salcod = u2.salcod AND u1.curcod = u2.curcod
SET u1.orden = u2.orden_generado
where FIND_IN_SET(u1.salcod , @salcods)
;
-- COMPROBACION
select t1.salcod, t1.nivel,t1.grado,t1.seccion, t1.ano, t2.curcod, t2.nombreita, t2.abreviatura, t3.orden from salon t1
join curso t2 on t1.nivel = t2.nivel AND t1.grado = t2.grado AND t1.ano = t2.ano
join saloncurso t3 on t1.salcod = t3.salcod AND t2.curcod = t3.curcod
where find_in_set(t1.salcod,@salcods)
order by t3.orden;
;
/*</RAIMONDI Coreeccion ordenamiento cursos>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*</RAIMONDI migrar notas de una seccion a otra del mismo grado>:::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano= '2023';
set @migratefrom = 'I3A';
set @migrateto = 'I3C';
set @lleno = IF((select t1.salcod from salon t1 join notas t2 on t1.salcod=t2.salcod WHERE find_in_set(CONCAT(t1.nivel,t1.grado,t1.seccion), @migrateto) AND t1.ano=@ano LIMIT 1 ) IS NULL, FALSE, TRUE) ;
-- insert into notas (salcod,curcod,alucod,periodo,notcod,orden,nota,titulo,tipo,conversion,conversionsoar,conversionaper,cerrado,fecha)
(select u2.salcod, u1.curcod, u2.alucod, u1.periodo, u1.notcod, u1.orden, "", u1.titulo, u1.tipo, u1.conversion, u1.conversionsoar, u1.conversionaper, u1.cerrado, u1.fecha
from
(
SELECT t1.ano,t1.salcod,t1.nivel,t1.grado,t1.seccion,t2.curcod,t2.nombreita,t2.abreviatura, t4.periodo, t4.notcod, t4.orden , t4.titulo, t4.tipo, t4.conversion, t4.conversionsoar, t4.conversionaper, t4.cerrado, t4.fecha FROM
salon t1
JOIN curso t2 ON t1.nivel = t2.nivel AND t1.grado=t2.grado AND t1.ano=t2.ano
JOIN grados t3 ON t1.nivel = t3.nivel AND t1.grado = t3.grado
JOIN notas t4 ON t1.salcod = t4.salcod AND t2.curcod = t4.curcod
WHERE find_in_set(CONCAT(t1.nivel,t1.grado,t1.seccion), @migratefrom) AND t1.ano=@ano and t2.tipo IN (0,1)
GROUP BY t1.salcod, t2.curcod, t4.periodo, t4.orden
ORDER BY t3.ordenacade, t1.seccion, t3.orden, t2.nombreita, t4.periodo, t4.orden
) u1
CROSS JOIN (select t1.salcod, t2.alucod from salon t1
join salonal t2 on t1.salcod = t2.salcod
where find_in_set(CONCAT(t1.nivel,t1.grado,t1.seccion), @migrateto) AND t1.ano=@ano) u2
where @lleno = 0)
;
/*</RAIMONDI migrar notas de una seccion a otra del mismo grado>:::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI checkeo de notas creadas>:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
SET @nivs = 'M1A,M1B,M1C,M1D';
SET @ano = '2023';
SET @salcods = '';
SELECT @salcods := GROUP_CONCAT(salcod) AS salcod FROM salon WHERE ano = @ano AND FIND_IN_SET(CONCAT(nivel, grado, seccion), @nivs);
SELECT t1.salcod
,t1.nivel
,t1.grado
,t1.seccion
,t3.nombreita
,t4.orden AS curso_orden
,t5.titulo
,t5.periodo
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN curso t3 ON t1.ano = t3.ano AND t1.nivel = t3.nivel AND t1.grado = t3.grado
LEFT JOIN saloncurso t4 ON t1.salcod = t4.salcod AND t3.curcod = t4.curcod
LEFT JOIN (
SELECT
s1.salcod,
s1.curcod,
s1.periodo,
s1.notcod,
s1.orden,
GROUP_CONCAT(DISTINCT s1.nota) AS lleno,
s1.titulo,
s1.tipo,
s1.conversion,
s1.conversionsoar,
s1.conversionaper,
s1.cerrado
FROM notas s1 WHERE FIND_IN_SET(s1.salcod, @salcods) GROUP BY s1.salcod, s1.curcod, s1.periodo, s1.notcod, s1.orden, s1.tipo, s1.conversion, s1.conversionsoar, s1.conversionaper, s1.cerrado
) t5 ON t1.salcod = t5.salcod AND t3.curcod = t5.curcod
WHERE FIND_IN_SET(t1.salcod, @salcods) AND t1.ano = @ano
ORDER BY t2.ordenacade,
t2.orden,
t1.seccion,
t4.orden,
t3.nombreita,
t5.periodo,
t5.orden
;
/*</RAIMONDI checkeo de notas creadas>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI consulta pesos civica>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano='2023';
select t2.titulo, t1.nombreita, JSON_UNQUOTE( JSON_EXTRACT(t1.mdata,"$.peso_civica.T1")) as T1, JSON_UNQUOTE( JSON_EXTRACT(t1.mdata,"$.peso_civica.T2")) as T2,JSON_UNQUOTE( JSON_EXTRACT(t1.mdata,"$.peso_civica.T3")) as T3 from curso t1
join grados t2 on t1.nivel = t2.nivel AND t1.grado = t2.grado
left join (
select s1.salcod, s2.curcod , s1.nivel,s1.grado, s2.orden from salon s1
join saloncurso s2
where s1.ano=@ano
group by s2.curcod
) t3 on t1.curcod = t3.curcod
where t1.ano=@ano and t1.tipo IN ('1','0')
order by t2.ordenacade, t1.nivel, t2.orden, t3.orden , t1.nombreita
;
/*</RAIMONDI consulta pesos civica>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI discrepancias raimondi edukit>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano = '2023';
set @alucods = (SELECT GROUP_CONCAT(t1.codalu)
FROM alumno t1
JOIN salonal t2 ON t1.codalu = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
WHERE t3.ano = @ano);
set @codapos = (
select GROUP_CONCAT(DISTINCT(t1.codapo)) from alumno t1
where FIND_IN_SET(t1.codalu , @alucods)
);
SELECT t1.codapo,
UPPER(t1.titular) as titular,
UPPER(t2.titular) as titular_edk,
IF(UPPER(t1.titular) = UPPER(t2.titular), 0, 1) as titular_atencion,
/*::::::::::::::::::::::::::::::::::Padre*/
t1.padfonotra,
t2.teltrab_papa as padfonotra_edk,
IF(t1.padfonotra = t2.teltrab_papa, 0, 1) as padfonotra_atencion,
t1.padcel,
t2.celular_papa as padcel_edk,
IF(t1.padcel = t2.celular_papa, 0, 1) as padcel_atencion,
t1.padfonocasa,
t2.telef_papa as padfonocasa_edk,
IF(t1.padfonocasa = t2.telef_papa, 0, 1) as padfonocasa_atencion,
/*::::::::::::::::::::::::::::::::::Madre*/
t1.madfonotra,
t2.teltrab_mama as madfonotra_edk,
IF(t1.madfonotra = t2.teltrab_mama, 0, 1) as madfonotra_atencion,
t1.madcel,
t2.celular_mama as madcel_edk,
IF(t1.madcel = t2.celular_mama, 0, 1) as madcel_atencion,
t1.madfonocasa,
t2.telef_mama as madfonocasa_edk,
IF(t1.madfonocasa = t2.telef_mama, 0, 1) as madfonocasa_atencion,
/*::::::::::::::::::::::::::::::::::Correos*/
CONCAT(
COALESCE(s1.correo,''),";",
COALESCE(JSON_UNQUOTE(JSON_EXTRACT(s1.metadata,'$.mailnoti[0]')),''),";",
COALESCE(JSON_UNQUOTE(JSON_EXTRACT(s1.metadata,'$.mailnoti[1]')),''),";",
COALESCE(JSON_UNQUOTE(JSON_EXTRACT(s1.metadata,'$.mailnoti[2]')),'')
) as correos_raimondi,
RTRIM(LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(RTRIM(LTRIM(t2.email_papa)), ";", 1),";",-1))) as correo_p1_edk,
RTRIM(LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(RTRIM(LTRIM(t2.email_papa)), ";", 2),";",-1))) as correo_p2_edk,
RTRIM(LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(RTRIM(LTRIM(t2.email_papa)), ";", 3),";",-1))) as correo_p3_edk,
RTRIM(LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(RTRIM(LTRIM(t2.email_mama)), ";", 1),";",-1))) as correo_m1_edk,
RTRIM(LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(RTRIM(LTRIM(t2.email_mama)), ";", 2),";",-1))) as correo_m2_edk,
RTRIM(LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(RTRIM(LTRIM(t2.email_mama)), ";", 3),";",-1))) as correo_m3_edk
FROM familia t1
JOIN usuario s1 ON t1.codapo = s1.cod AND s1.tipo = 'F'
LEFT JOIN edkfam t2 ON t1.codapo = t2.codfam
WHERE FIND_IN_SET(t1.codapo, @codapos);
/*</RAIMONDI discrepancias raimondi edukit>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI estado notas publicadas>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
SELECT t1.salcod as 'SALCOD',
t1.nivel,t1.grado,t1.seccion,
CONCAT(t2.titulo, ' ', t1.seccion) as 'NIVEL',
t3.curcod as 'CURCOD',
UPPER(t3.nombre) as 'CURSO NOMBRE',
UPPER(t3.nombreita) as 'CURSO ITALIANO',
IFNULL(CONCAT(t5.paterno,
' ',
t5.materno,
', ',
t5.nombres),
'') as 'PROFESOR',
t6.pericod as 'periodo',
t6.titulo as 'grupo_titulo',
t7.titulo as 'nota_titulo',
t7.comentario as 'nota_descripcion',
t8.publicado as 'Publicado'
FROM salon t1
JOIN grados t2 ON t1.nivel = t2.nivel AND t1.grado = t2.grado
JOIN curso t3
ON t1.ano = t3.ano AND t1.nivel = t3.nivel AND t1.grado = t3.grado
LEFT JOIN saloncurso t4
ON t1.salcod = t4.salcod AND t3.curcod = t4.curcod
LEFT JOIN personal t5 ON t4.percod = t5.percod
JOIN registrogrupo t6 ON t3.curcod = t6.curcod AND t1.salcod = t6.salcod
JOIN registrogruponota t7 on t7.reggrucod = t6.reggrucod
LEFT JOIN registronota t8 on t7.reggrunotcod = t8.reggrunotcod
WHERE t1.ano = '2023' and t3.tipo IN ('0','1') and t6.tipo NOT IN ('L','A','M','G','T') AND t7.tipo NOT IN ('P')
group by t7.reggrunotcod
ORDER BY t2.ordenacade,
t1.nivel,
t2.orden,
t1.seccion,
t3.nombre,
t6.pericod,
t6.titulo,
t7.pericod,
t7.titulo
;
/*</RAIMONDI estado notas publicadas>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI notas de progreso y competencias por alumno>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano='2023';
set @codalu='003850';
set @salcod := (select t1.salcod from salon t1 join salonal t2 on t1.salcod = t2.salcod where t1.ano=@ano AND t2.alucod=@codalu);
set @pericod='2';
select @salcod;
SELECT
t6.reggrunotcod,
t2.titulo,
t1.seccion,
t4.nombreita,
t5.titulo as grupo_titulo,LTRIM(RTRIM(t6.titulo)) as nota_titulo,
t6.comentario as nota_descripcion,
t7.alucod, t7.nota, t7.comentario
,GROUP_CONCAT(t8.titulo) as CMP_enlazadas
-- , t8.titulo, t8.comentario
FROM salon t1
JOIN grados t2 on t1.nivel = t2.nivel and t1.grado=t2.grado
JOIN saloncurso t3 on t1.salcod = t3.salcod
JOIN curso t4 on t3.curcod = t4.curcod
JOIN registrogrupo t5 on t1.salcod = t5.salcod and t4.curcod = t5.curcod
JOIN registrogruponota t6 on t5.reggrucod = t6.reggrucod
LEFT JOIN registronota t7 on t6.reggrunotcod = t7.reggrunotcod AND t7.alucod=@codalu
LEFT JOIN (
select R1.reggrunotcod as ori, R1.titulo, R1.comentario, JSON_UNQUOTE(JSON_EXTRACT(R1.mdata,CONCAT('$.compe[',R2.ind,']'))) as reggrunotcod, R1.mdata
from registrogruponota R1
JOIN (
Select 0 as ind UNION
select 1 UNION
select 2 UNION
select 3 UNION
select 4 UNION
select 5 UNION
select 6 UNION
select 7 UNION
select 8 UNION
select 9
) R2 ON JSON_UNQUOTE(JSON_EXTRACT(R1.mdata,CONCAT('$.compe[',R2.ind,']'))) IS NOT NULL
where FIND_IN_SET(R1.salcod, @salcod) and R1.pericod=@pericod
) t8 on t6.reggrunotcod = t8.reggrunotcod
WHERE FIND_IN_SET(t1.salcod, @salcod) AND t5.pericod=@pericod AND COALESCE(t7.alucod,'') IN (@codalu,'') AND t5.tipo NOT IN ('A','L','G','T','C') AND t6.tipo NOT IN ('P')
GROUP BY t6.reggrunotcod
ORDER BY t2.ordenacade, t1.nivel, t2.orden, t1.seccion, t3.orden, t5.orden, t6.orden
;
/*</RAIMONDI notas de progreso y competencias por alumno>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI reporte fotos matricula>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano='2023';
SELECT
t3.nivel, t3.grado, t3.seccion,
CONCAT(t4.titulo, ' ', t3.seccion) as 'Titulo Nivel',
t1.codalu,
concat(t1.paterno,
' ',
t1.materno,
', ',
t1.nombres)
AS alunomb,
COALESCE(t1.fechnaci, t1.fechnaci,'') as 'Fecha Nacimiento',
IF(t5.foto='1', 'Autoriza', 'No autoriza') as foto,
CONCAT("http://siar.pe/filematricula/autorizacion",t5.matricod,".pdf") as archivo
FROM alumno t1
JOIN salonal t2 ON t1.codalu = t2.alucod
JOIN salon t3 ON t2.salcod = t3.salcod
JOIN grados t4 ON t3.nivel = t4.nivel AND t3.grado = t4.grado
JOIN (select s1.alucod, s1.stdfoto as foto, s1.matricod from prematricula s1
where s1.ano=@ano) t5 ON t1.codalu = t5.alucod
WHERE t3.ano = @ano and t2.estado='1'
ORDER BY t4.ordenacade,
t4.nivel,
t4.orden,
t3.seccion,
alunomb asc;
/*</RAIMONDI reporte fotos matricula>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*<RAIMONDI reporte notas civica>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano='2023';
set @ngs = 'A1U';
set @pericod = '2';
SELECT @salcods := GROUP_CONCAT(salcod) AS salcod FROM salon WHERE ano = @ano AND FIND_IN_SET(CONCAT(nivel, grado, seccion), @ngs);
select
t1.salcod, t1.nivel, t1.grado, t1.seccion, t4.curcod, t5.notcod , t5.alucod, t2.titulo as nivel_titulo
, CONCAT(t6.paterno, ' ',t6.materno,', ', t6.nombres ) as alunomb
, t4.nombreita, t4.abreviatura, IF(t4.abreviatura='EDCV','Calculado',COALESCE(JSON_EXTRACT(t4.mdata,CONCAT('$.peso_civica.T',@pericod)),'')) as peso
, t5.nota
from salon t1
join grados t2 on t1.nivel = t2.nivel and t1.grado = t2.grado
join saloncurso t3 on t1.salcod = t3.salcod
join curso t4 on t3.curcod = t4.curcod
join notas t5 on t1.salcod = t5.salcod and t4.curcod = t5.curcod and t5.periodo=@pericod
join alumno t6 on t5.alucod = t6.codalu
where FIND_IN_SET(t1.salcod, @salcods) AND ( (COALESCE(JSON_EXTRACT(t4.mdata,CONCAT('$.peso_civica.T',@pericod)),'') != '' and t5.titulo='Civica') OR (t4.abreviatura = 'EDCV' AND t5.tipo='P'))
order by t2.ordenacade, t2.nivel, t2.orden, alunomb, t3.orden, t4.nombreita
;
/*</RAIMONDI reporte notas civica>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*</RAIMONDI comprobacion calculo civica por alumno>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
set @ano='2023';
-- set @alucods= (select GROUP_CONCAT(t2.alucod) from salon t1 join salonal t2 on t1.salcod = t2.salcod where t1.ano=@ano and CONCAT(t1.nivel, t1.grado) IN ('E5') );
set @alucods='005387,004311,004294,005021,005503,005317,004275,005015';
set @periodo = '3';
select t3.nivel,t3.grado,t3.seccion, t6.salcod,t6.curcod,t6.alucod,t6.periodo,t6.notcod,t6.orden, CONCAT(t5.paterno,' ',t5.materno,', ',t5.nombres) as alunomb
, t1.nombreita as cursonomb, JSON_EXTRACT(t1.mdata,CONCAT('$.peso_civica.T',@periodo)) as peso_civica, t6.titulo as notanomb, t6.nota
from curso t1
join saloncurso t2 on t1.curcod = t2.curcod
join salon t3 on t2.salcod = t3.salcod
join salonal t4 on t3.salcod = t4.salcod
join alumno t5 on t4.alucod=t5.codalu
left join notas t6 on t6.salcod = t2.salcod AND t6.curcod = t1.curcod AND t6.alucod = t4.alucod AND t6.periodo=@periodo AND t6.titulo='Civica'
where t1.ano=@ano AND JSON_CONTAINS_PATH(t1.mdata,'one',CONCAT('$.peso_civica.T',@periodo)) AND find_in_set(t4.alucod ,@alucods)
ORDER BY t3.nivel,t3.grado,t3.seccion, alunomb, t2.orden, t6.orden
;
/*</RAIMONDI comprobacion calculo civica por alumno>::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment