/// <summary>
/// Returns units
/// </summary>
/// <param name="unitName"></param>
/// <returns></returns>
/// <remarks>Pass Empty String For All Records</remarks>
public DataTable GetAllUnits(string unitName)
{
DataTable RetVal;
string SqlQuery = string.Empty;
string FilterString = string.Empty;
unitName = DICommon.RemoveQuotes(unitName);
try
{
//get query
if (!string.IsNullOrEmpty(unitName))
{
FilterString = " " + Unit.UnitName + " LIKE '%" + unitName + "%' ";
SqlQuery = this.DBQueries.Unit.GetUnit(FilterFieldType.Search, "" + FilterString + "");
}
else
{
SqlQuery = this.DBQueries.Unit.GetUnit(FilterFieldType.None, string.Empty);
}
//get datatable
RetVal = this.DBConnection.ExecuteDataTable(SqlQuery);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Returns Timeperiods
/// </summary>
/// <param name="timePeriod"></param>
/// <returns></returns>
/// <remarks>Pass Empty String For All Records</remarks>
public DataTable GetAllTimePeriods(string timePeriod)
{
DataTable RetVal;
string SqlQuery = string.Empty;
string FilterString = string.Empty;
timePeriod = DICommon.RemoveQuotes(timePeriod);
try
{
//get query
if (!string.IsNullOrEmpty(timePeriod))
{
FilterString = " " + Timeperiods.TimePeriod + " LIKE '%" + timePeriod + "%' ";
SqlQuery = this.DBQueries.Timeperiod.GetTimePeriod(FilterFieldType.Search, "" + FilterString + "");
}
else
{
SqlQuery = this.DBQueries.Timeperiod.GetTimePeriod(FilterFieldType.None, string.Empty);
}
//get datatable
RetVal = this.DBConnection.ExecuteDataTable(SqlQuery);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Returns areas
/// </summary>
///<param name="name"></param>
/// <returns></returns>
public DataTable GetAreaByName(string name)
{
DataTable RetVal;
string SqlQuery = string.Empty;
string FilterString = string.Empty;
name = DICommon.RemoveQuotes(name);
try
{
if (string.IsNullOrEmpty(name))
{
SqlQuery = this.DBQueries.Area.GetArea(FilterFieldType.None, string.Empty);
}
else
{
FilterString = " " + Area.AreaName + " LIKE '%" + name + "%' ";
SqlQuery = this.DBQueries.Area.GetArea(FilterFieldType.Search, FilterString);
}
//get datatable
RetVal = this.DBConnection.ExecuteDataTable(SqlQuery);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Returns indicators
/// </summary>
/// <param name="indicatorName"></param>
/// <returns></returns>
public DataTable GetAllIndicators(string indicatorName)
{
DataTable RetVal;
string SqlQuery = string.Empty;
string FilterString = string.Empty;
indicatorName = DICommon.RemoveQuotes(indicatorName);
try
{
//get query
if (!string.IsNullOrEmpty(indicatorName))
{
FilterString = " " + Indicator.IndicatorName + " LIKE '%" + indicatorName + "%' ";
SqlQuery = this.DBQueries.Indicators.GetIndicator(FilterFieldType.Search, FilterString, FieldSelection.Light);
}
else
{
SqlQuery = this.DBQueries.Indicators.GetIndicator(FilterFieldType.None, string.Empty, FieldSelection.Light);
}
//get datatable
RetVal = this.DBConnection.ExecuteDataTable(SqlQuery);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Imports records from source database to target database/template
/// </summary>
/// <param name="selectedNids"></param>
/// <param name="allSelected">Set true to import all records</param>
public override void ImportValues(List <string> selectedNids, bool allSelected)
{
DI6SubgroupTypeBuilder SGTypeBuilderObj = new DI6SubgroupTypeBuilder(this._TargetDBConnection, this._TargetDBQueries);
DI6SubgroupTypeInfo SourceDBSubgroupType;
DataRow Row;
int ProgressBarValue = 0;
foreach (string Nid in selectedNids)
{
try
{
//get subgroup Type from source table
Row = this.SourceTable.Select(SubgroupTypes.SubgroupTypeNId + "=" + Nid)[0];
SourceDBSubgroupType = new DI6SubgroupTypeInfo();
SourceDBSubgroupType.Name = DICommon.RemoveQuotes(Row[SubgroupTypes.SubgroupTypeName].ToString());
SourceDBSubgroupType.GID = Row[SubgroupTypes.SubgroupTypeGID].ToString();
SourceDBSubgroupType.Global = Convert.ToBoolean(Row[SubgroupTypes.SubgroupTypeGlobal]);
SourceDBSubgroupType.Nid = Convert.ToInt32(Row[SubgroupTypes.SubgroupTypeNId]);
SourceDBSubgroupType.Order = Convert.ToInt32(Row[SubgroupTypes.SubgroupTypeOrder]);
//import into target database
SGTypeBuilderObj.ImportSubgroupType(SourceDBSubgroupType, SourceDBSubgroupType.Nid, this.SourceDBQueries, this.SourceDBConnection);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
this.RaiseIncrementProgessBarEvent(ProgressBarValue);
ProgressBarValue++;
}
}
/// <summary>
/// Imports records from source database to target database/template
/// </summary>
/// <param name="selectedNids"></param>
/// <param name="allSelected">Set true to import all records</param>
public override void ImportValues(List <string> selectedNids, bool allSelected)
{
UnitBuilder UnitBuilderObj = new UnitBuilder(this._TargetDBConnection, this._TargetDBQueries);
UnitInfo SourceDBUnit;
DataRow Row;
int ProgressBarValue = 0;
foreach (string Nid in selectedNids)
{
try
{
//get unit from source table
Row = this.SourceTable.Select(Unit.UnitNId + "=" + Nid)[0];
SourceDBUnit = new UnitInfo();
SourceDBUnit.Name = DICommon.RemoveQuotes(Row[Unit.UnitName].ToString());
SourceDBUnit.GID = Row[Unit.UnitGId].ToString();
SourceDBUnit.Global = Convert.ToBoolean(Row[Unit.UnitGlobal]);
SourceDBUnit.Nid = Convert.ToInt32(Row[Unit.UnitNId]);
//import into target database
UnitBuilderObj.ImportUnit(SourceDBUnit, SourceDBUnit.Nid, this.SourceDBQueries, this.SourceDBConnection);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
this.RaiseIncrementProgessBarEvent(ProgressBarValue);
ProgressBarValue++;
}
}
//private void DeleteExtraRows(DataTable table)
//{
// int IndicatorNid = 0;
// int UnitNid = 0;
// int SubgroupValNid = 0;
// int ICNid = 0;
// string FilterString = string.Empty;
// string LevelColumnName = string.Empty;
// List<ExtaRowInfo> ExtraRows = new List<ExtaRowInfo>();
// try
// {
// for (int Level = 1; Level < this.MaxLevel; Level++)
// {
// DataRow[] Rows = table.Select(Constants.LanguageKeys.Level + "=" + Level);
// // Get records where level is equal to 1
// foreach (DataRow Row in Rows)
// {
// ICNid = Convert.ToInt32(Row[IndicatorClassifications.ICNId]);
// IndicatorNid = Convert.ToInt32(Row[Indicator.IndicatorNId]);
// //create filterstring
// FilterString = string.Empty;
// for (int i = 1; i <= Level; i++)
// {
// LevelColumnName = Constants.LanguageKeys.Level + " " + i;
// FilterString += "[" + LevelColumnName + "]='" + Row[LevelColumnName].ToString() + "' And ";
// }
// FilterString += " " + Indicator.IndicatorNId + "=" + IndicatorNid;
// if (this._ShowIUS)
// {
// UnitNid = Convert.ToInt32(Row[Unit.UnitNId]);
// SubgroupValNid = Convert.ToInt32(Row[SubgroupVals.SubgroupValNId]);
// FilterString += " and " + Unit.UnitNId + "=" + UnitNid;
// FilterString += " and " + SubgroupVals.SubgroupValNId + "=" + SubgroupValNid;
// }
// //delete row if this is not the lowest IC level for the current IUS
// if (table.Select(FilterString).Length > 1)
// {
// //ExtraRows.Add(new ExtaRowInfo(ICNid, IndicatorNid, UnitNid, SubgroupValNid));
// Row.Delete();
// table.AcceptChanges();
// }
// }
// }
// }
// catch (Exception ex)
// {
// throw new ApplicationException(ex.ToString());
// }
//}
private DataTable DeleteExtraRows(DataTable table)
{
DataTable RetVal;
int IndicatorNid = 0;
int UnitNid = 0;
int SubgroupValNid = 0;
int ICNid = 0;
string FilterString = string.Empty;
string LevelColumnName = string.Empty;
try
{
RetVal = table.Clone();
DataRow NewRow;
for (int Level = this.MaxLevel; Level > 0; Level--)
{
DataRow[] Rows = table.Select(Constants.LanguageKeys.Level + "=" + Level);
// Get records by level
foreach (DataRow Row in Rows)
{
IndicatorNid = Convert.ToInt32(Row[Indicator.IndicatorNId]);
LevelColumnName = Constants.LanguageKeys.Level + " " + Level;
FilterString = "[" + LevelColumnName + "]='" + DICommon.RemoveQuotes(Row[LevelColumnName].ToString()) + "' And ";
FilterString += " " + Indicator.IndicatorNId + "=" + IndicatorNid;
if (this._ShowIUS)
{
UnitNid = Convert.ToInt32(Row[Unit.UnitNId]);
SubgroupValNid = Convert.ToInt32(Row[SubgroupVals.SubgroupValNId]);
FilterString += " and " + Unit.UnitNId + "=" + UnitNid;
FilterString += " and " + SubgroupVals.SubgroupValNId + "=" + SubgroupValNid;
}
if (RetVal.Select(FilterString).Length == 0)
{
RetVal.Rows.Add(Row.ItemArray);
}
}
}
RetVal.AcceptChanges();
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Imports records from source database to target database/template
/// </summary>
/// <param name="selectedNids"></param>
/// <param name="allSelected">Set true to import all records</param>
private void ImportSectors(List <string> selectedNids, bool allSelected)
{
int ProgressBarValue = 0;
IndicatorClassificationInfo SrcClassification;
DataRow Row;
List <string> ImportedNids = new List <string>();
foreach (string Nid in selectedNids)
{
try
{
//get ic from source table
Row = this.SourceTable.Select(this.TagValueColumnName + "=" + Nid)[0];
if (!ImportedNids.Contains(Row[IndicatorClassifications.ICNId].ToString()))
{
SrcClassification = new IndicatorClassificationInfo();
SrcClassification.Name = DICommon.RemoveQuotes(Row[IndicatorClassifications.ICName].ToString());
SrcClassification.GID = Row[IndicatorClassifications.ICGId].ToString();
SrcClassification.IsGlobal = Convert.ToBoolean(Row[IndicatorClassifications.ICGlobal]);
SrcClassification.Nid = Convert.ToInt32(Row[IndicatorClassifications.ICNId]);
if (!Information.IsDBNull(Row[IndicatorClassifications.ICInfo]))
{
SrcClassification.ClassificationInfo = DICommon.RemoveQuotes(Row[IndicatorClassifications.ICInfo].ToString());
}
SrcClassification.Parent = new IndicatorClassificationInfo();
SrcClassification.Parent.Nid = Convert.ToInt32(Row[IndicatorClassifications.ICParent_NId]);
SrcClassification.Type = this._IndicatorClassificationType;
//import into target database
Utility.CreateClassificationChainFromExtDB(
SrcClassification.Nid,
SrcClassification.Parent.Nid,
SrcClassification.GID,
SrcClassification.Name,
SrcClassification.Type,
SrcClassification.ClassificationInfo,
SrcClassification.IsGlobal,
this.SourceDBQueries, this.SourceDBConnection, this._TargetDBQueries, this._TargetDBConnection);
ImportedNids.Add(Row[IndicatorClassifications.ICNId].ToString());
}
this.RaiseIncrementProgessBarEvent(ProgressBarValue);
ProgressBarValue++;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
}
private DI6SubgroupValInfo GetSubgroupValInfo(DataRow row)
{
DI6SubgroupValInfo RetVal;
DataTable TempTable;
try
{
RetVal = new DI6SubgroupValInfo();
RetVal.Name = DICommon.RemoveQuotes(row[SubgroupVals.SubgroupVal].ToString());
RetVal.GID = row[SubgroupVals.SubgroupValGId].ToString();
RetVal.Global = Convert.ToBoolean(row[SubgroupVals.SubgroupValGlobal]);
RetVal.Nid = Convert.ToInt32(row[SubgroupVals.SubgroupValNId]);
////get nids of age,sex,others and location
//TempTable = this.SourceDBConnection.ExecuteDataTable(this.SourceDBQueries.Subgroup.GetSubgroupVals(FilterFieldType.NId, RetVal.Nid.ToString()));
//if (!Microsoft.VisualBasic.Information.IsDBNull(TempTable.Rows[0][SubgroupVals.SubgroupValAge]))
//{
// RetVal.Age.Nid = Convert.ToInt32(TempTable.Rows[0][SubgroupVals.SubgroupValAge]);
//}
//if (!Microsoft.VisualBasic.Information.IsDBNull(TempTable.Rows[0][SubgroupVals.SubgroupValSex]))
//{
// RetVal.Sex.Nid = Convert.ToInt32(TempTable.Rows[0][SubgroupVals.SubgroupValSex]);
//}
//if (!Microsoft.VisualBasic.Information.IsDBNull(TempTable.Rows[0][SubgroupVals.SubgroupValOthers]))
//{
// RetVal.Others.Nid = Convert.ToInt32(TempTable.Rows[0][SubgroupVals.SubgroupValOthers]);
//}
//if (!Microsoft.VisualBasic.Information.IsDBNull(TempTable.Rows[0][SubgroupVals.SubgroupValLocation]))
//{
// RetVal.Location.Nid = Convert.ToInt32(TempTable.Rows[0][SubgroupVals.SubgroupValLocation]);
//}
}
catch (Exception ex)
{
RetVal = null;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private UnitInfo GetUnitInfo(DataRow row)
{
UnitInfo RetVal;
try
{
//get unit from source table
RetVal = new UnitInfo();
RetVal.Name = DICommon.RemoveQuotes(row[Unit.UnitName].ToString());
RetVal.GID = row[Unit.UnitGId].ToString();
RetVal.Global = Convert.ToBoolean(row[Unit.UnitGlobal]);
RetVal.Nid = Convert.ToInt32(row[Unit.UnitNId]);
}
catch (Exception ex)
{
RetVal = null;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private DI6SubgroupValInfo GetSubgroupValInfo(DataRow row)
{
DI6SubgroupValInfo RetVal;
DataTable TempTable;
try
{
RetVal = new DI6SubgroupValInfo();
RetVal.Name = DICommon.RemoveQuotes(row[SubgroupVals.SubgroupVal].ToString());
RetVal.GID = row[SubgroupVals.SubgroupValGId].ToString();
RetVal.Global = Convert.ToBoolean(row[SubgroupVals.SubgroupValGlobal]);
RetVal.Nid = Convert.ToInt32(row[SubgroupVals.SubgroupValNId]);
}
catch (Exception ex)
{
RetVal = null;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private IndicatorInfo GetIndicatorInfo(DataRow row)
{
IndicatorInfo RetVal;
try
{
//get unit from source table
RetVal = new IndicatorInfo();
RetVal.Name = DICommon.RemoveQuotes(row[Indicator.IndicatorName].ToString());
RetVal.GID = row[Indicator.IndicatorGId].ToString();
RetVal.Global = Convert.ToBoolean(row[Indicator.IndicatorGlobal]);
RetVal.Info = DICommon.RemoveQuotes(Convert.ToString(row[Indicator.IndicatorInfo]));
RetVal.Nid = Convert.ToInt32(row[Indicator.IndicatorNId]);
}
catch (Exception ex)
{
RetVal = null;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private void ImportSector(DataRow row)
{
IndicatorClassificationInfo SrcClassification;
try
{
//get ic from source table
SrcClassification = new IndicatorClassificationInfo();
SrcClassification.Name = DICommon.RemoveQuotes(row[IndicatorClassifications.ICName].ToString());
SrcClassification.GID = row[IndicatorClassifications.ICGId].ToString();
SrcClassification.IsGlobal = Convert.ToBoolean(row[IndicatorClassifications.ICGlobal]);
SrcClassification.Nid = Convert.ToInt32(row[IndicatorClassifications.ICNId]);
if (!Information.IsDBNull(row[IndicatorClassifications.ICInfo]))
{
SrcClassification.ClassificationInfo = DICommon.RemoveQuotes(row[IndicatorClassifications.ICInfo].ToString());
}
SrcClassification.Parent = new IndicatorClassificationInfo();
SrcClassification.Parent.Nid = Convert.ToInt32(row[IndicatorClassifications.ICParent_NId]);
SrcClassification.Type = ICType.Sector;
//import into target database
Utility.CreateClassificationChainFromExtDB(
SrcClassification.Nid,
SrcClassification.Parent.Nid,
SrcClassification.GID,
SrcClassification.Name,
SrcClassification.Type,
SrcClassification.ClassificationInfo,
SrcClassification.IsGlobal,
this.SourceDBQueries, this.SourceDBConnection, this._TargetDBQueries, this._TargetDBConnection);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
public void ReadXmlAndUpdateCategoryTable(string metadataXmlInfo, string metadataTypeText)
{
int CategoryOrder = 0;
XmlDocument XmlDoc = new XmlDocument();
XmlNodeList RootNodeList;
string DefaultCategoryLanguageValue = string.Empty;
MetadataCategoryInfo MetadataInfo;
string CategoryForDatabase = string.Empty;
DITables Tables = null;
DIQueries TempDBQueries = null;
if (!string.IsNullOrEmpty(metadataXmlInfo.Trim()))
{
XmlDoc.LoadXml(metadataXmlInfo);
// Get "Metadata" Root Element NodeList
RootNodeList = XmlDoc.SelectNodes(Constants.MetadataCategory.MetadataCategoryNodePath);
// Get first recrod from "metadata/Category" Node and insert into all available Metadata_Category language table
// Check Category In each "metadata/Category" Node
for (int Index = 0; Index < RootNodeList.Count; Index++)//reach (XmlElement InputNodeList in RootNodeList.Count)
{
MetadataInfo = new MetadataCategoryInfo();
// Set MetadataCategoryInfo Value
MetadataInfo.CategoryName = DICommon.RemoveQuotes(Convert.ToString(RootNodeList[Index].Attributes["name"].Value));
//-- Get Max Category Order
CategoryOrder = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(this.DBQueries.Metadata_Category.GetMaxMetadataCategoryOrder(metadataTypeText)));
MetadataInfo.CategoryOrder = CategoryOrder;
MetadataInfo.CategoryType = metadataTypeText;
// Add MetadataCategory Into all metdata category language tables
this.CheckNCreateMetadataCategory(MetadataInfo);
}
}
}
/// <summary>
/// Check and Create or Update Sender information
/// </summary>
/// <param name="senderInfoObj"></param>
/// <param name="isSender"></param>
public void CheckAndUpdateSender(SenderInfo senderInfoObj, bool isSender)
{
string SqlQuery = string.Empty;
if (this.ISSenderExist(isSender))
{
//-- UPDATE
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.SDMXUser.Update.UpdateSender(this.DBQueries.TablesName.SDMXUser, isSender, DICommon.RemoveQuotes(senderInfoObj.ID), DICommon.RemoveQuotes(senderInfoObj.SenderName), DICommon.RemoveQuotes(senderInfoObj.ContactName), DICommon.RemoveQuotes(senderInfoObj.Role), DICommon.RemoveQuotes(senderInfoObj.Department), DICommon.RemoveQuotes(senderInfoObj.Telephone), DICommon.RemoveQuotes(senderInfoObj.Email), DICommon.RemoveQuotes(senderInfoObj.Fax));
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
else
{
//-- INSERT
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.SDMXUser.Insert.InsertSender(this.DBQueries.TablesName.SDMXUser, isSender, DICommon.RemoveQuotes(senderInfoObj.ID), DICommon.RemoveQuotes(senderInfoObj.SenderName), DICommon.RemoveQuotes(senderInfoObj.ContactName), DICommon.RemoveQuotes(senderInfoObj.Role), DICommon.RemoveQuotes(senderInfoObj.Department), DICommon.RemoveQuotes(senderInfoObj.Telephone), DICommon.RemoveQuotes(senderInfoObj.Email), DICommon.RemoveQuotes(senderInfoObj.Fax));
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
}
/// <summary>
/// Update Recommended Source Label By DataNid
/// </summary>
/// <param name="dataNId"></param>
/// <param name="icIUSLabel"></param>
/// <returns></returns>
public bool UpdateRecommendedSources(int dataNId, string icIUSLabel)
{
bool RetVal = false;
string SqlQuery = string.Empty;
DITables tables = new DITables(this.DBQueries.DataPrefix, this.DBQueries.LanguageCode);
try
{
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Update.UpdateRecommendedSourceValue(tables.RecommendedSources, dataNId, DICommon.RemoveQuotes(icIUSLabel));
this.DBConnection.ExecuteNonQuery(SqlQuery);
RetVal = true;
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
private void ConvertAreaMapMetadata()
{
DataTable MapTable = null;
string MetadataText = string.Empty;
// Get Area_Map Metadata Table
MapTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Area.GetAreaMapMetadata(FilterFieldType.Search, Area_Map_Metadata.MetadataText + " IS NOT NULL AND " + Area_Map_Metadata.MetadataText + " <> '' ", FieldSelection.Heavy));
foreach (DataRow Row in MapTable.Rows)
{
MetadataText = Convert.ToString(Row[Area_Map_Metadata.MetadataText]);
if (!string.IsNullOrEmpty(MetadataText))
{
MetadataText = DICommon.CheckNConvertMetadataXml(MetadataText);
// Update IndicatorClassificationInfo(Metadata)
this.DBConnection.ExecuteNonQuery(this.DBQueries.Update.Area.UpdateAreaMetadataInfo(DICommon.RemoveQuotes(MetadataText), Convert.ToString(Row[Area_Map_Metadata.LayerName])));
}
}
}
private void UpdateXsltFromResource()
{
string DefaultXsltText = string.Empty;
DataTable XsltTable = null;
XsltTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Xslt.GetXSLT(FilterFieldType.None, string.Empty));
// Get Xslt Text from Resource File
DefaultXsltText = Resource1.XSLTFile2;
foreach (DataRow Row in XsltTable.Rows)
{
// Update XSLT Text value to Resource File Xslt Text
this.DBConnection.ExecuteNonQuery(DALQueries.Xslt.Update.UpdateXSLT(this.DBQueries.DataPrefix, DICommon.RemoveQuotes(DefaultXsltText), Convert.ToString(Row[XSLT.XSLTFile])));
}
}
private string GetUnitNIds(string[] searchString)
{
string RetVal = "";
string sSql = "";
IDataReader rd;
String AllIndicatorNIdsWithData = String.Empty;
string FilterClause = string.Empty;
//-- Creating Filter Clause (FilterClause may have Multiple like Clauses)
// -- Adding each element of SearchString into FilterClause
for (int i = 0; i <= searchString.Length - 1; i++)
{
if (FilterClause.Length > 0)
{
FilterClause += " OR ";
}
//change on 18-03-08.If searchString[i] is in quote use equal to (=) in query instead of like
// Change on 14-07-08 .Like search instead of equal in case of phrase search.
// When search text is written inside double quote.
// Use search text after removing quotes from first and last position in like claues
if (searchString[i].ToString().StartsWith("'") && searchString[i].ToString().EndsWith("'") && searchString[i].ToString() != "'")
{
//FilterClause += Indicator.IndicatorName + " =" + DICommon.EscapeWildcardChar(searchString[i]) ;
FilterClause += Unit.UnitName + " LIKE '%" + DICommon.EscapeWildcardChar(DICommon.RemoveQuotes(searchString[i].Substring(1, searchString[i].Length - 2))) + "%'";
}
else
{
FilterClause += Unit.UnitName + " LIKE '%" + DICommon.EscapeWildcardChar(DICommon.RemoveQuotes(searchString[i])) + "%'";
}
}
//Running Query to get IndicatorNId's
sSql = this.DBQueries.Unit.GetUnit(FilterFieldType.Search, FilterClause);
try
{
rd = this.DBConnection.ExecuteReader(sSql);
RetVal = DI_LibDAL.Connection.DIConnection.GetDelimitedValuesFromReader(rd, Unit.UnitNId);
rd.Close();
}
catch (Exception ex)
{
//rd.Close();
}
return(RetVal);
}
public virtual void MapRecord(string unmatchedKey, string availableKey)
{
MappedRowInfo RowInfo = null;
try
{
//-- Initialize MappedTable if not Initialized
if (this._MappedTable.MappedRows == null)
{
this._MappedTable.MappedRows = new Dictionary <string, MappedRowInfo>();
}
//-- Check Key value Exist or Not
if (!this._MappedTable.MappedRows.ContainsKey(availableKey))
{
RowInfo = new MappedRowInfo();
//-- Get Unmatched and Available Row
RowInfo.AvailableRow = this._AvailableTable.Copy().Select(this.KeyColumnName + "='" + DICommon.RemoveQuotes(availableKey) + "'")[0];
RowInfo.UnmatchedRow = this._UnmatchedTable.Copy().Select(this.KeyColumnName + "='" + DICommon.RemoveQuotes(unmatchedKey) + "'")[0];
RowInfo.KeyValue = availableKey;
this._MappedTable.MappedRows.Add(availableKey, RowInfo);
this.DeleteRecord(unmatchedKey);
}
}
catch (Exception ex) { throw ex; }
}
protected override bool IsElementAlreadyExists(DataRow unmatchedRow, DataRow availableRow)
{
bool RetVal = false;
DataTable Table;
string SelectionCriteria = string.Empty;
string SourceFileName = string.Empty;
try
{
//get sourcefiilename
SourceFileName = Convert.ToString(unmatchedRow[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);
// 1. check by GID
Table = this.TargetDBConnection.ExecuteDataTable(this.TargetDBQueries.IndicatorClassification.GetIC(FilterFieldType.GId, "'" + DICommon.RemoveQuotes(Convert.ToString(unmatchedRow[IndicatorClassifications.ICGId])) + "'", FieldSelection.Light));
if (Table != null && Table.Rows.Count > 0)
{
RetVal = true;
}
// 2. check src IC_Name exists under parent of target IC
if (RetVal == false)
{
SelectionCriteria = " " + IndicatorClassifications.ICParent_NId + "=" + Convert.ToInt32(availableRow[IndicatorClassifications.ICParent_NId]) + " AND " +
IndicatorClassifications.ICName + "='" + Convert.ToString(unmatchedRow[IndicatorClassifications.ICName]) + "' ";
Table = this.TargetDBConnection.ExecuteDataTable(this.TargetDBQueries.IndicatorClassification.GetIC(FilterFieldType.Search, SelectionCriteria, FieldSelection.Light));
if (Table != null && Table.Rows.Count > 0)
{
RetVal = true;
}
}
}
catch (Exception)
{
throw;
}
return(RetVal);
}
public override void Import(string selectedNIds)
{
IndicatorClassificationInfo SrcClassification;
string SrcFileName = string.Empty;
DataTable Table = null;
int ProgressCounter = 0;
//IndicatorClassificationBuilder ICBuilder = null;
//Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>();
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
// Initialize progress bar
this.RaiseProgressBarInitialize(selectedNIds.Split(',').GetUpperBound(0) + 1);
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportICs(selectedNIds));
////-- Step 2:Initialise Indicator Builder with Target DBConnection
//ICBuilder = new IndicatorClassificationBuilder(this.TargetDBConnection, this.TargetDBQueries);
try
{
//-- Step 3: Import Nids for each SourceFile
foreach (DataRow Row in Table.Copy().Rows)
{
try
{
if (SrcFileName != Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]))
{
SrcFileName = Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);
//-- Step 2:Initialise DBConnection
if (SourceDBConnection != null)
{
SourceDBConnection.Dispose();
}
SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, SrcFileName, String.Empty, MergetTemplateConstants.DBPassword);
SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection);
}
SrcClassification = new IndicatorClassificationInfo();
SrcClassification.Name = DICommon.RemoveQuotes(Row[IndicatorClassifications.ICName].ToString());
SrcClassification.GID = Row[IndicatorClassifications.ICGId].ToString();
SrcClassification.IsGlobal = Convert.ToBoolean(Row[IndicatorClassifications.ICGlobal]);
SrcClassification.Nid = Convert.ToInt32(Row[IndicatorClassifications.ICNId]);
if (!string.IsNullOrEmpty(Convert.ToString((Row[IndicatorClassifications.ICInfo]))))
{
SrcClassification.ClassificationInfo = DICommon.RemoveQuotes(Row[IndicatorClassifications.ICInfo].ToString());
}
SrcClassification.Parent = new IndicatorClassificationInfo();
SrcClassification.Parent.Nid = Convert.ToInt32(Row[IndicatorClassifications.ICParent_NId]);
SrcClassification.Type = this._CurrentICType;
//import into target database
this.CreateClassificationChainFromExtDB(
SrcClassification.Nid,
SrcClassification.Parent.Nid,
SrcClassification.GID,
SrcClassification.Name,
SrcClassification.Type,
SrcClassification.ClassificationInfo,
SrcClassification.IsGlobal,
SourceDBQueries, SourceDBConnection, this._TargetDBQueries, this._TargetDBConnection);
ProgressCounter += 1;
this.RaiseProgressBarIncrement(ProgressCounter);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
this._UnmatchedTable = this.GetUnmatchedTable();
this._AvailableTable = this.GetAvailableTable();
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
finally
{
if (SourceDBConnection != null)
{
SourceDBConnection.Dispose();
}
}
// Close ProgressBar
this.RaiseProgressBarClose();
}
/// <summary>
/// Insert DBMetadata information
/// </summary>
/// <param name="description"></param>
/// <param name="publisherName"></param>
/// <param name="publisherDate"></param>
/// <param name="publisherCountry"></param>
/// <param name="publisherRegion"></param>
/// <param name="publisherOffice"></param>
/// <param name="areaCount"></param>
/// <param name="indicatorCount"></param>
/// <param name="IUSCount"></param>
/// <param name="timeperiodCount"></param>
/// <param name="sourceCount"></param>
/// <param name="dataCount"></param>
public void InsertRecord(string description, string publisherName, string publisherDate,
string publisherCountry, string publisherRegion, string publisherOffice,
string areaCount, string indicatorCount, string IUSCount, string timeperiodCount, string sourceCount, string dataCount)
{
string SqlQuery = string.Empty;
DITables TablesName;
string DataPrefix = this.DBConnection.DIDataSetDefault();
try
{
if (string.IsNullOrEmpty(areaCount))
{
areaCount = "0";
}
if (string.IsNullOrEmpty(indicatorCount))
{
indicatorCount = "0";
}
if (string.IsNullOrEmpty(IUSCount))
{
IUSCount = "0";
}
if (string.IsNullOrEmpty(timeperiodCount))
{
timeperiodCount = "0";
}
if (string.IsNullOrEmpty(sourceCount))
{
sourceCount = "0";
}
if (string.IsNullOrEmpty(dataCount))
{
dataCount = "0";
}
foreach (DataRow Row in this.DBConnection.DILanguages(DataPrefix).Rows)
{
TablesName = new DITables(DataPrefix, "_" + Row[Language.LanguageCode].ToString());
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.InsertRecord(TablesName.DBMetadata, DICommon.RemoveQuotes(description),
DICommon.RemoveQuotes(publisherName), publisherDate, DICommon.RemoveQuotes(publisherCountry), DICommon.RemoveQuotes(publisherRegion),
DICommon.RemoveQuotes(publisherOffice), areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// To import indicator information from mapped indicator
/// </summary>
/// <param name="indicatorInfo"></param>
/// <param name="NidInSourceDB"></param>
/// <param name="NidInTrgDB"></param>
/// <param name="sourceQurey"></param>
/// <param name="sourceDBConnection"></param>
/// <returns></returns>
public int ImportIndicatorFrmMappedIndicator(IndicatorInfo indicatorInfo, int NidInSourceDB, int NidInTrgDB, DIQueries sourceQurey, DIConnection sourceDBConnection)
{
int RetVal = -1;
string metadataInfo = string.Empty;
string SqlString = string.Empty;
DataRow Row;
DataTable TempTable;
Dictionary <String, String> OldIconNId_NewIconNId = new Dictionary <string, string>();
MetaDataBuilder MetaDataBuilderObj;
IndicatorInfo TrgIndicatorInfo;
try
{
// set RetVal to targetNID
RetVal = NidInTrgDB;
if (RetVal > 0)
{
TrgIndicatorInfo = this.GetIndicatorInfo(FilterFieldType.NId, RetVal.ToString(), FieldSelection.Light);
// dont import if trg indicator is global but source indicator is local
if (TrgIndicatorInfo.Global & indicatorInfo.Global == false)
{
// dont import if trg indicator is global but source indicator is local
}
else
{
//update the gid,name and global on the basis of nid
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Update.UpdateByNid(this.DBQueries.DataPrefix, this.DBQueries.LanguageCode, indicatorInfo.Name, indicatorInfo.GID, indicatorInfo.Global, indicatorInfo.Info, RetVal));
}
}
//update/insert icon
DIIcons.ImportElement(NidInSourceDB, RetVal, IconElementType.Indicator, sourceQurey, sourceDBConnection, this.DBQueries, this.DBConnection);
OldIconNId_NewIconNId = DIIcons.ImportElement(NidInSourceDB, RetVal, IconElementType.MetadataIndicator, sourceQurey, sourceDBConnection, this.DBQueries, this.DBConnection);
// get metadata info.
metadataInfo = indicatorInfo.Info;
// Update IconNids in xml if exists
foreach (string OldIconName in OldIconNId_NewIconNId.Keys)
{
metadataInfo = metadataInfo.Replace(OldIconName, OldIconNId_NewIconNId[OldIconName].ToString());
}
metadataInfo = DICommon.CheckNConvertMetadataXml(metadataInfo);
// Update Metadata
this.DBConnection.ExecuteNonQuery(DALQueries.Indicator.Update.UpdateIndicatorInfo(this.DBQueries.DataPrefix, this.DBQueries.LanguageCode, DICommon.RemoveQuotes(metadataInfo), FilterFieldType.GId, indicatorInfo.GID));
// -- insert records in xslt tables
SqlString = sourceQurey.Xslt.GetXSLT(NidInSourceDB.ToString(), MetadataElementType.Indicator);
TempTable = sourceDBConnection.ExecuteDataTable(SqlString);
if (TempTable.Rows.Count > 0)
{
Row = TempTable.Rows[0];
MetaDataBuilderObj = new MetaDataBuilder(this.DBConnection, this.DBQueries);
MetaDataBuilderObj.ImportTransformInfo(Row[XSLT.XSLTText].ToString(), Row[XSLT.XSLTFile].ToString(), RetVal.ToString(), MetadataElementType.Indicator);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
return(RetVal);
}
/// <summary>
/// Updates DBMetadata information into current language table.
/// </summary>
/// <param name="NID"></param>
/// <param name="description"></param>
/// <param name="publisherName"></param>
/// <param name="publisherDate"></param>
/// <param name="publisherCountry"></param>
/// <param name="publisherRegion"></param>
/// <param name="publisherOffice"></param>
/// <param name="areaCount"></param>
/// <param name="indicatorCount"></param>
/// <param name="IUSCount"></param>
/// <param name="timeperiodCount"></param>
/// <param name="sourceCount"></param>
/// <param name="dataCount"></param>
public void UpdateRecord(int NID, string description, string publisherName, string publisherDate,
string publisherCountry, string publisherRegion, string publisherOffice,
string areaCount, string indicatorCount, string IUSCount, string timeperiodCount, string sourceCount, string dataCount)
{
string SqlQuery = string.Empty;
DITables TablesName;
string DataPrefix = this.DBConnection.DIDataSetDefault();
try
{
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Update.UpdateRecord(this.DBQueries.TablesName.DBMetadata, NID,
DICommon.RemoveQuotes(description), DICommon.RemoveQuotes(publisherName), publisherDate,
DICommon.RemoveQuotes(publisherCountry), DICommon.RemoveQuotes(publisherRegion), DICommon.RemoveQuotes(publisherOffice),
areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void CreateIndicatorTransformInfo(string XSLTText, string XsltFilename, string elementNId, MetadataElementType elementType)
{
// -- this function insert the values into RT_XSLT table
string SqlString = string.Empty;
try
{
int XsltNid = 0;
// -- step1 : check xslt info already exists or not
SqlString = this.DBQueries.Xslt.GetXSLT(FilterFieldType.Name, XsltFilename);
if (this.DBConnection.ExecuteDataTable(SqlString).Rows.Count == 0)
{
//-- step 2: insert into xslt table
SqlString = DALQueries.Xslt.Insert.InsertXSLT(this.DBQueries.DataPrefix, DICommon.RemoveQuotes(XSLTText), DICommon.RemoveQuotes(XsltFilename));
this.DBConnection.ExecuteNonQuery(SqlString);
}
else
{
//-- step 2: Update xslt table
SqlString = DALQueries.Xslt.Update.UpdateXSLT(this.DBQueries.DataPrefix, DICommon.RemoveQuotes(XSLTText), XsltFilename);
this.DBConnection.ExecuteNonQuery(SqlString);
}
//-- step 3: find the xslt_nid against xslFilename
SqlString = this.DBQueries.Xslt.GetXSLT(FilterFieldType.Name, XsltFilename);
// -- step 4: insert into Element_xslt table
XsltNid = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(SqlString));
SqlString = DALQueries.Xslt.Insert.InsertElementXSLT(this.DBQueries.DataPrefix, Convert.ToInt32(elementNId), elementType, Convert.ToInt32(XsltNid));
this.DBConnection.ExecuteNonQuery(SqlString);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void UpdateXsltMetadata()
{
DataTable MapTable = null;
string MetadataText = string.Empty;
// Get Area_Map Metadata Table
MapTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Xslt.GetXSLT(FilterFieldType.Search, XSLT.XSLTText + " IS NOT NULL OR " + XSLT.XSLTText + " <> '' "));
foreach (DataRow Row in MapTable.Rows)
{
MetadataText = Convert.ToString(Row[XSLT.XSLTText]);
if (!string.IsNullOrEmpty(MetadataText))
{
MetadataText = MetadataConverter.ConvertXml(MetadataText);
// Update IndicatorClassificationInfo(Metadata)
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Xslt.Update.UpdateXSLT(this.DBQueries.DataPrefix, DICommon.RemoveQuotes(MetadataText), Convert.ToString(Row[XSLT.XSLTFile])));
}
}
}
/// <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)
{
}
}
private void UpdateXsltIntoXsltTable()
{
string SqlQuery = string.Empty;
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Xslt.Update.UpdateXSLT(this.DBQueries.DataPrefix, DICommon.RemoveQuotes(DI_LibBAL.Resource1.XSLT_DI6));
try
{
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Updating XSLT from Resourse File into database.
/// </summary>
/// <param name="dataprefix"></param>
public void UpdateXSLT(string dataprefix)
{
string xsltString = DI7MetadataCategoryBuilder.GetMetadataXslt();
string SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Xslt.Update.UpdateXSLT(dataprefix, DICommon.RemoveQuotes(xsltString));
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
