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 ""; } } }