public static string DeleteMetadataReportByCategory(DITables tables, MetadataElementType categoryType)
{
string RetVal=string.Empty;
RetVal = "DELETE MR.* FROM " + tables.MetadataCategory + " MC INNER JOIN " + tables.MetadataReport + " MR ON MC." + DIColumns.Metadata_Category.CategoryNId + " = MR." + DIColumns.MetadataReport.CategoryNid + " where MC." + DIColumns.Metadata_Category.CategoryType + "=" + DIQueries.MetadataElementTypeText[categoryType] +";";
return RetVal;
}
public static string AddDIUser(string userName, string password, string dataPrefix, int permissionsource, int permissiontimeperiod, int permissionarea, int permissionic, string associatedic, string associatedicwithparent, string associatedarea, string associatedareawithparent, int isAdmin, DITables tablenames)
{
string RetVal = null;
StringBuilder sSql = new StringBuilder();
sSql.Append("INSERT INTO " + tablenames.DIUser + "(" + DIColumns.DIUser.DBPrefix + "," + DIColumns.DIUser.UserName + "," + DIColumns.DIUser.UserPWD + "," + DIColumns.DIUser.PermissionSource + "," + DIColumns.DIUser.PermissionTimePeriod + "," + DIColumns.DIUser.PermissionArea + "," + DIColumns.DIUser.PermissionIC + "," + DIColumns.DIUser.AssocitedIC + "," + DIColumns.DIUser.AssocitedICWithParent + "," + DIColumns.DIUser.AssociatedArea + "," + DIColumns.DIUser.AssociatedAreaWithParent + "," + DIColumns.DIUser.IsAdmin + ")");
sSql.Append(" VALUES('" + dataPrefix + "','" + DIQueries.RemoveQuotesForSqlQuery( userName) + "','" + DIQueries.RemoveQuotesForSqlQuery(password) + "'," + permissionsource + "," + permissiontimeperiod + "," + permissionarea + "," + permissionic + ",'" + associatedic + "','" + associatedicwithparent + "','" + associatedarea + "','" + associatedareawithparent + "'," + isAdmin + ")");
RetVal = sSql.ToString();
sSql = null;
return RetVal;
}
/// <summary>
/// Inserts all recommended sources' label into UT_RecommendedSources_en table from IndicatorClassification_IUS table
/// </summary>
/// <param name="tablesName"></param>
/// <returns></returns>
public static string InsertAllLabelFrmICIUS(DITables tablesName)
{
string RetVal = string.Empty;
RetVal = "INSERT INTO " + tablesName.RecommendedSources +" ("+ DIColumns.RecommendedSources.DataNId +","+ DIColumns.RecommendedSources.ICIUSLabel +") "
+ " SELECT D." + DIColumns.Data.DataNId + ", ICIUS."+ DIColumns.IndicatorClassificationsIUS.ICIUSLabel
+ " FROM " + tablesName.Data +" D,"+ tablesName.IndicatorClassificationsIUS +" ICIUS "
+ " WHERE ICIUS."+ DIColumns.IndicatorClassificationsIUS.IUSNId +"=D."+ DIColumns.Data.IUSNId
+ " AND ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId + "= D."+ DIColumns.Data.SourceNId
+ " AND ICIUS."+ DIColumns.IndicatorClassificationsIUS.ICIUSLabel +" is not NULL";
return RetVal;
}
/// <summary>
/// Returns query to add record in UserAccess Table
/// </summary>
/// <param name="userNId"></param>
/// <param name="accessTo"></param>
/// <param name="dataPrefix"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
public static string AddUserAccess(int userNId, char accessTo, string dataPrefix, DITables tableNames)
{
string RetVal = null;
StringBuilder sSql = new StringBuilder();
sSql.Append("INSERT INTO " + tableNames.DBUserAccess + "(" + DIColumns.DBUserAccess.UserNId + ", " + DIColumns.DBUserAccess.AccessTo + ", " + DIColumns.DBUserAccess.DBPrefix + ")");
sSql.Append(" VALUES(" + userNId + ",'" + accessTo + "','" + dataPrefix + "')");
RetVal = sSql.ToString();
//--dispose
sSql = null;
return RetVal;
}
/// <summary>
/// Return Sql Query for Delete records from UserAccess Table based on UserNid and DataPrefix
/// </summary>
/// <param name="userNId"></param>
/// <param name="dataPrefix"></param>
/// <param name="tablesName"></param>
/// <returns></returns>
public static string DeleteUserAccess(int userNId, string dataPrefix, DITables tablesName)
{
string RetVal = null;
StringBuilder sSql = new StringBuilder();
//DITables TablesName;
sSql.Append("DELETE FROM " + tablesName.DBUserAccess);
sSql.Append(" WHERE " + DIColumns.DBUserAccess.UserNId + "=" + userNId + " AND " + DIColumns.DBUserAccess.DBPrefix + "= '" + dataPrefix + "'");
RetVal = sSql.ToString();
//--dispose
sSql = null;
return RetVal;
}
private void AddColumnIntoAreaTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(Area.AreaShortName, tablesName.Area))
{
// Add Data_Exist Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Area.Insert.AddAreaShortNameColumn(tablesName, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Inserts missing language values into all language dependent tables.
/// </summary>
public void InsertMissingLanguageValues()
{
string LanguageCode = string.Empty;
DITables TableNames;
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
try
{
LanguageCode = Row[Language.LanguageCode].ToString();
// insert all missing language values
if (LanguageCode != this.DBConnection.DILanguageCodeDefault(this.DBQueries.DataPrefix).Replace("_", ""))
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + LanguageCode);
// INDICATOR
this.UpdateMissingTextValues(this.DBQueries.TablesName.Indicator, TableNames.Indicator);
// UNIT
this.UpdateMissingTextValues(this.DBQueries.TablesName.Unit, TableNames.Unit);
// Subgroup Vals
this.UpdateMissingTextValues(this.DBQueries.TablesName.SubgroupVals, TableNames.SubgroupVals);
// Subgroup
this.UpdateMissingTextValues(this.DBQueries.TablesName.Subgroup, TableNames.Subgroup);
// AREA
this.UpdateMissingTextValues(this.DBQueries.TablesName.Area, TableNames.Area);
// AREA_LEVEL
this.UpdateMissingTextValues(this.DBQueries.TablesName.AreaLevel, TableNames.AreaLevel);
// AREA_MAP_METADATA
this.UpdateMissingTextValues(this.DBQueries.TablesName.AreaMapMetadata, TableNames.AreaMapMetadata);
// AREA_FEATURE_TYPE
this.UpdateMissingTextValues(this.DBQueries.TablesName.AreaFeatureType, TableNames.AreaFeatureType);
// UT_Indicator_Classifications_ar
this.UpdateMissingTextValues(this.DBQueries.TablesName.IndicatorClassifications, TableNames.IndicatorClassifications);
// UT_RecommendedSources_en
this.UpdateMissingTextValues(this.DBQueries.TablesName.RecommendedSources, TableNames.RecommendedSources);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
}
public static string AddUserAccess(int userNId, string dataPrefix,int PermissionSource,int PermissionTimePeriod,int PermissionArea,int PermissionIC,int IsAdmin,string PermissionAreaDesc,string PermissionICDesc, DITables tableNames)
{
string RetVal = null;
StringBuilder sSql = new StringBuilder();
sSql.Append("INSERT INTO " + tableNames.DBUserAccess + "(" + DIColumns.DBUserAccess.UserNId + ", " + DIColumns.DBUserAccess.DBPrefix + ","+ DIColumns.DBUserAccess.PermissionSource+","+DIColumns.DBUserAccess.PermissionTimePeriod+","+DIColumns.DBUserAccess.PermissionArea+","+DIColumns.DBUserAccess.PermissionIC+","+DIColumns.DBUserAccess.IsAdmin+","+DIColumns.DBUserAccess.PermissionAreaDescription+","+DIColumns.DBUserAccess.PermissionICDescription+")");
//sSql.Append(" VALUES(" + userNId + "," + dataPrefix +","+PermissionSource+","+PermissionTimePeriod+","+PermissionArea+","+PermissionIC+","+IsAdmin+","+PermissionAreaDesc+","+PermissionICDesc+ ")");
sSql.Append(" VALUES(" + userNId + ",'" + dataPrefix + "'," + PermissionSource + ","
+ PermissionTimePeriod + "," + PermissionArea + "," + PermissionIC + ","
+ IsAdmin + ",'" + PermissionAreaDesc + "','" + PermissionICDesc + "')");
RetVal = sSql.ToString();
//--dispose
sSql = null;
return RetVal;
}
private void RenameIndicatorClassificationIUSTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(" * ", tablesName.IndicatorClassificationsIUS))
{
//-- Carate New IC_IUS table from existing table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.CreateNewICIUSTableFromExisting(this._DBQueries.DataPrefix));
//-- Delete old table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Delete.DeleteOldICIUSTable(this._DBQueries.DataPrefix));
}
}
catch (Exception ex)
{
}
}
private void UpdateDBSchema(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TablesName = this.DBQueries.TablesName;
DIServerType ServerType;
try
{
ServerType = this.DBConnection.ConnectionStringParameters.ServerType;
// step1: add columns in all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// add columns in all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// create tables name
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
// insert new columns into indicator_classification table
this.AddColumnsIntoIndicatorClassificationsTable(forOnlineDB, TablesName, ServerType);
// insert new columns into indicator table
this.AddColumnsIntoIndicatorTable(forOnlineDB, TablesName, ServerType);
// insert new column "Data_Exist" into area table
this.AddColumnIntoAreaTable(forOnlineDB, TablesName, ServerType);
}
// add columns into indicator_unit_subgroup table
this.AddColumnsIntoIUSTable(forOnlineDB, TablesName, ServerType);
// add document table
this.CreateDocumentTable(forOnlineDB, TablesName, ServerType);
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
public static string DIEditUser(int userNId, string dataPrefix, int PermissionSource, int PermissionTimePeriod, int PermissionArea, int PermissionIC, int IsAdmin, string associatedic, string associatedicwithdesc, string associatedarea, string associatedareadesc, DITables tableNames)
{
string RetVal = null;
StringBuilder sSql = new StringBuilder();
sSql.Append("UPDATE " + tableNames.DIUser);
sSql.Append(" Set "+ DIColumns.DIUser.PermissionSource + "=" + PermissionSource + "," + DIColumns.DIUser.PermissionTimePeriod + "=" + PermissionTimePeriod + "," + DIColumns.DIUser.PermissionArea + "=" + PermissionArea + "," + DIColumns.DIUser.PermissionIC + "=" + PermissionIC + "," + DIColumns.DIUser.AssocitedIC + "='" + associatedic + "'," + DIColumns.DIUser.AssocitedICWithParent + "= '" + associatedicwithdesc + "'," + DIColumns.DIUser.AssociatedArea + "= '" + associatedarea+"',"+DIColumns.DIUser.AssociatedAreaWithParent+"= '"+associatedareadesc+"',"+DIColumns.DIUser.IsAdmin+"="+IsAdmin);
sSql.Append(" WHERE " + DIColumns.DIUser.UserNId + "=" + userNId);
sSql.Append(" AND " + DIColumns.DIUser.DBPrefix + " = '" + dataPrefix + "'");
RetVal = sSql.ToString();
//--dispose
sSql = null;
return RetVal;
}
private void AddColumnsIntoIndicatorClassificationsTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// Add IC_short_name Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.ICShortName, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertICShortNameColumn(tablesName, forOnlineDB, serverType));
}
// Add publisher Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.Publisher, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertPublisherColumn(tablesName, forOnlineDB, serverType));
}
// Add title Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.Title, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertTitleColumn(tablesName, forOnlineDB, serverType));
}
// Add DIYear Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.DIYear, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertDIYearColumn(tablesName, forOnlineDB, serverType));
}
// Add SourceLink1 Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.SourceLink1, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertSourceLink1Column(tablesName, forOnlineDB, serverType));
}
// Add SourceLink2 Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.SourceLink2, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertSourceLink2Column(tablesName, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void AddColumnsIntoIUSTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// add subgroup_nids column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertSubgroupNidsColumn(tablesName, forOnlineDB, serverType));
////// add subgroup_type_nids column
////this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertSubgroupTypeNidsColumn(tablesName, forOnlineDB, serverType));
// add data_exist column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDataExistColumn(tablesName, forOnlineDB, serverType));
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void CreateDBMetaTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
DBMetadataTableBuilder DBMetadataTblBuilder;
DIQueries TempQueries;
try
{
// create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// create table for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
DBMetadataTblBuilder = new DBMetadataTableBuilder(this._DBConnection, TempQueries);
if (DBMetadataTblBuilder.IsDBMetadataTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.CreateTable(TableNames.DBMetadata,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
}
}
// insert blank records with counts
// reset DBMetadata builder with main DIQuerie's object
DBMetadataTblBuilder = new DBMetadataTableBuilder(this._DBConnection, this._DBQueries);
DBMetadataTblBuilder.InsertRecord(string.Empty, string.Empty, string.Empty, string.Empty,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
DBMetadataTblBuilder.GetNUpdateCounts();
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void CreateSubgroupTypeTable(bool forOnlineDB)
{
DITables TableNames;
try
{
//-- Get all Languages from database
foreach (DataRow Row in this._DBConnection.DILanguages(this._DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this._DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
this._DBConnection.ExecuteNonQuery(DALQueries.SubgroupTypes.Insert.CreateTable(forOnlineDB, TableNames.SubgroupType, this._DBConnection.ConnectionStringParameters.ServerType));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void AddColumnsIntoMetadataCategoryTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(Metadata_Category.ParentCategoryNId, tablesName.MetadataCategory))
{
// check and create metadata category table
this.CheckNCreateMetadataCategoryTable(forOnlineDB, tablesName);
// Add ParentCategoryNId Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertParentCategoryNIdColumn(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Metadata_Category.CategoryGId, tablesName.MetadataCategory))
{
// Add CategoryGId Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertCategoryGIdColumn(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Metadata_Category.CategoryDescription, tablesName.MetadataCategory))
{
// Add CategoryDescription Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertCategoryDescriptionColumn(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Metadata_Category.IsPresentational, tablesName.MetadataCategory))
{
// Add IsPresentational Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertIsPresentationalColumn(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Metadata_Category.IsMandatory, tablesName.MetadataCategory))
{
// Add IsMandatory Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertIsMandatoryColumn(tablesName, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Update Recommended Source Label By DataNid
/// </summary>
/// <param name="dataNId"></param>
/// <param name="icIUSLabel"></param>
/// <returns></returns>
public bool UpdateRecommendedSources(int dataNId, string icIUSLabel)
{
bool RetVal = false;
string SqlQuery = string.Empty;
DITables tables = new DITables(this.DBQueries.DataPrefix, this.DBQueries.LanguageCode);
try
{
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Update.UpdateRecommendedSourceValue(tables.RecommendedSources, dataNId, DICommon.RemoveQuotes(icIUSLabel));
this.DBConnection.ExecuteNonQuery(SqlQuery);
RetVal = true;
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Deletes MetadataCategory
/// </summary>
/// <param name="indicatorNids"></param>
public void DeleteMetadataCategory(string metadataCategoryNids)
{
DITables TableNames;
try
{
// Step 1: Delete records from Indicator table for each Language
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
// Get Language Table Name for Indicator
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
// Delete MetadataCategory
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Delete.DeleteMetadataCategory(TableNames.MetadataCategory, metadataCategoryNids));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Insert Notes Classification by NotesClassificationInfo
/// </summary>
/// <param name="notesClassInfo">NotesClassificationInfo Object</param>
/// <returns >Return Inserted Notes Nid</returns>
public int InsertNotesClassification(NotesClassificationInfo notesClassInfo)
{
int RetVal = 0;
DITables TableNames;
string LanguageCode = string.Empty;
string DefaultLanguageCode = string.Empty;
string ClassName = string.Empty;
string SqlQuery = string.Empty;
try
{
DefaultLanguageCode = this.DBQueries.LanguageCode;
//-- Insert Classification for Each Language
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = Convert.ToString(Row[Language.LanguageCode]);
if (LanguageCode == DefaultLanguageCode.Replace("_", String.Empty))
{
ClassName = notesClassInfo.Classification_Name;
}
else
{
ClassName = Constants.PrefixForNewValue + notesClassInfo.Classification_Name;
}
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + LanguageCode);
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Notes.Insert.InsertNotesClassification(TableNames.NotesClassification, ClassName);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
RetVal = this.DBConnection.GetNewId();
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
/// <summary>
/// Updates count information
/// </summary>
/// <param name="NID"></param>
/// <param name="areaCount"></param>
/// <param name="indicatorCount"></param>
/// <param name="IUSCount"></param>
/// <param name="timeperiodCount"></param>
/// <param name="sourceCount"></param>
/// <param name="dataCount"></param>
public void UpdateCounts(int NID,
string areaCount, string indicatorCount, string IUSCount, string timeperiodCount, string sourceCount, string dataCount)
{
string PublishedOn = System.DateTime.Now.ToString("yyyy-MM-dd");
string SqlQuery = string.Empty;
DITables TablesName;
string DataPrefix = this.DBConnection.DIDataSetDefault();
bool UseUpdateQuery = true;
try
{
// check record exists in DB_Metadata table or not
if (Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(this.DBQueries.TablesName.DBMetadata, string.Empty))) == 0)
{
UseUpdateQuery = false;
}
foreach (DataRow Row in this.DBConnection.DILanguages(DataPrefix).Rows)
{
TablesName = new DITables(DataPrefix, "_" + Row[Language.LanguageCode].ToString());
if (UseUpdateQuery)
{
// update query
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Update.UpdateCounts(TablesName.DBMetadata, NID,
areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
}
else
{
//insert query
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.InsertRecord(TablesName.DBMetadata, string.Empty, string.Empty, PublishedOn, string.Empty, string.Empty, string.Empty,
areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
}
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private int InsertSubgroupType(string subgroupTypeName, string subgroupTypeGID, int order, bool isGlobal)
{
int RetVal = 0;
DITables TableNames;
string SubgroupTypeNameForDatabase = string.Empty;
string SubgroupTypeGID = Guid.NewGuid().ToString();
try
{
// if the given gid is not null then use it.
if (!string.IsNullOrEmpty(subgroupTypeGID))
{
SubgroupTypeGID = subgroupTypeGID;
}
//-- Get all Languages from database and insert subgroup type
foreach (DataRow Row in this._DBConnection.DILanguages(this._DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this._DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
if ("_" + Row[Language.LanguageCode].ToString().ToUpper() == this._DBQueries.LanguageCode.ToUpper())
{
SubgroupTypeNameForDatabase = subgroupTypeName;
}
else
{
SubgroupTypeNameForDatabase = Constants.PrefixForNewValue + subgroupTypeName;
}
this._DBConnection.ExecuteNonQuery(DALQueries.SubgroupTypes.Insert.InsertSubgroupType(TableNames.SubgroupType, SubgroupTypeNameForDatabase, SubgroupTypeGID, order, isGlobal));
RetVal = this._DBConnection.ExecuteNonQuery("SELECT @@IDENTITY");
}
}
catch (Exception ex)
{
RetVal = 0;
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
private bool UpdateDBSchema(bool forOnlineDB)
{
bool RetVal = false;
DITables TablesName = this.DBQueries.TablesName;
string dataType = string.Empty;
try
{
//Step 1 Merge datavalues into single column data_value(memo)
this.RaiseProcessInfoEvent(3);
this.MergeDataValueIntoSingleColumn();
//Step 2 Convert DI7 metadata into DI6 metadata [Info] column update
this.RaiseProcessInfoEvent(4);
this.ConvertDI7MetadataIntoDI6Metadata();
//Step 3 Remove extra columns and table [ut_sdmx ,ut_metadata report]
this.RaiseProcessInfoEvent(5);
this.RemoveExtraColumnsNTable();
//Step 4 Update xslt into ut_xslt add BAL resource XSLT_DI6
this.RaiseProcessInfoEvent(6);
this.UpdateXsltIntoXsltTable();
//Step 5 Rename icius table into ut_Indicator_Classifications_IUS
this.RaiseProcessInfoEvent(7);
this.RenameIndicatorClassificationIUSTable(false, TablesName, DIServerType.MsAccess);
//Step 5 Compact database but don't call SeparateTextualandNemericData method
this.RaiseProcessInfoEvent(8);
// this.CompactDatabase();
RetVal = true;
}
catch (Exception ex)
{
throw 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());
}
}
public void UpdateDataNIdDataType(bool forOnilneDB)
{
DITables TableNames;
try
{
// Step1: update data type of data_nid column
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
// Get table name
TableNames = new DITables(this.DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
// update datatype
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Update.UpdateTypeofDataNIdColumn(TableNames.RecommendedSources, forOnilneDB, this.DBConnection.ConnectionStringParameters.ServerType));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void AddColumnsIntoIndicatorClassificationsTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(IndicatorClassifications.ISBN, tablesName.IndicatorClassifications))
{
// Add ISBN Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertDI7ISBNColumns(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(IndicatorClassifications.Nature, tablesName.IndicatorClassifications))
{
// Add Nature Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertDI7NatureColumns(tablesName, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void DeleteSubgroupValColumns()
{
DITables TableNames;
try
{
//-- Get all Languages from database and delete Age,Sex,Location and others columns
foreach (DataRow Row in this._DBConnection.DILanguages(this._DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this._DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
this._DBConnection.ExecuteNonQuery(DALQueries.Subgroup.Delete.DeleteColumnForDI6(TableNames.SubgroupVals, SubgroupType.Age));
this._DBConnection.ExecuteNonQuery(DALQueries.Subgroup.Delete.DeleteColumnForDI6(TableNames.SubgroupVals, SubgroupType.Location));
this._DBConnection.ExecuteNonQuery(DALQueries.Subgroup.Delete.DeleteColumnForDI6(TableNames.SubgroupVals, SubgroupType.Others));
this._DBConnection.ExecuteNonQuery(DALQueries.Subgroup.Delete.DeleteColumnForDI6(TableNames.SubgroupVals, SubgroupType.Sex));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Deletes extra rows from RecommendSources table
/// </summary>
public void DeleteExtraRowsFrmRecommendedSources()
{
DITables TableNames;
try
{
// Step1: Delete records from RecommendedSources table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
// Get table name
TableNames = new DITables(this.DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
// delete records
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Delete.DeleteExtraRecords(TableNames.RecommendedSources, TableNames.Data));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Insert RecommendedSource record into database
/// </summary>
/// <param name="dataNId"></param>
/// <param name="icIUSLabel"></param>
/// <returns>Ture/False. Return true after successful insertion otherwise false</returns>
public bool InsertIntoDatabase(int dataNId, string ICIUSLabel)
{
bool RetVal = false;
DITables TablesName;
string LanguageCode = string.Empty;
string DefaultLanguageCode = string.Empty;
string ICIUSLabelForDatabase = string.Empty;
try
{
DefaultLanguageCode = this.DBQueries.LanguageCode;
// insert IC_IUS_label in all language tables
foreach (DataRow languageRow in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = languageRow[Language.LanguageCode].ToString();
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
if (LanguageCode == DefaultLanguageCode.Replace("_", String.Empty))
{
ICIUSLabelForDatabase = ICIUSLabel;
}
else
{
ICIUSLabelForDatabase = Constants.PrefixForNewValue + ICIUSLabel;
}
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Insert.InsertRecommendedSource(TablesName.RecommendedSources, dataNId, ICIUSLabelForDatabase));
}
RetVal = true;
}
catch (Exception)
{
RetVal = false;
}
return(RetVal);
}
public void DeleteMetadataReports(MetadataElementType categoryType)
{
DITables TableNames;
try
{
// Step1: Delete records from metadata Report table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
// Get table name
TableNames = new DITables(this.DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
// delete records
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataReport.Delete.DeleteMetadataReportByCategory(TableNames, categoryType));
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void AddColumnsIntoIUSTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(Indicator_Unit_Subgroup.IsDefaultSubgroup, tablesName.IndicatorUnitSubgroup))
{
// add ISDefaultsubgroup column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7IsDefaultSubgroupColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Indicator_Unit_Subgroup.AvlMinDataValue, tablesName.IndicatorUnitSubgroup))
{
// add AvlMinDataValue column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7AvlMinDataValueColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Indicator_Unit_Subgroup.AvlMaxDataValue, tablesName.IndicatorUnitSubgroup))
{
// Add AvlMaxDataValue Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7AvlMaxDataValueColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Indicator_Unit_Subgroup.AvlMinTimePeriod, tablesName.IndicatorUnitSubgroup))
{
// Add AvlMinTimePeriod Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7AvlMinTimePeriodColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Indicator_Unit_Subgroup.AvlMaxTimePeriod, tablesName.IndicatorUnitSubgroup))
{
// Add AvlMaxTimePeriod Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7AvlMaxTimePeriodColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void CreateMetadatCategoryTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
MetadataCategoryBuilder MetadataCategoryBuilderObj = null;
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);
MetadataCategoryBuilderObj = new MetadataCategoryBuilder(this._DBConnection, TempQueries);
if (MetadataCategoryBuilderObj.IsMetadataCategoryTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.CreateTable(TableNames.MetadataCategory,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Deletes sources from Indicator_Classification table and associated records from IC_IUS table
/// </summary>
/// <param name="nids">Comma separated nids which may be blank</param>
public void DeleteSources(string nids)
{
DITables TableNames;
IndicatorClassificationBuilder ICBuilder;
MetaDataBuilder MetadataBuilderObject;
if (!string.IsNullOrEmpty(nids))
{
try
{
// Step1: Delete records from IndicatorClassification table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Delete.DeleteSources(TableNames.IndicatorClassifications, nids));
}
if (!string.IsNullOrEmpty(nids))
{
// Step2: Delete records from IC_IUS table
ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries);
ICBuilder.DeleteClassificationIUSRelation(nids, string.Empty);
// delete records data table
//this.DBConnection.ExecuteNonQuery(this.DBQueries.Delete.Data.DeleteRecordsBySourceNIds(nids));
new DIDatabase(this.DBConnection, this.DBQueries).DeleteDataValue(string.Empty, string.Empty, string.Empty, nids);
// delete metadata
MetadataBuilderObject = new MetaDataBuilder(this.DBConnection, this.DBQueries);
MetadataBuilderObject.DeleteMetadata(nids, MetadataElementType.Source);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
}
private bool UpdateDBSchema(bool forOnlineDB)
{
bool RetVal = false;
DITables TablesName = this.DBQueries.TablesName;
string dataType = string.Empty;
try
{
//1) For timeperiod update column periodicity column datatype from Number to Text.
if (forOnlineDB)
{
dataType = "nvarchar(255)";
}
else
{
dataType = "Text(50)";
}
this.AlterColumn(Timeperiods.Periodicity, TablesName.TimePeriod, dataType);
this.RaiseProcessInfoEvent(1);
////2) For Data table update column data_value column datatype from Text to Number.
//dataType = "int";
//this.AlterColumn(Data.DataValue, TablesName.Data, dataType);
this.RaiseProcessInfoEvent(2);
//3) Check for table Indicator_Classifications_IUS then update name as ic_ius
this.RenameIndicatorClassificationIUSTable(forOnlineDB, TablesName, this._DBConnection.ConnectionStringParameters.ServerType);
this.RaiseProcessInfoEvent(3);
RetVal = true;
}
catch (Exception ex)
{
throw ex;
}
return(RetVal);
}
/// <summary>
/// Returns qurey to insert AreaShortName column into UT_Area_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string AddAreaShortNameColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.Area + " ADD COLUMN " + DIColumns.Area.AreaShortName + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " varchar(255) ";
}
else
{
RetVal += " nvarchar(255) ";
}
}
else
{
RetVal += " Text(255) ";
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert HighIsGood column into UT_Indicator_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertHighIsGoodColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.Indicator + " ADD COLUMN " + DIColumns.Indicator.HighIsGood + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " TinyInt(1) ";
}
else
{
RetVal += " Bit ";
}
}
else
{
RetVal += " Bit ";
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert Data_Exist column into UT_Indicator_Unit_Subgroup table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertDataExistColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.IndicatorUnitSubgroup + " ADD COLUMN " + DIColumns.Indicator_Unit_Subgroup.DataExist + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " TinyInt(1) ";
}
else
{
RetVal += " Bit ";
}
}
else
{
RetVal += " Bit ";
}
return RetVal;
}
/// <summary>
/// Deletes indicators and associated records from IUS and IC_IUS table
/// </summary>
/// <param name="indicatorNids"></param>
public void DeleteIndicator(string indicatorNids)
{
DITables TableNames;
IUSBuilder IUSBuilder;
MetaDataBuilder MetadataBuilderObject;
string AssocicatedIUSNIds = string.Empty;
try
{
IUSBuilder = new IUSBuilder(this.DBConnection, this.DBQueries);
// Step 1: Delete records from Indicator table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Delete.DeleteIndicator(TableNames.Indicator, indicatorNids));
}
// Step 2: Delete records from IUS table
// Step2(a): Get all associated IUSNIds
AssocicatedIUSNIds = IUSBuilder.GetAllAssociatedIUSNids(indicatorNids, string.Empty, string.Empty);
// Step2(b): Delete all associated IUSNIds
IUSBuilder.DeleteIUS(AssocicatedIUSNIds);
// delete metadata
MetadataBuilderObject = new MetaDataBuilder(this.DBConnection, this.DBQueries);
MetadataBuilderObject.DeleteMetadata(indicatorNids, MetadataElementType.Indicator);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
public void ReadXmlAndUpdateCategoryTable(string metadataXmlInfo, string metadataTypeText)
{
int CategoryOrder = 0;
XmlDocument XmlDoc = new XmlDocument();
XmlNodeList RootNodeList;
string DefaultCategoryLanguageValue = string.Empty;
MetadataCategoryInfo MetadataInfo;
string CategoryForDatabase = string.Empty;
DITables Tables = null;
DIQueries TempDBQueries = null;
if (!string.IsNullOrEmpty(metadataXmlInfo.Trim()))
{
XmlDoc.LoadXml(metadataXmlInfo);
// Get "Metadata" Root Element NodeList
RootNodeList = XmlDoc.SelectNodes(Constants.MetadataCategory.MetadataCategoryNodePath);
// Get first recrod from "metadata/Category" Node and insert into all available Metadata_Category language table
// Check Category In each "metadata/Category" Node
for (int Index = 0; Index < RootNodeList.Count; Index++)//reach (XmlElement InputNodeList in RootNodeList.Count)
{
MetadataInfo = new MetadataCategoryInfo();
// Set MetadataCategoryInfo Value
MetadataInfo.CategoryName = DICommon.RemoveQuotes(Convert.ToString(RootNodeList[Index].Attributes["name"].Value));
//-- Get Max Category Order
CategoryOrder = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(this.DBQueries.Metadata_Category.GetMaxMetadataCategoryOrder(metadataTypeText)));
MetadataInfo.CategoryOrder = CategoryOrder;
MetadataInfo.CategoryType = metadataTypeText;
// Add MetadataCategory Into all metdata category language tables
this.CheckNCreateMetadataCategory(MetadataInfo);
}
}
}
/// <summary>
/// Returns qurey to insert DataValue column into UT_Area_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string AlterDataValueColumnDataTypeToDouble(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.Data + " Add COLUMN " + DIColumns.Data.DataValue + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " Decimal(18,5) ";
}
else
{
RetVal += " Decimal(18,5) ";
}
}
else
{
RetVal += " Double ";
}
return RetVal;
}
private void AddColumnsIntoIndicatorTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// Add short_name Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertShortNameColumn(tablesName, forOnlineDB, serverType));
// Add keywords Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertKeywordsColumn(tablesName, forOnlineDB, serverType));
// Add indicator_order Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertIndicatorOrderColumn(tablesName, forOnlineDB, serverType));
// Add Data exist Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertDataExistColumn(tablesName, forOnlineDB, serverType));
// Add HighIsGood Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertHighIsGoodColumn(tablesName, forOnlineDB, serverType));
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void DeleteNewLanguageBasedTable()
{
// get data prefix
string DataPrefix = this.DBConnection.DIDataSetDefault();
// For Language Code
string LanguageCode = string.Empty;
DITables TableNames = null;
foreach (DataRow LanguageRow in this.DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// Getting Table With Language Code
TableNames = new DITables(DataPrefix, LanguageCode);
// delete DBMetadata table
this.DBConnection.DropTable(TableNames.DBMetadata);
// delete recommendedSources table
this.DBConnection.DropTable(TableNames.RecommendedSources);
}
}
/// <summary>
/// Add column CategoryDescription
/// </summary>
/// <param name="tablesName"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertCategoryDescriptionColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.MetadataCategory + " ADD COLUMN " + DIColumns.Metadata_Category.CategoryDescription + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " varchar(4000) ";
}
else
{
RetVal += " nvarchar(4000) ";
}
}
else
{
RetVal += " Memo ";
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert ParentCategoryNId column into MetadataCategory table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertParentCategoryNIdColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.MetadataCategory + " ADD COLUMN " + DIColumns.Metadata_Category.ParentCategoryNId + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " int(4) ";
}
else
{
RetVal += " int ";
}
}
else
{
RetVal += " number ";
}
return RetVal;
}
/// <summary>
/// Returns sql query to update dataexist to true but where data exists
/// </summary>
/// <param name="serverType"></param>
/// <param name="tablesName"></param>
/// <returns></returns>
public static string UpdateDataExistValues(DIServerType serverType, DITables tablesName)
{
string RetVal = string.Empty;
switch (serverType)
{
case DIServerType.SqlServer:
break;
case DIServerType.MsAccess:
RetVal = " UPDATE " + tablesName.Indicator + " AS I SET I." + DIColumns.Indicator.DataExist + "=True where Exists ( select * from " + tablesName.Data + " D where I." + DIColumns.Indicator.IndicatorNId + "=D." + DIColumns.Data.IndicatorNId + ") ";
break;
case DIServerType.Oracle:
break;
case DIServerType.MySql:
break;
case DIServerType.SqlServerExpress:
break;
case DIServerType.Excel:
break;
default:
break;
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert ISBN column into UT_Indicator_Classifications_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertDI7ISBNColumns(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.IndicatorClassifications + " ADD COLUMN " + DIColumns.IndicatorClassifications.ISBN + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " varchar(255) ";
}
else
{
RetVal += " nvarchar(255) ";
}
}
else
{
RetVal += " Text(255) ";
}
return RetVal;
}
public Select(DITables tablesName)
{
this.TablesName = tablesName;
}
/// <summary>
/// Get Nid and Name where two language table have different Gid with same Nid
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="langCode"></param>
/// <returns></returns>
public string GetUnmatchedGidForLanguage(string dataPrefix, string langCode)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
DITables table = new DITables(dataPrefix, langCode);
sbQuery.Append("SELECT IC." + DIColumns.IndicatorClassifications.ICNId + " AS " + DIColumns.IndicatorClassifications.ICNId + ",IC." + DIColumns.IndicatorClassifications.ICName + " AS " + DIColumns.IndicatorClassifications.ICName + ",IC." + DIColumns.IndicatorClassifications.ICGId + " AS " + DIColumns.IndicatorClassifications.ICGId);
sbQuery.Append(",IC1." + DIColumns.IndicatorClassifications.ICNId + ",IC1." + DIColumns.IndicatorClassifications.ICName + ",IC1." + DIColumns.IndicatorClassifications.ICGId + " FROM " + this.TablesName.IndicatorClassifications + " IC," + table.IndicatorClassifications + " IC1 ");
sbQuery.Append(" WHERE ");
sbQuery.Append(" IC." + DIColumns.IndicatorClassifications.ICNId + "= IC1." + DIColumns.IndicatorClassifications.ICNId);
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICGId + "<> IC1." + DIColumns.IndicatorClassifications.ICGId);
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns sql query to update the data exists values in the given language form the default area language table
/// </summary>
/// <param name="targetLanguageTablesName"></param>
/// <returns></returns>
public string UpdateDataExistValuesInOtherLangauge(DITables targetLanguageTablesName)
{
string RetVal = string.Empty;
RetVal = "UPDATE " + this.TablesName.Area + " AS A INNER JOIN " + targetLanguageTablesName.Area + " AS A1 ON A." + DIColumns.Area.AreaNId + " = A1." + DIColumns.Area.AreaNId + " SET A1." + DIColumns.Area.DataExist + " = A." + DIColumns.Area.DataExist + " ";
return RetVal;
}
/// <summary>
/// Returns qurey to insert Title column into UT_Indicator_Classifications_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertTitleColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.IndicatorClassifications + " ADD COLUMN " + DIColumns.IndicatorClassifications.Title + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " LongText ";
}
else
{
RetVal += " nvarchar(4000) ";
}
}
else
{
RetVal += " Memo ";
}
return RetVal;
}
/// <summary>
/// Get Nid and Name where two language table have different Gid with same Nid
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="langCode"></param>
/// <returns></returns>
public string GetUnmatchedGidForLanguage(string dataPrefix, string langCode)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
DITables table = new DITables(dataPrefix, langCode);
sbQuery.Append("SELECT U." + DIColumns.Unit.UnitNId + " AS " + DIColumns.Unit.UnitNId + ",U." + DIColumns.Unit.UnitName + " AS " + DIColumns.Unit.UnitName + ",U." + DIColumns.Unit.UnitGId + " AS " + DIColumns.Unit.UnitGId);
sbQuery.Append(", U1." + DIColumns.Unit.UnitNId + ",U1." + DIColumns.Unit.UnitName + ",U1." + DIColumns.Unit.UnitGId);
sbQuery.Append(" FROM " + this.TablesName.Unit + " U," + table.Unit + " U1 ");
sbQuery.Append(" WHERE ");
sbQuery.Append(" U." + DIColumns.Unit.UnitNId + "= U1." + DIColumns.Unit.UnitNId);
sbQuery.Append(" AND U." + DIColumns.Unit.UnitGId + "<> U1." + DIColumns.Unit.UnitGId);
RetVal = sbQuery.ToString();
return RetVal;
}
public Delete(DITables tablesName)
{
this.TablesName = tablesName;
}
/// <summary>
/// Return Sql query for Update records in User Access Table for the given UserNID,AccessTo and Data_Prefix
/// </summary>
/// <param name="iUserNId"></param>
/// <param name="cAccessTo"></param>
/// <param name="sDataPrefix"></param>
/// <param name="TablesName"></param>
/// <returns></returns>
public static string EditUserAccess(int userNId, char accessTo, string dataPrefix, DITables tablesName)
{
string RetVal = null;
StringBuilder sSql = new StringBuilder();
sSql.Append("UPDATE " + tablesName.DBUserAccess);
sSql.Append(" Set " + DIColumns.DBUserAccess.AccessTo + "='" + accessTo + "'");
sSql.Append(" WHERE " + DIColumns.DBUserAccess.UserNId + "=" + userNId);
sSql.Append(" AND " + DIColumns.DBUserAccess.DBPrefix + " = '" + dataPrefix + "'");
RetVal = sSql.ToString();
//--dispose
sSql = null;
return RetVal;
}
public Update(DITables tablesName)
{
this.TablesName = tablesName;
}
internal Select(DITables tablesName)
{
this.TablesName = tablesName;
}
/// <summary>
/// Get Nid and Name where two language table have different Gid with same Nid
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="langCode"></param>
/// <returns></returns>
public string GetUnmatchedGidForLanguage(string dataPrefix, string langCode)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
DITables LangTable = new DITables(dataPrefix, langCode);
sbQuery.Append("SELECT A." + DIColumns.Area.AreaNId + " AS " + DIColumns.Area.AreaNId + ",A." + DIColumns.Area.AreaID + " AS " + DIColumns.Area.AreaID + ",A." + DIColumns.Area.AreaGId + " AS " + DIColumns.Area.AreaGId);
sbQuery.Append(",A1." + DIColumns.Area.AreaNId + ",A1." + DIColumns.Area.AreaID + ",A1." + DIColumns.Area.AreaGId);
sbQuery.Append (" FROM " + this.TablesName.Area + " A," + LangTable.Area + " A1 ");
sbQuery.Append(" WHERE ");
sbQuery.Append(" A." + DIColumns.Area.AreaID + "= A1." + DIColumns.Area.AreaID);
sbQuery.Append(" AND A." + DIColumns.Area.AreaGId + "<> A1." + DIColumns.Area.AreaGId);
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns query to create DI_User table into sql server
/// </summary>
/// <returns></returns>
public static string CreateTable()
{
string RetVal = null;
StringBuilder SqlQuery = new StringBuilder();
DITables Tables = new DITables(string.Empty, string.Empty);
SqlQuery.AppendLine("CREATE TABLE " + Tables.DIUser +" (");
SqlQuery.AppendLine(DIColumns.DIUser.UserNId + " Decimal(18,0) IDENTITY(1,1) NOT NULL ,");
SqlQuery.AppendLine(DIColumns.DIUser.DBPrefix + " Char(9) ,");
SqlQuery.AppendLine(DIColumns.DIUser.UserName + " Char(50) ,");
SqlQuery.AppendLine(DIColumns.DIUser.UserPWD + " Char(50) ,");
SqlQuery.AppendLine(DIColumns.DIUser.PermissionSource + " bit ,");
SqlQuery.AppendLine(DIColumns.DIUser.PermissionTimePeriod + " bit ,");
SqlQuery.AppendLine(DIColumns.DIUser.PermissionArea + " bit ,");
SqlQuery.AppendLine(DIColumns.DIUser.PermissionIC + " bit ,");
SqlQuery.AppendLine(DIColumns.DIUser.AssocitedIC + " varchar(MAX) ,");
SqlQuery.AppendLine(DIColumns.DIUser.AssocitedICWithParent + " varchar(MAX) ,");
SqlQuery.AppendLine(DIColumns.DIUser.AssociatedArea + " varchar(MAX),");
SqlQuery.AppendLine(DIColumns.DIUser.AssociatedAreaWithParent + " varchar(MAX) ,");
SqlQuery.AppendLine(DIColumns.DIUser.IsAdmin + " bit ,");
SqlQuery.AppendLine(DIColumns.DIUser.ISloggedIn + " bit ,");
SqlQuery.AppendLine(DIColumns.DIUser.LastLogin + " datetime ,");
SqlQuery.AppendLine(DIColumns.DIUser.LastLogout + " datetime )");
RetVal = SqlQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns qurey to insert indicator_Order column into UT_Indicator_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertIndicatorOrderColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.Indicator + " ADD COLUMN " + DIColumns.Indicator.IndicatorOrder + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " int(4) ";
}
else
{
RetVal += " int ";
}
}
else
{
RetVal += " number ";
}
return RetVal;
}
public static string EditUserAccess(int userNId, string dataPrefix, int PermissionSource, int PermissionTimePeriod, int PermissionArea, int PermissionIC, int IsAdmin, string PermissionAreaDesc, string PermissionICDesc, DITables tableNames)
{
string RetVal = null;
StringBuilder sSql = new StringBuilder();
sSql.Append("UPDATE " + tableNames.DBUserAccess);
sSql.Append(" Set " + DIColumns.DBUserAccess.PermissionSource + "=" + PermissionSource + "," + DIColumns.DBUserAccess.PermissionTimePeriod + "=" + PermissionTimePeriod + "," + DIColumns.DBUserAccess.PermissionArea + "=" + PermissionArea + "," + DIColumns.DBUserAccess.PermissionIC + "=" + PermissionIC + "," + DIColumns.DBUserAccess.PermissionAreaDescription + "='" + PermissionAreaDesc + "'," + DIColumns.DBUserAccess.PermissionICDescription + "= '" + PermissionICDesc + "'," + DIColumns.DBUserAccess.IsAdmin + "=" + IsAdmin);
sSql.Append(" WHERE " + DIColumns.DBUserAccess.UserNId + "=" + userNId);
sSql.Append(" AND " + DIColumns.DBUserAccess.DBPrefix + " = '" + dataPrefix + "'");
RetVal = sSql.ToString();
//--dispose
sSql = null;
return RetVal;
}
/// <summary>
/// Get Nid and Name where two language table have different Gid with same Nid
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="langCode"></param>
/// <returns></returns>
public string GetUnmatchedGidForLanguage(string dataPrefix, string langCode)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
DITables table = new DITables(dataPrefix, langCode);
sbQuery.Append("SELECT S." + DIColumns.Subgroup.SubgroupNId + " AS " + DIColumns.Subgroup.SubgroupNId + ",S." + DIColumns.Subgroup.SubgroupName + " AS " + DIColumns.Subgroup.SubgroupName + ",S." + DIColumns.Subgroup.SubgroupGId + " AS " + DIColumns.Subgroup.SubgroupGId );
sbQuery.Append(", S1." + DIColumns.Subgroup.SubgroupNId + ",S1." + DIColumns.Subgroup.SubgroupName + ",S1." + DIColumns.Subgroup.SubgroupGId + " FROM " + this.TablesName.Subgroup + " S," + table.Subgroup + " S1 ");
sbQuery.Append(" WHERE ");
sbQuery.Append(" S." + DIColumns.Subgroup.SubgroupNId + "= S1." + DIColumns.Subgroup.SubgroupNId);
sbQuery.Append(" AND S." + DIColumns.Subgroup.SubgroupGId + "<> S1." + DIColumns.Subgroup.SubgroupGId );
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns qurey to insert Short_Name column into UT_Indicator_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertShortNameColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.Indicator + " ADD COLUMN " + DIColumns.Indicator.ShortName + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " varchar(50) ";
}
else
{
RetVal += " varchar(50) ";
}
}
else
{
RetVal += " Text(50) ";
}
return RetVal;
}
