public ExcelFileReader(string Path, int ReportYear) { this.Path = Path; this.reportYear = ReportYear; Init(); Total = new DonerCollection(); logger = Logger.CreateLogger(); }
private static void CreateReportForDoners(DonerCollection data) { List<Doner> doners = data.GetAllDoners(); foreach (var item in doners) { logger.WriteInfo("Processing Doner: {0} ....", item.Name); ExcelWriter writer = new ExcelWriter(String.Format("{0}_Donations{1}.xlsx", item.Name, reportingYear), ExcelWriter.ReportType.YearlyDonerReport); DonerCollection donationsByDoner = new DonerCollection(); donationsByDoner.AddDonation(item, data.GetDonationsOfDonerByMonth(item, reportingYear)); writer.Write(donationsByDoner.GetAllDonations(), item); } }
public void Write(DonerCollection donationData) { string text = "blah blah text to add"; StringBuilder sb = new StringBuilder(); sb.AppendLine(String.Format("{0}: \n", donationData.GetAllDoners().FirstOrDefault().Name)); sb.AppendLine(); sb.AppendLine(text); sb.AppendLine("Your total donations for " +donationData.GetAllDonations().FirstOrDefault().DonationTime.Year + " are: "); sb.AppendLine(String.Format("\t{0:C}", donationData.CalculateTotal())); sb.AppendLine(); sb.AppendLine(); sb.AppendLine("Thank you for your support!"); sb.AppendLine("--- CGC Finance Dept ---"); using (StreamWriter file = new StreamWriter(Filename)) { file.Write(sb.ToString()); } }
static void Main(string[] args) { DonerCollection data; try { logger = Logger.CreateLogger(true); //Test(); // string filePath = @"TestData\"; string filePath = System.Configuration.ConfigurationManager.AppSettings["SpreadSheetLocation"]; string[] files = Directory.GetFiles(filePath, "*.xls*"); data = new DonerCollection(); ReadExcel(files, data); //TestMerge(); //TestLogger(); //TestFileNames(files); //TestDonationQueries(); data.PrintDoners(); // TestPrintDonationsOfAaron(data); data.ConsolidateDoners(); data.PrintDoners(); data.PrintDonationCollectionDates(); if (reportTypes.Contains("Donors")) CreateReportForDoners(data); if (reportTypes.Contains("Monthly")) CreateMonthlyReports(data); //TestExcelWriter(ref data); } catch (Exception e) { logger.WriteError("Exception occurred during execution: \n {0}, {1}", e.Message, e.StackTrace); } finally { logger.Close(); } }
private static void CreateMonthlyReports(DonerCollection data) { List<Doner> doners = data.GetAllDoners(); for (DateTime start = new DateTime(reportingYear, 1, 1); start.Year < reportingYear+1; start = start.AddMonths(1)) //for (DateTime start = new DateTime(2010, 1, 1); start.Month < 2; start = start.AddMonths(1)) { DateTime end = start.AddMonths(1); logger.WriteInfo("Processing Month: {0} ....", CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName( start.Month )); var monthData = data.GetDonationsByDate(start, end); if (!monthData.Any()) { logger.WriteWarning("No data found for this month"); continue; } ExcelWriter writer = new ExcelWriter(String.Format("{0}_{1}_TallyReport.xlsx", CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName( start.Month ), start.Year.ToString()), FinanceApplication.ExcelWriter.ReportType.MonthyTally); logger.WriteInfo("Done. Total = {0:C}", writer.Write(monthData, new Doner("nobody"))); // TEMP CODE FOR DEBUGGING //data.GetDonationsByDate(start, end).ForEach(d => d.PrintDonations()); } }
public void MergeDonerCollection(DonerCollection mergeData) { foreach (var item in mergeData.GetAllDonersAndDonations()) { this.AddDonation((Doner)item.Key, (ICollection< Donation>)item.Value); } }
public DonerCollection(DonerCollection pastData) { }
public DonerCollection Deserialize() { excelReader.IsFirstRowAsColumnNames = true; DataSet result = excelReader.AsDataSet(); DateTime donationTime = GetDonationTime(); DonerCollection donationData = new DonerCollection(); logger.WriteInfo("INFO: Reading FileName: {0}....", Path.ToString()); foreach (DataTable sheet in result.Tables) { if (sheet.TableName.ToLowerInvariant().Contains("tally")) { logger.WriteInfo("skipping sheet: " + sheet.TableName); continue; } // Use the donation date from the worksheet name instead if it exists. DateTime sheetDonationTime; if (DateTime.TryParse(sheet.TableName, out sheetDonationTime)) { donationTime = new DateTime(reportYear, sheetDonationTime.Month, sheetDonationTime.Day); } foreach (DataRow donationRow in sheet.Rows) { Doner currentDoner = new Doner(donationRow["Names"].ToString().Trim()); Donation currentDonation = new Donation(donationTime); double amount = 0.0; // If the row has no member name (Names) or is the date line, then skip it. if (String.IsNullOrWhiteSpace(currentDoner.Name) || currentDoner.Name.ToLower().Contains("date")) continue; // If we have the Total Deposit line, just save this into the special variable // of the Total donation (saved only in this ExcelReader) for verification if (currentDoner.Name.ToLower().Contains("total deposit")) { if (Double.TryParse(donationRow[1].ToString(), out amount) && amount > 0.0) { currentDonation.DepositTotal = amount; Total.AddDonation(currentDoner, currentDonation); break; // This is a break because the Total Deposit row should be the last one with real // data on it. everything after is used for book-keeping... } } foreach (DataColumn columnCategory in sheet.Columns) { // Trimming the column names from spreadsheet; they could contain extra spaces // or they could contain "/" (hack but ok) string name = columnCategory.ColumnName.ToLower().Replace(" ", String.Empty); name = name.Replace("/", String.Empty); string strCellValue = donationRow[columnCategory].ToString(); // don't bother with member column if (name.Equals("names") || String.IsNullOrWhiteSpace(strCellValue)) continue; if (name.Equals("specifyother")) { currentDonation.OtherCategory = strCellValue; continue; } // look for the column extracted from the spreadsheet in the known category list var donationCategory = from Donation.Category c in Enum.GetValues(typeof(Donation.Category)) where c.ToString().ToLower().Equals(name) select c; // Extract out the value from the cell if (Double.TryParse(strCellValue, out amount) && (donationCategory.Count() == 1)) { if (currentDoner.Name.ToLower().Contains("sunday school")) { // Always add donations for Sunday School under the Sunday School category, and keep it a running total currentDonation.Add(Donation.Category.SundaySchool, currentDonation.Get(Donation.Category.SundaySchool) + amount); } else { currentDonation.Add(donationCategory.First(), amount); } } else { logger.WriteError($"Unable to parse column or value from spreadsheet. Sheet: {sheet.TableName}, " + $"Column: {columnCategory.ColumnName}, Cell: {strCellValue}"); } // always add amount to running total currentDonation.SummarizedTotal += amount; } // Special "doner" is the total line. Save this the current object in the Total donation if (currentDoner.Name.ToLower().Equals("grand totals") && currentDonation.HasDonations()) { Total.AddDonation(currentDoner, currentDonation); continue; } if (currentDonation.HasDonations()) { donationData.AddDonation(currentDoner, currentDonation); } } } logger.WriteInfo("INFO: Done reading file\n"); return donationData; }
private static void TestPrintDonationsOfAaron(DonerCollection data) { List<Doner> doner = data.GetAllDoners().Where(d => d.Name.Contains("Aaron")).ToList(); foreach (var item in doner) { logger.WriteInfo("Printing Donations for {0}", item.Name); data.GetDonationsOfDoner(item).ForEach(donation => donation.PrintDonations()); double donationTotal = 0.0; logger.WriteInfo("Total of all donations is ${0}", data.GetDonationsOfDoner(item).Aggregate(donationTotal, (total, variant) => total + variant.CalculateTotal())); logger.WriteInfo("Processing Doner: {0} ....", item.Name); ExcelWriter writer = new ExcelWriter(String.Format("{0}_Donations2010.xlsx", item.Name), FinanceApplication.ExcelWriter.ReportType.YearlyDonerReport); writer.Write(data.GetDonationsOfDoner(item), item); } }
private static bool TestExcelWriter(ref DonerCollection data) { List<Doner> doners = data.GetAllDoners().Where( d => d.Name.ToLower().Contains("aaron")).ToList(); foreach (var item in doners) { logger.WriteInfo("Processing Doner: {0} ....", item.Name); ExcelWriter writer = new ExcelWriter(String.Format("{0}_Donations{1}.xlsx", item.Name, reportingYear), FinanceApplication.ExcelWriter.ReportType.YearlyDonerReport); writer.Write(data.GetDonationsOfDoner(item), item); } return true; }
private static void TestDonationQueries() { string[] files = { @"C:\temp\TestData\03-07-2010.xls", @"C:\temp\TestData\03-8-2010.xls", @"C:\temp\TestData\2-28-2010.xls", @"C:\temp\TestData\8-1-2010.xls", @"C:\temp\TestData\09-01-2010.xls"}; DonerCollection data = new DonerCollection(); ReadExcel(files, data); logger.WriteInfo("*************ALL DATA START******************"); data.Print(); logger.WriteInfo("*************ALL DATA END ******************"); logger.WriteInfo("*************ALL DONERS START******************"); data.PrintDoners(); logger.WriteInfo("*************ALL DONERS END ******************"); logger.WriteInfo("*************ALL DONATIONS FOR FIRST START******************"); data.GetDonationsOfDoner(data.GetAllDoners().First()).ForEach( d => d.PrintDonations() ); logger.WriteInfo("*************ALL DONATIONS FOR FIRST END ******************"); //logger.WriteInfo("*************ALL DONATIONS FOR FIRST START******************"); //data.GetCategoryDonations(Donation.Category.Tithes).ForEach(d => d.PrintDonations()); //logger.WriteInfo("*************ALL DONATIONS FOR FIRST END ******************"); logger.WriteInfo("*************ALL TITHES START******************"); data.GetDonationsByCategory(Donation.Category.Tithes).ForEach(d => d.PrintDonations()); logger.WriteInfo("*************ALL TITHES END ******************"); logger.WriteInfo("*************RANGE TITHES START******************"); data.GetDonationsByCategory(Donation.Category.Tithes, new DateTime(2010, 02, 01), new DateTime(2010, 04, 01)).ForEach(d => d.PrintDonations()); logger.WriteInfo("*************RANGE TITHES END ******************"); logger.WriteInfo("*************ALL DONATIONS FOR FEB START******************"); data.GetAllDonersAndDonations(new DateTime(2010, 02, 01), new DateTime(2010, 02, 29)) .ForEach(d => { Console.WriteLine( "Name: {0}", ((Doner)d.Key).Name); ((Donation)d.Value).PrintDonations(); } ); logger.WriteInfo("*************ALL DONATIONS FOR FEB END ******************"); }
private static void TestMerge() { string[] files = { @"TestData\1-2-2009.xls" }; DonerCollection data = new DonerCollection(); ReadExcel(files, data); logger.WriteInfo("*************TIME TO MERGE******************"); List<Doner> doners = data.GetAllDoners(); data.MergeDoners(doners.First(), doners.Last()); data.Print(); }
private static void ReadExcel(string[] files, DonerCollection data) { List<string> ErrorFiles = new List<string>(); foreach (var item in files) { logger.WriteInfo("\n___________________________________START READ__________________________________________"); ExcelFileReader xRead = new ExcelFileReader(item, reportingYear); DonerCollection currentSheet = xRead.Deserialize(); //currentSheet.Print(); double currentFileCalcTotal = xRead.Total.CalculateTotal(); double currentFileSumTotal = xRead.Total.SummarizedTotal(); double currentFileDepTotal = xRead.Total.DepositTotal(); double collectionCalcTotal = currentSheet.CalculateTotal(); double collectionSumTotal = currentSheet.SummarizedTotal(); // Run some validation to compare all the donations received per user against the "Totals" row and also // against a running total of values read (regardless of their category) if (!CompareDbls(currentFileCalcTotal, collectionCalcTotal) || !CompareDbls(collectionCalcTotal, currentFileDepTotal) || !CompareDbls(currentFileDepTotal, currentFileSumTotal) || !CompareDbls(currentFileSumTotal, collectionSumTotal)) { logger.WriteInfo("\tSpreadsheet Calculated Total: {0:C}", currentFileCalcTotal); logger.WriteInfo("\tCollections Data Calculated Total: {0:C}", collectionCalcTotal); logger.WriteInfo("\tSpreadsheet Total Deposit Row: {0:C}", currentFileDepTotal); logger.WriteInfo("\tSpreadsheet Grand Totals Row: {0:C}", currentFileSumTotal); logger.WriteInfo("\tCollections Data Grant Totals Row: {0:C}", collectionSumTotal); logger.WriteError("Totals do not add up correctly. Check the file for consistencies: {0}", item); ErrorFiles.Add(item); } logger.WriteInfo("\n"); xRead.Close(); double dataCollectionCalcTotalBefore = data.CalculateTotal(); data.MergeDonerCollection(currentSheet); double dataCollectionCalcTotalAfter = data.CalculateTotal(); // One last validation to make sure the data merged into the overall collection maintains a consistent summary if (!CompareDbls(dataCollectionCalcTotalAfter, (dataCollectionCalcTotalBefore + collectionCalcTotal))) { logger.WriteInfo("INFO: Post Merge"); logger.WriteInfo("\tCalculated Total in Data Collection before Merge: {0}", dataCollectionCalcTotalBefore); logger.WriteInfo("\tCalculated Total from the new current Collection: {0}", collectionCalcTotal); logger.WriteInfo("\tCalculated Total in Data Collection after Merge: {0}", dataCollectionCalcTotalAfter); logger.WriteInfo("Problem occurred after merge. Data did not propagate correctly"); } logger.WriteInfo("___________________________________FINISH READ___________________________________________\n"); } logger.WriteInfo("___________________________________START SUMMARY OF ALL DATA______________________________\n"); //data.Print(); logger.WriteInfo("___________________________________END SUMMARY____________________________________________\n"); logger.WriteInfo("Summary of Results"); logger.WriteInfo("Below is the list of filenames which had a problem importing: "); ErrorFiles.ForEach(file => logger.WriteInfo("\t {0}", file)); if (ErrorFiles.Count() == 0) logger.WriteInfo("\t No Files..."); }