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)); }