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