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 IndicatorInfo GetIndicatorInfo(DataRow row)
{
IndicatorInfo RetVal;
try
{
//get unit from source table
RetVal = new IndicatorInfo();
RetVal.Name = DICommon.RemoveQuotes(row[Indicator.IndicatorName].ToString());
RetVal.GID = row[Indicator.IndicatorGId].ToString();
RetVal.Global = Convert.ToBoolean(row[Indicator.IndicatorGlobal]);
RetVal.Info = DICommon.RemoveQuotes(Convert.ToString(row[Indicator.IndicatorInfo]));
RetVal.Nid = Convert.ToInt32(row[Indicator.IndicatorNId]);
}
catch (Exception ex)
{
RetVal = null;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
/// <summary>
/// Imports records from source database to target database/template
/// </summary>
/// <param name="selectedNids"></param>
/// <param name="allSelected">Set true to import all records</param>
public override void ImportValues(List <string> selectedNids, bool allSelected)
{
DI6SubgroupValBuilder SubgroupValBuilderObj = new DI6SubgroupValBuilder(this._TargetDBConnection, this._TargetDBQueries);
int ProgressBarValue = 0;
foreach (string Nid in selectedNids)
{
try
{
//import into target database
SubgroupValBuilderObj.ImportSubgroupVal(Convert.ToInt32(Nid), this.SourceDBQueries, this.SourceDBConnection);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
this.RaiseIncrementProgessBarEvent(ProgressBarValue);
ProgressBarValue++;
}
}
/// <summary>
/// Saves ICIUSLabel into recommendedsources table
/// </summary>
/// <param name="ICIUSLabel"></param>
/// <param name="ICIUSOrder"></param>
/// <param name="dataNId"></param>
/// <returns></returns>
public int SaveICIUSLabel(string ICIUSLabel, string ICIUSOrder, string dataNId)
{
int RetVal = -1;
DataRow[] Rows;
RecommendedSourcesBuilder RecBuilder = new RecommendedSourcesBuilder(this._DBConnection, this._DBQueries);
try
{
// check records is already exists or not. IF exists then get NID
Rows = this._RecommendedSourcesTable.Select(RecommendedSources.DataNId + "=" + dataNId);
if (Rows.Length > 0)
{
RetVal = Convert.ToInt32(Rows[0][RecommendedSources.NId]);
}
if (RetVal <= 0)
{
//insert into database
RecBuilder.InsertIntoDatabase(Convert.ToInt32(dataNId), ICIUSLabel);
RetVal = this._DBConnection.GetNewId();
}
else
{
// update into database
RecBuilder.UpdateRecommendedSources(Convert.ToInt32(dataNId), ICIUSLabel);
}
//update RecommendedSourcesTable
this.UpdateRecommendedSourcesTable(ICIUSLabel, Convert.ToInt32(dataNId), RetVal);
}
catch (Exception ex)
{
RetVal = -1;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private void 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 int GetSubgroupTypeNid(string typeName)
{
int RetVal = -1;
DataTable Table;
try
{
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.SubgroupTypes.GetSubgroupTypes(FilterFieldType.GId, "'" + typeName + "'"));
if (Table.Rows.Count > 0)
{
RetVal = Convert.ToInt32(Table.Rows[0][SubgroupTypes.SubgroupTypeNId]);
}
}
catch (Exception ex)
{
RetVal = -1;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
/// <summary>
/// Get NotesProfilesInfo for NotesProfileNid
/// </summary>
/// <param name="notesProfileNid"></param>
/// <returns></returns>
public NotesProfilesInfo GetNotesProfilesInfo(string notesProfileNid)
{
NotesProfilesInfo RetVal = null;
string SqlQuery = string.Empty;
DbDataReader DBReader = null;
try
{
SqlQuery = this.DBQueries.Notes.GetNotesProfiles(FilterFieldType.NId, notesProfileNid);
DBReader = (DbDataReader)this.DBConnection.ExecuteReader(SqlQuery);
if (DBReader.HasRows)
{
RetVal = new NotesProfilesInfo();
while (DBReader.Read())
{
RetVal.Profile_NId = (int)DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Profile.ProfileNId];
RetVal.Profile_Name = Convert.ToString(DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Profile.ProfileName]);
RetVal.Profile_EMail = Convert.ToString(DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Profile.ProfileEMail]);
RetVal.Profile_Country = Convert.ToString(DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Profile.ProfileCountry]);
RetVal.ProfileOrganization = Convert.ToString(DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Profile.ProfileOrg]);
RetVal.Profile_Org_Type = Convert.ToString(DBReader[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Notes_Profile.ProfileOrgType]);
break;
}
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
finally
{
if (!DBReader.IsClosed)
{
DBReader.Close();
}
}
return(RetVal);
}
private void ImportSector(DataRow row)
{
IndicatorClassificationInfo SrcClassification;
try
{
//get ic from source table
SrcClassification = new IndicatorClassificationInfo();
SrcClassification.Name = DICommon.RemoveQuotes(row[IndicatorClassifications.ICName].ToString());
SrcClassification.GID = row[IndicatorClassifications.ICGId].ToString();
SrcClassification.IsGlobal = Convert.ToBoolean(row[IndicatorClassifications.ICGlobal]);
SrcClassification.Nid = Convert.ToInt32(row[IndicatorClassifications.ICNId]);
if (!Information.IsDBNull(row[IndicatorClassifications.ICInfo]))
{
SrcClassification.ClassificationInfo = DICommon.RemoveQuotes(row[IndicatorClassifications.ICInfo].ToString());
}
SrcClassification.Parent = new IndicatorClassificationInfo();
SrcClassification.Parent.Nid = Convert.ToInt32(row[IndicatorClassifications.ICParent_NId]);
SrcClassification.Type = ICType.Sector;
//import into target database
Utility.CreateClassificationChainFromExtDB(
SrcClassification.Nid,
SrcClassification.Parent.Nid,
SrcClassification.GID,
SrcClassification.Name,
SrcClassification.Type,
SrcClassification.ClassificationInfo,
SrcClassification.IsGlobal,
this.SourceDBQueries, this.SourceDBConnection, this._TargetDBQueries, this._TargetDBConnection);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Updates DBMetadata information into current language table.
/// </summary>
/// <param name="NID"></param>
/// <param name="description"></param>
/// <param name="publisherName"></param>
/// <param name="publisherDate"></param>
/// <param name="publisherCountry"></param>
/// <param name="publisherRegion"></param>
/// <param name="publisherOffice"></param>
/// <param name="areaCount"></param>
/// <param name="indicatorCount"></param>
/// <param name="IUSCount"></param>
/// <param name="timeperiodCount"></param>
/// <param name="sourceCount"></param>
/// <param name="dataCount"></param>
public void UpdateRecord(int NID, string description, string publisherName, string publisherDate,
string publisherCountry, string publisherRegion, string publisherOffice,
string areaCount, string indicatorCount, string IUSCount, string timeperiodCount, string sourceCount, string dataCount)
{
string SqlQuery = string.Empty;
DITables TablesName;
string DataPrefix = this.DBConnection.DIDataSetDefault();
try
{
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Update.UpdateRecord(this.DBQueries.TablesName.DBMetadata, NID,
DICommon.RemoveQuotes(description), DICommon.RemoveQuotes(publisherName), publisherDate,
DICommon.RemoveQuotes(publisherCountry), DICommon.RemoveQuotes(publisherRegion), DICommon.RemoveQuotes(publisherOffice),
areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Returns true/false.True after successfully insertion/updation of xslt text into database
/// </summary>
/// <param name="xsltText"></param>
/// <returns></returns>
protected bool SaveXsltTextIntoDB(string xsltText, string metadataXsltType, bool removeQuotes)
{
bool RetVal = false;
try
{
if (removeQuotes)
{
xsltText = DevInfo.Lib.DI_LibBAL.Utility.DICommon.RemoveQuotes(xsltText);
//xsltText = DIQueries.RemoveQuotesForSqlQuery(xsltText);
}
// 1. Check xslt exists in UT_Xslt table or not
if (this.DBConnection.ExecuteDataTable(this.DBQueries.Xslt.GetXSLT(FilterFieldType.Name, metadataXsltType)).Rows.Count == 0)
{
// 2. If not then insert XLST text into all UT_XSLT tables
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Xslt.Insert.InsertXSLT(this.DBQueries.DataPrefix, xsltText, metadataXsltType));
}
else
{
// 3. Else, Update XLST text into current UT_XLST language table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Xslt.Update.UpdateXSLT(this.DBQueries.DataPrefix, xsltText, metadataXsltType));
}
RetVal = true;
}
catch (Exception ex)
{
RetVal = false;
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private void ConvertMetadataXmlIntoDatabse()
{
DI7MetadataConverter MetadataConverter = new DI7MetadataConverter(this._DBConnection, this._DBQueries);
DIQueries TempDBQueries = null;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
try
{
// step1: create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// get language for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempDBQueries = new DIQueries(DataPrefix, LanguageCode);
MetadataConverter = new DI7MetadataConverter(this._DBConnection, TempDBQueries);
MetadataConverter.WrongMetdataFoundEvent += new WrongMetadataFound(MetadataConverter_WrongMetdataFoundEvent);
// convert Indicator,Map and Source Info
MetadataConverter.ConvertIndicatorMapICAndXsltMetadataIntoNewFormat();
}
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
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 UpdateCategoryTable()
{
MetadataCategoryBuilder CategoryBuilder;
DIQueries TempDBQueries = null;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
string CategoryNid = string.Empty;
string CategoryGID = string.Empty;
string SourceCategoryTable = string.Empty;
DataTable CategoryTable;
try
{
// step1: create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// get language for all available languages
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempDBQueries = new DIQueries(DataPrefix, LanguageCode);
if (string.IsNullOrEmpty(SourceCategoryTable))
{
// update GID in only one language table and for other language tables, use this language table
CategoryBuilder = new MetadataCategoryBuilder(this._DBConnection, TempDBQueries);
CategoryTable = CategoryBuilder.GetAllRecordsFromMetadataCategory();
foreach (DataRow Row in CategoryTable.Rows)
{
//update metdata category table (set parent_nid to -1 and update gids)
//CategoryGID = Convert.ToString(Row[Metadata_Category.CategoryName]).ToUpper().Replace(" ", "_");
//CategoryGID = MetaDataBuilder.GetNewMetaDataCategoryGID();
CategoryNid = Convert.ToString(Row[Metadata_Category.CategoryNId]);
CategoryGID = DICommon.GetValidGIdForSDMXRule(Convert.ToString(Row[Metadata_Category.CategoryName]).ToUpper()) + "_" + CategoryNid;
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Update.UpdateMetadataCategory(TempDBQueries.TablesName.MetadataCategory, Convert.ToInt32(CategoryNid), CategoryGID, "-1", false, false));
}
SourceCategoryTable = TempDBQueries.TablesName.MetadataCategory;
}
else
{
// use first language table to update other language table
this.DBConnection.ExecuteNonQuery("UPDATE " + SourceCategoryTable + " AS src INNER JOIN " + TempDBQueries.TablesName.MetadataCategory + " AS trg ON src." + Metadata_Category.CategoryNId + " = trg." + Metadata_Category.CategoryNId + " SET trg." + Metadata_Category.CategoryGId + "=src." + Metadata_Category.CategoryGId + " and trg." + Metadata_Category.ParentCategoryNId + "=src." + Metadata_Category.ParentCategoryNId + ";");
}
}
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
public override void Import(string selectedGIds)
{
int ProgressCounter = 0;
DataTable Table = null;
string selectedNids = string.Empty;
selectedNids = selectedGIds;
// Initialize progress bar
this.RaiseProgressBarInitialize(selectedGIds.Split(',').GetUpperBound(0) + 1);
IndicatorBuilder TrgIndicatorBuilderObj = null;
IndicatorBuilder SourceIndicatorBuilder = null;
IndicatorInfo IndicatorInfoObj = null;
Dictionary <string, DataRow> FileWithNids = new Dictionary <string, DataRow>();
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportIndicator(selectedNids));
//-- Step 2:Initialise Indicator Builder with Target DBConnection
TrgIndicatorBuilderObj = new IndicatorBuilder(this.TargetDBConnection, this.TargetDBQueries);
//-- Step 3: Import Nids for each SourceFile
foreach (DataRow Row in Table.Copy().Rows)
{
try
{
string SourceFileWPath = Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);
SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, SourceFileWPath, String.Empty, MergetTemplateConstants.DBPassword);
SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection);
// Get Source Indicator Info
SourceIndicatorBuilder = new IndicatorBuilder(SourceDBConnection, SourceDBQueries);
IndicatorInfoObj = SourceIndicatorBuilder.GetIndicatorInfo(FilterFieldType.NId, Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]), FieldSelection.Light);
// Import INdicator By Nid
TrgIndicatorBuilderObj.ImportIndicator(IndicatorInfoObj, Convert.ToInt32(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]), SourceDBQueries, SourceDBConnection);
ProgressCounter += 1;
this.RaiseProgressBarIncrement(ProgressCounter);
}
catch (Exception ex) { ExceptionFacade.ThrowException(ex); }
finally
{
if (SourceDBConnection != null)
{
SourceDBConnection.Dispose();
}
if (SourceDBQueries != null)
{
SourceDBQueries.Dispose();
}
}
}
this._AvailableTable = this.GetAvailableTable();
this._UnmatchedTable = this.GetUnmatchedTable();
// Close ProgressBar
this.RaiseProgressBarClose();
}
public override void Import(string selectedNIds)
{
IndicatorClassificationInfo SrcClassification;
string SrcFileName = string.Empty;
DataTable Table = null;
int ProgressCounter = 0;
//IndicatorClassificationBuilder ICBuilder = null;
//Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>();
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
// Initialize progress bar
this.RaiseProgressBarInitialize(selectedNIds.Split(',').GetUpperBound(0) + 1);
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportICs(selectedNIds));
////-- Step 2:Initialise Indicator Builder with Target DBConnection
//ICBuilder = new IndicatorClassificationBuilder(this.TargetDBConnection, this.TargetDBQueries);
try
{
//-- Step 3: Import Nids for each SourceFile
foreach (DataRow Row in Table.Copy().Rows)
{
try
{
if (SrcFileName != Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]))
{
SrcFileName = Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);
//-- Step 2:Initialise DBConnection
if (SourceDBConnection != null)
{
SourceDBConnection.Dispose();
}
SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, SrcFileName, String.Empty, MergetTemplateConstants.DBPassword);
SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection);
}
SrcClassification = new IndicatorClassificationInfo();
SrcClassification.Name = DICommon.RemoveQuotes(Row[IndicatorClassifications.ICName].ToString());
SrcClassification.GID = Row[IndicatorClassifications.ICGId].ToString();
SrcClassification.IsGlobal = Convert.ToBoolean(Row[IndicatorClassifications.ICGlobal]);
SrcClassification.Nid = Convert.ToInt32(Row[IndicatorClassifications.ICNId]);
if (!string.IsNullOrEmpty(Convert.ToString((Row[IndicatorClassifications.ICInfo]))))
{
SrcClassification.ClassificationInfo = DICommon.RemoveQuotes(Row[IndicatorClassifications.ICInfo].ToString());
}
SrcClassification.Parent = new IndicatorClassificationInfo();
SrcClassification.Parent.Nid = Convert.ToInt32(Row[IndicatorClassifications.ICParent_NId]);
SrcClassification.Type = this._CurrentICType;
//import into target database
this.CreateClassificationChainFromExtDB(
SrcClassification.Nid,
SrcClassification.Parent.Nid,
SrcClassification.GID,
SrcClassification.Name,
SrcClassification.Type,
SrcClassification.ClassificationInfo,
SrcClassification.IsGlobal,
SourceDBQueries, SourceDBConnection, this._TargetDBQueries, this._TargetDBConnection);
ProgressCounter += 1;
this.RaiseProgressBarIncrement(ProgressCounter);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
this._UnmatchedTable = this.GetUnmatchedTable();
this._AvailableTable = this.GetAvailableTable();
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
finally
{
if (SourceDBConnection != null)
{
SourceDBConnection.Dispose();
}
}
// Close ProgressBar
this.RaiseProgressBarClose();
}
/// <summary>
/// 发生异常时，跳转至异常信息显示页
/// </summary>
/// <param name="filterContext"></param>
public virtual void OnException(ExceptionContext filterContext)
{
if (filterContext == null)
{
throw new ArgumentNullException("filterContext");
}
if (filterContext.IsChildAction)
{
return;
}
// If custom errors are disabled, we need to let the normal ASP.NET exception handler
if (filterContext.ExceptionHandled)
{
return;
}
Exception exception = filterContext.Exception;
ExceptionFacade exceptionFacade = new ExceptionFacade(exception.Message, exception);
exceptionFacade.Log();
// If this is not an HTTP 500 (for example, if somebody throws an HTTP 404 from an action method),
// ignore it.
if (new HttpException(null, exception).GetHttpCode() != 500)
{
return;
}
//过滤异步请求
if (filterContext.HttpContext.Request.Headers.Get("X-Requested-With") != null)
{
return;
}
if (exception.InnerException != null && exception.InnerException is ExceptionFacade)
{
exceptionFacade = exception.InnerException as ExceptionFacade;
}
if (exceptionFacade == null)
{
return;
}
if (!filterContext.HttpContext.IsCustomErrorEnabled)
{
return;
}
//todo:zhengw by:sunx SystemMessageViewModel的Body原为exceptionFacade.OperationContextMessage，此处抛未实例化异常
filterContext.Result = new RedirectResult(SiteUrls.Instance().SystemMessage(filterContext.Controller.TempData, new SystemMessageViewModel {
StatusMessageType = Tunynet.Mvc.StatusMessageType.Error, Title = "出错了", Body = exceptionFacade.Message
}));
filterContext.ExceptionHandled = true;
filterContext.HttpContext.Response.Clear();
filterContext.HttpContext.Response.StatusCode = 500;
// Certain versions of IIS will sometimes use their own error page when
// they detect a server error. Setting this property indicates that we
// want it to try to render ASP.NET MVC's error page instead.
filterContext.HttpContext.Response.TrySkipIisCustomErrors = true;
}
private void UpdateDBSchema(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TablesName = this.DBQueries.TablesName;
DIServerType ServerType;
try
{
ServerType = this.DBConnection.ConnectionStringParameters.ServerType;
// step1: add columns in all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// add columns in all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// create tables name
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
// insert new columns into indicator_classification table
this.AddColumnsIntoIndicatorClassificationsTable(forOnlineDB, TablesName, ServerType);
// insert new columns into indicator table
this.AddColumnsIntoMetadataCategoryTable(forOnlineDB, TablesName, ServerType);
// insert new column "AreaShortName" into area table
this.AddColumnIntoAreaTable(forOnlineDB, TablesName, ServerType);
if (!this.ISColumnExist("*", TablesName.MetadataReport))
{
//-- Create Metedata Report table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataReport.Insert.CreateTable(TablesName.MetadataReport, forOnlineDB, ServerType));
}
}
this.RaiseProcessInfoEvent(2);
// add columns into StartDate,EndDate,Perodicity into Timeperiod table
this.AddColumnIntoTimeperiodTable(forOnlineDB, TablesName, ServerType);
this.RaiseProcessInfoEvent(3);
// insert new column IsDefaultSubgroup,AvlMinDataValue,AvlMaxDataValue,AvlMinTimePeriod,AvlMaxTimePeriod into IUS table
this.AddColumnsIntoIUSTable(forOnlineDB, TablesName, ServerType);
//-- Update Default Subgroup
this.UpdateDefaultSubgroupValue();
this.RaiseProcessInfoEvent(4);
// insert new column Textual_Data_Value,IsTextualData,IsMRD,IsPlannedValue,IUNId,ConfidenceIntervalUpper,
// ConfidenceIntervalLower into data table
this.AddColumnIntoDataTable(forOnlineDB, TablesName, ServerType);
this.RaiseProcessInfoEvent(5);
//update parent nid and category gid into metadata category table
this.UpdateCategoryTable();
// update metadata and xslt
this.ConvertMetadataXmlIntoDatabse();
this.RaiseProcessInfoEvent(6);
// Update Textual And Numeric Data
this.UpdateTextualAndNumericData(forOnlineDB, TablesName, ServerType);
this.RaiseProcessInfoEvent(7);
//-- Rename IC_IUS table
this.RenameIndicatorClassificationIUSTable(forOnlineDB, TablesName, ServerType);
this.RaiseProcessInfoEvent(8);
this.CreateSDMXUserTable(forOnlineDB);
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Generate SDMX Files from DES
/// </summary>
/// <param name="DESFileNameWithPath"></param>
/// <param name="DSDFileNameWPath"></param>
/// <param name="outputFolder"></param>
/// <returns></returns>
public static bool Generate_SDMXML_Data(string DESFileNameWithPath, string DSDFileNameWPath, string outputFolder)
{
bool RetVal = false;
DIConnection DBConnection = null;
DIQueries DBQueries = null;
int count = 0;
// Utility.TemporaryFileNamePath
DataTable Table = DevInfo.Lib.DI_LibBAL.Import.DAImport.DES.DataEntrySpreadsheets.GetDataTableForAllDESSheets(DESFileNameWithPath);
string TempDatabaseName = string.Empty;
string TempDatabaseNameCompact = string.Empty;
TempDatabaseName = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template));
Dictionary <string, int> IndicatorList = new Dictionary <string, int>();
Dictionary <string, int> UnitList = new Dictionary <string, int>();
Dictionary <string, int> AreaList = new Dictionary <string, int>();
Dictionary <string, int> SgValList = new Dictionary <string, int>();
Dictionary <string, int> TimepeirodList = new Dictionary <string, int>();
Dictionary <string, int> SourceList = new Dictionary <string, int>();
try
{
DevInfo.Lib.DI_LibDAL.Resources.Resource.GetBlankDevInfoDBFile(TempDatabaseName);
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseName, string.Empty, string.Empty);
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
DIDataValueHelper.MergeTextualandNumericDataValueColumn(DBConnection, DBQueries);
IndicatorBuilder IndBuilder = new IndicatorBuilder(DBConnection, DBQueries);
UnitBuilder UBuilder = new UnitBuilder(DBConnection, DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(DBConnection, DBQueries);
AreaBuilder AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries);
IndicatorClassificationBuilder ICBuilder = new IndicatorClassificationBuilder(DBConnection, DBQueries);
TimeperiodBuilder TBuilder = new TimeperiodBuilder(DBConnection, DBQueries);
SourceBuilder SrcBuilder = new SourceBuilder(DBConnection, DBQueries);
DIDatabase DatabaseObj = new DIDatabase(DBConnection, DBQueries);
IUSBuilder IUSBuilderObj = new IUSBuilder(DBConnection, DBQueries);
foreach (DataRow Row in Table.Rows)
{
int IndicatorNId = 0;
int UnitNId = 0;
int SGValNId = 0;
int AreaNId = 0;
int TimeperiodNId = 0;
int SourceNid = 0;
int IUSNID = 0;
//-- Import Indicator
if (IndicatorList.ContainsKey(Convert.ToString(Row[Indicator.IndicatorGId])))
{
IndicatorNId = IndicatorList[Convert.ToString(Row[Indicator.IndicatorGId])];
}
else
{
IndicatorNId = IndBuilder.ImportIndicator(Convert.ToString(Row[Indicator.IndicatorName]), Convert.ToString(Row[Indicator.IndicatorGId]), false);
IndicatorList.Add(Convert.ToString(Row[Indicator.IndicatorGId]), IndicatorNId);
}
//-- Import Unit
if (UnitList.ContainsKey(Convert.ToString(Row[Unit.UnitGId])))
{
UnitNId = UnitList[Convert.ToString(Row[Unit.UnitGId])];
}
else
{
UnitNId = UBuilder.ImportUnit(Convert.ToString(Row[Unit.UnitGId]), Convert.ToString(Row[Unit.UnitName]).Trim(), false);
UnitList.Add(Convert.ToString(Row[Unit.UnitGId]), UnitNId);
}
//-- Import SubgroupVal
if (!string.IsNullOrEmpty(Convert.ToString(Row[SubgroupVals.SubgroupVal])))
{
if (SgValList.ContainsKey(Convert.ToString(Row[SubgroupVals.SubgroupValGId])))
{
SGValNId = SgValList[Convert.ToString(Row[SubgroupVals.SubgroupValGId])];
}
else
{
SGValNId = SGBuilder.ImportSubgroupVal(Convert.ToString(Row[SubgroupVals.SubgroupVal]), Convert.ToString(Row[SubgroupVals.SubgroupValGId]), false);
SgValList.Add(Convert.ToString(Row[SubgroupVals.SubgroupValGId]), SGValNId);
}
}
//-- Import Area
if (AreaList.ContainsKey(Convert.ToString(Row[Area.AreaID])))
{
AreaNId = AreaList[Convert.ToString(Row[Area.AreaID])];
}
else
{
AreaNId = AreaBuilderObj.ImportArea(Convert.ToString(Row[Area.AreaName]), Convert.ToString(Row[Area.AreaID]), String.Empty, false);
AreaList.Add(Convert.ToString(Row[Area.AreaID]), AreaNId);
}
//-- Import Timeperiod
if (TimepeirodList.ContainsKey(Convert.ToString(Row[Timeperiods.TimePeriod])))
{
TimeperiodNId = TimepeirodList[Convert.ToString(Row[Timeperiods.TimePeriod])];
}
else
{
TimeperiodNId = TBuilder.CheckNCreateTimeperiod(Convert.ToString(Row[Timeperiods.TimePeriod]));
TimepeirodList.Add(Convert.ToString(Row[Timeperiods.TimePeriod]), TimeperiodNId);
}
//-- Import Source
if (SourceList.ContainsKey(Convert.ToString(Row[IndicatorClassifications.ICName])))
{
SourceNid = SourceList[Convert.ToString(Row[IndicatorClassifications.ICName])];
}
else
{
SourceNid = SrcBuilder.CheckNCreateSource(Convert.ToString(Row[IndicatorClassifications.ICName]));
SourceList.Add(Convert.ToString(Row[IndicatorClassifications.ICName]), SourceNid);
}
IUSNID = IUSBuilderObj.ImportIUS(IndicatorNId, UnitNId, SGValNId, 0, 0, DBQueries, DBConnection);
if (IUSNID > 0 && AreaNId > 0 && TimeperiodNId > 0 && !string.IsNullOrEmpty(Convert.ToString(Row[Data.DataValue])))
{
DatabaseObj.CheckNCreateData(AreaNId, IUSNID, SourceNid, TimeperiodNId, Convert.ToString(Row[Data.DataValue]));
}
}
DatabaseObj.UpdateIndicatorUnitSubgroupNIDsInData();
//-- Compact Database into TempFile
TempDatabaseNameCompact = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template));
DIDatabase.CompactDataBase(ref DBConnection, DBQueries, TempDatabaseNameCompact, true);
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseNameCompact, string.Empty, string.Empty);
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
if (!Directory.Exists(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_'))))
{
Directory.CreateDirectory(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_')));
}
System.Xml.XmlDocument XmlDoc = SDMXUtility.Get_Query(SDMXSchemaType.Two_One, null, QueryFormats.StructureSpecificTS, DataReturnDetailTypes.Full, Guid.NewGuid().ToString().Replace("-", "").Replace("_", ""), DBConnection, DBQueries);
RetVal = SDMXUtility.Generate_Data(SDMXSchemaType.Two_One, XmlDoc, DevInfo.Lib.DI_LibSDMX.DataFormats.StructureSpecificTS, DBConnection, DBQueries, outputFolder);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
return(RetVal);
}
private void UpdateSubgroupVal()
{
DataTable SubgroupValTable;
DataTable AssociatedSubgroupTable;
int SubgroupValNId = -1;
string UpdateColumnName = string.Empty;
string DataPrefix = string.Empty;
string LangaugeCode = string.Empty;
DITables TablesName = null;
try
{
// get data prefix
DataPrefix = this.DBConnection.DIDataSetDefault();
foreach (DataRow LanguageRow in this.DBConnection.DILanguages(DataPrefix).Rows)
{
// get language code
LangaugeCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// set table names
TablesName = new DITables(DataPrefix, LangaugeCode);
// Step1: add Subgroup_age,Subgroup_sex,Subgroup_location,Subgroup_others columns in subgroup_Val table
this.DBConnection.AddColumn(TablesName.SubgroupVals, SubgroupValRemovedColumns.SubgroupValAge, "int", "0");
this.DBConnection.AddColumn(TablesName.SubgroupVals, SubgroupValRemovedColumns.SubgroupValSex, "int", "0");
this.DBConnection.AddColumn(TablesName.SubgroupVals, SubgroupValRemovedColumns.SubgroupValLocation, "int", "0");
this.DBConnection.AddColumn(TablesName.SubgroupVals, SubgroupValRemovedColumns.SubgroupValOthers, "int", "0");
// get subgroupval from database
SubgroupValTable = this.DBConnection.ExecuteDataTable(this.DBQueries.SubgroupVals.GetSubgroupVals());
// Step2: Do the following for each subgroup_val record
// Step 2(a): On the basis of subgroup_val_Nid, update age,sex,location and others column using subgroup_Val_subgroup table
foreach (DataRow Row in SubgroupValTable.Rows)
{
SubgroupValNId = Convert.ToInt32(Row[SubgroupVals.SubgroupValNId]);
AssociatedSubgroupTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Subgroup.GetSubgroupNIdNType(SubgroupValNId));
// set age, sex , others and location to zero
this.DBConnection.ExecuteNonQuery(UpdateQueries.SubgroupVal.Update.SetAgeSexLocationNOthersToZero(DataPrefix, LangaugeCode, SubgroupValNId));
if (AssociatedSubgroupTable.Rows.Count > 0)
{
foreach (DataRow SGRow in AssociatedSubgroupTable.Rows)
{
switch (Convert.ToInt32(SGRow[Subgroup.SubgroupType]))
{
case DI6ToDI5Converter.AgeNId:
UpdateColumnName = SubgroupValRemovedColumns.SubgroupValAge;
break;
case DI6ToDI5Converter.SexNId:
UpdateColumnName = SubgroupValRemovedColumns.SubgroupValSex;
break;
case DI6ToDI5Converter.LocationNId:
UpdateColumnName = SubgroupValRemovedColumns.SubgroupValLocation;
break;
case DI6ToDI5Converter.OthersNId:
UpdateColumnName = SubgroupValRemovedColumns.SubgroupValOthers;
break;
default:
UpdateColumnName = string.Empty;
break;
}
if (!string.IsNullOrEmpty(UpdateColumnName))
{
this.DBConnection.ExecuteNonQuery(UpdateQueries.SubgroupVal.Update.UpdateAgeSexLocationNOthersInSubgroupVal(
DataPrefix, LangaugeCode, UpdateColumnName, Convert.ToInt32(SGRow[Subgroup.SubgroupNId]), SubgroupValNId));
}
}
}
}
}
//Step 3: Drop Subgroupvalssubgrouptable
this.DBConnection.DropTable(this.DBQueries.TablesName.SubgroupValsSubgroup);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Create new language & language dependent tables like UT_Indicator_ar,UT_Unit_ar,..etc.
/// </summary>
/// <param name="languageCode">Language Code like en</param>
/// <param name="languageName">Language name like DI_English [en]</param>
/// <param name="dataPrefix">Dataset prefix like UT</param>
public void CreateNewLanguageTables(string languageCode, string languageName, string dataPrefix)
{
Dictionary <string, LanguageTableInfo> LanguageTables = new Dictionary <string, LanguageTableInfo>();
try
{
if (!this.DBConnection.IsValidDILanguage(dataPrefix, languageCode))
{
DITables ExistingTableNames = new DITables(dataPrefix, this.DBConnection.DILanguageCodeDefault(dataPrefix));
DITables NewTableNames = new DITables(dataPrefix, "_" + languageCode);
//create collection for new language tables
//add table name, text field column and column width
LanguageTables.Add(ExistingTableNames.Area, new LanguageTableInfo(NewTableNames.Area, Area.AreaName, ColumnWidth.AreaName));
LanguageTables.Add(ExistingTableNames.Indicator, new LanguageTableInfo(NewTableNames.Indicator, Indicator.IndicatorName, ColumnWidth.IndicatorName));
LanguageTables.Add(ExistingTableNames.Unit, new LanguageTableInfo(NewTableNames.Unit, Unit.UnitName, ColumnWidth.UnitName));
LanguageTables.Add(ExistingTableNames.Subgroup, new LanguageTableInfo(NewTableNames.Subgroup, Subgroup.SubgroupName, ColumnWidth.Subgroup));
LanguageTables.Add(ExistingTableNames.SubgroupVals, new LanguageTableInfo(NewTableNames.SubgroupVals, SubgroupVals.SubgroupVal, ColumnWidth.SubgroupVal));
LanguageTables.Add(ExistingTableNames.AgePeriod, new LanguageTableInfo(NewTableNames.AgePeriod, AgePeriods.AgePeriod, ColumnWidth.AgePeriod));
LanguageTables.Add(ExistingTableNames.AreaLevel, new LanguageTableInfo(NewTableNames.AreaLevel, Area_Level.AreaLevelName, ColumnWidth.AreaLevel));
LanguageTables.Add(ExistingTableNames.Assistant, new LanguageTableInfo(NewTableNames.Assistant, Assistant.AssistantType, ColumnWidth.Assistant));
LanguageTables.Add(ExistingTableNames.AssistantTopic, new LanguageTableInfo(NewTableNames.AssistantTopic, Assistant_Topic.TopicName, ColumnWidth.AssistantTopic));
LanguageTables.Add(ExistingTableNames.FootNote, new LanguageTableInfo(NewTableNames.FootNote, FootNotes.FootNote, -1));
LanguageTables.Add(ExistingTableNames.IndicatorClassifications, new LanguageTableInfo(NewTableNames.IndicatorClassifications, IndicatorClassifications.ICName, ColumnWidth.ICName));
LanguageTables.Add(ExistingTableNames.NotesClassification, new LanguageTableInfo(NewTableNames.NotesClassification, Notes_Classification.ClassificationName, ColumnWidth.NotesClassificationName));
LanguageTables.Add(ExistingTableNames.Notes, new LanguageTableInfo(NewTableNames.Notes, Notes.Note, ColumnWidth.NotesName));
LanguageTables.Add(ExistingTableNames.AssistanteBook, new LanguageTableInfo(NewTableNames.AssistanteBook, string.Empty, -1));
LanguageTables.Add(ExistingTableNames.AreaFeatureType, new LanguageTableInfo(NewTableNames.AreaFeatureType, string.Empty, -1));
LanguageTables.Add(ExistingTableNames.AreaMapMetadata, new LanguageTableInfo(NewTableNames.AreaMapMetadata, string.Empty, -1));
// subgroup_type
LanguageTables.Add(ExistingTableNames.SubgroupType, new LanguageTableInfo(NewTableNames.SubgroupType, SubgroupTypes.SubgroupTypeName, ColumnWidth.SubgroupTypeName));
// add DBMetaData table
LanguageTables.Add(ExistingTableNames.DBMetadata, new LanguageTableInfo(NewTableNames.DBMetadata, DBMetaData.Description, -1));
// add recommended sources table
LanguageTables.Add(ExistingTableNames.RecommendedSources, new LanguageTableInfo(NewTableNames.RecommendedSources, string.Empty, -1));
// add metadata category table
LanguageTables.Add(ExistingTableNames.MetadataCategory, new LanguageTableInfo(NewTableNames.MetadataCategory, string.Empty, -1));
// add metadata report table
LanguageTables.Add(ExistingTableNames.MetadataReport, new LanguageTableInfo(NewTableNames.MetadataReport, string.Empty, -1));
//Insert new Language record into Language table.
this.DBConnection.ExecuteNonQuery(DIQueries.InsertLanguage(dataPrefix, languageName, languageCode, false, false));
//create new langauge table and update text field column according to column width.
foreach (string ExistingTableName in LanguageTables.Keys)
{
this.CreateTable(LanguageTables[ExistingTableName].TableName, ExistingTableName);
if (!string.IsNullOrEmpty(LanguageTables[ExistingTableName].ColumnName))
{
this.UpdateTextField(LanguageTables[ExistingTableName]);
}
}
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
