/// <inheritdoc />
public IList <NotificationModel> GetNotificationsData(NotificationsSearchModel searchModel)
{
var notifications = new List <NotificationModel>();
using (var connection = new SqlConnection(Constants.ConnectorDatabase))
{
const string commandText = @"SELECT ED.Id,ED.EventCategoryId,ec.CategoryName,ED.CreatedTime,ED.IsMailSent,ED.Description,ED.LongDescription
FROM EventDetails ED
join EventCategories EC on ED.EventCategoryId=ec.Id
WHERE ED.IsMailSent =ISNULL(@IsMailSent,ED.IsMailSent)
AND ED.EventCategoryId =ISNULL(@EventCategoryId,ED.EventCategoryId)
AND ED.CreatedTime >=ISNULL(@CreatedTimeS,ED.CreatedTime)
AND ED.CreatedTime <=ISNULL(@CreatedTimeE,ED.CreatedTime)
order by ED.CreatedTime DESC";
using (var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddWithValue("@IsMailSent", searchModel.IsAll ? DBNull.Value : (object)searchModel.IsYes);
command.Parameters.AddWithValue("@EventCategoryId", searchModel.CategoryId == 0 ? DBNull.Value : (object)searchModel.CategoryId);
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())
{
notifications.Add(new NotificationModel
{
Index = i++,
Id = reader.GetInt64(0),
CategoryId = reader.GetInt32(1),
CategoryName = reader.GetString(2),
CreatedTime = reader.GetDateTime(3),
IsMailSent = reader.GetBoolean(4) ? "Yes" : "No",
Description = reader.GetString(5),
DetailDescription = reader.GetString(6),
ViewRecord = "View"
});
}
}
}
return(notifications);
}
/// <summary>
/// Sets the default filters.
/// </summary>
private void SetDefaultFilters()
{
SearchModel = new NotificationsSearchModel
{
CategoryId = 0,
IsAll = true,
FromDate = new DateModel
{
Date = DateTime.Today.AddDays(-7),
HasValue = true
},
ToDate = new DateModel
{
Date = DateTime.Today.AddDays(1),
HasValue = true
},
};
}
private void LoadData()
{
var searchModel = new NotificationsSearchModel
{
CategoryId = 0,
IsAll = true,
FromDate = new DateModel
{
Date = DateTime.Today,
HasValue = true
},
ToDate = new DateModel
{
Date = DateTime.Today.AddDays(1),
HasValue = true
},
};
RecordList = reportService.GetNotificationsData(searchModel);
RecordCount = RecordList.Count;
}