private void ValidateLines(ref XmlDocument doc, string[] lines, ref List <string> returnMessages) { XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null); doc.AppendChild(dec); XmlElement root = doc.CreateElement("BookingChannels"); doc.AppendChild(root); string returnMessage; int i = 0; //loop through CSV lines foreach (string line in lines) { i++; if (i > 1) //ignore first line with titles { Regex csvParser = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))"); String[] cells = csvParser.Split(line); //extract the data items from the file string gdsCode = CWTStringHelpers.NullToEmpty(CWTStringHelpers.UnescapeQuotes(cells[0])); //Required string bookingChannelTypeDescription = CWTStringHelpers.NullToEmpty(CWTStringHelpers.UnescapeQuotes(cells[1])); //Required string productChannelTypeDescription = CWTStringHelpers.NullToEmpty(CWTStringHelpers.UnescapeQuotes(cells[2])); //Required string bookingPseudoCityOrOfficeId = CWTStringHelpers.NullToEmpty(CWTStringHelpers.UnescapeQuotes(cells[3])); string ticketingPseudoCityOrOfficeId = CWTStringHelpers.NullToEmpty(CWTStringHelpers.UnescapeQuotes(cells[4])); string desktopUsedTypeDescription = CWTStringHelpers.NullToEmpty(CWTStringHelpers.UnescapeQuotes(cells[5])); //Required when Booking Channel = Offline string additionalBookingCommentDescription = CWTStringHelpers.NullToEmpty(CWTStringHelpers.UnescapeQuotes(cells[6])); string languageCode = CWTStringHelpers.NullToEmpty(CWTStringHelpers.UnescapeQuotes(cells[7])); //Build the XML Element for items XmlElement xmlBookingChannelItem = doc.CreateElement("BookingChannelItem"); //Validate data /* GDS Code */ //Required if (string.IsNullOrEmpty(gdsCode) == true) { returnMessage = "Row " + i + ": GDSCode is missing. Please provide a GDS Code"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } else { //GDSCode must contain any one value from the GDSCode column of the GDS table GDSRepository gdsRepository = new GDSRepository(); GDS gds = gdsRepository.GetGDS(gdsCode); if (gds == null) { returnMessage = "Row " + i + ": GDSCode " + gdsCode + " is invalid. Please provide a valid GDS Code"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } } XmlElement xmlGDSCode = doc.CreateElement("GDSCode"); xmlGDSCode.InnerText = gdsCode; xmlBookingChannelItem.AppendChild(xmlGDSCode); /* Booking Channel Type */ string bookingChannelTypeId = string.Empty; BookingChannelTypeRepository bookingChannelTypeRepository = new BookingChannelTypeRepository(); BookingChannelType bookingChannelType = new BookingChannelType(); //Required if (string.IsNullOrEmpty(bookingChannelTypeDescription) == true) { returnMessage = "Row " + i + ": Booking Channel is missing. Please provide a Booking Channel"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } else { //BookingChannelTypeDescription must contain any one value from the BookingChannelTypeDescription column of the BookingChannelType table bookingChannelType = bookingChannelTypeRepository.GetBookingChannelTypeByDescription(bookingChannelTypeDescription); if (bookingChannelType == null) { returnMessage = "Row " + i + ": BookingChannelTypeDescription " + bookingChannelTypeDescription + " is invalid. Please provide a valid BookingChannelType Code"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } else { bookingChannelTypeId = bookingChannelType.BookingChannelTypeId.ToString(); } } XmlElement xmlBookingChannelTypeId = doc.CreateElement("BookingChannelTypeId"); xmlBookingChannelTypeId.InnerText = bookingChannelTypeId; xmlBookingChannelItem.AppendChild(xmlBookingChannelTypeId); /* Product Channel Type */ string productChannelTypeId = string.Empty; ProductChannelTypeRepository productChannelTypeRepository = new ProductChannelTypeRepository(); ProductChannelType productChannelType = new ProductChannelType(); //Required if (string.IsNullOrEmpty(productChannelTypeDescription) == true) { returnMessage = "Row " + i + ": Product Channel is missing. Please provide a Product Channel"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } else { //ProductChannelTypeDescription must contain any one value from the ProductChannelTypeDescription column of the ProductChannelType table productChannelType = productChannelTypeRepository.GetProductChannelTypeByDescription(productChannelTypeDescription); if (productChannelType == null) { returnMessage = "Row " + i + ": ProductChannelTypeDescription " + productChannelTypeDescription + " is invalid. Please provide a valid ProductChannelType Code"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } else { productChannelTypeId = productChannelType.ProductChannelTypeId.ToString(); } } XmlElement xmlProductChannelTypeId = doc.CreateElement("ProductChannelTypeId"); xmlProductChannelTypeId.InnerText = productChannelTypeId; xmlBookingChannelItem.AppendChild(xmlProductChannelTypeId); //ProductChannelTypeId and BookingChannelTypeID must be present in the ProductChannelType table if (bookingChannelType != null && bookingChannelType.BookingChannelTypeId > 0 && productChannelType != null && productChannelType.ProductChannelTypeId > 0) { ProductChannelType productChannelBookingChannelType = productChannelTypeRepository.GetProductChannelTypeBookingChannelType(bookingChannelType.BookingChannelTypeId, productChannelType.ProductChannelTypeId); if (productChannelBookingChannelType == null) { returnMessage = "Row " + i + ": Product Channel and Booking Channel combination is invalid. Please provide a valid combination"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } } /* DesktopUsedTypeDescription */ string desktopUsedTypeId = string.Empty; //Required if BookingChannelTypeDescription is Offline if (string.IsNullOrEmpty(desktopUsedTypeDescription) == true && bookingChannelTypeDescription.ToLower() == "offline") { returnMessage = "Row " + i + ": Desktop Used is required when Booking Channel is Offline. Please provide a valid Desktop Used or change Booking Channel"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } else if (!string.IsNullOrEmpty(desktopUsedTypeDescription)) { //DesktopUsed must contain any one value from the DesktopUsedTypeDescription column of the DesktopUsedType table DesktopUsedTypeRepository desktopUsedTypeRepository = new DesktopUsedTypeRepository(); DesktopUsedType desktopUsedType = desktopUsedTypeRepository.GetDesktopUsedTypeByDescription(desktopUsedTypeDescription); if (desktopUsedType == null) { returnMessage = "Row " + i + ": Desktop Used " + desktopUsedTypeDescription + " is invalid. Please provide a valid Desktop Used"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } else { desktopUsedTypeId = desktopUsedType.DesktopUsedTypeId.ToString(); } } XmlElement xmlDesktopUsedTypeId = doc.CreateElement("DesktopUsedTypeId"); xmlDesktopUsedTypeId.InnerText = desktopUsedTypeId; xmlBookingChannelItem.AppendChild(xmlDesktopUsedTypeId); /* BookingPseudoCityOrOfficeId */ int validBookingPseudoCityOrOfficeIdCount = db.ValidPseudoCityOrOfficeIds.Where(x => x.PseudoCityOrOfficeId == bookingPseudoCityOrOfficeId).Count(); if (!string.IsNullOrEmpty(bookingPseudoCityOrOfficeId) && validBookingPseudoCityOrOfficeIdCount == 0) { returnMessage = "Row " + i + ": BookingPseudoCityOrOfficeId " + bookingPseudoCityOrOfficeId + " is invalid. Please provide a valid BookingPseudoCityOrOfficeId"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } /* Trim if is not null */ if (!string.IsNullOrEmpty(bookingPseudoCityOrOfficeId)) { bookingPseudoCityOrOfficeId = bookingPseudoCityOrOfficeId.Trim(); } XmlElement xmlBookingPseudoCityOrOfficeId = doc.CreateElement("BookingPseudoCityOrOfficeId"); xmlBookingPseudoCityOrOfficeId.InnerText = bookingPseudoCityOrOfficeId; xmlBookingChannelItem.AppendChild(xmlBookingPseudoCityOrOfficeId); /* TicketingPseudoCityOrOfficeId */ int validTicketingPseudoCityOrOfficeIdCount = db.ValidPseudoCityOrOfficeIds.Where(x => x.PseudoCityOrOfficeId == ticketingPseudoCityOrOfficeId).Count(); if (!string.IsNullOrEmpty(ticketingPseudoCityOrOfficeId) && validTicketingPseudoCityOrOfficeIdCount == 0) { returnMessage = "Row " + i + ": TicketingPseudoCityOrOfficeId " + ticketingPseudoCityOrOfficeId + " is invalid. Please provide a valid TicketingPseudoCityOrOfficeId"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } /* Trim if is not null */ if (!string.IsNullOrEmpty(ticketingPseudoCityOrOfficeId)) { ticketingPseudoCityOrOfficeId = ticketingPseudoCityOrOfficeId.Trim(); } XmlElement xmlTicketingPseudoCityOrOfficeId = doc.CreateElement("TicketingPseudoCityOrOfficeId"); xmlTicketingPseudoCityOrOfficeId.InnerText = ticketingPseudoCityOrOfficeId; xmlBookingChannelItem.AppendChild(xmlTicketingPseudoCityOrOfficeId); /* AdditionalBookingComment */ if (!string.IsNullOrEmpty(additionalBookingCommentDescription)) { //AdditionalBookingComment is freeform text and will allow up to 1500 alphanumeric and allowable special characters. if (additionalBookingCommentDescription.Length > 1500) { returnMessage = "Row " + i + ": AdditionalBookingComment contains more than 1500 characters. Please provide a valid AdditionalBookingComment"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } //Allowable special characters are: space, dash, underscore, right and left parentheses, period, apostrophe (O’Reily), ampersand and accented characters. string additionalBookingCommentPattern = @"^[À-ÿ\w\s\-\(\)\.\&\'\’_]+$"; if (!Regex.IsMatch(additionalBookingCommentDescription, additionalBookingCommentPattern, RegexOptions.IgnoreCase)) { returnMessage = "Row " + i + ": AdditionalBookingComment contains invalid special characters. Please provide a valid AdditionalBookingComment"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } } XmlElement xmlAdditionalBookingCommentDescription = doc.CreateElement("AdditionalBookingCommentDescription"); xmlAdditionalBookingCommentDescription.InnerText = additionalBookingCommentDescription; xmlBookingChannelItem.AppendChild(xmlAdditionalBookingCommentDescription); /* Language Code */ //Where the AdditionalBookingComment value exists, then the LanguageCode column must contain a LanguageCode value, if (!string.IsNullOrEmpty(additionalBookingCommentDescription) && (languageCode == null || string.IsNullOrEmpty(languageCode))) { returnMessage = "Row " + i + ": AdditionalBookingComment provided, LanguageCode is mandatory. Please provide a valid LanguageCode"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } //LanguageCode must contain any one value from the LanguageCode column of the Language table if (!string.IsNullOrEmpty(languageCode)) { LanguageRepository languageRepository = new LanguageRepository(); Language language = languageRepository.GetLanguage(languageCode); if (language == null) { returnMessage = "Row " + i + ": LanguageCode " + languageCode + " is invalid. Please provide a valid Language Code"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } } XmlElement xmlLanguageCode = doc.CreateElement("LanguageCode"); xmlLanguageCode.InnerText = languageCode; xmlBookingChannelItem.AppendChild(xmlLanguageCode); //Attach the XML Element for an item to the Document root.AppendChild(xmlBookingChannelItem); } } if (i == 0) { returnMessage = "There is no data in the file"; returnMessages.Add(returnMessage); } }
//Export Items to CSV public byte[] Export(string id) { StringBuilder sb = new StringBuilder(); //Add Headers List <string> headers = new List <string> { "ClientSubUnitGuid", "GDSCode", "BookingChannel", "ProductChannel", "BookingPseudoCityOrOfficeId", "TicketingPseudoCityOrOfficeId", "DesktopUsed", "AdditionalBookingComment", "CreationTimestamp", "LastUpdateTimestamp" }; sb.AppendLine(String.Join(",", headers.Select(x => x.ToString()).ToArray())); //Get ClientSubUnit ClientSubUnitRepository clientSubUnitRepository = new ClientSubUnitRepository(); ClientSubUnit clientSubUnit = clientSubUnitRepository.GetClientSubUnit(id); //Add Items List <BookingChannel> bookingChannels = db.BookingChannels.Where(x => x.ClientSubUnitGuid == clientSubUnit.ClientSubUnitGuid).ToList(); foreach (BookingChannel item in bookingChannels) { string date_format = "MM/dd/yy HH:mm"; //BookingChannelTypeDescription string bookingChannelTypeDescription = string.Empty; if (item.BookingChannelTypeId != null) { BookingChannelTypeRepository bookingChannelTypeRepository = new BookingChannelTypeRepository(); BookingChannelType bookingChannelType = bookingChannelTypeRepository.GetBookingChannelType(Int32.Parse(item.BookingChannelTypeId.ToString())); if (bookingChannelType != null) { bookingChannelTypeDescription = bookingChannelType.BookingChannelTypeDescription; } } //ProductChannelTypeDescription string productChannelTypeDescription = string.Empty; if (item.ProductChannelTypeId != null) { ProductChannelTypeRepository productChannelTypeRepository = new ProductChannelTypeRepository(); ProductChannelType productChannelType = productChannelTypeRepository.GetProductChannelType(Int32.Parse(item.ProductChannelTypeId.ToString())); if (productChannelType != null) { productChannelTypeDescription = productChannelType.ProductChannelTypeDescription; } } //DesktopUsedTypeDescription string desktopUsedTypeDescription = string.Empty; if (item.DesktopUsedTypeId != null) { DesktopUsedTypeRepository desktopUsedTypeRepository = new DesktopUsedTypeRepository(); DesktopUsedType desktopUsedType = desktopUsedTypeRepository.GetDesktopUsedType(Int32.Parse(item.DesktopUsedTypeId.ToString())); if (desktopUsedType != null) { desktopUsedTypeDescription = desktopUsedType.DesktopUsedTypeDescription; } } //AdditionalBookingComments StringBuilder additionalBookingCommentsList = new StringBuilder(); AdditionalBookingCommentRepository additionalBookingCommentRepository = new AdditionalBookingCommentRepository(); List <AdditionalBookingComment> additionalBookingComments = additionalBookingCommentRepository.GetAdditionalBookingComments(item.BookingChannelId); foreach (AdditionalBookingComment additionalBookingComment in additionalBookingComments) { additionalBookingCommentsList.AppendFormat("{0} - {1}; ", additionalBookingComment.AdditionalBookingCommentDescription, additionalBookingComment.LanguageCode); } sb.AppendFormat( "{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}", clientSubUnit != null && !string.IsNullOrEmpty(clientSubUnit.ClientSubUnitGuid) ? clientSubUnit.ClientSubUnitGuid : "", item.GDSCode != null && !string.IsNullOrEmpty(item.GDSCode) ? item.GDSCode : "", bookingChannelTypeDescription != null && !string.IsNullOrEmpty(bookingChannelTypeDescription) ? bookingChannelTypeDescription : "", productChannelTypeDescription != null && !string.IsNullOrEmpty(productChannelTypeDescription) ? productChannelTypeDescription : "", item.BookingPseudoCityOrOfficeId != null && !string.IsNullOrEmpty(item.BookingPseudoCityOrOfficeId) ? item.BookingPseudoCityOrOfficeId : "", item.TicketingPseudoCityOrOfficeId != null && !string.IsNullOrEmpty(item.TicketingPseudoCityOrOfficeId) ? item.TicketingPseudoCityOrOfficeId : "", desktopUsedTypeDescription != null && !string.IsNullOrEmpty(desktopUsedTypeDescription) ? desktopUsedTypeDescription : "", additionalBookingCommentsList != null && !string.IsNullOrEmpty(additionalBookingCommentsList.ToString()) ? string.Format("\"{0}\"", additionalBookingCommentsList) : "", item.CreationTimestamp != null && item.CreationTimestamp.HasValue ? item.CreationTimestamp.Value.ToString(date_format) : "", item.LastUpdateTimestamp != null && item.LastUpdateTimestamp.HasValue ? item.LastUpdateTimestamp.Value.ToString(date_format) : "" ); sb.Append(Environment.NewLine); } return(Encoding.ASCII.GetBytes(sb.ToString())); }