public IList <OrderRecordDetails> GetPageList(int userId,
int?shopId, int?orderStateId, int start, int end, out int total)
{
const string sql = @"
SELECT COUNT(1) AS Total
FROM OrderRecord ORT
WHERE ORT.UserId = @UserId{0}
SELECT T.Id ,
T.UserId ,
T.UserShopId ,
T.UserShopWangWangAccount ,
T.ClientUserId ,
T.ClientUserAccount ,
T.ClientUserSubAccountId ,
T.ClientUserSubAccount ,
T.ClientUserLogin ,
T.OrderIp ,
T.OrderNum ,
T.OrderStateId ,
T.OrderTypeId ,
T.StartDateTime ,
T.LastUpdateDateTime
FROM ( SELECT ORT.Id ,
ORT.UserId ,
ORT.UserShopId ,
ST.WangWangAccount AS UserShopWangWangAccount ,
ORT.ClientUserId ,
UT.Account AS ClientUserAccount ,
ORT.ClientUserSubAccountId ,
USAT.TaoBaoAccount AS ClientUserSubAccount ,
ULST.ClientLogin AS ClientUserLogin ,
ULST.ClientLastLoginIpAddress AS OrderIp ,
ORT.OrderNum ,
ORT.OrderStateId ,
ORT.OrderTypeId ,
ORT.StartDateTime ,
ORT.LastUpdateDateTime ,
ROW_NUMBER() OVER ( ORDER BY ORT.OrderStateId, ORT.Id DESC ) AS RowNum
FROM OrderRecord ORT
LEFT JOIN Shop ST ON ST.Id = ORT.UserShopId
LEFT JOIN [User] UT ON UT.Id = ClientUserId
LEFT JOIN UserSubAccount USAT ON USAT.Id = ORT.ClientUserSubAccountId
LEFT JOIN UserLoginState ULST ON ULST.UserId = ORT.ClientUserId
WHERE ORT.UserId = @UserId{0}
) T
WHERE T.RowNum BETWEEN @Start AND @End";
StringBuilder querySb = new StringBuilder();
if (shopId.HasValue)
{
querySb.Append(" AND ORT.UserShopId = @UserShopId");
}
if (orderStateId.HasValue)
{
querySb.Append(" AND ORT.OrderStateId = @OrderStateId");
}
string sqlTmp = string.Format(sql, querySb.ToString());
using (var con = DbFactory.CreateConnection())
{
var reader = con.QueryMultiple(sqlTmp, new
{
UserId = userId,
UserShopId = shopId ?? 0,
OrderStateId = orderStateId ?? 0,
Start = start,
End = end
});
total = reader.Read <int>().First();
return(reader.Read <OrderRecordDetails>().ToList());
}
}