/// <summary>
/// Returns the instance of SubgroupInfo on the basis of Subgroup Nid
/// </summary>
/// <param name="sourceQuery"></param>
/// <param name="sourceDBConnection"></param>
/// <param name="subgroupNid"></param>
/// <returns></returns>
public static DI6SubgroupInfo GetSubgroupInfoByNid(DIQueries sourceQuery, DIConnection sourceDBConnection, int subgroupNid)
{
DI6SubgroupInfo RetVal = new DI6SubgroupInfo();
RetVal = DI6SubgroupBuilder.GetSubgroupInfo(sourceQuery, sourceDBConnection, FilterFieldType.NId, subgroupNid.ToString());
return(RetVal);
}
/// <summary>
/// Deletes subgroup type and associated Subgroup, SubgroupVal, SubgroupValSubgroup, Indicator_Uni_Subgroup and IC_IUS
/// </summary>
/// <param name="NIDs">Comma separated NIDs</param>
public void DeleteSubgroupType(string NIDs)
{
string SqlQuery = string.Empty;
string AssociatedSubgroupNIds = string.Empty;
DITables TablesName;
DI6SubgroupBuilder SGBuilder;
try
{
// Step 1:Delete subgroup type
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TablesName = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.SubgroupTypes.Delete.DeleteSubgroupType(TablesName.SubgroupType, NIDs);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
// Step 2: Delete associated records from Subgroup and Subgroup Builder will automatically delete records from Subgroup, SubgroupValSubgroup, Indicator_Unit_Subgroup and IC_IUS.
SGBuilder = new DI6SubgroupBuilder(this.DBConnection, this.DBQueries);
// Get all associated SubgroupNIds
AssociatedSubgroupNIds = SGBuilder.GetAllAssociatedSubgroupNIds(NIDs);
if (!string.IsNullOrEmpty(AssociatedSubgroupNIds))
{
SGBuilder.DeleteSubgroup(AssociatedSubgroupNIds);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <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)
{
DI6SubgroupBuilder SGBuilderObj = new DI6SubgroupBuilder(this._TargetDBConnection, this._TargetDBQueries);
DI6SubgroupInfo SourceDBSubgroup;
DataRow Row;
int ProgressBarValue = 0;
foreach (string Nid in selectedNids)
{
try
{
//get subgroup from source table
Row = this.SourceTable.Select(Subgroup.SubgroupNId + "=" + Nid)[0];
SourceDBSubgroup = new DI6SubgroupInfo();
SourceDBSubgroup.Name = DICommon.RemoveQuotes(Row[Subgroup.SubgroupName].ToString());
SourceDBSubgroup.GID = Row[Subgroup.SubgroupGId].ToString();
SourceDBSubgroup.Global = Convert.ToBoolean(Row[Subgroup.SubgroupGlobal]);
SourceDBSubgroup.Nid = Convert.ToInt32(Row[Subgroup.SubgroupNId]);
SourceDBSubgroup.Type = Convert.ToInt32(Row[Subgroup.SubgroupType]);
//import into target database
SGBuilderObj.ImportSubgroup(SourceDBSubgroup, SourceDBSubgroup.Nid, this.SourceDBQueries, this.SourceDBConnection);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
this.RaiseIncrementProgessBarEvent(ProgressBarValue);
ProgressBarValue++;
}
}
public override void Import(string selectedNids)
{
DataTable Table = null;
int ProgressCounter = 0;
DI6SubgroupBuilder SGBuilderObj = null;
DI6SubgroupInfo SGInfoObj = null;
Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>();
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
DI6SubgroupBuilder SrcSGBuilder=null;
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportSubgroupDimensionValues(selectedNids));
//-- Step 2:Initialise DI6SubgroupBuilder Builder with Target DBConnection
SGBuilderObj = new DI6SubgroupBuilder(this.TargetDBConnection, this.TargetDBQueries);
// Initialize progress bar
this.RaiseProgressBarInitialize(selectedNids.Split(',').GetUpperBound(0) + 1);
//-- 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 subgroup info
SrcSGBuilder = new DI6SubgroupBuilder(SourceDBConnection, SourceDBQueries);
SGInfoObj= SrcSGBuilder.GetSubgroupInfo(FilterFieldType.NId,Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]));
SGBuilderObj.ImportSubgroup(SGInfoObj, 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();
}
private void AddSubgroupNIds(DI6SubgroupBuilder DI6SGBuilder, SerializableDictionary<string, DI6SubgroupInfo> subgroups, List<string> SGNIds)
{
int SGNId = -1;
foreach (DI6SubgroupInfo SGInfo in subgroups.Values)
{
SGNId = DI6SGBuilder.GetSubgroupNid(SGInfo.GID, string.Empty);
if (SGNId > 0 && SGNIds.Contains(SGNId.ToString()) == false)
{
SGNIds.Add(SGNId.ToString());
}
}
}
/// <summary>
/// Returns cell mapping values
/// </summary>
/// <param name="selectedTableInfo"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <returns></returns>
public Mapping GetCellMapping(int rowIndex, int colIndex, Database dbDatabase)
{
Mapping RetVal = null;
Mapping ColumnMapping;
Mapping RowMapping;
int SGValNID = -1;
List<string> SGNIds = new List<string>();
DI6SubgroupBuilder DI6SGBuilder;
try
{
// Get mapping information
if (rowIndex >= 0 && colIndex >= 0)
{
ColumnMapping = this.ColumnsMapping[colIndex].Mappings.CellMap;
RowMapping = this.RowsMapping[rowIndex].Mappings.CellMap;
// Step1: copy column mapping
RetVal = ColumnMapping.Copy();
// Step2: set the blank mapping value with the values available in row's mapping
RetVal.ReplaceEmptyValues(RowMapping);
// Step3: IF SubgoupVal is empty then get subgroupval on the basis of column's subgroups NID + row's subgroups NID
if (string.IsNullOrEmpty(RetVal.SubgroupVal))
{
DI6SGBuilder = new DI6SubgroupBuilder(dbDatabase.DBConnection, dbDatabase.DBQueries);
// Step 3a: get subgroup nids(dimensionvalues nid)
this.AddSubgroupNIds(DI6SGBuilder, RetVal.Subgroups, SGNIds);
this.AddSubgroupNIds(DI6SGBuilder, RowMapping.Subgroups, SGNIds);
if (SGNIds.Count > 0)
{
// Step 3b: get subgroupval for the selected dimensions
DI6SubgroupValBuilder SGValBuilder = new DI6SubgroupValBuilder(dbDatabase.DBConnection, dbDatabase.DBQueries);
DI6SubgroupValInfo SGValInfo;
SGValNID = SGValBuilder.GetSubgroupValNIdBySugbroups(SGNIds);
if (SGValNID > 0)
{
SGValInfo = SGValBuilder.GetSubgroupValInfo(FilterFieldType.NId, SGValNID.ToString());
RetVal.SubgroupVal = SGValInfo.Name;
RetVal.SubgroupValGID = SGValInfo.GID;
}
}
}
// Step 4: set the blank mapping value with the values available in Default mapping
RetVal.ReplaceEmptyValues(this._DefaultMapping);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return RetVal;
}
private void ProcessMappedSubgroupDimValues()
{
DIConnection SrcDBConnection = null;
DIQueries SrcDBQueries = null;
DI6SubgroupBuilder TrgSubgroupsBuilder = null;
DI6SubgroupBuilder SourceSubgroupBuilder = null;
DI6SubgroupInfo SrcSubgroupInfo = null;
DataTable Table = null;
string SourceFileWPath = string.Empty;
int TrgSGDVNid = 0;
if (this.MappedTables.ContainsKey(TemplateMergeControlType.SubgroupDimensionsValue))
{
TrgSubgroupsBuilder = new DI6SubgroupBuilder(this.DBConnection, this.DBQueries);
foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.SubgroupDimensionsValue].MappedTable.MappedTable.Rows)
{
Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportSubgroupDimensionValues(Convert.ToString(Row[MergetTemplateConstants.Columns.UNMATCHED_COL_Prefix + Subgroup.SubgroupNId])));
TrgSGDVNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + Subgroup.SubgroupNId]);
if (Table != null && Table.Rows.Count > 0)
{
try
{
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 Subgroup Dimension Values Info
SourceSubgroupBuilder = new DI6SubgroupBuilder(SrcDBConnection, SrcDBQueries);
SrcSubgroupInfo = SourceSubgroupBuilder.GetSubgroupInfo(FilterFieldType.NId, Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID]));
// Import Mapped Subgroup Dimension Values
TrgSubgroupsBuilder.ImportSubgroupFrmMappedSubgroup(SrcSubgroupInfo, SrcSubgroupInfo.Nid, TrgSGDVNid, SrcDBQueries, SrcDBConnection);
}
finally
{
if (SrcDBConnection != null)
{
SrcDBConnection.Dispose();
SrcDBQueries.Dispose();
}
}
}
}
}
}
/// <summary>
/// Deletes subgroup type and associated Subgroup, SubgroupVal, SubgroupValSubgroup, Indicator_Uni_Subgroup and IC_IUS
/// </summary>
/// <param name="NIDs">Comma separated NIDs</param>
public void DeleteSubgroupType(string NIDs)
{
string SqlQuery = string.Empty;
string AssociatedSubgroupNIds = string.Empty;
DITables TablesName;
DI6SubgroupBuilder SGBuilder;
try
{
// Step 1:Delete subgroup type
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
TablesName = new DITables(this.DBQueries.DataPrefix, "_" + Row[Language.LanguageCode].ToString());
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.SubgroupTypes.Delete.DeleteSubgroupType(TablesName.SubgroupType, NIDs);
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
// Step 2: Delete associated records from Subgroup and Subgroup Builder will automatically delete records from Subgroup, SubgroupValSubgroup, Indicator_Unit_Subgroup and IC_IUS.
SGBuilder=new DI6SubgroupBuilder(this.DBConnection,this.DBQueries);
// Get all associated SubgroupNIds
AssociatedSubgroupNIds =SGBuilder.GetAllAssociatedSubgroupNIds(NIDs);
if(!string.IsNullOrEmpty(AssociatedSubgroupNIds))
{
SGBuilder.DeleteSubgroup(AssociatedSubgroupNIds);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Returns instance of SubgrouopInfo
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public DI6SubgroupInfo GetSubgroupInfo(FilterFieldType filterClause, string filterText)
{
return(DI6SubgroupBuilder.GetSubgroupInfo(this.DBQueries, this.DBConnection, filterClause, filterText));
}
public bool UpdateSubgroupValsInOthersSGDimensionInTables()
{
bool RetVal = false;
string SqlQuery = string.Empty;
DataTable SubgroupTable = null;
int SubgroupNid = 0;
int Others_SubgroupTypeNid = -1;
DI6SubgroupBuilder subgroupBuilder = new DI6SubgroupBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupInfo subgroupInfo = null;
try
{
subgroupBuilder = new DI6SubgroupBuilder(this.DBConnection,this.DBQueries);
Others_SubgroupTypeNid = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(this.DBQueries.SubgroupTypes.GetSubgroupTypes(FilterFieldType.GId, "'OTHERS'")));
if (Others_SubgroupTypeNid > 0)
{
SqlQuery = "select * from " + this.DBQueries.TablesName.SubgroupVals + " sv where " + SubgroupVals.SubgroupValNId + " not in(select " + SubgroupVals.SubgroupValNId + " from " + this.DBQueries.TablesName.SubgroupValsSubgroup + " svs where svs." + SubgroupVals.SubgroupValNId + "=sv." + SubgroupVals.SubgroupValNId + ")";
//Get those subgroupVals which is not exists in ut_subgroup_val_subgroup
DataTable SubgroupValsTable = this.DBConnection.ExecuteDataTable(SqlQuery);
foreach (DataRow SGRow in SubgroupValsTable.Rows)
{
SubgroupTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Subgroup.GetSubgroup(FilterFieldType.Name, "'" + Convert.ToString(SGRow[SubgroupVals.SubgroupVal]) + "'"));
if (SubgroupTable.Rows.Count == 0)
{
subgroupInfo = new DI6SubgroupInfo();
subgroupInfo.Name = Convert.ToString(SGRow[SubgroupVals.SubgroupVal]);
subgroupInfo.GID = Convert.ToString(SGRow[SubgroupVals.SubgroupValGId]);
subgroupInfo.Global = Convert.ToBoolean(SGRow[SubgroupVals.SubgroupValGlobal]);
subgroupInfo.Type = Others_SubgroupTypeNid;
SubgroupNid = subgroupBuilder.CheckNCreateSubgroup(subgroupInfo);
}
else
{
SubgroupNid = Convert.ToInt32(SubgroupTable.Rows[0][Subgroup.SubgroupNId]);
}
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.SubgroupValSubgroup.Insert.InsertSubgroupValRelation(this.DBQueries.DataPrefix, Convert.ToInt32(SGRow[SubgroupVals.SubgroupValNId]), SubgroupNid));
}
}
RetVal = true;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
/// <summary>
/// Process Matched Target Subgroup Dimension Values
/// </summary>
public void ProcessSubgroupDimensionValues()
{
DataTable Table = null;
DI6SubgroupBuilder SGBuilderObj = null;
DI6SubgroupInfo SourceSGInfoObj = null;
Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>();
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
DI6SubgroupBuilder SourceSGBuilder = null;
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this.DBConnection.ExecuteDataTable(this.TemplateQueries.GetMatchedSubgroupDimValues());
//-- Step 2:Initialise Indicator Builder with Target DBConnection
SGBuilderObj = new DI6SubgroupBuilder(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 subgroup info from source
SourceSGBuilder = new DI6SubgroupBuilder(SourceDBConnection, SourceDBQueries);
SourceSGInfoObj = SourceSGBuilder.GetSubgroupInfo(FilterFieldType.NId, Convert.ToString(Row[Subgroup.SubgroupNId]));
//import subgroup
SGBuilderObj.ImportSubgroup(SourceSGInfoObj, Convert.ToInt32(Row[Subgroup.SubgroupNId]), SourceDBQueries, SourceDBConnection);
}
catch (Exception ex) { ExceptionFacade.ThrowException(ex); }
finally
{
if (SourceDBConnection != null)
SourceDBConnection.Dispose();
if (SourceDBQueries != null)
SourceDBQueries.Dispose();
}
}
}
