/// <summary>
/// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue
/// and merge both column value into new column Data_Value
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
public static void MergeTextualandNumericDataValueColumn(DIConnection dbConnection, DIQueries dbQueries)
{
try
{
if (!ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data))
{
//1. Rename Textual_Data_Value & Data_value to orgTextual_Data_value & orgData_Value respectively
RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.TextualDataValue, Constants.Data.Orginal_Textual_Data_valueColumn, " Memo");
RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.DataValue, Constants.Data.Orginal_Data_valueColumn, " Double ");
System.Threading.Thread.Sleep(100);
//2. Create new column Data_Value of memo data type
string SqlQuery = "ALTER Table " + dbQueries.TablesName.Data + " Add Column " + Data.DataValue + " Memo NULL";
dbConnection.ExecuteNonQuery(SqlQuery);
System.Threading.Thread.Sleep(10);
//3. Merge all data values into Data_Value column
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Data_valueColumn;
dbConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Textual_Data_valueColumn + " WHERE " + Data.IsTextualData + "=" + true;
dbConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void ClearIcon(DIConnection dbConnection, string dataPrefix, IconElementType elementType)
{
try
{
dbConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.ClearIcon(dataPrefix, DIIcons.Elements[elementType].ToString()));
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
public static int CreateDISearchResultsTable(DIConnection dbConnection, string languageCode)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
try
{
TblName = QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode;
dbConnection.DropTable(TblName);
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.NId + "] COUNTER, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + "] Text(50) NOT NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + "] YESNO NOT NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + "] Text(255) NOT NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Unit + "] Text(128) NOT NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Area + "] Text(255) NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + "] Text(255) NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.MRD + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SGCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SourceCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.TPCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + "] Text(255) NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVNIds + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Dimensions + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.BlockAreaParentNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IsBlockAreaRecord + "] YESNO");
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
/// <summary>
/// Create and insert into TmpDI7SearchAreas table
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="searchAreas"></param>
/// <param name="isSearchForQS"></param>
public static void CreateTmpAreaSearchTbl(DIConnection dbConnection, string searchAreas, bool isSearchForQS)
{
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
string SearchLanguage = string.Empty;
try
{
#region "-- Create table schema --"
if (isSearchForQS)
{
CacheUtility.CreateSearchAreasTable(dbConnection, false);
}
else
{
CacheUtility.CreateSearchAreasTable(dbConnection, true);
}
#endregion
if (!string.IsNullOrEmpty(searchAreas))
{
CacheUtility.GetSplittedList(dbConnection, searchAreas, ",", false);
SBQry.Remove(0, SBQry.Length);
SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.SearchAreas.TableName);
SBQry.Append(" SELECT List." + QDSConstants.QDSTables.SplittedList.Columns.Value + " as AreaNId");
SBQry.Append(" FROM " + QDSConstants.QDSTables.SplittedList.TableName + " List");
StrQry = SBQry.ToString();
dbConnection.ExecuteNonQuery(StrQry);
}
}
catch (Exception)
{
throw;
}
}
public bool CreateMetadataFromSDMX(string templateFileName,string tempFolderPath, string languageCode,string sdmxRegistryUrl)
{
bool RetVal = true;
DIConnection DBConnection = null;
DIQueries DBQueries;
try
{
//create temp template file
DIDatabase TempTemplateFile = new DIDatabase(templateFileName, "UT_", languageCode);
// create database object
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty);
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), languageCode);
// delete default categories from blank template
DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Delete.DeleteMetadataCategory(DBQueries.TablesName.MetadataCategory, string.Empty));
// import data from SDMX
BaseSDMXHelper HelperObj = new BaseSDMXHelper(DBConnection, DBQueries);
string TempXmlFile = tempFolderPath + "\\IMPORT_MSDXML_" + this.GetDateTimeStamp() + DICommon.FileExtension.XML;
HelperObj.GenerateMetadataXMLFromSDMXWebservice(sdmxRegistryUrl, TempXmlFile);
}
catch (Exception ex)
{
RetVal = false;
}
finally
{
// dispose database object
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
return RetVal;
}
public static int CreateSplittedListTable(DIConnection dbConnection, bool isNumericValues)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
string ColumnDataType = "Text(255)";
try
{
TblName = QDSConstants.QDSTables.SplittedList.TableName;
dbConnection.DropTable(TblName);
if (isNumericValues)
{
ColumnDataType = "Long";
}
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.SplittedList.Columns.Value + "] " + ColumnDataType);
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
public static int CreateTempMRDRecordsTable(DIConnection dbConnection)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
try
{
TblName = QDSConstants.QDSTables.TempMRDRecords.TableName;
dbConnection.DropTable(TblName);
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + "] Text(255) NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.DVCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.DV + "] Text(255) NULL ");
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
public static int CreateSearchIndicatorsTable(DIConnection dbConnection)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
try
{
TblName = QDSConstants.QDSTables.SearchIndicators.TableName;
dbConnection.DropTable(TblName);
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + "] Long");
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
public static int CreateSearchIndicatorsTable1(DIConnection dbConnection)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
try
{
TblName = QDSConstants.QDSTables.SearchIndicators.TableName;
dbConnection.DropTable(TblName);
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.Id + "] COUNTER CONSTRAINT PKey PRIMARY KEY, ");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + "] Long DEFAULT 0, ");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorName + "] Varchar(255) DEFAULT '' NOT NULL , ");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.ICName + "] Varchar DEFAULT '' NOT NULL ");
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
/// <summary>
/// Creates Icon tables for all available language
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="forOnlineDB"></param>
public static void CreateIconsTblsForAllLngs(DIConnection dbConnection, DIQueries dbQueries, bool forOnlineDB)
{
if (DIIcons.IsIconsTblExists(dbQueries.TablesName.Icons, dbConnection) == false)
{
try
{
//-- create Icon table
dbConnection.ExecuteNonQuery(DIIcons.CreateIconsTbl(dbQueries.TablesName.Icons, forOnlineDB));
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
}
}
/// <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;
}
}
public static void DeleteTempTables(DIConnection connection)
{
try
{
connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Indicator);
connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Unit);
connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Subgroup);
connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.SubgroupType);
connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.SubgroupVals);
connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Area);
connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.IndicatorClassification);
}
catch (Exception)
{ }
}
/// <summary>
/// Create new table schema of DI_Search_Result table
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="tableName"></param>
/// <param name="languageCode"></param>
/// <returns></returns>
private static int CreateSearchResultCopyTable(DIConnection dbConnection, string tableName, string languageCode)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
try
{
dbConnection.DropTable(tableName);
SBQry.Remove(0, SBQry.Length);
SBQry.Append("SELECT * INTO " + tableName);
SBQry.Append(" FROM " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode);
SBQry.Append(" WHERE 1=2");
StrQry = SBQry.ToString();
RetVal = dbConnection.ExecuteNonQuery(StrQry);
}
catch (Exception)
{
}
return RetVal;
}
private void UpdateMRDTable(DIConnection ObjDIConnection)
{
string Query = string.Empty;
string TempTableName = "temp1";
string DTData = ObjDIConnection.DIDataSetDefault() + "Data";
// 1. drop temp1 table
try
{
ObjDIConnection.ExecuteNonQuery("Drop table " + TempTableName);
}
catch (Exception)
{
}
// 2. Create Temp1 table for IsMRD calculation
Query = "Select MRDTable.*, T2.Timeperiod_nid into " + TempTableName + " from ( SELECT d.IUSNId, d.Area_NId, MAX(t.TimePeriod) AS timeperiod FROM " + ObjDIConnection.DIDataSetDefault() + "Data" + " d," + ObjDIConnection.DIDataSetDefault() + "TimePeriod" + " t WHERE d.TimePeriod_NId= t.TimePeriod_NId GROUP BY d.IUSNId,d.Area_NId) AS MRDTable , " + ObjDIConnection.DIDataSetDefault() + "TimePeriod" + " T2 where MRDTable.timeperiod=T2.Timeperiod";
ObjDIConnection.ExecuteNonQuery(Query);
// 3. set IsMrd to false in data table
Query = "UPDATE " + ObjDIConnection.DIDataSetDefault() + "Data" + " SET IsMRD=0, MultipleSource=0";
ObjDIConnection.ExecuteNonQuery(Query);
// 4. update IsMrd in DataTable
Query = "UPDATE " + DTData + " SET " + DTData + ".IsMRD=1, " + DTData + ".MultipleSource=1 FROM temp1 INNER JOIN " + DTData + " ON temp1.IUSNId = " + DTData + ".IUSNId AND temp1.Timeperiod_nid = " + DTData + ".TimePeriod_NId AND temp1.Area_NId = " + DTData + ".Area_NId";
ObjDIConnection.ExecuteNonQuery(Query);
//4. drop table
ObjDIConnection.ExecuteNonQuery("Drop table " + TempTableName);
}
// this method is used for creating cms database if database not exist
/// <summary>
/// Create CMS database for adaptation If Database Not exise
/// </summary>
/// <param name="CMSDatabaseName">Name of the created Cms Database</param>
/// <returns>True if database created or already exist</returns>
public static bool CheckNCreateCMSDatabase(out string CMSDatabaseName)
{
bool RetVal = false;
DIConnection ObjDIConnection = null;
FileInfo ScriptFile;
string DbScripts = string.Empty;
string LngDbScripts = string.Empty;
string DBConn = string.Empty;
string[] DBConnArr;
string DbNId = string.Empty;
string Password = string.Empty;
string DatabaseName = string.Empty; // Name of Adaptation Database
CMSDatabaseName = string.Empty; // Name of CMS Database
string DatabasePath = string.Empty;
try
{
// Get Database NID of Default Database
DbNId = Global.GetDefaultDbNId();
// If Database NID is null or empty break further execution of code
if (string.IsNullOrEmpty(DbNId))
{
RetVal = false;
return RetVal;
}
//Get connection details of database
DBConnArr = Global.GetDbNConnectionDetails(DbNId, string.Empty);
// If Database returned database details are less than 6 break further execution of code
if (DBConnArr.Length < 6)
{
RetVal = false;
return RetVal;
}
//Get Decrypted password
Password = DBConnArr[6].ToString();
//Get Adaptation Database name
DatabaseName = DBConnArr[4].ToString();
//Name of CMS Database
CMSDatabaseName = DatabaseName + "_CMS";
// Create Database connection
ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(DBConnArr[2])), DBConnArr[3].ToString(), string.Empty, DatabaseName, DBConnArr[5].ToString(), Password);
//Create and Execute database creation script
ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Global.CmsDbCreationFile));
// If Script File not exist in the folder break further execution of code
if (!ScriptFile.Exists)
{
RetVal = false;
return RetVal;
}
// get path of database file from web config file
DatabasePath = Global.CmsDataBasePath;// ConfigurationManager.AppSettings[Constants.WebConfigKey.CmsDataBasePath].ToString();
// This Script file contains sql syntax to creates database, if database is not already existing in database
// Replace DBName with new database name
DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("DBName", CMSDatabaseName).Replace("GO", "");
DbScripts = DbScripts.Replace("CmsDataBasePath", DatabasePath);
// Execute script file to check and create database schema
ObjDIConnection.ExecuteNonQuery(DbScripts);
// Close file object
ScriptFile.OpenText().Close();
//Create and Execute database tables creation script
ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Global.CmsTableDefinition));
// If Script File not exist in the folder break further execution of code
if (!ScriptFile.Exists)
{
RetVal = false;
return RetVal;
}
// This Script file contains sql syntax to creates datatables, if datatables are not already existing in database
DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("DBName", CMSDatabaseName);//.Replace("GO", "\nGO");
if (DbScripts.Contains("--GO"))
{
List<string> ListQuery = new List<string>();
ListQuery.AddRange(Global.SplitString(DbScripts, "--GO"));
foreach (string StrQuery in ListQuery)
{
// Execute script file to create database Tables
ObjDIConnection.ExecuteNonQuery(StrQuery);
}
}
else
{
ObjDIConnection.ExecuteNonQuery(DbScripts);
}
// Close file object
ScriptFile.OpenText().Close();
// Set Retval to true
RetVal = true;
// Dispose connection object
ObjDIConnection.Dispose();
}
catch (Exception ex)
{
RetVal = false;
Global.CreateExceptionString(ex, null);
}
finally
{ // dispose connection object
ObjDIConnection = null;
ScriptFile = null;
}
return RetVal;
}
///// <summary>
///// Get all available language code from database
///// </summary>
///// <param name="diConnection"></param>
///// <returns></returns>
//private string[] GetAllAvailableLanguageCode(DIConnection diConnection)
//{
// string[] RetVal;
// DataTable DTAvailableLanguages = null;
// List<string> AvlLanguages = new List<string>();
// try
// {
// DTAvailableLanguages = diConnection.DILanguages(diConnection.DIDataSetDefault());
// foreach (DataRow Row in DTAvailableLanguages.Rows)
// {
// AvlLanguages.Add(Row["Language_Code"].ToString());
// }
// }
// catch (Exception)
// {
// }
// RetVal = AvlLanguages.ToArray();
// return RetVal;
//}
/// <summary>
/// Run dbscripts in database from script files
/// </summary>
/// <param name="ObjDIConnection"></param>
/// <returns></returns>
private Boolean RunDBScripts(DIConnection ObjDIConnection, string DBNid, string DbDefaultLanguage)
{
Boolean RetVal = false;
FileInfo ScriptFile;
string DbScripts = string.Empty;
string LngFile = string.Empty;
string LngCode = string.Empty;
string LngDbScripts = string.Empty;
int DefaultDBNid = 0;
DataTable DatabaseLanguages = null;
try
{
//Create and Execute table definition scripts
ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.TableDefinitionsFile]));
DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("GO", "");
ObjDIConnection.ExecuteNonQuery(DbScripts);
ScriptFile.OpenText().Close();
//Create and Execute db language scrpts
ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DbScriptsCreationFile]));
DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("GO", "");
if (DBNid.Trim() != "")
{
DefaultDBNid = Convert.ToInt32(DBNid);
}
else
{
DefaultDBNid = Convert.ToInt32(Global.GetDefaultDbNId());
}
DatabaseLanguages = Global.GetAllDBLangaugeCodesByDbNid(DefaultDBNid, ObjDIConnection);
foreach (DataRow langRow in DatabaseLanguages.Rows)
{
LngDbScripts = DbScripts;
LngDbScripts = LngDbScripts.Replace("_XX", "_" + langRow["Language_Code"]);
foreach (string LngDbScriptBlock in Global.SplitString(LngDbScripts, "--SPSEPARATOR--"))
{
try
{
ObjDIConnection.ExecuteNonQuery(LngDbScriptBlock);
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
}
}
ScriptFile.OpenText().Close();
////Create and Execute db language scrpts
//DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("GO", "");
//foreach (string lang in getAllDbLangCodes(Convert.ToInt32(Global.GetDefaultDbNId())))
//{
// LngDbScripts = DbScripts;
// LngDbScripts = LngDbScripts.Replace("_XX", "_" + lang);
// foreach (string LngDbScriptBlock in Global.SplitString(LngDbScripts, "--SPSEPARATOR--"))
// {
// try
// {
// ObjDIConnection.ExecuteNonQuery(LngDbScriptBlock);
// }
// catch (Exception ex)
// {
// Global.CreateExceptionString(ex, null);
// }
// }
//}
//ScriptFile.OpenText().Close();
//Create and Execute table indexs scripts
ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DbTableIndexes]));
DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("GO", "");
ObjDIConnection.ExecuteNonQuery(DbScripts);
ScriptFile.OpenText().Close();
RetVal = true;
// Execute method to synchronize language files from ut_language
this.SynchronizeLanguage(ObjDIConnection, DefaultDBNid, DbDefaultLanguage);
this.ReverseSynchronizeLanguage(ObjDIConnection, DefaultDBNid);
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
finally
{
}
return RetVal;
}
/// Update connection details by selected db
/// </summary>
/// <param name="requestParam"></param>
/// <returns></returns>
public string AdminUpdateDbConnection(string requestParam)
{
string RetVal = string.Empty;
string[] Params;
string DBConnectionsFile = string.Empty;
XmlDocument XmlDoc;
XmlNode xmlNode;
string DbNId = string.Empty;
string ConnName = string.Empty;
string DbConn = string.Empty;
string Password = string.Empty;
string DefArea = string.Empty;
string Description = string.Empty;
string IsDefDb = string.Empty;
string CategoryName = string.Empty;
XmlElement NewCategoryElement;
XmlNode NewXmlNode;
XmlNode CategoryNode;
DIConnection ObjDIConnection = null;
string[] DbAvailableLanguage;
string strDefaultLanguage = string.Empty;
string CountStr = string.Empty;
DataTable DTCounts;
string QryStr = string.Empty;
string DbDefaultLanguage = string.Empty;
string DbAvailableLanguageStr = string.Empty;
string DefAreaJSon = string.Empty;
string DefAreaCount = "0";
string[] DefAreaStr;
string[] DefIndStr;
string DefInd = string.Empty;
string DefIndJSon = string.Empty;
DBConverterDecorator objDBConverterDecorator = null;
DBVersionBuilder VersionBuilder = null;
///Variables for creatin XLSLogfile
string[] DatabaseParams;
string XLSFileMsg = string.Empty;
string Server_HostName = string.Empty;
string DatabaseName = string.Empty;
string DataBaseUserName = string.Empty;
string[] DBConnArr;
try
{
requestParam = HttpUtility.UrlDecode(requestParam);
Params = Global.SplitString(requestParam, Constants.Delimiters.ParamDelimiter);
DbNId = Params[0];
ConnName = Params[1];
DbConn = Params[2];
Password = Params[3];
Description = Params[4];
IsDefDb = Params[5];
CategoryName = Params[6];
DBConnectionsFile = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DBConnectionsFile]);
XmlDoc = new XmlDocument();
XmlDoc.Load(DBConnectionsFile);
DBConnArr = DbConn.Split(new string[] { "||" }, StringSplitOptions.None);
ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(DBConnArr[0])), DBConnArr[1].ToString(), string.Empty, DBConnArr[2].ToString(), DBConnArr[3].ToString(), Password);
if (!string.IsNullOrEmpty(Password))
{
Password = Global.EncryptString(Password);
DbConn = DbConn + Password;
}
QryStr = "SELECT Language_Code FROM ut_language WHERE Language_Default = 1";
DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
if (DTCounts.Rows.Count == 1) strDefaultLanguage = DTCounts.Rows[0]["Language_Code"].ToString();
else strDefaultLanguage = "en";
// Get counts from db
QryStr = "select DBMtd_AreaCnt, DBMtd_IndCnt, DBMtd_SrcCnt, DBMtd_DataCnt from ut_dbmetadata_" + strDefaultLanguage;
DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
DataRow Row = DTCounts.Rows[0];
CountStr = string.Format("{0:0,0}", Row[0]) + "_" + string.Format("{0:0,0}", Row[1]) + "_" + string.Format("{0:0,0}", Row[2]) + "_" + string.Format("{0:0,0}", Row[3]);
//get default language code in database
DbDefaultLanguage = ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault());
//get all avalilable language code in database
DbAvailableLanguage = Global.GetAllAvailableLanguageCode(ObjDIConnection);
if (DbAvailableLanguage.Length > 0)
{
DbAvailableLanguageStr = string.Join(",", DbAvailableLanguage);
}
#region -- get default indicator and their Json data --
DefIndStr = GetDefaultIndicators(ObjDIConnection, strDefaultLanguage);
DefInd = DefIndStr[0];
DefIndJSon = DefIndStr[1];
#endregion
#region --get default area of level 1 and 2 (L1 + L2) by stored procedure with their json and counts --
DefAreaStr = GetDefaultAreas(ObjDIConnection, strDefaultLanguage);
DefArea = DefAreaStr[0];
DefAreaJSon = DefAreaStr[1];
DefAreaCount = DefAreaStr[2];
#endregion
//Check connection name already exists or not
if (XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Name + "='" + ConnName + "']") != null && XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Name + "='" + ConnName + "']").Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Id].Value != DbNId.ToString())
{
RetVal = "exists";
}
else
{
xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Id + "=" + DbNId + "]");
if (xmlNode.Attributes["langcode_csvfiles"] == null)
{
XmlAttribute attrCSV = XmlDoc.CreateAttribute("langcode_csvfiles");
attrCSV.Value = string.Empty;
xmlNode.Attributes.Append(attrCSV);
}
else
{
xmlNode.Attributes["langcode_csvfiles"].Value = string.Empty;
}
if (xmlNode.ParentNode.Attributes[Constants.XmlFile.Db.Tags.CategoryAttributes.Name].Value == CategoryName)
{
//Update for same category name
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Name].Value = ConnName;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DatabaseConnection].Value = DbConn;
if (xmlNode.Attributes["desc" + DbDefaultLanguage] == null)
{
XmlAttribute attrDesc = XmlDoc.CreateAttribute("desc" + DbDefaultLanguage);
attrDesc.Value = Description;
xmlNode.Attributes.Append(attrDesc);
}
else
{
xmlNode.Attributes["desc" + DbDefaultLanguage].Value = Description;
}
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Count].Value = CountStr;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.LastModified].Value = string.Format("{0:yyyy-MM-dd}", DateTime.Today.Date);
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.AvailableLanguage].Value = DbAvailableLanguageStr;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultLanguage].Value = DbDefaultLanguage.Substring(1);
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicator].Value = DefInd;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicatorJSON].Value = DefIndJSon;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultArea].Value = DefArea;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaJSON].Value = DefAreaJSon;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaCount].Value = DefAreaCount;
//Update default dbNId
if (IsDefDb == "true")
{
XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).Attributes[Constants.XmlFile.Db.Tags.RootAttributes.Default].Value = DbNId;
}
File.SetAttributes(DBConnectionsFile, FileAttributes.Normal);
XmlDoc.Save(DBConnectionsFile);
//Update Metadata Description in the database
ObjDIConnection.ExecuteNonQuery("UPDATE " + ObjDIConnection.DIDataSetDefault() + "dbmetadata" + ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()).ToString() + " SET DBMtd_Desc = '" + Description.Replace("'", "''") + "'");
RetVal = "true";
}
else if (xmlNode.ParentNode.Attributes[Constants.XmlFile.Db.Tags.CategoryAttributes.Name].Value != CategoryName)
{
//Update when category name has changed for same id
//Update values in node
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Name].Value = ConnName;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DatabaseConnection].Value = DbConn;
xmlNode.Attributes["desc_en"].Value = Description;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Count].Value = CountStr;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.LastModified].Value = string.Format("{0:yyyy-MM-dd}", DateTime.Today.Date);
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.AvailableLanguage].Value = DbAvailableLanguageStr;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultLanguage].Value = DbDefaultLanguage.Substring(1);
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicator].Value = DefInd;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicatorJSON].Value = DefIndJSon;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultArea].Value = DefArea;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaJSON].Value = DefAreaJSon;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaCount].Value = DefAreaCount;
//Update default dbNId
if (IsDefDb == "true")
{
XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).Attributes[Constants.XmlFile.Db.Tags.RootAttributes.Default].Value = DbNId;
}
//Copy updated node values in new node
NewXmlNode = xmlNode;
//Remove current node
xmlNode.ParentNode.RemoveChild(xmlNode);
//Read the category node
CategoryNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "[@" + Constants.XmlFile.Db.Tags.CategoryAttributes.Name + "='" + CategoryName + "']");
//Create a category node if not exists
if (CategoryNode == null)
{
NewCategoryElement = XmlDoc.CreateElement(Constants.XmlFile.Db.Tags.Category);
NewCategoryElement.SetAttribute(Constants.XmlFile.Db.Tags.CategoryAttributes.Name, CategoryName);
CategoryNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).AppendChild(NewCategoryElement);
}
//Append the new node in category
CategoryNode.AppendChild(NewXmlNode);
File.SetAttributes(DBConnectionsFile, FileAttributes.Normal);
XmlDoc.Save(DBConnectionsFile);
RetVal = "true";
//Update Metadata Description in the database
ObjDIConnection.ExecuteNonQuery("UPDATE " + ObjDIConnection.DIDataSetDefault() + "dbmetadata" + ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()).ToString() + " SET DBMtd_Desc = '" + Description.Replace("'", "''") + "'");
}
if (Global.standalone_registry != "true")
{
this.RunDBScripts(ObjDIConnection, DbNId, DbDefaultLanguage.Substring(1));
// Generate language file because RunDBScripts method deletes existing language file, so user will not be able to execute furter functionality.
GenerateAllPagesXML();
VersionBuilder = new DBVersionBuilder(ObjDIConnection, new DIQueries(ObjDIConnection.DIDataSetDefault(), ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault())));
VersionBuilder.InsertVersionInfo(Constants.DBVersion.DI7_0_0_0, Constants.DBVersion.VersionsChangedDatesDI7_0_0_0, Constants.DBVersion.VersionCommentsDI7_0_0_0);
}
// Set description filed to update in service database
this.DataBaseDescription = Description.Replace("'", "''").Replace("\n", "<br />").Replace(@"""", @"\""");
GetAndUpdateIndexedAreas(DbAvailableLanguage, ObjDIConnection);
GetAndUpdateIndexedIndicators(DbAvailableLanguage, ObjDIConnection);
UpdateAdaptations(ObjDIConnection);
DeleteSitemapFiles();
}
#region "Call method to write log in XLS file"
if (RetVal == "true")
{
DatabaseParams = DbConn.Split(new string[] { "||" }, StringSplitOptions.None);
if (!string.IsNullOrEmpty(DatabaseParams[1].ToString()))
{
Server_HostName = DatabaseParams[1].ToString();
}
if (!string.IsNullOrEmpty(DatabaseParams[2].ToString()))
{
DatabaseName = DatabaseParams[2].ToString();
}
if (!string.IsNullOrEmpty(DatabaseParams[3].ToString()))
{
DataBaseUserName = DatabaseParams[3].ToString();
}
//"Connection Name:{0}, Server/Host Name:{1}, Database Name:{2}, User Name:{3}, Description:{4}";
XLSFileMsg = string.Format(Constants.CSVLogMessage.UpdateConnection, ConnName, Server_HostName, DatabaseName, DataBaseUserName, Description);
WriteLogInXLSFile(Constants.AdminModules.DatabaseSettings.ToString(), XLSFileMsg);
}
#endregion
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
return RetVal;
}
/// <summary>
/// Store mapping information
/// </summary>
/// <param name="mappingInfo">string containing unmatched list & mapping ids</param>
/// <param name="type">which type of list(aid or aname)</param>
public string storeMappingDetails(string mappingInfo, string type)
{
DIConnection dIConnection = null;
try
{
string delemeter1 = "{||}";
string delemeter2 = "{}";
string[] mappingAreaInfo = mappingInfo.Split(new string[] { delemeter1 }, StringSplitOptions.None);
string mapping_nid = string.Empty;
string areaName = string.Empty;
DataTable dtKeyword = null;
string query = string.Empty;
string UserDefinedKey = string.Empty;
string MappingKey = string.Empty;
dIConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Server.MapPath("~//stock//Database.mdb"),
string.Empty, string.Empty);
bool usingMapServer = false;//Convert.ToBoolean(isMapServer.Trim());
if (Session["IsMapServer"] != null)
{
usingMapServer = Convert.ToBoolean(Session["IsMapServer"]);
}
foreach (string mappingArea in mappingAreaInfo)
{
if (mappingArea.IndexOf(delemeter2) > -1)
{
string[] areaData = mappingArea.Split(new string[] { delemeter2 }, StringSplitOptions.None);
UserDefinedKey = MappingKey = "";
if (areaData != null)
{
UserDefinedKey = areaData[0].Trim();
MappingKey = areaData[1].Trim();
}
if (UserDefinedKey.IndexOf("'") > -1)
UserDefinedKey = UserDefinedKey.Replace("'", "''");
query = "select * from mappinginformation where user_key = '" + UserDefinedKey + "' and mapping_key='" + MappingKey + "' and type ='" + type + "' and mapserver_used ='" + usingMapServer.ToString() + "'";
dtKeyword = dIConnection.ExecuteDataTable(query);
if (dtKeyword.Rows.Count == 0) // If record doesn't exist into table
{
if (!string.IsNullOrEmpty(MappingKey))
{
query = "insert into mappinginformation(user_key,mapping_key,type,item_count,mapserver_used) values('" + UserDefinedKey + "','" + MappingKey + "','" + type + "',0,'" + usingMapServer.ToString() + "')";
dIConnection.ExecuteNonQuery(query);
}
}
query = "select * from mappinginformation where user_key = '" + UserDefinedKey + "' and mapping_key='" + MappingKey + "' and type ='" + type + "'";
dtKeyword = dIConnection.ExecuteDataTable(query);
foreach (DataRow dr in dtKeyword.Rows)
{
if (!string.IsNullOrEmpty(dr["mapping_key"].ToString()))
{
int count = Int32.Parse(dr["item_count"].ToString());
count++;
query = "update mappinginformation set item_count=" + count + " where user_key = '" + UserDefinedKey + "' and mapping_key='" + MappingKey + "' and type ='" + type + "' and mapserver_used ='" + usingMapServer.ToString() + "'";
dIConnection.ExecuteNonQuery(query);
}
}
}
}
dIConnection.Dispose();
}
catch (Exception ex)
{
dIConnection.Dispose();
Global.CreateExceptionString(ex, null);
}
return string.Empty;
}
public static int DeleteTableRecords(DIConnection dbConnection, string tableName)
{
int RetVal = -1;
string StrQry = string.Empty;
try
{
StrQry = "Delete * from " + tableName;
RetVal = dbConnection.ExecuteNonQuery(StrQry);
}
catch (Exception)
{
}
return RetVal;
}
private int GetClassNId(DIConnection DBConnection, DIQueries DBQueries, out bool bNewClass, String classGUID, Boolean SectorGlobal, int iParentNId, String className)
{
int RetVal = 0;
string sQry = string.Empty;
Boolean NewClass = false;
try
{
//-- Getting Query from DAL
sQry = DBQueries.Calculates.GetICNIdByGId("'" + classGUID + "'");
//-- Executing Query
RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (RetVal == 0)
{
NewClass = true;
// -- Getting insert Query
sQry = DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(DBQueries.DataPrefix, DBQueries.LanguageCode, className.Replace("'", "''"), classGUID, SectorGlobal, iParentNId, "", ICType.Sector);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
}
catch (Exception ex)
{
RetVal = 0;
}
bNewClass = NewClass;
return RetVal;
}
public static void GetSplittedList(DIConnection dbConnection, string strDelimitedText, string delimiter, bool isNumericValues)
{
//-- FN_GET_SPLITTED_LIST
string[] TxtArr;
string StrQry = string.Empty;
try
{
if (!string.IsNullOrEmpty(strDelimitedText))
{
CreateSplittedListTable(dbConnection, isNumericValues);
//-- Split delimited text into array
TxtArr = DICommon.SplitString(strDelimitedText, delimiter);
//-- Insert each text into table
foreach (string Txt in TxtArr)
{
if (!string.IsNullOrEmpty(Txt))
{
StrQry = "Insert into " + QDSConstants.QDSTables.SplittedList.TableName;
if (isNumericValues)
{
StrQry += " values (" + Txt + ")";
}
else
{
StrQry += " values ('" + Txt + "')";
}
dbConnection.ExecuteNonQuery(StrQry);
}
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Add langPrefNid (INT) table to the Artefacts table in database.mdb
/// </summary>
/// <returns>bool</returns>
public static bool BaselineAccessDbSchema()
{
DIConnection diConnection;
bool retVal = false;
bool IsColumnExists = false;
string query = string.Empty;
using (diConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, @"stock\Database.mdb"), string.Empty, string.Empty))
{
try
{
// Check if field exists to avoid exception
try
{
query = @" ALTER TABLE Artefacts ADD COLUMN LangPrefNid INT";
diConnection.ExecuteNonQuery(query);
}
catch
{
IsColumnExists = true;
}
//if (IsColumnExists == false)
//{
// query = @" ALTER TABLE Artefacts ADD COLUMN LangPrefNid INT";
// diConnection.ExecuteNonQuery(query);
//}
retVal = true;
}
catch (Exception ex) //catch specific exception by refering to the DIConnectino source.
{
Global.CreateExceptionString(ex, null);
throw ex;
}
}
return retVal;
}
/// <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>
/// Dictionary containing old IconNId as key and new IconNId as value.
/// This information shall be utilised to update metadata xml being inserted / updated
/// </summary>
/// <param name="NidInSourceDB"></param>
/// <param name="NidInTargetDB"></param>
/// <param name="elementType"></param>
/// <param name="sourceQurey"></param>
/// <param name="SourceDBConnection"></param>
/// <param name="targetQurey"></param>
/// <param name="TargetDBConnection"></param>
/// <returns>
/// </returns>
/// <remarks></remarks>
public static Dictionary<string, string> ImportElement(int NidInSourceDB, int NidInTargetDB, IconElementType elementType, DIQueries sourceQurey, DIConnection sourceDBConnection, DIQueries targetQurey, DIConnection targetDBConnection)
{
Dictionary<string, string> RetVal = new Dictionary<string, string>();
string ElementValue = DIIcons.Elements[elementType];
string SqlQuery = string.Empty;
DataTable IconsDatatable;
string OldIconNId = string.Empty;
string NewIconNId = string.Empty;
try {
if ((sourceDBConnection != null)) {
if (DIIcons.IsIconsTblExists(sourceQurey.TablesName.Icons, sourceDBConnection)) {
//-- In Target Database: delete records from UT_Icon table if Icon is already associated with given Element Type
SqlQuery=DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.DeleteIcon(targetQurey.DataPrefix,ElementValue, NidInTargetDB.ToString());
targetDBConnection.ExecuteNonQuery(SqlQuery);
//-- In Source Database: check Icon is associated with the given Element type in UT_ICon table
SqlQuery = sourceQurey.Icon.GetIcon(NidInSourceDB.ToString(), ElementValue);
IconsDatatable = sourceDBConnection.ExecuteDataTable(SqlQuery);
//-- If associated, then copy it it from Source database into target database
foreach(DataRow Row in IconsDatatable.Rows)
{
//-- Insert Icon and get new IconNId
NewIconNId = DIIcons.InsertIcon(targetDBConnection, targetQurey, (byte[])(Row["Element_Icon"]), Row["Icon_Type"].ToString(), Convert.ToInt32(Row["Icon_Dim_W"]), Convert.ToInt32(Row["Icon_Dim_H"]), ElementValue,Convert.ToString( NidInTargetDB)).ToString();
//-- Add Item to Dictionary with New IconNId as Key and Old IconNId as Value
RetVal.Add(IMG_PREFIX + Row["Icon_NId"].ToString() + ".", IMG_PREFIX + NewIconNId + ".");
}
}
}
}
catch (Exception ex) {
throw new ApplicationException(ex.Message);
}
return RetVal;
}
/// <summary>
/// Insert Newly added records from DES to database
/// </summary>
/// <param name="sDestDBPath">Destination Database Path</param>
/// <param name="MsAccessPwd">Password</param>
/// <param name="sLng_Suffix">Lang suffix</param>
public void InsertNewRecords(String tempDESheetPath, string sDestDBPath, String MsAccessPwd)
{
DataView oDV = null;
DataRowView oDVRow;
string sDB_Prefix;
string sQry = "";
DIConnection DBConnection;
int i;
int j;
int iIndicatorNId = 0;
int iUnitNId = 0;
int[] iSubgroupNId = new int[this.DESheetInformation.Subgroup.Length];
int[] iIUSNId = new int[this.DESheetInformation.Subgroup.Length];
int iSectorNID = 0;
//int[] iClassNId = new int[Strings.Split(this.DESheetInformation.ClassGUID, "{}").Length];
int[] iClassNId = new int[this.DESheetInformation.ClassGUID.Split("{}".ToCharArray()).Length];
int iParentNId;
//Used for Class and Source
bool bNewIUS = false;
Boolean bNewSector = false;
bool bNewClass = false;
//*** True if any of I / U / S is new
bool SectorGlobal = false;
// -- Create New DiConnection with db as sDestDBPath
DBConnection = new DIConnection(DIServerType.MsAccess, "", "", sDestDBPath, "", MsAccessPwd);
//--- Using DAL for getting Database default Prefix
sDB_Prefix = DBConnection.DIDataSetDefault();
String sLng_Suffix = DBConnection.DILanguageCodeDefault(sDB_Prefix);
DIQueries DBQueries = new DIQueries(sDB_Prefix, DBConnection.DILanguageCodeDefault(sDB_Prefix));
// Get SectorNId
iSectorNID = GetSectorNId(DBConnection, DBQueries, out bNewSector, out bNewSector);
//*** If Selected Class has not been inserted by export routine then insert new Class
string[] sClassGuid;
string[] sClassName;
string[] sClassGlobal;
iParentNId = iSectorNID;
//sClassGuid = Strings.Split(this.DESheetInformation.ClassGUID,"{}");
sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray());
sClassName = this.DESheetInformation.ClassName.Split("{}".ToCharArray());
sClassGlobal = this.DESheetInformation.ClassGlobal.Split("{}".ToCharArray());
//*** BugFix 29 Aug 2006 Do not try to insert class into database if indicator is directly associated with sector
if (this.DESheetInformation.ClassGUID != "" & this.DESheetInformation.ClassName != "")
{
for (i = 0; i <= sClassGuid.Length - 1; i++)
{
iClassNId[i] = GetClassNId(DBConnection, DBQueries, out bNewClass, sClassGuid[i].ToString(), SectorGlobal, iParentNId, sClassName[i].ToString());
iParentNId = Convert.ToInt32(iClassNId[i]);
}
}
//*** Indicator_NId
sQry = DBQueries.Indicators.GetIndicator(FilterFieldType.GId, "'" + this.DESheetInformation.IndicatorGUID + "'", FieldSelection.NId);
iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iIndicatorNId == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DI_LibDAL.Queries.Indicator.Insert.InsertIndicator(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Indicator.Replace("'", "''"), this.DESheetInformation.IndicatorGUID, "", false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Unit_NId
//-- Using DAL for getting and executing query for getting unit NId
sQry = DBQueries.Calculates.GetUnitNIdByGId(this.DESheetInformation.UnitGUID);
iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iUnitNId == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DI_LibDAL.Queries.Unit.Insert.InsertUnit(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Unit.Replace("'", "''"), this.DESheetInformation.UnitGUID, false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Subgroup_Val_NId
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
sQry = DBQueries.Calculates.GetSubgroupValNIdByGId(this.DESheetInformation.SubgroupGUID[i]);
iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSubgroupNId[i] == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DBQueries.Calculates.InsertSubgroupVal(this.DESheetInformation.Subgroup[0].Replace("'", "''"), this.DESheetInformation.SubgroupGUID[0], false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
}
//*** IUSNId
if (bNewIUS)
{
//*** Insert New IUS
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
//-- Get query from DAL
sQry = DBQueries.Calculates.InsertNIdsInIUSTable(iIndicatorNId, iUnitNId, iSubgroupNId[i]);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create Relationship with IndicatorClassification
string sICGuid;
sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray());
sICGuid = "'" + this.DESheetInformation.SectorGUID + "'";
for (j = 0; j <= sClassGuid.Length - 1; j++)
{
sICGuid += ",'" + sClassGuid[j].ToString() + "'";
}
sQry = DBQueries.Calculates.GetICNIdByGId(sICGuid);
//-- Get Dataview using DAL
oDV = DBConnection.ExecuteDataTable(sQry).DefaultView;
foreach (DataRowView oDVRow1 in oDV)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(Convert.ToInt32(oDVRow1["IC_NId"]), iIUSNId[i]);
DBConnection.ExecuteNonQuery(sQry);
}
}
}
else if (bNewSector || bNewClass)
{
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
sQry = DBQueries.IUS.GetIUSByI_U_S(iIndicatorNId.ToString(), iUnitNId.ToString(), iSubgroupNId[i].ToString());
iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (bNewSector)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSectorNID, iIUSNId[i]);
DBConnection.ExecuteNonQuery(sQry);
}
if (bNewClass)
{
for (j = 0; j <= iClassNId.Length - 1; j++)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iClassNId[j], iIUSNId[i]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
}
}
}
else
{
int Index = 0;
foreach (int SubgroupNId in iSubgroupNId)
{
//-- Using DAL Query for getting IUSNId
sQry = DBQueries.Calculates.GetIUSNIdByIUS(iIndicatorNId, iUnitNId, SubgroupNId);
iIUSNId[Index] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
Index += 1;
}
}
string sTimePeriod = string.Empty;
int iTimePeriod_NId = 0;
Hashtable oTimePeriod = new Hashtable();
string sAreaId = string.Empty;
int iArea_NId = 0;
Hashtable oAreaId = new Hashtable();
string sDataValue = string.Empty;
System.DateTime dtStartDate = System.DateTime.Now;
System.DateTime dtEndDate = System.DateTime.Now;
string sSubgroup = string.Empty;
string sSource = string.Empty;
int iSource_NId = 0;
Hashtable oSource = new Hashtable();
string sFootNote = "";
int iFootNote_NId = -1;
Hashtable oFootNote = new Hashtable();
string sData_Denominator = "";
IWorksheet DataSheet;
DIDatabase DIdatabase = null;
this.DIExcel = new DIExcel(tempDESheetPath);
// this.DIExcel = new DIExcel(this.TempLogFilePath);
DataSheet = DIExcel.GetWorksheet(0);
{
//For each record in excel sheet '*** Get TimePeriod_NId, Area_NId, Data Value, Subgroup, SourceNId
for (i = 10; i <= miCtrFillTo; i++)
{
try
{
if (DataSheet.Cells[i, 0].Value.ToString() == "" || DataSheet.Cells[i, 1].Value.ToString() == "" || DataSheet.Cells[i, 3].Value.ToString() == "" || DataSheet.Cells[i, 4].Value.ToString() == "" || DataSheet.Cells[i, 5].Value.ToString() == "")
{
}
//*** If TimePeriod, AreaID, DataValue,Subgroup, Source is blank leave this record
else
{
//*** TimePeriod
sTimePeriod = DataSheet.Cells[i, 0].Value.ToString();
iTimePeriod_NId = -1;
if (CheckDate(sTimePeriod))
{
//*** Check for Valid TimeFormat allowed in DE
if (oTimePeriod.ContainsKey(sTimePeriod))
{
iTimePeriod_NId = Convert.ToInt32(oTimePeriod[sTimePeriod]);
}
else
{
sQry = DBQueries.Calculates.GetTimeperiodNIdByTimePeriod(sTimePeriod);
iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iTimePeriod_NId == 0)
{
// Using DAL for getting and executing Query for inserting timeperiod
sQry = DI_LibDAL.Queries.Timeperiod.Insert.InsertTimeperiod(sDB_Prefix, sTimePeriod);
DBConnection.ExecuteNonQuery(sQry);
iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
oTimePeriod.Add(sTimePeriod, iTimePeriod_NId);
}
SetDate(sTimePeriod, ref dtStartDate, ref dtEndDate);
}
else
{
iTimePeriod_NId = -1;
}
//*** Area
sAreaId = DataSheet.Cells[i, 1].Value.ToString();
iArea_NId = -1;
if (oAreaId.ContainsKey(sAreaId))
{
iArea_NId = Convert.ToInt32(oAreaId[sAreaId]);
}
else
{
//-- Using DAL for getting AreaNId Using AreaID
sQry = DBQueries.Calculates.GetAreaNIdByAreaID(sAreaId);
iArea_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iArea_NId == 0)
{
oAreaId.Add(sAreaId, -1);
}
//*** Don't add New areas to database
else
{
oAreaId.Add(sAreaId, iArea_NId);
}
}
//*** Data value
sDataValue = DataSheet.Cells[i, 3].Value.ToString();
//*** Subgroup
sSubgroup = DataSheet.Cells[i, 4].Value.ToString();
//*** Source
sSource = DI_LibBAL.Utility.DICommon.RemoveQuotes(DataSheet.Cells[i, 5].Value.ToString());
iSource_NId = -1;
if (oSource.ContainsKey(sSource))
{
iSource_NId = Convert.ToInt32(oSource[sSource]);
}
else
{
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSource + "'", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
string[] sSourceArr;
sSourceArr = sSource.Split('_');
//Publisher_ISOCode_Year: Parent= Publisher; Child= Abbr_Year
if (sSourceArr.Length >= 2)
{
//*** Insert Parent
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSourceArr[0] + "'", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSourceArr[0], Guid.NewGuid().ToString(), false, -1, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
//'sQry = "INSERT INTO " & sDB_Prefix & "Indicator_Classifications_IUS" & " (IC_NId,IUSNId) VALUES (" & iSource_NId & "," & iIUSNId(j) & ")"
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
//*** Insert Source
// Using DAL for inserting Source
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
//' oDestDB.ExecuteNonQuery(sQry)
catch (Exception ex)
{
}
}
}
else
{
//iSource_NId = -1
if (sSource.Trim() != "")
{
//*** Insert Parent as "Global"
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, "Global", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global", Guid.NewGuid().ToString(), false, -1, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
//*** Insert Source
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global_" + sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
}
}
}
else
{
//*** If New IUS was created then Create Relationship with Source
if (bNewIUS)
{
// using DAL query for getting parentNId
sQry = DBQueries.Calculates.GetICParentNIdByICNId(iSource_NId, "SR");
iParentNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iParentNId != 0)
{
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iParentNId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
//*** database maintains unique composite key for IC_NId and IUSNId - this will prevent duplicate entry if any
}
}
}
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
}
}
oSource.Add(sSource, iSource_NId);
}
try
{
sFootNote = DataSheet.Cells[i, 6].Value.ToString();
}
catch (Exception ex)
{
sFootNote = "";
}
iFootNote_NId = -1;
if (sFootNote != "")
{
if (oFootNote.ContainsKey(sFootNote))
{
iFootNote_NId = Convert.ToInt32(oFootNote[sFootNote]);
}
else
{
sQry = DBQueries.Calculates.GetFootNoteNIdByFootNote(Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(sFootNote)));
iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iFootNote_NId == 0)
{
sQry = DI_LibDAL.Queries.Footnote.Insert.InsertFootnote(sDB_Prefix, sLng_Suffix, sFootNote, Guid.NewGuid().ToString());
DBConnection.ExecuteNonQuery(sQry);
iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
oFootNote.Add(sFootNote, iFootNote_NId);
}
}
try
{
if (Utility.DICommon.IsNumeric(DataSheet.Cells[i, 7].Value.ToString(), System.Threading.Thread.CurrentThread.CurrentCulture))
{
sData_Denominator = DataSheet.Cells[i, 7].Value.ToString() + ",";
}
else
{
sData_Denominator = "";
}
}
catch (Exception ex)
{
sData_Denominator = "";
}
if (iIUSNId[0] == -1 | iTimePeriod_NId == -1 | iArea_NId == -1 | iSource_NId == -1 | sDataValue == "")
{
}
else
{
if (this.ApplicationWizardType == WizardType.HundredMinus)
{
sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "", iFootNote_NId, iSource_NId);
DBConnection.ExecuteNonQuery(sQry);
}
else
{
sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", sData_Denominator, iFootNote_NId, iSource_NId);
DBConnection.ExecuteNonQuery(sQry);
}
}
}
}
catch (Exception ex)
{
}
}
// Update Indicator Unit subgroupNId
DIdatabase = new DIDatabase(DBConnection, DBQueries);
DIdatabase.UpdateIndicatorUnitSubgroupNIDsInData();
DBConnection.Dispose();
}
//1.Time - 2.AreaId - 3.AreaName - 4.DataValue - 5.Subgroup - 6.Source - 7.Footnote - 8.Denominator - 12.SubgroupNId
//DataSheet = null;
if ((oDV != null))
{
oDV.Dispose();
oDV = null;
}
}
/// <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>
/// Get SectorNId
/// </summary>
/// <param name="DBConnection"></param>
/// <param name="DBQueries"></param>
/// <param name="bNewSector"></param>
/// <returns></returns>
private int GetSectorNId(DIConnection DBConnection, DIQueries DBQueries, out bool bNewSector, out Boolean bSectorGlobal)
{
int RetVal = 0;
string sQry = string.Empty;
Boolean SectorGlobal = false;
Boolean NewSector = false;
try
{
sQry = DBQueries.Calculates.GetICNIdByGId("'" + this.DESheetInformation.SectorGUID + "'");
//-- Executing Query
RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (RetVal == 0)
{
NewSector = true;
if (string.IsNullOrEmpty(this.DESheetInformation.SectorGlobal))
{
}
else
{
SectorGlobal = Convert.ToBoolean(this.DESheetInformation.SectorGlobal);
}
sQry = DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(DBQueries.DataPrefix, DBQueries.LanguageCode, this.DESheetInformation.SectorName.Replace("'", "''"), this.DESheetInformation.IndicatorGUID,
SectorGlobal, -1, "", ICType.Sector);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
RetVal = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
}
catch (Exception ex)
{
RetVal = 0;
}
bSectorGlobal = SectorGlobal;
bNewSector = NewSector;
return RetVal;
}
/// <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;
}
}
private void DeleteTable(string tableName, DIConnection dbConnection)
{
try
{
dbConnection.ExecuteNonQuery(" Drop Table " + tableName);
}
catch
{
}
}
/// <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;
}
}