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...");
}