コード例 #1
0
        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"));
        }
コード例 #2
0
        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;
        }