public ComplaintEntity Get(int entityId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT c.*, s.SystemName, c.Comments AS UpdatedByName from Complaints c, Systems s ");
strSql.Append("WHERE c.ComplaintID=@ComplaintID AND c.SystemID=s.SystemID ");
ComplaintEntity comEntity = null;
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "ComplaintID", DbType.Int32, entityId);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
comEntity = ComplaintEntity.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(comEntity);
}
public List <ComplaintEntity> SearchComplaints(ComplaintSearchEntity request, out int recordCount)
{
recordCount = 0;
int startID = request.CurrentPage * request.PageCount + 1 - request.PageCount;
int endID = request.CurrentPage * request.PageCount;
StringBuilder strSql = new StringBuilder();
StringBuilder strSqlBody = new StringBuilder();
StringBuilder strSelAll = new StringBuilder();
StringBuilder strSelCnt = new StringBuilder();
StringBuilder strOrderBy = new StringBuilder();
StringBuilder strFitPage = new StringBuilder();
strSelCnt.Append("SELECT Count(*) FROM ");
strSelAll.Append(String.Format("SELECT * from(SELECT *, Row_Number() Over(Order By {0} {1}) RowNum FROM ", request.OrderExpression, request.OrderDirection));
strSqlBody.Append("(SELECT c.*, s.SystemName, '' AS UpdatedByName ");
strSqlBody.Append("FROM Complaints c, Systems s ");
strSqlBody.Append("WHERE c.SystemID=s.SystemID AND c.UpdatedByID is NULL ");
strSqlBody.Append("UNION ");
strSqlBody.Append("SELECT c.*, s.SystemName, u.FirstName+' '+u.LastName AS UpdatedByName ");
strSqlBody.Append("FROM Complaints c, Systems s, Users u ");
strSqlBody.Append("WHERE c.SystemID=s.SystemID AND c.UpdatedByID=u.UserID) combine ");
strSqlBody.Append("WHERE combine.ComplaintID>0 ");
strFitPage.Append(") bigCombine where RowNum BETWEEN @StartID AND @EndID ");
if (request.Type >= 0)
{
strSqlBody.Append("AND combine.Type=@Type ");
}
if (request.Reason >= 0)
{
strSqlBody.Append("AND combine.Reason=@Reason ");
}
if (request.SystemID >= 0)
{
strSqlBody.Append("AND combine.SystemID=@SystemID ");
}
if (request.AppSrc >= 0)
{
strSqlBody.Append("AND combine.AppSrc=@AppSrc ");
}
if (request.Status >= 0)
{
strSqlBody.Append("AND combine.Status=@Status ");
}
if (request.Keyword.Length > 0)
{
strSqlBody.Append("AND (combine.AdditionalInfo like @Keywords OR combine.SystemName like @Keywords) ");
}
if (request.UpdatedByName.Length > 0)
{
strSqlBody.Append("AND combine.UpdatedByName like @UpdatedByName ");
}
strSql.Append(strSelCnt);
strSql.Append(strSqlBody);
strSql.Append(";");
strSql.Append(strSelAll);
strSql.Append(strSqlBody);
strSql.Append(strFitPage);
// Execute
List <ComplaintEntity> list = new List <ComplaintEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Type", DbType.Int32, request.Type);
db.AddInParameter(dbCommand, "Reason", DbType.Int32, request.Reason);
db.AddInParameter(dbCommand, "SystemID", DbType.Int32, request.SystemID);
db.AddInParameter(dbCommand, "AppSrc", DbType.Int32, request.AppSrc);
db.AddInParameter(dbCommand, "Status", DbType.Int32, request.Status);
db.AddInParameter(dbCommand, "Keywords", DbType.String, request.Keyword);
db.AddInParameter(dbCommand, "UpdatedByName", DbType.String, request.UpdatedByName);
db.AddInParameter(dbCommand, "StartID", DbType.Int32, startID);
db.AddInParameter(dbCommand, "EndID", DbType.Int32, endID);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
recordCount = dataReader.GetInt32(0);
dataReader.NextResult();
}
while (dataReader.Read())
{
list.Add(ComplaintEntity.ReaderBind(dataReader));
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSqlBody.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
return(list);
}
}