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