public GarmentMonitoringCuttingDto(GarmentMonitoringCuttingDto garmentMonitoringCuttingDto) { Id = garmentMonitoringCuttingDto.Id; roJob = garmentMonitoringCuttingDto.roJob; article = garmentMonitoringCuttingDto.article; qtyOrder = garmentMonitoringCuttingDto.qtyOrder; productCode = garmentMonitoringCuttingDto.productCode; style = garmentMonitoringCuttingDto.style; hours = garmentMonitoringCuttingDto.hours; cuttingQtyMeter = garmentMonitoringCuttingDto.cuttingQtyMeter; stock = garmentMonitoringCuttingDto.stock; cuttingQtyPcs = garmentMonitoringCuttingDto.cuttingQtyPcs; fc = garmentMonitoringCuttingDto.fc; expenditure = garmentMonitoringCuttingDto.expenditure; remainQty = garmentMonitoringCuttingDto.remainQty; nominal = garmentMonitoringCuttingDto.nominal; }
public async Task <GarmentMonitoringCuttingListViewModel> Handle(GetMonitoringCuttingQuery 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 QueryRoCuttingIn = (from a in garmentCuttingInRepository.Query where a.UnitId == request.unit && a.CuttingInDate <= dateTo select a.RONo).Distinct(); var QueryRoAvalComp = (from a in garmentAvalComponentRepository.Query where a.UnitId == request.unit && a.Date <= dateTo select a.RONo).Distinct(); var QueryRo = QueryRoCuttingOut.Union(QueryRoCuttingIn).Union(QueryRoAvalComp).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 sumFCs = (from a in garmentCuttingInRepository.Query where /*(request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && */ a.CuttingType == "Main Fabric" && a.UnitId == request.unit && a.CuttingInDate <= dateTo select new { a.FC, a.RONo }) .GroupBy(x => new { x.RONo }, (key, group) => new ViewFC { RO = key.RONo, FC = group.Sum(s => s.FC), Count = group.Count() }); var QueryCuttingIn = from a in (from aa in garmentCuttingInRepository.Query where aa.UnitId == request.unit && aa.CuttingInDate <= dateTo select aa) join b in garmentCuttingInItemRepository.Query on a.Identity equals b.CutInId join c in garmentCuttingInDetailRepository.Query on b.Identity equals c.CutInItemId select new monitoringView { buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), fc = (from cost in sumFCs where cost.RO == a.RONo select cost.FC / cost.Count).FirstOrDefault(), cuttingQtyMeter = 0, remainQty = 0, stock = a.CuttingInDate < dateFrom ? c.CuttingInQuantity : 0, cuttingQtyPcs = a.CuttingInDate >= dateFrom ? c.CuttingInQuantity : 0, roJob = a.RONo, article = a.Article, productCode = c.ProductCode, 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(), hours = (from cost in costCalculation.data where cost.ro == a.RONo select cost.hours).FirstOrDefault(), expenditure = 0 }; var QueryCuttingOut = from a in (from aa in garmentCuttingOutRepository.Query where aa.UnitFromId == request.unit && aa.CuttingOutDate <= dateTo select aa) join b in garmentCuttingOutItemRepository.Query on a.Identity equals b.CutOutId select new monitoringView { buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), fc = (from cost in sumFCs where cost.RO == a.RONo select cost.FC / cost.Count).FirstOrDefault(), cuttingQtyMeter = 0, remainQty = 0, stock = a.CuttingOutDate < dateFrom ? -b.TotalCuttingOut : 0, cuttingQtyPcs = 0, roJob = a.RONo, article = a.Article, productCode = b.ProductCode, 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(), hours = (from cost in costCalculation.data where cost.ro == a.RONo select cost.hours).FirstOrDefault(), expenditure = a.CuttingOutDate >= dateFrom ? b.TotalCuttingOut : 0 }; var QueryAvalComp = from a in (from aa in garmentAvalComponentRepository.Query where aa.UnitId == request.unit && aa.Date <= dateTo select aa) join b in garmentAvalComponentItemRepository.Query on a.Identity equals b.AvalComponentId select new monitoringView { buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), fc = (from cost in sumFCs where cost.RO == a.RONo select cost.FC / cost.Count).FirstOrDefault(), cuttingQtyMeter = 0, remainQty = 0, stock = a.Date < dateFrom ? -b.Quantity : 0, cuttingQtyPcs = 0, roJob = a.RONo, article = a.Article, productCode = b.ProductCode, 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(), hours = (from cost in costCalculation.data where cost.ro == a.RONo select cost.hours).FirstOrDefault(), expenditure = a.Date >= dateFrom ? b.Quantity : 0 }; var queryNow = QueryCuttingIn.Union(QueryCuttingOut).Union(QueryAvalComp); var querySum = queryNow.ToList().GroupBy(x => new { x.price, x.fc, x.buyerCode, x.qtyOrder, x.roJob, x.article, x.productCode, x.style, x.hours }, (key, group) => new { QtyOrder = key.qtyOrder, RoJob = key.roJob, Fc = key.fc, Stock = group.Sum(s => s.stock), ProductCode = key.productCode, buyer = key.buyerCode, bPrice = key.price, Article = key.article, Style = key.style, CuttingQtyPcs = group.Sum(s => s.cuttingQtyPcs), CuttingQtyMeter = group.Sum(s => s.cuttingQtyMeter), Expenditure = group.Sum(s => s.expenditure), Hours = key.hours }).OrderBy(s => s.RoJob); GarmentMonitoringCuttingListViewModel listViewModel = new GarmentMonitoringCuttingListViewModel(); List <GarmentMonitoringCuttingDto> monitoringCuttingDtos = new List <GarmentMonitoringCuttingDto>(); foreach (var item in querySum) { GarmentMonitoringCuttingDto cuttingDto = new GarmentMonitoringCuttingDto { roJob = item.RoJob, article = item.Article, productCode = item.ProductCode, style = item.Style, hours = item.Hours, qtyOrder = item.QtyOrder, cuttingQtyPcs = item.CuttingQtyPcs, expenditure = item.Expenditure, stock = item.Stock, remainQty = item.Stock + item.CuttingQtyPcs - item.Expenditure, fc = Math.Round(item.Fc, 2), cuttingQtyMeter = Math.Round(item.Fc * item.CuttingQtyPcs, 2), price = Math.Round(Convert.ToDecimal(item.bPrice), 2), buyerCode = item.buyer }; monitoringCuttingDtos.Add(cuttingDto); } var data = from a in monitoringCuttingDtos where a.stock > 0 || a.expenditure > 0 || a.cuttingQtyPcs > 0 || a.remainQty > 0 select a; listViewModel.garmentMonitorings = data.ToList(); return(listViewModel); }
public async Task <MemoryStream> Handle(GetXlsCuttingQuery 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 QueryRoCuttingIn = (from a in garmentCuttingInRepository.Query where a.UnitId == request.unit && a.CuttingInDate <= dateTo select a.RONo).Distinct(); var QueryRoAvalComp = (from a in garmentAvalComponentRepository.Query where a.UnitId == request.unit && a.Date <= dateTo select a.RONo).Distinct(); var QueryRo = QueryRoCuttingOut.Union(QueryRoCuttingIn).Union(QueryRoAvalComp).Distinct(); List <string> _ro = new List <string>(); foreach (var item in QueryRo) { _ro.Add(item); } CostCalculationGarmentDataProductionReport costCalculation = await GetDataCostCal(_ro, request.token); var _unitName = (from a in garmentCuttingOutRepository.Query where a.UnitId == request.unit select a.UnitName).FirstOrDefault(); 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 sumFCs = (from a in garmentCuttingInRepository.Query where /*(request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && */ a.CuttingType == "Main Fabric" && a.UnitId == request.unit && a.CuttingInDate <= dateTo select new { a.FC, a.RONo }) .GroupBy(x => new { x.RONo }, (key, group) => new ViewFC { RO = key.RONo, FC = group.Sum(s => s.FC), Count = group.Count() }); var QueryCuttingIn = from a in (from aa in garmentCuttingInRepository.Query where aa.UnitId == request.unit && aa.CuttingInDate <= dateTo select aa) join b in garmentCuttingInItemRepository.Query on a.Identity equals b.CutInId join c in garmentCuttingInDetailRepository.Query on b.Identity equals c.CutInItemId select new monitoringView { buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), fc = (from cost in sumFCs where cost.RO == a.RONo select cost.FC / cost.Count).FirstOrDefault(), cuttingQtyMeter = 0, remainQty = 0, stock = a.CuttingInDate < dateFrom ? c.CuttingInQuantity : 0, cuttingQtyPcs = a.CuttingInDate >= dateFrom ? c.CuttingInQuantity : 0, roJob = a.RONo, article = a.Article, productCode = c.ProductCode, 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(), hours = (from cost in costCalculation.data where cost.ro == a.RONo select cost.hours).FirstOrDefault(), expenditure = 0 }; var QueryCuttingOut = from a in (from aa in garmentCuttingOutRepository.Query where aa.UnitFromId == request.unit && aa.CuttingOutDate <= dateTo select aa) join b in garmentCuttingOutItemRepository.Query on a.Identity equals b.CutOutId select new monitoringView { buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), fc = (from cost in sumFCs where cost.RO == a.RONo select cost.FC / cost.Count).FirstOrDefault(), cuttingQtyMeter = 0, remainQty = 0, stock = a.CuttingOutDate < dateFrom ? -b.TotalCuttingOut : 0, cuttingQtyPcs = 0, roJob = a.RONo, article = a.Article, productCode = b.ProductCode, 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(), hours = (from cost in costCalculation.data where cost.ro == a.RONo select cost.hours).FirstOrDefault(), expenditure = a.CuttingOutDate >= dateFrom ? b.TotalCuttingOut : 0 }; var QueryAvalComp = from a in (from aa in garmentAvalComponentRepository.Query where aa.UnitId == request.unit && aa.Date <= dateTo select aa) join b in garmentAvalComponentItemRepository.Query on a.Identity equals b.AvalComponentId select new monitoringView { buyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), fc = (from cost in sumFCs where cost.RO == a.RONo select cost.FC / cost.Count).FirstOrDefault(), cuttingQtyMeter = 0, remainQty = 0, stock = a.Date < dateFrom ? -b.Quantity : 0, cuttingQtyPcs = 0, roJob = a.RONo, article = a.Article, productCode = b.ProductCode, 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(), hours = (from cost in costCalculation.data where cost.ro == a.RONo select cost.hours).FirstOrDefault(), expenditure = a.Date >= dateFrom ? b.Quantity : 0 }; var queryNow = QueryCuttingIn.Union(QueryCuttingOut).Union(QueryAvalComp); var querySum = queryNow.ToList().GroupBy(x => new { x.price, x.fc, x.buyerCode, x.qtyOrder, x.roJob, x.article, x.productCode, x.style, x.hours }, (key, group) => new { QtyOrder = key.qtyOrder, RoJob = key.roJob, Fc = key.fc, Stock = group.Sum(s => s.stock), ProductCode = key.productCode, buyer = key.buyerCode, bPrice = key.price, Article = key.article, Style = key.style, CuttingQtyPcs = group.Sum(s => s.cuttingQtyPcs), CuttingQtyMeter = group.Sum(s => s.cuttingQtyMeter), Expenditure = group.Sum(s => s.expenditure), Hours = key.hours }).OrderBy(s => s.RoJob); GarmentMonitoringCuttingListViewModel listViewModel = new GarmentMonitoringCuttingListViewModel(); List <GarmentMonitoringCuttingDto> monitoringCuttingDtos = new List <GarmentMonitoringCuttingDto>(); foreach (var item in querySum) { GarmentMonitoringCuttingDto cuttingDto = new GarmentMonitoringCuttingDto { roJob = item.RoJob, article = item.Article, productCode = item.ProductCode, style = item.Style, hours = item.Hours, qtyOrder = item.QtyOrder, cuttingQtyPcs = Math.Round(item.CuttingQtyPcs, 2), expenditure = Math.Round(item.Expenditure, 2), stock = Math.Round(item.Stock, 2), remainQty = Math.Round(item.Stock + item.CuttingQtyPcs - item.Expenditure, 2), fc = Math.Round(item.Fc, 2), cuttingQtyMeter = Math.Round(item.Fc * item.CuttingQtyPcs, 2), price = Math.Round(Convert.ToDecimal(item.bPrice)), buyerCode = item.buyer, nominal = item.bPrice * Convert.ToDecimal(Math.Round(item.Stock + item.CuttingQtyPcs - item.Expenditure, 2)) }; monitoringCuttingDtos.Add(cuttingDto); } var data = from a in monitoringCuttingDtos where a.stock > 0 || a.expenditure > 0 || a.cuttingQtyPcs > 0 || a.remainQty > 0 select a; double stocks = 0; double cuttingQtyPcs = 0; double expenditure = 0; decimal nominals = 0; foreach (var item in data) { stocks += item.stock; cuttingQtyPcs += item.cuttingQtyPcs; expenditure += item.expenditure; nominals += item.nominal; } monitoringCuttingDtos = data.ToList(); GarmentMonitoringCuttingDto cuttingDtos = new GarmentMonitoringCuttingDto { roJob = "", article = "", productCode = "", style = "", hours = 0, qtyOrder = 0, cuttingQtyPcs = cuttingQtyPcs, expenditure = expenditure, stock = stocks, remainQty = stocks + cuttingQtyPcs - expenditure, fc = 0, cuttingQtyMeter = 0, price = 0, buyerCode = "", nominal = nominals }; monitoringCuttingDtos.Add(cuttingDtos); listViewModel.garmentMonitorings = monitoringCuttingDtos; 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 Barang", 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 = "FC", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Hours", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Hasil Potong (M)", DataType = typeof(double) }); 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 Potong", 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 = "Sisa Nominal", DataType = typeof(double) }); int counter = 5; if (listViewModel.garmentMonitorings.Count > 0) { foreach (var report in listViewModel.garmentMonitorings) { reportDataTable.Rows.Add(report.roJob, report.article, report.productCode, report.buyerCode, report.qtyOrder, report.style, Math.Round(report.fc), report.hours, report.cuttingQtyMeter, Math.Round(report.price, 2), report.stock, report.cuttingQtyPcs, report.expenditure, report.remainQty, report.nominal); counter++; } } using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet 1"); worksheet.Column(5).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells["E" + 2 + ":E" + counter + ""].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells["G" + 6 + ":O" + counter + ""].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells["G" + 6 + ":O" + counter + ""].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells["A" + 5 + ":O" + counter + ""].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells["A" + 5 + ":O" + counter + ""].Style.Border.Top.Style = ExcelBorderStyle.Thin; worksheet.Cells["A" + 5 + ":O" + counter + ""].Style.Border.Left.Style = ExcelBorderStyle.Thin; worksheet.Cells["A" + 5 + ":O" + counter + ""].Style.Border.Right.Style = ExcelBorderStyle.Thin; worksheet.Cells["I" + (counter) + ":O" + (counter) + ""].Style.Font.Bold = true; worksheet.Cells["A" + 1 + ":O" + 1 + ""].Style.Font.Bold = true; worksheet.Cells["A1"].Value = "Report Cutting"; worksheet.Cells["A" + 1 + ":O" + 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 + ":O" + 1 + ""].Merge = true; worksheet.Cells["A" + 2 + ":O" + 2 + ""].Merge = true; worksheet.Cells["A" + 3 + ":O" + 3 + ""].Merge = true; worksheet.Cells["A" + 1 + ":O" + 3 + ""].Style.Font.Size = 15; worksheet.Cells["A" + 1 + ":O" + 5 + ""].Style.Font.Bold = true; worksheet.Cells["A" + 1 + ":O" + 5 + ""].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells["A" + 1 + ":O" + 5 + ""].Style.VerticalAlignment = ExcelVerticalAlignment.Center; worksheet.Cells["A5"].LoadFromDataTable(reportDataTable, true); var stream = new MemoryStream(); if (request.type != "bookkeeping") { worksheet.Column(4).Hidden = true; worksheet.Column(10).Hidden = true; worksheet.Cells["A" + (counter) + ":i" + (counter) + ""].Merge = true; } else { worksheet.Cells["A" + (counter) + ":J" + (counter) + ""].Merge = true; } package.SaveAs(stream); return(stream); } }