/// <summary>
/// Returns true/false. True if Database is in valid format otherwise false.
/// </summary>
/// <param name="forOnlineDB"></param>
/// <returns></returns>
public override bool IsValidDB(bool forOnlineDB)
{
bool RetVal = false;
// check RecommendedSources table exists or not
RecommendedSourcesBuilder RecommendedSrcBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
RetVal = RecommendedSrcBuilder.IsRecommendedSourcesTableExists();
return(RetVal);
}
private void RemoveEmptyRecordsFrmRecommendedSources(bool forOnlineDB)
{
RecommendedSourcesBuilder RecommendedSourcesTblBuilder;
try
{
RecommendedSourcesTblBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
RecommendedSourcesTblBuilder.DeleteRecordsWhereICIUSIsEmpty();
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void UpdateDataNIdTypeForRecommendedSourcesTable(bool forOnlineDB)
{
RecommendedSourcesBuilder RecommendedSourcesTblBuilder;
try
{
RecommendedSourcesTblBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
RecommendedSourcesTblBuilder.UpdateDataNIdDataType(forOnlineDB);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Saves ICIUSOrder into Data Table
/// </summary>
/// <param name="ICIUSOrder"></param>
/// <param name="dataNId"></param>
/// <returns></returns>
public bool SaveICIUSOrder(string ICIUSOrder, int dataNId)
{
bool RetVal = false;
RecommendedSourcesBuilder RecBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
try
{
RecBuilder.UpdateICIUSOrderInDataTable(dataNId, ICIUSOrder);
RetVal = true;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
public bool SaveRecommendedSources(DataTable table)
{
bool RetVal = false;
RecommendedSourcesBuilder RecBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
try
{
foreach (DataRow row in table.Rows)
{
RecBuilder.CheckNInsertRecommendedSource(Convert.ToInt32(row[Data.DataNId]), Convert.ToString(row[Data.ICIUSOrder]), Convert.ToString(row[RecommendedSources.ICIUSLabel]));
}
RetVal = true;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private void CreateNUpdateRecommendedSourcesTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
RecommendedSourcesBuilder RecommendedSourcesTblBuilder;
DIQueries TempQueries;
try
{
// step1: 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);
RecommendedSourcesTblBuilder = new RecommendedSourcesBuilder(this._DBConnection, TempQueries);
if (RecommendedSourcesTblBuilder.IsRecommendedSourcesTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Insert.CreateTable(TableNames.RecommendedSources,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
// step2: insert IC_IUS_Label into recommendedsource table from IC_IUS table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Insert.InsertAllLabelFrmICIUS(TableNames));
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Saves ICIUSLabel into recommendedsources table
/// </summary>
/// <param name="ICIUSLabel"></param>
/// <param name="ICIUSOrder"></param>
/// <param name="dataNId"></param>
/// <returns></returns>
public int SaveICIUSLabel(string ICIUSLabel, string ICIUSOrder, string dataNId)
{
int RetVal = -1;
DataRow[] Rows;
RecommendedSourcesBuilder RecBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
try
{
// check records is already exists or not. IF exists then get NID
Rows = this._RecommendedSourcesTable.Select(RecommendedSources.DataNId + "=" + dataNId);
if (Rows.Length > 0)
{
RetVal = Convert.ToInt32(Rows[0][RecommendedSources.NId]);
}
if (RetVal <= 0)
{
//insert into database
RecBuilder.InsertIntoDatabase(Convert.ToInt32(dataNId), ICIUSLabel);
RetVal = this._DBConnection.GetNewId();
}
else
{
// update into database
RecBuilder.UpdateRecommendedSources(Convert.ToInt32(dataNId), ICIUSLabel);
}
//update RecommendedSourcesTable
this.UpdateRecommendedSourcesTable(ICIUSLabel, Convert.ToInt32(dataNId), RetVal);
}
catch (Exception ex)
{
RetVal = -1;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
/// <summary>
/// Returns true/false. True if Database is in valid format otherwise false.
/// </summary>
/// <param name="forOnlineDB"></param>
/// <returns></returns>
public override bool IsValidDB(bool forOnlineDB)
{
bool RetVal = false;
// check RecommendedSources table exists or not
RecommendedSourcesBuilder RecommendedSrcBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
RetVal = RecommendedSrcBuilder.IsRecommendedSourcesTableExists();
return RetVal;
}
private void CreateNUpdateRecommendedSourcesTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
RecommendedSourcesBuilder RecommendedSourcesTblBuilder;
DIQueries TempQueries;
try
{
// step1: 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);
RecommendedSourcesTblBuilder = new RecommendedSourcesBuilder(this._DBConnection, TempQueries);
if (RecommendedSourcesTblBuilder.IsRecommendedSourcesTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Insert.CreateTable(TableNames.RecommendedSources,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
// step2: insert IC_IUS_Label into recommendedsource table from IC_IUS table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Insert.InsertAllLabelFrmICIUS(TableNames));
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
public bool SaveRecommendedSources(DataTable table)
{
bool RetVal = false;
RecommendedSourcesBuilder RecBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
try
{
foreach (DataRow row in table.Rows)
{
RecBuilder.CheckNInsertRecommendedSource(Convert.ToInt32(row[Data.DataNId]), Convert.ToString(row[Data.ICIUSOrder]), Convert.ToString(row[RecommendedSources.ICIUSLabel]));
}
RetVal = true;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
/// <summary>
/// Saves ICIUSOrder into Data Table
/// </summary>
/// <param name="ICIUSOrder"></param>
/// <param name="dataNId"></param>
/// <returns></returns>
public bool SaveICIUSOrder(string ICIUSOrder, int dataNId)
{
bool RetVal = false;
RecommendedSourcesBuilder RecBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
try
{
RecBuilder.UpdateICIUSOrderInDataTable(dataNId, ICIUSOrder);
RetVal = true;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
/// <summary>
/// Saves ICIUSLabel into recommendedsources table
/// </summary>
/// <param name="ICIUSLabel"></param>
/// <param name="ICIUSOrder"></param>
/// <param name="dataNId"></param>
/// <returns></returns>
public int SaveICIUSLabel(string ICIUSLabel, string ICIUSOrder, string dataNId)
{
int RetVal = -1;
DataRow[] Rows;
RecommendedSourcesBuilder RecBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
try
{
// check records is already exists or not. IF exists then get NID
Rows = this._RecommendedSourcesTable.Select(RecommendedSources.DataNId + "=" + dataNId);
if (Rows.Length > 0)
{
RetVal =Convert.ToInt32( Rows[0][RecommendedSources.NId]);
}
if (RetVal <= 0)
{
//insert into database
RecBuilder.InsertIntoDatabase(Convert.ToInt32(dataNId), ICIUSLabel);
RetVal = this._DBConnection.GetNewId();
}
else
{
// update into database
RecBuilder.UpdateRecommendedSources(Convert.ToInt32(dataNId), ICIUSLabel);
}
//update RecommendedSourcesTable
this.UpdateRecommendedSourcesTable(ICIUSLabel, Convert.ToInt32(dataNId), RetVal);
}
catch (Exception ex)
{
RetVal = -1;
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
private void UpdateDataNIdTypeForRecommendedSourcesTable(bool forOnlineDB)
{
RecommendedSourcesBuilder RecommendedSourcesTblBuilder;
try
{
RecommendedSourcesTblBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
RecommendedSourcesTblBuilder.UpdateDataNIdDataType(forOnlineDB);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void RemoveEmptyRecordsFrmRecommendedSources(bool forOnlineDB)
{
RecommendedSourcesBuilder RecommendedSourcesTblBuilder;
try
{
RecommendedSourcesTblBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
RecommendedSourcesTblBuilder.DeleteRecordsWhereICIUSIsEmpty();
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Convets Database into template
/// </summary>
public void ConvertDatabaseToTemplate()
{
SourceBuilder SrcBuilder;
FootnoteBuilder FoonotesBuilder;
IndicatorClassificationBuilder ICBuilder;
RecommendedSourcesBuilder RecommendedSrcBuilder;
DataTable ICTable;
string ICNIds = string.Empty;
try
{
// STEP 1: Remove records from DATA table
this.DBConnection.ExecuteNonQuery(this.DBQueries.Delete.Data.DeleteRecords(string.Empty));
// STEP 2: Remove records from TIME table
this.DBConnection.ExecuteNonQuery(this.DBQueries.Delete.Timeperiod.DeleteRecords(string.Empty));
// STEP 3: Remove records from SOURCE table
SrcBuilder = new SourceBuilder(this.DBConnection, this.DBQueries);
SrcBuilder.DeleteSources(string.Empty);
// STEP 4: Remove source records from Indicator_classification tables
ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries);
ICTable = this.DBConnection.ExecuteDataTable(this.DBQueries.IndicatorClassification.GetIC(FilterFieldType.Type, "'SR'", FieldSelection.NId));
if (ICTable != null && ICTable.Rows.Count > 0)
{
ICNIds = DIConnection.GetDelimitedValuesFromDataTable(ICTable, IndicatorClassifications.ICNId);
}
ICBuilder.DeleteClassification(ICNIds);
// STEP 5: Remove records from Footnotes table
FoonotesBuilder = new FootnoteBuilder(this.DBConnection, this.DBQueries);
FoonotesBuilder.DeleteFootnote(string.Empty);
// STEP 6: Creates DevInfoSP2 tables if missing.
DICommon.CheckNCreateDevInfoSP2Database(this.DBConnection, this.DBQueries, false);
// STEP 7: remove records from notes table
this.ClearNotesTables();
// STEP 8: remove records from RecommendedSources table
RecommendedSrcBuilder = new RecommendedSourcesBuilder(this.DBConnection, this.DBQueries);
RecommendedSrcBuilder.DeleteRecommendedSources(string.Empty);
// STEP 9:Remove Source metadata reports
new DI7MetaDataBuilder(this.DBConnection, this.DBQueries).DeleteMetadataReports(MetadataElementType.Source);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void InsertRecommendedSources()
{
String SqlQuery = string.Empty;
string ICIUSLabel = string.Empty;
string IUSNId = string.Empty;
string TimeperiodNId = string.Empty;
string SourceNId = string.Empty;
string AreaNId = string.Empty;
string ICIUSOrder = string.Empty;
int DataNId = -1;
DataTable TempTable;
RecommendedSourcesBuilder RecommendedSrcBuilder;
try
{
RecommendedSrcBuilder = new RecommendedSourcesBuilder(this.DBConnection, this.DBQueries);
// 1. Get IC_IUS_Label from Temp_Data table " Select * from Temp_data table where IC_IUS_Label<> null or IC_IUS_Label<> ''"
TempTable = this.DBConnection.ExecuteDataTable("Select * from " + DAImportCommon.Constants.TempDataTableName + " where " + RecommendedSources.ICIUSLabel + "<> null or " + RecommendedSources.ICIUSLabel + "<> '' or " + Data.ICIUSOrder + " <>null");
// 2. Insert recommendedsources(IC_IUS_Label) from TempData Table into UT_REcommendedSources_en
RecommendedSrcBuilder = new RecommendedSourcesBuilder(this.DBConnection, this.DBQueries);
foreach (DataRow Row in TempTable.Rows)
{
ICIUSLabel = Lib.DI_LibBAL.Utility.DICommon.RemoveQuotes(Convert.ToString(Row[RecommendedSources.ICIUSLabel]));
IUSNId = Convert.ToString(Row[DevInfo.Lib.DI_LibBAL.Import.DAImport.Common.Constants.NewIUSColumnName]);
TimeperiodNId = Convert.ToString(Row[Data.TimePeriodNId]);
AreaNId = Convert.ToString(Row[Data.AreaNId]);
SourceNId = Convert.ToString(Row[Data.SourceNId]);
ICIUSOrder = Convert.ToString(Row[Data.ICIUSOrder]);
// get new dataNid by IUSAST
DataNId = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(this.DBQueries.Data.GetDataNIdByIUSTimePeriodAreaSource(IUSNId, TimeperiodNId, AreaNId, SourceNId)));
if (DataNId > 0)
{
// insert or update recommendedSource
RecommendedSrcBuilder.CheckNInsertRecommendedSource(DataNId, ICIUSOrder, ICIUSLabel);
}
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}