public virtual async Task <string> Create(GarmentPackingListViewModel viewModel) { viewModel.Status = GarmentPackingListStatusEnum.CREATED.ToString(); GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); foreach (var item in garmentPackingListModel.Items) { foreach (var detail in item.Details) { detail.SetNetNetWeight(detail.NetNetWeight == 0 ? 0.9 * detail.NetWeight : detail.NetNetWeight, _identityProvider.Username, UserAgent); } } var totalNnw = garmentPackingListModel.Items .SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, totalNetNetWeight = d.CartonQuantity * d.NetNetWeight })) .GroupBy(g => new { g.Carton1, g.Carton2 }, (key, value) => value.First().totalNetNetWeight).Sum(); garmentPackingListModel.SetNetNetWeight(totalNnw, _identityProvider.Username, UserAgent); garmentPackingListModel.StatusActivities.Add(new GarmentPackingListStatusActivityModel(_identityProvider.Username, UserAgent, garmentPackingListModel.Status)); await _packingListRepository.InsertAsync(garmentPackingListModel); garmentPackingListModel.SetShippingMarkImagePath(await UploadImage(viewModel.ShippingMarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.ShippingMarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); garmentPackingListModel.SetSideMarkImagePath(await UploadImage(viewModel.SideMarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.SideMarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); garmentPackingListModel.SetRemarkImagePath(await UploadImage(viewModel.RemarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.RemarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); await _packingListRepository.SaveChanges(); return(garmentPackingListModel.InvoiceNo); }
public virtual async Task <int> Update(int id, GarmentPackingListViewModel viewModel) { viewModel.ShippingMarkImagePath = await UploadImage(viewModel.ShippingMarkImageFile, viewModel.Id, viewModel.ShippingMarkImagePath, viewModel.CreatedUtc); viewModel.SideMarkImagePath = await UploadImage(viewModel.SideMarkImageFile, viewModel.Id, viewModel.SideMarkImagePath, viewModel.CreatedUtc); viewModel.RemarkImagePath = await UploadImage(viewModel.RemarkImageFile, viewModel.Id, viewModel.RemarkImagePath, viewModel.CreatedUtc); GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); foreach (var item in garmentPackingListModel.Items) { foreach (var detail in item.Details) { detail.SetNetNetWeight(detail.NetNetWeight == 0 ? 0.9 * detail.NetWeight : detail.NetNetWeight, _identityProvider.Username, UserAgent); } } var totalNnw = garmentPackingListModel.Items .SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, totalNetNetWeight = d.CartonQuantity * d.NetNetWeight })) .GroupBy(g => new { g.Carton1, g.Carton2 }, (key, value) => value.First().totalNetNetWeight).Sum(); garmentPackingListModel.SetNetNetWeight(totalNnw, _identityProvider.Username, UserAgent); return(await _packingListRepository.UpdateAsync(id, garmentPackingListModel)); }
public virtual async Task <int> Unpost(int id, GarmentPackingListViewModel viewModel) { GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); garmentPackingListModel.SetIsSampleDelivered(false, _identityProvider.Username, UserAgent); return(await _packingListRepository.UpdateAsync(id, garmentPackingListModel)); }
public virtual async Task <int> Update(int id, GarmentPackingListViewModel viewModel) { viewModel.ShippingMarkImagePath = await UploadImage(viewModel.ShippingMarkImageFile, viewModel.Id, viewModel.ShippingMarkImagePath, viewModel.CreatedUtc); viewModel.SideMarkImagePath = await UploadImage(viewModel.SideMarkImageFile, viewModel.Id, viewModel.SideMarkImagePath, viewModel.CreatedUtc); viewModel.RemarkImagePath = await UploadImage(viewModel.RemarkImageFile, viewModel.Id, viewModel.RemarkImagePath, viewModel.CreatedUtc); GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); return(await _packingListRepository.UpdateAsync(id, garmentPackingListModel)); }
private string GenerateInvoiceNo(GarmentPackingListViewModel viewModel) { var year = DateTime.Now.ToString("yy"); var prefix = $"{(viewModel.InvoiceType ?? "").Trim().ToUpper()}/{year}"; var lastInvoiceNo = _packingListRepository.ReadAll().Where(w => w.InvoiceNo.StartsWith(prefix)) .OrderByDescending(o => o.InvoiceNo) .Select(s => int.Parse(s.InvoiceNo.Replace(prefix, ""))) .FirstOrDefault(); var invoiceNo = $"{prefix}{(lastInvoiceNo + 1).ToString("D4")}"; return(invoiceNo); }
public async Task SetApproveShipping(int id, GarmentPackingListViewModel viewModel) { GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); await _packingListRepository.UpdateAsync(id, garmentPackingListModel); var status = GarmentPackingListStatusEnum.APPROVED_SHIPPING; var oldModel = _packingListRepository.Query.Single(m => m.Id == id); if (oldModel.Status != status) { oldModel.SetStatus(status, _identityProvider.Username, UserAgent); oldModel.StatusActivities.Add(new GarmentPackingListStatusActivityModel(_identityProvider.Username, UserAgent, status)); await _packingListRepository.SaveChanges(); } }
public override async Task <string> Create(GarmentPackingListViewModel viewModel) { viewModel.Status = GarmentPackingListStatusEnum.DRAFT.ToString(); GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); garmentPackingListModel.StatusActivities.Add(new GarmentPackingListStatusActivityModel(_identityProvider.Username, UserAgent, garmentPackingListModel.Status)); await _packingListRepository.InsertAsync(garmentPackingListModel); garmentPackingListModel.SetShippingMarkImagePath(await UploadImage(viewModel.ShippingMarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.ShippingMarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); garmentPackingListModel.SetSideMarkImagePath(await UploadImage(viewModel.SideMarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.SideMarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); garmentPackingListModel.SetRemarkImagePath(await UploadImage(viewModel.RemarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.RemarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); await _packingListRepository.SaveChanges(); return(garmentPackingListModel.InvoiceNo); }
protected GarmentPackingListModel MapToModel(GarmentPackingListViewModel viewModel) { var items = (viewModel.Items ?? new List <GarmentPackingListItemViewModel>()).Select(i => { var details = (i.Details ?? new List <GarmentPackingListDetailViewModel>()).Select(d => { var sizes = (d.Sizes ?? new List <GarmentPackingListDetailSizeViewModel>()).Select(s => { s.Size = s.Size ?? new SizeViewModel(); return(new GarmentPackingListDetailSizeModel(s.Size.Id, s.Size.Size, s.Quantity) { Id = s.Id }); }).ToList(); return(new GarmentPackingListDetailModel(d.Carton1, d.Carton2, d.Style, d.Colour, d.CartonQuantity, d.QuantityPCS, d.TotalQuantity, d.Length, d.Width, d.Height, d.GrossWeight, d.NetWeight, d.NetNetWeight, sizes, d.Index) { Id = d.Id }); }).ToList(); i.BuyerBrand = i.BuyerBrand ?? new Buyer(); i.Uom = i.Uom ?? new UnitOfMeasurement(); i.Unit = i.Unit ?? new Unit(); i.Comodity = i.Comodity ?? new Comodity(); return(new GarmentPackingListItemModel(i.RONo, i.SCNo, i.BuyerBrand.Id, i.BuyerBrand.Name, i.Comodity.Id, i.Comodity.Code, i.Comodity.Name, i.ComodityDescription, i.Quantity, i.Uom.Id.GetValueOrDefault(), i.Uom.Unit, i.PriceRO, i.Price, i.PriceFOB, i.PriceCMT, i.Amount, i.Valas, i.Unit.Id, i.Unit.Code, i.Article, i.OrderNo, i.Description, i.DescriptionMd, details) { Id = i.Id }); }).ToList(); var measurements = (viewModel.Measurements ?? new List <GarmentPackingListMeasurementViewModel>()).Select(m => new GarmentPackingListMeasurementModel(m.Length, m.Width, m.Height, m.CartonsQuantity) { Id = m.Id }).ToList(); viewModel.Section = viewModel.Section ?? new Section(); viewModel.BuyerAgent = viewModel.BuyerAgent ?? new Buyer(); viewModel.InvoiceNo = viewModel.InvoiceNo ?? GenerateInvoiceNo(viewModel); viewModel.ShippingStaff = viewModel.ShippingStaff ?? new ShippingStaff(); Enum.TryParse(viewModel.Status, true, out GarmentPackingListStatusEnum status); GarmentPackingListModel garmentPackingListModel = new GarmentPackingListModel(viewModel.InvoiceNo, viewModel.PackingListType, viewModel.InvoiceType, viewModel.Section.Id, viewModel.Section.Code, viewModel.Date.GetValueOrDefault(), viewModel.PaymentTerm, viewModel.LCNo, viewModel.LCDate.GetValueOrDefault(), viewModel.IssuedBy, viewModel.BuyerAgent.Id, viewModel.BuyerAgent.Code, viewModel.BuyerAgent.Name, viewModel.Destination, viewModel.FinalDestination, viewModel.ShipmentMode, viewModel.TruckingDate.GetValueOrDefault(), viewModel.TruckingEstimationDate.GetValueOrDefault(), viewModel.ExportEstimationDate.GetValueOrDefault(), viewModel.Omzet, viewModel.Accounting, viewModel.FabricCountryOrigin, viewModel.FabricComposition, viewModel.RemarkMd, items, viewModel.GrossWeight, viewModel.NettWeight, viewModel.NetNetWeight, viewModel.TotalCartons, measurements, viewModel.SayUnit, viewModel.ShippingMark, viewModel.SideMark, viewModel.Remark, viewModel.ShippingMarkImagePath, viewModel.SideMarkImagePath, viewModel.RemarkImagePath, viewModel.IsUsed, viewModel.IsPosted, viewModel.ShippingStaff.id, viewModel.ShippingStaff.name, status, viewModel.Description, viewModel.IsCostStructured, viewModel.OtherCommodity); return(garmentPackingListModel); }
public virtual async Task <int> Update(int id, GarmentPackingListViewModel viewModel) { viewModel.ShippingMarkImagePath = await UploadImage(viewModel.ShippingMarkImageFile, viewModel.Id, viewModel.ShippingMarkImagePath, viewModel.CreatedUtc); viewModel.SideMarkImagePath = await UploadImage(viewModel.SideMarkImageFile, viewModel.Id, viewModel.SideMarkImagePath, viewModel.CreatedUtc); viewModel.RemarkImagePath = await UploadImage(viewModel.RemarkImageFile, viewModel.Id, viewModel.RemarkImagePath, viewModel.CreatedUtc); GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); var invoice = _invoiceRepository.ReadAll(); GarmentShippingInvoiceModel shippingInvoice = (from a in invoice where a.InvoiceNo == garmentPackingListModel.InvoiceNo select a).FirstOrDefault(); if (shippingInvoice != null) { var invoiceItem = await _invoiceRepository.ReadByIdAsync(shippingInvoice.Id); GarmentShippingInvoiceModel shippingInvoiceItem = invoiceItem; if (shippingInvoiceItem != null) { shippingInvoiceItem.InvoiceDate = garmentPackingListModel.Date; await _invoiceRepository.UpdateAsync(shippingInvoiceItem.Id, shippingInvoiceItem); } } foreach (var item in garmentPackingListModel.Items) { foreach (var detail in item.Details) { detail.SetNetNetWeight(detail.NetNetWeight == 0 ? 0.9 * detail.NetWeight : detail.NetNetWeight, _identityProvider.Username, UserAgent); } } var totalNnw = garmentPackingListModel.Items .SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, totalNetNetWeight = d.CartonQuantity * d.NetNetWeight })) .GroupBy(g => new { g.Carton1, g.Carton2 }, (key, value) => value.First().totalNetNetWeight).Sum(); garmentPackingListModel.SetNetNetWeight(totalNnw, _identityProvider.Username, UserAgent); return(await _packingListRepository.UpdateAsync(id, garmentPackingListModel)); }
public override async Task <string> Create(GarmentPackingListViewModel viewModel) { viewModel.Status = GarmentPackingListStatusEnum.DRAFT.ToString(); GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); foreach (var item in garmentPackingListModel.Items) { foreach (var detail in item.Details) { detail.SetNetNetWeight(detail.NetNetWeight == 0 ? 0.9 * detail.NetWeight : detail.NetNetWeight, _identityProvider.Username, UserAgent); } } garmentPackingListModel.StatusActivities.Add(new GarmentPackingListStatusActivityModel(_identityProvider.Username, UserAgent, garmentPackingListModel.Status.ToString())); await _packingListRepository.InsertAsync(garmentPackingListModel); garmentPackingListModel.SetShippingMarkImagePath(await UploadImage(viewModel.ShippingMarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.ShippingMarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); garmentPackingListModel.SetSideMarkImagePath(await UploadImage(viewModel.SideMarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.SideMarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); garmentPackingListModel.SetRemarkImagePath(await UploadImage(viewModel.RemarkImageFile, garmentPackingListModel.Id, garmentPackingListModel.RemarkImagePath, garmentPackingListModel.CreatedUtc), _identityProvider.Username, UserAgent); await _packingListRepository.SaveChanges(); return(garmentPackingListModel.InvoiceNo); }
public GarmentPackingListDraftPdfByCartonMDTemplatePageEvent(IIdentityProvider identityProvider, GarmentPackingListViewModel viewModel) { this.identityProvider = identityProvider; this.viewModel = viewModel; }
public MemoryStream GeneratePdfTemplate(GarmentPackingListViewModel viewModel, int timeoffset) { int maxSizesCount = viewModel.Items == null || viewModel.Items.Count < 1 ? 0 : viewModel.Items.Max(i => i.Details == null || i.Details.Count < 1 ? 0 : i.Details.Max(d => d.Sizes == null || d.Sizes.Count < 1 ? 0 : d.Sizes.GroupBy(g => g.Size.Id).Count())); int SIZES_COUNT = maxSizesCount > 11 ? 20 : 11; Font header_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 14); Font normal_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Font body_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Font normal_font_underlined = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8, Font.UNDERLINE); Font bold_font = FontFactory.GetFont(BaseFont.HELVETICA_BOLD, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Document document = new Document(maxSizesCount > 11 ? PageSize.A4.Rotate() : PageSize.A4, 20, 20, 70, 30); MemoryStream stream = new MemoryStream(); PdfWriter writer = PdfWriter.GetInstance(document, stream); writer.PageEvent = new GarmentPackingListDraftPdfByCartonMDTemplatePageEvent(_identityProvider, viewModel); document.Open(); PdfContentByte cb = writer.DirectContent; PdfPCell cellBorderBottomRight = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER | Rectangle.RIGHT_BORDER | Rectangle.LEFT_BORDER, HorizontalAlignment = Element.ALIGN_CENTER }; PdfPCell cellBorderBottom = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, HorizontalAlignment = Element.ALIGN_CENTER }; var cartons = new List <GarmentPackingListDetailViewModel>(); double grandTotal = 0; var arraySubTotal = new Dictionary <String, double>(); List <string> cartonNumbers = new List <string>(); var newItems = new List <GarmentPackingListItemViewModel>(); var newItems2 = new List <GarmentPackingListItemViewModel>(); var newDetails = new List <GarmentPackingListDetailViewModel>(); foreach (var item in viewModel.Items.OrderBy(a => a.RONo)) { foreach (var detail in item.Details) { newDetails.Add(detail); } } newDetails = newDetails.OrderBy(a => a.Carton1).ToList(); foreach (var d in newDetails) { if (newItems.Count == 0) { var i = viewModel.Items.Single(a => a.Id == d.PackingListItemId); i.Details = new List <GarmentPackingListDetailViewModel>(); i.Details.Add(d); newItems.Add(i); } else { if (newItems.Last().Id == d.PackingListItemId) { newItems.Last().Details.Add(d); } else { var y = viewModel.Items.OrderBy(o => o.RONo).Select(a => new GarmentPackingListItemViewModel { Id = a.Id, RONo = a.RONo, Article = a.Article, BuyerAgent = a.BuyerAgent, ComodityDescription = a.ComodityDescription, OrderNo = a.OrderNo, AVG_GW = a.AVG_GW, AVG_NW = a.AVG_NW, Uom = a.Uom }) .Single(a => a.Id == d.PackingListItemId); y.Details = new List <GarmentPackingListDetailViewModel>(); y.Details.Add(d); newItems.Add(y); } } } foreach (var item in newItems) { if (newItems2.Count == 0) { newItems2.Add(item); } else { if (newItems2.Last().RONo == item.RONo && newItems2.Last().OrderNo == item.OrderNo) { foreach (var d in item.Details.OrderBy(a => a.Carton1)) { newItems2.Last().Details.Add(d); } } else { newItems2.Add(item); } } } foreach (var item in newItems2) { #region Item PdfPTable tableItem = new PdfPTable(6); tableItem.SetWidths(new float[] { 2f, 0.2f, 2.8f, 2f, 0.2f, 2.8f }); PdfPCell cellItemContent = new PdfPCell() { Border = Rectangle.NO_BORDER }; cellItemContent.Phrase = new Phrase("Shipping Method", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(viewModel.ShipmentMode, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("Fabric Composition", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(viewModel.FabricComposition, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("Tgl Trucking", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(viewModel.TruckingEstimationDate.GetValueOrDefault().ToOffset(new TimeSpan(timeoffset, 0, 0)).ToString("dd MMMM yyyy", new System.Globalization.CultureInfo("id-ID")), normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("Negara Asal Fabric", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(viewModel.FabricCountryOrigin, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("Negara Tujuan Akhir", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(viewModel.FinalDestination, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("Remark Merhandiser", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(viewModel.RemarkMd, normal_font); tableItem.AddCell(cellItemContent); // cellItemContent.Phrase = new Phrase("RO No", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(item.RONo, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("ARTICLE", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(item.Article, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("BUYER", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(viewModel.BuyerAgent.Name, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("", normal_font); cellItemContent.Phrase = new Phrase("DESCRIPTION OF GOODS", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(item.ComodityDescription, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("", normal_font); tableItem.AddCell(cellItemContent); tableItem.AddCell(cellItemContent); tableItem.AddCell(cellItemContent); new PdfPCell(tableItem); tableItem.ExtendLastRow = false; document.Add(tableItem); #endregion var sizes = new Dictionary <int, string>(); foreach (var detail in item.Details) { foreach (var size in detail.Sizes) { sizes[size.Size.Id] = size.Size.Size; } } PdfPTable tableDetail = new PdfPTable(SIZES_COUNT + 11); var width = new List <float> { 2f, 3.5f, 4f, 4f }; for (int i = 0; i < SIZES_COUNT; i++) { width.Add(1f); } width.AddRange(new List <float> { 1.5f, 1f, 1.5f, 2f, 1.5f, 1.5f, 1.5f }); tableDetail.SetWidths(width.ToArray()); PdfPCell cellDetailLine = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = 19, Padding = 0.5f, Phrase = new Phrase("") }; tableDetail.AddCell(cellDetailLine); tableDetail.AddCell(cellDetailLine); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("CARTON NO.", normal_font, 0.75f)); cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("COLOUR", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("STYLE", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("ORDER NO.", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("S I Z E", normal_font, 0.75f)); cellBorderBottomRight.Colspan = SIZES_COUNT; cellBorderBottomRight.Rowspan = 1; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("CTNS", normal_font, 0.75f)); cellBorderBottomRight.Colspan = 1; cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("@", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("QTY", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("SATUAN", normal_font, 0.75f)); cellBorderBottomRight.Colspan = 1; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("GW/\nCTN", normal_font, 0.75f)); cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("NW/\nCTN", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("NNW/\nCTN", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Rowspan = 1; for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(size.Key == 0 ? "" : size.Value, normal_font, 0.5f)); cellBorderBottomRight.Rowspan = 1; tableDetail.AddCell(cellBorderBottomRight); } var subCartons = new List <GarmentPackingListDetailViewModel>(); var subGrossWeight = new List <GarmentPackingListDetailViewModel>(); var subNetWeight = new List <GarmentPackingListDetailViewModel>(); var subNetNetWeight = new List <GarmentPackingListDetailViewModel>(); double subTotal = 0; var sizeSumQty = new Dictionary <int, double>(); foreach (var detail in item.Details) { var ctnsQty = detail.CartonQuantity; var grossWeight = detail.GrossWeight; var netWeight = detail.NetWeight; var netNetWeight = detail.NetNetWeight; if (cartonNumbers.Contains($"{detail.Index}-{detail.Carton1}- {detail.Carton2}")) { ctnsQty = 0; grossWeight = 0; netWeight = 0; netNetWeight = 0; } else { cartonNumbers.Add($"{detail.Index}-{detail.Carton1}- {detail.Carton2}"); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk($"{detail.Carton1}- {detail.Carton2}", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.Colour, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.Style, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(item.OrderNo, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = detail.Sizes.Where(w => w.Size.Id == size.Key).Sum(s => s.Quantity); } if (sizeSumQty.ContainsKey(size.Key)) { sizeSumQty[size.Key] += quantity * detail.CartonQuantity; } else { sizeSumQty.Add(size.Key, quantity * detail.CartonQuantity); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(quantity == 0 ? "" : quantity.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(ctnsQty.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.QuantityPCS.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); var totalQuantity = (detail.CartonQuantity * detail.QuantityPCS); subTotal += totalQuantity; cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(totalQuantity.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(item.Uom.Unit, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.GrossWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.NetWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.NetNetWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); if (cartons.FindIndex(c => c.Carton1 == detail.Carton1 && c.Carton2 == detail.Carton2 && c.Index == detail.Index) < 0) { cartons.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = ctnsQty }); } if (subCartons.FindIndex(c => c.Carton1 == detail.Carton1 && c.Carton2 == detail.Carton2 && c.Index == detail.Index) < 0) { subCartons.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = ctnsQty }); subGrossWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, GrossWeight = grossWeight }); subNetWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, NetWeight = netWeight }); subNetNetWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, NetNetWeight = netNetWeight }); } } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.5f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.5f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.5f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("SUMMARY", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = sizeSumQty.Where(w => w.Key == size.Key).Sum(a => a.Value); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(quantity == 0 ? "" : quantity.ToString(), normal_font, 0.5f)); tableDetail.AddCell(cellBorderBottomRight); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); grandTotal += subTotal; if (!arraySubTotal.ContainsKey(item.Uom.Unit)) { arraySubTotal.Add(item.Uom.Unit, subTotal); } else { arraySubTotal[item.Uom.Unit] += subTotal; } tableDetail.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = SIZES_COUNT + 6, Padding = 5, Phrase = new Phrase("SUB TOTAL ....................................................................................................................................................................... ", normal_font) }); cellBorderBottom.Phrase = new Phrase(subTotal.ToString() + " " + item.Uom.Unit, normal_font); cellBorderBottom.Colspan = 2; tableDetail.AddCell(cellBorderBottom); cellBorderBottom.Phrase = new Phrase("", normal_font); cellBorderBottom.Colspan = 3; tableDetail.AddCell(cellBorderBottom); cellBorderBottom.Colspan = 1; var subCtns = subCartons.Sum(c => c.CartonQuantity); var subGw = subGrossWeight.Sum(c => c.CartonQuantity * c.GrossWeight); var subNw = subNetWeight.Sum(c => c.CartonQuantity * c.NetWeight); var subNnw = subNetNetWeight.Sum(c => c.CartonQuantity * c.NetNetWeight); tableDetail.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = SIZES_COUNT + 11, Phrase = new Phrase($" - Sub Ctns = {subCtns} - Sub G.W. = {String.Format("{0:0.00}", subGw)} Kgs - Sub N.W. = {String.Format("{0:0.00}", subNw)} Kgs - Sub N.N.W. = {String.Format("{0:0.00}", subNnw)} Kgs", normal_font) }); new PdfPCell(tableDetail); tableDetail.ExtendLastRow = false; //tableDetail.KeepTogether = true; tableDetail.WidthPercentage = 95f; //tableDetail.HeaderRows = 3; document.Add(tableDetail); } #region GrandTotal PdfPTable tableGrandTotal = new PdfPTable(2); tableGrandTotal.SetWidths(new float[] { 18f + SIZES_COUNT * 1f, 5f }); PdfPCell cellHeaderLine = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = 2, Padding = 0.5f, Phrase = new Phrase("") }; tableGrandTotal.AddCell(cellHeaderLine); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Padding = 5, Phrase = new Phrase("GRAND TOTAL ...................................................................................................................................................................................", normal_font) }); var grandTotalResult = string.Join(" / ", arraySubTotal.Select(x => x.Value + " " + x.Key).ToArray()); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Padding = 5, HorizontalAlignment = Element.ALIGN_CENTER, Phrase = new Phrase(grandTotalResult, normal_font) }); tableGrandTotal.AddCell(cellHeaderLine); var totalCtns = cartons.Sum(c => c.CartonQuantity); var comodities = viewModel.Items.Select(s => s.Comodity.Name.ToUpper()).Distinct(); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.NO_BORDER, Colspan = 2, Padding = 5, Phrase = new Phrase($"{totalCtns} {viewModel.SayUnit} [ {NumberToTextEN.toWords(totalCtns).Trim().ToUpper()} {viewModel.SayUnit} OF {string.Join(" AND ", comodities)}]", normal_font) }); new PdfPCell(tableGrandTotal); tableGrandTotal.ExtendLastRow = false; tableGrandTotal.WidthPercentage = 95f; tableGrandTotal.SpacingAfter = 5f; document.Add(tableGrandTotal); #endregion #region Mark PdfPTable tableMark = new PdfPTable(2); tableMark.SetWidths(new float[] { 1f, 1f }); PdfPCell cellShippingMark = new PdfPCell() { Border = Rectangle.NO_BORDER }; Chunk chunkShippingMark = new Chunk("SHIPPING MARKS", normal_font); chunkShippingMark.SetUnderline(0.5f, -1); Phrase phraseShippingMark = new Phrase(); phraseShippingMark.Add(chunkShippingMark); phraseShippingMark.Add(new Chunk(" :", normal_font)); cellShippingMark.AddElement(phraseShippingMark); cellShippingMark.AddElement(new Paragraph(viewModel.ShippingMark, normal_font)); tableMark.AddCell(cellShippingMark); PdfPCell cellSideMark = new PdfPCell() { Border = Rectangle.NO_BORDER }; Chunk chunkSideMark = new Chunk("SIDE MARKS", normal_font); chunkSideMark.SetUnderline(0.5f, -1); Phrase phraseSideMark = new Phrase(); phraseSideMark.Add(chunkSideMark); phraseSideMark.Add(new Chunk(" :", normal_font)); cellSideMark.AddElement(phraseSideMark); cellSideMark.AddElement(new Paragraph(viewModel.SideMark, normal_font) { }); tableMark.AddCell(cellSideMark); byte[] shippingMarkImage; if (!String.IsNullOrEmpty(viewModel.ShippingMarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile))) { shippingMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile)); Image shipMarkImage = Image.GetInstance(imgb: shippingMarkImage); if (shipMarkImage.Width > 60) { float percentage = 0.0f; percentage = 100 / shipMarkImage.Width; shipMarkImage.ScalePercent(percentage * 100); PdfPCell shipMarkImageCell = new PdfPCell(shipMarkImage); shipMarkImageCell.Border = Rectangle.NO_BORDER; tableMark.AddCell(shipMarkImageCell); } } } byte[] sideMarkImage; if (!String.IsNullOrEmpty(viewModel.SideMarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile))) { sideMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile)); Image _sideMarkImage = Image.GetInstance(imgb: sideMarkImage); if (_sideMarkImage.Width > 60) { float percentage = 0.0f; percentage = 100 / _sideMarkImage.Width; _sideMarkImage.ScalePercent(percentage * 100); } PdfPCell _sideMarkImageCell = new PdfPCell(_sideMarkImage); _sideMarkImageCell.Border = Rectangle.NO_BORDER; tableMark.AddCell(_sideMarkImageCell); } } new PdfPCell(tableMark); tableMark.ExtendLastRow = false; tableMark.SpacingAfter = 5f; document.Add(tableMark); #endregion #region Measurement PdfPTable tableMeasurement = new PdfPTable(3); tableMeasurement.SetWidths(new float[] { 2f, 0.2f, 12f }); PdfPCell cellMeasurement = new PdfPCell() { Border = Rectangle.NO_BORDER }; cellMeasurement.Phrase = new Phrase("GROSS WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(viewModel.GrossWeight + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("NET WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(viewModel.NettWeight + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("NET NET WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(viewModel.NetNetWeight + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("MEASUREMENT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); PdfPTable tableMeasurementDetail = new PdfPTable(5); tableMeasurementDetail.SetWidths(new float[] { 1f, 1f, 1f, 1.5f, 2f }); PdfPCell cellMeasurementDetail = new PdfPCell() { Border = Rectangle.NO_BORDER, HorizontalAlignment = Element.ALIGN_RIGHT }; decimal totalCbm = 0; foreach (var measurement in viewModel.Measurements) { cellMeasurementDetail.Phrase = new Phrase(measurement.Length + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(measurement.Width + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(measurement.Height + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(measurement.CartonsQuantity + " CTNS = ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); var cbm = (decimal)measurement.Length * (decimal)measurement.Width * (decimal)measurement.Height * (decimal)measurement.CartonsQuantity / 1000000; totalCbm += cbm; cellMeasurementDetail.Phrase = new Phrase(string.Format("{0:N2} CBM", cbm), normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); } cellMeasurementDetail.Border = Rectangle.TOP_BORDER; cellMeasurementDetail.Phrase = new Phrase("", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase("TOTAL", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(viewModel.Measurements.Sum(m => m.CartonsQuantity) + " CTNS .", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(string.Format("{0:N2} CBM", totalCbm), normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); new PdfPCell(tableMeasurementDetail); tableMeasurementDetail.ExtendLastRow = false; var paddingRight = SIZES_COUNT > 11 ? 400 : 200; tableMeasurement.AddCell(new PdfPCell(tableMeasurementDetail) { Border = Rectangle.NO_BORDER, PaddingRight = paddingRight }); tableMeasurement.AddCell(new PdfPCell { Border = Rectangle.NO_BORDER, Colspan = 3, Phrase = new Phrase("REMARK :", normal_font_underlined) }); tableMeasurement.AddCell(new PdfPCell { Border = Rectangle.NO_BORDER, Colspan = 3, Phrase = new Phrase(viewModel.Remark, normal_font) }); byte[] remarkImage; if (!String.IsNullOrEmpty(viewModel.RemarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.RemarkImageFile))) { remarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.RemarkImageFile)); Image images = Image.GetInstance(imgb: remarkImage); if (images.Width > 60) { float percentage = 0.0f; percentage = 100 / images.Width; images.ScalePercent(percentage * 100); } PdfPCell imageCell = new PdfPCell(images); imageCell.Border = Rectangle.NO_BORDER; imageCell.Colspan = 3; tableMeasurement.AddCell(imageCell); } } new PdfPCell(tableMeasurement); tableMeasurement.ExtendLastRow = false; tableMeasurement.SpacingAfter = 5f; document.Add(tableMeasurement); //document.Add(images); #endregion document.Close(); byte[] byteInfo = stream.ToArray(); stream.Write(byteInfo, 0, byteInfo.Length); stream.Position = 0; return(stream); }
public override Task <int> Update(int id, GarmentPackingListViewModel viewModel) { GarmentPackingListModel model = MapToModel(viewModel); var modelToUpdate = _packingListRepository.Query .Include(i => i.Items) .ThenInclude(i => i.Details) .ThenInclude(i => i.Sizes) .Include(i => i.Measurements) .FirstOrDefault(s => s.Id == id); modelToUpdate.SetSectionId(model.SectionId, _identityProvider.Username, UserAgent); modelToUpdate.SetSectionCode(model.SectionCode, _identityProvider.Username, UserAgent); foreach (var itemToUpdate in modelToUpdate.Items.Where(i => i.CreatedBy == _identityProvider.Username)) { var item = model.Items.FirstOrDefault(i => i.Id == itemToUpdate.Id); if (item != null) { itemToUpdate.SetRONo(item.RONo, _identityProvider.Username, UserAgent); itemToUpdate.SetSCNo(item.SCNo, _identityProvider.Username, UserAgent); itemToUpdate.SetBuyerBrandId(item.BuyerBrandId, _identityProvider.Username, UserAgent); itemToUpdate.SetBuyerBrandName(item.BuyerBrandName, _identityProvider.Username, UserAgent); itemToUpdate.SetComodityId(item.ComodityId, _identityProvider.Username, UserAgent); itemToUpdate.SetComodityCode(item.ComodityCode, _identityProvider.Username, UserAgent); itemToUpdate.SetComodityName(item.ComodityName, _identityProvider.Username, UserAgent); itemToUpdate.SetComodityDescription(item.ComodityDescription, _identityProvider.Username, UserAgent); itemToUpdate.SetQuantity(item.Quantity, _identityProvider.Username, UserAgent); itemToUpdate.SetUomId(item.UomId, _identityProvider.Username, UserAgent); itemToUpdate.SetUomUnit(item.UomUnit, _identityProvider.Username, UserAgent); itemToUpdate.SetPriceRO(item.PriceRO, _identityProvider.Username, UserAgent); itemToUpdate.SetPrice(item.Price, _identityProvider.Username, UserAgent); itemToUpdate.SetPriceFob(item.PriceFOB, _identityProvider.Username, UserAgent); itemToUpdate.SetPriceCmt(item.PriceCMT, _identityProvider.Username, UserAgent); itemToUpdate.SetAmount(item.Amount, _identityProvider.Username, UserAgent); itemToUpdate.SetValas(item.Valas, _identityProvider.Username, UserAgent); itemToUpdate.SetUnitId(item.UnitId, _identityProvider.Username, UserAgent); itemToUpdate.SetUnitCode(item.UnitCode, _identityProvider.Username, UserAgent); itemToUpdate.SetArticle(item.Article, _identityProvider.Username, UserAgent); itemToUpdate.SetOrderNo(item.OrderNo, _identityProvider.Username, UserAgent); itemToUpdate.SetDescription(item.Description, _identityProvider.Username, UserAgent); itemToUpdate.SetDescriptionMd(item.DescriptionMd, _identityProvider.Username, UserAgent); foreach (var detailToUpdate in itemToUpdate.Details) { var detail = item.Details.FirstOrDefault(d => d.Id == detailToUpdate.Id); if (detail != null) { detailToUpdate.SetCarton1(detail.Carton1, _identityProvider.Username, UserAgent); detailToUpdate.SetCarton2(detail.Carton2, _identityProvider.Username, UserAgent); detailToUpdate.SetStyle(detail.Style, _identityProvider.Username, UserAgent); detailToUpdate.SetColour(detail.Colour, _identityProvider.Username, UserAgent); detailToUpdate.SetCartonQuantity(detail.CartonQuantity, _identityProvider.Username, UserAgent); detailToUpdate.SetQuantityPCS(detail.QuantityPCS, _identityProvider.Username, UserAgent); detailToUpdate.SetTotalQuantity(detail.TotalQuantity, _identityProvider.Username, UserAgent); detailToUpdate.SetLength(detail.Length, _identityProvider.Username, UserAgent); detailToUpdate.SetWidth(detail.Width, _identityProvider.Username, UserAgent); detailToUpdate.SetHeight(detail.Height, _identityProvider.Username, UserAgent); detailToUpdate.SetGrossWeight(detail.GrossWeight, _identityProvider.Username, UserAgent); detailToUpdate.SetNetWeight(detail.NetWeight, _identityProvider.Username, UserAgent); detailToUpdate.SetNetNetWeight(detail.NetNetWeight, _identityProvider.Username, UserAgent); foreach (var sizeToUpdate in detailToUpdate.Sizes) { var size = detail.Sizes.FirstOrDefault(s => s.Id == sizeToUpdate.Id); if (size != null) { sizeToUpdate.SetSizeId(size.SizeId, _identityProvider.Username, UserAgent); sizeToUpdate.SetSize(size.Size, _identityProvider.Username, UserAgent); sizeToUpdate.SetQuantity(size.Quantity, _identityProvider.Username, UserAgent); } else { sizeToUpdate.FlagForDelete(_identityProvider.Username, UserAgent); } } foreach (var size in detail.Sizes.Where(w => w.Id == 0)) { size.FlagForCreate(_identityProvider.Username, UserAgent); detailToUpdate.Sizes.Add(size); } } else { detailToUpdate.FlagForDelete(_identityProvider.Username, UserAgent); } } foreach (var detail in item.Details.Where(w => w.Id == 0)) { detail.FlagForCreate(_identityProvider.Username, UserAgent); foreach (var size in detail.Sizes) { size.FlagForCreate(_identityProvider.Username, UserAgent); } itemToUpdate.Details.Add(detail); } } else { itemToUpdate.FlagForDelete(_identityProvider.Username, UserAgent); } } foreach (var item in model.Items.Where(w => w.Id == 0)) { item.FlagForCreate(_identityProvider.Username, UserAgent); foreach (var detail in item.Details) { detail.FlagForCreate(_identityProvider.Username, UserAgent); foreach (var size in detail.Sizes) { size.FlagForCreate(_identityProvider.Username, UserAgent); } } modelToUpdate.Items.Add(item); } var measurements = modelToUpdate.Items .SelectMany(i => i.Details.Select(d => new { d.Carton1, d.Carton2, d.Length, d.Width, d.Height, d.CartonQuantity })) .GroupBy(m => new { m.Length, m.Width, m.Height }, (k, g) => new GarmentPackingListMeasurementModel(k.Length, k.Width, k.Height, g.Distinct().Sum(d => d.CartonQuantity))); foreach (var measurementToUpdate in modelToUpdate.Measurements) { var measurement = measurements.FirstOrDefault(m => m.Length == measurementToUpdate.Length && m.Width == measurementToUpdate.Width && m.Height == measurementToUpdate.Height); if (measurement != null) { measurementToUpdate.SetCartonsQuantity(measurement.CartonsQuantity, _identityProvider.Username, UserAgent); } else { measurementToUpdate.FlagForDelete(_identityProvider.Username, UserAgent); } } foreach (var measurement in measurements) { var oldMeasurement = modelToUpdate.Measurements.FirstOrDefault(m => m.IsDeleted == false && m.Length == measurement.Length && m.Width == measurement.Width && m.Height == measurement.Height); if (oldMeasurement == null) { measurement.FlagForCreate(_identityProvider.Username, UserAgent); modelToUpdate.Measurements.Add(measurement); } } var itemsUpdate = modelToUpdate.Items.Where(i => i.IsDeleted == false).OrderBy(o => o.ComodityDescription); var totalCartons = itemsUpdate .SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, d.CartonQuantity })) .Distinct().Sum(d => d.CartonQuantity); modelToUpdate.SetTotalCartons(totalCartons, _identityProvider.Username, UserAgent); var totalGw = itemsUpdate .SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, totalGrossWeight = d.CartonQuantity * d.GrossWeight })) .GroupBy(g => new { g.Carton1, g.Carton2 }, (key, value) => value.First().totalGrossWeight).Sum(); modelToUpdate.SetGrossWeight(totalGw, _identityProvider.Username, UserAgent); var totalNw = itemsUpdate .SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, totalNetWeight = d.CartonQuantity * d.NetWeight })) .GroupBy(g => new { g.Carton1, g.Carton2 }, (key, value) => value.First().totalNetWeight).Sum(); modelToUpdate.SetNettWeight(totalNw, _identityProvider.Username, UserAgent); var totalNnw = itemsUpdate .SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, totalNetNetWeight = d.CartonQuantity * d.NetNetWeight })) .GroupBy(g => new { g.Carton1, g.Carton2 }, (key, value) => value.First().totalNetNetWeight).Sum(); modelToUpdate.SetNetNetWeight(totalNnw, _identityProvider.Username, UserAgent); return(_packingListRepository.SaveChanges()); }
public GarmentPackingListWithHederPdfByOrderNoSectionDPageEvent(IIdentityProvider identityProvider, GarmentPackingListViewModel viewModel) { this.identityProvider = identityProvider; this.viewModel = viewModel; }
public MemoryStream GenerateExcelTemplate(GarmentPackingListViewModel viewModel, string fob, string cPrice) { //int maxSizesCount = viewModel.Items.Max(i => i.Details.Max(d => d.Sizes.GroupBy(g => g.Size.Id).Count())); int maxSizesCount = 0; var sizesMax = new Dictionary <int, string>(); foreach (var item in viewModel.Items) { foreach (var detail in item.Details) { foreach (var size in detail.Sizes) { sizesMax[size.Size.Id] = size.Size.Size; } } } maxSizesCount = sizesMax.Count; int SIZES_COUNT = maxSizesCount > 11 ? 20 : 11; var col = GetColNameFromIndex(4 + SIZES_COUNT); var colCtns = GetColNameFromIndex(SIZES_COUNT + 5); var colPcs = GetColNameFromIndex(SIZES_COUNT + 6); var colQty = GetColNameFromIndex(SIZES_COUNT + 7); var colSatuan = GetColNameFromIndex(SIZES_COUNT + 8); var colGw = GetColNameFromIndex(SIZES_COUNT + 9); var colNw = GetColNameFromIndex(SIZES_COUNT + 10); var colNnw = GetColNameFromIndex(SIZES_COUNT + 11); DataTable result = new DataTable(); ExcelPackage package = new ExcelPackage(); var sheet = package.Workbook.Worksheets.Add("Report"); sheet.Cells["A1"].Value = "Invoice No."; sheet.Cells["A1:B1"].Merge = true; sheet.Column(1).Width = 6; sheet.Column(2).Width = 5; sheet.Column(3).Width = 6; sheet.Column(3).Width = 7; sheet.Cells["C1"].Value = viewModel.InvoiceNo; sheet.Cells["C1:D1"].Merge = true; sheet.Cells[$"A1:{colNnw}1"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; sheet.Cells["E1"].Value = "Date : " + viewModel.Date.GetValueOrDefault().ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).ToString("MMM dd, yyyy."); sheet.Cells[$"E1:{col}1"].Merge = true; sheet.Cells[$"{colCtns}1"].Value = "Ref No. : FM-00-SP-24-005 "; sheet.Cells[$"{colCtns}1"].Style.Font.Bold = true; sheet.Cells[$"{colCtns}1:{colNnw}1"].Merge = true; sheet.Cells["A3"].Value = cPrice; sheet.Cells["A3:C3"].Merge = true; sheet.Cells["D3"].Value = ":"; sheet.Cells["E3"].Value = fob; sheet.Cells[$"E3:{colNnw}3"].Merge = true; if (viewModel.PaymentTerm == "LC") { sheet.Cells["A4"].Value = "LC No."; sheet.Cells["A4:C4"].Merge = true; sheet.Cells["D4"].Value = ":"; sheet.Cells["E4"].Value = viewModel.LCNo; sheet.Cells[$"E4:{colNnw}4"].Merge = true; sheet.Cells["A5"].Value = "Tgl LC."; sheet.Cells["A5:C5"].Merge = true; sheet.Cells["D5"].Value = ":"; sheet.Cells["E5"].Value = viewModel.LCDate.GetValueOrDefault().ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).ToString("dd MMMM yyyy"); sheet.Cells[$"E5:{colNnw}5"].Merge = true; sheet.Cells["A6"].Value = "ISSUED BY"; sheet.Cells["A6:C6"].Merge = true; sheet.Cells["D6"].Value = ":"; sheet.Cells["E6"].Value = viewModel.IssuedBy; sheet.Cells[$"E6:{colNnw}6"].Merge = true; } else { sheet.Cells["A4"].Value = "Payment Term"; sheet.Cells["A4:C4"].Merge = true; sheet.Cells["E4"].Value = viewModel.PaymentTerm; sheet.Cells[$"E4:{colNnw}4"].Merge = true; } double totalCtns = 0; double totalGw = 0; double totalNw = 0; double totalNnw = 0; double grandTotal = 0; var uom = ""; var arrayGrandTotal = new Dictionary <String, double>(); List <string> cartonNumbers = new List <string>(); var newItems = new List <GarmentPackingListItemViewModel>(); var newItems2 = new List <GarmentPackingListItemViewModel>(); var newDetails = new List <GarmentPackingListDetailViewModel>(); foreach (var item in viewModel.Items.OrderBy(a => a.RONo)) { foreach (var detail in item.Details) { newDetails.Add(detail); } } newDetails = newDetails.OrderBy(a => a.Carton1).ToList(); foreach (var d in newDetails) { if (newItems.Count == 0) { var i = viewModel.Items.Single(a => a.Id == d.PackingListItemId); i.Details = new List <GarmentPackingListDetailViewModel>(); i.Details.Add(d); newItems.Add(i); } else { if (newItems.Last().Id == d.PackingListItemId) { newItems.Last().Details.Add(d); } else { var y = viewModel.Items.Select(a => new GarmentPackingListItemViewModel { Id = a.Id, RONo = a.RONo, Article = a.Article, BuyerAgent = a.BuyerAgent, ComodityDescription = a.ComodityDescription, OrderNo = a.OrderNo, AVG_GW = a.AVG_GW, AVG_NW = a.AVG_NW, Description = a.Description, Uom = a.Uom }) .Single(a => a.Id == d.PackingListItemId); y.Details = new List <GarmentPackingListDetailViewModel>(); y.Details.Add(d); newItems.Add(y); } } } foreach (var item in newItems) { if (newItems2.Count == 0) { newItems2.Add(item); } else { if (newItems2.Last().OrderNo == item.OrderNo) { foreach (var d in item.Details.OrderBy(a => a.Carton1)) { newItems2.Last().Details.Add(d); } } else { newItems2.Add(item); } } } var index = 8; var afterSubTotalIndex = 0; foreach (var item in newItems2) { var afterIndex = index + 1; var sizeIndex = afterIndex + 1; var valueIndex = sizeIndex + 1; sheet.Cells[$"A{index}"].Value = "DESCRIPTION OF GOODS"; sheet.Cells[$"A{index}:C{index}"].Merge = true; sheet.Row(index).Height = 25; sheet.Cells[$"D{index}"].Value = ":"; sheet.Cells[$"E{index}"].Value = item.Description; sheet.Cells[$"E{index}:{colNnw}{index}"].Merge = true; sheet.Cells[$"A{afterIndex}"].Value = "CARTON NO."; sheet.Cells[$"A{afterIndex}:A{afterIndex + 1}"].Merge = true; sheet.Cells[$"A{afterIndex}:A{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"A{afterIndex}:A{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double; sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[$"A{afterIndex}:A{afterIndex + 1}"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex + 1}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[$"B{afterIndex}"].Value = "COLOUR"; sheet.Cells[$"B{afterIndex}:B{afterIndex + 1}"].Merge = true; sheet.Cells[$"B{afterIndex}:B{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"B{afterIndex}:B{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"C{afterIndex}"].Value = "ART. NO."; sheet.Cells[$"C{afterIndex}:C{afterIndex + 1}"].Merge = true; sheet.Cells[$"C{afterIndex}:C{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"C{afterIndex}:C{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"D{afterIndex}"].Value = "ORDER. NO."; sheet.Cells[$"D{afterIndex}:D{afterIndex + 1}"].Merge = true; sheet.Cells[$"D{afterIndex}:D{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"D{afterIndex}:D{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"E{afterIndex}"].Value = "SIZE"; sheet.Cells[$"E{afterIndex}:{col}{afterIndex}"].Merge = true; sheet.Cells[$"E{afterIndex}:{col}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; var sizes = new Dictionary <int, string>(); foreach (var detail in item.Details) { foreach (var size in detail.Sizes) { sizes[size.Size.Id] = size.Size.Size; } } ; for (int i = 0; i < SIZES_COUNT; i++) { var colSize = GetColNameFromIndex(5 + i); var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); sheet.Cells[$"{colSize}{sizeIndex}"].Value = size.Key == 0 ? "" : size.Value; sheet.Cells[$"{colSize}{sizeIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; } sheet.Cells[$"{colCtns}{afterIndex}"].Value = "CTNS"; sheet.Column(GetColNumberFromName(colCtns)).Width = 4; sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex + 1}"].Merge = true; sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colPcs}{afterIndex}"].Value = "@"; sheet.Column(GetColNumberFromName(colPcs)).Width = 4; sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex + 1}"].Merge = true; sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colQty}{afterIndex}"].Value = "QTY"; sheet.Column(GetColNumberFromName(colQty)).Width = 4; sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex + 1}"].Merge = true; sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colSatuan}{afterIndex}"].Value = "SATUAN"; sheet.Column(GetColNumberFromName(colSatuan)).Width = 6; sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex + 1}"].Merge = true; sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colGw}{afterIndex}"].Value = "GW"; sheet.Column(GetColNumberFromName(colGw)).Width = 4; sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex + 1}"].Merge = true; sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colNw}{afterIndex}"].Value = "NW"; sheet.Column(GetColNumberFromName(colNw)).Width = 4; sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex + 1}"].Merge = true; sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colNnw}{afterIndex}"].Value = "NNW"; sheet.Column(GetColNumberFromName(colNnw)).Width = 4; sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex + 1}"].Merge = true; sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; double subCtns = 0; double subGw = 0; double subNw = 0; double subNnw = 0; double subTotal = 0; var sizeSumQty = new Dictionary <int, double>(); var arraySubTotal = new Dictionary <String, double>(); foreach (var detail in item.Details) { var ctnsQty = detail.CartonQuantity; uom = viewModel.Items.Where(a => a.Id == detail.PackingListItemId).Single().Uom.Unit; if (cartonNumbers.Contains($"{detail.Carton1}- {detail.Carton2}")) { ctnsQty = 0; } else { cartonNumbers.Add($"{detail.Carton1}- {detail.Carton2}"); } sheet.Cells[$"A{valueIndex}"].Value = $"{detail.Carton1}- {detail.Carton2}"; sheet.Cells[$"A{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"B{valueIndex}"].Value = detail.Colour; sheet.Cells[$"B{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"C{valueIndex}"].Value = item.Article; sheet.Cells[$"C{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"D{valueIndex}"].Value = item.OrderNo; sheet.Cells[$"D{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; for (int i = 0; i < SIZES_COUNT; i++) { var colSize = GetColNameFromIndex(5 + i); var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = detail.Sizes.Where(w => w.Size.Id == size.Key).Sum(s => s.Quantity); } if (sizeSumQty.ContainsKey(size.Key)) { sizeSumQty[size.Key] += quantity * detail.CartonQuantity; } else { sizeSumQty.Add(size.Key, quantity * detail.CartonQuantity); } sheet.Cells[$"{colSize}{valueIndex}"].Value = quantity == 0 ? "" : quantity.ToString(); sheet.Column(GetColNumberFromName(colSize)).Width = 3.5; sheet.Cells[$"{colSize}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; } subCtns += ctnsQty; subGw += detail.GrossWeight; subNw += detail.NetWeight; subNnw += detail.NetNetWeight; sheet.Cells[$"{colCtns}{valueIndex}"].Value = ctnsQty.ToString(); sheet.Cells[$"{colCtns}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colPcs}{valueIndex}"].Value = detail.QuantityPCS.ToString(); sheet.Cells[$"{colPcs}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; var totalQuantity = detail.CartonQuantity * detail.QuantityPCS; subTotal += totalQuantity; if (!arraySubTotal.ContainsKey(uom)) { arraySubTotal.Add(uom, totalQuantity); } else { arraySubTotal[uom] += totalQuantity; } sheet.Cells[$"{colQty}{valueIndex}"].Value = totalQuantity.ToString(); sheet.Cells[$"{colQty}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colSatuan}{valueIndex}"].Value = uom; sheet.Cells[$"{colSatuan}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colGw}{valueIndex}"].Value = detail.GrossWeight.ToString(); sheet.Cells[$"{colGw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colNw}{valueIndex}"].Value = detail.NetWeight.ToString(); sheet.Cells[$"{colNw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colNnw}{valueIndex}"].Value = detail.NetNetWeight.ToString(); sheet.Cells[$"{colNnw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; valueIndex++; } var sumValueIndex = 0; for (int i = 0; i < SIZES_COUNT; i++) { var colSize = GetColNameFromIndex(5 + i); sumValueIndex = valueIndex + 1; var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = sizeSumQty.Where(w => w.Key == size.Key).Sum(a => a.Value); } sheet.Cells[$"D{valueIndex}"].Value = "SUMMARY"; sheet.Cells[$"D{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colSize}{valueIndex}"].Value = quantity == 0 ? "" : quantity.ToString(); } sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; totalCtns += subCtns; totalGw += subGw; totalNw += subNw; totalNnw += subNnw; grandTotal += subTotal; if (!arrayGrandTotal.ContainsKey(uom)) { arrayGrandTotal.Add(uom, subTotal); } else { arrayGrandTotal[uom] += subTotal; } var subTotalResult = string.Join(" / ", arraySubTotal.Select(x => x.Value + " " + x.Key).ToArray()); sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Merge = true; sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Value = "SUB TOTAL"; sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colQty}{sumValueIndex}"].Value = subTotalResult; sheet.Cells[$"{colQty}{sumValueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colQty}{sumValueIndex}:{colNnw}{sumValueIndex}"].Merge = true; sheet.Cells[$"A{sumValueIndex}:{colNnw}{sumValueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); sheet.Cells[$"A{sumValueIndex}:{colNnw}{sumValueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; afterSubTotalIndex = sumValueIndex + 1; sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Merge = true; sheet.Cells[$"A{afterSubTotalIndex}"].Value = $" - Sub Ctns = {subCtns} - Sub G.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.GrossWeight * a.CartonQuantity))} Kgs - Sub N.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.NetWeight * a.CartonQuantity))} Kgs - Sub N.N.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.NetNetWeight * a.CartonQuantity))} Kgs"; sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; afterIndex = sizeIndex++; index = afterSubTotalIndex + 2; } #region GrandTotal var grandTotalResult = string.Join(" / ", arrayGrandTotal.Select(x => x.Value + " " + x.Key).ToArray()); var grandTotalIndex = afterSubTotalIndex + 2; sheet.Cells[$"A{grandTotalIndex}:{colPcs}{grandTotalIndex}"].Merge = true; sheet.Cells[$"A{grandTotalIndex}:{colPcs}{grandTotalIndex}"].Value = "GRAND TOTAL"; sheet.Cells[$"A{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double; sheet.Cells[$"A{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double; sheet.Cells[$"{colQty}{grandTotalIndex}"].Value = grandTotalResult; sheet.Cells[$"{colQty}{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Merge = true; var comodities = viewModel.Items.Select(s => s.Comodity.Name.ToUpper()).Distinct(); var spellingWordIndex = grandTotalIndex + 2; sheet.Cells[$"A{spellingWordIndex}:{colNnw}{spellingWordIndex}"].Merge = true; sheet.Cells[$"A{spellingWordIndex}"].Value = $"{totalCtns} {viewModel.SayUnit} [ {NumberToTextEN.toWords(totalCtns).Trim().ToUpper()} {viewModel.SayUnit} OF {string.Join(" AND ", comodities)}]"; for (int i = 8; i < grandTotalIndex; i++) { if (sheet.Row(i).Height != 25) { sheet.Row(i).Height = 16; } } #endregion #region Mark var shippingMarkIndex = spellingWordIndex + 2; var sideMarkIndex = spellingWordIndex + 2; sheet.Cells[$"A{shippingMarkIndex}"].Value = "SHIPPING MARKS"; sheet.Cells[$"A{shippingMarkIndex}:B{shippingMarkIndex}"].Merge = true; sheet.Cells[$"A{++shippingMarkIndex}"].Value = viewModel.ShippingMark; sheet.Cells[$"F{sideMarkIndex}"].Value = "SIDE MARKS"; sheet.Cells[$"F{sideMarkIndex}:G{sideMarkIndex}"].Merge = true; sheet.Cells[$"F{++sideMarkIndex}"].Value = viewModel.SideMark; byte[] shippingMarkImage; if (!String.IsNullOrEmpty(viewModel.ShippingMarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile))) { shippingMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile)); Image shipMarkImage = byteArrayToImage(shippingMarkImage); var imageShippingMarkIndex = shippingMarkIndex + 1; ExcelPicture excelPictureShipMarkImage = sheet.Drawings.AddPicture("ShippingMarkImage", shipMarkImage); excelPictureShipMarkImage.From.Column = 0; excelPictureShipMarkImage.From.Row = imageShippingMarkIndex; excelPictureShipMarkImage.SetSize(200, 200); } } byte[] sideMarkImage; if (!String.IsNullOrEmpty(viewModel.SideMarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile))) { sideMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile)); Image _sideMarkImage = byteArrayToImage(sideMarkImage); var sideMarkImageIndex = sideMarkIndex + 1; ExcelPicture excelPictureSideMarkImage = sheet.Drawings.AddPicture("SideMarkImage", _sideMarkImage); excelPictureSideMarkImage.From.Column = 5; excelPictureSideMarkImage.From.Row = sideMarkImageIndex; excelPictureSideMarkImage.SetSize(200, 200); } } #endregion #region Measurement var grossWeightIndex = shippingMarkIndex + 18; var netWeightIndex = grossWeightIndex + 1; var netNetWeightIndex = netWeightIndex + 1; var measurementIndex = netNetWeightIndex + 1; sheet.Cells[$"A{grossWeightIndex}"].Value = "GROSS WEIGHT"; sheet.Cells[$"A{grossWeightIndex}:B{grossWeightIndex}"].Merge = true; sheet.Cells[$"C{grossWeightIndex}"].Value = viewModel.GrossWeight + " KGS"; sheet.Cells[$"A{netWeightIndex}"].Value = "NET WEIGHT"; sheet.Cells[$"A{netWeightIndex}:B{netWeightIndex}"].Merge = true; sheet.Cells[$"C{netWeightIndex}"].Value = viewModel.NettWeight + " KGS"; sheet.Cells[$"A{netNetWeightIndex}"].Value = "NET NET WEIGHT"; sheet.Cells[$"A{netNetWeightIndex}:B{netNetWeightIndex}"].Merge = true; sheet.Cells[$"C{netNetWeightIndex}"].Value = viewModel.NetNetWeight + " KGS"; sheet.Cells[$"A{measurementIndex}"].Value = "MEASUREMENT"; sheet.Cells[$"A{measurementIndex}:B{measurementIndex}"].Merge = true; decimal totalCbm = 0; foreach (var measurement in viewModel.Measurements) { sheet.Cells[$"C{measurementIndex}"].Value = measurement.Length + " X "; sheet.Cells[$"D{measurementIndex}"].Value = measurement.Width + " X "; sheet.Cells[$"E{measurementIndex}"].Value = measurement.Height + " X "; sheet.Cells[$"E{measurementIndex}:G{measurementIndex}"].Merge = true; sheet.Cells[$"H{measurementIndex}"].Value = measurement.CartonsQuantity + " CTNS"; sheet.Cells[$"H{measurementIndex}:I{measurementIndex}"].Merge = true; sheet.Cells[$"J{measurementIndex}"].Value = "="; var cbm = (decimal)measurement.Length * (decimal)measurement.Width * (decimal)measurement.Height * (decimal)measurement.CartonsQuantity / 1000000; totalCbm += cbm; sheet.Cells[$"K{measurementIndex}"].Value = string.Format("{0:N2} CBM", cbm); sheet.Cells[$"K{measurementIndex}:M{measurementIndex}"].Merge = true; measurementIndex++; } var totalMeasurementIndex = measurementIndex; sheet.Cells[$"C{totalMeasurementIndex}:K{totalMeasurementIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[$"D{totalMeasurementIndex}"].Value = "TOTAL"; sheet.Cells[$"D{totalMeasurementIndex}:G{totalMeasurementIndex}"].Merge = true; sheet.Cells[$"H{totalMeasurementIndex}"].Value = viewModel.Measurements.Sum(m => m.CartonsQuantity) + " CTNS ."; sheet.Cells[$"H{totalMeasurementIndex}:I{totalMeasurementIndex}"].Merge = true; sheet.Cells[$"K{totalMeasurementIndex}"].Value = string.Format("{0:N2} CBM", totalCbm); sheet.Cells[$"J{totalMeasurementIndex}"].Value = "="; sheet.Cells[$"K{totalMeasurementIndex}:L{totalMeasurementIndex}"].Merge = true; #endregion #region remark var remarkIndex = totalMeasurementIndex + 1; sheet.Cells[$"A{remarkIndex}"].Value = "REMARK"; sheet.Cells[$"A{++remarkIndex}"].Value = viewModel.Remark; byte[] remarkImage; var remarkImageIndex = remarkIndex + 1; if (!String.IsNullOrEmpty(viewModel.RemarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.RemarkImageFile))) { remarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.RemarkImageFile)); Image _remarkImage = byteArrayToImage(remarkImage); ExcelPicture excelPictureRemarkImage = sheet.Drawings.AddPicture("RemarkImage", _remarkImage); excelPictureRemarkImage.From.Column = 0; excelPictureRemarkImage.From.Row = remarkImageIndex; excelPictureRemarkImage.SetSize(200, 200); } } #endregion #region Signature var signatureIndex = remarkImageIndex + 14; sheet.Cells[$"{colCtns}{signatureIndex}:{colNnw}{signatureIndex}"].Merge = true; sheet.Cells[$"{colCtns}{signatureIndex}"].Value = "( MRS.ADRIYANA DAMAYANTI )"; sheet.Cells[$"{colCtns}{signatureIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colCtns}{++signatureIndex}"].Value = "AUTHORIZED SIGNATURE"; sheet.Cells[$"{colCtns}{signatureIndex}:{colNnw}{signatureIndex}"].Merge = true; sheet.Cells[$"{colCtns}{signatureIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; #endregion sheet.Cells.Style.Font.SetFromFont(new Font("Tahoma", 7, FontStyle.Regular)); //sheet.Cells[sheet.Dimension.Address].AutoFitColumns(15, 40); sheet.Cells.Style.WrapText = true; sheet.PrinterSettings.LeftMargin = 0.39M; sheet.PrinterSettings.TopMargin = 0; sheet.PrinterSettings.RightMargin = 0; sheet.PrinterSettings.Orientation = maxSizesCount > 11 ? eOrientation.Landscape : eOrientation.Portrait; MemoryStream stream = new MemoryStream(); package.DoAdjustDrawings = false; package.SaveAs(stream); return(stream); }
public GarmentPackingListPdfByOrderNoPageEvent(IIdentityProvider identityProvider, GarmentPackingListViewModel viewModel) { this.identityProvider = identityProvider; this.viewModel = viewModel; }
public override async Task <int> Update(int id, GarmentPackingListViewModel viewModel) { viewModel.ShippingMarkImagePath = await UploadImage(viewModel.ShippingMarkImageFile, viewModel.Id, viewModel.ShippingMarkImagePath, viewModel.CreatedUtc); viewModel.SideMarkImagePath = await UploadImage(viewModel.SideMarkImageFile, viewModel.Id, viewModel.SideMarkImagePath, viewModel.CreatedUtc); viewModel.RemarkImagePath = await UploadImage(viewModel.RemarkImageFile, viewModel.Id, viewModel.RemarkImagePath, viewModel.CreatedUtc); GarmentPackingListModel model = MapToModel(viewModel); GarmentPackingListModel modelToUpdate; if (model.Status == GarmentPackingListStatusEnum.DRAFT_APPROVED_SHIPPING) { modelToUpdate = _packingListRepository.Query .Include(i => i.Items) .ThenInclude(i => i.Details) .Include(i => i.Measurements) .FirstOrDefault(s => s.Id == id); foreach (var itemToUpdate in modelToUpdate.Items) { var item = model.Items.First(i => i.Id == itemToUpdate.Id); foreach (var detailToUpdate in itemToUpdate.Details) { var detail = item.Details.Where(d => d.Id == detailToUpdate.Id).First(); detailToUpdate.SetCarton1(detail.Carton1, _identityProvider.Username, UserAgent); detailToUpdate.SetCarton2(detail.Carton2, _identityProvider.Username, UserAgent); detailToUpdate.SetCartonQuantity(detail.CartonQuantity, _identityProvider.Username, UserAgent); detailToUpdate.SetTotalQuantity(detail.TotalQuantity, _identityProvider.Username, UserAgent); } } foreach (var measurementToUpdate in modelToUpdate.Measurements) { var measurement = model.Measurements.First(m => m.Id == measurementToUpdate.Id); measurementToUpdate.SetCartonsQuantity(measurement.CartonsQuantity, _identityProvider.Username, UserAgent); } } else { modelToUpdate = _packingListRepository.Query.FirstOrDefault(s => s.Id == id); } modelToUpdate.SetDate(model.Date, _identityProvider.Username, UserAgent); modelToUpdate.SetDestination(model.Destination, _identityProvider.Username, UserAgent); modelToUpdate.SetGrossWeight(model.GrossWeight, _identityProvider.Username, UserAgent); modelToUpdate.SetNettWeight(model.NettWeight, _identityProvider.Username, UserAgent); modelToUpdate.SetNetNetWeight(model.NetNetWeight, _identityProvider.Username, UserAgent); modelToUpdate.SetTotalCartons(model.TotalCartons, _identityProvider.Username, UserAgent); modelToUpdate.SetSayUnit(model.SayUnit, _identityProvider.Username, UserAgent); modelToUpdate.SetOtherCommodity(model.OtherCommodity, _identityProvider.Username, UserAgent); modelToUpdate.SetShippingMark(model.ShippingMark, _identityProvider.Username, UserAgent); modelToUpdate.SetSideMark(model.SideMark, _identityProvider.Username, UserAgent); modelToUpdate.SetRemark(model.Remark, _identityProvider.Username, UserAgent); modelToUpdate.SetShippingMarkImagePath(model.ShippingMarkImagePath, _identityProvider.Username, UserAgent); modelToUpdate.SetSideMarkImagePath(model.SideMarkImagePath, _identityProvider.Username, UserAgent); modelToUpdate.SetRemarkImagePath(model.RemarkImagePath, _identityProvider.Username, UserAgent); modelToUpdate.SetShippingStaff(model.ShippingStaffId, model.ShippingStaffName, _identityProvider.Username, UserAgent); modelToUpdate.SetDescription(model.Description, _identityProvider.Username, UserAgent); return(await _packingListRepository.SaveChanges()); }
protected GarmentPackingListViewModel MapToViewModel(GarmentPackingListModel model) { var vm = new GarmentPackingListViewModel() { Active = model.Active, Id = model.Id, CreatedAgent = model.CreatedAgent, CreatedBy = model.CreatedBy, CreatedUtc = model.CreatedUtc, DeletedAgent = model.DeletedAgent, DeletedBy = model.DeletedBy, DeletedUtc = model.DeletedUtc, IsDeleted = model.IsDeleted, LastModifiedAgent = model.LastModifiedAgent, LastModifiedBy = model.LastModifiedBy, LastModifiedUtc = model.LastModifiedUtc, InvoiceNo = model.InvoiceNo, PackingListType = model.PackingListType, InvoiceType = model.InvoiceType, Section = new Section { Id = model.SectionId, Code = model.SectionCode }, Date = model.Date, PaymentTerm = model.PaymentTerm, LCNo = model.LCNo, LCDate = model.LCDate, IssuedBy = model.IssuedBy, BuyerAgent = new Buyer { Id = model.BuyerAgentId, Code = model.BuyerAgentCode, Name = model.BuyerAgentName, }, Destination = model.Destination, FinalDestination = model.FinalDestination, ShipmentMode = model.ShipmentMode, TruckingDate = model.TruckingDate, TruckingEstimationDate = model.TruckingEstimationDate, ExportEstimationDate = model.ExportEstimationDate, Omzet = model.Omzet, Accounting = model.Accounting, FabricCountryOrigin = model.FabricCountryOrigin, FabricComposition = model.FabricComposition, RemarkMd = model.RemarkMd, IsUsed = model.IsUsed, IsPosted = model.IsPosted, IsCostStructured = model.IsCostStructured, Description = model.Description, Items = (model.Items ?? new List <GarmentPackingListItemModel>()).Select(i => new GarmentPackingListItemViewModel { Active = i.Active, Id = i.Id, CreatedAgent = i.CreatedAgent, CreatedBy = i.CreatedBy, CreatedUtc = i.CreatedUtc, DeletedAgent = i.DeletedAgent, DeletedBy = i.DeletedBy, DeletedUtc = i.DeletedUtc, IsDeleted = i.IsDeleted, LastModifiedAgent = i.LastModifiedAgent, LastModifiedBy = i.LastModifiedBy, LastModifiedUtc = i.LastModifiedUtc, RONo = i.RONo, SCNo = i.SCNo, BuyerBrand = new Buyer { Id = i.BuyerBrandId, Name = i.BuyerBrandName }, Comodity = new Comodity { Id = i.ComodityId, Code = i.ComodityCode, Name = i.ComodityName }, ComodityDescription = i.ComodityDescription, Quantity = i.Quantity, Uom = new UnitOfMeasurement { Id = i.UomId, Unit = i.UomUnit }, PriceRO = i.PriceRO, Price = i.Price, PriceFOB = i.PriceFOB, PriceCMT = i.PriceCMT, Amount = i.Amount, Valas = i.Valas, Unit = new Unit { Id = i.UnitId, Code = i.UnitCode }, Article = i.Article, OrderNo = i.OrderNo, Description = i.Description, DescriptionMd = i.DescriptionMd, Details = (i.Details ?? new List <GarmentPackingListDetailModel>()).Select(d => new GarmentPackingListDetailViewModel { Active = d.Active, Id = d.Id, CreatedAgent = d.CreatedAgent, CreatedBy = d.CreatedBy, CreatedUtc = d.CreatedUtc, DeletedAgent = d.DeletedAgent, DeletedBy = d.DeletedBy, DeletedUtc = d.DeletedUtc, IsDeleted = d.IsDeleted, LastModifiedAgent = d.LastModifiedAgent, LastModifiedBy = d.LastModifiedBy, LastModifiedUtc = d.LastModifiedUtc, PackingListItemId = d.PackingListItemId, Carton1 = d.Carton1, Carton2 = d.Carton2, Style = d.Style, Colour = d.Colour, CartonQuantity = d.CartonQuantity, QuantityPCS = d.QuantityPCS, TotalQuantity = d.TotalQuantity, Length = d.Length, Width = d.Width, Height = d.Height, GrossWeight = d.GrossWeight, NetWeight = d.NetWeight, NetNetWeight = d.NetNetWeight, Index = d.Index, Sizes = (d.Sizes ?? new List <GarmentPackingListDetailSizeModel>()).Select(s => new GarmentPackingListDetailSizeViewModel { Active = s.Active, Id = s.Id, CreatedAgent = s.CreatedAgent, CreatedBy = s.CreatedBy, CreatedUtc = s.CreatedUtc, DeletedAgent = s.DeletedAgent, DeletedBy = s.DeletedBy, DeletedUtc = s.DeletedUtc, IsDeleted = s.IsDeleted, LastModifiedAgent = s.LastModifiedAgent, LastModifiedBy = s.LastModifiedBy, LastModifiedUtc = s.LastModifiedUtc, Size = new SizeViewModel { Id = s.SizeId, Size = s.Size }, Quantity = s.Quantity }).ToList() }).ToList(), }).ToList(), GrossWeight = model.GrossWeight, NettWeight = model.NettWeight, NetNetWeight = model.NetNetWeight, TotalCartons = model.TotalCartons, Measurements = (model.Measurements ?? new List <GarmentPackingListMeasurementModel>()).Select(m => new GarmentPackingListMeasurementViewModel { Active = m.Active, Id = m.Id, CreatedAgent = m.CreatedAgent, CreatedBy = m.CreatedBy, CreatedUtc = m.CreatedUtc, DeletedAgent = m.DeletedAgent, DeletedBy = m.DeletedBy, DeletedUtc = m.DeletedUtc, IsDeleted = m.IsDeleted, LastModifiedAgent = m.LastModifiedAgent, LastModifiedBy = m.LastModifiedBy, LastModifiedUtc = m.LastModifiedUtc, Length = m.Length, Width = m.Width, Height = m.Height, CartonsQuantity = m.CartonsQuantity, }).ToList(), SayUnit = model.SayUnit, OtherCommodity = model.OtherCommodity, ShippingMark = model.ShippingMark, SideMark = model.SideMark, Remark = model.Remark, ShippingMarkImagePath = model.ShippingMarkImagePath, SideMarkImagePath = model.SideMarkImagePath, RemarkImagePath = model.RemarkImagePath, ShippingStaff = new ShippingStaff { id = model.ShippingStaffId, name = model.ShippingStaffName, }, Status = model.Status.ToString(), StatusActivities = (model.StatusActivities ?? new List <GarmentPackingListStatusActivityModel>()).Select(a => new GarmentPackingListStatusActivityViewModel { Id = a.Id, CreatedDate = a.CreatedDate, CreatedBy = a.CreatedBy, CreatedAgent = a.CreatedAgent, Status = a.Status.ToString(), Remark = a.Remark }).ToList() }; return(vm); }
public MemoryStream GeneratePdfTemplate(GarmentPackingListViewModel viewModel) { //int maxSizesCount = viewModel.Items == null || viewModel.Items.Count < 1 ? 0 : viewModel.Items.Max(i => i.Details == null || i.Details.Count < 1 ? 0 : i.Details.Max(d => d.Sizes == null || d.Sizes.Count < 1 ? 0 : d.Sizes.GroupBy(g => g.Size.Id).Count())); var newItems = new List <GarmentPackingListItemViewModel>(); var newDetails = new List <GarmentPackingListDetailViewModel>(); foreach (var item in viewModel.Items) { foreach (var detail in item.Details) { newDetails.Add(detail); } } newDetails = newDetails.OrderBy(a => a.Carton1).ToList(); foreach (var x in viewModel.Items.OrderBy(o => o.RONo)) { if (newItems.Count == 0) { newItems.Add(x); } else { if (newItems.Last().RONo == x.RONo && newItems.Last().OrderNo == x.OrderNo) { foreach (var d in x.Details.OrderBy(a => a.Carton1)) { newItems.Last().Details.Add(d); } } else { var y = viewModel.Items.Select(a => new GarmentPackingListItemViewModel { Id = a.Id, RONo = a.RONo, Article = a.Article, BuyerAgent = a.BuyerAgent, ComodityDescription = a.ComodityDescription, OrderNo = a.OrderNo, AVG_GW = a.AVG_GW, AVG_NW = a.AVG_NW, Uom = a.Uom, }).Single(a => a.RONo == x.RONo && a.OrderNo == x.OrderNo && a.Id == x.Id); y.Details = new List <GarmentPackingListDetailViewModel>(); foreach (var d in x.Details.OrderBy(a => a.Carton1)) { y.Details.Add(d); } newItems.Add(y); } } } var sizesCount = false; foreach (var item in newItems.OrderBy(a => a.RONo)) { var sizesMax = new Dictionary <int, string>(); foreach (var detail in item.Details.OrderBy(o => o.Carton1)) { foreach (var size in detail.Sizes) { sizesMax[size.Size.Id] = size.Size.Size; } } if (sizesMax.Count > 11) { sizesCount = true; } } int SIZES_COUNT = sizesCount ? 20 : 11; Font header_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 14); Font normal_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Font body_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Font normal_font_underlined = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8, Font.UNDERLINE); Font bold_font = FontFactory.GetFont(BaseFont.HELVETICA_BOLD, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Document document = new Document(sizesCount ? PageSize.A4.Rotate() : PageSize.A4, 20, 20, 70, 30); MemoryStream stream = new MemoryStream(); PdfWriter writer = PdfWriter.GetInstance(document, stream); writer.PageEvent = new GarmentPackingListDraftPDFTemplatePageEvent(_identityProvider, viewModel); document.Open(); PdfContentByte cb = writer.DirectContent; PdfPCell cellBorderBottomRight = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER | Rectangle.RIGHT_BORDER | Rectangle.LEFT_BORDER, HorizontalAlignment = Element.ALIGN_CENTER }; PdfPCell cellBorderBottom = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, HorizontalAlignment = Element.ALIGN_CENTER }; var cartons = new List <GarmentPackingListDetailViewModel>(); double grandTotal = 0; var arraySubTotal = new Dictionary <String, double>(); List <string> cartonNumbers = new List <string>(); //foreach (var d in newDetails) //{ // if (newItems.Count == 0) // { // var i = viewModel.Items.Single(a => a.Id == d.PackingListItemId); // i.Details = new List<GarmentPackingListDetailViewModel>(); // i.Details.Add(d); // newItems.Add(i); // } // else // { // if (newItems.Last().Id == d.PackingListItemId) // { // newItems.Last().Details.Add(d); // } // else // { // var y = viewModel.Items.Select(a=> new GarmentPackingListItemViewModel { // Id =a.Id, RONo=a.RONo, Article=a.Article, BuyerAgent=a.BuyerAgent, ComodityDescription=a.ComodityDescription, // OrderNo=a.OrderNo, AVG_GW=a.AVG_GW, AVG_NW=a.AVG_NW}) // .Single(a => a.Id == d.PackingListItemId); // y.Details = new List<GarmentPackingListDetailViewModel>(); // y.Details.Add(d); // newItems.Add(y); // } // } //} document.Add(new Paragraph("SHIPPING METHOD : " + viewModel.ShipmentMode + "\n", normal_font)); foreach (var item in newItems.OrderBy(a => a.RONo)) { #region Item PdfPTable tableItem = new PdfPTable(6); tableItem.SetWidths(new float[] { 2f, 0.2f, 2.8f, 2f, 0.2f, 2.8f }); PdfPCell cellItemContent = new PdfPCell() { Border = Rectangle.NO_BORDER }; cellItemContent.Phrase = new Phrase("RO No", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(item.RONo, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("ARTICLE", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(item.Article, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("BUYER", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(viewModel.BuyerAgent.Name, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("", normal_font); cellItemContent.Phrase = new Phrase("DESCRIPTION OF GOODS", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(item.ComodityDescription, normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase("", normal_font); tableItem.AddCell(cellItemContent); tableItem.AddCell(cellItemContent); tableItem.AddCell(cellItemContent); new PdfPCell(tableItem); tableItem.ExtendLastRow = false; document.Add(tableItem); #endregion var sizes = new Dictionary <int, string>(); foreach (var detail in item.Details) { foreach (var size in detail.Sizes) { sizes[size.Size.Id] = size.Size.Size; } } PdfPTable tableDetail = new PdfPTable(SIZES_COUNT + 11); var width = new List <float> { 2f, 3.5f, 4f, 4f }; for (int i = 0; i < SIZES_COUNT; i++) { width.Add(1f); } width.AddRange(new List <float> { 1.5f, 1f, 1.5f, 2f, 1.5f, 1.5f, 1.5f }); tableDetail.SetWidths(width.ToArray()); PdfPCell cellDetailLine = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = 20, Padding = 0.5f, Phrase = new Phrase("") }; tableDetail.AddCell(cellDetailLine); tableDetail.AddCell(cellDetailLine); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("CARTON NO.", normal_font, 0.75f)); cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("COLOUR", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("STYLE", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("ORDER NO.", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("S I Z E", normal_font, 0.75f)); cellBorderBottomRight.Colspan = SIZES_COUNT; cellBorderBottomRight.Rowspan = 1; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("CTNS", normal_font, 0.75f)); cellBorderBottomRight.Colspan = 1; cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("@", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("QTY", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("UNIT", normal_font, 0.75f)); cellBorderBottomRight.Colspan = 1; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("GW/\nCTN", normal_font, 0.75f)); cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("NW/\nCTN", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("NNW/\nCTN", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Rowspan = 1; for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(size.Key == 0 ? "" : size.Value, normal_font, 0.5f)); cellBorderBottomRight.Rowspan = 1; tableDetail.AddCell(cellBorderBottomRight); } var subCartons = new List <GarmentPackingListDetailViewModel>(); var subGrossWeight = new List <GarmentPackingListDetailViewModel>(); var subNetWeight = new List <GarmentPackingListDetailViewModel>(); var subNetNetWeight = new List <GarmentPackingListDetailViewModel>(); double subTotal = 0; var sizeSumQty = new Dictionary <int, double>(); foreach (var detail in item.Details.OrderBy(o => o.Carton1)) { var ctnsQty = detail.CartonQuantity; var grossWeight = detail.GrossWeight; var netWeight = detail.NetWeight; var netNetWeight = detail.NetNetWeight; if (cartonNumbers.Contains($"{detail.Index}-{detail.Carton1}- {detail.Carton2}")) { ctnsQty = 0; grossWeight = 0; netWeight = 0; netNetWeight = 0; } else { cartonNumbers.Add($"{detail.Index}-{detail.Carton1}- {detail.Carton2}"); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk($"{detail.Carton1}- {detail.Carton2}", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.Colour, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.Style, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(item.OrderNo, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = detail.Sizes.Where(w => w.Size.Id == size.Key).Sum(s => s.Quantity); } if (sizeSumQty.ContainsKey(size.Key)) { sizeSumQty[size.Key] += quantity * detail.CartonQuantity; } else { sizeSumQty.Add(size.Key, quantity * detail.CartonQuantity); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(quantity == 0 ? "" : quantity.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(ctnsQty.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.QuantityPCS.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); var totalQuantity = (detail.CartonQuantity * detail.QuantityPCS); subTotal += totalQuantity; cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(totalQuantity.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(item.Uom.Unit, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.GrossWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.NetWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.NetNetWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); if (cartons.FindIndex(c => c.Carton1 == detail.Carton1 && c.Carton2 == detail.Carton2 && c.Index == detail.Index) < 0) { cartons.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = ctnsQty }); } if (subCartons.FindIndex(c => c.Carton1 == detail.Carton1 && c.Carton2 == detail.Carton2 && c.Index == detail.Index) < 0) { subCartons.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = ctnsQty }); subGrossWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, GrossWeight = grossWeight }); subNetWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, NetWeight = netWeight }); subNetNetWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, NetNetWeight = netNetWeight }); } } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.5f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.5f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.5f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("SUMMARY", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = sizeSumQty.Where(w => w.Key == size.Key).Sum(a => a.Value); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(quantity == 0 ? "" : quantity.ToString(), normal_font, 0.5f)); tableDetail.AddCell(cellBorderBottomRight); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); if (!arraySubTotal.ContainsKey(item.Uom.Unit)) { arraySubTotal.Add(item.Uom.Unit, subTotal); } else { arraySubTotal[item.Uom.Unit] += subTotal; } grandTotal += subTotal; tableDetail.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = SIZES_COUNT + 7, Padding = 5, Phrase = new Phrase("SUB TOTAL ....................................................................................................................................................................... ", normal_font) }); cellBorderBottom.Phrase = new Phrase(subTotal.ToString() + " " + item.Uom.Unit, normal_font); cellBorderBottom.Colspan = 2; tableDetail.AddCell(cellBorderBottom); cellBorderBottom.Phrase = new Phrase("", normal_font); cellBorderBottom.Colspan = 3; tableDetail.AddCell(cellBorderBottom); cellBorderBottom.Colspan = 1; var subCtns = subCartons.Sum(c => c.CartonQuantity); var subGw = subGrossWeight.Sum(c => c.GrossWeight * c.CartonQuantity); var subNw = subNetWeight.Sum(c => c.NetWeight * c.CartonQuantity); var subNnw = subNetNetWeight.Sum(c => c.NetNetWeight * c.CartonQuantity); tableDetail.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = SIZES_COUNT + 11, Phrase = new Phrase($" - Sub Ctns = {subCtns} - Sub G.W. = {String.Format("{0:0.00}", subGw)} Kgs - Sub N.W. = {String.Format("{0:0.00}", subNw)} Kgs - Sub N.N.W. = {String.Format("{0:0.00}", subNnw)} Kgs", normal_font) }); new PdfPCell(tableDetail); tableDetail.ExtendLastRow = false; //tableDetail.KeepTogether = true; tableDetail.WidthPercentage = 95f; //tableDetail.HeaderRows = 3; document.Add(tableDetail); } #region GrandTotal PdfPTable tableGrandTotal = new PdfPTable(2); tableGrandTotal.SetWidths(new float[] { 18f + SIZES_COUNT * 1f, 5f }); PdfPCell cellHeaderLine = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = 2, Padding = 0.5f, Phrase = new Phrase("") }; tableGrandTotal.AddCell(cellHeaderLine); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Padding = 5, Phrase = new Phrase("GRAND TOTAL .......................................................................................................................................................................", normal_font) }); var grandTotalResult = string.Join(" / ", arraySubTotal.Select(x => x.Value + " " + x.Key).ToArray()); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Padding = 5, HorizontalAlignment = Element.ALIGN_CENTER, Phrase = new Phrase(grandTotalResult, normal_font) }); tableGrandTotal.AddCell(cellHeaderLine); var totalCtns = cartons.Sum(c => c.CartonQuantity); var comodities = viewModel.Items.Select(s => s.Comodity.Name.ToUpper()).Distinct(); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.NO_BORDER, Colspan = 2, Padding = 5, Phrase = new Phrase($"{totalCtns} {viewModel.SayUnit} [ {NumberToTextEN.toWords(totalCtns).Trim().ToUpper()} {viewModel.SayUnit} OF {string.Join(" AND ", comodities)}]", normal_font) }); new PdfPCell(tableGrandTotal); tableGrandTotal.ExtendLastRow = false; tableGrandTotal.WidthPercentage = 95f; tableGrandTotal.SpacingAfter = 5f; document.Add(tableGrandTotal); #endregion #region Mark PdfPTable tableMark = new PdfPTable(2); tableMark.SetWidths(new float[] { 1f, 1f }); PdfPCell cellShippingMark = new PdfPCell() { Border = Rectangle.NO_BORDER }; Chunk chunkShippingMark = new Chunk("SHIPPING MARKS", normal_font); chunkShippingMark.SetUnderline(0.5f, -1); Phrase phraseShippingMark = new Phrase(); phraseShippingMark.Add(chunkShippingMark); phraseShippingMark.Add(new Chunk(" :", normal_font)); cellShippingMark.AddElement(phraseShippingMark); cellShippingMark.AddElement(new Paragraph(viewModel.ShippingMark, normal_font)); tableMark.AddCell(cellShippingMark); PdfPCell cellSideMark = new PdfPCell() { Border = Rectangle.NO_BORDER }; Chunk chunkSideMark = new Chunk("SIDE MARKS", normal_font); chunkSideMark.SetUnderline(0.5f, -1); Phrase phraseSideMark = new Phrase(); phraseSideMark.Add(chunkSideMark); phraseSideMark.Add(new Chunk(" :", normal_font)); cellSideMark.AddElement(phraseSideMark); cellSideMark.AddElement(new Paragraph(viewModel.SideMark, normal_font) { }); tableMark.AddCell(cellSideMark); var noImage = "data:image/png;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEAA0NDQ0ODQ4QEA4UFhMWFB4bGRkbHi0gIiAiIC1EKjIqKjIqRDxJOzc7STxsVUtLVWx9aWNpfZeHh5e+tb75+f8BDQ0NDQ4NDhAQDhQWExYUHhsZGRseLSAiICIgLUQqMioqMipEPEk7NztJPGxVS0tVbH1pY2l9l4eHl761vvn5///CABEIAAoACgMBIgACEQEDEQH/xAAVAAEBAAAAAAAAAAAAAAAAAAAAB//aAAgBAQAAAACnD//EABQBAQAAAAAAAAAAAAAAAAAAAAD/2gAIAQIQAAAAf//EABQBAQAAAAAAAAAAAAAAAAAAAAD/2gAIAQMQAAAAf//EABQQAQAAAAAAAAAAAAAAAAAAACD/2gAIAQEAAT8AH//EABQRAQAAAAAAAAAAAAAAAAAAAAD/2gAIAQIBAT8Af//EABQRAQAAAAAAAAAAAAAAAAAAAAD/2gAIAQMBAT8Af//Z"; byte[] shippingMarkImage; if (String.IsNullOrEmpty(viewModel.ShippingMarkImageFile)) { viewModel.ShippingMarkImageFile = noImage; } if (IsBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile))) { shippingMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile)); Image shipMarkImage = Image.GetInstance(imgb: shippingMarkImage); if (shipMarkImage.Width > 60) { float percentage = 0.0f; percentage = 100 / shipMarkImage.Width; shipMarkImage.ScalePercent(percentage * 100); PdfPCell shipMarkImageCell = new PdfPCell(shipMarkImage); shipMarkImageCell.Border = Rectangle.NO_BORDER; tableMark.AddCell(shipMarkImageCell); } } byte[] sideMarkImage; if (String.IsNullOrEmpty(viewModel.SideMarkImageFile)) { viewModel.SideMarkImageFile = noImage; } if (IsBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile))) { sideMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile)); Image _sideMarkImage = Image.GetInstance(imgb: sideMarkImage); if (_sideMarkImage.Width > 60) { float percentage = 0.0f; percentage = 100 / _sideMarkImage.Width; _sideMarkImage.ScalePercent(percentage * 100); } PdfPCell _sideMarkImageCell = new PdfPCell(_sideMarkImage); _sideMarkImageCell.Border = Rectangle.NO_BORDER; tableMark.AddCell(_sideMarkImageCell); } new PdfPCell(tableMark); tableMark.ExtendLastRow = false; tableMark.SpacingAfter = 5f; document.Add(tableMark); #endregion #region Measurement PdfPTable tableMeasurement = new PdfPTable(3); tableMeasurement.SetWidths(new float[] { 2f, 0.2f, 12f }); PdfPCell cellMeasurement = new PdfPCell() { Border = Rectangle.NO_BORDER }; cellMeasurement.Phrase = new Phrase("GROSS WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(String.Format("{0:0.00}", viewModel.GrossWeight) + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("NET WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(String.Format("{0:0.00}", viewModel.NettWeight) + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("NET NET WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(String.Format("{0:0.00}", viewModel.NetNetWeight) + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("MEASUREMENT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); PdfPTable tableMeasurementDetail = new PdfPTable(5); tableMeasurementDetail.SetWidths(new float[] { 2f, 2f, 2f, 2f, 2f }); PdfPCell cellMeasurementDetail = new PdfPCell() { Border = Rectangle.NO_BORDER, HorizontalAlignment = Element.ALIGN_RIGHT }; decimal totalCbm = 0; foreach (var measurement in viewModel.Measurements) { cellMeasurementDetail.Phrase = new Phrase(String.Format("{0:0.00}", measurement.Length) + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(String.Format("{0:0.00}", measurement.Width) + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(String.Format("{0:0.00}", measurement.Height) + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(measurement.CartonsQuantity + " CTNS = ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); var cbm = (decimal)measurement.Length * (decimal)measurement.Width * (decimal)measurement.Height * (decimal)measurement.CartonsQuantity / 1000000; totalCbm += cbm; cellMeasurementDetail.Phrase = new Phrase(string.Format("{0:N3} CBM", cbm), normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); } cellMeasurementDetail.Border = Rectangle.TOP_BORDER; cellMeasurementDetail.Phrase = new Phrase("", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase("TOTAL", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(viewModel.Measurements.Sum(m => m.CartonsQuantity) + " CTNS .", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(string.Format("{0:N3} CBM", totalCbm), normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); new PdfPCell(tableMeasurementDetail); tableMeasurementDetail.ExtendLastRow = false; var paddingRight = SIZES_COUNT > 11 ? 400 : 200; tableMeasurement.AddCell(new PdfPCell(tableMeasurementDetail) { Border = Rectangle.NO_BORDER, PaddingRight = paddingRight }); tableMeasurement.AddCell(new PdfPCell { Border = Rectangle.NO_BORDER, Colspan = 3, Phrase = new Phrase("REMARK :", normal_font_underlined) }); tableMeasurement.AddCell(new PdfPCell { Border = Rectangle.NO_BORDER, Colspan = 3, Phrase = new Phrase(viewModel.Remark, normal_font) }); byte[] remarkImage; if (String.IsNullOrEmpty(viewModel.RemarkImageFile)) { viewModel.RemarkImageFile = noImage; } if (IsBase64String(Base64.GetBase64File(viewModel.RemarkImageFile))) { remarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.RemarkImageFile)); Image images = Image.GetInstance(imgb: remarkImage); if (images.Width > 60) { float percentage = 0.0f; percentage = 100 / images.Width; images.ScalePercent(percentage * 100); } PdfPCell imageCell = new PdfPCell(images); imageCell.Border = Rectangle.NO_BORDER; imageCell.Colspan = 3; tableMeasurement.AddCell(imageCell); } new PdfPCell(tableMeasurement); tableMeasurement.ExtendLastRow = false; tableMeasurement.SpacingAfter = 5f; document.Add(tableMeasurement); //document.Add(images); #endregion document.Close(); byte[] byteInfo = stream.ToArray(); stream.Write(byteInfo, 0, byteInfo.Length); stream.Position = 0; return(stream); }
public MemoryStream GeneratePdfTemplate(GarmentPackingListViewModel viewModel, string fob, string cprice) { //int maxSizesCount = viewModel.Items.Max(i => i.Details.Max(d => d.Sizes.GroupBy(g => g.Size.Id).Count())); var newItems = new List <GarmentPackingListItemViewModel>(); var newItems2 = new List <GarmentPackingListItemViewModel>(); var newDetails = new List <GarmentPackingListDetailViewModel>(); foreach (var item in viewModel.Items) { foreach (var detail in item.Details) { newDetails.Add(detail); } } newDetails = newDetails.OrderBy(a => a.Index).ToList(); foreach (var d in newDetails) { if (newItems.Count == 0) { var i = viewModel.Items.Single(a => a.Id == d.PackingListItemId); i.Details = new List <GarmentPackingListDetailViewModel>(); i.Details.Add(d); newItems.Add(i); } else { if (newItems.Last().Id == d.PackingListItemId) { newItems.Last().Details.Add(d); } else { var y = viewModel.Items.Select(a => new GarmentPackingListItemViewModel { Id = a.Id, RONo = a.RONo, Article = a.Article, BuyerAgent = a.BuyerAgent, ComodityDescription = a.ComodityDescription, OrderNo = a.OrderNo, AVG_GW = a.AVG_GW, AVG_NW = a.AVG_NW, Description = a.Description, Uom = a.Uom }) .Single(a => a.Id == d.PackingListItemId); y.Details = new List <GarmentPackingListDetailViewModel>(); y.Details.Add(d); newItems.Add(y); } } } foreach (var item in newItems) { if (newItems2.Count == 0) { newItems2.Add(item); } else { if (newItems2.Last().OrderNo == item.OrderNo && newItems2.Last().Description == item.Description) { foreach (var d in item.Details) { newItems2.Last().Details.Add(d); } } else { var y = viewModel.Items.Select(a => new GarmentPackingListItemViewModel { Id = a.Id, RONo = a.RONo, Article = a.Article, BuyerAgent = a.BuyerAgent, ComodityDescription = a.ComodityDescription, OrderNo = a.OrderNo, AVG_GW = a.AVG_GW, AVG_NW = a.AVG_NW, Description = a.Description, Uom = a.Uom }) .Single(a => a.Id == item.Id); y.Details = new List <GarmentPackingListDetailViewModel>(); foreach (var d in item.Details) { y.Details.Add(d); } newItems2.Add(y); } } } var sizesCount = false; foreach (var item in newItems2) { var sizesMax = new Dictionary <int, string>(); foreach (var detail in item.Details.OrderBy(o => o.Carton1).ThenBy(o => o.Carton2)) { foreach (var size in detail.Sizes) { sizesMax[size.Size.Id] = size.Size.Size; } } if (sizesMax.Count > 11) { sizesCount = true; } } int SIZES_COUNT = sizesCount ? 20 : 11; Font header_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 14); Font normal_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Font body_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Font normal_font_underlined = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8, Font.UNDERLINE); Font bold_font = FontFactory.GetFont(BaseFont.HELVETICA_BOLD, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8); Document document = new Document(sizesCount ? PageSize.A4.Rotate() : PageSize.A4, 20, 20, 170, 30); MemoryStream stream = new MemoryStream(); PdfWriter writer = PdfWriter.GetInstance(document, stream); writer.PageEvent = new GarmentPackingListPDFTemplatePageEvent(_identityProvider, viewModel); document.Open(); #region Description PdfPTable tableDescription = new PdfPTable(3); tableDescription.SetWidths(new float[] { 2f, 0.2f, 7.8f }); PdfPCell cellDescription = new PdfPCell() { Border = Rectangle.NO_BORDER }; cellDescription.Phrase = new Phrase(cprice, normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(":", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(fob, normal_font); tableDescription.AddCell(cellDescription); if (viewModel.PaymentTerm == "LC") { cellDescription.Phrase = new Phrase("LC No.", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(":", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(viewModel.LCNo, normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase("Tgl. LC", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(":", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(viewModel.LCDate.GetValueOrDefault().ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).ToString("dd MMMM yyyy"), normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase("ISSUED BY", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(":", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(viewModel.IssuedBy, normal_font); tableDescription.AddCell(cellDescription); } else { cellDescription.Phrase = new Phrase("Payment Term", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(":", normal_font); tableDescription.AddCell(cellDescription); cellDescription.Phrase = new Phrase(viewModel.PaymentTerm, normal_font); tableDescription.AddCell(cellDescription); } new PdfPCell(tableDescription); tableDescription.ExtendLastRow = false; tableDescription.SpacingAfter = 5f; document.Add(tableDescription); #endregion PdfPCell cellBorderBottomRight = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER | Rectangle.RIGHT_BORDER | Rectangle.LEFT_BORDER, HorizontalAlignment = Element.ALIGN_CENTER }; PdfPCell cellBorderBottom = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, HorizontalAlignment = Element.ALIGN_CENTER }; double totalCtns = 0; double grandTotal = 0; var uom = ""; var arrayGrandTotal = new Dictionary <String, double>(); List <string> cartonNumbers = new List <string>(); foreach (var item in newItems2) { #region Item PdfPTable tableItem = new PdfPTable(3); tableItem.SetWidths(new float[] { 2f, 0.2f, 7.8f }); PdfPCell cellItemContent = new PdfPCell() { Border = Rectangle.NO_BORDER }; cellItemContent.Phrase = new Phrase("DESCRIPTION OF GOODS", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(":", normal_font); tableItem.AddCell(cellItemContent); cellItemContent.Phrase = new Phrase(item.Description, normal_font); tableItem.AddCell(cellItemContent); new PdfPCell(tableItem); tableItem.ExtendLastRow = false; document.Add(tableItem); #endregion var sizes = new Dictionary <int, string>(); foreach (var detail in item.Details) { foreach (var size in detail.Sizes) { sizes[size.Size.Id] = size.Size.Size; } } PdfPTable tableDetail = new PdfPTable(SIZES_COUNT + (viewModel.InvoiceType == "DL" ? 11 : 8)); var width = new List <float> { 2f, 3.5f, 4f, 4f }; for (int i = 0; i < SIZES_COUNT; i++) { width.Add(1f); } if (viewModel.InvoiceType == "DL") { width.AddRange(new List <float> { 1.5f, 1f, 1.5f, 2f, 1.5f, 1.5f, 1.5f }); } else { width.AddRange(new List <float> { 1.5f, 1f, 1.5f, 2f }); } tableDetail.SetWidths(width.ToArray()); PdfPCell cellDetailLine = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = 19, Padding = 0.5f, Phrase = new Phrase("") }; tableDetail.AddCell(cellDetailLine); tableDetail.AddCell(cellDetailLine); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("CARTON NO.", normal_font, 0.75f)); cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("COLOUR", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("STYLE", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("ORDER NO.", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("S I Z E", normal_font, 0.75f)); cellBorderBottomRight.Colspan = SIZES_COUNT; cellBorderBottomRight.Rowspan = 1; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("CTNS", normal_font, 0.75f)); cellBorderBottomRight.Colspan = 1; cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("@", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("QTY", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("SATUAN", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); if (viewModel.InvoiceType == "DL") { cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("GW/\nCTN", normal_font, 0.75f)); cellBorderBottomRight.Rowspan = 2; tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("NW/\nCTN", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("NNW/\nCTN", normal_font, 0.75f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Rowspan = 1; } for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(size.Key == 0 ? "" : size.Value, normal_font, 0.5f)); cellBorderBottomRight.Rowspan = 1; tableDetail.AddCell(cellBorderBottomRight); } double subCtns = 0; double subTotal = 0; var sizeSumQty = new Dictionary <int, double>(); var arraySubTotal = new Dictionary <String, double>(); foreach (var detail in item.Details.OrderBy(o => o.Carton1).ThenBy(o => o.Carton2)) { var ctnsQty = detail.CartonQuantity; uom = viewModel.Items.Where(a => a.Id == detail.PackingListItemId).Single().Uom.Unit; var article = viewModel.Items.Where(a => a.Id == detail.PackingListItemId).Single().Article; if (cartonNumbers.Contains($"{detail.Index} - {detail.Carton1}- {detail.Carton2}")) { ctnsQty = 0; } else { cartonNumbers.Add($"{detail.Index} - {detail.Carton1}- {detail.Carton2}"); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk($"{detail.Carton1}- {detail.Carton2}", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.Colour, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.Style, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(item.OrderNo, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = detail.Sizes.Where(w => w.Size.Id == size.Key).Sum(s => s.Quantity); } if (sizeSumQty.ContainsKey(size.Key)) { sizeSumQty[size.Key] += quantity * detail.CartonQuantity; } else { sizeSumQty.Add(size.Key, quantity * detail.CartonQuantity); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(quantity == 0 ? "" : quantity.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); } subCtns += ctnsQty; cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(ctnsQty.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(detail.QuantityPCS.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); var totalQuantity = (detail.CartonQuantity * detail.QuantityPCS); subTotal += totalQuantity; if (!arraySubTotal.ContainsKey(uom)) { arraySubTotal.Add(uom, totalQuantity); } else { arraySubTotal[uom] += totalQuantity; } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(totalQuantity.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(uom, normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); if (viewModel.InvoiceType == "DL") { cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.GrossWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.NetWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(string.Format("{0:n2}", detail.NetNetWeight), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); } } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("SUMMARY", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); for (int i = 0; i < SIZES_COUNT; i++) { var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = sizeSumQty.Where(w => w.Key == size.Key).Sum(a => a.Value); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk(quantity == 0 ? "" : quantity.ToString(), normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); } cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); if (viewModel.InvoiceType == "DL") { cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); cellBorderBottomRight.Phrase = new Phrase(GetScalledChunk("", normal_font, 0.6f)); tableDetail.AddCell(cellBorderBottomRight); } totalCtns += subCtns; grandTotal += subTotal; if (!arrayGrandTotal.ContainsKey(uom)) { arrayGrandTotal.Add(uom, subTotal); } else { arrayGrandTotal[uom] += subTotal; } tableDetail.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = SIZES_COUNT + (viewModel.InvoiceType == "DL" ? 6 : 3), Padding = 5, Phrase = new Phrase("SUB TOTAL .............................................................................................................................................. ", normal_font) }); var subTotalResult = string.Join(" / ", arraySubTotal.Select(x => x.Value + " " + x.Key).ToArray()); cellBorderBottom.Phrase = new Phrase(subTotalResult, normal_font); cellBorderBottom.Colspan = 2; tableDetail.AddCell(cellBorderBottom); cellBorderBottom.Phrase = new Phrase("", normal_font); cellBorderBottom.Colspan = 3; tableDetail.AddCell(cellBorderBottom); cellBorderBottom.Colspan = 1; tableDetail.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = SIZES_COUNT + (viewModel.InvoiceType == "DL" ? 11 : 8), Phrase = new Phrase($" - Sub Ctns = {subCtns} - Sub G.W. = {String.Format("{0:0.00}", item.Details.Select(d => new { d.Index, d.Carton1, d.Carton2, TotalGrossWeight = d.CartonQuantity * d.GrossWeight }).GroupBy(g => new { g.Index, g.Carton1, g.Carton2 }, (key, value) => value.First().TotalGrossWeight).Sum())} Kgs - Sub N.W. = {String.Format("{0:0.00}", item.Details.Select(d => new { d.Index, d.Carton1, d.Carton2, TotalNetWeight = d.CartonQuantity * d.NetWeight }).GroupBy(g => new { g.Index, g.Carton1, g.Carton2 }, (key, value) => value.First().TotalNetWeight).Sum())} Kgs - Sub N.N.W. = {String.Format("{0:0.00}", item.Details.Select(d => new { d.Index, d.Carton1, d.Carton2, TotalNetNetWeight = d.CartonQuantity * d.NetNetWeight }).GroupBy(g => new { g.Index, g.Carton1, g.Carton2 }, (key, value) => value.First().TotalNetNetWeight).Sum())} Kgs", normal_font) }); cellBorderBottom.Phrase = new Phrase("", normal_font); tableDetail.AddCell(cellBorderBottom); new PdfPCell(tableDetail); tableDetail.ExtendLastRow = false; tableDetail.KeepTogether = true; tableDetail.WidthPercentage = 95f; tableDetail.SpacingAfter = 10f; document.Add(tableDetail); } #region GrandTotal PdfPTable tableGrandTotal = new PdfPTable(2); tableGrandTotal.SetWidths(new float[] { 18f + SIZES_COUNT * 1f, 3f }); PdfPCell cellHeaderLine = new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Colspan = 2, Padding = 0.5f, Phrase = new Phrase("") }; tableGrandTotal.AddCell(cellHeaderLine); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Padding = 6, Phrase = new Phrase("GRAND TOTAL ...................................................................................................................................................................................", normal_font) }); var grandTotalResult = string.Join(" / ", arrayGrandTotal.Select(x => x.Value + " " + x.Key).ToArray()); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.BOTTOM_BORDER, Padding = 4, HorizontalAlignment = Element.ALIGN_CENTER, Phrase = new Phrase(grandTotalResult, normal_font) }); tableGrandTotal.AddCell(cellHeaderLine); var comodities = viewModel.Items.Select(s => s.Comodity.Name.ToUpper()).Distinct(); tableGrandTotal.AddCell(new PdfPCell() { Border = Rectangle.NO_BORDER, Colspan = 2, Padding = 5, Phrase = new Phrase($"{totalCtns} {viewModel.SayUnit} [ {NumberToTextEN.toWords(totalCtns).Trim().ToUpper()} {viewModel.SayUnit} OF {string.Join(" AND ", viewModel.OtherCommodity)}]", normal_font) }); new PdfPCell(tableGrandTotal); tableGrandTotal.ExtendLastRow = false; tableGrandTotal.WidthPercentage = 95f; tableGrandTotal.SpacingAfter = 5f; document.Add(tableGrandTotal); #endregion #region Mark PdfPTable tableMark = new PdfPTable(2); tableMark.SetWidths(new float[] { 1f, 1f }); PdfPCell cellShippingMark = new PdfPCell() { Border = Rectangle.NO_BORDER }; Chunk chunkShippingMark = new Chunk("SHIPPING MARKS", normal_font); chunkShippingMark.SetUnderline(0.5f, -1); Phrase phraseShippingMark = new Phrase(); phraseShippingMark.Add(chunkShippingMark); phraseShippingMark.Add(new Chunk(" :", normal_font)); cellShippingMark.AddElement(phraseShippingMark); cellShippingMark.AddElement(new Paragraph(viewModel.ShippingMark, normal_font)); tableMark.AddCell(cellShippingMark); PdfPCell cellSideMark = new PdfPCell() { Border = Rectangle.NO_BORDER }; Chunk chunkSideMark = new Chunk("SIDE MARKS", normal_font); chunkSideMark.SetUnderline(0.5f, -1); Phrase phraseSideMark = new Phrase(); phraseSideMark.Add(chunkSideMark); phraseSideMark.Add(new Chunk(" :", normal_font)); cellSideMark.AddElement(phraseSideMark); cellSideMark.AddElement(new Paragraph(viewModel.SideMark, normal_font) { }); tableMark.AddCell(cellSideMark); var noImage = "data:image/png;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEAA0NDQ0ODQ4QEA4UFhMWFB4bGRkbHi0gIiAiIC1EKjIqKjIqRDxJOzc7STxsVUtLVWx9aWNpfZeHh5e+tb75+f8BDQ0NDQ4NDhAQDhQWExYUHhsZGRseLSAiICIgLUQqMioqMipEPEk7NztJPGxVS0tVbH1pY2l9l4eHl761vvn5///CABEIAAoACgMBIgACEQEDEQH/xAAVAAEBAAAAAAAAAAAAAAAAAAAAB//aAAgBAQAAAACnD//EABQBAQAAAAAAAAAAAAAAAAAAAAD/2gAIAQIQAAAAf//EABQBAQAAAAAAAAAAAAAAAAAAAAD/2gAIAQMQAAAAf//EABQQAQAAAAAAAAAAAAAAAAAAACD/2gAIAQEAAT8AH//EABQRAQAAAAAAAAAAAAAAAAAAAAD/2gAIAQIBAT8Af//EABQRAQAAAAAAAAAAAAAAAAAAAAD/2gAIAQMBAT8Af//Z"; byte[] shippingMarkImage; if (String.IsNullOrEmpty(viewModel.ShippingMarkImageFile)) { viewModel.ShippingMarkImageFile = noImage; } if (IsBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile))) { shippingMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile)); Image shipMarkImage = Image.GetInstance(imgb: shippingMarkImage); if (shipMarkImage.Width > 60) { float percentage = 0.0f; percentage = 100 / shipMarkImage.Width; shipMarkImage.ScalePercent(percentage * 100); } PdfPCell shipMarkImageCell = new PdfPCell(shipMarkImage); shipMarkImageCell.Border = Rectangle.NO_BORDER; tableMark.AddCell(shipMarkImageCell); } byte[] sideMarkImage; if (String.IsNullOrEmpty(viewModel.SideMarkImageFile)) { viewModel.SideMarkImageFile = noImage; } if (IsBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile))) { sideMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile)); Image _sideMarkImage = Image.GetInstance(imgb: sideMarkImage); if (_sideMarkImage.Width > 60) { float percentage = 0.0f; percentage = 100 / _sideMarkImage.Width; _sideMarkImage.ScalePercent(percentage * 100); } PdfPCell _sideMarkImageCell = new PdfPCell(_sideMarkImage); _sideMarkImageCell.Border = Rectangle.NO_BORDER; tableMark.AddCell(_sideMarkImageCell); } new PdfPCell(tableMark); tableMark.ExtendLastRow = false; tableMark.SpacingAfter = 5f; document.Add(tableMark); #endregion #region Measurement PdfPTable tableMeasurement = new PdfPTable(3); tableMeasurement.SetWidths(new float[] { 2f, 0.2f, 12f }); PdfPCell cellMeasurement = new PdfPCell() { Border = Rectangle.NO_BORDER }; cellMeasurement.Phrase = new Phrase("GROSS WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(String.Format("{0:0.00}", viewModel.GrossWeight) + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("NET WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(String.Format("{0:0.00}", viewModel.NettWeight) + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("NET NET WEIGHT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(String.Format("{0:0.00}", viewModel.NetNetWeight) + " KGS", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase("MEASUREMENT", normal_font); tableMeasurement.AddCell(cellMeasurement); cellMeasurement.Phrase = new Phrase(":", normal_font); tableMeasurement.AddCell(cellMeasurement); PdfPTable tableMeasurementDetail = new PdfPTable(5); tableMeasurementDetail.SetWidths(new float[] { 1f, 1f, 1f, 1.5f, 2f }); PdfPCell cellMeasurementDetail = new PdfPCell() { Border = Rectangle.NO_BORDER, HorizontalAlignment = Element.ALIGN_RIGHT }; decimal totalCbm = 0; int countMeasurement = 0; var measurements = new List <(double, double, double)>(); foreach (var measurement in viewModel.Measurements) { cellMeasurementDetail.Phrase = new Phrase(measurement.Length + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(measurement.Width + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(measurement.Height + " CM X ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(measurement.CartonsQuantity + " CTNS = ", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); var cbm = (decimal)measurement.Length * (decimal)measurement.Width * (decimal)measurement.Height * (decimal)measurement.CartonsQuantity / 1000000; totalCbm += cbm; cellMeasurementDetail.Phrase = new Phrase(string.Format("{0:N2} CBM", cbm), normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); if (measurements.Contains((measurement.Length, measurement.Width, measurement.Height)) == false) { measurements.Add((measurement.Length, measurement.Width, measurement.Height)); countMeasurement++; } } cellMeasurementDetail.Border = Rectangle.TOP_BORDER; cellMeasurementDetail.Phrase = new Phrase("", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); tableMeasurementDetail.AddCell(cellMeasurementDetail); if (countMeasurement > 1) { cellMeasurementDetail.Phrase = new Phrase("TOTAL", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(viewModel.Measurements.Sum(m => m.CartonsQuantity) + " CTNS .", normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); cellMeasurementDetail.Phrase = new Phrase(string.Format("{0:N2} CBM", totalCbm), normal_font); tableMeasurementDetail.AddCell(cellMeasurementDetail); } new PdfPCell(tableMeasurementDetail); tableMeasurementDetail.ExtendLastRow = false; var paddingRight = SIZES_COUNT > 11 ? 400 : 200; tableMeasurement.AddCell(new PdfPCell(tableMeasurementDetail) { Border = Rectangle.NO_BORDER, PaddingRight = paddingRight }); tableMeasurement.AddCell(new PdfPCell { Border = Rectangle.NO_BORDER, Colspan = 3, Phrase = new Phrase("REMARK :", normal_font_underlined) }); tableMeasurement.AddCell(new PdfPCell { Border = Rectangle.NO_BORDER, Colspan = 3, Phrase = new Phrase(viewModel.Remark, normal_font) }); byte[] remarkImage; if (String.IsNullOrEmpty(viewModel.RemarkImageFile)) { viewModel.RemarkImageFile = noImage; } if (IsBase64String(Base64.GetBase64File(viewModel.RemarkImageFile))) { remarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.RemarkImageFile)); Image images = Image.GetInstance(imgb: remarkImage); if (images.Width > 60) { float percentage = 0.0f; percentage = 100 / images.Width; images.ScalePercent(percentage * 100); } PdfPCell imageCell = new PdfPCell(images); imageCell.Border = Rectangle.NO_BORDER; imageCell.Colspan = 3; tableMeasurement.AddCell(imageCell); } new PdfPCell(tableMeasurement); tableMeasurement.ExtendLastRow = false; tableMeasurement.SpacingAfter = 5f; document.Add(tableMeasurement); #endregion #region sign PdfPTable tableSign = new PdfPTable(3); tableSign.WidthPercentage = 100; tableSign.SetWidths(new float[] { 1f, 1f, 1f }); PdfPCell cellBodySignNoBorder = new PdfPCell() { Border = Rectangle.NO_BORDER, HorizontalAlignment = Element.ALIGN_CENTER }; cellBodySignNoBorder.Phrase = new Phrase("", normal_font); tableSign.AddCell(cellBodySignNoBorder); cellBodySignNoBorder.Phrase = new Phrase("", normal_font); tableSign.AddCell(cellBodySignNoBorder); cellBodySignNoBorder.Phrase = new Phrase("\n\n\n\n", normal_font); tableSign.AddCell(cellBodySignNoBorder); cellBodySignNoBorder.Phrase = new Phrase("", normal_font); tableSign.AddCell(cellBodySignNoBorder); cellBodySignNoBorder.Phrase = new Phrase("", normal_font); tableSign.AddCell(cellBodySignNoBorder); cellBodySignNoBorder.Phrase = new Phrase("( MRS. ADRIYANA DAMAYANTI )", normal_font); tableSign.AddCell(cellBodySignNoBorder); cellBodySignNoBorder.Phrase = new Phrase("", normal_font); tableSign.AddCell(cellBodySignNoBorder); cellBodySignNoBorder.Phrase = new Phrase("", normal_font); tableSign.AddCell(cellBodySignNoBorder); cellBodySignNoBorder.Phrase = new Phrase("AUTHORIZED SIGNATURE", normal_font_underlined); tableSign.AddCell(cellBodySignNoBorder); document.Add(tableSign); #endregion document.Close(); byte[] byteInfo = stream.ToArray(); stream.Write(byteInfo, 0, byteInfo.Length); stream.Position = 0; return(stream); }
public GarmentPackingListPDFTemplatePageEvent(IIdentityProvider identityProvider, GarmentPackingListViewModel viewModel) { this.identityProvider = identityProvider; this.viewModel = viewModel; }
public MemoryStream GenerateExcelTemplate(GarmentPackingListViewModel viewModel) { //int maxSizesCount = viewModel.Items == null || viewModel.Items.Count < 1 ? 0 : viewModel.Items.Max(i => i.Details == null || i.Details.Count < 1 ? 0 : i.Details.Max(d => d.Sizes == null || d.Sizes.Count < 1 ? 0 : d.Sizes.GroupBy(g => g.Size.Id).Count())); var newItems = new List <GarmentPackingListItemViewModel>(); var newItems2 = new List <GarmentPackingListItemViewModel>(); var newDetails = new List <GarmentPackingListDetailViewModel>(); foreach (var item in viewModel.Items.OrderBy(a => a.RONo)) { foreach (var detail in item.Details) { newDetails.Add(detail); } } newDetails = newDetails.OrderBy(a => a.Carton1).ToList(); foreach (var d in newDetails) { if (newItems.Count == 0) { var i = viewModel.Items.Single(a => a.Id == d.PackingListItemId); i.Details = new List <GarmentPackingListDetailViewModel>(); i.Details.Add(d); newItems.Add(i); } else { if (newItems.Last().Id == d.PackingListItemId) { newItems.Last().Details.Add(d); } else { var y = viewModel.Items.OrderBy(o => o.RONo).Select(a => new GarmentPackingListItemViewModel { Id = a.Id, RONo = a.RONo, Article = a.Article, BuyerAgent = a.BuyerAgent, ComodityDescription = a.ComodityDescription, OrderNo = a.OrderNo, AVG_GW = a.AVG_GW, AVG_NW = a.AVG_NW, Uom = a.Uom }) .Single(a => a.Id == d.PackingListItemId); y.Details = new List <GarmentPackingListDetailViewModel>(); y.Details.Add(d); newItems.Add(y); } } } foreach (var item in newItems) { if (newItems2.Count == 0) { newItems2.Add(item); } else { if (newItems2.Last().RONo == item.RONo && newItems2.Last().OrderNo == item.OrderNo) { foreach (var d in item.Details.OrderBy(a => a.Carton1)) { newItems2.Last().Details.Add(d); } } else { newItems2.Add(item); } } } var sizesCount = false; foreach (var item in newItems2) { var sizesMax = new Dictionary <int, string>(); foreach (var detail in item.Details) { foreach (var size in detail.Sizes) { sizesMax[size.Size.Id] = size.Size.Size; } } if (sizesMax.Count > 11) { sizesCount = true; } } int SIZES_COUNT = sizesCount ? 20 : 11; var col = GetColNameFromIndex(SIZES_COUNT + 4); var colCtns = GetColNameFromIndex(SIZES_COUNT + 5); var colPcs = GetColNameFromIndex(SIZES_COUNT + 6); var colQty = GetColNameFromIndex(SIZES_COUNT + 7); var colSatuan = GetColNameFromIndex(SIZES_COUNT + 8); var colGw = GetColNameFromIndex(SIZES_COUNT + 9); var colNw = GetColNameFromIndex(SIZES_COUNT + 10); var colNnw = GetColNameFromIndex(SIZES_COUNT + 11); DataTable result = new DataTable(); ExcelPackage package = new ExcelPackage(); var sheet = package.Workbook.Worksheets.Add("Report"); sheet.Cells.Style.Font.SetFromFont(new Font("Tahoma", 7, FontStyle.Regular)); sheet.Cells["A1"].Value = "DRAFT PACKING LIST"; sheet.Cells[$"A1:{colNnw}1"].Merge = true; sheet.Cells[$"A1:{colNnw}1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells["A1"].Style.Font.SetFromFont(new Font("Helvetica", 25)); sheet.Cells["A1"].Style.Font.Bold = true; sheet.Row(1).Height = 30; sheet.Cells["A2"].Value = "Invoice No."; sheet.Cells["A2:B2"].Merge = true; sheet.Cells["C2"].Value = ":"; sheet.Cells["D2"].Value = viewModel.InvoiceNo; sheet.Cells["D2:E2"].Merge = true; sheet.Cells[$"{colCtns}2"].Value = "Date"; sheet.Cells[$"{colPcs}2"].Value = ":"; sheet.Cells[$"{colQty}2"].Value = viewModel.CreatedUtc.ToString("MMM dd, yyyy."); sheet.Cells[$"{colQty}2:{colNnw}2"].Merge = true; sheet.Cells[$"A2:{colNnw}2"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick; sheet.Cells["A4"].Value = "SHIPPING METHOD"; sheet.Cells["A4:B4"].Merge = true; sheet.Cells["C4"].Value = ":"; sheet.Cells["D4"].Value = viewModel.ShipmentMode; sheet.Cells["D4:E4"].Merge = true; var cartons = new List <GarmentPackingListDetailViewModel>(); double grandTotal = 0; var arraySubTotal = new Dictionary <String, double>(); List <string> cartonNumbers = new List <string>(); var index = 8; var indexHeader = 5; var afterSubTotalIndex = 0; foreach (var item in newItems2) { var sizeIndex = index + 1; var valueIndex = sizeIndex + 1; #region Item sheet.Cells[$"A{indexHeader}"].Value = "RO No"; sheet.Cells[$"A{indexHeader}:B{indexHeader}"].Merge = true; sheet.Cells[$"C{indexHeader}"].Value = ":"; sheet.Cells[$"D{indexHeader}"].Value = item.RONo; sheet.Cells[$"D{indexHeader}:H{indexHeader}"].Merge = true; sheet.Cells[$"I{indexHeader}"].Value = "ARTICLE"; sheet.Cells[$"I{indexHeader}:O{indexHeader}"].Merge = true; sheet.Cells[$"P{indexHeader}"].Value = ":"; sheet.Cells[$"Q{indexHeader}"].Value = item.Article; sheet.Cells[$"Q{indexHeader}:{colNnw}{indexHeader}"].Merge = true; sheet.Cells[$"A{indexHeader + 1}"].Value = "BUYER"; sheet.Cells[$"A{indexHeader + 1}:B{indexHeader + 1}"].Merge = true; sheet.Cells[$"C{indexHeader + 1}"].Value = ":"; sheet.Cells[$"D{indexHeader + 1}"].Value = viewModel.BuyerAgent.Name; sheet.Cells[$"D{indexHeader + 1}:H{indexHeader + 1}"].Merge = true; sheet.Cells[$"I{indexHeader + 1}"].Value = "DESCRIPTION OF GOODS"; sheet.Cells[$"I{indexHeader + 1}:O{indexHeader + 1}"].Merge = true; sheet.Cells[$"P{indexHeader + 1}"].Value = ":"; sheet.Cells[$"Q{indexHeader + 1}"].Value = item.ComodityDescription; sheet.Cells[$"Q{indexHeader + 1}:{colNnw}{indexHeader + 1}"].Merge = true; #endregion var sizes = new Dictionary <int, string>(); foreach (var detail in item.Details) { foreach (var size in detail.Sizes) { sizes[size.Size.Id] = size.Size.Size; } } sheet.Cells[$"A{index}"].Value = "CARTON NO."; sheet.Cells[$"A{index}:A{index + 1}"].Merge = true; sheet.Cells[$"A{index}:A{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"A{index}:A{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"A{index}:{colNnw}{index}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double; sheet.Cells[$"A{index}:{colNnw}{index}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[$"A{index}:A{index + 1}"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[$"A{index}:{colNnw}{index + 1}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Column(GetColNumberFromName("A")).Width = 8; sheet.Cells[$"B{index}"].Value = "COLOUR"; sheet.Cells[$"B{index}:B{index + 1}"].Merge = true; sheet.Cells[$"B{index}:B{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"B{index}:B{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Column(GetColNumberFromName("B")).Width = 5; sheet.Cells[$"C{index}"].Value = "STYLE"; sheet.Cells[$"C{index}:C{index + 1}"].Merge = true; sheet.Cells[$"C{index}:C{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"C{index}:C{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Column(GetColNumberFromName("C")).Width = 5; sheet.Cells[$"D{index}"].Value = "ORDER NO."; sheet.Cells[$"D{index}:D{index + 1}"].Merge = true; sheet.Cells[$"D{index}:D{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"D{index}:D{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Column(GetColNumberFromName("D")).Width = 7; sheet.Cells[$"E{index}"].Value = "SIZE"; sheet.Cells[$"E{index}:{col}{index}"].Merge = true; sheet.Cells[$"E{index}:{col}{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colCtns}{index}"].Value = "CTNS"; sheet.Column(GetColNumberFromName(colCtns)).Width = 5; sheet.Cells[$"{colCtns}{index}:{colCtns}{index + 1}"].Merge = true; sheet.Cells[$"{colCtns}{index}:{colCtns}{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colCtns}{index}:{colCtns}{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colPcs}{index}"].Value = "@"; sheet.Column(GetColNumberFromName(colPcs)).Width = 5; sheet.Cells[$"{colPcs}{index}:{colPcs}{index + 1}"].Merge = true; sheet.Cells[$"{colPcs}{index}:{colPcs}{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colPcs}{index}:{colPcs}{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colQty}{index}"].Value = "QTY"; sheet.Column(GetColNumberFromName(colQty)).Width = 4; sheet.Cells[$"{colQty}{index}:{colQty}{index + 1}"].Merge = true; sheet.Cells[$"{colQty}{index}:{colQty}{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colQty}{index}:{colQty}{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colSatuan}{index}"].Value = "SATUAN"; sheet.Column(GetColNumberFromName(colSatuan)).Width = 4; sheet.Cells[$"{colSatuan}{index}:{colSatuan}{index + 1}"].Merge = true; sheet.Cells[$"{colSatuan}{index}:{colSatuan}{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colSatuan}{index}:{colSatuan}{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colGw}{index}"].Value = "GW"; sheet.Column(GetColNumberFromName(colGw)).Width = 4; sheet.Cells[$"{colGw}{index}:{colGw}{index + 1}"].Merge = true; sheet.Cells[$"{colGw}{index}:{colGw}{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colGw}{index}:{colGw}{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colNw}{index}"].Value = "NW"; sheet.Column(GetColNumberFromName(colNw)).Width = 4; sheet.Cells[$"{colNw}{index}:{colNw}{index + 1}"].Merge = true; sheet.Cells[$"{colNw}{index}:{colNw}{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colNw}{index}:{colNw}{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colNnw}{index}"].Value = "NNW"; sheet.Column(GetColNumberFromName(colNnw)).Width = 4; sheet.Cells[$"{colNnw}{index}:{colNnw}{index + 1}"].Merge = true; sheet.Cells[$"{colNnw}{index}:{colNnw}{index}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[$"{colNnw}{index}:{colNnw}{index}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; for (int i = 0; i < SIZES_COUNT; i++) { var colSize = GetColNameFromIndex(5 + i); var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); sheet.Cells[$"{colSize}{sizeIndex}"].Value = size.Key == 0 ? "" : size.Value; sheet.Cells[$"{colSize}{sizeIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; } var subCartons = new List <GarmentPackingListDetailViewModel>(); var subGrossWeight = new List <GarmentPackingListDetailViewModel>(); var subNetWeight = new List <GarmentPackingListDetailViewModel>(); var subNetNetWeight = new List <GarmentPackingListDetailViewModel>(); double subTotal = 0; var sizeSumQty = new Dictionary <int, double>(); foreach (var detail in item.Details) { var ctnsQty = detail.CartonQuantity; var grossWeight = detail.GrossWeight; var netWeight = detail.NetWeight; var netNetWeight = detail.NetNetWeight; if (cartonNumbers.Contains($"{detail.Index}-{detail.Carton1}- {detail.Carton2}")) { ctnsQty = 0; grossWeight = 0; netWeight = 0; netNetWeight = 0; } else { cartonNumbers.Add($"{detail.Index}-{detail.Carton1}- {detail.Carton2}"); } sheet.Cells[$"A{valueIndex}"].Value = $"{detail.Carton1}- {detail.Carton2}"; sheet.Cells[$"A{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"B{valueIndex}"].Value = detail.Colour; sheet.Cells[$"B{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"C{valueIndex}"].Value = detail.Style; sheet.Cells[$"C{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"D{valueIndex}"].Value = item.OrderNo; sheet.Cells[$"D{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; for (int i = 0; i < SIZES_COUNT; i++) { var colSize = GetColNameFromIndex(5 + i); var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = detail.Sizes.Where(w => w.Size.Id == size.Key).Sum(s => s.Quantity); } if (sizeSumQty.ContainsKey(size.Key)) { sizeSumQty[size.Key] += quantity * detail.CartonQuantity; } else { sizeSumQty.Add(size.Key, quantity * detail.CartonQuantity); } sheet.Cells[$"{colSize}{valueIndex}"].Value = quantity == 0 ? "" : quantity.ToString(); sheet.Column(GetColNumberFromName(colSize)).Width = 3.5; sheet.Cells[$"{colSize}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; } sheet.Cells[$"{colCtns}{valueIndex}"].Value = ctnsQty.ToString(); sheet.Cells[$"{colCtns}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colPcs}{valueIndex}"].Value = detail.QuantityPCS.ToString(); sheet.Cells[$"{colPcs}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; var totalQuantity = (detail.CartonQuantity * detail.QuantityPCS); subTotal += totalQuantity; sheet.Cells[$"{colQty}{valueIndex}"].Value = totalQuantity.ToString(); sheet.Cells[$"{colQty}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colSatuan}{valueIndex}"].Value = item.Uom.Unit; sheet.Cells[$"{colSatuan}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colGw}{valueIndex}"].Value = string.Format("{0:n2}", detail.GrossWeight); sheet.Cells[$"{colGw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colNw}{valueIndex}"].Value = string.Format("{0:n2}", detail.NetWeight); sheet.Cells[$"{colNw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colNnw}{valueIndex}"].Value = string.Format("{0:n2}", detail.NetNetWeight); sheet.Cells[$"{colNnw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; if (cartons.FindIndex(c => c.Carton1 == detail.Carton1 && c.Carton2 == detail.Carton2 && c.Index == detail.Index) < 0) { cartons.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = ctnsQty }); } if (subCartons.FindIndex(c => c.Carton1 == detail.Carton1 && c.Carton2 == detail.Carton2 && c.Index == detail.Index) < 0) { subCartons.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = ctnsQty }); subGrossWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, GrossWeight = grossWeight }); subNetWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, NetWeight = netWeight }); subNetNetWeight.Add(new GarmentPackingListDetailViewModel { Carton1 = detail.Carton1, Carton2 = detail.Carton2, CartonQuantity = detail.CartonQuantity, NetNetWeight = netNetWeight }); } valueIndex++; } var sumValueIndex = 0; for (int i = 0; i < SIZES_COUNT; i++) { var colSize = GetColNameFromIndex(5 + i); sumValueIndex = valueIndex + 1; var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i); double quantity = 0; if (size.Key != 0) { quantity = sizeSumQty.Where(w => w.Key == size.Key).Sum(a => a.Value); } sheet.Cells[$"D{valueIndex}"].Value = "SUMMARY"; sheet.Cells[$"D{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colSize}{valueIndex}"].Value = quantity == 0 ? "" : quantity.ToString(); } sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; if (!arraySubTotal.ContainsKey(item.Uom.Unit)) { arraySubTotal.Add(item.Uom.Unit, subTotal); } else { arraySubTotal[item.Uom.Unit] += subTotal; } grandTotal += subTotal; sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Merge = true; sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Value = "SUB TOTAL"; sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"{colQty}{sumValueIndex}"].Value = subTotal.ToString() + " " + item.Uom.Unit; sheet.Cells[$"{colQty}{sumValueIndex}:{colNnw}{sumValueIndex}"].Merge = true; sheet.Cells[$"{colQty}{sumValueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"A{sumValueIndex}:{colNnw}{sumValueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); sheet.Cells[$"A{sumValueIndex}:{colNnw}{sumValueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; afterSubTotalIndex = sumValueIndex + 1; var subCtns = subCartons.Sum(c => c.CartonQuantity); var subGw = subGrossWeight.Sum(c => c.GrossWeight * c.CartonQuantity); var subNw = subNetWeight.Sum(c => c.NetWeight * c.CartonQuantity); var subNnw = subNetNetWeight.Sum(c => c.NetNetWeight * c.CartonQuantity); sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Merge = true; sheet.Cells[$"A{afterSubTotalIndex}"].Value = $" - Sub Ctns = {subCtns} - Sub G.W. = {String.Format("{0:0.00}", subGw)} Kgs - Sub N.W. = {String.Format("{0:0.00}", subNw)} Kgs - Sub N.N.W. = {String.Format("{0:0.00}", subNnw)} Kgs"; sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; index = afterSubTotalIndex + 3; indexHeader = afterSubTotalIndex + 1; } #region GrandTotal var grandTotalIndex = afterSubTotalIndex + 2; sheet.Cells[$"A{grandTotalIndex}:{colPcs}{grandTotalIndex}"].Merge = true; sheet.Cells[$"A{grandTotalIndex}:{colPcs}{grandTotalIndex}"].Value = "GRAND TOTAL"; sheet.Cells[$"A{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double; sheet.Cells[$"A{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double; var grandTotalResult = string.Join(" / ", arraySubTotal.Select(x => x.Value + " " + x.Key).ToArray()); sheet.Cells[$"{colQty}{grandTotalIndex}"].Value = grandTotalResult; sheet.Cells[$"{colQty}{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Merge = true; sheet.Cells[$"{colQty}{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; var totalCtns = cartons.Sum(c => c.CartonQuantity); var comodities = viewModel.Items.Select(s => s.Comodity.Name.ToUpper()).Distinct(); var spellingWordIndex = grandTotalIndex + 2; sheet.Cells[$"A{spellingWordIndex}:{colNnw}{spellingWordIndex}"].Merge = true; sheet.Cells[$"A{spellingWordIndex}"].Value = $"{totalCtns} {viewModel.SayUnit} [ {NumberToTextEN.toWords(totalCtns).Trim().ToUpper()} {viewModel.SayUnit} OF {string.Join(" AND ", comodities)}]"; for (int i = 8; i < grandTotalIndex; i++) { sheet.Row(i).Height = 16; } #endregion #region Mark var shippingMarkIndex = spellingWordIndex + 2; var sideMarkIndex = spellingWordIndex + 2; sheet.Cells[$"A{shippingMarkIndex}"].Value = "SHIPPING MARKS"; sheet.Cells[$"A{shippingMarkIndex}:B{shippingMarkIndex}"].Merge = true; sheet.Cells[$"A{++shippingMarkIndex}"].Value = viewModel.ShippingMark; sheet.Cells[$"H{sideMarkIndex}"].Value = "SIDE MARKS"; sheet.Cells[$"H{sideMarkIndex}:J{sideMarkIndex}"].Merge = true; sheet.Cells[$"H{++sideMarkIndex}"].Value = viewModel.SideMark; byte[] shippingMarkImage; if (!String.IsNullOrEmpty(viewModel.ShippingMarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile))) { shippingMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile)); Image shipMarkImage = byteArrayToImage(shippingMarkImage); var imageShippingMarkIndex = shippingMarkIndex + 1; ExcelPicture excelPictureShipMarkImage = sheet.Drawings.AddPicture("ShippingMarkImage", shipMarkImage); excelPictureShipMarkImage.From.Column = 0; excelPictureShipMarkImage.From.Row = imageShippingMarkIndex; excelPictureShipMarkImage.SetSize(200, 200); } } byte[] sideMarkImage; if (!String.IsNullOrEmpty(viewModel.SideMarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile))) { sideMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile)); Image _sideMarkImage = byteArrayToImage(sideMarkImage); var sideMarkImageIndex = sideMarkIndex + 1; ExcelPicture excelPictureSideMarkImage = sheet.Drawings.AddPicture("SideMarkImage", _sideMarkImage); excelPictureSideMarkImage.From.Column = 7; excelPictureSideMarkImage.From.Row = sideMarkImageIndex; excelPictureSideMarkImage.SetSize(200, 200); } } #endregion #region Measurement var grossWeightIndex = shippingMarkIndex + 13; var netWeightIndex = grossWeightIndex + 1; var netNetWeightIndex = netWeightIndex + 1; var measurementIndex = netNetWeightIndex + 1; sheet.Cells[$"A{grossWeightIndex}"].Value = "GROSS WEIGHT"; sheet.Cells[$"A{grossWeightIndex}:B{grossWeightIndex}"].Merge = true; sheet.Cells[$"C{grossWeightIndex}"].Value = ":"; sheet.Cells[$"D{grossWeightIndex}"].Value = viewModel.GrossWeight + " KGS"; sheet.Cells[$"A{netWeightIndex}"].Value = "NET WEIGHT"; sheet.Cells[$"A{netWeightIndex}:B{netWeightIndex}"].Merge = true; sheet.Cells[$"C{netWeightIndex}"].Value = ":"; sheet.Cells[$"D{netWeightIndex}"].Value = viewModel.NettWeight + " KGS"; sheet.Cells[$"A{netNetWeightIndex}"].Value = "NET NET WEIGHT"; sheet.Cells[$"A{netNetWeightIndex}:B{netNetWeightIndex}"].Merge = true; sheet.Cells[$"C{netNetWeightIndex}"].Value = ":"; sheet.Cells[$"D{netNetWeightIndex}"].Value = viewModel.NetNetWeight + " KGS"; sheet.Cells[$"A{measurementIndex}"].Value = "MEASUREMENT"; sheet.Cells[$"A{measurementIndex}:B{measurementIndex}"].Merge = true; sheet.Cells[$"C{measurementIndex}"].Value = ":"; decimal totalCbm = 0; foreach (var measurement in viewModel.Measurements) { sheet.Cells[$"D{measurementIndex}"].Value = measurement.Length + " X "; sheet.Cells[$"D{measurementIndex}:E{measurementIndex}"].Merge = true; sheet.Cells[$"F{measurementIndex}"].Value = measurement.Width + " X "; sheet.Cells[$"F{measurementIndex}:G{measurementIndex}"].Merge = true; sheet.Cells[$"H{measurementIndex}"].Value = measurement.Height + " X "; sheet.Cells[$"H{measurementIndex}:I{measurementIndex}"].Merge = true; sheet.Cells[$"J{measurementIndex}"].Value = measurement.CartonsQuantity + " CTNS"; sheet.Cells[$"J{measurementIndex}:K{measurementIndex}"].Merge = true; sheet.Cells[$"L{measurementIndex}"].Value = "="; var cbm = (decimal)measurement.Length * (decimal)measurement.Width * (decimal)measurement.Height * (decimal)measurement.CartonsQuantity / 1000000; totalCbm += cbm; sheet.Cells[$"M{measurementIndex}"].Value = string.Format("{0:N3} CBM", cbm); sheet.Cells[$"M{measurementIndex}:O{measurementIndex}"].Merge = true; measurementIndex++; } var totalMeasurementIndex = measurementIndex; sheet.Cells[$"D{totalMeasurementIndex}:O{totalMeasurementIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[$"D{totalMeasurementIndex}"].Value = "TOTAL"; sheet.Cells[$"D{totalMeasurementIndex}:I{totalMeasurementIndex}"].Merge = true; sheet.Cells[$"J{totalMeasurementIndex}"].Value = viewModel.Measurements.Sum(m => m.CartonsQuantity) + " CTNS ."; sheet.Cells[$"J{totalMeasurementIndex}:K{totalMeasurementIndex}"].Merge = true; sheet.Cells[$"M{totalMeasurementIndex}"].Value = string.Format("{0:N3} CBM", totalCbm); sheet.Cells[$"L{totalMeasurementIndex}"].Value = "="; sheet.Cells[$"M{totalMeasurementIndex}:O{totalMeasurementIndex}"].Merge = true; #endregion #region remark var remarkIndex = totalMeasurementIndex + 1; sheet.Cells[$"A{remarkIndex}"].Value = "REMARK"; sheet.Cells[$"A{++remarkIndex}"].Value = viewModel.Remark; byte[] remarkImage; var remarkImageIndex = remarkIndex + 1; if (!String.IsNullOrEmpty(viewModel.RemarkImageFile)) { if (IsBase64String(Base64.GetBase64File(viewModel.RemarkImageFile))) { remarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.RemarkImageFile)); Image _remarkImage = byteArrayToImage(remarkImage); ExcelPicture excelPictureRemarkImage = sheet.Drawings.AddPicture("RemarkImage", _remarkImage); excelPictureRemarkImage.From.Column = 0; excelPictureRemarkImage.From.Row = remarkImageIndex; excelPictureRemarkImage.SetSize(200, 200); } } #endregion sheet.Cells.Style.WrapText = true; sheet.PrinterSettings.LeftMargin = 0.39M; sheet.PrinterSettings.TopMargin = 0; sheet.PrinterSettings.RightMargin = 0; sheet.PrinterSettings.Orientation = sizesCount ? eOrientation.Landscape : eOrientation.Portrait; MemoryStream stream = new MemoryStream(); package.DoAdjustDrawings = false; package.SaveAs(stream); return(stream); }