public JsonResult GetPOs(FormCollection fc)
{
GetK3POParams p = new GetK3POParams();
MyUtils.SetFieldValueToModel(fc, p);
if (p.beginDate == DateTime.MinValue)
{
return(Json(new SRM(false, "必须输入正确的开始日期")));
}
if (p.endDate == DateTime.MinValue)
{
return(Json(new SRM(false, "必须输入正确的结束日期")));
}
if (p.beginDate > p.endDate)
{
return(Json(new SRM(false, "开始日期不能大于结束日期")));
}
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;
if (p.poNumbers.Length > 800)
{
return(Json(new SRM(false, "订单编号字符数不能大于800个")));
}
List <K3POs> result;
try {
result = new DRSv().GetPOs(p);
}
catch (Exception ex) {
return(Json(new SRM(ex)));
}
if (result.Count() == 0)
{
return(Json(new SRM(false, "查询不到任何符合条件的记录")));
}
return(Json(new { suc = true, rows = result.OrderBy(r => r.poDate).ThenBy(r => r.poId).ThenBy(r => r.poEntryId) }));
}
/// <summary>
/// 获取k3中的po信息
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
public List <K3POs> GetPOs(GetK3POParams p)
{
return(db.ExecuteQuery <K3POs>(
"exec GetK3POs @account = {0},@billType = {1},@poNumbers = {2},@itemInfo = {9},@beginDate = {3},@endDate = {4},@userNumber = {5},@userId = {6},@k3HasAudit = {7},@isFinished = {8}",
p.account, p.billType, p.poNumbers, p.beginDate.ToString(), p.endDate.ToString(), p.userNumber, p.userId, p.k3HasAudit, p.isFinished, p.itemInfo).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();
}