public async Task <IActionResult> ListClientAll([DataTablesRequest] DataTablesRequest dataRequest)
{
var pgData = new PageData(dataRequest, _context)
{
select = @"SELECT cm.ID ClientID,cm.ClientCode,cm.Name ClientName,cm.ClientTypeID,
cm.IsActive,cm.UserCreate,cm.DateCreate,ct.UserUpdate,ct.DateUpdate,
cm.Building,cm.Address,cm.Phone,cm.Fax,cm.Email,ct.Name ClientTypeName ",
Tabel = @" FROM Client cm WITH(NOLOCK)
INNER JOIN dbo.ClientType ct WITH(NOLOCK) ON ct.ID = cm.ClientTypeID
WHERE 1=1 ",
};
//defenisikan Where condition
foreach (var req in dataRequest.Columns)
{
if (string.IsNullOrEmpty(req.SearchValue))
{
continue;
}
else if (req.Data == "clientID")
{
pgData.AddWhereRegex(pgData.paternAngkaLike, req.SearchValue, "cm.ID");
}
else if (req.Data == "clientCode")
{
pgData.AddWhereRegex(pgData.paternAngkaHurufLike, req.SearchValue, "cm.ClientCode");
}
else if (req.Data == "clientName")
{
pgData.AddWhereRegex(pgData.paternAngkaHurufLike, req.SearchValue, "cm.Name");
}
else if (req.Data == "clientTypeName")
{
pgData.AddWhereRegex(pgData.paternAngka, req.SearchValue, "cm.ClientTypeID");
}
else if (req.Data == "isActive")
{
pgData.AddWhereRegex(pgData.paternAngka, req.SearchValue, "cm.IsActive");
}
}
List <ClientListVM> ls = new List <ClientListVM>();
try
{
pgData.CountData(); // hitung jlh total data dan total dataFilter
ls = await _context.Set <ClientListVM>().FromSql(pgData.GenerateQueryString()).ToListAsync();
}
catch (Exception ex)
{
flashMessage.Danger("Error ListClientAll : " + ex.Message);
}
return(Json(ls.ToDataTablesResponse(dataRequest, pgData.recordsTotal, pgData.recordsFilterd)));
}
public async Task <IActionResult> ClientActiveAll([DataTablesRequest] DataTablesRequest dataRequest)
{
var pgData = new PageData(dataRequest, _context)
{
select = @"SELECT cl.ID ClientID,cl.ClientCode,cl.Name ClientName,ct.Name ClientTypeName,cl.Building,cl.Address,cl.ClientTypeID,cl.ID ",
Tabel = @" FROM dbo.Client cl
INNER JOIN dbo.ClientType ct ON ct.ID = cl.ClientTypeID
WHERE cl.IsActive=1 ",
};
//defenisikan Where condition
foreach (var req in dataRequest.Columns)
{
if (string.IsNullOrEmpty(req.SearchValue))
{
continue;
}
else if (req.Data == "clientID")
{
pgData.AddWhereRegex(pgData.paternAngkaLike, req.SearchValue, "cl.ID");
}
else if (req.Data == "clientCode")
{
pgData.AddWhereRegex(pgData.paternAngkaHurufLike, req.SearchValue, "cl.ClientCode");
}
else if (req.Data == "clientName")
{
pgData.AddWhereRegex(pgData.paternAngkaHurufLike, req.SearchValue, "cl.Name");
}
else if (req.Data == "clientName")
{
pgData.AddWhereRegex(pgData.paternAngkaHurufLike, req.SearchValue, "Name");
}
else if (req.Data == "clientName")
{
pgData.AddWhereRegex(pgData.paternAngkaHurufLike, req.SearchValue, "Name");
}
}
List <EnrollmentVM> ls = new List <EnrollmentVM>();
try
{
pgData.CountData(); // hitung jlh total data dan total dataFilter
ls = await _context.Set <EnrollmentVM>().FromSql(pgData.GenerateQueryString()).ToListAsync();
}
catch (Exception ex) { flashMessage.Info("Error Client Enroll : " + ex.Message); }
return(Json(ls.ToDataTablesResponse(dataRequest, pgData.recordsTotal, pgData.recordsFilterd)));
}