All the code is in the Rails project.
File 1 :
using ATG.Rails.Repository.Entities.C3;
namespace ATG.Rails.Repository.Helpers.Interfaces
{
public interface IC3ContextFactory
{
/// <summary>
/// Generates a context for the Unit of Work pattern on the C3 database
/// </summary>
C3Context GetC3DatabaseContext(int branchId);
}
}
Getting the context:
using ATG.Rails.Common.Utils;
using ATG.Rails.Logic.Services.Interfaces;
using ATG.Rails.Repository.Helpers.Interfaces;
using Microsoft.Extensions.Logging;
namespace ATG.Rails.Logic.Services
{
/// <summary>
/// Responsible for returning the appropriate service.
/// </summary>
public class FileFactoryService : BaseService, IFileFactoryService
{
private readonly IC3ContextFactory _c3ContextFactory;
private readonly IEmployeeService _employeeService;
/// <summary>
/// Constructor for FileFactory Service
/// </summary>
/// <param name="employeeService">The employee service</param>
/// <param name="c3ContextFactory">Context to C3 Factory</param>
/// <param name="logger">The logger where messages are logged.</param>
public FileFactoryService(IEmployeeService employeeService, IC3ContextFactory c3ContextFactory,
ILogger<FileFactoryService> logger) : base(logger)
{
ArgumentValidator.ThrowOnNull("c3ContextFactory", c3ContextFactory, "FileFactoryService");
ArgumentValidator.ThrowOnNull("employeeService", employeeService, "FileFactoryService");
_c3ContextFactory = c3ContextFactory;
_employeeService = employeeService;
}
/// <summary>
/// Responsible for returning the appropriate service.
/// </summary>
/// <param name="serviceType"></param>
/// <returns>The service that is used for further processing</returns>
public IValidationService GetService(string serviceType)
{
// fail fast here
switch (serviceType)
{
case "Employee":
return _employeeService;
default:
return null;
}
}
}
}
file 3:
using ATG.Rails.Common.Params;
using ATG.Rails.Common.Utils;
using ATG.Rails.Logic.Services.Interfaces;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Swashbuckle.SwaggerGen.Annotations;
namespace ATG.Rails.API.Controllers
{
/// <summary>
/// Allows user to post files that need to be processed.
/// </summary>
[ApiVersion("1.0")]
public class FileUploadController : BaseController
{
private readonly IFileFactoryService _fileFactoryService;
/// <summary>
/// Responsible for allowing user to post excel files for further processing
/// </summary>
/// <param name="fileFactoryService"></param>
/// <param name="logger"></param>
public FileUploadController(IFileFactoryService fileFactoryService,
ILogger<FileUploadController> logger) : base(logger)
{
ArgumentValidator.ThrowOnNull("fileFactoryService", fileFactoryService);
_fileFactoryService = fileFactoryService;
}
/// <summary>
/// Allows processing for file with branch code, template and actual excel file.
/// </summary>
/// <param name="fileProcessingParams">File processing parameters <see cref="FileProcessingParams" /></param>
/// <returns>Returns a standard file content with processing information about the file. For more information <see cref="FileContentResult"/></returns>
[HttpPost]
[Route("/api/v1.0/FileUpload")]
[SwaggerOperation("Post")]
[Produces(typeof(FileContentResult))]
public IActionResult Post(FileProcessingParams fileProcessingParams)
{
_logger.LogTrace("Entering FileUploadController.Post");
var service = _fileFactoryService.GetService(fileProcessingParams.TemplateName);
var fileResult = ProcessResponse(service.ProcessFile(fileProcessingParams));
_logger.LogTrace("Exiting FileUploadController.Post");
return fileResult;
}
}
}
File 4:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ATG.Rails.Repository.Caches.Interfaces;
using ATG.Rails.Repository.Dtos;
using ATG.Rails.Repository.Entities.C3;
using ATG.Rails.Repository.Entities.Soar;
using ATG.Rails.Repository.Helpers.Interfaces;
using Microsoft.EntityFrameworkCore;
using System.Linq;
namespace ATG.Rails.Repository.Helpers
{
public class C3ContextFactory : IC3ContextFactory
{
#region Member Variables
/// <summary>
/// Key for cache of C3Map
/// </summary>
private const string _c3MapKey = "C3Map";
/// <summary>
/// Hours to keep cache of C3Map
/// </summary>
private const int _c3Hours = 24;
#endregion
private readonly IMappingCache _mappingCache;
private AssociaSoarContext _associaSoarContext;
public C3ContextFactory(IMappingCache mappingCache, AssociaSoarContext associaSoarContext){
_mappingCache = mappingCache;
_associaSoarContext = associaSoarContext;
}
/// <summary>
/// Handles the cache and database interaction to get the Branch -> C3 mapping
/// </summary>
private Dictionary<int, C3Connection> GetBranchToC3Mapping()
{
var map = _mappingCache.GetItem<Dictionary<int, C3Connection>>(_c3MapKey);
if (map == null)
{
using (var context = _associaSoarContext)
{
map = GetBranchToC3MappingFromContext();
if (map != null)
{
_mappingCache.SaveItem(_c3MapKey, map, _c3Hours);
}
}
}
return map;
}
/// <summary>
/// Retrieves a C3 instance connection string for a given Branch
/// </summary>
/// <param name="branchId">ID corresponding to the Branch identifier in SOAR</param>
/// <returns></returns>
private C3Connection GetC3InstanceForBranch(int branchId)
{
var map = GetBranchToC3Mapping();
if (map != null && map.ContainsKey(branchId))
{
return map[branchId];
}
throw new Exception("Unable to get mapping for C3 Instance");
}
#region Database Calls
/// <summary>
/// Calls into the SOAR database to get the Branch -> C3 mapping
/// </summary>
private Dictionary<int, C3Connection> GetBranchToC3MappingFromContext()
{
try
{
var mappings = _associaSoarContext.LoadConfiguration.ToList();
var map = new Dictionary<int, C3Connection>();
foreach (LoadConfiguration mapping in mappings)
{
if (!map.ContainsKey(mapping.BranchId))
{
map.Add(mapping.BranchId, new C3Connection
{
DataSource = mapping.DataSource,
InitialCatalog = mapping.InitialCatalog
});
}
}
return map;
} catch(Exception e)
{
throw e;
}
}
#endregion
#region IDbContextFactory Implementation
/// <summary>
/// Generates a context for the Unit of Work pattern on the C3 database
/// </summary>
public C3Context GetC3DatabaseContext(int branchId)
{
var connection = GetC3InstanceForBranch(branchId);
var templateString = "Server =.; initial catalog = c3_ntx_uat_restore; integrated security = True; MultipleActiveResultSets = True; App = EntityFramework";
var sqlCsBuilder = new SqlConnectionStringBuilder(templateString)
{
DataSource = connection.DataSource,
InitialCatalog = connection.InitialCatalog
};
var providerConnectionString = sqlCsBuilder.ToString();
string contextConnectionString = providerConnectionString;
var optionsBuilder = new DbContextOptionsBuilder<C3Context>();
optionsBuilder.UseSqlServer(contextConnectionString);
return new C3Context(optionsBuilder.Options);
}
#endregion
}
}
-------------------- This is the service code. ---------------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Linq;
using ATG.Rails.Common.Params;
using ATG.Rails.Common.Utils;
using ATG.Rails.Logic.Infrastructure;
using ATG.Rails.Logic.Interpreters.Interfaces;
using ATG.Rails.Logic.Services.Interfaces;
using ATG.Rails.Logic.ViewModels;
using ATG.Rails.Repository.Entities.Rails;
using ATG.Rails.Repository.Helpers.Interfaces;
using ATG.Rails.Repository.Repositories.C3.Interfaces;
using ATG.Rails.Repository.Repositories.Rails.Interfaces;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using OfficeOpenXml;
namespace ATG.Rails.Logic.Services
{
/// <summary>
/// Service responsible for saving Employee data to database.
/// </summary>
public class EmployeeService : BaseService, IEmployeeService, IValidateDataInColumns<EmployeeViewModel>
{
private readonly IEmployeeStagingRepository _employeeStagingRepository;
private readonly IEmployeeViewModelInterpreter _employeeViewModelInterpreter;
private readonly IHostingEnvironment _hostingEnvironment;
private readonly ITemplateColumnService _templateColumnService;
private readonly IUploadStatusRepository _uploadStatusRepository;
private readonly IC3ContextFactory _c3ContextFactory;
private List<string> _messages = new List<string>();
public EmployeeService(IHostingEnvironment hostingEnvironment,
ITemplateColumnService templateColumnService,
IEmployeeStagingRepository employeeStagingRepository,
IEmployeeViewModelInterpreter employeeViewModelInterpreter,
IUploadStatusRepository uploadStatusRepository,
IC3ContextFactory c3ContextFactory,
ILogger<EmployeeService> logger) : base(logger)
{
ArgumentValidator.ThrowOnNull("hostingEnvironment", hostingEnvironment, "EmployeeService");
ArgumentValidator.ThrowOnNull("templateColumnService", templateColumnService, "EmployeeService");
ArgumentValidator.ThrowOnNull("employeeStagingRepository", employeeStagingRepository, "EmployeeService");
ArgumentValidator.ThrowOnNull("employeeViewModelInterpreter", employeeViewModelInterpreter, "EmployeeService");
ArgumentValidator.ThrowOnNull("uploadStatusRepository", uploadStatusRepository, "EmployeeService");
ArgumentValidator.ThrowOnNull("c3ContextFactory", c3ContextFactory, "EmployeeService");
_hostingEnvironment = hostingEnvironment;
_templateColumnService = templateColumnService;
_employeeStagingRepository = employeeStagingRepository;
_employeeViewModelInterpreter = employeeViewModelInterpreter;
_uploadStatusRepository = uploadStatusRepository;
_c3ContextFactory = c3ContextFactory;
}
/// <summary>
/// Processes the file that is uploaded and saves to appropriate stating table in the provided branch.
/// </summary>
/// <param name="fileProcessingParams"></param>
/// <returns>Response of file uploaded with messages</returns>
Response<FileUploadResult> IValidationService.ProcessFile(FileProcessingParams fileProcessingParams)
{
var fileName = GetFileName(fileProcessingParams.Files);
var package = new ExcelPackage(new FileInfo(fileName));
var excelColumns = GetExcelColumns(package);
var requiredColumns = _templateColumnService.GetRequiredColumns(fileProcessingParams.TemplateName);
if (!ValidateColumns(requiredColumns, excelColumns))
{
return new Response<FileUploadResult>(new FileUploadResult
{
LocalFilePath = fileName,
FileName = Path.GetFileName(fileName),
FileLength = new FileInfo(fileName).Length,
Status = StatusTypes.Failed,
Message = string.Join("<br/> ", _messages.ToArray())
});
}
var columnPositions = GetMappingColumnsPosition(package);
var employeeViewModels = PopulateViewModels(columnPositions, package);
if (!ValidateData(employeeViewModels) || !ValidateDataIntegrity() || !ValidateSave(employeeViewModels, fileProcessingParams.BranchCode))
{
return new Response<FileUploadResult>(new FileUploadResult
{
LocalFilePath = fileName,
FileName = Path.GetFileName(fileName),
FileLength = new FileInfo(fileName).Length,
Status = StatusTypes.Failed,
Message = string.Join("<br/> ", _messages.ToArray())
});
}
// Now save and get the Id for file that needs to be used for future reference.
var fileId = string.Format("{0}_{1}", fileProcessingParams.BranchCode, Path.GetFileName(fileName));
_uploadStatusRepository.SaveUploadStatus(new UploadStatus
{
Id = fileId,
Status = "Processing",
CreatedDate = DateTime.Now
});
// Call the stored proc with fileId and do what needs to be done.
return new Response<FileUploadResult>(new FileUploadResult
{
LocalFilePath = fileName,
FileName = Path.GetFileName(fileName),
FileLength = new FileInfo(fileName).Length,
FileId = fileId,
Status = StatusTypes.Processing,
Message =
string.Format(
"Successfully uploaded the file for further processing. Please note the reference tag \"{0}\" for future references.",
fileId)
});
}
/// <summary>
/// Validate the required columns. Checks to see if all the required columns are in the spreadsheet that is uploaded.
/// </summary>
/// <param name="requiredColumns">Columns that must have a value</param>
/// <param name="excelColumns">Columns in the excel file uploaded by user.</param>
/// <returns>True or false.</returns>
public bool ValidateColumns(HashSet<string> requiredColumns, HashSet<string> excelColumns)
{
_messages = (from column in requiredColumns
where !excelColumns.Contains(column)
select string.Format("Uploaded spreadsheet doesn't contain column {0}", column)).ToList();
return _messages.Count <= 0;
}
public bool ValidateDataIntegrity()
{
// There are no integrity validation checks here, so just return true;
return true;
}
/// <summary>
/// Converts the view models to entities and persists data to database.
/// </summary>
/// <param name="employeeViewModels">populated view models from spread sheet.</param>
/// <returns>True or false.</returns>
public bool ValidateSave(List<EmployeeViewModel> employeeViewModels, string branchCode)
{
try
{
using (var context = _c3ContextFactory.GetC3DatabaseContext(int.Parse(branchCode)))
{
var employeeStagings = _employeeViewModelInterpreter.ConvertViewModelsToEntities(employeeViewModels);
_employeeStagingRepository.SaveEmployeeStaging(employeeStagings, context);
}
}
catch (Exception ex)
{
_messages.Add(ex.Message);
return false;
}
return true;
}
/// <summary>
/// Checks to see if data in the model is valid. If not adds errors.
/// </summary>
/// <param name="employeeViewModels">Data that is populated from spreadsheet</param>
/// <returns>True or false.</returns>
public bool ValidateData(List<EmployeeViewModel> employeeViewModels)
{
var employeeErrors = from u in employeeViewModels
where u.Messages.Count > 0
select u;
var viewModels = employeeErrors as EmployeeViewModel[] ?? employeeErrors.ToArray();
if (!viewModels.Any()) return true;
foreach (var item in viewModels)
{
_messages.Add(string.Join("<br/> ", item.Messages.ToArray()));
}
return false;
}
// This method is too long , condense it.
/// <summary>
/// Tries to retrieve the value from excel sheet and map them to view model.
/// </summary>
/// <param name="columnPositions">column positions from excel</param>
/// <param name="package">Excel package uploaded by user.</param>
/// <returns>List of employee view with error messages if any.</returns>
private List<EmployeeViewModel> PopulateViewModels(List<ColumnPositionViewModel> columnPositions,
ExcelPackage package)
{
var employeeViewModels = new List<EmployeeViewModel>();
var worksheet = package.Workbook.Worksheets[1];
for (var j = worksheet.Dimension.Start.Row + 1; j <= worksheet.Dimension.End.Row; j++)
{
var employeeViewModel = new EmployeeViewModel { Id = j };
try
{
if (GetPositionForMappedColumn("Name", columnPositions) > 0)
{
employeeViewModel.Name =
Convert.ToString(worksheet.Cells[j, GetPositionForMappedColumn("Name", columnPositions)].Text);
}
}
catch (Exception ex)
{
employeeViewModel.Messages.Add(ex.Message);
}
try
{
if (GetPositionForMappedColumn("Age", columnPositions) > 0)
{
employeeViewModel.Age =
Convert.ToInt16(worksheet.Cells[j, GetPositionForMappedColumn("Age", columnPositions)].Text);
}
}
catch (Exception ex)
{
employeeViewModel.Messages.Add(ex.Message);
}
try
{
if (GetPositionForMappedColumn("Sex", columnPositions) > 0)
{
employeeViewModel.Sex =
Convert.ToString(worksheet.Cells[j, GetPositionForMappedColumn("Sex", columnPositions)].Value);
}
}
catch (Exception ex)
{
employeeViewModel.Messages.Add(ex.Message);
}
try
{
if (GetPositionForMappedColumn("Telephone", columnPositions) > 0)
{
employeeViewModel.Telephone =
Convert.ToString(worksheet.Cells[j, GetPositionForMappedColumn("Telephone", columnPositions)].Value);
}
}
catch (Exception ex)
{
employeeViewModel.Messages.Add(ex.Message);
}
try
{
if (GetPositionForMappedColumn("Extension", columnPositions) > 0)
{
employeeViewModel.Extension =
Convert.ToString(worksheet.Cells[j, GetPositionForMappedColumn("Extension", columnPositions)].Value);
}
}
catch (Exception ex)
{
employeeViewModel.Messages.Add(ex.Message);
}
var validationResults = new List<ValidationResult>();
var isValid = Validator.TryValidateObject(employeeViewModel, new ValidationContext(employeeViewModel),
validationResults, true);
if (isValid)
{
employeeViewModels.Add(employeeViewModel);
}
else
{
foreach (var validationResult in validationResults)
{
employeeViewModel.Messages.Add(string.Format("For row {0} Error is {1}", employeeViewModel.Id,
validationResult.ErrorMessage));
}
employeeViewModels.Add(employeeViewModel);
}
}
return employeeViewModels;
}
/// <summary>
/// Gets the position of column if found.
/// </summary>
/// <param name="columnName">Column name from database.</param>
/// <param name="columnPositions">Columns from spreadsheet.</param>
/// <returns>Position id of the column. If it cannot find it returns -1.</returns>
private int GetPositionForMappedColumn(string columnName, List<ColumnPositionViewModel> columnPositions)
{
return columnPositions.Find(x => x.Name == columnName) != null
? columnPositions.Find(x => x.Name == columnName).Position
: -1;
}
/// <summary>
/// Get the mapping columns and positions of those columns.
/// </summary>
/// <param name="package">Excel file package that is uploaded by user.</param>
/// <returns>List of column positions that is used a map to import data. </returns>
private List<ColumnPositionViewModel> GetMappingColumnsPosition(ExcelPackage package)
{
var columnPositions = new List<ColumnPositionViewModel>();
var workSheet = package.Workbook.Worksheets[1];
for (var i = workSheet.Dimension.Start.Column; i <= workSheet.Dimension.End.Column; i++)
{
columnPositions.Add(new ColumnPositionViewModel
{
Name = ((string)workSheet.Cells[1, i].Value).Trim(),
Position = i
});
}
return columnPositions;
}
/// <summary>
/// Gets the list of excel columns.
/// </summary>
/// <param name="package">Excel package that is uploaded by user.</param>
/// <returns>Returns unique list or columns. </returns>
/// <exceptions>Throws an exception if the columns in the spreadsheets are not unique.</exceptions>
private HashSet<string> GetExcelColumns(ExcelPackage package)
{
// TODO: Need to unit test this method and throw an error.
var spreadSheetColumns = new HashSet<string>();
// All the data is in the first excelsheet
var workSheet = package.Workbook.Worksheets[1];
for (var i = workSheet.Dimension.Start.Column; i <= workSheet.Dimension.End.Column; i++)
{
spreadSheetColumns.Add(((string)workSheet.Cells[1, i].Value).Trim());
}
return spreadSheetColumns;
}
/// <summary>
/// Saves and returns the saved file name.
/// </summary>
/// <param name="files">File that is uploaded by user.</param>
/// <returns>File name that is saved in a known folder.</returns>
private string GetFileName(ICollection<IFormFile> files)
{
var filename = string.Empty;
var uploads = Path.Combine(_hostingEnvironment.WebRootPath, "Uploads");
foreach (var file in files)
{
if (file.Length <= 0) continue;
filename = Path.Combine(uploads, DateUtils.GetTicks().ToString() + '_' + file.FileName);
using (var fileStream = new FileStream(filename, FileMode.Create))
{
file.CopyTo(fileStream);
}
}
return filename;
}
}
}
