コード例 #1
0
        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;
        }
コード例 #2
0
        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()));
            }
        }
コード例 #3
0
ファイル: Select.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #4
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #5
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #6
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #7
0
            /// <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)
                {
                }
            }
コード例 #8
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #9
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
 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);
 }
コード例 #10
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #11
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #12
0
ファイル: DICommon.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #13
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #14
0
            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;
            }
コード例 #15
0
            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;
            }
コード例 #16
0
            /// <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;
                    }
                }
            }
コード例 #17
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #18
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #19
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        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;
        }
コード例 #20
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #21
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        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;
        }
コード例 #22
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #23
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
 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);
 }
コード例 #24
0
        /// <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;
        }
コード例 #25
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #26
0
ファイル: DICommon.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #27
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }
コード例 #28
0
        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;
        }
コード例 #29
0
        /// <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;
        }
コード例 #30
0
ファイル: DIExport.cs プロジェクト: SDRC-India/sdrcdevinfo
        /// <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;
        }