public List <ComplaintEntity> SearchComplaints(ComplaintSearchEntity request, out int recordCount)
{
return(complaintRepository.SearchComplaints(request, out recordCount));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Get value from QueryStrings
txtKeyword.Text = QS("Keyword");
txtUpdatedBy.Text = QS("UpdatedBy");
if (QS("Type") != "" && QS("Type") != "-1")
{
ddlType.SelectedValue = QS("Type");
}
if (QS("Status") != "" && QS("Status") != "-1")
{
ddlStatus.SelectedValue = QS("Status");
}
if (QS("Reason") != "" && QS("Reason") != "-1")
{
ddlReason.SelectedValue = QS("Reason");
}
if (QS("SystemID") != "" && QS("SystemID") != "-1")
{
ddlSystemID.SelectedValue = QS("SystemID");
}
if (QS("AppSrc") != "" && QS("AppSrc") != "-1")
{
ddlAppSrc.SelectedValue = QS("AppSrc");
}
// Bind dropdownlists
List <ListItem> lst = ComplaintTypeHelper.AllComplaintType.Select(x => new ListItem()
{
Text = x.ToText(), Value = ((int)x).ToString()
}).ToList();
lst.BindDropdown <ListItem>(ddlType, "Text", "Value", "ALL", "-1");
lst = ComplaintReasonHelper.AllReason.Select(x => new ListItem()
{
Text = x.ToText(), Value = ((int)x).ToString()
}).ToList();
lst.BindDropdown <ListItem>(ddlReason, "Text", "Value", "ALL", "-1");
ISystemRepository systemRepository = ObjectFactory.GetInstance <ISystemRepository>();
List <SystemEntity> sysList = systemRepository.GetAllSystems();
sysList.BindDropdown(ddlSystemID, "SystemName", "SystemID", "ALL", "-1");
lst = ComplaintAppSrcHelper.AllAppSrc.Select(x => new ListItem()
{
Text = x.ToText(), Value = ((int)x).ToString()
}).ToList();
lst.BindDropdown <ListItem>(ddlAppSrc, "Text", "Value", "ALL", "-1");
lst = ComplaintStatusHelper.AllStatus.Select(x => new ListItem()
{
Text = x.ToText(), Value = ((int)x).ToString()
}).ToList();
lst.BindDropdown <ListItem>(ddlStatus, "Text", "Value", "ALL", "-1");
// Search db to display the table
ComplaintSearchEntity request = new ComplaintSearchEntity(true, OrderBy, OrderDirection);
request.Type = int.Parse(ddlType.SelectedValue);
request.Reason = int.Parse(ddlReason.SelectedValue);
request.SystemID = int.Parse(ddlSystemID.SelectedValue);
request.AppSrc = int.Parse(ddlAppSrc.SelectedValue);
request.Status = int.Parse(ddlStatus.SelectedValue);
request.Keyword = txtKeyword.Text.NoHTML();
request.UpdatedByName = txtUpdatedBy.Text.NoHTML();
request.CurrentPage = CurrentPageIndex;
request.PageCount = ComplaintsPage.PageSize;
ComplaintApplication complaintApp = new ComplaintApplication();
int recordCount;
List <ComplaintEntity> comEntityLst = complaintApp.SearchComplaints(request, out recordCount);
rptUsers.DataSource = comEntityLst;
rptUsers.DataBind();
ComplaintsPage.RecordCount = recordCount;
}
}
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);
}
}
