public static bool ChinhSuaTour(ThaoTacTourViewModel tourViewModel)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
TOUR tour = entity.TOURs.Where(t => t.MaTour == tourViewModel.Tour.MaTour).FirstOrDefault();
tour.MaTour = tourViewModel.Tour.MaTour;
tour.TenTour = tourViewModel.Tour.TenTour;
//tour.AnhDaiDien = tourViewModel.AnhDiaDiem;
tour.GiaNguoiLon = tourViewModel.Tour.GiaNguoiLon;
tour.GiaTreEm = tourViewModel.Tour.GiaTreEm;
tour.MaHanhTrinh = entity.HANHTRINHs.FirstOrDefault(t => (t.NoiDen == tourViewModel.Tour.MaDiaDiemDen) &&
t.NoiDi == tourViewModel.Tour.MaDiaDiemDi).MaHanhTrinh;
tour.MaHDV = tourViewModel.Tour.MaHDV;
tour.MaKS = tourViewModel.KhachSan;
tour.MaLoaiTour = tourViewModel.Tour.LoaiTour;
tour.NgayKetThuc = tourViewModel.Tour.NgayKT;
tour.NgayKhoiHanh = tourViewModel.Tour.NgayKH;
entity.SaveChanges();
return(true);
}
catch (Exception)
{
entity.Dispose();
return(false);
}
}
public static List <TourChiTietViewModel> TimTour(TimKiemTourViewModel model)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
var res = (from t in entity.TOURs
//join ks in entity.KHACHSANs
//on t.MaKS equals ks.MaKS
join ht in entity.HANHTRINHs
on t.MaHanhTrinh equals ht.MaHanhTrinh
join dd1 in entity.DIADIEMs
on ht.NoiDen equals dd1.MaDiaDiem
join dd2 in entity.DIADIEMs
on ht.NoiDi equals dd2.MaDiaDiem
where t.NgayKhoiHanh.Value.Year == model.NgayDi.Year &&
t.NgayKhoiHanh.Value.Month == model.NgayDi.Month &&
t.NgayKhoiHanh.Value.Day == model.NgayDi.Day
select new TourChiTietViewModel()
{
Tour = new TourViewModel()
{
MaTour = t.MaTour,
NgayKH = t.NgayKhoiHanh,
NgayKT = t.NgayKetThuc,
//TenKhachSan = ks.TenKS,
DiemDen = dd1.TenDiaDiem,
DiemDi = dd2.TenDiaDiem,
GiaNguoiLon = t.GiaNguoiLon,
GiaTreEm = t.GiaTreEm
},
AnhDiaDiem = t.AnhDaiDien
});
return(res.ToList());
}
public static List <ThongKeTheoTourViewModel> LayDuLieuThongKe()
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
var result = (from ct in entity.ChiTietHopDongs
join t in entity.TOURs
on ct.MaTour equals t.MaTour
join ht in entity.HANHTRINHs
on t.MaHanhTrinh equals ht.MaHanhTrinh
join dd in entity.DIADIEMs
on ht.NoiDen equals dd.MaDiaDiem
group new { dd, ct } by new { dd.TenDiaDiem, dd.MaDiaDiem } into grp
select new ThongKeTheoTourViewModel()
{
TienDaBan = grp.Sum(t => t.ct.ThanhTien),
MaDiemDen = grp.Key.MaDiaDiem,
TenDiemDen = grp.Key.TenDiaDiem
});
return(result.ToList());
}
catch (Exception)
{
entity.Dispose();
}
return(null);
}
public static bool ThemTour(ThaoTacTourViewModel tourDTO)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
TOUR tour = new TOUR()
{
MaTour = new Random().Next(0, 10000).ToString(),
//AnhDaiDien = tourDTO.AnhDiaDiem,
GiaNguoiLon = tourDTO.Tour.GiaNguoiLon,
GiaTreEm = tourDTO.Tour.GiaTreEm,
MaHanhTrinh = entity.HANHTRINHs.FirstOrDefault(t => (t.NoiDi == tourDTO.Tour.MaDiaDiemDi &&
t.NoiDen == tourDTO.Tour.MaDiaDiemDen)).MaHanhTrinh,
MaHDV = tourDTO.Tour.MaHDV,
MaKS = tourDTO.KhachSan,
MaLoaiTour = tourDTO.Tour.LoaiTour,
NgayKetThuc = tourDTO.Tour.NgayKT,
NgayKhoiHanh = tourDTO.Tour.NgayKH,
};
entity.TOURs.Add(tour);
entity.SaveChanges();
return(true);
}
catch (Exception)
{
entity.Dispose();
}
return(false);
}
public static SelectList LayDsKhachSan(string maKS)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
//var result = entity.KHACHSANs.Select(t => new SelectListItem() { Value = t.MaKS, Text = t.TenKS });
var result = new SelectList(entity.KHACHSANs, "MaKS", "TenKS", maKS);
return(result);
}
public static SelectList LoadDiaDiem(string selectedDiaDiem = null)
{
QlTourDuLichEntities entities = new QlTourDuLichEntities();
if (selectedDiaDiem == null)
{
return(new SelectList(entities.DIADIEMs, "MaDiaDiem", "TenDiaDiem", entities.DIADIEMs.FirstOrDefault().MaDiaDiem));
}
return(new SelectList(entities.DIADIEMs, "MaDiaDiem", "TenDiaDiem", selectedDiaDiem));
}
public static TourChiTietViewModel TimTour(string maTour)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
var res = (from t in entity.TOURs
where t.MaTour == maTour
join ks in entity.KHACHSANs
on t.MaKS equals ks.MaKS
join ht in entity.HANHTRINHs
on t.MaHanhTrinh equals ht.MaHanhTrinh
join dd1 in entity.DIADIEMs
on ht.NoiDen equals dd1.MaDiaDiem
join dd2 in entity.DIADIEMs
on ht.NoiDi equals dd2.MaDiaDiem
join hdv in entity.HUONGDANVIENs
on t.MaHDV equals hdv.MaHDV
select new TourChiTietViewModel()
{
Tour = new TourViewModel()
{
MaTour = t.MaTour,
LoaiTour = t.MaLoaiTour,
NgayKH = t.NgayKhoiHanh,
NgayKT = t.NgayKetThuc,
TenKhachSan = ks.TenKS,
DiemDen = dd1.TenDiaDiem,
DiemDi = dd2.TenDiaDiem,
GiaNguoiLon = t.GiaNguoiLon,
GiaTreEm = t.GiaTreEm,
MaDiaDiemDen = ht.NoiDen,
MaDiaDiemDi = ht.NoiDi,
TenTour = t.TenTour,
MaHDV = hdv.MaHDV,
TenHDV = hdv.TenHDV
},
AnhDiaDiem = t.AnhDaiDien,
GiaKhachSan = ks.GiaTien,
TGDen = t.NgayKetThuc,
TGDi = t.NgayKetThuc,
MaKS = ks.MaKS,
});
return(res.FirstOrDefault());
}
catch (Exception)
{
entity.Dispose();
}
return(null);
}
public static List <TourChiTietViewModel> LocNoiDung(decimal giaBD, decimal giaKT, int soLuongChon)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
var res = (from t in entity.TOURs
where t.GiaNguoiLon.Value >= giaBD && t.GiaNguoiLon.Value <= giaKT
join ks in entity.KHACHSANs
on t.MaKS equals ks.MaKS
join ht in entity.HANHTRINHs
on t.MaHanhTrinh equals ht.MaHanhTrinh
join dd1 in entity.DIADIEMs
on ht.NoiDen equals dd1.MaDiaDiem
join dd2 in entity.DIADIEMs
on ht.NoiDi equals dd2.MaDiaDiem
join hdv in entity.HUONGDANVIENs
on t.MaHDV equals hdv.MaHDV
select new TourChiTietViewModel()
{
Tour = new TourViewModel()
{
MaTour = t.MaTour,
LoaiTour = t.MaLoaiTour,
NgayKH = t.NgayKhoiHanh,
NgayKT = t.NgayKetThuc,
TenKhachSan = ks.TenKS,
DiemDen = dd1.TenDiaDiem,
DiemDi = dd2.TenDiaDiem,
GiaNguoiLon = t.GiaNguoiLon,
GiaTreEm = t.GiaTreEm,
TenTour = t.TenTour,
TenHDV = hdv.TenHDV
},
AnhDiaDiem = t.AnhDaiDien,
GiaKhachSan = ks.GiaTien,
TGDen = t.NgayKetThuc,
TGDi = t.NgayKetThuc,
MaKS = ks.MaKS,
});
return(res.Take(soLuongChon).ToList());
}
catch (Exception)
{
entity.Dispose();
}
return(null);
}
public static bool XoaTour(string MaTour)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
entity.TOURs.Remove(entity.TOURs.Where(t => t.MaTour == MaTour).FirstOrDefault());
entity.SaveChanges();
return(true);
}
catch (Exception)
{
entity.Dispose();
}
return(false);
}
public static bool KiemTraDangNhap(DangNhapViewModel model)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
var result = entity.NGUOIDUNGs.FirstOrDefault(t => (t.TenDangNhap == model.Username && t.MatKhau == model.Password));
//if (result != null)
//{
// HttpContext.Current.Session[Constants.Constants.LOGIN] = model.Username;
// //if (model.GhiNhoDangNhap)
// //{
// //}
// return true;
//}
return(result != null);
}
public static KhachHangViewModel TimKhachHangTheoMa(string maKH)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
var result = entity.KHACHHANGs.FirstOrDefault(t => t.MaKH == maKH);
if (result == null)
{
return(null);
}
return(new KhachHangViewModel()
{
DiaChi = result.DCKH,
MaKH = result.MaKH,
Email = result.Email,
SoDT = result.SDTKH,
TenKH = result.TenKH
});
}
public static List <string> TimHD(string idTour)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
var result = (from ct in entity.ChiTietHopDongs
where ct.MaTour == idTour
group new { ct } by new { ct.MaHopDong } into grp
select grp.Key.MaHopDong
);
return(result.ToList());
}
catch (Exception)
{
entity.Dispose();
return(null);
}
}
public static List <TourDaDatViewModel> LayDSTourDaDat(string idHD)
{
QlTourDuLichEntities entities = new QlTourDuLichEntities();
List <TourDaDatViewModel> lst = new List <TourDaDatViewModel>();
var result = entities.InHopDong(idHD).ToList();
if (result.Count > 0)
{
foreach (var item in result)
{
lst.Add(new TourDaDatViewModel()
{
matour = item.MaTour,
soluong = item.SoLuongDat
});
}
}
return(lst);
}
public static List <TourDaDatViewModel> LayDSTourGioHang(List <TourDaDatViewModel> gioHang)
{
QlTourDuLichEntities entities = new QlTourDuLichEntities();
var foods = entities.TOURs.Select(t => t.MaTour).ToList().OrderBy(t => t);
List <TourDaDatViewModel> res = new List <TourDaDatViewModel>();
TourDaDatViewModel tour;
if (gioHang == null)
{
foreach (var item in foods)
{
tour = new TourDaDatViewModel()
{
matour = item, soluong = 0
};
res.Add(tour);
}
}
else
{
foreach (var item in foods)
{
var tourChon = gioHang.FirstOrDefault(t => t.matour == item);
if (tourChon != null)
{
tour = new TourDaDatViewModel()
{
matour = item, soluong = tourChon.soluong
};
}
else
{
tour = new TourDaDatViewModel()
{
matour = item, soluong = 0
};
}
res.Add(tour);
}
}
return(res);
}
public static KhachHangViewModel DangNhap(KhachHangDangNhapViewModel model)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
var result = entity.KHACHHANGs.FirstOrDefault(t => (t.Email == model.TenDangNhap.Trim() || t.SDTKH == model.TenDangNhap.Trim()) &&
t.Password == model.MatKhau.Trim());
if (result == null)
{
return(null);
}
return(new KhachHangViewModel()
{
MaKH = result.MaKH,
TenKH = result.TenKH,
SoDT = result.SDTKH,
DiaChi = result.DCKH,
Email = result.Email,
GioiTinh = result.GioiTinh
});
}
public TourDaDatViewModel(string masp)
{
//-----------------------------------------------------
TOUR tour = ql.TOURs.Single(t => t.MaTour == masp);
matour = tour.MaTour;
tentour = tour.TenTour;
hinhanh = tour.AnhDaiDien;
giatreem = tour.GiaTreEm;
KHACHSAN khachsan = ql.KHACHSANs.Single(t => t.MaKS == tour.MaKS);
dongia = tour.GiaNguoiLon + khachsan.GiaTien;
slnguoilon = 1;
sltreem = 0;
soluong = 1;
//----------------------------------------------
QlTourDuLichEntities entity = new QlTourDuLichEntities();
var res = (from t in entity.TOURs
where t.MaTour == masp
join ks in entity.KHACHSANs
on t.MaKS equals ks.MaKS
join ht in entity.HANHTRINHs
on t.MaHanhTrinh equals ht.MaHanhTrinh
join dd1 in entity.DIADIEMs
on ht.NoiDen equals dd1.MaDiaDiem
join dd2 in entity.DIADIEMs
on ht.NoiDi equals dd2.MaDiaDiem
select new
{
NoiBD = dd1.TenDiaDiem,
NoiKT = dd2.TenDiaDiem,
NgayDi = t.NgayKhoiHanh.Value,
NgayDen = t.NgayKetThuc.Value
});
var kq = res.FirstOrDefault();
NoiDi = kq.NoiBD;
NoiDen = kq.NoiKT;
Ngaykh = kq.NgayDi;
Ngaykt = kq.NgayDen;
}
public static List <string> LayDSKhachHangDatTour(string maTour)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
var result = (from ct in entity.ChiTietHopDongs
where ct.MaTour == maTour
join hd in entity.HOPDONGs
on ct.MaHopDong equals hd.MaHD
group new { hd } by new { hd.MaKH } into grp
select grp.Key.MaKH
);
return(result.ToList());
}
catch (Exception)
{
entity.Dispose();
return(null);
}
}
public static KhachHangViewModel TimKhachHang(string maHopDong)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
string maKH = entity.HOPDONGs.FirstOrDefault(t => t.MaHD == maHopDong).MaKH;
var result = entity.KHACHHANGs.FirstOrDefault(t => t.MaKH == maKH);
return(new KhachHangViewModel()
{
DiaChi = result.DCKH,
SoDT = result.SDTKH,
TenKH = result.TenKH
});
}
catch (Exception)
{
entity.Dispose();
}
return(null);
}
public static List <ThongKeTheoThoiGianViewModel> ThongKeTheoTG(int thang, int nam)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
var result = (from hd in entity.HOPDONGs
where hd.ThoiGianDat.Value.Year == nam && hd.ThoiGianDat.Value.Month == thang
join ct in entity.ChiTietHopDongs
on hd.MaHD equals ct.MaHopDong
join t in entity.TOURs
on ct.MaTour equals t.MaTour
join ht in entity.HANHTRINHs
on t.MaHanhTrinh equals ht.MaHanhTrinh
join dd in entity.DIADIEMs
on ht.NoiDen equals dd.MaDiaDiem
join ddi in entity.DIADIEMs
on ht.NoiDi equals ddi.MaDiaDiem
select new TourDaBanViewModel()
{
NoiDi = ddi.TenDiaDiem,
NoiDen = dd.TenDiaDiem,
ThanhTien = ct.ThanhTien
});
decimal?sum = result.Sum(t => t.ThanhTien);
var thongKe = result.GroupBy(t => new { t.NoiDi, t.NoiDen }).Select(x => new ThongKeTheoThoiGianViewModel()
{
TenTour = x.Key.NoiDi + " - " + x.Key.NoiDen,
ChuThich = "Tour " + x.Key.NoiDi + " - " + x.Key.NoiDen,
TienDaBan = Math.Round(((x.Sum(y => y.ThanhTien) / sum) * 100).Value, 2)
});
return(thongKe.ToList());
}
catch (Exception e)
{
entity.Dispose();
}
return(null);
}
public static bool SuaThongTinKhachHang(KhachHangViewModel model)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
var result = entity.KHACHHANGs.FirstOrDefault(t => t.MaKH == model.MaKH);
if (result != null)
{
result.TenKH = model.TenKH;
result.SDTKH = model.SoDT;
result.DCKH = model.DiaChi;
try
{
entity.SaveChanges();
return(true);
}
catch (Exception)
{
entity.Dispose();
}
}
return(false);
}
public static SelectList TimDiemDen2(int?MaDiemDi)
{
QlTourDuLichEntities entities = new QlTourDuLichEntities();
try
{
var result = (from ht in entities.HANHTRINHs
join dd in entities.DIADIEMs
on ht.NoiDen equals dd.MaDiaDiem
where ht.NoiDi == MaDiemDi
select new
{
dd.TenDiaDiem, dd.MaDiaDiem
});
return(new SelectList(result, "MaDiaDiem", "TenDiaDiem", result.FirstOrDefault().MaDiaDiem));
}
catch (Exception)
{
entities.Dispose();
return(null);
}
}
public static List <ChiTietHopDongViewModel> TimHopDong(string maHopDong)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
try
{
var result = (from hd in entity.HOPDONGs
where hd.MaHD == maHopDong
join ct in entity.ChiTietHopDongs
on hd.MaHD equals ct.MaHopDong
join t in entity.TOURs
on ct.MaTour equals t.MaTour
join ht in entity.HANHTRINHs
on t.MaHanhTrinh equals ht.MaHanhTrinh
join dden in entity.DIADIEMs
on ht.NoiDen equals dden.MaDiaDiem
join ddi in entity.DIADIEMs
on ht.NoiDi equals ddi.MaDiaDiem
//join kh in entity.KHACHHANGs
//on hd.MaKH equals kh.MaKH
select new ChiTietHopDongViewModel()
{
MaTour = t.MaTour,
DiemDi = ddi.TenDiaDiem,
DiemDen = dden.TenDiaDiem,
NgayDi = t.NgayKhoiHanh,
NgayDen = t.NgayKetThuc,
NguoiLon = ct.SLNguoiLon,
TreEm = ct.SLTreEm,
ThanhTien = ct.ThanhTien,
}).ToList();
return(result);
}
catch (Exception)
{
entity.Dispose();
}
return(null);
}
public static List <SelectListItem> TimDiemDen(int?MaDiemDi)
{
QlTourDuLichEntities entities = new QlTourDuLichEntities();
try
{
var result = (from ht in entities.HANHTRINHs
join dd in entities.DIADIEMs
on ht.NoiDen equals dd.MaDiaDiem
where ht.NoiDi == MaDiemDi
select new SelectListItem()
{
Value = SqlFunctions.StringConvert((double)dd.MaDiaDiem).Trim(),
Text = dd.TenDiaDiem
});
return(result.ToList());
}
catch (Exception)
{
entities.Dispose();
return(new List <SelectListItem>());
}
}
public static bool DangKyBangFacebook(KhachHangViewModel model)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
KHACHHANG khachhang = new KHACHHANG()
{
MaKH = model.MaKH,
Email = model.Email,
GioiTinh = model.GioiTinh,
SDTKH = model.SoDT,
TenKH = model.TenKH
};
try
{
entity.KHACHHANGs.Add(khachhang);
entity.SaveChanges();
return(true);
}
catch (Exception)
{
entity.Dispose();
}
return(false);
}
public static bool ThemHanhTrinh(int maNoiDi, int maNoiDen)
{
QlTourDuLichEntities entities = new QlTourDuLichEntities();
if (maNoiDi != maNoiDen && entities.HANHTRINHs.FirstOrDefault(t => (t.NoiDi == maNoiDi && t.NoiDen == maNoiDen)) == null)
{
try
{
entities.HANHTRINHs.Add(new HANHTRINH()
{
MaHanhTrinh = new Random().Next(0, 1000).ToString(),
NoiDi = maNoiDi,
NoiDen = maNoiDen
});
entities.SaveChanges();
return(true);
}
catch (Exception)
{
entities.Dispose();
}
}
return(false);
}
public static SelectList LoadHuongDanVien(object HDVChon = null)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
return(new SelectList(entity.HUONGDANVIENs, "MaHDV", "TenHDV", HDVChon));
}
public static bool KiemTraTaiKhoanFacebook(string idKH)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
return(entity.KHACHHANGs.FirstOrDefault(t => t.MaKH == idKH) != null);
}
public static SelectList LoadLoaiTour()
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
return(new SelectList(entity.LOAITOURs, "MaLoaiTour", "TenLoaiTour"));
}
public static SelectList LoadLoaiTour(string maLoaiDuocChon)
{
QlTourDuLichEntities entity = new QlTourDuLichEntities();
return(new SelectList(entity.LOAITOURs, "MaLoaiTour", "TenLoaiTour", maLoaiDuocChon));
}
