private string BuildVendorSalesReportQueryQuery(VendorSalesReportQuery query) { StringBuilder stringBuilder = new StringBuilder(); if (!string.IsNullOrEmpty(query.SupplierName)) { stringBuilder.AppendFormat(" AND SupplierName like '%{0}%'", query.SupplierName); } if (query.StartDate.HasValue) { stringBuilder.AppendFormat(" AND PayDate >= '{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value)); } if (query.EndDate.HasValue) { stringBuilder.AppendFormat(" AND PayDate <= '{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value)); } return(stringBuilder.ToString()); }
public DbQueryResult GetVendorSalesReport(VendorSalesReportQuery query) { DbQueryResult result; if (null == query) { result = new DbQueryResult(); } else { DbQueryResult dbQueryResult = new DbQueryResult(); StringBuilder stringBuilder = new StringBuilder(); string text = this.BuildVendorSalesReportQueryQuery(query); int StartIndex = (query.PageIndex - 1) * query.PageSize; int EndIndex = query.PageIndex * query.PageSize; stringBuilder.AppendFormat(@"select * from ( select COUNT(1) as 'OrderCount',SUM(ItemAdjustedPrice) as 'OrderTotal',SupplierName as 'SupplierName',SupplierId, Row_Number() OVER (ORDER BY SupplierName desc) 'JournalNumber' from ( select distinct a.OrderId,b.ItemAdjustedPrice*Quantity as 'ItemAdjustedPrice',c.SupplierId,d.SupplierName 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 != 9 and OrderStatus!=1 and OrderStatus!=4 and OrderStatus!=98) {0} ) as c group by SupplierName,SupplierId ) as RS where JournalNumber>{1} and JournalNumber<{2}", text, StartIndex, EndIndex); if (query.IsCount) { stringBuilder.AppendFormat(@";select count(1) from ( select COUNT(1) as 'OrderCount',SUM(ItemAdjustedPrice) as 'OrderTotal',SupplierName as 'SupplierName', Row_Number() OVER (partition by SupplierName ORDER BY SupplierName desc) 'JournalNumber' from ( select distinct a.OrderId,b.ItemAdjustedPrice*Quantity as 'ItemAdjustedPrice',c.SupplierId,d.SupplierName 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 != 9 and OrderStatus!=1 and OrderStatus!=4 and OrderStatus!=98) {0} ) as c group by SupplierName ) as RS ", 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 GetVendorSalesReport(VendorSalesReportQuery query) { return(new BalanceDetailDao().GetVendorSalesReport(query)); }