#region
|
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Text;
|
using System.Windows.Forms;
|
using CSFrameworkV5.Business;
|
using CSFrameworkV5.Common;
|
using CSFrameworkV5.Core;
|
using CSFrameworkV5.Interfaces;
|
using CSFrameworkV5.Interfaces.InterfaceModels;
|
using DevExpress.XtraBars;
|
|
#endregion
|
|
namespace CSFrameworkV5.Library
|
{
|
public partial class frmAdvancedFilter : Form
|
{
|
//通用查询接口
|
private IAdvancedSearcher _advancedSearcher;
|
|
//当前操作的配置
|
private MyConfigData _Current;
|
private BarButtonItem btnViewLarge;
|
private BarButtonItem btnViewList;
|
private BarButtonItem btnViewListDetail;
|
private BarButtonItem btnViewSmall;
|
|
/// <summary>
|
/// 私有构造器
|
/// </summary>
|
private frmAdvancedFilter()
|
{
|
InitializeComponent();
|
}
|
|
private void btmToLeft_Click(object sender, EventArgs e)
|
{
|
if (listSelectedFields.SelectedItem == null) return;
|
|
var item = listSelectedFields.SelectedItem as FieldNameDef;
|
|
var source = listSelectedFields.DataSource as List<FieldNameDef>;
|
var target = listFields.DataSource as List<FieldNameDef>;
|
|
target.Add(item);
|
source.Remove(item);
|
|
listSelectedFields.Refresh();
|
listFields.Refresh();
|
}
|
|
private void btnAdd_Click(object sender, EventArgs e)
|
{
|
if (txtFieldList.Text == "" || txtOperator.Text == "")
|
{
|
Msg.Warning("必须选择字段及操作符！");
|
return;
|
}
|
|
if (txtSQL.Text.Trim().Length > 3 &&
|
txtLogicOption.EditValue == null)
|
{
|
Msg.Warning("请选择条件关系！");
|
return;
|
}
|
|
var op = txtOperator.EditValue.ToStringEx();
|
var item = txtFieldList.GetSelectedDataRow() as FieldNameDef;
|
var condition = "";
|
var alias = string.IsNullOrEmpty(_advancedSearcher.TableAliasName)
|
? ""
|
: _advancedSearcher.TableAliasName + ".";
|
|
if (op == "LIKE")
|
{
|
if (item.IsStringType()) //LIKE 关键字仅支持字符类型
|
condition = "(" + alias + item.FieldName + " " + op +
|
" '%" + txtValue.Text + "%')";
|
}
|
else
|
{
|
if (item.IsStringType()) //字符类型，值要加单引号
|
condition = "(" + alias + item.FieldName + " " + op + " '" +
|
txtValue.Text + "')";
|
else
|
condition = "(" + alias + item.FieldName + " " + op +
|
txtValue.Text + ")";
|
}
|
|
if (txtSQL.Text.Trim() == "")
|
txtSQL.Text = condition;
|
else
|
txtSQL.Text = txtSQL.Text + "\r\n " +
|
txtLogicOption.EditValue.ToStringEx() + " " +
|
condition;
|
|
txtFieldList.EditValue = null;
|
txtValue.Text = "";
|
}
|
|
private void btnCancel_Click(object sender, EventArgs e)
|
{
|
Close();
|
}
|
|
private void btnClearEditor_Click(object sender, EventArgs e)
|
{
|
if (Msg.AskQuestion("确定要清除吗?")) ClearEditor();
|
}
|
|
private void btnDelete_Click(object sender, EventArgs e)
|
{
|
if (lvConfig.SelectedItems.Count <= 0) return;
|
|
var configName =
|
(lvConfig.SelectedItems[0].Tag as MyConfigData).Name;
|
|
if (Msg.AskQuestion("确定要删除 " + configName + " 的配置信息吗?"))
|
{
|
//数据库删除
|
var ok = AdvancedFilterTools.Delete(Loginer.CurrentUser.Account,
|
configName);
|
if (ok)
|
{
|
//删除列表项目
|
lvConfig.Items.Remove(lvConfig.SelectedItems[0]);
|
|
ClearEditor();
|
|
//预算为所有可选字段
|
listFields.DataSource = _advancedSearcher.GetFieldList();
|
listSelectedFields.DataSource = new List<FieldNameDef>();
|
|
listFields.Refresh();
|
listSelectedFields.Refresh();
|
|
_Current = null;
|
lblCurrentStyle.Text = "请打开方案!";
|
|
Msg.ShowInformation("删除成功！");
|
}
|
else
|
{
|
Msg.Warning("删除失败！");
|
}
|
}
|
}
|
|
private void btnOK_Click(object sender, EventArgs e)
|
{
|
//一般选项
|
if (xtraTabControl1.SelectedTabPage == xtraTabPage1)
|
{
|
if (!ValidateData()) return;
|
|
_advancedSearcher.WhereSQL = GenerateSqlWhere(); //生成SQL.Where条件
|
_advancedSearcher.TopRows =
|
ConvertEx.ToInt(txtRows.EditValue); //记录数
|
btnOK.Tag = "OK";
|
Close();
|
}
|
else //高级设置
|
{
|
var sb = new StringBuilder(" ORDER BY ");
|
var list = listSelectedFields.DataSource as List<FieldNameDef>;
|
foreach (var F in list)
|
if (list.IndexOf(F) == 0)
|
sb.Append(" " + F.FieldName + " " +
|
(F.OrderBy == 1 ? "ASC" : "DESC"));
|
else
|
sb.Append("," + F.FieldName + " " +
|
(F.OrderBy == 1 ? "ASC" : "DESC"));
|
|
_advancedSearcher.WhereSQL =
|
txtSQL.Text + "\r\n" + sb.ToStringEx();
|
_advancedSearcher.TopRows = int.Parse(txtRows.Text); //记录数
|
btnOK.Tag = "OK";
|
Close();
|
}
|
}
|
|
private void btnOpen_Click(object sender, EventArgs e)
|
{
|
if (lvConfig.SelectedItems.Count <= 0)
|
{
|
Msg.Warning("请选择一个方案！");
|
return;
|
}
|
|
var item = lvConfig.SelectedItems[0];
|
_Current = item.Tag as MyConfigData;
|
lblCurrentStyle.Text = "当前方案：" + _Current.Name;
|
|
txtSQL.Text = _Current.SQL;
|
listFields.DataSource = CopyObject(_Current.AllFields);
|
listSelectedFields.DataSource =
|
CopyObject(_Current.AllSelectedFields);
|
|
//高级设置
|
xtraTabControl1.SelectedTabPage = xtraTabPage2;
|
}
|
|
private void btnSave_Click(object sender, EventArgs e)
|
{
|
if (txtSQL.Text.Trim() == "")
|
{
|
Msg.Warning("您没有配置任何条件！");
|
return;
|
}
|
|
//输入名字
|
var name = frmAdvancedFilterDlg.Execute();
|
if (string.IsNullOrEmpty(name)) return;
|
|
//DB检查是否存在
|
if (AdvancedFilterTools.Exists(Loginer.CurrentUser.Account, name))
|
{
|
Msg.Warning("方案名称重复，不能保存！");
|
return;
|
}
|
|
//构建数据实例
|
var data = new MyConfigData();
|
data.BusinessName = _advancedSearcher.BusinessName;
|
data.CreateDate = DateTime.Now;
|
data.Name = name;
|
data.SQL = txtSQL.Text;
|
data.AllFields =
|
CopyObject(listFields.DataSource as List<FieldNameDef>);
|
data.AllSelectedFields =
|
CopyObject(listSelectedFields.DataSource as List<FieldNameDef>);
|
data.UserID = Loginer.CurrentUser.Account;
|
|
//DB保存数据
|
var ok = AdvancedFilterTools.Post(data);
|
|
if (ok)
|
{
|
//在列表内添加一条记录
|
var item = lvConfig.Items.Add(data.Name, 0);
|
item.SubItems.Add(data.CreateDate.ToStringEx());
|
item.Tag = data;
|
|
//设置当前对象
|
lvConfig.FocusedItem = item;
|
lvConfig.Focus();
|
|
_Current = data;
|
lblCurrentStyle.Text = "当前方案：" + _Current.Name;
|
|
Msg.ShowInformation("保存成功！");
|
}
|
}
|
|
private void btnToRight_Click(object sender, EventArgs e)
|
{
|
if (listFields.SelectedItem == null) return;
|
|
var item = listFields.SelectedItem as FieldNameDef;
|
|
var source = listFields.DataSource as List<FieldNameDef>;
|
var target = listSelectedFields.DataSource as List<FieldNameDef>;
|
|
item.OrderBy = 1;
|
|
target.Add(item);
|
source.Remove(item);
|
|
listSelectedFields.Refresh();
|
listFields.Refresh();
|
}
|
|
private void BtnViewLarge_ItemClick(object sender, ItemClickEventArgs e)
|
{
|
if (e.Item == btnViewLarge) LoadListViewDataSource(View.LargeIcon);
|
|
if (e.Item == btnViewSmall) LoadListViewDataSource(View.SmallIcon);
|
|
if (e.Item == btnViewList) LoadListViewDataSource(View.List);
|
|
if (e.Item == btnViewListDetail)
|
LoadListViewDataSource(View.Details);
|
}
|
|
private void ClearEditor()
|
{
|
txtFieldList.EditValue = null;
|
txtOperator.EditValue = null;
|
txtLogicOption.EditValue = null;
|
txtValue.EditValue = "";
|
txtSQL.EditValue = "";
|
}
|
|
private List<FieldNameDef> CopyObject(List<FieldNameDef> source)
|
{
|
var result = new List<FieldNameDef>();
|
foreach (var o in source) result.Add(o.Copy());
|
|
return result;
|
}
|
|
/// <summary>
|
/// 加载逻辑运算符数据源
|
/// </summary>
|
/// <returns></returns>
|
private DataTable CreateLogicOperatorData()
|
{
|
var dt = new DataTable();
|
dt.Columns.Add("ID", typeof(string));
|
dt.Columns.Add("NAME", typeof(string));
|
|
dt.Rows.Add("", "");
|
dt.Rows.Add("AND", "并且");
|
dt.Rows.Add("OR", "或者");
|
|
return dt;
|
}
|
|
/// <summary>
|
/// 加载运算符数据源
|
/// </summary>
|
/// <returns></returns>
|
private DataTable CreateOperatorData()
|
{
|
var dt = new DataTable();
|
dt.Columns.Add("ID", typeof(string));
|
dt.Columns.Add("NAME", typeof(string));
|
|
dt.Rows.Add("=", "等于");
|
dt.Rows.Add(">", "大于");
|
dt.Rows.Add("<", "小于");
|
dt.Rows.Add(">=", "大于或等于");
|
dt.Rows.Add("<=", "小于或等于");
|
dt.Rows.Add("<>", "不等于");
|
dt.Rows.Add("LIKE", "包含");
|
|
return dt;
|
}
|
|
/// <summary>
|
/// 外部调用通用接口
|
/// </summary>
|
/// <param name="advanceFilter"></param>
|
public static bool Execute(IAdvancedSearcher advancedSearcher)
|
{
|
var form = new frmAdvancedFilter();
|
form.InitUI(advancedSearcher);
|
form.ShowDialog();
|
return form.btnOK.Tag != null;
|
}
|
|
private void frmAdvancedFilter_Load(object sender, EventArgs e)
|
{
|
LoadListButton();
|
|
LoadListViewDataSource(View.LargeIcon);
|
}
|
|
private string GenerateSqlWhere()
|
{
|
var sb = new StringBuilder();
|
|
var data = (List<ItemConfig>)gcWhere.DataSource;
|
var condition = "";
|
var alias = string.IsNullOrEmpty(_advancedSearcher.TableAliasName)
|
? ""
|
: _advancedSearcher.TableAliasName + ".";
|
ItemConfig item;
|
|
for (var i = 0; i <= data.Count - 1; i++)
|
{
|
item = data[i];
|
if (item.Operator == "") continue; //无操作符，不生成SQL
|
|
condition = "";
|
|
if (item.Operator == "LIKE")
|
{
|
if (item.IsStringType) //LIKE 关键字仅支持字符类型
|
condition = "(" + alias + item.FieldName + " " +
|
item.Operator + " '%" + item.Value + "%')";
|
}
|
else
|
{
|
if (item.IsStringType) //字符类型，值要加单引号
|
condition = "(" + alias + item.FieldName + " " +
|
item.Operator + " '" + item.Value + "')";
|
else
|
condition = "(" + alias + item.FieldName + " " +
|
item.Operator + item.Value + ")";
|
}
|
|
if (condition.Trim() != "")
|
{
|
//添加逻辑运算符，最后一项除外
|
if (i > 0 && sb.Length > 0)
|
sb.Append(" " + item.LogicOperator + " ");
|
|
sb.Append(condition);
|
}
|
}
|
|
return sb.ToStringEx();
|
}
|
|
private void InitUI(IAdvancedSearcher advancedSearcher)
|
{
|
_advancedSearcher = advancedSearcher;
|
|
//选择框：返回的记录数
|
txtRows.Properties.Items.Clear();
|
txtRows.Properties.Items.AddRange(new object[]
|
{ 50, 100, 200, 300, 500 });
|
txtRows.EditValue = 200; //预设值
|
|
//表格的Lookup选择组件
|
lueLogic.DisplayMember = "NAME";
|
lueLogic.ValueMember = "ID";
|
lueLogic.DataSource = CreateLogicOperatorData();
|
|
lueOperator.DisplayMember = "NAME";
|
lueOperator.ValueMember = "ID";
|
lueOperator.DataSource = CreateOperatorData();
|
|
//界面的Lookup选择组件
|
txtFieldList.Properties.DataSource =
|
_advancedSearcher.GetFieldList();
|
txtFieldList.Properties.DisplayMember = "Caption";
|
txtFieldList.Properties.ValueMember = "FieldName";
|
|
txtOperator.Properties.DataSource = CreateOperatorData();
|
txtOperator.Properties.DisplayMember = "NAME";
|
txtOperator.Properties.ValueMember = "ID";
|
|
//全部用于排序字段列表
|
listFields.DataSource = _advancedSearcher.GetFieldList();
|
listFields.DisplayMember = "Caption";
|
listFields.ValueMember = "FieldName";
|
|
//选择的排序字段列表
|
listSelectedFields.DataSource = new List<FieldNameDef>();
|
listSelectedFields.DisplayMember = "Caption";
|
listSelectedFields.ValueMember = "FieldName";
|
|
//加载业务功能对应的条件数据源
|
var data = new List<ItemConfig>();
|
|
var list = _advancedSearcher.GetFieldList();
|
foreach (var F in list)
|
{
|
var T = new ItemConfig(F);
|
T.LogicOperator = "AND"; //预算逻辑运算符
|
data.Add(T);
|
}
|
|
gcWhere.DataSource = data;
|
}
|
|
private void listFields_DoubleClick(object sender, EventArgs e)
|
{
|
//左到右
|
if (listFields.SelectedItem != null)
|
btnToRight_Click(btnToRight, new EventArgs());
|
}
|
|
private void listSelectedFields_Click(object sender, EventArgs e)
|
{
|
if (listSelectedFields.SelectedItem == null) return;
|
|
var item = listSelectedFields.SelectedItem as FieldNameDef;
|
if (item.OrderBy == 1)
|
item.OrderBy = 2;
|
else
|
item.OrderBy = 1;
|
|
listSelectedFields.Refresh();
|
}
|
|
private void listSelectedFields_DoubleClick(object sender, EventArgs e)
|
{
|
//右到左
|
if (listSelectedFields.SelectedItem != null)
|
btmToLeft_Click(btmToLeft, new EventArgs());
|
}
|
|
private void LoadListButton()
|
{
|
btnViewLarge = new BarButtonItem();
|
btnViewSmall = new BarButtonItem();
|
btnViewList = new BarButtonItem();
|
btnViewListDetail = new BarButtonItem();
|
|
//
|
// barButtonItem1
|
//
|
btnViewLarge.Caption = "大型图标";
|
btnViewLarge.Id = 0;
|
btnViewLarge.Name = "barButtonItem1";
|
btnViewLarge.ImageIndex = 0;
|
btnViewLarge.ItemClick += BtnViewLarge_ItemClick;
|
//
|
// barButtonItem2
|
//
|
btnViewSmall.Caption = "小型图标";
|
btnViewSmall.Id = 1;
|
btnViewSmall.Name = "barButtonItem2";
|
btnViewSmall.ImageIndex = 1;
|
btnViewSmall.ItemClick += BtnViewLarge_ItemClick;
|
//
|
// barButtonItem3
|
//
|
btnViewList.Caption = "清单";
|
btnViewList.Id = 2;
|
btnViewList.Name = "barButtonItem3";
|
btnViewList.ImageIndex = 2;
|
btnViewList.ItemClick += BtnViewLarge_ItemClick;
|
//
|
// barButtonItem4
|
//
|
btnViewListDetail.Caption = "详细资料";
|
btnViewListDetail.Id = 3;
|
btnViewListDetail.Name = "barButtonItem4";
|
btnViewListDetail.ImageIndex = 3;
|
btnViewListDetail.ItemClick += BtnViewLarge_ItemClick;
|
|
popupMenu1.LinksPersistInfo.AddRange(new[]
|
{
|
new LinkPersistInfo(btnViewLarge),
|
new LinkPersistInfo(btnViewSmall),
|
new LinkPersistInfo(btnViewList),
|
new LinkPersistInfo(btnViewListDetail)
|
});
|
|
barManager1.Items.AddRange(new BarItem[]
|
{
|
btnViewLarge,
|
btnViewSmall,
|
btnViewList,
|
btnViewListDetail
|
});
|
}
|
|
private void LoadListViewDataSource(View viewStyle)
|
{
|
// 获取我的配置
|
var list =
|
AdvancedFilterTools.GetConfig(Loginer.CurrentUser.Account);
|
|
lvConfig.Items.Clear();
|
lvConfig.Columns.Clear();
|
lvConfig.Columns.Add("名称", 100);
|
lvConfig.Columns.Add("日期", 200);
|
|
foreach (var R in list)
|
{
|
var item = lvConfig.Items.Add(R.Name, 0);
|
item.SubItems.Add(R.CreateDate.ToStringEx());
|
item.Tag = R;
|
}
|
|
lvConfig.View = viewStyle;
|
}
|
|
/// <summary>
|
/// 校验数据
|
/// </summary>
|
/// <returns></returns>
|
private bool ValidateData()
|
{
|
var data = (List<ItemConfig>)gcWhere.DataSource;
|
for (var i = 0; i <= data.Count - 1; i++)
|
{
|
//检查操作符
|
if (data[i].Operator == "" && data[i].Value.Length > 0)
|
{
|
MessageBox.Show("请选择【操作符】！");
|
gvWhere.FocusedRowHandle = i;
|
return false;
|
}
|
|
data[i].IsValid =
|
data[i].Operator != "" && data[i].Value.Length > 0;
|
|
//检查逻辑运算符
|
if (i > 0 && data[i].IsValid && data[i - 1].IsValid &&
|
data[i - 1].LogicOperator == "")
|
{
|
MessageBox.Show("配置复合查询条件，必须选择【逻辑符】！");
|
gvWhere.FocusedRowHandle = i - 1;
|
return false;
|
}
|
}
|
|
return true;
|
}
|
}
|
}
|
