public static string PrintGiftReceipt(
String AGiftCurrency,
String ADonorShortName,
Int64 ADonorKey,
TPartnerClass ADonorClass,
AGiftTable GiftsThisDonor,
string AHTMLTemplateFilename
)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
string HtmlDoc;
try
{
string LocalCountryCode = TAddressTools.GetCountryCodeFromSiteLedger(Transaction);
HtmlDoc = FormatHtmlReceipt(
ADonorShortName,
ADonorKey,
ADonorClass,
AGiftCurrency,
LocalCountryCode,
GiftsThisDonor,
AHTMLTemplateFilename,
Transaction);
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(HtmlDoc);
}
public static string GetCountryCodeFromSiteLedger()
{
bool NewTransaction;
TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
string CountryCode = TAddressTools.GetCountryCodeFromSiteLedger(ReadTransaction);
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
return(CountryCode);
}
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);
}
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();
}
}
/// <summary>
/// Parses certain p_partner_location data columns' content into a data structure that is p_parnter_attribute
/// representation.
/// </summary>
/// <remarks>Similar to code found in \csharp\ICT\BuildTools\DataDumpPetra2\FixData.cs, Method 'FixData',
/// in the code section that starts with the comment 'Process p_partner_location records and migrate certain values
/// of p_partner_location records to 'Contact Detail' records'.</remarks>
/// <param name="AMainDS">Typed DataSet that holds the p_partner_location records that are to be parsed.</param>
/// <param name="ATransaction">Instantiated DB Transaction.</param>
public static void ParsePartnerLocationsForContactDetails(PartnerImportExportTDS AMainDS, TDBTransaction ATransaction)
{
DataTable PartnerLocationsDT;
DataRow NewPartnerLocationDR;
string TelephoneNumber = String.Empty;
string FaxNumber = String.Empty;
string PhoneExtension;
string FaxExtension;
// collect the partner classes
foreach (PPartnerRow PartnerDR in AMainDS.PPartner.Rows)
{
TPartnerContactDetails.PartnerClassInformation[PartnerDR.PartnerKey] = PartnerDR.PartnerClass;
}
SortedList <long, DataTable> PartnerLocationsTables = new SortedList <long, DataTable>();
for (int counter = 0; counter < TPartnerContactDetails.NumberOfTables; counter++)
{
PartnerLocationsTables[counter] = TPartnerContactDetails.BestAddressHelper.GetNewPPartnerLocationTableInstance();
}
TPartnerContactDetails.PartnerLocationRecords = PartnerLocationsTables;
foreach (PPartnerLocationRow PartnerLocationDR in AMainDS.PPartnerLocation.Rows)
{
PartnerLocationsDT = PartnerLocationsTables[Math.Abs(PartnerLocationDR.PartnerKey) % TPartnerContactDetails.NumberOfTables];
DataRow LocationDR = AMainDS.PLocation.Rows.Find(new object[] { PartnerLocationDR.SiteKey, PartnerLocationDR.LocationKey });
// Phone Extension: Ignore if value in the dumped data is either null or 0
if (PartnerLocationDR.IsExtensionNull())
{
PhoneExtension = String.Empty;
}
PhoneExtension = PartnerLocationDR.Extension.ToString();
if (PhoneExtension == "0")
{
PhoneExtension = String.Empty;
}
// Fax Extension: Ignore if value in the dumped data is either null or 0
if (PartnerLocationDR.IsFaxExtensionNull())
{
FaxExtension = String.Empty;
}
FaxExtension = PartnerLocationDR.FaxExtension.ToString();
if (FaxExtension == "0")
{
FaxExtension = String.Empty;
}
if (!PartnerLocationDR.IsTelephoneNumberNull())
{
// Concatenate Phone Number and Phone Extension ONLY if both of them aren't null and Phone Extension isn't 0 either.
TelephoneNumber = PartnerLocationDR.TelephoneNumber + PhoneExtension;
}
if (!PartnerLocationDR.IsFaxNumberNull())
{
// Concatenate Fax Number and Fax Extension ONLY if both of them aren't null and Fax Extension isn't 0 either.
FaxNumber = PartnerLocationDR.FaxNumber + FaxExtension;
}
// Create representation of key data of the p_partner_location row and add it to the TPartnerContactDetails.PartnerLocationRecords Data Structure
NewPartnerLocationDR = PartnerLocationsDT.NewRow();
NewPartnerLocationDR["p_partner_key_n"] = PartnerLocationDR.PartnerKey;
NewPartnerLocationDR["p_site_key_n"] = PartnerLocationDR.SiteKey;
NewPartnerLocationDR["p_location_key_i"] = PartnerLocationDR.LocationKey;
if (!PartnerLocationDR.IsDateEffectiveNull())
{
NewPartnerLocationDR["p_date_effective_d"] = PartnerLocationDR.DateEffective;
}
else
{
PartnerLocationDR.SetDateEffectiveNull();
}
if (!PartnerLocationDR.IsDateGoodUntilNull())
{
NewPartnerLocationDR["p_date_good_until_d"] = PartnerLocationDR.DateGoodUntil;
}
else
{
PartnerLocationDR.SetDateGoodUntilNull();
}
NewPartnerLocationDR["p_location_type_c"] = PartnerLocationDR.LocationType;
NewPartnerLocationDR["p_send_mail_l"] = PartnerLocationDR.SendMail;
NewPartnerLocationDR["p_telephone_number_c"] = TelephoneNumber;
NewPartnerLocationDR["p_fax_number_c"] = FaxNumber;
NewPartnerLocationDR["p_mobile_number_c"] = PartnerLocationDR.MobileNumber;
NewPartnerLocationDR["p_alternate_telephone_c"] = PartnerLocationDR.AlternateTelephone;
NewPartnerLocationDR["p_email_address_c"] = PartnerLocationDR.EmailAddress;
NewPartnerLocationDR["p_url_c"] = PartnerLocationDR.Url;
NewPartnerLocationDR["p_value_country_c"] = LocationDR["p_country_code_c"];
PartnerLocationsDT.Rows.Add(NewPartnerLocationDR);
}
// get data for entire country table
PCountryTable CountryTable = PCountryAccess.LoadAll(ATransaction);
string InternatAccessCode = null;
string SiteCountryCode = TAddressTools.GetCountryCodeFromSiteLedger(ATransaction);
DataRow SiteCountryRow = CountryTable.Rows.Find(SiteCountryCode);
// get InternatAccessCode for site country
if (SiteCountryRow != null)
{
InternatAccessCode = SiteCountryRow[PCountryTable.GetInternatAccessCodeDBName()].ToString();
}
TPartnerContactDetails.CreateContactDetailsRow = CreatePartnerContactDetailRecord;
TPartnerContactDetails.EmptyStringIndicator = String.Empty;
TPartnerContactDetails.PartnerAttributeHoldingDataSet = AMainDS;
TPartnerContactDetails.CountryTable = CountryTable;
TPartnerContactDetails.SiteCountryCode = SiteCountryCode;
TPartnerContactDetails.SiteInternatAccessCode = InternatAccessCode;
TPartnerContactDetails.PopulatePPartnerAttribute();
Ict.Petra.Shared.MPartner.Calculations.DeterminePartnerContactDetailAttributes(AMainDS.PPartnerAttribute);
}