public string GetStringCatalogIDByParentCatID(int ParentCatID, int AgentCatID, string Lang)
{
string s = "";
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID FROM GroupCat WHERE AgentCatID='" + AgentCatID + "' AND ParentCatID='" + ParentCatID + "' AND Lang='" + Lang + "'";
ds = ca.SelectData("GroupCat", Sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
s += Convert.ToString(ds.Tables[0].Rows[i]["ID"]);
if (i < ds.Tables[0].Rows.Count - 1)
{
s += ",";
}
}
if (s != "")
{
s = ParentCatID + "," + s;
}
else if (s == "")
{
s += ParentCatID;
}
}
catch { }
return(s);
}
public DataSet Search(string DatasetName, int AgentCatID, string Lang, int DistrictID, int TransactionTypeID, int RealEstateTypeID, int MinPrice, int MaxPrice)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
//try
{
string Sql = "SELECT * FROM RealEstate WHERE AgentCatID='" + AgentCatID + "' AND Lang='" + Lang + "' ";
if (DistrictID != -1)
{
Sql += " AND DistrictID='" + DistrictID + "'";
}
if (TransactionTypeID != -1)
{
Sql += " AND TransactionTypeID='" + TransactionTypeID + "'";
}
if (RealEstateTypeID != -1)
{
Sql += " AND RealEstateTypeID='" + RealEstateTypeID + "'";
}
ds = ca.SelectData(DatasetName, Sql);
}
//catch { }
return(ds);
}
public string GetStringCategoryIDByParentCateID(int ParentCateID, int AgentCatID, string Lang, string ContentType)
{
string s = "";
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT CategoryID FROM ArticleCategory WHERE ContentType = '" + ContentType + "' AND AgentCatID='" + AgentCatID + "' AND ParentCategoryID='" + ParentCateID + "' AND Lang='" + Lang + "'";
ds = ca.SelectData("ArticleCategory", Sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
s += Convert.ToString(ds.Tables[0].Rows[i]["CategoryID"]);
if (i < ds.Tables[0].Rows.Count - 1)
{
s += ",";
}
}
if (s != "")
{
s = ParentCateID + "," + s;
}
else if (s == "")
{
s += ParentCateID;
}
}
catch { }
return(s);
}
/// <summary>
/// Check username login
/// </summary>
/// <param name="Username"></param>
/// <param name="Password"></param>
/// <param name="AgentCatID"></param>
/// <returns></returns>
public bool ValidateUser(string Username, string Password, int AgentCatID)
{
bool IsValid = false;
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
//try
{
string Sql = "SELECT Username, Password FROM UserCat WHERE AgentCatID=@AgentCatID AND Username=@Username AND Password=@Password";
string[] strParaName = new string[3] {
"@AgentCatID", "@Username", "@Password"
};
SqlDbType[] sqlDbType = new SqlDbType[3] {
SqlDbType.Int, SqlDbType.VarChar, SqlDbType.VarChar
};
object[] objValue = new object[3] {
AgentCatID, Username, Password
};
ds = ca.SelectData("UserCat", Sql, strParaName, sqlDbType, objValue);
if (ds.Tables[0].Rows.Count > 0) //có Username này rùi!
{
IsValid = true;
}
}
//catch { IsValid = false; }
return(IsValid);
}
/// <summary>
/// Lấy DS các ngành
/// </summary>
/// <param name="DatasetName"></param>
/// <param name="AgentCatID"></param>
/// <param name="Lang"></param>
/// <param name="OrderType">1:mới nhất, 2:cũ nhất, 3:theo tên(ABC)</param>
/// <returns></returns>
public DataSet GetAllFaculty(string DatasetName, int AgentCatID, string Lang, int OrderType)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
//try
{
string Sql = "SELECT FacultyID, AgentCatID, FacultyName" + Lang + ", ParentID, CreationDate ";
Sql += "FROM Faculty WHERE AgentCatID = '" + AgentCatID + "' ";
if (OrderType == 1)
{
Sql += "ORDER BY CreationDate DESC";
}
else if (OrderType == 2)
{
Sql += "ORDER BY CreationDate ASC";
}
else if (OrderType == 3)
{
Sql += "ORDER BY FacultyName" + Lang + " ASC";
}
ds = ca.SelectData(DatasetName, Sql);
}
//catch { }
return(ds);
}
public DataSet SearchAgentCat(string DatasetName, int ParentID, string Lang, string Name, bool IsAddress, int FacultyID, int ProvinceID)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, AgentCode, AgentName" + Lang + ", ContactName, Address" + Lang + ", ProvinceID, Tel, Fax, Email, Yahoo, Skype, Website, Logo, FooterWebsite" + Lang + ", Visitors, ParentID, FacultyID, IsHot, CreationDate ";
Sql += "FROM AgentCat WHERE ParentID='" + ParentID + "' ";
Sql += "AND (AgentName" + Lang + " LIKE N'%" + Name + "%' OR AgentNameHidden LIKE N'%" + Name + "%') ";
if (IsAddress)
{
Sql += "OR (Address" + Lang + " LIKE N'%" + Name + "%' OR Keyword LIKE N'%" + Name + "%') ";
}
if (FacultyID != 0)
{
Sql += "AND FacultyID = '" + FacultyID + "' ";
}
if (ProvinceID != 0)
{
Sql += "AND ProvinceID = '" + ProvinceID + "' ";
}
Sql += "ORDER By CreationDate DESC";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
/// <summary>
/// Tìm kiếm sản phẩm (chuẩn)
/// </summary>
/// <param name="DatasetName"></param>
/// <param name="AgentCatID"></param>
/// <param name="Lang"></param>
/// <param name="Text"></param>
/// <param name="StringCatalogID"></param>
/// <param name="ProducerID"></param>
/// <param name="OrderBy">"Default", "Brand", "StyleName", "PercentOff", "PriceLowToHigh", "PriceLowToHigh"</param>
/// <returns></returns>
public DataSet SearchProduct(string DatasetName, int AgentCatID, string Lang, string Text, string StringCatalogID, int ProducerID, string OrderBy)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT p.ID, GroupID, p.ProducerID, MaSanPham, TenGoi, QuyCach, NgoaiTe, GiaBan, (GiaBan + ' ' + NgoaiTe) as FullPrice, GiaThamKhao, GiaKhuyenMai, ThongTin1, ThongTin2, TonKho, TrangThai, Anh, DonViTinh, (convert(int,GiaBan) - convert(int,GiaKhuyenMai)) as SaleOff ";
string strFrom = "FROM ProductCat as p ";
string strWhere = "WHERE p.AgentCatID = '" + AgentCatID + "' AND p.Lang='" + Lang + "' ";
if (StringCatalogID != "")
{
strWhere += "AND p.GroupID IN (" + StringCatalogID + ") ";
}
if (Text != "")
{
strWhere += "AND (TenGoi LIKE N'%" + Text + "%' OR ThongTin1 LIKE N'%" + Text + "%' OR ThongTin2 LIKE N'%" + Text + "%' ) ";
}
if (ProducerID != -1)
{
strWhere += "AND ProducerID = '" + ProducerID + "' ";
}
string strOrderBy = "";
if (OrderBy == "Default")
{
strOrderBy = " ORDER BY p.ID DESC";
}
else if (OrderBy == "Brand")
{
strOrderBy = " ORDER BY b.ProducerName ASC";
}
else if (OrderBy == "StyleName")
{
strOrderBy = " ORDER BY p.TenGoi ASC";
}
else if (OrderBy == "PercentOff")
{
strOrderBy = " ORDER BY SaleOff DESC";
}
else if (OrderBy == "PriceLowToHigh")
{
strOrderBy = " ORDER BY GiaBan ASC";
}
else if (OrderBy == "PriceLowToHigh")
{
strOrderBy = " ORDER BY GiaBan DESC";
}
Sql += strFrom + strWhere + strOrderBy;
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetListColor(string DatasetName, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ColorID, ColorName FROM Color WHERE Lang='" + Lang + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetAllCustomer(string DatasetName, int AgentCatID)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT * FROM Customer WHERE AgentCatID = '" + AgentCatID + "' ORDER BY CustomerID DESC";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetCustomerByCustomerID(string DatasetName, int AgentCatID, int CustomerID)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT * FROM Customer WHERE CustomerID='" + CustomerID + "' AND AgentCatID = '" + AgentCatID + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
/// <summary>
/// Lấy DS quảng cáo theo vị trí
/// </summary>
/// <param name="DatasetName"></param>
/// <param name="AgentCatID"></param>
/// <param name="Position">"left":Banner cột trái, "right":Banner cột phải</param>
/// <returns></returns>
public DataSet GetLinkByPosition(string DatasetName, int AgentCatID, string Position, string t)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT * FROM AgentLink WHERE AgentCatID='" + AgentCatID + "' AND Position='" + Position + "' AND ContentType = '" + t + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetLinkByLinkID(string DatasetName, int LinkID)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT * FROM AgentLink WHERE ID='" + LinkID + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetAllLink(string DatasetName, int AgentCatID, string ContentType)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT * FROM AgentLink WHERE AgentCatID='" + AgentCatID + "' AND ContentType = '" + ContentType + "' ORDER BY LinkUpdate DESC";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetRealEstateDetails(string DatasetName, int ID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT * FROM RealEstate WHERE ID='" + ID + "' AND Lang='" + Lang + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetAllAgentCat2(string DatasetName, int ParentID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT * FROM AgentCat WHERE ParentID='" + ParentID + "' ORDER By CreationDate DESC";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetTopHotRealEstate(string DatasetName, int AgentCatID, int nTop, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
//try
{
string Sql = "SELECT TOP " + nTop + " * FROM RealEstate WHERE AgentCatID='" + AgentCatID + "' AND Lang='" + Lang + "' AND IsHot=1 ORDER BY ID DESC";
ds = ca.SelectData(DatasetName, Sql);
}
//catch { }
return(ds);
}
public DataSet GetRealEstatePositionByID(string DatasetName, int ID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, AgentCatID, Lang, RealEstatePositionName, RealEstatePositionOrder, CreationDate ";
Sql += "FROM RealEstatePosition WHERE ID = '" + ID + "' AND Lang='" + Lang + "' ORDER BY RealEstatePositionOrder ASC";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetAllRealEstateLegalStatus(string DatasetName, int AgentCatID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, AgentCatID, Lang, RealEstateLegalStatusName, RealEstateLegalStatusOrder, CreationDate ";
Sql += "FROM RealEstateLegalStatus WHERE AgentCatID = '" + AgentCatID + "' AND Lang='" + Lang + "' ORDER BY RealEstateLegalStatusOrder ASC, CreationDate ASC";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetRealEstateTypeByParentID(string DatasetName, int AgentCatID, int ParentID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, AgentCatID, ParentID, Lang, Name, [Order], CreationDate ";
Sql += "FROM RealEstateType WHERE AgentCatID = '" + AgentCatID + "' AND ParentID='" + ParentID + "' AND Lang='" + Lang + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetColorByColorID(string DatasetName, int ColorID)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ColorID, Lang, ColorName FROM Color ";
Sql += "WHERE ColorID = '" + ColorID + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetTopCategoryByParentCategoryID(string DatasetName, int AgentCatID, int nTop, int ParentCategoryID, string Lang, string ContentType)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT TOP " + nTop + " CategoryID, Lang, CategoryName, CategoryOrder, ParentCategoryID, CategoryAddDate, CategoryUpdate ";
Sql += "FROM ArticleCategory WHERE ContentType='" + ContentType + "' AND AgentCatID = '" + AgentCatID + "' AND ParentCategoryID='" + ParentCategoryID + "' AND Lang='" + Lang + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetCategoryByCategoryID(string DatasetName, int CategoryID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT CategoryID, Lang, CategoryName, CategoryOrder, ParentCategoryID, CategoryAddDate, CategoryUpdate ";
Sql += "FROM ArticleCategory WHERE CategoryID='" + CategoryID + "' AND Lang='" + Lang + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetListSizeByParentCatID(string DatasetName, int AgentCatID, int ParentCatID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT * ";
Sql += "FROM Size WHERE AgentCatID = '" + AgentCatID + "' AND ParentGroupCatID_" + Lang + "='" + ParentCatID + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetUserByUsername(string DatasetName, string Username, int AgentCatID)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
//try
{
string Sql = "SELECT ID, AgentCatID, FullName, Username, Password, Tel, Address, Email, Info, DateCreated, IDGroup, BirthDay, Gender, Active, RandomKey, LoginNumber, DisableDate, Expiration ";
Sql += "FROM UserCat WHERE AgentCatID = '" + AgentCatID + " ' AND Username = '******' ORDER By DateCreated DESC";
ds = ca.SelectData(DatasetName, Sql);
}
//catch { }
return(ds);
}
public DataSet GetAllUser(string DatasetName)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, AgentCatID, FullName, Username, Password, Tel, Address, Email, Info, DateCreated, IDGroup, BirthDay, Gender, Active, RandomKey, LoginNumber, DisableDate, Expiration ";
Sql += "FROM UserCat ORDER By DateCreated DESC";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetAlbumByAlbumID(string DatasetName, int AlbumID)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, AgentCatID, AlbumName, AlbumDescriptions, ParentID, AlbumOrder, CreationDate ";
Sql += "FROM GalleryAlbum WHERE ID='" + AlbumID + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetListSizeByChildSizeTypeID(string DatasetName, int ChildSizeTypeID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT SizeID, SizeName, ChildSizeTypeID, ParentGroupCatID_" + Lang + ", ChildSizeTypeName_" + Lang + " ";
Sql += "FROM Size WHERE ChildSizeTypeID='" + ChildSizeTypeID + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetCatalogByParentCatID(string DatasetName, int AgentCatID, int ParentCatID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, AgentCatID, ID_Group, MaNhom, TenNhom, STT, Anh_Group, ThongTin1_Group, ThongTin2_Group, Lang, ParentCatID ";
Sql += "FROM GroupCat WHERE AgentCatID='" + AgentCatID + "' AND ParentCatID='" + ParentCatID + "' AND Lang='" + Lang + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
/// <summary>
/// Lấy tất cả doanh nhân
/// </summary>
/// <param name="DatasetName"></param>
/// <param name="ParentAgentCatID"></param>
/// <param name="Lang"></param>
/// <returns></returns>
public DataSet GetAllEGroup(string DatasetName, int ParentAgentCatID, string Lang)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, GroupName" + Lang + ", ImageURL, Marks, BirthDay, ParentAgentCatID ";
Sql += "FROM EGroup WHERE ParentAgentCatID='" + ParentAgentCatID + "' ORDER By Marks DESC";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}
public DataSet GetPhotoByPhotoID(string DatasetName, int PhotoID)
{
DataSet ds = new DataSet();
MyConnection ca = new MyConnection();
try
{
string Sql = "SELECT ID, AgentCatID, AlbumID, PhotoTitle, PhotoUrl, PhotoDescriptions, PhotoOrder, CreationDate ";
Sql += "FROM GalleryPhoto WHERE ID='" + PhotoID + "'";
ds = ca.SelectData(DatasetName, Sql);
}
catch { }
return(ds);
}