public IActionResult Index()
{
//签约公司
var companyInfoDtos = _commonDataService.CompanyInfos();
var selectListItems = companyInfoDtos.Select(s => new SelectListItem(s.CompanyName, s.CompanyCode)).ToList();
selectListItems.Insert(0, new SelectListItem("缺省", "-99"));
selectListItems.Insert(0, new SelectListItem("请选择", ""));
ViewBag.Company = selectListItems;
//订单来源（采购大类）
var types = _commonDataService.GetRelevanceInfos(5).ToList();
var typesListItem = types.Select(s => new SelectListItem(s.Value, s.Key)).ToList();
typesListItem.Insert(0, new SelectListItem("请选择", "0"));
ViewBag.Types = typesListItem;
//物料分类
var productCategoryDtos = _commonDataService.GetProductCategorys(level: 1, type: "MaterialClass");
var productListItem = productCategoryDtos.Select(s => new SelectListItem(s.Name, s.ProductCatID)).ToList();
productListItem.Insert(0, new SelectListItem("请选择", ""));
ViewBag.productCategories = productListItem;
//支出类型一级
var payOutTypes = _commonDataService.GetDicInfos(723).ToList();
var payOutTypeListItem = payOutTypes.Select(s => new SelectListItem(s.DictName, s.DictId.ToString())).ToList();
payOutTypeListItem.Insert(0, new SelectListItem("请选择", "0"));
ViewBag.payOutTypes = payOutTypeListItem;
return(View());
}
/// <summary>
/// 上传 文件
/// </summary>
/// <returns></returns>
public async Task <JsonResult> FileSave(List <IFormFile> filesss)
{
var count = 0;
var files = Request.Form.Files;
long size = files.Sum(f => f.Length);
string webRootPath = _hostingEnvironment.WebRootPath;
string contentRootPath = _hostingEnvironment.ContentRootPath;
Dictionary <string, double> dic = new Dictionary <string, double>();
foreach (var formFile in files)
{
if (formFile.Length > 0)
{
string fileExt = FileUtils.GetFileExt(formFile.FileName); //文件扩展名，不含“.”
double fileSize = Math.Round(Convert.ToDouble(Convert.ToDouble(formFile.Length) / 1024 / 1024), 2); //获得文件大小，以字节为单位
string newFileName = formFile.FileName.Substring(0, formFile.FileName.LastIndexOf(".", StringComparison.Ordinal) - 1) + $"-{DateTime.Now:yyyy-MM-dd-HH-mm}." + fileExt; //随机生成新的文件名
var filePath = contentRootPath + "/upload/" + newFileName;
using (var stream = new FileStream(filePath, FileMode.Create))
{
await formFile.CopyToAsync(stream);
}
//读取Excel ----->DB
var importDtos = new List <FixedAssetsDepreciationImportDto>();
#region 读取Excel
FileInfo ff = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(ff))
{
var sheet = package.Workbook.Worksheets.ToList()[0];
importDtos = (from cell in sheet.Cells[sheet.Dimension.Address]
where cell.Start.Row > 1
group cell by cell.Start.Row into rr
select new FixedAssetsDepreciationImportDto()
{
CurrentRow = rr.Key,
FixedAssetsCode = rr.ToList()[0].Value.ToString(),
LineNum = rr.ToList()[1].Value == null ? (int?)null : Convert.ToInt32(rr.ToList()[1].Value),
Period = rr.ToList()[2].Value.ToString(),
DepartNamePath = rr.ToList()[3].Value.ToString(),
Amount = rr.ToList()[4].Value == null ? (decimal?)null : Convert.ToDecimal(rr.ToList()[4].Value),
CompanyName = rr.ToList()[5].Value.ToString(),
}).ToList();
}
#endregion
#region 数据校验
//为空校验
var isNull = importDtos.Where(s => s.Amount == null ||
s.LineNum == null ||
string.IsNullOrEmpty(s.FixedAssetsCode) ||
string.IsNullOrEmpty(s.DepartNamePath) ||
string.IsNullOrEmpty(s.CompanyName) ||
string.IsNullOrEmpty(s.Period))
.Select(s => s.CurrentRow).Distinct().ToList();
if (isNull.Count > 0)
{
return(Json(new JsonFlag(0, "有空数据，行号：" + string.Join(',', isNull))));
}
//多月数据校验
var nu = importDtos.Select(s => s.Period).Distinct();
if (nu.Count() > 1)
{
return(Json(new JsonFlag(0, "核算期间存在多月数据，如下：<br/>" + string.Join("，<br/>", nu))));
}
//数据准确性校验
var excelDepartNames = importDtos.Select(s => s.DepartNamePath).Distinct();
var departMentsByVersion = _commonDataService.GetDepartMentsByVersion(importDtos.Select(s => s.Period).FirstOrDefault()).ToList();
var exceptDeparts = excelDepartNames.Except(departMentsByVersion.Select(s => s.NamePath));//不在 部门版本中的 部门名称
if (exceptDeparts.Any())
{
return(Json(new JsonFlag(0, "部门名称无法找到对应的部门编号，如下：<br/>" + string.Join("，<br/>", exceptDeparts))));
}
var excelCompanyNames = importDtos.Select(s => s.CompanyName).Distinct();
var companys = _commonDataService.CompanyInfos().ToList();
var exceptCompanys = excelCompanyNames.Except(companys.Select(s => s.CompanyName));
if (exceptCompanys.Any())
{
return(Json(new JsonFlag(0, "签约公司无法找打对应的编号，如下：<br/>" + string.Join("，<br/>", exceptCompanys))));
}
#endregion
var histories = new List <FixedAssetsDepreciationHistory>();
foreach (var item in importDtos)
{
var model = new FixedAssetsDepreciationHistory()
{
FixedAssetsCode = item.FixedAssetsCode,
LineNum = item.LineNum.Value,
Period = item.Period,
DepartID = departMentsByVersion.Where(s => s.NamePath == item.DepartNamePath).Select(s => s.DepartID).FirstOrDefault(),
DepartNamePath = item.DepartNamePath,
Amount = item.Amount.Value,
CompanyCode = companys.Where(s => s.CompanyName == item.CompanyName).Select(s => s.CompanyCode).FirstOrDefault(),
CompanyName = item.CompanyName,
CreateTime = DateTime.Now,
CreateUserID = Convert.ToInt32(User.Claims.FirstOrDefault(c => c.Type == "userId")?.Value),
CreateUserName = User.Identity.Name,
LastUpdateTime = DateTime.Now
};
histories.Add(model);
}
count = _expenseService.AddFixedAssetsDepreciation(histories);
dic.Add(newFileName, fileSize);
}
}
var tem = dic.Select(s => new { newFileName = s.Key, size = s.Value });
return(Json(new JsonFlag(1, $"成功导入{count}条数据，到数据库", tem)));
}
public PagedResultDto <CostAccountConfigOut> GetAccountConfigsByPage(CostAccountConfigInput queryModel)
{
#region expression表达式
var expression = Expression(queryModel);
#endregion
//var count = _costAccountConfigRepository.Count(expression);
var model = _costAccountConfigRepository.PageListSelect(expression, s => new CostAccountConfig()
{
Id = s.Id,
CompanyCode = s.CompanyCode,
OrderType = s.OrderType,
ContentType = s.ContentType,
ProductCat1 = s.ProductCat1,
ProductCat2 = s.ProductCat2,
PayOutType1 = s.PayOutType1,
PayOutType2 = s.PayOutType2
}, queryModel.CurrentPage, queryModel.PageSize, new Tuple <Expression <Func <CostAccountConfig, object> >, SortOrder>(s => s.Id, SortOrder.Descending));
//签约公司
var companyInfos = _commonDataService.CompanyInfos();
//订单类型
var relevanceInfos = _commonDataService.GetRelevanceInfos(5);
//采购类型
var contentTypes = _commonDataService.GetDicInfos(701);
//物料分类
var productCat1s = _commonDataService.GetProductCategorys(level: 1, type: "MaterialClass");
//物料
var productCat2s = _commonDataService.GetProductCategorys(level: 2, type: "Material");
//支出类型1
var payOutType1s = _commonDataService.GetDicInfos(723);
var payOutType2s = new List <DicInfoDto>();
payOutType1s.ForEach(s =>
{
payOutType2s.AddRange(_commonDataService.GetDicInfos(s.DictId));
});
var models = new List <CostAccountConfigOut>();
foreach (var item in model.Items)
{
var tem = new CostAccountConfigOut()
{
Id = item.Id,
CompanyCode = item.CompanyCode,
CompanyName = item.CompanyCode == "-99" ? "缺省" : companyInfos.Where(s => s.CompanyCode == item.CompanyCode.ToString()).Select(s => s.CompanyName).FirstOrDefault(),
OrderType = item.OrderType,
OrderTypeName = relevanceInfos.Where(s => s.Key == item.OrderType.ToString()).Select(s => s.Value).FirstOrDefault(),
ContentType = item.ContentType,
ContentTypeName = item.ContentType == -99 ? "缺省" : contentTypes.Where(s => s.DictId == item.ContentType).Select(s => s.DictName).FirstOrDefault(),
ProductCat1 = item.ProductCat1,
ProductCat1Name = productCat1s.Where(s => s.ProductCatID == item.ProductCat1.ToString()).Select(s => s.Name).FirstOrDefault(),
ProductCat2 = item.ProductCat2,
ProductCat2Name = item.ProductCat2 == "-99" ? "缺省" : productCat2s.Where(s => s.ProductCatID == item.ProductCat2.ToString()).Select(s => s.Name).FirstOrDefault(),
PayOutType1 = item.PayOutType1,
PayOutType1Name = payOutType1s.Where(s => s.DictId == item.PayOutType1).Select(s => s.DictName).FirstOrDefault(),
PayOutType2 = item.PayOutType2,
PayOutType2Name = item.PayOutType2 == -99 ? "缺省" : payOutType2s.Where(s => s.DictId == item.PayOutType2).Select(s => s.DictName).FirstOrDefault(),
};
models.Add(tem);
}
return(new PagedResultDto <CostAccountConfigOut>(model.Total, models));
}