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