public ResultModel updateHBLConsole(int hidx, int midx)
{
ResultModel result = new ResultModel();
try
{
MBL mblModel = HyundaiContext.MBL.Where(c => c.midx == midx).FirstOrDefault();
if (mblModel.status == 31)
{
mblModel.status = 32;
HyundaiContext.MBL.ApplyCurrentValues(mblModel);
}
if (mblModel.status == 32)
{
HBL entity = HyundaiContext.HBL.Where(c => c.hidx == hidx).FirstOrDefault();
entity.midx = midx;
entity.Status = 22;
HyundaiContext.HBL.ApplyCurrentValues(entity);
HyundaiContext.SaveChanges();
}
else
{
result.ResultCode = -2;
}
}
catch (Exception ex)
{
result.ResultCode = -1;
result.ResultMessage = ex.Message;
throw ex;
}
return(result);
}
public ResultModel updateHBLConsoleList(int midx, int[] hidx)
{
ResultModel result = new ResultModel();
try
{
var hblList = HyundaiContext.HBL.Where(c => c.midx == midx);
foreach (var item in hblList)
{
item.midx = null;
HyundaiContext.HBL.ApplyCurrentValues(item);
}
if (hidx != null)
{
foreach (var item in hidx)
{
HBL entity = HyundaiContext.HBL.Where(c => c.hidx == item).FirstOrDefault();
entity.midx = midx;
HyundaiContext.HBL.ApplyCurrentValues(entity);
}
}
HyundaiContext.SaveChanges();
}
catch (Exception ex)
{
result.ResultCode = -1;
result.ResultMessage = ex.Message;
throw ex;
}
return(result);
}
public ResultModel updateHBLConsole(string hblNo, int midx)
{
ResultModel result = new ResultModel();
try
{
//Load BL models
MBL mblModel = HyundaiContext.MBL.Where(c => c.midx == midx).FirstOrDefault();
HBL entity = HyundaiContext.HBL.Where(c => c.HblNo == hblNo).FirstOrDefault();
//Check existing console
if (entity == null)
{
result.ResultCode = -2;
result.ResultMessage = "HBL not found";
return(result);
}
else if (entity.Status == 22)
{
result.ResultCode = -3;
result.ResultMessage = "This HBL has already been processed";
return(result);
}
if (mblModel.status == 31)
{
mblModel.status = 32;
HyundaiContext.MBL.ApplyCurrentValues(mblModel);
}
if (mblModel.status == 32)
{
entity.midx = midx;
entity.Status = 22;
HyundaiContext.HBL.ApplyCurrentValues(entity);
HyundaiContext.SaveChanges();
}
else
{
result.ResultCode = -2;
}
}
catch (Exception ex)
{
result.ResultCode = -1;
result.ResultMessage = ex.Message;
throw ex;
}
return(result);
}
public ActionResult updateHBL(HBLModel model)
{
ResultModel result = new ResultModel();
try
{
Mapper.CreateMap <HBLModel, HBL>();
HBL entity = Mapper.Map <HBLModel, HBL>(model);
entity.CreateId = userInfo.uidx;
result = blService.updateHBL(entity);
}
catch (Exception ex)
{
result.ResultCode = -1;
result.ResultMessage = ex.Message;
throw ex;
}
Response.Redirect("/BL/House");
return(View());
}
public ResultModel updateHBL(HBL model)
{
ResultModel result = new ResultModel();
try
{
if (model.hidx == 0)
{
result.ResultCode = -1;
result.ResultMessage = "Invalid parameter";
return(result);
}
else
{
HBL entity = HyundaiContext.HBL.Where(c => c.hidx == model.hidx).FirstOrDefault();
HBL newEntity = Mapper.Map(model, entity);
//ViewModel 에서 전달받지 않는 값들을 유지하기 위한 코드
newEntity.cidx = entity.cidx;
newEntity.CreateDate = entity.CreateDate;
newEntity.CreateId = entity.CreateId;
newEntity.midx = entity.midx;
HyundaiContext.HBL.ApplyCurrentValues(newEntity);
}
HyundaiContext.SaveChanges();
}
catch (Exception ex)
{
result.ResultCode = -1;
result.ResultMessage = ex.Message;
throw ex;
}
return(result);
}
public ResultModel setHBL(HBLAPIModel model)
{
ResultModel result = new ResultModel();
try
{
var hbl = new HBL();
Mapper.CreateMap <HBLAPIModel, HBL>();
Mapper.Map(model, hbl);
var converter = new KoreanRomanizer();
using (var scope = new TransactionScope(TransactionScopeOption.Required,
TimeSpan.FromMinutes(5)))
{
int hidx = 0;
int hblCnt = 0;
string hblNo = "";
var transportCodeModel = HyundaiContext.CODE.Where(c => c.GROUPCD == 1002).ToList();
if (!string.IsNullOrEmpty(hbl.HblNo))
{
if (hbl.HblNo == null || hbl.HblNo.Length != 13)
{
result.ResultCode = -1;
result.ResultMessage = "Invalid HBL No. : " + hblNo;
scope.Dispose();
return(result);
}
if (HyundaiContext.HBL.Where(h => h.HblNo == hbl.HblNo).Count() > 0)
{
result.ResultCode = -1;
result.ResultMessage = "HBL already exists : " + hblNo;
scope.Dispose();
return(result);
}
if (string.IsNullOrEmpty(hbl.ConsigneeEngName))
{
hbl.ConsigneeEngName = converter.romanize(hbl.ConsigneeName);
}
if (string.IsNullOrEmpty(hbl.EngZipaddress))
{
string zipcode = hbl.ConsigneeZipCode.Replace("-", "");
var zipModel = HyundaiContext.ZIPCODE.Where(c => c.Zipcode1 == zipcode).FirstOrDefault();
if (zipModel != null)
{
hbl.EngZipaddress = zipModel.Address_Eng;
}
else
{
hbl.EngZipaddress = converter.romanize(hbl.ConsigneeZipAddress);
}
}
if (string.IsNullOrEmpty(hbl.EngAddress))
{
hbl.EngAddress = converter.romanize(hbl.ConsigneeAddress);
}
hbl.WeightType = 5;
hbl.CreateDate = DateTime.Now;
hbl.Status = 20;
HyundaiContext.AddToHBL(hbl);
HyundaiContext.SaveChanges();
hidx = hbl.hidx;
//priductitem
foreach (var item in model.itemList)
{
var productItem = new PRODUCTITEM();
productItem.hidx = hidx;
productItem.itemAmt = item.itemAmt;
productItem.itemBrand = item.itemBrand;
productItem.itemName = item.itemName;
productItem.itemQty = item.itemQty;
productItem.ItemTotalAmt = item.ItemTotalAmt;
productItem.url = item.url;
HyundaiContext.AddToPRODUCTITEM(productItem);
}
HyundaiContext.SaveChanges();
}
else
{
result.ResultCode = -1;
result.ResultMessage = "Invalid HBL No.";
}
scope.Complete();
}
}
catch (Exception ex)
{
result.ResultCode = -1;
result.ResultMessage = ex.Message;
throw ex;
}
return(result);
}
public ResultModel uploadHouseBL(USER userInfo, string filePath)
{
ResultModel result = new ResultModel();
try
{
string e = Path.GetExtension(filePath).ToLower();
if (e != ".xls" && e != ".xlsx")
{
result.ResultCode = -1;
result.ResultMessage = "엑셀 파일이 아닙니다.";
}
else
{
var excelFIle = new ExcelQueryFactory(filePath);
var worksheetNames = excelFIle.GetWorksheetNames();
string sheetName = "Sheet1";
foreach (var sheet in worksheetNames)
{
if (sheet.Substring(sheet.Length - 1) != "_")
{
var columnCnt = excelFIle.Worksheet(sheet).FirstOrDefault().Count();
if (columnCnt == 29 || columnCnt == 31 || columnCnt == 30 || columnCnt == 33 || columnCnt == 123)
{
sheetName = sheet;
}
}
}
var hblData = excelFIle.Worksheet(sheetName).ToList();
var converter = new KoreanRomanizer();
using (var scope = new TransactionScope(TransactionScopeOption.Required,
TimeSpan.FromMinutes(5)))
{
int hidx = 0;
int hblCnt = 0;
string hblNo = "";
try
{
var transportCodeModel = HyundaiContext.CODE.Where(c => c.GROUPCD == 1002).ToList();
foreach (var item in hblData)
{
var model = new HBL();
if (item.Count() == 31)
{
if (!string.IsNullOrEmpty(item[2]))
{
hblCnt++;
model.HblNo = item[2].ToString().Trim();
hblNo = item[2].ToString().Trim();
if (hblNo == null)
{
result.ResultCode = -1;
result.ResultMessage = "Invalid HBL No. : " + hblNo;
scope.Dispose();
return(result);
}
if (HyundaiContext.HBL.Where(h => h.HblNo == hblNo).Count() > 0)
{
result.ResultCode = -1;
result.ResultMessage = "HBL already exists : " + hblNo;
scope.Dispose();
return(result);
}
if (item[4].Value != null && item[4].Value != "")
{
var dateStr = DateTime.Parse(item[4].Value.ToString().Split(' ')[0]);
model.OnBoardDate = dateStr;
}
model.ShipperCd = item[5];
model.ShipperName = item[6];
model.ShipperAddress = item[7];
model.ShipperCity = item[8];
model.ShipperState = item[9];
model.ShipperZipCode = item[10];
model.ShipperPhone = item[11];
model.ConsigneeName = item[12];
model.ConsigneeEngName = converter.romanize(model.ConsigneeName);
model.ConsigneeZipCode = item[13];
model.ConsigneeZipAddress = item[14];
model.ConsigneeAddress = item[15];
model.ConsigneePhone = item[16];
model.ConsigneeCellPhone = item[17];
model.Memo = item[18];
model.juminNo = item[19];
string TransPortType = string.IsNullOrEmpty(item[21]) ? "B" : item[21];
model.TransportType = transportCodeModel.Where(c => c.GROUPCD == 1002 && c.CD == TransPortType).FirstOrDefault().cdidx;
model.WeightType = 5;
model.Weight = Convert.ToDecimal(item[24]);
model.Carton = Convert.ToInt16(item[25]);
model.CreateDate = DateTime.Now;
model.CreateId = userInfo.uidx;
model.cidx = userInfo.cidx;
model.Status = 20;
string zipcode = model.ConsigneeZipCode.Replace("-", "");
var zipModel = HyundaiContext.ZIPCODE.Where(c => c.Zipcode1 == zipcode).FirstOrDefault();
if (zipModel != null)
{
model.EngZipaddress = zipModel.Address_Eng;
}
else
{
model.EngZipaddress = converter.romanize(model.ConsigneeZipAddress);
}
model.EngAddress = converter.romanize(model.ConsigneeAddress);
HyundaiContext.AddToHBL(model);
HyundaiContext.SaveChanges();
hidx = model.hidx;
//
var productItem = getXLSProductItem(item, hidx);
HyundaiContext.AddToPRODUCTITEM(productItem);
}
else if (!string.IsNullOrEmpty(item[26]))
{
var productItem = getXLSProductItem(item, hidx);
HyundaiContext.AddToPRODUCTITEM(productItem);
}
}
else if (item.Count() == 30)
{
if (!string.IsNullOrEmpty(item[1]) && item[1] != "운송장번호")
{
hblCnt++;
model.HblNo = item[1];
hblNo = item[1];
if (hblNo == null)
{
result.ResultCode = -1;
result.ResultMessage = "Invalid HBL No. : " + hblNo;
scope.Dispose();
return(result);
}
if (HyundaiContext.HBL.Where(h => h.HblNo == hblNo).Count() > 0)
{
result.ResultCode = -1;
result.ResultMessage = "HBL already exists : " + hblNo;
scope.Dispose();
return(result);
}
model.ShipperName = item[3];
model.ShipperAddress = item[5] + " " + item[6];
model.ShipperZipCode = item[4];
model.ShipperPhone = item[7];
model.ConsigneeName = item[9];
model.ConsigneeEngName = converter.romanize(model.ConsigneeName);
model.ConsigneeZipCode = item[11];
model.ConsigneeZipAddress = item[12];
model.ConsigneeAddress = item[13];
model.ConsigneePhone = item[16];
model.ConsigneeCellPhone = item[17];
model.OnBoardDate = DateTime.Now;
model.Memo = item[23];
model.juminNo = item[8];
string TransPortType = "B";
model.TransportType = transportCodeModel.Where(c => c.GROUPCD == 1002 && c.CD == TransPortType).FirstOrDefault().cdidx;
model.WeightType = 5;
model.Weight = Convert.ToDecimal(item[19]);
model.Carton = Convert.ToInt16(item[18]);
model.CreateDate = DateTime.Now;
model.CreateId = userInfo.uidx;
model.cidx = userInfo.cidx;
model.Status = 20;
string zipcode = model.ConsigneeZipCode.Replace("-", "");
var zipModel = HyundaiContext.ZIPCODE.Where(c => c.Zipcode1 == zipcode).FirstOrDefault();
if (zipModel != null)
{
model.EngZipaddress = zipModel.Address_Eng;
}
else
{
model.EngZipaddress = converter.romanize(model.ConsigneeZipAddress);
}
model.EngAddress = converter.romanize(model.ConsigneeAddress);
HyundaiContext.AddToHBL(model);
HyundaiContext.SaveChanges();
hidx = model.hidx;
//
var productItem = getXLSProductItem2(item, hidx);
HyundaiContext.AddToPRODUCTITEM(productItem);
}
else if (!string.IsNullOrEmpty(item[24]) && item[24] != "ITEM_NM")
{
var productItem = getXLSProductItem2(item, hidx);
HyundaiContext.AddToPRODUCTITEM(productItem);
}
}
else if (item.Count() == 33) // 신 우정 양식
{
if (!string.IsNullOrEmpty(item[1]) && item[1] != "운송장번호")
{
hblCnt++;
model.HblNo = item[1];
hblNo = item[1];
if (hblNo == null)
{
result.ResultCode = -1;
result.ResultMessage = "Invalid HBL No. : " + hblNo;
scope.Dispose();
return(result);
}
if (HyundaiContext.HBL.Where(h => h.HblNo == hblNo).Count() > 0)
{
result.ResultCode = -1;
result.ResultMessage = "HBL already exists : " + hblNo;
scope.Dispose();
return(result);
}
model.ShipperName = item[3];
model.ShipperAddress = item[5];
model.ShipperZipAddress = item[6];
model.ShipperZipCode = item[4];
model.ShipperPhone = item[7];
model.ConsigneeName = item[9];
model.ConsigneeEngName = item[10];
if (string.IsNullOrEmpty(model.ConsigneeEngName) || model.ConsigneeEngName == ".")
{
model.ConsigneeEngName = converter.romanize(model.ConsigneeName);
}
model.ConsigneeZipCode = item[11];
model.ConsigneeZipAddress = item[12];
model.ConsigneeAddress = item[13];
model.EngZipaddress = item[14];
model.EngAddress = item[15];
if (string.IsNullOrEmpty(model.EngZipaddress) || model.EngZipaddress == ".")
{
string zipcode = model.ConsigneeZipCode.Replace("-", "");
var zipModel = HyundaiContext.ZIPCODE.Where(c => c.Zipcode1 == zipcode).FirstOrDefault();
if (zipModel != null)
{
model.EngZipaddress = zipModel.Address_Eng;
}
else
{
model.EngZipaddress = converter.romanize(model.ConsigneeZipAddress);
}
}
if (string.IsNullOrEmpty(model.EngAddress) || model.EngAddress == ".")
{
model.EngAddress = converter.romanize(model.ConsigneeAddress);
}
model.ConsigneePhone = item[16];
model.ConsigneeCellPhone = item[17];
model.OnBoardDate = DateTime.Now;
model.Memo = item[24];
model.juminNo = item[8];
string TransPortType = "B";
model.TransportType = transportCodeModel.Where(c => c.GROUPCD == 1002 && c.CD == TransPortType).FirstOrDefault().cdidx;
model.WeightType = 5;
model.Weight = Convert.ToDecimal(item[19]);
model.Carton = Convert.ToInt16(item[18]);
model.CreateDate = DateTime.Now;
model.CreateId = userInfo.uidx;
model.cidx = userInfo.cidx;
model.Status = 20;
HyundaiContext.AddToHBL(model);
HyundaiContext.SaveChanges();
hidx = model.hidx;
//
var productItem = getXLSProductItem3(item, hidx);
HyundaiContext.AddToPRODUCTITEM(productItem);
}
else if (!string.IsNullOrEmpty(item[29]) && item[29] != "ITEM_NM")
{
var productItem = getXLSProductItem3(item, hidx);
HyundaiContext.AddToPRODUCTITEM(productItem);
}
}
else if (item.Count() == 29) // SHIN
{
if (!string.IsNullOrEmpty(item[0]) && (item[0] != "SEQ_NO" && item[0] != "순번(NO)"))
{
hblCnt++;
model.HblNo = item[1];
hblNo = item[1];
if (hblNo == null)
{
result.ResultCode = -1;
result.ResultMessage = "Invalid HBL No. : " + hblNo;
scope.Dispose();
return(result);
}
if (HyundaiContext.HBL.Where(h => h.HblNo == hblNo).Count() > 0)
{
result.ResultCode = -1;
result.ResultMessage = "HBL already exists : " + hblNo;
scope.Dispose();
return(result);
}
model.ShipperName = item[3];
model.ShipperAddress = item[5];
model.ShipperZipAddress = item[6];
model.ShipperZipCode = item[4];
model.ShipperPhone = item[7];
model.ConsigneeName = item[9];
model.ConsigneeEngName = item[10];
if (string.IsNullOrEmpty(model.ConsigneeEngName) || model.ConsigneeEngName == ".")
{
model.ConsigneeEngName = converter.romanize(model.ConsigneeName);
}
model.ConsigneeZipCode = item[11];
model.ConsigneeZipAddress = item[12];
model.ConsigneeAddress = item[13];
model.EngZipaddress = item[14];
model.EngAddress = item[15];
if (string.IsNullOrEmpty(model.EngZipaddress) || model.EngZipaddress == ".")
{
model.EngZipaddress = converter.romanize(model.ConsigneeZipAddress);
}
if (string.IsNullOrEmpty(model.EngAddress) || model.EngAddress == ".")
{
model.EngAddress = converter.romanize(model.ConsigneeAddress);
}
model.ConsigneePhone = item[16];
model.ConsigneeCellPhone = item[17];
model.OnBoardDate = DateTime.Now;
model.Memo = item[23];
model.juminNo = item[8];
string TransPortType = "B";
model.TransportType = transportCodeModel.Where(c => c.GROUPCD == 1002 && c.CD == TransPortType).FirstOrDefault().cdidx;
model.WeightType = 5;
model.Weight = Convert.ToDecimal(item[19]);
model.Carton = Convert.ToInt16(item[18]);
model.CreateDate = DateTime.Now;
model.CreateId = userInfo.uidx;
model.cidx = userInfo.cidx;
model.Status = 20;
HyundaiContext.AddToHBL(model);
HyundaiContext.SaveChanges();
hidx = model.hidx;
//
var productItem = getXLSProductItem2(item, hidx);
HyundaiContext.AddToPRODUCTITEM(productItem);
}
else if (!string.IsNullOrEmpty(item[25]) && item[25] != "ITEM_NM")
{
var productItem = getXLSProductItem2(item, hidx);
HyundaiContext.AddToPRODUCTITEM(productItem);
}
}
else if (item.Count() == 123) // EZ
{
if (!string.IsNullOrEmpty(item[1]) && item[1] != "주문번호")
{
hblCnt++;
model.HblNo = item[2];
hblNo = item[2];
if (hblNo == null)
{
result.ResultCode = -1;
result.ResultMessage = "Invalid HBL No. : " + hblNo;
scope.Dispose();
return(result);
}
if (HyundaiContext.HBL.Where(h => h.HblNo == hblNo).Count() > 0)
{
result.ResultCode = -1;
result.ResultMessage = "HBL already exists : " + hblNo;
scope.Dispose();
return(result);
}
model.ShipperName = item[6];
model.ShipperZipAddress = item[7] ?? "";
model.ShipperZipCode = model.ShipperZipAddress.Length > 5 ? model.ShipperZipAddress.Substring(model.ShipperZipAddress.Length - 5) : "";
model.ShipperZipAddress = model.ShipperZipAddress.Replace(model.ShipperZipCode, "");
model.ConsigneeName = item[10];
model.ConsigneeEngName = converter.romanize(model.ConsigneeName);
model.juminNo = item[11];
model.ConsigneePhone = item[12];
model.ConsigneeCellPhone = item[13];
model.ConsigneeZipCode = item[14];
model.ConsigneeZipAddress = item[15];
model.ConsigneeAddress = item[16];
model.EngZipaddress = converter.romanize(model.ConsigneeZipAddress);
model.EngAddress = converter.romanize(model.ConsigneeAddress);
model.OnBoardDate = DateTime.Now;
model.Memo = item[22];
string TransPortType = "B";
model.TransportType = transportCodeModel.Where(c => c.GROUPCD == 1002 && c.CD == TransPortType).FirstOrDefault().cdidx;
model.WeightType = 5;
model.Weight = Convert.ToDecimal(item[18]);
model.Carton = Convert.ToInt16(item[20]);
model.CreateDate = DateTime.Now;
model.CreateId = userInfo.uidx;
model.cidx = userInfo.cidx;
model.Status = 20;
HyundaiContext.AddToHBL(model);
HyundaiContext.SaveChanges();
hidx = model.hidx;
//
for (int i = 23; i < 123; i += 4)
{
if (string.IsNullOrEmpty(item[i]))
{
break;
}
PRODUCTITEM itemModel = new PRODUCTITEM();
itemModel.itemName = item[i];
itemModel.itemAmt = Convert.ToDecimal(item[i + 1]);
itemModel.itemQty = Convert.ToInt16(item[i + 2]);
itemModel.ItemTotalAmt = Convert.ToDecimal(item[i + 3]);
itemModel.hidx = hidx;
HyundaiContext.AddToPRODUCTITEM(itemModel);
}
}
}
HyundaiContext.SaveChanges();
}
}
catch (Exception ex)
{
File.Delete(filePath);
throw new Exception("Error on HBL NO : " + hblNo, ex);
}
scope.Complete();
result.ResultMessage = hblCnt.ToString();
}
}
File.Delete(filePath);
}
catch (Exception ex)
{
result.ResultCode = -1;
result.ResultMessage = ex.Message;
throw ex;
}
return(result);
}
