public IActionResult GetReport(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset?startDate, DateTimeOffset?endDate, DateTimeOffset?startDateAccounting, DateTimeOffset?endDateAccounting)
{
try
{
VerifyUser();
endDate = !endDate.HasValue ? DateTimeOffset.Now : endDate.GetValueOrDefault().AddHours(_identityService.TimezoneOffset).Date.AddHours(17);
startDate = !startDate.HasValue ? DateTimeOffset.MinValue : startDate;
endDateAccounting = !endDateAccounting.HasValue ? DateTimeOffset.Now : endDateAccounting.GetValueOrDefault().AddHours(_identityService.TimezoneOffset).Date.AddHours(17);
startDateAccounting = !startDateAccounting.HasValue ? DateTimeOffset.MinValue : startDateAccounting;
var result = _reportService.GetReportViewModel(internalNoteId, supplierId, position, startDate.GetValueOrDefault(), endDate.GetValueOrDefault(), startDateAccounting.GetValueOrDefault(), endDateAccounting.GetValueOrDefault());
return(Ok(new
{
apiVersion = ApiVersion,
statusCode = General.OK_STATUS_CODE,
message = General.OK_MESSAGE,
data = result
}));
}
catch (Exception e)
{
return(StatusCode(General.INTERNAL_ERROR_STATUS_CODE, e.Message + " " + e.StackTrace));
}
}
public GarmentDispositionPaymentReportDto(int dispositionId, string dispositionNoteNo, DateTimeOffset dispositionNoteDate, DateTimeOffset dispositionNoteDueDate, string proformaNo, int supplierId, string supplierCode, string supplierName, int currencyId, string currencyCode, double currencyRate, double dPPAmount, double currencyDPPAmount, double vATAmount, double currencyVATAmount, double incomeTaxAmount, double currencyIncomeTaxAmount, double othersExpenditureAmount, double totalAmount, int categoryId, string categoryCode, string categoryName, GarmentPurchasingExpeditionPosition position, string sendToPurchasingRemark, DateTimeOffset?sendToVerificationDate, DateTimeOffset?verificationAcceptedDate, string verifiedBy, DateTimeOffset?cashierAcceptedDate, string bankExpenditureNoteDate, string bankExpenditureNoteNo, string paidAmount, int externalPurchaseOrderId, string externalPurchaseOrderNo, double dispositionQuantity, int deliveryOrderId, string deliveryOrderNo, double deliveryOrderQuantity, string paymentBillsNo, string billsNo, int customsNoteId, string customsNoteNo, DateTimeOffset?customsNoteDate, int unitReceiptNoteId, string unitReceiptNoteNo, int internalNoteId, string internalNoteNo, DateTimeOffset?internalNoteDate, string sendToVerificationBy, DateTimeOffset?verifiedDate, string remark, string purchaseBy)
{
DispositionId = dispositionId;
DispositionNoteNo = dispositionNoteNo;
DispositionNoteDate = dispositionNoteDate;
DispositionNoteDueDate = dispositionNoteDueDate;
ProformaNo = proformaNo;
SupplierId = supplierId;
SupplierCode = supplierCode;
SupplierName = supplierName;
CurrencyId = currencyId;
CurrencyCode = currencyCode;
CurrencyRate = currencyRate;
DPPAmount = dPPAmount;
CurrencyDPPAmount = currencyDPPAmount;
VATAmount = vATAmount;
CurrencyVATAmount = currencyVATAmount;
IncomeTaxAmount = incomeTaxAmount;
CurrencyIncomeTaxAmount = currencyIncomeTaxAmount;
OthersExpenditureAmount = othersExpenditureAmount;
TotalAmount = totalAmount;
CategoryId = categoryId;
CategoryCode = categoryCode;
CategoryName = categoryName;
Position = position;
PositionDescription = position.ToDescriptionString();
SendToPurchasingRemark = sendToPurchasingRemark;
SendToVerificationDate = sendToVerificationDate;
VerificationAcceptedDate = verificationAcceptedDate;
VerifiedBy = verifiedBy;
VerifiedDate = verifiedDate;
CashierAcceptedDate = cashierAcceptedDate;
BankExpenditureNoteDate = bankExpenditureNoteDate;
BankExpenditureNoteNo = bankExpenditureNoteNo;
PaidAmount = paidAmount;
ExternalPurchaseOrderId = externalPurchaseOrderId;
ExternalPurchaseOrderNo = externalPurchaseOrderNo;
DispositionQuantity = dispositionQuantity;
DeliveryOrderId = deliveryOrderId;
DeliveryOrderNo = deliveryOrderNo;
DeliveryOrderQuantity = deliveryOrderQuantity;
PaymentBillsNo = paymentBillsNo;
BillsNo = billsNo;
CustomsNoteId = customsNoteId;
CustomsNoteNo = customsNoteNo;
CustomsNoteDate = customsNoteDate;
UnitReceiptNoteId = unitReceiptNoteId;
UnitReceiptNoteNo = unitReceiptNoteNo;
InternalNoteId = internalNoteId;
InternalNoteNo = internalNoteNo;
InternalNoteDate = internalNoteDate;
SendToVerificationby = sendToVerificationBy;
Remark = remark;
PurchaseBy = purchaseBy;
}
public static string ToDescriptionString(this GarmentPurchasingExpeditionPosition me)
{
var enumType = me.GetType();
var memberInfo = enumType.GetMember(me.ToString());
if (memberInfo != null && memberInfo.Length > 0)
{
var _attr = memberInfo[0].GetCustomAttributes(typeof(DescriptionAttribute), false);
if (_attr != null && _attr.Count() > 0)
{
return(((DescriptionAttribute)_attr.ElementAt(0)).Description);
}
}
return(me.ToString());
}
public IActionResult GetReportXls(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset?startDate, DateTimeOffset?endDate)
{
try
{
endDate = !endDate.HasValue ? DateTimeOffset.Now : endDate;
startDate = !startDate.HasValue ? endDate.Value.AddMonths(-1) : startDate;
var stream = _reportService.GenerateExcel(internalNoteId, supplierId, position, startDate.GetValueOrDefault(), endDate.GetValueOrDefault());
var bytes = stream.ToArray();
var filename = "Laporan Ekspedisi Garment.xlsx";
var file = File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
return(file);
}
catch (Exception e)
{
return(StatusCode(General.INTERNAL_ERROR_STATUS_CODE, e.Message + " " + e.StackTrace));
}
}
public IActionResult GetReport(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset?startDate, DateTimeOffset?endDate)
{
try
{
endDate = !endDate.HasValue ? DateTimeOffset.Now : endDate;
startDate = !startDate.HasValue ? endDate.Value.AddMonths(-1) : startDate;
var result = _reportService.GetReport(internalNoteId, supplierId, position, startDate.GetValueOrDefault(), endDate.GetValueOrDefault());
return(Ok(new
{
apiVersion = ApiVersion,
statusCode = General.OK_STATUS_CODE,
message = General.OK_MESSAGE,
data = result
}));
}
catch (Exception e)
{
return(StatusCode(General.INTERNAL_ERROR_STATUS_CODE, e.Message + " " + e.StackTrace));
}
}
public static MemoryStream GenerateExcel(List <GarmentDispositionPaymentReportDto> data, DateTimeOffset startDate, DateTimeOffset endDate, int timezoneOffset, GarmentPurchasingExpeditionPosition position)
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn()
{
ColumnName = "No. Disposisi", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Disposisi", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Jatuh Tempo", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor Proforma", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Supplier", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Kurs", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "DPP", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "PPN", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "PPh", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Biaya Lain-lain", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Total", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Kategori", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Posisi", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Alasan Retur", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Pembelian Kirim", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Keterangan", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Terima", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Kirim", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Verifikator", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Terima", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl Bayar", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "No. Bukti Pengeluaran Bank", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nominal yang Dibayar", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Mata Uang", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "PO External", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Qty Disposisi", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor Surat Jalan", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Qty Surat Jalan", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor SJ", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor BP Kecil", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor BP Besar", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor BeaCukai", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal BeaCukai", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor Bon Terima", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor Nota Intern", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal Nota Intern", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Staff", DataType = typeof(string)
});
if (data.Count == 0)
{
dt.Rows.Add("", "", "", "", "", "", 0, 0, 0, 0, 0, "", "", "", "", "", "", "", "", "", "", "", "", "", "", 0, "", "", "", "", "", "", "", "", "", "", "");
}
else
{
data = data.OrderByDescending(s => s.DispositionNoteDate).ToList();
foreach (var item in data)
{
dt.Rows.Add(
item.DispositionNoteNo,
item.DispositionNoteDate.AddHours(timezoneOffset).ToString("dd/MM/yyyy"),
item.DispositionNoteDueDate.AddHours(timezoneOffset).ToString("dd/MM/yyyy"),
item.ProformaNo,
item.SupplierName,
item.CurrencyCode,
item.DPPAmount,
item.VATAmount,
item.IncomeTaxAmount,
item.OthersExpenditureAmount,
item.TotalAmount,
item.CategoryName,
item.PositionDescription,
item.SendToPurchasingRemark,
item.SendToVerificationDate != null ? item.SendToVerificationDate.GetValueOrDefault().AddHours(timezoneOffset).ToString("dd/MM/yyyy") : "",
item.Remark,
item.VerificationAcceptedDate != null ? item.VerificationAcceptedDate.GetValueOrDefault().AddHours(timezoneOffset).ToString("dd/MM/yyyy") : "",
item.VerifiedDate != null ? item.VerifiedDate.GetValueOrDefault().AddHours(timezoneOffset).ToString("dd/MM/yyyy") : "",
item.VerifiedBy,
item.CashierAcceptedDate != null ? item.CashierAcceptedDate.GetValueOrDefault().AddHours(timezoneOffset).ToString("dd/MM/yyyy") : "",
item.BankExpenditureNoteDate,
item.BankExpenditureNoteNo,
item.PaidAmount,
item.CurrencyCode,
item.ExternalPurchaseOrderNo,
item.DispositionQuantity,
item.DeliveryOrderNo,
item.DeliveryOrderQuantity,
item.DeliveryOrderNo,
item.BillsNo,
item.PaymentBillsNo,
item.CustomsNoteNo,
item.CustomsNoteDate != null ? item.CustomsNoteDate.GetValueOrDefault().AddHours(timezoneOffset).ToString("dd/MM/yyyy") : "",
item.UnitReceiptNoteNo,
item.InternalNoteNo,
item.InternalNoteDate != null ? item.InternalNoteDate.GetValueOrDefault().AddHours(timezoneOffset).ToString("dd/MM/yyyy") : "",
item.SendToVerificationby
);
}
}
return(CreateExcelDispositionReport(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(dt, "Laporan Ekspedisi Garment")
}, startDate, endDate, timezoneOffset, position, false));
}
public MemoryStream GenerateExcel(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset startDate, DateTimeOffset endDate)
{
var query = GetQuery(internalNoteId, supplierId, position, startDate, endDate);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn()
{
ColumnName = "No. NI", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. NI", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Supplier", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Jatuh Tempo", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Nomor Invoice", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "DPP", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "PPN", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "PPh", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Total", DataType = typeof(double)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tipe Bayar", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Term Pembayaran", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tempo", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Posisi", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Pembelian Kirim", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Admin", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Verif Terima", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Verif Kirim", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Verifikator", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Terima Kasir", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "No. Bukti Pengeluaran Bank", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Terima Pembelian", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Admin Pembelian", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Alasan", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Tgl. Terima Accounting", DataType = typeof(string)
});
dt.Columns.Add(new DataColumn()
{
ColumnName = "Admin Accounting", DataType = typeof(string)
});
if (query.Count() == 0)
{
dt.Rows.Add("", "", "", "", "", 0, 0, 0, 0, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "");
}
else
{
var data = query.OrderByDescending(s => s.LastModifiedUtc).ToList();
foreach (var item in data)
{
dt.Rows.Add(
item.InternalNoteNo,
item.InternalNoteDate.ToOffset(new TimeSpan(_identityService.TimezoneOffset, 0, 0)).ToString("d/M/yyyy", new CultureInfo("id-ID")),
item.SupplierName,
item.InternalNoteDueDate.ToOffset(new TimeSpan(_identityService.TimezoneOffset, 0, 0)).ToString("d/M/yyyy", new CultureInfo("id-ID")),
item.InvoicesNo,
item.DPP,
item.VAT,
item.IncomeTax,
item.TotalPaid,
item.PaymentType,
item.PaymentMethod,
item.PaymentDueDays.ToString(),
item.Position.ToDescriptionString(),
item.SendToVerificationDate?.ToOffset(new TimeSpan(_identityService.TimezoneOffset, 0, 0)).ToString("d/M/yyyy", new CultureInfo("id-ID")),
item.SendToVerificationBy,
item.VerificationAcceptedDate?.ToOffset(new TimeSpan(_identityService.TimezoneOffset, 0, 0)).ToString("d/M/yyyy", new CultureInfo("id-ID")),
item.SendToVerificationDate?.ToOffset(new TimeSpan(_identityService.TimezoneOffset, 0, 0)).ToString("d/M/yyyy", new CultureInfo("id-ID")),
item.VerificationAcceptedBy,
item.CashierAcceptedDate?.ToOffset(new TimeSpan(_identityService.TimezoneOffset, 0, 0)).ToString("d/M/yyyy", new CultureInfo("id-ID")),
null,
item.SendToPurchasingDate?.ToOffset(new TimeSpan(_identityService.TimezoneOffset, 0, 0)).ToString("d/M/yyyy", new CultureInfo("id-ID")),
item.SendToPurchasingBy,
item.SendToPurchasingRemark,
item.AccountingAcceptedDate?.ToOffset(new TimeSpan(_identityService.TimezoneOffset, 0, 0)).ToString("d/M/yyyy", new CultureInfo("id-ID")),
item.AccountingAcceptedBy
);
}
}
return(Excel.CreateExcel(new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(dt, "Laporan Ekspedisi Garment")
}, true));
}
public IndexDto(int id, string internalNoteNo, DateTimeOffset internalNoteDate, DateTimeOffset internalNoteDueDate, string supplierName, double amount, string currencyCode, string remark, GarmentPurchasingExpeditionPosition position, string sendToPurchasingRemark) : this(id, internalNoteNo, internalNoteDate, internalNoteDueDate, supplierName, amount, currencyCode, remark, position)
{
SendToPurchasingRemark = sendToPurchasingRemark;
}
public static MemoryStream CreateExcelDispositionReport(List <KeyValuePair <DataTable, string> > dtSourceList, DateTimeOffset startDate, DateTimeOffset endDate, int timezoneOffset, GarmentPurchasingExpeditionPosition position, bool styling = false)
{
ExcelPackage package = new ExcelPackage();
foreach (KeyValuePair <DataTable, string> item in dtSourceList)
{
var sheet = package.Workbook.Worksheets.Add(item.Value);
sheet.Cells["A1"].Value = "PT.Dan Liris";
sheet.Cells["A2"].Value = "LAPORAN EKSPEDISI BUKTI PEMBAYARAN DISPOSISI";
var positionDescription = "SEMUA POSISI";
if (position > GarmentPurchasingExpeditionPosition.Invalid)
{
positionDescription = position.ToDescriptionString();
}
sheet.Cells["A3"].Value = $"{positionDescription}";
sheet.Cells["A4"].Value = $"PERIODE : {startDate.AddHours(timezoneOffset).ToString("dd/MM/yyyy")} sampai dengan {endDate.AddHours(timezoneOffset).ToString("dd/MM/yyyy")}";
sheet.Cells["A7"].LoadFromDataTable(item.Key, true);
FixColumnCustom(sheet, item.Key);
//sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
var startRow = 6;
var startCol = 1;
var maxROw = 7 + item.Key.Rows.Count;
var maxColumnt = item.Key.Columns.Count;
sheet.Cells[startRow, startCol, maxROw, maxColumnt].AutoFitColumns();
sheet.Cells[startRow, startCol, maxROw, maxColumnt].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[startRow, startCol, maxROw, maxColumnt].Style.Border.Left.Style = ExcelBorderStyle.Thin;
sheet.Cells[startRow, startCol, maxROw, maxColumnt].Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells[startRow, startCol, maxROw, maxColumnt].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
MemoryStream stream = new MemoryStream();
package.SaveAs(stream);
return(stream);
}
private IQueryable <GarmentPurchasingExpeditionModel> GetQuery(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset startDate, DateTimeOffset endDate)
{
var result = _dbContext.GarmentPurchasingExpeditions.Where(entity => entity.InternalNoteDate >= startDate && entity.InternalNoteDate <= endDate);
if (internalNoteId > 0)
{
result = result.Where(entity => entity.InternalNoteId == internalNoteId);
}
if (supplierId > 0)
{
result = result.Where(entity => entity.SupplierId == supplierId);
}
if (position > 0)
{
result = result.Where(entity => entity.Position == position);
}
if (position == GarmentPurchasingExpeditionPosition.Purchasing)
{
result = result.Where(entity => !string.IsNullOrWhiteSpace(entity.SendToPurchasingRemark));
}
if (internalNoteId > 0)
{
result = result.Where(entity => entity.InternalNoteId == internalNoteId);
}
return(result);
}
public static MemoryStream CreateExcelDispositionReport(List <KeyValuePair <DataTable, string> > dtSourceList, DateTimeOffset startDate, DateTimeOffset endDate, int timezoneOffset, GarmentPurchasingExpeditionPosition position, bool styling = false)
{
ExcelPackage package = new ExcelPackage();
foreach (KeyValuePair <DataTable, string> item in dtSourceList)
{
var sheet = package.Workbook.Worksheets.Add(item.Value);
sheet.Cells["A1"].Value = "PT.Dan Liris";
sheet.Cells["A2"].Value = "LAPORAN EKSPEDISI BUKTI PEMBAYARAN DISPOSISI";
var positionDescription = "SEMUA POSISI";
if (position > GarmentPurchasingExpeditionPosition.Invalid)
{
positionDescription = position.ToDescriptionString();
}
sheet.Cells["A3"].Value = $"{positionDescription}";
sheet.Cells["A4"].Value = $"PERIODE : {startDate.AddHours(timezoneOffset).ToString("dd/MM/yyyy")} sampai dengan {endDate.AddHours(timezoneOffset).ToString("dd/MM/yyyy")}";
sheet.Cells["A5"].LoadFromDataTable(item.Key, true, (styling == true) ? OfficeOpenXml.Table.TableStyles.Light16 : OfficeOpenXml.Table.TableStyles.None);
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
}
MemoryStream stream = new MemoryStream();
package.SaveAs(stream);
return(stream);
}
public IActionResult Get([FromQuery] string keyword, [FromQuery] int internalNoteId, [FromQuery] int supplierId, [FromQuery] GarmentPurchasingExpeditionPosition position, [FromQuery] string order = "{}", [FromQuery] int page = 1, [FromQuery] int size = 10, [FromQuery] string currencyCode = null)
{
try
{
var result = _service.GetByPosition(keyword, page, size, order, position, internalNoteId, supplierId, currencyCode);
return(Ok(new
{
apiVersion = ApiVersion,
statusCode = General.OK_STATUS_CODE,
message = General.OK_MESSAGE,
data = result.Data,
info = new
{
total = result.Count,
page,
size
}
}));
}
catch (Exception e)
{
return(StatusCode(General.INTERNAL_ERROR_STATUS_CODE, e.Message + " " + e.StackTrace));
}
}
public ReadResponse <IndexDto> GetByPosition(string keyword, int page, int size, string order, GarmentPurchasingExpeditionPosition position, int internalNoteId, int supplierId)
{
var query = _dbContext.GarmentPurchasingExpeditions.Where(entity => entity.Position == position);
if (!string.IsNullOrWhiteSpace(keyword))
{
query = query.Where(entity => entity.InternalNoteNo.Contains(keyword) || entity.SupplierName.Contains(keyword) || entity.CurrencyCode.Contains(keyword));
}
if (internalNoteId > 0)
{
query = query.Where(entity => entity.InternalNoteId == internalNoteId);
}
if (supplierId > 0)
{
query = query.Where(entity => entity.SupplierId == supplierId);
}
if (position == GarmentPurchasingExpeditionPosition.Purchasing)
{
var notPurchasingInternalNoteIds = _dbContext.GarmentPurchasingExpeditions
.GroupBy(entity => new { entity.InternalNoteId, entity.Position })
.Select(groupped => new { groupped.Key.InternalNoteId, groupped.Key.Position })
.Where(entity => entity.Position > GarmentPurchasingExpeditionPosition.Purchasing)
.Select(entity => entity.InternalNoteId)
.ToList();
var firstInternalNoteIds = _dbContext.GarmentPurchasingExpeditions
.Where(entity => entity.Position == GarmentPurchasingExpeditionPosition.Purchasing && !string.IsNullOrEmpty(entity.SendToPurchasingRemark))
.GroupBy(entity => new { entity.InternalNoteId, entity.Position })
.Select(groupped => new { groupped.OrderByDescending(entity => entity.CreatedUtc).FirstOrDefault().Id })
.Select(entity => entity.Id)
.ToList();
query = query.Where(entity => !notPurchasingInternalNoteIds.Contains(entity.InternalNoteId));
query = query.Where(entity => firstInternalNoteIds.Contains(entity.Id));
}
var orderDictionary = JsonConvert.DeserializeObject <Dictionary <string, string> >(order);
query = QueryHelper <GarmentPurchasingExpeditionModel> .Order(query, orderDictionary);
var count = query.Count();
var data = query
.Skip((page - 1) * size)
.Take(size)
.Select(entity => new IndexDto(entity))
.ToList();
return(new ReadResponse <IndexDto>(data, count, orderDictionary, new List <string>()));
}
public static MemoryStream GenerateExcel2(List <GarmentDispositionPaymentReportDto> data, DateTimeOffset startDate, DateTimeOffset endDate, int timezoneOffset, GarmentPurchasingExpeditionPosition position)
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet 1");
SetTitle(worksheet, startDate, endDate, timezoneOffset, position);
//SetTableHeader(worksheet, timezoneOffset);
//SetData(worksheet, data, timezoneOffset);
worksheet.Cells[worksheet.Cells.Address].AutoFitColumns();
var stream = new MemoryStream();
package.SaveAs(stream);
return(stream);
}
}
public async Task <List <GarmentDispositionPaymentReportDto> > GetReport(int dispositionId, int supplierId, GarmentPurchasingExpeditionPosition position, string purchasingStaff, DateTimeOffset startDate, DateTimeOffset endDate)
{
var result = new List <GarmentDispositionPaymentReportDto>();
if (position <= GarmentPurchasingExpeditionPosition.Purchasing)
{
var dispositions = await GetDispositions(startDate, endDate, new List <int>());
if (dispositionId > 0)
{
dispositions = dispositions.Where(element => element.DispositionId == dispositionId).ToList();
}
if (supplierId > 0)
{
dispositions = dispositions.Where(entity => entity.SupplierId == supplierId).ToList();
}
if (!string.IsNullOrWhiteSpace(purchasingStaff))
{
dispositions = dispositions.Where(entity => entity.DispositionCreatedBy == purchasingStaff).ToList();
}
var dispositionIds = dispositions.Select(element => element.DispositionId).ToList();
var expeditions = _dbContext.GarmentDispositionExpeditions.Where(entity => dispositionIds.Contains(entity.DispositionNoteId)).ToList();
var dispositionPaymentItems = _dbContext.GarmentInvoicePurchasingDispositionItems.Where(entity => dispositionIds.Contains(entity.DispositionId)).ToList();
var dispositionPaymentIds = dispositionPaymentItems.Select(element => element.GarmentInvoicePurchasingDisposistionId).ToList();
var dispositionPayments = _dbContext.GarmentInvoicePurchasingDispositions.Where(entity => dispositionPaymentIds.Contains(entity.Id)).ToList();
foreach (var disposition in dispositions)
{
var selectedExpeditions = expeditions.Where(element => element.DispositionNoteId == disposition.DispositionId).ToList();
if (selectedExpeditions.Count > 0)
{
foreach (var expedition in selectedExpeditions)
{
var paymentItems = dispositionPaymentItems.Where(element => element.DispositionId == disposition.DispositionId).ToList();
if (paymentItems != null && paymentItems.Count > 0)
{
//payment.InvoiceDate, payment.InvoiceNo, paymentItem.TotalPaid
var invoicesDate = "";
var paymentInvoicesNo = "";
var paymentTotalPaid = "";
foreach (var paymentItem in paymentItems)
{
var payment = dispositionPayments.FirstOrDefault(element => element.Id == paymentItem.GarmentInvoicePurchasingDisposistionId);
invoicesDate += $"- {payment.InvoiceDate:dd/MM/yyyy}\n";
paymentInvoicesNo += $"- {payment.InvoiceNo}\n";
paymentTotalPaid += $"- {paymentItem.TotalPaid:N2}\n";
}
result.Add(new GarmentDispositionPaymentReportDto(dispositionId, disposition.DispositionNoteNo, disposition.DispositionNoteDate, disposition.DispositionNoteDueDate, disposition.ProformaNo, disposition.SupplierId, disposition.SupplierCode, disposition.SupplierName, disposition.CurrencyId, disposition.CurrencyCode, disposition.CurrencyRate, disposition.DPPAmount, 0, disposition.VATAmount, 0, disposition.IncomeTaxAmount, 0, disposition.OthersExpenditureAmount, disposition.TotalAmount, 0, disposition.CategoryCode, disposition.CategoryName, GarmentPurchasingExpeditionPosition.DispositionPayment, expedition.SendToPurchasingRemark, expedition.SendToVerificationDate, expedition.VerificationAcceptedDate, expedition.VerifiedBy, expedition.CashierAcceptedDate, invoicesDate, paymentInvoicesNo, paymentTotalPaid, disposition.ExternalPurchaseOrderId, disposition.ExternalPurchaseOrderNo, disposition.DispositionQuantity, disposition.DeliveryOrderId, disposition.DeliveryOrderNo, disposition.DeliveryOrderQuantity, disposition.PaymentBillsNo, disposition.BillsNo, disposition.CustomsNoteId, disposition.CustomsNoteNo, disposition.CustomsNoteDate, disposition.UnitReceiptNoteId, disposition.UnitReceiptNoteNo, disposition.InternalNoteId, disposition.InternalNoteNo, disposition.InternalNoteDate, expedition.SendToVerificationBy, expedition.VerifiedDate));
}
else
{
result.Add(new GarmentDispositionPaymentReportDto(dispositionId, disposition.DispositionNoteNo, disposition.DispositionNoteDate, disposition.DispositionNoteDueDate, disposition.ProformaNo, disposition.SupplierId, disposition.SupplierCode, disposition.SupplierName, disposition.CurrencyId, disposition.CurrencyCode, disposition.CurrencyRate, disposition.DPPAmount, 0, disposition.VATAmount, 0, disposition.IncomeTaxAmount, 0, disposition.OthersExpenditureAmount, disposition.TotalAmount, disposition.CategoryId, disposition.CategoryCode, disposition.CategoryName, expedition.Position, expedition.SendToPurchasingRemark, expedition.SendToVerificationDate, expedition.VerificationAcceptedDate, expedition.VerifiedBy, expedition.CashierAcceptedDate, null, null, null, disposition.ExternalPurchaseOrderId, disposition.ExternalPurchaseOrderNo, disposition.DispositionQuantity, disposition.DeliveryOrderId, disposition.DeliveryOrderNo, disposition.DeliveryOrderQuantity, disposition.PaymentBillsNo, disposition.BillsNo, disposition.CustomsNoteId, disposition.CustomsNoteNo, disposition.CustomsNoteDate, disposition.UnitReceiptNoteId, disposition.UnitReceiptNoteNo, disposition.InternalNoteId, disposition.InternalNoteNo, disposition.InternalNoteDate, expedition.SendToVerificationBy, expedition.VerifiedDate));
}
}
}
else
{
result.Add(new GarmentDispositionPaymentReportDto(dispositionId, disposition.DispositionNoteNo, disposition.DispositionNoteDate, disposition.DispositionNoteDueDate, disposition.ProformaNo, disposition.SupplierId, disposition.SupplierCode, disposition.SupplierName, disposition.CurrencyId, disposition.CurrencyCode, disposition.CurrencyRate, disposition.DPPAmount, 0, disposition.VATAmount, 0, disposition.IncomeTaxAmount, 0, disposition.OthersExpenditureAmount, disposition.TotalAmount, disposition.CategoryId, disposition.CategoryCode, disposition.CategoryName, GarmentPurchasingExpeditionPosition.Purchasing, null, null, null, null, null, null, null, null, disposition.ExternalPurchaseOrderId, disposition.ExternalPurchaseOrderNo, disposition.DispositionQuantity, disposition.DeliveryOrderId, disposition.DeliveryOrderNo, disposition.DeliveryOrderQuantity, disposition.PaymentBillsNo, disposition.BillsNo, disposition.CustomsNoteId, disposition.CustomsNoteNo, disposition.CustomsNoteDate, disposition.UnitReceiptNoteId, disposition.UnitReceiptNoteNo, disposition.InternalNoteId, disposition.InternalNoteNo, disposition.InternalNoteDate, disposition.DispositionCreatedBy, null));
}
}
}
else
{
var expeditionQuery = _dbContext.GarmentDispositionExpeditions.AsQueryable();
var expenditureQuery = _dbContext.GarmentInvoicePurchasingDispositions.AsQueryable();
switch (position)
{
case GarmentPurchasingExpeditionPosition.SendToVerification:
expeditionQuery = expeditionQuery.Where(entity => entity.SendToVerificationDate.HasValue && entity.SendToVerificationDate.GetValueOrDefault() >= startDate && entity.SendToVerificationDate.GetValueOrDefault() <= endDate && entity.Position == GarmentPurchasingExpeditionPosition.SendToVerification);
break;
case GarmentPurchasingExpeditionPosition.VerificationAccepted:
expeditionQuery = expeditionQuery.Where(entity => entity.VerificationAcceptedDate.HasValue && entity.VerificationAcceptedDate.GetValueOrDefault() >= startDate && entity.VerificationAcceptedDate.GetValueOrDefault() <= endDate && entity.Position == GarmentPurchasingExpeditionPosition.VerificationAccepted);
break;
case GarmentPurchasingExpeditionPosition.SendToCashier:
expeditionQuery = expeditionQuery.Where(entity => entity.SendToCashierDate.HasValue && entity.SendToCashierDate.GetValueOrDefault() >= startDate && entity.SendToCashierDate.GetValueOrDefault() <= endDate && entity.Position == GarmentPurchasingExpeditionPosition.SendToCashier);
break;
case GarmentPurchasingExpeditionPosition.CashierAccepted:
expeditionQuery = expeditionQuery.Where(entity => entity.CashierAcceptedDate.HasValue && entity.CashierAcceptedDate.GetValueOrDefault() >= startDate && entity.CashierAcceptedDate.GetValueOrDefault() <= endDate && entity.Position == GarmentPurchasingExpeditionPosition.CashierAccepted);
break;
case GarmentPurchasingExpeditionPosition.SendToPurchasing:
expeditionQuery = expeditionQuery.Where(entity => entity.SendToPurchasingDate.HasValue && entity.SendToPurchasingDate.GetValueOrDefault() >= startDate && entity.SendToPurchasingDate.GetValueOrDefault() <= endDate && entity.Position == GarmentPurchasingExpeditionPosition.SendToPurchasing);
break;
case GarmentPurchasingExpeditionPosition.DispositionPayment:
expenditureQuery = expenditureQuery.Where(entity => entity.InvoiceDate >= startDate && entity.InvoiceDate <= endDate);
break;
}
if (position == GarmentPurchasingExpeditionPosition.DispositionPayment)
{
var dispositionPayments = expenditureQuery.ToList();
var dispositionPaymentIds = dispositionPayments.Select(element => element.Id).ToList();
var dispositionPaymentItems = _dbContext.GarmentInvoicePurchasingDispositionItems.Where(entity => dispositionPaymentIds.Contains(entity.GarmentInvoicePurchasingDisposistionId)).ToList();
var dispositionIds = dispositionPaymentItems.Select(element => element.DispositionId).ToList();
var dispositions = await GetDispositions(startDate, endDate, dispositionIds);
if (dispositionId > 0)
{
dispositions = dispositions.Where(element => element.DispositionId == dispositionId).ToList();
}
if (supplierId > 0)
{
dispositions = dispositions.Where(entity => entity.SupplierId == supplierId).ToList();
}
if (!string.IsNullOrWhiteSpace(purchasingStaff))
{
dispositions = dispositions.Where(entity => entity.DispositionCreatedBy == purchasingStaff).ToList();
}
var expeditions = _dbContext.GarmentDispositionExpeditions.Where(entity => dispositionIds.Contains(entity.DispositionNoteId) && entity.Position >= GarmentPurchasingExpeditionPosition.CashierAccepted).ToList();
foreach (var disposition in dispositions)
{
var selectedExpeditions = expeditions.Where(element => element.DispositionNoteId == disposition.DispositionId).ToList();
foreach (var expedition in selectedExpeditions)
{
var paymentItems = dispositionPaymentItems.Where(element => element.DispositionId == disposition.DispositionId).ToList();
if (paymentItems != null && paymentItems.Count > 0)
{
var invoicesDate = "";
var paymentInvoicesNo = "";
var paymentTotalPaid = "";
foreach (var paymentItem in paymentItems)
{
var payment = dispositionPayments.FirstOrDefault(element => element.Id == paymentItem.GarmentInvoicePurchasingDisposistionId);
invoicesDate += $"- {payment.InvoiceDate:dd/MM/yyyy}\n";
paymentInvoicesNo += $"- {payment.InvoiceNo}\n";
paymentTotalPaid += $"- {paymentItem.TotalPaid:N2}\n";
}
result.Add(new GarmentDispositionPaymentReportDto(dispositionId, disposition.DispositionNoteNo, disposition.DispositionNoteDate, disposition.DispositionNoteDueDate, disposition.ProformaNo, disposition.SupplierId, disposition.SupplierCode, disposition.SupplierName, disposition.CurrencyId, disposition.CurrencyCode, disposition.CurrencyRate, disposition.DPPAmount, 0, disposition.VATAmount, 0, disposition.IncomeTaxAmount, 0, disposition.OthersExpenditureAmount, disposition.TotalAmount, 0, disposition.CategoryCode, disposition.CategoryName, GarmentPurchasingExpeditionPosition.DispositionPayment, expedition.SendToPurchasingRemark, expedition.SendToVerificationDate, expedition.VerificationAcceptedDate, expedition.VerifiedBy, expedition.CashierAcceptedDate, invoicesDate, paymentInvoicesNo, paymentTotalPaid, disposition.ExternalPurchaseOrderId, disposition.ExternalPurchaseOrderNo, disposition.DispositionQuantity, disposition.DeliveryOrderId, disposition.DeliveryOrderNo, disposition.DeliveryOrderQuantity, disposition.PaymentBillsNo, disposition.BillsNo, disposition.CustomsNoteId, disposition.CustomsNoteNo, disposition.CustomsNoteDate, disposition.UnitReceiptNoteId, disposition.UnitReceiptNoteNo, disposition.InternalNoteId, disposition.InternalNoteNo, disposition.InternalNoteDate, expedition.SendToVerificationBy, expedition.VerifiedDate));
}
else
{
result.Add(new GarmentDispositionPaymentReportDto(dispositionId, disposition.DispositionNoteNo, disposition.DispositionNoteDate, disposition.DispositionNoteDueDate, disposition.ProformaNo, disposition.SupplierId, disposition.SupplierCode, disposition.SupplierName, disposition.CurrencyId, disposition.CurrencyCode, disposition.CurrencyRate, disposition.DPPAmount, 0, disposition.VATAmount, 0, disposition.IncomeTaxAmount, 0, disposition.OthersExpenditureAmount, disposition.TotalAmount, disposition.CategoryId, disposition.CategoryCode, disposition.CategoryName, expedition.Position, expedition.SendToPurchasingRemark, expedition.SendToVerificationDate, expedition.VerificationAcceptedDate, expedition.VerifiedBy, expedition.CashierAcceptedDate, null, null, null, disposition.ExternalPurchaseOrderId, disposition.ExternalPurchaseOrderNo, disposition.DispositionQuantity, disposition.DeliveryOrderId, disposition.DeliveryOrderNo, disposition.DeliveryOrderQuantity, disposition.PaymentBillsNo, disposition.BillsNo, disposition.CustomsNoteId, disposition.CustomsNoteNo, disposition.CustomsNoteDate, disposition.UnitReceiptNoteId, disposition.UnitReceiptNoteNo, disposition.InternalNoteId, disposition.InternalNoteNo, disposition.InternalNoteDate, expedition.SendToVerificationBy, expedition.VerifiedDate));
}
}
}
}
else
{
var expeditions = expeditionQuery.ToList();
var dispositionIds = expeditions.Select(element => element.DispositionNoteId).ToList();
var dispositions = await GetDispositions(startDate, endDate, dispositionIds);
if (dispositionId > 0)
{
dispositions = dispositions.Where(element => element.DispositionId == dispositionId).ToList();
}
if (supplierId > 0)
{
dispositions = dispositions.Where(entity => entity.SupplierId == supplierId).ToList();
}
if (!string.IsNullOrWhiteSpace(purchasingStaff))
{
dispositions = dispositions.Where(entity => entity.DispositionCreatedBy == purchasingStaff).ToList();
}
var dispositionPaymentItems = _dbContext.GarmentInvoicePurchasingDispositionItems.Where(entity => dispositionIds.Contains(entity.DispositionId)).ToList();
var dispositionPaymentIds = dispositionPaymentItems.Select(element => element.GarmentInvoicePurchasingDisposistionId).ToList();
var dispositionPayments = _dbContext.GarmentInvoicePurchasingDispositions.Where(entity => dispositionPaymentIds.Contains(entity.Id)).ToList();
foreach (var disposition in dispositions)
{
var selectedExpeditions = expeditions.Where(element => element.DispositionNoteId == disposition.DispositionId).ToList();
foreach (var expedition in selectedExpeditions)
{
var paymentItems = dispositionPaymentItems.Where(element => element.DispositionId == disposition.DispositionId).ToList();
if (paymentItems != null && paymentItems.Count > 0)
{
var invoicesDate = "";
var paymentInvoicesNo = "";
var paymentTotalPaid = "";
foreach (var paymentItem in paymentItems)
{
var payment = dispositionPayments.FirstOrDefault(element => element.Id == paymentItem.GarmentInvoicePurchasingDisposistionId);
invoicesDate += $"- {payment.InvoiceDate:dd/MM/yyyy}\n";
paymentInvoicesNo += $"- {payment.InvoiceNo}\n";
paymentTotalPaid += $"- {paymentItem.TotalPaid:N2}\n";
}
result.Add(new GarmentDispositionPaymentReportDto(dispositionId, disposition.DispositionNoteNo, disposition.DispositionNoteDate, disposition.DispositionNoteDueDate, disposition.ProformaNo, disposition.SupplierId, disposition.SupplierCode, disposition.SupplierName, disposition.CurrencyId, disposition.CurrencyCode, disposition.CurrencyRate, disposition.DPPAmount, 0, disposition.VATAmount, 0, disposition.IncomeTaxAmount, 0, disposition.OthersExpenditureAmount, disposition.TotalAmount, 0, disposition.CategoryCode, disposition.CategoryName, GarmentPurchasingExpeditionPosition.DispositionPayment, expedition.SendToPurchasingRemark, expedition.SendToVerificationDate, expedition.VerificationAcceptedDate, expedition.VerifiedBy, expedition.CashierAcceptedDate, invoicesDate, paymentInvoicesNo, paymentTotalPaid, disposition.ExternalPurchaseOrderId, disposition.ExternalPurchaseOrderNo, disposition.DispositionQuantity, disposition.DeliveryOrderId, disposition.DeliveryOrderNo, disposition.DeliveryOrderQuantity, disposition.PaymentBillsNo, disposition.BillsNo, disposition.CustomsNoteId, disposition.CustomsNoteNo, disposition.CustomsNoteDate, disposition.UnitReceiptNoteId, disposition.UnitReceiptNoteNo, disposition.InternalNoteId, disposition.InternalNoteNo, disposition.InternalNoteDate, expedition.SendToVerificationBy, expedition.VerifiedDate));
}
else
{
result.Add(new GarmentDispositionPaymentReportDto(dispositionId, disposition.DispositionNoteNo, disposition.DispositionNoteDate, disposition.DispositionNoteDueDate, disposition.ProformaNo, disposition.SupplierId, disposition.SupplierCode, disposition.SupplierName, disposition.CurrencyId, disposition.CurrencyCode, disposition.CurrencyRate, disposition.DPPAmount, 0, disposition.VATAmount, 0, disposition.IncomeTaxAmount, 0, disposition.OthersExpenditureAmount, disposition.TotalAmount, disposition.CategoryId, disposition.CategoryCode, disposition.CategoryName, expedition.Position, expedition.SendToPurchasingRemark, expedition.SendToVerificationDate, expedition.VerificationAcceptedDate, expedition.VerifiedBy, expedition.CashierAcceptedDate, null, null, null, disposition.ExternalPurchaseOrderId, disposition.ExternalPurchaseOrderNo, disposition.DispositionQuantity, disposition.DeliveryOrderId, disposition.DeliveryOrderNo, disposition.DeliveryOrderQuantity, disposition.PaymentBillsNo, disposition.BillsNo, disposition.CustomsNoteId, disposition.CustomsNoteNo, disposition.CustomsNoteDate, disposition.UnitReceiptNoteId, disposition.UnitReceiptNoteNo, disposition.InternalNoteId, disposition.InternalNoteNo, disposition.InternalNoteDate, expedition.SendToVerificationBy, expedition.VerifiedDate));
}
}
}
}
}
//override filter position
if (position != GarmentPurchasingExpeditionPosition.Invalid)
{
result = result.Where(s => s.Position == position).ToList();
}
return(result);
}
private async Task <int> UpdateInternalNotePosition(List <int> internalNoteIds, GarmentPurchasingExpeditionPosition position)
{
var httpClient = _serviceProvider.GetService <IHttpClientService>();
var updateInternalNotePositionData = new
{
Ids = internalNoteIds,
Position = position
};
await httpClient.PutAsync($"{APIEndpoint.Purchasing}garment-purchasing-expeditions/internal-notes/position", new StringContent(JsonConvert.SerializeObject(updateInternalNotePositionData), Encoding.UTF8, General.JsonMediaType));
return(_dbContext.SaveChanges());
}
private IQueryable <GarmentPurchasingExpeditionModel> GetQuery(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset startDate, DateTimeOffset endDate)
{
var result = _dbContext.GarmentPurchasingExpeditions.AsQueryable();
if (position > 0)
{
result = result.Where(entity => entity.Position == position);
}
switch (position)
{
case GarmentPurchasingExpeditionPosition.Purchasing:
result = result.Where(entity => entity.SendToPurchasingDate.GetValueOrDefault() >= startDate && entity.SendToPurchasingDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.SendToVerification:
result = result.Where(entity => entity.SendToVerificationDate.GetValueOrDefault() >= startDate && entity.SendToVerificationDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.VerificationAccepted:
result = result.Where(entity => entity.VerificationAcceptedDate.GetValueOrDefault() >= startDate && entity.VerificationAcceptedDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.SendToCashier:
result = result.Where(entity => entity.SendToCashierDate.GetValueOrDefault() >= startDate && entity.SendToCashierDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.CashierAccepted:
result = result.Where(entity => entity.CashierAcceptedDate.GetValueOrDefault() >= startDate && entity.CashierAcceptedDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.AccountingAccepted:
result = result.Where(entity => entity.AccountingAcceptedDate.GetValueOrDefault() >= startDate && entity.AccountingAcceptedDate.GetValueOrDefault() <= endDate);
break;
default:
result = result.Where(entity => entity.SendToVerificationDate.GetValueOrDefault() >= startDate && entity.SendToVerificationDate.GetValueOrDefault() <= endDate);
break;
}
if (internalNoteId > 0)
{
result = result.Where(entity => entity.InternalNoteId == internalNoteId);
}
if (supplierId > 0)
{
result = result.Where(entity => entity.SupplierId == supplierId);
}
if (position == GarmentPurchasingExpeditionPosition.Purchasing)
{
result = result.Where(entity => !string.IsNullOrWhiteSpace(entity.SendToPurchasingRemark));
}
if (internalNoteId > 0)
{
result = result.Where(entity => entity.InternalNoteId == internalNoteId);
}
return(result);
}
public async Task <IActionResult> GetReportXls([FromQuery] int dispositionId, [FromQuery] int supplierId, [FromQuery] GarmentPurchasingExpeditionPosition position, [FromQuery] string purchasingStaff, [FromQuery] DateTimeOffset?startDate, [FromQuery] DateTimeOffset?endDate)
{
try
{
VerifyUser();
endDate = !endDate.HasValue ? DateTimeOffset.Now : endDate.GetValueOrDefault().AddHours(_identityService.TimezoneOffset).Date.AddHours(17);
startDate = !startDate.HasValue ? DateTimeOffset.MinValue : startDate;
var result = await _reportService.GetReport(dispositionId, supplierId, position, purchasingStaff, startDate.GetValueOrDefault(), endDate.GetValueOrDefault());
var stream = DispositionPaymentReportExcelGenerator.GenerateExcel(result, startDate.GetValueOrDefault(), endDate.GetValueOrDefault(), _identityService.TimezoneOffset, position);
var bytes = stream.ToArray();
var filename = "Laporan Ekspedisi Disposisi Garment.xlsx";
var file = File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
return(file);
}
catch (Exception e)
{
return(StatusCode(General.INTERNAL_ERROR_STATUS_CODE, e.Message + " " + e.StackTrace));
}
}
public async Task <IActionResult> GetReport([FromQuery] int dispositionId, [FromQuery] int supplierId, [FromQuery] GarmentPurchasingExpeditionPosition position, [FromQuery] string purchasingStaff, [FromQuery] DateTimeOffset?startDate, [FromQuery] DateTimeOffset?endDate)
{
try
{
VerifyUser();
endDate = !endDate.HasValue ? DateTimeOffset.Now : endDate.GetValueOrDefault().AddHours(_identityService.TimezoneOffset).Date.AddHours(17);
startDate = !startDate.HasValue ? DateTimeOffset.MinValue : startDate;
var result = await _reportService.GetReport(dispositionId, supplierId, position, purchasingStaff, startDate.GetValueOrDefault(), endDate.GetValueOrDefault());
return(Ok(new
{
apiVersion = ApiVersion,
statusCode = General.OK_STATUS_CODE,
message = General.OK_MESSAGE,
data = result
}));
}
catch (Exception e)
{
return(StatusCode(General.INTERNAL_ERROR_STATUS_CODE, e.Message + " " + e.StackTrace));
}
}
public IndexDto(int id, string internalNoteNo, DateTimeOffset internalNoteDate, DateTimeOffset internalNoteDueDate, string supplierName, double amount, string currencyCode, string remark, GarmentPurchasingExpeditionPosition position)
{
Id = id;
InternalNoteNo = internalNoteNo;
InternalNoteDate = internalNoteDate;
InternalNoteDueDate = internalNoteDueDate;
SupplierName = supplierName;
Amount = amount;
CurrencyCode = currencyCode;
Remark = remark;
Status = position.ToDescriptionString();
}
public List <GarmentPurchasingExpeditionReportViewModel> GetReportViewModel(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset startDate, DateTimeOffset endDate, DateTimeOffset startDateAccounting, DateTimeOffset endDateAccounting)
{
var query = GetQuery(internalNoteId, supplierId, position, startDate, endDate, startDateAccounting, endDateAccounting);
//var queryViewModel = query.Select(entity => new GarmentPurchasingExpeditionReportViewModel(entity));
return(query.ToList());
}
public ReadResponse <IndexDto> GetByPosition(string keyword, int page, int size, string order, GarmentPurchasingExpeditionPosition position, int dispositionNoteId, int supplierId, string currencyCode = null, string filter = "{}")
{
var query = _dbContext.GarmentDispositionExpeditions.Where(entity => entity.Position == position);
if (!string.IsNullOrWhiteSpace(keyword))
{
query = query.Where(entity => entity.DispositionNoteNo.Contains(keyword) || entity.SupplierName.Contains(keyword) || entity.CurrencyCode.Contains(keyword));
}
if (dispositionNoteId > 0)
{
query = query.Where(entity => entity.DispositionNoteId == dispositionNoteId);
}
if (supplierId > 0)
{
query = query.Where(entity => entity.SupplierId == supplierId);
}
if (!string.IsNullOrEmpty(currencyCode))
{
query = query.Where(entity => entity.CurrencyCode == currencyCode);
}
if (position == GarmentPurchasingExpeditionPosition.Purchasing)
{
var notPurchasingDispositionNoteIds = _dbContext.GarmentDispositionExpeditions
.GroupBy(entity => new { entity.DispositionNoteId, entity.Position })
.Select(groupped => new { groupped.Key.DispositionNoteId, groupped.Key.Position })
.Where(entity => entity.Position > GarmentPurchasingExpeditionPosition.Purchasing)
.Select(entity => entity.DispositionNoteId)
.ToList();
var firstDispositionNoteIds = _dbContext.GarmentDispositionExpeditions
.Where(entity => entity.Position == GarmentPurchasingExpeditionPosition.Purchasing && !string.IsNullOrEmpty(entity.SendToPurchasingRemark))
.GroupBy(entity => new { entity.DispositionNoteId, entity.Position })
.Select(groupped => new { groupped.OrderByDescending(entity => entity.CreatedUtc).FirstOrDefault().Id })
.Select(entity => entity.Id)
.ToList();
query = query.Where(entity => !notPurchasingDispositionNoteIds.Contains(entity.DispositionNoteId));
query = query.Where(entity => firstDispositionNoteIds.Contains(entity.Id));
}
var orderDictionary = JsonConvert.DeserializeObject <Dictionary <string, string> >(order);
query = QueryHelper <GarmentDispositionExpeditionModel> .Order(query, orderDictionary);
var filterDictionary = JsonConvert.DeserializeObject <Dictionary <string, object> >(filter);
query = QueryHelper <GarmentDispositionExpeditionModel> .Filter(query, filterDictionary);
var count = query.Count();
var data = query
.Skip((page - 1) * size)
.Take(size)
.Select(entity => new IndexDto(entity))
.ToList();
var dataDispositionIds = data.Select(t => t.DispositionNoteId);
var lastAmountPayment = _dbContext.GarmentInvoicePurchasingDispositionItems.Where(s => dataDispositionIds.Contains(s.DispositionId)).ToList();
data.ForEach(entity =>
{
entity.TotalPaidPaymentBefore = lastAmountPayment.Where(t => t.DispositionId == entity.DispositionNoteId).Sum(t => t.TotalPaid);
entity.DiffTotalPaidPayment = entity.TotalPaid - entity.TotalPaidPaymentBefore;
});
return(new ReadResponse <IndexDto>(data, count, orderDictionary, new List <string>()));
}
private IQueryable <GarmentPurchasingExpeditionReportViewModel> GetQuery(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset startDate, DateTimeOffset endDate, DateTimeOffset startDateAccounting, DateTimeOffset endDateAccounting)
{
var result = _dbContext.GarmentPurchasingExpeditions.AsQueryable();
if (position > 0)
{
result = result.Where(entity => entity.Position == position);
}
switch (position)
{
case GarmentPurchasingExpeditionPosition.Purchasing:
result = result.Where(entity => entity.SendToPurchasingDate.GetValueOrDefault() >= startDate && entity.SendToPurchasingDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.SendToVerification:
result = result.Where(entity => entity.SendToVerificationDate.GetValueOrDefault() >= startDate && entity.SendToVerificationDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.VerificationAccepted:
result = result.Where(entity => entity.VerificationAcceptedDate.GetValueOrDefault() >= startDate && entity.VerificationAcceptedDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.SendToCashier:
result = result.Where(entity => entity.SendToCashierDate.GetValueOrDefault() >= startDate && entity.SendToCashierDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.CashierAccepted:
result = result.Where(entity => entity.CashierAcceptedDate.GetValueOrDefault() >= startDate && entity.CashierAcceptedDate.GetValueOrDefault() <= endDate);
break;
case GarmentPurchasingExpeditionPosition.AccountingAccepted:
result = result.Where(entity => entity.AccountingAcceptedDate.GetValueOrDefault() >= startDate && entity.AccountingAcceptedDate.GetValueOrDefault() <= endDate);
break;
default:
result = result.Where(entity => entity.SendToVerificationDate.GetValueOrDefault() >= startDate && entity.SendToVerificationDate.GetValueOrDefault() <= endDate);
break;
}
if (internalNoteId > 0)
{
result = result.Where(entity => entity.InternalNoteId == internalNoteId);
}
if (supplierId > 0)
{
result = result.Where(entity => entity.SupplierId == supplierId);
}
if (position == GarmentPurchasingExpeditionPosition.Purchasing)
{
result = result.Where(entity => !string.IsNullOrWhiteSpace(entity.SendToPurchasingRemark));
}
if (internalNoteId > 0)
{
result = result.Where(entity => entity.InternalNoteId == internalNoteId);
}
result = result.Where(entity => entity.SendToAccountingDate.GetValueOrDefault() >= startDateAccounting && entity.SendToAccountingDate.GetValueOrDefault() <= endDateAccounting);
var query = from a in result
join b in _dbContext.DPPVATBankExpenditureNoteItems on a.InternalNoteId equals b.InternalNoteId into l
from b in l.DefaultIfEmpty()
join c in _dbContext.DPPVATBankExpenditureNotes on b.DPPVATBankExpenditureNoteId equals c.Id into m
from c in m.DefaultIfEmpty()
select new GarmentPurchasingExpeditionReportViewModel
{
InternalNoteId = a.InternalNoteId,
InternalNoteNo = a.InternalNoteNo,
InternalNoteDate = a.InternalNoteDate,
InternalNoteDueDate = a.InternalNoteDueDate,
SupplierId = a.SupplierId,
SupplierName = a.SupplierName,
DPP = a.DPP,
VAT = a.VAT,
CorrectionAmount = a.CorrectionAmount,
IncomeTax = a.IncomeTax,
TotalPaid = a.TotalPaid,
CurrencyId = a.CurrencyId,
CurrencyCode = a.CurrencyCode,
PaymentType = a.PaymentType,
InvoicesNo = a.InvoicesNo,
PaymentMethod = a.PaymentMethod,
PaymentDueDays = a.PaymentDueDays,
Remark = a.Remark,
Position = a.Position,
SendToVerificationDate = a.SendToVerificationDate,
SendToVerificationBy = a.SendToVerificationBy,
VerificationAcceptedDate = a.VerificationAcceptedDate,
VerificationAcceptedBy = a.VerificationAcceptedBy,
SendToCashierDate = a.SendToCashierDate,
SendToCashierBy = a.SendToCashierBy,
CashierAcceptedDate = a.CashierAcceptedDate,
CashierAcceptedBy = a.CashierAcceptedBy,
SendToPurchasingDate = a.SendToPurchasingDate,
SendToPurchasingBy = a.SendToPurchasingBy,
SendToPurchasingRemark = a.SendToPurchasingRemark,
SendToAccountingDate = a.SendToAccountingDate,
SendToAccountingBy = a.SendToAccountingBy,
AccountingAcceptedDate = a.AccountingAcceptedDate,
AccountingAcceptedBy = a.AccountingAcceptedBy,
VerificationSendDate =
(a.Position == GarmentPurchasingExpeditionPosition.SendToCashier || a.Position == GarmentPurchasingExpeditionPosition.CashierAccepted || a.Position == GarmentPurchasingExpeditionPosition.DispositionPayment? a.SendToCashierDate :
a.Position == GarmentPurchasingExpeditionPosition.SendToAccounting || a.Position == GarmentPurchasingExpeditionPosition.AccountingAccepted? a.SendToAccountingDate : a.SendToPurchasingDate
),
BankExpenditureNoteNo = b == null? "-":(a.CashierAcceptedDate == null? "-": c.DocumentNo),
BankExpenditureNoteDate = a.CashierAcceptedDate != null ? c.Date : (DateTimeOffset?)null
};
return(query);
}
public PositionOption(GarmentPurchasingExpeditionPosition position)
{
Value = position;
Label = position.ToDescriptionString();
}
public List <GarmentPurchasingExpeditionModel> GetReport(int internalNoteId, int supplierId, GarmentPurchasingExpeditionPosition position, DateTimeOffset startDate, DateTimeOffset endDate)
{
var query = GetQuery(internalNoteId, supplierId, position, startDate, endDate);
return(query.ToList());
}
public IndexDto(int id, string dispositionNoteNo, DateTimeOffset dispositionNoteDate, int dispositionNoteId, string supplierName, GarmentPurchasingExpeditionPosition position, double totalPaid, string currencyCode, string remark, DateTimeOffset verifiedDateSend, DateTimeOffset verifiedDateReceived, string sendToPurchasingRemark, DateTimeOffset createdDate)
{
Id = id;
DispositionNoteNo = dispositionNoteNo;
DispositionNoteDate = dispositionNoteDate;
DispositionNoteId = dispositionNoteId;
SupplierName = supplierName;
TotalPaid = totalPaid;
CurrencyCode = currencyCode;
Remark = remark;
Status = position.ToDescriptionString();
VerifiedDateReceived = verifiedDateReceived;
VerifiedDateSend = verifiedDateSend;
SendToPurchasingRemark = sendToPurchasingRemark;
CreatedDate = createdDate;
}
private static void SetTitle(ExcelWorksheet worksheet, DateTimeOffset startDate, DateTimeOffset endDate, int timezoneOffset, GarmentPurchasingExpeditionPosition position)
{
var company = "PT DAN LIRIS";
var title = "LAPORAN BUKTI PENGELUARAN BANK DPP + PPN";
var cultureInfo = new CultureInfo("id-ID");
var date = $"PERIODE: {startDate.DateTime.AddHours(timezoneOffset).ToString("dd MMMM yyyy", cultureInfo)} sampai dengan {endDate.DateTime.AddHours(timezoneOffset).ToString("dd MMMM yyyy", cultureInfo)}";
worksheet.Cells["A1"].Value = company;
worksheet.Cells["A1:AC1"].Merge = true;
worksheet.Cells["A1:AC1"].Style.Font.Size = 20;
worksheet.Cells["A1:AC1"].Style.Font.Bold = true;
worksheet.Cells["A2"].Value = title;
worksheet.Cells["A2:AC2"].Merge = true;
worksheet.Cells["A2:AC2"].Style.Font.Size = 20;
worksheet.Cells["A2:AC2"].Style.Font.Bold = true;
worksheet.Cells["A3"].Value = date;
worksheet.Cells["A3:AC3"].Merge = true;
worksheet.Cells["A3:AC3"].Style.Font.Size = 20;
worksheet.Cells["A3:AC3"].Style.Font.Bold = true;
}
