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