using System;

using System.Data;

using System.Data.SqlClient;

using Fan.Dongping.DataBase;

using Fan.Dongping.Common;



namespace Fan.Dongping.Ali.DataAccess

{

    /// <summary>

    /// user data access layer

    /// </summary>

    public class UserDA

    {

        /// <summary>

        /// ??????

        /// </summary>

        /// <returns></returns>

        public static DataTable GetAllUsers()

        {

            string sql = "select top 200 * from users order by RegTime Desc";

            DataTable users = DA_Base.RunReturnDataset(sql).Tables[0];

            users.Columns.Add("AutoNumber", typeof(Int32));

            int i = 1;

            foreach (DataRow dr in users.Rows)

            {

                dr.BeginEdit();

                dr["AutoNumber"] = i;

                dr.EndEdit();

                i++;

            }

            return users;

        }



        /// <summary>

        /// ????????

        /// </summary>

        /// <returns></returns>

        public static int GetUserCount()

        {

            string sql = "select count(*) from users";

            return (int)DA_Base.ExecuteScalar(sql);

        }



        /// <summary>

        /// ?????????

        /// </summary>

        /// <returns></returns>

        public static int GetADayRegister(DateTime aDay)

        {

            string sql = "select count(*) from Users where year(RegTime)=year(@aDay) and month(RegTime)=month(@aDay) and day(RegTime)=day(@aDay)";

            SqlParameter[] paras = new SqlParameter[1];

            paras[0] = new SqlParameter("@aDay", aDay);

            return (int)DA_Base.ExecuteScalar(sql,paras);

        }



        /// <summary>

        /// ?????????

        /// </summary>

        /// <returns></returns>

        public static DataSet GetActiveUser()

        {

            string sql = "select * from users where username not in (select username from users where status ='Closed')";

            return DA_Base.RunReturnDataset(sql);

        }



        /// <summary>

        /// ??????????????

        /// </summary>

        /// <param name="userName"></param>

        /// <returns></returns>

        public static DataRow GetUserByName(string userName)

        {

            string sql = "select * from users where userName = @userName";



            SqlParameter[] paras = new SqlParameter[1];

            paras[0] = new SqlParameter("@userName", userName);



            DataSet ds = DA_Base.RunReturnDataset(sql, paras);

            if (ds.Tables[0].Rows.Count > 0)

            {

                return ds.Tables[0].Rows[0];

            }

            else

            {

                return null;

            }

        }



        /// <summary>

        /// check a user is exist or not

        /// </summary>

        /// <param name="userName"></param>

        /// <returns></returns>

        public static bool Exist(string userName)

        {

            string sql = "select count(*) as number from users where userName=@userName ";

            SqlParameter[] paras = new SqlParameter[1];

            paras[0] = new SqlParameter("@userName", userName);



            int number = (int)DA_Base.ExecuteScalar(sql, paras);



            if (number > 0)

            {

                return true;

            }

            else

            {

                return false;

            }

        }



        /// <summary>

        /// 

        /// </summary>

        /// <param name="userName"></param>

        /// <param name="trueName"></param>

        /// <param name="gender"></param>

        /// <param name="remark"></param>

        /// <returns></returns>

        public static int UpdateBaseInfo(string userName, string trueName, string gender,  string remark)

        {

            string sql = "update Users set trueName = @trueName ,Gender=@Gender ,remark=@remark where userName =@userName ";



            SqlParameter[] paras = new SqlParameter[4];

            paras[0] = new SqlParameter("@userName", userName);

            paras[1] = new SqlParameter("@trueName", trueName);

            paras[2] = new SqlParameter("@Gender", gender);

            paras[3] = new SqlParameter("@remark", remark);



            return DA_Base.ExecuteNonQuery(sql, paras);

        }



        /// <summary>

        /// 

        /// </summary>

        /// <param name="userName"></param>

        /// <param name="company"></param>

        /// <param name="department"></param>

        /// <param name="duty"></param>

        /// <param name="instantMessager"></param>

        /// <param name="officeTel"></param>

        /// <param name="officeAddress"></param>

        /// <returns></returns>

        public static int UpdateConnectInfo(string userName, string company, string department, string duty, string instantMessager, string officeTel, string officeAddress)

        {

            string sql = "update users set company=@company ,department=@department ,duty=@duty ,instantMessager=@instantMessager,officeTel=@officeTel,officeAddress=@officeAddress  where userName=@userName ";



            SqlParameter[] paras = new SqlParameter[7];

            paras[0] = new SqlParameter("@userName", userName);

            paras[1] = new SqlParameter("@company", company);

            paras[2] = new SqlParameter("@department", department);

            paras[3] = new SqlParameter("@duty", duty);

            paras[4] = new SqlParameter("@instantMessager", instantMessager);

            paras[5] = new SqlParameter("@officeTel", officeTel);

            paras[6] = new SqlParameter("@officeAddress", officeAddress);



            return DA_Base.ExecuteNonQuery(sql, paras);

        }



        /// <summary>

        /// update user status

        /// </summary>

        /// <param name="status"></param>

        /// <param name="userName"/>

        /// <returns></returns>

        public static string UpdateUserStatus(string status, string userName)

        {

            string sql = "update users set status = @status where userName = @userName";

            SqlParameter[] para = new SqlParameter[2];

            para[0] = new SqlParameter("@status", status);

            para[1] = new SqlParameter("@userName", userName);

            return DA_Base.ExecuteNonQuery(sql, para).ToString();

        }





        /// <summary>

        /// update user lastLogin

        /// </summary>

        /// <param name="userName"></param>

        /// <returns></returns>

        public static string UpdateLastLogin(string userName)

        {

            string sql = "update users set LastLogin =GetDate()  where userName = @userName";

            SqlParameter[] para = new SqlParameter[1];

            para[0] = new SqlParameter("@userName", userName);

            return DA_Base.ExecuteNonQuery(sql, para).ToString();

        }





        /// <summary>

        /// change password

        /// </summary>

        /// <param name="userName"></param>

        /// <param name="newPassword"></param>

        /// <returns></returns>

        public static ErrorType ChangePassword(string userName, string newPassword)

        {

            //SqlCommand passwordCommand = new SqlCommand();

            //passwordCommand.Connection = this.sqlconnection;

            string sql = "update users set userPass = @userPass where userName = @userName";

            SqlParameter[] para = new SqlParameter[2];

            para[0] = new SqlParameter("@userPass", newPassword);

            para[1] = new SqlParameter("@userName", userName);

            int i = DA_Base.ExecuteNonQuery(sql, para);

            if (i > 0)

            {

                return ErrorType.None;

            }

            else

            {

                return ErrorType.DB_Update0Record;

            }

        }



        /// <summary>

        /// Get User true Name

        /// </summary>

        /// <param name="userName"></param>

        /// <returns></returns>

        public static string GetTrueName(string userName)

        {

            string sql = "select TrueName from users where userName = @userName";



            SqlParameter[] paras = new SqlParameter[1];

            paras[0] = new SqlParameter("@userName", userName);

            return (string)DA_Base.ExecuteScalar(sql, paras);

        }



        /// <summary>

        /// Insert a user

        /// </summary>

        /// <param name="userName"></param>

        /// <param name="userPass"></param>

        /// <param name="trueName"></param>

        /// <param name="gender"></param>

        /// <param name="duty"></param>

        /// <param name="remark"></param>

        /// <param name="regIP"></param>

        /// <returns></returns>

        public static int Insert(string userName, string userPass, string trueName, string gender, string duty, string remark, string regIP)

        {

            string sPass = StringUtils.SHA1(userPass);



            string sql = "INSERT INTO users (userName,userPass,trueName,Gender,duty,remark,RegIP,regTime ) VALUES (@userName,@userPassword,@trueName,@Gender,@duty,@remark,@RegIP,GetDate()) ";

            SqlParameter[] paras = new SqlParameter[7];

            paras[0] = new SqlParameter("@userName", userName);

            paras[1] = new SqlParameter("@userPassword", sPass);

            paras[2] = new SqlParameter("@trueName", trueName);

            paras[3] = new SqlParameter("@Gender", gender);

            paras[4] = new SqlParameter("@duty", duty);

            paras[5] = new SqlParameter("@remark", remark);

            paras[6] = new SqlParameter("@RegIP", regIP);



            return DA_Base.ExecuteNonQuery(sql, paras);

        }



    }

}