Untitled
unknown
plain_text
2 years ago
11 kB
3
Indexable
Never
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WassProITC.Business.Data;
using WassProITC.Models;
using WassProITC.Utilities;
namespace WassProITC.Modules.ThongKePhieuThuCuaNV
{
//[SysAuthorize, Authorize(Roles = "ThongKePhieuThuCuaNVIndex")]
public class ThongKePhieuThuCuaNVController : Controller
{
SPContext db = new SPContext();
decimal cnID=0;
// GET: ThongKePhieuThuCuaNV
public ThongKePhieuThuCuaNVController()
{
cnID = (decimal)db.NhanViens.FirstOrDefault(x => x.NhanVienID == db.Users.FirstOrDefault(c => c.UserID == SessionStore.UserData.UserID).NhanVienID).ChiNhanhID;
}
public ActionResult Index()
{
//cần : danh sách khách hàng, lộ trình- lộ trình dựa theo chi nhánh id, nhân viên, giá
decimal userID = SessionStore.UserData.UserID;
decimal? nvid = (from a in db.Users where (a.UserID == userID) select a.NhanVienID).FirstOrDefault();
decimal? chinhanhid = (from a in db.NhanViens where (a.NhanVienID == nvid) select a.ChiNhanhID).FirstOrDefault();
ViewBag.ChiNhanhFilter = new SelectList(db.ChiNhanhs.Where(x => x.ChiNhanhID == chinhanhid), "ChiNhanhID", "TenCN"); //lấy ra combobox tên chi nhánh
decimal? lotrinhid = (from a in db.LoTrinhs where (a.ChiNhanhID == chinhanhid) select a.LoTrinhID).FirstOrDefault();
var NhanVien = db.NhanViens.Where(z => z.ChiNhanhID == cnID).OrderBy(x => x.NhanVienID).ToList();
var kh = (from a in db.DTKhachHangs where a.LoTrinhID == lotrinhid select new{HoTenKH = a.HoTenKH,a.KhachHangID}).ToList();
ViewBag.TenNVFilter = new SelectList(NhanVien, "NhanVienID", "HoTenNV"); // lấy ra combobox tên nv
var lt = (from a in db.LoTrinhs where a.ChiNhanhID == chinhanhid select new {TenLT = a.MaLT + " - " + a.TenLT, a.LoTrinhID}).ToList();
ViewBag.LoTrinhFilter = new SelectList(lt, "LoTrinhID", "TenLT");
ViewBag.KhachHangFilter = new SelectList(kh, "KhachHangID", "HoTenKH");
return View();
}
//
public JsonResult GetLoTrinh(int ChiNhanhID = 0)
{
var lt = db.LoTrinhs.Where(x => x.ChiNhanhID == ChiNhanhID).Select(x => new { LoTrinhID = x.LoTrinhID, TenLT = x.MaLT + " - " + x.TenLT });
return Json(lt, JsonRequestBehavior.AllowGet);
}
public JsonResult GetBangKePhieuThuCuaNV(decimal ChiNhanhID=0, decimal LoTrinhID=0, decimal NVID = 0, decimal KHID = 0)
{
var bc = BusThongKePhieuThuCuaNV.RPThongKePhieuThuCuaNV(db, ChiNhanhID, LoTrinhID, NVID, KHID).AsEnumerable();
var search = "";
if (!string.IsNullOrEmpty(Request.Form["search[value]"]))
{
search = Request.Form["search[value]"];
}
var data = bc.Select(s => new
{
PhieuThuID = s.PhieuThuID,
SoPhieu = s.SoPhieu,
KhachHangID = s.KhachHangID,
HoTenKH = s.HoTenKH,
LayHoaDon = s.LayHoaDon,
LoTrinhID = s.LoTrinhID,
TenLT =s.TenLT,
GiaID = s.GiaID,
MaGia = s.MaGia,
DonGia = s.DonGia,
DonGia0V = s.DonGia0V,
ThueSuat = s.ThueSuat,
HinhThucTT = s.HinhThucTT,
NhanVienThu = s.NhanVienThu,
NgayThu = s.NgayThu.HasValue ? s.NgayThu.Value.ToString("dd/MM/yyyy") : "",
LyDo = s.LyDo,
GhiChu= s.GhiChu,
}).ToList() ;
var length = Int32.Parse(Request.Form["length"]);
var start = Int32.Parse(Request.Form["start"]);
var result = new
{
draw = Request.Form["draw"],
recordsTotal = data.Count(),
recordsFiltered = data.Count(),
data = data.Skip(start).Take(length)
};
return Json(result, JsonRequestBehavior.AllowGet);
}
public ActionResult ExportExcelPhieuThuCuaNV( decimal ChiNhanhID = 0, decimal LoTrinhID = 0, decimal KHID = 0, decimal NVID = 0)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
// BusBangKeKhachHangNgungSDNuoc adt = new BusBangKeKhachHangNgungSDNuoc();
var dataExport = BusThongKePhieuThuCuaNV.RPThongKePhieuThuCuaNV(db, ChiNhanhID, LoTrinhID, KHID, NVID).ToList();
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0}", "sheet1"));
using (ExcelRange cells = workSheet.Cells["A1:C1"])
{
cells.Value = "CÔNG TY............";
cells.Merge = true;
cells.Style.Font.Size = 12;
cells.Style.Font.Bold = true;
cells.Style.Font.Name = "Times New Roman";
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
using (ExcelRange cells = workSheet.Cells["D1:N1"])
{
cells.Value = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM";
cells.Merge = true;
cells.Style.Font.Size = 12;
cells.Style.Font.Name = "Times New Roman";
cells.Style.Font.Bold = true;
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
using (ExcelRange cells = workSheet.Cells["D2:N2"])
{
cells.Value = "Độc lập - Tự do - Hạnh phúc";
cells.Merge = true;
cells.Style.Font.Size = 12;
cells.Style.Font.Name = "Times New Roman";
cells.Style.Font.Bold = true;
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
using (ExcelRange cells = workSheet.Cells["D3:N3"])
{
cells.Value = "------------------";
cells.Merge = true;
cells.Style.Font.Size = 12;
cells.Style.Font.Name = "Times New Roman";
cells.Style.Font.Bold = true;
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
using (ExcelRange cells = workSheet.Cells["A5:N5"])
{
cells.Value = "DANH SÁCH PHIẾU THU CỦA NHÂN VIÊN";
cells.Merge = true;
cells.Style.Font.Size = 15;
cells.Style.Font.Name = "Times New Roman";
cells.Style.Font.Bold = true;
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
string[] valHeaderTbl = { "STT", "Số phiếu", "Tên khách hàng", "Lấy hóa đơn", "Tên lộ trình", "Mã giá", "Đơn giá", "Đơn giá 0VAT", "Thuế suất", "Hình thức thanh toán", "Nhân viên thu", "Ngày thu", "Lý do", "Ghi chú" };
int lengHeaderTbl = valHeaderTbl.Length;
int colHeader;
int rowHeader = 8;
int valBegin = 1;
for (colHeader = valBegin; colHeader < valBegin + lengHeaderTbl; colHeader++)
{
int j = colHeader - valBegin;
workSheet.Cells[rowHeader, colHeader].Value = valHeaderTbl[j];
workSheet.Cells[rowHeader, colHeader].Style.Font.Name = "Time New Roman";
workSheet.Cells[rowHeader, colHeader].Style.Font.Size = 11;
workSheet.Cells[rowHeader, colHeader].Style.Font.Bold = true;
workSheet.Cells[rowHeader, colHeader].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
int startRowFrom = rowHeader + 1;
if (dataExport.Count() > 0)
{
ExcelExportHelper.SetStyleHeader(workSheet.Cells["A" + rowHeader + ":N" + (rowHeader)]);
for (int j = 0; j < dataExport.Count; j++)
{
var item = dataExport[j];
int i = j + 1;
// noi dung
workSheet.Cells[startRowFrom, 1].Value = i;
workSheet.Cells[startRowFrom, 2].Value = item.SoPhieu;
workSheet.Cells[startRowFrom, 3].Value = item.HoTenKH;
workSheet.Cells[startRowFrom, 4].Value = item.LayHoaDon;
workSheet.Cells[startRowFrom, 5].Value = item.TenLT;
workSheet.Cells[startRowFrom, 6].Value = item.MaGia;
workSheet.Cells[startRowFrom, 7].Value = item.DonGia;
workSheet.Cells[startRowFrom, 8].Value = item.DonGia0V;
workSheet.Cells[startRowFrom, 9].Value = item.ThueSuat;
workSheet.Cells[startRowFrom, 10].Value = item.HinhThucTT;
workSheet.Cells[startRowFrom, 11].Value = item.NhanVienThu;
workSheet.Cells[startRowFrom, 12].Value = item.NgayThu.HasValue ? item.NgayThu.Value.ToString("dd/MM/yyyy") : "";
workSheet.Cells[startRowFrom, 13].Value = item.LyDo;
workSheet.Cells[startRowFrom, 14].Value = item.GhiChu;
for (int style = 1; style <= lengHeaderTbl; style++)
{
workSheet.Cells[startRowFrom, style].Style.Font.Name = "Time New Roman";
workSheet.Cells[startRowFrom, style].Style.Font.Size = 11;
}
startRowFrom++;
}
ExcelExportHelper.SetStyleContent(workSheet.Cells[rowHeader, 1, startRowFrom - 1, lengHeaderTbl]);
}
ExcelExportHelper.SetAutoFit(workSheet, lengHeaderTbl);
result = package.GetAsByteArray();
}
return File(result, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ExportExcelDSPhieuThuCuaNV.xlsx");
}
}
}