public static string PrintReceipts(int ALedgerNumber, DataTable AGiftTbl, string AHTMLTemplateFilename)
{
string HtmlDoc = "";
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
SortedList <Int64, AGiftTable> GiftsPerDonor = new SortedList <Int64, AGiftTable>();
SortedList <Int64, TempDonorInfo> DonorInfo = new SortedList <Int64, TempDonorInfo>();
try
{
string LocalCountryCode = TAddressTools.GetCountryCodeFromSiteLedger(Transaction);
foreach (DataRow Row in AGiftTbl.Rows)
{
String SqlQuery = "SELECT DISTINCT " +
"a_date_entered_d AS DateEntered," +
"p_partner_short_name_c AS Donor," +
"p_donor_key_n AS DonorKey," +
"p_partner_class_c AS DonorClass," +
"a_reference_c AS Reference, " +
"a_currency_code_c AS GiftCurrency " +
"FROM PUB_a_gift LEFT JOIN PUB_p_partner on PUB_a_gift.p_donor_key_n = PUB_p_partner.p_partner_key_n " +
"LEFT JOIN PUB_a_gift_batch ON PUB_a_gift.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i AND PUB_a_gift.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i "
+
"WHERE PUB_a_gift.a_ledger_number_i=" + ALedgerNumber +
" AND PUB_a_gift.a_batch_number_i=" + Row["BatchNumber"] +
" AND PUB_a_gift.a_gift_transaction_number_i=" + Row["TransactionNumber"];
DataRow TempRow = DBAccess.GDBAccessObj.SelectDT(SqlQuery, "UnreceiptedGiftsTbl", Transaction).Rows[0];
Int64 DonorKey = Convert.ToInt64(TempRow["DonorKey"]);
//
// I need to merge any rows that have the same donor.
//
if (!GiftsPerDonor.ContainsKey(DonorKey))
{
GiftsPerDonor.Add(DonorKey, new AGiftTable());
DonorInfo.Add(DonorKey, new TempDonorInfo());
}
TempDonorInfo DonorRow = DonorInfo[DonorKey];
DonorRow.DonorShortName = TempRow["Donor"].ToString();
DonorRow.DonorClass = SharedTypes.PartnerClassStringToEnum(TempRow["DonorClass"].ToString());
DonorRow.GiftCurrency = TempRow["GiftCurrency"].ToString();
DonorRow.DateEntered = Convert.ToDateTime(TempRow["DateEntered"]);
AGiftRow GiftRow = GiftsPerDonor[DonorKey].NewRowTyped();
GiftRow.LedgerNumber = ALedgerNumber;
GiftRow.BatchNumber = Convert.ToInt32(Row["BatchNumber"]);
GiftRow.GiftTransactionNumber = Convert.ToInt32(Row["TransactionNumber"]);
GiftRow.Reference = TempRow["Reference"].ToString();
GiftRow.DateEntered = Convert.ToDateTime(TempRow["DateEntered"]);
GiftsPerDonor[DonorKey].Rows.Add(GiftRow);
} // foreach Row
foreach (Int64 DonorKey in GiftsPerDonor.Keys)
{
TempDonorInfo DonorRow = DonorInfo[DonorKey];
string PageHtml = FormatHtmlReceipt(
DonorRow.DonorShortName,
DonorKey,
DonorRow.DonorClass,
DonorRow.GiftCurrency,
LocalCountryCode,
GiftsPerDonor[DonorKey],
AHTMLTemplateFilename,
Transaction);
TFormLettersTools.AttachNextPage(ref HtmlDoc, PageHtml);
} // foreach DonorKey
TFormLettersTools.CloseDocument(ref HtmlDoc);
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(HtmlDoc);
}
/// <summary>
/// Print a receipt for each gift (one page for each donor) in the batch
/// </summary>
/// <param name="AGiftTDS"></param>
public void PrintGiftBatchReceipts(GiftBatchTDS AGiftTDS)
{
AGiftBatchRow GiftBatchRow = AGiftTDS.AGiftBatch[0];
DataView GiftView = new DataView(AGiftTDS.AGift);
//AGiftTDS.AGift.DefaultView.RowFilter
GiftView.RowFilter = String.Format("{0}={1} and {2}={3}",
AGiftTable.GetLedgerNumberDBName(), GiftBatchRow.LedgerNumber,
AGiftTable.GetBatchNumberDBName(), GiftBatchRow.BatchNumber);
String ReceiptedDonorsList = "";
List <Int32> ReceiptedGiftTransactions = new List <Int32>();
SortedList <Int64, AGiftTable> GiftsPerDonor = new SortedList <Int64, AGiftTable>();
foreach (DataRowView rv in GiftView)
{
AGiftRow GiftRow = (AGiftRow)rv.Row;
bool ReceiptEachGift;
String ReceiptLetterFrequency;
bool EmailGiftStatement;
bool AnonymousDonor;
TRemote.MPartner.Partner.ServerLookups.WebConnectors.GetPartnerReceiptingInfo(
GiftRow.DonorKey,
out ReceiptEachGift,
out ReceiptLetterFrequency,
out EmailGiftStatement,
out AnonymousDonor);
if (ReceiptEachGift)
{
// I want to print a receipt for this gift,
// but if there's already one queued for this donor,
// I'll add this gift onto the existing receipt.
if (!GiftsPerDonor.ContainsKey(GiftRow.DonorKey))
{
GiftsPerDonor.Add(GiftRow.DonorKey, new AGiftTable());
}
AGiftRow NewRow = GiftsPerDonor[GiftRow.DonorKey].NewRowTyped();
DataUtilities.CopyAllColumnValues(GiftRow, NewRow);
GiftsPerDonor[GiftRow.DonorKey].Rows.Add(NewRow);
} // if receipt required
} // foreach gift
String HtmlDoc = "";
OpenFileDialog DialogOpen = new OpenFileDialog();
if (Directory.Exists(TAppSettingsManager.GetValue("Formletters.Path")))
{
DialogOpen.InitialDirectory = TAppSettingsManager.GetValue("Formletters.Path");
}
DialogOpen.Filter = Catalog.GetString("HTML file (*.html)|*.html;*.htm");
DialogOpen.RestoreDirectory = true;
DialogOpen.Title = Catalog.GetString("Select the template for the gift receipt");
if (DialogOpen.ShowDialog() != DialogResult.OK)
{
return;
}
string HTMLTemplateFilename = DialogOpen.FileName;
foreach (Int64 DonorKey in GiftsPerDonor.Keys)
{
String DonorShortName;
TPartnerClass DonorClass;
TRemote.MPartner.Partner.ServerLookups.WebConnectors.GetPartnerShortName(DonorKey, out DonorShortName, out DonorClass);
DonorShortName = Calculations.FormatShortName(DonorShortName, eShortNameFormat.eReverseShortname);
string HtmlPage = TRemote.MFinance.Gift.WebConnectors.PrintGiftReceipt(
GiftBatchRow.CurrencyCode,
DonorShortName,
DonorKey,
DonorClass,
GiftsPerDonor[DonorKey],
HTMLTemplateFilename
);
TFormLettersTools.AttachNextPage(ref HtmlDoc, HtmlPage);
ReceiptedDonorsList += (DonorShortName + "\r\n");
foreach (AGiftRow GiftRow in GiftsPerDonor[DonorKey].Rows)
{
ReceiptedGiftTransactions.Add(GiftRow.GiftTransactionNumber);
}
}
TFormLettersTools.CloseDocument(ref HtmlDoc);
if (ReceiptedGiftTransactions.Count > 0)
{
TFrmReceiptControl.PreviewOrPrint(HtmlDoc);
if (MessageBox.Show(
Catalog.GetString(
"Press OK if receipts to these recipients were printed correctly.\r\nThe gifts will be marked as receipted.\r\n") +
ReceiptedDonorsList,
Catalog.GetString("Receipt Printing"),
MessageBoxButtons.OKCancel) == DialogResult.OK)
{
foreach (Int32 Trans in ReceiptedGiftTransactions)
{
TRemote.MFinance.Gift.WebConnectors.MarkReceiptsPrinted(
GiftBatchRow.LedgerNumber,
GiftBatchRow.BatchNumber,
Trans);
}
}
}
}
public static string CreateAnnualGiftReceipts(Int32 ALedgerNumber,
DateTime AStartDate,
DateTime AEndDate,
string AHTMLTemplate,
bool ADeceasedFirst = false,
string AExtract = null,
Int64 ADonorKey = 0)
{
TLanguageCulture.LoadLanguageAndCulture();
// get BaseCurrency
System.Type typeofTable = null;
TCacheable CachePopulator = new TCacheable();
ALedgerTable LedgerTable = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails,
"",
false,
ALedgerNumber,
out typeofTable);
string BaseCurrency = LedgerTable[0].BaseCurrency;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
// get the local country code
string LocalCountryCode = TAddressTools.GetCountryCodeFromSiteLedger(Transaction);
DataTable donorkeys = new DataTable();
string SqlStmt = "";
if (ADonorKey != 0)
{
TPartnerClass Class;
string ShortName;
TPartnerServerLookups.GetPartnerShortName(ADonorKey, out ShortName, out Class);
donorkeys.Columns.Add(new DataColumn("DonorKey"));
donorkeys.Columns.Add(new DataColumn("DonorName"));
DataRow SingleRow = donorkeys.NewRow();
SingleRow[0] = ADonorKey;
SingleRow[1] = ShortName;
donorkeys.Rows.Add(SingleRow);
}
else
{
SortedList <string, string> Defines = new SortedList <string, string>();
if (!string.IsNullOrEmpty(AExtract))
{
Defines.Add("BYEXTRACT", string.Empty);
}
// first get all donors in the given date range
SqlStmt = TDataBase.ReadSqlFile("Gift.ReceiptPrinting.GetDonors.sql", Defines);
OdbcParameter[] parameters = new OdbcParameter[4];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("StartDate", OdbcType.Date);
parameters[1].Value = AStartDate;
parameters[2] = new OdbcParameter("EndDate", OdbcType.Date);
parameters[2].Value = AEndDate;
parameters[3] = new OdbcParameter("Extract", OdbcType.VarChar);
parameters[3].Value = AExtract;
donorkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "DonorKeys", Transaction, parameters);
// put deceased partner's at the front (still sorted alphabetically)
if (ADeceasedFirst)
{
// create a new datatable with same structure as donorkeys
DataTable temp = donorkeys.Clone();
temp.Clear();
// add deceased donors to the temp table and delete from donorkeys
for (int i = 0; i < donorkeys.Rows.Count; i++)
{
if (SharedTypes.StdPartnerStatusCodeStringToEnum(donorkeys.Rows[i][2].ToString()) == TStdPartnerStatusCode.spscDIED)
{
temp.Rows.Add((object[])donorkeys.Rows[i].ItemArray.Clone());
donorkeys.Rows[i].Delete();
}
}
// add remaining partners to temp table
donorkeys.AcceptChanges();
temp.Merge(donorkeys);
donorkeys = temp;
}
}
string ResultDocument = "";
SqlStmt = TDataBase.ReadSqlFile("Gift.ReceiptPrinting.GetDonationsOfDonor.sql");
foreach (DataRow donorrow in donorkeys.Rows)
{
Int64 donorKey = Convert.ToInt64(donorrow[0]);
string donorName = donorrow[1].ToString();
OdbcParameter[] parameters = new OdbcParameter[4];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("StartDate", OdbcType.Date);
parameters[1].Value = AStartDate;
parameters[2] = new OdbcParameter("EndDate", OdbcType.Date);
parameters[2].Value = AEndDate;
parameters[3] = new OdbcParameter("DonorKey", OdbcType.BigInt);
parameters[3].Value = donorKey;
// TODO: should we print each gift detail, or just one row per gift?
DataTable donations = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "Donations", Transaction, parameters);
if (donations.Rows.Count > 0)
{
string letter = FormatLetter(donorKey, donorName, donations, BaseCurrency, AHTMLTemplate, LocalCountryCode, Transaction);
if (TFormLettersTools.AttachNextPage(ref ResultDocument, letter))
{
// TODO: store somewhere that the receipt has been printed?
// TODO also store each receipt with the donor in document management, and in contact management?
}
}
}
TFormLettersTools.CloseDocument(ref ResultDocument);
return(ResultDocument);
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
