public async Task <ImportResultViewModel> ProcessFile(Stream stream)
{
var result = new ImportResultViewModel
{
LinesImported = 0,
LinesIgnored = new List <string>()
};
var linesProcessed = 1;
using (var reader = new CSVTransactionReader(stream))
{
var tran = new TransactionViewModel();
while (await reader.ReadTransactionAsync(tran))
{
TransactionValidation.Validate(tran);
if (tran.IsValid)
{
result.LinesImported++;
_repository.Add(tran.ToTransaction());
}
else
{
result.LinesIgnored.Add($"{linesProcessed} ignored, Reason: {tran.ValidationMessage}.");
}
linesProcessed++;
}
await _repository.SaveChangesAsync();
reader.Close();
}
return(result);
}
public JsonResult importData(string tableName, string dataName, string fileUrl)
{
List <Map4D> data = null;
ImportResultViewModel result = new ImportResultViewModel {
TimeQuery = 0,
RowQuery = ""
};
string message = "";
var status = false;
try
{
StreamReader sr = new StreamReader(fileUrl);
string jsonString = sr.ReadToEnd();
data = JsonConvert.DeserializeObject <List <Map4D> >(jsonString);
status = true;
result = databaseBo.ImportData(tableName, dataName, data);
}
catch (FileNotFoundException ex)
{
message = ex.Message;
status = false;
}
return(Json(new
{
status = status,
message = message,
timequery = result.TimeQuery,
rowquery = result.RowQuery
}));
}
public ActionResult ImportResult(string executionId)
{
var result = _recipeResultAccessor.GetResult(executionId);
var viewModel = new ImportResultViewModel()
{
Result = result
};
return(View(viewModel));
}
public ActionResult Import(FormCollection form)
{
try
{
var file = Request.Files["importexcelfile"];
if (file != null && file.ContentLength > 0)
{
using (var unitOfWork = UnitOfWorkManager.NewUnitOfWork())
{
var result = new ImportResultViewModel();
//是否覆盖
var isCoverData = !string.IsNullOrEmpty(form["IsCoverData"]) && form["IsCoverData"] == "on";
//导入职业史
result.Results.Add(_employeeWorkHistoryService.ImportEmployeeWorkHistories(file.InputStream));
//导入员工信息
result.Results.Add(_companyEmployeeService.ImportCompanyEmployees(file.InputStream, isCoverData));
//执行插入或修改
unitOfWork.Commit();
//赋值准备导出
this.TempData["ImportResult"] = result;
var flag = true;
foreach (var r in result.Results)
{
if (r.ErrorRows.Count > 0)
{
flag = false;
break;
}
}
if (flag)
{
SuccessNotification("导入员工信息成功！");
}
return(View(result));
}
}
else
{
ErrorNotification(new Exception("上传失败，尝试上传无效的文件"));
return(View());
}
}
catch (Exception ex)
{
ErrorNotification(ex);
return(View());
}
}
public ActionResult Import(FormCollection form)
{
try
{
var file = Request.Files["importexcelfile"];
if (file != null && file.ContentLength > 0)
{
using (var unitOfWork = UnitOfWorkManager.NewUnitOfWork())
{
var result = new ImportResultViewModel();
//是否覆盖
var isCoverData = !string.IsNullOrEmpty(form["IsCoverData"]) && form["IsCoverData"] == "on";
//体检结果
result.Results.Add(_healthResultService.ImportHealthResult(file.InputStream, isCoverData));
//执行插入或修改
unitOfWork.Commit();
//赋值准备导出
this.TempData["ImportResult"] = result;
if (result.Results[0].ErrorRows.Count == 0)
{
SuccessNotification("上传文件成功！");
}
return(View(result));
}
}
else
{
ErrorNotification(new Exception("上传失败，尝试上传无效的文件"));
return(View());
}
}
catch (Exception ex)
{
ErrorNotification(ex);
return(View());
}
}
public ActionResult ExportErrorResult()
{
try
{
ImportResultViewModel data = (ImportResultViewModel)this.TempData["ImportResult"];
var fileName = DateTime.Now.Ticks + ".xlsx";
var filePath = Server.MapPath("~/Content/ExportFiles/" + fileName);
System.IO.File.Copy(Server.MapPath("~/Content/Templates/体检结果模板.xlsx"), filePath);
_healthResultService.ExportHealthResult(filePath, data.Results);
//byte[] bytes = result.ToArray();
//return File(bytes, "text/xls", "出错信息.xlsx");
return(File(filePath, "text/xls", "出错信息.xlsx"));
}
catch (Exception ex)
{
ErrorNotification(ex);
return(RedirectToAction("Import"));
}
}
public ActionResult ExportCompanyEmployeeManage()
{
try
{
ImportResultViewModel data = (ImportResultViewModel)this.TempData["ImportResult"];
var fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
var filePath = Server.MapPath("~/Content/ExportFiles/" + fileName);
System.IO.File.Copy(Server.MapPath("~/Content/Templates/员工信息模板.xlsx"), filePath);
_companyEmployeeService.ExportCompanyEmployees(filePath, data.Results);
//byte[] bytes = result.ToArray();
//return File(bytes, "text/xls", "出错信息.xlsx");
return(File(filePath, "text/xls", "出错信息.xlsx"));
}
catch (Exception ex)
{
ErrorNotification(ex);
return(RedirectToAction("Import"));
}
}
public IActionResult Import(int year, int month, IFormFile excelFile)
{
ViewBag.ReturnUrl = $"/Security/Index?year={year}&month={month}";
ViewBag.ImportUrl = $"/Security/Import?year={year}&month={month}";
// 基础验证
if (excelFile == null || Path.GetExtension(excelFile.FileName) != ".xlsx")
{
return(Content("不受支持的文件"));
}
// 获取原始数据
MonthReportSearchParam param = new MonthReportSearchParam();
param.Month = month;
param.Year = year;
MonthReportService service = new MonthReportService();
var source = service.GetSecurityList(param);
if (source.Count() == 0)
{
return(Content("当前日期原数据为空"));
}
// 解析Excel中的记录
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string folderName = Path.Combine(sWebRootFolder, "reports");
if (!Directory.Exists(folderName))
{
Directory.CreateDirectory(folderName);
}
FileInfo file = new FileInfo(Path.Combine(folderName, excelFile.FileName));
try
{
using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
{
excelFile.CopyTo(fs);
fs.Flush();
}
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
if (ColCount != 8)
{
return(Content("不合法的数据列"));
}
List <ImportResultViewModel> result = new List <ImportResultViewModel>();
// 获取转换后的数据列表
List <MonthReportEntity> content = new List <MonthReportEntity>();
for (int row = 2; row <= rowCount; row++)
{
Convert(worksheet, row);
}
// 内部方法，单条处理数据
void Convert(ExcelWorksheet sheet, int idx)
{
try
{
var entity = new MonthReportEntity();
int id = 0;
decimal fwf = 0;
int y = 0;
int m = 0;
int.TryParse(sheet.Cells[idx, 2].Value.ToString(), out y);
int.TryParse(sheet.Cells[idx, 3].Value.ToString(), out m);
int.TryParse(sheet.Cells[idx, 1].Value.ToString(), out id);
decimal.TryParse(sheet.Cells[idx, 8].Value.ToString(), out fwf);
entity.Id = id;
entity.FWF = fwf;
entity.Year = y;
entity.Month = m;
var deptName = sheet.Cells[idx, 4].Value.ToString();
var salerName = sheet.Cells[idx, 6].Value.ToString();
var r = new ImportResultViewModel
{
Id = id,
DeptName = deptName,
SalerName = salerName,
Result = "清洗失败",
Remark = "源数据中不存在此记录"
};
var s = source.Where(mr => mr.Id == id).SingleOrDefault();
if (s == null)
{
r.Result = "数据清洗失败";
r.Remark = "元数据中不存在此记录";
}
else if (s.Year != year || s.Month != month)
{
r.Result = "数据清洗失败";
r.Remark = "年度和月度与源数据不符";
}
else if (s.SalerName != salerName.Trim() || s.DeptName != deptName.Trim())
{
r.Result = "数据清洗失败";
r.Remark = "部门和业务员不匹配";
}
else
{
var submitResult = service.SecuritySubmit(entity, AppUser);
if (submitResult.Code == 200)
{
r.Result = "更新成功";
r.Remark = "";
}
else
{
r.Result = "数据更新失败";
r.Remark = submitResult.Message;
}
}
result.Add(r);
}
catch (Exception ex)
{
result.Add(new ImportResultViewModel
{
Id = 0,
DeptName = "",
SalerName = "",
Result = "系统错误",
Remark = $"第{idx}条记录发生错误：{ex.Message}"
});
}
}
return(View("Result", result));
}
}
catch (Exception ex)
{
return(Content(ex.Message));
}
}
public ImportResultViewModel ImportData(string tableName, string dataName, List <Map4D> map4Ds)
{
var watch = Stopwatch.StartNew();
int r = 0, i = 0;
foreach (var mapdata in map4Ds)
{
var xyvn2000 = Coordinate.xyvn2000_2_blwgs84(mapdata.LATITUDE_HOTRONGCAY, mapdata.LONGITUDE_HOTRONGCAY, MapParams.pdo, MapParams.pphut, MapParams.zone);
#region query
var query = $"use {dataName}\n" + "INSERT INTO " + tableName + "(ID_DONVIHANHCHINH ,TEN_CAYXANH,THONGTINCHITIET,TEN_LOAICAYXANH,BANKINHTANLA , IS_BOVIEN , " +
"IS_PLANT , IS_ONGNUOC , CAYXANH , IS_CAOHONVIAHE , IS_ONGCONG , ID_HINHDANGHO , COUNT , " +
"TENCONGTRINHGANNHAT,ID_TINHTRANGSINHTRUONG,LONGITUDE_HOTRONGCAY,NAMBANGIAOCONGTRINH,TEN_PHANLOAIQUYHOACH,IMAGE," +
"DUONGKINHGOC,ID_LOAICAYXANH,TEN_CHUNGLOAICAYXANH,DIENTICHTANLA,ID_CAYXANH,CHIEUCAOVUTNGON,GHICHU," +
"ID_HOTRONGCAY,DUONGPHO,ID_KHUVUCCAYXANH, KHUVUCCAYXANH , NAMTRONGCAY, DONVIHANHCHINH,SOLANCAPNHAT," +
"ID_NHOMNGUYCO,IS_DAYDIEN,NGAYTAO,ID_LOAIBOVIEN,SONHA,KICHTHUOCHO , DIENTICHHO , IS_BORAO , TRANGTHAIBOVIEN " +
", LATITUDE_HOTRONGCAY , ENDX , STARTX , ENDY , STARTY , LOAIBOVIEN , KHOANGCACHHOVIA , " +
"MA_CAYXANH,ID_TRANGTHAIBOVIEN,TEN_NHOMNGUYCO,HINHDANGHO, IS_CAPNGAM, ID_NHOMCHUNGLOAI , MA_HOTRONGCAY, " +
"ID_PHANLOAIQUYHOACH,TEN_TINHTRANGSINHTRUONG)" +
"VALUES(@ID_DONVIHANHCHINH , @TEN_CAYXANH,@THONGTINCHITIET,@TEN_LOAICAYXANH,@BANKINHTANLA , @IS_BOVIEN , " +
"@IS_PLANT , @IS_ONGNUOC , @CAYXANH , @IS_CAOHONVIAHE , @IS_ONGCONG , @ID_HINHDANGHO , @COUNT , " +
"@TENCONGTRINHGANNHAT,@ID_TINHTRANGSINHTRUONG,@LONGITUDE_HOTRONGCAY,@NAMBANGIAOCONGTRINH,@TEN_PHANLOAIQUYHOACH,@IMAGE," +
"@DUONGKINHGOC,@ID_LOAICAYXANH,@TEN_CHUNGLOAICAYXANH,@DIENTICHTANLA,@ID_CAYXANH,@CHIEUCAOVUTNGON,@GHICHU," +
"@ID_HOTRONGCAY,@DUONGPHO,@ID_KHUVUCCAYXANH, @KHUVUCCAYXANH , @NAMTRONGCAY, @DONVIHANHCHINH,@SOLANCAPNHAT," +
"@ID_NHOMNGUYCO,@IS_DAYDIEN,@NGAYTAO,@ID_LOAIBOVIEN,@SONHA,@KICHTHUOCHO , @DIENTICHHO , @IS_BORAO , @TRANGTHAIBOVIEN " +
", @LATITUDE_HOTRONGCAY , @ENDX , @STARTX , @ENDY , @STARTY , @LOAIBOVIEN , @KHOANGCACHHOVIA , " +
"@MA_CAYXANH,@ID_TRANGTHAIBOVIEN,@TEN_NHOMNGUYCO,@HINHDANGHO, @IS_CAPNGAM, @ID_NHOMCHUNGLOAI , @MA_HOTRONGCAY, " +
"@ID_PHANLOAIQUYHOACH,@TEN_TINHTRANGSINHTRUONG)";
#endregion
//var query = $"use {dataName}\n" + "INSERT INTO " + tableName + "(ID_DONVIHANHCHINH ,TEN_CAYXANH,THONGTINCHITIET,TEN_LOAICAYXANH,BANKINHTANLA , IS_BOVIEN , " +
// "IS_PLANT , IS_ONGNUOC , CAYXANH , IS_CAOHONVIAHE , IS_ONGCONG , ID_HINHDANGHO , COUNT , " +
// "TENCONGTRINHGANNHAT,ID_TINHTRANGSINHTRUONG,LONGITUDE_HOTRONGCAY,NAMBANGIAOCONGTRINH,TEN_PHANLOAIQUYHOACH,IMAGE," +
// "DUONGKINHGOC,ID_LOAICAYXANH,TEN_CHUNGLOAICAYXANH,DIENTICHTANLA,ID_CAYXANH,CHIEUCAOVUTNGON,GHICHU," +
// "ID_HOTRONGCAY,DUONGPHO,ID_KHUVUCCAYXANH, KHUVUCCAYXANH , NAMTRONGCAY, DONVIHANHCHINH,SOLANCAPNHAT," +
// "ID_NHOMNGUYCO,IS_DAYDIEN,NGAYTAO,ID_LOAIBOVIEN,SONHA,KICHTHUOCHO , DIENTICHHO , IS_BORAO , TRANGTHAIBOVIEN " +
// ", LATITUDE_HOTRONGCAY , ENDX , STARTX , ENDY , STARTY , LOAIBOVIEN , KHOANGCACHHOVIA , " +
// "MA_CAYXANH,ID_TRANGTHAIBOVIEN,TEN_NHOMNGUYCO,HINHDANGHO, IS_CAPNGAM, ID_NHOMCHUNGLOAI , MA_HOTRONGCAY, " +
// "ID_PHANLOAIQUYHOACH,TEN_TINHTRANGSINHTRUONG)" +
// $"VALUES({mapdata.ID_DONVIHANHCHINH} , '{mapdata.TEN_CAYXANH}','{mapdata.THONGTINCHITIET}','{mapdata.TEN_LOAICAYXANH}',{mapdata.BANKINHTANLA }, {(mapdata.IS_BOVIEN?1:0 )}, " +
// $"{(mapdata.IS_PLANT?1:0)} , {(mapdata.IS_ONGNUOC?1:0)} , '{mapdata.CAYXANH}' , {(mapdata.IS_CAOHONVIAHE?1:0)} , {(mapdata.IS_ONGCONG?1:0)} , '{mapdata.ID_HINHDANGHO}' , {mapdata.COUNT }, " +
// $"'{mapdata.TENCONGTRINHGANNHAT}',{mapdata.ID_TINHTRANGSINHTRUONG},{mapdata.LONGITUDE_HOTRONGCAY},{mapdata.NAMBANGIAOCONGTRINH},'{mapdata.TEN_PHANLOAIQUYHOACH}','{mapdata.IMAGE}'," +
// $"{mapdata.DUONGKINHGOC},{mapdata.ID_LOAICAYXANH},'{mapdata.TEN_CHUNGLOAICAYXANH}',{mapdata.DIENTICHTANLA},{mapdata.ID_CAYXANH},{mapdata.CHIEUCAOVUTNGON},'{mapdata.GHICHU}'," +
// $"{mapdata.ID_HOTRONGCAY},'{mapdata.DUONGPHO}',{mapdata.ID_KHUVUCCAYXANH}, '{mapdata.KHUVUCCAYXANH }', {mapdata.NAMTRONGCAY}, '{mapdata.DONVIHANHCHINH}',{mapdata.SOLANCAPNHAT}," +
// $"{mapdata.ID_NHOMNGUYCO},{(mapdata.IS_DAYDIEN?1:0)},{mapdata.NGAYTAO},{mapdata.ID_LOAIBOVIEN},'{mapdata.SONHA}',{mapdata.KICHTHUOCHO }, {mapdata.DIENTICHHO }, {(mapdata.IS_BORAO?1:0) }, '{mapdata.TRANGTHAIBOVIEN}', " +
// $" {mapdata.LATITUDE_HOTRONGCAY }, {mapdata.ENDX }, {mapdata.STARTX }, {mapdata.ENDY }, {mapdata.STARTY }, '{mapdata.LOAIBOVIEN }', {mapdata.KHOANGCACHHOVIA }, " +
// $"'{mapdata.MA_CAYXANH}',{mapdata.ID_TRANGTHAIBOVIEN},'{mapdata.TEN_NHOMNGUYCO}','{mapdata.HINHDANGHO}', {(mapdata.IS_CAPNGAM?1:0)}, {mapdata.ID_NHOMCHUNGLOAI }, '{mapdata.MA_HOTRONGCAY}', " +
// $"{mapdata.ID_PHANLOAIQUYHOACH},'{mapdata.TEN_TINHTRANGSINHTRUONG}')";
// SqlCommand command = new SqlCommand(query, ConnectDatabase.connect);
#region addParams
SqlCommand command = new SqlCommand(query, ConnectDatabase.connect);
command.Parameters.Add(new SqlParameter("ID_DONVIHANHCHINH", mapdata.ID_DONVIHANHCHINH));
command.Parameters.Add(new SqlParameter("TEN_CAYXANH", mapdata.TEN_CAYXANH));
command.Parameters.Add(new SqlParameter("THONGTINCHITIET", mapdata.THONGTINCHITIET));
command.Parameters.Add(new SqlParameter("TEN_LOAICAYXANH", mapdata.TEN_LOAICAYXANH));
command.Parameters.Add(new SqlParameter("BANKINHTANLA", mapdata.BANKINHTANLA));
command.Parameters.Add(new SqlParameter("IS_BOVIEN", mapdata.IS_BOVIEN));
command.Parameters.Add(new SqlParameter("IS_PLANT", mapdata.IS_PLANT));
command.Parameters.Add(new SqlParameter("IS_ONGNUOC", mapdata.IS_ONGNUOC));
command.Parameters.Add(new SqlParameter("CAYXANH", mapdata.CAYXANH));
command.Parameters.Add(new SqlParameter("IS_CAOHONVIAHE", mapdata.IS_CAOHONVIAHE));
command.Parameters.Add(new SqlParameter("IS_ONGCONG", mapdata.IS_ONGCONG));
command.Parameters.Add(new SqlParameter("ID_HINHDANGHO", mapdata.ID_HINHDANGHO));
command.Parameters.Add(new SqlParameter("COUNT", mapdata.COUNT));
command.Parameters.Add(new SqlParameter("TENCONGTRINHGANNHAT", mapdata.TENCONGTRINHGANNHAT));
command.Parameters.Add(new SqlParameter("ID_TINHTRANGSINHTRUONG", mapdata.ID_TINHTRANGSINHTRUONG));
command.Parameters.Add(new SqlParameter("LONGITUDE_HOTRONGCAY", xyvn2000[1]));
command.Parameters.Add(new SqlParameter("NAMBANGIAOCONGTRINH", mapdata.NAMBANGIAOCONGTRINH));
command.Parameters.Add(new SqlParameter("TEN_PHANLOAIQUYHOACH", mapdata.TEN_PHANLOAIQUYHOACH));
command.Parameters.Add(new SqlParameter("IMAGE", mapdata.IMAGE));
command.Parameters.Add(new SqlParameter("DUONGKINHGOC", mapdata.DUONGKINHGOC));
command.Parameters.Add(new SqlParameter("ID_LOAICAYXANH", mapdata.ID_LOAICAYXANH));
command.Parameters.Add(new SqlParameter("TEN_CHUNGLOAICAYXANH", mapdata.TEN_CHUNGLOAICAYXANH));
command.Parameters.Add(new SqlParameter("DIENTICHTANLA", mapdata.DIENTICHTANLA));
command.Parameters.Add(new SqlParameter("ID_CAYXANH", mapdata.ID_CAYXANH));
command.Parameters.Add(new SqlParameter("CHIEUCAOVUTNGON", mapdata.CHIEUCAOVUTNGON));
command.Parameters.Add(new SqlParameter("GHICHU", mapdata.GHICHU));
command.Parameters.Add(new SqlParameter("ID_HOTRONGCAY", mapdata.ID_HOTRONGCAY));
command.Parameters.Add(new SqlParameter("DUONGPHO", mapdata.DUONGPHO));
command.Parameters.Add(new SqlParameter("ID_KHUVUCCAYXANH", mapdata.ID_KHUVUCCAYXANH));
command.Parameters.Add(new SqlParameter("KHUVUCCAYXANH", mapdata.KHUVUCCAYXANH));
command.Parameters.Add(new SqlParameter("NAMTRONGCAY", mapdata.NAMTRONGCAY));
command.Parameters.Add(new SqlParameter("DONVIHANHCHINH", mapdata.DONVIHANHCHINH));
command.Parameters.Add(new SqlParameter("SOLANCAPNHAT", mapdata.SOLANCAPNHAT));
command.Parameters.Add(new SqlParameter("ID_NHOMNGUYCO", mapdata.ID_NHOMNGUYCO));
command.Parameters.Add(new SqlParameter("IS_DAYDIEN", mapdata.IS_DAYDIEN));
command.Parameters.Add(new SqlParameter("NGAYTAO", mapdata.NGAYTAO));
command.Parameters.Add(new SqlParameter("ID_LOAIBOVIEN", mapdata.ID_LOAIBOVIEN));
command.Parameters.Add(new SqlParameter("SONHA", mapdata.SONHA));
command.Parameters.Add(new SqlParameter("KICHTHUOCHO", mapdata.KICHTHUOCHO));
command.Parameters.Add(new SqlParameter("HINHDANGHO", mapdata.HINHDANGHO));
command.Parameters.Add(new SqlParameter("DIENTICHHO", mapdata.DIENTICHHO));
command.Parameters.Add(new SqlParameter("MA_HOTRONGCAY", mapdata.MA_HOTRONGCAY));
command.Parameters.Add(new SqlParameter("IS_BORAO", mapdata.IS_BORAO));
command.Parameters.Add(new SqlParameter("TRANGTHAIBOVIEN", mapdata.TRANGTHAIBOVIEN));
command.Parameters.Add(new SqlParameter("LATITUDE_HOTRONGCAY", xyvn2000[0]));
command.Parameters.Add(new SqlParameter("ENDX", mapdata.ENDX));
command.Parameters.Add(new SqlParameter("STARTX", mapdata.STARTX));
command.Parameters.Add(new SqlParameter("ENDY", mapdata.ENDY));
command.Parameters.Add(new SqlParameter("STARTY", mapdata.STARTY));
command.Parameters.Add(new SqlParameter("LOAIBOVIEN", mapdata.LOAIBOVIEN));
command.Parameters.Add(new SqlParameter("KHOANGCACHHOVIA", mapdata.KHOANGCACHHOVIA));
command.Parameters.Add(new SqlParameter("MA_CAYXANH", mapdata.MA_CAYXANH));
command.Parameters.Add(new SqlParameter("ID_TRANGTHAIBOVIEN", mapdata.ID_TRANGTHAIBOVIEN));
command.Parameters.Add(new SqlParameter("TEN_NHOMNGUYCO", mapdata.TEN_NHOMNGUYCO));
command.Parameters.Add(new SqlParameter("IS_CAPNGAM", mapdata.IS_CAPNGAM));
command.Parameters.Add(new SqlParameter("ID_NHOMCHUNGLOAI", mapdata.ID_NHOMCHUNGLOAI));
command.Parameters.Add(new SqlParameter("ID_PHANLOAIQUYHOACH", mapdata.ID_PHANLOAIQUYHOACH));
command.Parameters.Add(new SqlParameter("TEN_TINHTRANGSINHTRUONG", mapdata.TEN_TINHTRANGSINHTRUONG));
#endregion
r = command.ExecuteNonQuery();
i++;
}
watch.Stop();
try
{
var elapsedMs = watch.ElapsedMilliseconds;
if (i != 0)
{
var result = new ImportResultViewModel
{
TimeQuery = elapsedMs,
RowQuery = $"Thêm mới thành công {i} dòng"
};
return(result);
}
else
{
var result = new ImportResultViewModel
{
TimeQuery = elapsedMs,
RowQuery = $"Lỗi"
};
return(result);
}
}
catch (SqlException e)
{
Debug.Write(e);
return(null);
}
}
public async Task <ActionResult> Import(ImportViewModel model)
{
var stopwatch = new Stopwatch();
stopwatch.Start();
try
{
if (string.IsNullOrEmpty(model.Username) || string.IsNullOrEmpty(model.Password))
{
return(new HttpStatusCodeResult(HttpStatusCode.Unauthorized, "Username or password empty."));
}
var signInStatus = await SignInManager.PasswordSignInAsync(model.Username, model.Password, false, false);
switch (signInStatus)
{
case SignInStatus.Success:
break;
case SignInStatus.LockedOut:
return(new HttpStatusCodeResult(HttpStatusCode.Unauthorized, "User locked."));
default:
return(new HttpStatusCodeResult(HttpStatusCode.Unauthorized, "Username or password invalid."));
}
var currentUser = await Db.Users.FirstAsync(x => x.UserName == model.Username);
var currentClient = currentUser.Client;
var account = await Db.Accounts.FirstOrDefaultAsync(x => x.ClientId == currentClient.Id && x.Name == model.Account);
if (account == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest, "Invalid account."));
}
ImporterType importer;
if (!Enum.TryParse(model.Importer, true, out importer))
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest, "Invalid importer."));
}
if (model.File == null || model.File.Length <= 0)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest, "Empty file."));
}
var result = new ImportResultViewModel();
var importLog = new ImportLog
{
AccountId = account.Id,
UserId = currentUser.Id,
Date = DateTime.UtcNow,
Type = ImportLogType.AutomaticOnClient
};
try
{
using (var stream = new MemoryStream(model.File))
{
using (var reader = new StreamReader(stream, Encoding.UTF8))
{
var concreteImporter = new ImporterFactory().GetImporter(reader, importer);
var importResult = await concreteImporter.LoadFileAndImport(Db, currentClient.Id, account.Id, new RulesApplier());
result.IgnoredCount = importResult.DuplicateLinesCount;
result.ImportedCount = importResult.NewLinesCount;
importLog.LinesDuplicatesCount = importResult.DuplicateLinesCount;
importLog.LinesFoundCount = importResult.DuplicateLinesCount + importResult.NewLinesCount;
importLog.LinesImportedCount = importResult.NewLinesCount;
}
}
}
catch (Exception ex)
{
importLog.Log = ex.Message;
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest, "Import failed: " + ex.Message));
}
finally
{
// save to import log
stopwatch.Stop();
importLog.Milliseconds = (int)stopwatch.ElapsedMilliseconds;
Db.ImportLog.Add(importLog);
await Db.SaveChangesAsync();
}
return(Json(result));
}
catch (Exception ex)
{
return(new HttpStatusCodeResult(HttpStatusCode.InternalServerError, ex.Message));
}
}
