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)); }