/// <summary>
/// Inserts IUS into database
/// </summary>
/// <param name="indicatorGId"></param>
/// <param name="unitGId"></param>
/// <param name="subgroupGId"></param>
/// <returns></returns>
public int InsertIUSIntoDB(string indicatorGId, string unitGId, string subgroupGId)
{
int RetVal = -1;
IndicatorBuilder IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilder UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
IUSBuilder IUSObj = new IUSBuilder(this.DBConnection, this.DBQueries);
int IndicatorNID = -1;
int UnitNID = -1;
int SubgroupValNID = -1;
try
{
IndicatorNID = IndicatorBuilderObj.GetIndicatorNid(indicatorGId, string.Empty);
UnitNID = UnitBuilderObj.GetUnitNid(unitGId, string.Empty);
SubgroupValNID = SGBuilder.GetSubgroupValNid(subgroupGId, string.Empty);
if (IndicatorNID > 0 & UnitNID > 0 & SubgroupValNID > 0)
{
RetVal = IUSObj.InsertIUS(IndicatorNID, UnitNID, SubgroupValNID, 0, 0);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
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)
{
DataRow Row;
IUSInfo IUSInfoObject;
IUSBuilder IUSBuilderObject;
int ProgressBarValue = 0;
foreach (string Nid in selectedNids)
{
try
{
Row = this.SourceTable.Select(this.TagValueColumnName + "=" + Nid)[0];
IUSInfoObject = new IUSInfo();
IUSBuilderObject = new IUSBuilder(this._TargetDBConnection, this._TargetDBQueries);
IUSInfoObject.IndicatorInfo = this.GetIndicatorInfo(Row);
IUSInfoObject.UnitInfo = this.GetUnitInfo(Row);
IUSInfoObject.SubgroupValInfo = this.GetSubgroupValInfo(Row);
IUSBuilderObject.ImportIUS(IUSInfoObject, this.SourceDBQueries, this.SourceDBConnection);
IUSBuilderObject.UpdateISDefaultSubgroup(IUSInfoObject.IndicatorInfo.Nid, IUSInfoObject.UnitInfo.Nid);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
this.RaiseIncrementProgessBarEvent(ProgressBarValue);
ProgressBarValue++;
}
}
/// <summary>
/// Inserts or updates IUS into database
/// </summary>
/// <param name="indicatorGId"></param>
/// <param name="unitGId"></param>
/// <param name="subgroupGId"></param>
/// <param name="minValue"></param>
/// <param name="maxValue"></param>
/// <returns></returns>
public int InsertOrUpdateIUSIntoDBByName(string indicatorName, string unitName, string subgroupval, int minValue, int maxValue)
{
int RetVal = -1;
IndicatorBuilder IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilder UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
IUSBuilder IUSObj = new IUSBuilder(this.DBConnection, this.DBQueries);
int IndicatorNID = -1;
int UnitNID = -1;
int SubgroupValNID = -1;
try
{
IndicatorNID = IndicatorBuilderObj.GetIndicatorNid(string.Empty, indicatorName);
UnitNID = UnitBuilderObj.GetUnitNid(string.Empty, unitName);
SubgroupValNID = SGBuilder.GetSubgroupValNid(string.Empty, subgroupval);
if (IndicatorNID > 0 & UnitNID > 0 & SubgroupValNID > 0)
{
// check IUS combination already exists or not
RetVal = this.GetIUSNid(IndicatorNID, UnitNID, SubgroupValNID);
if (RetVal > 0)
{
//update record
this.UpdateIUS(IndicatorNID, UnitNID, SubgroupValNID, maxValue.ToString(), minValue.ToString(), RetVal);
}
else
{
// insert record
RetVal = IUSObj.InsertIUS(IndicatorNID, UnitNID, SubgroupValNID, minValue, maxValue);
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Deletes indicators and associated records from IUS and IC_IUS table
/// </summary>
/// <param name="indicatorNids"></param>
public void DeleteIndicator(string indicatorNids)
{
DITables TableNames;
IUSBuilder IUSBuilder;
MetaDataBuilder MetadataBuilderObject;
string AssocicatedIUSNIds = string.Empty;
try
{
IUSBuilder = new IUSBuilder(this.DBConnection, this.DBQueries);
// Step 1: Delete records from Indicator table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Delete.DeleteIndicator(TableNames.Indicator, indicatorNids));
}
// Step 2: Delete records from IUS table
// Step2(a): Get all associated IUSNIds
AssocicatedIUSNIds = IUSBuilder.GetAllAssociatedIUSNids(indicatorNids, string.Empty, string.Empty);
// Step2(b): Delete all associated IUSNIds
IUSBuilder.DeleteIUS(AssocicatedIUSNIds);
// delete metadata
MetadataBuilderObject = new MetaDataBuilder(this.DBConnection, this.DBQueries);
MetadataBuilderObject.DeleteMetadata(indicatorNids, MetadataElementType.Indicator);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void ProcessIndicators()
{
try
{
if (string.IsNullOrEmpty(this.IndicatorNIDs) == false)
{
//- Get IUSNId for given Indicators.
string NewIUSNIds = Common.GetCommaSeperatedString(this.SourceDBConnection.ExecuteDataTable(this.SourceDBQueries.IUS.GetIUSByI_U_S(this.IndicatorNIDs, this.UnitNIDs, this.SubgroupValNIDs)), Indicator_Unit_Subgroup.IUSNId);
//-- Merge NewIUS with given IUS.
this.IUSNIDs = Common.MergeNIds(this.IUSNIDs, NewIUSNIds);
}
if (string.IsNullOrEmpty(this.IUSNIDs) == false)
{
IUSBuilder SourceIUSBuilder = new IUSBuilder(this.SourceDBConnection, this.SourceDBQueries);
IUSBuilder DestinationIUSBuilder = new IUSBuilder(this.DestDBConnection, this.DestDBQueries);
IUSInfo SourceIUSInfo = null;
// import each ius
foreach (string SourceIUSNid in DICommon.SplitString(this.IUSNIDs, ","))
{
// 1. get source ius info
SourceIUSInfo = SourceIUSBuilder.GetIUSInfo(FilterFieldType.NId, SourceIUSNid, FieldSelection.Light);
if (SourceIUSInfo != null && SourceIUSInfo.Nid > 0)
{
// 2. import ius into destination db
DestinationIUSBuilder.ImportIUS(SourceIUSInfo, this.SourceDBQueries, this.SourceDBConnection);
}
}
}
}
catch
{
}
}
/// <summary>
/// Deletes units and associated records from IUS a IC_IUS table table
/// </summary>
/// <param name="nids"></param>
public void DeleteUnit(string nids)
{
DITables TableNames;
IUSBuilder IUSBuilder;
string AssocicatedIUSNIds = string.Empty;
try
{
IUSBuilder = new IUSBuilder(this.DBConnection, this.DBQueries);
// Step 1: Delete records from Unit table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Unit.Delete.DeleteUnits(TableNames.Unit, nids));
}
// Step 2: Delete records from IUS table
// Step2(a): Get all associated IUSNIds
AssocicatedIUSNIds = IUSBuilder.GetAllAssociatedIUSNids(string.Empty, nids, string.Empty);
// Step2(b): Delete all associated IUSNIds
IUSBuilder.DeleteIUS(AssocicatedIUSNIds);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Inserts or updates IUS into database
/// </summary>
/// <param name="indicatorGId"></param>
/// <param name="unitGId"></param>
/// <param name="subgroupGId"></param>
/// <param name="minValue"></param>
/// <param name="maxValue"></param>
/// <returns></returns>
public int InsertOrUpdateIUSIntoDBByName(string indicatorName, string unitName, string subgroupval, int minValue, int maxValue)
{
int RetVal = -1;
IndicatorBuilder IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilder UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
IUSBuilder IUSObj = new IUSBuilder(this.DBConnection, this.DBQueries);
int IndicatorNID = -1;
int UnitNID = -1;
int SubgroupValNID = -1;
try
{
IndicatorNID = IndicatorBuilderObj.GetIndicatorNid(string.Empty, indicatorName);
UnitNID = UnitBuilderObj.GetUnitNid(string.Empty, unitName);
SubgroupValNID = SGBuilder.GetSubgroupValNid(string.Empty, subgroupval);
if (IndicatorNID > 0 & UnitNID > 0 & SubgroupValNID > 0)
{
// check IUS combination already exists or not
RetVal = this.GetIUSNid(IndicatorNID, UnitNID, SubgroupValNID);
if (RetVal > 0)
{
//update record
this.UpdateIUS(IndicatorNID, UnitNID, SubgroupValNID, maxValue.ToString(), minValue.ToString(), RetVal);
}
else
{
// insert record
RetVal = IUSObj.InsertIUS(IndicatorNID, UnitNID, SubgroupValNID, minValue, maxValue);
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return RetVal;
}
/// <summary>
/// Inserts IUS into database
/// </summary>
/// <param name="indicatorGId"></param>
/// <param name="unitGId"></param>
/// <param name="subgroupGId"></param>
/// <returns></returns>
public int InsertIUSIntoDB(string indicatorGId, string unitGId, string subgroupGId)
{
int RetVal = -1;
IndicatorBuilder IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilder UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
IUSBuilder IUSObj = new IUSBuilder(this.DBConnection, this.DBQueries);
int IndicatorNID = -1;
int UnitNID = -1;
int SubgroupValNID = -1;
try
{
IndicatorNID = IndicatorBuilderObj.GetIndicatorNid(indicatorGId, string.Empty);
UnitNID = UnitBuilderObj.GetUnitNid(unitGId, string.Empty);
SubgroupValNID = SGBuilder.GetSubgroupValNid(subgroupGId, string.Empty);
if (IndicatorNID > 0 & UnitNID > 0 & SubgroupValNID > 0)
{
RetVal = IUSObj.InsertIUS(IndicatorNID, UnitNID, SubgroupValNID, 0, 0);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return RetVal;
}
/// <summary>
/// Deletes indicators and associated records from IUS and IC_IUS table
/// </summary>
/// <param name="indicatorNids"></param>
public void DeleteIndicator(string indicatorNids)
{
DITables TableNames;
IUSBuilder IUSBuilder;
MetaDataBuilder MetadataBuilderObject;
string AssocicatedIUSNIds = string.Empty;
try
{
IUSBuilder = new IUSBuilder(this.DBConnection, this.DBQueries);
// Step 1: Delete records from Indicator table
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Delete.DeleteIndicator(TableNames.Indicator, indicatorNids));
}
// Step 2: Delete records from IUS table
// Step2(a): Get all associated IUSNIds
AssocicatedIUSNIds = IUSBuilder.GetAllAssociatedIUSNids(indicatorNids, string.Empty, string.Empty);
// Step2(b): Delete all associated IUSNIds
IUSBuilder.DeleteIUS(AssocicatedIUSNIds);
// delete metadata
MetadataBuilderObject = new MetaDataBuilder(this.DBConnection, this.DBQueries);
MetadataBuilderObject.DeleteMetadata(indicatorNids, MetadataElementType.Indicator);
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void UpdateDefaultSubgroupValue()
{
try
{
IUSBuilder IUSbuilderObj = new IUSBuilder(this.DBConnection, this.DBQueries);
IUSbuilderObj.UpdateISDefaultSubgroups();
}
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);
}
/// <summary>
/// To open or create DevInfo database
/// </summary>
/// <param name="fileNameWPath"></param>
/// <param name="datasetPrefix"></param>
/// <param name="langaugeCode"></param>
public void OpenDatabase(string fileNameWPath, string datasetPrefix, string langaugeCode)
{
DBConverterDecorator DBConverter;
//check file exists or not. If not exists then create it.
if (!File.Exists(fileNameWPath))
{
DIDatabase.CreateDevInfoDBFile(fileNameWPath);
// update the db schema
DBConverter = new DBConverterDecorator(fileNameWPath);
DBConverter.DoConversion(false);
DBConverter.Dispose();
}
this.SetDatabaseObjects(fileNameWPath, datasetPrefix, langaugeCode);
this.DIArea = new AreaBuilder(this.DBConnection, this.DBQueries);
this.DIIUS = new IUSBuilder(this.DBConnection, this.DBQueries);
this.DITimeperiod = new TimeperiodBuilder(this.DBConnection, this.DBQueries);
this.DISource = new SourceBuilder(this.DBConnection, this.DBQueries);
this.DIFootnote = new FootnoteBuilder(this.DBConnection, this.DBQueries);
}
/// <summary>
/// Returns instance of DIDatabase.
/// </summary>
/// <param name="connection">Instance of DIConnection which is already opened</param>
/// <param name="queries">Instance of DIQueries which is already opened</param>
public DIDatabase(DIConnection connection, DIQueries queries)
{
this.DBConnection = connection;
this.DBQueries = queries;
this.DIArea = new AreaBuilder(this.DBConnection, this.DBQueries);
this.DIIUS = new IUSBuilder(this.DBConnection, this.DBQueries);
this.DITimeperiod = new TimeperiodBuilder(this.DBConnection, this.DBQueries);
this.DISource = new SourceBuilder(this.DBConnection, this.DBQueries);
this.DIFootnote = new FootnoteBuilder(this.DBConnection, this.DBQueries);
}
/// <summary>
/// Update auto calculated columns ( IC table - Publisher, Year & title ,Indicator table- Data_Exists, area table - data_exist, IUS table - subgroup_nids & data_exist) into the database/template
/// </summary>
/// <returns></returns>
public bool UpdateAutoCalculatedFieldsInTables()
{
bool RetVal = false;
IndicatorClassificationBuilder ICBuilder;
IndicatorBuilder IndBuilder;
IUSBuilder IUSBuilderObj;
AreaBuilder AreaBuilderObj;
try
{
// 1. Indicator classification table - Publisher, year & title columns
ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries);
ICBuilder.UpdatePublisherTitleYear();
// 2. Indicator table - Data_exist column
IndBuilder = new IndicatorBuilder(this.DBConnection, this.DBQueries);
IndBuilder.UpdateDataExistValues();
// 3. IUS table- Subgroup_Nids & Data_Exist columns
IUSBuilderObj = new IUSBuilder(this.DBConnection, this.DBQueries);
IUSBuilderObj.UpdateSubgroupNids();
IUSBuilderObj.UpdateDataExistValues();
// 4. Area table- Data_Exist column
AreaBuilderObj = new AreaBuilder(this.DBConnection, this.DBQueries);
AreaBuilderObj.UpdateDataExistValues();
RetVal = true;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
private int ImportForIUS(DataRow row)
{
int RetVal = 0;
IUSInfo iusInfo = new IUSInfo();
IUSBuilder iusBuilder = new IUSBuilder(this._TargetDBConnection, this._TargetDBQueries);
iusInfo.IndicatorInfo = this.GetIndicatorInfo(row);
iusInfo.UnitInfo = this.GetUnitInfo(row);
iusInfo.SubgroupValInfo = this.GetSubgroupValInfo(row);
RetVal = iusBuilder.ImportIUS(iusInfo, this.SourceDBQueries, this.SourceDBConnection);
bool ISDefault = false;
if (!string.IsNullOrEmpty(Convert.ToString(row[Indicator_Unit_Subgroup.IsDefaultSubgroup])))
{
ISDefault = Convert.ToBoolean(row[Indicator_Unit_Subgroup.IsDefaultSubgroup]);
}
iusBuilder.UpdateIUSISDefaultSubgroup(RetVal.ToString(), ISDefault);
iusBuilder.UpdateISDefaultSubgroup(iusInfo.IndicatorInfo.Nid, iusInfo.UnitInfo.Nid);
//if (importSector)
//{
// //create indicator classification
// this.ImportSector(row);
//}
return RetVal;
}
/// <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;
}
