public void BuildSQL()
{
try
{
_sql = "";
ExecutionError = "";
if (Source.MetaData == null) return;
AddSubReportsElements();
InitReferences();
execSelectClause = new StringBuilder();
execFromClause = new StringBuilder();
execWhereClause = new StringBuilder(Restriction);
execGroupByClause = new StringBuilder();
execOrderByNameClause = new StringBuilder();
execHavingClause = new StringBuilder(AggregateRestriction);
execOrderByClause = new StringBuilder();
//build restriction
RestrictionText = "";
foreach (ReportRestriction restriction in ExecutionRestrictions)
{
if (restriction.HasValue) Helper.AddValue(ref RestrictionText, "\r\n", restriction.DisplayText);
execWhereClause = execWhereClause.Replace("[" + restriction.GUID + "]", restriction.SQLText);
}
if (Report.CheckingExecution)
{
if (execWhereClause.ToString().Trim().Length == 0) execWhereClause.Append("1=0");
else execWhereClause.Append(" AND (1=0)");
}
foreach (ReportRestriction restriction in ExecutionAggregateRestrictions)
{
if (restriction.HasValue) Helper.AddValue(ref RestrictionText, "\r\n", restriction.DisplayText);
execHavingClause = execHavingClause.Replace("[" + restriction.GUID + "]", restriction.SQLText);
}
if (Elements.Count > 0)
{
_fromTables = new List<MetaTable>();
List<MetaJoin> joins = new List<MetaJoin>();
List<string> selectColumns = new List<string>();
List<string> groupByColumns = new List<string>();
SetColumnsName();
foreach (ReportElement element in Elements)
{
string sqlColumn = element.SQLColumn + " AS " + element.SQLColumnName;
if (!selectColumns.Contains(sqlColumn))
{
Helper.AddValue(ref execSelectClause, ",\r\n", " " + sqlColumn);
selectColumns.Add(sqlColumn);
}
MetaTable table = element.MetaColumn.MetaTable;
if (table != null && !_fromTables.Contains(table)) _fromTables.Add(table);
if (element.PivotPosition != PivotPosition.Data && !groupByColumns.Contains(element.SQLColumn))
{
Helper.AddValue(ref execGroupByClause, ",", element.SQLColumn);
groupByColumns.Add(element.SQLColumn);
}
}
foreach (ReportRestriction restriction in ExecutionRestrictions.Union(ExecutionAggregateRestrictions))
{
MetaTable table = restriction.MetaColumn.MetaTable;
if (table != null && !_fromTables.Contains(table) && restriction.HasValue && restriction.Operator != Operator.ValueOnly) _fromTables.Add(table);
}
if (GetElements(PivotPosition.Data).Count() == 0 && execHavingClause.Length == 0) execGroupByClause = new StringBuilder();
List<string> orderColumns = new List<string>();
buildOrderClause(GetElements(PivotPosition.Page), orderColumns, ref execOrderByClause, ref execOrderByNameClause);
buildOrderClause(GetElements(PivotPosition.Row), orderColumns, ref execOrderByClause, ref execOrderByNameClause);
buildOrderClause(GetElements(PivotPosition.Column), orderColumns, ref execOrderByClause, ref execOrderByNameClause);
buildOrderClause(GetElements(PivotPosition.Data), orderColumns, ref execOrderByClause, ref execOrderByNameClause);
List<MetaTable> extraWhereTables = _fromTables.Where(i => !string.IsNullOrEmpty(i.WhereSQL)).ToList();
if (_fromTables.Count == 1)
{
execFromClause = new StringBuilder(_fromTables[0].FullSQLName + "\r\n");
}
else
{
//multiple tables, find joins...
List<MetaTable> tablesToUse = _fromTables.ToList();
List<JoinPath> resultPaths = new List<JoinPath>();
foreach (var leftTable in _fromTables)
{
JoinPath rootPath = new JoinPath() { currentTable = leftTable, joinsToUse = new List<MetaJoin>(Source.MetaData.Joins) };
rootPath.tablesToUse = new List<MetaTable>(_fromTables.Where(i => i.GUID != leftTable.GUID));
JoinTables(rootPath, resultPaths);
}
//Choose the path having all tables, then preferred, then less joins...
JoinPath bestPath = resultPaths.Where(i => i.tablesToUse.Count == 0).OrderByDescending(i => i.rank).ThenBy(i => i.joins.Count).FirstOrDefault();
if (bestPath == null)
{
//no direct joins found...try using several path...
foreach (var path in resultPaths.OrderByDescending(i => i.rank).ThenBy(i => i.tablesToUse.Count))
{
JoinPath newPath = new JoinPath() { joins = new List<MetaJoin>(path.joins), tablesToUse = new List<MetaTable>(path.tablesToUse) };
foreach (var join in path.joins)
{
//search a path starting from RightTable and finishing by a remaining table
foreach (var path2 in resultPaths.Where(i => i.startTable == join.RightTable && path.tablesToUse.Contains(i.finalTable)))
{
//ok add joins to the newPath and remove tables to use
int index = path.joins.IndexOf(join);
foreach (var join2 in path2.joins)
{
//Note that we insert the joins just before the join having the RightTable...un peu limite tricky !
if (!newPath.joins.Exists(i => i.GUID == join2.GUID))
{
newPath.joins.Insert(index, join2);
}
newPath.tablesToUse.Remove(join2.RightTable);
}
}
if (newPath.tablesToUse.Count == 0)
{
//got it
bestPath = newPath;
break;
}
}
if (bestPath != null) break;
}
}
if (bestPath == null) throw new Exception("Unable to link all elements using the joins defined...\r\nAdd Joins to your Data Source\r\nOR remove elements or restrictions in your model\r\nOR add relevant elements or restrictions in your model.");
if (bestPath.joins.Count == 0)
{
//only one table
execFromClause = new StringBuilder(bestPath.currentTable.FullSQLName + "\r\n");
}
else
{
/* this was the old flat method that does not work with Access...but produce a clean select
foreach (MetaJoin join in bestPath.joins)
{
if (execFromClause.Length == 0) execFromClause = new StringBuilder(join.LeftTable.FullSQLName + "\r\n");
execFromClause.AppendFormat("{0} {1} ON {2}\r\n", join.SQLJoinType, join.RightTable.FullSQLName, join.Clause.Trim());
}*/
bestPath.print();
string lastTable = null;
List<MetaTable> tablesUsed = new List<MetaTable>();
for (int i = bestPath.joins.Count - 1; i >= 0; i--)
{
MetaJoin join = bestPath.joins[i];
if (string.IsNullOrEmpty(lastTable))
{
lastTable = join.RightTable.FullSQLName + "\r\n";
tablesUsed.Add(join.RightTable);
}
//check if tables are already in the join
var leftTable = join.LeftTable;
if (tablesUsed.Contains(leftTable)) leftTable = join.RightTable;
if (tablesUsed.Contains(leftTable)) continue;
string joinClause = join.Clause.Trim();
//For outer join, add the extra restriction in the ON clause -> hopefully they are not defined as bi-directional
MetaTable extraWhereTable = null;
if (join.JoinType == JoinType.LeftOuter && !string.IsNullOrEmpty(join.RightTable.WhereSQL)) extraWhereTable = join.RightTable;
else if (join.JoinType == JoinType.RightOuter && !string.IsNullOrEmpty(join.LeftTable.WhereSQL)) extraWhereTable = join.LeftTable;
else if (!string.IsNullOrEmpty(leftTable.WhereSQL) && !extraWhereTables.Contains(leftTable))
{
extraWhereTables.Add(leftTable);
}
if (extraWhereTable != null)
{
string where = Helper.ParseRazor(extraWhereTable.WhereSQL, extraWhereTable);
if (!string.IsNullOrEmpty(where)) joinClause += " AND " + where;
extraWhereTables.Remove(extraWhereTable);
}
//finally build the clause
if (join.JoinType != JoinType.Cross) lastTable = string.Format("\r\n({0} {1} {2} ON {3})\r\n", leftTable.FullSQLName, join.SQLJoinType, lastTable, joinClause);
else lastTable = string.Format("\r\n({0} {1} {2})\r\n", leftTable.FullSQLName, join.SQLJoinType, lastTable);
tablesUsed.Add(leftTable);
}
execFromClause = new StringBuilder(lastTable);
}
}
//add extra where clause
foreach (var table in extraWhereTables)
{
if (!string.IsNullOrEmpty(table.WhereSQL))
{
string where = Helper.ParseRazor(table.WhereSQL, table);
if (!string.IsNullOrEmpty(where))
{
if (execWhereClause.Length != 0) execWhereClause.Append("\r\nAND ");
execWhereClause.AppendFormat("({0})", where);
}
}
}
execSelect = execGroupByClause.Length > 0 ? "SELECT\r\n" : "SELECT DISTINCT\r\n";
execSelect = !string.IsNullOrEmpty(SqlSelect) ? SqlSelect : execSelect;
_sql = execSelect;
_sql += string.Format("{0}\r\n", execSelectClause);
_sql += !string.IsNullOrEmpty(SqlFrom) ? SqlFrom : string.Format("FROM {0}", execFromClause);
if (execWhereClause.Length > 0) _sql += string.Format("WHERE {0}\r\n", execWhereClause);
if (execGroupByClause.Length > 0) _sql += (!string.IsNullOrEmpty(SqlGroupBy) ? SqlGroupBy : string.Format("GROUP BY {0}", execGroupByClause)) + "\r\n";
if (execHavingClause.Length > 0) _sql += string.Format("HAVING {0}\r\n", execHavingClause);
if (execOrderByClause.Length > 0) _sql += (!string.IsNullOrEmpty(SqlOrderBy) ? SqlOrderBy : string.Format("ORDER BY {0}", execOrderByClause)) + "\r\n";
}
}
catch (TemplateCompilationException ex)
{
_sql = "";
ExecutionError = string.Format("Got unexpected error when building the SQL statement:\r\n{0}", Helper.GetExceptionMessage(ex));
}
catch (Exception ex)
{
_sql = "";
ExecutionError = string.Format("Got unexpected error when building the SQL statement:\r\n{0}", ex.Message);
}
}
void JoinTables(JoinPath path, List<JoinPath> resultPath)
{
if (path.tablesToUse.Count != 0)
{
foreach (var join in path.joinsToUse.Where(i => i.LeftTableGUID == path.currentTable.GUID || (i.RightTableGUID == path.currentTable.GUID && i.IsBiDirectional)))
{
JoinPath newJoinPath = new JoinPath() { joins = new List<MetaJoin>(path.joins), tablesToUse = new List<MetaTable>(path.tablesToUse), joinsToUse = new List<MetaJoin>(path.joinsToUse), rank = path.rank };
MetaTable newTable = join.RightTable;
MetaJoin newJoin = join;
if (join.RightTable == path.currentTable && join.IsBiDirectional)
{
//Create a new join having the other left-right
newJoin = MetaJoin.Create();
newJoin.GUID = join.GUID;
newJoin.Source = join.Source;
newJoin.LeftTableGUID = join.RightTableGUID;
newJoin.RightTableGUID = join.LeftTableGUID;
newJoin.JoinType = join.JoinType;
newJoin.Clause = join.Clause;
//In this case the next table is the left one...
newTable = join.LeftTable;
}
//add the join and continue the path
newJoinPath.currentTable = newTable;
newJoinPath.joins.Add(newJoin);
newJoinPath.tablesToUse.Remove(newTable);
newJoinPath.joinsToUse.Remove(join);
//Set preferred path
if (newTable == ForceJoinTable) newJoinPath.rank++;
if (newTable == AvoidJoinTable) newJoinPath.rank--;
JoinTables(newJoinPath, resultPath);
}
}
if (path.joins.Count > 0 && _fromTables.Contains(path.joins.Last().RightTable))
{
path.startTable = path.joins.First().LeftTable;
path.finalTable = path.joins.Last().RightTable;
resultPath.Add(path);
}
}
void JoinTables(JoinPath path, List<JoinPath> resultPath)
{
//TODO: optimize speed of this procedure...
//If the search is longer than 5 seconds, we exite with the first path found...
if ((DateTime.Now - _buildTimer).TotalSeconds > BuildTimeout)
{
if (resultPath.Exists(i => i.tablesToUse.Count == 0))
{
Debug.WriteLine("Exiting the joins search after 5 seconds");
return;
}
}
if (path.tablesToUse.Count != 0)
{
foreach (var join in path.joinsToUse.Where(i => i.LeftTableGUID == path.currentTable.GUID || (i.RightTableGUID == path.currentTable.GUID && i.IsBiDirectional)))
{
//Check that the new table has not already been reached
if (path.joins.Exists(i => i.RightTable == (join.RightTable == path.currentTable && join.IsBiDirectional ? join.LeftTable : join.RightTable))) continue;
MetaTable newTable = join.RightTable;
MetaJoin newJoin = join;
if (join.RightTable == path.currentTable && join.IsBiDirectional)
{
//Create a new join having the other left-right
newJoin = MetaJoin.Create();
newJoin.GUID = join.GUID;
newJoin.Source = join.Source;
newJoin.LeftTableGUID = join.RightTableGUID;
newJoin.RightTableGUID = join.LeftTableGUID;
newJoin.JoinType = join.JoinType;
newJoin.Clause = join.Clause;
//In this case the next table is the left one...
newTable = join.LeftTable;
}
//if (_level == 1) Debug.WriteLine("{0} {1}", resultPath.Count, newTable.Name);
JoinPath newJoinPath = new JoinPath() { joins = new List<MetaJoin>(path.joins), tablesToUse = new List<MetaTable>(path.tablesToUse), joinsToUse = new List<MetaJoin>(path.joinsToUse), rank = path.rank };
//add the join and continue the path
newJoinPath.currentTable = newTable;
newJoinPath.joins.Add(newJoin);
newJoinPath.tablesToUse.Remove(newTable);
newJoinPath.joinsToUse.Remove(join);
//Set preferred path
if (newTable == ForceJoinTable) newJoinPath.rank++;
if (newTable == AvoidJoinTable) newJoinPath.rank--;
JoinTables(newJoinPath, resultPath);
}
}
if (path.joins.Count > 0 && _fromTables.Contains(path.joins.Last().RightTable))
{
path.startTable = path.joins.First().LeftTable;
path.finalTable = path.joins.Last().RightTable;
resultPath.Add(path);
}
}
