#region

using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using CSFrameworkV5.Business;
using CSFrameworkV5.Common;
using CSFrameworkV5.Core;
using CSFrameworkV5.Interfaces;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid;

#endregion

namespace CSFrameworkV5.Library.CommonForms
{
    public partial class frmAdvancedSearch : frmBase
    {
        //支持高级搜索的接口
        private ISupportAdvancedSearch _BLL;

        private string _CombinedSQL = string.Empty;

        private GridView _OutputView;
        private Control _ValueControl1;
        private Control _ValueControl2;

        private frmAdvancedSearch()
        {
            InitializeComponent();

            _ValueControl1 = txtValue;
            _ValueControl2 = txtValue1;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(cbFields.Text) &&
                string.IsNullOrEmpty(cbFields1.Text))
            {
                Msg.Warning("没有指定查询字段！");
                return;
            }

            if (_BLL != null)
            {
                try
                {
                    frmWaitingEx.ShowMe(this);

                    if (_BLL.ResultType == AdvancedSearchResult.WhereSQL)
                    {
                        var where = GetCombinedSQL(); //组合查询条件
                        var result = _BLL.AdvancedSearch(where);
                        _OutputView.GridControl.DataSource = result;
                    }

                    if (_BLL.ResultType == AdvancedSearchResult.DbParamList)
                    {
                        string whereSQL; //组合where条件, 如：where Code=@Code
                        var paramList =
                            GetDbParameterList(out whereSQL); //组合查询条件
                        var result = _BLL.AdvancedSearch(whereSQL, paramList);
                        _OutputView.GridControl.DataSource = result;
                    }
                }
                catch (Exception ex)
                {
                    LogDB.Log.WriteLog(LogTypeSystem.Exception, ex.Message,
                        GetType().Name, "高级查询", "");
                }
                finally
                {
                    frmWaitingEx.HideMe(this);
                }

                Msg.ShowInformation("查询成功，共找到 " +
                                    _OutputView.RowCount.ToStringEx() +
                                    " 条记录！");
                //this.Close();
            }
            else
            {
                Msg.Warning("未指查询的业务对象(BLL)！");
            }
        }

        private bool CheckNum(Control editor)
        {
            decimal ret = 0;
            if (!decimal.TryParse(editor.Text, out ret))
            {
                Msg.Warning("无效的数字!");
                editor.Focus();
                return false;
            }

            return true;
        }

        /// <summary>
        ///     打开高级查询窗体. 参数gridView用于列出所有显示字段. objType:为搜索的对象类型.
        /// </summary>
        public static string Execute(GridView gridView,
            ISupportAdvancedSearch BLL)
        {
            var form = new frmAdvancedSearch();
            form._BLL = BLL;
            form.InitWindow(gridView);
            form.ShowDialog();
            return form._CombinedSQL;
        }

        public static string Execute(GridView summaryView, GridView detailView,
            ISupportAdvancedSearch BLL)
        {
            var form = new frmAdvancedSearch();
            form._BLL = BLL;
            form.InitWindow(summaryView, detailView);
            form.ShowDialog();
            return form._CombinedSQL;
        }

        private void frmAdvSearch_Load(object sender, EventArgs e)
        {
            if (cbFields.Properties.Items.Count > 0) cbFields.SelectedIndex = 0;
        }

        /// <summary>
        ///     组合Where查询条件
        /// </summary>
        /// <returns></returns>
        private string GetCombinedSQL()
        {
            _CombinedSQL = " ({0}{1}{2}) ";
            var sql = " (1=1) ";
            var sql1 = " (1=1) ";
            var logicOperator = " AND ";
            if (rbOr.Checked) logicOperator = " OR ";

            var item1 = (ItemObject)cbFields.SelectedItem;
            if (item1 != null)
            {
                var type1 = (Type)item1.Tag;
                if (IsNum(type1))
                    if (!CheckNum(_ValueControl1))
                        return "";

                sql = GetSQL((ItemObject)cbFields.SelectedItem, 0);
            }

            var item2 = (ItemObject)cbFields1.SelectedItem;
            if (item2 != null)
            {
                var type2 = (Type)item2.Tag;
                if (IsNum(type2))
                    if (!CheckNum(_ValueControl2))
                        return "";

                sql1 = GetSQL((ItemObject)cbFields1.SelectedItem, 1);
            }

            _CombinedSQL =
                string.Format(_CombinedSQL, sql, logicOperator, sql1);

            return _CombinedSQL;
        }

        private DbParameterModel[] GetDbParameterList(out string whereSQL)
        {
            var list = new List<DbParameterModel>();

            whereSQL = "";

            var sql = " (1=1) ";
            var logicOperator = " AND ";
            if (rbOr.Checked) logicOperator = " OR ";

            var paramValue = "";
            var paramName = "";

            var item1 = (ItemObject)cbFields.SelectedItem;
            if (item1 != null)
            {
                var type1 = (Type)item1.Tag;
                if (IsNum(type1))
                    if (!CheckNum(_ValueControl1))
                        return list.ToArray();

                sql = GetSqlDbParameter((ItemObject)cbFields.SelectedItem, 0,
                    out paramName, out paramValue);
                list.Add(new DbParameterModel
                    { ParamName = paramName, ParamValue = paramValue });
                whereSQL = sql;
            }

            var item2 = (ItemObject)cbFields1.SelectedItem;
            if (item2 != null)
            {
                var type2 = (Type)item2.Tag;
                if (IsNum(type2))
                    if (!CheckNum(_ValueControl2))
                        return list.ToArray();

                sql = GetSqlDbParameter((ItemObject)cbFields1.SelectedItem, 1,
                    out paramName, out paramValue);
                list.Add(new DbParameterModel
                    { ParamName = paramName, ParamValue = paramValue });

                whereSQL = whereSQL + logicOperator + sql;
            }

            whereSQL = " (" + whereSQL + ") ";

            return list.ToArray();
        }

        private string GetOperator(int index)
        {
            ItemObject item = null;
            if (index == 0) item = (ItemObject)cbOperator.SelectedItem;

            if (index == 1) item = (ItemObject)cbOperator1.SelectedItem;

            if (item != null) return item.Value.ToStringEx();

            return string.Empty;
        }

        private string GetSQL(ItemObject item, int index)
        {
            var operate = GetOperator(index);
            var value = "";

            var type = (Type)item.Tag;

            if (index == 0)
                value = _ValueControl1.Text;
            else if (index == 1) value = _ValueControl2.Text;

            //防止SQL注入
            value = Globals.RemoveInjection(value.ToStringEx());

            var sql = " (1=1) ";

            //处理日期类型
            if (type == typeof(DateTime) &&
                value.ToStringEx().Trim() != string.Empty)
            {
                sql = "{0} {1} '{2}'";
                if (item is FieldItem)
                    sql = string.Format(sql,
                        ((FieldItem)item).TableAlias + item.Value, operate,
                        ConvertEx.ToCharYYYY_MM_DD_HHMMSS(
                            DateTime.Parse(value.ToStringEx())));
                else
                    sql = string.Format(sql, item.Value, operate,
                        ConvertEx.ToCharYYYY_MM_DD_HHMMSS(
                            DateTime.Parse(value.ToStringEx())));
            }
            //处理字符类型
            else if (type == typeof(string) || type == typeof(char))
            {
                if (operate == "like")
                {
                    if (item is FieldItem)
                        sql = string.Format("({0} {1} '%{2}%')",
                            ((FieldItem)item).TableAlias + item.Value, operate,
                            value);
                    else
                        sql = string.Format("({0} {1} '%{2}%')", item.Value,
                            operate, value);
                }
                else
                {
                    if (item is FieldItem)
                        sql = string.Format("({0} {1} '{2}')",
                            ((FieldItem)item).TableAlias + item.Value, operate,
                            value);
                    else
                        sql = string.Format("({0} {1} '{2}')", item.Value,
                            operate, value);
                }
            }
            //处理数字类型
            else if (IsNum(type))
            {
                if (item is FieldItem)
                    sql = string.Format(" {0}{1}{2}",
                        ((FieldItem)item).TableAlias + item.Value, operate,
                        value.ToStringEx());
                else
                    sql = string.Format(" {0}{1}{2}", item.Value, operate,
                        value.ToStringEx());
            }

            return sql;
        }

        /// <summary>
        /// </summary>
        /// <param name="item"></param>
        /// <param name="index"></param>
        /// <param name="value">返回DbParameter参数的值</param>
        /// <returns>返回SQL, 如： Code=#P#Code </returns>
        private string GetSqlDbParameter(ItemObject item, int index,
            out string paramName, out string value)
        {
            value = "";

            var operate = GetOperator(index);

            var type = (Type)item.Tag;

            if (index == 0)
                value = _ValueControl1.Text;
            else if (index == 1) value = _ValueControl2.Text;

            //防止SQL注入, 用户输入的查询条件值
            value = Globals.RemoveInjection(value.ToStringEx());

            var sql = " (1=1) ";
            paramName = DbParameterModel.ParamSymbol +
                        item.Value
                            .ToStringEx(); //如：#P#FieldName, #P#用于DAL层替换参数符号为@FieldName

            //处理日期类型
            if (type == typeof(DateTime) &&
                value.ToStringEx().Trim() != string.Empty)
            {
                sql = "{0} {1} {2}";
                if (item is FieldItem)
                    sql = string.Format(sql,
                        ((FieldItem)item).TableAlias + item.Value, operate,
                        paramName);
                else
                    sql = string.Format(sql, item.Value, operate, paramName);
            }
            //处理字符类型
            else if (type == typeof(string) || type == typeof(char))
            {
                if (operate == "like")
                {
                    value = $"%{value}%";
                    if (item is FieldItem)
                        sql = string.Format("{0} {1} {2}",
                            ((FieldItem)item).TableAlias + item.Value, operate,
                            paramName);
                    else
                        sql = string.Format("{0} {1} {2}", item.Value, operate,
                            paramName);
                }
                else
                {
                    if (item is FieldItem)
                        sql = string.Format("{0} {1} {2}",
                            ((FieldItem)item).TableAlias + item.Value, operate,
                            paramName);
                    else
                        sql = string.Format("{0} {1} {2}", item.Value, operate,
                            paramName);
                }
            }
            //处理数字类型
            else if (IsNum(type))
            {
                if (item is FieldItem)
                    sql = string.Format(" {0}{1}{2}",
                        ((FieldItem)item).TableAlias + item.Value, operate,
                        paramName);
                else
                    sql = string.Format(" {0}{1}{2}", item.Value, operate,
                        paramName);
            }

            return sql;
        }

        private void InitWindow(GridView gridView)
        {
            _CombinedSQL = string.Empty;
            _OutputView = gridView;

            var dataSource = gridView.GridControl.DataSource;

            if (dataSource == null)
                dataSource = _BLL.AdvancedSearch("1=0", null) as DataTable;

            if (dataSource is DataTable)
            {
                ItemObject item = null;
                foreach (GridColumn col in gridView.Columns)
                {
                    item = new ItemObject(col.Caption, col.FieldName);
                    item.Tag = (dataSource as DataTable).Columns[col.FieldName]
                        .DataType;
                    cbFields.Properties.Items.Add(item);
                    cbFields1.Properties.Items.Add(item);
                }

                cbFields.Properties.Items.Insert(0, new ItemObject("", ""));
                cbFields1.Properties.Items.Insert(0, new ItemObject("", ""));
            }
        }

        private void InitWindow(GridView summaryView, GridView detailView)
        {
            _OutputView = summaryView;

            var master = summaryView.GridControl.DataSource;
            var detail = summaryView.GridControl.DataSource;

            if (master is DataTable && detail is DataTable == false) return;

            _CombinedSQL = string.Empty;
            FieldItem item = null;
            foreach (GridColumn col in summaryView.Columns)
            {
                item = new FieldItem(col.Caption, col.FieldName);
                item.Tag = (master as DataTable).Columns[col.FieldName]
                    .DataType;
                item.TableAlias = "a."; //主表别名
                cbFields.Properties.Items.Add(item);
                cbFields1.Properties.Items.Add(item);
            }

            foreach (GridColumn col in detailView.Columns)
            {
                item = new FieldItem(col.Caption, col.FieldName);
                item.Tag = (detail as DataTable).Columns[col.FieldName]
                    .DataType;
                item.TableAlias = "b."; //明细表别名
                cbFields.Properties.Items.Add(item);
                cbFields1.Properties.Items.Add(item);
            }

            cbFields.Properties.Items.Insert(0, new ItemObject("", ""));
            cbFields1.Properties.Items.Insert(0, new ItemObject("", ""));
        }

        private bool IsNum(Type type)
        {
            if (type == typeof(int) || type == typeof(float)
                                    || type == typeof(decimal)
                                    || type == typeof(float)
                                    || type == typeof(double)
                                    || type == typeof(int))
                return true;

            return false;
        }

        private void LoadOperators(bool allowLike, int index)
        {
            if (index == 0)
            {
                cbOperator.Properties.Items.Clear();
                if (cbFields.Text.Trim() != string.Empty)
                {
                    cbOperator.Properties.Items.Add(new ItemObject("等于=", "="));
                    cbOperator.Properties.Items.Add(
                        new ItemObject("大于等于>=", ">="));
                    cbOperator.Properties.Items.Add(
                        new ItemObject("小于等于<=", "<="));
                    cbOperator.Properties.Items.Add(
                        new ItemObject("不等于<>", "<>"));
                    if (allowLike)
                        cbOperator.Properties.Items.Add(
                            new ItemObject("匹配字符like", "like"));

                    cbOperator.SelectedIndex = 0;
                }
            }

            if (index == 1)
            {
                cbOperator1.Properties.Items.Clear();
                if (cbFields1.Text.Trim() != string.Empty)
                {
                    cbOperator1.Properties.Items.Add(
                        new ItemObject("等于=", "="));
                    cbOperator1.Properties.Items.Add(
                        new ItemObject("大于等于>=", ">="));
                    cbOperator1.Properties.Items.Add(
                        new ItemObject("小于等于<=", "<="));
                    cbOperator1.Properties.Items.Add(
                        new ItemObject("不等于<>", "<>"));
                    if (allowLike)
                        cbOperator1.Properties.Items.Add(
                            new ItemObject("匹配字符like", "like"));

                    cbOperator1.SelectedIndex = 0;
                }
            }
        }

        private void OnSelectedIndexChanged(object sender, EventArgs e)
        {
            //cbOperator
            var index = 0;
            var combo = (ComboBoxEdit)sender;
            if (combo == cbFields1) index = 1;

            var item = (ItemObject)combo.SelectedItem;
            if (item != null)
            {
                var type = (Type)item.Tag;
                var field = item.Value.ToStringEx().Trim();

                if (type == typeof(DateTime))
                {
                    if (index == 0) SetValueEditor(txtDate, index);

                    if (index == 1) SetValueEditor(txtDate1, index);

                    LoadOperators(false, index);
                }
                else if (type == typeof(string) || type == typeof(char))
                {
                    Control valueControl = null;
                    if (field.ToUpper().IndexOf("FLAG") == 0)
                    {
                        if (index == 0)
                            valueControl = txtFlag;
                        else
                            valueControl = txtFlag1;
                    }

                    else
                    {
                        if (index == 0)
                            valueControl = txtValue;
                        else
                            valueControl = txtValue1;
                    }

                    SetValueEditor(valueControl, index);
                    LoadOperators(true, index);
                }
                else if (IsNum(type))
                {
                    if (index == 0) SetValueEditor(txtValue, index);

                    if (index == 1) SetValueEditor(txtValue1, index);

                    LoadOperators(false, index);
                }
                else
                {
                    pbAlarm.Visible = true;
                }
            }

            if (index == 0)
                if (txtValue.CanFocus)
                    txtValue.Focus();

            if (index == 1)
                if (txtValue1.CanFocus)
                    txtValue1.Focus();
        }

        private void SetValueEditor(Control editor, int index)
        {
            foreach (Control ctl in Controls)
            {
                if (ctl.Tag == null) continue;

                if (ctl.Tag.ToStringEx() != "100") continue;

                if (ctl != _ValueControl1 && ctl != _ValueControl2)
                    ctl.Visible = false;
            }

            if (index == 0)
            {
                _ValueControl1.Visible = false;
                _ValueControl1 = editor;
                _ValueControl1.Location = txtValue.Location;
                _ValueControl1.Width = txtValue.Width;
                _ValueControl1.Visible = true;
            }
            else if (index == 1)
            {
                _ValueControl2.Visible = false;
                _ValueControl2 = editor;
                _ValueControl2.Location = txtValue1.Location;
                _ValueControl2.Width = txtValue1.Width;
                _ValueControl2.Visible = true;
            }

            pbAlarm.Visible = false;
            lbAlarm.Visible = false;
        }

        //自定义对象
        private class FieldItem : ItemObject
        {
            private string _TableAlias;

            public FieldItem(string keyName, object value)
                : base(keyName, value)
            {
                _Tag = null;
            }

            public string TableAlias
            {
                get => _TableAlias;
                set => _TableAlias = value;
            }
        }
    }
}