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);
}
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 ActionResult DownloadPDF(int midx)
{
KoreanRomanizer converter = new KoreanRomanizer();
var param = new ParameterModel();
param.midx = midx;
param.searchKey = "midx";
param.searchText = midx.ToString();
var mblModel = blService.getMBL(param);
var hblModel = blService.getHBLList(param);
var mblTable = new DataTable();
var hblTable = new DataTable();
mblTable.Columns.Add("FltNo");
mblTable.Columns.Add("mblNo");
mblTable.Columns.Add("FltDate");
mblTable.Columns.Add("ArrivalDate");
mblTable.Columns.Add("DptrCd");
mblTable.Columns.Add("DestCd");
mblTable.Columns.Add("ShipperAddress1");
mblTable.Columns.Add("ShipperName");
mblTable.Columns.Add("CneeName");
mblTable.Columns.Add("CneePhone");
var dataRow = mblTable.NewRow();
dataRow["FltNo"] = mblModel.FltNo;
dataRow["mblNo"] = mblModel.mblNo;
dataRow["FltDate"] = mblModel.FltDate;
dataRow["ArrivalDate"] = mblModel.ArrivalDate;
dataRow["DptrCd"] = mblModel.DptrCd;
dataRow["DestCd"] = mblModel.DestCd;
dataRow["ShipperAddress1"] = mblModel.CUSTOMER.CustFullAddress;
dataRow["ShipperName"] = mblModel.CUSTOMER.CustEngName;
dataRow["CneeName"] = mblModel.CUSTOMER1.CustEngName;
dataRow["CneePhone"] = mblModel.CUSTOMER1.WPhone;
mblTable.Rows.Add(dataRow);
//dataRow
hblTable.Columns.Add("HblNo");
hblTable.Columns.Add("ShipperName");
hblTable.Columns.Add("ShipperAddress");
hblTable.Columns.Add("ConsigneeName");
hblTable.Columns.Add("ConsigneeAddress");
hblTable.Columns.Add("Carton", typeof(int));
hblTable.Columns.Add("Value", typeof(decimal));
hblTable.Columns.Add("Weight", typeof(decimal));
hblTable.Columns.Add("Memo");
foreach (var item in hblModel)
{
var conEngName = item.ConsigneeEngName;
if (string.IsNullOrEmpty(conEngName))
{
conEngName = converter.romanize(item.ConsigneeName);
}
var newRow = hblTable.NewRow();
newRow["HblNo"] = item.HblNo;
newRow["ShipperName"] = item.ShipperName;
newRow["ShipperAddress"] = item.ShipperAddress + item.ShipperZipAddress + "\r" + ((item.ShipperZipCode ?? "").Length == 4?"0":"") + item.ShipperZipCode;
newRow["ConsigneeName"] = conEngName;
newRow["ConsigneeAddress"] = item.EngAddress + item.EngZipaddress + "\r" + item.ConsigneeZipCode;
newRow["Carton"] = item.Carton;
newRow["Value"] = item.PRODUCTITEM.Sum(i => i.ItemTotalAmt);
newRow["Weight"] = item.Weight;
newRow["Memo"] = string.Join("\r", item.PRODUCTITEM.Select(i => i.itemName).Take(3)) + (item.PRODUCTITEM.Count() > 3 ? "\r..." : "");
hblTable.Rows.Add(newRow);
}
ReportDocument rd = new ReportDocument();
rd.Load(Path.Combine(Server.MapPath("~/Reports"), "Manifest.rpt"));
//rd.SetDataSource(getHblList(param));
rd.Database.Tables[0].SetDataSource(hblTable);
rd.Database.Tables[1].SetDataSource(mblTable);
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
try
{
Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/pdf", "Manifest.pdf"));
}
catch (Exception ex)
{
throw ex;
}
}