/// <summary>
/// Sản phẩm mới của trang chủ
/// </summary>
/// <param name="N"></param>
/// <returns></returns>
public DataTable GetNewAboutUs(int N)
{
DbTask db = new DbTask();
string Sql = "SELECT a.id,a.shortlink,a.title,a.groupid, a.fimage, a.summary AS summary, b.idx AS idx FROM " + TableName + " as a INNER JOIN " + TableName_noibat + " as b ON a.id = b.id ";
Sql += "WHERE a.status = 1 ";
Sql += "ORDER BY b.idx ASC, a.created DESC ";
return(db.GetData(Sql));
}
/// <summary>
/// Lấy N sản phẩm của chuyên mục sắp xếp theo số thứ tự trong CMS
/// </summary>
/// <param name="groupid"></param>
/// <param name="N"></param>
/// <returns></returns>
///
public DataTable GetProductByLikeGroup(int N)
{
DbTask db = new DbTask();
string Sql = "SELECT top " + N + " a.title, a.fimage, a.id, a.groupid, a.shortlink, a.summary, a.created,b.idx as bidx, b.shortlink as pshortlink from " + TableName + " AS a ";
Sql += " INNER JOIN " + TableGroup + " as b ON a.groupid = b.id ";
Sql += " WHERE a.status = 1 AND a.hotlike = 1 ORDER BY created DESC ";
return(db.GetData(Sql));
}
public DataTable SearchingDefault(int CurrentPage, int RecordPerPages, out int TotalRecords, out int TotalPages)
{
DbTask db = new DbTask();
DataTable dt = null;
TotalRecords = 0;
TotalPages = 0;
string Sql = string.Empty;
Sql = "SELECT COUNT(ID) AS Total FROM " + TableNameDefault + " ";
Sql += "WHERE 1=1 ";
dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
TotalRecords = Convert.ToInt32(dt.Rows[0]["Total"]);
TotalPages = (int)TotalRecords / RecordPerPages;
if ((int)TotalRecords % RecordPerPages > 0)
{
TotalPages = TotalPages + 1;
}
Sql = "Select " + TableNameDefault + ".*, row_number() over (order by id ASC) as row_index INTO #Temp_Table ";
Sql += "From " + TableNameDefault + " ";
Sql += "Where 1 = 1 ";
int nS = RecordPerPages * (CurrentPage - 1);
int record_next = nS + RecordPerPages;
Sql += "Select * from #Temp_Table ";
if (nS == 0)
{
Sql += " WHERE (row_index >=" + nS + ") AND (row_index <=" + record_next + ")";
}
else
{
Sql += " WHERE (row_index >" + nS + ") AND (row_index <=" + record_next + ")";
}
Sql += " ORDER BY row_index ASC ";
dt = db.GetData(Sql);
return(dt);
}
public DataTable SearchingDeTail(string GroupID, int CurrentPage, int RecordPerPages, out int TotalRecords, out int TotalPages, out int Total)
{
string tbl_News = this.TableName;
DbTask db = new DbTask();
DataTable dt = null;
TotalRecords = 0;
TotalPages = 0;
string Sql = string.Empty;
Sql = " SELECT COUNT(ID) AS Total FROM " + tbl_News + " ";
Sql += " WHERE groupid Like '" + GroupID + "%' AND status= 1 ";
dt = db.GetData(Sql);
Total = Convert.ToInt32(dt.Rows[0]["Total"].ToString());
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
TotalRecords = Convert.ToInt32(dt.Rows[0]["Total"]);
TotalPages = (int)TotalRecords / RecordPerPages;
if ((int)TotalRecords % RecordPerPages > 0)
{
TotalPages = TotalPages + 1;
}
Sql = "Select " + tbl_News + ".*, " + TableGroup + ".shortlink as pshortlink , row_number() over (order by " + tbl_News + ".created DESC) as row_index INTO #Temp_Table ";
Sql += "from " + tbl_News + " INNER JOIN " + TableGroup + " ON " + tbl_News + ".groupid = " + TableGroup + ".Id ";
Sql += "where " + tbl_News + ".groupid Like '" + GroupID + "%' AND " + tbl_News + ".status = 1 ";
int nS = RecordPerPages * (CurrentPage - 1);
int record_next = nS + RecordPerPages;
Sql += "Select * from #Temp_Table ";
if (nS == 0)
{
Sql += " WHERE (row_index >=" + nS + ") AND (row_index <=" + record_next + ")";
}
else
{
Sql += " WHERE (row_index >" + nS + ") AND (row_index <=" + record_next + ")";
}
Sql += " ORDER BY row_index ASC ";
dt = db.GetData(Sql);
return(dt);
}
//Moi Nhat
public DataTable GetPublish_Noibat()
{
DbTask db = new DbTask();
string tbl_News = News.TableName;
string Sql = "SELECT " + tbl_News + ".id AS id," + tbl_News + ".title AS title, " + TableName_MoiNhat + ".idx AS idx FROM " + tbl_News + " INNER JOIN " + TableName_MoiNhat + " ON " + tbl_News + ".id = " + TableName_MoiNhat + ".id ";
Sql += "WHERE " + tbl_News + ".status = 1 AND " + tbl_News + ".kind = 0 ";
Sql += "ORDER BY " + TableName_MoiNhat + ".idx ASC, " + tbl_News + ".created DESC ";
return(db.GetData(Sql));
}
//-----------------------------------------------------
public DataTable GetPublish_Ykienkhachhang()
{
DbTask db = new DbTask();
string tbl_News = News.TableName;
string Sql = "SELECT " + tbl_News + ".id AS id," + tbl_News + ".title AS title, " + TableName_Ykienkhachhang + ".idx AS idx FROM " + tbl_News + " INNER JOIN " + TableName_Ykienkhachhang + " ON " + tbl_News + ".id = " + TableName_Ykienkhachhang + ".id ";
Sql += "WHERE " + tbl_News + ".status = 1 ";
Sql += "ORDER BY " + TableName_Ykienkhachhang + ".idx ASC, " + tbl_News + ".created DESC ";
return(db.GetData(Sql));
}
//TAB01
public DataTable GetInforLink_Tab_Home(int IDTAB, int recordPages)
{
DbTask db = new DbTask();
DataTable dt = null;
string Sql = "SELECT Top " + recordPages + " a.*,b.idx as idx FROM " + TableTabLink_Home + " as a," + TableTochucLink_Home + " as b ";
Sql += " WHERE b.idtab = " + IDTAB + " AND a.id = b.linktab_id ORDER BY idx ASC ";
dt = db.GetData(Sql);
return(dt);
}
public DataRow GetInfo(string id)
{
DbTask db = new DbTask();
string Sql = "SELECT * FROM " + TableName + " ";
Sql += " WHERE id = '" + id.Trim() + "' ";
DataTable dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
return null;
return dt.Rows[0];
}
//Voi Root la kieu du lieu (id:varchar 01,0101,010101)
public DataTable GetChild_SiteMap(string ParentID)
{
DbTask dbtask = new DbTask();
string parent = (ParentID == null || ParentID == string.Empty || ParentID == "") ? "00" : ParentID;
string Sql = "Select id AS pid, title AS ptitle, parentid as pparentid, link as plink ";
//Sql += "(SELECT COUNT(id) FROM " + TableName + " WHERE parentid = pid) AS nextchild ";
Sql += "FROM " + TableName + " WHERE parentid = '" + parent + "' ORDER BY idx ";
return(dbtask.GetData(Sql));
}
public DataRow getUsers_Login(string vUser)
{
DbTask db = new DbTask();
string Sql = "SELECT * FROM " + TableName + " WHERE username = N'" + vUser + "' ";
DataTable dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
return null;
return dt.Rows[0];
}
public DataRow GetInfoByUsersName(string username)
{
DbTask db = new DbTask();
string Sql = "SELECT * FROM " + TableName + " ";
Sql += " WHERE username= N'" + username.Trim() + "' ";
DataTable dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
return null;
return dt.Rows[0];
}
public Hashtable[] GetChildGroup(int kind, string strParentID, bool SelectParent)
{
string Sql = "SELECT * FROM " + TableName + " WHERE 1=1 ";
int len = _nLength;
if (_mIdAllow != string.Empty)
{
string arrParentIdAllow = Globals.GetArrParentID(_mIdAllow, strParentID, _nLength);
Sql += "AND id IN(" + arrParentIdAllow + ") ";
}
if (strParentID != null && strParentID != "" && strParentID != string.Empty)
{
len += strParentID.Length;
Sql += "AND id LIKE N'" + strParentID + "%' ";
}
if (kind < 2)
{
Sql += "AND kind = " + kind + " ";
}
Sql += "AND Len(link) < 7 ";
Sql += "AND len(id) = " + len + " ";
Sql += "ORDER BY idx ASC";
DbTask db = new DbTask();
DataTable tb = db.GetData(Sql);
if (tb == null)
{
return(null);
}
Hashtable[] hash_array = new Hashtable[tb.Rows.Count];
int i = 0;
foreach (DataRow dr in tb.Rows)
{
hash_array[i] = new Hashtable(3);
hash_array[i].Add("text", dr["Title"].ToString());
if (NextChild(dr["ID"].ToString()))
{
hash_array[i].Add("src", dr["ID"].ToString());
if (SelectParent)
{
hash_array[i].Add("action", dr["ID"].ToString());
}
}
else
{
hash_array[i].Add("action", dr["ID"].ToString());
}
i++;
}
return(hash_array);
}
/// <summary>
/// Menu context
/// </summary>
/// <param name="childID"></param>
/// <returns></returns>
public DataTable GetMutil_Parent(string childID)
{
DbTask dbtask = new DbTask();
string Sql = "select * from " + TableName + " where id='" + childID + "'";
DataTable dt = dbtask.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
string _childID = dt.Rows[0]["parentid"].ToString();
DataTable dtTmp = null;
while (_childID != "00")
{
Sql = "select * from " + TableName + " where id='" + _childID + "'";
dtTmp = dbtask.GetData(Sql);
if (dtTmp != null && dtTmp.Rows.Count > 0)
{
dt.ImportRow(dtTmp.Rows[0]);
_childID = dtTmp.Rows[0]["parentid"].ToString();
}
else
{
break;
}
}
// return dt; // ve xuoi
// Ve nguoc
DataTable dtTmp1 = null;
dt.AcceptChanges();
dtTmp1 = dt.Clone(); //Copy structure
int n = dt.Rows.Count;
for (int i = n - 1; i >= 0; i--)
{
dtTmp1.ImportRow(dt.Rows[i]);
}
dtTmp1.AcceptChanges();
return(dtTmp1);
}
public DataTable GetSanphamlienquan(string id, string idhoivien)
{
DbTask db = new DbTask();
DataTable dt = null;
string Sql = "Select title, shortlink, fimage, id from " + TableName + " Where idhoivien = ?idhoivien AND status = 1 AND id <> '" + id + "'";
db.AddParameters(ref dt, "idhoivien", DbType.NVarChar, idhoivien);
db.AddParameters(ref dt, "id", DbType.NVarChar, id);
dt = db.GetData(Sql, dt);
return(dt);
}
public DataTable GetChildMenu(string ParentID)
{
DbTask dbtask = new DbTask();
string Sql = "Select a.id AS pid, a.col, a.shortlink, a.icon as icon, a.fimages, a.summary, a.title AS ptitle, a.parentid as pparentid, a.link as plink, a.idx as idx, ";
Sql += "(SELECT COUNT(c.id) FROM " + TableName + " as c WHERE c.parentid = a.id) AS nextchild ";
Sql += " FROM " + TableName + " as a ";
Sql += " WHERE a.parentid = '" + ParentID + "' AND a.status = 1 ";
Sql += "ORDER BY a.idx ASC";
return(dbtask.GetData(Sql));
}
//get child news
public DataTable GetChildNews(string ParentID)
{
DbTask dbtask = new DbTask();
string parent = (ParentID == null || ParentID == string.Empty || ParentID == "") ? "00" : ParentID;
string Sql = "Select c.id AS pid, c.title AS ptitle, c.parentid as pparentid, ";
Sql += "(SELECT COUNT(id) FROM " + TableName + " WHERE parentid = c.id) AS nextchild ";
//Sql += "FROM " + TableName + " AS c WHERE parentid = '" + parent + "' and kind = '0'";
Sql += "FROM " + TableName + " AS c WHERE parentid = '" + parent + "' ";
return(dbtask.GetData(Sql));
}
public DataTable GetSiblings(string GroupID)
{
string Sql = "SELECT * FROM " + TableName + " ";
Sql += "WHERE status =1 ";
Sql += "AND parentid ='" + GroupID + "' ";
Sql += "ORDER BY idx ASC";
DbTask db = new DbTask();
return(db.GetData(Sql));
}
public DataTable Searching(int CurrentPage, int RecordPerPages, out int TotalRecords, out int TotalPages)
{
DbTask db = new DbTask();
DataTable dt = null;
TotalRecords = 0;
TotalPages = 0;
string dtNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string Sql = string.Empty;
Sql = "SELECT COUNT(a.ID) AS Total FROM " + TableName + " as a ";
dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
TotalRecords = Convert.ToInt32(dt.Rows[0]["Total"]);
TotalPages = (int)TotalRecords / RecordPerPages;
if ((int)TotalRecords % RecordPerPages > 0)
{
TotalPages = TotalPages + 1;
}
Sql = "SELECT distinct a.*, row_number() over (order by created DESC) as row_index INTO #Temp_Table FROM " + TableName + " as a ";
Sql += " WHERE a.created <= '" + dtNow + "'";
int nS = RecordPerPages * (CurrentPage - 1);
int record_next = nS + RecordPerPages;
Sql += "Select * from #Temp_Table ";
if (nS == 0)
{
Sql += " WHERE (row_index >=" + nS + ") AND (row_index <=" + record_next + ")";
}
else
{
Sql += " WHERE (row_index >" + nS + ") AND (row_index <=" + record_next + ")";
}
Sql += " ORDER BY row_index ASC ";
dt = db.GetData(Sql);
return(dt);
}
public bool CheckExist(string username)
{
string Sql = "SELECT * FROM " + TableName + " ";
Sql += "WHERE username = N'" + username.Trim() + "' ";
DbTask db = new DbTask();
DataTable dt = db.GetData(Sql);
if (dt != null && dt.Rows.Count > 0)
{
return true;
}
return false;
}
private string GetAllID()
{
string Sql = "SELECT id FROM " + TableName + "";
DbTask db = new DbTask();
DataTable dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
return string.Empty;
string sRet = ",";
foreach (DataRow dr in dt.Rows)
sRet += dr["id"].ToString() + ",";
return sRet;
}
public DataRow GetInfo(string id)
{
string Sql = "SELECT * FROM " + TableName + " WHERE id = '" + id.Trim() + "' ";
DbTask db = new DbTask();
DataTable dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
return(dt.Rows[0]);
}
public DataTable GetMenu()
{
string tbl_Group = Groups.TableName;
string Sql = "SELECT " + tbl_Group + ".id AS id," + tbl_Group + ".title AS title, " + tbl_Group + ".summary AS summary, " + _TableName + ".idx AS idx ";
Sql += " FROM " + tbl_Group + " INNER JOIN " + _TableName + " ON " + tbl_Group + ".id = " + _TableName + ".id ";
Sql += " WHERE " + _TableName + ".groupid = " + _GroupID + " ";
Sql += " ORDER BY " + _TableName + ".idx ASC";
DbTask db = new DbTask();
return(db.GetData(Sql));
}
//No Content Tab4
public DataRow GetName_Tab4(int ID)
{
DbTask db = new DbTask();
string Sql = "SELECT * from " + TableTABnews + " Where id = " + ID + " ";
DataTable dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
return(dt.Rows[0]);
}
public DataRow GetInfoByShortlink(string shortlink)
{
DbTask db = new DbTask();
string Sql = "SELECT id, title, groupid FROM " + TableHoiVien + " WHERE shortlink = '" + shortlink.Trim() + "' ";
DataTable dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
return(dt.Rows[0]);
}
//Log file
public DataTable SearchingLog(string LogID)
{
DbTask db = new DbTask();
DataTable dt = null;
string Sql = string.Empty;
Sql = "SELECT * FROM " + TableName + " ";
Sql += "WHERE LogId ='" + LogID + "' ";
Sql += "ORDER BY Ngaythang DESC ";
dt = db.GetData(Sql);
return(dt);
}
public DataTable CheckShortlinkUrl(string sshortLink)
{
DbTask db = new DbTask();
string Sql = "Select shortlink from " + TableName + " where shortlink = '" + sshortLink + "' ";
DataTable dt = db.GetData(Sql);
if (dt.Rows.Count > 0 && dt != null)
{
return(dt);
}
return(null);
}
public DataTable GetPartner(string groupid)
{
string tbl_Advert = Advert.TableName;
string Sql = "SELECT " + tbl_Advert + ".id AS id," + tbl_Advert + ".title AS title," + tbl_Advert + ".filename AS filename, " + tbl_Advert + ".summary AS summary, " + TableName + ".startdate AS startdate, " + TableName + ".enddate AS enddate, " + TableName + ".idx AS idx FROM " + tbl_Advert + " INNER JOIN " + TableName + " ON " + tbl_Advert + ".id = " + TableName + ".id ";
Sql += "WHERE " + tbl_Advert + ".status = 1 ";
Sql += "AND " + TableName + ".groupid = '" + groupid + "' ";
Sql += "ORDER BY " + TableName + ".idx ASC," + tbl_Advert + ".created DESC ";
DbTask db = new DbTask();
return(db.GetData(Sql));
}
public DataRow GetInfoByShortLink(string shortlink)
{
DbTask db = new DbTask();
string SQL = "Select title, summary, fimages, keywords, titlemeta, Description, kind, id From " + TableName + " where shortlink = '" + shortlink + "'";
DataTable dt = db.GetData(SQL);
if (dt.Rows.Count > 0 && dt != null)
{
return(dt.Rows[0]);
}
return(null);
}
public DataRow GetInfo()
{
string Sql = "SELECT * FROM " + TableName + " ";
DbTask db = new DbTask();
DataTable dt = db.GetData(Sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
return(dt.Rows[0]);
}
public string GetCountSPbyNSXandGroupID(string idnsx, string groupid)
{
DbTask db = new DbTask();
string Sql = "select Count(id) as Tong from tbl_news_vn where idnhasanxuat = '" + idnsx + "' AND groupid = '" + groupid + "' AND status = 1";
DataTable dt = db.GetData(Sql);
if (dt.Rows.Count > 0 && dt != null)
{
return(dt.Rows[0]["tong"].ToString());
}
return(string.Empty);
}