public int[] getTotalValuesForAllBudgetItems(String startDate, String endDate)
{
//Arguments checks
if (startDate == null || endDate == null)
{
return(null);
}
if ("".Equals(startDate) || "".Equals(endDate))
{
return(null);
}
//Data container object and MySqlCommand creation
QueryData paramContainer = new QueryData.Builder(userID).addStartDate(startDate).addEndDate(endDate).build();
MySqlCommand getTotalValuesForAllItemsCommand = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementGetTotalValuesForAllItems, paramContainer);
DataTable itemsTotalValuesDataTable = DBConnectionManager.getData(getTotalValuesForAllItemsCommand);
//Null and row count check on the resulted DataTable object
if (itemsTotalValuesDataTable == null || itemsTotalValuesDataTable.Rows.Count <= 0)
{
return(null);
}
//DataTable values conversion to int
int expensesTotalValue = itemsTotalValuesDataTable.Rows[0].ItemArray[0] != DBNull.Value ? Convert.ToInt32(itemsTotalValuesDataTable.Rows[0].ItemArray[0]) : 0;
int debtsTotalValue = itemsTotalValuesDataTable.Rows[0].ItemArray[1] != DBNull.Value ? Convert.ToInt32(itemsTotalValuesDataTable.Rows[0].ItemArray[1]) : 0;
int savingsTotalValue = itemsTotalValuesDataTable.Rows[0].ItemArray[2] != DBNull.Value ? Convert.ToInt32(itemsTotalValuesDataTable.Rows[0].ItemArray[2]) : 0;
//Creating the array containing the total values for each budget item
int[] budgetItemsTotals = new int[] { expensesTotalValue, debtsTotalValue, savingsTotalValue };
return(budgetItemsTotals);
}
//Method that gets the total value of the selected element for the specified month
private int getTotalValueForSelectedElement(BudgetItemType itemType, String sqlStatement, QueryData paramContainer)
{
int totalValue = 0;
//Getting the correct SQL comand for the selected element
MySqlCommand command = getCommand(itemType, sqlStatement, paramContainer);
if (command == null)
{
return(-1);
}
//Getting the data based on the previously created command
DataTable resultDataTable = DBConnectionManager.getData(command);
//Checking if the DataTable contains data and if so converting the value to int
if (resultDataTable != null && resultDataTable.Rows.Count == 1)
{
Object result = resultDataTable.Rows[0].ItemArray[0];
totalValue = result != DBNull.Value ? Convert.ToInt32(result) : 0;
return(totalValue);
}
return(-1);
}
private DataTable retrieveData(String sqlStatement)
{
Guard.notNull(sqlStatement, "data retrieval sqlStatement");
MySqlCommand retrieveDataCommand = new MySqlCommand(sqlStatement);
DataTable retrievedData = DBConnectionManager.getData(retrieveDataCommand);
return(retrievedData);
}
//Method for checking if the specified creditor/debtor is present in the user's creditor/debtor list(users_creditors or user_debtors table of the database)
public static bool isAssignedToCurrentUser(MySqlCommand command)
{
DataTable assignmentListTable = DBConnectionManager.getData(command);
if (assignmentListTable != null && assignmentListTable.Rows.Count > 0)
{
return(true);
}
return(false);
}
//Method for checking if the specified creditor is present in the user's creditor list
private bool isPresentInUserCreditorList(MySqlCommand command)
{
DataTable creditorListPresenceTable = DBConnectionManager.getData(command);
if (creditorListPresenceTable != null && creditorListPresenceTable.Rows.Count > 0)
{
return(true);
}
return(false);
}
private DataTable retrieveData(String sqlStatement, int userID)
{
Guard.notNull(sqlStatement, "data retrieval sqlStatement");
MySqlCommand retrieveDataCommand = new MySqlCommand(sqlStatement);
retrieveDataCommand.Parameters.AddWithValue("@paramUserID", userID);
DataTable retrievedData = DBConnectionManager.getData(retrieveDataCommand);
return(retrievedData);
}
private bool hasPlanForCurrenMonthSelection(int userID, int month)
{
int year = DateTime.Now.Year;
int day = 1;
//Creates a DateTime object representing the first day of the selected month from the current year
DateTime startDate = new DateTime(year, month, day);
if (oneMonthCheckBox.Checked == true)
{
//Creates a data container which will be passed to the MySqlCommand builder method(the date is transformed into a string having the format required by the MySql database)
QueryData paramContainer = new QueryData.Builder(userID).addStartDate(startDate.ToString("yyyy-MM-dd")).build(); //CHANGE
MySqlCommand budgetPlanStartDateCheckCommand = SQLCommandBuilder.getBudgetPlanCheckCommand(sqlStatementCheckBudgetPlanExistence, paramContainer);
//Executes a MySqlCommand for checking the existence of a budget plan for the selected interval
DataTable budgetPlanDataTable = DBConnectionManager.getData(budgetPlanStartDateCheckCommand);
//The DataTable object is checked to see if it contains any results
if (budgetPlanDataTable != null && budgetPlanDataTable.Rows.Count > 0)
{
return(true);
}
}
else if (sixMonthsCheckBox.Checked == true)
{
//Gets the last month of the interval
int endMonth = month + 6;
//Gets the last day of the month
int lastDayOfEndMonth = DateTime.DaysInMonth(year, endMonth);
DateTime endDate = new DateTime(year, endMonth, lastDayOfEndMonth);
//SQL commands are created for the start month and end month of the interval
QueryData paramContainerStartDate = new QueryData.Builder(userID).addStartDate(startDate.ToString("yyyy-MM-dd")).build(); //CHANGE
MySqlCommand budgetPlanStartDateCheckCommand = SQLCommandBuilder.getBudgetPlanCheckCommand(sqlStatementCheckBudgetPlanExistence, paramContainerStartDate);
QueryData paramContainerEndDate = new QueryData.Builder(userID).addEndDate(endDate.ToString("yyyy-MM-dd")).build();//CHANGE
MySqlCommand budgetPlanEndDateCheckCommand = SQLCommandBuilder.getBudgetPlanCheckCommand(sqlStatementCheckBudgetPlanExistence, paramContainerEndDate);
//The commands are executed against the DB
DataTable budgetPlanDataTableStart = DBConnectionManager.getData(budgetPlanStartDateCheckCommand);
DataTable budgetPlanDataTableEnd = DBConnectionManager.getData(budgetPlanEndDateCheckCommand);
//If the start month or the end month is present in the interval specified by an existing budget plan then it means that the two plans overlap and the new plan will not be created.
if (budgetPlanDataTableStart != null && budgetPlanDataTableStart.Rows.Count > 0 || budgetPlanDataTableEnd != null && budgetPlanDataTableEnd.Rows.Count > 0)
{
return(true);
}
}
return(false);
}
//Method that retrieves the budget plan data(if the plan exists) based on the specified currentDate(it is actually the date that the user selects for the new entry which is checked to see if it overlaps any existing budget plan timespan)
public DataTable getBudgetPlanData()
{
QueryData paramContainer = new QueryData.Builder(userID).addStartDate(currentDate).build();
MySqlCommand budgetPlanExistenceCheckCommand = SQLCommandBuilder.getBudgetPlanCheckCommand(sqlStatementCheckBudgetPlanExistence, paramContainer);
DataTable budgetPlanDataTable = DBConnectionManager.getData(budgetPlanExistenceCheckCommand);
if (budgetPlanDataTable != null && budgetPlanDataTable.Rows.Count == 1)
{
return(budgetPlanDataTable);
}
return(null);
}
//Method for retrieving the ID for the selected budget plan type
private int getBudgetTypeID(String budgetPlanTypeName)
{
MySqlCommand getTypeIDCommand = SQLCommandBuilder.getTypeIDForItemCommand(sqlStatementGetBudgetPlanTypeID, budgetPlanTypeName); //CHANGE
DataTable typeIDDataTable = DBConnectionManager.getData(getTypeIDCommand);
if (typeIDDataTable != null && typeIDDataTable.Rows.Count == 1)
{
//Null check for the retrieved data(if the element at index 0 of the ItemArray is null then the typeID variable is assigned -1 value)
int typeID = typeIDDataTable.Rows[0].ItemArray[0] != DBNull.Value ? Convert.ToInt32(typeIDDataTable.Rows[0].ItemArray[0]) : -1;
return(typeID);
}
return(-1);
}
private int getID(String sqlStatement, String typeName)
{
MySqlCommand getTypeIDCommand = new MySqlCommand(sqlStatement);
getTypeIDCommand.Parameters.AddWithValue("@paramTypeName", typeName);
DataTable typeIDTable = DBConnectionManager.getData(getTypeIDCommand);
if (typeIDTable != null && typeIDTable.Rows.Count == 1)
{
int typeID = Convert.ToInt32(typeIDTable.Rows[0].ItemArray[0]);
return(typeID);
}
return(-1);
}
//Method for retrieving the record value from the saving account balance table
public int getRecordValue()
{
int recordValue = -1;
QueryData paramContainer = new QueryData.Builder(userID).addMonth(balanceRecordMonth).addYear(balanceRecordYear).build();
MySqlCommand recordRetrievalCommand = SQLCommandBuilder.getSingleMonthCommand(sqlStatementCheckRecordExistence, paramContainer);
DataTable resultDataTable = DBConnectionManager.getData(recordRetrievalCommand);
if (resultDataTable != null && resultDataTable.Rows.Count == 1)
{
Object valueObject = resultDataTable.Rows[0].ItemArray[3];
recordValue = valueObject != DBNull.Value ? Convert.ToInt32(valueObject) : -1;
}
return(recordValue);
}
//Method for retrieving the total value for the item that the user wants to insert into the DB(for the period between the start and end date of the budget plan)
public int getTotalValueForSelectedItem(BudgetItemType itemType, String startDate, String endDate)
{
MySqlCommand getSelectedItemTotalValueCommand = getCorrectSqlCommand(itemType, startDate, endDate);
if (getSelectedItemTotalValueCommand == null)
{
return(-1);
}
DataTable itemTotalValueDataTable = DBConnectionManager.getData(getSelectedItemTotalValueCommand);
if (itemTotalValueDataTable != null && itemTotalValueDataTable.Rows.Count == 1)
{
int totalItemValue = itemTotalValueDataTable.Rows[0].ItemArray[0] != DBNull.Value ? Convert.ToInt32(itemTotalValueDataTable.Rows[0].ItemArray[0]) : 0;
return(totalItemValue);
}
return(-1);
}
//Method for checking if adding the user input value to the total value of the selected item would result in exceeding the imposed limit
public bool exceedsItemLimitValue(int userInputValue, int limitValue, BudgetItemType itemType, String startDate, String endDate)
{
//Converts the date strings into the format required by the MySqL database
String sqlFormatStartDate = DateTime.Parse(startDate).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
String sqlFormatEndDate = DateTime.Parse(endDate).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
//Creates the command that will retrieve the the total value of the item (expense debt, saving) up to the current date
MySqlCommand totalItemValueCommand = getCorrectSqlCommand(itemType, sqlFormatStartDate, sqlFormatEndDate);
DataTable totalItemValueDataTable = DBConnectionManager.getData(totalItemValueCommand);
//The default value for the total item value is set to 0 so that it does not affect the calculations in case there are no expense record for the current time interval
int totalItemValue = 0;
if (totalItemValueDataTable != null && totalItemValueDataTable.Rows.Count == 1)
{
totalItemValue = totalItemValueDataTable.Rows[0].ItemArray[0] != DBNull.Value ? Convert.ToInt32(totalItemValueDataTable.Rows[0].ItemArray[0]) : 0;
}
return(totalItemValue + userInputValue > limitValue);
}
//Method for retrieving the total saving amount
private int getSavingAccountCurrentBalance(String sqlStatement, QueryData paramContainer)
{
//Setting the default value for current balance.If data cannot be retrieved for any reason then 0 will be returned since it is not be allowed for the saving account to have negative balance
int currentBalance = 0;
MySqlCommand getCurrentBalanceCommand = SQLCommandBuilder.getRecordSumValueCommand(sqlStatementGetSavingAccountBalance, paramContainer);
//getCurrentBalanceCommand.Parameters.AddWithValue("@paramID", paramContainer.UserID);
//getCurrentBalanceCommand.Parameters.AddWithValue("@paramYear", paramContainer.Year);
DataTable resultDataTable = DBConnectionManager.getData(getCurrentBalanceCommand);
if (resultDataTable != null && resultDataTable.Rows.Count == 1)
{
Object result = resultDataTable.Rows[0].ItemArray[0];
currentBalance = result != DBNull.Value ? Convert.ToInt32(result) : 0;
return(currentBalance);
}
return(currentBalance);
}
//Method for retrieving the total income value for the specified interval
public int getTotalIncomes(String startDate, String endDate)
{
String sqlFormatStartDate = DateTime.Parse(startDate).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
String sqlFormatEndDate = DateTime.Parse(endDate).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
QueryData paramContainer = new QueryData.Builder(userID).addStartDate(sqlFormatStartDate).addEndDate(sqlFormatEndDate).build();
MySqlCommand getTotalIncomesCommand = SQLCommandBuilder.getMultipleMonthsCommand(sqlStatementGetTotalIncomes, paramContainer);
//The DataTable object that contains the total incomes value for the specified time interval
DataTable totalIncomesDataTable = DBConnectionManager.getData(getTotalIncomesCommand);
if (totalIncomesDataTable != null && totalIncomesDataTable.Rows.Count == 1)
{
int totalIncomes = totalIncomesDataTable.Rows[0].ItemArray[0] != DBNull.Value ? Convert.ToInt32(totalIncomesDataTable.Rows[0].ItemArray[0]) : 0;
return(totalIncomes);
}
return(-1);
}
//Method for checking if the specififed creditor is present in the database
private bool entryIsPresent(MySqlCommand command, String entryName)
{
//Executes the data retrieval command using the name of the specified creditor
DataTable entryDataTable = DBConnectionManager.getData(command);
if (entryDataTable != null)
{
if (entryDataTable.Rows.Count > 0)
{
for (int i = 0; i < entryDataTable.Rows.Count; i++)
{
//Checks if the name of the creditor that was obtained after the execution of the command is the same as the one that the users tries to insert(case insensitive string comparison)
if (entryName.Equals(entryDataTable.Rows[i].ItemArray[0].ToString(), StringComparison.InvariantCultureIgnoreCase))
{
return(true);
}
}
}
}
return(false);
}
//Checks if there is any existing record for the specified month and year for the current user
public bool hasBalanceRecord()
{
if (balanceRecordMonth <= 0 || balanceRecordMonth > 12)
{
return(false);
}
if (balanceRecordYear <= 0)
{
return(false);
}
QueryData paramContainer = new QueryData.Builder(userID).addMonth(balanceRecordMonth).addYear(balanceRecordYear).build();
MySqlCommand recordExistenceCheckCommand = SQLCommandBuilder.getSingleMonthCommand(sqlStatementCheckRecordExistence, paramContainer);
DataTable resultDataTable = DBConnectionManager.getData(recordExistenceCheckCommand);
if (resultDataTable != null && resultDataTable.Rows.Count == 1)
{
return(true);
}
return(false);
}
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 = getCorrectSqlCommandForDataDisplay(option, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
break;
case SelectedDataSource.STATIC_DATASOURCE:
//command = getCorrectSqlCommandForDataDisplay(option, paramContainer);
break;
default:
break;
}
}
else if (option == QueryType.FULL_YEAR)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = getCorrectSqlCommandForDataDisplay(option, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
break;
case SelectedDataSource.STATIC_DATASOURCE:
//command = getCorrectSqlCommandForDataDisplay(option, paramContainer);
break;
default:
break;
}
//Budget plan option branch
}
else if (option == QueryType.BUDGET_PLAN_INFO)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = getCorrectSqlCommandForDataDisplay(option, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
break;
default:
break;
}
}
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 data source
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = getCorrectCommandForDataDisplay(option, paramContainer);
break;
//Column chart data source
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = getCorrectCommandForDataDisplay(option, paramContainer);
break;
//Current balance field data source
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getSpecificUserRecordsCommand(sqlStatementSavingAccountCurrentBalance, paramContainer);
break;
}
}
else if (option == QueryType.MULTIPLE_MONTHS)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
command = getCorrectCommandForDataDisplay(option, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
command = getCorrectCommandForDataDisplay(option, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getSpecificUserRecordsCommand(sqlStatementSavingAccountCurrentBalance, paramContainer);
break;
}
}
else if (option == QueryType.MONTHLY_TOTALS)
{
switch (dataSource)
{
case SelectedDataSource.DYNAMIC_DATASOURCE_1:
//command = getCorrectCommandForDataDisplay(option, paramContainer);
break;
case SelectedDataSource.DYNAMIC_DATASOURCE_2:
//The getCorrectCommandFordataDisplay() method is not used in this case since displaying the monthly balance evolution for the selected year does not require to specify a table from which data will be extracted
command = SQLCommandBuilder.getMonthlyTotalsCommand(sqlStatementFullYearBalanceEvolution, paramContainer);
break;
case SelectedDataSource.STATIC_DATASOURCE:
command = SQLCommandBuilder.getSpecificUserRecordsCommand(sqlStatementSavingAccountCurrentBalance, paramContainer);
break;
//command = SQLCommandBuilder.getFullYearRecordsCommand(sqlStatementFullYearBalanceEvolution, paramContainer);
}
}
else if (option == QueryType.TOTAL_VALUE)
{
command = SQLCommandBuilder.getSpecificUserRecordsCommand(sqlStatementSavingAccountCurrentBalance, paramContainer);;
}
return(DBConnectionManager.getData(command));
}
