internal CostCalculationGarmentDataProductionReport GetComodityQtyOrderHoursBuyerByRo(string ro) { CostCalculationGarmentDataProductionReport costCalculationGarmentDataProductionReport = new CostCalculationGarmentDataProductionReport(); var costCalculation = DbSet.Single(m => m.RO_Number == ro); costCalculationGarmentDataProductionReport.ro = costCalculation.RO_Number; costCalculationGarmentDataProductionReport.buyerCode = costCalculation.BuyerCode; costCalculationGarmentDataProductionReport.hours = costCalculation.SMV_Cutting; costCalculationGarmentDataProductionReport.comodityName = costCalculation.Commodity; costCalculationGarmentDataProductionReport.qtyOrder = costCalculation.Quantity; return(costCalculationGarmentDataProductionReport); }
public void should_Success_Instantiate() { CostCalculationGarmentDataProductionReport viewModel = new CostCalculationGarmentDataProductionReport() { buyerCode = "buyerCode", comodityName = "comodityName", hours = 1, qtyOrder = 1, ro = "ro" }; Assert.Equal("buyerCode", viewModel.buyerCode); Assert.Equal("comodityName", viewModel.comodityName); Assert.Equal(1, viewModel.hours); Assert.Equal(1, viewModel.qtyOrder); Assert.Equal("ro", viewModel.ro); }
public async Task <CostCalculationGarmentDataProductionReport> GetDataCostCal(List <string> ro, string token) { CostCalculationGarmentDataProductionReport costCalculationGarmentDataProductionReport = new CostCalculationGarmentDataProductionReport(); var listRO = string.Join(",", ro.Distinct()); var costCalculationUri = SalesDataSettings.Endpoint + $"cost-calculation-garments/data/{listRO}"; var httpResponse = await _http.GetAsync(costCalculationUri, token); var freeRO = new List <string>(); if (httpResponse.IsSuccessStatusCode) { var contentString = await httpResponse.Content.ReadAsStringAsync(); Dictionary <string, object> content = JsonConvert.DeserializeObject <Dictionary <string, object> >(contentString); var dataString = content.GetValueOrDefault("data").ToString(); var listData = JsonConvert.DeserializeObject <List <CostCalViewModel> >(dataString); foreach (var item in ro) { var data = listData.SingleOrDefault(s => s.ro == item); if (data != null) { costCalculationGarmentDataProductionReport.data.Add(data); } else { freeRO.Add(item); } } } HOrderDataProductionReport hOrderDataProductionReport = await GetDataHOrder(freeRO, token); Dictionary <string, string> comodities = new Dictionary <string, string>(); if (hOrderDataProductionReport.data.Count > 0) { var comodityCodes = hOrderDataProductionReport.data.Select(s => s.Kode).Distinct().ToList(); var filter = "{\"(" + string.Join(" || ", comodityCodes.Select(s => "Code==" + "\\\"" + s + "\\\"")) + ")\" : \"true\"}"; var masterGarmentComodityUri = MasterDataSettings.Endpoint + $"master/garment-comodities?filter=" + filter; var garmentComodityResponse = _http.GetAsync(masterGarmentComodityUri).Result; var garmentComodityResult = new GarmentComodityResult(); if (garmentComodityResponse.IsSuccessStatusCode) { garmentComodityResult = JsonConvert.DeserializeObject <GarmentComodityResult>(garmentComodityResponse.Content.ReadAsStringAsync().Result); //comodities = garmentComodityResult.data.ToDictionary(d => d.Code, d => d.Name); foreach (var comodity in garmentComodityResult.data) { comodities[comodity.Code] = comodity.Name; } } } foreach (var hOrder in hOrderDataProductionReport.data) { costCalculationGarmentDataProductionReport.data.Add(new CostCalViewModel { ro = hOrder.No, buyerCode = hOrder.Codeby, comodityName = comodities.GetValueOrDefault(hOrder.Kode), hours = (double)hOrder.Sh_Cut, qtyOrder = (double)hOrder.Qty }); } return(costCalculationGarmentDataProductionReport); }
public async Task <MemoryStream> Handle(GetXlsExpenditureGoodQuery 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 QueryRo = (from a in garmentExpenditureGoodRepository.Query where a.UnitId == request.unit && a.ExpenditureDate >= dateFrom && a.ExpenditureDate <= dateTo select a.RONo).Distinct(); List <string> _ro = new List <string>(); foreach (var item in QueryRo) { _ro.Add(item); } var _unitName = (from a in garmentPreparingRepository.Query where a.UnitId == request.unit select a.UnitName).FirstOrDefault(); CostCalculationGarmentDataProductionReport costCalculation = await GetDataCostCal(_ro, request.token); GarmentMonitoringExpenditureGoodListViewModel listViewModel = new GarmentMonitoringExpenditureGoodListViewModel(); List <GarmentMonitoringExpenditureGoodDto> monitoringDtos = new List <GarmentMonitoringExpenditureGoodDto>(); 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 Query = from a in (from aa in garmentExpenditureGoodRepository.Query where aa.UnitId == request.unit && aa.ExpenditureDate >= dateFrom && aa.ExpenditureDate <= dateTo select aa) join b in garmentExpenditureGoodItemRepository.Query on a.Identity equals b.ExpenditureGoodId where a.UnitId == request.unit && a.ExpenditureDate >= dateFrom && a.ExpenditureDate <= dateTo select new monitoringView { fc = (from aa in sumFCs where aa.RO == a.RONo select aa.FC / aa.Count).FirstOrDefault(), price = Convert.ToDecimal((from aa in sumbasicPrice where aa.RO == a.RONo select aa.BasicPrice / aa.Count).FirstOrDefault()), buyer = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), buyerArticle = a.BuyerCode + " " + a.Article, roNo = a.RONo, expenditureDate = a.ExpenditureDate, expenditureGoodNo = a.ExpenditureGoodNo, expenditureGoodType = a.ExpenditureType, invoice = a.Invoice, colour = b.Description, qty = b.Quantity, name = (from cost in costCalculation.data where cost.ro == a.RONo select cost.comodityName).FirstOrDefault() }; var querySum = Query.ToList().GroupBy(x => new { x.fc, x.buyer, x.buyerArticle, x.roNo, x.expenditureDate, x.expenditureGoodNo, x.expenditureGoodType, x.invoice, x.colour, x.name }, (key, group) => new { ros = key.roNo, buyer = key.buyerArticle, expenditureDates = key.expenditureDate, qty = group.Sum(s => s.qty), expendituregoodNo = key.expenditureGoodNo, expendituregoodTypes = key.expenditureGoodType, color = key.colour, price = group.Sum(s => s.price), buyerC = key.buyer, names = key.name, invoices = key.invoice, fcs = key.fc }).OrderBy(s => s.expendituregoodNo); foreach (var item in querySum) { GarmentMonitoringExpenditureGoodDto dto = new GarmentMonitoringExpenditureGoodDto { roNo = item.ros, buyerArticle = item.buyer, expenditureGoodType = item.expendituregoodTypes, expenditureGoodNo = item.expendituregoodNo, expenditureDate = item.expenditureDates, qty = item.qty, colour = item.color, name = item.names, invoice = item.invoices, price = Math.Round(Convert.ToDecimal(Convert.ToDouble(Math.Round(item.price, 2)) * Math.Round(item.fcs, 2)), 2), buyerCode = item.buyerC, nominal = Math.Round(Convert.ToDecimal(item.qty) * Convert.ToDecimal(Convert.ToDouble(Math.Round(item.price, 2)) * Math.Round(item.fcs, 2)), 2) }; monitoringDtos.Add(dto); } var data = from a in monitoringDtos where a.qty > 0 select a; monitoringDtos = data.ToList(); double qty = 0; decimal nominal = 0; foreach (var item in data) { qty += item.qty; nominal += item.nominal; } GarmentMonitoringExpenditureGoodDto dtos = new GarmentMonitoringExpenditureGoodDto { roNo = "", buyerArticle = "", expenditureGoodType = "", expenditureGoodNo = "", expenditureDate = null, qty = qty, colour = "", name = "", invoice = "", price = 0, buyerCode = "", nominal = nominal }; monitoringDtos.Add(dtos); listViewModel.garmentMonitorings = monitoringDtos; var reportDataTable = new DataTable(); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "NO BON", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "TIPE PENGELUARAN", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "TGL", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "RO", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "BUYER & ARTICLE", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "COLOUR", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "NAMA", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "HARGA (PCS)", DataType = typeof(decimal) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "QTY", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "NOMINAL", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "INVOICE", DataType = typeof(string) }); int counter = 5; if (listViewModel.garmentMonitorings.Count > 0) { foreach (var report in listViewModel.garmentMonitorings) { reportDataTable.Rows.Add(report.expenditureGoodNo, report.expenditureGoodType, report.expenditureDate.GetValueOrDefault().ToString("dd MMM yyy"), report.roNo, report.buyerArticle, report.colour, report.name, report.price, report.qty, report.nominal, report.invoice); counter++; } } using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet 1"); worksheet.Cells["A" + 5 + ":K" + 5 + ""].Style.Font.Bold = true; worksheet.Cells["A1"].Value = "Report Barang Jadi "; worksheet.Cells["A" + 1 + ":K" + 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 + ":K" + 1 + ""].Merge = true; worksheet.Cells["A" + 2 + ":K" + 2 + ""].Merge = true; worksheet.Cells["A" + 3 + ":K" + 3 + ""].Merge = true; worksheet.Cells["A" + 1 + ":K" + 3 + ""].Style.Font.Size = 15; worksheet.Cells["A" + 1 + ":K" + 5 + ""].Style.Font.Bold = true; worksheet.Cells["A" + 1 + ":K" + 5 + ""].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells["A" + 1 + ":K" + 5 + ""].Style.VerticalAlignment = ExcelVerticalAlignment.Center; worksheet.Cells["A5"].LoadFromDataTable(reportDataTable, true); worksheet.Column(8).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells["H" + 2 + ":J" + counter + ""].Style.Numberformat.Format = "#,##0.00"; worksheet.Column(9).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells["I" + 2 + ":I" + counter + ""].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells["A" + 5 + ":K" + counter + ""].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells["A" + 5 + ":K" + counter + ""].Style.Border.Top.Style = ExcelBorderStyle.Thin; worksheet.Cells["A" + 5 + ":K" + counter + ""].Style.Border.Left.Style = ExcelBorderStyle.Thin; worksheet.Cells["A" + 5 + ":K" + counter + ""].Style.Border.Right.Style = ExcelBorderStyle.Thin; worksheet.Cells["H" + (counter) + ":I" + (counter) + ""].Style.Font.Bold = true; worksheet.Cells["A" + 5 + ":K" + 5 + ""].Style.Font.Bold = true; var stream = new MemoryStream(); if (request.type != "bookkeeping") { worksheet.Cells["A" + (counter) + ":H" + (counter) + ""].Merge = true; worksheet.Column(8).Hidden = true; } else { worksheet.Cells["A" + (counter) + ":G" + (counter) + ""].Merge = true; } package.SaveAs(stream); return(stream); } }
public async Task <MemoryStream> Handle(GetXlsFinishingQuery 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 QueryRoFinishing = (from a in garmentFinishingOutRepository.Query where a.UnitId == request.unit && a.FinishingOutDate <= dateTo select a.RONo).Distinct(); var QueryRoSewingOut = (from a in garmentSewingOutRepository.Query where a.UnitToId == request.unit && a.SewingOutDate <= dateTo && a.SewingTo == "FINISHING" select a.RONo).Distinct(); var QueryRo = QueryRoSewingOut.Union(QueryRoFinishing).Distinct(); List <string> _ro = new List <string>(); foreach (var item in QueryRo) { _ro.Add(item); } var _unitName = (from a in garmentFinishingOutRepository.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() }); GarmentMonitoringFinishingListViewModel listViewModel = new GarmentMonitoringFinishingListViewModel(); List <GarmentMonitoringFinishingDto> monitoringDtos = new List <GarmentMonitoringFinishingDto>(); var QueryFinishing = from a in (from aa in garmentFinishingOutRepository.Query where aa.UnitId == request.unit && aa.FinishingOutDate <= dateTo select aa) join b in garmentFinishingOutItemRepository.Query on a.Identity equals b.FinishingOutId 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(), finishingQtyPcs = a.FinishingOutDate >= dateFrom ? b.Quantity : 0, sewingQtyPcs = 0, uomUnit = "PCS", remainQty = 0, stock = a.FinishingOutDate < 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 QuerySewingOut = from a in (from aa in garmentSewingOutRepository.Query where aa.UnitToId == request.unit && aa.SewingOutDate <= dateTo && aa.SewingTo == "FINISHING" 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(), finishingQtyPcs = 0, sewingQtyPcs = a.SewingOutDate >= dateFrom ? b.Quantity : 0, uomUnit = "PCS", remainQty = 0, stock = a.SewingOutDate < 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(QueryFinishing); var querySum = queryNow.ToList().GroupBy(x => new { x.buyerCode, x.qtyOrder, x.roJob, x.article, x.uomUnit, x.style }, (key, group) => new { QtyOrder = key.qtyOrder, RoJob = key.roJob, Style = key.style, Stock = group.Sum(s => s.stock), UomUnit = key.uomUnit, Article = key.article, buyer = key.buyerCode, price = group.Sum(s => s.price), SewingQtyPcs = group.Sum(s => s.sewingQtyPcs), Finishing = group.Sum(s => s.finishingQtyPcs) }).OrderBy(s => s.RoJob); foreach (var item in querySum) { GarmentMonitoringFinishingDto dto = new GarmentMonitoringFinishingDto { roJob = item.RoJob, article = item.Article, uomUnit = item.UomUnit, qtyOrder = item.QtyOrder, sewingOutQtyPcs = Math.Round(item.SewingQtyPcs, 2), finishingOutQtyPcs = Math.Round(item.Finishing, 2), stock = Math.Round(item.Stock, 2), style = item.Style, buyerCode = item.buyer, price = Math.Round(item.price, 2), remainQty = Math.Round(item.Stock + item.SewingQtyPcs - item.Finishing, 2), nominal = Math.Round(Convert.ToDecimal(item.Stock + item.SewingQtyPcs - item.Finishing) * item.price) }; monitoringDtos.Add(dto); } var data = from a in monitoringDtos where a.stock > 0 || a.sewingOutQtyPcs > 0 || a.finishingOutQtyPcs > 0 || a.remainQty > 0 select a; double stocks = 0; double finishing = 0; double sewingOutQtyPcs = 0; decimal nominals = 0; foreach (var item in data) { stocks += item.stock; finishing += item.finishingOutQtyPcs; sewingOutQtyPcs += item.sewingOutQtyPcs; nominals += item.nominal; } GarmentMonitoringFinishingDto dtos = new GarmentMonitoringFinishingDto { roJob = "", article = "", buyerCode = "", uomUnit = "", qtyOrder = 0, sewingOutQtyPcs = sewingOutQtyPcs, finishingOutQtyPcs = finishing, stock = stocks, style = "", price = 0, remainQty = stocks + sewingOutQtyPcs - finishing, 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(decimal) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Stok Masuk", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Barang Awal", 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(decimal) }); 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.sewingOutQtyPcs, report.finishingOutQtyPcs, 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 Finishing "; 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.Cells["D" + 2 + ":D" + counter + ""].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells["D" + 2 + ":D" + counter + ""].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells["F" + 6 + ":k" + counter + ""].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells["F" + 6 + ":k" + 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 <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(GetXlsMonitoringProductionFlowQuery request, CancellationToken cancellationToken) { DateTimeOffset date = new DateTimeOffset(request.date, new TimeSpan(7, 0, 0)); var QueryRo = (from a in garmentCuttingOutRepository.Query join b in garmentCuttingOutItemRepository.Query on a.Identity equals b.CutOutId where a.UnitFromId == request.unit && a.CuttingOutDate <= date select a.RONo).Distinct(); List <string> _ro = new List <string>(); foreach (var item in QueryRo) { _ro.Add(item); } CostCalculationGarmentDataProductionReport costCalculation = await GetDataCostCal(_ro, request.token); var QueryCuttingOut = (from a in garmentCuttingOutRepository.Query join b in garmentCuttingOutItemRepository.Query on a.Identity equals b.CutOutId join c in garmentCuttingOutDetailRepository.Query on b.Identity equals c.CutOutItemId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.UnitFromId == request.unit && a.CuttingOutDate <= date select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtyCutting = c.CuttingOutQuantity, Size = c.SizeName }); var QueryLoading = (from a in garmentLoadingRepository.Query join b in garmentLoadingItemRepository.Query on a.Identity equals b.LoadingId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.UnitId == request.unit && a.LoadingDate <= date select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtyLoading = b.Quantity, Size = b.SizeName }); var QuerySewingOutIsDifSize = from a in garmentSewingOutRepository.Query join b in garmentSewingOutItemRepository.Query on a.Identity equals b.SewingOutId join c in garmentSewingOutDetailRepository.Query on b.Identity equals c.SewingOutItemId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.SewingTo == "FINISHING" && a.UnitId == request.unit && a.SewingOutDate <= date select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtySewing = c.Quantity, Size = c.SizeName }; var QuerySewingOut = from a in garmentSewingOutRepository.Query join b in garmentSewingOutItemRepository.Query on a.Identity equals b.SewingOutId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.SewingTo == "FINISHING" && a.UnitId == request.unit && a.SewingOutDate <= date && a.IsDifferentSize == false select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtySewing = b.Quantity, Size = b.SizeName }; var QueryFinishingOutisDifSize = from a in garmentFinishingOutRepository.Query join b in garmentFinishingOutItemRepository.Query on a.Identity equals b.FinishingOutId join c in garmentFinishingOutDetailRepository.Query on b.Identity equals c.FinishingOutItemId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.FinishingTo == "GUDANG JADI" && a.UnitId == request.unit && a.FinishingOutDate <= date select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtyFinishing = c.Quantity, Size = c.SizeName }; var QueryFinishingOut = from a in garmentFinishingOutRepository.Query join b in garmentFinishingOutItemRepository.Query on a.Identity equals b.FinishingOutId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.FinishingTo == "GUDANG JADI" && a.UnitId == request.unit && a.FinishingOutDate <= date && a.IsDifferentSize == false select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtyFinishing = b.Quantity, Size = b.SizeName }; var queryNow = QueryCuttingOut.Union(QueryLoading).Union(QuerySewingOutIsDifSize).Union(QuerySewingOut).Union(QueryFinishingOut).Union(QueryFinishingOutisDifSize).AsEnumerable(); var querySum = queryNow.GroupBy(x => new { x.Size, x.Ro, x.Article, x.BuyerCode, x.Comodity, x.QtyOrder }, (key, group) => new { ro = key.Ro, article = key.Article, buyer = key.BuyerCode, comodity = key.Comodity, qtyOrder = key.QtyOrder, qtycutting = group.Sum(s => s.QtyCutting), qtySewing = group.Sum(s => s.QtySewing), qtyLoading = group.Sum(s => s.QtyLoading), qtyFinishing = group.Sum(s => s.QtyFinishing), size = key.Size, }); var querySumTotal = queryNow.GroupBy(x => new { x.Ro, x.Article, x.BuyerCode, x.Comodity, x.QtyOrder }, (key, group) => new { ro = key.Ro, article = key.Article, buyer = key.BuyerCode, comodity = key.Comodity, qtyOrder = key.QtyOrder, qtycutting = group.Sum(s => s.QtyCutting), qtySewing = group.Sum(s => s.QtySewing), qtyLoading = group.Sum(s => s.QtyLoading), qtyFinishing = group.Sum(s => s.QtyFinishing), size = "TOTAL" }); var query = querySum.Union(querySumTotal).OrderBy(s => s.ro); GarmentMonitoringProductionFlowListViewModel garmentMonitoringProductionFlow = new GarmentMonitoringProductionFlowListViewModel(); List <GarmentMonitoringProductionFlowDto> monitoringDtos = new List <GarmentMonitoringProductionFlowDto>(); foreach (var item in query) { GarmentMonitoringProductionFlowDto garmentMonitoringDto = new GarmentMonitoringProductionFlowDto() { Article = item.article, Ro = item.ro, BuyerCode = item.buyer, QtyOrder = item.qtyOrder, QtyCutting = item.qtycutting, QtySewing = item.qtySewing, QtyFinishing = item.qtyFinishing, QtyLoading = item.qtyLoading, Size = item.size, Comodity = item.comodity, Wip = item.qtycutting - item.qtyFinishing }; monitoringDtos.Add(garmentMonitoringDto); } garmentMonitoringProductionFlow.garmentMonitorings = monitoringDtos; var reportDataTable = new DataTable(); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "RO", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Kode Buyer", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "No Article", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Komoditi", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Jumlah Order", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Ukuran", DataType = typeof(string) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Hasil Potong", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Hasil Loading", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Hasil Sewing", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Hasil Finishing", DataType = typeof(double) }); reportDataTable.Columns.Add(new DataColumn() { ColumnName = "Barang Dalam Proses", DataType = typeof(double) }); if (garmentMonitoringProductionFlow.garmentMonitorings.Count > 0) { foreach (var report in garmentMonitoringProductionFlow.garmentMonitorings) { reportDataTable.Rows.Add(report.Ro, report.BuyerCode, report.Article, report.Comodity, report.QtyOrder, report.Size, report.QtyCutting, report.QtyLoading, report.QtySewing, report.QtyFinishing, report.Wip); } } using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet 1"); worksheet.Cells["A1"].LoadFromDataTable(reportDataTable, true); var stream = new MemoryStream(); var lisa = garmentMonitoringProductionFlow.garmentMonitorings.GroupBy(x => new { x.Ro, x.Article, x.BuyerCode, x.Comodity, x.QtyOrder }). Select(x => new { Id = x.Key, Quantity = x.Count(), buyerGroup = x.GroupBy(y => y.BuyerCode) .Select(y => new { Id = y.Key, buyerGroup = y.Count() }) }); int A = 1, B = 0; List <int> listbold = new List <int>(); foreach (var item in lisa) { B = A + 1; A += item.Quantity; listbold.Add(item.Quantity); worksheet.Cells["A" + B + ":E" + A + ""].Merge = true; worksheet.Cells["A" + B + ":E" + A + ""].Style.VerticalAlignment = ExcelVerticalAlignment.Top; worksheet.Cells["F" + (A)].Style.Font.Bold = true; worksheet.Cells["G" + (A)].Style.Font.Bold = true; worksheet.Cells["H" + (A)].Style.Font.Bold = true; worksheet.Cells["I" + (A)].Style.Font.Bold = true; worksheet.Cells["J" + (A)].Style.Font.Bold = true; worksheet.Cells["K" + (A)].Style.Font.Bold = true; } package.SaveAs(stream); return(stream); } }
public async Task <GarmentMonitoringExpenditureGoodListViewModel> Handle(GetMonitoringExpenditureGoodQuery 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 QueryRo = (from a in garmentExpenditureGoodRepository.Query where a.UnitId == request.unit && a.ExpenditureDate >= dateFrom && a.ExpenditureDate <= dateTo select a.RONo).Distinct(); List <string> _ro = new List <string>(); foreach (var item in QueryRo) { _ro.Add(item); } CostCalculationGarmentDataProductionReport costCalculation = await GetDataCostCal(_ro, request.token); GarmentMonitoringExpenditureGoodListViewModel listViewModel = new GarmentMonitoringExpenditureGoodListViewModel(); List <GarmentMonitoringExpenditureGoodDto> monitoringDtos = new List <GarmentMonitoringExpenditureGoodDto>(); 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 Query = from a in (from aa in garmentExpenditureGoodRepository.Query where aa.UnitId == request.unit && aa.ExpenditureDate >= dateFrom && aa.ExpenditureDate <= dateTo select aa) join b in garmentExpenditureGoodItemRepository.Query on a.Identity equals b.ExpenditureGoodId where a.UnitId == request.unit && a.ExpenditureDate >= dateFrom && a.ExpenditureDate <= dateTo select new monitoringView { fc = (from aa in sumFCs where aa.RO == a.RONo select aa.FC / aa.Count).FirstOrDefault(), 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(), buyerArticle = a.BuyerCode + " " + a.Article, roNo = a.RONo, expenditureDate = a.ExpenditureDate, expenditureGoodNo = a.ExpenditureGoodNo, expenditureGoodType = a.ExpenditureType, invoice = a.Invoice, colour = b.Description, qty = b.Quantity, name = (from cost in costCalculation.data where cost.ro == a.RONo select cost.comodityName).FirstOrDefault() }; var querySum = Query.ToList().GroupBy(x => new { x.fc, x.buyerCode, x.buyerArticle, x.roNo, x.expenditureDate, x.expenditureGoodNo, x.expenditureGoodType, x.invoice, x.colour, x.name }, (key, group) => new { ros = key.roNo, buyer = key.buyerArticle, expenditureDates = key.expenditureDate, qty = group.Sum(s => s.qty), expendituregoodNo = key.expenditureGoodNo, expendituregoodTypes = key.expenditureGoodType, color = key.colour, price = group.Sum(s => s.price), buyerC = key.buyerCode, names = key.name, invoices = key.invoice, fcs = key.fc }).OrderBy(s => s.expendituregoodNo); foreach (var item in querySum) { GarmentMonitoringExpenditureGoodDto dto = new GarmentMonitoringExpenditureGoodDto { roNo = item.ros, buyerArticle = item.buyer, expenditureGoodType = item.expendituregoodTypes, expenditureGoodNo = item.expendituregoodNo, expenditureDate = item.expenditureDates, qty = item.qty, colour = item.color, name = item.names, invoice = item.invoices, price = Math.Round(Convert.ToDecimal(Convert.ToDouble(Math.Round(item.price, 2)) * Math.Round(item.fcs, 2)), 2), buyerCode = item.buyerC }; monitoringDtos.Add(dto); } listViewModel.garmentMonitorings = monitoringDtos; return(listViewModel); }
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); } }
public async Task <GarmentMonitoringProductionFlowListViewModel> Handle(GetMonitoringProductionFlowQuery request, CancellationToken cancellationToken) { DateTimeOffset date = new DateTimeOffset(request.date, new TimeSpan(7, 0, 0)); var QueryRo = (from a in garmentCuttingOutRepository.Query join b in garmentCuttingOutItemRepository.Query on a.Identity equals b.CutOutId where a.UnitFromId == request.unit && a.CuttingOutDate <= date select a.RONo).Distinct(); List <string> _ro = new List <string>(); foreach (var item in QueryRo) { _ro.Add(item); } CostCalculationGarmentDataProductionReport costCalculation = await GetDataCostCal(_ro, request.token); var QueryCuttingOut = (from a in garmentCuttingOutRepository.Query join b in garmentCuttingOutItemRepository.Query on a.Identity equals b.CutOutId join c in garmentCuttingOutDetailRepository.Query on b.Identity equals c.CutOutItemId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.UnitFromId == request.unit && a.CuttingOutDate <= date select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtyCutting = c.CuttingOutQuantity, Size = c.SizeName }); var QueryLoading = (from a in garmentLoadingRepository.Query join b in garmentLoadingItemRepository.Query on a.Identity equals b.LoadingId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.UnitId == request.unit && a.LoadingDate <= date select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtyLoading = b.Quantity, Size = b.SizeName }); var QuerySewingOutIsDifSize = from a in garmentSewingOutRepository.Query join b in garmentSewingOutItemRepository.Query on a.Identity equals b.SewingOutId join c in garmentSewingOutDetailRepository.Query on b.Identity equals c.SewingOutItemId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.SewingTo == "FINISHING" && a.UnitId == request.unit && a.SewingOutDate <= date select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtySewing = c.Quantity, Size = c.SizeName }; var QuerySewingOut = from a in garmentSewingOutRepository.Query join b in garmentSewingOutItemRepository.Query on a.Identity equals b.SewingOutId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.SewingTo == "FINISHING" && a.UnitId == request.unit && a.SewingOutDate <= date && a.IsDifferentSize == false select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtySewing = b.Quantity, Size = b.SizeName }; var QueryFinishingOutisDifSize = from a in garmentFinishingOutRepository.Query join b in garmentFinishingOutItemRepository.Query on a.Identity equals b.FinishingOutId join c in garmentFinishingOutDetailRepository.Query on b.Identity equals c.FinishingOutItemId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.FinishingTo == "GUDANG JADI" && a.UnitId == request.unit && a.FinishingOutDate <= date select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtyFinishing = c.Quantity, Size = c.SizeName }; var QueryFinishingOut = from a in garmentFinishingOutRepository.Query join b in garmentFinishingOutItemRepository.Query on a.Identity equals b.FinishingOutId where (request.ro == null || (request.ro != null && request.ro != "" && a.RONo == request.ro)) && a.FinishingTo == "GUDANG JADI" && a.UnitId == request.unit && a.FinishingOutDate <= date && a.IsDifferentSize == false select new monitoringView { Ro = a.RONo, Article = a.Article, Comodity = a.ComodityName, BuyerCode = (from cost in costCalculation.data where cost.ro == a.RONo select cost.buyerCode).FirstOrDefault(), QtyOrder = (from cost in costCalculation.data where cost.ro == a.RONo select cost.qtyOrder).FirstOrDefault(), QtyFinishing = b.Quantity, Size = b.SizeName }; var queryNow = QueryCuttingOut.Union(QueryLoading).Union(QuerySewingOutIsDifSize).Union(QuerySewingOut).Union(QueryFinishingOut).Union(QueryFinishingOutisDifSize).AsEnumerable(); var querySum = queryNow.GroupBy(x => new { x.Size, x.Ro, x.Article, x.BuyerCode, x.Comodity, x.QtyOrder }, (key, group) => new { ro = key.Ro, article = key.Article, buyer = key.BuyerCode, comodity = key.Comodity, qtyOrder = key.QtyOrder, qtycutting = group.Sum(s => s.QtyCutting), qtySewing = group.Sum(s => s.QtySewing), qtyLoading = group.Sum(s => s.QtyLoading), qtyFinishing = group.Sum(s => s.QtyFinishing), size = key.Size, }); var querySumTotal = queryNow.GroupBy(x => new { x.Ro, x.Article, x.BuyerCode, x.Comodity, x.QtyOrder }, (key, group) => new { ro = key.Ro, article = key.Article, buyer = key.BuyerCode, comodity = key.Comodity, qtyOrder = key.QtyOrder, qtycutting = group.Sum(s => s.QtyCutting), qtySewing = group.Sum(s => s.QtySewing), qtyLoading = group.Sum(s => s.QtyLoading), qtyFinishing = group.Sum(s => s.QtyFinishing), size = "TOTAL" }); var query = querySum.Union(querySumTotal).OrderBy(s => s.ro); GarmentMonitoringProductionFlowListViewModel garmentMonitoringProductionFlow = new GarmentMonitoringProductionFlowListViewModel(); List <GarmentMonitoringProductionFlowDto> monitoringDtos = new List <GarmentMonitoringProductionFlowDto>(); foreach (var item in query) { GarmentMonitoringProductionFlowDto garmentMonitoringDto = new GarmentMonitoringProductionFlowDto() { Article = item.article, Ro = item.ro, BuyerCode = item.buyer, QtyOrder = item.qtyOrder, QtyCutting = item.qtycutting, QtySewing = item.qtySewing, QtyFinishing = item.qtyFinishing, QtyLoading = item.qtyLoading, Size = item.size, Comodity = item.comodity, Wip = item.qtycutting - item.qtyFinishing }; monitoringDtos.Add(garmentMonitoringDto); } garmentMonitoringProductionFlow.garmentMonitorings = monitoringDtos; return(garmentMonitoringProductionFlow); }