private void LoadCbbStaffList()
{
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "Select * from NHANVIEN";
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.Text = reader["manhanvien"] + " - " + reader["tennhanvien"];
item.Value = reader["manhanvien"];
cbbStaff.Items.Add(item);
}
cbbStaff.SelectedIndex = 0;
}
private double CalTotalTutionFeeOff(string maloaimiengiam)
{
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "SELECT SUM(mucmiengiam) AS tongmiengiam FROM DANHMUCMIENGIAM WHERE mamiengiam IN(" + maloaimiengiam + ")";
double tongmiengiam = 0;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
tongmiengiam = (double)reader["tongmiengiam"];
}
CloseConnection(conn);
return(tongmiengiam);
}
private double CalTotalTutionFee(string maloaiphi)
{
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "SELECT SUM(tienhoc) AS tongtienhoc FROM DANHMUCHOCPHI WHERE maloaihocphi IN(" + maloaiphi + ")";
double tongtienhoc = 0;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
tongtienhoc = (double)reader["tongtienhoc"];
}
CloseConnection(conn);
return(tongtienhoc);
}
private string AggTutionFeeOffName(string maloaimiengiam)
{
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "SELECT tenmiengiam FROM DANHMUCMIENGIAM WHERE mamiengiam IN(" + maloaimiengiam + ")";
string strAggTutionOff = "";
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
strAggTutionOff += reader["tenmiengiam"] + ", ";
}
strAggTutionOff = strAggTutionOff.TrimEnd();
strAggTutionOff = "Miễn giảm: " + strAggTutionOff.Substring(0, strAggTutionOff.Length - 1);
CloseConnection(conn);
return(strAggTutionOff);
}
private void BtnDeleteClickEventHandler(object sender, EventArgs e)
{
DialogResult confirmDelete = MessageBox.Show("Bạn có thực sự muốn xóa tài khoản này không ?", "Xóa tài khoản", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (confirmDelete == DialogResult.Yes)
{
string selectID = grdAccount.SelectedCells[0].Value.ToString();
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "DELETE NGUOIDUNG WHERE Username=@Username";
SqlParameter paramUsername = new SqlParameter();
paramUsername.ParameterName = "@Username";
paramUsername.Value = selectID;
command.Parameters.Add(paramUsername);
command.ExecuteNonQuery();
CloseConnection(conn);
LoadGridAccountData();
}
}
private void updatePhieuThu()
{
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "UPDATE THUHOCPHI SET dathu = @Dathu, ngaythu = GETDATE(), nguoithu = @Nguoithu WHERE mathu = @Mathu";
SqlParameter paramDathu = new SqlParameter
{
ParameterName = "@Dathu",
Value = tongtienNum
};
command.Parameters.Add(paramDathu);
SqlParameter paramNguoithu = new SqlParameter
{
ParameterName = "@Nguoithu",
Value = Frm_dangnhap.manhanvien
};
command.Parameters.Add(paramNguoithu);
SqlParameter paramMathu = new SqlParameter
{
ParameterName = "@Mathu",
Value = lbl_mathu.Text
};
command.Parameters.Add(paramMathu);
command.ExecuteNonQuery();
currentFrmDsHocPhi.LoadGridData("");
}
private bool IsAccountCorrect(string username, string password)
{
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "Select 1 from NGUOIDUNG WHERE username = @Username AND password = @Password";
SqlParameter paramUsername = new SqlParameter
{
ParameterName = "@Username",
Value = username
};
command.Parameters.Add(paramUsername);
SqlParameter paramPassword = new SqlParameter
{
ParameterName = "@Password",
Value = password
};
command.Parameters.Add(paramPassword);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
CloseConnection(conn);
return(true);
}
CloseConnection(conn);
return(false);
}
private void BtnChangePwdClickEventHandler(object sender, EventArgs e)
{
MD5 md5Hash = MD5.Create();
string usernameInput = txt_taikhoan.Text;
string oldpassInput = GetMd5Hash(md5Hash, txt_oldpass.Text);
string newpassInput = GetMd5Hash(md5Hash, txt_newpass.Text);
string renewpassInput = GetMd5Hash(md5Hash, txt_renewpass.Text);
if (string.IsNullOrWhiteSpace(usernameInput) || string.IsNullOrWhiteSpace(oldpassInput) || string.IsNullOrWhiteSpace(newpassInput) || string.IsNullOrWhiteSpace(renewpassInput))
{
MessageBox.Show("Bạn không thể để trống bất kỳ ô dữ liệu nào", "Lỗi");
return;
}
if (!IsAccountCorrect(usernameInput, oldpassInput))
{
MessageBox.Show("Tên tài khoản hoặc mật khẩu của bạn không chính xác", "Lỗi");
return;
}
if (!IsRePasswordMatch(newpassInput, renewpassInput))
{
MessageBox.Show("Mật khẩu mới không trùng nhau", "Lỗi");
return;
}
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "UPDATE NGUOIDUNG SET password = @Password WHERE username = @Username";
SqlParameter paramUsername = new SqlParameter
{
ParameterName = "@Username",
Value = usernameInput
};
command.Parameters.Add(paramUsername);
SqlParameter paramPassword = new SqlParameter
{
ParameterName = "@Password",
Value = newpassInput
};
command.Parameters.Add(paramPassword);
int returnVal = command.ExecuteNonQuery();
if (returnVal == 1)
{
MessageBox.Show("Mật khẩu cho tài khoản: " + usernameInput + " đã được cập nhật");
txt_taikhoan.Text = "";
txt_oldpass.Text = "";
txt_newpass.Text = "";
txt_renewpass.Text = "";
}
else
{
MessageBox.Show("Xảy ra lỗi");
}
}
private void BtnCreateClickEventHandler(object sender, EventArgs e)
{
MD5 md5Hash = MD5.Create();
string usernameInput = txtUsername.Text;
string passwordInput = GetMd5Hash(md5Hash, txtPassword.Text);
string repasswordInput = GetMd5Hash(md5Hash, txtRePassword.Text);
string staffIdInput = (cbbStaff.SelectedItem as ComboBoxItem).Value.ToString();
if (string.IsNullOrWhiteSpace(usernameInput) || string.IsNullOrWhiteSpace(passwordInput) || string.IsNullOrWhiteSpace(repasswordInput) || string.IsNullOrWhiteSpace(staffIdInput))
{
MessageBox.Show("Bạn không thể để trống bất kỳ ô dữ liệu nào", "Lỗi");
return;
}
if (IsDuplicateUsername(usernameInput))
{
MessageBox.Show("Tên đăng nhập đã có trong hệ thống", "Lỗi");
return;
}
if (!IsRePasswordMatch(passwordInput, repasswordInput))
{
MessageBox.Show("Mật khẩu không trùng nhau", "Lỗi");
return;
}
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "INSERT INTO NGUOIDUNG (username, password, manhanvien, quyenid) VALUES(@Username, @Password, @Manhanvien, @QuyenID)";
SqlParameter paramUsername = new SqlParameter();
paramUsername.ParameterName = "@Username";
paramUsername.Value = usernameInput;
command.Parameters.Add(paramUsername);
SqlParameter paramPassword = new SqlParameter();
paramPassword.ParameterName = "@Password";
paramPassword.Value = passwordInput;
command.Parameters.Add(paramPassword);
SqlParameter paramStaffID = new SqlParameter();
paramStaffID.ParameterName = "@Manhanvien";
paramStaffID.Value = staffIdInput;
command.Parameters.Add(paramStaffID);
SqlParameter paramRoleID = new SqlParameter();
paramRoleID.ParameterName = "@QuyenID";
paramRoleID.Value = "KT";
command.Parameters.Add(paramRoleID);
int returnVal = command.ExecuteNonQuery();
if (returnVal == 1)
{
MessageBox.Show("Đã thêm người dùng");
LoadGridAccountData();
grdAccount.Refresh();
ResetInput();
}
else
{
MessageBox.Show("Xảy ra lỗi");
}
}
private void insertNguonThu()
{
string maphieuthuinput = txt_mathu.Text;
string sotienthuinput = txt_sotien.Text;
string sotienchuinput = txt_sotienbangchu.Text;
string lidothuinput = txt_noidungthu.Text;
ComboBoxItem cbbManguoinopSelected = (ComboBoxItem)cbb_manguoinop.SelectedItem;
ComboBoxItem cbbLoainguoinopSelected = (ComboBoxItem)cbb_loainguoinop.SelectedItem;
if (string.IsNullOrWhiteSpace(maphieuthuinput) || string.IsNullOrWhiteSpace(sotienthuinput) || string.IsNullOrWhiteSpace(sotienchuinput) || string.IsNullOrWhiteSpace(lidothuinput))
{
MessageBox.Show("Bạn không thể để trống bất kỳ ô dữ liệu nào", "Lỗi");
return;
}
SqlConnection conn = DBUtils.getConnection();
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "INSERT INTO NGUONTHU VALUES (@mathu,@loainguoinop,@manguoinop,@lidothu,@sotienthu,GETDATE(),@nguoithu)";
SqlParameter paramMaThu = new SqlParameter();
paramMaThu.ParameterName = "@mathu";
paramMaThu.Value = maphieuthuinput;
command.Parameters.Add(paramMaThu);
SqlParameter paramLoainguoinop = new SqlParameter();
paramLoainguoinop.ParameterName = "@loainguoinop";
paramLoainguoinop.Value = cbbLoainguoinopSelected.Value;
command.Parameters.Add(paramLoainguoinop);
SqlParameter paramManguoinop = new SqlParameter();
paramManguoinop.ParameterName = "@manguoinop";
paramManguoinop.Value = cbbManguoinopSelected.Value;
command.Parameters.Add(paramManguoinop);
SqlParameter paramLydothu = new SqlParameter();
paramLydothu.ParameterName = "@lidothu";
paramLydothu.Value = lidothuinput;
command.Parameters.Add(paramLydothu);
SqlParameter paramSotienthu = new SqlParameter();
paramSotienthu.ParameterName = "@sotienthu";
paramSotienthu.Value = sotienthuinput;
command.Parameters.Add(paramSotienthu);
SqlParameter paramNguoithu = new SqlParameter();
paramNguoithu.ParameterName = "@nguoithu";
paramNguoithu.Value = Frm_dangnhap.manhanvien;
command.Parameters.Add(paramNguoithu);
int returnVal = command.ExecuteNonQuery();
}