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