//Metoda ce executa comanda SQL de interogare a bazei de date cu privire la utilizatorul specificat
private DataTable getUser(String sqlStatement, String userName)
{
MySqlCommand userExistenceCheckCommand = new MySqlCommand(sqlStatement);
userExistenceCheckCommand.Parameters.AddWithValue("@paramUserName", userName);
return(DBConnectionManager.getData(userExistenceCheckCommand));
}
public DataTable getNewData(QueryType option, QueryData paramContainer, SelectedDataSource dataSource)
{
MySqlCommand command = null;
if (option == QueryType.SINGLE_MONTH)
{
switch (dataSource)
{
//Grid view
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementSingleMonthExpenses, paramContainer);
break;
//Pie chart
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementExpenseTypeSumSingle, paramContainer);
break;
//Column chart
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalExpenses, paramContainer);
break;
default:
break;
}
}
else if (option == QueryType.MULTIPLE_MONTHS)
{
switch (dataSource)
{
//Grid view
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementMultipleMonthsExpenses, paramContainer);
break;
//Pie chart
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementExpenseTypeSumMultiple, paramContainer);
break;
//Column chart
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalExpenses, paramContainer);
break;
default:
break;
}
}
else if (option == QueryType.MONTHLY_TOTALS)
{
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalExpenses, paramContainer);
}
return(DBConnectionManager.getData(command));
}
private DataTable getData(int userID)
{
String sqlStatementGetAuthenticationData = @"SELECT username, salt, password FROM users WHERE userID = @paramID";
MySqlCommand command = new MySqlCommand(sqlStatementGetAuthenticationData);
command.Parameters.AddWithValue("@paramID", userID);
return(DBConnectionManager.getData(command));
}
//The method returns a DataTable object using the provided arguments to decide the actual SQl query that will be executed(the query type(single/multiple months) and the data source that will be populated with data are taken into account for this decision)
public DataTable getNewData(QueryType option, QueryData paramContainer, SelectedDataSource dataSource)
{
//Creates a MySqlCommand object tht will be populated with the actual command object selected after analyzing the previously mentioned arguments inside the if/else and switch block
MySqlCommand command = null;
//Single month query
//The specific SQL query from which the command will be created is selected based on the data source and the type of data that needs to be displayed
if (option == QueryType.SINGLE_MONTH)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementSingleMonthIncomes, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementIncomeTypeSumSingle, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalIncomes, paramContainer);
break;
default:
break;
}
//Multiple months query
}
else if (option == QueryType.MULTIPLE_MONTHS)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementMultipleMonthsIncomes, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementIncomeTypeSumMultiple, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalIncomes, paramContainer);
break;
default:
break;
}
//Monthly totals query
}
else if (option == QueryType.MONTHLY_TOTALS)
{
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalIncomes, paramContainer);
}
//The data is retrieved by passing the previously obtained command to the DBConnectionManger's getData() method
return(DBConnectionManager.getData(command));
}
public DataTable getNewData(QueryType option, QueryData paramContainer, SelectedDataSource dataSource)
{
MySqlCommand command = null;
if (option == QueryType.SINGLE_MONTH)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementSingleMonthSavings, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementSavingsValueSumSingle, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalSavings, paramContainer);
break;
default:
break;
}
}
else if (option == QueryType.MULTIPLE_MONTHS)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementMultipleMonthsSavings, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementSavingsValueSumMultiple, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalSavings, paramContainer);
break;
default:
break;
}
//Obtinere comanda pentru extragere date pentru fiecare luna a unui an
}
else if (option == QueryType.MONTHLY_TOTALS)
{
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalSavings, paramContainer);
}
return(DBConnectionManager.getData(command));
}
public DataTable getNewData(QueryType option, QueryData paramContainer, SelectedDataSource dataSource)
{
MySqlCommand command = null;
if (option == QueryType.SINGLE_MONTH)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementSingleMonthDebts, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementDebtValueSumForCreditorSingle, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalDebts, paramContainer);
break;
default:
break;
}
}
else if (option == QueryType.MULTIPLE_MONTHS)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementMultipleMonthsDebts, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementDebtValueSumForCreditorMultiple, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalDebts, paramContainer);
break;
default:
break;
}
}
else if (option == QueryType.MONTHLY_TOTALS)
{
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementMonthlyTotalDebts, paramContainer);
}
return(DBConnectionManager.getData(command));
}
//Metoda prin care modelul aduce date din DB
public DataTable getNewData(QueryType option, QueryData paramContainer, SelectedDataSource dataSource)
{
MySqlCommand command = null;
if (option == QueryType.SINGLE_MONTH)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getSingleMonthCommand(sqlStatementSummarySingle, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
break;
case SelectedDataSource.STATIC_DATASOURCE:
break;
default:
break;
}
}
else if (option == QueryType.MULTIPLE_MONTHS)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementSummaryMultiple, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
break;
case SelectedDataSource.STATIC_DATASOURCE:
break;
default:
break;
}
}
//In cazul modelelor care nu utilizeaza toate cele trei data tables definite in interfata IModel comanda SQL ramane cu valoarea null si
//astfel nu se va mai executa metoda de extragere a datelor din DB
if (command == null)
{
return(null);
}
return(DBConnectionManager.getData(command));
}
public DataTable getNewData(QueryType option, QueryData paramContainer, SelectedDataSource dataSource)
{
MySqlCommand command = null;
if (option == QueryType.SINGLE_MONTH)
{
switch (dataSource)
{
//Grid view
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
break;
//Pie chart
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
break;
//Column chart
case SelectedDataSource.STATIC_DATASOURCE:
command = getCorrectSqlCommandForDataDisplay(option, paramContainer);
break;
default:
break;
}
}
else if (option == QueryType.FULL_YEAR)
{
switch (dataSource)
{
//Grid view
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
break;
//Pie chart
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
break;
//Column chart
case SelectedDataSource.STATIC_DATASOURCE:
command = getCorrectSqlCommandForDataDisplay(option, paramContainer);
break;
default:
break;
}
}
return(DBConnectionManager.getData(command));
}
//Aduce adresa de email a utilizatorului care cere resetarea parolei, din baza de date
private String getUserEmail(String userName)
{
MySqlCommand retrieveUserEmailCommand = new MySqlCommand(sqlStatementRetrieveUserEmail);
retrieveUserEmailCommand.Parameters.AddWithValue("@paramUserName", userName);
DataTable userEmailTable = DBConnectionManager.getData(retrieveUserEmailCommand);
String userEmail = "";
if (userEmailTable != null && userEmailTable.Rows.Count == 1)
{
userEmail = userEmailTable.Rows[0].ItemArray[0].ToString();
}
return(userEmail);
}
private void loginButton_Click(object sender, EventArgs e)
{
if (!DBConnectionManager.hasConnection())
{
//MessageBox.Show(this, "No database connection! Unable to login.", "Login");
MessageBox.Show(this, "No database connection! Unable to login.", "Login", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
String userName = userNameTextBox.Text;
String password = passwordTextBox.Text;
MySqlCommand authenticationDataCommand = new MySqlCommand(sqlStatementGetAuthenticationData);
authenticationDataCommand.Parameters.AddWithValue("@paramUserName", userName);
//Aducere informatii din baza de date
DataTable authenticationData = DBConnectionManager.getData(authenticationDataCommand);
//Verificarea existentei utilizatorului si a corectitudinii datelor introduse
if (userExists(authenticationData) && hasValidCredentials(authenticationData, password))
{
//Se extrage id-ul de utilizator
int userID = getUserID(authenticationData);
this.Visible = false;
//Se trimite id-ul ca argument constructorului clasei UserDashboard pt a putea fi utilizat ulterior la interogarea bazei de date
UserDashboard userDashboard = new UserDashboard(userID, userName);
userDashboard.Visible = true;
}
else
{
//MessageBox.Show("Invalid username and/or password! Please try again", "Login");
MessageBox.Show("Invalid username and/or password! Please try again", "Login", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void resetPasswordButton_Click(object sender, EventArgs e)
{
if (!DBConnectionManager.hasConnection())
{
//MessageBox.Show(this, "No database connection! Unable to reset your password.", "Password reset manager");
MessageBox.Show(this, "No database connection! Unable to reset your password.", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Cere confirmarea utilizatorului pentru resetarea parolei si inregistreaza rezultatul
//DialogResult userOption = MessageBox.Show(this, "Are you sure that you want to reset your password?", "Password reset manager", MessageBoxButtons.YesNo);
DialogResult userOption = MessageBox.Show(this, "Are you sure that you want to reset your password?", "Password reset manager", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
//Daca se selecteaza optiunea "No" se iese din metoda si se opreste procesul de resetare
if (userOption == DialogResult.No)
{
//Console.WriteLine("User selected NO option");
return;
}
//Adunare date necesare resetarii parolei
String userName = userNameTextBox.Text;
String newPassword = newPasswordTextBox.Text;
String confirmationPassword = confirmPasswordTextBox.Text;
//Verifica daca noua parola si parola de confirmare sunt identice
if (!newPassword.Equals(confirmationPassword))
{
//MessageBox.Show("The input passwords don't match! Please try again!", "Password reset manager");
MessageBox.Show("The input passwords don't match! Please try again!", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//Verifica daca parola respecta regulile de complexitate
if (newPassword.Length < minimumPasswordLength)
{
//MessageBox.Show("Your password should be at least 10 characters long! Please try again.", "Password reset manager");
MessageBox.Show("Your password should be at least 10 characters long! Please try again.", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (!isValidPassword(newPassword))
{
//MessageBox.Show("Invalid password! Your password must contain:\n1.Lowercase and uppercase letters (a-zA-z) \n2.Digits (0-9) \n3.Special characters (@#$%<>?)", "Password reset manager");
MessageBox.Show("Invalid password! Your password must contain:\n1.Lowercase and uppercase letters (a-zA-z) \n2.Digits (0-9) \n3.Special characters (@#$%<>?)", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
MySqlCommand retrieveResetPasswordDataCommand = new MySqlCommand(sqlStatementGetDataForPasswordReset);
retrieveResetPasswordDataCommand.Parameters.AddWithValue("@paramUserName", userName);
DataTable resetPasswordDataTable = DBConnectionManager.getData(retrieveResetPasswordDataCommand);
//Se verifica daca exista utilizatorul
if (userExists(resetPasswordDataTable))
{
Object emailData = resetPasswordDataTable.Rows[0].ItemArray[2];
String userEmail = emailData != DBNull.Value ? emailData.ToString() : "";
//Se verifica daca utilizatorul are setata o adresa de email
if ("".Equals(userEmail))
{
//MessageBox.Show("Unable to retrieve the email address for the selected user!", "Password reset manager");
MessageBox.Show("Unable to retrieve the email address for the selected user!", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Se creeaza obiectele necesare pt procesul de resetare doar daca sunt indeplinite toate conditiile anterioare
//Obiectul pt resetarea efectiva a parolei
PasswordResetManager passwordResetManager = new PasswordResetManager();
//Obiectul pt trimiterea codului de confirmare
ConfirmationSender confirmationSender = new ConfirmationSender();
//Date necesare pt trimiterea emailului
string emailSubject = "Password reset";
string emailBody = "A password reset was requested for the account associated to this email address.\nPlease enter the following code to finish the password reset process: {0} \nIf you have not requested the password reset please ignore this email and delete it.";
string onSuccessMessage = "An email containing the reset password procedure has been sent to your email address";
string parentWindowName = "Password reset manager";
string generatedConfirmationCode = confirmationSender.generateConfirmationCode();
confirmationSender.sendConfirmationEmail(userEmail, emailSubject, emailBody, generatedConfirmationCode, onSuccessMessage, parentWindowName);
String userInputConfirmationCode = Interaction.InputBox("Enter the code received on your email to finish the reset process:", "Confirmation Code", "Enter code", 200, 200);
if (confirmationSender.confirmationCodesMatch(generatedConfirmationCode, userInputConfirmationCode))
{
int userID = Convert.ToInt32(resetPasswordDataTable.Rows[0].ItemArray[0]);
int executionResult = passwordResetManager.resetPassword(newPassword, userID);//Daca din diverse motive nu se pot insera in baza de date noile informatii(salt si hashcode) metoda returneaza -1
if (executionResult == -1)
{
//MessageBox.Show("Could not reset your password!", "Password reset manager");
MessageBox.Show("Could not reset your password!", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//MessageBox.Show("Your password has been succesfully reset!", "Password reset manager");
MessageBox.Show("Your password has been succesfully reset!", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
//Se afiseaza mesajul doar daca codul introdus nu se potriveste cu cel generat nu si in situatia in care
//utilizatorul selecteaza optiunea Cancel sau inchide fereastra
if (!"".Equals(userInputConfirmationCode))
{
//MessageBox.Show("Invalid confirmation code! Please try again.", "Password reset manager");
MessageBox.Show("Invalid confirmation code! Please try again.", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
//MessageBox.Show("Invalid username!", "Password reset manager");
MessageBox.Show("Invalid username!", "Password reset manager", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
TextBox[] textBoxes = new TextBox[] { newPasswordTextBox, confirmPasswordTextBox };
clearInputFields(textBoxes);
resetPasswordButton.Enabled = false;
}
