public MemoryStream GenerateExcel(string no, string buyerCode, string statusCancel, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var Query = GetReportQuery(no, buyerCode, statusCancel, dateFrom, dateTo, offset);
var statusCheck = "";
var bookingOrderNoTemp = "";
long?IdTemp = 0;
long?bookingOrderItemId = 0;
List <CanceledGarmentBookingOrderReportViewModel> Data = new List <CanceledGarmentBookingOrderReportViewModel>();
Query = Query.OrderBy(b => b.BookingOrderNo).ThenBy(b => b.CancelStatus);
foreach (var item in Query)
{
CanceledGarmentBookingOrderReportViewModel _new = new CanceledGarmentBookingOrderReportViewModel
{
Id = item.Id,
CreatedUtc = item.CreatedUtc,
BookingOrderDate = item.BookingOrderDate,
BookingOrderNo = item.BookingOrderNo,
BuyerName = item.BuyerName,
OrderQuantity = item.OrderQuantity,
DeliveryDate = item.DeliveryDate,
ComodityName = item.ComodityName,
ConfirmDate = item.ConfirmDate,
ConfirmQuantity = item.ConfirmQuantity,
DeliveryDateItem = item.DeliveryDateItem,
Remark = item.Remark,
CanceledDate = item.CanceledDate,
CanceledQuantity = item.CanceledQuantity,
ExpiredBookingQuantity = item.ExpiredBookingQuantity,
BookingOrderItemId = item.BookingOrderItemId,
TotalBeginningQuantity = item.TotalBeginningQuantity,
CancelStatus = item.CancelStatus,
LastModifiedUtc = item.LastModifiedUtc
};
if (bookingOrderNoTemp == item.BookingOrderNo && statusCheck == item.CancelStatus && statusCheck != "Cancel Confirm" && IdTemp == item.Id && bookingOrderItemId != item.BookingOrderItemId)
{
_new = null;
}
else
{
Data.Add(_new);
}
if (bookingOrderNoTemp == "" || bookingOrderNoTemp != item.BookingOrderNo)
{
bookingOrderNoTemp = item.BookingOrderNo;
}
if (statusCheck == "" || statusCheck != item.CancelStatus)
{
statusCheck = item.CancelStatus;
}
if (IdTemp == 0 || IdTemp != item.Id)
{
IdTemp = item.Id;
}
if (bookingOrderItemId == 0 || bookingOrderItemId != item.BookingOrderItemId)
{
bookingOrderItemId = item.BookingOrderItemId;
}
}
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "Kode Booking", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Booking", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Buyer", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah Booking Order Awal", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah Booking Order Akhir", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Pengiriman (Booking)", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Komoditi", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jml Confirm", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Confirm", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Pengiriman (Confirm)", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Keterangan", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Cancel", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah yg Dicancel", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Status Cancel", DataType = typeof(String)
});
List <(string, Enum, Enum)> mergeCells = new List <(string, Enum, Enum)>()
{
};
if (Data.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", "", "", "", "", "", ""); // to allow column name to be generated properly for empty data as template
}
else
{
int index = 0;
string temp_No = "";
int rowPosition = 1;
int counterTemp = 1;
foreach (var item in Data.OrderByDescending(o => o.LastModifiedUtc))
{
index++;
rowPosition++;
DateTimeOffset bookingOrderDate = item.BookingOrderDate ?? new DateTime(1970, 1, 1);
DateTimeOffset deliveryDate = item.DeliveryDate ?? new DateTime(1970, 1, 1);
DateTimeOffset confirmDate = item.ConfirmDate ?? new DateTime(1970, 1, 1);
DateTimeOffset deliveryDateItem = item.DeliveryDateItem ?? new DateTime(1970, 1, 1);
DateTimeOffset canceledDate = item.CanceledDate ?? new DateTime(1970, 1, 1);
string BookingOrderDate = bookingOrderDate == new DateTime(1970, 1, 1) ? "-" : bookingOrderDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMMM yyyy", new CultureInfo("id-ID"));
string DeliveryDate = deliveryDate == new DateTime(1970, 1, 1) ? "-" : deliveryDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMMM yyyy", new CultureInfo("id-ID"));
string ConfirmDate = confirmDate == new DateTime(1970, 1, 1) ? "-" : confirmDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMMM yyyy", new CultureInfo("id-ID"));
string DeliveryDateItem = deliveryDateItem == new DateTime(1970, 1, 1) ? "-" : deliveryDateItem.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMMM yyyy", new CultureInfo("id-ID"));
string CanceledDate = canceledDate == new DateTime(1970, 1, 1) ? "-" : canceledDate.ToOffset(new TimeSpan(offset, 0, 0)).ToString("dd MMMM yyyy", new CultureInfo("id-ID"));
if (temp_No == item.BookingOrderNo)
{
item.BookingOrderNo = null;
BookingOrderDate = null;
item.BuyerName = null;
item.TotalBeginningQuantity = null;
item.OrderQuantity = null;
DeliveryDate = null;
counterTemp++;
result.Rows.Add(item.BookingOrderNo, BookingOrderDate, item.BuyerName, item.TotalBeginningQuantity,
item.OrderQuantity, DeliveryDate, item.ComodityName ?? "-", item.ConfirmQuantity.ToString() ?? "-", ConfirmDate, DeliveryDateItem, item.Remark ?? "-", CanceledDate, item.CanceledQuantity, item.CancelStatus);
}
else
{
result.Rows.Add(item.BookingOrderNo, BookingOrderDate, item.BuyerName, item.TotalBeginningQuantity,
item.OrderQuantity, DeliveryDate, item.ComodityName ?? "-", item.ConfirmQuantity.ToString() ?? "-", ConfirmDate, DeliveryDateItem, item.Remark ?? "-", CanceledDate, item.CanceledQuantity, item.CancelStatus);
if (counterTemp > 1)
{
mergeCells.Add(($"A{rowPosition - counterTemp}:A{rowPosition - 1}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"B{rowPosition - counterTemp}:B{rowPosition - 1}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"C{rowPosition - counterTemp}:C{rowPosition - 1}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"D{rowPosition - counterTemp}:D{rowPosition - 1}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"E{rowPosition - counterTemp}:E{rowPosition - 1}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"F{rowPosition - counterTemp}:F{rowPosition - 1}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
counterTemp = 1;
}
temp_No = item.BookingOrderNo;
}
}
if (counterTemp > 1)
{
mergeCells.Add(($"A{rowPosition + 1 - counterTemp}:A{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"B{rowPosition + 1 - counterTemp}:B{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"C{rowPosition + 1 - counterTemp}:C{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"D{rowPosition + 1 - counterTemp}:D{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"E{rowPosition + 1 - counterTemp}:E{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
mergeCells.Add(($"F{rowPosition + 1 - counterTemp}:F{rowPosition}", OfficeOpenXml.Style.ExcelHorizontalAlignment.Left, OfficeOpenXml.Style.ExcelVerticalAlignment.Center));
counterTemp = 1;
}
}
return(Excel.CreateExcel(new List <(DataTable, string, List <(string, Enum, Enum)>)>()
{
(result, "Report", mergeCells)
}, true));
}
public IQueryable <CanceledGarmentBookingOrderReportViewModel> GetReportQuery(string no, string buyerCode, string statusCancel, DateTime?dateFrom, DateTime?dateTo, int offset)
{
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
List <CanceledGarmentBookingOrderReportViewModel> listGarmentBookingReport = new List <CanceledGarmentBookingOrderReportViewModel>();
var Query = (from a in DbContext.GarmentBookingOrders
join b in DbContext.GarmentBookingOrderItems on a.Id equals b.BookingOrderId into temp
from b in temp.DefaultIfEmpty()
//Conditions
where a.IsDeleted == false &&
a.BookingOrderNo == (string.IsNullOrWhiteSpace(no) ? a.BookingOrderNo : no) &&
a.BuyerCode == (string.IsNullOrWhiteSpace(buyerCode) ? a.BuyerCode : buyerCode) &&
a.BookingOrderDate.AddHours(offset).Date >= DateFrom.Date &&
a.BookingOrderDate.AddHours(offset).Date <= DateTo.Date
select new CanceledGarmentBookingOrderReportViewModel
{
Id = a.Id,
CreatedUtc = a.CreatedUtc,
BookingOrderDate = a.BookingOrderDate,
BookingOrderNo = a.BookingOrderNo,
BuyerName = a.BuyerName,
OrderQuantity = a.OrderQuantity,
CanceledQuantity = a.CanceledQuantity,
ExpiredBookingQuantity = a.ExpiredBookingQuantity,
DeliveryDate = a.DeliveryDate,
ComodityName = b.ComodityName,
ConfirmQuantity = b.ConfirmQuantity,
ConfirmDate = b.ConfirmDate,
DeliveryDateItem = b.DeliveryDate,
Remark = b.Remark,
CanceledDate = a.CanceledDate,
CanceledDateItem = b.CanceledDate,
ExpiredBookingDate = a.ExpiredBookingDate,
CancelStatus = "",
IsCanceled = b.IsCanceled,
BookingOrderItemId = b.Id,
TotalBeginningQuantity = (a.OrderQuantity + a.CanceledQuantity + a.ExpiredBookingQuantity),
LastModifiedUtc = a.LastModifiedUtc,
row_count = 1
});
foreach (var query in Query)
{
if (query.IsCanceled == true)
{
CanceledGarmentBookingOrderReportViewModel view = new CanceledGarmentBookingOrderReportViewModel
{
Id = query.Id,
CreatedUtc = query.CreatedUtc,
BookingOrderDate = query.BookingOrderDate,
BookingOrderNo = query.BookingOrderNo,
BuyerName = query.BuyerName,
OrderQuantity = query.OrderQuantity,
DeliveryDate = query.DeliveryDate,
ComodityName = query.ComodityName,
ConfirmQuantity = query.ConfirmQuantity,
ConfirmDate = query.ConfirmDate,
DeliveryDateItem = query.DeliveryDateItem,
Remark = query.Remark,
CanceledDate = query.CanceledDateItem,
CanceledQuantity = query.ConfirmQuantity,
ExpiredBookingQuantity = query.ExpiredBookingQuantity,
BookingOrderItemId = query.BookingOrderItemId,
TotalBeginningQuantity = query.TotalBeginningQuantity,
CancelStatus = "Cancel Confirm",
LastModifiedUtc = query.LastModifiedUtc,
row_count = 1
};
listGarmentBookingReport.Add(view);
}
if (query.CanceledQuantity > 0)
{
CanceledGarmentBookingOrderReportViewModel view = new CanceledGarmentBookingOrderReportViewModel
{
Id = query.Id,
CreatedUtc = query.CreatedUtc,
BookingOrderDate = query.BookingOrderDate,
BookingOrderNo = query.BookingOrderNo,
BuyerName = query.BuyerName,
OrderQuantity = query.OrderQuantity,
DeliveryDate = query.DeliveryDate,
ComodityName = null,
ConfirmQuantity = null,
ConfirmDate = null,
DeliveryDateItem = null,
Remark = null,
CanceledDate = query.CanceledDate,
CanceledQuantity = query.CanceledQuantity,
ExpiredBookingQuantity = query.ExpiredBookingQuantity,
BookingOrderItemId = query.BookingOrderItemId,
TotalBeginningQuantity = query.TotalBeginningQuantity,
CancelStatus = "Cancel Sisa",
LastModifiedUtc = query.LastModifiedUtc,
row_count = 1
};
listGarmentBookingReport.Add(view);
}
if (query.ExpiredBookingQuantity > 0)
{
CanceledGarmentBookingOrderReportViewModel view = new CanceledGarmentBookingOrderReportViewModel
{
Id = query.Id,
CreatedUtc = query.CreatedUtc,
BookingOrderDate = query.BookingOrderDate,
BookingOrderNo = query.BookingOrderNo,
BuyerName = query.BuyerName,
OrderQuantity = query.OrderQuantity,
DeliveryDate = query.DeliveryDate,
ComodityName = null,
ConfirmQuantity = null,
ConfirmDate = null,
DeliveryDateItem = null,
Remark = null,
CanceledDate = query.ExpiredBookingDate,
CanceledQuantity = query.ExpiredBookingQuantity,
ExpiredBookingQuantity = query.ExpiredBookingQuantity,
BookingOrderItemId = query.BookingOrderItemId,
TotalBeginningQuantity = query.TotalBeginningQuantity,
CancelStatus = "Expired",
LastModifiedUtc = query.LastModifiedUtc,
row_count = 1
};
listGarmentBookingReport.Add(view);
}
}
if (statusCancel != null)
{
return(listGarmentBookingReport.Where(m => m.CancelStatus == statusCancel).AsQueryable());
}
else
{
return(listGarmentBookingReport.AsQueryable());
}
}
public Tuple <List <CanceledGarmentBookingOrderReportViewModel>, int> Read(string no, string buyerCode, string statusCancel, DateTime?dateFrom, DateTime?dateTo, int page, int size, string Order, int offset)
{
var Query = GetReportQuery(no, buyerCode, statusCancel, dateFrom, dateTo, offset);
var statusCheck = "";
var bookingOrderNoTemp = "";
long?IdTemp = 0;
long?bookingOrderItemId = 0;
List <CanceledGarmentBookingOrderReportViewModel> Data = new List <CanceledGarmentBookingOrderReportViewModel>();
foreach (var item in Query.OrderBy(b => b.BookingOrderNo).ThenBy(b => b.CancelStatus))
{
CanceledGarmentBookingOrderReportViewModel _new = new CanceledGarmentBookingOrderReportViewModel
{
Id = item.Id,
CreatedUtc = item.CreatedUtc,
BookingOrderDate = item.BookingOrderDate,
BookingOrderNo = item.BookingOrderNo,
BuyerName = item.BuyerName,
OrderQuantity = item.OrderQuantity,
DeliveryDate = item.DeliveryDate,
ComodityName = item.ComodityName,
ConfirmDate = item.ConfirmDate,
ConfirmQuantity = item.ConfirmQuantity,
DeliveryDateItem = item.DeliveryDateItem,
Remark = item.Remark,
CanceledDate = item.CanceledDate,
CanceledQuantity = item.CanceledQuantity,
ExpiredBookingQuantity = item.ExpiredBookingQuantity,
BookingOrderItemId = item.BookingOrderItemId,
TotalBeginningQuantity = item.TotalBeginningQuantity,
CancelStatus = item.CancelStatus,
LastModifiedUtc = item.LastModifiedUtc,
row_count = 1
};
if (bookingOrderNoTemp == item.BookingOrderNo && statusCheck == item.CancelStatus && statusCheck != "Cancel Confirm" && IdTemp == item.Id && bookingOrderItemId != item.BookingOrderItemId)
{
_new = null;
}
else
{
Data.Add(_new);
}
if (bookingOrderNoTemp == "" || bookingOrderNoTemp != item.BookingOrderNo)
{
bookingOrderNoTemp = item.BookingOrderNo;
}
if (statusCheck == "" || statusCheck != item.CancelStatus)
{
statusCheck = item.CancelStatus;
}
if (IdTemp == 0 || IdTemp != item.Id)
{
IdTemp = item.Id;
}
if (bookingOrderItemId == 0 || bookingOrderItemId != item.BookingOrderItemId)
{
bookingOrderItemId = item.BookingOrderItemId;
}
}
Pageable <CanceledGarmentBookingOrderReportViewModel> pageable = new Pageable <CanceledGarmentBookingOrderReportViewModel>(Data, page - 1, size);
List <CanceledGarmentBookingOrderReportViewModel> Data_ = pageable.Data.ToList <CanceledGarmentBookingOrderReportViewModel>();
int TotalData = pageable.TotalCount;
return(Tuple.Create(Data_.OrderByDescending(o => o.LastModifiedUtc).ToList(), TotalData));
}
