632 lines
22 KiB
C#
632 lines
22 KiB
C#
using Learun.Util;
|
||
using Learun.Util.SqlSugar;
|
||
using SqlSugar;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.ComponentModel.Design;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using DbType = System.Data.DbType;
|
||
|
||
namespace Learun.Application.Organization
|
||
{
|
||
/// <summary>
|
||
/// 版 本 PIT-ADMS V7.0.3 敏捷开发框架
|
||
/// Copyright (c) 2013-2018 Hexagon PPM
|
||
/// 创建人：研发部
|
||
/// 日 期：2017.03.04
|
||
/// 描 述：用户模块数据操作服务类
|
||
/// </summary>
|
||
public class UserService
|
||
{
|
||
#region 仓储
|
||
Repository<UserEntity> _userRepository => new Repository<UserEntity>();
|
||
#endregion
|
||
#region 属性 构造函数
|
||
private string fieldSql;
|
||
public UserService()
|
||
{
|
||
fieldSql = @"
|
||
t.F_UserId,
|
||
t.F_EnCode,
|
||
t.F_Account,
|
||
t.F_Password,
|
||
t.F_Secretkey,
|
||
t.F_RealName,
|
||
t.F_NickName,
|
||
t.F_HeadIcon,
|
||
t.F_QuickQuery,
|
||
t.F_SimpleSpelling,
|
||
t.F_Gender,
|
||
t.F_Birthday,
|
||
t.F_Mobile,
|
||
t.F_Telephone,
|
||
t.F_Email,
|
||
t.F_OICQ,
|
||
t.F_WeChat,
|
||
t.F_MSN,
|
||
t.F_CompanyId,
|
||
t.F_DepartmentId,
|
||
t.F_SecurityLevel,
|
||
t.F_OpenId,
|
||
t.F_Question,
|
||
t.F_AnswerQuestion,
|
||
t.F_CheckOnLine,
|
||
t.F_AllowStartTime,
|
||
t.F_AllowEndTime,
|
||
t.F_LockStartDate,
|
||
t.F_LockEndDate,
|
||
t.F_SortCode,
|
||
t.F_DeleteMark,
|
||
t.F_EnabledMark,
|
||
t.F_Description,
|
||
t.F_CreateDate,
|
||
t.F_CreateUserId,
|
||
t.F_CreateUserName,
|
||
t.F_ModifyDate,
|
||
t.F_ModifyUserId,
|
||
t.F_ModifyUserName,
|
||
t.PasswordUpdateTime,
|
||
t.WX_OpenId
|
||
";//update by chenkai 20210322
|
||
}
|
||
#endregion
|
||
|
||
#region 获取数据
|
||
|
||
/// <summary>
|
||
/// 获取实体,通过用户账号
|
||
/// </summary>
|
||
/// <param name="account">用户账号</param>
|
||
/// <returns></returns>
|
||
public UserEntity GetEntityByAccount(string account)
|
||
{
|
||
try
|
||
{
|
||
var strSql = new StringBuilder();
|
||
strSql.Append("SELECT ");
|
||
strSql.Append(fieldSql);
|
||
strSql.Append(" FROM LR_Base_User t ");
|
||
strSql.Append(" WHERE t.F_Account = @account AND t.F_DeleteMark = 0 ");
|
||
//return this.BaseRepository().FindEntity<UserEntity>(strSql.ToString(), new { account = account });
|
||
var x = SqlSugarHelper.Db.Queryable<UserEntity>().ToList();
|
||
return SqlSugarHelper.Db.Ado.SqlQuerySingle<UserEntity>(strSql.ToString(), new { account = account });
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 用户列表(根据公司主键)
|
||
/// </summary>
|
||
/// <param name="companyId">公司主键</param>
|
||
/// <returns></returns>
|
||
public List<UserEntity> GetList(string companyId)
|
||
{
|
||
try
|
||
{
|
||
|
||
var List = this.GetAllList();
|
||
return List.Where(t => t.F_CompanyId == companyId).
|
||
OrderBy(t => t.F_DepartmentId).OrderBy(x => x.F_RealName).ToList();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 用户列表(根据公司主键)(分页)
|
||
/// </summary>
|
||
/// <param name="companyId"></param>
|
||
/// <param name="departmentId"></param>
|
||
/// <param name="pagination"></param>
|
||
/// <param name="keyword"></param>
|
||
/// <returns></returns>
|
||
public List<UserEntity> GetPageList(string companyId, string departmentId, Pagination pagination, string keyword)
|
||
{
|
||
try
|
||
{
|
||
var strSql = new StringBuilder();
|
||
strSql.Append("SELECT ");
|
||
strSql.Append(fieldSql.Replace("t.F_Password,", "").Replace("t.F_Secretkey,", ""));
|
||
strSql.Append($" FROM LR_Base_User t WHERE t.F_DeleteMark = 0 AND t.F_CompanyId = '{companyId}' ");
|
||
|
||
if (!string.IsNullOrEmpty(departmentId))
|
||
{
|
||
strSql.Append($" AND t.F_DepartmentId = '{departmentId}' ");
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(keyword))
|
||
{
|
||
keyword = "%" + keyword + "%";
|
||
strSql.Append($" AND( t.F_Account like '{keyword}' or t.F_RealName like '{keyword}' or t.F_Mobile like '{keyword}' ) ");
|
||
}
|
||
|
||
//return this.BaseRepository().FindList<UserEntity>(strSql.ToString(), new { companyId, departmentId, keyword }, pagination);
|
||
var AllUsers = SqlSugarHelper.Db.Queryable<UserEntity>().Where(x => x.F_DeleteMark == 0 && x.F_CompanyId == companyId);
|
||
if (!string.IsNullOrEmpty(departmentId))
|
||
{
|
||
AllUsers = AllUsers.Where(x => x.F_DepartmentId == departmentId);
|
||
}
|
||
if (!string.IsNullOrEmpty(keyword))
|
||
{
|
||
AllUsers = AllUsers.Where(x => x.F_Account.Contains(keyword) || x.F_RealName.Contains(keyword) || x.F_Mobile.Contains(keyword));
|
||
}
|
||
return AllUsers.ToPageList(pagination.page, pagination.rows);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 用户列表,全部
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public List<UserEntity> GetAllList()
|
||
{
|
||
try
|
||
{
|
||
var List = _userRepository.GetList();
|
||
foreach (var user in List)
|
||
{
|
||
user.F_Password = ""; user.F_Secretkey = "";
|
||
}
|
||
return List.Where(t => t.F_DeleteMark == 0).
|
||
OrderBy(t => t.F_DepartmentId).OrderBy(x => x.F_RealName).ToList();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 用户列表（导出Excel）
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public DataTable GetExportList()
|
||
{
|
||
try
|
||
{
|
||
var strSql = new StringBuilder();
|
||
strSql.Append(@"SELECT u.F_Account
|
||
,u.F_RealName
|
||
,CASE WHEN u.F_Gender=1 THEN '男' WHEN u.F_Gender=0 THEN '女' ELSE '' END AS F_Gender
|
||
,u.F_Birthday
|
||
,u.F_Mobile
|
||
,u.F_Telephone
|
||
,u.F_Email
|
||
,u.F_WeChat
|
||
,u.F_MSN
|
||
,o.F_FullName AS F_Company
|
||
,d.F_FullName AS F_Department
|
||
,u.F_Description
|
||
,u.F_CreateDate
|
||
,u.F_CreateUserName
|
||
FROM LR_Base_User u
|
||
LEFT JOIN LR_Base_Department d ON u.F_DepartmentId=d.F_DepartmentId
|
||
INNER JOIN LR_Base_Company o ON u.F_CompanyId=o.F_CompanyId WHERE u.F_DeleteMark = 0 ");
|
||
return SqlSugarHelper.Db.Ado.GetDataTable(strSql.ToString());
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 用户实体
|
||
/// </summary>
|
||
/// <param name="keyValue">主键值</param>
|
||
/// <returns></returns>
|
||
public UserEntity GetEntity(string keyValue)
|
||
{
|
||
try
|
||
{
|
||
var strSql = new StringBuilder();
|
||
strSql.Append("SELECT ");
|
||
strSql.Append("t.*,t2.F_FullName ");
|
||
strSql.Append(" FROM LR_Base_User t LEFT JOIN lr_base_company t2 on t.F_CompanyId=t2.F_CompanyId");
|
||
strSql.Append(" WHERE t.F_UserId = @F_UserId AND t.F_DeleteMark = 0 ");
|
||
return SqlSugarHelper.Db.Ado.SqlQuerySingle<UserEntity>(strSql.ToString(), new { F_UserId = keyValue });
|
||
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 获取超级管理员用户列表
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public IEnumerable<UserEntity> GetAdminList()
|
||
{
|
||
try
|
||
{
|
||
return _userRepository.GetList(t => t.F_SecurityLevel == 1);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据OpenId获取用户
|
||
/// </summary>
|
||
/// <param name="account">用户账号</param>
|
||
/// <returns></returns>
|
||
public UserEntity GetEntityByOpenId(string openId)
|
||
{
|
||
try
|
||
{
|
||
var strSql = new StringBuilder();
|
||
strSql.Append("SELECT ");
|
||
strSql.Append(fieldSql);
|
||
strSql.Append(" FROM LR_Base_User t ");
|
||
strSql.Append(" WHERE t.WX_OpenId = @openId AND t.F_DeleteMark = 0 ");
|
||
return SqlSugarHelper.Db.Ado.SqlQuerySingle<UserEntity>(strSql.ToString(), new { openId = openId });
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 验证数据
|
||
/// <summary>
|
||
/// 账户不能重复
|
||
/// </summary>
|
||
/// <param name="account">账户值</param>
|
||
/// <param name="keyValue">主键</param>
|
||
/// <returns></returns>
|
||
public bool ExistAccount(string account, string keyValue)
|
||
{
|
||
try
|
||
{
|
||
//var expression = LinqExtensions.True<UserEntity>();
|
||
//expression = expression.And(t => t.F_Account == account);
|
||
//if (!string.IsNullOrEmpty(keyValue))
|
||
//{
|
||
// expression = expression.And(t => t.F_UserId != keyValue);
|
||
//}
|
||
//return this.BaseRepository().IQueryable(expression).Count() == 0 ? true : false;
|
||
UserEntity userEntity = _userRepository.GetFirst(t => t.F_Account == account && t.F_DeleteMark == 0);
|
||
return userEntity == null ? false : true;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 提交数据
|
||
/// <summary>
|
||
/// 删除
|
||
/// </summary>
|
||
/// <param name="keyValue">主键</param>
|
||
public void DeleteUser(string keyValue)
|
||
{
|
||
try
|
||
{
|
||
_userRepository.DeleteById(keyValue);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 虚拟删除
|
||
/// </summary>
|
||
/// <param name="keyValue">主键</param>
|
||
public void VirtualDelete(string keyValue)
|
||
{
|
||
try
|
||
{
|
||
//UserEntity entity = new UserEntity()
|
||
//{
|
||
// F_UserId = keyValue,
|
||
// F_DeleteMark = 1
|
||
//};
|
||
UserEntity entity = _userRepository.GetById(keyValue);
|
||
entity.F_DeleteMark = 1;
|
||
_userRepository.Update(entity);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 保存用户表单（新增、修改）
|
||
/// </summary>
|
||
/// <param name="keyValue">主键值</param>
|
||
/// <param name="userEntity">用户实体</param>
|
||
/// <returns></returns>
|
||
public void SaveEntity(string keyValue, UserEntity userEntity)
|
||
{
|
||
try
|
||
{
|
||
if (string.IsNullOrEmpty(keyValue))
|
||
{
|
||
userEntity.Create();
|
||
userEntity.F_Secretkey = Md5Helper.Encrypt(CommonHelper.CreateNo(), 16).ToLower();
|
||
userEntity.F_Password = Md5Helper.Encrypt(DESEncrypt.Encrypt(userEntity.F_Password, userEntity.F_Secretkey).ToLower(), 32).ToLower();
|
||
_userRepository.Insert(userEntity);
|
||
}
|
||
else
|
||
{
|
||
userEntity.Modify(keyValue);
|
||
//userEntity.F_Secretkey = null;
|
||
//userEntity.F_Password = null;
|
||
_userRepository.Update(userEntity);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 修改用户登录密码
|
||
/// </summary>
|
||
/// <param name="keyValue">主键值</param>
|
||
/// <param name="password">新密码（MD5 小写）</param>
|
||
public void RevisePassword(string keyValue, string password, bool reset)
|
||
{
|
||
//update by chenkai 20210322
|
||
try
|
||
{
|
||
//UserEntity userEntity = new UserEntity();
|
||
UserEntity userEntity = _userRepository.GetById(keyValue);
|
||
userEntity.Modify(keyValue);
|
||
userEntity.F_Secretkey = Md5Helper.Encrypt(CommonHelper.CreateNo(), 16).ToLower();
|
||
userEntity.PasswordUpdateTime = DateTime.Now;
|
||
userEntity.F_Password = Md5Helper.Encrypt(DESEncrypt.Encrypt(password, userEntity.F_Secretkey).ToLower(), 32).ToLower();
|
||
//重置密码时需要清空
|
||
if (reset)
|
||
{
|
||
//日期需要为NULL时，设置为最小日期
|
||
userEntity.PasswordUpdateTime = DateTime.Parse("0001-01-01 00:00:00");
|
||
}
|
||
_userRepository.Update(userEntity);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 修改用户状态
|
||
/// </summary>
|
||
/// <param name="keyValue">主键值</param>
|
||
/// <param name="state">状态：1-启动；0-禁用</param>
|
||
public void UpdateState(string keyValue, int state)
|
||
{
|
||
try
|
||
{
|
||
//UserEntity userEntity = new UserEntity();
|
||
UserEntity userEntity = _userRepository.GetById(keyValue);
|
||
userEntity.Modify(keyValue);
|
||
userEntity.F_EnabledMark = state;
|
||
_userRepository.Update(userEntity);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 修改用户信息
|
||
/// </summary>
|
||
/// <param name="userEntity">实体对象</param>
|
||
public void UpdateEntity(UserEntity userEntity)
|
||
{
|
||
try
|
||
{
|
||
_userRepository.Update(userEntity);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 修改用户OpenId
|
||
/// </summary>
|
||
/// <param name="keyValue">主键值</param>
|
||
/// <param name="openId">openId</param>
|
||
public void UpdateOpenId(string keyValue, string openId)
|
||
{
|
||
try
|
||
{
|
||
string sql = string.Format("Update lr_base_user set WX_OpenId='{1}' where F_UserId='{0}'", keyValue, openId);
|
||
//this.BaseRepository().ExecuteBySql(sql);
|
||
SqlSugarHelper.Db.Ado.ExecuteCommand(sql);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
if (ex is ExceptionEx)
|
||
{
|
||
throw;
|
||
}
|
||
else
|
||
{
|
||
throw ExceptionEx.ThrowServiceException(ex);
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 扩展方法
|
||
/// <summary>
|
||
/// 获取通用的人员选择
|
||
/// </summary>
|
||
/// <param name="queryJson">查询参数</param>
|
||
/// <returns></returns>
|
||
public List<UserEntity> GetCommonSelectUsers(Pagination pagination, string queryJson)
|
||
{
|
||
var queryParam = queryJson.ToJObject();
|
||
StringBuilder strSql = new StringBuilder();
|
||
//var dp = new DynamicParameters(new { });
|
||
var dp = new List<SugarParameter>();
|
||
strSql.Append(@"SELECT
|
||
t.*
|
||
FROM lr_base_user t
|
||
WHERE t.F_DeleteMark = 0 AND t.F_EnabledMark = 1 AND t.F_SecurityLevel IS NULL ");
|
||
|
||
if (!queryParam["keyword"].IsEmpty())
|
||
{
|
||
dp.Add(new SugarParameter("keyword", "%" + queryParam["keyword"].ToString() + "%", DbType.String));
|
||
strSql.Append(" AND (t.F_Account LIKE @keyword OR t.F_RealName LIKE @keyword)");
|
||
}
|
||
if (!queryParam["companyId"].IsEmpty())
|
||
{
|
||
dp.Add(new SugarParameter("F_CompanyId", queryParam["companyId"].ToString(), DbType.String));
|
||
strSql.Append(" AND t.F_CompanyId = @F_CompanyId");
|
||
}
|
||
if (!queryParam["postId"].IsEmpty() && !queryParam["ProjectId"].IsEmpty())
|
||
{
|
||
dp.Add(new SugarParameter("F_ObjectId", queryParam["postId"].ToString(), DbType.String));
|
||
dp.Add(new SugarParameter("ProjectId", queryParam["ProjectId"].ToString(), DbType.String));
|
||
strSql.Append(" AND t.F_UserId IN (SELECT F_UserId FROM lr_base_userrelation WHERE F_ObjectId = @F_ObjectId AND ProjectId = @ProjectId) ");
|
||
}
|
||
else if (!queryParam["postId"].IsEmpty())
|
||
{
|
||
dp.Add(new SugarParameter("F_ObjectId", queryParam["postId"].ToString(), DbType.String));
|
||
strSql.Append(" AND t.F_UserId IN (SELECT F_UserId FROM lr_base_userrelation WHERE F_ObjectId = @F_ObjectId) ");
|
||
}
|
||
else if (!queryParam["ProjectId"].IsEmpty() && queryParam["ProjectId"].ToString() != "00000000-0000-0000-0000-000000000001")
|
||
{
|
||
dp.Add(new SugarParameter("ProjectId", queryParam["ProjectId"].ToString(), DbType.String));
|
||
strSql.Append(" AND t.F_UserId IN (SELECT F_UserId FROM lr_base_userrelation WHERE F_Category = 2 AND ProjectId = @ProjectId) ");
|
||
}
|
||
|
||
if (pagination == null)
|
||
{
|
||
//return this.BaseRepository().FindList<UserEntity>(strSql.ToString(), dp);
|
||
return SqlSugarHelper.Db.SqlQueryable<UserEntity>(strSql.ToString()).AddParameters(dp).ToList();
|
||
}
|
||
else
|
||
{
|
||
//return this.BaseRepository().FindList<UserEntity>(strSql.ToString(), dp, pagination);
|
||
return SqlSugarHelper.Db.SqlQueryable<UserEntity>(strSql.ToString()).AddParameters(dp).ToPageList(pagination.page, pagination.rows);
|
||
}
|
||
}
|
||
#endregion
|
||
}
|
||
}
|