public UserView(User user)
{
InitializeComponent();
radLabelAddress.Text = Utility.Utility.DisplayNullValues(user.Address);
radLabelUserName.Text = Utility.Utility.DisplayNullValues(user.UserName);
radLabelPhoneNumber.Text = Utility.Utility.DisplayNullValues(user.MobilePhoneNumber);
radLabelMarital.Text = Utility.Utility.DisplayNullValues(user.MaritalStatus);
radLabelFirstName.Text = Utility.Utility.DisplayNullValues(user.FirstName);
radLabelLastName.Text = Utility.Utility.DisplayNullValues(user.LastName);
radLabelEmail.Text = Utility.Utility.DisplayNullValues(user.email);
radLabelDOB.Text = Utility.Utility.DisplayNullValues(Utility.Utility.ConvertDateToString(user.DateOfBirth));
radLabelGender.Text = Utility.Utility.DisplayNullValues(user.Gender);
}
public List<User> GetUsers() {
List<User> listUser = null;
try
{
using (SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString)) {
Constant.VisitaJayaPerkasaApplication.anyConnection = false;
con.Open();
Constant.VisitaJayaPerkasaApplication.anyConnection = true;
using (SqlCommand command = new SqlCommand(
"SELECT person_id, username, first_name, last_name, email, address, " +
"date_of_birth, gender, mobile_phone_number FROM [USER] WHERE (deleted is null OR deleted = '0') " +
"ORDER BY first_name ASC, last_name ASC"
, con)) {
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
User user = new User();
user.PersonID = Utility.Utility.ConvertToUUID(reader.GetValue(0).ToString());
user.UserName = reader.GetString(1);
user.FirstName = (Utility.Utility.IsDBNull(reader.GetValue(2))) ? null : reader.GetString(2);
user.LastName = (Utility.Utility.IsDBNull(reader.GetValue(3))) ? null : reader.GetString(3);
user.email = (Utility.Utility.IsDBNull(reader.GetValue(4))) ? null : reader.GetString(4);
user.Address = (Utility.Utility.IsDBNull(reader.GetValue(5))) ? null : reader.GetString(5);
user.DateOfBirth = (Utility.Utility.IsDBNull(reader.GetValue(6))) ? Utility.Utility.DefaultDateTime() : reader.GetDateTime(6);
user.Gender = (Utility.Utility.IsDBNull(reader.GetValue(7))) ? null : reader.GetString(7);
user.MobilePhoneNumber = (Utility.Utility.IsDBNull(reader.GetValue(8))) ? null : reader.GetString(8);
if (listUser == null)
listUser = new List<User>();
listUser.Add(user);
user = null;
}
}
}
}
catch (Exception e) {
Logging.Error("SqlUserRepository.cs - GetUsers() " + e.Message);
}
return listUser;
}
public UserEdit(User user)
{
InitializeComponent();
sqlRoleRepository = new SqlRoleRepository();
List<Role> listRole = sqlRoleRepository.GetRoles();
cboUserRole.DataSource = listRole;
cboUserRole.DisplayMember = "RoleName";
cboUserRole.ValueMember = "ID";
//radioButtonMale.IsChecked = true;
if (!Constant.VisitaJayaPerkasaApplication.anyConnection)
MessageBox.Show(this, "Please check your connection", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
else if (listRole != null)
{
if (user != null)
{
wantToCreateUser = false;
this.user = user;
etUserName.Text = user.UserName;
etFirstName.Text = user.FirstName;
etLastName.Text = user.LastName;
etEmail.Text = user.email;
etAddress.Text = user.Address;
etPassword.Text = user.Password;
etPasswordHint.Text = user.PasswordHint;
etMobilePhone.Text = user.MobilePhoneNumber;
DOB.Value = user.DateOfBirth;
cbMarital.SelectedItem = user.MaritalStatus;
}
else
{
wantToCreateUser = true;
DOB.Value = DateTime.Today;
}
}
else {
MessageBox.Show(this, "Cannot create/edit user, try again please", "Warning");
radButtonElement2.PerformClick();
}
sqlRoleRepository = null;
}
private void radButtonElement1_Click(object sender, EventArgs e)
{
if (etUserName.Text.Trim().Length == 0)
MessageBox.Show(this, "Please fill username", "Information");
else if (etPassword.Text.Trim().Length == 0)
MessageBox.Show(this, "Please fill password", "Information");
else if (cboUserRole.Text.Equals(Constant.VisitaJayaPerkasaApplication.cboDefaultText))
MessageBox.Show(this, "Please select user role", "Information");
else if (!Regex.Match(etEmail.Text.Trim(), @"^(([\w-]+\.)+[\w-]+|([a-zA-Z]{1}|[\w-]{2,}))@"
+ @"((([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\.([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\."
+ @"([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])\.([0-1]?[0-9]{1,2}|25[0-5]|2[0-4][0-9])){1}|"
+ @"([a-zA-Z]+[\w-]+\.)+[a-zA-Z]{2,4})$").Success)
{
MessageBox.Show(this, "invalid email", "Information");
}
else {
sqlUserRepository = new SqlUserRepository();
//Check username has already exists?
SqlParameter[] param = SqlUtility.SetSqlParameter(new string[]{"username"}, new object[]{etUserName.Text.Trim()});
if (wantToCreateUser)
{
User user = new User();
user.Address = etAddress.Text.Trim();
user.DateOfBirth = DOB.Value;
user.Deleted = 0;
user.email = etEmail.Text.Trim();
user.FirstName = etFirstName.Text.Trim();
user.LastName = etLastName.Text.Trim();
user.MaritalStatus = (cbMarital.Text.Equals(Constant.VisitaJayaPerkasaApplication.cboDefaultText)) ? null : cbMarital.Text;
user.MobilePhoneNumber = etMobilePhone.Text.Trim();
user.Password = Utility.Utility.MD5(etPassword.Text);
user.PasswordHint = etPasswordHint.Text;
user.PersonID = Guid.NewGuid();
user.UserName = etUserName.Text.Trim();
user.RoleObj = new Role();
user.RoleObj.ID = Utility.Utility.ConvertToUUID(cboUserRole.SelectedValue.ToString());
if (sqlUserRepository.CheckUserName(param, Guid.Empty, true))
{
DialogResult dResult = MessageBox.Show(this, "Username has already deleted. Do you want to activate ?", "Confirmation", MessageBoxButtons.YesNo);
if (dResult == DialogResult.Yes)
{
SqlParameter[] parameters = SqlUtility.SetSqlParameter(new string[] { "person_id", "username", "password", "password_hint", "email", "first_name", "last_name", "address", "date_of_birth", "marital_status", "gender", "mobile_phone_number", "deleted", "user_role_id", "user_id", "role_id", "deleted" }
, new object[] { user.PersonID, user.UserName, user.Password, user.PasswordHint, user.email, user.FirstName, user.LastName, user.Address, user.DateOfBirth, user.MaritalStatus, user.Gender, user.MobilePhoneNumber, user.Deleted, Guid.NewGuid(), user.PersonID, user.RoleObj.ID, user.Deleted });
if (sqlUserRepository.ActivateUser(parameters))
{
MessageBox.Show(this, "Success Activate User", "Information");
radButtonElement2.PerformClick();
}
else if (!Constant.VisitaJayaPerkasaApplication.anyConnection)
{
MessageBox.Show(this, "Please check your connection", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
MessageBox.Show(this, "Cannot Activate User", "Information");
parameters = null;
}
return;
}
else if (!Constant.VisitaJayaPerkasaApplication.anyConnection)
{
MessageBox.Show(this, "Please check your connection", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (sqlUserRepository.CheckUserName(param, Guid.Empty))
{
MessageBox.Show(this, "Username has already exists", "Information");
return;
}
else if (!Constant.VisitaJayaPerkasaApplication.anyConnection)
{
MessageBox.Show(this, "Please check your connection", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Create user
SqlParameter []sqlParam = SqlUtility.SetSqlParameter(new string[] { "person_id", "username", "password", "password_hint", "email", "first_name", "last_name", "address", "date_of_birth", "marital_status", "gender", "mobile_phone_number", "deleted", "user_role_id", "user_id", "role_id", "deleted" }
, new object[] { user.PersonID, user.UserName, user.Password, user.PasswordHint, user.email, user.FirstName, user.LastName, user.Address, user.DateOfBirth, user.MaritalStatus, user.Gender, user.MobilePhoneNumber, user.Deleted, Guid.NewGuid(), user.PersonID, user.RoleObj.ID, user.Deleted });
if (sqlUserRepository.CreateUser(sqlParam))
{
MessageBox.Show(this, "Success create user", "Information");
radButtonElement2.PerformClick();
}
else if (!Constant.VisitaJayaPerkasaApplication.anyConnection)
MessageBox.Show(this, "Please check your connection", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
else {
MessageBox.Show(this, "Cannot Create User", "Information");
}
}
else{
User user = new User();
user.Address = etAddress.Text.Trim();
user.DateOfBirth = DOB.Value;
user.Deleted = 0;
user.email = etEmail.Text.Trim();
user.FirstName = etFirstName.Text.Trim();
user.LastName = etLastName.Text.Trim();
user.MaritalStatus = (cbMarital.Text.Equals(Constant.VisitaJayaPerkasaApplication.cboDefaultText)) ? null : cbMarital.Text;
user.MobilePhoneNumber = etMobilePhone.Text.Trim();
user.Password = Utility.Utility.MD5(etPassword.Text);
user.PasswordHint = etPasswordHint.Text;
user.PersonID = this.user.PersonID;
user.UserName = etUserName.Text.Trim();
user.RoleObj = new Role();
user.RoleObj.ID = Utility.Utility.ConvertToUUID(cboUserRole.SelectedValue.ToString());
if (sqlUserRepository.CheckUserName(param, user.PersonID))
{
MessageBox.Show(this, "Username has already exist. if it has already deleted. you must activate it with create new data", "Information");
return;
}
else if (!Constant.VisitaJayaPerkasaApplication.anyConnection)
{
MessageBox.Show(this, "Please check your connection", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
SqlParameter []sqlParam = SqlUtility.SetSqlParameter(new string[] { "person_id", "username", "password", "password_hint", "email", "first_name", "last_name", "address", "date_of_birth", "marital_status", "gender", "mobile_phone_number", "deleted", "user_id", "role_id", "deleted" }
, new object[] { user.PersonID, user.UserName, user.Password, user.PasswordHint, user.email, user.FirstName, user.LastName, user.Address, user.DateOfBirth, user.MaritalStatus, user.Gender, user.MobilePhoneNumber, user.Deleted, user.PersonID, user.RoleObj.ID, user.Deleted });
if (sqlUserRepository.EditUser(sqlParam))
{
MessageBox.Show(this, "Success edit user", "Information");
radButtonElement2.PerformClick();
}
else if (!Constant.VisitaJayaPerkasaApplication.anyConnection)
MessageBox.Show(this, "Please check your connection", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
else {
MessageBox.Show(this, "Cannot edit User", "Information");
}
}
}
}
public void ValidateLogin(SqlParameter[] sqlParam) {
try
{
using (SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString))
{
Constant.VisitaJayaPerkasaApplication.anyConnection = false;
con.Open();
Constant.VisitaJayaPerkasaApplication.anyConnection = true;
using (SqlCommand command = new SqlCommand(
"SELECT TOP 1 username, role_name, first_name, last_name FROM [user] u JOIN [user_role] ur " +
"ON (u.deleted is null OR u.deleted = '0') AND (ur.deleted is null OR ur.deleted = '0') AND u.person_id = ur.user_id AND u.username = "******" " +
"AND u.password = "******" JOIN role r ON r.role_id = ur.role_id", con))
{
foreach (SqlParameter tempSqlParam in sqlParam)
command.Parameters.Add(tempSqlParam);
SqlDataReader reader = command.ExecuteReader();
User user = null;
while (reader.Read())
{
user = new User();
user.UserName = reader.GetString(0);
user.RoleObj = new Role();
user.RoleObj.RoleName = reader.GetString(1);
user.FirstName = reader.GetString(2);
user.LastName = reader.GetString(3);
}
if (user != null)
{
UserProfile.user = user;
user = null;
}
}
}
}
catch(Exception e){
Logging.Error("SqlUserRepository.cs - ValidateLogin() " + e.Message);
}
}
