public UsersEntity GetUserByUserName(string username)
{
string strSql = @"SELECT u.*,c.CompanyName
FROM [Users] u left join Companys c on u.CompanyID=c.ComID
WHERE UserName =@UserName AND [IsDelete]=0";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
db.AddInParameter(dbCommand, "UserName", DbType.String, username.FilterSqlString());
UsersEntity model = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
try
{
if (dataReader.Read())
{
model = UsersEntity.ReaderBind(dataReader);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
return(model);
}
}
/// <summary>
/// Get an object entity
/// </summary>
public UsersEntity Get(int UserID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select u.*,c.CompanyName as CompanyName from Users u left join Companys c on u.CompanyID=c.ComID ");
strSql.Append(" where UserID=@UserID ");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "UserID", DbType.Int32, UserID);
UsersEntity model = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
model = UsersEntity.ReaderBind(dataReader);
}
}
return(model);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
}
public SearchUserResponse SearchUsers(SearchUsersRequest request)
{
int start = request.CurrentPage * request.PageCount + 1 - request.PageCount;
int end = request.CurrentPage * request.PageCount;
string strSelCount = " SELECT COUNT(1) FROM [Users] u ";
if (request.OrderExpression.ToLower().Equals("status"))
{
request.OrderExpression = " u.Status ";
}
string strOrderby = string.Format(" {0} {1} ", request.OrderExpression, request.OrderDirection);
string strSelAttrs = " SELECT u.*,c.CompanyName FROM [Users] u left join Companys c on u.CompanyID=c.ComID ";
string strSelAttrsOrderBy = string.Format(@" Order BY {0} ", strOrderby);
string strSelPageModel = string.Format(@"SELECT * FROM(
SELECT ROW_NUMBER() OVER(
Order BY {0}) as INDEX_ID,u.*,c.CompanyName FROM [Users] u left join Companys c on u.CompanyID=c.ComID ", strOrderby);
string strWherePageModel = @") NEW_TB WHERE INDEX_ID BETWEEN @Strat AND @End;";
StringBuilder strWhere = new StringBuilder();
strWhere.Append(" WHERE IsDelete=0 ");
switch (request.SearchType)
{
case SearchUsersType.All:
if (request.IsSunnet)
{
strWhere.Append(" AND [UserType] = 'SUNNET'");
}
if (request.IsClient)
{
strWhere.Append(" AND [UserType] = 'CLIENT'");
}
//if (!string.IsNullOrEmpty(request.Status) && request.Status.ToUpper() != "ALL")
//{
// strWhere.Append(" AND u.Status=@Status ");
//}
strWhere.Append(" AND u.Status='ACTIVE'");
break;
case SearchUsersType.List:
strWhere.Append(" AND ( UserName like @Keywords OR FirstName like @Keywords OR LastName like @Keywords )");
if (request.CompanyID != 0)
{
strWhere.AppendFormat(" AND (CompanyID={0})", request.CompanyID);
}
if (!string.IsNullOrEmpty(request.Status) && request.Status.ToUpper() != "All".ToUpper())
{
strWhere.Append(" AND u.Status=@Status ");
}
break;
case SearchUsersType.Company:
strWhere.Append(" AND u.Status='ACTIVE'");
strWhere.Append(" AND u.CompanyID =@CompanyID");
break;
case SearchUsersType.CompanyByProject:
strWhere.Append(" AND u.Status='ACTIVE'");
strWhere.Append(" AND u.CompanyID IN (SELECT CompanyID FROM Projects WHERE ProjectID = @ProjectID )");
break;
case SearchUsersType.Project:
strWhere.Append(" AND u.Status='ACTIVE'");
strWhere.Append(" AND u.UserID IN (SELECT UserID FROM ProjectUsers WHERE ProjectID = @ProjectID)");
break;
case SearchUsersType.Role:
strWhere.Append(" AND u.RoleID =@RoleID ");
strWhere.Append(" AND u.Status='ACTIVE'");
break;
case SearchUsersType.Ticket:
strWhere.Append(" AND u.UserID in ( SELECT [UserID] FROM [TicketUsers] WHERE [TicketID]=@TicketID)");
break;
default: break;
}
StringBuilder strSql = new StringBuilder();
if (request.IsPageModel)
{
strSql.Append(strSelCount);
strSql.Append(strWhere);
strSql.Append(";");
strSql.Append(strSelPageModel);
strSql.Append(strWhere);
strSql.Append(strWherePageModel);
}
else
{
strSql.Append(strSelAttrs);
strSql.Append(strWhere);
strSql.Append(strSelAttrsOrderBy);
strSql.Append(";");
}
SearchUserResponse response = new SearchUserResponse();
List <UsersEntity> list;
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "UserID", DbType.Int32, request.UserID);
db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString()));
db.AddInParameter(dbCommand, "Status", DbType.String, request.Status);
db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, request.CompanyID);
db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID);
db.AddInParameter(dbCommand, "RoleID", DbType.Int32, (int)request.Role);
db.AddInParameter(dbCommand, "TicketID", DbType.Int32, request.TicketID);
db.AddInParameter(dbCommand, "Strat", DbType.Int32, start);
db.AddInParameter(dbCommand, "End", DbType.Int32, end);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
list = new List <UsersEntity>();
if (request.IsPageModel)
{
if (dataReader.Read())
{
response.ResultCount = dataReader.GetInt32(0);
dataReader.NextResult();
}
}
while (dataReader.Read())
{
list.Add(UsersEntity.ReaderBind(dataReader));
}
response.ResultList = list;
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(response);
}