public void FetchEssentialAssets_FilterValueWithPercentageWildCardOperators_ValidQuery()
{
AssetSettingsListRequestDto request = new AssetSettingsListRequestDto
{
CustomerUid = Guid.NewGuid().ToString("N"),
UserUid = Guid.NewGuid().ToString("N"),
PageNumber = 1,
PageSize = 10,
SortColumn = "AssetId",
SortDirection = "ASC",
FilterName = "AssetId",
FilterValue = "%"
};
_stubTransactions
.GetMultipleResultSetAsync <AssetSettingsListDto, long>(Arg.Any <string>(), Arg.Any <object>())
.ReturnsForAnyArgs(x =>
{
return(new Tuple <IEnumerable <AssetSettingsListDto>, IEnumerable <long> >(
new List <AssetSettingsListDto>(), new List <long> {
0
}));
});
var values = _assetSettingsListRepository.FetchEssentialAssets(request).Result;
var queryExpected = string.Format(Queries.FetchAssetsForCustomerAndUserUId + Queries.SelectFoundRows,
(request.PageNumber > 0) ? string.Format(Queries.LimitClause, (request.PageNumber - 1) * request.PageSize, request.PageSize) : string.Empty, // Limit offset and limit page size
string.Format(Queries.OrderByClause, Constants.AssetSettingsSortConfig[(AssetSettingsSortColumns)Enum.Parse(typeof(AssetSettingsSortColumns), request.SortColumn)], request.SortDirection), // order by clause
string.Format("AND a.AssetName LIKE '%{0}%'", @"\%"),
request.DeviceType);
_stubTransactions.Received(1).GetMultipleResultSetAsync <AssetSettingsListDto, long>(Arg.Is <string>(queryExpected), Arg.Any <dynamic>());
}
public object GetAssetsforSupportUser(string searchString, int pageNum, int pageLimit)
{
var assets = new List <object>();
var assetsToBeReturned = new DbModel.AssetDeviceForSupportUserListD();
int rowToStart = (pageNum - 1) * pageLimit;
int noOfRows = pageLimit;
if (searchString != null)
{
searchString = MySqlHelper.EscapeString(searchString);
}
var query = $"SELECT DISTINCT " +
$"HEX(a.AssetUID) AS AssetUID, " +
$"a.AssetName AS AssetName, " +
$"a.SerialNumber AS AssetSerialNumber, " +
$"a.MakeCode AS AssetMakeCode, " +
$"IFNULL(HEX(d.DeviceUID), ' - ') AS DeviceUID, " +
$"IFNULL(d.SerialNumber, ' - ') AS DeviceSerialNumber, " +
$"IFNULL(dt.TypeName, ' - ') AS DeviceType " +
$"FROM " +
$"md_asset_Asset a " +
$"LEFT OUTER JOIN " +
$"md_asset_AssetDevice ad ON a.AssetUID = ad.fk_AssetUID " +
$"LEFT OUTER JOIN " +
$"md_device_Device d ON d.DeviceUID = ad.fk_DeviceUID " +
$"JOIN " +
$"md_device_DeviceType dt ON dt.DeviceTypeID = d.fk_DeviceTypeID " +
$"WHERE " +
$"a.StatusInd = 1 " +
$"AND (a.AssetName LIKE '%{searchString}%' " +
$"OR a.SerialNumber LIKE '%{searchString}%') " +
$"UNION SELECT DISTINCT " +
$"IFNULL(HEX(a.AssetUID), ' - ') AS AssetUID, " +
$"IFNULL(a.AssetName, ' - ') AS AssetName, " +
$"IFNULL(a.SerialNumber, ' - ') AS AssetSerialNumber, " +
$"IFNULL(a.MakeCode, ' - ') AS AssetMakeCode, " +
$"HEX(d.DeviceUID) AS DeviceUID, " +
$"d.SerialNumber AS DeviceSerialNumber, " +
$"dt.TypeName AS DeviceType " +
$"FROM " +
$"md_device_Device d " +
$"LEFT OUTER JOIN " +
$"md_asset_AssetDevice ad ON d.DeviceUID = ad.fk_DeviceUID " +
$"LEFT OUTER JOIN " +
$"md_asset_Asset a ON a.AssetUID = ad.fk_AssetUID " +
$"AND a.StatusInd = 1 " +
$"JOIN " +
$"md_device_DeviceType dt ON dt.DeviceTypeID = d.fk_DeviceTypeID " +
$"WHERE " +
$"d.SerialNumber LIKE '%{searchString}%' " +
$"ORDER BY AssetSerialNumber " +
$"LIMIT {rowToStart}, {noOfRows}; " +
$"SELECT FOUND_ROWS();";
try
{
var resultSet = _transaction.GetMultipleResultSetAsync <dynamic, int>(query, null).Result;
var lstAssets = resultSet.Item1 as IList <dynamic>;
var assetFoundRows = (resultSet.Item2 as IList <int>);
for (int i = 0; i < lstAssets.Count; i++)
{
AssetDeviceForSupportUserD asset = new AssetDeviceForSupportUserD();
asset.AssetMakeCode = lstAssets[i].AssetMakeCode != null ? lstAssets[i].AssetMakeCode.ToString() : null;
asset.AssetName = lstAssets[i].AssetName != null ? lstAssets[i].AssetName.ToString() : null;
asset.AssetSerialNumber = lstAssets[i].AssetSerialNumber != null ? lstAssets[i].AssetSerialNumber.ToString() : null;
asset.AssetUID = lstAssets[i].AssetUID != null && lstAssets[i].AssetUID.ToString().Trim() != "-" ? (Guid?)new Guid(lstAssets[i].AssetUID.ToString()) : null;
asset.DeviceSerialNumber = lstAssets[i].DeviceSerialNumber != null ? lstAssets[i].DeviceSerialNumber.ToString() : null;
asset.DeviceType = lstAssets[i].DeviceType != null ? lstAssets[i].DeviceType.ToString() : null;
asset.DeviceUID = lstAssets[i].DeviceUID != null && lstAssets[i].DeviceUID.ToString().Trim() != "-" ? (Guid?)new Guid(lstAssets[i].DeviceUID.ToString()) : null;
assets.Add(asset);
}
assetsToBeReturned.AssetDevices = assets.Select(x => (AssetDeviceForSupportUserD)x).ToList();
assetsToBeReturned.TotalNumberOfPages = (int)Math.Ceiling(Convert.ToDouble(Convert.ToDouble(assetFoundRows.FirstOrDefault()) / pageLimit));
assetsToBeReturned.PageNumber = assetsToBeReturned.TotalNumberOfPages > 0 ? pageNum : 0;
}
catch (Exception ex)
{
_logger.LogError($"{ex}");
throw;
}
finally
{
}
return(assetsToBeReturned);
}
