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 Dictionary <string, object> GetRanking(FilterRanking filtro)
{
var DictResult = new Dictionary <string, object>();
DictResult.Add("encargados", this.GetEncargadosRanking(filtro)); //listo
DictResult.Add("eventos", this.GetEventosRanking(filtro)); //
DictResult.Add("contenidos", this.GetContenidosRanking(filtro)); //
DictResult.Add("general", this.GetFullRating(filtro));
return(DictResult);
}
private List <Dictionary <string, object> > GetFullRating(FilterRanking filtro)
{
return(GetGenericRating("r.ratingEvento + r.ratingEncargado + r.ratingContenido", filtro, 3));
}
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);
}
}
private List <Dictionary <string, object> > GetContenidosRanking(FilterRanking filtro)
{
return(GetGenericRating("r.ratingContenido", filtro));
}