/// <summary> /// 获取关注作者列表 /// </summary> /// <param name="Start"></param> /// <param name="PageSize"></param> /// <returns></returns> public List <Concerns> GetConcernsList(int StartRow, int PageSize) { List <Concerns> result = new List <Concerns>(); try { //获取当前用户 AccountProvider _AccountProvider = new AccountProvider(); var user = _AccountProvider.GetCurrentUser(); if (user == null || user.id == 0) { return(result); } string sql = "select * from de2_concerns where createID=@userID order by createTime DESC"; MySqlClient _client = new MySqlClient(); result = _client.ExecuteQuery <Concerns>(sql.ToString(), new MySqlParameter("@userID", user.id)); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取关注作者列表", "GetCollectionList", ex); } return(result); }
/// <summary> /// 获取收藏列表 /// </summary> /// <param name="StartRow">开始行</param> /// <param name="PageSize">页面数据大小</param> /// <param name="userID">用户ID</param> /// <returns></returns> public List <Archives> GetCollectionList(int StartRow, int PageSize, int userID) { List <Archives> Result = new List <Archives>(); try { //生成检索语句 StringBuilder sql = new StringBuilder(); sql.AppendLine("select ar.* from de2_archives as ar join de2_collection as co on ar.id=co.aid where co.createID=@userID"); sql.AppendLine(" order by sortrank desc"); sql.AppendLine(" limit @start,@count "); MySqlClient _client = new MySqlClient(); Result = _client.ExecuteQuery <Archives>(sql.ToString(), new MySqlParameter("@start", StartRow), new MySqlParameter("@count", PageSize), new MySqlParameter("@userID", userID)); Result = FromateLitpic(Result); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取收藏列表", "GetCollectionList", ex); } return(Result); }
/// <summary> /// 根据父级ID删除目录 /// </summary> /// <param name="parentID"></param> /// <returns></returns> private bool DelMenuByParent(int parentID) { try { //获取目录列表 string sql = "select * from de2_aresource_menu where parentID=@parentID"; MySqlClient _Client = new MySqlClient(); var list = _Client.ExecuteQuery <AcademicResources_Menu>(sql, new MySqlParameter("@parentID", parentID)); if (list.Count > 0) { foreach (var item in list) { if (!DelResourceMenu(item.id).IsSuccess) { return(false); } } } } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "根据父级ID删除目录", "DelMenuByParent", ex); return(false); } return(true); }
/// <summary> /// 获取目录详细信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public AcademicResources_Menu GetAcademicResourcesMenuDetail(int id) { AcademicResources_Menu result = null; try { StringBuilder sql = new StringBuilder(); sql.AppendLine(@"select dam.id,dam.title,dam.parentID,dam.createTime,dam.createUserID ,u.userName as createUserName,dam.sourceTypeID,dat.typeName as sourceTypeName,dam.author,lastModifyTime,lastModifyUsrID from de2_aresource_menu dam LEFT JOIN de2_user u on u.id=dam.createUserID LEFT JOIN de2_aresource_type dat on dat.id=dam.sourceTypeID where dam.id=@id"); MySqlClient _Client = new MySqlClient(); result = _Client.ExecuteQuery <AcademicResources_Menu>(sql.ToString(), new MySqlParameter("@id", id)).FirstOrDefault(); return(result); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "删除目录", "DelResourceMenu", ex); return(null); } }
/// <summary> /// 根据父级id删除学术资源 /// </summary> /// <param name="parentID"></param> /// <returns></returns> public bool DelAcademicResourcesByParentID(int parentID) { try { //获取子级学术资源 string sql = "select * from de2_aresource where parentID=@parentID"; //执行sql MySqlClient _Client = new MySqlClient(); var result = _Client.ExecuteQuery <AcademicResources>(sql, new MySqlParameter("@parentID", parentID)); if (result.Count > 0) { foreach (AcademicResources item in result) { if (!DelAcademicResources(item)) { return(false); } } } return(true); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "根据父级id删除学术资源", "DelAcademicResourcesByParentID", ex); } return(false); }
/// <summary> /// 获取目录 /// </summary> /// <param name="Param"></param> /// <returns></returns> public BaseResopne <List <AcademicResources_Menu> > GetAcademicResourcesMenu(GetAcademicResourcesMenuParam Param) { BaseResopne <List <AcademicResources_Menu> > result = new BaseResopne <List <AcademicResources_Menu> >(); try { StringBuilder sql = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); sql.AppendLine(@"select dam.id,dam.title,dam.parentID,dam.createTime,dam.createUserID ,u.userName as createUserName,dam.sourceTypeID,dat.typeName as sourceTypeName,dam.author,lastModifyTime,lastModifyUsrID from de2_aresource_menu dam LEFT JOIN de2_user u on u.id=dam.createUserID LEFT JOIN de2_aresource_type dat on dat.id=dam.sourceTypeID"); sqlCount.AppendLine(@"select count(id) from de2_aresource_menu "); //排序 sql.AppendLine(" order by dam.createTime @orderByType"); sql.AppendLine(" limit @start,@pageSize "); //执行 MySqlClient _Client = new MySqlClient(); result.Total = Convert.ToInt32(_Client.ExecuteScalar(sqlCount.ToString())); result.Data = _Client.ExecuteQuery <AcademicResources_Menu>(sqlCount.ToString() , new MySqlParameter("@orderByType", Param.orderType) , new MySqlParameter("@start", Param.StartRow) , new MySqlParameter("@pageSize", Param.PageSize)); result.IsSuccess = true; } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取目录", "GetAcademicResourcesMenu", ex); result.IsSuccess = false; } return(result); }
/// <summary> /// 获取广告列表 /// </summary> /// <param name="count"></param> /// <returns></returns> public BaseResopne <List <Advertisement> > GetAdvertisementes(GetAdvertisementesParam Param) { BaseResopne <List <Advertisement> > result = new BaseResopne <List <Advertisement> >(); try { StringBuilder sql = new StringBuilder(); StringBuilder countSql = new StringBuilder(); sql.AppendLine("select * from de2_advertisement"); countSql.AppendLine("select count(id) from de2_advertisement"); sql.AppendLine("order by `index` desc limit @start,@count"); // string sql = string.Format("select * from de2_advertisement order by index desc limit @limit"); MySqlClient _client = new MySqlClient(); result.Total = Convert.ToInt32(_client.ExecuteScalar(countSql.ToString())); result.Data = _client.ExecuteQuery <Advertisement>(sql.ToString(), new MySqlParameter("@start", Param.StartRow), new MySqlParameter("@count", Param.PageSize)); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取广告列表", "GetAdvertisementList", ex); result.IsSuccess = false; result.Error = ex.Message; } return(result); }
/// <summary> /// 获取头条新闻 /// </summary> /// <param name="Count"></param> /// <returns></returns> public List <Archives> GetHeadline(int Count) { List <Archives> Result = new List <Archives>(); try { log.SaveLog("开始获取数据!"); AccountProvider _AccountProvider = new AccountProvider(); //获取访问权限 int VewBrowsePermissions = _AccountProvider.GetBrowsePermissions(); //生成检索语句 string sql = string.Format("select * from de2_archives where flag like '%h%' and arcrank!=-1 and( arcrank=0 or arcrank={0} )order by sortrank desc limit @limit", VewBrowsePermissions); MySqlClient _client = new MySqlClient(); log.SaveLog("开始执行SQL!"); Result = _client.ExecuteQuery <Archives>(sql, new MySqlParameter("@limit", Count)); log.SaveLog("完成SQL!"); Result = FromateLitpic(Result); } catch (Exception ex) { log.SaveLog("执行错误!" + ex.Message); LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取头条新闻", "GetHeadline", ex); } return(Result); }
/// <summary> /// 获取年度计划详情 /// </summary> /// <param name="wid"></param> /// <returns></returns> public WorkPlan GetWorkPlanDetial(int wid) { WorkPlan _result = new WorkPlan(); try { //获取年度计划详情 string sql = @" select p.id,`year`,startTime,endTime,p.`name`,content,scale,address,addressCity,addressCityID, addressProvince,addressProvinceID,workPlanTypeID ,t.`Name` workPlanTypeName from de2_workplan as p left join de2_workplantype as t on p.workPlanTypeID=t.id where p.id=@id"; MySqlClient _Client = new MySqlClient(); _result = _Client.ExecuteQuery <WorkPlan>(sql, new MySqlParameter("@id", wid)).FirstOrDefault(); //获取联系人 if (_result == null) { return(_result); } _result.Contacts = GetWorkPlanContacts(wid); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取年度计划详情", "GetWorkPlanDetial", ex); } return(_result); }
/// <summary> /// 缓存角色信息 /// </summary> /// <returns></returns> private bool CacheRole(DataCacheProvider Provider) { try { string sql = "select * from de2_user_roletype"; MySqlClient _Client = new MySqlClient(); var result = _Client.ExecuteQuery <RoleType>(sql); return(Provider.SetCache("roetype", result)); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "缓存角色信息", "CacheRole", ex); return(false); } }
/// <summary> /// 获取省列表 /// </summary> /// <returns></returns> public List <NativePlace> GetProvinceList() { List <NativePlace> result = new List <NativePlace>(); try { string sql = "select ename as Name,evalue as id from de2_sys_enum where egroup='nativeplace' and issign=0 order by disorder"; MySqlClient _Client = new MySqlClient(); result = _Client.ExecuteQuery <NativePlace>(sql); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取省列表", "GetProvinceList", ex); } return(result); }
/// <summary> /// 获取广告详细信息 /// </summary> /// <param name="key">主键</param> /// <returns></returns> public Advertisement GetAdvertisement(int key) { Advertisement result = null; try { string sql = string.Format("select * from de2_advertisement where id=@key"); MySqlClient _client = new MySqlClient(); result = _client.ExecuteQuery <Advertisement>(sql, new MySqlParameter("@key", key)).FirstOrDefault(); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取广告详细信息", "GetAdvertisementList", ex); } return(result); }
/// <summary> /// 记录广告点击次数 /// </summary> /// <param name="key">主键</param> /// <returns></returns> public Advertisement AdvertisementClickCount(int key) { Advertisement result = null; try { string sql = string.Format("update de2_advertisement set count=(count+1) where id=@key"); MySqlClient _client = new MySqlClient(); result = _client.ExecuteQuery <Advertisement>(sql, new MySqlParameter("@key", key)).FirstOrDefault(); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "记录广告点击次数", "AdvertisementClickCount", ex); } return(result); }
/// <summary> /// 获取职业列表 /// </summary> /// <returns></returns> public List <Occupation> GetOccupationList() { List <Occupation> result = new List <Occupation>(); try { string sql = "select * from de2_user_occupation order by oindex"; MySqlClient _Client = new MySqlClient(); result = _Client.ExecuteQuery <Occupation>(sql); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取职业列表", "GetOccupationList", ex); } return(result); }
/// <summary> /// 获取广告列表 /// </summary> /// <param name="count"></param> /// <returns></returns> public List <Advertisement> GetAdvertisementList(int count) { List <Advertisement> result = new List <Advertisement>(); try { string sql = string.Format("select * from de2_advertisement where isVail=true order by `index` desc limit @limit"); MySqlClient _client = new MySqlClient(); result = _client.ExecuteQuery <Advertisement>(sql, new MySqlParameter("@limit", count)); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取广告列表", "GetAdvertisementList", ex); } return(result); }
/// <summary> /// 获取学术资源详情 /// </summary> /// <param name="id"></param> /// <returns></returns> private AcademicResources GetAcademicResources(int id) { AcademicResources result = new AcademicResources(); try { string sql = "select * from de2_aresource where id=@id "; //执行sql MySqlClient _Client = new MySqlClient(); result = _Client.ExecuteQuery <AcademicResources>(sql, new MySqlParameter("@id", id)).FirstOrDefault(); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取学术资源详情", "GetAcademicResources", ex); } return(result); }
/// <summary> /// 获取联系人 /// </summary> /// <param name="wpid"></param> /// <returns></returns> public List <WorkPlanContacts> GetWorkPlanContacts(int wpid) { List <WorkPlanContacts> result = new List <WorkPlanContacts>(); try { // string sql = "select id ,phone,contactsName ,wid,'edit' as statue from de2_workplancontacts where wid=@wid"; MySqlClient _Client = new MySqlClient(); result = _Client.ExecuteQuery <WorkPlanContacts>(sql, new MySqlParameter("@wid", wpid)); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取联系人", "GetWorkPlanContacts", ex); } return(result); }
/// <summary> /// 获取文章内容信息 /// </summary> /// <param name="aid"></param> /// <returns></returns> public Addonarticle GetAddonarticle(int aid) { Addonarticle result = null; try { StringBuilder sql = new StringBuilder(); sql.AppendLine("select * from de2_addonarticle where aid=@id"); MySqlClient _Client = new MySqlClient(); result = _Client.ExecuteQuery <Addonarticle>(sql.ToString(), new MySqlParameter("@id", aid)).FirstOrDefault(); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取文件内容信息", "GetAddonarticle", ex); } return(result); }
/// <summary> /// 管理员登陆认证 /// </summary> /// <param name="Param"></param> /// <returns></returns> public LoginResult AdminCheckIn(LoginInParam Param) { LoginResult _LoginResult = new LoginResult(); try { //加密密码 Param.Param2 = EncryptPassword(Param.Param2); string sql = @"select u.id, userKey,loginName, userName,memberCode,m.level,rank.membername as levelName ,r.admintypeid as adminType from de2_user as u left join de2_user_member as m on u.id = m.userid left join de2_arcrank as rank on rank.id = m.`level` left join de2_user_role as r on u.id=r.uid where u.loginName=@loginName and u.password=@password and u.state=1 "; MySqlClient _MySqlClient = new MySqlClient(); User result = _MySqlClient.ExecuteQuery <User>(sql, new MySqlParameter("loginName", Param.Param1), new MySqlParameter("password", Param.Param2)).FirstOrDefault(); if (result == null) { _LoginResult.isSuccess = false; _LoginResult.errMessage = "用户名或密码错误"; return(_LoginResult); } if (result.adminType == 0 || result.adminType > 2) { _LoginResult.isSuccess = false; _LoginResult.errMessage = "用户名没有访问权限"; return(_LoginResult); } _LoginResult.isSuccess = true; _LoginResult.UserInfo = result; SetSession(result); WriteLoginLog(result.userKey, result.userName); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "管理员登陆认证", "AdminCheckIn", ex); _LoginResult.isSuccess = false; _LoginResult.errMessage = "登录失败"; } return(_LoginResult); }
/// <summary> /// 获取专业分会 /// </summary> /// <returns></returns> public List <Branch> GetBranchList() { List <Branch> result = new List <Branch>(); try { string sql = "select id,branchName from de2_user_member_branch order by `index` "; MySqlClient _MySqlClient = new MySqlClient(); result = _MySqlClient.ExecuteQuery <Branch>(sql); result.Insert(0, new Branch() { id = 0, branchName = "--", index = 0 }); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取专业分会", "GetBranchList", ex); } return(result); }
/// <summary> /// 获取所在地区 /// </summary> /// <returns></returns> public List <Area> GetAreaList() { List <Area> result = new List <Area>(); try { string sql = "select id, areaName from de2_user_member_area order by `index` "; MySqlClient _MySqlClient = new MySqlClient(); result = _MySqlClient.ExecuteQuery <Area>(sql); result.Insert(0, new Area() { id = 0, areaName = "--", index = 0 }); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取所在地区", "GetAreaList", ex); } return(result); }
/// <summary> /// 根据省ID 获取市 /// </summary> /// <param name="ParentID">省ID</param> /// <returns></returns> public List <NativePlace> GetCityList(string ParentID) { List <NativePlace> result = new List <NativePlace>(); try { if (string.IsNullOrEmpty(ParentID)) { return(result); } ParentID = ParentID.Substring(0, (ParentID.Length - 2)); string sql = @"select ename as Name,evalue as id from de2_sys_enum where egroup='nativeplace' and issign=1 and substring(evalue ,1,(LENGTH(evalue)-2))=@ParentID order by disorder "; MySqlClient _Client = new MySqlClient(); result = _Client.ExecuteQuery <NativePlace>(sql, new MySqlParameter("@ParentID", ParentID)); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "根据省ID 获取市", "GetCityList", ex); } return(result); }
/// <summary> /// 获取学术资源详细 /// </summary> /// <param name="parentID">父级ID</param> /// <returns></returns> public AcademicResources_File GetAcademicResourcesFile(int parentID) { AcademicResources_File result = null; try { StringBuilder sql = new StringBuilder(); sql.AppendLine(@"select daf.id,title ,createTime ,createUserID ,u.userName as createUserName ,targetFileUrl,sourceTypeID,dat.typeName as sourceTypeName,parentID from de2_aresource_file daf LEFT JOIN de2_user u on u.id=daf.createUserID LEFT JOIN de2_aresource_type dat on dat.id=daf.sourceTypeID"); //检索条件 sql.AppendLine("where parentID=@parentID"); MySqlClient _Client = new MySqlClient(); result = _Client.ExecuteQuery <AcademicResources_File>(sql.ToString(), new MySqlParameter("@parentID", parentID)).FirstOrDefault();; } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取目录", "GetAcademicResourcesMenu", ex); } return(result); }
/// <summary> /// 获取用户详细信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public Member GetMember(string ID) { if (string.IsNullOrEmpty(ID)) { return(null); } Member _Member = new Member(); try { string sql = @"select u.id, u.sex, u.loginName , u.address,u.addressCity, u.addressProvince ,u.addressCityID, u.addressProvinceID,u.age,u.email,u.mobileNO,u.levelID as userLeveID,l.levelName as userLeveName ,a.areaName as areaName, b.branchName as branchName , p.postName as post , s.`name` as state,m.state as stateID ,m.areaID,m.branchID,m.postID,u.oid as occupationID ,o.oname as occupationName,u.pid as professionID ,pr.typeName as professionName,m.memberID,m.memberName ,m.`level`,rank.membername as levelName from de2_user as u LEFT JOIN de2_user_level as l on l.id=u.levelID left join de2_user_member m on m.userid=u.id left join de2_arcrank rank on rank.id = m.`level` left join de2_user_member_area a on a.id = m.areaID left join de2_user_member_branch b on b.id = m.branchID LEFT JOIN de2_user_member_post p on p.id = m.postID LEFT JOIN de2_user_member_state s on s.id = u.state left join de2_user_occupation o on o.id=u.oid LEFT JOIN de2_user_profession pr on pr.id=u.pid where u.id=@ID"; MySqlClient _MySqlClient = new MySqlClient(); _Member = _MySqlClient.ExecuteQuery <Member>(sql, new MySqlParameter("ID", Convert.ToInt32(ID))).FirstOrDefault(); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取用户详细信息", "GetMember", ex); } return(_Member); }
/// <summary> /// 注册用户绑定 /// </summary> /// <param name="Param"></param> /// <returns></returns> public BindMemberResult BindMember(BindMemberParam Param) { BindMemberResult _result = new BindMemberResult(); try { //查询会员账号是否已被绑定 //查询会员是否存在 string sql = "select * from de2_user_member where memberID=@memberID and memberName=@memberName"; MySqlClient _MySqlClient = new MySqlClient(); Member _Member = _MySqlClient.ExecuteQuery <Member>(sql, new MySqlParameter("memberID", Param.MemberCode), new MySqlParameter("memberName", Param.UserName)).FirstOrDefault(); if (_Member == null) { _result.isSuccess = false; _result.errMessage = "会员证号验证失败"; return(_result); } if (_Member.userID != 0) { _result.isSuccess = false; _result.errMessage = "会员证号已被绑定请联系管理员"; return(_result); } //获取用户ID sql = "select * from de2_user where userKey=@userKey"; User UserInfo = _MySqlClient.ExecuteQuery <User>(sql, new MySqlParameter("userKey", Param.UserKey)).FirstOrDefault(); if (UserInfo == null) { _result.isSuccess = false; _result.errMessage = "注册用户不存在"; return(_result); } //关联会员信息 StringBuilder sqlb = new StringBuilder(); sqlb.AppendLine(" update de2_user_member set userid=@userid where memberID=@memberID ;"); sqlb.AppendLine(" update de2_user set memberCode= @memberID ,userName=@userName where id=@userid ;"); _MySqlClient.ExecuteNonQuery(sqlb.ToString(), new MySqlParameter("userid", UserInfo.id) , new MySqlParameter("memberID", _Member.memberID), new MySqlParameter("userName", _Member.memberName)); //设置session User SessionUserInfo = new User(); SessionUserInfo.id = UserInfo.id; SessionUserInfo.userKey = UserInfo.userKey; SessionUserInfo.loginName = UserInfo.loginName; SessionUserInfo.userName = UserInfo.userName; SessionUserInfo.memberCode = _Member.memberID; SessionUserInfo.level = _Member.level; SessionUserInfo.levelName = _Member.levelName; SetSession(SessionUserInfo); //修改用户信息表 _result.isSuccess = true; return(_result); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "注册用户绑定", "BindMember", ex); _result.isSuccess = false; _result.errMessage = "用户绑定失败"; } return(_result); }
/// <summary> /// 根据新闻类型获取新闻列表 /// </summary> /// <param name="StartRow"></param> /// <param name="PageSize"></param> /// <param name="TypeID"></param> /// <param name="Author"></param> /// <returns></returns> public BaseResopne <List <Archives> > GetNewsByTypeID(int StartRow, int PageSize, string TypeID, string SearchKey) { BaseResopne <List <Archives> > Result = new BaseResopne <List <Archives> >(); try { AccountProvider _AccountProvider = new AccountProvider(); //获取访问权限 int VewBrowsePermissions = _AccountProvider.GetBrowsePermissions(); //生成检索语句 StringBuilder sql = new StringBuilder(); StringBuilder countsql = new StringBuilder(); sql.AppendLine("select * from de2_archives where arcrank!=-1 and ( arcrank=0 or arcrank=@Permissions)"); countsql.AppendLine("select count(id) from de2_archives where arcrank!=-1 and ( arcrank=0 or arcrank=@Permissions)"); if (!string.IsNullOrEmpty(TypeID)) { if (TypeID.IndexOf(",") > 0) { sql.AppendLine("and typeid in (" + TypeID + ")"); countsql.AppendLine("and typeid in (" + TypeID + ")"); } else { sql.AppendLine(" and typeid=@typeid "); countsql.AppendLine(" and typeid=@typeid "); } } if (!string.IsNullOrEmpty(SearchKey)) { sql.AppendLine(" and (writer like @SearchKey or title like @SearchKey or keywords like @SearchKey)"); countsql.AppendLine(" and (writer like @SearchKey or title like @SearchKey or keywords like @SearchKey) "); } sql.AppendLine(" order by sortrank desc"); sql.AppendLine(" limit @start,@count "); MySqlClient _client = new MySqlClient(); Result.Total = Convert.ToInt32(_client.ExecuteScalar(countsql.ToString(), new MySqlParameter("@start", StartRow), new MySqlParameter("@count", PageSize), new MySqlParameter("@Permissions", VewBrowsePermissions), new MySqlParameter("@typeid", TypeID), new MySqlParameter("@SearchKey", "%" + SearchKey + "%"))); Result.Data = _client.ExecuteQuery <Archives>(sql.ToString(), new MySqlParameter("@start", StartRow), new MySqlParameter("@count", PageSize), new MySqlParameter("@Permissions", VewBrowsePermissions), new MySqlParameter("@typeid", TypeID), new MySqlParameter("@SearchKey", "%" + SearchKey + "%")); Result.Data = FromateLitpic(Result.Data); } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "根据新闻类型获取新闻列表", "GetNewsByTypeID", ex); Result.IsSuccess = false; Result.Error = ex.Message; } return(Result); }
/// <summary> /// 获取年度工作计划 /// </summary> /// <param name="Year"></param> /// <param name="Month"></param> /// <param name="StartRow"></param> /// <param name="PageSize"></param> /// <returns></returns> public BaseResopne <List <WorkPlan> > GetWorkPlanList(string Year, string Month, string StartRow, string PageSize, string Title, string StartTime, string EndTime) { BaseResopne <List <WorkPlan> > result = new BaseResopne <List <WorkPlan> >(); result.IsSuccess = true; try { StringBuilder sql = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); //获取工作计划 sql.AppendLine(@"select p.id,`year`,startTime,endTime,p.`name`,content,scale,address,addressCity,addressCityID, addressProvince,addressProvinceID,workPlanTypeID ,t.`Name` workPlanTypeName from de2_workplan as p left join de2_workplantype as t on p.workPlanTypeID=t.id where 1=1"); sqlCount.AppendLine(@"select count( p.id) from de2_workplan as p where 1=1"); //拼接where条件 if (!string.IsNullOrEmpty(Year)) { sql.AppendLine("and `year`=@`year`"); sqlCount.AppendLine("and `year`=@`year`"); } //月份 if (!string.IsNullOrEmpty(Month)) { sql.AppendLine("and MONTH(startTime)<=@Month and MONTH(endTime)>=@Month"); sqlCount.AppendLine("and MONTH(startTime)<=@Month and MONTH(endTime)>=@Month"); } //标题 if (!string.IsNullOrEmpty(Title)) { sql.AppendLine("and p.`name` like @title"); sqlCount.AppendLine("and p.`name` like @title"); } //开始时间 if (!string.IsNullOrEmpty(StartTime)) { sql.AppendLine("and startTime >=@StartTime"); sqlCount.AppendLine("and startTime >=@StartTime"); } //结束时间 if (!string.IsNullOrEmpty(EndTime)) { sql.AppendLine("and endTime <@EndTime"); sqlCount.AppendLine("and endTime <@EndTime"); EndTime = Convert.ToDateTime(EndTime).AddDays(1).ToString(); } int Start = 0; int Size = int.MaxValue; if (!string.IsNullOrEmpty(StartRow)) { Start = Convert.ToInt32(StartRow); } if (!string.IsNullOrEmpty(PageSize)) { Size = Convert.ToInt32(PageSize); } sql.AppendLine("limit @start,@count"); MySqlClient _client = new MySqlClient(); //获取总数 result.Total = Convert.ToInt32(_client.ExecuteScalar(sqlCount.ToString(), new MySqlParameter("@start", Start), new MySqlParameter("@count", Size), new MySqlParameter("@Month", Month), new MySqlParameter("@title", "%" + Title + "%"), new MySqlParameter("@StartTime", StartTime), new MySqlParameter("@EndTime", EndTime), new MySqlParameter("@`year`", Year))); //获取数据 result.Data = _client.ExecuteQuery <WorkPlan>(sql.ToString(), new MySqlParameter("@start", Start), new MySqlParameter("@count", Size), new MySqlParameter("@Month", Month), new MySqlParameter("@title", "%" + Title + "%"), new MySqlParameter("@StartTime", StartTime), new MySqlParameter("@EndTime", EndTime), new MySqlParameter("@`year`", Year)); //获取联系人 if (result.Data.Count > 0) { foreach (var item in result.Data) { item.Contacts = GetWorkPlanContacts(item.id); } } } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取年度工作计划", "GetSocietyIntroduction", ex); result.IsSuccess = false; result.Error = ex.Message; } return(result); }
/// <summary> /// 获取学术资源列表 /// </summary> /// <param name="Param"></param> /// <param name="isPublish"></param> /// <returns></returns> private BaseResopne <List <AcademicResources> > GetAcademicResourcesList(GetPeriodicalListParam Param, bool isPublish) { BaseResopne <List <AcademicResources> > result = new BaseResopne <List <AcademicResources> >(); try { StringBuilder sql = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); sql.AppendLine(@"select dac.id,dac.`name`,dac.parentID,dac.sourceTypeID,dat.typeName as sourceTypeName ,dac.createTime,u.userName as createUserName,dac.createUserID,dac.isMenu ,dac.isChild,dac.author,`year`,periodNumber from de2_aresource dac LEFT JOIN de2_user u on u.id=dac.createUserID LEFT JOIN de2_aresource_type dat on dat.id=dac.sourceTypeID where 1=1"); sql.AppendLine("select count(id) from de2_aresource where 1=1"); //检索条件 if (Param.sourceTypeID != 0) { sql.AppendLine("sourceTypeID=@sourceTypeID"); sqlCount.AppendLine("sourceTypeID=@sourceTypeID"); } //年度 if (!string.IsNullOrEmpty(Param.year)) { sql.AppendLine("`year`=@`year`"); sqlCount.AppendLine("`year`=@`year`"); } //期数 if (!string.IsNullOrEmpty(Param.periodNumber)) { sql.AppendLine("periodNumber=@periodNumber"); sqlCount.AppendLine("periodNumber=@periodNumber"); } //父级ID sql.AppendLine(" parentID=@parentID"); sqlCount.AppendLine(" parentID=@parentID"); //是否发布 sql.AppendLine(" isPublish=@isPublish"); sqlCount.AppendLine(" isPublish=@isPublish"); //排序 sql.AppendLine(" order by dam.createTime @orderByType"); sql.AppendLine(" limit @start,@pageSize "); //执行sql MySqlClient _client = new MySqlClient(); result.Total = Convert.ToInt32(_client.ExecuteScalar(sqlCount.ToString() , new MySqlParameter("@sourceTypeID", Param.sourceTypeID) , new MySqlParameter("@parentID", Param.parenId) , new MySqlParameter("@`year`", Param.year) , new MySqlParameter("@periodNumber", Param.periodNumber) , new MySqlParameter("@isPublish", isPublish))); result.Data = _client.ExecuteQuery <AcademicResources>(sql.ToString() , new MySqlParameter("@sourceTypeID", Param.sourceTypeID) , new MySqlParameter("@orderByType", Param.orderType) , new MySqlParameter("@parentID", Param.parenId) , new MySqlParameter("@isPublish", isPublish) , new MySqlParameter("@`year`", Param.year) , new MySqlParameter("@periodNumber", Param.periodNumber) , new MySqlParameter("@start", Param.StartRow) , new MySqlParameter("@pageSize", Param.PageSize)); result.IsSuccess = true; } catch (Exception ex) { LogFactory _LogFactory = new LogFactory(this.GetType()); _LogFactory.CreateLog(LogType.Error, "获取学术资源列表", "GetAcademicResourcesList", ex); result.IsSuccess = false; } return(result); }