public List <Dictionary <string, object> > GetAsistenciasPorTipoEvento(FilterDateRange rango)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"
SELECT aux.nombre,SUM(aux.asistencias) AS 'asistencias',SUM(aux.inscriptos) AS 'inscriptos' FROM
(SELECT
t.nombre,
0 AS 'asistencias',
COUNT(i.id) AS inscriptos
FROM
tipoevento t
INNER JOIN evento e ON t.id = e.IdTipoEvento
INNER JOIN detalleevento de ON e.id = de.IdEvento
INNER JOIN inscripcion i ON i.idEvento = e.id
WHERE de.asistencia=1 AND {filterRangeDate(rango, "de.fechaDesde")}
GROUP BY
t.nombre
UNION ALL
SELECT
t.nombre,
COUNT(a.id) AS asistencias,
0 AS 'inscriptos'
FROM
tipoevento t
INNER JOIN evento e ON t.id = e.IdTipoEvento
INNER JOIN detalleevento de ON e.id = de.IdEvento
LEFT JOIN asistencia a ON a.idDetalleEvento = de.id
WHERE de.asistencia=1 AND {filterRangeDate(rango, "de.fechaDesde")}
GROUP BY t.nombre) AS aux GROUP BY nombre
";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetIngresosEnElTiempo()
{
var DbHelper = new DBBase(strConnection);
var sql = @"
SELECT aux.fecha,SUM(aux.matriculas) as 'matriculas',SUM(aux.eventos) as 'eventos' from (
SELECT
SUM(pago) AS 'matriculas',
0 as 'eventos',
DATE_FORMAT(fechaPago, '%Y-%m') as 'fecha'
FROM
matriculaxsocios group by fecha
UNION ALL
SELECT
0 AS 'matriculas',
SUM(monto) as 'eventos',
DATE_FORMAT(fechaCobro, '%Y-%m') as 'fecha'
FROM pago WHERE estaPagado = 1 group by fecha ) as aux group by fecha order by fecha
";
var tabla = DbHelper.ExecuteDataTable(sql);
return(Helper.Helper.ConvertDT(tabla));
}
private List <Dictionary <string, object> > GetEncargadosRanking(FilterRanking filtro)
{
if (filtro.tipoEvento == 0)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT Concat(en.nombre,' ',en.apellido) as 'nombre',
SUM(r.ratingEncargado)/COUNT(r.id) as 'stars',
COUNT(r.id) as 'cantidadVotos'
FROM ratingevento r, evento e, encargado en where r.idEvento = e.id AND e.idEncargado = en.id AND e.gratuito={filtro.gratuito}
group by CONCAT(en.nombre,' ',en.apellido) order by stars desc";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
else
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT Concat(en.nombre,' ',en.apellido) as 'nombre',
SUM(r.ratingEncargado)/COUNT(r.id) as 'stars',
COUNT(r.id) as 'cantidadVotos'
FROM ratingevento r, evento e, encargado en where r.idEvento = e.id AND e.idEncargado = en.id AND e.gratuito={filtro.gratuito} AND e.idTipoEvento={filtro.tipoEvento}
group by idEvento order by stars desc";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
}
public DataTable ExecuteDataTable(string consulta)
{
var DbHelper = new DBBase(strConnection);
var tabla = DbHelper.ExecuteDataTable(consulta);
return(tabla);
}
public List <Dictionary <string, object> > GetEventosPorTipo()
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT t.nombre, count(e.id) as cantidad FROM tipoevento t, evento e where t.id = e.idTipoEvento group by t.id";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetTagsPorSocios(filterSocio filtro)
{
var DbHelper = new DBBase(strConnection);
var sql = String.Format(@"SELECT
ts.Tag_id as 'id',
t.nombre,
COUNT(ts.Tag_id) as 'cantidad'
FROM
{0} ts INNER JOIN tags t on t.id = ts.Tag_id
WHERE
ts.Socio_id IN (
SELECT
s.id AS 'Socio_id'
FROM
socio s {1}
)
GROUP BY
ts.Tag_id",
"sociotags",
filtro.getFilterSqlSocio());
var tabla = DbHelper.ExecuteDataTable(sql);
return(Helper.Helper.ConvertDT(tabla));
}
public List <Dictionary <string, object> > GetAsistenciasPorEspacio()
{
var DbHelper = new DBBase(strConnection);
var sql = @"SELECT e.nombre , COUNT(asis.id) AS 'asistencias' FROM espaciocomun e , detalleevento det, asistencia asis
WHERE asis.idDetalleEvento = det.id AND det.idEspacio = e.id AND det.asistencia = 1 GROUP BY e.nombre";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetSociosMasActivos(FilterDateRange rango)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT CONCAT(soc.nombre, ',' , soc.apellido) AS Nombre, COUNT(DISTINCT det.idEvento) AS Eventos, YEAR(CURDATE()) - YEAR(soc.fechaNacimiento) AS Edad, soc.estadoCivil AS Civil, COUNT(soc.id) AS asistencias
FROM asistencia AS asis, socio AS soc, detalleevento det
WHERE asis.idSocio = soc.id AND asis.idDetalleEvento = det.id AND {filterRangeDate(rango, "asis.fechaAsistencia")} GROUP BY CONCAT(soc.nombre, ',', soc.apellido) order by asistencias desc LIMIT 10";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetIngresosPorTipo()
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT 'matriculas' as nombre, SUM(pago) AS monto FROM matriculaxsocios
union all
SELECT 'eventos' as nombre, SUM(monto) AS monto FROM pago where estaPagado = 1 ";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
// public List<Dictionary<string, object>> GetIngresosPorTipoEvento(FilterDateRange rango)
// {
// var DbHelper = new DBBase(strConnection);
// var sql = $@"
// SELECT
// SUM(p.monto) AS monto,
// t.nombre
// FROM pago p
// inner join inscripcion i on i.id = p.idInscripcion
// inner join evento e on e.id = i.idEvento
// inner join tipoevento t on t.id = e.idTipoEvento
// WHERE
// estaPagado = 1 AND {filterRangeDate(rango, "fechaCobro")}
// GROUP BY
// e.idTipoEvento
//";
// return Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql));
// }
public List <Dictionary <string, object> > GetIngresosPorTipo(FilterDateRange rango)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT 'Matriculas' as nombre, SUM(pago) AS monto, COUNT(pago) AS cantidad FROM matriculaxsocios
where {filterRangeDate(rango, "fechaPago")}
union all
SELECT 'Eventos' as nombre, SUM(monto) AS monto, COUNT(monto) FROM pago where estaPagado = 1 AND {filterRangeDate(rango, "fechaCobro")}";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetSocios(filterSocio filtro)
{
var DbHelper = new DBBase(strConnection);
var sql = @"SELECT s.* FROM socio s {filtro_socio} and ISNULL(s.fechaBaja)";
sql = sql.Replace("{filtro_socio}", filtro.getFilterSqlSocio());
var tabla = DbHelper.ExecuteDataTable(sql);
return(Helper.Helper.ConvertDT(tabla));
}
public List <Dictionary <string, object> > GetInscriptosPorTipoEvento()
{
var sql = $@"select t.nombre, COUNT(i.id) as 'cantidad' from inscripcion i
inner join evento e on e.id = i.IdEvento
inner join tipoevento t on t.id = e.IdTipoEvento
where i.fechaBaja is null
group by t.nombre";
var DbHelper = new DBBase(strConnection);
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetSociosPorEdad(filterSocio filtro)
{
var DbHelper = new DBBase(strConnection);
var sql = @"select TIMESTAMPDIFF(YEAR, s.fechaNacimiento, CURDATE()) as 'edad' ,Count(s.id) as 'count'
FROM socio s {filtro_socio} and ISNULL(s.fechaBaja) GROUP BY TIMESTAMPDIFF(YEAR, s.fechaNacimiento, CURDATE())";
sql = sql.Replace("{filtro_socio}", filtro.getFilterSqlSocio());
var tabla = DbHelper.ExecuteDataTable(sql);
return(Helper.Helper.ConvertDT(tabla));
}
private List <Dictionary <string, object> > GetGenericRating(string ratingColName, int factor = 1)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT e.nombre,
SUM({ratingColName})/(COUNT(r.id)*{factor}) as 'stars',
COUNT(r.id) as 'cantidadVotos'
FROM ratingevento r, evento e where r.idEvento = e.id
group by idEvento order by stars desc";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetSociosPorEdad(FilterDateRange rango)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"select TIMESTAMPDIFF(YEAR, s.fechaNacimiento, CURDATE()) as 'edad' ,Count(s.id) as 'count'
FROM socio s where {filterRangeDate(rango, "s.fechaCreacion")} and ISNULL(s.fechaBaja) and s.estado='Activo' GROUP BY TIMESTAMPDIFF(YEAR, s.fechaNacimiento, CURDATE())";
//sql = sql.Replace("{filtro_socio}", filtro.getFilterSqlSocio());
var tabla = DbHelper.ExecuteDataTable(sql);
return(Helper.Helper.ConvertDT(tabla));
}
private List <Dictionary <string, object> > GetGenericRating(string ratingColName, FilterRanking filtro, int factor = 1)
{
if (filtro.tipoEvento == 0)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT e.nombre,
SUM({ratingColName})/(COUNT(r.id)*{factor}) as 'stars',
COUNT(r.id) as 'cantidadVotos',
t.nombre as 'tipoevento'
FROM ratingevento r, evento e inner join tipoevento t on t.id = e.idTipoEvento where r.idEvento = e.id AND e.gratuito={filtro.gratuito}
group by idEvento order by stars desc";
var lista = Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql));
var i = 1;
foreach (Dictionary <string, object> item in lista)
{
item.Add("orden", i);
i++;
}
return(lista);
}
else
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT e.nombre,
SUM({ratingColName})/(COUNT(r.id)*{factor}) as 'stars',
COUNT(r.id) as 'cantidadVotos',
t.nombre as 'tipoevento'
FROM ratingevento r, evento e inner join tipoevento t on t.id = e.idTipoEvento where r.idEvento = e.id AND e.gratuito={filtro.gratuito} AND e.idTipoEvento={filtro.tipoEvento}
group by idEvento order by stars desc";
var lista = Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql));
var i = 1;
foreach (Dictionary <string, object> item in lista)
{
item.Add("orden", i);
i++;
}
return(lista);
}
}
public List <Dictionary <string, object> > GetCrecimientoSocios(FilterDateRange rango)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"SELECT aux.fecha,MAX(aux.bajas) as 'bajas',MAX(aux.altas) as 'altas' from (
SELECT
DATE_FORMAT(s.fechaCreacion, '%Y-%m') as 'fecha', Count(s.id) as 'altas',0 as 'bajas'
FROM socio s where {filterRangeDate(rango, "s.fechaCreacion")}
GROUP BY DATE_FORMAT(s.fechaCreacion, '%Y-%m')
UNION ALL SELECT
DATE_FORMAT(s.fechaBaja, '%Y-%m') as 'fecha', 0 as 'altas', Count(s.id) as 'bajas'
FROM socio s where {filterRangeDate(rango, "s.fechaBaja")} and not ISNULL(s.fechaBaja)
GROUP BY DATE_FORMAT(s.fechaBaja, '%Y-%m')) as aux
GROUP BY aux.fecha order by aux.fecha";
//sql = sql.Replace("{filtro_socio}", filtro.getFilterSqlSocio());
var tabla = DbHelper.ExecuteDataTable(sql);
return(Helper.Helper.ConvertDT(tabla));
}
public List <Dictionary <string, object> > GetIngresosPorTipoEvento(FilterDateRange rango)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"
SELECT
SUM(p.monto) AS monto,
t.nombre
FROM pago p
inner join inscripcion i on i.id = p.idInscripcion
inner join evento e on e.id = i.idEvento
inner join tipoevento t on t.id = e.idTipoEvento
WHERE
estaPagado = 1 AND {filterRangeDate(rango, "fechaCobro")}
GROUP BY
e.idTipoEvento
";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetAsistenciasPorTipoEvento()
{
var DbHelper = new DBBase(strConnection);
var sql = @"
SELECT
t.nombre,
count(a.id) AS asistencias,
count(i.id) as inscriptos
FROM
tipoevento t
inner join evento e on t.id = e.IdTipoEvento
inner join detalleevento de on e.id = de.IdEvento
inner join inscripcion i on i.idEvento = e.id
left join asistencia a on a.idDetalleEvento = de.id
GROUP BY
t.nombre
";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > GetAsistenciasPorDiaDeLaSemana()
{
var DbHelper = new DBBase(strConnection);
var sql = @"
select count(id) as cantidad,
CASE
WHEN DAYOFWEEK(fechaAsistencia) = 1 THEN 'Lunes'
WHEN DAYOFWEEK(fechaAsistencia) = 2 THEN 'Martes'
WHEN DAYOFWEEK(fechaAsistencia) = 3 THEN 'Miércoles'
WHEN DAYOFWEEK(fechaAsistencia) = 4 THEN 'Jueves'
WHEN DAYOFWEEK(fechaAsistencia) = 5 THEN 'Viernes'
WHEN DAYOFWEEK(fechaAsistencia) = 6 THEN 'Sabado'
WHEN DAYOFWEEK(fechaAsistencia) = 7 THEN 'Domingo'
END as dia
from asistencia
where DAYOFWEEK(fechaAsistencia) is not null
group by DAYOFWEEK(fechaAsistencia)
";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
// SELECT e.nombre , COUNT(asis.id) AS 'asistencias' FROM espaciocomun e , detalleevento det, asistencia asis
//WHERE asis.idDetalleEvento = det.id AND det.idEspacio = e.id AND det.asistencia = 1 GROUP BY e.nombre
public List <Dictionary <string, object> > GetAsistenciasPorDiaDeLaSemana(FilterDateRange rango)
{
var DbHelper = new DBBase(strConnection);
// var sql = @"
//select count(id) as cantidad,
//CASE
//WHEN DAYOFWEEK(fechaAsistencia) = 1 THEN 'Lunes'
//WHEN DAYOFWEEK(fechaAsistencia) = 2 THEN 'Martes'
//WHEN DAYOFWEEK(fechaAsistencia) = 3 THEN 'Miércoles'
//WHEN DAYOFWEEK(fechaAsistencia) = 4 THEN 'Jueves'
//WHEN DAYOFWEEK(fechaAsistencia) = 5 THEN 'Viernes'
//WHEN DAYOFWEEK(fechaAsistencia) = 6 THEN 'Sabado'
//WHEN DAYOFWEEK(fechaAsistencia) = 7 THEN 'Domingo'
//END as dia
//from asistencia
//where DAYOFWEEK(fechaAsistencia) is not null
//group by DAYOFWEEK(fechaAsistencia)
//";
var sql = $@"SELECT COUNT(asis.id)AS cantidad, CASE WHEN DAYOFWEEK(det.fechaDesde) = 1 THEN 'Domingo' WHEN DAYOFWEEK(det.fechaDesde) = 2
THEN 'Lunes' WHEN DAYOFWEEK(det.fechaDesde) = 3 THEN 'Martes'
WHEN DAYOFWEEK(det.fechaDesde) = 4 THEN 'Miercoles'
WHEN DAYOFWEEK(det.fechaDesde) = 5 THEN 'Jueves'
WHEN DAYOFWEEK(det.fechaDesde) = 6 THEN 'Viernes'
WHEN DAYOFWEEK(det.fechaDesde) = 7 THEN 'Sabado'
END AS dia FROM asistencia AS asis, detalleevento AS det
WHERE asis.idDetalleEvento = det.id AND det.asistencia = 1 AND {filterRangeDate(rango, "det.fechaDesde")} AND DAYOFWEEK(det.fechaDesde) IS NOT NULL GROUP BY DAYOFWEEK(det.fechaDesde)";
return(Helper.Helper.ConvertDT(DbHelper.ExecuteDataTable(sql)));
}
public List <Dictionary <string, object> > CantidadIngresosEnElTiempo(FilterDateRange rango)
{
var DbHelper = new DBBase(strConnection);
var sql = $@"
SELECT SUM(aux.matriculas) as 'Matriculas',SUM(aux.eventos) as 'Eventos' from (
SELECT
COUNT(pago) AS 'matriculas',
0 as 'eventos',
DATE_FORMAT(fechaPago, '%Y-%m') as 'fecha'
FROM
matriculaxsocios where {filterRangeDate(rango, "fechaPago")}
UNION ALL
SELECT
0 AS 'matriculas',
COUNT(monto) as 'eventos',
DATE_FORMAT(fechaCobro, '%Y-%m') as 'fecha'
FROM pago WHERE estaPagado = 1 AND {filterRangeDate(rango, "fechaCobro")}
group by fecha ) as aux
";
var tabla = DbHelper.ExecuteDataTable(sql);
return(Helper.Helper.ConvertDT(tabla));
}