Example #1
0
        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);
        }
Example #2
0
        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);
        }