//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);
}
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));
}
