private void btnAddUserOK_Click(object sender, EventArgs e)
{
//MessageBox.Show("Thêm thành công");
OracleDataProvider.Connect(connect);
// string sql = "select * from PHIEUKHAM";
string sql = "select USERNAME from dba_users where account_status = " + "\'OPEN\'" + "and default_tablespace = " + "\'USERS\'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
string username = txbUserAdd.Text;
string pass = txbUserAddPass.Text;
//MessageBox.Show(dt.Rows[0][0].ToString());
DataRow[] foundAuthors = dt.Select("USERNAME = '******'");
if (foundAuthors.Length != 0)
{
MessageBox.Show("Username đã tồn tại rồi!");
}
else
{
OracleDataProvider.Connect(connect);
string dlItem = "create user " + username + " identified by " + pass;
OracleCommand cmd = new OracleCommand(dlItem, OracleDataProvider.conn);
int ret = cmd.ExecuteNonQuery();
if (ret == -1)
{
MessageBox.Show("Đã thêm thành công!");
}
}
}
private void btnKTVXacNhan_Click(object sender, EventArgs e)
{
/*
* idmapk in varchar2,
* iddichvu in varchar2,
* txtketqua in nvarchar2 DEFAULT null,
* idnhanvien in varchar2 DEFAULT 'NV16'
*/
OracleDataProvider.conn.Open();
string[] arrs = cbxKTVTenBN.SelectedItem.ToString().Split('-');
OracleCommand cmdIn = new OracleCommand("qlbv.update_ketqua", OracleDataProvider.conn);
cmdIn.Parameters.Clear();
cmdIn.CommandType = CommandType.StoredProcedure;
cmdIn.Parameters.Add(new OracleParameter("idmapk", arrs[0]));
cmdIn.Parameters.Add(new OracleParameter("iddichvu", arrs[2]));
cmdIn.Parameters.Add(new OracleParameter("txtketqua", txbKTVMoTa.Text));
cmdIn.Parameters.Add(new OracleParameter("idnhanvien", fLogin.username));
int ret = cmdIn.ExecuteNonQuery();
if (ret == -1)
{
DataTable dt = OracleDataProvider.GetDataTable("select * from QLBV.PK_DV");
dataGridView1.DataSource = dt;
}
// OracleDataProvider.DisConnect();
}
private void fThongTinNV_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
DataTable dt = OracleDataProvider.GetDataTable("select * from qlbv.nhanvien");
dataGridView1.DataSource = dt;
}
private void button1_Click(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
DataTable dt = OracleDataProvider.GetDataTable("select * from qlbv.benhnhan");
dataGridViewThuocDSBN.DataSource = dt;
}
private void btnTiepTanThem_Click(object sender, EventArgs e)
{
OracleDataProvider.conn.Open();
OracleCommand cmdIn = new OracleCommand("qlbv.add_benhnhan", OracleDataProvider.conn);
cmdIn.Parameters.Clear();
cmdIn.CommandType = CommandType.StoredProcedure;
cmdIn.Parameters.Add(new OracleParameter("name_bn", tbxTiepTanTenBN.Text));
cmdIn.Parameters.Add(new OracleParameter("add_bn", tbxTiepTanDiaChi.Text));
cmdIn.Parameters.Add(new OracleParameter("phone", tbxTiepTanDT.Text));
int t = (radioBtnTiepTanNam.Checked == true ? 1 : 0);
cmdIn.Parameters.Add(new OracleParameter("phai1", t.ToString()));
cmdIn.Parameters.Add(new OracleParameter("datens", dateTimePickerNgSinh.Value.ToString("MM-dd-yy")));
cmdIn.Parameters.Add(new OracleParameter("ic", tbxTiepTanCMND.Text));
cmdIn.Parameters.Add("idbn", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
cmdIn.Parameters["idbn"].Size = 255;
int ret = cmdIn.ExecuteNonQuery();
txbDieuPhoiMaBN.Text = cmdIn.Parameters["idbn"].Value.ToString();
if (ret == -1)
{
MessageBox.Show("Them thanh cong!");
}
OracleDataProvider.DisConnect();
OracleDataProvider.conn.Open();
string sql = "select * from QLBV.BENHNHAN ORDER BY MABN DESC";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dataGridViewTiepTanTTBN.DataSource = dt;
}
public void LoadDSNV()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.NHANVIEN order by MANV";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvTNNSNV.DataSource = dt;
}
//private static OracleConnection ocnn;
private void fBacSi_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "SELECT MAPK,MABN,MABS,NGAYKHAM,TRIEUCHUNG,QLBV.DBCRYPT.DECRYPT(CHANDOAN,MABN) AS CHUANDOAN FROM QLBV.PHIEUKHAM";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dataGridViewBSDSBN.DataSource = dt;
}
private void btnTiepTanTimKiem_Click(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.BENHNHAN WHERE DIENTHOAI ='" + txbTiepTanTimSDT.Text + "'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dataGridViewTiepTanTimKiem.DataSource = dt;
}
public void LoadDSBN()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "SELECT V.MAPK, V.MABN,V.NGAYKHAM,V.MABS,T.TENTHUOC,P.SOLUONG FROM QLBV.V_PHIEUKHAM V, QLBV.PK_THUOC P, QLBV.THUOC T WHERE V.MAPK = P.MAPK AND P.MATHUOC = T.MATHUOC";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvDSBN.DataSource = dt;
}
public void LoadDSBN()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "SELECT V.MAPK, V.MABN,V.NGAYKHAM,V.MABS,P.MAKTV,D.TENDV,P.KETQUA,V.CHANDOAN FROM QLBV.V_PHIEUKHAM V, QLBV.PK_DV P, QLBV.DICHVU D WHERE V.MAPK = P.MAPK AND P.MADV = D.MADV";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvTVDSBN.DataSource = dt;
}
public void LoadThuoc()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.THUOC order by MATHUOC";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvQLTVThuoc.DataSource = dt;
}
public void LoadLoaiNV()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.LOAINV order by MALOAI";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvTNNSLoaiNV.DataSource = dt;
}
public void LoadPhanCong()
{
OracleDataProvider.conn.Open();
string sql = @"SELECT * FROM QLBV.CHAMCONG ORDER BY MANV";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvTNNSPCong.DataSource = dt;
}
public void LoadPhong()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.PHONG order by MAPHONG";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvTNNSPhong.DataSource = dt;
}
public void LoadDV()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.DICHVU order by MADV";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvQLTVDV.DataSource = dt;
}
private void btnDsUser_Click(object sender, EventArgs e)
{
OracleDataProvider.Connect(connect);
// string sql = "select * from PHIEUKHAM";
string sql = "select * from dba_users where account_status = " + "\'OPEN\'" + "and default_tablespace = " + "\'USERS\'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dataGridViewInfoUser.DataSource = dt;
}
private void cbxQLTVMaDV_SelectedIndexChanged(object sender, EventArgs e)
{
OracleDataProvider.conn.Open();
string sql = @"select * FROM QLBV.DICHVU WHERE MADV = '" + cbxQLTVMaDV.SelectedItem.ToString() + "'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
txbQLTVTenDV.Text = dt.Rows[0][1].ToString();
txbQLTVGiaDV.Text = dt.Rows[0][2].ToString();
}
private void fTiepTanDieuPhoi_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.BENHNHAN ORDER BY MABN DESC";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dataGridViewTiepTanTTBN.DataSource = dt;
//select QLBV.NHANVIEN.MANV from QLBV.NHANVIEN,QLBV.LOAINV where QLBV.LOAINV.maloai = QLBV.NHANVIEN.LOAINV;
}
private void cbxQLTVMaThuoc_SelectedIndexChanged(object sender, EventArgs e)
{
OracleDataProvider.conn.Open();
string sql = @"select * FROM QLBV.THUOC WHERE MATHUOC = '" + cbxQLTVMaThuoc.SelectedItem.ToString() + "'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
txbQLTVTenThuoc.Text = dt.Rows[0][1].ToString();
txbQLTVGiaThuoc.Text = dt.Rows[0][4].ToString();
}
private void button1_Click(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select nv.manv, ROUND((COUNT(EXTRACT(DAY FROM cc.NGAY))/QLBV.COUNTDAY)*nv.LUONG + nv.PHUCAP ) AS LUONG "
+ "from QLBV.chamcong cc, QLBV.nhanvien nv where nv.manv = cc.manv "
+ "group by nv.manv,nv.LUONG,nv.PHUCAP";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dataGridViewKeToanDSNV.DataSource = dt;
}
private void btnInfoPrivilleges_Click(object sender, EventArgs e)
{
//select * from user_tab_privs where GRANTEE like '%BV_%';
OracleDataProvider.Connect(connect);
// string sql = "select * from PHIEUKHAM";
string sql = "select * from user_tab_privs where GRANTEE like \'%BV_%\'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dataGridViewInfoUser.DataSource = dt;
}
private void fUserRole_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
DataTable dt = OracleDataProvider.GetDataTable("select role from dba_roles where common = \'NO\'");
cbxDropRole.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxDropRole.Items.Add(dt.Rows[i][0] + "");
}
}
private void cbxKeToanMaNV_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbxKeToanMaNV.SelectedIndex != -1)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select luong,phucap from qlbv.NHANVIEN where manv ='" + cbxKeToanMaNV.SelectedItem.ToString() + "'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
txbKeToanLuong.Text = dt.Rows[0][0] + "";
txbKeToanPhuCap.Text = dt.Rows[0][1] + "";
}
}
public void LoadCBXNVPC()
{
OracleDataProvider.conn.Open();
string sql = @"select MANV FROM QLBV.NHANVIEN ORDER BY MANV";
DataTable dt = OracleDataProvider.GetDataTable(sql);
cbxTNNSPCongMaNV.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxTNNSPCongMaNV.Items.Add(dt.Rows[i][0].ToString());
}
}
public void LoadMaPK()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select distinct MAPK from QLBV.PK_DV";
DataTable dt = OracleDataProvider.GetDataTable(sql);
//cbxUserAlterRoleMoi.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxTaiVuMaPK.Items.Add(dt.Rows[i][0].ToString());
}
}
public void LoadCBXLoaiNV()
{
OracleDataProvider.conn.Open();
string sql = @"select maloai from QLBV.LOAINV order by maloai";
DataTable dt = OracleDataProvider.GetDataTable(sql);
cbxTNNSNV_LoaiNV.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxTNNSNV_LoaiNV.Items.Add(dt.Rows[i][0].ToString());
}
}
public void LoadCBXMaThuoc()
{
OracleDataProvider.conn.Open();
string sql = @"select MATHUOC FROM QLBV.THUOC ORDER BY MATHUOC";
DataTable dt = OracleDataProvider.GetDataTable(sql);
cbxQLTVMaThuoc.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxQLTVMaThuoc.Items.Add(dt.Rows[i][0].ToString());
}
}
public void LoadCBXMaNV()
{
OracleDataProvider.conn.Open();
string sql = @"select username from SYS.all_users where username not in (select manv from QLBV.NHANVIEN) order by username";
DataTable dt = OracleDataProvider.GetDataTable(sql);
cbxTNNSMaNV.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxTNNSMaNV.Items.Add(dt.Rows[i][0].ToString());
}
}
private void fThuoc_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select MATHUOC || '-' || TENTHUOC FROM QLBV.THUOC";
DataTable dt = OracleDataProvider.GetDataTable(sql);
comboBox1.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
comboBox1.Items.Add(dt.Rows[i][0] + "");
}
}
public void LoadCBXMaPhong()
{
OracleDataProvider.conn.Open();
string sql = @"select maphong from QLBV.PHONG order by maphong";
DataTable dt = OracleDataProvider.GetDataTable(sql);
cbxTNNSNV_MaPhong.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxTNNSNV_MaPhong.Items.Add(dt.Rows[i][0].ToString());
}
}