public static Boolean GetConferenceApplications(ref ConferenceApplicationTDS AMainDS, Int64 AConferenceKey)
{
// make sure outreach codes are up to date in case it has changed in Unit record
TAttendeeManagement.RefreshOutreachCode(AConferenceKey);
TDataBase db = DBAccess.Connect("GetConferenceApplications");
TDBTransaction ReadTransaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
PcConferenceTable ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, ReadTransaction);
if (ConferenceTable.Count == 0)
{
ReadTransaction.Rollback();
throw new Exception("Cannot find conference " + AConferenceKey.ToString("0000000000"));
}
string OutreachPrefix = ConferenceTable[0].OutreachPrefix;
// load application data for all conference attendees from db
TApplicationManagement.GetApplications(ref AMainDS, AConferenceKey, OutreachPrefix, "all", -1, true, null, false);
// obtain PPartner records for all the home offices
foreach (PcAttendeeRow AttendeeRow in AMainDS.PcAttendee.Rows)
{
if (AMainDS.PPartner.Rows.Find(new object[] { AttendeeRow.HomeOfficeKey }) == null)
{
PPartnerAccess.LoadByPrimaryKey(AMainDS, AttendeeRow.HomeOfficeKey, ReadTransaction);
}
}
ReadTransaction.Rollback();
return(true);
}
private Int64 GetLedgerPartnerKey(Int32 ALedgerNumber)
{
Int64 retVal = 0;
bool NewTransaction = false;
TDataBase db = DBAccess.Connect("GetLedgerPartnerKey");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
try
{
GiftBatchTDS MainDS = new GiftBatchTDS();
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
if (MainDS.ALedger.Count > 0)
{
retVal = MainDS.ALedger[0].PartnerKey;
}
}
catch (Exception)
{
throw;
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
return(retVal);
}
public void TestDBAccess_SimpleAutoDBConnAndReadTransactionSelector_RequestedConnectionJoin()
{
if (FDBType == TDBType.SQLite)
{
// do not run this test with SQLite
return;
}
TDataBase RequestedConnection = DBAccess.Connect("New DB Connection");
TDBTransaction FirstTransaction = RequestedConnection.BeginTransaction(IsolationLevel.ReadCommitted, -1, "FirstTransaction");
bool newTransaction;
TDBTransaction ReadTransaction = RequestedConnection.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
out newTransaction,
"NewTransaction");
Assert.AreEqual("New DB Connection", ReadTransaction.DataBaseObj.ConnectionName);
// Did we get not a new transaction
Assert.AreEqual(false, newTransaction);
// Did we get the expected transaction?
Assert.AreEqual("FirstTransaction", ReadTransaction.TransactionName);
// Check the transaction we joined is left open
Assert.True(ReadTransaction.Valid);
ReadTransaction.Rollback();
RequestedConnection.CloseDBConnection();
}
public void TestDBAccess_GNoETransaction_throws_proper_ExceptionOnWrongMinimumIsolationLevel()
{
bool NewTrans;
TDataBase db = DBAccess.Connect("TestDBAccess", FDataBase);
//
// Arrange
//
TDBTransaction transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTrans);
// Guard Assert: Check that the new DB Transaction has been taken out
Assert.That(NewTrans, Is.True);
//
// Act and Assert
//
Assert.Throws <EDBTransactionIsolationLevelTooLowException>(() =>
db.GetNewOrExistingTransaction(
IsolationLevel.Serializable,
out NewTrans), "GetNewOrExistingTransaction didn't throw expected " +
"Exception (EDBTransactionIsolationLevelTooLowException) on asking for a minimum IsolationLevel which "
+
"cannot be met");
// Roll back the one DB Transaction that has been requested (this would happen automatically on DB closing, but
// it's better to do this explicitly here so it is clear it isn't forgotten about.
transaction.Rollback();
}
public void TestDBAccess_SimpleAutoDBConnAndReadTransactionSelector_JoinExistingTransaction()
{
TDataBase db = DBAccess.Connect("Test");
TDBTransaction FirstTransaction = db.BeginTransaction(ATransactionName: "FirstTransaction");
TDBTransaction ReadTransaction = new TDBTransaction();
int Result = 0;
DBAccess.SimpleAutoDBConnAndReadTransactionSelector(ATransaction: out ReadTransaction, AName: "SecondTransaction",
AEncapsulatedDBAccessCode: delegate
{
Result =
Convert.ToInt32(ReadTransaction.DataBaseObj.ExecuteScalar("SELECT COUNT(*) FROM p_partner WHERE p_partner_key_n = 43005001",
ReadTransaction));
// Is this the expected connection?
Assert.AreEqual("Default NUnit TTestCommonDB DB Connection", ReadTransaction.DataBaseObj.ConnectionName);
});
// Did we get the expected transaction?
Assert.AreEqual("FirstTransaction", ReadTransaction.TransactionName);
// Check we get a result
Assert.AreEqual(1, Result);
// Check the existing transaction we joined is not rolled back
Assert.True(ReadTransaction.Valid);
// Clear up the FirstTransaction we Began earlier
ReadTransaction.Rollback();
}
public void TestDBAccess_CheckRunningDBTransactionIsolationLevelIsCompatible_WithMinimumIsolationLevel_ExpectTrue()
{
bool NewTrans;
bool Result;
TDataBase db = DBAccess.Connect("TestDBAccess", FDataBase);
//
// Arrange
//
TDBTransaction transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTrans);
// Guard Assert: Check that the new DB Transaction has been taken out
Assert.That(NewTrans, Is.True);
// Act
Result = db.CheckRunningDBTransactionIsolationLevelIsCompatible(IsolationLevel.ReadCommitted);
// Primary Assert
Assert.IsTrue(Result, "Calling CheckRunningDBTransactionIsolationLevelIsCompatible and asking for a minimum " +
"IsolationLevel that is met by the running DB Transactions' IsolationLevel did not return true");
// Roll back the one DB Transaction that has been requested (this would happen automatically on DB closing, but
// it's better to do this explicitly here so it is clear it isn't forgotten about.
transaction.Rollback();
}
public static bool GetBestAddress(Int64 APartnerKey,
out PLocationTable AAddress,
out string ACountryNameLocal)
{
bool NewTransaction;
TDataBase db = DBAccess.Connect("GetBestAddress");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
bool ResultValue = false;
try
{
ResultValue = TAddressTools.GetBestAddress(APartnerKey,
out AAddress,
out ACountryNameLocal,
Transaction);
}
catch (Exception)
{
throw;
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
return(ResultValue);
}
/// create a new church
public static PPartnerRow CreateNewChurchPartner(PartnerEditTDS AMainDS, TDataBase ADataBase = null)
{
PPartnerRow PartnerRow = CreateNewPartner(AMainDS, ADataBase);
TDataBase db = DBAccess.Connect("CreateNewChurchPartner", ADataBase);
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.Serializable);
// make sure denomination "UNKNOWN" exists as this is the default value
if (!PDenominationAccess.Exists("UNKNOWN", Transaction))
{
PDenominationTable DenominationTable = new PDenominationTable();
PDenominationRow DenominationRow = DenominationTable.NewRowTyped();
DenominationRow.DenominationCode = "UNKNOWN";
DenominationRow.DenominationName = "Unknown";
DenominationTable.Rows.Add(DenominationRow);
PDenominationAccess.SubmitChanges(DenominationTable, Transaction);
Transaction.Commit();
}
else
{
Transaction.Rollback();
}
PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_CHURCH;
PartnerRow.PartnerShortName = PartnerRow.PartnerKey.ToString() + ", TestChurch";
PChurchRow ChurchRow = AMainDS.PChurch.NewRowTyped();
ChurchRow.PartnerKey = PartnerRow.PartnerKey;
ChurchRow.ChurchName = "TestChurch";
ChurchRow.DenominationCode = "UNKNOWN";
AMainDS.PChurch.Rows.Add(ChurchRow);
return(PartnerRow);
}
private static void CreateOptionTypes()
{
TDataBase db = DBAccess.Connect("CreateOptionTypes");
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.Serializable);
bool RowsToAdd = false;
string[, ] OptionTypes = new string[, ]
{
{
"ADD_ACCOMM_COST_FOR_TOTAL", "Add accommodation costs to get total costs"
},
{
"COST_PER_DAY", "Calculate conference cost per day"
},
{
"COST_PER_NIGHT", "Calculate conference cost per night"
}
};
try
{
PcConferenceOptionTypeTable OptionTypeTable = PcConferenceOptionTypeAccess.LoadAll(Transaction);
for (int i = 0; i < 3; i++)
{
if (OptionTypeTable.Rows.Find(new object[] { OptionTypes[i, 0] }) == null)
{
PcConferenceOptionTypeRow NewRow = OptionTypeTable.NewRowTyped(false);
NewRow.OptionTypeCode = OptionTypes[i, 0];
NewRow.OptionTypeDescription = OptionTypes[i, 1];
NewRow.UnassignableFlag = false;
NewRow.DeletableFlag = false;
OptionTypeTable.Rows.Add(NewRow);
RowsToAdd = true;
}
}
if (RowsToAdd)
{
// add any new rows to database
PcConferenceOptionTypeAccess.SubmitChanges(OptionTypeTable, Transaction);
}
Transaction.Commit();
TLogging.LogAtLevel(7, "TConferenceDataReaderWebConnector.CreateOptionTypes: commit own transaction.");
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the creation of option types:" + Environment.NewLine + Exc.ToString());
Transaction.Rollback();
throw;
}
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="AErrorCode"></param>
/// <param name="AContext"></param>
/// <param name="AMessageLine1"></param>
/// <param name="AMessageLine2"></param>
/// <param name="AMessageLine3"></param>
/// <param name="AUserID"></param>
/// <param name="AProcessID"></param>
public void AddErrorLogEntry(String AErrorCode,
String AContext,
String AMessageLine1,
String AMessageLine2,
String AMessageLine3,
String AUserID,
Int32 AProcessID)
{
SErrorLogTable ErrorLogTable;
SErrorLogRow NewErrorLogRow;
DateTime ErrorLogDateTime;
String Context;
ErrorLogTable = new SErrorLogTable();
NewErrorLogRow = ErrorLogTable.NewRowTyped(false);
ErrorLogDateTime = DateTime.Now;
if (AContext != "")
{
Context = AContext;
}
else
{
Context = UNKNOWN_CONTEXT;
}
// Set DataRow values
NewErrorLogRow.ErrorCode = AErrorCode;
NewErrorLogRow.UserId = AUserID.ToUpper();
NewErrorLogRow.Date = ErrorLogDateTime;
NewErrorLogRow.Time = Conversions.DateTimeToInt32Time(ErrorLogDateTime);
NewErrorLogRow.ReleaseNumber = TSrvSetting.ApplicationVersion.ToString();
NewErrorLogRow.FileName = Context;
NewErrorLogRow.ProcessId = AProcessID.ToString();
NewErrorLogRow.MessageLine1 = AMessageLine1;
NewErrorLogRow.MessageLine2 = AMessageLine2;
NewErrorLogRow.MessageLine3 = AMessageLine3;
ErrorLogTable.Rows.Add(NewErrorLogRow);
TDataBase db = DBAccess.Connect("AddErrorLogEntry");
TDBTransaction WriteTransaction = db.BeginTransaction(IsolationLevel.Serializable);
// Save DataRow
try
{
SErrorLogAccess.SubmitChanges(ErrorLogTable, WriteTransaction);
WriteTransaction.Commit();
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the saving of the Error Log:" + Environment.NewLine + Exc.ToString());
WriteTransaction.Rollback();
throw;
}
}
/// create new PM data
public static PDataLabelTable CreateNewPMData(long AFromPartnerKey, long AToPartnerKey, IndividualDataTDS AMainDS, TDataBase ADataBase = null)
{
TDataBase db = DBAccess.Connect("CreateNewPMData", ADataBase);
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
// Create a new DataLabel record
PDataLabelTable AllDataLabelTable = PDataLabelAccess.LoadAll(Transaction);
PDataLabelTable DataLabelTable = new PDataLabelTable();
PDataLabelRow DataLabelRow = DataLabelTable.NewRowTyped();
// Get the first available key, which is our unique primary key field
Int32 Key = 1;
while (AllDataLabelTable.Rows.Find(new object[] { Key }) != null)
{
Key++;
}
DataLabelRow.Key = Key;
DataLabelRow.DataType = "char";
DataLabelTable.Rows.Add(DataLabelRow);
// Create a new DataLabelValuePartner record
PDataLabelValuePartnerRow DataLabelValuePartner = AMainDS.PDataLabelValuePartner.NewRowTyped();
DataLabelValuePartner.PartnerKey = AFromPartnerKey;
DataLabelValuePartner.DataLabelKey = DataLabelRow.Key;
AMainDS.PDataLabelValuePartner.Rows.Add(DataLabelValuePartner);
// Create a new PassportDetails record
IndividualDataTDSPmPassportDetailsRow PassportDetails = AMainDS.PmPassportDetails.NewRowTyped();
PassportDetails.PartnerKey = AFromPartnerKey;
PassportDetails.PassportNumber = "0";
PassportDetails.PassportNationalityName = "IRELAND";
AMainDS.PmPassportDetails.Rows.Add(PassportDetails);
// Create two new PersonalData records
PmPersonalDataRow FromPersonalData = AMainDS.PmPersonalData.NewRowTyped();
FromPersonalData.PartnerKey = AFromPartnerKey;
FromPersonalData.HeightCm = 175;
FromPersonalData.WeightKg = 80;
AMainDS.PmPersonalData.Rows.Add(FromPersonalData);
PmPersonalDataRow ToPersonalData = AMainDS.PmPersonalData.NewRowTyped();
ToPersonalData.PartnerKey = AToPartnerKey;
ToPersonalData.WeightKg = 95;
AMainDS.PmPersonalData.Rows.Add(ToPersonalData);
Transaction.Rollback();
return(DataLabelTable);
}
/// <summary>
/// get the number and name of the registration offices that the current user has access for
/// </summary>
/// <returns></returns>
public static PPartnerTable GetRegistrationOffices()
{
bool NewTransaction;
TDataBase db = DBAccess.Connect("GetRegistrationOffices");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
PPartnerTable result = new PPartnerTable();
try
{
List <Int64> offices = GetRegistrationOfficeKeysOfUser(Transaction);
StringCollection FieldList = new StringCollection();
FieldList.Add(PPartnerTable.GetPartnerKeyDBName());
FieldList.Add(PPartnerTable.GetPartnerShortNameDBName());
// get the short names of the registration offices
foreach (Int64 OfficeKey in offices)
{
PPartnerTable partnerTable = PPartnerAccess.LoadByPrimaryKey(OfficeKey, FieldList, Transaction);
result.Merge(partnerTable);
}
// remove unwanted columns
List <string> ColumnNames = new List <string>();
foreach (DataColumn column in result.Columns)
{
ColumnNames.Add(column.ColumnName);
}
foreach (string columnName in ColumnNames)
{
if (!FieldList.Contains(columnName))
{
result.Columns.Remove(columnName.ToString());
}
}
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
result.DefaultView.Sort = PPartnerTable.GetPartnerKeyDBName();
return(result);
}
/// create new gift info
public static AGiftBatchRow CreateNewGiftInfo(Int64 APartnerKey, ref GiftBatchTDS AGiftDS, TDataBase ADataBase = null)
{
TDataBase db = DBAccess.Connect("CreateNewGiftInfo", ADataBase);
bool NewTransaction;
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
ALedgerAccess.LoadAll(AGiftDS, Transaction);
AGiftDS = TGiftTransactionWebConnector.CreateAGiftBatch(AGiftDS.ALedger[0].LedgerNumber, DateTime.Today, "Test batch", db);
// Create a new GiftBatch
AGiftBatchRow Batch = AGiftDS.AGiftBatch[0];
Batch.BankAccountCode = "6000";
Batch.BatchYear = 1;
Batch.BatchPeriod = 1;
Batch.CurrencyCode = "EUR";
Batch.BankCostCentre = Batch.LedgerNumber.ToString() + "00";
Batch.LastGiftNumber = 1;
Batch.ExchangeRateToBase = 0.5M;
// Create a new Gift record
AGiftRow Gift = AGiftDS.AGift.NewRowTyped();
Gift.LedgerNumber = Batch.LedgerNumber;
Gift.BatchNumber = Batch.BatchNumber;
Gift.GiftTransactionNumber = 1;
Gift.DonorKey = APartnerKey;
AGiftDS.AGift.Rows.Add(Gift);
// Create a new GiftDetail record
AGiftDetailRow GiftDetail = AGiftDS.AGiftDetail.NewRowTyped();
GiftDetail.LedgerNumber = Gift.LedgerNumber;
GiftDetail.BatchNumber = Gift.BatchNumber;
GiftDetail.GiftTransactionNumber = Gift.GiftTransactionNumber;
GiftDetail.DetailNumber = 1;
GiftDetail.MotivationGroupCode = "GIFT";
GiftDetail.MotivationDetailCode = "SUPPORT";
// this won't work with RecipientKey 0 anymore. see https://github.com/openpetra/openpetra/issues/183
GiftDetail.RecipientKey = 43000000;
GiftDetail.RecipientLedgerNumber = APartnerKey;
GiftDetail.GiftTransactionAmount = 10;
AGiftDS.AGiftDetail.Rows.Add(GiftDetail);
if (NewTransaction)
{
Transaction.Rollback();
}
return(Batch);
}
public void UpdateRecord()
{
TDBTransaction ReadTransaction = new TDBTransaction();
GiftBatchTDS MainDS = new GiftBatchTDS();
TDataBase db = DBAccess.Connect("test");
db.ReadTransaction(ref ReadTransaction,
delegate
{
ALedgerAccess.LoadAll(MainDS, ReadTransaction);
});
ReadTransaction.Rollback();
MainDS.ALedger[0].LastGiftBatchNumber++;
AGiftBatchRow batch = MainDS.AGiftBatch.NewRowTyped();
batch.LedgerNumber = MainDS.ALedger[0].LedgerNumber;
batch.BatchNumber = MainDS.ALedger[0].LastGiftBatchNumber;
batch.BankAccountCode = "6000";
batch.BatchYear = 0;
batch.BatchPeriod = 1;
batch.CurrencyCode = "EUR";
batch.BatchDescription = "test";
batch.BankCostCentre = (MainDS.ALedger[0].LedgerNumber * 100).ToString("0000");
batch.LastGiftNumber = 0;
batch.HashTotal = 83;
MainDS.AGiftBatch.Rows.Add(batch);
GiftBatchTDSAccess.SubmitChanges(MainDS);
MainDS.AcceptChanges();
MainDS.AGiftBatch[0].BatchDescription = "test2";
GiftBatchTDSAccess.SubmitChanges(MainDS);
TDBTransaction transaction = new TDBTransaction();
AGiftBatchTable batches = null;
db.ReadTransaction(
ref transaction,
delegate
{
batches = AGiftBatchAccess.LoadByPrimaryKey(batch.LedgerNumber, batch.BatchNumber, transaction);
});
// some problems with sqlite and datagrid
Assert.AreEqual(typeof(decimal), batches[0][AGiftBatchTable.ColumnHashTotalId].GetType(), "type decimal");
Assert.AreEqual(83.0m, batches[0].HashTotal, "gift batch hashtotal does not equal");
}
/// <summary>
/// get the family keys of the specified persons
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
public static DataTable GetFamilyKeys(TParameterList AParameters, TResultList AResults)
{
SortedList <string, string> Defines = new SortedList <string, string>();
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
try
{
// prepare the sql statement parameters
AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList);
}
catch (Exception e)
{
TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString());
return(null);
}
string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.GetFamilyKeyOfPerson.sql", Defines);
Boolean NewTransaction;
TDataBase db = DBAccess.Connect("GetFamilyKeys");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
// now run the database query
DataTable resultTable = db.SelectDT(SqlStmt, "result", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(null);
}
return(resultTable);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return(null);
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
}
public static bool ExportAllTables(out string ADataYmlGzBase64)
{
TDataBase DBConnectionObj = null;
TDBTransaction ReadTransaction = new TDBTransaction();
XmlDocument OpenPetraData = TYml2Xml.CreateXmlDocument();
XmlNode rootNode = OpenPetraData.FirstChild.NextSibling;
Assembly TypedTablesAssembly = Assembly.LoadFrom(
TAppSettingsManager.ApplicationDirectory + Path.DirectorySeparatorChar + "Ict.Petra.Shared.lib.data.dll");
try
{
// Open a separate DB Connection for the exporting of the data...
DBConnectionObj = DBAccess.Connect("ExportAllTables");
// ...and start a DB Transaction on that separate DB Connection
ReadTransaction = DBConnectionObj.BeginTransaction(IsolationLevel.Serializable, 0, "ExportAllTables");
ExportTables(rootNode, "MSysMan", "", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MCommon", "", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MPartner", "Partner", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MPartner", "Mailroom", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MFinance", "Account", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MFinance", "Gift", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MFinance", "AP", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MFinance", "AR", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MPersonnel", "Personnel", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MPersonnel", "Units", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MConference", "", TypedTablesAssembly, ReadTransaction);
ExportTables(rootNode, "MHospitality", "", TypedTablesAssembly, ReadTransaction);
ExportSequences(rootNode, ReadTransaction);
}
finally
{
if (DBConnectionObj != null)
{
ReadTransaction.Rollback();
DBConnectionObj.CloseDBConnection();
}
}
ADataYmlGzBase64 = TYml2Xml.Xml2YmlGz(OpenPetraData);
return(true);
}
/// create new recurring gift info
public static ARecurringGiftBatchRow CreateNewRecurringGiftInfo(Int64 APartnerKey, ref GiftBatchTDS AGiftDS, TDataBase ADataBase = null)
{
TDataBase db = DBAccess.Connect("CreateNewRecurringGiftInfo", ADataBase);
bool NewTransaction;
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
ALedgerAccess.LoadAll(AGiftDS, Transaction);
AGiftDS = TGiftTransactionWebConnector.CreateARecurringGiftBatch(AGiftDS.ALedger[0].LedgerNumber, db);
if (NewTransaction)
{
Transaction.Rollback();
}
// Create a new RecurringGiftBatch
ARecurringGiftBatchRow Batch = AGiftDS.ARecurringGiftBatch[0];
Batch.BankAccountCode = "6000";
Batch.CurrencyCode = "EUR";
// Create a new RecurringGift record
ARecurringGiftRow RecurringGift = AGiftDS.ARecurringGift.NewRowTyped();
RecurringGift.LedgerNumber = Batch.LedgerNumber;
RecurringGift.BatchNumber = Batch.BatchNumber;
RecurringGift.GiftTransactionNumber = 1;
RecurringGift.DonorKey = APartnerKey;
AGiftDS.ARecurringGift.Rows.Add(RecurringGift);
// Create a new RecurringGiftDetail record
ARecurringGiftDetailRow RecurringGiftDetail = AGiftDS.ARecurringGiftDetail.NewRowTyped();
RecurringGiftDetail.LedgerNumber = Batch.LedgerNumber;
RecurringGiftDetail.BatchNumber = Batch.BatchNumber;
RecurringGiftDetail.GiftTransactionNumber = 1;
RecurringGiftDetail.MotivationGroupCode = "GIFT";
RecurringGiftDetail.MotivationDetailCode = "SUPPORT";
RecurringGiftDetail.RecipientKey = 43000000;
RecurringGiftDetail.RecipientLedgerNumber = APartnerKey;
RecurringGiftDetail.GiftAmount = 10;
AGiftDS.ARecurringGiftDetail.Rows.Add(RecurringGiftDetail);
return(Batch);
}
public void TestDBAccess_SimpleAutoDBConnAndReadTransactionSelector_CantJoinExistingTransaction()
{
if (FDBType == TDBType.SQLite)
{
// do not run this test with SQLite
return;
}
TDataBase db = DBAccess.Connect("TestDBAccess_SimpleAutoDBConnAndReadTransactionSelector_CantJoinExistingTransaction");
TDBTransaction FirstTransaction = db.BeginTransaction(ATransactionName: "FirstTransaction");
TDBTransaction ReadTransaction = new TDBTransaction();
int Result = 0;
// Initialize TSrvSetting; needed by DBAccess.Connect()
var oink = new TSrvSetting();
Assert.NotNull(oink);
DBAccess.SimpleAutoDBConnAndReadTransactionSelector(ATransaction: out ReadTransaction, AName: "SecondTransaction",
AIsolationLevel: IsolationLevel.Serializable,
AEncapsulatedDBAccessCode: delegate
{
Result =
Convert.ToInt32(ReadTransaction.DataBaseObj.ExecuteScalar("SELECT COUNT(*) FROM p_partner WHERE p_partner_key_n = 43005001",
ReadTransaction));
// Is this the expected connection?
Assert.AreEqual("SecondTransaction", ReadTransaction.DataBaseObj.ConnectionName);
});
// Did we get the expected transaction?
Assert.AreEqual("SecondTransaction", ReadTransaction.TransactionName);
// Check we get a result
Assert.AreEqual(1, Result);
// Check the new transaction is rolled back
Assert.False(ReadTransaction.Valid);
// Check the existing transaction is not rolled back
Assert.True(FirstTransaction.Valid);
// Clear up the FirstTransaction we Began earlier
FirstTransaction.Rollback();
}
public static Boolean LoadUserDefaultsTable(String AUserName,
out SUserDefaultsTable AUserDefaultsDataTable, TDataBase ADataBase = null)
{
Boolean ReturnValue = false;
TDataBase db = DBAccess.Connect("LoadUserDefaultsTable", ADataBase);
TDBTransaction ReadTransaction = null;
bool NewTransaction = false;
try
{
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
if (SUserDefaultsAccess.CountViaSUser(AUserName, ReadTransaction) != 0)
{
AUserDefaultsDataTable = SUserDefaultsAccess.LoadViaSUser(AUserName, null, ReadTransaction,
StringHelper.InitStrArr(new string[] { "ORDER BY", SUserDefaultsTable.GetDefaultCodeDBName() }), 0, 0);
AUserDefaultsDataTable.AcceptChanges();
}
else
{
AUserDefaultsDataTable = new SUserDefaultsTable();
}
ReturnValue = true;
}
finally
{
if (NewTransaction && (ReadTransaction != null))
{
ReadTransaction.Rollback();
TLogging.LogAtLevel(9, "TUserDefaults.LoadUserDefaultsTable: rolled back own transaction.");
}
}
if (ADataBase == null)
{
db.CloseDBConnection();
}
return(ReturnValue);
}
private static BankImportTDS LoadData(Int32 ALedgerNumber, Int32 AStatementKey)
{
TDataBase db = DBAccess.Connect("LoadData");
TDBTransaction dbtransaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
BankImportTDS MatchDS = new BankImportTDS();
// TODO: would it help not to load all? use a_recent_match_d?
AEpMatchAccess.LoadViaALedger(MatchDS, ALedgerNumber, dbtransaction);
TLogging.LogAtLevel(1, "loaded " + MatchDS.AEpMatch.Rows.Count.ToString() + " a_ep_match rows");
AEpTransactionAccess.LoadViaAEpStatement(MatchDS, AStatementKey, dbtransaction);
dbtransaction.Rollback();
MatchDS.AEpMatch.AcceptChanges();
MatchDS.AEpTransaction.AcceptChanges();
return(MatchDS);
}
public void TestSimpleDatabaseAccess()
{
bool NewTransaction = false;
TDataBase db = DBAccess.Connect("TestSimpleDatabaseAccess");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
try
{
Assert.AreEqual(1, Ict.Petra.Server.MFinance.Account.Data.Access.ALedgerAccess.CountAll(Transaction), "Testing the number of ledgers");
}
catch (Exception)
{
throw;
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
}
/// is the current user representing the office that is organising the conference?
/// This user gets to see all registrations.
public static bool IsConferenceOrganisingOffice()
{
// TODO: check for permissions for just one specific office, linked from the config file?
bool NewTransaction;
TDataBase db = DBAccess.Connect("IsConferenceOrganisingOffice");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
List <Int64> AllowedRegistrationOffices = new List <long>();
try
{
AllowedRegistrationOffices = GetRegistrationOfficeKeysOfUser(Transaction);
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
return(AllowedRegistrationOffices.Count > MINIMUM_OFFICES_TO_BECOME_ORGANIZER);
}
private void ExecuteFullQuery(string AContext = null, TDataBase ADataBase = null)
{
TDataBase DBConnectionObj = null;
TDBTransaction ReadTransaction = new TDBTransaction();
bool SeparateDBConnectionEstablished = false;
if (FFindParameters.FParametersGivenSeparately)
{
string SQLOrderBy = "";
string SQLWhereCriteria = "";
if (FFindParameters.FPagedTableWhereCriteria != "")
{
SQLWhereCriteria = "WHERE " + FFindParameters.FPagedTableWhereCriteria;
}
if (FFindParameters.FPagedTableOrderBy != "")
{
SQLOrderBy = " ORDER BY " + FFindParameters.FPagedTableOrderBy;
}
FSelectSQL = "SELECT " + FFindParameters.FPagedTableColumns + " FROM " + FFindParameters.FPagedTable +
' ' +
SQLWhereCriteria + SQLOrderBy;
}
else
{
FSelectSQL = FFindParameters.FSqlQuery;
}
TLogging.LogAtLevel(9, (this.GetType().FullName + ".ExecuteFullQuery SQL:" + FSelectSQL));
// clear temp table. do not recreate because it may be typed
FTmpDataTable.Clear();
try
{
if (ADataBase == null)
{
ADataBase = new TDataBase();
ADataBase.EstablishDBConnection(AContext + " Connection");
SeparateDBConnectionEstablished = true;
}
ReadTransaction = ADataBase.BeginTransaction(IsolationLevel.ReadCommitted,
-1, AContext + " Transaction");
// Fill temporary table with query results (all records)
FTotalRecords = ADataBase.SelectUsingDataAdapter(FSelectSQL, ReadTransaction,
ref FTmpDataTable, out FDataAdapterCanceller,
delegate(ref IDictionaryEnumerator AEnumerator)
{
if (FFindParameters.FColumNameMapping != null)
{
AEnumerator = FFindParameters.FColumNameMapping.GetEnumerator();
return(FFindParameters.FPagedTable + "_for_paging");
}
else
{
return(String.Empty);
}
}, 60, FFindParameters.FParametersArray);
}
catch (PostgresException Exp)
{
if (Exp.SqlState == "57014") // Exception with Code 57014 is what Npgsql raises as a response to a Cancel request of a Command
{
TLogging.LogAtLevel(7, this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; proper reply from Npgsql!");
}
else
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general PostgresException occured: " + Exp.ToString());
}
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
return;
}
catch (Exception Exp)
{
TLogging.Log(this.GetType().FullName + ".ExecuteFullQuery: Query got cancelled; general Exception occured: " + Exp.ToString());
TProgressTracker.SetCurrentState(FProgressID, "Query cancelled!", 0.0m);
TProgressTracker.CancelJob(FProgressID);
return;
}
finally
{
ReadTransaction.Rollback();
// Close separate DB Connection if we opened one earlier
if (SeparateDBConnectionEstablished)
{
DBConnectionObj.CloseDBConnection();
}
}
TLogging.LogAtLevel(7,
(this.GetType().FullName + ".ExecuteFullQuery: FDataAdapter.Fill finished. FTotalRecords: " + FTotalRecords.ToString()));
FPageDataTable = FTmpDataTable.Clone();
FPageDataTable.TableName = FFindParameters.FSearchName;
TProgressTracker.SetCurrentState(FProgressID, "Query executed.", 100.0m);
TProgressTracker.FinishJob(FProgressID);
}
/// <summary>
/// checks if the result for this query is already cached.
/// If not, the result is retrieved from the database.
/// The result is added to the cache, and the result is returned
/// as a DataSet from the cache.
/// </summary>
/// <param name="sql"></param>
/// <param name="AParameters">odbc parameters</param>
/// <param name="ATable">can already have some prepared columns; optional parameter, can be null
/// </param>
/// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null
/// gets passed then the Method executes DB commands with a new DataBase connection</param>
/// <returns>void</returns>
public DataSet GetDataSet(String sql, OdbcParameter[] AParameters, DataTable ATable, TDataBase ADataBase = null)
{
TDataBase DBAccessObj = DBAccess.Connect("GetDataSet", ADataBase);
TDBTransaction ReadTransaction = new TDBTransaction();
Boolean NewTransaction = false;
DataSet newDataSet;
int counter = 0;
foreach (string sqlstr in storedSQLQuery)
{
if (sqlstr == sql)
{
bool SameParameters = false;
if (AParameters == null && storedParameters[counter] == null)
{
SameParameters = true;
}
else if (AParameters == null || storedParameters[counter] == null)
{
SameParameters = false;
}
else if (AParameters.Length != ((OdbcParameter[])storedParameters[counter]).Length)
{
SameParameters = false;
}
else
{
int countParam = 0;
SameParameters = true;
foreach (OdbcParameter p in AParameters)
{
if (p.OdbcType != ((OdbcParameter[])storedParameters[counter])[countParam].OdbcType)
{
SameParameters = false;
}
else
{
SameParameters = (p.Value.ToString() == ((OdbcParameter[])storedParameters[counter])[countParam].ToString());
}
countParam++;
}
}
if (SameParameters)
{
// create a clone of the result, so that the returned datasets
// can be treated separately
return(((DataSet)storedDataSet[counter]).Copy());
}
}
counter++;
}
try
{
ReadTransaction = DBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
if (ATable == null)
{
newDataSet = DBAccessObj.Select(sql, "Cache", ReadTransaction, AParameters);
}
else
{
newDataSet = new DataSet();
newDataSet.Tables.Add(ATable);
ATable.TableName = "Cache";
DBAccessObj.Select(newDataSet, sql, "Cache", ReadTransaction, AParameters);
}
}
finally
{
if (NewTransaction && (ReadTransaction != null))
{
ReadTransaction.Rollback();
}
}
storedDataSet.Add(newDataSet);
storedSQLQuery.Add(sql);
storedParameters.Add(AParameters);
// return a copy, so that changes to the dataset don't affect the stored copy.
return(newDataSet.Copy());
}
/// <summary>
/// Get the actual amount
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="AGLMSeqThisYear"></param>
/// <param name="AGLMSeqNextYear"></param>
/// <param name="APeriodNumber"></param>
/// <param name="ANumberAccountingPeriods"></param>
/// <param name="ACurrentFinancialYear"></param>
/// <param name="AThisYear"></param>
/// <param name="AYTD"></param>
/// <param name="ABalSheetForwardPeriods"></param>
/// <param name="ACurrencySelect"></param>
/// <returns></returns>
private static decimal GetActualInternal(int ALedgerNumber,
int AGLMSeqThisYear,
int AGLMSeqNextYear,
int APeriodNumber,
int ANumberAccountingPeriods,
int ACurrentFinancialYear,
int AThisYear,
bool AYTD,
bool ABalSheetForwardPeriods,
string ACurrencySelect)
{
decimal retVal = 0;
decimal currencyAmount = 0;
bool incExpAccountFwdPeriod = false;
//DEFINE BUFFER a_glm_period FOR a_general_ledger_master_period.
//DEFINE BUFFER a_glm FOR a_general_ledger_master.
//DEFINE BUFFER buf_account FOR a_account.
if (AGLMSeqThisYear == -1)
{
return(retVal);
}
bool newTransaction = false;
TDataBase db = DBAccess.Connect("GetActualInternal");
TDBTransaction dBTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out newTransaction);
AGeneralLedgerMasterTable generalLedgerMasterTable = null;
AGeneralLedgerMasterRow generalLedgerMasterRow = null;
AGeneralLedgerMasterPeriodTable generalLedgerMasterPeriodTable = null;
AGeneralLedgerMasterPeriodRow generalLedgerMasterPeriodRow = null;
AAccountTable AccountTable = null;
AAccountRow AccountRow = null;
try
{
if (APeriodNumber == 0) /* start balance */
{
generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction);
generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0];
switch (ACurrencySelect)
{
case MFinanceConstants.CURRENCY_BASE:
currencyAmount = generalLedgerMasterRow.StartBalanceBase;
break;
case MFinanceConstants.CURRENCY_INTERNATIONAL:
currencyAmount = generalLedgerMasterRow.StartBalanceIntl;
break;
default:
currencyAmount = generalLedgerMasterRow.StartBalanceForeign;
break;
}
}
else if (APeriodNumber > ANumberAccountingPeriods) /* forwarding periods only exist for the current financial year */
{
if (ACurrentFinancialYear == AThisYear)
{
generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear,
APeriodNumber,
dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
else
{
generalLedgerMasterPeriodTable =
AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqNextYear,
(APeriodNumber - ANumberAccountingPeriods),
dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
}
else /* normal period */
{
generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear, APeriodNumber, dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
if (generalLedgerMasterPeriodRow != null)
{
switch (ACurrencySelect)
{
case MFinanceConstants.CURRENCY_BASE:
currencyAmount = generalLedgerMasterPeriodRow.ActualBase;
break;
case MFinanceConstants.CURRENCY_INTERNATIONAL:
currencyAmount = generalLedgerMasterPeriodRow.ActualIntl;
break;
default:
currencyAmount = generalLedgerMasterPeriodRow.ActualForeign;
break;
}
}
if ((APeriodNumber > ANumberAccountingPeriods) && (ACurrentFinancialYear == AThisYear))
{
generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction);
generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0];
AccountTable = AAccountAccess.LoadByPrimaryKey(ALedgerNumber, generalLedgerMasterRow.AccountCode, dBTransaction);
AccountRow = (AAccountRow)AccountTable.Rows[0];
if ((AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) ||
(AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_EXPENSE.ToUpper()) &&
!ABalSheetForwardPeriods)
{
incExpAccountFwdPeriod = true;
currencyAmount -= GetActualInternal(ALedgerNumber,
AGLMSeqThisYear,
AGLMSeqNextYear,
ANumberAccountingPeriods,
ANumberAccountingPeriods,
ACurrentFinancialYear,
AThisYear,
true,
ABalSheetForwardPeriods,
ACurrencySelect);
}
}
if (!AYTD)
{
if (!((APeriodNumber == (ANumberAccountingPeriods + 1)) && incExpAccountFwdPeriod) &&
!((APeriodNumber == (ANumberAccountingPeriods + 1)) && (ACurrentFinancialYear > AThisYear)))
{
/* if it is an income expense acount, and we are in period 13, nothing needs to be subtracted,
* because that was done in correcting the amount in the block above;
* if we are in a previous year, in period 13, don't worry about subtracting.
*
* THIS IS CLEARLY INCORRECT - THE CONDITION ABOVE APPLIES *ONLY* IN THE FIRST FORWARDING PERIOD, NOT IN EVERY FORWARDING PERIOD.
* IF THE METHOD IS ONLY CALLED FROM AUTOGENERATE BUDGETS, THIS IS PROBABLY INCONSEQUENTIAL.
*/
currencyAmount -= GetActualInternal(ALedgerNumber,
AGLMSeqThisYear,
AGLMSeqNextYear,
(APeriodNumber - 1),
ANumberAccountingPeriods,
ACurrentFinancialYear,
AThisYear,
true,
ABalSheetForwardPeriods,
ACurrencySelect);
}
}
retVal = currencyAmount;
}
finally
{
if (newTransaction)
{
dBTransaction.Rollback();
}
}
return(retVal);
}
/// <summary>
/// return a table with gift details for the given date with donor partner keys and bank account numbers
/// </summary>
private static bool GetGiftsByDate(Int32 ALedgerNumber,
BankImportTDS AMainDS,
DateTime ADateEffective,
string ABankAccountCode,
out List <int> AGiftBatchNumbers)
{
TDataBase db = DBAccess.Connect("GetGiftsByDate");
TDBTransaction transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
// first get all gifts, even those that have no bank account associated
string stmt = TDataBase.ReadSqlFile("BankImport.GetDonationsByDate.sql");
OdbcParameter[] parameters = new OdbcParameter[3];
parameters[0] = new OdbcParameter("ALedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date);
parameters[1].Value = ADateEffective;
parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
parameters[2].Value = ABankAccountCode;
db.SelectDT(AMainDS.AGiftDetail, stmt, transaction, parameters, 0, 0);
// calculate the totals of gifts
AMainDS.AGift.Clear();
AGiftBatchNumbers = new List <int>();
foreach (BankImportTDSAGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
{
BankImportTDSAGiftRow giftRow =
(BankImportTDSAGiftRow)AMainDS.AGift.Rows.Find(new object[] { giftdetail.LedgerNumber, giftdetail.BatchNumber,
giftdetail.GiftTransactionNumber });
if (giftRow == null)
{
giftRow = AMainDS.AGift.NewRowTyped(true);
giftRow.LedgerNumber = giftdetail.LedgerNumber;
giftRow.BatchNumber = giftdetail.BatchNumber;
giftRow.GiftTransactionNumber = giftdetail.GiftTransactionNumber;
giftRow.TotalAmount = 0;
giftRow.DonorKey = giftdetail.DonorKey;
AMainDS.AGift.Rows.Add(giftRow);
}
giftRow.TotalAmount += giftdetail.GiftTransactionAmount;
if (!AGiftBatchNumbers.Contains(giftRow.BatchNumber))
{
AGiftBatchNumbers.Add(giftRow.BatchNumber);
}
}
// get PartnerKey and banking details (most important BankAccountNumber) for all donations on the given date
stmt = TDataBase.ReadSqlFile("BankImport.GetBankAccountByDate.sql");
parameters = new OdbcParameter[2];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date);
parameters[1].Value = ADateEffective;
// TODO ? parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
//parameters[2].Value = ABankAccountCode;
// There can be several donors with the same banking details
AMainDS.PBankingDetails.Constraints.Clear();
db.Select(AMainDS, stmt, AMainDS.PBankingDetails.TableName, transaction, parameters);
transaction.Rollback();
return(true);
}
public void Test_YearEnd()
{
intLedgerNumber = CommonNUnitFunctions.CreateNewLedger();
TDataBase db = DBAccess.Connect("Test_YearEnd");
TLedgerInfo LedgerInfo = new TLedgerInfo(intLedgerNumber, db);
Assert.AreEqual(0, LedgerInfo.CurrentFinancialYear, "Before YearEnd, we should be in year 0");
TAccountPeriodInfo periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1);
Assert.AreEqual(new DateTime(DateTime.Now.Year,
1,
1), periodInfo.PeriodStartDate, "Calendar from base database should start with January 1st of this year");
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\test-sql\\gl-test-year-end.sql", intLedgerNumber);
TDBTransaction TestBatchTransaction = db.BeginTransaction(IsolationLevel.Serializable, -1, "Test_YearEnd.PrepareBatches");
TCommonAccountingTool commonAccountingTool =
new TCommonAccountingTool(intLedgerNumber, "NUNIT", db);
commonAccountingTool.AddBaseCurrencyJournal();
commonAccountingTool.JournalDescription = "Test Data accounts";
string strAccountGift = "0200";
string strAccountBank = "6200";
string strAccountExpense = "4100";
// Accounting of some gifts ...
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4301", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 100);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4302", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 200);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4303", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 300);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountGift, "4301", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 100);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountGift, "4302", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 200);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountGift, "4303", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 300);
// Accounting of some expenses ...
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountExpense, "4301", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 150);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountExpense, "4302", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 150);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountExpense, "4303", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 200);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4301", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 150);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4302", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 150);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4303", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 200);
TVerificationResultCollection verificationResult = new TVerificationResultCollection();
commonAccountingTool.CloseSaveAndPost(verificationResult, db); // returns true if posting seemed to work
TestBatchTransaction.Commit();
bool blnLoop = true;
while (blnLoop)
{
TDBTransaction PeriodEndTransaction = db.BeginTransaction(IsolationLevel.Serializable, -1, "Test_2YearEnds.PeriodEnd");
TLedgerInfo LedgerInfo2 = new TLedgerInfo(intLedgerNumber, db);
if (LedgerInfo2.ProvisionalYearEndFlag)
{
blnLoop = false;
}
else
{
TVerificationResultCollection VerificationResult;
List <Int32> glBatchNumbers;
Boolean stewardshipBatch;
TPeriodIntervalConnector.PeriodMonthEnd(
intLedgerNumber, false,
out glBatchNumbers,
out stewardshipBatch,
out VerificationResult,
db);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"Running MonthEnd gave critical error");
}
PeriodEndTransaction.Commit();
}
// check before year end that income and expense accounts are not 0
int intYear = 0;
CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
-50, 0, 50, 0, 100, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
150, 0, 150, 0, 200, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
100, 0, 200, 0, 300, 0);
// test that we cannot post to period 12 anymore, all periods are closed?
LedgerInfo = new TLedgerInfo(intLedgerNumber, db);
Assert.AreEqual(true, LedgerInfo.ProvisionalYearEndFlag, "Provisional YearEnd flag should be set");
List <Int32> glBatches = new List <int>();
TDBTransaction transaction = new TDBTransaction();
bool SubmissionOK = false;
db.WriteTransaction(
ref transaction,
ref SubmissionOK,
delegate
{
//
// Reallocation is never called explicitly like this - it's not really appropriate
// because I'm about to call it again as part of YearEnd, below.
// But a tweak in the reallocation code means that it should now cope with being called twice.
TReallocation reallocation = new TReallocation(LedgerInfo, glBatches, transaction);
reallocation.VerificationResultCollection = verificationResult;
reallocation.IsInInfoMode = false;
reallocation.RunOperation();
SubmissionOK = true;
});
// check amounts after reallocation
CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
-50, 0, 50, 0, 100, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
0, -150, 0, -150, 0, -200);
CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
0, -100, 0, -200, 0, -300);
// first run in info mode
TPeriodIntervalConnector.PeriodYearEnd(intLedgerNumber, true,
out glBatches,
out verificationResult, db);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(verificationResult,
"YearEnd test should not have critical errors");
transaction = db.BeginTransaction(IsolationLevel.Serializable);
// now run for real
TPeriodIntervalConnector.PeriodYearEnd(intLedgerNumber, false,
out glBatches,
out verificationResult, db);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(verificationResult,
"YearEnd should not have critical errors");
transaction.Commit();
transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
++intYear;
// check after year end that income and expense accounts are 0, bank account remains
CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
-50, 0, 50, 0, 100, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
0, 0, 0, 0, 0, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
0, 0, 0, 0, 0, 0);
// also check the glm period records
CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountBank,
-50, 50, 100, db);
CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountExpense,
0, 0, 0, db);
CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountGift,
0, 0, 0, db);
// 9700 is the account that the expenses and income from last year is moved to
TGlmInfo glmInfo = new TGlmInfo(intLedgerNumber, intYear, "9700", db);
glmInfo.Reset();
Assert.IsTrue(glmInfo.MoveNext(), "9700 account not found");
Assert.AreEqual(100, glmInfo.YtdActualBase);
Assert.AreEqual(0, glmInfo.ClosingPeriodActualBase);
LedgerInfo = new TLedgerInfo(intLedgerNumber, db);
Assert.AreEqual(1, LedgerInfo.CurrentFinancialYear, "After YearEnd, we are in a new financial year");
Assert.AreEqual(1, LedgerInfo.CurrentPeriod, "After YearEnd, we are in Period 1");
Assert.AreEqual(false, LedgerInfo.ProvisionalYearEndFlag, "After YearEnd, ProvisionalYearEnd flag should not be set");
periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1, db);
Assert.AreEqual(new DateTime(DateTime.Now.Year + 1,
1,
1), periodInfo.PeriodStartDate, "new Calendar should start with January 1st of next year");
transaction.Rollback();
}
public static DataTable FindContacts(string AContactor,
DateTime?AContactDate,
string ACommentContains,
string AMethodOfContact,
string AModuleID,
string AMailingCode,
PPartnerContactAttributeTable AContactAttributes)
{
Boolean NewTransaction;
DataTable Contacts = new DataTable();
TDataBase db = DBAccess.Connect("FindContacts");
TDBTransaction WriteTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
try
{
string Query = "SELECT p_contact_log.*, p_partner_contact.p_partner_key_n, p_partner.p_partner_short_name_c" +
" FROM p_contact_log, p_partner_contact, p_partner" +
" WHERE" +
" p_partner_contact.p_contact_log_id_i = p_contact_log.p_contact_log_id_i" +
" AND p_partner.p_partner_key_n = p_partner_contact.p_partner_key_n";
if (AContactor.Length > 0)
{
Query += " AND p_contact_log.p_contactor_c = '" + AContactor + "'";
}
if (AContactDate.HasValue)
{
Query += " AND p_contact_log.s_contact_date_d = '" + AContactDate + "'";
}
if (AMethodOfContact.Length > 0)
{
Query += " AND p_contact_log.p_contact_code_c = '" + AMethodOfContact + "'";
}
if (AModuleID.Length > 0)
{
Query += " AND p_contact_log.s_module_id_c = '" + AModuleID + "'";
}
if (AMailingCode.Length > 0)
{
Query += " AND p_contact_log.p_mailing_code_c = '" + AMailingCode + "'";
}
if (ACommentContains.Length > 0)
{
Query += " AND p_contact_log.p_contact_comment_c LIKE '%" + ACommentContains + "%'";
}
if ((AContactAttributes != null) && (AContactAttributes.Rows.Count > 0))
{
Query += " AND EXISTS (SELECT * " +
" FROM p_partner_contact_attribute" +
" WHERE" +
" p_partner_contact_attribute.p_contact_id_i = p_contact_log.p_contact_log_id_i" +
" AND (";
foreach (PPartnerContactAttributeRow Row in AContactAttributes.Rows)
{
Query += " (p_partner_contact_attribute.p_contact_attribute_code_c = '" + Row.ContactAttributeCode + "'" +
" AND p_partner_contact_attribute.p_contact_attr_detail_code_c = '" + Row.ContactAttrDetailCode + "') OR";
}
// remove the final " OR"
Query = Query.Substring(0, Query.Length - 3) + "))";
}
WriteTransaction.DataBaseObj.SelectDT(Contacts, Query, WriteTransaction);
Contacts.PrimaryKey = new DataColumn[] {
Contacts.Columns["p_partner_key_n"], Contacts.Columns["p_contact_log_id_i"]
};
}
catch (Exception e)
{
TLogging.Log(e.Message);
TLogging.Log(e.StackTrace);
}
if (NewTransaction)
{
WriteTransaction.Rollback();
}
return(Contacts);
}
/// <summary>
/// generate the key ministries
/// </summary>
/// <param name="AKeyMinCSVFile"></param>
public static void GenerateKeyMinistries(string AKeyMinCSVFile)
{
XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AKeyMinCSVFile, ",");
XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;
PartnerImportExportTDS PartnerDS = new PartnerImportExportTDS();
TDataBase db = DBAccess.Connect("GenerateKeyMinistries");
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys = "SELECT p_partner_key_n, p_unit_name_c FROM PUB_p_unit WHERE u_unit_type_code_c = 'F'";
DataTable FieldKeys = db.SelectDT(sqlGetFieldPartnerKeys, "keys", Transaction);
Transaction.Rollback();
Int32 NumberOfPartnerKeysReserved = 100;
Int64 NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved);
while (RecordNode != null)
{
int FieldID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "field")) % FieldKeys.Rows.Count;
long FieldPartnerKey = Convert.ToInt64(FieldKeys.Rows[FieldID].ItemArray[0]);
PUnitRow UnitRow = PartnerDS.PUnit.NewRowTyped();
if (NumberOfPartnerKeysReserved == 0)
{
NumberOfPartnerKeysReserved = 100;
NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved);
}
long UnitPartnerKey = NextPartnerKey;
NextPartnerKey++;
NumberOfPartnerKeysReserved--;
UnitRow.PartnerKey = UnitPartnerKey;
UnitRow.UnitName = FieldKeys.Rows[FieldID].ItemArray[1].ToString() + " - " + TXMLParser.GetAttribute(RecordNode, "KeyMinName");
UnitRow.UnitTypeCode = "KEY-MIN";
PartnerDS.PUnit.Rows.Add(UnitRow);
PPartnerRow PartnerRow = PartnerDS.PPartner.NewRowTyped();
PartnerRow.PartnerKey = UnitRow.PartnerKey;
PartnerRow.PartnerShortName = UnitRow.UnitName;
PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_UNIT;
PartnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE;
PartnerDS.PPartner.Rows.Add(PartnerRow);
// add empty location so that the partner can be found in the Partner Find screen
PPartnerLocationRow PartnerLocationRow = PartnerDS.PPartnerLocation.NewRowTyped();
PartnerLocationRow.PartnerKey = UnitRow.PartnerKey;
PartnerLocationRow.LocationKey = 0;
PartnerLocationRow.SiteKey = 0;
PartnerDS.PPartnerLocation.Rows.Add(PartnerLocationRow);
// create unit hierarchy
UmUnitStructureRow UnitStructureRow = PartnerDS.UmUnitStructure.NewRowTyped();
UnitStructureRow.ParentUnitKey = FieldPartnerKey;
UnitStructureRow.ChildUnitKey = UnitRow.PartnerKey;
PartnerDS.UmUnitStructure.Rows.Add(UnitStructureRow);
RecordNode = RecordNode.NextSibling;
}
PartnerImportExportTDSAccess.SubmitChanges(PartnerDS);
}
/// <summary>
/// get all partners that we want to display on the current birthday report
/// </summary>
public static DataTable CalculateBirthdays(TParameterList AParameters, TResultList AResults)
{
SortedList <string, string> Defines = new SortedList <string, string>();
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
try
{
// prepare the sql statement parameters
if (AParameters.Exists("FamilyKey"))
{
SqlParameterList.Add(new OdbcParameter("FamilyKey", OdbcType.Decimal)
{
Value = AParameters.Get("FamilyKey").ToDecimal()
});
Defines.Add("BYFAMILYKEY", string.Empty);
}
else
{
AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList);
}
if (AParameters.Get("param_chkSelectTypes").ToBool() == true)
{
string[] types = AParameters.Get("param_typecode").ToString().Split(new char[] { ',' });
string FilterForTypes = string.Empty;
foreach (string type in types)
{
if (FilterForTypes.Length > 0)
{
FilterForTypes += " OR ";
}
FilterForTypes += "pptype.p_type_code_c = ?";
SqlParameterList.Add(new OdbcParameter("typecode" + FilterForTypes.Length, OdbcType.VarChar)
{
Value = type
});
}
Defines.Add("SELECTTYPES", "(" + FilterForTypes + ")");
}
if (AParameters.Get("param_chkUseDate").ToBool() == true)
{
DateTime FromDate = AParameters.Get("param_dtpFromDate").ToDate();
DateTime ToDate = AParameters.Get("param_dtpToDate").ToDate();
if (FromDate.DayOfYear < ToDate.DayOfYear)
{
Defines.Add("WITHDATERANGE", string.Empty);
SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date)
{
Value = FromDate
});
SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date)
{
Value = ToDate
});
}
else
{
Defines.Add("WITHOUTDATERANGE", string.Empty);
SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date)
{
Value = ToDate
});
SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date)
{
Value = FromDate
});
}
}
}
catch (Exception e)
{
TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString());
return(null);
}
string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.Birthday.sql", Defines);
Boolean NewTransaction;
TDataBase db = DBAccess.Connect("CalculateBirthdays");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
DataTable resultTable = db.SelectDT(SqlStmt, "result", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(null);
}
// if end date is not set, use the end of this year
DateTime AgeDay = DateTime.Now;
if (AParameters.Get("param_chkUseDate").ToBool() == true)
{
AgeDay = AParameters.Get("param_dtpToDate").ToDate();
}
else
{
AgeDay = new DateTime(AgeDay.Year, 12, 31);
}
// Calculate the age, in new column
resultTable.Columns.Add(new DataColumn("age", typeof(Int32)));
foreach (DataRow r in resultTable.Rows)
{
int age = 0;
if (r["DOB"] != DBNull.Value)
{
DateTime BDay = Convert.ToDateTime(r["DOB"]);
age = AgeDay.Year - BDay.Year;
}
r["Age"] = age;
}
// filter by anniversaries?
if ((AParameters.Get("param_chkAnniversaries").ToBool() == true) &&
!AParameters.Get("param_txtAnniversaries").IsZeroOrNull())
{
List <string> anniversaries = new List <string>(AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',' }));
List <DataRow> RowsToDelete = new List <DataRow>();
foreach (DataRow r in resultTable.Rows)
{
if (!anniversaries.Contains(r["Age"].ToString()))
{
RowsToDelete.Add(r);
}
}
foreach (DataRow r in RowsToDelete)
{
resultTable.Rows.Remove(r);
}
}
return(resultTable);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return(null);
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
}