public static IPageOfList <OrderAdminInfo> List(SearchSetting settings)
{
FastPaging fp = new FastPaging();
fp.PageIndex = settings.PageIndex;
fp.PageSize = settings.PageSize;
fp.TableName = "OrderAdmins";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " CreateDateTime DESC";
IList <OrderAdminInfo> list = new List <OrderAdminInfo>();
OrderAdminInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = Get(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Count();
return(new PageOfList <OrderAdminInfo>(list, settings.PageIndex, settings.PageSize, count));
}
//public static void Applying(List<ForumApplyUserInfo> modelList) {
// string strSQL = "INSERT INTO ForumApplyUsers(UserId,UserName,ForumGroupId,ContactPerson,[Status],CreateDateTime) VALUES(@UserId,@UserName,@ForumGroupId,@ContactPerson,0,GETDATE());";
// SqlParameter[] parms = null;
// foreach(var model in modelList){
// parms = ParameterHelper.GetClassSqlParameters(model);
// Goodspeed.Library.Data.SQLPlus.ExecuteNonQuery(CommandType.Text,strSQL,parms);
// }
//}
public static IPageOfList <ForumApplyUserInfo> List(int pageIndex, int pageSize)
{
FastPaging fp = new FastPaging();
fp.PageIndex = pageIndex;
fp.PageSize = pageSize;
fp.Ascending = false;
fp.TableName = "ForumApplyUsers";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*,g.Name AS ForumGroupName";
fp.OverOrderBy = "p.CreateDateTime DESC";
fp.JoinSQL = "LEFT JOIN dbo.ForumGroups AS g ON p.ForumGroupId = g.Id";
IList <ForumApplyUserInfo> list = new List <ForumApplyUserInfo>();
ForumApplyUserInfo model = null;
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = Get(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = CountForList();
return(new PageOfList <ForumApplyUserInfo>(list, pageIndex, pageSize, count));
}
/// <summary>
/// 广告位列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList <AdPositionInfo> List(AdSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CreateDateTime DESC";
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.QueryFields = "*";
fp.TableName = "AdPosition";
fp.PrimaryKey = "Id";
fp.WithOptions = " WITH(NOLOCK)";
IList <AdPositionInfo> list = new List <AdPositionInfo>();
AdPositionInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
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()));
return(new PageOfList <AdPositionInfo>(list, setting.PageIndex, setting.PageSize, count));
}
/// <summary>
/// 列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList <JobInfo> List(JobSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Job";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " IsHot DESC,Sort DESC ,CreateDateTime DESC";
fp.WithOptions = " WITH(NOLOCK)";
fp.Condition = " IsDeleted = 0";
if (setting.IsOnlyShowPublished)
{
fp.Condition += " AND IsPublished = 1 ";
}
IList <JobInfo> list = new List <JobInfo>();
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(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));;
return(new PageOfList <JobInfo>(list, setting.PageIndex, setting.PageSize, count));
}
public static IPageOfList <AttachmentInfo> List(SearchSetting settings)
{
FastPaging fp = new FastPaging();
fp.PageIndex = settings.PageIndex;
fp.PageSize = settings.PageSize;
fp.TableName = "Attachments";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
StringBuilder sbCondition = new StringBuilder();
sbCondition.AppendFormat(" SiteId = @SiteId ");
if (!settings.ShowDeleted)
{
sbCondition.Append(" AND IsDeleted = 0 ");
}
if (settings.CategoryId > 0)
{
sbCondition.Append(@" AND EXISTS(
SELECT * FROM dbo.Categories AS AC WITH(NOLOCK)
WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0) /*获取此分类下所有的子分类*/
AND SiteId = @SiteId
AND IsDeleted = 0
AND p.CategoryId = AC.ID
)");
}
fp.Condition = sbCondition.ToString();
fp.OverOrderBy = "Sort ASC ,PublishDate DESC";
SqlParameter[] parms =
{
new SqlParameter("@SiteId", SqlDbType.Int),
new SqlParameter("@CID", SqlDbType.Int)
};
parms[0].Value = settings.SiteId;
parms[1].Value = settings.CategoryId;
IList <AttachmentInfo> list = new List <AttachmentInfo>();
AttachmentInfo 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 = Count(settings);
return(new PageOfList <AttachmentInfo>(list, settings.PageIndex, settings.PageSize, count));
}
public static IPageOfList <CompanyInfo> CompanyList(MemberSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " C.ApplyDateTime DESC";
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.QueryFields = "*";
fp.TableName = "Members";
fp.PrimaryKey = "Id";
fp.WithOptions = " WITH(NOLOCK)";
fp.TableReName = "M";
fp.JoinSQL = "INNER JOIN Companies AS C WITH(NOLOCK) ON M.Id = C.UserId";
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(" 1 = 1");
if (setting.CompanyStatus != CompanyStatus.None)
{
sbSQL.AppendFormat(" AND C.CompanyStatus = {0}", (int)setting.CompanyStatus);
}
if (!string.IsNullOrEmpty(setting.CompanyName))
{
//这块应该检查一下是否有危险字符，防止SQL注入
//目前没有加上 xingbaifang 2012-12-05
sbSQL.AppendFormat(" AND C.CompanyName LIKE '%{0}%'", setting.CompanyName);
}
fp.Condition = sbSQL.ToString();
//throw new Exception(fp.Build2005());
IList <CompanyInfo> list = new List <CompanyInfo>();
CompanyInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetCompanyByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
sbSQL = new StringBuilder();
sbSQL.Append("SELECT COUNT(*) FROM Members AS M WITH(NOLOCK) INNER JOIN Companies AS C WITH(NOLOCK) ON M.Id = C.UserId");
sbSQL.Append(" WHERE 1 = 1 ");
if (fp.Condition.Length > 0)
{
sbSQL.AppendFormat(" AND {0}", fp.Condition);
}
//throw new Exception(sbSQL.ToString());
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, sbSQL.ToString()));
return(new PageOfList <CompanyInfo>(list, setting.PageIndex, setting.PageSize, count));
}
public static IPageOfList <OrderInfo> List(OrderSearchSetting settings)
{
FastPaging fp = new FastPaging();
fp.PageIndex = settings.PageIndex;
fp.PageSize = settings.PageSize;
fp.TableName = "Orders";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
StringBuilder sbCondition = new StringBuilder(" 1 = 1");
if (!string.IsNullOrEmpty(settings.CompanyName))
{
sbCondition.Append(" AND OrderCompanyName LIKE '%'+@CompanyName+'%'");
}
if (!string.IsNullOrEmpty(settings.OrderNumber))
{
sbCondition.Append(" AND OrderNumber = @OrderNumber");
}
if (!settings.ShowDeleted)
{
sbCondition.Append(" AND IsDeleted = 0 ");
}
fp.Condition = sbCondition.ToString();
fp.OverOrderBy = " CreateDateTime DESC";
SqlParameter[] parms =
{
new SqlParameter("@CompanyName", SqlDbType.NVarChar),
new SqlParameter("@OrderNumber", SqlDbType.NVarChar)
};
parms[0].Value = settings.CompanyName;
parms[1].Value = settings.OrderNumber;
IList <OrderInfo> list = new List <OrderInfo>();
OrderInfo 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 = Count(settings);
return(new PageOfList <OrderInfo>(list, settings.PageIndex, settings.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 <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 <JobInfo> List(SearchSetting settings)
{
FastPaging fp = new FastPaging();
fp.PageIndex = settings.PageIndex;
fp.PageSize = settings.PageSize;
fp.TableName = "Jobs";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
StringBuilder sbCondition = new StringBuilder();
sbCondition.AppendFormat(" SiteId = @SiteId ");
if (!settings.ShowDeleted)
{
sbCondition.Append(" AND IsDeleted = 0 ");
}
fp.Condition = sbCondition.ToString();
fp.OverOrderBy = "Sort ASC , EndDateTime DESC";
SqlParameter[] parms =
{
new SqlParameter("@SiteId", SqlDbType.Int),
};
parms[0].Value = settings.SiteId;
IList <JobInfo> list = new List <JobInfo>();
JobInfo 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 = Count(settings);
return(new PageOfList <JobInfo>(list, settings.PageIndex, settings.PageSize, count));
}
/// <summary>
/// 列表
/// </summary>
/// <param name="searchSetting"></param>
/// <returns></returns>
public static IPageOfList <CompanyProductInfo> List(CompanyProductSearchSetting searchSetting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CP.PublishDateTime DESC";
fp.PageIndex = searchSetting.PageIndex;
fp.PageSize = searchSetting.PageSize;
fp.QueryFields = "*";
fp.TableName = "CompanyProducts";
fp.PrimaryKey = "Id";
fp.TableReName = "CP";
fp.WithOptions = " WITH(NOLOCK)";
fp.Condition = " IsDeleted = 0 ";
if (searchSetting.CompanyId > 0)
{
fp.Condition += string.Format(" AND CompanyId = {0}", searchSetting.CompanyId);
}
if (searchSetting.CategoryId > 0)
{
fp.Condition += string.Format(" AND CategoryId = {0}", searchSetting.CategoryId);
}
if (searchSetting.SystemCategoryId > 0)
{
fp.Condition += string.Format(" AND SystemCategoryId = {0}", searchSetting.SystemCategoryId);
}
IList <CompanyProductInfo> list = new List <CompanyProductInfo>();
CompanyProductInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <CompanyProductInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count));
}
/// <summary>
/// 博客列表
/// </summary>
/// <param name="searchSetting"></param>
/// <returns></returns>
public static IPageOfList <BlogPostInfo> List(BlogSearchSetting searchSetting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " BP.CreateDateTime DESC";
fp.PageIndex = searchSetting.PageIndex;
fp.PageSize = searchSetting.PageSize;
fp.QueryFields = "*";
fp.TableName = "BlogPosts";
fp.PrimaryKey = "Id";
fp.TableReName = "BP";
fp.WithOptions = " WITH(NOLOCK)";
StringBuilder sbCondition = new StringBuilder();
sbCondition.Append(" IsDeleted = 0 ");
if (searchSetting.UserId > 0)
{
sbCondition.AppendFormat(" AND UserId = {0}", searchSetting.UserId);
}
if (searchSetting.SystemCategoryId > 0)
{
sbCondition.AppendFormat(" AND SystemCategoryId = {0}", searchSetting.SystemCategoryId);
}
fp.Condition = sbCondition.ToString();
IList <BlogPostInfo> list = new List <BlogPostInfo>();
BlogPostInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <BlogPostInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count));
}
public static IPageOfList <MemberInfo> BaseInfoList(MemberSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CreateDateTime DESC";
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.QueryFields = "*";
fp.TableName = "Members";
fp.PrimaryKey = "Id";
fp.WithOptions = " WITH(NOLOCK)";
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(" 1 = 1 ");
//根据用户名查询信息
if (!string.IsNullOrEmpty(setting.UserName))
{
sbSQL.AppendFormat(" AND UserName LIKE '%{0}%'", Controleng.Common.Utils.ChkSQL(setting.UserName));
}
fp.Condition += sbSQL.ToString();
IList <MemberInfo> list = new List <MemberInfo>();
MemberInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
string strSQL = "SELECT COUNT(*) FROM Members AS M WITH(NOLOCK) WHERE ";
strSQL += fp.Condition;
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL));
return(new PageOfList <MemberInfo>(list, setting.PageIndex, setting.PageSize, count));
}
/// <summary>
/// 获得前台显示列表
/// </summary>
/// <param name="companyId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static IPageOfList <CompanyFeedbackFrontInfo> FrontList(int companyId, int pageIndex, int pageSize)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CF.CreateDateTime DESC";
fp.PageIndex = pageIndex;
fp.PageSize = pageSize;
fp.QueryFields = "CF.Id,CF.RealName,CF.CreateDateTime,CF.[Type],CF.Content,C.CompanyName AS ForCompanyName,CF.ForCompanyId,CF.UserId,ISNULL(M.Avatar,'nophoto.jpg') AS Avatar,ISNULL(M.UserName,'') AS UserName";
fp.TableName = "CompanyFeedback";
fp.PrimaryKey = "Id";
fp.TableReName = "CF";
fp.WithOptions = " WITH(NOLOCK)";
fp.JoinSQL = "INNER JOIN Companies AS C WITH(NOLOCK) ON CF.ForCompanyId = C.CompanyId LEFT JOIN Members AS M WITH(NOLOCK) ON CF.UserId = M.Id";
fp.Condition = string.Format(" CF.ForCompanyId = {0} AND CF.IsDeleted = 0", companyId);
IList <CompanyFeedbackFrontInfo> list = new List <CompanyFeedbackFrontInfo>();
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
var model = new CompanyFeedbackFrontInfo();
model.Avatar = dr.Field <string>("Avatar");
model.Content = dr.Field <string>("Content");
model.CreateDateTime = dr.Field <DateTime>("CreateDateTime");
model.ForCompanyId = dr.Field <int>("ForCompanyId");
model.ForCompanyName = dr.Field <string>("ForCompanyName");
model.RealName = dr.Field <string>("RealName");
model.Type = dr.Field <string>("Type");
model.UserId = dr.Field <int>("UserId");
model.UserName = dr.Field <string>("UserName");
if (string.IsNullOrEmpty(model.UserName))
{
model.UserName = model.RealName;
}
list.Add(model);
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <CompanyFeedbackFrontInfo>(list, pageIndex, 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));
}
public static IPageOfList <AttachmentInfo> List(SearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Attachments";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.WithOptions = " WITH(NOLOCK)";
fp.OverOrderBy = " CreateDateTime DESC";
if (!setting.ShowDeleted)
{
fp.Condition += " IsDeleted = 0";
}
IList <AttachmentInfo> list = new List <AttachmentInfo>();
AttachmentInfo model = null;
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = new AttachmentInfo()
{
Id = dr.Field <int>("ID"),
Title = dr.Field <string>("Title"),
CreateDateTime = dr.Field <DateTime>("CreateDateTime"),
FileType = dr.Field <string>("FileType"),
Size = dr.Field <int>("Size"),
Url = dr.Field <string>("Url"),
IsDeleted = dr.Field <bool>("IsDeleted")
};
list.Add(model);
}
}
int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM Attachments"));
return(new PageOfList <AttachmentInfo>(list, setting.PageIndex, setting.PageSize, count));
}
/// <summary>
/// 根据公司ID获得反馈列表
/// </summary>
/// <param name="companyId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static IPageOfList <CompanyFeedbackInfo> List(int companyId, int pageIndex, int pageSize)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CreateDateTime DESC";
fp.PageIndex = pageIndex;
fp.PageSize = pageSize;
fp.QueryFields = "*";
fp.TableName = "CompanyFeedback";
fp.PrimaryKey = "Id";
fp.WithOptions = " WITH(NOLOCK)";
fp.Condition = string.Format(" CompanyId = {0} AND IsDeleted = 0", companyId);
IList <CompanyFeedbackInfo> list = new List <CompanyFeedbackInfo>();
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(new CompanyFeedbackInfo()
{
CompanyName = dr.Field <string>("CompanyName"),
Content = dr.Field <string>("Content"),
CreateDateTime = dr.Field <DateTime>("CreateDateTime"),
UserId = dr.Field <int>("UserId"),
Email = dr.Field <string>("Email"),
Phone = dr.Field <string>("Phone"),
ForCompanyId = dr.Field <int>("ForCompanyId"),
Id = dr.Field <int>("Id"),
IP = dr.Field <string>("IP"),
RealName = dr.Field <string>("RealName"),
Type = dr.Field <string>("Type"),
ReplyList = _ReplyList(dr.Field <int>("Id"))
});
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <CompanyFeedbackInfo>(list, pageIndex, 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));
}
public static IPageOfList <FeedbackInfo> List(int pageIndex, int pageSize)
{
FastPaging fp = new FastPaging();
fp.PageIndex = pageIndex;
fp.PageSize = pageSize;
fp.Ascending = false;
fp.TableName = "Feedbacks";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " CreateDateTime DESC";
IList <FeedbackInfo> list = new List <FeedbackInfo>();
FeedbackInfo model = null;
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = new FeedbackInfo()
{
Id = dr.Field <int>("ID"),
Title = dr.Field <string>("Title"),
Content = dr.Field <string>("Content"),
Phone = dr.Field <string>("Phone"),
Email = dr.Field <string>("Email"),
CreateDateTime = dr.Field <DateTime>("CreateDateTime"),
IP = dr.Field <string>("IP"),
Realname = dr.Field <string>("Realname"),
FeedbackType = dr.Field <string>("FeedbackType")
};
list.Add(model);
}
}
int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM Feedbacks"));
return(new PageOfList <FeedbackInfo>(list, pageIndex, pageSize, count));
}
public static IPageOfList <AdminInfo> List(SearchSetting settings)
{
FastPaging fp = new FastPaging();
fp.PageIndex = settings.PageIndex;
fp.PageSize = settings.PageSize;
fp.TableName = "Admins";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
StringBuilder sbCondition = new StringBuilder();
if (!settings.ShowDeleted)
{
sbCondition.Append(" IsDeleted = 0 ");
}
fp.Condition = sbCondition.ToString();
fp.OverOrderBy = " CreateDateTime DESC";
IList <AdminInfo> list = new List <AdminInfo>();
AdminInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = Get(dr, true);
if (model != null)
{
list.Add(model);
}
}
}
int count = Count(settings);
return(new PageOfList <AdminInfo>(list, settings.PageIndex, settings.PageSize, count));
}
/// <summary>
/// 分页列表（包含CompanyNews和Articles两个部分）
/// </summary>
/// <param name="searchSetting"></param>
/// <returns></returns>
public static IPageOfList <CompanyNewsInfo> List(CompanyNewsSearchSetting searchSetting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CN.PublishDateTime DESC";
fp.PageIndex = searchSetting.PageIndex;
fp.PageSize = searchSetting.PageSize;
fp.QueryFields = "*";
fp.TableName = string.Format(@"(
SELECT Id,CompanyId,[Type],Title,Content,Remark,ImageUrl,Tags,PublishDateTime,ModifyDateTime,CreateDateTime,IsDeleted,Url
FROM CompanyNews WITH(NOLOCK) WHERE CompanyId = {0} AND [Type] = '{1}' AND IsDeleted = 0
UNION ALL
SELECT Id,CompanyId,'article',Title,Content,Remark,ImageUrl,Tags,PublishDateTime,LastModifyDateTime,CreateDateTime,IsDeleted,Url
FROM Articles WITH(NOLOCK) WHERE CompanyId = {0} AND IsDeleted = 0)", searchSetting.CompanyId, searchSetting.Type);
fp.PrimaryKey = "Id";
fp.TableReName = "CN";
IList <CompanyNewsInfo> list = new List <CompanyNewsInfo>();
CompanyNewsInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <CompanyNewsInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count));
}
/// <summary>
/// 带有分页的文章列表
/// </summary>
/// <param name="searchSetting"></param>
/// <returns></returns>
public static IPageOfList <ArticleInfo> List(ArticleSearchSetting searchSetting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " A.PublishDateTime DESC";
fp.PageIndex = searchSetting.PageIndex;
fp.PageSize = searchSetting.PageSize;
fp.QueryFields = "*";
fp.TableName = "Articles";
fp.PrimaryKey = "Id";
fp.TableReName = "A";
fp.WithOptions = " WITH(NOLOCK)";
StringBuilder sbSQL = new StringBuilder();
if (searchSetting.ColumnIds != null && searchSetting.ColumnIds.Length > 0)
{
//过滤栏目分类
//跳过为0的值
string temp = Enumerable.Range(0, searchSetting.ColumnIds.Length).Select(i => {
if (searchSetting.ColumnIds[i] > 0)
{
return(string.Format(" A.CategoryId = {0} ", searchSetting.ColumnIds[i]));
}
return(string.Empty);
}).Aggregate((a, b) => a + " OR " + b);
if (!string.IsNullOrEmpty(temp))
{
sbSQL.AppendFormat(" AND ( {0} )", temp);
}
}
if (searchSetting.TechIds != null && searchSetting.TechIds.Length > 0)
{
//过滤技术分类
string temp = Enumerable.Range(0, searchSetting.TechIds.Length).Select(i =>
{
if (searchSetting.TechIds[i] > 0)
{
return(string.Format(" A2C.CategoryId = {0} ", searchSetting.TechIds[i]));
}
return(string.Empty);
}).Aggregate((a, b) => a + " OR " + b);
if (!string.IsNullOrEmpty(temp))
{
sbSQL.Append(" AND EXISTS(");
sbSQL.Append(" SELECT * FROM dbo.Article2Category AS A2C WITH(NOLOCK)");
sbSQL.Append(" WHERE A2C.[Type] = 'tech'");
sbSQL.Append(" AND A.Id = A2C.ArticleId");
sbSQL.AppendFormat(" AND ( {0} )", temp);
sbSQL.Append(" )");
}
}
if (searchSetting.IndustryIds != null && searchSetting.IndustryIds.Length > 0)
{
//过滤行业分类
string temp = Enumerable.Range(0, searchSetting.IndustryIds.Length).Select(i =>
{
if (searchSetting.IndustryIds[i] > 0)
{
return(string.Format(" A2C.CategoryId = {0} ", searchSetting.IndustryIds[i]));
}
return(string.Empty);
}).Aggregate((a, b) => a + " OR " + b);
if (!string.IsNullOrEmpty(temp))
{
sbSQL.Append(" AND EXISTS(");
sbSQL.Append(" SELECT * FROM dbo.Article2Category AS A2C WITH(NOLOCK)");
sbSQL.Append(" WHERE A2C.[Type] = 'industry'");
sbSQL.Append(" AND A.Id = A2C.ArticleId");
sbSQL.AppendFormat(" AND ( {0} )", temp);
sbSQL.Append(" )");
}
}
if (!searchSetting.IsDeleted)
{
sbSQL.Append(" AND IsDeleted = 0");
}
fp.Condition = " 1 = 1 " + sbSQL.ToString();
//throw new Exception(fp.Build2005());
IList <ArticleInfo> list = new List <ArticleInfo>();
ArticleInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <ArticleInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count));
}
public static IPageOfList <ArticleInfo> List(SearchSetting 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) ");
}
if (Regex.IsMatch(setting.PublishDate, @"^\d{4}-\d{1,2}-\d{1,2}$", RegexOptions.IgnoreCase))
{
sbCondition.Append(" AND CONVERT(VARCHAR(10),PublishDateTime,120) = @PublishDate");
}
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 = CountForList(setting);
return(new PageOfList <ArticleInfo>(list, setting.PageIndex, setting.PageSize, count));
}
