public ActionResult UploadExcelMembersFile(string fileType) { Dictionary <int, string> returnMsgDictinary = new Dictionary <int, string>(); FileHandleDetails fileHandleDetails = new FileHandleDetails(); SetDictinaryReturnMsg(returnMsgDictinary); var context = System.Web.HttpContext.Current.Request; string fileName = null; string pathDirectory = null; string validateMsg; //Core.Services.Logger.InsertHovalaLog(LogType.Debug, "UploadExcelhovalaFile", System.Web.HttpContext.Current.User.Identity.Name); if (context.Files != null && context.Files.Count > 0) { try { HttpPostedFile file = context.Files["file"]; if (file != null) { //This one take a lot of time if (Enum.IsDefined(typeof(ExcelFileType), fileType) == false) { fileHandleDetails.FileUploadMsg = returnMsgDictinary[(int)FileReturnMsg.FileTypeIncorrect]; return(Content(new JavaScriptSerializer().Serialize(fileHandleDetails))); } if (CheckIsUploadFileExcelFormat(file) == false) { fileHandleDetails.FileUploadMsg = returnMsgDictinary[(int)FileReturnMsg.FileExtensionIncorrect]; return(Content(new JavaScriptSerializer().Serialize(fileHandleDetails))); } //If file is OK saving it to folder switch ((ExcelFileType)Enum.Parse(typeof(ExcelFileType), fileType)) { case ExcelFileType.CoecoMembers: pathDirectory = ConfigurationManager.AppSettings["SaveUploadedMembersFilePath"]; break; } fileName = CheckIsFilesSameByByteAndSave(fileType, file, pathDirectory); if (fileName == null) { fileName = SaveUploadExcelFile(System.Web.HttpContext.Current.User.Identity.Name, file, pathDirectory, fileType); } validateMsg = _fileHandle.ReadMembersExcelFile(file.FileName, pathDirectory, fileName, System.Web.HttpContext.Current.User.Identity.Name, fileHandleDetails); if (validateMsg == FileReturnMsg.DuplicatedRowsProblem.ToString()) { return(SetDataForReturnAndDeleteFileWithLog(returnMsgDictinary[(int)FileReturnMsg.DuplicatedRowsProblem], fileHandleDetails, false, true, fileName)); } else if (validateMsg == FileReturnMsg.FileWasHandle.ToString()) { validateMsg = returnMsgDictinary[(int)FileReturnMsg.FileWasHandle]; } else if (validateMsg == FileReturnMsg.MainUploadFileFailed.ToString()) { validateMsg = returnMsgDictinary[(int)FileReturnMsg.MainUploadFileFailed]; } return(SetDataForReturnAndDeleteFileWithLog(validateMsg, fileHandleDetails, true)); } else { return(SetDataForReturnAndDeleteFileWithLog(returnMsgDictinary[(int)FileReturnMsg.NoFileToUpload], fileHandleDetails, false)); } } catch (Exception ex) { _loggerService.InsertLogMessage(LogType.Error.ToString(), $"Failed on UploadExcelMembersFile {ex.ToString()}", System.Web.HttpContext.Current.User.Identity.Name); return(SetDataForReturnAndDeleteFileWithLog(returnMsgDictinary[(int)FileReturnMsg.ErrorUploadFile], fileHandleDetails, false, true, fileName)); } } return(SetDataForReturnAndDeleteFileWithLog(returnMsgDictinary[(int)FileReturnMsg.NoFileToUpload], fileHandleDetails, false)); }
public string ReadMembersExcelFile(string uploadedFileName, string basePathDirectory, string fileName, string userIdentity, FileHandleDetails fileHandleDetails) { var keyRowValueList = new List <string>(); var membersTableRowList = new List <Member>(); var membersFileErrorToBulk = new List <CoecoErrorMemberBulk>(); var unitsID = _dataAccessService.GetAllUnits().Select(x => x.ID).ToList(); var permissionsProfilesID = _dataAccessService.GetAllPermissionsProfile().Select(x => x.ID).ToList(); using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(fileName))) { ExcelWorksheet myWorksheet = xlPackage.Workbook.Worksheets.First(); //select sheet here int totalRows = myWorksheet.Dimension.End.Row; int totalColumns = myWorksheet.Dimension.End.Column; int startRow = myWorksheet.Dimension.Start.Row; List <KeyValuePair <string, string> > errorMeshartimTableRowList = new List <KeyValuePair <string, string> >(); for (int rowNum = startRow; rowNum <= totalRows; rowNum++) //selet starting row here { ExcelRange range = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns]; if (range.Any(c => c.Value != null)) { try { var errMessage = string.Empty; var TZ = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.TZ].Text; var firstName = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.FirstName].Text; var lastName = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.LastName].Text; var email = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.Email].Text; var phoneNumber = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.PhoneNumber].Text; var role = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.Role].Text; var unitID = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.UnitID].Text; var permissionsProfileID = myWorksheet.Cells[rowNum, (int)ExcelMembersCol.PermissionsProfileID].Text; errMessage = _fileValidation.CheckTZFromExecl(TZ); if (errMessage == string.Empty) { errMessage = _fileValidation.CheckUnitOrProfilePermmisionIsExists(unitID, unitsID, TableName.Unit.ToString()); } if (errMessage == string.Empty) { errMessage = _fileValidation.CheckUnitOrProfilePermmisionIsExists(permissionsProfileID, permissionsProfilesID, TableName.PermissionsProfile.ToString()); } DateTime now = DateTime.Now; if (errMessage == string.Empty) { keyRowValueList.Add(TZ); Member memberTableRow = new Member { TZ = TZ, FirstName = firstName, LastName = lastName, Email = email, PhoneNumber = phoneNumber, Role = role, UnitID = int.Parse(unitID), PermissionsProfileID = int.Parse(permissionsProfileID) }; membersTableRowList.Add(memberTableRow); } else { membersFileErrorToBulk.Add(new CoecoErrorMemberBulk() { CreatedBy = userIdentity, CreatedOn = now, Disable = false, UpdatedBy = userIdentity, UpdatedOn = now, ErrorMsg = errMessage, FilesUploadName = fileName, TZ = TZ, }); } } catch (Exception ex) { _loggerService.InsertLogMessage(LogType.Error.ToString(), $"Failed on ReadMembersExcelFile {ex.ToString()}", userIdentity); } } } } SetDefaultValues(membersTableRowList, userIdentity); if (CheckDuplicated(keyRowValueList) == false) { var membersToInsertBulk = membersTableRowList.ToList().Select(d => new CoecoMemberBulk { ID = d.ID, TZ = d.TZ, Email = d.Email, Role = d.Role, PermissionsProfileID = d.PermissionsProfileID, UnitID = d.UnitID, PhoneNumber = d.PhoneNumber, CreatedOn = d.CreatedOn, CreatedBy = d.CreatedBy, UpdatedOn = d.UpdatedOn, UpdatedBy = d.UpdatedBy, Disable = d.Disable, FirstName = d.FirstName, LastName = d.LastName, }).ToList(); bool isMeshartimUploadSuceed = true; if (membersToInsertBulk.Any()) { isMeshartimUploadSuceed = BulkInsert.Copy <CoecoMemberBulk>(membersToInsertBulk, "dbo.SYS_TBL_Members", "#Members", "Members added: "); } if (membersFileErrorToBulk.Any()) { BulkInsert.Copy <CoecoErrorMemberBulk>(membersFileErrorToBulk, "dbo.SYS_TBL_MembersFileError", "#MembersFileError", "MembersFileError added: "); } string messageString = FileReturnMsg.FileWasHandle.ToString(); if (!(isMeshartimUploadSuceed)) { messageString = FileReturnMsg.MainUploadFileFailed.ToString(); } return(messageString); } else { return(FileReturnMsg.DuplicatedRowsProblem.ToString()); } }
private ActionResult SetDataForReturnAndDeleteFileWithLog(string fileUploadMessageString, FileHandleDetails fileHandleDetails, bool isFileContentOk, bool isDelete = false, string fileName = null) { if (isDelete) { if (System.IO.File.Exists(fileName)) { System.IO.File.Delete(fileName); } } fileHandleDetails.FileUploadMsg = fileUploadMessageString; fileHandleDetails.FileContentOk = isFileContentOk; var userIdentity = System.Web.HttpContext.Current.User.Identity.Name; return(Content(new JavaScriptSerializer().Serialize(fileHandleDetails))); }