public IQueryable <TraceableINViewModel> getQueryTracable(string filter, string tipe, DateTime?Datefrom, DateTime?DateTo)
{
DateTime dateFrom = Datefrom == null ? new DateTime(1970, 1, 1) : (DateTime)Datefrom;
DateTime dateTo = DateTo == null ? DateTime.Now : (DateTime)DateTo;
string datefrom = dateFrom.ToString("yyyy-MM-dd");
string dateto = dateTo.ToString("yyyy-MM-dd");
List <TraceableINViewModel> reportData = new List <TraceableINViewModel>();
try
{
if (tipe == "BCDate")
{
string cmdtracable = "select * from(select distinct BCNo,BonNo,BCType, BCDate,fcf.ItemCode, fcf.ItemName, isnull((select top 1 Quantity * ConvertValue from DL_Inventories.dbo.DETAIL_RECEIPT_GOODS where DetailShippingOrderId = fcf.DetailShippingOrderId),0) As QtyReceipt,(select top 1 b.UnitQtyName from DL_Inventories.dbo.DETAIL_RECEIPT_GOODS a join DL_Inventories.dbo.UNIT_QUANTITY b on a.SmallestUnitQtyCode = b.UnitQtyCode where a.DetailShippingOrderId = fcf.DetailShippingOrderId) as SatMasuk,(select top 1 b.UnitQtyName from DL_Inventories.dbo.DETAIL_EXPENDITURE_GOODS a join DL_Inventories.dbo.UNIT_QUANTITY b on a.SmallestUnitQuantity = b.UnitQtyCode Where a.DetailExpenditureGoodsId = fcf.DetailExpendId) as SatKeluar, ROJob,DetailExpendId, (select top(1) Invoice from ExpenditureGood e where e.RO= ROJob) as Invoice, (select top(1) PEB from ExpenditureGood e join Shipping.dbo.OmzetKonf o on o.Invoice_No=e.Invoice where e.RO= fcf.ROJob) PEB, (select top 1 PlanPO from DL_Supports.dbo.PURCHASE_ORDER f where f.POId = fcf.POId) NoPO, (select top 1 ExpenditureNo from DL_Inventories.dbo.DETAIL_EXPENDITURE_GOODS a where a.DetailExpenditureGoodsId = fcf.DetailExpendId) BUK, isnull((select top 1 SmallestQuantity from DL_Inventories.dbo.DETAIL_EXPENDITURE_GOODS a where a.DetailExpenditureGoodsId = fcf.DetailExpendId),0) QtyBUK, (select top(1) TglPEB from ExpenditureGood e join Shipping.dbo.OmzetKonf o on o.Invoice_No=e.Invoice where e.RO= fcf.ROJob) TAnggalPEB, isnull((select Isnull(SUM(a.Qty),0) from ExpenditureGoodDetail a join ExpenditureGood b on a.ExpenditureGoodId = b.ExpenditureGoodId where b.RO = fcf.ROJob and ExpenditureType = 'E001'),0) EksporQty, isnull((select Isnull(SUM(a.Qty),0) from ExpenditureGoodDetail a join ExpenditureGood b on a.ExpenditureGoodId = b.ExpenditureGoodId where b.RO = fcf.ROJob and ExpenditureType = 'E003'),0) SampleQty, isnull((SELECT Sum(Qty) as a from FinishingOutDetail a join FinishingOut b on a.FinishingOutId = b.FinishingOutId where b.RO = fcf.ROJob and b.FinishingOutTo='GUDANG JADI'),0) FinisihingOutQty, isnull((select isnull(sum(a.Qty),0) from FinishingOutDetail a join FinishingOut b on a.FinishingOutId = b.FinishingOutId join FinishingInDetail c on a.ReferenceFinishingInDetailId = c.FinishingInDetailId join FinishingIn d on c.FinishingNo = d.FinishingId where b.RO = fcf.ROJob and b.FinishingOutTo = 'GUDANG JADI' and d.FinishingFrom = 'PEMBELIAN'),0) subconOut, isnull((SELECT SUM(A.QtyCutting) FROM CuttingDODetail a join CuttingDO b on a.DeliveryOrderNo = B.DeliveryOrderNo where RO = fcf.ROJob),0) ProduksiQty from FactControlFlow fcf group by BCNo,BCType, BCDate,BonNo,ROJob ,DetailReceiptId,DetailShippingOrderId,fcf.ItemCode,fcf.ItemName, DetailExpendId,POId) as data where bcdate > @datefrom And bcdate <= @dateto order by BCType,BCNo,BonNo";
List <SqlParameter> parameters = new List <SqlParameter>();
//parameters.Add(new SqlParameter("filter", tipe));
parameters.Add(new SqlParameter("dateto", dateto));
parameters.Add(new SqlParameter("datefrom", datefrom));
var data = dBContext.ExecuteReader(cmdtracable, parameters);
while (data.Read())
{
reportData.Add(new TraceableINViewModel
{
BCType = data["BCType"].ToString(),
BCNo = data["BCNo"].ToString(),
BCDate = data["BCDate"].ToString(),
BonNo = data["BonNo"].ToString(),
PO = data["NoPO"].ToString(),
ItemCode = data["ItemCode"].ToString(),
ItemName = data["ItemName"].ToString(),
ReceiptQty = (double)data["QtyReceipt"],
SatuanReceipt = data["SatMasuk"].ToString(),
BUK = data["BUK"].ToString(),
QtyBUK = (double)data["QtyBUK"],
SatuanBUK = data["SatKeluar"].ToString(),
ROJob = data["ROJob"].ToString(),
ProduksiQty = (double)data["ProduksiQty"],
BJQty = (double)data["subconOut"] + (double)data["FinisihingOutQty"],
Invoice = data["Invoice"].ToString(),
PEB = data["PEB"].ToString(),
PEBDate = data["TAnggalPEB"].ToString() == null ? "-" : data["TAnggalPEB"].ToString(),
EksporQty = (double)data["EksporQty"],
SampleQty = (double)data["SampleQty"],
SubkonOutQty = (double)data["subconOut"]
});
}
}
else
{
string cmdtracable = "select * from(select distinct BCNo,BonNo,BCType, BCDate,fcf.ItemCode, fcf.ItemName, isnull((select Quantity * ConvertValue from DL_Inventories.dbo.DETAIL_RECEIPT_GOODS where DetailShippingOrderId = fcf.DetailShippingOrderId),0) As QtyReceipt,(select b.UnitQtyName from DL_Inventories.dbo.DETAIL_RECEIPT_GOODS a join DL_Inventories.dbo.UNIT_QUANTITY b on a.SmallestUnitQtyCode = b.UnitQtyCode where a.DetailShippingOrderId = fcf.DetailShippingOrderId) as SatMasuk,(select b.UnitQtyName from DL_Inventories.dbo.DETAIL_EXPENDITURE_GOODS a join DL_Inventories.dbo.UNIT_QUANTITY b on a.SmallestUnitQuantity = b.UnitQtyCode Where a.DetailExpenditureGoodsId = fcf.DetailExpendId) as SatKeluar, ROJob,DetailExpendId, (select top(1) Invoice from ExpenditureGood e where e.RO= ROJob) as Invoice, (select top(1) PEB from ExpenditureGood e join Shipping.dbo.OmzetKonf o on o.Invoice_No=e.Invoice where e.RO= fcf.ROJob) PEB, (select top 1 PlanPO from DL_Supports.dbo.PURCHASE_ORDER f where f.POId = fcf.POId) NoPO, (select top 1 ExpenditureNo from DL_Inventories.dbo.DETAIL_EXPENDITURE_GOODS a where a.DetailExpenditureGoodsId = fcf.DetailExpendId) BUK, isnull((select top 1 SmallestQuantity from DL_Inventories.dbo.DETAIL_EXPENDITURE_GOODS a where a.DetailExpenditureGoodsId = fcf.DetailExpendId),0) QtyBUK, (select top(1) TglPEB from ExpenditureGood e join Shipping.dbo.OmzetKonf o on o.Invoice_No=e.Invoice where e.RO= fcf.ROJob) TAnggalPEB, isnull((select Isnull(SUM(a.Qty),0) from ExpenditureGoodDetail a join ExpenditureGood b on a.ExpenditureGoodId = b.ExpenditureGoodId where b.RO = fcf.ROJob and ExpenditureType = 'E001'),0) EksporQty, isnull((select Isnull(SUM(a.Qty),0) from ExpenditureGoodDetail a join ExpenditureGood b on a.ExpenditureGoodId = b.ExpenditureGoodId where b.RO = fcf.ROJob and ExpenditureType = 'E003'),0) SampleQty, isnull((SELECT Sum(Qty) as a from FinishingOutDetail a join FinishingOut b on a.FinishingOutId = b.FinishingOutId where b.RO = fcf.ROJob and b.FinishingOutTo='GUDANG JADI'),0) FinisihingOutQty, isnull((select isnull(sum(a.Qty),0) from FinishingOutDetail a join FinishingOut b on a.FinishingOutId = b.FinishingOutId join FinishingInDetail c on a.ReferenceFinishingInDetailId = c.FinishingInDetailId join FinishingIn d on c.FinishingNo = d.FinishingId where b.RO = fcf.ROJob and b.FinishingOutTo = 'GUDANG JADI' and d.FinishingFrom = 'PEMBELIAN'),0) subconOut, isnull((SELECT SUM(A.QtyCutting) FROM CuttingDODetail a join CuttingDO b on a.DeliveryOrderNo = B.DeliveryOrderNo where RO = fcf.ROJob),0) ProduksiQty from FactControlFlow fcf group by BCNo,BCType, BCDate,BonNo,ROJob ,DetailReceiptId,DetailShippingOrderId,fcf.ItemCode,fcf.ItemName, DetailExpendId,POId) as data where " + tipe + " = @key AND bcdate > '" + datefrom + "' And bcdate <= '" + dateTo + "' order by BCType,BCNo,BonNo";
List <SqlParameter> parameters = new List <SqlParameter>();
//parameters.Add(new SqlParameter("filter", tipe));
parameters.Add(new SqlParameter("key", filter));
var data = dBContext.ExecuteReader(cmdtracable, parameters);
while (data.Read())
{
reportData.Add(new TraceableINViewModel
{
BCType = data["BCType"].ToString(),
BCNo = data["BCNo"].ToString(),
BCDate = data["BCDate"].ToString(),
BonNo = data["BonNo"].ToString(),
PO = data["NoPO"].ToString(),
ItemCode = data["ItemCode"].ToString(),
ItemName = data["ItemName"].ToString(),
ReceiptQty = (double)data["QtyReceipt"],
SatuanReceipt = data["SatMasuk"].ToString(),
BUK = data["BUK"].ToString(),
QtyBUK = (double)data["QtyBUK"],
SatuanBUK = data["SatKeluar"].ToString(),
ROJob = data["ROJob"].ToString(),
ProduksiQty = (double)data["ProduksiQty"],
BJQty = (double)data["subconOut"] + (double)data["FinisihingOutQty"],
Invoice = data["Invoice"].ToString(),
PEB = data["PEB"].ToString(),
PEBDate = data["TAnggalPEB"].ToString() == null ? "-" : data["TAnggalPEB"].ToString(),
EksporQty = (double)data["EksporQty"],
SampleQty = (double)data["SampleQty"],
SubkonOutQty = (double)data["subconOut"]
});
}
}
var b = reportData.ToArray();
var index = 0;
foreach (TraceableINViewModel a in reportData)
{
TraceableINViewModel dup = Array.Find(b, o => o.BCType == a.BCType && o.BCNo == a.BCNo && o.BonNo == o.BonNo);
if (dup != null)
{
if (dup.count == 0)
{
index++;
dup.count = index;
}
}
a.count = dup.count;
}
}
catch (Exception e)
{
}
return(reportData.AsQueryable());
}
public MemoryStream GetTraceableInExcel(string filter, string tipe, DateTime?Datefrom, DateTime?DateTo)
{
var Query = getQueryTracable(filter, tipe, Datefrom, DateTo);
Query.OrderBy(x => x.BCNo);
DataTable result = new DataTable();
result.Columns.Add(new DataColumn()
{
ColumnName = "No", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jenis BC", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nomor BC", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tanggal BC", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No Bon", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "PO", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Kode Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Nama Barang", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah Terima", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Satuan Terima", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "No BUK", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Jumlah Keluar", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Satuan Keluar", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Produksi Qty", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "BJ Qty", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Invoice", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "BC Keluar", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Tgl BC Keluar", DataType = typeof(String)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Ekspor Qty", DataType = typeof(Double)
});
result.Columns.Add(new DataColumn()
{
ColumnName = "Sample Qty", DataType = typeof(Double)
});
if (Query.ToArray().Count() == 0)
{
result.Rows.Add("", "", "", "", "", "", "", "", 0, "", "", 0, "", 0, 0, "", "", "", 0, 0); // to allow column name to be generated properly for empty data as template
}
else
{
var docNo = Query.ToArray();
var q = Query.ToList();
var index = 0;
foreach (TraceableINViewModel a in q)
{
TraceableINViewModel dup = Array.Find(docNo, o => o.BCType == a.BCType && o.BCNo == a.BCNo && o.BonNo == o.BonNo);
if (dup != null)
{
if (dup.count == 0)
{
index++;
dup.count = index;
}
}
a.count = dup.count;
}
Query = q.AsQueryable();
//var index = 0;
foreach (var item in Query)
{
string bcdate = String.IsNullOrWhiteSpace(item.BCDate) ? "-" : Convert.ToDateTime(item.BCDate).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
string pebdate = String.IsNullOrWhiteSpace(item.PEBDate) ? "-" : Convert.ToDateTime(item.PEBDate).ToString("dd MMM yyyy", new CultureInfo("id-ID"));
result.Rows.Add(item.count, item.BCType, item.BCNo, bcdate, item.BonNo, item.PO, item.ItemCode, item.ItemName, item.ReceiptQty, item.SatuanReceipt, item.BUK, item.QtyBUK, item.SatuanBUK, item.ProduksiQty, item.BJQty, item.Invoice, item.PEB, pebdate, item.EksporQty, item.SampleQty);
}
}
//return Excel.CreateExcel(new List<KeyValuePair<DataTable, string>>() { new KeyValuePair<DataTable, string>(result, "Territory") }, true);
ExcelPackage package = new ExcelPackage();
bool styling = true;
foreach (KeyValuePair <DataTable, String> item in new List <KeyValuePair <DataTable, string> >()
{
new KeyValuePair <DataTable, string>(result, "Territory")
})
{
var sheet = package.Workbook.Worksheets.Add(item.Value);
sheet.Cells["A1"].LoadFromDataTable(item.Key, true, (styling == true) ? OfficeOpenXml.Table.TableStyles.Light16 : OfficeOpenXml.Table.TableStyles.None);
//sheet.Cells["C1:D1"].Merge = true;
//sheet.Cells["C1:D1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//sheet.Cells["E1:F1"].Merge = true;
//sheet.Cells["C1:D1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
Dictionary <string, int> counts = new Dictionary <string, int>();
Dictionary <string, int> countsType = new Dictionary <string, int>();
var docNo = Query.ToArray();
int value;
foreach (var a in Query)
{
//FactBeacukaiViewModel dup = Array.Find(docNo, o => o.BCType == a.BCType && o.BCNo == a.BCNo);
if (counts.TryGetValue(a.BCType + a.BCNo + a.BonNo, out value))
{
counts[a.BCType + a.BCNo + a.BonNo]++;
}
else
{
counts[a.BCType + a.BCNo + a.BonNo] = 1;
}
//FactBeacukaiViewModel dup1 = Array.Find(docNo, o => o.BCType == a.BCType);
if (countsType.TryGetValue(a.BCType, out value))
{
countsType[a.BCType]++;
}
else
{
countsType[a.BCType] = 1;
}
}
int index = 2;
foreach (KeyValuePair <string, int> b in counts)
{
sheet.Cells["A" + index + ":A" + (index + b.Value - 1)].Merge = true;
sheet.Cells["A" + index + ":A" + (index + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
sheet.Cells["C" + index + ":C" + (index + b.Value - 1)].Merge = true;
sheet.Cells["C" + index + ":C" + (index + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
sheet.Cells["D" + index + ":D" + (index + b.Value - 1)].Merge = true;
sheet.Cells["D" + index + ":D" + (index + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
sheet.Cells["E" + index + ":E" + (index + b.Value - 1)].Merge = true;
sheet.Cells["E" + index + ":E" + (index + b.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
index += b.Value;
}
index = 2;
foreach (KeyValuePair <string, int> c in countsType)
{
sheet.Cells["B" + index + ":B" + (index + c.Value - 1)].Merge = true;
sheet.Cells["B" + index + ":B" + (index + c.Value - 1)].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
index += c.Value;
}
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
}
MemoryStream stream = new MemoryStream();
package.SaveAs(stream);
return(stream);
//return Excel.CreateExcel(new List<KeyValuePair<DataTable, string>>() { new KeyValuePair<DataTable, string>(result, "Territory") }, true);
}
