private void GenerateExcelFile(string xmlFileName, string excelFileName)
{
Questionnarie XMLFile = new Questionnarie();
DIExcel ExcelFile;
int RowIndex = 1;
string HeaderText = string.Empty;
try
{
ExcelFile = new DIExcel();//excelFileName);
if (XMLFile.OpenQuestionnaire(xmlFileName))
{
foreach (Question XmlQuestion in XMLFile.GetAllQuestions().Values)
{
//Write Section Name
if (HeaderText != XmlQuestion.HeaderTxt)
{
HeaderText = XmlQuestion.HeaderTxt;
this.WriteHeader(ExcelFile, RowIndex, HeaderText);
RowIndex += 1;
}
this.WriteQuestionIntoExcel(ExcelFile, XmlQuestion, ref RowIndex);
}
//delete file if already exists
if (File.Exists(excelFileName))
{
File.Delete(excelFileName);
}
ExcelFile.SaveAs(excelFileName);
ExcelFile.Close();
}
}
catch (Exception)
{
}
}
private bool CreateReportForUploadedDSD(SDMXApi_2_0.Message.StructureType UploadedDSDStructure, string sdmxFolderPath, int DbNId, string UserNId)
{
bool RetVal;
RetVal = true;
string AgencyId;
AgencyId = string.Empty;
try
{
AgencyId = DevInfo.Lib.DI_LibSDMX.Constants.MaintenanceAgencyScheme.Prefix + UserNId;
DIExcel ReportExcel = new DIExcel();
ReportExcel = this.GenerateDSDWorksheet(ReportExcel, 0, UploadedDSDStructure);
ReportExcel = this.GenerateCodelistWorksheets(ReportExcel, 0, UploadedDSDStructure);
ReportExcel.ActiveSheetIndex = 0;
ReportExcel.SaveAs(sdmxFolderPath + "\\" + DevInfo.Lib.DI_LibSDMX.Constants.Report.FileName);
this.Save_Artefacts_Details_For_Uploaded_DSD_In_Database(DbNId, DevInfo.Lib.DI_LibSDMX.Constants.Report.Id, AgencyId, DevInfo.Lib.DI_LibSDMX.Constants.Report.Version, string.Empty, ArtefactTypes.Report, sdmxFolderPath + "\\" + DevInfo.Lib.DI_LibSDMX.Constants.Report.FileName, DevInfo.Lib.DI_LibSDMX.Constants.Report.FileName);
}
catch (Exception ex)
{
RetVal = false;
Global.CreateExceptionString(ex, null);
//Global.WriteErrorsInLog("Creating Report For Uploaded DSD From Admin");
//Global.WriteErrorsInLog(ex.StackTrace);
//Global.WriteErrorsInLog(ex.Message);
}
finally
{
}
return RetVal;
}
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;
}
