Load <TThirdPartyType, TOwnedType>(
ISpreadsheet spreadsheet,
IFileIO <TOwnedType> pending_file_io,
ICSVFile <TOwnedType> pending_file,
IFileIO <TThirdPartyType> third_party_file_io,
IFileIO <TOwnedType> owned_file_io,
BudgetingMonths budgeting_months,
DataLoadingInformation <TThirdPartyType, TOwnedType> data_loading_info,
IMatcher matcher)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
Load_pending_data(pending_file_io, pending_file, data_loading_info);
Merge_budget_data(spreadsheet, pending_file, budgeting_months, data_loading_info);
Merge_other_data(spreadsheet, pending_file, budgeting_months, data_loading_info);
Generate_ad_hoc_data(spreadsheet, pending_file, budgeting_months, data_loading_info);
Merge_unreconciled_data(spreadsheet, pending_file, data_loading_info);
var reconciliator = Load_third_party_and_owned_files_into_reconciliator <TThirdPartyType, TOwnedType>(
data_loading_info,
third_party_file_io,
owned_file_io,
spreadsheet);
var reconciliation_interface = Create_reconciliation_interface(data_loading_info, reconciliator, matcher);
return(reconciliation_interface);
}
public IEnumerable <IPotentialMatch> Find_expense_matches <TThirdPartyType, TOwnedType>
(TThirdPartyType source_record, ICSVFile <TOwnedType> owned_file)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
return(Debug_find_expense_matches(source_record as ActualBankRecord, owned_file as ICSVFile <BankRecord>));
}
public void Match_specified_records <TThirdPartyType, TOwnedType>(
RecordForMatching <TThirdPartyType> record_for_matching,
int match_index,
ICSVFile <TOwnedType> owned_file)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
if (record_for_matching.Matches[match_index].Actual_records.Count > 1)
{
var new_match = new TOwnedType
{
Date = record_for_matching.SourceRecord.Date,
Description = Create_new_description(record_for_matching.Matches[match_index])
};
(new_match as BankRecord).Unreconciled_amount = record_for_matching.Matches[match_index].Actual_records.Sum(x => x.Main_amount());
(new_match as BankRecord).Type = (record_for_matching.Matches[match_index].Actual_records[0] as BankRecord).Type;
foreach (var actual_record in record_for_matching.Matches[match_index].Actual_records)
{
_bank_and_bank_in_loader.Update_expected_income_record_when_matched(record_for_matching.SourceRecord, (TOwnedType)actual_record);
owned_file.Remove_record_permanently((TOwnedType)actual_record);
}
record_for_matching.Matches[match_index].Actual_records.Clear();
record_for_matching.Matches[match_index].Actual_records.Add(new_match);
owned_file.Add_record_permanently(new_match);
}
else
{
_bank_and_bank_in_loader.Update_expected_income_record_when_matched(
record_for_matching.SourceRecord,
(TOwnedType)record_for_matching.Matches[match_index].Actual_records[0]);
}
Match_records(record_for_matching.SourceRecord, record_for_matching.Matches[match_index].Actual_records[0]);
}
private void Add_records_to_pending_file_for_records_that_have_matching_months <TRecordType>(
IEnumerable <TRecordType> base_records,
ICSVFile <TRecordType> pending_file,
BudgetingMonths budgeting_months) where TRecordType : ICSVRecord, new()
{
var final_month = budgeting_months.Last_month_for_budget_planning >= budgeting_months.Next_unplanned_month
? budgeting_months.Last_month_for_budget_planning
: budgeting_months.Last_month_for_budget_planning + 12;
for (int month = budgeting_months.Next_unplanned_month; month <= final_month; month++)
{
var new_month = month;
var new_year = budgeting_months.Start_year;
if (month > 12)
{
new_month = month - 12;
new_year = new_year + 1;
}
var new_annual_records = base_records
.Where(x => x.Date.Month == new_month)
.Select(x => (TRecordType)
With_correct_days_per_month(x.Copy(), new_year, x.Date.Month));
pending_file.Records.AddRange(new_annual_records.ToList());
}
pending_file.Records = pending_file.Records.OrderBy(record => record.Date).ToList();
}
public void Create_new_combined_record(
RecordForMatching <ActualBankRecord> record_for_matching,
int match_index,
ICSVFile <BankRecord> owned_file)
{
var sum_of_all_matches =
record_for_matching.Matches[match_index].Actual_records.Sum(x => x.Main_amount());
var missing_balance = record_for_matching.SourceRecord.Main_amount() - sum_of_all_matches;
var expense_amounts_match = record_for_matching.SourceRecord.Main_amount()
.Double_equals(sum_of_all_matches);
BankRecord new_match = New_combined_record(
record_for_matching,
match_index,
expense_amounts_match,
sum_of_all_matches);
Update_expected_income_and_owned_files(
record_for_matching,
match_index,
expense_amounts_match,
sum_of_all_matches,
missing_balance,
owned_file);
record_for_matching.Matches[match_index].Actual_records.Clear();
record_for_matching.Matches[match_index].Actual_records.Add(new_match);
owned_file.Add_record_permanently(new_match);
}
public void Copy_records_to_csv_file(ICSVFile <TRecordType> target_file)
{
foreach (var record in Records)
{
target_file.Records.Add(record);
}
}
public void Generate_ad_hoc_data(
IInputOutput input_output,
ISpreadsheet spreadsheet,
ICSVFile <BankRecord> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <ActualBankRecord, BankRecord> data_loading_info)
{
}
public void Generate_ad_hoc_data(
IInputOutput input_output,
ISpreadsheet spreadsheet,
ICSVFile <CredCard2InOutRecord> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <CredCard2Record, CredCard2InOutRecord> data_loading_info)
{
}
public void Merge_bespoke_data_with_pending_file(
IInputOutput input_output,
ISpreadsheet spreadsheet,
ICSVFile <BankRecord> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <ActualBankRecord, BankRecord> data_loading_info)
{
}
public void Copy_to_pending_file(ICSVFile <BankRecord> pending_file)
{
List <BankRecord> records_as_bank_records = File.Records.Select(x => x.Convert_to_bank_record()).ToList();
foreach (var bank_record in records_as_bank_records)
{
pending_file.Records.Add(bank_record);
}
}
public void Merge_bespoke_data_with_pending_file(
IInputOutput input_output,
ISpreadsheet spreadsheet,
ICSVFile <CredCard2InOutRecord> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <CredCard2Record, CredCard2InOutRecord> data_loading_info)
{
var most_recent_cred_card_direct_debit = spreadsheet.Get_most_recent_row_containing_text <BankRecord>(
MainSheetNames.Bank_out,
ReconConsts.Cred_card2_dd_description,
new List <int> {
ReconConsts.DescriptionColumn, ReconConsts.DdDescriptionColumn
});
var statement_date = new DateTime();
var next_date = most_recent_cred_card_direct_debit.Date.AddMonths(1);
var input = input_output.Get_input(string.Format(
ReconConsts.AskForCredCardDirectDebit,
ReconConsts.Cred_card2_name,
next_date.ToShortDateString()));
double new_balance = 0;
while (input != "0")
{
if (double.TryParse(input, out new_balance))
{
new_balance = new_balance * -1;
pending_file.Records.Add(new CredCard2InOutRecord
{
Date = next_date,
Description = ReconConsts.Cred_card2_regular_pymt_description,
Unreconciled_amount = new_balance
});
}
statement_date = next_date.AddMonths(-1);
next_date = next_date.Date.AddMonths(1);
input = input_output.Get_input(string.Format(
ReconConsts.AskForCredCardDirectDebit,
ReconConsts.Cred_card2_name,
next_date.ToShortDateString()));
}
if (!new_balance.Double_equals(0))
{
spreadsheet.Update_balance_on_totals_sheet(
Codes.Cred_card2_bal,
new_balance,
string.Format(
ReconConsts.CredCardBalanceDescription,
ReconConsts.Cred_card2_name,
$"{statement_date.ToString("MMM")} {statement_date.Year}"),
balance_column: 5,
text_column: 6,
code_column: 4,
input_output: input_output);
}
}
private void Merge_other_data <TThirdPartyType, TOwnedType>(
ISpreadsheet spreadsheet,
ICSVFile <TOwnedType> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <TThirdPartyType, TOwnedType> data_loading_info)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
data_loading_info.Loader.Merge_bespoke_data_with_pending_file(_input_output, spreadsheet, pending_file, budgeting_months, data_loading_info);
}
public void Match_specified_records(
RecordForMatching <CredCard2Record> record_for_matching,
int match_index,
ICSVFile <CredCard2InOutRecord> owned_file)
{
if (record_for_matching.Matches[match_index].Actual_records.Count > 1)
{
Create_new_combined_record(record_for_matching, match_index, owned_file);
}
Match_records(record_for_matching.SourceRecord, record_for_matching.Matches[match_index].Actual_records[0]);
}
private void Generate_ad_hoc_data <TThirdPartyType, TOwnedType>(
ISpreadsheet spreadsheet,
ICSVFile <TOwnedType> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <TThirdPartyType, TOwnedType> data_loading_info)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
_input_output.Output_line("Generating ad hoc data...");
data_loading_info.Loader.Generate_ad_hoc_data(_input_output, spreadsheet, pending_file, budgeting_months, data_loading_info);
}
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 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 Load_pending_data <TThirdPartyType, TOwnedType>(
IFileIO <TOwnedType> pending_file_io,
ICSVFile <TOwnedType> pending_file,
DataLoadingInformation <TThirdPartyType, TOwnedType> data_loading_info)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
_input_output.Output_line(
"Loading data from pending file (which you should have already split out, if necessary)...");
pending_file_io.Set_file_paths(data_loading_info.File_paths.Main_path, data_loading_info.Pending_file_name);
pending_file.Load(true, data_loading_info.Default_separator);
// The separator we loaded with had to match the source. Then we convert it here to match its destination.
pending_file.Convert_source_line_separators(data_loading_info.Default_separator, data_loading_info.Loading_separator);
}
private void Merge_budget_data <TThirdPartyType, TOwnedType>(
ISpreadsheet spreadsheet,
ICSVFile <TOwnedType> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <TThirdPartyType, TOwnedType> data_loading_info)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
_input_output.Output_line("Merging budget data with pending data...");
spreadsheet.Add_budgeted_monthly_data_to_pending_file(budgeting_months, pending_file, data_loading_info.Monthly_budget_data);
if (null != data_loading_info.Annual_budget_data)
{
spreadsheet.Add_budgeted_annual_data_to_pending_file(budgeting_months, pending_file, data_loading_info.Annual_budget_data);
}
}
public void Create_new_combined_record(
RecordForMatching <CredCard2Record> record_for_matching,
int match_index,
ICSVFile <CredCard2InOutRecord> owned_file)
{
foreach (var actual_record in record_for_matching.Matches[match_index].Actual_records)
{
owned_file.Remove_record_permanently((CredCard2InOutRecord)actual_record);
}
CredCard2InOutRecord new_match = New_combined_record(record_for_matching, match_index);
record_for_matching.Matches[match_index].Actual_records.Clear();
record_for_matching.Matches[match_index].Actual_records.Add(new_match);
owned_file.Add_record_permanently(new_match);
}
public void Append_csv_file <TRecordType>(string sheet_name, ICSVFile <TRecordType> csv_file)
where TRecordType : ICSVRecord, new()
{
Open_sheet(sheet_name);
var new_row_number = Last_row_number(sheet_name) + 1;
List <TRecordType> ordered_records = csv_file.Records_ordered_for_spreadsheet();
foreach (var csv_record in ordered_records)
{
csv_record.Populate_spreadsheet_row(new ExcelRange(_current_worksheet.Cells), new_row_number);
new_row_number++;
}
_workbook.Save();
}
public void Match_BankIn_records(
RecordForMatching <ActualBankRecord> record_for_matching,
int match_index,
ICSVFile <BankRecord> owned_file)
{
var match = (BankRecord)record_for_matching.Matches[match_index].Actual_records[0];
if (match.Type == Codes.ExpectedInBankTransaction)
{
_bank_and_bank_in_loader.Update_expected_income_record_when_matched(
record_for_matching.SourceRecord,
match);
}
Reconciliator <ActualBankRecord, BankRecord> .Match_specified_records(record_for_matching, match_index, owned_file);
}
private void Merge_unreconciled_data <TThirdPartyType, TOwnedType>(
ISpreadsheet spreadsheet,
ICSVFile <TOwnedType> pending_file,
DataLoadingInformation <TThirdPartyType, TOwnedType> data_loading_info)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
_input_output.Output_line("Merging unreconciled rows from spreadsheet with pending and budget data...");
spreadsheet.Add_unreconciled_rows_to_csv_file <TOwnedType>(data_loading_info.Sheet_name, pending_file);
_input_output.Output_line("Copying merged data (from pending, unreconciled, and budgeting) into main 'owned' csv file...");
pending_file.Update_source_lines_for_output(data_loading_info.Loading_separator);
pending_file.Write_to_file_as_source_lines(data_loading_info.File_paths.Owned_file_name);
_input_output.Output_line("...");
}
public void Write_back_to_main_spreadsheet(ICSVFile <TRecordType> csv_file, string worksheet_name)
{
ISpreadsheetRepo spreadsheet_repo = _spreadsheet_factory.Create_spreadsheet_repo();
var spreadsheet = new Spreadsheet(spreadsheet_repo);
try
{
Write_back_to_spreadsheet(spreadsheet, csv_file, worksheet_name);
spreadsheet_repo.Dispose();
}
catch (Exception)
{
spreadsheet_repo.Dispose();
throw;
}
}
public IEnumerable <IPotentialMatch> Standby_find_expense_matches <TThirdPartyType, TOwnedType>
(TThirdPartyType source_record, ICSVFile <TOwnedType> owned_file)
where TThirdPartyType : ICSVRecord, new()
where TOwnedType : ICSVRecord, new()
{
var result = new List <PotentialMatch>();
if (owned_file.Records[0].Main_amount() == source_record.Main_amount())
{
var actual_records = new List <ICSVRecord>();
actual_records.Add(owned_file.Records[0]);
result.Add(new PotentialMatch {
Actual_records = actual_records
});
}
return(result);
}
public void Merge_bespoke_data_with_pending_file(
IInputOutput input_output,
ISpreadsheet spreadsheet,
ICSVFile <BankRecord> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <ActualBankRecord, BankRecord> data_loading_info)
{
input_output.Output_line(ReconConsts.Loading_expenses);
_expected_income_csv_file.Load(false);
spreadsheet.Add_unreconciled_rows_to_csv_file <ExpectedIncomeRecord>(MainSheetNames.Expected_in, _expected_income_file.File);
_expected_income_csv_file.Populate_source_records_from_records();
_expected_income_file.Filter_for_employer_expenses_and_bank_transactions_only();
_expected_income_file.Copy_to_pending_file(pending_file);
_expected_income_csv_file.Populate_records_from_original_file_load();
}
public void Match_expense_records(
RecordForMatching <ActualBankRecord> record_for_matching,
int match_index,
ICSVFile <BankRecord> owned_file)
{
if (record_for_matching.Matches[match_index].Actual_records.Count > 1)
{
Create_new_combined_record(record_for_matching, match_index, owned_file);
}
else
{
_bank_and_bank_in_loader.Update_expected_income_record_when_matched(
record_for_matching.SourceRecord,
(BankRecord)record_for_matching.Matches[match_index].Actual_records[0]);
}
Match_records(
record_for_matching.SourceRecord,
(record_for_matching.Matches[match_index].Actual_records[0] as BankRecord));
}
public void Generate_ad_hoc_data(
IInputOutput input_output,
ISpreadsheet spreadsheet,
ICSVFile <BankRecord> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <ActualBankRecord, BankRecord> data_loading_info)
{
if (budgeting_months.Do_expected_out_budgeting)
{
Update_owed_CHB(input_output, spreadsheet, budgeting_months);
Update_weekly_item(input_output, spreadsheet, budgeting_months, "weekly spends", Codes.Code004, Codes.Code074);
Update_weekly_item(input_output, spreadsheet, budgeting_months, "grocery shopping", Codes.Code005, Codes.Code075);
Update_monthly_item(input_output, spreadsheet, budgeting_months, "yoga", Codes.Code078, Codes.Code078);
Update_monthly_item(input_output, spreadsheet, budgeting_months, "fuel", Codes.Code006, Codes.Code006);
Update_monthly_item(input_output, spreadsheet, budgeting_months, "vet", Codes.Code007, Codes.Code007);
Update_monthly_item(input_output, spreadsheet, budgeting_months, "domain hosting", Codes.Code011, Codes.Code011);
}
}
private void Update_expected_income_and_owned_files(
RecordForMatching <ActualBankRecord> record_for_matching,
int match_index,
bool expense_amounts_match,
double sum_of_all_matches,
double missing_balance,
ICSVFile <BankRecord> owned_file)
{
foreach (var actual_record in record_for_matching.Matches[match_index].Actual_records)
{
_bank_and_bank_in_loader.Update_expected_income_record_when_matched(record_for_matching.SourceRecord, actual_record);
owned_file.Remove_record_permanently((BankRecord)actual_record);
}
if (!expense_amounts_match)
{
_bank_and_bank_in_loader.Create_new_expenses_record_to_match_balance(
record_for_matching.SourceRecord,
missing_balance);
}
}
public void Merge_bespoke_data_with_pending_file(
IInputOutput input_output,
ISpreadsheet spreadsheet,
ICSVFile <BankRecord> pending_file,
BudgetingMonths budgeting_months,
DataLoadingInformation <ActualBankRecord, BankRecord> data_loading_info)
{
Add_most_recent_credit_card_direct_debits(
input_output,
spreadsheet,
pending_file,
ReconConsts.Cred_card1_name,
ReconConsts.Cred_card1_dd_description);
Add_most_recent_credit_card_direct_debits(
input_output,
spreadsheet,
pending_file,
ReconConsts.Cred_card2_name,
ReconConsts.Cred_card2_dd_description);
}
private void Add_most_recent_credit_card_direct_debits(
IInputOutput input_output,
ISpreadsheet spreadsheet,
ICSVFile <BankRecord> pending_file,
string cred_card_name,
string direct_debit_description)
{
var most_recent_cred_card_direct_debit = spreadsheet.Get_most_recent_row_containing_text <BankRecord>(
MainSheetNames.Bank_out,
direct_debit_description,
new List <int> {
ReconConsts.DescriptionColumn, ReconConsts.DdDescriptionColumn
});
var next_date = most_recent_cred_card_direct_debit.Date.AddMonths(1);
var input = input_output.Get_input(string.Format(
ReconConsts.AskForCredCardDirectDebit,
cred_card_name,
next_date.ToShortDateString()));
while (input != "0")
{
double amount;
if (double.TryParse(input, out amount))
{
pending_file.Records.Add(new BankRecord
{
Date = next_date,
Description = direct_debit_description,
Type = "POS",
Unreconciled_amount = amount
});
}
next_date = next_date.Date.AddMonths(1);
input = input_output.Get_input(string.Format(
ReconConsts.AskForCredCardDirectDebit,
cred_card_name,
next_date.ToShortDateString()));
}
}
