/// <summary>
/// Sets the default filters.
/// </summary>
private void SetDefaultFilters()
{
SearchModel = new ExclusionsSearchModel
{
EntityId = 0,
FromDate = new DateModel
{
Date = DateTime.Today.AddDays(-7),
HasValue = true
},
ToDate = new DateModel
{
Date = DateTime.Today.AddDays(1),
HasValue = true
},
};
}
/// <inheritdoc />
public IList <ExclusionModel> GetExclusionsData(ExclusionsSearchModel searchModel)
{
var exclusions = new List <ExclusionModel>();
using (var connection = new SqlConnection(Constants.ConnectorDatabase))
{
const string commandText = @"SELECT EX.Id,EX.RecordId,EX.CreatedTime,EX.IsJson,EX.Parameters, IE.EntityName
FROM Exclusions EX
JOIN EntityMaster IE ON ex.EntityId=IE.EntityId
WHERE EX.RecordId LIKE '%'+ISNULL(@RECORDID,EX.RecordId)+'%'
AND EX.CreatedTime >=ISNULL(@CREATEDTIMES,EX.CreatedTime)
AND EX.CreatedTime <=ISNULL(@CREATEDTIMEE,EX.CreatedTime)
order by EX.CreatedTime DESC";
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddWithValue("@ENTITYID", searchModel.EntityId == 0 ? DBNull.Value : (object)searchModel.EntityId);
command.Parameters.AddWithValue("@RECORDID", string.IsNullOrEmpty(searchModel.RecordNumber) ? DBNull.Value : (object)searchModel.RecordNumber);
command.Parameters.AddWithValue("@CREATEDTIMES", searchModel.FromDate.HasValue ? (object)searchModel.FromDate.Date : DBNull.Value);
command.Parameters.AddWithValue("@CREATEDTIMEE", searchModel.ToDate.HasValue ? (object)searchModel.ToDate.Date : DBNull.Value);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
int i = 1;
while (reader.Read())
{
exclusions.Add(new ExclusionModel
{
Index = i++,
Id = reader.GetInt64(0),
RecordId = reader.GetString(1),
CreatedTime = reader.GetDateTime(2),
IsJson = reader.GetBoolean(3),
Parameters = reader.GetString(4),
EntityName = reader.GetString(5)
});
}
}
}
return(exclusions);
}
