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 ExportExcelDetail(ParameterKiemKe pi)
{
var itemCollection = new List <NvKiemKeVm.ObjectReportCha>();
List <NvKiemKeVm.ObjectReportCha> itemCollectionGroup = new List <NvKiemKeVm.ObjectReportCha>();
List <BTS.API.SERVICE.NV.NvKiemKeVm.ObjectReportCha> data = new List <BTS.API.SERVICE.NV.NvKiemKeVm.ObjectReportCha>();
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.KKChiTiet(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.KKChiTiet(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.KKChiTiet(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.KKChiTiet(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.KKChiTiet(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.KKChiTiet(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.KKChiTiet(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, 11].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, 11].Merge = true;
worksheet.Cells[3, 1, 3, 11].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, 11, 5, 11].Merge = true;
worksheet.Cells[4, 12, 5, 12].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, 11].Value = "Ngày KK"; worksheet.Cells[4, 11].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 12].Value = "Barcode"; worksheet.Cells[4, 12].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, 11].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[5, 12].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 = 0;
worksheet.Cells[currentRow, 1, currentRow, 12].Merge = true;
worksheet.Cells[currentRow, startColumn].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[currentRow, startColumn].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(252, 245, 76));
worksheet.Cells[currentRow, startColumn].Value = item.Ma + " - " + item.Ten;
currentRow++;
foreach (var itemdetails in item.DataDetails)
{
++stt;
worksheet.Cells[currentRow, startColumn].Value = stt.ToString();
worksheet.Cells[currentRow, startColumn + 1].Value = itemdetails.Ma;
worksheet.Cells[currentRow, startColumn + 2].Value = itemdetails.Ten;
worksheet.Cells[currentRow, startColumn + 3].Value = itemdetails.GiaVon; worksheet.Cells[currentRow, 4].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 4].Value = itemdetails.SoLuongTonMay;
worksheet.Cells[currentRow, startColumn + 5].Value = itemdetails.SoLuongKiemKe;
worksheet.Cells[currentRow, startColumn + 6].Value = itemdetails.SoLuongThua;
worksheet.Cells[currentRow, startColumn + 7].Value = itemdetails.GiaTriThua; worksheet.Cells[currentRow, 8].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 8].Value = itemdetails.SoLuongThieu;
worksheet.Cells[currentRow, startColumn + 9].Value = itemdetails.GiaTriThieu; worksheet.Cells[currentRow, 10].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 10].Value = string.Format("{0}/{1}/{2}", itemdetails.NgayKiemKe.Day, itemdetails.NgayKiemKe.Month, itemdetails.NgayKiemKe.Year);
worksheet.Cells[currentRow, startColumn + 11].Value = itemdetails.Barcode;
worksheet.Cells[currentRow, 1, currentRow, startColumn + 11].Style.Border.BorderAround(ExcelBorderStyle.Dotted);
itemTotal.GiaVon += itemdetails.GiaVon;
itemTotal.SoLuongTonMay += itemdetails.SoLuongTonMay;
itemTotal.SoLuongKiemKe += itemdetails.SoLuongKiemKe;
itemTotal.SoLuongThua += itemdetails.SoLuongThua;
itemTotal.GiaTriThua += itemdetails.SoLuongThua;
itemTotal.SoLuongThieu += itemdetails.SoLuongThieu;
itemTotal.GiaTriThieu += itemdetails.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 + 11].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();
worksheet.Column(11).AutoFit();
worksheet.Column(12).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);
}
}