#region 查询导出物料报销情况
/// <summary>
/// 查询物料报销明细
/// </summary>
//[Chaolj.Apps.Core.Utils.GlobalContext.CheckApi]
public FileResult QueryMaterialPayStatus(FormCollection formData)
{
string qryString = this.Request.Params["qryString"].SafeConvert().ToStr();
DateTime beginDate = this.Request.Params["beginDate"].SafeConvert().ToDateTime().Date;
DateTime endDate = this.Request.Params["endDate"].SafeConvert().ToDateTime().Date;
#region 1. 查询数据
List<ZTZS.DAL.ZSERP.vMaterialSheetDetail> list = new List<ZTZS.DAL.ZSERP.vMaterialSheetDetail>(); // 物料出入库明细清单
// 1. 从老erp查询出相应的入库明细；
using (var erpdb = ZTZS.DAL.ZSERP.ZSErpContext.Create())
{
var qry = erpdb.vMaterialSheetDetail.Where(p => p.Transdate >= beginDate && p.Transdate < endDate && (p.SheetTypeNo == "PR" || p.SheetTypeNo == "RI"));
list = qry.ToList();
}
List<MaterialIsSettlement> listSettlement = new List<MaterialIsSettlement>(); // 已进入报销流程的单据；
// 2. 从新erp判定哪些明细已经报销；
using (var h3db2 = Chaolj.ZTBPM.DAL.H3Context.Create())
using (var h3Db = ZTZS.DAL.H3.H3Context.Create())
{
// 2.1 获取当前所有单据
var listBillNo = list.Select(m => m.SheetNo).Distinct().ToList();
// 2.2 查询当前单据列表，所有已进入报销流程的单据；
var billNos1 = h3db2.BD_ReceiveOrReturnOrder.Where(m => listBillNo.Contains(m.OrderNo)).Select(m => new MaterialIsSettlement { BillNo = m.OrderNo, InstanceId = m.INSTANCEID }).ToList();
var billNos2 = h3Db.报销材料报销入库单.Where(m => listBillNo.Contains(m.单据编号)).Select(m => new MaterialIsSettlement { BillNo = m.单据编号, InstanceId = m.INSTANCEID }).ToList();
// 2.3 用sql语句查询
string strBillNos = "";
for (int i = 0; i < listBillNo.Count(); i++)
{
strBillNos += "'" + listBillNo[i] + "'";
if (i < listBillNo.Count - 1) strBillNos += ",";
}
string strWhere = setConditionByOr(strBillNos, 50, "单据编号");
string sql = "SELECT * FROM h3.报销材料报销入库单 WHERE " + strWhere + "";
var billNos3Model = h3Db.Database.SqlQuery<MaterialSheetInSettlement>(sql).ToList();
var billNos3 = billNos3Model.Select(m => new MaterialIsSettlement { BillNo = m.单据编号, InstanceId = m.INSTANCEID }).ToList();
string strWhere4 = setConditionByOr(strBillNos, 50, "入库单编号");
string sql4 = "SELECT * FROM h3.材料零星报销入库单 WHERE " + strWhere4 + "";
var billNos4Model = h3Db.Database.SqlQuery<MaterialSheetLinXinSettlement>(sql4);
var billNos4 = billNos4Model.Select(m => new MaterialIsSettlement { BillNo = m.入库单编号, InstanceId = m.INSTANCEID }).ToList();
listSettlement.AddRange(billNos1);
listSettlement.AddRange(billNos2);
listSettlement.AddRange(billNos3);
listSettlement.AddRange(billNos4);
listSettlement = listSettlement.Distinct().ToList();
}
// 3. 从支付中心判定，哪些数据已经支付、待支付等状态；
List<string> listWorkflowId = new List<string>();
listWorkflowId = listSettlement.Select(m => m.InstanceId).ToList();
using (var erp = Chaolj.DAL.ERP.ERPContext2.Create())
{
// 已支付金额:FromTypeName in (材料_材料报销，材料_临星材料报销)
var qryPay = erp.vFI_Order_Payment.Where(p => listWorkflowId.Contains(p.InstanceId) && (p.Status == 2 || p.Status == 4)).Select(m => new { InstanceId = m.InstanceId }).ToList(); // 状态为：已结算、已完成；
listSettlement = (from a in listSettlement
join p in qryPay on a.InstanceId equals p.InstanceId into ap
from api in ap.DefaultIfEmpty()
select new MaterialIsSettlement
{
BillNo = a.BillNo,
InstanceId = a.InstanceId,
PayStatus = (api == null || api.InstanceId == null) ? "未支付" : "已支付"
}).ToList();
}
var result = (from a in list
join s in listSettlement on a.SheetNo equals s.BillNo into sa
from sai in sa.DefaultIfEmpty()
select new
{
Id = a.Id,
项目编码 = a.ProjectNo,
项目名称 = a.ProjectName,
供应商编码 = a.VenderCode,
供应商名称 = a.VenderName,
单据编码 = a.SheetNo,
Line = a.Line,
交易时间 = a.Transdate,
物料编码 = a.ItemNo,
类别 = a.ItemType,
大类 = a.ItemMainClass,
子类 = a.ItemSubClass,
FSB = a.FSB,
厂家 = a.Manufacturer,
规格 = a.Specification,
品牌 = a.Brand,
属性 = a.Property,
单位 = a.Uom,
数量 = a.Qty,
单价 = a.Price,
金额 = a.Amount,
描述 = a.Description,
单据状态 = a.Stat,
业务类型 = a.SheetTypeNo == "PR" ? "入库" : "退货",
报销状态 = (sai == null || sai.InstanceId == null) ? "未报销" : "已报销",
支付状态 = sai == null ? "" : sai.PayStatus
}).ToList();
#endregion
#region 2. 生成xls
#region 1.列头定义
List<string> lstTitle = new List<string>
{
"项目编码",// 0
"项目名称", // 1
"供应商编码",// 2
"供应商名称", // 3
"单据编码",// 4
"交易时间",// 5
"物料编码",//6
"类别",// 7
"大类",// 8
"子类",// 9
"FSB",// 10
"厂家",// 11
"规格",// 12
"品牌",// 13
"属性",// 14
"单位",// 15
"数量",// 16
"单价",// 17
"金额",// 18
"描述",// 19
"单据状态", // 20
"业务类型", // 21
"报销状态", // 22
"支付状态",// 23
};
#endregion
IWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("Sheet1");
IRow rowTitle = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
for (int i = 0; i < lstTitle.Count; i++)
{
rowTitle.CreateCell(i).SetCellValue(lstTitle[i]);
}
if (list != null)
{
result.OrderBy(p => p.单据编码);
int start = 0;//记录同组开始行号
int end = 0;//记录同组结束行号
string temp = "";//记录上一行的值
for (int i = 0; i < result.Count(); i++)
{
IRow row = sheet.CreateRow(i + 1);
#region 单元格赋值
row.CreateCell(0).SetCellValue(result[i].项目编码);
row.CreateCell(1).SetCellValue(result[i].项目名称);
row.CreateCell(2).SetCellValue(result[i].供应商编码);
row.CreateCell(3).SetCellValue(result[i].供应商名称);
row.CreateCell(4).SetCellValue(result[i].单据编码);
row.CreateCell(5).SetCellValue(result[i].交易时间.ToString());
row.CreateCell(6).SetCellValue(result[i].物料编码);
row.CreateCell(7).SetCellValue(result[i].类别);
row.CreateCell(8).SetCellValue(result[i].大类);
row.CreateCell(9).SetCellValue(result[i].子类);
row.CreateCell(10).SetCellValue(result[i].FSB);
row.CreateCell(11).SetCellValue(result[i].厂家);
row.CreateCell(12).SetCellValue(result[i].规格);
row.CreateCell(13).SetCellValue(result[i].品牌);
row.CreateCell(14).SetCellValue(result[i].属性);
row.CreateCell(15).SetCellValue(result[i].单位);
row.CreateCell(16).SetCellValue(result[i].数量.ToString("f2"));
row.CreateCell(17).SetCellValue(result[i].单价.ToString("f4"));
row.CreateCell(18).SetCellValue(result[i].金额.ToString("f4"));
row.CreateCell(19).SetCellValue(result[i].描述);
row.CreateCell(20).SetCellValue(result[i].单据状态);
row.CreateCell(21).SetCellValue(result[i].业务类型);
row.CreateCell(22).SetCellValue(result[i].报销状态);
row.CreateCell(23).SetCellValue(result[i].支付状态);
#endregion
row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellType(CellType.String);
var cellText = row.Cells[0].StringCellValue;//获取当前行 第1列的单元格的值
if (cellText == temp)//上下行相等，记录要合并的最后一行
{
end = i;
}
else//上下行不等，记录
{
if (start != end)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region = new CellRangeAddress(start + 1, end + 1, 0, 0);
sheet.AddMergedRegion(region);
}
start = i;
end = i;
temp = cellText;
}
}
}
#endregion
#region 3. 格式化并保存
for (int i = 0; i < 7; i++)
{
sheet.AutoSizeColumn(i);//i：根据标题的个数设置自动列宽
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
#endregion
return File(ms, "application/vnd.ms-excel", "出入库物料报销明细.xls");
}
/// <summary>
/// 查询单据报销明细
/// </summary>
//[Chaolj.Apps.Core.Utils.GlobalContext.CheckApi]
public FileResult QueryMaterialSheetPayStatus(FormCollection formData)
{
string qryString = this.Request.Params["qryString"].SafeConvert().ToStr();
DateTime beginDate = this.Request.Params["beginDate"].SafeConvert().ToDateTime().Date;
DateTime endDate = this.Request.Params["endDate"].SafeConvert().ToDateTime().Date;
#region 1. 查询数据
List<ZTZS.DAL.ZSERP.MaterialSheet> list = new List<ZTZS.DAL.ZSERP.MaterialSheet>(); // 物料出入库明细清单
// 1. 从老erp查询出相应的入库明细；
using (var erpdb = ZTZS.DAL.ZSERP.ZSErpContext.Create())
{
var qry = erpdb.MaterialSheet.Where(p => p.Transdate >= beginDate && p.Transdate < endDate && p.Stat == "审核" && (p.SheetTypeNo == "PR" || p.SheetTypeNo == "RI"));
list = qry.ToList();
}
List<MaterialIsSettlement> listSettlement = new List<MaterialIsSettlement>(); // 已进入报销流程的单据；
// 2. 从新erp判定哪些明细已经报销；
using (var h3db2 = Chaolj.ZTBPM.DAL.H3Context.Create())
using (var h3Db = ZTZS.DAL.H3.H3Context.Create())
{
// 2.1 获取当前所有单据
var listBillNo = list.Select(m => m.SheetNo).Distinct().ToList();
// 2.2 查询当前单据列表，所有已进入报销流程的单据；
var billNos1 = h3db2.BD_ReceiveOrReturnOrder.Where(m => listBillNo.Contains(m.OrderNo)).Select(m => new MaterialIsSettlement { BillNo = m.OrderNo, InstanceId = m.INSTANCEID }).ToList();
var billNos2 = h3Db.报销材料报销入库单.Where(m => listBillNo.Contains(m.单据编号)).Select(m => new MaterialIsSettlement { BillNo = m.单据编号, InstanceId = m.INSTANCEID }).ToList();
// 2.3 用sql语句查询
string strBillNos = "";
for (int i = 0; i < listBillNo.Count(); i++)
{
strBillNos += "'" + listBillNo[i]+"'";
if (i < listBillNo.Count - 1) strBillNos += ",";
}
string strWhere = setConditionByOr(strBillNos, 50, "单据编号");
string sql = "SELECT * FROM h3.报销材料报销入库单 WHERE "+strWhere+"";
var billNos3Model = h3Db.Database.SqlQuery<MaterialSheetInSettlement>(sql).ToList();
var billNos3 = billNos3Model.Select(m => new MaterialIsSettlement { BillNo = m.单据编号, InstanceId = m.INSTANCEID }).ToList();
string strWhere4 = setConditionByOr(strBillNos, 50, "入库单编号");
string sql4 = "SELECT * FROM h3.材料零星报销入库单 WHERE " + strWhere4 + "";
var billNos4Model = h3Db.Database.SqlQuery<MaterialSheetLinXinSettlement>(sql4);
var billNos4 = billNos4Model.Select(m => new MaterialIsSettlement { BillNo = m.入库单编号, InstanceId = m.INSTANCEID }).ToList();
listSettlement.AddRange(billNos1);
listSettlement.AddRange(billNos2);
listSettlement.AddRange(billNos3);
listSettlement.AddRange(billNos4);
listSettlement = listSettlement.Distinct().ToList();
}
// 3. 从支付中心判定，哪些数据已经支付、待支付等状态；
List<string> listWorkflowId = new List<string>();
listWorkflowId = listSettlement.Select(m => m.InstanceId).ToList();
using (var erp = Chaolj.DAL.ERP.ERPContext2.Create())
{
// 已支付金额:FromTypeName in (材料_材料报销，材料_临星材料报销)
var qryPay = erp.vFI_Order_Payment.Where(p => listWorkflowId.Contains(p.InstanceId) && (p.Status == 2 || p.Status == 4)).Select(m => new { InstanceId = m.InstanceId }).ToList(); // 状态为：已结算、已完成；
listSettlement = (from a in listSettlement
join p in qryPay on a.InstanceId equals p.InstanceId into ap
from api in ap.DefaultIfEmpty()
select new MaterialIsSettlement
{
BillNo = a.BillNo,
InstanceId = a.InstanceId,
PayStatus = (api == null || api.InstanceId == null) ? "未支付" : "已支付"
}).ToList();
}
var result = (from a in list
join s in listSettlement on a.SheetNo equals s.BillNo into sa
from sai in sa.DefaultIfEmpty()
select new
{
Id = a.Id,
项目名称 = a.ProjectName,
供应商名称 = a.Vender,
单据编码 = a.SheetNo,
交易时间 = a.Transdate,
送货金额 = a.DeliveryAmount,
金额 = a.TotalAmount,
描述 = a.Description,
单据状态 = a.Stat,
业务类型 = a.SheetTypeNo == "PR" ? "入库" : "退货",
报销状态 = (sai == null || sai.InstanceId == null) ? "未报销" : "已报销",
支付状态 = sai == null ? "" : sai.PayStatus
}).ToList();
#endregion
#region 2. 生成xls
#region 1.列头定义
List<string> lstTitle = new List<string>
{
"项目名称", // 0
"供应商名称", // 1
"单据编码",// 2
"交易时间",// 3
"送货金额",// 4
"金额",// 5
"描述",// 6
"单据状态", // 7
"业务类型", // 8
"报销状态", // 9
"支付状态",// 10
};
#endregion
IWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("Sheet1");
IRow rowTitle = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
for (int i = 0; i < lstTitle.Count; i++)
{
rowTitle.CreateCell(i).SetCellValue(lstTitle[i]);
}
if (list != null)
{
result.OrderBy(p => p.单据编码);
int start = 0;//记录同组开始行号
int end = 0;//记录同组结束行号
string temp = "";//记录上一行的值
for (int i = 0; i < result.Count(); i++)
{
IRow row = sheet.CreateRow(i + 1);
#region 单元格赋值
row.CreateCell(0).SetCellValue(result[i].项目名称);
row.CreateCell(1).SetCellValue(result[i].供应商名称);
row.CreateCell(2).SetCellValue(result[i].单据编码);
row.CreateCell(3).SetCellValue(result[i].交易时间.ToString());
row.CreateCell(4).SetCellValue(result[i].送货金额.Value.ToString("f2"));
row.CreateCell(5).SetCellValue(result[i].金额.ToString("f4"));
row.CreateCell(6).SetCellValue(result[i].描述);
row.CreateCell(7).SetCellValue(result[i].单据状态);
row.CreateCell(8).SetCellValue(result[i].业务类型);
row.CreateCell(9).SetCellValue(result[i].报销状态);
row.CreateCell(10).SetCellValue(result[i].支付状态);
#endregion
row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellType(CellType.String);
var cellText = row.Cells[0].StringCellValue;//获取当前行 第1列的单元格的值
if (cellText == temp)//上下行相等，记录要合并的最后一行
{
end = i;
}
else//上下行不等，记录
{
if (start != end)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region = new CellRangeAddress(start + 1, end + 1, 0, 0);
sheet.AddMergedRegion(region);
}
start = i;
end = i;
temp = cellText;
}
}
}
#endregion
#region 3. 格式化并保存
for (int i = 0; i < 7; i++)
{
sheet.AutoSizeColumn(i);//i：根据标题的个数设置自动列宽
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
#endregion
return File(ms, "application/vnd.ms-excel", "出入库单据报销明细.xls");
}
public string GetSqlIn(string sqlParam, string columnName)
{
int width = sqlParam.IndexOf("'", 1) - 1;
string temp = string.Empty;
for (int i = 0; i < sqlParam.Length; i += 1000 * (width + 3))
{
if (i + 1000 * (width + 3) - 1 < sqlParam.Length)
{
temp = temp + sqlParam.Substring(i, 1000 * (width + 3) - 1)
+ ") OR " + columnName + " IN (";
}
else
{
temp = temp + sqlParam.Substring(i, sqlParam.Length - i);
}
}
return temp;
}
/**
* 解决Oracle where条件中in条件超过1000大小 列表中的最大表达式数为 1000
* 把以逗号（','）分割的字符串分割成固定大小的多个组
* 把分割开的字符串用 or关键字拼接 然后查询
* param：以逗号分隔的字符串
* eachGroupNum：每组多少个数量
* field：SQL语句中哪个字段查询
*/
public String setConditionByOr(String param, int eachGroupNum, String field)
{
String[] paramArray = param.Split(',');
int inArrayNum = (paramArray.Length % eachGroupNum == 0) ? paramArray.Length / eachGroupNum : paramArray.Length / eachGroupNum + 1;
int m = 0;
int b = 0;
int n = eachGroupNum;
String[] p = new String[inArrayNum];
String[] sql = new String[inArrayNum];
for (int k = 0; k < paramArray.Length; ++k) {
if (b < inArrayNum) {
p[b] = "";
for (; m < n; ++m) {
if (m >= paramArray.Length) { break; }
int temp = b;
String[] tmp = p;
tmp[temp] = tmp[temp] + paramArray[m] + ",";
}
p[b] = p[b].Substring(0, p[b].LastIndexOf(","));
sql[b] = field + " in (" + p[b] + ")";
++b;
n += eachGroupNum;
}
}
String condition = "";
for (int j = 0; j < sql.Length; ++j) {
condition = condition + sql[j] + " or ";
}
condition = condition.Substring(0, condition.LastIndexOf(" or "));
return condition;
}
#endregion
浙公网安备 33010602011771号