/// <summary>
/// Checks and insert metadata category into database.
/// </summary>
/// <param name="metadataCategory"></param>
/// <returns></returns>
public int CheckNInsertCategory(DI7MetadataCategoryInfo metadataCategory)
{
int RetVal = 0;
if (!string.IsNullOrEmpty(metadataCategory.CategoryGID) && !string.IsNullOrEmpty(metadataCategory.CategoryName))
{
RetVal = this.GetNidByGID(metadataCategory.CategoryGID, metadataCategory.CategoryType);
if (RetVal <= 0)
{
RetVal = this.GetNidByName(metadataCategory.CategoryName, metadataCategory.CategoryType, metadataCategory.ParentNid.ToString());
}
if (RetVal > 0)
{
// update category info
this.UpdateMetadataCategory(metadataCategory);
}
else
{
// insert metadata category
RetVal = this.InsertIntoDatabase(metadataCategory);
}
}
return(RetVal);
}
/// <summary>
/// Insert MetadataCategory record into database
/// </summary>
/// <param name="MetadataCategoryInfo">object of IndicatorInfo</param>
/// <returns>Ture/False. Return true after successful insertion otherwise false</returns>
public int InsertIntoDatabase(DI7MetadataCategoryInfo metadataCategory)
{
int RetVal = 0;
string MetadataCategoryName = metadataCategory.CategoryName;
string LanguageCode = string.Empty;
string DefaultLanguageCode = string.Empty;
string MetadataCategoryForDatabase = string.Empty;
DITables TablesName;
int LastOrder = 0;
try
{
DefaultLanguageCode = this.DBQueries.LanguageCode;
//get and set category order
// get max category order from database/template
try
{
LastOrder = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(this.DBQueries.Metadata_Category.GetMaxMetadataCategoryOrder("'" + metadataCategory.CategoryType + "'", metadataCategory.ParentNid.ToString())));
}
catch (Exception)
{
}
LastOrder += 1;
metadataCategory.CategoryOrder = LastOrder;
// insert metadata category into database/template
foreach (DataRow languageRow in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = languageRow[Language.LanguageCode].ToString();
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
//if (LanguageCode == DefaultLanguageCode.Replace("_", String.Empty))
//{
MetadataCategoryForDatabase = MetadataCategoryName;
//}
//else
//{
// MetadataCategoryForDatabase = Constants.PrefixForNewValue + MetadataCategoryName;
//}
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertMetadataCategory(TablesName.MetadataCategory, MetadataCategoryForDatabase, metadataCategory.CategoryGID,
metadataCategory.Description, metadataCategory.CategoryType, metadataCategory.CategoryOrder, metadataCategory.ParentNid.ToString(), metadataCategory.IsPresentational, metadataCategory.IsMandatory));
}
RetVal = this.DBConnection.GetNewId();
}
catch (Exception)
{
RetVal = 0;
}
return(RetVal);
}
private DI7MetadataCategoryInfo GetMedataCategoryInfo(DataTable table)
{
DI7MetadataCategoryInfo RetVal = new DI7MetadataCategoryInfo();
foreach (DataRow Row in table.Rows)
{
RetVal = DI7MetadataCategoryBuilder.GetMetadataCategoryInfo(Row, this.DBConnection, this.DBQueries);
break;
}
return(RetVal);
}
/// <summary>
/// Updates Metadata category
/// </summary>
/// <param name="metadataCategoryInfo"></param>
public void UpdateMetadataCategory(DI7MetadataCategoryInfo metadataCategoryInfo)
{
string SqlQuery = string.Empty;
try
{
if (metadataCategoryInfo.CategoryNId > 0)
{
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Update.UpdateMetadataCategory(this.DBQueries.TablesName.MetadataCategory, metadataCategoryInfo.CategoryNId, DICommon.RemoveQuotes(metadataCategoryInfo.CategoryName), metadataCategoryInfo.CategoryType, metadataCategoryInfo.CategoryGID, metadataCategoryInfo.Description, metadataCategoryInfo.ParentNid.ToString(), metadataCategoryInfo.IsMandatory, metadataCategoryInfo.IsPresentational);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception)
{
}
}
/// <summary>
/// Returns metadata category info(with sub categories)
/// </summary>
/// <param name="categoryNid"></param>
/// <returns></returns>
public DI7MetadataCategoryInfo GetMedataCategoryInfo(int categoryNid)
{
DI7MetadataCategoryInfo RetVal = null;
DataTable Table = null;
try
{
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.NId, Convert.ToString(categoryNid)));
RetVal = this.GetMedataCategoryInfo(Table);
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private static DI7MetadataCategoryInfo GetMetadataCategoryInfo(DataRow row, DIConnection dbConnection, DIQueries dbQueries)
{
DI7MetadataCategoryInfo RetVal = new DI7MetadataCategoryInfo();
DI7MetadataCategoryInfo SubCategory;
RetVal.CategoryNId = Convert.ToInt32(row[Metadata_Category.CategoryNId]);
RetVal.CategoryGID = Convert.ToString(row[Metadata_Category.CategoryGId]);
RetVal.CategoryName = Convert.ToString(row[Metadata_Category.CategoryName]);
RetVal.Description = Convert.ToString(row[Metadata_Category.CategoryDescription]);
RetVal.CategoryOrder = Convert.ToInt32(row[Metadata_Category.CategoryOrder]);
RetVal.CategoryType = Convert.ToString(row[Metadata_Category.CategoryType]);
RetVal.IsMandatory = Convert.ToBoolean(row[Metadata_Category.IsMandatory]);
RetVal.IsPresentational = Convert.ToBoolean(row[Metadata_Category.IsPresentational]);
RetVal.ParentNid = Convert.ToInt32(row[Metadata_Category.ParentCategoryNId]);
// add subcategories (if any)
RetVal.SubCategories = new Dictionary <string, DI7MetadataCategoryInfo>();
foreach (DataRow SubCategoryRow in dbConnection.ExecuteDataTable(dbQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Type, "'" + RetVal.CategoryType + "'", RetVal.CategoryNId.ToString())).Rows)
{
if (!RetVal.SubCategories.ContainsKey(Convert.ToString(SubCategoryRow[Metadata_Category.CategoryGId])))
{
RetVal.SubCategories.Add(Convert.ToString(SubCategoryRow[Metadata_Category.CategoryGId]), new DI7MetadataCategoryInfo());
}
// Add subcategory information
SubCategory = RetVal.SubCategories[Convert.ToString(SubCategoryRow[Metadata_Category.CategoryGId])];
SubCategory.CategoryNId = Convert.ToInt32(SubCategoryRow[Metadata_Category.CategoryNId]);
SubCategory.CategoryGID = Convert.ToString(SubCategoryRow[Metadata_Category.CategoryGId]);
SubCategory.CategoryName = Convert.ToString(SubCategoryRow[Metadata_Category.CategoryName]);
SubCategory.Description = Convert.ToString(SubCategoryRow[Metadata_Category.CategoryDescription]);
SubCategory.CategoryOrder = Convert.ToInt32(SubCategoryRow[Metadata_Category.CategoryOrder]);
SubCategory.CategoryType = Convert.ToString(SubCategoryRow[Metadata_Category.CategoryType]);
SubCategory.IsMandatory = Convert.ToBoolean(SubCategoryRow[Metadata_Category.IsMandatory]);
SubCategory.IsPresentational = Convert.ToBoolean(SubCategoryRow[Metadata_Category.IsPresentational]);
SubCategory.ParentNid = Convert.ToInt32(SubCategoryRow[Metadata_Category.ParentCategoryNId]);
}
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);
}
}
private void InsertMetadataReportFromXML(int targetNid, bool isNameAttributeExist, DI7MetadataCategoryBuilder MDBuilder, XmlNode ReportNode, string metadataTypeText, int categoryParentNId)
{
string MDCategotyID = string.Empty;
string MDCategotyName = string.Empty;
DI7MetadataCategoryInfo MDCateInfo = null;
int CategotyNid = -1;
foreach (XmlNode ReportAttNode in ReportNode.ChildNodes)
{
if (ReportAttNode.Name == ATTRIBUTESET_ELEMENT)
{
this.InsertMetadataReportFromXML(targetNid, isNameAttributeExist, MDBuilder, ReportAttNode, metadataTypeText, categoryParentNId);
}
if (ReportAttNode.Name == "ReportedAttribute")
{
MDCategotyID = Convert.ToString(ReportAttNode.Attributes[ID_ATTRIBUTE].Value);
if (isNameAttributeExist)
{
MDCategotyName = Convert.ToString(ReportAttNode.Attributes[NAME_ATTRIBUTE].Value);
}
else
{
MDCategotyName = MDCategotyID;
}
try
{
if (!string.IsNullOrEmpty(ReportAttNode.InnerXml) && Convert.ToString(ReportAttNode[COMMONTEXT_ELEMENT].Attributes[XMLLANG_ATTRIBUTE].Value).Trim('_') == this.DBQueries.LanguageCode.Trim('_'))
{
string MetadataReport = Convert.ToString(ReportAttNode[COMMONTEXT_ELEMENT].InnerText);
if (!string.IsNullOrEmpty(MetadataReport))
{
MDCateInfo = new DI7MetadataCategoryInfo();
MDCateInfo.CategoryGID = MDCategotyID;
MDCateInfo.CategoryName = MDCategotyName;
MDCateInfo.CategoryType = metadataTypeText;
MDCateInfo.ParentNid = categoryParentNId;
CategotyNid = MDBuilder.CheckNInsertCategory(MDCateInfo);
//--
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataReport.Insert.InsertMetadataReport(this.DBQueries.TablesName.MetadataReport, targetNid.ToString(), CategotyNid.ToString(), MetadataReport));
if (ReportAttNode.ChildNodes.Count > 0)
{
this.InsertMetadataReportFromXML(targetNid, isNameAttributeExist, MDBuilder, ReportAttNode, metadataTypeText, CategotyNid);
}
}
else
{
//this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataReport.Delete.DeleteMetadataReport(this.DBQueries.TablesName.MetadataReport, targetNid.ToString(), CategotyNid.ToString()));
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public bool ImportMetataFromExcel(MetadataElementType categoryType, MetaDataType metadataType, int elementNId, string xlsFileNameWPath, string xsltFldrPath)
{
bool RetVal = true;
string MetadataText = string.Empty;
string FirstColumnValue = string.Empty;
string SecondColumnValue = string.Empty;
string ThirdColumnValue = string.Empty;
string ElementName = string.Empty;
string ElementGID = string.Empty;
DataTable ExcelDataTable = null;
DataRow Row;
DI7MetaDataBuilder MDBuilder;
DI7MetadataCategoryBuilder MDCategoryBuilder;
DI7MetadataCategoryInfo MetadataCategory;
DIExcel ExcelFile = null;
try
{
// -- Get data table from excel file
ExcelFile = new DIExcel(xlsFileNameWPath);
ExcelDataTable = ExcelFile.GetDataTableFromSheet(ExcelFile.GetSheetName(0));
// -- create database builder objects
MDCategoryBuilder = new DI7MetadataCategoryBuilder(this.DBConnection, this.DBQueries);
MDBuilder = new DI7MetaDataBuilder(this.DBConnection, this.DBQueries);
// -- import metadata reports with category
for (int RowIndex = 1; RowIndex < ExcelDataTable.Rows.Count; RowIndex++)
{
Row = ExcelDataTable.Rows[RowIndex];
FirstColumnValue = Convert.ToString(Row[0]);
SecondColumnValue = Convert.ToString(Row[1]);
ThirdColumnValue = Convert.ToString(Row[2]);
// get element name
if (string.IsNullOrEmpty(ElementName))
{
if (string.IsNullOrEmpty(FirstColumnValue))
{
break;
}
else
{
ElementName = FirstColumnValue;
continue;
}
}
// get element gid/id
if (string.IsNullOrEmpty(ElementGID))
{
if (string.IsNullOrEmpty(FirstColumnValue))
{
break;
}
else
{
ElementGID = FirstColumnValue;
// get element nid by element gid
if (metadataType == MetaDataType.Source)
{
elementNId = this.GetElementNidByGID(ElementName, metadataType);
}
else
{
elementNId = this.GetElementNidByGID(ElementGID, metadataType);
}
// Skip title row by incrementing row index
RowIndex++;
continue;
}
}
// continue if row is blank
if (string.IsNullOrEmpty(FirstColumnValue) && string.IsNullOrEmpty(SecondColumnValue) && string.IsNullOrEmpty(ThirdColumnValue))
{
// reset element value
elementNId = 0;
ElementName = string.Empty;
ElementGID = string.Empty;
continue;
}
else if (elementNId > 0)
{
// import metadata report with metadata category
// get metadata category and metedata report
MetadataCategory = new DI7MetadataCategoryInfo();
MetadataCategory.CategoryName = SecondColumnValue;
MetadataCategory.CategoryGID = FirstColumnValue;
MetadataCategory.CategoryType = DIQueries.MetadataElementTypeText[categoryType];
// import metadata category
MetadataCategory.CategoryNId = MDCategoryBuilder.CheckNInsertCategory(MetadataCategory);
// import metadata report
if (MetadataCategory.CategoryNId > 0)
{
MDBuilder.InsertORUpdateMetadataInfo(metadataType, MetadataCategory.CategoryNId.ToString(), elementNId, this.ReplaceNewLineInMetadataReport(ThirdColumnValue));
}
}
}
}
catch (Exception)
{
RetVal = false;
}
finally
{
if (ExcelFile != null)
{
ExcelFile.Close();
}
}
return(RetVal);
}
private void InsertMetadataReportFromXML(int targetNid, bool isNameAttributeExist, DI7MetadataCategoryBuilder MDBuilder, XmlNode ReportNode, string metadataTypeText, int categoryParentNId)
{
string MDCategotyID = string.Empty;
string MDCategotyName = string.Empty;
DI7MetadataCategoryInfo MDCateInfo = null;
int CategotyNid = -1;
foreach (XmlNode ReportAttNode in ReportNode.ChildNodes)
{
if (ReportAttNode.Name == ATTRIBUTESET_ELEMENT)
{
this.InsertMetadataReportFromXML(targetNid, isNameAttributeExist, MDBuilder, ReportAttNode, metadataTypeText, categoryParentNId);
}
if (ReportAttNode.Name == "ReportedAttribute")
{
MDCategotyID = Convert.ToString(ReportAttNode.Attributes[ID_ATTRIBUTE].Value);
if (isNameAttributeExist)
{
MDCategotyName = Convert.ToString(ReportAttNode.Attributes[NAME_ATTRIBUTE].Value);
}
else
{
MDCategotyName = MDCategotyID;
}
try
{
if (!string.IsNullOrEmpty(ReportAttNode.InnerXml) && Convert.ToString(ReportAttNode[COMMONTEXT_ELEMENT].Attributes[XMLLANG_ATTRIBUTE].Value).Trim('_') == this.DBQueries.LanguageCode.Trim('_'))
{
string MetadataReport = Convert.ToString(ReportAttNode[COMMONTEXT_ELEMENT].InnerText);
if (!string.IsNullOrEmpty(MetadataReport))
{
MDCateInfo = new DI7MetadataCategoryInfo();
MDCateInfo.CategoryGID = MDCategotyID;
MDCateInfo.CategoryName = MDCategotyName;
MDCateInfo.CategoryType = metadataTypeText;
MDCateInfo.ParentNid = categoryParentNId;
CategotyNid = MDBuilder.CheckNInsertCategory(MDCateInfo);
//--
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataReport.Insert.InsertMetadataReport(this.DBQueries.TablesName.MetadataReport, targetNid.ToString(), CategotyNid.ToString(), MetadataReport));
if (ReportAttNode.ChildNodes.Count > 0)
{
this.InsertMetadataReportFromXML(targetNid, isNameAttributeExist, MDBuilder, ReportAttNode, metadataTypeText, CategotyNid);
}
}
else
{
//this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataReport.Delete.DeleteMetadataReport(this.DBQueries.TablesName.MetadataReport, targetNid.ToString(), CategotyNid.ToString()));
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public bool ImportMetataFromExcel(MetadataElementType categoryType, MetaDataType metadataType, int elementNId, string xlsFileNameWPath, string xsltFldrPath)
{
bool RetVal = true;
string MetadataText = string.Empty;
string FirstColumnValue = string.Empty;
string SecondColumnValue = string.Empty;
string ThirdColumnValue = string.Empty;
string ElementName = string.Empty;
string ElementGID = string.Empty;
DataTable ExcelDataTable = null;
DataRow Row;
DI7MetaDataBuilder MDBuilder;
DI7MetadataCategoryBuilder MDCategoryBuilder;
DI7MetadataCategoryInfo MetadataCategory;
DIExcel ExcelFile = null;
try
{
// -- Get data table from excel file
ExcelFile = new DIExcel(xlsFileNameWPath);
ExcelDataTable = ExcelFile.GetDataTableFromSheet(ExcelFile.GetSheetName(0));
// -- create database builder objects
MDCategoryBuilder = new DI7MetadataCategoryBuilder(this.DBConnection, this.DBQueries);
MDBuilder = new DI7MetaDataBuilder(this.DBConnection, this.DBQueries);
// -- import metadata reports with category
for (int RowIndex = 1; RowIndex < ExcelDataTable.Rows.Count; RowIndex++)
{
Row = ExcelDataTable.Rows[RowIndex];
FirstColumnValue = Convert.ToString(Row[0]);
SecondColumnValue = Convert.ToString(Row[1]);
ThirdColumnValue = Convert.ToString(Row[2]);
// get element name
if (string.IsNullOrEmpty(ElementName))
{
if (string.IsNullOrEmpty(FirstColumnValue))
{
break;
}
else
{
ElementName = FirstColumnValue;
continue;
}
}
// get element gid/id
if (string.IsNullOrEmpty(ElementGID))
{
if (string.IsNullOrEmpty(FirstColumnValue))
{
break;
}
else
{
ElementGID = FirstColumnValue;
// get element nid by element gid
if (metadataType == MetaDataType.Source)
{
elementNId = this.GetElementNidByGID(ElementName, metadataType);
}
else
{
elementNId = this.GetElementNidByGID(ElementGID, metadataType);
}
// Skip title row by incrementing row index
RowIndex++;
continue;
}
}
// continue if row is blank
if (string.IsNullOrEmpty(FirstColumnValue) && string.IsNullOrEmpty(SecondColumnValue) && string.IsNullOrEmpty(ThirdColumnValue))
{
// reset element value
elementNId = 0;
ElementName = string.Empty;
ElementGID = string.Empty;
continue;
}
else if (elementNId > 0)
{
// import metadata report with metadata category
// get metadata category and metedata report
MetadataCategory = new DI7MetadataCategoryInfo();
MetadataCategory.CategoryName = SecondColumnValue;
MetadataCategory.CategoryGID = FirstColumnValue;
MetadataCategory.CategoryType = DIQueries.MetadataElementTypeText[categoryType];
// import metadata category
MetadataCategory.CategoryNId = MDCategoryBuilder.CheckNInsertCategory(MetadataCategory);
// import metadata report
if (MetadataCategory.CategoryNId > 0)
{
MDBuilder.InsertORUpdateMetadataInfo(metadataType, MetadataCategory.CategoryNId.ToString(), elementNId, this.ReplaceNewLineInMetadataReport(ThirdColumnValue));
}
}
}
}
catch (Exception)
{
RetVal = false;
}
finally
{
if (ExcelFile != null)
{
ExcelFile.Close();
}
}
return RetVal;
}
