/// <summary>
/// Create and insert into TmpDI7SearchIndicators table
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="tableNames"></param>
/// <param name="searchIndicators"></param>
/// <param name="isSearchForQS"></param>
public static void CreateTmpIndSearchTbl(DIConnection dbConnection, DITables tableNames, string searchIndicators, bool isSearchForQS)
{
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
string SearchLanguage = string.Empty;
try
{
CacheUtility.CreateSearchIndicatorsTable(dbConnection);
if (!string.IsNullOrEmpty(searchIndicators))
{
CacheUtility.GetSplittedList(dbConnection, searchIndicators, ",", true);
SBQry.Remove(0, SBQry.Length);
SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.SearchIndicators.TableName);
SBQry.Append(" (" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + ")");
SBQry.Append(" SELECT Ind."+ Indicator.IndicatorNId +" As IndicatorNId FROM "+ tableNames.Indicator +" As Ind");
SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SplittedList.TableName + " L");
SBQry.Append(" ON Ind." + Indicator.IndicatorNId + " = L." + QDSConstants.QDSTables.SplittedList.Columns.Value);
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Delete Notes Classificaton by Classification Nid
/// </summary>
/// <param name="classificationNids">Comma separated NIDs</param>
/// <returns >Count Of Records Updated</returns>
public int DeleteNotesClassification(string classificationNids)
{
int RetVal = 0;
string LanguageCode = string.Empty;
DITables TableNames;
string SqlQuery = string.Empty;
string AssociatedNotesNids=string.Empty;
NotesBuilder NoteBuilder=new NotesBuilder(this.DBConnection,this.DBQueries);
try
{
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = Convert.ToString(Row[Language.LanguageCode]);
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + LanguageCode);
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Notes.Delete.DeleteFrmNotesClassification(TableNames.NotesClassification, classificationNids);
RetVal = this.DBConnection.ExecuteNonQuery(SqlQuery);
AssociatedNotesNids = DIConnection.GetDelimitedValuesFromDataTable(NoteBuilder.GetNotesByNotesNid(string.Empty, string.Empty, string.Empty, classificationNids, CheckedStatus.All,FieldSelection.Light), Notes.NotesNId);
NoteBuilder.DeleteComments(AssociatedNotesNids);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
return RetVal;
}
/// <summary>
/// Delete all metatdata associated with all layer from all language tables
/// </summary>
public void ClearAreaMapMetadata()
{
DITables TableNames;
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Area.Delete.ClearAreaMapMetadata(TableNames.AreaMapMetadata));
}
}
private void CreateAssistantTblsForAllLngs(bool forOnlineDB)
{
DITables TableNames;
if (this.IsAssistantTblExists(forOnlineDB) == false)
{
try
{
//-- Get all Languages from database
foreach (DataRow Row in this._DBConnection.DILanguages(this._DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this._DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
//-- eBook table
try
{
// drop table if already exists
this._DBConnection.DropTable(TableNames.AssistanteBook);
}
catch (Exception)
{ }
this._DBConnection.ExecuteNonQuery(DALQueries.Assistant.Insert.CreateAssistantEBookTbl(TableNames.AssistanteBook, forOnlineDB));
//-- Assistant table
try
{
// drop table if already exists
this._DBConnection.DropTable(TableNames.Assistant);
}
catch (Exception)
{}
this._DBConnection.ExecuteNonQuery(DALQueries.Assistant.Insert.CreateAssistantTbl(TableNames.Assistant, forOnlineDB));
//-- topic table
try
{
// drop table if already exists
this._DBConnection.DropTable(TableNames.AssistantTopic);
}
catch (Exception)
{}
this._DBConnection.ExecuteNonQuery(DALQueries.Assistant.Insert.CreateAssistantTopicTbl(TableNames.AssistantTopic, forOnlineDB));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
}
public void DeleteMetadataReports(MetadataElementType categoryType)
{
DITables TableNames;
try
{
// Step1: Delete records from metadata Report table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
// Get table name
TableNames = new DITables(this.DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
// delete records
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataReport.Delete.DeleteMetadataReportByCategory(TableNames, categoryType));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Delete Comments by Notes_Nids from each language
/// </summary>
/// <param name="notesNids">Comma Seperated Nids</param>
public int DeleteComments(string notesNids)
{
int RetVal = 0;
string LanguageCode = string.Empty;
DITables TableNames;
string SqlQuery = string.Empty;
try
{
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode =Convert.ToString( Row[Language.LanguageCode]);
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + LanguageCode);
RetVal=this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Notes.Delete.DeleteFrmNotes(TableNames.Notes, notesNids));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
return RetVal;
}
/// <summary>
/// Deletes indicators and associated records from IUS and IC_IUS table
/// </summary>
/// <param name="indicatorNids"></param>
public void DeleteIndicator(string indicatorNids)
{
DITables TableNames;
IUSBuilder IUSBuilder;
MetaDataBuilder MetadataBuilderObject;
string AssocicatedIUSNIds = string.Empty;
try
{
IUSBuilder = new IUSBuilder(this.DBConnection, this.DBQueries);
// Step 1: Delete records from Indicator table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Delete.DeleteIndicator(TableNames.Indicator, indicatorNids));
}
// Step 2: Delete records from IUS table
// Step2(a): Get all associated IUSNIds
AssocicatedIUSNIds = IUSBuilder.GetAllAssociatedIUSNids(indicatorNids, string.Empty, string.Empty);
// Step2(b): Delete all associated IUSNIds
IUSBuilder.DeleteIUS(AssocicatedIUSNIds);
// delete metadata
MetadataBuilderObject = new MetaDataBuilder(this.DBConnection, this.DBQueries);
MetadataBuilderObject.DeleteMetadata(indicatorNids, MetadataElementType.Indicator);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void CreateDBMetaTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
DBMetadataTableBuilder DBMetadataTblBuilder;
DIQueries TempQueries;
try
{
// create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// create table for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
DBMetadataTblBuilder = new DBMetadataTableBuilder(this._DBConnection, TempQueries);
if (DBMetadataTblBuilder.IsDBMetadataTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.CreateTable(TableNames.DBMetadata,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
}
}
// insert blank records with counts
// reset DBMetadata builder with main DIQuerie's object
DBMetadataTblBuilder = new DBMetadataTableBuilder(this._DBConnection, this._DBQueries);
DBMetadataTblBuilder.InsertRecord(string.Empty, string.Empty, string.Empty, string.Empty,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
DBMetadataTblBuilder.GetNUpdateCounts();
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Deletes subgroup and associated records from SubgroupVal, SubgroupValSubgroup, Indicator_Unit_Subgroup and
/// </summary>
/// <param name="NIds"></param>
public void DeleteSubgroup(string NIds)
{
string SqlQuery = string.Empty;
DITables TablesName;
DI6SubgroupValBuilder SGValBuilder;
string AssociatedSubgroupValNIds = string.Empty;
try
{
// Step 1: Delete subgroup
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TablesName = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Subgroup.Delete.DeleteSubgroups(TablesName.Subgroup, NIds);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
// Step 2: Delete associated SubgroupVal which will automatically delete associated records from SubgroupValSubgroup, IUS and IC_IUS
SGValBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
AssociatedSubgroupValNIds = SGValBuilder.GetAllAssociatedSubgroupValNIds(NIds);
if (!string.IsNullOrEmpty(AssociatedSubgroupValNIds))
{
SGValBuilder.DeleteSubgroupVals(AssociatedSubgroupValNIds);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void ImportEBook(ref DIConnection Connection, ref DIQueries queries, string languageCode, DITables sourceTableNames, DITables targetTableNames)
{
string SqlString = string.Empty;
string TablePrefix = this._TargetDBConnection.DIDataSetDefault();
DataTable SourceTopicTable = null;
string TargetConnectionString = this._TargetDBConnection.GetConnection().ConnectionString;
string SourceConnectionString = Connection.GetConnection().ConnectionString;
string SourceDBName = Connection.ConnectionStringParameters.DbName;
string TargetDBName = this._TargetDBConnection.ConnectionStringParameters.DbName;
OleDbCommand InsertCommand;
OleDbDataAdapter Adapter;
OleDbCommandBuilder CmdBuilder;
DataSet EbookDataset;
DataRow Row;
try
{
this._TargetDBConnection.ExecuteNonQuery(AssistantQueries.DeleteFrmEBook(targetTableNames.AssistanteBook));
// get record from source database
SourceTopicTable = Connection.ExecuteDataTable(" Select * from " + sourceTableNames.AssistanteBook);
if (SourceTopicTable.Rows.Count > 0)
{
//dispose target and source connection
this._TargetDBConnection.Dispose();
Connection.Dispose();
InsertCommand = new OleDbCommand();
InsertCommand.Connection = new OleDbConnection(TargetConnectionString);
Adapter = new OleDbDataAdapter("Select * from " + sourceTableNames.AssistanteBook, TargetConnectionString);
CmdBuilder = new OleDbCommandBuilder(Adapter);
EbookDataset = new DataSet(sourceTableNames.AssistanteBook);
Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
Adapter.Fill(EbookDataset, targetTableNames.AssistanteBook); //Fill data adapter
Row = EbookDataset.Tables[0].NewRow();
try
{
Row[Assistant_eBook.EBook] = SourceTopicTable.Rows[0][Assistant_eBook.EBook]; //ShpBuffer
EbookDataset.Tables[0].Rows.Add(Row);
Adapter.Update(EbookDataset, targetTableNames.AssistanteBook); // Save changes to the database
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
if (CmdBuilder != null)
{
CmdBuilder.Dispose();
CmdBuilder = null;
}
if (InsertCommand != null)
{
InsertCommand.Dispose();
InsertCommand = null;
}
if (Adapter != null)
{
Adapter.Dispose();
Adapter = null;
}
//reconnect the source and target database
this._TargetDBConnection = new DIConnection(new DIConnectionDetails(DIServerType.MsAccess,
string.Empty, string.Empty, TargetDBName, string.Empty, Common.Constants.DBPassword));
Connection = new DIConnection(SourceConnectionString, DIServerType.MsAccess);
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (SourceTopicTable != null)
{
SourceTopicTable.Dispose();
}
}
}
private void UpdateTextualAndNumericData(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
string SqlQuery = string.Empty;
const string DataValueTempColumn = "Data_Value_Temp";
try
{
//-- Create Temp Column
SqlQuery = "ALTER TABLE " + this.DBQueries.TablesName.Data + " ADD COLUMN " + DataValueTempColumn + " Double";
this.DBConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + Data.IsTextualData + "=1";
this.DBConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + Data.IsTextualData + "=0 WHERE ISNUMERIC(" + Data.DataValue + ")";
this.DBConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + Data.TextualDataValue + "=" + Data.DataValue + " WHERE " + Data.IsTextualData + "<>0";
this.DBConnection.ExecuteNonQuery(SqlQuery);
//-- Update Numeric data
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + DataValueTempColumn + " =" + Data.DataValue + " WHERE " + Data.IsTextualData + " = 0";
this.DBConnection.ExecuteNonQuery(SqlQuery);
//-- Drop DataValue col
SqlQuery = "ALTER TABLE " + this.DBQueries.TablesName.Data + " DROP COLUMN " + Data.DataValue;
this.DBConnection.ExecuteNonQuery(SqlQuery);
//-- Drop DataValue col
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Update.AlterDataValueColumnDataTypeToDouble(tablesName, forOnlineDB, serverType));
//-- Update Numeric column into
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + Data.DataValue + " =" + DataValueTempColumn;
this.DBConnection.ExecuteNonQuery(SqlQuery);
//-- DROp Temp Column
SqlQuery = "ALTER TABLE " + this.DBQueries.TablesName.Data + " DROP COLUMN " + DataValueTempColumn;
this.DBConnection.ExecuteNonQuery(SqlQuery);
//#region "//-- Exception handled:File sharing lock count exceeded. Increase MaxLocksPerFile registry entry. --"
//DIConnectionDetails Obj = this._DBConnection.ConnectionStringParameters;
//if (this._DBConnection != null)
//{
// this._DBConnection.Dispose();
//}
//this._DBConnection = new DIConnection(Obj);
//#endregion
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="languageCode"></param>
/// <param name="dataBaseFileWPath">Target Database File Name With Path</param>
public ImportTableQueries(string dataPrefix, string languageCode, string dataBaseFileWPath,string sourceFileNameWPath)
{
this.Tables = new DITables(dataPrefix, languageCode);
TargetDataBaseFileWPath = dataBaseFileWPath;
this._SourceFileNameWPath = sourceFileNameWPath;
}
/// <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>
/// Create cache results for levels
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="languageCode"></param>
private void CreateCacheResultsForLevel(DIConnection dbConnection, string languageCode, DITables tableNames)
{
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
int AreaLevel = 4;
int ProgressCount = 33;
try
{
#region "-- Quick Search (level) --"
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- 1 Add TempColumn column into Area table
StrQry = "Alter table " + tableNames.Area + " add column " + QDSConstants.QDSTables.Area.Columns.TempColumn + " varchar";
dbConnection.ExecuteNonQuery(StrQry);
//-- 2 Get area levels
StrQry = "Select count(*) from " + tableNames.AreaLevel;
AreaLevel = (int)dbConnection.ExecuteScalarSqlQuery(StrQry);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
if (AreaLevel < 4)
{
AreaLevel = 4;
}
//-- 3 Update query and execute it
for (int i = 2; i <= AreaLevel; i++)
{
StrQry = GetUpdateAreaTempColumnQry(i, languageCode, tableNames);
dbConnection.ExecuteNonQuery(StrQry);
}
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- 4. Update into table DI_Search_Results of AreaNIds column with TempComumn column from ut_Area_xx table
SBQry.Remove(0, SBQry.Length);
SBQry.Append("Update " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode + " as R");
SBQry.Append(" Inner Join "+ tableNames.Area + " as A");
SBQry.Append(" On R." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + " = A." + Area.AreaNId);
SBQry.Append(" And R."+ QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + " = A." + Area.AreaParentNId);
SBQry.Append(" Set R." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + " = A.TempColumn");
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- 5 Drop TempColumn column from Area table
StrQry = "Alter table " + tableNames.Area + " Drop Column " + QDSConstants.QDSTables.Area.Columns.TempColumn;
dbConnection.ExecuteNonQuery(StrQry);
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Create cache result for DISearchResults
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="languageCode"></param>
private void CreateCacheResultsForDISearchResults(DIConnection dbConnection, string languageCode, DITables tableNames)
{
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
int ProgressCount = 24;
try
{
#region "-- Create cache table (DI_Search_Results) --"
//-- Create DI_Search_Result table for using in cache generation
CacheUtility.CreateDISearchResultsTable(dbConnection, languageCode);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- Delete all record
CacheUtility.DeleteTableRecords(dbConnection, QDSConstants.QDSTables.TempMRDRecords.TableName);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- Get MRD records and create tempMRDRecords table
SBQry.Remove(0, SBQry.Length);
SBQry.Append("Insert Into " + QDSConstants.QDSTables.TempMRDRecords.TableName);
SBQry.Append(" (" + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + ", " + QDSConstants.QDSTables.TempMRDRecords.Columns.DVCount + " )");
SBQry.Append(" SELECT " + Data.IUSNId + ", D." + Data.IndicatorNId + ", D." + Data.UnitNId + ", D." + Data.SubgroupValNId + ", " + Data.AreaNId + ", MAX(D." + QDSConstants.QDSTables.Data.Columns.TimePeriod + ") AS " + QDSConstants.QDSTables.Data.Columns.TimePeriod + ",");
SBQry.Append(" Count(*) AS " + QDSConstants.QDSTables.Data.Columns.DVCount);
SBQry.Append(" FROM " + tableNames.Data + " AS D");
SBQry.Append(" WHERE D."+ QDSConstants.QDSTables.Data.Columns.ISDefaultSG +" = -1");
SBQry.Append(" GROUP BY D."+ Data.IUSNId +", D."+ Data.AreaNId +", D."+ Data.IndicatorNId +", D."+ Data.UnitNId +", D." + Data.SubgroupValNId);
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- Update DataValue into TempMRDRecords table using data table
SBQry.Remove(0, SBQry.Length);
SBQry.Append("UPDATE "+ tableNames.Data +" AS d1 INNER JOIN " + QDSConstants.QDSTables.TempMRDRecords.TableName + " AS t1");
SBQry.Append(" ON (d1." + QDSConstants.QDSTables.Data.Columns.TimePeriod + "=t1." + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + ")");
SBQry.Append(" AND (d1." + Data.IUSNId + "=t1." + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + ") AND (D1." + Data.AreaNId + " = t1." + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + ")");
SBQry.Append(" SET t1." + QDSConstants.QDSTables.TempMRDRecords.Columns.DV + " = d1." + Data.DataValue);
SBQry.Append(" WHERE d1.isdefaultSG = -1");
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- Insert records into DISearchResults table
SBQry.Remove(0, SBQry.Length);
SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode);
SBQry.Append(" (" + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRD + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Unit + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Area + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + ")");
SBQry.Append(" SELECT '" + languageCode + "', D." + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + ", D." + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + ", D." + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + ", -1 AS Expr1, D." + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + ", t." + Timeperiods.TimePeriodNId + ", D." + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + ",");
SBQry.Append(" d." + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + " & '[@@@@]' & SG."+ SubgroupVals.SubgroupVal + " AS Expr2, I."+ Indicator.IndicatorName +", U."+ Unit.UnitName +", A."+ Area.AreaName +",");
SBQry.Append(" A.area_parent_nid, D." + QDSConstants.QDSTables.TempMRDRecords.Columns.DV + ", D." + QDSConstants.QDSTables.TempMRDRecords.Columns.DVCount);
SBQry.Append(" FROM " + tableNames.TimePeriod + " AS t");
SBQry.Append(" INNER JOIN (((" + tableNames.Unit + " AS U INNER JOIN (" + tableNames.Area + " AS A");
SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.TempMRDRecords.TableName + " AS D ON A."+ Area.AreaNId +" = D." + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + ")");
SBQry.Append(" ON U."+ Unit.UnitNId +" = D." + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + ") INNER JOIN " + tableNames.SubgroupVals + " AS SG ON D." + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + " = SG." + SubgroupVals.SubgroupValNId + ")");
SBQry.Append(" INNER JOIN " + tableNames.Indicator + " AS I ON D." + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + " = I."+ Indicator.IndicatorNId +")");
SBQry.Append(" ON t."+ Timeperiods.TimePeriod +" = D." + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod);
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Create cache result of blocks
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="languageCode"></param>
private void CreateCacheResultsForBlock(DIConnection dbConnection, string languageCode, DITables tableNames)
{
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
int ProgressCount = 29;
try
{
#region "-- Block --"
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- Area block - get area where block exists
SBQry.Remove(0, SBQry.Length);
SBQry.Append("SELECT " + Area.AreaNId + ", " + Area.AreaBlock);
SBQry.Append(" FROM " + tableNames.Area );
SBQry.Append(" WHERE " + Area.AreaBlock + " is not null and " + Area.AreaBlock + "<>''");
StrQry = SBQry.ToString();
DataTable DtAreaBlocks = dbConnection.ExecuteDataTable(StrQry);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- Create BlockAreaResults table
CacheUtility.CreateBlockAreaResultsTable(dbConnection, languageCode);
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
if (DtAreaBlocks.Rows.Count > 0)
{
foreach (DataRow Row in DtAreaBlocks.Rows)
{
string AreaNId = Convert.ToString(Row[Area.AreaNId]);
string AreaBlock = Convert.ToString(Row[Area.AreaBlock]);
//-- Area block - insert record into block area results
SBQry.Remove(0, SBQry.Length);
SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.BlockAreaResults.TableName);
SBQry.Append(" ( " + QDSConstants.QDSTables.BlockAreaResults.Columns.SearchLanguage + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.UnitNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.IndicatorName + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.Unit + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.Area + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.DefaultSG + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.MRDTP + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.MRD + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.SGCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.SourceCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.TPCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.DVCount + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaNIds + ", ");
SBQry.Append(QDSConstants.QDSTables.BlockAreaResults.Columns.SGNIds + " , " + QDSConstants.QDSTables.BlockAreaResults.Columns.SourceNIds + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.TPNIds + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.DVNIds + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.DVSeries + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.Dimensions + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.BlockAreaParentNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.IUSNId + ", " + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaParentNId + " )");
SBQry.Append(" SELECT D." + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.Unit + ",");
SBQry.Append(" D." + QDSConstants.QDSTables.DISearchResult.Columns.Area + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.MRD + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.AreaCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.SGCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.SourceCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.TPCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + ",");
SBQry.Append(" D." + QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.DVNIds + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.Dimensions + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.BlockAreaParentNId + ", D." + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + ", " + AreaNId);
SBQry.Append(" FROM " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode + " AS D");
SBQry.Append(" WHERE d." + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + " in (" + AreaBlock + ")");
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
}
}
// increment progress bar value
this.RaiseProgressChangedEvent(ProgressCount++, string.Empty, string.Empty, false);
//-- Area block - Insert into di search results table
SBQry.Remove(0, SBQry.Length);
SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode);
SBQry.Append(" ( " + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Unit + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Area + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + ", " + QDSConstants.QDSTables.DISearchResult.Columns.MRD + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SGCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.SourceCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.TPCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + ", ");
SBQry.Append(QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + " , " + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVNIds + ", " + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + ", " + QDSConstants.QDSTables.DISearchResult.Columns.Dimensions + ", " + QDSConstants.QDSTables.DISearchResult.Columns.BlockAreaParentNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + ", " + QDSConstants.QDSTables.DISearchResult.Columns.IsBlockAreaRecord + " )");
SBQry.Append(" SELECT D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SearchLanguage + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.IndicatorNId + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.UnitNId + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaNId + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.IsAreaNumeric + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.IndicatorName + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.Unit + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.Area + ",");
SBQry.Append(" D." + QDSConstants.QDSTables.BlockAreaResults.Columns.DefaultSG + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.MRDTP + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.MRD + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SGCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SourceCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.TPCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.DVCount + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaNIds + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SGNIds + ",");
SBQry.Append(" D." + QDSConstants.QDSTables.BlockAreaResults.Columns.SourceNIds + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.TPNIds + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.DVNIds + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.DVSeries + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.Dimensions + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.BlockAreaParentNId + ", D." + QDSConstants.QDSTables.BlockAreaResults.Columns.IUSNId + ", d." + QDSConstants.QDSTables.BlockAreaResults.Columns.AreaParentNId + ", -1");
SBQry.Append(" FROM " + QDSConstants.QDSTables.BlockAreaResults.TableName + " AS D");
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Create chache results for a language
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="languageCode"></param>
private void CreateCacheResults(DIConnection dbConnection, string languageCode)
{
DITables TableNames;
try
{
//-- Get all tables by dataset and language basis
TableNames = new DITables(dbConnection.DIDataSetDefault(), languageCode);
//-- Create cache table (DI_Search_Results)
this.CreateCacheResultsForDISearchResults(dbConnection, languageCode, TableNames);
//-- Block
this.CreateCacheResultsForBlock(dbConnection, languageCode, TableNames);
//-- Quick Search (level)
this.CreateCacheResultsForLevel(dbConnection, languageCode, TableNames);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Updates publisher, title & year values
/// </summary>
public void UpdatePublisherTitleYear()
{
DIServerType ServerType = this.DBConnection.ConnectionStringParameters.ServerType;
DITables TablesName;
DataTable Table;
string LanguageCode = string.Empty;
string PublisherName = string.Empty;
string ICName = string.Empty;
int Index = -1;
int ICNid = 0;
try
{
// 1.Do the following for the default langauge
// 1.1 update publisher
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdatePublisherColumn(ServerType, this.DBQueries.TablesName.IndicatorClassifications));
// 1.2 update year
//this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdateYearColumn(ServerType, this.DBQueries.TablesName.IndicatorClassifications));
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.IndicatorClassification.GetAllSourceColumnsWithoutPublishers());
foreach (DataRow Row in Table.Rows)
{
ICNid = Convert.ToInt32(Row[IndicatorClassifications.ICNId]);
PublisherName = Convert.ToString(Row[IndicatorClassifications.Publisher]) + "_";
ICName = Convert.ToString(Row[IndicatorClassifications.ICName]);
if (ICName.StartsWith(PublisherName))
{
ICName = ICName.Substring(PublisherName.Length);
}
Index = ICName.LastIndexOf("_");
if (Index >= 0)
{
ICName = ICName.Substring(Index + 1);
if (ICName.Length > 10)
{
ICName = string.Empty;
}
}
else
{
ICName = string.Empty;
}
// update diyear into database
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdateYearColumn(this.DBQueries.TablesName.IndicatorClassifications, ICNid, ICName));
}
// 1.3 update title
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdateTitleColumn(ServerType, this.DBQueries.TablesName.IndicatorClassifications));
// 2. update Publisher, title & year in other language tables
foreach (DataRow LanguageRow in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = Convert.ToString(LanguageRow[Language.LanguageCode]);
// update all Language tables except default langauge table
if (("_" + LanguageCode) != this.DBQueries.LanguageCode)
{
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdatePublisherTitleYearInOtherLanguages(this.DBQueries.TablesName.IndicatorClassifications, TablesName.IndicatorClassifications));
}
}
}
catch (Exception ex)
{
DevInfo.Lib.DI_LibBAL.ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Deletes records from Indicator_Classification table and associated records from IC_IUS table. Use this method to delete records against given nids. This methods will not delete the associate child of the given Nids
/// </summary>
/// <param name="nids"></param>
public void DeleteClassification(string nids)
{
DITables TableNames;
if (!string.IsNullOrEmpty(nids))
{
try
{
// Step1: Delete records from IndicatorClassification table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Delete.DeleteIC(TableNames.IndicatorClassifications, nids));
}
// Step2: Delete records from IC_IUS table
this.DeleteClassificationIUSRelation(nids, string.Empty);
// step3: update parent_nid in IC table where IC_Parent_NId in( given nids)
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdateIC(IndicatorClassifications.ICParent_NId + " IN(" + nids + ") ", this.DBQueries.DataPrefix, this.DBQueries.LanguageCode, string.Empty, string.Empty, null, -1, string.Empty, null, nids));
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
}
/// <summary>
/// Updates data exists values
/// </summary>
public void UpdateDataExistValues()
{
DIServerType ServerType = this.DBConnection.ConnectionStringParameters.ServerType;
DITables TablesName;
string LanguageCode = string.Empty;
try
{
// 1. set all indicators' data_exist value to false in default language table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Update.UpdateDataExistToFalse(ServerType, this.DBQueries.TablesName.Indicator));
// 2. set data_exist to true but where data exists
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Update.UpdateDataExistValues(ServerType, this.DBQueries.TablesName));
// 3. update data_exist in other language tables
foreach (DataRow LanguageRow in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = Convert.ToString(LanguageRow[Language.LanguageCode]);
// update all Language tables except default language table
if (("_" + LanguageCode) != this.DBQueries.LanguageCode)
{
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Update.UpdateDataExistValuesInOtherLanguage(this.DBQueries.TablesName.Indicator, TablesName.Indicator));
}
}
}
catch (Exception ex)
{
DevInfo.Lib.DI_LibBAL.ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Deletes recrods from Footnote table
/// </summary>
/// <param name="footnoteNids"></param>
public void DeleteFootnote(string footnoteNids)
{
DITables TableNames;
string SqlQuery = string.Empty;
try
{
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode]);
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Footnote.Delete.DeleteFootnote(TableNames.FootNote, footnoteNids);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
//update data table
if (!string.IsNullOrEmpty(footnoteNids))
{
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Data.Update.RemoveFootnoteNId(this.DBQueries.DataPrefix, footnoteNids);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Deletes units and associated records from IUS a IC_IUS table table
/// </summary>
/// <param name="nids"></param>
public void DeleteUnit(string nids)
{
DITables TableNames;
IUSBuilder IUSBuilder;
string AssocicatedIUSNIds = string.Empty;
try
{
IUSBuilder = new IUSBuilder(this.DBConnection, this.DBQueries);
// Step 1: Delete records from Unit table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Unit.Delete.DeleteUnits(TableNames.Unit, nids));
}
// Step 2: Delete records from IUS table
// Step2(a): Get all associated IUSNIds
AssocicatedIUSNIds = IUSBuilder.GetAllAssociatedIUSNids(string.Empty, nids, string.Empty);
// Step2(b): Delete all associated IUSNIds
IUSBuilder.DeleteIUS(AssocicatedIUSNIds);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Updates count information
/// </summary>
/// <param name="NID"></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 UpdateCounts(int NID,
string areaCount, string indicatorCount, string IUSCount, string timeperiodCount, string sourceCount, string dataCount)
{
string PublishedOn=System.DateTime.Now.ToString( "yyyy-MM-dd");
string SqlQuery = string.Empty;
DITables TablesName;
string DataPrefix = this.DBConnection.DIDataSetDefault();
bool UseUpdateQuery = true;
try
{
// check record exists in DB_Metadata table or not
if (Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(this.DBQueries.TablesName.DBMetadata, string.Empty))) == 0)
{
UseUpdateQuery = false;
}
foreach (DataRow Row in this.DBConnection.DILanguages(DataPrefix).Rows)
{
TablesName = new DITables(DataPrefix, "_" + Row[Language.LanguageCode].ToString());
if (UseUpdateQuery)
{
// update query
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Update.UpdateCounts(TablesName.DBMetadata, NID,
areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
}
else
{
//insert query
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.InsertRecord(TablesName.DBMetadata, string.Empty, string.Empty, PublishedOn, string.Empty, string.Empty, string.Empty,
areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
}
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void RenameIndicatorClassificationIUSTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(" * ", tablesName.IndicatorClassificationsIUS))
{
//-- Carate New IC_IUS table from existing table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.CreateNewICIUSTableFromExisting(this._DBQueries.DataPrefix));
//-- Delete old table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Delete.DeleteOldICIUSTable(this._DBQueries.DataPrefix));
}
}
catch (Exception ex)
{
}
}
/// <summary>
/// Create and alter tables schema in database
/// </summary>
/// <param name="dbConnection"></param>
private void CreateNAlterSchemas(DIConnection dbConnection)
{
string StrQry = string.Empty;
DITables TableNames;
string DataPrefix = string.Empty;
try
{
//-- Get default data prefix in database
DataPrefix = dbConnection.DIDataSetDefault();
//-- Get all table names
TableNames = new DITables(dbConnection.DIDataSetDefault(), dbConnection.DILanguageCodeDefault(dbConnection.DIDataSetDefault()));
//-- Add timeperiod column into data
StrQry = "Alter table "+ TableNames.Data +" add column " + QDSConstants.QDSTables.Data.Columns.TimePeriod + " varchar(100)";
dbConnection.ExecuteNonQuery(StrQry);
//-- Add IsDefaultSG column into data
StrQry = "Alter table "+ TableNames.Data +" add column " + QDSConstants.QDSTables.Data.Columns.ISDefaultSG + " bit";
dbConnection.ExecuteNonQuery(StrQry);
//-- Create TempMRDRecords table
CacheUtility.CreateTempMRDRecordsTable(dbConnection);
//-- Update timeperiod values into data table
StrQry = "UPDATE "+ TableNames.Data +" AS d INNER JOIN "+ TableNames.TimePeriod +" AS t ON d."+ Data.TimePeriodNId +" = t."+ Timeperiods.TimePeriodNId +" SET d."+ QDSConstants.QDSTables.Data.Columns.TimePeriod +" = t." + Timeperiods.TimePeriod;
dbConnection.ExecuteNonQuery(StrQry);
//-- Update default sg into data table
StrQry = "UPDATE "+ TableNames.Data +" AS D INNER JOIN "+ TableNames.IndicatorUnitSubgroup +" AS IUS ON D." + Data.IUSNId + " = IUS." + Indicator_Unit_Subgroup.IUSNId + " SET D."+ QDSConstants.QDSTables.Data.Columns.ISDefaultSG +" = IUS." + Indicator_Unit_Subgroup.IsDefaultSubgroup;
dbConnection.ExecuteNonQuery(StrQry);
}
catch (Exception ex)
{
throw ex;
}
}
private void UpdateDBSchema(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TablesName = this.DBQueries.TablesName;
DIServerType ServerType;
try
{
ServerType = this.DBConnection.ConnectionStringParameters.ServerType;
// step1: add columns in all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// add columns in all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// create tables name
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
// insert new columns into indicator_classification table
this.AddColumnsIntoIndicatorClassificationsTable(forOnlineDB, TablesName, ServerType);
// insert new columns into indicator table
this.AddColumnsIntoMetadataCategoryTable(forOnlineDB, TablesName, ServerType);
// insert new column "AreaShortName" into area table
this.AddColumnIntoAreaTable(forOnlineDB, TablesName, ServerType);
if (!this.ISColumnExist("*", TablesName.MetadataReport))
{
//-- Create Metedata Report table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataReport.Insert.CreateTable(TablesName.MetadataReport, forOnlineDB, ServerType));
}
}
this.RaiseProcessInfoEvent(2);
// add columns into StartDate,EndDate,Perodicity into Timeperiod table
this.AddColumnIntoTimeperiodTable(forOnlineDB, TablesName, ServerType);
this.RaiseProcessInfoEvent(3);
// insert new column IsDefaultSubgroup,AvlMinDataValue,AvlMaxDataValue,AvlMinTimePeriod,AvlMaxTimePeriod into IUS table
this.AddColumnsIntoIUSTable(forOnlineDB, TablesName, ServerType);
//-- Update Default Subgroup
this.UpdateDefaultSubgroupValue();
this.RaiseProcessInfoEvent(4);
// insert new column Textual_Data_Value,IsTextualData,IsMRD,IsPlannedValue,IUNId,ConfidenceIntervalUpper,
// ConfidenceIntervalLower into data table
this.AddColumnIntoDataTable(forOnlineDB, TablesName, ServerType);
this.RaiseProcessInfoEvent(5);
//update parent nid and category gid into metadata category table
this.UpdateCategoryTable();
// update metadata and xslt
this.ConvertMetadataXmlIntoDatabse();
this.RaiseProcessInfoEvent(6);
// Update Textual And Numeric Data
this.UpdateTextualAndNumericData(forOnlineDB, TablesName, ServerType);
this.RaiseProcessInfoEvent(7);
//-- Rename IC_IUS table
this.RenameIndicatorClassificationIUSTable(forOnlineDB, TablesName, ServerType);
this.RaiseProcessInfoEvent(8);
this.CreateSDMXUserTable(forOnlineDB);
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Insert Notes Classification by NotesClassificationInfo
/// </summary>
/// <param name="notesClassInfo">NotesClassificationInfo Object</param>
/// <returns >Return Inserted Notes Nid</returns>
public int InsertNotesClassification(NotesClassificationInfo notesClassInfo)
{
int RetVal = 0;
DITables TableNames;
string LanguageCode = string.Empty;
string DefaultLanguageCode = string.Empty;
string ClassName = string.Empty;
string SqlQuery = string.Empty;
try
{
DefaultLanguageCode = this.DBQueries.LanguageCode;
//-- Insert Classification for Each Language
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = Convert.ToString(Row[Language.LanguageCode]);
if (LanguageCode == DefaultLanguageCode.Replace("_", String.Empty))
{
ClassName = notesClassInfo.Classification_Name;
}
else
{
ClassName = Constants.PrefixForNewValue + notesClassInfo.Classification_Name;
}
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + LanguageCode);
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Notes.Insert.InsertNotesClassification(TableNames.NotesClassification, ClassName);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
RetVal = this.DBConnection.GetNewId();
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
/// <summary>
/// Get query for update AreaNIds column on level basis new quick search id
/// </summary>
/// <param name="level"></param>
/// <param name="languageCode"></param>
/// <returns></returns>
private string GetUpdateAreaTempColumnQry(int level, string languageCode, DITables tableNames)
{
string RetVal = string.Empty;
StringBuilder SBQry = new StringBuilder();
string ParentIndex = "";
string CurrentIndex = "";
try
{
SBQry.Append("UPDATE ");
for (int i = 2; i < level; i++)
{
SBQry.Append(" ( ");
}
SBQry.Append( tableNames.Area +" AS a1 ");
for (int i = 2; i<=level; i++)
{
CurrentIndex = i.ToString();
ParentIndex = Convert.ToString(i - 1);
SBQry.Append(" INNER JOIN " + tableNames.Area + " AS a" + CurrentIndex + " ON a" + ParentIndex + "."+ Area.AreaNId +" = a" + CurrentIndex + "." + Area.AreaParentNId);
if (i >= 2 && i < level)
{
SBQry.Append(" ) ");
}
}
SBQry.Append(" SET a"+ level.ToString() +"."+ QDSConstants.QDSTables.Area.Columns.TempColumn +" = 'QS_' & a1."+ Area.AreaID +" & '_L"+ level.ToString() + "'");
SBQry.Append(" WHERE a1."+ Area.AreaParentNId +"=-1");
RetVal = SBQry.ToString();
}
catch (Exception ex)
{
throw ex;
}
return RetVal;
}
internal bool ExportMDB(string destinationDBNameWPath, string tempFolderPath, bool createNewDatabase)
{
bool RetVal = false;
int ProgressCount = 0;
try
{
if (createNewDatabase)
{
this.ExportMDB(destinationDBNameWPath, tempFolderPath);
}
else
{
DIExport.RaiseExportProgressBarInitialize(100);
//- Establish Connection with Destination Database in temp folder
this.DestDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, destinationDBNameWPath, string.Empty, string.Empty);
this.DestDBQueries = new DIQueries(DestDBConnection.DIDataSetDefault(), DestDBConnection.DILanguageCodeDefault(DestDBConnection.DIDataSetDefault()));
this.DBTableNames = new DITables(DestDBConnection.DIDataSetDefault(), DestDBConnection.DILanguageCodeDefault(DestDBConnection.DIDataSetDefault()));
this.SourceTableNames = this.SourceDBQueries.TablesName;
// if database/tempalte already in DI6 format then convert it into latest format
DBConverterDecorator DBConverter = new DBConverterDecorator(this.DestDBConnection, this.DestDBQueries);
DBConverter.DoConversion(false);
//-- Get LanguageTable
this.LanguageTable = this.DestDBConnection.DILanguages(this.DestDBConnection.DIDataSetDefault());
DIExport.RaiseExportProgressBarIncrement(1);
//- Export Indicator, Unit, Subgroupval, IUS
this.ProcessIndicators();
DIExport.RaiseExportProgressBarIncrement(10);
// Export linked IC information
this.ProcessIndicatorClassifications();
DIExport.RaiseExportProgressBarIncrement(20);
//- Export Area
this.ProcessAreas();
DIExport.RaiseExportProgressBarIncrement(30);
//- Export TimePeriod
this.ProcessTimePeriods();
DIExport.RaiseExportProgressBarIncrement(40);
//- Export Data
this.ExportData();
DIExport.RaiseExportProgressBarIncrement(80);
this.ProcessOtherTables();
DIExport.RaiseExportProgressBarIncrement(90);
DIExport.RaiseExportProgressBarIncrement(ProgressCount++);
this.DeleteTable(TEMP_TABLE_Data, this.DestDBConnection);
DIExport.RaiseExportProgressBarIncrement(100);
}
}
catch
{
throw;
}
return RetVal;
}
private void ImportAssistant(DIConnection sourceDBConnection, DIQueries sourceDBQueries)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TargetTableNames;
DITables SourceTableNames;
string SqlString = string.Empty;
Dictionary<string, string> SkippedTopics = new Dictionary<string, string>();
try
{
DataPrefix = this._TargetDBConnection.DIDataSetDefault();
//Get all languages from target database
foreach (DataRow Row in this._TargetDBConnection.DILanguages(DataPrefix).Rows)
{
// check language exists in source database
LanguageCode = Row[Language.LanguageCode].ToString();
if (sourceDBConnection.IsValidDILanguage(DataPrefix, LanguageCode))
{
try
{
LanguageCode = "_" + LanguageCode;
//create source table names
SourceTableNames = new DITables(DataPrefix, LanguageCode);
//create target table names
TargetTableNames = new DITables(DataPrefix, LanguageCode);
// overwrite Assistant_EBook table from source database to Target database
this.ImportEBook(ref sourceDBConnection, ref sourceDBQueries, LanguageCode, SourceTableNames, TargetTableNames);
//import topic info from source database
try
{
// check Topic exists in target database
foreach (DataRow SourceTopicRow in sourceDBConnection.ExecuteDataTable(AssistantQueries.GetALLTopics(SourceTableNames.AssistantTopic)).Rows)
{
DataTable TargetTopicsTable = null;
try
{
TargetTopicsTable = this._TargetDBConnection.ExecuteDataTable(AssistantQueries.GetALLTopics(TargetTableNames.AssistantTopic, " where Topic_Name='" + SourceTopicRow[Assistant_Topic.TopicName].ToString() + "' "));
// Check Indicator_Gid or IUS Gids exists in target database.If not exists, then skip topic.
if (this.IsIndicatorGidExistsForAssistant(SourceTopicRow[Assistant_Topic.IndicatorGId].ToString()))
{
if (TargetTopicsTable.Rows.Count > 0) // Overwrite
{
SqlString = AssistantQueries.UpdateTopicIntro(TargetTableNames.AssistantTopic,
DICommon.RemoveQuotes(SourceTopicRow[Assistant_Topic.TopicIntro].ToString()),
DICommon.RemoveQuotes(SourceTopicRow[Assistant_Topic.TopicName].ToString()),
DICommon.RemoveQuotes(SourceTopicRow[Assistant_Topic.IndicatorGId].ToString()));
}
else // create new record
{
SqlString = AssistantQueries.InsertTopicInfo(TargetTableNames.AssistantTopic,
DICommon.RemoveQuotes(SourceTopicRow[Assistant_Topic.TopicName].ToString()),
SourceTopicRow[Assistant_Topic.IndicatorGId].ToString(),
DICommon.RemoveQuotes(SourceTopicRow[Assistant_Topic.TopicIntro].ToString()));
}
this._TargetDBConnection.ExecuteNonQuery(SqlString);
}
else
{
if (!SkippedTopics.ContainsKey(SourceTopicRow[Assistant_Topic.TopicName].ToString()))
{
SkippedTopics.Add(SourceTopicRow[Assistant_Topic.TopicName].ToString(), SourceTopicRow[Assistant_Topic.TopicName].ToString());
}
}
}
catch (Exception)
{
if (TargetTopicsTable != null)
{
TargetTopicsTable.Dispose();
}
}
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
//dispose source database connection
}
try
{
// get All Values of Assistant from source Database
DataTable SourceDbTable = sourceDBConnection.ExecuteDataTable(AssistantQueries.GetAssistantWTopicInfo(SourceTableNames.Assistant, SourceTableNames.AssistantTopic));
// check record exists in target database
foreach (DataRow SrcRow in SourceDbTable.Rows)
{
DataTable TargetDBTable = null;
try
{
// Check Indicator_Gid or IUS Gids exists in target database.If not exists, then skip topic.
if (!SkippedTopics.ContainsKey(SrcRow[Assistant_Topic.TopicName].ToString()))
{
SqlString = AssistantQueries.GetAssistantWTopicInfo(TargetTableNames.Assistant, TargetTableNames.AssistantTopic,
" where T.Topic_Name='" + DICommon.RemoveQuotes(SrcRow[Assistant_Topic.TopicName].ToString()) + "' and A.Assistant_Type='" +
DICommon.RemoveQuotes(SrcRow[Assistant.AssistantType].ToString()) + "' ");
TargetDBTable = this._TargetDBConnection.ExecuteDataTable(SqlString);
if (Microsoft.VisualBasic.Information.IsDBNull(SrcRow[Assistant.AssistantText].ToString()))
{
SrcRow[Assistant.AssistantText] = string.Empty;
}
if (TargetDBTable.Rows.Count > 0) // overwrite
{
SqlString = AssistantQueries.UpdateBlankTopicRecord(TargetTableNames.Assistant,
DICommon.RemoveQuotes(SrcRow[Assistant.AssistantText].ToString()),
SrcRow[Assistant.AssistantType].ToString(), TargetDBTable.Rows[0][Assistant.AssistantNId].ToString());
}
else // create new record
{
// get Topic Nid from Target Database
SqlString = AssistantQueries.GetALLTopics(TargetTableNames.AssistantTopic,
" where Topic_Name ='" + DICommon.RemoveQuotes(SrcRow[Assistant_Topic.TopicName].ToString()) + "' ");
int TopicNid = Convert.ToInt32(this._TargetDBConnection.ExecuteDataTable(SqlString).Rows[0][Assistant_Topic.TopicNId].ToString());
SqlString = AssistantQueries.CreateNewAssistantInfo(TargetTableNames.Assistant,
TopicNid, SrcRow[Assistant.AssistantType].ToString(),
DICommon.RemoveQuotes(SrcRow[Assistant.AssistantText].ToString()),
SrcRow[Assistant.AssistantOrder].ToString());
}
this._TargetDBConnection.ExecuteNonQuery(SqlString);
}
}
catch (Exception e1)
{
ExceptionHandler.ExceptionFacade.ThrowException(e1);
}
finally
{
if (TargetDBTable != null)
{
TargetDBTable.Dispose();
}
}
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
//dispose source database connection
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}