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