public ActionResult QueryPurchaseSaleInventoryDetail(Pager page, PurchaseSaleInventoryDetailSearch condition) { if (string.IsNullOrEmpty(condition.StoreId) || condition.StoreId == "0") { condition.StoreId = _context.CurrentAccount.CanViewStores; } var rows = _reportQuery.QueryPurchaseSaleInventoryDetail(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 <PurchaseSaleInventoryDetailDto> QueryPurchaseSaleInventoryDetail(Pager page, PurchaseSaleInventoryDetailSearch condition) { dynamic param = new ExpandoObject(); string where = ""; if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0") { where += "and t.StoreId in @StoreId "; param.StoreId = condition.StoreId.Split(',').ToIntArray(); } if (!string.IsNullOrEmpty(condition.productName)) { where += "and p.Name like @ProductName "; param.ProductName = string.Format("%{0}%", condition.productName); } param.YearMonth = int.Parse(string.Format("{0}{1}", condition.Year, condition.Month.ToString().PadLeft(2, '0'))); if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode)) { where += "and (p.`Code` = @ProductCodeOrBarCode or p.BarCode =@ProductCodeOrBarCode) "; param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode; } if (!string.IsNullOrEmpty(condition.CategoryId)) { where += "and c.Id like @CategoryId "; param.CategoryId = string.Format("{0}%", condition.CategoryId); } string sql = @"select p.Name as ProductName,p.code as ProductCode,p.BarCode,p.Specification,s.Name as StoreName ,c.FullName as CategoryName,t.* from purchasesaleinventoryDetail t left join product p on p.Id=t.productId left join store s on s.Id= t.StoreId left join category c on c.Id =p.categoryId where YearMonth=@YearMonth {0} "; if (!page.toExcel) { sql += string.Format(" LIMIT {0},{1}", (page.PageIndex - 1) * page.PageSize, page.PageSize); } sql = string.Format(sql, where); var rows = this._query.FindAll <PurchaseSaleInventoryDetailDto>(sql, param); //求和 string sqlSum = @"select count(*) as TotalCount,sum(PreInventoryQuantity) as PreInventoryQuantity,sum(PreInventoryAmount) as PreInventoryAmount,sum(PurchaseQuantity) as PurchaseQuantity,sum(PurchaseAmount) as PurchaseAmount,sum(SaleQuantity) as SaleQuantity,sum(SaleCostAmount) as SaleCostAmount,sum(SaleAmount) as SaleAmount,sum(EndInventoryQuantity) as EndInventoryQuantity,sum(EndInventoryAmount) as EndInventoryAmount from purchasesaleinventoryDetail t left join product p on p.Id=t.productId left join store s on s.Id= t.StoreId left join category c on c.Id =p.categoryId where YearMonth=@YearMonth {0}"; sqlSum = string.Format(sqlSum, where); var sumStoreInventory = this._query.Find <SumPurchaseSaleInventory>(sqlSum, param) as SumPurchaseSaleInventory; page.Total = sumStoreInventory.TotalCount; page.SumColumns.Add(new SumColumn("PreInventoryQuantity", sumStoreInventory.PreInventoryQuantity.ToString())); page.SumColumns.Add(new SumColumn("PreInventoryAmount", sumStoreInventory.PreInventoryAmount.ToString("F4"))); page.SumColumns.Add(new SumColumn("PurchaseQuantity", sumStoreInventory.PurchaseQuantity.ToString())); page.SumColumns.Add(new SumColumn("PurchaseAmount", sumStoreInventory.PurchaseAmount.ToString("F4"))); page.SumColumns.Add(new SumColumn("SaleQuantity", sumStoreInventory.SaleQuantity.ToString())); page.SumColumns.Add(new SumColumn("SaleCostAmount", sumStoreInventory.SaleCostAmount.ToString("F4"))); page.SumColumns.Add(new SumColumn("SaleAmount", sumStoreInventory.SaleAmount.ToString("F2"))); page.SumColumns.Add(new SumColumn("EndInventoryQuantity", sumStoreInventory.EndInventoryQuantity.ToString())); page.SumColumns.Add(new SumColumn("EndInventoryAmount", sumStoreInventory.EndInventoryAmount.ToString("F4"))); page.SumColumns.Add(new SumColumn("ProfitAmount", sumStoreInventory.ProfitAmount.ToString())); page.SumColumns.Add(new SumColumn("ProfitPercent", sumStoreInventory.ProfitPercent.ToString("F2") + "%")); return(rows); }