/// <summary>
/// Returns a string "table1 join table2 on x = y join table3 on a = b"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected override string GetFromClause(UniqueFieldPaths paths)
{
throw new NotImplementedException();
}
private void AssignAliases(UniqueFieldPaths paths)
{
int i = 1;
foreach (var path in paths)
{
var queue = new Queue<UniqueFieldPath>();
queue.Enqueue(path);
while (queue.Count > 0)
{
var fields = queue.Dequeue();
fields.Alias = String.Concat("q", i++);
foreach (var f in fields)
queue.Enqueue(f);
}
}
}
/// <summary>
/// Returns a string "SORT BY column1 asc, column2 desc"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected virtual string GetOrderByClause(UniqueFieldPaths paths)
{
var sb = new StringBuilder();
foreach (var s in _orderBy)
sb.AppendFormat("{0}.[{1}] {2}, ", paths[s.Path].Alias, s.Path.Last.SourceName, s.Direction);
if (sb.Length == 0)
return null;
sb.Insert(0, " ORDER BY ");
return sb.Remove(sb.Length - 2, 2).ToString();
}
/// <summary>
/// Returns a string "GROUP BY column1, column2"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected virtual string GetGroupByClause(UniqueFieldPaths paths)
{
var sb = new StringBuilder();
foreach (var g in _groupBy)
sb.AppendFormat("{0}.[{1}], ", paths[g].Alias, g.Last.SourceName);
if (sb.Length == 0)
return null;
sb.Insert(0, " GROUP BY ");
return sb.Remove(sb.Length - 2, 2).ToString();
}
/// <summary>
/// Returns a string "table1 join table2 on x = y join table3 on a = b"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected virtual string GetFromClause(UniqueFieldPaths paths)
{
// Target subject will be q0
// All other subjects aliased from q1 up
var sb = new StringBuilder();
sb.AppendFormat("({0}) AS {1} ", _target.Source, paths[new FieldPath(_target.IdField)].Alias);
foreach (var ufp in paths)
{
// if this isn't part of our original target, join through the matrix
if (!ufp.Subject.Equals(_target))
{
// else top level subject not included - join with matrix
sb.AppendFormat("LEFT OUTER JOIN ({1}) AS m{2} ON m{2}.FromID = {0}.[{4}] LEFT OUTER JOIN ({3}) AS {2} ON m{2}.ToID = {2}.[{5}] ",
paths[new FieldPath(_target.IdField)].Alias,
_configuration[_target, ufp.Subject].Query,
ufp.Alias,
ufp.Subject.Source,
_target.IdField.SourceName,
ufp.Subject.IdField.SourceName);
}
// ok so the first part of UniqueFieldPath is all good, now join the rest
var queue = new Queue<UniqueFieldPath>();
foreach (var f in ufp)
queue.Enqueue(f);
while (queue.Count > 0)
{
var field = queue.Dequeue();
sb.AppendFormat("LEFT OUTER JOIN ({0}) AS {1} ON {1}.[{4}] = {2}.[{3}] ",
field.Subject.Source,
field.Alias,
field.Predecessor.Alias,
field.Field.SourceName,
field.Subject.IdField.SourceName);
foreach (var child in field)
queue.Enqueue(child);
}
}
return sb.ToString();
}
/// <summary>
/// Returns a string "field1, field2, field3"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected virtual string GetColumnDefinitions(UniqueFieldPaths paths)
{
var sb = new StringBuilder();
foreach (var c in _columns)
sb.AppendFormat(String.Concat("{0}.[{1}]", (AliasColumns ? " AS [{2}]" : string.Empty), ", "),
paths[c].Alias, c.Last.SourceName, c.Description);
return sb.Remove(sb.Length - 2, 2).ToString();
}
