public IEnumerable <Product> GetRecommendedProducts(Int64 customerId)
{
var products = new List <Product>();
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT DISTINCT Top 10 Related.RecommendedProductId, Products.Name, Products.Price, Products.TitlesCount FROM PersonalizedRecommendations AS Related INNER JOIN Products ON Related.RecommendedProductId = Products.Id WHERE Related.CustomerId = @CustomerId";
cmd.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.BigInt)
{
Value = customerId
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
products.Add(
new Product
{
Id = (Int64)reader["RecommendedProductId"],
Name = reader["Name"].ToString(),
Price = (int)reader["Price"],
TitlesCount = (int)reader["TitlesCount"]
});
}
}
}
}
return(products);
}
public Product GetProduct(Int64 id)
{
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Products WHERE Id = @Id";
cmd.Parameters.Add(new SqlParameter("Id", SqlDbType.BigInt)
{
Value = id
});
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
return(new Product
{
Id = (Int64)reader["Id"],
Name = reader["Name"].ToString(),
Description = reader["Description"].ToString(),
Title1 = reader["Title1"].ToString(),
Title2 = reader["Title2"].ToString(),
TitlesCount = (int)(reader["TitlesCount"] ?? 0),
Price = (int)reader["Price"]
});
}
}
}
}
public IEnumerable <Product> GetProducts()
{
var products = new List <Product>();
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT top 20 * FROM Products";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
products.Add(
new Product
{
Id = (Int64)reader["Id"],
Name = reader["Name"].ToString(),
Description = reader["Description"].ToString(),
Title1 = reader["Title1"].ToString(),
Title2 = reader["Title2"].ToString(),
TitlesCount = (int)reader["TitlesCount"],
Price = (int)reader["Price"]
});
}
}
}
}
return(products);
}
public CustomerRec GetCustomerById(Int64 id)
{
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Customers WHERE Id = @Id";
cmd.Parameters.Add(new SqlParameter("Id", SqlDbType.BigInt)
{
Value = id
});
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
return(new CustomerRec
{
CustomerId = (Int64)reader["Id"],
UserName = reader["Name"].ToString(),
Region = reader["Region"].ToString()
});
}
}
}
}
public IEnumerable <Promotion> GetPromotions(Int64 customerId)
{
var promotions = new List <Promotion>();
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT DISTINCT CustomerId, ProductId, NewPrice, Promotion FROM Promotions WHERE CustomerId = @CustomerId";
cmd.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.BigInt)
{
Value = customerId
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
promotions.Add(
new Promotion
{
CustomerId = (Int64)reader["CustomerId"],
ProductId = (Int64)reader["ProductId"],
PromotionDiscount = reader["Promotion"].ToString(),
NewPrice = (int)reader["NewPrice"]
});
}
}
}
}
return(promotions);
}
public Promotion GetPromotion(Int64 customerId, Int64 productId)
{
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT DISTINCT CustomerId, ProductId, NewPrice, Promotion FROM Promotions WHERE CustomerId = @CustomerId AND ProductId = @ProductId";
cmd.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.BigInt)
{
Value = customerId
});
cmd.Parameters.Add(new SqlParameter("ProductId", SqlDbType.BigInt)
{
Value = productId
});
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
return(new Promotion
{
CustomerId = (Int64)reader["CustomerId"],
ProductId = (Int64)reader["ProductId"],
PromotionDiscount = reader["Promotion"].ToString(),
NewPrice = (int)reader["NewPrice"]
});
}
}
}
}
public int GetSongPlayCount(Int64 productId, Int64 customerId)
{
var playCount = 0;
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT Sum(CU.IsPlayed) as PlayCount FROM CustomerUsage CU WHERE Cu.CustomerId = @CustomerId AND Cu.ProductId = @ProductId";
cmd.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.BigInt)
{
Value = customerId
});
cmd.Parameters.Add(new SqlParameter("ProductId", SqlDbType.BigInt)
{
Value = productId
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (!DBNull.Value.Equals(reader["PlayCount"]))
{
playCount = (int)(reader["PlayCount"]);
}
else
{
playCount = 0;
}
}
}
}
}
return(playCount);
}