private static string BuildConditionItemByStatisticsColumn(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv)
{
switch (_cItem.Column.ColumnDataType.ToUpper())
{
case "DATE": //日期型
case "DATETIME":
case "TIMESTAMP":
return(BuildDateFieldQueryCondition(_cItem));
case "NUMBER": //数值型
case "INT":
case "TINYINT":
return(BuildNumberFieldCondition(_cItem, _qv));
case "CHAR":
case "VARCHAR":
case "NVARCHAR":
case "NVARCHAR2":
case "VARCHAR2":
return(BuildCharFieldCondition(_cItem, _qv));
default:
throw new Exception(string.Format("以{0}类型字段做条件的查询功能尚未实现.", _cItem.Column.ColumnDataType));
}
}
private static string BuildConditionItemByCalculationColumn(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv)
{
switch (_cItem.Column.ColumnDataType.ToUpper())
{
case "DATE": //日期型
return(BuildDateFieldQueryCondition(_cItem));
break;
case "NUMBER": //数值型
return(BuildNumberFieldCondition(_cItem, _qv));
break;
case "CHAR":
case "VACHAR":
case "NVARCHAR":
case "NVARCHAR2":
case "VARCHAR2":
return(BuildCharFieldCondition(_cItem, _qv));
break;
default:
throw new Exception(string.Format("以{0}类型字段做条件的查询功能尚未实现.", _cItem.Column.ColumnDataType));
break;
}
return("");
}
public override void SetInputValue(MDQuery_ConditionItem _columnCodition)
{
this.teOption.EditValue = _columnCodition.Operator;
this.CaseSensitive = _columnCodition.CaseSensitive;
this.te_xh.Text = _columnCodition.ColumnIndex;
refCodeBox1.SetValues(_columnCodition.Values);
}
public SinoSZUC_ConditionItem(MDQuery_ConditionItem _citem)
{
InitializeComponent();
ColumnDefine = null;
ShowValue(_citem);
InitFinished = true;
}
public void AddCondition(MDQuery_ConditionItem _tc)
{
SinoSZUC_ConditionItem _item = new SinoSZUC_ConditionItem(_tc);
_item.Dock = DockStyle.Top;
this.xtraScrollableControl1.Controls.Add(_item);
}
public void AddConditonItem(MDQuery_ConditionItem _conditionItem)
{
if (this.queryRequest.ConditionItems == null)
{
this.queryRequest.ConditionItems = new List <MDQuery_ConditionItem>();
}
this.queryRequest.ConditionItems.Add(_conditionItem);
}
public void InsertConditions2QueryRequest(MC_QueryRequsetFactory _queryRequestFactory)
{
_queryRequestFactory.AddExpression(this.textEdit1.EditValue.ToString());
foreach (SinoSZUC_ConditionItem _c in this.xtraScrollableControl1.Controls)
{
MDQuery_ConditionItem _cItem = _c.GetConditionItem();
_queryRequestFactory.AddConditonItem(_cItem);
}
}
/// <summary>
/// 构建日期型字段条件的表达式
/// </summary>
/// <param name="_cItem"></param>
/// <returns></returns>
private static string BuildDateFieldConditon_Table(MDQuery_ConditionItem _cItem, string _fieldStr)
{
string DateFirst = string.Format("to_date('{0}','YYYY-MM-DD')", _cItem.Values[0]);
string DateEnd = string.Format("to_date('{0}235959','YYYY-MM-DDhh24miss')", _cItem.Values[0]);
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} between {1} and {2} ", _fieldStr, DateFirst, DateEnd));
case "时间段":
#region 处理时间段
string enddate = string.Format("to_date('{0}235959','YYYY-MM-DDhh24miss')", _cItem.Values[1]);
return(string.Format(" {0} between {1} and {2} ", _fieldStr, DateFirst, enddate));
#endregion
case "集合":
#region 处理时间集合
string _collectionStr = string.Format(" {0} in (", _fieldStr);
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format("{0}to_date('{1}','YYYY-MM-DD')", _fg, _dateStr);
_fg = ",";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "不等于":
return(string.Format("{0} not between {1} and {2} ", _fieldStr, DateFirst, DateEnd));
case "大于":
return(string.Format("{0} > {1} ", _fieldStr, DateEnd));
case "大于等于":
return(string.Format("{0} >= {1} ", _fieldStr, DateFirst));
case "小于":
return(string.Format("{0} < {1} ", _fieldStr, DateFirst));
case "小于等于":
return(string.Format("{0} <= {1} ", _fieldStr, DateEnd));
break;
case "为空值":
return(string.Format(" {0} is null ", _fieldStr));
case "为非空值":
return(string.Format(" {0} is not null ", _fieldStr));
}
return("");
}
private static string BuildCharFieldCondition_Method(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv, string _fieldStr)
{
MDModel_Table _table;
string _tname = _cItem.Column.TableName;
if (_qv.MainTable.TableName == _tname)
{
_table = _qv.MainTable;
}
else
{
_table = (from _c in _qv.ChildTables
where _c.TableName == _tname
select _c).First();
}
//普通字符型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} ='{1}' ", _fieldStr, _cItem.Values[0]));
case "不等于":
return(string.Format("{0} <>'{1}' ", _fieldStr, _cItem.Values[0]));
case "包含":
return(string.Format("{0} like '%{1}%' ", _fieldStr, _cItem.Values[0]));
case "匹配":
return(string.Format("{0} like '{1}' ", _fieldStr, _cItem.Values[0]));
case "集合":
#region 处理集合
string _collectionStr = string.Format(" {0} in (", _fieldStr);
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format("{0}'{1}'", _fg, _dateStr);
_fg = ",";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "为空值":
return(string.Format(" {0} is null ", _fieldStr));
case "为非空值":
return(string.Format(" {0} is not null ", _fieldStr));
}
return("");
}
/// <summary>
/// 取条件项字符串
/// </summary>
/// <param name="_qv"></param>
/// <param name="_queryRequest"></param>
/// <param name="_itemIndex"></param>
/// <returns></returns>
private static string GetConditionStr(MDModel_QueryModel _qv, MDQuery_Request _queryRequest, string _itemIndex)
{
MDQuery_ConditionItem _cItem = _queryRequest.GetConditionItemByIndex(_itemIndex);
if (_cItem == null)
{
throw new Exception(string.Format("缺少编号为{0}的条件项!", _itemIndex));
}
else
{
return(SQLConditionItemBuilder.BuildConditionItemString(_cItem, _qv));
}
}
/// <summary>
/// 构建数值型字段条件的表达式
/// </summary>
/// <param name="_cItem"></param>
/// <returns></returns>
private static string BuildNumberFieldCondition(MDQuery_ConditionItem _cItem)
{
string _fieldStr = string.Format("{0}.{1} ", _cItem.Column.TableName, _cItem.Column.ColumnName);
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} = {1} ", _fieldStr, _cItem.Values[0]));
case "不等于":
return(string.Format("{0} <> {1} ", _fieldStr, _cItem.Values[0]));
case "大于":
return(string.Format("{0} > {1} ", _fieldStr, _cItem.Values[0]));
case "大于等于":
return(string.Format("{0} >= {1} ", _fieldStr, _cItem.Values[0]));
case "小于":
return(string.Format("{0} < {1} ", _fieldStr, _cItem.Values[0]));
case "小于等于":
return(string.Format("{0} <= {1} ", _fieldStr, _cItem.Values[0]));
break;
case "集合":
#region 处理集合
string _collectionStr = " (";
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format(" {2} {0}={1} ", _fieldStr, _dateStr, _fg);
_fg = " or ";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "范围":
return(string.Format("{0}>= {1} and {0}<={2} ", _fieldStr, _cItem.Values[0], _cItem.Values[1]));
case "为空值":
return(string.Format(" {0} is null ", _fieldStr));
case "为非空值":
return(string.Format(" {0} is not null ", _fieldStr));
}
return("");
}
/// <summary>
/// 取条件项字符串
/// </summary>
/// <param name="_qv"></param>
/// <param name="_queryRequest"></param>
/// <param name="_itemIndex"></param>
/// <returns></returns>
private static string GetConditionStr(MDModel_QueryModel _qv, MDQuery_Request _queryRequest, string _itemIndex)
{
var _find = from _c in _queryRequest.ConditionItems
where _c.ColumnIndex == _itemIndex
select _c;
if (_find == null || _find.Count() < 1)
{
throw new Exception(string.Format("缺少编号为{0}的条件项!", _itemIndex));
}
else
{
MDQuery_ConditionItem _cItem = _find.First();
return(MySqlConditionItemBuilder.BuildConditionItemString(_cItem, _qv));
}
}
public static string BuildConditionItemString(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv)
{
switch (_cItem.Column.ColumnType)
{
case QueryColumnType.TableColumn:
return(BuildConditionItemByTableColumn(_cItem, _qv));
case QueryColumnType.CalculationColumn:
return(BuildConditionItemByCalculationColumn(_cItem, _qv));
case QueryColumnType.StatisticsColumn:
return(BuildConditionItemByStatisticsColumn(_cItem, _qv));
}
return("");
}
private void ShowValue(MDQuery_ConditionItem _citem)
{
this.teColName.EditValue = _citem.Column.ColumnTitle;
this.teOption.EditValue = _citem.Operator;
string _valueStr = "";
string _fg = "";
foreach (string _s in _citem.Values)
{
_valueStr += string.Format("{0}{1}", _fg, _s);
_fg = ",";
}
this.te_Value.EditValue = _valueStr;
this.te_Value.Properties.ReadOnly = true;
this.te_xh.EditValue = _citem.ColumnIndex;
}
/// <summary>
/// 构建查询条件项的语句
/// </summary>
/// <param name="_cItem"></param>
/// <param name="_qv"></param>
/// <returns></returns>
internal static string BuildConditionItemString(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv)
{
switch (_cItem.Column.ColumnType)
{
case QueryColumnType.TableColumn:
return(BuildConditionItemByTableColumn(_cItem, _qv));
case QueryColumnType.CalculationColumn:
throw new Exception("以计算项做条件的查询功能尚未实现.");
break;
case QueryColumnType.StatisticsColumn:
throw new Exception("以统计项做条件的查询功能尚未实现.");
break;
}
return("");
}
public static MDQuery_Request RuleToQueryRequest(MD_CheckRule _rule, MDModel_QueryModel _queryModel, MDQuery_Request filter_Request)
{
MDQuery_Request _ret = new MDQuery_Request();
_ret.ChildResultTables = new List <MDQuery_ResultTable>();
_ret.QueryModelName = _rule.QueryModelName;
_ret.ConditionExpressions = StrUtils.GetMetaByName("C_BDS", _rule.MethodDefine);
MDQuery_ResultTable _restable = new MDQuery_ResultTable();
_restable.TableName = _queryModel.MainTable.TableName;
_restable.DisplayTitle = _queryModel.DisplayName;
_restable.Columns = new List <MDQuery_TableColumn>();
_ret.MainResultTable = _restable;
if (_ret.ConditionItems == null)
{
_ret.ConditionItems = new List <MDQuery_ConditionItem>();
}
foreach (string _field in StrUtils.GetMetasByName("C_FIELD", _rule.MethodDefine))
{
string _index = StrUtils.GetMetaByName("MII_INDEX", _field);
MDQuery_ConditionItem _citem = new MDQuery_ConditionItem();
_citem.Operator = StrUtils.GetMetaByName("MII_OPER", _field).TrimStart().TrimEnd();
MDModel_Table_Column _columnDefine = GetColumn(StrUtils.GetMetaByName("MII_FIELD", _field), _queryModel);
_citem.Column = new MDQuery_TableColumn(_columnDefine);
_ret.ConditionItems.Add(_citem);
_citem.Values = GetValues(StrUtils.GetMetaByName("MII_VALUE", _field), (_columnDefine.ColumnRefDMB != ""));
_citem.ColumnIndex = _index;
}
if (filter_Request != null && filter_Request.ConditionItems.Count > 0)
{
int _oldcount = _ret.ConditionItems.Count;
_ret.ConditionExpressions = string.Format("({0})", _ret.ConditionExpressions);
foreach (MDQuery_ConditionItem _fci in filter_Request.ConditionItems)
{
MDQuery_ConditionItem _citem = new MDQuery_ConditionItem();
_citem.Operator = _fci.Operator;
_citem.Column = _fci.Column;
_citem.Values = _fci.Values;
_citem.CaseSensitive = _fci.CaseSensitive;
_citem.ColumnIndex = (int.Parse(_fci.ColumnIndex) + _oldcount).ToString();
_ret.ConditionItems.Add(_citem);
_ret.ConditionExpressions += string.Format("*{0}", _citem.ColumnIndex);
}
}
return(_ret);
}
private static string BuildCharFieldCondition(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv)
{
string _fieldStr;
switch (_cItem.Column.ColumnType)
{
case QueryColumnType.TableColumn:
_fieldStr = string.Format("{0}.{1} ", _cItem.Column.TableName, _cItem.Column.ColumnName);
return(BuildCharFieldCondition_Table(_cItem, _qv, _fieldStr));
case QueryColumnType.CalculationColumn:
case QueryColumnType.StatisticsColumn:
_fieldStr = string.Format("({0}) ", _cItem.Column.ColumnAlgorithm);
return(BuildCharFieldCondition_Method(_cItem, _qv, _fieldStr));
}
return("");
}
public void InsertConditions2QueryRequest(MC_QueryRequsetFactory _queryRequestFactory)
{
string _expression = "";
string _fg = "";
foreach (SinoSZUC_ConditionItem _c in this.xtraScrollableControl1.Controls)
{
string _msg = "";
if (_c.CheckInput(ref _msg))
{
MDQuery_ConditionItem _cItem = _c.GetConditionItem();
_queryRequestFactory.AddConditonItem(_cItem);
_expression += string.Format("{0}{1}", _fg, _cItem.ColumnIndex);
_fg = "*";
}
}
_queryRequestFactory.AddExpression(_expression);
}
private MDQuery_Request CreateZGDQueryRequest(string _dh, DateTime _sdate, DateTime _edate)
{
string _qvName = "HG_GDFS.海关报关单";
MDModel_QueryModel _qv = MetaDataCache.GetQueryModelDefine(_qvName);
MC_QueryRequsetFactory _rf = new MC_QueryRequsetFactory();
_rf.QueryModelName = _qvName;
//加条件
MDQuery_ConditionItem _cItem = new MDQuery_ConditionItem();
_cItem.ColumnIndex = "1";
_cItem.Column = new MDQuery_TableColumn(_qv.MainTable.GetColumnByName("VOYAGE_NO"));
_cItem.Operator = "等于";
_cItem.Values = new List <string>();
_cItem.Values.Add(_dh);
_rf.AddConditonItem(_cItem);
_cItem = new MDQuery_ConditionItem();
_cItem.ColumnIndex = "2";
_cItem.Column = new MDQuery_TableColumn(_qv.MainTable.GetColumnByName("D_DATE"));
_cItem.Operator = "时间段";
_cItem.Values = new List <string>();
_cItem.Values.Add(_sdate.ToString("yyyyMMdd"));
_cItem.Values.Add(_edate.ToString("yyyyMMdd"));
_rf.AddConditonItem(_cItem);
_rf.AddExpression("1*2");
//加结果
_rf.AddResultTable(_qv.MainTable);
foreach (MDModel_Table_Column _tc in _qv.MainTable.Columns)
{
_rf.AddResultTableColumn(_qv.MainTable, _tc);
}
foreach (string _cTName in _qv.ChildTableDict.Keys)
{
MDModel_Table _ctable = _qv.ChildTableDict[_cTName];
_rf.AddResultTable(_ctable);
foreach (MDModel_Table_Column _ccol in _ctable.Columns)
{
_rf.AddResultTableColumn(_ctable, _ccol);
}
}
return(_rf.GetQueryRequest());
}
/// <summary>
/// 取用户输入的查询条件项
/// </summary>
/// <returns></returns>
virtual public MDQuery_ConditionItem GetConditionItem()
{
string _emsg = "";
if (CheckInput(ref _emsg))
{
MDQuery_ConditionItem _ret = new MDQuery_ConditionItem();
_ret.ColumnIndex = this.te_xh.Text;
_ret.Column = new MDQuery_TableColumn(ColumnDefine);
_ret.Operator = GetOperator();
_ret.Values = GetConditionValues();
_ret.CaseSensitive = this.CaseSensitive;
return(_ret);
}
else
{
return(null);
}
}
public void InsertConditions2QueryRequest(MC_CompareRequsetFactory _compareRequestFactory)
{
string _filterExp = (this.textEdit1.EditValue == null) ? "" : this.textEdit1.EditValue.ToString();
_compareRequestFactory.AddExpression(_filterExp);
foreach (SinoSZUC_ConditionItem _c in this.xtraScrollableControl1.Controls)
{
MDQuery_ConditionItem _cItem = _c.GetConditionItem();
_compareRequestFactory.AddConditonItem(_cItem);
}
string _compareExp = (this.textEdit2.EditValue == null) ? "" : this.textEdit2.EditValue.ToString();
_compareRequestFactory.AddCompareExpression(_compareExp);
foreach (SinoSZUC_CompareConditionItem _c in this.xtraScrollableControl2.Controls)
{
MDCompare_ConditionItem _cItem = _c.GetCompareConditionItem();
_compareRequestFactory.AddCompareConditonItem(_cItem);
}
}
virtual public void SetInputValue(MDQuery_ConditionItem _columnCodition)
{
this.teOption.EditValue = _columnCodition.Operator;
this.CaseSensitive = _columnCodition.CaseSensitive;
this.te_xh.Text = _columnCodition.ColumnIndex;
if (_columnCodition.Values.Count > 1)
{
string _valuestr = "";
string _fg = "";
foreach (string _v in _columnCodition.Values)
{
_valuestr += string.Format("{0}{1}", _fg, _v);
_fg = ",";
}
this.te_Value.EditValue = _valuestr;
}
else
{
this.te_Value.EditValue = _columnCodition.Values[0];
}
}
private void AddSavedCondition(MDModel_Table_Column _tc, MDQuery_ConditionItem _columnCodition)
{
SinoSZUC_ConditionItem _item = null;
switch (_tc.ColumnDataType.ToUpper())
{
case "DATE":
_item = new SinoSZUC_ConditionItem_Date(_tc);
break;
case "NUMBER":
_item = new SinoSZUC_ConditionItem_Number(_tc);
break;
default:
if (_tc.ColumnDefine.TableColumn.RefDMB != "")
{
_item = new SinoSZUC_ConditionItem_RefCode(_tc);
}
else
{
_item = new SinoSZUC_ConditionItem(_tc);
}
break;
}
if (_item != null)
{
_item.Dock = DockStyle.Top;
this.xtraScrollableControl1.Controls.Add(_item);
_item.GetFocused += new EventHandler <EventArgs>(_item_GetFocused);
_item.LoseFocused += new EventHandler <EventArgs>(_item_LoseFocused);
_item.RemoveCondition += new EventHandler <EventArgs>(_item_RemoveCondition);
_item.SendToBack();
this.xtraScrollableControl1.ScrollControlIntoView(_item);
_item.SetInputValue(_columnCodition);
}
}
private MDQuery_Request CreateBGDQueryRequest(string _dh)
{
string _qvName = "HG_GDFS.海关报关单";
MDModel_QueryModel _qv = MetaDataCache.GetQueryModelDefine(_qvName);
MC_QueryRequsetFactory _rf = new MC_QueryRequsetFactory();
_rf.QueryModelName = _qvName;
//加条件
MDQuery_ConditionItem _cItem = new MDQuery_ConditionItem();
_cItem.ColumnIndex = "1";
_cItem.Column = new MDQuery_TableColumn(_qv.MainTable.GetColumnByName("ENTRY_ID_14"));
_cItem.Operator = "等于";
_cItem.Values = new List <string>();
foreach (string _s in _dh.Split(','))
{
_cItem.Values.Add(_s);
}
_rf.AddConditonItem(_cItem);
_rf.AddExpression("1");
//加结果
_rf.AddResultTable(_qv.MainTable);
foreach (MDModel_Table_Column _tc in _qv.MainTable.Columns)
{
_rf.AddResultTableColumn(_qv.MainTable, _tc);
}
foreach (string _cTName in _qv.ChildTableDict.Keys)
{
MDModel_Table _ctable = _qv.ChildTableDict[_cTName];
_rf.AddResultTable(_ctable);
foreach (MDModel_Table_Column _ccol in _ctable.Columns)
{
_rf.AddResultTableColumn(_ctable, _ccol);
}
}
return(_rf.GetQueryRequest());
}
private MDQuery_Request CreateBGDQueryRequest2(DateTime I_E_Date, string _IEFlagNum, string _formID)
{
string _qvName = "HG_GDFS.海关报关单";
MDModel_QueryModel _qv = MetaDataCache.GetQueryModelDefine(_qvName);
MC_QueryRequsetFactory _rf = new MC_QueryRequsetFactory();
_rf.QueryModelName = _qvName;
//加条件 D_Date> I_E_DATE-半年
MDQuery_ConditionItem _cItem_startDate = new MDQuery_ConditionItem();
_cItem_startDate.ColumnIndex = "1";
_cItem_startDate.Column = new MDQuery_TableColumn(_qv.MainTable.GetColumnByName("D_DATE"));
_cItem_startDate.Operator = "大于";
_cItem_startDate.Values = new List <string>();
DateTime _startDate = I_E_Date.AddMonths(-6);
_cItem_startDate.Values.Add(_startDate.ToString("yyyyMMdd"));
_rf.AddConditonItem(_cItem_startDate);
//加条件 ENTRY_ID_SHORT = 进出口标志+单证号
MDQuery_ConditionItem _cItem = new MDQuery_ConditionItem();
_cItem.ColumnIndex = "2";
_cItem.Column = new MDQuery_TableColumn(_qv.MainTable.GetColumnByName("ENTRY_ID_SHORT"));
_cItem.Operator = "等于";
_cItem.Values = new List <string>();
_cItem.Values.Add(string.Format("{0}{1}", _IEFlagNum, _formID));
_rf.AddConditonItem(_cItem);
//加条件 D_DATE<I_E_DATE
MDQuery_ConditionItem _cItem_endDate = new MDQuery_ConditionItem();
_cItem_endDate.ColumnIndex = "3";
_cItem_endDate.Column = new MDQuery_TableColumn(_qv.MainTable.GetColumnByName("D_DATE"));
_cItem_endDate.Operator = "小于";
_cItem_endDate.Values = new List <string>();
DateTime _endDate = I_E_Date.AddDays(1);
_cItem_endDate.Values.Add(_endDate.ToString("yyyyMMdd"));
_rf.AddConditonItem(_cItem_endDate);
_rf.AddExpression("1*2*3");
//加结果
_rf.AddResultTable(_qv.MainTable);
foreach (MDModel_Table_Column _tc in _qv.MainTable.Columns)
{
_rf.AddResultTableColumn(_qv.MainTable, _tc);
}
foreach (string _cTName in _qv.ChildTableDict.Keys)
{
MDModel_Table _ctable = _qv.ChildTableDict[_cTName];
_rf.AddResultTable(_ctable);
foreach (MDModel_Table_Column _ccol in _ctable.Columns)
{
_rf.AddResultTableColumn(_ctable, _ccol);
}
}
return(_rf.GetQueryRequest());
}
private static string BuildNumberFieldCondition_Table(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv, string _fieldStr)
{
MDModel_Table _table;
string _tname = _cItem.Column.TableName;
if (_qv.MainTable.TableName == _tname)
{
_table = _qv.MainTable;
}
else
{
_table = (from _c in _qv.ChildTables
where _c.TableName == _tname
select _c).First();
}
MDModel_Table_Column _tc = (from _cd in _table.Columns
where _cd.ColumnName == _cItem.Column.ColumnName
select _cd).First();
if (_tc.ColumnRefDMB != "")
{
#region 代码表型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} = '{1}'", _fieldStr, _cItem.Values[0]));
case "不等于":
return(string.Format("{0} <> '{1}'", _fieldStr, _cItem.Values[0]));
case "属于":
string[] _rtNames = _tc.ColumnRefDMB.Split('.');
string _tName = (_rtNames.Length > 1) ? _rtNames[1] : _rtNames[0];
return(string.Format("{0} in (select dm from jsods.{2} start with dm='{1}' connect by prior DM=FATHERCODE) ", _fieldStr, _cItem.Values[0], _tName));
case "集合":
#region 处理集合
string _collectionStr = string.Format(" {0} in (", _fieldStr);
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format("{0}'{1}'", _fg, _dateStr);
_fg = ",";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "为空值":
return(string.Format("{0} is null ", _fieldStr));
case "为非空值":
return(string.Format("{0} is not null ", _fieldStr));
}
return("");
#endregion
}
else
{
#region 普通数值型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} = {1} ", _fieldStr, _cItem.Values[0]));
case "不等于":
return(string.Format("{0} <> {1} ", _fieldStr, _cItem.Values[0]));
case "大于":
return(string.Format("{0} > {1} ", _fieldStr, _cItem.Values[0]));
case "大于等于":
return(string.Format("{0} >= {1} ", _fieldStr, _cItem.Values[0]));
case "小于":
return(string.Format("{0} < {1} ", _fieldStr, _cItem.Values[0]));
case "小于等于":
return(string.Format("{0} <= {1} ", _fieldStr, _cItem.Values[0]));
case "集合":
#region 处理集合
string _collectionStr = string.Format(" {0} in (", _fieldStr);
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format("{0}{1}", _fg, _dateStr);
_fg = ",";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "范围":
return(string.Format("{0} between {1} and {2} ", _fieldStr, _cItem.Values[0], _cItem.Values[1]));
case "为空值":
return(string.Format(" {0} is null ", _fieldStr));
case "为非空值":
return(string.Format(" {0} is not null ", _fieldStr));
}
#endregion
}
return("");
}
private static string BuildNumberFieldCondition_Method(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv, string _fieldStr)
{
MDModel_Table _table;
string _tname = _cItem.Column.TableName;
if (_qv.MainTable.TableName == _tname)
{
_table = _qv.MainTable;
}
else
{
_table = (from _c in _qv.ChildTables
where _c.TableName == _tname
select _c).First();
}
#region 普通数值型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} = {1} ", _fieldStr, _cItem.Values[0]));
case "不等于":
return(string.Format("{0} <> {1} ", _fieldStr, _cItem.Values[0]));
case "大于":
return(string.Format("{0} > {1} ", _fieldStr, _cItem.Values[0]));
case "大于等于":
return(string.Format("{0} >= {1} ", _fieldStr, _cItem.Values[0]));
case "小于":
return(string.Format("{0} < {1} ", _fieldStr, _cItem.Values[0]));
case "小于等于":
return(string.Format("{0} <= {1} ", _fieldStr, _cItem.Values[0]));
case "集合":
#region 处理集合
string _collectionStr = string.Format(" {0} in (", _fieldStr);
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format("{0}{1}", _fg, _dateStr);
_fg = ",";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "范围":
return(string.Format("{0} between {1} and {2} ", _fieldStr, _cItem.Values[0], _cItem.Values[1]));
case "为空值":
return(string.Format(" {0} is null ", _fieldStr));
case "为非空值":
return(string.Format(" {0} is not null ", _fieldStr));
}
return("");
#endregion
}
private static string BuildCharFieldCondition_Table(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv, string _fieldStr)
{
MDModel_Table _table;
string _tname = _cItem.Column.TableName;
if (_qv.MainTable.TableName == _tname)
{
_table = _qv.MainTable;
}
else
{
_table = (from _c in _qv.ChildTables
where _c.TableName == _tname
select _c).First();
}
MDModel_Table_Column _tc = (from _cd in _table.Columns
where _cd.ColumnName == _cItem.Column.ColumnName
select _cd).First();
if (_tc.ColumnRefDMB != "")
{
//代码表型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} = '{1}'", _fieldStr, _cItem.Values[0]));
case "不等于":
return(string.Format("{0} <> '{1}'", _fieldStr, _cItem.Values[0]));
case "属于":
string[] _rtNames = _tc.ColumnRefDMB.Split('.');
string _tName = (_rtNames.Length > 1) ? _rtNames[1] : _rtNames[0];
return(string.Format("{0} in (select dm from jsods.{2} start with dm='{1}' connect by prior DM=FATHERCODE) ", _fieldStr, _cItem.Values[0], _tName));
case "集合":
#region 处理集合
string _collectionStr = string.Format(" {0} in (", _fieldStr);
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format("{0}'{1}'", _fg, _dateStr);
_fg = ",";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "为空值":
return(string.Format("{0} is null ", _fieldStr));
case "为非空值":
return(string.Format("{0} is not null ", _fieldStr));
}
return("");
}
else
{
//普通字符型
switch (_cItem.Operator)
{
case "等于":
if (_cItem.CaseSensitive)
{
return(string.Format("{0} ='{1}' ", _fieldStr, _cItem.Values[0]));
}
else
{
return(string.Format("upper({0}) ='{1}' ", _fieldStr, _cItem.Values[0].ToUpper()));
}
case "不等于":
if (_cItem.CaseSensitive)
{
return(string.Format("{0} <> '{1}' ", _fieldStr, _cItem.Values[0]));
}
else
{
return(string.Format("upper({0}) <> '{1}' ", _fieldStr, _cItem.Values[0].ToUpper()));
}
case "包含":
if (_cItem.CaseSensitive)
{
return(string.Format("{0} like '%{1}%' ", _fieldStr, _cItem.Values[0]));
}
else
{
return(string.Format("upper({0}) like '%{1}%' ", _fieldStr, _cItem.Values[0].ToUpper()));
}
case "匹配":
if (_cItem.CaseSensitive)
{
return(string.Format("{0} like '{1}' ", _fieldStr, _cItem.Values[0]));
}
else
{
return(string.Format("upper({0}) like '{1}' ", _fieldStr, _cItem.Values[0].ToUpper()));
}
case "集合":
#region 处理集合
string _collectionStr = (_cItem.CaseSensitive) ? string.Format(" {0} in (", _fieldStr)
: string.Format(" upper({0}) in (", _fieldStr);
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
if (_cItem.CaseSensitive)
{
_collectionStr += string.Format("{0}'{1}'", _fg, _dateStr);
}
else
{
_collectionStr += string.Format("{0}'{1}'", _fg, _dateStr.ToUpper());
}
_fg = ",";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "为空值":
return(string.Format(" {0} is null ", _fieldStr));
case "为非空值":
return(string.Format(" {0} is not null ", _fieldStr));
}
return("");
}
}
/// <summary>
/// 构建日期型字段条件的表达式
/// </summary>
/// <param name="_cItem"></param>
/// <returns></returns>
private static string BuildDateFieldConditon(MDQuery_ConditionItem _cItem)
{
string _fieldStr = string.Format("{0}.{1} ", _cItem.Column.TableName, _cItem.Column.ColumnName);
string _year = _cItem.Values[0].Substring(0, 4);
string _month = _cItem.Values[0].Substring(4, 2);
string _day = _cItem.Values[0].Substring(6, 2);
string DateFirst = string.Format(" '{0}-{1}-{2}' ", _year, _month, _day);
string DateEnd = string.Format(" '{0}-{1}-{2} 23:59:59' ", _year, _month, _day);
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} >={1} and {0}<={2} ", _fieldStr, DateFirst, DateEnd));
case "时间段":
#region 处理时间段
string enddate = string.Format(" '{0} 23:59:59'", _cItem.Values[1]);
return(string.Format("{0} >={1} and {0}<={2} ", _fieldStr, DateFirst, enddate));
#endregion
case "集合":
#region 处理时间集合
string _collectionStr = string.Format(" (", _fieldStr);
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
string _sDate = string.Format(" '{0}' ", _dateStr);
string _eDate = string.Format(" '{0} 23:59:59' ", _dateStr);
_collectionStr += string.Format(" {3} ({0} >={1} and {0} <={2}) ", _fieldStr, _sDate, _eDate, _fg);
_fg = " or ";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "不等于":
return(string.Format("{0} <{1} or {0} >{2} ", _fieldStr, DateFirst, DateEnd));
case "大于":
return(string.Format("{0} > {1} ", _fieldStr, DateEnd));
case "大于等于":
return(string.Format("{0} > {1} ", _fieldStr, DateFirst));
case "小于":
return(string.Format("{0} < {1} ", _fieldStr, DateFirst));
case "小于等于":
return(string.Format("{0} < {1} ", _fieldStr, DateEnd));
break;
case "为空值":
return(string.Format(" {0} is null ", _fieldStr));
case "为非空值":
return(string.Format(" {0} is not null ", _fieldStr));
}
return("");
}
/// <summary>
/// 构建字符型字段表达式
/// </summary>
/// <param name="_cItem"></param>
/// <returns></returns>
private static string BuildCharFieldCondition(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv)
{
MDModel_Table _table;
string _fieldStr = string.Format("{0}.{1} ", _cItem.Column.TableName, _cItem.Column.ColumnName);
string _tname = _cItem.Column.TableName;
if (_qv.MainTable.TableName == _tname)
{
_table = _qv.MainTable;
}
else
{
_table = _qv.ChildTableDict[_tname];
}
var _find = from _c in _table.Columns
where _c.ColumnName == _cItem.Column.ColumnName
select _c;
MDModel_Table_Column _tc = _find.First();
if (_tc.ColumnRefDMB != "")
{
//代码表型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} = '{1}'", _fieldStr, _cItem.Values[0]));
break;
case "不等于":
return(string.Format("{0} <> '{1}'", _fieldStr, _cItem.Values[0]));
break;
case "属于":
throw new Exception("代码表型的属于操作未实现!");
break;
case "集合":
#region 处理集合
string _collectionStr = " (";
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format(" {2} {0}='{1}' ", _fieldStr, _dateStr, _fg);
_fg = " or ";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
break;
case "为空值":
return(string.Format("{0} is null ", _fieldStr));
break;
case "为非空值":
return(string.Format("{0} is not null ", _fieldStr));
break;
}
return("");
}
else
{
//普通字符型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} ='{1}' ", _fieldStr, _cItem.Values[0]));
case "不等于":
return(string.Format("{0} <>'{1}' ", _fieldStr, _cItem.Values[0]));
case "包含":
return(string.Format("{0} like '%{1}%' ", _fieldStr, _cItem.Values[0]));
case "匹配":
return(string.Format("{0} like '{1}' ", _fieldStr, _cItem.Values[0]));
case "集合":
#region 处理集合
string _collectionStr = " (";
string _fg = "";
foreach (string _dateStr in _cItem.Values)
{
_collectionStr += string.Format(" {2} {0}='{1}' ", _fieldStr, _dateStr, _fg);
_fg = " or ";
}
_collectionStr += ") ";
return(_collectionStr);
#endregion
case "为空值":
return(string.Format(" {0} is null ", _fieldStr));
case "为非空值":
return(string.Format(" {0} is not null ", _fieldStr));
}
return("");
}
}
