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"); }
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"); }
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."); }
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,”显示 } }