public ActionResult QueryPurchaseSaleInventorySummary(Pager page, PurchaseSaleInventorySearch condition) { var rows = _reportQuery.QueryPurchaseSaleInventorySummary(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 <PurchaseSaleInventoryDto> QueryPurchaseSaleInventorySummary(Pager page, PurchaseSaleInventorySearch condition) { dynamic param = new ExpandoObject(); string where = ""; if (!string.IsNullOrEmpty(condition.StoreId)) { where += "and t.StoreId in(" + condition.StoreId + ")"; // param.StoreId = condition.StoreId; } param.YearMonth = int.Parse(string.Format("{0}{1}", condition.Year, condition.Month.ToString().PadLeft(2, '0'))); // 此处多显示起止时间,主要是为了让前端表格框架,连接明细时能传递时间参数 string sql = @"select * from purchasesaleinventory t 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 <PurchaseSaleInventoryDto>(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 purchasesaleinventory t 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); }