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> > 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>> 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> > 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));
}
public string filterRangeDate(FilterDateRange rango, string nombreCampo = "fecha")
{
var filter = "1=1";
if (rango.fechaInicio > DateTime.MinValue)
{
filter += $" AND {nombreCampo} >= '{rango.fechaInicio.ToString("yyyy-MM-dd")}'";
}
if (rango.fechaFin > DateTime.MinValue)
{
filter += $" AND {nombreCampo} <= '{rango.fechaFin.ToString("yyyy-MM-dd")}'";
}
return($"({filter})");
}
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)));
}
// 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));
}
