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));
}
//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));
}
//Creaza comanda SQL de actualizarea a datelor in baza de date utilizand ca parametri un sir de bytes, hashcode-ul parolei si id-ul usrrului care cere resetarea parolei
private int updatePassword(byte[] inputSalt, String inputHash, int userID)
{
MySqlCommand updatePasswordCommand = SQLCommandBuilder.getUpdatePasswordCommand(sqlStatementUpdatePassword, inputSalt, inputHash, userID);
//Trimite comanda pt a fi executata de metoda specializata a clasei DBConnectionManager
int executionResult = DBConnectionManager.insertData(updatePasswordCommand);
return(executionResult);
}
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));
}
private void registerButton_Click(object sender, EventArgs e)
{
string userName = userNameTextBox.Text;
string password = passwordTextBox.Text;
string emailAddress = emailTextBox.Text;
if (!isValidUserName(userName))
{
MessageBox.Show("The username must have at least 3 characters and can contain only lowercase(a-z) and uppercase(A-Z) letters, digits(0-9) and underscores(_)!", "User registration", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (password.Length < minimumPasswordLength)
{
MessageBox.Show("Your password should be at least 10 characters long! Please try again.", "User registration", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (!isValidPassword(password))
{
MessageBox.Show("Invalid password! Your password must contain:\n1.Lowercase and uppercase letters (a-zA-z) \n2.Digits (0-9) \n3.Special characters (@#$%<>?)", "User registration", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (!isValidEmail(emailAddress))
{
MessageBox.Show("Invalid email address!", "User registration", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (userExists(getUser(sqlStatementCheckUserExistence, userName)))
{
MessageBox.Show("The selected username already exists! Please try again", "User registration", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
ConfirmationSender emailSender = new ConfirmationSender();
string emailSubject = "New user creation";
string emailBody = "A user creation request was made for an account that will associated to this email address.\nPlease enter the following code to finish user creation process and confirm your email: {0} \nIf you have not made such a request please ignore this email and delete it.";
string onSuccessMessage = "An email containing the confirmation code for the new user creation was sent to the specified email address";
string parentWindowName = "Register";
string generatedConfirmationCode = emailSender.generateConfirmationCode();
emailSender.sendConfirmationEmail(emailAddress, emailSubject, emailBody, generatedConfirmationCode, onSuccessMessage, parentWindowName);
String userInputConfirmationCode = Interaction.InputBox("Enter the code received on your email to finish the user creation process:", "Confirmation Code", "Enter code", 200, 200);
if (emailSender.confirmationCodesMatch(generatedConfirmationCode, userInputConfirmationCode))
{
PasswordSecurityManager securityManager = new PasswordSecurityManager();
byte[] salt = securityManager.getSalt(16);
string hashCode = securityManager.createPasswordHash(password, salt);
MySqlCommand userCreationCommand = SQLCommandBuilder.getNewUserCreationCommand(sqlStatementCreateNewUser, userName, salt, hashCode, emailAddress);
int executionResult = DBConnectionManager.insertData(userCreationCommand);
if (executionResult == -1)
{
MessageBox.Show("Could not create the requested user!", "Register", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
MessageBox.Show("Your user was succesfully created!", "Register", MessageBoxButtons.OK, MessageBoxIcon.Information);
clearInputFields(textBoxes);
registerButton.Enabled = false;
}
else
{
MessageBox.Show("Invalid confirmation code! Please try again.", "Register", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private MySqlCommand getCorrectSqlCommandForDataDisplay(QueryType option, QueryData paramContainer)
{
//Retrieving data from the Querydata object(container object)
int userID = paramContainer.UserID;
String tableName = paramContainer.TableName;
int selectedMonth = 0;
//If single month data is requested then the value of the month from the QueryData object will be retrieved
if (option == QueryType.SINGLE_MONTH)
{
selectedMonth = paramContainer.Month;
}
int selectedYear = paramContainer.Year;
switch (tableName)
{
//Creates the correct SQL command based on the dateTimePicker selection(single month command/full year command)
case "Incomes":
if (option == QueryType.SINGLE_MONTH)
{
return(SQLCommandBuilder.getSingleMonthCommand(sqlStatementSelectSingleMonthIncomes, new QueryData.Builder(userID).addMonth(selectedMonth).addYear(selectedYear).build()));
}
else if (option == QueryType.FULL_YEAR)
{
return(SQLCommandBuilder.getFullYearRecordsCommand(sqlStatementSelectFullYearIncomes, new QueryData.Builder(userID).addYear(selectedYear).build())); //CHANGE
}
else
{
return(null);
}
case "General expenses":
if (option == QueryType.SINGLE_MONTH)
{
return(SQLCommandBuilder.getSingleMonthCommand(sqlStatementSelectSingleMonthGeneralExpenses, new QueryData.Builder(userID).addMonth(selectedMonth).addYear(selectedYear).build()));
}
else if (option == QueryType.FULL_YEAR)
{
return(SQLCommandBuilder.getFullYearRecordsCommand(sqlStatementSelectFullYearGeneralExpenses, new QueryData.Builder(userID).addYear(selectedYear).build()));
}
else
{
return(null);
}
case "Saving accounts expenses":
if (option == QueryType.SINGLE_MONTH)
{
return(SQLCommandBuilder.getSingleMonthCommand(sqlStatementSelectSingleMonthSavingAccountExpenses, new QueryData.Builder(userID).addMonth(selectedMonth).addYear(selectedYear).build()));
}
else if (option == QueryType.FULL_YEAR)
{
return(SQLCommandBuilder.getFullYearRecordsCommand(sqlStatementSelectFullYearSavingAccountExpenses, new QueryData.Builder(userID).addYear(selectedYear).build()));
}
else
{
return(null);
}
case "Debts":
if (option == QueryType.SINGLE_MONTH)
{
return(SQLCommandBuilder.getSingleMonthCommand(sqlStatementSelectSingleMonthDebts, new QueryData.Builder(userID).addMonth(selectedMonth).addYear(selectedYear).build()));
}
else if (option == QueryType.FULL_YEAR)
{
return(SQLCommandBuilder.getFullYearRecordsCommand(sqlStatementSelectFullYearDebts, new QueryData.Builder(userID).addYear(selectedYear).build()));
}
else
{
return(null);
}
case "Savings":
if (option == QueryType.SINGLE_MONTH)
{
return(SQLCommandBuilder.getSingleMonthCommand(sqlStatementSelectSingleMonthSavings, new QueryData.Builder(userID).addMonth(selectedMonth).addYear(selectedYear).build()));
}
else if (option == QueryType.FULL_YEAR)
{
return(SQLCommandBuilder.getFullYearRecordsCommand(sqlStatementSelectFullYearSavings, new QueryData.Builder(userID).addYear(selectedYear).build()));
}
else
{
return(null);
}
default:
return(null);
}
}
