/// <summary>
/// Đẩy danh sách khách hàng xuống
/// </summary>
/// <param name="TTDoc"></param>
/// <param name="tuyen"></param>
public string pushKhachHangXuong(int TTDoc, int tuyen, long createdTime)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
var khachHang = _db.Khachhangs.Where(p => p.TTDoc >= TTDoc && p.TuyenKHID == tuyen && p.IsDelete == false).ToList();
foreach (var item in khachHang)
{
if (item.TTDoc >= TTDoc)
{
if (createdTime > item.UpdatedTime || item.UpdatedTime == null)
{
Khachhang kH = _db.Khachhangs.Find(item.KhachhangID);
if (kH.Ngaykyhopdong == null)
{
kH.Ngaykyhopdong = new DateTime(1970, 1, 1);
}
int thuTuDoc = kH.TTDoc.Value;
kH.TTDoc = thuTuDoc + 1;
_db.Entry(item).State = EntityState.Modified;
_db.SaveChanges();
}
}
}
return("");
}
/// <summary>
/// Lấy tên phòng ban dựa theo phòng ban ID
/// </summary>
/// <param name="phongBanID"></param>
/// <returns></returns>
public string getTenPhongBan(int?phongBanID)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
if (phongBanID != null)
{
return(_db.Phongbans.Find(phongBanID).Ten);
}
return("");
}
/// <summary>
/// Lấy nhân viên ID từ người dùng ID
/// </summary>
/// <param name="ngDungID"></param>
/// <returns></returns>
public int getNhanVienIDFromNguoiDungID(int ngDungID)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
Nguoidung ngDungObj = _db.Nguoidungs.Find(ngDungID);
if (ngDungObj != null)
{
return(ngDungObj.NhanvienID.Value);
}
return(0);
}
/// <summary>
/// Lấy thứ tự đọc của hóa đơn dựa theo hóa đơn ID
/// </summary>
/// <param name="hoaDonID"></param>
/// <returns></returns>
public int getThuTuDoc(int KHID)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
var khachHang = _db.Khachhangs.FirstOrDefault(p => p.KhachhangID == KHID);
if (khachHang != null)
{
return(khachHang.TTDoc.Value);
}
return(0);
}
/// <summary>
/// Kiểm tra xem hóa đơn đó có phải là hóa đơn áp giá đặc biệt hay không
/// </summary>
/// <returns></returns>
public bool isDacBiet(int hoaDonNuocID, String month, String year)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
var isDacBiet = (from dacbiet in _db.ApGiaDacBiets
join hoadon in _db.Hoadonnuocs on dacbiet.HoaDonNuocID.Value equals hoadon.HoadonnuocID
where dacbiet.HoaDonNuocID == hoaDonNuocID && hoadon.ThangHoaDon.ToString() == month && hoadon.NamHoaDon.ToString() == year
select new { }).Count();
if (isDacBiet > 0)
{
return(true);
}
return(false);
}
/// <summary>
/// Kiểm tra xem hóa đơn đã chốt hay chưa, nếu chưa chốt thì vẫn để mở
/// </summary>
/// <param name="hoadonID"></param>
/// <returns></returns>
public bool isChotHoaDon(int hoadonID)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
Hoadonnuoc hoaDon = _db.Hoadonnuocs.Find(hoadonID);
if (hoaDon != null)
{
bool?ttChot = hoaDon.Trangthaichot;
if (ttChot == null || ttChot == false)
{
return(false);
}
}
return(true);
}
/// <summary>
/// Lấy tổ của người dùng thuộc phòng ban ID và quận huyện ID
/// </summary>
/// <param name="nguoiDungID"></param>
/// <returns></returns>
public List <HoaDonNuocHaDong.Models.ToQuanHuyen.ToQuanHuyen> getPhongBanCuaNguoiDung(int phongbanID, int quanHuyenID, int option)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
var toQH = (from i in _db.Nguoidungs
join r in _db.Nhanviens on i.NhanvienID equals r.NhanvienID
join s in _db.ToQuanHuyens on r.ToQuanHuyenID equals s.ToQuanHuyenID
where s.PhongbanID == phongbanID && s.ToQuanHuyenID == quanHuyenID
select new HoaDonNuocHaDong.Models.ToQuanHuyen.ToQuanHuyen
{
ToQuanHuyenID = s.ToQuanHuyenID,
Ten = s.Ma
});
return(toQH.ToList());
}
public List <HoaDonNuocHaDong.Models.TuyenKhachHang.TuyenKhachHang> loadTuyenChuaCoNhanVien()
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
var tuyens = (from i in _db.Tuyenkhachhangs
where i.IsDelete == false
select new HoaDonNuocHaDong.Models.TuyenKhachHang.TuyenKhachHang
{
MaTuyenKH = i.Matuyen,
TenTuyen = i.Ten,
TuyenKHID = i.TuyenKHID.ToString(),
}
).ToList();
return(tuyens);
}
/// <summary>
/// xem danh sách kiểm định
/// </summary>
/// <param name="month">Tháng kiểm định</param>
/// <param name="year">Năm kiểm định </param>
/// <returns></returns>
public List <HoaDonNuocHaDong.Models.KhachHang.KiemDinhModel> getDanhSachKiemDinh(int?month, int?year, int?tuyenID)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
if (tuyenID == 0)
{
var dsKiemDinh = (from i in _db.Kiemdinhs
join t in _db.Hoadonnuocs on i.HoaDonId equals t.HoadonnuocID
join s in _db.Chitiethoadonnuocs on i.HoaDonId equals s.HoadonnuocID
join r in _db.Khachhangs on i.KhachhangID equals r.KhachhangID
join tuyenKhachHang in _db.Tuyenkhachhangs on r.TuyenKHID equals tuyenKhachHang.TuyenKHID
where t.ThangHoaDon == month && t.NamHoaDon == year
select new HoaDonNuocHaDong.Models.KhachHang.KiemDinhModel
{
KhachHangID = r.KhachhangID,
MaKhachHang = r.MaKhachHang,
TenKhachHang = r.Ten,
NgayKiemDinh = i.Ngaykiemdinh.Value,
ChiSoCu = s.Chisocu.Value,
ChiSoLucKiemDinh = i.Chisoluckiemdinh.Value,
KiemDinhID = i.KiemdinhID,
GhiChu = i.Ghichu,
ChiSoSauKiemDinh = i.Chisosaukiemdinh.Value,
MaTuyen = tuyenKhachHang.Matuyen
});
return(dsKiemDinh.ToList());
}
var dsKiemDinhTuyen = (from i in _db.Kiemdinhs
join t in _db.Hoadonnuocs on i.HoaDonId equals t.HoadonnuocID
join s in _db.Chitiethoadonnuocs on i.HoaDonId equals s.HoadonnuocID
join r in _db.Khachhangs on i.KhachhangID equals r.KhachhangID
where t.ThangHoaDon == month && t.NamHoaDon == year && r.TuyenKHID == tuyenID
select new HoaDonNuocHaDong.Models.KhachHang.KiemDinhModel
{
KhachHangID = r.KhachhangID,
MaKhachHang = r.MaKhachHang,
TenKhachHang = r.Ten,
NgayKiemDinh = i.Ngaykiemdinh.Value,
ChiSoLucKiemDinh = i.Chisoluckiemdinh.Value,
KiemDinhID = i.KiemdinhID,
GhiChu = i.Ghichu,
ChiSoSauKiemDinh = i.Chisosaukiemdinh.Value
});
return(dsKiemDinhTuyen.ToList());
}
public String getPhongBan(int?nhanVienID)
{
HoaDonHaDongEntities db1 = new HoaDonHaDongEntities();
if (nhanVienID != null)
{
var nhanVienObject = db1.Nhanviens.FirstOrDefault(p => p.NhanvienID == nhanVienID);
if (nhanVienObject != null)
{
var phongBanObject = db1.Phongbans.FirstOrDefault(p => p.PhongbanID == nhanVienObject.PhongbanID);
if (phongBanObject != null)
{
return(phongBanObject.Ten);
}
return("");
}
}
return("");
}
public List <HoaDonNuocHaDong.Models.SoLieuTieuThu.HoaDonNuoc> getHoaDonThang(int month, int year, int tuyenKHID)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
var hoaDonNuocs = (from i in _db.Hoadonnuocs
join kH in _db.Khachhangs on i.KhachhangID equals kH.KhachhangID
join cT in _db.Chitiethoadonnuocs on i.HoadonnuocID equals cT.HoadonnuocID
where i.ThangHoaDon == month && i.NamHoaDon == year && kH.TuyenKHID == tuyenKHID
select new HoaDonNuocHaDong.Models.SoLieuTieuThu.HoaDonNuoc
{
NgayKetThucSuDung = i.Ngayketthucsudung,
KhachHangID = i.KhachhangID.Value,
ChiSoMoi = cT.Chisomoi,
HoaDonNuocID = i.HoadonnuocID,
SanLuong = i.Tongsotieuthu,
NgayBatDauSuDung = i.Ngaybatdausudung,
Thang = month,
Nam = year,
});
return(hoaDonNuocs.ToList());
}
public static String[] sortLichSuHoaDonByTTDoc(String[] hoadons)
{
HoaDonHaDongEntities db = new HoaDonHaDongEntities();
List <Lichsuhoadon> lichSuHoaDons = new List <Lichsuhoadon>();
String[] lichSuHoaDonsAsArray = new String[hoadons.Length];
foreach (var hoadon in hoadons)
{
int hoaDonId = Convert.ToInt32(hoadon);
Lichsuhoadon lichSuHoaDon = db.Lichsuhoadons.FirstOrDefault(p => p.HoaDonID == hoaDonId);
lichSuHoaDons.Add(lichSuHoaDon);
}
List <Lichsuhoadon> sortedLichSuHoaDonsByTTDoc = lichSuHoaDons.OrderBy(p => p.TTDoc).ToList();
int i = 0;
foreach (var sortedLichSuHoaDonByTTDoc in sortedLichSuHoaDonsByTTDoc)
{
lichSuHoaDonsAsArray[i] = sortedLichSuHoaDonByTTDoc.HoaDonID.ToString();
i++;
}
return(lichSuHoaDonsAsArray);
}
public ActionResult Edit([Bind(Include = "Matuyen,TuyenKHID,ToID,CumdancuID,Ten,Diachi,IsDelete")] Tuyenkhachhang tuyenkhachhang)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
Tuyenkhachhang tuyen = _db.Tuyenkhachhangs.Find(tuyenkhachhang.TuyenKHID);
if (ModelState.IsValid)
{
Tuyenkhachhang existingTuyen = db.Tuyenkhachhangs.AsNoTracking().FirstOrDefault(p => p.Matuyen == tuyenkhachhang.Matuyen && p.IsDelete == false);
if (existingTuyen == null || tuyen.Matuyen == tuyenkhachhang.Matuyen)
{
db.Tuyenkhachhangs.AsNoTracking();
db.Tuyenkhachhangs.Attach(tuyenkhachhang);
db.Entry(tuyenkhachhang).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
else
{
ViewBag.hasTuyen = "Mã tuyến đã tồn tại trong cơ sở dữ liệu";
}
}
return(View(tuyenkhachhang));
}
public Khachhang getKH(int KHID)
{
HoaDonHaDongEntities db = new HoaDonHaDongEntities();
return(db.Khachhangs.Find(KHID));
}
/// <summary>
/// Lấy số tiền theo áp giá
/// </summary>
/// <param name="ApGiaID"></param>
/// <returns></returns>
public double?getSoTienTheoApGia(String tenApGia)
{
HoaDonHaDongEntities thisDB = new HoaDonHaDongEntities();
switch (tenApGia)
{
case "SH1":
Apgia _apGiaSH1 = thisDB.Apgias.FirstOrDefault(p => p.Ten == "SH1");
if (_apGiaSH1 != null)
{
return(_apGiaSH1.Gia);
}
break;
case "SH2":
Apgia _apGiaSH2 = thisDB.Apgias.FirstOrDefault(p => p.Ten == "SH2");
if (_apGiaSH2 != null)
{
return(_apGiaSH2.Gia);
}
break;
case "SH3":
Apgia _apGiaSH3 = thisDB.Apgias.FirstOrDefault(p => p.Ten == "SH3");
if (_apGiaSH3 != null)
{
return(_apGiaSH3.Gia);
}
break;
case "SH4":
Apgia _apGiaSH4 = thisDB.Apgias.FirstOrDefault(p => p.Ten == "SH4");
if (_apGiaSH4 != null)
{
return(_apGiaSH4.Gia);
}
break;
//khối kinh doanh
case "HC":
Apgia HC = thisDB.Apgias.FirstOrDefault(p => p.Ten == "HC");
if (HC != null)
{
return(HC.Gia);
}
break;
case "CC":
Apgia CC = thisDB.Apgias.FirstOrDefault(p => p.Ten == "CC");
if (CC != null)
{
return(CC.Gia);
}
break;
case "SX-XD":
Apgia SXXD = thisDB.Apgias.FirstOrDefault(p => p.Ten == "SX-XD");
if (SXXD != null)
{
return(SXXD.Gia);
}
break;
case "KDDV":
Apgia KDDV = thisDB.Apgias.FirstOrDefault(p => p.Ten == "KDDV");
if (KDDV != null)
{
return(KDDV.Gia);
}
break;
}
return(0);
}
/// <summary>
/// Lưu chỉ số tổng hợp cho khách hàng
/// </summary>
/// <param name="KhachHangID"></param>
/// <param name="isPhanTram"></param>
/// <param name="SH"></param>
/// <param name="KD"></param>
/// <param name="SX"></param>
public void saveGiaTongHop(int KhachHangID, byte isPhanTram, double SH, double KD, double HC, double CC, double SX, int month, short year)
{
HoaDonHaDongEntities _db = new HoaDonHaDongEntities();
_db.Apgiatonghops.RemoveRange(_db.Apgiatonghops.Where(p => p.KhachhangID == KhachHangID).ToList());
if (KD != -1)
{
Apgiatonghop apTongHopKD = new Apgiatonghop();
apTongHopKD.KhachhangID = KhachHangID;
apTongHopKD.IsDelete = false;
apTongHopKD.CachTinh = isPhanTram;
apTongHopKD.IDLoaiApGia = KhachHang.KINHDOANHDICHVU; //KD
apTongHopKD.SanLuong = KD;
apTongHopKD.NamTongHop = year;
apTongHopKD.ThangTongHop = month;
_db.Apgiatonghops.Add(apTongHopKD);
_db.SaveChanges();
}
if (HC != -1)
{
Apgiatonghop apTongHopHC = new Apgiatonghop();
apTongHopHC.KhachhangID = KhachHangID;
apTongHopHC.IsDelete = false;
apTongHopHC.CachTinh = isPhanTram;
apTongHopHC.IDLoaiApGia = KhachHang.COQUANHANHCHINH; //HC
apTongHopHC.SanLuong = HC;
apTongHopHC.NamTongHop = year;
apTongHopHC.ThangTongHop = month;
_db.Apgiatonghops.Add(apTongHopHC);
_db.SaveChanges();
}
if (CC != -1)
{
Apgiatonghop apTongHopCC = new Apgiatonghop();
apTongHopCC.KhachhangID = KhachHangID;
apTongHopCC.IsDelete = false;
apTongHopCC.CachTinh = isPhanTram;
apTongHopCC.IDLoaiApGia = KhachHang.DONVISUNGHIEP; //CC
apTongHopCC.SanLuong = CC;
apTongHopCC.NamTongHop = year;
apTongHopCC.ThangTongHop = month;
_db.Apgiatonghops.Add(apTongHopCC);
_db.SaveChanges();
}
if (SX != -1)
{
Apgiatonghop apTongHopSX = new Apgiatonghop();
apTongHopSX.KhachhangID = KhachHangID;
apTongHopSX.IsDelete = false;
apTongHopSX.CachTinh = isPhanTram;
apTongHopSX.IDLoaiApGia = KhachHang.SANXUAT; //SX
apTongHopSX.SanLuong = SX;
apTongHopSX.NamTongHop = year;
apTongHopSX.ThangTongHop = month;
_db.Apgiatonghops.Add(apTongHopSX);
_db.SaveChanges();
}
if (SH != -1)
{
Apgiatonghop apTongHop = new Apgiatonghop();
apTongHop.KhachhangID = KhachHangID;
apTongHop.IsDelete = false;
apTongHop.CachTinh = isPhanTram;
apTongHop.IDLoaiApGia = KhachHang.SINHHOAT; //SH
apTongHop.SanLuong = SH;
apTongHop.NamTongHop = year;
apTongHop.ThangTongHop = month;
_db.Apgiatonghops.Add(apTongHop);
_db.SaveChanges();
}
}
/// <summary>
/// Lưu thông tin giá đặc biệt
/// </summary>
/// <param name="KhachHangID"></param>
/// <param name="isPhanTram"></param>
/// <param name="SH"></param>
/// <param name="KD"></param>
/// <param name="HC"></param>
/// <param name="CC"></param>
/// <param name="SX"></param>
/// <param name="month"></param>
/// <param name="year"></param>
public void saveGiaDacBiet(int KhachHangID, double SH, double KD, double HC, double CC, double SX, double SH1, double SH2, double SH3, double SH4, int month, int year)
{
HoaDonHaDongEntities db = new HoaDonHaDongEntities();
//xóa record ứng với áp giá tổng hợp của khách hàng đó
db.Apgiatonghops.RemoveRange(db.Apgiatonghops.Where(p => p.KhachhangID == KhachHangID && p.ThangTongHop == month && p.NamTongHop == year));
if (KD != -1)
{
Apgiatonghop apTongHopKD = new Apgiatonghop();
apTongHopKD.KhachhangID = KhachHangID;
apTongHopKD.IsDelete = false;
apTongHopKD.IDLoaiApGia = KhachHang.KINHDOANHDICHVU; //KD
apTongHopKD.SanLuong = KD;
apTongHopKD.NamTongHop = year;
apTongHopKD.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHopKD);
}
if (HC != -1)
{
Apgiatonghop apTongHopHC = new Apgiatonghop();
apTongHopHC.KhachhangID = KhachHangID;
apTongHopHC.IsDelete = false;
apTongHopHC.IDLoaiApGia = KhachHang.COQUANHANHCHINH; //HC
apTongHopHC.SanLuong = HC;
apTongHopHC.NamTongHop = year;
apTongHopHC.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHopHC);
}
if (CC != -1)
{
Apgiatonghop apTongHopCC = new Apgiatonghop();
apTongHopCC.KhachhangID = KhachHangID;
apTongHopCC.IsDelete = false;
apTongHopCC.IDLoaiApGia = KhachHang.DONVISUNGHIEP; //CC
apTongHopCC.SanLuong = CC;
apTongHopCC.NamTongHop = year;
apTongHopCC.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHopCC);
}
if (SX != -1)
{
Apgiatonghop apTongHopSX = new Apgiatonghop();
apTongHopSX.KhachhangID = KhachHangID;
apTongHopSX.IsDelete = false;
apTongHopSX.IDLoaiApGia = KhachHang.SANXUAT; //SX
apTongHopSX.SanLuong = SX;
apTongHopSX.NamTongHop = year;
apTongHopSX.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHopSX);
}
if (SH != -1)
{
Apgiatonghop apTongHop = new Apgiatonghop();
apTongHop.KhachhangID = KhachHangID;
apTongHop.IsDelete = false;
apTongHop.IDLoaiApGia = KhachHang.SINHHOAT; //SH
apTongHop.SanLuong = SH;
apTongHop.NamTongHop = year;
apTongHop.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHop);
}
if (SH1 != -1)
{
Apgiatonghop apTongHop = new Apgiatonghop();
apTongHop.KhachhangID = KhachHangID;
apTongHop.IsDelete = false;
apTongHop.IDLoaiApGia = KhachHang.SH1; //SH
apTongHop.SanLuong = SH1;
apTongHop.NamTongHop = year;
apTongHop.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHop);
}
if (SH2 != -1)
{
Apgiatonghop apTongHop = new Apgiatonghop();
apTongHop.KhachhangID = KhachHangID;
apTongHop.IsDelete = false;
apTongHop.IDLoaiApGia = KhachHang.SH2; //SH
apTongHop.SanLuong = SH2;
apTongHop.NamTongHop = year;
apTongHop.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHop);
}
if (SH3 != -1)
{
Apgiatonghop apTongHop = new Apgiatonghop();
apTongHop.KhachhangID = KhachHangID;
apTongHop.IsDelete = false;
apTongHop.IDLoaiApGia = KhachHang.SH3; //SH
apTongHop.SanLuong = SH3;
apTongHop.NamTongHop = year;
apTongHop.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHop);
}
if (SH4 != -1)
{
Apgiatonghop apTongHop = new Apgiatonghop();
apTongHop.KhachhangID = KhachHangID;
apTongHop.IsDelete = false;
apTongHop.IDLoaiApGia = KhachHang.SH4; //SH
apTongHop.SanLuong = SH4;
apTongHop.NamTongHop = year;
apTongHop.ThangTongHop = month;
db.Apgiatonghops.Add(apTongHop);
}
db.SaveChanges();
}
