context: namespace Maticsoft.SQLServerDAL.Members
  {
      using Maticsoft.DBUtility;
      using Maticsoft.IDAL.Members;
      using Maticsoft.Model.Members;
      using System;
      using System.Collections.Generic;
      using System.Data;
      using System.Data.SqlClient;
      using System.Runtime.InteropServices;
      using System.Text;
  
      public class Users : IUsers
      {
          public int Add(Maticsoft.Model.Members.Users model)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("insert into Accounts_Users(");
              builder.Append("UserName,Password,TrueName,Sex,Phone,Email,EmployeeID,DepartmentID,Activity,UserType,Style,User_iCreator,User_dateCreate,User_dateValid,User_dateExpire,User_iApprover,User_dateApprove,User_iApproveState,User_cLang)");
              builder.Append(" values (");
              builder.Append("@UserName,@Password,@TrueName,@Sex,@Phone,@Email,@EmployeeID,@DepartmentID,@Activity,@UserType,@Style,@User_iCreator,@User_dateCreate,@User_dateValid,@User_dateExpire,@User_iApprover,@User_dateApprove,@User_iApproveState,@User_cLang)");
              builder.Append(";select @@IDENTITY");
              SqlParameter[] cmdParms = new SqlParameter[] { 
                  new SqlParameter("@UserName", SqlDbType.NVarChar, 50), new SqlParameter("@Password", SqlDbType.Binary, 20), new SqlParameter("@TrueName", SqlDbType.NVarChar, 50), new SqlParameter("@Sex", SqlDbType.Char, 10), new SqlParameter("@Phone", SqlDbType.NVarChar, 20), new SqlParameter("@Email", SqlDbType.NVarChar, 100), new SqlParameter("@EmployeeID", SqlDbType.Int, 4), new SqlParameter("@DepartmentID", SqlDbType.NVarChar, 15), new SqlParameter("@Activity", SqlDbType.Bit, 1), new SqlParameter("@UserType", SqlDbType.Char, 2), new SqlParameter("@Style", SqlDbType.Int, 4), new SqlParameter("@User_iCreator", SqlDbType.Int, 4), new SqlParameter("@User_dateCreate", SqlDbType.DateTime), new SqlParameter("@User_dateValid", SqlDbType.DateTime), new SqlParameter("@User_dateExpire", SqlDbType.DateTime), new SqlParameter("@User_iApprover", SqlDbType.Int, 4), 
                  new SqlParameter("@User_dateApprove", SqlDbType.DateTime), new SqlParameter("@User_iApproveState", SqlDbType.Int, 4), new SqlParameter("@User_cLang", SqlDbType.NVarChar, 10)
               };
              cmdParms[0].Value = model.UserName;
              cmdParms[1].Value = model.Password;
              cmdParms[2].Value = model.TrueName;
              cmdParms[3].Value = model.Sex;
              cmdParms[4].Value = model.Phone;
              cmdParms[5].Value = model.Email;
              cmdParms[6].Value = model.EmployeeID;
              cmdParms[7].Value = model.DepartmentID;
              cmdParms[8].Value = model.Activity;
              cmdParms[9].Value = model.UserType;
              cmdParms[10].Value = model.Style;
              cmdParms[11].Value = model.User_iCreator;
              cmdParms[12].Value = model.User_dateCreate;
              cmdParms[13].Value = model.User_dateValid;
              cmdParms[14].Value = model.User_dateExpire;
              cmdParms[15].Value = model.User_iApprover;
              cmdParms[0x10].Value = model.User_dateApprove;
              cmdParms[0x11].Value = model.User_iApproveState;
              cmdParms[0x12].Value = model.User_cLang;
              object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
              if (single == null)
              {
                  return 0;
              }
              return Convert.ToInt32(single);
          }
  
          public bool Delete(int UserID)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("delete from Accounts_Users ");
              builder.Append(" where UserID=@UserID");
              SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.Int, 4) };
              cmdParms[0].Value = UserID;
              return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
          }
  
          public bool DeleteByDepartmentID(int DepartmentID)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("delete from Accounts_Users ");
              builder.Append(" where DepartmentID=@DepartmentID");
              SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@DepartmentID", SqlDbType.Int, 4) };
              cmdParms[0].Value = DepartmentID;
              return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
          }
  
          public bool DeleteEx(int userId)
          {
              List<CommandInfo> cmdList = new List<CommandInfo>();
              StringBuilder builder = new StringBuilder();
              builder.Append("delete from Accounts_Users ");
              builder.Append(" where UserID=@UserID");
              SqlParameter[] para = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.Int, 4) };
              para[0].Value = userId;
              CommandInfo item = new CommandInfo(builder.ToString(), para);
              cmdList.Add(item);
              StringBuilder builder2 = new StringBuilder();
              builder2.Append("delete from Accounts_UsersExp ");
              builder2.Append(" where UserID=@UserID  ");
              SqlParameter[] parameterArray2 = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.Int, 4) };
              parameterArray2[0].Value = userId;
              CommandInfo info2 = new CommandInfo(builder2.ToString(), parameterArray2);
              cmdList.Add(info2);
              if (DbHelperSQL.ExecuteSqlTran(cmdList) <= 0)
              {
                  return false;
              }
              return true;
          }
  
          public bool DeleteList(string UserIDlist)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("delete from Accounts_Users ");
              builder.Append(" where UserID in (" + UserIDlist + ")  ");
              return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
          }
  
          public bool DeleteListByDepartmentID(string DepartmentIDlist)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("delete from Accounts_Users ");
              builder.Append(" where DepartmentID in (" + DepartmentIDlist + ")  ");
              return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
          }
  
          public bool ExistByPhone(string Phone)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select  top 1 * from Accounts_Users ");
              builder.Append(" where Phone=@Phone ");
              SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Phone", SqlDbType.NVarChar) };
              cmdParms[0].Value = Phone;
              return (DbHelperSQL.Query(builder.ToString(), cmdParms).Tables[0].Rows.Count > 0);
          }
  
          public bool Exists(int UserID)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select count(1) from Accounts_Users");
              builder.Append(" where UserID=@UserID");
              SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.Int, 4) };
              cmdParms[0].Value = UserID;
              return DbHelperSQL.Exists(builder.ToString(), cmdParms);
          }
  
          public bool ExistsByEmail(string Email)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select  top 1 *  from Accounts_Users ");
              builder.Append(" where Email=@Email");
              SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Email", SqlDbType.NVarChar) };
              cmdParms[0].Value = Email;
              return (DbHelperSQL.Query(builder.ToString(), cmdParms).Tables[0].Rows.Count > 0);
          }
  
          public bool ExistsNickName(string nickname)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select count(1) from Accounts_Users");
              builder.Append(" where NickName=@NickName");
              SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@NickName", SqlDbType.NVarChar, 50) };
              cmdParms[0].Value = nickname;
              return DbHelperSQL.Exists(builder.ToString(), cmdParms);
          }
  
          public bool ExistsNickName(int userid, string nickname)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select count(1) from Accounts_Users");
              builder.Append(" where UserID<>@UserID AND NickName=@NickName");
              SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@UserID", SqlDbType.Int, 4), new SqlParameter("@NickName", SqlDbType.NVarChar, 50) };
              cmdParms[0].Value = userid;
              cmdParms[1].Value = nickname;
              return DbHelperSQL.Exists(builder.ToString(), cmdParms);
          }
  
          public int GetDefaultUserId()
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select MIN(UserID) from Accounts_Users   where  Activity=1 and UserType='UU'");
              object single = DbHelperSQL.GetSingle(builder.ToString());
              if (single != null)
              {
                  return Convert.ToInt32(single);
              }
              return 0;
          }
  
          public DataSet GetList(string strWhere)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select * ");
              builder.Append(" FROM Accounts_Users ");
              if (strWhere.Trim() != "")
              {
                  builder.Append(" where " + strWhere);
              }
              return DbHelperSQL.Query(builder.ToString());
          }
  
          public DataSet GetList(string type, string keyWord)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("SELECT * FROM Accounts_Users ");
              new StringBuilder();
              builder.Append(" WHERE Activity=1 ");
              if (!string.IsNullOrWhiteSpace(type))
              {
                  builder.Append(" AND UserType=" + type);
              }
              if (!string.IsNullOrWhiteSpace(keyWord))
              {
                  builder.AppendFormat(" AND UserName LIKE '%{0}%' ", keyWord);
              }
              return DbHelperSQL.Query(builder.ToString());
          }
  
          public DataSet GetList(int Top, string strWhere, string filedOrder)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select ");
              if (Top > 0)
              {
                  builder.Append(" top " + Top.ToString());
              }
              builder.Append(" UserID,UserName,Password,TrueName,Sex,Phone,Email,EmployeeID,DepartmentID,Activity,UserType,Style,User_iCreator,User_dateCreate,User_dateValid,User_dateExpire,User_iApprover,User_dateApprove,User_iApproveState,User_cLang ");
              builder.Append(" FROM Accounts_Users ");
              if (strWhere.Trim() != "")
              {
                  builder.Append(" where " + strWhere);
              }
              builder.Append(" order by " + filedOrder);
              return DbHelperSQL.Query(builder.ToString());
          }
  
          public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("SELECT * FROM ( ");
              builder.Append(" SELECT ROW_NUMBER() OVER (");
              if (!string.IsNullOrWhiteSpace(orderby.Trim()))
              {
                  builder.Append("order by T." + orderby);
              }
              else
              {
                  builder.Append("order by T.UserID desc");
              }
              builder.Append(")AS Row, T.*  from Accounts_Users T ");
              if (!string.IsNullOrWhiteSpace(strWhere.Trim()))
              {
                  builder.Append(" WHERE " + strWhere);
              }
              builder.Append(" ) TT");
              builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
              return DbHelperSQL.Query(builder.ToString());
          }
  
          public DataSet GetListEX(string keyWord = "")
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select * from Accounts_Users inner join Accounts_UsersExp on Accounts_UsersExp.UserID=Accounts_Users.UserID");
              builder.Append(" WHERE UserType='UU'");
              if (!string.IsNullOrWhiteSpace(keyWord))
              {
                  builder.AppendFormat(" AND UserName LIKE '%{0}%' ", keyWord);
              }
              return DbHelperSQL.Query(builder.ToString());
          }
  
          public DataSet GetListEXByType(string type, string keyWord = "")
          {
              StringBuilder builder = new StringBuilder();
              builder.Append("select * from Accounts_Users inner join Accounts_UsersExp on Accounts_UsersExp.UserID=Accounts_Users.UserID");
  
gt:             StringBuilder builder2 = new StringBuilder();