public ActionResult List()
{
Dictionary <String, String> options = new Dictionary <string, string>();
options.Add("CountryProgrammeId", countryProg.Id.ToString().ToUpper());
OrderRequestPagePacket packet = orderRequestService.getPagedOrderRequests(1, 10, options);
return(View("ViewPagedOrderRequests"));
}
public ActionResult ViewOrderRequestsWithPaging(DataTablesPageRequest gridParams)
{
int page = gridParams.DisplayStart / (gridParams.DisplayLength == 0 ? 1 : gridParams.DisplayLength);
//countryProg.Id
Dictionary <String, String> options = new Dictionary <string, string>();
options.Add("CountryProgrammeId", countryProg.Id.ToString().ToUpper());
OrderRequestPagePacket packet = orderRequestService.getPagedOrderRequests(page, gridParams.DisplayLength, options);
return(Json(new
{
iTotalRecords = packet.TotalOrders,
iTotalDisplayRecords = packet.TotalOrders,
aaData = packet.Orders,
sEcho = gridParams.Echo
},
JsonRequestBehavior.AllowGet));
}
/// <summary>
/// Use raw SQL for speed and custom pagination
/// </summary>
/// <param name="page"></param>
/// <param name="size"></param>
/// <param name="args"></param>
/// <returns></returns>
public OrderRequestPagePacket getPagedOrderRequests(int page = 1, int size = 25, Dictionary <String, String> args = null)
{
// Raw sql
string qString = @"SELECT * FROM (SELECT oq.Id, RefNumber, TotalAmount AS ORValue, pd.ProjectNumber,
CASE
WHEN oq.IsAuthorized=1 THEN 'AU'
WHEN oq.IsRejected=1 THEN 'RJ'
WHEN oq.IsReviewed=1 THEN 'RV'
WHEN oq.IsApproved=1 THEN 'AP'
WHEN oq.IsSubmitted=1 THEN 'CR'
ELSE 'NEW' END
AS Status,
CASE
WHEN oq.IsAuthorized=1 THEN AuthorizedOn
WHEN oq.IsRejected=1 AND oq.AuthorizedOn IS NOT NULL THEN oq.AuthorizedOn
WHEN oq.IsRejected=1 AND oq.ReviewedOn IS NOT NULL THEN oq.ReviewedOn
WHEN oq.IsRejected=1 AND oq.ApprovedOn IS NOT NULL THEN oq.ApprovedOn
WHEN oq.IsReviewed=1 THEN oq.ReviewedOn
WHEN oq.IsApproved=1 THEN oq.ApprovedOn
WHEN oq.IsSubmitted=1 THEN oq.PreparedOn
ELSE oq.PreparedOn END
AS StatusDate,
pn.FirstName+' '+ pn.OtherNames AS Requestor,
ri2.Name AS FirstItem,
oq.CountryProgrammeId,
ROW_NUMBER() OVER(ORDER BY oq.RefNumber) AS RowNum
FROM orderRequest oq
JOIN projectDonor pd ON oq.ProjectDonorId=pd.Id
JOIN Staff sf ON sf.id= oq.PreparedBy
JOIN Person pn ON pn.Id = sf.PersonId
CROSS APPLY (select top(1) ri.ItemId, ri.Id,ri.OrderRequestId, im.Name from orderrequestitem ri JOIN Item im ON im.Id=ri.ItemId WHERE ri.OrderRequestId=oq.Id) ri2
) orders
WHERE RowNum>({PAGE_NUM}-1)*{PAGE_SIZE} AND RowNum<={PAGE_NUM}*{PAGE_SIZE} {PREDICATE_QUERY}";
String totalQuery = "SELECT COUNT(*) AS TotalCount FROM OrderRequest {PREDICATE_TOTAL}";
int _page = page;
if (_page < 1)
{
_page = 1;
}
int _size = 10;
if (size > 10)
{
_size = size;
}
qString = qString.Replace("{PAGE_SIZE}", _size.ToString());
Dictionary <String, String> options = new Dictionary <string, string>();
if (args != null)
{
options = args;
}
if (!options.ContainsKey("CountryProgrammeId"))
{
//force
}
totalQuery = totalQuery.Replace("{PREDICATE_TOTAL}", "WHERE CountryProgrammeId='" + options["CountryProgrammeId"] + "'");
String queryPredicate = " AND CountryProgrammeId='" + options["CountryProgrammeId"] + "'";
qString = qString.Replace("{PREDICATE_QUERY}", queryPredicate);
OrderRequestPagePacket pack = new OrderRequestPagePacket();
using (var context = new SCMSEntities())
{
pack.TotalOrders = ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery <Int32>(totalQuery).First <Int32>();
if ((pack.TotalOrders - _page * _size) <= _size)
{
_page++;
}
qString = qString.Replace("{PAGE_NUM}", _page.ToString());
pack.Orders = ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery <OrderRequestSummary>(qString).ToList <OrderRequestSummary>();
}
return(pack);
}
