public MemoryStream ReportDatHangChiTiet(NvPhieuDatHangVm.ParameterDatHang pi)
{
List <NvPhieuDatHangVm.DatHangExpImpChiTiet> data = GetDataReportDatHangChiTiet(pi);
DateTime beginDay = new DateTime(pi.ToDate.Year, pi.ToDate.Month, pi.ToDate.Day, 0, 0, 0);
DateTime endDay = new DateTime(pi.FromDate.Year, pi.FromDate.Month, pi.FromDate.Day, 0, 0, 0);
using (ExcelPackage package = new ExcelPackage())
{
var ms = new MemoryStream();
package.Workbook.Worksheets.Add("Data");
var worksheet = package.Workbook.Worksheets[1];
int startRow = 4;
int startColumn = 1;
worksheet.Cells[1, 1, 1, 7].Merge = true;
worksheet.Cells[1, 1].Value = "Báo cáo Đặt hàng"; 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}", beginDay.Day, beginDay.Month, beginDay.Year, endDay.Day, endDay.Month, endDay.Year);
worksheet.Cells[2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[2, 1, 2, 7].Merge = true;
worksheet.Cells[3, 1, 3, 7].Merge = true;
worksheet.Cells[3, 1].Value = "Điều kiện, Nhóm theo";
worksheet.Cells[4, 1].Value = "STT"; worksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 2].Value = "Mã"; worksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 3].Value = "Tên hàng"; worksheet.Cells[5, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 4].Value = "Tên"; worksheet.Cells[5, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 5].Value = "Đơn giá"; worksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 6].Value = "Số lượng"; worksheet.Cells[5, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin);
worksheet.Cells[4, 7].Value = "Thành tiền"; worksheet.Cells[5, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin);
var itemTotal = new NvPhieuDatHangVm.DatHangExpImpChiTiet();
int currentRow = startRow;
int stt = 0;
foreach (var item in data)
{
stt = 0;
worksheet.Cells[currentRow, 1, currentRow, 7].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.Code + " - " + item.Name;
currentRow++;
foreach (var itemdetail in item.DataDetail)
{
++stt;
worksheet.Cells[currentRow, startColumn].Value = stt.ToString();
worksheet.Cells[currentRow, startColumn + 1].Value = itemdetail.Code;
worksheet.Cells[currentRow, startColumn + 2].Value = itemdetail.MaHang;
worksheet.Cells[currentRow, startColumn + 3].Value = itemdetail.TenHang;
//worksheet.Cells[currentRow, startColumn + 4].Value = itemdetail.Name;
worksheet.Cells[currentRow, startColumn + 4].Value = itemdetail.DonGia; worksheet.Cells[currentRow, startColumn + 4].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 5].Value = itemdetail.SoLuong; worksheet.Cells[currentRow, startColumn + 5].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 6].Value = itemdetail.ThanhTien; worksheet.Cells[currentRow, startColumn + 6].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, 1, currentRow, startColumn + 6].Style.Border.BorderAround(ExcelBorderStyle.Dotted);
itemTotal.SoLuong = itemTotal.SoLuong + itemdetail.SoLuong;
itemTotal.ThanhTien = itemTotal.ThanhTien + itemdetail.ThanhTien;
currentRow++;
}
}
worksheet.Cells[currentRow, 1, currentRow, startColumn + 4].Merge = true;
worksheet.Cells[currentRow, startColumn].Value = "TỔNG CỘNG"; worksheet.Cells[currentRow, startColumn].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[currentRow, startColumn + 5].Value = itemTotal.SoLuong; worksheet.Cells[currentRow, startColumn + 4].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells[currentRow, startColumn + 6].Value = itemTotal.ThanhTien; worksheet.Cells[currentRow, startColumn + 4].Style.Numberformat.Format = "#,##0.00";
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();
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);
}
}
public List <NvPhieuDatHangVm.DatHangExpImpChiTiet> GetDataReportDatHangChiTiet(NvPhieuDatHangVm.ParameterDatHang pi)
{
List <NvPhieuDatHangVm.DatHangExpImpChiTiet> data = new List <NvPhieuDatHangVm.DatHangExpImpChiTiet>();
List <NvPhieuDatHangVm.DatHangExpImpDetailChiTiet> dataTemp = new List <NvPhieuDatHangVm.DatHangExpImpDetailChiTiet>();
DateTime beginDay, endDay;
string ky = string.Empty;
beginDay = new DateTime(pi.FromDate.Year, pi.FromDate.Month, pi.FromDate.Day, 0, 0, 0);
endDay = new DateTime(pi.FromDate.Year, pi.FromDate.Month, pi.FromDate.Day, 23, 59, 59);
string merchandiseCodes = "";
string nhaCungCapCodes = "";
string NhanVienCodes = "";
string groupby = "";
using (var ctx = new ERPContext())
{
using (var dbContextTransaction = ctx.Database.BeginTransaction())
{
try
{
merchandiseCodes = _convertToArrayCondition(pi.MerchandiseCodes);
nhaCungCapCodes = _convertToArrayCondition(pi.NhaCungCapCodes);
NhanVienCodes = _convertToArrayCondition(pi.NhanVienCodes);
groupby = pi.GroupBy.ToString();
var pGroupBy = new OracleParameter("pGroupBy", OracleDbType.NVarchar2, groupby, ParameterDirection.Input);
var pMerchandiseCode = new OracleParameter("pMerchandiseCode", OracleDbType.NVarchar2, merchandiseCodes, ParameterDirection.Input);
var pNhaCungCapCode = new OracleParameter("pNhaCungCapCode", OracleDbType.NVarchar2, nhaCungCapCodes, ParameterDirection.Input);
var pNhanVienCode = new OracleParameter("pNhanVienCode", OracleDbType.NVarchar2, NhanVienCodes, ParameterDirection.Input);
var pTrangThai = new OracleParameter("pTrangThai", OracleDbType.NVarchar2, pi.TrangThaiDatHang, ParameterDirection.Input);
var pUnitCode = new OracleParameter("pUnitCode", OracleDbType.NVarchar2, pi.UnitCode, ParameterDirection.Input);
var pToDate = new OracleParameter("pToDate", OracleDbType.Date, pi.ToDate, ParameterDirection.Input);
var pFromDate = new OracleParameter("pFromDate", OracleDbType.Date, pi.FromDate, ParameterDirection.Input);
var outRef = new OracleParameter("outRef", OracleDbType.RefCursor, ParameterDirection.Output);
var str = "BEGIN TBNETERP.DATHANG.DATHANGCHITIET(:pGroupBy, :pTrangThai, :pNhanVienCode, :pMerchandiseCode, :pNhaCungCapCode,:pUnitCode, :pToDate,:pFromDate, :outRef); END;";
ctx.Database.ExecuteSqlCommand(str, pGroupBy, pTrangThai, pNhanVienCode, pMerchandiseCode, pNhaCungCapCode, pUnitCode, pToDate, pFromDate, outRef);
OracleDataReader reader = ((OracleRefCursor)outRef.Value).GetDataReader();
while (reader.Read())
{
decimal soLuong, donGia;
var isSoLuong = decimal.TryParse(reader["SOLUONG"].ToString(), out soLuong);
var isDonGia = decimal.TryParse(reader["DONGIA"].ToString(), out donGia);
var item = new NvPhieuDatHangVm.DatHangExpImpDetailChiTiet();
switch ((int)pi.GroupBy)
{
case 1:
item = new NvPhieuDatHangVm.DatHangExpImpDetailChiTiet()
{
Code = reader["TRANGTHAI"].ToString(),
Name = _parseTrangThai(reader["TRANGTHAI"].ToString()),
MaHang = reader["MAHANG"].ToString(),
TenHang = reader["TENHANG"].ToString(),
SoLuong = isSoLuong ? soLuong : 0,
DonGia = isDonGia ? donGia : 0,
ThanhTien = (isSoLuong ? soLuong : 0) * (isDonGia ? donGia : 0),
};
dataTemp.Add(item);
break;
case 2:
item = new NvPhieuDatHangVm.DatHangExpImpDetailChiTiet()
{
//SoPhieu = reader["SOPHIEU"].ToString(),
SoLuong = isSoLuong ? soLuong : 0,
DonGia = isDonGia ? donGia : 0,
MaHang = reader["MAHANG"].ToString(),
TenHang = reader["TENHANG"].ToString(),
ThanhTien = (isSoLuong ? soLuong : 0) * (isDonGia ? donGia : 0),
Code = reader["MANHANVIEN"].ToString(),
Name = reader["TENNHANVIEN"].ToString(),
};
dataTemp.Add(item);
break;
case 3:
break;
case 4:
item = new NvPhieuDatHangVm.DatHangExpImpDetailChiTiet()
{
//SoPhieu = reader["SOPHIEU"].ToString(),
SoLuong = isSoLuong ? soLuong : 0,
DonGia = isDonGia ? donGia : 0,
MaHang = reader["MAHANG"].ToString(),
TenHang = reader["TENHANG"].ToString(),
ThanhTien = (isSoLuong ? soLuong : 0) * (isDonGia ? donGia : 0),
Code = reader["MAKHACHHANG"].ToString(),
Name = reader["Tenkh"].ToString(),
};
dataTemp.Add(item);
break;
}
}
var lstTemp = dataTemp.GroupBy(x => x.Code);
lstTemp.ToList().ForEach(x => {
NvPhieuDatHangVm.DatHangExpImpChiTiet model = new NvPhieuDatHangVm.DatHangExpImpChiTiet();
model.DataDetail = new List <NvPhieuDatHangVm.DatHangExpImpDetailChiTiet>();
model.Code = x.Key;
var children = dataTemp.Where(i => i.Code == x.Key).ToList();
if (children[0] != null)
{
model.Name = children[0].Name;
}
model.DataDetail.AddRange(children);
data.Add(model);
});
dbContextTransaction.Commit();
return(data);
}
catch
{
dbContextTransaction.Rollback();
throw new Exception("Xảy ra lỗi trong khi chạy Store_Procude. Tự động rollback!");
}
}
}
}