public ActionResult DownloadReport(int maNhom, int maThanhVien, string fromDate, string toDate, string maHS, string cmnd, int loaiNgay)
{
string newUrl = string.Empty;
try
{
List <HoSoDuyetModel> rs = new List <HoSoDuyetModel>();
DateTime dtFromDate = DateTime.MinValue, dtToDate = DateTime.MinValue;
if (fromDate != "")
{
dtFromDate = DateTimeFormat.ConvertddMMyyyyToDateTime(fromDate);
}
if (toDate != "")
{
dtToDate = DateTimeFormat.ConvertddMMyyyyToDateTime(toDate);
}
string trangthai = "";
trangthai += ((int)TrangThaiHoSo.TuChoi).ToString() + ","
+ ((int)TrangThaiHoSo.NhapLieu).ToString() + ","
+ ((int)TrangThaiHoSo.ThamDinh).ToString() + ","
+ ((int)TrangThaiHoSo.BoSungHoSo).ToString() + ","
+ ((int)TrangThaiHoSo.Cancel).ToString() + ","
+ ((int)TrangThaiHoSo.DaDoiChieu).ToString() + ","
+ ((int)TrangThaiHoSo.PCB).ToString() + ","
+ ((int)TrangThaiHoSo.GiaiNgan).ToString();
int totalRecord = new HoSoBLL().CountHosoDuyet(GlobalData.User.IDUser, maNhom, maThanhVien, dtFromDate, dtToDate, maHS, cmnd, loaiNgay, trangthai);
rs = new HoSoBLL().TimHoSoDuyet(GlobalData.User.IDUser, maNhom, maThanhVien, dtFromDate, dtToDate, maHS, cmnd, loaiNgay, trangthai, string.Empty, 1, totalRecord, true);
if (rs == null)
{
rs = new List <HoSoDuyetModel>();
}
string destDirectory = VS_LOAN.Core.Utility.Path.DownloadBill + "/" + DateTime.Now.Year.ToString() + "/" + DateTime.Now.Month.ToString() + "/";
bool exists = System.IO.Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + destDirectory);
if (!exists)
{
System.IO.Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + destDirectory);
}
string fileName = "Report-DSHS" + DateTime.Now.ToString("ddMMyyyyHHmmssfff") + ".xlsx";
using (FileStream stream = new FileStream(Server.MapPath(destDirectory + fileName), FileMode.CreateNew))
{
Byte[] info = System.IO.File.ReadAllBytes(Server.MapPath(VS_LOAN.Core.Utility.Path.ReportTemplate + "Report-DSHS.xlsx"));
stream.Write(info, 0, info.Length);
using (ZipArchive archive = new ZipArchive(stream, ZipArchiveMode.Update))
{
string nameSheet = "DSHS";
ExcelOOXML excelOOXML = new ExcelOOXML(archive);
int rowindex = 4;
if (rs != null)
{
excelOOXML.InsertRow(nameSheet, rowindex, rs.Count - 1, true);
for (int i = 0; i < rs.Count; i++)// dòng
{
excelOOXML.SetCellData(nameSheet, "A" + rowindex, (i + 1).ToString());
excelOOXML.SetCellData(nameSheet, "B" + rowindex, rs[i].MaHoSo.ToString());
excelOOXML.SetCellData(nameSheet, "C" + rowindex, rs[i].NgayTao.ToString("dd/MM/yyyy"));
excelOOXML.SetCellData(nameSheet, "D" + rowindex, rs[i].DoiTac);
excelOOXML.SetCellData(nameSheet, "E" + rowindex, rs[i].CMND);
excelOOXML.SetCellData(nameSheet, "F" + rowindex, rs[i].TenKH);
excelOOXML.SetCellData(nameSheet, "G" + rowindex, rs[i].TrangThaiHS);
excelOOXML.SetCellData(nameSheet, "H" + rowindex, rs[i].KetQuaHS);
excelOOXML.SetCellData(nameSheet, "I" + rowindex, rs[i].NgayCapNhat == DateTime.MinValue ? "" : rs[i].NgayCapNhat.ToString("dd/MM/yyyy"));
excelOOXML.SetCellData(nameSheet, "J" + rowindex, rs[i].MaNV);
excelOOXML.SetCellData(nameSheet, "K" + rowindex, rs[i].NhanVienBanHang);
excelOOXML.SetCellData(nameSheet, "L" + rowindex, rs[i].DoiNguBanHang);
excelOOXML.SetCellData(nameSheet, "M" + rowindex, rs[i].CoBaoHiem == true ? "N" : "Y");
excelOOXML.SetCellData(nameSheet, "N" + rowindex, rs[i].DiaChiKH);
excelOOXML.SetCellData(nameSheet, "O" + rowindex, rs[i].GhiChu);
excelOOXML.SetCellData(nameSheet, "P" + rowindex, rs[i].MaNVLayHS);
rowindex++;
}
}
archive.Dispose();
}
stream.Dispose();
}
bool result = true;
if (result)
{
newUrl = "/File/GetFile?path=" + destDirectory + fileName;
return(ToJsonResponse(true, null, newUrl));
}
return(ToJsonResponse(false));
}
catch (BusinessException ex)
{
return(ToJsonResponse(false, ex.Message));
}
}
public async Task <ActionResult> ExportFile(string freeText = null, int provinceId = 0, int courierId = 0, string status = null, int groupId = 0, int page = 1, int limit = 10, string salecode = null)
{
var request = new CourierSearchRequestModel
{
freeText = freeText,
provinceId = provinceId,
courierId = courierId,
status = status,
groupId = groupId,
page = page,
limit = limit,
salecode = salecode
};
try
{
string destDirectory = VS_LOAN.Core.Utility.Path.DownloadBill + "/" + DateTime.Now.Year.ToString() + "/" + DateTime.Now.Month.ToString() + "/";
bool exists = System.IO.Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + destDirectory);
if (!exists)
{
System.IO.Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + destDirectory);
}
string fileName = "Report-DSHS" + DateTime.Now.ToString("ddMMyyyyHHmmssfff") + ".xlsx";
using (FileStream stream = new FileStream(Server.MapPath(destDirectory + fileName), FileMode.CreateNew))
{
Byte[] info = System.IO.File.ReadAllBytes(Server.MapPath(VS_LOAN.Core.Utility.Path.ReportTemplate + "CourierExportTemplate.xlsx"));
stream.Write(info, 0, info.Length);
using (ZipArchive archive = new ZipArchive(stream, ZipArchiveMode.Update))
{
string nameSheet = "DSHS";
ExcelOOXML excelOOXML = new ExcelOOXML(archive);
int rowindex = 2;
long totalRecord = 100;
decimal totalPage = 10;
for (int p = 1; p <= totalPage; p++)
{
var result = await SearchByModel(request);
if (p == 1)
{
excelOOXML.InsertRow(nameSheet, rowindex, result.Datas.Count - 1, true);
}
totalPage = Math.Ceiling((decimal)result.TotalRecord / request.limit);
if (result != null)
{
totalRecord = result.TotalRecord;
for (int i = 0; i < result.Datas.Count; i++)// dòng
{
excelOOXML.SetCellData(nameSheet, "A" + rowindex, (i + 1).ToString());
excelOOXML.SetCellData(nameSheet, "B" + rowindex, result.Datas[i].CustomerName.ToString());
excelOOXML.SetCellData(nameSheet, "C" + rowindex, result.Datas[i].Phone);
excelOOXML.SetCellData(nameSheet, "D" + rowindex, result.Datas[i].Cmnd);
excelOOXML.SetCellData(nameSheet, "E" + rowindex, result.Datas[i].Status);
excelOOXML.SetCellData(nameSheet, "F" + rowindex, result.Datas[i].DistrictName);
excelOOXML.SetCellData(nameSheet, "G" + rowindex, result.Datas[i].ProvinceName);
excelOOXML.SetCellData(nameSheet, "H" + rowindex, result.Datas[i].AssignUser);
excelOOXML.SetCellData(nameSheet, "I" + rowindex, result.Datas[i].LastNote);
excelOOXML.SetCellData(nameSheet, "J" + rowindex, result.Datas[i].CreatedTime);
excelOOXML.SetCellData(nameSheet, "K" + rowindex, result.Datas[i].CreatedUser);
excelOOXML.SetCellData(nameSheet, "L" + rowindex, result.Datas[i].UpdatedTime);
excelOOXML.SetCellData(nameSheet, "M" + rowindex, result.Datas[i].UpdatedBy);
rowindex++;
}
//rowindex++;
}
}
archive.Dispose();
}
stream.Dispose();
}
var file = "/File/GetFile?path=" + destDirectory + fileName;
return(ToResponse(true, null, file));
}
catch (Exception e)
{
return(ToResponse(false));
}
//var result = await ExportUtil.Export<CourierSearchRequestModel, CourierExportModel>(Response, SearchByModel, request, "CourierProfiles.csv", columns, filePath);
}
public ActionResult DownloadReport(int maNhom, int maThanhVien, string fromDate, string toDate, string maHS, string cmnd, int loaiNgay)
{
if (GlobalData.User.IDUser != 1)
{
return(RedirectToAction("DanhSachHoSo"));
}
string newUrl = string.Empty;
try
{
List <HoSoQuanLyModel> rs = new List <HoSoQuanLyModel>();
DateTime dtFromDate = DateTime.MinValue, dtToDate = DateTime.MinValue;
if (fromDate != "")
{
dtFromDate = DateTimeFormat.ConvertddMMyyyyToDateTime(fromDate);
}
if (toDate != "")
{
dtToDate = DateTimeFormat.ConvertddMMyyyyToDateTime(toDate);
}
string trangthai = Helpers.Helpers.GetAllStatusString();
int totalRecord = new HoSoBLL().CountHoSoQuanLy(GlobalData.User.IDUser, maNhom, maThanhVien, dtFromDate, dtToDate, maHS, cmnd, trangthai, loaiNgay, freeText: null);
if (totalRecord <= 0)
{
return(ToResponse(false, "Không có dữ liệu"));
}
rs = new HoSoBLL().TimHoSoQuanLy(maNVDangNhap: GlobalData.User.IDUser,
maNhom: maNhom,
maThanhVien: maThanhVien,
tuNgay: dtFromDate,
denNgay: dtToDate,
maHS: maHS,
cmnd: cmnd,
trangthai: trangthai,
loaiNgay: loaiNgay,
freeText: string.Empty,
page: 1,
limit: totalRecord,
isDownload: true);
if (rs == null)
{
rs = new List <HoSoQuanLyModel>();
}
string destDirectory = VS_LOAN.Core.Utility.Path.DownloadBill + "/" + DateTime.Now.Year.ToString() + "/" + DateTime.Now.Month.ToString() + "/";
bool exists = System.IO.Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + destDirectory);
if (!exists)
{
System.IO.Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + destDirectory);
}
string fileName = "Report-DSHS" + DateTime.Now.ToString("ddMMyyyyHHmmssfff") + ".xlsx";
using (FileStream stream = new FileStream(Server.MapPath(destDirectory + fileName), FileMode.CreateNew))
{
Byte[] info = System.IO.File.ReadAllBytes(Server.MapPath(VS_LOAN.Core.Utility.Path.ReportTemplate + "Report-DSHS.xlsx"));
stream.Write(info, 0, info.Length);
using (ZipArchive archive = new ZipArchive(stream, ZipArchiveMode.Update))
{
string nameSheet = "DSHS";
ExcelOOXML excelOOXML = new ExcelOOXML(archive);
int rowindex = 4;
if (rs != null)
{
excelOOXML.InsertRow(nameSheet, rowindex, rs.Count - 1, true);
for (int i = 0; i < rs.Count; i++)// dòng
{
excelOOXML.SetCellData(nameSheet, "A" + rowindex, (i + 1).ToString());
excelOOXML.SetCellData(nameSheet, "B" + rowindex, rs[i].MaHoSo.ToString());
excelOOXML.SetCellData(nameSheet, "C" + rowindex, rs[i].NgayTao.ToString("dd/MM/yyyy"));
excelOOXML.SetCellData(nameSheet, "D" + rowindex, rs[i].DoiTac);
excelOOXML.SetCellData(nameSheet, "E" + rowindex, rs[i].CMND);
excelOOXML.SetCellData(nameSheet, "F" + rowindex, rs[i].Phone);
excelOOXML.SetCellData(nameSheet, "G" + rowindex, rs[i].TenKH);
excelOOXML.SetCellData(nameSheet, "H" + rowindex, rs[i].TrangThaiHS);
excelOOXML.SetCellData(nameSheet, "I" + rowindex, rs[i].KetQuaHS);
excelOOXML.SetCellData(nameSheet, "J" + rowindex, rs[i].NgayCapNhat == DateTime.MinValue ? "" : rs[i].NgayCapNhat.ToString("dd/MM/yyyy"));
excelOOXML.SetCellData(nameSheet, "K" + rowindex, rs[i].MaNV);
excelOOXML.SetCellData(nameSheet, "L" + rowindex, rs[i].NhanVienBanHang);
excelOOXML.SetCellData(nameSheet, "M" + rowindex, rs[i].DoiNguBanHang);
excelOOXML.SetCellData(nameSheet, "N" + rowindex, rs[i].CoBaoHiem == true ? "N" : "Y");
excelOOXML.SetCellData(nameSheet, "O" + rowindex, rs[i].KhuVucText);
excelOOXML.SetCellData(nameSheet, "P" + rowindex, rs[i].GhiChu);
excelOOXML.SetCellData(nameSheet, "Q" + rowindex, rs[i].MaNVLayHS);
rowindex++;
}
}
archive.Dispose();
}
stream.Dispose();
}
bool result = true;
if (result)
{
newUrl = "/File/GetFile?path=" + destDirectory + fileName;
return(ToResponse(true, newUrl));
}
return(ToResponse(false, null));
}
catch (BusinessException ex)
{
return(ToResponse(false, ex.Message));
}
}
