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