public void Arrange()
{
_mediator = new Mock <IMediator>();
_mediator.Setup(x => x.Send(It.Is <GetCommitmentQueryRequest>(req => req.CommitmentId == CohortId && req.ProviderId == ProviderId), It.IsAny <CancellationToken>()))
.ReturnsAsync(() => new GetCommitmentQueryResponse
{
Commitment = new CommitmentView
{
EmployerAccountId = AccountId,
EditStatus = EditStatus.ProviderOnly,
AgreementStatus = AgreementStatus.NotAgreed
}
});
var bulkUploader = new BulkUploader(_mediator.Object, Mock.Of <IBulkUploadValidator>(),
Mock.Of <IBulkUploadFileParser>(), Mock.Of <IProviderCommitmentsLogger>());
_reservationsService = new Mock <IReservationsService>();
_mockHashingService = new Mock <IHashingService>();
_mockHashingService.Setup(hs => hs.DecodeValue(HashedCohortId)).Returns(CohortId);
_mockBulkUploadFileParser = new Mock <IBulkUploadFileParser>();
_bulkUploadOrchestrator = new BulkUploadOrchestrator(
_mediator.Object,
bulkUploader,
_mockHashingService.Object,
new BulkUploadMapper(_mediator.Object),
Mock.Of <IProviderCommitmentsLogger>(),
_mockBulkUploadFileParser.Object,
_reservationsService.Object);
}
public void Arrange()
{
_commitmentView = new CommitmentView
{
EditStatus = EditStatus.ProviderOnly,
AgreementStatus = AgreementStatus.NotAgreed
};
_mediator = new Mock <IMediator>();
_mediator.Setup(x => x.Send(It.IsAny <GetCommitmentQueryRequest>(), It.IsAny <CancellationToken>()))
.ReturnsAsync(() => new GetCommitmentQueryResponse
{
Commitment = _commitmentView
});
_reservationsService = new Mock <IReservationsService>();
_reservationsService.Setup(rs => rs.IsAutoReservationEnabled(It.IsAny <long>(), It.IsAny <long?>())).ReturnsAsync(true);
var bulkUploader = new BulkUploader(_mediator.Object, Mock.Of <IBulkUploadValidator>(),
Mock.Of <IBulkUploadFileParser>(), Mock.Of <IProviderCommitmentsLogger>());
_bulkUploadOrchestrator = new BulkUploadOrchestrator(
_mediator.Object,
bulkUploader,
Mock.Of <IHashingService>(),
new BulkUploadMapper(_mediator.Object),
Mock.Of <IProviderCommitmentsLogger>(),
Mock.Of <IBulkUploadFileParser>(),
_reservationsService.Object
);
}
public void Setup()
{
var mockHashingService = new Mock <IHashingService>();
mockHashingService.Setup(x => x.DecodeValue(It.IsAny <string>())).Returns(123L);
_file = new Mock <HttpPostedFileBase>();
_file.Setup(m => m.FileName).Returns("APPDATA-20051030-213855.csv");
_file.Setup(m => m.ContentLength).Returns(400);
_mockMediator = new Mock <IMediator>();
_mockMediator.Setup(m => m.Send(It.IsAny <GetTrainingProgrammesQueryRequest>(), It.IsAny <CancellationToken>()))
.ReturnsAsync(new GetTrainingProgrammesQueryResponse {
TrainingProgrammes = new List <TrainingProgramme>
{
{
new TrainingProgramme {
CourseCode = "2", Name = "Hej"
}
},
{
new TrainingProgramme {
CourseCode = "1-2-3"
}
}
}
});
_mockMediator.Setup(m => m.Send(It.IsAny <GetOverlappingApprenticeshipsQueryRequest>(), It.IsAny <CancellationToken>()))
.Returns(
Task.Run(() => new GetOverlappingApprenticeshipsQueryResponse
{
Overlaps = new List <ApprenticeshipOverlapValidationResult>()
}));
_mockReservationsService = new Mock <IReservationsService>();
_mockReservationsService.Setup(rs => rs.IsAutoReservationEnabled(It.IsAny <long>(), It.IsAny <long?>())).ReturnsAsync(true);
var uploadValidator = BulkUploadTestHelper.GetBulkUploadValidator(512);
_logger = new Mock <IProviderCommitmentsLogger>();
_logger.Setup(x => x.Info(It.IsAny <string>(), It.IsAny <long?>(), It.IsAny <long?>(), It.IsAny <long?>())).Verifiable();
_logger.Setup(x => x.Error(It.IsAny <Exception>(), It.IsAny <string>(), It.IsAny <long?>(), It.IsAny <long?>(), It.IsAny <long?>())).Verifiable();
var uploadFileParser = new BulkUploadFileParser(_logger.Object);
var bulkUploader = new BulkUploader(_mockMediator.Object, uploadValidator, uploadFileParser, Mock.Of <IProviderCommitmentsLogger>());
var bulkUploadMapper = new BulkUploadMapper(_mockMediator.Object);
_sut = new BulkUploadOrchestrator(_mockMediator.Object, bulkUploader, mockHashingService.Object,
bulkUploadMapper, Mock.Of <IProviderCommitmentsLogger>(), Mock.Of <IBulkUploadFileParser>(), _mockReservationsService.Object);
}
public BulkUploadOrchestrator(
IMediator mediator,
BulkUploader bulkUploader,
IHashingService hashingService,
BulkUploadMapper mapper,
IProviderCommitmentsLogger logger,
IBulkUploadFileParser fileParser,
IReservationsService reservationsService) : base(mediator, hashingService, logger)
{
_bulkUploader = bulkUploader;
_mapper = mapper;
_fileParser = fileParser;
_reservationsService = reservationsService ?? throw new ArgumentNullException(nameof(reservationsService));
}
public static bool BulkInsertLinelist(string FilePath,
string Extension, string isHDR,
string LinelistType, string user)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
using (OleDbConnection connExcel = new OleDbConnection(conStr))
{
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataTable dt = new DataTable();
string SheetName = string.Empty;
if (dtExcelSchema.Rows.Count == 1)
SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
else
{
var count = 0;
foreach (DataRow row in dtExcelSchema.Rows)
{
if (dtExcelSchema.Rows[count]["TABLE_NAME"].ToString().Contains("Linelist"))
{
SheetName = dtExcelSchema.Rows[count]["TABLE_NAME"].ToString();
}
count++;
}
}
var pathogen = string.Empty;
if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H5N1).ToString())
pathogen = ((int)LineListType.H5N1).ToString();
else if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H5N6).ToString())
pathogen = ((int)LineListType.H5N6).ToString();
else
pathogen = ((int)LineListType.H7N9).ToString();
var now = DateTime.Now.ToString();
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT *,('" + now + "') as date_created ,('" + user + "') as created_by,('" + pathogen + "') as pathogen_id From [" + SheetName + "]"
, connExcel))
{
oda.Fill(dt);
}
connExcel.Close();
try
{
var constring = new CryptLib.CryptLib().Decrypt3DES(ConfigurationManager.ConnectionStrings["ESRSITUPD"].ConnectionString);
using (SqlConnection con = new SqlConnection(constring))
{
con.Open();
using (SqlTransaction transaction = con.BeginTransaction())
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepNulls, transaction))
{
// sqlBulkCopy.DestinationTableName = "[ESRSITUPD].[dbo].[T_LINE_LIST]";
/*
* Add Column mapping here
* In LINELIST_H5N1
Admin1st_Province => Region
Admin2nd_District => City
In LINELIST_H5N6
Region => Region
Province => City
In LINELIST_H7N9
Province or State (reporting) => Region
City => City
District or County
*/
if (LinelistType == LineListType.Standard.ToString())
{
//sqlBulkCopy.ColumnMappings.Add("pathogen_id", "pathogen_id");
//sqlBulkCopy.ColumnMappings.Add("case_id", "case_id");
//sqlBulkCopy.ColumnMappings.Add("ctry_code", "ctry_code");
//sqlBulkCopy.ColumnMappings.Add("admin1", "Country");
//sqlBulkCopy.ColumnMappings.Add("admin2", "Region");
//sqlBulkCopy.ColumnMappings.Add("age", "Age");
//sqlBulkCopy.ColumnMappings.Add("gender", "Gender");
//sqlBulkCopy.ColumnMappings.Add("onset_date", "Onset Date");
//sqlBulkCopy.ColumnMappings.Add("created_by", "created_by");
//sqlBulkCopy.ColumnMappings.Add("date_created", "date_created");
//sqlBulkCopy.ColumnMappings.Add("updated_by", "updated_by");
//sqlBulkCopy.ColumnMappings.Add("date_updated", "date_updated");
}
if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H5N1).ToString())
{
var query = from tbl1 in dt.AsEnumerable()
where tbl1["ID"].ToString() != String.Empty
select new
{
date_created = tbl1["date_created"],
pathogen_id = tbl1["pathogen_id"],
case_id = tbl1["ID"],
ctry_code = tbl1["Country"],
admin1 = tbl1["Admin1st_Province"],
admin2 = tbl1["Admin2nd_District"],
age = tbl1["Age"],
gender = tbl1["Sex"],
onset_date = tbl1["Onset Date"],
created_by = user,
updated_by = user,
date_updated = tbl1["date_created"],
ro = tbl1["RO"]
};
dt = new DataTable();
dt.Columns.Add("pathogen_id", typeof(string));
dt.Columns.Add("case_id", typeof(string));
dt.Columns.Add("ctry_code", typeof(string));
dt.Columns.Add("admin1", typeof(string));
dt.Columns.Add("admin2", typeof(string));
dt.Columns.Add("age", typeof(string));
dt.Columns.Add("gender", typeof(string));
var column = new DataColumn("onset_date", typeof(DateTime));
column.AllowDBNull = true;
dt.Columns.Add(column);
dt.Columns.Add("created_by", typeof(string));
dt.Columns.Add("date_created", typeof(DateTime));
dt.Columns.Add("updated_by", typeof(string));
dt.Columns.Add("date_updated", typeof(DateTime));
dt.Columns.Add("region_code", typeof(string));
foreach (var rowinfo in query)
{
try
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
Math.Round(Convert.ToDecimal(rowinfo.age), 0, MidpointRounding.AwayFromZero).ToString(),
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
catch
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
0,
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
}
}
if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H5N6).ToString())
{
var query = from tbl1 in dt.AsEnumerable()
select new
{
date_created = tbl1["date_created"],
pathogen_id = tbl1["pathogen_id"],
case_id = tbl1["No"],
ctry_code = tbl1["Country"],
admin1 = tbl1["Region"],
admin2 = tbl1["Province"],
age = tbl1["Age"].ToString(),
gender = tbl1["Sex"],
onset_date = tbl1["Onset"] ?? DBNull.Value,
created_by = user,
updated_by = user,
date_updated = tbl1["date_created"],
};
dt = new DataTable();
dt.Columns.Add("pathogen_id", typeof(int));
dt.Columns.Add("case_id", typeof(int));
dt.Columns.Add("ctry_code", typeof(string));
dt.Columns.Add("admin1", typeof(string));
dt.Columns.Add("admin2", typeof(string));
dt.Columns.Add("age", typeof(int));
dt.Columns.Add("gender", typeof(string));
var column = new DataColumn("onset_date", typeof(DateTime));
column.AllowDBNull = true;
dt.Columns.Add(column);
dt.Columns.Add("created_by", typeof(string));
dt.Columns.Add("date_created", typeof(DateTime));
dt.Columns.Add("updated_by", typeof(string));
dt.Columns.Add("date_updated", typeof(DateTime));
dt.Columns.Add("region_code", typeof(string));
foreach (var rowinfo in query)
{
try
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
Math.Round(Convert.ToDecimal(rowinfo.age), 0, MidpointRounding.AwayFromZero).ToString(),
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
catch
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
0,
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
}
}
if (LinelistType == Enum.GetName(typeof(LineListType), LineListType.H7N9).ToString())
{
var query = from tbl1 in dt.AsEnumerable()
where tbl1["Case ID no"].ToString() != String.Empty
select new
{
date_created = tbl1["date_created"],
pathogen_id = tbl1["pathogen_id"],
case_id = tbl1["Case ID no"],
ctry_code = tbl1["Country"],
admin1 = tbl1["Province or State (reporting)"],
admin2 = tbl1["City"],
age = tbl1["Age (years)"],
gender = tbl1["Sex (M/F)"],
onset_date = tbl1["Onset date (dd/mm/yyyy)"] ?? DBNull.Value,
created_by = user,
updated_by = user,
date_updated = tbl1["date_created"],
};
dt = new DataTable();
//dt.Columns.Add("pathogen_id", typeof(string));
//dt.Columns.Add("case_id", typeof(string));
//dt.Columns.Add("ctry_code", typeof(string));
//dt.Columns.Add("admin1", typeof(string));
//dt.Columns.Add("admin2", typeof(string));
//dt.Columns.Add("age", typeof(string));
//dt.Columns.Add("gender", typeof(string));
//dt.Columns.Add("onset_date", typeof(string));
//dt.Columns.Add("created_by", typeof(string));
//dt.Columns.Add("date_created", typeof(string));
//dt.Columns.Add("updated_by", typeof(string));
//dt.Columns.Add("date_updated", typeof(string));
dt.Columns.Add("pathogen_id", typeof(string));
dt.Columns.Add("case_id", typeof(string));
dt.Columns.Add("ctry_code", typeof(string));
dt.Columns.Add("admin1", typeof(string));
dt.Columns.Add("admin2", typeof(string));
dt.Columns.Add("age", typeof(string));
dt.Columns.Add("gender", typeof(string));
var column = new DataColumn("onset_date", typeof(DateTime));
column.AllowDBNull = true;
dt.Columns.Add(column);
dt.Columns.Add("created_by", typeof(string));
dt.Columns.Add("date_created", typeof(DateTime));
dt.Columns.Add("updated_by", typeof(string));
dt.Columns.Add("date_updated", typeof(DateTime));
dt.Columns.Add("region_code", typeof(string));
foreach (var rowinfo in query)
{
try
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
Math.Round(Convert.ToDecimal(rowinfo.age), 0, MidpointRounding.AwayFromZero).ToString(),
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
catch
{
dt.Rows.Add(
rowinfo.pathogen_id,
rowinfo.case_id,
rowinfo.ctry_code,
rowinfo.admin1,
rowinfo.admin2,
0,
rowinfo.gender,
rowinfo.onset_date,
rowinfo.created_by,
rowinfo.date_created,
rowinfo.updated_by,
rowinfo.date_updated);
}
}
}
string tableName = "#tmp_linelist";
BulkUploader<Model.Linelist> linelistUploader = new BulkUploader<Model.Linelist>(tableName);
linelistUploader.AddMapping(x => x.admin1, "admin1", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.admin2, "admin2", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.age, "age", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.case_id, "case_id", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.flu_week, "flu_week", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.flu_year, "flu_year", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.created_by, "created_by", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.ctry_code, "ctry_code", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.date_created, "date_created", "datetime");
linelistUploader.AddMapping(x => x.date_updated, "date_updated", "datetime");
linelistUploader.AddMapping(x => x.gender, "gender", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.onset_date, "onset_date", "date");
linelistUploader.AddMapping(x => x.pathogen_id, "pathogen_id", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.updated_by, "updated_by", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.remarks, "remarks", "nvarchar(Max)");
linelistUploader.AddMapping(x => x.region_code, "region_code", "nvarchar(Max)");
linelistUploader.CreateTable(con, transaction);
sqlBulkCopy.BulkCopyTimeout = 60;
//var records = dt.AsEnumerable();
linelistUploader.Upload(sqlBulkCopy, MiscHelper.DataTableToListLinelist(dt));
// USP Clean
/*
* Remarking and linelist cleaning
*
*/
var cmd = new SqlCommand(StoredProcedure.Linelist_Import, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
}
}
return true;
}
catch (Exception e)
{
throw e;
}
}
}
public static int Handle(UploadParameters options)
{
try
{
Console.OutputEncoding = Encoding.UTF8;
}
catch (Exception)
{
// swallow. The above throws a handle error when run in Visual Studio
}
IsUploading = true;
// -u user, and <path> are all required, so they must contain strings.
// -d destination has a default value, so it also must contain a string.
options.Path = options.Path.TrimEnd(new[] { '/', '\\', System.IO.Path.PathSeparator }); // remove any trailing slashes
// validate the value for the upload destination.
options.Dest = options.Dest.ToLowerInvariant();
switch (options.Dest)
{
case UploadDestination.DryRun:
case UploadDestination.Development:
case UploadDestination.Production:
break;
default:
Console.WriteLine($"Error: if present, upload destination (-d) must be one of {UploadDestination.DryRun}, {UploadDestination.Development}, or {UploadDestination.Production}");
return(1);
}
BookUpload.Destination = options.Dest; // must be set before calling SetupErrorHandling() (or BloomParseClient constructor)
// This task will be all the program does. We need to do enough setup so that
// the upload code can work, then tear it down.
Program.SetUpErrorHandling();
try
{
using (var applicationContainer = new ApplicationContainer())
{
Program.SetUpLocalization(applicationContainer);
LocalizationManager.SetUILanguage(Settings.Default.UserInterfaceLanguage, false);
var singleBookUploader = new BookUpload(new BloomParseClient(), ProjectContext.CreateBloomS3Client(),
applicationContainer.BookThumbNailer);
var uploader = new BulkUploader(singleBookUploader);
// Since Bloom is not a normal console app, when run from a command line, the new command prompt
// appears at once. The extra newlines here are attempting to separate this from our output.
switch (options.Dest)
{
case UploadDestination.DryRun:
Console.WriteLine($"\nThe following actions would happen if you set destination to '{(BookUpload.UseSandboxByDefault ? UploadDestination.Development : UploadDestination.Production)}'.");
break;
case UploadDestination.Development:
Console.WriteLine("\nThe upload will go to dev.bloomlibrary.org.");
break;
case UploadDestination.Production:
Console.WriteLine("\nThe upload will go to bloomlibrary.org.");
break;
}
Console.WriteLine("\nStarting upload...");
uploader.BulkUpload(applicationContainer, options);
Console.WriteLine(("\nBulk upload complete.\n"));
}
return(0);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
return(1);
}
}
