/// <summary>
/// 建立单结果查询语句
/// </summary>
/// <param name="_qv"></param>
/// <param name="_queryRequest"></param>
/// <returns></returns>
private static string CreateSingleDisplayString(MDModel_QueryModel _qv, MDQuery_Request _queryRequest)
{
StringBuilder _sql = new StringBuilder();
_sql.Append(string.Format("{0}.{1} MAINID", _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey));
foreach (MDQuery_TableColumn _rc in _queryRequest.MainResultTable.Columns)
{
_sql.Append(OraResultItemBuilder.BuildItem(_rc, _qv));
}
if (_queryRequest.ChildResultTables != null)
{
foreach (MDQuery_ResultTable _rResultTable in _queryRequest.ChildResultTables)
{
foreach (MDQuery_TableColumn _rc in _rResultTable.Columns)
{
_sql.Append(OraResultItemBuilder.BuildItem(_rc, _qv));
}
}
}
return(_sql.ToString());
}
/// <summary>
/// 通过字段名从查询模型中取一个表中的字段定义
/// </summary>
/// <param name="_qv"></param>
/// <param name="_tName"></param>
/// <param name="_cName"></param>
/// <returns></returns>
public static MDModel_Table_Column GetColumnDefineByName(MDModel_QueryModel _qv, string _tName, string _cName)
{
MDModel_Table _mt = GetTableDefine(_qv, _tName);
if (_mt == null)
{
return(null);
}
var _find = from _c in _mt.Columns
where _c.ColumnName == _cName
select _c;
if (_find != null && _find.Count() > 0)
{
return(_find.First());
}
else
{
return(null);
}
}
/// <summary>
/// 构建通过用户岗位权限筛选数据的语句
/// </summary>
/// <param name="_qv"></param>
/// <returns></returns>
public static string CreateDataFilterStr(MDModel_QueryModel _qv, string _dwid)
{
string _res = "";
switch (_qv.MainTable.TableDefine.Table.SecretFun.ToUpper())
{
case "AREAID": //通过表的areaid进行权限处理
_res = string.Format(" ({0}.areaid in (select containsid from md_areamap where areaid={1})) and ",
_qv.MainTable.TableDefine.TableName,
_dwid);
break;
}
//权限扩展函数先不实现
//if (_qv.MainTable.TableDefine.Table.ExtSecret != "")
//{
// _res = string.Format("{0} and {1}", ReplaceExtSecret(_qv.MainTable.TableDefine.Table.ExtSecret, _qv.FullQueryModelName), _res);
//}
return(_res);
}
public static Dictionary <string, string> GetQueryStr(MDModel_QueryModel _qv, MDQuery_Request _queryRequest, ref string _mainQueryStr, string _dwid, List <MD_SecretFieldItem> secretFields, string _tempTableName)
{
_mainQueryStr = "";
string _filterStr = CreateDataFilterStr(_qv, _dwid);
bool canQueryOnce = IsOneResultTable(_qv, _queryRequest);
string _conditionStr = CreateConditionString(_qv, _queryRequest);
if (canQueryOnce)
{
//单查询语句
List <string> _usedTableList = GetSingleQueryUsedTable(_qv, _queryRequest);
string _displayStr = CreateSingleDisplayString(_qv, _queryRequest, secretFields);
string _tableStr = CreateTableString(_qv, _usedTableList);
string _tableRelationStr = CreateTableRelationString(_qv, _usedTableList);
_mainQueryStr = string.Format("select DISTINCT {0} from {1} where {4} ( {2} ({3})) ",
_displayStr, _tableStr, _tableRelationStr, _conditionStr, _filterStr);
return(new Dictionary <string, string>());
}
else
{
//多查询语句
List <string> _QueryUsedTableList = GetQueryUsedTable(_qv, _queryRequest);
string _tableStr = CreateTableString(_qv, _QueryUsedTableList);
string _tableRelationStr = CreateTableRelationString(_qv, _QueryUsedTableList);
string _displayStr = string.Format("{0}.{1} MAINID", _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey);
_mainQueryStr = string.Format("select DISTINCT {0} from {1} where {4} ( {2} ({3})) ",
_displayStr, _tableStr, _tableRelationStr, _conditionStr, _filterStr);
Dictionary <string, string> _ret = new Dictionary <string, string>();
_ret.Add(_qv.MainTable.TableName, CreateMainTableResult(_qv, _queryRequest.MainResultTable, secretFields, _tempTableName));
if (_queryRequest.ChildResultTables != null)
{
foreach (MDQuery_ResultTable _rt in _queryRequest.ChildResultTables)
{
_ret.Add(_rt.TableName, CreateChildTableResult(_qv, _rt, secretFields, _tempTableName));
}
}
return(_ret);
}
}
private static bool IsOneResultTable(MDModel_QueryModel _qv, MDQuery_Request _queryRequest)
{
if (_queryRequest.ChildResultTables.Count < 1)
{
return(true);
}
foreach (MDQuery_ResultTable _tb in _queryRequest.ChildResultTables)
{
string _tname = _tb.TableName;
var findResult =
from ctb in _queryRequest.ChildResultTables
where ctb.TableName == _tname
select ctb;
if (!findResult.Any())
{
throw new Exception(string.Format("构建查询字符串时发出错误，不存在子表{0}", _tname));
}
}
return(false);
}
/// <summary>
/// 取单查询中所有使用到的表
/// </summary>
/// <param name="_qv"></param>
/// <param name="_queryRequest"></param>
/// <returns></returns>
private static List <string> GetSingleQueryUsedTable(MDModel_QueryModel _qv, MDQuery_Request _queryRequest)
{
List <string> _usedTableList = new List <string>();
_usedTableList.Add(_qv.MainTable.TableName);
foreach (MDQuery_ResultTable _table in _queryRequest.ChildResultTables)
{
if (!_usedTableList.Contains(_table.TableName))
{
_usedTableList.Add(_table.TableName);
}
}
foreach (MDQuery_ConditionItem _cItem in _queryRequest.ConditionItems)
{
if (!_usedTableList.Contains(_cItem.Column.TableName))
{
_usedTableList.Add(_cItem.Column.TableName);
}
}
return(_usedTableList);
}
private static List <string> GetSingleQueryUsedTable(MDModel_QueryModel _qv, MDQuery_Request _queryRequest)
{
List <string> _usedTableList = (from _c in _queryRequest.ChildResultTables
select _c.TableName).Distinct().ToList();
if (!_usedTableList.Contains(_qv.MainTable.TableName))
{
_usedTableList.Add(_qv.MainTable.TableName);
}
var _conditionTabls = (from _c in _queryRequest.ConditionItems
select _c.Column.TableName).Distinct();
foreach (string _cItem in _conditionTabls)
{
if (!_usedTableList.Contains(_cItem))
{
_usedTableList.Add(_cItem);
}
}
return(_usedTableList);
}
public static string CreateDataFilterStr(MDModel_QueryModel _qv, SinoRequestUser RequestUser)
{
string _res = "";
if (_qv.MainTable.SecretFun.Trim() != "")
{
_res = string.Format(" {0}({1}.ZHCX_DW,'{2}') = '1' and ",
_qv.MainTable.SecretFun,
_qv.MainTable.TableName,
RequestUser.SinoPost.PostDWDM);
}
else
{
_res = "";
}
if (_qv.MainTable.ExtSecret.Trim() != "")
{
_res = string.Format("{0} and {1}", ReplaceExtSecret(_qv, _qv.MainTable.ExtSecret, RequestUser), _res);
}
return(_res);
}
/// <summary>
/// 检查规则
/// </summary>
/// <param name="_rule"></param>
private void CheckRule(MD_CheckRule _rule, MDModel_QueryModel _queryModel)
{
DataSet _lsdt;
MDQuery_Request _request = MC_CheckRule.RuleToQueryRequest(_rule, _queryModel, this.CheckParam.Request);
if (_request == null)
{
return;
}
using (SinoSZClientBase.MetaDataQueryService.MetaDataQueryServiceClient _rsc = new SinoSZClientBase.MetaDataQueryService.MetaDataQueryServiceClient())
{
_lsdt = _rsc.QueryData(_request);
}
if (_lsdt != null && _lsdt.Tables.Count > 0)
{
DataTable _dt = _lsdt.Tables[0];
if (_dt.Rows.Count > 0)
{
writeRuleCheckRusult(_dt, _rule.RuleName);
}
}
}
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 void QueryTaskID()
{
if (this._queryTask != null)
{
using (MetaDataQueryServiceClient _msc = new MetaDataQueryServiceClient())
{
QueryRequest = _msc.GetQueryTaskRequestContext(this._queryTask.TaskID);
QueryModel = MetaDataCache.GetQueryModelDefine(QueryRequest.QueryModelName);
switch (this._queryTask.TaskType)
{
case "HGSQL":
QueryResultData = _msc.GetTaskQueryResult_DataSet(this._queryTask.TaskID);
MC_QueryModel.CreateDataRelation(this.QueryModel, QueryResultData);
break;
case "LOCALORA":
QueryResultData = _msc.GetTaskQueryResult_ORA(this._queryTask.TaskID);
MC_QueryModel.CreateDataRelation(this.QueryModel, QueryResultData);
break;
}
}
}
}
/// <summary>
/// 构建通过用户岗位权限筛选数据的语句
/// </summary>
/// <param name="_qv"></param>
/// <returns></returns>
public static string CreateDataFilterStr(MDModel_QueryModel _qv)
{
string _res = "";
if (_qv.MainTable.TableDefine.Table.SecretFun != "")
{
_res = string.Format(" {0}({1}.ZHCX_DW,'{2}') = '1' and ",
_qv.MainTable.TableDefine.Table.SecretFun,
_qv.MainTable.TableDefine.TableName,
SinoUserCtx.CurUser.QxszDWDM);
}
else
{
_res = "";
}
if (_qv.MainTable.TableDefine.Table.ExtSecret != "")
{
_res = string.Format("{0} and {1}", ReplaceExtSecret(_qv.MainTable.TableDefine.Table.ExtSecret, _qv.FullQueryModelName), _res);
}
return(_res);
}
/// <summary>
/// 将查询结果集中的表建立表关联
/// </summary>
/// <param name="_ds"></param>
public static void CreateDataRelation(MDModel_QueryModel _qv, DataSet _ds)
{
DataTable _mtable = _ds.Tables[_qv.MainTable.TableName];
_mtable.CaseSensitive = true;
CheckMainKey(_mtable, "MAINID");
_mtable.Columns["MAINID"].Unique = true;
_mtable.PrimaryKey = new DataColumn[] { _mtable.Columns["MAINID"] };
for (int i = 0; i < _ds.Tables.Count; i++)
{
DataTable _dt = _ds.Tables[i];
_dt.CaseSensitive = true;
if (_dt.TableName != _qv.MainTable.TableName)
{
MDModel_Table _ctable = MC_QueryModel.GetTableDefine(_qv, _dt.TableName);
if (_ctable != null)
{
string _rname = _ctable.TableDefine.DisplayTitle;
_ds.Relations.Add(_rname, _mtable.Columns["MAINID"], _dt.Columns["MAINID"]);
}
}
}
}
public static Dictionary <string, string> GetQueryStr(MDModel_QueryModel _qv, MDQuery_Request _queryRequest, ref string _mainQueryStr, SinoRequestUser RequestUser)
{
_mainQueryStr = "";
string _filterStr = CreateDataFilterStr(_qv, RequestUser);
bool canQueryOnce = IsOneResultTable(_qv, _queryRequest);
string _conditionStr = CreateConditionString(_qv, _queryRequest);
if (canQueryOnce)
{
//单查询语句
List <string> _usedTableList = GetSingleQueryUsedTable(_qv, _queryRequest);
string _displayStr = CreateSingleDisplayString(_qv, _queryRequest, RequestUser);
string _tableStr = CreateTableString(_qv, _usedTableList);
string _tableRelationStr = CreateTableRelationString(_qv, _usedTableList, RequestUser);
_mainQueryStr = string.Format("select * from (select DISTINCT {0} from {1} where {4} ( {2} ({3})) ) where rownum<{5}",
_displayStr, _tableStr, _tableRelationStr, _conditionStr, _filterStr, QueryMaxReturnRows);
return(new Dictionary <string, string>());
}
else
{
//多查询语句
List <string> _QueryUsedTableList = GetQueryUsedTable(_qv, _queryRequest);
string _tableStr = CreateTableString(_qv, _QueryUsedTableList);
string _tableRelationStr = CreateTableRelationString(_qv, _QueryUsedTableList, RequestUser);
string _displayStr = string.Format("{0}.{1} MAINID", _qv.MainTable.TableName, _qv.MainTable.MainKey);
_mainQueryStr = string.Format("select * from (select {0} from {1} where {4} ( {2} ({3})) ) where rownum<{5} ",
_displayStr, _tableStr, _tableRelationStr, _conditionStr, _filterStr, QueryMaxReturnRows);
Dictionary <string, string> _ret = new Dictionary <string, string>();
_ret.Add(_qv.MainTable.TableName, CreateMainTableResult(_qv, _queryRequest.MainResultTable, RequestUser));
foreach (MDQuery_ResultTable _rt in _queryRequest.ChildResultTables)
{
_ret.Add(_rt.TableName, CreateChildTableResult(_qv, _rt, RequestUser));
}
return(_ret);
}
}
/// <summary>
/// 以指定范围数据
/// </summary>
/// <param name="_qv"></param>
/// <param name="_rangeDWID"></param>
/// <returns></returns>
private static string CreateDataFilterStrWithRange(MDModel_QueryModel _qv, string _rangeDWDM)
{
string _res = "";
string _rangeStr = "";
if (_qv.MainTable.TableDefine.Table.SecretFun != "")
{
//if (SinoUserCtx.CurUser.QxszDWDM == _rangeDWDM)
//{
// _res = string.Format(" {0}({1}.ZHCX_DW,'{2}') = '1' and ",
// _qv.MainTable.TableDefine.Table.SecretFun,
// _qv.MainTable.TableDefine.TableName,
// SinoUserCtx.CurUser.QxszDWDM);
//}
//else
//{
// _res = string.Format(" ({0}({1}.ZHCX_DW,'{2}') = '1' and {0}({1}.ZHCX_DW,'{3}') = '1' ) and ",
// _qv.MainTable.TableDefine.Table.SecretFun,
// _qv.MainTable.TableDefine.TableName,
// SinoUserCtx.CurUser.QxszDWDM,
// _rangeDWDM);
//}
}
else
{
_res = "";
}
if (_qv.MainTable.TableDefine.Table.ExtSecret != "")
{
_res = string.Format("{0} and {1}", ReplaceExtSecret(_qv.MainTable.TableDefine.Table.ExtSecret, _qv.FullQueryModelName), _res);
}
return(_res);
}
private void InitForm()
{
_queryModel = MetaDataCache.GetQueryModelDefine(this.QueryModelName);
this.sinoSZUC_FixConditionPanel1.ShowConditionItems(_queryModel);
this.checkedListBoxControl1.Items.Clear();
foreach (MDModel_Table _cTable in _queryModel.ChildTableDict.Values)
{
bool _canShow = false;
foreach (MDModel_Table_Column _tc in _cTable.Columns)
{
if (_tc.ColumnDefine.DefaultResult)
{
_canShow = true;
}
}
if (_canShow)
{
FixChildItem _item = new FixChildItem(_cTable.TableDefine.DisplayTitle, _cTable);
CheckedListBoxItem _cb = new CheckedListBoxItem();
_cb.Value = _item;
this.checkedListBoxControl1.Items.Add(_cb);
}
}
}
private static string BuildConditionItemByTableColumn(MDQuery_ConditionItem _cItem, MDModel_QueryModel _qv)
{
switch (_cItem.Column.ColumnDataType.ToUpper())
{
case "DATE": //日期型
return(BuildDateFieldQueryCondition(_cItem));
case "NUMBER": //数值型
return(BuildNumberFieldCondition(_cItem, _qv));
case "CHAR":
case "VACHAR":
case "NVARCHAR":
case "NVARCHAR2":
case "VARCHAR2":
case "代码表":
return(BuildCharFieldCondition(_cItem, _qv));
default:
throw new Exception(string.Format("以{0}类型字段做条件的查询功能尚未实现.", _cItem.Column.ColumnDataType));
}
}
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 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(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(BuildNumberFieldCondition_Table(_cItem, _qv, _fieldStr));
case QueryColumnType.CalculationColumn:
case QueryColumnType.StatisticsColumn:
_fieldStr = string.Format("({0}) ", _cItem.Column.ColumnAlgorithm);
return(BuildNumberFieldCondition_Method(_cItem, _qv, _fieldStr));
}
return("");
}
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("");
}
}
private static string CreateConditionStringByMainID(MDModel_QueryModel _qv)
{
string _str = string.Format("{0}.{1} in (select PK_C2 from COMP_TEMP) ", _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey);
return(_str);
}
public static SinoSZ_TableRelationCompose Compse(MDQuery_Request _srcRequest, MDModel_QueryModel _srcModel, DataTable _srcChildTable)
{
SinoSZ_TableRelationCompose _ret = new SinoSZ_TableRelationCompose();
#region 处理查询结果字段
_ret.QueryRequest = new MDQuery_Request();
_ret.ResultData = new DataTable();
_ret.ResultData.Columns.Add(new DataColumn("MAINID", typeof(String)));
MDQuery_ResultTable _qrDefine = new MDQuery_ResultTable();
_qrDefine.TableName = _srcRequest.MainResultTable.TableName;
_qrDefine.TableName = _srcRequest.MainResultTable.DisplayTitle;
_qrDefine.Columns = new List <MDQuery_TableColumn>();
//添加所有主表字段
Dictionary <string, string> _aliasLib = new Dictionary <string, string>();
int _index = 0;
DataTable _mainTable = _srcChildTable.DataSet.Tables[_srcRequest.MainResultTable.TableName];
foreach (MDQuery_TableColumn _col in _srcRequest.MainResultTable.Columns)
{
MDQuery_TableColumn _newcol = CopyResultTableColumn(_col);
_newcol.ColumnAlias = string.Format("F{0}", _index++);
_aliasLib.Add(string.Format("{0}.{1}", _col.TableName, _col.ColumnAlias), _newcol.ColumnAlias);
_qrDefine.Columns.Add(_newcol);
DataColumn _dc = _mainTable.Columns[_col.ColumnAlias];
DataColumn _newdc = new DataColumn(_newcol.ColumnAlias, _dc.DataType);
_newdc.Caption = _dc.Caption;
_ret.ResultData.Columns.Add(_newdc);
}
//添加所有子表字段
var _find = from _t in _srcRequest.ChildResultTables
where _t.TableName == _srcChildTable.TableName
select _t;
if (_find != null && _find.Count() > 0)
{
MDQuery_ResultTable _CResultTable = _find.First();
foreach (MDQuery_TableColumn _col in _CResultTable.Columns)
{
MDQuery_TableColumn _newcol = CopyResultTableColumn(_col);
_newcol.ColumnAlias = string.Format("F{0}", _index++);
_aliasLib.Add(string.Format("{0}.{1}", _col.TableName, _col.ColumnAlias), _newcol.ColumnAlias);
_qrDefine.Columns.Add(_newcol);
DataColumn _dc = _srcChildTable.Columns[_col.ColumnAlias];
DataColumn _newdc = new DataColumn(_newcol.ColumnAlias, _dc.DataType);
_newdc.Caption = _dc.Caption;
_ret.ResultData.Columns.Add(_newdc);
}
}
else
{
foreach (DataColumn _dc in _srcChildTable.Columns)
{
if (_dc.ColumnName != "MAINID")
{
MDQuery_TableColumn _newcol = new MDQuery_TableColumn();
_newcol.ColumnAlias = _dc.ColumnName;
_newcol.ColumnTitle = "EXCEL_" + _dc.ColumnName;
_aliasLib.Add(string.Format("{0}.{1}", _srcChildTable.TableName, _dc.ColumnName), _dc.ColumnName);
_qrDefine.Columns.Add(_newcol);
DataColumn _newdc = new DataColumn(_dc.ColumnName, _dc.DataType);
_newdc.Caption = _dc.Caption;
_ret.ResultData.Columns.Add(_newdc);
}
}
}
_ret.QueryRequest.MainResultTable = _qrDefine;
#endregion
#region 处理元数据定义字段
#endregion
#region 处理数据表
//添加数据
foreach (DataRow _dr in _srcChildTable.Rows)
{
DataRow _newrow = _ret.ResultData.NewRow();
foreach (DataColumn _dc in _srcChildTable.Columns)
{
if (_dc.ColumnName != "MAINID")
{
string _newFieldName = _aliasLib[string.Format("{0}.{1}", _dc.Table.TableName, _dc.ColumnName)];
_newrow[_newFieldName] = _dr[_dc.ColumnName];
}
}
DataRow _fatherRow = _dr.GetParentRow(_srcChildTable.ParentRelations[0].RelationName);
foreach (DataColumn _dc in _fatherRow.Table.Columns)
{
if (_dc.ColumnName == "MAINID")
{
_newrow["MAINID"] = _fatherRow[_dc.ColumnName];
}
else
{
string _newFieldName = _aliasLib[string.Format("{0}.{1}", _dc.Table.TableName, _dc.ColumnName)];
_newrow[_newFieldName] = _fatherRow[_dc.ColumnName];
}
}
_ret.ResultData.Rows.Add(_newrow);
}
#endregion
return(_ret);
}
public void Init(string _title, string _menuName, string _mainid, MDQuery_Request _request, MDModel_QueryModel _queryModel, DataRow _dr)
{
QueryModel = _queryModel;
QueryRequest = _request;
MainKeyID = _mainid;
CurrentRow = _dr;
this.Text = _title;
this._menuPageName = _menuName;
InitForm();
this._initFinished = true;
RaiseMenuChanged();
}
private static string CreateCompareTableString(MDModel_QueryModel _qv, List <string> CompareUsedTableList)
{
string _a1 = OraQueryBuilder.CreateTableString(_qv, CompareUsedTableList);
return(string.Format("{0},TEMP_IMPDATA", _a1));
}
private static string BuildCharFieldCondition_Table(MDCompare_ConditionItem _cItem, MDModel_QueryModel _qv, string _fieldStr)
{
MDModel_Table _table;
string _targetField = string.Format("TEMP_IMPDATA.{0}", _cItem.CompareTagetField);
string _tname = _cItem.Column.TableName;
if (_qv.MainTable.TableName == _tname)
{
_table = _qv.MainTable;
}
else
{
_table = _qv.ChildTableDict[_tname];
}
MDModel_Table_Column _tc = _table.GetColumnByName(_cItem.Column.ColumnName);
//普通字符型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("{0} ={1}", _fieldStr, _targetField));
case "近似":
return(string.Format("{0} = {1} ", _fieldStr, _targetField));
}
return("");
}
public static SinoSZ_TableRelationCompose CompareResultFullCompse(MDCompare_Request _Request, MDModel_QueryModel mDModel_QueryModel, DataSet CompareResult)
{
SinoSZ_TableRelationCompose _ret = new SinoSZ_TableRelationCompose();
#region 处理查询结果字段
_ret.QueryRequest = _Request as MDQuery_Request;
_ret.ResultData = new DataTable();
_ret.ResultData.Columns.Add(new DataColumn("MAINID", typeof(String)));
#endregion
#region 处理数据表
#endregion
return(_ret);
}
/// <summary>
/// 构建数值型字段条件的表达式
/// </summary>
/// <param name="_cItem"></param>
/// <returns></returns>
private static string BuildNumberFieldCondition(MDCompare_ConditionItem _cItem, MDModel_QueryModel _qv)
{
MDModel_Table _table;
string _targetField = string.Format("TEMP_IMPDATA.{0}", _cItem.CompareTagetField);
string _fieldStr = "";
switch (_cItem.Column.ColumnType)
{
case QueryColumnType.TableColumn:
_fieldStr = string.Format("{0}.{1} ", _cItem.Column.TableName, _cItem.Column.ColumnName);
break;
}
string _tname = _cItem.Column.TableName;
if (_qv.MainTable.TableName == _tname)
{
_table = _qv.MainTable;
}
else
{
_table = _qv.ChildTableDict[_tname];
}
MDModel_Table_Column _tc = _table.GetColumnByName(_cItem.Column.ColumnName);
#region 普通数值型
switch (_cItem.Operator)
{
case "等于":
return(string.Format("to_char({0}) = {1} ", _fieldStr, _targetField));
}
#endregion
return("");
}
private static string BuildStatisticsColumnResult(MDQuery_TableColumn _rColumn, MDModel_QueryModel _qv)
{
string _itemString = "";
if (_rColumn.ColumnDataType == "NUMBER")
{
_itemString = string.Format("Round( ({0}),20) {1}", _rColumn.ColumnAlgorithm, _rColumn.ColumnAlias);
}
else
{
_itemString = string.Format("({0}) {1}", _rColumn.ColumnAlgorithm, _rColumn.ColumnAlias);
}
return("," + _itemString);
}
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("");
}
