public GarmentMonitoringSewingDto(GarmentMonitoringSewingDto garmentMonitoring)
{
roJob = garmentMonitoring.roJob;
article = garmentMonitoring.article;
qtyOrder = garmentMonitoring.qtyOrder;
stock = garmentMonitoring.stock;
sewingOutQtyPcs = garmentMonitoring.sewingOutQtyPcs;
loadingQtyPcs = garmentMonitoring.loadingQtyPcs;
uomUnit = garmentMonitoring.uomUnit;
remainQty = garmentMonitoring.remainQty;
style = garmentMonitoring.style;
price = garmentMonitoring.price;
buyerCode = garmentMonitoring.buyerCode;
nominal = garmentMonitoring.nominal;
}
public async Task <GarmentMonitoringSewingListViewModel> Handle(GetMonitoringSewingQuery 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 QueryRoSewingOut = (from a in garmentSewingOutRepository.Query
where a.UnitId == request.unit && a.SewingOutDate <= 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 = QueryRoSewingOut.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 = Convert.ToDecimal(group.Sum(s => s.BasicPrice)),
Count = group.Count()
});
var QuerySewingOut = from a in (from aa in garmentSewingOutRepository.Query
where aa.UnitId == request.unit && aa.SewingOutDate <= dateTo
select aa)
join b in garmentSewingOutItemRepository.Query on a.Identity equals b.SewingOutId
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.SewingOutDate < dateFrom ? -b.Quantity : 0, sewingQtyPcs = b.Quantity, 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, sewingQtyPcs = 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 = QuerySewingOut.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,
Style = key.style,
buyer = key.buyerCode,
price = key.price,
Stock = group.Sum(s => s.stock),
UomUnit = key.uomUnit,
Article = key.article,
SewingQtyPcs = group.Sum(s => s.sewingQtyPcs),
Loading = group.Sum(s => s.loadingQtyPcs)
}).OrderBy(s => s.RoJob);
GarmentMonitoringSewingListViewModel listViewModel = new GarmentMonitoringSewingListViewModel();
List <GarmentMonitoringSewingDto> monitoringDtos = new List <GarmentMonitoringSewingDto>();
foreach (var item in querySum)
{
GarmentMonitoringSewingDto dto = new GarmentMonitoringSewingDto
{
roJob = item.RoJob,
article = item.Article,
buyerCode = item.buyer,
price = Math.Round(item.price, 2),
uomUnit = item.UomUnit,
qtyOrder = item.QtyOrder,
sewingOutQtyPcs = item.SewingQtyPcs,
loadingQtyPcs = item.Loading,
stock = item.Stock,
style = item.Style,
remainQty = item.Stock + item.Loading - item.SewingQtyPcs
};
monitoringDtos.Add(dto);
}
listViewModel.garmentMonitorings = monitoringDtos;
var data = from a in monitoringDtos
where a.stock > 0 || a.loadingQtyPcs > 0 || a.sewingOutQtyPcs > 0 || a.remainQty > 0
select a;
listViewModel.garmentMonitorings = data.ToList();
return(listViewModel);
}
public async Task <MemoryStream> Handle(GetXlsSewingQuery 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 QueryRoSewingOut = (from a in garmentSewingOutRepository.Query
where a.UnitId == request.unit && a.SewingOutDate <= 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 = QueryRoSewingOut.Union(QueryRoLoading).Distinct();
List <string> _ro = new List <string>();
foreach (var item in QueryRo)
{
_ro.Add(item);
}
var _unitName = (from a in garmentSewingOutRepository.Query
where a.UnitId == request.unit
select a.UnitName).FirstOrDefault();
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 = Convert.ToDecimal(group.Sum(s => s.BasicPrice)),
Count = group.Count()
});
var QuerySewingOut = from a in (from aa in garmentSewingOutRepository.Query
where aa.UnitId == request.unit && aa.SewingOutDate <= dateTo
select aa)
join b in garmentSewingOutItemRepository.Query on a.Identity equals b.SewingOutId
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.SewingOutDate < dateFrom ? -b.Quantity : 0, sewingQtyPcs = b.Quantity, 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, sewingQtyPcs = 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 = QuerySewingOut.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,
Style = key.style,
buyer = key.buyerCode,
price = key.price,
Stock = group.Sum(s => s.stock),
UomUnit = key.uomUnit,
Article = key.article,
SewingQtyPcs = group.Sum(s => s.sewingQtyPcs),
Loading = group.Sum(s => s.loadingQtyPcs)
}).OrderBy(s => s.RoJob);
GarmentMonitoringSewingListViewModel listViewModel = new GarmentMonitoringSewingListViewModel();
List <GarmentMonitoringSewingDto> monitoringDtos = new List <GarmentMonitoringSewingDto>();
foreach (var item in querySum)
{
GarmentMonitoringSewingDto dto = new GarmentMonitoringSewingDto
{
roJob = item.RoJob,
article = item.Article,
buyerCode = item.buyer,
price = Math.Round(item.price, 2),
uomUnit = item.UomUnit,
qtyOrder = item.QtyOrder,
sewingOutQtyPcs = Math.Round(item.SewingQtyPcs, 2),
loadingQtyPcs = Math.Round(item.Loading, 2),
stock = Math.Round(item.Stock, 2),
style = item.Style,
remainQty = Math.Round(item.Stock + item.Loading - item.SewingQtyPcs, 2),
nominal = Math.Round(Convert.ToDecimal(item.Stock + item.Loading - item.SewingQtyPcs) * item.price, 2)
};
monitoringDtos.Add(dto);
}
var data = from a in monitoringDtos
where a.stock > 0 || a.loadingQtyPcs > 0 || a.sewingOutQtyPcs > 0 || a.remainQty > 0
select a;
monitoringDtos = data.ToList();
double stocks = 0;
double loadingQtyPcs = 0;
double sewingOutQtyPcs = 0;
decimal nominals = 0;
foreach (var item in data)
{
stocks += item.stock;
loadingQtyPcs += item.loadingQtyPcs;
sewingOutQtyPcs += item.sewingOutQtyPcs;
nominals += item.nominal;
}
GarmentMonitoringSewingDto dtos = new GarmentMonitoringSewingDto
{
roJob = "",
article = "",
buyerCode = "",
uomUnit = "",
qtyOrder = 0,
sewingOutQtyPcs = sewingOutQtyPcs,
loadingQtyPcs = loadingQtyPcs,
stock = stocks,
style = "",
price = 0,
remainQty = stocks - sewingOutQtyPcs + loadingQtyPcs,
nominal = nominals
};
monitoringDtos.Add(dtos);
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 = "Hasil Sewing", 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, report.price, report.stock, report.loadingQtyPcs, report.sewingOutQtyPcs, report.remainQty, report.nominal, report.uomUnit);
counter++;
}
}
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet 1");
worksheet.Cells["D" + 6 + ":L" + counter + ""].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells["D" + 6 + ":L" + 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) + ":L" + (counter) + ""].Style.Font.Bold = true;
worksheet.Cells["A" + 5 + ":L" + 5 + ""].Style.Font.Bold = true;
worksheet.Cells["A1"].Value = "Report Sewing "; 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" + 2 + ""].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells["A5"].LoadFromDataTable(reportDataTable, 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);
}
}