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); }