public PaginationQueryResult<User> GetLightUser(PaginationQueryCondition condition)
{
PaginationQueryResult<User> result = new PaginationQueryResult<User>();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT id, username, real_name, sex, education, mobile, email, join_date, contract_date, commission, company_id FROM (SELECT id, username, real_name, sex, education, mobile, email, join_date, contract_date, commission, company_id, row_number() over (ORDER BY Id DESC) AS RN FROM users WHERE is_delete = 0) AS Result WHERE RN BETWEEN ");
sb.Append((condition.CurrentPage - 1) * condition.PageSize + 1);
sb.Append(" AND ");
sb.Append(condition.CurrentPage * condition.PageSize);
sb.Append(";SELECT COUNT(*) FROM users where is_delete = 0");
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sb.ToString(), null))
{
while (dr.Read())
{
User user = new User();
user.Id = dr.GetInt32(0);
user.Username = dr.GetString(1);
user.RealName = dr.GetString(2);
user.Sex = dr.GetBoolean(3);
user.Education = dr.GetString(4);
user.Mobile = dr.GetString(5);
user.Email = dr.GetString(6);
user.JoinDate = dr.GetDateTime(7);
user.ContractDate = dr.GetDateTime(8);
user.Commission = dr.GetDecimal(9);
user.CompanyId = dr.GetInt32(10);
result.Results.Add(user);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public void CreateUser(User user)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputVarcharParameter("@username", 50, user.Username),
SqlUtilities.GenerateInputVarcharParameter("@password", 50, user.Password),
SqlUtilities.GenerateInputNVarcharParameter("@real_name", 50, user.RealName),
SqlUtilities.GenerateInputNVarcharParameter("@id_card", 50, user.IdCard),
SqlUtilities.GenerateInputNVarcharParameter("@phone", 50, user.Phone),
SqlUtilities.GenerateInputNVarcharParameter("@mobile", 50, user.Mobile),
SqlUtilities.GenerateInputNVarcharParameter("@email", 50, user.Email),
SqlUtilities.GenerateInputNVarcharParameter("@address", 200, user.Address),
SqlUtilities.GenerateInputNVarcharParameter("@nation", 50, user.Nation),
SqlUtilities.GenerateInputIntParameter("@company_id", user.CompanyId),
SqlUtilities.GenerateInputParameter("@sex", SqlDbType.Bit, user.Sex),
SqlUtilities.GenerateInputParameter("@birthday", SqlDbType.SmallDateTime, user.Birthday),
SqlUtilities.GenerateInputIntParameter("@department_id", user.DepartmentId),
SqlUtilities.GenerateInputParameter("@position_id", SqlDbType.TinyInt, user.PositionId),
SqlUtilities.GenerateInputNVarcharParameter("@marital_status", 50, user.MaritalStatus),
SqlUtilities.GenerateInputParameter("@join_date", SqlDbType.SmallDateTime, user.JoinDate),
SqlUtilities.GenerateInputParameter("@contract_date", SqlDbType.SmallDateTime, user.ContractDate),
SqlUtilities.GenerateInputNVarcharParameter("@education", 50, user.Education),
SqlUtilities.GenerateInputParameter("@commission", SqlDbType.Decimal, user.Commission),
SqlUtilities.GenerateInputParameter("@create_date", SqlDbType.SmallDateTime, user.CreateDate)
};
string sql = "INSERT INTO users (username, password, real_name, id_card, phone, mobile, email, address, nation, company_id, sex, birthday, department_id, marital_status, join_date, contract_date, education, commission, create_date, position_id) VALUES (@username, @password, @real_name, @id_card, @phone, @mobile, @email, @address, @nation, @company_id, @sex, @birthday, @department_id, @marital_status, @join_date, @contract_date, @education, @commission, @create_date, @position_id)";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public static OperatorStaus CreateUser(User user)
{
User ur = dal.GetUserByUsername(user.Username);
if (ur != null) return OperatorStaus.OPERATOR_USERNAME_EXISTED;
user.Password = EncryptionHelper.EncryptString(user.Password);
dal.CreateUser(user);
return OperatorStaus.SUCCESS;
}
public static void UpdateUserPassword(User user, string password)
{
int id = user.Id;
string newPassword = EncryptionHelper.EncryptString(password);
dal.UpdateUserPassword(id, newPassword);
}
public static void UpdateUser(User user)
{
dal.UpdateUser(user);
}
public static OperatorStaus UserLogin(User user)
{
User ur = dal.GetUserByUsername(user.Username);
if (ur == null) return OperatorStaus.OPERATOR_USERNAME_INCORROECT;
if (ur.Password != EncryptionHelper.EncryptString(user.Password)) return OperatorStaus.OPERATOR_PASSWORD_INCORROECT;
user.Id = ur.Id;
user.CreateDate = ur.CreateDate;
user.CompanyId = ur.CompanyId;
user.RealName = ur.RealName;
user.ModuleAuthorizations = ur.ModuleAuthorizations;
return OperatorStaus.SUCCESS;
}
public List<User> GetLightUser()
{
List<User> result = new List<User>();
string sql = "SELECT id, username, real_name, sex, education, mobile, email, join_date, contract_date, commission, company_id FROM users WHERE is_delete = 0 ORDER BY id DESC";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
User user = new User();
user.Id = dr.GetInt32(0);
user.Username = dr.GetString(1);
user.RealName = dr.GetString(2);
user.Sex = dr.GetBoolean(3);
user.Education = dr.GetString(4);
user.Mobile = dr.GetString(5);
user.Email = dr.GetString(6);
user.JoinDate = dr.GetDateTime(7);
user.ContractDate = dr.GetDateTime(8);
user.Commission = dr.GetDecimal(9);
user.CompanyId = dr.GetInt32(10);
result.Add(user);
}
}
return result;
}
private User GetUser(string condition, SqlParameter param)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT id, username, password, real_name, id_card, phone, mobile, email, address, nation, company_id, sex, birthday, department_id, marital_status, join_date, contract_date, education, commission, create_date, position_id FROM users WHERE ");
sb.Append(condition);
sb.Append(";SELECT OA.module_id, OA.accessible, OA.writable FROM operator_Authorizations AS OA, users AS O WHERE O.is_delete = 0 AND O.id = OA.operator_id and O.");
sb.Append(condition);
User user = null;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sb.ToString(), new SqlParameter[] { param }))
{
while (dr.Read())
{
user = new User();
user.Id = dr.GetInt32(0);
user.Username = dr.GetString(1);
user.Password = dr.GetString(2);
user.RealName = dr.GetString(3);
user.IdCard = dr.GetString(4);
user.Phone = dr.GetString(5);
user.Mobile = dr.GetString(6);
user.Email = dr.GetString(7);
user.Address = dr.GetString(8);
user.Nation = dr.GetString(9);
user.CompanyId = dr.GetInt32(10);
user.Sex = dr.GetBoolean(11);
user.Birthday = dr.GetDateTime(12);
user.DepartmentId = dr.GetInt32(13);
user.MaritalStatus = dr.GetString(14);
user.JoinDate = dr.GetDateTime(15);
user.ContractDate = dr.GetDateTime(16);
user.Education = dr.GetString(17);
user.Commission = dr.GetDecimal(18);
user.CreateDate = dr.GetDateTime(19);
user.PositionId = dr.GetByte(20);
}
if (user != null)
{
dr.NextResult();
user.ModuleAuthorizations = new List<ModuleAuthorization>();
while (dr.Read())
{
ModuleAuthorization ma = new ModuleAuthorization();
ma.ModuleId = dr.GetInt32(0);
ma.Accessible = dr.GetBoolean(1);
ma.Writable = dr.GetBoolean(2);
user.ModuleAuthorizations.Add(ma);
}
}
}
return user;
}
public void UpdateUser(User user)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", user.Id),
SqlUtilities.GenerateInputNVarcharParameter("@real_name", 50, user.RealName),
SqlUtilities.GenerateInputIntParameter("@company_id", user.CompanyId),
SqlUtilities.GenerateInputNVarcharParameter("@id_card", 50, user.IdCard),
SqlUtilities.GenerateInputNVarcharParameter("@phone", 50, user.Phone),
SqlUtilities.GenerateInputNVarcharParameter("@mobile", 50, user.Mobile),
SqlUtilities.GenerateInputNVarcharParameter("@email", 50, user.Email),
SqlUtilities.GenerateInputNVarcharParameter("@address", 200, user.Address),
SqlUtilities.GenerateInputNVarcharParameter("@nation", 50, user.Nation),
SqlUtilities.GenerateInputParameter("@sex", SqlDbType.Bit, user.Sex),
SqlUtilities.GenerateInputParameter("@birthday", SqlDbType.SmallDateTime, user.Birthday),
SqlUtilities.GenerateInputIntParameter("@department_id", user.DepartmentId),
SqlUtilities.GenerateInputParameter("@position_id", SqlDbType.TinyInt, user.PositionId),
SqlUtilities.GenerateInputNVarcharParameter("@marital_status", 50, user.MaritalStatus),
SqlUtilities.GenerateInputParameter("@join_date", SqlDbType.SmallDateTime, user.JoinDate),
SqlUtilities.GenerateInputParameter("@contract_date", SqlDbType.SmallDateTime, user.ContractDate),
SqlUtilities.GenerateInputNVarcharParameter("@education", 50, user.Education),
SqlUtilities.GenerateInputParameter("@commission", SqlDbType.Decimal, user.Commission)
};
string sql = "UPDATE USERS SET real_name = @real_name, id_card = @id_card, phone = @phone, mobile = @mobile, email = @email, address = @address, nation = @nation, sex = @sex, birthday = @birthday, department_id = @department_id, position_id = @position_id, marital_status = @marital_status, join_date = @join_date, contract_date = @contract_date, education = @education, commission = @commission, company_id = @company_id WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public PaginationQueryResult<User> GetLightUserByCompanyId(PaginationQueryCondition condition, int compId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
PaginationQueryResult<User> result = new PaginationQueryResult<User>();
string sql = "SELECT TOP " + condition.PageSize + " id, username, real_name, sex, education, mobile, email, join_date, contract_date, commission, company_id FROM users WHERE is_delete = 0 AND company_id = @company_id";
if (condition.CurrentPage > 1)
{
sql += " AND id< SELECT MIN(id) FROM(SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM users Where is_delete = 0 AND company_id = @company_id ORDER BY id DESC) AS D)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM users WHERE is_delete = 0 AND company_id = @company_id ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
User user = new User();
user.Id = dr.GetInt32(0);
user.Username = dr.GetString(1);
user.RealName = dr.GetString(2);
user.Sex = dr.GetBoolean(3);
user.Education = dr.GetString(4);
user.Mobile = dr.GetString(5);
user.Email = dr.GetString(6);
user.JoinDate = dr.GetDateTime(7);
user.ContractDate = dr.GetDateTime(8);
user.Commission = dr.GetDecimal(9);
user.CompanyId = dr.GetInt32(10);
result.Results.Add(user);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}