public static Int32 CreateGLBatch(BankImportTDS AMainDS,
                                          Int32 ALedgerNumber,
                                          Int32 AStatementKey,
                                          Int32 AGLBatchNumber,
                                          out TVerificationResultCollection AVerificationResult)
        {
            AMainDS.AEpTransaction.DefaultView.RowFilter =
                String.Format("{0}={1}",
                              AEpTransactionTable.GetStatementKeyDBName(),
                              AStatementKey);
            AMainDS.AEpStatement.DefaultView.RowFilter =
                String.Format("{0}={1}",
                              AEpStatementTable.GetStatementKeyDBName(),
                              AStatementKey);
            AEpStatementRow stmt = (AEpStatementRow)AMainDS.AEpStatement.DefaultView[0].Row;

            AVerificationResult = null;

            Int32          DateEffectivePeriodNumber, DateEffectiveYearNumber;
            TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);

            if (!TFinancialYear.IsValidPostingPeriod(ALedgerNumber, stmt.Date, out DateEffectivePeriodNumber, out DateEffectiveYearNumber,
                                                     Transaction))
            {
                string msg = String.Format(Catalog.GetString("Cannot create a GL batch for date {0} since it is not in an open period of the ledger."),
                                           stmt.Date.ToShortDateString());
                TLogging.Log(msg);
                AVerificationResult = new TVerificationResultCollection();
                AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating GL Batch"), msg, TResultSeverity.Resv_Critical));

                DBAccess.GDBAccessObj.RollbackTransaction();
                return(-1);
            }

            Int32 BatchYear, BatchPeriod;

            // if DateEffective is outside the range of open periods, use the most fitting date
            DateTime DateEffective = stmt.Date;

            TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref DateEffective, out BatchYear, out BatchPeriod, Transaction, true);

            ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);

            DBAccess.GDBAccessObj.RollbackTransaction();

            GLBatchTDS GLDS = TGLTransactionWebConnector.CreateABatch(ALedgerNumber);

            ABatchRow glbatchRow = GLDS.ABatch[0];

            glbatchRow.BatchPeriod      = BatchPeriod;
            glbatchRow.DateEffective    = DateEffective;
            glbatchRow.BatchDescription = String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString());

            decimal HashTotal   = 0.0M;
            decimal DebitTotal  = 0.0M;
            decimal CreditTotal = 0.0M;

            // TODO: support several journals
            // TODO: support several currencies, support other currencies than the base currency
            AJournalRow gljournalRow = GLDS.AJournal.NewRowTyped();

            gljournalRow.LedgerNumber        = glbatchRow.LedgerNumber;
            gljournalRow.BatchNumber         = glbatchRow.BatchNumber;
            gljournalRow.JournalNumber       = glbatchRow.LastJournal + 1;
            gljournalRow.TransactionCurrency = LedgerTable[0].BaseCurrency;
            glbatchRow.LastJournal++;
            gljournalRow.JournalPeriod       = glbatchRow.BatchPeriod;
            gljournalRow.DateEffective       = glbatchRow.DateEffective;
            gljournalRow.JournalDescription  = glbatchRow.BatchDescription;
            gljournalRow.SubSystemCode       = CommonAccountingSubSystemsEnum.GL.ToString();
            gljournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
            gljournalRow.ExchangeRateToBase  = 1.0m;
            GLDS.AJournal.Rows.Add(gljournalRow);

            foreach (DataRowView dv in AMainDS.AEpTransaction.DefaultView)
            {
                AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;

                DataView v = AMainDS.AEpMatch.DefaultView;
                v.RowFilter = AEpMatchTable.GetActionDBName() + " = '" + MFinanceConstants.BANK_STMT_STATUS_MATCHED_GL + "' and " +
                              AEpMatchTable.GetMatchTextDBName() + " = '" + transactionRow.MatchText + "'";

                if (v.Count > 0)
                {
                    AEpMatchRow     match = (AEpMatchRow)v[0].Row;
                    ATransactionRow trans = GLDS.ATransaction.NewRowTyped();
                    trans.LedgerNumber      = glbatchRow.LedgerNumber;
                    trans.BatchNumber       = glbatchRow.BatchNumber;
                    trans.JournalNumber     = gljournalRow.JournalNumber;
                    trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1;
                    trans.AccountCode       = match.AccountCode;
                    trans.CostCentreCode    = match.CostCentreCode;
                    trans.Reference         = match.Reference;
                    trans.Narrative         = match.Narrative;
                    trans.TransactionDate   = transactionRow.DateEffective;

                    if (transactionRow.TransactionAmount < 0)
                    {
                        trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount;
                        trans.TransactionAmount    = -1 * transactionRow.TransactionAmount;
                        trans.DebitCreditIndicator = true;
                        DebitTotal += trans.AmountInBaseCurrency;
                    }
                    else
                    {
                        trans.AmountInBaseCurrency = transactionRow.TransactionAmount;
                        trans.TransactionAmount    = transactionRow.TransactionAmount;
                        trans.DebitCreditIndicator = false;
                        CreditTotal += trans.AmountInBaseCurrency;
                    }

                    GLDS.ATransaction.Rows.Add(trans);
                    gljournalRow.LastTransactionNumber++;

                    // add one transaction for the bank as well
                    trans = GLDS.ATransaction.NewRowTyped();
                    trans.LedgerNumber      = glbatchRow.LedgerNumber;
                    trans.BatchNumber       = glbatchRow.BatchNumber;
                    trans.JournalNumber     = gljournalRow.JournalNumber;
                    trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1;
                    trans.AccountCode       = stmt.BankAccountCode;
                    trans.CostCentreCode    = TLedgerInfo.GetStandardCostCentre(ALedgerNumber);
                    trans.Reference         = match.Reference;
                    trans.Narrative         = match.Narrative;
                    trans.TransactionDate   = transactionRow.DateEffective;

                    if (transactionRow.TransactionAmount < 0)
                    {
                        trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount;
                        trans.TransactionAmount    = -1 * transactionRow.TransactionAmount;
                        trans.DebitCreditIndicator = false;
                        CreditTotal += trans.AmountInBaseCurrency;
                    }
                    else
                    {
                        trans.AmountInBaseCurrency = transactionRow.TransactionAmount;
                        trans.TransactionAmount    = transactionRow.TransactionAmount;
                        trans.DebitCreditIndicator = true;
                        DebitTotal += trans.AmountInBaseCurrency;
                    }

                    GLDS.ATransaction.Rows.Add(trans);
                    gljournalRow.LastTransactionNumber++;
                }
            }

            gljournalRow.JournalDebitTotal  = DebitTotal;
            gljournalRow.JournalCreditTotal = CreditTotal;
            glbatchRow.BatchDebitTotal      = DebitTotal;
            glbatchRow.BatchCreditTotal     = CreditTotal;
            glbatchRow.BatchControlTotal    = HashTotal;

            TVerificationResultCollection VerificationResult;

            TSubmitChangesResult result = TGLTransactionWebConnector.SaveGLBatchTDS(ref GLDS,
                                                                                    out VerificationResult);

            if (result == TSubmitChangesResult.scrOK)
            {
                return(glbatchRow.BatchNumber);
            }

            TLogging.Log("Problems storing GL Batch");
            return(-1);
        }
        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);
        }