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