public List <SaveResult <UserDTO> > ExecuteImport(UserImportDTO importDTO, DateTime dateStamp) { using (var transaction = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromHours(1))) { var list = CreateListFromExcelBase64(importDTO); var result = GetSaveResults(list, dateStamp); Db.SaveChanges(); transaction.Complete(); return(result); } }
public List <SaveResult <UserDTO> > Import(UserImportDTO userImportDTO) { using (var userCreateHandler = new UserCreateHandler(db, user, new UserValidator(), new UserFactory(db, user), new UserQuery(db), new AccessControl(user))) { using (var transaction = new TransactionScope()) { List <SaveResult <UserDTO> > saveResults = new List <SaveResult <UserDTO> >(); var stream = new CSVConverter().GetStreamFromBase64(userImportDTO.File); using (var reader = new StreamReader(stream)) { int current = 0; var jabatanQuery = new KategoriJabatan.Queries.KategoriJabatanQuery(db); while (!reader.EndOfStream) { var line = reader.ReadLine(); if (current == 0) { current++; continue; } var values = line.Split(';'); UserDTO userDTO = CreateUserDTO(jabatanQuery, values); var saveResult = userCreateHandler.Save(userDTO: userDTO, dateStamp: DateTime.UtcNow); saveResults.Add(new SaveResult <UserDTO>() { Message = saveResult.Message, Model = saveResult.Model?.Model, Success = saveResult.Success, ValidationResult = saveResult.ValidationResult }); current++; } } transaction.Complete(); return(saveResults); } } }
public List <UserDTO> CreateListFromExcelBase64(UserImportDTO importDTO) { var base64 = importDTO.File; base64 = base64.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", ""); var wb = ExcelConverter.FromBase64(base64); var sheet = wb.Worksheet("UserUpload"); var nonEmptyRowCount = sheet.RowsUsed().Count() + 1; var userQuery = new UserQuery(Db); List <UserDTO> userList = new List <UserDTO>(); var jabatanQuery = new KategoriJabatanQuery(Db); MaritalStatusQuery maritalStatusQuery = new MaritalStatusQuery(); GenderQuery genderQuery = new GenderQuery(); CategoryContractQuery categoryContractQuery = new CategoryContractQuery(); ReligionQuery religionQuery = new ReligionQuery(); //first index is 1, and the first one is header title for (int i = 2; i < nonEmptyRowCount; i++) { var row = sheet.Row(i); //Excel value var xNomor = row.Cell(1).Value; var xUserName = row.Cell(2).Value; var XJabatanName = row.Cell(3).Value; var xFullName = row.Cell(4).Value; var xTglLahir = row.Cell(5).Value; var xKtp = row.Cell(6).Value; var xReligion = row.Cell(7).Value; var xCategoryContract = row.Cell(8).Value; var xProject = row.Cell(9).Value; var xGender = row.Cell(10).Value; var xMaritalStatus = row.Cell(11).Value; var xNpwp = row.Cell(12).Value; var xBpjs = row.Cell(13).Value; var xJoinDate = row.Cell(14).Value; var xContactNumber = row.Cell(15).Value; var xG1EmailId = row.Cell(16).Value; var xPersonalEmail = row.Cell(17).Value; var xAddress = row.Cell(18).Value; var xBankName = row.Cell(19).Value; var xAccountNumber = row.Cell(20).Value; var xSalary = row.Cell(21).Value; var xRemark = row.Cell(22).Value; var xStatus = row.Cell(23).Value; var defaultEmptyDate = new DateTime(1990, 01, 01); var dateContainer = new DateTime(1990, 01, 01); double doubleContainer = 0; int intContainer = 0; var nomor = row.Cell(1).Value; var userName = xUserName == null ? "" : xUserName.ToString(); var jabatanName = XJabatanName == null ? "" : XJabatanName.ToString(); var fullName = xFullName == null ? "" : xFullName.ToString(); var tglLahir = xTglLahir == null ? defaultEmptyDate : DateTime.TryParse(xTglLahir.ToString(), out dateContainer) == true ? dateContainer : defaultEmptyDate; var ktp = xKtp == null ? "" : xKtp.ToString(); var religion = xReligion == null ? "" : xReligion.ToString(); var categoryContract = xCategoryContract == null ? "" : xCategoryContract.ToString(); var project = xProject == null ? "" : xProject.ToString(); var gender = xGender == null ? "" : xGender.ToString(); var maritalStatus = xMaritalStatus == null ? "" : xMaritalStatus.ToString(); var npwp = xNpwp == null ? "" : xNpwp.ToString(); var bpjs = xBpjs == null ? "" : xBpjs.ToString(); var joinDate = xJoinDate == null ? defaultEmptyDate : DateTime.TryParse(xJoinDate.ToString(), out dateContainer) == true ? dateContainer : defaultEmptyDate; var contactNumber = xContactNumber == null ? "" : xContactNumber.ToString(); var g1EmailId = xG1EmailId == null ? "" : xG1EmailId.ToString(); var personalEmail = xPersonalEmail == null ? "" : xPersonalEmail.ToString(); var address = xAddress == null ? "" : xAddress.ToString(); var bankName = xBankName == null ? "" : xBankName.ToString(); var accountNumber = xAccountNumber == null ? "" : xAccountNumber.ToString(); var salary = xSalary == null ? 0 : double.TryParse(xSalary.ToString(), out doubleContainer) == true ? doubleContainer : 0; var remark = xRemark == null ? "" : xRemark.ToString(); var status = xStatus == null ? 1 : int.TryParse(xStatus.ToString(), out intContainer) == true ? intContainer : 1; var userPk = 0; var userDetailFk = 0; //get by itsname first; var userDtoOnDb = userQuery.GetByUsername(userName); var userCode = userName; if (userDtoOnDb != null) { userPk = userDtoOnDb.User_PK; userDetailFk = userDtoOnDb.UserDetail_FK; } var jabatanFk = 0; var jabatan = jabatanQuery.GetByTitle(jabatanName); if (jabatan != null) { jabatanFk = jabatan.KategoriJabatan_PK; } var newUser = new UserDTO() { User_PK = userPk, UserDetail_FK = userDetailFk, UserDetail_PK = userDetailFk, UserCode = userCode, Name = fullName, TglLahir = tglLahir, NoKTP = ktp, NoHP = contactNumber, Email = g1EmailId, PersonalEmail = personalEmail, Address = address, Description = remark, Username = userName, KategoriJabatan_FK = jabatanFk, KategoriJabatanTitle = jabatanName, //Password = user.Password, Status_FK = status, Password = "******", AccountNumber = accountNumber, BankName = bankName, BPJS = bpjs, ////WAIT CategoryContract = categoryContract == "" ? 0 : categoryContractQuery.GetByName(categoryContract).CategoryContract_PK, Gender = gender == "" ? 0 : genderQuery.GetByName(gender).Gender_PK, JoinDate = joinDate, MaritalStatus = maritalStatus == "" ? 0 : maritalStatusQuery.GetByName(maritalStatus).MaritalStatus_PK, NPWP = npwp, Project = project == "" ? 0 : Convert.ToInt16(project.Split('-')[0]), Religion = religion == "" ? 0 : religionQuery.GetByName(religion).Religion_PK, Salary = salary }; userList.Add(newUser); } return(userList); }