//public Tuple<List<UnitReceiptNoteViewModel>, int> GetReports(string no, string unit, string category, DateTime? dateFrom, DateTime? dateTo)
        //{
        //    List<FilterDefinition<BsonDocument>> filter = new List<FilterDefinition<BsonDocument>>
        //    {
        //        filterBuilder.Eq("_deleted", false),
        //        filterBuilder.Eq("supplier.import", true)
        //    };

        //    if (no != null)
        //        filter.Add(filterBuilder.Eq("no", no));
        //    if (unit != null)
        //        filter.Add(filterBuilder.Eq("unit.code", unit));
        //    if (category != null)
        //        filter.Add(filterBuilder.Eq("items.purchaseOrder.category.code", category));
        //    if (dateFrom != null && dateTo != null)
        //        filter.Add(filterBuilder.And(filterBuilder.Gte("date", dateFrom), filterBuilder.Lte("date", dateTo)));

        //    List<BsonDocument> ListData = collection.Find(filterBuilder.And(filter)).ToList();
        //    //List<BsonDocument> ListData = collection.Aggregate()
        //    //    .Match(filterBuilder.And(filter))
        //    //    .ToList();

        //    List<UnitReceiptNoteViewModel> Data = new List<UnitReceiptNoteViewModel>();

        //    foreach (var data in ListData)
        //    {
        //        List<UnitReceiptNoteItemViewModel> Items = new List<UnitReceiptNoteItemViewModel>();
        //        foreach (var item in data.GetValue("items").AsBsonArray)
        //        {
        //            var itemDocument = item.AsBsonDocument;
        //            Items.Add(new UnitReceiptNoteItemViewModel
        //            {
        //                deliveredQuantity = GetBsonValue.ToDouble(itemDocument, "deliveredQuantity"),
        //                pricePerDealUnit = GetBsonValue.ToDouble(itemDocument, "pricePerDealUnit"),
        //                currencyRate = GetBsonValue.ToDouble(itemDocument, "currencyRate"),
        //                product = new ProductViewModel
        //                {
        //                    name = GetBsonValue.ToString(itemDocument, "product.name")
        //                },
        //                purchaseOrder = new PurchaseOrderViewModel
        //                {
        //                    category = new CategoryViewModel
        //                    {
        //                        name = GetBsonValue.ToString(itemDocument, "purchaseOrder.category.code")
        //                    }
        //                },
        //            });
        //        }
        //        var UnitReceiptNoteNo = GetBsonValue.ToString(data, "no");
        //        var dataUnitPaymentOrder = collectionUnitPaymentOrder.Find(filterBuilder.Eq("items.unitReceiptNote.no", UnitReceiptNoteNo)).FirstOrDefault();
        //        Data.Add(new UnitReceiptNoteViewModel
        //        {
        //            no = UnitReceiptNoteNo,
        //            date = data.GetValue("date").ToUniversalTime(),
        //            unit = new UnitViewModel
        //            {
        //                name = GetBsonValue.ToString(data, "unit.name")
        //            },
        //            pibNo = dataUnitPaymentOrder != null ? GetBsonValue.ToString(dataUnitPaymentOrder, "pibNo", new BsonString("-")) : "-",
        //            items = Items,
        //        });
        //    }

        //    return Tuple.Create(Data, Data.Count);
        //}

        //// JSON ora iso nge-cast
        //public Tuple<List<BsonDocument>, int> GetReport()
        //{
        //    IMongoCollection<BsonDocument> collection = new MongoDbContext().UnitReceiptNote;
        //    List<BsonDocument> ListData = collection.Aggregate().ToList();

        //    return Tuple.Create(ListData, ListData.Count);
        //}

        #endregion

        public MemoryStream GenerateExcel(string no, string unit, string category, DateTime?dateFrom, DateTime?dateTo)
        {
            Tuple <List <UnitReceiptNoteViewModel>, int> Data = this.GetReport(no, unit, category, dateFrom, dateTo);

            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "TGL", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NOMOR NOTA", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NAMA BARANG", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "TIPE", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "UNIT", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NO PIB", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NILAI", DataType = typeof(double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "RATE", DataType = typeof(double)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "TOTAL", DataType = typeof(double)
            });

            List <(string, Enum, Enum)> mergeCells = new List <(string, Enum, Enum)>()
            {
            };

            if (Data.Item2 == 0)
            {
                result.Rows.Add("", "", "", "", "", "", 0, 0, 0); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                Dictionary <string, List <UnitReceiptNoteViewModel> > dataByCategory = new Dictionary <string, List <UnitReceiptNoteViewModel> >();
                Dictionary <string, double> subTotalCategory = new Dictionary <string, double>();

                foreach (UnitReceiptNoteViewModel data in Data.Item1)
                {
                    foreach (UnitReceiptNoteItemViewModel item in data.items)
                    {
                        string categoryName = item.purchaseOrder.category.name;

                        if (!dataByCategory.ContainsKey(categoryName))
                        {
                            dataByCategory.Add(categoryName, new List <UnitReceiptNoteViewModel> {
                            });
                        }
                        dataByCategory[categoryName].Add(new UnitReceiptNoteViewModel
                        {
                            no    = data.no,
                            date  = data.date,
                            pibNo = data.pibNo,
                            unit  = data.unit,
                            items = new List <UnitReceiptNoteItemViewModel>()
                            {
                                item
                            }
                        });

                        if (!subTotalCategory.ContainsKey(categoryName))
                        {
                            subTotalCategory.Add(categoryName, 0);
                        }
                        subTotalCategory[categoryName] += item.deliveredQuantity;
                    }
                }

                double total       = 0;
                int    rowPosition = 1;

                foreach (KeyValuePair <string, List <UnitReceiptNoteViewModel> > categoryName in dataByCategory)
                {
                    foreach (UnitReceiptNoteViewModel data in categoryName.Value)
                    {
                        UnitReceiptNoteItemViewModel item = data.items[0];
                        result.Rows.Add(data.date.ToString("dd MMM yyyy", new CultureInfo("id-ID")), data.no, item.product.name, item.purchaseOrder.category.name, data.unit.name, data.pibNo, item.pricePerDealUnit * item.deliveredQuantity, item.currencyRate, item.pricePerDealUnit * item.deliveredQuantity * item.currencyRate);
                        rowPosition += 1;
                    }
                    result.Rows.Add("SUB TOTAL", "", "", "", "", "", 0, 0, subTotalCategory[categoryName.Key]);
                    rowPosition += 1;

                    mergeCells.Add(($"A{rowPosition}:H{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Right, OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom));

                    total += subTotalCategory[categoryName.Key];
                }
                result.Rows.Add("TOTAL", "", "", "", "", "", 0, 0, total);
                rowPosition += 1;

                mergeCells.Add(($"A{rowPosition}:H{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Right, OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom));
            }

            return(Excel.CreateExcel(new List <(DataTable, string, List <(string, Enum, Enum)>)>()
            {
                (result, "Report", mergeCells)
            }, true));
        }
        public MemoryStream GeneratePdfTemplate(UnitReceiptNoteViewModel viewModel)
        {
            Font header_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 12);
            Font normal_font = FontFactory.GetFont(BaseFont.HELVETICA, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8);
            Font bold_font   = FontFactory.GetFont(BaseFont.HELVETICA_BOLD, BaseFont.CP1250, BaseFont.NOT_EMBEDDED, 8);

            Document     document = new Document(PageSize.A6.Rotate(), 15, 15, 15, 15);
            MemoryStream stream   = new MemoryStream();
            PdfWriter    writer   = PdfWriter.GetInstance(document, stream);

            document.Open();

            #region Header

            string    titleString = "BON PENERIMAAN BARANG";
            Paragraph title       = new Paragraph(titleString, bold_font)
            {
                Alignment = Element.ALIGN_CENTER
            };
            document.Add(title);

            string    companyNameString = "PT. MULTIYASA ABADI SENTOSA";
            Paragraph companyName       = new Paragraph(companyNameString, header_font)
            {
                Alignment = Element.ALIGN_LEFT
            };
            document.Add(companyName);

            PdfPTable tableHeader = new PdfPTable(2);
            tableHeader.SetWidths(new float[] { 4f, 4f });
            PdfPCell cellHeaderContentLeft = new PdfPCell()
            {
                Border = Rectangle.NO_BORDER, HorizontalAlignment = Element.ALIGN_LEFT
            };
            PdfPCell cellHeaderContentRight = new PdfPCell()
            {
                Border = Rectangle.NO_BORDER, HorizontalAlignment = Element.ALIGN_RIGHT
            };

            cellHeaderContentLeft.Phrase = new Phrase("BANARAN, GROGOL, SUKOHARJO", bold_font);
            tableHeader.AddCell(cellHeaderContentLeft);

            //cellHeaderContentRight.Phrase = new Phrase("FM-PB-00-06-010/R1", bold_font);
            //tableHeader.AddCell(cellHeaderContentRight);

            PdfPCell cellHeader = new PdfPCell(tableHeader);
            tableHeader.ExtendLastRow = false;
            tableHeader.SpacingAfter  = 10f;
            document.Add(tableHeader);

            LineSeparator lineSeparator = new LineSeparator(1f, 100f, BaseColor.Black, Element.ALIGN_CENTER, 1);
            document.Add(lineSeparator);


            #endregion

            #region Identity


            PdfPTable tableIdentity = new PdfPTable(4);
            tableIdentity.SetWidths(new float[] { 3f, 7f, 2f, 5f });
            PdfPCell cellIdentityContentLeft = new PdfPCell()
            {
                Border = Rectangle.NO_BORDER, HorizontalAlignment = Element.ALIGN_LEFT
            };

            cellIdentityContentLeft.Phrase = new Phrase("Tanggal", normal_font);
            tableIdentity.AddCell(cellIdentityContentLeft);
            cellIdentityContentLeft.Phrase = new Phrase(": " + viewModel.date.ToString("dd/MM/yyyy", new CultureInfo("id-ID")), normal_font);
            tableIdentity.AddCell(cellIdentityContentLeft);
            cellIdentityContentLeft.Phrase = new Phrase("Bagian", normal_font);
            tableIdentity.AddCell(cellIdentityContentLeft);
            cellIdentityContentLeft.Phrase = new Phrase(": " + viewModel.unit.name, normal_font);
            tableIdentity.AddCell(cellIdentityContentLeft);
            cellIdentityContentLeft.Phrase = new Phrase("Diterima dari", normal_font);
            tableIdentity.AddCell(cellIdentityContentLeft);
            cellIdentityContentLeft.Phrase = new Phrase(": " + viewModel.supplier.name, normal_font);
            tableIdentity.AddCell(cellIdentityContentLeft);
            cellIdentityContentLeft.Phrase = new Phrase("No.", normal_font);
            tableIdentity.AddCell(cellIdentityContentLeft);
            cellIdentityContentLeft.Phrase = new Phrase(": " + viewModel.no, normal_font);
            tableIdentity.AddCell(cellIdentityContentLeft);

            PdfPCell cellIdentity = new PdfPCell(tableIdentity);
            tableIdentity.ExtendLastRow = false;
            tableIdentity.SpacingAfter  = 10f;
            tableIdentity.SpacingBefore = 10f;
            document.Add(tableIdentity);

            #endregion

            #region TableContent

            PdfPCell cellCenter = new PdfPCell()
            {
                Border = Rectangle.TOP_BORDER | Rectangle.LEFT_BORDER | Rectangle.BOTTOM_BORDER | Rectangle.RIGHT_BORDER, HorizontalAlignment = Element.ALIGN_CENTER, VerticalAlignment = Element.ALIGN_MIDDLE, Padding = 5
            };
            PdfPCell cellRight = new PdfPCell()
            {
                Border = Rectangle.TOP_BORDER | Rectangle.LEFT_BORDER | Rectangle.BOTTOM_BORDER | Rectangle.RIGHT_BORDER, HorizontalAlignment = Element.ALIGN_RIGHT, VerticalAlignment = Element.ALIGN_MIDDLE, Padding = 5
            };
            PdfPCell cellLeft = new PdfPCell()
            {
                Border = Rectangle.TOP_BORDER | Rectangle.LEFT_BORDER | Rectangle.BOTTOM_BORDER | Rectangle.RIGHT_BORDER, HorizontalAlignment = Element.ALIGN_LEFT, VerticalAlignment = Element.ALIGN_MIDDLE, Padding = 5
            };

            PdfPTable tableContent = new PdfPTable(5);
            tableContent.SetWidths(new float[] { 1.5f, 9f, 2.5f, 2.5f, 5f });

            cellCenter.Phrase = new Phrase("No", bold_font);
            tableContent.AddCell(cellCenter);
            cellCenter.Phrase = new Phrase("Nama Barang", bold_font);
            tableContent.AddCell(cellCenter);
            cellCenter.Phrase = new Phrase("Jumlah", bold_font);
            tableContent.AddCell(cellCenter);
            cellCenter.Phrase = new Phrase("Satuan", bold_font);
            tableContent.AddCell(cellCenter);
            cellCenter.Phrase = new Phrase("Keterangan", bold_font);
            tableContent.AddCell(cellCenter);

            //for (int a = 0; a < 20; a++) // coba kalau banyak baris ^_^
            for (int indexItem = 0; indexItem < viewModel.items.Count; indexItem++)
            {
                UnitReceiptNoteItemViewModel item = viewModel.items[indexItem];

                cellCenter.Phrase = new Phrase((indexItem + 1).ToString(), normal_font);
                tableContent.AddCell(cellCenter);

                cellLeft.Phrase = new Phrase($"{item.product.code} - {item.product.name}", normal_font);
                tableContent.AddCell(cellLeft);
                //cellCenter.Phrase = new Phrase(string.Format("{0:n2}", item.deliveredQuantity), normal_font);
                cellCenter.Phrase = new Phrase($"{item.deliveredQuantity}", normal_font);
                tableContent.AddCell(cellCenter);

                cellLeft.Phrase = new Phrase(item.product.uom.unit, normal_font);
                tableContent.AddCell(cellLeft);

                cellCenter.Phrase = new Phrase(item.prNo + "\n" + item.productRemark, normal_font);
                tableContent.AddCell(cellCenter);
            }


            PdfPCell cellContent = new PdfPCell(tableContent);
            tableContent.ExtendLastRow = false;
            tableContent.SpacingAfter  = 10f;
            document.Add(tableContent);

            #endregion


            Paragraph date = new Paragraph($"Sukoharjo, {viewModel.date.ToString("dd MMMM yyyy", new CultureInfo("id-ID"))}", normal_font)
            {
                Alignment = Element.ALIGN_RIGHT
            };
            document.Add(date);

            #region TableSignature

            PdfPTable tableSignature = new PdfPTable(2);

            PdfPCell cellSignatureContent = new PdfPCell()
            {
                Border = Rectangle.NO_BORDER, HorizontalAlignment = Element.ALIGN_CENTER
            };
            cellSignatureContent.Phrase = new Phrase("Mengetahui\n\n\n\n\n(  _____________________  )", bold_font);
            tableSignature.AddCell(cellSignatureContent);
            cellSignatureContent.Phrase = new Phrase("Yang Menerima\n\n\n\n\n(  _____________________  )", bold_font);
            tableSignature.AddCell(cellSignatureContent);


            PdfPCell cellSignature = new PdfPCell(tableSignature); // dont remove
            tableSignature.ExtendLastRow = false;
            tableSignature.SpacingBefore = 10f;
            document.Add(tableSignature);

            #endregion

            document.Close();
            byte[] byteInfo = stream.ToArray();
            stream.Write(byteInfo, 0, byteInfo.Length);
            stream.Position = 0;

            return(stream);
        }
Exemple #3
0
        public MemoryStream GenerateExcel(string no, string unit, string category, DateTime?dateFrom, DateTime?dateTo)
        {
            Tuple <List <UnitReceiptNoteViewModel>, int> Data = this.GetReport(no, unit, category, dateFrom, dateTo);

            DataTable result = new DataTable();

            result.Columns.Add(new DataColumn()
            {
                ColumnName = "TGL", DataType = typeof(String)
            });
            //result.Merge
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NOMOR NOTA", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NAMA BARANG", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "NO FAKTUR PAJAK", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "TIPE", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "UNIT", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "PEMBELIAN", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "", DataType = typeof(String)
            });
            result.Columns.Add(new DataColumn()
            {
                ColumnName = "TOTAL", DataType = typeof(double)
            });

            result.Rows.Add("", "", "", "", "", "", "DPP", "PPN", 0);

            ExcelPackage package = new ExcelPackage();

            foreach (KeyValuePair <DataTable, String> item in new List <KeyValuePair <DataTable, string> >()
            {
                new KeyValuePair <DataTable, string>(result, "Report")
            })
            {
                var sheet = package.Workbook.Worksheets.Add(item.Value);
                sheet.Cells["A1:A2"].Merge = true;
                sheet.Cells["B1:B2"].Merge = true;
                sheet.Cells["C1:C2"].Merge = true;
                sheet.Cells["D1:D2"].Merge = true;
                sheet.Cells["E1:E2"].Merge = true;
                sheet.Cells["F1:F2"].Merge = true;
                sheet.Cells["G1:H1"].Merge = true;
                sheet.Cells["I1:I2"].Merge = true;
            }

            List <(string, Enum, Enum)> mergeCells = new List <(string, Enum, Enum)>()
            {
            };

            if (Data.Item2 == 0)
            {
                result.Rows.Add("", "", "", "", "", "", "", "", 0); // to allow column name to be generated properly for empty data as template
            }
            else
            {
                Dictionary <string, List <UnitReceiptNoteViewModel> > dataByCategory = new Dictionary <string, List <UnitReceiptNoteViewModel> >();
                Dictionary <string, double> subTotalCategory = new Dictionary <string, double>();

                foreach (UnitReceiptNoteViewModel data in Data.Item1)
                {
                    foreach (UnitReceiptNoteItemViewModel item in data.items)
                    {
                        string categoryCode = item.purchaseOrder.category.code;

                        if (!dataByCategory.ContainsKey(categoryCode))
                        {
                            dataByCategory.Add(categoryCode, new List <UnitReceiptNoteViewModel> {
                            });
                        }
                        dataByCategory[categoryCode].Add(new UnitReceiptNoteViewModel
                        {
                            no          = data.no,
                            date        = data.date,
                            incomeTaxNo = data.incomeTaxNo,
                            unit        = data.unit,
                            items       = new List <UnitReceiptNoteItemViewModel>()
                            {
                                item
                            }
                        });

                        if (!subTotalCategory.ContainsKey(categoryCode))
                        {
                            subTotalCategory.Add(categoryCode, 0);
                        }
                        subTotalCategory[categoryCode] += item.deliveredQuantity;
                    }
                }

                double total       = 0;
                int    rowPosition = 1;

                foreach (KeyValuePair <string, List <UnitReceiptNoteViewModel> > categoryCode in dataByCategory)
                {
                    string catCode = "";
                    foreach (UnitReceiptNoteViewModel data in categoryCode.Value)
                    {
                        UnitReceiptNoteItemViewModel item = data.items[0];
                        result.Rows.Add(data.date.ToString("dd MMM yyyy", new CultureInfo("id-ID")), data.no, item.product.name, data.incomeTaxNo, item.purchaseOrder.category.code, data.unit.name, item.pricePerDealUnit * item.deliveredQuantity, (item.pricePerDealUnit * item.deliveredQuantity) * 0.1, (item.pricePerDealUnit * item.deliveredQuantity) + ((item.pricePerDealUnit * item.deliveredQuantity) * 0.1));
                        rowPosition += 1;
                        catCode      = item.purchaseOrder.category.code;
                    }
                    result.Rows.Add("", "", "", "SUB TOTAL", catCode, "", subTotalCategory[categoryCode.Key], subTotalCategory[categoryCode.Key] * 0.1, subTotalCategory[categoryCode.Key] + (subTotalCategory[categoryCode.Key] * 0.1));
                    rowPosition += 1;

                    mergeCells.Add(($"A{rowPosition}:H{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Right, OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom));

                    total += subTotalCategory[categoryCode.Key];
                }
                result.Rows.Add("", "", "", "TOTAL", "", "", total, total * 0.1, total + (total * 0.1));
                rowPosition += 1;

                mergeCells.Add(($"A{rowPosition}:H{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Right, OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom));
            }


            return(Excel.CreateExcel(new List <(DataTable, string, List <(string, Enum, Enum)>)>()
            {
                (result, "Report", mergeCells)
            }, true));
        }