public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable().AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.ColorName.ToLower().Contains(keyword) ||
x.ColorCode.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "ColorName":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.ColorName);
}
else
{
QueryData = QueryData.OrderBy(e => e.ColorName);
}
break;
case "ColorCode":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.ColorCode);
}
else
{
QueryData = QueryData.OrderBy(e => e.ColorCode);
}
break;
case "VolumeSolids":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.VolumeSolids);
}
else
{
QueryData = QueryData.OrderBy(e => e.VolumeSolids);
}
break;
default:
QueryData = QueryData.OrderByDescending(e => e.CreateDate);
break;
}
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return(new JsonResult(new ScrollDataViewModel <ColorItem>(Scroll,
this.ConvertTable.ConverterTableToViewModel <ColorItemViewModel, ColorItem>(await QueryData.AsNoTracking().ToListAsync())),
this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable();
// Where
if (Scroll.WhereId.HasValue)
{
QueryData = QueryData.Where(x => x.TypeStandardTimeId == Scroll.WhereId.Value);
}
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.StandardTimeCode.ToLower().Contains(keyword) ||
x.Description.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "StandardTimeCode":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.StandardTimeCode) :
QueryData.OrderBy(e => e.StandardTimeCode);
break;
case "Description":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.Description) :
QueryData.OrderBy(e => e.Description);
break;
case "StandardTimeValue":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.StandardTimeValue) :
QueryData.OrderBy(e => e.StandardTimeValue);
break;
default:
QueryData = QueryData.OrderBy(e => e.StandardTimeCode);
break;
}
// Get TotalRow
Scroll.TotalRow = await QueryData.CountAsync();
// Skip and Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 10);
var MapDatas = new List <StandardTimeViewModel>();
// Foreach
foreach (var item in await QueryData.ToListAsync())
{
MapDatas.Add(this.mapper.Map <StandardTime, StandardTimeViewModel>(item));
}
return(new JsonResult(new ScrollDataViewModel <StandardTimeViewModel>
(Scroll, MapDatas), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetReport([FromBody] ScrollViewModel Scroll)
{
var Message = "Data not been found";
try
{
if (Scroll != null)
{
var mapDatas = await this.GetData(Scroll);
if (mapDatas.Any())
{
var table = new DataTable();
//Adding the Columns
table.Columns.AddRange(new DataColumn[]
{
new DataColumn("PaymentDate", typeof(string)),
new DataColumn("PaymentNo.", typeof(string)),
new DataColumn("SupplierName", typeof(string)),
new DataColumn("BankAmount", typeof(string)),
new DataColumn("BankAmount2", typeof(string)),
new DataColumn("CheckNo", typeof(string)),
new DataColumn("Description", typeof(string)),
new DataColumn("BankName", typeof(string)),
new DataColumn("BankNo", typeof(string)),
new DataColumn("RefNo", typeof(string)),
new DataColumn("PayBy", typeof(string)),
new DataColumn("SupplierNo", typeof(string)),
});
//Adding the Rows
foreach (var item in mapDatas)
{
table.Rows.Add(
item.PaymentDateString,
item.PaymentNo,
item.SupplierName,
item.AmountString,
item.Amount2String,
item.CheckNo,
item.Description,
item.BankName,
item.BankNo,
item.RefNo,
item.PayBy,
item.SupplierNo
);
}
var report = this.helperService.CreateExcelFile(table, "PaymentReport");
return(File(report, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx"));
}
}
}
catch (Exception ex)
{
Message = $"Has error {ex.ToString()}";
}
return(BadRequest(new { Message }));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable()
.AsQueryable();
// Where
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.ProjectCode.ToLower().Contains(keyword) ||
x.ProjectName.ToLower().Contains(keyword) ||
x.ProjectCodeDetails.Any(z => z.ProjectCodeDetailCode.ToLower().Contains(keyword)) ||
x.ProjectCodeDetails.Any(z => z.Description.ToLower().Contains(keyword)));
}
// Order
switch (Scroll.SortField)
{
case "ProjectCode":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.ProjectCode) :
QueryData.OrderBy(e => e.ProjectCode);
break;
case "ProjectName":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.ProjectName) :
QueryData.OrderBy(e => e.ProjectName);
break;
case "StartDate":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.StartDate) :
QueryData.OrderBy(e => e.StartDate);
break;
default:
QueryData = QueryData.OrderByDescending(e => e.ProjectCode);
break;
}
// Get TotalRow
Scroll.TotalRow = await QueryData.CountAsync();
// Skip and Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 10);
var ListData = new List <ProjectCodeMasterViewModel>();
// Foreach
foreach (var item in await QueryData.ToListAsync())
{
ListData.Add(this.mapper.Map <ProjectCodeMaster, ProjectCodeMasterViewModel>(item));
}
return(new JsonResult(new ScrollDataViewModel <ProjectCodeMasterViewModel>
(Scroll, ListData), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable().AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.Description.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "Description":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Description);
}
else
{
QueryData = QueryData.OrderBy(e => e.Description);
}
break;
case "PaymentTypeString":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.PaymentType);
}
else
{
QueryData = QueryData.OrderBy(e => e.PaymentType);
}
break;
case "LastCost":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.LastCost);
}
else
{
QueryData = QueryData.OrderBy(e => e.LastCost);
}
break;
default:
QueryData = QueryData.OrderByDescending(e => e.CreateDate);
break;
}
// Skip Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
// Return
return(new JsonResult(new ScrollDataViewModel <PaymentDetailViewModel>(Scroll,
this.ConvertTable.ConverterTableToViewModel <PaymentDetailViewModel, PaymentDetail>
(await QueryData.AsNoTracking().ToListAsync())), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetReport([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
var Message = "";
try
{
var MapDatas = await this.GetData2(Scroll);
if (MapDatas.Any())
{
var table = new DataTable();
//Adding the Columns
table.Columns.AddRange(new DataColumn[]
{
new DataColumn("Code", typeof(string)),
new DataColumn("Name", typeof(string)),
new DataColumn("Model", typeof(string)),
new DataColumn("Brand", typeof(string)),
new DataColumn("S/N", typeof(string)),
new DataColumn("Branch", typeof(string)),
new DataColumn("Employee", typeof(string)),
new DataColumn("Group", typeof(string)),
new DataColumn("Register", typeof(string)),
new DataColumn("Cancel", typeof(string)),
});
//Adding the Rows
foreach (var item in MapDatas)
{
table.Rows.Add(
item.ItemCode,
item.Name,
item.Model,
item.Brand,
item.Property,
item.BranchString,
item.EmpResposibleString,
item.GroupMisString,
item.RegisterDate == null ? "-" : item.RegisterDate.Value.ToString("dd/MM/yy"),
item.CancelDate == null ? "-" : item.CancelDate.Value.ToString("dd/MM/yy")
);
}
return(File(this.helperService.CreateExcelFile(table, "ItemMaintenance"),
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ItemMaintenance_Report.xlsx"));
}
}
catch (Exception ex)
{
Message = $"{ex.ToString()}";
}
return(BadRequest(new { Message }));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
//var GroupEmployee = await this.repositoryEmployee.GetAllAsQueryable()
// .GroupBy(x => x.GroupCode)
// .Select(x => x.Key)
// .ToListAsync();
var QueryData = this.repository.GetAllAsQueryable()
// .Where(x => GroupEmployee.Contains(x.GroupCode))
.AsQueryable()
.Where(x => !x.GroupCode.StartsWith('1') &&
!x.GroupCode.StartsWith("2") &&
!x.GroupCode.StartsWith("3"));
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.GroupCode.ToLower().Contains(keyword) ||
x.Description.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "GroupCode":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.GroupCode);
}
else
{
QueryData = QueryData.OrderBy(e => e.GroupCode);
}
break;
case "Description":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Description);
}
else
{
QueryData = QueryData.OrderBy(e => e.Description);
}
break;
default:
QueryData = QueryData.OrderByDescending(e => e.Description);
break;
}
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return(new JsonResult(new ScrollDataViewModel <EmployeeGroup>
(Scroll, await QueryData.AsNoTracking().ToListAsync()), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable().AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.Size.ToLower().Contains(keyword) ||
x.Grade.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "Size":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Size);
}
else
{
QueryData = QueryData.OrderBy(e => e.Size);
}
break;
//case "ClassificationString":
// if (Scroll.SortOrder == -1)
// QueryData = QueryData.OrderByDescending(e => e.ClassificationMaterial.ClassificationCode);
// else
// QueryData = QueryData.OrderBy(e => e.ClassificationMaterial.ClassificationCode);
// break;
case "Grade":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Grade);
}
else
{
QueryData = QueryData.OrderBy(e => e.Grade);
}
break;
default:
QueryData = QueryData.OrderByDescending(e => e.Size);
break;
}
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return(new JsonResult(new ScrollDataViewModel <MaterialViewModel>
(
Scroll,
this.ConverterTableToViewModel <MaterialViewModel, Material>(await QueryData.AsNoTracking().ToListAsync())
), this.DefaultJsonSettings));
}
public async Task<IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
return BadRequest();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.Split(null);
var predicate = PredicateBuilder.False<WorkGroup>();
foreach (string temp in filters)
{
string keyword = temp;
predicate = predicate.Or(x => x.Name.ToLower().Contains(keyword) ||
x.Description.ToLower().Contains(keyword) ||
x.Remark.ToLower().Contains(keyword));
}
if (!string.IsNullOrEmpty(Scroll.Where))
predicate = predicate.And(p => p.Creator == Scroll.Where);
//Order by
Func<IQueryable<WorkGroup>, IOrderedQueryable<WorkGroup>> order;
// Order
switch (Scroll.SortField)
{
case "Name":
if (Scroll.SortOrder == -1)
order = o => o.OrderByDescending(x => x.Name);
else
order = o => o.OrderBy(x => x.Name);
break;
case "Description":
if (Scroll.SortOrder == -1)
order = o => o.OrderByDescending(x => x.Description);
else
order = o => o.OrderBy(x => x.Description);
break;
default:
order = o => o.OrderByDescending(x => x.Name);
break;
}
var QueryData = await this.repository.GetToListAsync(
selector: selected => selected, // Selected
predicate: predicate, // Where
orderBy: order, // Order
include: null, // Include
skip: Scroll.Skip ?? 0, // Skip
take: Scroll.Take ?? 50); // Take
// Get TotalRow
Scroll.TotalRow = await this.repository.GetLengthWithAsync(predicate: predicate);
return new JsonResult(new ScrollDataViewModel<WorkGroup>(Scroll, QueryData), this.DefaultJsonSettings);
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
var QueryData = this.repository.GetAllAsQueryable().AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.Name.ToLower().Contains(keyword) ||
x.Address.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "Name":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Name);
}
else
{
QueryData = QueryData.OrderBy(e => e.Name);
}
break;
case "Address":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Address);
}
else
{
QueryData = QueryData.OrderBy(e => e.Address);
}
break;
default:
QueryData = QueryData.OrderBy(e => e.Name);
break;
}
// Get TotalRow
Scroll.TotalRow = await QueryData.CountAsync();
// Skip Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return(new JsonResult(new ScrollDataViewModel <Branch>(Scroll,
await QueryData.ToListAsync()), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable()
.Include(x => x.TypeMachine)
.AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.Name.ToLower().Contains(keyword) ||
x.TypeMachine.TypeMachineCode.ToLower().Contains(keyword) ||
x.TypeMachine.Name.ToLower().Contains(keyword) ||
x.StandardTimes
.Any(z => z.StandardTimeCode.ToLower().Contains(keyword) ||
z.Description.ToLower().Contains(keyword)));
}
// Order
switch (Scroll.SortField)
{
case "TypeMachineString":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(s => $"{s.TypeMachine.TypeMachineCode}/{s.TypeMachine.Name}");
}
else
{
QueryData = QueryData.OrderBy(s => $"{s.TypeMachine.TypeMachineCode}/{s.TypeMachine.Name}");
}
break;
case "Name":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.TypeMachine.Name);
}
else
{
QueryData = QueryData.OrderBy(e => e.TypeMachine.Name);
}
break;
default:
QueryData = QueryData.OrderByDescending(s => $"{s.TypeMachine.TypeMachineCode}/{s.TypeMachine.Name}");
break;
}
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return(new JsonResult(new ScrollDataViewModel <TypeStandardTimeViewModel>
(Scroll, this.ConverterTableToViewModel <TypeStandardTimeViewModel, TypeStandardTime>(await QueryData.AsNoTracking().ToListAsync()))
, this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable()
.Where(x => !x.GroupCode.StartsWith('1') &&
!x.GroupCode.StartsWith("2") &&
!x.GroupCode.StartsWith("3"));
// Where
if (!string.IsNullOrEmpty(Scroll.Where))
{
// QueryData = QueryData.Where(x => x.GroupCode == Scroll.Where);
}
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.Description.ToLower().Contains(keyword) ||
x.GroupCode.ToLower().Contains(keyword) ||
x.Remark.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "GroupMis":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.GroupCode) :
QueryData.OrderBy(e => e.GroupCode);
break;
case "GroupDesc":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.Description) :
QueryData.OrderBy(e => e.Description);
break;
default:
QueryData = QueryData.OrderBy(e => e.Description);
break;
}
// Get TotalRow
Scroll.TotalRow = await QueryData.CountAsync();
// Skip and Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 10);
return(new JsonResult(new ScrollDataViewModel <EmployeeGroup>
(Scroll, await QueryData.ToListAsync()), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable()
.AsQueryable()
.Where(x => !x.GroupMIS.StartsWith("00"));
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.GroupMIS.ToLower().Contains(keyword) ||
x.GroupDesc.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "GroupMIS":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.GroupMIS);
}
else
{
QueryData = QueryData.OrderBy(e => e.GroupMIS);
}
break;
case "GroupDesc":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.GroupDesc);
}
else
{
QueryData = QueryData.OrderBy(e => e.GroupDesc);
}
break;
default:
QueryData = QueryData.OrderByDescending(e => e.GroupDesc);
break;
}
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return(new JsonResult(new ScrollDataViewModel <EmployeeGroupMIS>
(Scroll, await QueryData.AsNoTracking().ToListAsync()), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.HolidayName.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "HolidayName":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.HolidayName);
}
else
{
QueryData = QueryData.OrderBy(e => e.HolidayName);
}
break;
case "HolidayDate":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.HolidayDate);
}
else
{
QueryData = QueryData.OrderBy(e => e.HolidayDate);
}
break;
default:
QueryData = QueryData.OrderByDescending(e => e.HolidayDate.Value.Date);
break;
}
// Skip and Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return(new JsonResult(new ScrollDataViewModel <HolidayOverTime>
(Scroll, await QueryData.AsNoTracking().ToListAsync()), this.DefaultJsonSettings));
}
public async Task <IActionResult> PaymentSubGetScroll([FromBody] ScrollViewModel Scroll)
{
var message = "Data not been found.";
try
{
var MapDatas = await this.GetDataPaymentSub(Scroll);
return(new JsonResult(new ScrollDataViewModel <PaymentSubConViewModel>(Scroll, MapDatas), this.DefaultJsonSettings));
}
catch (Exception ex)
{
message = $"Has error {ex.ToString()}";
}
return(BadRequest(new { message }));
}
public async Task<IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable().Include(x => x.PaintTeam).AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.BlastRoomName.ToLower().Contains(keyword) ||
x.BlastRoomNumber.ToString().Contains(keyword) ||
x.PaintTeam.TeamName.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "BlastRoomName":
if (Scroll.SortOrder == -1)
QueryData = QueryData.OrderByDescending(e => e.BlastRoomName);
else
QueryData = QueryData.OrderBy(e => e.BlastRoomName);
break;
case "TeamBlastString":
if (Scroll.SortOrder == -1)
QueryData = QueryData.OrderByDescending(e => e.PaintTeam.TeamName);
else
QueryData = QueryData.OrderBy(e => e.PaintTeam.TeamName);
break;
default:
QueryData = QueryData.OrderByDescending(e => e.CreateDate);
break;
}
// Skip Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return new JsonResult(new ScrollDataViewModel<BlastRoomViewModel>(Scroll,
this.ConvertTable.ConverterTableToViewModel<BlastRoomViewModel,BlastRoom>
(await QueryData.AsNoTracking().ToListAsync())), this.DefaultJsonSettings);
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
var Message = "";
try
{
var MapDatas = await this.GetData(Scroll);
return(new JsonResult(new ScrollDataViewModel <MiscAndAcountViewModel>(Scroll, MapDatas), this.DefaultJsonSettings));
}
catch (Exception ex)
{
Message = $"{ex.ToString()}";
}
return(BadRequest());
}
public async Task <IActionResult> InvoiceOutStandingGetScroll([FromBody] ScrollViewModel Scroll)
{
var message = "Data not been found.";
try
{
var MapDatas = await this.GetDataInvoiceOutStanding(Scroll);
foreach (var item in MapDatas)
{
item.InvoiceStatus = item.DIFF > 0 ? InvoiceStatus.OutStanding : InvoiceStatus.OverDue;
}
return(new JsonResult(new ScrollDataViewModel <InvoiceOutStandingViewModel>(Scroll, MapDatas), this.DefaultJsonSettings));
}
catch (Exception ex)
{
message = $"Has error {ex.ToString()}";
}
return(BadRequest(new { message }));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
var Message = "";
try
{
var HasData = await this.GetData2(Scroll);
return(new JsonResult(new ScrollDataViewModel <StockMovementViewModel>(Scroll, HasData), this.DefaultJsonSettings));
}
catch (Exception ex)
{
Message = $"{ex.ToString()}";
}
return(BadRequest(new { Error = Message }));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var message = "Data not been found.";
try
{
if (Scroll == null)
{
return(BadRequest());
}
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.Split(null);
var predicate = PredicateBuilder.False <Treatments>();
foreach (string temp in filters)
{
string keyword = temp;
predicate = predicate.Or(x => x.TreatmentRegimen.ToLower().Contains(keyword) ||
x.Medicines.Name.ToLower().Contains(keyword) ||
x.Description.ToLower().Contains(keyword) ||
x.Remark.ToLower().Contains(keyword));
}
// Order by
Func <IQueryable <Treatments>, IOrderedQueryable <Treatments> > order;
// Order
switch (Scroll.SortField)
{
case "TreatmentRegimen":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.TreatmentRegimen);
}
else
{
order = o => o.OrderBy(x => x.TreatmentRegimen);
}
break;
case "Description":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.Description);
}
else
{
order = o => o.OrderBy(x => x.Description);
}
break;
case "Medicines":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.Medicines.Name);
}
else
{
order = o => o.OrderBy(x => x.Medicines.Name);
}
break;
case "Remark":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.Remark);
}
else
{
order = o => o.OrderBy(x => x.Remark);
}
break;
default:
order = o => o.OrderBy(x => x.Medicines.Name);
break;
}
var QueryData = await this.repository.GetToListAsync(
selector : selected => selected, // Selected
predicate : predicate, // Where
orderBy : order, // Order
include : x => x.Include(z => z.Medicines), // Include
skip : Scroll.Skip ?? 0, // Skip
take : Scroll.Take ?? 50); // Take
// Get TotalRow
Scroll.TotalRow = await this.repository.GetLengthWithAsync(predicate : predicate);
var mapDatas = new List <TreatmentsViewModel>();
foreach (var item in QueryData)
{
var MapItem = this.mapper.Map <Treatments, TreatmentsViewModel>(item);
mapDatas.Add(MapItem);
}
return(new JsonResult(new ScrollDataViewModel <TreatmentsViewModel>(Scroll, mapDatas), this.DefaultJsonSettings));
}
catch (Exception ex)
{
message = $"Has error {ex.ToString()}";
}
return(BadRequest(new { Error = message }));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.Split(null);
var predicate = PredicateBuilder.False <Location>();
foreach (string temp in filters)
{
string keyword = temp;
predicate = predicate.Or(x => x.Name.ToLower().Contains(keyword) ||
x.Description.ToLower().Contains(keyword));
}
if (!string.IsNullOrEmpty(Scroll.Where))
{
predicate = predicate.And(p => p.Creator == Scroll.Where);
}
// Order by
Func <IQueryable <Location>, IOrderedQueryable <Location> > order;
// Order
switch (Scroll.SortField)
{
case "Name":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.Name);
}
else
{
order = o => o.OrderBy(x => x.Name);
}
break;
case "Description":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.Description);
}
else
{
order = o => o.OrderBy(x => x.Description);
}
break;
default:
order = o => o.OrderBy(x => x.Name);
break;
}
var QueryData = await this.repository.GetToListAsync(
selector : selected => selected, // Selected
predicate : predicate, // Where
orderBy : order, // Order
include : null, // Include
skip : Scroll.Skip ?? 0, // Skip
take : Scroll.Take ?? 10); // Take
// Get TotalRow
Scroll.TotalRow = await this.repository.GetLengthWithAsync(predicate : predicate);
//var mapDatas = new List<ApprovedFlowMaster>();
//foreach (var item in QueryData)
//{
// var MapItem = this.mapper.Map<JobCardMaster, JobCardMasterViewModel>(item);
// mapDatas.Add(MapItem);
//}
return(new JsonResult(new ScrollDataViewModel <Location>(Scroll, QueryData.ToList()), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable()
.Where(x => x.GroupCode != "999999")
.Include(x => x.EmployeeGroupMIS)
.AsQueryable();
// Where
if (!string.IsNullOrEmpty(Scroll.Where))
{
QueryData = QueryData.Where(x => x.GroupCode == Scroll.Where);
}
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.NameEng.ToLower().Contains(keyword) ||
x.NameThai.ToLower().Contains(keyword) ||
x.EmpCode.ToLower().Contains(keyword) ||
x.GroupCode.ToLower().Contains(keyword) ||
x.GroupName.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "EmpCode":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.EmpCode) :
QueryData.OrderBy(e => e.EmpCode);
break;
case "NameThai":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.NameThai) :
QueryData.OrderBy(e => e.NameThai);
break;
case "GroupName":
QueryData = Scroll.SortOrder == 1 ?
QueryData.OrderByDescending(e => e.GroupName) :
QueryData.OrderBy(e => e.GroupName);
break;
default:
QueryData = QueryData.OrderByDescending(e => e.NameThai)
.ThenBy(e => e.EmpCode);
break;
}
// Get TotalRow
Scroll.TotalRow = await QueryData.CountAsync();
// Skip and Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 10);
var ListData = new List <EmployeeViewModel>();
// Foreach
foreach (var item in await QueryData.ToListAsync())
{
ListData.Add(this.mapper.Map <Employee, EmployeeViewModel>(item));
}
return(new JsonResult(new ScrollDataViewModel <EmployeeViewModel>
(Scroll, ListData), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.Split(null);
Expression <Func <Atextra, bool> > predicate = x => x.Zone0 == "DESTRA" && x.Ident10 == "WG" && x.Codfic0 == "CACCE";
foreach (string temp in filters)
{
string keyword = temp;
predicate = predicate.And(x => x.Ident20.ToLower().Contains(keyword.ToLower()) ||
x.Texte0.ToLower().Contains(keyword.ToLower()));
}
//Order by
Func <IQueryable <Atextra>, IOrderedQueryable <Atextra> > order;
// Order
switch (Scroll.SortField)
{
case "WorkGroupCode":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.Ident20);
}
else
{
order = o => o.OrderBy(x => x.Ident20);
}
break;
case "WorkGroupName":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.Texte0);
}
else
{
order = o => o.OrderBy(x => x.Texte0);
}
break;
default:
order = o => o.OrderBy(x => x.Ident20);
break;
}
var QueryData = await this.repositoryDim2.GetToListAsync(
selector : selected => selected, // Selected
predicate : predicate, // Where
orderBy : order, // Order
include : null, // Include
skip : Scroll.Skip ?? 0, // Skip
take : Scroll.Take ?? 10); // Take
// Get TotalRow
Scroll.TotalRow = await this.repositoryDim2.GetLengthWithAsync(predicate : predicate);
var mapDatas = new List <WorkGroupViewModel>();
foreach (var item in QueryData)
{
var MapItem = this.mapper.Map <Atextra, WorkGroupViewModel>(item);
mapDatas.Add(MapItem);
}
return(new JsonResult(new ScrollDataViewModel <WorkGroupViewModel>(Scroll, mapDatas), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
var QueryData = this.repository.GetAllAsQueryable()
.AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.TreatmentRegimen.ToLower().Contains(keyword) ||
x.Medicines.Name.ToLower().Contains(keyword) ||
x.Description.ToLower().Contains(keyword) ||
x.Remark.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "TreatmentRegimen":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.TreatmentRegimen);
}
else
{
QueryData = QueryData.OrderBy(e => e.TreatmentRegimen);
}
break;
case "Medicines":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Medicines.Name);
}
else
{
QueryData = QueryData.OrderBy(e => e.Medicines.Name);
}
break;
case "Description":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Description);
}
else
{
QueryData = QueryData.OrderBy(e => e.Description);
}
break;
case "Remark":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Remark);
}
else
{
QueryData = QueryData.OrderBy(e => e.Remark);
}
break;
default:
QueryData = QueryData.OrderBy(e => e.Medicines.Name);
break;
}
// Get TotalRow
Scroll.TotalRow = await QueryData.CountAsync();
// Skip Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
// Mapper
var HasMapper = new List <TreatmentsViewModel>();
foreach (var item in await QueryData.ToListAsync())
{
HasMapper.Add(this.mapper.Map <Treatments, TreatmentsViewModel>(item));
}
return(new JsonResult(
new ScrollDataViewModel <TreatmentsViewModel>(Scroll, HasMapper), this.DefaultJsonSettings));
}
public async Task <IActionResult> GetReport([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
var Message = "";
var MapDatas = await this.GetData2(Scroll);
try
{
if (MapDatas.Any())
{
var table = new DataTable();
//Adding the Columns
table.Columns.AddRange(new DataColumn[]
{
new DataColumn("ItemCode", typeof(string)),
new DataColumn("Description", typeof(string)),
new DataColumn("CategoryDescription", typeof(string)),
new DataColumn("Uom", typeof(string)),
new DataColumn("DocNo", typeof(string)),
new DataColumn("MovementType", typeof(string)),
new DataColumn("MovementDate", typeof(string)),
new DataColumn("QuantityIn", typeof(string)),
new DataColumn("QuantityOut", typeof(string)),
new DataColumn("Location", typeof(string)),
new DataColumn("Bom", typeof(string)),
new DataColumn("Project", typeof(string)),
new DataColumn("WorkGroup", typeof(string)),
});
//Adding the Rows
foreach (var item in MapDatas)
{
foreach (var item2 in item.StockMovement2s)
{
item.ItemDescFull = this.helperService.ConvertHtmlToText(item.ItemDescFull);
item.ItemDescFull = item.ItemDescFull.Replace("\r\n", "");
item.ItemDescFull = item.ItemDescFull.Replace("\n", "");
table.Rows.Add(
item.ItemCode,
item.ItemDescFull,
item.CategoryDesc,
item.Uom,
item2.DocNo,
item2.MovementType,
item2.MovementDateString,
item2.QuantityInString,
item2.QuantityOutString,
item2.Location,
item2.Bom,
item2.Project,
item2.WorkGroup
);
}
}
return(File(this.helperService.CreateExcelFilePivotTables(table, "StockMovement", "StockMovementPivot"),
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx"));
}
}
catch (Exception ex)
{
Message = $"{ex.ToString()}";
}
return(BadRequest());
}
private async Task <List <StockMovementViewModel> > GetData(ScrollViewModel Scroll)
{
#region Query
var QueryData = (from ProductMaster in this.sageContext.Itmmaster
//join StockJou in this.sageContext.Stojou on ProductMaster.Itmref0 equals StockJou.Itmref0 into StockJou2
//from nStockJou in StockJou2.DefaultIfEmpty()
join ProductCate in this.sageContext.Itmcateg on ProductMaster.Tclcod0 equals ProductCate.Tclcod0 into ProductCate2
from nProductCate in ProductCate2.DefaultIfEmpty()
join aText in this.sageContext.Atextra on new { code1 = nProductCate.Tclcod0, code2 = "TCLAXX" } equals new { code1 = aText.Ident10, code2 = aText.Zone0 } into AText
from nAText in AText.DefaultIfEmpty()
join bText in this.sageContext.Texclob on new { Code0 = ProductMaster.Purtex0 } equals new { bText.Code0 } into bText2
from fullText in bText2.DefaultIfEmpty()
select new
{
// nStockJou,
ProductMaster,
nProductCate,
nAText,
fullText,
}).Where(x => this.sageContext.Stojou.Any(z => z.Itmref0 == x.ProductMaster.Itmref0)).AsQueryable();
// .Where(x => x.nStockJou != null)
#endregion Query
#region Filter
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.Split(null);
foreach (string temp in filters)
{
string keyword = temp.ToLower();
QueryData = QueryData.Where(x => x.nAText.Texte0.ToLower().Contains(keyword) ||
x.ProductMaster.Itmdes10.ToLower().Contains(keyword) ||
x.ProductMaster.Itmdes20.ToLower().Contains(keyword) ||
x.ProductMaster.Itmdes30.ToLower().Contains(keyword) ||
x.ProductMaster.Itmref0.ToLower().Contains(keyword));
}
// Product Category
if (Scroll.WhereBanks.Any())
{
QueryData = QueryData.Where(x => Scroll.WhereBanks.Contains(x.ProductMaster.Tclcod0));
}
#endregion Filter
#region Scroll
switch (Scroll.SortField)
{
case "ItemCode":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.ProductMaster.Itmref0);
}
else
{
QueryData = QueryData.OrderBy(x => x.ProductMaster.Itmref0);
}
break;
case "ItemDescFull":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.ProductMaster.Itmdes10);
}
else
{
QueryData = QueryData.OrderBy(x => x.ProductMaster.Itmdes10);
}
break;
case "Uom":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.ProductMaster.Stu0);
}
else
{
QueryData = QueryData.OrderBy(x => x.ProductMaster.Stu0);
}
break;
case "Category":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.nProductCate.Tclcod0);
}
else
{
QueryData = QueryData.OrderBy(x => x.nProductCate.Tclcod0);
}
break;
case "CategoryDesc":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(x => x.nAText.Texte0);
}
else
{
QueryData = QueryData.OrderBy(x => x.nAText.Texte0);
}
break;
default:
QueryData = QueryData.OrderBy(x => x.ProductMaster.Itmref0);
break;
}
#endregion Scroll
Scroll.TotalRow = await QueryData.CountAsync();
var Message = "";
try
{
var Datasource = await QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 15).AsNoTracking().ToListAsync();
var MapDatas = new List <StockMovementViewModel>();
var Purchase = new List <int>()
{
6, 8
};
var Stock = new List <int>()
{
19, 20, 31, 32
};
var Sale = new List <int>()
{
5, 13
};
foreach (var item in Datasource)
{
var MapData = new StockMovementViewModel()
{
Category = item?.nProductCate?.Tclcod0 ?? "",
CategoryDesc = item?.nAText?.Texte0 ?? "",
ItemCode = item?.ProductMaster?.Itmref0,
ItemDesc = item?.ProductMaster?.Itmdes10,
Uom = string.IsNullOrEmpty(item?.ProductMaster?.Pcu0.Trim()) ? item?.ProductMaster?.Stu0 : item?.ProductMaster?.Pcu0,
};
//ItemName
if (item?.fullText?.Texte0 != null)
{
if (item.fullText.Texte0.StartsWith("{\\rtf1"))
{
MapData.ItemDescFull = Rtf.ToHtml(item?.fullText?.Texte0);
}
else
{
MapData.ItemDescFull = item?.fullText?.Texte0;
}
}
else
{
MapData.ItemDescFull = item?.fullText?.Texte0 ?? "-";
}
var StockJoc = await this.repository.GetToListAsync(x => x, x => x.Itmref0 == MapData.ItemCode && x.Regflg0 == 1);
foreach (var item2 in StockJoc.GroupBy(x => new {
x.Vcrnum0,
x.Vcrtyp0,
x.Iptdat0,
x.Loc0,
}))
{
MapData.StockMovement2s.Add(new StockMovement2ViewModel
{
Bom = item2?.FirstOrDefault()?.Cce1 ?? "",
DocNo = item2?.Key?.Vcrnum0 ?? "",
Location = item2?.FirstOrDefault()?.Loc0 ?? "",
MovementDate = item2?.Key?.Iptdat0,
MovementType = Purchase.Contains(item2.Key.Vcrtyp0) ? "Purchase" :
(Stock.Contains(item2.Key.Vcrtyp0) ? "Stock" :
(Sale.Contains(item2.Key.Vcrtyp0) ? "Sale" : "Stock")),
Project = item2?.FirstOrDefault()?.Cce2 ?? "",
WorkGroup = item2?.FirstOrDefault()?.Cce3 ?? "",
QuantityIn = (double)item2?.Where(x => x.Qtypcu0 > 0)?.Sum(x => x?.Qtypcu0 ?? (decimal)0),
QuantityOut = (double)item2?.Where(x => x.Qtypcu0 <= 0)?.Sum(x => x?.Qtypcu0 ?? (decimal)0),
});
}
MapDatas.Add(MapData);
}
return(MapDatas);
}
catch (Exception ex)
{
Message = $"{ex.ToString()}";
}
return(null);
}
private async Task <List <StockMovementViewModel> > GetData2(ScrollViewModel scroll)
{
if (scroll != null)
{
string sWhere = "";
string sSort = "";
string sQuery = "";
#region Where
var filters = string.IsNullOrEmpty(scroll.Filter) ? new string[] { "" }
: scroll.Filter.Split(null);
foreach (string temp in filters)
{
if (string.IsNullOrEmpty(temp))
{
continue;
}
string keyword = temp.ToLower();
sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") +
$@"(LOWER(ITM.ITMREF_0) LIKE '%{keyword}%'
OR LOWER(ITM.ITMDES1_0) LIKE '%{keyword}%'
OR LOWER(ITM.ITMDES2_0) LIKE '%{keyword}%'
OR LOWER(ITM.ITMDES3_0) LIKE '%{keyword}%''
OR LOWER(ATE.TEXTE_0) LIKE '%{keyword}%')";
}
// Product Category
if (scroll.WhereBanks.Any())
{
var list = new List <string>();
foreach (var item in scroll.WhereBanks)
{
list.Add($"'{item}'");
}
var banks = string.Join(',', list);
sWhere += (string.IsNullOrEmpty(sWhere) ? "WHERE " : " AND ") + $"ITM.TCLCOD_0 IN ({banks})";
// predicate = predicate.And(x => Scroll.WhereBanks.Contains(x.Ban0));
}
#endregion Where
#region Sort
switch (scroll.SortField)
{
case "ItemCode":
if (scroll.SortOrder == -1)
{
sSort = $"ITM.ITMREF_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pshnum0);
}
else
{
sSort = $"ITM.ITMREF_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pshnum0);
}
break;
case "ItemDescFull":
if (scroll.SortOrder == -1)
{
sSort = $"ITM.ITMDES1_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Pjth0);
}
else
{
sSort = $"ITM.ITMDES1_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Pjth0);
}
break;
case "Uom":
if (scroll.SortOrder == -1)
{
sSort = $"ITM.STU_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0);
}
else
{
sSort = $"ITM.STU_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Prqdat0);
}
break;
case "Category":
if (scroll.SortOrder == -1)
{
sSort = $"ITM.TCLCOD_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.prd.Itmdes0);
}
else
{
sSort = $"ITM.TCLCOD_0 ASC"; //QueryData = QueryData.OrderBy(x => x.prd.Itmdes0);
}
break;
case "CategoryDesc":
if (scroll.SortOrder == -1)
{
sSort = $"ATE.TEXTE_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Cce0);
}
else
{
sSort = $"ATE.TEXTE_0 ASC"; //QueryData = QueryData.OrderBy(x => x.PAYM.Cce0);
}
break;
default:
sSort = $"ITM.ITMREF_0 DESC"; //QueryData = QueryData.OrderByDescending(x => x.PAYM.Prqdat0);
break;
}
#endregion Sort
#region Query
// Query mulitple command
sQuery = $@"SELECT ITM.ITMREF_0 AS [ItemCode],
ITM.ITMDES1_0 AS [ItemDesc],
TXT.TEXTE_0 AS [ItemDescFull],
CAT.TCLCOD_0 AS [Category],
ATE.TEXTE_0 AS [CategoryDesc],
ISNULL(NULLIF(ITM.PCU_0,''),ITM.STU_0) AS [Uom]
FROM VIPCO.ITMMASTER ITM
LEFT JOIN VIPCO.TEXCLOB TXT
ON TXT.CODE_0 = ITM.PURTEX_0
LEFT JOIN VIPCO.ITMCATEG CAT
ON ITM.TCLCOD_0 = CAT.TCLCOD_0
LEFT JOIN VIPCO.ATEXTRA ATE
ON CAT.TCLCOD_0 = ATE.IDENT1_0
AND ATE.ZONE_0 = 'TCLAXX'
{sWhere}
ORDER BY {sSort}
OFFSET @Skip ROWS -- skip 10 rows
FETCH NEXT @Take ROWS ONLY; -- take 10 rows;
SELECT COUNT(*)
FROM VIPCO.ITMMASTER ITM
LEFT JOIN VIPCO.TEXCLOB TXT
ON TXT.CODE_0 = ITM.PURTEX_0
LEFT JOIN VIPCO.ITMCATEG CAT
ON ITM.TCLCOD_0 = CAT.TCLCOD_0
LEFT JOIN VIPCO.ATEXTRA ATE
ON CAT.TCLCOD_0 = ATE.IDENT1_0
AND ATE.ZONE_0 = 'TCLAXX'
{sWhere};";
#endregion Query
var result = await this.repositoryStock.GetListEntitesAndTotalRow(sQuery, new { Skip = scroll.Skip ?? 0, Take = scroll.Take ?? 15 });
var dbData = result.Entities;
scroll.TotalRow = result.TotalRow;
string stockJournal = "";
foreach (var item in dbData)
{
if (item.ItemDescFull.StartsWith("{\\rtf1"))
{
item.ItemDescFull = Rtf.ToHtml(item?.ItemDescFull);
}
else
{
item.ItemDescFull = item?.ItemDesc;
}
#region stockJournal
stockJournal = $@"SELECT STO.VCRNUM_0 AS DocNo,
STO.VCRTYP_0 AS MovementType,
STO.IPTDAT_0 AS MovementDate,
(CASE
WHEN SUM(STO.QTYPCU_0) > 0
THEN SUM(STO.QTYPCU_0)
END) AS QuantityIn,
(CASE
WHEN SUM(STO.QTYPCU_0) <= 0
THEN SUM(STO.QTYPCU_0)
END) AS QuantityOut,
STO.LOC_0 AS [Location],
STO.CCE_1 AS Bom,
STO.CCE_2 AS Project,
STO.CCE_3 AS WorkGroup
FROM VIPCO.STOJOU STO
WHERE STO.ITMREF_0 = @ItemCode AND STO.REGFLG_0 = 1
GROUP BY STO.VCRNUM_0,
STO.VCRTYP_0,
STO.IPTDAT_0,
STO.LOC_0,
STO.CCE_1,
STO.CCE_2,
STO.CCE_3
ORDER BY STO.IPTDAT_0";
var stockMoves = await this.repositoryStock2.GetListEntites(stockJournal, new { item.ItemCode });
item.StockMovement2s.AddRange(stockMoves);
#endregion stockJournal
}
return(dbData);
}
return(null);
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
var QueryData = this.repository.GetAllAsQueryable()
//.AsNoTracking() Error EF-Core 2.1 Preview2
.AsQueryable();
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.Name.ToLower().Contains(keyword) ||
x.MarkNo.ToLower().Contains(keyword) ||
x.DrawingNo.ToLower().Contains(keyword) ||
x.Remark.ToLower().Contains(keyword) ||
x.Description.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "MarkNo":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.MarkNo);
}
else
{
QueryData = QueryData.OrderBy(e => e.MarkNo);
}
break;
case "Name":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Name);
}
else
{
QueryData = QueryData.OrderBy(e => e.Name);
}
break;
case "Quantity":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Quantity);
}
else
{
QueryData = QueryData.OrderBy(e => e.Quantity);
}
break;
default:
QueryData = QueryData.OrderBy(e => e.MarkNo);
break;
}
// Get TotalRow
Scroll.TotalRow = await QueryData.CountAsync();
// Skip Take
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
try
{
var HasData = await QueryData.ToListAsync();
var listData = new List <MasterProjectListViewModel>();
foreach (var item in HasData)
{
var MapItem = this.mapper.Map <MasterProjectList, MasterProjectListViewModel>(item);
listData.Add(MapItem);
}
return(new JsonResult(new ScrollDataViewModel <MasterProjectListViewModel>(Scroll, listData), this.DefaultJsonSettings));
}
catch (Exception ex)
{
return(BadRequest(new { Error = $"{ex.ToString()}" }));
}
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
var QueryData = this.repository.GetAllAsQueryable()
.Include(x => x.ProjectCodeDetail.ProjectCodeMaster)
.AsNoTracking()
.AsQueryable();
// Where
if (!string.IsNullOrEmpty(Scroll.Where))
{
if (int.TryParse(Scroll.Where, out int id))
{
QueryData = QueryData.Where(x => x.ProjectCodeDetailId == id);
}
}
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.ToLower().Split(null);
foreach (var keyword in filters)
{
QueryData = QueryData.Where(x => x.CuttingPlanNo.ToLower().Contains(keyword) ||
x.Description.ToLower().Contains(keyword) ||
x.ProjectCodeDetail.ProjectCodeDetailCode.ToLower().Contains(keyword) ||
x.ProjectCodeDetail.Description.ToLower().Contains(keyword));
}
// Order
switch (Scroll.SortField)
{
case "CuttingPlanNo":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.CuttingPlanNo);
}
else
{
QueryData = QueryData.OrderBy(e => e.CuttingPlanNo);
}
break;
case "Description":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.Description);
}
else
{
QueryData = QueryData.OrderBy(e => e.Description);
}
break;
case "ProjectCodeString":
if (Scroll.SortOrder == -1)
{
QueryData = QueryData.OrderByDescending(e => e.ProjectCodeDetail.ProjectCodeDetailCode);
}
else
{
QueryData = QueryData.OrderBy(e => e.ProjectCodeDetail.ProjectCodeDetailCode);
}
break;
default:
QueryData = QueryData.OrderByDescending(e => e.CuttingPlanNo);
break;
}
QueryData = QueryData.Skip(Scroll.Skip ?? 0).Take(Scroll.Take ?? 50);
return(new JsonResult(new ScrollDataViewModel <CuttingPlanViewModel>
(Scroll,
this.ConverterTableToViewModel <CuttingPlanViewModel, CuttingPlan>(await QueryData.AsNoTracking().ToListAsync())),
this.DefaultJsonSettings));
}
public async Task <IActionResult> GetScroll([FromBody] ScrollViewModel Scroll)
{
if (Scroll == null)
{
return(BadRequest());
}
// Filter
var filters = string.IsNullOrEmpty(Scroll.Filter) ? new string[] { "" }
: Scroll.Filter.Split(null);
var predicate = PredicateBuilder.False <ReceiveStockSp>();
foreach (string temp in filters)
{
string keyword = temp;
predicate = predicate.Or(x => x.PurchaseOrder.ToLower().Contains(keyword) ||
x.Remark.ToLower().Contains(keyword) ||
x.SparePart.Name.ToLower().Contains(keyword));
}
if (!string.IsNullOrEmpty(Scroll.Where))
{
predicate = predicate.And(p => p.Creator == Scroll.Where);
}
//Order by
Func <IQueryable <ReceiveStockSp>, IOrderedQueryable <ReceiveStockSp> > order;
// Order
switch (Scroll.SortField)
{
case "SparePartName":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.SparePart.Name);
}
else
{
order = o => o.OrderBy(x => x.SparePart.Name);
}
break;
case "Quantity":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.Quantity);
}
else
{
order = o => o.OrderBy(x => x.Quantity);
}
break;
case "ReceiveDate":
if (Scroll.SortOrder == -1)
{
order = o => o.OrderByDescending(x => x.ReceiveDate);
}
else
{
order = o => o.OrderBy(x => x.ReceiveDate);
}
break;
default:
order = o => o.OrderByDescending(x => x.ReceiveDate);
break;
}
var QueryData = await this.repository.GetToListAsync(
selector : x => new ReceiveStockSpViewModel
{
MovementStockSpId = x.MovementStockSpId,
PurchaseOrder = x.PurchaseOrder,
Quantity = x.Quantity,
ReceiveDate = x.ReceiveDate,
ReceiveEmp = x.ReceiveEmp,
ReceiveStockSpId = x.ReceiveStockSpId,
Remark = x.Remark,
SparePartId = x.SparePartId,
SparePartName = x.SparePart.Name
}, // Selected
predicate : predicate, // Where
orderBy : order, // Order
include : x => x.Include(z => z.SparePart), // Include
skip : Scroll.Skip ?? 0, // Skip
take : Scroll.Take ?? 50); // Take
// Get TotalRow
Scroll.TotalRow = await this.repository.GetLengthWithAsync(predicate : predicate);
//var mapDatas = new List<ReceiveStockSpViewModel>();
//foreach (var item in QueryData)
//{
// var MapItem = this.mapper.Map<ReceiveStockSp, ReceiveStockSpViewModel>(item);
// mapDatas.Add(MapItem);
//}
return(new JsonResult(new ScrollDataViewModel <ReceiveStockSpViewModel>(Scroll, QueryData), this.DefaultJsonSettings));
}
