public double Write(List<Donation> donations, Doner doner)
{
if (Report == ReportType.MonthyTally)
return WriteMonthlyTally(donations);
else if (Report == ReportType.YearlyDonerReport)
return WriteYearlyDonerReport(donations, doner);
else
return 0.0;
}
private void AddDoner(Doner newDoner)
{
if (IsValidDoner(newDoner) &&
!data.ContainsKey(newDoner))
{
data.Add(newDoner, new List<Donation>());
}
else
{
throw new ArgumentOutOfRangeException("Doner is not acceptable. Why???");
}
}
public void AddDonation(Doner doner, ICollection<Donation> donation)
{
// TODO: once we get real IDs coming from a DB, convert this to use IDs instead of Name
var q = from d in data.Keys
where d.Name == doner.Name
select d;
Doner newDoner;
if (q.Count() == 0)
{
AddDoner(doner);
newDoner = doner;
}
else
{
newDoner = q.First();
}
data[newDoner].AddRange(donation);
}
/// <summary>
/// Expecting to get a list of donation for a single doner
/// </summary>
/// <param name="donations"></param>
/// <returns></returns>
private double WriteYearlyDonerReport(List<Donation> donations, Doner doner)
{
double total = 0.0;
try
{
if (oApplication == null)
{
logger.WriteError("Unable to start an excel sheet");
return total;
}
oApplication.Visible = false;
oWorkbook = oApplication.Workbooks.Add(Type.Missing);
oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkbook.Worksheets[1];
FormatFont();
int maxRowDepth = 0;
int donationStartingRow = 10;
MergeColumns(1, 1, 10); oWorksheet.Cells[1, 1] = "Christ Gospel Church of Tacoma WA";
MergeColumns(2, 1, 10); oWorksheet.Cells[2, 1] = "3909 Steilacoom Blvd SW";
MergeColumns(3, 1, 10); oWorksheet.Cells[3, 1] = "Lakewood, WA 98499";
MergeColumns(4, 1, 10); oWorksheet.Cells[4, 1] = "(253) 584-3904";
MergeColumns(6, 4, 8); oWorksheet.Cells[6, 4] = String.Format("As of {0:dd/MM/yyyy}", donations.Last().DonationTime.AddMonths(1).AddDays(-1.0));
MergeColumns(8, 1, 10); oWorksheet.Cells[8, 1] = String.Format("{0}:", doner.Name);
// Fill in all the columns (categories) with the donations
foreach (Donation.Category currentCategory in Enum.GetValues(typeof(Donation.Category)))
{
int row = donationStartingRow;
oWorksheet.Cells[row, (int)currentCategory] = currentCategory.ToString();
foreach (var item in donations)
{
string value = item.Get(currentCategory).ToString();
if (value.Equals("0"))
value = String.Empty;
oWorksheet.Cells[++row, (int)currentCategory] = value;
}
// keep a running total of how deep we go (rows) into the spreadsheet
maxRowDepth = row > maxRowDepth ? row : maxRowDepth;
}
// Fill out the first column
int rowNum = donationStartingRow;
oWorksheet.Cells[rowNum, 1] = "Date";
foreach (var item in donations)
{
oWorksheet.Cells[++rowNum, 1] = String.Format("{0:MM/yyyy}", item.DonationTime);
}
// donationStartingRow + 1 == where the actual donations start (first row is column name)
int totalsRow = CalculateTotals(donationStartingRow+1, donationStartingRow + 1 + donations.Count , out total);
// Some formatting of Donation Name row (font and bold)
oWorksheet.Rows[donationStartingRow, Type.Missing].Font.Size = 9;
oWorksheet.Rows[donationStartingRow, Type.Missing].Font.Bold = true;
// Totals row bold.
oWorksheet.Cells[totalsRow, Type.Missing].Font.Bold = true;
rowNum = totalsRow + 4;
MergeColumns(++rowNum, 1, 13); oWorksheet.Cells[rowNum, 1] =
"The goods or services that Christ Gospel Church of Tacoma provided in return for your contribution consisted entirely of intangible religious benefits.";
++rowNum; ++rowNum;
oWorksheet.Cells[rowNum, 1] = "Sincerely,";
oWorksheet.Cells[++rowNum, 1] = "Treasury Department";
int lastCol = (int)Donation.Category.Other + 1;
SetPrintArea(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[rowNum, lastCol]]);
}
catch (Exception e)
{
logger.WriteError("Failure while writing excel file. Message: {0}, Stack: {1}", e.Message, e.StackTrace);
}
finally
{
Cleanup();
}
return total;
}
public void AddDonation(Doner doner, Donation donation)
{
List<Donation> singleDonation = new List<Donation>();
singleDonation.Add(donation);
AddDonation(doner, singleDonation);
}
public void MergeDoners(Doner destinationDoner, Doner sourceDoner)
{
if (!data.ContainsKey(destinationDoner) || !data.ContainsKey(sourceDoner))
{
return; // Can't merge if both aren't in the collection
}
AddDonation(destinationDoner, data[sourceDoner]);
data.Remove(sourceDoner);
}
private bool IsValidDoner(Doner newDoner)
{
return true;
}
public List<Donation> GetDonationsOfDonerByMonth(Doner doner, int Year)
{
List<Donation> donations = new List<Donation>();
for (int month = 1; month < 13; month++)
{
DateTime startDT = new DateTime(Year, month, 1);
Donation monthDonation = new Donation(startDT);
List<Donation> donationsForCurrentMonth = GetDonationsOfDoner(doner, startDT, startDT.AddMonths(1));
foreach (Donation.Category item in Enum.GetValues(typeof(Donation.Category)))
{
double donationForCategory = 0.0;
donationForCategory = donationsForCurrentMonth
.Aggregate(donationForCategory, (runningTotal, variant) => runningTotal + variant.Get(item));
monthDonation.Add(item, donationForCategory);
}
donations.Add(monthDonation);
}
return donations;
}
public List<Donation> GetDonationsOfDoner(Doner doner, DateTime start, DateTime end)
{
return GetDonationsOfDoner(doner).Where(d => d.DonationTime >= start && d.DonationTime < end).OrderBy(item => item.DonationTime).ToList();
}
public List<Donation> GetDonationsOfDoner(Doner doner)
{
return data.ContainsKey(doner) ? data[doner].OrderBy(item => item.DonationTime).ToList() : new List<Donation>();
}
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 Test()
{
Doner me = new Doner("Aaron Swerdlin");
Donation money = new Donation();
money.Add(Donation.Category.Tithes, 22.00);
money.Add(Donation.Category.FirstFruits, 23.900);
money.Add(Donation.Category.Alms, 20.10);
money.PrintDonations();
money.Add(Donation.Category.Tithes, 1100.00);
money.PrintDonations();
logger.WriteInfo("Totals: {0}: ", money.CalculateTotal());
}
