public Tuple <List <InventoryWeavingOutReportViewModel>, int> GetReport(string bonType, DateTime?dateFrom, DateTime?dateTo, int page, int size, string Order, int offset)
{
var Query = GetQueryReport(bonType, dateFrom, dateTo, offset);
//Query.OrderBy(x => x.BillNo).ThenBy(x => x.CorDate).ThenBy(x => x.CorrNo).ThenBy(x => x.CorrType).ThenBy(x => x.CurrCode).ThenBy(x => x.DoDate).ThenBy(x => x.DoNo).ThenBy(x => x.IncomeTaxDate).ThenBy(x => x.INDate).ThenBy(x => x.INNo).ThenBy(x => x.InvoDate).ThenBy(x => x.InvoiceNo).ThenBy(x => x.NPH).ThenBy(x => x.NPN).ThenBy(x => x.PayBill).ThenBy(x => x.PaymentDueDate).ThenBy(x => x.PaymentMethod).ThenBy(x => x.PriceTot).ThenBy(x => x.SuppCode).ThenBy(x => x.SuppName).ThenBy(x => x.VatDate);
//Query.OrderBy(x =>x.InvoiceNo).ThenBy(x => x.BillNo).ThenBy(x => x.CorDate).ThenBy(x => x.CorrNo).ThenBy(x => x.CorrType).ThenBy(x => x.CurrCode).ThenBy(x => x.DoDate).ThenBy(x => x.DoNo).ThenBy(x => x.IncomeTaxDate).ThenBy(x => x.INDate).ThenBy(x => x.INNo).ThenBy(x => x.InvoDate).ThenBy(x => x.NPH).ThenBy(x => x.NPN).ThenBy(x => x.PayBill).ThenBy(x => x.PaymentDueDate).ThenBy(x => x.PaymentMethod).ThenBy(x => x.PriceTot).ThenBy(x => x.SuppCode).ThenBy(x => x.SuppName).ThenBy(x => x.VatDate);
//Console.WriteLine(Query);
var b = Query.ToArray();
var q = Query.ToList();
var index = 0;
foreach (InventoryWeavingOutReportViewModel a in q)
{
InventoryWeavingOutReportViewModel dup = Array.Find(b, o => o.Id == a.Id && o.BonNo == a.BonNo);
if (dup != null)
{
if (dup.Number == 0)
{
index++;
dup.Number = index;
}
}
a.Number = dup.Number;
}
Query = q.AsQueryable();
Pageable <InventoryWeavingOutReportViewModel> pageable = new Pageable <InventoryWeavingOutReportViewModel>(Query, page - 1, size);
List <InventoryWeavingOutReportViewModel> Data = pageable.Data.ToList <InventoryWeavingOutReportViewModel>();
int TotalData = pageable.TotalCount;
return(Tuple.Create(Data, TotalData));
}
public MemoryStream GenerateExcelReceiptReport(string bonType, DateTimeOffset?dateFrom, DateTimeOffset?dateTo, int offset)
{
var Query = GetQueryReport(bonType, dateFrom, dateTo, offset);
var r = Query.ToArray();
var q = Query.ToList();
var index1 = 0;
foreach (InventoryWeavingOutReportViewModel f in q)
{
InventoryWeavingOutReportViewModel dup = Array.Find(r, o => o.Id == f.Id && o.BonNo == f.BonNo);
if (dup != null)
{
if (dup.Number == 0)
{
index1++;
dup.Number = index1;
}
}
f.Number = dup.Number;
}
Query = q.AsQueryable();
DataTable result = new DataTable();
var headers = new string[] { "No", "Nomor Bon", "Konstruksi", "Grade", "Piece", "Jumlah Meter", "Jumlah Piece", "Total", "Total1", "Keterangan" };
var subheaders = new string[] { "Meter", "Piece" };
for (int i = 0; i < 5; i++)
{
result.Columns.Add(new DataColumn()
{
ColumnName = headers[i], DataType = typeof(string)
});
}
result.Columns.Add(new DataColumn()
{
ColumnName = headers[5], DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = headers[6], DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = headers[7], DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = headers[8], DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = headers[9], DataType = typeof(String)
});
var index = 1;
double TotalQuantity = 0;
double TotalQuantityPiece = 0;
foreach (var item in Query)
{
TotalQuantity += item.Quantity;
TotalQuantityPiece += item.QuantityPiece;
//result.Rows.Add(index++, item.ProductCode, item.RO, item.PlanPo, item.NoArticle, item.ProductName, item.Information, item.Buyer,
// item.BeginningBalanceQty, item.BeginningBalanceUom, item.ReceiptQty, item.ReceiptCorrectionQty, item.ReceiptUom,
// NumberFormat(item.ExpendQty),
// item.ExpandUom, item.EndingBalanceQty, item.EndingUom, item.From);
result.Rows.Add(item.Number, item.BonNo, item.Construction, item.Grade, item.Piece, item.Quantity, item.QuantityPiece,
item.QuantityTot, item.QuantityPieceTot, item.Remark);
}
ExcelPackage package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Data");
var col = (char)('A' + (result.Columns.Count - 1));
//string tglawal = new DateTimeOffset(dateFrom.Value.Date).ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
//string tglakhir = new DateTimeOffset(dateTo.Value.Date).ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
sheet.Cells[$"A1:{col}1"].Value = string.Format("BON PENGANTAR GREY");
sheet.Cells[$"A1:{col}1"].Merge = true;
sheet.Cells[$"A1:{col}1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"A1:{col}1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"A1:{col}1"].Style.Font.Bold = true;
sheet.Cells[$"A2:{col}2"].Value = string.Format("FM.W.00.GG.15.003");
sheet.Cells[$"A2:{col}2"].Merge = true;
sheet.Cells[$"A2:{col}2"].Style.Font.Bold = true;
sheet.Cells[$"A2:{col}2"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
sheet.Cells[$"A2:{col}2"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"A3:{col}3"].Value = string.Format("Untuk Bagian : {0}", bonType);
sheet.Cells[$"A3:{col}3"].Merge = true;
sheet.Cells[$"A3:{col}3"].Style.Font.Bold = true;
sheet.Cells[$"A3:{col}3"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
sheet.Cells[$"A3:{col}3"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells["A7"].LoadFromDataTable(result, false, OfficeOpenXml.Table.TableStyles.Light16);
sheet.Cells["H5"].Value = headers[7];
sheet.Cells["H5:I5"].Merge = true;
Dictionary <string, int> counts = new Dictionary <string, int>();
// var docNo = Query.ToArray();
int value;
foreach (var c in Query)
{
if (counts.TryGetValue(c.BonNo, out value))
{
counts[c.BonNo]++;
}
else
{
counts[c.BonNo] = 1;
}
}
int num = 7;
foreach (KeyValuePair <string, int> b in counts)
{
sheet.Cells["A" + num + ":A" + (num + b.Value - 1)].Merge = true;
sheet.Cells["A" + num + ":A" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
sheet.Cells["B" + num + ":B" + (num + b.Value - 1)].Merge = true;
sheet.Cells["B" + num + ":B" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
sheet.Cells["C" + num + ":C" + (num + b.Value - 1)].Merge = true;
sheet.Cells["C" + num + ":C" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
sheet.Cells["H" + num + ":H" + (num + b.Value - 1)].Merge = true;
sheet.Cells["H" + num + ":H" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
sheet.Cells["I" + num + ":I" + (num + b.Value - 1)].Merge = true;
sheet.Cells["I" + num + ":I" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
num += b.Value;
}
//foreach (KeyValuePair<string, int> b in counts)
//{
// sheet.Cells["B" + num + ":B" + (num + b.Value - 1)].Merge = true;
// sheet.Cells["B" + num + ":B" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
// num += b.Value;
//}
//foreach (KeyValuePair<string, int> b in counts)
//{
// sheet.Cells["C" + num + ":C" + (num + b.Value - 1)].Merge = true;
// sheet.Cells["C" + num + ":C" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
// num += b.Value;
//}
//foreach (KeyValuePair<string, int> b in counts)
//{
// sheet.Cells["H" + num + ":H" + (num + b.Value - 1)].Merge = true;
// sheet.Cells["H" + num + ":H" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
// num += b.Value;
//}
//foreach (KeyValuePair<string, int> b in counts)
//{
// sheet.Cells["I" + num + ":I" + (num + b.Value - 1)].Merge = true;
// sheet.Cells["I" + num + ":I" + (num + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
// num += b.Value;
//}
foreach (var i in Enumerable.Range(0, 7))
{
col = (char)('A' + i);
sheet.Cells[$"{col}5"].Value = headers[i];
sheet.Cells[$"{col}5:{col}6"].Merge = true;
}
for (var i = 0; i < 2; i++)
{
col = (char)('H' + i);
sheet.Cells[$"{col}6"].Value = subheaders[i];
}
foreach (var i in Enumerable.Range(0, 1))
{
col = (char)('J' + i);
sheet.Cells[$"{col}5"].Value = headers[i + 9];
sheet.Cells[$"{col}5:{col}6"].Merge = true;
}
sheet.Cells["A5:J6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells["A5:J6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells["A5:J6"].Style.Font.Bold = true;
var widths = new int[] { 10, 20, 30, 10, 20, 15, 15, 15, 15, 20 };
foreach (var i in Enumerable.Range(0, headers.Length))
{
sheet.Column(i + 1).Width = widths[i];
}
sheet.Column(5).Hidden = true;
var a = Query.Count();
var date = a + 2;
var dateNow = DateTime.Now.Date;
var ttd = a + 3;
var ttd1 = a + 4;
//sheet.Cells[$"A{5}:J{5 + a + 3}"].Style.Border.BorderAround(ExcelBorderStyle.Thin);
sheet.Cells[$"A{4}:J{5 + a + 2}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells[$"A{5}:K{5 + a + 2}"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
sheet.Cells[$"A{7 + a}"].Value = "T O T A L . . . . . . . . . . . . . . .";
sheet.Cells[$"A{7 + a}:D{7 + a}"].Merge = true;
sheet.Cells[$"A{7 + a}:D{7 + a}"].Style.Font.Bold = true;
sheet.Cells[$"A{7 + a}:D{7 + a}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[$"A{7 + a}:D{7 + a}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells[$"F{7 + a}"].Value = TotalQuantity;
sheet.Cells[$"G{7 + a}"].Value = TotalQuantityPiece;
sheet.Cells[$"G{7 + date}"].Value = "Sukoharjo :" + dateNow.ToString("dd/MM/yyyy");
sheet.Cells[$"G{7 + date}:H{7 + date}"].Merge = true;
sheet.Cells[$"G{7 + date}:H{7 + date}"].Style.Font.Bold = true;
sheet.Cells[$"G{7 + date}:H{7 + date}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[$"G{7 + date}:H{7 + date}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells[$"G{7 + ttd}"].Value = "Diserahkan Oleh : ";
sheet.Cells[$"G{7 + ttd}:H{7 + ttd}"].Merge = true;
sheet.Cells[$"G{7 + ttd}:H{7 + ttd}"].Style.Font.Bold = true;
sheet.Cells[$"G{7 + ttd}:H{7 + ttd}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[$"G{7 + ttd}:H{7 + ttd}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells[$"B{7 + ttd}"].Value = "Diterima Oleh : ";
//sheet.Cells[$"B{7 + ttd}"].Merge = true;
sheet.Cells[$"B{7 + ttd}"].Style.Font.Bold = true;
sheet.Cells[$"B{7 + ttd}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[$"B{7 + ttd}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells[$"G{7 + ttd1}"].Value = "(....................................) ";
sheet.Cells[$"G{7 + ttd1}:H{7 + ttd1 + 3}"].Merge = true;
sheet.Cells[$"G{7 + ttd1}:H{7 + ttd1 + 3}"].Style.Font.Bold = true;
sheet.Cells[$"G{7 + ttd1}:H{7 + ttd1 + 3}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[$"G{7 + ttd1}:H{7 + ttd1 + 3}"].Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
sheet.Cells[$"B{7 + ttd1}"].Value = "(...........................) ";
sheet.Cells[$"B{7 + ttd1}:B{7 + ttd1 + 3}"].Merge = true;
sheet.Cells[$"B{7 + ttd1}:B{7 + ttd1 + 3}"].Style.Font.Bold = true;
sheet.Cells[$"B{7 + ttd1}:B{7 + ttd1 + 3}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[$"B{7 + ttd1}:B{7 + ttd1 + 3}"].Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
//sheet.Cells[$"K{7 + a}"].Value = CorrQtyTotal;
//sheet.Cells[$"M{7 + a}"].Value = ExpendQtyTotal;
//sheet.Cells[$"O{7 + a}"].Value = EndingQtyTotal;
//sheet.Cells[$"K1338"].Value = ReceiptQtyTotal;
MemoryStream stream = new MemoryStream();
package.SaveAs(stream);
return(stream);
}
