using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

using MySql.Data.MySqlClient;

namespace FangYar.OracleDAL
{
    /// <summary>
    /// 用户（Oracle数据库实现）
    /// </summary>
    public class BaseUserDAL : FangYar.IDAL.BaseUserIDAL
    {
        #region 私有方法
        
        /// <summary>
        /// 把DataRow行转成实体类对象
        /// </summary>
        private void DataRowToModel(FangYar.Model.BaseUserModel model, DataRow dr)
        {
            if (!DBNull.Value.Equals(dr["ID"]))
                model.ID = dr["ID"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_UID"]))
                model.USERS_UID = dr["USERS_UID"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_NAME"]))
                model.USERS_NAME = dr["USERS_NAME"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_PWD"]))
                model.USERS_PWD = dr["USERS_PWD"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_AUTH"]))
                model.USERS_AUTH = dr["USERS_AUTH"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_RULE"]))
                model.USERS_RULE = dr["USERS_RULE"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_TYPE"]))
                model.USERS_TYPE = dr["USERS_TYPE"].ToString();

        }
        /// <summary>
        /// 把DataRow行转成实体类对象
        /// </summary>
        private FangYar.Model.BaseUserModel DataRowToModel(DataRow dr)
        {
            FangYar.Model.BaseUserModel model = new Model.BaseUserModel();
            if (!DBNull.Value.Equals(dr["ID"]))
                model.ID = dr["ID"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_UID"]))
                model.USERS_UID = dr["USERS_UID"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_NAME"]))
                model.USERS_NAME = dr["USERS_NAME"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_PWD"]))
                model.USERS_PWD = dr["USERS_PWD"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_AUTH"]))
                model.USERS_AUTH = dr["USERS_AUTH"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_RULE"]))
                model.USERS_RULE = dr["USERS_RULE"].ToString();
            if (!DBNull.Value.Equals(dr["USERS_TYPE"]))
                model.USERS_TYPE = dr["USERS_TYPE"].ToString();

            return model;


        }
        /// <summary>
        /// 把DataTable行转成实体类List集合
        /// </summary>
        private List<FangYar.Model.BaseUserModel> DataTableToList(DataTable dt)
        {
            List<FangYar.Model.BaseUserModel> modellist = new List<Model.BaseUserModel>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow myRow in dt.Rows)
                {
                    modellist.Add(DataRowToModel(myRow));
                }
            }
            return modellist;

        }
        #endregion

        #region 基本方法

        /// <summary>
        /// 添加用户
        /// </summary>
        public bool Add(Model.BaseUserModel user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TBL_BASE_USERS(USERS_UID,USERS_PWD,USERS_NAME,USERS_TYPE)");
            strSql.Append(" values ");
            strSql.Append("(@USERS_UID,@USERS_PWD,@USERS_NAME,@USERS_TYPE)");

            MySqlParameter[] paras = { 
                                       new MySqlParameter("@USERS_UID",MySqlDbType.VarChar,50),
                                       new MySqlParameter("@USERS_PWD",MySqlDbType.VarChar,50),
                                       new MySqlParameter("@USERS_NAME",MySqlDbType.VarChar,50),
                                       new MySqlParameter("@USERS_TYPE",MySqlDbType.VarChar,50)
                    };
            paras[0].Value = user.USERS_UID;
            paras[1].Value = user.USERS_PWD;
            paras[2].Value = user.USERS_NAME;
            paras[3].Value = user.USERS_TYPE;
            int i = FangYar.Common.MySqlHelper.ExecuteSql(strSql.ToString(), paras);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        public bool Edit(Model.BaseUserModel user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TBL_BASE_USERS set ");
            strSql.Append("USERS_UID=@USERS_UID,");
            strSql.Append("USERS_PWD=@USERS_PWD,");
            strSql.Append("USERS_NAME=@USERS_NAME,");
            strSql.Append("USERS_RULE=@USERS_RULE,");
            strSql.Append("USERS_AUTH=@USERS_AUTH,");
            strSql.Append("USERS_TYPE=@USERS_TYPE ");
            strSql.Append(" where ID=@ID");

            MySqlParameter[] paras = { 
                                   new MySqlParameter("@USERS_UID",user.USERS_UID),
                                   new MySqlParameter("@USERS_PWD",user.USERS_PWD),
                                   new MySqlParameter("@USERS_NAME",user.USERS_NAME),
                                   new MySqlParameter("@USERS_RULE",user.USERS_RULE),
                                   new MySqlParameter("@USERS_AUTH",user.USERS_AUTH),
                                   new MySqlParameter("@USERS_TYPE",user.USERS_TYPE),
                                   new MySqlParameter("@ID",user.ID)
                                   };
            paras[0].Value = user.USERS_UID;
            paras[1].Value = user.USERS_PWD;
            paras[2].Value = user.USERS_NAME;
            paras[3].Value = user.USERS_RULE;
            paras[4].Value = user.USERS_AUTH;
            paras[5].Value = user.USERS_TYPE;
            paras[6].Value = user.ID;

            int i = FangYar.Common.MySqlHelper.ExecuteSql(strSql.ToString(), paras); ;
            if (i > 0)
                return true;
            else
                return false;
        }


        /// <summary>
        /// 删除用户（可批量删除，删除用户同时删除对应的权限和所处的部门）
        /// </summary>
        public bool Delete(string IDList)
        {
            string sql = "delete from TBL_BASE_USERS where ID in ('" + IDList + "')";
            try
            {
                FangYar.Common.MySqlHelper.ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 根据用户账号获取用户
        /// </summary>
        public Model.BaseUserModel GetModelByUserID(string USERS_UID)
        {
            string sql = "select * from TBL_BASE_USERS where USERS_UID =@USERS_UID";
            FangYar.Model.BaseUserModel user = null;
            MySqlParameter[] paras = {
                    new MySqlParameter("@USERS_UID",MySqlDbType.VarChar,50)//
                };
            paras[0].Value = USERS_UID;
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);

            if (dt.Rows.Count > 0)
            {
                user = new FangYar.Model.BaseUserModel();
                DataRowToModel(user, dt.Rows[0]);
                return user;
            }
            else
                return null;
        }

        /// <summary>
        /// 根据ID获取用户
        /// </summary>
        public Model.BaseUserModel GetModelByID(string ID)
        {
            string sql = "select * from TBL_BASE_USERS where ID =@ID";
            FangYar.Model.BaseUserModel user = null;
            MySqlParameter[] paras = {
                    new MySqlParameter("@ID",MySqlDbType.VarChar,50)//
                };
            paras[0].Value = ID;
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);

            if (dt.Rows.Count > 0)
            {
                user = new FangYar.Model.BaseUserModel();
                DataRowToModel(user, dt.Rows[0]);
                return user;
            }
            else
                return null;
        }


        #endregion

        #region 分页

        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public string QueryJsonList(int PageIndex, int PageSize, string strwhere)
        {

            return FangYar.Common.JsonHelper.ToJson(QueryPage(PageIndex, PageSize, strwhere));
        }
        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public string QueryJsonList(int PageIndex, int PageSize, string strwhere, string order)
        {

            return FangYar.Common.JsonHelper.ToJson(QueryPage(PageIndex, PageSize, strwhere, order));
        }
        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public List<FangYar.Model.BaseUserModel> QueryList(int PageIndex, int PageSize, string strwhere)
        {

            return DataTableToList(QueryPage(PageIndex, PageSize, strwhere));
        }
        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public List<FangYar.Model.BaseUserModel> QueryList(int PageIndex, int PageSize, string strwhere, string order)
        {

            return DataTableToList(QueryPage(PageIndex, PageSize, strwhere, order));
        }
        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public DataTable QueryPage(int PageIndex, int PageSize, string strwhere)
        {

            return FangYar.Common.MySqlHelper.QueryPage(PageIndex, PageSize, "TBL_BASE_USERS", strwhere);
        }

        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <param name="strwhere">排序</param>
        /// <returns></returns>
        public DataTable QueryPage(int PageIndex, int PageSize, string strwhere, string order)
        {

            return FangYar.Common.MySqlHelper.QueryPage(PageIndex, PageSize, "TBL_BASE_USERS", strwhere, order);
        }
        /// <summary>
        /// 存储过程分页
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <param name="strwhere">排序</param>
        /// <returns></returns>
        public void QueryProc(int PageIndex, int PageSize, string strwhere, string order)
        {
            int totalcount=0;
            FangYar.Common.SqlPagerHelperOra.Select(PageSize,PageIndex,out  totalcount, "TBL_BASE_USERS","*", null,null, null);
        
         
        }
        #endregion

        #region 扩展业务方法

        /// <summary>
        /// 首次登陆强制修改密码
        /// </summary>
        public bool InitUserPwd(Model.BaseUserModel user)
        {
            string sql = "update TBL_BASE_USERS set USERS_PWD =@USERS_PWD where ID =@ID";
            MySqlParameter[] paras = {
                    new MySqlParameter("@USERS_PWD",MySqlDbType.VarChar,50),
                     new MySqlParameter("@ID",MySqlDbType.VarChar,50)
                };
            paras[0].Value = user.USERS_PWD;
            paras[1].Value = user.ID;
            int i= FangYar.Common.MySqlHelper.ExecuteSql(sql, paras);;
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        public bool ChangePwd(Model.BaseUserModel user)
        {
            string sql = "update TBL_BASE_USERS set USERS_PWD =@USERS_PWD where ID =@ID";
            MySqlParameter[] paras = {
                    new MySqlParameter("@USERS_PWD",MySqlDbType.VarChar,50),
                     new MySqlParameter("@ID",MySqlDbType.VarChar,50)
                };
            paras[0].Value = user.USERS_PWD;
            paras[1].Value = user.ID;
            int i = FangYar.Common.MySqlHelper.ExecuteSql(sql, paras); ;
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 用户登录
        /// </summary>
        public Model.BaseUserModel UserLogin(string loginID, string loginPwd)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select ID,USERS_UID,USERS_PWD,USERS_NAME,USERS_RULE,USERS_AUTH,USERS_TYPE from TBL_BASE_USERS ");
            sbSql.Append(" where USERS_UID=@USERS_UID and USERS_PWD=@USERS_PWD");
            MySqlParameter[] paras = { 
                                       new MySqlParameter("@USERS_UID",MySqlDbType.VarChar,50),
                                       new MySqlParameter("@USERS_PWD",MySqlDbType.VarChar,50)
                                       };
            paras[0].Value = loginID;
            paras[1].Value = loginPwd;
            FangYar.Model.BaseUserModel user = null;

            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sbSql.ToString(), paras);
            if (dt.Rows.Count > 0)
            {
                user = new Model.BaseUserModel();
                //DataRowToModel(user, dt.Rows[0]);
                //只取部分属性写入cookie（防止某些列特别长，例如description，导致cookie过长）：
                if (!DBNull.Value.Equals(dt.Rows[0]["ID"]))
                    user.ID = dt.Rows[0]["ID"].ToString();
                if (!DBNull.Value.Equals(dt.Rows[0]["USERS_UID"]))
                    user.USERS_UID = dt.Rows[0]["USERS_UID"].ToString();
                if (!DBNull.Value.Equals(dt.Rows[0]["USERS_NAME"]))
                    user.USERS_NAME = dt.Rows[0]["USERS_NAME"].ToString();
                if (!DBNull.Value.Equals(dt.Rows[0]["USERS_PWD"]))
                    user.USERS_PWD = dt.Rows[0]["USERS_PWD"].ToString();
                if (!DBNull.Value.Equals(dt.Rows[0]["USERS_AUTH"]))
                    user.USERS_AUTH = dt.Rows[0]["USERS_AUTH"].ToString();
                if (!DBNull.Value.Equals(dt.Rows[0]["USERS_RULE"]))
                    user.USERS_RULE = dt.Rows[0]["USERS_RULE"].ToString();
                return user;
            }
            return user;
        }

        /// <summary>
        /// 根据用户ID判断用户是否可用
        /// </summary>
        public Model.BaseUserModel CheckLoginByUserID(string USERS_UID)
        {
            string sql = " select ID,USERS_UID,USERS_PWD,USERS_NAME,USERS_RULE,USERS_AUTH,USERS_TYPE from TBL_BASE_USERS  where USERS_UID=@USERS_UID";
            FangYar.Model.BaseUserModel user = null;
            MySqlParameter[] paras = { 
                                       new MySqlParameter("@USERS_UID",MySqlDbType.VarChar,50)
                                       };
            paras[0].Value = USERS_UID;
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);
            if (dt.Rows.Count > 0)
            {
                user = new FangYar.Model.BaseUserModel();
                DataRowToModel(user, dt.Rows[0]);
                return user;
            }
            else
            {
                return null;
            }
        }
        #endregion

        
    }
}