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