private ResultRetrun CheckName(int id, string categoryName, CategoryType categoryType)
{
ResultRetrun rmodel = new ResultRetrun();
FilterEntityModel filterModel = new FilterEntityModel();
filterModel.KeyValueList = new List <KeyValueObj>();
filterModel.KeyValueList.Add(new KeyValueObj()
{
Key = "Name", Value = categoryName, FieldType = FieldType.String
});
List <CategoryDictionary> cdList = cdService.GetAll(categoryType);
if (cdList != null && cdList.Count > 0 && cdList.Where(g => g.Name.Equals(categoryName)).Count() > 0)
{
if (cdList.FindAll(g => g.Name.Equals(categoryName)).Where(g => !g.Id.Equals(id)).Count() > 0)
{
rmodel.message = "类别名称已经存在";
}
else
{
rmodel.isSuccess = true;
}
}
else
{
rmodel.isSuccess = true;
}
return(rmodel);
}
public List <RotationChart> GetAll(RotationType rotationType)
{
FilterEntityModel filterModel = new FilterEntityModel();
filterModel.SortDict = new KeyValuePair <string, SortType>("Id", SortType.Asc);
if (rotationType != RotationType.UnKnown)
{
filterModel.KeyValueList = new List <KeyValueObj>()
{
new KeyValueObj()
{
Key = "Type", Value = (int)rotationType, FieldType = FieldType.Int
}
};
}
List <int> sortList = GetSortList(rotationType);
List <RotationChart> rotationChartList = _rotationChartProvider.GetAll(filterModel);
if (rotationChartList != null && rotationChartList.Count > 0 && sortList.Count > 0)
{
rotationChartList = rotationChartList.OrderByDescending(x => Enumerable.Reverse(sortList.Select(g => g)).ToList().IndexOf(x.Id)).ToList();
}
return(rotationChartList);
}
public MemberUnit GetByNumber(string number)
{
//参数验证
if (string.IsNullOrEmpty(number))
{
ErrorMsg = ErrorCode.ParameterNull;
return(null);
}
FilterEntityModel filterModel = new FilterEntityModel()
{
KeyValueList = new List <KeyValueObj>()
{
new KeyValueObj()
{
Key = "Number", Value = number, TbAsName = Constant.MEMBER_UNIT_AS_NAME
}
}
};
List <MemberUnit> memberUnitList = _memberUnitProvider.GetAll(filterModel);
if (memberUnitList != null && memberUnitList.Count > 0)
{
return(memberUnitList.FirstOrDefault());
}
return(null);
}
public JsonResult CheckTeaNumber(int id, string number)
{
ResultRetrun rmodel = new ResultRetrun();
FilterEntityModel filterModel = new FilterEntityModel();
filterModel.KeyValueList = new List <KeyValueObj>();
filterModel.KeyValueList.Add(new KeyValueObj()
{
Key = "number", Value = number, FieldType = FieldType.String
});
List <TeaCertificate> teaList = teaService.GetAll(filterModel);
if (teaList != null && teaList.Count > 0)
{
if (teaList.Where(g => !g.Id.Equals(id)).Count() > 0)
{
rmodel.message = "证书编号已经存在";
}
else
{
rmodel.isSuccess = true;
}
}
else
{
rmodel.isSuccess = true;
}
return(Json(rmodel.isSuccess, JsonRequestBehavior.AllowGet));
}
public ActionResult GetJsonData(SearchDataTable dt, SearchViewModel svm)
{
int totalNum = 0;
FilterEntityModel fillter = new FilterEntityModel();
fillter.PageIndex = dt.pageIndex;
fillter.PageSize = dt.length;
fillter.KeyValueList = new List <KeyValueObj>();
if (svm != null && !string.IsNullOrEmpty(svm.FilterKey) && !string.IsNullOrEmpty(svm.FilterVal))
{
string fkey = string.Empty;
SearchType ftype = (SearchType)Convert.ToInt16(svm.FilterKey);
switch (ftype)
{
case SearchType.Name:
fkey = "Name";
fillter.FilterType = FilterType.Like;
break;
case SearchType.Type:
fkey = "Category";
break;
case SearchType.Number:
fkey = "Number";
break;
case SearchType.Area:
fkey = "City";
break;
}
fillter.KeyValueList.Add(new KeyValueObj()
{
Key = fkey, Value = svm.FilterVal
});
}
List <TeaCertificate> teaList = teaService.GetPage(fillter, out totalNum);
ReturnPageResultIList <TeaCertificate> data = new ReturnPageResultIList <Core.TeaCertificate>(teaList, totalNum);
IList <TeaViewModel> gmList = new List <TeaViewModel>();
if (data != null && data.DataT != null)
{
gmList = data.DataT.Select(g => new TeaViewModel()
{
Id = g.Id, UserId = g.UserId, TeacherName = g.Name, Number = g.Number, Gender = g.Gender, State = (int)g.State, CreateTime = g.CreateDate.ToString("yyyy-MM-dd HH:mm"), Category = g.Category, Level = g.Level, Province = Convert.ToInt32(g.Province), City = Convert.ToInt32(g.City), UserName = g.UserName, Area = GetAreaStr(g.Province, g.City)
}).ToList();
}
return(Json(new
{
recordsFiltered = data.totalRecords,
recordsTotal = data.totalPages,
data = gmList
}, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 获取会员分页信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <param name="totalNum">总条数</param>
/// <returns>会员集合</returns>
public List <User> GetPage(FilterEntityModel condition, out int totalNum)
{
totalNum = 0;
string sort = condition.OrderBy;
string where = condition.Where;
List <User> userList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSqlQuery = @"select count(ID) from [user] where IsDeleted=0 " + where;//查询有多少条记录
IList <DbParameter> parameList = new List <DbParameter>();
parameList.Add(new SqlParameter("@pageSize", condition.PageSize));
parameList.Add(new SqlParameter("@pageIndex", condition.PageIndex));
if (condition.SqlParList.Count > 0)
{
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
totalNum = Convert.ToInt32(dbHelper.ExecuteScalar(strSqlQuery, parameList));
string strSql = @"SELECT [ID]
,[UserName]
,[IsAdmin]
,[Name]
,[State]
,[Telephone]
,[Email]
,[CreateDate]
FROM (select top (@pageSize*@pageIndex) [ID]
,[UserName]
,[IsAdmin]
,[Name]
,[State]
,[Telephone]
,[Email]
,[CreateDate]
,ROW_NUMBER() over(" + sort + ") as num from [user] where IsDeleted=0 " + where + ") as t where num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex*@pageSize " + sort;
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
userList = new List <User>();
User userInfo = null;
while (reader.Read())
{
userInfo = GetUserFromReader(reader);
userList.Add(userInfo);
}
}
return(userList);
}
public List <MemberUnit> GetAll(FilterEntityModel filterModel = null)
{
if (filterModel != null)
{
filterModel.FillWhereTbAsName(Constant.MEMBER_UNIT_AS_NAME); //筛选器添加表别名
}
List <MemberUnit> memberUnitList = _memberUnitProvider.GetAll(filterModel);
return(memberUnitList);
}
public List <FileDownload> GetAll(FilterEntityModel filterModel = null)
{
if (filterModel != null)
{
filterModel.FillWhereTbAsName(Constant.FILE_DOWNLOAD_AS_NAME); //筛选器添加表别名
}
List <FileDownload> fileDownloadList = _fileDownloadProvider.GetAll(filterModel);
return(fileDownloadList);
}
public List <TeaCertificate> GetAll(FilterEntityModel filterModel = null)
{
if (filterModel != null)
{
filterModel.FillWhereTbAsName(Constant.TEA_CERTIFICATE_AS_NAME); //筛选器添加表别名
}
List <TeaCertificate> teaCertificateList = _certificateProvider.GetTeaAll(filterModel);
return(teaCertificateList);
}
public List <Article> GetAll(FilterEntityModel filterModel = null)
{
if (filterModel != null)
{
filterModel.FillWhereTbAsName(Constant.ARTICLE_AS_NAME); //筛选器添加表别名
}
List <Article> articleList = _articleProvider.GetAll(filterModel);
return(articleList);
}
public ActionResult GetJsonData(SearchDataTable dt, SearchViewModel svm)
{
int totalNum = 0;
FilterEntityModel fillter = new FilterEntityModel();
fillter.PageIndex = dt.pageIndex;
fillter.PageSize = dt.length;
fillter.KeyValueList = new List <KeyValueObj>();
if (svm != null && !string.IsNullOrEmpty(svm.FilterKey) && !string.IsNullOrEmpty(svm.FilterVal))
{
string fkey = string.Empty;
SearchType ftype = (SearchType)Convert.ToInt16(svm.FilterKey);
switch (ftype)
{
case SearchType.Name:
fkey = "Name";
fillter.FilterType = FilterType.Like;
break;
case SearchType.State:
fkey = "State";
break;
case SearchType.Number:
fkey = "Telephone";
break;
}
fillter.KeyValueList.Add(new KeyValueObj()
{
Key = fkey, Value = svm.FilterVal
});
}
List <User> userList = uService.GetPage(fillter, out totalNum);
ReturnPageResultIList <User> data = new ReturnPageResultIList <Core.User>(userList, totalNum);
IList <UserViewModel> gmList = new List <UserViewModel>();
if (data != null && data.DataT != null)
{
gmList = data.DataT.Select(g => new UserViewModel()
{
Id = g.Id, RealName = g.Name, UserName = g.UserName, Phone = g.Telephone, State = (int)g.State, CreateTime = g.CreateDate.ToString("yyyy-MM-dd HH:mm"), Email = g.Email, IsSupAdmin = g.IsAdmin
}).ToList();
}
return(Json(new
{
recordsFiltered = data.totalRecords,
recordsTotal = data.totalPages,
data = gmList
}, JsonRequestBehavior.AllowGet));
}
public ActionResult GetJsonData(SearchDataTable dt, SearchViewModel svm)
{
int totalNum = 0;
FilterEntityModel fillter = new FilterEntityModel();
fillter.PageIndex = dt.pageIndex;
fillter.PageSize = dt.length;
fillter.KeyValueList = new List <KeyValueObj>();
if (svm != null && !string.IsNullOrEmpty(svm.FilterKey) && !string.IsNullOrEmpty(svm.FilterVal))
{
string fkey = string.Empty;
SearchType ftype = (SearchType)Convert.ToInt16(svm.FilterKey);
switch (ftype)
{
case SearchType.Title:
fkey = "Title";
fillter.FilterType = FilterType.Like;
break;
case SearchType.Type:
fkey = "Category";
break;
case SearchType.State:
fkey = "State";
break;
}
fillter.KeyValueList.Add(new KeyValueObj()
{
Key = fkey, Value = svm.FilterVal
});
}
List <FileDownload> downList = downService.GetPage(fillter, out totalNum);
ReturnPageResultIList <FileDownload> data = new ReturnPageResultIList <Core.FileDownload>(downList, totalNum);
IList <DownloadViewModel> gmList = new List <DownloadViewModel>();
if (data != null && data.DataT != null)
{
gmList = data.DataT.Select(g => new DownloadViewModel()
{
Id = g.Id, UserId = g.UserId, FileName = g.Name, CategoryName = g.CategoryName, State = (int)g.State, CreateTime = g.CreateDate.ToString("yyyy-MM-dd HH:mm"), Category = g.Category, FileType = g.Type, FileTitle = g.Title, UserName = g.UserName, Src = g.Src
}).ToList();
}
return(Json(new
{
recordsFiltered = data.totalRecords,
recordsTotal = data.totalPages,
data = gmList
}, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 检查是否存在
/// </summary>
/// <param name="value">名称</param>
/// <param name="type">id</param>
/// <returns></returns>
private JsonResult CheckIsExist(int id, string value, LoginType loginType)
{
ResultRetrun rmodel = new ResultRetrun();
string nameTemp = loginType.GetDescription();
FilterEntityModel filterModel = new FilterEntityModel();
filterModel.KeyValueList = new List <KeyValueObj>();
switch (loginType)
{
case LoginType.UserName:
filterModel.KeyValueList.Add(new KeyValueObj()
{
Key = "UserName", Value = value, FieldType = FieldType.String
});
break;
case LoginType.Telephone:
filterModel.KeyValueList.Add(new KeyValueObj()
{
Key = "Telephone", Value = value, FieldType = FieldType.String
});
break;
case LoginType.Email:
filterModel.KeyValueList.Add(new KeyValueObj()
{
Key = "Email", Value = value, FieldType = FieldType.String
});
break;
}
List <User> userList = uService.GetAll(filterModel);
if (userList != null && userList.Count > 0)
{
if (userList.Where(g => !g.Id.Equals(id)).Count() > 0)
{
rmodel.message = nameTemp + "已经存在";
}
else
{
rmodel.isSuccess = true;
}
}
else
{
rmodel.isSuccess = true;
}
return(Json(rmodel.isSuccess, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 获取根据条件分页获取会员集合
/// </summary>
/// <param name="filterModel">条件</param>
/// <param name="totalNum">总记录数</param>
/// <returns>会员集合</returns>
public List <User> GetPage(FilterEntityModel filterModel, out int totalNum)
{
totalNum = 0;
//参数验证
if (filterModel == null)
{
ErrorMsg = ErrorCode.ParameterNull;
return(null);
}
//获取数据
List <User> userList = _userProvider.GetPage(filterModel, out totalNum);
return(userList);
}
public List <Article> GetPage(FilterEntityModel filterModel, out int totalNum)
{
totalNum = 0;
//参数验证
if (filterModel == null)
{
ErrorMsg = ErrorCode.ParameterNull;
return(null);
}
filterModel.FillWhereTbAsName(Constant.ARTICLE_AS_NAME);//筛选器添加表别名
//获取数据
List <Article> articleList = _articleProvider.GetPage(filterModel, out totalNum);
return(articleList);
}
public List <TeaCertificate> GetPage(FilterEntityModel filterModel, out int totalNum)
{
totalNum = 0;
//参数验证
if (filterModel == null)
{
ErrorMsg = ErrorCode.ParameterNull;
return(null);
}
filterModel.FillWhereTbAsName(Constant.TEA_CERTIFICATE_AS_NAME);//筛选器添加表别名
//获取数据
List <TeaCertificate> teaCertificateList = _certificateProvider.GetTeaPage(filterModel, out totalNum);
return(teaCertificateList);
}
public List <MemberUnit> GetPage(FilterEntityModel filterModel, out int totalNum)
{
totalNum = 0;
//参数验证
if (filterModel == null)
{
ErrorMsg = ErrorCode.ParameterNull;
return(null);
}
filterModel.FillWhereTbAsName(Constant.MEMBER_UNIT_AS_NAME);//筛选器添加表别名
//获取数据
List <MemberUnit> memberUnitList = _memberUnitProvider.GetPage(filterModel, out totalNum);
return(memberUnitList);
}
public List <FileDownload> GetPage(FilterEntityModel filterModel, out int totalNum)
{
totalNum = 0;
//参数验证
if (filterModel == null)
{
ErrorMsg = ErrorCode.ParameterNull;
return(null);
}
filterModel.FillWhereTbAsName(Constant.FILE_DOWNLOAD_AS_NAME);//筛选器添加表别名
//获取数据
List <FileDownload> fileDownloadList = _fileDownloadProvider.GetPage(filterModel, out totalNum);
return(fileDownloadList);
}
/// <summary>
/// 获取所有会员信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>会员集合</returns>
public List <User> GetAll(FilterEntityModel condition)
{
string whereSort = condition == null ? "" : condition.Where + condition.OrderBy;
List <User> userList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT [ID]
,[UserName]
,[IsAdmin]
,[Name]
,[State]
,[Telephone]
,[Email]
,[CreateDate]
FROM [user] where IsDeleted=0 " + whereSort;
IList <DbParameter> parameList = null;
if (condition != null && condition.SqlParList.Count > 0)
{
parameList = new List <DbParameter>();
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
userList = new List <User>();
User userInfo = null;
while (reader.Read())
{
userInfo = GetUserFromReader(reader);
userList.Add(userInfo);
}
}
return(userList);
}
public List <CategoryDictionary> GetAll(List <CategoryType> categoryTypes)
{
FilterEntityModel filterModel = new FilterEntityModel();
filterModel.SortDict = new KeyValuePair <string, SortType>("id", SortType.Asc);
if (categoryTypes != null && categoryTypes.Count > 0)
{
filterModel.FilterType = FilterType.In;
filterModel.KeyValueList = new List <KeyValueObj>();
List <int> categorys = categoryTypes.Select(g => (int)g).ToList();
filterModel.KeyValueList.Add(new KeyValueObj()
{
Key = "Type", Value = categorys, FieldType = FieldType.Int, TbAsName = Constant.CATEGORY_DICTIONARY_AS_NAME
});
}
List <CategoryDictionary> categoryList = _categoryDictionaryProvider.GetAll(filterModel);
return(categoryList);
}
public List <CategoryDictionary> GetAll(CategoryType categoryType)
{
FilterEntityModel filterModel = new FilterEntityModel();
filterModel.SortDict = new KeyValuePair <string, SortType>("id", SortType.Asc);
if (categoryType != CategoryType.UnKnown)
{
filterModel.KeyValueList = new List <KeyValueObj>()
{
new KeyValueObj()
{
Key = "Type", Value = (int)categoryType, FieldType = FieldType.Int, TbAsName = Constant.CATEGORY_DICTIONARY_AS_NAME
}
};
}
List <CategoryDictionary> categoryList = _categoryDictionaryProvider.GetAll(filterModel);
return(categoryList);
}
/// <summary>
/// 获取所有证书信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>证书集合</returns>
public List <StuCertificate> GetStuAll(FilterEntityModel condition)
{
string whereSort = string.Empty;
if (condition != null)
{
condition.DefaultSort = SortType.Desc;
whereSort = condition.Where + condition.OrderBy;
}
List <StuCertificate> stuList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT sc.Id, sc.UserId, sc.Name, sc.Gender, sc.HeadImg, sc.Number,u.Name as UserName, sc.State, sc.Category, sc.Province, sc.City, sc.County, sc.CreateDate from StuCertificate sc inner join [User] u on sc.UserId=u.Id where sc.IsDeleted=0 " + whereSort;
IList <DbParameter> parameList = null;
if (condition != null && condition.SqlParList.Count > 0)
{
parameList = new List <DbParameter>();
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
stuList = new List <StuCertificate>();
StuCertificate stuInfo = null;
while (reader.Read())
{
stuInfo = GetStuFromReader(reader);
stuList.Add(stuInfo);
}
}
return(stuList);
}
/// <summary>
/// 获取所有类别字典信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>类别字典集合</returns>
public List <CategoryDictionary> GetAll(FilterEntityModel condition)
{
string whereSort = string.Empty;
if (condition != null)
{
condition.DefaultSort = SortType.Desc;
whereSort = condition.Where + condition.OrderBy;
}
List <CategoryDictionary> categoryList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT cd.Id, cd.UserId, cd.Parent, cd.Name, cd.Type, cd.State, cd.CreateDate,u.Name UserName,cdp.Name ParentName from CategoryDictionary cd left join [User] u on cd.UserId=u.Id left join CategoryDictionary cdp on cd.Parent=cdp.Id where cd.IsDeleted=0 " + whereSort;
IList <DbParameter> parameList = null;
if (condition != null && condition.SqlParList.Count > 0)
{
parameList = new List <DbParameter>();
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
categoryList = new List <CategoryDictionary>();
CategoryDictionary categoryInfo = null;
while (reader.Read())
{
categoryInfo = GetCategoryDictionaryFromReader(reader);
categoryList.Add(categoryInfo);
}
}
return(categoryList);
}
/// <summary>
/// 获取所有会员单位信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>会员集合</returns>
public List <MemberUnit> GetAll(FilterEntityModel condition)
{
string whereSort = string.Empty;
if (condition != null)
{
condition.DefaultSort = SortType.Desc;
whereSort = condition.Where + condition.OrderBy;
}
List <MemberUnit> MemberUnitList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT m.Id, m.Name, m.HeadImg, m.Star, m.Number, m.State, m.Category,cd.name as CategoryName, Province, City, County, m.CreateDate,m.Synopsis from MemberUnit m left join CategoryDictionary cd on m.category=cd.id where m.IsDeleted=0 " + whereSort;
IList <DbParameter> parameList = null;
if (condition != null && condition.SqlParList.Count > 0)
{
parameList = new List <DbParameter>();
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
MemberUnitList = new List <MemberUnit>();
MemberUnit MemberUnitInfo = null;
while (reader.Read())
{
MemberUnitInfo = GetMemberUnitFromReader(reader);
MemberUnitList.Add(MemberUnitInfo);
}
}
return(MemberUnitList);
}
/// <summary>
/// 获取所有轮播信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>轮播集合</returns>
public List <RotationChart> GetAll(FilterEntityModel condition)
{
string whereSort = string.Empty;
if (condition != null)
{
condition.DefaultSort = SortType.Desc;
whereSort = condition.Where + condition.OrderBy;
}
List <RotationChart> rotationList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT Id, ImgSrc, WebLink, State, Type, CreateDate from RotationChart where IsDeleted=0 " + whereSort;
IList <DbParameter> parameList = null;
if (condition != null && condition.SqlParList.Count > 0)
{
parameList = new List <DbParameter>();
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
rotationList = new List <RotationChart>();
RotationChart rotationInfo = null;
while (reader.Read())
{
rotationInfo = GetRotationChartFromReader(reader);
rotationList.Add(rotationInfo);
}
}
return(rotationList);
}
/// <summary>
/// 获取所有文件下载信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>文件下载集合</returns>
public List <FileDownload> GetAll(FilterEntityModel condition)
{
string whereSort = string.Empty;
if (condition != null)
{
condition.DefaultSort = SortType.Desc;
whereSort = condition.Where + condition.OrderBy;
}
List <FileDownload> fileDownloadList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT fd.Id, fd.UserId, fd.Name, fd.Title, fd.Category, fd.Type, fd.Src, fd.State, fd.HeadImg, fd.Synopsis,fd.CreateDate ,cd.Name as CategoryName,u.Name as UserName from FileDownload fd inner join CategoryDictionary cd on fd.category=cd.id left join [User] u on fd.userid=u.id where fd.IsDeleted=0 " + whereSort;
IList <DbParameter> parameList = null;
if (condition != null && condition.SqlParList.Count > 0)
{
parameList = new List <DbParameter>();
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
fileDownloadList = new List <FileDownload>();
FileDownload fileDownloadInfo = null;
while (reader.Read())
{
fileDownloadInfo = GetFileDownloadFromReader(reader);
fileDownloadList.Add(fileDownloadInfo);
}
}
return(fileDownloadList);
}
/// <summary>
/// 获取所有文章信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>文章集合</returns>
public List <Article> GetAll(FilterEntityModel condition)
{
string whereSort = string.Empty;
if (condition != null)
{
condition.DefaultSort = SortType.Desc;
whereSort = condition.Where + condition.OrderBy;
}
List <Article> articleList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT a.Id, a.UserId, a.Title, a.HeadImg, a.Content, a.Author, a.Section, a.State, a.ParCategory, a.Category,cd.Name as CategoryName,u.Name as UserName,pcd.Name as ParCategoryName, a.UpdateDate, a.CreateDate, a.Synopsis,a.PublishDate from Article a inner join CategoryDictionary cd on a.category=cd.id left join CategoryDictionary pcd on a.ParCategory=pcd.id left join [User] u on a.userid=u.id where a.IsDeleted=0 " + whereSort;
IList <DbParameter> parameList = null;
if (condition != null && condition.SqlParList.Count > 0)
{
parameList = new List <DbParameter>();
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
articleList = new List <Article>();
Article articleInfo = null;
while (reader.Read())
{
articleInfo = GetArticleFromReader(reader);
articleList.Add(articleInfo);
}
}
return(articleList);
}
/// <summary>
/// 获取会员单位分页信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <param name="totalNum">总条数</param>
/// <returns>会员集合</returns>
public List <MemberUnit> GetPage(FilterEntityModel condition, out int totalNum)
{
totalNum = 0;
condition.DefaultSort = SortType.Desc;
condition.SortTbAsName = Constant.MEMBER_UNIT_AS_NAME;
string sort = condition.OrderBy;
string asSort = condition.AsOrderBy;
string where = condition.Where;
List <MemberUnit> MemberUnitList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSqlQuery = @"select count(m.ID) from [MemberUnit] m left join CategoryDictionary cd on m.Category=cd.id where m.IsDeleted=0 " + where;//查询有多少条记录
IList <DbParameter> parameList = new List <DbParameter>();
parameList.Add(new SqlParameter("@pageSize", condition.PageSize));
parameList.Add(new SqlParameter("@pageIndex", condition.PageIndex));
if (condition.SqlParList.Count > 0)
{
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
totalNum = Convert.ToInt32(dbHelper.ExecuteScalar(strSqlQuery, parameList));
string strSql = @"SELECT [ID]
,[Name]
,[HeadImg]
,[Star]
,[Number]
,[State]
,[Category]
,[CategoryName]
,[Province]
,[City]
,[County]
,[CreateDate]
,[Synopsis]
FROM (select top (@pageSize*@pageIndex) m.[ID]
,m.[Name]
,m.[HeadImg]
,m.[Star]
,m.[Number]
,m.[State]
,m.[Category]
,cd.[Name] as CategoryName
,m.[Province]
,m.[City]
,m.[County]
,m.[CreateDate]
,m.[Synopsis]
,ROW_NUMBER() over(" + asSort + ") as num from [MemberUnit] m left join CategoryDictionary cd on m.Category=cd.id where m.IsDeleted=0 " + where + ") as t where num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex*@pageSize " + sort;
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
MemberUnitList = new List <MemberUnit>();
MemberUnit MemberUnitInfo = null;
while (reader.Read())
{
MemberUnitInfo = GetMemberUnitFromReader(reader);
MemberUnitList.Add(MemberUnitInfo);
}
}
return(MemberUnitList);
}
public ActionResult GetJsonData(SearchDataTable dt, SearchViewModel svm)
{
try
{
int totalNum = 0;
FilterEntityModel fillter = new FilterEntityModel();
fillter.PageIndex = dt.pageIndex;
fillter.PageSize = dt.length;
fillter.KeyValueList = new List <KeyValueObj>();
if (svm != null)
{
if (!string.IsNullOrEmpty(svm.ReserveField))
{
fillter.KeyValueReserves = new List <KeyValueObj>()
{
new KeyValueObj()
{
Key = "Section", Value = svm.ReserveField
}
};
}
if (!string.IsNullOrEmpty(svm.FilterKey) && !string.IsNullOrEmpty(svm.FilterVal))
{
SearchType ftype = (SearchType)Convert.ToInt16(svm.FilterKey);
KeyValueObj kvb = new KeyValueObj()
{
Value = svm.FilterVal
};
switch (ftype)
{
case SearchType.Title:
kvb.Key = "Title";
fillter.FilterType = FilterType.Like;
break;
case SearchType.State:
kvb.Key = "State";
break;
case SearchType.Type:
kvb.Key = "Category";
break;
case SearchType.Date:
kvb.Key = "PublishDate";
kvb.FieldType = FieldType.Date;
fillter.FilterType = FilterType.Between;
break;
}
fillter.KeyValueList.Add(kvb);
}
}
List <Article> downList = articleService.GetPage(fillter, out totalNum);
ReturnPageResultIList <Article> data = new ReturnPageResultIList <Core.Article>(downList, totalNum);
IList <ArticleViewModel> gmList = new List <ArticleViewModel>();
if (data != null && data.DataT != null)
{
gmList = data.DataT.Select(g => new ArticleViewModel()
{
Id = g.Id, UserId = g.UserId, Title = g.Title, CategoryName = !string.IsNullOrEmpty(g.ParCategoryName) ? g.ParCategoryName + "-" + g.CategoryName : g.CategoryName, State = (int)g.State, CreateTime = g.CreateDate.ToString("yyyy-MM-dd HH:mm"), PublishDate = g.PublishDate <= DateTime.MinValue ? "无发布日期" : g.PublishDate.ToString("yyyy-MM-dd HH:mm"), Category = g.Category, UserName = g.UserName, ParCategory = g.ParCategory, HeadImg = g.HeadImg, SmallHeadImg = GetThumb(g.HeadImg), Section = g.Section, ParCategoryName = g.ParCategoryName
}).ToList();
}
return(Json(new
{
recordsFiltered = data.totalRecords,
recordsTotal = data.totalPages,
data = gmList
}, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
AddLog("GetList", ex.ToString());
}
return(EmptyJsonResult());
}
/// <summary>
/// 获取文件下载分页信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <param name="totalNum">总条数</param>
/// <returns>文件下载集合</returns>
public List <FileDownload> GetPage(FilterEntityModel condition, out int totalNum)
{
totalNum = 0;
condition.DefaultSort = SortType.Desc;
condition.SortTbAsName = Constant.FILE_DOWNLOAD_AS_NAME;
string sort = condition.OrderBy;
string asSort = condition.AsOrderBy;
string where = condition.Where;
List <FileDownload> fileDownloadList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSqlQuery = @"select count(fd.ID) from [FileDownload] fd inner join CategoryDictionary cd on fd.Category=cd.id where fd.IsDeleted=0 " + where;//查询有多少条记录
IList <DbParameter> parameList = new List <DbParameter>();
parameList.Add(new SqlParameter("@pageSize", condition.PageSize));
parameList.Add(new SqlParameter("@pageIndex", condition.PageIndex));
if (condition.SqlParList.Count > 0)
{
foreach (var item in condition.SqlParList)
{
parameList.Add(new SqlParameter(item.Key, item.Value));
}
}
totalNum = Convert.ToInt32(dbHelper.ExecuteScalar(strSqlQuery, parameList));
string strSql = @"SELECT [ID]
,[UserId]
,[Name]
,[Title]
,[Category]
,[Type]
,[Src]
,[State]
,[HeadImg]
,[Synopsis]
,[CreateDate]
,[UserName]
,[CategoryName]
FROM (select top (@pageSize*@pageIndex) fd.[ID]
,fd.[UserId]
,fd.Name
,fd.[Title]
,fd.[Category]
,fd.[Type]
,fd.[Src]
,fd.[State]
,fd.[HeadImg]
,fd.Synopsis
,fd.[CreateDate]
,u.[Name] as UserName
,cd.Name as [CategoryName]
,ROW_NUMBER() over(" + asSort + ") as num from [FileDownload] fd inner join CategoryDictionary cd on fd.category=cd.id left join [User] u on fd.userid=u.id where fd.IsDeleted=0 " + where + ") as t where num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex*@pageSize " + sort;
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
fileDownloadList = new List <FileDownload>();
FileDownload fileDownloadInfo = null;
while (reader.Read())
{
fileDownloadInfo = GetFileDownloadFromReader(reader);
fileDownloadList.Add(fileDownloadInfo);
}
}
return(fileDownloadList);
}
