//This get provides a list of Paged Destinations public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "DestinationID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var DestinationQuery = _context.Destinations .Include(s => s.StartLocation) .Include(e => e.EndLocation) // .Include(c => c.Customer) .Select(dest => new { dest.DestinationID, // dest.CustomerID, dest.StartLocationID, dest.EndLocationID, // CustomerName = dest.Customer.Name, StartLocationName = dest.StartLocation.Province.Country.CountryName + " : " + dest.StartLocation.Province.ProvinceName + " : " + dest.StartLocation.LocationName, EndLocationName = dest.EndLocation.Province.Country.CountryName + " : " + dest.EndLocation.Province.ProvinceName + " : " + dest.EndLocation.LocationName, dest.Distance } ); totalResultsCount = DestinationQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { DestinationQuery = DestinationQuery .Where( d => d.StartLocationName.ToLower().Contains(Model.search.value.ToLower()) || d.EndLocationName.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.Distance.ToString().ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = DestinationQuery.Count(); } var Result = await DestinationQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "Code", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var StockCountQuery = _context.StockCountItems .Include(s => s.SKU) .Include(u => u.UOM) .Include(q => q.StockQuality) .Select(s => new { StockCountItemID = s.StockCountItemID, code = s.SKU.Code, sku = s.SKU.Description, qty = s.Qty, uom = s.UOM.Description, quality = s.StockQuality.Description, s.Comments, principleName = s.SKU.Principle.PrincipalName, } ); totalResultsCount = StockCountQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { StockCountQuery = StockCountQuery .Where( c => c.sku.ToLower().Contains(Model.search.value.ToLower()) || c.principleName.ToLower().Contains(Model.search.value.ToLower()) || c.code.ToLower().Contains(Model.search.value.ToLower()) || c.quality.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = StockCountQuery.Count(); } var Result = await StockCountQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//This get provides a list of Staff Allocation Items public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "ActCostSiloAllocationID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var AlloSiloQuery = _context.ActCostSiloAllocations .Include(p => p.ActCostPeriod) .Include(s => s.Silo) .Include(e => e.Employee) .Select(s => new { s.ActCostSiloAllocationID, Silo = s.Silo.SiloName, s.ActCostPeriod.Period, s.Employee.EmployeeNo, s.Employee.LastName, s.AllocPercentage, s.Description, s.Comments } ); totalResultsCount = AlloSiloQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { AlloSiloQuery = AlloSiloQuery .Where( d => d.Period.ToLower().Contains(Model.search.value.ToLower()) || d.Description.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.LastName.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.Comments.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.EmployeeNo.ToString().ToLower().Contains(Model.search.value.ToLower())); filteredResultsCount = AlloSiloQuery.Count(); } var Result = await AlloSiloQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//This get provides a list of Paged Warehouses public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "ActCostWarehouseID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var WarehouseQuery = _context.ActCostWarehouse .Include(b => b.Branch) .Include(c => c.ActCostChannel) .Include(sc => sc.ActCostWarehouseType) .Select(w => new { w.ActCostWarehouseID, w.WarehouseName, Branch = w.Branch.BranchName, Channel = w.ActCostChannel.ChannelName, WarehouseType = w.ActCostWarehouseType.Description, w.WarehouseCode } ); totalResultsCount = WarehouseQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { WarehouseQuery = WarehouseQuery .Where( w => w.WarehouseName.ToLower().Contains(Model.search.value.ToLower()) || w.WarehouseCode.ToString().ToLower().Contains(Model.search.value.ToLower()) || w.Branch.ToString().ToLower().Contains(Model.search.value.ToLower()) || w.Channel.ToString().ToLower().Contains(Model.search.value.ToLower()) || w.WarehouseType.ToString().ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = WarehouseQuery.Count(); } var Result = await WarehouseQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "CustomerAccountLocationLink", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var AccountLocationQuery = _context.CustomerAccountLocationLink .Include(u => u.Location) .Include(u => u.Location.Province) .Include(f => f.CustomerAccount) .Select(c => new { Id = c.CustomerAccountLocationLinkID, AccountNo = c.CustomerAccount.AccountNumber, CustomerName = c.CustomerAccount.AccountDescription, Address = c.CustomerAccount.Address, c.DefaultLocation, c.Location.Province.ProvinceName, c.Location.LocationName, c.Location.LocationID, c.CustomerAccountID } ); totalResultsCount = AccountLocationQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { AccountLocationQuery = AccountLocationQuery .Where( c => c.AccountNo.ToLower().Contains(Model.search.value.ToLower()) || c.CustomerName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = AccountLocationQuery.Count(); } var Result = await AccountLocationQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//This get provides a list of Other Income Items public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "ActCostAccountAmtPrincipleID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var PrincipleAmtQuery = _context.ActCostAccountAmtPrinciple .Include(p => p.ActCostPeriod) .Include(pr => pr.Principle) .Include(a => a.ActCostAccount) .Select(Pamt => new { Pamt.ActCostAccountAmtPrincipleID , Principle = Pamt.Principle.PastelName , Period = Pamt.ActCostPeriod.Period , Account = Pamt.ActCostAccountID , AccountName = Pamt.ActCostAccount.Description , Amount = Pamt.Amount , Comments = Pamt.Comments } ); totalResultsCount = PrincipleAmtQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { PrincipleAmtQuery = PrincipleAmtQuery .Where( d => d.Principle.ToLower().Contains(Model.search.value.ToLower()) || d.Period.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.Account.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.Comments.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.Amount.ToString().ToLower().Contains(Model.search.value.ToLower())); filteredResultsCount = PrincipleAmtQuery.Count(); } var Result = await PrincipleAmtQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "LocationName", out bool SortDir, out string SortBy); if (SortBy.ToLower().Contains("gps")) { SortBy = ExtensionMethods.UppercaseSpecifiedNumbers(SortBy, 3); } //First create the View of the new model you wish to display to the user var LocationQuery = _context.Locations .Include(p => p.Province) .Include(c => c.Province.Country) .Select(loc => new { loc.LocationID, loc.Province.Country.CountryName, loc.Province.ProvinceName, loc.LocationName, loc.GPSCoordinates } ); totalResultsCount = LocationQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { LocationQuery = LocationQuery .Where( l => l.LocationName.ToLower().Contains(Model.search.value.ToLower()) || l.GPSCoordinates.ToString().ToLower().Contains(Model.search.value.ToLower()) || l.CountryName.ToLower().Contains(Model.search.value.ToLower()) || l.ProvinceName.ToLower().Contains(Model.search.value.ToLower())); filteredResultsCount = LocationQuery.Count(); } var Result = await LocationQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "ModelID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var ModelQuery = _context.Models. Include(f => f.FuelType). Include(d => d.DriveType). Include(m => m.Make). Include(v => v.VehicleType) .Select(m => new { Id = m.ModelID, m.VehicleTypeID, m.ModelName, FuelType = m.FuelType.Description, DriveType = m.DriveType.Description, Make = m.Make.MakeName, Tonnage = m.VehicleType.Description, Wheels = m.NoOfWheels } ); totalResultsCount = ModelQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { ModelQuery = ModelQuery .Where( c => c.ModelName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = ModelQuery.Count(); } var Result = await ModelQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "code", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var SKUQuery = _context.SkuUomLinks .Include(u => u.SKU) .Include(t => t.ToUOM) .Include(f => f.FromUOM) .Select(s => new { Id = s.SkuUomLinkID, s.SKU.Code, SkuName = s.SKU.Description, conversionDescription = s.Description, FromUom = s.FromUOM.Description, ToUom = s.ToUOM.Description, s.ConversionFactor, } ); totalResultsCount = SKUQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { SKUQuery = SKUQuery .Where( c => c.Code.ToLower().Contains(Model.search.value.ToLower()) || c.conversionDescription.ToLower().Contains(Model.search.value.ToLower()) || c.SkuName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = SKUQuery.Count(); } var Result = await SKUQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostEmployeePaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "EmployeeID", out bool SortDir, out string SortBy); var EmployeeQuery = _context.Employees .Include(m => m.Department) .Include(m => m.JobDescription) .Include(b => b.Branch) .Include(c => c.CostCentre) .Select(e => new { e.EmployeeID, e.EmployeeNo, JobDescription = e.JobDescription.Description, e.FirstName, e.LastName, e.Branch.BranchName, } ); totalResultsCount = EmployeeQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { EmployeeQuery = EmployeeQuery .Where( v => v.FirstName.ToLower().Contains(Model.search.value.ToLower()) || v.LastName.ToLower().Contains(Model.search.value.ToLower()) || v.EmployeeNo.ToLower().Contains(Model.search.value.ToLower()) || v.BranchName.ToLower().Contains(Model.search.value.ToLower()) || v.JobDescription.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = EmployeeQuery.Count(); } var Result = await EmployeeQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "Code", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var StockCountQuery = _context.VwStockCountSummary .Select(s => new { NoOfCounts = s.NoOfCounts, Code = s.Code, Description = s.SKUName, Qty = s.Qty, Quality = s.Quality, UOM = s.UOM, PrincipleName = s.PrincipalName, } ); totalResultsCount = StockCountQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { StockCountQuery = StockCountQuery .Where( c => c.Code.ToLower().Contains(Model.search.value.ToLower()) || c.PrincipleName.ToLower().Contains(Model.search.value.ToLower()) || c.Quality.ToLower().Contains(Model.search.value.ToLower()) || c.UOM.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = StockCountQuery.Count(); } var Result = await StockCountQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//This get provides a list of Paged Drivers public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "DriverID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var DriverQuery = _context.Drivers .Where(d => d.SubContractorID != 2) .Select(driv => new { SubContractor = driv.SubContractor.Name, driv.DriverID, driv.FirstName, driv.Surname, driv.CellNumber, driv.IDNumber, driv.PDPExpiryDate, } ); totalResultsCount = DriverQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { DriverQuery = DriverQuery .Where( d => d.FirstName.ToLower().Contains(Model.search.value.ToLower()) || d.Surname.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.CellNumber.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.SubContractor.ToString().ToLower().Contains(Model.search.value.ToLower())); filteredResultsCount = DriverQuery.Count(); } var Result = await DriverQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "PrincipleID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var PrincipalQuery = _context.Principle .Include(u => u.Silo) .Include(m => m.MainPrinciple) .Select(s => new { s.PrincipleID, mainPrincipal = s.MainPrinciple.PrincipalName, s.PrincipalName, s.PastelName, gp = s.GPPercentage, s.Silo.SiloName, s.SortOrder } ); totalResultsCount = PrincipalQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { PrincipalQuery = PrincipalQuery .Where( c => c.PrincipalName.ToLower().Contains(Model.search.value.ToLower()) || c.SiloName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = PrincipalQuery.Count(); } var Result = await PrincipalQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "Name", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var ExchangeQuery = _context.ExchangeRates .Include(a => a.ActCostPeriod) .Include(c => c.SecondCurrency) .Include(cf => cf.FirstCurrency) .Select(e => new { e.ExchangeRateID, FirstCurrency = e.FirstCurrency.CurrencyName, SecondCurrency = e.SecondCurrency.CurrencyName, e.Description, e.ActCostPeriod.Period, e.AverageRate, e.ClosingRate, } ); totalResultsCount = ExchangeQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { ExchangeQuery = ExchangeQuery .Where( c => c.FirstCurrency.ToLower().Contains(Model.search.value.ToLower()) || c.SecondCurrency.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = ExchangeQuery.Count(); } var Result = await ExchangeQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//get a list of Available invoices for Loading public async Task <JsonResult> OnPostInvoicePaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; int Test = Model.FilterItemID; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "invoiceNumber", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var InvoiceQuery = _context.Invoices .Select(i => new { i.InvoiceID, i.InvoiceNumber, i.InvoicePrintDate, StatusName = i.Status.Name, i.StatusID } ).Where(i => i.StatusID == 4); //Status 4 is Warehouse Picked and Packed //Only once Picked and Packed can it be put on a load schedule totalResultsCount = InvoiceQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { InvoiceQuery = InvoiceQuery .Where( i => i.InvoiceNumber.ToLower().Contains(Model.search.value.ToLower()) || i.StatusName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = InvoiceQuery.Count(); } var Result = await InvoiceQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//This get provides a list of Paged Employee Sales Rep Links public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "SalesRepEmplo", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var SalesRepEmployeeQuery = _context.SalesRepCodeEmployeeNoLink .Include(e => e.Employee) .Include(s => s.SalesRep) .Select(l => new { LinkNo = l.SalesRepCodeEmployeeNoLinkID, l.Employee.EmployeeNo, EmployeeName = l.Employee.FirstName + l.Employee.LastName, l.SalesRep.SalesRepCode, l.SalesRep.SalesRepName } ); totalResultsCount = SalesRepEmployeeQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { SalesRepEmployeeQuery = SalesRepEmployeeQuery .Where( d => d.EmployeeName.ToLower().Contains(Model.search.value.ToLower()) || d.EmployeeNo.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.SalesRepName.ToString().ToLower().Contains(Model.search.value.ToLower()) || d.SalesRepCode.ToString().ToLower().Contains(Model.search.value.ToLower())); filteredResultsCount = SalesRepEmployeeQuery.Count(); } var Result = await SalesRepEmployeeQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//get the list of invoice Statuses which have been selected for loading public async Task <JsonResult> OnPostInvoiceLinePaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "code", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var InvoiceLineQuery = _context.InvoiceLine .Include(s => s.SKU) .Select(i => new { i.InvoiceID, i.InvoiceLineID, i.SKU.Code, i.SKU.Description, i.Qty, i.Amt, i.Weight, i.CubicMetres } ).Where(i => i.InvoiceID == Model.InvoiceID); totalResultsCount = InvoiceLineQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { InvoiceLineQuery = InvoiceLineQuery .Where( i => i.Description.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = InvoiceLineQuery.Count(); } var Result = await InvoiceLineQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//get the list of invoice Statuses which have been selected for loading public async Task <JsonResult> OnPostInvoiceStatusPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "CreatedUtc", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var InvoiceStatusQuery = _context.InvoiceStatus .Include(l => l.Load) .Select(i => new { i.InvoiceID, StatusName = i.Status.Name, StatusDate = i.CreatedUtc, User = i.User.UserName, LoadDate = i.Load.LoadDate.ToString(), i.Load.LoadName, i.LoadID } ).Where(i => i.InvoiceID == Model.InvoiceID); totalResultsCount = InvoiceStatusQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { InvoiceStatusQuery = InvoiceStatusQuery .Where( i => i.User.ToLower().Contains(Model.search.value.ToLower()) || i.StatusName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = InvoiceStatusQuery.Count(); } var Result = await InvoiceStatusQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//This get provides a list of Staff Allocation Items public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "ActCostInsuranceTypeID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var InsQuery = _context.ActCostInsuranceType .Include(p => p.ActCostPeriod) .Include(a => a.ActCostDriver) .Select(i => new { i.ActCostInsuranceTypeID, Insurance = i.Description, CostDriver = i.ActCostDriver.Description, i.ActCostPeriod.Period, i.Amount } ); totalResultsCount = InsQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { InsQuery = InsQuery .Where( d => d.Period.ToLower().Contains(Model.search.value.ToLower()) || d.Insurance.ToLower().Contains(Model.search.value.ToLower()) || d.CostDriver.ToString().ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = InsQuery.Count(); } var Result = await InsQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; int Test = Model.FilterItemID; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "roleName", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var RoleStatusQuery = _context.VwInvoiceStatusRoles .Select(i => new { i.RoleName, i.StatusName, i.SortOrder, } ); //Filter based on Users Input totalResultsCount = RoleStatusQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { RoleStatusQuery = RoleStatusQuery .Where( i => i.RoleName.ToLower().Contains(Model.search.value.ToLower()) || i.StatusName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = RoleStatusQuery.Count(); } var Result = await RoleStatusQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; int Test = Model.FilterItemID; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "YearMonth", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var SLAQuery = _context.VwDeliveryStatusSummaryPerMonth_2 .Select(i => new { i.OutsideSlaDeliveries, i.PercentageInside, i.PercentageOutside, i.TotalDeliveries, i.YearMonth } ); totalResultsCount = SLAQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { SLAQuery = SLAQuery .Where( i => i.YearMonth.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = SLAQuery.Count(); } var Result = await SLAQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//get a list of vehicles public async Task <JsonResult> OnPostVehiclePaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "VehicleID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var VehicleQuery = _context.Vehicles .Where(v => v.VehiclePurposeID == 2) .Select(v => new { v.VehicleID, v.RegistrationNumber, vehicleType = v.VechileType.Description, SubContractor = v.SubContractor.Name } ); totalResultsCount = VehicleQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { VehicleQuery = VehicleQuery .Where( v => v.RegistrationNumber.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = VehicleQuery.Count(); } var Result = await VehicleQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "Name", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var StatusQuery = _context.Status .Select(s => new { s.StatusID, StatusName = s.Name, s.SortOrder, s.HrsInStatus } ); totalResultsCount = StatusQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { StatusQuery = StatusQuery .Where( c => c.StatusName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = StatusQuery.Count(); } var Result = await StatusQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//This get provides a list of Paged Brands public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "DriverID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var BrandQuery = _context.Brand .Include(p => p.Principle) .Select(b => new { b.BrandID, BrandName = b.Description, PrincipalName = b.Principle.PrincipalName, } ); totalResultsCount = BrandQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { BrandQuery = BrandQuery .Where( d => d.BrandName.ToLower().Contains(Model.search.value.ToLower()) || d.PrincipalName.ToString().ToLower().Contains(Model.search.value.ToLower())); filteredResultsCount = BrandQuery.Count(); } var Result = await BrandQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "Description", out bool SortDir, out string SortBy); ApplicationUser Currentuser = _context.Users.FirstOrDefault(u => u.Id == Model.UserID); var RolesQuery = (from user in _context.UserRoles join role in _context.Roles on user.RoleId equals role.Id where user.UserId == Currentuser.Id select new { RoleID = role.Id, RoleName = role.Name }); totalResultsCount = RolesQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { RolesQuery = RolesQuery .Where( v => v.RoleName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = RolesQuery.Count(); } var Result = await RolesQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "ProvinceID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var ProvinceQuery = _context.Provinces.Include(c => c.Country) .Select(prv => new { prv.ProvinceID, prv.Country.CountryName, prv.ProvinceName } ); totalResultsCount = ProvinceQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { ProvinceQuery = ProvinceQuery .Where( p => p.ProvinceName.ToLower().Contains(Model.search.value.ToLower()) || p.CountryName.ToString().ToLower().Contains(Model.search.value.ToLower())); filteredResultsCount = ProvinceQuery.Count(); } var Result = await ProvinceQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "Description", out bool SortDir, out string SortBy); var UserQuery = (from user in _context.Users select new { Id = user.Id, UserName = user.UserName, user.Email }); totalResultsCount = UserQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { UserQuery = UserQuery .Where( v => v.UserName.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = UserQuery.Count(); } var Result = await UserQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
//get a list of invoices which have been selected for loading public async Task <JsonResult> OnPostSalesRepPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "employeeID", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var SalesRepQuery = _context.SalesRepCodeEmployeeNoLink .Include(s => s.SalesRep) .Select(s => new { s.SalesRepID, s.EmployeeID, s.SalesRep.SalesRepCode, s.SalesRep.SalesRepName, } ).Where(i => i.EmployeeID == Model.EmployeeID); totalResultsCount = SalesRepQuery.Count(); filteredResultsCount = totalResultsCount; var Result = await SalesRepQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostFleetPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "VehicleID", out bool SortDir, out string SortBy); var VehicleQuery = _context.Vehicles .Include(m => m.Model) .Include(b => b.Branch) .Include(c => c.CostCentre) .Include(e => e.Employee) .Include(vp => vp.VehiclePurpose) .Include(s => s.VehicleStatus) .Where(v => v.SubContractor.SubContractorID == 2) .Select(v => new { v.VehicleID, purpose = v.VehiclePurpose.Description, Branch = v.Branch.BranchName, CostCentre = v.CostCentre.CostCentreName, Employee = v.Employee.FirstName + " " + v.Employee.LastName, v.RegistrationNumber, Make = v.Model.Make.MakeName, Model = v.Model.ModelName, FuelType = v.Model.FuelType.Description, DriveType = v.Model.DriveType.Description, Tonnage = v.Model.VehicleType.Description, v.FixedAssetsNumber, v.AcquisitionDate, AcquisitionCost = v.AcquistionCost, v.DepreciationMonths, Status = v.VehicleStatus.Description } ); totalResultsCount = VehicleQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { VehicleQuery = VehicleQuery .Where( v => v.RegistrationNumber.ToLower().Contains(Model.search.value.ToLower()) || v.Branch.ToLower().Contains(Model.search.value.ToLower()) || v.CostCentre.ToLower().Contains(Model.search.value.ToLower()) || v.Employee.ToLower().Contains(Model.search.value.ToLower()) || v.FixedAssetsNumber.ToLower().Contains(Model.search.value.ToLower()) || v.FuelType.ToLower().Contains(Model.search.value.ToLower()) || v.Make.ToLower().Contains(Model.search.value.ToLower()) || v.Model.ToLower().Contains(Model.search.value.ToLower()) || v.Tonnage.ToLower().Contains(Model.search.value.ToLower()) || v.DriveType.ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = VehicleQuery.Count(); } var Result = await VehicleQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }
public async Task <JsonResult> OnPostPaging([FromForm] DataTableAjaxPostModel Model) { int filteredResultsCount = 0; int totalResultsCount = 0; DataTableAjaxPostModel.GetOrderByParameters(Model.order, Model.columns, "invoiceDate", out bool SortDir, out string SortBy); //First create the View of the new model you wish to display to the user var InvoiceQuery = _context.VwDeliveryStatusPivot .Select(i => new { i.InvoiceDate, i.ProductType, i.Finance, i.Transport, i.Warehouse, i.Pod, i.Other, i.Total, i.UnDelivered } ); //Filter based on Users Input InvoiceQuery = InvoiceQuery.Where(i => i.InvoiceDate >= Model.StartDate).Where (i => i.InvoiceDate <= Model.EndDate); totalResultsCount = InvoiceQuery.Count(); filteredResultsCount = totalResultsCount; if (!string.IsNullOrEmpty(Model.search.value)) { InvoiceQuery = InvoiceQuery .Where( i => i.ProductType.ToLower().Contains(Model.search.value.ToLower()) || i.InvoiceDate.ToString().ToLower().Contains(Model.search.value.ToLower()) ); filteredResultsCount = InvoiceQuery.Count(); } var Result = await InvoiceQuery .Skip(Model.start) .Take(Model.length) .OrderBy(SortBy, SortDir) .ToListAsync(); var value = new { // this is what datatables wants sending back draw = Model.draw, recordsTotal = totalResultsCount, recordsFiltered = filteredResultsCount, data = Result }; return(new JsonResult(value)); }