public static List<ZtDbList> GetDatat(int PageIndex, int PageSize, out int count)
{
PQDataContext db = new PQDataContext();
var rs = from item in db.ZtDbList
where item.dbType == 0 && item.IsDel == false
orderby item.CreateTime
select item;
count = rs.Count();
return rs.Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList<ZtDbList>();
}
protected void Page_Load(object sender, EventArgs e)
{
PQDataContext db = new PQDataContext();
IQueryable<ZtDbList> result = from x in db.ZtDbList
where x.dbType.Value == 0
select x;
this.ztnamelist.DataSource = result;
this.ztnamelist.DataTextField = "ztDbName";
this.ztnamelist.DataValueField = "DbID";
this.ztnamelist.DataBind();
}
private string getNodes(string zid, string PId,int userid)
{
StringBuilder sb = new StringBuilder();
PQDataContext db = new PQDataContext();
IQueryable<ztTree> tree;
if (PId == "")
{
tree = from x in db.ztTree
where x.PNid == PId && x.CreateUserId == userid && x.isdel == false && x.zid==zid
orderby x.NodeId ascending
select x;
if (tree.Count() > 0)
{
sb.Append(",\"children\":[");
//sb.Append("[");
foreach (var x in tree)
{
sb.Append("{");
sb.Append(string.Format("\"id\":\"{1}\",\"text\":\"{2}\"", "{", x.Nid, x.NodeName.Trim()));
if (x.IsParent == 1)
{
string result = getNodes(zid, x.Nid, userid);
sb.Append(result);
}
sb.Append("},");
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
}
else
{
tree = from x in db.ztTree
where x.zid==zid && x.PNid == PId && x.isdel == false
orderby x.NodeId ascending
select x;
if (tree.Count() > 0)
{
sb.Append(",\"children\":[");
foreach (var x in tree)
{
sb.Append("{");
sb.Append(string.Format("\"id\":\"{1}\",\"text\":\"{2}\"", "{", x.Nid, x.NodeName.Trim()));
if (x.IsParent == 1)
{
string result = getNodes(zid, x.Nid, userid);
sb.Append(result);
}
sb.Append("},");
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
}
return sb.ToString();
}
/// <summary>
/// 得到某一用户的，某一节点的分页数据
/// </summary>
/// <param name="UserId"></param>
/// <param name="AlbumId"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <returns></returns>
public static List<int> GetResultList(string type, int AlbumId)
{
type = type.ToUpper();
int userid = Convert.ToInt32(HttpContext.Current.Session["Userid"].ToString());
List<int> list = null;
PQDataContext db = new PQDataContext();
list = (from th in db.TLC_Collects
where th.AlbumId == AlbumId && th.Type == type && th.UserId == userid && th.isdel == false
orderby th.CollectId descending
select th.Pid.Value).ToList<int>();
return list;
}
/// <summary>
/// 得到某一用户的，某一节点的分页数据
/// </summary>
/// <param name="UserId"></param>
/// <param name="AlbumId"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <returns></returns>
public static List<int> GetResultList(string type, int AlbumId, int PageIndex, int PageSize, out int ItemCount, string filter, string Query)
{
type = type.ToUpper();
int userid = Convert.ToInt32(HttpContext.Current.Session["Userid"].ToString());
List<int> list = null;
try
{
PQDataContext db = new PQDataContext();
switch (filter.ToUpper())
{
case "ALL":
list = (from th in db.TLC_Collects
where th.AlbumId == AlbumId && th.Type == type && th.UserId == userid && th.Note.Contains(Query) && th.isdel == false
orderby th.CollectId descending
select th.Pid.Value).Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList<int>();
ItemCount = (from th in db.TLC_Collects
where th.AlbumId == AlbumId && th.Type == type && th.UserId == userid && th.Note.Contains(Query) && th.isdel == false
select th).Count();
break;
case "YES":
list = (from th in db.TLC_Collects
where th.AlbumId == AlbumId && th.Type == type && th.UserId == userid && th.NoteDate.Value != null && th.Note.Contains(Query) && th.isdel == false
orderby th.CollectId descending
select th.Pid.Value).Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList<int>();
ItemCount = (from th in db.TLC_Collects
where th.AlbumId == AlbumId && th.Type == type && th.UserId == userid && th.NoteDate.Value != null && th.Note.Contains(Query) && th.isdel == false
select th).Count();
break;
case "NO":
list = (from th in db.TLC_Collects
where th.AlbumId == AlbumId && th.Type == type && th.UserId == userid && th.NoteDate.Value == null && th.Note.Contains(Query) && th.isdel == false
orderby th.CollectId descending
select th.Pid.Value).Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList<int>();
ItemCount = (from th in db.TLC_Collects
where th.AlbumId == AlbumId && th.Type == type && th.UserId == userid && th.NoteDate.Value == null && th.Note.Contains(Query) && th.isdel == false
select th).Count();
break;
default:
ItemCount = 0;
break;
}
return list;
}
catch (Exception ex)
{
//logger.Error(ex.ToString());
ItemCount = 0;
return null;
}
}
public static string GetNodes(int PId)
{
int userid = Convert.ToInt32(HttpContext.Current.Session["Userid"].ToString());
StringBuilder sb = new StringBuilder();
PQDataContext db = new PQDataContext();
IQueryable<TLC_Albums> tree;
if (PId == 0)
{
tree = from x in db.TLC_Albums
where x.UserId == userid && x.ParentId == PId && x.isdel == false
orderby x.AlbumId ascending
select x;
if (tree.Count() > 0)
{
sb.Append("[");
foreach (var x in tree)
{
sb.Append(string.Format("{0}\"id\":\"{1}\",\"text\":\"{2}\",\"state\":\"{3}\",\"attributes\":{0}\"live\":\"{4}\",\"des\":\"{5}\"{6}", "{", x.AlbumId, x.Title.Trim(), "open", x.live, x.Note, "}"));
//sb.Append(string.Format("{0}\"id\":\"{1}\",\"name\":\"{2}\",\"open\":\"{3}\"", "{", x.AlbumId, x.Title, "true"));
var subnoNote = from node in db.TLC_Albums
where node.UserId == userid && node.ParentId == x.AlbumId && node.isdel == false
orderby x.AlbumId ascending
select node;
if (subnoNote.Count() > 0)
{
sb.Append(",\"children\": [");
foreach (var sbn in subnoNote)
{
sb.Append(string.Format("{0}\"id\":\"{1}\",\"text\":\"{2}\",\"state\":\"{3}\",\"attributes\":{0}\"live\":\"{4}\",\"des\":\"{5}\"{6}{7}", "{", sbn.AlbumId, sbn.Title.Trim(), (sbn.IsParent == 0 ? "open" : "closed"), sbn.live, sbn.Note, "}", "},"));
//sb.Append(string.Format("{0}\"id\":\"{1}\",\"name\":\"{2}\",\"isParent\":\"{3}\"{4}", "{", sbn.AlbumId, sbn.Title, (sbn.IsParent == 0 ? "false" : "true"), "},"));
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
sb.Append("},");
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
}
else
{
tree = from x in db.TLC_Albums
where x.UserId == userid && x.ParentId == PId && x.isdel == false
orderby x.AlbumId ascending
select x;
if (tree.Count() > 0)
{
sb.Append("[");
foreach (var x in tree)
{
sb.Append(string.Format("{0}\"id\":\"{1}\",\"text\":\"{2}\",\"state\":\"{3}\",\"attributes\":{0}\"live\":\"{4}\",\"des\":\"{5}\"{6}{7}", "{", x.AlbumId, x.Title.Trim(), (x.IsParent == 0 ? "open" : "closed"), x.live, x.Note, "}", "},"));
//sb.Append(string.Format("{0}\"id\":\"{1}\",\"name\":\"{2}\",\"isParent\":\"{3}\"{4}", "{", x.AlbumId, x.Title, (x.IsParent == 0 ? "false" : "true"), "},"));
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
}
return sb.ToString();
}
/// <summary>
/// 得到top 10
/// </summary>
/// <param name="UserId"></param>
/// <param name="AlbumId"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <returns></returns>
public static DataTable GetHotTop(int top)
{
try
{
PQDataContext db = new PQDataContext();
DataTable list = DBA.SqlDbAccess.GetDataTable(CommandType.Text, "select top " + top.ToString() + " pid,appno,title, [type],'' as Number,COUNT(*) as [count] from TLC_Collects group by pid,appno,Title,[type] order by COUNT(*) desc");
return list;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 得到专题库某一节点的子节点
/// </summary>
/// <param name="zid"></param>
/// <param name="PId"></param>
/// <param name="fileter"></param>
/// <returns></returns>
public static string GetNodes(string zid, string PId, string fileter)
{
StringBuilder sb = new StringBuilder();
PQDataContext db = new PQDataContext();
IQueryable<ztTree> tree;
if (PId == "")
{
tree = from x in db.ztTree
where x.zid == zid && x.PNid == PId && x.isdel == false
orderby x.NodeId ascending
select x;
if (tree.Count() > 0)
{
sb.Append("[");
foreach (var x in tree)
{
sb.Append(string.Format("{0}\"id\":\"{1}\",\"text\":\"{2}\",\"state\":\"{3}\",\"attributes\":{0}\"live\":\"{4}\",\"des\":\"{5}\",\"Nid\":\"{7}\"{6}", "{", x.NodeId, x.NodeName.Trim(), "open", x.live, x.des.Trim(), "}", x.Nid));
//sb.Append(string.Format("{0}\"id\":\"{1}\",\"name\":\"{2}\",\"open\":\"{3}\"", "{", x.NodeId, x.NodeName, "true"));
var subnodes = from node in db.ztTree
where node.PNid == x.Nid && node.isdel == false
orderby x.NodeId ascending
select node;
if (subnodes.Count() > 0)
{
sb.Append(",\"children\": [");
foreach (var sbn in subnodes)
{
sb.Append(string.Format("{0}\"id\":\"{1}\",\"text\":\"{2}\",\"state\":\"{3}\",\"attributes\":{0}\"live\":\"{4}\",\"des\":\"{5}\",\"Nid\":\"{8}\"{6}{7}", "{", sbn.NodeId, sbn.NodeName.Trim(), (sbn.IsParent == 0 ? "open" : "closed"), sbn.live, sbn.des.Trim(), "}", "},", sbn.Nid));
//sb.Append(string.Format("{0}\"id\":\"{1}\",\"name\":\"{2}\",\"isParent\":\"{3}\"{4}", "{", sbn.NodeId, sbn.NodeName, (sbn.IsParent == 0 ? "false" : "true"), "},"));
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
sb.Append("},");
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
}
else
{
var tmp = from y in db.ztTree
where y.NodeId.ToString() == PId
select y.Nid;
if (tmp.Count() > 0)
{
PId = tmp.First();
tree = from x in db.ztTree
where x.PNid == PId && x.isdel == false
orderby x.NodeId ascending
select x;
if (tree.Count() > 0)
{
sb.Append("[");
foreach (var x in tree)
{
sb.Append(string.Format("{0}\"id\":\"{1}\",\"text\":\"{2}\",\"state\":\"{3}\",\"attributes\":{0}\"live\":\"{4}\",\"des\":\"{5}\",\"Nid\":\"{8}\"{6}{7}", "{", x.NodeId, x.NodeName.Trim(), (x.IsParent == 0 ? "open" : "closed"), x.live, x.des.Trim(), "}", "},", x.Nid));
//sb.Append(string.Format("{0}\"id\":\"{1}\",\"name\":\"{2}\",\"isParent\":\"{3}\"{4}", "{", x.NodeId, x.NodeName, (x.IsParent == 0 ? "false" : "true"), "},"));
}
sb.Remove(sb.Length - 1, 1);
sb.Append("]");
}
}
}
return sb.ToString();
}
/// <summary>
/// 得到某一个用户，某一节点的数据总数
/// </summary>
/// <param name="CreateUserId"></param>
/// <param name="NodeId"></param>
/// <returns></returns>
public static int GetItemCountByNodeId(int NodeId, string type, string isupdate)
{
bool isup = false;
if (isupdate.ToUpper() == "UP") isup = true;
PQDataContext db = new PQDataContext();
return (from th in db.ztdb
where th.NodeId == NodeId && th.type == type && th.isdel == false && th.isUpdate == isup
select th.Pid.Value).Count();
}
/// <summary>
/// 得到某一个用户，某一节点的数据总数
/// </summary>
/// <param name="CreateUserId"></param>
/// <param name="NodeId"></param>
/// <returns></returns>
public static int GetItemCountByNodeId(int NodeId, string type)
{
PQDataContext db = new PQDataContext();
return (from th in db.ztdb
where th.NodeId == NodeId && th.type == type && th.isdel == false
select th.Pid.Value).Count();
}
