public List <CustomHtmlLink> MostViewed(int categoryId, int returnAmount)
{
List <CustomHtmlLink> viewList = new List <CustomHtmlLink>();
string queryString = "MostViewed";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@categoryId", categoryId);
command.Parameters.AddWithValue("@returnAmount", returnAmount);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CustomHtmlLink link = new CustomHtmlLink();
link.Href = "/" + reader.GetStringSafe(0);
if (!reader.IsDBNull(1))
{
link.Href += reader.GetStringSafe(1);
}
if (!reader.IsDBNull(2))
{
link.Href += reader.GetStringSafe(2);
}
link.LinkText = reader.GetStringSafe(3);
link.Title = reader.GetStringSafe(4);
viewList.Add(link);
}
reader.Close();
}
catch (Exception ex)
{
DIYError sError = new DIYError(ex);
}
}
return(viewList);
}
//public static List<CustomHtmlLink> GenerateCrumbLinks(string Url)
//{
// List<CustomHtmlLink> linkList = new List<CustomHtmlLink>();
// return linkList;
//}
public static List<CustomHtmlLink> GenerateCrumbLinks(int categoryRowId, string linkPrefix)
{
List<CustomHtmlLink> linkList = new List<CustomHtmlLink>();
Category cat = AppStatic.Categories
.Where(c => c.CategoryRowId == categoryRowId)
.FirstOrDefault();
if (cat != null)
{
if (!String.IsNullOrEmpty(cat.CategoryUrl))
{
CustomHtmlLink link = new CustomHtmlLink();
link.LinkText = cat.CategoryName;
link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl;
link.Title = cat.CategoryName;
linkList.Add(link);
}
if (!String.IsNullOrEmpty(cat.SecondLevCategoryUrl))
{
CustomHtmlLink link = new CustomHtmlLink();
link.LinkText = cat.SecondLevCategoryName;
link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl;
link.Title = cat.SecondLevCategoryName;
linkList.Add(link);
}
if (!String.IsNullOrEmpty(cat.ThirdLevCategoryUrl))
{
CustomHtmlLink link = new CustomHtmlLink();
link.LinkText = cat.ThirdLevCategoryName;
link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl + "/" + cat.ThirdLevCategoryUrl;
link.Title = cat.ThirdLevCategoryName;
linkList.Add(link);
}
}
else
{
throw (new Exception("Unable to find correct category."));
}
return linkList;
}
public List <CustomHtmlLink> RelatedArticleLinks(Category cat, string linkPrefix, int categoryLevel)
{
List <CustomHtmlLink> linkList = new List <CustomHtmlLink>();
//List<Article> articles = new List<Article>();
string queryString = "SELECT [Name],[Title],[URLLink] FROM [MLB].[dbo].[Article]";
string whereSql = "";
switch (categoryLevel)
{
case 1:
whereSql = "WHERE CategoryId = " + cat.CategoryId;
break;
case 2:
whereSql = "WHERE SecondLevCategoryId = " + cat.SecondLevCategoryId + " AND CategoryId= " + cat.CategoryId + " AND ThirdLevCategoryId = 0";
break;
case 3:
whereSql = "WHERE ThirdLevCategoryId = " + cat.ThirdLevCategoryId;
break;
case 4:
whereSql = "WHERE CategoryId = " + cat.CategoryId;
break;
default:
break;
}
queryString += whereSql;
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
//command.Parameters.AddWithValue("leagueId", leagueId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CustomHtmlLink link = new CustomHtmlLink
{
LinkText = reader.GetString(0),
Title = reader.GetString(1),
Href = linkPrefix + reader.GetString(2)
};
linkList.Add(link);
}
reader.Close();
}
catch (Exception ex)
{
DIYError sError = new DIYError(ex);
}
}
return(linkList);
//if (!String.IsNullOrEmpty(cat.ThirdLevCategoryUrl))
//{
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.ThirdLevCategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl + "/" + cat.ThirdLevCategoryUrl;
// link.Title = cat.ThirdLevCategoryName;
// linkList.Add(link);
// foreach (Article art in CategoryArticles(cat.ThirdLevCategoryId, 3))
// {
// }
// //List<Article> articles =
// return linkList;
//}
//if (!String.IsNullOrEmpty(cat.SecondLevCategoryUrl))
//{
// //List<Article> articles =
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.SecondLevCategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl;
// link.Title = cat.SecondLevCategoryName;
// linkList.Add(link);
// return linkList;
//}
//if (!String.IsNullOrEmpty(cat.CategoryUrl))
//{
// //List<Article> articles =
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.CategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl;
// link.Title = cat.CategoryName;
// linkList.Add(link);
// return linkList;
//}
//if (!String.IsNullOrEmpty(cat.CategoryUrl))
//{
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.CategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl;
// link.Title = cat.CategoryName;
// linkList.Add(link);
//}
//if (String.IsNullOrEmpty(cat.SecondLevCategoryUrl))
//{
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.SecondLevCategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl;
// link.Title = cat.SecondLevCategoryName;
// linkList.Add(link);
//}
//return linkList;
}
//public static List<CustomHtmlLink> GenerateTreeViewFirstLev(int categoryRowId, string linkPrefix)
//{
// List<CustomHtmlLink> linkList = new List<CustomHtmlLink>();
// ListAccess la = new ListAccess();
// Category cat = AppStatic.Categories
// .Where(c => c.CategoryRowId == categoryRowId)
// .FirstOrDefault();
// if (!String.IsNullOrEmpty(cat.ThirdLevCategoryUrl))
// {
// CustomHtmlLink htmlLink = new CustomHtmlLink
// {
// LinkText = cat.ThirdLevCategoryName,
// Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl + "/" + cat.ThirdLevCategoryUrl,
// Title = cat.ThirdLevCategoryName,
// SubLinks = la.RelatedArticleLinks(cat, linkPrefix, 3)
// };
// //linkList[0].SubLinks=la.RelatedArticleLinks(cat, linkPrefix, 3);
// List<Category> secondLevelCats = AppStatic.Categories
// .Where(c => c.SecondLevCategoryId == cat.SecondLevCategoryId
// && c.CategoryRowId != categoryRowId).ToList();
// foreach (Category _cat in secondLevelCats)
// {
// //linkList.Add(new CustomHtmlLink
// //{
// // LinkText = _cat.SecondLevCategoryName,
// // Href = "/" + linkPrefix + "/" + _cat.CategoryUrl + "/" + _cat.SecondLevCategoryUrl,
// // Title = _cat.SecondLevCategoryName + "-category",
// //});
// //(!String.IsNullOrEmpty(_cat.ThirdLevCategoryUrl))
// //{
// //}
// //linkList.Last().SubLinks = la.RelatedArticleLinks(_cat, linkPrefix);
// }
// return linkList;
// }
// if (!String.IsNullOrEmpty(cat.SecondLevCategoryUrl))
// {
// List<Category> secondLevelCats = AppStatic.Categories
// .Where(c => c.CategoryId == cat.CategoryId).ToList();
// foreach (Category _cat in secondLevelCats)
// {
// linkList.Add(new CustomHtmlLink
// {
// LinkText = _cat.CategoryName,
// Href = "/" + linkPrefix + "/" + _cat.CategoryUrl,
// Title = _cat.CategoryName
// });
// if (!String.IsNullOrEmpty(_cat.ThirdLevCategoryUrl))
// {
// linkList.Last().SubLinks = GenerateRelatedTreeView(_cat.CategoryRowId, linkPrefix);
// }
// else
// {
// linkList.Last().SubLinks = la.RelatedArticleLinks(_cat, linkPrefix, 3);
// }
// }
// return linkList;
// }
// //ListAccess la = new ListAccess();
// //linkList = la.RelatedLinks(cat, linkPrefix);
// return linkList;
//}
public static List<CustomHtmlLink> GenerateTreeViewThirdLev(int secondLevCatId, string linkPrefix)
{
List<CustomHtmlLink> linkList = new List<CustomHtmlLink>();
ListAccess la = new ListAccess();
List<Category> thirdLevCats = AppStatic.Categories
.Where(c => c.SecondLevCategoryId == secondLevCatId
&& c.ThirdLevCategoryId > 0)
.ToList();
//List<Category> thirdLevelCats = AppStatic.Categories
// .Where(c => c.SecondLevCategoryId == cat.SecondLevCategoryId
// && !String.IsNullOrEmpty(c.ThirdLevCategoryName))
// .ToList();
foreach (Category _thirdLevCat in thirdLevCats)
{
CustomHtmlLink newCat = new CustomHtmlLink
{
LinkText = _thirdLevCat.ThirdLevCategoryName,
Href = AppStatic.BaseSiteUrl + linkPrefix + "/" + _thirdLevCat.CategoryUrl + "/" + _thirdLevCat.SecondLevCategoryUrl + "/" + _thirdLevCat.ThirdLevCategoryUrl,
Title = _thirdLevCat.ThirdLevCategoryName,
SubLinks = la.RelatedArticleLinks(_thirdLevCat, AppStatic.BaseSiteUrl + linkPrefix, 3)
};
linkList.Add(newCat);
//newCat.SubLinks.AddRange(GenerateTreeViewThirdLev(_secondLevCat.SecondLevCategoryId, linkPrefix));
}
return linkList;
}
public static List<CustomHtmlLink> GenerateTreeViewSecondLev(int firstLevCategoryId, string linkPrefix)
{
List<CustomHtmlLink> linkList = new List<CustomHtmlLink>();
ListAccess la = new ListAccess();
//Category cat = AppStatic.Categories
// .Where(c => c.CategoryRowId == categoryRowId)
// .FirstOrDefault();
List<Category> secondLevCats = AppStatic.Categories
.Where(c => c.CategoryId == firstLevCategoryId
&& c.SecondLevCategoryId > 0
&& c.ThirdLevCategoryId == 0)
.ToList();
//List<Category> thirdLevelCats = AppStatic.Categories
// .Where(c => c.SecondLevCategoryId == cat.SecondLevCategoryId
// && !String.IsNullOrEmpty(c.ThirdLevCategoryName))
// .ToList();
foreach (Category _secondLevCat in secondLevCats)
{
CustomHtmlLink newCat = new CustomHtmlLink
{
LinkText = _secondLevCat.SecondLevCategoryName,
Href = AppStatic.BaseSiteUrl + linkPrefix + "/" + _secondLevCat.CategoryUrl + "/" + _secondLevCat.SecondLevCategoryUrl,
Title = _secondLevCat.SecondLevCategoryName,
SubLinks = la.RelatedArticleLinks(_secondLevCat, AppStatic.BaseSiteUrl + linkPrefix, 2)
};
newCat.SubLinks.AddRange(GenerateTreeViewThirdLev(_secondLevCat.SecondLevCategoryId, linkPrefix));
linkList.Add(newCat);
//if (String.IsNullOrEmpty(_cat.ThirdLevCategoryName))
//{
// linkList.Add(new CustomHtmlLink
// {
// LinkText = _cat.SecondLevCategoryName,
// Href = "/" + linkPrefix + "/" + _cat.CategoryUrl + "/" + _cat.SecondLevCategoryUrl,
// Title = _cat.SecondLevCategoryName,
// SubLinks = la.RelatedArticleLinks(_cat, linkPrefix, 2)
// });
//}
//foreach(Category __cat in thirdLevelCats){
// linkList.Last().SubLinks.Add(new CustomHtmlLink
// {
// LinkText = __cat.ThirdLevCategoryName,
// Href = "/" + linkPrefix + "/" + _cat.CategoryUrl + "/" + __cat.SecondLevCategoryUrl + "/" + __cat.ThirdLevCategoryUrl,
// Title = __cat.ThirdLevCategoryName,
// SubLinks = la.RelatedArticleLinks(_cat, linkPrefix, 3)
// });
//}
//else
//{
// linkList.Add(new CustomHtmlLink
// {
// LinkText = _cat.ThirdLevCategoryName,
// Href = "/" + linkPrefix + "/" + _cat.CategoryUrl + "/" + _cat.SecondLevCategoryUrl + "/" + _cat.ThirdLevCategoryUrl,
// Title = _cat.ThirdLevCategoryName,
// SubLinks = la.RelatedArticleLinks(_cat, linkPrefix, 3)
// });
//}
//foreach (Category thirdLevCat in AppStatic.Categories
// .Where(c => c.SecondLevCategoryId == _cat.SecondLevCategoryId
// && !String.IsNullOrEmpty(c.ThirdLevCategoryUrl))
// .ToList())
//{
// linkList.Last().SubLinks.Add(new CustomHtmlLink
// {
// LinkText = thirdLevCat.ThirdLevCategoryName,
// Href = "/" + linkPrefix + "/" + thirdLevCat.CategoryUrl + "/" + thirdLevCat.SecondLevCategoryUrl + "/" + thirdLevCat.ThirdLevCategoryUrl,
// Title = thirdLevCat.SecondLevCategoryName,
// SubLinks = la.RelatedArticleLinks(thirdLevCat, linkPrefix, 3)
// });
//}
}
//if (!String.IsNullOrEmpty(cat.ThirdLevCategoryUrl))
//{
// CustomHtmlLink htmlLink = new CustomHtmlLink
// {
// LinkText = cat.ThirdLevCategoryName,
// Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl + "/" + cat.ThirdLevCategoryUrl,
// Title = cat.ThirdLevCategoryName,
// SubLinks = la.RelatedArticleLinks(cat, linkPrefix, 3)
// };
// //linkList[0].SubLinks=la.RelatedArticleLinks(cat, linkPrefix, 3);
// List<Category> secondLevelCats = AppStatic.Categories
// .Where(c => c.SecondLevCategoryId == cat.SecondLevCategoryId
// && c.CategoryRowId != categoryRowId).ToList();
// foreach (Category _cat in secondLevelCats)
// {
// //linkList.Add(new CustomHtmlLink
// //{
// // LinkText = _cat.SecondLevCategoryName,
// // Href = "/" + linkPrefix + "/" + _cat.CategoryUrl + "/" + _cat.SecondLevCategoryUrl,
// // Title = _cat.SecondLevCategoryName + "-category",
// //});
// //(!String.IsNullOrEmpty(_cat.ThirdLevCategoryUrl))
// //{
// //}
// //linkList.Last().SubLinks = la.RelatedArticleLinks(_cat, linkPrefix);
// }
// return linkList;
//}
//if (!String.IsNullOrEmpty(cat.SecondLevCategoryUrl))
//{
// List<Category> secondLevelCats = AppStatic.Categories
// .Where(c => c.CategoryId == cat.CategoryId).ToList();
// foreach (Category _cat in secondLevelCats)
// {
// linkList.Add(new CustomHtmlLink
// {
// LinkText = _cat.CategoryName,
// Href = "/" + linkPrefix + "/" + _cat.CategoryUrl,
// Title = _cat.CategoryName
// });
// if (!String.IsNullOrEmpty(_cat.ThirdLevCategoryUrl))
// {
// linkList.Last().SubLinks = GenerateRelatedTreeView(_cat.CategoryRowId, linkPrefix);
// }
// else
// {
// linkList.Last().SubLinks = la.RelatedArticleLinks(_cat, linkPrefix, 3);
// }
// }
// return linkList;
//}
//ListAccess la = new ListAccess();
//linkList = la.RelatedLinks(cat, linkPrefix);
return linkList;
}
public List<CustomHtmlLink> RelatedArticleLinks(Category cat, string linkPrefix, int categoryLevel)
{
List<CustomHtmlLink> linkList = new List<CustomHtmlLink>();
//List<Article> articles = new List<Article>();
string queryString = "SELECT [Name],[Title],[URLLink] FROM [MLB].[dbo].[Article]";
string whereSql = "";
switch (categoryLevel)
{
case 1:
whereSql = "WHERE CategoryId = " + cat.CategoryId;
break;
case 2:
whereSql = "WHERE SecondLevCategoryId = " + cat.SecondLevCategoryId + " AND CategoryId= " + cat.CategoryId + " AND ThirdLevCategoryId = 0";
break;
case 3:
whereSql = "WHERE ThirdLevCategoryId = " + cat.ThirdLevCategoryId;
break;
case 4:
whereSql = "WHERE CategoryId = " + cat.CategoryId;
break;
default:
break;
}
queryString += whereSql;
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
//command.Parameters.AddWithValue("leagueId", leagueId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CustomHtmlLink link = new CustomHtmlLink
{
LinkText = reader.GetString(0),
Title = reader.GetString(1),
Href = linkPrefix + reader.GetString(2)
};
linkList.Add(link);
}
reader.Close();
}
catch (Exception ex)
{
DIYError sError = new DIYError(ex);
}
}
return linkList;
//if (!String.IsNullOrEmpty(cat.ThirdLevCategoryUrl))
//{
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.ThirdLevCategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl + "/" + cat.ThirdLevCategoryUrl;
// link.Title = cat.ThirdLevCategoryName;
// linkList.Add(link);
// foreach (Article art in CategoryArticles(cat.ThirdLevCategoryId, 3))
// {
// }
// //List<Article> articles =
// return linkList;
//}
//if (!String.IsNullOrEmpty(cat.SecondLevCategoryUrl))
//{
// //List<Article> articles =
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.SecondLevCategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl;
// link.Title = cat.SecondLevCategoryName;
// linkList.Add(link);
// return linkList;
//}
//if (!String.IsNullOrEmpty(cat.CategoryUrl))
//{
// //List<Article> articles =
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.CategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl;
// link.Title = cat.CategoryName;
// linkList.Add(link);
// return linkList;
//}
//if (!String.IsNullOrEmpty(cat.CategoryUrl))
//{
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.CategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl;
// link.Title = cat.CategoryName;
// linkList.Add(link);
//}
//if (String.IsNullOrEmpty(cat.SecondLevCategoryUrl))
//{
// CustomHtmlLink link = new CustomHtmlLink();
// link.LinkText = cat.SecondLevCategoryName;
// link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl;
// link.Title = cat.SecondLevCategoryName;
// linkList.Add(link);
//}
//return linkList;
}
public List<CustomHtmlLink> MostViewed(int categoryId, int returnAmount)
{
List<CustomHtmlLink> viewList = new List<CustomHtmlLink>();
string queryString = "MostViewed";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@categoryId", categoryId);
command.Parameters.AddWithValue("@returnAmount", returnAmount);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CustomHtmlLink link = new CustomHtmlLink();
link.Href = "/" + reader.GetStringSafe(0);
if (!reader.IsDBNull(1)) {
link.Href += reader.GetStringSafe(1);
}
if (!reader.IsDBNull(2)) {
link.Href += reader.GetStringSafe(2);
}
link.LinkText = reader.GetStringSafe(3);
link.Title = reader.GetStringSafe(4);
viewList.Add(link);
}
reader.Close();
}
catch (Exception ex)
{
DIYError sError = new DIYError(ex);
}
}
return viewList;
}