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));
}
private Int64 GetSociosCount(filterSocio filtro)
{
var DbHelper = new DBBase(strConnection);
var sql = @"SELECT COUNT(s.id) FROM socio s {filtro_socio} and ISNULL(s.fechaBaja)";
sql = sql.Replace("{filtro_socio}", filtro.getFilterSqlSocio());
return((Int64)DbHelper.ExecuteScalar(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> > 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));
}
public List <Dictionary <string, object> > GetCrecimientoSocios(filterSocio filtro)
{
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 {filtro_socio}
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 {filtro_socio} and not ISNULL(s.fechaBaja)
GROUP BY DATE_FORMAT(s.fechaBaja, '%Y-%m')) as aux
GROUP BY aux.fecha";
sql = sql.Replace("{filtro_socio}", filtro.getFilterSqlSocio());
var tabla = DbHelper.ExecuteDataTable(sql);
return(Helper.Helper.ConvertDT(tabla));
}