public List <BACSI> LoadBacSi()
{
// khai bao truy van sql
string sTruyVan = "select * from BACSI where TinhTrangTonTai = '1'";
// Mo ket noi
Con = DataProvider_1.KetNoi();
// Tien hanh truy van
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
// tao List PHIEUKHAM_DTO
List <BACSI> listbs = new List <BACSI>();
for (int i = 0; i < dt.Rows.Count; i++)
{
BACSI bs = new BACSI();
bs.MaBS1 = int.Parse(dt.Rows[i]["MaBS"].ToString());
bs.TenBS1 = dt.Rows[i]["TenBS"].ToString();
bs.TenDangNhap1 = dt.Rows[i]["TenDangNhap"].ToString();
bs.Matkhau = dt.Rows[i]["MatKhau"].ToString();
bs.Loai1 = int.Parse(dt.Rows[i]["Loai"].ToString());
bs.TinhTrangTonTai1 = int.Parse(dt.Rows[i]["TinhTrangTonTai"].ToString());
listbs.Add(bs);
}
DataProvider_1.DongKetNoi(Con);
return(listbs);
}
// them - sua - xoa table BACSI
public static DataTable BangKiemTraTonTai(BACSI bnDTO)
{
// string sTruyVan = string.Format("SELECT MaBN from BENHNHAN WHERE (TenBN like N'{0}') and (NgaySinh = N'{1}' and (DienThoai = '{2}' and GioiTinh = N'{3}'))", bnDTO.TenBN1, bnDTO.NgaySinh1, bnDTO.DienThoai1, bnDTO.GioiTinh1); // MaBN tu dong tang
string sTruyVan = string.Format("SELECT MaBS from BACSI WHERE TenDangNhap =N'{0}' AND TinhTrangTonTai = '1'", bnDTO.TenDangNhap1);
try
{
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
DataProvider_1.DongKetNoi(Con);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
return(dt);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
DataProvider_1.DongKetNoi(Con);
return(null);
}
}
public static List <CTTT> LoadCTTT(int MaPK)
{
string sTruyVan = string.Format("select * from CTTT WHERE MaPK ='{0}'", MaPK);
// Mo ket noi
Con = DataProvider_1.KetNoi();
// Tien hanh truy van
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
// tao List BenhNhan_DTO
List <CTTT> listbn = new List <CTTT>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CTTT DTO = new CTTT();
DTO.MaPK1 = int.Parse(dt.Rows[i]["MaPK"].ToString());
DTO.MaThuoc1 = int.Parse(dt.Rows[i]["MaThuoc"].ToString());
DTO.SoLuong1 = int.Parse(dt.Rows[i]["SoLuong"].ToString());
DTO.DonGia1 = float.Parse(dt.Rows[i]["DonGia"].ToString());
DTO.CachDung1 = dt.Rows[i]["CachDung"].ToString();
}
DataProvider_1.DongKetNoi(Con);
return(listbn);
}
//Load THUOC
public static List <THUOC> LoadTHUOC()
{
// khai bao truy van sql
string sTruyVan = "select * from THUOC where TinhTrangTonTai = '1'";
// Mo ket noi
Con = DataProvider_1.KetNoi();
// Tien hanh truy van
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
// tao List THUOC_DTO
List <THUOC> listbn = new List <THUOC>();
for (int i = 0; i < dt.Rows.Count; i++)
{
THUOC bn = new THUOC();
bn.Mathuoc1 = int.Parse(dt.Rows[i]["MaThuoc"].ToString());
bn.TenThuoc1 = dt.Rows[i]["TenThuoc"].ToString();
bn.DonViTinh1 = dt.Rows[i]["DonViTinh"].ToString();
bn.Gia1 = float.Parse(dt.Rows[i]["Gia"].ToString());
bn.TinhTrangTonTai1 = int.Parse(dt.Rows[i]["TinhTrangTonTai"].ToString());
listbn.Add(bn);
}
DataProvider_1.DongKetNoi(Con);
return(listbn);
}
// bac sy tham gia kham benh
public List <NHANVIEN> LoadNHANVIENThamGiaKhamBenh()
{
// khai bao truy van sql
string sTruyVan = "select * from NHANVIEN where TrangThai = '1' and LoaiNV ='2'";
// Mo ket noi
Con = DataProvider_1.KetNoi();
// Tien hanh truy van
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
// tao List PHIEUKHAM_DTO
List <NHANVIEN> listNV = new List <NHANVIEN>();
for (int i = 0; i < dt.Rows.Count; i++)
{
NHANVIEN NV = new NHANVIEN();
NV.MaNV1 = int.Parse(dt.Rows[i]["MaNV"].ToString());
NV.TenNV1 = dt.Rows[i]["TenNV"].ToString();
NV.TenDangNhap1 = dt.Rows[i]["TenDangNhap"].ToString();
NV.Matkhau1 = dt.Rows[i]["MatKhau"].ToString();
NV.LoaiNV1 = int.Parse(dt.Rows[i]["LoaiNV"].ToString());
NV.TrangThai1 = int.Parse(dt.Rows[i]["TrangThai"].ToString());
listNV.Add(NV);
}
DataProvider_1.DongKetNoi(Con);
return(listNV);
}
//Load benhnhan
public static List <BENHNHAN> LoadBenhNhan()
{
// khai bao truy van sql
string sTruyVan = "select * from BENHNHAN where TrangThai = '1'";
// Mo ket noi
Con = DataProvider_1.KetNoi();
// Tien hanh truy van
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
// tao List BenhNhan_DTO
List <BENHNHAN> listbn = new List <BENHNHAN>();
for (int i = 0; i < dt.Rows.Count; i++)
{
BENHNHAN bn = new BENHNHAN();
// bn.MaBN1 = dt.Rows[i]["MaBN"].ToString(); // MaBN kieu string
bn.MaBN1 = int.Parse(dt.Rows[i]["MaBN"].ToString());
bn.TenBN1 = dt.Rows[i]["TenBN"].ToString();
bn.NgaySinh1 = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
bn.DiaChi1 = dt.Rows[i]["DiaChi"].ToString();
bn.DienThoai1 = dt.Rows[i]["DienThoai"].ToString();
bn.GioiTinh1 = dt.Rows[i]["GioiTinh"].ToString();
bn.TrangThai1 = int.Parse(dt.Rows[i]["TrangThai"].ToString());
listbn.Add(bn);
}
DataProvider_1.DongKetNoi(Con);
return(listbn);
}
public static List <CTTT> LoadCTTT()
{
// khai bao truy van sql
string sTruyVan = "select * from CTTT";
// Mo ket noi
Con = DataProvider_1.KetNoi();
// Tien hanh truy van
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
// tao List PHIEUKHAM_DTO
List <CTTT> listb = new List <CTTT>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CTTT b = new CTTT();
b.MaPK1 = int.Parse(dt.Rows[i]["MaPK"].ToString());
b.MaThuoc1 = int.Parse(dt.Rows[i]["MaThuoc"].ToString());
b.SoLuong1 = int.Parse(dt.Rows[i]["SoLuong"].ToString());
b.DonGia1 = float.Parse(dt.Rows[i]["DonGia"].ToString());
b.CachDung1 = dt.Rows[i]["CachDung"].ToString();
listb.Add(b);
}
DataProvider_1.DongKetNoi(Con);
return(listb);
}
public static float LayGiaCu()
{
float GiaCu = 0;
string query = "select TienKham from QUIDINH";
Con = DataProvider_1.KetNoi();
try
{
DataTable dt = DataProvider_1.LayDataTable(query, Con);
DataProvider_1.DongKetNoi(Con);
if (dt.Rows.Count == 0)
{
return(0);
}
//bn.TenThuoc1 = dt.Rows[i]["TenThuoc"].ToString();
GiaCu = float.Parse(dt.Rows[0]["TienKham"].ToString());
// GiaCu = float.Parse(dt.Rows.ToString());
return(GiaCu);
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
DataProvider_1.DongKetNoi(Con);
return(0);
}
//DataTable dt = DataProvider.LoadTable(query, Con);
//if (dt.Rows.Count == 0)
// return 0;
//GiaCu = float.Parse(dt.Rows.ToString());
//return GiaCu;
}
//Load PHIEUKHAM
public static List <PHIEUKHAM> LoadPhieuKham()
{
// khai bao truy van sql
string sTruyVan = "select * from PHIEUKHAM WHERE TrangThai = '1'";
// Mo ket noi
Con = DataProvider_1.KetNoi();
// Tien hanh truy van
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
// tao List PHIEUKHAM_DTO
List <PHIEUKHAM> listbn = new List <PHIEUKHAM>();
for (int i = 0; i < dt.Rows.Count; i++)
{
PHIEUKHAM bn = new PHIEUKHAM();
bn.MaPK1 = int.Parse(dt.Rows[i]["MaPK"].ToString());
bn.MaNV1 = int.Parse(dt.Rows[i]["MaNV"].ToString());
bn.MaBN1 = int.Parse(dt.Rows[i]["MaBN"].ToString());
bn.NgayKham1 = DateTime.Parse(dt.Rows[i]["NgayKham"].ToString());
bn.TrieuChung1 = dt.Rows[i]["TrieuChung"].ToString();
//bn.KetQua1 = dt.Rows[i]["KetQua"].ToString();
listbn.Add(bn);
}
DataProvider_1.DongKetNoi(Con);
return(listbn);
}
public static DataTable BangKiemTraTonTai(BENHNHAN bnDTO)
{
string sTruyVan = string.Format("SELECT MaBN from BENHNHAN WHERE TinhTrangTonTai = '1' and (NgaySinh = N'{1}' and (DienThoai = '{2}' and GioiTinh = N'{3}'))", bnDTO.TenBN1, bnDTO.NgaySinh1, bnDTO.DienThoai1, bnDTO.GioiTinh1); // MaBN tu dong tang
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
if (dt.Rows.Count == 0)
{
return(null);
}
return(dt);
}
// xoa benh nhan
public DataTable TaoBang(BACSI bnDTO)
{
string query = string.Format("SELECT MaPK from PHIEUKHAM, BACSI WHERE BACSI.MaBS = PHIEUKHAM.MaBS and TenDangNhap = N'{0}'", bnDTO.TenDangNhap1);
Con = DataProvider_1.KetNoi();
DataTable dt = DataProvider_1.LayDataTable(query, Con);
DataProvider_1.DongKetNoi(Con);
return(dt);
}
// xoa benh nhan
public DataTable TaoBang(NHANVIEN bnDTO)
{
string query = string.Format("SELECT MaPK from PHIEUKHAM, NHANVIEN WHERE NHANVIEN.MaNV = PHIEUKHAM.MaNV and TenDangNhap = N'{0}'", bnDTO.TenDangNhap1);
Con = DataProvider_1.KetNoi();
DataTable dt = DataProvider_1.LayDataTable(query, Con);
DataProvider_1.DongKetNoi(Con);
return(dt);
}
public static DataTable TaoBang(THUOC thuoc)
{
string query = string.Format("select CTTT.MaThuoc from CTTT where CTTT.MaThuoc =N'{0}'", thuoc.Mathuoc1);
Con = DataProvider_1.KetNoi();
DataTable dt = DataProvider_1.LayDataTable(query, Con);
DataProvider_1.DongKetNoi(Con);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
return(dt);
}
}
// ham kiem tra ton tai
public static bool KiemTraTonTai(THUOC bnDTO)
{
string sTruyVan = string.Format("SELECT MaThuoc from THUOC WHERE TenThuoc =N'{0}' AND TinhTrangTonTai = '1'", bnDTO.TenThuoc1); // MaBN tu dong tang
Con = DataProvider_1.KetNoi();
try
{
// thuc thi truy van
bool kq = DataProvider_1.ThucThiTruyVanNonQuery(sTruyVan, Con);
// dong ket noi
DataProvider_1.DongKetNoi(Con);
if (kq == true)
{
DataTable dt = DataProvider_1.LayDataTable(sTruyVan, Con);
DataProvider_1.DongKetNoi(Con);
if (dt.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
else
{
return(false);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
DataProvider_1.DongKetNoi(Con);
return(false);
}
}
//
public static DataTable TaoBang(BENHNHAN bnDTO)
{
//string x = "BN003";
//string query = "select pk.MaPK from BENHNHAN bn , PHIEUKHAM pk where bn.MaBN = pk.MaBN and bn.MaBN ='BN001'";
//string query = "select pk.MaPK from BENHNHAN bn , PHIEUKHAM pk where bn.MaBN = pk.MaBN and bn.MaBN ='"+bnDTO.MaBN1+"' ";
//string query = string.Format("select pk.MaPK from BENHNHAN bn , PHIEUKHAM pk where bn.MaBN = pk.MaBN and bn.MaBN ='{0}'", x);
// string query = string.Format("select PHIEUKHAM.MaPK from BENHNHAN , PHIEUKHAM where BENHNHAN.MaBN = PHIEUKHAM.MaBN and BENHNHAN.MaBN =N'{0}'", bnDTO.MaBN1);
string query = string.Format("select PHIEUKHAM.MaPK from PHIEUKHAM where PHIEUKHAM.MaBN =N'{0}'", bnDTO.MaBN1);
Con = DataProvider_1.KetNoi();
DataTable dt = DataProvider_1.LayDataTable(query, Con);
DataProvider_1.DongKetNoi(Con);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
return(dt);
}
}