/// <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 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 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++;
}
}
public override void Import(string selectedNids)
{
DataTable Table = null;
int ProgressCounter = 0;
AreaBuilder AreaBuilderObj = null;
AreaInfo AreaInfoObj = 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.GetImportAreas(this._CurrentTemplateFileNameWPath,selectedNids));
//-- Step 2:Initialise Indicator Builder with Target DBConnection
AreaBuilderObj = new AreaBuilder(this.TargetDBConnection, this.TargetDBQueries);
////-- Step 3: Import Nids for each SourceFile
//foreach (DataRow Row in Table.Copy().Rows)
//{
try
{
SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, this._CurrentTemplateFileNameWPath, String.Empty, MergetTemplateConstants.DBPassword);
SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection);
// AreaInfoObj = this.GetIndicatorInfo(Row);
//AreaBuilderObj.ImportArea(selectedNids, 1, SourceDBConnection, SourceDBQueries);
//AreaBuilderObj.ImportAreaMaps(selectedNids, 1, SourceDBConnection, SourceDBQueries);
AreaBuilderObj.ImportArea(selectedNids, DICommon.SplitString(selectedNids, ",").Length, SourceDBConnection, SourceDBQueries, true);
ProgressCounter += 1;
this.RaiseProgressBarIncrement(ProgressCounter);
}
catch (Exception ex) { ExceptionFacade.ThrowException(ex); }
finally
{
if (SourceDBConnection != null)
{
SourceDBConnection.Dispose();
}
if (SourceDBQueries != null)
{
SourceDBQueries.Dispose();
}
}
//}
this._UnmatchedTable = this.GetUnmatchedTable();
this._AvailableTable = this.GetAvailableTable();
// Close ProgressBar
this.RaiseProgressBarClose();
}
//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 void IntializeSubgroupVal(ref string[] sgRow, bool exactMatch)
{
string[] SubgroupNids = new string[0];
for (int Index = 0; Index < this.SelectedSubgroups.Count; Index++)
{
sgRow[Index] = "0";
SubgroupNids = DICommon.SplitString(this.SelectedSubgroups[Index].ToString(), ",");
this.SelectedNIds.Add(SubgroupNids[0]);
}
this.GetCommaSepratedSubgroupNids(exactMatch);
}
public override bool DoConversion(bool forOnlineDB)
{
bool RetVal = false;
int TotalSteps = 10;
DBVersionBuilder VersionBuilder;
// Do the conversion only if database has different Schema
try
{
if (DICommon.IsDI7Database(this.DBConnection.ConnectionStringParameters.DbName))
{
this._DBQueries = new DIQueries(this._DBQueries.DataPrefix, this._DBQueries.LanguageCode);
if (this._ConvertDatabase)
{
this.RaiseProcessStartedEvent(TotalSteps);
this.RaiseProcessInfoEvent(1);
this.SetAllLanguageCodes();
this.RaiseProcessInfoEvent(2);
if (this.UpdateDBSchema(forOnlineDB))
{
this.RaiseProcessInfoEvent(9);
// Insert version info into database after conversion
VersionBuilder = new DBVersionBuilder(this._DBConnection, this._DBQueries);
//Delete newer version from table keep 6.0.0.5 record and delete which greater than 6.0.0.5 as like 7 version
VersionBuilder.DeleteVersionsFromVersionNumberToEnd(Constants.Versions.DI6_0_0_5);
this.RaiseProcessInfoEvent(10);
RetVal = true;
}
}
}
else
{
RetVal = true;
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Updates denominator table
/// </summary>
/// <param name="denominatorColIndex"></param>
/// <param name="appliedColIndexes"></param>
public void UpdateDenominatorTable(int denominatorColIndex, string appliedColIndexes)
{
DataRow NewRow;
DataTable TempTable;
String TempAppliedColIndex = string.Empty;
// update denominator column info
if (this._DenominatorTable != null)
{
// delete already mapped row where denominator column is equal to denominator column index
foreach (DataRow Row in this._DenominatorTable.Select(DenominatorColumns.DenominatorColumn + " = '" + denominatorColIndex.ToString() + "'"))
{
Row.Delete();
}
// delete already mapped row where denominator column is equal to applied column indexes
foreach (string appliedColIndex in DICommon.SplitString(appliedColIndexes, ","))
{
if (TempAppliedColIndex.Length > 0)
{
TempAppliedColIndex += ",";
}
TempAppliedColIndex = "'" + appliedColIndex + "'";
}
foreach (DataRow Row1 in this._DenominatorTable.Select(DenominatorColumns.DenominatorColumn + " IN ( " + TempAppliedColIndex + ")"))
{
Row1.Delete();
}
// delete already mapped row where applied column is equal to denominator column
foreach (DataRow Row2 in this._DenominatorTable.Select(DenominatorColumns.AppliedColumn + " = '" + denominatorColIndex + "'"))
{
Row2.Delete();
}
// add denominator column index and applied columns indexes
foreach (string appliedColIndex in DICommon.SplitString(appliedColIndexes, ","))
{
NewRow = this._DenominatorTable.NewRow();
NewRow[DenominatorColumns.DenominatorColumn] = denominatorColIndex.ToString();
NewRow[DenominatorColumns.AppliedColumn] = appliedColIndex;
this._DenominatorTable.Rows.Add(NewRow);
}
this._DenominatorTable.AcceptChanges();
}
}
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);
}
private void GetSubgroupValsFromNIds(string[] sgRow, int startColIndex, int endRowIndex, int endColIndex, bool exactMatch)
{
string[] SubgroupNids = new string[0];
int RowIndex = 0;
if (this.SelectedSubgroups.Count > 1)
{
for (int SGTypeIndex = 0; SGTypeIndex < SelectedSubgroups.Count; SGTypeIndex++)
{
//-- Increase the end column index so that it can reach to the end of the selected row.
SubgroupNids = DICommon.SplitString(this.SelectedSubgroups[endRowIndex].ToString(), ",");
if (SGTypeIndex + 1 < SelectedSubgroups.Count && endColIndex < SubgroupNids.Length - 1)
{
endColIndex += 1;
sgRow[endRowIndex] = endColIndex.ToString();
this.SelectedNIds[endRowIndex] = SubgroupNids[endColIndex];
this.GetCommaSepratedSubgroupNids(exactMatch);
}
else if (endColIndex == SubgroupNids.Length - 1)
{
RowIndex = this.GetEndColumnIndex(ref sgRow, exactMatch);
if (RowIndex == -1)
{
break;
}
else
{
endColIndex = 0;
}
}
this.GetSubgroupValsFromNIds(sgRow, startColIndex, endRowIndex, endColIndex, exactMatch);
}
}
else
{
if (this.SelectedSubgroups.Count > 0)
{
SubgroupNids = DICommon.SplitString(this.SelectedSubgroups[0].ToString(), ",");
for (int Index = 1; Index < SubgroupNids.Length; Index++)
{
this.SelectedNIds.Clear();
this.SelectedNIds.Add(SubgroupNids[Index]);
this.GetCommaSepratedSubgroupNids(exactMatch);
}
}
}
}
private void AddColumnsIntoIndicatorClassificationsTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// Add IC_short_name Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.ICShortName, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertICShortNameColumn(tablesName, forOnlineDB, serverType));
}
// Add publisher Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.Publisher, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertPublisherColumn(tablesName, forOnlineDB, serverType));
}
// Add title Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.Title, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertTitleColumn(tablesName, forOnlineDB, serverType));
}
// Add DIYear Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.DIYear, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertDIYearColumn(tablesName, forOnlineDB, serverType));
}
// Add SourceLink1 Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.SourceLink1, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertSourceLink1Column(tablesName, forOnlineDB, serverType));
}
// Add SourceLink2 Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.SourceLink2, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertSourceLink2Column(tablesName, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Insert Indicator record into database
/// </summary>
/// <param name="indicatorInfo">object of IndicatorInfo</param>
/// <returns>Ture/False. Return true after successful insertion otherwise false</returns>
private bool InsertIntoDatabase(IndicatorInfo indicatorInfo)
{
bool RetVal = false;
string IndicatorName = indicatorInfo.Name;
string IndicatorGId = Guid.NewGuid().ToString();
string LanguageCode = string.Empty;
string DefaultLanguageCode = string.Empty;
string IndicatorForDatabase = string.Empty;
try
{
DefaultLanguageCode = this.DBQueries.LanguageCode;
//replace GID only if given gid is not empty or null.
if (!string.IsNullOrEmpty(indicatorInfo.GID))
{
IndicatorGId = indicatorInfo.GID;
}
foreach (DataRow languageRow in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = languageRow[Language.LanguageCode].ToString();
if (LanguageCode == DefaultLanguageCode.Replace("_", String.Empty))
{
IndicatorForDatabase = IndicatorName;
}
else
{
IndicatorForDatabase = Constants.PrefixForNewValue + IndicatorName;
}
//--
indicatorInfo.Info = DICommon.CheckNConvertMetadataXml(indicatorInfo.Info);
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertIndicator(this.DBQueries.DataPrefix, "_" + LanguageCode, IndicatorForDatabase, IndicatorGId, indicatorInfo.Info, indicatorInfo.Global, indicatorInfo.HighIsGood, this.DBConnection.ConnectionStringParameters.ServerType));
}
RetVal = true;
}
catch (Exception)
{
RetVal = false;
}
return(RetVal);
}
private void RenameIndicatorClassificationIUSTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (DICommon.ISColumnExistInTable(this.DBConnection, " top 1 * ", this._DBQueries.DataPrefix + "ic_ius"))
{
//-- Carate New IC_IUS table from existing table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.CreateOldICIUSTableFromExisting(this._DBQueries.DataPrefix));
//-- Delete new table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Delete.DeleteNewICIUSTable(this._DBQueries.DataPrefix));
}
}
catch (Exception ex)
{
throw ex;
}
}
private void ConvertIndicatorMetadata()
{
DataTable IndicatorTable = null;
string IndicatorMetadata = string.Empty;
IndicatorTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Indicators.GetIndicator(FilterFieldType.Search, Indicator.IndicatorInfo + " IS NOT NULL AND " + Indicator.IndicatorInfo + " <> '' ", FieldSelection.Heavy));
foreach (DataRow Row in IndicatorTable.Rows)
{
IndicatorMetadata = Convert.ToString(Row[Indicator.IndicatorInfo]);
if (!string.IsNullOrEmpty(IndicatorMetadata))
{
IndicatorMetadata = DICommon.CheckNConvertMetadataXml(IndicatorMetadata);
// Update IndicatorInfo(Metadata)
this.DBConnection.ExecuteNonQuery(DALQueries.Indicator.Update.UpdateIndicatorInfo(this.DBQueries.DataPrefix, this.DBQueries.LanguageCode, DICommon.RemoveQuotes(IndicatorMetadata), FilterFieldType.NId, Convert.ToString(Row[Indicator.IndicatorNId])));
}
}
}
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 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 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 UpdateMetadataInfoByCategoryType(MetadataElementType categoryType, MetadataCategoryBuilder metadataCategoryBuilder, DIQueries queries)
{
List <string> TargetNids;
DataTable SrcCategoryTable = null;
StringBuilder Metadata_Category_Info = new StringBuilder();
SrcCategoryTable = this._DBConnection.ExecuteDataTable(queries.MetadataReport.GetAllMetadataReportsByCategoryType(categoryType));
TargetNids = DICommon.GetCommaSeperatedListOfGivenColumn(SrcCategoryTable, MetadataReport.TargetNid, false, string.Empty);
//for every target like for indicator one there is many metadata category may be definition, Classification, Method of Computation
foreach (string targetNid in TargetNids)
{
Metadata_Category_Info.Remove(0, Metadata_Category_Info.Length);
//Step 1 Create metadata Info in xml format
this.CreateMetadataXML(SrcCategoryTable.Select(MetadataReport.TargetNid + "='" + targetNid + "'"), ref Metadata_Category_Info, metadataCategoryBuilder, DIQueries.MetadataElementTypeText[categoryType].Trim("'".ToCharArray()));
//Step 2 Update metadata Info in xml format
this.UpdateMetadataInfo(queries, targetNid, categoryType, Metadata_Category_Info.ToString());
}
}
/// <summary>
/// Get the IUSNIds on the basis of IUNId
/// </summary>
/// <param name="iuNIds"></param>
/// <returns></returns>
public override List <string> GetIUSNIds(string iuNIds, bool checkUserSelection, bool selectSingleTon)
{
List <string> RetVal = new List <string>();
try
{
IDataReader IUSReader;
string[] IUNId = new string[0];
string UserSelectionIUSNIds = this._UserPrefences.UserSelection.IndicatorNIds;
bool IUSFound = false;
UserSelectionIUSNIds = UserSelectionIUSNIds.Insert(0, ",");
UserSelectionIUSNIds += ",";
IUNId = DICommon.SplitString(iuNIds, ",");
IUSReader = this._DBConnection.ExecuteReader(this.SqlQueries.IUS.GetIUSNIdByI_U_S(IUNId[0], IUNId[1], string.Empty));
while (IUSReader.Read())
{
if (checkUserSelection && UserSelectionIUSNIds.Contains("," + IUSReader[Indicator_Unit_Subgroup.IUSNId].ToString() + ","))
{
IUSFound = true;
break;
}
RetVal.Add(IUSReader[Indicator_Unit_Subgroup.IUSNId].ToString());
}
IUSReader.Close();
if (IUSFound)
{
RetVal.Clear();
}
}
catch (Exception)
{
}
return(RetVal);
}
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);
}
}
}
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);
}
}
/// <summary>
/// Get the row against which next subgroup nid is used to generate the subgroupVal.
/// </summary>
/// <param name="sgRow"></param>
/// <param name="endRowIndex"></param>
/// <param name="endColIndex"></param>
/// <returns></returns>
private int GetEndColumnIndex(ref string[] sgRow, bool exactMatch)
{
int RetVal = -1;
try
{
string[] SubgroupNids = new string[0];
for (int Index = this.SelectedSubgroups.Count - 2; Index >= 0; Index--)
{
SubgroupNids = DICommon.SplitString(this.SelectedSubgroups[Index].ToString(), ",");
//-- Check if the dimension contains unused subgroupNIds
if (Convert.ToInt32(sgRow[Index]) < SubgroupNids.Length - 1)
{
sgRow[Index] = Convert.ToString(Convert.ToInt32(sgRow[Index]) + 1);
this.SelectedNIds[Index] = SubgroupNids[Convert.ToInt32(sgRow[Index])];
RetVal = this.SelectedSubgroups.Count - 1;
for (int NIndex = Index + 1; NIndex < this.SelectedSubgroups.Count; NIndex++)
{
sgRow[NIndex] = "0";
SubgroupNids = DICommon.SplitString(this.SelectedSubgroups[NIndex].ToString(), ",");
this.SelectedNIds[NIndex] = SubgroupNids[0];
this.GetCommaSepratedSubgroupNids(exactMatch);
}
break;
}
}
}
catch (Exception)
{
}
return(RetVal);
}
private void UpdateCategoryTable()
{
MetadataCategoryBuilder CategoryBuilder;
DIQueries TempDBQueries = null;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
string CategoryNid = string.Empty;
string CategoryGID = string.Empty;
string SourceCategoryTable = string.Empty;
DataTable CategoryTable;
try
{
// step1: create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// get language for all available languages
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempDBQueries = new DIQueries(DataPrefix, LanguageCode);
if (string.IsNullOrEmpty(SourceCategoryTable))
{
// update GID in only one language table and for other language tables, use this language table
CategoryBuilder = new MetadataCategoryBuilder(this._DBConnection, TempDBQueries);
CategoryTable = CategoryBuilder.GetAllRecordsFromMetadataCategory();
foreach (DataRow Row in CategoryTable.Rows)
{
//update metdata category table (set parent_nid to -1 and update gids)
//CategoryGID = Convert.ToString(Row[Metadata_Category.CategoryName]).ToUpper().Replace(" ", "_");
//CategoryGID = MetaDataBuilder.GetNewMetaDataCategoryGID();
CategoryNid = Convert.ToString(Row[Metadata_Category.CategoryNId]);
CategoryGID = DICommon.GetValidGIdForSDMXRule(Convert.ToString(Row[Metadata_Category.CategoryName]).ToUpper()) + "_" + CategoryNid;
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Update.UpdateMetadataCategory(TempDBQueries.TablesName.MetadataCategory, Convert.ToInt32(CategoryNid), CategoryGID, "-1", false, false));
}
SourceCategoryTable = TempDBQueries.TablesName.MetadataCategory;
}
else
{
// use first language table to update other language table
this.DBConnection.ExecuteNonQuery("UPDATE " + SourceCategoryTable + " AS src INNER JOIN " + TempDBQueries.TablesName.MetadataCategory + " AS trg ON src." + Metadata_Category.CategoryNId + " = trg." + Metadata_Category.CategoryNId + " SET trg." + Metadata_Category.CategoryGId + "=src." + Metadata_Category.CategoryGId + " and trg." + Metadata_Category.ParentCategoryNId + "=src." + Metadata_Category.ParentCategoryNId + ";");
}
}
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
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());
}
}