public List <DeckSiegeModels> ListarVitoriasTimes(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.IdDeck,b.IdPlayer,h.Nome, ");
select.AppendLine("e.Id IdMonstro1, e.Nome NomeMonstro1, e.Imagem Imagem1, ");
select.AppendLine("f.Id IdMonstro2, f.Nome NomeMonstro2, f.Imagem Imagem2, ");
select.AppendLine("g.Id IdMonstro3, g.Nome NomeMonstro3, g.Imagem Imagem3, ");
select.AppendLine("sum(d.Vitoria) Vitoria ");
select.AppendLine(",(select ");
select.AppendLine("COUNT(1) ");
select.AppendLine("from dbo.SiegePlayerDefesa def ");
select.AppendLine("inner join dbo.SiegeDefenseDeckAssign ass on ass.IdSiege = def.IdSiege and ass.Base = def.Base ");
select.AppendLine("inner join dbo.SiegeDefenseDeck defd on defd.IdSiege = def.IdSiege and defd.IdPlayer = def.IdPlayer and defd.IdDeck = ass.IdDeck ");
select.AppendLine("where def.IdPlayer = b.IdPlayer ");
select.AppendLine("and ass.IdDeck = a.IdDeck ");
select.AppendLine(")AtaquesRecebidos ");
select.AppendLine("from dbo.SiegeDefenseDeckAssign a ");
select.AppendLine("inner join dbo.SiegeDefenseDeck b on b.IdSiege = a.IdSiege and b.IdDeck = a.IdDeck ");
select.AppendLine("inner join dbo.SiegeTimesDefesas c on c.IdDeck = b.Id ");
select.AppendLine("inner join dbo.SiegePlayerDefesa d on d.IdSiege = a.IdSiege and d.Base = a.Base and d.IdPlayer=b.IdPlayer");
select.AppendLine("inner join dbo.Monstro e on e.Id = c.Monstro1 ");
select.AppendLine("inner join dbo.Monstro f on f.Id = c.Monstro2 ");
select.AppendLine("inner join dbo.Monstro g on g.Id = c.Monstro3 ");
select.AppendLine("inner join dbo.Player h on h.ID = b.IdPlayer ");
select.AppendLine("where 1 = 1 ");
select.AppendLine("and d.Vitoria = 1 ");
select.AppendLine("and a.idSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda) ");
select.AppendLine("group by a.IdDeck,b.IdPlayer,h.Nome, ");
select.AppendLine("e.Id,e.Nome,e.Imagem, ");
select.AppendLine("f.Id,f.Nome,f.Imagem, ");
select.AppendLine("g.Id,g.Nome,g.Imagem ");
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
{
DeckSiegeModels objDeck;
List <Models.DeckSiegeModels> objRetorno = new List <DeckSiegeModels>();
conn.Open();
sqlCom.Connection = conn;
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read())
{
objDeck = new DeckSiegeModels();
objDeck.IdDeck = long.Parse(reader["IdDeck"].ToString());
objDeck.Monstro1 = new MonstroModels()
{
Id = int.Parse(reader["IdMonstro1"].ToString()),
Nome = reader["NomeMonstro1"].ToString(),
Imagem = reader["Imagem1"].ToString()
};
objDeck.Monstro2 = new MonstroModels()
{
Id = int.Parse(reader["IdMonstro2"].ToString()),
Nome = reader["NomeMonstro2"].ToString(),
Imagem = reader["Imagem2"].ToString()
};
objDeck.Monstro3 = new MonstroModels()
{
Id = int.Parse(reader["IdMonstro3"].ToString()),
Nome = reader["NomeMonstro3"].ToString(),
Imagem = reader["Imagem3"].ToString()
};
objDeck.Player = new PlayerModels()
{
Id = int.Parse(reader["IdPlayer"].ToString()),
Nome = reader["Nome"].ToString()
};
objDeck.Vitoria = int.Parse(reader["Vitoria"].ToString());
objDeck.AtaquesRecebidos = int.Parse(reader["AtaquesRecebidos"].ToString());
objRetorno.Add(objDeck);
}
conn.Close();
conn.Dispose();
return(objRetorno);
}
catch (Exception ex)
{
throw ex;
}
}
public List <DeckSiegeModels> ListarDecksPlayer(int idGuilda, long idSiege, int idPlayer)
{
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.Id,a.IdDeck,a.IdGuild,a.IdPlayer,pl.Nome,a.IdSiege,s.Data, ");
select.AppendLine("c.Id IdMonstro1,c.Nome NomeMonstro1,c.Imagem Imagem1, ");
select.AppendLine("d.Id IdMonstro2, d.Nome NomeMonstro2,d.Imagem Imagem2, ");
select.AppendLine("e.Id IdMonstro3, e.Nome NomeMonstro3,e.Imagem Imagem3, ");
select.AppendLine("( ");
select.AppendLine("select count(1) ");
select.AppendLine("from dbo.SiegePlayerDefesa f ");
select.AppendLine("inner join dbo.SiegeDefenseDeckAssign g on g.Base = f.Base and g.IdSiege = f.IdSiege ");
select.AppendLine("where g.IdSiege = a.IdSiege and f.IdPlayer = a.IdPlayer ");
select.AppendLine("and g.IdDeck = a.IdDeck ");
select.AppendLine("and f.Vitoria = 1 ");
select.AppendLine(")Vitoria, ");
select.AppendLine("( ");
select.AppendLine("select count(1) ");
select.AppendLine("from dbo.SiegePlayerDefesa f ");
select.AppendLine("inner join dbo.SiegeDefenseDeckAssign g on g.Base = f.Base and g.IdSiege = f.IdSiege ");
select.AppendLine("where g.IdSiege = a.IdSiege and f.IdPlayer = a.IdPlayer ");
select.AppendLine("and g.IdDeck = a.IdDeck and f.Vitoria = 2)Derrota, ");
select.AppendLine("(SELECT Stuff( ");
select.AppendLine("(SELECT N', ' + CONVERT(varchar, base) FROM dbo.SiegeDefenseDeckAssign bases where bases.IdDeck = a.IdDeck and bases.IdSiege=a.IdSiege order by bases.base FOR XML PATH(''), TYPE) ");
select.AppendLine(".value('text()[1]', 'nvarchar(max)'),1,2,N'') ");
select.AppendLine(") as BasesDefendidas ");
select.AppendLine("from dbo.SiegeDefenseDeck a ");
select.AppendLine("inner join dbo.SiegeTimesDefesas b on b.IdDeck = a.id ");
select.AppendLine("inner join dbo.Siege s on s.Id = a.IdSiege ");
select.AppendLine("inner join dbo.Player pl on pl.ID = a.IdPlayer ");
select.AppendLine("left join dbo.Monstro c on c.Id = b.Monstro1 ");
select.AppendLine("left join dbo.Monstro d on d.Id = b.Monstro2 ");
select.AppendLine("left join dbo.Monstro e on e.Id = b.Monstro3 ");
select.AppendLine("where ");
select.AppendLine("a.IdSiege = @idSiege ");
select.AppendLine("and a.IdPlayer = @IdPlayer ");
select.AppendLine("and a.idSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda) ");
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;
sqlCom.Parameters.Add(new SqlParameter("@IdPlayer", System.Data.SqlDbType.Int));
sqlCom.Parameters["@IdPlayer"].Value = idPlayer;
try
{
DeckSiegeModels objDeck;
List <Models.DeckSiegeModels> objRetorno = new List <DeckSiegeModels>();
SiegeModels objSiege = ObterSiege(idSiege);
conn.Open();
sqlCom.Connection = conn;
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read())
{
objDeck = new DeckSiegeModels();
objDeck.Id = long.Parse(reader["Id"].ToString());
objDeck.IdDeck = long.Parse(reader["IdDeck"].ToString());
objDeck.Monstro1 = new MonstroModels()
{
Id = int.Parse(reader["IdMonstro1"].ToString()),
Nome = reader["NomeMonstro1"].ToString(),
Imagem = reader["Imagem1"].ToString()
};
objDeck.Monstro2 = new MonstroModels()
{
Id = int.Parse(reader["IdMonstro2"].ToString()),
Nome = reader["NomeMonstro2"].ToString(),
Imagem = reader["Imagem2"].ToString()
};
objDeck.Monstro3 = new MonstroModels()
{
Id = int.Parse(reader["IdMonstro3"].ToString()),
Nome = reader["NomeMonstro3"].ToString(),
Imagem = reader["Imagem3"].ToString()
};
objDeck.Player = new PlayerModels()
{
Id = int.Parse(reader["IdPlayer"].ToString()),
Nome = reader["Nome"].ToString()
};
objDeck.Siege = objSiege;
objDeck.Vitoria = int.Parse(reader["Vitoria"].ToString());
objDeck.Derrota = int.Parse(reader["Derrota"].ToString());
objDeck.BasesDefendidas = reader["BasesDefendidas"].ToString();
objRetorno.Add(objDeck);
}
conn.Close();
conn.Dispose();
return(objRetorno);
}
catch (Exception ex)
{
throw ex;
}
}
