private string Get_budget_item_description(BudgetItemListData budget_item_list_data)
{
string result = null;
int row_before_first_budget_item = _spreadsheet_io.Find_row_number_of_last_row_containing_cell(
budget_item_list_data.Budget_sheet_name,
budget_item_list_data.Start_divider,
new List <int> {
ReconConsts.BudgetDividerColumn
});
int row_after_last_budget_item = _spreadsheet_io.Find_row_number_of_last_row_containing_cell(
budget_item_list_data.Budget_sheet_name,
budget_item_list_data.End_divider,
new List <int> {
ReconConsts.BudgetDividerColumn
});
if (row_after_last_budget_item - row_before_first_budget_item > 1)
{
result = _spreadsheet_io.Get_text(
budget_item_list_data.Budget_sheet_name,
row_before_first_budget_item + 1,
budget_item_list_data.Last_column_number);
}
return(result);
}
public void M_CanReadAllBudgetItems()
{
// Arrange
var budget_item_list_data = new BudgetItemListData
{
Sheet_name = MainSheetNames.Budget_out,
Start_divider = Dividers.Cred_card1,
End_divider = Dividers.Cred_card2,
First_column_number = 2,
Last_column_number = 5
};
const int expectedFirstRowNumber = 6;
const int expectedLastRowNumber = 20;
var mock_spreadsheet_repo = new Mock <ISpreadsheetRepo>();
mock_spreadsheet_repo.Setup(x => x.Find_row_number_of_last_row_containing_cell(
budget_item_list_data.Sheet_name, budget_item_list_data.Start_divider, 2))
.Returns(expectedFirstRowNumber - 1);
mock_spreadsheet_repo.Setup(x => x.Find_row_number_of_last_row_containing_cell(
budget_item_list_data.Sheet_name, budget_item_list_data.End_divider, 2))
.Returns(expectedLastRowNumber + 1);
var spreadsheet = new Spreadsheet(mock_spreadsheet_repo.Object);
// Act
spreadsheet.Get_all_budget_items <CredCard1InOutRecord>(budget_item_list_data);
// Assert
mock_spreadsheet_repo.Verify(x => x.Get_rows_as_records <CredCard1InOutRecord>(
budget_item_list_data.Sheet_name,
expectedFirstRowNumber,
expectedLastRowNumber,
budget_item_list_data.First_column_number,
budget_item_list_data.Last_column_number));
}
public DateTime Get_next_unplanned_month <TRecordType>(BudgetItemListData budget_item_list_data = null)
where TRecordType : ICSVRecord, new()
{
string budget_item_description = $"Couldn't find budget item on {budget_item_list_data.Budget_sheet_name} between {budget_item_list_data.Start_divider} and {budget_item_list_data.End_divider}";
DateTime next_unplanned_month = DateTime.Today;
try
{
budget_item_description = Get_budget_item_description(budget_item_list_data);
if (null != budget_item_description)
{
TRecordType record = Get_last_record_with_specified_description <TRecordType>(budget_item_description, budget_item_list_data);
next_unplanned_month = record.Date.AddMonths(1);
}
else
{
next_unplanned_month = DateTime.Today.AddMonths(1);
}
}
catch (Exception)
{
throw new MonthlyBudgetedRowNotFoundException(budget_item_description);
}
return(next_unplanned_month);
}
private BudgetingMonths Initialise_budgeting_months <TRecordType>(
ISpreadsheet spreadsheet,
BudgetItemListData budget_item_list_data)
where TRecordType : ICSVRecord, new()
{
DateTime next_unplanned_month = _clock.Today_date_time();
bool do_transaction_budgeting = Confirm_budgeting(ReconConsts.ConfirmTransactionBudgeting);
bool do_expected_out_budgeting = Confirm_budgeting(ReconConsts.ConfirmExpectedOutBudgeting);
var budgeting_months = new BudgetingMonths
{
Do_expected_out_budgeting = do_expected_out_budgeting,
Do_transaction_budgeting = do_transaction_budgeting
};
if (do_transaction_budgeting)
{
next_unplanned_month = Get_next_unplanned_month <TRecordType>(spreadsheet, budget_item_list_data);
}
budgeting_months.Next_unplanned_month = next_unplanned_month.Month;
budgeting_months.Start_year = next_unplanned_month.Year;
return(budgeting_months);
}
protected bool Equals(BudgetItemListData other)
{
return(string.Equals(Sheet_name, other.Sheet_name) &&
string.Equals(Start_divider, other.Start_divider) &&
string.Equals(End_divider, other.End_divider) &&
First_column_number == other.First_column_number &&
Last_column_number == other.Last_column_number);
}
private DateTime Get_next_unplanned_month <TRecordType>(
ISpreadsheet spreadsheet,
BudgetItemListData budget_item_list_data)
where TRecordType : ICSVRecord, new()
{
DateTime default_month = _clock.Today_date_time();
DateTime next_unplanned_month = default_month;
bool bad_input = false;
try
{
next_unplanned_month = spreadsheet.Get_next_unplanned_month <TRecordType>(budget_item_list_data);
}
catch (MonthlyBudgetedRowNotFoundException exception)
{
string new_month = _input_output.Get_input(String.Format(ReconConsts.CantFindPreviousBudgetRow, exception.Message));
try
{
if (!String.IsNullOrEmpty(new_month) && Char.IsDigit(new_month[0]))
{
int actual_month = Convert.ToInt32(new_month);
if (actual_month < 1 || actual_month > 12)
{
bad_input = true;
}
else
{
var year = default_month.Year;
if (actual_month < default_month.Month)
{
if (Month_is_smaller_because_end_of_year(actual_month, default_month.Month))
{
year++;
}
}
next_unplanned_month = new DateTime(year, actual_month, 1);
}
}
else
{
bad_input = true;
}
}
catch (Exception)
{
bad_input = true;
}
}
if (bad_input)
{
_input_output.Output_line(ReconConsts.DefaultUnplannedMonth);
next_unplanned_month = default_month;
}
return(next_unplanned_month);
}
public void Add_budgeted_monthly_data_to_pending_file <TRecordType>(
BudgetingMonths budgeting_months,
ICSVFile <TRecordType> pending_file,
BudgetItemListData budget_item_list_data) where TRecordType : ICSVRecord, new()
{
var base_records = Get_all_budget_items <TRecordType>(budget_item_list_data);
Add_records_to_pending_file_for_every_specified_month <TRecordType>(
base_records,
(ICSVFile <TRecordType>)pending_file,
budgeting_months);
}
public void Add_budgeted_annual_data_to_pending_file <TRecordType>(
BudgetingMonths budgeting_months,
ICSVFile <TRecordType> pending_file,
BudgetItemListData budget_item_list_data) where TRecordType : ICSVRecord, new()
{
var annual_records = Get_all_budget_items <TRecordType>(budget_item_list_data);
Add_records_to_pending_file_for_records_that_have_matching_months <TRecordType>(
annual_records,
(ICSVFile <TRecordType>)pending_file,
budgeting_months);
}
public void M_WhenAddingAnnualBudgetedDataToSpreadsheet_WillOrderResultsByDate()
{
// Arrange
var budget_item_list_data = new BudgetItemListData
{
Sheet_name = MainSheetNames.Budget_out,
Start_divider = Dividers.Cred_card1,
End_divider = Dividers.Cred_card2,
First_column_number = 2,
Last_column_number = 5
};
var first_month = 12;
var last_month = 3;
var mock_spreadsheet_repo = new Mock <ISpreadsheetRepo>();
var budget_data_setup = When_adding_budgeted_data_to_spreadsheet <CredCard1InOutRecord>(
first_month,
last_month,
mock_spreadsheet_repo,
budget_item_list_data);
var mock_cred_card1_in_out_file_io = new Mock <IFileIO <CredCard1InOutRecord> >();
mock_cred_card1_in_out_file_io.Setup(x => x.Load(It.IsAny <List <string> >(), null))
.Returns(new List <CredCard1InOutRecord> {
new CredCard1InOutRecord {
Date = new DateTime(budget_data_setup.BudgetingMonths.Start_year, first_month, 10)
},
new CredCard1InOutRecord {
Date = new DateTime(budget_data_setup.BudgetingMonths.Start_year, first_month, 4)
},
new CredCard1InOutRecord {
Date = new DateTime(budget_data_setup.BudgetingMonths.Start_year, first_month, 25)
}
});
var pending_file = new CSVFile <CredCard1InOutRecord>(mock_cred_card1_in_out_file_io.Object);
pending_file.Load();
var spreadsheet = new Spreadsheet(mock_spreadsheet_repo.Object);
// Act
spreadsheet.Add_budgeted_annual_data_to_pending_file(budget_data_setup.BudgetingMonths, pending_file, budget_item_list_data);
// Assert
CredCard1InOutRecord previous_record = null;
foreach (CredCard1InOutRecord record in pending_file.Records)
{
if (null != previous_record)
{
Assert.IsTrue(record.Date.ToOADate() > previous_record.Date.ToOADate());
}
previous_record = record;
}
}
public List <TRecordType> Get_all_budget_items <TRecordType>(BudgetItemListData budget_item_list_data)
where TRecordType : ICSVRecord, new()
{
int first_row_number = _spreadsheet_io
.Find_row_number_of_last_row_containing_cell(budget_item_list_data.Sheet_name, budget_item_list_data.Start_divider) + 1;
int last_row_number = _spreadsheet_io
.Find_row_number_of_last_row_containing_cell(budget_item_list_data.Sheet_name, budget_item_list_data.End_divider) - 1;
return(_spreadsheet_io.Get_rows_as_records <TRecordType>(
budget_item_list_data.Sheet_name,
first_row_number,
last_row_number,
budget_item_list_data.First_column_number,
budget_item_list_data.Last_column_number));
}
private BudgetDataSetup <TRecordType> When_adding_budgeted_data_to_spreadsheet <TRecordType>(
int first_month,
int last_month,
Mock <ISpreadsheetRepo> mock_spreadsheet_repo,
BudgetItemListData budget_item_list_data,
int default_day = 5) where TRecordType : ICSVRecord, new()
{
// Arrange
var budgeting_months = new BudgetingMonths
{
Next_unplanned_month = first_month,
Last_month_for_budget_planning = last_month,
Start_year = 2018
};
var first_monthly_row = 3;
var last_monthly_row = first_monthly_row + 2;
string desc1 = "first monthly record";
string desc2 = "second monthly record";
var monthly_records = new List <TRecordType>
{
new TRecordType {
Date = new DateTime(budgeting_months.Start_year, first_month, default_day), Description = desc1
},
new TRecordType {
Date = new DateTime(budgeting_months.Start_year, first_month, 20), Description = desc2
}
};
mock_spreadsheet_repo.Setup(x => x.Find_row_number_of_last_row_containing_cell(budget_item_list_data.Sheet_name, budget_item_list_data.Start_divider, 2)).Returns(first_monthly_row);
mock_spreadsheet_repo.Setup(x => x.Find_row_number_of_last_row_containing_cell(budget_item_list_data.Sheet_name, budget_item_list_data.End_divider, 2)).Returns(last_monthly_row);
mock_spreadsheet_repo.Setup(x => x.Get_rows_as_records <TRecordType>(
budget_item_list_data.Sheet_name,
first_monthly_row + 1,
last_monthly_row - 1,
budget_item_list_data.First_column_number,
budget_item_list_data.Last_column_number)).Returns(monthly_records);
return(new BudgetDataSetup <TRecordType>
{
Desc1 = desc1,
Desc2 = desc2,
BudgetingMonths = budgeting_months,
MonthlyRecords = monthly_records
});
}
public void M_WhenAddingAnnualBudgetedDataToSpreadsheet_WillAdjustDayIfDaysInMonthIsTooLow()
{
// Arrange
var budget_item_list_data = new BudgetItemListData
{
Sheet_name = MainSheetNames.Budget_in,
Start_divider = Dividers.Date,
End_divider = Dividers.Total,
First_column_number = 2,
Last_column_number = 6
};
var first_month = 12;
var last_month = 2;
var mock_spreadsheet_repo = new Mock <ISpreadsheetRepo>();
var budget_data_setup = When_adding_budgeted_data_to_spreadsheet <BankRecord>(
first_month,
last_month,
mock_spreadsheet_repo,
budget_item_list_data,
default_day: 31);
var pending_file_records = new List <BankRecord>();
var mock_pending_file = new Mock <ICSVFile <BankRecord> >();
mock_pending_file.Setup(x => x.Records).Returns(pending_file_records);
var spreadsheet = new Spreadsheet(mock_spreadsheet_repo.Object);
bool exception_thrown = false;
// Act
try
{
spreadsheet.Add_budgeted_annual_data_to_pending_file(budget_data_setup.BudgetingMonths, mock_pending_file.Object, budget_item_list_data);
}
catch (Exception)
{
exception_thrown = true;
}
// Assert
Assert.IsFalse(exception_thrown);
}
private TRecordType Get_last_record_with_specified_description <TRecordType>(
string description,
BudgetItemListData budget_item_list_data)
where TRecordType : ICSVRecord, new()
{
var record = new TRecordType();
var row_number_of_last_relevant_payment = _spreadsheet_io.Find_row_number_of_last_row_containing_cell(
budget_item_list_data.Owned_sheet_name,
description,
new List <int> {
budget_item_list_data.Last_column_number - 1, budget_item_list_data.Third_party_desc_col
},
false);
var spreadsheet_row = _spreadsheet_io.Read_specified_row(
budget_item_list_data.Owned_sheet_name,
row_number_of_last_relevant_payment);
record.Read_from_spreadsheet_row(spreadsheet_row);
return(record);
}
public BudgetingMonths Recursively_ask_for_budgeting_months <TRecordType>(
ISpreadsheet spreadsheet,
BudgetItemListData budget_item_list_data)
where TRecordType : ICSVRecord, new()
{
BudgetingMonths budgeting_months = Initialise_budgeting_months <TRecordType>(spreadsheet, budget_item_list_data);
if (budgeting_months.Do_transaction_budgeting || budgeting_months.Do_expected_out_budgeting)
{
int last_month_for_budget_planning = Get_last_month_for_budget_planning(spreadsheet, budgeting_months.Next_unplanned_month);
budgeting_months.Last_month_for_budget_planning = last_month_for_budget_planning;
if (last_month_for_budget_planning == 0)
{
budgeting_months.Do_expected_out_budgeting = false;
budgeting_months.Do_transaction_budgeting = false;
}
else
{
budgeting_months.Last_month_for_budget_planning = Confirm_budgeting_month_choices_with_user(budgeting_months, spreadsheet);
}
}
return(budgeting_months);
}
public void M_WhenAddingMonthlyBudgetedDataToSpreadsheet_WillAddAllMonthlyItemsToPendingFile_WithMonthsAndYearsChanged(
int first_month, int last_month)
{
// Arrange
var budget_item_list_data = new BudgetItemListData
{
Sheet_name = MainSheetNames.Budget_in,
Start_divider = Dividers.Date,
End_divider = Dividers.Total,
First_column_number = 2,
Last_column_number = 6
};
var mock_spreadsheet_repo = new Mock <ISpreadsheetRepo>();
var budget_data_setup = When_adding_budgeted_data_to_spreadsheet <BankRecord>(
first_month,
last_month,
mock_spreadsheet_repo,
budget_item_list_data);
var pending_file_records = new List <BankRecord>();
var mock_pending_file = new Mock <ICSVFile <BankRecord> >();
mock_pending_file.Setup(x => x.Records).Returns(pending_file_records);
var spreadsheet = new Spreadsheet(mock_spreadsheet_repo.Object);
// Act
spreadsheet.Add_budgeted_monthly_data_to_pending_file(budget_data_setup.BudgetingMonths, mock_pending_file.Object, budget_item_list_data);
// Assert
Assert_WillAddAllMonthlyItemsToPendingFile_WithMonthsAndYearsChanged <BankRecord>(
first_month,
last_month,
budget_data_setup.MonthlyRecords.Count,
pending_file_records,
budget_data_setup.BudgetingMonths,
budget_data_setup.Desc1,
budget_data_setup.Desc2);
}
public void M_WhenAddingBudgetedBankOutDataToSpreadsheet_WillAddAnnualBankOutTransactionsToPendingFile_IfMonthMatchesBudgetingMonths(
int first_month, int last_month)
{
// Arrange
var annual_budget_item_list_data = new BudgetItemListData
{
Sheet_name = MainSheetNames.Budget_out,
Start_divider = Dividers.Annual_sodds,
End_divider = Dividers.Annual_total,
First_column_number = 2,
Last_column_number = 6
};
var mock_spreadsheet_repo = new Mock <ISpreadsheetRepo>();
var first_annual_row = 10;
var last_annual_row = first_annual_row + 2;
string desc1 = "annual record with matching month";
string desc2 = "other annual record with matching month";
string desc3 = "annual record with non-matching month";
var annual_bank_records = new List <BankRecord>
{
new BankRecord {
Date = new DateTime(2018, first_month, 1), Description = desc1
},
new BankRecord {
Date = new DateTime(2018, last_month, 1), Description = desc2
},
new BankRecord {
Date = new DateTime(2018, last_month + 2, 1), Description = desc3
}
};
mock_spreadsheet_repo.Setup(x => x.Find_row_number_of_last_row_containing_cell(annual_budget_item_list_data.Sheet_name, annual_budget_item_list_data.Start_divider, 2)).Returns(first_annual_row);
mock_spreadsheet_repo.Setup(x => x.Find_row_number_of_last_row_containing_cell(annual_budget_item_list_data.Sheet_name, annual_budget_item_list_data.End_divider, 2)).Returns(last_annual_row);
mock_spreadsheet_repo.Setup(x => x.Get_rows_as_records <BankRecord>(annual_budget_item_list_data.Sheet_name, first_annual_row + 1, last_annual_row - 1, annual_budget_item_list_data.First_column_number, annual_budget_item_list_data.Last_column_number)).Returns(annual_bank_records);
// Everything else:
var budgeting_months = new BudgetingMonths
{
Next_unplanned_month = first_month,
Last_month_for_budget_planning = last_month,
Start_year = 2018
};
var pending_file_records = new List <BankRecord>();
var mock_pending_file = new Mock <ICSVFile <BankRecord> >();
mock_pending_file.Setup(x => x.Records).Returns(pending_file_records);
var spreadsheet = new Spreadsheet(mock_spreadsheet_repo.Object);
// Act
spreadsheet.Add_budgeted_annual_data_to_pending_file(budgeting_months, mock_pending_file.Object, annual_budget_item_list_data);
// Assert
Assert.AreEqual(2, pending_file_records.Count, "total num records");
Assert.AreEqual(1, pending_file_records.Count(x => x.Description == desc1), "num repetitions of matching record");
Assert.AreEqual(1, pending_file_records.Count(x => x.Description == desc2), "num repetitions of other matching record");
Assert.AreEqual(0, pending_file_records.Count(x => x.Description == desc3), "num repetitions of non-matching record");
var expected_record2_year = last_month >= first_month
? budgeting_months.Start_year
: budgeting_months.Start_year + 1;
Assert.AreEqual(expected_record2_year, pending_file_records[1].Date.Year);
}
