using System.Collections.Generic;
namespace FastReport.FastQueryBuilder
{
internal class SQLGenerator
{
private Query query;
private List
tableHasAlias = new List();
public string qch;
public SQLGenerator(Query qr)
{
query = qr;
}
public string getSql()
{
tableHasAlias.Clear();
string result = "";
result += getSelect();
result += getFrom();
result += getWhere();
result += getGroup();
result += getOrder();
return result;
}
private string getGroup()
{
string result = "";
foreach (Field fld in query.GroupedFields)
{
result += fld.getFullName(qch) + ", ";
}
if (result != "")
return "GROUP BY " + result.Substring(0, result.Length - 2) + "\r\n";
else
return "";
}
private string getOrder()
{
string result = "";
foreach (Field fld in query.SelectedFields)
{
if (!string.IsNullOrEmpty(fld.Order))
result += fld.getFullName(qch) + " " + fld.Order + ", ";
}
if (result != "")
return "ORDER BY " + result.Substring(0, result.Length - 2) + "\r\n";
else
return "";
}
private string getSelect()
{
if (query.SelectedFields.Count == 0)
return "SELECT 1 ";
string result = "";
foreach (Field fld in query.SelectedFields)
{
if (!string.IsNullOrEmpty(fld.Func))
result += fld.Func + '(' + fld.getFullName(qch) + ')';
else
result += fld.getFullName(qch);
if (fld.Alias.Length != 0)
result += " AS " + fld.Alias;
result += ", ";
}
return "SELECT " + result.Remove(result.Length - 2) + "\r\n";
}
private string getJoin(Link lnk)
{
string result = QueryEnums.JoinTypesToStr[(int)lnk.Join] + " " +
lnk.To.Table.getFromName(qch, !tableHasAlias.Contains(lnk.To.Table)) + " ON " +
lnk.From.getFullName(qch, !tableHasAlias.Contains(lnk.From.Table)) +
" " + QueryEnums.WhereTypesToStr[(int)lnk.Where] + " " +
lnk.To.getFullName(qch, !tableHasAlias.Contains(lnk.To.Table)) + " \r\n";
tableHasAlias.Add(lnk.To.Table);
return result;
}
private string getFrom()
{
if (query.TableList.Count == 0)
return "";
string result = "";
List tableList = new List();
foreach (Table tbl in query.TableList)
{
tableList.Add(tbl);
}
foreach (Link lnk in query.LinkList)
{
if (lnk.Join != QueryEnums.JoinTypes.Where)
{
if (result == "")
{
result += lnk.From.Table.getFromName(qch) + " ";
tableList.Remove(lnk.From.Table);
result += getJoin(lnk);
}
else
{
result = "(" + result + ") " + getJoin(lnk);
}
tableList.Remove(lnk.To.Table);
}
else
{
if (!tableList.Contains(lnk.To.Table))
{
if (result != "")
result += ", ";
result += lnk.To.Table.getFromName(qch);
tableList.Remove(lnk.To.Table);
}
}
}
foreach (Table tbl in tableList)
{
if (result != "")
result += ", ";
result += tbl.getFromName(qch);
}
return "FROM " + result + "\r\n";
}
private string getWhere()
{
string result = "";
foreach (Field fld in query.SelectedFields)
{
if (!string.IsNullOrEmpty(fld.Filter))
result += fld.getFullName(qch) + fld.Filter + " AND ";
}
if (query.LinkList.Count != 0)
{
foreach (Link lnk in query.LinkList)
{
string wop = "";
if (lnk.Join == QueryEnums.JoinTypes.Where)
{
wop = QueryEnums.WhereTypesToStr[(int)lnk.Where];
result += lnk.From.getFullName(qch) + " " + wop + " " + lnk.To.getFullName(qch) + " AND ";
}
}
}
if (result != "")
{
result = result.Substring(0, result.Length - 5);
return "WHERE " + result + "\r\n";
}
return "";
}
}
}