public GarmentMonitoringExpenditureGoodDto(GarmentMonitoringExpenditureGoodDto garmentMonitoring) { expenditureGoodNo = garmentMonitoring.expenditureGoodNo; expenditureGoodType = garmentMonitoring.expenditureGoodType; expenditureDate = garmentMonitoring.expenditureDate; roNo = garmentMonitoring.roNo; buyerArticle = garmentMonitoring.buyerArticle; colour = garmentMonitoring.colour; name = garmentMonitoring.name; qty = garmentMonitoring.qty; invoice = garmentMonitoring.invoice; nominal = garmentMonitoring.nominal; }
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 <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); }