Excel数据导出导入 V3

/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
public async Task<(string fielName, byte[] bytes)> ExportMaterialCodeMapping(ExportMaterialCodeMappingReq input, CancellationToken token)
{
	IQueryable<ExportMaterialCodeMappingData>? q_mappingData;

	if (input.BusinessPartnerRole == JOCAPI.Models.Enums.BusinessPartnerRoleCategory.Customer)
	{
		q_mappingData = from a in _businessContext.Query<data_material_custom_mapping>().Where(w => w.customer_code == input.BusinessPartnerCode)
						join b in _businessContext.Query<data_material>() on a.material_id equals b.row_id
						select new ExportMaterialCodeMappingData()
						{
							MaterialCode = b.material_code,
							MaterialName = b.material_name,
							MappingCode = a.customer_material_code ?? "",
							MappingName = a.customer_material_name ?? ""
						};
	}
	else if (input.BusinessPartnerRole == JOCAPI.Models.Enums.BusinessPartnerRoleCategory.Supplier)
	{
		q_mappingData = from a in _businessContext.Query<data_material_supplier_mapping>().Where(w => w.supplier_code == input.BusinessPartnerCode)
						join b in _businessContext.Query<data_material>() on a.material_id equals b.row_id
						select new ExportMaterialCodeMappingData()
						{
							MaterialCode = b.material_code,
							MaterialName = b.material_name,
							MappingCode = a.supplier_material_code ?? "",
							MappingName = a.supplier_material_name ?? ""
						};
	}
	else
	{
		throw new RequestErrorException("无法识别业务合作伙伴角色：" + input.BusinessPartnerRole);
	}


	var mappings = new List<(string propertyName, string caption)>()
	{
		(nameof(ExportMaterialCodeMappingData.MaterialCode ),"团餐易物料代码"),
		(nameof(ExportMaterialCodeMappingData.MaterialName ),"团餐易物料名称"),
		(nameof(ExportMaterialCodeMappingData.MappingCode ),"合作伙伴物料代码"),
		(nameof(ExportMaterialCodeMappingData.MappingName ),"合作伙伴物料名称")
	};
	var data = await q_mappingData.ToListAsync(token);
	var bytes = NOPIHelper.saveExcel(data, mappings, true, NOPIHelper.WorkbookType.xlsx);
	string fielName = $"合作伙伴物料映射-{input.BusinessPartnerCode}-{input.BusinessPartnerRole}.xlsx";
	return (fielName, bytes);
}

/// <summary>
/// 导出合作伙伴物料编码对照表
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
[HttpPost]
public async Task<IActionResult> ExportMaterialCodeMapping(ExportMaterialCodeMappingReq input)
{
	var (fileName, bytes) = await _business.ExportMaterialCodeMapping(input, HttpContext.RequestAborted);

	MemoryStream ms = new MemoryStream(bytes);
	ms.Position = 0;

	// 添加命名空间：Microsoft.AspNetCore.Http
	Response.Headers.Append("Access-Control-Expose-Headers", "Content-Disposition");

	// 返回中文文件名，需要使用Uri.EscapeDataString 编码一下，要不然文件名中中文部分会异常
	return File(ms, "application/octet-stream", Uri.EscapeDataString(fileName));
}

/// <summary>
/// 业务合作伙伴物料映射导入
/// </summary>
/// <param name="data"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
[Transactional<JOCBusinessContext>] // 使用事务
public async Task<List<ImportRes>> Import(ImportMaterialCodeMappingReq input, CancellationToken cancellationToken)
{
 List<ImportRes> result = input.MaterialCodeMappingData.Select(s => new ImportRes()
 {
	 Id = s.Id,
	 LstErrMessage = new List<string>()
 }).ToList();

 var q_tempData = await _tempDataBusiness.Value.AddTempData(input.MaterialCodeMappingData.Select(s => s.MaterialCode).ToList(), cancellationToken);
 var q_data = from a in _businessContext.Query<data_material>()
			  join b in q_tempData on a.material_code equals b.data_value
			  select new
			  {
				  a.material_code,
				  material_id = a.row_id
			  };

 var dataMaterial = await q_data.ToListAsync(cancellationToken);

 // 物料不存在

 var errMaterialData = input.MaterialCodeMappingData.Where(w => dataMaterial.Any(x => x.material_code == w.MaterialCode) == false).Select(s => s.Id).ToList();
 if (errMaterialData.Count > 0)
 {
	 result.Where(w => errMaterialData.Contains(w.Id)).ForEach(item => item.LstErrMessage.Add("物料编码错误"));
 }

 try
 {
	 var dictMaterial = q_data.ToListAsync(cancellationToken);

	 if (input.BusinessPartnerRole == JOCAPI.Models.Enums.BusinessPartnerRoleCategory.Customer)
	 {
		 if (input.IsClear)
			 await _businessContext.Query<data_material_custom_mapping>().Where(w => w.customer_code == input.BusinessPartnerCode).BatchDeleteAsync(cancellationToken);

		 var newData = (from a in input.MaterialCodeMappingData
						join b in dataMaterial on a.MaterialCode equals b.material_code
						select new data_material_custom_mapping()
						{
							row_id = IDHelper.GetID(),
							material_id = b.material_id,
							material_code = b.material_code,
							customer_code = input.BusinessPartnerCode,
							customer_material_code = a.MappingCode,
							customer_material_name = a.MappingName
						}).ToList();
		 if (newData.Count > 0)
		 {
			 newData.InitCommonCreate(_operator, true);
			 await _businessContext.BulkInsertAsync(newData, cancellationToken: cancellationToken);
		 }
	 }
	 else if (input.BusinessPartnerRole == JOCAPI.Models.Enums.BusinessPartnerRoleCategory.Supplier)
	 {
		 if (input.IsClear)
			 await _businessContext.Query<data_material_supplier_mapping>().Where(w => w.supplier_code == input.BusinessPartnerCode).BatchDeleteAsync(cancellationToken);

		 var newData = (from a in input.MaterialCodeMappingData
						join b in dataMaterial on a.MaterialCode equals b.material_code
						select new data_material_supplier_mapping()
						{
							row_id = IDHelper.GetID(),
							material_id = b.material_id,
							material_code = b.material_code,
							supplier_code = input.BusinessPartnerCode,
							supplier_material_code = a.MappingCode,
							supplier_material_name = a.MappingName
						}).ToList();
		 if (newData.Count > 0)
		 {
			 newData.InitCommonCreate(_operator, true);
			 await _businessContext.BulkInsertAsync(newData, cancellationToken: cancellationToken);
		 }
	 }
	 else
	 {
		 throw new RequestErrorException("无法识别业务合作伙伴角色：" + input.BusinessPartnerRole);
	 }
 }
 catch
 {
	 throw;
 }
 finally
 {
	 await q_tempData.BatchDeleteAsync(cancellationToken);
	 return result;
 }
}

/// <summary>
/// 导入合作伙伴物料编码对照表
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
[HttpPost]
public async Task<List<ImportRes>> ImportMaterialCodeMappingAsync(ImportMaterialCodeMappingReq input)
{
	return await _business.ImportMaterialCodeMappingAsync(input, HttpContext.RequestAborted);
}