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); }