public ActionResult UploadCrewPaxFile(int portCallId)
{
try
{
var file = Request.Form.Files.FirstOrDefault();
var folderName = "Upload";
var rootPath = _hostingEnvironment.WebRootPath;
var newPath = Path.Combine(rootPath, folderName);
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
var fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
var fullPath = Path.Combine(newPath, fileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
var Crew = new List <PersonOnBoard>();
var Pax = new List <PersonOnBoard>();
var fi = new FileInfo(fullPath);
using (var package = new ExcelPackage(fi))
{
var crewSheetDefinition = new CrewSheet();
var paxSheetDefinition = new PaxSheet();
var workbook = package.Workbook;
var crewSheet = workbook.Worksheets[crewSheetDefinition.Sheetname];
var paxSheet = workbook.Worksheets[paxSheetDefinition.Sheetname];
var crewTypeId = _context.PersonOnBoardType.Where(pobt => pobt.EnumValue == PERSON_ON_BOARD_TYPE_ENUM.CREW.ToString()).FirstOrDefault().PersonOnBoardTypeId;
var paxTypeId = _context.PersonOnBoardType.Where(pobt => pobt.EnumValue == PERSON_ON_BOARD_TYPE_ENUM.PAX.ToString()).FirstOrDefault().PersonOnBoardTypeId;
Crew = ConvertToCrew(crewSheet, crewSheetDefinition, crewTypeId, portCallId);
Pax = ConvertToPax(paxSheet, paxSheetDefinition, paxTypeId, portCallId);
var crewAndPax = Crew;
crewAndPax.AddRange(Pax);
var crewAndPaxWithErrors = new List <PersonOnBoard>();
var saved = false;
if (crewAndPax.Any(x => x.Errors.Any()))
{
crewAndPax.ForEach(x => x.PortCall = null);
crewAndPaxWithErrors = crewAndPax.Where(x => x.Errors.Any()).ToList();
crewAndPax = crewAndPax.Where(x => !x.Errors.Any()).ToList();
}
if (crewAndPax.Any())
{
saved = SaveCrewAndPaxToPortCall(crewAndPax, portCallId);
}
if (crewAndPaxWithErrors.Any())
{
return(Json(crewAndPaxWithErrors));
}
if (saved)
{
return(Json(true));
}
return(Json(false));
}
}
}
return(Json(true));
}
catch (Exception ex)
{
Logger.Error(ex);
Logger.Error(ex.Message);
Logger.Error(ex.InnerException);
Logger.Error(ex.StackTrace);
return(Json(false));
}
}
private List <PersonOnBoard> ConvertToCrew(ExcelWorksheet worksheet, CrewSheet sheetDefinition, int crewTypeId, int portCallId)
{
var crewList = new List <PersonOnBoard>();
var validator = new PersonOnBoardSpreadSheetValidator();
var identityType = _context.IdentityDocumentType.Where(idt => idt.EnumValue == IDENTITY_DOCUMENT_TYPES.PASSPORT.ToString()).FirstOrDefault();
var sequenceNo = 0;
var genders = _context.Gender.ToList();
var portDictionairy = new Dictionary <string, int>();
var countriesDictionairy = new Dictionary <string, int>();
var master = true;
for (var rowNum = sheetDefinition.startRow; rowNum <= worksheet.Dimension.End.Row; rowNum++)
{
var row = worksheet.Cells[string.Format("{0}:{0}", rowNum)];
var LastName = worksheet.Cells[rowNum, sheetDefinition.LastNameAddress].Text;
var FirstName = worksheet.Cells[rowNum, sheetDefinition.FirstNameAddress].Text + " " + worksheet.Cells[rowNum, sheetDefinition.MiddleNameAddress].Text;
var Nationality = worksheet.Cells[rowNum, sheetDefinition.NationalityAddress].Text;
var Sex = worksheet.Cells[rowNum, sheetDefinition.SexAddress].Text;
var DateOfBirth = worksheet.Cells[rowNum, sheetDefinition.DateOfBirthAddress].Text;
var DocumentNumber = worksheet.Cells[rowNum, sheetDefinition.DocumentNumberAddress].Text;
var CountryOfIssue = worksheet.Cells[rowNum, sheetDefinition.CountryOfIssueAddress].Text;
var DateOfExpiry = worksheet.Cells[rowNum, sheetDefinition.DateOfExpiryAddress].Text;
var PortOfEmbarkation = worksheet.Cells[rowNum, sheetDefinition.PortOfEmbarkAddress].Text;
var PortOfDebarkation = worksheet.Cells[rowNum, sheetDefinition.PortOfDebarkAddress].Text;
var RankOrRating = worksheet.Cells[rowNum, sheetDefinition.RankOrRatingAddress].Text;
var PlaceOfBirth = worksheet.Cells[rowNum, sheetDefinition.PlaceOfBirthAddress].Text;
var Effects = worksheet.Cells[rowNum, sheetDefinition.EffectsCustomsAddress].Text;
Regex rgx = new Regex($"^[{_validCharacters}]+[*]?$");
FirstName = FirstName.TrimEnd();
LastName = rgx.Replace(LastName, "");
FirstName = rgx.Replace(FirstName, "");
if (string.IsNullOrWhiteSpace(LastName) && string.IsNullOrWhiteSpace(FirstName))
{
continue;
}
Nationality = rgx.Replace(Nationality, "");
Sex = rgx.Replace(Sex, "");
DocumentNumber = rgx.Replace(DocumentNumber, "");
CountryOfIssue = rgx.Replace(CountryOfIssue, "");
PortOfEmbarkation = rgx.Replace(PortOfEmbarkation, "");
PortOfDebarkation = rgx.Replace(PortOfDebarkation, "");
RankOrRating = rgx.Replace(RankOrRating, "");
PlaceOfBirth = rgx.Replace(PlaceOfBirth, "");
Effects = rgx.Replace(Effects, "");
var crew = new PersonOnBoard
{
FamilyName = LastName,
GivenName = FirstName,
PersonOnBoardTypeId = crewTypeId,
PortCallId = portCallId,
IsMaster = master
};
master = false;
if (!string.IsNullOrWhiteSpace(Nationality))
{
if (countriesDictionairy.ContainsKey(Nationality))
{
crew.NationalityId = countriesDictionairy.GetValueOrDefault(Nationality);
}
else
{
var nationality = GetCountryByThreeCharCode(Nationality);
if (nationality != null)
{
crew.NationalityId = nationality.CountryId;
countriesDictionairy.Add(Nationality, nationality.CountryId);
}
}
}
if (!string.IsNullOrWhiteSpace(Sex))
{
var genderEnum = ConvertToGender(Sex);
var gender = genders.FirstOrDefault(g => g.EnumValue == genderEnum.ToString());
if (gender == null)
{
gender = genders.FirstOrDefault(g => g.EnumValue == GENDER_TYPES.OTHER.ToString());
}
crew.GenderId = gender.GenderId;
}
if (!string.IsNullOrWhiteSpace(DateOfBirth))
{
crew.DateOfBirth = ConvertToDatetime(DateOfBirth);
}
if (!string.IsNullOrWhiteSpace(DocumentNumber) || !string.IsNullOrWhiteSpace(CountryOfIssue) || !string.IsNullOrWhiteSpace(DateOfExpiry))
{
var expiryDate = ConvertToDatetime(DateOfExpiry);
if (identityType != null)
{
var identityDocument = new IdentityDocument
{
IdentityDocumentNumber = DocumentNumber,
IdentityDocumentExpiryDate = expiryDate,
IdentityDocumentType = identityType
};
if (countriesDictionairy.ContainsKey(CountryOfIssue))
{
identityDocument.IssuingNationId = countriesDictionairy.GetValueOrDefault(CountryOfIssue);
}
else
{
var issuingNation = GetCountryByThreeCharCode(CountryOfIssue);
if (issuingNation != null)
{
identityDocument.IssuingNationId = issuingNation.CountryId;
countriesDictionairy.Add(CountryOfIssue, issuingNation.CountryId);
}
}
crew.IdentityDocument = new List <IdentityDocument>
{
identityDocument
};
}
}
if (!string.IsNullOrWhiteSpace(PortOfEmbarkation))
{
if (portDictionairy.ContainsKey(PortOfEmbarkation))
{
crew.PortOfEmbarkationId = portDictionairy.GetValueOrDefault(PortOfEmbarkation);
}
else
{
var portOfEmbark = GetPortByCode(PortOfEmbarkation);
if (portOfEmbark != null)
{
crew.PortOfEmbarkationId = portOfEmbark.LocationId;
portDictionairy.Add(PortOfEmbarkation, portOfEmbark.LocationId);
}
}
}
if (!string.IsNullOrWhiteSpace(PortOfDebarkation))
{
if (portDictionairy.ContainsKey(PortOfDebarkation))
{
crew.PortOfDisembarkationId = portDictionairy.GetValueOrDefault(PortOfDebarkation);
}
else
{
var portOfDebarkation = GetPortByCode(PortOfEmbarkation);
if (portOfDebarkation != null)
{
crew.PortOfDisembarkationId = portOfDebarkation.LocationId;
portDictionairy.Add(PortOfDebarkation, portOfDebarkation.LocationId);
}
}
}
if (!string.IsNullOrWhiteSpace(PlaceOfBirth))
{
crew.PlaceOfBirth = PlaceOfBirth;
}
if (!string.IsNullOrWhiteSpace(RankOrRating))
{
crew.RankName = RankOrRating;
}
if (!string.IsNullOrWhiteSpace(Effects))
{
crew.CrewEffects = Effects;
}
var errors = validator.ValidatePersonOnBoardSpreadSheetModel(crew);
crew.Errors = errors;
crew.ExcelRowNum = rowNum;
crew.ErrorMessages = validator.ConvertErrorsToMsg(errors);
crew.SequenceNumber = sequenceNo;
crew.IsPax = false;
sequenceNo++;
crewList.Add(crew);
}
return(crewList);
}
public ActionResult UploadCrewFile(int portCallId)
{
try
{
var file = Request.Form.Files.FirstOrDefault();
var folderName = "Upload";
var rootPath = _hostingEnvironment.WebRootPath;
var newPath = Path.Combine(rootPath, folderName);
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (file.Length > 0)
{
var fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
var fullPath = Path.Combine(newPath, fileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
var Crew = new List <PersonOnBoard>();
var fi = new FileInfo(fullPath);
using (var package = new ExcelPackage(fi))
{
var crewSheetDefinition = new CrewSheet();
var workbook = package.Workbook;
var worksheet = workbook.Worksheets[crewSheetDefinition.Sheetname];
var crewTypeId = _context.PersonOnBoardType.Where(pobt => pobt.EnumValue == PERSON_ON_BOARD_TYPE_ENUM.CREW.ToString()).FirstOrDefault().PersonOnBoardTypeId;
Crew = ConvertToCrew(worksheet, crewSheetDefinition, crewTypeId, portCallId);
var CrewListWithErrors = new List <PersonOnBoard>();
var saved = false;
if (Crew.Any(c => c.Errors.Any()))
{
Crew.ForEach(c => c.PortCall = null);
CrewListWithErrors = Crew.Where(c => c.Errors.Any()).ToList();
Crew = Crew.Where(c => !c.Errors.Any()).ToList();
}
if (Crew.Any())
{
saved = SavePoBToPortCall(Crew, portCallId, crewTypeId);
}
if (CrewListWithErrors.Any())
{
return(Json(CrewListWithErrors));
}
if (saved)
{
return(Json(true));
}
return(Json(false));
}
}
}
return(Json(true));
}
catch (Exception ex)
{
return(Json(false));
}
}