public ActionResult MaterialImport(HttpPostedFileBase fileImport)
{
JsonObjectResult jsonObjectResult = new JsonObjectResult();
string sError = "";
string userName = string.Empty;
if (User.Identity.IsAuthenticated)
{
userName = User.Identity.Name;
}
try
{
if (fileImport != null)
{
List<ImOrExMaterialDefine> list = null;
List<AscmMaterialItem> listAscmMaterialItem = new List<AscmMaterialItem>();
using (Stream stream = fileImport.InputStream)
{
list = new List<ImOrExMaterialDefine>();
NPOI.SS.UserModel.IWorkbook wb = NPOI.SS.UserModel.WorkbookFactory.Create(stream);
ISheet sheet = wb.GetSheet("Sheet1");
IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current;
if (row.RowNum != 0)
{
List<NPOI.SS.UserModel.ICell> iCellList = new List<NPOI.SS.UserModel.ICell>();
for (int i = 0; i < ImOrExMaterialDefine.GetList().Count; i++)
{
NPOI.SS.UserModel.ICell iCell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
iCellList.Add(iCell);
}
ImOrExMaterialDefine ImportMaterial = new ImOrExMaterialDefine();
if (iCellList[0].ToString() != null)
ImportMaterial.materialDocnumber = iCellList[0].ToString().Trim();
if (iCellList[1].ToString() != null)
ImportMaterial.wipSupplyType = iCellList[1].ToString().Trim();
if (iCellList[2].ToString() != null)
ImportMaterial.materialDescription = iCellList[2].ToString().Trim();
if (iCellList[3].ToString() != null)
ImportMaterial.zMtlCategoryStatus = iCellList[3].ToString().Trim();
if (iCellList[4].ToString() != null)
ImportMaterial.dMtlCategoryStatus = iCellList[4].ToString().Trim();
if (iCellList[5].ToString() != null)
ImportMaterial.wMtlCategoryStatus = iCellList[5].ToString().Trim();
ImportMaterial.rowNumber = row.RowNum + 1;
list.Add(ImportMaterial);
}
}
}
if (list != null && list.Count > 0)
{
foreach (ImOrExMaterialDefine ImportMaterial in list)
{
object obj = YnDaoHelper.GetInstance().nHibernateHelper.GetObject("select count(*) from AscmMaterialItem where docNumber = '" + ImportMaterial.materialDocnumber + "'");
if (obj == null)
throw new Exception("查询异常！");
int iCount = 0;
if (int.TryParse(obj.ToString(), out iCount) && iCount > 0)
{
List<AscmMaterialItem> tempList = AscmMaterialItemService.GetInstance().GetList("from AscmMaterialItem where docNumber = '" + ImportMaterial.materialDocnumber + "'");
if (tempList != null && tempList.Count > 0)
{
foreach (AscmMaterialItem ascmMaterialItem in tempList)
{
if (ImportMaterial.zMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.inStock))
{
ascmMaterialItem.zMtlCategoryStatus = MtlCategoryStatusDefine.inStock;
}
else if (ImportMaterial.zMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.mixStock))
{
ascmMaterialItem.zMtlCategoryStatus = MtlCategoryStatusDefine.mixStock;
}
else if (ImportMaterial.zMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.preStock))
{
ascmMaterialItem.zMtlCategoryStatus = MtlCategoryStatusDefine.preStock;
}
else if (string.IsNullOrEmpty(ImportMaterial.zMtlCategoryStatus))
{
ascmMaterialItem.wMtlCategoryStatus = "";
}
else if (!string.IsNullOrEmpty(ImportMaterial.zMtlCategoryStatus))
{
sError += "物料编码[" + ascmMaterialItem.docNumber + "]总装备料形式书写不正确:" + ImportMaterial.zMtlCategoryStatus;
continue;
}
if (ImportMaterial.dMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.inStock))
{
ascmMaterialItem.dMtlCategoryStatus = MtlCategoryStatusDefine.inStock;
}
else if (ImportMaterial.dMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.mixStock))
{
ascmMaterialItem.dMtlCategoryStatus = MtlCategoryStatusDefine.mixStock;
}
else if (ImportMaterial.dMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.preStock))
{
ascmMaterialItem.dMtlCategoryStatus = MtlCategoryStatusDefine.preStock;
}
else if (string.IsNullOrEmpty(ImportMaterial.dMtlCategoryStatus))
{
ascmMaterialItem.wMtlCategoryStatus = "";
}
else if (!string.IsNullOrEmpty(ImportMaterial.dMtlCategoryStatus))
{
sError += "物料编码[" + ascmMaterialItem.docNumber + "]电装备料形式书写不正确:" + ImportMaterial.dMtlCategoryStatus;
continue;
}
if (ImportMaterial.wMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.inStock))
{
ascmMaterialItem.wMtlCategoryStatus = MtlCategoryStatusDefine.inStock;
}
else if (ImportMaterial.wMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.mixStock))
{
ascmMaterialItem.wMtlCategoryStatus = MtlCategoryStatusDefine.mixStock;
}
else if (ImportMaterial.wMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.preStock))
{
ascmMaterialItem.wMtlCategoryStatus = MtlCategoryStatusDefine.preStock;
}
else if (string.IsNullOrEmpty(ImportMaterial.wMtlCategoryStatus))
{
ascmMaterialItem.wMtlCategoryStatus = "";
}
else if (!string.IsNullOrEmpty(ImportMaterial.wMtlCategoryStatus))
{
sError += "物料编码[" + ascmMaterialItem.docNumber + "]其他备料形式书写不正确:" + ImportMaterial.wMtlCategoryStatus;
continue;
}
ascmMaterialItem.modifyTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
ascmMaterialItem.modifyUser = userName;
listAscmMaterialItem.Add(ascmMaterialItem);
}
}
}
else
{
sError +="未找到对应的物料编码:" + ImportMaterial.materialDocnumber+"<br />";
}
}
using (ITransaction tx = YnDaoHelper.GetInstance().nHibernateHelper.GetCurrentSession().BeginTransaction())
{
try
{
if (listAscmMaterialItem != null && listAscmMaterialItem.Count > 0)
{
YnDaoHelper.GetInstance().nHibernateHelper.UpdateList(listAscmMaterialItem);
}
tx.Commit();
sError += "【成功更新" + listAscmMaterialItem.Count.ToString() + "条记录!】";
jsonObjectResult.message = sError;
jsonObjectResult.result = true;
}
catch (Exception ex)
{
tx.Rollback();
sError += ex.Message;
}
}
}
}
}
catch (Exception ex)
{
jsonObjectResult.message += ex.Message;
YnBaseClass2.Helper.LogHelper.GetLog().Error(ex.Message,ex);
}
string sReturn = JsonConvert.SerializeObject(jsonObjectResult);
return Content(sReturn);
}
public ActionResult MaterialExport(string queryWord, string queryType, string queryStarDocnumber, string queryEndDocnumber, string zStatus, string dStatus, string wStatus, string queryDescribe)
{
List<ImOrExMaterialDefine> list = new List<ImOrExMaterialDefine>();
NPOI.SS.UserModel.IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet("Sheet1");
sheet.SetColumnWidth(0, 20 * 256);
sheet.SetColumnWidth(1, 13 * 256);
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(3, 15 * 256);
sheet.SetColumnWidth(4, 15 * 256);
sheet.SetColumnWidth(5, 15 * 256);
int iRow = 0;
NPOI.SS.UserModel.IRow titleRow = sheet.CreateRow(0);
titleRow.Height = 20 * 20;
for (int i = 0; i < ImOrExMaterialDefine.GetList().Count; i++)
{
titleRow.CreateCell(i).SetCellValue(ImOrExMaterialDefine.DisplayText(ImOrExMaterialDefine.GetList()[i].ToString()));
}
YnBaseDal.YnPage ynPage = new YnBaseDal.YnPage();
List<AscmMaterialItem> listAscmMaterialItem = AscmMaterialItemService.GetInstance().GetList(ynPage, "", "", queryWord, queryType, queryStarDocnumber, queryEndDocnumber, zStatus, dStatus, wStatus, queryDescribe);
if (listAscmMaterialItem != null && listAscmMaterialItem.Count > 0)
{
ImOrExMaterialDefine titleExportMaterial = new ImOrExMaterialDefine();
List<string> titleCols = ImOrExMaterialDefine.GetList();
titleExportMaterial.materialDocnumber = ImOrExMaterialDefine.DisplayText(titleCols[0]);
titleExportMaterial.wipSupplyType = ImOrExMaterialDefine.DisplayText(titleCols[1]);
titleExportMaterial.materialDescription = ImOrExMaterialDefine.DisplayText(titleCols[2]);
titleExportMaterial.zMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[3]);
titleExportMaterial.dMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[4]);
titleExportMaterial.wMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[5]);
titleExportMaterial.rowNumber = iRow++;
list.Add(titleExportMaterial);
foreach (AscmMaterialItem ascmMaterialItem in listAscmMaterialItem)
{
ImOrExMaterialDefine contentExportMaterial = new ImOrExMaterialDefine();
contentExportMaterial.materialDocnumber = ascmMaterialItem.docNumber;
contentExportMaterial.wipSupplyType = AscmMaterialItem.WipSupplyTypeDefine.DisplayText(ascmMaterialItem.wipSupplyType);
contentExportMaterial.materialDescription = ascmMaterialItem.description;
contentExportMaterial.zMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.zMtlCategoryStatus);
contentExportMaterial.dMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.dMtlCategoryStatus);
contentExportMaterial.wMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.wMtlCategoryStatus);
contentExportMaterial.rowNumber = iRow++;
list.Add(contentExportMaterial);
}
}
if (list != null && list.Count > 0)
{
foreach (ImOrExMaterialDefine ExportMaterial in list)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(ExportMaterial.rowNumber);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue(ExportMaterial.materialDocnumber);
row.CreateCell(1).SetCellValue(ExportMaterial.wipSupplyType);
row.CreateCell(2).SetCellValue(ExportMaterial.materialDescription);
row.CreateCell(3).SetCellValue(ExportMaterial.zMtlCategoryStatus);
row.CreateCell(4).SetCellValue(ExportMaterial.dMtlCategoryStatus);
row.CreateCell(5).SetCellValue(ExportMaterial.wMtlCategoryStatus);
}
}
}
catch (Exception ex)
{
throw ex;
}
byte[] buffer = new byte[] { };
using (System.IO.MemoryStream stream = new MemoryStream())
{
wb.Write(stream);
buffer = stream.GetBuffer();
}
return File(buffer, "application/vnd.ms-excel", "备料形式维护.xls");
}