/// <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(""); }