private String sqlStatementGetDebtorID = @"SELECT debtorID FROM debtors WHERE debtorName = @paramTypeName";//Modify the getID method of DataInsertionUtils class to use a more generic record ID retrieval method from the SQLCommandBuilder class
public int execute(QueryData paramContainer)
{
Guard.notNull(paramContainer, "parameter container");
int executionResult = -1;
//Retrieving the ID of the selected debtor
int debtorID = DataInsertionUtils.getID(sqlStatementGetDebtorID, paramContainer.DebtorName);
//Creating a new param container object that contains all the values from the param container received as argument plus the debtor ID(which will be necessary for inserting the new receivable record)
QueryData updatedParamContainer = new QueryData.Builder(paramContainer.UserID)
.addItemName(paramContainer.ItemName)
.addItemValue(paramContainer.ItemValue)
.addDebtorName(paramContainer.DebtorName)
.addDebtorID(debtorID)
.addStartDate(paramContainer.StartDate)
.addEndDate(paramContainer.EndDate)
.addIncomeSource(paramContainer.IncomeSource)
.addPaidAmount(paramContainer.PaidAmount)
.build();
MySqlCommand insertReceivableCommand = SQLCommandBuilder.getReceivableInsertionCommand(sqlStatementInsertReceivable, updatedParamContainer);
executionResult = DBConnectionManager.insertData(insertReceivableCommand);
return(executionResult);
}
public int execute(QueryData paramContainer)
{
//Data retrieval from the container object
IncomeSource incomeSource = paramContainer.IncomeSource;
int userID = paramContainer.UserID;
String expenseName = paramContainer.ItemName;
int expenseTypeID = DataInsertionUtils.getID(sqlStatementSelectExpenseTypeID, paramContainer.TypeName);
int expenseValue = paramContainer.ItemValue;
String expenseDate = paramContainer.ItemCreationDate;
int executionResult = -1;
MySqlCommand expenseInsertionCommand = null;
if (incomeSource == IncomeSource.GENERAL_INCOMES)
{
expenseInsertionCommand = SQLCommandBuilder.getInsertCommandForMultipleTypeItem(sqlStatementInsertGeneralIncomesExpense, userID, expenseName, expenseTypeID, expenseValue, expenseDate);
executionResult = DBConnectionManager.insertData(expenseInsertionCommand);;//Uses the SQL statement that inserts the expense in the expenses table of the DB
}
else if (incomeSource == IncomeSource.SAVING_ACCOUNT)
{
expenseInsertionCommand = SQLCommandBuilder.getInsertCommandForMultipleTypeItem(sqlStatementInsertSavingAccountExpense, userID, expenseName, expenseTypeID, expenseValue, expenseDate);
executionResult = DBConnectionManager.insertData(expenseInsertionCommand);//Uses SQL statement that inserts the expense in the saving_account_expenses table of the DB
}
return(executionResult);
}
public int execute(QueryData paramContainer)
{
int executionResult = -1;
//Checks if the entered debtor name exists in the database
MySqlCommand debtorSelectionCommand = new MySqlCommand(sqlStatementCheckDebtorExistence);
debtorSelectionCommand.Parameters.AddWithValue("@paramDebtorName", paramContainer.DebtorName);
if (DataInsertionUtils.entryIsPresent(debtorSelectionCommand, paramContainer.DebtorName))
{
DialogResult userChoice = MessageBox.Show("The provided debtor name already exists. Do you want to add it to your debtors list?", "Data insertion", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (userChoice == DialogResult.Yes)
{
//Checks if the debtor is already present in the current user debtor's list
MySqlCommand debtorPresenceInListCommand = new MySqlCommand(sqlStatementCheckDebtorExistenceInUserList);
debtorPresenceInListCommand.Parameters.AddWithValue("@paramUserID", paramContainer.UserID);
debtorPresenceInListCommand.Parameters.AddWithValue("@paramDebtorID", DataInsertionUtils.getID(sqlStatementSelectDebtorID, paramContainer.DebtorName));//Looks for the id of the debtor whose name was inserted
if (DataInsertionUtils.isAssignedToCurrentUser(debtorPresenceInListCommand))
{
MessageBox.Show("The provided debtor is already present in your debtor list and cannot be assigned again! Please enter a different debtor", "Data insertion", MessageBoxButtons.OK, MessageBoxIcon.Warning);
//return executionResult;
}
else
{
//If the debtor already exists but it's not assigned to the current user a new entry will be created in the users_debtors table of the database
MySqlCommand debtorIDInsertCommandForExistingEntry = new MySqlCommand(sqlStatementInsertDebtorID);
debtorIDInsertCommandForExistingEntry.Parameters.AddWithValue("@paramUserID", paramContainer.UserID);
debtorIDInsertCommandForExistingEntry.Parameters.AddWithValue("@paramDebtorID", DataInsertionUtils.getID(sqlStatementSelectDebtorID, paramContainer.DebtorName));
executionResult = DBConnectionManager.insertData(debtorIDInsertCommandForExistingEntry);
}
}
else
{
//If the user option is 'No' we return from the method
return(executionResult);
}
}
else
{
//Inserting a new debtor in the debtors table of the database
MySqlCommand debtorInsertCommand = new MySqlCommand(sqlStatementInsertDebtor);
debtorInsertCommand.Parameters.AddWithValue("@paramDebtorName", paramContainer.DebtorName);
executionResult = DBConnectionManager.insertData(debtorInsertCommand);
//Checks if the insertion in the debtortable of the database was successfull and if not returns the value of the executionResult(which will be -1) so that the user will know that something went wrong during the process
if (executionResult == -1)
{
return(executionResult);
}
//Inserting the ID of the newly created creditor in the users_creditors table of the database
MySqlCommand debtorIDInsertCommand = new MySqlCommand(sqlStatementInsertDebtorID);
debtorIDInsertCommand.Parameters.AddWithValue("@paramUserID", paramContainer.UserID);
debtorIDInsertCommand.Parameters.AddWithValue("@paramDebtorID", DataInsertionUtils.getID(sqlStatementSelectDebtorID, paramContainer.DebtorName));
executionResult = DBConnectionManager.insertData(debtorIDInsertCommand);
}
return(executionResult);
}
public int execute(QueryData paramContainer)
{
//Getting the necessary data
int userID = paramContainer.UserID;
String debtName = paramContainer.ItemName;
int debtValue = paramContainer.ItemValue;
int creditorID = DataInsertionUtils.getID(sqlStatementSelectCreditorID, paramContainer.CreditorName);
String debtDate = paramContainer.ItemCreationDate;
//Creating command for debt insertion
MySqlCommand debtInsertionCommand = SQLCommandBuilder.getDebtInsertionCommand(sqlStatementInsertDebt, userID, debtName, debtValue, creditorID, debtDate);
int executionResult = DBConnectionManager.insertData(debtInsertionCommand);
return(executionResult);
}
