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
    }
}