/// <summary>
/// 处理SELECT部分
/// </summary>
/// <param name="columnList"></param>
/// <param name="table"></param>
/// <param name="selectBuilder"></param>
private void MakeSelectStarPartition(IEnumerable <FapColumn> columnList, AliasedSource aliaseSource, SelectBuilder select)
{
string tableAlias = aliaseSource.Alias;
int mcIndex = 0;
foreach (var column in columnList)
{
//AliasedSource table = select.AddTable(new Table($"{column.TableName}"), tableAlias);
if (column.IsMultiLang == 1)
{
if (IsGridQuery)
{
select.AddProjection(aliaseSource.Column($"{column.ColName}{CurrentLang}"), column.ColName);
}
else
{
select.AddProjection(aliaseSource.Column(column.ColName));
}
var langs = typeof(MultiLanguageEnum).EnumItems();
foreach (var lang in langs)
{
select.AddProjection(aliaseSource.Column($"{column.ColName}{lang.Value}"), $"{column.ColName}{lang.Value}");
}
}
else
{
select.AddProjection(aliaseSource.Column(column.ColName));
}
if (this._withMC)
{
//处理MC字段
if (FapColumn.CTRL_TYPE_COMBOBOX == column.CtrlType && !IsGridQuery)
{
string refAlias = "b" + (mcIndex++);
BuildFapDict(select, column, aliaseSource, refAlias);
}
else if (FapColumn.CTRL_TYPE_REFERENCE == column.CtrlType)
{
string refAlias = "b" + (mcIndex++);
BuildRefrenceName(select, column, aliaseSource, refAlias);
}
}
//处理MC字段的ID字段
if (this._withId)
{
if (FapColumn.CTRL_TYPE_REFERENCE == column.CtrlType)
{
string refAlias = "b" + (mcIndex++);
BuildRefrenceCode(select, column, aliaseSource, refAlias);
}
}
}
}
private static void BuildRefrenceCode(SelectBuilder select, FapColumn column, AliasedSource table, string refAlias)
{
string colName = column.RefCode;
if (colName.IsPresent())
{
colName = "Id";
}
SelectBuilder inner = new SelectBuilder();
AliasedSource innerTable = inner.AddTable(new Table($"{column.RefTable}"), refAlias);
inner.AddProjection(innerTable.Column($"{colName}"));
if (column.MultiAble == 1) //是否多选
{
}
FilterGroup joinFilter = new FilterGroup(Conjunction.And,
new EqualToFilter(innerTable.Column($"{column.RefID}"), table.Column(column.ColName)),
new LessThanEqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_EnableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new GreaterThanEqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_DisableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new EqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_Dr), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_Dr)));
if (column.RefTable.EqualsWithIgnoreCase(nameof(FapColumn)))
{
IFilter filter = new EqualToFilter(innerTable.Column("TableName"), table.Column("TableName"));
joinFilter.AddFilter(filter);
}
inner.AddWhere(joinFilter);
select.AddProjection(inner, $"{column.ColName}MCID");
}
public void TestFilterGroup_Optimize_SimplifiesConditions()
{
FilterGroup topFilter = new FilterGroup(Conjunction.Or,
new FilterGroup(Conjunction.And,
new EqualToFilter(new Column("FirstName"), new StringLiteral("Albert")),
new FilterGroup(Conjunction.And,
new EqualToFilter(new Column("LastName"), new StringLiteral("Einstein")))),
new FilterGroup(Conjunction.And,
new EqualToFilter(new Column("FirstName"), new StringLiteral("Max")),
new FilterGroup(Conjunction.And,
new EqualToFilter(new Column("LastName"), new StringLiteral("Planck")))));
wrapInParentheses(topFilter, true);
SelectBuilder selectBuilder = new SelectBuilder();
selectBuilder.AddTable(new Table("Person"));
selectBuilder.AddProjection(new Column("FirstName"));
selectBuilder.AddProjection(new Column("LastName"));
selectBuilder.AddWhere(topFilter);
Formatter formatter = new Formatter();
string beforeActual = formatter.GetCommandText(selectBuilder);
const string beforeExpected = "SELECT FirstName, LastName FROM Person WHERE (((FirstName = 'Albert') AND ((LastName = 'Einstein'))) OR ((FirstName = 'Max') AND ((LastName = 'Planck'))))";
Assert.AreEqual(beforeExpected, beforeActual, "The initial query had an unexpected string representation.");
wrapInParentheses(topFilter, false);
topFilter.Optimize();
wrapInParentheses(topFilter, true);
string afterActual = formatter.GetCommandText(selectBuilder, new CommandOptions()
{
WrapFiltersInParentheses = true
});
const string afterExpected = "SELECT FirstName, LastName FROM Person WHERE (((FirstName = 'Albert') AND (LastName = 'Einstein')) OR ((FirstName = 'Max') AND (LastName = 'Planck')))";
Assert.AreEqual(afterExpected, afterActual, "The optimized query had an unexpected string representation.");
}
/// <summary>
/// 处理Select语句：有明确字段的SELECT语句
/// </summary>
/// <param name="tables"></param>
/// <param name="field"></param>
/// <param name="builder"></param>
private void HandleSelectStatement(SelectBuilder select, AliasedProjection currProjection, Column column)
{
string colName = column.Name;
//string tableAlias = column.Source.Alias;
string tableName = column.Source.Source.GetSourceName();
FapColumn fCol = GetSingleColumnOfTable(tableName, colName);
if (fCol.IsMultiLang == 1 && IsGridQuery)
{
select.RemoveProjection(currProjection);
select.AddProjection(column.Source.Column($"{colName}{CurrentLang}"), colName);
}
if (_withMC)
{
this.MakeSelectPartition(fCol, select, column);
}
}
private static void BuildRefrenceName(SelectBuilder select, FapColumn column, AliasedSource table, string refAlias)
{
SelectBuilder inner = new SelectBuilder();
AliasedSource innerTable = inner.AddTable(new Table($"{column.RefTable}"), refAlias);
inner.AddProjection(innerTable.Column($"{column.RefName}"));
FilterGroup joinFilter = new FilterGroup(Conjunction.And,
new EqualToFilter(innerTable.Column($"{column.RefID}"), table.Column(column.ColName)),
new LessThanEqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_EnableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new GreaterThanEqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_DisableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new EqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_Dr), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_Dr)));
if (column.RefTable.EqualsWithIgnoreCase(nameof(FapColumn)))
{
//fapcolumn存在重复colName，加一个去重
IFilter filter = new EqualToFilter(innerTable.Column("TableName"), table.Column("RefTable"));
joinFilter.AddFilter(filter);
}
inner.AddWhere(joinFilter);
select.AddProjection(inner, $"{column.ColName}MC");
}
private static void BuildFapDict(SelectBuilder select, FapColumn column, AliasedSource table, string refAlias)
{
SelectBuilder inner = new SelectBuilder();
AliasedSource innerTable = inner.AddTable(new Table("FapDict"), refAlias);
inner.AddProjection(innerTable.Column("Name"));
if (column.MultiAble == 1) //是否多选
{
}
FilterGroup joinFilter = new FilterGroup(Conjunction.And,
new EqualToFilter(innerTable.Column("Code"), table.Column(column.ColName)),
new EqualToFilter(innerTable.Column("Category"), new StringLiteral(column.ComboxSource)),
new LessThanEqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_EnableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new GreaterThanEqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_DisableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new EqualToFilter(innerTable.Column(FapDbConstants.FAPCOLUMN_FIELD_Dr), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_Dr)));
inner.AddWhere(joinFilter);
select.AddProjection(inner, $"{column.ColName}MC");
if (column.MultiAble == 1) //是否多选
{
//selectBuilder.Append(" for xml path('')"); // 逗号分隔的字符串，比如，以“aaa,bbb,ccc,”显示
}
}