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