public override void Init(string _title, string _menuName, object _param) { compareRequest = _param as MDCompare_Request; this.Text = _title; _initFinished = true; RaiseMenuChanged(); }
public void ShowQueryResult(MDCompare_Request _request, MDModel_QueryModel _qv, DataTable _excelResultData) { //QueryResultData = new DataSet(); 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) { if (_ctable.TableName != "") { CreateBandColumn(_ctable, DefaultBoolean.False); } } CreateExcelBandColumn(_excelResultData); }
private static string CreateCompareMainKeyString(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { string _res = ""; _res = string.Format(" TEMP_IMPDATA.XH,{0}.{1} MAINID ", _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey); return(_res); }
private static string GetMainTableQueryStr(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { MDQuery_Request _queryRequest = compareRequest as MDQuery_Request; List <string> _QueryUsedTableList = OraQueryBuilder.GetQueryUsedTable(_qv, _queryRequest); string _conditionRes = CreateConditionStringByMainID(_qv); string _tableStr = OraQueryBuilder.CreateTableString(_qv, _QueryUsedTableList); string _tableRelationStr = OraQueryBuilder.CreateTableRelationString(_qv, _QueryUsedTableList); string _displayStr = OraQueryBuilder.CreateDisplayString(_qv, compareRequest.MainResultTable); return(string.Format("select distinct {0} from {1},COMP_TEMP where ( {2} ({3})) ", _displayStr, _tableStr, _tableRelationStr, _conditionRes)); }
private static string GetExcelResidual(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { StringBuilder _sb = new StringBuilder(); _sb.Append(string.Format(" select TEMP_IMPDATA.XH 行号 ", _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey)); foreach (string _key in compareRequest.ColumnDictionary.Keys) { _sb.Append(string.Format(",TEMP_IMPDATA.{0} \"{1}\"", _key, compareRequest.ColumnDictionary[_key])); } _sb.Append(" from TEMP_IMPDATA where xh not in (select pk_c from COMP_TEMP) "); return(_sb.ToString()); }
private static string GetCompareConditionStr(MDModel_QueryModel _qv, MDCompare_Request compareRequest, string _itemIndex) { if (!compareRequest.CompareItems.ContainsKey(_itemIndex)) { throw new Exception(string.Format("缺少编号为{0}的条件项!", _itemIndex)); } else { MDCompare_ConditionItem _cItem = compareRequest.CompareItems[_itemIndex]; return(OraConditionItemBuilder.BuildCompareConditionItemString(_cItem, _qv)); } }
public static string CreateCompareSQL(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { //1.生成以筛选条件对查询模型数据集进行筛选的语句 string _filterSQL = CreateFilterStr(_qv, compareRequest); //2.生成比对语句 string _compareSQL = CreateCompareStr(_qv, compareRequest); string _mainSql = string.Format("{0} and ({1}.{2} in ({3}))", _compareSQL, _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey, _filterSQL); string _mainResult = string.Format("insert into COMP_TEMP (PK_C,PK_C2) {0}", _mainSql); return(_mainResult); }
private static string GetExecelResultStr(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { StringBuilder _sb = new StringBuilder(); _sb.Append(string.Format(" select COMP_TEMP.PK_C 行号, {0}.{1} MAINID ", _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey)); foreach (string _key in compareRequest.ColumnDictionary.Keys) { _sb.Append(string.Format(",TEMP_IMPDATA.{0} \"{1}\"", _key, compareRequest.ColumnDictionary[_key])); } _sb.Append(string.Format(" from COMP_TEMP,TEMP_IMPDATA,{0} where TEMP_IMPDATA.XH = COMP_TEMP.pk_c and ", _qv.MainTable.TableName)); _sb.Append(string.Format("{0}.{1}= COMP_TEMP.pk_c2", _qv.MainTable.TableName, _qv.MainTable.TableDefine.Table.MainKey)); return(_sb.ToString()); }
private static string CreateCompareWhereStr(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { if (compareRequest.CompareConditionExpression == "") { return("1=1"); } string conditionStr = compareRequest.CompareConditionExpression; StringBuilder res = new StringBuilder(); string _itemIndex = ""; for (int i = 0; i < conditionStr.Length; i++) { char c = conditionStr[i]; switch (c) { case '+': res.Append(" or "); break; case '*': res.Append(" and "); break; case '!': res.Append(" not "); break; case '(': res.Append(" ("); break; case ')': res.Append(" ) "); break; default: //数字 if (char.IsDigit(c)) { _itemIndex = OraQueryBuilder.ProcDigit(conditionStr, ref i); } res.Append(GetCompareConditionStr(_qv, compareRequest, _itemIndex)); break; } } return(res.ToString()); }
private static List <string> GetAllCompareUsedTables(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { List <string> _ret = new List <string>(); _ret.Add(_qv.MainTable.TableName); foreach (MDCompare_ConditionItem _conItem in compareRequest.CompareItems.Values) { string _table = _conItem.Column.TableName; if (!_ret.Contains(_table)) { _ret.Add(_table); } } return(_ret); }
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); }
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); }
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); }
public static Dictionary <string, string> GetAllResultSQL(MDModel_QueryModel _qv, MDCompare_Request compareRequest) { Dictionary <string, string> _SqlCollection = new Dictionary <string, string>(); _SqlCollection.Add(_qv.MainTable.TableName, GetMainTableQueryStr(_qv, compareRequest)); foreach (MDQuery_ResultTable _qrc in compareRequest.ChildResultTables) { _SqlCollection.Add(_qrc.TableName, GetQueryStrByMainID(_qv, _qrc)); } _SqlCollection.Add("EXCELRESULTDATA", GetExecelResultStr(_qv, compareRequest)); _SqlCollection.Add("RESIDUAL", GetExcelResidual(_qv, compareRequest)); return(_SqlCollection); }
public static DataTable CreateFullComboData(MDCompare_Request QueryRequest, MDModel_QueryModel QueryModel, DataSet QueryResultData) { DataTable _ret = new DataTable(); //建TableColumn CreateFullComboTableColumn(QueryRequest as MDQuery_Request, QueryModel, _ret); foreach (DataColumn _dc in QueryResultData.Tables["EXCELRESULTDATA"].Columns) { if (_dc.ColumnName != "MAINID") { string FieldName = "EXCEL_" + _dc.ColumnName; DataColumn _newdc = new DataColumn(FieldName); _newdc.Caption = _dc.ColumnName; _ret.Columns.Add(_newdc); } } //填充数据 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]; if (_rname != "比对的EXCEL文件") { 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]; } } else { foreach (DataColumn _dc in QueryResultData.Tables["EXCELRESULTDATA"].Columns) { if (_dc.ColumnName != "MAINID") { string cFieldName = "EXCEL_" + _dc.ColumnName; _newRow[cFieldName] = _CRow[_dc.ColumnName]; } } } } } #endregion _ret.Rows.Add(_newRow); } } return(_ret); }
private void DoCompare() { //开始数据比对 string _errorMsg = ""; if (_excelReader == null) { MessageBox.Show("未打开EXCEL文件!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //取结果集字段 if (!this.sinoSZUC_MD_Model_FieldList1.CheckItems(ref _errorMsg)) { XtraMessageBox.Show(string.Format("选择比对结果不正确:{0}", _errorMsg), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (!this.sinoSZUC_CompareConditionPanel1.CheckInput(ref _errorMsg)) { XtraMessageBox.Show(string.Format("筛选条件或比对条件不正确:{0}", _errorMsg), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MC_CompareRequsetFactory _rf = new MC_CompareRequsetFactory(); _rf.QueryModelName = this.QueryModelName; this.sinoSZUC_CompareConditionPanel1.InsertConditions2QueryRequest(_rf); this.sinoSZUC_MD_Model_FieldList1.InsertResultFields2QueryRequest(_rf); MDCompare_Request _compareRequest = _rf.GetCompareQueryRequest(); //if (_queryResult._resultItems.Count < 1) //{ // MessageBox.Show("未选择结果字段!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); // return; //} //SinoProgressControler.BeginProgress(); ////SinoProgressControler.ShowMessage = "正在加载比对目标数据...."; ////1.将需要比对的目标文件的数据加载入DataTable //ArrayList _usedExcelRowList = GetUsedExcelRows(); ////ArrayList _usedExcelRowList = this._ExcelColumnNames; ////2.设置别名 //ReBuildAlias(ref _usedExcelRowList); ////2.将表达式进行处理 //CompareConditionClass _CompareCon = new CompareConditionClass(); //_CompareCon.Express = textEdit1.EditValue.ToString(); //foreach (UC_CompareConditionItem _uc in this.panel14.Controls) //{ // if (!_uc.CheckInput()) // { // MessageBox.Show("比对条件错误!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); // return; // } // CompareConditionItemClass _item = _uc.GetCondition(); // _CompareCon.AddItem(_item); //} //_CompareCon.QueryView = (QueryView_Class)comboBoxEdit1.SelectedItem; //QueryCondition_Class _qcc = this.uC_RelationConditionPanel1.GetConditions(); //foreach (Meta_ConditionItem_Class _mcc in _qcc.ConditionItems) //{ // _mcc.Column.Table_Meta.AliasName = _mcc.Column.Table_Meta.TableName; //} ////3.导入数据到DataTable //DataTable _importdt = SessionClass.IMetaData.GetImportTableTemp(); //if (this._excelReader != null) //{ // _excelReader.OpenReportTempalte(); // _excelReader.InsertExcelData(_usedExcelRowList, ref _importdt); // _excelReader.CloseReportTemplate(); //} ////3.调用后台服务进行比对,并返回结果的DataSet //QueryResult_Class _mainRes = this.uC_CompareResultPanl1.GetMainTableResults(); //ArrayList _childRes = this.uC_CompareResultPanl1.GetChildResults(); //DataSet _CompareResult = SessionClass.IMetaData.CompareData2(_CompareCon, _importdt, _mainRes, _childRes, _qcc, _usedExcelRowList); ////4.将结果显示出来 //SinoProgressControler.EndProgress(); //CompareResultForm_D2E _f = new CompareResultForm_D2E(_CompareCon.QueryView, _mainRes, _childRes, _importdt, _CompareResult, _usedExcelRowList); //SessionClass.mainForm.AddPage("比对结果", 0, _f); _compareRequest.ExcelData = _excelReader.GetData(); _compareRequest.ColumnDictionary = _excelReader.CreateColumnDictionary(); frmDataCompareResult _frmResult = MenuFunctions.AddPage <frmDataCompareResult>(Guid.NewGuid().ToString(), Application); if (_frmResult != null) { _frmResult.Init("数据比对结果", "", _compareRequest, _excelReader.GetColumns()); } }