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