/// <summary>
/// Generates blank DES
/// </summary>
/// <param name="singleWorkbook"></param>
/// <param name="dBConnection"></param>
/// <param name="dBQueries"></param>
/// <param name="userSelection"></param>
/// <param name="sortedFields"></param>
/// <param name="xlsFileNameWPath"></param>
/// <param name="includeGUID"></param>
/// <param name="languageFileNameWPath"></param>
/// <returns></returns>
private DataView GetBlankDESDataView(DataView blankDESDataView, string indicatorNId, string unitNId, bool singleWorkbook, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, bool includeGUID)
{
DataView RetVal = null;
string SqlQuery = string.Empty;
string OriginalSubgroupNIds = string.Empty;
DataTable AutoSubgroupValsTable = null;
IndicatorInfo IndicatorInfoObj;
IndicatorBuilder IndicatorBuilderObj;
UnitInfo UnitInfoObj;
UnitBuilder UnitBuilderObj;
DI6SubgroupValBuilder SGValBuilder;
DI6SubgroupValInfo SGValINfo;
DataTable DT;
try
{
// 1. Get blank DES dataview only if it is null
if (blankDESDataView == null)
{
blankDESDataView = GetBlankDESDataViewWithoutIUS(userSelection, dBConnection, dBQueries);
}
// 2. get dAtaview for the given Indicator + Unit.
try
{
// 3. Preserve original SubgroupNIds into a temp
OriginalSubgroupNIds = userSelection.SubgroupValNIds;
// 4. If SubgroupVal is blank, then get all subgroups for which given IU are combined as IUS.
if (userSelection.SubgroupValNIds.Length == 0)
{
if (userSelection.ShowIUS & userSelection.IndicatorNIds.Length > 0)
{
// 4.1 Get SubgroupNIDs for given IUSNId as userSelection.IndicatorNId
AutoSubgroupValsTable = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUS(FilterFieldType.NId, userSelection.IndicatorNIds, FieldSelection.Light));
// 4.2 Set filter for given I, U
AutoSubgroupValsTable.DefaultView.RowFilter = Indicator.IndicatorNId + " = " + indicatorNId + " AND " + Unit.UnitNId + " = " + unitNId;
AutoSubgroupValsTable = AutoSubgroupValsTable.DefaultView.ToTable();
}
else
{
// 4.3 Get SubgroupNIDs for given Indicator + Unit
AutoSubgroupValsTable = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUSNIdByI_U_S(indicatorNId, unitNId, string.Empty));
}
// 4.4 Set those SubgroupNIds into userSelection
userSelection.SubgroupValNIds = DIExport.DataColumnValuesToString(AutoSubgroupValsTable, SubgroupVals.SubgroupValNId);
}
//////// get IUSNIds on the basis of IndicatorsNId,UnitsNId and subgroupValSNId
//////SqlQuery = dBQueries.IUS.GetIUSByI_U_S(indicatorNId, unitNId, userSelection.SubgroupValNIds);
//////IUSTable = dBConnection.ExecuteDataTable(SqlQuery);
//////IUSNIds = DIConnection.GetDelimitedValuesFromDataTable(IUSTable, Indicator_Unit_Subgroup.IUSNId);
// **************************************************************************
// get indicator,unit and subgroup info
IndicatorBuilderObj = new IndicatorBuilder(dBConnection, dBQueries);
IndicatorInfoObj = IndicatorBuilderObj.GetIndicatorInfo(FilterFieldType.NId, indicatorNId, FieldSelection.Light);
UnitBuilderObj = new UnitBuilder(dBConnection, dBQueries);
UnitInfoObj = UnitBuilderObj.GetUnitInfo(FilterFieldType.NId, unitNId);
SGValBuilder = new DI6SubgroupValBuilder(dBConnection, dBQueries);
// 5. process and update filteredDataview for all subgroup nids
if (RetVal == null)
{
RetVal = new DataView(blankDESDataView.Table.Copy());
RetVal.Table.Clear();
// add subgroup columns
if (includeGUID)
{
RetVal.Table.Columns.Add(SubgroupVals.SubgroupValGId);
}
RetVal.Table.Columns.Add(SubgroupVals.SubgroupVal);
// check and insert columns for indicator & unit
if (!(RetVal.Table.Columns.Contains(Indicator.IndicatorName)))
{
RetVal.Table.Columns.Add(Indicator.IndicatorName);
}
if (!(RetVal.Table.Columns.Contains(Indicator.IndicatorGId)))
{
RetVal.Table.Columns.Add(Indicator.IndicatorGId);
}
if (!(RetVal.Table.Columns.Contains(Unit.UnitName)))
{
RetVal.Table.Columns.Add(Unit.UnitName);
}
if (!(RetVal.Table.Columns.Contains(Unit.UnitGId)))
{
RetVal.Table.Columns.Add(Unit.UnitGId);
}
// set indicator and unit values
RetVal.Table.Columns[Indicator.IndicatorGId].DefaultValue = IndicatorInfoObj.GID;
RetVal.Table.Columns[Indicator.IndicatorName].DefaultValue = IndicatorInfoObj.Name;
RetVal.Table.Columns[Unit.UnitGId].DefaultValue = UnitInfoObj.GID;
RetVal.Table.Columns[Unit.UnitName].DefaultValue = UnitInfoObj.Name;
}
else
{
RetVal.Table.Clear();
}
foreach (string SGNid in DICommon.SplitString(userSelection.SubgroupValNIds, ","))
{
//////// 5.1 if rows are morethan 65500 then dont add more rows
//////if (RetVal.Table.Rows.Count > 65500)
//////{
////// break;
//////}
// 5.2 get subgroup val info
SGValINfo = SGValBuilder.GetSubgroupValInfo(FilterFieldType.NId, SGNid);
DT = blankDESDataView.Table.Copy();
DT.Clear();
// 5.3 add subgroup columns
if (includeGUID)
{
DT.Columns.Add(SubgroupVals.SubgroupValGId);
DT.Columns[SubgroupVals.SubgroupValGId].DefaultValue = SGValINfo.GID;
}
DT.Columns.Add(SubgroupVals.SubgroupVal);
DT.Columns[SubgroupVals.SubgroupVal].DefaultValue = SGValINfo.Name;
// 5.4 merge blank DES data view
DT.Merge(blankDESDataView.Table);
// 5.5 update Subgroup in BlankDESDataView
RetVal.Table.Merge(DT);
}
// **************************************************************************
//////// 5.6 Limit DataRows to 65,536 as Excels sheet has 65,536 rows limit.
//////if (RetVal.Table.Rows.Count > 65500)
//////{
////// for (int i = RetVal.Table.Rows.Count - 1; i > 65500; i--)
////// {
////// RetVal.Table.Rows[i].Delete();
////// }
//////}
//////RetVal.Table.AcceptChanges();
// 5.7 Set original SubgroupNIDs back into userSelection
userSelection.SubgroupValNIds = OriginalSubgroupNIds;
////////// 5.9 Rename GID columns if not required.
////////if (!(includeGUID))
////////{
//////// FilteredDataView.Table.Columns[Indicator.IndicatorGId].ColumnName = "I_GID";
//////// FilteredDataView.Table.Columns[Unit.UnitGId].ColumnName = "U_GID";
//////// if (FilteredDataView.Table.Columns.Contains(SubgroupVals.SubgroupValGId))
//////// {
//////// FilteredDataView.Table.Columns[SubgroupVals.SubgroupValGId].ColumnName = "S_GID";
//////// }
////////}
////////// 5.10 Update Sector , Class in DataView
////////DIExport.AddSectorClassInDataView(ref FilteredDataView, dBConnection, dBQueries, indicatorNId, unitNId);
////////FilteredDataView.Table.AcceptChanges();
}
catch (Exception ex)
{
}
finally
{
if (RetVal == null)
{
RetVal = (new DataTable()).DefaultView;
}
}
}
catch (Exception ex)
{
}
finally
{
if (RetVal == null)
{
RetVal = (new DataTable()).DefaultView;
}
}
return RetVal;
}
private void ProcessMappedUnits()
{
DIConnection SrcDBConnection = null;
DIQueries SrcDBQueries = null;
UnitBuilder TrgUnitBilder = null;
UnitInfo SrcUnitInfoObj = null;
UnitBuilder SrcUnitBuilderObj = null;
DataTable Table = null;
string SourceFileWPath = string.Empty;
int TrgUnitNid = 0;
if (this.MappedTables.ContainsKey(TemplateMergeControlType.Unit))
{
TrgUnitBilder = new UnitBuilder(this.DBConnection, this.DBQueries);
foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.Unit].MappedTable.MappedTable.Rows)
{
Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportUnits(Convert.ToString(Row[MergetTemplateConstants.Columns.UNMATCHED_COL_Prefix + Unit.UnitNId])));
TrgUnitNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + Unit.UnitNId]);
if (Table != null && Table.Rows.Count > 0)
{
SourceFileWPath = Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);
SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty);
SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection);
// get unit info
SrcUnitBuilderObj = new UnitBuilder(SrcDBConnection, SrcDBQueries);
SrcUnitInfoObj = SrcUnitBuilderObj.GetUnitInfo(FilterFieldType.NId, Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID]));
// Import Mapped Unit Values
TrgUnitBilder.ImportMappedUnitInformation(SrcUnitInfoObj, SrcUnitInfoObj.Nid, TrgUnitNid, SrcDBQueries, SrcDBConnection);
SrcDBConnection.Dispose();
}
}
}
}
/// <summary>
/// Returns instance of IUSInfo.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public IUSInfo GetIUSInfo(FilterFieldType filterClause, string filterText, FieldSelection selectionType)
{
string Query = string.Empty;
IUSInfo RetVal = new IUSInfo();
IndicatorInfo IndicatorObject = new IndicatorInfo();
UnitInfo UnitObject = new UnitInfo();
IndicatorBuilder IndicatorBuilderObj = null;
UnitBuilder UnitBuilderObj = null;
DI6SubgroupValBuilder SubgroupValBuilderObj = null;
DI6SubgroupValInfo SubgroupValObject = new DI6SubgroupValInfo();
int IndicatorNid = 0;
int UnitNid = 0;
int SGNid = 0;
int MinVal = 0;
int MaxVal = 0;
DataTable Table = null;
try
{
//get IUS information
Query = this.DBQueries.IUS.GetIUS(filterClause, filterText, selectionType);
Table = this.DBConnection.ExecuteDataTable(Query);
//set IUS info
if (Table != null)
{
if (Table.Rows.Count > 0)
{
MinVal = 0;
MaxVal = 0;
// initialize builder objects
IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
SubgroupValBuilderObj = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
// set IUS properties
//-- set maximum value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue])))
{
MaxVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
}
//-- Set Minmum Value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MinValue])))
{
MinVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
}
RetVal.Maximum = MaxVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
RetVal.Minimum = MinVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
RetVal.Nid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IUSNId]);
// set indicator, unit and subgroup info
IndicatorNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IndicatorNId]);
UnitNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.UnitNId]);
SGNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.SubgroupValNId]);
RetVal.IndicatorInfo = IndicatorBuilderObj.GetIndicatorInfo(FilterFieldType.NId, IndicatorNid.ToString(), FieldSelection.Light);
RetVal.UnitInfo = UnitBuilderObj.GetUnitInfo(FilterFieldType.NId, UnitNid.ToString());
RetVal.SubgroupValInfo = SubgroupValBuilderObj.GetSubgroupValInfo(FilterFieldType.NId, SGNid.ToString());
}
}
}
catch (Exception)
{
RetVal = null;
}
return RetVal;
}
/// <summary>
/// Process Target Units which matched with Source Units
/// </summary>
public void ProcessMatchedUnits()
{
DataTable Table = null;
UnitBuilder UnitBuilderObj = null;
UnitBuilder SrcUnitBuilderObj = null;
UnitInfo SrcUnitInfoObj = null;
Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>();
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this.DBConnection.ExecuteDataTable(this.TemplateQueries.GetMatchedUnit());
//-- Step 2:Initialise Indicator Builder with Target DBConnection
UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
//-- 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 unit info
SrcUnitBuilderObj = new UnitBuilder(SourceDBConnection, SourceDBQueries);
SrcUnitInfoObj = SrcUnitBuilderObj.GetUnitInfo(FilterFieldType.NId, Convert.ToString(Row[Unit.UnitNId]));
// import unit
UnitBuilderObj.ImportUnit(SrcUnitInfoObj, Convert.ToInt32(Row[Unit.UnitNId]), SourceDBQueries, SourceDBConnection);
}
catch (Exception ex) { ExceptionFacade.ThrowException(ex); }
finally
{
if (SourceDBConnection != null)
SourceDBConnection.Dispose();
if (SourceDBQueries != null)
SourceDBQueries.Dispose();
}
}
}