private string SaveComparisonReport(string dsd1FileNameWPath, string dsd2FileNameWPath, string hlngcodedb, Dictionary<string, string> dictMappedIndicators, Dictionary<string, string> dictMappedAttributes)
{
string RetVal;
XmlDocument DSD1Xml;
XmlDocument DSD2Xml;
List<String> ListOfMissingDimensions;
List<String> ListOfMissingAttributes;
List<String> DSD2DimensionList;
List<String> DSD2AttributeList;
List<String> DSD1DimensionList;
List<String> DSD1AttributeList;
List<String> AdditionalDSD1DimensionList;
List<String> AdditionalDSD1AttributeList;
List<CodelistType> DSD1Codelists;
List<CodelistType> DSD2Codelists;
RepresentationType LocalRepresentation;
SDMXObjectModel.Structure.DimensionType Dimension;
SDMXObjectModel.Structure.TimeDimensionType TimeDimension;
SDMXObjectModel.Structure.AttributeType Attribute;
SDMXObjectModel.Common.ConceptReferenceType ConceptIdentity;
SDMXObjectModel.Structure.StructuresType ConceptsObjDSD1;
SDMXObjectModel.Structure.StructuresType ConceptsObjDSD2;
int i,j,SheetIndex;
RetVal = string.Empty;
DSD1Xml = new XmlDocument();
DSD2Xml = new XmlDocument();
DSD2DimensionList = new List<string>();
DSD2AttributeList = new List<string>();
DSD1DimensionList = new List<string>();
DSD1AttributeList = new List<string>();
ListOfMissingDimensions = new List<string>();
ListOfMissingAttributes = new List<string>();
AdditionalDSD1DimensionList = new List<string>();
AdditionalDSD1AttributeList = new List<string>();
string DSD1Dimension = string.Empty;
string DSD2Dimension = string.Empty;
string DSD1Attribute = string.Empty;
string DSD2Attribute = string.Empty;
string UnmatchedDimensionName = string.Empty;
string UnmatchedAttributeName = string.Empty;
string DSD1CodelistId = string.Empty;
string DSD2CodelistId = string.Empty;
DIExcel ReportExcel = new DIExcel();
string tempPath = string.Empty;
string FileName = string.Empty;
string FilePath = string.Empty;
try
{
DSD1Xml.Load(dsd1FileNameWPath);
DSD2Xml.Load(dsd2FileNameWPath);
SDMXObjectModel.Message.StructureType DSD1 = new SDMXObjectModel.Message.StructureType();
SDMXObjectModel.Message.StructureType DSD2 = new SDMXObjectModel.Message.StructureType();
SDMXObjectModel.Structure.DataStructureComponentsType DSD1DSComponents = new DataStructureComponentsType();
SDMXObjectModel.Structure.DataStructureComponentsType DSD2DSComponents = new DataStructureComponentsType();
CodelistType DSD1Codelist = new CodelistType();
CodelistType DSD2Codelist = new CodelistType();
DSD1 = (SDMXObjectModel.Message.StructureType)Deserializer.LoadFromXmlDocument(typeof(SDMXObjectModel.Message.StructureType), DSD1Xml);
DSD2 = (SDMXObjectModel.Message.StructureType)Deserializer.LoadFromXmlDocument(typeof(SDMXObjectModel.Message.StructureType), DSD2Xml);
DSD1DSComponents = (SDMXObjectModel.Structure.DataStructureComponentsType)(DSD1.Structures.DataStructures[0].Item);
DSD2DSComponents = (SDMXObjectModel.Structure.DataStructureComponentsType)(DSD2.Structures.DataStructures[0].Item);
ConceptsObjDSD1 = DSD1.Structures;
ConceptsObjDSD2 = DSD2.Structures;
DSD1Codelists = DSD1.Structures.Codelists;
DSD2Codelists = DSD2.Structures.Codelists;
// Binding DSD2 Dimension in a list - DSD2DimensionList
for (i = 0; i < DSD2DSComponents.Items[0].Items.Count; i++)
{
if (DSD2DSComponents.Items[0].Items[i] is SDMXObjectModel.Structure.TimeDimensionType)
{
TimeDimension = (SDMXObjectModel.Structure.TimeDimensionType)(DSD2DSComponents.Items[0].Items[i]);
ConceptIdentity = TimeDimension.ConceptIdentity;
}
else
{
Dimension = (SDMXObjectModel.Structure.DimensionType)(DSD2DSComponents.Items[0].Items[i]);
ConceptIdentity = Dimension.ConceptIdentity;
}
DSD2DimensionList.Add(((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString());
}
// Binding DSD2 Attributes in a list - DSD2AttributeList
for (i = 0; i < DSD2DSComponents.Items[1].Items.Count; i++)
{
Attribute = (SDMXObjectModel.Structure.AttributeType)(DSD2DSComponents.Items[1].Items[i]);
ConceptIdentity = Attribute.ConceptIdentity;
DSD2AttributeList.Add(((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString());
}
// Binding Matched Dimensions in a dictionary- dictMappedIndicators that exist in DSD1 as well as in DSD2
// and unmatched Dimensions in a list-AdditionalDSD1DimensionList that exist in DSD1 but not in DSD2
for (i = 0; i < DSD1DSComponents.Items[0].Items.Count; i++)
{
if (DSD1DSComponents.Items[0].Items[i] is SDMXObjectModel.Structure.TimeDimensionType)
{
TimeDimension = (SDMXObjectModel.Structure.TimeDimensionType)(DSD1DSComponents.Items[0].Items[i]);
ConceptIdentity = TimeDimension.ConceptIdentity;
}
else
{
Dimension = (SDMXObjectModel.Structure.DimensionType)(DSD1DSComponents.Items[0].Items[i]);
ConceptIdentity = Dimension.ConceptIdentity;
}
DSD1Dimension = ((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString();
DSD1DimensionList.Add(DSD1Dimension);
if ((DSD2DimensionList.Contains(DSD1Dimension)))
{
dictMappedIndicators.Add(DSD1Dimension, DSD1Dimension);
}
else
{
AdditionalDSD1DimensionList.Add(DSD1Dimension);
}
}
// Binding Matched Attributes in a dictionary- dictMappedAttributes that exist in DSD1 as well as in DSD2
// and unmatched Attributes in a list-AdditionalDSD1AttributeList that exist in DSD1 but not in DSD2
for (i = 0; i < DSD1DSComponents.Items[1].Items.Count; i++)
{
Attribute = (SDMXObjectModel.Structure.AttributeType)(DSD1DSComponents.Items[1].Items[i]);
ConceptIdentity = Attribute.ConceptIdentity;
DSD1Attribute = ((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString();
DSD1AttributeList.Add(DSD1Attribute);
if ((DSD2AttributeList.Contains(DSD1Attribute)))
{
dictMappedAttributes.Add(DSD1Attribute, DSD1Attribute);
}
else
{
AdditionalDSD1AttributeList.Add(DSD1Attribute);
}
}
//Binding Missing Dimensions in a list-ListOfMissingDimensions that exist in DSD2 but not in DSD1
for (i = 0; i < DSD2DimensionList.Count; i++)
{
if (!(DSD1DimensionList.Contains(DSD2DimensionList[i])))
{
ListOfMissingDimensions.Add(DSD2DimensionList[i]);
}
}
//Binding Missing Attributes in a list-ListOfMissingAttributes that exist in DSD2 but not in DSD1
for (i = 0; i < DSD2AttributeList.Count; i++)
{
if (!(DSD1AttributeList.Contains(DSD2AttributeList[i])))
{
ListOfMissingAttributes.Add(DSD2AttributeList[i]);
}
}
//Binding Comparison of the two DSDs - DSD1 and DSD2
tempPath= Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath,"stock\\tempSDMXFiles");
FileName = "ComparisonReport_" + Guid.NewGuid().ToString() + ".xls";
FilePath = tempPath + "\\" + FileName;
ReportExcel = this.GenerateDimensionAndAttributesComparison(hlngcodedb,DSD1, DSD2, ReportExcel, dictMappedIndicators, ListOfMissingDimensions, AdditionalDSD1DimensionList, dictMappedAttributes, ListOfMissingAttributes, AdditionalDSD1AttributeList);
SheetIndex = 2;
foreach (string Indicator in dictMappedIndicators.Keys)
{
DSD1CodelistId = string.Empty;
for (i = 0; i < DSD1DSComponents.Items[0].Items.Count; i++)
{
if (DSD1DSComponents.Items[0].Items[i] is SDMXObjectModel.Structure.DimensionType)
{
Dimension = (SDMXObjectModel.Structure.DimensionType)(DSD1DSComponents.Items[0].Items[i]);
ConceptIdentity = Dimension.ConceptIdentity;
LocalRepresentation = Dimension.LocalRepresentation;
DSD1Dimension = ((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString();
if (DSD1Dimension == Indicator)
{
DSD1CodelistId = ((CodelistRefType)((((CodelistReferenceType)(LocalRepresentation.Items[0])).Items[0]))).id;
break;
}
}
}
if (string.IsNullOrEmpty(DSD1CodelistId) == false)
{
for (j = 0; j < DSD1Codelists.Count; j++)
{
if (DSD1CodelistId == DSD1Codelists[j].id)
{
DSD1Codelist = DSD1Codelists[j];
break;
}
}
}
for (i = 0; i < DSD2DSComponents.Items[0].Items.Count; i++)
{
DSD2CodelistId = string.Empty;
if (DSD2DSComponents.Items[0].Items[i] is SDMXObjectModel.Structure.DimensionType)
{
Dimension = (SDMXObjectModel.Structure.DimensionType)(DSD2DSComponents.Items[0].Items[i]);
ConceptIdentity = Dimension.ConceptIdentity;
LocalRepresentation = Dimension.LocalRepresentation;
DSD2Dimension = ((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString();
if (DSD2Dimension == dictMappedIndicators[Indicator])
{
DSD2CodelistId = ((CodelistRefType)((((CodelistReferenceType)(LocalRepresentation.Items[0])).Items[0]))).id;
break;
}
}
}
if (string.IsNullOrEmpty(DSD2CodelistId) == false)
{
for (j = 0; j < DSD2Codelists.Count; j++)
{
if (DSD2CodelistId == DSD2Codelists[j].id)
{
DSD2Codelist = DSD2Codelists[j];
break;
}
}
}
if ((DSD1CodelistId != string.Empty) && (DSD2CodelistId != string.Empty))
{
if( (DSD1Codelist.Items.Count > 0) && (DSD2Codelist.Items.Count > 0) )
{
ReportExcel.InsertWorkSheet(DSD1Codelist.id + " Vs " + DSD2Codelist.id);
ReportExcel = this.GenerateCodelistsComparison(hlngcodedb, DSD1Codelist, DSD2Codelist, ReportExcel, SheetIndex);
SheetIndex = SheetIndex + 1;
}
}
}
ReportExcel.ActiveSheetIndex = 0;
if (ReportExcel.AvailableWorksheetsCount > 1)
{
ReportExcel.SaveAs(FilePath);
RetVal = FileName;
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return RetVal;
}
private DIExcel GenerateCodelistWorksheets(DIExcel ReportExcel, int SheetIndex, SDMXApi_2_0.Message.StructureType CompleteStructure)
{
int i, j;
IWorksheet CodelistWorkSheet = null;
SDMXApi_2_0.Structure.CodeListType Codelist;
SDMXApi_2_0.Structure.CodeType Code;
string CodelistName = string.Empty;
string CodeValue = string.Empty;
string CodeDescription = string.Empty;
int rowindex = 0;
string Language = string.Empty;
try
{
if (CompleteStructure.Concepts.Concept.Count > 0)
{
Language = CompleteStructure.Concepts.Concept[0].Name[0].lang;
}
else if (CompleteStructure.Concepts.ConceptScheme.Count > 0)
{
Language = CompleteStructure.Concepts.ConceptScheme[0].Name[0].lang;
}
for (i = 1; i <= CompleteStructure.CodeLists.Count; i++)
{
Codelist = CompleteStructure.CodeLists[i - 1];
CodelistName = GetLangSpecificValueFor_Version_2_0(Codelist.Name, Language);
ReportExcel.InsertWorkSheet(CodelistName);
CodelistWorkSheet = ReportExcel.GetWorksheet(i);
rowindex = 1;
this.WriteValueInCell(ReportExcel, CodelistName, rowindex, 1, 12, true, 30, 0, i);
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Code", rowindex, 1, 10, true, 60, 0, i);
this.WriteValueInCell(ReportExcel, "Description", rowindex, 2, 10, true, 250, 0, i);
rowindex = rowindex + 2;
//Binding Codelist
for (j = 0; j < Codelist.Code.Count; j++)
{
Code = new SDMXApi_2_0.Structure.CodeType();
Code = Codelist.Code[j];
CodeValue = Code.value;
CodeDescription = GetLangSpecificValueFor_Version_2_0(Code.Description, Language);
if ((CodeValue.Length + 1) <= 30)
{
this.WriteValueInCell(ReportExcel, CodeValue, rowindex, 1, 10, false, 30, 0, i);
}
else
{
this.WriteValueInCell(ReportExcel, CodeValue, rowindex, 1, 10, false, CodeValue.Length + 1, 0, i);
}
this.WriteValueInCell(ReportExcel, CodeDescription, rowindex, 2, 10, false, 250, 0, i);
rowindex = rowindex + 1;
}
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return ReportExcel;
}
private DIExcel GenerateDimensionAndAttributesComparison(string hlngcodedb,SDMXObjectModel.Message.StructureType DSD1, SDMXObjectModel.Message.StructureType DSD2, DIExcel ReportExcel, Dictionary<string, string> dictMappedIndicators, List<String> ListOfMissingDimensions, List<String> AdditionalDSD1DimensionList, Dictionary<string, string> dictMappedAttributes, List<String> ListOfMissingAttributes, List<String> AdditionalDSD1AttributeList)
{
int rowindex = 0;
string DimensionName = string.Empty;
string AttributeName = string.Empty;
int SNo;
SDMXObjectModel.Structure.DataStructureComponentsType DSD1DSComponents;
SDMXObjectModel.Structure.DataStructureComponentsType DSD2DSComponents;
SDMXObjectModel.Structure.StructuresType ConceptsObjDSD1;
SDMXObjectModel.Structure.StructuresType ConceptsObjDSD2;
IWorksheet WorkbookSheet = null;
try
{
DSD1DSComponents = (SDMXObjectModel.Structure.DataStructureComponentsType)(DSD1.Structures.DataStructures[0].Item);
DSD2DSComponents = (SDMXObjectModel.Structure.DataStructureComponentsType)(DSD2.Structures.DataStructures[0].Item);
ConceptsObjDSD1 = DSD1.Structures;
ConceptsObjDSD2 = DSD2.Structures;
ReportExcel.RenameWorkSheet(0, "Dimensions");
// Writing into Dimensions Worksheet
// Writing Matched Dimensions that exist in both DSD1 and DSD2
rowindex = rowindex + 1;
WorkbookSheet = ReportExcel.GetWorksheet(0);
this.WriteValueInCell(ReportExcel, "Matched Dimensions", rowindex, 1, 14, true, 10, 0, 0);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 0);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 0);
this.WriteValueInCell(ReportExcel, "Name", rowindex,3, 12, true, 70, 0, 0);
rowindex = rowindex + 1;
SNo = 0;
foreach (string MatchedDimension in dictMappedIndicators.Keys)
{
if(!(AdditionalDSD1DimensionList.Contains(MatchedDimension)))
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 0);
DimensionName = string.Empty;
DimensionName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD1, MatchedDimension, hlngcodedb);
this.WriteValueInCell(ReportExcel, MatchedDimension, rowindex, 2, 10, false, 60, 0, 0);
if (DimensionName != string.Empty)
{
this.WriteValueInCell(ReportExcel,DimensionName, rowindex, 3, 10, false, 70, 0, 0);
}
rowindex = rowindex + 1;
}
}
// Writing Missing Dimensions that exist in DSD2 but not in DSD1
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Missing Dimensions", rowindex, 1, 14, true, 10, 0, 0);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 0);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 0);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 0);
rowindex = rowindex + 1;
SNo = 0;
foreach (string MissingDimension in ListOfMissingDimensions)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 0);
DimensionName = string.Empty;
DimensionName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD2, MissingDimension, hlngcodedb);
this.WriteValueInCell(ReportExcel, MissingDimension, rowindex, 2, 10, false, 60, 0, 0);
if (DimensionName != string.Empty)
{
this.WriteValueInCell(ReportExcel, DimensionName, rowindex, 3, 10, false, 70, 0, 0);
}
rowindex = rowindex + 1;
}
// Writing Additional Dimensions that exist in DSD1 but not in DSD2
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Additional Dimensions", rowindex, 1, 14, true, 10, 0, 0);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 0);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 0);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 0);
rowindex = rowindex + 1;
SNo = 0;
foreach (string AdditionalDimension in AdditionalDSD1DimensionList)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 0);
DimensionName = string.Empty;
DimensionName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD1, AdditionalDimension, hlngcodedb);
this.WriteValueInCell(ReportExcel, AdditionalDimension, rowindex, 2, 10, false, 60, 0, 0);
if (DimensionName != string.Empty)
{
this.WriteValueInCell(ReportExcel, DimensionName, rowindex, 3, 10, false, 70, 0, 0);
}
rowindex = rowindex + 1;
}
// Writing into Attributes Worksheet
ReportExcel.InsertWorkSheet("Attributes");
WorkbookSheet = ReportExcel.GetWorksheet(1);
// Writing Matched Attributes that exist in both DSD1 and DSD2
rowindex = 1;
this.WriteValueInCell(ReportExcel, "Matched Attributes", rowindex, 1, 14, true, 10, 0, 1);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0,1);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 1);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 1);
rowindex = rowindex + 1;
SNo = 0;
foreach (string MatchedAttribute in dictMappedAttributes.Keys)
{
if (!(AdditionalDSD1AttributeList.Contains(MatchedAttribute)))
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false,10, 0, 1);
AttributeName = string.Empty;
AttributeName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD1, MatchedAttribute, hlngcodedb);
this.WriteValueInCell(ReportExcel, MatchedAttribute, rowindex, 2, 10, false, 60, 0, 1);
if (AttributeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, AttributeName, rowindex, 3, 10, false, 70, 0, 1);
}
rowindex = rowindex + 1;
}
}
// Writing Missing Attributes that exist in DSD2 but not in DSD1
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Missing Attributes", rowindex, 1, 14, true, 10, 0, 1);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 1);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0,1);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 1);
rowindex = rowindex + 1;
SNo = 0;
foreach (string MissingAttribute in ListOfMissingAttributes)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 1);
AttributeName = string.Empty;
AttributeName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD2, MissingAttribute, hlngcodedb);
this.WriteValueInCell(ReportExcel, MissingAttribute, rowindex, 2, 10, false, 60, 0, 1);
if (AttributeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, AttributeName, rowindex, 3, 10, false, 70, 0, 1);
}
rowindex = rowindex + 1;
}
// Writing Additional Attributes that exist in DSD1 but not in DSD2
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Additional Attributes", rowindex, 1, 14, true, 10, 0, 1);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 1);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 1);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 1);
rowindex = rowindex + 1;
SNo = 0;
foreach (string AdditionalAttribute in AdditionalDSD1AttributeList)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 1);
AttributeName = string.Empty;
AttributeName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD1, AdditionalAttribute, hlngcodedb);
this.WriteValueInCell(ReportExcel, AdditionalAttribute, rowindex, 2, 10, false, 60, 0, 1);
if (AttributeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, AttributeName, rowindex, 3, 10, false, 70, 0, 1);
}
rowindex = rowindex + 1;
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return ReportExcel;
}
