using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CommonHelper;
|
using Dapper;
|
using GasolineBlend.Entity;
|
|
namespace GasolineBlend.DAL
|
{
|
public class AccountDAL : BaseDAL
|
{
|
#region 列表集合List
|
/// <summary>
|
/// 获取用户信息全集
|
/// </summary>
|
/// <returns></returns>
|
public List<Account> GetAccountList()
|
{
|
var sql = $"select * from sysAccount order by id asc";
|
return Conn.Query<Account>(sql).ToList();
|
}
|
|
/// <summary>
|
/// 根据角色获取账户列表
|
/// </summary>
|
/// <param name="RoleId"></param>
|
/// <returns></returns>
|
public List<Account> GetAccountList(int RoleId)
|
{
|
var sql = $"select a.*,b.AccCompanyRemark as CompanyRemark,b.SearchLtdLimit,b.TypeNoteId from sysAccount as a left join sysAccountGroup as b ON a.CompanyId=b.AccCompanyId where a.RoleId={RoleId} and a.IsDeleted=0 order by a.id asc";
|
return Conn.Query<Account>(sql).ToList();
|
}
|
|
/// <summary>
|
/// 根据角色+企业Id获取账户列表
|
/// </summary>
|
/// <param name="RoleId"></param>
|
/// <param name="SysCompanyId"></param>
|
/// <returns></returns>
|
public List<Account> GetAccountList(int RoleId,int CompanyId)
|
{
|
var sql = $"select a.*,b.AccCompanyRemark as CompanyRemark,b.SearchLtdLimit,b.TypeNoteId from sysAccount as a left join sysAccountGroup as b ON a.CompanyId=b.AccCompanyId where a.RoleId={RoleId} and a.CompanyId={CompanyId} and a.IsDeleted=0 order by a.id asc";
|
return Conn.Query<Account>(sql).ToList();
|
}
|
|
/// <summary>
|
/// 提取不包含某个用户的用户组清单
|
/// </summary>
|
/// <param name="RoleId"></param>
|
/// <param name="CompanyId"></param>
|
/// <param name="UserIdExclude"></param>
|
/// <returns></returns>
|
//public List<Account> GetAccountList(int RoleId, int CompanyId,int UserIdExclude)
|
//{
|
// var sql = $"select * from sysAccount where RoleId={RoleId} and CompanyId={CompanyId} and Id<>{UserIdExclude} and IsDeleted=0 order by id asc";
|
// return Conn.Query<Account>(sql).ToList();
|
//}
|
#endregion
|
|
#region 获取信息Id
|
|
/// <summary>
|
/// 根据用户id获取用户信息
|
/// </summary>
|
/// <param name="id">用户id</param>
|
/// <returns></returns>
|
public Account GetAccount(int id = 0)
|
{
|
var sql = new StringBuilder("select a.*,b.AccCompanyRemark as CompanyRemark,b.SearchLtdLimit,b.TypeNoteId from sysAccount as a left join sysAccountGroup as b ON a.CompanyId=b.AccCompanyId");
|
DynamicParameters paras = new DynamicParameters();
|
if (id != 0)
|
{
|
sql = sql.Append(" where a.id=@id");
|
paras.Add("id", id);
|
return Conn.QueryFirstOrDefault<Account>(sql.ToString(), paras);
|
}
|
else
|
{
|
return null;
|
}
|
}
|
/// <summary>
|
/// 根据用户手机号获取用户id
|
/// </summary>
|
/// <param name="id">用户id</param>
|
/// <returns></returns>
|
public int GetMobileAccountid(string Mobile)
|
{
|
var sql = "SELECT id FROM sysAccount WHERE Mobile = @Mobile";
|
var result = Conn.Query<int>(sql, new { Mobile = Mobile }).SingleOrDefault();
|
if (result == 0)
|
{
|
return -1;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 根据Id 获取列表
|
/// </summary>
|
/// <param name="Id"></param>
|
/// <returns></returns>
|
public List<string> GetAccountByIdList(string Id)
|
{
|
var sql = $"select RealName from sysAccount where Id in({Id})";
|
return Conn.Query<string>(sql).ToList();
|
}
|
|
|
|
/// <summary>
|
/// 根据用户名获取用户信息
|
/// </summary>
|
/// <param name="loginName"></param>
|
/// <param name="companyRemark"></param>
|
/// <returns></returns>
|
public Account GetAccount(string loginName, string companyRemark )
|
{
|
var sql = new StringBuilder("select a.*,b.AccCompanyRemark as CompanyRemark from sysAccount as a left join sysAccountGroup as b ON a.CompanyId=b.AccCompanyId");
|
DynamicParameters paras = new DynamicParameters();
|
if (!loginName.IsNullOrEmpty())
|
{
|
sql = sql.Append(" where (a.loginName=@loginName or a.Mobile=@loginName) and b.AccCompanyRemark=@CompanyRemark");
|
paras.Add("loginName", loginName);
|
paras.Add("CompanyRemark", companyRemark);
|
return Conn.QueryFirstOrDefault<Account>(sql.ToString(), paras);
|
}
|
else
|
{
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 根据微信openId获取用户信息
|
/// </summary>
|
/// <param name="WXopenid"></param>
|
/// <returns></returns>
|
public Account GetAccount(string WXopenid)
|
{
|
var sql = new StringBuilder("select a.*,b.AccCompanyRemark as CompanyRemark from sysAccount as a left join sysAccountGroup as b ON a.CompanyId=b.AccCompanyId");
|
DynamicParameters paras = new DynamicParameters();
|
if (!WXopenid.IsNullOrEmpty())
|
{
|
sql = sql.Append(" where a.WXopenid=@WXopenid");
|
paras.Add("WXopenid", WXopenid);
|
return Conn.QueryFirstOrDefault<Account>(sql.ToString(), paras);
|
}
|
else
|
{
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 获取预警配置基本信息--邮件/微信
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public AccountAlarm GetAlarmInfo(int id)
|
{
|
var sql = new StringBuilder("select * from sysAccount");
|
DynamicParameters paras = new DynamicParameters();
|
if (id>0)
|
{
|
sql = sql.Append(" where id=@id");
|
paras.Add("id", id);
|
return Conn.QueryFirstOrDefault<AccountAlarm>(sql.ToString(), paras);
|
}
|
else
|
{
|
return null;
|
}
|
}
|
|
#endregion
|
|
#region 存在判断Id
|
/// <summary>
|
/// 判断用户是否存在
|
/// </summary>
|
/// <param name="account"></param>
|
/// <param name="ModifyFlag"></param>
|
/// <returns></returns>
|
public bool ExistAccount(Account account, bool ModifyFlag)
|
{
|
string strCompareSQL = (ModifyFlag == false ? "" : " and id<>@id");
|
var sql = $"select * from sysAccount where LoginName=@LoginName and CompanyId=@CompanyId" + strCompareSQL;
|
return Conn.ExecuteScalar<int>(sql, account) > 0;
|
}
|
/// <summary>
|
/// 判断用户是否存在Reg
|
/// </summary>
|
/// <param name="account"></param>
|
/// <returns></returns>
|
public bool ExistAccount(Account account)
|
{
|
var sql = $"select * from sysAccount where LoginName=@LoginName";
|
return Conn.ExecuteScalar<int>(sql, account) > 0;
|
}
|
|
/// <summary>
|
/// 判断用户名称是否重名
|
/// </summary>
|
/// <param name="account"></param>
|
/// <param name="ModifyFlag"></param>
|
/// <returns></returns>
|
public bool ExistAccountRealName(Account account, bool ModifyFlag)
|
{
|
string strCompareSQL = (ModifyFlag == false ? "" : " and id<>@id");
|
var sql = $"select * from sysAccount where RealName=@RealName and CompanyId=@CompanyId " + strCompareSQL;
|
return Conn.ExecuteScalar<int>(sql, account) > 0;
|
}
|
|
/// <summary>
|
/// 判断机构名称是否重名
|
/// </summary>
|
/// <param name="account"></param>
|
/// <param name="ModifyFlag"></param>
|
/// <returns></returns>
|
public bool ExistAccountCompanyName(Account account, bool ModifyFlag)
|
{
|
string strCompareSQL = (ModifyFlag == false ? "" : " and id<>@id");
|
var sql = $"select * from sysAccount where CompanyName=@CompanyName" + strCompareSQL;
|
return Conn.ExecuteScalar<int>(sql, account) > 0;
|
}
|
|
#endregion
|
|
#region 添加部分
|
/// <summary>
|
/// 添加用户账户信息
|
/// </summary>
|
/// <param name="account"></param>
|
/// <returns></returns>
|
public int AddAccount(Account account)
|
{
|
var sql = $"insert into sysAccount (LoginName,Password,AccountLimit,CompanyName,CompanyChar,ClassLabel,ClassFirst,ClassSecond,ClassThird,Country,Province,City,RealName,Mobile,Mail,QQ,Wechat,Address,RoleId,CompanyId,AuthResultFlag,MoneyMonitorId,CompanyMonitorId,CurrentTypeId,CurrentTypeName,CurrentRedirect) Values" +
|
$"(@LoginName,@Password,@AccountLimit,@CompanyName,@CompanyChar,@ClassLabel,@ClassFirst,@ClassSecond,@ClassThird,@Country,@Province,@City,@RealName,@Mobile,@Mail,@QQ,@Wechat,@Address,@RoleId,@CompanyId,@AuthResultFlag,@MoneyMonitorId,@CompanyMonitorId,@CurrentTypeId,@CurrentTypeName,@CurrentRedirect);select @@IDENTITY;";
|
return Conn.ExecuteScalar<int>(sql, account) ;
|
}
|
#endregion
|
|
#region 修改部分
|
/// <summary>
|
/// 修改用户账户信息
|
/// </summary>
|
/// <param name="account"></param>
|
/// <returns></returns>
|
public bool UpdateAccount(Account account)
|
{
|
var sql = $"update sysAccount set AccountLimit=@AccountLimit,CompanyName=@CompanyName," +
|
$"CompanyChar=@CompanyChar,ClassLabel=@ClassLabel,ClassFirst=@ClassFirst,ClassSecond=@ClassSecond,ClassThird=@ClassThird," +
|
$"Country=@Country,Province=@Province,City=@City,RealName=@RealName,Mobile=@Mobile,Mail=@Mail,QQ=@QQ,Wechat=@Wechat,Address=@Address,AuthResultFlag=@AuthResultFlag,MoneyMonitorId=@MoneyMonitorId,CompanyMonitorId=@CompanyMonitorId where Id=@Id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("AccountLimit", account.AccountLimit);
|
paras.Add("CompanyName", account.CompanyName);
|
paras.Add("CompanyChar", account.CompanyChar);
|
paras.Add("ClassLabel", account.ClassLabel);
|
paras.Add("ClassFirst", account.ClassFirst);
|
paras.Add("ClassSecond", account.ClassSecond);
|
paras.Add("ClassThird", account.ClassThird);
|
paras.Add("Country", account.Country);
|
paras.Add("Province", account.Province);
|
paras.Add("City", account.City);
|
paras.Add("RealName", account.RealName);
|
paras.Add("Mobile", account.Mobile);
|
paras.Add("Mail", account.Mail);
|
paras.Add("QQ", account.QQ);
|
paras.Add("Wechat", account.Wechat);
|
paras.Add("Address", account.Address);
|
paras.Add("AuthResultFlag",account.AuthResultFlag);
|
paras.Add("MoneyMonitorId", account.MoneyMonitorId);
|
paras.Add("CompanyMonitorId", account.CompanyMonitorId);
|
paras.Add("Id", account.Id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
/// <summary>
|
/// 修改当前版本状态
|
/// </summary>
|
/// <param name="CurrentTypeId"></param>
|
/// <param name="Id"></param>
|
/// <returns></returns>
|
public bool UpdCurrentTypeId(int CurrentTypeId,string CurrentTypeName,string Redirect ,int Id)
|
{
|
var sql = $"update sysAccount set CurrentTypeId={CurrentTypeId} ,CurrentTypeName='{CurrentTypeName}',CurrentRedirect='{Redirect}' where Id={Id}";
|
return Conn.Execute(sql) > 0;
|
}
|
/// <summary>
|
/// 更新用户基本信息
|
/// </summary>
|
/// <param name="account"></param>
|
/// <returns></returns>
|
public bool UpdateAccountBasic(Account account)
|
{
|
var sql = $"update sysAccount set RealName=@RealName,Mobile=@Mobile,Mail=@Mail,QQ=@QQ,Wechat=@Wechat,Address=@Address where Id=@Id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("RealName", account.RealName);
|
paras.Add("Mobile", account.Mobile);
|
paras.Add("Mail", account.Mail);
|
paras.Add("QQ", account.QQ);
|
paras.Add("Wechat", account.Wechat);
|
paras.Add("Address", account.Address);
|
paras.Add("Id", account.Id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 更新用户基本信息
|
/// </summary>
|
/// <param name="account"></param>
|
/// <returns></returns>
|
public bool UpdateAccountAddress(Account account)
|
{
|
var sql = $"update sysAccount set Address=@Address where Id=@Id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("Address", account.Address);
|
paras.Add("Id", account.Id);
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 绑定微信账户-登录
|
/// </summary>
|
/// <param name="userName"></param>
|
/// <param name="password"></param>
|
/// <param name="companyRemark"></param>
|
/// <param name="WXopenid"></param>
|
/// <param name="WXunionid"></param>
|
/// <returns></returns>
|
public bool BindWXAccount(string userName, string password, string companyRemark, string WXopenid, string WXunionid)
|
{
|
var sql = $"update sysAccount set WXopenid='{WXopenid}',WXunionid='{WXunionid}' where LoginName='{userName}' and Password='{password}' and CompanyId in (select AccCompanyId from sysAccountGroup where AccCompanyRemark='{companyRemark}')";
|
return Conn.Execute(sql) > 0;
|
}
|
|
/// <summary>
|
/// 绑定微信账户-预警
|
/// </summary>
|
/// <param name="userName"></param>
|
/// <param name="password"></param>
|
/// <param name="companyRemark"></param>
|
/// <param name="AlarmWXId"></param>
|
/// <returns></returns>
|
public bool BindWXAlarmAccount(string userName, string password, string companyRemark, string AlarmWXId)
|
{
|
var sql = $"update sysAccount set AlarmWXId='{AlarmWXId}' where LoginName='{userName}' and Password='{password}' and CompanyId in (select AccCompanyId from sysAccountGroup where AccCompanyRemark='{companyRemark}')";
|
return Conn.Execute(sql) > 0;
|
}
|
|
/// <summary>
|
/// 更新标准版默认的ProjId和Mode
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateDefaultProjIdMode(Account model)
|
{
|
var sql = "update sysAccount set DefaultProjId=@DefaultProjId,DefaultMode=@DefaultMode where Id=@Id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("DefaultProjId", model.DefaultProjId);
|
paras.Add("DefaultMode", model.DefaultMode);
|
paras.Add("Id", model.Id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 更新极速版默认的ProjId和Mode
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateDefaultProjIdModeEx(Account model)
|
{
|
var sql = "update sysAccount set DefaultProjIdEx=@DefaultProjIdEx,DefaultModeEx=@DefaultModeEx where Id=@Id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("DefaultProjIdEx", model.DefaultProjIdEx);
|
paras.Add("DefaultModeEx", model.DefaultModeEx);
|
paras.Add("Id", model.Id);
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 更新是否启用主标尺的标识(特定用户)
|
/// </summary>
|
/// <param name="id"></param>
|
/// <param name="RateGradeFlag"></param>
|
/// <returns></returns>
|
public bool UpdateRateGradeFlag(int id, bool RateGradeFlag)
|
{
|
var sql = "update sysAccount set RateGradeFlag=@RateGradeFlag where id=@id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("RateGradeFlag", RateGradeFlag);
|
paras.Add("Id", id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 企业监控邮件告警配置
|
/// </summary>
|
/// <param name="id"></param>
|
/// <param name="AlarmEmailFlag"></param>
|
/// <param name="AlarmEmail"></param>
|
/// <param name="AlarmEmailGrade"></param>
|
/// <returns></returns>
|
public bool UpdateAlarmEmail(int id, bool AlarmEmailFlag, string AlarmEmail, string AlarmEmailGrade)
|
{
|
var sql = "update sysAccount set AlarmEmailFlag=@AlarmEmailFlag,AlarmEmail=@AlarmEmail,AlarmEmailGrade=@AlarmEmailGrade where id=@id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("AlarmEmailFlag", AlarmEmailFlag);
|
paras.Add("AlarmEmail", AlarmEmail);
|
paras.Add("AlarmEmailGrade", AlarmEmailGrade);
|
paras.Add("Id", id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
/// <summary>
|
/// 企业监控邮件告警配置
|
/// </summary>
|
/// <param name="id"></param>
|
/// <param name="AlarmWXFlag"></param>
|
/// <param name="AlarmWXId"></param>
|
/// <param name="AlarmWXGrade"></param>
|
/// <returns></returns>
|
public bool UpdateAlarmWX(int id, bool AlarmWXFlag, string AlarmWXId, string AlarmWXGrade)
|
{
|
var sql = "update sysAccount set AlarmWXFlag=@AlarmWXFlag,AlarmWXId=@AlarmWXId,AlarmWXGrade=@AlarmWXGrade where id=@id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("AlarmWXFlag", AlarmWXFlag);
|
paras.Add("AlarmWXId", AlarmWXId);
|
paras.Add("AlarmWXGrade", AlarmWXGrade);
|
paras.Add("Id", id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 融资监控邮件告警配置
|
/// </summary>
|
/// <param name="id"></param>
|
/// <param name="AlarmEmailFlag"></param>
|
/// <param name="AlarmEmail"></param>
|
/// <param name="AlarmEmailGrade"></param>
|
/// <returns></returns>
|
public bool UpdateAlarmRZEmail(int id, bool AlarmEmailRZFlag, string AlarmEmailRZ)
|
{
|
var sql = "update sysAccount set AlarmEmailRZFlag=@AlarmEmailRZFlag,AlarmEmailRZ=@AlarmEmailRZ where id=@id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("AlarmEmailRZFlag", AlarmEmailRZFlag);
|
paras.Add("AlarmEmailRZ", AlarmEmailRZ);
|
paras.Add("Id", id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
/// <summary>
|
/// 融资监控邮件告警配置
|
/// </summary>
|
/// <param name="id"></param>
|
/// <param name="AlarmWXFlag"></param>
|
/// <param name="AlarmWXId"></param>
|
/// <param name="AlarmWXGrade"></param>
|
/// <returns></returns>
|
public bool UpdateAlarmRZWX(int id, bool AlarmWXRZFlag, string AlarmWXRZId)
|
{
|
var sql = "update sysAccount set AlarmWXRZFlag=@AlarmWXRZFlag,AlarmWXRZId=@AlarmWXRZId where id=@id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("AlarmWXRZFlag", AlarmWXRZFlag);
|
paras.Add("AlarmWXRZId", AlarmWXRZId);
|
paras.Add("Id", id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
|
/// <summary>
|
/// 更新最后登录时间和ip
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateLastLogin(Account model)
|
{
|
var sql = "update sysAccount set LastLoginTime=@LastLoginTime,LastLoginIp=@LastLoginIp where Id=@Id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("LastLoginTime", model.LastLoginTime);
|
paras.Add("LastLoginIp", model.LastLoginIp);
|
paras.Add("Id", model.Id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 添加企业管理员后更新CompanyID
|
/// </summary>
|
/// <param name="id"></param>
|
/// <param name="CompanyId"></param>
|
/// <returns></returns>
|
public bool UpdateCompanyIdbyId(int id, int CompanyId)
|
{
|
var sql = "update sysAccount set CompanyId=@CompanyId where Id=@Id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("CompanyId", CompanyId);
|
paras.Add("Id", id);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 修改密码
|
/// </summary>
|
/// <param name="userid">用户id</param>
|
/// <param name="pwd">密码（已加密）</param>
|
/// <returns></returns>
|
public bool ChangePassword(int userid, string pwd)
|
{
|
var sql = "update sysAccount set password=@password where Id=@Id;";
|
DynamicParameters paras = new DynamicParameters();
|
paras.Add("password", pwd);
|
paras.Add("Id", userid);
|
|
return Conn.Execute(sql, paras) > 0;
|
}
|
#endregion
|
|
#region 删除部分
|
/// <summary>
|
/// 删除当前单独账号
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public bool DeleteAccountbyId(int id)
|
{
|
var sql = $"delete from sysAccount where id={id}";
|
return Conn.Execute(sql) > 0;
|
//var sql = "update sysAccount set IsDeleted=1 where Id=@Id;";
|
//DynamicParameters paras = new DynamicParameters();
|
//paras.Add("Id", id);
|
//return Conn.Execute(sql, paras) > 0;
|
}
|
|
/// <summary>
|
/// 删除当前账号的所有子账号
|
/// </summary>
|
/// <param name="CompanyId"></param>
|
/// <returns></returns>
|
public bool DeleteAccountbyCompanyId(int CompanyId)
|
{
|
var sql = $"delete from sysAccount where CompanyId={CompanyId}";
|
return Conn.Execute(sql) > 0;
|
//var sql = "update sysAccount set IsDeleted=1 where CompanyId=@CompanyId;";
|
//DynamicParameters paras = new DynamicParameters();
|
//paras.Add("CompanyId", CompanyId);
|
//return Conn.Execute(sql, paras) > 0;
|
}
|
#endregion
|
|
|
|
#region 数量统计
|
public int GetAccountNum(int CompanyId)
|
{
|
var sql = $"select (Count(CompanyId)-1) as CompanyIdNum from sysAccount where CompanyId={CompanyId} and IsDeleted=0";
|
return Conn.ExecuteScalar<int>(sql);
|
}
|
#endregion
|
}
|
}
|
