public int GetElementNidByGID(string elementGID, MetaDataType metadataType)
{
int RetVal = 0;
string Query = string.Empty;
string NIDColumnName = string.Empty;
try
{
elementGID = DIQueries.RemoveQuotesForSqlQuery(elementGID);
switch (metadataType)
{
case MetaDataType.Indicator:
Query = this.DBQueries.Indicators.GetIndicator(FilterFieldType.GId, "'" + elementGID + "'", FieldSelection.NId);
NIDColumnName = Indicator.IndicatorNId;
break;
case MetaDataType.Map:
Query = this.DBQueries.Area.GetAreaMapLayer(DevInfo.Lib.DI_LibDAL.Queries.Area.Select.MapFilterFieldType.LayerName, "'" + elementGID + "'", FieldSelection.NId);
NIDColumnName = Area_Map_Layer.LayerNId;
break;
case MetaDataType.Source:
Query = this.DBQueries.Source.GetSource(FilterFieldType.Name, "'" + elementGID + "'", FieldSelection.NId, false);
NIDColumnName = IndicatorClassifications.ICNId;
break;
//case MetaDataType.Sector:
// break;
//case MetaDataType.Goal:
// break;
//case MetaDataType.CF:
// break;
//case MetaDataType.Theme:
// break;
//case MetaDataType.Institution:
// break;
//case MetaDataType.Convention:
// break;
//case MetaDataType.IndicatorClassification:
// break;
default:
break;
}
if (!string.IsNullOrEmpty(Query))
{
foreach (DataRow Row in this.DBConnection.ExecuteDataTable(Query).Rows)
{
RetVal = Convert.ToInt32(Row[NIDColumnName]);
break;
}
}
}
catch (Exception)
{
RetVal = 0;
}
return(RetVal);
}
/// <summary>
/// Get subgroup Type Nid by Name
/// </summary>
/// <returns> Nid</returns>
private int GetSubgroupTypeByName(string name)
{
int RetVal = 0;
string SqlQuery = string.Empty;
try
{
SqlQuery = this.DBQueries.SubgroupTypes.GetSubgroupTypes(FilterFieldType.Name, "'" +
DIQueries.RemoveQuotesForSqlQuery(name) + "'");
RetVal = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(SqlQuery));
}
catch (Exception)
{
RetVal = 0;
}
return(RetVal);
}
private void UpdateMetadataInfo(DIQueries queries, string targetNid, MetadataElementType categoryType, String metadataCategoryInfo)
{
switch (categoryType)
{
case MetadataElementType.Indicator:
this._DBConnection.ExecuteNonQuery("update " + queries.TablesName.Indicator + " set " + Indicator.IndicatorInfo + "='" + DIQueries.RemoveQuotesForSqlQuery(metadataCategoryInfo) + "'where " + Indicator.IndicatorNId + "=" + targetNid);
break;
case MetadataElementType.Area:
this._DBConnection.ExecuteNonQuery("update " + queries.TablesName.AreaMapMetadata + " set " + Area_Map_Metadata.MetadataText + "='" + DIQueries.RemoveQuotesForSqlQuery(metadataCategoryInfo) + "' where " + Area_Map_Layer.LayerNId + "=" + targetNid);
break;
case MetadataElementType.Source:
this._DBConnection.ExecuteNonQuery("update " + queries.TablesName.IndicatorClassifications + " set " + IndicatorClassifications.ICInfo + "='" + DIQueries.RemoveQuotesForSqlQuery(metadataCategoryInfo) + "'where " + IndicatorClassifications.ICNId + "=" + targetNid);
break;
default:
break;
}
}
/// <summary>
/// Retruns nid only if GID exists in the database
/// </summary>
/// <returns></returns>
private int GetNidByGID(string GID)
{
string SqlQuery = string.Empty;
int RetVal = 0;
try
{
SqlQuery = this.DBQueries.Indicators.GetIndicator(FilterFieldType.GId, "'" + DIQueries.RemoveQuotesForSqlQuery(GID) + "'", FieldSelection.NId);
RetVal = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(SqlQuery));
}
catch (Exception)
{
RetVal = 0;
}
return(RetVal);
}
/// <summary>
/// Retruns nid only if name exists in the database
/// </summary>
/// <returns></returns>
private int GetNidByName(string name, DIQueries dbQueries, string parentNId)
{
int RetVal = 0;
string SqlQuery = string.Empty;
try
{
SqlQuery = dbQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Name, "'" + DIQueries.RemoveQuotesForSqlQuery(name) + "'", parentNId);
RetVal = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(SqlQuery));
}
catch (Exception)
{
RetVal = 0;
}
return(RetVal);
}
/// <summary>
/// Imports all metadata categories from given source database to current database
/// </summary>
/// <param name="srcDBConnection"></param>
/// <param name="srcDBQueries"></param>
/// <param name="categoryType"></param>
public void ImportAllMetadataCategories(DIConnection srcDBConnection, DIQueries srcDBQueries, MetadataElementType categoryType)
{
// import by GID. If GID exists then update existing category otherwise insert it into current database // update process will update in current langauge but insert process will insert in all langauges
DataTable SrcCategoryTable = null;
DI7MetadataCategoryInfo SrcCategoryInfo = null;
DataTable TrgCategoryTable = null;
int TrgCategoryNid = 0;
int TrgCategoryParentNid = -1;
try
{
// get source category table
SrcCategoryTable = srcDBConnection.ExecuteDataTable(srcDBQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Type, DIQueries.MetadataElementTypeText[categoryType]));
// get target(current database) category table
TrgCategoryTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Type, DIQueries.MetadataElementTypeText[categoryType]));
// import categories & sub categories from source database into current database
foreach (DataRow SrcRow in SrcCategoryTable.Select(Metadata_Category.ParentCategoryNId + "=-1"))
{
// get category from source database
SrcCategoryInfo = DI7MetadataCategoryBuilder.GetMetadataCategoryInfo(SrcRow, srcDBConnection, srcDBQueries);
// check src category gid exists in current(target) database of not
TrgCategoryNid = 0;
TrgCategoryParentNid = -1;
foreach (DataRow TrgRow in TrgCategoryTable.Select(Metadata_Category.CategoryGId + "='" + DIQueries.RemoveQuotesForSqlQuery(SrcCategoryInfo.CategoryGID) + "' OR " + Metadata_Category.CategoryName + "='" + DIQueries.RemoveQuotesForSqlQuery(SrcCategoryInfo.CategoryName) + "'"))
{
// if exists then get nid and parent nid
TrgCategoryNid = Convert.ToInt32(TrgRow[Metadata_Category.CategoryNId]);
TrgCategoryParentNid = Convert.ToInt32(TrgRow[Metadata_Category.ParentCategoryNId]);
}
// update nid & parent nid in src category info
SrcCategoryInfo.CategoryNId = TrgCategoryNid;
SrcCategoryInfo.ParentNid = TrgCategoryParentNid;
if (TrgCategoryNid > 0)
{
// update category info
this.UpdateMetadataCategory(SrcCategoryInfo);
}
else
{
// insert category into current database
TrgCategoryNid = this.InsertIntoDatabase(SrcCategoryInfo);
}
#region -- insert/update sub categories into current database --
// insert/update only if target category parent nid is equal to -1 (means at level1)
if (TrgCategoryParentNid == -1)
{
foreach (DI7MetadataCategoryInfo SrcSubCategory in SrcCategoryInfo.SubCategories.Values)
{
SrcSubCategory.CategoryNId = 0;
SrcSubCategory.ParentNid = TrgCategoryNid;
// check sub category exists ( where gid=<src gid> and parent nid=<trg nid>
foreach (DataRow TrgSubCategoryRow in TrgCategoryTable.Select(Metadata_Category.CategoryGId + "='" + DIQueries.RemoveQuotesForSqlQuery(SrcSubCategory.CategoryGID) + "' AND " + Metadata_Category.ParentCategoryNId + "=" + TrgCategoryNid))
{
// if exists then get nid
SrcSubCategory.CategoryNId = Convert.ToInt32(TrgSubCategoryRow[Metadata_Category.CategoryNId]);
}
if (SrcSubCategory.CategoryNId > 0)
{
// update sub category into current database
this.UpdateMetadataCategory(SrcSubCategory);
}
else
{
// insert sub category into current database
this.InsertIntoDatabase(SrcSubCategory);
}
}
}
#endregion
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
/// <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>
/// Get source nid
/// </summary>
/// <param name="sourceName">source Name</param>
/// <returns>Source Nid</returns>
private int GetSourceNid(string sourceName)
{
int RetVal = 0;
try
{
RetVal = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(this.DBQueries.Source.GetSource(FilterFieldType.Name, "'" + DIQueries.RemoveQuotesForSqlQuery(sourceName) + "'", FieldSelection.NId, false)));
}
catch (Exception)
{
RetVal = 0;
}
return(RetVal);
}
