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