private void PopulateDelegatesSheet(IXLWorkbook workbook, int centreId)
{
var delegateRecords = userDataService.GetDelegateUserCardsByCentreId(centreId);
var delegates = delegateRecords.OrderBy(x => x.LastName).Select(
x => new
{
x.LastName,
x.FirstName,
DelegateID = x.CandidateNumber,
AliasID = x.AliasId,
JobGroupID = x.JobGroupId,
x.Answer1,
x.Answer2,
x.Answer3,
x.Answer4,
x.Answer5,
x.Answer6,
x.Active,
x.EmailAddress,
HasPRN = GetHasPrnForDelegate(x.HasBeenPromptedForPrn, x.ProfessionalRegistrationNumber),
PRN = x.HasBeenPromptedForPrn ? x.ProfessionalRegistrationNumber : null,
}
);
ClosedXmlHelper.AddSheetToWorkbook(workbook, DelegatesSheetName, delegates, TableTheme);
}
private void PopulateJobGroupsSheet(IXLWorkbook workbook)
{
var jobGroups = jobGroupsDataService.GetJobGroupsAlphabetical()
.OrderBy(x => x.id)
.Select(
item => new { JobGroupID = item.id, JobGroupName = item.name }
);
ClosedXmlHelper.AddSheetToWorkbook(workbook, JobGroupsSheetName, jobGroups, TableTheme);
}
private static void FormatAllDelegateWorksheetColumns(IXLWorkbook workbook, DataTable dataTable)
{
ClosedXmlHelper.FormatWorksheetColumn(workbook, dataTable, RegisteredDate, XLDataType.DateTime);
var boolColumns = new[] { PasswordSet, Active, Approved, IsAdmin };
foreach (var columnName in boolColumns)
{
ClosedXmlHelper.FormatWorksheetColumn(workbook, dataTable, columnName, XLDataType.Boolean);
}
}
private void PopulateCourseDelegatesSheetForCourse(
IXLWorkbook workbook,
int customisationId,
int centreId,
string?sortBy,
string?filterString,
string sortDirection
)
{
var adminFields = courseAdminFieldsService.GetCourseAdminFieldsForCourse(customisationId);
var customRegistrationPrompts = registrationPromptsService.GetCentreRegistrationPromptsByCentreId(centreId);
var courseDelegates = courseDataService.GetDelegatesOnCourseForExport(customisationId, centreId)
.ToList();
var filteredCourseDelegates =
FilteringHelper.FilterItems(courseDelegates.AsQueryable(), filterString).ToList();
var sortedCourseDelegates =
GenericSortingHelper.SortAllItems(
filteredCourseDelegates.AsQueryable(),
sortBy ?? nameof(CourseDelegateForExport.FullNameForSearchingSorting),
sortDirection
);
var dataTable = new DataTable();
SetUpDataTableColumns(customRegistrationPrompts, adminFields, dataTable);
foreach (var courseDelegate in sortedCourseDelegates)
{
AddDelegateToDataTable(dataTable, courseDelegate, customRegistrationPrompts, adminFields);
}
if (dataTable.Rows.Count == 0)
{
var row = dataTable.NewRow();
dataTable.Rows.Add(row);
}
ClosedXmlHelper.AddSheetToWorkbook(
workbook,
$"Course {customisationId}",
dataTable.AsEnumerable(),
XLTableTheme.None
);
FormatWorksheetColumns(workbook, dataTable);
}
private XLWorkbook GetExcelFile(CustomersQueryViewModel cond = null)
{
List <CustomersViewModel> list = CustomerRepo.Search(cond.Query, cond.Paging, cond.Sort).Select(x => new CustomersViewModel()
{
Address = x.地址,
ClientName = x.客戶名稱,
CompanyNumber = x.統一編號,
CustomerTypeName = x.客戶類別.類別名稱,
Email = x.Email,
Fax = x.傳真,
Phone = x.電話,
Id = x.Id
}).ToList();
return(ClosedXmlHelper.ToClosedXmlExcel(list));
}
private static void FormatWorksheetColumns(IXLWorkbook workbook, DataTable dataTable)
{
var dateColumns = new[] { Enrolled, LastAccessed, CompleteBy, CompletedDate, RemovedDate };
foreach (var columnName in dateColumns)
{
ClosedXmlHelper.FormatWorksheetColumn(workbook, dataTable, columnName, XLDataType.DateTime);
}
var numberColumns = new[] { Logins, TimeMinutes, DiagnosticScore, AssessmentsPassed, PassRate };
foreach (var columnName in numberColumns)
{
ClosedXmlHelper.FormatWorksheetColumn(workbook, dataTable, columnName, XLDataType.Number);
}
var boolColumns = new[] { Active, Locked };
foreach (var columnName in boolColumns)
{
ClosedXmlHelper.FormatWorksheetColumn(workbook, dataTable, columnName, XLDataType.Boolean);
}
}
private void PopulateAllDelegatesSheet(
IXLWorkbook workbook,
int centreId,
string?searchString,
string?sortBy,
string sortDirection,
string?filterString
)
{
var registrationPrompts = centreRegistrationPromptsService.GetCentreRegistrationPromptsByCentreId(centreId);
var delegatesToExport = GetDelegatesToExport(centreId, searchString, sortBy, sortDirection, filterString)
.ToList();
var dataTable = new DataTable();
SetUpDataTableColumnsForAllDelegates(registrationPrompts, dataTable);
foreach (var delegateRecord in delegatesToExport)
{
SetDelegateRowValues(dataTable, delegateRecord, registrationPrompts);
}
if (dataTable.Rows.Count == 0)
{
var row = dataTable.NewRow();
dataTable.Rows.Add(row);
}
ClosedXmlHelper.AddSheetToWorkbook(
workbook,
AllDelegatesSheetName,
dataTable.AsEnumerable(),
XLTableTheme.None
);
FormatAllDelegateWorksheetColumns(workbook, dataTable);
}
private XLWorkbook GetExcelFile(CustomerContactsQueryViewModel cond = null)
{
List <CustomerContactViewModel> list = ContactsRepo.SearchAll(cond.Query);;
return(ClosedXmlHelper.ToClosedXmlExcel(list));
}
private XLWorkbook GetExcelFile()
{
List <CustomerReportViewModel> list = CustomerRepo.GetReport();
return(ClosedXmlHelper.ToClosedXmlExcel(list));
}
private XLWorkbook GetExcelFile()
{
return(ClosedXmlHelper.ToClosedXmlExcel(repo.GetCustomers()));
}
private XLWorkbook GetExcelFile(BankAccountQueryViewModel cond = null)
{
List <BankAccountViewModel> list = BankAccountsRepo.Search(cond.Query);
return(ClosedXmlHelper.ToClosedXmlExcel(list));
}
