/// <summary>
/// 获取拦截物流审核详情历史记录
/// </summary>
/// <param name="ID"></param>
private void GetApproveHistory(int ID)
{
var model = (from mod in db.T_InterceptLogistics
join user in db.T_User on mod.Creator equals user.ID
join user2 in db.T_User on mod.FinanceApproveUser equals user2.ID into JoinedEmpFinance
from user2 in JoinedEmpFinance.DefaultIfEmpty()
join user3 in db.T_User on mod.ExpressApproveUser equals user3.ID into JoinedEmpExpress
from user3 in JoinedEmpExpress.DefaultIfEmpty()
join user4 in db.T_User on mod.WarehouseApproveUser equals user4.ID into JoinedEmpWarehouse
from user4 in JoinedEmpWarehouse.DefaultIfEmpty()
select new
{
ID = mod.ID,
OrderNumber = mod.OrderNumber,
ExpressName = mod.ExpressName,
ExpressNumber = mod.ExpressNumber,
OrderMoney = mod.OrderMoney,
FinanceApproveStatus = mod.FinanceApproveStatus,
FinanceApproveUser = user2.Nickname,
FinanceApproveTime = mod.FinanceApproveTime,
FinanceReason = mod.FinanceReason,
ExpressApproveStatus = mod.ExpressApproveStatus,
ExpressApproveUser = user3.Nickname,
ExpressApproveTime = mod.ExpressApproveTime,
ExpressReason = mod.ExpressReason,
WarehouseApproveStatus = mod.WarehouseApproveStatus,
WarehouseApproveUser = user4.Nickname,
WarehouseApproveTime = mod.WarehouseApproveTime,
WarehouseReason = mod.WarehouseReason,
Creator = user.Nickname,
CreateTime = mod.CreateTime,
Del = mod.Del
}).SingleOrDefault(a => a.Del == 0 && a.ID == ID);
string table = "<table class=\"fromEditTable setTextWidth300\"> <tbody><tr><td></td><td>审核结果</td><td>审核人</td><td>审核时间</td><td>备注</td></tr>";
string tr = "";
//财务
string s = "";
if (model.FinanceApproveStatus == -1)
{
s = "<font color=#d02e2e>未审核</font>";
}
if (model.FinanceApproveStatus == 0)
{
s = "<font color=#2299ee>审核中</font>";
}
if (model.FinanceApproveStatus == 1)
{
s = "<font color=#1fc73a>已同意</font>";
}
if (model.FinanceApproveStatus == 2)
{
s = "<font color=#d02e2e>不同意</font>";
}
tr += string.Format("<tr><td>财务</td><td><label>{0}</label></td><td>{1}</td><td>{2}</td><td>{3}</td></tr>", s, model.FinanceApproveUser, model.FinanceApproveTime, model.FinanceReason);
//快递组
s = "";
if (model.ExpressApproveStatus == -1)
{
s = "<font color=#d02e2e>未审核</font>";
}
if (model.ExpressApproveStatus == 0)
{
s = "<font color=#2299ee>审核中</font>";
}
if (model.ExpressApproveStatus == 1)
{
s = "<font color=#1fc73a>已同意</font>";
}
if (model.ExpressApproveStatus == 2)
{
s = "<font color=#d02e2e>不同意</font>";
}
tr += string.Format("<tr><td>快递组</td><td><label>{0}</label></td><td>{1}</td><td>{2}</td><td>{3}</td></tr>", s, model.ExpressApproveUser, model.ExpressApproveTime, model.ExpressReason);
//仓库
s = "";
if (model.WarehouseApproveStatus == -1)
{
s = "<font color=#d02e2e>未复核</font>";
}
if (model.WarehouseApproveStatus == 0)
{
s = "<font color=#2299ee>复核中</font>";
}
if (model.WarehouseApproveStatus == 1)
{
s = "<font color=#1fc73a>已收货</font>";
}
if (model.WarehouseApproveStatus == 2)
{
s = "<font color=#d02e2e>未收货</font>";
}
tr += string.Format("<tr><td>仓库</td><td><label>{0}</label></td><td>{1}</td><td>{2}</td><td>{3}</td></tr>", s, model.WarehouseApproveUser, model.WarehouseApproveTime, model.WarehouseReason);
ViewData["history"] = table + tr + "</tbody></table>";
}
public FileResult ExportExcel(string Status, string who, string flag)
{
var queryData = (from mod in db.T_InterceptLogistics
join user in db.T_User on mod.Creator equals user.ID
join user2 in db.T_User on mod.FinanceApproveUser equals user2.ID into JoinedEmpFinance
from user2 in JoinedEmpFinance.DefaultIfEmpty()
join user3 in db.T_User on mod.ExpressApproveUser equals user3.ID into JoinedEmpExpress
from user3 in JoinedEmpExpress.DefaultIfEmpty()
join user4 in db.T_User on mod.WarehouseApproveUser equals user4.ID into JoinedEmpWarehouse
from user4 in JoinedEmpWarehouse.DefaultIfEmpty()
select new
{
ID = mod.ID,
OrderNumber = mod.OrderNumber,
ExpressName = mod.ExpressName,
ExpressNumber = mod.ExpressNumber,
OrderMoney = mod.OrderMoney,
FinanceApproveStatus = mod.FinanceApproveStatus,
FinanceApproveUser = user2.Nickname,
FinanceApproveTime = mod.FinanceApproveTime,
FinanceReason = mod.FinanceReason,
ExpressApproveStatus = mod.ExpressApproveStatus,
ExpressApproveUser = user3.Nickname,
ExpressApproveTime = mod.ExpressApproveTime,
ExpressReason = mod.ExpressReason,
WarehouseApproveStatus = mod.WarehouseApproveStatus,
WarehouseApproveUser = user4.Nickname,
WarehouseApproveTime = mod.WarehouseApproveTime,
WarehouseReason = mod.WarehouseReason,
Creator = user.Nickname,
CreateTime = mod.CreateTime,
Del = mod.Del
}).Where(a => a.Del == 0).OrderByDescending(a => a.CreateTime).ToList();
if (flag == "1") //flag = 1从审批页面导出
{
if (!string.IsNullOrEmpty(who))
{
if (who == "快递组")
{
queryData = queryData.Where(a => a.FinanceApproveStatus == 1).ToList();
}
else if (who == "仓库")
{
queryData = queryData.Where(a => a.ExpressApproveStatus == 1).ToList();
}
}
}
if (!string.IsNullOrEmpty(who) && !string.IsNullOrEmpty(Status))
{
int status = Convert.ToInt32(Status);
if (status != -2)
{
if (who == "财务")
{
queryData = queryData.Where(a => a.FinanceApproveStatus == status).ToList();
}
else if (who == "快递组")
{
queryData = queryData.Where(a => a.ExpressApproveStatus == status).ToList();
}
else if (who == "仓库")
{
queryData = queryData.Where(a => a.WarehouseApproveStatus == status).ToList();
}
}
}
if (queryData.Count > 0)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
for (int inirows = 0; queryData.Count - inirows > 0; inirows += 65000)
{
string a = "";
if (inirows / 65000 >= 1)
{
a = "(" + (inirows / 65000 + 1) + ")";
}
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet" + a);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("申请时间");
row1.CreateCell(1).SetCellValue("订单号");
row1.CreateCell(2).SetCellValue("快递公司");
row1.CreateCell(3).SetCellValue("快递单号");
row1.CreateCell(4).SetCellValue("订单金额");
row1.CreateCell(5).SetCellValue("申请人");
row1.CreateCell(6).SetCellValue("财务审批状态");
row1.CreateCell(7).SetCellValue("财务审批人");
row1.CreateCell(8).SetCellValue("财务备注");
row1.CreateCell(9).SetCellValue("快递组审批状态");
row1.CreateCell(10).SetCellValue("快递组审批人");
row1.CreateCell(11).SetCellValue("快递组备注");
sheet1.SetColumnWidth(0, 20 * 256);
sheet1.SetColumnWidth(1, 20 * 330);
sheet1.SetColumnWidth(2, 20 * 256);
sheet1.SetColumnWidth(3, 20 * 330);
sheet1.SetColumnWidth(4, 20 * 156);
sheet1.SetColumnWidth(5, 20 * 156);
sheet1.SetColumnWidth(6, 20 * 200);
sheet1.SetColumnWidth(7, 20 * 156);
sheet1.SetColumnWidth(8, 20 * 256);
sheet1.SetColumnWidth(9, 20 * 200);
sheet1.SetColumnWidth(10, 20 * 156);
sheet1.SetColumnWidth(11, 20 * 200);
int pagerows = 0;
pagerows = queryData.Count - inirows;
if (pagerows > 65000)
{
pagerows = 65000;
}
for (int i = inirows; i < inirows + pagerows; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i - inirows + 1);
rowtemp.CreateCell(0).SetCellValue(queryData[i].CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
rowtemp.CreateCell(1).SetCellValue(queryData[i].OrderNumber);
rowtemp.CreateCell(2).SetCellValue(queryData[i].ExpressName);
rowtemp.CreateCell(3).SetCellValue(queryData[i].ExpressNumber);
rowtemp.CreateCell(4).SetCellValue(queryData[i].OrderMoney.ToString());
rowtemp.CreateCell(5).SetCellValue(queryData[i].Creator);
rowtemp.CreateCell(6).SetCellValue(TransferStatus(queryData[i].FinanceApproveStatus.ToString()));
rowtemp.CreateCell(7).SetCellValue(queryData[i].FinanceApproveUser);
rowtemp.CreateCell(8).SetCellValue(queryData[i].FinanceReason);
rowtemp.CreateCell(9).SetCellValue(TransferStatus(queryData[i].ExpressApproveStatus.ToString()));
rowtemp.CreateCell(10).SetCellValue(queryData[i].ExpressApproveUser);
rowtemp.CreateCell(11).SetCellValue(queryData[i].ExpressReason);
}
}
Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "特殊物流拦截.xls"));
}
else
{
Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
ms.Seek(0, SeekOrigin.Begin);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "特殊物流拦截.xls"));
}
}
public JsonResult InterceptLogistics(Lib.GridPager pager, string queryStr, string who, string Status)//拦截物流列表
{
try
{
//var InterceptLogistics = db.T_InterceptLogistics.Where(a => a.Del == 0).ToList();
//左连
var InterceptLogistics = (from mod in db.T_InterceptLogistics
join user in db.T_User on mod.Creator equals user.ID
join user2 in db.T_User on mod.FinanceApproveUser equals user2.ID into JoinedEmpFinance
from user2 in JoinedEmpFinance.DefaultIfEmpty()
join user3 in db.T_User on mod.ExpressApproveUser equals user3.ID into JoinedEmpExpress
from user3 in JoinedEmpExpress.DefaultIfEmpty()
join user4 in db.T_User on mod.WarehouseApproveUser equals user4.ID into JoinedEmpWarehouse
from user4 in JoinedEmpWarehouse.DefaultIfEmpty()
select new
{
ID = mod.ID,
OrderNumber = mod.OrderNumber,
ExpressName = mod.ExpressName,
ExpressNumber = mod.ExpressNumber,
OrderMoney = mod.OrderMoney,
FinanceApproveStatus = mod.FinanceApproveStatus,
FinanceApproveUser = user2.Nickname,
FinanceApproveTime = mod.FinanceApproveTime,
FinanceReason = mod.FinanceReason,
ExpressApproveStatus = mod.ExpressApproveStatus,
ExpressApproveUser = user3.Nickname,
ExpressApproveTime = mod.ExpressApproveTime,
ExpressReason = mod.ExpressReason,
WarehouseApproveStatus = mod.WarehouseApproveStatus,
WarehouseApproveUser = user4.Nickname,
WarehouseApproveTime = mod.WarehouseApproveTime,
WarehouseReason = mod.WarehouseReason,
Creator = user.Nickname,
CreateTime = mod.CreateTime,
Del = mod.Del
}).Where(a => a.Del == 0).OrderByDescending(a => a.CreateTime).ToList();
if (!string.IsNullOrEmpty(who))
{
if (who == "快递组")
{
InterceptLogistics = InterceptLogistics.Where(a => a.FinanceApproveStatus == 1).ToList();
}
else if (who == "仓库")
{
InterceptLogistics = InterceptLogistics.Where(a => a.ExpressApproveStatus == 1).ToList();
}
}
if (!string.IsNullOrEmpty(Status))
{
int status = Convert.ToInt32(Status);
if (status != -2)
{
if (who == "财务")
{
InterceptLogistics = InterceptLogistics.Where(a => a.FinanceApproveStatus == status).ToList();
}
else if (who == "快递组")
{
InterceptLogistics = InterceptLogistics.Where(a => a.ExpressApproveStatus == status).ToList();
}
else if (who == "仓库")
{
InterceptLogistics = InterceptLogistics.Where(a => a.WarehouseApproveStatus == status).ToList();
}
}
}
if (!string.IsNullOrEmpty(queryStr))
{
InterceptLogistics = InterceptLogistics.Where(a => a.OrderNumber.Contains(queryStr) || a.ExpressNumber.Contains(queryStr) || a.Creator.Contains(queryStr) || a.FinanceApproveUser.Contains(queryStr) || a.ExpressApproveUser.Contains(queryStr) || a.WarehouseApproveUser.Contains(queryStr)).ToList();
}
pager.totalRows = InterceptLogistics.Count;
InterceptLogistics = InterceptLogistics.Skip((pager.page - 1) * pager.rows).Take(pager.rows).ToList();
return(Json(new { total = pager.totalRows, rows = InterceptLogistics }));
}
catch (Exception ex)
{
return(Json(new { total = 0, rows = new int[] { } }));
}
}