public ActionResult ImportExcel()
{
if (Request.Files.Count == 0)
{
return(View());
}
var httpPostedFileBase = Request.Files[0];
if (httpPostedFileBase != null)
{
var result = new Resuldt();
if (!_starting)
{
_starting = true;
try
{
result = PrPurchaseBo.Instance.ImportExcel(httpPostedFileBase.InputStream,
CurrentUser.Base.SysNo);
}
catch (Exception e)
{
result.Message = string.Format(e.Message);
result.Status = false;
}
finally
{
_starting = false;
}
}
else
{
result.Message = string.Format("正在导入数据，请稍后再操作");
result.Status = false;
}
ViewBag.result = HttpUtility.UrlEncode(JsonConvert.SerializeObject(result).ToString());// HttpUtility.UrlEncode();
}
return(View());
}
/// <summary>
/// 套装商品数据导入Excel
/// </summary>
/// <param name="stream"></param>
/// <param name="SysNo"></param>
/// <returns></returns>
/// 2017-08-25 吴琨 创建
public Resuldt <PdPackagedGoodsEntry> ImportExcel(System.IO.Stream stream, int SysNo)
{
DataTable dt = null;
var cols = DicColsMapping.Select(p => p.Value).ToArray();
#region 基础验证
try
{
dt = ExcelUtil.ImportExcel(stream, cols);
}
catch (Exception ex)
{
return(new Resuldt <PdPackagedGoodsEntry>
{
Message = string.Format("数据导入错误,请选择正确的excel文件"),
Status = false
});
}
if (dt == null)
{
//not all the cols mapped
return(new Resuldt <PdPackagedGoodsEntry>
{
Message = string.Format("请选择正确的excel文件!"),
Status = false
});
}
if (dt.Rows.Count == 0)
{
return(new Resuldt <PdPackagedGoodsEntry>
{
Message = "导入的数据为空!",
Status = false
});
}
#endregion
Resuldt <PdPackagedGoodsEntry> run = new Resuldt <PdPackagedGoodsEntry>();
List <PdPackagedGoodsEntry> listModel = new List <PdPackagedGoodsEntry>();
int fail = 0; //失败记录数
int success = 0; //成功记录数
string failstr = ""; //失败条数记录
for (int i = 0; i < dt.Rows.Count; i++)
{
success++;
PdPackagedGoodsEntry model = new PdPackagedGoodsEntry();
model.PdCode = dt.Rows[i]["商品代码"].ToString();
if (!string.IsNullOrEmpty(model.PdCode))
{
var product = PdProductBo.Instance.GetProductErpCode(dt.Rows[i]["商品代码"].ToString().Trim(), null);
if (product != null)
{
model.PdSysNo = product.SysNo;
model.PdName = product.EasName;
}
else
{
fail++;
failstr += (i + 2) + "、";
dt.Rows.Remove(dt.Rows[i]);
continue;
}
}
model.Company = dt.Rows[i]["单位"].ToString();
model.UnitPrice = Convert.ToDecimal(dt.Rows[i]["单价"]);
model.Count = Convert.ToDecimal(dt.Rows[i]["用量"]);
#region 发料仓库信息
model.WarehouseCode = dt.Rows[i]["发料仓库"].ToString();
if (!string.IsNullOrEmpty(model.WarehouseCode))
{
var WhWarehouse = Hyt.BLL.Web.WhWarehouseBo.Instance.GetModelErpCode(model.WarehouseCode);
if (WhWarehouse != null)
{
model.WarehouseSysNo = WhWarehouse.SysNo;
model.WarehouseName = WhWarehouse.BackWarehouseName;
}
else
{
fail++;
failstr += (i + 2) + "、";
dt.Rows.Remove(dt.Rows[i]);
continue;
}
}
#endregion
model.Remarks = dt.Rows[i]["备注"].ToString();
if (model.PdSysNo > 0 && model.WarehouseSysNo > 0)
{
listModel.Add(model);
}
}
if (success > 0 && dt.Rows.Count > 0)
{
run.Data = null;
}
if (success > 0 && listModel != null)
{
run.listModel = listModel;
}
run.Message = "导入成功" + success + "件商品,失败" + fail + "件商品;";
if (fail > 0)
{
run.Message += "失败原因为:产品编码或仓库erp编码有误,不存在此件商品。失败条数为第" + failstr.Trim('、') + "条。";
}
run.Status = true;
return(run);
}
public Resuldt <OtherOutOfStorageDetailed> OtherImportExcel(System.IO.Stream stream, int SysNo)
{
DataTable dt = null;
var cols = OtherDicColsMapping.Select(p => p.Value).ToArray();
try
{
dt = ExcelUtil.ImportExcel(stream, cols);
}
catch (Exception ex)
{
return(new Resuldt <OtherOutOfStorageDetailed>
{
Message = string.Format("数据导入错误,请选择正确的excel文件"),
Status = false
});
}
if (dt == null)
{
//not all the cols mapped
return(new Resuldt <OtherOutOfStorageDetailed>
{
Message = string.Format("请选择正确的excel文件!"),
Status = false
});
}
if (dt.Rows.Count == 0)
{
return(new Resuldt <OtherOutOfStorageDetailed>
{
Message = "导入的数据为空!",
Status = false
});
}
Resuldt <OtherOutOfStorageDetailed> run = new Resuldt <OtherOutOfStorageDetailed>();
List <OtherOutOfStorageDetailed> listModel = new List <OtherOutOfStorageDetailed>();
int fail = 0; //失败记录数
int success = 0; //成功记录数
string failstr = ""; //失败条数记录
for (int i = 0; i < dt.Rows.Count; i++)
{
OtherOutOfStorageDetailed model = new OtherOutOfStorageDetailed();
var product = PdProductBo.Instance.GetProductErpCode(dt.Rows[i]["商品代码"].ToString(), dt.Rows[i]["商品条形码"].ToString());
var ware = Hyt.BLL.Web.WhWarehouseBo.Instance.GetModelErpCode(dt.Rows[i][7].ToString());
if (product == null || ware == null)
{
fail++;
failstr += (i + 2) + "、";
dt.Rows.Remove(dt.Rows[i]);
}
else
{
#region 判断商品是否已入库,若未入库将商品插入至商品库存表
var Stock = IWhInventoryDao.Instance.GetuditPdProductStockTo(dt.Rows[i]["商品代码"].ToString().Trim(), dt.Rows[i][7].ToString().Trim());
if (Stock == null)
{
// WhInventoryBo
PdProductStock stockModel = new PdProductStock()
{
WarehouseSysNo = ware.SysNo,
PdProductSysNo = product.SysNo,
StockQuantity = 0,
LockStockQuantity = 0,
CreatedDate = DateTime.Now,
Barcode = product.Barcode,
CostPrice = 0,
CustomsNo = "",
InStockTime = "",
Remark = "库存盘点自动入库"
};
PdProductStockBo.Instance.SaveProductStock(stockModel, 0);
}
#endregion
//重新获取商品库存信息
Stock = IWhInventoryDao.Instance.GetuditPdProductStockTo(dt.Rows[i]["商品代码"].ToString().Trim(), dt.Rows[i][7].ToString().Trim());
success++;
model.ProductCode = product.ErpCode;
model.BarCode = product.Barcode;
model.ProductName = product.EasName;
model.Count = dt.Rows[i]["实收数量"] != DBNull.Value ? Convert.ToDecimal(dt.Rows[i]["实收数量"]) : Convert.ToDecimal(0.00);
model.UnitPrice = dt.Rows[i]["单价"] != DBNull.Value ? Convert.ToDecimal(dt.Rows[i]["单价"]) : Convert.ToDecimal(0.00);
model.Price = (model.Count * model.UnitPrice);
model.Remarks = dt.Rows[i]["备注"].ToString();
model.ProductSysNo = product.SysNo;
model.CollectWarehouseName = ware.BackWarehouseName;
model.CollectWarehouseCode = ware.ErpCode;
model.CollectWarehouseSysNo = ware.SysNo;
listModel.Add(model);
}
}
if (success > 0 && dt.Rows.Count > 0)
{
run.Data = dt;
}
if (success > 0 && listModel != null)
{
run.listModel = listModel;
}
run.Message = "导入成功" + success + "件商品,失败" + fail + "件商品;";
if (fail > 0)
{
run.Message += "失败原因为:产品编码或仓库erp编码有误,不存在此件商品或仓库。失败条数为第" + failstr.Trim('、') + "条。";
}
run.Status = true;
return(run);
}
public Resuldt <WhInventoryProductDetailOutput> ImportExcelTo(System.IO.Stream stream, int SysNo)
{
DataTable dt = null;
var cols = DicColsMappingTo.Select(p => p.Value).ToArray();
#region 基础验证
try
{
dt = ExcelUtil.ImportExcel(stream, cols);
}
catch (Exception ex)
{
return(new Resuldt <WhInventoryProductDetailOutput>
{
Message = string.Format("数据导入错误,请选择正确的excel文件"),
Status = false
});
}
if (dt == null)
{
//not all the cols mapped
return(new Resuldt <WhInventoryProductDetailOutput>
{
Message = string.Format("请选择正确的excel文件!"),
Status = false
});
}
if (dt.Rows.Count == 0)
{
return(new Resuldt <WhInventoryProductDetailOutput>
{
Message = "导入的数据为空!",
Status = false
});
}
#endregion
Resuldt <WhInventoryProductDetailOutput> run = new Resuldt <WhInventoryProductDetailOutput>();
List <WhInventoryProductDetailOutput> listModel = new List <WhInventoryProductDetailOutput>();
int fail = 0; //失败记录数
int success = 0; //成功记录数
string failstr = ""; //失败条数记录
for (int i = 0; i < dt.Rows.Count; i++)
{
success++;
WhInventoryProductDetailOutput model = new WhInventoryProductDetailOutput();
model.WarehouseNameDate = dt.Rows[i]["仓库名称"].ToString();
model.PrCode = dt.Rows[i]["商品编号"].ToString();
model.PrName = dt.Rows[i]["商品名称"].ToString();
model.ZhangCunQuantity = Convert.ToDecimal(dt.Rows[i]["账存数量"]);
model.InventoryQuantity = Convert.ToDecimal(dt.Rows[i]["盘点数量"]);
model.Remarks = dt.Rows[i]["备注"].ToString();
listModel.Add(model);
}
if (success > 0 && dt.Rows.Count > 0)
{
run.Data = null;
}
if (success > 0 && listModel != null)
{
run.listModel = listModel;
}
run.Message = "导入成功" + success + "件商品,失败" + fail + "件商品;";
if (fail > 0)
{
run.Message += "失败原因为:产品编码或仓库erp编码有误,不存在此件商品。失败条数为第" + failstr.Trim('、') + "条。";
}
run.Status = true;
return(run);
}
public Resuldt <uditPdProductStock> ImportExcel(System.IO.Stream stream, int SysNo)
{
DataTable dt = null;
var cols = DicColsMapping.Select(p => p.Value).ToArray();
try
{
dt = ExcelUtil.ImportExcel(stream, cols);
}
catch (Exception ex)
{
return(new Resuldt <uditPdProductStock>
{
Message = string.Format("数据导入错误,请选择正确的excel文件"),
Status = false
});
}
if (dt == null)
{
//not all the cols mapped
return(new Resuldt <uditPdProductStock>
{
Message = string.Format("请选择正确的excel文件!"),
Status = false
});
}
if (dt.Rows.Count == 0)
{
return(new Resuldt <uditPdProductStock>
{
Message = "导入的数据为空!",
Status = false
});
}
Resuldt <uditPdProductStock> run = new Resuldt <uditPdProductStock>();
List <uditPdProductStock> listModel = new List <uditPdProductStock>();
int fail = 0; //失败记录数
int success = 0; //成功记录数
string failstr = ""; //失败条数记录
for (int i = 0; i < dt.Rows.Count; i++)
{
uditPdProductStock model = IWhInventoryDao.Instance.GetuditPdProductStockTo(dt.Rows[i]["商品代码"].ToString().Trim(), dt.Rows[i]["仓库Erp编号"].ToString().Trim());
if (model == null)
{
model = new uditPdProductStock();
var product = PdProductBo.Instance.GetProductErpCode(dt.Rows[i]["商品代码"].ToString().Trim(), null);
var ware = Hyt.BLL.Web.WhWarehouseBo.Instance.GetModelErpCode(dt.Rows[i]["仓库Erp编号"].ToString().Trim());
if (product == null || ware == null)
{
fail++;
failstr += (i + 2) + "、";
dt.Rows.Remove(dt.Rows[i]);
}
else
{
#region 将商品插入至商品库存表
// WhInventoryBo
PdProductStock stockModel = new PdProductStock()
{
WarehouseSysNo = ware.SysNo,
PdProductSysNo = product.SysNo,
StockQuantity = 0,
LockStockQuantity = 0,
CreatedDate = DateTime.Now,
Barcode = product.Barcode,
CostPrice = 0,
CustomsNo = "",
InStockTime = "",
Remark = "库存盘点自动入库"
};
var r = PdProductStockBo.Instance.SaveProductStock(stockModel, 0);
#endregion
if (r > 0)
{
model = IWhInventoryDao.Instance.GetuditPdProductStockTo(dt.Rows[i]["商品代码"].ToString().Trim(), dt.Rows[i]["仓库Erp编号"].ToString().Trim());
success++;
model.BeiZhu = dt.Rows[i]["备注"] == null ? "" : dt.Rows[i]["备注"].ToString().Trim();
model.ShiQuantity = dt.Rows[i]["实存数"] == null ? 0 : Convert.ToDecimal(dt.Rows[i]["实存数"].ToString().Trim());
model.WhCostPrice = dt.Rows[i]["单价"] == null ? 0 : Convert.ToDecimal(dt.Rows[i]["单价"].ToString().Trim());
listModel.Add(model);
}
else
{
fail++;
failstr += (i + 2) + "、";
dt.Rows.Remove(dt.Rows[i]);
}
}
}
else
{
success++;
model.BeiZhu = dt.Rows[i]["备注"] == null ? "" : dt.Rows[i]["备注"].ToString().Trim();
model.ShiQuantity = dt.Rows[i]["实存数"] == null ? 0 : Convert.ToDecimal(dt.Rows[i]["实存数"].ToString().Trim());
model.WhCostPrice = dt.Rows[i]["单价"] == null ? 0 : Convert.ToDecimal(dt.Rows[i]["单价"].ToString().Trim());
listModel.Add(model);
}
}
if (success > 0 && dt.Rows.Count > 0)
{
run.Data = dt;
}
if (success > 0 && listModel != null)
{
run.listModel = listModel;
}
run.Message = "导入成功" + success + "件商品,失败" + fail + "件商品;";
if (fail > 0)
{
run.Message += "失败原因为:产品编码或仓库erp编码有误,不存在此件商品。失败条数为第" + failstr.Trim('、') + "条。";
}
run.Status = true;
return(run);
}
public Resuldt ImportExcel(System.IO.Stream stream, int SysNo)
{
DataTable dt = null;
var cols = DicColsMapping.Select(p => p.Value).ToArray();
try
{
dt = ExcelUtil.ImportExcel(stream, cols);
}
catch (Exception ex)
{
return(new Resuldt
{
Message = string.Format("数据导入错误,请选择正确的excel文件"),
Status = false
});
}
if (dt == null)
{
//not all the cols mapped
return(new Resuldt
{
Message = string.Format("请选择正确的excel文件!"),
Status = false
});
}
if (dt.Rows.Count == 0)
{
return(new Resuldt
{
Message = "导入的数据为空!",
Status = false
});
}
Resuldt run = new Resuldt();
List <CBSimplePdProduct> listModel = new List <CBSimplePdProduct>();
int fail = 0; //失败记录数
int success = 0; //成功记录数
string failstr = ""; //失败条数记录
for (int i = 0; i < dt.Rows.Count; i++)
{
CBSimplePdProduct model = PdProductBo.Instance.GetProductErpCode(dt.Rows[i]["产品编码"].ToString(), dt.Rows[i]["条形码"].ToString());
if (model == null)
{
fail++;
failstr += (i + 2) + "、";
dt.Rows.Remove(dt.Rows[i]);
}
else
{
success++;
listModel.Add(model);
}
}
if (success > 0 && dt.Rows.Count > 0)
{
run.Data = dt;
}
if (success > 0 && listModel != null)
{
run.listModel = listModel;
}
run.Message = "导入成功" + success + "件商品,失败" + fail + "件商品;";
if (fail > 0)
{
run.Message += "失败原因为:产品编码有误,不存在此件商品。失败条数为第" + failstr.Trim('、') + "条。";
}
run.Status = true;
return(run);
}
