private string BuildVendorSalesDetailsQuery(VendorSalesDetailQuery query)
{
StringBuilder stringBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(query.SupplierId))
{
stringBuilder.AppendFormat(" AND d.SupplierId={0}", query.SupplierId);
}
if (!string.IsNullOrEmpty(query.OrderId))
{
stringBuilder.AppendFormat(" AND a.OrderId like '%{0}%'", query.OrderId);
}
if (!string.IsNullOrEmpty(query.ProductCode))
{
stringBuilder.AppendFormat(" AND c.ProductCode like '%{0}%'", query.ProductCode);
}
if (!string.IsNullOrEmpty(query.ProductName))
{
stringBuilder.AppendFormat(" AND c.ProductName like '%{0}%'", query.ProductName);
}
if (query.FromDate.HasValue)
{
stringBuilder.AppendFormat(" AND PayDate >= '{0}'", DataHelper.GetSafeDateTimeFormat(query.FromDate.Value));
}
if (query.ToDate.HasValue)
{
stringBuilder.AppendFormat(" AND PayDate <= '{0}'", DataHelper.GetSafeDateTimeFormat(query.ToDate.Value));
}
return(stringBuilder.ToString());
}
/// <summary>
/// 获取供应商品订单交易明细
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DbQueryResult GetVendorSalesDetail(VendorSalesDetailQuery query)
{
DbQueryResult result;
if (null == query)
{
result = new DbQueryResult();
}
else
{
DbQueryResult dbQueryResult = new DbQueryResult();
StringBuilder stringBuilder = new StringBuilder();
int StartIndex = (query.PageIndex - 1) * query.PageSize;
int EndIndex = query.PageIndex * query.PageSize;
string text = this.BuildVendorSalesDetailsQuery(query);
stringBuilder.AppendFormat(@"select * from (select
Row_Number() OVER (ORDER BY PayDate desc) 'JournalNumber',
a.OrderId,
c.ProductName,
c.ProductCode,
b.ItemAdjustedPrice,
d.SupplierName,
a.PayDate from dbo.Ecshop_Orders as a
left join Ecshop_OrderItems as b on a.OrderId=b.OrderId
left join Ecshop_Products as c on b.ProductId=c.ProductId
inner join Ecshop_Supplier as d on c.SupplierId=d.SupplierId
where OrderStatus<10 and OrderStatus!=1 and OrderStatus!=4
{0}
) as c where JournalNumber>{1} and JournalNumber<{2}", text, StartIndex, EndIndex);
if (query.IsCount)
{
stringBuilder.AppendFormat(@";
select COUNT(1) from dbo.Ecshop_Orders as a
left join Ecshop_OrderItems as b on a.OrderId=b.OrderId
left join Ecshop_Products as c on b.ProductId=c.ProductId
inner join Ecshop_Supplier as d on c.SupplierId=d.SupplierId
where OrderStatus<10 and OrderStatus!=1 and OrderStatus!=4 {0} ", text);
}
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader);
if (query.IsCount && dataReader.NextResult())
{
dataReader.Read();
dbQueryResult.TotalRecords = dataReader.GetInt32(0);
}
}
result = dbQueryResult;
}
return(result);
}
public static DbQueryResult GetVendorSalesDetail(VendorSalesDetailQuery query)
{
return(new BalanceDetailDao().GetVendorSalesDetail(query));
}
