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");
}
/// <summary>
/// 处理In中的select
/// </summary>
/// <param name="where"></param>
private void HandlerInFilterWhere(IEnumerable <IFilter> where)
{
if (where.Any())
{
foreach (IFilter filter in where)
{
if (filter is InFilter)
{
InFilter infilter = filter as InFilter;
if (infilter.Values is SelectBuilder)
{
SelectBuilder inSel = infilter.Values as SelectBuilder;
var intb = inSel.Sources.Sources;
if (intb.Any())
{
foreach (AliasedSource tb in intb)
{
//添加有效期验证
FilterGroup inFilter = new FilterGroup(Conjunction.And,
new LessThanEqualToFilter(tb.Column(FapDbConstants.FAPCOLUMN_FIELD_EnableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new GreaterThanEqualToFilter(tb.Column(FapDbConstants.FAPCOLUMN_FIELD_DisableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new EqualToFilter(tb.Column(FapDbConstants.FAPCOLUMN_FIELD_Dr), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_Dr)));
inSel.AddWhere(inFilter);
}
}
}
}
}
}
}
private static void ParserWhere(SelectBuilder select)
{
foreach (AliasedSource tb in select.Sources.Sources)
{
//添加有效期验证
FilterGroup validFilter = new FilterGroup(Conjunction.And,
new LessThanEqualToFilter(tb.Column(FapDbConstants.FAPCOLUMN_FIELD_EnableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new GreaterThanEqualToFilter(tb.Column(FapDbConstants.FAPCOLUMN_FIELD_DisableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new EqualToFilter(tb.Column(FapDbConstants.FAPCOLUMN_FIELD_Dr), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_Dr)));
select.AddWhere(validFilter);
}
}
public void TestSelect_AddFilter()
{
const string commandText = "SELECT * FROM Customer";
CommandBuilder commandBuilder = new CommandBuilder();
SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText);
Column customerId = select.Sources["Customer"].Column("CustomerId");
customerId.Qualify = false;
Placeholder parameter = new Placeholder("@customerId");
select.AddWhere(new EqualToFilter(customerId, parameter));
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(select);
string expected = "SELECT * FROM Customer WHERE CustomerId = @customerId";
Assert.AreEqual(expected, actual, "The SELECT statement was not updated as expected.");
}
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,”显示
}
}
public void TestSelect_ComplexCommand()
{
string commandText =
@"SELECT
r.RouteId,
r.RouteNumber,
o.CustomerId,
o.CustomerKey AS [Outlet#],
o.Name AS CustomerName,
vm.VendingMachineId,
vm.AssetNumber AS [Equipment#],
m.ModelType AS Model,
rc.FillFrequency,
rc.EffectiveDate AS SettlementDate,
p.ProductLookupId,
p.ProductSKU AS ProductCode,
rcvc.FillLevel AS ProductCapacity,
st.QuantityDelivered AS FillUnits
FROM Company b
INNER JOIN Route r ON b.CompanyId = r.CompanyId
INNER JOIN RouteSchedule rs ON r.RouteId = rs.RouteId
INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId
INNER JOIN
(
SELECT
rc.RouteCardId,
rcvc.ProductLookupId,
SUM(rcvc.FillLevel) AS FillLevel
FROM RouteSchedule rs
INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId
INNER JOIN RouteCardVendColumn rcvc ON rc.RouteCardId = rcvc.RouteCardId
WHERE rs.RouteId IN (1, 2, 3) AND rc.EffectiveDate BETWEEN @startDate AND @stopDate
GROUP BY rc.RouteCardId, rcvc.ProductLookupId
) as rcvc ON rc.RouteCardId = rcvc.RouteCardId
INNER JOIN ProductLookup p ON rcvc.ProductLookupId = p.ProductLookupId
INNER JOIN VendingMachine vm ON rc.VendingMachineId = vm.VendingMachineId
INNER JOIN MachineTypeLookup m ON vm.MachineTypeLookupId = m.MachineTypeLookupId
INNER JOIN Customer o ON vm.CustomerId = o.CustomerId
INNER JOIN ServiceTransaction svc ON
(rc.VendingMachineId = svc.VendingMachineId AND rc.EffectiveDate = svc.ServiceTransactionDate)
INNER JOIN SettlementTransactionSKU st ON
(svc.ServiceTransactionId = st.ServiceTransactionId AND p.ProductLookupId = st.ProductLookupId)
WHERE rc.EffectiveDate BETWEEN @startDate AND @endDate AND r.RouteId IN (1, 2, 3)
ORDER BY b.CompanyId, r.RouteId, vm.VendingMachineId, p.ProductLookupId, rc.EffectiveDate DESC";
CommandBuilder commandBuilder = new CommandBuilder();
SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText);
select.AddWhere(new EqualToFilter(new NumericLiteral(1), new NumericLiteral(1)));
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(select);
string expected = "SELECT"
+ " r.RouteId,"
+ " r.RouteNumber,"
+ " o.CustomerId,"
+ " o.CustomerKey AS [Outlet#],"
+ " o.Name AS CustomerName,"
+ " vm.VendingMachineId,"
+ " vm.AssetNumber AS [Equipment#],"
+ " m.ModelType AS Model,"
+ " rc.FillFrequency,"
+ " rc.EffectiveDate AS SettlementDate,"
+ " p.ProductLookupId,"
+ " p.ProductSKU AS ProductCode,"
+ " rcvc.FillLevel AS ProductCapacity,"
+ " st.QuantityDelivered AS FillUnits"
+ " FROM Company b"
+ " INNER JOIN Route r ON b.CompanyId = r.CompanyId"
+ " INNER JOIN RouteSchedule rs ON r.RouteId = rs.RouteId"
+ " INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId"
+ " INNER JOIN (SELECT rc.RouteCardId, rcvc.ProductLookupId, SUM(rcvc.FillLevel) AS FillLevel FROM RouteSchedule rs INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId INNER JOIN RouteCardVendColumn rcvc ON rc.RouteCardId = rcvc.RouteCardId WHERE rs.RouteId IN (1, 2, 3) AND rc.EffectiveDate BETWEEN @startDate AND @stopDate GROUP BY rc.RouteCardId, rcvc.ProductLookupId) rcvc ON rc.RouteCardId = rcvc.RouteCardId"
+ " INNER JOIN ProductLookup p ON rcvc.ProductLookupId = p.ProductLookupId"
+ " INNER JOIN VendingMachine vm ON rc.VendingMachineId = vm.VendingMachineId"
+ " INNER JOIN MachineTypeLookup m ON vm.MachineTypeLookupId = m.MachineTypeLookupId"
+ " INNER JOIN Customer o ON vm.CustomerId = o.CustomerId"
+ " INNER JOIN ServiceTransaction svc ON (rc.VendingMachineId = svc.VendingMachineId AND rc.EffectiveDate = svc.ServiceTransactionDate)"
+ " INNER JOIN SettlementTransactionSKU st ON (svc.ServiceTransactionId = st.ServiceTransactionId AND p.ProductLookupId = st.ProductLookupId)"
+ " WHERE rc.EffectiveDate BETWEEN @startDate AND @endDate AND r.RouteId IN (1, 2, 3) AND 1 = 1"
+ " ORDER BY b.CompanyId, r.RouteId, vm.VendingMachineId, p.ProductLookupId, rc.EffectiveDate DESC";
Assert.AreEqual(expected, actual, "The SELECT statement was not reproduced as expected.");
}