public ActionResult Upload(HttpPostedFileBase upload) { if (!ModelState.IsValid) { return View("Error"); } if (upload == null && upload.ContentLength == 0) { return View("Error"); } //var file = new FilePath //{ // FileName = Guid.NewGuid().ToString() + System.IO.Path.GetExtension(upload.FileName), // FileType = FileType.Excel //}; //string path = System.IO.Path.Combine( // Server.MapPath("~/Content/Uploads"), file.FileName); //// file is uploaded //upload.SaveAs(path); //Remove all items of current container var wb = new XLWorkbook(upload.InputStream); try { var containerWS = wb.Worksheet("Container"); var containerRow = containerWS.FirstRowUsed(); var container = new Container(); while (!containerRow.IsEmpty()) { var prop = containerRow.Cell(1).GetString(); var value = containerRow.Cell(2).Value; Type type = container.GetType(); PropertyInfo containerProp = type.GetProperty(prop); containerProp.SetValue(container, value, null); containerRow = containerRow.RowBelow(); } if (ModelState.IsValid) { //If container with container ID already exists, update it otherwise insert new container.ContainerID = CurrentContainerID; db.Entry(container).State = EntityState.Modified; db.SaveChanges(); } } catch (Exception e) { return View("InvalidExcel", (object)("Something wrong with container information<br/>" + e.Message)); } DeleteAllContainerItems(); var ws = wb.Worksheet("Items"); var dataRange = ws.RangeUsed(); // Treat the range as a table (to be able to use the column names) var dataTable = dataRange.AsTable(); var dataObj = dataTable.DataRange.Rows() .Select(packingList => new { CartonNumber = packingList.Field("CartonNumber").GetString(), Marka = packingList.Field("Marka").GetString(), PartyName = packingList.Field("PartyName").GetString(), JobNumber = packingList.Field("JobNumber").GetString(), BillOnBoardingDate = packingList.Field("BillOnBoardingDate").GetString(), BillDeliveryDate = packingList.Field("BillDeliveryDate").GetString(), BillNumber = packingList.Field("BillNumber").GetString(), BillTTDAPNumber = packingList.Field("BillTTDAPNumber").GetString(), BillTTDAPDate = packingList.Field("BillTTDAPDate").GetString(), LotSize = packingList.Field("LotSize").GetString(), ProductCustomsName = packingList.Field("ProductCustomsName").GetString(), ProductBuyerName = packingList.Field("ProductBuyerName").GetString(), ProductUnit = packingList.Field("ProductUnit").GetString(), Quantity = packingList.Field("Quantity").GetString(), Cartons = packingList.Field("Cartons").GetString(), BuyerCurrency = packingList.Field("BuyerCurrency").GetString(), BuyerUnitPrice = packingList.Field("BuyerUnitPrice").GetString(), CustomsQuantity = packingList.Field("CustomsQuantity").GetString(), CustomsProductUnit = packingList.Field("CustomsProductUnit").GetString(), CustomsCurrency = packingList.Field("CustomsCurrency").GetString(), CustomsUnitPrice = packingList.Field("CustomsUnitPrice").GetString() }) .ToList(); // Get the list of company names foreach (var item in dataObj) { var containerItem = new TmpContainerItem(); containerItem.ContainerID = CurrentContainerID; containerItem.CartonNumber = item.CartonNumber; containerItem.Marka = item.Marka; containerItem.ProductBuyerName = item.ProductBuyerName; containerItem.ProductUnit = item.ProductUnit.TrimEnd('.'); containerItem.Quantity = Convert.ToDecimal(item.Quantity); containerItem.PartyName = item.PartyName; containerItem.JobNumber = item.JobNumber; containerItem.LotSize = item.LotSize; containerItem.BillOnBoardingDate = item.BillOnBoardingDate; containerItem.BillDeliveryDate = item.BillDeliveryDate; containerItem.BillNumber = item.BillNumber; containerItem.BillTTDAPDate = item.BillTTDAPDate; containerItem.BillTTDAPNumber = item.BillTTDAPNumber; var cartonNumber = containerItem.CartonNumber; try { containerItem.Cartons = CartonsFromCartonNumber(cartonNumber); } catch (FormatException e) { return View("InvalidExcel", (object)e.Message); } if (!containerItem.CartonNumber.Contains("CTN") && db.TmpContainerItems .Any(c => c.ContainerID == CurrentContainerID && c.CartonNumber == item.CartonNumber && c.Marka == item.Marka)) { containerItem.Cartons = 0; } containerItem.BuyerCurrency = item.BuyerCurrency; if(String.IsNullOrEmpty(item.BuyerUnitPrice)){ containerItem.BuyerUnitPrice = db.TmpContainerItems .OrderByDescending(i => i.ContainerID) .Where(i => i.PartyName == item.PartyName && i.ProductBuyerName == item.ProductBuyerName && i.ProductUnit == item.ProductUnit) .Select(i => i.BuyerUnitPrice) .FirstOrDefault(); } else { containerItem.BuyerUnitPrice = Convert.ToDecimal(item.BuyerUnitPrice); } if (String.IsNullOrEmpty(item.ProductCustomsName)) { containerItem.ProductCustomsName = db.TmpContainerItems .OrderByDescending(i => i.ContainerID) .Where(i => i.ProductBuyerName == item.ProductBuyerName) .Select(i => i.ProductCustomsName) .FirstOrDefault(); } else { containerItem.ProductCustomsName = item.ProductCustomsName; } if (String.IsNullOrEmpty(item.CustomsProductUnit)) { containerItem.CustomsProductUnit = db.TmpContainerItems .OrderByDescending(i => i.ContainerID) .Where(i => i.ProductCustomsName == item.ProductCustomsName) .Select(i => i.CustomsProductUnit) .DefaultIfEmpty("") .FirstOrDefault(); } else { containerItem.CustomsProductUnit = item.CustomsProductUnit; } //Populate CustomsQuantity automatically if (String.IsNullOrEmpty(item.CustomsQuantity)) { if (String.IsNullOrEmpty(item.Quantity)) { containerItem.CustomsQuantity = db.TmpContainerItems .OrderByDescending(i => i.ContainerID) .Where(i => i.ProductBuyerName == item.ProductBuyerName) .Select(i => i.CustomsQuantity) .FirstOrDefault(); } else { if ((containerItem.ProductUnit.Trim().Equals("PCS") || containerItem.ProductUnit.Trim().Equals("PRS")) && containerItem.CustomsProductUnit.Trim().Equals("DOZ")) { containerItem.CustomsQuantity = containerItem.Quantity / 12; } else { containerItem.CustomsQuantity = containerItem.Quantity; } } } else { containerItem.CustomsQuantity = Convert.ToDecimal(item.CustomsQuantity); } if (String.IsNullOrEmpty(item.CustomsCurrency)) { containerItem.CustomsCurrency = db.TmpContainerItems .OrderByDescending(i => i.ContainerID) .Where(i => i.ProductBuyerName == item.ProductBuyerName) .Select(i => i.CustomsCurrency) .FirstOrDefault(); } else { containerItem.CustomsCurrency = item.CustomsCurrency; } if (String.IsNullOrEmpty(item.CustomsUnitPrice)) { containerItem.CustomsUnitPrice = db.TmpContainerItems .OrderByDescending(i => i.ContainerID) .Where(i => i.ProductCustomsName == item.ProductCustomsName) .Select(i => i.CustomsUnitPrice) .FirstOrDefault(); } else { containerItem.CustomsUnitPrice = Convert.ToDecimal(item.CustomsUnitPrice); } db.TmpContainerItems.Add(containerItem); db.SaveChanges(); } var MarkaPartyInfo = db.TmpContainerItems .Where(c => c.ContainerID == CurrentContainerID) .GroupBy(c => new { c.Marka }) .Select(group => new { Marka = group.Key.Marka, PartyName = group.FirstOrDefault(a => a.PartyName != "").PartyName, JobNumber = group.FirstOrDefault(a => a.JobNumber != "").JobNumber, LotSize = group.FirstOrDefault(a => a.LotSize != "").LotSize, BillOnBoardingDate = group.FirstOrDefault(a => a.BillOnBoardingDate != "").BillOnBoardingDate, BillDeliveryDate = group.FirstOrDefault(a => a.BillDeliveryDate != "").BillDeliveryDate, BillTTDAPDate = group.FirstOrDefault(a => a.BillTTDAPDate != "").BillTTDAPDate, BillTTDAPNumber = group.FirstOrDefault(a => a.BillTTDAPNumber != "").BillTTDAPNumber } ).ToDictionary(a => a.Marka); //db.TmpContainerItems // .Where(c => c.ContainerID == CurrentContainerID) // .ToList() // .ForEach(a => { // a.PartyName = MarkaPartyInfo[a.Marka].PartyName; // a.JobNumber = MarkaPartyInfo[a.Marka].JobNumber; // a.LotSize = MarkaPartyInfo[a.Marka].LotSize; // a.BillOnBoardingDate = MarkaPartyInfo[a.Marka].BillOnBoardingDate; // a.BillDeliveryDate = MarkaPartyInfo[a.Marka].BillDeliveryDate; // a.BillTTDAPDate = MarkaPartyInfo[a.Marka].BillTTDAPDate; // a.BillTTDAPNumber = MarkaPartyInfo[a.Marka].BillTTDAPNumber; // }); //db.SaveChanges(); //var CustomsInfo = db.TmpContainerItems // .Where(c => c.ContainerID == CurrentContainerID) // .GroupBy(c => new // { // c.ProductBuyerName // }) // .Select(group => // new { // ProductBuyerName = group.Key.ProductBuyerName, // ProductCustomsName = group.FirstOrDefault(a => a.ProductCustomsName != "").ProductCustomsName, // CustomsProductUnit = group.FirstOrDefault(a => a.CustomsProductUnit != "").CustomsProductUnit, // CustomsCurrency = group.FirstOrDefault(a => a.CustomsCurrency != "").CustomsCurrency, // CustomsUnitPrice = (decimal?)group.FirstOrDefault(a => a.CustomsUnitPrice != 0).CustomsUnitPrice ?? 0 // }).ToDictionary(a => a.ProductBuyerName); //db.TmpContainerItems // .Where(c => c.ContainerID == CurrentContainerID) // .ToList() // .ForEach(a => // { // a.ProductCustomsName = CustomsInfo[a.ProductBuyerName].ProductCustomsName; // a.CustomsProductUnit = CustomsInfo[a.ProductBuyerName].CustomsProductUnit; // a.CustomsCurrency = CustomsInfo[a.ProductBuyerName].CustomsCurrency; // a.CustomsUnitPrice = CustomsInfo[a.ProductBuyerName].CustomsUnitPrice; // }); db.SaveChanges(); return new RedirectResult(Url.Action("Index", "TmpDashboard")); }
public void AddContainerInfo(IXLWorksheet ws, Container container) { ws.Cell("A1").SetValue(container.ExporterName).Style.Font.FontSize = 20; ws.Range("A1:E1").Merge(); ws.Cell("A2").SetValue(container.ExporterAddress).Style.Alignment.WrapText = true; ws.Range("A2:B3").Merge(); ws.Cell("A5").SetValue("Shipped Per"); ws.Cell("B5").SetValue(container.ShippedPer); ws.Range("B5:C5").Merge(); ws.Cell("A6").SetValue("On/About"); ws.Cell("B6").SetValue(container.OnAbout); ws.Range("B6:C6").Merge(); ws.Cell("A7").SetValue("From"); ws.Cell("B7").SetValue(container.From); // ws.Row(7) // .Style // .Alignment.SetVertical(XLAlignmentVerticalValues.Top) // .Alignment.SetWrapText(true); ws.Range("B7:C7").Merge(); ws.Row(7).Height = 70; ws.Cell("A8").SetValue("Airway Bill No. \nor B/L No."); ws.Cell("B8").SetValue(container.AirwayBillNumber); ws.Range("B8:C8").Merge(); ws.Row(8).Height = 30; // ws.Row(8).Style.Alignment.SetWrapText(true); ws.Cell("A9").SetValue("Letter of\nCredit No."); ws.Cell("B9").SetValue(container.LetterOfCreditNumber); ws.Range("B9:C9").Merge(); ws.Row(9).Height = 30; ws.Cell("A10").SetValue("Drawn Under"); ws.Cell("B10").SetValue(container.DrawnUnder) .Style .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); // .Alignment.SetWrapText(true); ws.Range("B10:C10").Merge(); ws.Row(10).Height = 70; // ws.Row(10).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top); ws.Range("A1:A10").Style.Font.Bold = true; ws.Range("B5:C10").Style.Border.BottomBorder = XLBorderStyleValues.Thin; // ws.Range("B5:C10").Style.Alignment.WrapText = true; ws.Rows("5:10").Style.Alignment.SetWrapText(true) .Alignment.SetVertical(XLAlignmentVerticalValues.Top); //Importer ws.Cell("E5").SetValue(container.ImporterName + "\n" + container.ImporterAddress + "\n" + "(TAX CERTIFICATE NO. " + container.ImporterTaxCertificateNumber + ")") .Style .Alignment.SetVertical(XLAlignmentVerticalValues.Top) .Alignment.SetWrapText(); ws.Range("E5:H10").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Medium; //Container Number + Container Date ws.Cell("F2").SetValue("INVOICE NO:"); ws.Cell("G2").SetValue(container.CustomsInvoiceNumber); ws.Cell("F3").SetValue("DATE:"); ws.Cell("G3").SetValue(container.Date); ws.Range("F2:F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range("G2:H2").Merge(); ws.Range("G3:H3").Merge(); ws.Range("G2:G3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Range("F2:H3").Style.Font.Bold = true; }