public List <DefesaSiegeModels> ListarDefesas(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,b.nome, ");
select.AppendLine("(select count(Vitoria) from dbo.SiegePlayerDefesa 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.SiegePlayerDefesa b where b.IdPlayer = a.IdPlayer and b.Vitoria = 2 and b.IdSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda)) Derrota ");
select.AppendLine("from dbo.SiegePlayers a ");
select.AppendLine("inner join dbo.Player b on b.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 b.Status = 'S'");
select.AppendLine("group by a.IdPlayer,b.nome ");
select.AppendLine("order by 3 desc ");
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
{
DefesaSiegeModels objDefesa;
List <Models.DefesaSiegeModels> objRetorno = new List <DefesaSiegeModels>();
conn.Open();
sqlCom.Connection = conn;
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read())
{
objDefesa = new DefesaSiegeModels();
objDefesa.Derrota = int.Parse(reader["Derrota"].ToString());
objDefesa.Player = new PlayerModels()
{
Id = int.Parse(reader["IdPlayer"].ToString()),
Nome = reader["Nome"].ToString()
};
objDefesa.Vitoria = int.Parse(reader["Vitoria"].ToString());
objRetorno.Add(objDefesa);
}
conn.Close();
conn.Dispose();
return(objRetorno);
}
catch (Exception ex)
{
throw ex;
}
}
public List <DefesaSiegeModels> ListarDefesasConsolidado(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,a.IdPlayer, d.Nome ");
select.AppendLine(", (select count(Vitoria) from dbo.SiegePlayerDefesa e where e.idSiege = a.idSiege and e.IdPlayer = a.IdPlayer and e.Vitoria = 1 ) Vitoria ");
select.AppendLine(", (select count(Vitoria) from dbo.SiegePlayerDefesa e where e.idSiege = a.idSiege and e.IdPlayer = a.IdPlayer and e.Vitoria = 2 ) Derrota ");
select.AppendLine("from dbo.SiegePlayerDefesa a ");
select.AppendLine("inner join dbo.SiegePlayers c on c.IdPlayer = a.IdPlayer and c.IdSiege = a.IdSiege ");
select.AppendLine("inner join dbo.Player d on d.ID = c.IdPlayer ");
select.AppendLine("where ");
select.AppendLine("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,a.IdPlayer, d.Nome ");
select.AppendLine("order by 3 ");
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
{
DefesaSiegeModels objDefesa;
List <Models.DefesaSiegeModels> objRetorno = new List <DefesaSiegeModels>();
conn.Open();
sqlCom.Connection = conn;
SqlDataReader reader = sqlCom.ExecuteReader();
while (reader.Read())
{
objDefesa = new DefesaSiegeModels();
objDefesa.Derrota = int.Parse(reader["Derrota"].ToString());
objDefesa.Player = new PlayerModels()
{
Id = int.Parse(reader["IdPlayer"].ToString()),
Nome = reader["Nome"].ToString()
};
objDefesa.Siege = objSiege;
objDefesa.Vitoria = int.Parse(reader["Vitoria"].ToString());
objRetorno.Add(objDefesa);
}
conn.Close();
conn.Dispose();
return(objRetorno);
}
catch (Exception ex)
{
throw ex;
}
}