/// <summary>
/// 搜索送货申请单据的底层方法
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private IQueryable <CheckApplyListModel> SearchApplyListBase(SearchMyApplyParams p)
{
var result = from d in db.DRBills
join e in db.DRBillDetails on d.bill_id equals e.bill_id
where d.send_date >= p.beginDate &&
d.send_date <= p.endDate &&
d.account == p.account
select new CheckApplyListModel()
{
billId = d.bill_id,
billType = d.bill_type,
billNo = d.bill_no,
buyerName = e.buyer_name,
buyerNumber = e.buyer_number,
buyType = d.buy_type,
inStockBillDate = d.in_stock_bill_date,
inStockBillNo = d.in_stock_bill_number,
itemModel = e.item_model,
itemName = e.item_name,
itemNumber = e.item_number,
matOrderNumber = d.mat_order_number,
matOrderName = d.mat_order_name,
poDate = e.po_date,
poEntryId = e.po_entry_id,
poNo = e.po_number,
poQty = e.po_qty,
prNo = e.pr_number,
pStatus = d.p_status,
sendDate = d.send_date,
sendQty = e.send_qty,
supplierName = d.supplier_name,
tradeTypeName = d.trade_type_name,
unitName = e.unit_name,
userId = d.user_id,
supplierNumber = d.supplier_number,
comment = e.comment
};
if (!string.IsNullOrWhiteSpace(p.billNo))
{
result = result.Where(r => r.billNo.Contains(p.billNo) || r.supplierName.Contains(p.billNo));
}
if (!"所有".Equals(p.pStatus))
{
result = result.Where(r => r.pStatus == p.pStatus);
}
if (!"所有".Equals(p.billType))
{
result = result.Where(r => r.billType == p.billType);
}
if (!string.IsNullOrWhiteSpace(p.poNo))
{
result = result.Where(r => r.poNo.Contains(p.poNo));
}
if (!string.IsNullOrWhiteSpace(p.itemInfo))
{
result = result.Where(r => r.itemModel.Contains(p.itemInfo) || r.itemName.Contains(p.itemInfo));
}
return(result);
}
/// <summary>
/// 查看我的送货申请列表，供应商用
/// </summary>
/// <param name="p"></param>
/// <param name="canCheckAll"></param>
/// <returns></returns>
public IQueryable <CheckApplyListModel> SearchMyApplyList(SearchMyApplyParams p, bool canCheckAll)
{
return(SearchApplyListBase(p)
.Where(s => (canCheckAll ||
s.userId == p.userId ||
(s.supplierNumber + "A").Contains(p.userName))
)
.OrderBy(s => s.sendDate));
}
/// <summary>
/// 查看我的审核申请，订料员和采购员用
/// </summary>
/// <param name="p"></param>
/// <param name="canCheckAll"></param>
/// <returns></returns>
public IQueryable <CheckApplyListModel> SearchMyAuditList(SearchMyApplyParams p, bool canCheckAll)
{
var groupUserName = new UASv().GetAuditGroupUsers(p.userId).Select(u => u.user_name).ToList();
return(SearchApplyListBase(p)
.Where(s => (canCheckAll ||
s.matOrderNumber == p.userName ||
s.buyerNumber == p.userName ||
groupUserName.Contains(s.matOrderNumber) ||
groupUserName.Contains(s.buyerName))
)
.OrderBy(s => s.sendDate));
}
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() }));
}
/// <summary>
/// 搜索送货申请单据的底层方法
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private IQueryable <CheckApplyListModel> SearchApplyListBase(SearchMyApplyParams p)
{
p.poNo = p.poNo ?? "";
p.billNo = p.billNo ?? "";
p.itemInfo = p.itemInfo ?? "";
var result = from d in db.DRBills
join e in db.DRBillDetails on d.bill_id equals e.bill_id
where d.send_date >= p.beginDate &&
d.send_date <= p.endDate &&
(d.bill_no.Contains(p.billNo) || d.supplier_name.Contains(p.billNo)) &&
(p.pStatus == "所有" || d.p_status == p.pStatus) &&
d.account == p.account &&
(p.billType == "所有" || d.bill_type == p.billType) &&
e.po_number.Contains(p.poNo) &&
(e.item_model.Contains(p.itemInfo) || e.item_name.Contains(p.itemInfo))
select new CheckApplyListModel()
{
billId = d.bill_id,
billType = d.bill_type,
billNo = d.bill_no,
buyerName = e.buyer_name,
buyerNumber = e.buyer_number,
buyType = d.buy_type,
inStockBillDate = d.in_stock_bill_date,
inStockBillNo = d.in_stock_bill_number,
itemModel = e.item_model,
itemName = e.item_name,
itemNumber = e.item_number,
matOrderNumber = d.mat_order_number,
matOrderName = d.mat_order_name,
poDate = e.po_date,
poEntryId = e.po_entry_id,
poNo = e.po_number,
poQty = e.po_qty,
prNo = e.pr_number,
pStatus = d.p_status,
sendDate = d.send_date,
sendQty = e.send_qty,
supplierName = d.supplier_name,
tradeTypeName = d.trade_type_name,
unitName = e.unit_name,
userId = d.user_id,
supplierNumber = d.supplier_number,
comment = e.comment
};
return(result);
}
public JsonResult GetMyAppliesData(FormCollection fc)
{
SearchMyApplyParams p = new SearchMyApplyParams();
MyUtils.SetFieldValueToModel(fc, p);
p.account = currentAccount;
p.userId = currentUser.userId;
p.userName = currentUser.userName;
try {
var result = new DRSv().SearchMyApplyList(p, canCheckAll);
return(Json(new { total = result.Count(), rows = result.OrderByDescending(r => r.sendDate).Skip((p.page - 1) * p.rows).Take(p.rows).ToList() }));
}
catch (Exception ex) {
return(Json(new SRM(ex)));
}
}
/// <summary>
/// 导出申请单excel，包括我的申请和我的审批
/// </summary>
/// <param name="fc"></param>
public void ExporDRData(string queryJson, string pageType)
{
SearchMyApplyParams p = JsonConvert.DeserializeObject <SearchMyApplyParams>(queryJson);
p.account = currentAccount;
p.userId = currentUser.userId;
p.userName = currentUser.userName;
var result = new List <CheckApplyListModel>();
string fileName = "";
if (pageType.Equals("apply"))
{
fileName = "我的送货申请_";
result = new DRSv().SearchMyApplyList(p, canCheckAll).ToList();
}
else if (pageType.Equals("audit"))
{
fileName = "审核送货申请_";
result = new DRSv().SearchMyAuditList(p, canCheckAll).ToList();
}
ushort[] colWidth = new ushort[] { 16, 18, 12, 16, 10, 24,
32, 16, 16, 12, 18, 18, 16,
16, 16, 16, 16, 18, 16, 18, 16 };
string[] colName = new string[] { "发货日期", "送货单号", "申请状态", "订单编号", "分录号", "物料名称",
"规格型号", "订单数量", "申请数量", "单位", "物料编码", "备注", "订单类型",
"采购方式", "订料员", "采购员", "贸易类型", "PR单号", "采购日期", "入库单号", "入库日期" };
//設置excel文件名和sheet名
XlsDocument xls = new XlsDocument();
xls.FileName = 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);
}
foreach (var d in result)
{
colIndex = 1;
//"发货日期", "送货单号", "申请状态", "订单编号", "分录号", "物料名称",
//"规格型号", "订单数量", "申请数量", "单位", "物料编码","订单类型",
//"采购方式","订料员","采购员","贸易类型","PR单号","采购日期","入库单号","入库日期"
cells.Add(++rowIndex, colIndex, ((DateTime)d.sendDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.billNo);
cells.Add(rowIndex, ++colIndex, d.pStatus);
cells.Add(rowIndex, ++colIndex, d.poNo);
cells.Add(rowIndex, ++colIndex, d.poEntryId);
cells.Add(rowIndex, ++colIndex, d.itemName);
cells.Add(rowIndex, ++colIndex, d.itemModel);
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.poQty));
cells.Add(rowIndex, ++colIndex, string.Format("{0:0.####}", d.sendQty));
cells.Add(rowIndex, ++colIndex, d.unitName);
cells.Add(rowIndex, ++colIndex, d.itemNumber);
cells.Add(rowIndex, ++colIndex, d.comment);
cells.Add(rowIndex, ++colIndex, d.billType);
cells.Add(rowIndex, ++colIndex, d.buyType);
cells.Add(rowIndex, ++colIndex, d.matOrderName);
cells.Add(rowIndex, ++colIndex, d.buyerName);
cells.Add(rowIndex, ++colIndex, d.tradeTypeName);
cells.Add(rowIndex, ++colIndex, d.prNo);
cells.Add(rowIndex, ++colIndex, ((DateTime)d.poDate).ToString("yyyy-MM-dd"));
cells.Add(rowIndex, ++colIndex, d.inStockBillNo);
cells.Add(rowIndex, ++colIndex, d.inStockBillDate == null?"":((DateTime)d.inStockBillDate).ToString("yyyy-MM-dd"));
}
xls.Send();
WLog("导出申请单Excel", fileName + ":" + queryJson);
}
