public DataTable GetTopicByReciever(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "SysNo desc";
#region 设置参数
columns = @"USR_SMS.SysNo, FromSysNo, Title, Context, IsRead, USR_SMS.DR, USR_SMS.TS, NickName, Photo,ReplyCount";
tables = "USR_SMS left join USR_Customer on FromSysNo= customer.sysNo";
where = "ToSysNo=" + UserSysno + " and Parent=0 and dr=" + (int)AppEnum.State.normal + " and IsToDeleted=" + (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);
}
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 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 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 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 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 GetListByQuest(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]
,[QuestionSysNo]
,[CustomerSysNo]
,[Title]
,[Context]
,[Love]
,[Hate]
,[Award]
,[QA_Answer].[DR]
,[QA_Answer].[TS]
,NickName
,Point
,TotalAnswer
,BestAnswer
,photo
,USR_Grade.Name
,USR_Grade.LevelNum";
tables = "[QA_Answer] left join USR_Customer on CustomerSysNo=USR_Customer.SysNo left join USR_Grade on GradeSysNo = USR_Grade.SysNo";
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", 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 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 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);
}
/// <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 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 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 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 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 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 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 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 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 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, 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 GetListByUserAnswer(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_Question.[SysNo]
,[CateSysNo]
,[CustomerSysNo]
,[Title]
,[Context]
,[Award]
,[EndTime]
,[IsSecret]
,[LastReplyTime]
,[LastReplyUser]
,[ReplyCount]
,[ReadCount]
,QA_Question.[DR]
,QA_Question.[TS]
,Photo
,NickName";
tables = " (select QuestionSysNo,TS from QA_Answer where CustomerSysNo=" + usersysno + ") as T left join QA_Question on T.QuestionSysNo = QA_Question.SysNo left join USR_Customer on CustomerSysNo=USR_Customer.SysNo";
where = "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_Question.[Title] like '%" + SQLData.SQLFilter(tmpstr[i]) + "%' and ";
}
where += " 1=1)";
}
if (best)
{
where += " and Award>0";
}
if (cate != 0)
{
where += " and CateSysNo=" + cate;
}
if (orderby == "timedown")
{
order = "T.TS desc";
}
else if (orderby == "timeup")
{
order = "T.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";
}
else
{
order = "QA_Question.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 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(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 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 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]
,[CustomerSysNo]
,[ProductType]
,[ProductSysNo]
,[Price]
,[PayType]
,[Discount]
,[PayAmount]
,[TS]
,[Status]
,[OrderID]
,[CurrentID]
,[PayTime]";
tables = "ORD_Cash";
where = "1=1";
if (customersysno != 0)
{
where += " and CustomerSysNo=" + customersysno;
}
if (type != AppConst.IntNull)
{
where += " and ProductType=" + type;
}
if (status != AppConst.IntNull)
{
where += " and status=" + status;
}
if (productsysno != AppConst.IntNull)
{
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 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);
}