/// <summary>
/// 获取Export表数据
/// <summary>
/// <param name="queryJson">查询参数</param>
/// <returns></returns>
public FileResult Export2(Pagination pagination, string queryJson)
{
pagination.page = 1;
pagination.records = 0;
pagination.rows = 999999;
pagination.sidx = "";
pagination.sord = "ASC";
var datas = materialsSumIBLL.GetInventoryDetail(pagination, queryJson);
var dt = AsDataTable(datas);
var queryParam = queryJson.ToJObject();
var StartTime = "";
var EndTime = "";
if (!queryParam["start"].ToString().IsEmpty() && !queryParam["end"].ToString().IsEmpty())
{
StartTime = queryParam["start"].ToString();
EndTime = queryParam["end"].ToString();
}
else
{
StartTime = queryParam["StartTime"].ToString();
EndTime = queryParam["EndTime"].ToString();
}
DateTime StartTimes = queryParam["StartTime"].ToDate();
DateTime EndTimes = queryParam["EndTime"].ToDate();
string starttime = StartTimes.ToString("yyyyMMdd");
string endtime = EndTimes.ToString("yyyyMMdd");
DateTime Start = queryParam["start"].ToDate();
DateTime End = queryParam["end"].ToDate();
string start = Start.ToString("yyyyMMdd");
string end = End.ToString("yyyyMMdd");
var ms = NPOIExcel.ToExcelMoreheader(dt, "库存明细统计", "库存明细统计", StartTime, EndTime);
if (!queryParam["start"].ToString().IsEmpty() && !queryParam["end"].ToString().IsEmpty())
{
if (start == end)
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", start + "_库存明细统计.xls"));
}
else
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", start + "-" + end + "_库存明细统计.xls"));
}
}
else
{
if (starttime == endtime)
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "_库存明细统计.xls"));
}
else
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "-" + endtime + "_库存明细统计.xls"));
}
}
}
public FileResult Export(Pagination pagination, string queryJson)
{
var datas = orgResMangerIBLL.GetProductRateList(pagination, queryJson);
var dt = AsDataTable(datas);
var queryParam = queryJson.ToJObject();
DateTime StartTime = queryParam["StartTime"].ToDate();
DateTime EndTime = queryParam["EndTime"].ToDate();
string starttime = StartTime.ToString("yyyyMMdd");
string endtime = EndTime.ToString("yyyyMMdd");
var ms = NPOIExcel.ToExcelMoreheader(dt, "物料出成率列表", "物料出成率列表", starttime, endtime);
if (starttime == endtime)
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "_物料出成率列表.xls"));
}
else
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "-" + endtime + "_物料出成率列表.xls"));
}
}
/// <summary>
/// 获取Export表数据
/// <summary>
/// <param name="queryJson">查询参数</param>
/// <returns></returns>
public FileResult Export(Pagination pagination, string queryJson)
{
pagination.page = 1;
pagination.records = 0;
pagination.rows = 99999999;
pagination.sidx = "g_code";
pagination.sord = "desc";
DataTable dt = materialsSumIBLL.GetMaterialSumListByDate(pagination, queryJson);
dt.Columns["rownum"].ColumnName = "序号";
dt.Columns["g_code"].ColumnName = "商品编码";
dt.Columns["g_name"].ColumnName = "商品名称";
dt.Columns["s_name"].ColumnName = "仓库名称";
dt.Columns["g_stockcode"].ColumnName = "仓库编码";
dt.Columns["g_unit"].ColumnName = "单位";
dt.Columns["price"].ColumnName = "加权平均价(元)";
dt.Columns["inventoryquantity"].ColumnName = "入库数量";
dt.Columns["delivery"].ColumnName = "出库数量";
dt.Columns["initialinventory"].ColumnName = "数量";
dt.Columns["initialamount"].ColumnName = "金额";
dt.Columns["endinginventory"].ColumnName = "数量 ";
dt.Columns["finalamount"].ColumnName = "金额 ";
dt.Columns["back_qty"].ColumnName = "次品退库_数量";
dt.Columns["withdrawingnumber"].ColumnName = "退回仓库_数量";
dt.Columns["materialssales"].ColumnName = " 数量";
dt.Columns["outprice"].ColumnName = "单价";
dt.Columns["outamount"].ColumnName = " 金额";
dt.Columns["scrapist"].ColumnName = "报废物料_数量";
dt.Columns["otherwarehouse"].ColumnName = "其它入库_数量";
dt.Columns["otheroutbound"].ColumnName = "其它出库_数量";
dt.Columns["supplierback"].ColumnName = "退供应商_数量";
dt.Columns.Remove("startTime");
dt.Columns.Remove("endTime");
//表格列名排序
dt.Columns["序号"].SetOrdinal(0);
dt.Columns["商品编码"].SetOrdinal(1);
dt.Columns["商品名称"].SetOrdinal(2);
dt.Columns["仓库名称"].SetOrdinal(3);
dt.Columns["仓库编码"].SetOrdinal(4);
dt.Columns["单位"].SetOrdinal(5);
dt.Columns["加权平均价(元)"].SetOrdinal(6);
dt.Columns["入库数量"].SetOrdinal(7);
dt.Columns["出库数量"].SetOrdinal(8);
dt.Columns["数量"].SetOrdinal(9);
dt.Columns["金额"].SetOrdinal(10);
dt.Columns["数量 "].SetOrdinal(11);
dt.Columns["金额 "].SetOrdinal(12);
dt.Columns["次品退库_数量"].SetOrdinal(13);
dt.Columns["退回仓库_数量"].SetOrdinal(14);
dt.Columns[" 数量"].SetOrdinal(15);
dt.Columns["单价"].SetOrdinal(16);
dt.Columns[" 金额"].SetOrdinal(17);
dt.Columns["报废物料_数量"].SetOrdinal(18);
dt.Columns["其它入库_数量"].SetOrdinal(19);
dt.Columns["其它出库_数量"].SetOrdinal(20);
dt.Columns["退供应商_数量"].SetOrdinal(21);
//给数据
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["单价"].ToString() == null || dt.Rows[i]["单价"].ToString() == "")
{
dt.Rows[i]["单价"] = 0;
}
else
{
dt.Rows[i]["单价"] = dt.Rows[i]["单价"];
}
if (dt.Rows[i][" 金额"].ToString() == null || dt.Rows[i][" 金额"].ToString() == "")
{
dt.Rows[i][" 金额"] = 0;
}
else
{
dt.Rows[i][" 金额"] = dt.Rows[i][" 金额"];
}
}
var queryParam = queryJson.ToJObject();
DateTime StartTime = queryParam["StartTime"].ToDate();
DateTime EndTime = queryParam["EndTime"].ToDate();
string starttime = StartTime.ToString("yyyyMMdd");
string endtime = EndTime.ToString("yyyyMMdd");
var ms = NPOIExcel.ToExcelMoreheader(dt, "原物料出入库统计", "原物料出入库统计", queryParam["StartTime"].ToString(), queryParam["EndTime"].ToString());
if (starttime == endtime)
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "_原物料出入库统计.xls"));
}
else
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "-" + endtime + "_原物料出入库统计.xls"));
}
}
/// <summary>
/// 获取Export表数据
/// <summary>
/// <param name="queryJson">查询参数</param>
/// <returns></returns>
public ActionResult Export(string queryJson)
{
string message = "";
DataTable dt = pickingMaterIBLL.GetProductReportData(queryJson, out message);
if (string.IsNullOrEmpty(message))
{
//给列名
dt.Columns.Add("rownum", typeof(string));
dt.Columns["rownum"].ColumnName = "序号";
dt.Columns["F_CreateDate"].ColumnName = "日期";
dt.Columns["F_GoodsCode_Source"].ColumnName = "原物料编码";
dt.Columns["F_GoodsName_Source"].ColumnName = "原物料名称";
dt.Columns["F_GoodsQty_Source"].ColumnName = "数量(KG)";
dt.Columns["F_GoodsCode_01"].ColumnName = "物料编码";
dt.Columns["F_GoodsName_01"].ColumnName = "物料名称";
dt.Columns["F_GoodsQty_01"].ColumnName = "数量(KG) ";
dt.Columns["F_ConvertRange_01"].ColumnName = "转换标准率";
dt.Columns["F_Convert_01"].ColumnName = "转换率(%)";
dt.Columns["F_GoodsCode_03"].ColumnName = "物料编码 ";
dt.Columns["F_GoodsName_03"].ColumnName = "物料名称 ";
dt.Columns["F_GoodsQty_03"].ColumnName = " 数量(KG)";
dt.Columns["F_ConvertRange_03"].ColumnName = "转换标准率 ";
dt.Columns["F_Convert_03"].ColumnName = "转换率(%) ";
dt.Columns["F_GoodsCode_04"].ColumnName = " 物料编码";
dt.Columns["F_GoodsName_04"].ColumnName = " 物料名称";
dt.Columns["F_GoodsQty_04"].ColumnName = "实际份数量(盒)";
dt.Columns["F_Convert_04"].ColumnName = "理论份数(盒)";
dt.Columns["F_ConvertTag_04"].ColumnName = "偏差数(盒)";
dt.Columns["F_ConvertRange_04"].ColumnName = "偏差率(%)";
//表格列名排序
dt.Columns["序号"].SetOrdinal(0);
dt.Columns["日期"].SetOrdinal(1);
dt.Columns["原物料编码"].SetOrdinal(2);
dt.Columns["原物料名称"].SetOrdinal(3);
dt.Columns["数量(KG)"].SetOrdinal(4);
dt.Columns["物料编码"].SetOrdinal(5);
dt.Columns["物料名称"].SetOrdinal(6);
dt.Columns["数量(KG) "].SetOrdinal(7);
dt.Columns["转换标准率"].SetOrdinal(8);
dt.Columns["转换率(%)"].SetOrdinal(9);
dt.Columns["物料编码 "].SetOrdinal(10);
dt.Columns["物料名称 "].SetOrdinal(11);
dt.Columns[" 数量(KG)"].SetOrdinal(12);
dt.Columns["转换标准率 "].SetOrdinal(13);
dt.Columns["转换率(%) "].SetOrdinal(14);
dt.Columns[" 物料编码"].SetOrdinal(15);
dt.Columns[" 物料名称"].SetOrdinal(16);
dt.Columns["实际份数量(盒)"].SetOrdinal(17);
dt.Columns["理论份数(盒)"].SetOrdinal(18);
dt.Columns["偏差数(盒)"].SetOrdinal(19);
dt.Columns["偏差率(%)"].SetOrdinal(20);
//删除不要的列
dt.Columns.Remove("F_ConvertTag_01");
dt.Columns.Remove("F_ConvertTag_03");
#region 添加合计、统计行
if (dt != null && dt.Rows.Count > 0)
{
//插入统计行
if (true)
{
string current = dt.Rows[0]["日期"].ToString();
for (int i = 0; i < dt.Rows.Count; i++)
{
string last = dt.Rows[i]["日期"].ToString();
if (current != last)
{
DataRow dr = dt.NewRow();
dr["日期"] = "[" + current + "]合计";
dt.Rows.InsertAt(dr, i);
current = last;
i++;
}
}
DataRow drEnd = dt.NewRow();
drEnd["日期"] = "[" + current + "]合计";
dt.Rows.InsertAt(drEnd, dt.Rows.Count);
DataRow drSum = dt.NewRow();
drSum["日期"] = "总计";
dt.Rows.InsertAt(drSum, dt.Rows.Count);
}
//计算统计行
if (true)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
//统计数量
if (dt.Columns[j].ColumnName.Contains("数量"))
{
decimal everysum_qty = 0;
decimal totalsum_qty = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
string current = dt.Rows[i]["日期"].ToString();
if (current.Contains("合计"))
{
dt.Rows[i][j] = Math.Round(everysum_qty, 2);
everysum_qty = 0;
}
else
{
if (current == "总计")
{
dt.Rows[i][j] = Math.Round(totalsum_qty, 2);
everysum_qty = 0;
}
else
{
if (dt.Rows[i][j] == DBNull.Value)
{
everysum_qty += 0;
totalsum_qty += 0;
}
else
{
everysum_qty += decimal.Parse(dt.Rows[i][j].ToString());
totalsum_qty += decimal.Parse(dt.Rows[i][j].ToString());
}
}
}
}
}
}
}
}
#endregion
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["序号"] = i + 1;
}
var queryParam = queryJson.ToJObject();
DateTime StartTime = queryParam["StartTime"].ToDate();
DateTime EndTime = queryParam["EndTime"].ToDate();
string starttime = StartTime.ToString("yyyyMMdd");
string endtime = EndTime.ToString("yyyyMMdd");
var ms = NPOIExcel.ToExcelMoreheader(dt, "出成率报表-按原物料", "出成率报表-按原物料", queryParam["StartTime"].ToString(), queryParam["EndTime"].ToString());
if (starttime == endtime)
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "_出成率报表-按原物料.xls"));
}
else
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "-" + endtime + "_出成率报表-按原物料.xls"));
}
}
else
{
return(Fail(message));
}
}
