public static DataTable XemHangHoa()
{
string query = "SELECT MAMON AS Ma,TENMON AS Ten FROM MONAN";
return(Dataprovider.ExcuteQuery(query));
}
public static List <GoiMonDataSource> XemChiTietHoaDon(string soHoaDon)
{
string query = "EXEC uspXemChiTietHoaDon @soHoaDon";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@soHoaDon", SqlDbType.VarChar)
{
Value = soHoaDon
}
};
List <GoiMonDataSource> list = null;
try
{
DataTable table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
list = table.AsEnumerable().ToList().ConvertAll(x =>
new GoiMonDataSource()
{
Ma = x[0].ToString(),
Ten = x[1].ToString(),
Soluong = x[2].ToString(),
Loai = x[3].ToString(),
Gia = x[4].ToString(),
Thanhtien = (int.Parse(x[2].ToString()) * int.Parse(x[4].ToString())).ToString()
}
);
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static bool KiemTraGoiMon(string ma, int soLuong, int soLuongCu)
{
string query = "EXEC uspKiemTraGoiMon @ma,@soLuong,@soLuongCu";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@ma", SqlDbType.VarChar)
{
IsNullable = false, Value = ma
},
new SqlParameter("@soLuong", SqlDbType.Int)
{
IsNullable = false, Value = soLuong
},
new SqlParameter("@soLuongCu", SqlDbType.Int)
{
IsNullable = false, Value = soLuongCu
},
};
int result = 0;
try
{
result = (int)Dataprovider.ExcuteScalar(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(result == 0 ? false : true);
}
public static bool ThemHoaDonMonAn(string soHoaDon, string ma, int soLuong)
{
string query = "EXEC uspThemHangHoaDon @soHoaDon,@ma,@soLuong";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@soHoaDon", SqlDbType.VarChar)
{
IsNullable = false, Value = soHoaDon
},
new SqlParameter("@ma", SqlDbType.VarChar)
{
IsNullable = false, Value = ma
},
new SqlParameter("@soLuong", SqlDbType.Int)
{
IsNullable = false, Value = soLuong
},
};
try
{
Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(true);
}
public static DataTable Xemnl()
{
string query = "SELECT B.MANL AS MaNguyenLieu ,B.TENNL as TenNguyenLieu,A.MAMON AS Ma FROM TPMONAN A INNER JOIN NGUYENLIEU B ON A.MANL=B.MANL";
return(Dataprovider.ExcuteQuery(query));
}
public static int DemSanPham(string nhaCungCap, int loai, string ten)
{
string query = "EXEC uspDemSanPham @nhaCungCap,@loai,@ten";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@nhaCungCap", SqlDbType.VarChar)
{
IsNullable = false, Value = nhaCungCap
},
new SqlParameter("@loai", SqlDbType.Int)
{
IsNullable = false, Value = loai
},
new SqlParameter("@ten", SqlDbType.NVarChar)
{
IsNullable = false, Value = ten
},
};
int count = 0;
try
{
count = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(count);
}
public static List <LoaiHangHoa> XemLoaiMon(int loai)
{
string query = "EXEC uspXemLoaiHangHoa @loai";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@loai", SqlDbType.Int)
{
IsNullable = false, Value = loai
}
};
List <LoaiHangHoa> list = null;
try
{
DataTable table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
list = table.AsEnumerable().ToList().ConvertAll(x =>
new LoaiHangHoa()
{
Ma = x[0].ToString(),
Ten = x[1].ToString()
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static bool NhanPhongDatTruoc(string soHoaDon)
{
string query = "EXEC uspNhanPhong @soHoaDon,@NgayNhan";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@soHoaDon", SqlDbType.VarChar)
{
Value = soHoaDon
},
new SqlParameter("@soHoaDon", SqlDbType.DateTime)
{
Value = DateTime.Now
},
};
try
{
Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(true);
}
public static bool CapNhatLoaiKH(LoaiKhachHang loaiKhach)
{
int rowNum = 0;
int maLoaiKH = loaiKhach.MaLoaiKH;
string tenLoaiKH = loaiKhach.TenLoaiKH;
float mucKM = loaiKhach.MucKM;
string query = "EXEC usp_CapNhatLoaiKH @maLoaiKH,@tenLoaiKH,@mucKM";
List <SqlParameter> parameters = new List <SqlParameter>
{
new SqlParameter("@maLoaiKH", SqlDbType.Int)
{
Value = maLoaiKH
},
new SqlParameter("@tenLoaiKH", SqlDbType.VarChar)
{
Value = tenLoaiKH
},
new SqlParameter("@mucKM", SqlDbType.Float)
{
Value = mucKM
},
};
try
{
rowNum = Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(rowNum > 0 ? true : false);
}
public static bool ThemKhachHang(KhachHang khachHang)
{
int rowNum = 0;
string query = "EXEC usp_ThemKhachHang @tenKH,@loaiKH,@sdt";
string tenKH = khachHang.Ten;
int loaiKH = khachHang.LoaiKH;
string sdt = khachHang.SDT;
List <SqlParameter> parameters = new List <SqlParameter>
{
new SqlParameter("@tenKH", SqlDbType.NVarChar)
{
IsNullable = false, Value = tenKH
},
new SqlParameter("@loaiKH", SqlDbType.Int)
{
IsNullable = true, Value = loaiKH != 0?loaiKH:0
},
new SqlParameter("@sdt", SqlDbType.NVarChar)
{
IsNullable = true, Value = sdt ?? (Object)DBNull.Value
}
};
try
{
rowNum = Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (SqlException ex)
{
Utility.Log(ex);
}
return(rowNum > 0 ? true : false);
}
public static bool KiemTraPhieuDatTiec(string maPhong, DateTime ngayNhanPhong, DateTime ngayGioKetThuc)
{
string query = "EXEC uspKiemTraDatPhong @maPhong,@ngayNhanPhong,@ngayGioKetThuc";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@maPhong", SqlDbType.VarChar)
{
IsNullable = false, Value = maPhong
},
new SqlParameter("@ngayNhanPhong", SqlDbType.DateTime)
{
Value = ngayNhanPhong
},
new SqlParameter("@ngayGioKetThuc", SqlDbType.DateTime)
{
Value = ngayGioKetThuc
}
};
int count = -1;
try
{
count = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(count > 0 ? false : true);
}
public static int DemPhongQuanLy(string maPhong, int loaiPhong)
{
string query = "EXEC uspDemPhongQuanLy @maPhong,@loaiPhong";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@maPhong", SqlDbType.VarChar)
{
Value = maPhong
},
new SqlParameter("@loaiPhong", SqlDbType.Int)
{
Value = loaiPhong
},
};
int count = 0;
try
{
count = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(count);
}
public static List <HangHoaDataSource> XemChiTietPhieuNhapSanPham(string soPhieuDat)
{
string query = "EXEC uspXemChiTietPhieuNhapSanPham @soPhieuDat";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@soPhieuDat", SqlDbType.VarChar)
{
IsNullable = false, Value = soPhieuDat
},
};
List <HangHoaDataSource> list = null;
try
{
list = Dataprovider.ExcuteQuery(query, parameters.ToArray()).AsEnumerable().ToList().ConvertAll(x =>
new HangHoaDataSource()
{
Ma = x[1].ToString(),
Ten = x[2].ToString(),
Loai = x[3].ToString(),
DonViTinh = x[4].ToString(),
Gia = x[5].ToString(),
Soluong = x[6].ToString(),
Thanhtien = x[7].ToString(),
MaLoaiHangHoa = x[3].ToString(),
TenLoaiHangHoa = x[8].ToString(),
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static int DemHangHoa(int loai, int loaiChiTiet)
{
string query = "EXEC uspDemHangHoa @loai,@loaiChiTiet";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@loai", SqlDbType.Int)
{
IsNullable = false, Value = loai
},
new SqlParameter("@loaiChiTiet", SqlDbType.Int)
{
IsNullable = false, Value = loaiChiTiet
},
};
int count = 0;
try
{
count = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(count);
}
public static bool ChuyenPhong(string phongHienTai, string phongChuyen)
{
string query = "EXEC uspChuyenPhong @maPhongHienTai,@maPhongChuyen ";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@maPhongHienTai", SqlDbType.VarChar)
{
Value = phongHienTai
},
new SqlParameter("@maPhongChuyen", SqlDbType.VarChar)
{
Value = phongChuyen
},
};
try
{
Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(true);
}
public static List <ThietBiDataSource> XemChiTietSuDungThietBi(string maSuDung)
{
string query = "EXEC uspXemChiTietSuDungThietBi @maSuDung";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@maSuDung", SqlDbType.NVarChar)
{
IsNullable = false, Value = maSuDung
},
};
List <ThietBiDataSource> list = null;
try
{
list = Dataprovider.ExcuteQuery(query, parameters.ToArray()).AsEnumerable().ToList().ConvertAll(x =>
new ThietBiDataSource()
{
MaThietBi = x[0].ToString(),
TenThietBi = x[1].ToString(),
SoLuong = x[3].ToString(),
DonGia = x[2].ToString(),
TenNhaCungCap = x[4].ToString(),
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static bool KiemTraTonThietBi(string maThietBi, int soLuong, int soLuongCu)
{
string query = "EXEC uspKiemTraTon @maThietBi,@soLuong,@soLuongCu";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@maThietBi", SqlDbType.NVarChar)
{
IsNullable = false, Value = maThietBi
},
new SqlParameter("@soLuong", SqlDbType.Int)
{
IsNullable = false, Value = soLuong
},
new SqlParameter("@soLuongCu", SqlDbType.Int)
{
IsNullable = false, Value = soLuongCu
},
};
int num = 0;
try
{
num = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(num > 0? true : false);
}
public static int DemSuDungThietBi(string lyDo, string tenThietBi)
{
string query = "EXEC uspDemSuDungThietBi @lyDo,@tenThietBi";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@lyDo", SqlDbType.NVarChar)
{
IsNullable = false, Value = lyDo
},
new SqlParameter("@tenThietBi", SqlDbType.NVarChar)
{
IsNullable = false, Value = tenThietBi
},
};
int count = 0;
try
{
count = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(count);
}
public static int DemThietBi(string tenThietBi, string maNhaCungCap)
{
string query = "EXEC uspDemThietBi @tenThietBi,@maNhaCungCap";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tenThietBi", SqlDbType.NVarChar)
{
IsNullable = true, Value = tenThietBi
},
new SqlParameter("@maNhaCungCap", SqlDbType.VarChar)
{
IsNullable = true, Value = maNhaCungCap
}
};
int count = 0;
try
{
count = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(count);
}
public static int DemThietBiSuDung(string tenThietBi, string maNhaCungCap, List <string> exceptList)
{
string query = "EXEC uspDemThietBiExcepList @tenThietBi,@maNhaCungCap,@danhSachLoaiTru";
string danhSachLoaiTru = exceptList == null?"": String.Join("|", exceptList);
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tenThietBi", SqlDbType.NVarChar)
{
IsNullable = true, Value = tenThietBi
},
new SqlParameter("@maNhaCungCap", SqlDbType.VarChar)
{
IsNullable = true, Value = maNhaCungCap
},
new SqlParameter("@danhSachLoaiTru", SqlDbType.VarChar)
{
IsNullable = true, Value = danhSachLoaiTru
}
};
int count = 0;
try
{
count = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(count);
}
public static bool XoaKhuyenMai(int maKhuyenMai)
{
string query = "EXEC uspXoaKhuyenMai @maKhuyenMai";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@maKhuyenMai", SqlDbType.Int)
{
IsNullable = false, Value = maKhuyenMai
}
};
try
{
//nếu số dòng thành công trả về lớn hơn 0 thì thành công
Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(true);
}
//public static int TinhTienGio(string soHoaDon, DateTime, gioRa)
//{
// string query = "EXEC uspThanhToanPhong @soHoaDon";
// List<SqlParameter> parameters = new List<SqlParameter>()
// {
// new SqlParameter("@soHoaDon",SqlDbType.VarChar){IsNullable=false,Value=soHoaDon }
// };
// int result = 0;
// try
// {
// result = (int)Dataprovider.ExcuteScalar(query, parameters.ToArray());
// }
// catch (Exception ex)
// {
// Utility.Log(ex);
// }
// return result == 0 ? false : true;
//}
public static bool ThanhToan(string soHoaDon, DateTime gioRa, int thanhTien, int giamGia)
{
string query = "EXEC uspThanhToanPhong @soHoaDon,@gioRa,@thanhTien,@giamGia";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@soHoaDon", SqlDbType.VarChar)
{
IsNullable = false, Value = soHoaDon
},
new SqlParameter("@gioRa", SqlDbType.DateTime)
{
IsNullable = false, Value = gioRa
},
new SqlParameter("@thanhTien", SqlDbType.Int)
{
IsNullable = false, Value = thanhTien
},
new SqlParameter("@giamGia", SqlDbType.Int)
{
IsNullable = false, Value = giamGia
},
};
try
{
Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(true);
}
public static int DemKhuyenMai(string tenKhuyenMai, int loaiKhachHang)
{
string query = "EXEC uspDemKhuyenMai @tenKhuyenMai,@loaiKhachHang";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tenKhuyenMai", SqlDbType.NVarChar)
{
IsNullable = false, Value = tenKhuyenMai
},
new SqlParameter("@loaiKhachHang", SqlDbType.Int)
{
IsNullable = false, Value = loaiKhachHang
},
};
int count = 0;
try
{
count = int.Parse(Dataprovider.ExcuteScalar(query, parameters.ToArray()).ToString());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(count);
}
public static bool GiaoHang(string soPhieuNhap, string nguoiGiao)
{
string query = "EXEC uspGiaoSanPham @soPhieuNhap,@nguoiGiao";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@soPhieuNhap", SqlDbType.NVarChar)
{
IsNullable = false, Value = soPhieuNhap
},
new SqlParameter("@nguoiGiao", SqlDbType.NVarChar)
{
IsNullable = false, Value = nguoiGiao
},
};
try
{
Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(true);
}
public static DataTable XemLichSuPhong(int pageNumber, int pageSize)
{
string query = "EXEC uspXemLichSuDatPhong @pageNumber,@pageSize";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@pageNumber", SqlDbType.Int)
{
Value = pageNumber
},
new SqlParameter("@pageSize", SqlDbType.Int)
{
Value = pageSize
},
};
DataTable table = null;
try
{
table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(table);
}
public static bool DatCocTien(uint tienDatCoc, string soHD)
{
string query = "UPDATE HOADON SET tienDatCoc=@tienDatCoc WHERE SOHD=@soHD";
int num = 0;
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@tienDatCoc", SqlDbType.Int)
{
IsNullable = false, Value = tienDatCoc
},
new SqlParameter("@soHD", SqlDbType.VarChar)
{
IsNullable = false, Value = soHD
},
};
try
{
num = Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
}
catch (Exception ex)
{
Utility.Log(ex);
return(false);
}
return(num == 1 ? true : false);
}
public static bool CapNhatSanPham(SanPham sanPham)
{
string query = "EXEC uspCapNhatSanPham @ma,@ten,@maNhaCungCap,@maLoai,@donViTinh,@donGia,@soLuongToiThieu,@anhMinhHoa,@donGiaNhap";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@ma", SqlDbType.NVarChar)
{
IsNullable = false, Value = sanPham.Ma
},
new SqlParameter("@ten", SqlDbType.NVarChar)
{
IsNullable = false, Value = sanPham.Ten
},
new SqlParameter("@maNhaCungCap", SqlDbType.NVarChar)
{
IsNullable = false, Value = sanPham.NhaCungCap
},
new SqlParameter("@maLoai", SqlDbType.VarChar)
{
IsNullable = false, Value = sanPham.Loai
},
new SqlParameter("@donViTinh", SqlDbType.NVarChar)
{
IsNullable = false, Value = sanPham.DonViTinh
},
new SqlParameter("@donGia", SqlDbType.Int)
{
IsNullable = false, Value = sanPham.Gia
},
new SqlParameter("@soLuongToiThieu", SqlDbType.Int)
{
IsNullable = false, Value = sanPham.SoLuongToiThieu
},
new SqlParameter("@anhMinhHoa", SqlDbType.NVarChar)
{
IsNullable = false, Value = sanPham.TenHinhAnh
},
new SqlParameter("@donGiaNhap", SqlDbType.Int)
{
IsNullable = false, Value = sanPham.DonGiaNhap
},
};
//nếu số dòng thành công trả về lớn hơn 0 thì thành công
int num = Dataprovider.ExcuteNonQuery(query, parameters.ToArray());
if (num == 0)
{
return(false);
}
return(true);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static List <KhachHang> LoadHanhKhach()
{
string query = "select * from khachhang";
DataTable table = Dataprovider.ExcuteQuery(query);
//Chuyển Table thành List tên hành khách
return(table.AsEnumerable().ToList().ConvertAll(x =>
new KhachHang()
{
Ten = x[0].ToString(), SoDT = x[1].ToString(), Address = x[2].ToString(), Ma = x[2].ToString()
}));
}
public static List <HangHoa> XemSanPham(string nhaCungCap, int loai, int pageNumber, int pageSize)
{
string query = "EXEC uspXemSanPham @nhaCungCap,@loai,'',@pageNumber,@pageSize";
//truyền tham số vào câu truy vấn
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("@nhaCungCap", SqlDbType.VarChar)
{
IsNullable = false, Value = nhaCungCap
},
new SqlParameter("@loai", SqlDbType.Int)
{
IsNullable = false, Value = loai
},
new SqlParameter("@pageNumber", SqlDbType.Int)
{
IsNullable = false, Value = pageNumber
},
new SqlParameter("@pageSize", SqlDbType.Int)
{
IsNullable = false, Value = pageSize
},
};
List <HangHoa> list = new List <HangHoa>();
try
{
DataTable table = Dataprovider.ExcuteQuery(query, parameters.ToArray());
list = table.AsEnumerable().ToList().ConvertAll(x =>
new HangHoa()
{
Ma = x[0].ToString(),
Ten = x[1].ToString(),
Loai = loai.ToString(),
Gia = uint.Parse(x[4].ToString()),
TenHinhAnh = x[5].ToString(),
DonViTinh = x[3].ToString(),
LoaiHangHoa = new LoaiHangHoa()
{
Ma = x[2].ToString(), Ten = x[6].ToString()
},
});
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(list);
}
public static DataTable LoadLoaiKH()
{
string query = "EXEC usp_LoadLoaiKH";
DataTable data = new DataTable();
try
{
data = Dataprovider.ExcuteQuery(query);
}
catch (Exception ex)
{
Utility.Log(ex);
}
return(data);
}