public static BudgetTDS LoadBudgetForAutoGenerate(Int32 ALedgerNumber)
{
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
//TODO: need to filter on Year
ABudgetAccess.LoadViaALedger(FMainDS, ALedgerNumber, Transaction);
ABudgetRevisionAccess.LoadViaALedger(FMainDS, ALedgerNumber, Transaction);
//TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey
ABudgetPeriodAccess.LoadAll(FMainDS, Transaction);
ALedgerAccess.LoadByPrimaryKey(FMainDS, ALedgerNumber, Transaction);
ABudgetTypeAccess.LoadAll(FMainDS, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
FMainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
FMainDS.RemoveEmptyTables();
return(FMainDS);
}
private void CommitLedgerChange()
{
TDBTransaction Transaction = null;
Boolean SubmissionOK = false;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, 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 ex;
}
GetDataRow();
}
public static GiftBatchTDS LoadMotivationDetails(Int32 ALedgerNumber, string AMotivationGroupCode)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadMotivationDetails");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
if (AMotivationGroupCode.Length > 0)
{
AMotivationGroupAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, AMotivationGroupCode, Transaction);
AMotivationDetailAccess.LoadViaAMotivationGroup(MainDS, ALedgerNumber, AMotivationGroupCode, Transaction);
}
else
{
AMotivationGroupAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
}
AMotivationDetailFeeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
return(MainDS);
}
public static Boolean GetCurrentPeriodDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateCurrentPeriod)
{
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
ledgerTable[0].CurrentPeriod,
Transaction);
startDate = accountingPeriodTable[0].PeriodStartDate;
endDate = accountingPeriodTable[0].PeriodEndDate;
});
AStartDateCurrentPeriod = startDate;
AEndDateCurrentPeriod = endDate;
return(true);
}
public static BudgetTDS LoadBudget(Int32 ALedgerNumber)
{
BudgetTDS MainDS = new BudgetTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
//TODO: need to filter on Year
ABudgetAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
ABudgetRevisionAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
//TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey
ABudgetPeriodAccess.LoadAll(MainDS, Transaction);
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
// ABudgetPeriodTable BudgetPeriodTable = new ABudgetPeriodTable();
// ABudgetPeriodRow TemplateRow = (ABudgetPeriodRow)BudgetPeriodTable.NewRow(false);
//
// TemplateRow.BudgetSequence;
// ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
return(MainDS);
}
public static bool GetCurrentPostingRangeDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateLastForwardingPeriod)
{
bool newTransaction = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod,
Transaction);
AStartDateCurrentPeriod = AccountingPeriodTable[0].PeriodStartDate;
AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods,
Transaction);
AEndDateLastForwardingPeriod = AccountingPeriodTable[0].PeriodEndDate;
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(true);
}
public static Boolean GetCurrentPeriodDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateCurrentPeriod)
{
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetCurrentPeriodDates");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
ledgerTable[0].CurrentPeriod,
Transaction);
startDate = accountingPeriodTable[0].PeriodStartDate;
endDate = accountingPeriodTable[0].PeriodEndDate;
});
db.CloseDBConnection();
AStartDateCurrentPeriod = startDate;
AEndDateCurrentPeriod = endDate;
return(true);
}
private void GetLedgerInfo(out DateTime APeriodStartDate, out DateTime APeriodEndDate,
out string AForexGainsLossesAccount, TDataBase ADataBase)
{
ALedgerTable LedgerTable = null;
TDataBase db = DBAccess.Connect("GetLedgerInfo", ADataBase);
TDBTransaction Transaction = new TDBTransaction();
DateTime PeriodStartDate = DateTime.Today;
DateTime PeriodEndDate = DateTime.Today;
string ForexGainsLossesAccount = String.Empty;
db.ReadTransaction(ref Transaction,
delegate
{
LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction);
ForexGainsLossesAccount = LedgerTable[0].ForexGainsLossesAccount;
TFinancialYear.GetStartAndEndDateOfPeriod(FLedgerNumber, LedgerTable[0].CurrentPeriod, out PeriodStartDate, out PeriodEndDate, Transaction);
});
AForexGainsLossesAccount = ForexGainsLossesAccount;
APeriodStartDate = PeriodStartDate;
APeriodEndDate = PeriodEndDate;
}
public static void SetLastReceiptNumber(Int32 ALedgerNumber, Int32 AReceiptNumber)
{
TDBTransaction ReadWriteTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
ALedgerTable LedgerTbl;
try
{
LedgerTbl = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ReadWriteTransaction);
if (LedgerTbl.Rows.Count > 0)
{
LedgerTbl[0].LastHeaderRNumber = AReceiptNumber;
ALedgerAccess.SubmitChanges(LedgerTbl, ReadWriteTransaction);
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the setting of the last Receipt Number:" + Environment.NewLine + Exc.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
}
/// create new AP info
public static AApDocumentRow CreateNewAPInfo(Int64 APartnerKey, ref AccountsPayableTDS AMainDS, TDataBase ADataBase = null)
{
TDataBase db = DBAccess.Connect("CreateNewAPInfo", ADataBase);
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.Serializable);
ALedgerTable LedgerTable = ALedgerAccess.LoadAll(Transaction);
AMainDS = TAPTransactionWebConnector.CreateAApDocument(((ALedgerRow)LedgerTable.Rows[0]).LedgerNumber, APartnerKey, true, db);
// Create a new RecurringGiftBatch
AApDocumentRow Document = AMainDS.AApDocument[0];
Document.DocumentCode = "TEST";
Document.CreditNoteFlag = false;
Document.DateIssued = DateTime.Today;
Document.DateEntered = DateTime.Today;
Document.TotalAmount = 0;
Document.CurrencyCode = "EUR";
Document.LastDetailNumber = 0;
// Create a new RecurringGift record
AApSupplierRow ApSupplierRow = AMainDS.AApSupplier.NewRowTyped();
ApSupplierRow.PartnerKey = APartnerKey;
ApSupplierRow.CurrencyCode = "EUR";
AMainDS.AApSupplier.Rows.Add(ApSupplierRow);
Transaction.Commit();
return(Document);
}
private static Int32 FindFinancialYearByDate(Int32 ALedgerNumber, DateTime ADate)
{
Int32 yearDateBelongsTo = 99;
DateTime yearStartDate = DateTime.Today;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction);
if (LedgerTable.Count == 0)
{
return;
}
ALedgerRow LedgerRow = (ALedgerRow)LedgerTable.Rows[0];
yearDateBelongsTo = LedgerRow.CurrentFinancialYear;
AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, transaction);
if (AccPeriodTable.Count == 0)
{
return;
}
//Find earliest start date (don't assume PK order)
AAccountingPeriodRow AccPeriodRow = null;
for (int i = 0; i < AccPeriodTable.Count; i++)
{
DateTime currentStartDate;
AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[i];
currentStartDate = AccPeriodRow.PeriodStartDate;
if (i > 0)
{
if (yearStartDate > currentStartDate)
{
yearStartDate = currentStartDate;
}
}
else
{
yearStartDate = currentStartDate;
}
}
//Find the correct year
while (ADate < yearStartDate)
{
ADate = ADate.AddYears(1);
yearDateBelongsTo--;
}
}); // Get NewOrExisting AutoReadTransaction
//Set the year to return
return(yearDateBelongsTo);
} // Find FinancialYear ByDate
public static Int32 GetNumberOfPeriods(
System.Int32 ALedgerNumber)
{
Int32 returnValue = 0;
bool newTransaction = false;
try
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
returnValue = LedgerTable[0].NumberOfAccountingPeriods;
}
finally
{
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
return(returnValue);
}
public static Boolean GetCurrentPostingRangeDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateLastForwardingPeriod)
{
DateTime StartDateCurrentPeriod = new DateTime();
DateTime EndDateLastForwardingPeriod = new DateTime();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod,
Transaction);
StartDateCurrentPeriod = AccountingPeriodTable[0].PeriodStartDate;
AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods,
Transaction);
EndDateLastForwardingPeriod = AccountingPeriodTable[0].PeriodEndDate;
});
AStartDateCurrentPeriod = StartDateCurrentPeriod;
AEndDateLastForwardingPeriod = EndDateLastForwardingPeriod;
return(true);
}
public void TestModifyGiftBatch()
{
TDataBase db = DBAccess.Connect("test");
TDBTransaction t = db.BeginTransaction(IsolationLevel.Serializable);
GiftBatchTDS MainDS;
ALedgerAccess.LoadAll(MainDS, t);
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 = 1;
batch.BatchPeriod = 1;
batch.CurrencyCode = "EUR";
batch.BankCostCentre = MainDS.ALedger[0].LedgerNumber.ToString() + "00";
batch.LastGiftNumber = 2;
MainDS.AGiftBatch.Rows.Add(batch);
AGiftRow gift = MainDS.AGift.NewRowTyped();
gift.LedgerNumber = batch.LedgerNumber;
gift.BatchNumber = batch.BatchNumber;
gift.GiftTransactionNumber = 1;
MainDS.AGift.Rows.Add(gift);
gift = MainDS.AGift.NewRowTyped();
gift.LedgerNumber = batch.LedgerNumber;
gift.BatchNumber = batch.BatchNumber;
gift.GiftTransactionNumber = 2;
gift.LastDetailNumber = 1;
MainDS.AGift.Rows.Add(gift);
AGiftDetailRow giftdetail = MainDS.AGiftDetail.NewRowTyped();
giftdetail.LedgerNumber = gift.LedgerNumber;
giftdetail.BatchNumber = gift.BatchNumber;
giftdetail.GiftTransactionNumber = gift.GiftTransactionNumber;
giftdetail.DetailNumber = 1;
giftdetail.MotivationGroupCode = "GIFT";
giftdetail.MotivationDetailCode = "SUPPORT";
MainDS.AGiftDetail.Rows.Add(giftdetail);
MainDS.SubmitChanges(t);
t.Commit();
// now delete the first gift, and fix the gift detail of the second gift
t = db.BeginTransaction(IsolationLevel.Serializable);
MainDS.AGift.Rows.RemoveAt(0);
MainDS.AGift[0].GiftTransactionNumber = 1;
MainDS.AGiftDetail[0].GiftTransactionNumber = 1;
MainDS.AGiftBatch[0].LastGiftNumber = 1;
MainDS.SubmitChanges(t);
g.Commit();
}
private Int64 GetLedgerPartnerKey(Int32 ALedgerNumber)
{
Int64 retVal = 0;
bool NewTransaction = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.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)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
return(retVal);
}
/// create new recurring gift info
public static ARecurringGiftBatchRow CreateNewRecurringGiftInfo(Int64 APartnerKey, ref GiftBatchTDS AGiftDS)
{
ALedgerAccess.LoadAll(AGiftDS, DBAccess.GDBAccessObj.Transaction);
AGiftDS = TGiftTransactionWebConnector.CreateARecurringGiftBatch(AGiftDS.ALedger[0].LedgerNumber);
// 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 = APartnerKey;
RecurringGiftDetail.RecipientLedgerNumber = APartnerKey;
AGiftDS.ARecurringGiftDetail.Rows.Add(RecurringGiftDetail);
return(Batch);
}
public static bool GetAccountingYearPeriodByDate(Int32 ALedgerNumber,
DateTime ADate,
out Int32 AYearNumber,
out Int32 APeriodNumber)
{
bool newTransaction;
Int32 CurrentFinancialYear;
//Set the year to return
AYearNumber = FindFinancialYearByDate(ALedgerNumber, ADate);
if (AYearNumber == 99)
{
AYearNumber = 0;
APeriodNumber = 0;
return(false);
}
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
CurrentFinancialYear = ((ALedgerRow)LedgerTable.Rows[0]).CurrentFinancialYear;
AAccountingPeriodTable AccPeriodTableTmp = new AAccountingPeriodTable();
AAccountingPeriodRow TemplateRow = AccPeriodTableTmp.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.PeriodStartDate = ADate.AddYears(CurrentFinancialYear - AYearNumber);
TemplateRow.PeriodEndDate = ADate.AddYears(CurrentFinancialYear - AYearNumber);
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "<=", ">=" });
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadUsingTemplate(TemplateRow,
operators,
null,
Transaction);
if ((AccountingPeriodTable == null) || (AccountingPeriodTable.Count == 0))
{
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
APeriodNumber = 0;
return(false);
}
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
APeriodNumber = AccountingPeriodRow.AccountingPeriodNumber;
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(true);
}
/// create new AP info
public static AApDocumentRow CreateNewAPInfo(Int64 APartnerKey, ref AccountsPayableTDS AMainDS)
{
ALedgerTable LedgerTable = ALedgerAccess.LoadAll(DBAccess.GDBAccessObj.Transaction);
AMainDS = TAPTransactionWebConnector.CreateAApDocument(((ALedgerRow)LedgerTable.Rows[0]).LedgerNumber, APartnerKey, true);
// Create a new RecurringGiftBatch
AApDocumentRow Document = AMainDS.AApDocument[0];
Document.DocumentCode = "TEST";
Document.CreditNoteFlag = false;
Document.DateIssued = DateTime.Today;
Document.DateEntered = DateTime.Today;
Document.TotalAmount = 0;
Document.CurrencyCode = "EUR";
Document.LastDetailNumber = 0;
// Create a new RecurringGift record
AApSupplierRow ApSupplierRow = AMainDS.AApSupplier.NewRowTyped();
ApSupplierRow.PartnerKey = APartnerKey;
ApSupplierRow.CurrencyCode = "EUR";
AMainDS.AApSupplier.Rows.Add(ApSupplierRow);
return(Document);
}
public static Int32 GetLastReceiptNumber(Int32 ALedgerNumber)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
ALedgerTable LedgerTbl;
try
{
LedgerTbl = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
DBAccess.GDBAccessObj.RollbackTransaction();
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the getting of the last Receipt Number:" + Environment.NewLine + Exc.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
if (LedgerTbl.Rows.Count > 0)
{
return(LedgerTbl[0].LastHeaderRNumber);
}
else
{
return(0); // This is obviously the wrong answer, but I judge it to be unlikely.
}
}
public static GiftBatchTDS LoadMotivationDetails(Int32 ALedgerNumber)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
AMotivationGroupAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailFeeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
return(MainDS);
}
/// <summary>
/// Get the start and end date of the given period in the given year
/// </summary>
public static bool GetStartAndEndDateOfPeriod(Int32 ALedgerNumber,
Int32 AYear,
Int32 APeriodNumber,
out DateTime APeriodStartDate,
out DateTime APeriodEndDate,
TDBTransaction ATransaction)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
// ATransaction can be null
//else if (ATransaction == null)
//{
// throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString(
// "Function:{0} - Database Transaction must not be NULL!"),
// Utilities.GetMethodName(true)));
//}
#endregion Validate Arguments
// invalid period
if (APeriodNumber == -1)
{
APeriodStartDate = DateTime.MinValue;
APeriodEndDate = DateTime.MaxValue;
return(false);
}
AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, ATransaction);
#region Validate Data
if ((AccPeriodTable == null) || (AccPeriodTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Accounting Period data for period {1} in Ledger number {2} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
APeriodNumber,
ALedgerNumber));
}
#endregion Validate Data
AAccountingPeriodRow AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[0];
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ATransaction);
Int32 currentYear = LedgerTable[0].CurrentFinancialYear;
Int32 yearsAgo = currentYear - AYear;
APeriodStartDate = AccPeriodRow.PeriodStartDate.AddYears(0 - yearsAgo);
APeriodEndDate = AccPeriodRow.PeriodEndDate.AddYears(0 - yearsAgo);
return(true);
}
public static BudgetTDS LoadBudgetForAutoGenerate(Int32 ALedgerNumber)
{
BudgetTDS FMainDS = new BudgetTDS();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadBudgetForAutoGenerate");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(FMainDS, ALedgerNumber, Transaction);
#region Validate Data
if ((FMainDS.ALedger == null) || (FMainDS.ALedger.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
int CurrentFinancialYear = FMainDS.ALedger[0].CurrentFinancialYear;
//Load all by Ledger/Year but none may exist
ABudgetTable BudgetTable = new ABudgetTable();
ABudgetRow TemplateRow = (ABudgetRow)BudgetTable.NewRow();
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.Year = CurrentFinancialYear;
StringCollection Operators = StringHelper.InitStrArr(new string[] { "=", "=" });
StringCollection OrderList = new StringCollection();
OrderList.Add("ORDER BY");
OrderList.Add(ABudgetTable.GetCostCentreCodeDBName() + " ASC");
OrderList.Add(ABudgetTable.GetAccountCodeDBName() + " ASC");
ABudgetAccess.LoadUsingTemplate(FMainDS, TemplateRow, Operators, null, Transaction, OrderList, 0, 0);
ABudgetPeriodAccess.LoadViaABudgetTemplate(FMainDS, TemplateRow, Operators, null, Transaction, OrderList, 0, 0);
ABudgetRevisionAccess.LoadByPrimaryKey(FMainDS, ALedgerNumber, CurrentFinancialYear, 0, Transaction);
ABudgetTypeAccess.LoadAll(FMainDS, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
FMainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
FMainDS.RemoveEmptyTables();
return(FMainDS);
}
public static bool GetAccountingYearPeriodByDate(Int32 ALedgerNumber,
DateTime ADate,
out Int32 AYearNumber,
out Int32 APeriodNumber)
{
Int32 CurrentFinancialYear;
//Set the year to return
Int32 YearNumber = FindFinancialYearByDate(ALedgerNumber, ADate);
AYearNumber = YearNumber;
if (AYearNumber == 99)
{
AYearNumber = 0;
APeriodNumber = 0;
return(false);
}
Int32 PeriodNumber = 0;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction);
CurrentFinancialYear = ((ALedgerRow)LedgerTable.Rows[0]).CurrentFinancialYear;
AAccountingPeriodTable AccPeriodTableTmp = new AAccountingPeriodTable();
AAccountingPeriodRow TemplateRow = AccPeriodTableTmp.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.PeriodStartDate = ADate.AddYears(CurrentFinancialYear - YearNumber);
TemplateRow.PeriodEndDate = ADate.AddYears(CurrentFinancialYear - YearNumber);
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "<=", ">=" });
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadUsingTemplate(TemplateRow,
operators,
null,
transaction);
if (AccountingPeriodTable.Count == 0)
{
return;
}
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
PeriodNumber = AccountingPeriodRow.AccountingPeriodNumber;
});
APeriodNumber = PeriodNumber;
return(true);
} // Get AccountingYear Period ByDate
/// <summary>
/// init the exchange rate, to avoid messages "Cannot find exchange rate for EUR USD"
/// </summary>
public static void InitExchangeRate()
{
TAccountPeriodInfo AccountingPeriodInfo =
new TAccountPeriodInfo(FLedgerNumber, 1);
ADailyExchangeRateTable dailyrates = new ADailyExchangeRateTable();
ADailyExchangeRateRow row = dailyrates.NewRowTyped(true);
row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
row.TimeEffectiveFrom = 100;
row.FromCurrencyCode = "USD";
row.ToCurrencyCode = "EUR";
row.RateOfExchange = 1.34m;
dailyrates.Rows.Add(row);
row = dailyrates.NewRowTyped(true);
row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
row.TimeEffectiveFrom = 100;
row.FromCurrencyCode = "USD";
row.ToCurrencyCode = "GBP";
row.RateOfExchange = 1.57m;
dailyrates.Rows.Add(row);
if (!ADailyExchangeRateAccess.Exists(row.FromCurrencyCode, row.ToCurrencyCode, row.DateEffectiveFrom, row.TimeEffectiveFrom, null))
{
ADailyExchangeRateAccess.SubmitChanges(dailyrates, null);
}
ALedgerTable Ledger = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, null);
for (int periodCounter = 1; periodCounter <= Ledger[0].NumberOfAccountingPeriods + Ledger[0].NumberFwdPostingPeriods; periodCounter++)
{
AccountingPeriodInfo = new TAccountPeriodInfo(FLedgerNumber, periodCounter);
ACorporateExchangeRateTable corprates = new ACorporateExchangeRateTable();
ACorporateExchangeRateRow corprow = corprates.NewRowTyped(true);
corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
corprow.TimeEffectiveFrom = 100;
corprow.FromCurrencyCode = "USD";
corprow.ToCurrencyCode = "EUR";
corprow.RateOfExchange = 1.34m;
corprates.Rows.Add(corprow);
corprow = corprates.NewRowTyped(true);
corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
corprow.TimeEffectiveFrom = 100;
corprow.FromCurrencyCode = "USD";
corprow.ToCurrencyCode = "GBP";
corprow.RateOfExchange = 1.57m;
corprates.Rows.Add(corprow);
if (!ACorporateExchangeRateAccess.Exists(corprow.FromCurrencyCode, corprow.ToCurrencyCode, corprow.DateEffectiveFrom, null))
{
ACorporateExchangeRateAccess.SubmitChanges(corprates, null);
}
}
}
/// <summary>
/// Return the country code for the ledger with this key
/// </summary>
public static string GetCountryCodeFromLedger(TDBTransaction ATransaction, Int32 LedgerNumber)
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey((int)(DomainManager.GSiteKey / 1000000), ATransaction);
if (ledgerTable.Rows.Count == 1)
{
return(ledgerTable[0].CountryCode);
}
// no Ledger row for this key, so return invalid country code
return("99");
}
/// 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);
}
/// create new ledger
public static void CreateNewLedger()
{
TVerificationResultCollection VerificationResult;
if (ALedgerAccess.Exists(FLedgerNumber, null) && !TGLSetupWebConnector.DeleteLedger(FLedgerNumber, out VerificationResult))
{
throw new Exception("could not delete ledger");
}
TGLSetupWebConnector.CreateNewLedger(FLedgerNumber, "SecondLedger", "GB", "EUR", "EUR", new DateTime(DateTime.Now.Year - 1,
4,
1), 12, 1, 8, false, true, 1, true, out VerificationResult);
}
private DataTable GetLedgerDetailsTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName)
{
// StringCollection FieldList = new StringCollection();
// FieldList.Add(ALedgerTable.GetLedgerNumberDBName());
// FieldList.Add(ALedgerTable.GetNumberFwdPostingPeriodsDBName());
// FieldList.Add(ALedgerTable.GetNumberOfAccountingPeriodsDBName());
// FieldList.Add(ALedgerTable.GetCurrentPeriodDBName());
// FieldList.Add(ALedgerTable.GetCurrentFinancialYearDBName());
// FieldList.Add(ALedgerTable.GetBranchProcessingDBName());
// FieldList.Add(ALedgerTable.GetBaseCurrencyDBName());
// FieldList.Add(ALedgerTable.GetIntlCurrencyDBName());
return(ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, AReadTransaction));
}
public void TearDown()
{
// Delete our working test rows
foreach (int LedgerNum in FTestLedgerList)
{
DeleteTestRowIfExists(LedgerNum);
}
ALedgerAccess.SubmitChanges(FMainDS.ALedger, null);
// Disconnect
TPetraServerConnector.Disconnect();
}
public static string GetLedgerBaseCurrency(Int32 ALedgerNumber)
{
string ReturnValue = "";
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(ref Transaction,
delegate
{
ReturnValue = ((ALedgerRow)ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction).Rows[0]).BaseCurrency;
});
return(ReturnValue);
}
