using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlClient;
///
/// Summary description for Categories
///
public class Categories
{
public Categories()
{
//
// TODO: Add constructor logic here
//
}
#region private variables
private Int32 cat_id;
private string cat_name,meta_title,meta_desc,meta_keyword;
private Int32 parent;
private bool flag;
private decimal fees;
#endregion
#region properties
public string MetaTitle
{
get { return meta_title; }
set { meta_title = value; }
}
public string MetaDesc
{
get { return meta_desc; }
set { meta_desc = value; }
}
public string MetaKeyword
{
get { return meta_keyword; }
set { meta_keyword = value; }
}
public bool Flag
{
get { return flag; }
set { flag = value; }
}
public decimal Fees
{
get { return fees ; }
set { fees = value; }
}
public Int32 Cat_Id
{
get { return cat_id; }
set { cat_id = value; }
}
public Int32 Parent
{
get { return parent; }
set { parent = value; }
}
public string Cat_name
{
get { return cat_name; }
set { cat_name = value; }
}
#endregion
#region Methods
ExecuteSQLQuerry Exec = new ExecuteSQLQuerry();
string sqlStr = "";
string error = "";
public DataSet Dataset_GetCategoryDetailsById()
{
sqlStr = "select cat_id,cat_name as service_name,parent from category where cat_id=@cat_id";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_id", cat_id);
DataSet ds = Exec.Getdataset(sqlStr, paramList, ref error);
return ds;
}
public string GetCatNameByCatId()
{
sqlStr = "select cat_name as service_name from category where cat_id=@cat_id";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_id", cat_id);
object catname = Exec.GetScalar(sqlStr, paramList, ref error);
return catname.ToString();
}
public string GetCatAmountByCatId()
{
sqlStr = "select fees as amount from category where cat_id=@cat_id";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_id", cat_id);
object amount = Exec.GetScalar(sqlStr, paramList, ref error);
return amount.ToString();
}
public DataSet Dataset_GetServicenames_search(string servicename)
{
sqlStr = "select cat_id, cat_name as service_name from category where cat_name like '%" + servicename + "%' and parent not in(0) and cat_Id in (select cat_id from service_questions)";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_name", servicename);
DataSet ds = Exec.Getdataset(sqlStr, paramList, ref error);
return ds;
}
public int DeleteProductCategoryById()
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add(new SqlParameter("@Exists", SqlDbType.Int));
cmd.Parameters["@Exists"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@cat_id", SqlDbType.BigInt));
cmd.Parameters["@cat_id"].Value = this.cat_id;
SQLHelper.ExecuteNonQuery(cmd, CommandType.StoredProcedure, "Sp_DeleteProductCategoryById");
return nvlint(cmd.Parameters["@Exists"].Value);
}
catch
{
return 0;
}
}
public DataSet GetAllCategoriesById()
{
SqlParameterList paramList = new SqlParameterList();
DataSet ds = Exec.GetdatasetOfProcedure("GetCategoriesById", paramList, ref error);
return ds;
}
public DataSet GetAllSubCategoriesByParentId()
{
sqlStr = "select C.cat_id,C.cat_name,C.parent,C.fees,C.cat_id,C.cat_name ,C.parent,C.fees,replace(replace(replace(replace(lower(C.cat_name),' ','-'),'&','-'),'---','-'),'/','')+'-subcategories'+'-'+cast(C.cat_id as varchar)+'.aspx' as url from category C where cat_Id in(select cat_id from service_questions) and C.parent=@parent order by C.cat_name asc ";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@parent", parent);
DataSet ds = Exec.Getdataset(sqlStr, paramList, ref error);
return ds;
}
public DataSet GetArticleCategories()
{
SqlCommand cmd = new SqlCommand();
return SQLHelper.ExecuteAdapter(cmd, CommandType.Text, @"select cat_id,cat_name,parent,fees,
replace(replace(replace(replace(lower(cat_name),' ','-'),'&','-'),'---','-'),'/','')+'-articlesubcategories'+'-'+cast(cat_id as varchar)+'.aspx' as url from category where parent=0 and cat_Id in (select Parent from category where cat_Id in(select cat_id from service_questions)) order by cat_name ");
}
public DataSet GetCategories()
{
SqlCommand cmd = new SqlCommand();
return SQLHelper.ExecuteAdapter(cmd, CommandType.Text, @"select cat_id,cat_name,parent,fees,
replace(replace(replace(replace(lower(cat_name),' ','-'),'&','-'),'---','-'),'/','')+'-subcategories'+'-'+cast(cat_id as varchar)+'.aspx' as url from category where parent=0 and cat_Id in (select Parent from category where cat_Id in(select cat_id from service_questions)) order by cat_name ");
}
public DataSet GetAllCategories()
{
SqlCommand cmd = new SqlCommand();
return SQLHelper.ExecuteAdapter(cmd, CommandType.Text, @"select * from category where parent not in (0)");
}
public DataSet GetCategoriesForHome()
{
SqlCommand cmd = new SqlCommand();
return SQLHelper.ExecuteAdapter(cmd, CommandType.Text, @"select top(15) cat_id,cat_name,parent,fees,replace(replace(replace(lower(cat_name),' ','-'),'&','-'),'---','-')+'-subcategories'+'-'+cast(cat_id as varchar)+'.aspx' as url from category where parent=0 and cat_Id in (select Parent from category where cat_Id in(select cat_id from service_questions))");
}
public DataSet GetEmergencyCategories()
{
SqlCommand cmd = new SqlCommand();
return SQLHelper.ExecuteAdapter(cmd, CommandType.Text, @"select top(6) * from category where cat_Id in (select cat_id from service_questions) and parent=777 ORDER BY newid()");
}
//
public DataSet GetCategoryMetaContents()
{
sqlStr = "GetCategoryMetaContents";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_id", cat_id);
DataSet retVal = Exec.GetdatasetOfProcedure(sqlStr, paramList, ref error);
return retVal;
}
public DataSet GetCategoryDetailsById()
{
sqlStr = "GetCategoryDetailsById";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_id", cat_id );
DataSet retVal = Exec.GetdatasetOfProcedure(sqlStr, paramList, ref error);
return retVal;
}
public DataSet SearchSubCategoryListByname()
{
sqlStr = "Search_subcategorylistbyname";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_name", cat_name);
DataSet retVal = Exec.GetdatasetOfProcedure(sqlStr, paramList, ref error);
return retVal;
}
public DataSet GetAllSuvCategories4Grid(int index, Int32 catid)
{
sqlStr = "GetAllsubCategories4Grid";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@pageIndex", index);
paramList.addParams("@maxCount", 10);
paramList.addParams("@cat_id", catid);
DataSet retVal = Exec.GetdatasetOfProcedure(sqlStr, paramList, ref error);
return retVal;
}
public DataSet GetAllCategories4Grid(int index,string search)
{
sqlStr = "usp_GetAllCategories4Grid";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@pageIndex", index);
paramList.addParams("@maxCount", 10);
paramList.addParams("@search", search);
DataSet retVal = Exec.GetdatasetOfProcedure(sqlStr, paramList, ref error);
return retVal;
}
public DataSet SearchSubCategoryListById()
{
sqlStr = "Search_subcategorylistbyId";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_Id", cat_id);
DataSet retVal = Exec.GetdatasetOfProcedure(sqlStr, paramList, ref error);
return retVal;
}
public DataSet SearchCategoryList()
{
sqlStr = "Search_categorylist";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_name", cat_name );
DataSet retVal = Exec.GetdatasetOfProcedure(sqlStr, paramList, ref error);
return retVal;
}
public DataSet GetAllCategory1()
{
SqlParameterList paramList = new SqlParameterList();
DataSet ds = Exec.GetdatasetOfProcedure("GetCategories1", paramList, ref error);
return ds;
}
public DataSet GetAllParentCategory()
{
SqlParameterList paramList = new SqlParameterList();
DataSet ds = Exec.GetdatasetOfProcedure("GetCategories2", paramList, ref error);
return ds;
}
// [GetSubCategories]
public DataSet GetAllSubCategory()
{
SqlParameterList paramList = new SqlParameterList();
DataSet ds = Exec.GetdatasetOfProcedure("GetSubCategories", paramList, ref error);
return ds;
}
public DataSet GetAllCategory()
{
SqlParameterList paramList = new SqlParameterList();
DataSet ds = Exec.GetdatasetOfProcedure("GetCategories", paramList, ref error);
return ds;
}
public DataSet GetCategory()
{
SqlParameterList paramList = new SqlParameterList();
DataSet ds = Exec.GetdatasetOfProcedure("GetAllCategories", paramList, ref error);
return ds;
}
public int DeleteCategory(Int32 catid)
{
sqlStr = "delete from category where cat_id=@cat_id";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_id", catid);
return Int32.Parse(Exec.GetdatasetOfProcedure(sqlStr, paramList, ref error).Tables[0].Rows[0][0].ToString());
}
public int InsertProductCategory()
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add(new SqlParameter("@Exists", SqlDbType.Int));
cmd.Parameters["@Exists"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@cat_id", SqlDbType.BigInt));
cmd.Parameters["@cat_id"].Value = this.cat_id;
cmd.Parameters.Add(new SqlParameter("@Flag", SqlDbType.Bit));
cmd.Parameters["@Flag"].Value = this.flag;
cmd.Parameters.Add(new SqlParameter("@cat_name", SqlDbType.VarChar, 300));
cmd.Parameters["@cat_name"].Value = this.cat_name;
cmd.Parameters.Add(new SqlParameter("@fees", SqlDbType.Decimal));
cmd.Parameters["@fees"].Value = this.fees;
cmd.Parameters.Add(new SqlParameter("@parent", SqlDbType.BigInt));
cmd.Parameters["@parent"].Value = this.parent;
cmd.Parameters.Add(new SqlParameter("@meta_title", SqlDbType.NVarChar, 100));
cmd.Parameters["@meta_title"].Value = this.meta_title;
cmd.Parameters.Add(new SqlParameter("@meta_keyword", SqlDbType.Text));
cmd.Parameters["@meta_keyword"].Value = this.meta_keyword;
cmd.Parameters.Add(new SqlParameter("@meta_description", SqlDbType.NVarChar,200));
cmd.Parameters["@meta_description"].Value = this.meta_desc;
SQLHelper.ExecuteNonQuery(cmd, CommandType.StoredProcedure, "InsertProductCategory_new");//InsertShoppingCartForCUI1
return nvlint(cmd.Parameters["@Exists"].Value);
}
catch
{
return 0;
}
}
//testing
public DataSet GetAllParents()
{
SqlCommand cmd = new SqlCommand();
return SQLHelper.ExecuteAdapter(cmd, CommandType.Text, @"select * from category where parent = 0 order by cat_name");
}
// select * from category where parent = 32
public DataSet Dataset_GetChild()
{
sqlStr = "select * from category where parent = @cat_id";
SqlParameterList paramList = new SqlParameterList();
paramList.addParams("@cat_id",cat_id);
DataSet ds = Exec.Getdataset(sqlStr, paramList, ref error);
return ds;
}
public static Int32 nvlint(object inval)
{
if (inval == DBNull.Value)
{
return 0;
}
return Convert.ToInt32(inval);
}
#endregion
}
