public ActionResult SearchServiceRequest(UserMonitoringSrSearchFilter searchFilter)
{
Logger.Info(_logMsg.Clear().SetPrefixMsg("User Monitoring :: Search UserAssign").ToInputLogString());
try
{
if (ModelState.IsValid)
{
_userMonitoringFacade = new UserMonitoringFacade();
var viewModel = new UserMonitoringSrViewModel();
viewModel.SearchFilter = searchFilter;
viewModel.ResultList = _userMonitoringFacade.SearchServiceRequest(searchFilter);
ViewBag.PageSize = viewModel.SearchFilter.PageSize;
Logger.Info(_logMsg.Clear().SetPrefixMsg("User Monitoring :: Search UserAssign").ToSuccessLogString());
return(PartialView("~/Views/UserMonitoring/_SearchServiceRequest.cshtml", viewModel));
}
return(Json(new
{
Valid = false,
Error = string.Empty,
Errors = GetModelValidationErrors()
}));
}
catch (Exception ex)
{
Logger.Error("Exception occur:\n", ex);
Logger.Info(_logMsg.Clear().SetPrefixMsg("User Monitoring :: Search UserAssign").Add("Error Message", ex.Message).ToFailLogString());
return(Error(new HandleErrorInfo(ex, this.ControllerContext.RouteData.Values["controller"].ToString(),
this.ControllerContext.RouteData.Values["action"].ToString())));
}
}
public List <int> GetFilterUserId(UserMonitoringSrSearchFilter searchFilter)
{
if (searchFilter.ViewType.ToUpper(CultureInfo.InvariantCulture) == "BRANCH" && searchFilter.BranchId.HasValue)
{
return(_context.TB_R_USER.Where(x => x.STATUS == 1 && (x.IS_GROUP ?? false) && x.BRANCH_ID == searchFilter.BranchId.Value).Select(x => x.USER_ID).ToList());
}
else if (searchFilter.ViewType.ToUpper(CultureInfo.InvariantCulture) == "USER" && searchFilter.UserId.HasValue)
{
return(new List <int> {
searchFilter.UserId.Value
});
}
else
{
return(new List <int>());
}
}
private static IQueryable <ServiceRequestEntity> SetServiceRequestSort(IQueryable <ServiceRequestEntity> resultList, UserMonitoringSrSearchFilter searchFilter)
{
if (string.IsNullOrEmpty(searchFilter.SortOrder))
{
searchFilter.SortOrder = "ASC";
}
if (string.IsNullOrEmpty(searchFilter.SortField))
{
searchFilter.SortField = "ThisAlert";
}
if (searchFilter.SortOrder.ToUpper(CultureInfo.InvariantCulture).Equals("ASC"))
{
switch (searchFilter.SortField)
{
case "ProductName":
return(resultList.OrderBy(a => a.ProductName));
case "AreaName":
return(resultList.OrderBy(a => a.AreaName));
case "SubAreaName":
return(resultList.OrderBy(a => a.SubAreaName));
case "SrStatus":
return(resultList.OrderBy(a => a.SrStatusName));
case "CreateDate":
return(resultList.OrderBy(a => a.CreateDate));
case "CloseDated":
return(resultList.OrderBy(a => a.ClosedDate));
case "OwnerUserFullname":
return(resultList.OrderBy(a => a.OwnerUserFullname));
case "DelegateUserFullname":
return(resultList.OrderBy(a => a.DelegateUserFullname));
default:
return(resultList.OrderByDescending(a => a.ThisAlert));
}
}
else
{
switch (searchFilter.SortField)
{
case "ProductName":
return(resultList.OrderByDescending(a => a.ProductName));
case "AreaName":
return(resultList.OrderByDescending(a => a.AreaName));
case "SubAreaName":
return(resultList.OrderByDescending(a => a.SubAreaName));
case "SrStatus":
return(resultList.OrderByDescending(a => a.SrStatusName));
case "CreateDate":
return(resultList.OrderByDescending(a => a.CreateDate));
case "CloseDated":
return(resultList.OrderByDescending(a => a.ClosedDate));
case "OwnerUserFullname":
return(resultList.OrderByDescending(a => a.OwnerUserFullname));
case "DelegateUserFullname":
return(resultList.OrderByDescending(a => a.DelegateUserFullname));
default:
return(resultList.OrderBy(a => a.ThisAlert));
}
}
}
public List <ServiceRequestEntity> SearchServiceRequest(UserMonitoringSrSearchFilter searchFilter)
{
var userIds = GetFilterUserId(searchFilter);
var ignoreStatusList = new string[] { Constants.SRStatusCode.Closed, Constants.SRStatusCode.Cancelled, Constants.SRStatusCode.Draft };
var fromDate = searchFilter.AssignDateFromValue.HasValue ? searchFilter.AssignDateFromValue.Value.Date : new Nullable <DateTime>();
var toDate = searchFilter.AssignDateToValue.HasValue ? searchFilter.AssignDateToValue.Value.Date : new Nullable <DateTime>();
var query = (from sr in _context.TB_T_SR
from ownerUser in _context.TB_R_USER.Where(user => sr.OWNER_USER_ID == user.USER_ID).DefaultIfEmpty()
from delegateUser in _context.TB_R_USER.Where(user => sr.DELEGATE_USER_ID == user.USER_ID).DefaultIfEmpty()
from status in _context.TB_C_SR_STATUS.Where(x => x.SR_STATUS_ID == sr.SR_STATUS_ID).DefaultIfEmpty()
where sr.OWNER_USER_ID.HasValue && userIds.Contains(sr.OWNER_USER_ID.Value) &&
(!searchFilter.ProductId.HasValue || sr.PRODUCT_ID == searchFilter.ProductId.Value) &&
(!searchFilter.CampaignServiceId.HasValue || sr.CAMPAIGNSERVICE_ID == searchFilter.CampaignServiceId.Value) &&
(!searchFilter.AreaId.HasValue || sr.AREA_ID == searchFilter.AreaId.Value) &&
(!searchFilter.SubAreaId.HasValue || sr.SUBAREA_ID == searchFilter.SubAreaId.Value) &&
(!fromDate.HasValue || EntityFunctions.TruncateTime(sr.CREATE_DATE.Value) >= fromDate.Value) &&
(!toDate.HasValue || EntityFunctions.TruncateTime(sr.CREATE_DATE.Value) <= toDate.Value)
&&
(
(!string.IsNullOrEmpty(searchFilter.StatusCode) && status.SR_STATUS_CODE == searchFilter.StatusCode)
||
(string.IsNullOrEmpty(searchFilter.StatusCode) && !ignoreStatusList.Contains(status.SR_STATUS_CODE))
//&& status.SR_STATUS_CODE != Constants.SRStatusCode.Closed && status.SR_STATUS_CODE != Constants.SRStatusCode.Cancelled
)
select new ServiceRequestEntity
{
TransferType = "OWNER",
SrId = sr.SR_ID,
SrNo = sr.SR_NO,
ThisAlert = sr.RULE_THIS_ALERT,
NextSLA = sr.RULE_NEXT_SLA,
TotalWorkingHours = sr.RULE_TOTAL_WORK,
CustomerCardNo = sr.TB_M_CUSTOMER.CARD_NO,
ChannelId = sr.CHANNEL_ID,
ChannelName = sr.TB_R_CHANNEL.CHANNEL_NAME,
ProductId = sr.PRODUCT_ID,
ProductName = sr.TB_R_PRODUCT.PRODUCT_NAME,
CampaignServiceId = sr.CAMPAIGNSERVICE_ID,
CampaignServiceName = sr.TB_R_CAMPAIGNSERVICE.CAMPAIGNSERVICE_NAME,
AreaId = sr.AREA_ID,
AreaName = sr.TB_M_AREA.AREA_NAME,
SubAreaId = sr.SUBAREA_ID,
SubAreaName = sr.TB_M_SUBAREA.SUBAREA_NAME,
Subject = sr.SR_SUBJECT,
SrStatusName = sr.TB_C_SR_STATUS.SR_STATUS_NAME,
CreateDate = sr.CREATE_DATE,
ClosedDate = sr.CLOSE_DATE,
OwnerUserId = ownerUser != null ? ownerUser.USER_ID : (int?)null,
OwnerUserPosition = ownerUser != null ? ownerUser.POSITION_CODE : null,
OwnerUserFirstName = ownerUser != null ? ownerUser.FIRST_NAME : null,
OwnerUserLastName = ownerUser != null ? ownerUser.LAST_NAME : null,
DelegateUserId = delegateUser != null ? delegateUser.USER_ID : (int?)null,
DelegateUserPosition = delegateUser != null ? delegateUser.POSITION_CODE : null,
DelegateUserFirstName = delegateUser != null ? delegateUser.FIRST_NAME : null,
DelegateUserLastName = delegateUser != null ? delegateUser.LAST_NAME : null,
ANo = sr.SR_ANO,
}).Concat(from sr in _context.TB_T_SR
from ownerUser in _context.TB_R_USER.Where(user => sr.OWNER_USER_ID == user.USER_ID).DefaultIfEmpty()
from delegateUser in _context.TB_R_USER.Where(user => sr.DELEGATE_USER_ID == user.USER_ID).DefaultIfEmpty()
from status in _context.TB_C_SR_STATUS.Where(x => x.SR_STATUS_ID == sr.SR_STATUS_ID).DefaultIfEmpty()
where sr.DELEGATE_USER_ID.HasValue && userIds.Contains(sr.DELEGATE_USER_ID.Value) &&
(!searchFilter.ProductId.HasValue || sr.PRODUCT_ID == searchFilter.ProductId.Value) &&
(!searchFilter.CampaignServiceId.HasValue || sr.CAMPAIGNSERVICE_ID == searchFilter.CampaignServiceId.Value) &&
(!searchFilter.AreaId.HasValue || sr.AREA_ID == searchFilter.AreaId.Value) &&
(!searchFilter.SubAreaId.HasValue || sr.SUBAREA_ID == searchFilter.SubAreaId.Value) &&
(!fromDate.HasValue || EntityFunctions.TruncateTime(sr.CREATE_DATE.Value) >= fromDate.Value) &&
(!toDate.HasValue || EntityFunctions.TruncateTime(sr.CREATE_DATE.Value) <= toDate.Value)
&&
(
(!string.IsNullOrEmpty(searchFilter.StatusCode) && status.SR_STATUS_CODE == searchFilter.StatusCode)
||
(string.IsNullOrEmpty(searchFilter.StatusCode) && status.SR_STATUS_CODE != Constants.SRStatusCode.Closed && status.SR_STATUS_CODE != Constants.SRStatusCode.Cancelled)
)
select new ServiceRequestEntity
{
TransferType = "DELEGATE",
SrId = sr.SR_ID,
SrNo = sr.SR_NO,
ThisAlert = sr.RULE_THIS_ALERT,
NextSLA = sr.RULE_NEXT_SLA,
TotalWorkingHours = sr.RULE_TOTAL_WORK,
CustomerCardNo = sr.TB_M_CUSTOMER.CARD_NO,
ChannelId = sr.CHANNEL_ID,
ChannelName = sr.TB_R_CHANNEL.CHANNEL_NAME,
ProductId = sr.PRODUCT_ID,
ProductName = sr.TB_R_PRODUCT.PRODUCT_NAME,
CampaignServiceId = sr.CAMPAIGNSERVICE_ID,
CampaignServiceName = sr.TB_R_CAMPAIGNSERVICE.CAMPAIGNSERVICE_NAME,
AreaId = sr.AREA_ID,
AreaName = sr.TB_M_AREA.AREA_NAME,
SubAreaId = sr.SUBAREA_ID,
SubAreaName = sr.TB_M_SUBAREA.SUBAREA_NAME,
Subject = sr.SR_SUBJECT,
SrStatusName = sr.TB_C_SR_STATUS.SR_STATUS_NAME,
CreateDate = sr.CREATE_DATE,
ClosedDate = sr.CLOSE_DATE,
OwnerUserId = ownerUser != null ? ownerUser.USER_ID : (int?)null,
OwnerUserPosition = ownerUser != null ? ownerUser.POSITION_CODE : null,
OwnerUserFirstName = ownerUser != null ? ownerUser.FIRST_NAME : null,
OwnerUserLastName = ownerUser != null ? ownerUser.LAST_NAME : null,
DelegateUserId = delegateUser != null ? delegateUser.USER_ID : (int?)null,
DelegateUserPosition = delegateUser != null ? delegateUser.POSITION_CODE : null,
DelegateUserFirstName = delegateUser != null ? delegateUser.FIRST_NAME : null,
DelegateUserLastName = delegateUser != null ? delegateUser.LAST_NAME : null,
ANo = sr.SR_ANO
});
int startPageIndex = (searchFilter.PageNo - 1) * searchFilter.PageSize;
searchFilter.TotalRecords = query.Count();
if (startPageIndex >= searchFilter.TotalRecords)
{
startPageIndex = 0;
searchFilter.PageNo = 1;
}
query = SetServiceRequestSort(query, searchFilter);
return(query.Skip(startPageIndex).Take(searchFilter.PageSize).ToList <ServiceRequestEntity>());
}
public List <ServiceRequestEntity> SearchServiceRequest(UserMonitoringSrSearchFilter searchFilter)
{
var dataAccess = new UserMonitoringDataAccess(_context);
return(dataAccess.SearchServiceRequest(searchFilter));
}
