/// <summary>
/// Function to extract the images from the database and copy then to the temp location Get icons referred inside metadata
/// </summary>
private static void ExtractImage(int elementNId, IconElementType IconElementType, DIConnection DIConnection, DIQueries DIQueries, String OutPutPath)
{
string sSql = DIQueries.Icon.GetIcon(elementNId.ToString(), IconElementType);
DataTable dtIcon = DIConnection.ExecuteDataTable(sSql);
try
{
if (dtIcon != null)
{
for (int index = 0; index < dtIcon.Rows.Count; index++)
{
Byte[] Buffer;
Buffer = (Byte[])dtIcon.Rows[index][Icons.ElementIcon];
//create memory stream from this array of bytes
System.IO.MemoryStream str = new System.IO.MemoryStream(Buffer);
System.Drawing.Image.FromStream(str).Save(System.IO.Path.Combine(OutPutPath, dtIcon.Rows[index][Icons.IconNId].ToString() + "." + dtIcon.Rows[index][Icons.IconType].ToString()));
str.Dispose();
}
}
}
catch (Exception)
{
//throw ex;
}
}
public static void ClearIcon(DIConnection dbConnection, string dataPrefix, IconElementType elementType)
{
try
{
dbConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.ClearIcon(dataPrefix, DIIcons.Elements[elementType].ToString()));
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
public static void ClearIcon(DIConnection dbConnection, string dataPrefix, IconElementType elementType)
{
try
{
dbConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.ClearIcon(dataPrefix, DIIcons.Elements[elementType].ToString()));
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Get Icon details from Icons table for a given elementNId and ElementType
/// </summary>
/// <param name="elementNId">elementNId for which icon is to be extracted</param>
/// <param name="IconElementType">For FilterFieldType "Search" FilterText should be FootNotes.FootNote LIKE '%Sample FootNote%'</param>
/// <returns></returns>
public string GetIcon(string elementNId, IconElementType IconElementType)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT " + DIColumns.Icons.IconNId + "," + DIColumns.Icons.IconType + "," + DIColumns.Icons.IconDimW );
sbQuery.Append("," + DIColumns.Icons.IconDimH + "," + DIColumns.Icons.ElementType + "," + DIColumns.Icons.ElementNId + "," + DIColumns.Icons.ElementIcon);
sbQuery.Append(" FROM " + this.TablesName.Icons);
sbQuery.Append(" WHERE ");
sbQuery.Append(DIColumns.Icons.ElementType + "=" + DIQueries.IconElementTypeText[IconElementType]);
if (elementNId.Trim().Length > 0)
{
sbQuery.Append(" AND " + DIColumns.Icons.ElementNId + " IN (" + elementNId + ")");
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Function to extract the images from the database and copy then to the temp location Get icons referred inside metadata
/// </summary>
private static void ExtractImage(int elementNId, IconElementType IconElementType, DIConnection DIConnection, DIQueries DIQueries, String OutPutPath)
{
string sSql = DIQueries.Icon.GetIcon(elementNId.ToString(), IconElementType);
DataTable dtIcon = DIConnection.ExecuteDataTable(sSql);
try
{
if (dtIcon != null)
{
for (int index = 0; index < dtIcon.Rows.Count; index++)
{
Byte[] Buffer;
Buffer = (Byte[])dtIcon.Rows[index][Icons.ElementIcon];
//create memory stream from this array of bytes
System.IO.MemoryStream str = new System.IO.MemoryStream(Buffer);
System.Drawing.Image.FromStream(str).Save(System.IO.Path.Combine(OutPutPath, dtIcon.Rows[index][Icons.IconNId].ToString() + "." + dtIcon.Rows[index][Icons.IconType].ToString()));
str.Dispose();
}
}
}
catch (Exception)
{
//throw ex;
}
}
/// <summary>
/// Dictionary containing old IconNId as key and new IconNId as value.
/// This information shall be utilised to update metadata xml being inserted / updated
/// </summary>
/// <param name="NidInSourceDB"></param>
/// <param name="NidInTargetDB"></param>
/// <param name="elementType"></param>
/// <param name="sourceQurey"></param>
/// <param name="SourceDBConnection"></param>
/// <param name="targetQurey"></param>
/// <param name="TargetDBConnection"></param>
/// <returns>
/// </returns>
/// <remarks></remarks>
public static Dictionary<string, string> ImportElement(int NidInSourceDB, int NidInTargetDB, IconElementType elementType, DIQueries sourceQurey, DIConnection sourceDBConnection, DIQueries targetQurey, DIConnection targetDBConnection)
{
Dictionary<string, string> RetVal = new Dictionary<string, string>();
string ElementValue = DIIcons.Elements[elementType];
string SqlQuery = string.Empty;
DataTable IconsDatatable;
string OldIconNId = string.Empty;
string NewIconNId = string.Empty;
try {
if ((sourceDBConnection != null)) {
if (DIIcons.IsIconsTblExists(sourceQurey.TablesName.Icons, sourceDBConnection)) {
//-- In Target Database: delete records from UT_Icon table if Icon is already associated with given Element Type
SqlQuery=DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.DeleteIcon(targetQurey.DataPrefix,ElementValue, NidInTargetDB.ToString());
targetDBConnection.ExecuteNonQuery(SqlQuery);
//-- In Source Database: check Icon is associated with the given Element type in UT_ICon table
SqlQuery = sourceQurey.Icon.GetIcon(NidInSourceDB.ToString(), ElementValue);
IconsDatatable = sourceDBConnection.ExecuteDataTable(SqlQuery);
//-- If associated, then copy it it from Source database into target database
foreach(DataRow Row in IconsDatatable.Rows)
{
//-- Insert Icon and get new IconNId
NewIconNId = DIIcons.InsertIcon(targetDBConnection, targetQurey, (byte[])(Row["Element_Icon"]), Row["Icon_Type"].ToString(), Convert.ToInt32(Row["Icon_Dim_W"]), Convert.ToInt32(Row["Icon_Dim_H"]), ElementValue,Convert.ToString( NidInTargetDB)).ToString();
//-- Add Item to Dictionary with New IconNId as Key and Old IconNId as Value
RetVal.Add(IMG_PREFIX + Row["Icon_NId"].ToString() + ".", IMG_PREFIX + NewIconNId + ".");
}
}
}
}
catch (Exception ex) {
throw new ApplicationException(ex.Message);
}
return RetVal;
}
/// <summary>
/// Imports metadata from the source database into current database
/// </summary>
/// <param name="srcConnection"></param>
/// <param name="srcQueries"></param>
/// <param name="srcElementNid"></param>
/// <param name="trgElementNid"></param>
/// <param name="categoryType"></param>
/// <param name="metadataType"></param>
/// <param name="iconType"></param>
public void ImportMetadata(DIConnection srcConnection, DIQueries srcQueries, int srcElementNid, int trgElementNid, MetadataElementType categoryType, MetaDataType metadataType, IconElementType iconType)
{
string SrcCategoryGID = string.Empty;
string MetadataText = string.Empty;
int TrgCategoryNid = 0;
DataTable TrgCategoryTable = null;
Dictionary <String, String> OldIconNIdnNewIconNId = new Dictionary <string, string>();
try
{
// Get target Category table
TrgCategoryTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Type, DIQueries.MetadataElementTypeText[categoryType]));
// update metadata icon in DICon table
OldIconNIdnNewIconNId = DIIcons.ImportElement(srcElementNid, trgElementNid, iconType, srcQueries, srcConnection, this.DBQueries, this.DBConnection);
//get source metadata reports
foreach (DataRow SrcRow in srcConnection.ExecuteDataTable(srcQueries.MetadataReport.GetMetadataReportsByTargetNid(srcElementNid.ToString(), categoryType)).Rows)
{
SrcCategoryGID = Convert.ToString(SrcRow[Metadata_Category.CategoryGId]);
// check source category GID exists in current database
// Import metadta report only if category exists in current database
foreach (DataRow TrgCategoryRow in TrgCategoryTable.Select(Metadata_Category.CategoryGId + "='" + DIQueries.RemoveQuotesForSqlQuery(SrcCategoryGID) + "' "))
{
MetadataText = Convert.ToString(SrcRow[MetadataReport.Metadata]);
// Update IconNids in metadata if exists
foreach (string OldIconName in OldIconNIdnNewIconNId.Keys)
{
MetadataText = MetadataText.Replace(OldIconName, OldIconNIdnNewIconNId[OldIconName].ToString());
}
TrgCategoryNid = Convert.ToInt32(TrgCategoryRow[Metadata_Category.CategoryNId]);
this.InsertORUpdateMetadataInfo(metadataType, TrgCategoryNid.ToString(), trgElementNid, MetadataText);
}
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Main Method for Displaying Metadata
/// </summary>
/// <param name="elementType"> Element Type.<example>ElementType.All, ElementType.Indicator </example> <seealso cref="DI_MetaInfo.ElementType"/></param>
/// <param name="elementNid">ID of selected Data.IND - IndicatorNId, Area - AreaNId, Source - ICNId</param>
/// <param name="DI_QueryBase"> This contain Connection Details</param>
/// <param name="outPutPath">This contains the path where output files were created</param>
/// <param name="outputFileName">
/// this will be the name of outputFile without extention
/// which will be created at the output path to display metadata
/// <remarks> extension like ".xml" ".xsl" and ".html" are added to this outputFile name
/// while creating files </remarks>
/// </param>
/// <returns>
/// </returns>
public static bool ExtractXmlMetadata(MetadataElementType metadataElementType, int elementNId, DIConnection DIConnection, DIQueries DIQueries, string outputFolder, string outputFileName, ref string infoTitle)
{
bool RetVal = false;
infoTitle = string.Empty; //Will return the Element name against ElementNId
IconElementType IconElementType = IconElementType.MetadataIndicator;
XmlDocument XMLDoc = new XmlDocument();
DataTable DTMetaDataInfo = null;
string MetadataInfo = string.Empty;
String sSql = "";
int LayerNId = -1;
string TargetObjectID = string.Empty;
Lib.DI_LibSDMX.MetadataTypes SDMXMetadataType = DevInfo.Lib.DI_LibSDMX.MetadataTypes.Indicator;
switch (metadataElementType)
{
case MetadataElementType.Area:
IconElementType = IconElementType.MetadataArea;
SDMXMetadataType = DevInfo.Lib.DI_LibSDMX.MetadataTypes.Area;
//-- Get LayerNId associated with AreaNId
if (_LayerNId == -1)
{
LayerNId = GetLayerNId(elementNId.ToString(), DIConnection, DIQueries);
}
else
{
LayerNId = _LayerNId;
}
//if (LayerNId != -1)
//{
// //-- Get Layer Metadata Info
// sSql = DIQueries.Area.GetAreaMapMetadata(LayerNId.ToString());
// DTMetaDataInfo = DIConnection.ExecuteDataTable(sSql);
// if (DTMetaDataInfo != null & DTMetaDataInfo.Rows.Count > 0)
// {
// MetadataInfo = DTMetaDataInfo.Rows[0][Area_Map_Metadata.MetadataText].ToString();
// }
//}
// Set Meta Info title.
sSql = DIQueries.Area.GetArea(FilterFieldType.NId, elementNId.ToString());
DTMetaDataInfo = DIConnection.ExecuteDataTable(sSql);
if (DTMetaDataInfo != null & DTMetaDataInfo.Rows.Count > 0)
{
infoTitle = DTMetaDataInfo.Rows[0][Area.AreaName].ToString() + " (" + DTMetaDataInfo.Rows[0][Area.AreaID].ToString() + ")";
TargetObjectID = Convert.ToString(DTMetaDataInfo.Rows[0][Area.AreaID]);
}
break;
case MetadataElementType.Indicator:
IconElementType = IconElementType.MetadataIndicator;
SDMXMetadataType = DevInfo.Lib.DI_LibSDMX.MetadataTypes.Indicator;
sSql = DIQueries.Indicators.GetIndicator(FilterFieldType.NId, elementNId.ToString(), FieldSelection.Heavy);
DTMetaDataInfo = DIConnection.ExecuteDataTable(sSql);
if (DTMetaDataInfo != null & DTMetaDataInfo.Rows.Count > 0)
{
infoTitle = DTMetaDataInfo.Rows[0][Indicator.IndicatorName].ToString();
TargetObjectID = Convert.ToString(DTMetaDataInfo.Rows[0][Indicator.IndicatorGId]);
}
break;
case MetadataElementType.Source:
IconElementType = IconElementType.MetadataSource;
SDMXMetadataType = DevInfo.Lib.DI_LibSDMX.MetadataTypes.Source;
sSql = DIQueries.IndicatorClassification.GetIC(FilterFieldType.NId, elementNId.ToString(), ICType.Source, FieldSelection.Heavy);
DTMetaDataInfo = DIConnection.ExecuteDataTable(sSql);
if (DTMetaDataInfo != null & DTMetaDataInfo.Rows.Count > 0)
{
infoTitle = DTMetaDataInfo.Rows[0][IndicatorClassifications.ICName].ToString();
TargetObjectID = Convert.ToString(DTMetaDataInfo.Rows[0][IndicatorClassifications.ICGId]);
}
break;
}
try
{
// Get metadata xml file using SDMX library
MetadataInfo = Lib.DI_LibSDMX.SDMXUtility.Get_MetadataReport(DevInfo.Lib.DI_LibSDMX.SDMXSchemaType.Two_One, TargetObjectID, SDMXMetadataType, "MDAgency", DIQueries.LanguageCode.Replace("_", ""), DIConnection, DIQueries).InnerXml;
DevInfo.Lib.DI_LibBAL.DA.DML.DI7MetaDataBuilder MetadataBuilder = new DevInfo.Lib.DI_LibBAL.DA.DML.DI7MetaDataBuilder(DIConnection, DIQueries);
// Get metadata xml file with category name..
XMLDoc = MetadataBuilder.GetMetadataReportWCategoryName(MetadataInfo, metadataElementType);
MetadataInfo = XMLDoc.InnerXml;
//Write Metadata text to physical file
// --Change on 18-03-08 .blank xml with root element will be written in case of no metadata info found in database
// If no metadata info create a blank xml file. with root element.
if (string.IsNullOrEmpty(MetadataInfo) || MetadataInfo.Trim().Length == 0)
{
WriteToFile("<?xml version='1.0'?><root ></root >", System.IO.Path.Combine(outputFolder, outputFileName + ".xml"));
}
else
{
WriteToFile(MetadataInfo, System.IO.Path.Combine(outputFolder, outputFileName + ".xml"));
}
// Commented on 18-march -2008. shifted in if else part above.
////WriteToFile(MetadataInfo, System.IO.Path.Combine(outputFolder, outputFileName + ".xml"));
}
catch (Exception ex)
{
RetVal = false;
}
if (MetadataInfo.Trim().Length > 0)
{
RetVal = true;
//Write XLS and HTMLFile
try
{
switch (metadataElementType)
{
case MetadataElementType.Area:
//In Case Area Get Xslt associated with LayerNId (not with AreaNId)
sSql = DIQueries.Xslt.GetXSLT(LayerNId.ToString(), metadataElementType);
break;
case MetadataElementType.Indicator:
case MetadataElementType.Source:
sSql = DIQueries.Xslt.GetXSLT(elementNId.ToString(), metadataElementType);
break;
default:
break;
}
DataTable XSLTInfo = (DIConnection.ExecuteDataTable(sSql));
// If XSLT info was Found Write write Xsl and Html file
if (XSLTInfo != null & XSLTInfo.Rows.Count > 0)
{
// Creating Xsl File
String xslString = XSLTInfo.Rows[0]["XSLT_Text"].ToString();
WriteToFile(xslString, System.IO.Path.Combine(outputFolder, outputFileName + ".xsl"));
//Write HTml file
WriteHtmlFile(outputFolder, outputFileName);
}
}
catch (Exception)
{
}
//Extract Images associated with metadata
try
{
if (metadataElementType == MetadataElementType.Area)
{
ExtractImage(LayerNId, IconElementType, DIConnection, DIQueries, outputFolder);
}
else
{
ExtractImage(elementNId, IconElementType, DIConnection, DIQueries, outputFolder);
}
}
catch (Exception)
{
//Old database may not have Icon table
}
}
infoTitle = infoTitle.Replace("&", "&&"); //label control do not display & character
return(RetVal);
}
/// <summary>
/// Imports metadata from the source database into current database
/// </summary>
/// <param name="srcConnection"></param>
/// <param name="srcQueries"></param>
/// <param name="srcElementNid"></param>
/// <param name="trgElementNid"></param>
/// <param name="categoryType"></param>
/// <param name="metadataType"></param>
/// <param name="iconType"></param>
public void ImportMetadata(DIConnection srcConnection, DIQueries srcQueries, int srcElementNid, int trgElementNid, MetadataElementType categoryType, MetaDataType metadataType, IconElementType iconType)
{
string SrcCategoryGID = string.Empty;
string MetadataText = string.Empty;
int TrgCategoryNid = 0;
DataTable TrgCategoryTable = null;
Dictionary<String, String> OldIconNIdnNewIconNId = new Dictionary<string, string>();
try
{
// Get target Category table
TrgCategoryTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Type, DIQueries.MetadataElementTypeText[categoryType]));
// update metadata icon in DICon table
OldIconNIdnNewIconNId = DIIcons.ImportElement(srcElementNid, trgElementNid, iconType, srcQueries, srcConnection, this.DBQueries, this.DBConnection);
//get source metadata reports
foreach (DataRow SrcRow in srcConnection.ExecuteDataTable(srcQueries.MetadataReport.GetMetadataReportsByTargetNid(srcElementNid.ToString(), categoryType)).Rows)
{
SrcCategoryGID = Convert.ToString(SrcRow[Metadata_Category.CategoryGId]);
// check source category GID exists in current database
// Import metadta report only if category exists in current database
foreach (DataRow TrgCategoryRow in TrgCategoryTable.Select(Metadata_Category.CategoryGId + "='" + DIQueries.RemoveQuotesForSqlQuery(SrcCategoryGID) + "' "))
{
MetadataText = Convert.ToString(SrcRow[MetadataReport.Metadata]);
// Update IconNids in metadata if exists
foreach (string OldIconName in OldIconNIdnNewIconNId.Keys)
{
MetadataText = MetadataText.Replace(OldIconName, OldIconNIdnNewIconNId[OldIconName].ToString());
}
TrgCategoryNid = Convert.ToInt32(TrgCategoryRow[Metadata_Category.CategoryNId]);
this.InsertORUpdateMetadataInfo(metadataType, TrgCategoryNid.ToString(), trgElementNid, MetadataText);
}
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Dictionary containing old IconNId as key and new IconNId as value.
/// This information shall be utilised to update metadata xml being inserted / updated
/// </summary>
/// <param name="NidInSourceDB"></param>
/// <param name="NidInTargetDB"></param>
/// <param name="elementType"></param>
/// <param name="sourceQurey"></param>
/// <param name="SourceDBConnection"></param>
/// <param name="targetQurey"></param>
/// <param name="TargetDBConnection"></param>
/// <returns>
/// </returns>
/// <remarks></remarks>
public static Dictionary <string, string> ImportElement(int NidInSourceDB, int NidInTargetDB, IconElementType elementType, DIQueries sourceQurey, DIConnection sourceDBConnection, DIQueries targetQurey, DIConnection targetDBConnection)
{
Dictionary <string, string> RetVal = new Dictionary <string, string>();
string ElementValue = DIIcons.Elements[elementType];
string SqlQuery = string.Empty;
DataTable IconsDatatable;
string OldIconNId = string.Empty;
string NewIconNId = string.Empty;
try {
if ((sourceDBConnection != null))
{
if (DIIcons.IsIconsTblExists(sourceQurey.TablesName.Icons, sourceDBConnection))
{
//-- In Target Database: delete records from UT_Icon table if Icon is already associated with given Element Type
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.DeleteIcon(targetQurey.DataPrefix, ElementValue, NidInTargetDB.ToString());
targetDBConnection.ExecuteNonQuery(SqlQuery);
//-- In Source Database: check Icon is associated with the given Element type in UT_ICon table
SqlQuery = sourceQurey.Icon.GetIcon(NidInSourceDB.ToString(), ElementValue);
IconsDatatable = sourceDBConnection.ExecuteDataTable(SqlQuery);
//-- If associated, then copy it it from Source database into target database
foreach (DataRow Row in IconsDatatable.Rows)
{
//-- Insert Icon and get new IconNId
NewIconNId = DIIcons.InsertIcon(targetDBConnection, targetQurey, (byte[])(Row["Element_Icon"]), Row["Icon_Type"].ToString(), Convert.ToInt32(Row["Icon_Dim_W"]), Convert.ToInt32(Row["Icon_Dim_H"]), ElementValue, Convert.ToString(NidInTargetDB)).ToString();
//-- Add Item to Dictionary with New IconNId as Key and Old IconNId as Value
RetVal.Add(IMG_PREFIX + Row["Icon_NId"].ToString() + ".", IMG_PREFIX + NewIconNId + ".");
}
}
}
}
catch (Exception ex) {
throw new ApplicationException(ex.Message);
}
return(RetVal);
}
