public List <NvKiemKeVm.ObjectReport> ReportKiemKe(ParameterKiemKe pi)
{
List <BTS.API.SERVICE.NV.NvKiemKeVm.ObjectReport> data = new List <BTS.API.SERVICE.NV.NvKiemKeVm.ObjectReport>();
var titleCotName = "";
var dieuKienLoc = "";
switch (pi.ReportType)
{
case TypeReportKiemKe.BAOCAOTHUA:
dieuKienLoc = "AND NVL(ct.SOLUONGCHENHLECH,0) < 0";
titleCotName += "(Báo cáo thừa) ";
break;
case TypeReportKiemKe.BAOCAOTHIEU:
dieuKienLoc = "AND NVL(ct.SOLUONGCHENHLECH,0) > 0";
titleCotName += "(Báo cáo thiếu) ";
break;
default:
dieuKienLoc = " ";
break;
}
switch (pi.GroupBy)
{
case TypeGroupKiemKe.TYPE:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MALOAIVATTU", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Loại hàng";
break;
case TypeGroupKiemKe.WAREHOUSE:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MAKHOHANG", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Kho hàng";
break;
case TypeGroupKiemKe.GROUP:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MANHOMVATTU", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Nhóm hàng";
break;
case TypeGroupKiemKe.NHACUNGCAP:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MANHACUNGCAP", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Nhà cung cấp";
break;
case TypeGroupKiemKe.KEHANG:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MAKEHANG", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Kệ hàng";
break;
case TypeGroupKiemKe.MERCHANDISE:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MAVATTU", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Mặt hàng";
break;
default:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MAVATTU", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Mặt hàng";
break;
}
return(data);
}
public MemoryStream ExportExcelTongHop(ParameterKiemKe pi)
{
List <NvKiemKeVm.ObjectReport> itemCollectionGroup = new List <NvKiemKeVm.ObjectReport>();
List <BTS.API.SERVICE.NV.NvKiemKeVm.ObjectReport> data = new List <BTS.API.SERVICE.NV.NvKiemKeVm.ObjectReport>();
var titleCotName = "";
var dieuKienLoc = "";
switch (pi.ReportType)
{
case TypeReportKiemKe.BAOCAOTHUA:
dieuKienLoc = "AND NVL(ct.SOLUONGCHENHLECH,0) < 0";
titleCotName += "(Báo cáo thừa) ";
break;
case TypeReportKiemKe.BAOCAOTHIEU:
dieuKienLoc = "AND NVL(ct.SOLUONGCHENHLECH,0) > 0";
break;
default:
dieuKienLoc = " ";
titleCotName += "(Báo cáo thiếu) ";
break;
}
switch (pi.GroupBy)
{
case TypeGroupKiemKe.TYPE:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MALOAIVATTU", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Loại hàng";
break;
case TypeGroupKiemKe.WAREHOUSE:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MAKHOHANG", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Kho hàng";
break;
case TypeGroupKiemKe.GROUP:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MANHOMVATTU", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Nhóm hàng";
break;
case TypeGroupKiemKe.NHACUNGCAP:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MANHACUNGCAP", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Nhà cung cấp";
break;
case TypeGroupKiemKe.KEHANG:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MAKEHANG", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Kệ hàng";
break;
case TypeGroupKiemKe.MERCHANDISE:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MAVATTU", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Mặt hàng";
break;
default:
data = ProcedureCollection.KKTongHop(dieuKienLoc, "MAVATTU", pi.WareHouseCodes, pi.MerchandiseTypeCodes, pi.MerchandiseGroupCodes, pi.MerchandiseCodes, pi.NhaCungCapCodes, pi.KeHangCodes, pi.UnitCode, pi.FromDate, pi.ToDate);
titleCotName += "Mặt hàng";
break;
}
if (data != null)
{
itemCollectionGroup.AddRange(data.ToList());
}
using (ExcelPackage package = new ExcelPackage())
{
var ms = new MemoryStream();
package.Workbook.Worksheets.Add("Data");
var worksheet = package.Workbook.Worksheets[1];
int startRow = 6;
int startColumn = 1;
///Header
///
worksheet.Cells[1, 1, 1, 10].Merge = true;
worksheet.Cells[1, 1].Value = "NHẬP XUẤT KIỂM KÊ"; worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[2, 1].Value =
string.Format("Từ ngày: {0}/{1}/{2} Đến ngày: {3}/{4}/{5}",
pi.FromDate.Day, pi.FromDate.Month, pi.FromDate.Year, pi.ToDate.Day, pi.ToDate.Month, pi.ToDate.Year);
worksheet.Cells[2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[2, 1, 2, 10].Merge = true;
worksheet.Cells[3, 1, 3, 10].Merge = true;
worksheet.Cells[4, 1, 5, 1].Merge = true;
worksheet.Cells[4, 2, 5, 2].Merge = true;
worksheet.Cells[4, 3, 5, 3].Merge = true;
worksheet.Cells[4, 4, 5, 4].Merge = true;
worksheet.Cells[4, 5, 5, 5].Merge = true;
worksheet.Cells[4, 6, 5, 6].Merge = true;
worksheet.Cells[4, 7, 4, 8].Merge = true;
worksheet.Cells[4, 9, 4, 10].Merge = true;
worksheet.Cells[3, 1].Value = "Điều kiện, Nhóm theo: " + titleCotName;
worksheet.Cells[4, 1].Value = "STT"; worksheet.Cells[4, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 2].Value = "Mã"; worksheet.Cells[4, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 3].Value = "Danh sách"; worksheet.Cells[4, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 4].Value = "Giá vốn"; worksheet.Cells[4, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 5].Value = "SL Máy"; worksheet.Cells[4, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 6].Value = "SL KK"; worksheet.Cells[4, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 7].Value = "Thừa"; worksheet.Cells[4, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 9].Value = "Thiếu"; worksheet.Cells[4, 9].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 10].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 7].Value = "SL"; worksheet.Cells[5, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 8].Value = "Giá trị"; worksheet.Cells[5, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 9].Value = "SL"; worksheet.Cells[5, 9].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 10].Value = "Giá trị"; worksheet.Cells[5, 10].Style.Border.BorderAround(ExcelBorderStyle.Thin);
var itemTotal = new NvKiemKeVm.ObjectReport();
int currentRow = startRow;
int stt = 0;
foreach (var item in itemCollectionGroup)
{
++stt;
worksheet.Cells[currentRow, startColumn].Value = stt.ToString();
worksheet.Cells[currentRow, startColumn + 1].Value = item.Ma;
worksheet.Cells[currentRow, startColumn + 2].Value = item.Ten;
worksheet.Cells[currentRow, startColumn + 3].Value = item.GiaVon; worksheet.Cells[currentRow, 4].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 4].Value = item.SoLuongTonMay;
worksheet.Cells[currentRow, startColumn + 5].Value = item.SoLuongKiemKe;
worksheet.Cells[currentRow, startColumn + 6].Value = item.SoLuongThua;
worksheet.Cells[currentRow, startColumn + 7].Value = item.GiaTriThua; worksheet.Cells[currentRow, 8].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 8].Value = item.SoLuongThieu;
worksheet.Cells[currentRow, startColumn + 9].Value = item.GiaTriThieu; worksheet.Cells[currentRow, 10].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, 1, currentRow, startColumn + 9].Style.Border.BorderAround(ExcelBorderStyle.Dotted);
itemTotal.GiaVon += item.GiaVon;
itemTotal.SoLuongTonMay += item.SoLuongTonMay;
itemTotal.SoLuongKiemKe += item.SoLuongKiemKe;
itemTotal.SoLuongThua += item.SoLuongThua;
itemTotal.GiaTriThua += item.SoLuongThua;
itemTotal.SoLuongThieu += item.SoLuongThieu;
itemTotal.GiaTriThieu += item.GiaTriThieu;
currentRow++;
}
worksheet.Cells[currentRow, 1, currentRow, startColumn + 2].Merge = true;
worksheet.Cells[currentRow, startColumn].Value = "TỔNG CỘNG"; worksheet.Cells[currentRow, startColumn].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[currentRow, startColumn + 3].Value = itemTotal.GiaVon; worksheet.Cells[currentRow, startColumn + 3].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 4].Value = itemTotal.SoLuongTonMay;
worksheet.Cells[currentRow, startColumn + 5].Value = itemTotal.SoLuongKiemKe;
worksheet.Cells[currentRow, startColumn + 6].Value = itemTotal.SoLuongThua;
worksheet.Cells[currentRow, startColumn + 7].Value = itemTotal.GiaTriThua; worksheet.Cells[currentRow, startColumn + 7].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 8].Value = itemTotal.SoLuongThieu;
worksheet.Cells[currentRow, startColumn + 9].Value = itemTotal.GiaTriThieu; worksheet.Cells[currentRow, startColumn + 9].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, 1, currentRow, startColumn + 9].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Column(1).AutoFit();
worksheet.Column(2).AutoFit();
worksheet.Column(3).AutoFit();
worksheet.Column(4).AutoFit();
worksheet.Column(5).AutoFit();
worksheet.Column(6).AutoFit();
worksheet.Column(7).AutoFit();
worksheet.Column(8).AutoFit();
worksheet.Column(9).AutoFit();
worksheet.Column(10).AutoFit();
int totalRows = worksheet.Dimension.End.Row;
int totalCols = worksheet.Dimension.End.Column;
var dataCells = worksheet.Cells[1, 1, totalRows, totalCols];
var dataFont = dataCells.Style.Font;
dataFont.SetFromFont(new System.Drawing.Font("Times New Roman", 10));
package.SaveAs(ms);
return(ms);
}
}