public static bool InsertHoaDon(HoaDonDTO hoadon)
{
bool result = false;
try
{
//create a list parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
//parameters.Add(new MySqlParameter("@MaTK", taikhoan.MaTK));
parameters.Add(new MySqlParameter("@MaHD", hoadon.MaHD));
parameters.Add(new MySqlParameter("@NgayLapHD", hoadon.NgayLapHD));
parameters.Add(new MySqlParameter("@TongTien", hoadon.TongTien));
parameters.Add(new MySqlParameter("@MaKH", hoadon.MaKH));
parameters.Add(new MySqlParameter("@ThanhTien", hoadon.ThanhTien));
parameters.Add(new MySqlParameter("@GiamGia", hoadon.GiamGia));
//call store Procedure
int n = MySqlDataAccessHelper.ExecuteNonQuery("INSERT INTO hoadon (MaHD, NgayLapHD, TongTien, FK_MaKH, GiamGia, ThanhTien) VALUES(@MaHD, @NgayLapHD, @TongTien, @MaKH, @GiamGia, @ThanhTien)", parameters);
if (n == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static List <KhachHangDTO> SelectKhachHangByTenKhachHang(String tenKhachHang)
{
List <KhachHangDTO> list = new List <KhachHangDTO>();
try
{
// Create List Sql Parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@TenKH", tenKhachHang));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM khachhang WHERE TenKH = @TenKH", parameters);
foreach (DataRow dr in dt.Rows)
{
KhachHangDTO khachhang = new KhachHangDTO();
khachhang.MaKH = dr["MaKH"].ToString();
khachhang.TenKH = dr["TenKH"].ToString();
khachhang.NamSinh = dr["NamSinh"].ToString();
khachhang.SDT = dr["SDT"].ToString();
khachhang.DiaChi = dr["DiaChi"].ToString();
list.Add(khachhang);
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static bool InsertChiTietBaoDuong(ChiTietBaoDuongDTO chitietbaoduong)
{
bool result = false;
try
{
//create a list parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
//parameters.Add(new MySqlParameter("@MaTK", taikhoan.MaTK));
parameters.Add(new MySqlParameter("@MaCTBD", chitietbaoduong.MaCTBD));
parameters.Add(new MySqlParameter("@TrangThai", chitietbaoduong.TrangThai));
parameters.Add(new MySqlParameter("@Phi", chitietbaoduong.Phi));
parameters.Add(new MySqlParameter("@FK_MaPT", chitietbaoduong.MaPT));
parameters.Add(new MySqlParameter("@FK_MaDV_CTBD", chitietbaoduong.MaDV));
parameters.Add(new MySqlParameter("@FK_MaHD", chitietbaoduong.MaHD));
parameters.Add(new MySqlParameter("@FK_MaCV", chitietbaoduong.MaCV));
parameters.Add(new MySqlParameter("@SoLuong", chitietbaoduong.SoLuong));
parameters.Add(new MySqlParameter("@DonGia", chitietbaoduong.DonGia));
//call store Procedure
int n = MySqlDataAccessHelper.ExecuteNonQuery("INSERT INTO chitietbaoduong (MaCTBD, TrangThai, Phi, FK_MaPT, FK_MaDV_CTBD, FK_MaHD, FK_MaCV,SoLuong, DonGia) VALUES(@MaCTBD, @TrangThai, @Phi, @FK_MaPT, @FK_MaDV_CTBD, @FK_MaHD, @FK_MaCV, @SoLuong, @DonGia)", parameters);
if (n == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static List <CongViecDTO> SelectCongViecByTenCongViec(String tenCV)
{
List <CongViecDTO> list = new List <CongViecDTO>();
try
{
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@TenCV", tenCV));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM congviec where TenCV = @TenCV", parameters);
foreach (DataRow dr in dt.Rows)
{
CongViecDTO congviec = new CongViecDTO();
congviec.MaCV = dr["MaCV"].ToString();
congviec.TenCV = dr["TenCV"].ToString();
congviec.MoTa = dr["MoTa"].ToString();
list.Add(congviec);
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static bool InsertTaiKhoan(TaiKhoanDTO taikhoan)
{
bool result = false;
try
{
//create a list parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
//parameters.Add(new MySqlParameter("@MaTK", taikhoan.MaTK));
parameters.Add(new MySqlParameter("@UserName", taikhoan.UserName));
parameters.Add(new MySqlParameter("@Password", taikhoan.Password));
parameters.Add(new MySqlParameter("@FK_NV", taikhoan.MaNhanVien));
parameters.Add(new MySqlParameter("@TinhTrang", taikhoan.TinhTrang));
//call store Procedure
int n = MySqlDataAccessHelper.ExecuteNonQuery("INSERT INTO taikhoan (UserName, Password, FK_NV, TinhTrang) VALUES(@UserName, @Password, @FK_NV, @TinhTrang)", parameters);
if (n == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static bool InsertPhanCong(PhanCongDTO phancong)
{
bool result = false;
try
{
//create a list parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
//parameters.Add(new MySqlParameter("@MaTK", taikhoan.MaTK));
parameters.Add(new MySqlParameter("@MaPC", phancong.MaPC));
parameters.Add(new MySqlParameter("@ThoiGian", phancong.ThoiGian));
parameters.Add(new MySqlParameter("@GhiChu", phancong.GhiChu));
parameters.Add(new MySqlParameter("@FK_MaCV", phancong.MaCV));
parameters.Add(new MySqlParameter("@FK_MaNV", phancong.MaNV));
parameters.Add(new MySqlParameter("@FK_MaDV", phancong.MaDV));
//call store Procedure
int n = MySqlDataAccessHelper.ExecuteNonQuery("INSERT INTO phancong (MaPC, NgayPC, GhiChu, MaCV, MaNV, MaDV) VALUES(@MaPC, @ThoiGian, @GhiChu, @FK_MaCV, @FK_MaNV, @FK_MaDV)", parameters);
if (n == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static NhanVienDTO SelectNhanVienById(String nhanvienID)
{
NhanVienDTO nhanvien = new NhanVienDTO();
try
{
// Create List Sql Parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@MaNV", nhanvienID));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM nhanvien WHERE MaNV = @MaNV", parameters);
DataRow dr = dt.Rows[0];
nhanvien.MaNV = dr["MaNV"].ToString();
nhanvien.TenNV = dr["TenNV"].ToString();
nhanvien.NamSinh = dr["NamSinh"].ToString();
nhanvien.SDT = dr["SDT"].ToString();
nhanvien.DiaChi = dr["DiaChi"].ToString();
nhanvien.ChucVu = dr["ChucVu"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(nhanvien);
}
public static bool InsertDichVu(DichVuDTO dichvu)
{
bool result = false;
try
{
//create a list parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
//parameters.Add(new MySqlParameter("@MaTK", taikhoan.MaTK));
parameters.Add(new MySqlParameter("@MaDV", dichvu.MaDV));
parameters.Add(new MySqlParameter("@NgayBD", dichvu.NgayDV));
parameters.Add(new MySqlParameter("@FK_SoXe", dichvu.SoXe));
//call store Procedure
int n = MySqlDataAccessHelper.ExecuteNonQuery("INSERT INTO dichvu (MaDV, NgayBD, FK_SoXE) VALUES(@MaDV, @NgayBD, @FK_SoXe)", parameters);
if (n == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static List <NhanVienDTO> SelectNhanVienByTenNhanVien(String tenNV)
{
List <NhanVienDTO> list = new List <NhanVienDTO>();
try
{
// Create List Sql Parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@TenNV", tenNV));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM nhanvien WHERE TenNV = @TenNV", parameters);
foreach (DataRow dr in dt.Rows)
{
NhanVienDTO nhanvien = new NhanVienDTO
{
MaNV = dr["MaNV"].ToString(),
TenNV = dr["TenNV"].ToString(),
NamSinh = dr["NamSinh"].ToString(),
SDT = dr["SDT"].ToString(),
DiaChi = dr["DiaChi"].ToString(),
ChucVu = dr["ChucVu"].ToString()
};
list.Add(nhanvien);
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static HoaDonDTO SelectHoaDonById(String hoadonID)
{
HoaDonDTO hoadon = new HoaDonDTO();
try
{
// Create List Sql Parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@MaHD", hoadonID));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM hoadon WHERE MaHD = @MaHD", parameters);
DataRow dr = dt.Rows[0];
hoadon.MaHD = dr["MaHD"].ToString();
hoadon.NgayLapHD = DateTime.Parse(dr["NgayLapHD"].ToString());
hoadon.TongTien = int.Parse(dr["TongTien"].ToString());
hoadon.MaKH = dr["FK_MaKH"].ToString();
hoadon.GiamGia = int.Parse(dr["GiamGia"].ToString());
hoadon.ThanhTien = int.Parse(dr["ThanhTien"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(hoadon);
}
public static List <NhanVienDTO> SelectNhanVienAll()
{
List <NhanVienDTO> list = new List <NhanVienDTO>();
try
{
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM nhanvien");
foreach (DataRow dr in dt.Rows)
{
NhanVienDTO nhanvien = new NhanVienDTO
{
MaNV = dr["MaNV"].ToString(),
TenNV = dr["TenNV"].ToString(),
NamSinh = dr["NamSinh"].ToString(),
SDT = dr["SDT"].ToString(),
DiaChi = dr["DiaChi"].ToString(),
ChucVu = dr["ChucVu"].ToString()
};
list.Add(nhanvien);
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static PhuTungDTO SelectPhuTungById(String phutungID)
{
PhuTungDTO phutung = new PhuTungDTO();
try
{
// Create List Sql Parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@MaPT", phutungID));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM phutung WHERE MaPT = @MaPT", parameters);
DataRow dr = dt.Rows[0];
phutung.MaPT = dr["MaPT"].ToString();
phutung.TenPT = dr["TenPT"].ToString();
phutung.MoTa = dr["MoTa"].ToString();
phutung.ThoiHanBH = int.Parse(dr["ThoiHanBH"].ToString());
phutung.GiaPT = int.Parse(dr["GiaPT"].ToString());
phutung.DonViTinh = dr["DVT"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(phutung);
}
public static String GenerateMaDichVu()
{
int newMaDichVu = 0;
try
{
System.Data.DataTable dt = MySqlDataAccessHelper.ExecuteQuery("select max(cast(replace(replace(MaDV, 'DV', ''), '', '') as unsigned)) + 1 as newMaDichVu from dichvu");
newMaDichVu = dt.Rows[0]["NewMaDichVu"].ToString() == "" ? 1 : int.Parse(dt.Rows[0]["NewMaDichVu"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(string.Format("DV{0:000}", newMaDichVu));
}
public static String GenerateMaPhanCong()
{
int newMaPhanCong = 0;
try
{
System.Data.DataTable dt = MySqlDataAccessHelper.ExecuteQuery("select max(cast(replace(replace(MaPC, 'PC', ''), '', '') as unsigned)) + 1 as newMaPhanCong from phancong");
newMaPhanCong = dt.Rows[0]["NewMaPhanCong"].ToString() == "" ? 1 : int.Parse(dt.Rows[0]["NewMaPhanCong"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(string.Format("PC{0:000}", newMaPhanCong));
}
public static String GenerateMaHoaDon()
{
int newMaHoaDon = 0;
try
{
System.Data.DataTable dt = MySqlDataAccessHelper.ExecuteQuery("select max(cast(replace(replace(MaHD, 'HD', ''), '', '') as unsigned)) + 1 as newMaHoaDon from hoadon");
newMaHoaDon = dt.Rows[0]["NewMaHoaDon"].ToString() == "" ? 1 : int.Parse(dt.Rows[0]["NewMaHoaDon"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(string.Format("HD{0:000}", newMaHoaDon));
}
public static String GenerateMaCTBD()
{
int newMaChiTiet = 0;
try
{
System.Data.DataTable dt = MySqlDataAccessHelper.ExecuteQuery("select max(cast(replace(replace(MaCTBD, 'CT', ''), '', '') as unsigned)) + 1 as newMaChiTiet from chitietbaoduong");
newMaChiTiet = dt.Rows[0]["NewMaChiTiet"].ToString() == "" ? 1 : int.Parse(dt.Rows[0]["NewMaChiTiet"].ToString());
}
catch (Exception ex)
{
throw ex;
}
return(string.Format("CT{0:000}", newMaChiTiet));
}
public static ChucVuDTO SelectChucVuById(String chucvuID)
{
ChucVuDTO chucvu = new ChucVuDTO();
try
{
// Create List Sql Parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@MaCV", chucvuID));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM chucvu WHERE (MaCV = @MaCV)", parameters);
DataRow dr = dt.Rows[0];
chucvu.MaCV = dr["MaCV"].ToString();
chucvu.TenCV = dr["TenCV"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(chucvu);
}
public static bool CheckNhanVienById(String nhanvienID)
{
bool result = false;
try
{
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@MaNV", nhanvienID));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT MaNV FROM nhanvien WHERE MaNV = @MaNV", parameters);
if (dt.Rows.Count == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static List <CongViecDTO> SelectCongViecAll()
{
List <CongViecDTO> list = new List <CongViecDTO>();
try
{
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM congviec");
foreach (DataRow dr in dt.Rows)
{
CongViecDTO congviec = new CongViecDTO();
congviec.MaCV = dr["MaCV"].ToString();
congviec.TenCV = dr["TenCV"].ToString();
congviec.MoTa = dr["MoTa"].ToString();
list.Add(congviec);
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
public static bool CheckTaiKhoanByUserNameAndPassword(string username, string password)
{
bool result = false;
try
{
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@UserName", username));
parameters.Add(new MySqlParameter("@Password", password));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT FK_NV FROM taikhoan WHERE UserName = @UserName AND Password= Password", parameters);
if (dt.Rows.Count == 1)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static KhachHangDTO SelectKhachHangById(String khachhangID)
{
KhachHangDTO khachhang = new KhachHangDTO();
try
{
// Create List Sql Parameter
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@MaKH", khachhangID));
DataTable dt = MySqlDataAccessHelper.ExecuteQuery("SELECT * FROM khachhang WHERE MaKH = @MaKH", parameters);
DataRow dr = dt.Rows[0];
khachhang.MaKH = dr["MaKH"].ToString();
khachhang.TenKH = dr["TenKH"].ToString();
khachhang.NamSinh = dr["NamSinh"].ToString();
khachhang.SDT = dr["SDT"].ToString();
khachhang.DiaChi = dr["DiaChi"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(khachhang);
}