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