public void TestGetRecipientFundNumber()
{
Int64 partnerKey = 73000000;
Int64 RecipientLedgerNumber = 0;
//bool NewTransaction = false;
//TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
try
{
RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(partnerKey);
}
catch (Exception)
{
throw;
}
//finally
//{
//if (NewTransaction)
//{
// DBAccess.GDBAccessObj.RollbackTransaction();
//}
//}
//TODO the value to check for needs to be updated oncw workwer field is implemented.
//TODO If this first one works, try different permatations for Assert.AreEqual
// Test also for exception handling
Assert.AreEqual(73000000, RecipientLedgerNumber,
String.Format("Expected Recipient Ledger Number: {0} but got {1}", 73000000, RecipientLedgerNumber));
}
public void Test_GetRecipientLedger()
{
Int64 partnerKey = CreateNewPartnerKey();
long RecipLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(partnerKey);
Assert.IsTrue((RecipLedgerNumber != 0), String.Format("PartnerKey {0} has a recipient ledger number of 0!", partnerKey));
}
/// <summary>
///
/// </summary>
//[Test] - TODO - reinstate once worker field is sorted
private void Test_ZRecipientLedgerEqualsLedgerPartner()
{
Int64 partnerKey = CreateNewPartnerKeyWithUnit();
Int64 ledgerPartnerKey = GetLedgerPartnerKey(43);
long RecipLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(partnerKey);
Assert.AreEqual(RecipLedgerNumber, ledgerPartnerKey,
String.Format("Expected RecipientLedgerNumber ({0}) to equal Ledger PartnerKey ({1})", RecipLedgerNumber, ledgerPartnerKey));
DeletePartnerKeyWithUnit(partnerKey);
}
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);
}
private static GiftBatchTDS CreateGiftBatches(SortedList <DateTime, List <XmlNode> > AGiftsPerDate, int APeriodNumber)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
ALedgerTable LedgerTable = null;
TDBTransaction ReadTransaction = new TDBTransaction();
DBAccess.ReadTransaction(ref ReadTransaction,
delegate
{
TDataBase db = ReadTransaction.DataBaseObj;
// get a list of potential donors (all class FAMILY)
string sqlGetFamilyPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_family";
DataTable FamilyKeys = db.SelectDT(sqlGetFamilyPartnerKeys,
"keys",
ReadTransaction);
// get a list of workers (all class FAMILY, with special type WORKER)
string sqlGetWorkerPartnerKeys =
"SELECT PUB_p_family.p_partner_key_n FROM PUB_p_family, PUB_p_partner_type WHERE PUB_p_partner_type.p_partner_key_n = PUB_p_family.p_partner_key_n AND p_type_code_c = 'WORKER'";
DataTable WorkerKeys = db.SelectDT(sqlGetWorkerPartnerKeys, "keys", ReadTransaction);
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys =
String.Format(
"SELECT U.p_partner_key_n FROM PUB_p_unit U WHERE u_unit_type_code_c = 'F' AND EXISTS (SELECT * FROM PUB_a_valid_ledger_number V WHERE V.a_ledger_number_i = {0} AND V.p_partner_key_n = U.p_partner_key_n)",
FLedgerNumber);
DataTable FieldKeys = db.SelectDT(sqlGetFieldPartnerKeys, "keys", ReadTransaction);
// get a list of key ministries (all class UNIT, with unit type KEY-MIN), and their field ledger number and cost centre code
string sqlGetKeyMinPartnerKeys =
"SELECT u.p_partner_key_n, us.um_parent_unit_key_n, vl.a_cost_centre_code_c " +
"FROM PUB_p_unit u, PUB_um_unit_structure us, PUB_a_valid_ledger_number vl " +
"WHERE u.u_unit_type_code_c = 'KEY-MIN' " +
"AND us.um_child_unit_key_n = u.p_partner_key_n " +
"AND vl.p_partner_key_n = us.um_parent_unit_key_n " +
"AND vl.a_ledger_number_i = " + FLedgerNumber.ToString();
DataTable KeyMinistries = db.SelectDT(sqlGetKeyMinPartnerKeys, "keys", ReadTransaction);
LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, ReadTransaction);
AAccountingPeriodRow AccountingPeriodRow = AAccountingPeriodAccess.LoadByPrimaryKey(FLedgerNumber,
APeriodNumber,
ReadTransaction)[0];
// create a gift batch for each day.
// TODO: could create one batch per month, if there are not so many gifts (less than 100 per month)
foreach (DateTime GlEffectiveDate in AGiftsPerDate.Keys)
{
if ((GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodStartDate) < 0) ||
(GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodEndDate) > 0))
{
// only create gifts in that period
continue;
}
AGiftBatchRow giftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref MainDS,
ref ReadTransaction,
ref LedgerTable,
FLedgerNumber,
GlEffectiveDate);
TLogging.LogAtLevel(1, "create gift batch for " + GlEffectiveDate.ToShortDateString());
giftBatch.BatchDescription = "Benerator Batch for " + GlEffectiveDate.ToShortDateString();
giftBatch.BatchTotal = 0.0m;
foreach (XmlNode RecordNode in AGiftsPerDate[GlEffectiveDate])
{
AGiftRow gift = MainDS.AGift.NewRowTyped();
gift.LedgerNumber = giftBatch.LedgerNumber;
gift.BatchNumber = giftBatch.BatchNumber;
gift.GiftTransactionNumber = giftBatch.LastGiftNumber + 1;
gift.DateEntered = GlEffectiveDate;
// set donorKey
int donorID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "donor")) % FamilyKeys.Rows.Count;
gift.DonorKey = Convert.ToInt64(FamilyKeys.Rows[donorID].ItemArray[0]);
// calculate gift detail information
int countDetails = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "splitgift"));
for (int counter = 1; counter <= countDetails; counter++)
{
AGiftDetailRow giftDetail = MainDS.AGiftDetail.NewRowTyped();
giftDetail.LedgerNumber = gift.LedgerNumber;
giftDetail.BatchNumber = gift.BatchNumber;
giftDetail.GiftTransactionNumber = gift.GiftTransactionNumber;
giftDetail.MotivationGroupCode = "GIFT";
giftDetail.GiftTransactionAmount = Convert.ToDecimal(TXMLParser.GetAttribute(RecordNode, "amount_" + counter.ToString()));
giftDetail.GiftAmount = giftDetail.GiftTransactionAmount;
giftBatch.BatchTotal += giftDetail.GiftAmount;
string motivation = TXMLParser.GetAttribute(RecordNode, "motivation_" + counter.ToString());
if (motivation == "SUPPORT")
{
if (WorkerKeys.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "SUPPORT";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_support_" +
counter.ToString())) % WorkerKeys.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(WorkerKeys.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey,
giftBatch.GlEffectiveDate);
// ignore this gift detail, if there is no valid commitment period for the worker
if (giftDetail.RecipientLedgerNumber == 0)
{
continue;
}
}
else if (motivation == "FIELD")
{
if (FieldKeys.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "FIELD";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_field_" +
counter.ToString())) % FieldKeys.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(FieldKeys.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = giftDetail.RecipientKey;
giftDetail.CostCentreCode = (giftDetail.RecipientKey / 10000).ToString("0000");
}
else if (motivation == "KEYMIN")
{
if (KeyMinistries.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "KEYMIN";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_keymin_" +
counter.ToString())) % KeyMinistries.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[1]);
// TTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey);
giftDetail.CostCentreCode = KeyMinistries.Rows[recipientID].ItemArray[2].ToString();
// TTransactionWebConnector.IdentifyPartnerCostCentre(FLedgerNumber, giftDetail.RecipientLedgerNumber);
}
giftDetail.DetailNumber = gift.LastDetailNumber + 1;
MainDS.AGiftDetail.Rows.Add(giftDetail);
gift.LastDetailNumber = giftDetail.DetailNumber;
}
if (gift.LastDetailNumber > 0)
{
MainDS.AGift.Rows.Add(gift);
giftBatch.LastGiftNumber = gift.GiftTransactionNumber;
}
if (giftBatch.LastGiftNumber >= MaxGiftsPerBatch)
{
break;
}
}
if (TLogging.DebugLevel > 0)
{
TLogging.Log(
GlEffectiveDate.ToShortDateString() + " " + giftBatch.LastGiftNumber.ToString());
}
}
});
// need to save the last gift batch number in a_ledger
if (LedgerTable != null)
{
TDBTransaction WriteTransaction = new TDBTransaction();
bool SubmissionOk = false;
DBAccess.WriteTransaction(ref WriteTransaction, ref SubmissionOk,
delegate
{
ALedgerAccess.SubmitChanges(LedgerTable, WriteTransaction);
SubmissionOk = true;
});
if (!SubmissionOk)
{
TLogging.Log("An Exception occured during the creation of Gift Batches" + Environment.NewLine);
}
}
return(MainDS);
}
