public List <HOADONDV> List(string search)
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <HOADONDV> query = from s in db.HOADONDVs where s.Deleted == 0 orderby s.NgayGioLap descending select s;
//Filter // neu de search=null thi kho search,
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => (x.ID != null &&
x.ID.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.TenKH != null &&
x.TenKH.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.ID_Phong != null &&
x.ID_Phong.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.TongTien != null &&
x.TongTien.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.NgayGioLap != null &&
x.NgayGioLap.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
);
}
return(query.ToList());
}
}
public bool Update(PHIEUCHI item)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.PHIEUCHIs
where s.ID == item.ID && s.Deleted == 0
select s;
var sv = x.FirstOrDefault();
if (sv != null)
{
sv.NgayLap = item.NgayLap;
sv.NoiDung = item.NoiDung;
sv.TongTien = item.TongTien;
sv.GhiChu = item.GhiChu;
db.SaveChanges();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <CT_HOADONDV> List(string search)
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <CT_HOADONDV> query = from s in db.CT_HOADONDV where s.Deleted == 0select s;
//Filter // neu de search=null thi kho search,
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => (x.ID_HoaDonDV != null &&
x.ID_HoaDonDV.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.ID_Hang != null &&
x.ID_Hang.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.SoLuong != null &&
x.SoLuong.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.DonGia != null &&
x.DonGia.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.ThanhTien != null &&
x.ThanhTien.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
);
}
return(query.ToList());
}
}
public DataTable GetListCT_BaoCao(int id_bc) //getlist cho radgrid 2
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var query = from s in db.CT_BCTONKHO join h in db.HANGs on s.ID_Hang equals h.ID where s.ID_BCTonKho == id_bc select new{
s.ID_Hang, s.SoLuongNhap, s.SuDung, s.TonCuoi, s.TonDau, h.Ten
};
var tb = new DataTable();
tb.Columns.Add("ID");
tb.Columns.Add("Ten");
tb.Columns.Add("tondau");
tb.Columns.Add("soluongnhap");
tb.Columns.Add("sudung");
tb.Columns.Add("toncuoi");
foreach (var item in query.ToList())
{
tb.Rows.Add(item.ID_Hang,
item.Ten,
item.TonDau,
item.SoLuongNhap,
item.SuDung,
item.TonCuoi
);
}
return(tb);
}
}
catch { return(null); }
}
public bool Insert(BCTONKHO item)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
db.BCTONKHOes.Add(item);
db.SaveChanges();
//lay id BC
int id = (from s in db.BCTONKHOes where s.Thang == item.Thang select s.ID).FirstOrDefault();
List <GetListBCTonKho_Result> _ctbc = GetList(item.Thang);
for (int i = 0; i < _ctbc.Count; i++)
{
CT_BCTONKHO ctbc = new CT_BCTONKHO();
ctbc.ID_BCTonKho = id;
ctbc.ID_Hang = _ctbc[i].ID;
ctbc.TonDau = _ctbc[i].tondau.GetValueOrDefault();
ctbc.SuDung = _ctbc[i].soluongban.GetValueOrDefault();
ctbc.SoLuongNhap = _ctbc[i].soluongnhap.GetValueOrDefault();
ctbc.TonCuoi = _ctbc[i].toncuoi.GetValueOrDefault();
db.CT_BCTONKHO.Add(ctbc);
db.SaveChanges();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <CT_HOADONDV> List_HD(int idhoadon, string search)
{
if (idhoadon < 1)
{
return(null);
}
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <CT_HOADONDV> query = from s in db.CT_HOADONDV where s.ID_HoaDonDV == idhoadon && s.Deleted == 0 select s;
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => (x.ID_HoaDonDV != null &&
x.ID_HoaDonDV.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.ID_Hang != null &&
x.ID_Hang.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.SoLuong != null &&
x.SoLuong.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.DonGia != null &&
x.DonGia.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.ThanhTien != null &&
x.ThanhTien.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
);
}
return(query.ToList());
}
}
public List <LOAIPHONG> List(string search)
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <LOAIPHONG> query = from s in db.LOAIPHONGs where s.Deleted == 0 select s;
//Filter // neu de search=null thi kho search,
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => (x.ID != null &&
x.ID.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.Ten != null &&
x.Ten.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.GiaDem != null &&
x.GiaDem.Value.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.GiaNgay != null &&
x.GiaNgay.Value.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.SoLuong != null &&
x.SoLuong.Value.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
);
}
return(query.ToList());
}
}
public List <DATPHONG> List(string search)
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <DATPHONG> query = from s in db.DATPHONGs select s;
//Filter // neu de search=null thi kho search,
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => (x.ID != null &&
x.ID.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.NgayGioDat != null &&
x.NgayGioDat.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.SDT != null &&
x.SDT.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.Note != null &&
x.Note.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
);
}
return(query.ToList());
}
}
public List <PHIEUCHI> List(string search)
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <PHIEUCHI> query = from s in db.PHIEUCHIs where s.Deleted == 0 select s;
//Filter // neu de search=null thi kho search,
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => (x.ID != null &&
x.ID.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.NgayLap != null &&
x.NgayLap.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.NoiDung != null &&
x.NoiDung.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.TongTien != null &&
x.TongTien.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.GhiChu != null &&
x.GhiChu.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
);
}
return(query.ToList());
}
}
public List <HANG> List_Warning(string search) //chi lay nhung cai co slton > 0
{
using (var db = new QLPhongKaraokeEntities())
{
var tmpquery = (from s in db.THAMSOes where s.Name == "WarningNum" select s).FirstOrDefault();
var warningnum = tmpquery == null? 0 : int.Parse(tmpquery.Value);
IEnumerable <HANG> query = from s in db.HANGs where s.Deleted == 0 && s.SLTon <= warningnum select s;
//Filter // neu de search=null thi kho search,
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => (x.ID != null &&
x.ID.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.Ten != null &&
x.Ten.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.DonGiaBan != null &&
x.DonGiaBan.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.DonGiaNhap != null &&
x.DonGiaNhap.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
);
}
return(query.ToList());
}
}
public bool Update(HANG phong)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.HANGs
where (s.ID == phong.ID)
select s;
var sv = x.FirstOrDefault();
if (sv != null)
{
sv.SLTon = phong.SLTon;
sv.Ten = phong.Ten;
sv.Requested = phong.Requested;
sv.DonVi = phong.DonVi;
sv.DonGiaBan = phong.DonGiaBan;
sv.DonGiaNhap = phong.DonGiaNhap;
db.SaveChanges();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool Delete(int ID)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.HOADONDVs
where s.ID == ID && s.Deleted == 0
select s;
var sv = x.FirstOrDefault();
if (sv != null)
{
sv.Deleted = 1;
db.SaveChanges();
(new MMCT_HoaDonDV()).DeleteAllHoaDon(ID); // xoa hoa don thi xoa luon ct
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <HANG> List(string search) //chi lay nhung cai co slton > 0
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <HANG> query = from s in db.HANGs where s.Deleted == 0 select s;
//Filter // neu de search=null thi kho search,
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => (x.ID != null &&
x.ID.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.Ten != null &&
x.Ten.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.DonGiaBan != null &&
x.DonGiaBan.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.DonGiaNhap != null &&
x.DonGiaNhap.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
(x.SLTon > 0 &&
x.SLTon.ToString()
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
);
}
return(query.ToList());
}
}
public bool Update(HOADONNHAP phong)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.HOADONNHAPs
where s.ID == phong.ID
select s;
var sv = x.FirstOrDefault();
if (sv != null)
{
sv.NgayNhap = phong.NgayNhap;
sv.TongTien = phong.TongTien;
db.SaveChanges();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool Update(LOAIPHONG item)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.LOAIPHONGs
where s.ID == item.ID && s.Deleted == 0
select s;
var sv = x.FirstOrDefault();
if (sv != null)
{
sv.Ten = item.Ten;
sv.GiaDem = item.GiaDem;
sv.GiaNgay = item.GiaNgay;
sv.SoLuong = item.SoLuong;
db.SaveChanges();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool Update(CT_HDNHAP phong)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.CT_HDNHAP
where (s.IDHoaDon == phong.IDHoaDon && s.IDHang == phong.IDHang)
select s;
var sv = x.FirstOrDefault();
if (sv != null)
{
sv.SoLuong = phong.SoLuong;
sv.ThanhTien = phong.ThanhTien;
sv.DonGiaNhap = phong.DonGiaNhap;
db.SaveChanges();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool Update(DATPHONG item)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.DATPHONGs
where s.ID == item.ID
select s;
var sv = x.FirstOrDefault();
if (sv != null)
{
sv.NgayGioDat = item.NgayGioDat;
sv.SDT = item.SDT;
sv.Note = item.Note;
db.SaveChanges();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool Update(HANG item)
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.HANGs
where s.ID == item.ID
select s;
var sv = x.FirstOrDefault();
if (sv != null)
{
sv.Ten = item.Ten;
sv.DonGiaNhap = item.DonGiaNhap;
sv.DonGiaBan = item.DonGiaBan;
// sv.SLTon = GetSLTon();
sv.DonVi = item.DonVi;
sv.Requested = item.Requested;
db.SaveChanges();
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool Delete(int idhaodon, int idhang)
{
decimal thanhtiencu = 0;
try
{
using (var db = new QLPhongKaraokeEntities())
{
var x = from s in db.CT_HOADONDV
where s.ID_HoaDonDV == idhaodon && s.ID_Hang == idhang && s.Deleted == 0
select s;
var sv = x.FirstOrDefault();
var hang = (from s in db.HANGs where s.ID == idhang select s).FirstOrDefault();
if (sv != null)
{
thanhtiencu = sv.ThanhTien;
sv.Deleted = 1;
db.SaveChanges();
// cap nhat so luong ton
hang.SLTon += sv.SoLuong;
db.SaveChanges();
//cap nhat tong tien hoa don
var hd = new MMHOADONDV();
hd.UpdateTongTien(sv.ID_HoaDonDV, hd.GetOne(sv.ID_HoaDonDV).TongTien - thanhtiencu);
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <NHOMQUYEN> ListNhomQuyen()
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <NHOMQUYEN> query = from s in db.NHOMQUYENs select s;
return(query.ToList());
}
}
public List <PHONG> GetAllPhong()
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <PHONG> query = from s in db.PHONGs where s.Deleted == 0 select s;
return(query.ToList());
}
}
public List <GetListBCDoanhThu_Result> GetListHoaDon(DateTime dateFrom, DateTime dateTo)
{
try{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <GetListBCDoanhThu_Result> query = db.GetListBCDoanhThu(dateFrom, dateTo);
return(query.ToList());
}
}
catch { return(null); }
}
public bool checkMonth(DateTime date)
{
using (var db = new QLPhongKaraokeEntities())
{
var id = (from s in db.BCTONKHOes where s.Thang.Year == date.Year && s.Thang.Month == date.Month select s);
if (id.ToList().Count > 0) //ton tai
{
return(false);
}
}
return(true);
}
public List <GetListBCTonKho_Result> GetList(DateTime date) //get list luc add bc
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <GetListBCTonKho_Result> query = db.GetListBCTonKho(date);
return(query.ToList());
}
}
catch { return(null); }
}
public List <CT_HOADONDV> List_HD(int idhoadon)
{
if (idhoadon < 1)
{
return(null);
}
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <CT_HOADONDV> query = from s in db.CT_HOADONDV where s.ID_HoaDonDV == idhoadon && s.Deleted == 0 select s;
return(query.ToList());
}
}
public CT_HDNHAP Get1Item(int IDHoaDon, int IDHang)
{
if (IDHoaDon == 0 || IDHang == 0)
{
return(null);
}
using (var db = new QLPhongKaraokeEntities())
{
var query = (from k in db.CT_HDNHAP select k).Where(x => (x.IDHang == IDHang && x.IDHoaDon == IDHoaDon)).FirstOrDefault();
return(query);
}
}
public HOADONNHAP Get1Item(long id)
{
if (id == 0)
{
return(null);
}
using (var db = new QLPhongKaraokeEntities())
{
var query = (from k in db.HOADONNHAPs select k).Where(x => x.ID == id).FirstOrDefault();
return(query);
}
}
public List <BCTONKHO> GetListBaoCao() //getlist cho radgrid 1
{
try
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <BCTONKHO> query = from s in db.BCTONKHOes select s;
return(query.ToList());
}
}
catch { return(null); }
}
public List <HANG> List()
{
using (var db = new QLPhongKaraokeEntities())
{
IEnumerable <HANG> query = from s in db.HANGs where s.Deleted == 0 select s;
//Filter // neu de search=null thi kho search,
return(query.ToList());
}
}
public LOAIPHONG Get1LoaiPhong(long id)
{
if (id == 0)
{
return(null);
}
using (var db = new QLPhongKaraokeEntities())
{
var query = (from k in db.LOAIPHONGs select k).Where(x => x.ID == id).FirstOrDefault();
return(query);
}
}