public async Task <LogSearchResults> FilterLogs(LogSearchCriteria searchCriteria)
{
throw new NotImplementedException(); // FIXME we need to implement searching in Lucene index
}
public Tuple <IEnumerable <Log>, int> Find(LogSearchCriteria criteria
, int pageIndex
, int pageSize
, List <SortDescriptor> sortings)
{
using (IDbConnection connection = base.OpenConnection())
{
const string countQuery = @"SELECT COUNT(1)
FROM [Log] l
INNER JOIN [Level] lv ON l.LevelId = lv.Id
/**where**/";
const string selectQuery = @" SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( /**orderby**/ ) AS RowNum, l.*, lv.Name as [Level]
FROM [Log] l
INNER JOIN [Level] lv ON l.LevelId = lv.Id
/**where**/
) AS RowConstrainedResult
WHERE RowNum >= (@PageIndex * @PageSize + 1 )
AND RowNum <= (@PageIndex + 1) * @PageSize
ORDER BY RowNum";
SqlBuilder builder = new SqlBuilder();
var count = builder.AddTemplate(countQuery);
var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize });
if (!string.IsNullOrEmpty(criteria.Level))
{
builder.Where("lv.Name= @Level", new { Level = criteria.Level });
}
if (!string.IsNullOrEmpty(criteria.Message))
{
var msg = "%" + criteria.Message + "%";
builder.Where("l.Message Like @Message", new { Message = msg });
}
foreach (var sorting in sortings)
{
if (string.IsNullOrWhiteSpace(sorting.Field))
{
continue;
}
if (sorting.Direction == SortDescriptor.SortingDirection.Ascending)
{
builder.OrderBy(sorting.Field);
}
else if (sorting.Direction == SortDescriptor.SortingDirection.Descending)
{
builder.OrderBy(sorting.Field + " desc");
}
}
var totalCount = connection.Query <int>(count.RawSql, count.Parameters).Single();
var rows = connection.Query <Log>(selector.RawSql, selector.Parameters);
return(new Tuple <IEnumerable <Log>, int>(rows, totalCount));
}
}
public Tuple <IEnumerable <Log>, int> FindWithOffsetFetch(LogSearchCriteria criteria
, int pageIndex
, int pageSize
, List <SortDescriptor> sortings)
{
using (IDbConnection connection = base.OpenConnection())
{
const string selectQuery = @" ;WITH _data AS (
SELECT l.*, lv.Name AS [Level]
FROM [Log] l
INNER JOIN [Level] lv ON l.LevelId = lv.Id
/**where**/
),
_count AS (
SELECT COUNT(1) AS TotalCount FROM _data
)
SELECT * FROM _data CROSS APPLY _count /**orderby**/ OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY";
SqlBuilder builder = new SqlBuilder();
var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize });
if (!string.IsNullOrEmpty(criteria.Level))
{
builder.Where("lv.Name = @Level", new { Level = criteria.Level });
}
if (!string.IsNullOrEmpty(criteria.Message))
{
var msg = "%" + criteria.Message + "%";
builder.Where("l.Message Like @Message", new { Message = msg });
}
foreach (var sorting in sortings)
{
if (string.IsNullOrWhiteSpace(sorting.Field))
{
continue;
}
if (sorting.Direction == SortDescriptor.SortingDirection.Ascending)
{
builder.OrderBy(sorting.Field);
}
else if (sorting.Direction == SortDescriptor.SortingDirection.Descending)
{
builder.OrderBy(sorting.Field + " desc");
}
}
var rows = connection.Query <Log>(selector.RawSql, selector.Parameters).ToList();
if (rows.Count() == 0)
{
return(new Tuple <IEnumerable <Log>, int>(rows, 0));
}
return(new Tuple <IEnumerable <Log>, int>(rows, rows[0].TotalCount));
}
}
private LogSearchCriteria GetCritera()
{
var newsearch = new LogSearchCriteria();
FlowLayoutPanel panel = null;
if (cboTable.SelectedIndex == 0)
{
panel = panel1;
// newsearch.Type = "Log";
}
else if (cboTable.SelectedIndex == 2)
{
panel = panel3;
// newsearch.Type = "Report";
}
if (panel == null)
{
return(null);
}
var dict = panel.Controls.Cast <GroupBox>().ToDictionary(a => a.Text, a => a.Controls[0] is DateTimePicker? ((DateTimePicker)a.Controls[0]).Value.ToString(): a.Controls[0].Text);
int s; decimal d; DateTime dt;
foreach (var p in dict)
{
var k = p.Key.ToLower();
if (k == "vennum")
{
if (int.TryParse(p.Value, out s))
{
newsearch.VenNum = p.Value;
}
}
else if (k == "responsexml")
{
newsearch.ResponseXML = p.Value;
}
else if (k == "requestxml")
{
newsearch.RequestXML = p.Value;
}
else if (k == "venname")
{
newsearch.VenName = p.Value;
}
else if (k == "venid")
{
newsearch.VenID = p.Value;
}
else if (k == "from responsetime" && decimal.TryParse(p.Value, out d))
{
newsearch.fromResponseTime = d;
}
else if (k == "to responsetime" && decimal.TryParse(p.Value, out d))
{
newsearch.toResponseTime = d;
}
else if (k == "responsetime" && decimal.TryParse(p.Value, out d))
{
newsearch.ResponseTime = d;
}
else if (k == "date" && DateTime.TryParse(p.Value, out dt))
{
newsearch.Date = dt.Date;
}
else if (k == "from date" && DateTime.TryParse(p.Value, out dt))
{
newsearch.fromDate = dt;
}
else if (k == "to date" && DateTime.TryParse(p.Value, out dt))
{
newsearch.toDate = dt;
}
else if (k == "responsecode" && int.TryParse(p.Value, out s))
{
newsearch.ResponseCode = s;
}
else if (k == "responsetype")
{
newsearch.ResponseType = p.Value;
}
else if (k == "requesttype")
{
newsearch.RequestType = p.Value;
}
else if (k == "responsedescription")
{
newsearch.ResponseDescription = p.Value;
}
}
return(newsearch);
}