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