/// <summary>
        /// Gets called in regular intervals from a Timer in Class TTimedProcessing.
        /// </summary>
        /// <param name="ADBAccessObj">Instantiated DB Access object with opened DB connection.</param>
        /// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
        public static void Process(TDataBase ADBAccessObj, bool ARunManually)
        {
            // only check once a day (or as specified in config file), if not manually called
            if (!ARunManually)
            {
                DateTime LastRun =
                    TVariant.DecodeFromString(


                        TSystemDefaults.GetSystemDefault(
                            PROCESSDATACHECK_LAST_RUN,
                            new TVariant(DateTime.MinValue).EncodeToString())).ToDate();

                if (LastRun.AddDays(TAppSettingsManager.GetInt16("DataChecks.RunEveryXDays", 1)) > DateTime.Now)
                {
                    // do not run the data check more than once a day or a week (depending on configuration setting), too many emails
                    TLogging.LogAtLevel(1, "TProcessDataChecks.Process: not running, since last run was at " + LastRun.ToString());
                    return;
                }
            }

            Errors_SinceDate = DateTime.Today.AddDays(-1 * SENDREPORTFORDAYS_TOUSERS);

            TLogging.LogAtLevel(1, "TProcessDataChecks.Process: Checking Modules");
            CheckModule(ADBAccessObj, "DataCheck.MPartner.");

            TSystemDefaults.SetSystemDefault(PROCESSDATACHECK_LAST_RUN, new TVariant(DateTime.Now).EncodeToString());
        }
Beispiel #2
0
        /// <summary>
        /// @param connection The System.Object that represents the established ODBC connection to the Petra Database
        /// </summary>
        /// <param name="connection">The System.Object that represents the established ODBC connection to the Petra Database
        /// </param>
        /// <param name="APathStandardReports"></param>
        /// <param name="APathCustomReports"></param>
        public TRptDataCalculator(TDataBase connection, String APathStandardReports, String APathCustomReports)
            : base(new TParameterList(), new TResultList(), new TReportStore(), null, connection, 0, -1, -1, -1)
        {
            if (!HasBeenInitialized)
            {
                InitializeUnit();
            }

            FPathStandardReports = APathStandardReports;
            FPathCustomReports = APathCustomReports;
        }
Beispiel #3
0
 /// <summary>
 /// constructor
 /// </summary>
 /// <param name="parameters"></param>
 /// <param name="results"></param>
 /// <param name="reportStore"></param>
 /// <param name="report"></param>
 /// <param name="dataDB"></param>
 /// <param name="depth"></param>
 /// <param name="column"></param>
 /// <param name="lineId"></param>
 /// <param name="parentRowId"></param>
 public TRptEvaluator(TParameterList parameters,
     TResultList results,
     TReportStore reportStore,
     TRptReport report,
     TDataBase dataDB,
     int depth,
     int column,
     int lineId,
     int parentRowId)
     : base(parameters, results, reportStore, report, dataDB, depth, column, lineId, parentRowId)
 {
 }
        /// <summary>
        /// Gets called in regular intervals from a Timer in Class TTimedProcessing.
        /// </summary>
        /// <param name="ADBAccessObj">Instantiated DB Access object with opened DB connection.</param>
        /// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
        public static void Process(TDataBase ADBAccessObj, bool ARunManually)
        {
            //At the moment this isn't ready for production use
            return;

#if TODO
            //TODO: create the gpg files from IntranetExport, with parameters for including finance or personnel

            string exportpath = TAppSettingsManager.GetValue("OpenPetra.PathExport");

            if (Directory.Exists(exportpath) == false)
            {
                TLogging.Log("Auto Export directory not found : " + exportpath);
                return;
            }

            //if there is a file there, send it
            bool foundfile = false;

            foreach (FileInfo fi in new DirectoryInfo(exportpath).GetFiles("*.gpg"))
            {
                foundfile = true;

                TLogging.Log("Emailing file: " + fi.FullName);
                //TODO: Get the name of the administrator from the system_defaults table
                bool result = new TSmtpSender().SendEmail("<*****@*****.**>",
                    "OpenPetra Server",
                    "<*****@*****.**>",
                    "Automatic Intranet Export",
                    "This is an automatic file upload from OpenPetra",
                    new string[] { fi.FullName });

                if (result)
                {
                    TLogging.Log("SMTP Server accepted file: " + fi.FullName);
                    fi.Delete();
                    //TODO: exception handler
                }
                else
                {
                    TLogging.Log("SMTP Server refused file: " + fi.FullName);
                }
            }

            if (!foundfile)
            {
                TLogging.Log("No file found to email");
            }
#endif
        }
Beispiel #5
0
        // establish connection to database
        public static bool InitDBConnection(
            string ADBFile, string ADBPassword)
        {
            db = new TDataBase();

            new TLogging("debug.log");
            TLogging.DebugLevel = TAppSettingsManager.GetInt16("DebugLevel", 0);

            db.EstablishDBConnection(TDBType.SQLite,
                ADBFile,
                "",
                "",
                "",
                ADBPassword,
                "");
            DBAccess.GDBAccessObj = db;

            return true;
        }
        private static void CheckModule(TDataBase ADBAccessObj, string AModule)
        {
            // get all sql files starting with module
            string[] sqlfiles = Directory.GetFiles(Path.GetFullPath(TAppSettingsManager.GetValue("SqlFiles.Path", ".")),
                AModule + "*.sql");

            DataTable errors = new DataTable(AModule + "Errors");

            foreach (string sqlfile in sqlfiles)
            {
                string sql = TDataBase.ReadSqlFile(Path.GetFileName(sqlfile));

                // extend the sql to load the s_date_created_d, s_created_by_c, s_date_modified_d, s_modified_by_c
                // only for the first table in the FROM clause
                string firstTableAlias = sql.Substring(sql.ToUpper().IndexOf("FROM ") + "FROM ".Length);
                firstTableAlias = firstTableAlias.Substring(0, firstTableAlias.ToUpper().IndexOf("WHERE"));
                int indexOfAs = firstTableAlias.ToUpper().IndexOf(" AS ");

                if (indexOfAs > -1)
                {
                    firstTableAlias = firstTableAlias.Substring(indexOfAs + " AS ".Length).Trim();

                    if (firstTableAlias.Contains(","))
                    {
                        firstTableAlias = firstTableAlias.Substring(0, firstTableAlias.IndexOf(",")).Trim();
                    }
                }

                sql = sql.Replace("FROM ", ", " + firstTableAlias + ".s_date_created_d AS DateCreated, " +
                    firstTableAlias + ".s_created_by_c AS CreatedBy, " +
                    firstTableAlias + ".s_date_modified_d AS DateModified, " +
                    firstTableAlias + ".s_modified_by_c AS ModifiedBy FROM ");

                errors.Merge(ADBAccessObj.SelectDT(sql, "temp", null));
            }

            if (errors.Rows.Count > 0)
            {
                SendEmailToAdmin(errors);
                SendEmailsPerUser(errors);
            }
        }
Beispiel #7
0
        public static void Main(string[] args)
        {
            try
            {
                // establish connection to database
                TCmdOpts settings = new TCmdOpts();

                if (!settings.IsFlagSet("Server.ODBC_DSN"))
                {
                    Console.WriteLine(
                        "sample call: " +
                        "ExportDataProgress.exe -Server.ODBC_DSN:Petra2_2sa -username:demo_sql -password:demo -sql:\"SELECT * from pub.a_account\" -output:test.xml");
                    Environment.Exit(-1);
                }

                new TLogging("debug.log");

                TDataBase db = new TDataBase();

                TDBType dbtype = TDBType.ProgressODBC;

                if (settings.IsFlagSet("Server.RDBMSType"))
                {
                    dbtype = CommonTypes.ParseDBType(settings.GetOptValue("Server.RDBMSType"));
                }

                if (dbtype != TDBType.ProgressODBC)
                {
                    throw new Exception("at the moment only Progress ODBC db is supported");
                }

                db.EstablishDBConnection(dbtype,
                    settings.GetOptValue("Server.ODBC_DSN"),
                    "",
                    "",
                    settings.GetOptValue("username"),
                    settings.GetOptValue("password"),
                    "");
                DBAccess.GDBAccessObj = db;

                TLogging.DebugLevel = 10;

                string sqlText = "";

                if (!settings.IsFlagSet("sql"))
                {
                    Console.WriteLine("Please enter sql and finish with semicolon: ");

                    while (!sqlText.Trim().EndsWith(";"))
                    {
                        sqlText += " " + Console.ReadLine();
                    }

                    sqlText = sqlText.Substring(0, sqlText.Length - 1);
                }
                else
                {
                    sqlText = settings.GetOptValue("sql");
                    Console.WriteLine(sqlText);
                }

                TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted);

                DataTable table = db.SelectDT(sqlText, "temp", transaction);

                XmlDocument doc = TDataBase.DataTableToXml(table);

                if (settings.IsFlagSet("output"))
                {
                    if (settings.GetOptValue("output").EndsWith("yml"))
                    {
                        TYml2Xml.Xml2Yml(doc, settings.GetOptValue("output"));
                    }
                    else if (settings.GetOptValue("output").EndsWith("csv"))
                    {
                        TCsv2Xml.Xml2Csv(doc, settings.GetOptValue("output"));
                    }
                    else if (settings.GetOptValue("output").EndsWith("xml"))
                    {
                        StreamWriter sw = new StreamWriter(settings.GetOptValue("output"));
                        sw.Write(TXMLParser.XmlToString2(doc));
                        sw.Close();
                    }
                }
                else
                {
                    TYml2Xml.Xml2Yml(doc, "temp.yml");
                    StreamReader sr = new StreamReader("temp.yml");
                    Console.WriteLine(sr.ReadToEnd());
                    sr.Close();
                }

                db.RollbackTransaction();
                db.CloseDBConnection();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine(e.StackTrace);
            }
        }
Beispiel #8
0
 /// <summary>
 /// Returns the current sequence value for the given Sequence from the DB.
 /// </summary>
 /// <param name="ASequenceName">Name of the Sequence.</param>
 /// <param name="ATransaction">An instantiated Transaction in which the Query
 /// to the DB will be enlisted.</param>
 /// <param name="ADatabase">Database object that can be used for querying.</param>
 /// <returns>Sequence Value.</returns>
 public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
 {
     return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT last_value FROM " + ASequenceName + "", ATransaction)));
 }
Beispiel #9
0
 /// <summary>
 /// Clears (flushes) all RDMBS Connection Pools and returns the new number of DB Connections after clearing all
 /// RDMBS Connection Pools.
 /// </summary>
 /// <returns>New number of DB Connections after clearing all RDMBS Connection Pools.</returns>
 public override int ClearConnectionPoolAndGetNumberOfDBConnections()
 {
     return(TDataBase.ClearConnectionPoolAndGetNumberOfDBConnections(TSrvSetting.RDMBSType));
 }
Beispiel #10
0
        /// <summary>
        /// Returns the current sequence value for the given Sequence from the DB.
        /// </summary>
        /// <param name="ASequenceName">Name of the Sequence.</param>
        /// <param name="ATransaction">An instantiated Transaction in which the Query
        /// to the DB will be enlisted.</param>
        /// <param name="ADatabase">Database object that can be used for querying.</param>
        /// <returns>Sequence Value.</returns>
        public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
        {
            string stmt = "SELECT MAX(sequence) FROM " + ASequenceName + ";";

            using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
            {
                return Convert.ToInt64(cmd.ExecuteScalar());
            }
        }
Beispiel #11
0
 /// the given period should not be changed with diffPeriod
 public TFinancialPeriod(TDataBase databaseConnection, int realPeriod, int year, TParameterList parameters, int column, bool real)
 {
     diffPeriod = 0;
     FCurrentFinancialYear = parameters.Get("param_current_financial_year_i", column).ToInt();
     FNumberAccountingPeriods = parameters.Get("param_number_of_accounting_periods_i", column).ToInt();
     FCurrentPeriod = parameters.Get("param_current_period_i", column).ToInt();
     FNumberForwardingPeriods = parameters.Get("param_number_of_accounting_periods_i", column).ToInt();
     Int32 glmSequenceNumber = parameters.Get("glm_sequence_i", column).ToInt();
     realGlmSequence = LedgerStatus.GlmSequencesCache.GetGlmSequence(glmSequenceNumber);
     MainConstructor(databaseConnection,
         realPeriod,
         year,
         diffPeriod,
         FCurrentFinancialYear,
         FCurrentPeriod,
         FNumberAccountingPeriods,
         FNumberForwardingPeriods,
         realGlmSequence);
 }
Beispiel #12
0
        private int GetGlmSequenceFromDB(TDataBase databaseConnection,
            int pv_ledger_number_i,
            String pv_cost_centre_code_c,
            String pv_account_code_c,
            int pv_year_i)
        {
            int ReturnValue = -1;
            String strSql = "SELECT a_glm_sequence_i FROM PUB_a_general_ledger_master WHERE a_ledger_number_i = " +
                            pv_ledger_number_i + " AND a_cost_centre_code_c = '" + pv_cost_centre_code_c +
                            "' AND a_account_code_c = '" +
                            pv_account_code_c + "' AND a_year_i = " + pv_year_i;
            DataTable tab = databaseConnection.SelectDT(strSql, "GetGlmSequenceFromDB_TempTable", databaseConnection.Transaction);

            if (tab.Rows.Count == 1)
            {
                ReturnValue = Convert.ToInt32(tab.Rows[0]["a_glm_sequence_i"]);
            }

            return ReturnValue;
        }
        private decimal GetCorporateExchangeRateFromDB(TDataBase databaseConnection,
            int pv_ledger_number_i,
            int pv_year_i,
            int pv_period_i,
            int currentFinancialYear)
        {
            ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(pv_ledger_number_i, databaseConnection.Transaction);
            AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(pv_ledger_number_i,
                pv_period_i,
                databaseConnection.Transaction);

            if (AccountingPeriodTable.Rows.Count < 1)
            {
                return -1; // This is poor (because the caller can blindly use it!)
                           // I wonder whether an exception would be better. (Tim Ingham, Oct 2013)
            }

            if (currentFinancialYear < 0)
            {
                currentFinancialYear = ledgerTable[0].CurrentFinancialYear;
            }

            DateTime startOfPeriod = AccountingPeriodTable[0].PeriodStartDate;
            DateTime endOfPeriod = AccountingPeriodTable[0].PeriodEndDate;

            startOfPeriod = new DateTime(startOfPeriod.Year - (currentFinancialYear - pv_year_i), startOfPeriod.Month, startOfPeriod.Day);

            if ((endOfPeriod.Month == 2) && (endOfPeriod.Day == 29)
                && (((currentFinancialYear - pv_year_i)) % 4 != 0))
            {
                endOfPeriod = endOfPeriod.AddDays(-1);
            }

            endOfPeriod = new DateTime(endOfPeriod.Year - (currentFinancialYear - pv_year_i), endOfPeriod.Month, endOfPeriod.Day);

            // get the corporate exchange rate between base and intl currency for the period
            return TExchangeRateTools.GetCorporateExchangeRate(ledgerTable[0].IntlCurrency,
                ledgerTable[0].BaseCurrency,
                startOfPeriod,
                endOfPeriod);
        }
Beispiel #14
0
        private TVerificationResultCollection PostAPDocument(AccountsPayableTDS AMainDS, DateTime APostingDate,
                                                             ref List <int> ADocumentIds, bool AReversal = false, TDataBase ADataBase = null)
        {
            string AssertFailMessage = AReversal ? "Failed to post AP document reversal: " : "Problems posting AP document: ";
            TVerificationResultCollection VerificationResult;

            if (!AReversal)
            {
                ADocumentIds.Add(AMainDS.AApDocument[0].ApDocumentId);
            }

            Int32 glBatchNumber;

            if (!TAPTransactionWebConnector.PostAPDocuments(FLedgerNumber,
                                                            ADocumentIds,
                                                            APostingDate,
                                                            AReversal,
                                                            out glBatchNumber,
                                                            out VerificationResult,
                                                            ADataBase))
            {
                Assert.Fail(AssertFailMessage +
                            VerificationResult.BuildVerificationResultString());
            }

            CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult, AssertFailMessage);   // Guard Assert

            return(VerificationResult);
        }
Beispiel #15
0
        private AAPInfos PostForeignSupplierAPDocument(decimal AAmount, decimal AExchangeRatePosting, string ADocumentCode, string ANarrative,
                                                       out decimal AAPAccountBalanceBefore, out decimal ABankAccountBefore, out decimal AExpAccountBefore,
                                                       out decimal ARevalAccountBefore, out List <int> ADocumentIds, TDataBase ADataBase)
        {
            TVerificationResultCollection VerificationResult;

            ADocumentIds = new List <int>();

            AAPInfos APInfos = APTestsArrangement(SUPPLIER_FOREIGN_PARTNER_KEY, AAmount, AExchangeRatePosting, ADocumentCode, ANarrative, ADataBase);

            // Save the current amount on the AP account
            AAPAccountBalanceBefore = new TGet_GLM_Info(FLedgerNumber,
                                                        APInfos.ApAccountCode, APInfos.CostCentreCode, ADataBase).YtdActual;
            ABankAccountBefore = new TGet_GLM_Info(FLedgerNumber,
                                                   APInfos.BankAccount, APInfos.CostCentreCode, ADataBase).YtdForeign;

            AExpAccountBefore = new TGet_GLM_Info(FLedgerNumber,
                                                  APInfos.ApDS.AApSupplier[0].DefaultExpAccount,
                                                  APInfos.CostCentreCode, ADataBase).YtdActual;
            ARevalAccountBefore = new TGet_GLM_Info(FLedgerNumber,
                                                    APInfos.ForexGainsLossesAccount,
                                                    APInfos.CostCentreCode, ADataBase).YtdActual;

            VerificationResult = PostAPDocument(APInfos.ApDS, APInfos.PeriodStartDate, ref ADocumentIds, false, ADataBase);
            CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult);   // Guard Assert

            //
            // Guard Assert: Posting OK?
            //
            decimal ExpAccountAfter = new TGet_GLM_Info(FLedgerNumber,
                                                        APInfos.ApDS.AApSupplier[0].DefaultExpAccount,
                                                        APInfos.ApDS.AApSupplier[0].DefaultCostCentre, ADataBase).YtdActual;

            Assert.AreEqual(Math.Round(AAmount / AExchangeRatePosting, 2), Math.Round(ExpAccountAfter - AExpAccountBefore,
                                                                                      2), "after posting the invoice, the expense account should be debited the amount in base currency (Exchange Rate is " +
                            AExchangeRatePosting + ")");

            return(APInfos);
        }
Beispiel #16
0
        /// <summary>
        /// get all partners and their commitment details
        /// </summary>
        public static DataTable GetLengthOfCommitment(TParameterList AParameters, TResultList AResults)
        {
            SortedList <string, string> Defines          = new SortedList <string, string>();
            List <OdbcParameter>        SqlParameterList = new List <OdbcParameter>();

            try
            {
                SqlParameterList.Add(new OdbcParameter("staffdate", OdbcType.Date)
                {
                    Value = AParameters.Get("param_dtpCurrentStaff").ToDate()
                });
                SqlParameterList.Add(new OdbcParameter("staffdate2", OdbcType.Date)
                {
                    Value = AParameters.Get("param_dtpCurrentStaff").ToDate()
                });
            }
            catch (Exception e)
            {
                TLogging.Log("problem while preparing sql statement for length of commitment report: " + e.ToString());
                return(null);
            }

            string         SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.AllCommitments.sql", Defines);
            Boolean        NewTransaction;
            TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);

            try
            {
                // now run the database query
                TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);

                LengthOfCommitmentReportTDSPmStaffDataTable CommitmentTable = new LengthOfCommitmentReportTDSPmStaffDataTable();
                DBAccess.GDBAccessObj.SelectDT(CommitmentTable, SqlStmt, Transaction,
                                               SqlParameterList.ToArray(), 0, 0);

                // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
                if (AParameters.Get("CancelReportCalculation").ToBool() == true)
                {
                    return(null);
                }

                List <Int32> SpecialAnniversaries = new List <int>();

                if (AParameters.Get("param_chkAnniversaries").ToBool() == true)
                {
                    string[] Anniversaries = AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',', ';' });

                    foreach (string s in Anniversaries)
                    {
                        SpecialAnniversaries.Add(Convert.ToInt32(s.Trim()));
                    }
                }

                return(CalculateLengthOfCommitment(
                           CommitmentTable,
                           AParameters.Get("param_dtpFromDate").ToDate(),
                           AParameters.Get("param_dtpToDate").ToDate(),
                           SpecialAnniversaries));
            }
            catch (Exception e)
            {
                TLogging.Log(e.ToString());
                return(null);
            }
            finally
            {
                if (NewTransaction)
                {
                    DBAccess.GDBAccessObj.RollbackTransaction();
                }
            }
        }
Beispiel #17
0
        /// <summary>
        /// Creates a AP document for the supplier specified with APartnerKey.
        /// </summary>
        /// <param name="APartnerKey"></param>
        /// <param name="AAmount"></param>
        /// <param name="AExchangeRatePosting"></param>
        /// <param name="ADocumentCode"></param>
        /// <param name="ANarrative"></param>
        /// <param name="AMainDS"></param>
        /// <param name="ADataBase"></param>
        /// <returns></returns>
        private TVerificationResultCollection CreateAPDocument(Int64 APartnerKey, decimal AAmount, decimal?AExchangeRatePosting,
                                                               string ADocumentCode, string ANarrative, out AccountsPayableTDS AMainDS, TDataBase ADataBase)
        {
            string AssertFailMessage = "Problems saving AP document: ";
            TSubmitChangesResult          SubmRes;
            TVerificationResultCollection VerificationResult;

            TDataBase db = DBAccess.Connect("CreateAPDocument", ADataBase);

            AMainDS = TAPTransactionWebConnector.CreateAApDocument(FLedgerNumber, APartnerKey, false, db);
            AccountsPayableTDS MainDS = AMainDS;

            TDBTransaction Transaction = new TDBTransaction();

            db.ReadTransaction(
                ref Transaction,
                delegate
            {
                AApSupplierAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
            });

            AMainDS.AApDocument[0].DocumentCode = ADocumentCode + (DocumentCounter++).ToString();

            AMainDS.Merge(TAPTransactionWebConnector.CreateAApDocumentDetail(
                              FLedgerNumber,
                              AMainDS.AApDocument[0].ApDocumentId,
                              AMainDS.AApSupplier[0].DefaultExpAccount,
                              AMainDS.AApSupplier[0].DefaultCostCentre,
                              AAmount,
                              AMainDS.AApDocument[0].LastDetailNumber + 1));

            AMainDS.AApDocument[0].LastDetailNumber++;
            AMainDS.AApDocument[0].TotalAmount     = AAmount;
            AMainDS.AApDocument[0].DocumentStatus  = MFinanceConstants.AP_DOCUMENT_APPROVED;
            AMainDS.AApDocumentDetail[0].Narrative = ANarrative;

            if (AExchangeRatePosting.HasValue)
            {
                AMainDS.AApDocument[0].ExchangeRateToBase = AExchangeRatePosting.Value;
            }

            SubmRes = TAPTransactionWebConnector.SaveAApDocument(ref AMainDS, out VerificationResult, db);

            if (SubmRes != TSubmitChangesResult.scrOK)
            {
                Assert.Fail(AssertFailMessage + String.Format(" - (SaveAApDocument return value: {0}) - ", SubmRes) +
                            VerificationResult.BuildVerificationResultString());
            }

            CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult, AssertFailMessage);   // Guard Assert

            return(VerificationResult);
        }
Beispiel #18
0
        /// <summary>
        /// reserve a number of partner keys, to be used by the calling function.
        /// useful to create many partner at once, eg. for the demodata
        /// </summary>
        /// <param name="AFieldPartnerKey"></param>
        /// <param name="ANumberOfKeys"></param>
        /// <param name="ADataBase"></param>
        /// <returns>the first valid partner key to use</returns>
        public static System.Int64 ReservePartnerKeys(System.Int64 AFieldPartnerKey, ref Int32 ANumberOfKeys, TDataBase ADataBase = null)
        {
            Int64 NextPartnerKey = -1;
            Int32 NumberOfKeys   = ANumberOfKeys;

            if (AFieldPartnerKey == -1)
            {
                AFieldPartnerKey = DomainManager.GSiteKey;
            }

            TDBTransaction ReadWriteTransaction = new TDBTransaction();
            TDataBase      db           = DBAccess.Connect("ReservePartnerKeys", ADataBase);
            bool           SubmissionOK = true;

            db.WriteTransaction(ref ReadWriteTransaction,
                                ref SubmissionOK,
                                delegate
            {
                PPartnerLedgerTable PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadWriteTransaction);

                NextPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1;

                Int64 NextUsedKey =
                    Convert.ToInt64(db.ExecuteScalar("SELECT MIN(p_partner_key_n) FROM PUB_p_partner WHERE p_partner_key_n >= " +
                                                     NextPartnerKey.ToString(), ReadWriteTransaction));

                if (NextUsedKey < NextPartnerKey + NumberOfKeys)
                {
                    NumberOfKeys = Convert.ToInt32(NextUsedKey - NextPartnerKey);
                }

                PartnerLedgerDT[0].LastPartnerId = Convert.ToInt32((NextPartnerKey + NumberOfKeys - 1) - PartnerLedgerDT[0].PartnerKey);

                PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, ReadWriteTransaction);

                SubmissionOK = true;
            });

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

            if (!SubmissionOK)
            {
                throw new Exception("ReservePartnerKeys failed");
            }

            ANumberOfKeys = NumberOfKeys;

            return(NextPartnerKey);
        }
Beispiel #19
0
        /// <summary>
        /// this checks if the new key is still available,
        /// and makes sure it will not be used as a default key anymore
        /// </summary>
        /// <param name="AFieldPartnerKey"></param>
        /// <param name="AOriginalDefaultKey">this has been previously retrieved from GetNewPartnerKey</param>
        /// <param name="ANewPartnerKey">the user proposes this key for a new partner; the function can change it and return a valid value, or -1</param>
        /// <param name="ADataBase"></param>
        /// <returns>whether or not ANewPartnerKey has a valid new partner key;
        /// if it cannot be assigned, the function returns false, and ANewPartnerKey is -1
        /// </returns>
        public static bool SubmitNewPartnerKey(System.Int64 AFieldPartnerKey, System.Int64 AOriginalDefaultKey, ref System.Int64 ANewPartnerKey, TDataBase ADataBase = null)
        {
            bool                ReturnValue = true;
            TDBTransaction      ReadTransaction;
            TDBTransaction      WriteTransaction;
            Boolean             NewTransaction;
            PPartnerLedgerTable PartnerLedgerDT;

            TDataBase db = DBAccess.Connect("SubmitNewPartnerKey", ADataBase);

            System.Int64 CurrentDefaultPartnerKey;

            if (ANewPartnerKey == AOriginalDefaultKey)
            {
                // The user has selected the default
                ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
                                                                 out NewTransaction);

                try
                {
                    // Fetch the partner ledger record to update the last key

                    PartnerLedgerDT          = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadTransaction);
                    CurrentDefaultPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1;

                    if (ANewPartnerKey != CurrentDefaultPartnerKey)
                    {
                        // Someone else has updated this since, so we will use the new default
                        ANewPartnerKey = CurrentDefaultPartnerKey;
                    }

                    // Now check that this does not exist, and increment until we
                    // find one which does not
                    while (PPartnerAccess.Exists(ANewPartnerKey, ReadTransaction))
                    {
                        ANewPartnerKey = ANewPartnerKey + 1;
                    }
                }
                finally
                {
                    if (NewTransaction)
                    {
                        ReadTransaction.Rollback();

                        if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_TRACE)
                        {
                            Console.WriteLine("TNewPartnerKey.SubmitNewPartnerKey: rolled back own transaction.");
                        }
                    }
                }

                PartnerLedgerDT[0].LastPartnerId = (int)(ANewPartnerKey - PartnerLedgerDT[0].PartnerKey);

                WriteTransaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable,
                                                                  out NewTransaction);

                try
                {
                    PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, WriteTransaction);

                    if (NewTransaction)
                    {
                        WriteTransaction.Commit();
                    }
                }
                catch (Exception Exc)
                {
                    TLogging.Log("An Exception occured during the submission of a new PartnerKey:" + Environment.NewLine + Exc.ToString());

                    if (NewTransaction)
                    {
                        WriteTransaction.Rollback();
                    }

                    throw;
                }
            }
            // end of: The user has selected the default
            else
            {
                ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
                                                                 out NewTransaction);

                try
                {
                    // check if the Partner Key is already being used
                    if (PPartnerAccess.Exists(ANewPartnerKey, ReadTransaction))
                    {
                        ANewPartnerKey = -1;
                        ReturnValue    = false;
                    }
                }
                finally
                {
                    if (NewTransaction)
                    {
                        ReadTransaction.Rollback();

                        if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_TRACE)
                        {
                            Console.WriteLine("TNewPartnerKey.SubmitNewPartnerKey: rolled back own transaction.");
                        }
                    }
                }
            }

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

            return(ReturnValue);
        }
Beispiel #20
0
        public static Boolean GetMotivationGroupAndDetailForPartner(Int64 APartnerKey,
                                                                    ref String AMotivationGroup,
                                                                    ref String AMotivationDetail)
        {
            Boolean PartnerKeyIsValid = false;

            if (APartnerKey != 0)
            {
                string motivationGroup  = MFinanceConstants.MOTIVATION_GROUP_GIFT;
                string motivationDetail = AMotivationDetail;

                TDBTransaction readTransaction = new TDBTransaction();
                TDataBase      db = DBAccess.Connect("GetMotivationGroupAndDetailForPartner");

                db.ReadTransaction(
                    ref readTransaction,
                    delegate
                {
                    PPartnerTable myPPartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, readTransaction);

                    if (myPPartnerTable.Rows.Count == 1)
                    {
                        // partnerKey is valid
                        PartnerKeyIsValid      = true;
                        PPartnerRow partnerRow = myPPartnerTable[0];

                        // Change motivationDetail if PartnerClass is UNIT
                        if (partnerRow.PartnerClass.Equals(MPartnerConstants.PARTNERCLASS_UNIT))
                        {
                            // AND KEY-MIN
                            bool KeyMinFound = false;

                            // first check if a specific motivation detail is linked to this partner
                            AMotivationDetailTable MotivationDetailTable
                                = AMotivationDetailAccess.LoadViaPPartner(APartnerKey, readTransaction);

                            if ((MotivationDetailTable != null) && (MotivationDetailTable.Rows.Count > 0))
                            {
                                foreach (AMotivationDetailRow Row in MotivationDetailTable.Rows)
                                {
                                    if (Row.MotivationStatus)
                                    {
                                        motivationGroup  = MotivationDetailTable[0].MotivationGroupCode;
                                        motivationDetail = MotivationDetailTable[0].MotivationDetailCode;

                                        KeyMinFound = true;
                                        break;     // Go with the first entry found.
                                    }
                                }
                            }

                            if (!KeyMinFound)
                            {
                                // Is this is a key min, or a field?
                                PUnitTable pUnitTable = PUnitAccess.LoadByPrimaryKey(APartnerKey, readTransaction);

                                if (pUnitTable.Rows.Count == 1)
                                {
                                    PUnitRow unitRow = pUnitTable[0];

                                    switch (unitRow.UnitTypeCode)
                                    {
                                    case MPartnerConstants.UNIT_TYPE_AREA:
                                    case MPartnerConstants.UNIT_TYPE_FUND:
                                    case MPartnerConstants.UNIT_TYPE_FIELD:
                                        motivationDetail = MFinanceConstants.GROUP_DETAIL_FIELD;
                                        break;

                                    case MPartnerConstants.UNIT_TYPE_KEYMIN:
                                        motivationDetail = MFinanceConstants.GROUP_DETAIL_KEY_MIN;
                                        break;

                                    case MPartnerConstants.UNIT_TYPE_COUNTRY:
                                    case MPartnerConstants.UNIT_TYPE_CONFERENCE:
                                    case MPartnerConstants.UNIT_TYPE_OTHER:
                                    case MPartnerConstants.UNIT_TYPE_ROOT:
                                    case MPartnerConstants.UNIT_TYPE_TEAM:
                                    case MPartnerConstants.UNIT_TYPE_WORKING_GROUP:
                                    default:
                                        motivationDetail = MFinanceConstants.GROUP_DETAIL_SUPPORT;
                                        break;
                                    }
                                }
                            }
                        }
                        else
                        {
                            motivationDetail = MFinanceConstants.GROUP_DETAIL_SUPPORT;
                        }
                    }
                });

                db.CloseDBConnection();

                AMotivationGroup  = motivationGroup;
                AMotivationDetail = motivationDetail;
            }

            return(PartnerKeyIsValid);
        }
Beispiel #21
0
 /// <summary>
 /// Returns the current sequence value for the given Sequence from the DB.
 /// </summary>
 /// <param name="ASequenceName">Name of the Sequence.</param>
 /// <param name="ATransaction">An instantiated Transaction in which the Query
 /// to the DB will be enlisted.</param>
 /// <param name="ADatabase">Database object that can be used for querying.</param>
 /// <returns>Sequence Value.</returns>
 public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
 {
     return Convert.ToInt64(ADatabase.ExecuteScalar("SELECT last_value FROM " + ASequenceName + "", ATransaction));
 }
Beispiel #22
0
        private AAPInfos PostAndPayForeignSupplierAPDocument(string ADocumentCode, out int APaymentNumber, out List <int> ADocumentIDs,
                                                             out decimal AAPAccountBalanceBefore, out decimal ABankAccountBefore, out decimal ARevalAccountBefore, TDataBase ADataBase)
        {
            decimal Amount = 100.0m;
            decimal ExchangeRatePosting = 1.2m;
            decimal ExchangeRatePayment = 1.1m;
            decimal ExpAccountBefore;
            TVerificationResultCollection VerificationResult;
            AAPInfos APInfos;

            APInfos = PostForeignSupplierAPDocument(Amount, ExchangeRatePosting, ADocumentCode, "Detail Item",
                                                    out AAPAccountBalanceBefore, out ABankAccountBefore, out ExpAccountBefore, out ARevalAccountBefore, out ADocumentIDs, ADataBase);

            //
            // Pay the AP document
            //
            VerificationResult = PayAPDocument(APInfos.ApDS.AApDocument[0].ApDocumentId, Amount,
                                               APInfos.BankAccount, APInfos.CurrencyCode, APInfos.PeriodEndDate, out APaymentNumber, ExchangeRatePayment, ADataBase);
            CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult);   // Guard Assert

            // Save the current amount on the AP account and Bank Account
            decimal APAccountBalanceAfter = new TGet_GLM_Info(FLedgerNumber,
                                                              APInfos.ApAccountCode, APInfos.CostCentreCode, ADataBase).YtdActual;
            decimal BankAccountAfter = new TGet_GLM_Info(FLedgerNumber,
                                                         APInfos.BankAccount, APInfos.CostCentreCode, ADataBase).YtdForeign;

            //
            // Guard Assert: Paying OK?
            //
            // Check the amount on the AP account
            Assert.AreEqual(0.0m, APAccountBalanceAfter - AAPAccountBalanceBefore, "after paying the invoice, the AP account should be cleared");
            Assert.AreEqual((-1.0m) * Amount, BankAccountAfter - ABankAccountBefore, "after paying the invoice, the bank account should be credited");

            decimal RevalAccountAfter = new TGet_GLM_Info(FLedgerNumber,
                                                          APInfos.ForexGainsLossesAccount, APInfos.CostCentreCode, ADataBase).YtdActual;

            Assert.AreEqual(
                Math.Round((Amount / ExchangeRatePayment) - (Amount / ExchangeRatePosting), 2),
                Math.Round((RevalAccountAfter - ARevalAccountBefore), 2),
                "after paying the invoice, the revaluation account should be credited with the forex gain");

            return(APInfos);
        }
Beispiel #23
0
        /// <summary>
        /// Get the sequence System.Object of a a_general_ledger_master
        /// </summary>
        /// <returns>the glm sequence System.Object of the a_general_ledger_master row of the given year; nil if there is no available a_general_ledger_master row
        /// </returns>
        public TGlmSequence GetGlmSequenceCurrentYear(TDataBase databaseConnection,
            int pv_ledger_number_i,
            String pv_cost_centre_code_c,
            String pv_account_code_c,
            int pv_current_financial_year_i)
        {
            TGlmSequence ReturnValue;
            int sequenceNumber;
            String accountType;
            bool postingAccount;
            bool debitCreditIndicator;

            // first check the local cache
            ReturnValue = null;

            foreach (TGlmSequence glmSequenceElement in glmSequences)
            {
                if ((glmSequenceElement.ledger_number == pv_ledger_number_i) && (glmSequenceElement.account_code == pv_account_code_c)
                    && (glmSequenceElement.cost_centre_code == pv_cost_centre_code_c) && (glmSequenceElement.year == pv_current_financial_year_i))
                {
                    ReturnValue = glmSequenceElement;
                    break;
                }
            }

            if (ReturnValue == null)
            {
                sequenceNumber = GetGlmSequenceFromDB(databaseConnection,
                    pv_ledger_number_i,
                    pv_cost_centre_code_c,
                    pv_account_code_c,
                    pv_current_financial_year_i);

                if (sequenceNumber == -1)
                {
                    // for the summary accounts from alternative account hierarchies,
                    // which don't have glm records.
                    sequenceNumber = NextNegativeSequence;
                    NextNegativeSequence = NextNegativeSequence - 1;
                }

                if (TAccountInfo(databaseConnection, pv_ledger_number_i, pv_account_code_c, out accountType, out postingAccount,
                        out debitCreditIndicator))
                {
                    TGlmSequence glmSequenceElement = new TGlmSequence(pv_ledger_number_i,
                        pv_account_code_c,
                        pv_cost_centre_code_c,
                        StringHelper.IsSame(accountType, "income") || StringHelper.IsSame(accountType, "expense"),
                        postingAccount,
                        debitCreditIndicator,
                        sequenceNumber,
                        pv_current_financial_year_i);
                    glmSequences.Add(glmSequenceElement);
                    ReturnValue = glmSequenceElement;
                }
                else
                {
                    // account could not be found
                    ReturnValue = null;
                }
            }

            return ReturnValue;
        }
Beispiel #24
0
        private TVerificationResultCollection PayAPDocument(int AApDocumentId, decimal AAmount, string ABankAccount,
                                                            string ACurrencyCode, DateTime APeriodEndDate, out int APaymentNumber, decimal?AExchangeRatePayment = null, TDataBase ADataBase = null)
        {
            string AssertFailMessage = "Problems paying AP document: ";
            TVerificationResultCollection VerificationResult;
            AccountsPayableTDS            MainDS = new AccountsPayableTDS();

            AApPaymentRow Payment = MainDS.AApPayment.NewRowTyped();

            Payment.LedgerNumber  = FLedgerNumber;
            Payment.PaymentNumber = -1;
            Payment.Amount        = AAmount;
            Payment.BankAccount   = ABankAccount;
            Payment.CurrencyCode  = ACurrencyCode;

            if (AExchangeRatePayment.HasValue)
            {
                Payment.ExchangeRateToBase = AExchangeRatePayment.Value;
            }

            MainDS.AApPayment.Rows.Add(Payment);

            AApDocumentPaymentRow DocPayment = MainDS.AApDocumentPayment.NewRowTyped();

            DocPayment.LedgerNumber  = FLedgerNumber;
            DocPayment.ApDocumentId  = AApDocumentId;
            DocPayment.Amount        = AAmount;
            DocPayment.PaymentNumber = Payment.PaymentNumber;
            MainDS.AApDocumentPayment.Rows.Add(DocPayment);
            Int32 glBatchNumber;
            AccountsPayableTDSAApPaymentTable newPayments;

            if (!TAPTransactionWebConnector.PostAPPayments(ref MainDS, APeriodEndDate,
                                                           out glBatchNumber,
                                                           out newPayments,
                                                           out VerificationResult,
                                                           ADataBase))
            {
                Assert.Fail(AssertFailMessage +
                            VerificationResult.BuildVerificationResultString());
            }

            CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult, AssertFailMessage);   // Guard Assert

            APaymentNumber = DocPayment.PaymentNumber;

            return(VerificationResult);
        }
Beispiel #25
0
        /// <summary>
        /// generate the partners from a text file that was generated with Benerator
        /// </summary>
        /// <param name="AInputBeneratorFile"></param>
        public static void GenerateWorkers(string AInputBeneratorFile)
        {
            PartnerEditTDS MainDS      = new PartnerEditTDS();
            PersonnelTDS   PersonnelDS = new PersonnelTDS();

            TDataBase      db          = DBAccess.Connect("GenerateWorkers");
            TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);

            // get a list of fields (all class UNIT, with unit type F)
            string    sqlGetFieldPartnerKeys = "SELECT p_partner_key_n, p_unit_name_c FROM PUB_p_unit WHERE u_unit_type_code_c = 'F'";
            DataTable FieldKeys = db.SelectDT(sqlGetFieldPartnerKeys, "keys", Transaction);

            // get a list of banks (all class BANK)
            string    sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank";
            DataTable BankKeys = db.SelectDT(sqlGetBankPartnerKeys, "keys", Transaction);

            Transaction.Rollback();

            // AlanP: May 2016 - We may no longer need the UTF8 because the method now automatically discovers the encoding even with no BOM
            XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",", Encoding.UTF8);

            XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;

            while (RecordNode != null)
            {
                string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation");

                PFamilyRow familyRecord = null;

                if (familySituation == "singleMan")
                {
                    familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS);
                    GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
                }
                else if (familySituation == "singleWoman")
                {
                    familyRecord = GenerateFamilyRecord(RecordNode, "Female", MainDS);
                    GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
                }
                else if (familySituation == "family")
                {
                    familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS);
                    GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
                    GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);

                    int      AgeDifferenceSpouse = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "AgeDifferenceSpouse"));
                    DataView FamilyView          = new DataView(MainDS.PPerson);
                    FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + familyRecord.PartnerKey.ToString();
                    FamilyView.Sort      = PPersonTable.GetFamilyIdDBName();

                    PPersonRow HusbandPersonRow = (PPersonRow)FamilyView[0].Row;
                    PPersonRow WifePersonRow    = (PPersonRow)FamilyView[1].Row;
                    WifePersonRow.DateOfBirth =
                        WifePersonRow.DateOfBirth.Value.AddYears(
                            AgeDifferenceSpouse - (WifePersonRow.DateOfBirth.Value.Year - HusbandPersonRow.DateOfBirth.Value.Year));

                    if (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year < 19)
                    {
                        WifePersonRow.DateOfBirth.Value.AddYears(
                            19 - (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year));
                    }

                    int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren"));

                    for (int countChild = 0; countChild < NumberOfChildren; countChild++)
                    {
                        DateTime DateOfBirthChild = Convert.ToDateTime(
                            TXMLParser.GetAttribute(RecordNode,
                                                    "Child" + (countChild + 1).ToString() + "DateOfBirth"));

                        // mother must have been 19 when the child was born
                        if (DateOfBirthChild.Year < WifePersonRow.DateOfBirth.Value.Year + 19)
                        {
                            continue;
                        }

                        GeneratePersonRecord(RecordNode, familyRecord, "Child" + (countChild + 1).ToString(), MainDS);
                    }
                }

                GenerateAddressForFamily(RecordNode, familyRecord, MainDS);

                GenerateCommitmentRecord(RecordNode, familyRecord, MainDS, PersonnelDS, FieldKeys);

                GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys);

                if (MainDS.PFamily.Rows.Count % 100 == 0)
                {
                    TLogging.Log("created worker " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName);
                }

                RecordNode = RecordNode.NextSibling;
            }

            MainDS.ThrowAwayAfterSubmitChanges = true;

            PartnerEditTDSAccess.SubmitChanges(MainDS);

            PersonnelDS.ThrowAwayAfterSubmitChanges = true;
            PersonnelTDSAccess.SubmitChanges(PersonnelDS);

            TLogging.Log("after saving workers");
        }
Beispiel #26
0
        private TVerificationResultCollection ReversePayment(int APaymentNumber, DateTime APeriodEndDate,
                                                             List <int> ADocumentIds, AccountsPayableTDS AApDS, TDataBase ADataBase)
        {
            string AssertFailMessage = "Failed to reverse AP payment: ";
            TVerificationResultCollection VerificationResult;

            List <Int32> glBatchNumbers;

            // "Un-pay" the specified invoice
            if (!TAPTransactionWebConnector.ReversePayment(FLedgerNumber,
                                                           APaymentNumber,
                                                           APeriodEndDate,
                                                           out glBatchNumbers,
                                                           out VerificationResult, ADataBase))
            {
                Assert.Fail(AssertFailMessage +
                            VerificationResult.BuildVerificationResultString());
            }

            CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult, AssertFailMessage);   // Guard Assert

            // "Un-post" the specified invoice - returning it to "Approved" status!
            ADocumentIds[0] += 2; // The invoice I posted was reversed, and a duplicate now exists with an Id 2 greater than the original.

            return(PostAPDocument(AApDS, APeriodEndDate, ref ADocumentIds, true, ADataBase));
        }
Beispiel #27
0
        /// <summary>
        /// Get the sequence number of another year, using a known sequence number
        /// </summary>
        /// <param name="databaseConnection"></param>
        /// <param name="glmSequence">The known sequence number of a general_ledger_master year</param>
        /// <param name="year">The year of the required sequence</param>
        /// <returns>the glm sequence System.Object of the a_general_ledger_master row of the another year; null if there is no available a_general_ledger_master row
        /// </returns>
        ///
        public TGlmSequence GetOtherYearGlmSequence(TDataBase databaseConnection, TGlmSequence glmSequence, int year)
        {
            TGlmSequence ReturnValue;
            int sequenceNumber;

            if (glmSequence == null)
            {
                return null;
            }

            // first check the local cache
            ReturnValue = null;

            foreach (TGlmSequence glmSequenceElement in glmSequences)
            {
                if ((glmSequenceElement.currentFinancialYearSequence == glmSequence.currentFinancialYearSequence) && (glmSequenceElement.year == year))
                {
                    ReturnValue = glmSequenceElement;
                    break;
                }
            }

            if ((ReturnValue == null) && (glmSequence != null))
            {
                sequenceNumber = GetGlmSequenceFromDB(databaseConnection,
                    glmSequence.ledger_number,
                    glmSequence.cost_centre_code,
                    glmSequence.account_code,
                    year);

                if (sequenceNumber == -1)
                {
                    // for the summary accounts from alternative account hierarchies,
                    // which don't have glm records.
                    sequenceNumber = NextNegativeSequence;
                    NextNegativeSequence--;
                }

                TGlmSequence glmSequenceElement = new TGlmSequence(glmSequence, sequenceNumber, year);
                glmSequences.Add(glmSequenceElement);
                ReturnValue = glmSequenceElement;
            }

            return ReturnValue;
        }
Beispiel #28
0
        public static void ExportGifts(int ALedgerNumber,
                                       string ACostCentre,
                                       string AAcctCode,
                                       string AMonthName,
                                       int APeriodNumber,
                                       DateTime APeriodStartDate,
                                       DateTime APeriodEndDate,
                                       string ACurrencySelect,
                                       int AIchNumber,
                                       DataTable AExportDataTable,
                                       TVerificationResultCollection AVerificationResult,
                                       TDataBase ADataBase)
        {
            /* Define local variables */
            bool    FirstLoopFlag         = true;
            Int32   LastRecipKey          = 0;
            string  LastGroup             = string.Empty;
            string  LastDetail            = string.Empty;
            string  LastDetailDesc        = string.Empty;
            decimal IndividualDebitTotal  = 0;
            decimal IndividualCreditTotal = 0;

            string ExportDescription = string.Empty;
            Int32  tmpLastRecipKey   = 0;
            string tmpLastGroup      = string.Empty;
            string tmpLastDetail     = string.Empty;

            //Find and total each gift transaction

            TDBTransaction DBTransaction = new TDBTransaction();
            TDataBase      db            = DBAccess.Connect("ExportGifts", ADataBase);

            db.ReadTransaction(ref DBTransaction,
                               delegate
            {
                String IchNumberFilter = "";

                if (AIchNumber > 0)
                {
                    IchNumberFilter = " AND GiftDetail.a_ich_number_i = " + AIchNumber;
                }

                String Query = "SELECT " +
                               " GiftDetail.a_ledger_number_i, " +
                               " GiftDetail.a_batch_number_i, " +
                               " GiftDetail.a_gift_transaction_number_i, " +
                               " GiftDetail.a_detail_number_i, " +
                               " GiftDetail.a_gift_amount_n, " +
                               " GiftDetail.a_gift_amount_intl_n, " +
                               " GiftDetail.a_motivation_group_code_c, " +
                               " GiftDetail.a_motivation_detail_code_c, " +
                               " GiftDetail.p_recipient_key_n, " +
                               " Gift.a_gift_status_c, " +
                               " MotiviationDetail.a_motivation_detail_desc_c, " +
                               " GiftBatch.a_batch_description_c " +
                               " FROM " +
                               " public.a_gift_detail AS GiftDetail, " +
                               " public.a_gift_batch AS GiftBatch, " +
                               " public.a_motivation_detail AS MotiviationDetail, " +
                               " public.a_gift AS Gift " +
                               " WHERE " +
                               " GiftDetail.a_ledger_number_i = GiftBatch.a_ledger_number_i  " +
                               " AND GiftDetail.a_batch_number_i = GiftBatch.a_batch_number_i  " +
                               " AND GiftDetail.a_ledger_number_i = MotiviationDetail.a_ledger_number_i  " +
                               " AND GiftDetail.a_motivation_group_code_c = MotiviationDetail.a_motivation_group_code_c  " +
                               " AND GiftDetail.a_motivation_detail_code_c = MotiviationDetail.a_motivation_detail_code_c  " +
                               " AND GiftDetail.a_ledger_number_i = Gift.a_ledger_number_i  " +
                               " AND GiftDetail.a_batch_number_i = Gift.a_batch_number_i  " +
                               " AND GiftDetail.a_gift_transaction_number_i = Gift.a_gift_transaction_number_i  " +
                               " AND GiftDetail.a_ledger_number_i = " + ALedgerNumber +
                               " AND GiftDetail.a_cost_centre_code_c = '" + ACostCentre + "'" +
                               IchNumberFilter +
                               " AND GiftBatch.a_batch_status_c = '" + MFinanceConstants.BATCH_POSTED + "'" +
                               " AND GiftBatch.a_gl_effective_date_d >= " + APeriodStartDate.ToString("#yyyy-MM-dd#") +
                               " AND GiftBatch.a_gl_effective_date_d <= " + APeriodEndDate.ToString("#yyyy-MM-dd#") +
                               " AND MotiviationDetail.a_account_code_c = '" + AAcctCode + "'" +
                               " ORDER BY " +
                               " GiftDetail.p_recipient_key_n ASC, " +
                               " GiftDetail.a_motivation_group_code_c ASC, " +
                               " GiftDetail.a_motivation_detail_code_c ASC;";


                DataTable TmpTable = db.SelectDT(Query, "table", DBTransaction);

                foreach (DataRow Row in TmpTable.Rows)
                {
                    /* Print totals etc. found for last recipient */
                    /* Only do after first loop due to last recipient key check */

                    tmpLastRecipKey = Convert.ToInt32(Row["p_recipient_key_n"]);
                    tmpLastGroup    = Row["a_motivation_group_code_c"].ToString();
                    tmpLastDetail   = Row["a_motivation_detail_code_c"].ToString();

                    if (!FirstLoopFlag &&
                        ((tmpLastRecipKey != LastRecipKey) ||
                         (tmpLastGroup != LastGroup) ||
                         (tmpLastDetail != LastDetail)
                        )
                        )
                    {
                        if ((IndividualCreditTotal != 0) ||
                            (IndividualDebitTotal != 0))
                        {
                            if (LastRecipKey != 0)
                            {
                                /* Find partner short name details */
                                PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LastRecipKey, DBTransaction);
                                PPartnerRow PartnerRow     = (PPartnerRow)PartnerTable.Rows[0];

                                LastDetailDesc += " : " + PartnerRow.PartnerShortName;

                                ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" + LastDetailDesc;
                            }
                            else
                            {
                                AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadByPrimaryKey(ALedgerNumber,
                                                                                                                     LastGroup,
                                                                                                                     DBTransaction);
                                AMotivationGroupRow MotivationGroupRow = (AMotivationGroupRow)MotivationGroupTable.Rows[0];

                                ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" +
                                                    MotivationGroupRow.MotivationGroupDescription.TrimEnd(
                                    new Char[] { (' ') }) + "," + LastDetailDesc;
                            }

                            //Add data to export table
                            DataRow DR = (DataRow)AExportDataTable.NewRow();

                            DR[0] = ACostCentre;
                            DR[1] = ConvertAccount(AAcctCode);
                            DR[2] = ExportDescription;
                            DR[3] = "ICH-" + APeriodNumber.ToString("00");
                            DR[4] = APeriodEndDate;
                            DR[5] = IndividualDebitTotal;
                            DR[6] = IndividualCreditTotal;

                            AExportDataTable.Rows.Add(DR);

                            /* Reset total */
                            IndividualDebitTotal  = 0;
                            IndividualCreditTotal = 0;
                        }
                    }

                    if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
                    {
                        Decimal GiftAmount = Convert.ToDecimal(Row["a_gift_amount_n"]);

                        if (GiftAmount < 0)
                        {
                            IndividualDebitTotal -= GiftAmount;
                        }
                        else
                        {
                            IndividualCreditTotal += GiftAmount;
                        }
                    }
                    else
                    {
                        Decimal IntlGiftAmount = Convert.ToDecimal(Row["a_gift_amount_intl_n"]);

                        if (IntlGiftAmount < 0)
                        {
                            IndividualDebitTotal -= IntlGiftAmount;
                        }
                        else
                        {
                            IndividualCreditTotal += IntlGiftAmount;
                        }
                    }

                    /* Set loop variables */
                    LastRecipKey   = tmpLastRecipKey;
                    LastGroup      = tmpLastGroup;
                    LastDetail     = tmpLastDetail;
                    LastDetailDesc = Convert.ToString(Row["a_motivation_detail_desc_c"]);
                    FirstLoopFlag  = false;
                }     // foreach

                /* Print totals etc. found for last recipient */
                /* Only do after first loop due to last recipient key check */
                if (!FirstLoopFlag && ((IndividualCreditTotal != 0) || (IndividualDebitTotal != 0)))
                {
                    if (LastRecipKey != 0)
                    {
                        /* Find partner short name details */
                        PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LastRecipKey, DBTransaction);
                        PPartnerRow PartnerRow     = (PPartnerRow)PartnerTable.Rows[0];

                        LastDetailDesc += ":" + PartnerRow.PartnerShortName;

                        ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" + LastDetailDesc;
                    }
                    else
                    {
                        AMotivationGroupTable MotivationGroupTable =
                            AMotivationGroupAccess.LoadByPrimaryKey(ALedgerNumber, LastGroup, DBTransaction);
                        AMotivationGroupRow MotivationGroupRow = (AMotivationGroupRow)MotivationGroupTable.Rows[0];


                        ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" +
                                            MotivationGroupRow.MotivationGroupDescription.TrimEnd() + "," + LastDetailDesc;
                    }

                    //Add rows to export table
                    DataRow DR = (DataRow)AExportDataTable.NewRow();

                    DR[0] = ACostCentre;
                    DR[1] = ConvertAccount(AAcctCode);
                    DR[2] = ExportDescription;
                    DR[3] = "ICH-" + APeriodNumber.ToString("00");
                    DR[4] = APeriodEndDate;;
                    DR[5] = IndividualDebitTotal;
                    DR[6] = IndividualCreditTotal;

                    AExportDataTable.Rows.Add(DR);
                }
            });     // Get NewOrExisting AutoReadTransaction
        } // Export Gifts
Beispiel #29
0
 /// <summary>
 /// Closes the Database connection to the Database
 /// for TTimedProcessing.
 /// </summary>
 /// <returns>void</returns>
 private static void CloseDBConnection(TDataBase DBAccessObj)
 {
     DBAccessObj.CloseDBConnection();
 }
Beispiel #30
0
        /// <summary>
        /// export all posted invoices of other suppliers in this year
        /// </summary>
        public static void Export(string AOutputPath,
                                  char ACSVSeparator,
                                  string ANewLine,
                                  Int32 ALedgerNumber,
                                  Int32 AFinancialYear,
                                  string ACostCentres)
        {
            string filename = Path.GetFullPath(Path.Combine(AOutputPath, "accountspayable.csv"));

            Console.WriteLine("Writing file: " + filename);

            StringBuilder sb = new StringBuilder();

            TDBTransaction Transaction = new TDBTransaction();

            DBAccess.ReadTransaction(ref Transaction,
                                     delegate
            {
                TDataBase db = Transaction.DataBaseObj;

                // get all posted or paid ap_documents by their date issued
                string sql =
                    String.Format(
                        "SELECT * FROM PUB_{0} " +
                        "WHERE {1} = {2} AND " +
                        "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
                        "{7} >= ? AND {7} <= ?",
                        AApDocumentTable.GetTableDBName(),
                        AApDocumentTable.GetLedgerNumberDBName(),
                        ALedgerNumber,
                        AApDocumentTable.GetDocumentStatusDBName(),
                        MFinanceConstants.AP_DOCUMENT_POSTED,
                        MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                        MFinanceConstants.AP_DOCUMENT_PAID,
                        AApDocumentTable.GetDateIssuedDBName());

                List <OdbcParameter> Parameters = new List <OdbcParameter>();
                OdbcParameter param             = new OdbcParameter("startdate", OdbcType.DateTime);
                param.Value = TAccountingPeriodsWebConnector.GetPeriodStartDate(ALedgerNumber, AFinancialYear, 0, 1);
                Parameters.Add(param);
                param = new OdbcParameter("enddate", OdbcType.DateTime);
                //TODO: Calendar vs Financial Date Handling - Check if this should use financial year start/end and not assume calendar
                param.Value = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber, AFinancialYear, 0, 12);
                Parameters.Add(param);

                AApDocumentTable apDocuments = new AApDocumentTable();
                db.SelectDT(apDocuments, sql, Transaction, Parameters.ToArray(), 0, 0);

                // get all ap details
                sql =
                    String.Format(
                        "SELECT Detail.* FROM PUB_{0} AS Doc, PUB_{8} AS Detail " +
                        "WHERE Doc.{1} = {2} AND " +
                        "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
                        "{7} >= ? AND {7} <= ? AND " +
                        "Detail.{1} = Doc.{1} AND Detail.{9} = Doc.{9} AND " +
                        "Detail.{10} IN ({11})",
                        AApDocumentTable.GetTableDBName(),
                        AApDocumentTable.GetLedgerNumberDBName(),
                        ALedgerNumber,
                        AApDocumentTable.GetDocumentStatusDBName(),
                        MFinanceConstants.AP_DOCUMENT_POSTED,
                        MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                        MFinanceConstants.AP_DOCUMENT_PAID,
                        AApDocumentTable.GetDateIssuedDBName(),
                        AApDocumentDetailTable.GetTableDBName(),
                        AApDocumentTable.GetApDocumentIdDBName(),
                        AApDocumentDetailTable.GetCostCentreCodeDBName(),
                        "'" + ACostCentres.Replace(",", "','") + "'");

                AApDocumentDetailTable apDetails = new AApDocumentDetailTable();
                db.SelectDT(apDetails, sql, Transaction, Parameters.ToArray(), 0, 0);

                apDetails.DefaultView.Sort = AApDocumentDetailTable.GetApDocumentIdDBName();

                // get all ap payments
                sql =
                    String.Format(
                        "SELECT DP.{0}, DP.{1}, P.{2} AS {3}, DP.{7}, DP.{15} FROM PUB_{4} AS Doc, PUB_{5} AS DP, PUB_{6} AS P " +
                        "WHERE Doc.{7} = {8} AND " +
                        "({9} = '{10}' OR {9} = '{11}' OR {9} = '{12}') AND " +
                        "{13} >= ? AND {13} <= ? AND " +
                        "DP.{7} = Doc.{7} AND DP.{14} = Doc.{14} AND " +
                        "P.{7} = Doc.{7} AND P.{15} = DP.{15}",

                        AApDocumentPaymentTable.GetApDocumentIdDBName(),
                        AApDocumentPaymentTable.GetAmountDBName(),
                        AApPaymentTable.GetPaymentDateDBName(),
                        AApDocumentPaymentTable.GetDateCreatedDBName(),
                        AApDocumentTable.GetTableDBName(),
                        AApDocumentPaymentTable.GetTableDBName(),
                        AApPaymentTable.GetTableDBName(),

                        AApDocumentTable.GetLedgerNumberDBName(),
                        ALedgerNumber,
                        AApDocumentTable.GetDocumentStatusDBName(),
                        MFinanceConstants.AP_DOCUMENT_POSTED,
                        MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                        MFinanceConstants.AP_DOCUMENT_PAID,
                        AApDocumentTable.GetDateIssuedDBName(),

                        AApDocumentTable.GetApDocumentIdDBName(),
                        AApPaymentTable.GetPaymentNumberDBName());

                AApDocumentPaymentTable apPayments = new AApDocumentPaymentTable();
                db.SelectDT(apPayments, sql, Transaction, Parameters.ToArray(), 0, 0);

                apPayments.DefaultView.Sort = AApDocumentPaymentTable.GetApDocumentIdDBName();

                // get the analysis attributes for the taxes
                sql =
                    String.Format(
                        "SELECT Attrib.* FROM PUB_{0} AS Doc, PUB_{8} AS Attrib " +
                        "WHERE Doc.{1} = {2} AND " +
                        "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
                        "{7} >= ? AND {7} <= ? AND " +
                        "Attrib.{1} = Doc.{1} AND Attrib.{9} = Doc.{9}",
                        AApDocumentTable.GetTableDBName(),
                        AApDocumentTable.GetLedgerNumberDBName(),
                        ALedgerNumber,
                        AApDocumentTable.GetDocumentStatusDBName(),
                        MFinanceConstants.AP_DOCUMENT_POSTED,
                        MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                        MFinanceConstants.AP_DOCUMENT_PAID,
                        AApDocumentTable.GetDateIssuedDBName(),
                        AApAnalAttribTable.GetTableDBName(),
                        AApDocumentTable.GetApDocumentIdDBName());

                AApAnalAttribTable apAttrib = new AApAnalAttribTable();
                db.SelectDT(apAttrib, sql, Transaction, Parameters.ToArray(), 0, 0);

                apAttrib.DefaultView.Sort = AApAnalAttribTable.GetApDocumentIdDBName() + "," + AApAnalAttribTable.GetDetailNumberDBName();

                // get the supplier name
                sql =
                    String.Format(
                        "SELECT DISTINCT p.* FROM PUB_{0} AS Doc, PUB_{8} AS p " +
                        "WHERE Doc.{1} = {2} AND " +
                        "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
                        "{7} >= ? AND {7} <= ? AND " +
                        "p.{9} = Doc.{10}",
                        AApDocumentTable.GetTableDBName(),
                        AApDocumentTable.GetLedgerNumberDBName(),
                        ALedgerNumber,
                        AApDocumentTable.GetDocumentStatusDBName(),
                        MFinanceConstants.AP_DOCUMENT_POSTED,
                        MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                        MFinanceConstants.AP_DOCUMENT_PAID,
                        AApDocumentTable.GetDateIssuedDBName(),
                        PPartnerTable.GetTableDBName(),
                        PPartnerTable.GetPartnerKeyDBName(),
                        AApDocumentTable.GetPartnerKeyDBName());

                PPartnerTable suppliers = new PPartnerTable();
                db.SelectDT(suppliers, sql, Transaction, Parameters.ToArray(), 0, 0);

                foreach (AApDocumentRow doc in apDocuments.Rows)
                {
                    PPartnerRow supplier = (PPartnerRow)suppliers.Rows.Find(doc.PartnerKey);

                    DataRowView[] detailsRV = apDetails.DefaultView.FindRows(doc.ApDocumentId);

                    foreach (DataRowView rv in detailsRV)
                    {
                        AApDocumentDetailRow detail = (AApDocumentDetailRow)rv.Row;

                        if (doc.CreditNoteFlag)
                        {
                            detail.Amount *= -1.0m;
                        }

                        DataRowView[] payments = apPayments.DefaultView.FindRows(doc.ApDocumentId);

                        string DatePaid = string.Empty;

                        if (payments.Length > 1)
                        {
                            DatePaid = "Several Payments: ";

                            foreach (DataRowView rvPayment in payments)
                            {
                                AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)rvPayment.Row);
                                DatePaid += payment.DateCreated.Value.ToString("yyyyMMdd") + " ";
                                DatePaid += payment.PaymentNumber.ToString() + "; ";
                            }

                            // for gdpdu, only write dates to this column
                            DatePaid = string.Empty;
                        }
                        else if (payments.Length == 1)
                        {
                            AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)payments[0].Row);
                            DatePaid = payment.DateCreated.Value.ToString("yyyyMMdd");
                        }

                        sb.Append(StringHelper.StrMerge(
                                      new string[] {
                            doc.ApNumber.ToString(),
                            detail.DetailNumber.ToString(),
                            doc.DateIssued.ToString("yyyyMMdd"),
                            DatePaid,
                            doc.PartnerKey.ToString(),
                            supplier.PartnerShortName,
                            detail.CostCentreCode,
                            detail.AccountCode,
                            String.Format("{0:N}", detail.Amount),
                            detail.Narrative
                        }, ACSVSeparator));
                        sb.Append(ANewLine);
                    }
                }
            });

            StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));

            sw.Write(sb.ToString());
            sw.Close();
        }
Beispiel #31
0
 /// <summary>
 /// Returns the next Sequence Value for the given Sequence from the DB. - IMPORTANT: This increasing of the
 /// Value of the Sequence PERSISTS in the PostgreSQL implmentation even if the DB Transction gets rolled back!!!
 /// --> See https://wiki.openpetra.org/index.php/PostgreSQL:_Sequences_Not_Tied_to_DB_Transactions
 /// </summary>
 /// <param name="ASequenceName">Name of the Sequence.</param>
 /// <param name="ATransaction">An instantiated Transaction in which the Query
 /// to the DB will be enlisted.</param>
 /// <param name="ADatabase">Database object that can be used for querying.</param>
 /// <returns>Sequence Value.</returns>
 public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
 {
     return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT NEXTVAL('" + ASequenceName + "')", ATransaction)));
 }
Beispiel #32
0
        public void TestBankingDetails()
        {
            TDataBase               db          = DBAccess.Connect("TestBankingDetails");
            TDBTransaction          Transaction = db.BeginTransaction(IsolationLevel.Serializable);
            TPartnerEditUIConnector connector   = new TPartnerEditUIConnector(db);

            PartnerEditTDS MainDS = new PartnerEditTDS();

            PPartnerRow PartnerRow = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS, db);

            TCreateTestPartnerData.CreateNewLocation(PartnerRow.PartnerKey, MainDS);

            DataSet ResponseDS = new PartnerEditTDS();
            TVerificationResultCollection VerificationResult;

            TSubmitChangesResult result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);

            CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
                                                                                "There was a critical error when saving:");

            Assert.AreEqual(TSubmitChangesResult.scrOK, result, "TPartnerEditUIConnector SubmitChanges return value");

            Transaction.Commit();
            Transaction = db.BeginTransaction(IsolationLevel.Serializable);

            connector = new TPartnerEditUIConnector(PartnerRow.PartnerKey, db);

            // add a banking detail
            PartnerEditTDSPBankingDetailsRow bankingDetailsRow = MainDS.PBankingDetails.NewRowTyped(true);

            bankingDetailsRow.AccountName       = "account of " + PartnerRow.PartnerShortName;
            bankingDetailsRow.BankAccountNumber = new Random().Next().ToString();
            bankingDetailsRow.BankingDetailsKey = (MainDS.PBankingDetails.Count + 1) * -1;
            bankingDetailsRow.BankKey           = 43005004;
            bankingDetailsRow.MainAccount       = true;
            bankingDetailsRow.BankingType       = MPartnerConstants.BANKINGTYPE_BANKACCOUNT;
            MainDS.PBankingDetails.Rows.Add(bankingDetailsRow);

            PPartnerBankingDetailsRow partnerBankingDetails = MainDS.PPartnerBankingDetails.NewRowTyped(true);

            partnerBankingDetails.PartnerKey        = PartnerRow.PartnerKey;
            partnerBankingDetails.BankingDetailsKey = bankingDetailsRow.BankingDetailsKey;
            MainDS.PPartnerBankingDetails.Rows.Add(partnerBankingDetails);

            result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);

            CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
                                                                                "There was a critical error when saving 2:");

            foreach (DataTable t in MainDS.Tables)
            {
                if ((t == MainDS.PBankingDetails) ||
                    (t == MainDS.PPartnerBankingDetails) ||
                    (t == MainDS.PDataLabelValuePartner))
                {
                    int NumRows = t.Rows.Count;

                    for (int RowIndex = NumRows - 1; RowIndex >= 0; RowIndex -= 1)
                    {
                        DataRow InspectDR = t.Rows[RowIndex];

                        // delete all added Rows.
                        if (InspectDR.RowState == DataRowState.Added)
                        {
                            InspectDR.Delete();
                        }
                    }
                }
            }

            MainDS.AcceptChanges();

            Transaction.Commit();
            Transaction = db.BeginTransaction(IsolationLevel.Serializable);

            Assert.AreEqual(1, PBankingDetailsUsageAccess.CountViaPPartner(PartnerRow.PartnerKey, Transaction), "count of main accounts for partner");

            // add another account
            bankingDetailsRow                   = MainDS.PBankingDetails.NewRowTyped(true);
            bankingDetailsRow.AccountName       = "2nd account of " + PartnerRow.PartnerShortName;
            bankingDetailsRow.BankAccountNumber = new Random().Next().ToString();
            bankingDetailsRow.BankingDetailsKey = (MainDS.PBankingDetails.Count + 1) * -1;
            bankingDetailsRow.BankKey           = 43005004;
            bankingDetailsRow.MainAccount       = false;
            bankingDetailsRow.BankingType       = MPartnerConstants.BANKINGTYPE_BANKACCOUNT;
            MainDS.PBankingDetails.Rows.Add(bankingDetailsRow);

            partnerBankingDetails                   = MainDS.PPartnerBankingDetails.NewRowTyped(true);
            partnerBankingDetails.PartnerKey        = PartnerRow.PartnerKey;
            partnerBankingDetails.BankingDetailsKey = bankingDetailsRow.BankingDetailsKey;
            MainDS.PPartnerBankingDetails.Rows.Add(partnerBankingDetails);

            PartnerEditTDS ChangedDS = MainDS.GetChangesTyped(true);

            result = connector.SubmitChanges(ref ChangedDS, ref ResponseDS, out VerificationResult);
            MainDS.Merge(ChangedDS);

            foreach (DataTable t in MainDS.Tables)
            {
                if ((t == MainDS.PBankingDetails) ||
                    (t == MainDS.PPartnerBankingDetails) ||
                    (t == MainDS.PDataLabelValuePartner))
                {
                    int NumRows = t.Rows.Count;

                    for (int RowIndex = NumRows - 1; RowIndex >= 0; RowIndex -= 1)
                    {
                        DataRow InspectDR = t.Rows[RowIndex];

                        // delete all added Rows.
                        if (InspectDR.RowState == DataRowState.Added)
                        {
                            InspectDR.Delete();
                        }
                    }
                }
            }

            MainDS.AcceptChanges();

            CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
                                                                                "There was a critical error when saving 3:");

            // now delete the main bank account
            PartnerEditTDSPBankingDetailsRow toDelete = null;

            foreach (PartnerEditTDSPBankingDetailsRow row in MainDS.PBankingDetails.Rows)
            {
                if (row.MainAccount)
                {
                    toDelete = row;
                    break;
                }
            }

            Assert.IsNotNull(toDelete, "cannot find main account");
            Assert.AreEqual(true, toDelete.MainAccount, "should be the main account");
            MainDS.PPartnerBankingDetails.Rows.Find(new object[] { PartnerRow.PartnerKey, toDelete.BankingDetailsKey }).Delete();
            toDelete.Delete();

            ChangedDS = MainDS.GetChangesTyped(true);
            result    = connector.SubmitChanges(ref ChangedDS, ref ResponseDS, out VerificationResult);

            Assert.AreEqual(1, VerificationResult.Count, "should fail because we have no main account anymore");
            Assert.AreEqual(
                "One Bank Account of a Partner must be set as the 'Main Account'. Please select the record that should become the 'Main Account' and choose 'Set Main Account'.",
                VerificationResult[0].ResultText,
                "should fail because we have no main account anymore");

            PartnerEditTDSPBankingDetailsRow otherAccount = null;

            foreach (PartnerEditTDSPBankingDetailsRow row in MainDS.PBankingDetails.Rows)
            {
                if ((row.RowState != DataRowState.Deleted) && !row.MainAccount)
                {
                    otherAccount = row;
                    break;
                }
            }

            otherAccount.MainAccount = true;

            ChangedDS = MainDS.GetChangesTyped(true);
            result    = connector.SubmitChanges(ref ChangedDS, ref ResponseDS, out VerificationResult);

            MainDS.Merge(ChangedDS);
            MainDS.AcceptChanges();

            CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
                                                                                "There was a critical error when saving 4:");

            Transaction.Commit();
            Transaction = db.BeginTransaction(IsolationLevel.Serializable);

            // now delete the last remaining bank account
            toDelete = MainDS.PBankingDetails[0];
            Assert.AreEqual(true, toDelete.MainAccount);
            MainDS.PPartnerBankingDetails.Rows.Find(new object[] { PartnerRow.PartnerKey, toDelete.BankingDetailsKey }).Delete();
            toDelete.Delete();
            ChangedDS = MainDS.GetChangesTyped(true);
            result    = connector.SubmitChanges(ref ChangedDS, ref ResponseDS, out VerificationResult);
            MainDS.Merge(ChangedDS);
            MainDS.AcceptChanges();

            CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
                                                                                "There was a critical error when saving 5:");

            Transaction.Commit();
        }
        /// <summary>
        /// Gets called in regular intervals from a Timer in Class TTimedProcessing.
        /// </summary>
        /// <param name="ADataBaseObj">Already instatiated DB Access object with opened DB connection.</param>
        /// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
        public static void Process(TDataBase ADataBaseObj, bool ARunManually)
        {
            TDBTransaction        ReadWriteTransaction;
            bool                  NewTransaction;
            bool                  LastReminderDateAcquired;
            DateTime              LastReminderDate;
            DataSet               ReminderResultsDS;
            SSystemDefaultsRow    SystemDefaultsDR;
            PPartnerReminderTable PartnerReminderDT;
            int         ReminderFreqency;
            TSmtpSender Sender = null;

            if (TLogging.DebugLevel >= 6)
            {
                TLogging.Log("Entering TProcessPartnerReminders.Process...");
            }

            ReadWriteTransaction = ADataBaseObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
                                                                            out NewTransaction);

            /*
             * This whole process must either succeed or fail, therefore the whole thing is in a try-catch.
             */
            try
            {
                /*
                 * Obtain date when PartnerReminders last ran. This is stored in a SystemDefault. If it doesn't exist already,
                 * a new SystemDefault with an ancient date is created for us.
                 */
                LastReminderDateAcquired = GetLastReminderDate(out LastReminderDate, out SystemDefaultsDR, ReadWriteTransaction);

                if (!LastReminderDateAcquired)
                {
                    TLogging.Log(
                        TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                        ": Could not send Partner Reminders because Petra couldn't create the required SystemDefault setting for the Last Reminder Date!");

                    ReadWriteTransaction.Rollback();

                    return;
                }

                try
                {
                    Sender = new TSmtpSender();
                }
                catch (ESmtpSenderInitializeException e)
                {
                    TLogging.Log(
                        TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                        ": " + e.Message);

                    if (e.InnerException != null)
                    {
                        TLogging.Log(e.InnerException.ToString(), TLoggingType.ToLogfile);
                    }

                    return;
                }

                // Retrieve all PartnerReminders we need to process.
                ReminderResultsDS = GetRemindersToProcess(LastReminderDate, out PartnerReminderDT,
                                                          ADataBaseObj, ReadWriteTransaction);

                /*
                 * We now have a Typed DataTable with the PartnerReminders that we need to process.
                 * Iterate through the PartnerReminders, update data, and send an email for each PartnerReminder.
                 */
                if (TLogging.DebugLevel >= 6)
                {
                    TLogging.Log("_---------------------------------_");
                    TLogging.Log("PartnerReminders data before we start processing all PartnerReminders....");
                    TLogging.Log(ReminderResultsDS.GetXml().ToString());
                }

                foreach (PPartnerReminderRow PartnerReminderDR in PartnerReminderDT.Rows)
                {
                    if (TLogging.DebugLevel >= 4)
                    {
                        TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                                   ": Processing Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
                    }

                    ReminderFreqency = (PartnerReminderDR.IsReminderFrequencyNull()) ? 0 : PartnerReminderDR.ReminderFrequency;

                    PartnerReminderDR.BeginEdit();
                    PartnerReminderDR.LastReminderSent = DateTime.Now.Date;
                    PartnerReminderDR.NextReminderDate = DateTime.Now.Date.AddDays(ReminderFreqency);

                    if (!PartnerReminderDR.IsEventDateNull())   // Reminder has an Event Date
                    {
                        if (PartnerReminderDR.NextReminderDate > PartnerReminderDR.EventDate)
                        {
                            if (TLogging.DebugLevel >= 5)
                            {
                                TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                                           ": Deactivating Reminder ID {0} for Partner {1} as its Event Date is in the past.",
                                                           PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
                            }

                            PartnerReminderDR.ReminderActive = false;
                        }
                    }

                    if (TLogging.DebugLevel >= 4)
                    {
                        TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                                   ": Sending email for Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
                    }

                    Boolean emailSentOk = false;
                    try
                    {
                        emailSentOk = SendReminderEmail(PartnerReminderDR, ReadWriteTransaction, Sender);
                    }
                    catch (ESmtpSenderInitializeException e) // if an exception was thrown, assume the email didn't go.
                    {
                        TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                                   ": Reminder ID {0} for Partner {1}: {2}", PartnerReminderDR.ReminderId,
                                                   PartnerReminderDR.PartnerKey,
                                                   e.Message));

                        if (e.InnerException != null)
                        {
                            TLogging.Log(e.InnerException.Message);
                        }
                    }
                    catch (ESmtpSenderSendException e)
                    {
                        TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                                   ": Reminder ID {0} for Partner {1}: {2}", PartnerReminderDR.ReminderId,
                                                   PartnerReminderDR.PartnerKey,
                                                   e.Message));

                        if (e.InnerException != null)
                        {
                            TLogging.Log(e.InnerException.Message);
                        }
                    }
                    catch (Exception e)
                    {
                        TLogging.Log(e.Message);
                    }

                    if (emailSentOk)
                    {
                        // Accept the edit
                        if (TLogging.DebugLevel >= 4)
                        {
                            TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                                       ": Reminder ID {0} for Partner {1} accepted by SMTP server.", PartnerReminderDR.ReminderId,
                                                       PartnerReminderDR.PartnerKey));
                        }

                        PartnerReminderDR.EndEdit();
                    }
                    else
                    {
                        // Cancel the edit
                        TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                                   ": Reminder ID {0} for Partner {1} REJECTED by SMTP server.", PartnerReminderDR.ReminderId,
                                                   PartnerReminderDR.PartnerKey));

                        PartnerReminderDR.CancelEdit();
                    }
                }

                if (TLogging.DebugLevel >= 6)
                {
                    TLogging.Log("_---------------------------------_");
                    TLogging.Log("PartnerReminders data after processing all PartnerReminders, before writing it to DB....");
                    TLogging.Log(PartnerReminderDT.DataSet.GetXml().ToString());
                }

                // Update all the changed PartnerReminder Rows
                PPartnerReminderAccess.SubmitChanges(PartnerReminderDT, ReadWriteTransaction);

                if (TLogging.DebugLevel >= 6)
                {
                    TLogging.Log("_---------------------------------_");
                }

                /*
                 * Update the SystemDefault that keeps track of when Partner Reminders last ran.
                 * (SystemDefaultsDR will point to the row we loaded earlier on, OR the row we added earlier on
                 * if there wasn't already a SystemDefault row.)
                 */
                UpdateLastReminderDate(SystemDefaultsDR, ReadWriteTransaction);

                if (NewTransaction)
                {
                    ReadWriteTransaction.Commit();
                }

                TLogging.LogAtLevel(1, TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " ran succesfully.");
            }
            catch (Exception Exc)
            {
                TLogging.Log(
                    TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " encountered an Exception:" + Environment.NewLine +
                    Exc.ToString());

                if (NewTransaction)
                {
                    ReadWriteTransaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (Sender != null)
                {
                    Sender.Dispose();
                }
            }
        }
        /// <summary>
        /// This method needs to be implemented by extracts that can't follow the default processing with just one query.
        /// </summary>
        /// <param name="AParameters"></param>
        /// <param name="ATransaction"></param>
        /// <param name="AExtractId"></param>
        protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
        {
            Boolean        ReturnValue = false;
            Int32          ExtractId   = -1;
            TDBTransaction Transaction = new TDBTransaction();
            TDataBase      db          = DBAccess.Connect("RunSpecialTreatment");

            db.ReadTransaction(ref Transaction,
                               delegate
            {
                DataTable giftdetails;

                string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByAmount.sql");

                List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
                bool AddressFilterAdded;
                DataTable partnerkeys = new DataTable();


                // call to derived class to retrieve parameters specific for extract
                RetrieveParameters(AParameters, ref SqlStmt, ref SqlParameterList);

                // add address filter information to sql statement and parameter list
                AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);

                // now run the database query
                TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
                giftdetails = db.SelectDT(SqlStmt, "partners", Transaction,
                                          SqlParameterList.ToArray());

                // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
                // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
                if (AParameters.Get("CancelReportCalculation").ToBool() == true)
                {
                    return;
                }

                TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);

                // With the result of the original query process the data and identify the partner keys for
                // the extract.
                partnerkeys.Columns.Add("0", typeof(Int64));
                partnerkeys.Columns.Add("1", typeof(string));
                partnerkeys.Columns.Add("p_site_key_n", typeof(Int64));
                partnerkeys.Columns.Add("p_location_key_i", typeof(Int32));
                ProcessGiftDetailRecords(giftdetails, AddressFilterAdded, AParameters, ref partnerkeys);

                // filter data by postcode (if applicable)
                PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction);

                // create an extract with the given name in the parameters
                int keyCount;
                List <long> ignoredPartnerKeys = null;
                ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
                    AParameters.Get("param_extract_name").ToString(),
                    AParameters.Get("param_extract_description").ToString(),
                    out ExtractId,
                    partnerkeys,
                    0,
                    AddressFilterAdded,
                    out keyCount,
                    out ignoredPartnerKeys);
            });
            AExtractId = ExtractId;
            return(ReturnValue);
        } // Run Special Treatment
Beispiel #35
0
        /// <summary>
        /// get the default bank account for this ledger
        /// </summary>
        /// <param name="ALedgerNumber"></param>
        /// <param name="ADataBase"></param>
        public static string GetDefaultBankAccount(int ALedgerNumber, TDataBase ADataBase = null)
        {
            #region Validate Arguments

            if (ALedgerNumber <= 0)
            {
                throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
                                                                                       "Function:{0} - The Ledger number must be greater than 0!"),
                                                                                   Utilities.GetMethodName(true)), ALedgerNumber);
            }

            #endregion Validate Arguments

            string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
                SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());

            if (BankAccountCode.Length == 0)
            {
                TDBTransaction readTransaction = null;

                try
                {
                    DBAccess.GetDBAccessObj(ADataBase).GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                                                                                           TEnforceIsolationLevel.eilMinimum, ref readTransaction,
                                                                                           delegate
                    {
                        // use the first bank account
                        AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction);

                        accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
                                                                  MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
                                                                  AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";

                        if (accountProperties.DefaultView.Count > 0)
                        {
                            BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
                        }
                        else
                        {
                            string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c" +
                                              " FROM a_gift_batch " +
                                              " WHERE a_gift_batch.a_ledger_number_i =" + ALedgerNumber +
                                              "  AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "'" +
                                              " ORDER BY a_gift_batch.a_batch_number_i DESC" +
                                              " LIMIT 1;";

                            DataTable latestAccountCode =
                                DBAccess.GetDBAccessObj(ADataBase).SelectDT(SQLQuery, "LatestAccountCode", readTransaction);

                            // use the Bank Account of the previous Gift Batch
                            if ((latestAccountCode != null) && (latestAccountCode.Rows.Count > 0))
                            {
                                BankAccountCode = latestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString();     //"a_bank_account_code_c"
                            }
                            // if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
                            else
                            {
                                AAccountTable accountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction);

                                #region Validate Data

                                if ((accountTable == null) || (accountTable.Count == 0))
                                {
                                    throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
                                                                                                               "Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
                                                                                                           Utilities.GetMethodName(true),
                                                                                                           ALedgerNumber));
                                }

                                #endregion Validate Data

                                DataView dv  = accountTable.DefaultView;
                                dv.Sort      = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
                                dv.RowFilter = String.Format("{0} = true AND {1} = true",
                                                             AAccountTable.GetAccountActiveFlagDBName(),
                                                             AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
                                DataTable sortedDT = dv.ToTable();

                                TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ALedgerNumber);
                                List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);

                                foreach (DataRow account in sortedDT.Rows)
                                {
                                    // check if this account reports to the CASH account
                                    if (children.Contains(account["a_account_code_c"].ToString()))
                                    {
                                        BankAccountCode = account["a_account_code_c"].ToString();
                                        break;
                                    }
                                }
                            }
                        }
                    });
                }
                catch (Exception ex)
                {
                    TLogging.LogException(ex, Utilities.GetMethodSignature());
                    throw;
                }
            }

            return(BankAccountCode);
        }
Beispiel #36
0
 /// <summary>
 /// constructor
 /// </summary>
 /// <param name="parameters"></param>
 /// <param name="results"></param>
 /// <param name="reportStore"></param>
 /// <param name="report"></param>
 /// <param name="dataDB"></param>
 /// <param name="depth"></param>
 /// <param name="column"></param>
 /// <param name="lineId"></param>
 /// <param name="parentRowId"></param>
 public TRptSituation(TParameterList parameters,
     TResultList results,
     TReportStore reportStore,
     TRptReport report,
     TDataBase dataDB,
     int depth,
     int column,
     int lineId,
     int parentRowId)
 {
     TRptSituation.RunningCode = 0;
     this.Parameters = parameters;
     this.Results = results;
     this.ReportStore = reportStore;
     this.CurrentReport = report;
     this.DatabaseConnection = dataDB;
     this.Depth = depth;
     this.column = column;
     this.LineId = lineId;
     this.ParentRowId = parentRowId;
 }
Beispiel #37
0
 /// <summary>
 /// Returns the next sequence value for the given Sequence from the DB.
 /// </summary>
 /// <param name="ASequenceName">Name of the Sequence.</param>
 /// <param name="ATransaction">An instantiated Transaction in which the Query
 /// to the DB will be enlisted.</param>
 /// <param name="ADatabase">Database object that can be used for querying.</param>
 /// <returns>Sequence Value.</returns>
 public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
 {
     // TODO problem: sequence should be committed? separate transaction?
     // see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=44
     // or use locking? see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=50
     return Convert.ToInt64(ADatabase.ExecuteScalar("SELECT NEXTVAL('" + ASequenceName + "')", ATransaction));
 }
Beispiel #38
0
 /// <summary>
 /// constructor
 /// </summary>
 /// <param name="situation"></param>
 /// <param name="depth"></param>
 /// <param name="column"></param>
 /// <param name="lineId"></param>
 /// <param name="parentRowId"></param>
 public TRptSituation(TRptSituation situation, int depth, int column, int lineId, int parentRowId)
 {
     this.Parameters = situation.Parameters;
     this.Results = situation.Results;
     this.ReportStore = situation.ReportStore;
     this.CurrentReport = situation.CurrentReport;
     this.DatabaseConnection = situation.DatabaseConnection;
     this.Depth = depth;
     this.column = column;
     this.LineId = lineId;
     this.ParentRowId = parentRowId;
 }
Beispiel #39
0
 /// <summary>
 /// Restart a sequence with the given value.
 /// </summary>
 public void RestartSequence(String ASequenceName,
     TDBTransaction ATransaction,
     TDataBase ADatabase,
     Int64 ARestartValue)
 {
     ADatabase.ExecuteScalar(
         "SELECT pg_catalog.setval('" + ASequenceName + "', " + ARestartValue.ToString() + ", true);", ATransaction);
 }
Beispiel #40
0
 /// <summary>
 /// restart a sequence with the given value has not been implemented
 /// </summary>
 public void RestartSequence(String ASequenceName,
     TDBTransaction ATransaction,
     TDataBase ADatabase,
     Int64 ARestartValue)
 {
     // not implemented
 }
Beispiel #41
0
        /// <summary>
        /// todoComment
        /// </summary>
        /// <param name="databaseConnection"></param>
        /// <param name="pv_ledger_number_i"></param>
        /// <param name="pv_account_code_c"></param>
        /// <param name="accountType">returns the type of account, ie. Income, Expense, Assets, Liab, Equity</param>
        /// <param name="postingAccount">returns true if the account is a posting account
        /// </param>
        /// <param name="debitCreditIndicator"></param>
        /// <returns></returns>
        private bool TAccountInfo(TDataBase databaseConnection,
            System.Int32 pv_ledger_number_i,
            String pv_account_code_c,
            out String accountType,
            out bool postingAccount,
            out bool debitCreditIndicator)
        {
            bool ReturnValue;
            string strSql;
            DataTable tab;

            ReturnValue = false;
            debitCreditIndicator = false;
            accountType = "";
            List <OdbcParameter>parameters = new List <OdbcParameter>();
            OdbcParameter param = new OdbcParameter("accountcode", OdbcType.VarChar);
            param.Value = pv_account_code_c;
            parameters.Add(param);
            postingAccount = false;
            strSql = "SELECT a_account_type_c, a_posting_status_l, a_debit_credit_indicator_l FROM PUB_a_account" +
                     " WHERE a_ledger_number_i = " + pv_ledger_number_i.ToString() +
                     " AND a_account_code_c = ?";
            tab =
                databaseConnection.SelectDT(strSql, "AccountType", databaseConnection.Transaction,
                    parameters.ToArray());

            if (tab.Rows.Count > 0)
            {
                accountType = Convert.ToString(tab.Rows[0]["a_account_type_c"]);
                postingAccount = (Boolean)tab.Rows[0]["a_posting_status_l"];
                debitCreditIndicator = (Boolean)tab.Rows[0]["a_debit_credit_indicator_l"];
                ReturnValue = true;
            }

            return ReturnValue;
        }
        /// <summary>
        /// Retrieves all the PartnerReminders that need to be processed.
        /// </summary>
        /// <param name="ALastReminderDate">Date when PartnerReminders processing last ran.</param>
        /// <param name="APartnerReminderDT">PartnerReminders DataRows that need to be processed.</param>
        /// <param name="ADBAccessObj">Already instatiated DB Access object with opened DB connection.</param>
        /// <param name="AReadTransaction">Already instantiated DB Transaction.</param>
        /// <returns>DataSet containing the <paramref name="APartnerReminderDT" /> DataTable.</returns>
        private static DataSet GetRemindersToProcess(DateTime ALastReminderDate, out PPartnerReminderTable APartnerReminderDT,
            TDataBase ADBAccessObj, TDBTransaction AReadTransaction)
        {
            List <OdbcParameter>OdbcParams;
            string SQLCommand;

            // Add Typed DataTable to a DataSet so we get the Partner Reminders in a Typed DataTable
            APartnerReminderDT = new PPartnerReminderTable();
            DataSet ReminderResultsDS = new DataSet();
            ReminderResultsDS.Tables.Add(APartnerReminderDT);

            SQLCommand = "SELECT * FROM PUB_p_partner_reminder WHERE ";
            SQLCommand += " ( p_next_reminder_date_d > ? OR p_last_reminder_sent_d IS NULL) AND ";
            SQLCommand += " p_next_reminder_date_d <= ? AND ";
            SQLCommand += " p_reminder_active_l = TRUE AND ";
            SQLCommand += " p_email_address_c <> ''";

            OdbcParams = new List <OdbcParameter>();

            // Parameter 1 = LastReminderDate from SystemDefaults
            OdbcParams.Add(new OdbcParameter("LastDate", OdbcType.Date));
            OdbcParams[0].Value = ALastReminderDate.Date;

            // Parameter 2 = Today's date
            OdbcParams.Add(new OdbcParameter("Now", OdbcType.Date));
            OdbcParams[1].Value = DateTime.Now.Date;

            DBAccess.GDBAccessObj.Select(ReminderResultsDS, SQLCommand, APartnerReminderDT.TableName, AReadTransaction, OdbcParams.ToArray());

            // Mark the data as being 'unchanged' (rather than 'new', which it is by default)
            ReminderResultsDS.AcceptChanges();

            return ReminderResultsDS;
        }
        /// <summary>
        /// Run extract in case the user wants to analyze receiving fields.
        /// </summary>
        /// <param name="AParameters"></param>
        /// <param name="ATransaction"></param>
        /// <param name="AExtractId"></param>
        private bool ProcessReceivingFields(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
        {
            /*Approach:
             * In case of a specified "Period" only find persons
             * that have a commitment record.
             * In case of "Now" or "Ever" also find partners
             * (persons & families) without such a commitment
             * record, that match the specified criteria.
             * In case of "Now" only find partners with a "Worker" type.
             * (This check is dropped in case of "Ever")
             * When interested in families only, also find families
             * for which a member matches the specified criteria.*/

            bool ReturnValue = false;

            // for receiving fields first look at commitments
            ReturnValue = ProcessCommitments(true, AParameters, ATransaction, out AExtractId);

            if (ReturnValue == false)
            {
                return(ReturnValue);
            }

            // if only commitments need to be considered then no need to continue here
            if (AParameters.Get("param_commitments_and_worker_field").IsZeroOrNull() ||
                (AParameters.Get("param_commitments_and_worker_field").ToString() == "CommitmentsOnly"))
            {
                return(ReturnValue);
            }

            bool   AddressFilterAdded;
            string SqlStmtWorkerFieldOriginal = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByField.WorkerField.sql");
            string SqlStmt;
            List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
            string TypeCodeParameter;

            // If date range was specified then only look at staff data. Otherwise look for persons and families seperately.
            if (AParameters.Get("param_field_dates").ToString() == "DateRange")
            {
                return(ReturnValue);
            }

            // prepare parameter field for partner type code.
            if (AParameters.Get("param_field_dates").ToString() == "DateEver")
            {
                TypeCodeParameter = "";
            }
            else
            {
                TypeCodeParameter = "OMER%";
            }

            // prepare list of selected fields
            ICollection <String> param_fields;

            param_fields = AParameters.Get("param_fields").ToString().Split(new Char[] { ',', });

            if (param_fields.Count == 0)
            {
                throw new NoNullAllowedException("At least one option must be checked.");
            }

            // now add parameters to sql parameter list
            SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));

            SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
            {
                Value = AParameters.Get("param_active").ToBool()
            });
            SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
            {
                Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
            });

            // ----------------------------------------------------------------------------------------
            // now start retrieving either families or persons whose worker field is set to given value
            // ----------------------------------------------------------------------------------------

            SqlStmt = SqlStmtWorkerFieldOriginal;
            SqlStmt = SqlStmt.Replace("##person_or_family_table##", ", pub_p_person");
            SqlStmt = SqlStmt.Replace("##person_or_family_table_name##", "pub_p_person");
            SqlStmt = SqlStmt.Replace("##exclude_familiy_members_existing_in_extract##", "");
            SqlStmt = SqlStmt.Replace("##worker_type##", TypeCodeParameter);

            if (AParameters.Get("param_families_only").ToBool())
            {
                /* In case that only family records are wanted a join via family key of a person is needed
                 * to find families of persons. */
                SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
                                          " AND pub_p_partner.p_partner_key_n = pub_p_person.p_family_key_n");
            }
            else
            {
                // in this case there will be person records in the extract
                SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
                                          " AND pub_p_partner.p_partner_key_n = pub_p_person.p_partner_key_n");
            }

            // add address filter information to sql statement and parameter list
            AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);

            // now run the database query
            TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
            DataTable partnerkeys = ATransaction.DataBaseObj.SelectDT(SqlStmt, "partners", ATransaction,
                                                                      SqlParameterList.ToArray());

            // filter data by postcode (if applicable)
            PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);

            // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
            // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
            if (AParameters.Get("CancelReportCalculation").ToBool() == true)
            {
                return(false);
            }

            TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);

            // create an extract with the given name in the parameters
            int         keyCount;
            List <long> ignoredPartnerKeys = null;

            TExtractsHandling.ExtendExtractFromListOfPartnerKeys(
                AExtractId,
                partnerkeys,
                0,
                AddressFilterAdded,
                out keyCount,
                out ignoredPartnerKeys,
                false);

            // ----------------------------------------------------------------------------------------
            // Now start retrieving families whose worker field is set to given value and that are not
            // already contained in the created extract.
            // ----------------------------------------------------------------------------------------

            SqlStmt = SqlStmtWorkerFieldOriginal;

            // need to rebuild parameter list as statement is also loaded again and filled
            SqlParameterList.Clear();
            SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));

            SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
            {
                Value = AParameters.Get("param_active").ToBool()
            });
            SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
            {
                Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
            });


            SqlStmt = SqlStmt.Replace("##person_or_family_table##", ", pub_p_family");
            SqlStmt = SqlStmt.Replace("##person_or_family_table_name##", "pub_p_family");
            SqlStmt = SqlStmt.Replace("##worker_type##", TypeCodeParameter);
            SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
                                      " AND pub_p_partner.p_partner_key_n = pub_p_family.p_partner_key_n");

            SqlStmt = SqlStmt.Replace("##exclude_familiy_members_existing_in_extract##",
                                      "AND NOT EXISTS (SELECT pub_p_family.p_partner_key_n " +
                                      " FROM pub_p_family, pub_p_person, pub_m_extract " +
                                      " WHERE pub_p_person.p_family_key_n = pub_p_family.p_partner_key_n " +
                                      " AND pub_m_extract.m_extract_id_i = " + AExtractId.ToString() +
                                      " AND pub_m_extract.p_partner_key_n = pub_p_person.p_partner_key_n)");

            // add address filter information to sql statement and parameter list
            AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);

            // now run the database query
            TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
            partnerkeys.Clear();
            partnerkeys = ATransaction.DataBaseObj.SelectDT(SqlStmt, "partners", ATransaction,
                                                            SqlParameterList.ToArray());

            // filter data by postcode (if applicable)
            PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);

            // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
            // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
            if (AParameters.Get("CancelReportCalculation").ToBool() == true)
            {
                return(false);
            }

            TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);

            // create an extract with the given name in the parameters
            TExtractsHandling.ExtendExtractFromListOfPartnerKeys(
                AExtractId,
                partnerkeys,
                0,
                AddressFilterAdded,
                out keyCount,
                out ignoredPartnerKeys,
                false);

            ReturnValue = true;

            return(ReturnValue);
        }
Beispiel #44
0
        public static DataConsentTDS LastKnownEntry(
            Int64 APartnerKey,
            string ADataType
            )
        {
            TDBTransaction       T            = new TDBTransaction();
            TDataBase            DB           = DBAccess.Connect("Get Last known entry");
            DataConsentTDS       Set          = new DataConsentTDS();
            List <OdbcParameter> SQLParameter = new List <OdbcParameter>();

            DB.ReadTransaction(ref T, delegate {
                string sql = "SELECT " +
                             "`p_consent_history`.*, " +
                             "GROUP_CONCAT(`p_consent_history_permission`.`p_purpose_code_c` SEPARATOR ',') AS `AllowedPurposes` " +
                             "FROM `p_consent_history` " +
                             "LEFT JOIN `p_consent_history_permission` " +
                             "ON `p_consent_history`.`p_entry_id_i` = `p_consent_history_permission`.`p_consent_history_entry_i` " +
                             "WHERE `p_consent_history`.`p_partner_key_n` = ? " +
                             "AND `p_consent_history`.`p_type_c` = ? " +
                             "GROUP BY `p_consent_history`.`p_entry_id_i` " +
                             "ORDER BY `p_consent_history`.`p_entry_id_i` DESC " +
                             "LIMIT 1";

                SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.BigInt)
                {
                    Value = APartnerKey
                });
                SQLParameter.Add(new OdbcParameter("DataType", OdbcType.VarChar)
                {
                    Value = ADataType
                });

                DB.SelectDT(Set.PConsentHistory, sql, T, SQLParameter.ToArray());

                if (Set.PConsentHistory.Count == 0)
                {
                    // there is no consent yet
                    // do we have a value at all?
                    List <string> Subscriptions;
                    List <string> PartnerTypes;
                    string DefaultEmailAddress;
                    string DefaultPhoneMobile;
                    string DefaultPhoneLandline;
                    PartnerEditTDS PartnerDS = TSimplePartnerEditWebConnector.GetPartnerDetails(APartnerKey,
                                                                                                out Subscriptions,
                                                                                                out PartnerTypes,
                                                                                                out DefaultEmailAddress,
                                                                                                out DefaultPhoneMobile,
                                                                                                out DefaultPhoneLandline);

                    if (ADataType == MPartnerConstants.CONSENT_TYPE_ADDRESS)
                    {
                        // what about new contact?
                        PLocationRow locationRow = null;

                        if (PartnerDS.PLocation.Rows.Count > 0)
                        {
                            locationRow = PartnerDS.PLocation[0];
                        }
                        else
                        {
                            locationRow = PartnerDS.PLocation.NewRowTyped();
                        }

                        PConsentHistoryRow row = Set.PConsentHistory.NewRowTyped();
                        row.EntryId            = -1;
                        row.PartnerKey         = APartnerKey;
                        row.Type        = ADataType;
                        row.Value       = locationRow.StreetName + ", " + locationRow.PostalCode + " " + locationRow.City + ", " + locationRow.CountryCode;
                        row.ConsentDate = DateTime.Today;
                        Set.PConsentHistory.Rows.Add(row);
                    }

                    if (ADataType == MPartnerConstants.CONSENT_TYPE_EMAIL)
                    {
                        PConsentHistoryRow row = Set.PConsentHistory.NewRowTyped();
                        row.EntryId            = -1;
                        row.PartnerKey         = APartnerKey;
                        row.Type        = ADataType;
                        row.Value       = DefaultEmailAddress;
                        row.ConsentDate = DateTime.Today;
                        Set.PConsentHistory.Rows.Add(row);
                    }

                    if (ADataType == MPartnerConstants.CONSENT_TYPE_LANDLINE)
                    {
                        PConsentHistoryRow row = Set.PConsentHistory.NewRowTyped();
                        row.EntryId            = -1;
                        row.PartnerKey         = APartnerKey;
                        row.Type        = ADataType;
                        row.Value       = DefaultPhoneLandline;
                        row.ConsentDate = DateTime.Today;
                        Set.PConsentHistory.Rows.Add(row);
                    }

                    if (ADataType == MPartnerConstants.CONSENT_TYPE_MOBILE)
                    {
                        PConsentHistoryRow row = Set.PConsentHistory.NewRowTyped();
                        row.EntryId            = -1;
                        row.PartnerKey         = APartnerKey;
                        row.Type        = ADataType;
                        row.Value       = DefaultPhoneMobile;
                        row.ConsentDate = DateTime.Today;
                        Set.PConsentHistory.Rows.Add(row);
                    }
                }

                PConsentChannelAccess.LoadAll(Set, T);
                PConsentPurposeAccess.LoadAll(Set, T);
            });

            return(Set);
        }
Beispiel #45
0
 /// <summary>
 /// constructor
 /// </summary>
 /// <param name="situation"></param>
 public TRptSituation(TRptSituation situation)
 {
     this.Parameters = situation.Parameters;
     this.Results = situation.Results;
     this.ReportStore = situation.ReportStore;
     this.CurrentReport = situation.CurrentReport;
     this.DatabaseConnection = situation.DatabaseConnection;
     this.Depth = situation.Depth;
     this.column = situation.column;
     this.LineId = situation.LineId;
     this.ParentRowId = situation.ParentRowId;
 }
        /// <summary>
        /// Add persons or families to extract that have commitments to specified receiving/sending fields
        /// </summary>
        /// <param name="AProcessReceivingFields"></param>
        /// <param name="AParameters"></param>
        /// <param name="ATransaction"></param>
        /// <param name="AExtractId"></param>
        private bool ProcessCommitments(bool AProcessReceivingFields, TParameterList AParameters,
                                        TDBTransaction ATransaction, out int AExtractId)
        {
            bool   ReturnValue = false;
            bool   AddressFilterAdded;
            string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByField.Commitment.sql");

            List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();

            // need to set initial value here in case method needs to return before value is set
            AExtractId = -1;

            // prepare list of selected fields
            ICollection <String> param_fields;

            param_fields = AParameters.Get("param_fields").ToString().Split(new Char[] { ',', });

            if (param_fields.Count == 0)
            {
                throw new NoNullAllowedException("At least one option must be checked.");
            }

            // now add parameters to sql parameter list
            SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));

            SqlParameterList.Add(new OdbcParameter("param_from_date_unset", OdbcType.Bit)
            {
                Value = AParameters.Get("param_from_date").IsZeroOrNull()
            });
            SqlParameterList.Add(new OdbcParameter("param_from_date", OdbcType.Date)
            {
                Value = AParameters.Get("param_from_date").ToDate()
            });
            SqlParameterList.Add(new OdbcParameter("param_until_date_unset", OdbcType.Bit)
            {
                Value = AParameters.Get("param_until_date").IsZeroOrNull()
            });
            SqlParameterList.Add(new OdbcParameter("param_until_date", OdbcType.Date)
            {
                Value = AParameters.Get("param_until_date").ToDate()
            });
            SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
            {
                Value = AParameters.Get("param_active").ToBool()
            });
            SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
            {
                Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
            });

            if (AProcessReceivingFields)
            {
                // for receiving fields target field table field needs to be used
                SqlStmt = SqlStmt.Replace("##sending_or_receiving_field##", "pm_receiving_field_n");
            }
            else
            {
                // for sending fields home office table field needs to be used
                SqlStmt = SqlStmt.Replace("##sending_or_receiving_field##", "pm_home_office_n");
            }

            if (AParameters.Get("param_families_only").ToBool())
            {
                /* In case that only family records are wanted a join via family key of a person is needed
                 * to find families of persons. */
                SqlStmt = SqlStmt.Replace("##person_table##", ", pub_p_person");
                SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
                                          " AND pub_p_person.p_partner_key_n = pub_pm_staff_data.p_partner_key_n" +
                                          " AND pub_p_partner.p_partner_key_n = pub_p_person.p_family_key_n ");
            }
            else
            {
                // in this case there will be person records in the extract
                SqlStmt = SqlStmt.Replace("##person_table##", "");
                SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
                                          " AND pub_p_partner.p_partner_key_n = pub_pm_staff_data.p_partner_key_n");
            }

            // add address filter information to sql statement and parameter list
            AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);

            // now run the database query
            TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
            DataTable partnerkeys = ATransaction.DataBaseObj.SelectDT(SqlStmt, "partners", ATransaction,
                                                                      SqlParameterList.ToArray());

            // filter data by postcode (if applicable)
            PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);

            // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
            // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
            if (AParameters.Get("CancelReportCalculation").ToBool() == true)
            {
                return(false);
            }

            TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);

            // create an extract with the given name in the parameters
            int         keyCount;
            List <long> ignoredPartnerKeys = null;

            ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
                AParameters.Get("param_extract_name").ToString(),
                AParameters.Get("param_extract_description").ToString(),
                out AExtractId,
                partnerkeys,
                0,
                AddressFilterAdded,
                out keyCount,
                out ignoredPartnerKeys);

            return(ReturnValue);
        }
Beispiel #47
0
        /// <summary>
        /// Returns the current sequence value for the given Sequence from the DB.
        /// </summary>
        /// <param name="ASequenceName">Name of the Sequence.</param>
        /// <param name="ATransaction">An instantiated Transaction in which the Query
        /// to the DB will be enlisted.</param>
        /// <param name="ADatabase">the database object that can be used for querying</param>
        /// <returns>Sequence Value.</returns>
        public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
        {
            DataTable table = ADatabase.SelectDT(
                "SELECT PUB." + ASequenceName + ".CURRVAL FROM PUB.\"_Sequence\" WHERE \"_Seq-Name\" = \"" + ASequenceName + "\"",
                "sequence",
                ATransaction);

            if (table.Rows.Count > 0)
            {
                return Convert.ToInt64(table.Rows[0][0]);
            }

            return -1;
        }
Beispiel #48
0
        /// <summary>
        /// return a table with gift details for the given date with donor partner keys and bank account numbers
        /// </summary>
        private static bool GetGiftsByDate(Int32 ALedgerNumber,
                                           BankImportTDS AMainDS,
                                           DateTime ADateEffective,
                                           string ABankAccountCode,
                                           out List <int> AGiftBatchNumbers)
        {
            TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted);

            // first get all gifts, even those that have no bank account associated
            string stmt = TDataBase.ReadSqlFile("BankImport.GetDonationsByDate.sql");

            OdbcParameter[] parameters = new OdbcParameter[3];
            parameters[0]       = new OdbcParameter("ALedgerNumber", OdbcType.Int);
            parameters[0].Value = ALedgerNumber;
            parameters[1]       = new OdbcParameter("ADateEffective", OdbcType.Date);
            parameters[1].Value = ADateEffective;
            parameters[2]       = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
            parameters[2].Value = ABankAccountCode;

            DBAccess.GDBAccessObj.SelectDT(AMainDS.AGiftDetail, stmt, transaction, parameters, 0, 0);

            // calculate the totals of gifts
            AMainDS.AGift.Clear();

            AGiftBatchNumbers = new List <int>();

            foreach (BankImportTDSAGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
            {
                BankImportTDSAGiftRow giftRow =
                    (BankImportTDSAGiftRow)AMainDS.AGift.Rows.Find(new object[] { giftdetail.LedgerNumber, giftdetail.BatchNumber,
                                                                                  giftdetail.GiftTransactionNumber });

                if (giftRow == null)
                {
                    giftRow = AMainDS.AGift.NewRowTyped(true);
                    giftRow.LedgerNumber          = giftdetail.LedgerNumber;
                    giftRow.BatchNumber           = giftdetail.BatchNumber;
                    giftRow.GiftTransactionNumber = giftdetail.GiftTransactionNumber;
                    giftRow.TotalAmount           = 0;
                    giftRow.DonorKey = giftdetail.DonorKey;
                    AMainDS.AGift.Rows.Add(giftRow);
                }

                giftRow.TotalAmount += giftdetail.GiftTransactionAmount;

                if (!AGiftBatchNumbers.Contains(giftRow.BatchNumber))
                {
                    AGiftBatchNumbers.Add(giftRow.BatchNumber);
                }
            }

            // get PartnerKey and banking details (most important BankAccountNumber) for all donations on the given date
            stmt                = TDataBase.ReadSqlFile("BankImport.GetBankAccountByDate.sql");
            parameters          = new OdbcParameter[2];
            parameters[0]       = new OdbcParameter("LedgerNumber", OdbcType.Int);
            parameters[0].Value = ALedgerNumber;
            parameters[1]       = new OdbcParameter("ADateEffective", OdbcType.Date);
            parameters[1].Value = ADateEffective;
            // TODO ? parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
            //parameters[2].Value = ABankAccountCode;

            // There can be several donors with the same banking details
            AMainDS.PBankingDetails.Constraints.Clear();

            DBAccess.GDBAccessObj.Select(AMainDS, stmt, AMainDS.PBankingDetails.TableName, transaction, parameters);
            DBAccess.GDBAccessObj.RollbackTransaction();

            return(true);
        }
Beispiel #49
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();
            }
        }
Beispiel #50
0
        /// <summary>
        /// Populate ledger with gifts and invoices, post batches, close periods and years, according to FNumberOfClosedPeriods
        /// </summary>
        public static void PopulateData(string datadirectory, bool smallNumber = false)
        {
            int periodOverall = 0;
            int yearCounter   = 0;
            int period        = 1;
            int YearAD        = DateTime.Today.Year - (FNumberOfClosedPeriods / 12);

            SampleDataGiftBatches.FLedgerNumber     = FLedgerNumber;
            SampleDataAccountsPayable.FLedgerNumber = FLedgerNumber;
            SampleDataGiftBatches.LoadBatches(Path.Combine(datadirectory, "donations.csv"), smallNumber);
            SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD, smallNumber);

            TDataBase db = DBAccess.Connect("GenerateLedger");

            while (periodOverall <= FNumberOfClosedPeriods)
            {
                TLogging.LogAtLevel(1, "working on year " + yearCounter.ToString() + " / period " + period.ToString());

                SampleDataGiftBatches.CreateGiftBatches(period);

                SampleDataLedger.InitExchangeRate(db);

                if (!SampleDataGiftBatches.PostBatches(yearCounter, period, (periodOverall == FNumberOfClosedPeriods) ? 1 : 0, db))
                {
                    throw new Exception("could not post gift batches");
                }

                if (!SampleDataAccountsPayable.PostAndPayInvoices(yearCounter, period, (periodOverall == FNumberOfClosedPeriods) ? 1 : 0))
                {
                    throw new Exception("could not post invoices");
                }

                TLedgerInfo LedgerInfo = new TLedgerInfo(FLedgerNumber, db);

                if (periodOverall < FNumberOfClosedPeriods)
                {
                    TAccountPeriodInfo AccountingPeriodInfo =
                        new TAccountPeriodInfo(FLedgerNumber, period);
                    TLogging.Log("closing period at " + AccountingPeriodInfo.PeriodEndDate.ToShortDateString());

                    // run month end
                    TMonthEnd MonthEndOperator = new TMonthEnd(db, LedgerInfo);
                    MonthEndOperator.SetNextPeriod(null);


                    if (period == 12)
                    {
                        TDBTransaction transaction  = new TDBTransaction();
                        bool           SubmissionOK = false;

                        db.WriteTransaction(
                            ref transaction,
                            ref SubmissionOK,
                            delegate
                        {
                            TYearEnd YearEndOperator = new TYearEnd(LedgerInfo);
                            // run year end
                            TVerificationResultCollection verificationResult = new TVerificationResultCollection();
                            List <Int32> glBatches     = new List <int>();
                            TReallocation reallocation = new TReallocation(LedgerInfo, glBatches, transaction);
                            reallocation.VerificationResultCollection = verificationResult;
                            reallocation.IsInInfoMode = false;
                            reallocation.RunOperation();

                            TGlmNewYearInit glmNewYearInit = new TGlmNewYearInit(LedgerInfo, yearCounter, YearEndOperator, transaction);
                            glmNewYearInit.VerificationResultCollection = verificationResult;
                            glmNewYearInit.IsInInfoMode = false;
                            glmNewYearInit.RunOperation();
                            YearEndOperator.SetNextPeriod(transaction);

                            SampleDataLedger.InitExchangeRate(db);

                            YearAD++;
                            yearCounter++;
                            SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD, smallNumber, db);
                            period       = 0;
                            SubmissionOK = true;
                        });
                    }
                }

                period++;
                periodOverall++;
            }
        }
        /// <summary>
        /// Gets called in regular intervals from a Timer in Class TTimedProcessing.
        /// </summary>
        /// <param name="ADBAccessObj">Already instatiated DB Access object with opened DB connection.</param>
        /// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
        public static void Process(TDataBase ADBAccessObj, bool ARunManually)
        {
            TDBTransaction ReadWriteTransaction;
            bool NewTransaction;
            bool LastReminderDateAcquired;
            DateTime LastReminderDate;
            DataSet ReminderResultsDS;
            SSystemDefaultsRow SystemDefaultsDR;
            PPartnerReminderTable PartnerReminderDT;
            int ReminderFreqency;
            TDataBase DBAccessObj;

            if (TLogging.DebugLevel >= 6)
            {
                TLogging.Log("Entering TProcessPartnerReminders.Process...");
            }

            // TODO: it is quite ipossible at the moment to use ADBAccessObj instead of DBAccess.GDBAccessObj due to SubmitChanges etc
            //DBAccessObj = ADBAccessObj;
            DBAccessObj = DBAccess.GDBAccessObj;

            // SubmitChanges references a user
            TPetraIdentity PetraIdentity = new TPetraIdentity(
                "SYSADMIN", "", "", "", "", DateTime.MinValue,
                DateTime.MinValue, DateTime.MinValue, 0, -1, -1, false,
                false);

            UserInfo.GUserInfo = new TPetraPrincipal(PetraIdentity, null);

            ReadWriteTransaction = DBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
                out NewTransaction);

            /*
             * This whole process must either succeed or fail, therefore the whole thing is in a try-catch.
             */
            try
            {
                /*
                 * Obtain date when PartnerReminders last ran. This is stored in a SystemDefault. If it doesn't exist already,
                 * a new SystemDefault with an ancient date is created for us.
                 */
                LastReminderDateAcquired = GetLastReminderDate(out LastReminderDate, out SystemDefaultsDR, ReadWriteTransaction);

                if (!LastReminderDateAcquired)
                {
                    TLogging.Log(
                        TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                        ": Could not send Partner Reminders because Petra couldn't create the required SystemDefault setting for the Last Reminder Date!");

                    DBAccessObj.RollbackTransaction();

                    return;
                }

                TSmtpSender Sender = new TSmtpSender();

                if (!Sender.FInitOk)
                {
                    TLogging.Log(
                        TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                        ": Could not send Partner Reminders because SMTP server didn't initialise.");

                    DBAccessObj.RollbackTransaction();
                    return;
                }

                // Retrieve all PartnerReminders we need to process.
                ReminderResultsDS = GetRemindersToProcess(LastReminderDate, out PartnerReminderDT,
                    DBAccessObj, ReadWriteTransaction);

                /*
                 * We now have a Typed DataTable with the PartnerReminders that we need to process.
                 * Iterate through the PartnerReminders, update data, and send an email for each PartnerReminder.
                 */
                if (TLogging.DebugLevel >= 6)
                {
                    TLogging.Log("_---------------------------------_");
                    TLogging.Log("PartnerReminders data before we start processing all PartnerReminders....");
                    TLogging.Log(ReminderResultsDS.GetXml().ToString());
                }

                foreach (PPartnerReminderRow PartnerReminderDR in PartnerReminderDT.Rows)
                {
                    if (TLogging.DebugLevel >= 4)
                    {
                        TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                ": Processing Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
                    }

                    ReminderFreqency = (PartnerReminderDR.IsReminderFrequencyNull()) ? 0 : PartnerReminderDR.ReminderFrequency;

                    PartnerReminderDR.BeginEdit();
                    PartnerReminderDR.LastReminderSent = DateTime.Now.Date;
                    PartnerReminderDR.NextReminderDate = DateTime.Now.Date.AddDays(ReminderFreqency);

                    if (!PartnerReminderDR.IsEventDateNull())   // Reminder has an Event Date
                    {
                        if (PartnerReminderDR.NextReminderDate > PartnerReminderDR.EventDate)
                        {
                            if (TLogging.DebugLevel >= 5)
                            {
                                TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                        ": Deactivating Reminder ID {0} for Partner {1} as its Event Date is in the past.",
                                        PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
                            }

                            PartnerReminderDR.ReminderActive = false;
                        }
                    }

                    if (TLogging.DebugLevel >= 4)
                    {
                        TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                ": Sending email for Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
                    }

                    if (SendReminderEmail(PartnerReminderDR, ReadWriteTransaction, Sender))
                    {
                        // Accept the edit
                        if (TLogging.DebugLevel >= 4)
                        {
                            TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                    ": Reminder ID {0} for Partner {1} accepted by SMTP server.", PartnerReminderDR.ReminderId,
                                    PartnerReminderDR.PartnerKey));
                        }

                        PartnerReminderDR.EndEdit();
                    }
                    else
                    {
                        // Cancel the edit
                        TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
                                ": Reminder ID {0} for Partner {1} REJECTED by SMTP server.", PartnerReminderDR.ReminderId,
                                PartnerReminderDR.PartnerKey));

                        PartnerReminderDR.CancelEdit();
                    }
                }

                if (TLogging.DebugLevel >= 6)
                {
                    TLogging.Log("_---------------------------------_");
                    TLogging.Log("PartnerReminders data after processing all PartnerReminders, before writing it to DB....");
                    TLogging.Log(PartnerReminderDT.DataSet.GetXml().ToString());
                }

                // Update all the changed PartnerReminder Rows
                PPartnerReminderAccess.SubmitChanges(PartnerReminderDT, ReadWriteTransaction);

                if (TLogging.DebugLevel >= 6)
                {
                    TLogging.Log("_---------------------------------_");
                }

                /*
                 * Update the SystemDefault that keeps track of when Partner Reminders last ran.
                 * (SystemDefaultsDR will point to the row we loaded earlier on, OR the row we added earlier on
                 * if there wasn't already a SystemDefault row.)
                 */
                UpdateLastReminderDate(SystemDefaultsDR, ReadWriteTransaction);

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

                TLogging.LogAtLevel(1, TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " ran succesfully.");
            }
            catch (Exception Exc)
            {
                TLogging.Log(
                    TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " encountered an Exception:" + Environment.NewLine +
                    Exc.ToString());

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

                throw;
            }
        }
Beispiel #52
0
        /// <summary>
        /// Returns the next sequence value for the given Sequence from the DB.
        /// </summary>
        /// <param name="ASequenceName">Name of the Sequence.</param>
        /// <param name="ATransaction">An instantiated Transaction in which the Query
        /// to the DB will be enlisted.</param>
        /// <param name="ADatabase">Database object that can be used for querying.</param>
        /// <returns>Sequence Value.</returns>
        public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
        {
            string stmt = "INSERT INTO " + ASequenceName + " VALUES(NULL, -1);";

            using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
            {
                cmd.ExecuteNonQuery();
            }

            return(GetCurrentSequenceValue(ASequenceName, ATransaction, ADatabase));
        }
        /// <summary>
        /// todoComment
        /// </summary>
        /// <param name="databaseConnection">The database connection.</param>
        /// <param name="pv_ledger_number_i">The pv_ledger_number_i.</param>
        /// <param name="pv_year_i">The pv_year_i.</param>
        /// <param name="pv_period_i">The pv_period_i.</param>
        /// <param name="currentFinancialYear">The current financial year.</param>
        /// <returns></returns>
        public decimal GetCorporateExchangeRate(TDataBase databaseConnection,
            int pv_ledger_number_i,
            int pv_year_i,
            int pv_period_i,
            int currentFinancialYear)
        {
            if (pv_period_i == 0) // I sometimes get asked for this. There's no period 0.
            {
                //TODO: Calendar vs Financial Date Handling - Need to confirm this below
                pv_period_i = 12; // Perhaps I should look up this value from number of periods?
                pv_year_i--;
            }

            foreach (TExchangeRate exchangeRateElement in exchangeRates)
            {
                if ((exchangeRateElement.ledger_number_i == pv_ledger_number_i) && (exchangeRateElement.year_i == pv_year_i)
                    && (exchangeRateElement.period_i == pv_period_i))
                {
                    return exchangeRateElement.rate_n;
                }
            }

            decimal ReturnValue = GetCorporateExchangeRateFromDB(databaseConnection, pv_ledger_number_i, pv_year_i, pv_period_i, currentFinancialYear);
            //
            // Cache this for the next time I'm asked...
            TExchangeRate exchangeRateElement2 = new TExchangeRate();
            exchangeRateElement2.ledger_number_i = pv_ledger_number_i;
            exchangeRateElement2.year_i = pv_year_i;
            exchangeRateElement2.period_i = pv_period_i;
            exchangeRateElement2.rate_n = ReturnValue;
            exchangeRates.Add(exchangeRateElement2);
            return ReturnValue;
        }
Beispiel #54
0
        /// <summary>
        /// Returns the current sequence value for the given Sequence from the DB.
        /// </summary>
        /// <param name="ASequenceName">Name of the Sequence.</param>
        /// <param name="ATransaction">An instantiated Transaction in which the Query
        /// to the DB will be enlisted.</param>
        /// <param name="ADatabase">Database object that can be used for querying.</param>
        /// <returns>Sequence Value.</returns>
        public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
        {
            string stmt = "SELECT MAX(sequence) FROM " + ASequenceName + ";";

            using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
            {
                return(Convert.ToInt64(cmd.ExecuteScalar()));
            }
        }
Beispiel #55
0
        /// <summary>
        /// Returns the next sequence value for the given Sequence from the DB.
        /// </summary>
        /// <param name="ASequenceName">Name of the Sequence.</param>
        /// <param name="ATransaction">An instantiated Transaction in which the Query
        /// to the DB will be enlisted.</param>
        /// <param name="ADatabase">Database object that can be used for querying.</param>
        /// <returns>Sequence Value.</returns>
        public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
        {
            string stmt = "INSERT INTO " + ASequenceName + " VALUES(NULL, -1);";

            using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
            {
                cmd.ExecuteNonQuery();
            }

            return GetCurrentSequenceValue(ASequenceName, ATransaction, ADatabase);
        }
Beispiel #56
0
        public static void ExportGifts(int ALedgerNumber,
                                       string ACostCentre,
                                       string AAcctCode,
                                       string AMonthName,
                                       int APeriodNumber,
                                       DateTime APeriodStartDate,
                                       DateTime APeriodEndDate,
                                       string ACurrencySelect,
                                       int AIchNumber,
                                       ref DataTable AExportDataTable,
                                       ref TVerificationResultCollection AVerificationResult)
        {
            /* Define local variables */
            bool   FirstLoopFlag  = true;
            Int32  LastRecipKey   = 0;          //FORMAT "9999999999"
            string LastGroup      = string.Empty;
            string LastDetail     = string.Empty;
            string LastDetailDesc = string.Empty; //FORMAT "X(15)"
            // string CurrentYearTotals = string.Empty;
            decimal IndividualDebitTotal  = 0;    //FORMAT "->>>,>>>,>>>,>>9.99"
            decimal IndividualCreditTotal = 0;    //FORMAT "->>>,>>>,>>>,>>9.99"

            string ExportDescription = string.Empty;
            Int32  tmpLastRecipKey   = 0;
            string tmpLastGroup      = string.Empty;
            string tmpLastDetail     = string.Empty;

            //Export Gifts gi3200-1.i
            //Find and total each gift transaction
            string SQLStmt = TDataBase.ReadSqlFile("ICH.HOSAExportGiftsInner.sql");

            //Begin the transaction
            bool NewTransaction = false;

            TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);

            OdbcParameter parameter;

            List <OdbcParameter> parameters = new List <OdbcParameter>();

            parameter       = new OdbcParameter("LedgerNumber", OdbcType.Int);
            parameter.Value = ALedgerNumber;
            parameters.Add(parameter);
            parameter       = new OdbcParameter("CostCentre", OdbcType.VarChar);
            parameter.Value = ACostCentre;
            parameters.Add(parameter);
            parameter       = new OdbcParameter("ICHNumber", OdbcType.Int);
            parameter.Value = AIchNumber;
            parameters.Add(parameter);
            parameter       = new OdbcParameter("BatchStatus", OdbcType.VarChar);
            parameter.Value = MFinanceConstants.BATCH_POSTED;
            parameters.Add(parameter);
            parameter       = new OdbcParameter("StartDate", OdbcType.DateTime);
            parameter.Value = APeriodStartDate;
            parameters.Add(parameter);
            parameter       = new OdbcParameter("EndDate", OdbcType.DateTime);
            parameter.Value = APeriodEndDate;
            parameters.Add(parameter);
            parameter       = new OdbcParameter("AccountCode", OdbcType.VarChar);
            parameter.Value = AAcctCode;
            parameters.Add(parameter);

            DataTable TmpTable = DBAccess.GDBAccessObj.SelectDT(SQLStmt, "table", DBTransaction, parameters.ToArray());

            foreach (DataRow untypedTransRow in TmpTable.Rows)
            {
                /* Print totals etc. found for last recipient */
                /* Only do after first loop due to last recipient key check */

                tmpLastRecipKey = Convert.ToInt32(untypedTransRow[8]);  //a_gift_detail.p_recipient_key_n
                tmpLastGroup    = untypedTransRow[6].ToString();        //a_motivation_detail.a_motivation_group_code_c
                tmpLastDetail   = untypedTransRow[7].ToString();        //a_motivation_detail.a_motivation_detail_code_c

                if (!FirstLoopFlag &&
                    ((tmpLastRecipKey != LastRecipKey) ||
                     (tmpLastGroup != LastGroup) ||
                     (tmpLastDetail != LastDetail)
                    )
                    )
                {
                    if ((IndividualCreditTotal != 0) ||
                        (IndividualDebitTotal != 0))
                    {
                        if (LastRecipKey != 0)
                        {
                            /* Find partner short name details */
                            PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LastRecipKey, DBTransaction);
                            PPartnerRow   PartnerRow   = (PPartnerRow)PartnerTable.Rows[0];

                            LastDetailDesc += " : " + PartnerRow.PartnerShortName;

                            ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" + LastDetailDesc;
                        }
                        else
                        {
                            AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadByPrimaryKey(ALedgerNumber,
                                                                                                                 LastGroup,
                                                                                                                 DBTransaction);
                            AMotivationGroupRow MotivationGroupRow = (AMotivationGroupRow)MotivationGroupTable.Rows[0];

                            ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" +
                                                MotivationGroupRow.MotivationGroupDescription.TrimEnd(new Char[] { (' ') }) + "," + LastDetailDesc;
                        }

                        //Add data to export table
                        DataRow DR = (DataRow)AExportDataTable.NewRow();

                        DR[0] = ACostCentre;
                        DR[1] = ConvertAccount(AAcctCode);
                        DR[2] = ExportDescription;
                        DR[3] = "ICH-" + APeriodNumber.ToString("00");
                        DR[4] = APeriodEndDate;
                        DR[5] = IndividualDebitTotal;
                        DR[6] = IndividualCreditTotal;

                        AExportDataTable.Rows.Add(DR);

                        /* Reset total */
                        IndividualDebitTotal  = 0;
                        IndividualCreditTotal = 0;
                    }
                }

                if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
                {
                    Decimal GiftAmount = Convert.ToDecimal(untypedTransRow[4]);          //a_gift_detail.a_gift_amount_n

                    if (GiftAmount < 0)
                    {
                        IndividualDebitTotal -= GiftAmount;
                    }
                    else
                    {
                        IndividualCreditTotal += GiftAmount;
                    }
                }
                else
                {
                    Decimal IntlGiftAmount = Convert.ToDecimal(untypedTransRow[5]);          //a_gift_detail.a_gift_amount_intl_n

                    if (IntlGiftAmount < 0)
                    {
                        IndividualDebitTotal -= IntlGiftAmount;
                    }
                    else
                    {
                        IndividualCreditTotal += IntlGiftAmount;
                    }
                }

                /* Set loop variables */
                LastRecipKey   = tmpLastRecipKey;
                LastGroup      = tmpLastGroup;
                LastDetail     = tmpLastDetail;
                LastDetailDesc = Convert.ToString(untypedTransRow[10]);         //a_motivation_detail.a_motivation_detail_desc_c
                FirstLoopFlag  = false;
            } // foreach

            /* Print totals etc. found for last recipient */
            /* Only do after first loop due to last recipient key check */
            if (!FirstLoopFlag && ((IndividualCreditTotal != 0) || (IndividualDebitTotal != 0)))
            {
                if (LastRecipKey != 0)
                {
                    /* Find partner short name details */
                    PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LastRecipKey, DBTransaction);
                    PPartnerRow   PartnerRow   = (PPartnerRow)PartnerTable.Rows[0];

                    LastDetailDesc += ":" + PartnerRow.PartnerShortName;

                    ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" + LastDetailDesc;
                }
                else
                {
                    AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadByPrimaryKey(ALedgerNumber, LastGroup, DBTransaction);
                    AMotivationGroupRow   MotivationGroupRow   = (AMotivationGroupRow)MotivationGroupTable.Rows[0];


                    ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" +
                                        MotivationGroupRow.MotivationGroupDescription.TrimEnd() + "," + LastDetailDesc;
                }

                //Add rows to export table
                DataRow DR = (DataRow)AExportDataTable.NewRow();

                DR[0] = ACostCentre;
                DR[1] = ConvertAccount(AAcctCode);
                DR[2] = ExportDescription;
                DR[3] = "ICH-" + APeriodNumber.ToString("00");
                DR[4] = APeriodEndDate;;
                DR[5] = IndividualDebitTotal;
                DR[6] = IndividualCreditTotal;

                AExportDataTable.Rows.Add(DR);
            }

            if (NewTransaction)
            {
                DBAccess.GDBAccessObj.RollbackTransaction();
            }
        }
Beispiel #57
0
 /// <summary>
 /// Restart a sequence with the given value.
 /// </summary>
 public void RestartSequence(String ASequenceName,
     TDBTransaction ATransaction,
     TDataBase ADatabase,
     Int64 ARestartValue)
 {
     ADatabase.ExecuteNonQuery("DELETE FROM " + ASequenceName + ";", ATransaction);
     ADatabase.ExecuteNonQuery("INSERT INTO " + ASequenceName + " VALUES(" + ARestartValue.ToString() + ", -1);", ATransaction);
 }
Beispiel #58
0
        public static bool GenerateHOSAFiles(int ALedgerNumber,
                                             int APeriodNumber,
                                             int AIchNumber,
                                             string ACostCentre,
                                             String ACurrencySelect,
                                             string AFileName,
                                             out TVerificationResultCollection AVerificationResult
                                             )
        {
            bool Successful = false;

            AVerificationResult = new TVerificationResultCollection();

            //Begin the transaction
            bool NewTransaction = false;

            TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);

            try
            {
                GLBatchTDS MainDS = new GLBatchTDS();

                //Load tables needed: AccountingPeriod, Ledger, Account, Cost Centre, Transaction, Gift Batch, ICHStewardship
                ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, DBTransaction);

                /* Retrieve info on the ledger. */
                ALedgerRow LedgerRow = (ALedgerRow)MainDS.ALedger.Rows[0];
                String     Currency  = (ACurrencySelect == MFinanceConstants.CURRENCY_BASE) ? LedgerRow.BaseCurrency : LedgerRow.IntlCurrency;

/*              String StoreNumericFormat = "#" + CultureInfo.CurrentCulture.NumberFormat.NumberGroupSeparator + "##0";
 *
 *              if (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits > 0)
 *              {
 *                  string DecPls = new String('0', CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits);
 *                  StoreNumericFormat += CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator + DecPls;
 *              }
 */
                AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, DBTransaction);
                AAccountingPeriodRow   AccountingPeriodRow   = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
                String MonthName = AccountingPeriodRow.AccountingPeriodDesc;

                //Create table definitions
                DataTable TableForExport = new DataTable();
                TableForExport.Columns.Add("CostCentre", typeof(string));
                TableForExport.Columns.Add("Account", typeof(string));
                TableForExport.Columns.Add("LedgerMonth", typeof(string));
                TableForExport.Columns.Add("ICHPeriod", typeof(string));
                TableForExport.Columns.Add("Date", typeof(DateTime));
                TableForExport.Columns.Add("IndividualDebitTotal", typeof(decimal));
                TableForExport.Columns.Add("IndividualCreditTotal", typeof(decimal));

                string TableForExportHeader = "/** Header **" + "," +
                                              APeriodNumber.ToString() + "," +
                                              TLedgerInfo.GetStandardCostCentre(ALedgerNumber) + "," +
                                              ACostCentre + "," +
                                              DateTime.Today.ToShortDateString() + "," +
                                              Currency;

                //See gi3200.p ln: 170
                //Select any gift transactions to export
                string strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGifts.sql");

                OdbcParameter parameter;

                List <OdbcParameter> parameters = new List <OdbcParameter>();
                parameter       = new OdbcParameter("LedgerNumber", OdbcType.Int);
                parameter.Value = ALedgerNumber;
                parameters.Add(parameter);
                parameter       = new OdbcParameter("Year", OdbcType.Int);
                parameter.Value = LedgerRow.CurrentFinancialYear;
                parameters.Add(parameter);
                parameter       = new OdbcParameter("CostCentre", OdbcType.VarChar);
                parameter.Value = ACostCentre;
                parameters.Add(parameter);

                DataTable TmpTable = DBAccess.GDBAccessObj.SelectDT(strSql, "table", DBTransaction, parameters.ToArray());

                foreach (DataRow untypedTransRow in TmpTable.Rows)
                {
                    string gLMAcctCode  = untypedTransRow[3].ToString();
                    string gLMCostCCode = untypedTransRow[4].ToString();
                    string gLMAcctType  = untypedTransRow[5].ToString();

                    if (gLMAcctType == MFinanceConstants.ACCOUNT_TYPE_INCOME)     //a_account.a_account_type_c
                    {
                        DateTime PeriodStartDate = AccountingPeriodRow.PeriodStartDate;
                        DateTime PeriodEndDate   = AccountingPeriodRow.PeriodEndDate;

                        /*RUN Export_gifts(INPUT pv_ledger_number_i...*/

                        //gi3200-1.i
                        ExportGifts(ALedgerNumber,
                                    ACostCentre,
                                    gLMAcctCode,
                                    MonthName,
                                    APeriodNumber,
                                    PeriodStartDate,
                                    PeriodEndDate,
                                    ACurrencySelect,
                                    AIchNumber,
                                    ref TableForExport,
                                    ref AVerificationResult);
                    }

                    /* Then see if there are any GL transactions to export */
                    //gi3200.i ln:33

                    /*
                     * This scheme with ODBC parameters consistently causes an "input string is the wrong type" eror:
                     *
                     * strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGLTrans.sql");
                     * OdbcParameter[] SqlParams = new OdbcParameter[] {
                     *      new OdbcParameter("LedgerNumber", (Int32)ALedgerNumber),
                     *      new OdbcParameter("Account", (String)gLMAcctCode),
                     *      new OdbcParameter("CostCentre", (String)gLMCostCCode),
                     *      new OdbcParameter("Narrative", (String)MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION),
                     *      new OdbcParameter("ICHNumber", (Int32)AIchNumber),
                     *      new OdbcParameter("ICHNumber2", (Int32)AIchNumber),
                     *      new OdbcParameter("PeriodNumber", (Int32)APeriodNumber)
                     *  };
                     * DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction, SqlParams);
                     */

                    strSql = "SELECT Trans.a_ledger_number_i, Trans.a_batch_number_i, Trans.a_journal_number_i, Trans.a_transaction_number_i, " +
                             "Trans.a_account_code_c, Trans.a_cost_centre_code_c, Trans.a_transaction_date_d, Trans.a_transaction_amount_n, " +
                             "Trans.a_amount_in_base_currency_n, Trans.a_amount_in_intl_currency_n, Trans.a_ich_number_i, Trans.a_system_generated_l, "
                             +
                             "Trans.a_narrative_c, Trans.a_debit_credit_indicator_l  FROM public.a_transaction AS Trans, public.a_journal AS Journal "
                             +
                             "WHERE Trans.a_ledger_number_i = Journal.a_ledger_number_i AND Trans.a_batch_number_i = Journal.a_batch_number_i " +
                             "AND Trans.a_journal_number_i = Journal.a_journal_number_i " +
                             String.Format(
                        "AND Trans.a_ledger_number_i = {0} AND Trans.a_account_code_c = '{1}' AND Trans.a_cost_centre_code_c = '{2}' " +
                        "AND Trans.a_transaction_status_l = true AND NOT (Trans.a_narrative_c LIKE '{3}%' AND Trans.a_system_generated_l = true) " +
                        "AND ((Trans.a_ich_number_i + {4}) = Trans.a_ich_number_i OR Trans.a_ich_number_i = {4}) " +
                        "AND Journal.a_journal_period_i = {5};",
                        ALedgerNumber,
                        gLMAcctCode,
                        gLMCostCCode,
                        MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION,
                        AIchNumber,
                        APeriodNumber
                        );

                    DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction);

                    foreach (DataRow untypedTransactRow in TmpTransTable.Rows)
                    {
                        Decimal DebitTotal  = 0;
                        Decimal CreditTotal = 0;

                        bool     Debit           = Convert.ToBoolean(untypedTransactRow[13]); //a_transaction.a_debit_credit_indicator_l
                        bool     SystemGenerated = Convert.ToBoolean(untypedTransactRow[11]); //a_transaction.a_system_generated_l
                        string   Narrative       = untypedTransactRow[12].ToString();         //a_transaction.a_narrative_c
                        DateTime TransactionDate = Convert.ToDateTime(untypedTransactRow[6]); //a_transaction.a_transaction_date_d

                        if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
                        {
                            decimal AmountInBaseCurrency = Convert.ToDecimal(untypedTransactRow[8]);  //a_transaction.a_amount_in_base_currency_n

                            /* find transaction amount and store as debit or credit */
                            if (Debit)
                            {
                                DebitTotal += AmountInBaseCurrency;
                            }
                            else
                            {
                                CreditTotal += AmountInBaseCurrency;
                            }
                        }
                        else
                        {
                            decimal AmountInIntlCurrency = Convert.ToDecimal(untypedTransactRow[9]);   //a_transaction.a_amount_in_intl_currency_n

                            if (Debit)
                            {
                                DebitTotal += AmountInIntlCurrency;
                            }
                            else
                            {
                                CreditTotal += AmountInIntlCurrency;
                            }
                        }

                        TLogging.LogAtLevel(4, "HOSA-Narrative: " + Narrative);

                        //Check for specific narrative strings
                        bool IsNarrativeGBGiftBatch             = false;
                        int  LenNarrativeGBGiftBatch            = MFinanceConstants.NARRATIVE_GB_GIFT_BATCH.Length;
                        bool IsNarrativeGiftsReceivedGiftBatch  = false;
                        int  LenNarrativeGiftsReceivedGiftBatch = MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH.Length;

                        if (Narrative.Length >= LenNarrativeGiftsReceivedGiftBatch)
                        {
                            IsNarrativeGiftsReceivedGiftBatch =
                                (Narrative.Substring(0, LenNarrativeGiftsReceivedGiftBatch) == MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH);
                        }

                        if (Narrative.Length >= LenNarrativeGBGiftBatch)
                        {
                            IsNarrativeGBGiftBatch = (Narrative.Substring(0, LenNarrativeGBGiftBatch) == MFinanceConstants.NARRATIVE_GB_GIFT_BATCH);
                        }

                        if ((gLMAcctType.ToUpper() != MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) ||
                            !(SystemGenerated && (IsNarrativeGBGiftBatch || IsNarrativeGiftsReceivedGiftBatch)))
                        {
                            // Put transaction information
                            DataRow DR = (DataRow)TableForExport.NewRow();

                            DR[0] = gLMCostCCode;
                            DR[1] = ConvertAccount(gLMAcctCode);
                            DR[2] = ALedgerNumber.ToString() + MonthName + ":" + Narrative;
                            DR[3] = "ICH-" + APeriodNumber.ToString("00");
                            DR[4] = TransactionDate;
                            DR[5] = DebitTotal;
                            DR[6] = CreditTotal;

                            TableForExport.Rows.Add(DR);
                        }
                    }
                }

                TableForExport.AcceptChanges();

                TLogging.LogAtLevel(4, "HOSA-TableForExport: " + TableForExport.Rows.Count.ToString());

                //DataTables to XML to CSV
                XmlDocument doc = TDataBase.DataTableToXml(TableForExport);

                TCsv2Xml.Xml2Csv(doc, AFileName);

                //Replace the default CSV header row with OM specific
                ReplaceHeaderInFile(AFileName, TableForExportHeader, ref AVerificationResult);

                /* Change number format back */
                //TODO

                Successful = true;
            }
            catch (Exception e)
            {
                TLogging.Log(e.ToString());
            }

            // rollback the reading transaction
            if (NewTransaction)
            {
                DBAccess.GDBAccessObj.RollbackTransaction();
            }

            return(Successful);
        }
Beispiel #59
0
 /// <summary>
 /// manage database connections for the ASP webclient
 /// </summary>
 /// <param name="ADatabaseConnection"></param>
 public void AddDBConnection(TDataBase ADatabaseConnection)
 {
     if (!FDBConnections.Contains(ADatabaseConnection))
     {
         FDBConnections.Add(ADatabaseConnection);
     }
 }
Beispiel #60
0
 /// <summary>
 /// constructor
 /// </summary>
 /// <param name="databaseConnection"></param>
 /// <param name="period"></param>
 /// <param name="year"></param>
 /// <param name="AFinancialPeriod"></param>
 public TFinancialPeriod(TDataBase databaseConnection, int period, int year, TFinancialPeriod AFinancialPeriod) :
     this(databaseConnection, period, year, AFinancialPeriod.diffPeriod, AFinancialPeriod.FCurrentFinancialYear,
          AFinancialPeriod.FCurrentPeriod, AFinancialPeriod.FNumberAccountingPeriods, AFinancialPeriod.FNumberForwardingPeriods,
          AFinancialPeriod.realGlmSequence)
 {
 }