public ChinhSuaQuyen()
{
OracleDataProvider.Connect(fLogin.cn);
ocnn = OracleDataProvider.conn;
InitializeComponent();
//tao list nguoi dung
String slUser = "******" + "\'OPEN\'" + "and default_tablespace = " + "\'USERS\'";
OracleCommand cmdSl = new OracleCommand(slUser, ocnn);
OracleDataReader slR = cmdSl.ExecuteReader();
while (slR.Read())
{
if (!slR["username"].ToString().Equals(fLogin.username.ToUpper()))
{
comboUser.Items.Add(slR["username"].ToString());
comboBox3.Items.Add(slR["username"].ToString());
}
}
slR.Close();
//tao list role
String role = "select role from dba_roles where common = " + "\'NO\'";
OracleCommand cmdRole = new OracleCommand(role, ocnn);
OracleDataReader slRole = cmdRole.ExecuteReader();
while (slRole.Read())
{
comboUser.Items.Add(slRole["role"].ToString());
//comboBox3.Items.Add(slRole["role"].ToString());
CbxTrenCot.Items.Add(slRole["role"].ToString());
}
slRole.Close();
CbxTrenCot.Items.Add("DBA");
}
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 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 fThongTinNV_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
DataTable dt = OracleDataProvider.GetDataTable("select * from qlbv.nhanvien");
dataGridView1.DataSource = dt;
}
private void btnDangNhap_Click(object sender, EventArgs e)
{
username = txbUsername.Text;
password = txbPassword.Text;
string connect = OracleDataProvider.stringConnection("10.211.55.4", 1521, "orcl3", username, password);
try
{
OracleDataProvider.Connect(connect);
if (username.Equals("QLBV"))
{
var frm = new fAdmin();
frm.ShowDialog();
}
}
catch
{
MessageBox.Show("Dang nhap that bai");
}
if (txbUsername.Text == "admin" && txbPassword.Text == "admin")
{
fAdmin f = new fAdmin();
this.Hide();
f.ShowDialog();
this.Show();
}
else
{
MessageBox.Show("Sai mật khẩu hoặc password !", "Login error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
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 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;
}
public void LoadThuoc()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.THUOC order by MATHUOC";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvQLTVThuoc.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 LoadDSNV()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.NHANVIEN order by MANV";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvTNNSNV.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;
}
private void fQLChuyenMon_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.PHONG";
DataTable dt = OracleDataProvider.GetDataTableWithoutDisConnect(sql);
dataGridViewQLCMPhong.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 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 LoadLoaiNV()
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from QLBV.LOAINV order by MALOAI";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvTNNSLoaiNV.DataSource = dt;
}
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 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 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 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 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] + "";
}
}
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] + "");
}
}
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());
}
}
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] + "");
}
}
private void btnTatNK_Click(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
String query = "ALTER SYSTEM SET audit_trail = none SCOPE = SPFILE";
OracleCommand cmd = new OracleCommand(query, OracleDataProvider.conn);
cmd.ExecuteNonQuery();
MessageBox.Show("Đã tắt theo dõi nhật ký", "Thông báo");
string sql = "select * from v$parameter where name like \'%audit%\'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
dtgvXemNK.DataSource = dt;
}
private void fUserDelete_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
// 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);
cbxUserDelete.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxUserDelete.Items.Add(dt.Rows[i][0].ToString());
}
}
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
if (tabControl1.SelectedIndex == 1)
{
cbxKTVTenBN.Items.Clear();
OracleDataProvider.Connect(fLogin.cn);
DataTable dt = OracleDataProvider.GetDataTable("select QLBV.PK_DV.MAPK || '-' || QLBV.DICHVU.TENDV || '-' ||QLBV.PK_DV.MADV from QLBV.PK_DV, QLBV.DICHVU where QLBV.DICHVU.MADV = QLBV.PK_DV.MADV ORDER BY QLBV.PK_DV.MAPK DESC");
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxKTVTenBN.Items.Add(dt.Rows[i][0] + "");
}
}
}
private void button2_Click(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select * from DBA_TAB_PRIVS WHERE grantee ='" + comboBox2.SelectedItem.ToString() + "'";
if (checkBox1.Checked == true)
{
sql = "select * from DBA_COL_PRIVS where GRANTEE = '" + comboBox2.SelectedItem.ToString() + "'";
}
DataTable dt = OracleDataProvider.GetDataTable(sql);
dataGridViewInfoUser.DataSource = dt;
}
private void tabTiepTan_SelectedIndexChanged(object sender, EventArgs e)
{
if (tabTiepTan.SelectedIndex == 1)
{
OracleDataProvider.Connect(fLogin.cn);
string sql = "select QLBV.NHANVIEN.MANV from QLBV.NHANVIEN,QLBV.LOAINV where QLBV.LOAINV.maloai = QLBV.NHANVIEN.LOAINV AND QLBV.LOAINV.maloai = \'L3\'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
cbxDieuPhoiBSi.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
cbxDieuPhoiBSi.Items.Add(dt.Rows[i][0].ToString());
}
}
}
private void Message_Load(object sender, EventArgs e)
{
OracleDataProvider.Connect(fLogin.cn);
DataTable dt = OracleDataProvider.GetDataTable("select matn,tinnhan from QLBV.THONGBAO");
dataGridView1.DataSource = dt;
OracleDataProvider.conn.Open();
dt = OracleDataProvider.GetDataTable("select DEFAULT_READ_LABEL from LBACSYS.ALL_SA_USER_LABELS");
if (dt.Rows.Count > 0)
{
textBox1.Text = dt.Rows[0][0] + "";
}
}
private void cbxUserAlter_SelectedIndexChanged(object sender, EventArgs e)
{
OracleDataProvider.Connect(connect);
//select * from dba_role_privs where GRANTED_ROLE like '%BV_%' and GRANTEE =
cbxUserAlter.Text = cbxUserAlter.SelectedItem.ToString();
string sql = "select * from dba_role_privs where GRANTED_ROLE like '%BV_%' and GRANTEE ='" + cbxUserAlter.SelectedItem.ToString() + "'";
DataTable dt = OracleDataProvider.GetDataTable(sql);
cbxUserAlterRoleCu.Text = "";
if (dt.Rows.Count > 0)
{
cbxUserAlterRoleCu.Text = (dt.Rows[0][1].ToString());
}
}