/// <summary> /// this method will try to create a gift for a non existing gift batch /// </summary> private void EnforceForeignKeyConstraint() { TDBTransaction t = new TDBTransaction(); bool SubmissionOK = true; TDataBase db = DBAccess.Connect("Test", FDataBase); string sql = "INSERT INTO a_gift(a_ledger_number_i, a_batch_number_i, a_gift_transaction_number_i) " + "VALUES(43, 99999999, 1)"; db.WriteTransaction(ref t, ref SubmissionOK, delegate { db.ExecuteNonQuery(sql, t); }); // UNDO the test t = new TDBTransaction(); SubmissionOK = true; sql = "DELETE FROM a_gift" + " WHERE a_ledger_number_i = 43 AND a_batch_number_i = 99999999 AND a_gift_transaction_number_i = 1"; db.WriteTransaction(ref t, ref SubmissionOK, delegate { db.ExecuteNonQuery(sql, t); }); }
/// Upgrade to version 2021-01 public static bool UpgradeDatabase202012_202101(TDataBase ADataBase) { // there are no changes to the database structure // but there is data we might need to add TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; string sql = String.Empty; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { sql = "SELECT COUNT(*) FROM PUB_p_banking_type"; if (Convert.ToInt32(ADataBase.ExecuteScalar(sql, SubmitChangesTransaction)) == 0) { sql = "INSERT INTO PUB_p_banking_type( p_id_i, p_type_c) VALUES(0, 'BANK ACCOUNT')"; ADataBase.ExecuteNonQuery(sql, SubmitChangesTransaction); } sql = "SELECT COUNT(*) FROM PUB_p_banking_details_usage_type"; if (Convert.ToInt32(ADataBase.ExecuteScalar(sql, SubmitChangesTransaction)) == 0) { sql = "INSERT INTO PUB_p_banking_details_usage_type(p_type_c, p_type_description_c) VALUES ('MAIN','The default banking detail that should be used for this partner')"; ADataBase.ExecuteNonQuery(sql, SubmitChangesTransaction); } SubmitOK = true; }); return(true); }
/// <summary> /// this method will try to create a gift for a new gift batch before creating the gift batch. /// if the constraints would be checked only when committing the transaction, everything would be fine. /// but usually you get a violation of foreign key constraint a_gift_fk1 /// </summary> private void WrongOrderSqlStatements() { TDBTransaction t = new TDBTransaction(); bool SubmissionOK = true; TDataBase db = DBAccess.Connect("Test", FDataBase); // setup test scenario: a gift batch, with 2 gifts, each with 2 gift details db.WriteTransaction(ref t, ref SubmissionOK, delegate { string sql = "INSERT INTO a_gift(a_ledger_number_i, a_batch_number_i, a_gift_transaction_number_i) " + "VALUES(43, 99999999, 1)"; db.ExecuteNonQuery(sql, t); sql = "INSERT INTO a_gift_batch(a_ledger_number_i, a_batch_number_i, a_batch_description_c, a_bank_account_code_c, a_batch_year_i, a_currency_code_c, a_bank_cost_centre_c) " + "VALUES(43, 99999999, 'Test', '6000', 1, 'EUR', '4300')"; db.ExecuteNonQuery(sql, t); }); // UNDO the test t = null; SubmissionOK = true; db.WriteTransaction(ref t, ref SubmissionOK, delegate { string sql = "DELETE FROM a_gift" + " WHERE a_ledger_number_i = 43 AND a_batch_number_i = 99999999 AND a_gift_transaction_number_i = 1"; db.ExecuteNonQuery(sql, t); sql = "DELETE FROM a_gift_batch" + " WHERE a_ledger_number_i = 43 AND a_batch_number_i = 99999999"; db.ExecuteNonQuery(sql, t); }); }
public void TestInsertMultipleRows() { TDBTransaction t = new TDBTransaction(); bool SubmissionOK = true; string sql; TDataBase db = DBAccess.Connect("TestInsertMultipleRows", FDataBase); db.WriteTransaction( ref t, ref SubmissionOK, delegate { sql = "INSERT INTO a_gift_batch(a_ledger_number_i, a_batch_number_i, a_batch_description_c, a_bank_account_code_c, a_batch_year_i, a_currency_code_c, a_bank_cost_centre_c, a_gl_effective_date_d) " + "VALUES (43, 990, 'TEST', '6000', 1, 'EUR', '4300', NOW()),(43, 991, 'TEST', '6000', 1, 'EUR', '4300', NOW())"; db.ExecuteNonQuery(sql, t); }); // UNDO the test t = new TDBTransaction(); SubmissionOK = true; db.WriteTransaction(ref t, ref SubmissionOK, delegate { sql = "DELETE FROM a_gift_batch" + " WHERE a_ledger_number_i = 43 AND (a_batch_number_i = 990 or a_batch_number_i = 991)"; db.ExecuteNonQuery(sql, t); }); }
/// <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); }
public void Unsuspense() { // The equivalent try/catch block that is used for Suspense() was removed 27 Jan 2015 in order to fix // the issue in Mantis #3730 OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[2]; ParametersArray[0] = new OdbcParameter("", OdbcType.Int); ParametersArray[0].Value = ledgerNumber; ParametersArray[1] = new OdbcParameter("", OdbcType.VarChar); ParametersArray[1].Value = strAcount; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("Unsuspense"); bool SubmissionOK = true; db.WriteTransaction(ref transaction, ref SubmissionOK, delegate { string strSQL = "DELETE FROM PUB_" + ASuspenseAccountTable.GetTableDBName() + " "; strSQL += "WHERE " + ASuspenseAccountTable.GetLedgerNumberDBName() + " = ? "; strSQL += "AND " + ASuspenseAccountTable.GetSuspenseAccountCodeDBName() + " = ? "; db.ExecuteNonQuery(strSQL, transaction, ParametersArray); }); }
/// <summary> /// load data from a CSV file in Postgresql COPY format /// </summary> static private bool LoadData(TDataBase DBAccessObj, TDataDefinitionStore ADataDefinition, string APath, string ATablename) { TTable table = ADataDefinition.GetTable(ATablename); // prepare the statement string sqlStmt = table.PrepareSQLInsertStatement(); // load the data from the text file string filename = APath + Path.DirectorySeparatorChar + ATablename + ".csv"; if (File.Exists(filename + ".local")) { filename += ".local"; } StreamReader reader = new StreamReader(filename); string line; while ((line = reader.ReadLine()) != null) { List <OdbcParameter> Parameters = table.PrepareParametersInsertStatement(line); if (DBAccessObj.ExecuteNonQuery(sqlStmt, DBAccessObj.Transaction, Parameters.ToArray()) == 0) { throw new Exception("failed to import line for table " + ATablename); } } return(true); }
/// Upgrade to version 2020-06 public static bool UpgradeDatabase202004_202006(TDataBase ADataBase) { // add new tables for data consent, and some values // add permission for p_country TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade202004_202006.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
/// Upgrade to version 2018-09 public static bool UpgradeDatabase201808_201809() { // there are some changes to the database structure TDataBase db = DBAccess.Connect("TDBUpgrade"); TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; db.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade201808_201809.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { db.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
/// Upgrade to version 2019-10 public static bool UpgradeDatabase201909_201910(TDataBase ADataBase) { // new module PARTNERSELFSERVICE and new user SELFSERVICE TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade201909_201910.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
public void Suspense() { try { OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[2]; ParametersArray[0] = new OdbcParameter("", OdbcType.Int); ParametersArray[0].Value = ledgerNumber; ParametersArray[1] = new OdbcParameter("", OdbcType.VarChar); ParametersArray[1].Value = strAcount; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("Suspense"); bool SubmissionOK = true; db.WriteTransaction(ref transaction, ref SubmissionOK, delegate { string strSQL = "INSERT INTO PUB_" + ASuspenseAccountTable.GetTableDBName() + " "; strSQL += "(" + ASuspenseAccountTable.GetLedgerNumberDBName(); strSQL += "," + ASuspenseAccountTable.GetSuspenseAccountCodeDBName() + ") "; strSQL += "VALUES ( ? , ? )"; db.ExecuteNonQuery(strSQL, transaction, ParametersArray); }); } catch (Exception) { Assert.Fail("No database access to run the test"); } }
/// <summary> /// load test data written in PostgreSQL syntax into MySQL /// </summary> private static void LoadTestDataMySQL(string fileName) { // parse the sql file, and for each table (are there multiple???) load the data string sqlfileContent = LoadCSVFileToString(fileName); string[] lines = sqlfileContent.Split(new char[] { '\n' }); string currenttable = string.Empty; string sqlStmt = string.Empty; TDataDefinitionParser parser = new TDataDefinitionParser(rootPath + Path.DirectorySeparatorChar + "db" + Path.DirectorySeparatorChar + "petra.xml"); TDataDefinitionStore store = new TDataDefinitionStore(); TTable table = null; if (!parser.ParseDocument(ref store)) { throw new Exception("failed to parse petra.xml"); } TDBTransaction LoadTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadTestDataMySQL"); bool SubmissionOK = false; db.WriteTransaction(ref LoadTransaction, ref SubmissionOK, delegate { foreach (string line in lines) { if (line.StartsWith("--") || (line.Trim() == String.Empty)) { continue; } else if (line.StartsWith("COPY ")) { currenttable = line.Substring("COPY ".Length, line.IndexOf("(") - "COPY ".Length - 1); table = store.GetTable(currenttable); sqlStmt = table.PrepareSQLInsertStatement(); } else if (line == "\\.") { currenttable = String.Empty; } else if (currenttable != String.Empty) { List <OdbcParameter> Parameters = table.PrepareParametersInsertStatement(line); if (db.ExecuteNonQuery(sqlStmt, LoadTransaction, Parameters.ToArray()) == 0) { throw new Exception("failed to import line for table " + currenttable); } } } SubmissionOK = true; }); }
/// <summary> /// prepare the test case /// </summary> public static bool ImportAndPostGiftBatch(int ALedgerNumber, out TVerificationResultCollection VerificationResult) { TGiftImporting importer = new TGiftImporting(); string testFile = TAppSettingsManager.GetValue("GiftBatch.file", "../../csharp/ICT/Testing/lib/MFinance/SampleData/sampleGiftBatch.csv"); StreamReader sr = new StreamReader(testFile); string FileContent = sr.ReadToEnd(); FileContent = FileContent.Replace("{ledgernumber}", ALedgerNumber.ToString()); FileContent = FileContent.Replace("{thisyear}", DateTime.Today.Year.ToString()); sr.Close(); Hashtable parameters = new Hashtable(); parameters.Add("Delimiter", ","); parameters.Add("ALedgerNumber", ALedgerNumber); parameters.Add("DateFormatString", "yyyy-MM-dd"); parameters.Add("DatesMayBeIntegers", false); parameters.Add("NumberFormat", "American"); parameters.Add("NewLine", Environment.NewLine); GiftBatchTDSAGiftDetailTable NeedRecipientLedgerNumber; bool refreshRequired; if (!importer.ImportGiftBatches(parameters, FileContent, out NeedRecipientLedgerNumber, out refreshRequired, out VerificationResult)) { return(false); } int BatchNumber = importer.GetLastGiftBatchNumber(); Int32 generatedGlBatchNumber; if (!TGiftTransactionWebConnector.PostGiftBatch(ALedgerNumber, BatchNumber, out generatedGlBatchNumber, out VerificationResult)) { CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult); return(false); } TDataBase db = DBAccess.Connect("FixSendMailPartnerLocation"); TDBTransaction t = new TDBTransaction(); bool SubmissionOK = false; db.WriteTransaction(ref t, ref SubmissionOK, delegate { // need to set sendmail = true for the donor with partner key 43005001 string sql = "UPDATE p_partner_location SET p_send_mail_l = true WHERE p_partner_key_n = 43005001"; db.ExecuteNonQuery(sql, t); SubmissionOK = true; }); return(true); }
/// <summary> /// load test data written in PostgreSQL syntax into MySQL /// </summary> private static void LoadTestDataMySQL(string fileName) { // parse the sql file, and for each table (are there multiple???) load the data // with command: load data infile '/tmp/mysqltsv' into table tblname string sqlfileContent = LoadCSVFileToString(fileName); string[] lines = sqlfileContent.Split(new char[] { '\n' }); string currenttable = string.Empty; string tempfile = string.Empty; StreamWriter sw = null; foreach (string line in lines) { if (line.StartsWith("--") || (line.Trim() == String.Empty)) { continue; } else if (line.StartsWith("COPY ")) { currenttable = line.Substring("COPY ".Length, line.IndexOf("(") - "COPY ".Length - 1); tempfile = Path.GetTempFileName(); sw = new StreamWriter(tempfile); } else if (line == "\\.") { sw.Close(); TDBTransaction LoadTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadTestDataMySQL"); bool SubmissionOK = false; db.WriteTransaction(ref LoadTransaction, ref SubmissionOK, delegate { db.ExecuteNonQuery("LOAD DATA LOCAL INFILE '" + tempfile + "' INTO TABLE " + currenttable, LoadTransaction); SubmissionOK = true; }); currenttable = String.Empty; File.Delete(tempfile); } else if (currenttable != String.Empty) { string convertedLine = line; convertedLine = convertedLine.Replace("\tt\t", "\t1\t").Replace("\tf\t", "\t0\t"); sw.WriteLine(convertedLine); } } }
public void TestTimeStamp() { TDBTransaction t = new TDBTransaction(); TDataBase db = DBAccess.Connect("Test", FDataBase); bool SubmissionOK = true; db.WriteTransaction(ref t, ref SubmissionOK, delegate { string countSql = "SELECT COUNT(*) FROM PUB_s_system_defaults"; int count = Convert.ToInt32(db.ExecuteScalar(countSql, t)); string code = "test" + (count + 1).ToString(); string insertSql = String.Format( "INSERT INTO PUB_s_system_defaults(s_default_code_c, s_default_description_c, s_default_value_c, s_modification_id_t) VALUES('{0}', '{1}','{2}',NOW())", code, "test", "test"); Assert.AreEqual(1, db.ExecuteNonQuery(insertSql, t)); string getTimeStampSql = String.Format( "SELECT s_modification_id_t FROM PUB_s_system_defaults WHERE s_default_code_c = '{0}'", code); DateTime timestamp = Convert.ToDateTime(db.ExecuteScalar(getTimeStampSql, t)); string updateSql = String.Format( "UPDATE PUB_s_system_defaults set s_modification_id_t = NOW(), s_default_description_c = '{0}' where s_default_code_c = '{1}' AND s_modification_id_t = ?", "test2", code); OdbcParameter param = new OdbcParameter("timestamp", OdbcType.DateTime); param.Value = timestamp; Assert.AreEqual(1, db.ExecuteNonQuery(updateSql, t, new OdbcParameter[] { param }), "update by timestamp"); }); }
/// <summary> /// set current database version /// </summary> private static bool SetCurrentDBVersion(TFileVersionInfo ANewVersion, TDataBase ADataBase) { TDBTransaction transaction = new TDBTransaction(); bool SubmitOK = true; ADataBase.WriteTransaction(ref transaction, ref SubmitOK, delegate { string newVersionSql = String.Format("UPDATE s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion';", ANewVersion.ToStringDotsHyphen()); ADataBase.ExecuteNonQuery(newVersionSql, transaction); }); return(true); }
/// Upgrade to version 2019-12 public static bool UpgradeDatabase201911_201912(TDataBase ADataBase) { TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { // fix for motivation details, new details have not been activated string stmt = "update a_motivation_detail set a_motivation_status_l = 1"; ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); SubmitOK = true; }); return(true); }
private static void RemoveSession() { TDataBase db = ConnectDB("RemoveSession"); TDBTransaction t = new TDBTransaction(); bool SubmissionOK = false; db.WriteTransaction(ref t, ref SubmissionOK, delegate { OdbcParameter[] parameters = new OdbcParameter[1]; parameters[0] = new OdbcParameter("s_session_id_c", OdbcType.VarChar); parameters[0].Value = FSessionID; string sql = "DELETE FROM s_session WHERE s_session_id_c = ?"; db.ExecuteNonQuery(sql, t, parameters); SubmissionOK = true; }); db.CloseDBConnection(); }
private void UnloadTestData_GetBatchInfo() { OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[1]; ParametersArray[0] = new OdbcParameter("", OdbcType.VarChar); ParametersArray[0].Value = strTestDataBatchDescription; TDBTransaction transaction = new TDBTransaction(); bool SubmissionOK = true; TDataBase db = DBAccess.Connect("UnloadTestData_GetBatchInfo"); db.WriteTransaction(ref transaction, ref SubmissionOK, delegate { string strSQL = "DELETE FROM PUB_" + ABatchTable.GetTableDBName() + " "; strSQL += "WHERE " + ABatchTable.GetBatchDescriptionDBName() + " = ? "; db.ExecuteNonQuery( strSQL, transaction, ParametersArray); }); db.CloseDBConnection(); }
/// Upgrade to version 2020-04 public static bool UpgradeDatabase202003_202004(TDataBase ADataBase) { // add new types for sponsorship TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade202003_202004.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
/// <summary> /// This method is called when clients access the Daily Exchange Rate data. /// The Daily Exchange Rate table is unusual in that it doesn't really need to hold any data because the DataSet that the client receives /// contains all the used rates from the GL/Gift tables whether or not those rates are in the DER table itself. Any rates in the DER table /// that are NOT used are also returned, but, of course, because they are not used anywhere they are not very inetresting! /// Additionally, because the GL/Gift tables do not necessarily hold a time or a time that matches the same rate in the DER table, it is possible /// for the DER table to have a rate that is used on the date but at a different time. As a result the client sometimes does not see all rows /// from the DER table - and so has no way of deleting them. /// /// That is the reason why we need to automatically clean the table. /// /// But there is some value in having some 'unused' rows that are work-in-progress. So we delete everything in the DER table that /// applies to dates older than 30 days. In the future this might become a configurable server option. /// </summary> private static void DoDailyExchangeRateClean() { DateTime PreviousDailyExchangeRateAccessTime = DateTime.UtcNow.AddHours(-24); if (TSession.HasVariable("PreviousDailyExchangeRateAccessTime")) { PreviousDailyExchangeRateAccessTime = (DateTime)TSession.GetVariable("PreviousDailyExchangeRateAccessTime"); } DateTime PreviousDailyExchangeRateCleanTime = DateTime.UtcNow.AddDays(-30); if (TSession.HasVariable("PreviousDailyExchangeRateCleanTime")) { PreviousDailyExchangeRateCleanTime = (DateTime)TSession.GetVariable("PreviousDailyExchangeRateCleanTime"); } if ((DateTime.UtcNow - PreviousDailyExchangeRateAccessTime).TotalHours > 8) { // Nobody has opened a DailyExchangeRate screen for 8 hours if ((DateTime.UtcNow - PreviousDailyExchangeRateCleanTime).TotalHours > 24) { // It is more than 24 hours since our last clean TDBTransaction t = new TDBTransaction(); TDataBase db = DBAccess.Connect("DoDailyExchangeRateClean"); bool bSubmissionOk = false; db.WriteTransaction(ref t, ref bSubmissionOk, delegate { string logMsg = String.Empty; int affectedRowCount = 0; // Standard is that we delete rows applicable to dates more than 60 days old string criticalDate = DateTime.Now.AddDays(-60).ToString("yyyy-MM-dd"); try { // Our deletion rule is to delete rows where // either the effective date is too old and we have no info about creation or modification // or the creation date is too old and we have no info about any modification // or the modification date is too old // These rules ensure that if rates are added to a DB that is past its last accounting period (like SA-DB) // we can still continue to use the DER screen to add unused rates because they will have create/modify times // that can be long past the final accounting period because we will keep // any row that has been modified recently, whatever the effective date or creation date // any row that was created recently but not subsequently modified, whatever the effective date // any row where we don't have info about create/modify but where the effective date is recent string sql = String.Format( "DELETE FROM PUB_{0} WHERE (({1}<'{2}') and {3} is NULL and {4} is NULL) or (({3}<'{2}') and {4} is NULL) or ({4}<'{2}')", ADailyExchangeRateTable.GetTableDBName(), ADailyExchangeRateTable.GetDateEffectiveFromDBName(), criticalDate, ADailyExchangeRateTable.GetDateCreatedDBName(), ADailyExchangeRateTable.GetDateModifiedDBName()); affectedRowCount = db.ExecuteNonQuery(sql, t); bSubmissionOk = true; TSession.SetVariable("PreviousDailyExchangeRateCleanTime", DateTime.UtcNow); } catch (Exception ex) { logMsg = "An error occurred while trying to purge the Daily Exchange Rate table of 'aged' rows."; logMsg += String.Format(" The exception message was: {0}", ex.Message); } if ((affectedRowCount > 0) && (logMsg == String.Empty)) { logMsg = String.Format("The Daily Exchange Rate table was purged of {0} entries applicable prior to ", affectedRowCount) + criticalDate; } if (logMsg != String.Empty) { TLogging.Log(logMsg); } }); } } TSession.SetVariable("PreviousDailyExchangeRateAccessTime", DateTime.UtcNow); }
/// <summary> /// run the report /// </summary> private static void Run(string AConfigFileName, string ASessionID, string AReportID, TRptDataCalculator ADatacalculator, TParameterList AParameterList) { // need to initialize the database session TSession.InitThread("Reporting Webconnector", AConfigFileName, ASessionID); TDataBase db = DBAccess.Connect("TReportGeneratorWebConnector"); TDBTransaction Transaction = new TDBTransaction(); bool Success = false; bool Submit = true; string HTMLOutput = String.Empty; HtmlDocument HTMLDocument = new HtmlDocument(); string ErrorMessage = String.Empty; try { db.ReadTransaction(ref Transaction, delegate { Exception myException = null; if (ADatacalculator.GenerateResult(ref AParameterList, ref HTMLOutput, out HTMLDocument, ref ErrorMessage, ref myException, Transaction)) { Success = true; } else { TLogging.Log(ErrorMessage); } }); } catch (Exception Exc) { TLogging.Log("Problem calculating report: " + Exc.ToString()); TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile); Success = false; ErrorMessage = Exc.Message; } /* * if (TDBExceptionHelper.IsTransactionSerialisationException(FException)) * { * // do nothing - we want this exception to bubble up * } * else if (FException is Exception && FException.InnerException is EOPDBException) * { * EOPDBException DbExc = (EOPDBException)FException.InnerException; * * if (DbExc.InnerException is Exception) * { * if (DbExc.InnerException is PostgresException) * { * PostgresException PgExc = (PostgresException)DbExc.InnerException; * * if (PgExc.SqlState == "57014") // SQL statement timeout problem * { * FErrorMessage = Catalog.GetString( * "Error - Database took too long to respond. Try different parameters to return fewer results."); * } * } * else * { * FErrorMessage = DbExc.InnerException.Message; * } * * FException = null; * } * } */ try { // store the report result db.WriteTransaction(ref Transaction, ref Submit, delegate { // delete report results that are expired. string sql = "DELETE FROM PUB_s_report_result WHERE s_valid_until_d < NOW()"; db.ExecuteNonQuery(sql, Transaction); // TODO: only keep maximum of 10 report results per user (s_created_by_c) // store success, store parameter list, store html document SReportResultTable table = new SReportResultTable(); SReportResultRow row = table.NewRowTyped(); row.ReportId = AReportID; row.SessionId = TSession.GetSessionID(); row.ValidUntil = DateTime.Now.AddHours(12); row.ParameterList = AParameterList.ToJson(); row.ResultHtml = HTMLOutput; row.Success = Success; row.ErrorMessage = ErrorMessage; table.Rows.Add(row); SReportResultAccess.SubmitChanges(table, Transaction); Submit = true; }); } catch (Exception Exc) { TLogging.Log("Problem storing report result: " + Exc.ToString()); TLogging.Log(Exc.StackTrace, TLoggingType.ToLogfile); Success = false; ErrorMessage = Exc.Message; } db.CloseDBConnection(); TProgressTracker.FinishJob(AReportID); }
public static TSubmitChangesResult SaveCorporateExchangeSetupTDS(ref CorporateExchangeSetupTDS AInspectDS, out int ATransactionsChanged, out TVerificationResultCollection AVerificationResult) { AVerificationResult = new TVerificationResultCollection(); ATransactionsChanged = -1; int TransactionsChanged = -1; AInspectDS = AInspectDS.GetChangesTyped(true); if (AInspectDS == null) { AVerificationResult.Add(new TVerificationResult( Catalog.GetString("Save Corportate Exchange Rates"), Catalog.GetString("No changes - nothing to do"), TResultSeverity.Resv_Info)); return(TSubmitChangesResult.scrNothingToBeSaved); } TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("SaveCorporateExchangeSetupTDS"); bool SubmissionOK = true; CorporateExchangeSetupTDS InspectDS = AInspectDS; db.WriteTransaction( ref Transaction, ref SubmissionOK, delegate { foreach (ACorporateExchangeRateRow xchangeRateRow in InspectDS.ACorporateExchangeRate.Rows) { if ((xchangeRateRow.RowState == DataRowState.Modified) || (xchangeRateRow.RowState == DataRowState.Added)) { // should only be -1 if no exchange rates were modified or created if (TransactionsChanged == -1) { TransactionsChanged = 0; } String rateOfExchangeStr = xchangeRateRow.RateOfExchange.ToString( CultureInfo.InvariantCulture); // update international amounts for all gl transaction using modified or new exchange rate string Query = "UPDATE a_transaction SET a_amount_in_intl_currency_n = " + "ROUND (a_amount_in_base_currency_n / " + rateOfExchangeStr + ", 2)" + " FROM a_ledger" + " WHERE EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Month + " AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Year + " AND a_ledger.a_ledger_number_i = a_transaction.a_ledger_number_i" + " AND a_ledger.a_base_currency_c = '" + xchangeRateRow.FromCurrencyCode + "'" + " AND a_ledger.a_intl_currency_c = '" + xchangeRateRow.ToCurrencyCode + "'"; TransactionsChanged += db.ExecuteNonQuery(Query, Transaction); } if (TransactionsChanged > 0) { // // I also need to correct entries in GLM and GLMP after modifying these transactions: DataTable ledgerTbl = db.SelectDT( "SELECT * FROM a_ledger WHERE " + " a_ledger.a_base_currency_c = '" + xchangeRateRow.FromCurrencyCode + "'" + " AND a_ledger.a_intl_currency_c = '" + xchangeRateRow.ToCurrencyCode + "'", "a_ledger", Transaction); foreach (DataRow ledgerRow in ledgerTbl.Rows) { Int32 ledgerNumber = Convert.ToInt32(ledgerRow["a_ledger_number_i"]); DataTable tempTbl = db.SelectDT( "SELECT DISTINCT a_batch.a_batch_period_i, a_batch.a_batch_year_i FROM" + " a_batch, a_transaction, a_ledger WHERE" + " EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Month + " AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Year + " AND a_transaction.a_ledger_number_i =" + ledgerNumber + " AND a_batch.a_batch_number_i = a_transaction.a_batch_number_i " + " ORDER BY a_batch_period_i", "temp", Transaction); if ((tempTbl == null) || (tempTbl.Rows.Count == 0)) { continue; } Int32 transactionPeriod = Convert.ToInt32(tempTbl.Rows[0]["a_batch_period_i"]); Int32 transactionYear = Convert.ToInt32(tempTbl.Rows[0]["a_batch_year_i"]); DataTable glmTbl = db.SelectDT( "SELECT * from a_general_ledger_master" + " WHERE a_ledger_number_i = " + ledgerNumber + " AND a_year_i = " + transactionYear, "temp", Transaction); Boolean seemsToWorkOk = true; // TLogging.Log("GLM correction: "); Int32 glmSequence = 0; String accountCode = ""; String costCentreCode = ""; String problem = ""; foreach (DataRow glmRow in glmTbl.Rows) { glmSequence = Convert.ToInt32(glmRow["a_glm_sequence_i"]); accountCode = glmRow["a_account_code_c"].ToString(); costCentreCode = glmRow["a_cost_centre_code_c"].ToString(); tempTbl = db.SelectDT( "SELECT sum(a_amount_in_intl_currency_n) AS debit_total FROM a_transaction WHERE " + " EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Month + " AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Year + " AND a_account_code_c = '" + accountCode + "'" + " AND a_cost_centre_code_c = '" + costCentreCode + "'" + " AND a_debit_credit_indicator_l", "temp", Transaction); Boolean hasDebitTransactions = ( tempTbl != null && tempTbl.Rows[0]["debit_total"].GetType() != typeof(System.DBNull) ); Decimal debitTotal = 0; if (hasDebitTransactions) { seemsToWorkOk = (tempTbl.Rows.Count == 1); if (!seemsToWorkOk) { problem = "DebitTotal"; break; } debitTotal = Convert.ToDecimal(tempTbl.Rows[0]["debit_total"]); } tempTbl = db.SelectDT( "SELECT sum(a_amount_in_intl_currency_n) AS credit_total FROM a_transaction WHERE " + " EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Month + " AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Year + " AND a_account_code_c = '" + accountCode + "'" + " AND a_cost_centre_code_c = '" + costCentreCode + "'" + " AND NOT a_debit_credit_indicator_l", "temp", Transaction); Boolean hasCreditTransactions = ( tempTbl != null && tempTbl.Rows[0]["credit_total"].GetType() != typeof(System.DBNull) ); Decimal creditTotal = 0; if (hasCreditTransactions) { seemsToWorkOk = (tempTbl.Rows.Count == 1); if (!seemsToWorkOk) { problem = "CreditTotal"; break; } creditTotal = Convert.ToDecimal(tempTbl.Rows[0]["credit_total"]); } if (!hasDebitTransactions && !hasCreditTransactions) { // TLogging.Log("CostCentre " + costCentreCode + " Account " + accountCode + " - no transactions."); continue; } Decimal lastMonthBalance = 0; if (transactionPeriod > 1) { tempTbl = db.SelectDT( "SELECT a_actual_intl_n as last_month_balance " + " FROM a_general_ledger_master_period WHERE " + " a_glm_sequence_i = " + glmSequence + " AND a_period_number_i = " + (transactionPeriod - 1), "temp", Transaction); seemsToWorkOk = (tempTbl.Rows.Count == 1); if (!seemsToWorkOk) { problem = "lastMonthBalance"; break; } lastMonthBalance = Convert.ToDecimal(tempTbl.Rows[0]["last_month_balance"]); } else { lastMonthBalance = Convert.ToInt32(glmRow["a_start_balance_intl_n"]); } tempTbl = db.SelectDT( "SELECT a_actual_intl_n as this_month_balance " + " FROM a_general_ledger_master_period WHERE " + " a_glm_sequence_i = " + glmSequence + " AND a_period_number_i = " + transactionPeriod, "temp", Transaction); seemsToWorkOk = (tempTbl.Rows.Count == 1); if (!seemsToWorkOk) { problem = "thisMonthBalance"; break; } Decimal thisMonthBalance = Convert.ToDecimal(tempTbl.Rows[0]["this_month_balance"]); tempTbl = db.SelectDT( "SELECT a_debit_credit_indicator_l AS debit_indicator FROM " + " a_account WHERE a_account_code_c = '" + accountCode + "'" + " AND a_ledger_number_i = " + ledgerNumber, "temp", Transaction); seemsToWorkOk = (tempTbl.Rows.Count == 1); if (!seemsToWorkOk) { problem = "debitCreditIndicator"; break; } Boolean debitCreditIndicator = Convert.ToBoolean(tempTbl.Rows[0]["debit_indicator"]); Decimal newPeriodBalance = (debitCreditIndicator) ? lastMonthBalance + debitTotal - creditTotal : lastMonthBalance - debitTotal + creditTotal; Decimal discrepency = newPeriodBalance - thisMonthBalance; db.ExecuteNonQuery( "UPDATE a_general_ledger_master_period SET " + " a_actual_intl_n = a_actual_intl_n + " + discrepency.ToString(CultureInfo.InvariantCulture) + " WHERE a_glm_sequence_i = " + glmSequence + " AND a_period_number_i >= " + transactionPeriod, Transaction); db.ExecuteNonQuery( "UPDATE a_general_ledger_master SET " + " a_ytd_actual_intl_n = a_ytd_actual_intl_n + " + discrepency.ToString(CultureInfo.InvariantCulture) + " WHERE a_glm_sequence_i = " + glmSequence, Transaction); // TLogging.Log("Discrepency for CostCentre " + costCentreCode + " Account " + accountCode + " is " + discrepency); } // foreach glmRow if (!seemsToWorkOk) { TLogging.Log("SaveCorporateExchangeSetupTDS: unable to read " + problem + " for CostCentre " + costCentreCode + " Account " + accountCode); SubmissionOK = false; } } // foreach ledgerRow } // if TransactionsChanged } // save changes to exchange rates ACorporateExchangeRateAccess.SubmitChanges(InspectDS.ACorporateExchangeRate, Transaction); }); // WriteTransaction TSubmitChangesResult SubmissionResult; if (SubmissionOK) { SubmissionResult = TSubmitChangesResult.scrOK; } else { SubmissionResult = TSubmitChangesResult.scrError; } AInspectDS = InspectDS; ATransactionsChanged = TransactionsChanged; return(SubmissionResult); }
/// load data from csv files and sql statements public static bool LoadData(string AHostname, string ADatabaseName, string AUsername, string APassword, string ALoadSQLFileName) { StreamReader sr = null; TDBTransaction WriteTransaction = new TDBTransaction(); bool SubmissionResult = false; TDataBase DBAccessObj = new TDataBase(TDBType.MySQL); try { DBAccessObj.EstablishDBConnection(TDBType.MySQL, AHostname, "", ADatabaseName, AUsername, APassword, "", true, "GenerateSQL.TLoadMysql.LoadData DB Connection"); sr = new StreamReader(ALoadSQLFileName); } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); throw e; } DBAccessObj.WriteTransaction(ref WriteTransaction, ref SubmissionResult, delegate { // one command per line. // file is in postgresql syntax // either COPY FROM or INSERT while (!sr.EndOfStream) { string line = sr.ReadLine(); while (!line.Trim().StartsWith("--") && !line.Trim().EndsWith(";") && !sr.EndOfStream) { string templine = sr.ReadLine(); if (!templine.StartsWith("--")) { line += " " + templine; } } if (line.Trim().ToUpper().StartsWith("INSERT")) { DBAccessObj.ExecuteNonQuery(line, WriteTransaction); } else if (line.Trim().ToUpper().StartsWith("UPDATE")) { DBAccessObj.ExecuteNonQuery(line, WriteTransaction); } else if (line.Trim().ToUpper().StartsWith("COPY")) { // pgsql: COPY p_language FROM 'c:/p_language.csv' WITH DELIMITER AS ',' NULL AS '?' CSV QUOTE AS '"' ESCAPE AS '"'; // mysql: LOAD DATA LOCAL INFILE 'c:/p_language.csv' INTO TABLE p_language FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'; // need to fix the NULL value from ? to NULL string DataFilename = line.Substring(line.IndexOf("'") + 1); DataFilename = DataFilename.Substring(0, DataFilename.IndexOf("'")); string TableName = line.Substring(line.IndexOf("COPY ") + 5); TableName = TableName.Substring(0, TableName.IndexOf(" ")); StreamReader sData = new StreamReader(DataFilename); StreamWriter sDataWriter = new StreamWriter(DataFilename + ".local"); bool firstRow = true; while (!sData.EndOfStream) { string CSVDataQuestionMark = sData.ReadLine().Trim(); string CSVDataNULL = string.Empty; while (CSVDataQuestionMark.Length > 0) { bool quotedValue = CSVDataQuestionMark.StartsWith("\""); string value = StringHelper.GetNextCSV(ref CSVDataQuestionMark, ","); if (value == "?") { value = "NULL"; } // if true or false is written in quotes, do not convert to integer. needed for a_account_property if ((!quotedValue && (value == "false")) || (value == "no")) { value = "0"; } if ((!quotedValue && (value == "true")) || (value == "yes")) { value = "1"; } CSVDataNULL = StringHelper.AddCSV(CSVDataNULL, value); } if (CSVDataNULL.Length > 0) { if (firstRow) { firstRow = false; } else { sDataWriter.WriteLine(); } sDataWriter.Write(CSVDataNULL); } } sData.Close(); sDataWriter.Close(); // see also http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html string stmt = String.Format( "LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '" + Environment.NewLine + "';", DataFilename + ".local", TableName); DBAccessObj.ExecuteNonQuery(stmt, WriteTransaction); } } SubmissionResult = true; sr.Close(); }); DBAccessObj.CloseDBConnection(); return(true); }
/// load data from csv files and sql statements public static bool ExecuteLoadScript(TDataDefinitionStore ADataDefinition, string AHostname, string ADatabaseName, string AUsername, string APassword, string ALoadSQLFileName) { StreamReader sr = null; TDBTransaction WriteTransaction = new TDBTransaction(); bool SubmissionResult = false; TDataBase DBAccessObj = new TDataBase(TDBType.MySQL); try { DBAccessObj.EstablishDBConnection(TDBType.MySQL, AHostname, "", ADatabaseName, AUsername, APassword, "", true, "GenerateSQL.TLoadMysql.LoadData DB Connection"); sr = new StreamReader(ALoadSQLFileName); } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); throw e; } // one command per line. // file is in postgresql syntax // either COPY FROM or INSERT while (!sr.EndOfStream) { string line = sr.ReadLine(); while (!line.Trim().StartsWith("--") && !line.Trim().EndsWith(";") && !sr.EndOfStream) { string templine = sr.ReadLine(); if (!templine.StartsWith("--")) { line += " " + templine; } } if (line.Trim().ToUpper().StartsWith("INSERT") || line.Trim().ToUpper().StartsWith("UPDATE")) { DBAccessObj.WriteTransaction(ref WriteTransaction, ref SubmissionResult, delegate { DBAccessObj.ExecuteNonQuery(line, WriteTransaction); SubmissionResult = true; }); } else if (line.Trim().ToUpper().StartsWith("COPY")) { // pgsql: COPY p_language FROM 'c:/p_language.csv' WITH DELIMITER AS ',' NULL AS '?' CSV QUOTE AS '"' ESCAPE AS '"'; // mysql: LOAD DATA LOCAL INFILE 'c:/p_language.csv' INTO TABLE p_language FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'; // But MySQL 8 makes it quite difficult for security reasons, to use LOAD DATA LOCAL INFILE. // So we parse the file and load the data directly. It is not a huge performance loss. DBAccessObj.WriteTransaction(ref WriteTransaction, ref SubmissionResult, delegate { string tablename = StringHelper.GetCSVValue(line.Trim().Replace(" ", ","), 1); LoadData(DBAccessObj, ADataDefinition, Path.GetDirectoryName(ALoadSQLFileName), tablename); SubmissionResult = true; }); } } sr.Close(); DBAccessObj.CloseDBConnection(); return(true); }
public void TestDBAccess_working_after_ExecuteNonQuery_threw_DBLevel_Exception() { TDBTransaction t = new TDBTransaction(); bool SubmissionOK = true; string sql; TDataBase db = DBAccess.Connect("TestDBAccess", FDataBase); try { TLogging.Log("Attempting to run BREAKING INSERT command using ExecuteNonQuery..."); // Arrange #1 // p_type_description_c must not be null, hence an Exception will be thrown when that SQL command is executed sql = "INSERT INTO p_type(p_type_code_c, p_type_description_c) " + "VALUES ('TEST_EXECUTENONQUERY', NULL)"; t = new TDBTransaction(); db.WriteTransaction(ref t, ref SubmissionOK, delegate { // Act #1 // We expect that ExecuteNonQuery will throw a not-null constraint exception - and this is *what we want*! db.ExecuteNonQuery(sql, t); }); } catch (EOPDBException) { // That is the result we want so we can continue. The transaction will have auto-rolled back } catch (Exception) { throw; } try { TLogging.Log("Running WORKING INSERT command using ExecuteNonQuery..."); // Arrange #2 sql = "INSERT INTO p_type(p_type_code_c, p_type_description_c) " + "VALUES ('TEST_EXECUTENONQUERY', 'Test should be fine')"; t = new TDBTransaction(); db.WriteTransaction(ref t, ref SubmissionOK, delegate { // Act #2 AND Assert // We expect that ExecuteNonQuery *will work* after the previous execution threw an Exception and the // Transaction it was enlisted it was rolled back. // Should it throw an Exception of Type 'System.InvalidOperationException' then the likely cause for // that would be that the underlying IDbCommand Object that is used by ExecuteNonQuery was not correctly // disposed of! Assert.DoesNotThrow(delegate { db.ExecuteNonQuery(sql, t); }, "No Exception should have been thrown by the call to the ExecuteNonQuery Method, but an Exception WAS thrown!"); }); } catch (Exception) { throw; } // UNDO the test t = new TDBTransaction(); SubmissionOK = true; db.WriteTransaction(ref t, ref SubmissionOK, delegate { sql = "DELETE FROM p_type" + " WHERE p_type_code_c = 'TEST_EXECUTENONQUERY' AND p_type_description_c = NULL"; db.ExecuteNonQuery(sql, t); sql = "DELETE FROM p_type" + " WHERE p_type_code_c = 'TEST_EXECUTENONQUERY' AND p_type_description_c = 'Test should be fine'"; db.ExecuteNonQuery(sql, t); }); }
public void T0_Consolidation() { // reset the database, so that there is no consolidated budget CommonNUnitFunctions.ResetDatabase(); TPetraServerConnector.Connect("../../etc/TestServer.config"); TDataBase db = DBAccess.Connect("T0_Consolidation"); string budgetTestFile = TAppSettingsManager.GetValue("GiftBatch.file", CommonNUnitFunctions.rootPath + "/csharp/ICT/Testing/lib/MFinance/SampleData/BudgetImport-All.csv"); int BudgetsAdded; int BudgetsUpdated; int BudgetsFailed; TVerificationResultCollection VerificationResult; BudgetTDS ImportDS = new BudgetTDS(); string ImportString = File.ReadAllText(budgetTestFile); // import budget from CSV decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets( FLedgerNumber, ImportString, budgetTestFile, new string[] { ",", "dmy", "American" }, ref ImportDS, out BudgetsAdded, out BudgetsUpdated, out BudgetsFailed, out VerificationResult); Assert.AreNotEqual(0, RowsImported, "expect to import several rows"); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult, "ImportBudgets has critical errors:"); BudgetTDSAccess.SubmitChanges(ImportDS, db); // check for value in budget table string sqlQueryBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_revision_i = 0 AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), ABudgetPeriodTable.GetTableDBName(), FLedgerNumber); TDBTransaction Transaction = new TDBTransaction(); decimal budgetValue = -1; db.ReadTransaction(ref Transaction, delegate { budgetValue = Convert.ToDecimal(db.ExecuteScalar(sqlQueryBudget, Transaction)); }); Assert.AreEqual(250m, budgetValue, "problem with importing budget from CSV"); // check for zero in glmperiod budget: that row does not even exist yet, so check that it does not exist string sqlQueryCheckEmptyConsolidatedBudget = String.Format( "SELECT COUNT(*) FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {2} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); Transaction = new TDBTransaction(); db.ReadTransaction(ref Transaction, delegate { Assert.AreEqual(0, db.ExecuteScalar(sqlQueryCheckEmptyConsolidatedBudget, Transaction), "budget should not be consolidated yet"); }); // consolidate the budget TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); // check for correct value in glmperiod budget string sqlQueryConsolidatedBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_glm_sequence_i = {2}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetBudgetBaseDBName(), AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); Transaction = new TDBTransaction(); decimal consolidatedBudgetValue = -1; db.ReadTransaction(ref Transaction, delegate { consolidatedBudgetValue = Convert.ToDecimal(db.ExecuteScalar(sqlQueryConsolidatedBudget, Transaction)); }); Assert.AreEqual(250m, consolidatedBudgetValue, "budget should now be consolidated"); // TODO: also check some summary account and cost centre for summed up budget values // check how reposting a budget works string sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 44 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); bool SubmissionOK = true; Transaction = new TDBTransaction(); db.WriteTransaction(ref Transaction, ref SubmissionOK, delegate { db.ExecuteNonQuery(sqlChangeBudget, Transaction); }); // post all budgets again TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); Transaction = new TDBTransaction(); db.ReadTransaction(ref Transaction, delegate { consolidatedBudgetValue = Convert.ToDecimal(db.ExecuteScalar(sqlQueryConsolidatedBudget, Transaction)); }); Assert.AreEqual(44.0m, consolidatedBudgetValue, "budget should be consolidated with the new value"); // post only a modified budget (testing UnPostBudget) sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 65 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); string sqlMarkBudgetForConsolidation = String.Format("UPDATE PUB_{0} SET {1} = false WHERE " + "a_ledger_number_i = {2} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetTable.GetTableDBName(), ABudgetTable.GetBudgetStatusDBName(), FLedgerNumber); SubmissionOK = true; Transaction = new TDBTransaction(); db.WriteTransaction(ref Transaction, ref SubmissionOK, delegate { db.ExecuteNonQuery(sqlChangeBudget, Transaction); db.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction); }); // post only modified budget again TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, false); Transaction = new TDBTransaction(); db.ReadTransaction( ref Transaction, delegate { consolidatedBudgetValue = Convert.ToDecimal(db.ExecuteScalar(sqlQueryConsolidatedBudget, Transaction)); }); Assert.AreEqual(65.0m, consolidatedBudgetValue, "budget should be consolidated with the new value, after UnPostBudget"); // TODO: test forwarding periods. what happens to next year values, when there is no next year glm record yet? }