public JsonResult ObterResumo(int id) { int idGuilda = 0; int.TryParse(User.Identity.GetIdGuilda(), out idGuilda); Relatorios daRelatorio = new Relatorios(); ResumoGuildaModels objResumo = new ResumoGuildaModels(); objResumo.Guilda = new GuildaModels(); objResumo = daRelatorio.ListarResumoGuilda(idGuilda, id); return(Json(objResumo, JsonRequestBehavior.AllowGet)); }
public ResumoGuildaModels ListarResumoGuilda(int idGuildaAtacante, int idGuilda) { try { SqlConnection conexao = new SqlConnection(); SqlCommand command = new SqlCommand(); conexao.ConnectionString = ConfigurationManager.ConnectionStrings["DB_SW"].ToString(); StringBuilder select = new StringBuilder(); select.AppendLine("select count(a.id) QtdGVGs,g.id,g.nome "); select.AppendLine(",(select count(1) from dbo.lutas z "); select.AppendLine("where z.CodBatalhas in (select y.id from dbo.Batalhas y where y.idGuilda = @idGuilda and y.IdGuildaAtacante = @idGuildaAtacante) "); select.AppendLine("and z.MomentoVitoria = 'Win' "); select.AppendLine("and z.DataHora = (select max(c.DataHora) from dbo.Lutas c where c.CodBatalhas = z.CodBatalhas and c.MomentoVitoria = 'Win'))VitoriaGvg, "); select.AppendLine("(select count(l.MomentoVitoria) "); select.AppendLine("from dbo.lutas l "); select.AppendLine("where l.CodBatalhas in (select y.id from dbo.Batalhas y where y.idGuilda = @idGuilda and y.IdGuildaAtacante = @idGuildaAtacante) "); select.AppendLine("and not exists(select 0 from dbo.Lutas b where b.CodBatalhas = l.CodBatalhas and MomentoVitoria = 'Win') "); select.AppendLine("and l.DataHora = (select max(c.DataHora) from dbo.Lutas c where c.CodBatalhas = l.CodBatalhas) "); select.AppendLine(") DerrotaGVG, "); select.AppendLine("(select "); select.AppendLine("count(1) qtd from "); select.AppendLine("dbo.SiegeGuilda s "); select.AppendLine("inner join dbo.Guilda b on b.Id = s.IdGuilda "); select.AppendLine("where s.IdGuilda = @idGuilda "); select.AppendLine("group by s.IdGuilda) Siege, "); select.AppendLine("(select count(1) from dbo.SiegeGuilda s1 where s1.IdGuilda = g.id and posicao = 1) as Posicao1, "); select.AppendLine("(select count(1) from dbo.SiegeGuilda s2 where s2.IdGuilda = g.id and posicao = 2) as Posicao2, "); select.AppendLine("(select count(1) from dbo.SiegeGuilda s3 where s3.IdGuilda = g.id and posicao = 3) as Posicao3 "); select.AppendLine("from dbo.Guilda g "); select.AppendLine("left join dbo.Batalhas a on a.idGuilda = g.id and a.IdGuildaAtacante = @idGuildaAtacante "); select.AppendLine("where 1 = 1 "); select.AppendLine("and g.id = @idGuilda "); select.AppendLine("group by g.id,g.Nome "); command.CommandText = select.ToString(); command.CommandType = System.Data.CommandType.Text; command.Parameters.Add(new SqlParameter("@idGuildaAtacante", System.Data.SqlDbType.Int)); command.Parameters["@idGuildaAtacante"].Value = idGuildaAtacante; command.Parameters.Add(new SqlParameter("@idGuilda", System.Data.SqlDbType.Int)); command.Parameters["@idGuilda"].Value = idGuilda; ResumoGuildaModels objRetorno = new ResumoGuildaModels(); objRetorno.Guilda = new GuildaModels(); conexao.Open(); command.Connection = conexao; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { objRetorno.Guilda = new GuildaModels() { Id = long.Parse(reader["id"].ToString()), Nome = reader["Nome"].ToString() }; objRetorno.QtdGVGs = int.Parse(reader["QtdGVGs"].ToString()); objRetorno.DerrotaGVG = int.Parse(reader["DerrotaGVG"].ToString()); objRetorno.VitoriaGvg = int.Parse(reader["VitoriaGvg"].ToString()); if (!string.IsNullOrEmpty(reader["Siege"].ToString())) { objRetorno.Siege = int.Parse(reader["Siege"].ToString()); objRetorno.SiegePosicao1 = int.Parse(reader["Posicao1"].ToString()); objRetorno.SiegePosicao2 = int.Parse(reader["Posicao2"].ToString()); objRetorno.SiegePosicao3 = int.Parse(reader["Posicao3"].ToString()); } } conexao.Close(); conexao.Dispose(); return(objRetorno); } catch (Exception ex) { throw ex; } }