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