/// <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);
});
}
/// <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);
});
}
public void Test_TAccountPeriodToNewYear()
{
// create new ledger which is in year 2010
int intLedgerNumber2010 = CommonNUnitFunctions.CreateNewLedger(new DateTime(2010, 1, 1));
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("Test_TAccountPeriodToNewYear");
bool SubmissionOK = false;
TAccountPeriodToNewYear accountPeriodToNewYear = null;
db.WriteTransaction(
ref transaction,
ref SubmissionOK,
delegate
{
// We are in 2010 and this and 2011 is not a leap year
TVerificationResultCollection verificationResult = new TVerificationResultCollection();
accountPeriodToNewYear = new TAccountPeriodToNewYear(intLedgerNumber2010, transaction);
accountPeriodToNewYear.VerificationResultCollection = verificationResult;
accountPeriodToNewYear.IsInInfoMode = false;
// RunEndOfPeriodOperation ...
accountPeriodToNewYear.RunOperation();
SubmissionOK = true;
});
TAccountPeriodInfo accountPeriodInfo = new TAccountPeriodInfo(intLedgerNumber2010);
accountPeriodInfo.AccountingPeriodNumber = 2;
Assert.AreEqual(2011, accountPeriodInfo.PeriodStartDate.Year, "Test of the year");
Assert.AreEqual(28, accountPeriodInfo.PeriodEndDate.Day, "Test of the Feb. 28th");
SubmissionOK = false;
db.WriteTransaction(
ref transaction,
ref SubmissionOK,
delegate
{
// Switch to 2012 - this is a leap year ...
accountPeriodToNewYear = new TAccountPeriodToNewYear(intLedgerNumber2010, transaction);
accountPeriodToNewYear.IsInInfoMode = false;
accountPeriodToNewYear.RunOperation();
SubmissionOK = true;
});
accountPeriodInfo = new TAccountPeriodInfo(intLedgerNumber2010);
accountPeriodInfo.AccountingPeriodNumber = 2;
Assert.AreEqual(29, accountPeriodInfo.PeriodEndDate.Day, "Test of the Feb. 29th");
}
/// remove all variables that start with a name, eg. PROGRESSTRACKER
public static void ClearVariables(string ANameStartsWith)
{
TDataBase db = ConnectDB("SessionClearVariables");
TDBTransaction t = new TDBTransaction();
bool SubmissionOK = false;
db.WriteTransaction(ref t, ref SubmissionOK,
delegate
{
bool finished = false;
while (!finished)
{
finished = true;
foreach (string name in FSessionValues.Keys)
{
if (name.StartsWith(ANameStartsWith))
{
FSessionValues.Remove(name);
finished = false;
break;
}
}
}
SaveSession(t);
SubmissionOK = true;
});
db.CloseDBConnection();
}
/// 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);
}
/// 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);
}
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);
});
}
/// 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);
}
/// 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);
}
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>
/// set a session variable.
/// store to database immediately
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public static void SetVariable(string name, object value)
{
TDataBase db = ConnectDB("SessionSetVariable");
TDBTransaction t = new TDBTransaction();
bool SubmissionOK = false;
db.WriteTransaction(ref t, ref SubmissionOK,
delegate
{
if (FSessionValues.Keys.Contains(name))
{
FSessionValues[name] = (new TVariant(value)).EncodeToString();
}
else
{
FSessionValues.Add(name, (new TVariant(value)).EncodeToString());
}
SaveSession(t);
SubmissionOK = true;
});
db.CloseDBConnection();
}
public static TSubmitChangesResult SaveData(string ATablename,
ref TTypedDataTable ASubmitTable, out TVerificationResultCollection AVerificationResult)
{
// check access permissions for the current user
TModuleAccessManager.CheckUserPermissionsForTable(ATablename, TTablePermissionEnum.eCanModify | TTablePermissionEnum.eCanModify | TTablePermissionEnum.eCanDelete);
TSubmitChangesResult ReturnValue = TSubmitChangesResult.scrError;
TTypedDataTable SubmitTable = null;
TVerificationResultCollection VerificationResult = null;
TDBTransaction WriteTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("SaveData");
SubmitTable = ASubmitTable;
bool submitOK = true;
// Automatic handling of a DB Transaction - and also the automatic establishment and closing of a DB
// Connection where a DB Transaction can be exectued (only if that should be needed).
db.WriteTransaction(
ref WriteTransaction,
ref submitOK,
delegate
{
ReturnValue = SaveData(ATablename, ref SubmitTable, out VerificationResult, WriteTransaction);
submitOK = ReturnValue == TSubmitChangesResult.scrOK;
});
AVerificationResult = VerificationResult;
if ((ATablename == SSystemDefaultsTable.GetTableDBName()) && (ReturnValue == TSubmitChangesResult.scrOK))
{
// TODO Reload the site key into the session, in case that was changed
}
return(ReturnValue);
}
private void DeleteFlag(String AFlag)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("DeleteFlag", FDataBase);
Boolean SubmissionOK = true;
db.WriteTransaction(
ref Transaction,
ref SubmissionOK,
delegate
{
ALedgerInitFlagTable LedgerInitFlagTable = ALedgerInitFlagAccess.LoadByPrimaryKey(
FLedgerNumber, AFlag, Transaction);
if (LedgerInitFlagTable.Rows.Count == 1)
{
LedgerInitFlagTable[0].Delete();
ALedgerInitFlagAccess.SubmitChanges(LedgerInitFlagTable, Transaction);
}
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
public static bool ReloadUserInfo()
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("ReloadUserInfo");
TPetraPrincipal UserDetails = null;
bool SubmitOK = false;
try
{
db.WriteTransaction(ref Transaction, ref SubmitOK,
delegate
{
LoadUser(UserInfo.GetUserInfo().UserID, out UserDetails, Transaction);
});
UserInfo.SetUserInfo(UserDetails);
SubmitOK = true;
}
catch (Exception Exp)
{
TLogging.Log("Exception occured in ReloadCachedUserInfo: " + Exp.ToString());
throw;
}
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
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>
/// Any foreign account that has a non-zero opening balance should be marked
/// for revaluation.
/// </summary>
private void NoteForexRevalRequired(Int32 ALedgerNumber, Int32 AYear, Int32 ABatchPeriod)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("NoteForexRevalRequired", FDataBase);
Boolean submissionOK = true;
if (ABatchPeriod == FledgerInfo.NumberOfAccountingPeriods)
{
if (FledgerInfo.NumberFwdPostingPeriods == 0)
{
return;
}
else
{
// If we're closing the final period, SetNextPeriod() doesn't update FledgerInfo.CurrentPeriod. But we still want to
// check the next period for foreign forward-posted amounts, so we have to increment the period locally here.
ABatchPeriod++;
}
}
db.WriteTransaction(
ref transaction,
ref submissionOK,
delegate
{
string strSQL = "SELECT Account.a_account_code_c," +
"SUM (GLMP.a_actual_foreign_n) AS Balance" +
" FROM PUB_a_account Account, PUB_a_general_ledger_master GLM, PUB_a_general_ledger_master_period GLMP" +
" WHERE Account.a_ledger_number_i=" + ALedgerNumber +
" AND Account.a_foreign_currency_flag_l=true" +
" AND GLM.a_ledger_number_i=" + ALedgerNumber +
" AND GLM.a_account_code_c=Account.a_account_code_c" +
" AND GLM.a_year_i= " + AYear +
" AND GLMP.a_glm_sequence_i=GLM.a_glm_sequence_i" +
" AND GLMP.a_period_number_i=" + ABatchPeriod +
" GROUP BY Account.a_account_code_c";
DataTable Balance = db.SelectDT(strSQL, "Balance", transaction);
foreach (DataRow Row in Balance.Rows)
{
if (Convert.ToDecimal(Row["Balance"]) != 0)
{
TLedgerInitFlag flag = new TLedgerInitFlag(ALedgerNumber, "", transaction.DataBaseObj);
flag.SetFlagComponent(MFinanceConstants.LEDGER_INIT_FLAG_REVAL,
Row["a_account_code_c"].ToString());
}
}
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
/// <summary>
/// reserve a number of partner keys, to be used by the calling function.
/// useful to create many partner at once, eg. for the demodata
/// </summary>
/// <param name="AFieldPartnerKey"></param>
/// <param name="ANumberOfKeys"></param>
/// <param name="ADataBase"></param>
/// <returns>the first valid partner key to use</returns>
public static System.Int64 ReservePartnerKeys(System.Int64 AFieldPartnerKey, ref Int32 ANumberOfKeys, TDataBase ADataBase = null)
{
Int64 NextPartnerKey = -1;
Int32 NumberOfKeys = ANumberOfKeys;
if (AFieldPartnerKey == -1)
{
AFieldPartnerKey = DomainManager.GSiteKey;
}
TDBTransaction ReadWriteTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("ReservePartnerKeys", ADataBase);
bool SubmissionOK = true;
db.WriteTransaction(ref ReadWriteTransaction,
ref SubmissionOK,
delegate
{
PPartnerLedgerTable PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadWriteTransaction);
NextPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1;
Int64 NextUsedKey =
Convert.ToInt64(db.ExecuteScalar("SELECT MIN(p_partner_key_n) FROM PUB_p_partner WHERE p_partner_key_n >= " +
NextPartnerKey.ToString(), ReadWriteTransaction));
if (NextUsedKey < NextPartnerKey + NumberOfKeys)
{
NumberOfKeys = Convert.ToInt32(NextUsedKey - NextPartnerKey);
}
PartnerLedgerDT[0].LastPartnerId = Convert.ToInt32((NextPartnerKey + NumberOfKeys - 1) - PartnerLedgerDT[0].PartnerKey);
PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, ReadWriteTransaction);
SubmissionOK = true;
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
if (!SubmissionOK)
{
throw new Exception("ReservePartnerKeys failed");
}
ANumberOfKeys = NumberOfKeys;
return(NextPartnerKey);
}
public static void SetDefault(String AKey, object AValue, Boolean ASendUpdateInfoToClient = true, TDataBase ADataBase = null)
{
SUserDefaultsTable UserDefaultsDataTable;
TDataBase db = DBAccess.Connect("LoadUserDefaultsTable", ADataBase);
TDBTransaction WriteTransaction = new TDBTransaction();
bool SubmitOK = false;
db.WriteTransaction(ref WriteTransaction,
ref SubmitOK,
delegate
{
LoadUserDefaultsTable(UserInfo.GetUserInfo().UserID, out UserDefaultsDataTable, db);
DataView view = new DataView(UserDefaultsDataTable);
view.Sort = SUserDefaultsTable.GetDefaultCodeDBName();
int FoundInRow = view.Find(AKey);
if (FoundInRow != -1)
{
// User default found
if (AValue.ToString() != view[FoundInRow][SUserDefaultsTable.GetDefaultValueDBName()].ToString())
{
// Update only if the value is actually different
view[FoundInRow][SUserDefaultsTable.GetDefaultValueDBName()] = AValue.ToString();
SubmitOK = true;
}
}
else
{
// User default not found, add it to the user defaults table
SUserDefaultsRow row = UserDefaultsDataTable.NewRowTyped();
row.UserId = UserInfo.GetUserInfo().UserID;
row.DefaultCode = AKey;
row.DefaultValue = AValue.ToString();
UserDefaultsDataTable.Rows.Add(row);
FoundInRow = view.Find(AKey);
SubmitOK = true;
}
if (SubmitOK)
{
SUserDefaultsAccess.SubmitChanges(UserDefaultsDataTable, WriteTransaction);
if (ASendUpdateInfoToClient)
{
UpdateUserDefaultsOnClient(UserInfo.GetUserInfo().UserID, AKey, AValue.ToString(),
view[FoundInRow][SUserDefaultsTable.GetModificationIdDBName()].ToString());
}
}
});
}
/// <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 Init()
{
TPetraServerConnector.Connect("../../etc/TestServer.config");
// These will be our test ledger numbers
FTestLedgerList.AddRange(new int[] { 9997, 9998, 9999 });
// Load existing data
FMainDS = new GLSetupTDS();
try
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("Init");
db.ReadTransaction(
ref transaction,
delegate
{
ALedgerAccess.LoadAll(FMainDS, transaction);
});
// Check that our test rows are not in the database already
if (!FindTestRows(FTestLedgerList))
{
// Get the initial number of available ledgers
FInitialLedgerCount = TGLSetupWebConnector.GetAvailableLedgers().DefaultView.Count;
bool SubmitOK = true;
db.WriteTransaction(
ref transaction,
ref SubmitOK,
delegate
{
// Add our test rows
AddTestRow(FTestLedgerList[0], "NUnitTestLedger1", false);
AddTestRow(FTestLedgerList[1], "NUnitTestLedger2", true, "JPY");
AddTestRow(FTestLedgerList[2], "NUnitTestLedger2", true);
// Save these new rows
ALedgerAccess.SubmitChanges(FMainDS.ALedger, transaction);
FMainDS.AcceptChanges();
});
FInitSucceeded = true;
}
}
catch (Exception ex)
{
FInitExceptionMessage = ex.Message;
}
}
/// this is a direct way to create a serializable transaction on an anonymous database connection
public static void WriteTransaction(ref TDBTransaction ATransaction,
ref bool ASubmitOK, Action AEncapsulatedDBAccessCode)
{
if (!ATransaction.Valid)
{
TDataBase db = DBAccess.Connect("DBAccess.WriteTransaction");
db.WriteTransaction(ref ATransaction, ref ASubmitOK, AEncapsulatedDBAccessCode);
db.CloseDBConnection();
}
else
{
ATransaction.DataBaseObj.WriteTransaction(ref ATransaction, ref ASubmitOK, AEncapsulatedDBAccessCode);
}
}
/// <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);
}
public static void UpdateUnpostedGiftsTaxDeductiblePct(Int64 ARecipientKey, decimal ANewPct, DateTime ADateFrom)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("UpdateUnpostedGiftsTaxDeductiblePct");
bool SubmissionOK = false;
db.WriteTransaction(
ref Transaction,
ref SubmissionOK,
delegate
{
string Query = "SELECT a_gift_detail.*" +
" FROM a_gift_detail, a_gift_batch, a_gift" +
" WHERE a_gift_detail.p_recipient_key_n = " + ARecipientKey +
" AND a_gift_detail.a_tax_deductible_pct_n <> " + ANewPct +
" AND a_gift_detail.a_modified_detail_l <> true" +
" AND a_gift_detail.a_tax_deductible_l = true" +
" AND a_gift_batch.a_ledger_number_i = a_gift_detail.a_ledger_number_i" +
" AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i" +
" AND a_gift_batch.a_batch_status_c = 'Unposted'" +
" AND a_gift.a_ledger_number_i = a_gift_detail.a_ledger_number_i" +
" AND a_gift.a_batch_number_i = a_gift_detail.a_batch_number_i" +
" AND a_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" +
" AND a_gift.a_date_entered_d >= '" + ADateFrom.ToString("yyyy-MM-dd") + "'";
AGiftDetailTable Table = new AGiftDetailTable();
db.SelectDT(Table, Query, Transaction);
// update fields for each row
for (int i = 0; i < Table.Rows.Count; i++)
{
AGiftDetailRow Row = Table[i];
Row.TaxDeductiblePct = ANewPct;
TaxDeductibility.UpdateTaxDeductibiltyAmounts(ref Row);
}
AGiftDetailAccess.SubmitChanges(Table, Transaction);
SubmissionOK = true;
});
}
private void CommitLedgerChange()
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("CommitLedgerChange", FDataBase);
Boolean SubmissionOK = false;
try
{
db.WriteTransaction(
ref Transaction,
ref SubmissionOK,
delegate
{
ALedgerAccess.SubmitChanges(FLedgerTbl, Transaction);
SubmissionOK = true;
});
FLedgerTbl.AcceptChanges();
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw;
}
if (FDataBase == null)
{
db.CloseDBConnection();
}
GetDataRow();
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
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();
}
public static Int64 GetNextSequence(TSequenceNames ASequence, TDataBase ADataBase = null)
{
Int64 NewSequenceValue = 0;
TDBTransaction Transaction = new TDBTransaction();
bool SubmissionOK = false;
TDataBase db = DBAccess.Connect("GetNextSequence " + ASequence.ToString(), ADataBase);
db.WriteTransaction(
ref Transaction, ref SubmissionOK,
delegate
{
NewSequenceValue = db.GetNextSequenceValue(ASequence.ToString(),
Transaction);
SubmissionOK = true;
});
return(NewSequenceValue);
}
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();
}
