/// <summary>
/// 验证用户是否存在
/// </summary>
/// <param name="LoginName"></param>
/// <param name="UserKey">新建时为空</param>
/// <returns></returns>
public bool LoginNameIsRepeat(string LoginName, Guid UserKey)
{
try
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select count(id) from de2_user where loginName=@loginName ");
if (UserKey != Guid.Empty)
{
sql.AppendLine(" && userKey=@userKey");
}
MySqlClient _MySqlClient = new MySqlClient();
int UserCount = 0;
if (UserKey != Guid.Empty)
{
UserCount = Convert.ToInt32(_MySqlClient.ExecuteScalar(sql.ToString(), new MySqlParameter("loginName", LoginName), new MySqlParameter("userKey", UserKey)));
}
else
{
UserCount = Convert.ToInt32(_MySqlClient.ExecuteScalar(sql.ToString(), new MySqlParameter("loginName", LoginName)));
}
if (UserCount > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
LogFactory _LogFactory = new LogFactory(this.GetType());
_LogFactory.CreateLog(LogType.Error, "验证用户是否存在", "LoginNameIsRepeat", ex);
return(true);
}
}
/// <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="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="aid">文章ID</param>
/// <param name="userid">当前用户</param>
/// <returns></returns>
public bool IsCollection(int aid, int userid)
{
try
{
string sql = "select count(id) from de2_collection where aid=@aid and createID=@createID";
MySqlClient _Client = new MySqlClient();
var count = Convert.ToInt32(_Client.ExecuteScalar(sql, new MySqlParameter("@aid", aid), new MySqlParameter("@createID", userid)));
if (count > 0)
{
return(true);
}
}
catch (Exception ex)
{
LogFactory _LogFactory = new LogFactory(this.GetType());
_LogFactory.CreateLog(LogType.Error, "验证文章是否被收藏", "IsCollection", ex);
}
return(false);
}
/// <summary>
/// 保存资源文件
/// </summary>
/// <param name="Data"></param>
/// <returns></returns>
public BaseResopne <Int32> SaveResourcesFile(AcademicResources_File Data)
{
BaseResopne <Int32> result = new BaseResopne <Int32>();
try
{
//获取当前登录
AccountProvider _AccountProvider = new AccountProvider();
var user = _AccountProvider.GetCurrentUser();
if (user == null)
{
result.IsSuccess = false;
result.Error = "获取当前用户信息失败";
return(result);
}
//判断数据是否存在
StringBuilder sql = new StringBuilder();
sql.AppendLine("select count(id) from de2_aresource_file where parentID=@parentID");
MySqlClient _Client = new MySqlClient();
var count = Convert.ToInt32(_Client.ExecuteScalar(sql.ToString(), new MySqlParameter("@parentID", Data.parentID)));
if (count == 0)
{
Data.createTime = DateTime.Now;
Data.createUserID = user.id;
Data.lastModifyTime = DateTime.Now;
Data.lastModifyUsrID = user.id;
AddResourcesFile(Data);
}
else
{
Data.lastModifyTime = DateTime.Now;
Data.lastModifyUsrID = user.id;
AddResourcesFile(Data);
}
}
catch (Exception ex)
{
LogFactory _LogFactory = new LogFactory(this.GetType());
_LogFactory.CreateLog(LogType.Error, "保存资源文件", "SaveResourcesFile", ex);
result.IsSuccess = false;
}
return(result);
}
/// <summary>
/// 修改登录密码
/// </summary>
/// <param name="Param"></param>
/// <returns></returns>
public UpdatePasswordResult UpdatePassword(UpdatePasswordParam Param)
{
UpdatePasswordResult _Result = new UpdatePasswordResult();
try
{
string oldPassword = EncryptPassword(Param.Param1);
string newPassword = EncryptPassword(Param.Param2);
User _User = GetCurrentUser();
//验证旧密码是否正确
string sql = @"select count(id) from de2_user where id=@id and password=@password";
MySqlClient _MySqlClient = new MySqlClient();
int count = Convert.ToInt32(_MySqlClient.ExecuteScalar(sql, new MySqlParameter("id", _User.id), new MySqlParameter("password", oldPassword)));
if (count == 0)
{
_Result.isSuccess = false;
_Result.errMessage = "原密码不正确";
return(_Result);
}
//验证新密码
RegUserResult CheckResult = CheckPassword(Param.Param2);
if (!CheckResult.isSuccess)
{
_Result.isSuccess = false;
_Result.errMessage = CheckResult.errMessage;
return(_Result);
}
//修改密码
sql = @"update de2_user set password=@password where id=@id ";
_MySqlClient.ExecuteNonQuery(sql, new MySqlParameter("id", _User.id), new MySqlParameter("@password", newPassword));
_Result.isSuccess = true;
return(_Result);
}
catch (Exception ex)
{
LogFactory _LogFactory = new LogFactory(this.GetType());
_LogFactory.CreateLog(LogType.Error, "修改登录密码", "UpdatePassword", ex);
}
return(_Result);
}
/// <summary>
/// 获取学会章程内容
/// </summary>
/// <param name="aid"></param>
/// <returns></returns>
public string GetConstitutionDetial()
{
string result = "";
try
{
//获取分类
MenuProvider _Provider = new MenuProvider();
string TypeID = _Provider.GetAboutTypeID(Constant.ConstitutionID);
//
string sql = "select content from de2_arctype where id=@id ";
//获取数据
MySqlClient _MySqlClient = new MySqlClient();
result = (string)_MySqlClient.ExecuteScalar(sql, new MySqlParameter("id", TypeID));
}
catch (Exception ex)
{
LogFactory _LogFactory = new LogFactory(this.GetType());
_LogFactory.CreateLog(LogType.Error, "获取学会章程内容", "GetConstitutionDetial", ex);
}
return(result);
}
/// <summary>
/// 获取常务理事会信息
/// </summary>
/// <returns></returns>
public string GetAffairsCouncil()
{
string Result = "";
try
{
//获取分类
MenuProvider _Provider = new MenuProvider();
string TypeID = _Provider.GetOrganizationalTypeID(Constant.AffairsCouncilID);
//
string sql = "select content from de2_arctype where id=@id ";
//获取数据
MySqlClient _MySqlClient = new MySqlClient();
Result = (string)_MySqlClient.ExecuteScalar(sql, new MySqlParameter("id", TypeID));
}
catch (Exception ex)
{
LogFactory _LogFactory = new LogFactory(this.GetType());
_LogFactory.CreateLog(LogType.Error, "获取常务理事会信息", "GetAffairsCouncil", ex);
}
return(Result);
}
/// <summary>
/// 验证作者是否被关注
/// </summary>
/// <param name="author">作者</param>
/// <param name="userid">当前用户</param>
/// <returns></returns>
public bool IsFocues(string author, int userid)
{
if (string.IsNullOrEmpty(author))
{
return(false);
}
try
{
string sql = "select count(id) from de2_concerns where authorName=@authorName and createID=@createID";
MySqlClient _Client = new MySqlClient();
var count = Convert.ToInt32(_Client.ExecuteScalar(sql, new MySqlParameter("@authorName", author), new MySqlParameter("@createID", userid)));
if (count > 0)
{
return(true);
}
}
catch (Exception ex)
{
LogFactory _LogFactory = new LogFactory(this.GetType());
_LogFactory.CreateLog(LogType.Error, "验证作者是否被关注", "IsFocues", ex);
}
return(false);
}
/// <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="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="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);
}