?using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace DAL
{
    /// <summary>
    /// 數據訪問類Accounts_Users。
    /// </summary>
    public class Accounts_Users :IDAL.IAccounts_Users
    {
        public Accounts_Users()
        { }
        #region  成員方法

        /// <summary>
        /// 是否存在該記錄
        /// </summary>
        public bool Exists(int UserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Accounts_Users where UserId=" + UserId + "");
            return DBUtility.DbHelperSQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一條數據
        /// </summary>
        public int Add(Model.Accounts_Users model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Accounts_Users(");
            strSql.Append("UserName,Password,TrueName,Sex,Phone,Email,EmployeeId,DepartmentId,Activity,UserType,Style");
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append("'" + model.UserName + "',");
            strSql.Append("'" + model.Password + "',");
            strSql.Append("'" + model.TrueName + "',");
            strSql.Append("'" + model.Sex + "',");
            strSql.Append("'" + model.Phone + "',");
            strSql.Append("'" + model.Email + "',");
            strSql.Append("" + model.EmployeeId + ",");
            strSql.Append("'" + model.DepartmentId + "',");
            strSql.Append("" + model.Activity + ",");
            strSql.Append("'" + model.UserType + "',");
            strSql.Append("" + model.Style + "");
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            object obj = DBUtility.DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一條數據
        /// </summary>
        public void Update(Model.Accounts_Users model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Accounts_Users set ");
            strSql.Append("UserName='" + model.UserName + "',");
            strSql.Append("Password='" + model.Password + "',");
            strSql.Append("TrueName='" + model.TrueName + "',");
            strSql.Append("Sex='" + model.Sex + "',");
            strSql.Append("Phone='" + model.Phone + "',");
            strSql.Append("Email='" + model.Email + "',");
            strSql.Append("EmployeeId=" + model.EmployeeId + ",");
            strSql.Append("DepartmentId='" + model.DepartmentId + "',");
            strSql.Append("Activity=" + model.Activity + ",");
            strSql.Append("UserType='" + model.UserType + "',");
            strSql.Append("Style=" + model.Style + "");
            strSql.Append(" where UserId=" + model.UserId + "");
            DBUtility.DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 刪除一條數據
        /// </summary>
        public void Delete(int UserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete Accounts_Users ");
            strSql.Append(" where UserId=" + UserId);
            DBUtility.DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 得到一個對象實體
        /// </summary>
        public Model.Accounts_Users GetModel(int UserId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *  ");
            strSql.Append(" UserId,UserName,Password,TrueName,Sex,Phone,Email,EmployeeId,DepartmentId,Activity,UserType,Style ");
            strSql.Append(" from Accounts_Users ");
            strSql.Append(" where UserId=" + UserId);
            Model.Accounts_Users model = new Model.Accounts_Users();
            DataSet ds = DBUtility.DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
                }
                model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                model.TrueName = ds.Tables[0].Rows[0]["TrueName"].ToString();
                model.Sex = ds.Tables[0].Rows[0]["Sex"].ToString();
                model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                if (ds.Tables[0].Rows[0]["EmployeeId"].ToString() != "")
                {
                    model.EmployeeId = int.Parse(ds.Tables[0].Rows[0]["EmployeeId"].ToString());
                }
                model.DepartmentId = ds.Tables[0].Rows[0]["DepartmentId"].ToString();
                if (ds.Tables[0].Rows[0]["Activity"].ToString() != "")
                {
                    model.Activity = int.Parse(ds.Tables[0].Rows[0]["Activity"].ToString());
                }
                model.UserType = ds.Tables[0].Rows[0]["UserType"].ToString();
                if (ds.Tables[0].Rows[0]["Style"].ToString() != "")
                {
                    model.Style = int.Parse(ds.Tables[0].Rows[0]["Style"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 獲得數據記錄數
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            int ret = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) ");
            strSql.Append(" FROM Accounts_Users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DBUtility.DbHelperSQL.GetSingle(strSql.ToString());
            if(object.Equals(obj,null))
            {
                ret= 0;
            }
            else
            {
                ret=int.Parse(obj.ToString());
            }
            return ret;
        }
        /// <summary>
        /// 獲得數據列表
        /// </summary>
        public DataSet GetData(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UserId,UserName,Password,TrueName,Sex,Phone,Email,EmployeeId,DepartmentId,Activity,UserType,Style ");
            strSql.Append(" FROM Accounts_Users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DBUtility.DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 根據分頁獲得數據列表
        /// </summary>
        public DataSet GetData(int PageSize, int PageIndex, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select UserId,UserName,Password,TrueName,Sex,Phone,Email,EmployeeId,DepartmentId,Activity,UserType,Style ");
            strSql.Append(" FROM Accounts_Users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet ds = DBUtility.DbHelperSQL.QueryPager(PageSize,PageIndex,strSql.ToString());            
            return ds;
        }
        /// <summary>
        /// 獲得數據列表
        /// </summary>
        public List<Model.Accounts_Users> GetList(string strWhere)
        {
            List<Model.Accounts_Users> retList = new List<Model.Accounts_Users>();
            StringBuilder strSql=new StringBuilder();
            strSql.Append("select UserId,UserName,Password,TrueName,Sex,Phone,Email,EmployeeId,DepartmentId,Activity,UserType,Style ");
            strSql.Append(" FROM Accounts_Users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet ds = DBUtility.DbHelperSQL.Query(strSql.ToString());
            for (int i = 0, j = ds.Tables[0].Rows.Count; i < j; i++)
            {
                retList.Add(new Model.Accounts_Users(int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString()), ds.Tables[0].Rows[0]["UserName"].ToString(), ds.Tables[0].Rows[0]["Password"].ToString(), ds.Tables[0].Rows[0]["TrueName"].ToString(), ds.Tables[0].Rows[0]["Sex"].ToString(), ds.Tables[0].Rows[0]["Phone"].ToString(), ds.Tables[0].Rows[0]["Email"].ToString(), int.Parse(ds.Tables[0].Rows[0]["EmployeeId"].ToString()), ds.Tables[0].Rows[0]["DepartmentId"].ToString(), int.Parse(ds.Tables[0].Rows[0]["Activity"].ToString()), ds.Tables[0].Rows[0]["UserType"].ToString(), int.Parse(ds.Tables[0].Rows[0]["Style"].ToString())));
            }
            return retList;
        }
        #endregion  成員方法
        
    }
}