public JsonResult SearchMyAuditingList(FormCollection fc)
{
SearchMyApplyParams p = new SearchMyApplyParams();
MyUtils.SetFieldValueToModel(fc, p);
p.account = currentAccount;
p.userId = currentUser.userId;
p.userName = currentUser.userName;
var result = new DRSv().SearchMyAuditList(p, canCheckAll);
return(Json(new { total = result.Count(), rows = result.Skip((p.page - 1) * p.rows).Take(p.rows).ToList() }));
}
public void ExportPOData(string queryJson)
{
GetK3POParams p = JsonConvert.DeserializeObject <GetK3POParams>(queryJson);
p.poNumbers = p.poNumbers == null ? "" : p.poNumbers.Trim();//去掉前后空格
p.itemInfo = p.itemInfo == null ? "" : p.itemInfo.Trim();
p.account = currentAccount;
p.userId = currentUser.userId;
p.userNumber = currentUser.userName;
p.k3HasAudit = true;
List <K3POs> result;
List <decimal> qtyList;
try {
result = new DRSv().GetPOs(p).OrderBy(po => po.poDate).Take(1000).ToList();
//一次性关联1000条到数据库查询会导致内存溢出，所以大于500条的，分2次获取
var infos = result.Take(500).Select(r => new IDModel()
{
interId = r.poId, entryId = r.poEntryId
}).ToList();
qtyList = new DRSv().GetPOTransitQty(infos);
if (result.Count() > 500)
{
infos = result.Skip(500).Select(r => new IDModel()
{
interId = r.poId, entryId = r.poEntryId
}).ToList();
qtyList.AddRange(new DRSv().GetPOTransitQty(infos));
}
}
catch {
return;
}
ushort[] colWidth = new ushort[] { 18, 10, 20, 24, 32, 16,
16, 16, 16, 12, 24, 12, 16,
12, 12, 16, 12, 12, 16 };
string[] colName = new string[] { "订单编号", "分录号", "PR单号", "物料名称", "规格型号", "订单数量",
"入库数量", "申请数量", "可申请数量", "订料员", "申购部门", "单位", "物料编码",
"订单类型", "采购方式", "订单日期", "贸易类型", "币别", "采购员" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = "订单明细表_" + DateTime.Now.ToString("MMddHHmmss");
Worksheet sheet = xls.Workbook.Worksheets.Add("订单列表");
//设置各种样式
//标题样式
XF boldXF = xls.NewXF();
boldXF.HorizontalAlignment = HorizontalAlignments.Centered;
boldXF.Font.Height = 12 * 20;
boldXF.Font.FontName = "宋体";
boldXF.Font.Bold = true;
//设置列宽
ColumnInfo col;
for (ushort i = 0; i < colWidth.Length; i++)
{
col = new ColumnInfo(xls, sheet);
col.ColumnIndexStart = i;
col.ColumnIndexEnd = i;
col.Width = (ushort)(colWidth[i] * 256);
sheet.AddColumnInfo(col);
}
Cells cells = sheet.Cells;
int rowIndex = 1;
int colIndex = 1;
//设置标题
foreach (var name in colName)
{
cells.Add(rowIndex, colIndex++, name, boldXF);
}
for (var i = 0; i < result.Count(); i++)
{
var d = result[i];
var q = qtyList[i];
colIndex = 1;
//"订单编号", "分录号", "PR单号", "物料名称", "规格型号", "订单数量",
//"入库数量", "申请数量", "可申请数量", "订料员", "申购部门","单位","物料编码",
//"订单类型","采购方式","订单日期","贸易类型","币别","采购员"
cells.Add(++rowIndex, colIndex, d.poNo);
cells.Add(rowIndex, ++colIndex, d.poEntryId);
cells.Add(rowIndex, ++colIndex, d.prNo);
cells.Add(rowIndex, ++colIndex, d.itemName);
cells.Add(rowIndex, ++colIndex, d.itemModel);
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.orderQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.realteQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", q));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.orderQty - d.realteQty - q));
cells.Add(rowIndex, ++colIndex, d.matOrderName);
cells.Add(rowIndex, ++colIndex, d.departmentName);
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.itemNumber);
cells.Add(rowIndex, ++colIndex, d.billType);
cells.Add(rowIndex, ++colIndex, d.buyType);
cells.Add(rowIndex, ++colIndex, ((DateTime)d.poDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.tradeTypeName);
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.buyerName);
}
xls.Send();
WLog("导出K3订单Excel", "行数：" + result.Count() + ";" + queryJson);
//return "已成功导出行数："+result.Count();
}