public JsonResult ListTasks([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
{
var data = LiveWorkspace.Instance.GetAllTasksByProfile(GetUser.User1());
var dataOrdenada = data.OrderBy(o => o.DataFim.Ticks);
var paged = dataOrdenada.Skip(requestModel.Start).Take(requestModel.Length);
var sortedColumns = requestModel.Columns.GetSortedColumns();
foreach (var column in sortedColumns)
{
if (requestModel.Draw > 1)
{
if (column.Orderable && column.IsOrdered)
{
if (column.SortDirection == DataTables.Mvc.Column.OrderDirection.Ascendant)
paged = paged.OrderBy(o => o.GetType()
.GetProperty(column.Data)
.GetValue(o, null));
else
paged = paged.OrderByDescending(o => o.GetType()
.GetProperty(column.Data)
.GetValue(o, null));
}
}
}
var resp = new DataTablesResponse(
requestModel.Draw,
paged,
dataOrdenada.Count(),
data.Count);
return Json(resp, JsonRequestBehavior.AllowGet);
}
public JsonResult GetCampaigns(
[ModelBinder(typeof (GetCampaignsDataTablesBinder))] GetCampaignsDataTablesRequest request)
{
IEnumerable<CampaignItemViewModel> campaignItemViewModels;
int campaignsTotal;
int campaignsFilteredTotal;
using (var connection = new SqlConnection(_shellSettings.DataConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetCampaigns";
var currentDateParameter = new SqlParameter("@CurrentDate", SqlDbType.DateTime)
{
Value = DateTime.UtcNow
};
command.Parameters.Add(currentDateParameter);
if (request.FilterCurrencyId.HasValue)
{
var currencyIdParameter = new SqlParameter("@CurrencyId", SqlDbType.Int)
{
Value = request.FilterCurrencyId.Value
};
command.Parameters.Add(currencyIdParameter);
}
if (!string.IsNullOrWhiteSpace(request.Search.Value))
{
var filterParameter = new SqlParameter("@Filter", SqlDbType.NVarChar, 4000)
{
Value = request.Search.Value
};
command.Parameters.Add(filterParameter);
}
var cultureParameter = new SqlParameter("@Culture", SqlDbType.NVarChar, 50)
{
Value = _cultureUsed
};
command.Parameters.Add(cultureParameter);
if (request.Columns.GetSortedColumns().Any())
{
var sortColumn = request.Columns.GetSortedColumns().First();
var sortColumnParameter = new SqlParameter("@SortColumn", SqlDbType.NVarChar, 100)
{
Value = sortColumn.Data
};
command.Parameters.Add(sortColumnParameter);
var sortDirection = sortColumn.SortDirection == Column.OrderDirection.Ascendant
? "ASC"
: "DESC";
var sortDirectionParameter = new SqlParameter("@SortDirection", SqlDbType.NVarChar, 50)
{
Value = sortDirection
};
command.Parameters.Add(sortDirectionParameter);
}
var skipParameter = new SqlParameter("@Skip", SqlDbType.Int)
{
Value = request.Start
};
command.Parameters.Add(skipParameter);
var takeParameter = new SqlParameter("@Take", SqlDbType.Int)
{
Value = request.Length
};
command.Parameters.Add(takeParameter);
IEnumerable<CampaignItem> campaignItems;
using (var reader = command.ExecuteReader())
{
campaignItems = GetCampaignItemsFrom(reader);
}
campaignItemViewModels = ConvertToCampaignItemViewModels(campaignItems);
}
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetCampaignsCount";
var cultureParameter = new SqlParameter("@Culture", SqlDbType.NVarChar, 50)
{
Value = _cultureUsed
};
command.Parameters.Add(cultureParameter);
if (request.FilterCurrencyId.HasValue)
{
var currencyIdParameter = new SqlParameter("@CurrencyId", SqlDbType.Int)
{
Value = request.FilterCurrencyId.Value
};
command.Parameters.Add(currencyIdParameter);
}
campaignsTotal = (int) command.ExecuteScalar();
}
if (!string.IsNullOrWhiteSpace(request.Search.Value))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetCampaignsCount";
var cultureParameter = new SqlParameter("@Culture", SqlDbType.NVarChar, 50)
{
Value = _cultureUsed
};
command.Parameters.Add(cultureParameter);
var filterParameter = new SqlParameter("@Filter", SqlDbType.NVarChar, 4000)
{
Value = request.Search.Value
};
command.Parameters.Add(filterParameter);
if (request.FilterCurrencyId.HasValue)
{
var currencyIdParameter = new SqlParameter("@CurrencyId", SqlDbType.Int)
{
Value = request.FilterCurrencyId.Value
};
command.Parameters.Add(currencyIdParameter);
}
campaignsFilteredTotal = (int) command.ExecuteScalar();
}
}
else
{
campaignsFilteredTotal = campaignsTotal;
}
transaction.Commit();
}
}
var dataTableResponse = new DataTablesResponse(
request.Draw,
campaignItemViewModels,
campaignsFilteredTotal,
campaignsTotal);
return Json(dataTableResponse);
}