public async Task <LibraryBookPagedListResponse> GetLibraryBooksPaged(PagedBase filterParameters, bool listLostAndStolen)
{
List <KeyValuePair <string, string> > kvpList = KeyValuePairUtil.KeyValuePairFromPagedBase(filterParameters);
kvpList.Add(new KeyValuePair <string, string>("listLostAndStolen", listLostAndStolen.ToString()));
return(await GetJsonDecodedContent <LibraryBookPagedListResponse, LibraryBookPageApiModel>(_apiUrl + APIConstants.Paged, kvpList.ToArray()).ConfigureAwait(false));
}
public IActionResult Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "pagedlibrarybook")] HttpRequest req, ILogger log)
{
try
{
var result = _tokenProvider.ValidateToken(req);
if (result.Status == AccessTokenStatus.Valid)
{
log.LogInformation($"Request received for {result.Principal.Identity.Name}.");
}
else
{
return(new UnauthorizedResult());
}
var dicItems = req.GetQueryParameterDictionary();
PagedBase pagedBase = new PagedBase()
{
PageNum = Helper.GetIntValue(dicItems["PageNum"]),
PageSize = Helper.GetIntValue(dicItems["PageSize"]),
OrderBy = Helper.GetIntValue(dicItems["OrderBy"]),
SortOrder = Helper.GetIntValue(dicItems["SortOrder"]),
SearchText = dicItems["SortOrder"]
};
bool listLostAndStolen = Helper.GetBoolValue(dicItems["listLostAndStolen"]);
var librarybookpaged = _libraryBookWebApiManager.GetLibraryBooksPaged(pagedBase, listLostAndStolen, out int searchResultCount);
if (librarybookpaged == null)
{
return(new BadRequestObjectResult("Nothing found"));
}
return((ActionResult) new OkObjectResult(librarybookpaged));
}
catch (Exception ex)
{
log.LogError($"Caught exception: {ex.Message}");
return(new BadRequestObjectResult(ex.Message));
}
}
/// <summary>
/// GetLibraryBookStatussPaged for a sales location
/// </summary>
/// <param name="filterParameters"></param>
/// <param name="searchResultCount"></param>
/// <returns>The <see cref="LibraryBookStatusPageApiModel"/> </returns>
public virtual LibraryBookStatusPageApiModel GetLibraryBookStatusPaged(PagedBase filterParameters, out int searchResultCount)
{
LibraryBookStatusPageApiModel LibraryBookStatusPageApiModel = new LibraryBookStatusPageApiModel();
searchResultCount = 0;
IEnumerable <LibraryBookStatusListItem> LibraryBookStatuss = null;
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT C.LibraryBookStatusCode, C.DateCheckedOut, C.DateReturned,
C.DateCreated, C.CreatedBy, C.DateModified, C.ModifiedBy,
LB.LibraryBookCode, LB.ISBN, LB.Title, LB.Author, LB.IsStolen, LB.IsLost, LB.CopyNumber,
LB.DateCreated, LB.CreatedBy, LB.DateModified, LB.ModifiedBy,
LU.LibraryUserCode, LU.Title, LU.Name, LU.PhoneNumber, LU.MobilePhoneNumber, LU.Email, LU.AlternativePhoneNumber, LU.AlternativeEmail,
LU.AddressLine1, LU.AddressLine2, LU.AddressLine3, LU.City, LU.County, LU.Country, LU.Postcode,
LU.GDPRInformedDate,LU.GDPRInformedBy,LU.GDPRHowInformed,LU.GDPRNotes,LU.LibraryUserByPost,LU.LibraryUserByPostConsentDate,LU.LibraryUserByEmail,
LU.LibraryUserByEmailConsentDate,LU.LibraryUserByPhone,LU.LibraryUserByPhoneConsentDate,LU.LibraryUserBySMS,LU.LibraryUserBySMSConsentDate,
LU.DateCreated, LU.CreatedBy, LU.DateModified, LU.ModifiedBy,
( SELECT COUNT(*) FROM LibraryBookStatus ) AS 'TotalRows' FROM LibraryBookStatus C
INNER JOIN LibraryBook LB ON LB.LibraryBookId = C.LibraryBookId
INNER JOIN LibraryUser LU ON LU.LibraryUserId = C.LibraryBookUserId
WHERE
(
C.DateCheckedOut LIKE @searchText
OR C.DateReturned LIKE @searchText
OR LB.ISBN LIKE @searchText
OR LB.Title LIKE @searchText
OR LB.CopyNumber LIKE @searchText
OR LU.Name LIKE @searchText
OR LU.AddressLine1 LIKE @searchText
OR LU.Postcode LIKE @searchText
OR C.ModifiedBy LIKE @searchText
OR C.DateModified LIKE @searchText
)
order by
CASE WHEN @orderBy = 0 THEN C.DateCheckedOut END ASC,
CASE WHEN @orderBy = 1 AND @sortOrder = 0 THEN C.DateCheckedOut END DESC,
CASE WHEN @orderBy = 1 AND @sortOrder = 1 THEN C.DateCheckedOut END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN C.DateReturned END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN C.DateReturned END ASC,
CASE WHEN @orderBy = 3 AND @sortOrder = 0 THEN LB.ISBN END DESC,
CASE WHEN @orderBy = 3 AND @sortOrder = 1 THEN LB.ISBN END ASC,
CASE WHEN @orderBy = 4 AND @sortOrder = 0 THEN LB.Title END DESC,
CASE WHEN @orderBy = 4 AND @sortOrder = 1 THEN LB.Title END ASC,
CASE WHEN @orderBy = 5 AND @sortOrder = 0 THEN LB.CopyNumber END DESC,
CASE WHEN @orderBy = 5 AND @sortOrder = 1 THEN LB.CopyNumber END ASC,
CASE WHEN @orderBy = 6 AND @sortOrder = 0 THEN LU.Name END DESC,
CASE WHEN @orderBy = 6 AND @sortOrder = 1 THEN LU.Name END ASC,
CASE WHEN @orderBy = 7 AND @sortOrder = 0 THEN LU.AddressLine1 END DESC,
CASE WHEN @orderBy = 7 AND @sortOrder = 0 THEN LU.AddressLine2 END DESC,
CASE WHEN @orderBy = 7 AND @sortOrder = 0 THEN LU.AddressLine3 END DESC,
CASE WHEN @orderBy = 7 AND @sortOrder = 0 THEN LU.City END DESC,
CASE WHEN @orderBy = 7 AND @sortOrder = 0 THEN LU.County END ASC,
CASE WHEN @orderBy = 7 AND @sortOrder = 0 THEN LU.PostCode END DESC,
CASE WHEN @orderBy = 8 AND @sortOrder = 1 THEN LU.AddressLine1 END ASC,
CASE WHEN @orderBy = 8 AND @sortOrder = 1 THEN LU.AddressLine2 END ASC,
CASE WHEN @orderBy = 8 AND @sortOrder = 1 THEN LU.AddressLine3 END ASC,
CASE WHEN @orderBy = 8 AND @sortOrder = 1 THEN LU.City END ASC,
CASE WHEN @orderBy = 8 AND @sortOrder = 1 THEN LU.County END ASC,
CASE WHEN @orderBy = 8 AND @sortOrder = 1 THEN LU.PostCode END ASC,
CASE WHEN @orderBy = 9 AND @sortOrder = 0 THEN C.DateModified END DESC,
CASE WHEN @orderBy = 9 AND @sortOrder = 1 THEN C.DateModified END ASC
LIMIT @pageSize OFFSET @pageOffset");
var pageOffset = (filterParameters.PageSize * (filterParameters.PageNum - 1));
var parameters = new {
searchText = "%" + filterParameters.SearchText + "%",
pageOffset,
pageSize = filterParameters.PageSize,
orderBy = filterParameters.OrderBy,
sortOrder = filterParameters.SortOrder
};
try
{
using (var connection = this.OpenConnection())
{
LibraryBookStatuss = connection.Query <LibraryBookStatusListItem, LibraryBookApiModel, LibraryUserApiModel, LibraryBookStatusListItem>(sb.ToString(),
(lbs, lb, lu) =>
{
lbs.LibraryBook = lb;
lbs.LibraryUser = lu;
return(lbs);
},
splitOn: "LibraryBookCode,LibraryUserCode",
param: parameters,
commandType: CommandType.Text);
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
LibraryBookStatusPageApiModel.Results = LibraryBookStatuss.Select(x => new LibraryBookStatusApiModel
{
LibraryBookStatusCode = x.LibraryBookStatusCode,
DateCheckedOut = x.DateCheckedOut,
DateReturned = x.DateReturned,
LibraryBook = x.LibraryBook,
LibraryUser = x.LibraryUser,
CreatedBy = x.CreatedBy,
DateCreated = x.DateCreated,
ModifiedBy = x.ModifiedBy,
DateModified = x.DateModified,
}).ToList();
if (LibraryBookStatusPageApiModel.Results != null && LibraryBookStatusPageApiModel.Results.Count() > 0)
{
searchResultCount = LibraryBookStatuss.First().TotalRows;
LibraryBookStatusPageApiModel.SearchResultCount = searchResultCount;
}
return(LibraryBookStatusPageApiModel);
}
public async Task <LibraryBookStatusPagedListResponse> GetLibraryBookStatusPaged(PagedBase filterParameters)
{
List <KeyValuePair <string, string> > kvpList = KeyValuePairUtil.KeyValuePairFromPagedBase(filterParameters);
return(await GetJsonDecodedContent <LibraryBookStatusPagedListResponse, LibraryBookStatusPageApiModel>(_apiUrl + APIConstants.Paged, kvpList.ToArray()).ConfigureAwait(false));
}
public async Task <ActionResult> GetLibraryUsers(LibraryUserViewModel param)
{
int sortDirection = 0;
string strSortDirection = Request.Query["sSortDir_0"]; // asc or desc
if (strSortDirection == "asc")
{
sortDirection = 0;
}
else
{
sortDirection = 1;
}
int sortColumnIndex = Convert.ToInt32(Request.Query["iSortCol_0"]);
int pageNumber = (param.iDisplayStart / param.iDisplayLength) + 1;
PagedBase filterParameters = new PagedBase()
{
SearchText = param.sSearch,
PageNum = pageNumber,
PageSize = param.iDisplayLength,
OrderBy = sortColumnIndex,
SortOrder = sortDirection
};
LibraryUserPagedListResponse response = await _libraryUserClient.GetLibraryUsersPaged(filterParameters);
if (response.ResponseCode == HttpStatusCode.Unauthorized)
{
//return new HttpStatusCodeResult(HttpStatusCode.Unauthorized, Url.Action("Logout", "UserAccount", new { area = "" }));
return(new StatusCodeResult((int)HttpStatusCode.Unauthorized));
}
IEnumerable <string[]> result = new List <string[]>();
int TotalDisplayRecords = 0;
if (response.StatusIsSuccessful)
{
if (response.Data.Results != null)
{
result = from c in response.Data.Results
select new[] {
c.LibraryUserCode,
c.LibraryUserName,
c.Address,
c.PhoneNumber,
c.MobilePhoneNumber,
c.Email,
c.ModifiedBy,
c.DateModified.ToString()
};
TotalDisplayRecords = response.Data.Results.Count();
}
}
#pragma warning disable IDE0037
return(Json(new
{
sEcho = param.sEcho,
iTotalRecords = response.Data.SearchResultCount,
iTotalDisplayRecords = response.Data.SearchResultCount,
aaData = result
}));
#pragma warning restore IDE0037
}
public IEnumerable <User> GetUsersPaged(PagedBase filterParameters, string userName, bool showAllUsers, out int searchResultCount)
{
return(this.userRepository.GetUsersPaged(filterParameters, userName, showAllUsers, out searchResultCount));
}
public IActionResult GetLibraryUsersPaged(PagedBase filterParameters)
{
var contactPagedListApiModel = _libraryBookStatusWebApiManager.GetLibraryBookStatusPaged(filterParameters, out int searchResultCount);
return(Ok(contactPagedListApiModel));
}
/// <summary>
/// GetLibraryBookStatussPaged for a sales location
/// </summary>
/// <param name="filterParameters"></param>
/// <param name="searchResultCount"></param>
/// <returns>The <see cref="LibraryBookStatusPageApiModel"/> </returns>
public LibraryBookStatusPageApiModel GetLibraryBookStatusPaged(PagedBase filterParameters, out int searchResultCount)
{
return(_libraryBookStatusRepository.GetLibraryBookStatusPaged(filterParameters, out searchResultCount));
}
/// <summary>
/// GetLibraryUsersPaged for a sales location
/// </summary>
/// <param name="filterParameters"></param>
/// <param name="searchResultCount"></param>
/// <returns>The <see cref="LibraryUserPageApiModel"/> </returns>
public LibraryUserPageApiModel GetLibraryUsersPaged(PagedBase filterParameters, out int searchResultCount)
{
return(this._libraryUserRepository.GetLibraryUsersPaged(filterParameters, out searchResultCount));
}
public virtual IEnumerable <User> GetUsersPaged(PagedBase filterParameters, string userName, bool showAllUsers, out int searchResultCount)
{
searchResultCount = 0;
IEnumerable <UserListItem> userListitems = null;
var pageOffset = (filterParameters.PageSize * (filterParameters.PageNum - 1));
string aspNetUsers = " [Identity].AspNetUsers ";
if (string.Equals(_apiConfiguration.Value.RDBMS, "MySQL", StringComparison.OrdinalIgnoreCase))
{
aspNetUsers = " AspNetUsers ";
}
int companyId = 0;
using (var connection = this.OpenConnection())
{
companyId = connection.Query <int>(
string.Format(@"SELECT C.CompanyId FROM Company C
INNER JOIN {0} U ON U.UserName = @userName AND C.CompanyId = U.CompanyId", aspNetUsers), new { userName }
).SingleOrDefault();
}
var parameters = new
{
userName,
companyId,
searchText = "%" + filterParameters.SearchText + "%",
pageOffset,
pageSize = filterParameters.PageSize,
orderBy = filterParameters.OrderBy,
sortOrder = filterParameters.SortOrder
};
string sql = null;
if (IsUserInRole("Administrator", userName))
{
if (showAllUsers)
{
sql = string.Format(@"SELECT U1.UserName, U1.Email As 'EmailAddress',
(SELECT COUNT(*) FROM {0} U2
INNER JOIN Company C1 ON U2.CompanyId = C1.CompanyId
LEFT OUTER JOIN Branch B ON U2.BranchId = B.BranchId) AS 'TotalRows', B.BranchCode, B.BranchName
FROM {1} U1
INNER JOIN Company C ON U1.CompanyId = C.CompanyId
LEFT OUTER JOIN Branch B ON U1.BranchId = B.BranchId
WHERE
U1.UserName LIKE @searchText
AND
(
U1.Email LIKE @searchText
OR B.BranchName LIKE @searchText
)
order by
CASE WHEN @orderBy = 0 AND @sortOrder = 0 THEN U1.UserName END ASC,
CASE WHEN @orderBy = 0 AND @sortOrder = 1 THEN U1.UserName END DESC,
CASE WHEN @orderBy = 1 AND @sortOrder = 0 THEN U1.Email END ASC,
CASE WHEN @orderBy = 1 AND @sortOrder = 1 THEN U1.Email END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN B.BranchName END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN B.BranchName END DESC " , aspNetUsers, aspNetUsers);
}
else
{
sql = string.Format(@"SELECT U1.UserName, U1.Email As 'EmailAddress',
(SELECT COUNT(*) FROM {0} U2
INNER JOIN Company C1 ON U2.CompanyId = C1.CompanyId
LEFT OUTER JOIN Branch B ON U2.BranchId = B.BranchId
WHERE U2.CompanyId = @companyId) AS 'TotalRows', B.BranchCode, B.BranchName
FROM {1} U1
INNER JOIN Company C ON U1.CompanyId = C.CompanyId
LEFT OUTER JOIN Branch B ON U1.BranchId = B.BranchId
WHERE
U1.CompanyId = @companyId AND U1.UserName LIKE @searchText
AND
(
U1.Email LIKE @searchText
OR B.BranchName LIKE @searchText
)
order by
CASE WHEN @orderBy = 0 AND @sortOrder = 0 THEN U1.UserName END ASC,
CASE WHEN @orderBy = 0 AND @sortOrder = 1 THEN U1.UserName END DESC,
CASE WHEN @orderBy = 1 AND @sortOrder = 0 THEN U1.Email END ASC,
CASE WHEN @orderBy = 1 AND @sortOrder = 1 THEN U1.Email END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN B.BranchName END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN B.BranchName END DESC " , aspNetUsers, aspNetUsers);
}
}
else
{
string aspNetUserRoles = " [Identity].AspNetUserRoles ";
string aspNetRoles = " [Identity].AspNetRoles ";
sql = string.Format(@"SELECT U1.UserName, U1.Email 'EmailAddress',
(SELECT COUNT(*) FROM {0} U2
LEFT OUTER JOIN Branch B ON U2.BranchId = B.BranchId
INNER JOIN {1} UR ON UR.UserId = U2.Id
INNER JOIN {2} R ON R.Id = UR.RoleId
WHERE U2.CompanyId = @companyId AND R.Name <> 'Administrator') AS 'TotalRows', B.BranchCode, B.BranchName
FROM {3} U1
LEFT OUTER JOIN Branch B ON U1.BranchId = B.BranchId
INNER JOIN {4} UR ON UR.UserId = U1.Id
INNER JOIN {5} R ON R.Id = UR.RoleId
WHERE U1.CompanyId = @companyId AND R.Name <> 'Administrator'
AND
(
U1.UserName LIKE @searchText
OR U1.Email LIKE @searchText
OR B.BranchName LIKE @searchText
)
order by
CASE WHEN @orderBy = 0 AND @sortOrder = 0 THEN U1.UserName END ASC,
CASE WHEN @orderBy = 0 AND @sortOrder = 1 THEN U1.UserName END DESC,
CASE WHEN @orderBy = 1 AND @sortOrder = 0 THEN U1.Email END ASC,
CASE WHEN @orderBy = 1 AND @sortOrder = 1 THEN U1.Email END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN B.BranchName END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN B.BranchName END DESC " , aspNetUsers, aspNetUserRoles, aspNetRoles, aspNetUsers, aspNetUserRoles, aspNetRoles);
}
sql += @"OFFSET @pageOffset ROWS
FETCH NEXT @pageSize ROWS ONLY";
using (var connection = this.OpenConnection())
{
userListitems = connection.Query <UserListItem>(
sql,
param: parameters,
commandType: CommandType.Text);
}
if (userListitems != null && userListitems.Count() > 0)
{
searchResultCount = userListitems.First().TotalRows;
}
IEnumerable <User> users = userListitems.Select(x => new User
{
UserName = x.UserName,
EmailAddress = x.EmailAddress,
//Branch = x.Branch
}).ToList();
return(users);
}
/// <summary>
/// GetLibraryUsersPaged for a sales location
/// </summary>
/// <param name="filterParameters"></param>
/// <param name="searchResultCount"></param>
/// <returns>The <see cref="LibraryUserPageApiModel"/> </returns>
public virtual LibraryUserPageApiModel GetLibraryUsersPaged(PagedBase filterParameters, out int searchResultCount)
{
LibraryUserPageApiModel LibraryUserPageApiModel = new LibraryUserPageApiModel();
searchResultCount = 0;
IEnumerable <LibraryUserListItem> LibraryUsers = null;
string sql = @"SELECT C.LibraryUserCode, C.Title, C.Name, C.PhoneNumber, C.MobilePhoneNumber, C.Email, C.AlternativePhoneNumber, C.AlternativeEmail, C.AddressLine1, C.AddressLine2, C.AddressLine3, C.City, C.County, C.Country, C.Postcode, C.ModifiedBy, C.DateModified,
( SELECT COUNT(*) FROM LibraryUser) AS 'TotalRows' FROM LibraryUser C
WHERE
(
CONCAT(C.Title, ' ', C.Name) LIKE @searchText
OR C.Name LIKE @searchText
OR C.AddressLine1 LIKE @searchText
OR C.City LIKE @searchText
OR C.County LIKE @searchText
OR C.PostCode LIKE @searchText
OR C.PhoneNumber LIKE @searchText
OR C.MobilePhoneNumber LIKE @searchText
OR C.Email LIKE @searchText
OR C.ModifiedBy LIKE @searchText
OR C.DateModified LIKE @searchText
)
order by
CASE WHEN @orderBy = 0 THEN C.Name END ASC,
CASE WHEN @orderBy = 1 AND @sortOrder = 0 THEN C.Name END DESC,
CASE WHEN @orderBy = 1 AND @sortOrder = 1 THEN C.Name END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN C.AddressLine1 END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN C.AddressLine2 END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN C.AddressLine3 END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN C.City END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN C.County END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN C.PostCode END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN C.AddressLine1 END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN C.AddressLine2 END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN C.AddressLine3 END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN C.City END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN C.County END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN C.PostCode END ASC,
CASE WHEN @orderBy = 3 AND @sortOrder = 0 THEN C.PhoneNumber END DESC,
CASE WHEN @orderBy = 3 AND @sortOrder = 1 THEN C.PhoneNumber END ASC,
CASE WHEN @orderBy = 4 AND @sortOrder = 0 THEN C.MobilePhoneNumber END DESC,
CASE WHEN @orderBy = 4 AND @sortOrder = 1 THEN C.MobilePhoneNumber END ASC,
CASE WHEN @orderBy = 5 AND @sortOrder = 0 THEN C.Email END DESC,
CASE WHEN @orderBy = 5 AND @sortOrder = 1 THEN C.Email END ASC,
CASE WHEN @orderBy = 6 AND @sortOrder = 0 THEN C.ModifiedBy END DESC,
CASE WHEN @orderBy = 6 AND @sortOrder = 1 THEN C.ModifiedBy END ASC,
CASE WHEN @orderBy = 7 AND @sortOrder = 0 THEN C.DateModified END DESC,
CASE WHEN @orderBy = 7 AND @sortOrder = 1 THEN C.DateModified END ASC
LIMIT @pageSize OFFSET @pageOffset";
var pageOffset = (filterParameters.PageSize * (filterParameters.PageNum - 1));
using (var connection = this.OpenConnection())
{
LibraryUsers = connection.Query <LibraryUserListItem>(sql, new
{
searchText = "%" + filterParameters.SearchText + "%",
pageOffset,
pageSize = filterParameters.PageSize,
orderBy = filterParameters.OrderBy,
sortOrder = filterParameters.SortOrder
});
}
LibraryUserPageApiModel.Results = LibraryUsers.Select(x => new LibraryUserApiModel
{
LibraryUserCode = x.LibraryUserCode,
Title = x.Title,
Name = x.Name,
AddressLine1 = x.AddressLine1,
AddressLine2 = x.AddressLine2,
AddressLine3 = x.AddressLine3,
City = x.City,
County = x.County,
Country = x.Country,
Postcode = x.Postcode,
PhoneNumber = x.PhoneNumber,
MobilePhoneNumber = x.MobilePhoneNumber,
Email = x.Email,
AlternativePhoneNumber = x.AlternativePhoneNumber,
AlternativeEmail = x.AlternativeEmail,
ModifiedBy = x.ModifiedBy,
DateModified = x.DateModified,
}).ToList();
if (LibraryUserPageApiModel.Results != null && LibraryUserPageApiModel.Results.Count() > 0)
{
searchResultCount = LibraryUsers.First().TotalRows;
LibraryUserPageApiModel.SearchResultCount = searchResultCount;
}
return(LibraryUserPageApiModel);
}
public static List <KeyValuePair <string, string> > KeyValuePairFromPagedBase(PagedBase filterParameters)
{
List <KeyValuePair <string, string> > kvpList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("searchText", filterParameters.SearchText),
new KeyValuePair <string, string>("pageNum", filterParameters.PageNum.ToString()),
new KeyValuePair <string, string>("pageSize", filterParameters.PageSize.ToString()),
new KeyValuePair <string, string>("orderBy", filterParameters.OrderBy.ToString()),
new KeyValuePair <string, string>("sortOrder", filterParameters.SortOrder.ToString())
};
if (filterParameters.FooterFilters != null)
{
foreach (string footerFilter in filterParameters.FooterFilters)
{
kvpList.Add(new KeyValuePair <string, string>("footerFilters", footerFilter));
}
}
return(kvpList);
}
/// <summary>
/// GetLibraryBooksPaged for a sales location
/// </summary>
/// <param name="filterParameters"></param>
/// <param name="listLostAndStolen"></param>
/// <param name="searchResultCount"></param>
/// <returns>The <see cref="LibraryBookPageApiModel"/> </returns>
public virtual LibraryBookPageApiModel GetLibraryBooksPaged(PagedBase filterParameters, bool listLostAndStolen, out int searchResultCount)
{
LibraryBookPageApiModel LibraryBookPageApiModel = new LibraryBookPageApiModel();
searchResultCount = 0;
IEnumerable <LibraryBookListItem> LibraryBooks = null;
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT C.LibraryBookCode, C.ISBN, C.Title, C.Author, C.IsStolen, C.IsLost, C.CopyNumber, C.DateCreated, C.CreatedBy, C.DateModified, C.ModifiedBy,
( SELECT COUNT(*) FROM LibraryBook ) AS 'TotalRows' FROM LibraryBook C
WHERE " );
if (!listLostAndStolen)
{
sb.Append(" C.IsStolen = 0 AND C.IsLost = 0 ");
sb.Append(@" AND ");
}
sb.Append(@"
(
C.ISBN LIKE @searchText
OR C.Title LIKE @searchText
OR C.Author LIKE @searchText
OR C.IsStolen LIKE @searchText
OR C.IsLost LIKE @searchText
OR C.CopyNumber LIKE @searchText
OR C.ModifiedBy LIKE @searchText
OR C.DateModified LIKE @searchText
)
order by
CASE WHEN @orderBy = 0 THEN C.DateModified END ASC,
CASE WHEN @orderBy = 1 AND @sortOrder = 0 THEN C.ISBN END DESC,
CASE WHEN @orderBy = 1 AND @sortOrder = 1 THEN C.ISBN END ASC,
CASE WHEN @orderBy = 2 AND @sortOrder = 0 THEN C.Title END DESC,
CASE WHEN @orderBy = 2 AND @sortOrder = 1 THEN C.Title END ASC,
CASE WHEN @orderBy = 3 AND @sortOrder = 0 THEN C.Author END DESC,
CASE WHEN @orderBy = 3 AND @sortOrder = 1 THEN C.Author END ASC,
CASE WHEN @orderBy = 4 AND @sortOrder = 0 THEN C.IsStolen END DESC,
CASE WHEN @orderBy = 4 AND @sortOrder = 1 THEN C.IsStolen END ASC,
CASE WHEN @orderBy = 5 AND @sortOrder = 0 THEN C.IsLost END DESC,
CASE WHEN @orderBy = 5 AND @sortOrder = 1 THEN C.IsLost END ASC,
CASE WHEN @orderBy = 6 AND @sortOrder = 0 THEN C.CopyNumber END DESC,
CASE WHEN @orderBy = 6 AND @sortOrder = 1 THEN C.CopyNumber END ASC,
CASE WHEN @orderBy = 7 AND @sortOrder = 0 THEN C.ModifiedBy END DESC,
CASE WHEN @orderBy = 7 AND @sortOrder = 1 THEN C.ModifiedBy END ASC,
CASE WHEN @orderBy = 8 AND @sortOrder = 0 THEN C.DateModified END DESC,
CASE WHEN @orderBy = 8 AND @sortOrder = 1 THEN C.DateModified END ASC
LIMIT @pageSize OFFSET @pageOffset ");
var pageOffset = (filterParameters.PageSize * (filterParameters.PageNum - 1));
using (var connection = this.OpenConnection())
{
LibraryBooks = connection.Query <LibraryBookListItem>(sb.ToString(), new
{
searchText = "%" + filterParameters.SearchText + "%",
pageOffset,
pageSize = filterParameters.PageSize,
orderBy = filterParameters.OrderBy,
sortOrder = filterParameters.SortOrder
});
}
LibraryBookPageApiModel.Results = LibraryBooks.Select(x => new LibraryBookApiModel
{
LibraryBookCode = x.LibraryBookCode,
ISBN = x.ISBN,
Title = x.Title,
Author = x.Author,
IsStolen = x.IsStolen,
IsLost = x.IsLost,
CopyNumber = x.CopyNumber,
CreatedBy = x.CreatedBy,
DateCreated = x.DateCreated,
ModifiedBy = x.ModifiedBy,
DateModified = x.DateModified,
}).ToList();
if (LibraryBookPageApiModel.Results != null && LibraryBookPageApiModel.Results.Count() > 0)
{
searchResultCount = LibraryBooks.First().TotalRows;
LibraryBookPageApiModel.SearchResultCount = searchResultCount;
}
return(LibraryBookPageApiModel);
}
/// <summary>
/// GetLibraryBooksPaged for a sales location
/// </summary>
/// <param name="filterParameters"></param>
/// <param name="userName"></param>
/// <param name="searchResultCount"></param>
/// <returns>The <see cref="LibraryBookPageApiModel"/> </returns>
public LibraryBookPageApiModel GetLibraryBooksPaged(PagedBase filterParameters, bool listLostAndStolen, out int searchResultCount)
{
return(this._libraryBookRepository.GetLibraryBooksPaged(filterParameters, listLostAndStolen, out searchResultCount));
}
public IActionResult GetLibraryBooksPaged(PagedBase filterParameters, bool listLostAndStolen)
{
var booksPagedListApiModel = _libraryBookWebApiManager.GetLibraryBooksPaged(filterParameters, listLostAndStolen, out int searchResultCount);
return(Ok(booksPagedListApiModel));
}