public async Task<ResponseData<yy_News_Type>> Get(RequestEntity value)
{
//查询的表名称
Type Table = typeof(yy_News_Type);
var FormData = await Request.Content.ReadAsAsync<Dictionary<String, String>>();
#region where condition
//筛选条件
var Where = String.Empty;
var WhereBuild = new List<string>();
#region 新闻标题
if (!String.IsNullOrEmpty(value.Title))
{
WhereBuild.Add("Title like '%" + value.Title + "%'");
}
#endregion
#region 新闻分类
if (value.TypeID > 0)
{
WhereBuild.Add("TypeIDs like '%," + value.TypeID + ",%'");
}
#endregion
#region 根据时间过滤
#region 大于等于 开始时间 && 小于等于 结束时间
if (!String.IsNullOrEmpty(value.StartTime) && !String.IsNullOrEmpty(value.EndTime))
{
DateTime st, et;
if (DateTime.TryParse(value.StartTime, out st) && DateTime.TryParse(value.EndTime, out et))
{
WhereBuild.Add(" CreateDate >= '" + st.ToString("yyyy-MM-dd") + " 00:00:00' AND CreateDate <= '" + et.ToString("yyyy-MM-dd") + " 23:59:59'");
}
}
#endregion
#region 大于等于开始时间
else if (!String.IsNullOrEmpty(value.StartTime))
{
DateTime st;
if (DateTime.TryParse(value.StartTime, out st))
{
WhereBuild.Add(" CreateDate >= '" + st.ToString("yyyy-MM-dd") + " 00:00:00'");
}
}
#endregion
#region 小于等于结束时间
else if (!String.IsNullOrEmpty(value.EndTime))
{
DateTime et;
if (DateTime.TryParse(value.EndTime, out et))
{
WhereBuild.Add(" CreateDate <= '" + et.ToString("yyyy-MM-dd") + " 23:59:59'");
}
}
#endregion
#endregion
if (WhereBuild.Count > 0)
{
Where = " WHERE " + String.Join(" AND ", WhereBuild);
}
#endregion
#region OrderBy
//排序规则
String OrderBy = " ID DESC ";
if (!String.IsNullOrEmpty(value.OrderBy))
{
OrderBy = " " + value.OrderBy + " " + (value.IsDesc ? "DESC" : "ASC");
}
#endregion
#region 拼接sql语句
String colsStr = " * ";
#region 查询数据
String QuertCMD = String.Format(@"SELECT TOP {0} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY {4}) AS RowNumber," + colsStr + @" FROM {2} WITH(NOLOCK) {3}
) A WHERE RowNumber > {0} * ({1}-1)", value.PageSize, value.PageIndex + 1, "[" + Table.Name + "]", Where, OrderBy);
#endregion
#region 查询总数
String DataCountCMD = @"SELECT COUNT(1) FROM [" + Table.Name + "] WITH(NOLOCK) " + Where;
#endregion
#endregion
#region 执行查询并返回数据
var DataCount = DB.Database.SqlQuery<int>(DataCountCMD).FirstOrDefault();
return new ResponseData<yy_News_Type>(value.PageSize,
value.PageIndex,
DataCount,
(DataCount % value.PageSize == 0 ? DataCount / value.PageSize : DataCount / value.PageSize + 1),
DB.Database.SqlQuery<yy_News_Type>(QuertCMD).ToList());
#endregion
}
public async Task<ResponseData<yy_Page_Type2>> Get(RequestEntity value)
{
//查询的表名称
Type Table = typeof(yy_Page_Type);
var FormData = await Request.Content.ReadAsAsync<Dictionary<String, String>>();
#region where condition
//筛选条件
var Where = String.Empty;
var WhereBuild = new List<string>();
#region 页面标题
if (!String.IsNullOrEmpty(value.Title))
{
WhereBuild.Add("Title like '%" + value.Title + "%'");
}
#endregion
#region 页面分类
if (value.TypeID > 0)
{
WhereBuild.Add("TypeIDs like '%," + value.TypeID + ",%'");
}
#endregion
#region 根据时间过滤
#region 大于等于 开始时间 && 小于等于 结束时间
if (!String.IsNullOrEmpty(value.StartTime) && !String.IsNullOrEmpty(value.EndTime))
{
DateTime st, et;
if (DateTime.TryParse(value.StartTime, out st) && DateTime.TryParse(value.EndTime, out et))
{
WhereBuild.Add(" CreateDate >= '" + st.ToString("yyyy-MM-dd") + " 00:00:00' AND CreateDate <= '" + et.ToString("yyyy-MM-dd") + " 23:59:59'");
}
}
#endregion
#region 大于等于开始时间
else if (!String.IsNullOrEmpty(value.StartTime))
{
DateTime st;
if (DateTime.TryParse(value.StartTime, out st))
{
WhereBuild.Add(" CreateDate >= '" + st.ToString("yyyy-MM-dd") + " 00:00:00'");
}
}
#endregion
#region 小于等于结束时间
else if (!String.IsNullOrEmpty(value.EndTime))
{
DateTime et;
if (DateTime.TryParse(value.EndTime, out et))
{
WhereBuild.Add(" CreateDate <= '" + et.ToString("yyyy-MM-dd") + " 23:59:59'");
}
}
#endregion
#endregion
if (WhereBuild.Count > 0)
{
Where = " WHERE " + String.Join(" AND ", WhereBuild);
}
#endregion
#region OrderBy
//排序规则
String OrderBy = " ID DESC ";
if (!String.IsNullOrEmpty(value.OrderBy))
{
OrderBy = " " + value.OrderBy + " " + (value.IsDesc ? "DESC" : "ASC");
}
#endregion
#region 拼接sql语句
String colsStr = " * ";
#region 查询数据
String QuertCMD = String.Format(@"SELECT TOP {0} * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY {4}) AS RowNumber," + colsStr + @" FROM {2} WITH(NOLOCK) {3}
) A WHERE RowNumber > {0} * ({1}-1)", value.PageSize, value.PageIndex + 1, "[" + Table.Name + "]", Where, OrderBy);
#endregion
#region 查询总数
String DataCountCMD = @"SELECT COUNT(1) FROM [" + Table.Name + "] WITH(NOLOCK) " + Where;
#endregion
#endregion
#region 执行查询并返回数据
var DataCount = DB.Database.SqlQuery<int>(DataCountCMD).FirstOrDefault();
return new ResponseData<yy_Page_Type2>(value.PageSize,
value.PageIndex,
DataCount,
(DataCount % value.PageSize == 0 ? DataCount / value.PageSize : DataCount / value.PageSize + 1),
DB.Database.SqlQuery<yy_Page_Type>(QuertCMD).ToList().Select(x => new yy_Page_Type2()
{
ID = x.ID,
Author = x.Author,
CreateDate = x.CreateDate,
Email = x.Email,
//配合客户端angular输出，这里必须做处理
Images = x.Images.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries)
.Select(m => new yy_Page_Type2Image (){ src = m }).ToList(),
IsMaster = x.IsMaster,
Mobile = x.Mobile,
Name = x.Name,
Name_En = x.Name_En,
QQ = x.QQ,
Summary = x.Summary,
SupportPlatform = x.SupportPlatform,
UserID = x.UserID,
Version = x.Version,
Website = x.Website
}).ToList());
#endregion
}
