3rdpartylibs/fastreports.wpf/FastReport/QueryBuilder/SQLGenerator.cs

using System.Collections.Generic;

namespace FastReport.FastQueryBuilder
{
    internal class SQLGenerator
    {
        private Query query;
        private List<Table> tableHasAlias = new List<Table>();

        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<Table> tableList = new List<Table>();
            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 "";
        }
    }
}