Exemple #1
0
        static void ParseListOfLink(XElement page, ITransformGroup group)
        {
            IEnumerable <XElement> links    = page.Elements(XName.Get("Links"));
            IEnumerable <XElement> linkList = null;

            foreach (XElement l in links)
            {
                linkList = l.Elements(XName.Get("Link"));
                break;
            }

            foreach (XElement link in linkList)
            {
                ITransformLink docLink = ParseLink(link);
                group.Links.Add(docLink);
            }
        }
        public SpecCertGenerationResult GenerateSpecCert(MapDetail mapDetail)
        {
            SpecCertGenerationResult result = new SpecCertGenerationResult();

            string currentDir   = Path.GetDirectoryName(Assembly.GetAssembly(typeof(SpecCertGenerator)).Location);
            string specCertName = string.Format("{0} - Spec Cert - XML - {1} - {2}.xlsx", mapDetail.OrgName, mapDetail.DocumentType, mapDetail.Direction.ToLower());
            string specCertPath = Path.Combine(currentDir, specCertName);
            bool   useSource    = string.Equals(mapDetail.Direction, "receive", StringComparison.OrdinalIgnoreCase);

            string templateFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, TemplateFile);

            if (File.Exists(specCertPath))
            {
                File.Delete(specCertPath);
            }

            using (ExcelPackage pck = new ExcelPackage(new FileInfo(templateFile)))
            {
                ExcelWorkbook  workBook          = pck.Workbook;
                ExcelWorksheet specCertWorksheet = workBook.Worksheets[1];
                specCertWorksheet.Name = string.Format("{0} {1} Spec Worksheet", mapDetail.DocumentType, mapDetail.Direction.ToUpper());

                Dictionary <string, ITransformLink> links     = new Dictionary <string, ITransformLink>();
                Dictionary <string, ITransformLink> fromLinks = new Dictionary <string, ITransformLink>();
                Dictionary <string, ITransformLink> toLinks   = new Dictionary <string, ITransformLink>();
                Dictionary <string, IFormula>       formulas  = new Dictionary <string, IFormula>();
                Dictionary <string, XmlLinkInfo>    xmlLinks  = new Dictionary <string, XmlLinkInfo>();

                IReferenceableElement refElement;
                string rootNodeName = null;

                foreach (ITransformGroup transformGroup in mapDetail.Map.Facets)
                {
                    foreach (ITransformLink link in transformGroup.Links)
                    {
                        links.Add(link.Name, link);
                        if (link.Source != null && link.Source.ReferenceType == ReferenceType.Formula && !fromLinks.ContainsKey(link.Source.Name))
                        {
                            fromLinks.Add(link.Source.Name, link);
                        }
                        if (link.Target != null && link.Target.ReferenceType == ReferenceType.Formula && !toLinks.ContainsKey(link.Target.Name))
                        {
                            toLinks.Add(link.Target.Name, link);
                        }

                        refElement = useSource ? link.Source : link.Target;
                        //Skip first part of path
                        string path = refElement.Name.Substring(refElement.Name.IndexOf("->") + 2);

                        if (refElement.ReferenceType == ReferenceType.Document && !xmlLinks.ContainsKey(path))
                        {
                            if (rootNodeName == null)
                            {
                                rootNodeName = refElement.Name.Substring(0, refElement.Name.IndexOf("->"));
                                specCertWorksheet.Cells[RootNodeNameRowIndex, RootNodeNameColumnIndex].Value = rootNodeName;
                            }

                            xmlLinks.Add(path, new XmlLinkInfo(link.Name));
                        }
                    }

                    foreach (IFormula formula in transformGroup.Formulas)
                    {
                        formulas.Add(formula.Name, formula);
                    }
                }

                // TODO: Check done for receive only
                // Set IsOptional to false if formula is not of type Logical*
                if (useSource)
                {
                    foreach (XmlLinkInfo xmlLink in xmlLinks.Values)
                    {
                        ITransformLink link = links[xmlLink.LinkName];

                        if (link.Target.ReferenceType == ReferenceType.Formula)
                        {
                            IFormula formula = formulas[link.Target.Name];
                            if (formula.FormulaType == FormulaType.LogicalString ||
                                formula.FormulaType == FormulaType.LogicalExistence)
                            {
                                xmlLink.IsOptional = true;
                            }
                        }
                    }
                }

                result.PathsUsed = new List <string>();
                int           row = StartRowIndex;
                int           mandatoryColumnIndex = useSource ? ReceiveMandatoryColumnIndex : SendMandatoryColumnIndex;
                string[]      prevParts            = null;
                List <string> paths = xmlLinks.Keys.ToList();
                paths.Sort();
                foreach (string path in paths)
                {
                    XmlLinkInfo xmlLink = xmlLinks[path];

                    string[] parts = path.Split(new string[] { "->" }, StringSplitOptions.RemoveEmptyEntries);

                    if (parts.Length > 5)
                    {
                        throw new NotSupportedException(string.Format("More than 5 levels deep xml elements are not supported. Path: {0}", path));
                    }

                    int level = 0;
                    if (prevParts != null)
                    {
                        while (level < prevParts.Length &&
                               level < parts.Length &&
                               prevParts[level] == parts[level])
                        {
                            level++;
                        }
                    }

                    prevParts = parts;

                    while (level < parts.Length)
                    {
                        bool highlightRow = level < parts.Length - 1 || xmlLinks.Count(entry => entry.Key.StartsWith(path + "->")) != 0;
                        AddRow(specCertWorksheet, row, level + 1, parts[level], mandatoryColumnIndex, highlightRow, xmlLink.IsOptional || level < parts.Length - 1);
                        level++;
                        row++;
                    }

                    result.PathsUsed.Add(path);
                }

                // Add errors to spec cert worksheet
                //AddErrorsWorksheet(workBook, result);

                if (result.PathsUsed.Count != 0)
                {
                    pck.SaveAs(new FileInfo(specCertPath));
                    result.SpecCertGenerated = true;
                }
            }

            result.SpecCertPath = specCertPath;

            return(result);
        }
        public SpecCertGenerationResult GenerateSpecCert(MapDetail mapDetail)
        {
            SpecCertGenerationResult result = new SpecCertGenerationResult();

            if (!mappingsAvailable)
            {
                // Not thread safe, but we don't really need it right now
                ReadCovastToX12Maps();
            }

            string currentDir = Path.GetDirectoryName(Assembly.GetAssembly(typeof(SpecCertGenerator)).Location);
            //string currentDir = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
            string specCertName = string.Format("{0} - Spec Cert - 4010 - {1} - {2}.xlsx", mapDetail.OrgName, mapDetail.DocumentType, mapDetail.Direction.ToLower());
            string specCertPath = Path.Combine(currentDir, specCertName);
            bool   useSource    = string.Equals(mapDetail.Direction, "receive", StringComparison.OrdinalIgnoreCase);

            Dictionary <string, List <string> > covastToX12Map;
            string templateFile = string.Empty;

            switch (mapDetail.DocumentType)
            {
            case 810:
                templateFile = TemplateFile_810;
                if (useSource)
                {
                    covastToX12Map = Map_810_Inbound;
                }
                else
                {
                    covastToX12Map = Map_810_Outbound;
                }
                break;

            case 850:
                templateFile = TemplateFile_850;
                if (useSource)
                {
                    covastToX12Map = Map_850_Inbound;
                }
                else
                {
                    covastToX12Map = Map_850_Outbound;
                }
                break;

            case 856:
                templateFile = TemplateFile_856;
                if (useSource)
                {
                    covastToX12Map = Map_856_Inbound;
                }
                else
                {
                    covastToX12Map = Map_856_Outbound;
                }
                break;

            default:
                result.Errors.Add(string.Format("Spec cert generation for document type {0} not supported", mapDetail.DocumentType));
                return(result);
            }

            templateFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, templateFile);

            if (File.Exists(specCertPath))
            {
                File.Delete(specCertPath);
            }

            //using (StreamReader streamReader = new StreamReader(templateFile))
            //using (ExcelPackage pck = new ExcelPackage(streamReader.BaseStream))
            using (ExcelPackage pck = new ExcelPackage(new FileInfo(templateFile)))
            {
                ExcelWorkbook  workBook          = pck.Workbook;
                ExcelWorksheet specCertWorksheet = workBook.Worksheets[1];
                specCertWorksheet.Name = string.Format("{0} {1} Spec Worksheet", mapDetail.DocumentType, mapDetail.Direction.ToUpper());

                Dictionary <string, ITransformLink> links     = new Dictionary <string, ITransformLink>();
                Dictionary <string, ITransformLink> fromLinks = new Dictionary <string, ITransformLink>();
                Dictionary <string, ITransformLink> toLinks   = new Dictionary <string, ITransformLink>();
                Dictionary <string, IFormula>       formulas  = new Dictionary <string, IFormula>();

                // Check if version is supported
                // Take any link and check source/target name
                IList <ITransformLink> refLinks   = mapDetail.Map.Facets[0].Links;
                IReferenceableElement  refElement = null;

                for (int i = 0; i < refLinks.Count; i++)
                {
                    if (useSource)
                    {
                        refElement = mapDetail.Map.Facets[0].Links[i].Source;
                    }
                    else
                    {
                        refElement = mapDetail.Map.Facets[0].Links[i].Target;
                    }

                    if (refElement.ReferenceType == ReferenceType.Document)
                    {
                        break;
                    }
                }

                if (refElement.Name.StartsWith("ASC_X12_810_004_010_DEFAULT_X") == false &&
                    refElement.Name.StartsWith("ASC_X12_850_004_010_DEFAULT_X") == false &&
                    refElement.Name.StartsWith("ASC_X12_856_004_010_DEFAULT_X") == false)
                {
                    throw new NotSupportedException(string.Format("Invalid root (version) name {0}. Valid value is ASC_X12_810|850|856_004_010_DEFAULT_X", refElement.Name.Substring(0, refElement.Name.IndexOf("->"))));
                }

                foreach (ITransformGroup transformGroup in mapDetail.Map.Facets)
                {
                    foreach (ITransformLink link in transformGroup.Links)
                    {
                        links.Add(link.Name, link);
                        if (link.Source != null && link.Source.ReferenceType == ReferenceType.Formula && !fromLinks.ContainsKey(link.Source.Name))
                        {
                            fromLinks.Add(link.Source.Name, link);
                        }
                        if (link.Target != null && link.Target.ReferenceType == ReferenceType.Formula && !toLinks.ContainsKey(link.Target.Name))
                        {
                            toLinks.Add(link.Target.Name, link);
                        }
                    }

                    foreach (IFormula formula in transformGroup.Formulas)
                    {
                        formulas.Add(formula.Name, formula);
                    }
                }

                // Get all enum values
                // Enum values are determined as
                // Document(1) => One or more Formula (Equal) => Logical Or formula => Document(2)
                // allEnumValues store first Document(1), Document(2), enum values
                Dictionary <string, Dictionary <string, List <string> > > allEnumValues = new Dictionary <string, Dictionary <string, List <string> > >();
                foreach (ITransformLink link in links.Values)
                {
                    if (useSource == false)
                    {
                        if (link.Source.ReferenceType == ReferenceType.Document && link.Target.ReferenceType == ReferenceType.Formula)
                        {
                            IFormula formula = formulas[link.Target.Name];

                            // In case of enum formulas it's possible that scripting formula is first applied to convert enum value
                            // If that is the case ignore scripting formula and move to next formula
                            if (formula.Parameters != null && formula.FormulaType == FormulaType.Scripting)
                            {
                                ITransformLink equalLink = fromLinks[formula.Name];
                                if (equalLink.Target != null && equalLink.Target.ReferenceType == ReferenceType.Formula)
                                {
                                    string equalFormulaName = equalLink.Target.Name;
                                    formula = formulas[equalFormulaName];
                                }
                            }

                            if (formula.Parameters != null && formula.FormulaType == FormulaType.Equality)
                            {
                                // Assumption: only 1 enum value and only 1 target formula exist
                                string enumValue = null;
                                foreach (IParameter parameter in formula.Parameters)
                                {
                                    if (parameter.Reference.ReferenceType == ReferenceType.Literal)
                                    {
                                        if (enumValue != null)
                                        {
                                            throw new NotSupportedException("Multiple enum values in 1 formula");
                                        }
                                        enumValue = parameter.Reference.Name;
                                    }
                                }

                                // If there are enum values check what's the target of these values
                                if (!string.IsNullOrEmpty(enumValue))
                                {
                                    ITransformLink equalLink = fromLinks[formula.Name];
                                    if (equalLink.Target != null && equalLink.Target.ReferenceType == ReferenceType.Formula)
                                    {
                                        string   targetFormulaName = equalLink.Target.Name;
                                        IFormula targetFormula     = formulas[targetFormulaName];

                                        // Check if the equal link points to logical or formula, if it is then the target link of that will give us the document
                                        if (targetFormula.FormulaType == FormulaType.LogicalOr)
                                        {
                                            string targetDocumentName = null;

                                            ITransformLink logicalOrLink = fromLinks[targetFormulaName];
                                            if (logicalOrLink.Target != null && logicalOrLink.Target.ReferenceType == ReferenceType.Document)
                                            {
                                                targetDocumentName = logicalOrLink.Target.Name;

                                                if (!string.IsNullOrEmpty(targetDocumentName))
                                                {
                                                    Dictionary <string, List <string> > nodeEnumValues;
                                                    if (!allEnumValues.TryGetValue(link.Source.Name, out nodeEnumValues))
                                                    {
                                                        nodeEnumValues = new Dictionary <string, List <string> >();
                                                        allEnumValues.Add(link.Source.Name, nodeEnumValues);
                                                    }

                                                    List <string> enumValues = null;
                                                    if (!nodeEnumValues.TryGetValue(targetDocumentName, out enumValues))
                                                    {
                                                        enumValues = new List <string>();
                                                        nodeEnumValues.Add(targetDocumentName, enumValues);
                                                    }
                                                    enumValues.Add(enumValue);
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                foreach (ITransformGroup transformGroup in mapDetail.Map.Facets)
                {
                    foreach (ITransformLink link in transformGroup.Links)
                    {
                        IReferenceableElement toRefElement   = link.Target;
                        IReferenceableElement fromRefElement = link.Source;

                        if (useSource)
                        {
                            toRefElement   = link.Source;
                            fromRefElement = link.Target;
                        }

                        if (toRefElement.ReferenceType == ReferenceType.Document)
                        {
                            try
                            {
                                x12Paths = null;
                                // Check if source has enum values
                                List <string> enumValues = GetEnumValues(link.Source.Name, toRefElement, allEnumValues);
                                if (enumValues != null)
                                {
                                    AddEnumValues(specCertWorksheet, covastToX12Map, toRefElement.Name, useSource, enumValues);
                                }
                                else
                                {
                                    if (fromRefElement.ReferenceType == ReferenceType.Formula &&
                                        (formulas[fromRefElement.Name].FormulaType == FormulaType.LogicalExistence ||
                                         formulas[fromRefElement.Name].FormulaType == FormulaType.LogicalString ||
                                         formulas[fromRefElement.Name].FormulaType == FormulaType.GreaterThan))
                                    {
                                        AddExistsCheckNote(specCertWorksheet, covastToX12Map, toRefElement.Name, useSource);
                                        //SetMandatoryValue(specCertWorksheet, covastToX12Map, toRefElement.Name, useSource, "n");
                                    }
                                    else
                                    {
                                        // In case of looping do not set the mandatory flag for the segment
                                        if (!(fromRefElement.ReferenceType == ReferenceType.Formula &&
                                              formulas[fromRefElement.Name].FormulaType == FormulaType.Looping))
                                        {
                                            SetMandatoryValue(specCertWorksheet, covastToX12Map, toRefElement.Name, useSource, "y");
                                        }
                                        //else
                                        //SetMandatoryValue(specCertWorksheet, covastToX12Map, toRefElement.Name, useSource, "n");
                                    }
                                }

                                if (x12Paths != null)
                                {
                                    result.PathsUsed.AddRange(x12Paths);
                                }
                            }
                            catch (Exception ex)
                            {
                                result.Errors.Add(ex.Message);
                            }
                        }
                    }
                }

                // Add errors to spec cert worksheet
                //AddErrorsWorksheet(workBook, result);

                if (result.PathsUsed.Count != 0)
                {
                    pck.SaveAs(new FileInfo(specCertPath));
                    result.SpecCertGenerated = true;
                }
            }

            result.SpecCertPath = specCertPath;

            return(result);
        }
        static void GenerateWorksheetFromFacets(ExcelWorkbook workBook, IList <ITransformGroup> facets, string sourceDocName, string targetDocName)
        {
            string name = string.Format("{0} -> {1}", sourceDocName, targetDocName);

            var ws = workBook.Worksheets.Add(name);

            ws.View.ShowGridLines = true;

            ws.Column(SegmentNameIndex).Width  = 10;
            ws.Column(SourceFieldIndex).Width  = 10;
            ws.Column(TargetFieldIndex).Width  = 10;
            ws.Column(TypeIndex).Width         = 8;
            ws.Column(GroupNameIndex).Width    = 10;
            ws.Column(TargetRecordIndex).Width = 15;
            ws.Column(SourceRecordIndex).Width = 15;


            //ws.Column(NotesIndex).Width = 10;

            //Headers
            ws.Cells["A1"].Value = "Segment";
            ws.Cells["B1"].Value = "Target Field";
            ws.Cells["C1"].Value = "Source Field";
            ws.Cells["D1"].Value = "Type";
            ws.Cells["E1"].Value = "Group Name";
            ws.Cells["F1"].Value = "Target Record";
            ws.Cells["G1"].Value = "Source Record";
            ws.Cells["A1:G1"].Style.Font.Bold = true;

            ws.View.FreezePanes(2, 1);
            ws.Select("A1");

            //Start at row 2;
            int row = 2;

            foreach (ITransformGroup group in facets)
            {
                Dictionary <string, ITransformLink> referencedLinkMap;
                Dictionary <string, IFormula>       formulaMap;
                GetReferencedLinksAndFormula(group, out referencedLinkMap, out formulaMap);

                //Pass1
                //There are 2 things to be done in this pass
                //1. Set LinkName, Src and Target colums for Document XPath references
                //2. If Target contains formula, set formula.Address to TargetFieldIndex

                string fieldName, recordName;
                string prevTargetRecordName = string.Empty;
                int    level = 0;
                foreach (ITransformLink link in group.Links)
                {
                    if (link.Ignore)
                    {
                        continue;
                    }

                    //set the link address to the base row
                    //All references from formula are to either the source or target of link, which can be
                    //derived from the link base address

                    //ws.Cells[row, LinkGroupNameIndex].Value = link.Name;



                    if (link.Target.ReferenceType == ReferenceType.Document)
                    {
                        GetFieldAndRecordName(link.Target.Name, out fieldName, out recordName);

                        /*
                         * if (recordName != prevTargetRecordName)
                         * {
                         *  //a new record is being encountered
                         *  //reset level to 0 and create a header row
                         *  level = 1;
                         *  ws.Cells[row, SegmentNameIndex].Value = recordName;
                         *  ws.Row(row).OutlineLevel = level;
                         *  ws.Row(row).Collapsed = true;
                         *  prevTargetRecordName = recordName;
                         *  row++;
                         * }
                         *
                         * else
                         * {
                         *  level = 2;
                         * }
                         */

                        ws.Cells[row, TargetFieldIndex].Value  = fieldName;
                        ws.Cells[row, TargetRecordIndex].Value = recordName;
                        ws.Cells[row, TypeIndex].Value         = "Data Copy";
                        //ws.Row(row).OutlineLevel = level;
                        //ws.Row(row).Collapsed = true;
                    }

                    else if (link.Target.ReferenceType == ReferenceType.Formula)
                    {
                        string   formulaAddress = GetCellAddress(ws, row, TargetFieldIndex);
                        IFormula formula        = formulaMap[link.Target.Name];
                        if (string.IsNullOrEmpty(formula.Address))
                        {
                            formula.Address = formulaAddress;
                        }

                        //ws.Cells[row, TargetFieldIndex].Value = ToString(formula);
                        ws.Cells[row, TargetFieldIndex].Style.Font.Bold = true;
                        ws.Cells[row, TypeIndex].Value = "Formula";
                    }

                    if (link.Source.ReferenceType == ReferenceType.Document)
                    {
                        GetFieldAndRecordName(link.Source.Name, out fieldName, out recordName);

                        ws.Cells[row, SourceFieldIndex].Value  = fieldName;
                        ws.Cells[row, SourceRecordIndex].Value = recordName;
                        ws.Cells[row, TypeIndex].Value         = "Data Copy";
                        //ws.Row(row).OutlineLevel = level;
                        //ws.Row(row).Collapsed = true;
                    }

                    else if (link.Source.ReferenceType == ReferenceType.Formula)
                    {
                        ws.Cells[row, SourceFieldIndex].Style.Font.Bold = true;
                        ws.Cells[row, TypeIndex].Value = "Formula";
                    }

                    link.Address = row.ToString();
                    ws.Cells[row, GroupNameIndex].Value = group.Name;

                    row++;
                }

                //Pass2
                //2nd pass, populate the address for links whose source contains a formula
                //Given that this formula is the source of value, it must have been the target
                //somewhere. Find that part and add it's address
                foreach (ITransformLink link in group.Links)
                {
                    if (link.Ignore)
                    {
                        continue;
                    }

                    int rowNum = int.Parse(link.Address);
                    if (link.Source.ReferenceType == ReferenceType.Formula)
                    {
                        IFormula formula = formulaMap[link.Source.Name];
                        if (string.IsNullOrEmpty(formula.Address))
                        {
                            throw new ArgumentNullException("formula has null address");
                        }
                        ws.Cells[rowNum, SourceFieldIndex].Formula = formula.Address;
                    }
                }

                //Pass3
                //Now populate the address of the parameters in each formula
                foreach (IFormula formula in group.Formulas)
                {
                    if (formula.Ignore)
                    {
                        continue;
                    }

                    foreach (IParameter param in formula.Parameters)
                    {
                        if (param.Reference.ReferenceType == ReferenceType.Document)
                        {
                            ITransformLink link = referencedLinkMap[param.Reference.Name];

                            //B stands for sourceColumns. Todo: remove hardcoding and programmatically
                            //determine the columns letter.User may have moved around columns
                            param.Address = LinkSourceColumn + link.Address;
                        }
                    }

                    //ExcelRange range = ws.Cells[formula.Address];
                    //range.Value = ToString(formula);
                }

                //Pass4
                //Render the formula
                foreach (ITransformLink link in group.Links)
                {
                    if (link.Ignore)
                    {
                        continue;
                    }

                    int rowNum = int.Parse(link.Address);
                    if (link.Target.ReferenceType == ReferenceType.Formula)
                    {
                        IFormula formula = formulaMap[link.Target.Name];
                        if (string.IsNullOrEmpty(formula.Address))
                        {
                            throw new ArgumentNullException("formula has null address");
                        }
                        bool   isFormulaRender;
                        string expression = ToString(formula, out isFormulaRender);
                        if (isFormulaRender)
                        {
                            ws.Cells[rowNum, TargetFieldIndex].Formula = expression;
                        }

                        else
                        {
                            ws.Cells[rowNum, TargetFieldIndex].Value = expression;
                        }
                    }
                }
            }

            string filterRange = string.Format(AutoFilterFormat, row.ToString());

            ws.Cells[filterRange].AutoFilter = true;
            ws.Cells[1, 1, row, 7].AutoFitColumns();
        }
        private const string DefaultSegmentDelimiter = "13 10"; // \r\n

        public SpecCertGenerationResult GenerateSpecCert(MapDetail mapDetail)
        {
            SpecCertGenerationResult result = new SpecCertGenerationResult();

            string currentDir   = Path.GetDirectoryName(Assembly.GetAssembly(typeof(SpecCertGenerator)).Location);
            string specCertName = string.Format("{0} - Spec Cert - Flat File - {1} - {2}.xlsx", mapDetail.OrgName, mapDetail.DocumentType, mapDetail.Direction.ToLower());
            string specCertPath = Path.Combine(currentDir, specCertName);
            bool   useSource    = string.Equals(mapDetail.Direction, "receive", StringComparison.OrdinalIgnoreCase);

            string templateFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, TemplateFile);

            if (File.Exists(specCertPath))
            {
                File.Delete(specCertPath);
            }

            using (ExcelPackage pck = new ExcelPackage(new FileInfo(templateFile)))
            {
                ExcelWorkbook  workBook          = pck.Workbook;
                ExcelWorksheet specCertWorksheet = workBook.Worksheets[1];
                specCertWorksheet.Name = string.Format("{0} {1} Spec Worksheet", mapDetail.DocumentType, mapDetail.Direction.ToUpper());

                Dictionary <string, ITransformLink> links     = new Dictionary <string, ITransformLink>();
                Dictionary <string, ITransformLink> fromLinks = new Dictionary <string, ITransformLink>();
                Dictionary <string, ITransformLink> toLinks   = new Dictionary <string, ITransformLink>();
                Dictionary <string, IFormula>       formulas  = new Dictionary <string, IFormula>();
                Dictionary <string, LinkInfo>       xmlLinks  = new Dictionary <string, LinkInfo>();

                IReferenceableElement refElement;
                string rootNodeName = null;

                specCertWorksheet.Cells[ElementDelimiterRowIndex, ElementDelimiterColumnIndex].Value = DefaultElementDelimiter;
                specCertWorksheet.Cells[SegmentDelimiterRowIndex, SegmentDelimiterColumnIndex].Value = DefaultSegmentDelimiter;

                foreach (ITransformGroup transformGroup in mapDetail.Map.Facets)
                {
                    foreach (ITransformLink link in transformGroup.Links)
                    {
                        links.Add(link.Name, link);
                        if (link.Source != null && link.Source.ReferenceType == ReferenceType.Formula && !fromLinks.ContainsKey(link.Source.Name))
                        {
                            fromLinks.Add(link.Source.Name, link);
                        }
                        if (link.Target != null && link.Target.ReferenceType == ReferenceType.Formula && !toLinks.ContainsKey(link.Target.Name))
                        {
                            toLinks.Add(link.Target.Name, link);
                        }

                        refElement = useSource ? link.Source : link.Target;

                        if (refElement.Name.Contains("->") == false)
                        {
                            continue;
                        }

                        //Skip first part of path
                        string path = refElement.Name.Substring(refElement.Name.IndexOf("->") + 2);

                        if (refElement.ReferenceType == ReferenceType.Document && !xmlLinks.ContainsKey(path))
                        {
                            xmlLinks.Add(path, new LinkInfo(link.Name));
                        }
                    }

                    foreach (IFormula formula in transformGroup.Formulas)
                    {
                        formulas.Add(formula.Name, formula);
                    }
                }

                // TODO: Check done for receive only
                // Set IsOptional to false if formula is not of type Logical*
                if (useSource)
                {
                    foreach (LinkInfo xmlLink in xmlLinks.Values)
                    {
                        ITransformLink link = links[xmlLink.LinkName];

                        if (link.Target.ReferenceType == ReferenceType.Formula)
                        {
                            IFormula formula = formulas[link.Target.Name];
                            if (formula.FormulaType == FormulaType.LogicalString ||
                                formula.FormulaType == FormulaType.LogicalExistence)
                            {
                                xmlLink.IsOptional = true;
                            }
                        }
                    }
                }

                result.PathsUsed = new List <string>();
                int           row = StartRowIndex;
                int           mandatoryColumnIndex = useSource ? ReceiveMandatoryColumnIndex : SendMandatoryColumnIndex;
                List <string> paths = xmlLinks.Keys.ToList();
                paths.Sort();
                string lastIterationSegmentName = null;
                foreach (string path in paths)
                {
                    LinkInfo link = xmlLinks[path];

                    string[]      partsTemp = path.Split(new string[] { "->" }, StringSplitOptions.RemoveEmptyEntries);
                    List <string> parts     = partsTemp.ToList();
                    parts.Remove("Loop");

                    // Only loop name is ignored as loop name is added on the segment row
                    if (parts.Count <= 1)
                    {
                        continue;
                    }

                    if (parts.Count > 3)
                    {
                        throw new NotSupportedException(string.Format("More than 5 levels deep xml elements are not supported. Path: {0}", path));
                    }

                    string loopName    = parts[0].Contains("Loop") ? parts[0] : string.Empty; // Loop name is optional
                    string segmentName = string.IsNullOrEmpty(loopName) ? parts[0] : parts[1];
                    // Some maps may have mapping only for segments (e.g. Header, BodyLoop etc.) that means
                    // data element is optional
                    string dataElementName = null;
                    int    index           = 2;
                    if (string.IsNullOrEmpty(loopName))
                    {
                        index = 1;
                    }

                    if (parts.Count > index)
                    {
                        dataElementName = parts[index];
                    }

                    // If segment changed then add segment row
                    if (segmentName.Equals(lastIterationSegmentName, StringComparison.OrdinalIgnoreCase) == false)
                    {
                        AddSegmentRow(specCertWorksheet, row, loopName, segmentName, mandatoryColumnIndex, link.IsOptional || !string.IsNullOrEmpty(dataElementName));
                        lastIterationSegmentName = segmentName;
                        row++;
                    }

                    if (string.IsNullOrEmpty(dataElementName) == false)
                    {
                        AddDataElementRow(specCertWorksheet, row, dataElementName, mandatoryColumnIndex, link.IsOptional);
                        row++;
                    }

                    result.PathsUsed.Add(path);
                }

                // Add errors to spec cert worksheet
                //AddErrorsWorksheet(workBook, result);

                if (result.PathsUsed.Count != 0)
                {
                    pck.SaveAs(new FileInfo(specCertPath));
                    result.SpecCertGenerated = true;
                }
            }

            result.SpecCertPath = specCertPath;

            return(result);
        }