private void AddToReport(string category, double amount, string date, string description, List <BudgetReport> report, string type = null)
{
bool isNewCategory = false;
var budgetCategory = report.Find(i => i.Category.Contains(category));
if (budgetCategory == null)
{
budgetCategory = new BudgetReport();
budgetCategory.Category = category;
budgetCategory.TransType = "debit"; //By default - TODO
isNewCategory = true;
}
budgetCategory.TotalAmount += amount;
if (type != null)
{
budgetCategory.TransType = type;
}
if (!String.IsNullOrEmpty(description) && !String.IsNullOrEmpty(date))
{
StringBuilder sBuilder = new StringBuilder();
budgetCategory.Notes += sBuilder.AppendLine(formatNotes(budgetCategory.TransType, description, date, amount).ToString()).ToString();
}
if (isNewCategory)
{
report.Add(budgetCategory);
}
}
private List <BudgetReport> ProcessBudgetRecordsByTransType(IEnumerable <Budget> budgetRecords, string transType)
{
List <BudgetReport> tmpBudgetReportList = new List <BudgetReport>();
var queryByCategory =
from record in budgetRecords
where record.TransactionType.Equals(transType)
group record by record.Category into g
select new
{
Category = g.Key,
Total = g.Sum(record => record.Amount),
TempDescription = string.Join("#;", g.Select(i => i.Description)),
TempAmount = string.Join("#;", g.Select(i => i.Amount)),
TempDates = string.Join("#;", g.Select(i => i.Date))
};
foreach (var row in queryByCategory)
{
BudgetReport bReport = new BudgetReport();
bReport.Category = row.Category;
bReport.TotalAmount = row.Total;
bReport.TransType = transType;
string[] splitDescription = row.TempDescription.Split(new string[] { "#;" }, StringSplitOptions.RemoveEmptyEntries);
string[] splitAmount = row.TempAmount.Split(new string[] { "#;" }, StringSplitOptions.RemoveEmptyEntries);
string[] splitDates = row.TempDates.Split(new string[] { "#;" }, StringSplitOptions.RemoveEmptyEntries);
if (splitDescription.Length == splitAmount.Length)
{
StringBuilder strNotes = new StringBuilder();
for (int i = 0; i < splitDescription.Length; i++)
{
strNotes = strNotes.AppendLine(formatNotes(transType, splitDescription[i], splitDates[i], Double.Parse(splitAmount[i])).ToString());
}
bReport.Notes = strNotes.ToString();
}
tmpBudgetReportList.Add(bReport);
}
return(tmpBudgetReportList);
}
private void btnProcess_Click(object sender, EventArgs e)
{
using (var csv = new CsvReader(File.OpenText(txtFilePath.Text)))
{
csv.Configuration.HasHeaderRecord = true;
csv.Configuration.RegisterClassMap <BudgetClassMap>();
DateTime sDate = DateTime.Parse(startDate.Text);
DateTime eDate = DateTime.Parse(endDate.Text);
List <Budget> budgetRecords = csv.GetRecords <Budget>().Where(f => f.Date >= sDate && f.Date <= eDate).Distinct(new DistinctItemComparer()).ToList();
//remove duplicates -- original description is bit messed up. .. cleaning up by ignoring spaces.
/*List<Budget> budgetRecords = getBudgetRecords.GroupBy(r => new { r.AccountName, r.Amount, r.Category, r.Date, r.TransactionType, r.Description, OriginalDescription = r.OriginalDescription.Replace(" ","") })
* .Select(r => r.First())
* .ToList();
*/
//Get "debit" TransType
List <BudgetReport> tmpBudgetDebitReportList = ProcessBudgetRecordsByTransType(budgetRecords, "debit");
//Get "credit" TransType -- REFUNDS
List <BudgetReport> tmpBudgetCreditReportList = ProcessBudgetRecordsByTransType(budgetRecords, "credit");
//Bind multiple categories into a useful form by looking at the config file
List <BudgetReport> budgetReportList = new List <BudgetReport>();
var appSettings = ConfigurationManager.AppSettings;
foreach (var key in appSettings.AllKeys)
{
BudgetReport bReport = new BudgetReport();
bReport.Category = key;
string[] categoryValues = appSettings[key].Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string category in categoryValues)
{
var tmpDebitBudget = tmpBudgetDebitReportList.Find(i => i.Category.Equals(category));
if (tmpDebitBudget != null)
{
tmpDebitBudget.IsProcessed = true;
bReport.TotalAmount += tmpDebitBudget.TotalAmount;
bReport.Notes += tmpDebitBudget.Notes;
}
//also check for refunds - based on credit type
var tmpCreditBudget = tmpBudgetCreditReportList.Find(i => i.Category.Equals(category));
if (tmpCreditBudget != null)
{
tmpCreditBudget.IsProcessed = true;
bReport.TotalAmount -= tmpCreditBudget.TotalAmount;
bReport.Notes += tmpCreditBudget.Notes;
}
}
budgetReportList.Add(bReport);
}
//Add all remaining Debit purchases
budgetReportList.AddRange(tmpBudgetDebitReportList.FindAll(i => !i.IsProcessed));
//Add all remaining Credit purchases
budgetReportList.AddRange(tmpBudgetCreditReportList.FindAll(i => !i.IsProcessed));
FixCategories("Shopping & Sporting Goods", budgetReportList, "Groceries", new[] { "Sam's Club", "Costco" });
FixCategories("Transfer", budgetReportList, "Daycare", new[] { "NANCYGKELLY", "HALLIEPETER" });
FixCategories("Transfer", budgetReportList, "Shopping & Sporting Goods", new[] { "Venmo" });
FixCategories("Entertainment", budgetReportList, "Fun & Getaways", new[] { "TICKETSATWORK.COM" });
FixCategories("Transfer", budgetReportList, "Mobile (ATT)", new[] { "SREELEELA" });
FixCategories("Transfer", budgetReportList, "Food Services", new[] { "ZELLE TO S PROMILA" });
FixCategories("Transfer", budgetReportList, "Miscellaneous", new[] { "ZELLE Debit", "#NAME?" });
FixCategories("Restaurants", budgetReportList, "Food Services", new[] { "Food" });
FixCategories("Transfer", budgetReportList, "Mortgage", new[] { "ZELLE TO AHUJA APARNA", "BLOSSOM REAL" });
FixCategories("Transfer", budgetReportList, "India Investment", new[] { "RIAMONEYTRANSFER", "Xoom", "ZELLE TO KONDA KRISHNA" });
FixCategories("Transfer", budgetReportList, "Car Payment", new[] { "- JPMorgan Chase Ext" });
FixCategories("Transfer", budgetReportList, "Education", new[] { "MCPHS" });
FixCategories("Transfer", budgetReportList, "Daycare", new[] { "ZELLE TO GANNU RAJANI" });
FixCategories("Groceries", budgetReportList, "Water", new[] { "DS SERVICES" });
FixCategories("Restaurants", budgetReportList, "Water", new[] { "WATER - COFFEE DELIVERY" });
FixCategories("Restaurants", budgetReportList, "Groceries", new[] { "CRESCENT RI" });
FixCategories("Utilities", budgetReportList, "Mortgage", new[] { "CHARLESTON MANAGEMENT CORP" });
FixCategories("Utilities", budgetReportList, "Daycare", new[] { "ADVENTURES PRESCHOOL" });
FixCategories("Mortgage", budgetReportList, "Daycare", new[] { "ADVENTURES PRESCHOOL" });
FixCategories("Mortgage", budgetReportList, "Room Rent", new[] { "Jeff", "Elan" });
FixCategories("Credit Card Payments", budgetReportList, "Groceries", new[] { "Hello Brother Indian" });
FixCategories("Credit Card Payments", budgetReportList, "Room Rent", new[] { "Erenterplan" });
FixCategories("Rental Income", budgetReportList, "Paycheck Income", new[] { "DEPOSIT MADE IN A BRANCH" });
FixCategories("Other Income", budgetReportList, "Rental Income", new[] { "RAVINDRA M BHEEM" });
FixCategories("Other Income", budgetReportList, "Groceries", new[] { "Costco Whse" });
FixCategories("Other Income", budgetReportList, "Paycheck Income", new[] { "INSIGHT", "BAXALTA", "TAKEDA DEVELOPME DIRECT DEP", "Check Deposit" });
FixCategories("Service & Parts", budgetReportList, "Gas & Fuel", new[] { "Auto Clinic Inc" });
FixCategories("Miscellaneous", budgetReportList, "Gas & Fuel", new[] { "Auto Clinic Inc" });
FixCategories("ATM Withdrawal", budgetReportList, "Car Payment", new[] { "TRANSFER TO LOAN 141" });
//At last calculate Expenses from the above budget list
CalculateTotalExpenses(budgetReportList);
//print
Logger.LogMessagetoExcelFile(budgetReportList);
}
}
private void RemoveNotesFromBudget(string note, BudgetReport budget)
{
int i = budget.Notes.IndexOf(note);
budget.Notes = budget.Notes.Remove(i, note.Length);
}
