/// <summary>
/// 从甲管物资系统输出的excel表格中导入甲管物料信息
/// </summary>
/// <returns></returns>
public virtual ActionResult ImportJGEquips()
{
HttpPostedFileBase sourcefile = Request.Files[0];
if (sourcefile == null) return Content("没有选择甲管物资文件");
var hssfworkbook = new HSSFWorkbook(sourcefile.InputStream);
var sheet = hssfworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
if (rows.MoveNext() && rows.MoveNext())
rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
var cell = row.GetCell(2);
if (cell == null) continue;
var erpcode = cell.StringCellValue.Trim();// erp 账号
cell = row.GetCell(11);
if (cell == null) continue;
var companyname = cell.StringCellValue.Trim(); // 供货商名称
var eqname = row.GetCell(5).StringCellValue.Trim();// 物资名称
var quantity = row.GetCell(7).StringCellValue.Trim(); // 数量
cell = row.GetCell(8);
DateTime? createdon = null;
if (cell != null)
{
cell.SetCellType(CellType.STRING);
createdon = DateTime.Parse(cell.StringCellValue); // 需求日期
}
var unit = row.GetCell(6).StringCellValue.Trim(); //单位
var price = Convert.ToDouble(row.GetCell(12).StringCellValue.Trim()); //单价
var orderamount = Convert.ToDouble(quantity) *price; //订单金额
var equipmentOrder = new EquipmentOrder()
{
ErpCode = erpcode,
CreatedOn = createdon,
CreatedBy = CurrentUser.UserName,
// OwnerId = 1,// 默认为闵升物资公司
Quantity = Convert.ToDecimal(quantity),
OrderAmount = Convert.ToDecimal(orderamount),
EquipName = eqname,
Unit = unit,
CompanyId = GetEqCompanyId(companyname)
};
equipmentOrder.PlanCompanyId = equipmentOrder.CompanyId;
Repository.Add(equipmentOrder);
}
Repository.Save();
return Content(Path.GetFileNameWithoutExtension(sourcefile.FileName) + "包含的工程设备供应记录已经导入系统");
}
/// <summary>
/// 从Erp系统中按照10位工程编号导入工程项目的物资供应情况
/// </summary>
/// <param name="erpaccount">erp编码</param>
/// <returns></returns>
public virtual ActionResult ImportProjectEquips()
{
HttpPostedFileBase sourcefile = Request.Files[0];
HSSFWorkbook hssfworkbook = new HSSFWorkbook(sourcefile.InputStream);
var sheet = hssfworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
if (rows.MoveNext()) rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
var cell = row.GetCell(1);
if (cell ==null) continue;
cell.SetCellType(CellType.STRING);
var erpcode = cell.StringCellValue.Trim();
cell = row.GetCell(32);
if (cell == null) continue;
var companyname = cell.StringCellValue.Trim(); //实际供货厂商
var eqname = row.GetCell(10).StringCellValue.Trim(); //设备型号名称
var quantity = row.GetCell(14).NumericCellValue; //数量
var unit = row.GetCell(15).StringCellValue.Trim(); //单位
var plancompany = row.GetCell(20).StringCellValue.Trim(); //计划分配厂商
// var owner = row.GetCell(21).StringCellValue.Trim(); //采购组织
var orderamount = row.GetCell(24).NumericCellValue; //订单总额
cell = row.GetCell(26);
var isrecieved = (cell == null) ? string.Empty : cell.StringCellValue; //是否完全收货
var recievedquantity = row.GetCell(27).NumericCellValue; //收货数量
var balanceamount =recievedquantity/quantity*orderamount; //结算金额
DateTime? createdOn = null;
cell = row.GetCell(28); // PO生成日期
if (cell != null && !string.IsNullOrEmpty(cell.StringCellValue))
{
string[] datesstr = cell.StringCellValue.Trim().Split(new char[] { '.' });
createdOn = new DateTime(Convert.ToInt32(datesstr[0]), Convert.ToInt32(datesstr[1]), Convert.ToInt32(datesstr[2]));
}
// add new eqorder record
var equipmentOrder = new EquipmentOrder()
{
ErpCode = erpcode,
CreatedOn = createdOn,
CreatedBy = CurrentUser.UserName,
// OwnerId = GetOwnerId(owner),
Quantity = (decimal?)quantity,
OrderAmount = (decimal?)orderamount,
BalanceAmount = (decimal?)balanceamount,
IsRecievedComplete = isrecieved.Equals("是"),
RecievedQuntity = (decimal?)recievedquantity,
EquipName = eqname,
Unit = unit,
CompanyId = GetEqCompanyId(companyname),
PlanCompanyId = GetEqCompanyId(plancompany)
};
Repository.Add(equipmentOrder);
}
Repository.Save();
return Content(Path.GetFileNameWithoutExtension(sourcefile.FileName)+"包含的工程设备供应记录已经导入系统");
}
public virtual ActionResult CreateOrder(int prjid, EquipmentOrder order)
{
if (ModelState.IsValid)
{
var neworder = new EquipmentOrder();
TryUpdateModel(neworder);
neworder.ErpCode = Repository.SingleOrDefault<Project>(x => x.ProjectId == prjid).ErpCode;
Repository.Add(neworder);
Repository.Save();
return null;
}
return Content("出错了");
}
