/// <summary>
///根据nodeid 和条件列表，获取所有新闻，includechild为0则不包括子节点的信息，不为0时则包括所有子节点信息（影响性能）
/// </summary>
/// <param name="NodeID"></param>
/// <param name="FiledName"></param>
/// <param name="FiledValue"></param>
/// <param name="includechild"></param>
/// <returns></returns>
public int GetInfoNumByNode(int NodeID, ArrayList FiledName, ArrayList FiledValue, int includechild)
{
int recordcount = 0;
string sbCondition = string.Empty;
sbCondition = CommOperate.SelectCondition(FiledName, FiledValue);
string cmdtxt = string.Empty;
if (NodeID == 0)
{
cmdtxt = "Select count(*) from T_InfoList where 1=1 and IsDelete=0 " + sbCondition.ToString();
}
else
{
if (includechild != 0) //包含子节点
{
NodeOperate nop = new NodeOperate();
ArrayList childlist = nop.GetAllChildNode(NodeID);
string childstring = string.Empty;//子节点列表
for (int i = 0; i < childlist.Count; i++)
{
childstring += childlist[i].ToString() + ",";
}
childstring = childstring.Substring(0, childstring.Length - 1);//截取最后一个"," ,组成类似1,2,3形式
cmdtxt = "Select count(*) from T_InfoList where IsDelete=0 and InfoCateID in(" + childstring.Trim() + ") " + sbCondition.ToString();
}
else
{
cmdtxt = "Select count(*) from T_InfoList where IsDelete=0 and InfoCateID= " + NodeID + " " + sbCondition.ToString();
}
}
// System.Web.HttpContext.Current.Response.Write(cmdtxt);
//System.Web.HttpContext.Current.Response.End();
dh.Open();
dh.BeginTrans();
using (DbDataReader dr = dh.ExecuteReader(CommandType.Text, cmdtxt, null))
{
if (dr.HasRows)
{
dr.Read();
Int32.TryParse(dr[0].ToString(), out recordcount);
}
else
{
recordcount = 0;
}
}
dh.CommitTrans();
dh.Close();
return(recordcount);
}
public DataTable GetInfoByNode(int NodeID, ArrayList FiledName, ArrayList FiledValues, int includechild, string OrderCondition, int startid, int endid)
{
string sbCondition = string.Empty;
sbCondition = CommOperate.SelectCondition(FiledName, FiledValues);
string cmdtxt = string.Empty;
string orderbystring = string.Empty;//排序字符串
if (OrderCondition == "")
{
orderbystring = " order by a.SortID desc,InfoID desc ";
}
else
{
orderbystring = " order by " + OrderCondition + " ";
}
if (NodeID == 0)
{
cmdtxt = "select * from (select InfoID,InfoTitle,InfoSubMemo,InfoMemo,InfoPic, InfoType,a.SortID,IsRecom,InfoCateID,InfoAdder,InfoAddTime,IsCheck,row_number() over (" + orderbystring + ") as rowno,Admin_RealName from T_InfoList a left join T_Admin b on a.InfoAdder=b.Admin_ID where 1=1 and a.IsDelete=0 " + sbCondition + " ) as row where rowno between " + startid + " and " + endid.ToString() + " order by SortID desc,InfoID desc ";
}
else
{
if (includechild != 0)//包含子节点
{
NodeOperate nop = new NodeOperate();
ArrayList childlist = nop.GetAllChildNode(NodeID);
string childstring = string.Empty;//子节点列表
for (int i = 0; i < childlist.Count; i++)
{
childstring += childlist[i].ToString() + ",";
}
childstring = childstring.Substring(0, childstring.Length - 1);
cmdtxt = "select * from (select InfoID,InfoTitle,InfoSubMemo,InfoMemo,InfoPic, InfoType,a.SortID,IsRecom,InfoCateID,InfoAdder,InfoAddTime,IsCheck,row_number() over (" + orderbystring + ") as rowno ,Admin_RealName from T_InfoList a left join T_Admin b on a.InfoAdder=b.Admin_ID where 1=1 and a.IsDelete=0 and InfoCateID in (" + childstring + ") " + sbCondition + " ) as row where rowno between " + startid + " and " + endid.ToString() + " order by SortID desc,InfoID desc ";
}
else
{
cmdtxt = "select * from (select InfoID,InfoTitle,InfoSubMemo,InfoMemo,InfoPic, InfoType,a.SortID,IsRecom,InfoCateID,InfoAdder,InfoAddTime,IsCheck,row_number() over (" + orderbystring + ") as rowno,Admin_RealName from T_InfoList a left join T_Admin b on a.InfoAdder=b.Admin_ID where 1=1 and a.IsDelete=0 and InfoCateID = " + NodeID.ToString() + " " + sbCondition + " ) as row where rowno between " + startid + " and " + endid.ToString() + " order by SortID asc,InfoID desc ";
}
}
DataTable dt;
dh.Open();
dh.BeginTrans();
dt = dh.GetDataTable(CommandType.Text, cmdtxt, null);
dh.CommitTrans();
dh.Close();
return(dt);
}
/// <summary>
/// 获取指定ID，指定数量的信息，按照权重，序号倒叙排列。
/// </summary>
/// <param name="Num">信息数量</param>
/// <param name="NodeID">类别ID</param>
/// <param name="IncludeChild">是否包含子节点信息</param>
/// <param name="NewOrHot">最新或是为最热:1为最新信息，2为最热信息</param>
/// <param name="isRecom">是否为推荐信息</param>
/// <param name="InfoType">信息种类，为0时候不区分信息种类，1为打折资讯，2为一般信息</param>
/// <param name="IsExpired ">是否为将要过期信息</param>
/// <returns></returns>
public DataTable GetLastInfo(int Num, int NodeID, int IncludeChild, int NewOrHot, bool IsRecom, int InfoType, bool IsExpired)
{
string cmdtxt = string.Empty;
string OrderString = string.Empty;
cmdtxt = @"select a.InfoID,a.InfoTitle,a.InfoSubMemo,a.InfoMemo,a.InfoPic, a.InfoType,a.SortID,a.IsRecom
,a.InfoCateID,a.InfoAddTime,InfoTag ,a.InfoClicks,a.IsRecom,b.NodeName
,c.ShopID,c.ShopTitle
from T_InfoList a inner join T_NodeList b on a.InfoCateID=b.NodeId
left join T_Shop c on a.ShopID =c.ShopID
where 1=1 and a.IsDelete=0 ";
DataTable NewsList = new DataTable();
if (NodeID == 0)
{
}
else
{
if (IncludeChild != 0)//包含子节点
{
NodeOperate nop = new NodeOperate();
ArrayList childlist = nop.GetAllChildNode(NodeID);
string childstring = string.Empty;//子节点列表
for (int i = 0; i < childlist.Count; i++)
{
childstring += childlist[i].ToString() + ",";
childstring = childstring.Substring(0, childstring.Length - 1);
cmdtxt = cmdtxt + " and InfoCateID in (" + childstring + ") ";
}
}
else
{
cmdtxt = cmdtxt + " and InfoCateID =" + NodeID.ToString() + " ";
}
}
//最新信息
if (NewOrHot == 1)
{
OrderString = "Order by a.SortID desc, a.InfoID desc";
}
//最热信息
else if (NewOrHot == 2)
{
OrderString = "Order by a.SortID desc, a.InfoClicks desc,a.InfoID desc";
}
if (IsRecom == true)
{
cmdtxt = cmdtxt + " and a.IsRecom=1 ";
}
if (InfoType == 1)
{
cmdtxt = cmdtxt + " and InfoType=1 ";
}
else if (InfoType == 2)
{
cmdtxt = cmdtxt + " and InfoType=2 ";
}
if (IsExpired == true)
{
cmdtxt = cmdtxt + " and InfoEndTime is not null and rtrim(ltrim(InfoEndTime)) <> '' and InfoEndTime < '" + DateTime.Now + "' ";
}
cmdtxt = cmdtxt + " " + OrderString;
dh.Open();
dh.BeginTrans();
NewsList = dh.GetDataTable(CommandType.Text, cmdtxt, null);
dh.CommitTrans();
dh.Close();
return(NewsList);
}
