public void SerializeXML()
        {
            var bookConfig = new WorkbookPropertiesConfig();

            bookConfig.Limit           = "100";
            bookConfig.SelectedColumns = new List <string>()
            {
                "ContributionDate", "ContributionAmount"
            };
            bookConfig.AdditionalQueries = new List <string>()
            {
                "Cities|;select State, sum(ContributionAmount) from cash_contributions where state != '' group by State;"
            };
            bookConfig.DrilldownSql         = "OrgID,ContributionAmount,CountributionDate,FirstName,LastName,State,Address1,City,Zip";
            bookConfig.RefreshColumnHeaders = true;
            bookConfig.SelectedTable        = "cash_contributions";
            var criteria1 = new Criteria("And", "", "ContributionDate", "LIKE", "(01/11/2017,03/15/2017)");

            criteria1.OrIsNull = true;
            bookConfig.Criteria.Add(criteria1);

            var xml = bookConfig.SerializeXML();

            System.Diagnostics.Debug.Print(xml);
            Assert.IsTrue(xml != null);
        }
Esempio n. 2
0
        public void test()
        {
            var bookConfig = new WorkbookPropertiesConfig();

            bookConfig.Limit           = 100;
            bookConfig.SelectedColumns = new List <string>()
            {
                "ContributionDate", "ContributionAmount"
            };
            bookConfig.AdditionalQueries = new List <string>()
            {
                "Cities|;select State, sum(ContributionAmount) from cash_contributions where state != '' group by State;"
            };
            bookConfig.DrilldownSql         = "OrgID,ContributionAmount,CountributionDate,FirstName,LastName,State,Address1,City,Zip";
            bookConfig.RefreshColumnHeaders = true;
            bookConfig.SelectedTable        = "cash_contributions";
            var criteria1 = new Criteria();

            criteria1.AndOr    = Conjunction.And;
            criteria1.Column   = "ContributionDate";
            criteria1.Operator = Operator.Like;
            criteria1.Filter   = "(01/11/2017,03/15/2017)";
            criteria1.OrIsNull = true;
            bookConfig.Criteria.Add(criteria1);

            var xml = bookConfig.SerializeXML();
        }
        public void ShowSqlCreator()
        {
            if (_appConfig.GetCredentialsRequired == AppConfig.CredentialsRequired.None || _appConfig.User != null)
            {
                var          sqlCreator = new SqlCreatorView(_appConfig, _workbookPropertiesConfig);
                DialogResult result     = sqlCreator.ShowDialog();
                if (result == DialogResult.OK)
                {
                    _workbookPropertiesConfig = sqlCreator.WorkbookPropertiesConfig;

                    var queries = new Dictionary <string, DataTable>();
                    queries = sqlCreator.SQLResult; // add main query to dict

                    //if (_workbookPropertiesConfig.LastMainQuery != null)
                    //{
                    var dbConnection = new DatabaseConnectionFactory().CreateDbConnection(_appConfig.DatabaseType, _appConfig.ConnectionString);
                    foreach (KeyValuePair <string, string> query in _workbookPropertiesConfig.AdditionalQueriesAsDictionary())
                    {
                        // create interpolator object
                        var interpolator = new Interpolator();

                        // test if string needs interpolation
                        var needsInterpolation = interpolator.SetFormattable(query.Value).SetCriteria(_workbookPropertiesConfig.LastMainQuery.Criteria).NeedsInterpolation();

                        // if no, then use sql to query against database and add results to queries dict
                        if (!needsInterpolation)
                        {
                            try
                            {
                                queries.Add(query.Key, dbConnection.Query(query.Value));
                            }
                            catch
                            {
                                MessageBox.Show($"{query.Key} did not run successfully", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                        }
                        else
                        {
                            // if yes, then interpolate
                            try
                            {
                                var interpolatedQuery = interpolator.Interpolate();
                                queries.Add(query.Key, dbConnection.Query(interpolatedQuery));
                            }
                            catch
                            {
                                MessageBox.Show($"{query.Key} did not run successfully", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                        }
                    }
                    //}

                    _presenter.PasteQueriesIntoExcel(queries);
                }
            }
            else
            {
                LogIn();
            }
        }
Esempio n. 4
0
 public ExcelPresenter(MSExcel.Application app, IDatabaseConnection dbConnection, BaseSqlGenerator sqlGenerator, WorkbookPropertiesConfig workbookPropertiesConfig)
 {
     _app  = app;
     _view = app.ActiveWorkbook;
     _workbookPropertiesConfig = workbookPropertiesConfig;
     _dbConnection             = dbConnection;
     _sqlGenerator             = sqlGenerator;
 }
Esempio n. 5
0
        public DrilldownColumns(AppConfig appConfig, WorkbookPropertiesConfig workbookPropertiesConfig)
        {
            InitializeComponent();
            _appConfig = appConfig;
            _workbookPropertiesConfig = workbookPropertiesConfig;
            IDrilldownColumnsPresenter presenter = new DrilldownColumnsPresenter(
                this,
                new DatabaseConnectionFactory().CreateDbConnection(appConfig.DatabaseType, appConfig.ConnectionString));

            presenter.Initialize();
        }
        public SqlCreatorView(AppConfig appConfig, WorkbookPropertiesConfig workbookPropertiesConfig)
        {
            InitializeComponent();
            _appConfig = appConfig;
            _workbookPropertiesConfig = workbookPropertiesConfig;
            ISqlCreatorPresenter presenter = new SqlCreatorPresenter(
                this,
                new DatabaseConnectionFactory().CreateDbConnection(appConfig.DatabaseType, appConfig.ConnectionString),
                new SqlGeneratorFactory().CreateSqlGenerator(AppConfig.DatabaseType));

            presenter.Initialize();
            HighlightedAvailableColumns.AllowNew = true;
        }
        public void AdditionalQueriesAsDictionary()
        {
            var list = new List <string>()
            {
                "query1|select * from table1;", "query2|select * from table2;"
            };
            var bookConfig = new WorkbookPropertiesConfig();

            bookConfig.AdditionalQueries = list;

            var dict = bookConfig.AdditionalQueriesAsDictionary();

            Assert.IsTrue(dict.Count == 2);
            Assert.IsTrue(dict["query1"] == "select * from table1;");
            Assert.IsTrue(dict["query2"] == "select * from table2;");
        }
 public void SetDrilldownColumns()
 {
     if (_appConfig.GetCredentialsRequired == AppConfig.CredentialsRequired.None || _appConfig.User != null)
     {
         var          drilldownColumns = new DrilldownColumns(_appConfig, _workbookPropertiesConfig);
         DialogResult result           = drilldownColumns.ShowDialog();
         if (result == DialogResult.OK)
         {
             _workbookPropertiesConfig = drilldownColumns.WorkbookPropertiesConfig;
         }
     }
     else
     {
         LogIn();
     }
 }
        //public void verifyReportStructure()
        //{
        //    // dict of missing structure and related message to display to user
        //    Dictionary<string, string> errors = new Dictionary<string, string>();

        //    // check for Data worsheet
        //    try
        //    {
        //        Excel.Worksheet sheet = app.ActiveWorkbook.Sheets["Data"];
        //    }
        //    catch (Exception)
        //    {
        //        errors["Data Worksheet"] = "A worksheet named 'Data' does not exist";
        //    }

        //    // check for startCell
        //    try
        //    {
        //        Excel.Range range = app.ActiveWorkbook.Sheets["Data"].Range["startCell"];
        //    }
        //    catch (Exception)
        //    {
        //        errors["Start Cell"] = "Named range, 'startCell', does not exist in Data worksheet";
        //    }

        //    // check that each pivot table's data source exists (truncate pivot table's name and search)
        //    foreach (Excel.PivotTable pt in app.ActiveWorkbook.PivotTables)
        //    {
        //        string sourceSheetName = pt.Name.Substring(0, pt.Name.IndexOf("__"));
        //        try
        //        {
        //            Excel.Worksheet sourceSheet = app.ActiveWorkbook.Sheets[sourceSheetName];
        //        }
        //        catch (Exception)
        //        {
        //            errors[sourceSheetName] = string.Format("{0} is not a sheet in this workbook.  If left uncorrected, this pivot table will not update " +
        //                "when expected", sourceSheetName);
        //        }
        //    }

        //    // check that Properties worksheet exists
        //    try
        //    {
        //        Excel.Worksheet sheet = app.ActiveWorkbook.Sheets["Properties"];
        //    }
        //    catch (Exception)
        //    {
        //        errors["Properties Worksheet"] = "A worksheet named 'Properties' does not exist";
        //    }

        //    // check that each workbook property exists in Properties worksheet
        //    foreach (string property in wBookPropertiesConfig.properties.Keys)
        //    {
        //        try
        //        {
        //            Excel.Range range = app.ActiveWorkbook.Sheets["Properties"].Range[property];
        //        }
        //        catch (Exception)
        //        {
        //            errors[property] = string.Format("Named range, '{0}', does not exist in the Properties worksheet", property);
        //        }
        //    }

        //    // show message box with 1) missing structure and 2) related message
        //    if (errors.Count > 0)
        //    {
        //        string message = "The following errors should be fixed before the workbook is saved:\r\n";
        //        foreach (KeyValuePair<string, string> error in errors)
        //        {
        //            message += string.Format("***{0}:  {1}\r\n", error.Key, error.Value);
        //        }
        //        MessageBox.Show(message, "Report Structure Errors", MessageBoxButtons.OK);
        //    }
        //    else
        //    {
        //        MessageBox.Show("Report structure is correct!", "Report Structure Check", MessageBoxButtons.OK);
        //    }

        //}

        //public void addCellMapping()
        //{
        //    //not implemented yet
        //}

        //public void deleteCellMapping()
        //{
        //    //not implemented yet
        //}

        public Hashtable EditConfiguration(IList <string> dbConnStrings)
        {
            var          editConfig = new ConfigurationEditorView(dbConnStrings);
            DialogResult result     = editConfig.ShowDialog();

            if (result == DialogResult.OK)
            {
                // wipe appConfig object, which reloads the ConnectionString and SQL_getAvailableTables settings set above
                var defaultDatabaseType     = editConfig.DefaultConnectionDatabaseType;
                var availableTablesSQL      = AvailableTablesSql.availableTablesSql[defaultDatabaseType];
                var defaultConnectionString = editConfig.DefaultConnectionString;

                // Update class fields with new default connection string, availableTablesSQL, and database type (don't update _application field, though)
                // Updating the _presenter field here allows the new connection string and database type to be tested for validity.  If either is not valid
                // then the an exception will be throw, a message will be displayed to the user, and no settings (null) will be returned to the calling method,
                // so that no application settings are updated in the calling application.  Essentially it guards against malformed connection strings or mismatched
                // database types leaving the NovenaLibrary application and going to the calling appliation.
                try
                {
                    _appConfig = new AppConfig(defaultConnectionString, availableTablesSQL, defaultDatabaseType);
                    _workbookPropertiesConfig = new WorkbookPropertiesConfig(_application.ActiveWorkbook);

                    _presenter = new ExcelPresenter(_application,
                                                    new DatabaseConnectionFactory().CreateDbConnection(defaultDatabaseType, defaultConnectionString),
                                                    new SqlGeneratorFactory().CreateSqlGenerator(defaultDatabaseType),
                                                    _workbookPropertiesConfig);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
                    return(null);
                }

                // Pass connection strings to calling application method to update application's settings.
                Hashtable results = new Hashtable();
                results.Add("dbConnStrings", editConfig.DatabaseConnections);
                results.Add("activeConnectionString", defaultConnectionString);
                results.Add("activeDatabaseType", (int)defaultDatabaseType);
                results.Add("availableTablesSQL", availableTablesSQL);
                return(results);
            }

            return(null);
        }
Esempio n. 10
0
        public ColumnItemsView(AppConfig appConfig, WorkbookPropertiesConfig workbookPropertiesConfig, ISqlCreatorView parentForm)
        {
            InitializeComponent();
            _appConfig = appConfig;
            _workbookPropertiesConfig = workbookPropertiesConfig;
            _parentForm = parentForm;
            IColumnItemsPresenter presenter = new ColumnItemsPresenter(
                this,
                parentForm.SelectedCriteria.Column,
                parentForm.AvailableTablesText,
                new DatabaseConnectionFactory().CreateDbConnection(appConfig.DatabaseType, appConfig.ConnectionString),
                new SqlGeneratorFactory().CreateSqlGenerator(appConfig.DatabaseType));

            presenter.Initialize();

            // Makes first item in drop down box the selected item, so that blank choice disappears and "" will not be passed to presenter for limit.
            cbox_paging_limit.SelectedIndex = 0;
            //SelectedItems.AllowNew = true;
        }
        public void run_before_each_test_method()
        {
            User user = new User("username", "password", false);

            appConfig = new AppConfig("fake connection string", "sql", DatabaseType.PostgreSQL);
            //appConfig.User.Username = "******";
            //appConfig.Password = "******";
            //appConfig.ConnectionString = "fake connection string";
            //appConfig.DatabaseType = DatabaseType.PostgreSQL;

            workbookPropertiesConfig = new WorkbookPropertiesConfig();

            view           = MockRepository.GenerateMock <ISqlCreatorView>();
            view.AppConfig = appConfig;
            view.WorkbookPropertiesConfig = workbookPropertiesConfig;

            dbConnection = MockRepository.GenerateStub <IDatabaseConnection>();

            sqlGenerator = MockRepository.GenerateStub <BaseSqlGenerator>();

            presenter = new SqlCreatorPresenter(view, dbConnection, sqlGenerator);
        }
        public NovenaReportingAPI(Excel.Application application, string connectionString, string availableTablesSQL, DatabaseType databaseType, string workbookPropertiesConfigXML)
        {
            _appConfig   = new AppConfig(connectionString, availableTablesSQL, databaseType);
            _application = application;

            try
            {
                _workbookPropertiesConfig = new WorkbookPropertiesConfig(application.ActiveWorkbook).DeserializeXML(workbookPropertiesConfigXML);

                // If no credentials are required, create ExcelPresenter with DatabaseConnection and SqlGenerator dependencies.
                // If credentials are required, then they are set in the Login() method below.
                if (_appConfig.GetCredentialsRequired == AppConfig.CredentialsRequired.None)
                {
                    _presenter = new ExcelPresenter(application,
                                                    new DatabaseConnectionFactory().CreateDbConnection(_appConfig.DatabaseType, _appConfig.ConnectionString),
                                                    new SqlGeneratorFactory().CreateSqlGenerator(databaseType),
                                                    _workbookPropertiesConfig);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }