/// <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 GenerateFromTable(Tables.ITable selectedTable, bool addGroupBy)
{
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 <Metadata.MetaModel.AxTable> tables = this.SuperTables(selectedTable.Name);
tableName = SqlNameMangling.GetSqlTableName(tables.First().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();
//}
if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
{
result.AppendLine($"SELECT {tableName}.DATAAREAID, COUNT(*) AS COUNTER");
}
else
{
result.AppendLine("SELECT COUNT(*) AS COUNTER");
}
result.AppendLine("FROM " + tableName);
if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes && addGroupBy)
{
result.AppendLine($"GROUP BY {tableName}.{SqlNameMangling.GetValidSqlNameForField("DATAAREAID")}");
}
}
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 <Tables.IRelation> selectedRelations)
{
Tables.ITable table = selectedRelations.First().Table;
Stack <Metadata.MetaModel.AxTable> tables = this.SuperTables(table.Name);
var result = this.GenerateFromTable(table, false);
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 (Tables.IRelationConstraint constraint in relation.RelationConstraints)
{
if (!first)
{
result.Append(" AND ");
}
if (constraint is Tables.IRelationConstraintField)
{ // Table.field = RelatedTable.relatedField
var fieldConstraint = constraint as Tables.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 Tables.IRelationConstraintFixed)
{ // Table.field = value
var fixedConstraint = constraint as Tables.IRelationConstraintFixed;
result.Append(SqlNameMangling.GetSqlTableName(table.Name) + ".[" + SqlNameMangling.GetValidSqlNameForField(fixedConstraint.Field) + "]");
result.Append(" = ");
result.AppendLine(fixedConstraint.Value.ToString(CultureInfo.InvariantCulture));
}
else if (constraint is Tables.IRelationConstraintRelatedFixed)
{ // Value = RelatedTable.field
var relatedFixedConstraint = constraint as Tables.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 (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
{
result.AppendLine($"GROUP BY {SqlNameMangling.GetSqlTableName(tables.First().Name)}.{SqlNameMangling.GetValidSqlNameForField("DATAAREAID")}");
}
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(Tables.ITableExtension selectedExtensionTable, bool addGroupBy)
{
var result = new StringBuilder();
bool first = true;
result.AppendLine(string.Format(CultureInfo.InvariantCulture, "USE {0}", BusinessDatabaseName));
result.AppendLine("GO");
result.AppendLine();
Metadata.MetaModel.AxTableExtension extension = this.MetadataProvider.TableExtensions.Read(selectedExtensionTable.Name);
var baseTableName = selectedExtensionTable.Name.Split('.').First();
var tables = this.SuperTables(baseTableName);
tableName = baseTableName;
HashSet <string> extendedFields = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
foreach (var extendedField in extension.Fields)
{
extendedFields.Add(extendedField.Name);
}
if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
{
result.AppendLine($"SELECT {SqlNameMangling.GetSqlTableName(tables.First().Name)}.DATAAREAID, COUNT(*) AS COUNTER");
}
else
{
result.AppendLine("SELECT COUNT(*) AS COUNTER");
}
result.AppendLine("FROM " + SqlNameMangling.GetSqlTableName(tables.First().Name));
if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes && addGroupBy)
{
result.AppendLine($"GROUP BY {SqlNameMangling.GetSqlTableName(tables.First().Name)}.{SqlNameMangling.GetValidSqlNameForField("DATAAREAID")}");
}
return(result);
}