public List<Drink> GetToplist(string name, string name2, string whiskeySort, string country, string region, double maxPrice, double minPrice, string userId)
{
SqlDataReader myReader = null;
List<Drink> toplist = new List<Drink>();
try
{
command.Connection = myConnection;
myConnection.ConnectionString = connectionString;
myConnection.Open();
command.CommandText = "sp_Toplist";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Clear();
command.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 255);
command.Parameters.Add("@Name2", System.Data.SqlDbType.NVarChar, 255);
command.Parameters.Add("@WhiskeySort", System.Data.SqlDbType.NVarChar, 255);
command.Parameters.Add("@Country", System.Data.SqlDbType.NVarChar, 255);
command.Parameters.Add("@Region", System.Data.SqlDbType.NVarChar, 255);
command.Parameters.Add("@MaxPrice", System.Data.SqlDbType.Float);
command.Parameters.Add("@MinPrice", System.Data.SqlDbType.Float);
command.Parameters.Add("@UserID", System.Data.SqlDbType.NVarChar, 128);
command.Parameters["@Name"].Value = name;
command.Parameters["@Name2"].Value = name2;
command.Parameters["@WhiskeySort"].Value = whiskeySort;
command.Parameters["@Country"].Value = country;
command.Parameters["@Region"].Value = region;
command.Parameters["@MaxPrice"].Value = maxPrice;
command.Parameters["@MinPrice"].Value = minPrice;
command.Parameters["@UserID"].Value = userId;
myReader = command.ExecuteReader();
if (myReader != null)
{
Drink tmpDrink = new Drink();
List<string> tmptoplist = new List<string>();
while (myReader.Read())
{
tmpDrink = new Drink();
tmpDrink.Nr=(myReader[0] is double ? (double)myReader[0]:0);
tmpDrink.ProductId= (myReader[1] is double ? (double)myReader[1] : 0);
tmpDrink.Varnummer = (myReader[2] is double ? (double)myReader[2] : 0);
tmpDrink.Name= myReader[3].ToString();
tmpDrink.Name2 = myReader[4].ToString();
tmpDrink.Price = Convert.ToDouble(myReader[5].ToString(), System.Globalization.CultureInfo.InvariantCulture);
tmpDrink.Volume = Convert.ToDouble(myReader[6].ToString(), System.Globalization.CultureInfo.InvariantCulture);
tmpDrink.PricePerLiter = Convert.ToDouble(myReader[7].ToString(), System.Globalization.CultureInfo.InvariantCulture);
tmpDrink.ProductGroup = myReader[8].ToString();
tmpDrink.Origin = myReader[9].ToString();
tmpDrink.Country = myReader[10].ToString();
tmpDrink.Producer = myReader[11].ToString();
tmpDrink.AlcoLevel= myReader[12].ToString();
tmpDrink.Grade=(myReader[13] is double ? (double)myReader[13]:0);
toplist.Add(tmpDrink);
}
}
}
catch (Exception ex)
{
}
finally
{
if (myReader != null)
{
myReader.Close();
}
if (myConnection!=null)
{
myConnection.Close();
}
}
return toplist;
}
private List<Drink> SortAndDistinct(List<Drink> listToSort)
{
Drink tmpDrink = new Drink();
var List = listToSort.GroupBy(x => new { Varnummer = x.Varnummer }).
Select(y => new
{
Count = y.Count(),
Average = y.Average(z => z.Grade),
Varnummer = y.Key.Varnummer
}).ToList();
var returnList = listToSort.DistinctBy(p => p.Varnummer).ToList();
foreach (var Drink in returnList)
{
foreach (var pretendedDrink in List)
{
if (Drink.Varnummer == pretendedDrink.Varnummer)
{
Drink.AverageGrade = pretendedDrink.Average;
Drink.Votes = pretendedDrink.Count;
}
}
}
return returnList.OrderByDescending(d => d.AverageGrade).ToList();
}
