public GarmentMonitoringLoadingDto(GarmentMonitoringLoadingDto garmentMonitoring)
{
Id = garmentMonitoring.Id;
roJob = garmentMonitoring.roJob;
article = garmentMonitoring.article;
qtyOrder = garmentMonitoring.qtyOrder;
stock = garmentMonitoring.stock;
cuttingQtyPcs = garmentMonitoring.cuttingQtyPcs;
loadingQtyPcs = garmentMonitoring.loadingQtyPcs;
uomUnit = garmentMonitoring.uomUnit;
remainQty = garmentMonitoring.remainQty;
style = garmentMonitoring.style;
buyerCode = garmentMonitoring.buyerCode;
price = garmentMonitoring.price;
nominal = garmentMonitoring.nominal;
}
public async Task <MemoryStream> Handle(GetXlsLoadingQuery request, CancellationToken cancellationToken)
{
DateTimeOffset dateFrom = new DateTimeOffset(request.dateFrom, new TimeSpan(7, 0, 0));
DateTimeOffset dateTo = new DateTimeOffset(request.dateTo, new TimeSpan(7, 0, 0));
var QueryRoCuttingOut = (from a in garmentCuttingOutRepository.Query
where a.UnitId == request.unit && a.CuttingOutDate <= dateTo
select a.RONo).Distinct();
var QueryRoLoading = (from a in garmentLoadingRepository.Query
where a.UnitId == request.unit && a.LoadingDate <= dateTo
select a.RONo).Distinct();
var QueryRo = QueryRoCuttingOut.Union(QueryRoLoading).Distinct();
List <string> _ro = new List <string>();
foreach (var item in QueryRo)
{
_ro.Add(item);
}
CostCalculationGarmentDataProductionReport costCalculation = await GetDataCostCal(_ro, request.token);
var sumbasicPrice = (from a in garmentPreparingRepository.Query
join b in garmentPreparingItemRepository.Query on a.Identity equals b.GarmentPreparingId
where /*(request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) &&*/
a.UnitId == request.unit
select new { a.RONo, b.BasicPrice })
.GroupBy(x => new { x.RONo }, (key, group) => new ViewBasicPrices
{
RO = key.RONo,
BasicPrice = Math.Round(Convert.ToDecimal(group.Sum(s => s.BasicPrice)), 2),
Count = group.Count()
});
var _unitName = (from a in garmentCuttingOutRepository.Query
where a.UnitId == request.unit
select a.UnitName).FirstOrDefault();
var QueryCuttingOut = from a in (from aa in garmentCuttingOutRepository.Query
where aa.UnitId == request.unit && aa.CuttingOutDate <= dateTo
select aa)
join b in garmentCuttingOutItemRepository.Query on a.Identity equals b.CutOutId
select new monitoringView {
price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), loadingQtyPcs = 0, uomUnit = "PCS", remainQty = 0, stock = a.CuttingOutDate < dateFrom ? b.TotalCuttingOut : 0, cuttingQtyPcs = a.CuttingOutDate >= dateFrom ? b.TotalCuttingOut : 0, roJob = a.RONo, article = a.Article, qtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), style = (from cost in costCalculation.data where cost.ro == a.RONo select cost.comodityName).FirstOrDefault()
};
var QueryLoading = from a in (from aa in garmentLoadingRepository.Query
where aa.UnitId == request.unit && aa.LoadingDate <= dateTo
select aa)
join b in garmentLoadingItemRepository.Query on a.Identity equals b.LoadingId
select new monitoringView {
price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), loadingQtyPcs = a.LoadingDate >= dateFrom ? b.Quantity : 0, cuttingQtyPcs = 0, uomUnit = "PCS", remainQty = 0, stock = a.LoadingDate < dateFrom ? -b.Quantity : 0, roJob = a.RONo, article = a.Article, qtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), style = (from cost in costCalculation.data where cost.ro == a.RONo select cost.comodityName).FirstOrDefault()
};
var queryNow = QueryCuttingOut.Union(QueryLoading);
var querySum = queryNow.ToList().GroupBy(x => new { x.price, x.buyerCode, x.qtyOrder, x.roJob, x.article, x.uomUnit, x.style }, (key, group) => new
{
QtyOrder = key.qtyOrder,
RoJob = key.roJob,
buyer = key.buyerCode,
price = key.price,
Style = key.style,
Stock = group.Sum(s => s.stock),
UomUnit = key.uomUnit,
Article = key.article,
CuttingQtyPcs = group.Sum(s => s.cuttingQtyPcs),
Loading = group.Sum(s => s.loadingQtyPcs)
}).OrderBy(s => s.RoJob);
GarmentMonitoringLoadingListViewModel listViewModel = new GarmentMonitoringLoadingListViewModel();
List <GarmentMonitoringLoadingDto> monitoringDtos = new List <GarmentMonitoringLoadingDto>();
foreach (var item in querySum)
{
GarmentMonitoringLoadingDto dtos = new GarmentMonitoringLoadingDto
{
roJob = item.RoJob,
article = item.Article,
buyerCode = item.buyer,
uomUnit = item.UomUnit,
qtyOrder = item.QtyOrder,
cuttingQtyPcs = Math.Round(item.CuttingQtyPcs, 2),
loadingQtyPcs = Math.Round(item.Loading, 2),
stock = Math.Round(item.Stock, 2),
style = item.Style,
price = Math.Round(item.price, 2),
remainQty = Math.Round(item.Stock + item.CuttingQtyPcs - item.Loading, 2),
nominal = Math.Round((Convert.ToDecimal(item.Stock + item.CuttingQtyPcs - item.Loading)) * item.price, 2)
};
monitoringDtos.Add(dtos);
}
listViewModel.garmentMonitorings = monitoringDtos;
var data = from a in monitoringDtos
where a.stock > 0 || a.loadingQtyPcs > 0 || a.cuttingQtyPcs > 0 || a.remainQty > 0
select a;
monitoringDtos = data.ToList();
double stocks = 0;
double cuttingQtyPcs = 0;
double loadingQtyPcs = 0;
decimal nominals = 0;
foreach (var item in data)
{
stocks += item.stock;
cuttingQtyPcs += item.cuttingQtyPcs;
loadingQtyPcs += item.loadingQtyPcs;
nominals += item.nominal;
}
GarmentMonitoringLoadingDto dto = new GarmentMonitoringLoadingDto
{
roJob = "",
article = "",
buyerCode = "",
uomUnit = "",
qtyOrder = 0,
cuttingQtyPcs = cuttingQtyPcs,
loadingQtyPcs = loadingQtyPcs,
stock = stocks,
style = "",
price = 0,
remainQty = stocks + cuttingQtyPcs - loadingQtyPcs,
nominal = nominals
};
monitoringDtos.Add(dto);
listViewModel.garmentMonitorings = monitoringDtos;
var reportDataTable = new DataTable();
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "RO JOB", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Article", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Kode Buyer", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Qty Order", DataType = typeof(double)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Style", DataType = typeof(string)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Harga (M)", DataType = typeof(double)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Stock Awal", DataType = typeof(double)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Barang MasuL", DataType = typeof(double)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Barang Keluar", DataType = typeof(double)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Sisa", DataType = typeof(double)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Nominal Sisa", DataType = typeof(double)
});
reportDataTable.Columns.Add(new DataColumn()
{
ColumnName = "Satuan", DataType = typeof(string)
});
int counter = 5;
if (listViewModel.garmentMonitorings.Count > 0)
{
foreach (var report in listViewModel.garmentMonitorings)
{
reportDataTable.Rows.Add(report.roJob, report.article, report.buyerCode, report.qtyOrder, report.style, Math.Round(report.price, 2), report.stock, report.cuttingQtyPcs, report.loadingQtyPcs, report.remainQty, report.nominal, report.uomUnit);
counter++;
}
}
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet 1");
worksheet.Cells["A" + 5 + ":L" + 5 + ""].Style.Font.Bold = true;
worksheet.Cells["A1"].Value = "Report Loading "; worksheet.Cells["A" + 1 + ":L" + 1 + ""].Merge = true;
worksheet.Cells["A2"].Value = "Periode " + dateFrom.ToString("dd-MM-yyyy") + " s/d " + dateTo.ToString("dd-MM-yyyy");
worksheet.Cells["A3"].Value = "Konfeksi " + _unitName;
worksheet.Cells["A" + 1 + ":L" + 1 + ""].Merge = true;
worksheet.Cells["A" + 2 + ":L" + 2 + ""].Merge = true;
worksheet.Cells["A" + 3 + ":L" + 3 + ""].Merge = true;
worksheet.Cells["A" + 1 + ":L" + 3 + ""].Style.Font.Size = 15;
worksheet.Cells["A" + 1 + ":L" + 5 + ""].Style.Font.Bold = true;
worksheet.Cells["A" + 1 + ":L" + 5 + ""].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["A" + 1 + ":L" + 5 + ""].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells["A5"].LoadFromDataTable(reportDataTable, true);
worksheet.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells["D" + 6 + ":D" + counter + ""].Style.Numberformat.Format = "#,##0.00";
worksheet.Column(6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells["F" + 6 + ":K" + counter + ""].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells["F" + 6 + ":K" + counter + ""].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells["F" + 6 + ":F" + counter + ""].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells["A" + 5 + ":L" + counter + ""].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A" + 5 + ":L" + counter + ""].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A" + 5 + ":L" + counter + ""].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A" + 5 + ":L" + counter + ""].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["F" + (counter) + ":K" + (counter) + ""].Style.Font.Bold = true;
worksheet.Cells["A" + 1 + ":L" + 1 + ""].Style.Font.Bold = true;
var stream = new MemoryStream();
if (request.type != "bookkeeping")
{
worksheet.Cells["A" + (counter) + ":E" + (counter) + ""].Merge = true;
worksheet.Column(3).Hidden = true;
worksheet.Column(6).Hidden = true;
}
else
{
worksheet.Cells["A" + (counter) + ":F" + (counter) + ""].Merge = true;
}
package.SaveAs(stream);
return(stream);
}
}
public async Task <GarmentMonitoringLoadingListViewModel> Handle(GetMonitoringLoadingQuery request, CancellationToken cancellationToken)
{
DateTimeOffset dateFrom = new DateTimeOffset(request.dateFrom, new TimeSpan(7, 0, 0));
DateTimeOffset dateTo = new DateTimeOffset(request.dateTo, new TimeSpan(7, 0, 0));
var QueryRoCuttingOut = (from a in garmentCuttingOutRepository.Query
where a.UnitId == request.unit && a.CuttingOutDate <= dateTo
select a.RONo).Distinct();
var QueryRoLoading = (from a in garmentLoadingRepository.Query
where a.UnitId == request.unit && a.LoadingDate <= dateTo
select a.RONo).Distinct();
var QueryRo = QueryRoCuttingOut.Union(QueryRoLoading).Distinct();
List <string> _ro = new List <string>();
foreach (var item in QueryRo)
{
_ro.Add(item);
}
CostCalculationGarmentDataProductionReport costCalculation = await GetDataCostCal(_ro, request.token);
var sumbasicPrice = (from a in garmentPreparingRepository.Query
join b in garmentPreparingItemRepository.Query on a.Identity equals b.GarmentPreparingId
where /*(request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) &&*/
a.UnitId == request.unit
select new { a.RONo, b.BasicPrice })
.GroupBy(x => new { x.RONo }, (key, group) => new ViewBasicPrices
{
RO = key.RONo,
BasicPrice = Math.Round(Convert.ToDecimal(group.Sum(s => s.BasicPrice)), 2),
Count = group.Count()
});
var _unitName = (from a in garmentCuttingOutRepository.Query
where a.UnitId == request.unit
select a.UnitName).FirstOrDefault();
var QueryCuttingOut = from a in (from aa in garmentCuttingOutRepository.Query
where aa.UnitId == request.unit && aa.CuttingOutDate <= dateTo
select aa)
join b in garmentCuttingOutItemRepository.Query on a.Identity equals b.CutOutId
select new monitoringView {
price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), loadingQtyPcs = 0, uomUnit = "PCS", remainQty = 0, stock = a.CuttingOutDate < dateFrom ? b.TotalCuttingOut : 0, cuttingQtyPcs = a.CuttingOutDate >= dateFrom ? b.TotalCuttingOut : 0, roJob = a.RONo, article = a.Article, qtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), style = (from cost in costCalculation.data where cost.ro == a.RONo select cost.comodityName).FirstOrDefault()
};
var QueryLoading = from a in (from aa in garmentLoadingRepository.Query
where aa.UnitId == request.unit && aa.LoadingDate <= dateTo
select aa)
join b in garmentLoadingItemRepository.Query on a.Identity equals b.LoadingId
select new monitoringView {
price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), loadingQtyPcs = a.LoadingDate >= dateFrom ? b.Quantity : 0, cuttingQtyPcs = 0, uomUnit = "PCS", remainQty = 0, stock = a.LoadingDate < dateFrom ? -b.Quantity : 0, roJob = a.RONo, article = a.Article, qtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), style = (from cost in costCalculation.data where cost.ro == a.RONo select cost.comodityName).FirstOrDefault()
};
var queryNow = QueryCuttingOut.Union(QueryLoading);
var querySum = queryNow.ToList().GroupBy(x => new { x.price, x.buyerCode, x.qtyOrder, x.roJob, x.article, x.uomUnit, x.style }, (key, group) => new
{
QtyOrder = key.qtyOrder,
RoJob = key.roJob,
buyer = key.buyerCode,
price = key.price,
Style = key.style,
Stock = group.Sum(s => s.stock),
UomUnit = key.uomUnit,
Article = key.article,
CuttingQtyPcs = group.Sum(s => s.cuttingQtyPcs),
Loading = group.Sum(s => s.loadingQtyPcs)
}).OrderBy(s => s.RoJob);
GarmentMonitoringLoadingListViewModel listViewModel = new GarmentMonitoringLoadingListViewModel();
List <GarmentMonitoringLoadingDto> monitoringDtos = new List <GarmentMonitoringLoadingDto>();
foreach (var item in querySum)
{
GarmentMonitoringLoadingDto dtos = new GarmentMonitoringLoadingDto
{
roJob = item.RoJob,
article = item.Article,
buyerCode = item.buyer,
uomUnit = item.UomUnit,
qtyOrder = item.QtyOrder,
cuttingQtyPcs = Math.Round(item.CuttingQtyPcs, 2),
loadingQtyPcs = Math.Round(item.Loading, 2),
stock = Math.Round(item.Stock, 2),
style = item.Style,
price = Math.Round(item.price, 2),
remainQty = Math.Round(item.Stock + item.CuttingQtyPcs - item.Loading, 2),
nominal = Math.Round((Convert.ToDecimal(item.Stock + item.CuttingQtyPcs - item.Loading)) * item.price, 2)
};
monitoringDtos.Add(dtos);
}
listViewModel.garmentMonitorings = monitoringDtos;
var data = from a in monitoringDtos
where a.stock > 0 || a.loadingQtyPcs > 0 || a.cuttingQtyPcs > 0 || a.remainQty > 0
select a;
listViewModel.garmentMonitorings = data.ToList();
return(listViewModel);
}
