public IEnumerable <ExpiredUserListingDto> GetExcelReportForUsers(UserListingReports userInputs,
DateTime?fromDate, DateTime?toDate, string reportType)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT [Username], [FirstName] ,[LastName],[Email],[IsLockedOut],[AccountType],[StaffPosition] ,[AccountExpiryDate],[CreationDate],[LastLogInDate] FROM [User] ");
var user = GetUserType(reportType);
sql.Append(user);
var searchData = GetSearchParam(userInputs, fromDate, toDate, reportType);
sql.Append(searchData);
return(_context.Query <ExpiredUserListingDto>(sql.ToString()) as IEnumerable <ExpiredUserListingDto>);
}
public void ExportExcel(string reportType, string searchKey)
{
#region local variables declaration
DateTimeHelper dateTimeHelper = new DateTimeHelper();
string userName = Request.Form["dUserName"];
string firstName = Request.Form["dFirstName"];
string lastName = Request.Form["dLastName"];
string mail = Request.Form["dEmail"];
UserListingReports uiInputSearchParam = new UserListingReports()
{
UserName = userName, FirstName = firstName, LastName = lastName, Email = mail
};
DateTime?fromDate = dateTimeHelper.ConvertToDateTime(Request.Form["dfrom-date"]);
DateTime?toDate = dateTimeHelper.ConvertToDateTime(Request.Form["dto-date"], true);
#endregion
IEnumerable <ExpiredUserListingDto> excelData = _reportService.GetExcelReportForUsers(uiInputSearchParam, fromDate, toDate, reportType);
new Export().ToFile(excelData, Response, "UsersList_Report");
}
private static StringBuilder GetSearchParam(UserListingReports searchParam, DateTime?frDateTime, DateTime?toDateTime, string reportType)
{
StringBuilder sqlQuery = new StringBuilder(); DateTimeHelper dtHelper = new DateTimeHelper();
UserListingReports objSearchParam = new UserListingReports();
if (searchParam != null && reportType != "AllUsers")
{
sqlQuery.Append(" AND ( ([Username] LIKE " + "'%" + searchParam.UserName + "%' )" +
" AND " + "([FirstName] LIKE '%" + searchParam.FirstName + "%')" +
" AND ( [LastName] LIKE '%" + searchParam.LastName + "%')" +
" AND ([Email] LIKE '%" + searchParam.Email + "%'))");
if (frDateTime != null)
{
sqlQuery.Append(" And ([CreationDate] >= '" + dtHelper.FormatDateTime(frDateTime.ToString(), format: Constants.DateFormats.LongDateTimeHyphen) + "' ");
}
if (toDateTime != null)
{
sqlQuery.Append(" AND [CreationDate] <= '" + dtHelper.FormatDateTime(toDateTime.ToString(), format: Constants.DateFormats.LongDateTimeHyphen) + " ')");
}
}
else
{
sqlQuery.Append(" ( ([Username] LIKE " + "'%" + searchParam.UserName + "%' )" +
" AND " + "([FirstName] LIKE '%" + searchParam.FirstName + "%')" +
" AND ( [LastName] LIKE '%" + searchParam.LastName + "%')" +
" AND ([Email] LIKE '%" + searchParam.Email + "%'))");
if (frDateTime != null)
{
sqlQuery.Append(" And ([CreationDate] >= '" + dtHelper.FormatDateTime(frDateTime.ToString(), format: Constants.DateFormats.LongDateTimeHyphen) + "' ");
}
if (toDateTime != null)
{
sqlQuery.Append(" AND [CreationDate] <= '" + dtHelper.FormatDateTime(toDateTime.ToString(), format: Constants.DateFormats.LongDateTimeHyphen) + " ')");
}
}
return(sqlQuery);
}
public IEnumerable <ExpiredUserListingDto> GetExcelReportForUsers(UserListingReports userInputs,
DateTime?fromDate, DateTime?toDate, string reportType)
{
return(_reportsRepo.GetExcelReportForUsers(userInputs, fromDate, toDate, reportType));
}
