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