public CDRImportStep2VM PreImportCheck(HttpPostedFileBase file, string clientSubUnitGuid, string displayName, string relatedToDisplayName = "") { //convert file to string so that we can parse int length = file.ContentLength; byte[] tempFile = new byte[length]; file.InputStream.Read(tempFile, 0, length); byte[] array = tempFile.ToArray(); System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding(); string fileToText = fileToText = enc.GetString(array); /* * Regex to match on CDRValue * The following Special Characters are currently allowed in CDR fields: * Alpha (A-Z), numeric (0-9), ampersand (&), asterisk (*), at sign (@), hypen (-), forward slash (/), period (.), * underscore (_), equal (=), colon (:), open/closed brackets ( ), space */ //string dataRegex = @"[^(\w|\-|@|*|(|)| |=|:|/)]+"; string dataRegex = @"[^(\w| |\-|\@|\*|\&|\(|\)|\=|\:|\/|\.)]+"; // Create the xml document container, this will be used to store the data after the Regex checks XmlDocument doc = new XmlDocument(); XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null); doc.AppendChild(dec); XmlElement root = doc.CreateElement("CSUCDRs"); doc.AppendChild(root); //Client SubUnit ClientSubUnit clientSubUnit = new ClientSubUnit(); ClientSubUnitClientAccountRepository clientSubUnitClientAccountRepository = new ClientSubUnitClientAccountRepository(); ClientSubUnitRepository clientSubUnitRepository = new ClientSubUnitRepository(); clientSubUnit = clientSubUnitRepository.GetClientSubUnit(clientSubUnitGuid); string clientSubUnitName = clientSubUnit.ClientSubUnitName; List <string> returnMessages = new List <string>(); string returnMessage; int i = 0; //Store Valid ClientSubUnitClientAccounts List <ClientSubUnitClientAccount> validClientSubUnitClientAccounts = new List <ClientSubUnitClientAccount>(); List <ClientSubUnitClientAccountImport> invalidClientSubUnitClientAccounts = new List <ClientSubUnitClientAccountImport>(); //Split the CSV into lines string[] lines = fileToText.Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries); //loop through CSV lines foreach (string line in lines) { i++; if (i > 1) //ignore first line with titles { string[] cells = line.Split(','); //extract the data items from the file string fileCostCentre = cells[0]; //CDRValue (Cost Centre) string fileSourceSystemCode = cells[1]; //SourceSystemCode string fileClientAccountNumber = cells[2]; //CWT Account No (Air/Rail) //Credit Card No field is an optional field on the .csv file string fileCreditCardNumber = string.Empty; if (cells.Length >= 4) { fileCreditCardNumber = cells[3] ?? ""; } //The RelatedToValue field is an optional field on the .csv file. If there is no CDR Display Name value entered in the Validate CDR field, //then the Import should not look for a RelatedToValue column on the .csv file string fileRelatedToValue = string.Empty; if (cells.Length >= 5) { fileRelatedToValue = cells[4] ?? ""; } //Build the XML Element for a CDR XmlElement xmlCDRItem = doc.CreateElement("CSUCDR"); //Populate the XML Element for a CDR //This XMLDoc is only used if the File Passes Validation XmlElement xmlSourceSystemCode = doc.CreateElement("SourceSystemCode"); xmlSourceSystemCode.InnerText = fileSourceSystemCode; xmlCDRItem.AppendChild(xmlSourceSystemCode); XmlElement xmlClientAccountNumber = doc.CreateElement("ClientAccountNumber"); xmlClientAccountNumber.InnerText = fileClientAccountNumber; xmlCDRItem.AppendChild(xmlClientAccountNumber); XmlElement xmlCostCentre = doc.CreateElement("CostCentre"); xmlCostCentre.InnerText = fileCostCentre; xmlCDRItem.AppendChild(xmlCostCentre); //Check for numerical credit card if (!string.IsNullOrEmpty(fileCreditCardNumber)) { int creditCardId; if (!Int32.TryParse(fileCreditCardNumber, out creditCardId)) { fileCreditCardNumber = ""; } XmlElement xmlCreditCardId = doc.CreateElement("CreditCardId"); xmlCreditCardId.InnerText = !string.IsNullOrEmpty(fileCreditCardNumber) ? fileCreditCardNumber : ""; xmlCDRItem.AppendChild(xmlCreditCardId); } if (!string.IsNullOrEmpty(fileRelatedToValue) && !string.IsNullOrEmpty(relatedToDisplayName)) { XmlElement xmlRelatedToDisplayName = doc.CreateElement("RelatedToDisplayName"); xmlRelatedToDisplayName.InnerText = !string.IsNullOrEmpty(relatedToDisplayName) ? relatedToDisplayName : ""; xmlCDRItem.AppendChild(xmlRelatedToDisplayName); XmlElement xmlRelatedToValue = doc.CreateElement("RelatedToValue"); xmlRelatedToValue.InnerText = !string.IsNullOrEmpty(fileRelatedToValue) ? fileRelatedToValue : ""; xmlCDRItem.AppendChild(xmlRelatedToValue); } //Attach the XML Element for a CDR to the Document root.AppendChild(xmlCDRItem); bool existingValidClient = validClientSubUnitClientAccounts.Where(x => x.ClientAccountNumber == fileClientAccountNumber && x.SourceSystemCode == fileSourceSystemCode && x.ClientSubUnitGuid == clientSubUnitGuid ).Count() > 0; bool existingInvalidClient = invalidClientSubUnitClientAccounts.Where(x => x.ClientAccountNumber == fileClientAccountNumber && x.SourceSystemCode == fileSourceSystemCode && x.ClientSubUnitGuid == clientSubUnitGuid ).Count() > 0; //Check Client Account belongs to this Client if (existingInvalidClient) { //Error: Account and SourceSystemCode have been detected in the data file that are not associated to the ClientSubUnit returnMessage = "Account '" + fileClientAccountNumber + "' and SourceSystemCode '" + fileSourceSystemCode + "' have been detected in the data file that are not associated to the ClientSubUnit that you have selected ('" + clientSubUnitName + "'). Please either associate the Account and SourceSystemCode to the chosen ClientSubUnit or update the data file with the correct Account and SourceSystemCode"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } else if (existingValidClient) { //No processing required as already marked as valid } else { ClientSubUnitClientAccount clientSubUnitClientAccount = clientSubUnitClientAccountRepository.GetClientSubUnitClientAccount( fileClientAccountNumber, fileSourceSystemCode, clientSubUnitGuid ); if (clientSubUnitClientAccount == null) { //Error: Account and SourceSystemCode have been detected in the data file that are not associated to the ClientSubUnit returnMessage = "Account '" + fileClientAccountNumber + "' and SourceSystemCode '" + fileSourceSystemCode + "' have been detected in the data file that are not associated to the ClientSubUnit that you have selected ('" + clientSubUnitName + "'). Please either associate the Account and SourceSystemCode to the chosen ClientSubUnit or update the data file with the correct Account and SourceSystemCode"; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } ClientSubUnitClientAccountImport clientSubUnitClientAccountImport = new ClientSubUnitClientAccountImport() { ClientAccountNumber = fileClientAccountNumber, SourceSystemCode = fileSourceSystemCode, ClientSubUnitGuid = clientSubUnitGuid, }; if (!invalidClientSubUnitClientAccounts.Contains(clientSubUnitClientAccountImport)) { invalidClientSubUnitClientAccounts.Add(clientSubUnitClientAccountImport); } } else { if (!validClientSubUnitClientAccounts.Contains(clientSubUnitClientAccount)) { validClientSubUnitClientAccounts.Add(clientSubUnitClientAccount); } } } //Error: There is a value that exceeds the maximum length of 50 characters if (fileCostCentre.Length > 50) { returnMessage = fileCostCentre + "is a VALUE that exceeds the maximum Value length of 50 characters. Please update the data file or select a new data file."; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } ////Error: There is a credit card id that is not present in the database //if (!string.IsNullOrEmpty(fileCreditCardNumber)) //{ // int creditCardId = Int32.Parse(fileCreditCardNumber); // CreditCardRepository creditCardRepository = new CreditCardRepository(); // CreditCard creditCard = creditCardRepository.GetCreditCard(creditCardId, false); // if (creditCard == null) // { // returnMessage = "Credit Card Id " + fileCreditCardNumber + " has been detected in the file but is not present in the database. Please check the value and try again"; // if (!returnMessages.Contains(returnMessage)) // { // returnMessages.Add(returnMessage); // break; // } // } //} //Error: An invalid character that has been detected within the data file if (Regex.Match(fileCostCentre, dataRegex).Success) { foreach (Match n in Regex.Matches(fileCostCentre, dataRegex)) { returnMessage = n.Groups[0].Value + " is an invalid character that has been detected within the data file. Please update the data file or select a new data file to import."; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } } //Error: If a user doesn’t include the Validate CDR field on the UI, and there are RelatedToValues listed on the Import, //the Import should fail and give the user an error message that Validate CDR value missing. if (!string.IsNullOrEmpty(fileRelatedToValue) && string.IsNullOrEmpty(relatedToDisplayName)) { returnMessage = "A value in the RelatedToValue column has been detected within the data file but the Validate CDR value missing. Please go back and enter a Validate CDR value or remove the RelatedToValue from the data file."; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } //Error: If a user enters a value in the Validate CDR field on the UI, the Import file must include RelatedToValues else // the Import should fail and give the user an error message that RelatedToValues missing on the Import if (string.IsNullOrEmpty(fileRelatedToValue) && !string.IsNullOrEmpty(relatedToDisplayName)) { returnMessage = "A Validate CDR has been detected but the RelatedToValue is missing. Please go back and remove the Validate CDR value or add the RelatedToValue data into the data file."; if (!returnMessages.Contains(returnMessage)) { returnMessages.Add(returnMessage); } } } } if (i == 0) { returnMessage = "There is no data in the file"; returnMessages.Add(returnMessage); } CDRImportStep2VM preImportCheckResult = new CDRImportStep2VM(); preImportCheckResult.ReturnMessages = returnMessages; if (returnMessages.Count != 0) { preImportCheckResult.IsValidData = false; } else { //DB Check string adminUserGuid = HttpContext.Current.User.Identity.Name.Split(new[] { '|' })[0]; var output = ( from n in db.spDesktopDataAdmin_UpdateClientSubUnitClientDefinedReferencesCount_v1( clientSubUnit.ClientSubUnitGuid, displayName, System.Xml.Linq.XElement.Parse(doc.OuterXml), adminUserGuid ) select n).ToList(); foreach (spDesktopDataAdmin_UpdateClientSubUnitClientDefinedReferencesCount_v1Result message in output) { returnMessages.Add(message.MessageText.ToString()); } preImportCheckResult.FileBytes = array; preImportCheckResult.IsValidData = true; } return(preImportCheckResult); }
public CDRImportStep3VM Import(byte[] FileBytes, string clientSubUnitGuid, string displayName, string relatedToDisplayName = "") { System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding(); string fileToText = fileToText = enc.GetString(FileBytes); /* * Regex to match on each line (5 items separated by commas, followed by a new line - last 2 items are optional) * CDR Value, SourceSystemCode, ClientAccountNumber, CreditCardID (optional), RelatedToValue (optional) */ CDRImportStep3VM cdrPostImportResult = new CDRImportStep3VM(); // Create the xml document container, this will be used to store the data after the Regex checks XmlDocument doc = new XmlDocument();// Create the XML Declaration, and append it to XML document XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null); doc.AppendChild(dec); XmlElement root = doc.CreateElement("CSUCDRs"); doc.AppendChild(root); ClientSubUnit clientSubUnit = new ClientSubUnit(); ClientSubUnitClientAccountRepository clientSubUnitClientAccountRepository = new ClientSubUnitClientAccountRepository(); ClientSubUnitRepository clientSubUnitRepository = new ClientSubUnitRepository(); clientSubUnit = clientSubUnitRepository.GetClientSubUnit(clientSubUnitGuid); //Counters are above loop, every time we call proc,we have to sum values (old+new) int deletedItemCount = 0; int addedItemCount = 0; //declare step flag for proc int step = 0; //Split the CSV into lines string[] lines = fileToText.Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries); //Max size of chunk int MaxSizeOfChunk = 10; //Lines buffer string[] linesBuffer; //begin loop for divide into chunks for (int j = 0; j < lines.Length; j += MaxSizeOfChunk) { int i = 0; //Set MaxSizeOfChunk for last iteration if (lines.Length - j < MaxSizeOfChunk) { MaxSizeOfChunk = lines.Length - j; } //Copy from source (lines), array size(chunk), into buffer array linesBuffer = new string[MaxSizeOfChunk]; Array.Copy(lines, j, linesBuffer, 0, MaxSizeOfChunk); //loop through CSV lines of buffer foreach (string line in linesBuffer) { if (i != j) //ignore first line with titles-we have to avoid first line, position (0,0) { string[] cells = line.Split(','); //extract the data items from the file string fileCostCentre = cells[0]; //CDRValue (Cost Centre) string fileSourceSystemCode = cells[1]; //SourceSystemCode string fileClientAccountNumber = cells[2]; //CWT Account No (Air/Rail) //Credit Card No field is an optional field on the .csv file string fileCreditCardNumber = string.Empty; if (cells.Length >= 4) { fileCreditCardNumber = cells[3] ?? ""; } //The RelatedToValue field is an optional field on the .csv file. If there is no CDR Display Name value entered in the Validate CDR field, //then the Import should not look for a RelatedToValue column on the .csv file string fileRelatedToValue = string.Empty; if (cells.Length >= 5) { fileRelatedToValue = cells[4] ?? ""; } //Build the XML Element for a CDR XmlElement xmlCDRItem = doc.CreateElement("CSUCDR"); //Populate the XML Element for a CDR //This XMLDoc is only used if the File Passes Validation XmlElement xmlSourceSystemCode = doc.CreateElement("SourceSystemCode"); xmlSourceSystemCode.InnerText = fileSourceSystemCode; xmlCDRItem.AppendChild(xmlSourceSystemCode); XmlElement xmlClientAccountNumber = doc.CreateElement("ClientAccountNumber"); xmlClientAccountNumber.InnerText = fileClientAccountNumber; xmlCDRItem.AppendChild(xmlClientAccountNumber); XmlElement xmlCostCentre = doc.CreateElement("CostCentre"); xmlCostCentre.InnerText = fileCostCentre; xmlCDRItem.AppendChild(xmlCostCentre); //Check for numerical credit card if (!string.IsNullOrEmpty(fileCreditCardNumber)) { int creditCardId; if (!Int32.TryParse(fileCreditCardNumber, out creditCardId)) { fileCreditCardNumber = ""; } XmlElement xmlCreditCardId = doc.CreateElement("CreditCardId"); xmlCreditCardId.InnerText = !string.IsNullOrEmpty(fileCreditCardNumber) ? fileCreditCardNumber : ""; xmlCDRItem.AppendChild(xmlCreditCardId); } if (!string.IsNullOrEmpty(fileRelatedToValue) && !string.IsNullOrEmpty(relatedToDisplayName)) { XmlElement xmlRelatedToDisplayName = doc.CreateElement("RelatedToDisplayName"); xmlRelatedToDisplayName.InnerText = !string.IsNullOrEmpty(relatedToDisplayName) ? relatedToDisplayName : ""; xmlCDRItem.AppendChild(xmlRelatedToDisplayName); XmlElement xmlRelatedToValue = doc.CreateElement("RelatedToValue"); xmlRelatedToValue.InnerText = !string.IsNullOrEmpty(fileRelatedToValue) ? fileRelatedToValue : ""; xmlCDRItem.AppendChild(xmlRelatedToValue); } //Attach the XML Element for a CDR to the Document root.AppendChild(xmlCDRItem); } i++; } //DB Check string adminUserGuid = HttpContext.Current.User.Identity.Name.Split(new[] { '|' })[0]; //Extra param added to proc, send 0 if first page, 1 others(if it is first page in that case we delete in proc) step = (j == 0 ? 0 : 1); var output = (from n in db.spDesktopDataAdmin_UpdateClientSubUnitClientDefinedReferences_v1( clientSubUnit.ClientSubUnitGuid, displayName, System.Xml.Linq.XElement.Parse(doc.OuterXml), adminUserGuid, step) select n).ToList(); foreach (spDesktopDataAdmin_UpdateClientSubUnitClientDefinedReferences_v1Result message in output) { deletedItemCount += message.DeletedCount; addedItemCount += message.AddedCount; } //empty xml nodes into root, ready for next iteration root.RemoveAll(); } //end loop for divide into chunks cdrPostImportResult.ClientSubUnitGuid = clientSubUnitGuid; cdrPostImportResult.DisplayName = displayName; cdrPostImportResult.AddedItemCount = addedItemCount; cdrPostImportResult.DeletedItemCount = deletedItemCount; return(cdrPostImportResult); }