public JsonResult DataHandler(DTParameters param)
{
try
{
var dtsource = new List<Customer>();
using (dataSetEntities dc = new dataSetEntities())
{
dtsource = dc.Customers.ToList();
}
List<String> columnSearch = new List<string>();
foreach (var col in param.Columns)
{
columnSearch.Add(col.Search.Value);
}
List<Customer> data = new ResultSet().GetResult(param.Search.Value, param.SortOrder, param.Start, param.Length, dtsource, columnSearch);
int count = new ResultSet().Count(param.Search.Value, dtsource, columnSearch);
DTResult<Customer> result = new DTResult<Customer>
{
draw = param.Draw,
data = data,
recordsFiltered = count,
recordsTotal = count
};
return Json(result);
}
catch (Exception ex)
{
return Json(new { error = ex.Message });
}
}
public void MachineAjaxList()
{
var db = new FakeDatabase();
var st = new FakeSkyTap();
db.AddSet(TestCourseMachineTempData.CourseMachineTemps);
var controller = new CoursesController(db, st);
controller.ControllerContext = new FakeControllerContext();
DTParameters param = new DTParameters() { Start = 2, Length = 5, Search = new DTSearch(), Order = new DTOrder[1] { new DTOrder() { Column = 1, Dir = DTOrderDir.ASC } }, Course = 1, SessionId = "12345" };
JsonResult result = controller.MachineAjax(param) as JsonResult;
Assert.IsNotNull(result);
Assert.AreEqual(5, ((List<CourseMachineTemp>)((DTResult<CourseMachineTemp>)result.Data).data).Count);
Assert.AreEqual("Test3", ((List<CourseMachineTemp>)((DTResult<CourseMachineTemp>)result.Data).data)[0].VMName);
}
public JsonResult GetDataTable(DTParameters param)
{
try
{
var columnSearch = param.Columns.Select(col => col.Search.Value).ToList();
var data = _electionService.GetElectionsTable(param.Search.Value, param.SortOrder, param.Start,
param.Length, columnSearch);
var count = _electionService.Count(param.Search.Value, columnSearch);
var result = new DTResult<Election>
{
draw = param.Draw,
data = data,
recordsFiltered = count,
recordsTotal = count
};
return Json(result);
}
catch (Exception ex)
{
return Json(new {error = ex.Message});
}
}
public static object GetGroupList_DatatbleView(DTParameters param)
{
Model_Subscriber cSG = new Model_Subscriber();
return(cSG.getSubscriberAll_DataTable(param));
}
internal DtResult <Vendor> GetList(DTParameters param)
{
var result = new DtResult <Vendor>();
var query = (from v in _dbContext.Vendor
where v.Status.Equals("1")
select new Vendor
{
Id = v.Id,
CreatedDate = v.CreatedDate,
Name = v.Name,
Address = v.Address,
EmailId = v.EmailId,
EmailId2 = v.EmailId2,
ContactNo = v.ContactNo,
ContactPerson = v.ContactPerson
});
result.data = new List <Vendor>();
result.recordsTotal = query.Count();
var searchColumn = (from sr in param.Columns where !string.IsNullOrWhiteSpace(sr.Search.Value) select sr).ToList();
if (searchColumn?.Count() > 0)
{
foreach (var item in searchColumn)
{
if (item.Name.ToLower().Equals("name"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Name.Contains(item.Search.Value));
}
}
}
}
if (param.Search != null && !string.IsNullOrEmpty(param.Search.Value))
{
var keyword = param.Search.Value;
query = query.Where(w => w.Name.Contains(keyword) || w.Address.Contains(keyword) || w.EmailId.Contains(keyword) || w.ContactNo.Contains(keyword) || w.ContactPerson.Contains(keyword));
//w.Entity.Value.Contains(keyword) || w.PromoterName.Contains(keyword));
}
if (param.Order != null && param.Order.Length > 0)
{
foreach (var item in param.Order)
{
//if (param.Columns[item.Column].Data.Equals("id"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Id) : query.OrderBy(o => o.Id);
if (param.Columns[item.Column].Data.Equals("name"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Name) : query.OrderBy(o => o.Name);
}
else if (param.Columns[item.Column].Data.Equals("address"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Address) : query.OrderBy(o => o.Address);
}
else if (param.Columns[item.Column].Data.Equals("emailId"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.EmailId) : query.OrderBy(o => o.EmailId);
}
else if (param.Columns[item.Column].Data.Equals("emailId2"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.EmailId2) : query.OrderBy(o => o.EmailId2);
}
else if (param.Columns[item.Column].Data.Equals("contactNo"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.ContactNo) : query.OrderBy(o => o.ContactNo);
}
else if (param.Columns[item.Column].Data.Equals("contactPerson"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.ContactPerson) : query.OrderBy(o => o.ContactPerson);
}
}
}
result.recordsFiltered = query.Count();
if (param.Length > 0)
{
query = query.Skip(param.Start).Take(param.Length);
}
var entries = query.ToList();
foreach (var e in entries)
{
result.data.Add(e);
}
return(result);
}
public async Task <IActionResult> TrialAssignments([FromBody] DTParameters dtParameters, DateTime?assignmentDate)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
// in this example we just default sort on the 1st column
try
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
catch (Exception)
{
orderCriteria = "Id";
orderAscendingDirection = true;
}
//orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
//orderCriteria = "Id";
}
else
{
// if we have an empty search then just order the results by Id ascending
orderCriteria = "Id";
orderAscendingDirection = true;
}
var result = new List <TrialAssignmentItem>();
if (assignmentDate == null)
{
result = await _context.TrialAssignmentItems.ToListAsync();
}
else
{
result = await _context.TrialAssignmentItems.Where(m => m.CreationTime == assignmentDate).ToListAsync();
}
if (!string.IsNullOrEmpty(searchBy))
{
result = result.Where(r =>
r.BlockName != null && r.BlockName.ToUpper().Contains(searchBy.ToUpper()) ||
r.Vessel != null && r.Vessel.ToUpper().Contains(searchBy.ToUpper()) ||
r.CreationTime != null && r.CreationTime.ToString("dd-MM-yyyy").Contains(searchBy.ToString()) ||
r.ImpReg != null && r.ImpReg.ToUpper().Contains(searchBy.ToUpper()) ||
r.MLO != null && r.MLO.ToUpper().Contains(searchBy.ToUpper()) ||
r.ContainerNumber != null && r.ContainerNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.LineNumber != null && r.LineNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.Remarks != null && r.Remarks.ToUpper().Contains(searchBy.ToUpper()) ||
r.VerifyNumber != null && r.VerifyNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.ExitNumber != null && r.ExitNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.CnFCode != null && r.CnFCode.ToUpper().Contains(searchBy.ToUpper()) ||
r.CnFName != null && r.CnFName.ToUpper().Contains(searchBy.ToUpper()) ||
r.Dst != null && r.Dst.ToUpper().Contains(searchBy.ToUpper()) ||
r.AssignmentType != null && r.AssignmentType.ToUpper().Contains(searchBy.ToUpper()) ||
r.Size.ToString().Contains(searchBy.ToUpper()) ||
r.Height.ToString().Contains(searchBy.ToUpper())).ToList();
}
result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Desc).ToList();
//now just get the count of items (without the skip and take) - eg how many could be returned with filtering
var filteredResultsCount = result.Count();
int totalResultsCount = 0;
if (assignmentDate == null)
{
totalResultsCount = await _context.TrialAssignmentItems.CountAsync();
}
else
{
totalResultsCount = await _context.TrialAssignmentItems.Where(m => m.CreationTime == assignmentDate).CountAsync();
}
//var totalResultsCount = await _context.TrialAssignmentItems.CountAsync();
if (dtParameters.Length == -1)
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result.Skip(dtParameters.Start).ToList()
}));
}
else
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result.Skip(dtParameters.Start).Take(dtParameters.Length).ToList()
}));
}
}
public async Task <IActionResult> TodaysAssignmentIndex([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
// in this example we just default sort on the 1st column
try
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
catch (Exception)
{
orderCriteria = "Id";
orderAscendingDirection = false;
}
//orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
// orderCriteria = "Id";
}
else
{
// if we have an empty search then just order the results by Id ascending
orderCriteria = "Id";
orderAscendingDirection = false;
}
var result = await _context.Assignments.Include(a => a.AssignmentSlot).Where(a => a.Date == DateTime.Today.Date).ToListAsync();
var assignments = new List <Assignment>();
foreach (var item in result)
{
var tempAssignment = new Assignment
{
Id = item.Id,
Date = item.Date,
AssignmentSlotAssignmentName = item.AssignmentSlot.AssignmentName,
NumberOfAssignmentItems = item.NumberOfAssignmentItems,
Status = item.Status
};
assignments.Add(tempAssignment);
}
if (!string.IsNullOrEmpty(searchBy))
{
assignments = assignments.Where(r =>
r.Date != null && r.Date.ToString("dd-MM-yyyy").Contains(searchBy.ToString()) ||
r.AssignmentSlotAssignmentName != null && r.AssignmentSlotAssignmentName.ToUpper().Contains(searchBy.ToUpper()) ||
r.NumberOfAssignmentItems.ToString().ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
assignments = orderAscendingDirection ? assignments.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Asc).ToList() : assignments.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Desc).ToList();
// now just get the count of items (without the skip and take) - eg how many could be returned with filtering
var filteredResultsCount = assignments.Count();
var totalResultsCount = await _context.Assignments.Where(a => a.Date == DateTime.Today.Date).CountAsync();
if (dtParameters.Length == -1)
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = assignments.Skip(dtParameters.Start).ToList()
}));
}
else
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = assignments.Skip(dtParameters.Start).Take(dtParameters.Length).ToList()
}));
}
}
public static void DataAll(DTParameters parameters)
{
AppTools.SendResponse(HttpContext.Current.Response, UsersController.GetUserRole_DatatbleView(parameters).ObjectToJSON());
}
internal DtResult <TaskItemViewModel> GetList(DTParameters param)
{
var result = new DtResult <TaskItemViewModel>();
var query = (from s in _dbContext.Bills
join v in _dbContext.Vendor on s.Vendor equals v.Id
join sp in _dbContext.Users on s.SalesPerson equals sp.Id
join r in _dbContext.Reason on s.Rejectreason equals r.Id.ToString() into abc
from r in abc.DefaultIfEmpty()
orderby s.Id descending
select new TaskItemViewModel
{
No = s.No,
Id = s.Id,
Date = s.Date,
SalesPersoName = sp.Name,
VendorName = v.Name,
DeliveryType = s.DeliveryType,
DelieveryPlaceId = s.DelieveryPlaceId,
PaymentTerm = s.PaymentTerm,
PaymentValue = s.PaymentValue,
Purchase = s.Purchase,
Accounts = s.Accounts,
Approver = s.Approver,
Recstatus = s.Recstatus,
Rejectreason = r.Name,
});
result.data = new List <TaskItemViewModel>();
result.recordsTotal = query.Count();
var searchColumn = (from sr in param.Columns where !string.IsNullOrWhiteSpace(sr.Search.Value) select sr).ToList();
if (searchColumn?.Count() > 0)
{
foreach (var item in searchColumn)
{
if (item.Name.ToLower().Equals("start"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Date >= DateTime.ParseExact(item.Search.Value.Trim(), "dd-MM-yyyy", CultureInfo.InvariantCulture)).OrderByDescending(o => o.Id);
}
}
if (item.Name.ToLower().Equals("end"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Date <= DateTime.ParseExact(item.Search.Value.Trim(), "dd-MM-yyyy", CultureInfo.InvariantCulture).AddHours(23)).OrderByDescending(o => o.Id);
}
}
}
}
if (param.Search != null && !string.IsNullOrEmpty(param.Search.Value))
{
var keyword = param.Search.Value;
query = query.Where(w => w.SalesPersoName.Contains(keyword)); // ||
//w.Entity.Value.Contains(keyword) || w.PromoterName.Contains(keyword));
}
if (param.Order != null && param.Order.Length > 0)
{
foreach (var item in param.Order)
{
//if (param.Columns[item.Column].Data.Equals("no"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Id) : query.OrderBy(o => o.Id);
//else if (param.Columns[item.Column].Data.Equals("vendorName"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.VendorName) : query.OrderBy(o => o.VendorName);
//else if (param.Columns[item.Column].Data.Equals("deliveryType"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.DeliveryType) : query.OrderBy(o => o.DeliveryType);
//else if (param.Columns[item.Column].Data.Equals("paymentTerm"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.PaymentTerm) : query.OrderBy(o => o.PaymentTerm);
}
}
result.recordsFiltered = query.Count();
if (param.Length > 0)
{
query = query.Skip(param.Start).Take(param.Length);
}
var entries = query.ToList();
foreach (var e in entries)
{
result.data.Add(e);
}
return(result);
}
public ActionResult Ajax(DTParameters param)
{
return this.Json(Generic.Ajax<Classroom>(this.db.Query<Classroom>().ToList(), param));
}
public IActionResult GetAutoManufacturerU(DTParameters dTParameters)
{
PageSet <AutoManufacturerViewModel> data = autoManufacturerService.GetAutoManufacturerDT(dTParameters);
return(Json(data));
}
public virtual async Task <JsonResult> GetWebSessionsWithPagination(DTParameters parameters) => await JsonAsync(_activityService.GetWebSessionsAsync(parameters), _serializerSettings);
public async Task <IActionResult> LoadTable([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
try
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
catch (Exception)
{
orderCriteria = "Id";
orderAscendingDirection = true;
}
}
else
{
orderCriteria = "Id";
orderAscendingDirection = true;
}
//here
var result = await _context.Gates.ToListAsync();
if (!string.IsNullOrEmpty(searchBy))
{
result = result.Where(r =>
r.Name != null && r.Name.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Desc).ToList();
var filteredResultsCount = result.Count();
//here
var totalResultsCount = await _context.Gates.CountAsync();
if (dtParameters.Length == -1)
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result
.Skip(dtParameters.Start)
.ToList()
}));
}
else
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
}
public JsonResult LoadUploadsJS(DTParameters param)
{
try
{
var dtSource = ExtractViewModel.GetExtracts(_unitOfWork);
List<ExtractViewModel> data = new ResultSet_Extracts().GetResult(param.Search.Value, param.SortOrder, param.Start, param.Length, dtSource, null);
int count = new ResultSet_Extracts().Count(param.Search.Value, dtSource, null);
DTResult<ExtractViewModel> result = new DTResult<ExtractViewModel>
{
draw = param.Draw,
data = data,
recordsFiltered = count,
recordsTotal = count
};
return Json(result);
}
catch (Exception ex)
{
ErrorTools.HandleError(ex, ErrorLevel.NonFatal); //just log, no redirect
return Json(new { error = ex.Message });
}
}
public JsonResult LoadUploadItemsJS(DTParameters param)
{
try
{
var dtSource = ExtractViewModel.GetExtractItems(_unitOfWork, param.PrimaryId);
//var dtsource = _unitOfWork.AuditExtracts.GetAll()
//.Select(s => new ManageStatusesViewModel { StatusId = s.StatusId, Name = s.Name, Description = s.Description, IncludeInAuditYN = s.IncludeInAuditYN }).ToList();
List<ExtractItemModel> data = new ResultSet_ExtractItems().GetResult(param.Search.Value, param.SortOrder, param.Start, param.Length, dtSource, null);
int count = new ResultSet_ExtractItems().Count(param.Search.Value, dtSource, null);
DTResult<ExtractItemModel> result = new DTResult<ExtractItemModel>
{
draw = param.Draw,
data = data,
recordsFiltered = count,
recordsTotal = count
};
return Json(result);
}
catch (Exception ex)
{
ErrorTools.HandleError(ex, ErrorLevel.NonFatal); //just log, no redirect
return Json(new { error = ex.Message });
}
}
public ActionResult AjaxSeat(DTParameters param)
{
return this.Json(Generic.Ajax<SeatTemp>(this.db.Query<SeatTemp>().Where(st => st.SessionId == param.SessionId).ToList(), param));
}
public JsonResult DataHandler(DTParameters param)
{
try
{
Models.SearchParemeters keyword = new SearchParemeters();
var dtsource = new List <AffidavitViewModel>();
List <String> columnSearch = new List <string>();
foreach (var col in param.Columns)
{
//switch (col.Data)
//{
// case "aff_no":
// keyword.AffidavitId = col.Search.Value;
// param.Search.Value = col.Search.Value;
// break;
// case "property_id":
// keyword.PropertyId = col.Search.Value;
// param.Search.Value = col.Search.Value;
// break;
// case "post_dt":
// keyword.InspectionDate = col.Search.Value;
// param.Search.Value = col.Search.Value;
// break;
// case "date_added":
// keyword.AffidavitId = col.Search.Value;
// param.Search.Value = col.Search.Value;
// break;
// case "notes":
// keyword.AffidavitId = col.Search.Value;
// param.Search.Value = col.Search.Value;
// break;
// case "designator":
// keyword.AffidavitId = col.Search.Value;
// param.Search.Value = col.Search.Value;
// break;
// case "property_desc":
// keyword.AffidavitId = col.Search.Value;
// param.Search.Value = col.Search.Value;
// break;
//}
columnSearch.Add(col.Search.Value);
}
dtsource = api.GetAffidavitByKeyword(keyword);
List <AffidavitViewModel> data = new AffidavitResultSet().GetResult(param.Search.Value, param.SortOrder, param.Start, param.Length, dtsource, columnSearch);
int count = new AffidavitResultSet().Count(param.Search.Value, dtsource, columnSearch);
DTResult <AffidavitViewModel> result = new DTResult <AffidavitViewModel>
{
draw = param.Draw,
data = data,
recordsFiltered = count,
recordsTotal = count
};
return(Json(result));
}
catch (Exception ex)
{
return(Json(new { error = ex.Message }));
}
}
public virtual async Task <JsonResult> GetConfirmedDevicesWithPagination(DTParameters parameters) => await JsonAsync(_activityService.GetPagedConfirmedDevicesAsync(parameters));
public IActionResult AddBMIBudget([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
else
{
orderCriteria = "UserName";
orderAscendingDirection = true;
}
var join = (from User in _mainSystemContext.AspNetUsers
join UserRole in _mainSystemContext.AspNetUserRoles on User.Id equals UserRole.UserId
join Role in _mainSystemContext.AspNetRoles on UserRole.RoleId equals Role.Id
join SystemUser in _mainSystemContext.AspNetSystemUsers on User.Id equals SystemUser.UserId
where Role.Id == "00050"
select new
{
UserId = SystemUser.UserId,
UserName = User.UserName,
Role = Role.Name,
Department = SystemUser.DepartmentCode,
MaksimumBudget = SystemUser.PurchaseAmountApprovalLimit
});
var result = join.ToList();
if (!String.IsNullOrEmpty(searchBy))
{
result = result.Where(a => a.UserName != null && a.UserName.ToUpper().Contains(searchBy.ToUpper()) ||
a.Role != null && a.Role.ToUpper().Contains(searchBy.ToUpper()) ||
a.Department != null && a.Department.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
var getCountResult = (from SystemUser in _mainSystemContext.AspNetSystemUsers
join UserRole in _mainSystemContext.AspNetUserRoles on SystemUser.UserId equals UserRole.UserId
where UserRole.RoleId == "00050"
select new
{
Id = SystemUser.UserId
}).ToList();
result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Desc).ToList();
//Dapatkan Jumlah Total Record
var filteredResultsCount = result.Count();
var totalResultsCount = getCountResult.Count();
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
public JsonResult LoadViewModels(DTParameters param, Guid entityId) => Json(_viewModelService.LoadViewModelsWithPagination(param, entityId));
public JsonResult GetWidgetGroups(DTParameters parameters) => _repository.GetWidgetGroupsInJqueryTableFormat(parameters);
public async Task <IActionResult> GetBMIPOList([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
else
{
orderCriteria = "PurchaseHeaderId";
orderAscendingDirection = true;
}
var result = _balimoonBMIContext.PurchasesHeader.Select(a => new PurchasesHeader
{
PurchaseHeaderId = a.PurchaseHeaderId,
DocumentType = a.DocumentType,
No = a.No,
BuyfromVendorName = a.BuyfromVendorName,
BuyfromVendorName2 = a.BuyfromVendorName2,
BuyfromVendorNo = a.BuyfromVendorNo,
PaytoVendorNo = a.PaytoVendorNo,
PaytoName = a.PaytoName,
PaytoName2 = a.PaytoName2,
PaytoAddress = a.PaytoAddress,
PaytoAddress2 = a.PaytoAddress2,
PaytoCity = a.PaytoCity,
PaytoContact = a.PaytoContact,
YourReference = a.YourReference,
ShiptoCode = a.ShiptoCode,
ShiptoName = a.ShiptoName,
ShiptoName2 = a.ShiptoName2,
ShiptoAddress = a.ShiptoAddress,
ShiptoAddress2 = a.ShiptoAddress2,
ShiptoCity = a.ShiptoCity,
ShiptoContact = a.ShiptoContact,
OrderDate = a.OrderDate,
PostingDate = a.PostingDate,
ExpectedReceiptDate = a.ExpectedReceiptDate,
PostingDescription = a.PostingDescription,
PaymentTermsCode = a.PaymentTermsCode,
DueDate = a.DueDate,
ShipmentMethodCode = a.ShipmentMethodCode,
Amount = a.Amount,
RefPrno = a.RefPrno
}).ToList();
if (!String.IsNullOrEmpty(searchBy))
{
result = result.Where(a => a.No != null && a.No.ToUpper().Contains(searchBy.ToUpper()) ||
a.BuyfromVendorName != null && a.BuyfromVendorName.ToUpper().Contains(searchBy.ToUpper()) ||
a.RefPrno != null && a.RefPrno.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Desc).ToList();
//Dapatkan Jumlah Total Record
var filteredResultsCount = result.Count();
var totalResultsCount = await _balimoonBMIContext.PurchasesHeader.CountAsync();
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
public async Task <IActionResult> GetPRList([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
else
{
orderCriteria = "RequisitionHeaderId";
orderAscendingDirection = true;
}
var result = _BalimoonBMIContext.PurchaseRequisitionHeader.Select(a => new PurchaseRequisitionHeader
{
RequisitionHeaderId = a.RequisitionHeaderId,
RequisitionNo = a.RequisitionNo,
DueDate = a.DueDate,
RequesterId = a.RequesterId,
ShortcutDimension1Code = a.ShortcutDimension1Code,
ShortcutDimension2Code = a.ShortcutDimension2Code,
ExpirationDate = a.ExpirationDate,
OrderDate = a.OrderDate,
Status = a.Status,
Priority = a.Priority,
RequestNotes = a.RequestNotes,
PurchaseNo = a.PurchaseNo,
RowStatus = a.RowStatus,
CreatedBy = a.CreatedBy,
CreatedTime = a.CreatedTime,
LastModifiedBy = a.LastModifiedBy,
LastModifiedTime = a.LastModifiedTime,
LocationCode = a.LocationCode
}).ToList();
if (!String.IsNullOrEmpty(searchBy))
{
result = result.Where(r => r.RequisitionNo != null && r.RequisitionNo.ToUpper().Contains(searchBy.ToUpper()) ||
r.RequesterId != null && r.RequesterId.ToUpper().Contains(searchBy.ToUpper()) ||
r.ShortcutDimension1Code != null && r.ShortcutDimension1Code.ToUpper().Contains(searchBy.ToUpper()) ||
r.ShortcutDimension2Code != null && r.ShortcutDimension2Code.ToUpper().Contains(searchBy.ToUpper()) ||
r.RequestNotes != null && r.RequestNotes.ToUpper().Contains(searchBy.ToUpper()) ||
r.PurchaseNo != null && r.PurchaseNo.ToUpper().Contains(searchBy.ToUpper()) ||
r.CreatedBy != null && r.CreatedBy.ToUpper().Contains(searchBy.ToUpper()) ||
r.LastModifiedBy != null && r.LastModifiedBy.ToUpper().Contains(searchBy.ToUpper()) ||
r.LocationCode != null && r.LocationCode.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Desc).ToList();
//Dapatkan Jumlah Total Record
var filteredResultsCount = result.Count();
var totalResultsCount = await _BalimoonBMIContext.PurchaseRequisitionHeader.CountAsync();
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
public IActionResult GetGrid([FromBody] DTParameters param)
{
return(Json(_appSettingService.GetGrid(param)));
}
public IActionResult GetBMLPOListCL([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
else
{
orderCriteria = "PurchaseHeaderId";
orderAscendingDirection = true;
}
var result = (from header in _balimoonBMLContext.PurchasesHeader
join line in _balimoonBMLContext.PurchasesLine on header.PurchaseHeaderId equals line.PurchaseHeaderId
where header.Status == 5
select new
{
PurchaseHeaderId = header.PurchaseHeaderId,
No = header.No,
BuyfromVendorName = header.BuyfromVendorName,
RefPrno = header.RefPrno,
OrderDate = header.OrderDate,
ExpectedReceiptDate = header.ExpectedReceiptDate,
Description = line.Description,
Amount = header.AmountIncludingVat
}).ToList();
if (!String.IsNullOrEmpty(searchBy))
{
result = result.Where(a => a.No != null && a.No.ToUpper().Contains(searchBy.ToUpper()) ||
a.BuyfromVendorName != null && a.BuyfromVendorName.ToUpper().Contains(searchBy.ToUpper()) ||
a.Description != null && a.Description.ToUpper().Contains(searchBy.ToUpper()) ||
a.RefPrno != null && a.RefPrno.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Desc).ToList();
//Dapatkan Jumlah Total Record
var total = (from header in _balimoonBMLContext.PurchasesHeader
join line in _balimoonBMLContext.PurchasesLine on header.PurchaseHeaderId equals line.PurchaseHeaderId
where header.Status == 5
select new
{
HeaderId = header.PurchaseHeaderId
}).ToList();
var filteredResultsCount = result.Count();
var totalResultsCount = total.Count();
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
public async Task <IActionResult> GateTruckIndex([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
// in this example we just default sort on the 1st column
try
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
catch (Exception)
{
orderCriteria = "Id";
orderAscendingDirection = false;
}
//orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
// orderCriteria = "Id";
}
else
{
// if we have an empty search then just order the results by Id ascending
orderCriteria = "Id";
orderAscendingDirection = false;
}
var user = (await _userManager.FindByNameAsync(HttpContext.User.Identity.Name)); //same thing
TimeSpan time = DateTime.Now.TimeOfDay;
int hour = time.Hours;
int mins = time.Minutes;
int totalTime = (hour * 100) + mins;
var result = await _context.TruckEntities
.Include(a => a.AssignmentItem)
.Include(a => a.AssignmentItem.Assignment)
.Include(a => a.AssignmentItem.CnFProfile)
.Include(a => a.AssignmentItem.Yard)
.Include(a => a.AssignmentItem.Yard.Gate)
.Where(a => (a.AssignmentItem.Status == 3 && a.AssignmentItem.Assignment.DeliveryDate.Date == DateTime.Now.Date && a.AssignmentItem.Yard.GateId == user.GateId && (a.AssignmentItem.Assignment.DeliveryStart <= totalTime && totalTime <= a.AssignmentItem.Assignment.DeliveryEnd)) || ((a.Status == 2 || a.Status == 3) && a.AssignmentItem.Yard.GateId == user.GateId))
.ToListAsync();
List <TruckEntity> truckEntities = new List <TruckEntity>();
foreach (var item in result)
{
TruckEntity tempTruckEntity = new TruckEntity
{
Id = item.Id,
TruckNumer = item.TruckNumer,
AssignmentItemCnFProfileName = item.AssignmentItem.CnFProfile.Name,
AssignmentItemContainerNumber = item.AssignmentItem.ContainerNumber,
AssignmentItemVerifyNumber = item.AssignmentItem.VerifyNumber,
AssignmentItemExitNumber = item.AssignmentItem.ExitNumber,
Status = item.Status,
};
truckEntities.Add(tempTruckEntity);
}
if (!string.IsNullOrEmpty(searchBy))
{
truckEntities = truckEntities.Where(r =>
r.TruckNumer != null && r.TruckNumer.ToUpper().Contains(searchBy.ToUpper()) ||
r.AssignmentItemCnFProfileName != null && r.AssignmentItemCnFProfileName.ToUpper().Contains(searchBy.ToUpper()) ||
r.AssignmentItemContainerNumber != null && r.AssignmentItemContainerNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.AssignmentItemVerifyNumber != null && r.AssignmentItemVerifyNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.AssignmentItemExitNumber != null && r.AssignmentItemExitNumber.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
truckEntities = orderAscendingDirection ? truckEntities.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Asc).ToList() : truckEntities.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Desc).ToList();
// now just get the count of items (without the skip and take) - eg how many could be returned with filtering
var filteredResultsCount = result.Count();
var totalResultsCount = await _context.TruckEntities
.Include(a => a.AssignmentItem)
.Include(a => a.AssignmentItem.Assignment)
.Include(a => a.AssignmentItem.CnFProfile)
.Include(a => a.AssignmentItem.Yard)
.Include(a => a.AssignmentItem.Yard.Gate)
.Where(a => (a.AssignmentItem.Status == 3 && a.AssignmentItem.Assignment.DeliveryDate.Date == DateTime.Now.Date && a.AssignmentItem.Yard.GateId == user.GateId && (a.AssignmentItem.Assignment.DeliveryStart <= totalTime && totalTime <= a.AssignmentItem.Assignment.DeliveryEnd)) || ((a.Status == 2 || a.Status == 3) && a.AssignmentItem.Yard.GateId == user.GateId))
.CountAsync();
if (dtParameters.Length == -1)
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = truckEntities
.Skip(dtParameters.Start)
.ToList()
}));
}
else
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = truckEntities
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
}
public IActionResult TabelPRJadi([FromBody] DTParameters dtParameters, int RequisitionHeaderId)
{
var getPRID = RequisitionHeaderId;
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderDescendingDirection = true;
if (dtParameters.Order != null)
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderDescendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "desc";
}
else
{
orderCriteria = "RequisitionLineId";
orderDescendingDirection = true;
}
var result = (from line in _balimoonBMLContext.PurchaseRequisitionLine
join Items in _balimoonBMLContext.Items on line.RecordNo equals Items.ItemNo
where line.RequisitionheaderId == getPRID
select new
{
RequisitionLineId = line.RequisitionLineId,
SeqLineNo = line.SeqLineNo,
ItemName = Items.Description,
Description = line.Description,
Quantity = line.Quantity,
UnitofMeasure = line.UnitofMeasure,
UnitCost = line.DirectUnitCost,
Amount = line.CostAmount,
}).ToList();
if (!String.IsNullOrEmpty(searchBy))
{
result = result.Where(r => r.Description != null && r.Description.ToUpper().Contains(searchBy.ToUpper()) ||
r.UnitofMeasure != null && r.UnitofMeasure.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
result = orderDescendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtension.Order.Desc).ToList();
//Dapatkan Jumlah Total Record
var total = (from line in _balimoonBMLContext.PurchaseRequisitionLine
join Items in _balimoonBMLContext.Items on line.RecordNo equals Items.ItemNo
where line.RequisitionheaderId == getPRID
select new
{
RequisitionLineId = line.RequisitionLineId,
}).ToList();
var filteredResultsCount = result.Count();
var totalResultsCount = total.Count();
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
public async Task <IActionResult> YardKeptDownAssignments([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
// in this example we just default sort on the 1st column
try
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
catch (Exception)
{
orderCriteria = "Id";
orderAscendingDirection = true;
}
//orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
// orderCriteria = "Id";
}
else
{
// if we have an empty search then just order the results by Id ascending
orderCriteria = "Id";
orderAscendingDirection = true;
}
var user = (await _userManager.FindByNameAsync(HttpContext.User.Identity.Name)); //same thing
var result = await _context.AssignmentItems
.Include(a => a.Assignment)
.ThenInclude(a => a.AssignmentSlot)
.Include(a => a.CnFProfile)
.Include(a => a.Yard)
.Where(a => a.Status == 3 && a.YardId == user.YardId)
.ToListAsync();
List <AssignmentItem> assignmentItems = new List <AssignmentItem>();
foreach (var item in result)
{
AssignmentItem tempAssignmentItem = new AssignmentItem
{
Id = item.Id,
Vessel = item.Vessel,
AssignmentDate = item.Assignment.Date,
ImpReg = item.ImpReg,
MLO = item.MLO,
ContainerNumber = item.ContainerNumber,
Size = item.Size,
Height = item.Height,
LineNumber = item.LineNumber,
Dst = item.Dst,
Remarks = item.Remarks,
VerifyNumber = item.VerifyNumber,
ExitNumber = item.ExitNumber,
AssignmentAssignmentSlotAssignmentName = item.Assignment.AssignmentSlot.AssignmentName,
CnFProfileName = item.CnFProfile.Name,
KeepDownTime = item.KeepDownTime
};
assignmentItems.Add(tempAssignmentItem);
}
if (!string.IsNullOrEmpty(searchBy))
{
assignmentItems = assignmentItems.Where(r =>
r.Vessel != null && r.Vessel.ToUpper().Contains(searchBy.ToUpper()) ||
r.AssignmentDate != null && r.AssignmentDate.ToString("dd-MM-yyyy").Contains(searchBy.ToString()) ||
r.KeepDownTime != null && r.KeepDownTime.ToString("dd-MM-yyyy HH:mm").Contains(searchBy.ToString()) ||
r.ImpReg != null && r.ImpReg.ToUpper().Contains(searchBy.ToUpper()) ||
r.MLO != null && r.MLO.ToUpper().Contains(searchBy.ToUpper()) ||
r.ContainerNumber != null && r.ContainerNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.LineNumber != null && r.LineNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.Remarks != null && r.Remarks.ToUpper().Contains(searchBy.ToUpper()) ||
r.VerifyNumber != null && r.VerifyNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.ExitNumber != null && r.ExitNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.AssignmentAssignmentSlotAssignmentName != null && r.AssignmentAssignmentSlotAssignmentName.ToUpper().Contains(searchBy.ToUpper()) ||
r.CnFProfileName != null && r.CnFProfileName.ToUpper().Contains(searchBy.ToUpper()) ||
r.Dst != null && r.Dst.ToUpper().Contains(searchBy.ToUpper()) ||
r.Size.ToString().Contains(searchBy.ToUpper()) ||
r.Height.ToString().Contains(searchBy.ToUpper())
).ToList();
}
assignmentItems = orderAscendingDirection ? assignmentItems.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Asc).ToList() : assignmentItems.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Desc).ToList();
// now just get the count of items (without the skip and take) - eg how many could be returned with filtering
var filteredResultsCount = result.Count();
var totalResultsCount = await _context.AssignmentItems
.Include(a => a.Assignment)
.ThenInclude(a => a.AssignmentSlot)
.Include(a => a.CnFProfile)
.Include(a => a.Yard)
.Where(a => a.Status == 3 && a.YardId == user.YardId)
.CountAsync();
if (dtParameters.Length == -1)
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = assignmentItems
.Skip(dtParameters.Start)
.ToList()
}));
}
else
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = assignmentItems
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
}
public static void DataAll(DTParameters parameters)
{
AppTools.SendResponse(HttpContext.Current.Response, SubScriberController.GetGroupList_DatatbleView(parameters).ObjectToJSON());
}
internal DtResult <UserViewModel> GetList(DTParameters param)
{
var result = new DtResult <UserViewModel>();
var query = (from s in _dbContext.SalesPerson
join d in _dbContext.Department on s.DepartmentId equals d.Id
where s.Status.Equals("1")
select new UserViewModel
{
Id = s.Id,
Name = s.Name,
Department = d.Name
});
result.data = new List <UserViewModel>();
result.recordsTotal = query.Count();
var searchColumn = (from sr in param.Columns where !string.IsNullOrWhiteSpace(sr.Search.Value) select sr).ToList();
if (searchColumn?.Count() > 0)
{
foreach (var item in searchColumn)
{
if (item.Name.ToLower().Equals("name"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Name.Contains(item.Search.Value));
}
}
}
}
if (param.Search != null && !string.IsNullOrEmpty(param.Search.Value))
{
var keyword = param.Search.Value;
query = query.Where(w => w.Name.Contains(keyword) || w.Department.Contains(keyword));// ||
//w.Entity.Value.Contains(keyword) || w.PromoterName.Contains(keyword));
}
if (param.Order != null && param.Order.Length > 0)
{
foreach (var item in param.Order)
{
if (param.Columns[item.Column].Data.Equals("name"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Name) : query.OrderBy(o => o.Name);
}
}
}
result.recordsFiltered = query.Count();
if (param.Length > 0)
{
query = query.Skip(param.Start).Take(param.Length);
}
var entries = query.ToList();
foreach (var e in entries)
{
result.data.Add(e);
}
return(result);
}
/// <summary>
/// Get my orders
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public virtual async Task <DTResult <GetOrdersViewModel> > GetMyOrdersWithPaginationWayAsync(DTParameters param)
{
var userRequest = await _userManager.GetCurrentUserAsync();
return(!userRequest.IsSuccess ? new DTResult <GetOrdersViewModel>() : GetPaginatedOrdersByUserId(param, userRequest.Result.Id));
}
public JsonResult GetPkgList(DTParameters parameters, int sid)
{
var columnSearch = parameters.Columns.Select(s => s.Search.Value).Take(PackageColumns.Count()).ToList();
//XMLPath uses nested queries so to avoid that we construct these 4 filters ourselves
string supname = "";
string geos = "";
string cats = "";
string conts = "";
int daysl = 0;
if (columnSearch[2]?.Length > 0)
{
geos = columnSearch[2]; columnSearch[2] = null;
}
if (columnSearch[4]?.Length > 0)
{
cats = columnSearch[4]; columnSearch[4] = null;
}
if (columnSearch[6]?.Length > 0)
{
conts = columnSearch[6]; columnSearch[6] = null;
}
if (columnSearch[5]?.Length > 0)
{
supname = columnSearch[5]; columnSearch[5] = null;
}
if (columnSearch[8]?.Length > 0)
{
int.TryParse(columnSearch[8], out daysl); columnSearch[8] = null;
}
string sortOrder = parameters.SortOrder.Replace("EndDateStr", "ValidTo");
var sql = new PetaPoco.Sql($"Select distinct p.*,ValidTo, (select max(v.ValidTo) from PackageValidity v where v.ServiceID=p.PackageID) as EndDate " +
$"from Package p Left Join PackageValidity v on v.ServiceID=p.PackageID and v.PVId=(select max(PVId) from PackageValidity where PackageId=p.PackageID)");
var fromsql = new PetaPoco.Sql();
var wheresql = new PetaPoco.Sql($" where p.ServiceTypeId={sid} ");
if (geos.Length > 0)
{
fromsql.Append(", Geotree g, package_geotree pg");
wheresql.Append($" and p.packageId=pg.packageId and pg.geotreeid=g.geotreeid and geoname like '%{geos}%'");
}
if (cats.Length > 0)
{
fromsql.Append(", Category c, package_category pc");
wheresql.Append($" and p.packageId=pc.packageId and pc.categoryid=c.categoryid and categoryname like '%{cats}%'");
}
if (supname.Length > 0 || conts.Length > 0)
{
fromsql.Append(", supplier s, package_supplier ps");
wheresql.Append($" and p.packageId=ps.packageId and ps.Supplierid=s.Supplierid ");
if (supname.Length > 0)
{
wheresql.Append($" and Suppliername like '%{supname}%'");
}
if (conts.Length > 0)
{
wheresql.Append($" and ContractNo like '%{conts}%'");
}
}
if (daysl > 0)
{
wheresql.Append($" and datediff(day,GETDATE(), v.ValidTo) <{daysl} ");
}
wheresql.Append($"{GetWhereWithOrClauseFromColumns(PackageColumns, columnSearch)}");
sql.Append(fromsql);
sql.Append(wheresql);
sql.Append($"order by {sortOrder}");
try
{
var res = db.Query <PackageDets>(sql).Skip(parameters.Start).Take(parameters.Length).ToList();
res.ForEach(r =>
{
r.GeoName = String.Join(", ", db.Query <string>("Select GeoName from GeoTree g, package_Geotree pg where g.GeoTreeId=pg.GeoTreeid and pg.packageId=@0", r.PackageID));
r.CategoryName = String.Join(", ", db.Query <string>("Select CategoryName from Category g, package_Category pg where g.CategoryId=pg.Categoryid and pg.packageId=@0", r.PackageID));
r.SupplierNames = String.Join(", ", db.Query <string>("Select SupplierName from Supplier g, package_Supplier pg where g.SupplierId=pg.Supplierid and pg.packageId=@0", r.PackageID));
r.SupplierContractNos = String.Join(", ", db.Query <string>("Select ContractNo from package_supplier where packageId=@0", r.PackageID));
});
var dataTableResult = new DTResult <PackageDets>
{
draw = parameters.Draw,
data = res,
recordsFiltered = 10,
recordsTotal = res.Count()
};
return(Json(dataTableResult, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <IActionResult> LoadTable([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
try
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
catch (Exception)
{
orderCriteria = "Id";
orderAscendingDirection = true;
}
}
else
{
orderCriteria = "Id";
orderAscendingDirection = true;
}
//here
var result = await _context.CnFProfiles.ToListAsync();
var cnFProfiles = new List <CnFProfile>();
foreach (var item in result)
{
var cnFProfile = new CnFProfile
{
Id = item.Id,
Name = item.Name,
PhoneNumber = item.PhoneNumber,
VerificationNumber = item.VerificationNumber,
Balance = item.Balance,
Address = item.Address,
};
cnFProfiles.Add(cnFProfile);
}
if (!string.IsNullOrEmpty(searchBy))
{
cnFProfiles = cnFProfiles.Where(r =>
r.Name != null && r.Name.ToUpper().Contains(searchBy.ToUpper()) ||
r.PhoneNumber != null && r.PhoneNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.VerificationNumber != null && r.VerificationNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.Balance.ToString().Contains(searchBy.ToUpper()) ||
r.Address != null && r.Address.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
cnFProfiles = orderAscendingDirection ? cnFProfiles.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Asc).ToList() : cnFProfiles.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Desc).ToList();
var filteredResultsCount = cnFProfiles.Count();
//here
var totalResultsCount = await _context.CnFProfiles.CountAsync();
if (dtParameters.Length == -1)
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = cnFProfiles
.Skip(dtParameters.Start)
.ToList()
}));
}
else
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = cnFProfiles
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
}
public JsonResult UserDataHandler(DTParameters param)
{
try
{
var dtsource = svc.GetUserDtos(u => !u.IsDeleted).ToList();
List <string> columnSearch = new List <string>();
foreach (var col in param.Columns)
{
columnSearch.Add(col.Search.Value);
}
List <UserDto> data = new ResultSet().GetResult <IEnumerable <UserDto> >(
param.Search.Value,
param.SortOrder,
param.Start,
param.Length,
dtsource,
columnSearch,
UserFilterType.UserTable).ToList();
var manager = new UserManager(new UserStore <User>(Db.GetInstance()));
foreach (var user in data)
{
IList <string> roles = manager.GetRoles(user.Id);
if (roles.Contains("ISPUser") && user.ISPObj != null)
{
user.ISPName = user.ISPObj.Name;
user.Role = "ISPUser";
}
else if (roles.Contains("Champ"))
{
if (user.UsePrecinctCodeForChamp || user.EstateObj == null)
{
user.ChampCoverage = user.LocationObj.Name;
}
else
{
user.ChampCoverage = string.Format("<strong>{0}</strong> in {1}", user.EstateObj.Name,
user.LocationObj.Name);
}
user.Role = "Champ";
}
else
{
user.Role = roles.FirstOrDefault();
}
//for circular json serialization problem
user.EstateObj = null;
user.LocationObj = null;
user.ZoneObj = null;
user.OrdersObj = null;
user.ISPClientContactObj = null;
user.ClientContactMethods = null;
}
int count = new ResultSet().Count(param.Search.Value, dtsource, columnSearch, UserFilterType.UserTable);
DTResult <UserDto> result = new DTResult <UserDto>
{
draw = param.Draw,
data = data,
recordsFiltered = count,
recordsTotal = count
};
return(Json(result, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new { error = ex.Message }));
}
}
public async Task <IActionResult> LoadTable2([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
try
{
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
catch (Exception)
{
orderCriteria = "Id";
orderAscendingDirection = false;
}
}
else
{
orderCriteria = "Id";
orderAscendingDirection = false;
}
//here
var id = (await _userManager.FindByNameAsync(HttpContext.User.Identity.Name)).Id;
var result = await _context.AssignmentItems
.Include(a => a.Assignment)
.ThenInclude(a => a.AssignmentSlot)
.Include(a => a.CnFProfile)
.Include(a => a.Yard)
.Where(a => a.Status >= 1 && a.CnFProfile.ApplicationUserId == id && a.YardId == 2).ToListAsync();
//here
List <AssignmentItem> AssignmentItems = new List <AssignmentItem>();
//here
foreach (var item in result)
{
AssignmentItem tempAssignmentItem = new AssignmentItem
{
Id = item.Id,
Vessel = item.Vessel,
ImpReg = item.ImpReg,
MLO = item.MLO,
ContainerNumber = item.ContainerNumber,
Size = item.Size,
Height = item.Height,
LineNumber = item.LineNumber,
Dst = item.Dst,
Remarks = item.Remarks,
VerifyNumber = item.VerifyNumber,
ExitNumber = item.ExitNumber,
EstimatedTruckQty = item.EstimatedTruckQty,
Status = item.Status,
AssignmentDate = item.Assignment.Date,
AssignmentAssignmentSlotAssignmentName = item.Assignment.AssignmentSlot.AssignmentName,
YardName = item.Yard.Name,
};
AssignmentItems.Add(tempAssignmentItem);
}
//here
if (!string.IsNullOrEmpty(searchBy))
{
AssignmentItems = AssignmentItems.Where(r =>
r.Vessel != null && r.Vessel.ToUpper().Contains(searchBy.ToUpper()) ||
r.ImpReg != null && r.ImpReg.ToUpper().Contains(searchBy.ToUpper()) ||
r.MLO != null && r.MLO.ToUpper().Contains(searchBy.ToUpper()) ||
r.ContainerNumber != null && r.ContainerNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.LineNumber != null && r.LineNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.Dst != null && r.Dst.ToUpper().Contains(searchBy.ToUpper()) ||
r.Remarks != null && r.Remarks.ToUpper().Contains(searchBy.ToUpper()) ||
r.VerifyNumber != null && r.VerifyNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.ExitNumber != null && r.ExitNumber.ToUpper().Contains(searchBy.ToUpper()) ||
r.Size.ToString().Contains(searchBy.ToUpper()) ||
r.Height.ToString().Contains(searchBy.ToUpper()) ||
r.EstimatedTruckQty.ToString().Contains(searchBy.ToUpper()) ||
r.AssignmentDate != null && r.AssignmentDate.ToString("dd-MM-yyyy").Contains(searchBy.ToString()) ||
r.AssignmentAssignmentSlotAssignmentName != null && r.AssignmentAssignmentSlotAssignmentName.ToUpper().Contains(searchBy.ToUpper()) ||
r.YardName != null && r.YardName.ToUpper().Contains(searchBy.ToUpper())
).ToList();
}
//here
AssignmentItems = orderAscendingDirection ? AssignmentItems.AsQueryable().OrderByDynamic(orderCriteria,
LinqExtensions.Order.Asc).ToList() : AssignmentItems.AsQueryable().OrderByDynamic(orderCriteria,
LinqExtensions.Order.Desc).ToList();
var filteredResultsCount = result.Count();
//here
var totalResultsCount = await _context.AssignmentItems
.Include(a => a.Assignment)
.ThenInclude(a => a.AssignmentSlot)
.Include(a => a.CnFProfile)
.Include(a => a.Yard)
.Where(a => a.Status >= 1 && a.CnFProfile.ApplicationUserId == id && a.YardId == 2).CountAsync();
if (dtParameters.Length == -1)
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
//here
data = AssignmentItems
.Skip(dtParameters.Start)
.ToList()
}));
}
else
{
return(Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
//here
data = AssignmentItems
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
}));
}
}
public async Task <IActionResult> LoadPostCommentsTable([FromBody] DTParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
// in this example we just default sort on the 1st column
orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
}
else
{
// if we have an empty search then just order the results by Id ascending
orderCriteria = "Id";
orderAscendingDirection = true;
}
var result = _unitOfWork.PostCommentRepository.GetAll();
var user = await _unitOfWork.UserRepository.GetByClaimsPrincipal(HttpContext.User);
if (!await _unitOfWork.UserRepository.IsInRole(user, ConstantUserRoles.SuperAdmin))
{
result = result.Where(x => x.Post.UserId == user.Id);
}
if (!string.IsNullOrWhiteSpace(searchBy))
{
result = result.Where(r =>
(r.UserFullName != null && r.UserFullName.ToUpper().Contains(searchBy.ToUpper())) ||
(r.Post.Title != null && r.Post.Title.ToUpper().Contains(searchBy.ToUpper())) ||
(r.Body != null && r.Body.ToUpper().Contains(searchBy.ToUpper())) ||
(r.Email != null && r.Email.ToUpper().Contains(searchBy.ToUpper())) ||
(r.Ip != null && r.Ip.ToUpper().Contains(searchBy.ToUpper())) ||
(r.CreateDate.ToString("F") != null && r.CreateDate.ToString("F").Contains(searchBy)) ||
(r.LastEditDate.ToString("F") != null && r.LastEditDate.ToString("F").Contains(searchBy))
);
}
if (string.Equals(orderCriteria,
nameof(PostCommentIndexViewModel.UserFullName), StringComparison.InvariantCultureIgnoreCase))
{
result = orderAscendingDirection
? result.OrderBy(x => x.User == null ? x.UserFullName : x.User.FirstName + " " + x.User.LastName)
: result.OrderByDescending(x => x.User == null ? x.UserFullName : x.User.FirstName + " " + x.User.LastName);
}
else if (string.Equals(orderCriteria,
nameof(PostCommentIndexViewModel.PostTitle), StringComparison.InvariantCultureIgnoreCase))
{
result = orderAscendingDirection
? result.OrderBy(x => x.Post.Title)
: result.OrderByDescending(x => x.Post.Title);
}
else
{
result = orderAscendingDirection ?
result.OrderByDynamic(orderCriteria, LinqExtensions.Order.Asc) :
result.AsQueryable().OrderByDynamic(orderCriteria, LinqExtensions.Order.Desc);
}
// now just get the count of items (without the skip and take) - eg how many could be returned with filtering
var filteredResultsCount = result.Count();
var totalResultsCount = await _unitOfWork.PostCommentRepository.Count();
var resultList = result
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList();
return(new JsonResult(new DatatableJsonResultModel()
{
Draw = dtParameters.Draw,
RecordsTotal = totalResultsCount,
RecordsFiltered = filteredResultsCount,
Data = _mapper
.Map <IEnumerable <PostComment>, IEnumerable <PostCommentIndexViewModel> >(resultList)
}));
}
//[Authorize(Roles = "Admin,Consultar Artículos")]
public override DTResultHub <Record> GetRecords(DTParameters param = null)
{
return(base.GetRecords(param));
}
