public void ShouldReturnCorrectSelectList()
{
var builder = new SelectBuilder<Model.Model>();
builder
.Select(model => model.Id)
.Select(model => model.Name);
builder.ToString().Should().Contain("SELECT [Id], [Name]");
}
public void ShouldSetMultipleSelectListThroughExpression()
{
var builder = new SelectBuilder<Model.Model>();
builder.Select(model => model.Id).Select(model => model.Name);
builder.SelectColumns.Count.Should().Be(2);
builder.SelectColumns.First().Should().Be("Id");
builder.SelectColumns.Last().Should().Be("Name");
}
public void ShouldInsertIndividualColumnNames1()
{
var builder = new SelectBuilder<Model.Model>();
builder.Select("Id", "Name");
builder.SelectColumns.Count.Should().Be(2);
builder.SelectColumns.First().Should().Be("Id");
builder.SelectColumns.Last().Should().Be("Name");
}
/// <summary> /// 未查询到数据时返回 null /// </summary> public abstract DateTime?SelectAsDateTime <T>(DbSession session, SelectBuilder selectBuilder) where T : IPDMTBase, new();
/// <summary>执行SQL查询，返回记录集</summary>
/// <param name="builder">SQL语句</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns></returns>
public virtual Task <DbTable> QueryAsync(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
InitData();
return(GetDAL(true).QueryAsync(builder, startRowIndex, maximumRows));
}
/// <summary>
/// 未查询到数据时返回 null
/// </summary>
public static TEarthworkBlock DbSelect(this TEarthworkBlock entity, DbSession session, SelectBuilder select)
{
var query = session.GetDbQueryBuilder();
query.SelectBuilder = select;
return(session.GetQueryOperator().Select <TEarthworkBlock>(query));
}
/// <summary>查询记录数</summary>
/// <param name="builder">查询生成器</param>
/// <returns>记录数</returns>
public virtual Int32 QueryCount(SelectBuilder builder)
{
InitData();
return(Dal.SelectCount(builder));
}
protected void Page_Load(object sender, EventArgs e)
{
e_Alteration alter = e_Alteration.Find("EmployeeID=" + Employee.Current.EmployeeID);
if (!IsPostBack)
{
//if (Request["shi_addr"] != null)
//{
// H_houseinfor hh = H_houseinfor.FindByHouseID(Request["HouseID"].ToDecimal().Value);
// string PingGuJia = hh.PingGuJia.ToString();
// if (PingGuJia != Request.Form["shi_addr"])
// {
// if (Convert.ToDecimal(Request["shi_addr"]) > 0)
// s_HouseDicFloorPrice.SetFloorPrice(hh.HouseDicID, hh.Build_id, hh.Build_floor, Request["shi_addr"].ToDecimal().Value, hh.HouseID, true);
// }
//}
if (Request["hid"] != null && Request["uid"] != null)
{
int iH = Convert.ToInt32(Request["hid"]), iU = Convert.ToInt32(Request["uid"]);
SelectBuilder sb = new SelectBuilder();
sb.Table = "h_SeeTelLog";
sb.Where = string.Format("EmployeeID={0} and HouseID={1} and SheBei in ('101','102') and CONVERT(DATE,exe_date,120)='" + DateTime.Now.ToString("yyyy-MM-dd") + "'", iU, iH);
//当日查看相同房源不算次数
if (h_SeeTelLog.Meta.QueryCount(sb) == 0)
{
H_houseinfor hou = H_houseinfor.FindByHouseID(iH);
//出售房源
if (hou.aType == 0)
{
H_houseinfor.Meta.Execute(string.Format("insert into h_SeeTelLog(HouseID,EmployeeID,SheBei,ComID) values({0},{1},101,{2})", iH, iU, Employee.Current.ComID));
}
else
{
H_houseinfor.Meta.Execute(string.Format("insert into h_SeeTelLog(HouseID,EmployeeID,SheBei,ComID) values({0},{1},103,{2})", iH, iU, Employee.Current.ComID));
}
//查看门牌的次数
int lookcount = int.Parse(DbHelperSQL.GetSingle(string.Format("select count(1) from h_SeeTelLog where EmployeeID={0} and SheBei in ('101','102') and CONVERT(DATE,exe_date,120)='" + DateTime.Now.ToString("yyyy-MM-dd") + "'", iU)).ToString());
//角色设置的次数
int count = int.Parse(DbHelperSQL.GetSingle(string.Format("select Max(Tabletnumber) as Tabletnumber from p_Role where RoleID in ({0})", Employee.Current.RoleIDs)).ToString());
if (lookcount > count && count != 0)
{
//alter.StateID = 5;
alter.Remarks = DateTime.Now.ToString("yyyy-MM-dd") + "日,查看出售房源门牌号超出角色限制" + count + "次";
alter.exe_Date = DateTime.Now;
alter.Update();
Response.Write(1);
Response.End();
}
else if (lookcount == count && count != 0)
{
Response.Write(2);
}
}
}
}
}
/// <summary>
/// 未查询到数据时返回 null
/// </summary>
public static TUserBasicInfo DbSelect(this TUserBasicInfo entity, DbSession session, SelectBuilder select)
{
var query = IORMProvider.GetDbQueryBuilder(session);
query.SelectBuilder = select;
return(IORMProvider.GetQueryOperator(session).Select <TUserBasicInfo>(session, query));
}
/// <summary>
/// 失败返回null
/// </summary>
public override T Select <T>(DbSession session, IDbQueryBuilder queryBuilder)
{
SelectBuilder selectBuilder = queryBuilder.SelectBuilders.FirstOrDefault();
return(Select <T>(session, selectBuilder));
}
/// <summary>根据条件把普通查询SQL格式化为分页SQL。</summary>
/// <remarks>
/// 因为需要继承重写的原因，在数据类中并不方便缓存分页SQL。
/// 所以在这里做缓存。
/// </remarks>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
public virtual SelectBuilder PageSplit(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows)
{
//builder.Table = FormatedTableName;
return(Dal.PageSplit(builder, startRowIndex, maximumRows));
}
protected override void VisitSelect(SelectBuilder item)
{
this.QueryExpression = new QueryExpression();
CurrentRequest.Query = this.QueryExpression;
if (!item.From.Any())
{
throw new InvalidOperationException("The query does not have a valid FROM clause");
}
Mode = VisitMode.From;
if (item.From.Count() == 1)
{
IsSingleSource = true;
}
VisitEach(item.From);
Mode = VisitMode.Projection;
NavigateProjections(item.Projection);
Mode = VisitMode.Filter;
if (item.WhereFilterGroup != null)
{
IFilter where = item.WhereFilterGroup;
where.Accept(this);
}
else
{
//TODO: Should only be one where clause?
foreach (IVisitableBuilder where in item.Where)
{
where.Accept(this);
}
}
if (item.Top != null)
{
Mode = VisitMode.Top;
IVisitableBuilder top = item.Top;
top.Accept(this);
}
else
{
// xrm wont let you use paging and top for some reason..
if (QueryExpression.PageInfo == null)
{
QueryExpression.PageInfo = new PagingInfo();
}
QueryExpression.PageInfo.PageNumber = 1;
//todo take this from the connection string..
QueryExpression.PageInfo.Count = 500;
QueryExpression.PageInfo.ReturnTotalRecordCount = true;
}
if (item.OrderBy != null)
{
Mode = VisitMode.OrderBy;
foreach (IVisitableBuilder order in item.OrderBy)
{
order.Accept(this);
}
}
}
/// <summary>
/// 未查询到数据时返回 null
/// </summary>
public static TNodeElement DbSelect(this TNodeElement entity, DbSession session, SelectBuilder select)
{
var query = session.GetDbQueryBuilder();
query.SelectBuilder = select;
return(session.GetQueryOperator().Select <TNodeElement>(query));
}
/// <summary>NotIn操作。直接使用字符串可能有注入风险</summary>
/// <remarks>空参数不参与表达式操作，不生成该部分SQL拼接</remarks>
/// <param name="builder">数值</param>
/// <returns></returns>
public Expression NotIn(SelectBuilder builder)
{
if (builder == null) return new Expression();
return CreateIn("{0} Not In({1})", builder);
}
public static void Main(string[] args)
{
var select = new SelectBuilder();
var insert = new InsertBuilder();
var update = new UpdateBuilder();
var delete = new DeleteBuilder();
int?age = null;
int?teste = null;
var selectBuilder =
select.Top(10)
.Distinct()
.Column("ID")
.Column("Name")
.Column("Age")
.From("Table")
.Where("ID").Eq(1).If(() => true)
.And.Where("Age").Gt(age).If(age)
.And.Where("Age").In(1, 2, 3, 4, 5)
.Or.Where("Age").NotIn(1, 2, 5, 6)
.Or.Where("Age").Between(1, 3).If(true)
.Or.Where("Name").Like("%Angelo%")
.And.Where("CreatedDate").Eq(teste)
.Build();
Console.WriteLine(selectBuilder.SQLCommand);
var insertBuilder =
insert.Into("table", "schema")
.Value("ID", 1)
.Value("Age", 20)
.Value("Name", "Angelo")
.Build();
Console.WriteLine(insertBuilder.SQLCommand);
var updateBuilder =
update.Table("Table", "schema")
.Set("ID", 1)
.Set("Age", 1)
.Set("Name", "José")
.Where("ID").Eq(1).If(() => true)
.And.Where("Age").Gt(age).If(age)
.And.Where("Age").In(1, 2, 3, 4, 5)
.Or.Where("Age").NotIn(1, 2, 5, 6)
.Or.Where("Age").Between(1, 3).If(true)
.Or.Where("Name").Like("%Angelo%")
.And.Where("CreatedDate").Eq(teste)
.Build();
Console.WriteLine(updateBuilder.SQLCommand);
var deleteBuilder =
delete.Table("Table")
.Where("ID").Eq(1).If(() => true)
.And.Where("Age").Gt(age).If(age)
.And.Where("Age").In(1, 2, 3, 4, 5)
.Or.Where("Age").NotIn(1, 2, 5, 6)
.Or.Where("Age").Between(1, 3).If(true)
.Or.Where("Name").Like("%Angelo%")
.And.Where("CreatedDate").Eq(teste)
.Build();
Console.WriteLine(deleteBuilder.SQLCommand);
Console.ReadKey();
}
/// <summary>
/// Generates the text for a Select builder.
/// </summary>
/// <param name="item">The Select builder to generate the text for.</param>
protected internal override void VisitSelect(SelectBuilder item)
{
forCommandType(CommandType.Select).visitSelect(item);
}
public static void Run(ILogger logger)
{
OnlineClient client = Bootstrap.Client(logger);
List <IFilter> filterList = new List <IFilter>();
filterList.Add((new Filter("CUSTOMERID")).SetLike("c%"));
filterList.Add((new Filter("CUSTOMERID")).SetLike("1%"));
OrOperator filter = new OrOperator(filterList);
OrderBuilder orderBuilder = new OrderBuilder();
IOrder[] orders = orderBuilder.Descending("CUSTOMERID").GetOrders();
SelectBuilder selectBuilder = new SelectBuilder();
ISelect[] fields = selectBuilder.
Fields(new[] { "CUSTOMERID", "CUSTOMERNAME" }).
Sum("TOTALDUE").
GetFields();
QueryFunction query = new QueryFunction()
{
SelectFields = fields,
FromObject = "ARINVOICE",
Filter = filter,
CaseInsensitive = true,
PageSize = 100,
OrderBy = orders
};
logger.LogInformation("Executing query to Intacct API");
Task <OnlineResponse> task = client.Execute(query);
task.Wait();
OnlineResponse response = task.Result;
Result result = response.Results[0];
dynamic json = JsonConvert.DeserializeObject(JsonConvert.SerializeObject(result.Data));
if (json != null && json.First != null)
{
Console.WriteLine("Success! Number of ARINVOICE objects found: " + result.TotalCount);
Console.WriteLine("First ARINVOICE result found");
Console.WriteLine("CUSTOMERID: " + json.First["ARINVOICE"]["CUSTOMERID"].Value);
Console.WriteLine("CUSTOMERNAME: " + json.First["ARINVOICE"]["CUSTOMERNAME"].Value);
Console.WriteLine("SUM.TOTALDUE: " + json.First["ARINVOICE"]["SUM.TOTALDUE"].Value);
Console.WriteLine("See the log file (logs/intacct.log) for the complete list of results.");
}
else
{
Console.WriteLine("The query executed, but no ARINVOICE objects met the query criteria.");
Console.WriteLine("Either modify the filter or comment it out from the query.");
Console.WriteLine("See the log file (logs/intacct.log) for the XML request.");
}
try
{
string jsonString = json.ToString();
logger.LogDebug(
"Query successful [ Company ID={0}, User ID={1}, Request control ID={2}, Function control ID={3}, Total count={4}, Data={5} ]",
response.Authentication.CompanyId,
response.Authentication.UserId,
response.Control.ControlId,
result.ControlId,
result.TotalCount,
jsonString
);
}
catch (NullReferenceException e)
{
logger.LogDebug("No response in Data. {0}", e);
}
finally
{
LogManager.Flush();
}
}
public PagedSelectCommandBuilder Select(string value) => this.Chain(() => SelectBuilder.Append(value));
public void EmptyConstructor()
{
SelectBuilder select = new SelectBuilder();
Assert.AreEqual(select.SelectStatement, string.Empty);
}
/// <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);
}
}
}
}
/// <summary>
/// 失败返回 new List<T>()
/// </summary>
public override List <T> SelectAll <T>(DbSession session, IDbQueryBuilder queryBuilder)
{
SelectBuilder selectBuilder = queryBuilder.SelectBuilders.First();
return(SelectAll <T>(session, selectBuilder));
}
private Int64 GetCount(Int64 count)
{
//if (count >= 0)
//{
// // 小于1000的精确查询，大于1000的快速查询
// if (count <= 1000L)
// {
// var builder = new SelectBuilder
// {
// Table = FormatedTableName
// };
// return Dal.SelectCount(builder);
// }
//}
// 第一次访问，SQLite的Select Count非常慢，数据大于阀值时，使用最大ID作为表记录数
if (count < 0 && Dal.DbType == DatabaseType.SQLite && Table.Identity != null)
{
var builder = new SelectBuilder
{
Table = FormatedTableName,
OrderBy = Table.Identity.Desc()
};
var ds = Dal.Select(builder, 0, 1);
if (ds.Tables[0].Rows.Count > 0)
{
count = Convert.ToInt64(ds.Tables[0].Rows[0][Table.Identity.ColumnName]);
}
}
// 100w数据时，没有预热Select Count需要3000ms，预热后需要500ms
if (count < 500000)
{
if (count <= 0)
{
count = Dal.Session.QueryCountFast(TableName);
}
// 查真实记录数，修正FastCount不够准确的情况
if (count < 10000000)
{
var builder = new SelectBuilder
{
Table = FormatedTableName
};
count = Dal.SelectCount(builder);
}
}
else
{
// 异步查询弥补不足，千万数据以内
if (count < 10000000)
{
count = Dal.Session.QueryCountFast(TableName);
}
}
return(count);
}
public void ThreeLevelFilterTest()
{
string expected = @"<?xml version=""1.0"" encoding=""utf-8""?>
<function controlid=""unittest"">
<query>
<select>
<field>BATCHNO</field>
<field>RECORDNO</field>
<field>STATE</field>
</select>
<object>GLBATCH</object>
<filter>
<or>
<and>
<equalto>
<field>JOURNAL</field>
<value>APJ</value>
</equalto>
<equalto>
<field>STATE</field>
<value>Posted</value>
</equalto>
</and>
<and>
<equalto>
<field>JOURNAL</field>
<value>RCPT</value>
</equalto>
<equalto>
<field>STATE</field>
<value>Posted</value>
</equalto>
<or>
<equalto>
<field>RECORDNO</field>
<value>168</value>
</equalto>
<equalto>
<field>RECORDNO</field>
<value>132</value>
</equalto>
</or>
</and>
</or>
</filter>
<options />
</query>
</function>";
SelectBuilder builder = new SelectBuilder();
ISelect[] fields = builder.Fields(new[] { "BATCHNO", "RECORDNO", "STATE" }).GetFields();
AndOperator apjAndState = new AndOperator(new List <IFilter>());
apjAndState.AddFilter((new Filter("JOURNAL")).SetEqualTo("APJ"));
apjAndState.AddFilter((new Filter("STATE")).SetEqualTo("Posted"));
OrOperator recordnoOr = new OrOperator(new List <IFilter>());
recordnoOr.AddFilter((new Filter("RECORDNO")).SetEqualTo("168"));
recordnoOr.AddFilter((new Filter("RECORDNO")).SetEqualTo("132"));
AndOperator rcptAndState = new AndOperator(new List <IFilter>());
rcptAndState.AddFilter((new Filter("JOURNAL")).SetEqualTo("RCPT"));
rcptAndState.AddFilter((new Filter("STATE")).SetEqualTo("Posted"));
rcptAndState.AddFilter(recordnoOr);
IFilter filter = new OrOperator(new List <IFilter>()
{
apjAndState, rcptAndState
});
IFunction query = new QueryFunction("unittest")
{
FromObject = "GLBATCH",
SelectFields = fields,
Filter = filter
};
this.CompareXml(expected, query);
}
/// <summary>
/// Add Where In (...) clausule
/// </summary>
/// <param name="builder">Builder to be include as select</param>
/// <param name="obj">Identifier</param>
/// <returns></returns>
public static bool Contains(this SelectBuilder builder, object obj)
{
return(true);
}
static void Test7()
{
var set = XCode.Setting.Current;
//set.Debug = true;
set.ShowSQL = false;
//XCode.Cache.CacheBase.Debug = true;
var dal = UserX.Meta.Session.Dal;
dal.Db.DataCache = 3;
var list = UserX.FindAll(null, null, null, 1, 20);
var u = UserX.FindByKey(1);
var n = UserX.FindCount();
Console.WriteLine("总数据：{0:n0}", n);
//using (var tr = UserX.Meta.CreateTrans())
//{
// u = new UserX
// {
// Name = Rand.NextString(8),
// DisplayName = Rand.NextString(16)
// };
// u.Insert();
// if (Rand.Next(2) == 1) tr.Commit();
//}
Task.Run(() =>
{
var us = new List <UserX>();
for (var i = 0; i < 1_000_000; i++)
{
var entity = new UserX
{
Name = Rand.NextString(8),
DisplayName = Rand.NextString(16)
};
us.Add(entity);
}
us.Insert(true);
});
var sql = "select * from user limit 20";
var ds = dal.Select(sql);
ds = dal.Select(sql, CommandType.Text);
ds = dal.Select(sql, CommandType.Text, new Dictionary <String, Object>());
var dt = dal.Query(sql, new Dictionary <String, Object>());
n = dal.SelectCount(sql, CommandType.Text);
var sb = SelectBuilder.Create("select roleid,count(*) from user group by roleid order by count(*) desc");
ds = dal.Select(sb, 3, 5);
dt = dal.Query(sb, 4, 6);
n = dal.SelectCount(sb);
for (var i = 0; i < 20; i++)
{
//Console.WriteLine(i);
var sw = Stopwatch.StartNew();
list = UserX.FindAll(null, null, null, 1, 20);
u = UserX.FindByKey(1);
n = UserX.FindCount();
ds = dal.Select(sql);
ds = dal.Select(sql, CommandType.Text);
ds = dal.Select(sql, CommandType.Text, new Dictionary <String, Object>());
dt = dal.Query(sql, new Dictionary <String, Object>());
n = dal.SelectCount(sql, CommandType.Text);
ds = dal.Select(sb, 3, 5);
dt = dal.Query(sb, 4, 6);
n = dal.SelectCount(sb);
sw.Stop();
XTrace.WriteLine("{0} {1:n0}us", i, sw.Elapsed.TotalMilliseconds * 1000);
Thread.Sleep(1000);
}
}
public void ShouldReturnSelectAll1()
{
var builder = new SelectBuilder<Model.Model>().Select(model => model.All);
builder.ToString().Should().Contain("SELECT *");
}
/// <summary>执行SQL查询，返回记录集</summary>
/// <param name="builder">SQL语句</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns></returns>
public virtual DataSet Query(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
InitData();
return(Dal.Select(builder, startRowIndex, maximumRows));
}
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.");
}
/// <summary>根据条件把普通查询SQL格式化为分页SQL。</summary>
/// <remarks>
/// 因为需要继承重写的原因，在数据类中并不方便缓存分页SQL。
/// 所以在这里做缓存。
/// </remarks>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
public virtual SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
return(Dal.PageSplit(builder, startRowIndex, maximumRows));
}
/// <summary>执行SQL查询，返回记录集</summary>
/// <param name="builder">SQL语句</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns></returns>
public static DataSet Query(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows)
{
WaitForInitData();
return(DBO.Select(builder, startRowIndex, maximumRows, Meta.TableName));
}
/// <summary>查询记录数</summary>
/// <param name="builder">查询生成器</param>
/// <returns>记录数</returns>
public virtual Int32 QueryCount(SelectBuilder builder)
{
InitData();
return(GetDAL(true).SelectCount(builder));
}
/// <summary>查询记录数</summary>
/// <param name="sb">查询生成器</param>
/// <returns>记录数</returns>
public static Int32 QueryCount(SelectBuilder sb)
{
WaitForInitData();
return(DBO.SelectCount(sb, new String[] { Meta.TableName }));
}
/// <summary>查询记录数</summary>
/// <param name="builder">查询生成器</param>
/// <returns>记录数</returns>
public virtual Task <Int64> QueryCountAsync(SelectBuilder builder)
{
InitData();
return(GetDAL(true).SelectCountAsync(builder));
}
/// <summary> /// 未查询到数据时返回 null /// </summary> public abstract string SelectAsString <T>(DbSession session, SelectBuilder selectBuilder) where T : IPDMTBase, new();
