/// <summary> /// 取查询条件中用到的所有表 /// </summary> /// <param name="_qv"></param> /// <param name="_queryRequest"></param> /// <returns></returns> public static List <string> GetQueryUsedTable(MDModel_QueryModel _qv, MDQuery_Request _queryRequest) { List <string> _usedTableList = new List <string>(); _usedTableList.Add(_qv.MainTable.TableName); foreach (MDQuery_ConditionItem _cItem in _queryRequest.ConditionItems) { if (!_usedTableList.Contains(_cItem.Column.TableName)) { _usedTableList.Add(_cItem.Column.TableName); } } return(_usedTableList); }
/// <summary> /// 建立平铺数据表字段 /// </summary> /// <param name="QueryRequest"></param> /// <param name="QueryModel"></param> /// <param name="_ret"></param> private static void CreateFullComboTableColumn(MDQuery_Request QueryRequest, MDModel_QueryModel QueryModel, DataTable _ret) { _ret.Columns.Clear(); MDQuery_ResultTable _mtable = QueryRequest.MainResultTable; //通过表定义建字段 CreateComboTableColumn(QueryModel, _mtable, _ret); _ret.Columns.Add(new DataColumn("ROW_MAINID", typeof(string))); _ret.Columns.Add(new DataColumn("IS_ODD_ROW", typeof(bool))); foreach (MDQuery_ResultTable _ctable in QueryRequest.ChildResultTables) { CreateComboTableColumn(QueryModel, _ctable, _ret); } }
/// <summary> /// 从查询请求中取一个表名 /// </summary> /// <param name="_qr"></param> /// <param name="_tName"></param> /// <returns></returns> private static MDQuery_ResultTable GetResultTableDefine(MDQuery_Request _qr, string _tName) { if (_qr.MainResultTable.TableName == _tName) { return(_qr.MainResultTable); } if (_qr.ChildResultTables.ContainsKey(_tName)) { return(_qr.ChildResultTables[_tName]); } else { return(null); } }
/// <summary> /// 查询结果是否仅有一个表 /// </summary> /// <param name="_queryRequest"></param> /// <returns></returns> private static bool IsOneResultTable(MDModel_QueryModel _qv, MDQuery_Request _queryRequest) { if (_queryRequest.ChildResultTables == null || _queryRequest.ChildResultTables.Count < 1) { return(true); } foreach (MDQuery_ResultTable _table in _queryRequest.ChildResultTables) { if (!_qv.ChildTableDict.ContainsKey(_table.TableName)) { throw new Exception(string.Format("构建查询字符串时发出错误,不存在子表{0}", _table.TableName)); } } return(false); }
/// <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)); } }
private static string CreateFilterStr(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { string _res = ""; MDQuery_Request _queryRequest = compareRequest as MDQuery_Request; List <string> _QueryUsedTableList = OraQueryBuilder.GetQueryUsedTable(_qv, _queryRequest); string _filterStr = OraQueryBuilder.CreateDataFilterStr(_qv); string _conditionStr = OraQueryBuilder.CreateConditionString(_qv, _queryRequest); string _tableStr = OraQueryBuilder.CreateTableString(_qv, _QueryUsedTableList); string _tableRelationStr = OraQueryBuilder.CreateTableRelationString(_qv, _QueryUsedTableList); string _displayStr = string.Format("{0}.{1} MAINID", _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey); _res = string.Format("select DISTINCT {0} from {1} where {4} ( {2} ({3})) ", _displayStr, _tableStr, _tableRelationStr, _conditionStr, _filterStr); return(_res); }
private static string CreateCompareStr(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { string _res = ""; MDQuery_Request _queryRequest = compareRequest as MDQuery_Request; List <string> CompareUsedTableList = GetAllCompareUsedTables(_qv, compareRequest); string _whereStr = CreateCompareWhereStr(_qv, compareRequest); string _displayStr = CreateCompareMainKeyString(_qv, compareRequest); string _tableStr = CreateCompareTableString(_qv, CompareUsedTableList); string _tableRelationStr = OraQueryBuilder.CreateTableRelationString(_qv, CompareUsedTableList); _res = string.Format("select DISTINCT {0} from {1} where {2} ({3}) ", _displayStr, _tableStr, _tableRelationStr, _whereStr); return(_res); }
/// <summary> /// 数据审核 /// </summary> /// <param name="_application"></param> public void CheckData(IApplication _application) { List <MD_CheckRule> _rules; if (FilterDefine == null) { return; } PropertyManager cm_Meta2 = (PropertyManager)this.BindingContext[FilterDefine, ""]; cm_Meta2.EndCurrentEdit(); string _msg = ""; if (!this.sinoSZUC_FixConditionPanel1.IsValid(ref _msg)) { XtraMessageBox.Show(_msg, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } MDQuery_Request _request = GetQueryRequest(); if (this._ruleList != null) { _rules = GetRuleList(); } else { _rules = new List <MD_CheckRule>(); } //if (_rules.Count < 1) //{ // XtraMessageBox.Show("请选择规则!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); // return; //} string _SelectRootDWDM = this.sinoUC_OrgComboBox1.DWDM.ToString(); DC_DataCheckParam _dcParam = new DC_DataCheckParam(_request, _rules, _SelectRootDWDM, FilterDefine); frmDataCheck _frm = MenuFunctions.AddPage <frmDataCheck>(Guid.NewGuid().ToString(), _application); if (_frm != null) { _frm.Init("数据审核", "审核功能", _dcParam); } }
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); }
/// <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(SQLResultItemBuilder.BuildItem(_rc, _qv)); } foreach (MDQuery_ResultTable _rResultTable in _queryRequest.ChildResultTables) { foreach (MDQuery_TableColumn _rc in _rResultTable.Columns) { _sql.Append(SQLResultItemBuilder.BuildItem(_rc, _qv)); } } return(_sql.ToString()); }
public static string GetQueryStrWithWHXH(MDModel_QueryModel _qv, MDQuery_Request _queryRequest, string _rangeDWDM) { string _res = ""; bool canQueryOnce = IsOneResultTable(_qv, _queryRequest); List <MD_SecretFieldItem> _secretFields = new List <MD_SecretFieldItem>(); List <string> _usedTableList = GetQueryUsedTableWithWHXH(_qv, _queryRequest); string _filterStr = CreateDataFilterStrWithRange(_qv, _rangeDWDM); string _displayStr = CreateSingleDisplayString(_qv, _queryRequest, _secretFields); string _conditionStr = CreateConditionString(_qv, _queryRequest); string _tableStr = CreateTableString(_qv, _usedTableList); string _tableRelationStr = CreateTableRelationString(_qv, _usedTableList); string _vn = _queryRequest.QueryModelName.Split('.')[1]; string _ns = _queryRequest.QueryModelName.Split('.')[0]; string _mainKeyTableName = _qv.MainTable.TableName; string _mainKeyColumnName = _qv.MainTable.TableDefine.Table.MainKey; string _shStr = string.Format(" (SELECT SH.SH_FJ FROM SJSH_B SH WHERE SH.VIEWNAME = '{0}' AND SH.NAMESPACE = '{1}' AND TRIM(TO_CHAR({2}.{3}))= SH.SHDXGJZ ) SH_FJ", _vn, _ns, _mainKeyTableName, _mainKeyColumnName); _shStr += string.Format(" ,(SELECT SH.SH_ZHISJ FROM SJSH_B SH WHERE SH.VIEWNAME = '{0}' AND SH.NAMESPACE = '{1}' AND TRIM(TO_CHAR({2}.{3}))= SH.SHDXGJZ ) SH_ZHISJ", _vn, _ns, _mainKeyTableName, _mainKeyColumnName); _shStr += string.Format(" ,(SELECT SH.SH_FSJ FROM SJSH_B SH WHERE SH.VIEWNAME = '{0}' AND SH.NAMESPACE = '{1}' AND TRIM(TO_CHAR({2}.{3}))= SH.SHDXGJZ ) SH_FSJ", _vn, _ns, _mainKeyTableName, _mainKeyColumnName); _shStr += string.Format(" ,(SELECT SH.SH_ZONGSJ FROM SJSH_B SH WHERE SH.VIEWNAME = '{0}' AND SH.NAMESPACE = '{1}' AND TRIM(TO_CHAR({2}.{3}))= SH.SHDXGJZ ) SH_ZONGSJ", _vn, _ns, _mainKeyTableName, _mainKeyColumnName); _shStr += string.Format(",ZHCX_HGJS.Get_ODS_CHANGED('{0}','{1}',{0}.{1}) RKQK", _mainKeyTableName, _mainKeyColumnName); string _whStr = string.Format("(SELECT SHJG.WHXH FROM SJSH_B SH,SJSH_JGJLB SHJG WHERE SH.VIEWNAME = '{0}' AND SH.NAMESPACE = '{1}' AND TRIM(TO_CHAR({2}.{3}))= SH.SHDXGJZ AND SH.ID = SHJG.ID AND SHJG.DWDM = '{4}') OLD_WHXH ", _vn, _ns, _mainKeyTableName, _mainKeyColumnName, _rangeDWDM); string _CurrentWhStr = string.Format("(SELECT WHXHB.WHXH FROM {0}_WHXH WHXHB WHERE {0}.{1} = WHXHB.{1}) CURRENT_WHXH ", _mainKeyTableName, _mainKeyColumnName); if (!canQueryOnce) { _res = string.Format("select DISTINCT {0},{5},{6},{7} from {1} where {4} ( {2} ({3})) ", _displayStr, _tableStr, _tableRelationStr, _conditionStr, _filterStr, _shStr, _whStr, _CurrentWhStr); } else { _res = string.Format("select {0},{5},{6},{7} from {1} where {4} ( {2} ({3})) ", _displayStr, _tableStr, _tableRelationStr, _conditionStr, _filterStr, _shStr, _whStr, _CurrentWhStr); } return(_res); }
private static string GetConditionStr(MDModel_QueryModel _qv, MDQuery_Request _queryRequest, string _itemIndex) { var conditionItem = from item in _queryRequest.ConditionItems where item.ColumnIndex == _itemIndex select item; if (conditionItem.Count() < 1) { throw new Exception(string.Format("缺少编号为{0}的条件项!", _itemIndex)); } else { foreach (MDQuery_ConditionItem _cItem in conditionItem) { return(OraConditionItemBuilder.BuildConditionItemString(_cItem, _qv)); } } return(""); }
/// <summary> /// 从查询请求中取一个表名 /// </summary> /// <param name="_qr"></param> /// <param name="_tName"></param> /// <returns></returns> private static MDQuery_ResultTable GetResultTableDefine(MDQuery_Request _qr, string _tName) { if (_qr.MainResultTable.TableName == _tName) { return(_qr.MainResultTable); } var _find = from _t in _qr.ChildResultTables where _t.TableName == _tName select _t; if (_find != null && _find.Count() > 0) { return(_find.First()); } else { return(null); } }
public void ShowBlankQueryResult(MDQuery_Request _request) { QueryRequest = _request; MDQuery_ResultTable _mtable = QueryRequest.MainResultTable; foreach (MDQuery_TableColumn _rc in _mtable.Columns) { GridColumn _gc = AddColumn(_rc.ColumnTitle, _rc.ColumnAlias, _rc.ColumnDataType, _rc.DisplayFormat, _rc.DisplayLength); _gc.Tag = _rc; } DataSet _tempDataSet = new DataSet(); //加主表 DataTable _mtb = new DataTable(); _tempDataSet.Tables.Add(_mtb); _mtb.Columns.Add("TEMP_MAINID", typeof(string)); DataRow _newrow = _mtb.NewRow(); _newrow["TEMP_MAINID"] = "1"; _mtb.Rows.Add(_newrow); //加子表 foreach (MDQuery_ResultTable _childTD in QueryRequest.ChildResultTables) { DataTable _ctb = new DataTable(); _ctb.Columns.Add("MAINID", typeof(string)); foreach (MDQuery_TableColumn _rc in _childTD.Columns) { _ctb.Columns.Add(_rc.ColumnTitle); } DataRow _cnrow = _ctb.NewRow(); _cnrow["MAINID"] = "1"; _ctb.Rows.Add(_cnrow); _tempDataSet.Tables.Add(_ctb); _tempDataSet.Relations.Add(_childTD.DisplayTitle, _mtb.Columns["TEMP_MAINID"], _ctb.Columns["MAINID"]); } this.gridControl1.DataSource = _mtb; this.gridView1.ExpandMasterRow(0); }
private void QueryData() { string _errorMsg = ""; if (!this.sinoSZUC_FixConditionPanel1.IsValid(ref _errorMsg)) { XtraMessageBox.Show(string.Format("查询条件不正确:{0}", _errorMsg), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MC_QueryRequsetFactory _rf = new MC_QueryRequsetFactory(); _rf.QueryModelName = this.QueryModelName; this.sinoSZUC_FixConditionPanel1.InsertConditions2QueryRequest(_rf); InsertQueryResult(_rf); MDQuery_Request _queryRequest = _rf.GetQueryRequest(); frmSinoSZ_QueryResult _qr = new frmSinoSZ_QueryResult(_queryModel, _queryRequest); _application.AddForm(Guid.NewGuid().ToString(), _qr); }
/// <summary> /// 通过字段别名从查询请求中取一个表中的字段定义 /// </summary> /// <param name="_qr"></param> /// <param name="_tName"></param> /// <param name="_aliasName"></param> /// <returns></returns> public static MDQuery_TableColumn GetColumnDefineByAlias(MDQuery_Request _qr, string _tName, string _aliasName) { MDQuery_ResultTable _table = GetResultTableDefine(_qr, _tName); if (_table == null) { return(null); } var _find = from _c in _table.Columns where _c.ColumnAlias == _aliasName select _c; if (_find != null && _find.Count() > 0) { return(_find.First()); } else { return(null); } }
/// <summary> /// 通过查询模型及查询请求建立字段 /// </summary> /// <param name="_request"></param> /// <param name="_qv"></param> public void ShowQueryResult(MDQuery_Request _request, MDModel_QueryModel _qv, DataSet _data) { QueryResultData = _data; QueryRequest = _request; QueryModel = _qv; this.bandedGridView1.Columns.Clear(); this.bandedGridView1.Bands.Clear(); FieldsDictionary.Clear(); MDQuery_ResultTable _mtable = QueryRequest.MainResultTable; CreateBandColumn(_mtable, DefaultBoolean.True); foreach (MDQuery_ResultTable _ctable in QueryRequest.ChildResultTables) { CreateBandColumn(_ctable, DefaultBoolean.False); } }
/// <summary> /// 用保存的查询请求更新此控件 /// </summary> /// <param name="_request"></param> public void RefreshBySaveRequest(MDQuery_Request _request) { //清除选择 foreach (TreeListNode _node in this.treeList1.Nodes) { _node.SetValue("State", 0); foreach (TreeListNode _cnode in _node.Nodes) { _cnode.SetValue("State", 0); } } //选择 MDQuery_ResultTable _mtable = _request.MainResultTable; RefreshTableSelected(_mtable); foreach (MDQuery_ResultTable _ctable in _request.ChildResultTables) { RefreshTableSelected(_ctable); } }
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); }
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); }
/// <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 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> virtual protected void QueryData(IApplication _application) { if (_application == null) { return; } string _errorMsg = ""; if (!this.sinoSZUC_MD_Model_FieldList1.CheckItems(ref _errorMsg)) { XtraMessageBox.Show(string.Format("选择查询结果不正确:{0}", _errorMsg), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (!this.sinoSZUC_ConditionPanel1.CheckInput(ref _errorMsg)) { XtraMessageBox.Show(string.Format("查询条件不正确:{0}", _errorMsg), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MC_QueryRequsetFactory _rf = new MC_QueryRequsetFactory(); _rf.QueryModelName = this.QueryModelName; this.sinoSZUC_ConditionPanel1.InsertConditions2QueryRequest(_rf); this.sinoSZUC_MD_Model_FieldList1.InsertResultFields2QueryRequest(_rf); MDQuery_Request _queryRequest = _rf.GetQueryRequest(); if (_queryRequest.MainResultTable.Columns == null || _queryRequest.MainResultTable.Columns.Count < 1) { XtraMessageBox.Show("主表必须至少选择一个结果字段!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } frmSinoSZ_QueryResult _qr = new frmSinoSZ_QueryResult(_queryModel, _queryRequest); AddExtMenu(_qr); _application.AddForm(Guid.NewGuid().ToString(), _qr); //XtraMessageBox.Show(string.Format("查询请求包占用存{0}Byte", CalcMemory.CalcInstanceMemory(_rf.GetQueryRequest()))); }
private static List <string> GetQueryUsedTableWithWHXH(MDModel_QueryModel _qv, MDQuery_Request _queryRequest) { List <string> _usedTableList = new List <string>(); _usedTableList.Add(_qv.MainTable.TableName); foreach (MDQuery_ConditionItem _cItem in _queryRequest.ConditionItems) { if (!_usedTableList.Contains(_cItem.Column.TableName)) { _usedTableList.Add(_cItem.Column.TableName); } } foreach (MDQuery_ResultTable _resItem in _queryRequest.ChildResultTables) { if (!_usedTableList.Contains(_resItem.TableName)) { _usedTableList.Add(_resItem.TableName); } } return(_usedTableList); }
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 static string QueryRequestToRule(MDQuery_Request _Request, MDModel_QueryModel _queryModel) { MDModel_Table_Column _columnDefine; StringBuilder _ret = new StringBuilder(); _ret.Append(string.Format("<C_BDS>{0}</C_BDS>", _Request.ConditionExpressions)); foreach (MDQuery_ConditionItem _citem in _Request.ConditionItems) { _ret.Append("<C_FIELD>"); #region 加条件序号 _ret.Append(string.Format("<MII_INDEX>{0}</MII_INDEX>", _citem.ColumnIndex)); _ret.Append(string.Format("<MII_OPER>{0}</MII_OPER>", _citem.Operator)); #endregion #region 加字段定义 _ret.Append("<MII_FIELD>"); _ret.Append(string.Format("<QCF_TABLE>{0}</QCF_TABLE>", _citem.Column.TableName)); _ret.Append(string.Format("<QCF_TITLE>{0}</QCF_TITLE>", _citem.Column.ColumnTitle)); switch (_citem.Column.ColumnType) { case QueryColumnType.TableColumn: _ret.Append(string.Format("<QCF_COL>{0}</QCF_COL>", _citem.Column.ColumnName)); _ret.Append(string.Format("<QCF_TYPE>{0}</QCF_TYPE>", _citem.Column.ColumnDataType)); break; case QueryColumnType.CalculationColumn: case QueryColumnType.StatisticsColumn: _ret.Append(string.Format("<QCF_COL>{0}</QCF_COL>", _citem.Column.ColumnAlgorithm)); _ret.Append(string.Format("<QCF_TYPE>#{0}</QCF_TYPE>", _citem.Column.ColumnDataType)); break; } _ret.Append(string.Format("<QCF_REF></QCF_REF>")); _ret.Append(string.Format("<QCF_VALUE></QCF_VALUE>")); _ret.Append(string.Format("<QCF_OPER></QCF_OPER>")); _ret.Append(string.Format("<QCF_SEC>0</QCF_SEC>")); _ret.Append(string.Format("<QCF_INDEX>0</QCF_INDEX>")); _ret.Append(string.Format("<QCF_CON></QCF_CON>")); _ret.Append("</MII_FIELD>"); #endregion #region 加条件值 _ret.Append("<MII_VALUE>"); if (_citem.Column.ColumnType == QueryColumnType.TableColumn) { _columnDefine = MC_QueryModel.GetColumnDefineByName(_queryModel, _citem.Column.TableName, _citem.Column.ColumnName); } else { _columnDefine = new MDModel_Table_Column("", "", "", true, _citem.Column.ColumnDataType, 0, 0, 0, "", "", 0, _citem.Column.ColumnTitle, "", 0, 0, 0, true, 80, "", "", "", "", true, true, true, true, true, 0, _citem.Column.ColumnDataType); _columnDefine.ColumnType = _citem.Column.ColumnType; } if (_columnDefine.ColumnRefDMB != "") { //代码表式 if (_citem.Values.Count > 1) { _ret.Append("<REF_MUTI>TRUE</REF_MUTI>"); } else { _ret.Append("<REF_MUTI>FALSE</REF_MUTI>"); } foreach (string _s in _citem.Values) { _ret.Append("<REF_DATA>"); _ret.Append(string.Format("<REF_CODE>{0}</REF_CODE>", _s)); _ret.Append("</REF_DATA>"); } } else { //普通 string _fg = ""; foreach (string _s in _citem.Values) { _ret.Append(_fg); _ret.Append(_s); _fg = ","; } } _ret.Append("</MII_VALUE>"); #endregion //加条件类型 _ret.Append(string.Format("<MII_TYPE>{0}</MII_TYPE>", _citem.Column.ColumnDataType)); _ret.Append("</C_FIELD>"); } return(_ret.ToString()); }
public System.Data.DataSet QueryData(MDQuery_Request Query_Request) { OraMetaDataQueryFactroy _of = new OraMetaDataQueryFactroy(); return(_of.QueryData(Query_Request)); }
/// <summary> /// 从查询结果中建立全部展示数据 /// </summary> /// <param name="QueryRequest"></param> /// <param name="QueryModel"></param> /// <param name="QueryResultData"></param> /// <returns></returns> public static DataTable CreateFullComboData(MDQuery_Request QueryRequest, MDModel_QueryModel QueryModel, DataSet QueryResultData) { DataTable _ret = new DataTable(); //建TableColumn CreateFullComboTableColumn(QueryRequest, QueryModel, _ret); //填充数据 DataTable _mtableData = QueryResultData.Tables[QueryRequest.MainResultTable.TableName]; bool _isOddRow = false; foreach (DataRow _mainRow in _mtableData.Rows) { _isOddRow = !_isOddRow; int _intRows = 1; int ChildTableCount = _mtableData.ChildRelations.Count; for (int i = 0; i < ChildTableCount; i++) { string _rname = _mtableData.ChildRelations[i].RelationName; int _ChildCount = _mainRow.GetChildRows(_rname).Length; _intRows = (_ChildCount > _intRows) ? _ChildCount : _intRows; } for (int _line = 0; _line < _intRows; _line++) { DataRow _newRow = _ret.NewRow(); _newRow["ROW_MAINID"] = _mainRow["MAINID"].ToString(); _newRow["IS_ODD_ROW"] = _isOddRow; #region 将主表的记录数据加入新行 foreach (MDQuery_TableColumn _rc in QueryRequest.MainResultTable.Columns) { string FieldName = _rc.TableName + "_" + _rc.ColumnName; _newRow[FieldName] = _mainRow[_rc.ColumnAlias]; } #endregion #region 依次将子表的数据加入新行 for (int i = 0; i < ChildTableCount; i++) { string _rname = _mtableData.ChildRelations[i].RelationName; DataRow[] ChildRows = _mainRow.GetChildRows(_rname); int _ChildCount = ChildRows.Length; if (_ChildCount > 0) { int _CurrentLine = 0; if (_line < _ChildCount) { _CurrentLine = _line; } else { _CurrentLine = _ChildCount - 1; } DataRow _CRow = ChildRows[_CurrentLine]; var _find = from _t in QueryRequest.ChildResultTables where _t.TableName == _mtableData.ChildRelations[i].ChildTable.TableName select _t; MDQuery_ResultTable _resultChildTable = _find.First(); foreach (MDQuery_TableColumn _crc in _resultChildTable.Columns) { string cFieldName = _crc.TableName + "_" + _crc.ColumnName; _newRow[cFieldName] = _CRow[_crc.ColumnAlias]; } } } #endregion _ret.Rows.Add(_newRow); } } return(_ret); }
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); } }
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); } }