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);
}
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();
}
}
public ExcelPresenter(MSExcel.Application app, IDatabaseConnection dbConnection, BaseSqlGenerator sqlGenerator, WorkbookPropertiesConfig workbookPropertiesConfig)
{
_app = app;
_view = app.ActiveWorkbook;
_workbookPropertiesConfig = workbookPropertiesConfig;
_dbConnection = dbConnection;
_sqlGenerator = sqlGenerator;
}
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);
}
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;
}
}