//-- Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column.
public static void SeparateTextualandNemericData(DIConnection dbConnection, DIQueries dbQueries)
{
string SqlQuery = string.Empty;
try
{
if (ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data))
{
//--Update Textual value true if data value is not Numeric
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.IsTextualData + " = 1 WHERE NOT ISNUMERIC(" + Data.DataValue + ")";
dbConnection.ExecuteNonQuery(SqlQuery);
//-- move orgData_Value values into Data_Value
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " Set "+ Constants.Data.Orginal_Data_valueColumn + "=" + Data.DataValue + " WHERE " + Data.IsTextualData + " = 0";
dbConnection.ExecuteNonQuery(SqlQuery);
//--move orgTextual_Data_value into Textual_Data_value if ISTextual is true
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " Set " + Constants.Data.Orginal_Textual_Data_valueColumn + " = " + Data.DataValue + " WHERE " + Data.IsTextualData + "<>0";
dbConnection.ExecuteNonQuery(SqlQuery);
//2. Remove Data_Value column and rename orgTextual_Data_Value and orgData_Value to Textual_Data_Value and Data_value"
DIDataValueHelper.RemoveDataValueAndRenameOrgColumns(dbConnection, dbQueries);
}
}
catch (Exception ex)
{
throw ex;
}
}
public override void Import(string selectedNids)
{
DataTable Table = null;
int ProgressCounter = 0;
DI6SubgroupTypeBuilder SGBuilderObj = null;
DI6SubgroupTypeInfo SGInfoObj = null;
Dictionary <string, DataRow> FileWithNids = new Dictionary <string, DataRow>();
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
DI6SubgroupTypeBuilder SourceSGTypeBuilder = null;
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportSubgroupDimensions(selectedNids));
//-- Step 2:Initialise Indicator Builder with Target DBConnection
SGBuilderObj = new DI6SubgroupTypeBuilder(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 type info from source
SourceSGTypeBuilder = new DI6SubgroupTypeBuilder(SourceDBConnection, SourceDBQueries);
SGInfoObj = SourceSGTypeBuilder.GetSubgroupTypeInfoByNid(Convert.ToInt32(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]));
// import subgroup type only if doesnt exist
SGBuilderObj.ImportSubgroupType(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();
}
/// <summary>
/// Rename Textual_Data_Value & Data_value TO orgTextual_Data_value & orgData_Value respectively
/// </summary>
/// <returns></returns>
private static bool RenameOriganalDataTableColumnInDatabase(DIConnection dbConnection, DIQueries dbQueries, string oldColumn, string newColumn, string newColumnDataType)
{
bool RetVal = true;
string SqlQuery = string.Empty;
try
{
//CatalogClass CatalogClassObj = new CatalogClass();
//-- cat.ActiveConnection = connString <-- Error here -- Cannot assign to cat.ActiveConnection directly.// Use code at below to assign connection string in C#.
//CatalogClassObj.let_ActiveConnection(dbConnection.ConnectionStringParameters.GetConnectionString());
//CatalogClassObj.Tables[dbQueries.TablesName.Data].Columns[oldColumn].Name = newColumn;
//CatalogClassObj = null;
//-- RENAME COLUMN
SqlQuery = "ALTER TABLE " + dbQueries.TablesName.Data + " ADD COLUMN " + newColumn + " " + newColumnDataType;
dbConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + newColumn + "=" + oldColumn;
dbConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "ALTER TABLE " + dbQueries.TablesName.Data + " DROP COLUMN " + oldColumn;
dbConnection.ExecuteNonQuery(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
return(RetVal);
}
/// <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>
/// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue
/// and merge both column value into new column Data_Value
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
public static void MergeTextualandNumericDataValueColumn(DIConnection dbConnection, DIQueries dbQueries)
{
try
{
if (!ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data))
{
//1. Rename Textual_Data_Value & Data_value to orgTextual_Data_value & orgData_Value respectively
RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.TextualDataValue, Constants.Data.Orginal_Textual_Data_valueColumn, " Memo");
RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.DataValue, Constants.Data.Orginal_Data_valueColumn, " Double ");
System.Threading.Thread.Sleep(100);
//2. Create new column Data_Value of memo data type
string SqlQuery = "ALTER Table " + dbQueries.TablesName.Data + " Add Column " + Data.DataValue + " Memo NULL";
dbConnection.ExecuteNonQuery(SqlQuery);
System.Threading.Thread.Sleep(10);
//3. Merge all data values into Data_Value column
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Data_valueColumn;
dbConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Textual_Data_valueColumn + " WHERE " + Data.IsTextualData + "=" + true;
dbConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// /// Deletes metadata category records from metadata category and metadata report tables
/// </summary>
/// <param name="categoryNid"></param>
public void DeleteMetadataCategory(int categoryNid)
{
string LanguageCode = string.Empty;
DITables TablesName;
string CategoryNids = categoryNid.ToString();
DataTable SubCategoriesTable = null;
try
{
// Get sub categories nid
SubCategoriesTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Search, Metadata_Category.ParentCategoryNId + "=" + categoryNid.ToString()));
if (SubCategoriesTable.Rows.Count > 0)
{
CategoryNids += "," + DIConnection.GetDelimitedValuesFromDataTable(SubCategoriesTable, Metadata_Category.CategoryNId);
}
foreach (DataRow languageRow in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = languageRow[Language.LanguageCode].ToString();
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
// Delete records from metadata category table
this.DBConnection.ExecuteNonQuery(DI_LibDAL.Queries.MetadataCategory.Delete.DeleteMetadataCategory(TablesName.MetadataCategory, CategoryNids));
// Delete records from metadata report table
this.DBConnection.ExecuteNonQuery(DI_LibDAL.Queries.MetadataReport.Delete.DeleteMetadataRecordsByCategories(TablesName.MetadataReport, CategoryNids));
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Delete Notes Classificaton by Classification Nid
/// </summary>
/// <param name="classificationNids">Comma separated NIDs</param>
/// <returns >Count Of Records Updated</returns>
public int DeleteNotesClassification(string classificationNids)
{
int RetVal = 0;
string LanguageCode = string.Empty;
DITables TableNames;
string SqlQuery = string.Empty;
string AssociatedNotesNids = string.Empty;
NotesBuilder NoteBuilder = new NotesBuilder(this.DBConnection, this.DBQueries);
try
{
foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = Convert.ToString(Row[Language.LanguageCode]);
TableNames = new DITables(this.DBQueries.DataPrefix, "_" + LanguageCode);
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.Notes.Delete.DeleteFrmNotesClassification(TableNames.NotesClassification, classificationNids);
RetVal = this.DBConnection.ExecuteNonQuery(SqlQuery);
AssociatedNotesNids = DIConnection.GetDelimitedValuesFromDataTable(NoteBuilder.GetNotesByNotesNid(string.Empty, string.Empty, string.Empty, classificationNids, CheckedStatus.All, FieldSelection.Light), Notes.NotesNId);
NoteBuilder.DeleteComments(AssociatedNotesNids);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
return(RetVal);
}
/// <summary>
/// Import HighISGood from Source database
/// </summary>
/// <param name="sourceDBConnection"></param>
/// <param name="sourceDBQueries"></param>
public void ImportHighISGood(DIConnection sourceDBConnection, DIQueries sourceDBQueries)
{
string IUSNids = string.Empty;
DataTable IndTable = null;
//-- Get IUS from Source Database
IndTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.Indicators.GetIndicator(FilterFieldType.None, string.Empty, FieldSelection.Light));
foreach (DataRow Row in IndTable.Rows)
{
bool HighISGood = false;
int IndNid = this.GetIndicatorNid(Convert.ToString(Row[Indicator.IndicatorGId]), Convert.ToString(Row[Indicator.IndicatorName]));
if (IndNid > 0)
{
if (!string.IsNullOrEmpty(Convert.ToString(Row[Indicator.HighIsGood])))
{
HighISGood = Convert.ToBoolean(Row[Indicator.HighIsGood]);
}
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Update.UpdateISHighGood(this.DBConnection.ConnectionStringParameters.ServerType, this.DBQueries.TablesName.Indicator, IndNid, HighISGood));
}
}
}
private void ConnectToDatabaseNCreateConverter(string fileNameWPath)
{
DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
this.CreateDBConverter(DBConnection, DBQueries);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="fileNameWPath"></param>
public DI6ToDI5Converter(string fileNameWPath)
{
this.DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
this.DBQueries = new DIQueries(this.DBConnection.DIDataSetDefault(), this.DBConnection.DILanguageCodeDefault("UT_"));
this.FilenameWPath = fileNameWPath;
}
/// <summary>
/// Returns the instance of BaseListViewSource. Based on Factory method.
/// </summary>
/// <param name="listType"></param>
/// <param name="showCheckBoxColumn"></param>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <returns></returns>
public static BaseListViewSource CreateListViewSource(ListViewType listType, DIConnection dbConnection, DIQueries dbQueries)
{
BaseListViewSource RetVal = null;
UserPreference DIUserPreferencce = new UserPreference();
RetVal = ListViewSourceFactory.CreateListViewSource(listType, dbConnection, dbQueries, DIUserPreferencce);
return(RetVal);
}
/// <summary>
/// Returns the instance of BaseListViewSource. Based on Factory method.
/// </summary>
/// <param name="listType"></param>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="userprefenece"></param>
/// <returns></returns>
public static BaseListViewSource CreateListViewSource(ListViewType listType, DIConnection dbConnection, DIQueries dbQueries, UserPreference userprefenece)
{
BaseListViewSource RetVal = null;
switch (listType)
{
case ListViewType.Indicator:
RetVal = new IndicatorListViewSource();
RetVal.RaiseInitializedIndicatorListViewEvent();
break;
case ListViewType.Timeperiod:
RetVal = new TimeperiodListViewSource();
RetVal.RaiseInitializedTimperiodListViewEvent();
break;
case ListViewType.Area:
RetVal = new AreaListViewSource();
RetVal.RaiseInitializedAreaListViewEvent();
break;
case ListViewType.Source:
RetVal = new DISourceListViewSource();
RetVal.RaiseInitializedDISourceListViewEvent();
break;
case ListViewType.Unit:
RetVal = new UnitListViewSource();
RetVal.RaiseInitializedUnitListViewEvent();
break;
case ListViewType.SubgroupVal:
RetVal = new SubgroupValListViewSource();
RetVal.RaiseInitializedUnitListViewEvent();
break;
case ListViewType.IC:
RetVal = new ICListViewSource();
break;
default:
break;
}
if (RetVal != null)
{
//set variables
RetVal.DBConnection = dbConnection;
RetVal.DBQueries = dbQueries;
RetVal.DIUserPreference = userprefenece;
//set columns
RetVal.SetColumnInfo();
}
return(RetVal);
}
public IUSBuilder(DIConnection connection, DIQueries queries)
{
this.DBConnection = connection;
this.DBQueries = queries;
this.DIIndicator = new IndicatorBuilder(connection, queries);
this.DIUnit = new UnitBuilder(connection, queries);
this.DISubgroupVal = new DI6SubgroupValBuilder(connection, queries);
DIConnection.ConnectionType = this.DBConnection.ConnectionStringParameters.ServerType;
}
public override void Import(string selectedNids)
{
DataTable Table = null;
int ProgressCounter = 0;
AreaBuilder AreaBuilderObj = null;
AreaInfo AreaInfoObj = 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.GetImportAreas(this._CurrentTemplateFileNameWPath,selectedNids));
//-- Step 2:Initialise Indicator Builder with Target DBConnection
AreaBuilderObj = new AreaBuilder(this.TargetDBConnection, this.TargetDBQueries);
////-- Step 3: Import Nids for each SourceFile
//foreach (DataRow Row in Table.Copy().Rows)
//{
try
{
SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, this._CurrentTemplateFileNameWPath, String.Empty, MergetTemplateConstants.DBPassword);
SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection);
// AreaInfoObj = this.GetIndicatorInfo(Row);
//AreaBuilderObj.ImportArea(selectedNids, 1, SourceDBConnection, SourceDBQueries);
//AreaBuilderObj.ImportAreaMaps(selectedNids, 1, SourceDBConnection, SourceDBQueries);
AreaBuilderObj.ImportArea(selectedNids, DICommon.SplitString(selectedNids, ",").Length, SourceDBConnection, SourceDBQueries, true);
ProgressCounter += 1;
this.RaiseProgressBarIncrement(ProgressCounter);
}
catch (Exception ex) { ExceptionFacade.ThrowException(ex); }
finally
{
if (SourceDBConnection != null)
{
SourceDBConnection.Dispose();
}
if (SourceDBQueries != null)
{
SourceDBQueries.Dispose();
}
}
//}
this._UnmatchedTable = this.GetUnmatchedTable();
this._AvailableTable = this.GetAvailableTable();
// Close ProgressBar
this.RaiseProgressBarClose();
}
public override void Import(string selectedNids)
{
DataTable Table = null;
int ProgressCounter = 0;
UnitBuilder TrgUnitBuilderObj = null;
UnitBuilder SourceUnitBuilderObj = null;
UnitInfo SrcUnitInfoObj = null;
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportUnits(selectedNids));
//-- Step 2:Initialise Indicator Builder with Target DBConnection
TrgUnitBuilderObj = new UnitBuilder(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);
SourceUnitBuilderObj = new UnitBuilder(SourceDBConnection, SourceDBQueries);
SrcUnitInfoObj = SourceUnitBuilderObj.GetUnitInfo(FilterFieldType.NId, Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]));
// Import Unit from Source
TrgUnitBuilderObj.ImportUnit(SrcUnitInfoObj, 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 static void RemoveDataValueAndRenameOrgColumns(DIConnection dbConnection, DIQueries dbQueries)
{
string SqlQuery = string.Empty;
//2. Remove Data_Value column and rename orgTextual_Data_Value and orgData_Value to Textual_Data_Value and Data_value"
SqlQuery = "ALTER TABLE " + dbQueries.TablesName.Data + " DROP COLUMN " + Data.DataValue;
dbConnection.ExecuteNonQuery(SqlQuery);
DIDataValueHelper.RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Constants.Data.Orginal_Textual_Data_valueColumn, Data.TextualDataValue, " Memo");
DIDataValueHelper.RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Constants.Data.Orginal_Data_valueColumn, Data.DataValue, " Double ");
}
public static void ClearIcon(DIConnection dbConnection, string dataPrefix, IconElementType elementType)
{
try
{
dbConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.ClearIcon(dataPrefix, DIIcons.Elements[elementType].ToString()));
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void CreateDBConverter(DIConnection dbConnection, DIQueries dbQueries)
{
#region "-- change here --"
this.DatabaseConverter = new DI7_0_0_1DBConverter(dbConnection, dbQueries);
#endregion
// add event handlers
this.DatabaseConverter.ProcessInfo += new DevInfo.Lib.DI_LibBAL.DA.DML.ProcessInfoDelegate(DatabaseConverter_ProcessInfo);
this.DatabaseConverter.ProcessStarted += new DevInfo.Lib.DI_LibBAL.DA.DML.ProcessInfoDelegate(DatabaseConverter_ProcessStarted);
}
public SearchIndicators(DIConnection dbConnection, DIQueries dbQueries)
{
this._GIdColumnName = Indicator.IndicatorNId;
this._NameColumnName = Indicator.IndicatorName;
this._GlobalValueColumnName = Indicator.IndicatorGlobal;
this._NameColumnHeader = DILanguage.GetLanguageString("INDICATOR");
this.DBConnection = dbConnection;
this.DBQueries = dbQueries;
FreeTextObj = new FreeText(dbConnection, dbQueries);
}
/// <summary>
/// Get layer name based on layer nid
/// </summary>
/// <param name="LayerNId">Layer NId</param>
/// <param name="DIConnection">DIConnection</param>
/// <param name="DIQueries">DIQueries</param>
/// <returns>Layer Name</returns>
private static string GetLayerName(string layerNId, DIConnection DIConnection, DIQueries DIQueries)
{
string RetVal = string.Empty;
string sSql = DIQueries.Area.GetAreaMapLayer(DevInfo.Lib.DI_LibDAL.Queries.Area.Select.MapFilterFieldType.LayerNId, layerNId.ToString(), FieldSelection.Light);
DataTable dtLayer = DIConnection.ExecuteDataTable(sSql);
if (dtLayer != null & dtLayer.Rows.Count > 0)
{
RetVal = dtLayer.Rows[0][Area_Map_Metadata.LayerName].ToString();
}
dtLayer.Dispose();
return(RetVal);
}
public static void DIConnect()
{
if (!DIApplication.Connected)
{
DIConnection lObjDIConnection = new DIConnection();
lObjDIConnection.ConnectToDI(GetCredentials());
if (lObjDIConnection.Company != null && lObjDIConnection.Company.Connected)
{
DIApplication.DIConnect(lObjDIConnection.Company);
}
}
}
/// <summary>
/// Get the base layer (most recent) associated with an area
/// </summary>
/// <param name="AreaNId"></param>
/// <param name="DIConnection"></param>
/// <param name="DIQueries"></param>
/// <returns>Layer NId</returns>
private static int GetLayerNId(string AreaNId, DIConnection DIConnection, DIQueries DIQueries)
{
int RetVal = -1;
string sSql = DIQueries.Area.GetAreaMapByAreaNIds(AreaNId, false); //Consider base layers only
DataTable dtAreaMap = DIConnection.ExecuteDataTable(sSql);
if (dtAreaMap != null && dtAreaMap.Rows.Count > 0)
{
//OPT If multiple base layer are associated with an area select latest map available
RetVal = (int)dtAreaMap.Rows[0][Area_Map.LayerNId];
}
dtAreaMap.Dispose();
return(RetVal);
}
/// <summary>
/// Get Image for the selected area
/// </summary>
/// <param name="AreaNId"> Area Nid of selected area</param>
/// <param name="OutPath"> Location where metadata related files are created</param>
/// <param name="DI_QueryBase"> This contain Conncetion Details</param>
/// <param name="PicHeight"> Height of the map </param>
/// <param name="PicWidth">width of the map</param>
/// <returns></returns>
public static Image GetAreaMapImage(DIConnection DIConnection, DIQueries DIQueries, int AreaNId, string TempFolder, int PicHeight, int PicWidth)
{
Image RetVal = null;
int LayerNId = -1;
try
{
//-- If any specific layer has not been defined then get the first base layer associated with the area
if (_LayerNId == -1)
{
LayerNId = GetLayerNId(AreaNId.ToString(), DIConnection, DIQueries);
}
else
{
LayerNId = _LayerNId;
}
if (LayerNId != -1)
{
//Get Layer Name
string LayerName = GetLayerName(LayerNId.ToString(), DIConnection, DIQueries);
//Extarct shape file for associated layer
Map.ExtractShapeFileByLayerId(LayerNId.ToString(), TempFolder, DIConnection, DIQueries);
Map map = new Map();
map.CanvasColor = Color.White;
// Add layer to map layer collection
map.Layers.Clear();
map.Width = PicWidth;
map.Height = PicHeight;
map.Layers.AddShapeFile(TempFolder, LayerName);
map.Layers[0].FillColor = System.Drawing.Color.White;
map.SetFullExtent();
//Extract map image
RetVal = Image.FromStream(map.GetMapStream());
map.Dispose();
map = null;
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
return(RetVal);
}
/// <summary>
/// Creates Icon tables for all available language
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="forOnlineDB"></param>
public static void CreateIconsTblsForAllLngs(DIConnection dbConnection, DIQueries dbQueries, bool forOnlineDB)
{
if (DIIcons.IsIconsTblExists(dbQueries.TablesName.Icons, dbConnection) == false)
{
try
{
//-- create Icon table
dbConnection.ExecuteNonQuery(DIIcons.CreateIconsTbl(dbQueries.TablesName.Icons, forOnlineDB));
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
}
}
/// <summary>
/// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue
/// and merge both column value into new column Data_Value
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
public static void MergeTextualandNumericDataValueColumn(string databaseFileNameWPath)
{
try
{
using (DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseFileNameWPath, string.Empty, string.Empty))
{
DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
MergeTextualandNumericDataValueColumn(DBConnection, DBQueries);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Import ISDefaultSubgroup from database
/// </summary>
/// <param name="sourceDatabase"></param>
/// <param name="sourceDBQueries"></param>
public void ImportDefaultSubgroups(DIConnection sourceDBConnection, DIQueries sourceDBQueries)
{
string IUSNids = string.Empty;
DataTable IUSTable = null;
DataTable IUTable = null;
IUSInfo IUSInfoObj = new IUSInfo();
//-- Get IUS from Source Database
IUSTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.IUS.GetIUS(FilterFieldType.None, string.Empty, FieldSelection.Light, true));
//-- Get Default IUS Values
DataRow[] IUSRows = IUSTable.Select(Indicator_Unit_Subgroup.IsDefaultSubgroup + "=1");
foreach (DataRow Row in IUSRows)
{
IUSInfoObj = new IUSInfo();
IUSInfoObj.IndicatorInfo = new IndicatorInfo();
IUSInfoObj.UnitInfo = new UnitInfo();
IUSInfoObj.SubgroupValInfo = new DI6SubgroupValInfo();
IUSInfoObj.IndicatorInfo.Name = Convert.ToString(Row[Indicator.IndicatorName]);
IUSInfoObj.IndicatorInfo.GID = Convert.ToString(Row[Indicator.IndicatorGId]);
IUSInfoObj.UnitInfo.Name = Convert.ToString(Row[Unit.UnitName]);
IUSInfoObj.UnitInfo.GID = Convert.ToString(Row[Unit.UnitGId]);
IUSInfoObj.SubgroupValInfo.Name = Convert.ToString(Row[SubgroupVals.SubgroupVal]);
IUSInfoObj.SubgroupValInfo.GID = Convert.ToString(Row[SubgroupVals.SubgroupValGId]);
int IUSNid = this.GetIUSNid(IUSInfoObj);
//-- Set Default Subgroup if IUS exists
if (IUSNid > 0)
{
IUTable = this.DBConnection.ExecuteDataTable(this.DBQueries.IUS.GetIUSByI_U_S(IUSInfoObj.IndicatorInfo.Nid.ToString(), IUSInfoObj.UnitInfo.Nid.ToString(), string.Empty));
IUSNids = DIConnection.GetDelimitedValuesFromDataTable(IUTable, Indicator_Unit_Subgroup.IUSNId);
//-- Make ISDefault false for other subgroupval for current I-U
this.UpdateIUSISDefaultSubgroup(IUSNids, false);
//-- Set DefaultSubgroupVal for IUSNid
this.UpdateIUSISDefaultSubgroup(IUSNid.ToString(), true);
}
}
}
/// <summary>
/// Import indicator, unit and subgroupval.
/// </summary>
/// <param name="iusInfo"></param>
/// <param name="sourceQurey"></param>
/// <param name="sourceDBConnection"></param>
/// <returns></returns>
public int ImportIUS(IUSInfo iusInfo, DIQueries sourceQurey, DIConnection sourceDBConnection)
{
int RetVal = 0;
int NewIndicatorNid = 0;
int NewUnitNid = 0;
int NewSubgroupValNid = 0;
try
{
//import indicator
NewIndicatorNid = this.DIIndicator.ImportIndicator(iusInfo.IndicatorInfo, iusInfo.IndicatorInfo.Nid, sourceQurey, sourceDBConnection);
//import unit
NewUnitNid = this.DIUnit.ImportUnit(iusInfo.UnitInfo, iusInfo.UnitInfo.Nid, sourceQurey, sourceDBConnection);
//import subgroupval
NewSubgroupValNid = this.DISubgroupVal.ImportSubgroupVal(iusInfo.SubgroupValInfo.Nid, sourceQurey, sourceDBConnection);
//check ius exists or not
if (NewIndicatorNid > 0 & NewUnitNid > 0 & NewSubgroupValNid > 0)
{
RetVal = this.GetIUSNid(NewIndicatorNid, NewUnitNid, NewSubgroupValNid);
//update nids
iusInfo.IndicatorInfo.Nid = NewIndicatorNid;
iusInfo.UnitInfo.Nid = NewUnitNid;
iusInfo.SubgroupValInfo.Nid = NewSubgroupValNid;
if (RetVal <= 0)
{
//insert ius combination
this.InsertIUS(iusInfo);
RetVal = this.GetIUSNid(NewIndicatorNid, NewUnitNid, NewSubgroupValNid);
}
}
}
catch (Exception ex)
{
RetVal = 0;
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Imports metadata from the source database into current database
/// </summary>
/// <param name="srcConnection"></param>
/// <param name="srcQueries"></param>
/// <param name="srcElementNid"></param>
/// <param name="trgElementNid"></param>
/// <param name="categoryType"></param>
/// <param name="metadataType"></param>
/// <param name="iconType"></param>
public void ImportMetadata(DIConnection srcConnection, DIQueries srcQueries, int srcElementNid, int trgElementNid, MetadataElementType categoryType, MetaDataType metadataType, IconElementType iconType)
{
string SrcCategoryGID = string.Empty;
string MetadataText = string.Empty;
int TrgCategoryNid = 0;
DataTable TrgCategoryTable = null;
Dictionary <String, String> OldIconNIdnNewIconNId = new Dictionary <string, string>();
try
{
// Get target Category table
TrgCategoryTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Type, DIQueries.MetadataElementTypeText[categoryType]));
// update metadata icon in DICon table
OldIconNIdnNewIconNId = DIIcons.ImportElement(srcElementNid, trgElementNid, iconType, srcQueries, srcConnection, this.DBQueries, this.DBConnection);
//get source metadata reports
foreach (DataRow SrcRow in srcConnection.ExecuteDataTable(srcQueries.MetadataReport.GetMetadataReportsByTargetNid(srcElementNid.ToString(), categoryType)).Rows)
{
SrcCategoryGID = Convert.ToString(SrcRow[Metadata_Category.CategoryGId]);
// check source category GID exists in current database
// Import metadta report only if category exists in current database
foreach (DataRow TrgCategoryRow in TrgCategoryTable.Select(Metadata_Category.CategoryGId + "='" + DIQueries.RemoveQuotesForSqlQuery(SrcCategoryGID) + "' "))
{
MetadataText = Convert.ToString(SrcRow[MetadataReport.Metadata]);
// Update IconNids in metadata if exists
foreach (string OldIconName in OldIconNIdnNewIconNId.Keys)
{
MetadataText = MetadataText.Replace(OldIconName, OldIconNIdnNewIconNId[OldIconName].ToString());
}
TrgCategoryNid = Convert.ToInt32(TrgCategoryRow[Metadata_Category.CategoryNId]);
this.InsertORUpdateMetadataInfo(metadataType, TrgCategoryNid.ToString(), trgElementNid, MetadataText);
}
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Returns true if ICON table exists otherwise false.
/// </summary>
/// <param name="iconTableName"></param>
/// <param name="dbConnection"></param>
/// <returns></returns>
public static bool IsIconsTblExists(string iconTableName, DIConnection dbConnection)
{
bool RetVal = false;
string sqlString = "Select count(*) from " + iconTableName + " where 1=2";
try
{
if ((dbConnection.ExecuteScalarSqlQuery(sqlString) != null))
{
RetVal = true;
}
}
catch (Exception ex)
{
RetVal = false;
}
return(RetVal);
}
private static bool ISColumnExists(DIConnection dbConnection, string columnName, string tableName)
{
bool RetVal = false;
string SqlQuery = string.Empty;
//-- Check orgTextual_Data_value exists or not.
try
{
SqlQuery = "Select " + columnName + " FROM " + tableName;
DataTable Table = dbConnection.ExecuteDataTable(SqlQuery);
RetVal = true;
}
catch (Exception)
{
RetVal = false;
}
return(RetVal);
}