public void TestReaderEmployee()
{
EmployeeReader reader = new EmployeeReader();
List <Employee> employees = reader.read("../employees.csv");
Assert.IsNotNull(employees);
}
static void Main(string[] args)
{
//1. Back up Employee Table
DatabaseBackupJob.BackUp();
//2. Drop Employee Table & Create it again
DatabaseCreateJob.Create();
//3. Read the Employees.csv using CSVHelper
EmployeeReader reader = new EmployeeReader();
List <Employee> employees = reader.Read(ConfigurationManager.AppSettings["DataFile"]);
//4. Insert each Employee into EmployeeTable
SqlBulkCopyJob.Copy(employees);
//5. Run All PLSQLs
/*
* CALCULATED TABLES
*
* STORED PROCEDURE
* CALCULATE_HEADCOUNT_BY_DEMOGRAPHICS
*
*
*
*
*
*/
}
public void TestEmployeeReader()
{
EmployeeReader reader = new EmployeeReader();
List <Employee> employees = reader.Read("../../../employees.csv");
Assert.IsNotNull(employees);
//var connectionString = ConfigurationManager.ConnectionStrings["dynamiconnection"].ConnectionString;
var connectionString = "data source = mauintapp01; initial catalog = Attrition; User ID = DashboardUser; Password = abc?123!; multipleactiveresultsets = True";
var dataTable = new DataTable();
dataTable.Columns.Add("EmployeeNumber");
dataTable.Columns.Add("FirstName");
dataTable.Columns.Add("LastName");
dataTable.Columns.Add("District");
dataTable.Columns.Add("DepartmentName");
dataTable.Columns.Add("Site");
dataTable.Columns.Add("OnSiteDepartment");
dataTable.Columns.Add("HireDate");
dataTable.Columns.Add("LocationName");
dataTable.Columns.Add("PositionName");
dataTable.Columns.Add("ManagerDisplayName");
dataTable.Columns.Add("TerminationDate");
dataTable.Columns.Add("EmploymentStatusName");
dataTable.Columns.Add("IsVoluntaryTermination");
dataTable.Columns.Add("TerminationRegretType");
dataTable.Columns.Add("EmployeeGender");
dataTable.Columns.Add("EmployeeBirthDate");
dataTable.Columns.Add("PrimaryAddressCity");
dataTable.Columns.Add("MaritialStatusName");
dataTable.Columns.Add("ShiftRotationName");
dataTable.Columns.Add("JobLevel");
Dictionary <int, Employee> map = new Dictionary <int, Employee>();
foreach (Employee employee in employees)
{
Console.WriteLine(" Row " + employees.IndexOf(employee) + 1);
if (!map.ContainsKey(employee.EmployeeNumber))
{
dataTable.Rows.Add(employee.EmployeeNumber, employee.FirstName, employee.LastName, employee.District, employee.DepartmentName, employee.Site ?? (object)DBNull.Value, employee.OnSiteDepartment, employee.HireDate, employee.LocationName, employee.PositionName, employee.ManagerDisplayName, employee.TerminationDate, employee.EmploymentStatusName, employee.IsVoluntaryTermination, employee.TerminationRegretType, employee.EmployeeGender, employee.EmployeeBirthDate, employee.PrimaryAddressCity, employee.MaritialStatusName, employee.ShiftRotationName, employee.JobLevel);
map.Add(employee.EmployeeNumber, employee);
}
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (var sqlBulk = new SqlBulkCopy(connectionString))
{
sqlBulk.DestinationTableName = "EmployeeTable";
sqlBulk.WriteToServer(dataTable);
}
}
}
public void WrongProjectID_ThrowsException()
{
// Arrange
EmployeeReader target = new EmployeeReader();
target.Separator = ',';
string line = "143, I2, 2013-11-01, 2014-01-05";
// Act
Assert.Throws <FormatException>(() => target.ParseEmployeeRecord(line));
}
public void UnknownDateFormat_ThrowsException()
{
// Arrange
EmployeeReader target = new EmployeeReader();
target.Separator = ',';
string line = "218, 10, 15-16-2012, NULL";
// Act
Assert.Throws <FormatException>(() => target.ParseEmployeeRecord(line));
}
public DatabasePlaygroundRunner()
{
_databaseFilePath = "test.db";
_connectionString = $"URI=file:{_databaseFilePath}";
_databaseExecutor = new DatabaseExecutor(_connectionString);
_departmentReader = new DepartmentReader(_databaseExecutor);
_departmentWriter = new DepartmentWriter(_databaseExecutor);
//_nodeReader = new NodeReader(_databaseExecutor);
_employeeWriter = new EmployeeWriter(_databaseExecutor);
_employeeReader = new EmployeeReader(_databaseExecutor);
}
public async Task <IActionResult> Remove(int employeeId)
{
var employee = await EmployeeReader.Read(employeeId);
if (employee.HasNoValue)
{
return(NotFound());
}
await EmployeeWriter.Remove(employee.Value);
return(Ok());
}
public async Task Write(Employee employee)
{
var emailAlreadyExists = await EmployeeReader.Any(employee.Email.Address);
if (emailAlreadyExists)
{
var notification = new Notification("Email already registred");
NotificationStore.AddNotification(notification);
return;
}
await EmployeeWriter.Write(employee);
}
public void NotUniformColumns_ThrowsException()
{
// Arrange
EmployeeReader target = new EmployeeReader();
target.Separator = ',';
string fileContents =
"143, 11, 2013-11-01, 2014-01-05" + Environment.NewLine +
"218, 10, 15-16-2012" + Environment.NewLine +
"143, 10, 2009-01-01, 2011-04-27";
Stream data = new MemoryStream(Encoding.Default.GetBytes(fileContents));
// Act
Assert.ThrowsAsync <InvalidDataException>(async() => await target.ReadRecordsAsync(data));
data.Close();
}
public async void ReadsEmployees_SpecifiedDateCulture()
{
// Arrange
EmployeeReader target = new EmployeeReader();
target.DateTimeCulture = new CultureInfo("en-US");
target.Separator = ',';
string fileContents =
$"143, 12, {new DateTime(2013, 11, 01).ToString(target.DateTimeCulture)}" +
$", {new DateTime(2014, 01, 05).ToString(target.DateTimeCulture)}" + Environment.NewLine +
$"218, 10, {new DateTime(2012, 05, 16).ToString(target.DateTimeCulture)}, NULL" + Environment.NewLine +
$"143, 10, {new DateTime(2009, 01, 01).ToString(target.DateTimeCulture)}," +
$" {new DateTime(2011, 04, 27).ToString(target.DateTimeCulture)}";
Stream data = new MemoryStream(Encoding.Default.GetBytes(fileContents));
IReadOnlyCollection <EmployeeRecord> records;
// Act
records = await target.ReadRecordsAsync(data);
data.Close();
// Assert
Assert.NotNull(records);
Assert.Collection(records, c =>
{
Assert.Equal(143, c.EmployeeID);
Assert.Equal(12, c.ProjectID);
Assert.Equal(new DateTime(2013, 11, 01), c.DateFrom);
Assert.Equal(new DateTime(2014, 01, 05), c.DateTo);
}, c =>
{
Assert.Equal(218, c.EmployeeID);
Assert.Equal(10, c.ProjectID);
Assert.Equal(new DateTime(2012, 05, 16), c.DateFrom);
Assert.Equal(DateTime.Today, c.DateTo);
}, c =>
{
Assert.Equal(143, c.EmployeeID);
Assert.Equal(10, c.ProjectID);
Assert.Equal(new DateTime(2009, 01, 01), c.DateFrom);
Assert.Equal(new DateTime(2011, 04, 27), c.DateTo);
});
}
public async Task Read_When_EmployeeId_Exists_Should_Return_Employee(Employee employee)
{
var options = DbContextOptionsFactory.Create(nameof(Read_When_EmployeeId_Exists_Should_Return_Employee));
using (var context = new EmployeeManagerContext(options))
{
context.Add(employee);
await context.SaveChangesAsync();
}
using (var context = new EmployeeManagerContext(options))
{
var sut = new EmployeeReader(context);
var actual = await sut.Read(employee.Id);
actual.Should().NotBeNull();
actual.Value.Id.Should().Be(employee.Id);
}
}
public async Task Any_When_EmailAddress_Exists_Should_Return_True(
Employee employee)
{
var options = DbContextOptionsFactory.Create(nameof(Any_When_EmailAddress_Exists_Should_Return_True));
using (var context = new EmployeeManagerContext(options))
{
context.Add(employee);
await context.SaveChangesAsync();
}
using (var context = new EmployeeManagerContext(options))
{
var sut = new EmployeeReader(context);
var actual = await sut.Any(employee.Email.Address);
actual.Should().BeTrue();
}
}
public async Task Read_When_EmployeeId_Does_Not_Exist_Should_Return_Maybe_None(
Employee employee,
int someEmployeeId)
{
var options = DbContextOptionsFactory.Create(nameof(Read_When_EmployeeId_Does_Not_Exist_Should_Return_Maybe_None));
using (var context = new EmployeeManagerContext(options))
{
context.Add(employee);
await context.SaveChangesAsync();
}
using (var context = new EmployeeManagerContext(options))
{
var sut = new EmployeeReader(context);
var actual = await sut.Read(someEmployeeId);
actual.Should().Be(Maybe <Employee> .None);
}
}
public async Task Any_When_EmailAddress_Does_Not_Exist_Should_Return_False(
Employee employee,
string someEmployeeEmail)
{
var options = DbContextOptionsFactory.Create(nameof(Any_When_EmailAddress_Does_Not_Exist_Should_Return_False));
using (var context = new EmployeeManagerContext(options))
{
context.Add(employee);
await context.SaveChangesAsync();
}
using (var context = new EmployeeManagerContext(options))
{
var sut = new EmployeeReader(context);
var actual = await sut.Any(someEmployeeEmail);
actual.Should().BeFalse();
}
}
public async void ReadsEmployees_SingleDateFormat()
{
// Arrange
EmployeeReader target = new EmployeeReader();
target.Separator = ',';
string fileContents =
"143, 12, 2013-11-01, 2014-01-05" + Environment.NewLine +
"218, 10, 2012-05-16, NULL" + Environment.NewLine +
"143, 10, 2009-01-01, 2011-04-27";
Stream data = new MemoryStream(Encoding.Default.GetBytes(fileContents));
IReadOnlyCollection <EmployeeRecord> records;
// Act
records = await target.ReadRecordsAsync(data);
data.Close();
// Assert
Assert.NotNull(records);
Assert.Collection(records, c =>
{
Assert.Equal(143, c.EmployeeID);
Assert.Equal(12, c.ProjectID);
Assert.Equal(new DateTime(2013, 11, 01), c.DateFrom);
Assert.Equal(new DateTime(2014, 01, 05), c.DateTo);
}, c =>
{
Assert.Equal(218, c.EmployeeID);
Assert.Equal(10, c.ProjectID);
Assert.Equal(new DateTime(2012, 05, 16), c.DateFrom);
Assert.Equal(DateTime.Today, c.DateTo);
}, c =>
{
Assert.Equal(143, c.EmployeeID);
Assert.Equal(10, c.ProjectID);
Assert.Equal(new DateTime(2009, 01, 01), c.DateFrom);
Assert.Equal(new DateTime(2011, 04, 27), c.DateTo);
});
}
public async Task Read_Should_Paginated_Employees(List <Employee> employees)
{
const int pageIndex = 1;
const int pageSize = 1;
var options = DbContextOptionsFactory.Create(nameof(Read_Should_Paginated_Employees));
using (var context = new EmployeeManagerContext(options))
{
context.AddRange(employees);
await context.SaveChangesAsync();
}
using (var context = new EmployeeManagerContext(options))
{
var sut = new EmployeeReader(context);
var actual = await sut.Read(pageIndex, pageSize);
actual.Count.Should().Be(pageSize);
}
}
public async Task <IActionResult> Import(IFormFile file)
{
var stream = file.OpenReadStream();
var reader = new EmployeeReader(stream);
var models = reader.Parse();
var list = new List <EmployeeBuffer>();
var data = await _service.GetDataForEmployeeAction();
if (!models.Any())
{
return(BadRequest("File trống"));
}
foreach (var model in models)
{
var row = model.Row;
var staff = model.Model;
var buffer = new EmployeeBuffer {
Employee = new Employee(), Errors = new List <string>()
};
if (string.IsNullOrEmpty(staff.Code))
{
buffer.Errors.Add("Mã nhân viên trống.");
buffer.Index = row;
}
else
{
buffer.Employee.DefCode = staff.Code;
}
if (string.IsNullOrEmpty(staff.Name))
{
buffer.Errors.Add("Tên nhân viên trống.");
buffer.Index = row;
}
else
{
buffer.Employee.Name = staff.Name;
}
buffer.Employee.DateOfBirth = staff.DateOfBirth;
buffer.Employee.Email = staff.Email;
buffer.Employee.Phone = staff.Phone;
buffer.Employee.CreatedDate = DateTime.Now;
buffer.Employee.LastModified = DateTime.Now;
buffer.Employee.IsPublished = true;
if (string.IsNullOrEmpty(staff?.FacutlyCode.ToString()))
{
buffer.Errors.Add("Mã khoa trống");
buffer.Index = row;
}
else
{
var facutly = data.Facutlies.ToList()?.FirstOrDefault(x => x.DefCode == staff.FacutlyCode);
if (facutly == null)
{
buffer.Errors.Add("Không tìm thấy khoa trên hệ thống");
buffer.Index = row;
}
else
{
buffer.Employee.FacutlyCode = facutly.Id.ToObjectId();
}
}
if (string.IsNullOrEmpty(staff.MajorCode.ToString()))
{
buffer.Errors.Add("Mã chuyên môn trống");
buffer.Index = row;
}
else
{
var major = data.Majors.ToList()?.FirstOrDefault(x => x.DefCode == staff.MajorCode);
if (major == null)
{
buffer.Errors.Add("Không tìm thấy chuyên môn trên hệ thống");
buffer.Index = row;
}
else
{
buffer.Employee.MajorCode = major.Id.ToObjectId();
}
}
if (string.IsNullOrEmpty(staff.LevelCode.ToString()))
{
buffer.Errors.Add("Mã trình độ trống");
buffer.Index = row;
}
else
{
var level = data.Levels.ToList()?.FirstOrDefault(x => x.DefCode == staff.LevelCode);
if (level == null)
{
buffer.Errors.Add("Không tìm thấy trình độ trên hệ thống");
buffer.Index = row;
}
else
{
buffer.Employee.LevelCode = level.Id.ToObjectId();
}
}
list.Add(buffer);
}
if (list.Any(x => x.Errors.Any()))
{
return(BadRequest(new
{
Errors = list.Where(x => x.Errors.Any()).ToList()
}));
}
var listToInsert = list.Select(x => x.Employee).ToList();
await _service.Import(listToInsert);
return(Ok());
}
