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)));
}
