public List <AtaquesSiegeModels> ListarAtaques(int idGuilda) { SqlConnection conn = new SqlConnection(); SqlCommand sqlCom = new SqlCommand(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DB_SW"].ToString(); StringBuilder select = new StringBuilder(); select.AppendLine("select "); select.AppendLine("a.IdPlayer,d.Nome, "); select.AppendLine("(select count(Vitoria) from dbo.SiegeAtaques b where b.IdPlayer = a.IdPlayer and b.Vitoria = 1 and b.IdSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda)) Vitoria, "); select.AppendLine("(select count(Vitoria) from dbo.SiegeAtaques c where c.IdPlayer = a.IdPlayer and c.Vitoria = 2 and c.IdSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda)) Derrota, "); select.AppendLine("(select count(distinct idSiege) from dbo.SiegeAtaques e where e.IdPlayer = a.IdPlayer and e.IdSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda)) QtsSieges, "); select.AppendLine("(select AVG(UsedUnitCount) from dbo.SiegePlayers f where f.IdPlayer = a.IdPlayer and f.IdSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda)) MediaMonstros, "); select.AppendLine("(select sum(1) from dbo.SiegeAtaques g where g.IdPlayer = a.IdPlayer and g.IdSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda)) AtaquesRealizados "); select.AppendLine("from dbo.SiegePlayers a "); select.AppendLine("inner join dbo.Player d on d.ID = a.IdPlayer "); select.AppendLine("where 1 = 1 "); select.AppendLine("and a.idSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda) "); select.AppendLine("and d.Status = 'S'"); select.AppendLine("group by a.IdPlayer,d.Nome "); sqlCom.CommandText = select.ToString(); sqlCom.CommandType = System.Data.CommandType.Text; sqlCom.Parameters.Add(new SqlParameter("@idGuilda", System.Data.SqlDbType.Int)); sqlCom.Parameters["@idGuilda"].Value = idGuilda; try { AtaquesSiegeModels objAtaque; List <Models.AtaquesSiegeModels> objRetorno = new List <AtaquesSiegeModels>(); conn.Open(); sqlCom.Connection = conn; SqlDataReader reader = sqlCom.ExecuteReader(); while (reader.Read()) { objAtaque = new AtaquesSiegeModels(); objAtaque.Derrota = int.Parse(reader["Derrota"].ToString()); objAtaque.Player = new PlayerModels() { Id = int.Parse(reader["IdPlayer"].ToString()), Nome = reader["Nome"].ToString() }; objAtaque.Vitoria = int.Parse(reader["Vitoria"].ToString()); objAtaque.QuantidadeSieges = int.Parse(reader["QtsSieges"].ToString()); objAtaque.MediaMonstros = int.Parse(reader["MediaMonstros"].ToString()); objAtaque.AtaquesRealizados = reader["AtaquesRealizados"].ToString() == string.Empty ? 0 : int.Parse(reader["AtaquesRealizados"].ToString()); objRetorno.Add(objAtaque); } conn.Close(); conn.Dispose(); return(objRetorno); } catch (Exception ex) { throw ex; } }
public List <AtaquesSiegeModels> ListarAtaquesConsolidado(int idGuilda, long idSiege) { SqlConnection conn = new SqlConnection(); SqlCommand sqlCom = new SqlCommand(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DB_SW"].ToString(); StringBuilder select = new StringBuilder(); select.AppendLine("select "); select.AppendLine("a.idSiege,siege.Data, a.IdPlayer,c.Nome "); select.AppendLine(", (select count(Vitoria) from dbo.SiegeAtaques e where e.idSiege = a.idSiege and e.IdPlayer = a.IdPlayer and e.Vitoria = 1 ) Vitoria "); select.AppendLine(", (select count(Vitoria) from dbo.SiegeAtaques e where e.idSiege = a.idSiege and e.IdPlayer = a.IdPlayer and e.Vitoria = 2 ) Derrota "); select.AppendLine(", 30 - b.UsedUnitCount as 'MobNaoUsado' "); select.AppendLine("from dbo.SiegeAtaques a"); select.AppendLine("inner "); select.AppendLine("join dbo.SiegePlayers b on b.IdSiege = a.idSiege and b.IdPlayer = a.IdPlayer "); select.AppendLine("inner join dbo.Player c on c.ID = b.IdPlayer "); select.AppendLine("inner join dbo.SiegePlayerOponente d on d.IdSiege = a.idSiege and d.Id = a.IdPlayerOponente and d.IdGuilda = a.IdGuildaOpp "); select.AppendLine("inner join dbo.Siege siege on siege.Id = a.idSiege "); select.AppendLine("where a.idSiege = @idSiege "); select.AppendLine("and a.idSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda) "); select.AppendLine("group by a.idSiege,siege.Data,a.IdPlayer,c.Nome,b.UsedUnitCount "); select.AppendLine("order by c.Nome "); sqlCom.CommandText = select.ToString(); sqlCom.CommandType = System.Data.CommandType.Text; sqlCom.Parameters.Add(new SqlParameter("@idSiege", System.Data.SqlDbType.BigInt)); sqlCom.Parameters["@idSiege"].Value = idSiege; sqlCom.Parameters.Add(new SqlParameter("@idGuilda", System.Data.SqlDbType.Int)); sqlCom.Parameters["@idGuilda"].Value = idGuilda; SiegeModels objSiege = ObterSiege(idSiege); try { AtaquesSiegeModels objAtaque; List <Models.AtaquesSiegeModels> objRetorno = new List <AtaquesSiegeModels>(); conn.Open(); sqlCom.Connection = conn; SqlDataReader reader = sqlCom.ExecuteReader(); while (reader.Read()) { objAtaque = new AtaquesSiegeModels(); objAtaque.Derrota = int.Parse(reader["Derrota"].ToString()); objAtaque.MonstrosNaoUsados = int.Parse(reader["MobNaoUsado"].ToString()); objAtaque.Player = new PlayerModels() { Id = int.Parse(reader["IdPlayer"].ToString()), Nome = reader["Nome"].ToString() }; objAtaque.Siege = objSiege; objAtaque.Vitoria = int.Parse(reader["Vitoria"].ToString()); objRetorno.Add(objAtaque); } conn.Close(); conn.Dispose(); return(objRetorno); } catch (Exception ex) { throw ex; } }