public List<Category> GetActiveCatagoryList()
{
var cat = new Category();
const string commandString =
@"select C.CategoryId,C.CategoryName,C.Ordr,SC.SubCategoryId,SC.SubCategoryName,SC.Ordr
FROM Categories C,SubCategories SC
WHERE C.Active = 1 AND SC.Active = 1 AND C.CategoryId = SC.CategoryId
ORDER BY C.Ordr,SC.Ordr";
int Categoryid = 0;
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var Catlist = new List<Category>();
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (Categoryid != (int)reader["CategoryID"])
{
cat = new Category();
cat.CategoryId = (int)reader["CategoryID"];
cat.CategoryName = (string)reader["CategoryName"];
cat.Ordr = Convert.ToDecimal(reader["Ordr"]);
Categoryid = (int)reader["CategoryID"];
if (Categoryid != 0)
{
Catlist.Add(cat);
}
}
var subcat = new SubCategory();
subcat.CategoryId = (int)reader["CategoryID"];
subcat.SubCategoryId = (int)reader["SubCategoryID"];
subcat.CategoryName = (string)reader["CategoryName"];
subcat.SubCategoryName = (string)reader["SubCategoryName"];
subcat.Ordr = Convert.ToDecimal(reader["Ordr"]);
cat.subcategoylist.Add(subcat);
}
}
}
return Catlist;
}
}
public ActionResult SaveSubCategory(SubCategory subCategory)
{
if (Request.Form["SubCategoryId"] == "0")
{
_adminRepository.AddSubCategory(subCategory);
}
else
{
_adminRepository.UpdateSubCategory_1(subCategory);
}
CategoryModel objlist = new CategoryModel();
objlist.Categories = _adminRepository.GetAllCategory();
return View("ManageSubCategories", objlist);
}
public ActionResult ChangeSubCategoryStatus(int SubCategoryId, bool Value)
{
SubCategory SubCategory = new SubCategory();
SubCategory = _adminRepository.GetSubCategorybyId(SubCategoryId);
if (SubCategory != null)
{
SubCategory.Active = Value;
}
_adminRepository.UpdateSubCategory(SubCategory);
return Json(new { Message = " Sub Category Status Changed Successfully", Success = true });
}
public void UpdateSubCategory(SubCategory subcategory)
{
const string commandString = @"Update SubCategories SET Active = @Active WHERE SubCategoryId=@SubCategoryId";
// const string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\kommanap\Gustin\Gustin\App_Data\Gustin.mdf;Integrated Security=True;User Instance=True";
try
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = new SqlCommand(commandString, connection);
command.Parameters.Add("@SubCategoryId", subcategory.SubCategoryId);
command.Parameters.Add("@Active", subcategory.Active);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
public SubCategory GetSubCategorybyId(int SubCategoryId)
{
const string commandString =
@"SELECT SubCategoryID, SubCategoryName, CategoryID,Active,Ordr
FROM SubCategories WHERE SubCategoryID = @SubCategoryId order by ordr";
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var subCategory = new SubCategory();
{
command.Parameters.Add("@SubCategoryId", SubCategoryId);
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
subCategory.SubCategoryId = (int)reader["SubCategoryID"];
subCategory.SubCategoryName = (string)reader["SubCategoryName"];
subCategory.CategoryId = (int)reader["CategoryID"];
subCategory.Active = Convert.ToBoolean(reader["Active"]);
subCategory.Ordr = Convert.ToDecimal(reader["Ordr"]);
}
}
}
}
return subCategory;
}
}
public List<SubCategory> GetSubCategoriesByCategoryId(int CategoryId)
{
const string commandString =
@"SELECT SubCategoryID, SubCategoryName, CategoryID,Active
FROM SubCategories WHERE CategoryID = @CategoryId order by ordr";
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var subCategories = new List<SubCategory>();
{
command.Parameters.Add("@CategoryId", CategoryId);
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var subCategory = new SubCategory();
subCategory.SubCategoryId = (int)reader["SubCategoryID"];
subCategory.SubCategoryName = (string)reader["SubCategoryName"];
subCategory.CategoryId = (int)reader["CategoryID"];
subCategory.Active = Convert.ToBoolean(reader["Active"]);
subCategories.Add(subCategory);
}
}
}
}
return subCategories;
}
}
public List<SubCategory> GetAllSubCategories()
{
const string commandString =
@"SELECT SubCategoryID, SubCategoryName, CategoryID
FROM SubCategories Order by ordr";
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var subCategories = new List<SubCategory>();
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var subCategory = new SubCategory();
subCategory.SubCategoryId = (int)reader["SubCategoryID"];
subCategory.SubCategoryName = (string)reader["SubCategoryName"];
subCategory.CategoryId = (int)reader["CategoryID"];
subCategories.Add(subCategory);
}
}
}
return subCategories;
}
}
public void AddSubCategory(SubCategory subcategory)
{
const string commandString = @"INSERT INTO SubCategories
(SubCategoryName,Active,CategoryId,Ordr) VALUES (@SubCategoryName,@Active,@CategoryId,@Ordr)";
try
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = new SqlCommand(commandString, connection);
command.Parameters.Add("@SubCategoryName", subcategory.SubCategoryName);
command.Parameters.Add("@Active", true);
command.Parameters.Add("@CategoryId", subcategory.CategoryId);
command.Parameters.Add("@Ordr", subcategory.Ordr );
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
public List<SubCategory> GetPopularCategoriesList()
{
const string commandString =
@"select top 10 C.CategoryId,C.CategoryName,C.Ordr,SC.SubCategoryId,SC.SubCategoryName,SC.Ordr,COUNT(AD.Id) No
FROM Categories C,SubCategories SC, AdvertisementDetails AD
WHERE C.Active = 1 AND SC.Active = 1 AND C.CategoryId = SC.CategoryId
AND AD.Active = 1 AND AD.CategoryId = C.CategoryId AND AD.SubCategoryId = SC.SubCategoryId
GROUP BY C.CategoryId,C.CategoryName,C.Ordr,SC.SubCategoryId,SC.SubCategoryName,SC.Ordr
ORDER BY COUNT(AD.Id) Desc";
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var Catlist = new List<SubCategory>();
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var subcat = new SubCategory();
subcat.CategoryId = (int)reader["CategoryID"];
subcat.SubCategoryId = (int)reader["SubCategoryID"];
subcat.CategoryName = (string)reader["CategoryName"];
subcat.SubCategoryName = (string)reader["SubCategoryName"];
subcat.Ordr = Convert.ToDecimal(reader["Ordr"]);
Catlist.Add(subcat);
}
}
}
return Catlist;
}
}
