/*
*
* public bool PostAsset( AssetImport assetImportItem)
* {
* //adding value to CSV file
* bool status = false;
* try
* {
* objCsvProcessingTest.addNewValuetoCSV(assetImportItem);
* objCsvProcessingTest.AssetImportsList().Add(assetImportItem);
*
* status = true;
* }
* catch (Exception ex)
* {
* throw ex;
* }
* return status;
* }
*
*/
public List <AssetImport> PostAddList(AssetImport assetImportItem)
{
try
{
lstAssetImport = objCsvProcessingTest.AssetImportsList();
lstAssetImport.Add(assetImportItem);
}
catch (Exception ex)
{
throw ex;
}
return(lstAssetImport);
}
public List <AssetImport> DeleteAsset(string AssetID)
{
try
{
lstAssetImport = objCsvProcessingTest.AssetImportsList();
AssetImport objAssetImport = lstAssetImport.Where(p => p.assetId == AssetID).FirstOrDefault();
if (objAssetImport != null)
{
lstAssetImport.RemoveAll(x => x.assetId.Equals(AssetID));
}
else
{
Console.WriteLine("ID doesnot exist");
}
}
catch (Exception ex)
{
throw ex;
}
return(lstAssetImport);
}
public List <AssetImport> PutAsset(AssetImport assetImportItem)
{
try
{
lstAssetImport = objCsvProcessingTest.AssetImportsList();
var ispresento = lstAssetImport.Where(x => x.assetId.Equals(assetImportItem.assetId)).Any();
AssetImport objAssetImport = lstAssetImport.Where(x => x.assetId.Equals(assetImportItem.assetId)).FirstOrDefault();
if (objAssetImport != null)
{
objAssetImport.country = assetImportItem.country;
objAssetImport.createdBy = assetImportItem.createdBy;
objAssetImport.description = assetImportItem.description;
objAssetImport.email = assetImportItem.email;
objAssetImport.mimeType = assetImportItem.mimeType;
objAssetImport.fileName = assetImportItem.fileName;
}
}
catch (Exception ex)
{
throw ex;
}
return(lstAssetImport);
}
public ActionResult Import(AssetImport model, HttpPostedFileBase file)
{
string action = Request["Submit"];
if (action == "cancel")
{
return(RedirectToAction("Index"));
}
if (!model.TypeId.HasValue || model.TypeId.Value <= 0)
{
ModelState.AddModelError("TypeId", "请选择分类.");
}
if (file == null)
{
ModelState.AddModelError("DataFile", "文件不能为空.");
}
if (file != null)
{
if (Path.GetExtension(file.FileName).ToLower() != ".xlsx")
{
ModelState.AddModelError("DataFile", "文件格式必须是.xlsx");
return(View(model));
}
}
if (ModelState.IsValid)
{
try
{
var typeAttributes = this.AssetAttributeService.GetAll();
var barcodeAttr = typeAttributes.Where(it => it.Name == "编号").FirstOrDefault();
if (barcodeAttr == null)
{
ModelState.AddModelError("", "[编号]是资产必须的,请先在系统添加[编号]属性.");
return(View(model));
}
var buyDateAttr = typeAttributes.Where(it => it.Name == "采购日期").FirstOrDefault();
if (buyDateAttr == null)
{
ModelState.AddModelError("", "[采购日期]是资产必须的,请先在系统添加[采购日期]属性.");
return(View(model));
}
var orderAreaAttr = typeAttributes.Where(it => it.Name == "入账区域").FirstOrDefault();
if (orderAreaAttr == null)
{
ModelState.AddModelError("", "[入账区域]是资产必须的,请先在系统添加[入账区域]属性.");
return(View(model));
}
var typeAttribute = this.AssetTypeService.GetTypeById(model.TypeId.Value);
var barcodeRelation = typeAttribute.Attributes.Where(it => it.Id == barcodeAttr.Id).FirstOrDefault();
if (null == barcodeRelation)
{
ModelState.AddModelError("", "[编号]是资产必须的,请先在系统将[编号]和导入类型进行关联设置.");
return(View(model));
}
var buyDateRelation = typeAttribute.Attributes.Where(it => it.Id == buyDateAttr.Id).FirstOrDefault();
if (null == buyDateRelation)
{
ModelState.AddModelError("", "[采购日期]是资产必须的,请先在系统将[采购日期]和导入类型进行关联设置.");
return(View(model));
}
var orderAreaRelation = typeAttribute.Attributes.Where(it => it.Id == orderAreaAttr.Id).FirstOrDefault();
if (null == orderAreaRelation)
{
ModelState.AddModelError("", "[入账区域]是资产必须的,请先在系统将[入账区域]和导入类型进行关联设置.");
return(View(model));
}
var workbook = new Aspose.Cells.Workbook(file.InputStream);
var ws = workbook.Worksheets[0];
var rows = ws.Cells.MaxDataRow;
var cols = ws.Cells.MaxDataColumn;
//检查必填字段[采购日期]
bool hasBuyDate = false;
int colBuyDateIndex = -1;
for (int i = 0; i <= cols; i++)
{
if (null != ws.Cells[0, i].Value && ws.Cells[0, i].Value.ToString() == "采购日期")
{
hasBuyDate = true;
colBuyDateIndex = i;
break;
}
}
if (!hasBuyDate)
{
ModelState.AddModelError("", "[采购日期]是资产必须的,请修改表格后再导入.");
return(View(model));
}
//Aspose.Cells.CellArea firstRow=new Aspose.Cells.CellArea()
//{
// StartRow=0,
// EndRow=0,
// StartColumn=0,
// EndColumn=cols
//};
//ws.Cells.FindString("入账区域",ws.Cells[0,0],firstRow);
//Cell Cells.FindString(inputString,previousCell,area);
bool hasOrderArea = false;
int colOrderAreaIndex = -1;
for (int i = 0; i <= cols; i++)
{
if (null != ws.Cells[0, i].Value && ws.Cells[0, i].Value.ToString() == "入账区域")
{
hasOrderArea = true;
colOrderAreaIndex = i;
break;
}
}
if (!hasOrderArea)
{
ModelState.AddModelError("", "[入账区域]是资产必须的,请修改表格后再导入.");
return(View(model));
}
bool hasUseUser = false;
int colsUseUserIndex = -1;
for (int i = 0; i <= cols; i++)
{
if (null != ws.Cells[0, i].Value && ws.Cells[0, i].Value.ToString() == "使用人")
{
hasUseUser = true;
colsUseUserIndex = i;
break;
}
}
var allUsers = this.UserService.GetAllUsers();
var defaultUser = this.UserService.GetAllUsers().Where(it => it.Email.ToLower() == "*****@*****.**").First();
if (defaultUser == null)
{
ModelState.AddModelError("", "未在系统找到默认使用人:[email protected]");
return(View(model));
}
string codePrefix = "";
switch (typeAttribute.Name)
{
case "主机":
codePrefix = "HC";
break;
case "显示器":
codePrefix = "LD";
break;
case "MACMINI电脑":
codePrefix = "MC";
break;
case "笔记本电脑":
codePrefix = "NC";
break;
case "平板电脑":
codePrefix = "TC";
break;
case "手机":
codePrefix = "MP";
break;
case "午休床":
codePrefix = "TY";
break;
case "其他固定资产":
codePrefix = "OA";
break;
case "其他低值易耗品":
codePrefix = "OG";
break;
default:
codePrefix = "OA";
break;
}
List <AssetModel> importList = new List <AssetModel>();
for (int row = 1; row <= rows; row++)
{
AssetModel newModel = new AssetModel()
{
TypeId = model.TypeId.Value,
Status = AssetStatus.Normal,
UserId = defaultUser.Id,
AssetInfoes = new List <AssetInfoModel>()
};
#region 属性
for (int column = 0; column <= cols; column++)
{
//资产使用人设置
if (hasUseUser && colsUseUserIndex == column)
{
if (ws.Cells[row, column].Value != null && ws.Cells[row, column].Value.ToString().Trim() != "")
{
var assetUser = allUsers.Where(it => it.EnglishName.Trim().ToLower() == ws.Cells[row, column].Value.ToString().Trim().ToLower()).FirstOrDefault();
if (assetUser == null)
{
ModelState.AddModelError("", "未在系统找到使用人:" + ws.Cells[row, column].Value.ToString());
return(View(model));
}
newModel.UserId = assetUser.Id;
}
continue;
}
//采购日期
if (colBuyDateIndex == column)
{
if (ws.Cells[row, column].Value != null && ws.Cells[row, column].Value.ToString().Trim() != "")
{
DateTime tempDate = DateTime.Now;
if (!DateTime.TryParse(ws.Cells[row, column].Value.ToString(), out tempDate))
{
ModelState.AddModelError("", "第" + (row + 1) + "行[采购日期]日期格式不正确.");
return(View(model));
}
var attrModel = new AssetInfoModel();
attrModel.AttributeId = buyDateAttr.Id;
attrModel.AttributeValue = tempDate.ToString("MM/dd/yyyy");
newModel.AssetInfoes.Add(attrModel);
}
else
{
var attrModel = new AssetInfoModel();
attrModel.AttributeId = buyDateAttr.Id;
attrModel.AttributeValue = DateTime.Now.ToString("MM/dd/yyyy");
newModel.AssetInfoes.Add(attrModel);
}
continue;
}
//入账区域
if (colOrderAreaIndex == column)
{
if (ws.Cells[row, column].Value != null && ws.Cells[row, column].Value.ToString().Trim() != "")
{
var text = ws.Cells[row, column].Value.ToString().Trim();
if (text.ToLower() != "sz" && text.ToLower() != "hk")
{
text = "SZ";
}
var attrModel = new AssetInfoModel();
attrModel.AttributeId = orderAreaAttr.Id;
attrModel.AttributeValue = text.ToUpper();
newModel.AssetInfoes.Add(attrModel);
}
else
{
var attrModel = new AssetInfoModel();
attrModel.AttributeId = orderAreaAttr.Id;
attrModel.AttributeValue = "SZ";
newModel.AssetInfoes.Add(attrModel);
}
continue;
}
if (ws.Cells[row, column].Value != null && ws.Cells[row, column].Value.ToString().Trim() != "")
{
var attrCol = typeAttributes.Where(it => it.Name == ws.Cells[0, column].Value.ToString()).FirstOrDefault();
if (attrCol != null)
{
var attrColRelation = typeAttribute.Attributes.Where(it => it.Id == attrCol.Id).FirstOrDefault();
if (attrColRelation != null)
{
//添加其他字段不能并保证不重复写入
var addedAttrIds = newModel.AssetInfoes.Select(it => it.AttributeId).ToList();
if (!addedAttrIds.Contains(attrCol.Id))
{
var attrModel = new AssetInfoModel();
attrModel.AttributeId = attrCol.Id;
attrModel.AttributeValue = ws.Cells[row, column].Value.ToString().Trim();
newModel.AssetInfoes.Add(attrModel);
}
}
}
}
}
//资产类型已经关联的字段而Excel里面没有的都添加为空(确保字段数量一致),除去条码字段,条码属于自动生成
var allAddedAttrIds = newModel.AssetInfoes.Select(it => it.AttributeId).ToList();
foreach (var relatedAttr in typeAttribute.Attributes.Where(it => it.Id != barcodeAttr.Id))
{
if (!allAddedAttrIds.Contains(relatedAttr.Id))
{
newModel.AssetInfoes.Add(new AssetInfoModel()
{
AttributeId = relatedAttr.Id,
AttributeValue = null
});
}
}
#endregion
importList.Add(newModel);
}
ViewBag.Message = this.AssetService.Import(importList, codePrefix);
return(View(model));
}
catch (Exception ex)
{
ViewBag.Message = ex.Message;
}
}
return(View(model));
}
/// <summary>
/// 导入
/// </summary>
/// <returns></returns>
public ActionResult Import()
{
AssetImport model = new AssetImport();
return(View(model));
}