public ActionResult LoadData(Pager page, SearchStoreInventory condition)
{
if (string.IsNullOrEmpty(condition.StoreId) || condition.StoreId == "0")
{
condition.StoreId = _context.CurrentAccount.CanViewStores;
}
var rows = _storeInventoryQuery.GetPageList(page, condition);
if (page.toExcel)
{
var data = _excelService.WriteToExcelStream(rows.ToList(), ExcelVersion.Above2007, false, true).ToArray();
var fileName = string.Format("库存_{0}.xlsx", DateTime.Now.ToString("yyyyMMdd"));
return(File(data, "application/ms-excel", fileName));
}
return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns }));
}
public IEnumerable <ProductQueryDto> QueryProduct(SearchStoreInventory condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
where += "and (p.BarCode=@ProductCodeOrBarCode or p.`Code`=@ProductCodeOrBarCode )";
param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode;
}
if (!string.IsNullOrEmpty(condition.ProductName))
{
where += "and p.Name like @ProductName ";
param.ProductName = string.Format("%{0}%", condition.ProductName);
}
//if (condition.StoreId > 0)
//{
// where += "and t.Id=@StoreId ";
// param.StoreId = condition.StoreId;
//}
string sql = @"select b.ProductId, p.`Name` as ProductName,p.`Code` as ProductCode,p.BarCode,p.Specification,p.Unit,p.SalePrice,
b.Quantity as BatchQuantity,s.Name as supplierName,t.`Name` as StoreName,b.Price,sp.StoreSalePrice,v.SalePrice as VipSalePrice
from ( select i.storeid,i.supplierId,i.productId,i.Price,sum(i.quantity) as Quantity from storeinventorybatch i group by i.storeid,i.supplierId,i.productId,i.Price ) b
left join product p on b.ProductId = p.Id
left join supplier s on s.Id = b.SupplierId
left join store t on t.Id = b.StoreId
left join storeinventory sp on sp.ProductId = p.Id and sp.StoreId = b.storeid
left join vipproduct v on v.ProductId = p.Id
where 1=1 and b.Quantity>0 {0} order by b.StoreId"; //b.Quantity>0
if (string.IsNullOrEmpty(where))
{
return(new List <ProductQueryDto>());
}
sql = string.Format(sql, where);
var rows = _query.FindAll <ProductQueryDto>(sql, param);
//设置当前件规
return(rows);
}
public JsonResult QueryProduct(SearchStoreInventory condition)
{
var rows = _storeInventoryQuery.QueryProduct(condition).ToList();
return(Json(new { success = true, data = rows, total = rows.Count }));
}
