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); } }