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 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 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 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 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 <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 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 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 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);
}