public static bool GetBestAddress(Int64 APartnerKey,
out PLocationTable AAddress,
out PPartnerLocationTable APartnerLocation,
out string ACountryNameLocal,
out string AEmailAddress)
{
bool NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
bool ResultValue = false;
try
{
ResultValue = TAddressTools.GetBestAddress(APartnerKey,
out AAddress,
out APartnerLocation,
out ACountryNameLocal,
out AEmailAddress,
Transaction);
}
catch (Exception)
{
throw;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
return(ResultValue);
}
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 bool GetBestAddress(Int64 APartnerKey,
out PLocationTable AAddress,
out string ACountryNameLocal)
{
bool NewTransaction;
TDataBase db = DBAccess.Connect("GetBestAddress");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
bool ResultValue = false;
try
{
ResultValue = TAddressTools.GetBestAddress(APartnerKey,
out AAddress,
out ACountryNameLocal,
Transaction);
}
catch (Exception)
{
throw;
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
return(ResultValue);
}
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);
}
/// <summary>
/// Run the queries and check the results
/// </summary>
/// <param name="StreetLine"></param>
public void ActAndAssert(string StreetLine)
{
string ACountryName;
PLocationTable AAddress = null;
TDBTransaction ATransaction = null;
DataTable DonorAddresses = null;
// Act
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref ATransaction,
delegate
{
TAddressTools.GetBestAddress(TestPartnerKey, out AAddress, out ACountryName, ATransaction);
DonorAddresses = TAddressTools.GetBestAddressForPartners(TestPartnerKey.ToString(), ATransaction);
});
// Assert
Assert.AreEqual(1, AAddress.Rows.Count, "GetBestAddress returned wrong number of addresses");
Assert.AreEqual(1, DonorAddresses.Rows.Count, "GetBestAddressForPartners returned wrong number of addresses");
Assert.AreEqual(TestPartnerKey + " " + StreetLine,
((PLocationRow)AAddress.Rows[0]).StreetName,
"GetBestAddress returned unexpected address");
Assert.AreEqual(((PLocationRow)AAddress.Rows[0]).LocationKey, DonorAddresses.Rows[0]["p_location_key_i"]);
}
public static bool ImportPartnerTaxCodes(
Hashtable AParameters,
String AImportString,
out TVerificationResultCollection AErrorMessages,
out List <string> AOutputLines,
out bool AExtractCreated,
out int AExtractId,
out int AExtractKeyCount,
out int AImportedCodeCount,
out int ADeletedCodeCount,
out int ATaxCodeMismatchCount)
{
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Importing Partner Tax Codes"),
100);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Initialising"),
5);
TVerificationResultCollection errorMessages = new TVerificationResultCollection();
List <string> outputLines = new List <string>();
List <long> partnerTaxCodesImported = new List <long>();
List <long> partnerTaxCodesDeleted = new List <long>();
int taxCodeMismatchCount = 0;
bool extractCreated = false;
int extractId = -1;
int extractKeyCount = 0;
string delimiter = Convert.ToString(AParameters["Delimiter"]);
bool firstRowIsHeader = Convert.ToBoolean(AParameters["FirstRowIsHeader"]);
bool failIfNotPerson = Convert.ToBoolean(AParameters["FailIfNotPerson"]);
bool failIfInvalidPartner = Convert.ToBoolean(AParameters["FailIfInvalidPartner"]);
bool overwriteExistingTaxCode = Convert.ToBoolean(AParameters["OverwriteExistingTaxCode"]);
bool createExtract = Convert.ToBoolean(AParameters["CreateExtract"]);
bool createOutputFile = Convert.ToBoolean(AParameters["CreateOutFile"]);
bool includePartnerDetails = Convert.ToBoolean(AParameters["IncludePartnerDetails"]);
int emptyTaxCodeAction = Convert.ToInt32(AParameters["EmptyTaxCode"]);
string taxCodeType = Convert.ToString(AParameters["TaxCodeType"]);
string extractName = Convert.ToString(AParameters["ExtractName"]);
string extractDescription = Convert.ToString(AParameters["ExtractDescription"]);
StringReader sr = new StringReader(AImportString);
int rowNumber = 0;
bool cancelledByUser = false;
bool doneHeaderRow = (firstRowIsHeader == false);
TDBTransaction transaction = null;
bool submissionOK = false;
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, 3, ref transaction, ref submissionOK,
delegate
{
try
{
PTaxTable taxTable = PTaxAccess.LoadAll(transaction);
string importLine = sr.ReadLine();
int percentDone = 10;
int previousPercentDone = 0;
int initialTextLength = AImportString.Length;
int totalRows = AImportString.Split('\n').Length;
if (delimiter.Length < 1)
{
// should not happen
delimiter = ",";
}
while (importLine != null)
{
rowNumber++;
percentDone = 10 + ((rowNumber * 80) / totalRows);
// skip empty lines and commented lines
if ((importLine.Trim().Length == 0) || importLine.StartsWith("/*") || importLine.StartsWith("#"))
{
importLine = sr.ReadLine();
continue;
}
int numberOfElements = StringHelper.GetCSVList(importLine, delimiter).Count;
if (numberOfElements < 2)
{
// That is a critical error
TVerificationResult errorMsg =
new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, rowNumber),
Catalog.GetString("Wrong number of columns. There must be at least 2"), TResultSeverity.Resv_Critical);
errorMessages.Add(errorMsg);
outputLines.Add(string.Format("\"Wrong number of columns in Line {0}\"", rowNumber));
importLine = sr.ReadLine();
continue;
}
if (!doneHeaderRow)
{
// Skip a header row
doneHeaderRow = true;
importLine = sr.ReadLine();
continue;
}
int preParseMessageCount = errorMessages.Count;
importLine = importLine.TrimStart(' '); // TCommonImport.ImportInt64 does not remove leading spaces before optional quotes
Int64 partnerKey = TCommonImport.ImportInt64(ref importLine, delimiter, null, null, rowNumber, errorMessages, null);
importLine = importLine.TrimStart(' '); // TCommonImport.ImportString does not remove leading spaces before optional quotes
string taxCode = TCommonImport.ImportString(ref importLine, delimiter, null, null, rowNumber, errorMessages, false);
if (errorMessages.Count > preParseMessageCount)
{
outputLines.Add(string.Format("\"Error reading Line {0}\"", rowNumber));
importLine = sr.ReadLine();
continue;
}
// Ok so we have a good key and a code, which might be null
// See if the partner exists
PartnerFindTDS partner = TSimplePartnerFindWebConnector.FindPartners(partnerKey, true);
PartnerFindTDSSearchResultTable result = partner.SearchResult;
if (result.DefaultView.Count == 0)
{
// We don't have the partner
if (failIfInvalidPartner)
{
TVerificationResult errorMsg =
new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, rowNumber),
Catalog.GetString("Could not find the specified partner"), TResultSeverity.Resv_Critical);
errorMessages.Add(errorMsg);
outputLines.Add(string.Format("\"Error\"{0}{1}{0}{0}{0}{0}\"Partner not found\"", delimiter, partnerKey));
}
else
{
outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}{0}{0}{0}\"Partner not found\"", delimiter, partnerKey));
}
importLine = sr.ReadLine();
continue;
}
// We already have this partner
PartnerFindTDSSearchResultRow row = (PartnerFindTDSSearchResultRow)result.DefaultView[0].Row;
string partnerClass = row.PartnerClass;
string partnerDetails = string.Empty;
if (includePartnerDetails)
{
string shortName = row.PartnerShortName;
string bestAddress = string.Empty;
PLocationTable locationTable;
string country;
if (TAddressTools.GetBestAddress(partnerKey, out locationTable, out country, transaction))
{
if ((locationTable != null) && (locationTable.Rows.Count > 0))
{
bestAddress =
Calculations.DetermineLocationString((PLocationRow)locationTable.Rows[0],
Calculations.TPartnerLocationFormatEnum.plfCommaSeparated);
}
}
partnerDetails = string.Format("{0}\"{1}\"{0}\"{2}\"", delimiter, shortName, bestAddress);
}
if (partnerClass != "PERSON")
{
if (failIfNotPerson)
{
TVerificationResult errorMsg =
new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, rowNumber),
Catalog.GetString("Partner is not a PERSON"), TResultSeverity.Resv_Critical);
errorMessages.Add(errorMsg);
outputLines.Add(string.Format("\"Error\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"", delimiter,
partnerKey, taxCode, partnerDetails, "Partner is not a PERSON"));
}
else
{
outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"", delimiter,
partnerKey, taxCode, partnerDetails, "Partner is not a PERSON"));
}
importLine = sr.ReadLine();
continue;
}
bool deleteExistingRow = false;
if (taxCode == null)
{
// what to do if the code is not supplied?
if ((emptyTaxCodeAction == 0) || (emptyTaxCodeAction == 1))
{
if (emptyTaxCodeAction == 0)
{
// Fail
TVerificationResult errorMsg =
new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, rowNumber),
Catalog.GetString("No tax code specified"), TResultSeverity.Resv_Critical);
errorMessages.Add(errorMsg);
outputLines.Add(string.Format("\"Error\"{0}{1}{0}{2}{0}\"{3}\"", delimiter,
partnerKey, partnerDetails, "No tax code specified"));
}
else
{
outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}{2}{0}\"{3}\"", delimiter,
partnerKey, partnerDetails, "No tax code specified"));
}
importLine = sr.ReadLine();
continue;
}
else if (emptyTaxCodeAction == 2)
{
deleteExistingRow = true;
}
else
{
throw new Exception("Developer error! Unknown action for an empty tax code...");
}
}
// See if there is a tax code already for this partner
PTaxRow taxRow = null;
taxTable.DefaultView.RowFilter = string.Format("{0}={1} AND {2}='{3}'",
PTaxTable.GetPartnerKeyDBName(), partnerKey, PTaxTable.GetTaxTypeDBName(), taxCodeType);
if (taxTable.DefaultView.Count == 0)
{
if (deleteExistingRow)
{
// The tax code is null and we can only delete if the row already exists in the tax table
outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"",
delimiter, partnerKey, taxCode, partnerDetails, "Cannot delete. The partner is not in the tax table."));
}
else
{
// Add a new row
taxRow = taxTable.NewRowTyped(true);
taxRow.PartnerKey = partnerKey;
taxRow.TaxType = taxCodeType;
taxRow.TaxRef = taxCode;
taxTable.Rows.Add(taxRow);
outputLines.Add(string.Format("\"Added\"{0}{1}{0}\"{2}\"{3}",
delimiter, partnerKey, taxCode, partnerDetails));
partnerTaxCodesImported.Add(partnerKey);
}
}
else
{
// the row exists already. We expect there to only be one row per tax code type for a given partner
// If partners can have multiple codes of a specified type the code below will need to change!
taxRow = (PTaxRow)taxTable.DefaultView[0].Row;
string currentCode = taxRow.TaxRef;
if (deleteExistingRow)
{
if ((taxRow.RowState != DataRowState.Detached) && (taxRow.RowState != DataRowState.Deleted))
{
taxRow.Delete();
}
outputLines.Add(string.Format("\"Deleted\"{0}{1}{0}\"{2}\"{3}",
delimiter, partnerKey, currentCode, partnerDetails));
partnerTaxCodesDeleted.Add(partnerKey);
}
else
{
// Check if it is the same
if (string.Compare(currentCode, taxCode, false) == 0)
{
// they are the same
outputLines.Add(string.Format("\"Unchanged\"{0}{1}{0}\"{2}\"{3}",
delimiter, partnerKey, taxCode, partnerDetails));
partnerTaxCodesImported.Add(partnerKey); // this counts as an import
}
else
{
// They are different
if (overwriteExistingTaxCode)
{
// Overwrite the old value
taxRow.TaxRef = taxCode;
outputLines.Add(string.Format("\"Modified\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"",
delimiter, partnerKey, taxCode, partnerDetails,
"Previous code was: " + currentCode));
partnerTaxCodesImported.Add(partnerKey);
}
else
{
// Do nothing on this row
outputLines.Add(string.Format("\"Skipped\"{0}{1}{0}\"{2}\"{3}{0}\"{4}\"",
delimiter, partnerKey, currentCode, partnerDetails,
"WARNING!! File and Database codes differ. New code is: " + taxCode));
taxCodeMismatchCount++;
}
}
}
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
cancelledByUser = true;
break;
}
if (errorMessages.HasCriticalErrors && (errorMessages.Count > 100))
{
// This probably means that it is a big file and the user has made the same mistake many times over
break;
}
// Update progress tracker every few percent
if ((percentDone - previousPercentDone) > 3)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString("Importing row {0}"), rowNumber),
(percentDone > 90) ? 90 : percentDone);
previousPercentDone = percentDone;
}
}
importLine = sr.ReadLine();
}
// No more lines in the file - or cancelled by the user
if (cancelledByUser)
{
errorMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation,
"The import was cancelled by the user.", TResultSeverity.Resv_Critical));
outputLines.Add("\"Import cancelled by user\"");
}
if (errorMessages.HasCriticalErrors)
{
submissionOK = false;
partnerTaxCodesImported.Clear();
}
else
{
PTaxAccess.SubmitChanges(taxTable, transaction);
submissionOK = true;
}
// Now create the optional extract
if (submissionOK && (partnerTaxCodesImported.Count > 0) && createExtract)
{
// we have more server work to do so we reset submission OK to false
submissionOK = false;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Creating new Extract ..."),
97);
DataTable table = new DataTable();
table.Columns.Add(new DataColumn());
for (int i = 0; i < partnerTaxCodesImported.Count; i++)
{
DataRow row = table.NewRow();
row[0] = partnerTaxCodesImported[i];
table.Rows.Add(row);
}
List <long> ignoredKeys;
extractCreated = TExtractsHandling.CreateExtractFromListOfPartnerKeys(extractName,
extractDescription,
out extractId,
table,
0,
false,
out extractKeyCount,
out ignoredKeys);
submissionOK = true;
}
}
catch (Exception ex)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Exception Occurred"),
0);
if (TDBExceptionHelper.IsTransactionSerialisationException(ex))
{
errorMessages.Add(new TVerificationResult("ImportPartnerTaxCodes",
ErrorCodeInventory.RetrieveErrCodeInfo(PetraErrorCodes.ERR_DB_SERIALIZATION_EXCEPTION)));
}
else
{
errorMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, rowNumber),
ex.Message, TResultSeverity.Resv_Critical));
}
partnerTaxCodesImported.Clear();
}
finally
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
}
});
AErrorMessages = errorMessages;
AOutputLines = outputLines;
AExtractCreated = extractCreated;
AExtractId = extractId;
AExtractKeyCount = extractKeyCount;
AImportedCodeCount = partnerTaxCodesImported.Count;
ADeletedCodeCount = partnerTaxCodesDeleted.Count;
ATaxCodeMismatchCount = taxCodeMismatchCount;
return(submissionOK);
}
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();
}
}
public static string FormatHtmlReceipt(
String ADonorShortName,
Int64 ADonorKey,
TPartnerClass ADonorClass,
String AGiftCurrency,
string ALocalCountryCode,
AGiftTable AGiftsThisDonor,
string AHTMLTemplateFilename,
TDBTransaction ATransaction)
{
SortedList <string, List <string> > FormValues = new SortedList <string, List <string> >();
// These are the fields that can be printed in the letter:
FormValues.Add("AdresseeShortName", new List <string>());
FormValues.Add("AdresseeTitle", new List <string>());
FormValues.Add("AdresseeFirstName", new List <string>());
FormValues.Add("AdresseeFamilyName", new List <string>());
FormValues.Add("AdresseeStreetAddress", new List <string>());
FormValues.Add("AdresseeAddress3", new List <string>());
FormValues.Add("AdresseeCity", new List <string>());
FormValues.Add("AdresseePostCode", new List <string>());
FormValues.Add("AdresseeCountry", new List <string>());
FormValues.Add("FormattedAddress", new List <string>());
FormValues.Add("DateToday", new List <string>());
FormValues.Add("DateEntered", new List <string>());
FormValues.Add("GiftAmount", new List <string>());
FormValues.Add("GiftCurrency", new List <string>());
FormValues.Add("GiftTxd", new List <string>());
FormValues.Add("RecipientShortName", new List <string>());
FormValues.Add("MotivationDetail", new List <string>());
FormValues.Add("Reference", new List <string>());
FormValues.Add("DonorComment", new List <string>());
FormValues.Add("GiftTotalAmount", new List <string>());
FormValues.Add("GiftTotalCurrency", new List <string>());
FormValues.Add("TxdTotal", new List <string>());
FormValues.Add("NonTxdTotal", new List <string>());
// Donor Name:
FormValues["AdresseeShortName"].Add(ADonorShortName);
if (ADonorClass == TPartnerClass.PERSON)
{
PPersonTable Tbl = PPersonAccess.LoadByPrimaryKey(ADonorKey, ATransaction);
if (Tbl.Rows.Count > 0)
{
FormValues["AdresseeTitle"].Add(Tbl[0].Title);
FormValues["AdresseeFirstName"].Add(Tbl[0].FirstName);
FormValues["AdresseeFamilyName"].Add(Tbl[0].FamilyName);
}
}
else if (ADonorClass == TPartnerClass.FAMILY)
{
PFamilyTable Tbl = PFamilyAccess.LoadByPrimaryKey(ADonorKey, ATransaction);
if (Tbl.Rows.Count > 0)
{
FormValues["AdresseeTitle"].Add(Tbl[0].Title);
FormValues["AdresseeFirstName"].Add(Tbl[0].FirstName);
FormValues["AdresseeFamilyName"].Add(Tbl[0].FamilyName);
}
}
else
{
FormValues["AdresseeFamilyName"].Add(ADonorShortName);
}
FormValues["DateToday"].Add(DateTime.Now.ToString("dd MMMM yyyy"));
// Donor Adress:
PLocationTable Location;
PPartnerLocationTable PartnerLocation;
string CountryName;
string EmailAddress;
if (TAddressTools.GetBestAddress(ADonorKey, out Location, out PartnerLocation, out CountryName, out EmailAddress, ATransaction))
{
PLocationRow LocRow = Location[0];
FormValues["AdresseeStreetAddress"].Add(LocRow.StreetName);
FormValues["AdresseeAddress3"].Add(LocRow.Address3);
FormValues["AdresseeCity"].Add(LocRow.City);
FormValues["AdresseePostCode"].Add(LocRow.PostalCode);
if (LocRow.CountryCode != ALocalCountryCode) // Don't add the Donor's country if it's also my country:
{
FormValues["AdresseeCountry"].Add(CountryName);
}
else
{
LocRow.CountryCode = "";
}
FormValues["FormattedAddress"].Add(Calculations.DetermineLocationString(LocRow,
Calculations.TPartnerLocationFormatEnum.plfHtmlLineBreak));
}
decimal GiftTotal = 0;
decimal TxdTotal = 0;
decimal NonTxdTotal = 0;
// Details per gift:
foreach (AGiftRow GiftRow in AGiftsThisDonor.Rows)
{
String DateEntered = GiftRow.DateEntered.ToString("dd MMM yyyy");
String GiftReference = GiftRow.Reference;
AGiftDetailTable DetailTbl = AGiftDetailAccess.LoadViaAGift(
GiftRow.LedgerNumber, GiftRow.BatchNumber, GiftRow.GiftTransactionNumber, ATransaction);
foreach (AGiftDetailRow DetailRow in DetailTbl.Rows)
{
FormValues["Reference"].Add(GiftReference);
FormValues["DateEntered"].Add(DateEntered);
GiftReference = ""; // Date and Reference are one-per-gift, not per detail
DateEntered = ""; // so if this gift has several details, I'll blank the subsequent lines.
string DonorComment = "";
FormValues["GiftAmount"].Add(StringHelper.FormatUsingCurrencyCode(DetailRow.GiftTransactionAmount, AGiftCurrency));
FormValues["GiftCurrency"].Add(AGiftCurrency);
FormValues["MotivationDetail"].Add(DetailRow.MotivationDetailCode);
GiftTotal += DetailRow.GiftTransactionAmount;
if (DetailRow.TaxDeductible)
{
FormValues["GiftTxd"].Add("Y");
TxdTotal += DetailRow.GiftTransactionAmount;
}
else
{
FormValues["GiftTxd"].Add(" ");
NonTxdTotal += DetailRow.GiftTransactionAmount;
}
// Recipient Short Name:
PPartnerTable RecipientTbl = PPartnerAccess.LoadByPrimaryKey(DetailRow.RecipientKey, ATransaction);
if (RecipientTbl.Rows.Count > 0)
{
String ShortName = Calculations.FormatShortName(RecipientTbl[0].PartnerShortName, eShortNameFormat.eReverseShortname);
FormValues["RecipientShortName"].Add(ShortName);
}
if (DetailRow.CommentOneType == "Donor")
{
DonorComment += DetailRow.GiftCommentOne;
}
if (DetailRow.CommentTwoType == "Donor")
{
if (DonorComment != "")
{
DonorComment += "\r\n";
}
DonorComment += DetailRow.GiftCommentTwo;
}
if (DetailRow.CommentThreeType == "Donor")
{
if (DonorComment != "")
{
DonorComment += "\r\n";
}
DonorComment += DetailRow.GiftCommentThree;
}
if (DonorComment != "")
{
DonorComment = "Comment: " + DonorComment;
}
FormValues["DonorComment"].Add(DonorComment);
} // foreach GiftDetail
} // foreach Gift
FormValues["GiftTotalAmount"].Add(StringHelper.FormatUsingCurrencyCode(GiftTotal, AGiftCurrency));
FormValues["GiftTotalCurrency"].Add(AGiftCurrency);
FormValues["TxdTotal"].Add(StringHelper.FormatUsingCurrencyCode(TxdTotal, AGiftCurrency));
FormValues["NonTxdTotal"].Add(StringHelper.FormatUsingCurrencyCode(NonTxdTotal, AGiftCurrency));
return(TFormLettersTools.PrintSimpleHTMLLetter(AHTMLTemplateFilename, FormValues));
}
/// <summary>
/// Format the letter for the donor with all the gifts
///
/// Can also used for a single receipt.
/// </summary>
/// <returns>One or more html documents, each in its own body tag, for printing with the HTML printer</returns>
private static string FormatLetter(Int64 ADonorKey,
string ADonorName,
DataTable ADonations,
string ABaseCurrency,
string AHTMLTemplate,
string ALedgerCountryCode,
TDBTransaction ATransaction)
{
// get details of the donor, and best address
PLocationTable Location;
PPartnerLocationTable PartnerLocation;
string CountryName;
string EmailAddress;
if (!TAddressTools.GetBestAddress(ADonorKey, out Location, out PartnerLocation, out CountryName, out EmailAddress, ATransaction))
{
return("");
}
bool TaxDeductiblePercentageEnabled = Convert.ToBoolean(
TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE"));
string msg = AHTMLTemplate;
if (ADonorName.Contains(","))
{
msg = msg.Replace("#TITLE", Calculations.FormatShortName(ADonorName, eShortNameFormat.eOnlyTitle));
}
else
{
// organisations have no title
msg = msg.Replace("#TITLE", "");
}
msg = msg.Replace("#NAME", Calculations.FormatShortName(ADonorName, eShortNameFormat.eReverseWithoutTitle));
msg = msg.Replace("#STREETNAME", GetStringOrEmpty(Location[0].StreetName));
msg = msg.Replace("#LOCATION", GetStringOrEmpty(Location[0].Locality));
msg = msg.Replace("#ADDRESS3", GetStringOrEmpty(Location[0].Address3));
msg = msg.Replace("#BUILDING1", GetStringOrEmpty(Location[0].Building1));
msg = msg.Replace("#BUILDING2", GetStringOrEmpty(Location[0].Building2));
msg = msg.Replace("#CITY", GetStringOrEmpty(Location[0].City));
msg = msg.Replace("#POSTALCODE", GetStringOrEmpty(Location[0].PostalCode));
msg = msg.Replace("#DATE", DateTime.Now.ToString("d. MMMM yyyy"));
// according to German Post, there is no country code in front of the post code
// if country code is same for the address of the recipient and this office, then COUNTRYNAME is cleared
if (GetStringOrEmpty(Location[0].CountryCode) != ALedgerCountryCode)
{
msg = msg.Replace("#COUNTRYNAME", CountryName);
}
else
{
msg = msg.Replace("#COUNTRYNAME", "");
}
// recognise detail lines automatically
string RowTemplate;
msg = TPrinterHtml.GetTableRow(msg, "#AMOUNT", out RowTemplate);
string OrigRowTemplate = RowTemplate;
string rowTexts = "";
decimal sum = 0;
decimal sumTaxDeduct = 0;
decimal sumNonDeduct = 0;
decimal prevAmount = 0.0M;
decimal prevAmountTaxDeduct = 0.0M;
decimal prevAmountNonDeduct = 0.0M;
string prevCurrency = String.Empty;
string prevCommentOne = String.Empty;
string prevAccountDesc = String.Empty;
string prevCostCentreDesc = String.Empty;
string prevgifttype = string.Empty;
DateTime prevDateEntered = DateTime.MaxValue;
foreach (DataRow rowGifts in ADonations.Rows)
{
DateTime dateEntered = Convert.ToDateTime(rowGifts["DateEntered"]);
decimal amount = Convert.ToDecimal(rowGifts["TransactionAmount"]);
decimal taxDeductibleAmount = 0;
decimal nonDeductibleAmount = 0;
string currency = rowGifts["Currency"].ToString();
string commentOne = rowGifts["CommentOne"].ToString();
string accountDesc = rowGifts["AccountDesc"].ToString();
string costcentreDesc = rowGifts["CostCentreDesc"].ToString();
string gifttype = rowGifts["GiftType"].ToString();
RowTemplate = OrigRowTemplate;
sum += Convert.ToDecimal(rowGifts["AmountInBaseCurrency"]);
if (TaxDeductiblePercentageEnabled)
{
taxDeductibleAmount = Convert.ToDecimal(rowGifts["TaxDeductibleAmount"]);
nonDeductibleAmount = Convert.ToDecimal(rowGifts["NonDeductibleAmount"]);
sumTaxDeduct += Convert.ToDecimal(rowGifts["TaxDeductibleAmountBase"]);
sumNonDeduct += Convert.ToDecimal(rowGifts["NonDeductibleAmountBase"]);
}
// can we sum up donations on the same date, or do we need to print each detail with the account description?
if (RowTemplate.Contains("#COMMENTONE") || RowTemplate.Contains("#ACCOUNTDESC") || RowTemplate.Contains("#COSTCENTREDESC"))
{
if (gifttype == MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT);
}
else if (gifttype == MFinanceConstants.GIFT_TYPE_GIFT)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND);
}
rowTexts += RowTemplate.
Replace("#DONATIONDATE", dateEntered.ToString("dd.MM.yyyy")).
Replace("#AMOUNTCURRENCY", currency).
Replace("#AMOUNT", StringHelper.FormatUsingCurrencyCode(amount, currency)).
Replace("#TAXDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(taxDeductibleAmount, currency)).
Replace("#TAXNONDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(nonDeductibleAmount, currency)).
Replace("#COMMENTONE", commentOne).
Replace("#ACCOUNTDESC", accountDesc).
Replace("#COSTCENTREDESC", costcentreDesc);
}
else
{
if ((dateEntered != prevDateEntered) && (prevDateEntered != DateTime.MaxValue))
{
if (prevgifttype == MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT);
}
else if (prevgifttype == MFinanceConstants.GIFT_TYPE_GIFT)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND);
}
rowTexts += RowTemplate.
Replace("#DONATIONDATE", prevDateEntered.ToString("dd.MM.yyyy")).
Replace("#AMOUNTCURRENCY", prevCurrency).
Replace("#AMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmount, prevCurrency)).
Replace("#TAXDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmountTaxDeduct, prevCurrency)).
Replace("#TAXNONDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmountNonDeduct, prevCurrency)).
Replace("#COMMENTONE", prevCommentOne).
Replace("#ACCOUNTDESC", prevAccountDesc).
Replace("#COSTCENTREDESC", prevCostCentreDesc);
prevAmount = amount;
if (TaxDeductiblePercentageEnabled)
{
prevAmountTaxDeduct = taxDeductibleAmount;
prevAmountNonDeduct = nonDeductibleAmount;
}
}
else
{
prevAmount += amount;
if (TaxDeductiblePercentageEnabled)
{
prevAmountTaxDeduct += taxDeductibleAmount;
prevAmountNonDeduct += nonDeductibleAmount;
}
}
prevCurrency = currency;
prevDateEntered = dateEntered;
prevCommentOne = commentOne;
prevAccountDesc = accountDesc;
prevCostCentreDesc = costcentreDesc;
prevgifttype = gifttype;
}
}
if (prevDateEntered != DateTime.MaxValue)
{
RowTemplate = OrigRowTemplate;
if (prevgifttype == MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT);
}
else if (prevgifttype == MFinanceConstants.GIFT_TYPE_GIFT)
{
RowTemplate = TPrinterHtml.RemoveDivWithClass(RowTemplate, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND);
}
rowTexts += RowTemplate.
Replace("#DONATIONDATE", prevDateEntered.ToString("dd.MM.yyyy")).
Replace("#AMOUNTCURRENCY", prevCurrency).
Replace("#AMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmount, prevCurrency)).
Replace("#TAXDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmountTaxDeduct, prevCurrency)).
Replace("#TAXNONDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(prevAmountNonDeduct, prevCurrency)).
Replace("#COMMENTONE", prevCommentOne).
Replace("#ACCOUNTDESC", prevAccountDesc).
Replace("#COSTCENTREDESC", prevCostCentreDesc);
prevAmount = 0.0M;
if (TaxDeductiblePercentageEnabled)
{
prevAmountTaxDeduct = 0.0M;
prevAmountNonDeduct = 0.0M;
}
}
msg = msg.Replace("#OVERALLAMOUNTCURRENCY", ABaseCurrency).
Replace("#OVERALLAMOUNT", StringHelper.FormatUsingCurrencyCode(sum, ABaseCurrency)).
Replace("#OVERALLTAXDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(sumTaxDeduct, ABaseCurrency)).
Replace("#OVERALLTAXNONDEDUCTAMOUNT", StringHelper.FormatUsingCurrencyCode(sumNonDeduct, ABaseCurrency));
if ((ADonations.Rows.Count == 1) && msg.Contains("#DONATIONDATE"))
{
// this is a receipt for just one gift
msg = msg.Replace("#DONATIONDATE", Convert.ToDateTime(ADonations.Rows[0]["DateEntered"]).ToString("dd.MM.yyyy"));
}
// TODO allow other currencies. use a_currency table, and base currency
msg = msg.Replace("#TOTALAMOUNTINWORDS", NumberToWords.AmountToWords(sum, "Euro", "Cent")).
Replace("#TOTALTAXDEDUCTAMOUNTINWORDS", NumberToWords.AmountToWords(sumTaxDeduct, "Euro", "Cent")).
Replace("#TOTALTAXNONDEDUCTAMOUNTINWORDS", NumberToWords.AmountToWords(sumNonDeduct, "Euro", "Cent"));
return(msg.Replace("#ROWTEMPLATE", rowTexts));
}
public static DataSet GiftsOverMinimum(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
String StartDate = AParameters["param_start_date"].ToDate().ToString("yyyy-MM-dd");
String EndDate = AParameters["param_end_date"].ToDate().ToString("yyyy-MM-dd");
String MinimumAmount = AParameters["param_minimum_amount"].ToString();
String CurrencyField = (AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n");
String DonorExclude = "";
String MotivationQuery = "";
TDBTransaction Transaction = null;
DataTable Gifts = new DataTable();
DataTable Donors = new DataTable();
DataTable Contacts = new DataTable();
DataSet Results = new DataSet();
#if DEBUG
foreach (String key in AParameters.Keys)
{
TLogging.Log(key + " => " + AParameters[key].ToString());
}
#endif
if (AParameters["param_exclude_anonymous_donors"].ToBool())
{
DonorExclude += "AND Donor.p_anonymous_donor_l = 0 ";
}
if (AParameters["param_exclude_no_solicitations"].ToBool())
{
DonorExclude += "AND Donor.p_no_solicitations_l = 0 ";
}
if (!AParameters["param_all_motivation_groups"].ToBool())
{
MotivationQuery += String.Format("AND a_gift_detail.a_motivation_group_code_c IN ({0}) ",
AParameters["param_motivation_group_quotes"]);
}
if (!AParameters["param_all_motivation_details"].ToBool())
{
MotivationQuery += String.Format("AND (a_gift_detail.a_motivation_group_code_c, a_gift_detail.a_motivation_detail_code_c) IN ({0}) ",
AParameters["param_motivation_group_detail_pairs"]);
}
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String Query =
@"
WITH Details AS (
SELECT
a_gift.p_donor_key_n AS DonorKey
-- We need to join to Donor to check p_anonymous_donor_l and p_no_solicitations_l for the query, so we may as well pull Name etc.
-- at the same time, to avoid doing a second Donor query later. But we'll consolidate this duplicated data into another DataTable
-- to return it to the client.
, Donor.p_partner_short_name_c AS DonorName
, Donor.p_partner_class_c AS DonorClass
, Donor.p_receipt_letter_frequency_c AS ReceiptFrequency
, a_gift.a_date_entered_d AS GiftDate
, a_gift_detail.a_confidential_gift_flag_l AS Confidential
, a_gift_detail.a_motivation_group_code_c AS MotivationGroup
, a_gift_detail.a_motivation_detail_code_c AS MotivationDetail
, a_motivation_group.a_motivation_group_description_c AS MotivationGroupDescription
, a_motivation_detail.a_motivation_detail_desc_c AS MotivationDetailDescription
, a_gift_detail.p_recipient_key_n AS RecipientKey
, a_gift_detail."
+
CurrencyField + @" AS GiftAmount
, sum(a_gift_detail." + CurrencyField +
@") OVER (PARTITION BY a_gift.p_donor_key_n) AS TotalAmount
FROM
a_gift
INNER JOIN
p_partner AS Donor
ON
(Donor.p_partner_key_n = a_gift.p_donor_key_n)
INNER JOIN
a_gift_detail
USING
(a_ledger_number_i, a_batch_number_i, a_gift_transaction_number_i)
INNER JOIN
a_motivation_group
USING
(a_ledger_number_i, a_motivation_group_code_c)
INNER JOIN
a_motivation_detail
USING
(a_ledger_number_i, a_motivation_group_code_c, a_motivation_detail_code_c)
WHERE
a_gift.a_ledger_number_i = "
+
LedgerNumber + @"
AND a_gift.a_date_entered_d BETWEEN '" + StartDate + "' AND '" +
EndDate +
@"'
-- I hope a_dont_report_l gets converted to a_report_l to avoid this horrible double negative:
AND a_motivation_detail.a_dont_report_l = False
"
+
MotivationQuery + DonorExclude +
@"
-- For OM Germany, exclude donors 99000000 and 27002909 (SHKI and anonymous UNBEKANNT)
AND NOT ((a_gift.a_ledger_number_i = 27 OR a_gift.a_ledger_number_i = 90 OR a_gift.a_ledger_number_i = 99)
AND (a_gift.p_donor_key_n = 99000000 OR a_gift.p_donor_key_n = 27002909))
)
SELECT
Details.*
, Recipient.p_partner_short_name_c AS RecipientName
FROM
Details
INNER JOIN
p_partner AS Recipient
ON
(Recipient.p_partner_key_n = Details.RecipientKey)
WHERE
TotalAmount >= "
+
MinimumAmount +
@"
ORDER BY
Details.DonorName
;
";
#if DEBUG
TLogging.Log(Query);
#endif
Gifts = DbAdapter.RunQuery(Query, "GiftsOverMinimum", Transaction);
#if DEBUG
TLogging.Log("Query finished");
#endif
// Get the donors' addresses
const int DONOR_KEY = 0;
const int DONOR_ADDR = 5;
const int DONOR_POSTCODE = 6;
const int DONOR_PHONE = 7;
const int DONOR_EMAIL = 8;
Donors = Gifts.DefaultView.ToTable("Donors", true, "DonorKey", "DonorName", "DonorClass", "ReceiptFrequency", "TotalAmount");
Donors.Columns.Add("Address", typeof(String));
Donors.Columns.Add("PostalCode", typeof(String));
Donors.Columns.Add("Phone", typeof(String));
Donors.Columns.Add("Email", typeof(String));
// Having copied the distinct names and totals from Gifts to Donors, we no longer need to pass their duplicated data back to the client
foreach (String col in new String[] { "DonorName", "DonorClass", "ReceiptFrequency", "TotalAmount" })
{
Gifts.Columns.Remove(col);
}
PLocationTable Address;
String Country, EmailAddress, PhoneNumber, FaxNumber;
List <String> DonorList = new List <string>();
#if DEBUG
TLogging.Log("Processing addresses");
#endif
foreach (DataRow Donor in Donors.Rows)
{
DonorList.Add(Donor[DONOR_KEY].ToString());
if (TAddressTools.GetBestAddress((Int64)Donor[DONOR_KEY], out Address, out Country, Transaction))
{
Donor[DONOR_ADDR] =
Calculations.DetermineLocationString(Address[0], Calculations.TPartnerLocationFormatEnum.plfCommaSeparated);
Donor[DONOR_POSTCODE] = Address[0]["p_postal_code_c"];
}
else
{
Donor[DONOR_ADDR] = "";
Donor[DONOR_POSTCODE] = "";
}
TContactDetailsAggregate.GetPrimaryEmailAndPrimaryPhoneAndFax((Int64)Donor[DONOR_KEY], out PhoneNumber, out EmailAddress,
out FaxNumber);
Donor[DONOR_PHONE] = PhoneNumber;
Donor[DONOR_EMAIL] = EmailAddress;
}
if (DonorList.Count == 0)
{
DonorList.Add("null");
}
#if DEBUG
TLogging.Log(
"Addresses finished");
#endif
// Get the most recent contacts with each donor
Query =
@"
WITH Contacts AS (
SELECT
row_number() OVER (PARTITION BY p_partner_contact.p_partner_key_n ORDER BY s_contact_date_d DESC, s_contact_time_i DESC) AS RowID
, p_partner_contact.p_partner_key_n AS DonorKey
, p_contact_log.p_contactor_c AS Contactor
, p_contact_log.s_contact_date_d AS ContactDate
, p_contact_log.s_contact_time_i AS ContactTime
, p_contact_log.s_contact_time_i * '1 second'::interval AS Time
, p_contact_log.p_contact_code_c AS ContactCode
, p_contact_log.p_contact_comment_c AS Comment
FROM
p_partner_contact
INNER JOIN
p_contact_log
USING
(p_contact_log_id_i)
WHERE
p_partner_key_n in ("
+
String.Join(",",
DonorList) +
@")
ORDER BY
DonorKey,
RowID
)
SELECT
*
FROM
Contacts
WHERE
Contacts.RowID <= "
+
AParameters["param_max_contacts"] + @";
";
#if DEBUG
TLogging.Log(Query);
#endif
Contacts = DbAdapter.RunQuery(Query, "Contacts", Transaction);
#if DEBUG
TLogging.Log("Query finished");
#endif
if (DbAdapter.IsCancelled)
{
Results = null;
return;
}
}); // GetNewOrExistingAutoReadTransaction
Results.Tables.Add(Gifts);
Results.Tables.Add(Donors);
Results.Tables.Add(Contacts);
return(Results);
}
/// <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);
}