public SearchResult <AreaDTO> Search(AreaSearchFilter filter)
{
if (string.IsNullOrEmpty(filter.SortName))
{
filter.SortName = "Area_PK";
}
var filteredRecords =
GetQuery()
.Where(area =>
area.Title.Contains(filter.Keyword));
var displayedRecords = filteredRecords.
SortBy(filter.SortName, filter.SortDir)
.Skip(filter.Skip)
.Take(filter.PageSize)
.ToList();
var searchResult = new SearchResult <AreaDTO>(filter);
searchResult.Filter = filter;
searchResult.Count.TotalRecords = GetTotalRecords();
searchResult.Count.TotalFiltered = filteredRecords.Count();
searchResult.Count.TotalDisplayed = displayedRecords.Count();
searchResult.Records = displayedRecords;
return(searchResult);
}
public ActionResult SearchAreaList(AreaSearchFilter searchFilter)
{
Logger.Info(_logMsg.Clear().SetPrefixMsg("Search Area").ToInputLogString());
try
{
if (ModelState.IsValid)
{
_areaFacade = new AreaFacade();
var model = new AreaViewModel();
model.SearchFilter = searchFilter;
model.AreaList = _areaFacade.GetAreaList(model.SearchFilter);
ViewBag.PageSize = model.SearchFilter.PageSize;
// Logger.Info(_logMsg.Clear().SetPrefixMsg("Search Customer").ToSuccessLogString());
return(PartialView("~/Views/Area/_AreaList.cshtml", model));
}
return(Json(new
{
Valid = false,
Error = string.Empty
}));
}
catch (Exception ex)
{
Logger.Info(_logMsg.Clear().SetPrefixMsg("Search Area").ToFailLogString());
return(Error(new HandleErrorInfo(ex, this.ControllerContext.RouteData.Values["controller"].ToString(),
this.ControllerContext.RouteData.Values["action"].ToString())));
}
}
public AreaListModel Get(AreaSearchFilter searchFilter)
{
AreaListFormData formData = new AreaListFormData();
SearchResult <AreaDTO> searchResult = areaQuery.Search(searchFilter);
return(new AreaListModel()
{
FormData = formData,
SearchResult = searchResult
});
}
public IHttpActionResult Search([FromUri] AreaSearchFilter filter)
{
ThrowIfUserHasNoRole(readRole);
if (filter == null)
{
throw new KairosException("Missing search filter parameter");
}
using (var areaQuery = new AreaQuery(Db))
{
var data = areaQuery.Search(filter);
return(Ok(new SuccessResponse(data)));
}
}
public IEnumerable <AreaItemEntity> GetAreaList(AreaSearchFilter searchFilter)
{
var areaStatus = searchFilter.Status == "all" ? null : searchFilter.Status.ToNullable <bool>();
var resultQuery = (from area in _context.TB_M_AREA
from createUser in _context.TB_R_USER.Where(x => x.USER_ID == area.CREATE_USER).DefaultIfEmpty()
from updateUser in _context.TB_R_USER.Where(x => x.USER_ID == area.UPDATE_USER).DefaultIfEmpty()
where ((searchFilter.AreaName == null || area.AREA_NAME.Contains(searchFilter.AreaName)) &&
(!areaStatus.HasValue || area.AREA_IS_ACTIVE == areaStatus) &&
(searchFilter.AreaCode == null || SqlFunctions.StringConvert(area.AREA_CODE).Contains(searchFilter.AreaCode)))
select new AreaItemEntity
{
AreaId = area.AREA_ID,
AreaName = area.AREA_NAME,
AreaCode = SqlFunctions.StringConvert(area.AREA_CODE),
IsActive = area.AREA_IS_ACTIVE ? "Active" : "Inactive",
UpdateUser = (updateUser != null ? new UserEntity()
{
PositionCode = updateUser.POSITION_CODE,
Firstname = updateUser.FIRST_NAME,
Lastname = updateUser.LAST_NAME
} : null),
CreateUser = (createUser != null ? new UserEntity()
{
PositionCode = createUser.POSITION_CODE,
Firstname = createUser.FIRST_NAME,
Lastname = createUser.LAST_NAME
} : null),
UpdateDate = area.UPDATE_DATE.HasValue ? area.UPDATE_DATE : area.CREATE_DATE,
});
int startPageIndex = (searchFilter.PageNo - 1) * searchFilter.PageSize;
searchFilter.TotalRecords = resultQuery.Count();
if (startPageIndex >= searchFilter.TotalRecords)
{
startPageIndex = 0;
searchFilter.PageNo = 1;
}
resultQuery = SetAreaListSort(resultQuery, searchFilter);
return(resultQuery.Skip(startPageIndex).Take(searchFilter.PageSize).ToList());
}
private static IQueryable <AreaItemEntity> SetAreaListSort(IQueryable <AreaItemEntity> areaList, AreaSearchFilter searchFilter)
{
if (searchFilter.SortOrder.ToUpper(CultureInfo.InvariantCulture).Equals("ASC"))
{
switch (searchFilter.SortField.ToUpper(CultureInfo.InvariantCulture))
{
case "AreaName":
return(areaList.OrderBy(a => a.AreaName));
default:
return(areaList.OrderBy(a => a.AreaName));
}
}
else
{
switch (searchFilter.SortField.ToUpper(CultureInfo.InvariantCulture))
{
case "AreaName":
return(areaList.OrderByDescending(a => a.AreaName));
default:
return(areaList.OrderByDescending(a => a.AreaName));
}
}
}
public IEnumerable <AreaItemEntity> GetAreaList(AreaSearchFilter searchFilter)
{
_areaDataAccess = new AreaDataAccess(_context);
return(_areaDataAccess.GetAreaList(searchFilter));
}
public HttpResponseMessage Export(GlobalSolusindoDb Db, string fileName, BTSStatusSearchFilter filter)
{
_fileName = fileName;
//CREATE WORKBOOK
var workbook = new XLWorkbook();
DataTable bts = new DataTable("BTSUpload"); //DataTable Name = Worksheet Name
BTSExportDTO obj = new BTSExportDTO();
//Setup Column Names
foreach (var item in obj.GetType().GetProperties())
{
bts.Columns.Add(item.Name);
}
workbook.AddWorksheet(bts); // NO DATA = ADD Worksheet to WorkBook
//Worksheet Properties
var worksheet = workbook.Worksheet(1);
worksheet.Columns().Width = 15;
//BTS Status
using (var BTSStatusSearch = new BTSStatusSearch(Db))
{
// Validation BTS Status
DataTable validationTable = new DataTable();
validationTable.Columns.Add("StatusBTSFK");
validationTable.Columns.Add("StatusBTS");
validationTable.TableName = "StatusBTS";
var data = BTSStatusSearch.GetDataByFilter(filter);
var dataExport = data.Records.Select(c => new tblM_BTSStatus
{
BTSStatus_PK = c.BTSStatus_PK,
Title = c.Title
}).ToList();
DataRow dr;
int startcell = 2, endcell = 2;
foreach (var item in dataExport)
{
dr = validationTable.NewRow();
dr["StatusBTSFK"] = item.BTSStatus_PK;
dr["StatusBTS"] = item.Title;
validationTable.Rows.Add(dr);
endcell++;
}
var worksheet2 = workbook.AddWorksheet(validationTable);
worksheet.Column(9).SetDataValidation().List(worksheet2.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//Validation Operator
DataTable validationTable2 = new DataTable();
validationTable2.Columns.Add("Operator_FK");
validationTable2.Columns.Add("Operator");
validationTable2.TableName = "Operator";
//Operator
OperatorSearchFilter a = new OperatorSearchFilter();
using (var OperatorSearch = new OperatorSearch(Db))
{
var data2 = OperatorSearch.GetDataByFilter(a);
var dataExport2 = data2.Records.Select(c => new tblM_Operator
{
Operator_PK = c.Operator_PK,
Title = c.Title
}).ToList();
DataRow dr2;
startcell = 2; endcell = 2;
foreach (var item in dataExport2)
{
dr2 = validationTable2.NewRow();
dr2["Operator_FK"] = item.Operator_PK;
dr2["Operator"] = item.Title;
validationTable2.Rows.Add(dr2);
endcell++;
}
var worksheet3 = workbook.AddWorksheet(validationTable2);
worksheet.Column(4).SetDataValidation().List(worksheet3.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//Validation Area
DataTable validationTable3 = new DataTable();
validationTable3.Columns.Add("AreaFK");
validationTable3.Columns.Add("Area");
validationTable3.TableName = "Area";
//Operator
AreaSearchFilter b = new AreaSearchFilter();
using (var AreaSearch = new AreaQuery(Db))
{
var data3 = AreaSearch.Search(b);
var dataExport3 = data3.Records.Select(e => new tblM_Area
{
Area_PK = e.Area_PK,
Title = e.Title
}).ToList();
DataRow dr3;
startcell = 2; endcell = 2;
foreach (var item in dataExport3)
{
dr3 = validationTable3.NewRow();
dr3["AreaFK"] = item.Area_PK;
dr3["Area"] = item.Title;
validationTable3.Rows.Add(dr3);
endcell++;
}
var worksheet4 = workbook.AddWorksheet(validationTable3);
worksheet.Column(7).SetDataValidation().List(worksheet4.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
}
}
//worksheet.Column(5).SetDataValidation().List(worksheet2.Range("B2:B4"), true);
MemoryStream memoryStream = GetStream(workbook);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(memoryStream.ToArray())
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue
("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.Content.Headers.ContentDisposition =
new ContentDispositionHeaderValue("attachment")
{
FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
return(response);
}