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

using FastReport.Data;
using FastReport.Forms;
using FastReport.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;

namespace FastReport.FastQueryBuilder
{
    internal class Core
    {
        private IQueryDesigner queryDesigner;
        private DataBase dataBase;
        private DialogResult dialogResult;
        private Query query = new Query();

        // unstable features
        private bool unstableFeatures;
        private bool unstableFeaturesForce;

        public Core(IQueryDesigner qd, DataBase db)
        {
            unstableFeatures = true;
            dataBase = db;
            queryDesigner = qd;
            queryDesigner.OnOk += OnOk;
            queryDesigner.OnCancel += OnCancel;
            queryDesigner.OnGetTableList += OnGetTableList;
            queryDesigner.OnAddTable += OnAddTable;
            queryDesigner.OnGenerateSQL += OnGenerateSQL;
            queryDesigner.OnRunSQL += OnRunSQL;
            queryDesigner.Fields = query.SelectedFields;
            queryDesigner.Groups = query.GroupedFields;
            queryDesigner.Links = query.LinkList;
        }

        public bool UseJoin { get; set; } = true;

        public DialogResult DesignQuery()
        {
            queryDesigner.DesignQuery();
            return dialogResult;
        }

        private void OnOk(object sender, EventArgs e)
        {
            dialogResult = DialogResult.OK;
            queryDesigner.Close();
        }

        private void OnCancel(object sender, EventArgs e)
        {
            dialogResult = DialogResult.Cancel;
            queryDesigner.Close();
        }

        private void OnGetTableList(object sender, EventArgs e)
        {
            queryDesigner.DoFillTableList(dataBase.TableList);
        }

        private Table FindTable(List<Table> list, string tableName)
        {
            foreach (Table table in list)
            {
                if (table.Name == tableName)
                    return table;
            }
            return null;
        }

        private void OnAddTable(object sender, AddTableEventArgs e)
        {
            Table tbl = (Table)e.table.Clone();
            tbl.Alias = GetUniqueAlias(tbl, e.alias);
            query.TableList.Add(tbl);

            var tv = queryDesigner.DoAddTable(tbl, e.position);
            tv.OnAddLink += OnAddLink;
            tv.OnSelectField += OnSelectField;
            tv.OnDeleteTable += OnDeleteTable;
            e.tableView = tv;
        }

        private Field FindSelectedField(SqlParser.FieldStruct fs)
        {
            foreach (Field f in query.SelectedFields)
            {
                if ((f.Table.Alias == fs.Table || f.Table.Name == fs.Table) && FieldEqFieldStruct(f, fs))
                {
                    return f;
                }
            }
            return null;
        }

        private Field FindField(SqlParser.FieldStruct fs)
        {
            foreach (Table t in query.TableList)
            {
                if (t.Name == fs.Table || t.Alias == fs.Table)
                {
                    foreach (Field f in t.FieldList)
                    {
                        if (FieldEqFieldStruct(f, fs))
                            return f;
                    }
                }
            }
            return null;
        }

        private bool FieldEqFieldStruct(Field f, SqlParser.FieldStruct fs)
        {
            return f.Name == fs.Name ||
                            !String.IsNullOrEmpty(f.Alias) && f.Alias == fs.Name ||
                            !String.IsNullOrEmpty(fs.Alias) && f.Name == fs.Alias ||
                            !String.IsNullOrEmpty(f.Alias) && !String.IsNullOrEmpty(fs.Alias) && f.Alias == fs.Alias;
        }

        private bool hasDuplicate(string alias)
        {
            foreach (Table t in query.TableList)
            {
                if (t.Alias == alias)
                    return true;
            }
            return false;
        }

        private string GetUniqueAlias(Table tbl, string al)
        {
            if (string.IsNullOrEmpty(al))
            {
                al = tbl.Name[0].ToString().ToUpper();
                if (al[0] < 'A' || al[0] > 'Z')
                    al = "A";
            }

            int n = 1;
            while (hasDuplicate(al))
            {
                al = tbl.Name[0] + n.ToString();
                n++;
            }
            return al;
        }

        private void OnDeleteTable(object sender, AddTableEventArgs e)
        {
            for (int i = query.SelectedFields.Count - 1; i >= 0; i--)
            {
                if (query.SelectedFields[i].Table == e.table)
                    query.SelectedFields.RemoveAt(i);
            }
            query.DeleteTable(e.table);
            queryDesigner.Fields = query.SelectedFields;
        }

        private void OnAddLink(object sender, AddLinkEventArgs e)
        {
            Link lnk;
            if (LinkHasFrom(query.LinkList, e.fieldTo.Table))
                lnk = new Link(e.fieldTo, e.fieldFrom);
            else
                lnk = new Link(e.fieldFrom, e.fieldTo);

            if (LinkHas(query.LinkList, lnk))
                return;

            if (!e.useDefaults)
            {
                lnk.Join = e.joinType;
                lnk.Where = e.whereType;
            }
            else
            {
                lnk.Join = UseJoin ? QueryEnums.JoinTypes.InnerJoin : QueryEnums.JoinTypes.Where;
                lnk.Where = QueryEnums.WhereTypes.Equal;
            }

            query.LinkList.Add(lnk);
        }

        private bool LinkHas(List<Link> list, Link lnk)
        {
            foreach (Link link in list)
            {
                if (link.From == lnk.From && link.To == lnk.To)
                    return true;
            }
            return false;
        }

        private bool LinkHasFrom(List<Link> list, Table from)
        {
            foreach (Link link in list)
            {
                if (link.From.Table == from)
                    return true;
            }
            return false;
        }

        private void OnSelectField(object sender, CheckFieldEventArgs e)
        {
            if (e.value)
            {
                query.SelectedFields.Add(e.field);
            }
            else
            {
                query.SelectedFields.Remove(e.field);
            }
            queryDesigner.Fields = query.SelectedFields;
        }

        private void OnGenerateSQL(object sender, EventArgs e)
        {
            var sqlGen = new SQLGenerator(query);
            sqlGen.qch = dataBase.GetQuotationChars();

            queryDesigner.SQLText = sqlGen.getSql();

            #region Debug
            //queryDesigner.SQLText += "\n\n\n\n/*Tables:\n";
            //foreach (Table tbl in query.TableList)
            //{
            //    queryDesigner.SQLText += tbl.Name + "\n";  
            //}
            //queryDesigner.SQLText += "\nLinks:\n";
            //foreach (Link lnk in query.LinkList)
            //{
            //    queryDesigner.SQLText += lnk.From.Table + ":" + lnk.From.Name + " => " + lnk.To.Table + ":" + lnk.To.Name + "\n";
            //}
            //queryDesigner.SQLText += "\nFields:\n";
            //foreach (Field fld in query.SelectedFields)
            //{
            //    queryDesigner.SQLText += fld.Table.ToString() + '.' + fld.Name + "\n";
            //}
            //queryDesigner.SQLText += "*/";
            #endregion
        }

        private void OnRunSQL(object sender, EventArgs e)
        {
            var sqlGen = new SQLGenerator(query);
            sqlGen.qch = dataBase.GetQuotationChars();

            string sql = string.IsNullOrEmpty(queryDesigner.SQLText) ? sqlGen.getSql() : queryDesigner.SQLText;

            DataTable table = new DataTable();
            DataConnectionBase dataConnection = dataBase.dataBase;
            DbConnection conn = dataConnection.GetConnection();
            try
            {
                dataConnection.OpenConnection(conn);
                using (DbDataAdapter adapter = dataConnection.GetAdapter(sql, conn, new CommandParameterCollection(null)))
                {
                    table.Clear();
                    adapter.Fill(table);
                }
            }
            finally
            {
                dataConnection.DisposeConnection(conn);
            }
            queryDesigner.DataSource = table;
        }

        public string GetSql()
        {
            if (string.IsNullOrEmpty(queryDesigner.SQLText))
                OnGenerateSQL(null, null);
            return queryDesigner.SQLText;
        }

        public void SetSql(string sql)
        {
            if (unstableFeatures && !string.IsNullOrEmpty(sql))
            {
                var parser = new SqlParser(sql);
                parser.qch = dataBase.GetQuotationChars();
                var tableViews = new List<ITableView>();

                try
                {
                    parser.Parse();

                    List<Table> tables = dataBase.TableList;

                    // add tables
                    foreach (SqlParser.TableStruct t in parser.Tables)
                    {
                        if (!unstableFeatures)
                            break;

                        Table table = FindTable(tables, t.Name);
                        if (table != null)
                        {
                            var args = new AddTableEventArgs(table, System.Drawing.Point.Empty, t.Alias);
                            OnAddTable(queryDesigner, args);
                            tableViews.Add(args.tableView);
                        }
                        else
                        {
                            ShowError(new FormatException("Table " + t.Name + " is not found!"));
                        }
                    }

                    if (unstableFeatures)
                    {
                        // select fields
                        foreach (SqlParser.FieldStruct fs in parser.Fields)
                        {
                            if (!unstableFeatures)
                                break;

                            string tableName = fs.Table;
                            foreach (ITableView tv in tableViews)
                            {
                                if (!unstableFeatures)
                                    break;

                                Table tbl = tv.Table;
                                if (tbl.Name == tableName || tbl.Alias == tableName)
                                {
                                    if (!tv.SelectCheckBox(fs.Name, fs.Func, fs.Alias))
                                        ShowError(new FormatException("Field " + fs.Name + " is not found!"));
                                    break;
                                }
                            }
                        }

                        foreach (SqlParser.FieldStruct fs in parser.Where)
                        {
                            if (!unstableFeatures)
                                break;

                            Field f = FindSelectedField(fs);
                            if (f == null)
                            {
                                ShowError(new FormatException("Field " + fs.Name + " is not found!"));
                            }
                            else
                            {
                                f.Filter = fs.Filter;
                            }
                        }

                        foreach (SqlParser.FieldStruct fs in parser.Groups)
                        {
                            if (!unstableFeatures)
                                break;

                            Field f = FindSelectedField(fs);
                            if (f == null)
                            {
                                ShowError(new FormatException("Field " + fs.Name + " is not found!"));
                            }
                            else
                            {
                                f.Group = true;
                                query.GroupedFields.Add(f);
                            }
                        }

                        foreach (SqlParser.FieldStruct fs in parser.Orders)
                        {
                            if (!unstableFeatures)
                                break;

                            Field f = FindSelectedField(fs);
                            if (f == null)
                            {
                                ShowError(new FormatException("Field " + fs.Name + " is not found!"));
                            }
                            else
                            {
                                switch (fs.SortType)
                                {
                                    case SortTypes.Asc:
                                        f.Order = "Asc";
                                        break;
                                    case SortTypes.Desc:
                                        f.Order = "Desc";
                                        break;
                                }
                            }
                        }
                    }

                    if (unstableFeatures)
                    {
                        foreach (SqlParser.LinkStruct ls in parser.Links)
                        {
                            if (!unstableFeatures)
                                break;

                            Field fieldFrom = FindField(ls.One);
                            Field fieldTo = FindField(ls.Two);
                            if (fieldFrom == null)
                            {
                                ShowError(new FormatException("Field " + ls.One + " is not found!"));
                            }
                            if (fieldTo == null)
                            {
                                ShowError(new FormatException("Field " + ls.Two + " is not found!"));
                            }

                            if (fieldFrom != null && fieldTo != null)
                                OnAddLink(queryDesigner, new AddLinkEventArgs(fieldFrom, fieldTo, ls.JoinType, ls.WhereType));
                        }
                    }

                    if (unstableFeatures && !unstableFeaturesForce)
                    {
                        var sqlGen = new SQLGenerator(query);
                        sqlGen.qch = dataBase.GetQuotationChars();

                        string newSql = sqlGen.getSql();

                        var lexerOld = new SqlLexer(sql, sqlGen.qch);
                        var lexerNew = new SqlLexer(newSql, sqlGen.qch);

                        var tokensOld = lexerOld.Parse();
                        var tokensNew = lexerNew.Parse();

                        bool isSqlEquals = true;
                        if (tokensOld.Count != tokensNew.Count)
                        {
                            isSqlEquals = false;
                        }
                        else
                        {
                            for (int i = 0; i < tokensOld.Count; i++)
                            {
                                if (!tokensOld[i].Equals(tokensNew[i]))
                                {
                                    isSqlEquals = false;
                                    break;
                                }
                            }
                        }

                        if (!isSqlEquals)
                            ShowError(new Exception("Sql is not equals"));
                    }

                    if (!unstableFeatures)
                    {
                        query.LinkList.Clear();
                        query.SelectedFields.Clear();
                        query.GroupedFields.Clear();
                        query.TableList.Clear();
                        queryDesigner.Clear();
                        queryDesigner.Fields = query.SelectedFields;
                    }
                }
                catch (Exception e)
                {
                    ShowError(e);
                }
            }
        }

        private void ShowError(Exception e)
        {
#if DEBUG
            ExceptionForm form = new ExceptionForm(e);
            form.ShowDialog();
#endif
            if (unstableFeatures && !unstableFeaturesForce)
            {
                switch (MessageBox.Show(Res.Get("Forms,QueryBuilder,QueryIsCorrupted"),
                    Res.Get("Forms,QueryBuilder,QueryIsCorruptedTitle"),
                    MessageBoxButtons.YesNo, MessageBoxIcon.Warning))
                {
                    case DialogResult.Yes:
                        unstableFeaturesForce = true;
                        break;
                    case DialogResult.No:
                        unstableFeatures = false;
                        break;
                }
            }
        }
    }
}