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