public static MySqlCommand getReceivableInsertionCommand(String sqlStatement, QueryData paramContainer)
{
Guard.notNull(paramContainer, "parameter container");
MySqlCommand insertReceivableCommand = new MySqlCommand(sqlStatement);
insertReceivableCommand.Parameters.AddWithValue("@paramItemName", paramContainer.ItemName);
insertReceivableCommand.Parameters.AddWithValue("@paramItemValue", paramContainer.ItemValue);
insertReceivableCommand.Parameters.AddWithValue("@paramDebtorID", paramContainer.DebtorID);
insertReceivableCommand.Parameters.AddWithValue("@paramUserID", paramContainer.UserID);
insertReceivableCommand.Parameters.AddWithValue("@paramPaidAmount", paramContainer.PaidAmount);
insertReceivableCommand.Parameters.AddWithValue("@paramStartDate", paramContainer.StartDate);
insertReceivableCommand.Parameters.AddWithValue("@paramEndDate", paramContainer.EndDate);
return(insertReceivableCommand);
}
public int requestUpdate(QueryType option, QueryData paramContainer, DataTable sourceDataTable)
{
int executionResult = model.updateData(option, paramContainer, sourceDataTable);
return(executionResult);
}
//Method for creating commands with a single parameter representing the user ID
public static MySqlCommand getSpecificUserRecordsCommand(String sqlStatement, QueryData paramContainer)
{
Guard.notNull(sqlStatement, "SQL statement");
Guard.notNull(paramContainer, "parameter container");
MySqlCommand getSpecificUserRecordsCommand = new MySqlCommand(sqlStatement);
getSpecificUserRecordsCommand.Parameters.AddWithValue("@paramID", paramContainer.UserID);
return(getSpecificUserRecordsCommand);
}
//Method for creating a command that will insert or update a record in the balance record table
public static MySqlCommand getBalanceRecordInsertUpdateCommand(String sqlStatement, QueryData paramContainer)
{
Guard.notNull(sqlStatement, "SQL statement");
Guard.notNull(paramContainer, "parameter container");
MySqlCommand balanceRecordInsertionCommand = new MySqlCommand(sqlStatement);
balanceRecordInsertionCommand.Parameters.AddWithValue("@paramID", paramContainer.UserID);
balanceRecordInsertionCommand.Parameters.AddWithValue("@paramRecordName", paramContainer.ItemName);
balanceRecordInsertionCommand.Parameters.AddWithValue("@paramValue", paramContainer.ItemValue);
balanceRecordInsertionCommand.Parameters.AddWithValue("paramMonth", paramContainer.Month);
balanceRecordInsertionCommand.Parameters.AddWithValue("paramYear", paramContainer.Year);
return(balanceRecordInsertionCommand);
}
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);
}
}
private void submitButton_Click(object sender, EventArgs e)
{
//Checks if the timespan selection combobxes are selected before the Submit button is pressed
if (monthRecordsCheckBox.Checked == false && yearRecordsCheckBox.Checked == false)
{
MessageBox.Show("Please select a time interval first!", "Update data", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//Displaying a message asking the user to confirm his intention of modifying the data and getting the result of his selection
DialogResult userOption = MessageBox.Show("Are you sure that you want to submit the changes to the database?", "Data update form", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (userOption == DialogResult.No)
{
return;
}
//Getting the currently selected table name
String tableName = tableSelectionComboBox.SelectedItem.ToString();
QueryType option = 0;
QueryData paramContainer = null;
//Getting the necessary data for restoring the SQL query used for displaying the data present in the table
//(for generating the commands that will automatically update th DB with the changes that the user made in the GUI)
if (monthRecordsCheckBox.Checked == true)
{
option = QueryType.SINGLE_MONTH;
int currentMonth = dateTimePickerTimeSpanSelection.Value.Month;
int currentYear = dateTimePickerTimeSpanSelection.Value.Year;
paramContainer = new QueryData.Builder(userID).addMonth(currentMonth).addYear(currentYear).addTableName(tableName).build(); //CHANGE
}
else if (yearRecordsCheckBox.Checked == true)
{
option = QueryType.FULL_YEAR;
int currentMonth = dateTimePickerTimeSpanSelection.Value.Month;
int currentYear = dateTimePickerTimeSpanSelection.Value.Year;
paramContainer = new QueryData.Builder(userID).addYear(currentYear).addTableName(tableName).build(); //CHANGE
}
//Getting the data source of the table displayed in the GUI
DataTable sourceDataTable = (DataTable)dataGridViewTableDisplay.DataSource;
//Sending data to controller and checking the execution result
int executionResult = controller.requestUpdate(option, paramContainer, sourceDataTable);
if (executionResult != -1)
{
MessageBox.Show("The selected data was updated successfully!", "Update data", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Unable to update the selected data! Please try again.", "Update data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//Balance record update section
BudgetItemType selectedItemType = getSelectedBudgetItemType();
if (selectedItemType == BudgetItemType.SAVING_ACCOUNT_EXPENSE || selectedItemType == BudgetItemType.SAVING)
{
//If only the record value is changed by the user then the new record value is set to it
if (hasChangedRecordDate && !hasChangedRecordValue)
{
//CHANGE
//Takes the value from the actual changed row not from the currently selected one which can be different.
//If a saving account expense is selected the value is taken from the column 3 of the row and if a saving is selected the value is taken from column 2 of the row.
//newRecordValue = selectedItemType == BudgetItemType.SAVING_ACCOUNT_EXPENSE ? getValueFromRow(changedRowIndex, 3) : getValueFromRow(changedRowIndex, 2);
//CHANGE-DGW MANAGEMENT
newRecordValue = selectedItemType == BudgetItemType.SAVING_ACCOUNT_EXPENSE ? gridViewManager.getSingleItemValueFromRow(changedRowIndex, 3) : gridViewManager.getSingleItemValueFromRow(changedRowIndex, 2);
}
int month = 0;
int year = 0;
DateTime selectedRecordDate = DateTime.MinValue;//CHANGE
//If the record date was modified then the month and year variables will be assigned the corresponding values from the old date otherwise the current date of the record will be used
if (hasChangedRecordDate)
{
month = oldRecordDate.Month;
year = oldRecordDate.Year;
selectedRecordDate = oldRecordDate;//Selected record date will be the old record date since we need to save the date that existed previous to the change
}
else
{
//CHANGE
//Takes the value from the actual changed row not from the currently selected one which can be different.
//If a saving account expense is selected the date is taken from the column 4 of the row and if a saving is selected the date is taken from column 3 of the row.
//selectedRecordDate = selectedItemType == BudgetItemType.SAVING_ACCOUNT_EXPENSE ? getDateFromRow(changedRowIndex, 4) : getDateFromRow(changedRowIndex, 3);
//CHANGE-DGW MANAGEMENT
selectedRecordDate = selectedItemType == BudgetItemType.SAVING_ACCOUNT_EXPENSE ? gridViewManager.getDateFromRow(changedRowIndex, 4) : gridViewManager.getDateFromRow(changedRowIndex, 3);
month = selectedRecordDate.Month;
year = selectedRecordDate.Year;
}
//Retrieving the execution result of the method responsible for updating the balance record table
//TEST TRIGGER DB
//int savingAccountBalanceUpdateResult = updateSavingAccountBalanceTable(userID, month, year, selectedRecordDate, getSelectedBudgetItemType(), false);
//if (savingAccountBalanceUpdateResult == -1) {
// MessageBox.Show("Unable to update the saving account balance record.", "Data update", MessageBoxButtons.OK, MessageBoxIcon.Error);
//}
}
submitButton.Enabled = false; //Disables the Submit button after deleting data from the table
deleteButton.Enabled = false; //Disables the Delete button after deleting data from the table
clearFlags();
resetSavedValues();
}
private void deleteButton_Click(object sender, EventArgs e)
{
//Getting the selected row index
int selectedRowIndex = dataGridViewTableDisplay.CurrentCell.RowIndex;
//The value of the deleted record and its date are saved before the actual deletion takes place because otherwise they would be lost and the update of the corresponding saving account balance record would be impossible
int selectedRowForDeletionIndex = dataGridViewTableDisplay.CurrentCell.RowIndex;
//Retrieving the correct column indexes from which data will be extracted
int[] columnIndexList = getColumnIndexList();
//deletedRecordValue = getValueFromRow(selectedRowForDeletionIndex, columnIndexList[0]);
//CHANGE-DGW MANAGEMENT
deletedRecordValue = gridViewManager.getSingleItemValueFromRow(selectedRowForDeletionIndex, columnIndexList[0]);
//deletedRecordDate = getDateFromRow(selectedRowForDeletionIndex, columnIndexList[1]);
//CHANGE-DGW MANAGEMENT
deletedRecordDate = gridViewManager.getDateFromRow(selectedRowForDeletionIndex, columnIndexList[1]);
String confirmationMessage = String.Format("Are you sure that you want to delete row number {0}?", selectedRowIndex);
DialogResult userOption1 = MessageBox.Show(confirmationMessage, "Data update form", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (userOption1 == DialogResult.No)
{
return;
}
//CHANGE-DGW MANAGEMENT
if (gridViewManager.hasChangedRows())
{
MessageBox.Show("You cannot delete the selected row before submitting or discarding the currently pending change/s!", "Data update", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//Getting the currently selected table name
String tableName = tableSelectionComboBox.Text;
//Getting the ID of the selected record from the DataTable object that represents the data source of the table displayed in the GUI
CurrencyManager currencyManager = (CurrencyManager)dataGridViewTableDisplay.BindingContext[dataGridViewTableDisplay.DataSource, dataGridViewTableDisplay.DataMember];
DataRowView selectedDataRow = (DataRowView)currencyManager.Current;
int itemID = selectedDataRow.Row.ItemArray[0] != null?Convert.ToInt32(selectedDataRow.Row.ItemArray[0]) : -1;
//CHANGE!!!!
QueryType option = 0;
QueryData paramContainer = null;
if (monthRecordsCheckBox.Checked == true)
{
option = QueryType.SINGLE_MONTH;
int currentMonth = dateTimePickerTimeSpanSelection.Value.Month;
int currentYear = dateTimePickerTimeSpanSelection.Value.Year;
paramContainer = new QueryData.Builder(userID).addMonth(currentMonth).addYear(currentYear).addTableName(tableName).build(); //CHANGE
}
else if (yearRecordsCheckBox.Checked == true)
{
option = QueryType.FULL_YEAR;
int currentMonth = dateTimePickerTimeSpanSelection.Value.Month;
int currentYear = dateTimePickerTimeSpanSelection.Value.Year;
paramContainer = new QueryData.Builder(userID).addYear(currentYear).addTableName(tableName).build(); //CHANGE
}
//Retrieves the DataTable object representing the data source of the DataGridView
DataTable sourceDataTable = (DataTable)dataGridViewTableDisplay.DataSource;
sourceDataTable.Rows[selectedRowIndex].Delete();//Deletes the row from the DataTable object
int executionResult = controller.requestDelete(option, paramContainer, sourceDataTable);
//Displaying info message regarding the delete operation result
if (executionResult != -1)
{
MessageBox.Show("The selected data was successfully deleted!", "Data update", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Unable to delete the selected data! Please try again.", "Data update", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//Balance record update section
//BudgetItemType selectedItemType = getSelectedBudgetItemType();
//if (selectedItemType == BudgetItemType.SAVING_ACCOUNT_EXPENSE || selectedItemType == BudgetItemType.SAVING) {
// int month = deletedRecordDate.Month;
// int year = deletedRecordDate.Year;
// int savingAccountBalanceUpdateResult = updateSavingAccountBalanceTable(userID, month, year, deletedRecordDate, getSelectedBudgetItemType(), true);
// if (savingAccountBalanceUpdateResult == -1) {
// MessageBox.Show("Unable to update the saving account balance record", "Data update", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// }
//}
submitButton.Enabled = false; //Disables the Submit button after deleting data from the table
deleteButton.Enabled = false; //Disables the Delete button after deleting data from the table
clearFlags();
resetSavedValues();
}