protected BXSqlCommand MakeListCommand(BXFormFilter f, BXPagingOptions paging, string sortExp)
{
string pagingQuery = "select top {5} * from (select (row_number() over ({3})) as __RowNumber__, {0} from {1} {2} ) _x_ where __RowNumber__ > {4} order by __RowNumber__";
string nonPagingQuery = "select {0} from {1} {2} {3}";
string select = "*";
string from = "b_Log";
string where = string.Empty;
string orderby = string.Empty;
BXSqlCommand qcmd = new BXSqlCommand();
if (f != null)
{
SqlDbType t;
BXSqlFilterCollection fc = new BXSqlFilterCollection();
foreach (BXFormFilterItem item in f)
{
string fn1 = item.filterName.ToLowerInvariant();
switch (fn1)
{
case "type":
t = SqlDbType.TinyInt;
break;
case "id":
t = SqlDbType.Int;
break;
case "code":
t = SqlDbType.Int;
break;
case "source":
t = SqlDbType.NVarChar;
break;
case "occured":
t = SqlDbType.DateTime;
break;
case "title":
t = SqlDbType.NVarChar;
break;
case "message":
t = SqlDbType.NVarChar;
break;
default:
continue;
}
fc.Add(new BXSqlFilter(item.filterName, item.filterOperator, item.filterValue, t));
}
BXSqlFilterAnd commandTextWhereAnd = new BXSqlFilterAnd(fc);
foreach (SqlParameter p in commandTextWhereAnd.FilterParameter)
qcmd.Parameters.Add(p);
string commandTextWhere = commandTextWhereAnd.FilterString;
if (!String.IsNullOrEmpty(commandTextWhere))
where = string.Format("WHERE {0}", commandTextWhere);
}
if (!String.IsNullOrEmpty(sortExp))
orderby = string.Format("ORDER BY {0}", sortExp);
if (paging != null && paging.startRowIndex >= 0 && paging.maximumRows > 0)
qcmd.CommandText = string.Format(pagingQuery, select, from, where, orderby, paging.startRowIndex, paging.maximumRows);
else
qcmd.CommandText = string.Format(nonPagingQuery, select, from, where, orderby);
return qcmd;
}
protected void Grid_SelectCount(object sender, Bitrix.UI.BXSelectCountEventArgs e)
{
string query = "select {0} from {1} {2} {3}";
string select = "count(*)";
string from = "b_Log";
string where = string.Empty;
string orderby = string.Empty;
BXFormFilter f = Filter.CurrentFilter;
using (BXSqlCommand qcmd = new BXSqlCommand())
{
if (f != null)
{
SqlDbType t;
BXSqlFilterCollection fc = new BXSqlFilterCollection();
foreach (BXFormFilterItem item in f)
{
string fn1 = item.filterName.ToLowerInvariant();
switch (fn1)
{
case "type":
t = SqlDbType.TinyInt;
break;
case "id":
t = SqlDbType.Int;
break;
case "code":
t = SqlDbType.Int;
break;
case "source":
t = SqlDbType.NVarChar;
break;
case "occured":
t = SqlDbType.DateTime;
break;
case "title":
t = SqlDbType.NVarChar;
break;
case "message":
t = SqlDbType.NVarChar;
break;
default:
continue;
}
fc.Add(new BXSqlFilter(item.filterName, item.filterOperator, item.filterValue, t));
}
BXSqlFilterAnd commandTextWhereAnd = new BXSqlFilterAnd(fc);
foreach (SqlParameter p in commandTextWhereAnd.FilterParameter)
qcmd.Parameters.Add(p);
string commandTextWhere = commandTextWhereAnd.FilterString;
if (!String.IsNullOrEmpty(commandTextWhere))
where = string.Format("WHERE {0}", commandTextWhere);
}
qcmd.CommandText = string.Format(query, select, from, where, orderby);
e.Count = (int)qcmd.ExecuteScalar();
}
}