private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
var representative = uowRe.Repository.GetAll();
var salesAreaDet = uowSa.Repository.GetAll()
.Join(representative, sa => sa.RepresentativeId, re => re.Id, (sa, re) => new { sa, re })
.Select(res => new
{
res.sa.Id,
res.sa.AreaCode,
Rayon = res.sa.Description,
Representative = res.re.Description
});
var outlet = uowOt.Repository.GetAll();
var cusDet = uowCus.Repository.GetAll()
.Join(outlet, c => c.OutletTypeId, o => o.Id, (c, o) => new { c, o })
.Select(res => new
{
res.c.Id,
res.c.CustomerCode,
res.c.CustomerName,
res.c.OutletTypeId,
res.o.OutletTypeCode,
OutletType = res.o.Description
});
var principal = uowPri.Repository.GetAll();
var productDet = uowPrd.Repository.GetAll()
.Join(principal, prd => prd.PrincipalId, pri => pri.Id, (prd, pri) => new { prd, pri })
.Select(res => new
{
ProductId = res.prd.Id,
res.prd.ProductCode,
res.prd.ProductName,
res.prd.PrincipalId,
res.pri.PrincipalName
});
var salesOrder = uowSo.Repository.GetAll().
Where(s => s.SalesDate >= dtpFrom.Value && s.SalesDate <= dtpTo.Value && s.Status == Constant.RecordStatus.Active);
var recapDetail = uowSoi.Repository.GetAll()
.Join(salesOrder, soi => soi.SalesOrderId, so => so.Id, (soi, so) => new { soi, so })
.Join(productDet, soiSo => soiSo.soi.ProductId, prdDet => prdDet.ProductId, (soiSo, prdDet) => new { soiSo, prdDet })
.Join(cusDet, soiSoPrd => soiSoPrd.soiSo.so.CustomerId, cusd => cusd.Id, (soiSoPrd, cusd) => new { soiSoPrd, cusd })
.Join(salesAreaDet, soiSoPrdCus => soiSoPrdCus.soiSoPrd.soiSo.so.SalesAreaId, sa => sa.Id, (soiSoPrdCus, sa) => new { soiSoPrdCus, sa })
.Select(res => new
{
res.soiSoPrdCus.soiSoPrd.soiSo.so.SalesDate,
res.soiSoPrdCus.soiSoPrd.soiSo.so.SalesNo,
res.soiSoPrdCus.soiSoPrd.soiSo.so.CustomerId,
res.soiSoPrdCus.cusd.CustomerCode,
res.soiSoPrdCus.soiSoPrd.soiSo.so.CustomerName,
res.soiSoPrdCus.soiSoPrd.soiSo.so.CustomerNpwp,
res.soiSoPrdCus.soiSoPrd.soiSo.so.CustomerAddress,
res.soiSoPrdCus.soiSoPrd.soiSo.so.CustomerDistrict,
res.soiSoPrdCus.soiSoPrd.soiSo.so.SalesmanCode,
res.soiSoPrdCus.soiSoPrd.soiSo.so.SalesAreaId,
res.soiSoPrdCus.soiSoPrd.soiSo.so.SalesAreaCode,
res.sa.Rayon,
res.sa.Representative,
res.soiSoPrdCus.cusd.OutletType,
res.soiSoPrdCus.soiSoPrd.soiSo.so.CustomerProvince,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.ProductId,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.ProductCode,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.ProductName,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.BatchCode,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.ExpiredDate,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.UomCode,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.Quantity,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.Price,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.TotalAmount,
res.soiSoPrdCus.soiSoPrd.soiSo.soi.DiscountPercentage,
res.soiSoPrdCus.soiSoPrd.soiSo.so.TaxBaseAmount,
res.soiSoPrdCus.soiSoPrd.soiSo.so.ValueAddedTaxAmount,
res.soiSoPrdCus.soiSoPrd.soiSo.so.ExtraDiscountAmount,
res.soiSoPrdCus.soiSoPrd.soiSo.so.GrandTotalAmount,
res.soiSoPrdCus.soiSoPrd.soiSo.so.DueDate,
res.soiSoPrdCus.soiSoPrd.soiSo.so.DeliveryAddress,
res.soiSoPrdCus.soiSoPrd.soiSo.so.DeliveryDistrict,
res.soiSoPrdCus.soiSoPrd.soiSo.so.CustomerPhone,
res.soiSoPrdCus.soiSoPrd.prdDet.PrincipalName
})
.OrderBy(res => res.SalesNo)
.ToList();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = "UBICAPS";
xlWorkSheet.Cells[1, 1].EntireRow.Font.Bold = true;
// Header
xlWorkSheet.Cells[1, 1] = "TANGGAL";
xlWorkSheet.Cells[1, 2] = "NO. FAKTUR";
xlWorkSheet.Cells[1, 3] = "KODE PELANGGAN";
xlWorkSheet.Cells[1, 4] = "NAMA PELANGGAN";
xlWorkSheet.Cells[1, 5] = "NPWP";
xlWorkSheet.Cells[1, 6] = "ALAMAT LENGKAP";
xlWorkSheet.Cells[1, 7] = "KOTA";
xlWorkSheet.Cells[1, 8] = "NAMA SALES";
xlWorkSheet.Cells[1, 9] = "RAYON";
xlWorkSheet.Cells[1, 10] = "PERWAKILAN";
xlWorkSheet.Cells[1, 11] = "JENIS OUTLET";
xlWorkSheet.Cells[1, 12] = "KODE BARANG";
xlWorkSheet.Cells[1, 13] = "NAMA BARANG";
xlWorkSheet.Cells[1, 14] = "NOMER BATCH";
xlWorkSheet.Cells[1, 15] = "KADALUWARSA";
xlWorkSheet.Cells[1, 16] = "SAT";
xlWorkSheet.Cells[1, 17] = "QTY";
xlWorkSheet.Cells[1, 18] = "HARGA HNA";
xlWorkSheet.Cells[1, 19] = "GROSS VALUE";
xlWorkSheet.Cells[1, 20] = "%DISKON";
xlWorkSheet.Cells[1, 21] = "EXT. DISKON";
xlWorkSheet.Cells[1, 22] = "TOTAL DISKON";
xlWorkSheet.Cells[1, 23] = "DPP";
xlWorkSheet.Cells[1, 24] = "PPN 10%";
xlWorkSheet.Cells[1, 25] = "NETT VALUE";
xlWorkSheet.Cells[1, 26] = "TGL.JTH TEMPO";
xlWorkSheet.Cells[1, 27] = "ALAMAT KIRIM";
xlWorkSheet.Cells[1, 28] = "KOTA";
xlWorkSheet.Cells[1, 29] = "NO. TELPON";
xlWorkSheet.Cells[1, 30] = "PRINCIPAL";
var salesOrderHelper = new SalesOrderHelper();
var rowNumber = 2; // Row number 1 is Header
var totalRecord = recapDetail.Count();
foreach (var item in recapDetail)
{
var qty = item.Quantity;
var nettPrice = item.Price;
var grossValue = salesOrderHelper.CalculateGrossValue(qty, nettPrice);
var discountPercent = Math.Round(Convert.ToDecimal(item.DiscountPercentage / 100), 4, MidpointRounding.AwayFromZero);
var extraDiscount = item.ExtraDiscountAmount;
var totalDiscount = Math.Round(Convert.ToDecimal(grossValue * discountPercent), 4, MidpointRounding.AwayFromZero) + extraDiscount;
// Set the Extra Discount proportionally
var sumOfGrossValue = recapDetail.Where(so => so.SalesNo == item.SalesNo)
.Sum(rc => (rc.Quantity * rc.Price));
var propotionalExtraDiscount = (grossValue / sumOfGrossValue) * extraDiscount;
var taxBaseAmount = salesOrderHelper.CalculateTaxBaseAmount(qty, nettPrice, discountPercent, extraDiscount);
var valueAddedAmount = salesOrderHelper.CalculateValueAddedAmount(taxBaseAmount);
var netValue = salesOrderHelper.CalculateNettValueAmount(taxBaseAmount, valueAddedAmount);
xlWorkSheet.Cells[rowNumber, 1].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlWorkSheet.Cells[rowNumber, 1] = item.SalesDate.ToString("dd/MM/yyyy");
xlWorkSheet.Cells[rowNumber, 2] = item.SalesNo;
xlWorkSheet.Cells[rowNumber, 3].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 3] = item.CustomerCode.ToString().Trim();
xlWorkSheet.Cells[rowNumber, 4] = item.CustomerName;
xlWorkSheet.Cells[rowNumber, 5].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 5] = item.CustomerNpwp;
xlWorkSheet.Cells[rowNumber, 6] = item.CustomerAddress;
xlWorkSheet.Cells[rowNumber, 7] = item.CustomerDistrict;
xlWorkSheet.Cells[rowNumber, 8] = item.SalesmanCode;
xlWorkSheet.Cells[rowNumber, 9] = item.Rayon;
xlWorkSheet.Cells[rowNumber, 10] = item.Representative;
xlWorkSheet.Cells[rowNumber, 11] = item.OutletType;
xlWorkSheet.Cells[rowNumber, 12] = item.ProductCode;
xlWorkSheet.Cells[rowNumber, 13] = item.ProductName;
xlWorkSheet.Cells[rowNumber, 14].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 14] = item.BatchCode;
xlWorkSheet.Cells[rowNumber, 15].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 15].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlWorkSheet.Cells[rowNumber, 15] = item.ExpiredDate.ToString("MM/yyyy");
xlWorkSheet.Cells[rowNumber, 16] = item.UomCode.ToString();
xlWorkSheet.Cells[rowNumber, 17].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 17] = qty;
xlWorkSheet.Cells[rowNumber, 18].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 18] = nettPrice;
xlWorkSheet.Cells[rowNumber, 19].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 19] = grossValue;
xlWorkSheet.Cells[rowNumber, 20].NumberFormat = "0,0%";
xlWorkSheet.Cells[rowNumber, 20] = discountPercent;
xlWorkSheet.Cells[rowNumber, 21].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 21] = propotionalExtraDiscount;
xlWorkSheet.Cells[rowNumber, 22].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 22] = totalDiscount;
xlWorkSheet.Cells[rowNumber, 23].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 23] = taxBaseAmount;
xlWorkSheet.Cells[rowNumber, 24].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 24] = valueAddedAmount;
xlWorkSheet.Cells[rowNumber, 25].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 25] = netValue;
xlWorkSheet.Cells[rowNumber, 26].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 26].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlWorkSheet.Cells[rowNumber, 26] = item.DueDate.ToString("dd/MM/yyyy");
xlWorkSheet.Cells[rowNumber, 27] = item.DeliveryAddress;
xlWorkSheet.Cells[rowNumber, 28] = item.DeliveryDistrict;
xlWorkSheet.Cells[rowNumber, 29].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 29] = item.CustomerPhone;
xlWorkSheet.Cells[rowNumber, 30] = item.PrincipalName;
// progress bar
var progress = ((rowNumber - 1) * 100) / totalRecord;
backgroundWorker.ReportProgress(progress);
rowNumber++;
}
xlWorkSheet.Columns.AutoFit();
var fileDir = Directory.CreateDirectory(System.IO.Path.Combine(Environment.CurrentDirectory, "REKAPITULASI", "UBICAPS"));
var fileName = "REKAPITULASI FAKTUR_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");
fileLoc = Path.Combine(fileDir.FullName, fileName);
xlWorkBook.SaveAs(fileLoc, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
catch (Exception ex)
{
exceptionMessage = ex.Message;
}
}
private void DoRecap()
{
try
{
var recapDetail = ReadDataInvoiceRecap(dtpFrom.Value, dtpTo.Value);
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = "UBICAPS";
xlWorkSheet.Cells[1, 1].EntireRow.Font.Bold = true;
// Header
xlWorkSheet.Cells[1, 1] = "TANGGAL";
xlWorkSheet.Cells[1, 2] = "NO. FAKTUR";
xlWorkSheet.Cells[1, 3] = "KODE PELANGGAN";
xlWorkSheet.Cells[1, 4] = "NAMA PELANGGAN";
xlWorkSheet.Cells[1, 5] = "NPWP";
xlWorkSheet.Cells[1, 6] = "ALAMAT LENGKAP";
xlWorkSheet.Cells[1, 7] = "KOTA";
xlWorkSheet.Cells[1, 8] = "NAMA SALES";
xlWorkSheet.Cells[1, 9] = "RAYON";
xlWorkSheet.Cells[1, 10] = "PERWAKILAN";
xlWorkSheet.Cells[1, 11] = "JENIS OUTLET";
xlWorkSheet.Cells[1, 12] = "KODE BARANG";
xlWorkSheet.Cells[1, 13] = "NAMA BARANG";
xlWorkSheet.Cells[1, 14] = "NOMER BATCH";
xlWorkSheet.Cells[1, 15] = "KADALUWARSA";
xlWorkSheet.Cells[1, 16] = "SAT";
xlWorkSheet.Cells[1, 17] = "QTY";
xlWorkSheet.Cells[1, 18] = "HARGA HNA";
xlWorkSheet.Cells[1, 19] = "GROSS VALUE";
xlWorkSheet.Cells[1, 20] = "%DISKON";
xlWorkSheet.Cells[1, 21] = "EXT. DISKON";
xlWorkSheet.Cells[1, 22] = "TOTAL DISKON";
xlWorkSheet.Cells[1, 23] = "DPP";
xlWorkSheet.Cells[1, 24] = "PPN 10%";
xlWorkSheet.Cells[1, 25] = "NETT VALUE";
xlWorkSheet.Cells[1, 26] = "TGL.JTH TEMPO";
xlWorkSheet.Cells[1, 27] = "ALAMAT KIRIM";
xlWorkSheet.Cells[1, 28] = "KOTA";
xlWorkSheet.Cells[1, 29] = "NO. TELPON";
xlWorkSheet.Cells[1, 30] = "PRINCIPAL";
var salesOrderHelper = new SalesOrderHelper();
var rowNumber = 2; // Row number 1 is Header
var totalRecord = recapDetail.Rows.Count;
for (int i = 0; i < totalRecord; i++)
{
var item = recapDetail.Rows[i];
var qty = Convert.ToInt32(item["Quantity"]);
var nettPrice = Convert.ToDecimal(item["Price"]);
var grossValue = salesOrderHelper.CalculateGrossValue(qty, nettPrice);
var discountPercent = Math.Round(Convert.ToDecimal(Convert.ToDecimal(item["DiscountPercentage"]) / 100), 4, MidpointRounding.AwayFromZero);
var extraDiscount = Convert.ToDecimal(item["ExtraDiscountAmount"]);
var totalDiscount = Math.Round(Convert.ToDecimal(grossValue * discountPercent), 4, MidpointRounding.AwayFromZero);;
// Set the Extra Discount proportionally
var sumOfGrossValue = recapDetail.AsEnumerable().Where(so => so.Field <String>("SalesNo") == item["SalesNo"].ToString())
.Sum(rc => (rc.Field <int>("Quantity") * rc.Field <Decimal>("Price")));
var propotionalExtraDiscount = (grossValue / sumOfGrossValue) * extraDiscount;
var taxBaseAmount = salesOrderHelper.CalculateTaxBaseAmount(qty, nettPrice, discountPercent, extraDiscount);
var valueAddedAmount = salesOrderHelper.CalculateValueAddedAmount(taxBaseAmount);
var netValue = salesOrderHelper.CalculateNettValueAmount(taxBaseAmount, valueAddedAmount);
xlWorkSheet.Cells[rowNumber, 1].NumberFormat = "dd/mm/yy;@";
xlWorkSheet.Cells[rowNumber, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlWorkSheet.Cells[rowNumber, 1] = DateTime.Parse(item["SalesDate"].ToString());
xlWorkSheet.Cells[rowNumber, 2] = item["SalesNo"].ToString().Trim();
xlWorkSheet.Cells[rowNumber, 3].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 3] = item["CustomerCode"].ToString().Trim();
xlWorkSheet.Cells[rowNumber, 4] = item["CustomerName"].ToString();
xlWorkSheet.Cells[rowNumber, 5].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 5] = item["CustomerNpwp"].ToString();
xlWorkSheet.Cells[rowNumber, 6] = item["CustomerAddress"].ToString();
xlWorkSheet.Cells[rowNumber, 7] = item["CustomerDistrict"].ToString();
xlWorkSheet.Cells[rowNumber, 8] = item["SalesmanCode"].ToString();
xlWorkSheet.Cells[rowNumber, 9] = item["Rayon"].ToString();
xlWorkSheet.Cells[rowNumber, 10] = item["Representative"].ToString();
xlWorkSheet.Cells[rowNumber, 11] = item["OutletType"].ToString();
xlWorkSheet.Cells[rowNumber, 12] = item["ProductCode"].ToString();
xlWorkSheet.Cells[rowNumber, 13] = item["ProductName"].ToString();
xlWorkSheet.Cells[rowNumber, 14].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 14] = item["BatchCode"].ToString();
xlWorkSheet.Cells[rowNumber, 15].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 15].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlWorkSheet.Cells[rowNumber, 15] = DateTime.Parse(item["ExpiredDate"].ToString()).ToString("MM/yyyy");
xlWorkSheet.Cells[rowNumber, 16] = item["UomCode"].ToString();
xlWorkSheet.Cells[rowNumber, 17].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 17] = qty;
xlWorkSheet.Cells[rowNumber, 18].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 18] = nettPrice;
xlWorkSheet.Cells[rowNumber, 19].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 19] = grossValue;
xlWorkSheet.Cells[rowNumber, 20].NumberFormat = "0,0%";
xlWorkSheet.Cells[rowNumber, 20] = discountPercent;
xlWorkSheet.Cells[rowNumber, 21].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 21] = propotionalExtraDiscount;
xlWorkSheet.Cells[rowNumber, 22].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 22] = totalDiscount;
xlWorkSheet.Cells[rowNumber, 23].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 24] = taxBaseAmount;
xlWorkSheet.Cells[rowNumber, 24].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 24] = valueAddedAmount;
xlWorkSheet.Cells[rowNumber, 25].NumberFormat = "#.##0";
xlWorkSheet.Cells[rowNumber, 25] = netValue;
xlWorkSheet.Cells[rowNumber, 26].NumberFormat = "dd/mm/yy;@";
xlWorkSheet.Cells[rowNumber, 26].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
xlWorkSheet.Cells[rowNumber, 26] = DateTime.Parse(item["DueDate"].ToString());
xlWorkSheet.Cells[rowNumber, 27] = item["DeliveryAddress"].ToString();
xlWorkSheet.Cells[rowNumber, 28] = item["DeliveryDistrict"].ToString();
xlWorkSheet.Cells[rowNumber, 29].NumberFormat = "@";
xlWorkSheet.Cells[rowNumber, 29] = item["Phone"].ToString();
xlWorkSheet.Cells[rowNumber, 30] = item["PrincipalName"].ToString();
var progress = ((rowNumber - 1) * 100) / totalRecord;
backgroundWorker.ReportProgress(progress);
rowNumber++;
}
xlWorkSheet.Columns.AutoFit();
var fileDir = Directory.CreateDirectory(System.IO.Path.Combine(Environment.CurrentDirectory, "REKAPITULASI", "UBICAPS"));
var fileName = "REKAPITULASI FAKTUR_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");
fileLoc = Path.Combine(fileDir.FullName, fileName);
xlWorkBook.SaveAs(fileLoc, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
catch (Exception ex)
{
exceptionMessage = ex.Message;
}
}