/// create new gift info public static AGiftBatchRow CreateNewGiftInfo(Int64 APartnerKey, ref GiftBatchTDS AGiftDS, TDataBase ADataBase = null) { TDataBase db = DBAccess.Connect("CreateNewGiftInfo", ADataBase); bool NewTransaction; TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction); ALedgerAccess.LoadAll(AGiftDS, Transaction); AGiftDS = TGiftTransactionWebConnector.CreateAGiftBatch(AGiftDS.ALedger[0].LedgerNumber, DateTime.Today, "Test batch", db); // Create a new GiftBatch AGiftBatchRow Batch = AGiftDS.AGiftBatch[0]; Batch.BankAccountCode = "6000"; Batch.BatchYear = 1; Batch.BatchPeriod = 1; Batch.CurrencyCode = "EUR"; Batch.BankCostCentre = Batch.LedgerNumber.ToString() + "00"; Batch.LastGiftNumber = 1; Batch.ExchangeRateToBase = 0.5M; // Create a new Gift record AGiftRow Gift = AGiftDS.AGift.NewRowTyped(); Gift.LedgerNumber = Batch.LedgerNumber; Gift.BatchNumber = Batch.BatchNumber; Gift.GiftTransactionNumber = 1; Gift.DonorKey = APartnerKey; AGiftDS.AGift.Rows.Add(Gift); // Create a new GiftDetail record AGiftDetailRow GiftDetail = AGiftDS.AGiftDetail.NewRowTyped(); GiftDetail.LedgerNumber = Gift.LedgerNumber; GiftDetail.BatchNumber = Gift.BatchNumber; GiftDetail.GiftTransactionNumber = Gift.GiftTransactionNumber; GiftDetail.DetailNumber = 1; GiftDetail.MotivationGroupCode = "GIFT"; GiftDetail.MotivationDetailCode = "SUPPORT"; // this won't work with RecipientKey 0 anymore. see https://github.com/openpetra/openpetra/issues/183 GiftDetail.RecipientKey = 43000000; GiftDetail.RecipientLedgerNumber = APartnerKey; GiftDetail.GiftTransactionAmount = 10; AGiftDS.AGiftDetail.Rows.Add(GiftDetail); if (NewTransaction) { Transaction.Rollback(); } return(Batch); }
/// create new gift info public static AGiftBatchRow CreateNewGiftInfo(Int64 APartnerKey, ref GiftBatchTDS AGiftDS) { ALedgerAccess.LoadAll(AGiftDS, DBAccess.GDBAccessObj.Transaction); AGiftDS = TGiftTransactionWebConnector.CreateAGiftBatch(AGiftDS.ALedger[0].LedgerNumber, DateTime.Today, "Test batch"); // Create a new GiftBatch AGiftBatchRow Batch = AGiftDS.AGiftBatch[0]; Batch.BankAccountCode = "6000"; Batch.BatchYear = 1; Batch.BatchPeriod = 1; Batch.CurrencyCode = "EUR"; Batch.BankCostCentre = Batch.LedgerNumber.ToString() + "00"; Batch.LastGiftNumber = 1; Batch.ExchangeRateToBase = 0.5M; // Create a new Gift record AGiftRow Gift = AGiftDS.AGift.NewRowTyped(); Gift.LedgerNumber = Batch.LedgerNumber; Gift.BatchNumber = Batch.BatchNumber; Gift.GiftTransactionNumber = 1; Gift.DonorKey = APartnerKey; AGiftDS.AGift.Rows.Add(Gift); // Create a new GiftDetail record AGiftDetailRow GiftDetail = AGiftDS.AGiftDetail.NewRowTyped(); GiftDetail.LedgerNumber = Gift.LedgerNumber; GiftDetail.BatchNumber = Gift.BatchNumber; GiftDetail.GiftTransactionNumber = Gift.GiftTransactionNumber; GiftDetail.DetailNumber = 1; GiftDetail.MotivationGroupCode = "GIFT"; GiftDetail.MotivationDetailCode = "SUPPORT"; GiftDetail.RecipientKey = 0; GiftDetail.RecipientLedgerNumber = APartnerKey; AGiftDS.AGiftDetail.Rows.Add(GiftDetail); return(Batch); }
public static Int32 CreateGiftBatch( Int32 ALedgerNumber, Int32 AStatementKey, Int32 AGiftBatchNumber, out TVerificationResultCollection AVerificationResult) { BankImportTDS MainDS = GetBankStatementTransactionsAndMatches(AStatementKey, ALedgerNumber); string MyClientID = DomainManager.GClientID.ToString(); TProgressTracker.InitProgressTracker(MyClientID, Catalog.GetString("Creating gift batch"), MainDS.AEpTransaction.DefaultView.Count + 10); AVerificationResult = new TVerificationResultCollection(); MainDS.AEpTransaction.DefaultView.RowFilter = String.Format("{0}={1}", AEpTransactionTable.GetStatementKeyDBName(), AStatementKey); MainDS.AEpStatement.DefaultView.RowFilter = String.Format("{0}={1}", AEpStatementTable.GetStatementKeyDBName(), AStatementKey); AEpStatementRow stmt = (AEpStatementRow)MainDS.AEpStatement.DefaultView[0].Row; // TODO: optional: use the preselected gift batch, AGiftBatchNumber Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber; DateTime BatchDateEffective = stmt.Date; TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); if (!TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref BatchDateEffective, out DateEffectiveYearNumber, out DateEffectivePeriodNumber, Transaction, true)) { // just use the latest possible date string msg = String.Format(Catalog.GetString("Date {0} is not in an open period of the ledger, using date {1} instead for the gift batch."), stmt.Date.ToShortDateString(), BatchDateEffective.ToShortDateString()); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Info)); } ACostCentreAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); MainDS.AEpMatch.DefaultView.Sort = AEpMatchTable.GetActionDBName() + ", " + AEpMatchTable.GetMatchTextDBName(); if (MainDS.AEpTransaction.DefaultView.Count == 0) { AVerificationResult.Add(new TVerificationResult( Catalog.GetString("Creating Gift Batch"), String.Format(Catalog.GetString("There are no transactions for statement #{0}."), AStatementKey), TResultSeverity.Resv_Info)); return(-1); } foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView) { AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row; DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] { MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT, transactionRow.MatchText }); if (matches.Length > 0) { AEpMatchRow match = (AEpMatchRow)matches[0].Row; if (match.IsDonorKeyNull() || (match.DonorKey == 0)) { string msg = String.Format(Catalog.GetString("Cannot create a gift for transaction {0} since there is no valid donor."), transactionRow.Description); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Critical)); DBAccess.GDBAccessObj.RollbackTransaction(); return(-1); } } } string MatchedGiftReference = stmt.Filename; if (!stmt.IsBankAccountKeyNull()) { string sqlGetBankSortCode = "SELECT bank.p_branch_code_c " + "FROM PUB_p_banking_details details, PUB_p_bank bank " + "WHERE details.p_banking_details_key_i = ?" + "AND details.p_bank_key_n = bank.p_partner_key_n"; OdbcParameter param = new OdbcParameter("detailkey", OdbcType.Int); param.Value = stmt.BankAccountKey; PBankTable bankTable = new PBankTable(); DBAccess.GDBAccessObj.SelectDT(bankTable, sqlGetBankSortCode, Transaction, new OdbcParameter[] { param }, 0, 0); MatchedGiftReference = bankTable[0].BranchCode + " " + stmt.Date.Day.ToString(); } DBAccess.GDBAccessObj.RollbackTransaction(); GiftBatchTDS GiftDS = TGiftTransactionWebConnector.CreateAGiftBatch( ALedgerNumber, BatchDateEffective, String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString())); AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0]; giftbatchRow.BankAccountCode = stmt.BankAccountCode; decimal HashTotal = 0.0M; MainDS.AEpTransaction.DefaultView.Sort = AEpTransactionTable.GetNumberOnPaperStatementDBName(); MainDS.AEpMatch.DefaultView.RowFilter = String.Empty; MainDS.AEpMatch.DefaultView.Sort = AEpMatchTable.GetActionDBName() + ", " + AEpMatchTable.GetMatchTextDBName(); int counter = 5; foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView) { TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("Preparing the gifts"), counter++); AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row; DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] { MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT, transactionRow.MatchText }); if (matches.Length > 0) { AEpMatchRow match = (AEpMatchRow)matches[0].Row; AGiftRow gift = GiftDS.AGift.NewRowTyped(); gift.LedgerNumber = giftbatchRow.LedgerNumber; gift.BatchNumber = giftbatchRow.BatchNumber; gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1; gift.DonorKey = match.DonorKey; gift.DateEntered = transactionRow.DateEffective; gift.Reference = MatchedGiftReference; GiftDS.AGift.Rows.Add(gift); giftbatchRow.LastGiftNumber++; foreach (DataRowView r in matches) { match = (AEpMatchRow)r.Row; AGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped(); detail.LedgerNumber = gift.LedgerNumber; detail.BatchNumber = gift.BatchNumber; detail.GiftTransactionNumber = gift.GiftTransactionNumber; detail.DetailNumber = gift.LastDetailNumber + 1; gift.LastDetailNumber++; detail.GiftTransactionAmount = match.GiftTransactionAmount; detail.GiftAmount = match.GiftTransactionAmount; HashTotal += match.GiftTransactionAmount; detail.MotivationGroupCode = match.MotivationGroupCode; detail.MotivationDetailCode = match.MotivationDetailCode; // do not use the description in comment one, because that could show up on the gift receipt??? // detail.GiftCommentOne = transactionRow.Description; detail.CommentOneType = MFinanceConstants.GIFT_COMMENT_TYPE_BOTH; detail.CostCentreCode = match.CostCentreCode; detail.RecipientKey = match.RecipientKey; detail.RecipientLedgerNumber = match.RecipientLedgerNumber; AMotivationDetailRow motivation = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find( new object[] { ALedgerNumber, detail.MotivationGroupCode, detail.MotivationDetailCode }); if (motivation == null) { AVerificationResult.Add(new TVerificationResult( String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description), String.Format(Catalog.GetString("Cannot find motivation group '{0}' and motivation detail '{1}'"), detail.MotivationGroupCode, detail.MotivationDetailCode), TResultSeverity.Resv_Critical)); } if (detail.CostCentreCode.Length == 0) { // try to retrieve the current costcentre for this recipient if (detail.RecipientKey != 0) { detail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(detail.RecipientKey); detail.CostCentreCode = TGiftTransactionWebConnector.IdentifyPartnerCostCentre(detail.LedgerNumber, detail.RecipientLedgerNumber); } else { if (motivation != null) { detail.CostCentreCode = motivation.CostCentreCode; } } } // check for active cost centre ACostCentreRow costcentre = (ACostCentreRow)MainDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, detail.CostCentreCode }); if ((costcentre == null) || !costcentre.CostCentreActiveFlag) { AVerificationResult.Add(new TVerificationResult( String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description), Catalog.GetString("Invalid or inactive cost centre"), TResultSeverity.Resv_Critical)); } GiftDS.AGiftDetail.Rows.Add(detail); } } } TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("Submit to database"), counter++); if (AVerificationResult.HasCriticalErrors) { return(-1); } giftbatchRow.HashTotal = HashTotal; giftbatchRow.BatchTotal = HashTotal; // do not overwrite the parameter, because there might be the hint for a different gift batch date TVerificationResultCollection VerificationResultSubmitChanges; TSubmitChangesResult result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS, out VerificationResultSubmitChanges); TProgressTracker.FinishJob(MyClientID); if (result == TSubmitChangesResult.scrOK) { return(giftbatchRow.BatchNumber); } return(-1); }
/// <summary> /// Creates data needed to test posting recalculations /// </summary> /// <param name="ARecipientKey">Partner Key of the recipient.</param> /// <param name="ARealRecipientLedgerNumber">What the RecipientLedgerNumber should be.</param> /// <param name="AFalseRecipientLedgerNumber">What the RecipientLedgerNumber is.</param> /// <param name="ARealCostCentreCode">What the CostCentreCode should be.</param> /// <param name="AFalseCostCentreCode">What the CostCentreCode is.</param> /// <param name="AGiftBatchNumber">Batch Number.</param> private void TestBatchPostingRecalculations_Arrange(out long ARecipientKey, out long ARealRecipientLedgerNumber, out long AFalseRecipientLedgerNumber, string ARealCostCentreCode, string AFalseCostCentreCode, out Int32 AGiftBatchNumber) { TVerificationResultCollection VerificationResult; TSubmitChangesResult Result; DataSet ResponseDS; TPartnerEditUIConnector PartnerEditUIUIConnector = new TPartnerEditUIConnector(); GiftBatchTDS MainDS = new GiftBatchTDS(); PartnerEditTDS PartnerEditDS = new PartnerEditTDS(); //GLSetupTDS GLSetupDS = new GLSetupTDS(); // this is a family partner in the test database const Int64 DONORKEY = 43005001; // create a new recipient TCreateTestPartnerData.CreateNewFamilyPartner(PartnerEditDS); ARecipientKey = PartnerEditDS.PFamily[0].PartnerKey; // create two new Unit partners TCreateTestPartnerData.CreateNewUnitPartner(PartnerEditDS); TCreateTestPartnerData.CreateNewUnitPartner(PartnerEditDS); AFalseRecipientLedgerNumber = PartnerEditDS.PPartner[0].PartnerKey; ARealRecipientLedgerNumber = PartnerEditDS.PPartner[1].PartnerKey; // create a Gift Destination for family PPartnerGiftDestinationRow GiftDestination = PartnerEditDS.PPartnerGiftDestination.NewRowTyped(true); GiftDestination.Key = TPartnerDataReaderWebConnector.GetNewKeyForPartnerGiftDestination(); GiftDestination.PartnerKey = ARecipientKey; GiftDestination.DateEffective = new DateTime(2011, 01, 01); GiftDestination.FieldKey = ARealRecipientLedgerNumber; PartnerEditDS.PPartnerGiftDestination.Rows.Add(GiftDestination); // Guard Assertions Assert.That(PartnerEditDS.PFamily[0], Is.Not.Null); Assert.That(PartnerEditDS.PPartner[0], Is.Not.Null); Assert.That(PartnerEditDS.PPartner[1], Is.Not.Null); // Submit the new PartnerEditTDS records to the database ResponseDS = new PartnerEditTDS(); Result = PartnerEditUIUIConnector.SubmitChanges(ref PartnerEditDS, ref ResponseDS, out VerificationResult); // Guard Assertion Assert.That(Result, Is.EqualTo( TSubmitChangesResult.scrOK), "SubmitChanges for PartnerEditDS failed: " + VerificationResult.BuildVerificationResultString()); // link unit to Cost Centre DataTable PartnerCostCentreTbl = TGLSetupWebConnector.LoadCostCentrePartnerLinks(FLedgerNumber, 0); DataRow PartnerCostCentreRow = PartnerCostCentreTbl.NewRow(); PartnerCostCentreRow["PartnerKey"] = ARealRecipientLedgerNumber; PartnerCostCentreRow["IsLinked"] = ARealCostCentreCode; PartnerCostCentreTbl.Rows.Add(PartnerCostCentreRow); TGLSetupWebConnector.SaveCostCentrePartnerLinks(FLedgerNumber, PartnerCostCentreTbl); // create a new Gift Batch MainDS = TGiftTransactionWebConnector.CreateAGiftBatch(FLedgerNumber); AGiftBatchNumber = MainDS.AGiftBatch[0].BatchNumber; // create two new gifts AGiftRow GiftRow = MainDS.AGift.NewRowTyped(true); GiftRow.LedgerNumber = FLedgerNumber; GiftRow.BatchNumber = AGiftBatchNumber; GiftRow.DonorKey = DONORKEY; GiftRow.GiftTransactionNumber = 1; GiftRow.LastDetailNumber = 1; MainDS.AGift.Rows.Add(GiftRow); GiftRow = MainDS.AGift.NewRowTyped(true); GiftRow.LedgerNumber = FLedgerNumber; GiftRow.BatchNumber = AGiftBatchNumber; GiftRow.DonorKey = DONORKEY; GiftRow.GiftTransactionNumber = 2; GiftRow.LastDetailNumber = 1; MainDS.AGift.Rows.Add(GiftRow); // create a new GiftDetail with a positive amount AGiftDetailRow GiftDetail = MainDS.AGiftDetail.NewRowTyped(true); GiftDetail.LedgerNumber = FLedgerNumber; GiftDetail.BatchNumber = AGiftBatchNumber; GiftDetail.GiftTransactionNumber = 1; GiftDetail.DetailNumber = 1; GiftDetail.RecipientLedgerNumber = AFalseRecipientLedgerNumber; GiftDetail.GiftAmount = 100; GiftDetail.MotivationGroupCode = "GIFT"; GiftDetail.MotivationDetailCode = "SUPPORT"; GiftDetail.RecipientKey = ARecipientKey; GiftDetail.CostCentreCode = AFalseCostCentreCode; GiftDetail.GiftTransactionAmount = 100; MainDS.AGiftDetail.Rows.Add(GiftDetail); // create a new GiftDetail with a negative amount GiftDetail = MainDS.AGiftDetail.NewRowTyped(true); GiftDetail.LedgerNumber = FLedgerNumber; GiftDetail.BatchNumber = AGiftBatchNumber; GiftDetail.GiftTransactionNumber = 2; GiftDetail.DetailNumber = 1; GiftDetail.RecipientLedgerNumber = AFalseRecipientLedgerNumber; GiftDetail.GiftAmount = -100; GiftDetail.MotivationGroupCode = "GIFT"; GiftDetail.MotivationDetailCode = "SUPPORT"; GiftDetail.RecipientKey = ARecipientKey; GiftDetail.CostCentreCode = AFalseCostCentreCode; GiftDetail.GiftTransactionAmount = -100; MainDS.AGiftDetail.Rows.Add(GiftDetail); // Submit the new GiftBatchTDS records to the database Result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref MainDS, out VerificationResult); // Guard Assertion Assert.That(Result, Is.EqualTo( TSubmitChangesResult.scrOK), "SaveGiftBatchTDS failed: " + VerificationResult.BuildVerificationResultString()); }