public DataTable GetListByFamousSysNo(int pageindex,int pagesize,int famous,ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"SYS_Famous_Comment.[SysNo]
,[FamousSysNo]
,[CustomerSysNo]
,[Context]
,SYS_Famous_Comment.[DR]
,SYS_Famous_Comment.[TS]
,USR_Customer.[Photo]
,USR_Customer.[NickName]";
tables = "SYS_Famous_Comment left join USR_Customer on [CustomerSysNo] = USR_Customer.SysNo";
order = "SYS_Famous_Comment.SysNo desc";
where = "FamousSysNo=" + famous + " and SYS_Famous_Comment.dr=" + (int)AppEnum.State.normal;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetArticleComments(int ArticleSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "BLG_Comment.SysNo desc";
#region 设置参数
columns = @"BLG_Comment.SysNo
, ArticleSysNo
, Title
, Context
, CustomerSysNo
, Love
, Hate
, BLG_Comment.TS
,[NickName]
,[Photo]
,[Credit]
,[Point]";
tables = "BLG_Comment left join USR_Customer on CustomerSysNo=USR_Customer.SysNo";
where = "BLG_Comment.ArticleSysNo = " + ArticleSysno + " and BLG_Comment.dr=" + (int)AppEnum.State.normal + " and USR_Customer.dr=" + (int)AppEnum.State.normal;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, string name, int except, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"SysNo, [Name], [Description], Logic, Type, DR";
tables = "RSH_BaziLogic";
order = "SysNo desc";
where = "dr=0";
if (name != "")
{
where += " and (";
string[] tmpstr = name.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " [Name] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' and ";
}
where += " 1=1)";
}
if (except != 0 && except != AppConst.IntNull)
{
where += " and SysNo <>" + except;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "*";
string tables = "USR_Notice";
string where = "1=1";
string order = "SysNo desc";
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetListByCustomer(int customersysno, int type)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"REL_Customer_Category.*,name";
tables = "REL_Customer_Category";
order = "REL_Customer_Category.sysno desc";
where = "1=1";
if (customersysno != 0)
{
where += " and CustomerSysNo=" + customersysno;
}
if (type != 0)
{
where += " and REL_Customer_Category.type=" + type;
}
switch (type)
{
case (int)AppEnum.CategoryType.QA:
tables += " left join QA_Category on CategorySysNo=QA_Category.sysno";
break;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", 1);
m_data.AddParameter("pagesize", 1000);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, int customersysno, int type,int productsysno, int status, string orderby, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"[SysNo]
,[OrderID]
,[CustomerSysNo]
,[ProductSysNo]
,[Point]
,[Type]
,[TS]
,[Status]";
tables = "ORD_Point ";
where = "1=1";
if (customersysno != 0)
{
where += " and CustomerSysNo=" + customersysno;
}
if (type != AppConst.IntNull)
{
where += " and Type=" + type;
}
if (status != AppConst.IntNull)
{
where += " and status=" + status;
}
if (productsysno != 0)
{
where += " and productsysno=" + productsysno;
}
if (orderby == "timedown")
{
order = "TS desc";
}
else if (orderby == "timeup")
{
order = "TS asc";
}
else
{
order = "TS desc";
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetListByCate(int catesysno, int type)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"USR_Customer.*";
tables = "REL_Customer_Category left join USR_Customer on USR_Customer.sysno=customersysno";
order = "REL_Customer_Category.sysno asc";
where = "1=1";
if (catesysno != 0)
{
where += " and CategorySysNo=" + catesysno;
}
if (type != 0)
{
where += " and REL_Customer_Category.type=" + type;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", 1);
m_data.AddParameter("pagesize", 1000);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
}
}
return m_dt;
}
/// <summary>
/// 获取该用户轻博客首页内容
/// </summary>
/// <param name="UserSysno"></param>
/// <param name="pagesize"></param>
/// <param name="pageindex"></param>
/// <param name="total"></param>
/// <returns></returns>
public DataTable GetAttendtionArticle(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "dbo.BLG_Article.SysNo desc";
#region 设置参数
columns = @"BLG_Article.SysNo
, Title
, Context
, CustomerSysNo
, LastReplyTime
, Love
, Hate
, Spread
, Type
, TargetUrl
, ChartSysNo
, CommentCount
, BLG_Article.TS
,[NickName]
,[Photo]
,[Credit]
,[Point]";
tables = "dbo.BLG_Article left join USR_Customer on CustomerSysNo=USR_Customer.SysNo";
where = "dbo.BLG_Article.CustomerSysNo in (select targetsysno from BLG_Attention where CustomerSysNo=" + UserSysno + " and BLG_Attention.dr=" + (int)AppEnum.State.normal + " aaa) and dbo.BLG_Article.dr=" + (int)AppEnum.State.normal + " and USR_Customer.dr=" + (int)AppEnum.State.normal;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, string name, int isinput, DateTime timeBegin, DateTime timeEnd,string nation, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = "SysNo, FamousName, Birthtmp, FamousSysNo, HomeTown, IsUnknow, AstroThemeID, Gender";
tables = "SPD_Famous";
order = "SysNo asc";
where = "1=1";
if (name != "")
{
where += " and (";
string[] tmpstr = name.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " FamousName like '%" + tmpstr[i] + "%' and ";
}
where += " 1=1)";
}
if (isinput == 1)
{
where += " and FamousSysNo is not null";
}
else if(isinput == 0)
{
where += " and FamousSysNo is null";
}
if (timeBegin != AppCmn.AppConst.DateTimeNull)
{
where += " and Birth>='" +timeBegin.ToString("yyyy-MM-dd 00:00:00")+"'";
}
if (timeEnd != AppCmn.AppConst.DateTimeNull)
{
where += " and Birth<='" + timeEnd.ToString("yyyy-MM-dd 23:59:59")+"'";
}
if (nation != "")
{
where += " and hometown like '%" + nation + "%'";
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
/// <summary>
/// 获取用户关注者的动态
/// </summary>
/// <param name="UserSysno"></param>
/// <param name="pagesize"></param>
/// <param name="pageindex"></param>
/// <param name="total"></param>
/// <returns></returns>
public DataTable GetUserAction(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "USR_Action.SysNo desc";
#region 设置参数
columns = @"USR_Action.[SysNo]
,USR_Action.[CustomerSysNo]
,USR_Action.[xml]
,USR_Action.[Type]
,USR_Action.[TS]
,[NickName]
,[Photo]
,[Credit]
,[Point]";
tables = "USR_Action left join USR_Customer on CustomerSysNo=USR_Customer.SysNo";
where = "USR_Action.CustomerSysNo in (select targetsysno from BLG_Attention where CustomerSysNo=" + UserSysno + " and BLG_Attention.dr=" + (int)AppEnum.State.normal + " aaa) and USR_Action.dr=" + (int)AppEnum.State.normal + " and USR_Customer.dr=" + (int)AppEnum.State.normal;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetFamousCollection(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "BLG_Collection.SysNo desc";
#region 设置参数
columns = @"BLG_Collection.[SysNo]
,BLG_Collection.[CustomerSysNo]
,BLG_Collection.[Name]
,[Type]
,[RefSysNo]
,BLG_Collection.[TS]
,BLG_Collection.[DR]
, SYS_Famous.Name as FamousName
, Description
, BirthYear
, BirthTime
, Death
, CateSysNo
, Level
, SYS_Famous.CustomerSysNo as CreateCustomerSysNo
, Source
, Gender
, HomeTown
, Height
, FullName
, TimeUnknown
, CollectCount ";
tables = "BLG_Collection left join SYS_Famous on RefSysNo=SYS_Famous.SysNo";
where = "BLG_Collection.CustomerSysNo = " + UserSysno + " and BLG_Collection.dr=" + (int)AppEnum.State.normal + " and SYS_Famous.dr=" + (int)AppEnum.State.normal + " and Type=" + (int)AppEnum.CollectionType.famous;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetSimpleListByQuest(int qid)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "[QA_Answer].TS asc";
#region 设置参数
columns = @"[QA_Answer].[SysNo]
,[CustomerSysNo]
,[QA_Answer].[TS]";
tables = "[QA_Answer]";
where = "[QA_Answer].dr=" + (int)AppEnum.State.normal;
if (qid != 0)
{
where += " and QuestionSysNo=" + qid;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", 1);
m_data.AddParameter("pagesize", 1000);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, string key, int cate,int usersysno, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "CMS_ArticleView.OrderID desc, CMS_ArticleView.SysNo desc";
#region 设置参数
columns = @"CMS_ArticleView.[SysNo]
,[ArticleSysNo]
,[CateSysNo]
,[Name]
,[Source]
,CMS_ArticleView.[DR]
,CMS_ArticleView.[TS]
,[OrderID]
,[Title]
,[CustomerSysNo]
,[KeyWords]
,[Limited]
,[ReadCount]
,[Description]
,[Cost]";
tables = "CMS_ArticleView left join CMS_Category on CateSysNo = CMS_Category.SysNo";
where = "CMS_ArticleView.dr=" + (int)AppEnum.State.normal;
if (key != "")
{
where += " and (";
string[] tmpstr = key.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " ([Title] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' or KeyWords like '%" + SQLData.SQLFilter(tmpstr[i]) + "%') and ";
}
where += " 1=1)";
}
if (cate != 0)
{
where += " and CateSysNo in (select SysNo from CMS_Category where SysNo=" + cate + " or ParentSysNo=" + cate + " or TopSysNo=" + cate + ")";
}
if (usersysno != 0)
{
where += " and CustomerSysNo=" + usersysno;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetListByQuestForConsult(int pagesize, int pageindex, int qid, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "[QA_Answer].TS asc";
#region 设置参数
columns = @"[QA_Answer].[SysNo]
,[QA_Answer].[QuestionSysNo]
,[QA_Answer].[CustomerSysNo]
,[Title]
,[Context]
,[Love]
,[Hate]
,[Award]
,[QA_Answer].[DR]
,[QA_Answer].[TS]
,NickName
,Point
,TotalAnswer
,BestAnswer
,photo
,USR_Grade.Name
,USR_Grade.LevelNum
,Price
,QA_Order.status
,QA_Order.SysNo as ordersysno
,description
,replytime
,score
,trial
,Words";
tables = "[QA_Answer] left join USR_Customer on CustomerSysNo=USR_Customer.SysNo left join USR_Grade on GradeSysNo = USR_Grade.SysNo left join QA_Order on QA_Answer.sysno = QA_Order.answersysno";
where = "[QA_Answer].dr=" + (int)AppEnum.State.normal;
if (qid != 0)
{
where += " and [QA_Answer].QuestionSysNo=" + qid;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetListByUser(int pagesize, int pageindex, int usersysno, string key, int cate, bool best, string orderby, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"QA_Answer.*
,QA_Question.[SysNo] as QuestSysNo
,[CateSysNo]
,QA_Question.[CustomerSysNo] as QuestCustomer
,QA_Question.[Title] as QuestTitle
,Photo
,NickName";
tables = " QA_Answer left join QA_Question on QA_Answer.QuestionSysNo = QA_Question.SysNo left join USR_Customer on QA_Question.CustomerSysNo=USR_Customer.SysNo";
where = "QA_Answer.CustomerSysNo=" + usersysno + " and QA_Answer.dr=" + (int)AppEnum.State.normal;
if (key != "")
{
where += " and (";
string[] tmpstr = key.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " QA_Answer.[Title] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' and ";
}
where += " 1=1)";
}
if (best)
{
where += " and QA_Answer.Award>0";
}
if (cate != 0)
{
where += " and CateSysNo=" + cate;
}
if (orderby == "timedown")
{
order = "QA_Answer.TS desc";
}
else if (orderby == "timeup")
{
order = "QA_Answer.TS asc";
}
else if (orderby == "replydown")
{
order = "ReplyCount desc";
}
else if (orderby == "replyup")
{
order = "ReplyCount asc";
}
else if (orderby == "pointdown")
{
order = "QA_Answer.Award desc";
}
else if (orderby == "pointup")
{
order = "QA_Answer.Award asc";
}
else
{
order = "QA_Answer.TS desc";
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetListByQuestForAdmin(int pagesize, int pageindex, int qid,int user, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "[QA_Answer].TS desc";
#region 设置参数
columns = @"[QA_Answer].[SysNo]
,[QuestionSysNo]
,[CustomerSysNo]
,[Title]
,[Context]
,[Love]
,[Hate]
,[Award]
,[QA_Answer].[DR]
,[QA_Answer].[TS]
,NickName
,photo";
tables = "[QA_Answer] left join USR_Customer on CustomerSysNo=USR_Customer.SysNo";
where = "[QA_Answer].dr=" + (int)AppEnum.State.normal;
if (qid != 0)
{
where += " and QuestionSysNo=" + qid;
}
if (user != 0)
{
where += " and CustomerSysNo=" + user;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetListForAdmin(int pagesize, int pageindex, string key, int cate,int user,int reply,int award,int status, string orderby, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"QA_Question.[SysNo]
,[CateSysNo]
,[CustomerSysNo]
,[Title]
,[Context]
,[Award]
,[EndTime]
,[IsSecret]
,[LastReplyTime]
,[ReplyCount]
,QA_Question.[DR]
,QA_Question.[TS]
,Name
,NickName";
tables = "QA_Question left join USR_Customer on CustomerSysNo=USR_Customer.SysNo left join QA_Category on CateSysNo=QA_Category.sysno";
where = "1=1";
if (key != "")
{
where += " and (";
string[] tmpstr = key.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " QA_Question.[Title] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' and ";
}
where += " 1=1)";
}
if (cate != 0)
{
where += " and CateSysNo=" + cate;
}
if (user != 0)
{
where += " and CustomerSysNo=" + user;
}
if (award != 0)
{
where += " and Award>=" + award;
}
if (reply != 0)
{
where += " and ReplyCount>=" + reply;
}
if (status != 100)
{
where += " and QA_Question.DR=" + status;
}
if (orderby == "timedown")
{
order = "QA_Question.TS desc";
}
else if (orderby == "timeup")
{
order = "QA_Question.TS asc";
}
else if (orderby == "replydown")
{
order = "ReplyCount desc";
}
else if (orderby == "replyup")
{
order = "ReplyCount asc";
}
else if (orderby == "pointdown")
{
order = "Award desc";
}
else if (orderby == "pointup")
{
order = "Award asc";
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, string key,int cate, string orderby, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"QA_Question.[SysNo]
,[CateSysNo]
,[CustomerSysNo]
,[Title]
,[Context]
,[Award]
,[buycount]
,[ordercount]
,[EndTime]
,[IsSecret]
,[LastReplyTime]
,[LastReplyUser]
,[ReplyCount]
,[ReadCount]
,QA_Question.[DR]
,QA_Question.[TS]
,a.NickName
,a.Photo
,b.NickName as ReplyNickName
,b.Photo as ReplyPhoto";
tables = "QA_Question left join USR_Customer a on CustomerSysNo=a.SysNo left join USR_Customer b on LastReplyUser=b.SysNo";
where = "dr=" + (int)AppEnum.State.normal;
if (!string.IsNullOrEmpty(key))
{
where += " and (";
string[] tmpstr = key.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " QA_Question.[Title] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' and ";
}
where += " 1=1)";
}
if (cate != 0)
{
where += " and CateSysNo in (select sysno from QA_Category where TopSysNo=" + cate+" or sysno="+ cate+")";
}
if (orderby == "timedown")
{
order = "QA_Question.LastReplyTime desc";
}
else if (orderby == "timeup")
{
order = "QA_Question.LastReplyTime asc";
}
else if (orderby == "replydown")
{
order = "ReplyCount desc";
}
else if (orderby == "replyup")
{
order = "ReplyCount asc";
}
else if (orderby == "replytimedown")
{
order = "LastReplyTime desc";
where += " and ReplyCount>0";
}
else if (orderby == "replytimeup")
{
order = "LastReplyTime asc";
where += " and ReplyCount>0";
}
else if (orderby == "pointdown")
{
order = "Award desc";
}
else if (orderby == "pointup")
{
order = "Award asc";
}
else
{
order = "QA_Question.LastReplyTime desc";
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(string name, int cate, int type)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"REL_Customer_Category.*,nickname,Email,name";
tables = "REL_Customer_Category left join USR_Customer on USR_Customer.SysNo=CustomerSysNo";
order = "REL_Customer_Category.sysno desc";
where = "1=1";
if (name != "")
{
where += " and (";
string[] tmpstr = name.Split(new char[] { ' ', '@' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " (USR_Customer.[Email] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' or USR_Customer.[NickName] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%') and ";
}
where += " 1=1)";
}
if (cate != 0)
{
where += " and CategorySysNo=" + cate;
}
if (type != 0)
{
where += " and REL_Customer_Category.type=" + type;
}
switch (type)
{
case (int)AppEnum.CategoryType.QA:
tables += " left join QA_Category on CategorySysNo=QA_Category.sysno";
break;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", 1);
m_data.AddParameter("pagesize", 1000);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
}
}
return m_dt;
}
public DataTable GetUrlCollection(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "BLG_Collection.SysNo desc";
#region 设置参数
columns = @"BLG_Collection.[SysNo]
,[CustomerSysNo]
,BLG_Collection.[Name]
,[Type]
,[RefUrl]
,Detail
,BLG_Collection.[TS]
,BLG_Collection.[DR]";
tables = "BLG_Collection";
where = "CustomerSysNo = " + UserSysno + " and BLG_Collection.dr=" + (int)AppEnum.State.normal + " and Type=" + (int)AppEnum.CollectionType.url;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, string name, string status, int privilege, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"SYS_Admin.*,Email,NickName";
tables = "SYS_Admin left join USR_Customer on Customersysno=USR_Customer.sysno";
order = "SYS_Admin.SysNo desc";
where = "1=1";
if (name != "")
{
where += " and ((";
string[] tmpstr = name.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " ([name] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%') and ";
}
where += " 1=1) or (";
for (int i = 0; i < tmpstr.Length; i++)
{
where += " ([Email] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%') and ";
}
where += " 1=1) or (";
for (int i = 0; i < tmpstr.Length; i++)
{
where += " ([NickName] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%') and ";
}
where += " 1=1))";
}
if (privilege != 0 && privilege != AppConst.IntNull)
{
where += " and SYS_Admin.sysno in (select AdminSysNo from Rel_AdminPrivilege where PrivilegeSysNo=" + privilege + ")";
}
if (status != "" && status != "100")
{
where += " and SYS_Admin.[dr]=" + status;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetListByKeys(int pagesize, int pageindex, int key, string status, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"SYS_Famous.[SysNo]
,SYS_Famous.[Name]
,Description
,[FullName]
,[BirthYear]
,[BirthTime]
,[Source]
,[Gender]
,[HomeTown]
,[Name1]
,[Name3]
,[EnglishName1]
,[EnglishName3]
,[TimeUnknown]
,[IsTop]
,[photo]
,SYS_Famous.[DR]";
tables = "SYS_Famous left join District3Level on [HomeTown] = District3Level.SysNo3";
order = "SYS_Famous.SysNo asc";
where = "1=1";
if (key != 0)
{
where += " and SYS_Famous.[SysNo] in (select Famous_SysNo from REL_Famous_KeyWord where KeyWord_SysNo="+key+")";
}
if (status != "" && status != "100")
{
where += " and dr=" + status;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetChartCollection(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "BLG_Collection.SysNo desc";
#region 设置参数
columns = @"BLG_Collection.[SysNo]
,[CustomerSysNo]
,[Name]
,[Detail]
,[Type]
,[RefSysNo]
,BLG_Collection.[TS]
,BLG_Collection.[DR]
, FirstBirth
, FirstPoi
, Transit
, TransitPoi
, SecondBirth
, SecondPoi
, CharType
, TheoryType
, Bitvalue";
tables = "BLG_Collection left join Fate_Chart on RefSysNo=Fate_Chart.SysNo";
where = "CustomerSysNo = "+UserSysno+" and BLG_Collection.dr=" + (int)AppEnum.State.normal + " and Type=" + (int)AppEnum.CollectionType.chart;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, string name, DateTime timeBegin, DateTime timeEnd, string status, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"*";
tables = "USR_Customer";
order = "USR_Customer.SysNo desc";
where = "1=1";
if (name != "")
{
where += " and (";
string[] tmpstr = name.Split(new char[] { ' ','@' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " (USR_Customer.[Email] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' or USR_Customer.[NickName] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%') and ";
}
where += " 1=1)";
}
if (timeBegin != AppCmn.AppConst.DateTimeNull)
{
where += " and (RegTime>" + timeBegin.Year.ToString() +
" or (RegTime=" + timeBegin.Year.ToString() + " and RegTime>='" + timeBegin.AddYears(AppConst.DateTimeNull.Year - timeBegin.Year).ToString("yyyy-MM-dd 00:00:00") + "'))";
}
if (timeEnd != AppCmn.AppConst.DateTimeNull)
{
where += " and (RegTime<" + timeEnd.Year.ToString() +
" or (RegTime=" + timeEnd.Year.ToString() + " and RegTime<='" + timeEnd.AddYears(AppConst.DateTimeNull.Year - timeEnd.Year).ToString("yyyy-MM-dd 23:59:59") + "'))";
}
if (status != "" && status != "100")
{
where += " and [Status]=" + status;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetQuestCollection(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "BLG_Collection.SysNo desc";
#region 设置参数
columns = @"BLG_Collection.[SysNo]
,QA_Question.SysNo as QuestSysNo
,BLG_Collection.[CustomerSysNo]
,[Name]
,[Type]
,[RefSysNo]
, QA_Question.[TS]
, photo
, CateSysNo
, QA_Question.CustomerSysNo
, Title
, Context
, Award
, EndTime
, IsSecret
, LastReplyTime
, ReplyCount";
tables = "BLG_Collection left join QA_Question on RefSysNo=QA_Question.SysNo left join USR_Customer on QA_Question.CustomerSysNo = USR_Customer.SysNo";
where = "BLG_Collection.CustomerSysNo = " + UserSysno + " and BLG_Collection.dr=" + (int)AppEnum.State.normal + " and QA_Question.dr=" + (int)AppEnum.State.normal + " and Type=" + (int)AppEnum.CollectionType.quest;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, string name,string status,bool istop, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"[SysNo]
,[KeyWords]
,[DR]
,[IsTop]";
tables = "SYS_Famous_KeyWords";
order = "SYS_Famous_KeyWords.SysNo asc";
where = "1=1";
if (name != "")
{
where += " and (";
string[] tmpstr = name.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " SYS_Famous_KeyWords.[KeyWords] like '%" + tmpstr[i] + "%' and ";
}
where += " 1=1)";
}
if (status == "0" || status == "1")
{
where += " and dr=" + status;
}
if (istop)
{
where += " and istop=" + (int)AppEnum.BOOL.True;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetArticleCollection(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "BLG_Collection.SysNo desc";
#region 设置参数
columns = @"BLG_Collection.[SysNo]
,BLG_Collection.[CustomerSysNo]
,[Name]
,[Type]
,[RefSysNo]
,BLG_Collection.[TS]
,BLG_Collection.[DR]
,ArticleSysNo
,CateSysNo
,Source
,OrderID
,Title
,CMS_ArticleView.CustomerSysNo
,KeyWords
,Limited
,ReadCount
,Description
,Cost ";
tables = "BLG_Collection left join CMS_ArticleView on RefSysNo=CMS_ArticleView.SysNo";
where = "BLG_Collection.CustomerSysNo = " + UserSysno + " and BLG_Collection.dr=" + (int)AppEnum.State.normal + " and CMS_ArticleView.dr=" + (int)AppEnum.State.normal + " and Type=" + (int)AppEnum.CollectionType.article;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetMessageByCustomer(int UserSysno, int pagesize, int pageindex, int isread,int type, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "SysNo desc";
#region 设置参数
columns = @"SysNo, CustomerSysNo, Title, Type, Context, IsRead, DR, TS";
tables = "USR_Message";
where = "CustomerSysNo="+UserSysno+" and type="+type+" and DR=" + (int)AppEnum.State.normal;
if (isread == 1 || isread == 0)
{
where += " and IsRead=" + isread;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetList(int pagesize, int pageindex, string name, DateTime timeBegin, DateTime timeEnd,string status,bool istop, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "";
#region 设置参数
columns = @"SYS_Famous.[SysNo]
,SYS_Famous.[Name]
,Description
,[FullName]
,[BirthYear]
,[BirthTime]
,[Source]
,[Gender]
,[HomeTown]
,[Name1]
,[Name3]
,[EnglishName1]
,[EnglishName3]
,[TimeUnknown]
,[IsTop]
,[photo]
,SYS_Famous.[DR]";
tables = "SYS_Famous left join District3Level on [HomeTown] = District3Level.SysNo3";
order = "SYS_Famous.SysNo asc";
where = "1=1";
if (name != "")
{
where += " and (";
string[] tmpstr = name.Split(new char[] { ' ' });
for (int i = 0; i < tmpstr.Length; i++)
{
where += " (SYS_Famous.[Name] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' or SYS_Famous.FullName like '%" + SQLData.SQLFilter(tmpstr[i]) + "%') and ";
}
where += " 1=1)";
}
if (timeBegin != AppCmn.AppConst.DateTimeNull)
{
where += " and (BirthYear>" + timeBegin.Year.ToString() +
" or (BirthYear=" + timeBegin.Year.ToString() + " and BirthTime>='" + timeBegin.AddYears(AppConst.DateTimeNull.Year - timeBegin.Year).ToString("yyyy-MM-dd 00:00:00") + "'))";
}
if (timeEnd != AppCmn.AppConst.DateTimeNull)
{
where += " and (BirthYear<" + timeEnd.Year.ToString() +
" or (BirthYear=" + timeEnd.Year.ToString() + " and BirthTime<='" + timeEnd.AddYears(AppConst.DateTimeNull.Year - timeEnd.Year).ToString("yyyy-MM-dd 23:59:59") + "'))";
}
if (status != "" && status != "100")
{
where += " and dr=" + status;
}
if (istop)
{
where += " and istop=" + (int)AppEnum.BOOL.True;
}
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
public DataTable GetTopicByUser(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "USR_SMS.SysNo desc";
#region 设置参数
columns = @"USR_SMS.SysNo, ToSysNo, FromSysNo, Title, Context,ReplyCount, IsRead, USR_SMS.DR, USR_SMS.TS, aaa.NickName as FromName,bbb.NickName as ToName, aaa.Photo as ToPhoto,bbb.Photo as FromPhoto";
tables = "USR_SMS left join USR_Customer aaa on ToSysNo= aaa.sysNo left join USR_Customer bbb on FromSysNo= bbb.sysNo";
where = "(FromSysNo=" + UserSysno + " or ToSysNo=" + UserSysno + ") and Parent=0 and dr=" + (int)AppEnum.State.normal + " and IsFromDeleted=" + (int)AppEnum.BOOL.False;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return m_dt;
}
