public void UpdateLINQModel()
{
if (Model.IsLINQ)
{
if (!string.IsNullOrEmpty(Model.LoadScript) && !_LINQwarningDone)
{
_LINQwarningDone = true;
MessageBox.Show("A custom 'Load Script' has been defined for this model. Adding or removing elements or restrictions may not work during the report execution...", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
//Check submodels and tables
Model.BuildSQL(false, true);
if (!string.IsNullOrEmpty(Model.ExecutionError))
{
MessageBox.Show(string.Format("An error occurs when building the LINQ Query:\r\n{0}", Model.ExecutionError), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MainForm.UpdateModelNode();
}
}
}
public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
{
Cursor.Current = Cursors.WaitCursor;
try
{
setContext(context);
if (_emailDevice != null)
{
if (context.PropertyDescriptor.Name == "HelperTestEmail")
{
_emailDevice.SendTestEmail();
}
}
else if (_metaConnection != null)
{
if (context.PropertyDescriptor.Name == "HelperCheckConnection")
{
_metaConnection.CheckConnection();
}
if (context.PropertyDescriptor.Name == "HelperCreateFromExcelAccess")
{
string accessDriver = "Microsoft Access Driver (*.mdb)";
string excelDriver = "Microsoft Excel Driver (*.xls)";
try
{
List <string> drivers = Helper.GetSystemDriverList();
string accessDriver2 = "Microsoft Access Driver (*.mdb, *.accdb)";
if (drivers.Contains(accessDriver2))
{
accessDriver = accessDriver2;
}
string excelDriver2 = "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)";
if (drivers.Contains(excelDriver2))
{
excelDriver = excelDriver2;
}
}
catch { }
OpenFileDialog dlg = new OpenFileDialog();
dlg.Title = "Open an Excel or an MS Access File";
dlg.CheckFileExists = true;
dlg.CheckPathExists = true;
dlg.InitialDirectory = _metaConnection.Source.Repository.RepositoryPath;
if (dlg.ShowDialog() == DialogResult.OK)
{
string ext = Path.GetExtension(dlg.FileName);
string driver = "";
if (ext == ".xls" || ext == ".xlsx" || ext == ".xlsm" || ext == ".xlsb")
{
_metaConnection.DatabaseType = DatabaseType.MSExcel;
driver = excelDriver;
}
else if (ext == ".mdb" || ext == ".accdb")
{
_metaConnection.DatabaseType = DatabaseType.MSAccess;
driver = accessDriver;
}
else
{
throw new Exception("Please select an Excel or MS Access file");
}
string path = dlg.FileName.Replace(_metaConnection.Source.Repository.RepositoryPath, Repository.SealRepositoryKeyword);
_metaConnection.ConnectionString = string.Format(@"Provider=MSDASQL.1;Extended Properties=""DBQ={0};Driver={{{1}}};""", path, driver);
setModified();
MessageBox.Show("The connection has been created successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
else if (_metaTable != null)
{
if (context.PropertyDescriptor.Name == "HelperRefreshColumns")
{
_metaTable.Refresh();
setModified();
initEntity(_metaTable);
}
if (context.PropertyDescriptor.Name == "HelperCheckTable")
{
_metaTable.CheckTable(null);
}
}
else if (_metaEnum != null)
{
if (context.PropertyDescriptor.Name == "HelperRefreshEnum")
{
_metaEnum.RefreshEnum();
setModified();
}
}
else if (_metaColumn != null)
{
if (context.PropertyDescriptor.Name == "HelperCheckColumn")
{
_metaColumn.MetaTable.CheckTable(_metaColumn);
}
else if (context.PropertyDescriptor.Name == "HelperCreateEnum")
{
MetaEnum result = _metaColumn.Source.CreateEnumFromColumn(_metaColumn);
_metaColumn.EnumGUID = result.GUID;
initEntity(result);
setModified();
}
else if (context.PropertyDescriptor.Name == "HelperShowValues")
{
try
{
Cursor.Current = Cursors.WaitCursor;
string result = _metaColumn.MetaTable.ShowValues(_metaColumn);
ExecutionForm frm = new ExecutionForm(null);
frm.Text = "Show values";
frm.cancelToolStripButton.Visible = false;
frm.pauseToolStripButton.Visible = false;
frm.logTextBox.Text = result;
frm.logTextBox.SelectionStart = 0;
frm.logTextBox.SelectionLength = 0;
frm.ShowDialog();
}
finally
{
Cursor.Current = Cursors.Default;
}
}
else if (context.PropertyDescriptor.Name == "HelperCreateDrillDates")
{
var year = _metaColumn.MetaTable.Source.AddColumn(_metaColumn.MetaTable);
year.DisplayName = _metaColumn.DisplayName + " Year";
year.Type = ColumnType.DateTime;
year.DateTimeStandardFormat = DateTimeStandardFormat.Custom;
year.Format = "yyyy";
var month = _metaColumn.MetaTable.Source.AddColumn(_metaColumn.MetaTable);
month.DisplayName = _metaColumn.DisplayName + " Month";
month.Type = ColumnType.DateTime;
month.DateTimeStandardFormat = DateTimeStandardFormat.Custom;
month.Format = "MM/yyyy";
if (_metaColumn.MetaTable.Source.Connection.DatabaseType == DatabaseType.Oracle)
{
year.Name = string.Format("trunc({0},'year')", _metaColumn.Name);
month.Name = string.Format("trunc({0},'month')", _metaColumn.Name);
}
else if (_metaColumn.MetaTable.Source.Connection.DatabaseType == DatabaseType.MSSQLServer)
{
year.Name = string.Format("DATETIME2FROMPARTS(year({0}),1,1,0,0,0,0,0)", _metaColumn.Name);
month.Name = string.Format("DATETIME2FROMPARTS(year({0}),month({0}),1,0,0,0,0,0)", _metaColumn.Name);
}
else if (_metaColumn.MetaTable.Source.Connection.DatabaseType == DatabaseType.MSAccess)
{
year.Name = string.Format("DateSerial(DatePart('yyyy',{0}), 1, 1)", _metaColumn.Name);
month.Name = string.Format("DateSerial(DatePart('yyyy',{0}), DatePart('m',{0}), 1)", _metaColumn.Name);
}
year.DrillChildren.Add(month.GUID);
month.DrillChildren.Add(_metaColumn.GUID);
initEntity(_metaColumn.MetaTable);
setModified();
MessageBox.Show("A 'Year' column and a 'Month' column have been added to the table with a drill hierarchy.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (context.PropertyDescriptor.Name == "HelperCreateSubReport")
{
Report report = Report.Create(Repository.Create());
//Only on detail view
report.Views.Clear();
report.AddView(ReportViewTemplate.ModelDetailName);
report.Views[0].InitParameters(true);
report.Views[0].Parameters.First(i => i.Name == "restriction_button").BoolValue = false;
report.Sources.RemoveAll(i => i.MetaSourceGUID != _metaColumn.Source.GUID);
if (report.Sources.Count == 0)
{
throw new Exception("Unable to create the detail report. Please save the Data Source first...");
}
//And one model
ReportModel model = report.Models[0];
model.SourceGUID = _metaColumn.Source.GUID;
//Add all the element of the table
foreach (var el in _metaColumn.MetaTable.Columns.OrderBy(i => i.DisplayOrder))
{
ReportElement element = ReportElement.Create();
element.MetaColumnGUID = el.GUID;
element.Name = el.Name;
element.PivotPosition = (el == _metaColumn ? PivotPosition.Page : PivotPosition.Row);
model.Elements.Add(element);
}
string entityName = _metaColumn.MetaTable.Name;
if (entityName.EndsWith("s"))
{
entityName = entityName.Substring(0, entityName.Length - 1);
}
string path = Path.Combine(_metaColumn.MetaTable.Source.Repository.SubReportsFolder, Helper.CleanFileName(entityName + " Detail.") + Repository.SealReportFileExtension);
path = FileHelper.GetUniqueFileName(path);
var sr = new SubReport()
{
Path = path.Replace(_metaColumn.Source.Repository.RepositoryPath, Repository.SealRepositoryKeyword), Name = entityName + " Detail"
};
//And the restriction, try to find out the table primary keys
try
{
DataTable schemaTables = ((OleDbConnection)_metaColumn.Source.GetOpenConnection()).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
Helper.DisplayDataTable(schemaTables);
foreach (DataRow row in schemaTables.Rows)
{
string schema = "";
if (schemaTables.Columns.Contains("TABLE_SCHEMA"))
{
schema = row["TABLE_SCHEMA"].ToString();
}
else if (schemaTables.Columns.Contains("TABLE_SCHEM"))
{
schema = row["TABLE_SCHEM"].ToString();
}
string fullName = (!string.IsNullOrEmpty(schema) ? _metaColumn.Source.GetTableName(schema) + "." : "") + _metaColumn.Source.GetTableName(row["TABLE_NAME"].ToString());
if (row["TABLE_NAME"].ToString() == _metaColumn.MetaTable.Name || fullName == _metaColumn.MetaTable.Name)
{
var col = _metaColumn.MetaTable.Columns.FirstOrDefault(i => i.Name.ToLower() == row["COLUMN_NAME"].ToString().ToLower() || i.Name.ToLower().EndsWith("." + row["COLUMN_NAME"].ToString().ToLower()));
if (col != null)
{
sr.Restrictions.Add(col.GUID);
}
else
{
//not all pk available....
sr.Restrictions.Clear();
break;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
string message = "";
if (sr.Restrictions.Count == 0)
{
//no PK found, we add the value itself...
sr.Restrictions.Add(_metaColumn.GUID);
message = "The Sub-Report restriction is based on the Column.";
}
else
{
message = "The Sub-Report restrictions are based on the table Primary Keys.";
}
foreach (var guid in sr.Restrictions)
{
ReportRestriction restriction = ReportRestriction.CreateReportRestriction();
restriction.MetaColumnGUID = guid;
restriction.PivotPosition = PivotPosition.Row;
restriction.Prompt = PromptType.Prompt;
restriction.Operator = Operator.Equal;
model.Restrictions.Add(restriction);
if (!string.IsNullOrEmpty(model.Restriction))
{
model.Restriction += "\r\nAND ";
}
model.Restriction += ReportRestriction.kStartRestrictionChar + restriction.GUID + ReportRestriction.kStopRestrictionChar;
}
model.InitReferences();
report.SaveToFile(path);
_metaColumn.SubReports.Add(sr);
if (MessageBox.Show(string.Format("A Sub-Report named '{0}' has been created in the dedicated Repository folder.\r\n{1}\r\nDo you want to edit it using a new Report Designer ?", Path.GetFileName(path), message), "Information", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
Process.Start(path);
}
;
_metaColumn.UpdateEditor();
setModified();
}
else if (context.PropertyDescriptor.Name == "HelperAddSubReport")
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.Filter = string.Format(Repository.SealRootProductName + " Reports files (*.{0})|*.{0}|All files (*.*)|*.*", Repository.SealReportFileExtension);
dlg.Title = "Select a Sub-Report having prompted restrictions";
dlg.CheckFileExists = true;
dlg.CheckPathExists = true;
dlg.InitialDirectory = _metaColumn.Source.Repository.SubReportsFolder;
if (dlg.ShowDialog() == DialogResult.OK)
{
Report report = Report.LoadFromFile(dlg.FileName, _metaColumn.Source.Repository, false);
var sr = new SubReport()
{
Path = report.FilePath.Replace(_metaColumn.Source.Repository.RepositoryPath, Repository.SealRepositoryKeyword), Name = Path.GetFileNameWithoutExtension(dlg.FileName)
};
bool tableOk = false;
var restrList = new List <ReportRestriction>();
foreach (var model in report.Models.Where(i => i.Source.MetaSourceGUID == _metaColumn.Source.GUID))
{
foreach (var restriction in model.Restrictions)
{
foreach (var table in _metaColumn.MetaTable.Source.MetaData.Tables)
{
var col = table.Columns.FirstOrDefault(i => i.GUID == restriction.MetaColumnGUID);
if (col != null)
{
tableOk = true;
if (!restrList.Exists(i => i.MetaColumnGUID == restriction.MetaColumnGUID))
{
restrList.Add(restriction);
}
}
}
}
}
if (!tableOk)
{
throw new Exception("Unable to add this Sub-Report:\r\nThe report does not contain any restriction...");
}
var frm = new MultipleSelectForm("Select the restrictions to include", restrList, "DisplayNameEl");
//select all by default
for (int i = 0; i < frm.checkedListBox.Items.Count; i++)
{
frm.checkedListBox.SetItemChecked(i, true);
}
if (frm.ShowDialog() == DialogResult.OK)
{
foreach (object item in frm.CheckedItems)
{
sr.Restrictions.Add(((ReportRestriction)item).MetaColumnGUID);
}
_metaColumn.SubReports.Add(sr);
MessageBox.Show(string.Format("The Sub-Report named '{0}' has been added with {1} restriction(s).", Path.GetFileName(dlg.FileName), sr.Restrictions.Count), "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
_metaColumn.UpdateEditor();
setModified();
}
}
}
else if (context.PropertyDescriptor.Name == "HelperOpenSubReportFolder")
{
Process.Start(_metaColumn.Source.Repository.SubReportsFolder);
}
}
else if (_metaJoin != null)
{
if (context.PropertyDescriptor.Name == "HelperCheckJoin")
{
_metaJoin.CheckJoin();
}
}
else if (_model != null)
{
if (context.PropertyDescriptor.Name == "HelperViewJoins")
{
try
{
_model.JoinPaths = new StringBuilder();
_model.BuildSQL();
var frm = new ExecutionForm(null);
frm.Text = "List of Joins";
frm.cancelToolStripButton.Visible = false;
frm.pauseToolStripButton.Visible = false;
frm.logTextBox.Text = _model.JoinPaths.ToString();
frm.logTextBox.SelectionStart = 0;
frm.logTextBox.SelectionLength = 0;
frm.ShowDialog();
}
finally
{
_model.JoinPaths = null;
}
}
}
else if (_reportView != null)
{
if (context.PropertyDescriptor.Name == "HelperReloadConfiguration")
{
_reportView.ReloadConfiguration();
}
else if (context.PropertyDescriptor.Name == "HelperResetParameters")
{
_reportView.InitParameters(true);
setModified();
}
else if (context.PropertyDescriptor.Name == "HelperResetPDFConfigurations")
{
_reportView.PdfConfigurations = new List <string>();
_reportView.PdfConverter = null;
_reportView.Information = Helper.FormatMessage("The PDF configuration values have been reset");
setModified();
}
else if (context.PropertyDescriptor.Name == "HelperResetExcelConfigurations")
{
_reportView.ExcelConfigurations = new List <string>();
_reportView.ExcelConverter = null;
_reportView.Information = Helper.FormatMessage("The Excel configuration values have been reset");
setModified();
}
}
else if (_configuration != null)
{
if (context.PropertyDescriptor.Name == "HelperResetPDFConfigurations")
{
_configuration.PdfConfigurations = new List <string>();
_configuration.PdfConverter = null;
}
else if (context.PropertyDescriptor.Name == "HelperResetExcelConfigurations")
{
_configuration.ExcelConfigurations = new List <string>();
_configuration.ExcelConverter = null;
}
}
else if (_reportSchedule != null)
{
if (HandlerInterface != null && context.PropertyDescriptor.Name == "HelperEditProperties")
{
HandlerInterface.EditSchedule(_reportSchedule);
}
else if (context.PropertyDescriptor.Name == "HelperRunTaskScheduler")
{
Process.Start(Path.Combine(Environment.SystemDirectory, "taskschd.msc"), "/s");
}
}
else if (_parameter != null)
{
if (context.PropertyDescriptor.Name == "HelperResetParameterValue")
{
_parameter.Value = _parameter.ConfigValue;
setModified();
}
}
else if (_security != null)
{
if (context.PropertyDescriptor.Name == "HelperSimulateLogin")
{
SecurityUser user = new SecurityUser(_security);
user.WebUserName = _security.TestUserName;
user.WebPassword = _security.TestPassword;
if (_security.TestCurrentWindowsUser)
{
user.WebPrincipal = new WindowsPrincipal(WindowsIdentity.GetCurrent());
}
user.Authenticate();
try
{
Cursor.Current = Cursors.WaitCursor;
ExecutionForm frm = new ExecutionForm(null);
frm.Text = "Test a login";
frm.cancelToolStripButton.Visible = false;
frm.pauseToolStripButton.Visible = false;
frm.logTextBox.Text = user.AuthenticationSummary;
frm.logTextBox.SelectionStart = 0;
frm.logTextBox.SelectionLength = 0;
frm.ShowDialog();
}
finally
{
Cursor.Current = Cursors.Default;
}
}
}
}
finally
{
Cursor.Current = Cursors.Default;
}
return(value);
}
public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
{
var svc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
if (svc != null)
{
var frm = new SQLEditorForm();
frm.Instance = context.Instance;
frm.PropertyName = context.PropertyDescriptor.Name;
string template = "";
string valueToEdit = (value == null ? "" : value.ToString());
if (context.Instance is ReportModel)
{
ReportModel model = context.Instance as ReportModel;
model.BuildSQL();
if (!string.IsNullOrEmpty(model.ExecutionError))
{
throw new Exception("Error building the SQL Statement...\r\nPlease fix these errors first.\r\n" + model.ExecutionError);
}
if (context.PropertyDescriptor.Name == "SqlEditor")
{
frm.clearToolStripButton.Visible = false;
frm.sqlTextBox.Text = model.Sql;
frm.sqlTextBox.IsReadOnly = true;
}
else if (context.PropertyDescriptor.Name == "PreSQL" || context.PropertyDescriptor.Name == "PostSQL")
{
template = razorModelTemplate;
frm.clearToolStripButton.Visible = false;
}
else
{
frm.checkSQLToolStripButton.Visible = false;
if (value == null)
{
value = "";
}
if (context.PropertyDescriptor.Name == "SqlSelect" && string.IsNullOrEmpty(value.ToString()))
{
valueToEdit = model.execSelect;
}
if (context.PropertyDescriptor.Name == "SqlFrom" && string.IsNullOrEmpty(value.ToString()))
{
valueToEdit = "FROM " + model.execFromClause.ToString();
}
if (context.PropertyDescriptor.Name == "SqlGroupBy" && string.IsNullOrEmpty(value.ToString()))
{
valueToEdit = "GROUP BY " + model.execGroupByClause.ToString();
}
if (context.PropertyDescriptor.Name == "SqlOrderBy" && string.IsNullOrEmpty(value.ToString()))
{
valueToEdit = "ORDER BY " + model.execOrderByClause.ToString();
}
}
}
else if (context.Instance is MetaJoin)
{
frm.clearToolStripButton.Visible = false;
}
else if (context.Instance is MetaEnum)
{
template = razorEnumTemplate;
frm.clearToolStripButton.Visible = false;
MetaEnum anEnum = context.Instance as MetaEnum;
if (value == null || string.IsNullOrEmpty(value.ToString()))
{
value = "";
valueToEdit = anEnum.DefaultSQL;
}
}
else if (context.Instance is ReportSource || context.Instance is MetaSource)
{
template = razorSourceTemplate;
frm.clearToolStripButton.Visible = false;
}
else if (context.Instance is MetaTable)
{
if (context.PropertyDescriptor.Name == "PreSQL" || context.PropertyDescriptor.Name == "PostSQL")
{
template = razorTableTemplate;
}
else if (context.PropertyDescriptor.Name == "WhereSQL")
{
template = razorTableWhereTemplate;
}
frm.clearToolStripButton.Visible = false;
}
else if (context.Instance is ReportTask)
{
template = razorTaskTemplate;
frm.clearToolStripButton.Visible = false;
}
if (value != null)
{
frm.sqlTextBox.Text = valueToEdit.ToString();
}
if (context.PropertyDescriptor.IsReadOnly)
{
frm.SetReadOnly();
}
else if (!string.IsNullOrEmpty(template))
{
frm.SetSamples(new List <string>()
{
template
});
frm.errorTextBox.Text = "Note that Razor script can be used if the text starts with '@'.\r\n";
}
if (svc.ShowDialog(frm) == DialogResult.OK)
{
value = frm.sqlTextBox.Text;
}
}
return(value);
}
void helperButton_Click(object sender, EventArgs e)
{
try
{
Cursor.Current = Cursors.WaitCursor;
if (sender is ToolStripItem)
{
Keys key = (Keys)((ToolStripItem)sender).Tag;
if (SelectedEntity is MetaSource)
{
if (key == Keys.F7)
{
MetaSource source = ((MetaSource)SelectedEntity);
source.Connection.CheckConnection();
source.Information = source.Connection.Information;
source.Error = source.Connection.Error;
source.InitEditor();
}
if (key == Keys.F12)
{
MetaTable table = ((MetaSource)SelectedEntity).MetaData.MasterTable;
if (table != null)
{
TreeViewHelper.SelectNode(MainTreeView, MainTreeView.SelectedNode.Nodes, table);
EditProperty("Default Load Script");
}
}
}
else if (SelectedEntity is MetaConnection)
{
if (key == Keys.F7)
{
((MetaConnection)SelectedEntity).CheckConnection();
}
}
else if (SelectedEntity is MetaTable)
{
if (key == Keys.F7)
{
((MetaTable)SelectedEntity).CheckTable(null);
}
if (key == Keys.F8)
{
if (((MetaTable)SelectedEntity).IsSQL)
{
EditProperty("SQL Select Statement");
}
else
{
EditProperty("Definition Script");
}
}
if (key == Keys.F9 && ((MetaTable)SelectedEntity).DynamicColumns)
{
((MetaTable)SelectedEntity).Refresh();
if (EntityHandler != null)
{
EntityHandler.SetModified();
EntityHandler.InitEntity(SelectedEntity);
}
}
if (key == Keys.F12)
{
EditProperty("Default Load Script");
}
}
else if (SelectedEntity is MetaColumn)
{
if (key == Keys.F7)
{
EditProperty("Check column SQL syntax");
}
if (key == Keys.F8)
{
EditProperty("Show column values");
}
}
else if (SelectedEntity is MetaEnum)
{
if (key == Keys.F8)
{
EditProperty("SQL Select Statement");
}
if (key == Keys.F9)
{
if (EntityHandler != null)
{
EntityHandler.SetModified();
}
((MetaEnum)SelectedEntity).RefreshEnum();
}
}
else if (SelectedEntity is MetaJoin)
{
if (key == Keys.F7)
{
((MetaJoin)SelectedEntity).CheckJoin();
}
if (key == Keys.F8)
{
EditProperty("SQL Clause");
}
}
else if (SelectedEntity is TasksFolder)
{
if (key == Keys.F7)
{
EditProperty("Common Scripts");
}
}
else if (SelectedEntity is ReportTask)
{
if (key == Keys.F7)
{
EditProperty("Script");
}
if (key == Keys.F8)
{
EditProperty("SQL Statement");
}
}
else if (SelectedEntity is ReportModel)
{
if (key == Keys.F7 || key == Keys.F8)
{
var frm = new SQLEditorForm();
frm.Instance = SelectedEntity;
frm.PropertyName = "";
ReportModel model = SelectedEntity as ReportModel;
if (model.IsSQLModel && key == Keys.F7)
{
frm.Text = "SQL Editor: Edit the SQL Select Statement";
frm.SetSamples(new List <string>()
{
"SELECT * FROM Orders", "SELECT *\r\nFROM Employees\r\nWHERE {CommonRestriction_LastName}",
"SELECT * FROM Orders", "SELECT *\r\nFROM Employees\r\nWHERE EmployeeID > {CommonValue_ID}"
});
frm.WarningOnError = true;
frm.sqlTextBox.Text = model.Table.Sql;
if (frm.ShowDialog() == DialogResult.OK)
{
try
{
Cursor.Current = Cursors.WaitCursor;
model.Table.Sql = frm.sqlTextBox.Text;
model.RefreshMetaTable(true);
}
finally
{
Cursor.Current = Cursors.Default;
}
if (EntityHandler != null)
{
EntityHandler.SetModified();
EntityHandler.RefreshModelTreeView();
}
if (!string.IsNullOrEmpty(model.Table.Error))
{
throw new Exception("Error when building columns from the SQL Select Statement:\r\n" + model.Table.Error);
}
}
}
else
{
model.Report.CheckingExecution = true;
try
{
model.BuildSQL();
frm.SqlToCheck = model.Sql;
model.Report.CheckingExecution = false;
model.BuildSQL();
}
finally
{
model.Report.CheckingExecution = false;
}
if (!string.IsNullOrEmpty(model.ExecutionError))
{
throw new Exception("Error building the SQL Statement...\r\nPlease fix these errors first.\r\n" + model.ExecutionError);
}
frm.sqlTextBox.Text = model.Sql;
frm.SetReadOnly();
if (key == Keys.F8)
{
frm.checkSQL();
}
frm.ShowDialog();
}
}
}
else if (SelectedEntity is ReportView)
{
if (key == Keys.F8)
{
EditProperty("Custom template");
}
}
else if (SelectedEntity is ViewFolder)
{
if (key == Keys.F8)
{
EditProperty("Report Input Values");
}
}
else if (SelectedEntity is ReportSchedule)
{
if (key == Keys.F8)
{
EditProperty("Edit schedule properties");
}
if (key == Keys.F9)
{
EditProperty("Run Task Scheduler MMC");
}
}
}
}
finally
{
Cursor.Current = Cursors.Default;
}
}
public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
{
var svc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
if (svc != null)
{
var frm = new SQLEditorForm();
frm.Instance = context.Instance;
frm.PropertyName = context.PropertyDescriptor.Name;
string valueToEdit = (value == null ? "" : value.ToString());
bool forceValueToEdit = false;
List <string> samples = new List <string>();
var description = "";
if (context.Instance is ReportModel)
{
ReportModel model = context.Instance as ReportModel;
model.BuildSQL();
if (!string.IsNullOrEmpty(model.ExecutionError))
{
throw new Exception("Error building the SQL Statement...\r\nPlease fix these errors first.\r\n" + model.ExecutionError);
}
if (context.PropertyDescriptor.Name == "PreSQL" || context.PropertyDescriptor.Name == "PostSQL")
{
samples.Add(razorModelTemplate);
samples.Add("update Employees set LastName=LastName");
samples.Add("update Employees set LastName=LastName where {CommonRestriction_LastName}");
samples.Add("update Employees set LastName={CommonValue_NewName} where {CommonRestriction_OldName}");
frm.clearToolStripButton.Visible = false;
description = descriptionTemplate2;
}
else
{
frm.checkSQLToolStripButton.Visible = false;
forceValueToEdit = true;
var value2 = value;
if (value2 == null)
{
value2 = "";
}
if (context.PropertyDescriptor.Name == "SqlSelect" && string.IsNullOrEmpty(value2.ToString()))
{
valueToEdit = model.execSelect;
}
if (context.PropertyDescriptor.Name == "SqlFrom" && string.IsNullOrEmpty(value2.ToString()))
{
valueToEdit = "FROM " + model.execFromClause.ToString();
}
if (context.PropertyDescriptor.Name == "SqlGroupBy" && string.IsNullOrEmpty(value2.ToString()))
{
valueToEdit = "GROUP BY " + model.execGroupByClause.ToString();
}
if (context.PropertyDescriptor.Name == "SqlOrderBy" && string.IsNullOrEmpty(value2.ToString()))
{
valueToEdit = "ORDER BY " + model.execOrderByClause.ToString();
}
if (context.PropertyDescriptor.Name == "SqlCTE" && string.IsNullOrEmpty(value2.ToString()))
{
valueToEdit = model.execCTEClause.ToString();
}
}
}
else if (context.Instance is MetaJoin)
{
frm.clearToolStripButton.Visible = false;
MetaJoin join = context.Instance as MetaJoin;
if (join.LeftTable != null && join.RightTable != null && join.LeftTable.Columns.Count > 0 && join.RightTable.Columns.Count > 0)
{
for (int i = 0; i < join.LeftTable.Columns.Count && i < join.RightTable.Columns.Count; i++)
{
samples.Add(string.Format("{0} = {1}", join.LeftTable.Columns[i].Name, join.RightTable.Columns[i].Name));
}
}
if ((value == null || string.IsNullOrEmpty(value.ToString())) && join.LeftTable != null && join.RightTable != null)
{
forceValueToEdit = true;
valueToEdit = string.Format("{0}.<ColumnName> = {1}.<ColumnName>", join.LeftTable.AliasName, join.RightTable.AliasName);
}
}
else if (context.Instance is MetaEnum)
{
if (context.PropertyDescriptor.Name == "SqlDisplay")
{
samples.Add("SELECT DISTINCT CategoryID, CategoryName\r\nFROM Categories\r\nWHERE CategoryName LIKE '%{EnumFilter}%'\r\nORDER BY 2");
samples.Add("SELECT DISTINCT City\r\nFROM Customers\r\nWHERE Country in ({EnumValues_Country})\r\nORDER BY 1");
description = descriptionTemplate1 + "The SQL may contain the filter tag by using the keyword '{EnumFilter}' to build the enum with filters got from the user.\r\nThe SQL may contain dependencies with other enum values got from the user by using the keyword {EnumValues_<Name>} where <Name> is the name of the other enumerated list.\r\n";
}
else
{
samples.Add(razorEnumTemplate);
samples.Add("SELECT DISTINCT CategoryID, CategoryName\r\nFROM Categories\r\nORDER BY 2");
samples.Add("SELECT DISTINCT CategoryID, CategoryName, CategoryName, 'font-style:italic', 'info'\r\nFROM Categories\r\nORDER BY 2");
description = descriptionTemplate1;
}
frm.clearToolStripButton.Visible = false;
MetaEnum anEnum = context.Instance as MetaEnum;
if (value == null || string.IsNullOrEmpty(value.ToString()))
{
forceValueToEdit = true;
valueToEdit = "SELECT col1,col2 FROM table ORDER BY col2";
}
}
else if (context.Instance is ReportSource || context.Instance is MetaSource)
{
samples.Add(razorSourceTemplate);
frm.clearToolStripButton.Visible = false;
description = descriptionTemplate1;
}
else if (context.Instance is MetaTable)
{
if (context.PropertyDescriptor.Name == "PreSQL" || context.PropertyDescriptor.Name == "PostSQL")
{
samples.Add(razorTableTemplate);
samples.Add("UPDATE Employees SET LastName=LastName");
samples.Add("UPDATE Employees SET LastName=LastName WHERE {CommonRestriction_LastName}");
description = descriptionTemplate2;
}
else if (context.PropertyDescriptor.Name == "WhereSQL")
{
samples.Add(razorTableWhereTemplate);
samples.Add("{CommonRestriction_LastName}");
samples.Add("Orders.EmployeeID in (SELECT EmployeeID FROM Employees WHERE LastName={CommonValue_LastNameValue})");
description = descriptionTemplate2;
}
else if (context.PropertyDescriptor.Name == "Sql")
{
samples.Add("SELECT * FROM Employees WHERE {CommonRestriction_LastName}");
samples.Add("SELECT * FROM Employees WHERE EmployeeID > {CommonValue_ID}");
description = descriptionTemplate3;
}
frm.clearToolStripButton.Visible = false;
}
else if (context.Instance is ReportTask)
{
samples.Add(razorTaskTemplate);
samples.Add("UPDATE Employees SET LastName=LastName");
description = descriptionTemplate1;
frm.clearToolStripButton.Visible = false;
}
if (value != null || forceValueToEdit)
{
frm.sqlTextBox.Text = valueToEdit.ToString();
}
if (context.PropertyDescriptor.IsReadOnly)
{
frm.SetReadOnly();
}
else
{
frm.SetSamples(samples);
if (!string.IsNullOrEmpty(description))
{
frm.errorTextBox.Text = description;
}
}
if (svc.ShowDialog(frm) == DialogResult.OK)
{
value = frm.sqlTextBox.Text;
}
}
return(value);
}
void helperButton_Click(object sender, EventArgs e)
{
try
{
Cursor.Current = Cursors.WaitCursor;
if (sender is ToolStripItem)
{
Keys key = (Keys)((ToolStripItem)sender).Tag;
if (SelectedEntity is MetaSource)
{
if (key == Keys.F7)
{
MetaSource source = ((MetaSource)SelectedEntity);
source.Connection.CheckConnection();
source.Information = source.Connection.Information;
source.Error = source.Connection.Error;
source.InitEditor();
}
if (key == Keys.F8 || key == Keys.F10)
{
MetaTable table = ((MetaSource)SelectedEntity).MetaData.MasterTable;
if (table != null)
{
TreeViewHelper.SelectNode(MainTreeView, MainTreeView.SelectedNode.Nodes, table);
EditProperty(table.IsSQL ? "SQL Statement" : "Default Load Script");
}
}
}
else if (SelectedEntity is MetaConnection)
{
if (key == Keys.F7)
{
((MetaConnection)SelectedEntity).CheckConnection();
}
}
else if (SelectedEntity is MetaTable)
{
if (key == Keys.F7)
{
((MetaTable)SelectedEntity).CheckTable(null);
}
if (key == Keys.F8)
{
if (((MetaTable)SelectedEntity).IsSQL)
{
EditProperty("SQL Statement");
}
else
{
EditProperty("Definition Script");
}
}
if (key == Keys.F9 && ((MetaTable)SelectedEntity).DynamicColumns)
{
((MetaTable)SelectedEntity).Refresh();
if (EntityHandler != null)
{
EntityHandler.SetModified();
EntityHandler.InitEntity(SelectedEntity);
}
}
if (key == Keys.F10)
{
EditProperty("Default Load Script");
}
}
else if (SelectedEntity is MetaColumn)
{
if (key == Keys.F7)
{
EditProperty("Check column SQL syntax");
}
if (key == Keys.F8)
{
EditProperty("Show column values");
}
}
else if (SelectedEntity is MetaEnum)
{
if (key == Keys.F8)
{
EditProperty("Select SQL Statement");
}
if (key == Keys.F9)
{
((MetaEnum)SelectedEntity).RefreshEnum();
}
}
else if (SelectedEntity is MetaJoin)
{
if (key == Keys.F7)
{
((MetaJoin)SelectedEntity).CheckJoin();
}
if (key == Keys.F8)
{
EditProperty("SQL Clause");
}
}
else if (SelectedEntity is ReportTask)
{
if (key == Keys.F7)
{
EditProperty("Script");
}
if (key == Keys.F8)
{
EditProperty("SQL Statement");
}
}
else if (SelectedEntity is ReportModel)
{
if (key == Keys.F7 || key == Keys.F8)
{
var frm = new SQLEditorForm();
frm.Instance = SelectedEntity;
frm.PropertyName = "";
ReportModel model = SelectedEntity as ReportModel;
model.Report.CheckingExecution = true;
try
{
model.BuildSQL();
}
finally
{
model.Report.CheckingExecution = false;
}
if (!string.IsNullOrEmpty(model.ExecutionError))
{
throw new Exception("Error building the SQL Statement...\r\nPlease fix these errors first.\r\n" + model.ExecutionError);
}
frm.sqlTextBox.Text = model.Sql;
frm.SetReadOnly();
if (key == Keys.F8)
{
frm.checkSQLToolStripButton_Click(null, null);
}
frm.ShowDialog();
}
}
else if (SelectedEntity is ReportView)
{
if (key == Keys.F7)
{
EditProperty("General Parameters");
}
else if (key == Keys.F8)
{
EditProperty("CSS");
}
else if (key == Keys.F9)
{
EditProperty("Data Table Configuration");
}
else if (key == Keys.F10)
{
EditProperty("NVD3 Chart Configuration");
}
}
else if (SelectedEntity is ReportSchedule)
{
if (key == Keys.F8)
{
EditProperty("Edit schedule properties");
}
if (key == Keys.F9)
{
EditProperty("Run Task Scheduler MMC");
}
}
}
}
finally
{
Cursor.Current = Cursors.Default;
}
}