예제 #1
0
        /// <summary>
        /// init the exchange rate, to avoid messages "Cannot find exchange rate for EUR USD"
        /// </summary>
        public static void InitExchangeRate()
        {
            TAccountPeriodInfo AccountingPeriodInfo =
                new TAccountPeriodInfo(FLedgerNumber, 1);
            ADailyExchangeRateTable dailyrates = new ADailyExchangeRateTable();
            ADailyExchangeRateRow   row        = dailyrates.NewRowTyped(true);

            row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
            row.TimeEffectiveFrom = 100;
            row.FromCurrencyCode  = "USD";
            row.ToCurrencyCode    = "EUR";
            row.RateOfExchange    = 1.34m;
            dailyrates.Rows.Add(row);
            row = dailyrates.NewRowTyped(true);
            row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
            row.TimeEffectiveFrom = 100;
            row.FromCurrencyCode  = "USD";
            row.ToCurrencyCode    = "GBP";
            row.RateOfExchange    = 1.57m;
            dailyrates.Rows.Add(row);

            if (!ADailyExchangeRateAccess.Exists(row.FromCurrencyCode, row.ToCurrencyCode, row.DateEffectiveFrom, row.TimeEffectiveFrom, null))
            {
                ADailyExchangeRateAccess.SubmitChanges(dailyrates, null);
            }
        }
예제 #2
0
        /// <summary>
        /// init the exchange rate, to avoid messages "Cannot find exchange rate for EUR USD"
        /// </summary>
        public static void InitExchangeRate()
        {
            TAccountPeriodInfo AccountingPeriodInfo =
                new TAccountPeriodInfo(FLedgerNumber, 1);
            ADailyExchangeRateTable dailyrates = new ADailyExchangeRateTable();
            ADailyExchangeRateRow   row        = dailyrates.NewRowTyped(true);

            row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
            row.TimeEffectiveFrom = 100;
            row.FromCurrencyCode  = "USD";
            row.ToCurrencyCode    = "EUR";
            row.RateOfExchange    = 1.34m;
            dailyrates.Rows.Add(row);
            row = dailyrates.NewRowTyped(true);
            row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
            row.TimeEffectiveFrom = 100;
            row.FromCurrencyCode  = "USD";
            row.ToCurrencyCode    = "GBP";
            row.RateOfExchange    = 1.57m;
            dailyrates.Rows.Add(row);

            if (!ADailyExchangeRateAccess.Exists(row.FromCurrencyCode, row.ToCurrencyCode, row.DateEffectiveFrom, row.TimeEffectiveFrom, null))
            {
                ADailyExchangeRateAccess.SubmitChanges(dailyrates, null);
            }

            ALedgerTable Ledger = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, null);

            for (int periodCounter = 1; periodCounter <= Ledger[0].NumberOfAccountingPeriods + Ledger[0].NumberFwdPostingPeriods; periodCounter++)
            {
                AccountingPeriodInfo = new TAccountPeriodInfo(FLedgerNumber, periodCounter);

                ACorporateExchangeRateTable corprates = new ACorporateExchangeRateTable();
                ACorporateExchangeRateRow   corprow   = corprates.NewRowTyped(true);
                corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
                corprow.TimeEffectiveFrom = 100;
                corprow.FromCurrencyCode  = "USD";
                corprow.ToCurrencyCode    = "EUR";
                corprow.RateOfExchange    = 1.34m;
                corprates.Rows.Add(corprow);
                corprow = corprates.NewRowTyped(true);
                corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
                corprow.TimeEffectiveFrom = 100;
                corprow.FromCurrencyCode  = "USD";
                corprow.ToCurrencyCode    = "GBP";
                corprow.RateOfExchange    = 1.57m;
                corprates.Rows.Add(corprow);

                if (!ACorporateExchangeRateAccess.Exists(corprow.FromCurrencyCode, corprow.ToCurrencyCode, corprow.DateEffectiveFrom, null))
                {
                    ACorporateExchangeRateAccess.SubmitChanges(corprates, null);
                }
            }
        }
        public static void GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable,
                                   TDBTransaction AReadTransaction)
        {
            AResultTable = null;
            string context = string.Format("GetData {0}", SharedConstants.MODULE_ACCESS_MANAGER);

            // check access permissions for the current user
            TModuleAccessManager.CheckUserPermissionsForTable(ATablename, TTablePermissionEnum.eCanRead);

            // TODO: auto generate
            if (ATablename == AApSupplierTable.GetTableDBName())
            {
                AResultTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == AApDocumentTable.GetTableDBName())
            {
                AResultTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == ATransactionTypeTable.GetTableDBName())
            {
                AResultTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == ACurrencyTable.GetTableDBName())
            {
                AResultTable = ACurrencyAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
            {
                AResultTable = ADailyExchangeRateAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
            {
                AResultTable = ACorporateExchangeRateAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
            {
                AResultTable = ACurrencyLanguageAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == AFeesPayableTable.GetTableDBName())
            {
                AResultTable = AFeesPayableAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == AFeesReceivableTable.GetTableDBName())
            {
                AResultTable = AFeesReceivableAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == AAnalysisTypeTable.GetTableDBName())
            {
                AResultTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == AGiftBatchTable.GetTableDBName())
            {
                AResultTable = AGiftBatchAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == AJournalTable.GetTableDBName())
            {
                AResultTable = AJournalAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == ALedgerTable.GetTableDBName())
            {
                AResultTable = ALedgerAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == MExtractMasterTable.GetTableDBName())
            {
                if (ASearchCriteria == null)
                {
                    AResultTable = MExtractMasterAccess.LoadAll(AReadTransaction);
                }
                else
                {
                    AResultTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
                }
            }
            else if (ATablename == MExtractTable.GetTableDBName())
            {
                // it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
                if (ASearchCriteria != null)
                {
                    AResultTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
                }
            }
            else if (ATablename == PcAttendeeTable.GetTableDBName())
            {
                AResultTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == PcConferenceCostTable.GetTableDBName())
            {
                AResultTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == PcEarlyLateTable.GetTableDBName())
            {
                AResultTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == PcSupplementTable.GetTableDBName())
            {
                AResultTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == PcDiscountTable.GetTableDBName())
            {
                AResultTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == PCountryTable.GetTableDBName())
            {
                AResultTable = PCountryAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == PFormTable.GetTableDBName())
            {
                string[]         columns   = TTypedDataTable.GetColumnStringList(PFormTable.TableId);
                StringCollection fieldList = new StringCollection();

                for (int i = 0; i < columns.Length; i++)
                {
                    // Do not load the template document - we don't display it and it is big!
                    if (columns[i] != PFormTable.GetTemplateDocumentDBName())
                    {
                        fieldList.Add(columns[i]);
                    }
                }

                AResultTable = PFormAccess.LoadAll(fieldList, AReadTransaction);
            }
            else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
            {
                AResultTable = PInternationalPostalTypeAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == PtApplicationTypeTable.GetTableDBName())
            {
                AResultTable = PtApplicationTypeAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == PFormalityTable.GetTableDBName())
            {
                AResultTable = PFormalityAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == PMailingTable.GetTableDBName())
            {
                AResultTable = PMailingAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
            {
                AResultTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
            }
            else if (ATablename == PmDocumentTypeTable.GetTableDBName())
            {
                AResultTable = PmDocumentTypeAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == SGroupTable.GetTableDBName())
            {
                TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context);
                AResultTable = SGroupAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == SSystemDefaultsTable.GetTableDBName())
            {
                TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context);
                AResultTable = SSystemDefaultsAccess.LoadAll(AReadTransaction);
            }
            else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName())
            {
                AResultTable = SSystemDefaultsGuiAccess.LoadAll(AReadTransaction);
            }
            else
            {
                throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
            }

            // Accept row changes here so that the Client gets 'unmodified' rows
            AResultTable.AcceptChanges();
        }
        public static TSubmitChangesResult SaveData(string ATablename,
                                                    ref TTypedDataTable ASubmitTable, out TVerificationResultCollection AVerificationResult,
                                                    TDBTransaction AWriteTransaction)
        {
            AVerificationResult = null;

            // TODO: check write permissions
            string context = string.Format("SaveData {0}", SharedConstants.MODULE_ACCESS_MANAGER);

            if (ASubmitTable != null)
            {
                AVerificationResult = new TVerificationResultCollection();

                try
                {
                    if (ATablename == AAccountingPeriodTable.GetTableDBName())
                    {
                        AAccountingPeriodAccess.SubmitChanges((AAccountingPeriodTable)ASubmitTable, AWriteTransaction);

                        TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                            TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
                    }
                    else if (ATablename == ACurrencyTable.GetTableDBName())
                    {
                        ACurrencyAccess.SubmitChanges((ACurrencyTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
                    {
                        TSecurityChecks.CheckUserModulePermissions(
                            string.Format("AND({0},{1})", SharedConstants.PETRAGROUP_FINANCE1, SharedConstants.PETRAMODULE_FINEXRATE),
                            context);
                        ADailyExchangeRateAccess.SubmitChanges((ADailyExchangeRateTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
                    {
                        // AlanP:  I don't think this is used any more.  There is a TDS Save method instead
                        ACorporateExchangeRateAccess.SubmitChanges((ACorporateExchangeRateTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
                    {
                        ACurrencyLanguageAccess.SubmitChanges((ACurrencyLanguageTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == AFeesPayableTable.GetTableDBName())
                    {
                        AFeesPayableAccess.SubmitChanges((AFeesPayableTable)ASubmitTable, AWriteTransaction);

                        TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                            TCacheableFinanceTablesEnum.FeesPayableList.ToString());
                    }
                    else if (ATablename == AFeesReceivableTable.GetTableDBName())
                    {
                        AFeesReceivableAccess.SubmitChanges((AFeesReceivableTable)ASubmitTable, AWriteTransaction);

                        TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                            TCacheableFinanceTablesEnum.FeesReceivableList.ToString());
                    }
                    else if (ATablename == AGiftBatchTable.GetTableDBName())
                    {
                        // This method is called from ADailyExchangeRate Setup - please do not remove
                        // The method is not required for changes made to the gift batch screens, which use a TDS
                        AGiftBatchAccess.SubmitChanges((AGiftBatchTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == AJournalTable.GetTableDBName())
                    {
                        // This method is called from ADailyExchangeRate Setup - please do not remove
                        // The method is not required for changes made to the journal screens, which use a TDS
                        AJournalAccess.SubmitChanges((AJournalTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == ARecurringJournalTable.GetTableDBName())
                    {
                        // This method is called from Submit Recurring GL Batch form - please do not remove
                        // The method is not required for changes made to the journal screens, which use a TDS
                        ARecurringJournalAccess.SubmitChanges((ARecurringJournalTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == ALedgerTable.GetTableDBName())
                    {
                        // This method is called from ADailyExchangeRate Testing - please do not remove
                        ALedgerAccess.SubmitChanges((ALedgerTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == AAnalysisTypeTable.GetTableDBName())
                    {
                        AAnalysisTypeAccess.SubmitChanges((AAnalysisTypeTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == ASuspenseAccountTable.GetTableDBName())
                    {
                        ASuspenseAccountAccess.SubmitChanges((ASuspenseAccountTable)ASubmitTable, AWriteTransaction);

                        TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                            TCacheableFinanceTablesEnum.SuspenseAccountList.ToString());
                    }
                    else if (ATablename == PcAttendeeTable.GetTableDBName())
                    {
                        PcAttendeeAccess.SubmitChanges((PcAttendeeTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PcConferenceTable.GetTableDBName())
                    {
                        PcConferenceAccess.SubmitChanges((PcConferenceTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PcConferenceCostTable.GetTableDBName())
                    {
                        PcConferenceCostAccess.SubmitChanges((PcConferenceCostTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PcEarlyLateTable.GetTableDBName())
                    {
                        PcEarlyLateAccess.SubmitChanges((PcEarlyLateTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PcSupplementTable.GetTableDBName())
                    {
                        PcSupplementAccess.SubmitChanges((PcSupplementTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PcDiscountTable.GetTableDBName())
                    {
                        PcDiscountAccess.SubmitChanges((PcDiscountTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
                    {
                        ValidateInternationalPostalType(ref AVerificationResult, ASubmitTable);
                        ValidateInternationalPostalTypeManual(ref AVerificationResult, ASubmitTable);

                        if (TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult))
                        {
                            PInternationalPostalTypeAccess.SubmitChanges((PInternationalPostalTypeTable)ASubmitTable, AWriteTransaction);
                        }
                    }
                    else if (ATablename == PtApplicationTypeTable.GetTableDBName())
                    {
                        PtApplicationTypeAccess.SubmitChanges((PtApplicationTypeTable)ASubmitTable, AWriteTransaction);

                        // mark dependent lists for needing to be refreshed since there was a change in base list
                        TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                            TCacheablePersonTablesEnum.EventApplicationTypeList.ToString());
                        TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                            TCacheablePersonTablesEnum.FieldApplicationTypeList.ToString());
                    }
                    else if (ATablename == PFormTable.GetTableDBName())
                    {
                        PFormAccess.SubmitChanges((PFormTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PFormalityTable.GetTableDBName())
                    {
                        PFormalityAccess.SubmitChanges((PFormalityTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PMailingTable.GetTableDBName())
                    {
                        PMailingAccess.SubmitChanges((PMailingTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
                    {
                        PPartnerGiftDestinationAccess.SubmitChanges((PPartnerGiftDestinationTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == PmDocumentTypeTable.GetTableDBName())
                    {
                        PmDocumentTypeAccess.SubmitChanges((PmDocumentTypeTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == SGroupTable.GetTableDBName())
                    {
                        SGroupAccess.SubmitChanges((SGroupTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == SSystemDefaultsTable.GetTableDBName())
                    {
                        SSystemDefaultsAccess.SubmitChanges((SSystemDefaultsTable)ASubmitTable, AWriteTransaction);
                    }
                    else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName())
                    {
                        SSystemDefaultsGuiAccess.SubmitChanges((SSystemDefaultsGuiTable)ASubmitTable, AWriteTransaction);
                    }
                    else
                    {
                        throw new EOPAppException("TCommonDataReader.SaveData: unknown table '" + ATablename + "'");
                    }
                }
                catch (Exception Exc)
                {
                    AVerificationResult.Add(
                        new TVerificationResult(null, "Cannot SubmitChanges:" + Environment.NewLine +
                                                Exc.Message, "UNDEFINED", TResultSeverity.Resv_Critical));
                }
            }

            if ((AVerificationResult != null) &&
                (AVerificationResult.Count > 0))
            {
                // Downgrade TScreenVerificationResults to TVerificationResults in order to allow
                // Serialisation (needed for .NET Remoting).
                TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);

                return(AVerificationResult.HasCriticalErrors ? TSubmitChangesResult.scrError : TSubmitChangesResult.scrOK);
            }

            return(TSubmitChangesResult.scrOK);
        }
        /// <summary>
        /// get daily exchange rate for the given currencies and date;
        /// TODO: might even collect the latest exchange rate from the web
        /// </summary>
        /// <param name="ACurrencyFrom"></param>
        /// <param name="ACurrencyTo"></param>
        /// <param name="ADateEffective"></param>
        /// <returns>Zero if no exchange rate found</returns>
        public static decimal GetDailyExchangeRate(string ACurrencyFrom, string ACurrencyTo, DateTime ADateEffective)
        {
            if (ACurrencyFrom == ACurrencyTo)
            {
                return(1.0M);
            }

            bool           NewTransaction;
            TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction
                                             (IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction);

            ADailyExchangeRateRow fittingRate = null;

            // TODO: get the most recent exchange rate for the given date and currencies
            bool oppositeRate             = false;
            ADailyExchangeRateTable rates = ADailyExchangeRateAccess.LoadByPrimaryKey(ACurrencyFrom, ACurrencyTo, ADateEffective, 0, Transaction);

            if (rates.Count == 0)
            {
                // try other way round
                rates        = ADailyExchangeRateAccess.LoadByPrimaryKey(ACurrencyTo, ACurrencyFrom, ADateEffective, 0, Transaction);
                oppositeRate = true;
            }

            if (rates.Count == 1)
            {
                fittingRate = rates[0];
            }
            else if (rates.Count == 0)
            {
                // TODO: collect exchange rate from the web; save to db
                // see tracker http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=87

                // Or look for most recent exchange rate???
                ADailyExchangeRateTable tempTable   = new ADailyExchangeRateTable();
                ADailyExchangeRateRow   templateRow = tempTable.NewRowTyped(false);
                templateRow.FromCurrencyCode = ACurrencyFrom;
                templateRow.ToCurrencyCode   = ACurrencyTo;
                oppositeRate = false;
                rates        = ADailyExchangeRateAccess.LoadUsingTemplate(templateRow, Transaction);

                if (rates.Count == 0)
                {
                    templateRow.FromCurrencyCode = ACurrencyTo;
                    templateRow.ToCurrencyCode   = ACurrencyFrom;
                    oppositeRate = true;
                    rates        = ADailyExchangeRateAccess.LoadUsingTemplate(templateRow, Transaction);
                }

                if (rates.Count > 0)
                {
                    // sort rates by date, look for rate just before the date we are looking for
                    rates.DefaultView.Sort      = ADailyExchangeRateTable.GetDateEffectiveFromDBName();
                    rates.DefaultView.RowFilter = ADailyExchangeRateTable.GetDateEffectiveFromDBName() + "<= #" +
                                                  ADateEffective.ToString("yyyy-MM-dd") + "#";

                    if (rates.DefaultView.Count > 0)
                    {
                        fittingRate = (ADailyExchangeRateRow)rates.DefaultView[rates.DefaultView.Count - 1].Row;
                    }
                }
            }

            if (NewTransaction)
            {
                DBAccess.GDBAccessObj.RollbackTransaction();
            }

            if (fittingRate != null)
            {
                if (oppositeRate)
                {
                    return(1.0M / fittingRate.RateOfExchange);
                }

                return(fittingRate.RateOfExchange);
            }

            TLogging.Log("Cannot find daily exchange rate for " + ACurrencyFrom + " " + ACurrencyTo);

            //return 1.0M;
            //Instead, cause a validation error to force the user to select an exchange rate
            return(0M);
        }
        public static bool GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable)
        {
            // TODO: check access permissions for the current user

            TDBTransaction ReadTransaction = null;

            TTypedDataTable tempTable = null;

            DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.RepeatableRead,
                                                                      TEnforceIsolationLevel.eilMinimum,
                                                                      ref ReadTransaction,
                                                                      delegate
            {
                // TODO: auto generate
                if (ATablename == AApSupplierTable.GetTableDBName())
                {
                    tempTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == AApDocumentTable.GetTableDBName())
                {
                    tempTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == ATransactionTypeTable.GetTableDBName())
                {
                    tempTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == ACurrencyTable.GetTableDBName())
                {
                    tempTable = ACurrencyAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
                {
                    tempTable = ADailyExchangeRateAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
                {
                    tempTable = ACorporateExchangeRateAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
                {
                    tempTable = ACurrencyLanguageAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AFeesPayableTable.GetTableDBName())
                {
                    tempTable = AFeesPayableAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AFeesReceivableTable.GetTableDBName())
                {
                    tempTable = AFeesReceivableAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AAnalysisTypeTable.GetTableDBName())
                {
                    tempTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == AGiftBatchTable.GetTableDBName())
                {
                    tempTable = AGiftBatchAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AJournalTable.GetTableDBName())
                {
                    tempTable = AJournalAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == ALedgerTable.GetTableDBName())
                {
                    tempTable = ALedgerAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == MExtractMasterTable.GetTableDBName())
                {
                    if (ASearchCriteria == null)
                    {
                        tempTable = MExtractMasterAccess.LoadAll(ReadTransaction);
                    }
                    else
                    {
                        tempTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                    }
                }
                else if (ATablename == MExtractTable.GetTableDBName())
                {
                    // it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
                    if (ASearchCriteria != null)
                    {
                        tempTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                    }
                }
                else if (ATablename == PcAttendeeTable.GetTableDBName())
                {
                    tempTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PcConferenceCostTable.GetTableDBName())
                {
                    tempTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PcEarlyLateTable.GetTableDBName())
                {
                    tempTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PcSupplementTable.GetTableDBName())
                {
                    tempTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PcDiscountTable.GetTableDBName())
                {
                    tempTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PFormTable.GetTableDBName())
                {
                    string[] columns           = TTypedDataTable.GetColumnStringList(PFormTable.TableId);
                    StringCollection fieldList = new StringCollection();

                    for (int i = 0; i < columns.Length; i++)
                    {
                        // Do not load the template document - we don't display it and it is big!
                        if (columns[i] != PFormTable.GetTemplateDocumentDBName())
                        {
                            fieldList.Add(columns[i]);
                        }
                    }

                    tempTable = PFormAccess.LoadAll(fieldList, ReadTransaction);
                }
                else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
                {
                    tempTable = PInternationalPostalTypeAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == PtApplicationTypeTable.GetTableDBName())
                {
                    tempTable = PtApplicationTypeAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == PFormalityTable.GetTableDBName())
                {
                    tempTable = PFormalityAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == PMailingTable.GetTableDBName())
                {
                    tempTable = PMailingAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
                {
                    tempTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PmDocumentTypeTable.GetTableDBName())
                {
                    tempTable = PmDocumentTypeAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == SGroupTable.GetTableDBName())
                {
                    tempTable = SGroupAccess.LoadAll(ReadTransaction);
                }
                else
                {
                    throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
                }
            });

            // Accept row changes here so that the Client gets 'unmodified' rows
            tempTable.AcceptChanges();

            // return the table
            AResultTable = tempTable;

            return(true);
        }
        public static TSubmitChangesResult SaveData(string ATablename,
                                                    ref TTypedDataTable ASubmitTable,
                                                    out TVerificationResultCollection AVerificationResult)
        {
            TDBTransaction  SubmitChangesTransaction = null;
            bool            SubmissionOK             = false;
            TTypedDataTable SubmitTable = ASubmitTable;

            TVerificationResultCollection VerificationResult = null;

            // TODO: check write permissions

            if (ASubmitTable != null)
            {
                VerificationResult = new TVerificationResultCollection();

                DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref SubmitChangesTransaction, ref SubmissionOK,
                                                           delegate
                {
                    try
                    {
                        if (ATablename == AAccountingPeriodTable.GetTableDBName())
                        {
                            AAccountingPeriodAccess.SubmitChanges((AAccountingPeriodTable)SubmitTable, SubmitChangesTransaction);

                            TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                                TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
                        }
                        else if (ATablename == ACurrencyTable.GetTableDBName())
                        {
                            ACurrencyAccess.SubmitChanges((ACurrencyTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
                        {
                            ADailyExchangeRateAccess.SubmitChanges((ADailyExchangeRateTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
                        {
                            ACorporateExchangeRateAccess.SubmitChanges((ACorporateExchangeRateTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
                        {
                            ACurrencyLanguageAccess.SubmitChanges((ACurrencyLanguageTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == AFeesPayableTable.GetTableDBName())
                        {
                            AFeesPayableAccess.SubmitChanges((AFeesPayableTable)SubmitTable, SubmitChangesTransaction);

                            TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                                TCacheableFinanceTablesEnum.FeesPayableList.ToString());
                        }
                        else if (ATablename == AFeesReceivableTable.GetTableDBName())
                        {
                            AFeesReceivableAccess.SubmitChanges((AFeesReceivableTable)SubmitTable, SubmitChangesTransaction);

                            TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                                TCacheableFinanceTablesEnum.FeesReceivableList.ToString());
                        }
                        else if (ATablename == AGiftBatchTable.GetTableDBName())
                        {
                            // This method is called from ADailyExchangeRate Setup - please do not remove
                            // The method is not required for changes made to the gift batch screens, which use a TDS
                            AGiftBatchAccess.SubmitChanges((AGiftBatchTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == AJournalTable.GetTableDBName())
                        {
                            // This method is called from ADailyExchangeRate Setup - please do not remove
                            // The method is not required for changes made to the journal screens, which use a TDS
                            AJournalAccess.SubmitChanges((AJournalTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == ARecurringJournalTable.GetTableDBName())
                        {
                            // This method is called from Submit Recurring GL Batch form - please do not remove
                            // The method is not required for changes made to the journal screens, which use a TDS
                            ARecurringJournalAccess.SubmitChanges((ARecurringJournalTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == ALedgerTable.GetTableDBName())
                        {
                            // This method is called from ADailyExchangeRate Testing - please do not remove
                            ALedgerAccess.SubmitChanges((ALedgerTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == AAnalysisTypeTable.GetTableDBName())
                        {
                            AAnalysisTypeAccess.SubmitChanges((AAnalysisTypeTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == ASuspenseAccountTable.GetTableDBName())
                        {
                            ASuspenseAccountAccess.SubmitChanges((ASuspenseAccountTable)SubmitTable, SubmitChangesTransaction);

                            TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                                TCacheableFinanceTablesEnum.SuspenseAccountList.ToString());
                        }
                        else if (ATablename == PcAttendeeTable.GetTableDBName())
                        {
                            PcAttendeeAccess.SubmitChanges((PcAttendeeTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PcConferenceTable.GetTableDBName())
                        {
                            PcConferenceAccess.SubmitChanges((PcConferenceTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PcConferenceCostTable.GetTableDBName())
                        {
                            PcConferenceCostAccess.SubmitChanges((PcConferenceCostTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PcEarlyLateTable.GetTableDBName())
                        {
                            PcEarlyLateAccess.SubmitChanges((PcEarlyLateTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PcSupplementTable.GetTableDBName())
                        {
                            PcSupplementAccess.SubmitChanges((PcSupplementTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PcDiscountTable.GetTableDBName())
                        {
                            PcDiscountAccess.SubmitChanges((PcDiscountTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
                        {
                            ValidateInternationalPostalType(ref VerificationResult, SubmitTable);
                            ValidateInternationalPostalTypeManual(ref VerificationResult, SubmitTable);

                            if (TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult))
                            {
                                PInternationalPostalTypeAccess.SubmitChanges((PInternationalPostalTypeTable)SubmitTable, SubmitChangesTransaction);
                            }
                        }
                        else if (ATablename == PtApplicationTypeTable.GetTableDBName())
                        {
                            PtApplicationTypeAccess.SubmitChanges((PtApplicationTypeTable)SubmitTable, SubmitChangesTransaction);

                            // mark dependent lists for needing to be refreshed since there was a change in base list
                            TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                                TCacheablePersonTablesEnum.EventApplicationTypeList.ToString());
                            TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
                                TCacheablePersonTablesEnum.FieldApplicationTypeList.ToString());
                        }
                        else if (ATablename == PFormTable.GetTableDBName())
                        {
                            PFormAccess.SubmitChanges((PFormTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PFormalityTable.GetTableDBName())
                        {
                            PFormalityAccess.SubmitChanges((PFormalityTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PMailingTable.GetTableDBName())
                        {
                            PMailingAccess.SubmitChanges((PMailingTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
                        {
                            PPartnerGiftDestinationAccess.SubmitChanges((PPartnerGiftDestinationTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == PmDocumentTypeTable.GetTableDBName())
                        {
                            PmDocumentTypeAccess.SubmitChanges((PmDocumentTypeTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else if (ATablename == SGroupTable.GetTableDBName())
                        {
                            SGroupAccess.SubmitChanges((SGroupTable)SubmitTable, SubmitChangesTransaction);
                        }
                        else
                        {
                            throw new EOPAppException("TCommonDataReader.SaveData: unknown table '" + ATablename + "'");
                        }

                        SubmissionOK = true;
                    }
                    catch (Exception Exc)
                    {
                        VerificationResult.Add(
                            new TVerificationResult(null, "Cannot SubmitChanges:" + Environment.NewLine +
                                                    Exc.Message, "UNDEFINED", TResultSeverity.Resv_Critical));
                    }
                });
            }

            ASubmitTable        = SubmitTable;
            AVerificationResult = VerificationResult;

            if ((AVerificationResult != null) &&
                (AVerificationResult.Count > 0))
            {
                // Downgrade TScreenVerificationResults to TVerificationResults in order to allow
                // Serialisation (needed for .NET Remoting).
                TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);

                return(AVerificationResult.HasCriticalErrors ? TSubmitChangesResult.scrError : TSubmitChangesResult.scrOK);
            }

            return(TSubmitChangesResult.scrOK);
        }
예제 #8
0
        public static ExchangeRateTDS LoadDailyExchangeRateData()
        {
            ExchangeRateTDS WorkingDS = new ExchangeRateTDS();

            WorkingDS.EnforceConstraints = false;
            TDBTransaction Transaction = null;

            DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                                                                      TEnforceIsolationLevel.eilMinimum,
                                                                      ref Transaction,
                                                                      delegate
            {
                // Load the table so we can read bits of it into our dataset
                ADailyExchangeRateTable exchangeRates = ADailyExchangeRateAccess.LoadAll(Transaction);

                // Populate the ExchangeRateTDSADailyExchangeRate table
                //-- This is the complete query for the DAILYEXCHANGERATE TABLE
                //-- It returns all the rows from the DailyExchangeRate table
                //-- PLUS all rows from the Journal and Gift Batch tables that are NOT referenced by the Daily Exchange Rate table.
                string strSQL = "SELECT * FROM ( ";

                // -- This part of the query returns all the rows from the ExchangeRate table
                // -- All rows in Journal and Gift that DO match an entry are reported in the usage count
                // -- It will always return exactly the same number of rows that are in the Daily Exchange Rate table itself.
                // --  In the development database case it returns 312 rows.
                // --  It includes 86 Journal entries and 1 Gift Batch entry
                strSQL += "SELECT der.a_from_currency_code_c, ";
                strSQL += "  der.a_to_currency_code_c, ";
                strSQL += "  der.a_rate_of_exchange_n, ";
                strSQL += "  der.a_date_effective_from_d, ";
                strSQL += "  der.a_time_effective_from_i, ";
                strSQL += String.Format(
                    "  count(j.a_journal_number_i) AS {0}, count(gb.a_batch_number_i) AS {1}, 'DEX' as {2} ",
                    ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
                    ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
                    ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
                strSQL += "FROM PUB_a_daily_exchange_rate AS der ";
                strSQL += "LEFT OUTER JOIN PUB_a_journal j ";
                strSQL += "  ON j.a_transaction_currency_c = der.a_from_currency_code_c ";
                strSQL += "  AND j.a_base_currency_c = der.a_to_currency_code_c ";
                strSQL += "  AND j.a_exchange_rate_to_base_n = der.a_rate_of_exchange_n ";
                strSQL += "  AND j.a_date_effective_d = der.a_date_effective_from_d ";
                strSQL += "  AND a_exchange_rate_time_i = der.a_time_effective_from_i ";
                strSQL += "LEFT OUTER JOIN PUB_a_gift_batch AS gb ";
                strSQL += "  ON gb.a_currency_code_c = der.a_from_currency_code_c ";
                strSQL += "  AND gb.a_exchange_rate_to_base_n = der.a_rate_of_exchange_n ";
                strSQL += "  AND gb.a_gl_effective_date_d = der.a_date_effective_from_d ";
                strSQL += "LEFT OUTER JOIN PUB_a_ledger AS ldg ";
                strSQL += "  ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
                strSQL += "  AND ldg.a_base_currency_c = der.a_to_currency_code_c ";
                strSQL +=
                    "GROUP BY der.a_from_currency_code_c, der.a_to_currency_code_c, der.a_rate_of_exchange_n, der.a_date_effective_from_d, der.a_time_effective_from_i ";

                strSQL += Environment.NewLine;
                strSQL += "UNION ";
                strSQL += Environment.NewLine;

                // -- This part of the query returns all the rows from the journal table that do NOT have an
                // -- entry in the exchange rate table
                // -- Using the devlopment database it returns 41 unique rows associated with 53 Journal entries
                strSQL += "SELECT ";
                strSQL += "  j.a_transaction_currency_c AS a_from_currency_code_c, ";
                strSQL += "  j.a_base_currency_c AS a_to_currency_code_c, ";
                strSQL += "  j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
                strSQL += "  j.a_date_effective_d AS a_date_effective_from_d, ";
                strSQL += "  j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
                strSQL += String.Format(
                    "  count(j.a_transaction_currency_c) AS {0},  0 AS {1},  'J' AS {2} ",
                    ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
                    ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
                    ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
                strSQL += "FROM a_journal j ";
                strSQL += "LEFT JOIN a_daily_exchange_rate der ";
                strSQL += "  ON der.a_from_currency_code_c = j.a_transaction_currency_c ";
                strSQL += "  AND der.a_to_currency_code_c = j.a_base_currency_c ";
                strSQL += "  AND der.a_date_effective_from_d = j.a_date_effective_d ";
                strSQL += "  AND der.a_time_effective_from_i = j.a_exchange_rate_time_i ";
                strSQL += "  AND der.a_rate_of_exchange_n = j.a_exchange_rate_to_base_n ";
                strSQL += "WHERE j.a_transaction_currency_c <> j.a_base_currency_c ";
                strSQL += "  AND der.a_from_currency_code_c IS NULL ";
                strSQL +=
                    "GROUP BY j.a_transaction_currency_c, j.a_base_currency_c, j.a_exchange_rate_to_base_n, j.a_date_effective_d, j.a_exchange_rate_time_i ";

                strSQL += Environment.NewLine;
                strSQL += "UNION ";
                strSQL += Environment.NewLine;

                // -- This part of the query returns all the rows in the gift batch table that do NOT have an
                // -- entry in the exchange rate table
                // -- Using the devlopment database it returns 0 rows, because the one row in the gift batch table has already been included
                // --   in the first query (on the Daily Exchange rate table)
                strSQL += "SELECT ";
                strSQL += "  gb.a_currency_code_c AS a_from_currency_code_c, ";
                strSQL += "  ldg.a_base_currency_c AS a_to_currency_code_c, ";
                strSQL += "  gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
                strSQL += "  gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
                strSQL += "  0 AS a_time_effective_from_i, ";
                strSQL += String.Format(
                    "  0 AS {0}, count(gb.a_currency_code_c) AS {1}, 'GB' AS {2} ",
                    ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
                    ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
                    ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
                strSQL += "FROM a_gift_batch gb ";
                strSQL += "LEFT JOIN a_ledger ldg ";
                strSQL += "  ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
                strSQL += "LEFT JOIN a_daily_exchange_rate der ";
                strSQL += "  ON der.a_from_currency_code_c = gb.a_currency_code_c ";
                strSQL += "  AND der.a_to_currency_code_c = ldg.a_base_currency_c ";
                strSQL += "  AND der.a_date_effective_from_d = gb.a_gl_effective_date_d ";
                strSQL += "  AND der.a_rate_of_exchange_n = gb.a_exchange_rate_to_base_n ";
                strSQL += "WHERE gb.a_currency_code_c <> ldg.a_base_currency_c ";
                strSQL += "  AND der.a_from_currency_code_c IS NULL ";
                strSQL += "GROUP BY gb.a_currency_code_c, ldg.a_base_currency_c, gb.a_exchange_rate_to_base_n, gb.a_gl_effective_date_d ";

                strSQL += ") AS allrates ";
                strSQL += "ORDER BY allrates.a_date_effective_from_d DESC, allrates.a_time_effective_from_i DESC ";

                DBAccess.GDBAccessObj.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRate.TableName, Transaction);

                // Now populate the ExchangeRateTDSADailyExchangerateUsage table
                //-- COMPLETE QUERY TO RETURN ADailyExchangeRateUsage
                //-- Query to return the Daily Exchange Rate Usage details
                //--  Only returns rows that are in a foreign currency
                //-- Querying this table by from/to/date/time will return one row per use case
                //-- If the Journal is 0 the batch refers to a gift batch, otherwise it is a GL batch
                strSQL = "SELECT * FROM ( ";

                //-- This part of the query returns the use cases from the Journal table
                strSQL += "SELECT ";
                strSQL += "  j.a_transaction_currency_c AS a_from_currency_code_c, ";
                strSQL += "  j.a_base_currency_c AS a_to_currency_code_c, ";
                strSQL += "  j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
                strSQL += "  j.a_date_effective_d AS a_date_effective_from_d, ";
                strSQL += "  j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
                strSQL += String.Format(
                    "  j.a_ledger_number_i AS {0}, j.a_batch_number_i AS {1}, j.a_journal_number_i AS {2}, b.a_batch_status_c AS {3}, b.a_batch_year_i AS {4}, b.a_batch_period_i AS {5}, 'J' AS {6} ",
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
                strSQL += "FROM a_journal j ";
                strSQL += "JOIN a_batch b ";
                strSQL += "  ON b.a_batch_number_i = j.a_batch_number_i ";
                strSQL += "  AND b.a_ledger_number_i = j.a_ledger_number_i ";
                strSQL += "WHERE j.a_transaction_currency_c <> j.a_base_currency_c ";

                strSQL += Environment.NewLine;
                strSQL += "UNION ";
                strSQL += Environment.NewLine;

                //-- This part of the query returns the use cases from the Gift Batch table
                strSQL += "SELECT ";
                strSQL += "  gb.a_currency_code_c AS a_from_currency_code_c, ";
                strSQL += "  ldg.a_base_currency_c AS a_to_currency_code_c, ";
                strSQL += "  gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
                strSQL += "  gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
                strSQL += "  0 AS a_time_effective_from_i, ";
                strSQL += String.Format(
                    "  gb.a_ledger_number_i AS {0}, gb.a_batch_number_i AS {1}, 0 AS {2}, gb.a_batch_status_c AS {3}, gb.a_batch_year_i AS {4}, gb.a_batch_period_i AS {5}, 'GB' AS {6} ",
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
                    ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
                strSQL += "FROM a_gift_batch gb ";
                strSQL += "JOIN a_ledger ldg ";
                strSQL += "  ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
                strSQL += "WHERE gb.a_currency_code_c <> ldg.a_base_currency_c ";

                strSQL += ") AS usage ";
                strSQL += "ORDER BY usage.a_date_effective_from_d DESC, usage.a_time_effective_from_i DESC ";

                DBAccess.GDBAccessObj.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRateUsage.TableName, Transaction);

                // Now we start a tricky bit to resolve potential primary key conflicts when the constraints are turned on.
                // By combining the Journal and Gift Batch data that is not referenced in the exchange rate table we can easily
                //  have introduced conflicts where more than one rate has been used for a given currency pair and effective date/time.
                // This is because there is no constraint that enforces the batch/journal tables to use a time from the exch rate table.
                // So we have to go through all the rows in our data table and potentially change the time to make it possible to get our primary key.

                // Start by creating a data view on the whole result set.  The oredering is important because we are going to step through the set row by row.
                // We order on: From, To, Date, Time, JournalUsage, GiftBatchUsage
                // We need to deal with the following possibilities:
                //   From  To   Date         Time   Rate  Journals  Gifts  TableSource
                //   EUR   GBP  2014-01-01   1234   2.115  0        0      DEX
                //   EUR   GBP  2014-01-01   1234   2.11   3        0      J
                //   EUR   GBP  2014-01-01   1234   2.22   1        0      J
                //   EUR   GBP  2014-01-01   1234   3.11   0        1      GB
                //
                // In the first row we have an entry from the DEX table that is not used anywhere, but a (slightly) different rate is actually used
                //   in a Journal.  So we actually don't show the DEX row.
                // In the other rows we have 3 different rates - all used somewhere.  We need to adjust the times so they are different.

                DataView dv = new DataView(WorkingDS.ADailyExchangeRate, "",
                                           String.Format("{0}, {1}, {2} DESC, {3} DESC, {4}, {5}",
                                                         ADailyExchangeRateTable.GetFromCurrencyCodeDBName(),
                                                         ADailyExchangeRateTable.GetToCurrencyCodeDBName(),
                                                         ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
                                                         ADailyExchangeRateTable.GetTimeEffectiveFromDBName(),
                                                         ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
                                                         ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName()), DataViewRowState.CurrentRows);

                for (int i = 0; i < dv.Count - 1; i++)
                {
                    // Get the 'current' row and the 'next' one...
                    ExchangeRateTDSADailyExchangeRateRow drThis = (ExchangeRateTDSADailyExchangeRateRow)dv[i].Row;
                    ExchangeRateTDSADailyExchangeRateRow drNext = (ExchangeRateTDSADailyExchangeRateRow)dv[i + 1].Row;

                    if ((drThis.JournalUsage == 0) && (drThis.GiftBatchUsage == 0))
                    {
                        // This will be a row that the client can edit/delete, so we need to add the modification info
                        ADailyExchangeRateRow foundRow = (ADailyExchangeRateRow)exchangeRates.Rows.Find(new object[] {
                            drThis.FromCurrencyCode, drThis.ToCurrencyCode, drThis.DateEffectiveFrom, drThis.TimeEffectiveFrom
                        });

                        if (foundRow != null)
                        {
                            // it should always be non-null
                            drThis.BeginEdit();
                            drThis.ModificationId = foundRow.ModificationId;
                            drThis.DateModified   = foundRow.DateModified;
                            drThis.ModifiedBy     = foundRow.ModifiedBy;
                            drThis.DateCreated    = foundRow.DateCreated;
                            drThis.CreatedBy      = foundRow.CreatedBy;
                            drThis.EndEdit();
                        }
                    }

                    if (!drThis.FromCurrencyCode.Equals(drNext.FromCurrencyCode) ||
                        !drThis.ToCurrencyCode.Equals(drNext.ToCurrencyCode) ||
                        !drThis.DateEffectiveFrom.Equals(drNext.DateEffectiveFrom) ||
                        !drThis.TimeEffectiveFrom.Equals(drNext.TimeEffectiveFrom))
                    {
                        // Something is different so our primary key will be ok for the current row
                        continue;
                    }

                    // We have got two (or more) rows with the same potential primary key and different rates.
                    // We need to work out how many rows ahead also have the same time and adjust them all
                    bool moveForwards = (drThis.TimeEffectiveFrom < 43200);
                    int timeOffset    = 60;         // 1 minute

                    // Start by adjusting our 'next' row we are already working with
                    drNext.BeginEdit();
                    int prevTimeEffectiveFrom = drNext.TimeEffectiveFrom;
                    drNext.TimeEffectiveFrom  = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
                    timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
                    drNext.EndEdit();
                    i++;                // we can increment our main loop counter now that we have dealt with our 'next' row.

                    // Modify all the rows in the usage table that refer to the previous time
                    OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drNext.FromCurrencyCode, drNext.ToCurrencyCode,
                                          drNext.DateEffectiveFrom,
                                          prevTimeEffectiveFrom, drNext.TimeEffectiveFrom, drNext.RateOfExchange);

                    // Now look ahead even further than the 'next' row and modify those times too, adding 1 more minute to each
                    for (int k = i + 2;; k++)
                    {
                        ExchangeRateTDSADailyExchangeRateRow drLookAhead = (ExchangeRateTDSADailyExchangeRateRow)dv[k].Row;

                        if (!drThis.FromCurrencyCode.Equals(drLookAhead.FromCurrencyCode) ||
                            !drThis.ToCurrencyCode.Equals(drLookAhead.ToCurrencyCode) ||
                            !drThis.DateEffectiveFrom.Equals(drLookAhead.DateEffectiveFrom) ||
                            !drThis.TimeEffectiveFrom.Equals(drLookAhead.TimeEffectiveFrom))
                        {
                            // No more rows match our potential primary key conflict on the 'current' row.
                            break;
                        }

                        // Do exactly the same to this row as we did to the 'next' row above
                        drLookAhead.BeginEdit();
                        prevTimeEffectiveFrom         = drLookAhead.TimeEffectiveFrom;
                        drLookAhead.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
                        timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
                        drLookAhead.EndEdit();
                        i++;

                        OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drLookAhead.FromCurrencyCode, drLookAhead.ToCurrencyCode,
                                              drLookAhead.DateEffectiveFrom, prevTimeEffectiveFrom, drLookAhead.TimeEffectiveFrom, drLookAhead.RateOfExchange);
                    }
                }           // check the next row in the table so that it becomes the 'current' row.

                WorkingDS.EnforceConstraints = true;

                // Load the Corporate exchange rate table using the usual method
                ACorporateExchangeRateAccess.LoadAll(WorkingDS, Transaction);
            });

            // Accept row changes here so that the Client gets 'unmodified' rows
            WorkingDS.AcceptChanges();

            return(WorkingDS);
        }
        public static bool GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable)
        {
            // TODO: check access permissions for the current user

            bool           NewTransaction = false;
            TDBTransaction ReadTransaction;

            TTypedDataTable tempTable = null;

            try
            {
                ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
                                                                                    TEnforceIsolationLevel.eilMinimum,
                                                                                    out NewTransaction);

                // TODO: auto generate
                if (ATablename == AApSupplierTable.GetTableDBName())
                {
                    tempTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == AApDocumentTable.GetTableDBName())
                {
                    tempTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == ATransactionTypeTable.GetTableDBName())
                {
                    tempTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == ACurrencyTable.GetTableDBName())
                {
                    tempTable = ACurrencyAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
                {
                    tempTable = ADailyExchangeRateAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
                {
                    tempTable = ACorporateExchangeRateAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
                {
                    tempTable = ACurrencyLanguageAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AFeesPayableTable.GetTableDBName())
                {
                    tempTable = AFeesPayableAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AFeesReceivableTable.GetTableDBName())
                {
                    tempTable = AFeesReceivableAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AAnalysisTypeTable.GetTableDBName())
                {
                    tempTable = AAnalysisTypeAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AGiftBatchTable.GetTableDBName())
                {
                    tempTable = AGiftBatchAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == AJournalTable.GetTableDBName())
                {
                    tempTable = AJournalAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == ALedgerTable.GetTableDBName())
                {
                    tempTable = ALedgerAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == MExtractMasterTable.GetTableDBName())
                {
                    if (ASearchCriteria == null)
                    {
                        tempTable = MExtractMasterAccess.LoadAll(ReadTransaction);
                    }
                    else
                    {
                        tempTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                    }
                }
                else if (ATablename == MExtractTable.GetTableDBName())
                {
                    // it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
                    if (ASearchCriteria != null)
                    {
                        tempTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                    }
                }
                else if (ATablename == PcAttendeeTable.GetTableDBName())
                {
                    tempTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PcConferenceCostTable.GetTableDBName())
                {
                    tempTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PcEarlyLateTable.GetTableDBName())
                {
                    tempTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PcSupplementTable.GetTableDBName())
                {
                    tempTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PcDiscountTable.GetTableDBName())
                {
                    tempTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
                {
                    tempTable = PInternationalPostalTypeAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == PtApplicationTypeTable.GetTableDBName())
                {
                    tempTable = PtApplicationTypeAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == PMailingTable.GetTableDBName())
                {
                    tempTable = PMailingAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
                {
                    tempTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
                }
                else if (ATablename == PmDocumentTypeTable.GetTableDBName())
                {
                    tempTable = PmDocumentTypeAccess.LoadAll(ReadTransaction);
                }
                else if (ATablename == SGroupTable.GetTableDBName())
                {
                    tempTable = SGroupAccess.LoadAll(ReadTransaction);
                }
                else
                {
                    throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
                }
            }
            catch (Exception Exp)
            {
                DBAccess.GDBAccessObj.RollbackTransaction();
                TLogging.Log("TCommonDataReader.GetData exception: " + Exp.ToString(), TLoggingType.ToLogfile);
                TLogging.Log(Exp.StackTrace, TLoggingType.ToLogfile);
                throw;
            }
            finally
            {
                if (NewTransaction)
                {
                    DBAccess.GDBAccessObj.CommitTransaction();
                    TLogging.LogAtLevel(7, "TCommonDataReader.GetData: committed own transaction.");
                }
            }

            // Accept row changes here so that the Client gets 'unmodified' rows
            tempTable.AcceptChanges();

            // return the table
            AResultTable = tempTable;

            return(true);
        }
예제 #10
0
        /// <summary>
        /// init the exchange rate, to avoid messages "Cannot find exchange rate for EUR USD"
        /// </summary>
        public static void InitExchangeRate(TDataBase ADataBase = null)
        {
            TDataBase      db          = DBAccess.Connect("InitExchangeRate", ADataBase);
            TDBTransaction Transaction = new TDBTransaction();
            bool           SubmitOK    = false;

            db.WriteTransaction(ref Transaction,
                                ref SubmitOK,
                                delegate
            {
                TAccountPeriodInfo AccountingPeriodInfo =
                    new TAccountPeriodInfo(FLedgerNumber, 1);
                ADailyExchangeRateTable dailyrates = new ADailyExchangeRateTable();
                ADailyExchangeRateRow row          = dailyrates.NewRowTyped(true);

                row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
                row.TimeEffectiveFrom = 100;
                row.FromCurrencyCode  = "USD";
                row.ToCurrencyCode    = "EUR";
                row.RateOfExchange    = 1.34m;
                dailyrates.Rows.Add(row);
                row = dailyrates.NewRowTyped(true);
                row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
                row.TimeEffectiveFrom = 100;
                row.FromCurrencyCode  = "USD";
                row.ToCurrencyCode    = "GBP";
                row.RateOfExchange    = 1.57m;
                dailyrates.Rows.Add(row);

                if (!ADailyExchangeRateAccess.Exists(row.FromCurrencyCode, row.ToCurrencyCode, row.DateEffectiveFrom, row.TimeEffectiveFrom, Transaction))
                {
                    ADailyExchangeRateAccess.SubmitChanges(dailyrates, Transaction);
                }

                ALedgerTable Ledger = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction);

                for (int periodCounter = 1; periodCounter <= Ledger[0].NumberOfAccountingPeriods + Ledger[0].NumberFwdPostingPeriods; periodCounter++)
                {
                    AccountingPeriodInfo = new TAccountPeriodInfo(FLedgerNumber, periodCounter);

                    ACorporateExchangeRateTable corprates = new ACorporateExchangeRateTable();
                    ACorporateExchangeRateRow corprow     = corprates.NewRowTyped(true);
                    corprow.DateEffectiveFrom             = AccountingPeriodInfo.PeriodStartDate;
                    corprow.TimeEffectiveFrom             = 100;
                    corprow.FromCurrencyCode = "USD";
                    corprow.ToCurrencyCode   = "EUR";
                    corprow.RateOfExchange   = 1.34m;
                    corprates.Rows.Add(corprow);
                    corprow = corprates.NewRowTyped(true);
                    corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
                    corprow.TimeEffectiveFrom = 100;
                    corprow.FromCurrencyCode  = "USD";
                    corprow.ToCurrencyCode    = "GBP";
                    corprow.RateOfExchange    = 1.57m;
                    corprates.Rows.Add(corprow);

                    if (!ACorporateExchangeRateAccess.Exists(corprow.FromCurrencyCode, corprow.ToCurrencyCode, corprow.DateEffectiveFrom, Transaction))
                    {
                        ACorporateExchangeRateAccess.SubmitChanges(corprates, Transaction);
                    }
                }
                SubmitOK = true;
            });

            if (ADataBase == null)
            {
                db.CloseDBConnection();
            }
        }