private static FileUploadQuery CreateQuery(SpreadsheetDocument document, OpenXmlElement row)
{
var companyTl = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.CompanyTl));
var checksum = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Checksum));
var modifiedOn = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.ModifiedOn));
var company = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Company));
var created = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Created));
var dateContacted = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.DateContacted));
var nextPlannedContact = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.NextPlannedContact));
var resolution = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Resolution));
var resolutionOther = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.ResolutionOther));
var offeredPlacements = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.OfferedPlacements));
var occupationalPath = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.OccupationalPath));
var technicalRouteWay = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.TechnicalRouteWay));
var pathwayOffered = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.PathwayOffered));
var routeOffered = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.RouteOffered));
var postCode = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.PostCode));
var region = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Region));
var query = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Query));
var queryOther = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.QueryOther));
var createdBy = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.CreatedBy));
var placementOffered = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.PlacementOffered));
var referToProvider = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.ReferToProvider));
var provider1 = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Provider1));
var provider2 = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Provider2));
var provider3 = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(QueryColumnIndex.Provider3));
var fileUploadQuery = new FileUploadQuery
{
CompanyTl = new Guid(companyTl),
Checksum = checksum,
ModifiedOn = modifiedOn.ToDate(),
Company = company,
Created = created.ToDate(),
DateContacted = dateContacted.ToDate(),
NextPlannedContact = nextPlannedContact.ToDate(),
Resolution = resolution,
ResolutionOther = resolutionOther,
OfferedPlacements = int.Parse(offeredPlacements),
OccupationalPath = occupationalPath,
TechnicalRouteWay = technicalRouteWay,
PathwayOffered = pathwayOffered,
RouteOffered = routeOffered,
PostCode = postCode,
Region = region,
Query = query,
QueryOther = queryOther,
CreatedBy = createdBy,
PlacementOffered = placementOffered,
ReferToProvider = GetNullableBool(referToProvider),
Provider1 = provider1,
Provider2 = provider2,
Provider3 = provider3
};
return(fileUploadQuery);
}
private static FileUploadContact CreateContact(SpreadsheetDocument document, OpenXmlElement row)
{
var contact = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.Contact));
var checksum = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.Checksum));
var modifiedOn = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.ModifiedOn));
var companyName = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.CompanyName));
var createdOn = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.CreatedOnCompany));
var contactType = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.ContactType));
var phoneHome = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.PhoneHome));
var jobTitle = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.JobTitle));
var phoneMobile = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.PhoneMobile));
var modifiedBy = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.ModifiedBy));
var modified = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.Modified));
var preferredContact = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.PreferredContact));
var fullName = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.FullName));
var firstName = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.FirstName));
var middleName = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.MiddleName));
var lastName = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.LastName));
var phoneBusiness = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.PhoneBusiness));
var createdBy = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.CreatedBy));
var created = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(ContactColumnIndex.Created));
var isValid = ValidateMandatory(contact, contactType, preferredContact);
if (!isValid)
{
return(null);
}
var fileUploadContact = new FileUploadContact
{
Contact = new Guid(contact),
Checksum = checksum,
ModifiedOn = modifiedOn.ToDate(),
CompanyName = companyName,
CreatedOnCompany = createdOn.ToDate(),
ContactType = contactType,
PhoneHome = phoneHome,
JobTitle = jobTitle,
PhoneMobile = phoneMobile,
ModifiedBy = modifiedBy,
Modified = modified.ToDate(),
PreferredContact = preferredContact,
FullName = fullName,
FirstName = firstName,
MiddleName = middleName,
LastName = lastName,
PhoneBusiness = phoneBusiness,
CreatedBy = createdBy,
Created = created.ToDate()
};
return(fileUploadContact);
}
private static FileUploadEmployer CreateEmployer(SpreadsheetDocument document, OpenXmlElement row)
{
var account = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Account));
var checksum = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Checksum));
var modifiedOn = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.ModifiedOn));
var companyName = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.CompanyName));
var companyAka = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.CompanyAka));
var aupa = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Aupa));
var companyType = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.CompanyType));
var primaryContact = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.PrimaryContact));
var phone = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Phone));
var email = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Email));
var website = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Website));
var address1 = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Address1));
var city = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.City));
var countryRegion = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.CountryRegion));
var postCode = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.PostCode));
var createdBy = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.CreatedBy));
var created = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Created));
var modified = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Modified));
var modifiedBy = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.ModifiedBy));
var owner = CellValueRetriever.Get(document, row.Descendants <Cell>().ElementAt(EmployerColumnIndex.Owner));
var fileUploadEmployer = new FileUploadEmployer
{
Account = new Guid(account),
Checksum = checksum,
ModifiedOn = modifiedOn.ToDate(),
CompanyName = companyName,
CompanyAka = companyAka,
Aupa = aupa,
CompanyType = companyType,
PrimaryContact = primaryContact,
Phone = phone,
Email = email,
Website = website,
Address1 = address1,
City = city,
CountryRegion = countryRegion,
PostCode = postCode,
CreatedBy = createdBy,
Created = created.ToDate(),
ModifiedBy = modifiedBy,
Modified = modified.ToDate(),
Owner = owner
};
return(fileUploadEmployer);
}
