#region

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using CSFrameworkV5.Common;
using CSFrameworkV5.Core;
using CSFrameworkV5.Core.CodeGenerator;
using CSFrameworkV5.Interfaces;
using CSFrameworkV5.Models;

///*************************************************************************/
///*
///* 文件名    ：dalUser.cs                                
///* 程序说明  : 用户数据字典的DAL层
///* 原创作者  ：www.csframework.com 
///* 
///* Copyright 2006-2021 wwww.csframework.com, 保留所有权利.
///*
///**************************************************************************/

#endregion

namespace CSFrameworkV5.DataAccess
{
    /// <summary>
    ///     用户数据字典的DAL层
    /// </summary>
    [DefaultORM_UpdateMode(typeof(tb_MyUser), true)]
    public class dalUser : dalBaseDataDict, IBridge_User
    {
        public dalUser(Loginer loginer)
            : base(loginer)
        {
            _TableName = tb_MyUser.__TableName; //表名
            _KeyName = tb_MyUser.__KeyName; //主键字段            
            _ModelType = typeof(tb_MyUser);
            _Database = DatabaseProvider.SystemDatabase;
        }

        public bool CopyPermission(string sourceUser, string targetUser)
        {
            var sp = _Database.CreateSqlProc("usp_CopyPermission");
            sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            sp.AddParam("AccountFrom", DbType.String, sourceUser);
            sp.AddParam("AccountTo", DbType.String, targetUser);
            sp.AddParam("OperateUser", DbType.String, _Loginer.Account);
            var i = _Database.ExecuteCommand(sp.Command);
            return i > 0;
        }

        /// <summary>
        ///     删除用户
        /// </summary>
        /// <param name="account">帐号</param>
        /// <returns></returns>
        public bool DeleteUser(string account)
        {
            var sp = _Database.CreateSqlProc("usp_DeleteUser");
            sp.AddParam("Account", DbType.String, account);
            sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            var i = _Database.ExecuteCommand(sp.Command);
            return i > 0;
        }

        public bool DeleteUserGroup(string account, string groupCode)
        {
            var sql =
                $"DELETE FROM tb_MyGroupUser WHERE DataSetID={_Database.ParamSymboName}DataSetID AND Account={_Database.ParamSymboName}Account AND GroupCode={_Database.ParamSymboName}GroupCode";

            var cmd = _Database.CreateCommand(sql.ToStringEx());
            cmd.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            cmd.AddParam("Account", DbType.String, account);
            cmd.AddParam("GroupCode", DbType.String, groupCode);

            var i = _Database.ExecuteCommand(cmd.Command);
            return i > 0;
        }

        public bool DeleteUserRole(string account, string roleID)
        {
            var sql =
                $"DELETE FROM tb_MyUserRoles WHERE DataSetID={_Database.ParamSymboName}DataSetID AND Account={_Database.ParamSymboName}Account AND RoleID={_Database.ParamSymboName}RoleID";

            var cmd = _Database.CreateCommand(sql.ToStringEx());
            cmd.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            cmd.AddParam("Account", DbType.String, account);
            cmd.AddParam("RoleID", DbType.String, roleID);

            var i = _Database.ExecuteCommand(cmd.Command);
            return i > 0;
        }

        public bool DestroyRights(string account)
        {
            var p = _Database.ParamSymboName;

            var sql1 =
                $"DELETE FROM tb_MyUserRoles WHERE DataSetID={p}DataSetID AND Account={p}Account;";
            var sql2 =
                $"DELETE FROM tb_MyGroupUser WHERE DataSetID={p}DataSetID1 AND Account={p}Account1;";

            var cmd = _Database.CreateCommand(sql1 + sql2);
            cmd.AddParam("DataSetID", _Loginer.DBID);
            cmd.AddParam("DataSetID1", _Loginer.DBID);
            cmd.AddParam("Account", account);
            cmd.AddParam("Account1", account);

            _Database.ExecuteCommand(cmd.Command);

            return true;
        }

        /// <summary>
        ///     检查用户是否存在
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public bool ExistsUser(string account)
        {
            var sql =
                $"SELECT COUNT(*) FROM tb_MyUser where Account={_Database.ParamSymboName}Account";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("Account", DbType.String, account);
            var o = _Database.ExecuteScalar(cmd.Command);
            return int.Parse(o.ToStringEx()) > 0;
        }

        /// <summary>
        ///     获取用户数据
        /// </summary>
        /// <param name="account">帐号</param>
        /// <returns></returns>
        public DataTable GetUser(string account)
        {
            var sql = "SELECT * FROM tb_MyUser WHERE Account=" +
                      _Database.ParamSymboName + "Account";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("Account", DbType.String, account);
            var dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
            return dt;
        }

        public DataTable GetUserActionsList(string account)
        {
            var sp = _Database.CreateSqlProc("usp_GetUserActionsList");
            sp.AddParam("Account", DbType.String, account);
            sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            return _Database.GetTable(sp.Command, "usp_GetUserActionsList");
        }

        public DataTable GetUserGroups(string account)
        {
            var sql = "SELECT a.GroupCode,b.GroupName FROM tb_MyGroupUser a " +
                      "LEFT JOIN tb_MyGroup b ON a.GroupCode=b.GroupCode " +
                      "WHERE a.Account=" + _Database.ParamSymboName +
                      "Account AND a.DataSetID=" +
                      _Database.ParamSymboName + "DataSetID ";
            var sp = _Database.CreateCommand(sql);
            sp.AddParam("Account", DbType.String, account);
            sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            return _Database.GetTable(sp.Command, tb_MyGroup.__TableName);
        }

        public DataSet GetUserReportData(DateTime createDateFrom,
            DateTime createDateTo)
        {
            var cmd = _Database.CreateSqlProc("usp_GetUserList");
            cmd.AddParam("DateFrom", createDateFrom);
            cmd.AddParam("DateTo", createDateTo);
            return _Database.GetDataSet(cmd.Command);
        }

        public DataTable GetUserRoles(string currentUser)
        {
            var sql =
                "SELECT a.*,b.RoleName FROM tb_MyUserRoles a LEFT JOIN tb_MyRole b ON a.RoleID=b.RoleID WHERE a.DataSetID=" +
                _Database.ParamSymboName + "DataSetID AND a.Account=" +
                _Database.ParamSymboName + "Account";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            cmd.AddParam("Account", DbType.String, currentUser);
            return _Database.GetTable(cmd.Command, tb_MyUserRoles.__TableName);
        }

        public DataTable GetUserRoles4Picker(string currentUser, string content)
        {
            var sp = _Database.CreateSqlProc("usp_GetUserRoles4Picker");
            sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            sp.AddParam("Account", DbType.String, currentUser);
            sp.AddParam("SearchContent", DbType.String, content);
            return _Database.GetTable(sp.Command, tb_MyRole.__TableName);
        }

        /// <summary>
        ///     获取用户的角色(包括临时角色)
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public DataTable GetUserRolesAll(string account)
        {
            var sp = _Database.CreateSqlProc("usp_GetUserRoles");
            sp.AddParam("Account", DbType.String, account);
            sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            return _Database.GetTable(sp.Command, tb_MyUserRoles.__TableName);
        }

        /// <summary>
        ///     获取所有用户列表,预设表格数据，必须返回图片
        /// </summary>
        /// <returns></returns>
        public DataTable GetUsers()
        {
            var sql = "SELECT * FROM tb_MyUser";
            return _Database.GetTable(sql, "tb_MyUser");
        }

        /// <summary>
        ///     用户登录
        /// </summary>
        /// <param name="loginUser">登录信息</param>
        /// <returns></returns>
        public DataTable Login(LoginUser loginUser)
        {
            var cmd = _Database.CreateSqlProc("sp_sys_Login");
            cmd.AddParam("Account", DbType.String, loginUser.Account);
            cmd.AddParam("Password", DbType.String, loginUser.Password);
            cmd.AddParam("DataSetID", DbType.String, loginUser.DBID);
            cmd.AddParam("LoginUserType", DbType.String,
                (int)loginUser.LoginAuthType);
            cmd.AddParam("IP", DbType.String, loginUser.IP);
            cmd.AddParam("MAC", DbType.String, loginUser.MAC);
            var ds = _Database.GetDataSet(cmd.Command);

            if (ds.Tables.Count == 2)
            {
                var error = ConvertEx.ToString(ds.Tables[1].Rows[0][0]);
                if (error.Trim() != string.Empty)
                    throw new CustomException(error); //抛出异常

                if (ds.Tables[0].Rows.Count >= 1) return ds.Tables[0];

                return null;
            }

            return null;
        }

        /// <summary>
        ///     登出
        /// </summary>
        /// <param name="loginUser">登录信息</param>
        public void Logout(LoginUser loginUser)
        {
            var cmd = _Database.CreateSqlProc("sp_sys_Logout");
            cmd.AddParam("Account", DbType.String, loginUser.Account);
            cmd.AddParam("IP", DbType.String, loginUser.IP);
            cmd.AddParam("MAC", DbType.String, loginUser.MAC);
            _Database.ExecuteCommand(cmd.Command);
        }

        /// <summary>
        ///     修改用户密码
        /// </summary>
        /// <param name="account">帐号</param>
        /// <param name="pwd">密码</param>
        /// <returns></returns>
        public bool ModifyPassword(string account, string OldPwd, string NewPwd)
        {
            var sql = "UPDATE tb_MyUser SET Password=" +
                      _Database.ParamSymboName + "NewPwd WHERE Account=" +
                      _Database.ParamSymboName + "Account";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("OldPwd", DbType.String, OldPwd);
            cmd.AddParam("NewPwd", DbType.String, NewPwd);
            cmd.AddParam("Account", DbType.String, account);
            object o = _Database.ExecuteCommand(cmd.Command);
            return int.Parse(o.ToStringEx()) == 1;
        }

        /// <summary>
        ///     插入PDA用户表
        /// </summary>
        /// <param name="account">帐号</param>
        /// <param name="pwd">密码</param>
        /// <returns></returns>
        public bool insrer(string account, string OldPwd, string NewPwd)
        {
            var sql = string.Format(
                @"INSERT INTO [dbo].[POWPAA](PAA001,PAA002,PAA003,PAA004,PAA006,PAA008,PAA011)VALUES('{0}','{1}','{2}','IT','normal','9999','MES')",
                account, OldPwd, NewPwd);
            return _Database.ExecuteSQL(sql) == 1;
        }


        public DataTable Search(string content)
        {
            var sql = "SELECT * FROM tb_MyUser ";

            var cmd = _Database.CreateCommand("");

            if (!string.IsNullOrEmpty(content))
            {
                sql = sql +
                      $" WHERE Account LIKE {_Database.ParamSymboName}Account OR UserName LIKE {_Database.ParamSymboName}UserName ";
                cmd.AddParam("Account", "%" + content + "%");
                cmd.AddParam("UserName", "%" + content + "%");
            }

            cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(sql);

            var dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
            return dt;
        }

        /// <summary>
        ///     搜索用户资料
        /// </summary>
        /// <param name="content">搜索内容</param>
        /// <param name="ignoreGroup">忽略改组的用户</param>
        /// <returns></returns>
        public List<AccountModel> SearchEx(string content, string ignoreGroup)
        {
            var sql = "SELECT Account,UserName FROM tb_MyUser WHERE 1=1 ";

            var cmd = _Database.CreateCommand("");

            if (!string.IsNullOrEmpty(content))
            {
                sql = sql +
                      $" AND (Account LIKE {_Database.ParamSymboName}Account OR UserName LIKE {_Database.ParamSymboName}UserName) ";
                cmd.AddParam("Account", "%" + content + "%");
                cmd.AddParam("UserName", "%" + content + "%");
            }

            if (!string.IsNullOrEmpty(ignoreGroup))
            {
                sql = sql +
                      $" AND Account NOT IN (SELECT Account FROM tb_MyGroupUser Where GroupCode={_Database.ParamSymboName}GroupCode) ";
                cmd.AddParam("GroupCode", ignoreGroup);
            }

            cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(sql);

            var list = _Database.ExecuteReaderList<AccountModel>(cmd.Command);
            return list;
        }

        public bool SetLockState(string account, bool isLock)
        {
            var sql =
                $"UPDATE tb_MyUser SET IsLocked={_Database.ParamSymboName}IsLocked WHERE Account={_Database.ParamSymboName}Account";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("IsLocked", isLock ? "Y" : "N");
            cmd.AddParam("Account", account);
            var i = _Database.ExecuteCommand(cmd.Command);
            return true;
        }

        /// <summary>
        ///     判断用户名密码是否正确
        /// </summary>
        /// <param name="User"></param>
        /// <param name="Pw"></param>
        /// <returns></returns>
        public bool TryLogin(string user, string encodedPwd)
        {
            var sql = new StringBuilder();
            sql.Append(" SELECT COUNT(*) FROM tb_MyUser ");
            sql.Append(" WHERE  Account=" + _Database.ParamSymboName +
                       "User AND Password=" + _Database.ParamSymboName +
                       "Pwd");
            var cmd = _Database.CreateCommand(sql.ToStringEx());
            cmd.AddParam("User", DbType.String, user);
            cmd.AddParam("Pwd", DbType.String, encodedPwd);
            return ConvertEx.ToInt(_Database.ExecuteScalar(cmd.Command)) > 0;
        }

        /// <summary>
        ///     跟据表名创建SQL命令生成器
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        protected override IGenerateSqlCommand CreateSqlGenerator(
            DataTable table)
        {
            Type ORM = null;

            if (table.TableName == tb_MyUser.__TableName)
                ORM = typeof(tb_MyUser);

            if (ORM == null) throw new Exception(table.TableName + "表没有ORM模型！");

            return new GenerateSqlCmdByTableFields(ORM, table,
                GeneratorFactory);
        }

        public override DataTable GetLookupData()
        {
            var sql =
                "SELECT Account,UserName,Password,FlagAdmin,DataSets FROM tb_MyUser ORDER BY UserName";
            return _Database.GetTable(sql, tb_MyUser.__TableName);
        }

        /// <summary>
        ///     获取窗体的可用权限值
        /// </summary>
        /// <param name="account">登录帐号</param>
        /// <param name="menuName">菜单名称</param>
        /// <returns></returns>
        public int GetUserActions(string account, string menuName)
        {
            var sp = _Database.CreateSqlProc("usp_GetUserActions");
            sp.AddParam("Account", DbType.String, account);
            sp.AddParam("menuName", DbType.String, menuName);
            sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
            var dt = _Database.GetTable(sp.Command);
            if (dt.Rows.Count == 0) return 0;

            return ConvertEx.ToInt(dt.Rows[0]["Actions"]);
        }

        /// <summary>
        ///     跟据Novell网帐号获取系统帐号
        /// </summary>
        /// <param name="novellAccount">Novell网帐号</param>
        /// <returns></returns>
        public DataTable GetUserByNovellID(string novellAccount)
        {
            var sql =
                $"SELECT * FROM tb_MyUser WHERE NovellAccount={_Database.ParamSymboName}NovellAccount";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("NovellAccount", DbType.String, novellAccount);
            var dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
            return dt;
        }

        public DataTable GetUserCodeNames()
        {
            var sql = "SELECT DISTINCT Account,UserName FROM tb_MyUser";
            return _Database.GetTable(sql, "tb_MyUser");
        }

        public DataTable GetUserDirect(string account)
        {
            var sql = "SELECT * FROM tb_MyUser WHERE Account=" +
                      _Database.ParamSymboName + "Account";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("Account", DbType.String, account);
            var dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
            return dt;
        }

        /// <summary>
        ///     仅返回帐号和密码资料，用于服务端的实时检测
        /// </summary>
        /// <returns></returns>
        public static DataTable GetUserList4Cache()
        {
            var sp =
                DatabaseProvider.SystemDatabase.CreateSqlProc(
                    "usp_GetUserList4Cache");
            var dt =
                DatabaseProvider.SystemDatabase.GetTable(sp.Command,
                    tb_MyUser.__TableName);
            return dt;
        }
    }
}