public JsonResult QuerySingleProductSale(Pager page, SearchSingleProductSale condition)
{
var rows = _saleOrderQuery.QuerySingleProductSale(page, condition);
return(Json(new { success = true, data = rows, total = page.Total }));
}
public IEnumerable <SingleProductSaleDto> QuerySingleProductSale(Pager page, SearchSingleProductSale condition)
{
dynamic param = new ExpandoObject();
string where = "";
string pwhere = "";
//单品查询，必须输入编码或条码，否则立即返回空
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
pwhere += @"and ( p.code=@ProductCodeOrBarCode or p.barcode =@ProductCodeOrBarCode ) ";
param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode;
}
else
{
return(new List <SingleProductSaleDto>());
}
if (condition.StartDate.HasValue)
{
where += "and o.UpdatedOn >=@StartDate ";
param.StartDate = condition.StartDate.Value;
}
if (condition.EndDate.HasValue)
{
where += "and o.UpdatedOn < @EndDate ";
param.EndDate = condition.EndDate.Value.AddDays(1);
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
where += "and o.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();
}
string sql = @"select s.name as StoreName,p.Id as ProductId, p.`Name` as ProductName,p.`Code` as ProductCode,p.BarCode,SaleQuantity,SaleCostAmount,SaleAmount from (
select o.StoreId,i.ProductId,sum(i.Quantity) as SaleQuantity,sum(i.AvgCostPrice* i.Quantity) as SaleCostAmount,sum(i.RealPrice* i.Quantity) as SaleAmount
from saleorder o inner join saleorderitem i on o.id =i.SaleOrderId
where o.`Status` = 3 {0}
GROUP BY o.StoreId,i.ProductId ) t
left join product p on p.id = t.ProductId
left join store s on s.id = t.storeid where 1=1 {3} LIMIT {1},{2}";
//rows = this._query.FindPage<ProductDto>(page.PageIndex, page.PageSize).Where<Product>(where, param);
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize, pwhere);
var rows = this._query.FindAll <SingleProductSaleDto>(sql, param);
string sqlCount = @"select count(*) from (
select o.StoreId,i.ProductId,sum(i.Quantity) as SaleQuantity,sum(i.AvgCostPrice* i.Quantity) as SaleCostAmount,sum(i.RealPrice* i.Quantity) as SaleAmount
from saleorder o inner join saleorderitem i on o.id =i.SaleOrderId
where o.`Status` = 3 {0}
GROUP BY o.StoreId,i.ProductId ) t
left join product p on p.id = t.ProductId
left join store s on s.id = t.storeid where 1=1 {1}";
sqlCount = string.Format(sqlCount, where, pwhere);
page.Total = this._query.Context.ExecuteScalar <int>(sqlCount, param);
return(rows);
}
