public static RecommendedSourceBase CreateInstance(RecommendedSourceViewType viewType,UserSelection userSelections)
{
RecommendedSourceBase RetVal=null;
switch (viewType)
{
case RecommendedSourceViewType.Area:
RetVal = new RecommendedSourceArea();
break;
case RecommendedSourceViewType.IUS:
RetVal = new RecommendedSourceIUS();
break;
case RecommendedSourceViewType.Timeperiod:
RetVal = new RecommendedSourceTimeperiod();
break;
default:
break;
}
RetVal.UserSelections = userSelections;
return RetVal;
}
internal ExportOnlineDatabase(UserSelection userSelection, DIConnection sourceDBConnection, DIQueries sourceDBQueries)
{
if (sourceDBConnection != null && userSelection != null)
{
this.UserSelection = userSelection;
//-- Assign NIDs in userSelection.
if (userSelection.ShowIUS)
{
this.IUSNIDs = userSelection.IndicatorNIds;
}
else
{
this.IndicatorNIDs = userSelection.IndicatorNIds;
}
this.AreaNIDs = userSelection.AreaNIds;
this.TimePeriodNIDs = userSelection.TimePeriodNIds;
this.SourceNIDs = userSelection.SourceNIds;
this.ApplyMRD = userSelection.DataViewFilters.MostRecentData;
this.SourceDBConnection = sourceDBConnection;
this.SourceDBQueries = sourceDBQueries; //new DIQueries(this.SourceDBConnection.DIDataSetDefault(), this.SourceDBConnection.DILanguageCodeDefault(this.SourceDBConnection.DIDataSetDefault()));
}
}
/// <summary>
/// Get AreaBy ICNid or AreaNids
/// </summary>
/// <param name="selectedICType"></param>
/// <param name="userSelections"></param>
/// <returns></returns>
public string GetAutoDistinctArea(ICType selectedICType, UserSelection.UserSelection userSelections)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT A." + DIColumns.Data.AreaNId + ", A." + DIColumns.Area.AreaGlobal + ", A." + DIColumns.Area.AreaName);
sbQuery.Append(" FROM " + this.TablesName.Area + " AS A ");
sbQuery.Append(" WHERE EXISTS ( Select * FROM " + this.TablesName.Data + " AS D ");
if (userSelections.ICNIds.Length > 0 && string.IsNullOrEmpty(userSelections.IndicatorNIds))
{
sbQuery.Append("," + this.TablesName.IndicatorClassifications + " AS IC");
sbQuery.Append("," + this.TablesName.IndicatorClassificationsIUS + " AS ICIUS");
}
sbQuery.Append(" WHERE D." + DIColumns.Data.AreaNId + " = A." + DIColumns.Area.AreaNId);
if (userSelections.AreaNIds.Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.AreaNId + " IN(" + userSelections.AreaNIds + ")");
}
if (userSelections.TimePeriodNIds.Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.TimePeriodNId + " IN(" + userSelections.TimePeriodNIds + ")");
}
if (userSelections.SourceNIds.Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.SourceNId + " IN(" + userSelections.SourceNIds + ")");
}
if (userSelections.ShowIUS & userSelections.IndicatorNIds.Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.IUSNId + " IN(" + userSelections.IndicatorNIds + ")");
}
else if (userSelections.IndicatorNIds.Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.IndicatorNId + " IN(" + userSelections.IndicatorNIds + ")");
}
if (userSelections.ICNIds.Length > 0 && string.IsNullOrEmpty(userSelections.IndicatorNIds))
{
sbQuery.Append(" AND ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId + " =IC." + DIColumns.IndicatorClassifications.ICNId);
sbQuery.Append(" AND ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId + " =D." + DIColumns.Data.IUSNId);
if (userSelections.ICNIds != "-1")
{
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICNId + " IN(" + userSelections.ICNIds + ")");
}
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICType + "=" + DIQueries.ICTypeText[selectedICType]);
}
sbQuery.Append(") ORDER BY A." + DIColumns.Area.AreaName + " DESC ");
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// It prepares PresentationData using DIDataView class for specified Userselections.
/// </summary>
/// <param name="dBConnection"></param>
/// <param name="dBQueries"></param>
/// <param name="userSelection"></param>
/// <param name="xmlLanguageFileNameWPath"></param>
/// <returns></returns>
private static DataView GetPresentationData(DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, string xmlLanguageFileNameWPath)
{
DataView RetVal = null;
//Create UserPreference required for DIDataView object.
UserPreference UserPreference = new UserPreference(xmlLanguageFileNameWPath);
UserPreference.UserSelection = userSelection;
// prepare Map's dataview
DIDataView DIDataView = new DevInfo.Lib.DI_LibBAL.UI.DataViewPage.DIDataView(UserPreference, dBConnection, dBQueries, "", "");
RetVal = DIDataView.GetAllDataByUserSelection();
if (RetVal == null)
{
RetVal = dBConnection.ExecuteDataTable(dBQueries.Data.GetPresentationData(userSelection, dBConnection.ConnectionStringParameters.ServerType)).DefaultView;
}
return RetVal;
}
/// <summary>
/// It prepares SQl query to get records each having blank DataValue.
/// No. of records will be (ICNid x TimePeriodNId x AreaNid x SubgroupValNid).
/// </summary>
/// <param name="includeGUID">true, if subgroupGIS to be included.</param>
/// <param name="userSelections">user Selections </param>
/// <param name="dBQueries">dbQueries to get tableNames</param>
/// <returns></returns>
private static string ProcessSQLQueryForEmptyDES(bool includeGUID, UserSelection userSelection, DIQueries dBQueries)
{
string RetVal = string.Empty;
try
{
RetVal = "SELECT TP." + Timeperiods.TimePeriod + ", A." + Area.AreaID + ", A." + Area.AreaName + ", \"\" AS " + Data.DataValue + ", S." + SubgroupVals.SubgroupVal + ", IC." + IndicatorClassifications.ICName + ", \"\" AS " + FootNotes.FootNote + ", \"\" AS " + Data.DataDenominator;
if (includeGUID)// If GUID is required, then include GID columns.
{
RetVal += ", S." + SubgroupVals.SubgroupValGId;
}
RetVal += " FROM " + dBQueries.TablesName.IndicatorClassifications + " AS IC, " + dBQueries.TablesName.TimePeriod + " AS TP, " + dBQueries.TablesName.Area + " AS A, " + dBQueries.TablesName.SubgroupVals + " AS S WHERE IC." + IndicatorClassifications.ICType + " = 'SR' AND IC." + IndicatorClassifications.ICParent_NId + " <>-1 ";
// Apply Filter for AreaNid, if present.
if (userSelection.AreaNIds.Length > 0)
{
RetVal += " AND (A." + Area.AreaNId + " in (" + userSelection.AreaNIds + "))";
}
// Apply Filter for TimePeriodNid ,if present.
if (userSelection.TimePeriodNIds.Length > 0)
{
RetVal += " AND (TP." + Timeperiods.TimePeriodNId + " in (" + userSelection.TimePeriodNIds + "))";
}
// Apply Filter for SourceNIds ,if present.
if (userSelection.SourceNIds.Length > 0)
{
RetVal += " AND (IC." + IndicatorClassifications.ICNId + " in (" + userSelection.SourceNIds + "))";
}
// Apply Filter for SubgroupValNIds ,if present.
if (userSelection.SubgroupValNIds.Length > 0)
{
RetVal += " AND (S." + SubgroupVals.SubgroupValNId + " in (" + userSelection.SubgroupValNIds + "))";
}
// Set order by
RetVal += " Order BY TP." + Timeperiods.TimePeriod + ", A." + Area.AreaID + ", S." + SubgroupVals.SubgroupVal + ", IC." + IndicatorClassifications.ICName + " ASC";
}
catch (Exception)
{
}
return RetVal;
}
/// <summary>
/// It prepares DataView having requred Data for DES generation.
/// <para>For empty DES, it prepares DataView where no DataValue presents against UserSelections.</para>
/// </summary>
/// <param name="includeGUID">whether GUID to be included or not.</param>
/// <param name="userSelection">userSelection object</param>
/// <param name="dBConnection">Source DBase DIConnection object</param>
/// <param name="dBQueries">Source DBase DBQueries object.</param>
/// <param name="indicatorNId">indicator NId for which DataView to be generated.</param>
/// <param name="unitNID">unit NId for which DataView to be generated.</param>
public static DataView GetDESDataViewForIU(bool includeGUID, bool emptyDES, UserSelection userSelection, DIConnection dBConnection, DIQueries dBQueries, string languageFileNameWPath, string indicatorNId, string unitNId
)
{
DataView RetVal = null;
string SqlQuery = string.Empty;
string UserSelectiosIndicatorNids = string.Empty;
string UserSelectiosUnitNids = string.Empty;
string OriginalSubgroupNIds = string.Empty;
string IUSNIds = string.Empty;
bool UserSelectionShowIUS = false;
DataTable AutoSubgroupVals = null;
DataTable IUSTable;
try
{
// If emptyDES = true, then get records having blank DataValue for every possoble combinations of Specified Area, Time, Source, Subgroup.
if (emptyDES)
{
// Preserve original SubgroupNIds into a temp
OriginalSubgroupNIds = userSelection.SubgroupValNIds;
// 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)
{
// Get SubgroupNIDs for given IUSNId as userSelection.IndicatorNId
AutoSubgroupVals = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUS(FilterFieldType.NId, userSelection.IndicatorNIds, FieldSelection.Light));
// Set filter for given I, U
AutoSubgroupVals.DefaultView.RowFilter = Indicator.IndicatorNId + " = " + indicatorNId + " AND " + Unit.UnitNId + " = " + unitNId;
AutoSubgroupVals = AutoSubgroupVals.DefaultView.ToTable();
}
else
{
// Get SubgroupNIDs for given Indicator + Unit
AutoSubgroupVals = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUSNIdByI_U_S(indicatorNId, unitNId, string.Empty));
}
// Set those SubgroupNIds into userSelection
userSelection.SubgroupValNIds = DIExport.DataColumnValuesToString(AutoSubgroupVals, 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);
// Build SQl query for empty DES. (DataValue column will be blank)
SqlQuery = DIExport.ProcessSQLQueryForEmptyDES(includeGUID, userSelection, dBQueries);
// Get DataView
RetVal = dBConnection.ExecuteDataTable(SqlQuery).DefaultView;
// 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();
// Update Indicator Unit in DataView
DIExport.UpdateIndicatorUnitInDataView(ref RetVal, dBConnection, dBQueries, indicatorNId, unitNId);
// Set original SubgroupNIDs back into userSelection
userSelection.SubgroupValNIds = OriginalSubgroupNIds;
}
else
{
//-- CASE: where DATA Exists
// Preserve indicatorNids & unitNids of UserSelection.
UserSelectiosIndicatorNids = userSelection.IndicatorNIds;
UserSelectiosUnitNids = userSelection.UnitNIds;
// Set single specified indicatorNId, unitNid in UserSelection, to get PresentationData only for selected IU
userSelection.IndicatorNIds = indicatorNId;
userSelection.UnitNIds = unitNId;
if (userSelection.ShowIUS)
{
// Preserve ShowIUS property
UserSelectionShowIUS = userSelection.ShowIUS;
// Set ShowIUS = false
userSelection.ShowIUS = false;
}
// Get Presentation Data for specified IU in userSelection using DIDataView class.
RetVal = DIExport.GetPresentationData(dBConnection, dBQueries, userSelection, languageFileNameWPath);
// Restore UserSelection.ShowIUS
userSelection.ShowIUS = UserSelectionShowIUS;
// Set back original IndicatorNid unitNid back into UserSelection object.
userSelection.IndicatorNIds = UserSelectiosIndicatorNids;
userSelection.UnitNIds = UserSelectiosUnitNids;
RetVal.RowFilter = Unit.UnitNId + "=" + unitNId; // by default, for specified unit_NId
// Set filter for UnitNid, SubgroupValNid and Source (becoz in GetPresentationData() does not filter Data for same)
if (userSelection.SourceNIds.Length > 0)
{
RetVal.RowFilter += " AND " + IndicatorClassifications.ICNId + " IN (" + userSelection.SourceNIds + ")";
}
if (userSelection.UnitNIds.Length > 0)
{
RetVal.RowFilter += " AND " + Unit.UnitNId + " IN (" + userSelection.UnitNIds + ")";
}
if (userSelection.SubgroupValNIds.Length > 0)
{
RetVal.RowFilter += " AND " + SubgroupVals.SubgroupValNId + " IN (" + userSelection.SubgroupValNIds + ")";
}
if ((userSelection.ShowIUS))
{
//If ShowIUS = true, apply rowFilter for given indicatorNid and UnitNid (in parameter)
RetVal.RowFilter += " AND " + Indicator_Unit_Subgroup.IUSNId + " IN (" + UserSelectiosIndicatorNids + ")";
//RetVal.RowFilter += " AND " + Unit.UnitNId + " = " + unitNId;
}
DataTable RetValTemp = RetVal.ToTable();
// Add Footnote in DataView, as GetPresentationData() does not include FootNote
DIExport.AddFootNoteInDataTable(ref RetValTemp, dBConnection, dBQueries);
RetVal = RetValTemp.DefaultView;
// Change Data_Value column type to string.
RetVal.Table.Columns[Data.DataValue].DataType = typeof(string);
}
// Rename GID columns if not required.
if (!(includeGUID))
{
RetVal.Table.Columns[Indicator.IndicatorGId].ColumnName = "I_GID";
RetVal.Table.Columns[Unit.UnitGId].ColumnName = "U_GID";
if (RetVal.Table.Columns.Contains(SubgroupVals.SubgroupValGId))
{
RetVal.Table.Columns[SubgroupVals.SubgroupValGId].ColumnName = "S_GID";
}
}
// Update Sector , Class in DataView
DIExport.AddSectorClassInDataView(ref RetVal, dBConnection, dBQueries, indicatorNId, unitNId);
RetVal.Table.AcceptChanges();
}
catch (Exception ex)
{
}
finally
{
if (RetVal == null)
{
RetVal = (new DataTable()).DefaultView;
}
}
return RetVal;
}
/// <summary>
/// Get all the AreaNIds of the selected level and update the user selection.
/// </summary>
/// <param name="selectedAreaNIds"></param>
/// <param name="levels"></param>
/// <remarks></remarks>
public void GetSubNationals(DIConnection dbConnection, DIQueries dbQueries, UserSelection dbUserSelection, string selectedAreaNIds, string levels)
{
try
{
if (!string.IsNullOrEmpty(this._SecondryAreaLevels))
{
StringBuilder sbArea = new StringBuilder();
string[] SelectedLevel = new string[0];
string sLevels = string.Empty;
DataTable Areadt = new DataTable();
DataRow[] Rows = null;
string SelectedAreaNId = string.Empty;
Areas = new Dictionary<int, string>();
foreach (Int32 Level in GetAreaLevel(dbConnection, dbQueries))
{
IDataReader AreaReader;
//'-- Get Area NIDs of the levles
AreaReader = dbConnection.ExecuteReader(dbQueries.Area.GetAreaNIdByAreaLevel(dbUserSelection.AreaNIds, Level));
while (AreaReader.Read())
{
sbArea.Append("," + AreaReader[Area.AreaNId].ToString());
}
//'-- Add the selected AreaNIDs according to their level
if (sbArea.Length > 0)
{
Areas.Add(Level, sbArea.ToString().Substring(1));
}
AreaReader.Close();
sbArea.Length = 0;
}
//'-- Get the Levels.
sLevels = this.GetParentLevel(this._SecondryAreaLevels, dbConnection, dbQueries);
this._AreaLevels = sLevels;
SelectedLevel = DICommon.SplitString(sLevels, ",");
//'-- Insert all the required level to build the comma seprated AreaNIds of selected levels
for (int Index = GetMaxKey(Areas); Index <= GetMinSelectedLevel(SelectedLevel) - 1; Index++)
{
Array.Resize(ref SelectedLevel, SelectedLevel.Length + 1);
SelectedLevel[SelectedLevel.Length - 1] = Index.ToString();
}
//'-- Sort on the all the required levels
SelectedLevel = Sort(SelectedLevel);
//'-- Comma seprated required levels.
sLevels = string.Empty;
foreach (string NewLevel in SelectedLevel)
{
sLevels += "," + NewLevel;
}
if (!string.IsNullOrEmpty(sLevels))
{
sLevels = sLevels.Substring(1);
}
if (!string.IsNullOrEmpty(this._SecondryAreaLevels))
{
//'-- Get all the areas of the required levels
Areadt = dbConnection.ExecuteDataTable(dbQueries.Area.GetAreasByAreaLevels(sLevels));
foreach (string sLevel in SelectedLevel)
{
sbArea.Length = 0;
if (string.IsNullOrEmpty(GetSelectedAreaNId(Areas, Convert.ToInt32(sLevel))))
{
//'-- Get the saved AreaNIds
if (Convert.ToInt32(sLevel) > 1)
{
SelectedAreaNId = GetSelectedAreaNId(Areas, Convert.ToInt32(sLevel) - 1);
}
//'-- Get the AreaNIds on the basis of selected areas and level
if (!string.IsNullOrEmpty(SelectedAreaNId))
{
Rows = Areadt.Select(Area.AreaLevel + " = " + Convert.ToInt32(sLevel) + " AND " + Area.AreaParentNId + " IN (" + SelectedAreaNId + ")");
}
else
{
Rows = Areadt.Select(Area.AreaLevel + " = " + Convert.ToInt32(sLevel));
}
//'-- Add the AreaNIds in the collection.
foreach (DataRow Row in Rows)
{
sbArea.Append("," + Row[Area.AreaNId].ToString());
}
if (sbArea.Length > 0)
{
Areas.Add(Convert.ToInt32(sLevel), sbArea.ToString().Substring(1));
}
}
}
SelectedLevel = DICommon.SplitString(this._SecondryAreaLevels, ",");
//'-- Update user selection with AreaNIds
dbUserSelection.AreaNIds = string.Empty;
dbUserSelection.AreaIds = string.Empty;
foreach (string selLevel in SelectedLevel)
{
SelectedAreaNId = GetSelectedAreaNId(Areas, Convert.ToInt32(selLevel));
if (!string.IsNullOrEmpty(SelectedAreaNId))
{
dbUserSelection.AreaNIds += "," + SelectedAreaNId;
}
}
if (!string.IsNullOrEmpty(dbUserSelection.AreaNIds))
{
dbUserSelection.AreaNIds = dbUserSelection.AreaNIds.Substring(1);
}
Areadt = dbConnection.ExecuteDataTable(dbQueries.Area.GetArea(FilterFieldType.NId, dbUserSelection.AreaNIds, Lib.DI_LibDAL.Queries.Area.Select.OrderBy.AreaName));
foreach (DataRow Row in Areadt.Rows)
{
if (string.IsNullOrEmpty(dbUserSelection.AreaIds))
{
dbUserSelection.AreaIds = Row[Area.AreaID].ToString();
}
else
{
dbUserSelection.AreaIds += Delimiter.TEXT_DELIMITER + Row[Area.AreaID].ToString();
}
}
}
}
}
catch (Exception ex)
{
}
}
/// <summary>
/// It prepares SQl query to get records each having blank DataValue.
/// No. of records will be (ICNid x TimePeriodNId x AreaNid x SubgroupValNid).
/// </summary>
/// <param name="userSelections">user Selections </param>
/// <param name="dBQueries">dbQueries to get tableNames</param>
/// <returns></returns>
private string GetQueryForEmptyDES(UserSelection userSelection, DIQueries dBQueries)
{
string RetVal = string.Empty;
try
{
RetVal = "SELECT ";
// fetch timeperiod only if it available in userSelection
if (userSelection.TimePeriodNIds.Length == 0)
{
RetVal += " \"\" AS " + Timeperiods.TimePeriod + " ,";
}
else
{
RetVal += " TP." + Timeperiods.TimePeriod + ",";
}
// fetch source only if it available in userSelection
if (userSelection.SourceNIds.Length == 0)
{
RetVal += " \"\" AS " + IndicatorClassifications.ICName + " ,";
}
else
{
RetVal += " IC." + IndicatorClassifications.ICName + ",";
}
RetVal += " A." + Area.AreaID + ", A." + Area.AreaName + ", \"\" AS " + Data.DataValue + ", \"\" AS " + FootNotes.FootNote + ", \"\" AS " + Data.DataDenominator;
#region "-- From Clause--"
// form clause
RetVal += " FROM " + dBQueries.TablesName.Area + " AS A ";
// add source table, if source nids exists
if (userSelection.SourceNIds.Length > 0)
{
RetVal += ", " + dBQueries.TablesName.IndicatorClassifications + " AS IC ";
}
// add timeperiod table, if timeperiod nids exists
if (userSelection.TimePeriodNIds.Length > 0)
{
RetVal += ", " + dBQueries.TablesName.TimePeriod + " AS TP ";
}
#endregion
#region "-- Where Clause --"
// where clause
RetVal += " WHERE 1=1 ";
// Apply Filter for sourceNid, if present.
if (userSelection.SourceNIds.Length > 0)
{
RetVal += " And IC." + IndicatorClassifications.ICType + " = 'SR' AND IC." + IndicatorClassifications.ICParent_NId + " <>-1 ";
}
// Apply Filter for AreaNid, if present.
if (userSelection.AreaNIds.Length > 0)
{
RetVal += " AND (A." + Area.AreaNId + " in (" + userSelection.AreaNIds + "))";
}
// Apply Filter for TimePeriodNid ,if present.
if (userSelection.TimePeriodNIds.Length > 0)
{
RetVal += " AND (TP." + Timeperiods.TimePeriodNId + " in (" + userSelection.TimePeriodNIds + "))";
}
// Apply Filter for SourceNIds ,if present.
if (userSelection.SourceNIds.Length > 0)
{
RetVal += " AND (IC." + IndicatorClassifications.ICNId + " in (" + userSelection.SourceNIds + "))";
}
#endregion
#region "-- Order By --"
// Set order by
RetVal += " Order BY ";
if (userSelection.TimePeriodNIds.Length > 0)
{
RetVal += " TP." + Timeperiods.TimePeriod + ", ";
}
RetVal += " A." + Area.AreaID;
if (userSelection.SourceNIds.Length > 0)
{
RetVal += ", IC." + IndicatorClassifications.ICName;
}
RetVal += " ASC";
#endregion
}
catch (Exception)
{
}
return RetVal;
}
public static bool ExportDatabase(UserSelection userSelection, DIConnection sourceDBConnection, DIQueries sourceDBQueries, bool maintainTemplate, string destinationDBNameWPath, string tempFolderPath)
{
return DIExport.ExportDatabase(userSelection, -1, sourceDBConnection, sourceDBQueries, maintainTemplate, destinationDBNameWPath, tempFolderPath);
}
/// <summary>
/// Generates DES where data exists
/// </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>
public bool ExportDESWhereDataExists(bool singleWorkbook, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, Fields sortedFields, string xlsFileNameWPath, bool includeGUID, string languageFileNameWPath)
{
bool RetVal = false;
IWorkbook DESWorkbook = null;
DataTable DistinctIU;
String SqlQuery = string.Empty;
DataView FilteredDataView = null;
DataTable MissingRecordTable;
DataTable WhereDataExistsTable;
string RowFilter = string.Empty;
int IUCounter = 0;
int ProgressBarValue = 0;
string IndicatorNId = string.Empty;
string UnitNId = string.Empty;
try
{
//1. Get Distinct IU
DistinctIU = DIExport.GetDistinctIU(false, dBConnection, dBQueries, userSelection);
// Initialize progress bar
this.RaiseProgressBarInitialize(DistinctIU.Rows.Count + 1);
// 3. Iterate through each Row having distinct Indicator + Unit.
foreach (DataRow dr in DistinctIU.Rows)
{
IndicatorNId = dr[Indicator.IndicatorNId].ToString();
UnitNId = dr[Unit.UnitNId].ToString();
// get records where data exists
FilteredDataView = this.GetDESDataViewWhereDataExits(includeGUID, userSelection, dBConnection, dBQueries, languageFileNameWPath, IndicatorNId, UnitNId);
// update filteredDataView
try
{
// 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";
}
}
//// Update Sector , Class in DataView
//DIExport.AddSectorClassInDataView(ref FilteredDataView, dBConnection, dBQueries, IndicatorNId, UnitNId);
FilteredDataView.Table.AcceptChanges();
}
catch (Exception)
{
}
ProgressBarValue++;
this.RaiseProgressBarIncrement(ProgressBarValue); //raise Progressbar_Increment event
if (FilteredDataView.Count > 0)
{
//// Set Indicator, Unit Name for progress bar event, and raise Progress_Increment event
//this.RaiseProcessIndicatorUnitInfo(FilteredDataView.Table.Rows[0][Indicator.IndicatorName].ToString(), FilteredDataView.Table.Rows[0][Unit.UnitName].ToString(), FilteredDataView.Table.Rows.Count);
// Set sort order on FilteredDataView.
FilteredDataView.Sort = this.GetSortString(sortedFields);
IUCounter++;
try
{
// Process Workbook by filling worksheet with DataView.
this.ProcessWorkbook(singleWorkbook, ref DESWorkbook, FilteredDataView, IUCounter, xlsFileNameWPath, languageFileNameWPath, dBConnection, dBQueries, IndicatorNId, UnitNId);
}
catch (Exception)
{
}
}
}
if (singleWorkbook)
{
if (!(DESWorkbook == null))
{
// Delete last worksheet which is left blank.
DESWorkbook.Worksheets[DESWorkbook.Worksheets.Count - 1].Delete();
// Set first sheet as active sheet.
DESWorkbook.Worksheets[0].Select();
// Save Workbook
DESWorkbook.Save();
}
}
RetVal = true;
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (!(DESWorkbook == null))
{
DESWorkbook.Save();
}
// Close progress bar
this.RaiseProcessIndicatorUnitInfo(string.Empty, string.Empty, 0);
this.RaiseProgressBarClose();
}
return RetVal;
}
/// <summary>
/// It prepares DataView having requred Data for DES generation.
/// <para>For empty DES, it prepares DataView where no DataValue presents against UserSelections.</para>
/// </summary>
/// <param name="includeGUID">whether GUID to be included or not.</param>
/// <param name="userSelection">userSelection object</param>
/// <param name="dBConnection">Source DBase DIConnection object</param>
/// <param name="dBQueries">Source DBase DBQueries object.</param>
/// <param name="indicatorNId">indicator NId for which DataView to be generated.</param>
/// <param name="unitNID">unit NId for which DataView to be generated.</param>
private DataView GetDESDataViewWhereDataExits(bool includeGUID, UserSelection userSelection, DIConnection dBConnection, DIQueries dBQueries, string languageFileNameWPath, string indicatorNId, string unitNId
)
{
DataView RetVal = null;
string SqlQuery = string.Empty;
string UserSelectiosIndicatorNids = string.Empty;
string UserSelectiosUnitNids = string.Empty;
string OriginalSubgroupNIds = string.Empty;
string IUSNIds = string.Empty;
bool UserSelectionShowIUS = false;
DataTable AutoSubgroupVals = null;
DataTable IUSTable;
try
{
#region "-- where DATA Exists --"
// Preserve indicatorNids & unitNids of UserSelection.
UserSelectiosIndicatorNids = userSelection.IndicatorNIds;
UserSelectiosUnitNids = userSelection.UnitNIds;
// Set single specified indicatorNId, unitNid in UserSelection, to get PresentationData only for selected IU
userSelection.IndicatorNIds = indicatorNId;
userSelection.UnitNIds = unitNId;
if (userSelection.ShowIUS)
{
// Preserve ShowIUS property
UserSelectionShowIUS = userSelection.ShowIUS;
// Set ShowIUS = false
userSelection.ShowIUS = false;
}
// Get Data for the given userSelection
RetVal = dBConnection.ExecuteDataTable(dBQueries.Data.GetDataNIDs(userSelection.IndicatorNIds, userSelection.TimePeriodNIds, userSelection.AreaNIds, userSelection.SourceNIds, userSelection.ShowIUS, FieldSelection.Heavy, false)).DefaultView;
//DIExport.GetPresentationData(dBConnection, dBQueries, userSelection, languageFileNameWPath);
// Restore UserSelection.ShowIUS
userSelection.ShowIUS = UserSelectionShowIUS;
// Set back original IndicatorNid unitNid back into UserSelection object.
userSelection.IndicatorNIds = UserSelectiosIndicatorNids;
userSelection.UnitNIds = UserSelectiosUnitNids;
RetVal.RowFilter = Unit.UnitNId + "=" + unitNId; // by default, for specified unit_NId
// Set filter for UnitNid, SubgroupValNid and Source (becoz in GetPresentationData() does not filter Data for same)
if (userSelection.SourceNIds.Length > 0)
{
RetVal.RowFilter += " AND " + IndicatorClassifications.ICNId + " IN (" + userSelection.SourceNIds + ")";
}
if (userSelection.UnitNIds.Length > 0)
{
RetVal.RowFilter += " AND " + Unit.UnitNId + " IN (" + userSelection.UnitNIds + ")";
}
if (userSelection.SubgroupValNIds.Length > 0)
{
RetVal.RowFilter += " AND " + SubgroupVals.SubgroupValNId + " IN (" + userSelection.SubgroupValNIds + ")";
}
if ((userSelection.ShowIUS) && !string.IsNullOrEmpty(UserSelectiosIndicatorNids))
{
//If ShowIUS = true, apply rowFilter for given indicatorNid and UnitNid (in parameter)
RetVal.RowFilter += " AND " + Indicator_Unit_Subgroup.IUSNId + " IN (" + UserSelectiosIndicatorNids + ")";
//RetVal.RowFilter += " AND " + Unit.UnitNId + " = " + unitNId;
}
DataTable RetValTemp = RetVal.ToTable();
// Add Footnote in DataView, as GetPresentationData() does not include FootNote
DIExport.AddFootNoteInDataTable(ref RetValTemp, dBConnection, dBQueries);
RetVal = RetValTemp.DefaultView;
// Change Data_Value column type to string.
RetVal.Table.Columns[Data.DataValue].DataType = typeof(string);
RetVal.Table.AcceptChanges();
#endregion
}
catch (Exception ex)
{
}
finally
{
if (RetVal == null)
{
RetVal = (new DataTable()).DefaultView;
}
}
return RetVal;
}
/// <summary>
/// Get the deleted source NIds
/// </summary>
/// <param name="userselection">user selection</param>
/// <param name="recSourceDt">Source data table</param>
/// <returns></returns>
public static string GetDeletedSourceNIds(UserSelection userselection, DataTable recSourceDt)
{
string Retval = string.Empty;
try
{
string[] DistinctIUSNIds = new string[0];
string[] DistinctTimePeriodNIds = new string[0];
string[] DistinctAreaNIds = new string[0];
DataRow[] Rows = new DataRow[0];
DataRow[] NonRecommnededRows = new DataRow[0];
//-- Get the distinct IUS
if (string.IsNullOrEmpty(userselection.IndicatorNIds.Trim()))
{
DistinctIUSNIds = SplitString(GetDistinctIUS(recSourceDt), ",");
}
else
{
DistinctIUSNIds = SplitString(userselection.IndicatorNIds, ",");
}
//-- Get the distinct Areas
if (string.IsNullOrEmpty(userselection.AreaNIds.Trim()))
{
DistinctAreaNIds = SplitString(GetDistinctArea(recSourceDt), ",");
}
else
{
DistinctAreaNIds = SplitString(userselection.AreaNIds, ",");
}
//-- Get the distinct TimePeriods
if (string.IsNullOrEmpty(userselection.TimePeriodNIds.Trim()))
{
DistinctTimePeriodNIds = SplitString(GetDistinctTimePeriods(recSourceDt), ",");
}
else
{
DistinctTimePeriodNIds = SplitString(userselection.TimePeriodNIds, ",");
}
//-- Loop thru the distinct IUSNIds, TimePeriod and Area NId
foreach (string IUSNId in DistinctIUSNIds)
{
foreach (string TimePeriodNId in DistinctTimePeriodNIds)
{
foreach (string AreaNId in DistinctAreaNIds)
{
//-- Get the recommended sources of the IUS, Area and TimePeriod NIds
Rows = recSourceDt.Select(Data.ICIUSOrder + " = 1 and " + Data.IUSNId + " = " + Convert.ToInt32(IUSNId) + " and " + Data.TimePeriodNId + " = " + Convert.ToInt32(TimePeriodNId) + " and " + Data.AreaNId + " = " + Convert.ToInt32(AreaNId));
if (Rows.Length > 0)
{
//-- If there are some recommended sources against the IUS, Area and Time period, move that non-recommended source into the deleted source NIds.
NonRecommnededRows = recSourceDt.Select("(" + Data.ICIUSOrder + " <> 1 or " + Data.ICIUSOrder + " is null) and " + Data.IUSNId + " = " + Convert.ToInt32(IUSNId) + " and " + Data.TimePeriodNId + " = " + Convert.ToInt32(TimePeriodNId) + " and " + Data.AreaNId + " = " + Convert.ToInt32(AreaNId));
if (NonRecommnededRows.Length > 0)
{
//-- Comma seprated string of recommended sources
foreach (DataRow Row in NonRecommnededRows)
{
if (string.IsNullOrEmpty(Retval))
{
Retval = Row[Data.DataNId].ToString();
}
else
{
Retval += "," + Row[Data.DataNId].ToString();
}
}
}
}
}
}
}
}
catch (Exception)
{
}
return Retval;
}
/// <summary>
/// Generates DevInfo Data Entry Spreadsheet using Database as data source.
/// </summary>
/// <param name="singleWorkbook">true, if single workbook is required.</param>
/// <param name="emptyDES">true, to get missing records</param>
/// <param name="withWhereDataExists">true to get records where data exists</param>
/// <param name="dBConnection">DbConnection object of source database.</param>
/// <param name="dBQueries">DBQueries object of source database.</param>
/// <param name="userSelection">UserSelection object containg NIDs of Indicator, Unit, Subgroup, Area, Source, TimePeriod</param>
/// <param name="xlsFileNameWPath">workbook file name with path.</param>
/// <param name="includeGUID">true, if GUID of I, U, S to be included in DES.</param>
/// <param name="languageFileNameWPath">Xml language file name with path, for language handling.</param>
/// <returns></returns>
public bool ExportDataEntrySpreadsheet(bool singleWorkbook, bool emptyDES, bool withWhereDataExists, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, Fields sortedFields, string xlsFileNameWPath, bool includeGUID, string languageFileNameWPath)
{
bool RetVal = false;
IWorkbook DESWorkbook = null;
DataTable DistinctIU;
String SqlQuery = string.Empty;
DataView FilteredDataView = null;
DataTable MissingRecordTable;
DataTable WhereDataExistsTable;
string RowFilter = string.Empty;
int IUCounter = 0;
int ProgressBarValue = 0;
try
{
//1. Get Distinct IU
DistinctIU = DIExport.GetDistinctIU(emptyDES, dBConnection, dBQueries, userSelection);
// Initialize progress bar
this.RaiseProgressBarInitialize(DistinctIU.Rows.Count );
// 3. Iterate through each Row having distinct Indicator + Unit.
foreach (DataRow dr in DistinctIU.Rows)
{
//// Prepare DES DataView for given IU
//if (emptyDES && FilteredDataView != null)
//{
// // If FilteredDataView is already made, Dataview will be same for every IU,
// // then use existing Dataview
// // Update Indicator & Unit in same DataView
// DIExport.UpdateIndicatorUnitInDataView(ref FilteredDataView, dBConnection, dBQueries, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString());
// // Update Sector , Class in DataView
// DIExport.AddSectorClassInDataView(ref FilteredDataView, dBConnection, dBQueries, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString());
//}
//else
// get missing records with where data exists
if (emptyDES & withWhereDataExists)
{
//-- CASE: Missing Records + DATA Exists
//get records where data exists
WhereDataExistsTable = DIExport.GetDESDataViewForIU(includeGUID, false, userSelection, dBConnection, dBQueries, languageFileNameWPath, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString()).Table;
//get missing records
MissingRecordTable = DIExport.GetMissingRecords(includeGUID, userSelection, dBConnection, dBQueries, languageFileNameWPath, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString()).Table;
List<String> RequiredColumns = new List<string>();
foreach (DataColumn Column in MissingRecordTable.Columns)
{
if (Column.ColumnName != Data.DataDenominator)
RequiredColumns.Add(Column.ColumnName);
}
MissingRecordTable = MissingRecordTable.DefaultView.ToTable(true, RequiredColumns.ToArray());
// merge missing records & records where data exists
WhereDataExistsTable.Merge(MissingRecordTable);
// add missing columns
if (WhereDataExistsTable.Columns.Contains(Data.DataDenominator) == false)
{
WhereDataExistsTable.Columns.Add(Data.DataDenominator);
}
FilteredDataView = WhereDataExistsTable.DefaultView;
}
else // get missing records or where data exists
{
//-- CASE 1: blank Records
//-- CASE 2: Where Data Exists
FilteredDataView = DIExport.GetDESDataViewForIU(includeGUID, emptyDES, userSelection, dBConnection, dBQueries, languageFileNameWPath, dr[Indicator.IndicatorNId].ToString(), dr[Unit.UnitNId].ToString());
}
//raise Progressbar_Increment event
if (FilteredDataView.Count > 0)
{
// Set Indicator, Unit Name for progress bar event, and raise Progress_Increment event
//this.RaiseProcessIndicatorUnitInfo(FilteredDataView.Table.Rows[0][Indicator.IndicatorName].ToString(), FilteredDataView.Table.Rows[0][Unit.UnitName].ToString(), FilteredDataView.Table.Rows.Count);
// Set sort order on FilteredDataView.
FilteredDataView.Sort = this.GetSortString(sortedFields);
IUCounter++;
try
{
// Process Workbook by filling worksheet with DataView.
this.ProcessWorkbook(singleWorkbook, ref DESWorkbook, FilteredDataView, IUCounter, xlsFileNameWPath, languageFileNameWPath);
}
catch (Exception)
{
}
}
ProgressBarValue++;
this.RaiseProgressBarIncrement(ProgressBarValue);
}
//if (singleWorkbook)
//{
if (!(DESWorkbook == null))
{
// Delete last worksheet which is left blank.
DESWorkbook.Worksheets[DESWorkbook.Worksheets.Count - 1].Delete();
// Set first sheet as active sheet.
DESWorkbook.Worksheets[0].Select();
// Save Workbook
DESWorkbook.Save();
}
//}
RetVal = true;
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (!(DESWorkbook == null))
{
DESWorkbook.Save();
}
// Close progress bar
this.RaiseProcessIndicatorUnitInfo(string.Empty, string.Empty, 0);
this.RaiseProgressBarClose();
}
return RetVal;
}
private string[] AutoSelectedTimePeriods(DIConnection dbConnection, DIQueries dbQueries, UserSelection dbUserSelection)
{
string[] Retval = new string[0];
try
{
string TimePeriods = string.Empty;
IDataReader TimePeriodReader;
if (string.IsNullOrEmpty(this._FromTimePeriod))
{
TimePeriodReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectTimeperiod(dbUserSelection.IndicatorNIds, dbUserSelection.ShowIUS, dbUserSelection.AreaNIds, dbUserSelection.SourceNIds));
}
else
{
if (this._IsToMRD)
{
TimePeriodReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectedTimePeriodsRange(this._FromTimePeriod, string.Empty, dbUserSelection));
}
else
{
TimePeriodReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectedTimePeriodsRange(this._FromTimePeriod, this._ToTimePeriod, dbUserSelection));
}
}
while (TimePeriodReader.Read())
{
TimePeriods += "," + TimePeriodReader[Timeperiods.TimePeriod].ToString();
}
TimePeriodReader.Close();
if (!string.IsNullOrEmpty(TimePeriods))
{
TimePeriods = TimePeriods.Substring(1);
}
Retval = DICommon.SplitString(TimePeriods, ",");
}
catch (Exception)
{
}
return Retval;
}
public string UpdateTimePeriods(DIConnection dbConnection, DIQueries dbQueries, UserSelection dbUserSelection)
{
string RetVal = string.Empty;
try
{
IDataReader TimeReader = null;
//'-- Set the MRD filter to true
if (this._IsToMRD | this._MRD)
{
dbUserSelection.DataViewFilters.MostRecentData = true;
}
else
{
dbUserSelection.DataViewFilters.MostRecentData = false;
}
if (this._IsToMRD || !string.IsNullOrEmpty(this._ToTimePeriod) || string.IsNullOrEmpty(dbUserSelection.TimePeriodNIds.Trim()))
{
if (this._IsToMRD)
{
//'-- Get all the time period greater then TO time period
TimeReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectedTimePeriodsRange(this._FromTimePeriod, "", dbUserSelection));
}
else if (!string.IsNullOrEmpty(this._ToTimePeriod))
{
//'-- Get auto selected time period between from and two
TimeReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectedTimePeriodsRange(this._FromTimePeriod, this._ToTimePeriod, dbUserSelection));
}
else if (string.IsNullOrEmpty(dbUserSelection.TimePeriodNIds.Trim()))
{
//-- Get auto selected time periods
TimeReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectByIndicatorAreaSource(string.Empty, dbUserSelection.AreaNIds, dbUserSelection.SourceNIds, dbUserSelection.IndicatorNIds));
}
while (TimeReader.Read())
{
RetVal += "," + TimeReader[Timeperiods.TimePeriodNId];
}
if (!string.IsNullOrEmpty(RetVal))
{
RetVal = RetVal.Substring(1);
}
else
{
RetVal = "-1";
}
TimeReader.Close();
}
//-- Exclude the TimePeriod from the selections.
//this.ExcludeTimePeriodsFromUserSelection(dbConnection, dbQueries, dbUserSelection);
}
catch (Exception)
{
}
return RetVal;
}
/// <summary>
/// Exclude the time periods from the selections
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="dbUserSelection"></param>
public void ExcludeTimePeriodsFromUserSelection(DIConnection dbConnection, DIQueries dbQueries, UserSelection dbUserSelection)
{
if (!string.IsNullOrEmpty(this._ExcludeTimePeriods))
{
string[] ExcludedTimePeriods = new string[0];
string[] SelectedTimePeriods = new string[0];
string[] AutoSelectedTimePeriods = new string[0];
bool NoRecord = true;
bool RefillWithAutoSelection = false;
IDataReader TimeReader;
//'-- Create the array of excluded and selected TP
ExcludedTimePeriods = DICommon.SplitString(this._ExcludeTimePeriods, ",");
SelectedTimePeriods = DICommon.SplitString(dbUserSelection.TimePeriods, Delimiter.TEXT_DELIMITER);
AutoSelectedTimePeriods = this.AutoSelectedTimePeriods(dbConnection, dbQueries, dbUserSelection);
for (int ExcludedIndex = 0; ExcludedIndex <= ExcludedTimePeriods.Length - 1; ExcludedIndex++)
{
for (int SelectedIndex = 0; SelectedIndex <= SelectedTimePeriods.Length - 1; SelectedIndex++)
{
if (ExcludedTimePeriods[ExcludedIndex] == SelectedTimePeriods[SelectedIndex])
{
SelectedTimePeriods[SelectedIndex] = string.Empty;
break;
}
}
//-- Remove the excluded time period from the auto selected time period array.
for (int AutoSelectedIndex = 0; AutoSelectedIndex <= AutoSelectedTimePeriods.Length - 1; AutoSelectedIndex++)
{
if (ExcludedTimePeriods[ExcludedIndex] == AutoSelectedTimePeriods[AutoSelectedIndex])
{
AutoSelectedTimePeriods[AutoSelectedIndex] = string.Empty;
break;
}
}
}
//'-- Update the table user selection
dbUserSelection.TimePeriodNIds = string.Empty;
dbUserSelection.TimePeriods = string.Empty;
for (int SelectedIndex = 0; SelectedIndex <= SelectedTimePeriods.Length - 1; SelectedIndex++)
{
if (!string.IsNullOrEmpty(SelectedTimePeriods[SelectedIndex]))
{
if (string.IsNullOrEmpty(dbUserSelection.TimePeriods))
{
dbUserSelection.TimePeriods = SelectedTimePeriods[SelectedIndex];
}
else
{
dbUserSelection.TimePeriods += Delimiter.TEXT_DELIMITER + SelectedTimePeriods[SelectedIndex];
}
}
}
if (string.IsNullOrEmpty(dbUserSelection.TimePeriodNIds.Trim()))
{
RefillWithAutoSelection = true;
}
for (int AutoSelectedIndex = 0; AutoSelectedIndex <= AutoSelectedTimePeriods.Length - 1; AutoSelectedIndex++)
{
if (!string.IsNullOrEmpty(AutoSelectedTimePeriods[AutoSelectedIndex]))
{
NoRecord = false;
//-- Fill time period NIds with auto selected nids
if (RefillWithAutoSelection)
{
if (string.IsNullOrEmpty(dbUserSelection.TimePeriods))
{
dbUserSelection.TimePeriods = AutoSelectedTimePeriods[AutoSelectedIndex];
}
else
{
dbUserSelection.TimePeriods += Delimiter.TEXT_DELIMITER + AutoSelectedTimePeriods[AutoSelectedIndex];
}
}
else
{
break;
}
}
}
if (NoRecord)
{
dbUserSelection.TimePeriodNIds = "-1";
dbUserSelection.TimePeriods = string.Empty;
}
}
}
/// <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;
}
/// <summary>
/// Exports Data Entry Spreadsheet for MICS Compiler. It exports workbook for area specified and generated Indicator-Unit data sheets.
/// </summary>
/// <param name="dBConnection"></param>
/// <param name="dBQueries"></param>
/// <param name="userSelection"></param>
/// <param name="xlsFileNameWPath"></param>
/// <returns></returns>
public bool ExportMICSCompilerDES(DIConnection dBConnection, DIQueries dBQueries, int areaNId, string xlsFileNameWPath, string languageFileNameWPath)
{
bool RetVal = false;
IWorkbook DESWorkbook = null;
DataTable DistinctIUS = null;
DataTable DistinctIU = null;
DataTable AllData = null;
DataTable IC_IUSTable = null;
DataTable DistinctSubgroupVal = null;
String SqlQuery = string.Empty;
DataView FilteredDataView = null;
int IUCounter = 0;
UserSelection userSelection = new UserSelection();
int ProgressBarValue = 0;
try
{
// 1. Get all IUS for IC Sector with sorting on ICName, IndicatorName, UnitName, SubgroupVals
DistinctIUS = dBConnection.ExecuteDataTable(dBQueries.IUS.GetDistinctIUSByIC(ICType.Sector, string.Empty, FieldSelection.Light, IndicatorClassifications.ICName + ", " + Indicator.IndicatorName + ", " + Unit.UnitName + ", " + SubgroupVals.SubgroupVal));
//- Get Distinct IU out of Distinct IUS
DistinctIU = DistinctIUS.DefaultView.ToTable(true, Indicator.IndicatorNId, Indicator.IndicatorName, Indicator.IndicatorGId, Unit.UnitNId, Unit.UnitName, Unit.UnitGId);
//DistinctIU.DefaultView.Sort = Indicator.IndicatorName + " ASC";
//- 2. Get all data for given Area from UT_Data
// Set specified AreaNId in UserSelection, to get PresentationData only for selected Area
userSelection.AreaNIds = areaNId.ToString();
userSelection.ShowIUS = false;
// Get Presentation Data for specified IU in userSelection using DIDataView class.
AllData = DIExport.GetPresentationData(dBConnection, dBQueries, userSelection, languageFileNameWPath).Table;
// Add Footnote in DataView, as GetPresentationData() does not include FootNote
DIExport.AddFootNoteInDataTable(ref AllData, dBConnection, dBQueries);
//- Get All IC_IUS at once
IC_IUSTable = dBConnection.ExecuteDataTable(dBQueries.IndicatorClassification.GetICForIUSNId(FilterFieldType.None, "", string.Empty, ICType.Sector, FieldSelection.Light));
//// Initialize progress bar
//this.RaiseProgressBarInitialize(DistinctIU.Rows.Count + 1);
// 3. Iterate through each Row having distinct Indicator + Unit.
foreach (DataRowView drIndicatorUnit in DistinctIU.DefaultView)
{
//// Prepare DES DataView for given IU
FilteredDataView = DIExport.GetMICSDataByIndicatorUnitArea(dBConnection, dBQueries, AllData, DistinctIUS, IC_IUSTable, languageFileNameWPath, areaNId, drIndicatorUnit.Row);
ProgressBarValue++;
this.RaiseProgressBarIncrement(ProgressBarValue); //raise Progressbar_Increment event
// Set Indicator, Unit Name for progress bar event, and raise Progress_Increment event
this.RaiseProcessIndicatorUnitInfo(drIndicatorUnit[Indicator.IndicatorName].ToString(), drIndicatorUnit[Unit.UnitName].ToString(), FilteredDataView.Count);
if (FilteredDataView.Count > 0)
{
// Set sort order on FilteredDataView.
FilteredDataView.Sort = Timeperiods.TimePeriod + " ASC";
IUCounter++;
try
{
// Process Workbook by filling worksheet with DataView.
this.ProcessMICSDataWorkbook(ref DESWorkbook, FilteredDataView, IUCounter, xlsFileNameWPath, languageFileNameWPath);
}
catch (Exception)
{
}
}
}
#region "- INDEX Sheet -"
//- Set AreaID and AreaName in Index Sheet. MICS Index sheet is first sheet in the workbook.
IWorksheet MICSIndexWorsheet = DESWorkbook.Worksheets[0];
DataTable dtArea = dBConnection.ExecuteDataTable(dBQueries.Area.GetArea(FilterFieldType.NId, areaNId.ToString(), DevInfo.Lib.DI_LibDAL.Queries.Area.Select.OrderBy.AreaNId));
if (dtArea.Rows.Count > 0)
{
MICSIndexWorsheet.Cells[Constants.MICSCompilerDESCells.IndexSheetAreaIDCell].Value = dtArea.Rows[0][Area.AreaID].ToString();
MICSIndexWorsheet.Cells[Constants.MICSCompilerDESCells.IndexSheetAreaNameCell].Value = dtArea.Rows[0][Area.AreaName].ToString();
}
//- Set Distinct Subgroups in Index Sheet. (This List is used as DropDown List Reference in subgroupVal Column in every Data Sheet)
DistinctSubgroupVal = DistinctIUS.DefaultView.ToTable(true, SubgroupVals.SubgroupVal);
foreach (DataRow drSG in DistinctSubgroupVal.Rows)
{
MICSIndexWorsheet.Cells["G4"].Insert(InsertShiftDirection.Down);
}
ExportDES.PasteDataViewInSheet(MICSIndexWorsheet, DistinctSubgroupVal.DefaultView, Constants.MICSCompilerDESCells.IndexSheetSubgroupListCell);
MICSIndexWorsheet.Protect(string.Empty);
#endregion
if (!(DESWorkbook == null))
{
// Delete last worksheet which is left blank.
DESWorkbook.Worksheets[DESWorkbook.Worksheets.Count - 1].Delete();
// Set first sheet as active sheet.
DESWorkbook.Worksheets[0].Select();
// Save Workbook
DESWorkbook.Save();
}
RetVal = true;
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (!(DESWorkbook == null))
{
DESWorkbook.Save();
}
//this.RaiseProgressBarClose();
}
return RetVal;
}
private DataView GetBlankDESDataViewWithoutIUS(UserSelection userSelection, DIConnection dBConnection, DIQueries dBQueries)
{
DataView RetVal = null;
string SqlQuery = string.Empty;
try
{
// get records having blank DataValue for every possoble combinations of Specified Area, Time, Source.
// Build SQl query for empty DES. (DataValue, Denominator, subgroupval, subgroupval_gid columns will be blank)
SqlQuery = this.GetQueryForEmptyDES(userSelection, dBQueries);
// Get DataView
RetVal = dBConnection.ExecuteDataTable(SqlQuery).DefaultView;
//////// 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();
}
catch (Exception ex)
{
}
finally
{
if (RetVal == null)
{
RetVal = (new DataTable()).DefaultView;
}
}
return RetVal;
}
/// <summary>
/// Generates DES for missing data ( blank DES + DES where data exists )
/// </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>
public bool ExportMissingDataDES(bool singleWorkbook, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, Fields sortedFields, string xlsFileNameWPath, bool includeGUID, string languageFileNameWPath)
{
bool RetVal = false;
IWorkbook DESWorkbook = null;
DataTable DistinctIU;
String SqlQuery = string.Empty;
DataView FilteredDataView = null;
DataView BlankDESDataView = null;
DataTable BlankRecordsTable = null;
DataTable WhereDataExistsTable = null;
DataColumn[] PrimayKeyColumns;
string RowFilter = string.Empty;
int IUCounter = 0;
int ProgressBarValue = 0;
string IndicatorNId = string.Empty;
string UnitNId = string.Empty;
try
{
//1. Get Distinct IU
DistinctIU = DIExport.GetDistinctIU(true, dBConnection, dBQueries, userSelection);
// Initialize progress bar
this.RaiseProgressBarInitialize(DistinctIU.Rows.Count + 1);
// 3. Iterate through each Row having distinct Indicator + Unit.
foreach (DataRow dr in DistinctIU.Rows)
{
IndicatorNId = dr[Indicator.IndicatorNId].ToString();
UnitNId = dr[Unit.UnitNId].ToString();
// 2.1 get records where data exists
WhereDataExistsTable = this.GetDESDataViewWhereDataExits(includeGUID, userSelection, dBConnection, dBQueries, languageFileNameWPath, IndicatorNId, UnitNId).Table;
// 2.2 get blank records for all given combination
// get blank DES data view
BlankRecordsTable = GetBlankDESDataView(BlankDESDataView, IndicatorNId, UnitNId, singleWorkbook, dBConnection, dBQueries, userSelection, includeGUID).Table;
// 2.3. merge WhereDataExistsTable and BlankRecordsTable
// delete denominator column from BlankRecordsTable
if (BlankRecordsTable.Columns.Contains(Data.DataDenominator))
{
BlankRecordsTable.Columns.Remove(Data.DataDenominator);
}
// merge missing records & records where data exists
if (WhereDataExistsTable != null)
{
// set primary key
if (WhereDataExistsTable.Rows.Count > 0)
{
PrimayKeyColumns = this.GetPrimaryKeyColumnForMissingDataDES(WhereDataExistsTable, userSelection);
WhereDataExistsTable.PrimaryKey = PrimayKeyColumns;
}
}
if (BlankRecordsTable != null)
{
// set primary key
if (BlankRecordsTable.Rows.Count > 0)
{
PrimayKeyColumns = this.GetPrimaryKeyColumnForMissingDataDES(BlankRecordsTable, userSelection);
BlankRecordsTable.PrimaryKey = PrimayKeyColumns;
}
}
try
{
if (userSelection.TimePeriodNIds.Length == 0 || userSelection.TimePeriodNIds.Length == 0)
{
foreach (DataRow BlankDESRow in BlankRecordsTable.Rows)
{
string SelectionClause = string.Empty;
SelectionClause = Area.AreaID + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Area.AreaID])) + "' AND "
+ Indicator.IndicatorName + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Indicator.IndicatorName])) + "' AND "
+ Unit.UnitName + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Unit.UnitName])) + "' AND "
+ SubgroupVals.SubgroupVal + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[SubgroupVals.SubgroupVal])) + "'";
if (userSelection.TimePeriodNIds.Length > 0)
{
SelectionClause += " AND " + Timeperiods.TimePeriod + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Timeperiods.TimePeriod])) + "'";
}
if (userSelection.SourceNIds.Length > 0)
{
SelectionClause += " AND " + IndicatorClassifications.ICName + " ='" + DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[IndicatorClassifications.ICName])) + "'";
}
if (WhereDataExistsTable.Select(SelectionClause).Length == 0)
{
DataRow NewRow = WhereDataExistsTable.NewRow();
// add timeperiod, Area_ID,Area_name,IC_name,Indicator_name,indicator_gid, Unit_Name,unit_gid, Sugbroup_Val, Subgroup_val_gid
// Timeperiod
NewRow[Timeperiods.TimePeriod] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Timeperiods.TimePeriod]));
// source
NewRow[IndicatorClassifications.ICName] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[IndicatorClassifications.ICName]));
// Area
NewRow[Area.AreaID] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Area.AreaID]));
NewRow[Area.AreaName] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Area.AreaName]));
//Indicator
NewRow[Indicator.IndicatorName] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Indicator.IndicatorName]));
if (BlankRecordsTable.Columns.Contains(Indicator.IndicatorGId))
{
NewRow[Indicator.IndicatorGId] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Indicator.IndicatorGId]));
}
//Unit
NewRow[Unit.UnitName] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Unit.UnitName]));
if (BlankRecordsTable.Columns.Contains(Unit.UnitGId))
{
NewRow[Unit.UnitGId] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[Unit.UnitGId]));
}
// subgroup_val
NewRow[SubgroupVals.SubgroupVal] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[SubgroupVals.SubgroupVal]));
if (BlankRecordsTable.Columns.Contains(SubgroupVals.SubgroupValGId))
{
NewRow[SubgroupVals.SubgroupValGId] = DICommon.RemoveQuotes(Convert.ToString(BlankDESRow[SubgroupVals.SubgroupValGId]));
}
WhereDataExistsTable.Rows.Add(NewRow);
WhereDataExistsTable.AcceptChanges();
}
}
}
else
{
WhereDataExistsTable.Merge(BlankRecordsTable, true, MissingSchemaAction.AddWithKey);
}
}
catch (Exception ex)
{
}
// add missing columns
if (WhereDataExistsTable.Columns.Contains(Data.DataDenominator) == false)
{
WhereDataExistsTable.Columns.Add(Data.DataDenominator);
}
FilteredDataView = WhereDataExistsTable.DefaultView;
//////// 2.4 Limit DataRows to 65,536 as Excels sheet has 65,536 rows limit.
//////if (FilteredDataView.Table.Rows.Count > 65500)
//////{
////// for (int i = FilteredDataView.Table.Rows.Count - 1; i > 65500; i--)
////// {
////// FilteredDataView.Table.Rows[i].Delete();
////// }
//////}
//////FilteredDataView.Table.AcceptChanges();
// 2.5 update filteredDataView
try
{
// 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";
}
}
//// Update Sector , Class in DataView
//DIExport.AddSectorClassInDataView(ref FilteredDataView, dBConnection, dBQueries, IndicatorNId, UnitNId);
FilteredDataView.Table.AcceptChanges();
}
catch (Exception)
{
}
ProgressBarValue++;
this.RaiseProgressBarIncrement(ProgressBarValue); //raise Progressbar_Increment event
if (FilteredDataView.Count > 0)
{
//// Set Indicator, Unit Name for progress bar event, and raise Progress_Increment event
//this.RaiseProcessIndicatorUnitInfo(FilteredDataView.Table.Rows[0][Indicator.IndicatorName].ToString(), FilteredDataView.Table.Rows[0][Unit.UnitName].ToString(), FilteredDataView.Table.Rows.Count);
// Set sort order on FilteredDataView.
FilteredDataView.Sort = this.GetSortString(sortedFields);
IUCounter++;
try
{
// Process Workbook by filling worksheet with DataView.
this.ProcessWorkbook(singleWorkbook, ref DESWorkbook, FilteredDataView, IUCounter, xlsFileNameWPath, languageFileNameWPath, dBConnection, dBQueries, IndicatorNId, UnitNId);
}
catch (Exception)
{
}
}
}
//if (singleWorkbook)
//{
if (!(DESWorkbook == null))
{
// Delete last worksheet which is left blank.
DESWorkbook.Worksheets[DESWorkbook.Worksheets.Count - 1].Delete();
// Set first sheet as active sheet.
DESWorkbook.Worksheets[0].Select();
// Save Workbook
DESWorkbook.Save();
}
//}
RetVal = true;
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
finally
{
if (!(DESWorkbook == null))
{
DESWorkbook.Save();
}
// Close progress bar
this.RaiseProcessIndicatorUnitInfo(string.Empty, string.Empty, 0);
this.RaiseProgressBarClose();
}
return RetVal;
}
private DataColumn[] GetPrimaryKeyColumnForMissingDataDES(DataTable table, UserSelection userSelections)
{
DataColumn[] RetVal = null;
RetVal = new DataColumn[] { table.Columns[Indicator.IndicatorName], table.Columns[Unit.UnitName], table.Columns[SubgroupVals.SubgroupVal], table.Columns[Timeperiods.TimePeriod], table.Columns[Area.AreaID], table.Columns[IndicatorClassifications.ICName] };
return RetVal;
}
/// <summary>
/// It extracts blank DES workbook from resource file and saves it as specified file name.
/// </summary>
/// <param name="fileNameWpath">Desired file name of workbook</param>
private static DataTable GetDistinctIU(bool emptyDES, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection)
{
DataTable RetVal = null;
string[] IndicatorUnitColumns;
DataTable DistinctIUS;
DataRow IUNids;
try
{
if (!emptyDES)
{
// 1. Get Distinct IUS from Database.
DistinctIUS = dBConnection.ExecuteDataTable(dBQueries.IUS.GetAutoDistinctIUS(userSelection));
// 2. Get Distinct IU from IUS
IndicatorUnitColumns = new string[4];
IndicatorUnitColumns[0] = Indicator.IndicatorNId;
IndicatorUnitColumns[1] = Unit.UnitNId;
IndicatorUnitColumns[2] = Indicator.IndicatorName;
IndicatorUnitColumns[3] = Unit.UnitName;
if (userSelection.UnitNIds.Length > 0)
{
DistinctIUS.DefaultView.RowFilter = Unit.UnitNId + " IN (" + userSelection.UnitNIds + ")";
}
DistinctIUS.DefaultView.Sort = Indicator.IndicatorName + ", " + Unit.UnitName + " ASC";
RetVal = DistinctIUS.DefaultView.ToTable(true, IndicatorUnitColumns);
}
else
{
// Incase of EmptyDES, all possible Indicator + Unit combinations will be made.
RetVal = new DataTable();
if (userSelection.ShowIUS)
{
// 1. Get Distinct IUS from Database.
DistinctIUS = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUS(FilterFieldType.NId, userSelection.IndicatorNIds, FieldSelection.Light));
}
else
{
// 1. Get Distinct IUS from Database.
DistinctIUS = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUSNIdByI_U_S(userSelection.IndicatorNIds, userSelection.UnitNIds, userSelection.SubgroupValNIds));
}
// 3. Get Distinct IU from IUS
IndicatorUnitColumns = new string[2];
IndicatorUnitColumns[0] = Indicator.IndicatorNId;
IndicatorUnitColumns[1] = Unit.UnitNId;
RetVal = DistinctIUS.DefaultView.ToTable(true, IndicatorUnitColumns);
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
public static bool ExportDatabase(UserSelection userSelection, int areaLevel, DIConnection sourceDBConnection, bool maintainTemplate, string destinationDBNameWPath, string tempFolderPath)
{
return DIExport.ExportDatabase(userSelection, areaLevel, sourceDBConnection, null, maintainTemplate, destinationDBNameWPath, tempFolderPath);
}
/// <summary>
/// Check for existence of nids in the in the updated user selection. If I,A,T is missing in the new updated user selection, then "no record found" msg will appears.
/// </summary>
/// <param name="oldUserSelection"></param>
/// <param name="updatedUserSelection"></param>
/// <returns></returns>
/// <remarks></remarks>
public static bool CompareIndicatorTimePeriodArea(UserSelection oldUserSelection, UserSelection updatedUserSelection)
{
bool Retval = false;
try
{
//-- Indicator check
if (!string.IsNullOrEmpty(oldUserSelection.IndicatorNIds) && string.IsNullOrEmpty(updatedUserSelection.IndicatorNIds))
{
Retval = true;
}
//-- Area check
if (!Retval && (!string.IsNullOrEmpty(oldUserSelection.AreaNIds) && string.IsNullOrEmpty(updatedUserSelection.AreaNIds)))
{
Retval = true;
}
//-- Time period. check
if (!Retval && (!string.IsNullOrEmpty(oldUserSelection.TimePeriodNIds) && string.IsNullOrEmpty(updatedUserSelection.TimePeriodNIds)))
{
Retval = true;
}
}
catch (Exception ex)
{
}
return Retval;
}
/// <summary>
/// Export database for Online and create new database file for Online connection
/// </summary>
/// <param name="userSelection"></param>
/// <param name="areaLevel"></param>
/// <param name="sourceDBConnection"></param>
/// <param name="sourceDBQueries"></param>
/// <param name="maintainTemplate"></param>
/// <param name="destinationDBNameWPath"></param>
/// <param name="tempFolderPath"></param>
/// <param name="createNewDatabaseForOnline"></param>
/// <returns></returns>
public static bool ExportDatabase(UserSelection userSelection, int areaLevel, DIConnection sourceDBConnection, DIQueries sourceDBQueries, bool maintainTemplate, string destinationDBNameWPath, string tempFolderPath,bool createNewDatabaseForOnline)
{
bool RetVal = false;
if (sourceDBConnection != null)
{
switch (sourceDBConnection.ConnectionStringParameters.ServerType)
{
case DIServerType.Excel:
break;
case DIServerType.MsAccess:
//-- Export database into Access (mdb).
Export.ExportDatabase ExportDB = new ExportDatabase(userSelection, sourceDBConnection);
ExportDB.AreaLevel = areaLevel;
RetVal = ExportDB.ExportMDB(maintainTemplate, destinationDBNameWPath, tempFolderPath);
break;
case DIServerType.Oracle:
break;
case DIServerType.SqlServer:
case DIServerType.MySql:
case DIServerType.SqlServerExpress:
Export.ExportOnlineDatabase ExportOnlineDB = new ExportOnlineDatabase(userSelection, sourceDBConnection, sourceDBQueries);
RetVal = ExportOnlineDB.ExportMDB(destinationDBNameWPath, tempFolderPath, createNewDatabaseForOnline);
break;
default:
break;
}
}
return RetVal;
}
/// <summary>
/// Get the most recent time periods.
/// </summary>
/// <param name="userSelection"></param>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <returns></returns>
public static string GetMostRecentTimePeriods(UserSelection userSelection, DIConnection dbConnection, DIQueries dbQueries)
{
string RetVal = string.Empty;
try
{
string AreaNId = string.Empty;
string IUSNId = string.Empty;
StringBuilder sbTimePeriodNIDs = new StringBuilder();
DataTable dtData = dbConnection.ExecuteDataTable(dbQueries.Data.GetAutoSelectedIUSTimePeriodArea(userSelection));
DataView dvData = dtData.DefaultView;
//sort dataview in decending order of timeperiod so that latest record can be obtained
dvData.Sort = Indicator_Unit_Subgroup.IUSNId + "," + Area.AreaNId + "," + Timeperiods.TimePeriod + " Desc";
foreach (DataRow DRowParentTable in dvData.ToTable().Rows)
{
// Get the record for latest timeperiod.
if (AreaNId != DRowParentTable[Area.AreaNId].ToString() || IUSNId != DRowParentTable[Indicator_Unit_Subgroup.IUSNId].ToString())
{
AreaNId = DRowParentTable[Area.AreaNId].ToString();
IUSNId = DRowParentTable[Indicator_Unit_Subgroup.IUSNId].ToString();
sbTimePeriodNIDs.Append("," + DRowParentTable[Data.TimePeriodNId].ToString());
}
}
if (sbTimePeriodNIDs.Length > 0)
{
RetVal = sbTimePeriodNIDs.ToString().Substring(1);
}
}
catch (Exception)
{
}
return RetVal;
}
/// <summary>
/// It prepares DataView having requred Data for DES generation.
/// <para>For empty DES, it prepares DataView where no DataValue presents against UserSelections.</para>
/// </summary>
/// <param name="includeGUID">whether GUID to be included or not.</param>
/// <param name="userSelection">userSelection object</param>
/// <param name="dBConnection">Source DBase DIConnection object</param>
/// <param name="dBQueries">Source DBase DBQueries object.</param>
/// <param name="indicatorNId">indicator NId for which DataView to be generated.</param>
/// <param name="unitNID">unit NId for which DataView to be generated.</param>
private static DataView GetMissingRecords(bool includeGUID, UserSelection userSelection, DIConnection dBConnection, DIQueries dBQueries, string languageFileNameWPath, string indicatorNId, string unitNId
)
{
DataView RetVal = null;
string SqlQuery = string.Empty;
string UserSelectiosIndicatorNids = string.Empty;
string UserSelectiosUnitNids = string.Empty;
string OriginalSubgroupNIds = string.Empty;
string IUSNIds = string.Empty;
bool UserSelectionShowIUS = false;
DataTable AutoSubgroupVals = null;
DataTable IUSTable;
try
{
// If emptyDES = true, then get records having blank DataValue for every possoble combinations of Specified Area, Time, Source, Subgroup.
// Preserve original SubgroupNIds into a temp
OriginalSubgroupNIds = userSelection.SubgroupValNIds;
// 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)
{
// Get SubgroupNIDs for given IUSNId as userSelection.IndicatorNId
AutoSubgroupVals = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUS(FilterFieldType.NId, userSelection.IndicatorNIds, FieldSelection.Light));
// Set filter for given I, U
AutoSubgroupVals.DefaultView.RowFilter = Indicator.IndicatorNId + " = " + indicatorNId + " AND " + Unit.UnitNId + " = " + unitNId;
AutoSubgroupVals = AutoSubgroupVals.DefaultView.ToTable();
}
else
{
// Get SubgroupNIDs for given Indicator + Unit
AutoSubgroupVals = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUSNIdByI_U_S(indicatorNId, unitNId, string.Empty));
}
// Set those SubgroupNIds into userSelection
userSelection.SubgroupValNIds = DIExport.DataColumnValuesToString(AutoSubgroupVals, 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);
// Build SQl query for empty DES. (DataValue column will be blank and dont include where data exists)
SqlQuery = DIExport.GetMissingRecrodsDESQuery(includeGUID, userSelection, dBQueries, IUSNIds);
// Get DataView
RetVal = dBConnection.ExecuteDataTable(SqlQuery).DefaultView;
//////// 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();
// Set original SubgroupNIDs back into userSelection
userSelection.SubgroupValNIds = OriginalSubgroupNIds;
// Rename GID columns if not required.
if (!(includeGUID))
{
RetVal.Table.Columns[Indicator.IndicatorGId].ColumnName = "I_GID";
RetVal.Table.Columns[Unit.UnitGId].ColumnName = "U_GID";
if (RetVal.Table.Columns.Contains(SubgroupVals.SubgroupValGId))
{
RetVal.Table.Columns[SubgroupVals.SubgroupValGId].ColumnName = "S_GID";
}
}
// Update Sector , Class in DataView
DIExport.AddSectorClassInDataView(ref RetVal, dBConnection, dBQueries, indicatorNId, unitNId);
RetVal.Table.AcceptChanges();
}
catch (Exception ex)
{
}
finally
{
if (RetVal == null)
{
RetVal = (new DataTable()).DefaultView;
}
}
return RetVal;
}
public UserPreference(string languageFolder)
{
// -- Create the instance of general preference class.
this._General = new GeneralPreference();
// -- Create the instance of language preference class.
this._Language = new LanguagePreference();
// -- Create the instance of indicator preference class.
this._Indicator = new IndicatorPreference();
// -- Create the instance of dataview preference class.
this._DataView = new DataviewPreference(Path.Combine(languageFolder, this._Language.InterfaceLanguage + ".xml"));
// -- Create the instance of chart preference class.
this._Chart = new GraphPreference();
// -- Create the instance of mapping preference class.
this._Mapping = new MapPrefernce();
//-- Vreate the instance of default legend colors and add the colors into it.
this._Mapping.DefaultLegendColors = new List<string>();
if (this._Mapping.DefaultLegendColors.Count == 0)
{
this._Mapping.DefaultLegendColors.Add("#DEE4F0");
this._Mapping.DefaultLegendColors.Add("#94A9D3");
this._Mapping.DefaultLegendColors.Add("#4A6EB6");
this._Mapping.DefaultLegendColors.Add("#003399");
}
// -- Create the instance of sound preference class.
this._Sound = new SoundPreference();
// -- Create the instance of database preference class.
this._Database = new DatabasePreference();
// -- Create the instance of user selection.
this._UserSelection = new UserSelection();
// -- Create the instance of MRU preference class.
this._MRU = new MRUPreference();
//-- Event to update the language of fields in dataview.
this._Language.InterfaceLanguageChanged += new LanguagePreference.InterfaceLanguageChangedDelegate(_Language_UpdateLanguageEvent);
//-- Set the language folder path.
LanguageFolderPath = languageFolder;
}
/// <summary>
/// Resets the current selection and copies the new selection.
/// </summary>
/// <param name="userSelection"></param>
public void CopyUserSelection(UserSelection userSelection)
{
// -- Reset the current Selections.
this.Reset();
// -- Indicators
this._IndicatorNIds = userSelection.IndicatorNIds;
this._IndicatorGIds = userSelection.IndicatorGIds;
this._ShowIUS = userSelection.ShowIUS;
// -- Area
this._AreaIds = userSelection.AreaIds;
this._AreaNIds = userSelection._AreaNIds;
// -- Time periods
this.TimePeriodNIds = userSelection.TimePeriodNIds;
this.TimePeriods = userSelection.TimePeriods;
// -- source
this.SourceNames = userSelection.SourceNames;
this.SourceNIds = userSelection.SourceNIds;
// -- Subgroup
this.SubgroupValNIds = userSelection.SubgroupValNIds;
this.SubgroupValGIds = userSelection.SubgroupValGIds;
// -- Unit
this.UnitNIds = userSelection.UnitNIds;
this.UnitGIds = userSelection.UnitGIds;
// -- ICs
this.ICNIds = userSelection.ICNIds;
this.ICGIds = userSelection.ICGIds;
// -- Datavalue filters
this.DataViewFilters.DataValueFilter.FromDataValue = userSelection.DataViewFilters.DataValueFilter.FromDataValue;
this.DataViewFilters.DataValueFilter.ToDataValue = userSelection.DataViewFilters.DataValueFilter.ToDataValue;
this.DataViewFilters.DataValueFilter.OpertorType = userSelection.DataViewFilters.DataValueFilter.OpertorType;
// -- Deleted Data NIds
this.DataViewFilters.DeletedDataNIds = userSelection.DataViewFilters.DeletedDataNIds;
this.DataViewFilters.DeletedDataGIds = userSelection.DataViewFilters.DeletedDataGIds;
// -- Deleted Source Nids
this.DataViewFilters.ShowSourceByIUS = userSelection.DataViewFilters.ShowSourceByIUS;
this.DataViewFilters.DeletedSourceNames = userSelection.DataViewFilters.DeletedSourceNames;
this.DataViewFilters.DeletedSourceNIds = userSelection.DataViewFilters.DeletedSourceNIds;
// -- Deleted Recommended Source
this.DataViewFilters.ShowRecommendedSourceByRank = userSelection.DataViewFilters.ShowRecommendedSourceByRank;
this.DataViewFilters.DeletedRanks = userSelection.DataViewFilters.DeletedRanks;
// -- Deleted Subgroup Nids
this.DataViewFilters.ShowSubgroupByIndicator = userSelection.DataViewFilters.ShowSubgroupByIndicator;
this.DataViewFilters.DeletedSubgroupNIds = userSelection.DataViewFilters.DeletedSubgroupNIds;
this.DataViewFilters.DeletedSubgroupGIds = userSelection.DataViewFilters.DeletedSubgroupGIds;
// -- Deleted Indicators Nids
this.DataViewFilters.ShowUnitByIndicator = userSelection.DataViewFilters.ShowUnitByIndicator;
this.DataViewFilters.DeletedUnitNIds = userSelection.DataViewFilters.DeletedUnitNIds;
this.DataViewFilters.DeletedUnitGIds = userSelection.DataViewFilters.DeletedUnitGIds;
// -- MRD
this.DataViewFilters.MostRecentData = userSelection.DataViewFilters.MostRecentData;
// -- Indicator Data Value filters
foreach (IndicatorDataValueFilter Filter in userSelection.DataViewFilters.IndicatorDataValueFilters)
{
this.DataViewFilters.IndicatorDataValueFilters.Add(Filter);
}
this.DataViewFilters.IndicatorDataValueFilters.IncludeArea = userSelection.DataViewFilters.IndicatorDataValueFilters.IncludeArea;
}
/// <summary>
/// It prepares SQl query to get records each having blank DataValue.
/// No. of records will be (ICNid x TimePeriodNId x AreaNid x SubgroupValNid).
/// </summary>
/// <param name="includeGUID">true, if subgroupGIS to be included.</param>
/// <param name="userSelection">user Selections </param>
/// <param name="dBQueries">dbQueries to get tableNames</param>
/// <param name="IUSNIds"></param>
/// <returns></returns>
private static string GetMissingRecrodsDESQuery(bool includeGUID, UserSelection userSelection, DIQueries dBQueries, string IUSNIds)
{
string RetVal = string.Empty;
try
{
StringBuilder SqlQuery = new StringBuilder();
SqlQuery.Append(" SELECT TP." + Timeperiods.TimePeriod + ", A." + Area.AreaID + ", A." + Area.AreaName + ", \"\" AS " + Data.DataValue + ",");
SqlQuery.Append(" S." + SubgroupVals.SubgroupVal + ",");
if (includeGUID)
{
SqlQuery.Append(" S." + SubgroupVals.SubgroupValGId + ",");
}
SqlQuery.Append(" IC." + IndicatorClassifications.ICName + ", \"\" AS FootNote, \"\" AS Data_Denominator,");
SqlQuery.Append(" I." + Indicator.IndicatorName + ",I." + Indicator.IndicatorGId + " ,U." + Unit.UnitName + ",U." + Unit.UnitGId + " ");
SqlQuery.Append(" FROM " + dBQueries.TablesName.IndicatorClassifications + " AS IC, " + dBQueries.TablesName.TimePeriod + " AS TP, " + dBQueries.TablesName.Area + " AS A," + dBQueries.TablesName.Indicator + " AS I ," + dBQueries.TablesName.Unit + " AS U, " + dBQueries.TablesName.SubgroupVals + " AS S");
SqlQuery.Append(" WHERE IC." + IndicatorClassifications.ICType + " = 'SR' AND IC." + IndicatorClassifications.ICParent_NId + "<>-1 ");
// Apply Filter for AreaNid, if present.
if (userSelection.AreaNIds.Length > 0)
{
SqlQuery.Append(" AND (A." + Area.AreaNId + " in (" + userSelection.AreaNIds + ")) ");
}
// Apply Filter for TimePeriodNid ,if present.
if (userSelection.TimePeriodNIds.Length > 0)
{
SqlQuery.Append(" AND (TP." + Timeperiods.TimePeriodNId + " in (" + userSelection.TimePeriodNIds + ")) ");
}
// Apply Filter for SourceNIds ,if present.
if (userSelection.SourceNIds.Length > 0)
{
SqlQuery.Append(" AND (IC." + IndicatorClassifications.ICNId + " in (" + userSelection.SourceNIds + ")) ");
}
SqlQuery.Append(" AND EXISTS ( Select * from " + dBQueries.TablesName.IndicatorUnitSubgroup + " IUS where IUS." + Indicator_Unit_Subgroup.IndicatorNId + "=I." + Indicator.IndicatorNId + " and IUS." + Indicator_Unit_Subgroup.UnitNId + "=U." + Unit.UnitNId + " and IUS." + Indicator_Unit_Subgroup.SubgroupValNId + "= S." + SubgroupVals.SubgroupValNId + " AND " + Indicator_Unit_Subgroup.IUSNId + " IN (" + IUSNIds + ") ");
SqlQuery.Append(" AND NOT EXISTS (Select * from " + dBQueries.TablesName.Data + " AS D where IUS." + Indicator_Unit_Subgroup.IUSNId + "= D." + Data.IUSNId + " AND A." + Area.AreaNId + " = D." + Data.AreaNId + " and D." + Data.SourceNId + " = IC." + IndicatorClassifications.ICNId + " and TP." + Timeperiods.TimePeriodNId + " = D." + Data.TimePeriodNId + " ) )");
SqlQuery.Append(" Order BY TP." + Timeperiods.TimePeriod + ", A." + Area.AreaID + ", S." + SubgroupVals.SubgroupVal + ", IC." + IndicatorClassifications.ICName + " ASC");
RetVal = SqlQuery.ToString();
}
catch (Exception)
{
}
return RetVal;
}