/// <summary>
/// Generate the SQL command selecting the set of fields from the given field groups
/// from the underlying table.
/// </summary>
/// <param name="selectedFieldGroups">The list of field groups to select fields from.</param>
/// <returns>The string containing the SQL command.</returns>
private StringBuilder GenerateFromTableFieldGroups(IEnumerable <TablesAutomation.IFieldGroup> selectedFieldGroups)
{
var result = new StringBuilder();
if (!selectedFieldGroups.Any())
{
return(result);
}
// We are implicitly assuming that all the field groups belong to the same table.
TablesAutomation.ITable selectedTable = selectedFieldGroups.FirstOrDefault().Table;
// Used to capture the set of fields containing the union of the fields
// from the selected field groups.
var fields = new HashSet <string>();
// Unwrap the fields in all of the selected field groups, using the set
// to weed out duplicate fields (that occur in multiple field groups)
foreach (var fieldGroup in selectedFieldGroups)
{
var refs = fieldGroup.FieldGroupFieldReferences;
foreach (IFieldGroupFieldReference reference in refs)
{
fields.Add(reference.DataField);
}
}
// Now the (unique) set of fields is captured.
// Generate the SQL query from the IBaseField references.
IList <TablesAutomation.IBaseField> fieldsToSelect = new List <TablesAutomation.IBaseField>();
foreach (TablesAutomation.IBaseField child in selectedTable.BaseFields)
{
if (fields.Contains(child.Name))
{
fieldsToSelect.Add(child);
}
}
result = this.GenerateFromTableFieldList(fieldsToSelect);
// If this table saves data per company, then add the where clause for
// the user to fill out.
if (selectedTable.SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
{
// Note: There is no way to get to the actual company, since the server
// is not involved in this at all.
result.AppendLine("-- where " + SqlNameMangling.GetValidSqlNameForField("DataAreaId") + " = 'DAT'");
}
return(result);
}
/// <summary>
/// Append the field in the given tabular object to the output, one field per line.
/// </summary>
/// <param name="result">The string containing the result</param>
/// <param name="tableName">The unmangled name of the tabular object</param>
/// <param name="fieldName">The unmangled name of the field</param>
/// <param name="fieldLabel">An optional label to use as the prompt. If this is null, the name is used.</param>
/// <param name="first">Determines whether the first field has been written or not.</param>
private void AddField(StringBuilder result, string tableName, string fieldName, string label, ref bool first)
{
// Fields are indented to be positioned under the select statement
result.Append(" ");
if (!first)
{
result.Append(",");
}
result.Append(SqlNameMangling.GetSqlTableName(tableName) + ".[" + SqlNameMangling.GetValidSqlNameForField(fieldName) + "]");
result.AppendLine(string.Format(CultureInfo.InvariantCulture, " as '{0}'", label ?? fieldName));
first = false;
}
/// <summary>
/// The method is called when the user has selected to view a single table.
/// The method generates a select on all fields, with no joins.
/// </summary>
/// <param name="selectedTable">The designer metadata designating the table.</param>
/// <returns>The string containing the SQL command.</returns>
private StringBuilder GenerateFromTable(TablesAutomation.ITable selectedTable)
{
var result = new StringBuilder();
// It is indeed a table. Look at the properties
if (!selectedTable.IsKernelTable)
{
bool first = true;
result.AppendLine(string.Format(CultureInfo.InvariantCulture, "use {0}", BusinessDatabaseName));
result.AppendLine("go");
Stack <AxTable> tables = this.SuperTables(selectedTable.Name);
// List any developer documentation as a SQL comment:
if (!string.IsNullOrEmpty(selectedTable.DeveloperDocumentation))
{
result.Append("-- " + selectedTable.Name);
result.AppendLine(" : " + this.ResolveLabel(selectedTable.DeveloperDocumentation));
}
else
{
result.AppendLine();
}
result.AppendLine("select ");
this.AddFields(result, tables.First(), tables.First().Fields, ref first);
this.AddSystemFields(result, tables.First(), ref first);
result.AppendLine("from " + SqlNameMangling.GetSqlTableName(tables.First().Name));
// If this table saves data per company, then add the where clause for
// the user to fill out or ignore.
if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
{
result.AppendLine("-- where " + SqlNameMangling.GetValidSqlNameForField("DataAreaId") + " = 'DAT'");
}
}
return(result);
}
/// <summary>
/// Generate the SQL command selecting the given fields from the underlying table.
/// </summary>
/// <param name="fields">The list of fields to select</param>
/// <returns>The string containing the SQL command.</returns>
private StringBuilder GenerateFromTableFieldListWithWhere(IEnumerable <TablesAutomation.IBaseField> fields)
{
var result = this.GenerateFromTableFieldList(fields);
if (fields.Any())
{
var tables = this.SuperTables(fields.First().Table.Name);
// If this table saves data per company, then add the where clause for
// the user to fill out.
if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
{
// Note: There is no way to get to the actual company, since the server
// is not involved in this at all.
result.AppendLine("-- where " + SqlNameMangling.GetValidSqlNameForField("DataAreaId") + " = 'DAT'");
}
}
return(result);
}
/// <summary>
/// The method is called when the user has selected to view a table extension instance.
/// The method generates a select on all fields, with no joins.
/// </summary>
/// <param name="selectedExtensionTable">The designer metadata designating the view.</param>
/// <returns>The string containing the SQL command.</returns>
private StringBuilder GenerateFromTableExtension(TablesAutomation.ITableExtension selectedExtensionTable)
{
var result = new StringBuilder();
bool first = true;
result.AppendLine(string.Format(CultureInfo.InvariantCulture, "use {0}", BusinessDatabaseName));
result.AppendLine("go");
result.AppendLine();
AxTableExtension extension = this.MetadataProvider.TableExtensions.Read(selectedExtensionTable.Name);
var baseTableName = selectedExtensionTable.Name.Split('.').First();
var tables = this.SuperTables(baseTableName);
HashSet <string> extendedFields = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
foreach (var extendedField in extension.Fields)
{
extendedFields.Add(extendedField.Name);
}
result.AppendLine("select ");
// List the extension fields first...
this.AddFields(result, tables.First(), extension.Fields, ref first);
// Then the normal ones...
this.AddFields(result, tables.First(), tables.First().Fields.Where(f => !extendedFields.Contains(f.Name)), ref first);
// And then system fields
this.AddSystemFields(result, tables.First(), ref first);
result.AppendLine("from " + SqlNameMangling.GetSqlTableName(tables.First().Name));
if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
{
result.AppendLine("-- where " + SqlNameMangling.GetValidSqlNameForField("DataAreaId") + " = 'DAT'");
}
return(result);
}
/// <summary>
/// The method is called when the user has selected to view a single view.
/// The method generates a select on all fields, with no joins.
/// </summary>
/// <param name="selectedTable">The designer metadata designating the view.</param>
/// <returns>The string containing the SQL command.</returns>
private StringBuilder GenerateFromView(ViewsAutomation.IView selectedView)
{
var result = new StringBuilder();
result.AppendLine(string.Format(CultureInfo.InvariantCulture, "use {0}", BusinessDatabaseName));
result.AppendLine("go");
if (!string.IsNullOrEmpty(selectedView.DeveloperDocumentation))
{
result.Append("-- " + selectedView.Name);
result.AppendLine(" : " + this.ResolveLabel(selectedView.DeveloperDocumentation));
}
else
{
result.AppendLine();
}
result.AppendLine("select * ");
result.AppendLine("from " + SqlNameMangling.GetSqlTableName(selectedView.Name));
return(result);
}
/// <summary>
/// Generate the SQL command selecting the fields from the selected table, performing
/// joins on the tables indicated by the relations selected.
/// </summary>
/// <param name="selectedRelations">The list of field groups to select fields from.</param>
/// <returns>The string containing the SQL command.</returns>
private StringBuilder GenerateFromTableRelations(IEnumerable <TablesAutomation.IRelation> selectedRelations)
{
TablesAutomation.ITable table = selectedRelations.First().Table;
Stack <AxTable> tables = this.SuperTables(table.Name);
IList <TablesAutomation.IBaseField> fields = new List <TablesAutomation.IBaseField>();
foreach (TablesAutomation.IBaseField field in table.BaseFields)
{
if (field.SaveContents == Metadata.Core.MetaModel.NoYes.Yes)
{
fields.Add(field);
}
}
var result = this.GenerateFromTableFieldList(fields);
int disambiguation = 1;
// Now add the joins. Assume that multiple relations can be
// selected; joins will be added for all of them.
foreach (var relation in selectedRelations)
{
var relatedTabularObjectName = relation.RelatedTable;
// TODO: In principle this could be a view. Assuming table for now
result.AppendLine(
string.Format(CultureInfo.InvariantCulture, "inner join {0} as t{1}", SqlNameMangling.GetSqlTableName(relatedTabularObjectName), disambiguation));
result.Append("on ");
bool first = true;
foreach (TablesAutomation.IRelationConstraint constraint in relation.RelationConstraints)
{
if (!first)
{
result.Append(" and ");
}
if (constraint is TablesAutomation.IRelationConstraintField)
{ // Table.field = RelatedTable.relatedField
var fieldConstraint = constraint as TablesAutomation.IRelationConstraintField;
result.Append(SqlNameMangling.GetSqlTableName(table.Name) + ".[" + SqlNameMangling.GetValidSqlNameForField(fieldConstraint.Field) + "]");
result.Append(" = ");
result.AppendLine(string.Format(CultureInfo.InvariantCulture, "t{0}", disambiguation) + "." + SqlNameMangling.GetValidSqlNameForField(fieldConstraint.RelatedField));
}
else if (constraint is TablesAutomation.IRelationConstraintFixed)
{ // Table.field = value
var fixedConstraint = constraint as TablesAutomation.IRelationConstraintFixed;
result.Append(SqlNameMangling.GetSqlTableName(table.Name) + ".[" + SqlNameMangling.GetValidSqlNameForField(fixedConstraint.Field) + "]");
result.Append(" = ");
result.AppendLine(fixedConstraint.Value.ToString(CultureInfo.InvariantCulture));
}
else if (constraint is TablesAutomation.IRelationConstraintRelatedFixed)
{ // Value = RelatedTable.field
var relatedFixedConstraint = constraint as TablesAutomation.IRelationConstraintRelatedFixed;
result.Append(relatedFixedConstraint.Value);
result.Append(" = ");
result.AppendLine(string.Format(CultureInfo.InvariantCulture, "t{0}", disambiguation) + ".[" + SqlNameMangling.GetValidSqlNameForField(relatedFixedConstraint.RelatedField) + "]");
}
first = false;
}
disambiguation += 1;
}
// If this table saves data per company, then add the where clause for
// the user to fill out.
if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
{
// Note: There is no way to get to the actual company, since the server
// is not involved in this at all.
result.AppendLine("-- where " + SqlNameMangling.GetValidSqlNameForField("DataAreaId") + " = 'DAT'");
}
return(result);
}
/// <summary>
/// Generate the SQL command selecting the given fields from the underlying table.
/// </summary>
/// <param name="fields">The list of fields to select</param>
/// <returns>The string containing the SQL command.</returns>
private StringBuilder GenerateFromViewFieldList(IEnumerable <ViewsAutomation.IViewBaseField> fields)
{
var result = new StringBuilder();
result.AppendLine(string.Format(CultureInfo.InvariantCulture, "use {0}", BusinessDatabaseName));
result.AppendLine("go");
if (!fields.Any())
{
return(result);
}
ViewsAutomation.IView selectedView1 = fields.FirstOrDefault().View;
AxView view = this.MetadataProvider.Views.Read(selectedView1.Name);
// Expand the developer documentation, if any
if (!string.IsNullOrEmpty(view.DeveloperDocumentation))
{
result.Append("-- " + view.Name);
result.AppendLine(" : " + this.ResolveLabel(view.DeveloperDocumentation));
}
else
{
result.AppendLine();
}
result.AppendLine("select");
bool first = true;
foreach (ViewsAutomation.IViewField field in fields.OfType <ViewsAutomation.IViewField>())
{
this.AddField(result, view.Name, field.Name, null, ref first);
// The field name refers to a name on the datasource. Find the datasource
// and the underlying table.
AxTable table = this.FindTableInDataSource(view, field.DataSource);
table = this.SuperTables(table.Name).First();
if (table != null)
{
AxTableField tableField = table.Fields[field.DataField];
if (tableField != null)
{
var edt = tableField.ExtendedDataType;
if (!string.IsNullOrWhiteSpace(edt))
{
// See if it happens to be an array field. If so, the first index
// does not have a suffix ([<n>]), and has already been written.
if (this.MetadataProvider.Edts.Exists(edt))
{
AxEdt typeDefinition = this.metadataProvider.Edts.Read(edt);
for (int i = 2; i <= typeDefinition.ArrayElements.Count + 1; i++)
{
var fn = field.Name + "[" + i.ToString(CultureInfo.InvariantCulture) + "]";
this.AddField(result, view.Name, fn, null, ref first);
}
}
}
}
}
}
// Now deal with computed columns.
foreach (ViewsAutomation.IViewComputedColumn computedColumn in fields.OfType <ViewsAutomation.IViewComputedColumn>())
{
this.AddField(result, view.Name, computedColumn.Name, null, ref first);
}
result.AppendLine("from " + SqlNameMangling.GetSqlTableName(view.Name));
return(result);
}
/// <summary>
/// Generate the SQL command selecting the given fields from the underlying table.
/// </summary>
/// <param name="fields">The list of fields to select</param>
/// <returns>The string containing the SQL command.</returns>
private StringBuilder GenerateFromTableFieldList(IEnumerable <TablesAutomation.IBaseField> fields)
{
var result = new StringBuilder();
bool first = true;
result.AppendLine(string.Format(CultureInfo.InvariantCulture, "use {0}", BusinessDatabaseName));
result.AppendLine("go");
if (!fields.Any())
{
return(result);
}
var tableName = string.Empty;
TablesAutomation.ITable selectedTable = fields.First().Table;
if (selectedTable != null)
{
tableName = selectedTable.Name;
}
else
{
var extensionTableName = fields.First().TableExtension.Name;
tableName = extensionTableName.Split('.').First();
}
Stack <AxTable> tables = this.SuperTables(tableName);
// Expand the developer documentation, if any
if (!string.IsNullOrEmpty(tables.First().DeveloperDocumentation))
{
result.Append("-- " + tables.First().Name);
result.AppendLine(" : " + this.ResolveLabel(tables.First().DeveloperDocumentation));
}
else
{
result.AppendLine();
}
result.AppendLine("select");
// Calculate the union of the fields in all the tables in the hierarchy:
var fieldUnion = new Dictionary <string, AxTableField>(StringComparer.OrdinalIgnoreCase);
foreach (var table in tables)
{
foreach (var field in table.Fields)
{
fieldUnion[field.Name] = field;
}
}
// Now pick the fields that the user actually wants to see
var selectedFields = new List <AxTableField>();
foreach (var field in fields)
{
selectedFields.Add(fieldUnion[field.Name]);
}
this.AddFields(result, tables.First(), selectedFields, ref first);
this.AddSystemFields(result, tables.First(), ref first);
result.AppendLine("from " + SqlNameMangling.GetSqlTableName(tables.First().Name));
return(result);
}
