public JsonResult LoadStatStorageAndForceFeeGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string customerName, string warehouse, string isConsigning)
{
//读取数据
string strErrText;
StockSystem stock = new StockSystem();
List<StatStorageAndForceFee> listFee = stock.LoadStatStorageAndForceFeeByConditions(startTime, endTime, customerName, warehouse, isConsigning, LoginAccountId, LoginStaffName, out strErrText);
if (listFee == null)
{
throw new Exception(strErrText);
}
//提取当前页面数据
int nTotalRows = listFee.Count;
int nPageIndex = page;
int nPageSize = rows;
int nTotalPages = nTotalRows / nPageSize;
if (nTotalRows % nPageSize > 0)
nTotalPages++;
string sortExpression = (sidx ?? "Id") + " " + (sord ?? "ASC");
var data = listFee.OrderBy(sortExpression).Skip((nPageIndex - 1) * nPageSize).Take(nPageSize).ToList();
//生成表格数据
var ret = new
{
total = nTotalPages,
page = nPageIndex,
records = nTotalRows,
rows = (
from f in data
select new
{
id = f.Id,
cell = new string[] {
f.Id.ToString(),
f.CustomerName,
f.CreateTime,
f.TotalDeliverEnterWarehouseTunnages > 0 ? f.TotalDeliverEnterWarehouseTunnages.ToString("#0.######") : string.Empty,
f.TotalDeliverEnterWarehousePiles > 0 ? f.TotalDeliverEnterWarehousePiles.ToString("#0.######") : string.Empty,
f.UnloadingForceFeePrice.ToString(),
f.DeliverEnterWarehouseForceFee > 0 ? f.DeliverEnterWarehouseForceFee.ToString() : string.Empty,
f.TotalAllocateEnterWarehouseTunnages > 0 ? f.TotalAllocateEnterWarehouseTunnages.ToString("#0.######") : string.Empty,
f.TotalDeliverOutWarehouseTunnages > 0 ? f.TotalDeliverOutWarehouseTunnages.ToString("#0.######") : string.Empty,
f.TotalDeliverOutWarehousePiles > 0 ? f.TotalDeliverOutWarehousePiles.ToString("#0.######") : string.Empty,
f.LoadingForceFeePrice.ToString(),
f.DeliverOutWarehouseForceFee > 0 ? f.DeliverOutWarehouseForceFee.ToString() : string.Empty,
f.TotalAllocateOutWarehouseTunnages > 0 ? f.TotalAllocateOutWarehouseTunnages.ToString("#0.######") : string.Empty,
f.AllocateOutWarehouseForceFee > 0 ? f.AllocateOutWarehouseForceFee.ToString() : string.Empty,
f.BalanceTunnages > 0 ? f.BalanceTunnages.ToString("#0.######") : string.Empty,
f.BalancePiles > 0 ? f.BalancePiles.ToString("#0.######") : string.Empty,
f.StorageFeePrice.ToString(),
f.StorageFee > 0 ? f.StorageFee.ToString() : string.Empty
}
}).ToArray(),
userdata = new
{
CreateTime = InnoSoft.LS.Resources.Labels.Total,
TotalDeliverEnterWarehouseTunnages = data.Sum(s => s.TotalDeliverEnterWarehouseTunnages),
TotalDeliverEnterWarehousePiles = data.Sum(s => s.TotalDeliverEnterWarehousePiles),
DeliverEnterWarehouseForceFee = data.Sum(s => s.DeliverEnterWarehouseForceFee),
TotalAllocateEnterWarehouseTunnages = data.Sum(s => s.TotalAllocateEnterWarehouseTunnages),
TotalDeliverOutWarehouseTunnages = data.Sum(s => s.TotalDeliverOutWarehouseTunnages),
TotalDeliverOutWarehousePiles = data.Sum(s => s.TotalDeliverOutWarehousePiles),
DeliverOutWarehouseForceFee = data.Sum(s => s.DeliverOutWarehouseForceFee),
TotalAllocateOutWarehouseTunnages = data.Sum(s => s.TotalAllocateOutWarehouseTunnages),
AllocateOutWarehouseForceFee = data.Sum(s => s.AllocateOutWarehouseForceFee),
BalanceTunnages = data.Count > 0 ? data[data.Count - 1].BalanceTunnages : 0,
BalancePiles = data.Count > 0 ? data[data.Count - 1].BalancePiles : 0,
StorageFee = data.Sum(s => s.StorageFee)
}
};
return Json(ret, JsonRequestBehavior.AllowGet);
}
public ActionResult ExportStorageAndForceFee()
{
#region 生成数据源
//读取条件
var request = HttpContext.Request;
string strStartTime = request.QueryString["startTime"] ?? string.Empty;
string strEndTime = request.QueryString["endTime"] ?? string.Empty;
string strCustomerName = request.QueryString["customerName"] ?? string.Empty;
string strWarehouse = request.QueryString["warehouse"] ?? string.Empty;
string strIsConsigning = request.QueryString["isConsigning"] ?? "false";
//读取数据
string strErrText;
StockSystem stock = new StockSystem();
List<StatStorageAndForceFee> listFee = stock.LoadStatStorageAndForceFeeByConditions(strStartTime, strEndTime, strCustomerName, strWarehouse, strIsConsigning, LoginAccountId, LoginStaffName, out strErrText);
if (listFee == null)
{
throw new Exception(strErrText);
}
//生成结果数据
List<StatStorageAndForceFee> listRet = new List<StatStorageAndForceFee>();
{
decimal decBalanceTunnages = 0;
decimal decBalancePiles = 0;
//按客户分组
var grpCustomerNames = listFee.GroupBy(f => f.CustomerName).OrderBy(f => f.Key);
foreach (var grpCustomerName in grpCustomerNames)
{
List<StatStorageAndForceFee> data = grpCustomerName.ToList<StatStorageAndForceFee>();
listRet.AddRange(data);
//生成小计行
StatStorageAndForceFee subtotal = new StatStorageAndForceFee();
subtotal.CustomerName = grpCustomerName.Key;
subtotal.CreateTime = InnoSoft.LS.Resources.Labels.Subtotal;
subtotal.TotalDeliverEnterWarehouseTunnages = data.Sum(s => s.TotalDeliverEnterWarehouseTunnages);
subtotal.TotalDeliverEnterWarehousePiles = data.Sum(s => s.TotalDeliverEnterWarehousePiles);
subtotal.DeliverEnterWarehouseForceFee = data.Sum(s => s.DeliverEnterWarehouseForceFee);
subtotal.TotalAllocateEnterWarehouseTunnages = data.Sum(s => s.TotalAllocateEnterWarehouseTunnages);
subtotal.TotalDeliverOutWarehouseTunnages = data.Sum(s => s.TotalDeliverOutWarehouseTunnages);
subtotal.TotalDeliverOutWarehousePiles = data.Sum(s => s.TotalDeliverOutWarehousePiles);
subtotal.DeliverOutWarehouseForceFee = data.Sum(s => s.DeliverOutWarehouseForceFee);
subtotal.TotalAllocateOutWarehouseTunnages = data.Sum(s => s.TotalAllocateOutWarehouseTunnages);
subtotal.AllocateOutWarehouseForceFee = data.Sum(s => s.AllocateOutWarehouseForceFee);
subtotal.BalanceTunnages = data.Last().BalanceTunnages;
subtotal.BalancePiles = data.Last().BalancePiles;
subtotal.StorageFee = data.Sum(s => s.StorageFee);
listRet.Add(subtotal);
decBalanceTunnages += subtotal.BalanceTunnages;
decBalancePiles += subtotal.BalancePiles;
}
//生成合计行
StatStorageAndForceFee total = new StatStorageAndForceFee();
total.CustomerName = InnoSoft.LS.Resources.Labels.Total;
total.TotalDeliverEnterWarehouseTunnages = listFee.Sum(s => s.TotalDeliverEnterWarehouseTunnages);
total.TotalDeliverEnterWarehousePiles = listFee.Sum(s => s.TotalDeliverEnterWarehousePiles);
total.DeliverEnterWarehouseForceFee = listFee.Sum(s => s.DeliverEnterWarehouseForceFee);
total.TotalAllocateEnterWarehouseTunnages = listFee.Sum(s => s.TotalAllocateEnterWarehouseTunnages);
total.TotalDeliverOutWarehouseTunnages = listFee.Sum(s => s.TotalDeliverOutWarehouseTunnages);
total.TotalDeliverOutWarehousePiles = listFee.Sum(s => s.TotalDeliverOutWarehousePiles);
total.DeliverOutWarehouseForceFee = listFee.Sum(s => s.DeliverOutWarehouseForceFee);
total.TotalAllocateOutWarehouseTunnages = listFee.Sum(s => s.TotalAllocateOutWarehouseTunnages);
total.AllocateOutWarehouseForceFee = listFee.Sum(s => s.AllocateOutWarehouseForceFee);
total.BalanceTunnages = decBalanceTunnages;
total.BalancePiles = decBalancePiles;
total.StorageFee = listFee.Sum(s => s.StorageFee);
listRet.Add(total);
}
#endregion
#region 导出Excel
#region 创建Excel工作簿
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
#endregion
#region 设置列宽度
sheet.SetColumnWidth(0, 20 * 256);//客户名称
sheet.SetColumnWidth(1, 12 * 256);//日期
sheet.SetColumnWidth(2, 12 * 256);//总计移仓入库吨数
sheet.SetColumnWidth(3, 12 * 256);//总计移仓入库垛数
sheet.SetColumnWidth(4, 7 * 256);//下力支费价格
sheet.SetColumnWidth(5, 10 * 256);//移仓入库力支费
sheet.SetColumnWidth(6, 12 * 256);//总计划拨入库吨数
sheet.SetColumnWidth(7, 12 * 256);//总计发货出库吨数
sheet.SetColumnWidth(8, 12 * 256);//总计发货出库垛数
sheet.SetColumnWidth(9, 7 * 256);//上力支费价格
sheet.SetColumnWidth(10, 10 * 256);//发货出库力支费
sheet.SetColumnWidth(11, 12 * 256);//总计划拨出库吨数
sheet.SetColumnWidth(12, 10 * 256);//划拨出库力支费
sheet.SetColumnWidth(13, 12 * 256);//结存吨数
sheet.SetColumnWidth(14, 12 * 256);//结存垛数
sheet.SetColumnWidth(15, 7 * 256);//仓储费价格
sheet.SetColumnWidth(16, 10 * 256);//仓储费
#endregion
#region 设置单元格样式
ICellStyle styleLeft = workbook.CreateCellStyle();
styleLeft.Alignment = HorizontalAlignment.LEFT;
styleLeft.VerticalAlignment = VerticalAlignment.CENTER;
styleLeft.BorderBottom = CellBorderType.THIN;
styleLeft.BorderLeft = CellBorderType.THIN;
styleLeft.BorderRight = CellBorderType.THIN;
styleLeft.BorderTop = CellBorderType.THIN;
styleLeft.WrapText = true;
ICellStyle styleCenter = workbook.CreateCellStyle();
styleCenter.Alignment = HorizontalAlignment.CENTER;
styleCenter.VerticalAlignment = VerticalAlignment.CENTER;
styleCenter.BorderBottom = CellBorderType.THIN;
styleCenter.BorderLeft = CellBorderType.THIN;
styleCenter.BorderRight = CellBorderType.THIN;
styleCenter.BorderTop = CellBorderType.THIN;
styleCenter.WrapText = true;
ICellStyle styleRight = workbook.CreateCellStyle();
styleRight.Alignment = HorizontalAlignment.RIGHT;
styleRight.VerticalAlignment = VerticalAlignment.CENTER;
styleRight.BorderBottom = CellBorderType.THIN;
styleRight.BorderLeft = CellBorderType.THIN;
styleRight.BorderRight = CellBorderType.THIN;
styleRight.BorderTop = CellBorderType.THIN;
styleRight.WrapText = true;
#endregion
#region 创建表头
//表头
var rowHeader = sheet.CreateRow(0);
rowHeader.HeightInPoints = 40;
rowHeader.CreateCell(0).SetCellValue(InnoSoft.LS.Resources.Labels.CustomerName);//客户名称
rowHeader.GetCell(0).CellStyle = styleCenter;
rowHeader.CreateCell(1).SetCellValue(InnoSoft.LS.Resources.Labels.Date);//日期
rowHeader.GetCell(1).CellStyle = styleCenter;
rowHeader.CreateCell(2).SetCellValue(InnoSoft.LS.Resources.Labels.TotalDeliverEnterWarehouse + InnoSoft.LS.Resources.Labels.Tunnages);//总计移仓入库吨数
rowHeader.GetCell(2).CellStyle = styleCenter;
rowHeader.CreateCell(3).SetCellValue(InnoSoft.LS.Resources.Labels.TotalDeliverEnterWarehouse + InnoSoft.LS.Resources.Labels.Piles);//总计移仓入库垛数
rowHeader.GetCell(3).CellStyle = styleCenter;
rowHeader.CreateCell(4).SetCellValue(InnoSoft.LS.Resources.Labels.UnloadingForceFeePrice);//下力支费价格
rowHeader.GetCell(4).CellStyle = styleCenter;
rowHeader.CreateCell(5).SetCellValue(InnoSoft.LS.Resources.Labels.DeliverEnterWarehouseForceFee);//移仓入库力支费
rowHeader.GetCell(5).CellStyle = styleCenter;
rowHeader.CreateCell(6).SetCellValue(InnoSoft.LS.Resources.Labels.TotalAllocateEnterWarehouseTunnages);//总计划拨入库吨数
rowHeader.GetCell(6).CellStyle = styleCenter;
rowHeader.CreateCell(7).SetCellValue(InnoSoft.LS.Resources.Labels.TotalDeliverOutWarehouse + InnoSoft.LS.Resources.Labels.Tunnages);//总计发货出库吨数
rowHeader.GetCell(7).CellStyle = styleCenter;
rowHeader.CreateCell(8).SetCellValue(InnoSoft.LS.Resources.Labels.TotalDeliverOutWarehouse + InnoSoft.LS.Resources.Labels.Piles);//总计发货出库垛数
rowHeader.GetCell(8).CellStyle = styleCenter;
rowHeader.CreateCell(9).SetCellValue(InnoSoft.LS.Resources.Labels.LoadingForceFeePrice);//上力支费价格
rowHeader.GetCell(9).CellStyle = styleCenter;
rowHeader.CreateCell(10).SetCellValue(InnoSoft.LS.Resources.Labels.DeliverOutWarehouseForceFee);//发货出库力支费
rowHeader.GetCell(10).CellStyle = styleCenter;
rowHeader.CreateCell(11).SetCellValue(InnoSoft.LS.Resources.Labels.TotalAllocateOutWarehouseTunnages);//总计划拨出库吨数
rowHeader.GetCell(11).CellStyle = styleCenter;
rowHeader.CreateCell(12).SetCellValue(InnoSoft.LS.Resources.Labels.AllocateOutWarehouseForceFee);//划拨出库力支费
rowHeader.GetCell(12).CellStyle = styleCenter;
rowHeader.CreateCell(13).SetCellValue(InnoSoft.LS.Resources.Labels.BalanceTunnages);//结存吨数
rowHeader.GetCell(13).CellStyle = styleCenter;
rowHeader.CreateCell(14).SetCellValue(InnoSoft.LS.Resources.Labels.BalancePiles);//结存垛数
rowHeader.GetCell(14).CellStyle = styleCenter;
rowHeader.CreateCell(15).SetCellValue(InnoSoft.LS.Resources.Labels.StorageFeePrice);//仓储费价格
rowHeader.GetCell(15).CellStyle = styleCenter;
rowHeader.CreateCell(16).SetCellValue(InnoSoft.LS.Resources.Labels.StorageFee);//仓储费
rowHeader.GetCell(16).CellStyle = styleCenter;
#endregion
#region 创建表体
int iRow = 1;
foreach (var ret in listRet)
{
var rowBody = sheet.CreateRow(iRow++);
rowBody.HeightInPoints = 20;
rowBody.CreateCell(0).SetCellValue(ret.CustomerName);//客户名称
rowBody.GetCell(0).CellStyle = styleLeft;
rowBody.CreateCell(1).SetCellValue(ret.CreateTime);//日期
rowBody.GetCell(1).CellStyle = styleCenter;
rowBody.CreateCell(2).SetCellValue(ret.TotalDeliverEnterWarehouseTunnages > 0 ? ret.TotalDeliverEnterWarehouseTunnages.ToString("#0.######") : string.Empty);//总计移仓入库吨数
rowBody.GetCell(2).CellStyle = styleCenter;
rowBody.CreateCell(3).SetCellValue(ret.TotalDeliverEnterWarehousePiles > 0 ? ret.TotalDeliverEnterWarehousePiles.ToString("#0.######") : string.Empty);//总计移仓入库垛数
rowBody.GetCell(3).CellStyle = styleCenter;
rowBody.CreateCell(4).SetCellValue(ret.UnloadingForceFeePrice > 0 ? ret.UnloadingForceFeePrice.ToString("N") : string.Empty);//下力支费价格
rowBody.GetCell(4).CellStyle = styleRight;
rowBody.CreateCell(5).SetCellValue(ret.DeliverEnterWarehouseForceFee > 0 ? ret.DeliverEnterWarehouseForceFee.ToString("N") : string.Empty);//移仓入库力支费
rowBody.GetCell(5).CellStyle = styleRight;
rowBody.CreateCell(6).SetCellValue(ret.TotalAllocateEnterWarehouseTunnages > 0 ? ret.TotalAllocateEnterWarehouseTunnages.ToString("#0.######") : string.Empty);//总计划拨入库吨数
rowBody.GetCell(6).CellStyle = styleCenter;
rowBody.CreateCell(7).SetCellValue(ret.TotalDeliverOutWarehouseTunnages > 0 ? ret.TotalDeliverOutWarehouseTunnages.ToString("#0.######") : string.Empty);//总计发货出库吨数
rowBody.GetCell(7).CellStyle = styleCenter;
rowBody.CreateCell(8).SetCellValue(ret.TotalDeliverOutWarehousePiles > 0 ? ret.TotalDeliverOutWarehousePiles.ToString("#0.######") : string.Empty);//总计发货出库垛数
rowBody.GetCell(8).CellStyle = styleCenter;
rowBody.CreateCell(9).SetCellValue(ret.LoadingForceFeePrice > 0 ? ret.LoadingForceFeePrice.ToString("N") : string.Empty);//上力支费价格
rowBody.GetCell(9).CellStyle = styleRight;
rowBody.CreateCell(10).SetCellValue(ret.DeliverOutWarehouseForceFee > 0 ? ret.DeliverOutWarehouseForceFee.ToString("N") : string.Empty);//发货出库力支费
rowBody.GetCell(10).CellStyle = styleRight;
rowBody.CreateCell(11).SetCellValue(ret.TotalAllocateOutWarehouseTunnages > 0 ? ret.TotalAllocateOutWarehouseTunnages.ToString("#0.######") : string.Empty);//总计划拨出库吨数
rowBody.GetCell(11).CellStyle = styleCenter;
rowBody.CreateCell(12).SetCellValue(ret.AllocateOutWarehouseForceFee > 0 ? ret.AllocateOutWarehouseForceFee.ToString("N") : string.Empty);//划拨出库力支费
rowBody.GetCell(12).CellStyle = styleRight;
rowBody.CreateCell(13).SetCellValue(ret.BalanceTunnages > 0 ? ret.BalanceTunnages.ToString("#0.######") : string.Empty);//结存吨数
rowBody.GetCell(13).CellStyle = styleCenter;
rowBody.CreateCell(14).SetCellValue(ret.BalancePiles > 0 ? ret.BalancePiles.ToString("#0.######") : string.Empty);//结存垛数
rowBody.GetCell(14).CellStyle = styleCenter;
rowBody.CreateCell(15).SetCellValue(ret.StorageFeePrice > 0 ? ret.StorageFeePrice.ToString("N") : string.Empty);//仓储费价格
rowBody.GetCell(15).CellStyle = styleRight;
rowBody.CreateCell(16).SetCellValue(ret.StorageFee > 0 ? ret.StorageFee.ToString("N") : string.Empty);//仓储费
rowBody.GetCell(16).CellStyle = styleRight;
}
#endregion
#region 输出Excel文件
MemoryStream output = new MemoryStream();
workbook.Write(output);
return File(output.ToArray(), "application/vnd.ms-excel", "StorageAndForceFee.xls");
#endregion
#endregion
}
