/// <summary>
/// 列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList<MemberInfo> List(SearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Members";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " CreateDateTime DESC";
fp.WithOptions = " WITH(NOLOCK)";
IList<MemberInfo> list = new List<MemberInfo>();
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(GetByRow(dr));
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); ;
return new PageOfList<MemberInfo>(list, setting.PageIndex, setting.PageSize, count);
}
/// <summary>
/// 反馈列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList<FeedbackInfo> List(SearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Feedback";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = "CreateDateTime DESC";
fp.Condition = " IsDeleted = 0";
IList<FeedbackInfo> list = new List<FeedbackInfo>();
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(new FeedbackInfo(){
Id = dr.Field<int>("Id"),
Title = dr.Field<string>("Title"),
Content = dr.Field<string>("Content"),
Contact = dr.Field<string>("Contact"),
Email = dr.Field<string>("Email"),
Phone = dr.Field<string>("Phone"),
Address = dr.Field<string>("Address"),
Fax = dr.Field<string>("Fax"),
CreateDateTime = dr.Field<DateTime>("CreateDateTime")
});
}
}
int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); ;
return new PageOfList<FeedbackInfo>(list, setting.PageIndex, setting.PageSize, count);
}
/// <summary>
/// 产品列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList<ProductInfo> List(ProductSearchSetting setting) {
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Products";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " Sort ASC,CreateDateTime ASC";
StringBuilder sbCondition = new StringBuilder();
sbCondition.Append(" 1 = 1 ");
if (setting.CategoryId > 0)
{
sbCondition.Append(@" AND EXISTS(
SELECT * FROM Categories AS AC WITH(NOLOCK)
WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0)
AND p.CategoryId = AC.ID
)");
}
if (!setting.ShowDeleted)
{
sbCondition.Append(" AND IsDeleted = 0 /*获取未删除的*/");
}
SqlParameter[] parms = {
new SqlParameter("CID",SqlDbType.Int),
};
parms[0].Value = setting.CategoryId;
fp.Condition = sbCondition.ToString();
IList<ProductInfo> list = new List<ProductInfo>();
ProductInfo model = null;
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(),parms);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text,fp.BuildCountSQL(),parms));
return new PageOfList<ProductInfo>(list, setting.PageIndex, setting.PageSize, count);
}
/// <summary>
/// 列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList<AttachmentDownloadLogInfo> ListLog(int attachId,SearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "AttachmentDownloadLog";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*,A.Title";
fp.OverOrderBy = " LastDownloadDateTime DESC";
fp.WithOptions = " WITH(NOLOCK)";
fp.JoinSQL = " INNER JOIN Attachment AS A WITH(NOLOCK) ON A.Id = P.AttachId";
if(attachId>0){
fp.Condition = string.Format(" AttachId = {0}",attachId);
}
IList<AttachmentDownloadLogInfo> list = new List<AttachmentDownloadLogInfo>();
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(new AttachmentDownloadLogInfo() {
Id = dr.Field<int>("Id"),
UserId = dr.Field<int>("UserId"),
UserName = dr.Field<string>("UserName"),
DownloadCount = dr.Field<int>("DownloadCount"),
LastDownloadDateTime = dr.Field<DateTime>("LastDownloadDateTime"),
AttachTitle = dr.Field<string>("Title")
});
}
}
int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); ;
return new PageOfList<AttachmentDownloadLogInfo>(list, setting.PageIndex, setting.PageSize, count);
}
/// <summary>
/// 列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList<AttachmentInfo> List(AttachmentSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Attachment";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " Sort ASC,CreateDateTime DESC";
fp.WithOptions = " WITH(NOLOCK)";
StringBuilder sbCondition = new StringBuilder();
sbCondition.Append(" IsDeleted = 0 ");
if (setting.CategoryId > 0)
{
sbCondition.Append(@" AND EXISTS(
SELECT * FROM Categories AS AC WITH(NOLOCK)
WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0)
AND p.CategoryId = AC.ID
)");
}
fp.Condition = sbCondition.ToString();
SqlParameter[] param = {
new SqlParameter("CID",SqlDbType.Int),
};
param[0].Value = setting.CategoryId;
IList<AttachmentInfo> list = new List<AttachmentInfo>();
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(),param);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(Get(dr));
}
}
int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL(),param)); ;
return new PageOfList<AttachmentInfo>(list, setting.PageIndex, setting.PageSize, count);
}
public static IPageOfList<ArticleInfo> List(ArticleSearchSetting setting)
{
SqlParameter[] parms = {
new SqlParameter("CID",SqlDbType.Int),
new SqlParameter("Title",SqlDbType.NVarChar),
new SqlParameter("PublishDate",SqlDbType.NVarChar)
};
parms[0].Value = setting.CategoryId;
parms[1].Value = setting.Title;
parms[2].Value = setting.PublishDate;
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Articles";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = "IsTop DESC,Sort ASC,PublishDateTime DESC";
StringBuilder sbCondition = new StringBuilder();
sbCondition.Append(@"EXISTS(
SELECT * FROM Categories AS AC WITH(NOLOCK)
WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0)
AND p.CategoryId = AC.ID
)");
if (!setting.ShowDeleted)
{
sbCondition.Append(" AND IsDeleted = 0 /*获取未删除的*/");
}
if (!string.IsNullOrEmpty(setting.Title))
{
//虚拟服务器，没有启用全文索引
//sbCondition.Append(" AND CONTAINS(Title,@Title) ");
sbCondition.Append(" AND Title LIKE '%'+@Title+'%' ");
}
if (Regex.IsMatch(setting.PublishDate, @"^\d{4}-\d{1,2}-\d{1,2}$", RegexOptions.IgnoreCase))
{
sbCondition.Append(" AND CONVERT(VARCHAR(10),PublishDateTime,120) = @PublishDate");
}
if(setting.IsOnlyShowPublished){
//只显示发布的文章
sbCondition.Append(" AND IsPublished = 1 ");
}
fp.Condition = sbCondition.ToString();
IList<ArticleInfo> list = new List<ArticleInfo>();
ArticleInfo model = null;
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), parms);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = Get(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL(), parms)); ;
return new PageOfList<ArticleInfo>(list, setting.PageIndex, setting.PageSize, count);
}